/// <summary> /// 创建批量修改所需要的DataTable和Mapping /// </summary> private void CreateRelateTable(ref DataTable issueReceiptD , ref List <BulkCopyColumnMapping> issueReceiptDMap) { #region 创建领料单身表 string[] issueReceiptDColumns = new string[] { "DOC_NO", //单号 "SequenceNumber", //序号 "ISSUE_RECEIPT_QTY", //领退料数量 "PLANT_CODE", //工厂编号 "ITEM_CODE", //品号 "ITEM_FEATURE_CODE", //特征码 "UNIT_CODE", //单位编号 "WAREHOUSE_CODE", //仓库编号 "BIN_CODE", //库位编号 "LOT_CODE", //批号 "BARCODE_NO" //条码 }; issueReceiptD = UtilsClass.CreateDataTable("ISSUE_RECEIPT_D", issueReceiptDColumns, new Type[] { typeof(string), //单号 typeof(int), //序号 typeof(decimal), //领退料数量 typeof(string), //工厂编号 typeof(string), //品号 typeof(string), //特征码 typeof(string), //单位编号 typeof(string), //仓库编号 typeof(string), //库位编号 typeof(string), //批号 typeof(string) //条码 }); //创建map对照表 Dictionary <string, string> dicIssueReceiptD = new Dictionary <string, string>(); foreach (string key in issueReceiptDColumns) { dicIssueReceiptD.Add(key, key); } issueReceiptDMap = UtilsClass.CreateBulkMapping(dicIssueReceiptD); #endregion }
/// <summary> /// 获取调出单查询信息 /// </summary> /// <param name="siteNo">工厂编号</param> /// <param name="programJobNo">作业编号</param> /// <returns></returns> private QueryNode GetTransferDocQueryNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode queryNode = OOQL.Select(true, OOQL.CreateProperty("TRANSFER_DOC.DOC_NO", "doc_no"), OOQL.CreateProperty("TRANSFER_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("TRANSFER_DOC", "TRANSFER_DOC") .InnerJoin("TRANSFER_DOC.TRANSFER_DOC_D", "TRANSFER_DOC_D") .On(OOQL.CreateProperty("TRANSFER_DOC_D.TRANSFER_DOC_ID") == OOQL.CreateProperty("TRANSFER_DOC.TRANSFER_DOC_ID")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Org.ROid")) .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Dept")) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On(OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID") == OOQL.CreateProperty("TRANSFER_DOC.Owner_Emp")); QueryConditionGroup group = //20170328 add by wangyq for P001-170327001 //.Where((OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC")) &//20170328 mark by wangyq for P001-170327001 ((OOQL.CreateProperty("TRANSFER_DOC.CATEGORY") == OOQL.CreateConstants("17")) & (OOQL.CreateProperty("TRANSFER_DOC.ApproveStatus") == OOQL.CreateConstants("Y")) & (OOQL.CreateProperty("TRANSFER_DOC_D.CLOSE") == OOQL.CreateConstants("0")) & (OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo)) ); //);//20170328 mark by wangyq for P001-170327001 //20170328 add by wangyq for P001-170327001 ================begin============== if (condition != null) { ConditionPropertyNameEntity conPropertyEntity = new ConditionPropertyNameEntity("TRANSFER_DOC.DOC_NO", "TRANSFER_DOC.DOC_DATE", new string[] { "1", "2", "3", "4" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } queryNode = ((JoinOnNode)queryNode).Where(OOQL.AuthFilter("TRANSFER_DOC", "TRANSFER_DOC") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(queryNode); }
private QueryNode GetMOReceiptReqListNode(string siteNo, string programJobNo , DependencyObjectCollection condition//20170328 add by wangyq for P001-170327001 ) { QueryNode node = OOQL.Select( OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_NO", "doc_no"), OOQL.CreateProperty("MO_RECEIPT_REQUISTION.DOC_DATE", "create_date"), Formulas.IsNull(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("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION") .InnerJoin("PLANT", "PLANT") .On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID"))) .LeftJoin("WORK_CENTER") .On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Dept") == OOQL.CreateProperty("WORK_CENTER.WORK_CENTER_ID"))) .LeftJoin("EMPLOYEE", "EMPLOYEE") .On((OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Emp") == OOQL.CreateProperty("EMPLOYEE.EMPLOYEE_ID"))) //20170328 add by wangyq for P001-170327001 ================begin============== .LeftJoin("ADMIN_UNIT", "ADMIN_UNIT") .On(OOQL.CreateProperty("MO_RECEIPT_REQUISTION.Owner_Dept") == OOQL.CreateProperty("ADMIN_UNIT.ADMIN_UNIT_ID")); //20170328 add by wangyq for P001-170327001 ================end============== //.Where((OOQL.AuthFilter("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION")) &//20170328 mark by wangyq for P001-170327001 QueryConditionGroup group =//20170328 add by wangyq for P001-170327001 (OOQL.CreateProperty("MO_RECEIPT_REQUISTION.ApproveStatus") == OOQL.CreateConstants("Y") & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(siteNo) & OOQL.CreateProperty("MO_RECEIPT_REQUISTION.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("MO_RECEIPT_REQUISTION.DOC_NO", "MO_RECEIPT_REQUISTION.DOC_DATE", new string[] { "1", "2", "3", "4" }); group = UtilsClass.CreateNewConditionByParameter(group, condition, conPropertyEntity); } node = ((JoinOnNode)node).Where(OOQL.AuthFilter("MO_RECEIPT_REQUISTION", "MO_RECEIPT_REQUISTION") & (group)); //20170328 add by wangyq for P001-170327001 ================end============== return(node); }
/// <summary> /// 根据品号获取品号采购信息 /// </summary> /// <param name="itemID">品号ID集合</param> /// <param name="mainOrganization">采购域</param> /// <returns></returns> private DependencyObjectCollection GetItemForSupplyCenter(DependencyObjectCollection itemID, string siteNo, object mainOrganization, string programJobNo) { List <QueryProperty> properties = GetQueryProperty(siteNo); properties.Add(Formulas.IsNull(OOQL.CreateProperty("ITEM_PURCHASE.RECEIPT_OVER_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_PURCHASE") .On(OOQL.CreateProperty("ITEM.ITEM_ID") == OOQL.CreateProperty("ITEM_PURCHASE.ITEM_ID")) .Where(OOQL.AuthFilter("ITEM", "ITEM") & OOQL.CreateProperty("ITEM_PURCHASE.Owner_Org.ROid") == OOQL.CreateConstants(mainOrganization) & OOQL.CreateProperty("ITEM.ITEM_ID").In(OOQL.CreateDyncParameter("IDS", itemID.Select(c => c["item_id"]).ToArray()))); return(UtilsClass.ExecuteDependencyObject(this.GetService <IQueryService>(), node)); }
/// <summary> /// /// </summary> /// <returns></returns> private string[] GetParaFIL() { ICreateService createService = this.GetService <ICreateService>("PARA_FIL"); DependencyObject filEntity = createService.Create() as DependencyObject; string[] result = new string[] { string.Empty, string.Empty, string.Empty }; if (filEntity.DependencyObjectType.Properties.Contains("BARCODE_SYMBOL_FOR_WH_BIN")) { QueryNode node = OOQL.Select(OOQL.CreateProperty("PARA_FIL.BC_INVENTORY_MANAGEMENT"), OOQL.CreateProperty("PARA_FIL.BARCODE_SYMBOL_FOR_WH_BIN"), OOQL.CreateProperty("PARA_FIL.ITEM_FEATURE_CONTROL")) .From("PARA_FIL", "PARA_FIL") .Where(OOQL.AuthFilter("PARA_FIL", "PARA_FIL")); DependencyObjectCollection filColl = this.GetService <IQueryService>().ExecuteDependencyObject(node); foreach (DependencyObject filObj in filColl) { result[0] = filObj["BC_INVENTORY_MANAGEMENT"].ToBoolean() ? "Y" : "N"; if (string.IsNullOrEmpty(filObj["BARCODE_SYMBOL_FOR_WH_BIN"].ToStringExtension())) { result[1] = "@"; } else { result[1] = filObj["BARCODE_SYMBOL_FOR_WH_BIN"].ToStringExtension(); } result[2] = filObj["ITEM_FEATURE_CONTROL"].ToBoolean() ? "Y" : "N"; break; } } else { bool isInvertory = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>()); result[0] = isInvertory ? "Y" : "N"; result[1] = "@"; result[2] = "Y"; } return(result); }
/// <summary> /// 利用临时表关联实体表进行批量新增条码交易明细 /// </summary> /// <param name="qrySrv"></param> /// <param name="tmpBCLine"></param> private void InsertBCLine(DateTime report_datetime) //20170413 modi by wangyq for P001-170412001 添加参数DateTime report_datetime { bool bcLintFlag = UtilsClass.IsBCLineManagement(_qurService); if (!bcLintFlag) { return; } List <QueryProperty> selectList = new List <QueryProperty>(); #region 查询新增字段集合 selectList.Add(Formulas.NewId("BC_LINE_ID")); selectList.Add(OOQL.CreateProperty("tmpTable.barcode_no", "BARCODE_NO")); selectList.Add(OOQL.CreateConstants("TRANSACTION_DOC.TRANSACTION_DOC_D", "SOURCE_ID_RTK")); selectList.Add(OOQL.CreateProperty("tmpTable.ID", "SOURCE_ID_ROid")); selectList.Add(Formulas.Ext("UNIT_CONVERT", "QTY", new object[] { OOQL.CreateProperty("ITEM.ITEM_ID") , OOQL.CreateProperty("UNIT.UNIT_ID") , OOQL.CreateProperty("tmpTable.picking_qty") , OOQL.CreateProperty("ITEM.STOCK_UNIT_ID") , OOQL.CreateConstants(0) })); selectList.Add(Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "WAREHOUSE_ID")); selectList.Add(Formulas.IsNull(Formulas.Case(null, OOQL.CreateProperty("BIN.BIN_ID"), new CaseItem[] { new CaseItem(OOQL.CreateProperty("tmpTable.storage_spaces_no") == OOQL.CreateConstants(string.Empty) , OOQL.CreateConstants(Maths.GuidDefaultValue())) }), OOQL.CreateConstants(Maths.GuidDefaultValue()), "BIN_ID")); //20170413 modi by wangyq for P001-170412001 ==================begin================= //20170330 add by wangrm for P001-170328001=====start======= selectList.Add(OOQL.CreateConstants("PLANT", GeneralDBType.String, "Owner_Org_RTK")); selectList.Add(OOQL.CreateProperty("PLANT.PLANT_ID", "Owner_Org_ROid")); selectList.Add(OOQL.CreateProperty("Table_scan.ID", "SOURCE_DOC_ID")); selectList.Add(OOQL.CreateConstants(report_datetime.Date, GeneralDBType.Date, "DOC_DATE")); //selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.RTK"), OOQL.CreateConstants(string.Empty), "Owner_Org_RTK")); //selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.Owner_Org.ROid"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "Owner_Org_ROid")); //selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue()), "SOURCE_DOC_ID")); //selectList.Add(Formulas.IsNull(OOQL.CreateProperty("TRANSACTION_DOC.DOC_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime), "DOC_DATE")); //20170330 add by wangrm for P001-170328001=====end======= //20170413 modi by wangyq for P001-170412001 ==================end================= #endregion QueryNode groupNode = GroupNode(true); QueryNode insertNode = OOQL.Select(selectList.ToArray()) .From(groupNode, "tmpTable") .InnerJoin(_tempScan.Name, "Table_scan") .On(OOQL.CreateProperty("Table_scan.info_lot_no") == OOQL.CreateProperty("tmpTable.info_lot_no")) .InnerJoin("PLANT", "PLANT") .On(OOQL.CreateProperty("Table_scan.site_no") == OOQL.CreateProperty("PLANT.PLANT_CODE")) .InnerJoin("ITEM", "ITEM") .On(OOQL.CreateProperty("tmpTable.item_no") == OOQL.CreateProperty("ITEM.ITEM_CODE")) .InnerJoin("UNIT", "UNIT") .On(OOQL.CreateProperty("tmpTable.picking_unit_no") == OOQL.CreateProperty("UNIT.UNIT_CODE")) .LeftJoin("WAREHOUSE", "WAREHOUSE") .On(OOQL.CreateProperty("tmpTable.warehouse_no") == OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") & OOQL.CreateProperty("PLANT.PLANT_ID") == OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid")) .LeftJoin("WAREHOUSE.BIN", "BIN") .On(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_ID") == OOQL.CreateProperty("BIN.WAREHOUSE_ID") & OOQL.CreateProperty("tmpTable.storage_spaces_no") == OOQL.CreateProperty("BIN.BIN_CODE")) //20170413 mark by wangyq for P001-170412001 ==================begin================ //20170330 add by wangrm for P001-170328001=====start======= //.LeftJoin("TRANSACTION_DOC", "TRANSACTION_DOC") //.On(OOQL.CreateProperty("Table_scan.ID") == OOQL.CreateProperty("TRANSACTION_DOC.TRANSACTION_DOC_ID")) //20170330 add by wangrm for P001-170328001=====end======= //20170413 mark by wangyq for P001-170412001 ==================end================ .Where(OOQL.CreateProperty("tmpTable.barcode_no") != OOQL.CreateConstants(string.Empty)); List <string> insertStrList = new List <string>(); #region 新增字段集合 insertStrList.Add("BC_LINE_ID"); insertStrList.Add("BARCODE_NO"); insertStrList.Add("SOURCE_ID.RTK"); insertStrList.Add("SOURCE_ID.ROid"); insertStrList.Add("QTY"); insertStrList.Add("WAREHOUSE_ID"); insertStrList.Add("BIN_ID"); //20170330 add by wangrm for P001-170328001=====start======= insertStrList.Add("Owner_Org_RTK"); insertStrList.Add("Owner_Org_ROid"); insertStrList.Add("SOURCE_DOC_ID"); insertStrList.Add("DOC_DATE"); //20170330 add by wangrm for P001-170328001=====end======= #endregion QueryNode node = OOQL.Insert("BC_LINE", insertNode, insertStrList.ToArray()); _qurService.ExecuteNoQueryWithManageProperties(node); }
private QueryNode QueryData(string program_job_no, string status, DependencyObjectCollection doc_no, string site_no, string warehouse_no, string warseHouseControl) { OrderByItem[] orderByItem = null; QueryProperty property = null; bool bcInvControl = UtilsClass.IsBCInventoryManagement(this.GetService <IQueryService>()); if (bcInvControl) { property = OOQL.CreateProperty("BC_INVENTORY.QTY"); } else { property = OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"); } //策略 if (warseHouseControl == "1") //1.生效日期先进先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "2") //2.生效日期后进先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.EFFECTIVE_DATE", SortType.Desc) } } ; else if (warseHouseControl == "3") //3.不限定 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.LOT_CODE", SortType.Asc) } } ; else if (warseHouseControl == "4") //4.先到期先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.INEFFECTIVE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "5") //5.允许出库日早者先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem("ITEM_LOT.ALLOW_ISSUE_DATE", SortType.Asc) } } ; else if (warseHouseControl == "6") //6.可用量少的先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Asc) }; } else if (warseHouseControl == "7") //6.可用量少的先出 { orderByItem = new OrderByItem[] { OOQL.CreateOrderByItem(property, SortType.Desc) }; } //组织单号 List <ConstantsQueryProperty> docNos = new List <ConstantsQueryProperty>(); foreach (DependencyObject item in doc_no) { docNos.Add(OOQL.CreateConstants(item["doc_no"].ToStringExtension())); } QueryNode node = null; if (program_job_no == "5")//20170328 modi by wangyq for P001-170327001去掉&& status == "A" { node = GetSalesDeliveryNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("7") && status == "A") { node = GetMONode(site_no, program_job_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("7") && status == "S") { node = GetIssueReceiptNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("11") && status == "S") { node = GeTransferDocNode(site_no, docNos, warseHouseControl); } else if (program_job_no.StartsWith("4")) //20170629 add by zhangcn for P001-170606002 //20170719 modi by shenbao for 拿掉status == "A" { node = GePurchaseReturnNode(site_no, docNos, warseHouseControl); //20170629 add by zhangcn for P001-170606002 } else if (program_job_no.StartsWith("5-1")) //20170829 add by shenbao for P001-170717001 { node = GetSalesOrderDocNode(site_no, docNos, warseHouseControl); } List <QueryProperty> pubProperties = new List <QueryProperty>() { OOQL.CreateConstants("99", "enterprise_no"), OOQL.CreateProperty("QuerySource.PLANT_CODE", "site_no"), OOQL.CreateProperty("QuerySource.ITEM_CODE", "item_no"), OOQL.CreateProperty("QuerySource.ITEM_NAME", "item_name"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_spec"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_CODE"), OOQL.CreateConstants(string.Empty), "item_feature_no"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_SPECIFICATION"), OOQL.CreateConstants(string.Empty), "item_feature_name"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.UNIT_CODE"), OOQL.CreateConstants(string.Empty), "unit_no"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.STOCK_UNIT_CODE"), OOQL.CreateConstants(string.Empty), "inventory_unit"), Formulas.IsNull(OOQL.CreateProperty("QuerySource.SOURCE_QTY"), OOQL.CreateConstants(0), "SOURCE_QTY") }; //查询 if (bcInvControl) { pubProperties.AddRange(new QueryProperty[] { OOQL.CreateProperty("BC_INVENTORY.BARCODE_NO", "barcode_no"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), OOQL.CreateConstants(UtilsClass.SpaceValue, "inventory_management_features"),//20170328 modi by wangyq for P001-170327001 old:string.Empty Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.CreateDate"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"), Formulas.IsNull(OOQL.CreateProperty("BC_INVENTORY.QTY"), OOQL.CreateConstants(0), "inventory_qty"), Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID") , OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID") , OOQL.CreateProperty("BC_INVENTORY.QTY") , OOQL.CreateProperty("QuerySource.UNIT_ID") , OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"), Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") }, orderByItem )) }); node = OOQL.Select( pubProperties ) .From(node, "QuerySource") .LeftJoin("BC_INVENTORY") .On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("BC_INVENTORY.ITEM_ID") & Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("BC_INVENTORY.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") //20170719 add by shenbao for P001-170717001 .On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)) | OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants("")) & OOQL.CreateProperty("BC_INVENTORY.QTY") > OOQL.CreateConstants(0) & OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001 } else { pubProperties.AddRange(new QueryProperty[] { OOQL.CreateConstants("", "barcode_no"), Formulas.IsNull(OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE"), OOQL.CreateConstants(string.Empty), "warehouse_no"), Formulas.IsNull(OOQL.CreateProperty("BIN.BIN_CODE"), OOQL.CreateConstants(string.Empty), "storage_spaces_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_LOT.LOT_CODE"), OOQL.CreateConstants(string.Empty), "lot_no"), Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.LAST_RECEIPT_DATE"), OOQL.CreateConstants(OrmDataOption.EmptyDateTime.Date), "first_storage_date"), Formulas.IsNull(OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY"), OOQL.CreateConstants(0), "inventory_qty"), Formulas.IsNull(Formulas.Ext("UNIT_CONVERT", new object[] { OOQL.CreateProperty("QuerySource.ITEM_ID") , OOQL.CreateProperty("QuerySource.STOCK_UNIT_ID") , OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") , OOQL.CreateProperty("QuerySource.UNIT_ID") , OOQL.CreateConstants(0) }), OOQL.CreateConstants(0), "conversion_qty"), Formulas.RowNumber("sort_no", OOQL.Over(new QueryProperty[] { OOQL.CreateProperty("QuerySource.ITEM_CODE") }, orderByItem )) }); node = OOQL.Select( pubProperties ) .From(node, "QuerySource") .LeftJoin("ITEM_WAREHOUSE_BIN") .On(OOQL.CreateProperty("QuerySource.ITEM_ID") == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.ITEM_ID") & Formulas.IsNull(OOQL.CreateProperty("QuerySource.ITEM_FEATURE_ID"), OOQL.CreateConstants(Maths.GuidDefaultValue())) == OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.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") //20170719 add by shenbao for P001-170717001 .On(OOQL.CreateProperty("WAREHOUSE.Owner_Org.ROid") == OOQL.CreateProperty("PLANT.PLANT_ID")) .Where(((OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_CODE") == OOQL.CreateConstants(warehouse_no)) | OOQL.CreateConstants(warehouse_no) == OOQL.CreateConstants("")) & OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.INVENTORY_QTY") > OOQL.CreateConstants(0) & OOQL.CreateProperty("ITEM_WAREHOUSE_BIN.BO_ID.RTK") == OOQL.CreateConstants("OTHER") & OOQL.CreateProperty("WAREHOUSE.WAREHOUSE_PROPERTY") == OOQL.CreateConstants("1") //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("WAREHOUSE.INCLUDED_AVAILABLE_QTY") == OOQL.CreateConstants(true) //20170717 add by shenbao for P001-170717001 & OOQL.CreateProperty("PLANT.PLANT_CODE") == OOQL.CreateConstants(site_no)); //20170719 add by shenbao for P001-170717001 } return(node); }
/// <summary> /// 更新领退料单 /// </summary> /// <param name="employeeNo">扫描人员</param> /// <param name="scanType">扫描类型 1.有箱条码 2.无箱条码</param> /// <param name="reportDatetime">上传时间</param> /// <param name="pickingDepartmentNo">领料部门</param> /// <param name="recommendedOperations">建议执行作业</param> /// <param name="recommendedFunction">A.新增 S.过帐</param> /// <param name="scanDocNo">扫描单号</param> /// <param name="collection">接口传入的领料单单身数据集合</param> public DependencyObjectCollection UpdateIssueReceipt(string employeeNo, string scanType, DateTime reportDatetime, string pickingDepartmentNo , string recommendedOperations, string recommendedFunction, string scanDocNo, DependencyObjectCollection collection) { DependencyObjectCollection rtnColl = CreateReturnCollection(); #region 参数检查 if (Maths.IsEmpty(recommendedOperations)) { throw new BusinessRuleException(EncodeSrv.GetMessage("A111201", new object[] { "recommended_operations" })); } if (Maths.IsEmpty(recommendedFunction)) { throw new BusinessRuleException(EncodeSrv.GetMessage("A111201", new object[] { "recommended_function" })); } #endregion //创建临时表需要的DataTable和Mapping信息 DataTable issueReceiptD = null; List <BulkCopyColumnMapping> issueReceiptDMap = null; this.CreateRelateTable(ref issueReceiptD, ref issueReceiptDMap); List <string> docNos = new List <string>(); //记录单身的单号,保存时需要,可以避免后续再查 //组织数据BulkCopy需要的DataTable数据 InsertDataTable(issueReceiptD, collection, docNos); #region 更新逻辑 using (ITransactionService trans = this.GetService <ITransactionService>()) { IQueryService querySrv = this.GetService <IQueryService>(); //新增临时表 IDataEntityType issueReceiptDTmp = CreateIssueReceiptDTmpTable(querySrv); //批量新增到临时表 querySrv.BulkCopy(issueReceiptD, issueReceiptDTmp.Name, issueReceiptDMap.ToArray()); if (issueReceiptD.Rows.Count <= 00 || issueReceiptD.Rows.Count <= 0) //没有数据值不再往下执行 { return(rtnColl); } DataRow[] drs = issueReceiptD.Select("BARCODE_NO<>\'\'"); //利用临时表批量更新相关数据 //20170905 add by wangyq for P001-170717001 =================begin=================== if (recommendedOperations == "7-5") { UpdateBcCheckStatus(querySrv, issueReceiptDTmp); } else { //20170905 add by wangyq for P001-170717001 =================end=================== UpdateIssueReceiptD(querySrv, issueReceiptDTmp); }//20170905 add by wangyq for P001-170717001 if (drs.Length > 0) //条码不为""的记录大于0时,才执行如下更新,避免多余的性能损失 { bool bcLintFlag = UtilsClass.IsBCLineManagement(querySrv); if (bcLintFlag) { DeleteBCLine(querySrv, issueReceiptDTmp); //先删除 InsertBCLine(querySrv, issueReceiptDTmp); //再重新生成 } } //保存 DependencyObjectCollection ids = GetIssueReceipt(docNos); IReadService readSrv = this.GetService <IReadService>("ISSUE_RECEIPT"); object[] entities = readSrv.Read(ids.Select(c => c["ISSUE_RECEIPT_ID"]).ToArray()); if (entities != null && entities.Length > 0) { ISaveService saveSrv = this.GetService <ISaveService>("ISSUE_RECEIPT"); saveSrv.Save(entities); } //保存时没有自动审核的,需要重新审核 entities = readSrv.Read(ids.Where(c => !c["AUTO_APPROVE"].ToBoolean()).Select(c => c["ISSUE_RECEIPT_ID"]).ToArray()); IConfirmService confirmService = this.GetService <IConfirmService>("ISSUE_RECEIPT"); ILogOnService logOnSrv = this.GetService <ILogOnService>(); foreach (DependencyObject obj in entities) { ConfirmContext context = new ConfirmContext(obj.Oid, logOnSrv.CurrentUserId, reportDatetime.ToDate()); confirmService.Execute(context); } trans.Complete(); } #endregion #region 组织返回结果 foreach (string item in docNos) { DependencyObject obj = rtnColl.AddNew(); obj["doc_no"] = item; } #endregion return(rtnColl); }
public DependencyObjectCollection UpdateSalesIssue(string employeeNo, string scanType, DateTime reportDatetime, string pickingDepartmentNo , string recommendedOperations, string recommendedFunction, string scanDocNo, DependencyObjectCollection collection) { DependencyObjectCollection rtnColl = CreateReturnCollection(); #region 参数检查 IInfoEncodeContainer encodeSrv = this.GetService <IInfoEncodeContainer>(); if (Maths.IsEmpty(recommendedOperations)) { throw new BusinessRuleException(encodeSrv.GetMessage("A111201", new object[] { "recommended_operations" })); } if (Maths.IsEmpty(recommendedFunction)) { throw new BusinessRuleException(encodeSrv.GetMessage("A111201", new object[] { "recommended_function" })); } #endregion //创建临时表需要的DataTable和Mapping信息 DataTable dtEntityD = new DataTable(); List <BulkCopyColumnMapping> entityDMap = new List <BulkCopyColumnMapping>(); CreateRelateTable(dtEntityD, entityDMap); List <string> docNos = new List <string>(); //记录单身的单号,保存时需要,可以避免后续再查 //组织数据BulkCopy需要的DataTable数据 InsertDataTable(dtEntityD, collection, docNos); if (dtEntityD.Rows.Count <= 0) { return(rtnColl); } #region 更新逻辑 using (ITransactionService trans = this.GetService <ITransactionService>()) { using (IConnectionService connService = this.GetService <IConnectionService>()) { IQueryService querySrv = this.GetService <IQueryService>(); //新增临时表 IDataEntityType dtTemp = CreateDTmpTable(querySrv); //批量新增到临时表 querySrv.BulkCopy(dtEntityD, dtTemp.Name, entityDMap.ToArray()); //利用临时表批量更新相关数据 UpdateSalesIssueD(querySrv, dtTemp); DataRow[] drs = dtEntityD.Select("BARCODE_NO<>\'\'"); if (drs.Length > 0) //条码不为""的记录大于0时,才执行如下更新,避免多余的性能损失 { ICreateService createService = this.GetService <ICreateService>("PARA_FIL"); if (createService != null) //PARA_FIL实体存在,服务端可以这么判断 { bool bcLintFlag = UtilsClass.IsBCLineManagement(querySrv); if (bcLintFlag) { DeleteBCLine(querySrv, dtTemp); //先删除 InsertBCLine(querySrv, dtTemp); //再重新生成 } } } } //保存 DependencyObjectCollection ids = GetSalesIssue(docNos); IReadService readSrv = this.GetService <IReadService>("SALES_ISSUE"); object[] entities = readSrv.Read(ids.Select(c => c["SALES_ISSUE_ID"]).ToArray()); if (entities != null && entities.Length > 0) { ISaveService saveSrv = this.GetService <ISaveService>("SALES_ISSUE"); saveSrv.Save(entities); } //保存时没有自动审核的,需要重新审核 entities = readSrv.Read(ids.Where(c => !c["AUTO_APPROVE"].ToBoolean()).Select(c => c["SALES_ISSUE_ID"]).ToArray()); IConfirmService confirmService = this.GetService <IConfirmService>("SALES_ISSUE"); ILogOnService logOnSrv = this.GetService <ILogOnService>(); foreach (DependencyObject obj in entities) { ConfirmContext context = new ConfirmContext(obj.Oid, logOnSrv.CurrentUserId, reportDatetime.ToDate()); confirmService.Execute(context); } trans.Complete(); } #endregion #region 组织返回结果 foreach (string item in docNos) { DependencyObject obj = rtnColl.AddNew(); obj["doc_no"] = item; } #endregion return(rtnColl); }
/// <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); }