private List <CopReturnOrderModel> FindBodyReturnOrderBy(string category, string code) { string sqlBodyWhere = string.Format(" where TJ001='{0}' and TJ002='{1}'", category, code); string sqlHeadWhere = string.Format("SELECT COPTI.TI004 AS 客户编号, COPMA.MA002 AS 客户简单 FROM COPTI INNER JOIN COPMA ON COPTI.TI004 = COPMA.MA001 where COPTI.TI001='{0}' and COPTI.TI002='{1}'", category, code); string customerId = string.Empty; string CustomerName = string.Empty; DataTable dt = DbHelper.Erp.LoadTable(sqlHeadWhere); if (dt != null && dt.Rows.Count > 0) { customerId = dt.Rows[0]["客户编号"].ToString().Trim(); CustomerName = dt.Rows[0]["客户简单"].ToString().Trim(); } return(ErpDbAccessHelper.FindDataBy <CopReturnOrderModel>(GetStoBodySqlFields, sqlBodyWhere, (dr, m) => { m.OrderId = category + "-" + code; m.OrderDesc = dr["序号"].ToString(); m.CustomerId = customerId; m.CustomerShortName = CustomerName; m.ProductID = dr["品号"].ToString().Trim(); m.ProductName = dr["品名"].ToString().Trim(); m.ProductSpecify = dr["规格"].ToString().Trim(); m.ProductNumber = dr["数量"].ToString().Trim().ToDouble(); m.ProductUnit = dr["单位"].ToString().Trim(); })); }
/// <summary> /// 根据查询条件获取请购单单头数据信息 /// </summary> /// <param name="sqlWhere"></param> /// <returns></returns> private List <RequisitionHeaderModel> FindReqHeaderBy(string sqlWhere) { return(ErpDbAccessHelper.FindDataBy <RequisitionHeaderModel>(GetReqHeaderSqlFields(), sqlWhere, (dr, m) => { this.MapReqHeaderRowAndModel(dr, m); })); }
private List <StockBodyModel> FindStoBodyBy(string sqlWhere) { return(ErpDbAccessHelper.FindDataBy <StockBodyModel>(GetStoBodySqlFields(), sqlWhere, (dr, m) => { this.MapStoBodyRowAndModel(dr, m); })); }
/// <summary> /// 供应商最近二次采购信息 /// </summary> /// <param name="suppplierId"></param> /// <returns></returns> public List <PurchaseHeaderModel> FindSupplierLatestTwoPurchaseBy(string suppplierId) { List <PurchaseHeaderModel> findSupplierLatestTwoPurchase = new List <PurchaseHeaderModel>(); string sqlFields = "Select TOP (1) TC001,TC002,TC003,TC004,TC011,TC019 from PURTC "; string whereSql = string.Format("WHERE (TC004 = '{0}') ORDER BY TC003 DESC ", suppplierId); var lastestPruchase = ErpDbAccessHelper.FindDataBy <PurchaseHeaderModel>(sqlFields, whereSql, (dr, m) => { this.MapPurHeaderRowAndModel(dr, m); }).FirstOrDefault(); if (lastestPruchase == null) { return(findSupplierLatestTwoPurchase); } findSupplierLatestTwoPurchase.Add(lastestPruchase); string notmonth = lastestPruchase.PurchaseDate.Substring(0, lastestPruchase.PurchaseDate.Length - 2); string whereSql2 = string.Format("WHERE (TC004 = '{0}') AND (NOT (TC003 LIKE '{1}%')) ORDER BY TC003 DESC ", suppplierId, notmonth); var firstPruchase = ErpDbAccessHelper.FindDataBy <PurchaseHeaderModel>(sqlFields, whereSql2, (dr, m) => { this.MapPurHeaderRowAndModel(dr, m); }).FirstOrDefault(); if (firstPruchase == null) { findSupplierLatestTwoPurchase.Add(lastestPruchase); } else { findSupplierLatestTwoPurchase.Add(firstPruchase); } return(findSupplierLatestTwoPurchase); }
private List <PurchaseBodyModel> FindPurBodyBy(string sqlWhere) { return(ErpDbAccessHelper.FindDataBy <PurchaseBodyModel>(GetPurBodySqlFields(), sqlWhere, (dr, m) => { this.MapPurBodyRowAndModel(dr, m); })); }
/// <summary> /// 根据采购部门,起止日期获取请购单单头数据信息 /// </summary> /// <param name="department"></param> /// <returns></returns> public List <MaterialHaaderModel> FindReqHeaderBy(string orderId) { IDModel idm = ErpDbAccessHelper.DecomposeID(orderId); string sqlWhere = string.Format(" where TA004='{0}' And TA003 ='{1}'", idm.Category, idm.Code); return(FindHeaderBy(sqlWhere)); }
private List <StockHeaderModel> FindStoHeaderBy(string code, string category) { string sqlWhere = string.Format(" where TG001='{0}' and TG002='{1}'", category, code); return(ErpDbAccessHelper.FindDataBy <StockHeaderModel>(GetStoHeaderSqlFields(), sqlWhere, (dr, m) => { this.MapStoHeaderRowAndModel(dr, m); })); }
/// <summary> /// 替代料件 /// </summary> /// <param name="productId"></param> /// <returns></returns> private List <AgentMaterilModel> GetAgentMaterialFormERP_BOMMB_BY(string productId) { string sqlFields = "SELECT distinct MB004 FROM BOMMB "; string sqlwhere = string.Format("WHERE MB001 = '{0}' AND (MB007 = '' or MB007 >= '{1}')", productId, DateTime.Now.Date.ToString("yyyyMMdd")); var ListModels = ErpDbAccessHelper.FindDataBy <AgentMaterilModel>(sqlFields, sqlwhere, (dr, m) => { m.MatreialID = productId; m.AgentMaterialId = dr["MB004"].ToString().Trim(); }); return(ListModels); }
/// <summary> /// 由物料料号得到物料所有相关信息 /// </summary> /// <param name="marteial">料号</param> /// <returns></returns> public List <ProductMaterailDto> GetProductInfoBy(string marteial) { if (marteial == null || marteial == string.Empty) { return(new List <ProductMaterailDto>()); } string sqlWhere = string.Format(" where MB001='{0}'", marteial.Trim()); return(ErpDbAccessHelper.FindDataBy <ProductMaterailDto>(GetPorductSqlFields(), sqlWhere, (dr, m) => { this.MapProductRowAndModel(dr, m); })); }
/// <summary> /// 获取工单物料列表 /// </summary> /// <param name="orderId"></param> /// <returns></returns> public List <OrderMaterialModel> GetOrderMaterialListBy(string orderId) { var idm = ErpDbAccessHelper.DecomposeID(orderId); string sqlWhere = string.Format(" where TB001='{0}' and TB002='{1}' ", idm.Category, idm.Code); return(ErpDbAccessHelper.FindDataBy <OrderMaterialModel>(SqlFields, sqlWhere, (dr, m) => { m.MaterialId = dr["材料品号"].ToString().Trim(); m.MaterialName = dr["品名"].ToString().Trim(); m.MaterialSpecify = dr["规格"].ToString().Trim(); m.Unit = dr["单位"].ToString().Trim(); m.ReceiveCount = dr["需领用量"].ToString().Trim().ToDouble(); })); }
/// <summary> /// Sql 品号 品名 规格 属性 单位 /// </summary> /// <param name="productId">产品料号</param> /// <returns></returns> public MarterialBaseInfo GetBomFormERP_INVMB_By(string productId) { string SqlFields = "SELECT MB001 as 品号,MB002 as 品名, MB003 as 规格, MB025 as 属性,MB004 as 单位 FROM INVMB"; string sqlWhere = string.Format(" where MB001='{0}'", productId); var ListModels = ErpDbAccessHelper.FindDataBy <MarterialBaseInfo>(SqlFields, sqlWhere, (dr, m) => { m.MaterialId = dr["品号"].ToString().Trim(); m.MaterialName = dr["品名"].ToString().Trim(); m.MaterialSpecify = dr["规格"].ToString().Trim(); m.Property = ConvertProperty(dr["属性"].ToString().Trim()); m.Unit = dr["单位"].ToString().Trim(); }); return(ListModels.FirstOrDefault()); }
/// <summary> /// 获取工单详情 主物料 品号 底数 组成用量 /// </summary> /// <param name="orderID"></param> /// <returns></returns> private List <BomMaterialModel> GetBomFormERP_BOMMD_By(string productId, int grade) { string SqlFields = "Select MD003 as 组料品号,MD007 as 底数,MD006 as 组成用量 from BOMMD"; string sqlWhere = string.Format(" where MD001='{0}' and MD012=''", productId); var ListModels = ErpDbAccessHelper.FindDataBy <BomMaterialModel>(SqlFields, sqlWhere, (dr, m) => { m.MainMaterialId = productId; m.MaterialId = dr["组料品号"].ToString().Trim(); m.MaterialIdInfo = GetBomFormERP_INVMB_By(dr["组料品号"].ToString().Trim()); m.Grade = ConvertGrade(grade); m.BaseNumber = dr["底数"].ToString().Trim().ToDouble(); m.NeedNumber = dr["组成用量"].ToString().Trim().ToDouble(); }); return(ListModels); }
/// <summary> /// 查找替代料件属性 /// </summary> /// <param name="mainMaterial">主物料</param> /// <param name="productId">分物料</param> /// <param name="agentproductId">替代料号</param> /// <returns></returns> private BomMaterialModel GetAgentBomFormERP_BOMMD_By(string mainMaterial, string productId, string agentproductId) { string SqlFields = "Select MD001 AS 主料件, MD003 as 组料品号,MD007 as 底数,MD006 as 组成用量 from BOMMD"; string sqlWhere = string.Format(" where MD001='{0}' and MD003='{1}' and MD012=''", mainMaterial, productId); var ListModels = ErpDbAccessHelper.FindDataBy <BomMaterialModel>(SqlFields, sqlWhere, (dr, m) => { m.MainMaterialId = mainMaterial; m.MaterialId = agentproductId; m.MaterialIdInfo = GetBomFormERP_INVMB_By(agentproductId.Trim()); m.Grade = "替代料件"; m.BaseNumber = dr["底数"].ToString().Trim().ToDouble(); m.NeedNumber = dr["组成用量"].ToString().Trim().ToDouble(); }); return(ListModels.FirstOrDefault()); }
private void MapPurBodyRowAndModel(DataRow dr, PurchaseBodyModel m) { m.Code = dr["TD002"].ToString(); m.Category = dr["TD001"].ToString(); m.DeliveredCount = dr["TD015"].ToString().ToDouble(); m.ProductID = dr["TD004"].ToString(); m.ProductName = dr["TD005"].ToString(); m.ProductSpecify = dr["TD006"].ToString(); m.PurchaseAmmount = dr["TD011"].ToString(); m.PurchaseCount = dr["TD008"].ToString().ToDouble(); m.PurchaseUnit = dr["TD010"].ToString(); m.Warehouse = dr["TD007"].ToString(); m.InventoryCount = dr["TD019"].ToString().ToDouble(); m.PlanDeliverDate = dr["TD012"].ToString(); m.BuyingID = ErpDbAccessHelper.ComposeID(dr["TD026"].ToString().Trim(), dr["TD027"].ToString().Trim()); }
public List <FinishedProductStoreModel> GetProductStroeInfoBy(string productId) { string sqlWhere = string.Format(" where (MC001 = '{0}') ", productId); var marterialBaasInfo = MarterialBaasInfo(productId); return(ErpDbAccessHelper.FindDataBy <FinishedProductStoreModel>(SqlFields, sqlWhere, (dr, m) => { m.ProductID = dr["品号"].ToString().Trim(); m.ProductName = marterialBaasInfo.MaterialName; m.ProductSpecify = marterialBaasInfo.MaterialSpecify; m.PutInStoreDate = dr["人库日期"].ToString().ToDate(); m.StroeId = dr["仓位"].ToString().Trim(); m.InStroeNumber = dr["人库数量"].ToString().ToDouble(); m.More = dr["备注"].ToString().Trim(); })); }
/// <summary> /// 未完工的工单 /// </summary> /// <param name="ContainsProductName">产品型号或规格</param> /// <returns></returns> public List <OrderModel> GetUnfinishedOrderBy(string ContainsProductName) { string sqlWhere = string.Format(" WHERE (NOT (TA011 = 'Y' OR TA011 = 'y')) AND (TA034 LIKE '%{0}%')", ContainsProductName); return(ErpDbAccessHelper.FindDataBy <OrderModel>(SqlFields, sqlWhere, (dr, m) => { m.OrderId = string.Format("{0}-{1}", dr["TA001"].ToString().Trim(), dr["TA002"].ToString().Trim());; m.ProductID = dr["TA006"].ToString().Trim(); m.ProductName = dr["TA034"].ToString().Trim(); m.ProductSpecify = dr["TA035"].ToString().Trim(); m.OrderFinishStatus = OrderFinishStatusConverter(dr["TA011"].ToString().Trim()); m.Count = dr["TA015"].ToString().Trim().ToDouble(); m.InStoreCount = dr["TA017"].ToString().Trim().ToDouble(); m.OrderFinishDate = DateTime.ParseExact(dr["TA010"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture); m.InStockDate = DateTime.ParseExact(dr["TA063"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture); })); }
/// <summary> /// 未完工的业务订单 /// </summary> /// <param name="containsProductType">所包括品名</param> /// <returns></returns> public List <CopOrderModel> GetCopOrderBy(string containsProductType) { string sqlWhere = string.Format(" where (TD005 like'%{0}%' or TD006 LIKE '%{0}%')and (TD016 = 'N') ", containsProductType); return(ErpDbAccessHelper.FindDataBy <CopOrderModel>(SqlFields, sqlWhere, (dr, m) => { m.OrderId = string.Format("{0}-{1}", dr["单别"].ToString().Trim(), dr["单号"].ToString().Trim());; m.OrderDesc = dr["序号"].ToString().Trim(); m.ProductID = dr["品号"].ToString().Trim(); m.ProductName = containsProductType; m.ProductSpecify = dr["规格"].ToString().Trim(); m.WarehouseID = (dr["仓位号"].ToString().Trim()); m.ProductNumber = dr["计划产量"].ToString().Trim().ToDouble(); m.FinishNumber = dr["已交量"].ToString().Trim().ToDouble(); })); }
private void MapStoBodyRowAndModel(DataRow dr, StockBodyModel m) { m.Code = dr["TH002"].ToString(); m.Category = dr["TH001"].ToString(); m.Auditor = dr["TH038"].ToString(); m.ProductID = dr["TH004"].ToString(); m.ProductName = dr["TH005"].ToString(); m.ProductSpecify = dr["TH006"].ToString(); m.CheckCount = dr["TH015"].ToString().ToDouble(); m.CheckDate = dr["TH014"].ToString(); m.InventoryCount = dr["TH034"].ToString().ToDouble(); m.Warehouse = dr["TH009"].ToString(); m.Memo = dr["TH033"].ToString(); m.PurchaseID = ErpDbAccessHelper.ComposeID(dr["TH011"].ToString().Trim(), dr["TH012"].ToString().Trim()); m.StockAmount = dr["TH019"].ToString().ToDouble(); m.StockCount = Convert.ToInt64(dr["TH007"].ToString().ToDouble()); m.StockUnit = dr["TH018"].ToString().ToDouble(); }
/// <summary> /// 获取工单详情 /// </summary> /// <param name="orderID"></param> /// <returns></returns> public OrderModel GetOrderDetailsBy(string orderID) { var idm = ErpDbAccessHelper.DecomposeID(orderID); string sqlWhere = string.Format(" where TA001='{0}' and TA002='{1}'", idm.Category, idm.Code); var ListModels = ErpDbAccessHelper.FindDataBy <OrderModel>(SqlFields, sqlWhere, (dr, m) => { m.OrderId = string.Format("{0}-{1}", dr["TA001"].ToString().Trim(), dr["TA002"].ToString().Trim());; m.ProductID = dr["TA006"].ToString().Trim(); m.ProductName = dr["TA034"].ToString().Trim(); m.ProductSpecify = dr["TA035"].ToString().Trim(); m.OrderFinishStatus = OrderFinishStatusConverter(dr["TA011"].ToString().Trim()); m.Count = dr["TA015"].ToString().Trim().ToDouble(); m.InStoreCount = dr["TA017"].ToString().Trim().ToDouble(); m.OrderFinishDate = dr["TA010"].ToString().Trim().ToDate(); m.InStockDate = dr["TA063"].ToString().Trim().ToDate(); }); return(ListModels.FirstOrDefault()); }
/// <summary> /// 根据采购部门获取采购购单单头数据信息 /// </summary> /// <param name="department"></param> /// <returns></returns> public List <PurchaseHeaderModel> FindPurHeaderByDepartment(string department, DateTime dateFrom, DateTime dateTo) { List <PurchaseBodyModel> reqBodys = this.FindPurBodyByDepartment(department, dateFrom, dateTo); List <PurchaseHeaderModel> purHeaders = new List <PurchaseHeaderModel>(); if (reqBodys != null && reqBodys.Count > 0) { List <string> ids = new List <string>(); reqBodys.ForEach(rb => { string id = ErpDbAccessHelper.ComposeID(rb.Code, rb.Category); if (!ids.Contains(id)) { ids.Add(id); purHeaders.AddRange(FindPurHeaderBy(rb.Code, rb.Category)); } }); } return(purHeaders); }
/// <summary> /// 根据采购部门获取进货单单头数据信息 /// </summary> /// <param name="department"></param> /// <returns></returns> public List <StockHeaderModel> FindStoHeaderByDepartment(string department, DateTime dateFrom, DateTime dateTo) { List <StockBodyModel> stoBodys = this.FindStoBodyByDepartment(department, dateFrom, dateTo); List <StockHeaderModel> stoHeaders = new List <StockHeaderModel>(); if (stoBodys != null && stoBodys.Count > 0) { List <string> ids = new List <string>(); stoBodys.ForEach(sto => { string id = ErpDbAccessHelper.ComposeID(sto.Code, sto.Category); if (!ids.Contains(id)) { ids.Add(id); stoHeaders.AddRange(FindStoHeaderBy(sto.Code, sto.Category)); } }); } return(stoHeaders); }
/// <summary> /// 查找该部门的请购单单身信息 /// </summary> /// <param name="department"></param> /// <returns></returns> public List <PurchaseBodyModel> FindPurBodyByDepartment(string department, DateTime dateFrom, DateTime dateTo) { List <RequisitionHeaderModel> reqHeaders = this.reqDb.FindReqHeaderBy(department, dateFrom, dateTo); List <PurchaseBodyModel> purBodys = new List <PurchaseBodyModel>(); if (reqHeaders != null && reqHeaders.Count > 0) { List <string> ids = new List <string>(); reqHeaders.ForEach(rh => { if (!ids.Contains(rh.BuyingID)) { ids.Add(rh.BuyingID); IDModel idm = ErpDbAccessHelper.DecomposeID(rh.BuyingID); string sqlWhere = string.Format(" where TD026='{0}' and TD027='{1}'", idm.Category, idm.Code); purBodys.AddRange(FindPurBodyBy(sqlWhere)); } }); } return(purBodys); }
/// <summary> /// 获得供应商信息 /// </summary> /// <param name="SupplierId">供应商ID</param> /// <returns></returns> public SupplierModel FindSpupplierInfoBy(string SupplierId) { string whereSql = string.Format("where MA001='{0}'", SupplierId); var listModels = ErpDbAccessHelper.FindDataBy <SupplierModel>(SqlFields, whereSql, (dr, m) => { m.SupplierID = dr["MA001"].ToString().Trim(); m.SupplierShortName = dr["MA002"].ToString().Trim(); m.SupplierName = dr["MA003"].ToString().Trim(); m.Tel = dr["MA008"].ToString().Trim(); m.FaxNo = dr["MA010"].ToString().Trim(); m.Email = dr["MA011"].ToString().Trim(); m.Principal = dr["MA012"].ToString().Trim(); m.Contact = dr["MA013"].ToString().Trim(); m.Address = dr["MA014"].ToString().Trim(); m.PayCondition = dr["MA025"].ToString().Trim(); m.BillAddress = dr["MA051"].ToString().Trim(); m.IsCooperate = HandelIsConnparate(dr["MA004"].ToString().Trim()); }); return(listModels.FirstOrDefault()); }
/// <summary> /// 查找该部门的请购单单身信息 /// </summary> /// <param name="department"></param> /// <returns></returns> public List <StockBodyModel> FindStoBodyByDepartment(string department, DateTime dateFrom, DateTime dateTo) { List <PurchaseHeaderModel> reqHeaders = this.purDb.FindPurHeaderByDepartment(department, dateFrom, dateTo); List <StockBodyModel> stoBodys = new List <StockBodyModel>(); if (reqHeaders != null && reqHeaders.Count > 0) { List <string> Ids = new List <string>(); reqHeaders.ForEach(req => { if (!Ids.Contains(req.PurchaseID)) { Ids.Add(req.PurchaseID); IDModel idm = ErpDbAccessHelper.DecomposeID(req.PurchaseID); string sqlWhere = string.Format(" where TH011='{0}' and TH012='{1}'", idm.Category, idm.Code); stoBodys.AddRange(FindStoBodyBy(sqlWhere)); } }); } return(stoBodys); }
public List <CopReturnOrderModel> FindReturnOrderByID(string returnHandleOrderid) { var idm = ErpDbAccessHelper.DecomposeID(returnHandleOrderid); return(FindBodyReturnOrderBy(idm.Category, idm.Code)); }
public List <StockBodyModel> FindStoBodyByID(string id) { var idm = ErpDbAccessHelper.DecomposeID(id); return(FindStoBodyBy(idm.Code, idm.Category)); }
/// <summary> /// 由单子 得到单别 单号 (单别为 591 110 (341 342 343 344)和制令单) /// <param name="id">单号XXX-XXXXXXX</param> /// <returns></returns> public List <MaterialModel> FindMaterialBy(string id) { var idm = ErpDbAccessHelper.DecomposeID(id); return(GetMaterialIdBy(idm.Category, idm.Code)); }
/// <summary> /// 查询该请购单的单身信息 /// </summary> /// <param name="reqID"></param> /// <returns></returns> public List <RequisitionBodyModel> FindReqBodyByID(string reqID) { IDModel idm = ErpDbAccessHelper.DecomposeID(reqID); return(FindReqBodyBy(idm.Code, idm.Category)); }