//20170302 add by shenbao for B001-170221021 private DependencyObjectCollection QueryBCRecord(string siteNo, string barcode_no) { object propertyID = QueryItemLot(); object qtPropertyID = QueryItemQty(); //20170302 add by shenbao for P001-170302002 QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no") //企业编号 , OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点 , OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no") //条码编号 , OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号 , OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION") , ArithmeticOperators.Plus, "item_name_spec") //品名 , Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no") //产品特征码 , OOQL.CreateConstants(string.Empty, "warehouse_no") //库位 , OOQL.CreateConstants(string.Empty, "storage_spaces_no") //储位 , Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_VALUE"), OOQL.CreateConstants(string.Empty), "lot_no") //批号 //, OOQL.CreateConstants(0m, "inventory_qty") //数量 //20170302 mark by shenbao for P001-170302002 , Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_VALUE"), OOQL.CreateConstants(0), "barcode_qty") //20170302 ADD by shenbao for P001-170302002 ) .From("BC_RECORD", "BC_RECORD") .InnerJoin("ITEM") .On(OOQL.CreateProperty("BC_RECORD.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("BC_RECORD.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D") .On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D.BC_RECORD_ID") & OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_ID") == OOQL.CreateConstants(propertyID)) //20170302 add by shenbao for P001-170302002 ===begin=== .LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D_QTY") .On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D_QTY.BC_RECORD_ID") & OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_ID") == OOQL.CreateConstants(qtPropertyID)) //20170302 add by shenbao for P001-1703020002 ===end=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")) .InnerJoin("PLANT") .On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where((OOQL.AuthFilter("BC_RECORD", "BC_RECORD") & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) | OOQL.CreateConstants(siteNo) == OOQL.CreateConstants(string.Empty)) // 20171023 add by 08628 for P001-171023001 ↓ & (OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcode_no) | OOQL.CreateConstants(barcode_no) == OOQL.CreateConstants(string.Empty)) // 20171023 add by 08628 for P001-171023001 ↑ )); return(this.GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 获取select集合,直接跟返回集合要求字段一致 /// </summary> /// <returns></returns> private List <QueryProperty> GetSelectList(string programJobNo, string status) { List <QueryProperty> selectList = new List <QueryProperty>(); selectList.Add(OOQL.CreateConstants("99", "enterprise_no")); selectList.Add(OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no")); selectList.Add(OOQL.CreateConstants(programJobNo, "source_operation")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE.DOC_NO", "source_no")); selectList.Add(OOQL.CreateArithmetic(OOQL.CreateConstants(programJobNo), OOQL.CreateConstants(status) , ArithmeticOperators.Plus, "doc_type")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE.DOC_DATE", "create_date")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE_D.SequenceNumber", "seq")); selectList.Add(OOQL.CreateConstants(0, "doc_line_seq")); selectList.Add(OOQL.CreateConstants(0, "doc_batch_seq")); selectList.Add(OOQL.CreateConstants(string.Empty, "object_no")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no")); selectList.Add(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no")); selectList.Add(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name")); selectList.Add(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE", "warehouse_no")); selectList.Add(OOQL.CreateProperty("BIN.BIN_CODE", "storage_spaces_no")); selectList.Add(OOQL.CreateProperty("ITEM_LOT.LOT_CODE", "lot_no")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_QTY", "doc_qty")); selectList.Add(OOQL.CreateConstants(0M, GeneralDBType.Decimal, "in_out_qty")); selectList.Add(OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no")); selectList.Add(OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE", "allow_error_rate")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec")); selectList.Add(Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateConstants("N", GeneralDBType.String), OOQL.CreateConstants("2", GeneralDBType.String))), "lot_control_typ")); selectList.Add(Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) })); //单位转换率分母 selectList.Add(Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) })); //单位转换率分子 selectList.Add(Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit")); //库存单位 selectList.Add(OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places")); selectList.Add(OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type")); selectList.Add(OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")); return(selectList); }
/// <summary> /// 查询工单产出信息 /// </summary> /// <param name="queryService">查询服务</param> /// <param name="barcodeNo">条形码编号</param> /// <param name="siteNo">营运据点</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private DependencyObjectCollection GetMoProduct(IQueryService queryService, string barcodeNo, string siteNo, string programJobNo, string status) { QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no"), //企业编号//20170328 modi by wangyq for P001-170327001 添加distinct OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点 OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业 OOQL.CreateProperty("MO.DOC_NO", "source_no"), //来源单号 OOQL.CreateConstants(programJobNo + status, "doc_type"), //单据类型 OOQL.CreateProperty("MO.DOC_DATE", "create_date"), // 单据日期 OOQL.CreateProperty("MO_PRODUCT.SequenceNumber", "seq"), //单据项次 OOQL.CreateConstants(0, GeneralDBType.Int32, "doc_line_seq"), //单据项序 OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序 OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), //料件编号 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(""), "item_feature_no"), //产品特征 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(""), "item_feature_name"), //产品特征说明 OOQL.CreateConstants("", "warehouse_no"), //库位 OOQL.CreateConstants("", "storage_spaces_no"), //储位 OOQL.CreateConstants("", "lot_no"), // 批号 OOQL.CreateConstants("", "object_no"), // 对象编号 OOQL.CreateProperty("MO_PRODUCT.PLAN_QTY", "doc_qty"), //单据数量 OOQL.CreateProperty("MO_PRODUCT.COMPLETED_QTY", "in_out_qty"), //出入数量 Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "unit_no"), //单位 OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), //品名 OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), //规格 OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.RECEIPT_OVERRUN_RATE"), OOQL.CreateConstants(0m, GeneralDBType.Decimal)) , OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"), //允许误差率 //20170302 modi by shenbao for P001-170302002 误差率统一乘100 Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("")) == OOQL.CreateConstants("N"), OOQL.CreateConstants("2")) }, "lot_control_type"), 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(""), "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 ) .From("MO.MO_PRODUCT", "MO_PRODUCT") .InnerJoin("MO", "MO") .On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_PRODUCT.MO_ID")) //20170410 mark by wangrm SD口述===start=========== //.LeftJoin("MO.MO_D", "MO_D") //.On(OOQL.CreateProperty("MO_D.MO_ID") == OOQL.CreateProperty("MO.MO_ID")) //20170410 mark by wangrm SD口述===end=========== .InnerJoin("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")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")) //20170410 modi by wangrm SD口述OLD:MO_D->MO_PRODUCT .LeftJoin("ITEM_PLANT", "ITEM_PLANT") .On(OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID") & OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("MO.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")) //20170504 modi by wangyq for P001-170427001 ===============begin==================== .InnerJoin("BC_RECORD", "BC_RECORD") .On(OOQL.CreateProperty("BC_RECORD.SOURCE_D_ID.ROid") == OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID")) .Where(OOQL.AuthFilter("MO.MO_PRODUCT", "MO_PRODUCT") & OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcodeNo) & //.LeftJoin("BC_RECORD", "BC_RECORD") //.On(OOQL.CreateProperty("BC_RECORD.SOURCE_D_ID.ROid") == OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID")) //.Where(Formulas.IsNull(OOQL.CreateProperty("BC_RECORD.BARCODE_NO"), OOQL.CreateConstants("")) == OOQL.CreateConstants(barcodeNo) & //20170504 modi by wangyq for P001-170427001 ===============end==================== OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")); DependencyObjectCollection coll = queryService.ExecuteDependencyObject(node); return(coll); }
/// <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); }
/// <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 }
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=== ); }
/// <summary> /// 获取工单信息查询信息 /// </summary> /// <param name="docNo">单据编号</param> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <param name="status"></param> /// <returns></returns> private QueryNode GetMOQueryNode(string[] docNo, string siteNo, string programJobNo, string status) { //20161216 add by liwei1 for P001-161215001 //private QueryNode GetMOQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001 string docType = programJobNo + status; QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("MO.DOC_NO", "source_no"), OOQL.CreateProperty("MO.DOC_DATE", "create_date"), Formulas.Cast(OOQL.CreateProperty("MO_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), //OOQL.CreateProperty("MO_D.REQUIRED_QTY", "doc_qty"),//20161208 mark by shenbao fro P001-161208001 //OOQL.CreateProperty("MO_D.ISSUED_QTY", "in_out_qty"),//20161208 mark by shenbao fro P001-161208001 //20161208 add by shenbao fro P001-161208001 ===begin=== Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("MO_D.ISSUED_QTY"), new CaseItem[] { new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%")) , OOQL.CreateProperty("MO_D.REQUIRED_QTY")), }), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "doc_qty"), //需领用量 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants(0), new CaseItem[] { new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%")) , OOQL.CreateProperty("MO_D.ISSUED_QTY")), }), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "in_out_qty"), //已领用量 //20161208 add by shenbao fro P001-161208001 ===end=== 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"), //20170223 modi by shenbao for B001-170223012 这个企业编号为字符串,无需转换 OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateConstants(docType, "doc_type"), Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "doc_line_seq"), Formulas.Cast(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.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("MO_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("MO_D.UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("MO_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("MO_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("MO", "MO") .LeftJoin("MO.MO_D", "MO_D") .On(OOQL.CreateProperty("MO_D.MO_ID") == OOQL.CreateProperty("MO.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_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("MO_D.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("MO_D.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("MO_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_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("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(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("MO.MO_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO.MO_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("MO.MO_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("MO", "MO")) & ((OOQL.CreateProperty("MO.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) & //20161216 add by liwei1 for P001-161215001 //((OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001 (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")))); 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); }
/// <summary> /// 根据传入的供应商查询出所有的对账清单数据 /// </summary> /// <param name="supplierNo">供应商</param> /// <param name="dateS">对账开始日期</param> /// <param name="dateE">对账截止日期</param> /// <param name="siteNo">营运中心</param> /// <returns></returns> private QueryNode GetCheckListDetail(string supplierNo, string dateS, string dateE, string siteNo) { //如果结束日期为空格,空值,null时默认最大日期 if (Maths.IsEmpty(dateE.ToDate())) { dateE = OrmDataOption.EmptyDateTime1.ToStringExtension(); } //如果开始日期为空格,空值,null时默认最小日期 if (Maths.IsEmpty(dateS.ToDate())) { dateS = OrmDataOption.EmptyDateTime.ToStringExtension(); } JoinOnNode joinOnNode = OOQL.Select( OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"), OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001 #region //20170508 mark by liwei1 for P001-161209002 //OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "qty"), //Formulas.IsNull( // OOQL.CreateProperty("UNIT.UNIT_CODE"), // OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), //OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "price"), #endregion //20170508 add by liwei1 for P001-161209002 ---begin--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"), Formulas.IsNull( OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(" ", GeneralDBType.String), "unit_no"), Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(" ", GeneralDBType.String), "valuation_unit_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "valuation_qty"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.DISCOUNTED_PRICE", "price"), //20170508 add by liwei1 for P001-161209002 ---end--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"), OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"), #region //20170508 mark by liwei1 for P001-161209002 //OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY", "billing_qty"), //(OOQL.CreateArithmetic( // OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"), // OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty")), //OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC", "billing_amount"), //OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC") // + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")), // OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), ArithmeticOperators.Sub, "not_billing_amount")) #endregion //20170508 add by liwei1 for P001-161209002 ---begin--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY", "billing_qty"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty"), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray( OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC"))), "billing_amount"), OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray( OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")))), ArithmeticOperators.Sub, "not_billing_amount")) //20170508 add by liwei1 for P001-161209002 ---end--- .From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT") .InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D") .On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID"))) #region //20170508 mark by liwei1 for P001-161209002 //.InnerJoin( // OOQL.Select( // OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid", "SOURCE2_ID_ROid"), // OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "DISCOUNTED_PRICE_INTAX"), // Formulas.Sum( // OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), "PRICE_QTY"), // Formulas.Sum( // OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), "AMT_TC")) // .From("PAYABLE_DOC.PAYABLE_DOC_D", "PAYABLE_DOC_D") // .InnerJoin("PAYABLE_DOC", "PAYABLE_DOC") // .On((OOQL.CreateProperty("PAYABLE_DOC.PAYABLE_DOC_ID") ==OOQL.CreateProperty("PAYABLE_DOC_D.PAYABLE_DOC_ID"))) // .Where((OOQL.CreateProperty("PAYABLE_DOC.ApproveStatus") ==OOQL.CreateConstants("Y")) // &((OOQL.CreateProperty("PAYABLE_DOC.BOOKKEEPING_DATE") >=OOQL.CreateConstants(dateS.ToDate())) // &(OOQL.CreateProperty("PAYABLE_DOC.DOC_DATE") <=OOQL.CreateConstants(dateE.ToDate())))) // .GroupBy( // OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid"), // OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX")), "PAYABLE_DOC_D") //.On((OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID_ROid") ==OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID"))) #endregion //20170508 add by liwei1 for P001-161209002 ---gebin--- .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID"))) .InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID"))) .LeftJoin("UNIT", "B_UNIT") .On((OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_UNIT_ID"))) //20170508 add by liwei1 for P001-161209002 ---end--- .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid"))) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID"))) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID"))) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001 .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001 .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID"))) .InnerJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID"))); #region 20171010 add by zhangcn for B001-171010004 QueryConditionGroup conditionGroup2 = (OOQL.AuthFilter("PURCHASE_ISSUE", "PURCHASE_ISSUE")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) & (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) & (OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") == OOQL.CreateConstants("Y")); //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup2 &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } QueryNode unioNode = OOQL.Select(OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_ISSUE.DOC_NO", "stock_in_no"), OOQL.CreateProperty("PURCHASE_ISSUE_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001 //OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY", "qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "qty"), Formulas.IsNull(OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY", "valuation_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "valuation_qty"), OOQL.CreateProperty("PURCHASE_RETURN_D.DISCOUNTED_PRICE", "price"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "amount"), //OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"), // OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"), // ArithmeticOperators.Plus, // "tax_amount"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "tax_amount"), OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY","billing_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "billing_qty"), //OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), // ArithmeticOperators.Sub, // "not_billing_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY") - OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "not_billing_qty"), //Formulas.Case(null, // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), // OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))), // "billing_amount"), OOQL.CreateArithmetic((Formulas.Case(null, OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))) ) ), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "billing_amount"), //OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")), // Formulas.Case(null, // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), // OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))), // ArithmeticOperators.Sub, // "not_billing_amount")) OOQL.CreateArithmetic((OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))), ArithmeticOperators.Sub)), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "not_billing_amount")) .From("PURCHASE_ISSUE", "PURCHASE_ISSUE") .InnerJoin("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D") .On(OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID")) .InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D") .On(OOQL.CreateProperty("PURCHASE_ISSUE_D.SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID")) .InnerJoin("PURCHASE_RETURN", "PURCHASE_RETURN") .On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID")) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On(OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID")) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001 .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001 .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_UNIT_ID")) .LeftJoin("UNIT", "B_UNIT") .On(OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_UNIT_ID")) .InnerJoin("SUPPLIER", "SUPPLIER") .On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.SUPPLIER_ID")) .Where(conditionGroup2); #endregion //初始Where条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")) & ((OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) //20170508 add by liwei1 for P001-161209002 & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) //20170508 add by liwei1 for P001-161209002 ); //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } return(joinOnNode.Where(conditionGroup).Union(unioNode));//20171010 modi by zhangcn for B001-171010004 【增加.Union(unioNode)】 }
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); }
/// <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 GetPurchaseArrivalQueryNode(string programJobNo, string id, string siteNo, string scanType, string[] docNo, string status) //20161216 add by liwei1 for P001-161215001 //private QueryNode GetPurchaseArrivalQueryNode(string programJobNo, string id, string siteNo, string scanType, string docNo, string status) {//20161216 mark by liwei1 for P001-161215001 { QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")) & ((OOQL.CreateProperty("PURCHASE_ARRIVAL.CATEGORY") == OOQL.CreateConstants("36")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPT_CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))); if (scanType == "1") { //箱条码 conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == Formulas.Cast(OOQL.CreateConstants(id), GeneralDBType.Guid)); } else if (scanType == "2") { //单据条码 conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));//20161216 add by liwei1 for P001-161215001 //conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO") == OOQL.CreateConstants(docNo));//20161216 mark by liwei1 for P001-161215001 } string docType = programJobNo + status; QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO", "source_no"), OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY", "doc_qty"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPTED_BUSINESS_QTY", "in_out_qty"), Formulas.Cast(OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_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")//20170903 mark by liwei1 for B001-170904001 OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "object_no") //20170903 add by liwei1 for B001-170904001 //20161226 add by liwei1 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_PURCHASE.RECEIPT_OVER_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("PURCHASE_ARRIVAL_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 //20161226 add by liwei1 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 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("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL") .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .On(OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID")) .InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER") .On(OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.Owner_Org.ROid")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID")) //20161226 add by liwei1 for P001-161215001 ===begin=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) //20170117 add by liwei1 for B001-170117019 //& OOQL.CreateProperty("PURCHASE_ARRIVAL.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))//20170117 mark by liwei1 for B001-170117019 .LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE") .On(OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID") & OOQL.CreateProperty("PURCHASE_ARRIVAL.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")) //20161226 add by liwei1 for P001-161215001 ===end=== .LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001 .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.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(conditionGroup); return(queryNode); }
public DependencyObjectCollection QuerySource(string counting_type, string counting_no, string site_no) { //20170209 modi by wangyq for B001-170206023 去除仓库 string warehouse_no, QueryNode node = OOQL.Select(OOQL.CreateConstants("99", "enterprise_no") //企业编号 , OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点 , OOQL.CreateProperty("PLANT.PLANT_ID", "plant_id") //工厂 , OOQL.CreateProperty("COUNTING_PLAN.DOC_NO", "counting_no") //盘点计划编号 , OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号 , OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION") , ArithmeticOperators.Plus, "item_name_spec") //品名 , Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_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.CreateProperty("COUNTING_PLAN.BO_ID.RTK", "transaction_type") //交易对象类型 , Formulas.Case(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.RTK"), OOQL.CreateConstants(""), new CaseItem[] { new CaseItem(OOQL.CreateConstants("CUSTOMER"), OOQL.CreateProperty("CUSTOMER.CUSTOMER_CODE")), new CaseItem(OOQL.CreateConstants("SUPPLIER"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE")), new CaseItem(OOQL.CreateConstants("EMPLOYEE"), OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE")), new CaseItem(OOQL.CreateConstants("OTHER_BO"), OOQL.CreateProperty("OTHER_BO.OTHER_BO_CODE")), }, "transaction_no") //交易对象编号 , OOQL.CreateProperty("COUNTING_PLAN.BOOK_QTY", "inventory_qty") //库存数量 ) .From("COUNTING_PLAN", "COUNTING_PLAN") .InnerJoin("PLANT") .On(OOQL.CreateProperty("COUNTING_PLAN.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("ITEM") .On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("COUNTING_PLAN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("COUNTING_PLAN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("CUSTOMER") .On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID")) .LeftJoin("SUPPLIER") .On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID")) .LeftJoin("EMPLOYEE") .On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")) .LeftJoin("OTHER_BO") .On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("OTHER_BO.OTHER_BO_ID")) .Where(OOQL.AuthFilter("COUNTING_PLAN", "COUNTING_PLAN") & (OOQL.CreateProperty("COUNTING_PLAN.DOC_NO") == OOQL.CreateConstants(counting_no) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no) | OOQL.CreateConstants(site_no) == OOQL.CreateConstants(string.Empty)) //& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)//20170209 mark by wangyq for B001-170206023 新需求 & OOQL.CreateProperty("COUNTING_PLAN.ApproveStatus") == OOQL.CreateConstants("N"))); return(this.GetService <IQueryService>().ExecuteDependencyObject(node)); }
private QueryNode GetSalesListDetail(string supplierNo, string dateS, string dateE, string siteNo) { JoinOnNode joinOnNode = OOQL.Select( OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "supplier_no"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "supplier_name"), OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_CONTACT_NAME", "employee_no"), Formulas.IsNull( OOQL.CreateProperty("SUPPLIER_CONTACT.TELEPHONE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "supplier_telephone_number"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"), OOQL.CreateProperty("PLANT.TELEPHONE", "telephone_number"), OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), //20171010 add by zhangcn for B001-171010004 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), //20171010 add by zhangcn for B001-171010004 Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PRICE", "price"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"), OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq")) .From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT") .InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D") .On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID"))) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid"))) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID"))) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID"))) .InnerJoin("SUPPLIER_PURCHASE", "SUPPLIER_PURCHASE") .On((OOQL.CreateProperty("SUPPLIER_PURCHASE.Owner_Org.ROid") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")) & (OOQL.CreateProperty("SUPPLIER_PURCHASE.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID"))) .LeftJoin("SUPPLIER_PURCHASE.SUPPLIER_CONTACT", "SUPPLIER_CONTACT") .On((OOQL.CreateProperty("SUPPLIER_CONTACT.SUPPLIER_BUSINESS_ID") == OOQL.CreateProperty("SUPPLIER_PURCHASE.SUPPLIER_PURCHASE_ID")) & (OOQL.CreateProperty("SUPPLIER_CONTACT.MIAN_ORDER_CONTACT") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID"))) .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID"))) .InnerJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID"))) .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID"))) //20171010 add by zhangcn for B001-171010004 ========================begin========================= .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")); //20171010 add by zhangcn for B001-171010004 ========================end========================= //如果结束日期为空格,空值,null时默认最大日期 if (Maths.IsEmpty(dateE.ToDate())) { dateE = OrmDataOption.EmptyDateTime1.ToStringExtension(); } //如果开始日期为空格,空值,null时默认最小日期 if (Maths.IsEmpty(dateS.ToDate())) { dateS = OrmDataOption.EmptyDateTime.ToStringExtension(); } //初始Where条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")) & (OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())); //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } return(joinOnNode.Where(conditionGroup)); }
/// <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)); }
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); }
/// <summary> /// 获取返回值单头与receipt_list的信息 /// </summary> /// <param name="barcodeNo"></param> /// <returns></returns> private DependencyObjectCollection GetPurchaseArrival(string barcodeNo) { JoinOnNode joinOnNode = OOQL.Select( Formulas.IsNull( OOQL.CreateProperty("ITEM_STRI_DEGREE_D.STRICTNESS_DEGREE"), OOQL.CreateConstants("1", GeneralDBType.String), "STRICTNESS_DEGREE"), OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_ID", "ITEM_ID"), OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid", "RECEIVE_Owner_Org_ROid"), OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.OPERATION_ID", "OPERATION_ID"), OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull( OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), Formulas.IsNull( OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY"), ArithmeticOperators.Sub, "receipt_qty"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY"), ArithmeticOperators.Sub, "ok_qty"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "unqualified_qty"), //20170801 add by liwei1 for P001-170717001 OOQL.CreateConstants(0m, GeneralDBType.Decimal, "checkdestroy_qty"), //20170801 add by liwei1 for P001-170717001 OOQL.CreateConstants(string.Empty, "result_type"), OOQL.CreateConstants(string.Empty, "qc_group"), OOQL.CreateConstants(string.Empty, "qc_degree"), OOQL.CreateConstants(string.Empty, "qc_level"), OOQL.CreateConstants(string.Empty, "qc_type"), OOQL.CreateProperty("FIL_ARRIVAL_D.DOC_NO", "delivery_no"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "supplier_no"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "supplier_name"), OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO", "receipt_no"), OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_DATE", "receipt_date")) .From("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL") .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_ID"))) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_FEATURE_ID"))) .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))) .LeftJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D") .On((OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID"))) .LeftJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.SUPPLIER_ID") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID"))) .LeftJoin("ITEM_PLANT", "ITEM_PLANT") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))) .LeftJoin("MO_ROUTING.MO_ROUTING_D", "MO_ROUTING_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK") == OOQL.CreateConstants("MO_ROUTING.MO_ROUTING_D")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.ROid") == OOQL.CreateProperty("MO_ROUTING_D.MO_ROUTING_D_ID"))) .LeftJoin("ITEM_STRI_DEGREE", "ITEM_STRI_DEGREE") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_STRI_DEGREE.Owner_Org.ROid")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.SUPPLIER_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE.SOURCE_ID.ROid"))) .LeftJoin("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_D", "ITEM_STRI_DEGREE_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_ID")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_STRI_DEGREE_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_ID"))); //共同存在的条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.CATEGORY") == OOQL.CreateConstants("36")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPT_CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO") == OOQL.CreateConstants(barcodeNo)) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY")); //主查询中增加不一样的条件 QueryConditionGroup conditionGroupUnionOne = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") == OOQL.CreateConstants("3")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS") != OOQL.CreateConstants("1")) & OOQL.CreateProperty("MO_ROUTING_D.INSPECT_MODE") == OOQL.CreateConstants("2"); //第一个Union中增加不一样的条件 QueryConditionGroup conditionGroupUnionTwo = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") != OOQL.CreateConstants("3")) & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS") != OOQL.CreateConstants("1")) & OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE") == OOQL.CreateConstants("2") & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK") != OOQL.CreateConstants("WIP")); //第二个Union中增加不一样的条件 QueryConditionGroup conditionGroupUnionThree = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") == OOQL.CreateConstants("3")) & ((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS")) != OOQL.CreateConstants("1")) & (OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE")) == OOQL.CreateConstants("2") & ((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK")) == OOQL.CreateConstants("WIP")); //组合node QueryNode queryNode = joinOnNode.Where(conditionGroupUnionOne) .Union(joinOnNode.Where(conditionGroupUnionTwo), true) .Union(joinOnNode.Where(conditionGroupUnionThree), true); return(GetService <IQueryService>().ExecuteDependencyObject(queryNode)); }