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));
}
