/// <summary>
/// 根据品号获取品号工厂信息
/// </summary>
/// <param name="itemID">品号ID集合</param>
/// <param name="sitNo">工厂ID</param>
/// <returns></returns>
private DependencyObjectCollection GetItemForPlant(DependencyObjectCollection itemID, string siteNo, string programJobNo)
{
List <QueryProperty> properties = GetQueryProperty(siteNo);
properties.Add(Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("ITEM_PLANT.RECEIPT_OVERRUN_RATE"), new CaseItem[] {
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%"))
, OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE")),
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("8%"))
, OOQL.CreateProperty("ITEM_PLANT.ISSUE_SHORTAGE_RATE"))
}), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "over_deliver_rate")); //超交率
QueryNode node = OOQL.Select(
properties
)
.From("ITEM", "ITEM")
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID"))
.LeftJoin("PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(OOQL.AuthFilter("ITEM", "ITEM")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("ITEM.ITEM_ID").In(OOQL.CreateDyncParameter("IDS", itemID.Select(c => c["item_id"]).ToArray())));
return(UtilsClass.ExecuteDependencyObject(this.GetService <IQueryService>(), node));
}
/// <summary>
/// 查询单号
/// </summary>
/// <param name="docNos">单号集合</param>
/// <returns></returns>
private DependencyObjectCollection QueryDoc(List <string> docNos)
{
_queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"),
OOQL.CreateProperty("DOC.AUTO_APPROVE"))
.From("TRANSACTION_DOC", "TRANSACTION_DOC")
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.DOC_ID"))
.Where(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray())));
return(_querySrv.ExecuteDependencyObject(_queryNode));
}
/// <summary>
/// 获取新插入的领料单，重新保存
/// </summary>
/// <param name="docNos">单号集合</param>
/// <returns></returns>
private DependencyObjectCollection GetSalesIssue(List <string> docNos)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID"),
OOQL.CreateProperty("DOC.AUTO_APPROVE"))
.From("SALES_ISSUE", "SALES_ISSUE")
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("SALES_ISSUE.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID"))
.Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") &
OOQL.CreateProperty("SALES_ISSUE.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray())));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <summary>
/// 获取新插入的领料单，重新保存
/// </summary>
/// <param name="docNos">单号集合</param>
/// <returns></returns>
private DependencyObjectCollection GetIssueReceipt(List <string> docNos)
{
QueryNode node = OOQL.Select(
"ISSUE_RECEIPT.ISSUE_RECEIPT_ID",
"DOC.AUTO_APPROVE"
)
.From("ISSUE_RECEIPT")
.InnerJoin("DOC")
.On(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID"))
.Where(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray())));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <summary>
/// 根据品号获取品号采购信息
/// </summary>
/// <param name="itemID">品号ID集合</param>
/// <param name="mainOrganization">采购域</param>
/// <returns></returns>
private DependencyObjectCollection GetItemForSupplyCenter(DependencyObjectCollection itemID, string siteNo, object mainOrganization, string programJobNo)
{
List <QueryProperty> properties = GetQueryProperty(siteNo);
properties.Add(Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0m, GeneralDBType.Decimal)
, "over_deliver_rate")); //超交率
QueryNode node = OOQL.Select(
properties
)
.From("ITEM", "ITEM")
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID"))
.Where(OOQL.AuthFilter("ITEM", "ITEM")
& OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateConstants(mainOrganization)
& OOQL.CreateProperty("ITEM.ITEM_ID").In(OOQL.CreateDyncParameter("IDS", itemID.Select(c => c["item_id"]).ToArray())));
return(UtilsClass.ExecuteDependencyObject(this.GetService <IQueryService>(), node));
}
/// <summary>
/// 拼接查询语句
/// </summary>
/// <param name="programJobNo"></param>
/// <param name="siteNo"></param>
/// <param name="condition"></param>
/// <returns></returns>
private QueryNode GetSalesIssueNode(string programJobNo, string siteNo, string[] docNo, string status)
{
List <QueryProperty> selectList = GetSelectList(programJobNo, status);
return(OOQL.Select(selectList.ToArray()
)
.From("SALES_ISSUE", "SALES_ISSUE")
.InnerJoin("SALES_ISSUE.SALES_ISSUE_D", "SALES_ISSUE_D")
.On(OOQL.CreateProperty("SALES_ISSUE_D.SALES_ISSUE_ID") == OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("SALES_ISSUE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("SALES_ISSUE_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("SALES_ISSUE_D.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("SALES_ISSUE_D.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("SALES_ISSUE_D.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("SALES_ISSUE.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_SALES", "ITEM_SALES")
.On(OOQL.CreateProperty("ITEM_SALES.ITEM_ID") == OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") &
OOQL.CreateProperty("ITEM_SALES.Owner_Org.ROid") == OOQL.CreateProperty("SALES_ISSUE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
.Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") & (
OOQL.CreateProperty("SALES_ISSUE.DOC_NO").In(OOQL.CreateDyncParameter("docNo", docNo))
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("SALES_ISSUE.ApproveStatus") == OOQL.CreateConstants("N", GeneralDBType.String)
& OOQL.CreateProperty("SALES_ISSUE_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1", GeneralDBType.String))));
}
/// <summary>
/// 获取采购订单QueryNode
/// </summary>
/// <param name="docNo">单据编号</param>
/// <param name="siteNo">工厂</param>
/// <param name="programJobNo">作业编号</param>
/// <param name="scanType"></param>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetPurchaseOrderQueryNode(string[] docNo, string siteNo, string programJobNo, string scanType,
string id, string status)
{
//20161216 add by liwei1 for P001-161215001
//private QueryNode GetPurchaseOrderQueryNode(string docNo, string siteNo, string programJobNo,string scanType,string id,string status) {//20161216 mark by liwei1 for P001-161215001
//where条件中存在Or条件，考虑到性能问题：OOQL写成Union形式
// 条件：
// （【入参scan_type】 = 1.箱条码’
//AND 采购订单子单身.主键(PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID) = 【入参ID】）
//OR(【入参scan_type】 = 2.单据条码
//采购订单信息.单号(PURCHASE_ORDER.DOC_NO) = 【入参doc_no】 AND)
List <QueryProperty> lstQueryProperties = new List <QueryProperty>();
lstQueryProperties.AddRange(new QueryProperty[]
{
OOQL.CreateConstants("99", GeneralDBType.String, "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "source_operation"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "source_no"),
OOQL.CreateConstants(programJobNo + status, GeneralDBType.String, "doc_type"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Int32, "doc_batch_seq"),
//OOQL.CreateConstants(string.Empty, GeneralDBType.String, "object_no"),//20170903 mark by liwei1 for B001-170904001
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "object_no"), //20170903 add by liwei1 for B001-170904001
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"),
Formulas.IsNull(
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "warehouse_no"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "lot_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "in_out_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE", "in_out_date1"),
//20170511 add by liwei1 for P001-170420001
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_CODE", "main_organization"),
//20160110 add by shenbao for P001-170110001 ===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"), //规格
OOQL.CreateArithmetic(
Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),
//允许误差率 //20170302 modi by shenbao for P001-170302002 误差率统一乘100
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("PURCHASE_ORDER_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[]
{
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0)
}), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty),
"inventory_unit"), //库存单位
//20160110 add by shenbao for P001-170110001 ===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
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
}
);
#region 20170619 add by zhangcn for P001-170606002 PURCHASE_ORDER.ALL_SYNERGY = True
QueryNode nodeAllSynery =
OOQL.Select(lstQueryProperties)
.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("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
//20170619 add by zhangcn for P001-170606002 ===begin===
.LeftJoin("SUPPLY_SYNERGY", "SUPPLY_SYNERGY")
.On(OOQL.CreateProperty("SUPPLY_SYNERGY.SUPPLY_SYNERGY_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.GROUP_SYNERGY_ID.ROid"))
.LeftJoin("PLANT", "PLANTSSY")
.On(OOQL.CreateProperty("PLANTSSY.PLANT_ID") ==
OOQL.CreateProperty("SUPPLY_SYNERGY.REQUIRE_Owner_Org.ROid"))
//20170619 add by zhangcn for P001-170606002 ===end===
.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")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID"))) //20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.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("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("1") == OOQL.CreateConstants(scanType, GeneralDBType.String))
&
(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") ==
OOQL.CreateConstants(id, GeneralDBType.Guid))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.GENERATE_STATUS") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.INVOICE_COMPANY_ID") ==
OOQL.CreateProperty("PLANTSSY.COMPANY_ID")) //20170619 add by zhangcn for P001-170606002
& (OOQL.CreateProperty("SUPPLY_SYNERGY.GENERATE_DIRE") == OOQL.CreateConstants("2"))
//20170619 add by zhangcn for P001-170606002
) //20170619 add by zhangcn for P001-170606002
.Union(
OOQL.Select(lstQueryProperties)
.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("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
//20170619 add by zhangcn for P001-170606002 ===begin===
.LeftJoin("SUPPLY_SYNERGY", "SUPPLY_SYNERGY")
.On(OOQL.CreateProperty("SUPPLY_SYNERGY.SUPPLY_SYNERGY_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.GROUP_SYNERGY_ID.ROid"))
.LeftJoin("PLANT", "PLANTSSY")
.On(OOQL.CreateProperty("PLANTSSY.PLANT_ID") ==
OOQL.CreateProperty("SUPPLY_SYNERGY.REQUIRE_Owner_Org.ROid"))
//20170619 add by zhangcn for P001-170606002 ===end===
.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")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.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("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("2", GeneralDBType.String) == OOQL.CreateConstants(scanType))
&
(OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO")
.In(OOQL.CreateDyncParameter("DOC_NO1", docNo)))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.GENERATE_STATUS") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.INVOICE_COMPANY_ID") ==
OOQL.CreateProperty("PLANTSSY.COMPANY_ID"))
//20170619 add by zhangcn for P001-170606002
& (OOQL.CreateProperty("SUPPLY_SYNERGY.GENERATE_DIRE") == OOQL.CreateConstants("2"))
//20170619 add by zhangcn for P001-170606002
)
); //20161216 add by liwei1 for P001-161215001
#endregion
return(OOQL.Select(lstQueryProperties)
.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("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.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")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.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("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("1") == OOQL.CreateConstants(scanType, GeneralDBType.String))
&
(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") ==
OOQL.CreateConstants(id, GeneralDBType.Guid))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(0, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
)
.Union(
OOQL.Select(lstQueryProperties)
.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("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.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")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.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("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("2", GeneralDBType.String) == OOQL.CreateConstants(scanType))
&
(OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO")
.In(OOQL.CreateDyncParameter("DOC_NO2", docNo)))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(0, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
)).Union(nodeAllSynery));
//20170619 add by zhangcn for P001-170606002 //20161216 add by liwei1 for P001-161215001
//& (OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateConstants(docNo, GeneralDBType.String))));//20161216 mark by liwei1 for P001-161215001
}
/// <summary>
/// 查询到货单查询信息
/// </summary>
/// <param name="programJobNo">作业编号</param>
/// <param name="id">主键</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="scanType">扫描类型1.箱条码 2.单据条码 3.料号</param>
/// <param name="docNo">单据编号</param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetTransferDocQueryNode(string programJobNo, string siteNo, string[] docNo, string scanType, string id, string status) //20161216 add by liwei1 for P001-161215001
//private QueryNode GetTransferDocQueryNode(string programJobNo, string siteNo, string docNo, string scanType, string id, string status) {//20161216 mark by liwei1 for P001-161215001
{
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC")) &
((OOQL.CreateProperty("TRANSFER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("TRANSFER_DOC_D.CLOSE") == OOQL.CreateConstants("0")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)));
if (scanType == "1")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_D_ID") ==
Formulas.Cast(OOQL.CreateConstants(id), GeneralDBType.Guid));
}
else if (scanType == "2")
{
//单据条码
conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));//20161216 add by liwei1 for P001-161215001
//conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO") == OOQL.CreateConstants(docNo));//20161216 mark by liwei1 for P001-161215001
}
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("TRANSFER_DOC.DOC_NO", "source_no"),
OOQL.CreateProperty("TRANSFER_DOC.DOC_DATE", "create_date"),
OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFERED_BUSINESS_QTY", "in_out_qty"),
Formulas.Cast(OOQL.CreateProperty("TRANSFER_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),
"outgoing_warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty),
"outgoing_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("TRANSFER_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_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
OOQL.CreateConstants(string.Empty, "first_in_first_out_control"),
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no")
)
.From("TRANSFER_DOC", "TRANSFER_DOC")
.InnerJoin("TRANSFER_DOC.TRANSFER_DOC_D", "TRANSFER_DOC_D")
.On(OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_ID") ==
OOQL.CreateProperty("TRANSFER_DOC.TRANSFER_DOC_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("TRANSFER_DOC_D.FROM_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.FROM_BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSFER_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("TRANSFER_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("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(conditionGroup);
return(queryNode);
}
/// <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="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));
}
/// <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);
}
private QueryNode GetPurchaseReturnNode(string programJobNo, string scanType, string ID, string siteNo, string status, string[] docNo)
{
string docType = programJobNo + status;
QueryConditionGroup whereNode = (OOQL.AuthFilter("PURCHASE_RETURN", "PURCHASE_RETURN")) &
(OOQL.CreateProperty("PURCHASE_RETURN.CATEGORY") == OOQL.CreateConstants("39")
& OOQL.CreateProperty("PURCHASE_RETURN.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUE_CLOSE") == OOQL.CreateConstants("0"));
switch (scanType)
{
case "1":
whereNode &= OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID") == OOQL.CreateConstants(ID);
break;
case "2":
whereNode &= OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo));
break;
}
QueryNode node = OOQL.Select(OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO", "source_no"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateProperty("PURCHASE_RETURN.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_RETURN_D.SequenceNumber", "seq"),
OOQL.CreateConstants(0, "doc_line_seq"),
OOQL.CreateConstants(0, "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"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUED_BUSINESS_QTY", "in_out_qty"),
OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no"),
OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),//20170302 modi by shenbao for P001-170302002 误差率统一乘100
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateConstants("N"), OOQL.CreateConstants("2")) }, "lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
new object[] {
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
new object[] {
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(0)
}), //单位转换率分子
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "inventory_unit"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_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
)
.From("PURCHASE_RETURN", "PURCHASE_RETURN")
.InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID"))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On(OOQL.CreateProperty("PURCHASE_RETURN.Owner_Org.ROid") == OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_RETURN.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
.Where(whereNode);
return(node);
}
private QueryNode GetSalesReturnQueryNode(string programJobNo, string scanType, string status, string[] docNos, string id, string siteNo)
{
QueryConditionGroup conditionGroup = OOQL.CreateProperty("SALES_RETURN.CATEGORY") == OOQL.CreateConstants("26") &
OOQL.CreateProperty("SALES_RETURN.ApproveStatus") == OOQL.CreateConstants("Y") &
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo);
if (scanType == "1")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("SALES_RETURN_D.SALES_RETURN_D_ID") == OOQL.CreateConstants(id));
}
else if (scanType == "2")
{
conditionGroup &= (OOQL.CreateProperty("SALES_RETURN.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNos)));
}
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"),
OOQL.CreateProperty("SALES_RETURN.DOC_NO", "source_no"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateProperty("SALES_RETURN.DOC_DATE", "create_date"),
OOQL.CreateProperty("SALES_RETURN_D.SequenceNumber", "seq"),
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"),
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"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("SALES_RETURN_D.RECEIPTED_BUSINESS_QTY", "in_out_qty"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE"), OOQL.CreateConstants(0m))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),//20170302 modi by shenbao for P001-170302002 误差率统一乘100
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("SALES_RETURN_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
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
// modi by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// modi by 08628 for P001-171023001 e
)
.From("SALES_RETURN", "SALES_RETURN")
.InnerJoin("SALES_RETURN.SALES_RETURN_D", "SALES_RETURN_D")
.On(OOQL.CreateProperty("SALES_RETURN_D.SALES_RETURN_ID") == OOQL.CreateProperty("SALES_RETURN.SALES_RETURN_ID"))
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On(OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("SALES_RETURN_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("SALES_RETURN_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID") &
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID"))
.LeftJoin("ITEM_SALES", "ITEM_SALES")
.On(OOQL.CreateProperty("ITEM_SALES.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID") &
OOQL.CreateProperty("ITEM_SALES.Owner_Org.ROid") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"))
// modi by 08628 for P001-171023001 b
.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))
// modi by 08628 for P001-171023001 e
.Where(conditionGroup);
return(queryNode);
}
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="receiptNo">到货单号</param>
/// <param name="paiSequenceNumber">单身序号集合</param>
/// <returns></returns>
private DataTable GetInsertPoArrivalInspection(string receiptNo, List <int> paiSequenceNumber)
{
string stringDefault = string.Empty;
object guidDefault = Maths.GuidDefaultValue();
const decimal decimalDefault = 0m;
QueryNode node =
OOQL.Select(true,
OOQL.CreateProperty("pad.SequenceNumber", "SequenceNumber"),
OOQL.CreateProperty("pa.ARRIVAL_DATE", "ARRIVAL_DATE"),
OOQL.CreateProperty("ip.INSPECT_DAYS", "INSPECT_DAYS"),
OOQL.CreateProperty("i.STOCK_UNIT_ID", "STOCK_UNIT_ID"),
OOQL.CreateProperty("i.SECOND_UNIT_ID", "SECOND_UNIT_ID"),
OOQL.CreateProperty("pad.PURCHASE_TYPE", "PURCHASE_TYPE"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "PO_ARRIVAL_INSPECTION_ID"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "DOC_ID"),
OOQL.CreateConstants("Q1", "CATEGORY"),
OOQL.CreateProperty("pa.DOC_DATE", "DOC_DATE"),
OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DOC_NO"),
Formulas.GetDate("INSPECTION_DATE"),
OOQL.CreateConstants(stringDefault, GeneralDBType.String, "INSPECTION_TIMES"),
OOQL.CreateProperty("pad.ITEM_ID", "ITEM_ID"),
OOQL.CreateProperty("pad.ITEM_DESCRIPTION", "ITEM_DESCRIPTION"),
OOQL.CreateProperty("pad.ITEM_FEATURE_ID", "ITEM_FEATURE_ID"),
OOQL.CreateProperty("pad.ITEM_SPECIFICATION", "ITEM_SPECIFICATION"),
OOQL.CreateProperty("pad.ITEM_LOT_ID", "ITEM_LOT_ID"),
OOQL.CreateProperty("pa.SUPPLIER_ID", "SUPPLIER_ID"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "INSPECTION_QTY"),
OOQL.CreateProperty("pad.BUSINESS_UNIT_ID", "INSPECTION_UNIT_ID"),
OOQL.CreateProperty("isdd.STRICTNESS_DEGREE", "STRICTNESS_DEGREE"),
Formulas.GetDate("INSPECTION_DUE_DATE"),
OOQL.CreateProperty("pa.Owner_Dept", "SUBMIT_DEPT_ID"),
OOQL.CreateProperty("pa.Owner_Emp", "SUBMIT_EMP_ID"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "INVENTORY_QTY"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "SECOND_QTY"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "ACCEPTABLE_QTY"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "UNQUALIFIED_QTY"),
Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "DESTROYED_QTY"),
OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DECISION"),
OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DECISION_DESCRIPTION"),
OOQL.CreateConstants("1", GeneralDBType.String, "RESULT_STATUS"),
OOQL.CreateConstants(stringDefault, GeneralDBType.String, "REMARK"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "INSPECTION_PLAN_ID"),
OOQL.CreateProperty("p.COMPANY_ID", "COMPANY_ID"),
OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_D_ID", "SOURCE_ID"),
Formulas.Case(null,
OOQL.CreateConstants(guidDefault, GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("pad.PURCHASE_TYPE") == OOQL.CreateConstants("3")),
OOQL.CreateProperty("pad.REFERENCE_SOURCE_ID.ROid"))), "MO_ROUTING_D_ID"),
OOQL.CreateProperty("pad.OPERATION_ID", "OPERATION_ID"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "TO_MO_ROUTING_D_ID"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "TO_OPERATION_ID"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "PROJECT_ID"),
OOQL.CreateConstants(false, GeneralDBType.Boolean, "DEDUCT_ARRIVED_QTY"),
OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid", "Owner_Org_ROid"),
OOQL.CreateConstants("PLANT", "Owner_Org_RTK"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "Owner_Emp"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "Owner_Dept"),
OOQL.CreateConstants("N", "ApproveStatus"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "CreateBy"),
OOQL.CreateConstants(DateTime.Now, "CreateDate"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "ModifiedBy"),
OOQL.CreateConstants(DateTime.Now, "ModifiedDate"),
Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "LastModifiedBy"),
OOQL.CreateConstants(DateTime.Now, "LastModifiedDate"))
.From("PURCHASE_ARRIVAL", "pa")
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "pad")
.On((OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("pa.PURCHASE_ARRIVAL_ID")))
.InnerJoin("ITEM", "i")
.On((OOQL.CreateProperty("i.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID")))
.LeftJoin("PLANT", "p")
.On((OOQL.CreateProperty("p.PLANT_ID") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid")))
.LeftJoin("ITEM_PLANT", "ip")
.On((OOQL.CreateProperty("ip.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID"))
& (OOQL.CreateProperty("ip.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid")))
.LeftJoin("ITEM_STRI_DEGREE", "isd")
.On((OOQL.CreateProperty("isd.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid"))
& (OOQL.CreateProperty("isd.SOURCE_ID.ROid") == OOQL.CreateProperty("pa.SUPPLIER_ID")))
.LeftJoin("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_D", "isdd")
.On((OOQL.CreateProperty("isdd.ITEM_STRI_DEGREE_ID") ==
OOQL.CreateProperty("isd.ITEM_STRI_DEGREE_ID"))
& (OOQL.CreateProperty("isdd.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID"))
& (OOQL.CreateProperty("isdd.ITEM_FEATURE_ID") == OOQL.CreateProperty("pad.ITEM_FEATURE_ID")))
.Where((OOQL.AuthFilter("PURCHASE_ARRIVAL", "pa"))
& (OOQL.CreateProperty("pa.DOC_NO") == OOQL.CreateConstants(receiptNo))
& (OOQL.CreateProperty("pad.SequenceNumber").In(OOQL.CreateDyncParameter("SequenceNumber", paiSequenceNumber))));
return(GetService <IQueryService>().Execute(node));
}
/// <summary>
/// 获取工单产出信息查询信息
/// </summary>
/// <param name="id">主键</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo"></param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetMOReceiptReqQueryNode(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("MO_RECEIPT_REQUISTION.DOC_NO", "source_no"), //来源单号
OOQL.CreateConstants(docType, "doc_type"), //单据类型
OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_DATE", "create_date"), //单据日期
Formulas.IsNull(OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.SequenceNumber"), OOQL.CreateConstants(0), "seq"), //单据项次
OOQL.CreateConstants(0, "doc_line_seq"), //单据项序
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
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"), //产品特征说明
OOQL.CreateConstants(string.Empty, "warehouse_no"), //库位
OOQL.CreateConstants(string.Empty, "storage_spaces_no"), //储位
OOQL.CreateConstants(string.Empty, "lot_no"), //批号
OOQL.CreateConstants(string.Empty, "object_no"), //对象编号
OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.REQUEST_QTY", "doc_qty"), //单据数量
OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.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("MO_RECEIPT_REQUISTION_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.BUSINESS_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
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
)
.From("MO_RECEIPT_REQUISTION.MO_RECEIPT_REQUISTION_D", "MO_RECEIPT_REQUISTION_D")
.InnerJoin("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION")
.On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.MO_RECEIPT_REQUISTION_ID") == OOQL.CreateProperty("MO_RECEIPT_REQUISTION.MO_RECEIPT_REQUISTION_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.ITEM_FEATURE_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("MO_RECEIPT_REQUISTION.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("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("MO_RECEIPT_REQUISTION.MO_RECEIPT_REQUISTION_D", "MO_RECEIPT_REQUISTION_D")) &
((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNo))) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO_RECEIPT_REQUISTION.ApproveStatus") == OOQL.CreateConstants("Y"))));
return(queryNode);
}
/// <summary>
/// 获取工单产出信息查询信息
/// </summary>
/// <param name="id">主键</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo"></param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetMOProductQueryNode(string id, string[] docNo, string siteNo, string programJobNo, string status) //20170724 modi by shenbao for P001-170717001 添加参数docno
{
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("MO_PRODUCT.PLAN_QTY", "doc_qty"),
Formulas.Case(null, OOQL.CreateProperty("MO_PRODUCT.COMPLETED_QTY"), new CaseItem[] {
new CaseItem(OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("9-1")
, OOQL.CreateProperty("MO_PRODUCT.REQ_QTY"))
}, "in_out_qty"), //20170724 modi by shenbao for P001-170717001
Formulas.Cast(OOQL.CreateProperty("MO_PRODUCT.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("MO.DOC_NO", "source_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
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("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants("9", "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateConstants(string.Empty, "warehouse_no"),
OOQL.CreateConstants(string.Empty, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "doc_line_seq"),
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.RECEIPT_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("MO_PRODUCT.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("MO_PRODUCT.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_PRODUCT.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===
// add by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
)
.From("MO.MO_PRODUCT", "MO_PRODUCT")
.LeftJoin("MO", "MO")
.On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_PRODUCT.MO_ID"))
.LeftJoin("MO.MO_D", "MO_D")
.On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_D.MO_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_FEATURE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")) //20170724 modi by shenbao for P001-170717001 用工单产出信息的单位
//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("MO.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("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("MO.MO_PRODUCT", "MO_PRODUCT")) &
((OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID") == OOQL.CreateConstants(id) | (OOQL.CreateProperty("MO.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)))) & //20170724 modi by shenbao for P001-170717001
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")) &
((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("9-1") & OOQL.CreateProperty("MO.RECEIPT_REQ_CONTROL") == OOQL.CreateConstants(true))
| (OOQL.CreateConstants(programJobNo) != OOQL.CreateConstants("9-1"))))); //20170724 add by shenbao for P001-170717001
return(queryNode);
}
private QueryNode GetFILPurchaseArrivalBcodeNode(string programJobNo, string status, string[] docNo)
{
QueryNode node = OOQL.Select(true,
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "source_no"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER.DOC_NO
OOQL.CreateConstants(programJobNo + status, "doc_type"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "create_date"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER.DOC_DATE
OOQL.CreateProperty("FIL_ARRIVAL_D.SequenceNumber", "seq"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER_D.SequenceNumber
OOQL.CreateConstants(0, "doc_line_seq"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER_SD.SequenceNumber
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
OOQL.CreateConstants(string.Empty, "object_no"), //对象编号
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"),
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE", "warehouse_no"),
OOQL.CreateConstants(string.Empty, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "in_out_qty"),
//20170331 add by wangrm for P001-170327001=======start==========
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "upper_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "upper_seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "upper_line_seq"),
OOQL.CreateConstants(0, "upper_batch_seq"),
//20170331 add by wangrm for P001-170327001=======end============
OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no"),
OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(100, GeneralDBType.Decimal), ArithmeticOperators.Mulit, "allow_error_rate"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1"),
new CaseItem[] { new CaseItem(OOQL.CreateConstants("N"), OOQL.CreateConstants("2")) }, "lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
new object[] {
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
new object[] {
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(0)
}), //单位转换率分子
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "inventory_unit"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_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
// modi by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// modi by 08628 for P001-171023001 e
)
.From("FIL_ARRIVAL", "FIL_ARRIVAL")
.InnerJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.On(OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID"))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On(OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID")
& OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_SE") == OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID")
& OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_SE_SE") == OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber"))
.LeftJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On(OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") == OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
// modi by 08628 for P001-171023001 b
.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))
// modi by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL"))
& (OOQL.CreateProperty("FIL_ARRIVAL_D.STATUS") == OOQL.CreateConstants("N"))
& OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));
return(node);
}
