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);
}
