/// <summary> /// 根据传入的工厂编号,查询工厂的其他相关信息 /// </summary> /// <param name="siteNo">门店编号</param> /// <returns></returns> private QueryNode GetSiteDetail(string siteNo) { QueryNode node = null; if (!Maths.IsEmpty(siteNo)) { node = OOQL.Select( OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"), OOQL.CreateProperty("PLANT.ADDRESS", "address"), OOQL.CreateProperty("PLANT.TELEPHONE", "telephone")) .From("PLANT", "PLANT") .Where(OOQL.AuthFilter("PLANT", "PLANT") & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo))); } else { node = OOQL.Select( OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("PLANT.PLANT_NAME", "site_name"), OOQL.CreateProperty("PLANT.ADDRESS", "address"), OOQL.CreateProperty("PLANT.TELEPHONE", "telephone")) .From("PLANT", "PLANT") .Where(OOQL.AuthFilter("PLANT", "PLANT")); } return(node); }
/// <summary> /// 获取qc_list数据 /// </summary> /// <param name="plantId"></param> /// <returns></returns> private DependencyObjectCollection GetQcList(object plantId) { QueryNode node = OOQL.Select( OOQL.CreateProperty("INSPECTION_PLAN_D.SEQUENCE", "qc_seq"), Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "INSPECTION_ITEM_ID"), Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_CODE"), OOQL.CreateConstants(string.Empty), "test_no"), Formulas.IsNull(OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_NAME"), OOQL.CreateConstants(string.Empty), "test_name"), OOQL.CreateProperty("INSPECTION_PLAN_D.DEFECT_CLASS", "defect_level"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "reject_qty"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "test_qty"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "reason_qty"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "return_qty"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "acceptable_qty"), //20170801 add by liwei1 for P001-170717001 OOQL.CreateConstants(0, GeneralDBType.Decimal, "rejected_qty"), //20170801 add by liwei1 for P001-170717001 OOQL.CreateConstants(0, GeneralDBType.Decimal, "measure_max"), OOQL.CreateConstants(0, GeneralDBType.Decimal, "measure_min"), OOQL.CreateConstants("Y", GeneralDBType.String, "result_type")) .From("INSPECTION_PLAN", "INSPECTION_PLAN") .InnerJoin("INSPECTION_PLAN.INSPECTION_PLAN_D", "INSPECTION_PLAN_D") .On((OOQL.CreateProperty("INSPECTION_PLAN.INSPECTION_PLAN_ID") == OOQL.CreateProperty("INSPECTION_PLAN_D.INSPECTION_PLAN_ID"))) .LeftJoin("INSPECTION_ITEM", "INSPECTION_ITEM") .On((OOQL.CreateProperty("INSPECTION_PLAN_D.INSPECTION_ITEM_ID") == OOQL.CreateProperty("INSPECTION_ITEM.INSPECTION_ITEM_ID"))) .Where(OOQL.AuthFilter("INSPECTION_PLAN", "INSPECTION_PLAN") & (OOQL.CreateProperty("INSPECTION_PLAN.INSPECTION_PLAN_ID") == OOQL.CreateConstants(plantId))); 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="itemID">品号ID集合</param> /// <param name="sitNo">工厂ID</param> /// <returns></returns> private DependencyObjectCollection GetItemForPlant(DependencyObjectCollection itemID, string siteNo, string programJobNo) { List <QueryProperty> properties = GetQueryProperty(siteNo); properties.Add(Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("ITEM_PLANT.RECEIPT_OVERRUN_RATE"), new CaseItem[] { new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("7%")) , OOQL.CreateProperty("ITEM_PLANT.ISSUE_OVERRUN_RATE")), new CaseItem(OOQL.CreateConstants(programJobNo).Like(OOQL.CreateConstants("8%")) , OOQL.CreateProperty("ITEM_PLANT.ISSUE_SHORTAGE_RATE")) }), OOQL.CreateConstants(0m, GeneralDBType.Decimal), "over_deliver_rate")); //超交率 QueryNode node = OOQL.Select( properties ) .From("ITEM", "ITEM") .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_ID")) .LeftJoin("UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")) .LeftJoin("PLANT") .On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(OOQL.AuthFilter("ITEM", "ITEM") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("ITEM.ITEM_ID").In(OOQL.CreateDyncParameter("IDS", itemID.Select(c => c["item_id"]).ToArray()))); return(UtilsClass.ExecuteDependencyObject(this.GetService <IQueryService>(), node)); }
//20170209 add by liwei1 for P001-170203001 ===begin=== /// <summary> /// 查询单据性质ID /// </summary> /// <param name="category">单据性质</param> /// <param name="scanColl">单身数据集</param> /// <returns>单据性质ID</returns> private object QueryDocId(string category, string recommendedOperations, DependencyObjectCollection scanColl) { QueryNode node = null; if (scanColl.Count > 0) { string infoLotNo = scanColl[0]["info_lot_no"].ToStringExtension(); //信息批号 string siteNo = scanColl[0]["site_no"].ToStringExtension(); //工厂 node = OOQL.Select(1, OOQL.CreateProperty("MO.DOC_ID")) .From("MO", "MO") .Where((OOQL.AuthFilter("MO", "MO")) & (OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(infoLotNo))); //根据条件查询满足条件的DOC_ID node = OOQL.Select(1, OOQL.CreateProperty("PARA_DOC_FIL.DOC_ID")) .From("PARA_DOC_FIL", "PARA_DOC_FIL") .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("PARA_DOC_FIL.Owner_Org.ROid"))) .Where((OOQL.AuthFilter("PARA_DOC_FIL", "PARA_DOC_FIL")) & ((OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) & (OOQL.CreateProperty("PARA_DOC_FIL.CATEGORY") == OOQL.CreateConstants(category)) & ((OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == OOQL.CreateConstants(Maths.GuidDefaultValue())) | (OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID") == node)))) .OrderBy( OOQL.CreateOrderByItem( OOQL.CreateProperty("PARA_DOC_FIL.SOURCE_DOC_ID"), SortType.Desc)); } return(GetService <IQueryService>().ExecuteScalar(node)); }
/// <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); }
/// <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); }
//20170629 add by zhangcn for P001-170606002 ===end=== //20170829 add by shenbao for P001-170717001 ===begin=== /// <summary> /// 查询寄售调拨单 /// </summary> /// <param name="site_no"></param> /// <param name="docNos"></param> /// <param name="warseHouseControl"></param> /// <returns></returns> private QueryNode GetSalesOrderDocNode(string site_no, List <ConstantsQueryProperty> docNos, string warseHouseControl) { QueryNode node = OOQL.Select(OOQL.CreateProperty("PLANT.PLANT_CODE"), OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "ITEM_FEATURE_SPECIFICATION"), OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "STOCK_UNIT_CODE"), OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"), OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("STOCK_UNIT.UNIT_ID", "STOCK_UNIT_ID"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"), Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("SALES_ORDER_DOC_SD.BUSINESS_QTY") - OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERED_BUSINESS_QTY")) , OOQL.CreateConstants(0), "SOURCE_QTY") ) .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.SALES_ORDER_DOC_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_D.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_D.SALES_ORDER_DOC_D_ID") == OOQL.CreateProperty("SALES_ORDER_DOC_SD.SALES_ORDER_DOC_D_ID")) .InnerJoin("PLANT") .On(OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("ITEM") .On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("UNIT") .On(OOQL.CreateProperty("SALES_ORDER_DOC_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID") & OOQL.CreateProperty("SALES_ORDER_DOC_SD.DELIVERY_PLANT_ID") == OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid")) .Where((OOQL.AuthFilter("SALES_ORDER_DOC", "SALES_ORDER_DOC")) & (OOQL.CreateProperty("SALES_ORDER_DOC.DOC_NO").In(docNos.ToArray()) & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no) & OOQL.CreateProperty("SALES_ORDER_DOC.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("SALES_ORDER_DOC.CATEGORY") == OOQL.CreateConstants("2B") & OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL") == OOQL.CreateConstants(warseHouseControl) )) .GroupBy(OOQL.CreateProperty("PLANT.PLANT_CODE"), OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"), OOQL.CreateProperty("SALES_ORDER_DOC_D.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")); return(node); }
/// <summary> /// 查询是否启用条码库存管理/依单据生成条码 /// </summary> /// <returns></returns> private bool GetBcInventoryManagement() { QueryNode node = OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_INVENTORY_MANAGEMENT")) .From("PARA_FIL", "PARA_FIL") .Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL")); return(this.GetService <IQueryService>().ExecuteScalar(node).ToBoolean()); }
//20170629 add by zhangcn for P001-170606002 ===begin=== /// <summary> /// 查询采购退货单 /// </summary> /// <param name="site_no"></param> /// <param name="docNos"></param> /// <returns></returns> private QueryNode GePurchaseReturnNode(string site_no, List <ConstantsQueryProperty> docNos, string warseHouseControl) { QueryNode node = OOQL.Select(OOQL.CreateProperty("PLANT.PLANT_CODE"), OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "ITEM_FEATURE_SPECIFICATION"), OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE", "STOCK_UNIT_CODE"), OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"), OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("STOCK_UNIT.UNIT_ID", "STOCK_UNIT_ID"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID"), Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_QTY")) - Formulas.Sum(OOQL.CreateProperty("PURCHASE_RETURN_D.ISSUED_BUSINESS_QTY")), OOQL.CreateConstants(0), "SOURCE_QTY") ) .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("PLANT") .On(OOQL.CreateProperty("PURCHASE_RETURN.PLANT_ID") == OOQL.CreateProperty("PLANT.PLANT_ID")) .InnerJoin("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("UNIT", "UNIT") .On(OOQL.CreateProperty("PURCHASE_RETURN_D.BUSINESS_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .LeftJoin("UNIT", "STOCK_UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("STOCK_UNIT.UNIT_ID")) .LeftJoin("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")) .Where((OOQL.AuthFilter("PURCHASE_RETURN", "PURCHASE_RETURN")) & (OOQL.CreateProperty("PURCHASE_RETURN.DOC_NO").In(docNos.ToArray()) & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no) & OOQL.CreateProperty("PURCHASE_RETURN.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PURCHASE_RETURN.CATEGORY") == OOQL.CreateConstants("39") & OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL") == OOQL.CreateConstants(warseHouseControl) )) .GroupBy(OOQL.CreateProperty("PLANT.PLANT_CODE"), OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateProperty("STOCK_UNIT.UNIT_CODE"), OOQL.CreateProperty("ITEM_PLANT.WAREHOUSE_CONTROL"), OOQL.CreateProperty("PURCHASE_RETURN_D.ITEM_ID"), OOQL.CreateProperty("UNIT.UNIT_ID"), OOQL.CreateProperty("STOCK_UNIT.UNIT_ID"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")); return(node); }
private int QueryMO(string docNo) { QueryNode node = OOQL.Select("MO.MO_ID") .From("MO", "MO") .Where((OOQL.AuthFilter("MO", "MO")) & (OOQL.CreateProperty("MO.DOC_NO") == OOQL.CreateConstants(docNo))); return(this.GetService <IQueryService>().ExecuteDependencyObject(node).Count); }
/// <summary> /// 查询工厂 /// </summary> /// <returns></returns> private DependencyObjectCollection QueryPlant() { _queryNode = OOQL.Select(1, OOQL.CreateProperty("PLANT.COMPANY_ID", "COMPANY_ID")) .From("PLANT", "PLANT") .Where((OOQL.AuthFilter("PLANT", "PLANT")) & (OOQL.CreateProperty("PLANT.PLANT_CODE").In(_lstQueryProperties.ToArray()))); return(_querySrv.ExecuteDependencyObject(_queryNode)); }
public object QueryItemLot() { QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_PROPERTY.BC_PROPERTY_ID")) .From("BC_PROPERTY", "BC_PROPERTY") .Where(OOQL.AuthFilter("BC_PROPERTY", "BC_PROPERTY") & OOQL.CreateProperty("BC_PROPERTY.BC_PROPERTY_CODE") == OOQL.CreateConstants("Sys_Item_Lot")); return(GetService <IQueryService>().ExecuteScalar(node)); }
/// <summary> /// 查询供应商信息 /// </summary> /// <param name="supplierNo">供应商编号</param> /// <returns>供应商编号\供应商名称</returns> private QueryNode GetSupplier(string supplierNo) { return(OOQL.Select( OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE", "SUPPLIER_CODE"), OOQL.CreateProperty("SUPPLIER.SUPPLIER_NAME", "SUPPLIER_NAME")) .From("SUPPLIER", "SUPPLIER") .Where((OOQL.AuthFilter("SUPPLIER", "SUPPLIER")) & (OOQL.CreateProperty("SUPPLIER.SUPPLIER_CODE") == OOQL.CreateConstants(supplierNo)))); }
public DependencyObjectCollection QueryItemInvQty(object itemId, object itemFeatureId, string itemLotCode, string siteNo, string scanWarehouseNo,//20170331 modi by wangyq for P001-170327001 old:public DependencyObjectCollection QueryItemInvQty(DependencyObject itemInfo, string siteNo, string scan_warehouse_no, string scanStorageSpacesNo, string showZeroInventory) { QueryNode node = OOQL.Select(OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"), //wangrm OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), //wangrm Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(""), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(""), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(""), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "inventory_unit"), Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")), OOQL.CreateConstants(0), "inventory_qty") ) .From("ITEM_WAREHOUSE_BIN", "ITEM_WAREHOUSE_BIN") .InnerJoin("ITEM") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("PLANT") .On(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .LeftJoin("UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .Where(OOQL.AuthFilter("ITEM_WAREHOUSE_BIN", "ITEM_WAREHOUSE_BIN") & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & (OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") == OOQL.CreateConstants(itemId) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_ID"] | (OOQL.CreateConstants(itemId) == OOQL.CreateConstants(Maths.GuidDefaultValue()))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_ID"] & (OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_FEATURE_ID") == OOQL.CreateConstants(itemFeatureId) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_FEATURE_ID"] | (OOQL.CreateConstants(itemFeatureId) == OOQL.CreateConstants(Maths.GuidDefaultValue()))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_FEATURE_ID"] & (OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(scanWarehouseNo) | (OOQL.CreateConstants(scanWarehouseNo) == OOQL.CreateConstants(""))) & (OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateConstants(scanStorageSpacesNo) | (OOQL.CreateConstants(scanStorageSpacesNo) == OOQL.CreateConstants(""))) & (OOQL.CreateProperty("ITEM_LOT.LOT_CODE") == OOQL.CreateConstants(itemLotCode) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_LOT_CODE"] | (OOQL.CreateConstants(itemLotCode) == OOQL.CreateConstants(""))) //20170331 modi by wangyq for P001-170327001 old:itemInfo["ITEM_LOT_CODE"] )) .GroupBy(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"),//wangrm OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateProperty("UNIT.UNIT_CODE") ) .Having((OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("N") & Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY")), OOQL.CreateConstants(0)) != OOQL.CreateConstants(0)) | (OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("Y")) ); return(GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 获取登录人员的员工信息 /// </summary> /// <param name="userId">用户ID</param> /// <returns></returns> public object GetEmployeeInfo(object userId) { QueryNode node = OOQL.Select(OOQL.CreateProperty("USER.EMPLOYEE_ID")) .From("USER", "USER") .Where((OOQL.AuthFilter("USER", "USER")) & (OOQL.CreateProperty("USER.USER_ID") == OOQL.CreateConstants(userId))); object adminUnitId = GetService <IQueryService>().ExecuteScalar(node); return(Maths.IsEmpty(adminUnitId) ? Maths.GuidDefaultValue() : adminUnitId); }
//20170302 add by shenbao for B001-170221021 private DependencyObjectCollection QueryBCRecord(string siteNo, string barcode_no) { object propertyID = QueryItemLot(); object qtPropertyID = QueryItemQty(); //20170302 add by shenbao for P001-170302002 QueryNode node = OOQL.Select(true, OOQL.CreateConstants("99", "enterprise_no") //企业编号 , OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no") //营运据点 , OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no") //条码编号 , OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no") //料件编号 , OOQL.CreateArithmetic(OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION") , ArithmeticOperators.Plus, "item_name_spec") //品名 , Formulas.IsNull(OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no") //产品特征码 , OOQL.CreateConstants(string.Empty, "warehouse_no") //库位 , OOQL.CreateConstants(string.Empty, "storage_spaces_no") //储位 , Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_VALUE"), OOQL.CreateConstants(string.Empty), "lot_no") //批号 //, OOQL.CreateConstants(0m, "inventory_qty") //数量 //20170302 mark by shenbao for P001-170302002 , Formulas.IsNull(OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_VALUE"), OOQL.CreateConstants(0), "barcode_qty") //20170302 ADD by shenbao for P001-170302002 ) .From("BC_RECORD", "BC_RECORD") .InnerJoin("ITEM") .On(OOQL.CreateProperty("BC_RECORD.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("BC_RECORD.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D") .On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D.BC_RECORD_ID") & OOQL.CreateProperty("BC_RECORD_D.BC_PROPERTY_ID") == OOQL.CreateConstants(propertyID)) //20170302 add by shenbao for P001-170302002 ===begin=== .LeftJoin("BC_RECORD.BC_RECORD_D", "BC_RECORD_D_QTY") .On(OOQL.CreateProperty("BC_RECORD.BC_RECORD_ID") == OOQL.CreateProperty("BC_RECORD_D_QTY.BC_RECORD_ID") & OOQL.CreateProperty("BC_RECORD_D_QTY.BC_PROPERTY_ID") == OOQL.CreateConstants(qtPropertyID)) //20170302 add by shenbao for P001-1703020002 ===end=== .LeftJoin("ITEM_PLANT") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PLANT.ITEM_ID")) .InnerJoin("PLANT") .On(OOQL.CreateProperty("ITEM_PLANT.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where((OOQL.AuthFilter("BC_RECORD", "BC_RECORD") & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) | OOQL.CreateConstants(siteNo) == OOQL.CreateConstants(string.Empty)) // 20171023 add by 08628 for P001-171023001 ↓ & (OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(barcode_no) | OOQL.CreateConstants(barcode_no) == OOQL.CreateConstants(string.Empty)) // 20171023 add by 08628 for P001-171023001 ↑ )); return(this.GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 获取登录人员的员工信息 /// </summary> /// <param name="employeeId">员工ID</param> /// <returns></returns> public object GetAdminUnitInfo(object employeeId) { QueryNode node = OOQL.Select(OOQL.CreateProperty("EMPLOYEE_D.ADMIN_UNIT_ID")) .From("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D") .Where((OOQL.AuthFilter("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D")) & OOQL.CreateProperty("EMPLOYEE_D.EMPLOYEE_ID") == OOQL.CreateConstants(employeeId) & (OOQL.CreateProperty("EMPLOYEE_D.IS_PRIMARY") == OOQL.CreateConstants(true))); object adminUnitId = GetService <IQueryService>().ExecuteScalar(node); return(Maths.IsEmpty(adminUnitId) ? Maths.GuidDefaultValue():adminUnitId); }
public DependencyObjectCollection QueryBarCodeInvQty(string barCodeNo, string siteNo, string scanWarehouseNo, string scanStorageSpacesNo, string showZeroInventory) { QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"), OOQL.CreateProperty("ITEM.ITEM_CODE", "item_no"), OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), //20170329 add by wangrm for P001-170316001 OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), //20170329 add by wangrm for P001-170316001 OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE", "item_feature_no"), //20170329 add by wangrm for P001-170316001 OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), //20170329 add by wangrm for P001-170316001 Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(""), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(""), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(""), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(""), "inventory_unit"), Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("BC_INVENTORY.QTY")), OOQL.CreateConstants(0), "inventory_qty") ) .From("BC_INVENTORY", "BC_INVENTORY") .InnerJoin("ITEM") .On(OOQL.CreateProperty("BC_INVENTORY.ITEM_ID") == OOQL.CreateProperty("ITEM.ITEM_ID")) .LeftJoin("ITEM.ITEM_FEATURE", "ITEM_FEATURE") .On(OOQL.CreateProperty("BC_INVENTORY.ITEM_FEATURE_ID") == OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_ID")) .LeftJoin("WAREHOUSE") .On(OOQL.CreateProperty("BC_INVENTORY.WAREHOUSE_ID") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("BC_INVENTORY.BIN_ID") == OOQL.CreateProperty("BIN.BIN_ID")) .LeftJoin("ITEM_LOT") .On(OOQL.CreateProperty("BC_INVENTORY.ITEM_LOT_ID") == OOQL.CreateProperty("ITEM_LOT.ITEM_LOT_ID")) .LeftJoin("PLANT") .On(OOQL.CreateProperty("BC_INVENTORY.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .LeftJoin("UNIT") .On(OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") == OOQL.CreateProperty("UNIT.UNIT_ID")) .Where(OOQL.AuthFilter("BC_INVENTORY", "BC_INVENTORY") & ((OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO") == OOQL.CreateConstants(barCodeNo) | OOQL.CreateConstants(barCodeNo) == OOQL.CreateConstants(string.Empty)) //20170331 add by wangyq for P001-170327001 & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & (OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(scanWarehouseNo) | (OOQL.CreateConstants(scanWarehouseNo) == OOQL.CreateConstants(""))) & (OOQL.CreateProperty("BIN.BIN_CODE") == OOQL.CreateConstants(scanStorageSpacesNo) | (OOQL.CreateConstants(scanStorageSpacesNo) == OOQL.CreateConstants(""))) )) .GroupBy(OOQL.CreateProperty("ITEM.ITEM_CODE"), OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO"), OOQL.CreateProperty("ITEM.ITEM_NAME"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION"), //wangrm OOQL.CreateProperty("ITEM_FEATURE.ITEM_FEATURE_CODE"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION"), //wangrm OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateProperty("UNIT.UNIT_CODE") ) .Having((OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("N") & Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("BC_INVENTORY.QTY")), OOQL.CreateConstants(0)) != OOQL.CreateConstants(0)) | (OOQL.CreateConstants(showZeroInventory) == OOQL.CreateConstants("Y")) ); return(GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <summary> /// 获取不良原因.主键 /// </summary> /// <param name="dependencyObject"></param> /// <returns></returns> private object GetDefectiveReasonsId(string dependencyObject) { QueryNode node = OOQL.Select( OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_ID")) .From("DEFECTIVE_REASONS", "DEFECTIVE_REASONS") .Where(OOQL.AuthFilter("DEFECTIVE_REASONS", "DEFECTIVE_REASONS") & (OOQL.CreateProperty("DEFECTIVE_REASONS.DEFECTIVE_REASONS_CODE") == OOQL.CreateConstants(dependencyObject))); object defectiveReasonsId = GetService <IQueryService>().ExecuteScalar(node); return(Maths.IsNotNull(defectiveReasonsId) ? defectiveReasonsId : Maths.GuidDefaultValue()); }
/// <summary> /// 获取检验项目Id /// </summary> /// <param name="testNo"></param> /// <param name="plantId"></param> /// <returns></returns> private object GetInspectionItem(object testNo, object plantId) { QueryNode node = OOQL.Select( OOQL.CreateProperty("ii.INSPECTION_ITEM_ID", "INSPECTION_ITEM_ID")) .From("INSPECTION_ITEM", "ii") .Where((OOQL.AuthFilter("INSPECTION_ITEM", "ii")) & (OOQL.CreateProperty("ii.INSPECTION_ITEM_CODE") == OOQL.CreateConstants(testNo)) & (OOQL.CreateProperty("ii.Owner_Org.ROid") == OOQL.CreateConstants(plantId))); return(GetService <IQueryService>().ExecuteScalar(node) ?? Maths.GuidDefaultValue()); }
/// <summary> /// 获取新插入的领料单,重新保存 /// </summary> /// <param name="docNos">单号集合</param> /// <returns></returns> private DependencyObjectCollection GetSalesIssue(List <string> docNos) { QueryNode node = OOQL.Select(OOQL.CreateProperty("SALES_ISSUE.SALES_ISSUE_ID"), OOQL.CreateProperty("DOC.AUTO_APPROVE")) .From("SALES_ISSUE", "SALES_ISSUE") .InnerJoin("DOC", "DOC") .On(OOQL.CreateProperty("SALES_ISSUE.DOC_ID") == OOQL.CreateProperty("DOC.DOC_ID")) .Where(OOQL.AuthFilter("SALES_ISSUE", "SALES_ISSUE") & OOQL.CreateProperty("SALES_ISSUE.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray()))); return(this.GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <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============== }
//20170508 add by liwei1 for P001-161209002 ---begin--- /// <summary> /// 获取送货单单身存在到货单主键记录数 /// </summary> /// <param name="deliveryNo"></param> /// <returns></returns> private int GetCountNum(string deliveryNo) { QueryNode node = OOQL.Select( Formulas.Count(OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_D_ID"), "COUNT_NUM")) .From("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D") .Where((OOQL.AuthFilter("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D")) & ((OOQL.CreateProperty("FIL_ARRIVAL_D.DOC_NO") == OOQL.CreateConstants(deliveryNo)) & (OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue())) & (OOQL.CreateProperty("FIL_ARRIVAL_D.PURCHASE_ARRIVAL_D_ID") != OOQL.CreateConstants(Maths.GuidDefaultValue())))); return(GetService <IQueryService>().ExecuteScalar(node).ToInt32()); }
/// <summary> /// 获取用户信息 /// </summary> /// <param name="logonName">用户名</param> /// <returns></returns> private QueryNode GetUserInfomation(string logonName) { //20170331 modi by wangyq for P001-170327001 ===============begin============== string[] paraList = GetParaFIL(); ////20161216 add by shenbao for P001-161215001 ===begin=== ////是否启用条码库存管理 //bool isInvertory = UtilsClass.IsBCInventoryManagement(this.GetService<IQueryService>()); //string manageBarCode = isInvertory ? "Y" : "N"; ////20161216 add by shenbao for P001-161215001 ===end=== //20170331 modi by wangyq for P001-170327001 ===============end============== return(OOQL.Select(true, OOQL.CreateConstants(0, GeneralDBType.Int32, "code"), OOQL.CreateConstants(string.Empty, GeneralDBType.String, "sql_code"), OOQL.CreateConstants(string.Empty, GeneralDBType.String, "description"), OOQL.CreateConstants("99", GeneralDBType.String, "enterprise_no"), OOQL.CreateProperty("PLANT.PLANT_CODE", "site_no"), OOQL.CreateProperty("USER.LOGONNAME", "account"), OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE", "employee_no"), //20161215 add by liwei1 for P001-161215001 //OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE", "employee_name"),//20161215 mark by liwei1 for P001-161215001 OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_NAME", "employee_name"), OOQL.CreateConstants(string.Empty, GeneralDBType.String, "language "), //OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE", "department_no"),//20161215 mark by liwei1 for B001-161215015 //OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME", "department_name")//20161215 mark by liwei1 for B001-161215015 //20161215 add by liwei1 for B001-161215015 ===begin=== Formulas.IsNull( OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE"), OOQL.CreateConstants(string.Empty), "department_no"), Formulas.IsNull( OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_NAME"), OOQL.CreateConstants(string.Empty), "department_name"), //20161215 add by liwei1 for B001-161215015 ===begin=== OOQL.CreateConstants(paraList[0], "manage_barcode_inventory") //20161216 add by shenbao for P001-161215001//20170331 modi by wangyq for P001-170327001 old:manageBarCode , OOQL.CreateConstants(paraList[1], "warehouse_separator") //20170331 add by wangyq for P001-170327001 , OOQL.CreateConstants(paraList[2], "feature") //20170331 add by wangyq for P001-170327001 ) .From("USER", "USER") .InnerJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("USER.EMPLOYEE_ID"))) .LeftJoin("EMPLOYEE.EMPLOYEE_D", "EMPLOYEE_D") //20161215 modi by liwei1 for B001-161215015 old:InnerJoin .On((OOQL.CreateProperty("EMPLOYEE_D.EMPLOYEE_ID") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID")) & (OOQL.CreateProperty("EMPLOYEE_D.IS_PRIMARY") == OOQL.CreateConstants(true, GeneralDBType.Boolean))) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") //20161215 modi by liwei1 for B001-161215015 old:InnerJoin .On((OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("EMPLOYEE_D.ADMIN_UNIT_ID"))) .InnerJoin("USER.USER_ORG", "USER_ORG") .On((OOQL.CreateProperty("USER_ORG.USER_ID") == OOQL.CreateProperty("USER.USER_ID"))) .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("USER_ORG.ORG.ROid"))) .Where((OOQL.AuthFilter("USER", "USER")) & ((OOQL.CreateProperty("USER.LOGONNAME") == OOQL.CreateConstants(logonName)) & (OOQL.CreateProperty("USER_ORG.ORG.RTK") == OOQL.CreateConstants("PLANT"))))); }
private DependencyObject GetInspectionPlantD(object inspectionPlanId, object inspectionItemId) { QueryNode node = OOQL.Select(1, OOQL.CreateProperty("ipd.DEFECT_CLASS", "DEFECT_CLASS"), OOQL.CreateProperty("ipd.IMPACT_RESULT", "IMPACT_RESULT")) .From("INSPECTION_PLAN.INSPECTION_PLAN_D1", "ipd") .Where((OOQL.AuthFilter("INSPECTION_PLAN.INSPECTION_PLAN_D1", "ipd")) & (OOQL.CreateProperty("ipd.INSPECTION_PLAN_ID") == OOQL.CreateConstants(inspectionPlanId)) & (OOQL.CreateProperty("ipd.INSPECTION_ITEM_ID") == OOQL.CreateConstants(inspectionItemId))); DependencyObjectCollection inspectionPlantD = GetService <IQueryService>().ExecuteDependencyObject(node); return(inspectionPlantD.Count > 0? inspectionPlantD[0]: new DependencyObject(inspectionPlantD.ItemDependencyObjectType)); }
/// <summary> ///查询品号的其他相关信息 /// </summary> /// <param name="item_no">料件编号</param> private QueryNode GetSiteDetail(string item_no) { return(OOQL.Select( OOQL.CreateProperty("ITEM.ITEM_NAME", "item_name"), OOQL.CreateProperty("ITEM.ITEM_SPECIFICATION", "item_spec"), Formulas.IsNull( OOQL.CreateProperty("UNIT.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no")) .From("ITEM", "ITEM") .LeftJoin("UNIT", "UNIT") .On((OOQL.CreateProperty("UNIT.UNIT_ID") == OOQL.CreateProperty("ITEM.STOCK_UNIT_ID"))) .Where((OOQL.AuthFilter("ITEM", "ITEM")) & (OOQL.CreateProperty("ITEM.ITEM_CODE") == OOQL.CreateConstants(item_no)))); }
/// <summary> /// 查询送货单数据 /// </summary> /// <param name="deliveryNo">送货单单号</param> /// <returns></returns> private QueryNode QueryFilArrival(string deliveryNo) { return(OOQL.Select(true, OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO", "delivery_no"), OOQL.CreateProperty("FIL_ARRIVAL.DOC_DATE", "date"), OOQL.CreateProperty("PURCHASE_ORDER.SUPPLIER_FULL_NAME", "supplier_name")) .From("FIL_ARRIVAL", "FIL_ARRIVAL") .InnerJoin("FIL_ARRIVAL.FIL_ARRIVAL_D", "FIL_ARRIVAL_D") .On((OOQL.CreateProperty("FIL_ARRIVAL_D.FIL_ARRIVAL_ID") == OOQL.CreateProperty("FIL_ARRIVAL.FIL_ARRIVAL_ID"))) .InnerJoin("PURCHASE_ORDER", "PURCHASE_ORDER") .On((OOQL.CreateProperty("PURCHASE_ORDER.DOC_NO") == OOQL.CreateProperty("FIL_ARRIVAL_D.ORDER_NO"))) .Where((OOQL.AuthFilter("FIL_ARRIVAL", "FIL_ARRIVAL")) & (OOQL.CreateProperty("FIL_ARRIVAL.DOC_NO") == OOQL.CreateConstants(deliveryNo)))); }
//20161216 add by shenbao for P001-161215001 /// <summary> /// 查询是否启用启用条码库存管理 /// </summary> /// <param name="srv"></param> /// <returns></returns> public static bool IsBCInventoryManagement(IQueryService srv) { QueryNode node = OOQL.Select("PARA_FIL.BC_INVENTORY_MANAGEMENT") .From("PARA_FIL", "PARA_FIL") .Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL")); object obj = srv.ExecuteScalar(node); if (obj == null) { return(false); } return(obj.ToBoolean()); }
public bool IsBcLineManagement() { QueryNode node = OOQL.Select("PARA_FIL.BC_INVENTORY_MANAGEMENT") .From("PARA_FIL", "PARA_FIL") .Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL")); object obj = GetService <IQueryService>().ExecuteScalar(node); if (obj == null) { return(false); } return(obj.ToBoolean()); }