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