/// <summary> /// 获取库存信息 /// </summary> /// <param name="manufacturersCodeList">商品编码数据</param> /// <param name="pageSize">每页获取条数</param> /// <param name="pageNum">获取页码</param> /// <returns></returns> public List <ErpProductList> GetXMProduct(List <string> manufacturersCodeList, TotalHeader header) { var sqlWhere = string.Join("','", manufacturersCodeList); var sqlProduct = string.Format(@"select a.PlatformMerchantCode,a.Saleprice,b.ProductName,b.ProductUnit,b.ProductWeight from dbo.XM_ProductDetails a join dbo.XM_Product b on a.ProductId = b.Id where a.PlatformMerchantCode in ('{0}') and b.IsEnable = 0 and a.IsEnable = 0", sqlWhere); //查询语句 var sqlComProduct = string.Format(@"select a.PlatformMerchantCode,a.Saleprice,b.ProductName,b.ProductWeight from dbo.XM_CombinationDetails a join dbo.XM_Combination b on a.ProductId = b.ID where a.PlatformMerchantCode in ('{0}') and b.IsEnabled = 0 and a.IsEnable = 0", sqlWhere); //查询语句 var dtProduct = SqlDataHelper.GetDatatableBySql(sqlProduct); var dtComProduct = SqlDataHelper.GetDatatableBySql(sqlComProduct); var dtProductList = CurUtilManager.ToEntity <ErpProductList>(dtProduct); var dtComProductList = CurUtilManager.ToEntity <ErpProductList>(dtComProduct); dtComProductList.AsParallel().ForAll(m => { GetProductChild(m); }); var ErpProductList = dtProductList.Concat(dtComProductList); #region 判断传入的商品编号是否都找得到对应的商品信息 var ErpPlatformMerchantCodeList = ErpProductList.Select(m => m.PlatformMerchantCode).ToList(); var ExceptList = manufacturersCodeList.Except(ErpPlatformMerchantCodeList).ToList(); if (ExceptList.Count > 0) { header.IsSuccess = false; header.Message = string.Format("这些商品编号{0}在ERP中无法找到对应的商品信息", string.Join(",", ExceptList)); } #endregion return(ErpProductList.ToList()); }
/// <summary> /// 获取库存信息 /// </summary> /// <param name="pageSize">每页获取条数</param> /// <param name="pageNum">获取页码</param> /// <returns></returns> public List <ErpProductList> GetXMProduct(int pageSize, int pageNum, ref int total) { try { // var sql = string.Format(@"select top {0} a.Id,b.ManufacturersCode,a.Saleprice,b.ProductName,b.ProductUnit,b.Specifications //from dbo.XM_ProductDetails a join dbo.XM_Product b on a.ProductId = b.Id //where a.Id not in //(select top {1} c.Id from dbo.XM_ProductDetails c join dbo.XM_Product d on c.ProductId = d.Id order by c.Id) //order by a.Id ", pageSize, pageSize * (pageNum - 1));//插入主表的sql语句 var sqlProduct = @"select a.Id,a.PlatformMerchantCode,a.Saleprice,b.ProductName,b.ProductUnit,b.ProductWeight from dbo.XM_ProductDetails a join dbo.XM_Product b on a.ProductId = b.Id where b.IsEnable = 0 and a.IsEnable = 0"; //插入主表的sql语句 var sqlComProduct = @"select a.ID,a.PlatformMerchantCode,a.Saleprice,b.ProductName,b.ProductWeight from dbo.XM_CombinationDetails a join dbo.XM_Combination b on a.ProductId = b.ID where b.IsEnabled = 0 and a.IsEnable = 0"; //插入主表的sql语句 var dtProduct = SqlDataHelper.GetDatatableBySql(sqlProduct); var dtComProduct = SqlDataHelper.GetDatatableBySql(sqlComProduct); var dtProductList = CurUtilManager.ToEntity <ErpProductList>(dtProduct); var dtComProductList = CurUtilManager.ToEntity <ErpProductList>(dtComProduct); dtComProductList.AsParallel().ForAll(m => { GetProductChild(m); }); //foreach(var elem in dtComProductList) //{ // GetProductChild(elem); //} var ErpProductList = dtProductList.Concat(dtComProductList).OrderBy(m => m.PlatformMerchantCode); total = ErpProductList.Count(); return(ErpProductList.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList()); }catch (Exception ex) { return(null); } }
/// <summary> /// 获取五道项目对应的仓库 /// </summary> /// <returns></returns> public List <int> getWarehouseIds() { var sql = "select * from XM_WareHouses where isEnable = 0 and ParentID = 0 and ProjectId = 1";//五道数据库中是1 //var sql = "select Id from XM_WareHouses where isEnable = 0 and ParentID = 0 and ProjectId = 21"; var dt = SqlDataHelper.GetDatatableBySql(sql); var ErpWarehouse = CurUtilManager.ToEntity <ErpWarehouseId>(dt); var ErpWarehouseIdList = ErpWarehouse.Select(m => m.Id).ToList(); return(ErpWarehouseIdList); }
/// <summary> /// 获取库存信息 /// </summary> /// <param name="pageSize">每页获取条数</param> /// <param name="pageNum">获取页码</param> /// <returns></returns> public List <ErpInventoryList> GetXLMInventory(int pageSize, int pageNum, ref int totalCount) { try { DateTime date = DateTime.Parse(DateTime.Now.ToShortDateString()); var ids = getWarehouseIds(); var sqlwehereids = string.Join(",", ids); #region var sql = string.Format(@"select top {0} * from( (select c.PlatformMerchantCode,b.ProductName,a.BarCode,b.ProductUnit as Unit,a.Inventory from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where a.CreateDate > '{1}' and b.IsEnable = 0 and c.IsEnable = 0 ) union ( select c.PlatformMerchantCode,b.ProductName,null as BarCode, b.ProductUnit as Unit,a.CanOrderCount as Inventory from XM_InventoryInfo a join XM_Product b on a.PlatformMerchantCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where b.IsEnable = 0 and c.IsEnable = 0 and a.WfId in ({3}) and c.PlatformMerchantCode not in( select c.PlatformMerchantCode from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where b.IsEnable = 0 and c.IsEnable = 0 ) )) as tb where tb.PlatformMerchantCode not in ( select top {2} * from ( (select c.PlatformMerchantCode from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where a.CreateDate > '{1}' and b.IsEnable = 0 and c.IsEnable = 0 ) union ( select c.PlatformMerchantCode from XM_InventoryInfo a join XM_Product b on a.PlatformMerchantCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where b.IsEnable = 0 and c.IsEnable = 0 and a.WfId in ({3}) and c.PlatformMerchantCode not in( select c.PlatformMerchantCode from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where b.IsEnable = 0 and c.IsEnable = 0)) ) as tb1 order by tb1.PlatformMerchantCode ) order by tb.PlatformMerchantCode ", pageSize, date, pageSize * (pageNum - 1), sqlwehereids);//查询库存管理中的物品库存信息 var dt = SqlDataHelper.GetDatatableBySql(sql); var ErpInventoryList = CurUtilManager.ToEntity <ErpInventoryList>(dt); #endregion totalCount = ErpInventoryList.Distinct().Count(); return(ErpInventoryList.ToList()); } catch (Exception ex) { return(null); } }
/// <summary> /// 寻找商品表中的商品信息 /// </summary> public List <XMProductNew> GetXMProductListByPlatFormMerchantCode(string PlatFormMerchantCode, int PlatformTypeId) { var sql = string.Format(@"select a.Id,a.ProductId,a.PlatformTypeId,a.PlatformMerchantCode,a.ProductTypeId,a.PlatformInventory, a.strUrl,a.Images,a.Costprice,a.Saleprice,a.TCostprice,a.TDateTimeStart,a.TDateTimeEnd,a.IsMainPush, b.BrandTypeId, b.ProductName,b.ManufacturersCode,b.Specifications,b.ManufacturersInventory,b.WarningQuantity,b.ProductColors, b.ProductUnit,b.IsPremiums,b.IsEnable,b.CreateID,b.CreateDate,b.UpdateID,b.UpdateDate,a.TemporaryManufacturersCode from XM_ProductDetails a join XM_Product b on a.ProductId = b.Id where a.IsEnable = 0 and a.PlatformMerchantCode = '{0}' and a.PlatformTypeId = {1}", PlatFormMerchantCode, PlatformTypeId); //先去找对应平台没有则找通用平台 var dt = SqlDataHelper.GetDatatableBySql(sql); var resultArray = CurUtilManager.ToEntity <XMProductNew>(dt); return(resultArray.ToList()); }
/// <summary> ///寻找组合产品中对应的erp供应商产品 /// </summary> public List <XMProductNew> GetXMProductListByzuheCode(string PlatFormMerchantCode, int PlatformTypeId) { var sql = string.Format(@"select s.Id,a.Count,s.BrandTypeId,s.ProductName,s.ManufacturersCode,s.Specifications, s.ManufacturersInventory,s.WarningQuantity,s.ProductColors,s.ProductUnit,s.IsPremiums, s.IsEnable, s.CreateID, s.CreateDate, s.UpdateID,s.UpdateDate from XM_Product s join XM_Product_Combination a on a.ProductId = s.Id join XM_Combination b on a.CombinationID = b.ID join XM_CombinationDetails p on b.ID = p.ProductId where a.IsEnabled = 0 and s.IsEnable = 0 and b.IsEnabled = 0 and p.IsEnable = 0 and p.PlatformMerchantCode = '{0}' and p.PlatformTypeId = {1}", PlatFormMerchantCode, PlatformTypeId); var dt = SqlDataHelper.GetDatatableBySql(sql); var resultArray = CurUtilManager.ToEntity <XMProductNew>(dt); List <XMProductNew> list = new List <XMProductNew>(); if (resultArray.ToList() != null && resultArray.ToList().Count > 0) { foreach (XMProductNew info in resultArray.ToList()) { var sqldetail = string.Format("select * from XM_ProductDetails where ProductId = {0} and IsEnable = 0", info.Id); var detaildt = SqlDataHelper.GetDatatableBySql(sqldetail); var detail = CurUtilManager.ToEntity <XMProductDetails>(detaildt); var Detail = detail.Where(p => p.PlatformTypeId == PlatformTypeId).ToList(); if (Detail != null && Detail.Count() > 0) { XMProductDetails q = Detail[Detail.Count() - 1]; info.Id = q.Id; info.ProductId = q.ProductId; info.PlatformTypeId = q.PlatformTypeId; info.PlatformMerchantCode = q.PlatformMerchantCode; info.ProductTypeId = q.ProductTypeId; info.PlatformInventory = q.PlatformInventory; info.strUrl = q.strUrl; info.Images = q.Images; info.Costprice = q.Costprice; info.Saleprice = q.Saleprice; info.TCostprice = q.TCostprice; info.TDateTimeStart = q.TDateTimeStart; info.TDateTimeEnd = q.TDateTimeEnd; info.IsMainPush = q.IsMainPush; info.TManufacturersCode = q.TemporaryManufacturersCode; list.Add(info); } } } return(list); }
/// <summary> /// 获取库存信息 /// </summary> /// <param name="manufacturersCodeList">商品编码数据</param> /// <param name="pageSize">每页获取条数</param> /// <param name="pageNum">获取页码</param> /// <returns></returns> public List <ErpInventoryList> GetXLMInventory(List <string> manufacturersCodeList, TotalHeader header) { try { DateTime date = DateTime.Parse(DateTime.Now.ToShortDateString()); var sqlWhere = string.Join("','", manufacturersCodeList); var sql = string.Format(@"select c.PlatformMerchantCode,b.ProductName,a.BarCode,b.ProductUnit as Unit,a.Inventory from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode join XM_ProductDetails c on c.ProductId = b.Id where c.PlatformMerchantCode in ('{0}') and b.IsEnable = 0 and c.IsEnable = 0 and a.CreateDate > '{1}'", sqlWhere, date.ToString());//从喜临门当日库存获取信息的sql语句 var dt = SqlDataHelper.GetDatatableBySql(sql); var ErpInventoryList = CurUtilManager.ToEntity <ErpInventoryList>(dt); #region 如果喜临门当日库存中没有这些商品库存,就到库存管理中寻找 var ids = getWarehouseIds(); var sqlwehereids = string.Join(",", ids); var sql1 = string.Format(@" select c.PlatformMerchantCode,b.ProductName,null as BarCode,b.ProductUnit as Unit,a.CanOrderCount as Inventory from XM_InventoryInfo a join XM_Product b on a.PlatformMerchantCode = b.ManufacturersCode join XM_ProductDetails c on c.ProductId = b.Id where c.PlatformMerchantCode in ('{0}') and c.PlatformMerchantCode and a.WfId in ({1}) not in( select c.PlatformMerchantCode from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode left join XM_ProductDetails c on c.ProductId = b.Id where b.IsEnable = 0 and c.IsEnable = 0) and b.IsEnable = 0 and c.IsEnable = 0", sqlWhere, sqlwehereids);//从库存管理获取信息的sql语句 var dt1 = SqlDataHelper.GetDatatableBySql(sql1); var ErpInventoryList1 = CurUtilManager.ToEntity <ErpInventoryList>(dt1); #endregion var ErpInventoryAllList = ErpInventoryList.Concat(ErpInventoryList1).Distinct().ToList(); #region 判断传入的商品编号是否都找得到对应的商品信息 var ErpPlatformMerchantCodeList = ErpInventoryAllList.Select(m => m.PlatformMerchantCode).ToList(); var ExceptList = manufacturersCodeList.Except(ErpPlatformMerchantCodeList).ToList(); if (ExceptList.Count > 0) { header.IsSuccess = false; header.Message = string.Format("这些商品编号{0}在ERP中无法找到对应的库存信息", string.Join(",", ExceptList)); } #endregion return(ErpInventoryList.Concat(ErpInventoryList1).Distinct().ToList()); } catch (Exception ex) { return(null); } }
/// <summary> /// 获取库存信息 /// </summary> /// <param name="orderCodeList">订单编号数组</param> /// <returns></returns> public List <ErpOrderStatusList> GetXMOrderStatus(List <RequestOrderStatusList> orderStatusList, Header header) { var baseProductbag = new System.Collections.Concurrent.ConcurrentBag <RequestOrderStatusList>(); var baseProductDictionary = new System.Collections.Concurrent.ConcurrentDictionary <string, List <string> >();//存储每个订单号和五道编码对应的拆分后的erp商品编码 #region 如果传回的商品编号中有组合商品的商品编号,则将组合商品拆分为基础商品数组 orderStatusList.AsParallel().ForAll(m => { setProductNum(m, baseProductbag, baseProductDictionary); }); #endregion var MerchantCodeList = baseProductbag.Select(m => m.PlatformMerchantCode).ToList(); var OrderCodeList = baseProductbag.Select(m => m.OrderCode).ToList(); var sqlMerchantCodeList = string.Join("','", MerchantCodeList); var sqlOrderNoList = string.Join("','", OrderCodeList); var sql = string.Format(@"select a.PlatformMerchantCode,a.OrderNo as OrderCode,b.IsDelivery,c.LogisticsName,b.LogisticsNumber from XM_Delivery_Details a join XM_Delivery b on b.Id = a.DeliveryId left join XM_CompanyCustom c on b.LogisticsId = c.LogisticsId where a.PlatformMerchantCode in ('{0}') and a.OrderNo in ('{1}') and b.IsEnabled = 0", sqlMerchantCodeList, sqlOrderNoList);//插入主表的sql语句 var dt = SqlDataHelper.GetDatatableBySql(sql); var ErpOrderStatusList = CurUtilManager.ToEntity <ErpOrderStatusList>(dt); var returnEntity = returnOrderStatus(orderStatusList, baseProductDictionary, ErpOrderStatusList.ToList()); #region 判断传入的订单号和商品编号是否都找到对应的订单信息 var returnOrderList = returnEntity.Select(m => new { Code = m.OrderCode + "_" + m.PlatformMerchantCode }).Select(m => m.Code).ToList(); var returnorderList = orderStatusList.Select(m => new { Code = m.OrderCode + "_" + m.PlatformMerchantCode }).Select(m => m.Code).ToList(); var ExceptList = returnorderList.Except(returnOrderList).ToList(); if (ExceptList.Count > 0) { header.IsSuccess = false; header.Message = string.Format("这些订单号和商品编号组合{0}在ERP中无法找到对应的订单状态信息", string.Join(",", ExceptList)); } #endregion return(returnEntity); }
public string CancelOrder(List <string> orderCodeList) { var errorstr = string.Empty; try { var sqlOrderCodeList = string.Join("','", orderCodeList); var sql = string.Format(@"select ID, OrderCode , IsAudit from XM_OrderInfo where OrderCode in ('{0}')", sqlOrderCodeList); var dt = SqlDataHelper.GetDatatableBySql(sql); var ErpErpOrderInfoList = CurUtilManager.ToEntity <ErpOrderInfo>(dt); #region 判断是否所有传入的订单号都找得到对应的订单信息 var ErpErpOrderCodeList = ErpErpOrderInfoList.Select(m => m.OrderCode).ToList(); var ExceptList = orderCodeList.Except(ErpErpOrderCodeList).ToList(); if (ExceptList.Count > 0) { return(string.Format("这些订单号{0}在ERP中无法找到对应的订单信息", string.Join(",", ExceptList))); } #endregion var notdeleteList = ErpErpOrderInfoList.Where(m => m.IsIsAudit == true).Select(m => m.OrderCode).ToList(); if (notdeleteList.Count > 0) { return(string.Format("这些订单号{0}已经审核无法取消", string.Join(",", notdeleteList))); } else { var deleteIDList = ErpErpOrderInfoList.Select(m => m.ID).ToList(); var sqlwhereids = string.Join(",", deleteIDList); var deleteSql = string.Format(@"delete from XM_OrderInfoProductDetails where OrderInfoID in ('{0}') delete from XM_OrderInfo where ID in ('{0}')", sqlwhereids); SqlDataHelper.ExcuteBySql(deleteSql); } }catch (Exception ex) { InsertLog.Insert("取消订单接口报错:" + ex.Message, ex); } return(errorstr); }