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