/// <summary>
/// 根据传入的不良原因编号，获取相应的不良原因信息
/// </summary>
/// <param name="reasonCode">入参理由码</param>
/// <param name="siteNo"></param>
/// <returns></returns>
private DependencyObjectCollection GetDefectiveReasons(string reasonCode,string siteNo)
{
//拼接where条件
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("DEFECTIVE_REASONS", "DEFECTIVE_REASONS"))
& (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_TYPE").In(
OOQL.CreateConstants("1"),
OOQL.CreateConstants("2"),
OOQL.CreateConstants("6")))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo));
//IF 【入参理由码reason_code】不为空 THEN
if (!Maths.IsEmpty(reasonCode)){
conditionGroup &= (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE") ==OOQL.CreateConstants(reasonCode));
}
//组合node
QueryNode node =
OOQL.Select(
OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE", "reason_code"),
OOQL.CreateProperty("DEFECTIVE_REASONS.DESCRIPTION", "reason_code_name"))
.From("DEFECTIVE_REASONS", "DEFECTIVE_REASONS")
.InnerJoin("PLANT","PLANT")
.On(OOQL.CreateProperty("DEFECTIVE_REASONS.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.Where(conditionGroup);
//查询数据并返回
return GetService<IQueryService>().ExecuteDependencyObject(node);
}
public QueryNode GetFILPurchaseArrivalListNode(string programJobNo, DependencyObjectCollection condition)
{
QueryConditionGroup group = (OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL"))
& (OOQL.CreateProperty("FIL_ARRIVAL.STATUS") == OOQL.CreateConstants("2"));
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("FIL_ARRIVAL.DOC_NO", "FIL_ARRIVAL.DOC_DATE", new string[] { "1", "2", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
QueryNode node = OOQL.Select(true,
OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "doc_no"),
OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "create_date"),
OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_FULL_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
OOQL.CreateConstants(string.Empty, "employee_name"))
.From("FIL_ARRIVAL", "FIL_ARRIVAL")
.LeftJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID"))
.LeftJoin("PURCHASE_ORDER", "PURCHASE_ORDER")
.On(OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO") == OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_ID") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID"))
.Where(group);
return(node);
}
/// <summary>
/// 获取工单信息查询信息
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetMOQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true, OOQL.CreateProperty("MO.DOC_NO", "doc_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
Formulas.Case(null,
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("MO.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")),
OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))),
"customer_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("MO", "MO")
.LeftJoin("MO.MO_D", "MO_D")
.On(OOQL.CreateProperty("MO_D.MO_ID") == OOQL.CreateProperty("MO.MO_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("MO.Owner_Org.ROid"))
.LeftJoin("WORK_CENTER", "WORK_CENTER")
.On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("MO.SOURCE_ID.ROid"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("MO.Owner_Emp"))
.LeftJoin("DOC", "DOC")
.On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("MO.DOC_ID"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("MO.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where(//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
//(OOQL.AuthFilter("MO", "MO")) & (Where(//20170328 mark by wangyq for P001-170327001
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("DOC.CATEGORY").In(OOQL.CreateConstants("51"),
OOQL.CreateConstants("52"),
OOQL.CreateConstants("53"),
OOQL.CreateConstants("54"))) &
(OOQL.CreateProperty("MO.LOT_MO_FLAG") == OOQL.CreateConstants(false)) &
(OOQL.CreateProperty("MO.STATUS").In(OOQL.CreateConstants("1"),
OOQL.CreateConstants("2"),
OOQL.CreateConstants("3"))) &
((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("MO_D.REQUIRED_QTY") > OOQL.CreateProperty("MO_D.ISSUED_QTY")) |
(OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("MO_D.ISSUED_QTY") > OOQL.CreateConstants(0)));
//));//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO.DOC_NO", "MO.DOC_DATE", new string[] { "1", "2", "3", "4", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("MO", "MO") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
//20170328 add by wangyq for P001-170327001
/// <summary>
///
/// </summary>
/// <param name="group">原来查询的条件,必须传入，避免拼接半天太麻烦</param>
/// <param name="condition"></param>
/// <returns></returns>
public static QueryConditionGroup CreateNewConditionByParameter(QueryConditionGroup group, DependencyObjectCollection condition, ConditionPropertyNameEntity conPropertyEntity)
{
if (condition.ItemDependencyObjectType.Properties.Contains("seq") &&
condition.ItemDependencyObjectType.Properties.Contains("value")) //都没有设置的时候会没有传入这个字段
{
foreach (DependencyObject conObj in condition)
{
string leftName = string.Empty;
bool isEmptyValue;
if (conObj["seq"].ToStringExtension() == "2") //日期类型要转换为日期后判断
{
isEmptyValue = Maths.IsEmpty(conObj["value"].ToDate());
}
else
{
isEmptyValue = Maths.IsEmpty(conObj["value"]);
}
if (!isEmptyValue && conPropertyEntity.ContainProperty.Length > 0 && conPropertyEntity.ContainProperty.Contains(conObj["seq"].ToStringExtension()))
{
switch (conObj["seq"].ToStringExtension())
{
case "1": //单号
leftName = conPropertyEntity.DocNoName;
break;
case "2": //日期
group &= OOQL.CreateProperty(conPropertyEntity.DocDateName) >= OOQL.CreateConstants(conObj["value"].ToDate(), GeneralDBType.Date);
break;
case "3": //人员
leftName = conPropertyEntity.EmployeeName;
break;
case "4": //部门
leftName = conPropertyEntity.AdminUnitName;
break;
case "5": //供应商
leftName = conPropertyEntity.SupplierName;
break;
case "6": //客户
leftName = conPropertyEntity.CustomerName;
break;
default:
break;
}
if (!string.IsNullOrEmpty(leftName))
{
group &= OOQL.CreateProperty(leftName).Like(OOQL.CreateConstants("%" + conObj["value"].ToString() + "%", GeneralDBType.String));
}
}
}
}
return(group);
}
/// <summary>
/// 获取销货单QueryNode
/// </summary>
/// <param name="siteNo">工厂</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetSalesDeliveryQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode = //20170328 modi by wangyq for P001-170327001 old: return
OOQL.Select(true, //20170104 add by wangyq for P001-161215001
OOQL.CreateProperty("SALES_DELIVERY.DOC_NO", "doc_no"),
OOQL.CreateProperty("SALES_DELIVERY.DOC_DATE", "create_date"),
OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty, GeneralDBType.String), "employee_name"),
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"))
.From("SALES_DELIVERY", "SALES_DELIVERY")
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Org.ROid")))
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_DELIVERY.PLANT_ID")))
.LeftJoin("CUSTOMER", "CUSTOMER")
.On((OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_DELIVERY.CUSTOMER_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_DELIVERY.Owner_Emp")))
//20170104 add by wangyq for P001-161215001 ================begin=====================
.LeftJoin("SALES_DELIVERY.SALES_DELIVERY_D", "SALES_DELIVERY_D")
.On(OOQL.CreateProperty("SALES_DELIVERY.SALES_DELIVERY_ID") == OOQL.CreateProperty("SALES_DELIVERY_D.SALES_DELIVERY_ID"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("SALES_DELIVERY.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//20170104 add by wangyq for P001-161215001 ================end=====================
//.Where( //20170328 mark by wangyq for P001-170327001
//20170104 add by wangyq for P001-161215001 ================begin=====================
//OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") &//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
(OOQL.CreateProperty("SALES_DELIVERY_D.ISSUED") == OOQL.CreateConstants("0", GeneralDBType.String) &
//20170104 add by wangyq for P001-161215001 ================end=====================
(OOQL.CreateProperty("SALES_DELIVERY.ApproveStatus") == OOQL.CreateConstants("Y", GeneralDBType.String))
& (OOQL.CreateProperty("SALES_DELIVERY.CATEGORY") == OOQL.CreateConstants("24", GeneralDBType.String))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo, GeneralDBType.String)));
//); //20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_DELIVERY.DOC_NO", "SALES_DELIVERY.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_DELIVERY", "SALES_DELIVERY") & (group));
return(queryNode);
//20170328 add by wangyq for P001-170327001 ================end==============
}
/// <summary>
/// 拼接查询条件
/// </summary>
/// <param name="siteNo"></param>
/// <param name="condition"></param>
/// <returns></returns>
private QueryConditionGroup CreateCondition(string siteNo, DependencyObjectCollection condition)
{
QueryConditionGroup group = OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("SALES_ISSUE.ApproveStatus") == OOQL.CreateConstants("N", GeneralDBType.String)
& OOQL.CreateProperty("SALES_ISSUE_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1", GeneralDBType.String);
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_ISSUE.DOC_NO", "SALES_ISSUE.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
return(group);
}
private QueryNode GetIssueReceiptReqListNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode node = OOQL.Select(
OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_NO", "doc_no"),
OOQL.CreateProperty("ISSUE_RECEIPT_REQ.DOC_DATE", "create_date"),
Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")
, OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))
}), OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty), "employee_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"))
.From("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")))
.LeftJoin("WORK_CENTER")
.On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.ROid") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID")))
.LeftJoin("SUPPLIER")
.On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.SOURCE_ID.ROid") == OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where((OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ")) &//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group =//20170328 add by wangyq for P001-170327001
(OOQL.CreateProperty("ISSUE_RECEIPT_REQ.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("ISSUE_RECEIPT_REQ.CLOSE") == OOQL.CreateConstants("0", GeneralDBType.String)//20170104 add by wangyq for P001-161215001
);
//);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("ISSUE_RECEIPT_REQ.DOC_NO", "ISSUE_RECEIPT_REQ.DOC_DATE", new string[] { "1", "2", "3", "4", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
node = ((JoinOnNode)node).Where(OOQL.AuthFilter("ISSUE_RECEIPT_REQ", "ISSUE_RECEIPT_REQ") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(node);
}
/// <summary>
/// 获取库存交易单查询信息
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetTransactionDocQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSACTION_DOC", "TRANSACTION_DOC")) &
(((OOQL.CreateProperty("TRANSACTION_DOC.CATEGORY") == OOQL.CreateConstants("11")) &
(OOQL.CreateProperty("TRANSACTION_DOC.ApproveStatus") == OOQL.CreateConstants("N")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))));
if (programJobNo == "11")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("TRANSACTION_DOC.STOCK_ACTION") == OOQL.CreateConstants(-1));
}
else if (programJobNo == "12")
{
//单据条码
conditionGroup &= (OOQL.CreateProperty("TRANSACTION_DOC.STOCK_ACTION") == OOQL.CreateConstants(1));
}
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO", "doc_no"),
OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE", "create_date"),
Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("TRANSACTION_DOC", "TRANSACTION_DOC")
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid"))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Dept"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("TRANSACTION_DOC.Owner_Emp"));
//.Where(conditionGroup);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSACTION_DOC.DOC_NO", "TRANSACTION_DOC.DOC_DATE", new string[] { "1", "2", "3", "4" });
conditionGroup = UtilsClass.CreateNewConditionByParameter(conditionGroup, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(conditionGroup);
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
/// <summary>
/// 获取寄售订单数据
/// </summary>
/// <param name="siteNo">工厂</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private DependencyObjectCollection GetSalesOrder(string siteNo, string programJobNo, DependencyObjectCollection condition)
{
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO", "doc_no"),
OOQL.CreateProperty("SALES_ORDER_DOC.DOC_DATE", "create_date"),
Formulas.IsNull(
OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME"),
OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty), "employee_name"),
Formulas.IsNull(
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE"),
OOQL.CreateConstants(string.Empty), "main_organization"))
.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("PLANT", "PLANT")
.On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PARTNER_ID.ROid")))
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On((OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Org.ROid")))
.LeftJoin("CUSTOMER", "CUSTOMER")
.On((OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.CUSTOMER_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Emp")))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On((OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("SALES_ORDER_DOC.Owner_Dept")));
QueryConditionGroup group =
((OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y"))
& (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))
& (OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B"))
& (OOQL.CreateProperty("SALES_ORDER_DOC.CLOSE") == OOQL.CreateConstants("0")));
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_ORDER_DOC.DOC_NO", "SALES_ORDER_DOC.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC") & (group));
return(GetService <IQueryService>().ExecuteDependencyObject(queryNode));
}
public QueryNode GetMOListNode(string siteNo, string programJobNo
, DependencyObjectCollection condition)
{
QueryNode node = OOQL.Select(OOQL.CreateProperty("MO.DOC_NO", "doc_no"),
OOQL.CreateProperty("MO.DOC_DATE", "create_date"),
OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization")
)
.From("MO", "MO")
.InnerJoin("PLANT")
.On(OOQL.CreateProperty("MO.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("ITEM")
.On(OOQL.CreateProperty("MO.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("MO.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.LeftJoin("UNIT")
.On(OOQL.CreateProperty("MO.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID"))
.LeftJoin("WORK_CENTER")
.On(OOQL.CreateProperty("MO.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")
& OOQL.CreateProperty("MO.SOURCE_ID.ROid") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID"))
.LeftJoin("ADMIN_UNIT")
.On(OOQL.CreateProperty("MO.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"))
.LeftJoin("EMPLOYEE")
.On(OOQL.CreateProperty("MO.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"));
QueryConditionGroup group =
(OOQL.CreateProperty("MO.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("MO.STATUS").NotIn(OOQL.CreateConstants("Y", GeneralDBType.String), OOQL.CreateConstants("y", GeneralDBType.String))
& OOQL.CreateProperty("MO.RECEIPT_REQ_CONTROL") == OOQL.CreateConstants(false)
);
if (condition != null && condition.Count > 0)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("MO.DOC_NO", "MO.DOC_DATE", new string[] { "1", "2", "3", "4" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
node = ((JoinOnNode)node).Where(OOQL.AuthFilter("MO", "MO") & (group));
return(node);
}
/// <summary>
/// 获取销退单通知
/// </summary>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetSalesReturnListQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true,
OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_CODE", "main_organization"),
OOQL.CreateProperty("SALES_RETURN.DOC_NO", "doc_no"),
OOQL.CreateProperty("SALES_RETURN.DOC_DATE", "create_date"),
OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("SALES_RETURN", "SALES_RETURN")
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("SALES_RETURN.PLANT_ID"))
.InnerJoin("SALES_CENTER", "SALES_CENTER")
.On(OOQL.CreateProperty("SALES_CENTER.SALES_CENTER_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Org.ROid"))
.InnerJoin("CUSTOMER", "CUSTOMER")
.On(OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID") == OOQL.CreateProperty("SALES_RETURN.CUSTOMER_ID"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("SALES_RETURN.Owner_Emp"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("SALES_RETURN.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where(//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = //20170328 add by wangyq for P001-170327001
OOQL.CreateProperty("SALES_RETURN.CATEGORY") == OOQL.CreateConstants("26") &
OOQL.CreateProperty("SALES_RETURN.ApproveStatus") == OOQL.CreateConstants("Y") &
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) &
OOQL.CreateProperty("SALES_RETURN.RECEIPTED_STATUS").In(OOQL.CreateConstants("1"), OOQL.CreateConstants("2"));
//);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("SALES_RETURN.DOC_NO", "SALES_RETURN.DOC_DATE", new string[] { "1", "2", "3", "4", "6" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("SALES_RETURN", "SALES_RETURN") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
/// <summary>
/// //7.1定义单据类型
/// </summary>
/// <param name="recommended_operations"></param>
/// <param name="siteNo"></param>
private DependencyObjectCollection ValidateDocSet(string stockAction, string siteNo)
{
QueryConditionGroup group = OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) &
OOQL.CreateProperty("PARA_DOC_FIL.CATEGORY") == OOQL.CreateConstants("11");
if (!string.IsNullOrEmpty(stockAction))
{
group &= OOQL.CreateProperty("PARA_DOC_FIL.STOCK_ACTION") == OOQL.CreateConstants(stockAction);
}
QueryNode node = OOQL.Select(OOQL.CreateProperty("DOC.DOC_ID"), OOQL.CreateProperty("DOC.SEQUENCE_DIGIT"))
.From("PARA_DOC_FIL", "PARA_DOC_FIL")
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PARA_DOC_FIL.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.InnerJoin("DOC", "DOC")
.On(OOQL.CreateProperty("PARA_DOC_FIL.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID"))
.Where(OOQL.AuthFilter("PARA_DOC_FIL", "PARA_DOC_FIL") & (group));
return(this.GetService <IQueryService>().ExecuteDependencyObject(node));
}
private QueryNode GetTransferReqListNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode node = OOQL.Select(true,//20170104 add by wangyq for P001-161215001
OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO", "doc_no"),
OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_DATE", "create_date"),
Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"),
OOQL.CreateConstants(string.Empty), "employee_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"))
.From("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")
.InnerJoin("PLANT", "PLANT")
.On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")))
.LeftJoin("ADMIN_UNIT")
.On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On((OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")))
//20170104 add by wangyq for P001-161215001 ================begin=====================
.LeftJoin("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D", "TRANSFER_REQUISITION_D")
.On(OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANSFER_REQUISITION_ID"));
//20170104 add by wangyq for P001-161215001 ================end=====================
//.Where((OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")) & //20170328 mark by wangyq for P001-170327001
QueryConditionGroup group =//20170328 add by wangyq for P001-170327001
(OOQL.CreateProperty("TRANSFER_REQUISITION.ApproveStatus") == OOQL.CreateConstants("Y")
& OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)
& OOQL.CreateProperty("TRANSFER_REQUISITION_D.CLOSE") == OOQL.CreateConstants("0", GeneralDBType.String)//20170104 add by wangyq for P001-161215001
);
//);//20170328 mark by wangyq for P001-170327001
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSFER_REQUISITION.DOC_NO", "TRANSFER_REQUISITION.DOC_DATE", new string[] { "1", "2", "3", "4" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
node = ((JoinOnNode)node).Where(OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(node);
}
/// <summary>
/// 更新批号
/// </summary>
/// <param name="item_no">料件编号</param>
/// <param name="item_feature_no">产品特征</param>
/// <param name="site_no">营运据点</param>
/// <param name="lot_no">批号</param>
/// <param name="lot_description">批号说明</param>
/// <param name="effective_date">生效日期</param>
/// <param name="effective_deadline">有效截止日</param>
/// <param name="remarks">备注</param>
/// <returns></returns>
public void UpdateLot(string item_no, string item_feature_no, string site_no, string lot_no, string lot_description, string effective_date, string effective_deadline, string remarks)
{
QueryNode selectNode = OOQL.Select(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID"))
.From("ITEM_LOT", "ITEM_LOT")
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID"));
QueryConditionGroup conditionGroup = OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateConstants(item_no, GeneralDBType.String)
& OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateConstants(lot_no, GeneralDBType.String);
DateTime dtEffection = effective_date.ToDate();
DateTime dtInEffection = effective_deadline.ToDate();
if (!string.IsNullOrEmpty(item_feature_no))
{
conditionGroup &= OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE") == OOQL.CreateConstants(item_feature_no, GeneralDBType.String);
//20170228 mark by shenbao for B001-170206007 将这里的语句往下放 ===begin ===
//selectNode = ((JoinOnNode)selectNode).InnerJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
// .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID")
// & OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
// .Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup));
//20170228 mark by shenbao for B001-170206007 ===end ===
}
//20170228 add by shenbao for B001-170206007 ===begin ===
selectNode = ((JoinOnNode)selectNode).LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") //20170228 modi by shenbao for B001-170206007 InnerJoin==>LeftJoin
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID")
& OOQL.CreateProperty("ITEM_LOT.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"))
.Where(OOQL.AuthFilter("ITEM_LOT", "ITEM_LOT") & (conditionGroup));
//20170228 add by shenbao for B001-170206007 ===end ===
List <SetItem> updateList = new List <SetItem>();
updateList.Add(new SetItem(OOQL.CreateProperty("LOT_DESCRIPTION"), OOQL.CreateConstants(lot_description, GeneralDBType.String)));
updateList.Add(new SetItem(OOQL.CreateProperty("EFFECTIVE_DATE"), OOQL.CreateConstants(dtEffection, GeneralDBType.Date)));
updateList.Add(new SetItem(OOQL.CreateProperty("INEFFECTIVE_DATE"), OOQL.CreateConstants(dtInEffection, GeneralDBType.Date)));
updateList.Add(new SetItem(OOQL.CreateProperty("REMARK"), OOQL.CreateConstants(remarks, GeneralDBType.String)));
QueryNode node = OOQL.Update("ITEM_LOT").Set(updateList.ToArray())
.From(selectNode, "selectNode")
.Where(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("selectNode.ITEM_LOT_ID"));
this.GetService <IQueryService>().ExecuteNoQueryWithManageProperties(node);
}
/// <summary>
/// 拼接查询语句
/// </summary>
/// <param name="programJobNo"></param>
/// <param name="siteNo"></param>
/// <param name="condition"></param>
/// <returns></returns>
private QueryNode GetSalesIssueListNode(string programJobNo, string siteNo, DependencyObjectCollection condition)
{
QueryConditionGroup group = CreateCondition(siteNo, condition);
return(OOQL.Select(true,
OOQL.CreateProperty("SALES_ISSUE.DOC_NO", "doc_no"),
OOQL.CreateProperty("SALES_ISSUE.DOC_DATE", "create_date"),
OOQL.CreateProperty("CUSTOMER.CUSTOMER_NAME", "customer_name"),
OOQL.CreateConstants(programJobNo, GeneralDBType.String, "program_job_no"),
OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME", "employee_name"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"))
.From("SALES_ISSUE", "SALES_ISSUE")
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("SALES_ISSUE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))
.LeftJoin("CUSTOMER", "CUSTOMER")
.On(OOQL.CreateProperty("SALES_ISSUE.SHIP_TO_CUSTOMER_ID") == OOQL.CreateProperty("CUSTOMER.CUSTOMER_ID"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("SALES_ISSUE.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("SALES_ISSUE.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"))
.LeftJoin("SALES_ISSUE.SALES_ISSUE_D", "SALES_ISSUE_D")
.On(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID") == OOQL.CreateProperty("SALES_ISSUE_D.SALES_ISSUE_ID"))
.Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") & group));
}
/// <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 GetGetTransferReqQueryNode(string programJobNo, string siteNo, string[] docNo, string scanType, string id, string status)
{
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")) &
((OOQL.CreateProperty("TRANSFER_REQUISITION.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("TRANSFER_REQUISITION_D.CLOSE") == OOQL.CreateConstants("0")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
(OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))));
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(
OOQL.CreateConstants("99", "enterprise_no"), //企业编号
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), //营运据点
OOQL.CreateConstants(programJobNo, "source_operation"), //来源作业
OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_NO", "source_no"), //来源单号
OOQL.CreateConstants(docType, "doc_type"), //单据类型
OOQL.CreateProperty("TRANSFER_REQUISITION.DOC_DATE", "create_date"), //单据日期
Formulas.IsNull(OOQL.CreateProperty("TRANSFER_REQUISITION_D.SequenceNumber"), OOQL.CreateConstants(0), "seq"), //单据项次
OOQL.CreateConstants(0, "doc_line_seq"), //单据项序
OOQL.CreateConstants(0, "doc_batch_seq"), //单据分批序
OOQL.CreateConstants(string.Empty, "object_no"), //对象编号
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"), //料件编号
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), //产品特征
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), //产品特征说明
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty),
"outgoing_warehouse_no"), //仓库
OOQL.CreateConstants(string.Empty, "outgoing_storage_spaces_no"), //储位
OOQL.CreateConstants(string.Empty, "lot_no"), //批号
OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_QTY", "doc_qty"), //单据数量
OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANS_OUT_BUSINESS_QTY", "in_out_qty"), //出入数量
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), //单位
OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"), //主营组织
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateConstants(string.Empty, "main_warehouse_no"),
OOQL.CreateConstants(string.Empty, "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("TRANSFER_REQUISITION", "TRANSFER_REQUISITION")
.InnerJoin("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D", "TRANSFER_REQUISITION_D")
.On(OOQL.CreateProperty("TRANSFER_REQUISITION_D.TRANSFER_REQUISITION_ID") ==
OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("TRANSFER_REQUISITION_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("TRANSFER_REQUISITION_D.FROM_WAREHOUSE_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSFER_REQUISITION_D.BUSINESS_UNIT_ID"))
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("TRANSFER_REQUISITION.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") ==
OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("TRANSFER_REQUISITION.TRANSFER_REQUISITION_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("TRANSFER_REQUISITION.TRANSFER_REQUISITION_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(conditionGroup);
return(queryNode);
}
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="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetIssueReceiptQueryNode(string siteNo, string programJobNo
, DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001
)
{
QueryNode queryNode =
OOQL.Select(true, OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "doc_no"),//20170925 modi by wangyq for P001-170717001 添加distinct因为后面关联加了单身
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.Case(null,
OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME"),
OOQL.CreateCaseArray(OOQL.CreateCaseItem((OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.RTK") == OOQL.CreateConstants("WORK_CENTER")),
OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_NAME"))),
"customer_name"),
OOQL.CreateConstants(programJobNo, "program_job_no"),
Formulas.IsNull(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME"), OOQL.CreateConstants(string.Empty), "employee_name"))
.From("ISSUE_RECEIPT", "ISSUE_RECEIPT")
//20170925 add by wangyq for P001-170717001 ================begin==============
.InnerJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D")
.On(OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID"))
//20170925 add by wangyq for P001-170717001 ================end==============
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid"))
.LeftJoin("WORK_CENTER", "WORK_CENTER")
.On(OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid"))
.LeftJoin("SUPPLIER", "SUPPLIER")
.On(OOQL.CreateProperty("SUPPLIER.SUPPLIER_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.SOURCE_ID.ROid"))
.LeftJoin("EMPLOYEE", "EMPLOYEE")
.On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Emp"))
//20170328 add by wangyq for P001-170327001 ================begin==============
.LeftJoin("ADMIN_UNIT", "ADMIN_UNIT")
.On(OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"));
//20170328 add by wangyq for P001-170327001 ================end==============
//.Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) &//20170328 mark by wangyq for P001-170327001
//20170925 modi by wangyq for P001-170717001 =============begin===============
//QueryConditionGroup group =//20170328 add by wangyq for P001-170327001
// (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
// //(OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N")) &//20170925 mark by wangyq for P001-170717001
// ((OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("7") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56")) |
// (OOQL.CreateConstants(programJobNo) == OOQL.CreateConstants("8") & OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57")));
////);//20170328 mark by wangyq for P001-170327001
QueryConditionGroup group = (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N");
switch (programJobNo)
{
case "7-5":
group &= OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1")
& OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56");
break;
case "7":
group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("56");
break;
case "8":
group &= OOQL.CreateProperty("ISSUE_RECEIPT.CATEGORY") == OOQL.CreateConstants("57");
break;
default:
break;
}
//20170925 modi by wangyq for P001-170717001 =============end===============
//20170328 add by wangyq for P001-170327001 ================begin==============
if (condition != null)
{
ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("ISSUE_RECEIPT.DOC_NO", "ISSUE_RECEIPT.DOC_DATE", new string[] { "1", "2", "3", "4", "5" });
group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity);
}
queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT") & (group));
//20170328 add by wangyq for P001-170327001 ================end==============
return(queryNode);
}
private QueryNode 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 GetTransferDocQueryNode(string programJobNo, string siteNo, string[] docNo, string scanType, string id, string status) //20161216 add by liwei1 for P001-161215001
//private QueryNode GetTransferDocQueryNode(string programJobNo, string siteNo, string docNo, string scanType, string id, string status) {//20161216 mark by liwei1 for P001-161215001
{
QueryConditionGroup conditionGroup = (OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC")) &
((OOQL.CreateProperty("TRANSFER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) &
(OOQL.CreateProperty("TRANSFER_DOC_D.CLOSE") == OOQL.CreateConstants("0")) &
(OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)));
if (scanType == "1")
{
//箱条码
conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_D_ID") ==
Formulas.Cast(OOQL.CreateConstants(id), GeneralDBType.Guid));
}
else if (scanType == "2")
{
//单据条码
conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)));//20161216 add by liwei1 for P001-161215001
//conditionGroup &= (OOQL.CreateProperty("TRANSFER_DOC.DOC_NO") == OOQL.CreateConstants(docNo));//20161216 mark by liwei1 for P001-161215001
}
string docType = programJobNo + status;
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("TRANSFER_DOC.DOC_NO", "source_no"),
OOQL.CreateProperty("TRANSFER_DOC.DOC_DATE", "create_date"),
OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_QTY", "doc_qty"),
OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFERED_BUSINESS_QTY", "in_out_qty"),
Formulas.Cast(OOQL.CreateProperty("TRANSFER_DOC_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty),
"outgoing_warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty),
"outgoing_storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_line_seq"),
OOQL.CreateConstants(0m, GeneralDBType.Decimal, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
//20161216 add by shenbao FOR P001-161215001 ===begin===
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty), "item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[] {
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
OOQL.CreateConstants(0, "allow_error_rate"), //允许误差率
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[] { OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(1) }), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[] { OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID")
, OOQL.CreateConstants(0) }), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), //库存单位
//20161216 add by shenbao FOR P001-161215001 ===end===
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"), //20170424 add by wangyq for P001-170420001
OOQL.CreateConstants(string.Empty, "first_in_first_out_control"),
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no")
)
.From("TRANSFER_DOC", "TRANSFER_DOC")
.InnerJoin("TRANSFER_DOC.TRANSFER_DOC_D", "TRANSFER_DOC_D")
.On(OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_ID") ==
OOQL.CreateProperty("TRANSFER_DOC.TRANSFER_DOC_ID"))
.InnerJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid"))
.InnerJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("TRANSFER_DOC_D.FROM_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.FROM_BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("TRANSFER_DOC_D.BUSINESS_UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
& OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===end===
// add by 08628 for P001-171023001 b
.LeftJoin("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);
}
/// <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 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===
);
}
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="docNo">单据编号</param>
/// <param name="siteNo">工厂编号</param>
/// <param name="programJobNo">作业编号</param>
/// <returns></returns>
private QueryNode GetIssueReceiptQueryNode(string[] docNo, string siteNo, string programJobNo, string status)
{
//20161216 add by liwei1 for P001-161215001
//private QueryNode GetIssueReceiptQueryNode(string docNo, string siteNo, string programJobNo, string status) {//20161216 mark by liwei1 for P001-161215001
string docType = programJobNo + status;
//20170925 add by wangyq for P001-170717001 =============begin===============
QueryConditionGroup group =
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo)) &
OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo);
if (programJobNo == "7-5")
{
group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N") &
OOQL.CreateProperty("ISSUE_RECEIPT_D.BC_CHECK_STATUS") == OOQL.CreateConstants("1");
}
else
{
group &= OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N");
}
//20170925 add by wangyq for P001-170717001 =============end===============
QueryNode queryNode =
OOQL.Select(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO", "source_no"),
OOQL.CreateProperty("ISSUE_RECEIPT.DOC_DATE", "create_date"),
Formulas.Cast(OOQL.CreateProperty("ISSUE_RECEIPT_D.SequenceNumber"), GeneralDBType.Decimal, "seq"),
OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_QTY", "doc_qty"),
//20161230 modi by shenbao for B001-161229011 INVENTORY_QTY=>ISSUE_RECEIPT_QTY
Formulas.Cast(OOQL.CreateConstants(0m), GeneralDBType.Decimal, "in_out_qty"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"),
OOQL.CreateProperty("PLANT.PLANT_CODE", "main_organization"),
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateConstants(string.Empty), "item_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"),
OOQL.CreateConstants(string.Empty), "item_feature_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),
OOQL.CreateConstants(string.Empty), "item_feature_name"),
Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty),
"warehouse_no"),
Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty),
"storage_spaces_no"),
Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty),
"lot_no"),
Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"),
OOQL.CreateConstants("99", "enterprise_no"),
OOQL.CreateConstants(programJobNo, "source_operation"),
OOQL.CreateConstants(docType, "doc_type"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_line_seq"),
Formulas.Cast(OOQL.CreateConstants(0), GeneralDBType.Decimal, "doc_batch_seq"),
OOQL.CreateConstants(string.Empty, "object_no"),
//20161216 add by shenbao FOR P001-161215001 ===begin===
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateConstants(string.Empty),
"item_name"), //品名
Formulas.IsNull(OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty),
"item_spec"), //规格
Formulas.IsNull(Formulas.Case(null, OOQL.CreateConstants("1"), new CaseItem[]
{
new CaseItem(OOQL.CreateProperty("ITEM_PLANT.LOT_CONTROL") == OOQL.CreateConstants("N")
, OOQL.CreateConstants("2"))
}), OOQL.CreateConstants(string.Empty), "lot_control_type"), //批号管控方式
OOQL.CreateArithmetic(
Formulas.IsNull(OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE"), OOQL.CreateConstants(0))
, OOQL.CreateConstants(100), ArithmeticOperators.Mulit, "allow_error_rate"),
//允许误差率//20170302 modi by shenbao for P001-170302002 误差率统一乘100
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_denominator", new object[]
{
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")
, OOQL.CreateConstants(1)
}), //单位转换率分母
Formulas.Ext("UNIT_CONVERT_02", "conversion_rate_molecular", new object[]
{
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID")
, OOQL.CreateProperty("ITEM.STOCK_UNIT_ID")
, OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID")
, OOQL.CreateConstants(0)
}), //单位转换率分子
Formulas.IsNull(OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty),
"inventory_unit"), //库存单位
//20161216 add by shenbao FOR P001-161215001 ===end===
OOQL.CreateProperty("UNIT.DICIMAL_DIGIT", "decimal_places"),
//20170424 add by wangyq for P001-170420001
OOQL.CreateConstants("1", GeneralDBType.String, "decimal_places_type"),
//20170424 add by wangyq for P001-170420001
// add by 08628 for P001-171023001 b
Formulas.Case(null,
Formulas.Case(null, Formulas.Case(null, OOQL.CreateConstants("", GeneralDBType.String),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_G.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_G.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I.FIFO_TYPE")))),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
OOQL.CreateProperty("REG_I_F.FIFO_TYPE").IsNotNull(),
OOQL.CreateProperty("REG_I_F.FIFO_TYPE"))), "first_in_first_out_control"),
OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_CODE", "main_warehouse_no"),
OOQL.CreateProperty("MAIN_BIN.BIN_CODE", "main_storage_no")
// add by 08628 for P001-171023001 e
)
.From("ISSUE_RECEIPT", "ISSUE_RECEIPT")
.LeftJoin("ISSUE_RECEIPT.ISSUE_RECEIPT_D", "ISSUE_RECEIPT_D")
.On(OOQL.CreateProperty("ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_ID"))
.LeftJoin("PLANT", "PLANT")
.On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid"))
.LeftJoin("ITEM", "ITEM")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_ID"))
.LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE")
.On(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_FEATURE_ID"))
.LeftJoin("WAREHOUSE", "WAREHOUSE")
.On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "BIN")
.On(OOQL.CreateProperty("BIN.BIN_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.BIN_ID"))
.LeftJoin("ITEM_LOT", "ITEM_LOT")
.On(OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT_D.ITEM_LOT_ID"))
.LeftJoin("UNIT", "UNIT")
.On(OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("ISSUE_RECEIPT_D.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===begin===
.LeftJoin("ITEM_PLANT")
.On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")
&
OOQL.CreateProperty("ISSUE_RECEIPT.Owner_Org.ROid") ==
OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid"))
.LeftJoin("UNIT", "STOCK_UNIT")
.On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"))
//20161216 add by shenbao FOR P001-161215001 ===end===
// add by 08628 for P001-171023001 b
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("ITEM_FEATURE_ID")
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG")
.Where(OOQL.CreateProperty("ITEM_FEATURE_ID").IsNotNull()
&
(OOQL.CreateProperty("ITEM_FEATURE_ID") !=
OOQL.CreateConstants(Maths.GuidDefaultValue()))),
"REG_I_F")
.On((OOQL.CreateProperty("REG_I_F.ITEM_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID"))
&
(OOQL.CreateProperty("REG_I_F.ITEM_FEATURE_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_FEATURE_ID"))
& (OOQL.CreateProperty("REG_I_F.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("ITEM_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("ITEM_ID")
}
, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_I")
.On((OOQL.CreateProperty("REG_I.ITEM_ID") ==
OOQL.CreateProperty("ISSUE_RECEIPT.ISSUE_RECEIPT_D.ITEM_ID"))
& (OOQL.CreateProperty("REG_I.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin(OOQL.Select(OOQL.CreateProperty("FEATURE_GROUP_ID"),
OOQL.CreateProperty("FIFO_TYPE"),
Formulas.RowNumber("SEQ", new OverClause(new[]
{
OOQL.CreateProperty("FEATURE_GROUP_ID"),
}, new[]
{
OOQL.CreateOrderByItem(Formulas.Case(null, OOQL.CreateProperty("CreateDate"),
OOQL.CreateCaseArray(
OOQL.CreateCaseItem(
(OOQL.CreateProperty("MAIN") == OOQL.CreateConstants(1, GeneralDBType.Int32)),
Formulas.Cast(OOQL.CreateConstants("9998-12-31", GeneralDBType.String),
GeneralDBType.Date)))), SortType.Desc)
})))
.From("ITEM_BC_REG"), "REG_G")
.On((OOQL.CreateProperty("REG_G.FEATURE_GROUP_ID") == OOQL.CreateProperty("ITEM.FEATURE_GROUP_ID"))
& (OOQL.CreateProperty("REG_G.SEQ") == OOQL.CreateConstants(1, GeneralDBType.Int32)))
.LeftJoin("WAREHOUSE", "MAIN_WAREHOUSE")
.On(OOQL.CreateProperty("MAIN_WAREHOUSE.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID"))
.LeftJoin("WAREHOUSE.BIN", "MAIN_BIN")
.On(OOQL.CreateProperty("MAIN_BIN.WAREHOUSE_ID") ==
OOQL.CreateProperty("ITEM_PLANT.INBOUND_WAREHOUSE_ID")
& OOQL.CreateProperty("MAIN_BIN.MAIN") == OOQL.CreateConstants(1))
// add by 08628 for P001-171023001 e
.Where((OOQL.AuthFilter("ISSUE_RECEIPT", "ISSUE_RECEIPT")) &
//20170925 modi by wangyq for P001-170717001 =============begin===============
group);
// (OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO").In(OOQL.CreateDyncParameter("DOC_NO", docNo))) &//20161216 add by liwei1 for P001-161215001
////(OOQL.CreateProperty("ISSUE_RECEIPT.DOC_NO") == OOQL.CreateConstants(docNo)) &//20161216 mark by liwei1 for P001-161215001
// (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) &
// (OOQL.CreateProperty("ISSUE_RECEIPT.ApproveStatus") == OOQL.CreateConstants("N")));
//20170925 modi by wangyq for P001-170717001 =============end===============
return(queryNode);
}
/// <summary>
/// 根据传入的供应商查询出退换货清单
/// </summary>
/// <param name="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));
}
/// <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));
}