/// <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); }
/// <summary> /// 获取select集合,直接跟返回集合要求字段一致 /// </summary> /// <returns></returns> private List <QueryProperty> GetSelectList(string programJobNo, string status) { List <QueryProperty> selectList = new List <QueryProperty>(); selectList.Add(OOQL.CreateConstants("99", "enterprise_no")); selectList.Add(OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no")); selectList.Add(OOQL.CreateConstants(programJobNo, "source_operation")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE.DOC_NO", "source_no")); selectList.Add(OOQL.CreateArithmetic(OOQL.CreateConstants(programJobNo), OOQL.CreateConstants(status) , ArithmeticOperators.Plus, "doc_type")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE.DOC_DATE", "create_date")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE_D.SequenceNumber", "seq")); selectList.Add(OOQL.CreateConstants(0, "doc_line_seq")); selectList.Add(OOQL.CreateConstants(0, "doc_batch_seq")); selectList.Add(OOQL.CreateConstants(string.Empty, "object_no")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no")); selectList.Add(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no")); selectList.Add(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name")); selectList.Add(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE", "warehouse_no")); selectList.Add(OOQL.CreateProperty("BIN.BIN_CODE", "storage_spaces_no")); selectList.Add(OOQL.CreateProperty("ITEM_LOT.LOT_CODE", "lot_no")); selectList.Add(OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_QTY", "doc_qty")); selectList.Add(OOQL.CreateConstants(0M, GeneralDBType.Decimal, "in_out_qty")); selectList.Add(OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no")); selectList.Add(OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE", "allow_error_rate")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name")); selectList.Add(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec")); selectList.Add(Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateConstants("N", GeneralDBType.String), OOQL.CreateConstants("2", GeneralDBType.String))), "lot_control_typ")); selectList.Add(Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) })); //单位转换率分母 selectList.Add(Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("SALES_ISSUE_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("SALES_ISSUE_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) })); //单位转换率分子 selectList.Add(Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit")); //库存单位 selectList.Add(OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places")); selectList.Add(OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type")); selectList.Add(OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")); return(selectList); }
/// <summary> /// 新增单身查询逻辑 /// </summary> /// <param name="docId"></param> /// <returns></returns> private DependencyObjectCollection QueryForTD_D(object docId) { QueryNode groupNode = GroupNode(false); QueryNode node = OOQL.Select(OOQL.CreateProperty("Table_scan_detail.SequenceNumber", "SequenceNumber"), OOQL.CreateProperty("Table_scan_detail.ID", "DETAIL_ID"), OOQL.CreateProperty("Table_scan.ID", "ID"), OOQL.CreateProperty("Table_scan.info_lot_no"), OOQL.CreateProperty("ITEM.ITEM_ID", "ITEM_ID"), OOQL.CreateProperty("ITEM.ITEM_NAME", "ITEM_NAME"), Formulas.Case(null, OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateConstants(Maths.GuidDefaultValue()))), "ITEM_FEATURE_ID"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "ITEM_SPECIFICATION"), OOQL.CreateProperty("Table_scan_detail.picking_qty", "picking_qty"), OOQL.CreateProperty("UNIT.UNIT_ID", "UNIT_ID"), Formulas.Ext("UNIT_CONVERT", "SECOND_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("Table_scan_detail.picking_qty"), OOQL.CreateProperty("ITEM.SECOND_UNIT_ID"), OOQL.CreateConstants(0) }), Formulas.Ext("UNIT_CONVERT", "INVENTORY_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("Table_scan_detail.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }), OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID", "WAREHOUSE_ID"), Formulas.Case(null, OOQL.CreateProperty("BIN.BIN_ID"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.storage_spaces_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateConstants(Maths.GuidDefaultValue()))), "BIN_ID"), Formulas.Case(null, OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.lot_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateConstants(Maths.GuidDefaultValue()))), "ITEM_LOT_ID"), Formulas.Case(null, OOQL.CreateConstants("1", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("ITEM.ITEM_SN_MANAGEMENT") == OOQL.CreateConstants(false, GeneralDBType.Boolean) | OOQL.CreateProperty("Table_scan_detail.picking_qty") == OOQL.CreateConstants(0M, GeneralDBType.Decimal), OOQL.CreateConstants("0", GeneralDBType.String))), "SN_COLLECTED_STATUS"), OOQL.CreateProperty("PLANT.COMPANY_ID"), OOQL.CreateProperty("PLANT.PLANT_ID"), OOQL.CreateProperty("PLANT.COST_DOMAIN_ID", "P_COST_DOMAIN_ID"), OOQL.CreateProperty("WAREHOUSE.COST_DOMAIN_ID", "W_COST_DOMAIN_ID"), OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") ) .From(groupNode, "Table_scan_detail") .InnerJoin(_tempScan.Name, "Table_scan") .On(OOQL.CreateProperty("Table_scan.info_lot_no") == OOQL.CreateProperty("Table_scan_detail.info_lot_no")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("Table_scan_detail.item_no") == OOQL.CreateProperty("ITEM.ITEM_CODE")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE") & OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .InnerJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("Table_scan_detail.picking_unit_no") == OOQL.CreateProperty("UNIT.UNIT_CODE")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("Table_scan_detail.warehouse_no") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") & OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("Table_scan_detail.storage_spaces_no") == OOQL.CreateProperty("BIN.BIN_CODE") & OOQL.CreateProperty("BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("Table_scan_detail.lot_no") == OOQL.CreateProperty("ITEM_LOT.LOT_CODE") & OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID") & ((OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String) & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue())) | OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))) .InnerJoin("DOC", "DOC") .On(OOQL.CreateConstants(docId) == OOQL.CreateProperty("DOC.DOC_ID")) .LeftJoin("PARA_COMPANY", "PARA_COMPANY") .On(OOQL.CreateProperty("PLANT.COMPANY_ID") == OOQL.CreateProperty("PARA_COMPANY.Owner_Org.ROid")); return(_qurService.ExecuteDependencyObject(node)); }
/// <summary> /// 更新调拨单单身 /// </summary> /// <param name="decimalPlace">金额小数位数</param> /// <param name="carryMode">金额取位模式</param> private void UpdateTransactionDocD(int decimalPlace, int carryMode) { #region 查询 QueryNode groupNode = GroupNode(false); //子查询的节点 _queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_D_ID", "TRANSACTION_DOC_D_ID"), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty", "BUSINESS_QTY"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "UNIT_ID"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "WAREHOUSE_ID"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "BIN_ID"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "ITEM_LOT_ID"), Formulas.Ext("UNIT_CONVERT", "SECOND_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.SECOND_UNIT_ID"), OOQL.CreateConstants(0) }), //领料第二数量 Formulas.Ext("UNIT_CONVERT", "INVENTORY_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }), //领料库存数量 Formulas.Case(null, OOQL.CreateConstants("COST_DOMAIN"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( ((OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(1)) ), OOQL.CreateConstants("COMPANY"))), "COST_DOMAIN_ID_RTK"), //成本域 Formulas.Case(null, OOQL.CreateConstants(Maths.GuidDefaultValue()), OOQL.CreateCaseArray( OOQL.CreateCaseItem( ((OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(1))), OOQL.CreateProperty("PLANT.COMPANY_ID")), OOQL.CreateCaseItem( ((OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(2))), OOQL.CreateProperty("PLANT.COST_DOMAIN_ID")), OOQL.CreateCaseItem( ((OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(3))), OOQL.CreateProperty("WAREHOUSE.COST_DOMAIN_ID"))), "COST_DOMAIN_ID_ROid"), //成本域类型 OOQL.CreateProperty("ITEM.ITEM_SN_MANAGEMENT", "ITEM_SN_MANAGEMENT"), Formulas.Case(null, OOQL.CreateConstants("1"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( ((OOQL.CreateProperty("ITEM.ITEM_SN_MANAGEMENT") == OOQL.CreateConstants(0, GeneralDBType.Boolean)) | (Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }) == OOQL.CreateConstants(0)) ), OOQL.CreateConstants("0")), OOQL.CreateCaseItem( ((OOQL.CreateProperty("ITEM.ITEM_SN_MANAGEMENT") == OOQL.CreateConstants(1, GeneralDBType.Boolean)) & (OOQL.CreateProperty("TRANSACTION_DOC_D.SN_COLLECTED_QTY") != OOQL.CreateConstants(0, GeneralDBType.Decimal)) & (Formulas.Abs(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) })) == OOQL.CreateProperty("TRANSACTION_DOC_D.SN_COLLECTED_QTY")) ), OOQL.CreateConstants("2"))), "SN_COLLECTED_STATUS"), //序列号检核码 Formulas.Case(null, Formulas.Round(OOQL.CreateProperty("TRANSACTION_DOC_D.UNIT_COST") * Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }), decimalPlace, 1, ""), OOQL.CreateCaseArray( OOQL.CreateCaseItem(((OOQL.CreateConstants(carryMode) == OOQL.CreateConstants(1)) ), Formulas.Round( OOQL.CreateProperty("TRANSACTION_DOC_D.UNIT_COST") * Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())), OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }), decimalPlace))), "COST_AMT") //成本金额 ) //.From(_TEMP_SCAN_DETAIL.Name, "TEMP_SCAN_DETAIL") .From(groupNode, "TEMP_SCAN_DETAIL") .InnerJoin("TRANSACTION_DOC", "TRANSACTION_DOC") .On(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.doc_no")) .InnerJoin("TRANSACTION_DOC.TRANSACTION_DOC_D", "TRANSACTION_DOC_D") .On((OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID")) & (OOQL.CreateProperty("TRANSACTION_DOC_D.SequenceNumber") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.seq"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.item_no"))) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.site_no")) .InnerJoin("PARA_COMPANY", "PARA_COMPANY") .On(OOQL.CreateProperty("PARA_COMPANY.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.COMPANY_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_FEATURE_ID")) & (Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty)) == OOQL.CreateProperty("TEMP_SCAN_DETAIL.item_feature_no"))) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On((OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.lot_no")) & (OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) & (OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())))) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_unit_no")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.warehouse_no")) & (OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("WAREHOUSE.BIN", "BIN") .On((OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.storage_spaces_no")) & (OOQL.CreateProperty("BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))); #endregion #region 执行修改 QueryNode updateNode = OOQL.Update("TRANSACTION_DOC.TRANSACTION_DOC_D") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("BUSINESS_QTY"), OOQL.CreateProperty("SelectNode.BUSINESS_QTY")), new SetItem(OOQL.CreateProperty("BUSINESS_UNIT_ID"), OOQL.CreateProperty("SelectNode.UNIT_ID")), new SetItem(OOQL.CreateProperty("SECOND_QTY"), OOQL.CreateProperty("SelectNode.SECOND_QTY")), new SetItem(OOQL.CreateProperty("INVENTORY_QTY"), OOQL.CreateProperty("SelectNode.INVENTORY_QTY")), new SetItem(OOQL.CreateProperty("WAREHOUSE_ID"), OOQL.CreateProperty("SelectNode.WAREHOUSE_ID")), new SetItem(OOQL.CreateProperty("BIN_ID"), OOQL.CreateProperty("SelectNode.BIN_ID")), new SetItem(OOQL.CreateProperty("ITEM_LOT_ID"), OOQL.CreateProperty("SelectNode.ITEM_LOT_ID")), new SetItem(OOQL.CreateProperty("COST_DOMAIN_ID.RTK"), OOQL.CreateProperty("SelectNode.COST_DOMAIN_ID_RTK")), new SetItem(OOQL.CreateProperty("COST_DOMAIN_ID.ROid"), OOQL.CreateProperty("SelectNode.COST_DOMAIN_ID_ROid")), new SetItem(OOQL.CreateProperty("SN_COLLECTED_STATUS"), OOQL.CreateProperty("SelectNode.SN_COLLECTED_STATUS")), new SetItem(OOQL.CreateProperty("COST_AMT"), OOQL.CreateProperty("SelectNode.COST_AMT")) }) .From(_queryNode, "SelectNode") .Where(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.TRANSACTION_DOC_D_ID") == OOQL.CreateProperty("SelectNode.TRANSACTION_DOC_D_ID")); #endregion _querySrv.ExecuteNoQueryWithManageProperties(updateNode); }
/// <summary> /// 获取库存信息 /// </summary> /// <param name="logonName"></param> /// <returns></returns> private QueryNode GetStockInfomation(string logonName, string reportTime) { //20160309 add by shenbao for B001-170309014 ===begin=== QueryCondition condition = null; if (Maths.IsNotEmpty(reportTime)) { condition = OOQL.CreateProperty("WAREHOUSE.LastModifiedDate") > OOQL.CreateConstants(reportTime); } else { condition = OOQL.CreateConstants(1) == OOQL.CreateConstants(1); } //20160309 add by shenbao for B001-170309014 ===end=== return(OOQL.Select(true, OOQL.CreateConstants("Y", GeneralDBType.String, "status"), OOQL.CreateConstants("99", GeneralDBType.String, "enterprise_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "warehouse_no"), Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_NAME"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "warehouse_name"), Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.BIN_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "storage_spaces_no"), Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.BIN_NAME"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "storage_spaces_name") //20170303 add by liwei1 for B001-170303008 ===begin=== , Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.BIN_CONTROL"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "storage_spaces")) //20170303 add by liwei1 for B001-170303008 ===end=== .From("USER", "USER") .InnerJoin("USER.USER_ORG", "USER_ORG") .On((OOQL.CreateProperty("USER_ORG.USER_ID") == OOQL.CreateProperty("USER.USER_ID"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("USER_ORG.ORG.ROid"))) .InnerJoin( //20160309 MODI by shenbao for B001-170309014 改成innerjoin OOQL.Select( OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid", "Owner_Org_ROid"), OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_NAME"), OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateProperty("BIN.BIN_NAME") //20170303 add by liwei1 for B001-170303008 ===begin=== , Formulas.Case(null, OOQL.CreateConstants("N", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem((OOQL.CreateProperty("WAREHOUSE.BIN_CONTROL") == OOQL.CreateConstants("1")), OOQL.CreateConstants("Y", GeneralDBType.String))), "BIN_CONTROL") //20170303 add by liwei1 for B001-170303008 ===end=== ) .From("WAREHOUSE", "WAREHOUSE") .LeftJoin("WAREHOUSE.BIN", "BIN") .On((OOQL.CreateProperty("BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) & (OOQL.CreateProperty("WAREHOUSE.BIN_CONTROL") == OOQL.CreateConstants("1"))) .Where(condition), "WAREHOUSE") //20160309 add by shenbao for B001-170309014 添加条件condition .On((OOQL.CreateProperty("WAREHOUSE.Owner_Org_ROid") == OOQL.CreateProperty("USER_ORG.ORG.ROid"))) .Where((OOQL.AuthFilter("USER", "USER")) & ((OOQL.CreateProperty("USER.LOGONNAME") == OOQL.CreateConstants(logonName)) & (OOQL.CreateProperty("USER_ORG.ORG.RTK") == OOQL.CreateConstants("PLANT"))))); }
/// <summary> /// 查询到货单查询信息 /// </summary> /// <param name="programJobNo">作业编号</param> /// <param name="siteNo">工厂编号</param> /// <param name="docNo">单据编号</param> /// <param name="status"></param> /// <returns></returns> private QueryNode GetTransactionDocQueryNode(string programJobNo, string siteNo, string[] docNo, string status) //20161216 modi by shenbao for P001-161215001 修改docNo类型为数组 { string docType = programJobNo + status; QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "source_no"), OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE", "create_date"), OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_QTY", "doc_qty"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "in_out_qty"), Formulas.Cast(OOQL.CreateProperty("TRANSACTION_DOC_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateConstants(docType, "doc_type"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_line_seq"), OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_batch_seq"), OOQL.CreateConstants(string.Empty, "object_no"), //20161216 add by shenbao FOR P001-161215001 ===begin=== Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 //20161216 add by shenbao FOR P001-161215001 ===end=== OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 // add by 08628 for P001-171023001 b Formulas.Case(null, Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_G.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"), OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"), OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no") // add by 08628 for P001-171023001 e ) .From("TRANSACTION_DOC", "TRANSACTION_DOC") .InnerJoin("TRANSACTION_DOC.TRANSACTION_DOC_D", "TRANSACTION_DOC_D") .On(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===begin=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===end=== // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID") } , new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_I") .On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("FEATURE_GROUP_ID"), }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_G") .On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID")) & (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE") .On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "MAIN_BIN") .On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID") & OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1)) // add by 08628 for P001-171023001 e .Where((OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC")) & ((OOQL.CreateProperty("TRANSACTION_DOC.ApproveStatus") == OOQL.CreateConstants("N")) & (OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO").In(OOQL.CreateDyncParameter("docNos", docNo))) & //20161216 add by shenbao FOR P001-161215001 OOQL.CreateConstants(docNo)==>.In(OOQL.CreateDyncParameter("docnos", docNo) (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)))); return(queryNode); }
//20170329 add by wangrm for P001-170316001======start======= private DependencyObjectCollection QueryBcFrozen(string scanBarcode) { QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), Formulas.Case(null, OOQL.CreateConstants("N"), OOQL.CreateCaseArray( OOQL.CreateCaseItem(((OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateEmptyConstants()) & (OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateNullConstant())), OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS"))), "frozen"), OOQL.CreateConstants(string.Empty, "warehouse_no"), OOQL.CreateConstants(string.Empty, "storage_spaces_no"), OOQL.CreateConstants(string.Empty, "lot_no"), OOQL.CreateConstants(0m, "inventory_qty"), OOQL.CreateConstants(string.Empty, "inventory_unit")) .From("BC_RECORD", "BC_RECORD") .LeftJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("BC_RECORD.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("BC_RECORD.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .Where(OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(scanBarcode)); return(GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 获取领料出库单查询信息 /// </summary> /// <param name="docNo">单据编号</param> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetIssueReceiptQueryNode(string[] docNo, string siteNo, string programJobNo, string status) { //20161216 add by liwei1 for P001-161215001 //private QueryNode GetIssueReceiptQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001 string docType = programJobNo + status; //20170925 add by wangyq for P001-170717001 =============begin=============== QueryConditionGroup group = OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)) & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo); if (programJobNo == "7-5") { group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N") & OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1"); } else { group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N"); } //20170925 add by wangyq for P001-170717001 =============end=============== QueryNode queryNode = OOQL.Select(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "source_no"), OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"), Formulas.Cast(OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber"), GeneralDBType.Decimal, "seq"), OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_QTY", "doc_qty"), //20161230 modi by shenbao for B001-161229011 INVENTORY_QTY=>ISSUE_RECEIPT_QTY Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "in_out_qty"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateConstants(docType, "doc_type"), Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_line_seq"), Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_batch_seq"), OOQL.CreateConstants(string.Empty, "object_no"), //20161216 add by shenbao FOR P001-161215001 ===begin=== Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 OOQL.CreateArithmetic( Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE"), OOQL.CreateConstants(0)) , OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"), //允许误差率//20170302 modi by shenbao for P001-170302002 误差率统一乘100 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 //20161216 add by shenbao FOR P001-161215001 ===end=== OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 // add by 08628 for P001-171023001 b Formulas.Case(null, Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_G.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"), OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"), OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no") // add by 08628 for P001-171023001 e ) .From("ISSUE_RECEIPT", "ISSUE_RECEIPT") .LeftJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D") .On(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID")) .LeftJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid")) .LeftJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===begin=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) //20161216 add by shenbao FOR P001-161215001 ===end=== // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID") } , new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_I") .On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("FEATURE_GROUP_ID"), }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_G") .On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID")) & (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE") .On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "MAIN_BIN") .On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID") & OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1)) // add by 08628 for P001-171023001 e .Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) & //20170925 modi by wangyq for P001-170717001 =============begin=============== group); // (OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &//20161216 add by liwei1 for P001-161215001 ////(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001 // (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & // (OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N"))); //20170925 modi by wangyq for P001-170717001 =============end=============== return(queryNode); }
/// <summary> /// 单身新增查询准备 /// </summary> /// <param name="category"></param> /// <param name="report_datetime"></param> /// <returns></returns> private DataTable QueryForMoReceipt_D(string category, DateTime report_datetime, string recommendedOperations) { List <QueryProperty> groupList = new List <QueryProperty>(); groupList.Add(OOQL.CreateProperty("A.ID")); groupList.Add(OOQL.CreateProperty("A.info_lot_no")); groupList.Add(OOQL.CreateProperty("A.SequenceNumber")); groupList.Add(OOQL.CreateProperty("A.doc_no")); groupList.Add(OOQL.CreateProperty("A.seq")); groupList.Add(OOQL.CreateProperty("A.item_no")); groupList.Add(OOQL.CreateProperty("A.item_feature_no")); groupList.Add(OOQL.CreateProperty("A.picking_unit_no")); groupList.Add(OOQL.CreateProperty("A.warehouse_no")); groupList.Add(OOQL.CreateProperty("A.storage_spaces_no")); groupList.Add(OOQL.CreateProperty("A.lot_no")); List <QueryProperty> selectList = new List <QueryProperty>(); selectList.AddRange(groupList); selectList.Add(Formulas.Sum(OOQL.CreateProperty("A.picking_qty"), "picking_qty")); QueryNode groupNode = OOQL.Select(selectList.ToArray()) .From(_Table_scan_detail.Name, "A") .GroupBy(groupList.ToArray()); QueryNode node = OOQL.Select(true, OOQL.CreateProperty("Table_scan_detail.SequenceNumber", "SequenceNumber"), OOQL.CreateProperty("MO.MO_ID", "MO_ID"), OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID", "MO_PRODUCT_ID"), OOQL.CreateProperty("MO_PRODUCT.PRODUCT_TYPE", "PRODUCT_TYPE"), OOQL.CreateProperty("ITEM.ITEM_ID", "ITEM_ID"), OOQL.CreateProperty("ITEM.ITEM_NAME", "ITEM_DESCRIPTION"), Formulas.Case(null, OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateConstants(Maths.GuidDefaultValue(), GeneralDBType.Guid))), "ITEM_FEATURE_ID"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "ITEM_SPECIFICATION"), OOQL.CreateProperty("Table_scan_detail.picking_qty", "REQUEST_QTY"), Formulas.Ext("UNIT_CONVERT", "REQUEST_INVENTORY_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("Table_scan_detail.picking_qty"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateConstants(0) }), Formulas.Cast(OOQL.CreateConstants(0M), GeneralDBType.Decimal, 16, 6, "QUALIFIED_BUSINESS_QTY"), Formulas.Case(null, OOQL.CreateConstants(0M), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE") == OOQL.CreateConstants("1"), OOQL.CreateProperty("Table_scan_detail.picking_qty")) }, "ACCEPTED_QTY"), Formulas.Case(null, OOQL.CreateConstants("2"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE") == OOQL.CreateConstants("1"), OOQL.CreateConstants("1")) }, "INSPECTION_STATUS"), Formulas.Cast(OOQL.CreateConstants(0M), GeneralDBType.Decimal, 16, 6, "SCRAP_QTY"), Formulas.Cast(OOQL.CreateConstants(0M), GeneralDBType.Decimal, 16, 6, "DESTROYED_QTY"), OOQL.CreateProperty("UNIT.UNIT_ID", "BUSINESS_UNIT_ID"), Formulas.Ext("UNIT_CONVERT", "SECOND_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("Table_scan_detail.picking_qty"), OOQL.CreateProperty("ITEM.SECOND_UNIT_ID"), OOQL.CreateConstants(0) }), OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID", "WAREHOUSE_ID"), Formulas.Case(null, OOQL.CreateProperty("BIN.BIN_ID"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.storage_spaces_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateConstants(Maths.GuidDefaultValue()))), "BIN_ID"), Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants(Maths.GuidDefaultValue()), new CaseItem[] { new CaseItem(OOQL.CreateProperty("MO.ITEM_LOT_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()) & OOQL.CreateProperty("MO_PRODUCT.PRODUCT_TYPE") == OOQL.CreateConstants("1"), OOQL.CreateProperty("MO.ITEM_LOT_ID")) }), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("Table_scan_detail.lot_no") != OOQL.CreateConstants(string.Empty, GeneralDBType.String), OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))), "ITEM_LOT_ID"), OOQL.CreateProperty("MO.MO_ID", "SOURCE_MO_ID"), OOQL.CreateConstants(string.Empty, GeneralDBType.String, "REMARK"), OOQL.CreateConstants("N", GeneralDBType.String, "ApproveStatus"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime, GeneralDBType.Date, "ApproveDate"), OOQL.CreateProperty("Table_scan.ID", "MO_RECEIPT_REQUISTION_ID"), OOQL.CreateProperty("Table_scan_detail.ID", "MO_RECEIPT_REQUISTION_D_ID"), OOQL.CreateConstants("0", GeneralDBType.String, "CLOSE") ) .From(groupNode, "Table_scan_detail") .InnerJoin(_Table_scan.Name, "Table_scan") .On(OOQL.CreateProperty("Table_scan_detail.info_lot_no") == OOQL.CreateProperty("Table_scan.info_lot_no")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("Table_scan_detail.item_no") == OOQL.CreateProperty("ITEM.ITEM_CODE")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On((OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE") & OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))) .InnerJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("Table_scan_detail.picking_unit_no") == OOQL.CreateProperty("UNIT.UNIT_CODE")) .InnerJoin("MO", "MO") .On(OOQL.CreateProperty("Table_scan_detail.doc_no") == OOQL.CreateProperty("MO.DOC_NO")) .InnerJoin("MO.MO_PRODUCT", "MO_PRODUCT") .On((OOQL.CreateProperty("Table_scan_detail.seq") == OOQL.CreateProperty("MO_PRODUCT.SequenceNumber") & OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_PRODUCT.MO_ID"))) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On((OOQL.CreateProperty("Table_scan_detail.warehouse_no") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") & OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("WAREHOUSE.BIN", "BIN") .On((OOQL.CreateProperty("Table_scan_detail.storage_spaces_no") == OOQL.CreateProperty("BIN.BIN_CODE") & OOQL.CreateProperty("BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On((OOQL.CreateProperty("Table_scan_detail.lot_no") == OOQL.CreateProperty("ITEM_LOT.LOT_CODE") & OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID") & ((OOQL.CreateProperty("Table_scan_detail.item_feature_no") == OOQL.CreateConstants(string.Empty, GeneralDBType.String) & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue(), GeneralDBType.Guid)) | OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")))) .InnerJoin("ITEM_PLANT") .On((OOQL.CreateProperty("MO_PRODUCT.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))); return(_qurService.Execute(node)); }
/// <summary> /// 根据传入的供应商查询出所有的对账清单数据 /// </summary> /// <param name="supplierNo">供应商</param> /// <param name="dateS">对账开始日期</param> /// <param name="dateE">对账截止日期</param> /// <param name="siteNo">营运中心</param> /// <returns></returns> private QueryNode GetCheckListDetail(string supplierNo, string dateS, string dateE, string siteNo) { //如果结束日期为空格,空值,null时默认最大日期 if (Maths.IsEmpty(dateE.ToDate())) { dateE = OrmDataOption.EmptyDateTime1.ToStringExtension(); } //如果开始日期为空格,空值,null时默认最小日期 if (Maths.IsEmpty(dateS.ToDate())) { dateS = OrmDataOption.EmptyDateTime.ToStringExtension(); } JoinOnNode joinOnNode = OOQL.Select( OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"), OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001 #region //20170508 mark by liwei1 for P001-161209002 //OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "qty"), //Formulas.IsNull( // OOQL.CreateProperty("UNIT.UNIT_CODE"), // OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), //OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "price"), #endregion //20170508 add by liwei1 for P001-161209002 ---begin--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"), Formulas.IsNull( OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(" ", GeneralDBType.String), "unit_no"), Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(" ", GeneralDBType.String), "valuation_unit_no"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "valuation_qty"), OOQL.CreateProperty("PURCHASE_ARRIVAL_D.DISCOUNTED_PRICE", "price"), //20170508 add by liwei1 for P001-161209002 ---end--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"), OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"), #region //20170508 mark by liwei1 for P001-161209002 //OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY", "billing_qty"), //(OOQL.CreateArithmetic( // OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"), // OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty")), //OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC", "billing_amount"), //OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC") // + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")), // OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), ArithmeticOperators.Sub, "not_billing_amount")) #endregion //20170508 add by liwei1 for P001-161209002 ---begin--- OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY", "billing_qty"), OOQL.CreateArithmetic( OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty"), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray( OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC"))), "billing_amount"), OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC") + OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray( OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")))), ArithmeticOperators.Sub, "not_billing_amount")) //20170508 add by liwei1 for P001-161209002 ---end--- .From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT") .InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D") .On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID"))) #region //20170508 mark by liwei1 for P001-161209002 //.InnerJoin( // OOQL.Select( // OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid", "SOURCE2_ID_ROid"), // OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "DISCOUNTED_PRICE_INTAX"), // Formulas.Sum( // OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), "PRICE_QTY"), // Formulas.Sum( // OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), "AMT_TC")) // .From("PAYABLE_DOC.PAYABLE_DOC_D", "PAYABLE_DOC_D") // .InnerJoin("PAYABLE_DOC", "PAYABLE_DOC") // .On((OOQL.CreateProperty("PAYABLE_DOC.PAYABLE_DOC_ID") ==OOQL.CreateProperty("PAYABLE_DOC_D.PAYABLE_DOC_ID"))) // .Where((OOQL.CreateProperty("PAYABLE_DOC.ApproveStatus") ==OOQL.CreateConstants("Y")) // &((OOQL.CreateProperty("PAYABLE_DOC.BOOKKEEPING_DATE") >=OOQL.CreateConstants(dateS.ToDate())) // &(OOQL.CreateProperty("PAYABLE_DOC.DOC_DATE") <=OOQL.CreateConstants(dateE.ToDate())))) // .GroupBy( // OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid"), // OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX")), "PAYABLE_DOC_D") //.On((OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID_ROid") ==OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID"))) #endregion //20170508 add by liwei1 for P001-161209002 ---gebin--- .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID"))) .InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL") .On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID"))) .LeftJoin("UNIT", "B_UNIT") .On((OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_UNIT_ID"))) //20170508 add by liwei1 for P001-161209002 ---end--- .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid"))) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID"))) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID"))) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001 .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001 .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID"))) .InnerJoin("SUPPLIER", "SUPPLIER") .On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID"))); #region 20171010 add by zhangcn for B001-171010004 QueryConditionGroup conditionGroup2 = (OOQL.AuthFilter("PURCHASE_ISSUE", "PURCHASE_ISSUE")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) & (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) & (OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") == OOQL.CreateConstants("Y")); //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup2 &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } QueryNode unioNode = OOQL.Select(OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE", "create_date"), OOQL.CreateProperty("PURCHASE_ISSUE.DOC_NO", "stock_in_no"), OOQL.CreateProperty("PURCHASE_ISSUE_D.SequenceNumber", "seq"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_DESCRIPTION", "item_name"), OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001 //OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY", "qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "qty"), Formulas.IsNull(OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY", "valuation_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "valuation_qty"), OOQL.CreateProperty("PURCHASE_RETURN_D.DISCOUNTED_PRICE", "price"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "amount"), //OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"), // OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"), // ArithmeticOperators.Plus, // "tax_amount"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "tax_amount"), OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"), OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"), //OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY","billing_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "billing_qty"), //OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), // ArithmeticOperators.Sub, // "not_billing_qty"), OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY") - OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "not_billing_qty"), //Formulas.Case(null, // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), // OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))), // "billing_amount"), OOQL.CreateArithmetic((Formulas.Case(null, OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))) ) ), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "billing_amount"), //OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")), // Formulas.Case(null, // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), // OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), // OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))), // ArithmeticOperators.Sub, // "not_billing_amount")) OOQL.CreateArithmetic((OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")), Formulas.Case(null, OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"), OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)), OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))), ArithmeticOperators.Sub)), OOQL.CreateConstants(-1), ArithmeticOperators.Mulit, "not_billing_amount")) .From("PURCHASE_ISSUE", "PURCHASE_ISSUE") .InnerJoin("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D") .On(OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID")) .InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D") .On(OOQL.CreateProperty("PURCHASE_ISSUE_D.SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID")) .InnerJoin("PURCHASE_RETURN", "PURCHASE_RETURN") .On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID")) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD") .On(OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID")) .InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D") .On(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.Owner_Org.ROid")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001 .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001 .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_UNIT_ID")) .LeftJoin("UNIT", "B_UNIT") .On(OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_UNIT_ID")) .InnerJoin("SUPPLIER", "SUPPLIER") .On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.SUPPLIER_ID")) .Where(conditionGroup2); #endregion //初始Where条件 QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")) & ((OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)) & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) //20170508 add by liwei1 for P001-161209002 & (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) //20170508 add by liwei1 for P001-161209002 ); //如果营运中心不为空增加条件 if (!Maths.IsEmpty(siteNo)) { conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)); } return(joinOnNode.Where(conditionGroup).Union(unioNode));//20171010 modi by zhangcn for B001-171010004 【增加.Union(unioNode)】 }
/// <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 GeIssueReceiptReqNode(string[] docNo, string siteNo, string programJobNo, string status) { string docType = programJobNo + status; QueryNode queryNode = OOQL.Select( OOQL.CreateConstants("99", "enterprise_no"), //企业编号 OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点 OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业 OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO", "source_no"), //来源单号 OOQL.CreateConstants(docType, "doc_type"), //单据类型 OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_DATE", "create_date"), //单据日期 Formulas.IsNull(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.SequenceNumber"), OOQL.CreateConstants(0), "seq"), //单据项序 OOQL.CreateConstants(0, "doc_line_seq"), //单据项序 OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序 OOQL.CreateConstants(string.Empty, "object_no"), //对象编号 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), //料件编号 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //产品特征 Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //产品特征说明 Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), //库位 Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), //储位 Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), //批号 OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.REQUEST_QTY", "doc_qty"), //单据数量 OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ISSUE_RECEIPT_QTY", "in_out_qty"), //出入数量 Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), //单位 OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名 Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格 Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N") , OOQL.CreateConstants("2")) }), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID") , OOQL.CreateConstants(1) }), //单位转换率分母 Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID") , OOQL.CreateConstants(0) }), //单位转换率分子 Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位 OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), //主营组织 OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001 OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001 // add by 08628 for P001-171023001 b Formulas.Case(null, Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_G.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"), OOQL.CreateConstants(string.Empty, "main_warehouse_no"), OOQL.CreateConstants(string.Empty, "main_storage_no") // add by 08628 for P001-171023001 e ) .From("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ") .LeftJoin("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D", "ISSUE_RECEIPT_REQ_D") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ISSUE_RECEIPT_REQ_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT", "ITEM_LOT") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ_D.UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID") } , new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_I") .On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ISSUE_RECEIPT_REQ_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("FEATURE_GROUP_ID"), }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_G") .On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID")) & (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) // add by 08628 for P001-171023001 e .Where((OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")) & (OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ApproveStatus") == OOQL.CreateConstants("Y"))); return(queryNode); }
/// <summary> /// 获取生成到货检验单基础数据 /// </summary> /// <param name="receiptNo">到货单号</param> /// <param name="paiSequenceNumber">单身序号集合</param> /// <returns></returns> private DataTable GetInsertPoArrivalInspection(string receiptNo, List <int> paiSequenceNumber) { string stringDefault = string.Empty; object guidDefault = Maths.GuidDefaultValue(); const decimal decimalDefault = 0m; QueryNode node = OOQL.Select(true, OOQL.CreateProperty("pad.SequenceNumber", "SequenceNumber"), OOQL.CreateProperty("pa.ARRIVAL_DATE", "ARRIVAL_DATE"), OOQL.CreateProperty("ip.INSPECT_DAYS", "INSPECT_DAYS"), OOQL.CreateProperty("i.STOCK_UNIT_ID", "STOCK_UNIT_ID"), OOQL.CreateProperty("i.SECOND_UNIT_ID", "SECOND_UNIT_ID"), OOQL.CreateProperty("pad.PURCHASE_TYPE", "PURCHASE_TYPE"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "PO_ARRIVAL_INSPECTION_ID"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "DOC_ID"), OOQL.CreateConstants("Q1", "CATEGORY"), OOQL.CreateProperty("pa.DOC_DATE", "DOC_DATE"), OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DOC_NO"), Formulas.GetDate("INSPECTION_DATE"), OOQL.CreateConstants(stringDefault, GeneralDBType.String, "INSPECTION_TIMES"), OOQL.CreateProperty("pad.ITEM_ID", "ITEM_ID"), OOQL.CreateProperty("pad.ITEM_DESCRIPTION", "ITEM_DESCRIPTION"), OOQL.CreateProperty("pad.ITEM_FEATURE_ID", "ITEM_FEATURE_ID"), OOQL.CreateProperty("pad.ITEM_SPECIFICATION", "ITEM_SPECIFICATION"), OOQL.CreateProperty("pad.ITEM_LOT_ID", "ITEM_LOT_ID"), OOQL.CreateProperty("pa.SUPPLIER_ID", "SUPPLIER_ID"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "INSPECTION_QTY"), OOQL.CreateProperty("pad.BUSINESS_UNIT_ID", "INSPECTION_UNIT_ID"), OOQL.CreateProperty("isdd.STRICTNESS_DEGREE", "STRICTNESS_DEGREE"), Formulas.GetDate("INSPECTION_DUE_DATE"), OOQL.CreateProperty("pa.Owner_Dept", "SUBMIT_DEPT_ID"), OOQL.CreateProperty("pa.Owner_Emp", "SUBMIT_EMP_ID"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "INVENTORY_QTY"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "SECOND_QTY"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "ACCEPTABLE_QTY"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "UNQUALIFIED_QTY"), Formulas.Cast(OOQL.CreateConstants(decimalDefault), GeneralDBType.Decimal, 16, 6, "DESTROYED_QTY"), OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DECISION"), OOQL.CreateConstants(stringDefault, GeneralDBType.String, "DECISION_DESCRIPTION"), OOQL.CreateConstants("1", GeneralDBType.String, "RESULT_STATUS"), OOQL.CreateConstants(stringDefault, GeneralDBType.String, "REMARK"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "INSPECTION_PLAN_ID"), OOQL.CreateProperty("p.COMPANY_ID", "COMPANY_ID"), OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_D_ID", "SOURCE_ID"), Formulas.Case(null, OOQL.CreateConstants(guidDefault, GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("pad.PURCHASE_TYPE") == OOQL.CreateConstants("3")), OOQL.CreateProperty("pad.REFERENCE_SOURCE_ID.ROid"))), "MO_ROUTING_D_ID"), OOQL.CreateProperty("pad.OPERATION_ID", "OPERATION_ID"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "TO_MO_ROUTING_D_ID"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "TO_OPERATION_ID"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "PROJECT_ID"), OOQL.CreateConstants(false, GeneralDBType.Boolean, "DEDUCT_ARRIVED_QTY"), OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid", "Owner_Org_ROid"), OOQL.CreateConstants("PLANT", "Owner_Org_RTK"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "Owner_Emp"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "Owner_Dept"), OOQL.CreateConstants("N", "ApproveStatus"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "CreateBy"), OOQL.CreateConstants(DateTime.Now, "CreateDate"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "ModifiedBy"), OOQL.CreateConstants(DateTime.Now, "ModifiedDate"), Formulas.Cast(OOQL.CreateConstants(guidDefault), GeneralDBType.Guid, "LastModifiedBy"), OOQL.CreateConstants(DateTime.Now, "LastModifiedDate")) .From("PURCHASE_ARRIVAL", "pa") .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "pad") .On((OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("pa.PURCHASE_ARRIVAL_ID"))) .InnerJoin("ITEM", "i") .On((OOQL.CreateProperty("i.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID"))) .LeftJoin("PLANT", "p") .On((OOQL.CreateProperty("p.PLANT_ID") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid"))) .LeftJoin("ITEM_PLANT", "ip") .On((OOQL.CreateProperty("ip.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID")) & (OOQL.CreateProperty("ip.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid"))) .LeftJoin("ITEM_STRI_DEGREE", "isd") .On((OOQL.CreateProperty("isd.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid")) & (OOQL.CreateProperty("isd.SOURCE_ID.ROid") == OOQL.CreateProperty("pa.SUPPLIER_ID"))) .LeftJoin("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_D", "isdd") .On((OOQL.CreateProperty("isdd.ITEM_STRI_DEGREE_ID") == OOQL.CreateProperty("isd.ITEM_STRI_DEGREE_ID")) & (OOQL.CreateProperty("isdd.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID")) & (OOQL.CreateProperty("isdd.ITEM_FEATURE_ID") == OOQL.CreateProperty("pad.ITEM_FEATURE_ID"))) .Where((OOQL.AuthFilter("PURCHASE_ARRIVAL", "pa")) & (OOQL.CreateProperty("pa.DOC_NO") == OOQL.CreateConstants(receiptNo)) & (OOQL.CreateProperty("pad.SequenceNumber").In(OOQL.CreateDyncParameter("SequenceNumber", paiSequenceNumber)))); return(GetService <IQueryService>().Execute(node)); }
/// <summary> /// 查询寄售订单 /// </summary> /// <param name="programJobNo">作业编号</param> /// <param name="status">A.新增 S.过帐</param> /// <param name="docNo">单据编号</param> /// <param name="siteNo">工厂</param> /// <returns></returns> private DependencyObjectCollection GetSalesOrderDoc(string programJobNo, string status, string[] docNo, string siteNo) { QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateConstants(programJobNo, "source_operation"), OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO", "source_no"), OOQL.CreateConstants(programJobNo + status, "doc_type"), OOQL.CreateProperty("SALES_ORDER_DOC.DOC_DATE", "create_date"), OOQL.CreateProperty("SALES_ORDER_DOC_D.SequenceNumber", "seq"), OOQL.CreateProperty("SALES_ORDER_DOC_SD.SequenceNumber", "doc_line_seq"), Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Int32, "doc_batch_seq"), OOQL.CreateConstants(string.Empty, "object_no"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), Formulas.IsNull( OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull( OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull( OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull( OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), OOQL.CreateConstants(string.Empty, "lot_no"), OOQL.CreateProperty("SALES_ORDER_DOC_SD.BUSINESS_QTY", "doc_qty"), OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERED_BUSINESS_QTY", "in_out_qty"), Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), Formulas.IsNull( OOQL.CreateArithmetic( OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE"), OOQL.CreateConstants(100), ArithmeticOperators.Mulit), OOQL.CreateConstants(0), "allow_error_rate"), OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), Formulas.Case(null, OOQL.CreateConstants("1"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")), OOQL.CreateConstants("2"))), "lot_control_type"), Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID"), OOQL.CreateConstants(1)), Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"), OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"), OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID"), OOQL.CreateConstants(0)), Formulas.IsNull( OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), Formulas.IsNull( OOQL.CreateProperty("UNIT.DICIMAL_DIGIT"), OOQL.CreateConstants(string.Empty), "decimal_places"), OOQL.CreateConstants(1, "decimal_places_type"), OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"), // add by 08628 for P001-171023001 b Formulas.Case(null, Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_G.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I.FIFO_TYPE")))), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(), OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"), OOQL.CreateConstants(string.Empty, "main_warehouse_no"), OOQL.CreateConstants(string.Empty, "main_storage_no") // add by 08628 for P001-171023001 e ) .From("SALES_ORDER_DOC", "SALES_ORDER_DOC") .InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D", "SALES_ORDER_DOC_D") .On((OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_ID"))) .InnerJoin("SALES_ORDER_DOC.SALES_ORDER_DOC_D.SALES_ORDER_DOC_SD", "SALES_ORDER_DOC_SD") .On((OOQL.CreateProperty("SALES_ORDER_DOC_SD.SALES_ORDER_DOC_D_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.SALES_ORDER_DOC_D_ID"))) .InnerJoin("SALES_CENTER", "SALES_CENTER") .On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PARTNER_ID.ROid"))) .InnerJoin("ITEM", "ITEM") .On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"))) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_FEATURE_ID"))) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_WAREHOUSE_ID"))) .LeftJoin("WAREHOUSE.BIN", "BIN") .On((OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.BIN_ID"))) .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID"))) .LeftJoin("ITEM_PLANT", "ITEM_PLANT") .On((OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("ITEM_SALES", "ITEM_SALES") .On((OOQL.CreateProperty("ITEM_SALES.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("ITEM_SALES.Owner_Org.ROid") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid"))) .LeftJoin("UNIT", "STOCK_UNIT") .On((OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"))) // add by 08628 for P001-171023001 b .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("ITEM_FEATURE_ID") }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG") .Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull() & (OOQL.CreateProperty("ITEM_FEATURE_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue()))), "REG_I_F") .On((OOQL.CreateProperty("REG_I_F.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_FEATURE_ID")) & (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("ITEM_ID") } , new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_I") .On((OOQL.CreateProperty("REG_I.ITEM_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.SALES_ORDER_DOC_D.ITEM_ID")) & (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) .LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"), OOQL.CreateProperty("FIFO_TYPE"), Formulas.RowNumber("SEQ", new OverClause(new[] { OOQL.CreateProperty("FEATURE_GROUP_ID"), }, new[] { OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( (OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)), Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String), GeneralDBType.Date)))), SortType.Desc) }))) .From("ITEM_BC_REG"), "REG_G") .On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID")) & (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32))) // add by 08628 for P001-171023001 e .Where((OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B")) & OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))); return(GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 检验单获取质检方案相关抽样数量 /// </summary> /// <param name="paraInspectionPlanId">质检方案id</param> /// <param name="paraInspectionItemId">检验项目</param> /// <param name="paraQty">送检批量</param> /// <param name="paraDegree">宽严程度</param> /// <returns></returns> public DependencyObject GetInspectionQty(object paraInspectionPlanId, object paraInspectionItemId, decimal paraQty, string paraDegree) { IQueryService querySrv = GetService <IQueryService>(); DependencyObject queryPlan = null; QueryNode node = OOQL.Select((Formulas.Case(null, OOQL.CreateProperty("A.MI_AQL"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("1"), OOQL.CreateProperty("A.CR_AQL")), OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("2"), OOQL.CreateProperty("A.MA_AQL"))), "AQL")), (Formulas.Case(null, OOQL.CreateProperty("A.MI_LQ"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("1"), OOQL.CreateProperty("A.CR_LQ")), OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("2"), OOQL.CreateProperty("A.MA_LQ"))), "LQ")), (Formulas.Case(null, OOQL.CreateProperty("A.MI_RQL"), OOQL.CreateCaseArray( OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("1"), OOQL.CreateProperty("A.CR_RQL")), OOQL.CreateCaseItem( OOQL.CreateProperty("B.DEFECT_CLASS") == OOQL.CreateConstants("2"), OOQL.CreateProperty("A.MA_RQL"))), "RQL")), OOQL.CreateProperty("A.INSPECTION_TIMES", "INSPECTION_TIMES"), OOQL.CreateProperty("B.IL", "IL"), OOQL.CreateProperty("B.DL", "DL"), OOQL.CreateProperty("A.INSPECTION_METHOD", "INSPECTION_METHOD"), OOQL.CreateProperty("A.AI_STANDARD", "AI_STANDARD"), OOQL.CreateProperty("A.VI_STANDARD", "VI_STANDARD"), OOQL.CreateProperty("A.CUSTOM_STANDARD_ID", "CUSTOM_STANDARD_ID"), OOQL.CreateProperty("B.DEFECT_CLASS", "DEFECT_CLASS"), OOQL.CreateProperty("A.INSPECTION_PLAN_ID", "INSPECTION_PLAN_ID"), OOQL.CreateProperty("B.INSPECTION_ITEM_ID", "INSPECTION_ITEM_ID"), OOQL.CreateProperty("A.MODE", "MODE")) .From("INSPECTION_PLAN", "A") .LeftJoin("INSPECTION_PLAN.INSPECTION_PLAN_D", "B") .On(OOQL.CreateProperty("A.INSPECTION_PLAN_ID") == OOQL.CreateProperty("B.INSPECTION_PLAN_ID")) .Where(OOQL.AuthFilter("INSPECTION_PLAN", "A") & OOQL.CreateProperty("A.INSPECTION_PLAN_ID") == OOQL.CreateConstants(paraInspectionPlanId) & OOQL.CreateProperty("B.INSPECTION_ITEM_ID") == OOQL.CreateConstants(paraInspectionItemId)); DependencyObjectCollection coll = querySrv.ExecuteDependencyObject(node); if (coll != null && coll.Count > 0) { queryPlan = coll[0]; } if (queryPlan == null) { return(null); } //6.2 根据抽样标准call不同的服务 //6.2.1 全检 DependencyObject result; if (queryPlan["INSPECTION_METHOD"].ToInt32() == 2) { return(null); } INumberedSampleTestService numberedSampleTestService = GetService <INumberedSampleTestService>("AI_STANDARD"); if (queryPlan["AI_STANDARD"].Equals("1") || queryPlan["AI_STANDARD"].Equals("4")) { result = numberedSampleTestService.GetNumberStandard(paraQty, paraDegree, queryPlan["IL"].ToString(), queryPlan["AQL"].ToString(), queryPlan["INSPECTION_TIMES"].ToString()); } else if (queryPlan["AI_STANDARD"].Equals("2")) { if (queryPlan["MODE"].Equals("1")) { result = numberedSampleTestService.GetNumberStandard(paraQty, queryPlan["LQ"].ToString(), queryPlan["INSPECTION_TIMES"].ToString()); } else { result = numberedSampleTestService.GetNumberStandard(paraQty, queryPlan["LQ"].ToString(), queryPlan["INSPECTION_TIMES"].ToString(), queryPlan["IL"].ToString()); } } else if (queryPlan["AI_STANDARD"].Equals("0")) { result = numberedSampleTestService.GetNumberStandard(queryPlan["DL"].ToString(), queryPlan["RQL"].ToString()); } else { return(null); } //6.2.4 抽检GB2829(暂不实现) //6.2.5 抽检自定义 if (!Maths.IsEmpty(queryPlan["CUSTOM_STANDARD_ID"])) { ICustomTestService customTestService = GetService <ICustomTestService>("CUSTOM_STANDARD"); DependencyObject obj = customTestService.GetCustomStandard(queryPlan["CUSTOM_STANDARD_ID"], paraQty); result["SAMPLE_SIZE_1ST"] = Convert.ToInt32(obj["SAMPLE_SIZE"].ToDecimal()); result["AC_1ST"] = Convert.ToInt32(obj["AC"].ToDecimal()); result["RE_1ST"] = Convert.ToInt32(obj["RE"].ToDecimal()); result["Result"] = obj["Result"].ToBoolean(); } //6.2.6 回传处理,如果样本量大于送检量,则样本量等于送检数量 zhufei //(/ / / /)>送检量(paraQty) decimal sampleSize1St = result["SAMPLE_SIZE_1ST"].ToDecimal(); decimal sampleSize2Nd = result["SAMPLE_SIZE_2ND"].ToDecimal(); decimal sampleSize3Rd = result["SAMPLE_SIZE_3RD"].ToDecimal(); decimal sampleSize4Th = result["SAMPLE_SIZE_4TH"].ToDecimal(); decimal sampleSize5Th = result["SAMPLE_SIZE_5TH"].ToDecimal(); if (result["SAMPLE_SIZE_1ST"].ToDecimal() > paraQty) { sampleSize1St = paraQty; } if (result["SAMPLE_SIZE_2ND"].ToDecimal() > paraQty) { sampleSize2Nd = paraQty; } if (result["SAMPLE_SIZE_3RD"].ToDecimal() > paraQty) { sampleSize3Rd = paraQty; } if (result["SAMPLE_SIZE_4TH"].ToDecimal() > paraQty) { sampleSize4Th = paraQty; } if (result["SAMPLE_SIZE_5TH"].ToDecimal() > paraQty) { sampleSize5Th = paraQty; } DependencyObjectType root01 = new DependencyObjectType("INSPECTION_QTY"); root01.RegisterSimpleProperty("SAMPLE_SIZE_1ST", typeof(decimal)); root01.RegisterSimpleProperty("AC_1ST", typeof(decimal)); root01.RegisterSimpleProperty("RE_1ST", typeof(decimal)); root01.RegisterSimpleProperty("SAMPLE_SIZE_2ND", typeof(decimal)); root01.RegisterSimpleProperty("AC_2ND", typeof(decimal)); root01.RegisterSimpleProperty("RE_2ND", typeof(decimal)); root01.RegisterSimpleProperty("SAMPLE_SIZE_3RD", typeof(decimal)); root01.RegisterSimpleProperty("AC_3RD", typeof(decimal)); root01.RegisterSimpleProperty("RE_3RD", typeof(decimal)); root01.RegisterSimpleProperty("SAMPLE_SIZE_4TH", typeof(decimal)); root01.RegisterSimpleProperty("AC_4TH", typeof(decimal)); root01.RegisterSimpleProperty("RE_4TH", typeof(decimal)); root01.RegisterSimpleProperty("SAMPLE_SIZE_5TH", typeof(decimal)); root01.RegisterSimpleProperty("AC_5TH", typeof(decimal)); root01.RegisterSimpleProperty("RE_5TH", typeof(decimal)); root01.RegisterSimpleProperty("Result", typeof(Boolean)); root01.RegisterSimpleProperty("Msg", typeof(String)); DependencyObject collection = new DependencyObject(root01); collection["SAMPLE_SIZE_1ST"] = sampleSize1St; // result["SAMPLE_SIZE_1ST"]; collection["AC_1ST"] = result["AC_1ST"]; collection["RE_1ST"] = result["RE_1ST"]; collection["SAMPLE_SIZE_2ND"] = sampleSize2Nd; // result["SAMPLE_SIZE_2ND"]; collection["AC_2ND"] = result["AC_2ND"]; collection["RE_2ND"] = result["RE_2ND"]; collection["SAMPLE_SIZE_3RD"] = sampleSize3Rd; // result["SAMPLE_SIZE_3RD"]; collection["AC_3RD"] = result["AC_3RD"]; collection["RE_3RD"] = result["RE_3RD"]; collection["SAMPLE_SIZE_4TH"] = sampleSize4Th; // result["SAMPLE_SIZE_4TH"]; collection["AC_4TH"] = result["AC_4TH"]; collection["RE_4TH"] = result["RE_4TH"]; collection["SAMPLE_SIZE_5TH"] = sampleSize5Th; // result["SAMPLE_SIZE_5TH"]; collection["AC_5TH"] = result["AC_5TH"]; collection["RE_5TH"] = result["RE_5TH"]; collection["Result"] = result["Result"]; collection["Msg"] = result["Msg"]; return(collection); }