/// <summary> /// 单头新增查询 /// </summary> /// <param name="report_datetime"></param> /// <param name="category"></param> /// <returns></returns> private DataTable QueryForMoReceiptReq(DateTime report_datetime, string category, string recommended_operations, object docId) //20170104 modi by wangyq for P001-161215001 添加参数recommended_operations //20170209 modi by liwei1 for P001-170124001 添加参数:docId { QueryNode node = OOQL.Select(true, Formulas.Cast(OOQL.CreateConstants(docId), GeneralDBType.Guid, "DOC_ID"),//20170209 add by liwei1 for P001-170124001 OOQL.CreateConstants(report_datetime, GeneralDBType.Date, "DOC_DATE"), OOQL.CreateConstants(string.Empty, GeneralDBType.String, "DOC_NO"), OOQL.CreateConstants(report_datetime, GeneralDBType.Date, "TRANSACTION_DATE"), OOQL.CreateConstants("PLANT", GeneralDBType.String, "Owner_Org_RTK"), OOQL.CreateProperty("PLANT.PLANT_ID", "Owner_Org_ROid"), OOQL.CreateProperty("MO.SOURCE_ID.ROid", "WORK_CENTER_ID"), Formulas.IsNull(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "Owner_Dept"),//20170328 modi by wangyq for P001-170327001 添加null判断 OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID", "Owner_Emp"), OOQL.CreateProperty("Table_scan.ID", "MO_RECEIPT_REQUISTION_ID"), OOQL.CreateConstants(false, GeneralDBType.Boolean, "CLOSE"), OOQL.CreateProperty("DOC.QC_RESULT_INPUT_TYPE", "temp_QC_RESULT_INPUT_TYPE") ) .From(_Table_scan.Name, "Table_scan") .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE")) .InnerJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("Table_scan.employee_no") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_CODE")) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") //20170328 modi by wangyq for P001-170327001 old:InnerJoin .On(OOQL.CreateProperty("Table_scan.picking_department_no") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_CODE")) .InnerJoin("DOC", "DOC") .On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateConstants(docId)) //20170209 add by liwei1 for P001-170124001 old:OOQL.CreateProperty("DOC.DOC_ID")) .InnerJoin(_Table_scan_detail.Name, "Table_scan_detail") .On(OOQL.CreateProperty("Table_scan.info_lot_no") == OOQL.CreateProperty("Table_scan_detail.info_lot_no")) .InnerJoin("MO", "MO") .On(OOQL.CreateProperty("Table_scan_detail.SequenceNumber") == OOQL.CreateConstants(1, GeneralDBType.Int32) & OOQL.CreateProperty("Table_scan_detail.doc_no") == OOQL.CreateProperty("MO.DOC_NO")); return(_qurService.Execute(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)); }
/// <summary> /// 对单身分组 /// 单身和条码明显的分组依据不一样 /// </summary> /// <param name="isEntityLine"></param> /// <returns></returns> public QueryNode GroupNode(bool isEntityLine) { List <QueryProperty> properties = new List <QueryProperty> { // OOQL.CreateProperty("TMP.info_lot_no") OOQL.CreateProperty("TMP.item_no") , OOQL.CreateProperty("TMP.item_feature_no") , OOQL.CreateProperty("TMP.picking_unit_no") , OOQL.CreateProperty("TMP.doc_no") , OOQL.CreateProperty("TMP.seq") , OOQL.CreateProperty("TMP.warehouse_no") , OOQL.CreateProperty("TMP.storage_spaces_no") , OOQL.CreateProperty("TMP.lot_no") , OOQL.CreateProperty("TMP.site_no") }; List <QueryProperty> groupProperties = new List <QueryProperty>(); groupProperties = new List <QueryProperty>(); groupProperties.AddRange(properties); if (isEntityLine) { properties.Add(OOQL.CreateProperty("TMP.barcode_no")); groupProperties.Add(OOQL.CreateProperty("TMP.barcode_no")); } properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.picking_qty")), OOQL.CreateConstants(0), "picking_qty")); QueryNode node = OOQL.Select(properties) .From(_TEMP_SCAN_DETAIL.Name, "TMP") .GroupBy(groupProperties); return(node); }
/// <summary> /// 例如,根据colls 插入到表,colls应与表字段一致 /// </summary> /// <param name="qurService"></param> /// <param name="businessTypeSrv"></param> /// <param name="entityD"></param> /// <param name="TableName"></param> private void Insert(IQueryService qurService, DependencyObjectCollection colls, string typeKey) { var ItemDependencyObjectType = colls.ItemDependencyObjectType; //创建临时表 DependencyObjectType tmpType = RegiesterType(ItemDependencyObjectType, null); qurService.CreateTempTable(tmpType); //创建DataTable var TempDt = new DataTable(); TempDt = CreateDt(ItemDependencyObjectType, TempDt); DOCToDataTable(colls, TempDt); //插入临时表 List <QueryProperty> propies = new List <QueryProperty>(); InsertTemp(qurService, TempDt, tmpType.Name); if (TempDt == null) { return; } foreach (DataColumn row in TempDt.Columns) { propies.Add(OOQL.CreateProperty(row.ColumnName, row.ColumnName)); } if (propies != null && propies.Count > 0) { QueryNode node = OOQL.Select(propies ).From(tmpType.Name); node = OOQL.Insert(typeKey, node, propies.Select(c => c.Alias).ToArray()); qurService.ExecuteNoQueryWithManageProperties(node); } }
private void TextOrm() { //_hepler.VeryfiyRepair(); DependencyObject entity = (DependencyObject)this._helper.Create(Product.DefaultType); entity[0] = Guid.NewGuid().ToString(); entity[1] = "001"; entity[2] = "CPU"; //this._helper.Save(entity); QueryNode node = OOQL.Select("CODE", "NAME") .From("Product") .Where(OOQL.CreateProperty("ID") == OOQL.CreateConstants("3DE1E070-241C-42BE-8704-AC46140CCE09")); IQueryBuilder builder = OrmEntry.CreateQuery(_helper.DBSource, _helper.DBDriver); string str = builder.Select("Product", new string[] { "CODE", "NAME" }, null, null); Digiwin.Common.Torridity.Query.QueryEntity queryEn = new Digiwin.Common.Torridity.Query.QueryEntity("Product"); //DataSet ds = builder.ExecuteSelect(new Digiwin.Common.Torridity.Query.QueryEntity[] { queryEn }); Product p = new Product(((DependencyObject)Product.DefaultType.CreateInstance())); p.ID = Guid.NewGuid(); p.CODE = "222"; this._helper.Save(p.DependencyObject); }
/// <summary> /// 对单身分组 /// 单身和条码明显的分组依据不一样 /// </summary> /// <param name="tmpIssueReceiptD"></param> /// <returns></returns> public QueryNode GroupNode(bool isEntityLine) { List <QueryProperty> properties = new List <QueryProperty>(); properties.Add(OOQL.CreateProperty("TMP.ID")); properties.Add(OOQL.CreateProperty("TMP.SequenceNumber")); properties.Add(OOQL.CreateProperty("TMP.item_no")); properties.Add(OOQL.CreateProperty("TMP.item_feature_no")); properties.Add(OOQL.CreateProperty("TMP.picking_unit_no")); properties.Add(OOQL.CreateProperty("TMP.warehouse_no")); properties.Add(OOQL.CreateProperty("TMP.storage_spaces_no")); properties.Add(OOQL.CreateProperty("TMP.lot_no")); properties.Add(OOQL.CreateProperty("TMP.info_lot_no")); if (isEntityLine) { properties.Add(OOQL.CreateProperty("TMP.barcode_no")); } List <QueryProperty> groupProperties = new List <QueryProperty>(); groupProperties = new List <QueryProperty>(); groupProperties.AddRange(properties); properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.picking_qty")), OOQL.CreateConstants(0), "picking_qty")); QueryNode node = OOQL.Select(properties.ToArray()) .From(_tempScanDetail.Name, "TMP") .GroupBy(groupProperties); 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="tmpIssueReceiptD"></param> /// <returns></returns> public QueryNode GroupNode(IDataEntityType tmpIssueReceiptD, bool isEntityLine) { List <QueryProperty> properties = new List <QueryProperty> { OOQL.CreateProperty("TMP.DOC_NO") , OOQL.CreateProperty("TMP.SequenceNumber") , OOQL.CreateProperty("TMP.PLANT_CODE") , OOQL.CreateProperty("TMP.ITEM_CODE") , OOQL.CreateProperty("TMP.ITEM_FEATURE_CODE") , OOQL.CreateProperty("TMP.UNIT_CODE") , OOQL.CreateProperty("TMP.WAREHOUSE_CODE") , OOQL.CreateProperty("TMP.BIN_CODE") , OOQL.CreateProperty("TMP.LOT_CODE") }; List <QueryProperty> groupProperties = new List <QueryProperty>(); groupProperties = new List <QueryProperty>(); groupProperties.AddRange(properties); if (!isEntityLine) { properties.Add(OOQL.CreateProperty("TMP.BARCODE_NO")); groupProperties.Add(OOQL.CreateProperty("TMP.BARCODE_NO")); } properties.Add(Formulas.IsNull(Formulas.Sum(OOQL.CreateProperty("TMP.ISSUE_RECEIPT_QTY")), OOQL.CreateConstants(0), "ISSUE_RECEIPT_QTY")); QueryNode node = OOQL.Select(properties ) .From(tmpIssueReceiptD.Name, "TMP") .GroupBy(groupProperties); return(node); }
/// <summary> /// 查询到货单信息 /// </summary> /// <param name="receiptNo"></param> /// <returns></returns> private DependencyObjectCollection GetPurchaseArrival(string receiptNo) { QueryNode node = OOQL.Select( OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid", "RECEIVE_Owner_Org_ROid"), OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_D_ID", "PURCHASE_ARRIVAL_D_ID"), OOQL.CreateProperty("pad.DEDUCT_ARRIVED_QTY", "DEDUCT_ARRIVED_QTY"), OOQL.CreateProperty("pad.PURCHASE_TYPE", "PURCHASE_TYPE"), OOQL.CreateProperty("mrd.INSPECT_MODE", "INSPECT_MODE_MRD"), OOQL.CreateProperty("ip.INSPECT_MODE", "INSPECT_MODE_IP"), OOQL.CreateProperty("pa.DOC_NO", "DOC_NO"), OOQL.CreateProperty("pad.SequenceNumber", "SequenceNumber")) .From("PURCHASE_ARRIVAL", "pa") .InnerJoin("PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_D", "pad") .On((OOQL.CreateProperty("pad.PURCHASE_ARRIVAL_ID") == OOQL.CreateProperty("pa.PURCHASE_ARRIVAL_ID"))) .LeftJoin("ITEM_PLANT", "ip") .On((OOQL.CreateProperty("ip.ITEM_ID") == OOQL.CreateProperty("pad.ITEM_ID")) & (OOQL.CreateProperty("ip.Owner_Org.ROid") == OOQL.CreateProperty("pa.RECEIVE_Owner_Org.ROid"))) .LeftJoin("MO_ROUTING.MO_ROUTING_D", "mrd") .On((OOQL.CreateProperty("mrd.MO_ROUTING_D_ID") == OOQL.CreateProperty("pad.REFERENCE_SOURCE_ID.ROid"))) .Where((OOQL.CreateProperty("pa.DOC_NO") == OOQL.CreateConstants(receiptNo))); return(GetService <IQueryService>().ExecuteDependencyObject(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="tmpIssueReceiptD"></param> /// <returns></returns> public QueryNode GetGroupForInsert(IDataEntityType tempEntityD) { List <QueryProperty> selectOrGroupList = new List <QueryProperty>(); selectOrGroupList.Add(OOQL.CreateProperty("item_no")); selectOrGroupList.Add(OOQL.CreateProperty("item_feature_no")); selectOrGroupList.Add(OOQL.CreateProperty("picking_unit_no")); selectOrGroupList.Add(OOQL.CreateProperty("doc_no")); selectOrGroupList.Add(OOQL.CreateProperty("seq")); selectOrGroupList.Add(OOQL.CreateProperty("warehouse_no")); selectOrGroupList.Add(OOQL.CreateProperty("storage_spaces_no")); selectOrGroupList.Add(OOQL.CreateProperty("lot_no")); selectOrGroupList.Add(OOQL.CreateProperty("barcode_no")); List <QueryProperty> selectList = new List <QueryProperty>(); foreach (QueryProperty selectOrGroupObj in selectOrGroupList) { selectList.Add(selectOrGroupObj); } selectList.Add(Formulas.Sum(OOQL.CreateProperty("picking_qty"), "sum_picking_qty")); QueryNode selectNode = OOQL.Select(selectList.ToArray()) .From(tempEntityD.Name, "TEMP") .GroupBy(selectOrGroupList.ToArray()); return(selectNode); }
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); }
//20170329 add by wangrm for P001-170316001======start======= private DependencyObjectCollection QueryBcFrozen(string scanBarcode) { QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_RECORD.BARCODE_NO", "barcode_no"), 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"), OOQL.CreateProperty("ITEM_FEATURE.ITEM_SPECIFICATION", "item_feature_name"), Formulas.Case(null, OOQL.CreateConstants("N"), OOQL.CreateCaseArray( OOQL.CreateCaseItem(((OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateEmptyConstants()) & (OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS") != OOQL.CreateNullConstant())), OOQL.CreateProperty("BC_RECORD.FROZEN_STATUS"))), "frozen"), OOQL.CreateConstants(string.Empty, "warehouse_no"), OOQL.CreateConstants(string.Empty, "storage_spaces_no"), OOQL.CreateConstants(string.Empty, "lot_no"), OOQL.CreateConstants(0m, "inventory_qty"), OOQL.CreateConstants(string.Empty, "inventory_unit")) .From("BC_RECORD", "BC_RECORD") .LeftJoin("ITEM", "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")) .Where(OOQL.CreateProperty("BC_RECORD.BARCODE_NO") == OOQL.CreateConstants(scanBarcode)); return(GetService <IQueryService>().ExecuteDependencyObject(node)); }
/// <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); }
//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> /// <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> /// 根据单号获取最大序号 /// </summary> /// <param name="countingNo"></param> /// <returns></returns> private int GetMaxSeq(string countingNo) { QueryNode node = OOQL.Select(Formulas.Max(OOQL.CreateProperty("SequenceNumber"), "SequenceNumber")) .From("COUNTING_PLAN", "COUNTING_PLAN") .Where(OOQL.CreateProperty("DOC_NO") == OOQL.CreateConstants(countingNo)); return(this.GetService <IQueryService>().ExecuteScalar(node).ToInt32()); }
/// <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()); }
/// <summary> /// 查询FIL参数 /// </summary> /// <returns></returns> private DependencyObjectCollection QueryParaFil() { _queryNode = OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_LINE_MANAGEMENT", "BC_LINE_MANAGEMENT")) .From("PARA_FIL", "PARA_FIL"); return(_querySrv.ExecuteDependencyObject(_queryNode)); }
//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); }
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)); }
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)); }
/// <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="docNos">单号集合</param> /// <returns></returns> private DependencyObjectCollection QueryDoc(List <string> docNos) { _queryNode = OOQL.Select(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"), OOQL.CreateProperty("DOC.AUTO_APPROVE")) .From("TRANSACTION_DOC", "TRANSACTION_DOC") .InnerJoin("DOC", "DOC") .On(OOQL.CreateProperty("DOC.DOC_ID") == OOQL.CreateProperty("TRANSACTION_DOC.DOC_ID")) .Where(OOQL.CreateProperty("TRANSACTION_DOC.DOC_NO").In(OOQL.CreateDyncParameter("docnos", docNos.ToArray()))); return(_querySrv.ExecuteDependencyObject(_queryNode)); }
/// <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); }
/// <summary> /// 查询条码规则属性项 /// </summary> /// <param name="queryService">查询服务</param> /// <param name="bcPropertyCode">属性项编码</param> /// <returns></returns> private object GetBcPropertyId(IQueryService queryService, string bcPropertyCode) { QueryNode node = OOQL.Select(OOQL.CreateProperty("BC_PROPERTY_ID")) .From("BC_PROPERTY") .Where(OOQL.CreateProperty("BC_PROPERTY_CODE") == OOQL.CreateConstants(bcPropertyCode)); object result = queryService.ExecuteScalar(node); //20161229 add by shenbao for P001-161215001 return(result); }
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)); }