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);
}
/// <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));
}
/// <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);
}
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);
}
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);
}
//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>
/// 获取领料出库单查询信息
/// </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);
}
