/* * 仓库 */ using System; using System.Web; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Data; using System.Threading; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Http; using Microsoft.Extensions.Logging; using Microsoft.Extensions.Options; using MySystem.Models; using Library; using LitJson; using MySystemLib; namespace MySystem.Areas.Admin.Controllers { [Area("Admin")] [Route("Admin/[controller]/[action]")] public class StoreHouseController : BaseController { public StoreHouseController(IHttpContextAccessor accessor, ILogger logger, IOptions setting) : base(accessor, logger, setting) { OtherMySqlConn.connstr = ConfigurationManager.AppSettings["SqlConnStr"].ToString(); } #region 仓库列表 /// /// 根据条件查询仓库列表 /// /// public IActionResult Index(StoreHouse data, string right) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; return View(); } #endregion #region 根据条件查询仓库列表 /// /// 仓库列表 /// /// public JsonResult IndexData(StoreHouse data, string UserIdMakerCode, string UserIdRealName, string BrandId, string ManageUserIdRealName, string ManageUserIdMobile, string ManageUserIdMakerCode, string StoreStatusSelect, string CreateDateData, string MakerCode, string YZOpCode, string OpStatusSelect, string StoreKindSelect, int ShowFlag = 0, int page = 1, int limit = 30) { Dictionary Fields = new Dictionary(); Fields.Add("StoreNo", "1"); //仓库编号 Fields.Add("StoreName", "1"); //仓库名称 Fields.Add("ManagerEmail", "1"); //管理者邮箱 if (ShowFlag == 0) { Dictionary objs = new Dictionary(); return Json(objs); } string condition = " and Status>-1"; //品牌 if (!string.IsNullOrEmpty(BrandId)) { condition += " and BrandId =" + BrandId; } //仓库归属人创客编号 if (!string.IsNullOrEmpty(UserIdMakerCode)) { condition += " and UserId in (select UserId from UserForMakerCode where MakerCode='" + UserIdMakerCode + "')"; } //仓库归属人真实姓名 if (!string.IsNullOrEmpty(UserIdRealName)) { DataTable dt = OtherMySqlConn.dtable("SELECT Id FROM Users WHERE RealName like '%" + UserIdRealName + "%'"); var Count = dt.Rows.Count; // int times = 0; string UserIds = ""; foreach (DataRow item in dt.Rows) { UserIds += item["Id"].ToString() + ","; } condition += " and UserId in (" + UserIds.TrimEnd(',') + ")"; } //仓库管理员真实姓名 if (!string.IsNullOrEmpty(ManageUserIdRealName)) { DataTable dts = OtherMySqlConn.dtable("SELECT Id FROM Users WHERE RealName like '%" + ManageUserIdRealName + "%'"); var Counts = dts.Rows.Count; // int time = 1; string UserIdss = ""; foreach (DataRow items in dts.Rows) { UserIdss += items["Id"].ToString() + ","; } condition += " and UserId in (" + UserIdss.TrimEnd(',') + ")"; } //仓库管理员手机号 if (!string.IsNullOrEmpty(ManageUserIdMobile)) { condition += " and ManageUserId in (select ManageUserId from UserForMobile where Mobile='" + ManageUserIdMobile + "')"; } //仓库管理员创客编号 if (!string.IsNullOrEmpty(ManageUserIdMakerCode)) { condition += " and ManageUserId in (select ManageUserId from UserForMakerCode where MakerCode='" + ManageUserIdMakerCode + "')"; } //仓库状态 if (!string.IsNullOrEmpty(StoreStatusSelect)) { condition += " and Status=" + StoreStatusSelect; } if (!string.IsNullOrEmpty(CreateDateData)) { string[] datelist = CreateDateData.Split(new string[] { " - " }, StringSplitOptions.None); string start = datelist[0]; string end = datelist[1]; condition += " and CreateDate>='" + start + " 00:00:00' and CreateDate<='" + end + " 23:59:59'"; } //下线创客编号 if (!string.IsNullOrEmpty(MakerCode)) { var userForMakerCode = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode) ?? new UserForMakerCode(); if (userForMakerCode.UserId > 0) { var user = db.Users.FirstOrDefault(m => m.Id == userForMakerCode.UserId); var users = db.Users.Where(m => m.ParentNav.Contains("," + user.Id.ToString() + ",")).ToList(); string userIds = ""; foreach (var item in users) { userIds += item.Id + ","; } userIds += user.Id;//下线创客Id包含自己 string Id = ""; DataTable dt = OtherMySqlConn.dtable("SELECT Id FROM StoreHouse WHERE UserId IN(" + userIds.TrimEnd(',') + ") AND `Status`=1 AND Sort=0 AND UserId>1"); foreach (DataRow item in dt.Rows) { Id += item["Id"].ToString() + ","; } condition += " and Id in (" + Id.TrimEnd(',') + ")"; } } //是否关联 if (!string.IsNullOrEmpty(OpStatusSelect)) { if (int.Parse(OpStatusSelect) > 0) { condition += " and OpId>0"; } else { condition += " and OpId=0"; } } //是否运营仓 if (!string.IsNullOrEmpty(StoreKindSelect)) { if (int.Parse(StoreKindSelect) > 0) { condition += " and Sort>0 and StoreKind=2"; } else { condition += " and Sort=0 and StoreKind!=2"; } } Dictionary obj = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).IndexData("StoreHouse", Fields, "Id desc", "0", page, limit, condition); List> diclist = obj["data"] as List>; foreach (Dictionary dic in diclist) { //仓库归属人 int UserId = int.Parse(function.CheckInt(dic["UserId"].ToString())); Users userid_Users = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users(); dic["UserIdMakerCode"] = userid_Users.MakerCode; dic["UserIdRealName"] = userid_Users.RealName; //产品类型 dic["BrandId"] = RelationClass.GetKqProductBrandList(dic["BrandId"].ToString()); //仓库管理员 int ManageUserId = int.Parse(function.CheckInt(dic["ManageUserId"].ToString())); Users manageuserid_Users = db.Users.FirstOrDefault(m => m.Id == ManageUserId) ?? new Users(); dic["ManageUserIdRealName"] = manageuserid_Users.RealName; dic["ManageUserIdMobile"] = manageuserid_Users.Mobile; dic["ManageUserIdMakerCode"] = manageuserid_Users.MakerCode; //仓库类型 int StoreType = int.Parse(dic["StoreType"].ToString()); if (StoreType == 0) dic["StoreType"] = "实体仓"; if (StoreType == 1) dic["StoreType"] = "虚拟仓"; //仓库状态 int Status = int.Parse(dic["Status"].ToString()); if (Status == 1) dic["Status"] = "启用"; if (Status == 0) dic["Status"] = "禁用"; //仓库归属类型 int StoreKind = int.Parse(dic["StoreKind"].ToString()); if (StoreKind == 0) dic["StoreKind"] = "分仓"; if (StoreKind == 1) dic["StoreKind"] = "总仓"; if (StoreKind == 2) dic["StoreKind"] = "运营仓"; } return Json(obj); } #endregion #region 增加仓库 /// /// 增加或修改仓库信息 /// /// public IActionResult Add(string right) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; return View(); } #endregion #region 增加仓库 /// /// 增加或修改仓库信息 /// /// [HttpPost] public string Add(StoreHouse data, string ManageMakerCode, string MakerCode) { string BrandIdString = data.BrandId; if (!string.IsNullOrEmpty(BrandIdString)) { string[] BrandIdList = BrandIdString.Split(','); foreach (string BrandId in BrandIdList) { Dictionary Fields = new Dictionary(); Fields.Add("CreateMan", SysUserName); //创建人 Fields.Add("StoreName", data.StoreName); //仓库名称 Fields.Add("FilePath", data.FilePath); //附件 Fields.Add("Deposit", data.Deposit); //押金 UserForMakerCode user = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode); if (user == null) { return "您输入的仓库归属创客编号不存在，请重新输入正确的仓库归属创客编号"; } Fields.Add("UserId", user.UserId); //仓库归属人 Fields.Add("Areas", data.Areas); //所属地区 Fields.Add("Address", data.Address); //仓库地址 UserForMakerCode manage = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == ManageMakerCode); if (manage == null) { return "您输入的仓库负责人编号不存在，请重新输入正确的仓库负责人编号"; } Fields.Add("ManageUserId", manage.UserId); //仓库管理员 Fields.Add("ManagerEmail", data.ManagerEmail); //管理者邮箱 Fields.Add("ManageMobile", data.ManageMobile); Fields.Add("Remark", data.Remark); //备注 Fields.Add("LimitTopUserId", data.LimitTopUserId); //限制创客特殊仓库 Fields.Add("StoreKind", data.StoreKind); //仓库归属类型 int bid = int.Parse(BrandId); KqProducts pro = db.KqProducts.FirstOrDefault(m => m.Id == bid) ?? new KqProducts(); Fields.Add("BrandId", BrandId); Fields.Add("ProductName", pro.Name); Fields.Add("StoreStatus", 1); Fields.Add("Status", 1); var name = db.StoreHouse.FirstOrDefault(m => m.UserId == user.UserId && m.BrandId == BrandId && m.StoreName == data.StoreName && m.OpId == 0); if (name != null) { return "您的仓库名重复，请重新填写"; } int Id = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Add("StoreHouse", Fields, 0); int StoreId = Id; var StoreNos = ""; //添加分仓创建时间 var users = db.Users.FirstOrDefault(m => m.Id == user.UserId) ?? new Users(); if (users.Id > 0) { if (string.IsNullOrEmpty(users.CreateStoreDate.ToString())) { users.CreateStoreDate = DateTime.Now; db.SaveChanges(); } } StoreHouse edit = db.StoreHouse.FirstOrDefault(m => m.Id == Id); if (edit != null) { string No = Id.ToString(); for (int i = 0; i < 7 - Id.ToString().Length; i++) { No = "0" + No; } No = "S" + No; edit.StoreNo = No; StoreNos = No; db.StoreForCode.Add(new StoreForCode() { Code = No, StoreId = Id, }); RedisDbconn.Instance.Set("StoreForCode:" + No, Id); } StoreForName storeForName = db.StoreForName.FirstOrDefault(m => m.Name == data.StoreName); if (storeForName == null) { db.StoreForName.Add(new StoreForName() { Name = data.StoreName, StoreId = Id, }); } else { storeForName.StoreId = Id; } UserAccount account = db.UserAccount.FirstOrDefault(m => m.Id == user.UserId); if (account == null) { account = db.UserAccount.Add(new UserAccount() { Id = user.UserId, UserId = user.UserId, }).Entity; db.SaveChanges(); } if (account.FixedAmount == 0 && account.ValidAmount == 0) { account.FixedAmount = 20000; account.ValidAmount = 20000; } account.StoreDeposit = data.Deposit; AddSysLog(data.Id.ToString(), "StoreHouse", "add"); db.SaveChanges(); SetRedis(Id, user.UserId); } } return "success"; } #endregion #region 修改仓库 /// /// 增加或修改仓库信息 /// /// public IActionResult Edit(string right, int Id = 0) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; StoreHouse editData = db.StoreHouse.FirstOrDefault(m => m.Id == Id) ?? new StoreHouse(); ViewBag.data = editData; Users manage = db.Users.FirstOrDefault(m => m.Id == editData.ManageUserId) ?? new Users(); ViewBag.ManageMakerCode = manage.MakerCode; ViewBag.ManageRealName = manage.RealName; Users user = db.Users.FirstOrDefault(m => m.Id == editData.UserId) ?? new Users(); ViewBag.MakerCode = user.MakerCode; return View(); } #endregion #region 修改仓库 /// /// 增加或修改仓库信息 /// /// [HttpPost] public string Edit(StoreHouse data, string ManageMakerCode, string MakerCode) { Dictionary Fields = new Dictionary(); Fields.Add("CreateMan", SysUserName); //创建人 Fields.Add("UpdateMan", SysUserName + "-" + SysRealName); //修改人 Fields.Add("StoreName", data.StoreName); //仓库名称 Fields.Add("FilePath", data.FilePath); //附件 Fields.Add("Deposit", data.Deposit); //押金 UserForMakerCode user = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode); if (user == null) { return "您输入的仓库归属创客编号不存在，请重新输入正确的仓库归属创客编号"; } Fields.Add("UserId", user.UserId); //仓库归属人 Fields.Add("ManagerEmail", data.ManagerEmail); //管理者邮箱 Fields.Add("ManageMobile", data.ManageMobile); Fields.Add("Areas", data.Areas); //所属地区 Fields.Add("Address", data.Address); //仓库地址 UserForMakerCode manage = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == ManageMakerCode); if (manage == null) { return "您输入的仓库负责人编号不存在，请重新输入正确的仓库负责人编号"; } Fields.Add("ManageUserId", manage.UserId); //仓库管理员 Fields.Add("Remark", data.Remark); //备注 Fields.Add("LimitTopUserId", data.LimitTopUserId); //限制创客特殊仓库 Fields.Add("StoreKind", data.StoreKind); //仓库归属类型 Fields.Add("Status", data.Status); Fields.Add("StoreStatus", data.Status); var sto = db.StoreHouse.FirstOrDefault(m => m.Id == data.Id); var store = db.StoreHouse.Where(m => m.Sort == 0 && m.Status == 1 && m.UserId == sto.UserId).ToList(); UserAccount userAccountOld = db.UserAccount.FirstOrDefault(m => m.Id == sto.UserId); UserAccount userAccount = db.UserAccount.FirstOrDefault(m => m.Id == user.UserId); decimal olddeposit = 0;//旧押金 decimal newdeposit = 0;//新押金 newdeposit = data.Deposit; olddeposit = sto.Deposit; if (sto.UserId != user.UserId) { var check = db.StoreHouse.Any(m => m.Sort == 0 && m.Status == 1 && m.UserId == user.UserId); if (check) { return "创客" + MakerCode + "名下已存在分仓，不可再变更"; } else { if (userAccount == null) { userAccount = db.UserAccount.Add(new UserAccount() { Id = user.UserId, UserId = user.UserId, }).Entity; db.SaveChanges(); } var checks = db.StoreHouseAmountPromiss.Any(m => m.ToUserId == sto.UserId); if (checks) { var storeHouseAmountPromiss = db.StoreHouseAmountPromiss.Where(m => m.Status == 1 && m.ToUserId == sto.UserId).ToList(); foreach (var item in storeHouseAmountPromiss) { var info = storeHouseAmountPromiss.FirstOrDefault(m => m.Id == item.Id); info.UpdateDate = DateTime.Now; info.UpdateMan = SysRealName + "-" + SysUserName; info.ToUserId = user.UserId; } } userAccount.UpdateMan = SysUserName + "-" + SysRealName; userAccount.StoreDeposit = data.Deposit; userAccount.FixedAmount = userAccountOld.FixedAmount; userAccount.ValidAmount = userAccountOld.ValidAmount; userAccount.TempAmount = userAccountOld.TempAmount; userAccount.TempAmountForBalance = userAccountOld.TempAmountForBalance; userAccountOld.UpdateMan = SysUserName + "-" + SysRealName; userAccountOld.StoreDeposit = 0; userAccountOld.ValidAmount = 0; userAccountOld.FixedAmount = 0; userAccountOld.TempAmount = 0; userAccountOld.TempAmountForBalance = 0; } } else { userAccount.UpdateMan = SysUserName + "-" + SysRealName; userAccount.StoreDeposit = data.Deposit; foreach (var item in store) { var storeInfo = store.FirstOrDefault(m => m.Id == item.Id); storeInfo.UpdateMan = SysUserName + "-" + SysRealName; storeInfo.UserId = user.UserId; storeInfo.ManageUserId = manage.UserId; storeInfo.ManagerEmail = data.ManagerEmail; storeInfo.ManageMobile = data.ManageMobile; storeInfo.Address = data.Address; storeInfo.Areas = data.Areas; storeInfo.LimitTopUserId = data.LimitTopUserId; storeInfo.FilePath = data.FilePath; storeInfo.Deposit = data.Deposit; storeInfo.Remark = data.Remark; } } db.SaveChanges(); new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("StoreHouse", Fields, data.Id); AddSysLog(data.Id.ToString(), "StoreHouse", "update"); db.SaveChanges(); return "success"; } #endregion #region 一键添加仓库 /// /// 一件添加仓库 /// /// public IActionResult Edits(string right, int Id = 0) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; StoreHouse editData = db.StoreHouse.FirstOrDefault(m => m.Id == Id) ?? new StoreHouse(); ViewBag.data = editData; Users manage = db.Users.FirstOrDefault(m => m.Id == editData.ManageUserId) ?? new Users(); ViewBag.ManageMakerCode = manage.MakerCode; ViewBag.ManageRealName = manage.RealName; Users user = db.Users.FirstOrDefault(m => m.Id == editData.UserId) ?? new Users(); ViewBag.MakerCode = user.MakerCode; return View(); } #endregion #region 一键添加仓库 /// /// 一键添加仓库 /// /// [HttpPost] public string Edits(StoreHouse data, string ManageMakerCode, string MakerCode) { Dictionary Fields = new Dictionary(); StoreHouse storeHouse = db.StoreHouse.FirstOrDefault(m => m.Id == data.Id); var brand = db.StoreHouse.FirstOrDefault(m => m.UserId == storeHouse.UserId && m.BrandId == data.BrandId && m.OpId == 0); if (brand != null) { return "您已有该品牌类型的仓库，请重新选择"; } var name = db.StoreHouse.FirstOrDefault(m => m.UserId == storeHouse.UserId && m.StoreName == data.StoreName); if (name != null) { return "您的仓库名重复，请重新填写"; } Fields.Add("StoreName", data.StoreName); //仓库名称 UserForMakerCode user = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode); if (user == null) { return "您输入的仓库归属创客编号不存在，请重新输入正确的仓库归属创客编号"; } Fields.Add("UserId", user.UserId); //仓库归属人 Fields.Add("Areas", data.Areas); //所属地区 Fields.Add("Address", data.Address); //仓库地址 UserForMakerCode manage = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == ManageMakerCode); if (manage == null) { return "您输入的仓库负责人编号不存在，请重新输入正确的仓库负责人编号"; } Fields.Add("CreateMan", SysUserName); //创建人 Fields.Add("ManageUserId", manage.UserId); //仓库管理员 Fields.Add("ManagerEmail", data.ManagerEmail); //管理者邮箱 Fields.Add("ManageMobile", data.ManageMobile); Fields.Add("Remark", data.Remark); //备注 Fields.Add("FilePath", data.FilePath); //附件 Fields.Add("Deposit", data.Deposit); //押金 Fields.Add("LimitTopUserId", data.LimitTopUserId); //限制创客特殊仓库 Fields.Add("StoreKind", data.StoreKind); //仓库归属类型 int bid = Convert.ToInt32(data.BrandId); KqProducts pro = db.KqProducts.FirstOrDefault(m => m.Id == bid) ?? new KqProducts(); Fields.Add("BrandId", data.BrandId); Fields.Add("ProductName", pro.Name); Fields.Add("StoreStatus", 1); Fields.Add("Status", 1); int Id = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Add("StoreHouse", Fields, 0); int StoreId = Id; var StoreNos = ""; //添加分仓创建时间 var users = db.Users.FirstOrDefault(m => m.Id == user.UserId) ?? new Users(); if (users.Id > 0) { if (string.IsNullOrEmpty(users.CreateStoreDate.ToString())) { users.CreateStoreDate = DateTime.Now; db.SaveChanges(); } } StoreHouse edit = db.StoreHouse.FirstOrDefault(m => m.Id == Id); if (edit != null) { string No = Id.ToString(); for (int i = 0; i < 7 - Id.ToString().Length; i++) { No = "0" + No; } No = "S" + No; edit.StoreNo = No; StoreNos = No; db.StoreForCode.Add(new StoreForCode() { Code = No, StoreId = Id, }); RedisDbconn.Instance.Set("StoreForCode:" + No, Id); } StoreForName storeForName = db.StoreForName.FirstOrDefault(m => m.Name == data.StoreName); if (storeForName == null) { db.StoreForName.Add(new StoreForName() { Name = data.StoreName, StoreId = Id, }); } else { storeForName.StoreId = Id; } UserAccount userAccount = db.UserAccount.FirstOrDefault(m => m.Id == user.UserId); if (userAccount == null) { userAccount = db.UserAccount.Add(new UserAccount() { Id = user.UserId, UserId = user.UserId, }).Entity; db.SaveChanges(); } userAccount.StoreDeposit = data.Deposit; db.SaveChanges(); AddSysLog(data.Id.ToString(), "StoreHouse", "add"); db.SaveChanges(); return "success"; } #endregion #region 删除仓库信息 /// /// 删除仓库信息 /// /// public string Delete(string Id) { string[] idlist = Id.Split(new char[] { ',' }); AddSysLog(Id, "StoreHouse", "del"); foreach (string subid in idlist) { int id = int.Parse(subid); Dictionary Fields = new Dictionary(); Fields.Add("Status", -1); StoreHouse edit = db.StoreHouse.FirstOrDefault(m => m.Id == id); if (edit != null) { StoreForCode code = db.StoreForCode.FirstOrDefault(m => m.Code == edit.StoreNo); if (code != null) { db.StoreForCode.Remove(code); } StoreForName name = db.StoreForName.FirstOrDefault(m => m.Name == edit.StoreName); if (name != null) { db.StoreForName.Remove(name); } } new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("StoreHouse", Fields, id); } db.SaveChanges(); return "success"; } #endregion #region 开启 /// /// 开启 /// /// public string Open(string Id) { string[] idlist = Id.Split(new char[] { ',' }); AddSysLog(Id, "StoreHouse", "open"); foreach (string subid in idlist) { int id = int.Parse(subid); Dictionary Fields = new Dictionary(); Fields.Add("Status", 1); new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("StoreHouse", Fields, id); } db.SaveChanges(); return "success"; } #endregion #region 关闭 /// /// 关闭 /// /// public string Close(string Id) { string[] idlist = Id.Split(new char[] { ',' }); AddSysLog(Id, "StoreHouse", "close"); foreach (string subid in idlist) { int id = int.Parse(subid); Dictionary Fields = new Dictionary(); Fields.Add("Status", 0); new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Edit("StoreHouse", Fields, id); } db.SaveChanges(); return "success"; } #endregion #region 排序 /// /// 排序 /// /// public string Sort(int Id, int Sort) { new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).Sort("StoreHouse", Sort, Id); AddSysLog(Id.ToString(), "StoreHouse", "sort"); return "success"; } #endregion #region 导入数据 public IActionResult Import(string right, string ExcelKind) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; ViewBag.ExcelKind = ExcelKind; return View(); } /// /// 导入数据 /// /// [HttpPost] public string ImportPost(string ExcelPath, int Kind = 0) { string key = function.MD5_16(Guid.NewGuid().ToString()); RedisDbconn.Instance.AddList("ExcelImportV2", ExcelPath + "#cut#" + Kind + "#cut#" + key + "#cut#" + SysUserName); return "success|" + key; } public string CheckImport(string key) { string result = RedisDbconn.Instance.Get("CheckImport:" + key); if (!string.IsNullOrEmpty(result)) { string[] datalist = result.Split('|'); if (datalist[0] == "success") { return result; } return datalist[0]; } return "0"; } public Dictionary CheckImportV2(string key) { Dictionary Obj = new Dictionary(); string result = RedisDbconn.Instance.Get("CheckImport:" + key); if (!string.IsNullOrEmpty(result)) { string[] datalist = result.Split('|'); if (datalist[0] == "success") { List errList = RedisDbconn.Instance.GetList("ErrList" + key); if (errList.Count > 0) { Obj.Add("status", 2); Obj.Add("errList", errList); } else { Obj.Add("status", 1); Obj.Add("data", result); } return Obj; } Obj.Add("status", 0); Obj.Add("data", datalist[0]); return Obj; } Obj.Add("status", -1); Obj.Add("data", "执行中..."); return Obj; } #endregion #region 机具驳回仓库 /// /// 机具驳回仓库 /// /// public string RejectStore(string ExcelData, int ExcelKind = 0) { ExcelData = HttpUtility.UrlDecode(ExcelData); JsonData list = JsonMapper.ToObject(ExcelData); if (ExcelKind == 1) { string error = ""; decimal amount = 0; List PosSnList = new List(); List opData = new List(); for (int i = 1; i < list.Count; i++) { JsonData dr = list[i]; string itemJson = dr.ToJson(); string PosSn = itemJson.Contains("\"A\"") ? dr["A"].ToString() : ""; string BrandId = itemJson.Contains("\"B\"") ? dr["B"].ToString() : ""; string MakerCode = itemJson.Contains("\"C\"") ? dr["C"].ToString() : ""; string StoreNo = itemJson.Contains("\"D\"") ? dr["D"].ToString() : ""; string No = itemJson.Contains("\"E\"") ? dr["E"].ToString() : ""; string Remark = itemJson.Contains("\"F\"") ? dr["F"].ToString() : ""; if (string.IsNullOrEmpty(PosSn) || string.IsNullOrEmpty(BrandId) || string.IsNullOrEmpty(MakerCode) || string.IsNullOrEmpty(StoreNo)) { return "失败,请检查机具Sn、品牌、创客编号、仓库编号是否为空！"; } UserForMakerCode userForMakerCode = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode); if (userForMakerCode == null) { return "未找到创客" + MakerCode + "相关创客关联数据" + '\n'; } StoreForCode storeForCode = db.StoreForCode.FirstOrDefault(m => m.Code == StoreNo); if (storeForCode == null) { return "未找到仓库" + StoreNo + "相关关联数据" + '\n'; } var store = db.StoreHouse.FirstOrDefault(m => m.Id == storeForCode.StoreId && m.BrandId == BrandId) ?? new StoreHouse(); MachineForSnNo machineForSnNo = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == PosSn) ?? new MachineForSnNo(); if (machineForSnNo == null) { return "未找到机具" + PosSn + "相关关联数据" + '\n'; } var pos = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machineForSnNo.SnId && m.BrandId == Convert.ToInt32(BrandId) && m.BindingState == 0 && m.BuyUserId == userForMakerCode.UserId) ?? new PosMachinesTwo(); var posInfo = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machineForSnNo.SnId && m.BrandId == Convert.ToInt32(BrandId) && m.BindingState == 0) ?? new PosMachinesTwo(); if (pos.Id == 0) { error += "以下操作失败" + PosSn + ',' + "未找到创客" + MakerCode + "的该台机具" + '\n'; } if (store.Id == 0) { error += "以下操作失败" + PosSn + ',' + StoreNo + ',' + "未找到该仓库" + '\n'; } if (posInfo.Id == 0) { error += "以下操作失败" + PosSn + ',' + "未找到该品牌的未使用机具" + '\n'; } if (PosSnList.Contains(PosSn)) { error += "以下操作失败" + PosSn + ',' + "该机具号重复" + '\n'; } else { PosSnList.Add(PosSn); } } if (!string.IsNullOrEmpty(error)) { return "Warning|" + error; } else { for (int i = 1; i < list.Count; i++) { JsonData dr = list[i]; string itemJson = dr.ToJson(); string PosSn = itemJson.Contains("\"A\"") ? dr["A"].ToString() : ""; string BrandId = itemJson.Contains("\"B\"") ? dr["B"].ToString() : ""; string MakerCode = itemJson.Contains("\"C\"") ? dr["C"].ToString() : ""; string StoreNo = itemJson.Contains("\"D\"") ? dr["D"].ToString() : ""; string No = itemJson.Contains("\"E\"") ? dr["E"].ToString() : ""; string Remark = itemJson.Contains("\"F\"") ? dr["F"].ToString() : ""; UserForMakerCode userForMakerCode = db.UserForMakerCode.FirstOrDefault(m => m.MakerCode == MakerCode); StoreForCode storeForCode = db.StoreForCode.FirstOrDefault(m => m.Code == StoreNo); var store = db.StoreHouse.FirstOrDefault(m => m.Id == storeForCode.StoreId && m.BrandId == BrandId) ?? new StoreHouse(); MachineForSnNo machineForSnNo = db.MachineForSnNo.FirstOrDefault(m => m.SnNo == PosSn) ?? new MachineForSnNo(); var pos = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machineForSnNo.SnId && m.BrandId == Convert.ToInt32(BrandId) && m.BindingState == 0 && m.BuyUserId == userForMakerCode.UserId) ?? new PosMachinesTwo(); var posInfo = db.PosMachinesTwo.FirstOrDefault(m => m.Id == machineForSnNo.SnId && m.BrandId == Convert.ToInt32(BrandId) && m.BindingState == 0) ?? new PosMachinesTwo(); var fromStore = db.StoreHouse.FirstOrDefault(m => m.Id == pos.StoreId && m.BrandId == BrandId) ?? new StoreHouse(); var toStore = db.StoreHouse.FirstOrDefault(m => m.Id == storeForCode.StoreId && m.BrandId == BrandId) ?? new StoreHouse(); var user = db.Users.FirstOrDefault(m => m.Id == toStore.UserId) ?? new Users(); var userAccount = db.UserAccount.FirstOrDefault(m => m.Id == toStore.UserId) ?? new UserAccount(); var brandInfo = db.KqProducts.FirstOrDefault(m => m.Id == Convert.ToInt32(BrandId)) ?? new KqProducts(); var fuserMachineData = db.UserMachineData.FirstOrDefault(m => m.IdBrand == userForMakerCode.UserId + "_" + brandInfo.Id); if (brandInfo.Name.Contains("电签")) { amount = 200; } if (brandInfo.Name.Contains("大POS")) { amount = 300; } userAccount.ValidAmount -= amount; db.SaveChanges(); if (fromStore.Id != toStore.Id) { var changeNo = "JJBH" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + function.get_Random(8); StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange() { CreateDate = DateTime.Now, StoreId = fromStore.Id, //出货仓库 ToStoreId = toStore.Id, //发货仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 ProductName = RelationClass.GetKqProductBrandInfo(Convert.ToInt32(BrandId)), //产品名称 ChangeNo = changeNo, //变更记录单号 BizBatchNo = No, //业务批次号 TransType = 4, //交易类型 SnNo = PosSn, //SN编号 StockOpDirect = 1, //库存操作方向 SnStatus = 1, //SN状态 DeviceVendor = posInfo.DeviceName, //设备厂商 DeviceModel = posInfo.DeviceKind, //设备型号 DeviceType = posInfo.DeviceType, //设备类型 SourceStoreId = posInfo.SourceStoreId, //源仓库编号 BrandType = posInfo.DeviceType, //品牌类型 }).Entity; db.SaveChanges(); db.StoreChangeHistory.Add(new StoreChangeHistory() { CreateDate = DateTime.Now, CreateMan = SysUserName, UserId = user.Id, //创客 FromUserId = posInfo.BuyUserId,//来源创客 ToUserId = toStore.UserId,//接收创客 StoreId = fromStore.Id, //出货仓库 ToStoreId = toStore.Id, //发货仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 ChangeRecordNo = changeNo, //变更记录单号 SeoTitle = "机具驳回仓库", BizBatchNo = No, //业务批次号 TransType = 4,//驳回 SnNo = PosSn, //SN编号 }); db.SaveChanges(); StoreBalance fbalance = db.StoreBalance.Add(new StoreBalance() { CreateDate = DateTime.Now, StoreId = fromStore.Id, //仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 OpStoreNum = 1, //操作库存数 OpSymbol = "-", //操作符 BeforeTotalNum = fromStore.TotalNum, //操作前总库存数 AfterTotalNum = fromStore.TotalNum - 1, //操作后总库存数 BeforeLaveNum = fromStore.LaveNum, //操作前剩余库存数 AfterLaveNum = fromStore.LaveNum - 1, //操作后剩余库存数 BeforeOutNum = fromStore.OutNum, //操作前出库数 AfterOutNum = fromStore.OutNum - 1, //操作后出库数 }).Entity; db.SaveChanges(); StoreBalance tbalance = db.StoreBalance.Add(new StoreBalance() { CreateDate = DateTime.Now, StoreId = toStore.Id, //仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 OpStoreNum = 1, //操作库存数 OpSymbol = "+", //操作符 BeforeTotalNum = toStore.TotalNum, //操作前总库存数 AfterTotalNum = toStore.TotalNum + 1, //操作后总库存数 BeforeLaveNum = toStore.LaveNum, //操作前剩余库存数 AfterLaveNum = toStore.LaveNum + 1, //操作后剩余库存数 BeforeOutNum = toStore.OutNum, //操作前出库数 AfterOutNum = toStore.OutNum, //操作后出库数 }).Entity; db.SaveChanges(); if (!opData.Contains(posInfo.BuyUserId + ":" + posInfo.BrandId)) { opData.Add(posInfo.BuyUserId + ":" + posInfo.BrandId); } posInfo.StoreId = toStore.Id; posInfo.BuyUserId = 0; posInfo.UserId = 0; posInfo.RecycEndDate = null; posInfo.PosSnType = 0; posInfo.BindMerchantId = 0; posInfo.ActivityList = null; posInfo.ScanQrTrade = 0; posInfo.DebitCardTrade = 0; posInfo.PrizeParams = null; posInfo.IsVip = 0; posInfo.CreditTrade = 0; posInfo.UserNav = null; posInfo.TransferTime = null; posInfo.IsPurchase = 0; posInfo.BindingTime = null; posInfo.BindingState = 0; posInfo.ActivationTime = null; posInfo.ActivationState = 0; posInfo.LeaderUserId = 0; posInfo.PreUserId = 0; posInfo.IsFirst = 0; fuserMachineData.TotalMachineCount -= 1; fuserMachineData.UnBindCount -= 1; toStore.TotalNum += 1; toStore.LaveNum += 1; fromStore.TotalNum -= 1; fromStore.LaveNum -= 1; PublicFunction.ClearPosHistory(db, posInfo.PosSn); //清除机具历史记录 db.SaveChanges(); } else { var changeNo = "JJBH" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + function.get_Random(8); StoreStockChange stockchange = db.StoreStockChange.Add(new StoreStockChange() { CreateDate = DateTime.Now, StoreId = fromStore.Id, //出货仓库 ToStoreId = toStore.Id, //发货仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 ProductName = RelationClass.GetKqProductBrandInfo(Convert.ToInt32(BrandId)), //产品名称 ChangeNo = changeNo, //变更记录单号 BizBatchNo = No, //业务批次号 TransType = 4, //交易类型 SnNo = PosSn, //SN编号 StockOpDirect = 1, //库存操作方向 SnStatus = 1, //SN状态 DeviceVendor = posInfo.DeviceName, //设备厂商 DeviceModel = posInfo.DeviceKind, //设备型号 DeviceType = posInfo.DeviceType, //设备类型 SourceStoreId = posInfo.SourceStoreId, //源仓库编号 BrandType = posInfo.DeviceType, //品牌类型 }).Entity; db.SaveChanges(); db.StoreChangeHistory.Add(new StoreChangeHistory() { CreateDate = DateTime.Now, CreateMan = SysUserName, UserId = user.Id, //创客 FromUserId = user.Id,//来源创客 ToUserId = toStore.UserId,//接收创客 StoreId = fromStore.Id, //出货仓库 ToStoreId = toStore.Id, //发货仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 ChangeRecordNo = changeNo, //变更记录单号 SeoTitle = "机具驳回仓库", BizBatchNo = No, //业务批次号 SnNo = PosSn, //SN编号 TransType = 4,//驳回 }); db.SaveChanges(); StoreBalance balance = db.StoreBalance.Add(new StoreBalance() { CreateDate = DateTime.Now, StoreId = fromStore.Id, //仓库 BrandId = Convert.ToInt32(BrandId), //产品类型 OpStoreNum = 1, //操作库存数 OpSymbol = "-", //操作符 BeforeTotalNum = fromStore.TotalNum, //操作前总库存数 AfterTotalNum = fromStore.TotalNum - 1, //操作后总库存数 BeforeLaveNum = fromStore.LaveNum, //操作前剩余库存数 AfterLaveNum = fromStore.LaveNum - 1, //操作后剩余库存数 BeforeOutNum = fromStore.OutNum, //操作前出库数 AfterOutNum = fromStore.OutNum - 1, //操作后出库数 }).Entity; db.SaveChanges(); if (!opData.Contains(posInfo.BuyUserId + ":" + posInfo.BrandId)) { opData.Add(posInfo.BuyUserId + ":" + posInfo.BrandId); } posInfo.StoreId = toStore.Id; posInfo.BuyUserId = 0; posInfo.UserId = 0; posInfo.RecycEndDate = null; posInfo.PosSnType = 0; posInfo.BindMerchantId = 0; posInfo.ActivityList = null; posInfo.ScanQrTrade = 0; posInfo.DebitCardTrade = 0; posInfo.PrizeParams = null; posInfo.IsVip = 0; posInfo.CreditTrade = 0; posInfo.UserNav = null; posInfo.TransferTime = null; posInfo.IsPurchase = 0; posInfo.BindingTime = null; posInfo.BindingState = 0; posInfo.ActivationTime = null; posInfo.ActivationState = 0; posInfo.LeaderUserId = 0; posInfo.PreUserId = 0; posInfo.IsFirst = 0; fuserMachineData.TotalMachineCount -= 1; fuserMachineData.UnBindCount -= 1; toStore.LaveNum += 1; toStore.TotalNum += 1; PublicFunction.ClearPosHistory(db, posInfo.PosSn); //清除机具历史记录 db.SaveChanges(); } db.SaveChanges(); } } foreach (string sub in opData) { string[] datalist = sub.Split(":"); PublicFunction.SycnMachineCount(int.Parse(datalist[0]), int.Parse(datalist[1])); } } AddSysLog("0", "MachinesRejectStore", "Import"); return "success"; } #endregion #region 导出Excel /// /// 导出Excel /// /// public JsonResult ExportExcel(StoreHouse data, string UserIdMakerCode, string UserIdRealName, string ManageUserIdRealName, string ManageUserIdMobile, string ManageUserIdMakerCode, string StoreStatusSelect) { Dictionary Fields = new Dictionary(); Fields.Add("StoreNo", "1"); //仓库编号 Fields.Add("CreateDate", "3"); //时间 Fields.Add("StoreName", "1"); //仓库名称 Fields.Add("ManagerEmail", "1"); //管理者邮箱 string condition = " and Status>-1"; //仓库归属人创客编号 if (!string.IsNullOrEmpty(UserIdMakerCode)) { condition += " and UserId in (select UserId from UserForMakerCode where MakerCode='" + UserIdMakerCode + "')"; } //仓库归属人真实姓名 if (!string.IsNullOrEmpty(UserIdRealName)) { condition += " and UserId in (select UserId from UserForRealName where RealName='" + UserIdRealName + "')"; } //仓库管理员真实姓名 if (!string.IsNullOrEmpty(ManageUserIdRealName)) { condition += " and ManageUserId in (select ManageUserId from UserForRealName where RealName='" + ManageUserIdRealName + "')"; } //仓库管理员手机号 if (!string.IsNullOrEmpty(ManageUserIdMobile)) { condition += " and ManageUserId in (select ManageUserId from UserForMobile where Mobile='" + ManageUserIdMobile + "')"; } //仓库管理员创客编号 if (!string.IsNullOrEmpty(ManageUserIdMakerCode)) { condition += " and ManageUserId in (select ManageUserId from UserForMakerCode where MakerCode='" + ManageUserIdMakerCode + "')"; } //仓库状态 if (!string.IsNullOrEmpty(StoreStatusSelect)) { condition += " and StoreStatus=" + StoreStatusSelect; } Dictionary obj = new AdminContentOther(_accessor.HttpContext, PublicFunction.MainTables).IndexData("StoreHouse", Fields, "Id desc", "0", 1, 20000, condition, "StoreNo,StoreName,UserId,BrandId,Address,ManageUserId,ManagerEmail,StoreType,TotalNum,LaveNum,OutNum,LimitTopUserId,StoreStatus,StoreKind", false); List> diclist = obj["data"] as List>; foreach (Dictionary dic in diclist) { //仓库归属人 int UserId = int.Parse(function.CheckInt(dic["UserId"].ToString())); Users userid_Users = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users(); dic["UserIdMakerCode"] = userid_Users.MakerCode; dic["UserIdRealName"] = userid_Users.RealName; //产品类型 dic["BrandId"] = RelationClass.GetKqProductBrandList(dic["BrandId"].ToString()); //仓库管理员 int ManageUserId = int.Parse(function.CheckInt(dic["ManageUserId"].ToString())); Users manageuserid_Users = db.Users.FirstOrDefault(m => m.Id == ManageUserId) ?? new Users(); dic["ManageUserIdRealName"] = manageuserid_Users.RealName; dic["ManageUserIdMobile"] = manageuserid_Users.Mobile; dic["ManageUserIdMakerCode"] = manageuserid_Users.MakerCode; //仓库类型 int StoreType = int.Parse(dic["StoreType"].ToString()); if (StoreType == 0) dic["StoreType"] = "实体仓"; if (StoreType == 1) dic["StoreType"] = "虚拟仓"; //仓库状态 int StoreStatus = int.Parse(dic["StoreStatus"].ToString()); if (StoreStatus == 1) dic["StoreStatus"] = "启用"; if (StoreStatus == 0) dic["StoreStatus"] = "禁用"; //仓库归属类型 int StoreKind = int.Parse(dic["StoreKind"].ToString()); if (StoreKind == 0) dic["StoreKind"] = "分仓"; if (StoreKind == 1) dic["StoreKind"] = "总仓"; } Dictionary result = new Dictionary(); result.Add("Status", "1"); result.Add("Info", "Excel报表-" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + ".xlsx"); result.Add("Obj", diclist); Dictionary ReturnFields = new Dictionary(); ReturnFields.Add("StoreNo", "仓库编号"); ReturnFields.Add("StoreName", "仓库名称"); ReturnFields.Add("UserIdMakerCode", "仓库归属人创客编号"); ReturnFields.Add("UserIdRealName", "仓库归属人真实姓名"); ReturnFields.Add("BrandId", "产品类型"); ReturnFields.Add("Address", "仓库地址"); ReturnFields.Add("ManageUserIdRealName", "仓库管理员真实姓名"); ReturnFields.Add("ManageUserIdMobile", "仓库管理员手机号"); ReturnFields.Add("ManageUserIdMakerCode", "仓库管理员创客编号"); ReturnFields.Add("ManagerEmail", "管理者邮箱"); ReturnFields.Add("StoreType", "仓库类型"); ReturnFields.Add("TotalNum", "总库存数"); ReturnFields.Add("LaveNum", "剩余库存数"); ReturnFields.Add("OutNum", "出库数"); ReturnFields.Add("LimitTopUserId", "限制创客特殊仓库"); ReturnFields.Add("StoreStatus", "仓库状态"); ReturnFields.Add("StoreKind", "仓库归属类型"); result.Add("Fields", ReturnFields); AddSysLog("0", "StoreHouse", "ExportExcel"); return Json(result); } #endregion #region 仓库列表 /// /// 根据条件查询仓库列表 /// /// public IActionResult Stat(StoreHouse data, string right) { ViewBag.RightInfo = RightInfo; ViewBag.right = right; return View(); } #endregion #region 根据条件查询仓库列表 /// /// 仓库列表 /// /// public JsonResult StatDo(string CreateDateData, int page = 1, int limit = 30) { OtherMySqlConn.connstr = ConfigurationManager.AppSettings["StatSqlConnStr"].ToString(); string sCreateDate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; string eCreateDate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"; if (!string.IsNullOrEmpty(CreateDateData)) { string[] datelist = CreateDateData.Split(new string[] { " - " }, StringSplitOptions.None); sCreateDate = datelist[0] + " 00:00:00"; eCreateDate = datelist[1] + " 23:59:59"; } else { Dictionary nodata = new Dictionary(); nodata.Add("code", 0); nodata.Add("msg", ""); nodata.Add("count", 0); nodata.Add("data", new List>()); return Json(nodata); } string minId = "0"; string maxId = "0"; string minPosId = "0"; string maxPosId = "0"; DataTable startDt = OtherMySqlConn.dtable("select min(Id) from StoreStockChange where CreateDate>='" + sCreateDate + "'"); if (startDt.Rows.Count > 0) { minId = function.CheckInt(startDt.Rows[0][0].ToString()); if (minId == "0") { startDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange"); if (startDt.Rows.Count > 0) { minId = function.CheckInt(startDt.Rows[0][0].ToString()); } } } DataTable endDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange where CreateDate>='" + sCreateDate + "'"); if (endDt.Rows.Count > 0) { maxId = function.CheckInt(endDt.Rows[0][0].ToString()); if (minId == "0") { endDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange"); if (endDt.Rows.Count > 0) { maxId = function.CheckInt(endDt.Rows[0][0].ToString()); } } } DataTable startPosDt = OtherMySqlConn.dtable("select min(Id) from PosMachinesTwo where ActivationTime>='" + sCreateDate + "'"); if (startPosDt.Rows.Count > 0) { minPosId = function.CheckInt(startPosDt.Rows[0][0].ToString()); if (minPosId == "0") { startPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo"); if (startPosDt.Rows.Count > 0) { minPosId = function.CheckInt(startPosDt.Rows[0][0].ToString()); } } } DataTable endPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo where ActivationTime>='" + sCreateDate + "'"); if (endPosDt.Rows.Count > 0) { maxPosId = function.CheckInt(endPosDt.Rows[0][0].ToString()); if (maxPosId == "0") { endPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo"); if (endPosDt.Rows.Count > 0) { maxPosId = function.CheckInt(endPosDt.Rows[0][0].ToString()); } } } DataTable list = OtherMySqlConn.dtable("select * from (select s.StoreName,s.StoreNo,u.RealName,u.Mobile,k.Name,s.LaveNum,(select count(Id) from StoreStockChange where Id>=" + minId + " and Id<=" + maxId + " and CreateDate>='" + sCreateDate + "' and CreateDate<='" + eCreateDate + "' and StoreId=s.Id and TransType=1) as c1,(select count(Id) from StoreStockChange where Id>=" + minId + " and Id<=" + maxId + " and CreateDate>='" + sCreateDate + "' and CreateDate<='" + eCreateDate + "' and StoreId=s.Id and TransType in (10,11,2)) as c2,(select count(Id) from PosMachinesTwo where Id>=" + minPosId + " and Id<=" + maxPosId + " and ActivationState=1 and ActivationTime>='" + sCreateDate + "' and ActivationTime<='" + eCreateDate + "' and StoreId=s.Id) as c3 from StoreHouse s left join Users u on s.BrandId>0 and s.UserId=u.Id left join KqProducts k on s.BrandId=k.Id) tb where c1>0 or c2>0 or c3>0"); int recordcount = list.Rows.Count; List> diclist = new List>(); foreach (DataRow sub in list.Rows) { Dictionary row = new Dictionary(); row.Add("StoreName", sub["StoreName"].ToString()); row.Add("StoreNo", sub["StoreNo"].ToString()); row.Add("Mobile", sub["Mobile"].ToString()); row.Add("RealName", sub["RealName"].ToString()); row.Add("Name", sub["Name"].ToString()); row.Add("LaveNum", sub["LaveNum"].ToString()); row.Add("c1", sub["c1"].ToString()); row.Add("c2", sub["c2"].ToString()); row.Add("c3", sub["c3"].ToString()); diclist.Add(row); } Dictionary obj = new Dictionary(); obj.Add("code", 0); obj.Add("msg", ""); obj.Add("count", recordcount); obj.Add("data", diclist); return Json(obj); } #endregion #region 导出Excel /// /// 导出Excel /// /// public JsonResult StatExportExcel(string CreateDateData) { OtherMySqlConn.connstr = ConfigurationManager.AppSettings["StatSqlConnStr"].ToString(); string sCreateDate = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"; string eCreateDate = DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"; if (!string.IsNullOrEmpty(CreateDateData)) { string[] datelist = CreateDateData.Split(new string[] { " - " }, StringSplitOptions.None); sCreateDate = datelist[0] + " 00:00:00"; eCreateDate = datelist[1] + " 23:59:59"; } else { Dictionary nodata = new Dictionary(); nodata.Add("Status", "1"); nodata.Add("Info", "分仓统计数据报表-" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + ".xlsx"); nodata.Add("Obj", new List>()); Dictionary NoDataReturnFields = new Dictionary(); NoDataReturnFields.Add("StoreName", "仓库名称"); NoDataReturnFields.Add("StoreNo", "仓库编号"); NoDataReturnFields.Add("Mobile", "仓库所属人手机号"); NoDataReturnFields.Add("RealName", "仓库所属人名称"); NoDataReturnFields.Add("MakerCode", "仓库所属人编码"); NoDataReturnFields.Add("ManageMakerCode", "仓库管理员编码"); NoDataReturnFields.Add("Name", "品牌"); NoDataReturnFields.Add("LaveNum", "当前库存"); NoDataReturnFields.Add("c1", "出库(调拨)量"); NoDataReturnFields.Add("c2", "出货(发货到创客)量"); NoDataReturnFields.Add("c3", "激活量"); nodata.Add("Fields", NoDataReturnFields); return Json(nodata); } string minId = "0"; string maxId = "0"; string minPosId = "0"; string maxPosId = "0"; DataTable startDt = OtherMySqlConn.dtable("select min(Id) from StoreStockChange where CreateDate>='" + sCreateDate + "'"); if (startDt.Rows.Count > 0) { minId = function.CheckInt(startDt.Rows[0][0].ToString()); if (minId == "0") { startDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange"); if (startDt.Rows.Count > 0) { minId = function.CheckInt(startDt.Rows[0][0].ToString()); } } } DataTable endDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange where CreateDate>='" + sCreateDate + "'"); if (endDt.Rows.Count > 0) { maxId = function.CheckInt(endDt.Rows[0][0].ToString()); if (minId == "0") { endDt = OtherMySqlConn.dtable("select max(Id) from StoreStockChange"); if (endDt.Rows.Count > 0) { maxId = function.CheckInt(endDt.Rows[0][0].ToString()); } } } DataTable startPosDt = OtherMySqlConn.dtable("select min(Id) from PosMachinesTwo where ActivationTime>='" + sCreateDate + "'"); if (startPosDt.Rows.Count > 0) { minPosId = function.CheckInt(startPosDt.Rows[0][0].ToString()); if (minPosId == "0") { startPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo"); if (startPosDt.Rows.Count > 0) { minPosId = function.CheckInt(startPosDt.Rows[0][0].ToString()); } } } DataTable endPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo where ActivationTime>='" + sCreateDate + "'"); if (endPosDt.Rows.Count > 0) { maxPosId = function.CheckInt(endPosDt.Rows[0][0].ToString()); if (maxPosId == "0") { endPosDt = OtherMySqlConn.dtable("select max(Id) from PosMachinesTwo"); if (endPosDt.Rows.Count > 0) { maxPosId = function.CheckInt(endPosDt.Rows[0][0].ToString()); } } } DataTable list = OtherMySqlConn.dtable("select * from (select s.StoreName,s.StoreNo,u.RealName,u.Mobile,u.MakerCode,(select MakerCode from Users where Id=s.ManageUserId) as ManageMakerCode,k.Name,s.LaveNum,(select count(Id) from StoreStockChange where Id>=" + minId + " and Id<=" + maxId + " and CreateDate>='" + sCreateDate + "' and CreateDate<='" + eCreateDate + "' and StoreId=s.Id and TransType=1) as c1,(select count(Id) from StoreStockChange where Id>=" + minId + " and Id<=" + maxId + " and CreateDate>='" + sCreateDate + "' and CreateDate<='" + eCreateDate + "' and StoreId=s.Id and TransType in (10,11,2)) as c2,(select count(Id) from PosMachinesTwo where Id>=" + minPosId + " and Id<=" + maxPosId + " and ActivationState=1 and ActivationTime>='" + sCreateDate + "' and ActivationTime<='" + eCreateDate + "' and StoreId=s.Id) as c3 from StoreHouse s left join Users u on s.BrandId>0 and s.UserId=u.Id left join KqProducts k on s.BrandId=k.Id) tb where c1>0 or c2>0 or c3>0"); int recordcount = list.Rows.Count; List> diclist = new List>(); foreach (DataRow sub in list.Rows) { Dictionary row = new Dictionary(); row.Add("StoreName", sub["StoreName"].ToString()); row.Add("StoreNo", sub["StoreNo"].ToString()); row.Add("Mobile", sub["Mobile"].ToString()); row.Add("RealName", sub["RealName"].ToString()); row.Add("MakerCode", sub["MakerCode"].ToString()); row.Add("ManageMakerCode", sub["ManageMakerCode"].ToString()); row.Add("Name", sub["Name"].ToString()); row.Add("LaveNum", sub["LaveNum"].ToString()); row.Add("c1", sub["c1"].ToString()); row.Add("c2", sub["c2"].ToString()); row.Add("c3", sub["c3"].ToString()); diclist.Add(row); } Dictionary result = new Dictionary(); result.Add("Status", "1"); result.Add("Info", "分仓统计数据报表-" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + ".xlsx"); result.Add("Obj", diclist); Dictionary ReturnFields = new Dictionary(); ReturnFields.Add("StoreName", "仓库名称"); ReturnFields.Add("StoreNo", "仓库编号"); ReturnFields.Add("Mobile", "仓库所属人手机号"); ReturnFields.Add("RealName", "仓库所属人名称"); ReturnFields.Add("MakerCode", "仓库所属人编码"); ReturnFields.Add("ManageMakerCode", "仓库管理员编码"); ReturnFields.Add("Name", "品牌"); ReturnFields.Add("LaveNum", "当前库存"); ReturnFields.Add("c1", "出库(调拨)量"); ReturnFields.Add("c2", "出货(发货到创客)量"); ReturnFields.Add("c3", "激活量"); result.Add("Fields", ReturnFields); AddSysLog("0", "StoreHouse", "StatExportExcel"); return Json(result); } #endregion #region 同步数据 /// /// 同步数据 /// /// public string SycnData(int Id = 0) { if (Id > 0) { DataTable dt = OtherMySqlConn.dtable("select Id,(select count(Id) from PosMachinesTwo where StoreId=s.Id and BuyUserId=0 and PreUserId=0 and Status>-1) as CurLaveNum from StoreHouse s where Id=" + Id); if (dt.Rows.Count > 0) { int CurLaveNum = int.Parse(function.CheckInt(dt.Rows[0]["CurLaveNum"].ToString())); StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == Id); if (store != null) { store.LaveNum = CurLaveNum; db.SaveChanges(); } } } else { DataTable dt = OtherMySqlConn.dtable("select s.Id,LaveNum,count(p.Id) as CurLaveNum from StoreHouse s left join PosMachinesTwo p on s.Id=p.StoreId and s.BrandId=p.BrandId and p.BuyUserId=0 and p.PreUserId=0 and p.Status>-1 group by s.Id HAVING LaveNum!=count(p.Id)"); foreach (DataRow dr in dt.Rows) { int StoreId = int.Parse(function.CheckInt(dr["Id"].ToString())); int CurLaveNum = int.Parse(function.CheckInt(dr["CurLaveNum"].ToString())); StoreHouse store = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId); if (store != null) { store.LaveNum = CurLaveNum; db.SaveChanges(); } } } AddSysLog(Id, "StoreHouse", "SycnData"); db.SaveChanges(); return "success"; } #endregion #region 重置仓库额度 /// /// 重置仓库额度 /// /// public string SycnAmountData(int Id = 0) { if (Id > 0) { var storeHouse = db.StoreHouse.FirstOrDefault(m => m.Id == Id && m.Sort == 0) ?? new StoreHouse(); RedisDbconn.Instance.AddList("ResetStoreReserveQueue", storeHouse.UserId); } AddSysLog(Id, "StoreHouse", "SycnAmountData"); db.SaveChanges(); return "success"; } #endregion #region 设置缓存 private void SetRedis(int Id, int UserId) { if (UserId > 0) { } } #endregion #region 关联分仓 /// /// 关联分仓 /// /// public IActionResult BatchSetting(string right, string StoreIds) { string[] idlist = StoreIds.Split(new char[] { ',' }); decimal totalAmount = 0; decimal deposit = 0; var userIds = ""; foreach (var item in idlist) { int StoreId = int.Parse(item); var amount = 0; var tamount = 0; var storeInfo = db.StoreHouse.FirstOrDefault(m => m.Id == StoreId); var posCount = db.PosMachinesTwo.Count(m => m.StoreId == StoreId && m.UserId == 0 && m.BuyUserId == 0 && m.OpId == 0); var BrandId = int.Parse(storeInfo.BrandId); var brandInfo = db.KqProducts.FirstOrDefault(m => m.Id == BrandId); if (brandInfo.Name.Contains("电签")) { amount = 100; } if (brandInfo.Name.Contains("大POS")) { amount = 150; } if (!userIds.Contains(storeInfo.UserId + ",")) { userIds += storeInfo.UserId + ","; deposit += storeInfo.Deposit; } tamount = amount * posCount;//扣减运营中心该品牌机具额度 totalAmount += tamount;//关联分仓打标记运营中心所需额度 } ViewBag.RightInfo = RightInfo; ViewBag.right = right; ViewBag.totalAmount = "关联分仓机具需要额度：" + totalAmount; ViewBag.deposit = "关联分仓获得押金：" + deposit; return View(); } #endregion } }