/// <summary>
/// 更新格口与邮寄方式的关联
/// </summary>
/// <param name="latticesetting">格口信息</param>
/// <param name="posttypesList">邮寄方式</param>
internal void UpdateSolutionPostType(LatticeSetting latticesetting, List <Posttypes> posttypeslist)
{
try
{
var newData = (from pt in posttypeslist
select new SolutionPostType
{
Id = Guid.NewGuid().ToString(),
SortingSolutionId = _sortingSolution,
CabinetId = latticesetting.CabinetId,
LatticeSettingId = latticesetting.ID,
PostTypeId = pt.PostID,
PostTypeName = pt.CnPostName,
});
using (var db = new OrderSortingDBEntities())
{
var oldData = db.SolutionPostType.Where(sp => sp.LatticeSettingId == latticesetting.ID && sp.SortingSolutionId == _sortingSolution);
db.SolutionPostType.RemoveRange(oldData);
db.SolutionPostType.AddRange(newData);
db.SaveChanges();
}
}
catch (Exception)
{
throw;
}
finally
{
_solutionPostTypeList = GetSolutionPostTypeList();
}
}
/// <summary>
/// 创建装箱记录（operationType：1自动满格，2手动满格，3打印包牌号）
/// </summary>
/// <param name="lattice">柜格</param>
/// <param name="userInfo">用户信息</param>
/// <param name="boxWeight">箱子重量</param>
/// <param name="operationType">操作类型：1自动满格，2手动满格，3打印包牌号</param>
/// <returns></returns>
internal override PackingLog CreatePackingLog(LatticeSetting lattice, UserInfo userInfo, decimal boxWeight, out List <LatticeOrdersCache> LatticeOrdersCacheList, int operationType = 3)
{
Debug.WriteLine("CreatePackingLog begin 2");
using (var db = new OrderSortingDBEntities())
{
var logCache = db.LatticeOrdersCache.Where(o => o.LatticesettingId == lattice.ID);
if (logCache == null || logCache.Count() < 1)
{
LatticeOrdersCacheList = null;
return(null);
}
var logList = logCache.ToList();
LatticeOrdersCacheList = logList;
PackingLog pkgLog = NewPackingLog(lattice, userInfo, operationType, logList, boxWeight);
db.PackingLog.Add(pkgLog);
db.LatticeOrdersCache.RemoveRange(logList);
var response = API_Helper.BatchOutbound(userInfo, pkgLog, logList);
if (response != null && !response.Success && !string.IsNullOrWhiteSpace(response.Message))
{
throw new Exception(response.Message);
}
db.SaveChanges();
return(pkgLog);
}
}
/// <summary>
/// 获取所有国家地区信息
/// </summary>
/// <returns></returns>
internal static List <Countrys> GetCountrysList()
{
using (var db = new OrderSortingDBEntities())
{
return(db.Countrys.OrderBy(c => c.EnName).ToList());
}
}
/// <summary>
/// 更新格口与国家地区的关联
/// </summary>
/// <param name="latticesetting">格口信息</param>
/// <param name="countrysList">国家地区</param>
internal void UpdateSolutionCountry(LatticeSetting latticesetting, List <Countrys> countrysList)
{
try
{
var newData = (from pt in countrysList
select new SolutionCountry
{
Id = Guid.NewGuid().ToString(),
SortingSolutionId = _sortingSolution,
CabinetId = latticesetting.CabinetId,
LatticeSettingId = latticesetting.ID,
CountryId = pt.ID,
CountryName = pt.CnName
});
using (var db = new OrderSortingDBEntities())
{
var oldData = db.SolutionCountry.Where(sp => sp.LatticeSettingId == latticesetting.ID && sp.SortingSolutionId == _sortingSolution);
db.SolutionCountry.RemoveRange(oldData);
db.SolutionCountry.AddRange(newData);
db.SaveChanges();
}
}
catch (Exception)
{
throw;
}
finally
{
_solutionCountryList = GetSolutionCountryList();
}
}
/// <summary>
/// 查询打包记录
/// </summary>
internal List <PackingLog> GetPackingLogByPageSize(DateTime startDate, DateTime endDate, int pageIndex, int pageSize, ref int recordCount)
{
try
{
//分页查询，默认每页20条
string sql1 = @"SELECT *
FROM packinglog o
WHERE (OperationTime between datetime(@startDate) and datetime(@endDate))";
//计算总数
string sql2 = @"SELECT COUNT(o.ID) rowCount FROM packinglog o
WHERE (OperationTime between datetime(@startDate) and datetime(@endDate))";
//参数
var sqlParams = new SQLiteParameter[2]
{
new SQLiteParameter("@startDate", startDate.ToString("yyyy-MM-dd")),
new SQLiteParameter("@endDate", endDate.AddDays(1).ToString("yyyy-MM-dd"))
};
sql1 += string.Format(" Order By o.OperationTime Desc limit {0} offset {1} ",
pageSize, pageSize * (pageIndex - 1));
using (var db = new OrderSortingDBEntities())
{
recordCount = db.Database.SqlQuery <int>(sql2, sqlParams).First();
return(db.Database.SqlQuery <PackingLog>(sql1, sqlParams).ToList());
}
}
catch (Exception)
{
throw;
}
}
internal bool CreateErrorOrderSortingLog(List <ThreadSortOrder> ThreadSortOrderList, UserInfo userInfo, int operationType)
{
try
{
//记录分拣日志
using (var db = new OrderSortingDBEntities())
{
foreach (ThreadSortOrder entity in ThreadSortOrderList)
{
for (int i = 0; i < entity.TargetLattice.Count; i++)
{
OrderSortingLog sortingLog = NewOrderSortingLog(entity.OrderInfo, entity.TargetLattice[i], entity.ResultLattice, userInfo, operationType, 3);
db.OrderSortingLog.Add(sortingLog);
}
}
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
SaveErrLogHelper.SaveErrorLog(string.Empty, ex.ToString());
return(false);
}
}
/// <summary>
/// 根据订单号撤回分拣
/// </summary>
/// <param name="orderIdArra"></param>
/// <returns></returns>
internal string DeleteOrderCacheByOrderId(string[] orderIdArra)
{
try
{
using (var db = new OrderSortingDBEntities())
{
//验证
foreach (string orderID in orderIdArra)
{
if (orderID == "")
{
continue;
}
if (db.LatticeOrdersCache.Where(o => o.OrderId == orderID).Count() <= 0)
{
return($"撤回失败，未找到订单号:{orderID}，请核对订单号是否正确！");
}
}
var logCache = db.LatticeOrdersCache.Where(o => orderIdArra.Contains(o.OrderId));
db.LatticeOrdersCache.RemoveRange(logCache);
db.SaveChanges();
//重新统计分拣信息
LoadTotalSortingDataList(db);
}
return($"撤回成功！");
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 跟据渠道获取所有的区
/// </summary>
/// <returns></returns>
internal static List <PostArea> GetPostAreaList(string PostTypeId)
{
using (var db = new OrderSortingDBEntities())
{
return(db.PostArea.Where(o => o.PostTypeId == PostTypeId).OrderBy(pt => pt.Flag).ToList());
}
}
/// <summary>
/// 获取所有邮寄方式信息
/// </summary>
/// <returns></returns>
internal static List <Posttypes> GetPostTypesList()
{
using (var db = new OrderSortingDBEntities())
{
return(db.Posttypes.OrderBy(pt => pt.CnPostName).ToList());
}
}
/// <summary>
/// 获取所有分拣方案
/// </summary>
/// <returns></returns>
internal static List <SortingSolutions> GetSortingSolutionsList()
{
using (var db = new OrderSortingDBEntities())
{
return(db.SortingSolutions.ToList());
}
}
/// <summary>
/// 更新格口与邮寄方式地区的关联
/// </summary>
/// <param name="latticesetting"></param>
/// <param name="posttypeslist"></param>
internal void UpdateSolutionPostArea(LatticeSetting latticesetting, List <PostArea> postArealist, string PostTypeId)
{
try
{
var newData = (from pt in postArealist
select new SolutionPostArea
{
ID = Guid.NewGuid().ToString(),
SortingSolutionId = _sortingSolution,
Type = pt.Type,
Area = pt.Area,
Flag = pt.Flag,
PostTypeId = pt.PostTypeId,
LactticeSettingId = latticesetting.ID,
CabinetId = latticesetting.CabinetId
});
using (var db = new OrderSortingDBEntities())
{
var oldData = db.SolutionPostArea.Where(sp => sp.LactticeSettingId == latticesetting.ID && sp.CabinetId == latticesetting.CabinetId && sp.PostTypeId == PostTypeId && sp.SortingSolutionId == _sortingSolution);
db.SolutionPostArea.RemoveRange(oldData);
db.SolutionPostArea.AddRange(newData);
db.SaveChanges();
}
}
catch (Exception)
{
throw;
}
finally
{
_solutionPostAreaList = GetSolutionPostAreaList();
}
}
internal static string GetPostTypeName(string postID)
{
using (var db = new OrderSortingDBEntities())
{
var postIdArr = postID.Split(',');
var data = db.Posttypes.Where(o => postIdArr.Contains(o.PostID)).ToList();
return(string.Join(",", data.Select(o => o.CnPostName)));
}
}
/// <summary>
/// 更新邮寄方式
/// </summary>
internal static void UpdatePostTypes()
{
try
{
var systemSetting = GetSystemSetting();
using (var db = new OrderSortingDBEntities())
{
var now = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
var loginCount = db.LoginLog.Count(ll => ll.LoginTime > now);
if (loginCount > 0)
{
return;
}
//获取OA的全部邮寄方式
DataTable dt = null;
if (systemSetting.InterfaceType == InterfaceType.SigleFlyt)
{
var content = API_Helper.GetPostListBySingleFlyt();
if (content != null && content.Count > 1)
{
foreach (var row in content)
{
Posttypes post = new Posttypes();
post.PostID = row.Id;
post.CnPostName = row.Type;
post.EnPostCode = row.Id;
db.Posttypes.AddOrUpdate(post);
}
db.SaveChangesAsync();
}
}
else
{
dt = API_Helper.GetPostList();
if (dt != null && dt.Rows.Count > 1)
{
foreach (DataRow row in dt.Rows)
{
Posttypes post = new Posttypes();
post.PostID = row["id"].ToString();
post.CnPostName = row["type"].ToString();
post.EnPostCode = row["entype"].ToString();
db.Posttypes.AddOrUpdate(post);
}
db.SaveChangesAsync();
}
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 定时清理/迁移旧数据
/// </summary>
/// <param name="logStorageDays">1:一天；2:三天；3:一周；4:一个月；5:三个月；6:半年；7:一年；8:两年；9:五年</param>
internal static void ClearData(int logStorageDays)
{
//参数
oldTime = GetLogStorageDateTime(logStorageDays);
using (var db = new OrderSortingDBEntities())
{
ClearOldOrderSortingLog(db);
ClearOldPackingLog(db);
ClearOldOrderInfo(db);
db.SaveChangesAsync();
}
}
/// <summary>
/// 根据Id删除分拣方案
/// </summary>
/// <param name="id"></param>
internal static void DeleteSortingSolutionsById(string id)
{
using (var db = new OrderSortingDBEntities())
{
var ss = db.SortingSolutions.Find(id);
db.SortingSolutions.Remove(ss);
var spt = db.SolutionPostType.Where(sp => sp.SortingSolutionId == id);
db.SolutionPostType.RemoveRange(spt);
var sc = db.SolutionCountry.Where(sp => sp.SortingSolutionId == id);
db.SolutionCountry.RemoveRange(sc);
db.SaveChanges();
}
}
/// <summary>
/// 清除旧订单信息
/// </summary>
private static void ClearOldOrderInfo(OrderSortingDBEntities db)
{
var oldOrderInfo = db.OrderInfo.Where(osl => osl.CreateTime < oldTime).ToList();
if (oldOrderInfo.Count < 1)
{
return;
}
var oldOrderInfo2 = (from old in oldOrderInfo
select SetProperties <OrderInfo, OldOrderInfo>(old)).ToList();
db.OrderInfo.RemoveRange(oldOrderInfo);
db.OldOrderInfo.AddRange(oldOrderInfo2);
}
/// <summary>
/// 清除旧装箱记录
/// </summary>
private static void ClearOldPackingLog(OrderSortingDBEntities db)
{
var packingLog = db.PackingLog.Where(osl => osl.OperationTime < oldTime).ToList();
if (packingLog.Count < 1)
{
return;
}
var oldPackingLog = (from old in packingLog
select SetProperties <PackingLog, OldPackingLog>(old)).ToList();
db.PackingLog.RemoveRange(packingLog);
db.OldPackingLog.AddRange(oldPackingLog);
}
/// <summary>
/// 根据格口Id获取格口内的订单信息
/// </summary>
/// <param name="latticesettingId"></param>
/// <returns></returns>
internal List <LatticeOrdersCache> GetLatticeOrdersListByLatticesettingId(int latticesettingId)
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.LatticeOrdersCache.Where(lo => lo.LatticesettingId == latticesettingId).ToList());
}
}
catch (Exception)
{
throw;
}
}
private List <SolutionPostType> GetSolutionPostTypeList()
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.SolutionPostType.Where(ls => ls.SortingSolutionId == _sortingSolution && ls.CabinetId <= _cabinetNumber).ToList());
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取最新的一条分拣记录
/// </summary>
/// <returns></returns>
internal OrderSortingLog GetTheLastOrderSortingLog()
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.OrderSortingLog.OrderByDescending(o => o.OperationTime).FirstOrDefault());
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 判断格口号是否已存在
/// </summary>
/// <param name="latticeId">格口号</param>
internal bool IsLatticeIdExists(string latticeId)
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.LatticeSetting.Any(als => als.LatticeId == latticeId));
}
}
catch (Exception)
{
throw;
}
}
private List <SolutionPostArea> GetSolutionPostAreaList()
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.SolutionPostArea.Where(ls => ls.SortingSolutionId == _sortingSolution).ToList());
}
}
catch (Exception)
{
throw;
}
}
private List <SolutionZipType> GetCurSolutionZipType()
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.SolutionZipType.Where(o => o.SortingSolutionId == _sortingSolution).ToList());
}
}
catch (Exception)
{
throw;
}
}
private List <ZipType> GetzipTypeList()
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(db.ZipType.ToList());
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 重新统计分拣信息
/// </summary>
internal void LoadTotalSortingDataList()
{
try
{
using (var db = new OrderSortingDBEntities())
{
LoadTotalSortingDataList(db);
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 保存格口设置信息
/// </summary>
/// <param name="latticesetting"></param>
internal void SaveLatticeSetting(LatticeSetting latticesetting)
{
try
{
using (var db = new OrderSortingDBEntities())
{
db.LatticeSetting.AddOrUpdate(latticesetting);
db.SaveChanges();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 清除格口内快件的分拣记录
/// </summary>
internal void ClearLatticeOrdersCache()
{
try
{
using (var db = new OrderSortingDBEntities())
{
db.Database.ExecuteSqlCommandAsync("DELETE FROM LatticeOrdersCache", new List <SQLiteParameter>());
db.SaveChangesAsync();
}
}
catch (Exception)
{
throw;
}
}
internal void SolutionZipType(SolutionZipType solutionZipType)
{
try
{
using (var db = new OrderSortingDBEntities())
{
db.SolutionZipType.AddOrUpdate(solutionZipType);
db.SaveChangesAsync();
}
}
catch (Exception ex)
{
SaveErrLogHelper.SaveErrorLog(string.Empty, ex.ToString());
}
}
internal SolutionZipType GetSolutionZipType(int latticeSettingId)
{
try
{
using (var db = new OrderSortingDBEntities())
{
return(_curSolutionZipType.Find(o => o.LatticeSettingId == latticeSettingId));
}
}
catch (Exception ex)
{
SaveErrLogHelper.SaveErrorLog(string.Empty, ex.ToString());
return(null);
}
}
/// <summary>
/// 查询分拣记录
/// </summary>
internal List <OrderSortingLog> GetOrderSortingLogByPageSize(string orderId, int operationType, int status, DateTime startDate, DateTime endDate, int pageIndex, int pageSize, ref int recordCount)
{
try
{
//分页查询，默认每页20条
string sql1 = "SELECT * FROM OrderSortingLog o ";
//计算总数
string sql2 = "SELECT COUNT(ID) rowCount FROM OrderSortingLog o ";
string where = " WHERE (OperationTime between datetime(@startDate) and datetime(@endDate))";
//参数
var sqlParams = new List <SQLiteParameter>()
{
new SQLiteParameter("@startDate", startDate.ToString("yyyy-MM-dd")),
new SQLiteParameter("@endDate", endDate.AddDays(1).ToString("yyyy-MM-dd"))
};
var list = new List <OrderSortingLog>();
//判断有没有输入OrderID
if (!string.IsNullOrWhiteSpace(orderId))
{
where += " and o.OrderId = @orderId ";
sqlParams.Add(new SQLiteParameter("@orderId", orderId));
}
if (operationType > 0)
{
where += " and o.OperationType = @operationType ";
sqlParams.Add(new SQLiteParameter("@operationType", operationType));
}
if (status > 0)
{
where += " and o.Status = @status ";
sqlParams.Add(new SQLiteParameter("@status", status));
}
sql1 += where + string.Format(" Order By o.OperationTime Desc limit {0} offset {1} ",
pageSize, pageSize * (pageIndex - 1));
sql2 += where;
using (var db = new OrderSortingDBEntities())
{
recordCount = db.Database.SqlQuery <int>(sql2, sqlParams.ToArray()).First();
return(db.Database.SqlQuery <OrderSortingLog>(sql1, sqlParams.ToArray()).ToList());
}
}
catch (Exception)
{
throw;
}
}
