/// <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 <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(); })); }
private List <StockBodyModel> FindStoBodyBy(string sqlWhere) { return(ErpDbAccessHelper.FindDataBy <StockBodyModel>(GetStoBodySqlFields(), sqlWhere, (dr, m) => { this.MapStoBodyRowAndModel(dr, m); })); }
/// <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 <PurchaseBodyModel> FindPurBodyBy(string sqlWhere) { return(ErpDbAccessHelper.FindDataBy <PurchaseBodyModel>(GetPurBodySqlFields(), sqlWhere, (dr, m) => { this.MapPurBodyRowAndModel(dr, m); })); }
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="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()); }
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="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="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(); })); }
/// <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="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()); }