C# ISession.CreateObjectQuery方法代码示例

 public WHRepository(ISession session)
 {
     DataTable table;
     _ds = new DataSet("Location");
     DataSet p = session.CreateObjectQuery("select LocationCode as LocationCode,CompanyID as CompanyID,Name as Name,Status as Status,Text as Text,Address as Address,ZipCode as ZipCode,Contact as Contact,Phone as Phone,FaxNumber as FaxNumber from WHLocation order by LocationCode")
         .Attach(typeof(WHLocation)).Where(Exp.In("Status", WHStatus.Disable, WHStatus.Enable))
         .DataSet();
     table = p.Tables[0];
     table.TableName = "l";
     p.Tables.Clear();
     _ds.Tables.Add(table);
     table.Constraints.Add("PK_l", table.Columns["LocationCode"], false);
     DataSet c = session.CreateObjectQuery("select AreaCode as AreaCode,LocationCode as LocationCode,ParentArea as ParentArea,Name as Name,Text as Text,Status as Status,AreaCapacity as AreaCapacity,HasSection as HasSection,IsTransArea as IsTransArea,IsReservedArea as IsReservedArea,AllowDelete as AllowDelete,AllowChild as AllowChild,UseFixCost as UseFixCost,CostFixValue as CostFixValue,CostTransRate as CostTransRate,FixedComsumeValue as FixedComsumeValue,IsQC as IsQC,IsNonFormal as IsNonFormal,IsScrap as IsScrap from WHArea")
         .Attach(typeof(WHArea)).Where(Exp.In("Status", WHStatus.Disable, WHStatus.Enable) & Exp.Eq("IsReservedArea", false))
         .DataSet();
     table = c.Tables[0];
     table.TableName = "a";
     c.Tables.Clear();
     _ds.Tables.Add(table);
     table.Constraints.Add("PK_a", table.Columns["AreaCode"], false);
     DataSet d = session.CreateObjectQuery("select AreaCode as AreaCode,SectionCode as SectionCode,Status as Status,SectionCapacity as SectionCapacity,Text as Text from WHSection")
         .Attach(typeof(WHSection)).Where(Exp.In("Status", WHStatus.Disable, WHStatus.Enable))
         .DataSet();
     table = d.Tables[0];
     table.TableName = "s";
     d.Tables.Clear();
     _ds.Tables.Add(table);
     //table.PrimaryKey = new DataColumn[] { table.Columns["AreaCode"], table.Columns["SectionCode"] };
 }

 public RegionRepository(ISession session)
 {
     DataTable table;
     _ds = new DataSet("Province");
     DataSet p = session.CreateObjectQuery("select PRV_ID as Province_ID,PRV_CODE as \"Code\",PRV_NAME as \"Name\",PRV_ALIAS as \"Alias\" from Province")
         .Attach(typeof(Province))
         .DataSet();
     table = p.Tables[0];
     table.TableName = "p";
     table.Constraints.Add("PK_p", table.Columns["Province_ID"], true);
     p.Tables.Clear();
     _ds.Tables.Add(table);
     DataSet c = session.CreateObjectQuery("select PRV_ID as Province_ID,CITY_ID as City_ID,CITY_CODE as City_Code,CITY_NAME as \"Name\" from City")
         .Attach(typeof(City))
         .DataSet();
     table = c.Tables[0];
     table.TableName = "c";
     table.Constraints.Add("PK_c", table.Columns["City_ID"], true);
     c.Tables.Clear();
     _ds.Tables.Add(table);
     DataSet d = session.CreateObjectQuery("select CITY_ID as City_ID,DST_ID as District_ID,DST_NAME as \"Name\",DST_ZIPCODE as Zip_Code,DST_SHIP_TO as Door2Door from District")
         .Attach(typeof(District))
         .DataSet();
     table = d.Tables[0];
     table.TableName = "d";
     table.Constraints.Add("PK_d", table.Columns["District_ID"], true);
     d.Tables.Clear();
     _ds.Tables.Add(table);
 }

 void InitdrpTax(ISession session)
 {
     ObjectQuery query = session.CreateObjectQuery(@"select t.TaxID as TaxID,t.TaxText as TaxText,t.TaxValue as TaxValue,t.TaxIndex as TaxIndex from TaxDef t").Attach(typeof(TaxDef));
     this.drpTax.DataTextField = "TaxValue";
     this.drpTax.DataValueField = "TaxID";
     this.drpTax.DataSource = query.DataSet();
     this.drpTax.DataBind();
 }

        public static DataSet Query(ISession session, bool requireCount, out int count, string fromLocation, string sku, string itemCode, string itemName, string color, string size, string area, string section, int pageIndex, int pageSize)
        {
            ObjectQuery query = session.CreateObjectQuery(@"
            select sku.BarCode as BarCode,i.ItemCode as ItemCode,i.ItemName as ItemName
            ,sku.ColorCode as ColorCode,color.ColorText as ColorText,sku.SizeCode as SizeCode
            ,sto.AreaCode as AreaCode,sto.SectionCode as SectionCode,sto.StockQty as StockQty,sto.FrozenQty as FrozenQty
            ,0 as TempQty,0 as TransferQty
            ,sto.StockDetailID as StockDetailID
            from StockDetail sto
            inner join ItemSpec sku on sku.SKUID=sto.SKUID
            inner join ItemMaster i on i.ItemID=sku.ItemID
            left join ItemColor color on color.ColorCode=sku.ColorCode
            where sto.StockQty>0 and sto.LocationCode=?loc
            order by i.ItemCode,sku.ColorCode,sku.SizeCode,sto.AreaCode,sto.SectionCode")
                .Attach(typeof(StockDetail))
                .Attach(typeof(ItemSpec)).Attach(typeof(ItemMaster)).Attach(typeof(ItemColor))
                .SetValue("?loc", fromLocation, "sto.LocationCode")
                .SetPage(pageIndex, pageSize);

            if (!string.IsNullOrEmpty(area))
                query.And(Exp.Eq("sto.AreaCode", area));
            if (section.Trim().Length > 0) query.And(Exp.Like("sto.SectionCode", "%" + section.Trim().ToUpper() + "%"));
            if (sku.Trim().Length > 0) query.And(Exp.Like("sku.BarCode", "%" + sku.Trim().ToUpper() + "%"));
            if (itemCode.Trim().Length > 0) query.And(Exp.Like("i.ItemCode", "%" + itemCode.Trim().ToUpper() + "%"));
            if (itemName.Trim().Length > 0) query.And(Exp.Like("i.ItemName", "%" + itemName.Trim() + "%"));
            if (color.Trim().Length > 0) query.And(Exp.Like("sku.ColorCode", "%" + color.Trim().ToUpper() + "%"));
            if (size.Trim().Length > 0) query.And(Exp.Like("sku.SizeCode", "%" + size.Trim().ToUpper() + "%"));

            //������ƿ�����
            DataSet ds = query.DataSet();
            if (ds.Tables[0].Rows.Count > 0)
            {
                IList<int> stoids = new List<int>(ds.Tables[0].Rows.Count);
                foreach (DataRow row in ds.Tables[0].Rows)
                    stoids.Add(Cast.Int(row["StockDetailID"]));
                DataSet tempDs = session.CreateObjectQuery(@"
            select l.FromStockID as FromStockID,sum(l.MoveQty) as Qty
            from WHTransferHead h
            inner join WHTransferLine l on h.OrderNumber=l.OrderNumber
            group by l.FromStockID")
                    .Attach(typeof(WHTransferHead)).Attach(typeof(WHTransferLine))
                    .Where(Exp.In("h.Status", WHTransferStatus.New, WHTransferStatus.Release, WHTransferStatus.Open) & Exp.In("l.FromStockID", stoids))
                    .DataSet();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    DataRow[] rows = tempDs.Tables[0].Select("FromStockID=" + Cast.Int(row["StockDetailID"]).ToString());
                    if (rows != null && rows.Length > 0)
                        row["TempQty"] = Cast.Decimal(rows[0]["Qty"]);
                    row["TransferQty"] = Cast.Decimal(row["StockQty"]) - Cast.Decimal(row["FrozenQty"]) - Cast.Decimal(row["TempQty"]);
                }
            }

            count = 0;
            if (requireCount) count = query.Count();

            return ds;
        }

 private void QueryAndBindData(ISession session)
 {
     this.repeatControl.DataSource = session.CreateObjectQuery(@"
     select t.OrderTypeCode as OrderTypeCode,t.TypeText as TypeText,t.SupportApprove as SupportApprove,t.NeedApprove as NeedApprove
     ,t.ViewURL as ViewURL,r.RuleDefineText as RuleDefineText
     from OrderTypeDef t
     left join OrderRuleDef r on t.RuleDefineID=r.RuleDefineID
     order by t.OrderTypeCode")
         .Attach(typeof(OrderTypeDef)).Attach(typeof(OrderRuleDef))
         .DataSet();
     this.repeatControl.DataBind();
 }

 public static IList<POLine> ReceivableLines(ISession session, string orderNumber)
 {
     return session.CreateObjectQuery(@"
     select 1
     from POLine l
     where l.OrderNumber=?orderNumber and l.LineStatus=?lineStatus
     and l.PurchaseQty-l.ReceiveQty-l.UnfinishedReceiveQty>0
     order by l.LineNumber")
         .Attach(typeof(POLine))
         .SetValue("?orderNumber", orderNumber, "l.OrderNumber")
         .SetValue("?lineStatus", POLineStatus.Open, "l.LineStatus")
         .List<POLine>();
 }

        public static SimpleJson GetWHInfo(ISession session, string area, string section)
        {
            if (string.IsNullOrEmpty(area) || area.Trim().Length <= 0)
                return new SimpleJson().HandleError("��û��ѡ���λ");

            string a = area.Trim().ToUpper();
            string sec = string.IsNullOrEmpty(section) ? "" : section.Trim().ToUpper();
            SimpleJson json = new SimpleJson().Add("area", area).Add("section", sec);

            if (sec.Length <= 0)
            {
                //���ؿ�λ��Ϣ
                WHArea whArea = WHArea.Retrieve(session, a);
                if (whArea == null) return json.HandleError("��λ" + a + "������");
                json.Add("capacity", Cast.Int(whArea.AreaCapacity));
                int stoQty = Cast.Int(session.CreateObjectQuery(@"select sum(StockQty) from StockDetail where AreaCode=?area group by AreaCode")
                    .Attach(typeof(StockDetail))
                    .SetValue("?area", a, "AreaCode")
                    .Scalar());
                json.Add("stored", stoQty);
            }
            else
            {
                //���ػ�����Ϣ
                WHSection whSection = WHSection.Retrieve(session, a, sec);
                if (whSection == null) return json.HandleError("��λ" + a + "�в����ڻ��ܺ�" + sec);
                json.Add("capacity", Cast.Int(whSection.SectionCapacity));
                int stoQty = Cast.Int(session.CreateObjectQuery(@"
            select sum(StockQty) from StockDetail
            where AreaCode=?area and SectionCode=?section
            group by AreaCode")
                    .Attach(typeof(StockDetail))
                    .SetValue("?area", a, "AreaCode")
                    .SetValue("?section", sec, "SectionCode")
                    .Scalar());
                json.Add("stored", stoQty);
            }
            return json;
        }

 public static IList<POLine> PipelineInvQuery(ISession session, int skuId)
 {
     return session.CreateObjectQuery(@"
     select 1
     from POHead h
     inner join POLine l on h.OrderNumber=l.OrderNumber
     where h.Status=?release and l.LineStatus=?open and l.SKUID=?skuid
     order by l.PlanDate desc")
         .Attach(typeof(POHead)).Attach(typeof(POLine))
         .SetValue("?release", POStatus.Release, "h.Status")
         .SetValue("?open", POLineStatus.Open, "l.LineStatus")
         .SetValue("?skuid", skuId, "l.SKUID")
         .List<POLine>();
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="session"></param>
 /// <param name="userId"></param>
 /// <param name="sendTimeBegin"></param>
 /// <param name="sendTimeEnd"></param>
 /// <returns></returns>
 public static DataTable QueryUnreadMessageByUser(ISession session, int userId, DateTime sendTimeBegin, DateTime sendTimeEnd)
 {
     string oql = "select r.SendTime as SendTime, r.ReceiverId as ReceiverId, m.MessageId as MessageId, m.Title as Title,m.ViewEntry as ViewEntry, t.TypeName as TypeName  from Message m left outer join MsgReceiver r on m.MessageId=r.MessageId inner join MessageType as t on t.MsgTypeId=m.MsgTypeId ";
     return
         session.CreateObjectQuery(oql)
          .Attach(typeof(Message))
          .Attach(typeof(MsgReceiver))
          .Attach(typeof(MessageType))
          .Where(Exp.Eq("r.UserId", userId))
          .And(Exp.In("m.Status",MessageStatus.Sent))
           .And(Exp.Eq("r.ReadStatus", MessageReadStatus.Unread))
          .And(Exp.Ge("r.SendTime", sendTimeBegin))
          .And(Exp.Le("r.SendTime", sendTimeEnd))
          .OrderBy(new OrderBy("r.SendTime", Order.Desc))
          .DataSet().Tables[0];
 }

 private void BindArea(ISession session)
 {
     IList<WHArea> areas = session.CreateObjectQuery(@"
     select 1 from WHArea where AreaCode not in(
     select AreaCode from WHAreaCfg where AreaCfgCode=?transTypeCode
     )
     and Status=?status and IsReservedArea=?isReserved and IsTransArea=?transArea
     order by AreaCode")
         .Attach(typeof(WHArea)).Attach(typeof(WHAreaCfg))
         .SetValue("?transTypeCode", this.TransTypeCode, EntityManager.GetPropMapping(typeof(WHAreaCfg), "AreaCfgCode").DbTypeInfo)
         .SetValue("?status", WHStatus.Enable, "Status")
         .SetValue("?isReserved", false, "IsReservedArea")
         .SetValue("?transArea", true, "IsTransArea")
         .List<WHArea>();
     this.drpArea.Items.Clear();
     foreach (WHArea area in areas)
         this.drpArea.Items.Add(new ListItem(area.AreaCode, area.AreaCode));
 }

    protected void ddlArea_SelectedIndexChanged(object sender, EventArgs e)
    {
        DropDownList ddlArea = sender as DropDownList;
        DropDownList ddlSection = ddlArea.Parent.FindControl("ddlSection") as DropDownList;

        using (_session = new Session())
        {
            ObjectQuery query = _session.CreateObjectQuery(@"
        SELECT A.SectionCode AS SectionCode,A.Text AS Text
        FROM WHSection A WHERE A.AreaCode=?
        ")
        .Attach(typeof(Magic.ERP.Core.WHSection))
        .SetValue(0, ddlArea.SelectedValue, "A.AreaCode");

            DataTable dt = query.DataSet().Tables[0];
            ddlSection.DataSource = dt;
            ddlSection.DataBind();
            ddlSection.Items.Insert(0, new ListItem("", ""));
        }
    }

    void InitDrpDown(ISession session)
    {
        this.drpArea.DataTextField = "AreaCode";
        this.drpArea.DataValueField = "AreaCode";
        this.drpArea.DataSource = session.CreateEntityQuery<WHArea>()
            .Where(Exp.Eq("Status", WHStatus.Enable) & Exp.Eq("IsReservedArea", false) & Exp.Eq("IsTransArea", true))
            .List<WHArea>();
        this.drpArea.DataBind();
        this.drpArea.Items.Add(new ListItem("　", ""));
        this.drpArea.Items[this.drpArea.Items.Count - 1].Selected = true;

        //TransTypeDef
        ObjectQuery query = session.CreateObjectQuery(@"select t.TransDefText as TransDefText,t.TransTypeCode as TransTypeCode from TransTypeDef t ").Attach(typeof(TransTypeDef));
        this.drpTransTypeDef.DataTextField = "TransDefText";
        this.drpTransTypeDef.DataValueField = "TransTypeCode";
        this.drpTransTypeDef.DataSource = query.DataSet();
        this.drpTransTypeDef.DataBind();
        this.drpTransTypeDef.Items.Add(new ListItem("　", ""));
        this.drpTransTypeDef.Items[this.drpTransTypeDef.Items.Count - 1].Selected = true;
    }

    void QueryAndBindData(ISession session, int pageIndex, int pageSize, bool fetchRecordCount)
    {
        string itemCode = this.txtItemCode.Text.Trim();
        string itemName = this.txtItemName.Text.Trim();
        string colorCode = this.txtColorCode.Text.Trim().ToUpper();
        string sizeCode = this.txtSizeCode.Text.Trim().ToUpper();
        string sku = this.txtSku.Text.Trim().ToUpper();
        string area = this.drpArea.SelectedValue;
        string section = this.txtSection.Text.Trim();
        ObjectQuery query = session.CreateObjectQuery(@"
        select spec.BarCode as BarCode,m.ItemCode as ItemCode,m.ItemName as ItemName
        ,spec.ColorCode as ColorCode,color.ColorText as ColorText,spec.SizeCode as SizeCode
        ,detail.StockQty as StockQty,detail.FrozenQty as FrozenQty,detail.SectionCode as SectionCode,detail.AreaCode as WHAName
        from StockDetail detail
        inner join ItemSpec spec on detail.SKUID = spec.SKUID
        inner join ItemMaster m on m.ItemID = spec.ItemID
        inner join WHArea wha on detail.AreaCode = wha.AreaCode
        left join WHSection whs on detail.SectionCode = whs.SectionCode and detail.AreaCode=whs.AreaCode
        left join ItemColor color on spec.ColorCode=color.ColorCode
        order by m.ItemCode,spec.ColorCode,spec.SizeCode
        ")
            .Attach(typeof(StockDetail)).Attach(typeof(ItemMaster)).Attach(typeof(ItemSpec))
            .Attach(typeof(WHSection)).Attach(typeof(WHArea))
            .Attach(typeof(ItemColor))
            .SetPage(pageIndex, pageSize);

        //追加条件
        if (!string.IsNullOrEmpty(itemCode)) query.And(Exp.Like("m.ItemCode", "%" + itemCode + "%"));
        if (!string.IsNullOrEmpty(itemName)) query.And(Exp.Like("m.ItemName", "%" + itemName + "%"));
        if (!string.IsNullOrEmpty(colorCode)) query.And(Exp.Like("spec.ColorCode", "%"+ colorCode.ToUpper() + "%"));
        if (!string.IsNullOrEmpty(sku)) query.And(Exp.Like("spec.BarCode", "%"+ sku.ToUpper() + "%"));
        if (!string.IsNullOrEmpty(sizeCode)) query.And(Exp.Like("spec.SizeCode", "%"+ sizeCode.ToUpper() + "%"));
        if (!string.IsNullOrEmpty(area)) query.And(Exp.Eq("detail.AreaCode", area.ToUpper()));
        if (!string.IsNullOrEmpty(section)) query.And(Exp.Like("detail.SectionCode", "%" + section.ToUpper() + "%"));
        this.rptPO.DataSource = query.DataSet();
        this.rptPO.DataBind();
        if (fetchRecordCount)
            this.magicPagerMain.RecordCount = this.magicPagerSub.RecordCount = query.Count();
        WebUtil.SetMagicPager(magicPagerMain, pageSize, pageIndex);
        WebUtil.SetMagicPager(magicPagerSub, pageSize, pageIndex);
    }

    private void QueryAndBindData(ISession session)
    {
        string ordNum = WebUtil.Param("ordNum");
        ObjectQuery query = session.CreateObjectQuery(@"
        select s.BarCode as BarCode,m.ItemCode as ItemCode,m.ItemName as ItemName
        ,color.ColorText as ColorText,s.ColorCode as ColorCode,s.SizeCode as SizeCode
        ,line.Quantity as Quantity,line.Price as Price
        from CRMSN sn
        inner join CRMSNLine line on sn.ID=line.SNID
        inner join ItemSpec s on s.SKUID=line.SKUID
        inner join ItemMaster m on m.ItemID=s.ItemID
        left join ItemColor color on color.ColorCode=s.ColorCode
        where sn.OrderNumber=?ordnum
        order by line.ID")
            .Attach(typeof(CRMSN)).Attach(typeof(CRMSNLine))
            .Attach(typeof(ItemMaster)).Attach(typeof(ItemSpec)).Attach(typeof(ItemColor))
            .SetValue("?ordnum", ordNum, "sn.OrderNumber");

        this.repeatControl.DataSource = query.DataSet();
        this.repeatControl.DataBind();
    }

        public static DataSet QueryLine(ISession session, bool isAssisItem, string ordNum, bool requireCount, out int count, string location, string sku, string itemCode, string itemName, string color, string size, string area, string section, int pageIndex, int pageSize)
        {
            ObjectQuery query = session.CreateObjectQuery(@"
            select sku.BarCode as BarCode,i.ItemCode as ItemCode,i.ItemName as ItemName
            ,sku.ColorCode as ColorCode,color.ColorText as ColorText,sku.SizeCode as SizeCode
            ,sto.AreaCode as AreaCode,sto.SectionCode as SectionCode,sto.StockQty as StockQty,sto.FrozenQty as FrozenQty
            ,sto.StockDetailID as StockDetailID,l.LineNumber as LineNumber,l.Quantity as Qty
            ,case when l.LineNumber is null then sku.AvgMoveCost else l.Price end as Price
            from StockDetail sto
            inner join ItemSpec sku on sku.SKUID=sto.SKUID
            inner join ItemMaster i on i.ItemID=sku.ItemID
            left join ItemColor color on color.ColorCode=sku.ColorCode
            left join StockInLine l on l.OrderNumber=?stoOrdNum and l.StockDetailID=sto.StockDetailID
            where sto.LocationCode=?loc and i.ItemType=?itype
            order by i.ItemCode,sku.ColorCode,sku.SizeCode,sto.AreaCode,sto.SectionCode")
                .Attach(typeof(StockDetail)).Attach(typeof(StockInLine))
                .Attach(typeof(ItemSpec)).Attach(typeof(ItemMaster)).Attach(typeof(ItemColor))
                .SetValue("?loc", location, "sto.LocationCode")
                .SetValue("?stoOrdNum", ordNum, "l.OrderNumber")
                .SetPage(pageIndex, pageSize);

            if (isAssisItem)
                query.SetValue("?itype", ItemType.AssistantItem, "i.ItemType");
            else
                query.SetValue("?itype", ItemType.NormalItem, "i.ItemType");
            if (!string.IsNullOrEmpty(area))
                query.And(Exp.Eq("sto.AreaCode", area));
            if (section.Trim().Length > 0) query.And(Exp.Like("sto.SectionCode", "%" + section.Trim().ToUpper() + "%"));
            if (sku.Trim().Length > 0) query.And(Exp.Like("sku.BarCode", "%" + sku.Trim().ToUpper() + "%"));
            if (itemCode.Trim().Length > 0) query.And(Exp.Like("i.ItemCode", "%" + itemCode.Trim().ToUpper() + "%"));
            if (itemName.Trim().Length > 0) query.And(Exp.Like("i.ItemName", "%" + itemName.Trim() + "%"));
            if (color.Trim().Length > 0) query.And(Exp.Like("sku.ColorCode", "%" + color.Trim().ToUpper() + "%"));
            if (size.Trim().Length > 0) query.And(Exp.Like("sku.SizeCode", "%" + size.Trim().ToUpper() + "%"));

            count = 0;
            if (requireCount) count = query.Count();

            return query.DataSet();
        }