/// <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);
}
