/// <summary> /// //7.2.3更新单头业务数量汇总 /// </summary> private void UpdateTD() { QueryNode selectNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID"), Formulas.Sum("BUSINESS_QTY", "SUM_BUSINESS_QTY")) .From("TRANSACTION_DOC.TRANSACTION_DOC_D", "TRANSACTION_DOC_D") .InnerJoin(_tempScan.Name, "temp") .On(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID") == OOQL.CreateProperty("temp.ID")) .GroupBy(OOQL.CreateProperty("TRANSACTION_DOC_D.TRANSACTION_DOC_ID")); QueryNode node = OOQL.Update("TRANSACTION_DOC") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("SUM_BUSINESS_QTY"), OOQL.CreateProperty("selectNode.SUM_BUSINESS_QTY")) }) .From(selectNode, "selectNode") .Where(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID") == OOQL.CreateProperty("selectNode.TRANSACTION_DOC_ID")); _qurService.ExecuteNoQueryWithManageProperties(node); }
//20170905 add by wangyq for P001-170717001 =================begin=================== /// <summary> /// 利用临时表关联实体表进行批量更新领料单 /// </summary> private void UpdateBcCheckStatus(IQueryService qrySrv, IDataEntityType tempEntityD) { QueryNode selectNode = OOQL.Select(true, OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_D_ID")) .From("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D") .InnerJoin("ISSUE_RECEIPT", "ISSUE_RECEIPT") .On(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID")) .InnerJoin(tempEntityD.Name, "TEMP") .On(OOQL.CreateProperty("TEMP.DOC_NO") == OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") & OOQL.CreateProperty("TEMP.SequenceNumber") == OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber")); QueryNode node = OOQL.Update("ISSUE_RECEIPT.ISSUE_RECEIPT_D") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("BC_CHECK_STATUS"), OOQL.CreateConstants("2")) }) .From(selectNode, "selectNode") .Where(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ISSUE_RECEIPT_D_ID") == OOQL.CreateProperty("selectNode.ISSUE_RECEIPT_D_ID")); qrySrv.ExecuteNoQueryWithManageProperties(node); }
/// <summary> /// 利用临时表关联实体表进行批量更新领料单 /// </summary> private void UpdateSalesIssueD(IQueryService qrySrv, IDataEntityType tempEntityD) { QueryNode selectNode = OOQL.Select(true, OOQL.CreateProperty("SALES_ISSUE_D.SALES_ISSUE_D_ID")) .From("SALES_ISSUE.SALES_ISSUE_D", "SALES_ISSUE_D") .InnerJoin("SALES_ISSUE", "SALES_ISSUE") .On(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID") == OOQL.CreateProperty("SALES_ISSUE_D.SALES_ISSUE_ID")) .InnerJoin(tempEntityD.Name, "TEMP") .On(OOQL.CreateProperty("TEMP.doc_no") == OOQL.CreateProperty("SALES_ISSUE.DOC_NO") & OOQL.CreateProperty("TEMP.seq") == OOQL.CreateProperty("SALES_ISSUE_D.SequenceNumber")); QueryNode node = OOQL.Update("SALES_ISSUE.SALES_ISSUE_D") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("BC_CHECK_STATUS"), OOQL.CreateConstants("2")) }) .From(selectNode, "selectNode") .Where(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_D.SALES_ISSUE_D_ID") == OOQL.CreateProperty("selectNode.SALES_ISSUE_D_ID")); qrySrv.ExecuteNoQueryWithManageProperties(node); }
/// <summary> /// 更新条码冻结状态服务实现 /// </summary> /// <param name="barcodeNo">条码</param> /// <param name="status">凍結狀態:N.未冻结,Y.冻结</param> /// <param name="siteNo">营运据点</param> public void UpdateBarCodeFrozenStatus(string barcode_no, string status, string site_no) //20170413 modi by wangrm for P001-170412001 OLD:barcodeNo { #region 参数检查 if (Maths.IsEmpty(barcode_no)) { var infoCodeSer = GetService <IInfoEncodeContainer>(); //信息编码服务 throw new BusinessRuleException(infoCodeSer.GetMessage("A111201", "barcode_no")); //‘入参【barcode_no】未传值’ } if (Maths.IsEmpty(status)) { var infoCodeSer = GetService <IInfoEncodeContainer>(); //信息编码服务 throw new BusinessRuleException(infoCodeSer.GetMessage("A111201", "status")); //‘入参【status】未传值’ } #endregion QueryNode updateNode = OOQL.Update("BC_RECORD", new SetItem[] { new SetItem(OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS"), OOQL.CreateConstants(status)) }) .Where(OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcode_no)); this.GetService <IQueryService>().ExecuteNoQueryWithManageProperties(updateNode); }
/// <summary> /// 更新批号 /// </summary> /// <param name="item_no">料件编号</param> /// <param name="item_feature_no">产品特征</param> /// <param name="site_no">营运据点</param> /// <param name="lot_no">批号</param> /// <param name="lot_description">批号说明</param> /// <param name="effective_date">生效日期</param> /// <param name="effective_deadline">有效截止日</param> /// <param name="remarks">备注</param> /// <returns></returns> public void UpdateLot(string item_no, string item_feature_no, string site_no, string lot_no, string lot_description, string effective_date, string effective_deadline, string remarks) { QueryNode selectNode = OOQL.Select(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .From("ITEM_LOT", "ITEM_LOT") .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")); QueryConditionGroup conditionGroup = OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateConstants(item_no, GeneralDBType.String) & OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateConstants(lot_no, GeneralDBType.String); DateTime dtEffection = effective_date.ToDate(); DateTime dtInEffection = effective_deadline.ToDate(); if (!string.IsNullOrEmpty(item_feature_no)) { conditionGroup &= OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE") == OOQL.CreateConstants(item_feature_no, GeneralDBType.String); //20170228 mark by shenbao for B001-170206007 将这里的语句往下放 ===begin === //selectNode = ((JoinOnNode)selectNode).InnerJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") // .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") // & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) // .Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup)); //20170228 mark by shenbao for B001-170206007 ===end === } //20170228 add by shenbao for B001-170206007 ===begin === selectNode = ((JoinOnNode)selectNode).LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20170228 modi by shenbao for B001-170206007 InnerJoin==>LeftJoin .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup)); //20170228 add by shenbao for B001-170206007 ===end === List <SetItem> updateList = new List <SetItem>(); updateList.Add(new SetItem(OOQL.CreateProperty("LOT_DESCRIPTION"), OOQL.CreateConstants(lot_description, GeneralDBType.String))); updateList.Add(new SetItem(OOQL.CreateProperty("EFFECTIVE_DATE"), OOQL.CreateConstants(dtEffection, GeneralDBType.Date))); updateList.Add(new SetItem(OOQL.CreateProperty("INEFFECTIVE_DATE"), OOQL.CreateConstants(dtInEffection, GeneralDBType.Date))); updateList.Add(new SetItem(OOQL.CreateProperty("REMARK"), OOQL.CreateConstants(remarks, GeneralDBType.String))); QueryNode node = OOQL.Update("ITEM_LOT").Set(updateList.ToArray()) .From(selectNode, "selectNode") .Where(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("selectNode.ITEM_LOT_ID")); this.GetService <IQueryService>().ExecuteNoQueryWithManageProperties(node); }
/// <summary> /// 更新调拨单单头 /// </summary> private void UpdateTransactionDoc() { #region 查询 #region 20170719 modi by zhangcn for B001-170717002 //_queryNode = // OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "DOC_NO"), // Formulas.Sum(OOQL.CreateProperty("TRANSACTION_DOC_D.BUSINESS_QTY"), "SUM_BUSINESS_QTY")) // .From(_TEMP_SCAN_DETAIL.Name, "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"))) // .GroupBy(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO")) ; _queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "DOC_NO"), Formulas.Sum(OOQL.CreateProperty("TEMP_SCAN_DETAIL.picking_qty"), "SUM_BUSINESS_QTY")) .From(_TEMP_SCAN_DETAIL.Name, "TEMP_SCAN_DETAIL") .InnerJoin("TRANSACTION_DOC", "TRANSACTION_DOC") .On(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO") == OOQL.CreateProperty("TEMP_SCAN_DETAIL.doc_no")) .GroupBy(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO")); #endregion #endregion #region 执行修改 QueryNode updateNode = OOQL.Update("TRANSACTION_DOC") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("SUM_BUSINESS_QTY"), OOQL.CreateProperty("SelectNode.SUM_BUSINESS_QTY")) }) .From(_queryNode, "SelectNode") .Where(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO") == OOQL.CreateProperty("SelectNode.DOC_NO"));//20170713 modi by zhangcn for B002-170713010 还原 Where条件,以前被注释掉了 #endregion _querySrv.ExecuteNoQueryWithManageProperties(updateNode); }
private QueryNode GetUpdateNode(string counting_type, string countingNo, QueryNode querySumBarcodeNode) { return(OOQL.Update("COUNTING_PLAN_BARCODE") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("COUNTING_QTY"), Formulas.Case(null, OOQL.CreateProperty("querySumBarcode.QTY"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("COUNTING_PLAN_BARCODE.COUNTING_TYPE") == OOQL.CreateConstants(counting_type), OOQL.CreateProperty("COUNTING_PLAN_BARCODE.COUNTING_QTY") + OOQL.CreateProperty("querySumBarcode.QTY")) })), new SetItem(OOQL.CreateProperty("COUNTING_TYPE"), OOQL.CreateConstants(counting_type)) }) .From(querySumBarcodeNode, "querySumBarcode") .Where(OOQL.CreateProperty("COUNTING_PLAN_BARCODE.DOC_NO") == OOQL.CreateConstants(countingNo) & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BARCODE_NO") == OOQL.CreateProperty("querySumBarcode.barcode_no") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.PLANT_ID") == OOQL.CreateProperty("querySumBarcode.PLANT_ID") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_ID") == OOQL.CreateProperty("querySumBarcode.ITEM_ID") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_FEATURE_ID") == OOQL.CreateProperty("querySumBarcode.ITEM_FEATURE_ID") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.WAREHOUSE_ID") == OOQL.CreateProperty("querySumBarcode.WAREHOUSE_ID") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BIN_ID") == OOQL.CreateProperty("querySumBarcode.BIN_ID") & OOQL.CreateProperty("COUNTING_PLAN_BARCODE.ITEM_LOT_ID") == OOQL.CreateProperty("querySumBarcode.ITEM_LOT_ID") //20170505 mark by shenbao for P001-170505001 ===begin=== //OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BO_ID.ROid") == OOQL.CreateProperty("querySumBarcode.BO_ID_ROid") & //OOQL.CreateProperty("COUNTING_PLAN_BARCODE.BO_ID.RTK") == OOQL.CreateProperty("querySumBarcode.BO_ID_RTK") //20170505 mark by shenbao for P001-170505001 ===end=== )); }
/// <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> private void UpdateIssueReceiptD(IQueryService qrySrv, IDataEntityType tmpIssueReceipt) { QueryNode groupNode = GroupNode(tmpIssueReceipt, true); #region 查询 QueryNode selectNode = OOQL.Select(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_D_ID") //领料单主键 , OOQL.CreateProperty("TmpIssueReceipt.ISSUE_RECEIPT_QTY") //领退料数量 , Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "UNIT_ID") //单位 //20161208 modi by shenbao fro P001-161208001 , Formulas.Ext("UNIT_CONVERT", "SECOND_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID") , OOQL.CreateProperty("UNIT.UNIT_ID") , OOQL.CreateProperty("TmpIssueReceipt.ISSUE_RECEIPT_QTY") , OOQL.CreateProperty("ITEM.SECOND_UNIT_ID") , OOQL.CreateConstants(0) }) //领料第二数量 , Formulas.Ext("UNIT_CONVERT", "ACTUAL_SECOND_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID") , OOQL.CreateProperty("UNIT.UNIT_ID") , OOQL.CreateProperty("TmpIssueReceipt.ISSUE_RECEIPT_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("TmpIssueReceipt.ISSUE_RECEIPT_QTY") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateConstants(0) }) //领料库存数量 , Formulas.Ext("UNIT_CONVERT", "ACTUAL_INVENTORY_QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID") , OOQL.CreateProperty("UNIT.UNIT_ID") , OOQL.CreateProperty("TmpIssueReceipt.ISSUE_RECEIPT_QTY") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateConstants(0) }) //实际库存数量 , Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "WAREHOUSE_ID") //仓库 //20161208 modi by shenbao fro P001-161208001 , Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("BIN.BIN_ID"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("TmpIssueReceipt.BIN_CODE") == OOQL.CreateConstants(""), OOQL.CreateConstants(Maths.GuidDefaultValue())) }), OOQL.CreateConstants(Maths.GuidDefaultValue()), "BIN_ID") //库位 //20161208 modi by shenbao fro P001-161208001 , Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("TmpIssueReceipt.LOT_CODE") == OOQL.CreateConstants(""), OOQL.CreateConstants(Maths.GuidDefaultValue())) }), OOQL.CreateConstants(Maths.GuidDefaultValue()), "ITEM_LOT_ID") //批号 //20161208 modi by shenbao fro P001-161208001 , Formulas.Case(null, OOQL.CreateConstants("COST_DOMAIN"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(1), OOQL.CreateConstants("COMPANY")) }, "COST_DOMAIN_ID_RTK") //成本域类型 , Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("WAREHOUSE.COST_DOMAIN_ID"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(1), OOQL.CreateProperty("PLANT.PLANT_ID")), new CaseItem(OOQL.CreateProperty("PARA_COMPANY.INVENTORY_VALUATION_LEVEL") == OOQL.CreateConstants(2), OOQL.CreateProperty("PLANT.COST_DOMAIN_ID")) }), OOQL.CreateConstants(Maths.GuidDefaultValue()), "COST_DOMAIN_ID_ROid") //成本域 //20161208 modi by shenbao fro P001-161208001 ) .From("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D") .InnerJoin("ISSUE_RECEIPT") .On(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID")) .InnerJoin(groupNode, "TmpIssueReceipt") .On(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") == OOQL.CreateProperty("TmpIssueReceipt.DOC_NO") & OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber") == OOQL.CreateProperty("TmpIssueReceipt.SequenceNumber")) .InnerJoin("PLANT") .On(OOQL.CreateProperty("TmpIssueReceipt.PLANT_CODE") == OOQL.CreateProperty("PLANT.PLANT_CODE")) .InnerJoin("ITEM") .On(OOQL.CreateProperty("TmpIssueReceipt.ITEM_CODE") == OOQL.CreateProperty("ITEM.ITEM_CODE")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID") & OOQL.CreateProperty("TmpIssueReceipt.ITEM_FEATURE_CODE") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE")) .InnerJoin("UNIT") .On(OOQL.CreateProperty("TmpIssueReceipt.UNIT_CODE") == OOQL.CreateProperty("UNIT.UNIT_CODE")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("TmpIssueReceipt.WAREHOUSE_CODE") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") & OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("BIN.WAREHOUSE_ID") & OOQL.CreateProperty("TmpIssueReceipt.BIN_CODE") == OOQL.CreateProperty("BIN.BIN_CODE")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("TmpIssueReceipt.LOT_CODE") == OOQL.CreateProperty("ITEM_LOT.LOT_CODE") & OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_ID") & ((OOQL.CreateProperty("TmpIssueReceipt.ITEM_FEATURE_CODE") == OOQL.CreateConstants("") & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue())) | (OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID")))) .InnerJoin("PARA_COMPANY") .On(OOQL.CreateProperty("PLANT.COMPANY_ID") == OOQL.CreateProperty("PARA_COMPANY.Owner_Org.ROid")); #endregion #region 执行修改 QueryNode updateNode = OOQL.Update("ISSUE_RECEIPT.ISSUE_RECEIPT_D") .Set(new SetItem[] { new SetItem(OOQL.CreateProperty("ISSUE_RECEIPT_QTY"), OOQL.CreateProperty("SelectNode.ISSUE_RECEIPT_QTY")), new SetItem(OOQL.CreateProperty("ACTUAL_ISSUE_RECEIPT_QTY"), OOQL.CreateProperty("SelectNode.ISSUE_RECEIPT_QTY")), new SetItem(OOQL.CreateProperty("UNIT_ID"), OOQL.CreateProperty("SelectNode.UNIT_ID")), new SetItem(OOQL.CreateProperty("SECOND_QTY"), OOQL.CreateProperty("SelectNode.SECOND_QTY")), new SetItem(OOQL.CreateProperty("ACTUAL_SECOND_QTY"), OOQL.CreateProperty("SelectNode.ACTUAL_SECOND_QTY")), new SetItem(OOQL.CreateProperty("INVENTORY_QTY"), OOQL.CreateProperty("SelectNode.INVENTORY_QTY")), new SetItem(OOQL.CreateProperty("ACTUAL_INVENTORY_QTY"), OOQL.CreateProperty("SelectNode.ACTUAL_INVENTORY_QTY")), new SetItem(OOQL.CreateProperty("REPLACED_QTY"), OOQL.CreateProperty("SelectNode.ISSUE_RECEIPT_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")), }) .From(selectNode, "SelectNode") .Where(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ISSUE_RECEIPT_D_ID") == OOQL.CreateProperty("SelectNode.ISSUE_RECEIPT_D_ID")); #endregion qrySrv.ExecuteNoQueryWithManageProperties(updateNode); }