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