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="supplierNo">供应商编号</param>
/// <param name="itemNo">料件编号</param>
/// <param name="itemName">品名</param>
/// <param name="itemSpec">规格</param>
/// <param name="siteNo">营运中心</param>
/// <returns></returns>
private QueryNode GetReturnChangeDetail(string supplierNo, string itemNo, string itemName, string itemSpec, string siteNo)
{
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"),
Formulas.IsNull(
OOQL.CreateProperty("ITEM.ITEM_CODE"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "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"), //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("B.QTY", "qty"))
.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")))
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.BUSINESS_UNIT_ID")))
.InnerJoin(
OOQL.Select(
Formulas.Sum(Formulas.IsNull(
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RETURN_BUSINESS_QTY"),
OOQL.CreateConstants(0, GeneralDBType.Int32)) + Formulas.IsNull(
OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"),
OOQL.CreateConstants(0, GeneralDBType.Int32)), "QTY"),
OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID"))
.From("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "A")
.LeftJoin(
OOQL.Select(
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SOURCE_ID.ROid", "SOURCE_ID"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.RETURN_BUSINESS_QTY"))
.From("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") ==
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID")))
.Where((OOQL.CreateProperty("PURCHASE_ARRIVAL.ApproveStatus") ==
OOQL.CreateConstants("Y"))), "PURCHASE_ARRIVAL_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.SOURCE_ID") ==
OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID")))
.LeftJoin(
OOQL.Select(
OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid", "ORDER_SOURCE_ID"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"))
.From("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D")
.InnerJoin("PURCHASE_ISSUE", "PURCHASE_ISSUE")
.On((OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") ==
OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID")))
.Where((OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") ==
OOQL.CreateConstants("Y"))), "PURCHASE_ISSUE_D")
.On((OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID") ==
OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID")))
.GroupBy(
OOQL.CreateProperty("A.PURCHASE_ORDER_SD_ID")), "B")
.On((OOQL.CreateProperty("B.PURCHASE_ORDER_SD_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") ==
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID")))
//20171010 add by zhangcn for B001-171010004 ========================begin=========================
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"));
//20171010 add by zhangcn for B001-171010004 ========================end=========================
//初始Where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_ORDER", "PURCHASE_ORDER"))
& ((OOQL.CreateProperty("PURCHASE_ORDER.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("PURCHASE_ORDER.CLOSE") == OOQL.CreateConstants("0"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)))
& (OOQL.CreateProperty("B.QTY") > OOQL.CreateConstants(0m));
//如果【品号】不为空增加条件
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));
}
//返回组合Node
return(joinOnNode.Where(conditionGroup));
}
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="supplierNo">供应商</param>
/// <param name="dateS">对账开始日期</param>
/// <param name="dateE">对账截止日期</param>
/// <param name="siteNo">营运中心</param>
/// <returns></returns>
private QueryNode GetCheckListDetail(string supplierNo, string dateS, string dateE, string siteNo)
{
//如果结束日期为空格，空值，null时默认最大日期
if (Maths.IsEmpty(dateE.ToDate()))
{
dateE = OrmDataOption.EmptyDateTime1.ToStringExtension();
}
//如果开始日期为空格，空值，null时默认最小日期
if (Maths.IsEmpty(dateS.ToDate()))
{
dateS = OrmDataOption.EmptyDateTime.ToStringExtension();
}
JoinOnNode joinOnNode =
OOQL.Select(
OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_RECEIPT.DOC_NO", "stock_in_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001
#region //20170508 mark by liwei1 for P001-161209002
//OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "qty"),
//Formulas.IsNull(
// OOQL.CreateProperty("UNIT.UNIT_CODE"),
// OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
//OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "price"),
#endregion
//20170508 add by liwei1 for P001-161209002 ---begin---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_QTY", "qty"),
Formulas.IsNull(
OOQL.CreateProperty("B_UNIT.UNIT_CODE"),
OOQL.CreateConstants(" ", GeneralDBType.String), "unit_no"),
Formulas.IsNull(
OOQL.CreateProperty("UNIT.UNIT_CODE"),
OOQL.CreateConstants(" ", GeneralDBType.String), "valuation_unit_no"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY", "valuation_qty"),
OOQL.CreateProperty("PURCHASE_ARRIVAL_D.DISCOUNTED_PRICE", "price"),
//20170508 add by liwei1 for P001-161209002 ---end---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC"), ArithmeticOperators.Plus, "tax_amount"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"),
#region //20170508 mark by liwei1 for P001-161209002
//OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY", "billing_qty"),
//(OOQL.CreateArithmetic(
// OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"),
// OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty")),
//OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC", "billing_amount"),
//OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC")
// + OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")),
// OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), ArithmeticOperators.Sub, "not_billing_amount"))
#endregion
//20170508 add by liwei1 for P001-161209002 ---begin---
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY", "billing_qty"),
OOQL.CreateArithmetic(
OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_QTY"),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_PRICE_QTY"), ArithmeticOperators.Sub, "not_billing_qty"),
Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC"))), "billing_amount"),
OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_RECEIPT_D.AMOUNT_UNINCLUDE_TAX_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.TAX_OC")),
Formulas.Case(null, OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")
+ OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_ARRIVAL.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_RECEIPT_D.SETTLEMENT_AMT_UN_OC")))), ArithmeticOperators.Sub, "not_billing_amount"))
//20170508 add by liwei1 for P001-161209002 ---end---
.From("PURCHASE_RECEIPT", "PURCHASE_RECEIPT")
.InnerJoin("PURCHASE_RECEIPT.PURCHASE_RECEIPT_D", "PURCHASE_RECEIPT_D")
.On((OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.PURCHASE_RECEIPT_ID")))
#region //20170508 mark by liwei1 for P001-161209002
//.InnerJoin(
// OOQL.Select(
// OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid", "SOURCE2_ID_ROid"),
// OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX", "DISCOUNTED_PRICE_INTAX"),
// Formulas.Sum(
// OOQL.CreateProperty("PAYABLE_DOC_D.PRICE_QTY"), "PRICE_QTY"),
// Formulas.Sum(
// OOQL.CreateProperty("PAYABLE_DOC_D.AMT_TC"), "AMT_TC"))
// .From("PAYABLE_DOC.PAYABLE_DOC_D", "PAYABLE_DOC_D")
// .InnerJoin("PAYABLE_DOC", "PAYABLE_DOC")
// .On((OOQL.CreateProperty("PAYABLE_DOC.PAYABLE_DOC_ID") ==OOQL.CreateProperty("PAYABLE_DOC_D.PAYABLE_DOC_ID")))
// .Where((OOQL.CreateProperty("PAYABLE_DOC.ApproveStatus") ==OOQL.CreateConstants("Y"))
// &((OOQL.CreateProperty("PAYABLE_DOC.BOOKKEEPING_DATE") >=OOQL.CreateConstants(dateS.ToDate()))
// &(OOQL.CreateProperty("PAYABLE_DOC.DOC_DATE") <=OOQL.CreateConstants(dateE.ToDate()))))
// .GroupBy(
// OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID.ROid"),
// OOQL.CreateProperty("PAYABLE_DOC_D.DISCOUNTED_PRICE_INTAX")), "PAYABLE_DOC_D")
//.On((OOQL.CreateProperty("PAYABLE_DOC_D.SOURCE2_ID_ROid") ==OOQL.CreateProperty("PURCHASE_RECEIPT_D.PURCHASE_RECEIPT_D_ID")))
#endregion
//20170508 add by liwei1 for P001-161209002 ---gebin---
.InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "PURCHASE_ARRIVAL_D")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.SOURCE_ID")))
.InnerJoin("PURCHASE_ARRIVAL", "PURCHASE_ARRIVAL")
.On((OOQL.CreateProperty("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID")))
.LeftJoin("UNIT", "B_UNIT")
.On((OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.BUSINESS_UNIT_ID")))
//20170508 add by liwei1 for P001-161209002 ---end---
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On((OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ORDER_SOURCE_ID.ROid")))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On((OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID")))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On((OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.Owner_Org.ROid")))
.InnerJoin("ITEM", "ITEM")
.On((OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_ID")))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001
.LeftJoin("UNIT", "UNIT")
.On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT_D.PRICE_UNIT_ID")))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On((OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_RECEIPT.SUPPLIER_ID")));
#region 20171010 add by zhangcn for B001-171010004
QueryConditionGroup conditionGroup2 =
(OOQL.AuthFilter("PURCHASE_ISSUE", "PURCHASE_ISSUE"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate()))
& (OOQL.CreateProperty("PURCHASE_ISSUE.ApproveStatus") == OOQL.CreateConstants("Y"));
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup2 &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
QueryNode unioNode =
OOQL.Select(OOQL.CreateProperty("PURCHASE_ISSUE.TRANSACTION_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ISSUE.DOC_NO", "stock_in_no"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SequenceNumber", "seq"),
OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_DESCRIPTION", "item_name"),
OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_SPECIFICATION", "item_spec"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //20171021 add by liwei1 for B001-171020001
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //20171021 add by liwei1 for B001-171020001
//OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY", "qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"qty"),
Formulas.IsNull(OOQL.CreateProperty("B_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "unit_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String), "valuation_unit_no"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY", "valuation_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"valuation_qty"),
OOQL.CreateProperty("PURCHASE_RETURN_D.DISCOUNTED_PRICE", "price"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC", "amount"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"amount"),
//OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC"),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"),
// ArithmeticOperators.Plus,
// "tax_amount"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"tax_amount"),
OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO", "purchase_no"),
OOQL.CreateProperty("PURCHASE_ORDER_D.SequenceNumber", "purchase_seq"),
//OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY","billing_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"billing_qty"),
//OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY"),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
// ArithmeticOperators.Sub,
// "not_billing_qty"),
OOQL.CreateArithmetic(OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_QTY") - OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_PRICE_QTY"),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"not_billing_qty"),
//Formulas.Case(null,
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
// OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC"))),
// "billing_amount"),
OOQL.CreateArithmetic((Formulas.Case(null,
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))
)
),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"billing_amount"),
//OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")),
// Formulas.Case(null,
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
// OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
// OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))),
// ArithmeticOperators.Sub,
// "not_billing_amount"))
OOQL.CreateArithmetic((OOQL.CreateArithmetic((OOQL.CreateProperty("PURCHASE_ISSUE_D.AMOUNT_UNINCLUDE_TAX_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.TAX_OC")),
Formulas.Case(null,
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC") + OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_TAX_OC"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("PURCHASE_RETURN.TAX_INCLUDED") == OOQL.CreateConstants(true, GeneralDBType.Boolean)),
OOQL.CreateProperty("PURCHASE_ISSUE_D.SETTLEMENT_AMOUNT_OC")))),
ArithmeticOperators.Sub)),
OOQL.CreateConstants(-1),
ArithmeticOperators.Mulit,
"not_billing_amount"))
.From("PURCHASE_ISSUE", "PURCHASE_ISSUE")
.InnerJoin("PURCHASE_ISSUE.PURCHASE_ISSUE_D", "PURCHASE_ISSUE_D")
.On(OOQL.CreateProperty("PURCHASE_ISSUE.PURCHASE_ISSUE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PURCHASE_ISSUE_ID"))
.InnerJoin("PURCHASE_RETURN.PURCHASE_RETURN_D", "PURCHASE_RETURN_D")
.On(OOQL.CreateProperty("PURCHASE_ISSUE_D.SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_D_ID"))
.InnerJoin("PURCHASE_RETURN", "PURCHASE_RETURN")
.On(OOQL.CreateProperty("PURCHASE_RETURN.PURCHASE_RETURN_ID") == OOQL.CreateProperty("PURCHASE_RETURN_D.PURCHASE_RETURN_ID"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D.PURCHASE_ORDER_SD", "PURCHASE_ORDER_SD")
.On(OOQL.CreateProperty("PURCHASE_ISSUE_D.ORDER_SOURCE_ID.ROid") == OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_SD_ID"))
.InnerJoin("PURCHASE_ORDER.PURCHASE_ORDER_D", "PURCHASE_ORDER_D")
.On(OOQL.CreateProperty("PURCHASE_ORDER_SD.PURCHASE_ORDER_D_ID") == OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_D_ID"))
.InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("PURCHASE_ORDER_D.PURCHASE_ORDER_ID") == OOQL.CreateProperty("PURCHASE_ORDER.PURCHASE_ORDER_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20171021 add by liwei1 for B001-171020001
.On((OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.ITEM_FEATURE_ID"))) //20171021 add by liwei1 for B001-171020001
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.PRICE_UNIT_ID"))
.LeftJoin("UNIT", "B_UNIT")
.On(OOQL.CreateProperty("B_UNIT.UNIT_ID") == OOQL.CreateProperty("PURCHASE_ISSUE_D.BUSINESS_UNIT_ID"))
.InnerJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("PURCHASE_ISSUE.SUPPLIER_ID"))
.Where(conditionGroup2);
#endregion
//初始Where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("PURCHASE_RECEIPT", "PURCHASE_RECEIPT"))
& ((OOQL.CreateProperty("PURCHASE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo))
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") >= OOQL.CreateConstants(dateS.ToDate())) //20170508 add by liwei1 for P001-161209002
& (OOQL.CreateProperty("PURCHASE_RECEIPT.TRANSACTION_DATE") <= OOQL.CreateConstants(dateE.ToDate())) //20170508 add by liwei1 for P001-161209002
);
//如果营运中心不为空增加条件
if (!Maths.IsEmpty(siteNo))
{
conditionGroup &= (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
}
return(joinOnNode.Where(conditionGroup).Union(unioNode));//20171010 modi by zhangcn for B001-171010004 【增加.Union(unioNode)】
}
/// <summary>
/// 获取返回值单头与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));
}