private QueryNode QueryData(string program_job_no, string status, DependencyObjectCollection doc_no, string site_no, string warehouse_no, string warseHouseControl) { OrderByItem[] orderByItem = null; QueryProperty property = null; bool bcInvControl = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>()); if (bcInvControl) { property = OOQL.CreateProperty("BC_INVENTORY.QTY"); } else { property = OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"); } //策略 if (warseHouseControl == "1") //1.生效日期先进先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "2") //2.生效日期后进先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Desc) } } ; else if (warseHouseControl == "3") //3.不限定 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.LOT_CODE", SortType.Asc) } } ; else if (warseHouseControl == "4") //4.先到期先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.INEFFECTIVE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "5") //5.允许出库日早者先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.ALLOW_ISSUE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "6") //6.可用量少的先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Asc) }; } else if (warseHouseControl == "7") //6.可用量少的先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Desc) }; } //组织单号 List <ConstantsQueryProperty> docNos = new List <ConstantsQueryProperty>(); foreach (DependencyObject item in doc_no) { docNos.Add(OOQL.CreateConstants(item["doc_no"].ToStringExtension())); } QueryNode node = null; if (program_job_no == "5")//20170328 modi by wangyq for P001-170327001去掉&& status == "A" { node = GetSalesDeliveryNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("7") && status == "A") { node = GetMONode(site_no, program_job_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("7") && status == "S") { node = GetIssueReceiptNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("11") && status == "S") { node = GeTransferDocNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("4")) //20170629 add by zhangcn for P001-170606002 //20170719 modi by shenbao for 拿掉status == "A" { node = GePurchaseReturnNode(site_no, docNos, warseHouseControl); //20170629 add by zhangcn for P001-170606002 } else if (program_job_no.StartsWith("5-1")) //20170829 add by shenbao for P001-170717001 { node = GetSalesOrderDocNode(site_no, docNos, warseHouseControl); } List <QueryProperty> pubProperties = new List <QueryProperty>() { OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateProperty("QuerySource.PLANT_CODE", "site_no"), OOQL.CreateProperty("QuerySource.ITEM_CODE", "item_no"), OOQL.CreateProperty("QuerySource.ITEM_NAME", "item_name"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.STOCK_UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.SOURCE_QTY"), OOQL.CreateConstants(0), "SOURCE_QTY") }; //查询 if (bcInvControl) { pubProperties.AddRange(new QueryProperty[] { OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), OOQL.CreateConstants(UtilsClass.SpaceValue, "inventory_management_features"),//20170328 modi by wangyq for P001-170327001 old:string.Empty Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.CreateDate"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"), Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.QTY"), OOQL.CreateConstants(0), "inventory_qty"), Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID") , OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID") , OOQL.CreateProperty("BC_INVENTORY.QTY") , OOQL.CreateProperty("QuerySource.UNIT_ID") , OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"), Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") }, orderByItem )) }); node = OOQL.Select( pubProperties ) .From(node, "QuerySource") .LeftJoin("BC_INVENTORY") .On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("BC_INVENTORY.ITEM_ID") & Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("BC_INVENTORY.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("BC_INVENTORY.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BC_INVENTORY.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("BC_INVENTORY.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001 .On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)) | OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants("")) & OOQL.CreateProperty("BC_INVENTORY.QTY") > OOQL.CreateConstants(0) & OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001 } else { pubProperties.AddRange(new QueryProperty[] { OOQL.CreateConstants("", "barcode_no"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.LAST_RECEIPT_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"), Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"), OOQL.CreateConstants(0), "inventory_qty"), Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID") , OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID") , OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") , OOQL.CreateProperty("QuerySource.UNIT_ID") , OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"), Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") }, orderByItem )) }); node = OOQL.Select( pubProperties ) .From(node, "QuerySource") .LeftJoin("ITEM_WAREHOUSE_BIN") .On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") & Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("PLANT") //20170719 add by shenbao for P001-170717001 .On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)) | OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants("")) & OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") > OOQL.CreateConstants(0) & OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BO_ID.RTK") == OOQL.CreateConstants("OTHER") & OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001 } return(node); }