//20170209 add by liwei1 for P001-170203001 ===begin===
/// <summary>
/// 查询单据性质ID
/// </summary>
/// <param name="category">单据性质</param>
/// <param name="scanColl">单身数据集</param>
/// <returns>单据性质ID</returns>
private object QueryDocId(string category, string recommendedOperations, DependencyObjectCollection scanColl)
{
QueryNode node = null;
if (scanColl.Count > 0)
{
string infoLotNo = scanColl[0]["info_lot_no"].ToStringExtension(); //信息批号
string siteNo = scanColl[0]["site_no"].ToStringExtension(); //工厂
node = OOQL.Select(1, OOQL.CreateProperty("MO.DOC_ID"))
.From("MO", "MO")
.Where((OOQL.AuthFilter("MO", "MO"))
& (OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(infoLotNo)));
//根据条件查询满足条件的DOC_ID
node = OOQL.Select(1, OOQL.CreateProperty("PARA_DOC_FIL.DOC_ID"))
.From("PARA_DOC_FIL", "PARA_DOC_FIL")
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PARA_DOC_FIL.Owner_Org.ROid")))
.Where((OOQL.AuthFilter("PARA_DOC_FIL", "PARA_DOC_FIL"))
& ((OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))
& (OOQL.CreateProperty("PARA_DOC_FIL.CATEGORY") == OOQL.CreateConstants(category))
& ((OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue()))
| (OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == node))))
.OrderBy(
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID"), SortType.Desc));
}
return(GetService <IQueryService>().ExecuteScalar(node));
}
/// <summary>
/// 获取单据性质
/// </summary>
/// <param name="receiptNo">到货单单号</param>
/// <param name="category">单据性质</param>
/// <returns></returns>
private object GetDocId(string receiptNo, string category)
{
QueryNode node =
OOQL.Select(1,
OOQL.CreateProperty("pdf.DOC_ID"))
.From("PARA_DOC_FIL", "pdf")
.InnerJoin(
OOQL.Select(
OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid", "RECEIVE_Owner_Org_ROid"),
OOQL.CreateProperty("pa.DOC_ID", "DOC_ID"))
.From("PURCHASE_ARRIVAL", "pa")
.Where((OOQL.CreateProperty("pa.DOC_NO") == OOQL.CreateConstants(receiptNo))), "pa")
.On((OOQL.CreateProperty("pa.RECEIVE_Owner_Org_ROid") == OOQL.CreateProperty("pdf.Owner_Org.ROid")))
.Where((OOQL.AuthFilter("PARA_DOC_FIL", "pdf"))
& (OOQL.CreateProperty("pdf.CATEGORY") == OOQL.CreateConstants(category))
& ((OOQL.CreateProperty("pdf.SOURCE_DOC_ID") == OOQL.CreateProperty("pa.DOC_ID"))
| (OOQL.CreateProperty("pdf.SOURCE_DOC_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue()))))
.OrderBy(OOQL.CreateOrderByItem(OOQL.CreateProperty("pdf.SOURCE_DOC_ID"), SortType.Desc));
return(GetService <IQueryService>().ExecuteScalar(node));
}
private QueryNode GetPurchaseDetail(string supplierNo, string dateS, string dateE, string dueDateS, string dueDateE,
string itemNo, string itemName, string itemSpec, string siteNo
, string purchase_no//20170919 add by liwei1 for B001-170918003
)
{
QueryNode subNodeFilArrival = GetFilArrivalQueryNode();
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_DATE", "purchase_date"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE", "so_due_date"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "line_seq"),
OOQL.CreateConstants(0, GeneralDBType.Int32, "batch_seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), //20170504 add by wangyq for P001-161209002
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), //20171010 modi by zhangcn for B001-171010004
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateConstants("N", GeneralDBType.String, "qc_type"), //20170619 modi by zhangcn for B001-170629006 【OLD：null】
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "purchase_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "receipt_qty"),
//OOQL.CreateArithmetic(
// OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"),
// OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub,"unpaid_qty"),
OOQL.CreateArithmetic(OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"), OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub),
Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0)),
ArithmeticOperators.Sub,
"unpaid_qty"),
Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0), "on_the_way_qty"), //20170619 modi by zhangcn for B001-170629006 【OLD：0】
Formulas.IsNull(
OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE") * OOQL.CreateConstants(100, GeneralDBType.Decimal),
OOQL.CreateConstants(0, GeneralDBType.Decimal), "over_deliver_rate"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.REMARK", "remark"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"),
Formulas.IsNull(
OOQL.CreateProperty("PRICE_UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PRICE_QTY", "valuation_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "returned_qty"), //20170619 add by zhangcn for B001-170629006
OOQL.CreateConstants(1, GeneralDBType.Decimal, "box_qty")) //20170619 add by zhangcn for B001-170629006
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
//20170504 add by wangyq for P001-161209002 ========================begin=========================
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
//20170504 add by wangyq for P001-161209002 ========================end=========================
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On((OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
& (OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Emp")))
.LeftJoin("UNIT", "PRICE_UNIT")
.On((OOQL.CreateProperty("PRICE_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PRICE_UNIT_ID")))
//20170619 add by zhangcn for B001-170629006 ===begin===
.LeftJoin(subNodeFilArrival, "FILARR")
.On(OOQL.CreateProperty("FILARR.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") &
OOQL.CreateProperty("FILARR.ORDER_SE") == OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber") &
OOQL.CreateProperty("FILARR.ORDER_SE_SE") == OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber"));
//20170619 add by zhangcn for B001-170629006 ===begin===
//如果起始日期为空格，空值，null时默认最小日期
if (Maths.IsEmpty(dateS.ToDate()))
{
dateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
if (Maths.IsEmpty(dueDateS.ToDate()))
{
dueDateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
//如果结束日期为空格，空值，null时默认最大日期
if (Maths.IsEmpty(dateE.ToDate()))
{
dateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
if (Maths.IsEmpty(dueDateE.ToDate()))
{
dueDateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC"))
& ((OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") == OOQL.CreateConstants("Y"))
//& (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIPTED_BUSINESS_QTY"))//20170903 mark by liwei1 for B001-170901012
& (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY")) //20170903 add by liwei1 for B001-170901012
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& ((OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") >= OOQL.CreateConstants(dateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") <= OOQL.CreateConstants(dateE.ToDate())))
& ((OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") >= OOQL.CreateConstants(dueDateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") <= OOQL.CreateConstants(dueDateE.ToDate()))));
//如果【品号】不为空增加条件
if (!Maths.IsEmpty(itemNo))
{
conditionGroup &= (OOQL.CreateProperty("ITEM.ITEM_CODE").Like(OOQL.CreateConstants("%" + itemNo + "%")));
}
//如果【品名】不为空增加条件
if (!Maths.IsEmpty(itemName))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION").Like(OOQL.CreateConstants("%" + itemName + "%")));
}
//如果【规格】不为空增加条件
if (!Maths.IsEmpty(itemSpec))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION").Like(OOQL.CreateConstants("%" + itemSpec + "%")));
}
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
//20170919 add by liwei1 for B001-170918003 ===begin===
//如果采购单号不为空增加条件
if (!Maths.IsEmpty(purchase_no))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateConstants(purchase_no));
}
//20170919 add by liwei1 for B001-170918003 ===end===
return(joinOnNode.Where(conditionGroup)
//20170919 add by liwei1 for B001-170918003 ===begin===
.OrderBy(
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"), SortType.Asc),
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber"), SortType.Asc))
//20170919 add by liwei1 for B001-170918003 ===end===
);
}
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);
}
/// <summary>
/// 查询到货单查询信息
/// </summary>
/// <param name="programJobNo">作业编号</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="docNo">单据编号</param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetTransactionDocQueryNode(string programJobNo, string siteNo, string[] docNo, string status) //20161216 modi by shenbao for P001-161215001 修改docNo类型为数组
{
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "source_no"),
OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE", "create_date"),
OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "in_out_qty"),
Formulas.Cast(OOQL.CreateProperty("TRANSACTION_DOC_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
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("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_line_seq"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
//20161216 add by shenbao FOR P001-161215001 ===begin===
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
//20161216 add by shenbao FOR P001-161215001 ===end===
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("TRANSACTION_DOC", "TRANSACTION_DOC")
.InnerJoin("TRANSACTION_DOC.TRANSACTION_DOC_D", "TRANSACTION_DOC_D")
.On(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===end===
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") ==
OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID")
}
, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_I")
.On((OOQL.CreateProperty("REG_I.ITEM_ID") ==
OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_ID"))
& (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("FEATURE_GROUP_ID"),
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_G")
.On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID"))
& (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC")) &
((OOQL.CreateProperty("TRANSACTION_DOC.ApproveStatus") == OOQL.CreateConstants("N")) &
(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO").In(OOQL.CreateDyncParameter("docNos", docNo))) & //20161216 add by shenbao FOR P001-161215001 OOQL.CreateConstants(docNo)==>.In(OOQL.CreateDyncParameter("docnos", docNo)
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))));
return(queryNode);
}
/// <summary>
/// 获取领料出库单查询信息
/// </summary>
/// <param name="docNo">单据编号</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetIssueReceiptQueryNode(string[] docNo, string siteNo, string programJobNo, string status)
{
//20161216 add by liwei1 for P001-161215001
//private QueryNode GetIssueReceiptQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001
string docType = programJobNo + status;
//20170925 add by wangyq for P001-170717001 =============begin===============
QueryConditionGroup group =
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)) &
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo);
if (programJobNo == "7-5")
{
group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N") &
OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1");
}
else
{
group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N");
}
//20170925 add by wangyq for P001-170717001 =============end===============
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "source_no"),
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"),
Formulas.Cast(OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_QTY", "doc_qty"),
//20161230 modi by shenbao for B001-161229011 INVENTORY_QTY=>ISSUE_RECEIPT_QTY
Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "in_out_qty"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty), "item_feature_name"),
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("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
//20161216 add by shenbao FOR P001-161215001 ===begin===
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty),
"item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty),
"item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[]
{
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
OOQL.CreateArithmetic(
Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),
//允许误差率//20170302 modi by shenbao for P001-170302002 误差率统一乘100
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[]
{
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")
, OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[]
{
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")
, OOQL.CreateConstants(0)
}), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty),
"inventory_unit"), //库存单位
//20161216 add by shenbao FOR P001-161215001 ===end===
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"),
//20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"),
//20170424 add by wangyq for P001-170420001
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("ISSUE_RECEIPT", "ISSUE_RECEIPT")
.LeftJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===end===
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID")
}
, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_I")
.On((OOQL.CreateProperty("REG_I.ITEM_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID"))
& (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("FEATURE_GROUP_ID"),
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_G")
.On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID"))
& (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) &
//20170925 modi by wangyq for P001-170717001 =============begin===============
group);
// (OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &//20161216 add by liwei1 for P001-161215001
////(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001
// (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
// (OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N")));
//20170925 modi by wangyq for P001-170717001 =============end===============
return(queryNode);
}
/// <summary>
/// 查询计算因子的函数
/// </summary>
/// <returns></returns>
public DependencyObjectCollection GetFunctionInfo()
{
SimpleQueryProperty namePro = null;
SimpleQueryProperty desPro = null;
SimpleQueryProperty paraName = null; //20170829 add by shenbao for P001-170829002
//namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_CHS", "FUNCTION_INFO_DISPLAY_NAME"); //20170829 mark by shenbao for P001-170829002
//desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_CHS", "FUNCTION_EXPLAIN"); //20170829 mark by shenbao for P001-170829002
//if (ThisToken.CultureInfoName == "en-US") {
// namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_ENU", "FUNCTION_INFO_DISPLAY_NAME");
// desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_ENU", "FUNCTION_EXPLAIN");
//} else if (ThisToken.CultureInfoName == "zh-CHT") {
// namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_CHT", "FUNCTION_INFO_DISPLAY_NAME");
// desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_CHT", "FUNCTION_EXPLAIN");
//} else {
// namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_CHS", "FUNCTION_INFO_DISPLAY_NAME");
// desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_CHS", "FUNCTION_EXPLAIN");
//}
//20170829 add by shenbao for P001-170829002 ===begin===
/* 关于这里的Token
* 由于是在服务端取多语言的字段，不能用缓存的方式，必须用客户端发送过来的实时的token
* 否则会出现简体客户端先发送请求，后英文客户端登录发请求，token中存储的还是简体语言的情况
*/
Token token = CallContext.GetData("Token") as Token;
if (token.CultureInfoName == "en-US")
{
namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_ENU", "FUNCTION_INFO_DISPLAY_NAME");
desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_ENU", "FUNCTION_EXPLAIN");
paraName = OOQL.CreateProperty("FUNCTION_INFO_D.PARAMETER_NAME_ENU", "PARAMETER_NAME");
}
else if (token.CultureInfoName == "zh-TW")
{
namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_CHT", "FUNCTION_INFO_DISPLAY_NAME");
desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_CHT", "FUNCTION_EXPLAIN");
paraName = OOQL.CreateProperty("FUNCTION_INFO_D.PARAMETER_NAME_CHT", "PARAMETER_NAME");
}
else
{
namePro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_DESC_CHS", "FUNCTION_INFO_DISPLAY_NAME");
desPro = OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_EXPLAIN_CHS", "FUNCTION_EXPLAIN");
paraName = OOQL.CreateProperty("FUNCTION_INFO_D.PARAMETER_NAME");
}
//20170829 add by shenbao for P001-170829002 ===end===
QueryNode node = OOQL.Select(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_NAME"), //函数编号
OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_ID"),
OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2", "FUNCTION_INFO_CLASS"), //供应链类别编号统一加100
namePro, //显示名称
desPro, //描述
Formulas.Case(null, OOQL.CreateConstants(Resources.LABEL_000010), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("101"), OOQL.CreateConstants(Resources.LABEL_000001)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("102"), OOQL.CreateConstants(Resources.LABEL_000002)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("103"), OOQL.CreateConstants(Resources.LABEL_000003)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("104"), OOQL.CreateConstants(Resources.LABEL_000004)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("105"), OOQL.CreateConstants(Resources.LABEL_000005)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("106"), OOQL.CreateConstants(Resources.LABEL_000006)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("107"), OOQL.CreateConstants(Resources.LABEL_000007)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("108"), OOQL.CreateConstants(Resources.LABEL_000008)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("109"), OOQL.CreateConstants(Resources.LABEL_000009)),
new CaseItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2") == OOQL.CreateConstants("110"), OOQL.CreateConstants(Resources.LABEL_000010)),
}, "FUNCTION_INFO_CLASS_NAME"),
OOQL.CreateProperty("FUNCTION_INFO_D.FUNCTION_INFO_D_ID"), //ID
OOQL.CreateProperty("FUNCTION_INFO_D.SequenceNumber"), //序号
OOQL.CreateProperty("FUNCTION_INFO_D.PARAMETER_CODE"), //参数编号
paraName, //参数名称 //20170829 modi by shenbao for P001-170829002
OOQL.CreateProperty("FUNCTION_INFO_D.PLACEHOLDER"), //占位符
OOQL.CreateProperty("FUNCTION_INFO_D.PARAMETER_VALUE_TYPE"), //值类型
OOQL.CreateProperty("FUNCTION_INFO_D.CONDITIONS"), //条件
OOQL.CreateProperty("FUNCTION_INFO_D.ORGANIZATION_TYPE"), //组织类型
OOQL.CreateProperty("FUNCTION_INFO_D.CONTROL_TYPE"), //控件类型
OOQL.CreateProperty("FUNCTION_INFO_D.CONTROL_CODE"), //控件绑定编号 如果控件类型是picklist编号为picklistname 如果控件类型是开窗 则编号为开窗的限定方案编号
OOQL.CreateProperty("FUNCTION_INFO_D.WINDOW_TYPEKEY") //开窗TYPEKEY
)
.From("FUNCTION_INFO", "FUNCTION_INFO")
.LeftJoin("FUNCTION_INFO.FUNCTION_INFO_D", "FUNCTION_INFO_D")
.On(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_ID") == OOQL.CreateProperty("FUNCTION_INFO_D.FUNCTION_INFO_ID"))
.Where(OOQL.AuthFilter("FUNCTION_INFO", "FUNCTION_INFO")
& OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_INFO_STYLE") == OOQL.CreateConstants("1"))
.OrderBy(OOQL.CreateOrderByItem(OOQL.CreateProperty("FUNCTION_INFO.FUNCTION_CLASS2"), SortType.Asc)
, OOQL.CreateOrderByItem(OOQL.CreateProperty("FUNCTION_INFO_D.SequenceNumber"), SortType.Asc));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
private QueryNode GeIssueReceiptReqNode(string[] docNo, string siteNo, string programJobNo, string status)
{
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(
OOQL.CreateConstants("99", "enterprise_no"), //企业编号
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点
OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业
OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO", "source_no"), //来源单号
OOQL.CreateConstants(docType, "doc_type"), //单据类型
OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_DATE", "create_date"), //单据日期
Formulas.IsNull(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.SequenceNumber"), OOQL.CreateConstants(0), "seq"), //单据项序
OOQL.CreateConstants(0, "doc_line_seq"), //单据项序
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
OOQL.CreateConstants(string.Empty, "object_no"), //对象编号
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), //料件编号
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //产品特征
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //产品特征说明
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.CreateProperty("ISSUE_RECEIPT_REQ_D.REQUEST_QTY", "doc_qty"), //单据数量
OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ISSUE_RECEIPT_QTY", "in_out_qty"), //出入数量
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), //单位
OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), //主营组织
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateConstants(string.Empty, "main_warehouse_no"),
OOQL.CreateConstants(string.Empty, "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")
.LeftJoin("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D", "ISSUE_RECEIPT_REQ_D")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ISSUE_RECEIPT_REQ_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID")
}
, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_I")
.On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_ID"))
& (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("FEATURE_GROUP_ID"),
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_G")
.On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID"))
& (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")) &
(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ApproveStatus") == OOQL.CreateConstants("Y")));
return(queryNode);
}
/// <summary>
/// 查询寄售订单
/// </summary>
/// <param name="programJobNo">作业编号</param>
/// <param name="status">A.新增 S.过帐</param>
/// <param name="docNo">单据编号</param>
/// <param name="siteNo">工厂</param>
/// <returns></returns>
private DependencyObjectCollection GetSalesOrderDoc(string programJobNo, string status, string[] docNo, string siteNo)
{
QueryNode node = OOQL.Select(true,
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO", "source_no"),
OOQL.CreateConstants(programJobNo + status, "doc_type"),
OOQL.CreateProperty("SALES_ORDER_DOC.DOC_DATE", "create_date"),
OOQL.CreateProperty("SALES_ORDER_DOC_D.SequenceNumber", "seq"),
OOQL.CreateProperty("SALES_ORDER_DOC_SD.SequenceNumber", "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Int32, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty), "item_feature_name"),
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"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateProperty("SALES_ORDER_DOC_SD.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERED_BUSINESS_QTY", "in_out_qty"),
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty), "unit_no"),
Formulas.IsNull(
OOQL.CreateArithmetic(
OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE"),
OOQL.CreateConstants(100), ArithmeticOperators.Mulit),
OOQL.CreateConstants(0), "allow_error_rate"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.Case(null,
OOQL.CreateConstants("1"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")),
OOQL.CreateConstants("2"))), "lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(1)),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(0)),
Formulas.IsNull(
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty), "inventory_unit"),
Formulas.IsNull(
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT"),
OOQL.CreateConstants(string.Empty), "decimal_places"),
OOQL.CreateConstants(1, "decimal_places_type"),
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"),
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateConstants(string.Empty, "main_warehouse_no"),
OOQL.CreateConstants(string.Empty, "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("SALES_ORDER_DOC", "SALES_ORDER_DOC")
.InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D", "SALES_ORDER_DOC_D")
.On((OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_ID")))
.InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D.SALES_ORDER_DOC_SD", "SALES_ORDER_DOC_SD")
.On((OOQL.CreateProperty("SALES_ORDER_DOC_SD.SALES_ORDER_DOC_D_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_D_ID")))
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PARTNER_ID.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_WAREHOUSE_ID")))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On((OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.BIN_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID")))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On((OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"))
& (OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")))
.LeftJoin("ITEM_SALES", "ITEM_SALES")
.On((OOQL.CreateProperty("ITEM_SALES.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"))
& (OOQL.CreateProperty("ITEM_SALES.Owner_Org.ROid") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid")))
.LeftJoin("UNIT", "STOCK_UNIT")
.On((OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")))
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") ==
OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID")
}
, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_I")
.On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_ID"))
& (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("FEATURE_GROUP_ID"),
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_G")
.On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID"))
& (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC"))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))
& (OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B"))
& OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));
return(GetService <IQueryService>().ExecuteDependencyObject(node));
}
