/// <summary> /// 根据传入的不良原因编号,获取相应的不良原因信息 /// </summary> /// <param name="reasonCode">入参理由码</param> /// <param name="siteNo"></param> /// <returns></returns> private DependencyObjectCollection GetDefectiveReasons(string reasonCode,string siteNo) { //拼接where条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("DEFECTIVE_REASONS", "DEFECTIVE_REASONS")) & (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_TYPE").In( OOQL.CreateConstants("1"), OOQL.CreateConstants("2"), OOQL.CreateConstants("6"))) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); //IF 【入参理由码reason_code】不为空 THEN if (!Maths.IsEmpty(reasonCode)){ conditionGroup &= (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE") ==OOQL.CreateConstants(reasonCode)); } //组合node QueryNode node = OOQL.Select( OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE", "reason_code"), OOQL.CreateProperty("DEFECTIVE_REASONS.DESCRIPTION", "reason_code_name")) .From("DEFECTIVE_REASONS", "DEFECTIVE_REASONS") .InnerJoin("PLANT","PLANT") .On(OOQL.CreateProperty("DEFECTIVE_REASONS.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(conditionGroup); //查询数据并返回 return GetService<IQueryService>().ExecuteDependencyObject(node); }
public QueryNode GetFILPurchaseArrivalListNode(string programJobNo, DependencyObjectCollection condition) { QueryConditionGroup group = (OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL")) & (OOQL.CreateProperty("FIL_ARRIVAL.STATUS") == OOQL.CreateConstants("2")); if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("FIL_ARRIVAL.DOC_NO", "FIL_ARRIVAL.DOC_DATE", new string[] { "1", "2", "5" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } QueryNode node = OOQL.Select(true, OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "doc_no"), OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_FULL_NAME", "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), OOQL.CreateConstants(string.Empty, "employee_name")) .From("FIL_ARRIVAL", "FIL_ARRIVAL") .LeftJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D") .On(OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID")) .LeftJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On(OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO")) .LeftJoin("SUPPLIER", "SUPPLIER") .On(OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID")) .Where(group); return(node); }
/// <summary> /// 获取工单信息查询信息 /// </summary> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetMOQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("MO.DOC_NO", "doc_no"), OOQL.CreateProperty("MO.DOC_DATE", "create_date"), Formulas.Case(null, OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("MO.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")), OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))), "customer_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name")) .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("WORK_CENTER", "WORK_CENTER") .On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid")) .LeftJoin("SUPPLIER", "SUPPLIER") .On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("MO.Owner_Emp")) .LeftJoin("DOC", "DOC") .On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("MO.DOC_ID")) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("MO.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //.Where(//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group = //20170328 add by wangyq for P001-170327001 //(OOQL.AuthFilter("MO", "MO")) & (Where(//20170328 mark by wangyq for P001-170327001 (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("DOC.CATEGORY").In(OOQL.CreateConstants("51"), OOQL.CreateConstants("52"), OOQL.CreateConstants("53"), OOQL.CreateConstants("54"))) & (OOQL.CreateProperty("MO.LOT_MO_FLAG") == OOQL.CreateConstants(false)) & (OOQL.CreateProperty("MO.STATUS").In(OOQL.CreateConstants("1"), OOQL.CreateConstants("2"), OOQL.CreateConstants("3"))) & ((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("MO_D.REQUIRED_QTY") > OOQL.CreateProperty("MO_D.ISSUED_QTY")) | (OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("MO_D.ISSUED_QTY") > OOQL.CreateConstants(0))); //));//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO.DOC_NO", "MO.DOC_DATE", new string[] { "1", "2", "3", "4", "5" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("MO", "MO") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(queryNode); }
//20170328 add by wangyq for P001-170327001 /// <summary> /// /// </summary> /// <param name="group">原来查询的条件,必须传入,避免拼接半天太麻烦</param> /// <param name="condition"></param> /// <returns></returns> public static QueryConditionGroup CreateNewConditionByParameter(QueryConditionGroup group, DependencyObjectCollection condition, ConditionPropertyNameEntity conPropertyEntity) { if (condition.ItemDependencyObjectType.Properties.Contains("seq") && condition.ItemDependencyObjectType.Properties.Contains("value")) //都没有设置的时候会没有传入这个字段 { foreach (DependencyObject conObj in condition) { string leftName = string.Empty; bool isEmptyValue; if (conObj["seq"].ToStringExtension() == "2") //日期类型要转换为日期后判断 { isEmptyValue = Maths.IsEmpty(conObj["value"].ToDate()); } else { isEmptyValue = Maths.IsEmpty(conObj["value"]); } if (!isEmptyValue && conPropertyEntity.ContainProperty.Length > 0 && conPropertyEntity.ContainProperty.Contains(conObj["seq"].ToStringExtension())) { switch (conObj["seq"].ToStringExtension()) { case "1": //单号 leftName = conPropertyEntity.DocNoName; break; case "2": //日期 group &= OOQL.CreateProperty(conPropertyEntity.DocDateName) >= OOQL.CreateConstants(conObj["value"].ToDate(), GeneralDBType.Date); break; case "3": //人员 leftName = conPropertyEntity.EmployeeName; break; case "4": //部门 leftName = conPropertyEntity.AdminUnitName; break; case "5": //供应商 leftName = conPropertyEntity.SupplierName; break; case "6": //客户 leftName = conPropertyEntity.CustomerName; break; default: break; } if (!string.IsNullOrEmpty(leftName)) { group &= OOQL.CreateProperty(leftName).Like(OOQL.CreateConstants("%" + conObj["value"].ToString() + "%", GeneralDBType.String)); } } } } return(group); }
/// <summary> /// 获取销货单QueryNode /// </summary> /// <param name="siteNo">工厂</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetSalesDeliveryQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode queryNode = //20170328 modi by wangyq for P001-170327001 old: return OOQL.Select(true, //20170104 add by wangyq for P001-161215001 OOQL.CreateProperty("SALES_DELIVERY.DOC_NO", "doc_no"), OOQL.CreateProperty("SALES_DELIVERY.DOC_DATE", "create_date"), OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"), OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"), Formulas.IsNull( OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"), OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization")) .From("SALES_DELIVERY", "SALES_DELIVERY") .InnerJoin("SALES_CENTER", "SALES_CENTER") .On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Org.ROid"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_DELIVERY.PLANT_ID"))) .LeftJoin("CUSTOMER", "CUSTOMER") .On((OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_DELIVERY.CUSTOMER_ID"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Emp"))) //20170104 add by wangyq for P001-161215001 ================begin===================== .LeftJoin("SALES_DELIVERY.SALES_DELIVERY_D", "SALES_DELIVERY_D") .On(OOQL.CreateProperty("SALES_DELIVERY.SALES_DELIVERY_ID") == OOQL.CreateProperty("SALES_DELIVERY_D.SALES_DELIVERY_ID")) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("SALES_DELIVERY.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //20170104 add by wangyq for P001-161215001 ================end===================== //.Where( //20170328 mark by wangyq for P001-170327001 //20170104 add by wangyq for P001-161215001 ================begin===================== //OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") &//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group = //20170328 add by wangyq for P001-170327001 (OOQL.CreateProperty("SALES_DELIVERY_D.ISSUED") == OOQL.CreateConstants("0", GeneralDBType.String) & //20170104 add by wangyq for P001-161215001 ================end===================== (OOQL.CreateProperty("SALES_DELIVERY.ApproveStatus") == OOQL.CreateConstants("Y", GeneralDBType.String)) & (OOQL.CreateProperty("SALES_DELIVERY.CATEGORY") == OOQL.CreateConstants("24", GeneralDBType.String)) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo, GeneralDBType.String))); //); //20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_DELIVERY.DOC_NO", "SALES_DELIVERY.DOC_DATE", new string[] { "1", "2", "3", "4", "6" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") & (group)); return(queryNode); //20170328 add by wangyq for P001-170327001 ================end============== }
/// <summary> /// 拼接查询条件 /// </summary> /// <param name="siteNo"></param> /// <param name="condition"></param> /// <returns></returns> private QueryConditionGroup CreateCondition(string siteNo, DependencyObjectCollection condition) { QueryConditionGroup group = OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("SALES_ISSUE.ApproveStatus") == OOQL.CreateConstants("N", GeneralDBType.String) & OOQL.CreateProperty("SALES_ISSUE_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1", GeneralDBType.String); if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_ISSUE.DOC_NO", "SALES_ISSUE.DOC_DATE", new string[] { "1", "2", "3", "4", "6" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } return(group); }
private QueryNode GetIssueReceiptReqListNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode node = OOQL.Select( OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO", "doc_no"), OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_DATE", "create_date"), Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER") , OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME")) }), OOQL.CreateConstants(string.Empty), "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull( OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")) .From("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ") .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("WORK_CENTER") .On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.ROid") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID"))) .LeftJoin("SUPPLIER") .On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.ROid") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //.Where((OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")) &//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group =//20170328 add by wangyq for P001-170327001 (OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("ISSUE_RECEIPT_REQ.CLOSE") == OOQL.CreateConstants("0", GeneralDBType.String)//20170104 add by wangyq for P001-161215001 ); //);//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("ISSUE_RECEIPT_REQ.DOC_NO", "ISSUE_RECEIPT_REQ.DOC_DATE", new string[] { "1", "2", "3", "4", "5" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } node = ((JoinOnNode)node).Where(OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(node); }
/// <summary> /// 获取库存交易单查询信息 /// </summary> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetTransactionDocQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC")) & (((OOQL.CreateProperty("TRANSACTION_DOC.CATEGORY") == OOQL.CreateConstants("11")) & (OOQL.CreateProperty("TRANSACTION_DOC.ApproveStatus") == OOQL.CreateConstants("N")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)))); if (programJobNo == "11") { //箱条码 conditionGroup &= (OOQL.CreateProperty("TRANSACTION_DOC.STOCK_ACTION") == OOQL.CreateConstants(-1)); } else if (programJobNo == "12") { //单据条码 conditionGroup &= (OOQL.CreateProperty("TRANSACTION_DOC.STOCK_ACTION") == OOQL.CreateConstants(1)); } QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "doc_no"), OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE", "create_date"), Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name")) .From("TRANSACTION_DOC", "TRANSACTION_DOC") .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid")) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Dept")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Emp")); //.Where(conditionGroup);//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSACTION_DOC.DOC_NO", "TRANSACTION_DOC.DOC_DATE", new string[] { "1", "2", "3", "4" }); conditionGroup = UtilsClass.CreateNewConditionByParameter(conditionGroup, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(conditionGroup); //20170328 add by wangyq for P001-170327001 ================end============== return(queryNode); }
/// <summary> /// 获取寄售订单数据 /// </summary> /// <param name="siteNo">工厂</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private DependencyObjectCollection GetSalesOrder(string siteNo, string programJobNo, DependencyObjectCollection condition) { QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO", "doc_no"), OOQL.CreateProperty("SALES_ORDER_DOC.DOC_DATE", "create_date"), Formulas.IsNull( OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull( OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"), Formulas.IsNull( OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE"), OOQL.CreateConstants(string.Empty), "main_organization")) .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("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PARTNER_ID.ROid"))) .InnerJoin("SALES_CENTER", "SALES_CENTER") .On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid"))) .LeftJoin("CUSTOMER", "CUSTOMER") .On((OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.CUSTOMER_ID"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Emp"))) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On((OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Dept"))); QueryConditionGroup group = ((OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B")) & (OOQL.CreateProperty("SALES_ORDER_DOC.CLOSE") == OOQL.CreateConstants("0"))); if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_ORDER_DOC.DOC_NO", "SALES_ORDER_DOC.DOC_DATE", new string[] { "1", "2", "3", "4", "6" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC") & (group)); return(GetService <IQueryService>().ExecuteDependencyObject(queryNode)); }
public QueryNode GetMOListNode(string siteNo, string programJobNo , DependencyObjectCollection condition) { QueryNode node = OOQL.Select(OOQL.CreateProperty("MO.DOC_NO", "doc_no"), OOQL.CreateProperty("MO.DOC_DATE", "create_date"), OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME", "customer_name"), OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"), Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization") ) .From("MO", "MO") .InnerJoin("PLANT") .On(OOQL.CreateProperty("MO.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("ITEM") .On(OOQL.CreateProperty("MO.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("MO.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("UNIT") .On(OOQL.CreateProperty("MO.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .LeftJoin("WORK_CENTER") .On(OOQL.CreateProperty("MO.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER") & OOQL.CreateProperty("MO.SOURCE_ID.ROid") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID")) .LeftJoin("ADMIN_UNIT") .On(OOQL.CreateProperty("MO.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")) .LeftJoin("EMPLOYEE") .On(OOQL.CreateProperty("MO.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")); QueryConditionGroup group = (OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("MO.STATUS").NotIn(OOQL.CreateConstants("Y", GeneralDBType.String), OOQL.CreateConstants("y", GeneralDBType.String)) & OOQL.CreateProperty("MO.RECEIPT_REQ_CONTROL") == OOQL.CreateConstants(false) ); if (condition != null && condition.Count > 0) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO.DOC_NO", "MO.DOC_DATE", new string[] { "1", "2", "3", "4" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } node = ((JoinOnNode)node).Where(OOQL.AuthFilter("MO", "MO") & (group)); return(node); }
/// <summary> /// 获取销退单通知 /// </summary> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetSalesReturnListQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"), OOQL.CreateProperty("SALES_RETURN.DOC_NO", "doc_no"), OOQL.CreateProperty("SALES_RETURN.DOC_DATE", "create_date"), OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name")) .From("SALES_RETURN", "SALES_RETURN") .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID")) .InnerJoin("SALES_CENTER", "SALES_CENTER") .On(OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid")) .InnerJoin("CUSTOMER", "CUSTOMER") .On(OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_RETURN.CUSTOMER_ID")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Emp")) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("SALES_RETURN.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //.Where(//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group = //20170328 add by wangyq for P001-170327001 OOQL.CreateProperty("SALES_RETURN.CATEGORY") == OOQL.CreateConstants("26") & OOQL.CreateProperty("SALES_RETURN.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("SALES_RETURN.RECEIPTED_STATUS").In(OOQL.CreateConstants("1"), OOQL.CreateConstants("2")); //);//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_RETURN.DOC_NO", "SALES_RETURN.DOC_DATE", new string[] { "1", "2", "3", "4", "6" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_RETURN", "SALES_RETURN") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(queryNode); }
/// <summary> /// //7.1定义单据类型 /// </summary> /// <param name="recommended_operations"></param> /// <param name="siteNo"></param> private DependencyObjectCollection ValidateDocSet(string stockAction, string siteNo) { QueryConditionGroup group = OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("PARA_DOC_FIL.CATEGORY") == OOQL.CreateConstants("11"); if (!string.IsNullOrEmpty(stockAction)) { group &= OOQL.CreateProperty("PARA_DOC_FIL.STOCK_ACTION") == OOQL.CreateConstants(stockAction); } QueryNode node = OOQL.Select(OOQL.CreateProperty("DOC.DOC_ID"), OOQL.CreateProperty("DOC.SEQUENCE_DIGIT")) .From("PARA_DOC_FIL", "PARA_DOC_FIL") .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PARA_DOC_FIL.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("DOC", "DOC") .On(OOQL.CreateProperty("PARA_DOC_FIL.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID")) .Where(OOQL.AuthFilter("PARA_DOC_FIL", "PARA_DOC_FIL") & (group)); return(this.GetService <IQueryService>().ExecuteDependencyObject(node)); }
private QueryNode GetTransferReqListNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode node = OOQL.Select(true,//20170104 add by wangyq for P001-161215001 OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO", "doc_no"), OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_DATE", "create_date"), Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull( OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")) .From("TRANSFER_REQUISITION", "TRANSFER_REQUISITION") .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("ADMIN_UNIT") .On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))) //20170104 add by wangyq for P001-161215001 ================begin===================== .LeftJoin("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D", "TRANSFER_REQUISITION_D") .On(OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANSFER_REQUISITION_ID")); //20170104 add by wangyq for P001-161215001 ================end===================== //.Where((OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")) & //20170328 mark by wangyq for P001-170327001 QueryConditionGroup group =//20170328 add by wangyq for P001-170327001 (OOQL.CreateProperty("TRANSFER_REQUISITION.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("TRANSFER_REQUISITION_D.CLOSE") == OOQL.CreateConstants("0", GeneralDBType.String)//20170104 add by wangyq for P001-161215001 ); //);//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSFER_REQUISITION.DOC_NO", "TRANSFER_REQUISITION.DOC_DATE", new string[] { "1", "2", "3", "4" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } node = ((JoinOnNode)node).Where(OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(node); }
/// <summary> /// 更新批号 /// </summary> /// <param name="item_no">料件编号</param> /// <param name="item_feature_no">产品特征</param> /// <param name="site_no">营运据点</param> /// <param name="lot_no">批号</param> /// <param name="lot_description">批号说明</param> /// <param name="effective_date">生效日期</param> /// <param name="effective_deadline">有效截止日</param> /// <param name="remarks">备注</param> /// <returns></returns> public void UpdateLot(string item_no, string item_feature_no, string site_no, string lot_no, string lot_description, string effective_date, string effective_deadline, string remarks) { QueryNode selectNode = OOQL.Select(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .From("ITEM_LOT", "ITEM_LOT") .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")); QueryConditionGroup conditionGroup = OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateConstants(item_no, GeneralDBType.String) & OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateConstants(lot_no, GeneralDBType.String); DateTime dtEffection = effective_date.ToDate(); DateTime dtInEffection = effective_deadline.ToDate(); if (!string.IsNullOrEmpty(item_feature_no)) { conditionGroup &= OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE") == OOQL.CreateConstants(item_feature_no, GeneralDBType.String); //20170228 mark by shenbao for B001-170206007 将这里的语句往下放 ===begin === //selectNode = ((JoinOnNode)selectNode).InnerJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") // .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") // & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) // .Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup)); //20170228 mark by shenbao for B001-170206007 ===end === } //20170228 add by shenbao for B001-170206007 ===begin === selectNode = ((JoinOnNode)selectNode).LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20170228 modi by shenbao for B001-170206007 InnerJoin==>LeftJoin .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup)); //20170228 add by shenbao for B001-170206007 ===end === List <SetItem> updateList = new List <SetItem>(); updateList.Add(new SetItem(OOQL.CreateProperty("LOT_DESCRIPTION"), OOQL.CreateConstants(lot_description, GeneralDBType.String))); updateList.Add(new SetItem(OOQL.CreateProperty("EFFECTIVE_DATE"), OOQL.CreateConstants(dtEffection, GeneralDBType.Date))); updateList.Add(new SetItem(OOQL.CreateProperty("INEFFECTIVE_DATE"), OOQL.CreateConstants(dtInEffection, GeneralDBType.Date))); updateList.Add(new SetItem(OOQL.CreateProperty("REMARK"), OOQL.CreateConstants(remarks, GeneralDBType.String))); QueryNode node = OOQL.Update("ITEM_LOT").Set(updateList.ToArray()) .From(selectNode, "selectNode") .Where(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("selectNode.ITEM_LOT_ID")); this.GetService <IQueryService>().ExecuteNoQueryWithManageProperties(node); }
/// <summary> /// 拼接查询语句 /// </summary> /// <param name="programJobNo"></param> /// <param name="siteNo"></param> /// <param name="condition"></param> /// <returns></returns> private QueryNode GetSalesIssueListNode(string programJobNo, string siteNo, DependencyObjectCollection condition) { QueryConditionGroup group = CreateCondition(siteNo, condition); return(OOQL.Select(true, OOQL.CreateProperty("SALES_ISSUE.DOC_NO", "doc_no"), OOQL.CreateProperty("SALES_ISSUE.DOC_DATE", "create_date"), OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"), OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"), OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME", "employee_name"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")) .From("SALES_ISSUE", "SALES_ISSUE") .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("SALES_ISSUE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .LeftJoin("CUSTOMER", "CUSTOMER") .On(OOQL.CreateProperty("SALES_ISSUE.SHIP_TO_CUSTOMER_ID") == OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("SALES_ISSUE.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("SALES_ISSUE.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")) .LeftJoin("SALES_ISSUE.SALES_ISSUE_D", "SALES_ISSUE_D") .On(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID") == OOQL.CreateProperty("SALES_ISSUE_D.SALES_ISSUE_ID")) .Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") & group)); }
/// <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 GetGetTransferReqQueryNode(string programJobNo, string siteNo, string[] docNo, string scanType, string id, string status) { QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")) & ((OOQL.CreateProperty("TRANSFER_REQUISITION.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("TRANSFER_REQUISITION_D.CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)))); string docType = programJobNo + status; QueryNode queryNode = OOQL.Select( OOQL.CreateConstants("99", "enterprise_no"), //企业编号 OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点 OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业 OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO", "source_no"), //来源单号 OOQL.CreateConstants(docType, "doc_type"), //单据类型 OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_DATE", "create_date"), //单据日期 Formulas.IsNull(OOQL.CreateProperty("TRANSFER_REQUISITION_D.SequenceNumber"), OOQL.CreateConstants(0), "seq"), //单据项次 OOQL.CreateConstants(0, "doc_line_seq"), //单据项序 OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序 OOQL.CreateConstants(string.Empty, "object_no"), //对象编号 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), //料件编号 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //产品特征 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //产品特征说明 Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "outgoing_warehouse_no"), //仓库 OOQL.CreateConstants(string.Empty, "outgoing_storage_spaces_no"), //储位 OOQL.CreateConstants(string.Empty, "lot_no"), //批号 OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_QTY", "doc_qty"), //单据数量 OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANS_OUT_BUSINESS_QTY", "in_out_qty"), //出入数量 Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), //单位 OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), //主营组织 OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 // add by 08628 for P001-171023001 b 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("TRANSFER_REQUISITION", "TRANSFER_REQUISITION") .InnerJoin("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D", "TRANSFER_REQUISITION_D") .On(OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANSFER_REQUISITION_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.FROM_WAREHOUSE_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID")) .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_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("TRANSFER_REQUISITION.TRANSFER_REQUISITION_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(conditionGroup); return(queryNode); }
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="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetIssueReceiptQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "doc_no"),//20170925 modi by wangyq for P001-170717001 添加distinct因为后面关联加了单身 OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), Formulas.Case(null, OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")), OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))), "customer_name"), OOQL.CreateConstants(programJobNo, "program_job_no"), Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name")) .From("ISSUE_RECEIPT", "ISSUE_RECEIPT") //20170925 add by wangyq for P001-170717001 ================begin============== .InnerJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D") .On(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID")) //20170925 add by wangyq for P001-170717001 ================end============== .LeftJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid")) .LeftJoin("WORK_CENTER", "WORK_CENTER") .On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid")) .LeftJoin("SUPPLIER", "SUPPLIER") .On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Emp")) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //.Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) &//20170328 mark by wangyq for P001-170327001 //20170925 modi by wangyq for P001-170717001 =============begin=============== //QueryConditionGroup group =//20170328 add by wangyq for P001-170327001 // (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & // //(OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N")) &//20170925 mark by wangyq for P001-170717001 // ((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56")) | // (OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57"))); ////);//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group = (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N"); switch (programJobNo) { case "7-5": group &= OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56"); break; case "7": group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56"); break; case "8": group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57"); break; default: break; } //20170925 modi by wangyq for P001-170717001 =============end=============== //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("ISSUE_RECEIPT.DOC_NO", "ISSUE_RECEIPT.DOC_DATE", new string[] { "1", "2", "3", "4", "5" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(queryNode); }
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 GetTransferDocQueryNode(string programJobNo, string siteNo, string[] docNo, string scanType, string id, string status) //20161216 add by liwei1 for P001-161215001 //private QueryNode GetTransferDocQueryNode(string programJobNo, string siteNo, string docNo, string scanType, string id, string status) {//20161216 mark by liwei1 for P001-161215001 { QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC")) & ((OOQL.CreateProperty("TRANSFER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("TRANSFER_DOC_D.CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))); if (scanType == "1") { //箱条码 conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_D_ID") == Formulas.Cast(OOQL.CreateConstants(id), GeneralDBType.Guid)); } else if (scanType == "2") { //单据条码 conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));//20161216 add by liwei1 for P001-161215001 //conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO") == OOQL.CreateConstants(docNo));//20161216 mark by liwei1 for P001-161215001 } string docType = programJobNo + status; QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("TRANSFER_DOC.DOC_NO", "source_no"), OOQL.CreateProperty("TRANSFER_DOC.DOC_DATE", "create_date"), OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_QTY", "doc_qty"), OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFERED_BUSINESS_QTY", "in_out_qty"), Formulas.Cast(OOQL.CreateProperty("TRANSFER_DOC_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "outgoing_warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "outgoing_storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateConstants(docType, "doc_type"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_line_seq"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_batch_seq"), OOQL.CreateConstants(string.Empty, "object_no"), //20161216 add by shenbao FOR P001-161215001 ===begin=== Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 //20161216 add by shenbao FOR P001-161215001 ===end=== OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants(string.Empty, "first_in_first_out_control"), OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"), OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no") ) .From("TRANSFER_DOC", "TRANSFER_DOC") .InnerJoin("TRANSFER_DOC.TRANSFER_DOC_D", "TRANSFER_DOC_D") .On(OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_ID") == OOQL.CreateProperty("TRANSFER_DOC.TRANSFER_DOC_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.FROM_WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.FROM_BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===begin=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===end=== // add by 08628 for P001-171023001 b .LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE") .On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "MAIN_BIN") .On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID") & OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1)) // add by 08628 for P001-171023001 e .Where(conditionGroup); return(queryNode); }
/// <summary> /// 根据传入的供应商查询出所有的对账清单数据 /// </summary> /// <param name="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 GetPurchaseDetail(string supplierNo, string dateS, string dateE, string dueDateS, string dueDateE, string itemNo, string itemName, string itemSpec, string siteNo , string purchase_no//20170919 add by liwei1 for B001-170918003 ) { QueryNode subNodeFilArrival = GetFilArrivalQueryNode(); JoinOnNode joinOnNode = OOQL.Select( OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_DATE", "purchase_date"), OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE", "so_due_date"), OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "seq"), OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "line_seq"), OOQL.CreateConstants(0, GeneralDBType.Int32, "batch_seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), //20170504 add by wangyq for P001-161209002 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), //20171010 modi by zhangcn for B001-171010004 Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), OOQL.CreateConstants("N", GeneralDBType.String, "qc_type"), //20170619 modi by zhangcn for B001-170629006 【OLD:null】 OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "purchase_qty"), OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "receipt_qty"), //OOQL.CreateArithmetic( // OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"), // OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub,"unpaid_qty"), OOQL.CreateArithmetic(OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"), OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub), Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0)), ArithmeticOperators.Sub, "unpaid_qty"), Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0), "on_the_way_qty"), //20170619 modi by zhangcn for B001-170629006 【OLD:0】 Formulas.IsNull( OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE") * OOQL.CreateConstants(100, GeneralDBType.Decimal), OOQL.CreateConstants(0, GeneralDBType.Decimal), "over_deliver_rate"), OOQL.CreateProperty("PURCHASE_ORDER_SD.REMARK", "remark"), Formulas.IsNull( OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"), Formulas.IsNull( OOQL.CreateProperty("PRICE_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"), OOQL.CreateProperty("PURCHASE_ORDER_SD.PRICE_QTY", "valuation_qty"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "returned_qty"), //20170619 add by zhangcn for B001-170629006 OOQL.CreateConstants(1, GeneralDBType.Decimal, "box_qty")) //20170619 add by zhangcn for B001-170629006 .From("PURCHASE_ORDER", "PURCHASE_ORDER") .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID"))) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID"))) //20170504 add by wangyq for P001-161209002 ========================begin========================= .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) //20170504 add by wangyq for P001-161209002 ========================end========================= .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"))) .InnerJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID"))) .LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE") .On((OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) & (OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Emp"))) .LeftJoin("UNIT", "PRICE_UNIT") .On((OOQL.CreateProperty("PRICE_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PRICE_UNIT_ID"))) //20170619 add by zhangcn for B001-170629006 ===begin=== .LeftJoin(subNodeFilArrival, "FILARR") .On(OOQL.CreateProperty("FILARR.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") & OOQL.CreateProperty("FILARR.ORDER_SE") == OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber") & OOQL.CreateProperty("FILARR.ORDER_SE_SE") == OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber")); //20170619 add by zhangcn for B001-170629006 ===begin=== //如果起始日期为空格,空值,null时默认最小日期 if (Maths.IsEmpty(dateS.ToDate())) { dateS = OrmDataOption.EmptyDateTime.ToStringExtension(); } if (Maths.IsEmpty(dueDateS.ToDate())) { dueDateS = OrmDataOption.EmptyDateTime.ToStringExtension(); } //如果结束日期为空格,空值,null时默认最大日期 if (Maths.IsEmpty(dateE.ToDate())) { dateE = OrmDataOption.EmptyDateTime1.ToStringExtension(); } if (Maths.IsEmpty(dueDateE.ToDate())) { dueDateE = OrmDataOption.EmptyDateTime1.ToStringExtension(); } QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC")) & ((OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") == OOQL.CreateConstants("Y")) //& (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIPTED_BUSINESS_QTY"))//20170903 mark by liwei1 for B001-170901012 & (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY")) //20170903 add by liwei1 for B001-170901012 & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & ((OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") >= OOQL.CreateConstants(dateS.ToDate())) & (OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") <= OOQL.CreateConstants(dateE.ToDate()))) & ((OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") >= OOQL.CreateConstants(dueDateS.ToDate())) & (OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") <= OOQL.CreateConstants(dueDateE.ToDate())))); //如果【品号】不为空增加条件 if (!Maths.IsEmpty(itemNo)) { conditionGroup &= (OOQL.CreateProperty("ITEM.ITEM_CODE").Like(OOQL.CreateConstants("%" + itemNo + "%"))); } //如果【品名】不为空增加条件 if (!Maths.IsEmpty(itemName)) { conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION").Like(OOQL.CreateConstants("%" + itemName + "%"))); } //如果【规格】不为空增加条件 if (!Maths.IsEmpty(itemSpec)) { conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION").Like(OOQL.CreateConstants("%" + itemSpec + "%"))); } //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } //20170919 add by liwei1 for B001-170918003 ===begin=== //如果采购单号不为空增加条件 if (!Maths.IsEmpty(purchase_no)) { conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateConstants(purchase_no)); } //20170919 add by liwei1 for B001-170918003 ===end=== return(joinOnNode.Where(conditionGroup) //20170919 add by liwei1 for B001-170918003 ===begin=== .OrderBy( OOQL.CreateOrderByItem( OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"), SortType.Asc), OOQL.CreateOrderByItem( OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber"), SortType.Asc)) //20170919 add by liwei1 for B001-170918003 ===end=== ); }
private QueryNode 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="docNo">单据编号</param> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetIssueReceiptQueryNode(string[] docNo, string siteNo, string programJobNo, string status) { //20161216 add by liwei1 for P001-161215001 //private QueryNode GetIssueReceiptQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001 string docType = programJobNo + status; //20170925 add by wangyq for P001-170717001 =============begin=============== QueryConditionGroup group = OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)) & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo); if (programJobNo == "7-5") { group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N") & OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1"); } else { group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N"); } //20170925 add by wangyq for P001-170717001 =============end=============== QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "source_no"), OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"), Formulas.Cast(OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_QTY", "doc_qty"), //20161230 modi by shenbao for B001-161229011 INVENTORY_QTY=>ISSUE_RECEIPT_QTY Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "in_out_qty"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateConstants(docType, "doc_type"), Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_line_seq"), Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_batch_seq"), OOQL.CreateConstants(string.Empty, "object_no"), //20161216 add by shenbao FOR P001-161215001 ===begin=== Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 OOQL.CreateArithmetic( Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE"), OOQL.CreateConstants(0)) , OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"), //允许误差率//20170302 modi by shenbao for P001-170302002 误差率统一乘100 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 //20161216 add by shenbao FOR P001-161215001 ===end=== OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 // add by 08628 for P001-171023001 b Formulas.Case(null, Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_G.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"), OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"), OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no") // add by 08628 for P001-171023001 e ) .From("ISSUE_RECEIPT", "ISSUE_RECEIPT") .LeftJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D") .On(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID")) .LeftJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid")) .LeftJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===begin=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===end=== // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID") } , new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_I") .On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("FEATURE_GROUP_ID"), }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_G") .On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID")) & (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE") .On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "MAIN_BIN") .On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID") & OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1)) // add by 08628 for P001-171023001 e .Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) & //20170925 modi by wangyq for P001-170717001 =============begin=============== group); // (OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &//20161216 add by liwei1 for P001-161215001 ////(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001 // (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & // (OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N"))); //20170925 modi by wangyq for P001-170717001 =============end=============== return(queryNode); }
/// <summary> /// 根据传入的供应商查询出退换货清单 /// </summary> /// <param name="supplierNo">供应商编号</param> /// <param name="itemNo">料件编号</param> /// <param name="itemName">品名</param> /// <param name="itemSpec">规格</param> /// <param name="siteNo">营运中心</param> /// <returns></returns> private QueryNode GetReturnChangeDetail(string supplierNo, string itemNo, string itemName, string itemSpec, string siteNo) { JoinOnNode joinOnNode = OOQL.Select( OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"), Formulas.IsNull( OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "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"), //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("B.QTY", "qty")) .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"))) .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"))) .InnerJoin( OOQL.Select( Formulas.Sum(Formulas.IsNull( OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RETURN_BUSINESS_QTY"), OOQL.CreateConstants(0, GeneralDBType.Int32)) + Formulas.IsNull( OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"), OOQL.CreateConstants(0, GeneralDBType.Int32)), "QTY"), OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID")) .From("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "A") .LeftJoin( OOQL.Select( OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SOURCE_ID.ROid", "SOURCE_ID"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RETURN_BUSINESS_QTY")) .From("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID"))) .Where((OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") == OOQL.CreateConstants("Y"))), "PURCHASE_ARRIVAL_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SOURCE_ID") == OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID"))) .LeftJoin( OOQL.Select( OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid", "ORDER_SOURCE_ID"), OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY")) .From("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D") .InnerJoin("PURCHASE_ISSUE", "PURCHASE_ISSUE") .On((OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID"))) .Where((OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") == OOQL.CreateConstants("Y"))), "PURCHASE_ISSUE_D") .On((OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID") == OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID"))) .GroupBy( OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID")), "B") .On((OOQL.CreateProperty("B.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID"))) .InnerJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID"))) //20171010 add by zhangcn for B001-171010004 ========================begin========================= .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")); //20171010 add by zhangcn for B001-171010004 ========================end========================= //初始Where条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER")) & ((OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("PURCHASE_ORDER.CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))) & (OOQL.CreateProperty("B.QTY") > OOQL.CreateConstants(0m)); //如果【品号】不为空增加条件 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)); } //返回组合Node return(joinOnNode.Where(conditionGroup)); }
/// <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)); }