//20170302 add by shenbao for B001-170221021
private DependencyObjectCollection QueryBCRecord(string siteNo, string barcode_no)
{
object propertyID = QueryItemLot();
object qtPropertyID = QueryItemQty(); //20170302 add by shenbao for P001-170302002
QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no") //企业编号
, OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点
, OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no") //条码编号
, OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号
,
OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION")
, ArithmeticOperators.Plus, "item_name_spec") //品名
,
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no") //产品特征码
, OOQL.CreateConstants(string.Empty, "warehouse_no") //库位
, OOQL.CreateConstants(string.Empty, "storage_spaces_no") //储位
,
Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_VALUE"), OOQL.CreateConstants(string.Empty),
"lot_no") //批号
//, OOQL.CreateConstants(0m, "inventory_qty") //数量 //20170302 mark by shenbao for P001-170302002
,
Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_VALUE"), OOQL.CreateConstants(0),
"barcode_qty") //20170302 ADD by shenbao for P001-170302002
)
.From("BC_RECORD", "BC_RECORD")
.InnerJoin("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"))
.LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D")
.On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D.BC_RECORD_ID")
& OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_ID") == OOQL.CreateConstants(propertyID))
//20170302 add by shenbao for P001-170302002 ===begin===
.LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D_QTY")
.On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D_QTY.BC_RECORD_ID")
& OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_ID") == OOQL.CreateConstants(qtPropertyID))
//20170302 add by shenbao for P001-1703020002 ===end===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID"))
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where((OOQL.AuthFilter("BC_RECORD", "BC_RECORD")
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
| OOQL.CreateConstants(siteNo) == OOQL.CreateConstants(string.Empty))
// 20171023 add by 08628 for P001-171023001 ↓
& (OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcode_no)
| OOQL.CreateConstants(barcode_no) == OOQL.CreateConstants(string.Empty))
// 20171023 add by 08628 for P001-171023001 ↑
));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
/// <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="queryService">查询服务</param>
/// <param name="barcodeNo">条形码编号</param>
/// <param name="siteNo">营运据点</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private DependencyObjectCollection GetMoProduct(IQueryService queryService, string barcodeNo, string siteNo, string programJobNo, string status)
{
QueryNode node =
OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no"), //企业编号//20170328 modi by wangyq for P001-170327001 添加distinct
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点
OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业
OOQL.CreateProperty("MO.DOC_NO", "source_no"), //来源单号
OOQL.CreateConstants(programJobNo + status, "doc_type"), //单据类型
OOQL.CreateProperty("MO.DOC_DATE", "create_date"), // 单据日期
OOQL.CreateProperty("MO_PRODUCT.SequenceNumber", "seq"), //单据项次
OOQL.CreateConstants(0, GeneralDBType.Int32, "doc_line_seq"), //单据项序
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), //料件编号
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(""), "item_feature_no"), //产品特征
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(""), "item_feature_name"), //产品特征说明
OOQL.CreateConstants("", "warehouse_no"), //库位
OOQL.CreateConstants("", "storage_spaces_no"), //储位
OOQL.CreateConstants("", "lot_no"), // 批号
OOQL.CreateConstants("", "object_no"), // 对象编号
OOQL.CreateProperty("MO_PRODUCT.PLAN_QTY", "doc_qty"), //单据数量
OOQL.CreateProperty("MO_PRODUCT.COMPLETED_QTY", "in_out_qty"), //出入数量
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "unit_no"), //单位
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), //品名
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), //规格
OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.RECEIPT_OVERRUN_RATE"), OOQL.CreateConstants(0m, GeneralDBType.Decimal))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"), //允许误差率 //20170302 modi by shenbao for P001-170302002 误差率统一乘100
Formulas.Case(null, OOQL.CreateConstants("1"),
new CaseItem[] {
new CaseItem(Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("")) == OOQL.CreateConstants("N"),
OOQL.CreateConstants("2"))
},
"lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
new object[] {
OOQL.CreateProperty("MO_PRODUCT.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("MO_PRODUCT.UNIT_ID"),
OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
new object[] {
OOQL.CreateProperty("MO_PRODUCT.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("MO_PRODUCT.UNIT_ID"),
OOQL.CreateConstants(0)
}), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "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
)
.From("MO.MO_PRODUCT", "MO_PRODUCT")
.InnerJoin("MO", "MO")
.On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_PRODUCT.MO_ID"))
//20170410 mark by wangrm SD口述===start===========
//.LeftJoin("MO.MO_D", "MO_D")
//.On(OOQL.CreateProperty("MO_D.MO_ID") == OOQL.CreateProperty("MO.MO_ID"))
//20170410 mark by wangrm SD口述===end===========
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_FEATURE_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")) //20170410 modi by wangrm SD口述OLD:MO_D->MO_PRODUCT
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID") &
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"))
//20170504 modi by wangyq for P001-170427001 ===============begin====================
.InnerJoin("BC_RECORD", "BC_RECORD")
.On(OOQL.CreateProperty("BC_RECORD.SOURCE_D_ID.ROid") == OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID"))
.Where(OOQL.AuthFilter("MO.MO_PRODUCT", "MO_PRODUCT") &
OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcodeNo) &
//.LeftJoin("BC_RECORD", "BC_RECORD")
//.On(OOQL.CreateProperty("BC_RECORD.SOURCE_D_ID.ROid") == OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID"))
//.Where(Formulas.IsNull(OOQL.CreateProperty("BC_RECORD.BARCODE_NO"), OOQL.CreateConstants("")) == OOQL.CreateConstants(barcodeNo) &
//20170504 modi by wangyq for P001-170427001 ===============end====================
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) &
OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y"));
DependencyObjectCollection coll = queryService.ExecuteDependencyObject(node);
return(coll);
}
/// <summary>
/// 获取工单产出信息查询信息
/// </summary>
/// <param name="id">主键</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo"></param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetMOProductQueryNode(string id, string[] docNo, string siteNo, string programJobNo, string status) //20170724 modi by shenbao for P001-170717001 添加参数docno
{
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("MO_PRODUCT.PLAN_QTY", "doc_qty"),
Formulas.Case(null, OOQL.CreateProperty("MO_PRODUCT.COMPLETED_QTY"), new CaseItem[] {
new CaseItem(OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("9-1")
, OOQL.CreateProperty("MO_PRODUCT.REQ_QTY"))
}, "in_out_qty"), //20170724 modi by shenbao for P001-170717001
Formulas.Cast(OOQL.CreateProperty("MO_PRODUCT.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("MO.DOC_NO", "source_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
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("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants("9", "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateConstants(string.Empty, "warehouse_no"),
OOQL.CreateConstants(string.Empty, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "doc_line_seq"),
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.RECEIPT_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("MO_PRODUCT.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("MO_PRODUCT.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_PRODUCT.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===
// add by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
)
.From("MO.MO_PRODUCT", "MO_PRODUCT")
.LeftJoin("MO", "MO")
.On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_PRODUCT.MO_ID"))
.LeftJoin("MO.MO_D", "MO_D")
.On(OOQL.CreateProperty("MO.MO_ID") == OOQL.CreateProperty("MO_D.MO_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("MO_PRODUCT.ITEM_FEATURE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_PRODUCT.UNIT_ID")) //20170724 modi by shenbao for P001-170717001 用工单产出信息的单位
//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("MO.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===end===
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("MO.MO_PRODUCT", "MO_PRODUCT")) &
((OOQL.CreateProperty("MO_PRODUCT.MO_PRODUCT_ID") == OOQL.CreateConstants(id) | (OOQL.CreateProperty("MO.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)))) & //20170724 modi by shenbao for P001-170717001
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")) &
((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("9-1") & OOQL.CreateProperty("MO.RECEIPT_REQ_CONTROL") == OOQL.CreateConstants(true))
| (OOQL.CreateConstants(programJobNo) != OOQL.CreateConstants("9-1"))))); //20170724 add by shenbao for P001-170717001
return(queryNode);
}
/// <summary>
/// 获取采购订单QueryNode
/// </summary>
/// <param name="docNo">单据编号</param>
/// <param name="siteNo">工厂</param>
/// <param name="programJobNo">作业编号</param>
/// <param name="scanType"></param>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetPurchaseOrderQueryNode(string[] docNo, string siteNo, string programJobNo, string scanType,
string id, string status)
{
//20161216 add by liwei1 for P001-161215001
//private QueryNode GetPurchaseOrderQueryNode(string docNo, string siteNo, string programJobNo,string scanType,string id,string status) {//20161216 mark by liwei1 for P001-161215001
//where条件中存在Or条件，考虑到性能问题：OOQL写成Union形式
// 条件：
// （【入参scan_type】 = 1.箱条码’
//AND 采购订单子单身.主键(PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID) = 【入参ID】）
//OR(【入参scan_type】 = 2.单据条码
//采购订单信息.单号(PURCHASE_ORDER.DOC_NO) = 【入参doc_no】 AND)
List <QueryProperty> lstQueryProperties = new List <QueryProperty>();
lstQueryProperties.AddRange(new QueryProperty[]
{
OOQL.CreateConstants("99", GeneralDBType.String, "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "source_operation"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "source_no"),
OOQL.CreateConstants(programJobNo + status, GeneralDBType.String, "doc_type"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Int32, "doc_batch_seq"),
//OOQL.CreateConstants(string.Empty, GeneralDBType.String, "object_no"),//20170903 mark by liwei1 for B001-170904001
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "object_no"), //20170903 add by liwei1 for B001-170904001
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"),
Formulas.IsNull(
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "warehouse_no"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String, "lot_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "in_out_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE", "in_out_date1"),
//20170511 add by liwei1 for P001-170420001
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_CODE", "main_organization"),
//20160110 add by shenbao for P001-170110001 ===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"), //规格
OOQL.CreateArithmetic(
Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),
//允许误差率 //20170302 modi by shenbao for P001-170302002 误差率统一乘100
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("PURCHASE_ORDER_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[]
{
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0)
}), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty),
"inventory_unit"), //库存单位
//20160110 add by shenbao for P001-170110001 ===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
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
}
);
#region 20170619 add by zhangcn for P001-170606002 PURCHASE_ORDER.ALL_SYNERGY = True
QueryNode nodeAllSynery =
OOQL.Select(lstQueryProperties)
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
//20170619 add by zhangcn for P001-170606002 ===begin===
.LeftJoin("SUPPLY_SYNERGY", "SUPPLY_SYNERGY")
.On(OOQL.CreateProperty("SUPPLY_SYNERGY.SUPPLY_SYNERGY_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.GROUP_SYNERGY_ID.ROid"))
.LeftJoin("PLANT", "PLANTSSY")
.On(OOQL.CreateProperty("PLANTSSY.PLANT_ID") ==
OOQL.CreateProperty("SUPPLY_SYNERGY.REQUIRE_Owner_Org.ROid"))
//20170619 add by zhangcn for P001-170606002 ===end===
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID"))) //20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where(OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("1") == OOQL.CreateConstants(scanType, GeneralDBType.String))
&
(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") ==
OOQL.CreateConstants(id, GeneralDBType.Guid))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.GENERATE_STATUS") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.INVOICE_COMPANY_ID") ==
OOQL.CreateProperty("PLANTSSY.COMPANY_ID")) //20170619 add by zhangcn for P001-170606002
& (OOQL.CreateProperty("SUPPLY_SYNERGY.GENERATE_DIRE") == OOQL.CreateConstants("2"))
//20170619 add by zhangcn for P001-170606002
) //20170619 add by zhangcn for P001-170606002
.Union(
OOQL.Select(lstQueryProperties)
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
//20170619 add by zhangcn for P001-170606002 ===begin===
.LeftJoin("SUPPLY_SYNERGY", "SUPPLY_SYNERGY")
.On(OOQL.CreateProperty("SUPPLY_SYNERGY.SUPPLY_SYNERGY_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.GROUP_SYNERGY_ID.ROid"))
.LeftJoin("PLANT", "PLANTSSY")
.On(OOQL.CreateProperty("PLANTSSY.PLANT_ID") ==
OOQL.CreateProperty("SUPPLY_SYNERGY.REQUIRE_Owner_Org.ROid"))
//20170619 add by zhangcn for P001-170606002 ===end===
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where(OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("2", GeneralDBType.String) == OOQL.CreateConstants(scanType))
&
(OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO")
.In(OOQL.CreateDyncParameter("DOC_NO1", docNo)))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.GENERATE_STATUS") ==
OOQL.CreateConstants(1, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
&
(OOQL.CreateProperty("PURCHASE_ORDER.INVOICE_COMPANY_ID") ==
OOQL.CreateProperty("PLANTSSY.COMPANY_ID"))
//20170619 add by zhangcn for P001-170606002
& (OOQL.CreateProperty("SUPPLY_SYNERGY.GENERATE_DIRE") == OOQL.CreateConstants("2"))
//20170619 add by zhangcn for P001-170606002
)
); //20161216 add by liwei1 for P001-161215001
#endregion
return(OOQL.Select(lstQueryProperties)
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where(OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("1") == OOQL.CreateConstants(scanType, GeneralDBType.String))
&
(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") ==
OOQL.CreateConstants(id, GeneralDBType.Guid))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(0, GeneralDBType.Boolean)) //20170619 add by zhangcn for P001-170606002
)
.Union(
OOQL.Select(lstQueryProperties)
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On((OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID")))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
//20160110 add by shenbao for P001-170110001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
&
OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20160110 add by shenbao for P001-170110001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where(OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER")
&
(OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") ==
OOQL.CreateConstants("Y", GeneralDBType.String))
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") ==
OOQL.CreateConstants(siteNo, GeneralDBType.String))
& (OOQL.CreateConstants("2", GeneralDBType.String) == OOQL.CreateConstants(scanType))
&
(OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO")
.In(OOQL.CreateDyncParameter("DOC_NO2", docNo)))
&
(OOQL.CreateProperty("PURCHASE_ORDER.ALL_SYNERGY") ==
OOQL.CreateConstants(0, GeneralDBType.Boolean))
//20170619 add by zhangcn for P001-170606002
)).Union(nodeAllSynery));
//20170619 add by zhangcn for P001-170606002 //20161216 add by liwei1 for P001-161215001
//& (OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateConstants(docNo, GeneralDBType.String))));//20161216 mark by liwei1 for P001-161215001
}
private QueryNode GetPurchaseDetail(string supplierNo, string dateS, string dateE, string dueDateS, string dueDateE,
string itemNo, string itemName, string itemSpec, string siteNo
, string purchase_no//20170919 add by liwei1 for B001-170918003
)
{
QueryNode subNodeFilArrival = GetFilArrivalQueryNode();
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_DATE", "purchase_date"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE", "so_due_date"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "line_seq"),
OOQL.CreateConstants(0, GeneralDBType.Int32, "batch_seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), //20170504 add by wangyq for P001-161209002
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), //20171010 modi by zhangcn for B001-171010004
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateConstants("N", GeneralDBType.String, "qc_type"), //20170619 modi by zhangcn for B001-170629006 【OLD：null】
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "purchase_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "receipt_qty"),
//OOQL.CreateArithmetic(
// OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"),
// OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub,"unpaid_qty"),
OOQL.CreateArithmetic(OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY"), OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY"), ArithmeticOperators.Sub),
Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0)),
ArithmeticOperators.Sub,
"unpaid_qty"),
Formulas.IsNull(OOQL.CreateProperty("FILARR.AQTY"), OOQL.CreateConstants(0), "on_the_way_qty"), //20170619 modi by zhangcn for B001-170629006 【OLD：0】
Formulas.IsNull(
OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE") * OOQL.CreateConstants(100, GeneralDBType.Decimal),
OOQL.CreateConstants(0, GeneralDBType.Decimal), "over_deliver_rate"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.REMARK", "remark"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"),
Formulas.IsNull(
OOQL.CreateProperty("PRICE_UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.PRICE_QTY", "valuation_qty"),
OOQL.CreateConstants(0, GeneralDBType.Decimal, "returned_qty"), //20170619 add by zhangcn for B001-170629006
OOQL.CreateConstants(1, GeneralDBType.Decimal, "box_qty")) //20170619 add by zhangcn for B001-170629006
.From("PURCHASE_ORDER", "PURCHASE_ORDER")
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID")))
//20170504 add by wangyq for P001-161209002 ========================begin=========================
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
//20170504 add by wangyq for P001-161209002 ========================end=========================
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On((OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
& (OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Emp")))
.LeftJoin("UNIT", "PRICE_UNIT")
.On((OOQL.CreateProperty("PRICE_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PRICE_UNIT_ID")))
//20170619 add by zhangcn for B001-170629006 ===begin===
.LeftJoin(subNodeFilArrival, "FILARR")
.On(OOQL.CreateProperty("FILARR.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") &
OOQL.CreateProperty("FILARR.ORDER_SE") == OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber") &
OOQL.CreateProperty("FILARR.ORDER_SE_SE") == OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber"));
//20170619 add by zhangcn for B001-170629006 ===begin===
//如果起始日期为空格，空值，null时默认最小日期
if (Maths.IsEmpty(dateS.ToDate()))
{
dateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
if (Maths.IsEmpty(dueDateS.ToDate()))
{
dueDateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
//如果结束日期为空格，空值，null时默认最大日期
if (Maths.IsEmpty(dateE.ToDate()))
{
dateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
if (Maths.IsEmpty(dueDateE.ToDate()))
{
dueDateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC"))
& ((OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") == OOQL.CreateConstants("Y"))
//& (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIPTED_BUSINESS_QTY"))//20170903 mark by liwei1 for B001-170901012
& (OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY")) //20170903 add by liwei1 for B001-170901012
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& ((OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") >= OOQL.CreateConstants(dateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ORDER.DOC_DATE") <= OOQL.CreateConstants(dateE.ToDate())))
& ((OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") >= OOQL.CreateConstants(dueDateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ORDER_SD.PLAN_ARRIVAL_DATE") <= OOQL.CreateConstants(dueDateE.ToDate()))));
//如果【品号】不为空增加条件
if (!Maths.IsEmpty(itemNo))
{
conditionGroup &= (OOQL.CreateProperty("ITEM.ITEM_CODE").Like(OOQL.CreateConstants("%" + itemNo + "%")));
}
//如果【品名】不为空增加条件
if (!Maths.IsEmpty(itemName))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_DESCRIPTION").Like(OOQL.CreateConstants("%" + itemName + "%")));
}
//如果【规格】不为空增加条件
if (!Maths.IsEmpty(itemSpec))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_SPECIFICATION").Like(OOQL.CreateConstants("%" + itemSpec + "%")));
}
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
//20170919 add by liwei1 for B001-170918003 ===begin===
//如果采购单号不为空增加条件
if (!Maths.IsEmpty(purchase_no))
{
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateConstants(purchase_no));
}
//20170919 add by liwei1 for B001-170918003 ===end===
return(joinOnNode.Where(conditionGroup)
//20170919 add by liwei1 for B001-170918003 ===begin===
.OrderBy(
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"), SortType.Asc),
OOQL.CreateOrderByItem(
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber"), SortType.Asc))
//20170919 add by liwei1 for B001-170918003 ===end===
);
}
/// <summary>
/// 获取工单信息查询信息
/// </summary>
/// <param name="docNo">单据编号</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetMOQueryNode(string[] docNo, string siteNo, string programJobNo, string status)
{
//20161216 add by liwei1 for P001-161215001
//private QueryNode GetMOQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("MO.DOC_NO", "source_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
Formulas.Cast(OOQL.CreateProperty("MO_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
//OOQL.CreateProperty("MO_D.REQUIRED_QTY", "doc_qty"),//20161208 mark by shenbao fro P001-161208001
//OOQL.CreateProperty("MO_D.ISSUED_QTY", "in_out_qty"),//20161208 mark by shenbao fro P001-161208001
//20161208 add by shenbao fro P001-161208001 ===begin===
Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("MO_D.ISSUED_QTY"), new CaseItem[]
{
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%"))
, OOQL.CreateProperty("MO_D.REQUIRED_QTY")),
}), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "doc_qty"), //需领用量
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants(0), new CaseItem[]
{
new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%"))
, OOQL.CreateProperty("MO_D.ISSUED_QTY")),
}), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "in_out_qty"), //已领用量
//20161208 add by shenbao fro P001-161208001 ===end===
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"),
//20170223 modi by shenbao for B001-170223012 这个企业编号为字符串，无需转换
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "doc_line_seq"),
Formulas.Cast(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.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("MO_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_D.UNIT_ID")
, OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[]
{
OOQL.CreateProperty("MO_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("MO_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("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("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("MO_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("MO_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("MO_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("MO_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("MO_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("MO_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("MO.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("MO.MO_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("MO.MO_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("MO.MO_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("MO", "MO")) &
((OOQL.CreateProperty("MO.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &
//20161216 add by liwei1 for P001-161215001
//((OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y"))));
return(queryNode);
}
private QueryNode GetPurchaseReturnNode(string programJobNo, string scanType, string ID, string siteNo, string status, string[] docNo)
{
string docType = programJobNo + status;
QueryConditionGroup whereNode = (OOQL.AuthFilter("PURCHASE_RETURN", "PURCHASE_RETURN")) &
(OOQL.CreateProperty("PURCHASE_RETURN.CATEGORY") == OOQL.CreateConstants("39")
& OOQL.CreateProperty("PURCHASE_RETURN.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUE_CLOSE") == OOQL.CreateConstants("0"));
switch (scanType)
{
case "1":
whereNode &= OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID") == OOQL.CreateConstants(ID);
break;
case "2":
whereNode &= OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo));
break;
}
QueryNode node = OOQL.Select(OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO", "source_no"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateProperty("PURCHASE_RETURN.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_RETURN_D.SequenceNumber", "seq"),
OOQL.CreateConstants(0, "doc_line_seq"),
OOQL.CreateConstants(0, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUED_BUSINESS_QTY", "in_out_qty"),
OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no"),
OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),//20170302 modi by shenbao for P001-170302002 误差率统一乘100
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1"), new CaseItem[] { new CaseItem(OOQL.CreateConstants("N"), OOQL.CreateConstants("2")) }, "lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
new object[] {
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
new object[] {
OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(0)
}), //单位转换率分子
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "inventory_unit"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_CODE", "main_organization"),
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type") //20170424 add by wangyq for P001-170420001
)
.From("PURCHASE_RETURN", "PURCHASE_RETURN")
.InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID"))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On(OOQL.CreateProperty("PURCHASE_RETURN.Owner_Org.ROid") == OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On(OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_RETURN.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
.Where(whereNode);
return(node);
}
/// <summary>
/// 根据传入的供应商查询出所有的对账清单数据
/// </summary>
/// <param name="supplierNo">供应商</param>
/// <param name="dateS">对账开始日期</param>
/// <param name="dateE">对账截止日期</param>
/// <param name="siteNo">营运中心</param>
/// <returns></returns>
private QueryNode GetCheckListDetail(string supplierNo, string dateS, string dateE, string siteNo)
{
//如果结束日期为空格，空值，null时默认最大日期
if (Maths.IsEmpty(dateE.ToDate()))
{
dateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
//如果开始日期为空格，空值，null时默认最小日期
if (Maths.IsEmpty(dateS.ToDate()))
{
dateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001
#region //20170508 mark by liwei1 for P001-161209002
//OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "qty"),
//Formulas.IsNull(
// OOQL.CreateProperty("UNIT.UNIT_CODE"),
// OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
//OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "price"),
#endregion
//20170508 add by liwei1 for P001-161209002 ---begin---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"),
Formulas.IsNull(
OOQL.CreateProperty("B_UNIT.UNIT_CODE"),
OOQL.CreateConstants(" ", GeneralDBType.String), "unit_no"),
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(" ", GeneralDBType.String), "valuation_unit_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "valuation_qty"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.DISCOUNTED_PRICE", "price"),
//20170508 add by liwei1 for P001-161209002 ---end---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"),
#region //20170508 mark by liwei1 for P001-161209002
//OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY", "billing_qty"),
//(OOQL.CreateArithmetic(
// OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"),
// OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty")),
//OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC", "billing_amount"),
//OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC")
// + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")),
// OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), ArithmeticOperators.Sub, "not_billing_amount"))
#endregion
//20170508 add by liwei1 for P001-161209002 ---begin---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY", "billing_qty"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty"),
Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC"))), "billing_amount"),
OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")),
Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")))), ArithmeticOperators.Sub, "not_billing_amount"))
//20170508 add by liwei1 for P001-161209002 ---end---
.From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")
.InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D")
.On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID")))
#region //20170508 mark by liwei1 for P001-161209002
//.InnerJoin(
// OOQL.Select(
// OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid", "SOURCE2_ID_ROid"),
// OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "DISCOUNTED_PRICE_INTAX"),
// Formulas.Sum(
// OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), "PRICE_QTY"),
// Formulas.Sum(
// OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), "AMT_TC"))
// .From("PAYABLE_DOC.PAYABLE_DOC_D", "PAYABLE_DOC_D")
// .InnerJoin("PAYABLE_DOC", "PAYABLE_DOC")
// .On((OOQL.CreateProperty("PAYABLE_DOC.PAYABLE_DOC_ID") ==OOQL.CreateProperty("PAYABLE_DOC_D.PAYABLE_DOC_ID")))
// .Where((OOQL.CreateProperty("PAYABLE_DOC.ApproveStatus") ==OOQL.CreateConstants("Y"))
// &((OOQL.CreateProperty("PAYABLE_DOC.BOOKKEEPING_DATE") >=OOQL.CreateConstants(dateS.ToDate()))
// &(OOQL.CreateProperty("PAYABLE_DOC.DOC_DATE") <=OOQL.CreateConstants(dateE.ToDate()))))
// .GroupBy(
// OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid"),
// OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX")), "PAYABLE_DOC_D")
//.On((OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID_ROid") ==OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID")))
#endregion
//20170508 add by liwei1 for P001-161209002 ---gebin---
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID")))
.InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID")))
.LeftJoin("UNIT", "B_UNIT")
.On((OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_UNIT_ID")))
//20170508 add by liwei1 for P001-161209002 ---end---
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID")))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID")));
#region 20171010 add by zhangcn for B001-171010004
QueryConditionGroup conditionGroup2 =
(OOQL.AuthFilter("PURCHASE_ISSUE", "PURCHASE_ISSUE"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") == OOQL.CreateConstants("Y"));
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup2 &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
QueryNode unioNode =
OOQL.Select(OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ISSUE.DOC_NO", "stock_in_no"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001
//OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY", "qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"qty"),
Formulas.IsNull(OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY", "valuation_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"valuation_qty"),
OOQL.CreateProperty("PURCHASE_RETURN_D.DISCOUNTED_PRICE", "price"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"amount"),
//OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"),
// ArithmeticOperators.Plus,
// "tax_amount"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"tax_amount"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY","billing_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"billing_qty"),
//OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
// ArithmeticOperators.Sub,
// "not_billing_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY") - OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"not_billing_qty"),
//Formulas.Case(null,
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
// OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))),
// "billing_amount"),
OOQL.CreateArithmetic((Formulas.Case(null,
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))
)
),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"billing_amount"),
//OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")),
// Formulas.Case(null,
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
// OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))),
// ArithmeticOperators.Sub,
// "not_billing_amount"))
OOQL.CreateArithmetic((OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")),
Formulas.Case(null,
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))),
ArithmeticOperators.Sub)),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"not_billing_amount"))
.From("PURCHASE_ISSUE", "PURCHASE_ISSUE")
.InnerJoin("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D")
.On(OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID"))
.InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D")
.On(OOQL.CreateProperty("PURCHASE_ISSUE_D.SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID"))
.InnerJoin("PURCHASE_RETURN", "PURCHASE_RETURN")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On(OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID"))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_UNIT_ID"))
.LeftJoin("UNIT", "B_UNIT")
.On(OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_UNIT_ID"))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.SUPPLIER_ID"))
.Where(conditionGroup2);
#endregion
//初始Where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT"))
& ((OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) //20170508 add by liwei1 for P001-161209002
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) //20170508 add by liwei1 for P001-161209002
);
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
return(joinOnNode.Where(conditionGroup).Union(unioNode));//20171010 modi by zhangcn for B001-171010004 【增加.Union(unioNode)】
}
private QueryNode GetSalesReturnQueryNode(string programJobNo, string scanType, string status, string[] docNos, string id, string siteNo)
{
QueryConditionGroup conditionGroup = OOQL.CreateProperty("SALES_RETURN.CATEGORY") == OOQL.CreateConstants("26") &
OOQL.CreateProperty("SALES_RETURN.ApproveStatus") == OOQL.CreateConstants("Y") &
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo);
if (scanType == "1")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("SALES_RETURN_D.SALES_RETURN_D_ID") == OOQL.CreateConstants(id));
}
else if (scanType == "2")
{
conditionGroup &= (OOQL.CreateProperty("SALES_RETURN.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNos)));
}
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"),
OOQL.CreateProperty("SALES_RETURN.DOC_NO", "source_no"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateProperty("SALES_RETURN.DOC_DATE", "create_date"),
OOQL.CreateProperty("SALES_RETURN_D.SequenceNumber", "seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("SALES_RETURN_D.RECEIPTED_BUSINESS_QTY", "in_out_qty"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
OOQL.CreateArithmetic(Formulas.IsNull(OOQL.CreateProperty("ITEM_SALES.GENERAL_DEL_OVERRUN_RATE"), OOQL.CreateConstants(0m))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),//20170302 modi by shenbao for P001-170302002 误差率统一乘100
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
// modi by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// modi by 08628 for P001-171023001 e
)
.From("SALES_RETURN", "SALES_RETURN")
.InnerJoin("SALES_RETURN.SALES_RETURN_D", "SALES_RETURN_D")
.On(OOQL.CreateProperty("SALES_RETURN_D.SALES_RETURN_ID") == OOQL.CreateProperty("SALES_RETURN.SALES_RETURN_ID"))
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On(OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("SALES_RETURN_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("SALES_RETURN_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("SALES_RETURN_D.BUSINESS_UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID") &
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID"))
.LeftJoin("ITEM_SALES", "ITEM_SALES")
.On(OOQL.CreateProperty("ITEM_SALES.ITEM_ID") == OOQL.CreateProperty("SALES_RETURN_D.ITEM_ID") &
OOQL.CreateProperty("ITEM_SALES.Owner_Org.ROid") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("STOCK_UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"))
// modi by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// modi by 08628 for P001-171023001 e
.Where(conditionGroup);
return(queryNode);
}
/// <summary>
/// 查询到货单查询信息
/// </summary>
/// <param name="programJobNo">作业编号</param>
/// <param name="id">主键</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="scanType">扫描类型1.箱条码 2.单据条码 3.料号</param>
/// <param name="docNo">单据编号</param>
/// <param name="status"></param>
/// <returns></returns>
private QueryNode GetPurchaseArrivalQueryNode(string programJobNo, string id, string siteNo, string scanType, string[] docNo, string status) //20161216 add by liwei1 for P001-161215001
//private QueryNode GetPurchaseArrivalQueryNode(string programJobNo, string id, string siteNo, string scanType, string docNo, string status) {//20161216 mark by liwei1 for P001-161215001
{
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")) &
((OOQL.CreateProperty("PURCHASE_ARRIVAL.CATEGORY") == OOQL.CreateConstants("36")) &
(OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPT_CLOSE") == OOQL.CreateConstants("0")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)));
if (scanType == "1")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") ==
Formulas.Cast(OOQL.CreateConstants(id), GeneralDBType.Guid));
}
else if (scanType == "2")
{
//单据条码
conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));//20161216 add by liwei1 for P001-161215001
//conditionGroup &= (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO") == OOQL.CreateConstants(docNo));//20161216 mark by liwei1 for P001-161215001
}
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO", "source_no"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPTED_BUSINESS_QTY", "in_out_qty"),
Formulas.Cast(OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_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")//20170903 mark by liwei1 for B001-170904001
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "object_no") //20170903 add by liwei1 for B001-170904001
//20161226 add by liwei1 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_PURCHASE.RECEIPT_OVER_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("PURCHASE_ARRIVAL_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
//20161226 add by liwei1 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
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// add by 08628 for P001-171023001 e
)
.From("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.On(OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID"))
.InnerJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On(OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.Owner_Org.ROid"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID"))
//20161226 add by liwei1 for P001-161215001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) //20170117 add by liwei1 for B001-170117019
//& OOQL.CreateProperty("PURCHASE_ARRIVAL.PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))//20170117 mark by liwei1 for B001-170117019
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ARRIVAL.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161226 add by liwei1 for P001-161215001 ===end===
.LeftJoin("SUPPLIER", "SUPPLIER") //20170903 add by liwei1 for B001-170904001
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.SUPPLIER_ID"))) //20170903 add by liwei1 for B001-170904001
// add by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where(conditionGroup);
return(queryNode);
}
public DependencyObjectCollection QuerySource(string counting_type, string counting_no, string site_no)
{
//20170209 modi by wangyq for B001-170206023 去除仓库 string warehouse_no,
QueryNode node = OOQL.Select(OOQL.CreateConstants("99", "enterprise_no") //企业编号
, OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点
, OOQL.CreateProperty("PLANT.PLANT_ID", "plant_id") //工厂
, OOQL.CreateProperty("COUNTING_PLAN.DOC_NO", "counting_no") //盘点计划编号
, OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号
,
OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION")
, ArithmeticOperators.Plus, "item_name_spec") //品名
,
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no") //产品特征码
,
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("COUNTING_PLAN.BO_ID.RTK", "transaction_type") //交易对象类型
, Formulas.Case(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.RTK"), OOQL.CreateConstants(""), new CaseItem[]
{
new CaseItem(OOQL.CreateConstants("CUSTOMER"), OOQL.CreateProperty("CUSTOMER.CUSTOMER_CODE")),
new CaseItem(OOQL.CreateConstants("SUPPLIER"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE")),
new CaseItem(OOQL.CreateConstants("EMPLOYEE"), OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE")),
new CaseItem(OOQL.CreateConstants("OTHER_BO"), OOQL.CreateProperty("OTHER_BO.OTHER_BO_CODE")),
}, "transaction_no") //交易对象编号
, OOQL.CreateProperty("COUNTING_PLAN.BOOK_QTY", "inventory_qty") //库存数量
)
.From("COUNTING_PLAN", "COUNTING_PLAN")
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("COUNTING_PLAN.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE")
.On(OOQL.CreateProperty("COUNTING_PLAN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("COUNTING_PLAN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID"))
.LeftJoin("ITEM_LOT")
.On(OOQL.CreateProperty("COUNTING_PLAN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.LeftJoin("CUSTOMER")
.On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID"))
.LeftJoin("SUPPLIER")
.On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID"))
.LeftJoin("EMPLOYEE")
.On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))
.LeftJoin("OTHER_BO")
.On(OOQL.CreateProperty("COUNTING_PLAN.BO_ID.ROid") == OOQL.CreateProperty("OTHER_BO.OTHER_BO_ID"))
.Where(OOQL.AuthFilter("COUNTING_PLAN", "COUNTING_PLAN")
& (OOQL.CreateProperty("COUNTING_PLAN.DOC_NO") == OOQL.CreateConstants(counting_no)
&
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no) |
OOQL.CreateConstants(site_no) == OOQL.CreateConstants(string.Empty))
//& OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)//20170209 mark by wangyq for B001-170206023 新需求
& OOQL.CreateProperty("COUNTING_PLAN.ApproveStatus") == OOQL.CreateConstants("N")));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
private QueryNode GetSalesListDetail(string supplierNo, string dateS, string dateE, string siteNo)
{
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "supplier_no"),
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "supplier_name"),
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_CONTACT_NAME", "employee_no"),
Formulas.IsNull(
OOQL.CreateProperty("SUPPLIER_CONTACT.TELEPHONE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "supplier_telephone_number"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"),
OOQL.CreateProperty("PLANT.TELEPHONE", "telephone_number"),
OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"), //20171010 add by zhangcn for B001-171010004
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"), //20171010 add by zhangcn for B001-171010004
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PRICE", "price"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"))
.From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")
.InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D")
.On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID")))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID")))
.InnerJoin("SUPPLIER_PURCHASE", "SUPPLIER_PURCHASE")
.On((OOQL.CreateProperty("SUPPLIER_PURCHASE.Owner_Org.ROid") == OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid"))
& (OOQL.CreateProperty("SUPPLIER_PURCHASE.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
.LeftJoin("SUPPLIER_PURCHASE.SUPPLIER_CONTACT", "SUPPLIER_CONTACT")
.On((OOQL.CreateProperty("SUPPLIER_CONTACT.SUPPLIER_BUSINESS_ID") == OOQL.CreateProperty("SUPPLIER_PURCHASE.SUPPLIER_PURCHASE_ID"))
& (OOQL.CreateProperty("SUPPLIER_CONTACT.MIAN_ORDER_CONTACT") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID")))
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID")))
//20171010 add by zhangcn for B001-171010004 ========================begin=========================
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"));
//20171010 add by zhangcn for B001-171010004 ========================end=========================
//如果结束日期为空格，空值，null时默认最大日期
if (Maths.IsEmpty(dateE.ToDate()))
{
dateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
//如果开始日期为空格，空值，null时默认最小日期
if (Maths.IsEmpty(dateS.ToDate()))
{
dateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
//初始Where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT"))
& (OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate()));
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
return(joinOnNode.Where(conditionGroup));
}
/// <summary>
/// 查询寄售订单
/// </summary>
/// <param name="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));
}
private QueryNode GetFILPurchaseArrivalBcodeNode(string programJobNo, string status, string[] docNo)
{
QueryNode node = OOQL.Select(true,
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "source_no"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER.DOC_NO
OOQL.CreateConstants(programJobNo + status, "doc_type"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "create_date"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER.DOC_DATE
OOQL.CreateProperty("FIL_ARRIVAL_D.SequenceNumber", "seq"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER_D.SequenceNumber
OOQL.CreateConstants(0, "doc_line_seq"), //20170331 modi by wangrm for P001-170327001 OLD:PURCHASE_ORDER_SD.SequenceNumber
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
OOQL.CreateConstants(string.Empty, "object_no"), //对象编号
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"),
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"),
OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE", "warehouse_no"),
OOQL.CreateConstants(string.Empty, "storage_spaces_no"),
OOQL.CreateConstants(string.Empty, "lot_no"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.ARRIVED_BUSINESS_QTY", "in_out_qty"),
//20170331 add by wangrm for P001-170327001=======start==========
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "upper_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "upper_seq"),
OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber", "upper_line_seq"),
OOQL.CreateConstants(0, "upper_batch_seq"),
//20170331 add by wangrm for P001-170327001=======end============
OOQL.CreateProperty("UNIT.UNIT_CODE", "unit_no"),
OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_RATE"), OOQL.CreateConstants(100, GeneralDBType.Decimal), ArithmeticOperators.Mulit, "allow_error_rate"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.Case(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL"), OOQL.CreateConstants("1"),
new CaseItem[] { new CaseItem(OOQL.CreateConstants("N"), OOQL.CreateConstants("2")) }, "lot_control_type"),
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator",
new object[] {
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular",
new object[] {
OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID"),
OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"),
OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID"),
OOQL.CreateConstants(0)
}), //单位转换率分子
OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "inventory_unit"),
OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_CODE", "main_organization"),
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
// modi by 08628 for P001-171023001 b
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no"),
OOQL.CreateConstants(string.Empty, "first_in_first_out_control")
// modi by 08628 for P001-171023001 e
)
.From("FIL_ARRIVAL", "FIL_ARRIVAL")
.InnerJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.On(OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID"))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On(OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID")
& OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_SE") == OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID")
& OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_SE_SE") == OOQL.CreateProperty("PURCHASE_ORDER_SD.SequenceNumber"))
.LeftJoin("SUPPLY_CENTER", "SUPPLY_CENTER")
.On(OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") == OOQL.CreateProperty("SUPPLY_CENTER.SUPPLY_CENTER_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ORDER_SD.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("ITEM_PURCHASE", "ITEM_PURCHASE")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")
& OOQL.CreateProperty("PURCHASE_ORDER.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
// modi by 08628 for P001-171023001 b
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// modi by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL"))
& (OOQL.CreateProperty("FIL_ARRIVAL_D.STATUS") == OOQL.CreateConstants("N"))
& OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));
return(node);
}
/// <summary>
/// 获取返回值单头与receipt_list的信息
/// </summary>
/// <param name="barcodeNo"></param>
/// <returns></returns>
private DependencyObjectCollection GetPurchaseArrival(string barcodeNo)
{
JoinOnNode joinOnNode =
OOQL.Select(
Formulas.IsNull(
OOQL.CreateProperty("ITEM_STRI_DEGREE_D.STRICTNESS_DEGREE"),
OOQL.CreateConstants("1", GeneralDBType.String), "STRICTNESS_DEGREE"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_ID", "ITEM_ID"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid", "RECEIVE_Owner_Org_ROid"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.OPERATION_ID", "OPERATION_ID"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"),
OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_no"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "item_feature_name"),
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY"), ArithmeticOperators.Sub, "receipt_qty"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY"), ArithmeticOperators.Sub, "ok_qty"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "unqualified_qty"), //20170801 add by liwei1 for P001-170717001
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "checkdestroy_qty"), //20170801 add by liwei1 for P001-170717001
OOQL.CreateConstants(string.Empty, "result_type"),
OOQL.CreateConstants(string.Empty, "qc_group"),
OOQL.CreateConstants(string.Empty, "qc_degree"),
OOQL.CreateConstants(string.Empty, "qc_level"),
OOQL.CreateConstants(string.Empty, "qc_type"),
OOQL.CreateProperty("FIL_ARRIVAL_D.DOC_NO", "delivery_no"),
OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "supplier_no"),
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "supplier_name"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO", "receipt_no"),
OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_DATE", "receipt_date"))
.From("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM.ITEM_FEATURE.ITEM_FEATURE_ID")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")))
.LeftJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.On((OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID")))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.SUPPLIER_ID") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID")))
.LeftJoin("ITEM_PLANT", "ITEM_PLANT")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")))
.LeftJoin("MO_ROUTING.MO_ROUTING_D", "MO_ROUTING_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK") == OOQL.CreateConstants("MO_ROUTING.MO_ROUTING_D"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.ROid") == OOQL.CreateProperty("MO_ROUTING_D.MO_ROUTING_D_ID")))
.LeftJoin("ITEM_STRI_DEGREE", "ITEM_STRI_DEGREE")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.RECEIVE_Owner_Org.ROid") == OOQL.CreateProperty("ITEM_STRI_DEGREE.Owner_Org.ROid"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL.SUPPLIER_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE.SOURCE_ID.ROid")))
.LeftJoin("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_D", "ITEM_STRI_DEGREE_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_ID"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("ITEM_STRI_DEGREE_D.ITEM_STRI_DEGREE_ID") == OOQL.CreateProperty("ITEM_STRI_DEGREE.ITEM_STRI_DEGREE_ID")));
//共同存在的条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL.CATEGORY") == OOQL.CreateConstants("36"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RECEIPT_CLOSE") == OOQL.CreateConstants("0"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL.DOC_NO") == OOQL.CreateConstants(barcodeNo))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.BUSINESS_QTY") > OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTED_QTY"));
//主查询中增加不一样的条件
QueryConditionGroup conditionGroupUnionOne = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") == OOQL.CreateConstants("3"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS") != OOQL.CreateConstants("1"))
& OOQL.CreateProperty("MO_ROUTING_D.INSPECT_MODE") == OOQL.CreateConstants("2");
//第一个Union中增加不一样的条件
QueryConditionGroup conditionGroupUnionTwo = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") != OOQL.CreateConstants("3"))
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS") != OOQL.CreateConstants("1"))
& OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE") == OOQL.CreateConstants("2")
& (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK") != OOQL.CreateConstants("WIP"));
//第二个Union中增加不一样的条件
QueryConditionGroup conditionGroupUnionThree = conditionGroup & (OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_TYPE") == OOQL.CreateConstants("3"))
& ((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.INSPECTION_STATUS")) != OOQL.CreateConstants("1"))
& (OOQL.CreateProperty("ITEM_PLANT.INSPECT_MODE")) == OOQL.CreateConstants("2")
& ((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.REFERENCE_SOURCE_ID.RTK")) == OOQL.CreateConstants("WIP"));
//组合node
QueryNode queryNode = joinOnNode.Where(conditionGroupUnionOne)
.Union(joinOnNode.Where(conditionGroupUnionTwo), true)
.Union(joinOnNode.Where(conditionGroupUnionThree), true);
return(GetService <IQueryService>().ExecuteDependencyObject(queryNode));
}
