C# Session.CreateObjectQuery方法代码示例

    protected void MagicItemCommand(object sender, MagicItemEventArgs e)
    {
        if (e.CommandName != "Download")
            return;

        using (ISession session = new Session())
        {
            int periodId = Cast.Int(this.drpPeriod.SelectedValue, 0);
            ObjectQuery query = session.CreateObjectQuery(@"
        select m.MemberNumber as MemberNumber,m.Name as MemberName,ca.BeginAmt as BeginAmt,ca.EndAmt as EndAmt
        from MbrCashAccountBalance ca
        left join Member m on m.MemberID=ca.MemberID
        order by m.MemberNumber")
                .Attach(typeof(MbrCashAccountBalance)).Attach(typeof(Member))
                .Where(Exp.Eq("ca.PeriodID", periodId));
            if (!string.IsNullOrEmpty(this.txtMbrID.Text) && this.txtMbrID.Text.Trim().Length > 0)
                query.And(Exp.Eq("m.MemberNumber", this.txtMbrID.Text.Trim()));
            if (!string.IsNullOrEmpty(this.txtMbrName.Text) && this.txtMbrName.Text.Trim().Length > 0)
                query.And(Exp.Like("m.Name", "%" + this.txtMbrName.Text.Trim() + "%"));
            DataSet ds = query.DataSet();

            string fileName = DownloadUtil.DownloadXls("Member_Account_Balance_" + DateTime.Now.ToString("yyMMdd") + ".xls", "RPT_MBR_ACC_",
                new List<DownloadFormat>()
                    {
                        new DownloadFormat(DataType.NumberText, "会员号", "MemberNumber"),
                        new DownloadFormat(DataType.Text, "会员姓名", "MemberName"),
                        new DownloadFormat(DataType.Number, "期初余额", "BeginAmt"),
                        new DownloadFormat(DataType.Number, "期末余额", "EndAmt")
                    }, ds);
            this.frameDownload.Attributes["src"] = fileName;
        }
    }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         this.cmdReturn1["Return"].NavigateUrl = this.cmdReturn2["Return"].NavigateUrl = WebUtil.Param("return");
         this.txtOrderNumber.Value = this.OrderNumber;
         using (ISession session = new Session())
         {
             DataSet ds = session.CreateObjectQuery(@"
     select rtl.LineNumber as LineNumber,s.BarCode as BarCode,m.ItemCode as ItemCode,m.ItemName as ItemName
     ,s.ColorCode as ColorCode,color.ColorText as ColorText,s.SizeCode as SizeCode
     ,st.Name as SaleType,snl.Price as Price,snl.Quantity as ShippingQty,rtl.Quantity as ReturnQty
     from ReturnHead rth
     inner join CRMSNLine snl on rth.RefOrderID=snl.SNID
     inner join ItemSpec s on s.SKUID=snl.SKUID
     inner join ItemMaster m on m.ItemID=s.ItemID
     inner join ReturnLine rtl on rtl.OrderNumber=rth.OrderNumber and rtl.RefOrderLineID=snl.ID
     left join ItemColor color on color.ColorCode=s.ColorCode
     left join CRMSaleType st on st.ID=snl.SellType
     where rth.OrderNumber=?ordNum
     order by rtl.LineNumber")
                 .Attach(typeof(ReturnHead)).Attach(typeof(ReturnLine))
                 .Attach(typeof(ItemSpec)).Attach(typeof(ItemMaster)).Attach(typeof(ItemColor))
                 .Attach(typeof(CRMSNLine)).Attach(typeof(CRMSaleType))
                 .SetValue("?ordNum", this.OrderNumber, "rtl.OrderNumber")
                 .DataSet();
             this.repeatControl.DataSource = ds;
             this.repeatControl.DataBind();
         }
     }
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         this.cmdReturn1["Return"].NavigateUrl = this.cmdReturn2["Return"].NavigateUrl = WebUtil.Param("return");
         this.txtOrderNumber.Value = this.OrderNumber;
         using (ISession session = new Session())
         {
             DataSet ds = session.CreateObjectQuery(@"
     select l.LineNumber as LineNumber,l.RefOrderLine as RefOrderLine
     ,s.BarCode as BarCode,m.ItemCode as ItemCode,m.ItemName as ItemName,s.ColorCode as ColorCode,color.ColorText as ColorText,s.SizeCode as SizeCode
     ,l.RCVTotalQty as RCVTotalQty,l.QualifiedQty as QualifiedQty,l.Price as Price,0 as TotalAmt,l.TaxValue as TaxValue,0 as TaxAmt,0 as CostAmt
     from RCVLine l
     inner join ItemSpec s on l.SKUID=s.SKUID
     inner join ItemMaster m on m.ItemID=s.ItemID
     left join ItemColor color on color.ColorCode=s.ColorCode
     where l.OrderNumber=?ordNum
     order by l.LineNumber")
                 .Attach(typeof(RCVLine)).Attach(typeof(ItemSpec)).Attach(typeof(ItemMaster)).Attach(typeof(ItemColor))
                 .SetValue("?ordNum", this.OrderNumber, "l.OrderNumber")
                 .DataSet();
             foreach (DataRow row in ds.Tables[0].Rows)
             {
                 decimal qty = Cast.Decimal(row["QualifiedQty"]), price = Cast.Decimal(row["Price"]), tax = Cast.Decimal(row["TaxValue"]);
                 row["TotalAmt"] = qty * price;
                 row["CostAmt"] = qty * price / (1 + tax);
                 row["TaxAmt"] = qty * price * (tax / (1 + tax));
                 this.TotalCost += qty * price / (1 + tax);
                 this.TotalAmt += qty * price;
                 this.TotalRcvQty += qty;
             }
             this.TotalTax = this.TotalAmt - this.TotalCost;
             this.lblTotalAmt.Text = RenderUtil.FormatNumber(this.TotalAmt, "#,##0.#0", "0.00");
             this.lblTotalTax.Text = RenderUtil.FormatNumber(this.TotalTax, "#,##0.#0", "0.00");
             this.lblTotalCost.Text = RenderUtil.FormatNumber(this.TotalCost, "#,##0.#0", "0.00");
             this.lblRcvQtyTotal.Text = RenderUtil.FormatNumber(this.TotalRcvQty, "#,##0.##", "0");
             this.repeatControl.DataSource = ds;
             this.repeatControl.DataBind();
         }
     }
 }

    protected void cmdAddUser_Click(object sender, EventArgs e)
    {
        string[] userIds = this.txtUserToAdd.Value.Trim().Trim(';').Split(';');
        if (userIds != null && userIds.Length > 0)
        {
            using (ISession session = new Session())
            {
                DataSet ds = session.CreateObjectQuery("select max(UserSequnce) as UserSequnce,max(StepIndex) as StepIndex from OrderApproveDef where OrderTypeCode=?typeCode")
                    .Attach(typeof(OrderApproveDef))
                    .SetValue("?typeCode", this.OrderTypeCode, "OrderTypeCode")
                    .DataSet();
                int maxSeq = ds.Tables[0].Rows.Count <= 0 ? 0 : Magic.Framework.Utils.Cast.Int(ds.Tables[0].Rows[0]["UserSequnce"], 0);
                int maxIndex = ds.Tables[0].Rows.Count <= 0 ? 0 : Magic.Framework.Utils.Cast.Int(ds.Tables[0].Rows[0]["StepIndex"], 0);

                try
                {
                    session.BeginTransaction();
                    for (int i = 0; i < userIds.Length; i++)
                    {
                        int userId = Magic.Framework.Utils.Cast.Int(userIds[i], 0);
                        if (userId <= 0) continue;
                        OrderApproveDef def = new OrderApproveDef();
                        def.OrderTypeCode = this.OrderTypeCode;
                        def.UserID = userId;
                        def.UserSequnce = (++maxSeq);
                        def.StepIndex = (++maxIndex);
                        def.Create(session);
                    }
                    session.Commit();
                    WebUtil.ShowMsg(this, "ǩ���û���ӳɹ�");
                    this.QueryAndBindData(session);
                }
                catch (Exception er)
                {
                    session.Rollback();
                    WebUtil.ShowError(this, er);
                }
            }
        }
    }

//.........这里部分代码省略.........
            }
            #endregion
        }
        else if (e.CommandName == "Download")
        {
            #region ����
            DataSet ds = null;
            IDictionary<string, string> dic = new Dictionary<string, string>();
            #region ��ʼ��dic
            dic["$LogisName$"] = "";
            dic["$LogisAddress$"] = "";
            dic["$LogisContact$"] = "";
            dic["$LogisPhone$"] = "";
            dic["$LogisZipCode$"] = "";
            dic["$LogisFax$"] = "";
            dic["$ICNumber$"] = "";
            dic["$ICUser$"] = "";
            dic["$AgentAmt$"] = "";
            dic["$Note$"] = "";
            dic["$Status$"] = "";
            dic["$ICTime$"] = "";
            dic["$ICBoxCount$"] = "";
            #endregion
            IList<DownloadFormat> format = new List<DownloadFormat>() {
                new DownloadFormat(DataType.NumberText, "", "OrderNumber"),
                new DownloadFormat(DataType.NumberText, "", "ShippingNumber"),
                new DownloadFormat(DataType.NumberText, "", "SaleOrderNumber"),
                new DownloadFormat(DataType.NumberText, "", "InvoiceNumber"),
                new DownloadFormat(DataType.Number, "", "PackageWeight"),
                new DownloadFormat(DataType.Number, "", "PackageCount"),
                new DownloadFormat(DataType.Text, "", "Contact"),
                new DownloadFormat(DataType.Text, "", "Province", "City"), //ʡ��2���ش�����
                new DownloadFormat(DataType.NumberText, "", "PostCode"),
                new DownloadFormat(DataType.Text, "", "Address"),
                new DownloadFormat(DataType.NumberText, "", "Phone"),
                new DownloadFormat(DataType.NumberText, "", "Mobile"),
                new DownloadFormat(DataType.Number, "", "AgentAmt"),
                new DownloadFormat(DataType.Text, "", "Remark")
            };
            using (ISession session = new Session())
            {
                ds = session.CreateObjectQuery(@"
        SELECT
        A.OrderNumber AS OrderNumber,A.ShippingNumber as ShippingNumber,A.SaleOrderNumber AS SaleOrderNumber
        ,A.InvoiceNumber as InvoiceNumber,A.PackageWeight as PackageWeight,A.PackageCount as PackageCount
        ,A.Contact as Contact,A.Province as Province,A.City as City,A.Address as Address,A.Mobile as Mobile,A.Phone as Phone
        ,A.AgentAmt as AgentAmt,A.Remark as Remark,A.PostCode as PostCode
        FROM ICLine L
        inner JOIN CRMSN A ON L.RefOrderNumber=A.OrderNumber
        LEFT JOIN Member E ON A.MemberID= E.MemberID
        order by L.LineNumber")
                .Attach(typeof(Magic.ERP.Orders.ICLine))
                .Attach(typeof(Magic.ERP.Orders.CRMSN))
                .Attach(typeof(Magic.Basis.Member))
                .And(Magic.Framework.ORM.Query.Exp.Eq("L.OrderNumber", this.OrderNumber))
                .DataSet();

                decimal totalAmt = 0M;
                foreach (DataRow row in ds.Tables[0].Rows)
                    totalAmt += Cast.Decimal("AgentAmt");

                ICHead head = ICHead.Retrieve(session, this.OrderNumber);
                if (head == null)
                {
                    WebUtil.ShowError(this, "���ӵ�" + this.OrderNumber + "������");
                    return;
                }
                dic["$ICNumber$"] = "'" + head.OrderNumber;
                dic["$AgentAmt$"] = totalAmt.ToString("#0.#0");
                dic["$Note$"] = "'" + head.Note;
                dic["$Status$"] = ERPUtil.StatusText(session, CRMSN.ORDER_TYPE_CODE_SD, head.Status);
                dic["$ICTime$"] = "'" + head.CreateTime.ToString("yyyy-MM-dd");
                dic["$ICBoxCount$"] = "'" + head.TotalPackageCount(session).ToString();
                Logistics logis = Logistics.Retrieve(session, head.LogisticCompID);
                if (logis != null)
                {
                    dic["$LogisName$"] = logis.ShortName;
                    dic["$LogisAddress$"] = logis.Address;
                    dic["$LogisContact$"] = logis.Contact;
                    dic["$LogisPhone$"] = "'" + logis.Phone;
                    dic["$LogisZipCode$"] = "'" + logis.ZipCode;
                    dic["$LogisFax$"] = "'" + logis.Fax;
                }
                if (head.CreateUser > 0)
                {
                    Magic.Sys.User user = Magic.Sys.User.Retrieve(session, head.CreateUser);
                    if (user != null) dic["$ICUser$"] = user.FullName;
                }
            }
            if (ds == null)
            {
                WebUtil.ShowError(this, "û���������ػ������س�����");
                return;
            }

            string fileName = DownloadUtil.DownloadXls("IC_" + this.OrderNumber + ".xls", "IC", Server.MapPath("/Template/IC_Download.xls"), dic, 7, format, ds);
            this.frameDownload.Attributes["src"] = fileName;
            #endregion
        }
    }

    /// <summary>
    /// Get query conditions from page , and query data from db, then bind the query result to Repeater
    /// </summary>
    private void QueryAndBindData(Repeater rpt, MagicPager pager, bool isGroup, int pageIndex, int pageSize, bool fetchRecordCount)
    {
        string tmplCode = txtTmplCode.Text.Trim();
        DataTable dtMsgBuscriber = null;

        int recordCount = 0;
        string oql = "";
        if (!isGroup)
            oql = @"select s.SubscriberId as SubscriberId,
                                      s.TmplCode as TmplCode,
                                       s.UserId as UserId,
                                        u.UserName as UserName,
                                        u.FullName as FullName,
                                        s.SubscribeTime as SubscribeTime
                            from MsgSubscriber s inner join User u on s.UserId=u.UserId and s.IsGroup=0";
        else
            oql = @" select s.SubscriberId as SubscriberId,
                                    s.TmplCode as TmplCode,
                                       s.UserId as UserId,
                                        s.GroupId as GroupId,
                                        g.Name as GroupName,
                                        g.Description as Description,
                                s.SubscribeTime as SubscribeTime
                            from MsgSubscriber s inner join UserGroup g on s.GroupId=g.GroupId and s.IsGroup=1";

        using (Session session = new Session())
        {
            ObjectQuery query = session.CreateObjectQuery(oql);

            query.Attach(typeof(MsgSubscriber));
            if (isGroup)
                query.Attach(typeof(UserGroup));
            else
                query.Attach(typeof(User));

            query.And(Exp.Eq("s.TmplCode", tmplCode));

            query.SetPage(pageIndex, pageSize);

            dtMsgBuscriber = query.DataSet().Tables[0];

            if (fetchRecordCount)
                recordCount = query.Count();
        }

        rpt.DataSource = dtMsgBuscriber;
        rpt.DataBind();

        if (fetchRecordCount)
        {
           pager.RecordCount = recordCount;
        }

        WebUtil.SetMagicPager(pager, pageSize, pageIndex);
    }

    protected void MagicItemCommand(object sender, MagicItemEventArgs e)
    {
        try
        {
            if (e.CommandName == "Download")
            {
                #region ����
                DataSet ds = null;
                INVCheckHead head = null;
                using (ISession session = new Session())
                {
                    head = INVCheckHead.Retrieve(session, this.OrderNumber);
                    Magic.Framework.ORM.Query.ObjectQuery query = session.CreateObjectQuery(string.Format(@"
        select l.LineNumber as LineNumber,l.AreaCode as AreaCode,l.SectionCode as SectionCode
        ,s.BarCode as SKU,m.ItemCode as ItemCode
        ,s.ColorCode as ColorCode,color.ColorText as ColorText,s.SizeCode as SizeCode
        ,{0} as BeforeQty,l.CurrentQty as CurrentQty,m.ItemName as ItemName
        from INVCheckLine l
        inner join ItemSpec s on l.SKUID=s.SKUID
        inner join ItemMaster m on m.ItemID=s.ItemID
        left join ItemColor color on color.ColorCode=s.ColorCode
        where l.OrderNumber=?ordNum {1}
        order by l.LineNumber"
                            , head.CheckType == INVCheckType.Implicit && head.Status == INVCheckStatus.Confirm ? "'-'" : "l.BeforeQty"
                            , this.drpViewType.SelectedValue == "2" ? "and l.BeforeQty<>l.CurrentQty" : ""))
                        .Attach(typeof(INVCheckLine)).Attach(typeof(ItemSpec)).Attach(typeof(ItemMaster))
                        .Attach(typeof(ItemColor)).Attach(typeof(ItemSize))
                        .SetValue("?ordNum", this.OrderNumber, "l.OrderNumber");
                    if (this.drpArea.SelectedValue.Trim().Length > 0) query.And(Exp.Eq("l.AreaCode", this.drpArea.SelectedValue.Trim()));
                    if (this.txtSKU.Text.Trim().Length > 0) query.And(Exp.Like("s.BarCode", "%" + this.txtSKU.Text.Trim() + "%"));
                    if (this.txtItemCode.Text.Trim().Length > 0) query.And(Exp.Like("m.ItemCode", "%" + this.txtItemCode.Text.Trim() + "%"));
                    if (this.txtName.Text.Trim().Length > 0) query.And(Exp.Like("m.ItemName", "%" + this.txtName.Text.Trim() + "%"));
                    if (this.txtColor.Text.Trim().Length > 0) query.And(Exp.Like("s.ColorCode", "%" + this.txtColor.Text.Trim().ToUpper() + "%"));
                    if (this.txtSize.Text.Trim().Length > 0) query.And(Exp.Like("s.SizeCode", "%" + this.txtSize.Text.Trim().ToUpper() + "%"));
                    ds = query.DataSet();
                }
                if (ds == null)
                {
                    WebUtil.ShowError(this, "û���������ػ������س�����");
                    return;
                }

                string fileName = DownloadUtil.DownloadXls("StockCheck_" + DateTime.Now.ToString("yyMMdd") + ".xls", "CK",
                    new List<DownloadFormat>()
                    {
                        new DownloadFormat(DataType.NumberText, "�к�", "LineNumber"),
                        new DownloadFormat(DataType.Text, "��λ", "AreaCode"),
                        new DownloadFormat(DataType.NumberText, "����", "SectionCode"),
                        new DownloadFormat(DataType.NumberText, "SKU", "SKU"),
                        new DownloadFormat(DataType.NumberText, "����", "ItemCode"),
                        new DownloadFormat(DataType.Text, "��ɫ", "ColorCode", "ColorText"),
                        new DownloadFormat(DataType.Text, "����", "SizeCode"),
                        new DownloadFormat((head.CheckType == INVCheckType.Implicit && head.Status == INVCheckStatus.Confirm ? DataType.Text : DataType.Number), "ϵͳ����", "BeforeQty"),
                        new DownloadFormat(DataType.Number, "�̵�����", "CurrentQty"),
                        new DownloadFormat(DataType.Text, "��Ʒ����", "ItemName")
                    }, ds);
                this.frameDownload.Attributes["src"] = fileName;
                #endregion
            }
            else if (e.CommandName == "Save")
            {
                #region ����
                using (ISession session = new Session())
                {
                    try
                    {
                        session.BeginTransaction();

                        foreach (RepeaterItem item in this.repeatControl.Items)
                        {
                            TextBox text = item.FindControl("txtQty") as TextBox;
                            if (text == null) continue;
                            string lineNum = text.Attributes["lineNumber"];
                            if (string.IsNullOrEmpty(lineNum) || lineNum.Trim().Length <= 0) continue;
                            INVCheckLine line = INVCheckLine.Retrieve(session, this.OrderNumber, lineNum);
                            if (line != null)
                            {
                                line.CurrentQty = Cast.Decimal(text.Text, line.CurrentQty);
                                line.Update(session, "CurrentQty");
                            }
                        }

                        session.Commit();
                        WebUtil.ShowMsg(this, "����ɹ�");
                    }
                    catch (Exception er)
                    {
                        session.Rollback();
                        WebUtil.ShowError(this, er);
                    }
                }
                #endregion
            }
            else if (e.CommandName == "Release")
            {
                #region ����
                using (ISession session = new Session())
                {
                    try
                    {
//.........这里部分代码省略.........

    private static SimpleJson MsgSubscriberUserGroupTree()
    {
        string tmplCode = WebUtil.Param("msgtmpl");

        try
        {
            using (Session session = new Session())
            {
                MsgTemplate msgtmpl = MsgTemplate.Retrieve(session, tmplCode);
                if (msgtmpl == null)
                    throw new Exception(string.Format("代码{0} 的模板找不到", tmplCode));

                DataTable dtGroupId = session.CreateObjectQuery("select s.GroupId as GroupId from MsgSubscriber s")
                    .Attach(typeof(MsgSubscriber))
                    .Where(Exp.Eq("s.TmplCode", tmplCode))
                    .DataSet().Tables[0];

                UserGroup group = UserGroup.Root;
                string id = "userGroup";

                StringBuilder builder = new StringBuilder();
                builder.Append(@"<ul id=""" + id + @""">");
                if (group != null)
                {
                    group.LoadSubTree(session);
                    foreach (UserGroup child in group.Children)
                    {
                        BuildMsgSubscriberUserGroupTree(session, child, builder, dtGroupId);
                    }
                }
                builder.Append("</ul>");

                return new SimpleJson(3)
                    .Add("html", builder.ToString())
                    .Add("id", id);
            }
        }
        catch (Exception e)
        {
            log.Error("MsgSubscriberUserGroupTree", e);
            return new SimpleJson().HandleError(e);
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.cmdReturn1.Visible = false;
            this.cmdReturn2.Visible = false;

            this.txtDateFrom.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
            this.txtDateTo.Text = DateTime.Now.ToString("yyyy-MM-dd");

            this.drpPeriod.Items.Clear();
            this.drpPeriod.Items.Add(new ListItem("　", "0"));
            this.drpFlush.Items.Clear();
            this.drpFlush.Items.Add(new ListItem("　", "0"));
            this.drpPayment.Items.Clear();
            this.drpPayment.Items.Add(new ListItem("　", "0"));
            using (ISession session = new Session())
            {
                IList<INVPeriod> periods = INVPeriod.ClosedPeriods(session);
                foreach (INVPeriod p in periods)
                    this.drpPeriod.Items.Add(new ListItem(p.PeriodCode, p.PeriodID.ToString()));
                if (this.drpPeriod.Items.Count > 1)
                    this.drpPeriod.SelectedIndex = 1;
                IList<FlushType> flush = FlushType.EffectiveList(session);
                foreach (FlushType f in flush)
                    this.drpFlush.Items.Add(new ListItem(f.Name, f.ID.ToString()));

                //直接sql取了
                IList<PaymentMethod> payment = session.CreateObjectQuery(@"
        Select * From s_payment_METHOD Where Id In(
        Select Distinct pay_method From mbr_money_history
        )
        order by name")
                    .List<PaymentMethod>();
                foreach (PaymentMethod p in payment)
                    this.drpPayment.Items.Add(new ListItem(p.Name, p.ID.ToString()));

                string mode = WebUtil.Param("mode");
                if (mode.Trim().ToLower() == "fix")
                {
                    //从帐户变动汇总导航过来
                    this.cmdReturn1.Visible = true;
                    this.cmdReturn2.Visible = true;
                    this.cmdReturn1["Return"].NavigateUrl = this.cmdReturn2["Return"].NavigateUrl = WebUtil.Param("return");

                    this.drpPeriod.SelectedValue = WebUtil.ParamInt("pd", 0).ToString();
                    this.drpFlush.SelectedValue = WebUtil.ParamInt("flush", 0).ToString();
                    this.drpPayment.SelectedValue = WebUtil.ParamInt("payment", 0).ToString();
                    DateTime dt;
                    dt = Cast.DateTime(WebUtil.Param("df"), new DateTime(1900, 1, 1));
                    if (dt > new DateTime(1900, 1, 1))
                        this.txtDateFrom.Text = dt.ToString("yyyy-MM-dd");
                    dt = Cast.DateTime(WebUtil.Param("dt"), new DateTime(1900, 1, 1));
                    if (dt > new DateTime(1900, 1, 1))
                        this.txtDateTo.Text = dt.ToString("yyyy-MM-dd");
                    this.txtMbrID.Text = WebUtil.Param("mbr");
                }

                this.QueryAndBindData(session, 1, this.magicPagerMain.PageSize, true);
            }
        }
        else this.frameDownload.Attributes["src"] = "about:blank;";
    }