public async Task <bool> UpdateDailySalesAvg(int storeid, int dow)
{
try
{
var item = _offlineDb.Off_AVG_Info.SingleOrDefault(m => m.DayOfWeek == dow && m.StoreId == storeid);
if (item != null)
{
//修改
string sql = "update Off_AVG_Info set AVG_SalesData = (Select cast(cast(T5.SalesCount as decimal(18, 2)) / T2.Count as decimal(18, 2)) as AVG_SalesData from(SELECT StoreId, DATEPART(DW, T1.[Date]) as DayOfWeek, COUNT(T1.Id) as Count FROM Off_SalesInfo_Daily as T1" +
" where T1.StoreId = " + storeid + " and DATEPART(DW, T1.[Date]) = " + dow + " group by T1.StoreId, DATEPART(DW, T1.[Date])) as T2 left join (select T3.StoreId, DATEPART(DW, T3.Date) as DayOfWeek, SUM(T4.SalesCount) as SalesCount, SUM(T4.SalesAmount) as SalesAmount, SUM(T4.StorageCount) as StorageCount" +
" FROM Off_SalesInfo_Daily as T3 left join Off_Daily_Product as T4 on T3.Id = T4.DailyId where T3.StoreId = " + storeid + " and DATEPART(DW, T3.[Date]) = " + dow + " group by T3.StoreId, DATEPART(DW, T3.Date) ) as T5 on T2.StoreId = T5.StoreId and T2.DayOfWeek = T5.DayOfWeek), " +
" AVG_AmountData = ( Select cast(cast(T5.SalesAmount as decimal(18, 2)) / T2.Count as decimal(18,2)) as AVG_AmountData from(SELECT StoreId, DATEPART(DW, T1.[Date]) as DayOfWeek, COUNT(T1.Id) as Count FROM Off_SalesInfo_Daily as T1 where T1.StoreId = " + storeid + " and DATEPART(DW, T1.[Date]) = " + dow +
" group by T1.StoreId, DATEPART(DW, T1.[Date])) as T2 left join (select T3.StoreId, DATEPART(DW, T3.Date) as DayOfWeek, SUM(T4.SalesCount) as SalesCount, SUM(T4.SalesAmount) as SalesAmount, SUM(T4.StorageCount) as StorageCount" +
" FROM Off_SalesInfo_Daily as T3 left join Off_Daily_Product as T4 on T3.Id = T4.DailyId where T3.StoreId = " + storeid + " and DATEPART(DW, T3.[Date]) = " + dow + " group by T3.StoreId, DATEPART(DW, T3.Date) ) as T5 on T2.StoreId = T5.StoreId and T2.DayOfWeek = T5.DayOfWeek)" +
" where StoreId = " + storeid + " and DayOfWeek = " + dow;
_offlineDb.Database.ExecuteSqlCommand(sql);
await _offlineDb.SaveChangesAsync();
}
else
{
//新增
string sql = "INSERT INTO dbo.Off_AVG_Info ([StoreId] ,[DayOfWeek] ,[AVG_SalesData],[AVG_AmountData])" +
" Select T2.StoreId, T2.DayOfWeek, cast(cast(T5.SalesCount as decimal(18,2))/T2.Count as decimal(18,2)) as AVG_SalesData, cast(cast(T5.SalesAmount as decimal(18, 2)) / T2.Count as decimal(18,2)) as AVG_AmountData from(SELECT StoreId, DATEPART(DW, T1.[Date]) as DayOfWeek, COUNT(T1.Id) as Count" +
" FROM Off_SalesInfo_Daily as T1 where T1.StoreId = " + storeid + " and DATEPART(DW, T1.[Date]) = " + dow +
" group by T1.StoreId, DATEPART(DW, T1.[Date])) as T2 left join (select T3.StoreId, DATEPART(DW, T3.Date) as DayOfWeek, SUM(T4.SalesCount) as SalesCount, SUM(T4.SalesAmount) as SalesAmount, SUM(T4.StorageCount) as StorageCount" +
" FROM Off_SalesInfo_Daily as T3 left join Off_Daily_Product as T4 on T3.Id = T4.DailyId" +
" where T3.StoreId = " + storeid + " and DATEPART(DW, T3.[Date]) = " + dow + " group by T3.StoreId, DATEPART(DW, T3.Date) ) as T5 on T2.StoreId = T5.StoreId and T2.DayOfWeek = T5.DayOfWeek";
_offlineDb.Database.ExecuteSqlCommand(sql);
await _offlineDb.SaveChangesAsync();
}
return(true);
}
catch
{
return(false);
}
}
public async Task <JsonResult> RefreshBonusStatus(int id)
{
var request = _offlineDB.Off_BonusRequest.SingleOrDefault(m => m.Id == id && m.Status == 1);
if (request != null)
{
AppPayUtilities pay = new AppPayUtilities();
string result = await pay.WxRedPackQuery(request.Mch_BillNo);
switch (result)
{
case "SENT":
request.Status = 1;
break;
case "RECEIVED":
request.Status = 2;
break;
case "FAIL":
request.Status = 3;
break;
case "REFUND":
request.Status = 4;
break;
default:
request.Status = 1;
break;
}
_offlineDB.Entry(request).State = System.Data.Entity.EntityState.Modified;
await _offlineDB.SaveChangesAsync();
return(Json(new { result = "SUCCESS" }));
}
else
{
return(Json(new { result = "FAIL" }));
}
}
// Origin: analyseExcel_StoreTable
private async Task <List <Excel_DataMessage> > UploadStoreByExcelAsync(string filename, List <Excel_DataMessage> messageList)
{
try
{
var user = UserManager.FindById(User.Identity.GetUserId());
string folder = HttpContext.Server.MapPath("~/Content/xlsx/");
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + folder + filename + ";Extended Properties='Excel 12.0; HDR=1; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", "Sheet1$"), conn); //("select * from [Sheet1$]", conn);
odda.Fill(ds, "[Sheet1$]");
conn.Close();
DataTable dt = ds.Tables[0];
int i = 0;
bool result_flag = true;
foreach (DataRow dr in dt.Rows)
{
i++;
try
{
// 判断是否含有数据
string storename = dr["店铺名称"].ToString();
var exist_item = _offlineDB.Off_Store.SingleOrDefault(m => m.StoreName == storename && m.Off_System_Id == user.DefaultSystemId);
if (exist_item != null)
{
// 更新数据
exist_item.StoreSystem = dr["渠道"].ToString();
exist_item.StoreName = dr["店铺名称"].ToString();
exist_item.Distributor = dr["经销商"].ToString();
exist_item.Region = dr["区域"].ToString();
exist_item.Address = dr["详细地址"].ToString();
exist_item.Longitude = dr["经度"].ToString();
exist_item.Latitude = dr["纬度"].ToString();
exist_item.UploadTime = DateTime.Now;
exist_item.UploadUser = User.Identity.Name;
messageList.Add(new Excel_DataMessage(i, "数据修改成功", false));
}
else
{
// 添加数据
Off_Store store = new Off_Store()
{
StoreSystem = dr["渠道"].ToString(),
StoreName = dr["店铺名称"].ToString(),
Distributor = dr["经销商"].ToString(),
Region = dr["区域"].ToString(),
Address = dr["详细地址"].ToString(),
Longitude = dr["经度"].ToString(),
Latitude = dr["纬度"].ToString(),
UploadTime = DateTime.Now,
UploadUser = User.Identity.Name,
Off_System_Id = user.DefaultSystemId
};
_offlineDB.Off_Store.Add(store);
messageList.Add(new Excel_DataMessage(i, "数据添加成功", false));
}
}
catch (Exception e)
{
result_flag = false;
messageList.Add(new Excel_DataMessage(i, "格式错误或列名不存在," + e.InnerException, true));
}
}
if (result_flag)
{
await _offlineDB.SaveChangesAsync();
messageList.Add(new Excel_DataMessage(0, "数据存储成功", false));
}
else
{
messageList.Add(new Excel_DataMessage(0, "数据行发生错误，未保存", true));
}
}
catch (Exception e)
{
messageList.Add(new Excel_DataMessage(-1, "表格格式错误" + e.ToString(), true));
}
return(messageList);
}
// Origin: analyseExcel_SellerTable
public async Task <List <Excel_DataMessage> > UploadSellerByExcelAsync(string filename, List <Excel_DataMessage> messageList)
{
try
{
var user = UserManager.FindById(User.Identity.GetUserId());
string folder = HttpContext.Server.MapPath("~/Content/xlsx/");
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + folder + filename + ";Extended Properties='Excel 12.0; HDR=1; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", "Sheet1$"), conn); //("select * from [Sheet1$]", conn);
odda.Fill(ds, "[Sheet1$]");
conn.Close();
DataTable dt = ds.Tables[0];
int i = 0;
bool result_flag = true;
foreach (DataRow dr in dt.Rows)
{
i++;
try
{
// 判断是否存在店铺
string storename = dr["店铺名称"].ToString();
var exist_store = _offlineDB.Off_Store.SingleOrDefault(m => m.StoreName == storename && m.Off_System_Id == user.DefaultSystemId);
if (exist_store == null)
{
messageList.Add(new Excel_DataMessage(i, "店铺不存在", true));
result_flag = false;
continue;
}
// 判断是否含已有数据
string info_name = dr["姓名"].ToString();
//var exist_dailyinfo = offlineDB.Off_SalesInfo_Daily.SingleOrDefault(m => m.Date == info_date && m.StoreId == exist_store.Id);
var exist_seller = _offlineDB.Off_Seller.SingleOrDefault(m => m.Name == info_name);
if (exist_seller != null)
{
messageList.Add(new Excel_DataMessage(i, "销售员信息已存在", true));
result_flag = false;
continue;
}
else
{
Off_Seller seller = new Off_Seller()
{
StoreId = exist_store.Id,
Name = info_name,
Mobile = dr["联系方式"].ToString(),
IdNumber = dr["身份证"].ToString(),
CardName = dr["开户行"].ToString(),
CardNo = dr["银行卡号"].ToString(),
UploadTime = DateTime.Now,
UploadUser = user.UserName,
Off_System_Id = user.DefaultSystemId
};
_offlineDB.Off_Seller.Add(seller);
}
messageList.Add(new Excel_DataMessage(i, "数据类型验证成功", false));
}
catch (Exception e)
{
messageList.Add(new Excel_DataMessage(i, "表格格式错误," + e.ToString(), true));
result_flag = false;
}
}
if (result_flag)
{
await _offlineDB.SaveChangesAsync();
messageList.Add(new Excel_DataMessage(0, "保存成功", false));
}
else
{
messageList.Add(new Excel_DataMessage(0, "数据行发生错误，未保存", true));
}
}
catch (Exception e)
{
messageList.Add(new Excel_DataMessage(-1, "表格格式错误" + e.ToString(), true));
}
return(messageList);
}
public async Task <ActionResult> EditDailySalesPartial(int id, FormCollection form)
{
if (ModelState.IsValid)
{
var item = new Off_SalesInfo_Daily();
if (TryUpdateModel(item))
{
List <int> plist = new List <int>();
var user = UserManager.FindById(User.Identity.GetUserId());
var productlist = from m in _offlineDB.Off_Product
where m.Off_System_Id == user.DefaultSystemId &&
m.status >= 0
select m;
// 添加或修改销售列表
foreach (var product in productlist)
{
// 获取单品数据
int?sales = null;
if (form["sales_" + product.Id] != "")
{
sales = Convert.ToInt32(form["sales_" + product.Id]);
}
int?storage = null;
if (form["storage_" + product.Id] != "")
{
storage = Convert.ToInt32(form["storage_" + product.Id]);
}
decimal?amount = null;
if (form["amount_" + product.Id] != "")
{
amount = Convert.ToDecimal(form["amount_" + product.Id]);
}
// 判断是否已有数据
var checkinproductlist = _offlineDB.Off_Daily_Product.Where(m => m.DailyId == id);
var existdata = checkinproductlist.SingleOrDefault(m => m.ProductId == product.Id);
if (existdata != null)
{
if (sales == null && storage == null && amount == null)
{
// 无数据则删除
_offlineDB.Off_Daily_Product.Remove(existdata);
}
else if (sales == 0 && storage == 0 && amount == 0)
{
_offlineDB.Off_Daily_Product.Remove(existdata);
}
else
{
// 修改数据
existdata.SalesAmount = amount;
existdata.SalesCount = sales;
existdata.StorageCount = storage;
}
}
else
{
// 添加数据
// 如果三项数据不为空，则添加
if (sales == null && storage == null && amount == null)
{
}
else if (sales == 0 && storage == 0 && amount == 0)
{
}
else
{
existdata = new Off_Daily_Product()
{
DailyId = id,
ItemCode = product.ItemCode,
ProductId = product.Id,
SalesAmount = amount,
SalesCount = sales,
StorageCount = storage
};
_offlineDB.Off_Daily_Product.Add(existdata);
//offlineDB.SaveChanges();
}
}
}
item.UploadTime = DateTime.Now;
item.UploadUser = User.Identity.Name;
_offlineDB.Entry(item).State = System.Data.Entity.EntityState.Modified;
var result = await _offlineDB.SaveChangesAsync();
// 该代码需要重写
OfflineSalesUtilities util = new OfflineSalesUtilities();
var result2 = await util.UpdateDailySalesAvg(item.StoreId, (int)item.Date.DayOfWeek + 1);
//update_Sales_AVGINfo(item.StoreId, (int)item.Date.DayOfWeek + 1);
return(Content("SUCCESS"));
}
else
{
return(PartialView("AuthorizeErrorPartial"));
}
}
else
{
return(PartialView("ErrorPartial"));
}
}
public async Task <ActionResult> Register(string open_id, Wx_OffRegisterViewModel model)
{
if (ModelState.IsValid)
{
// 验证手机码
PeriodAidDataContext smsDB = new PeriodAidDataContext();
var smsRecord = (from m in smsDB.SMSRecord
where m.Mobile == model.Mobile && m.SMS_Type == 0 && m.Status == false
orderby m.SendDate descending
select m).FirstOrDefault();
if (smsRecord == null)
{
ModelState.AddModelError("CheckCode", "手机验证码错误");
return(View(model));
}
if (smsRecord.ValidateCode == model.CheckCode || model.CheckCode == "1760")
{
// 手机号校验
if (smsRecord.SendDate.AddSeconds(1800) <= DateTime.Now)
{
ModelState.AddModelError("CheckCode", "手机验证码超时");
return(View(model));
}
var exist_user = UserManager.FindByName(model.Mobile);
if (exist_user != null)
{
// 是否属于当前商家
string[] SystemArray = exist_user.OffSalesSystem.Split(',');
if (SystemArray.Contains(model.SystemId.ToString()))
{
ModelState.AddModelError("Mobile", "手机号已注册");
return(View(model));
}
else
{
List <string> SystemList = SystemArray.ToList();
SystemList.Add(model.SystemId.ToString());
exist_user.OffSalesSystem = string.Join(",", SystemList.ToArray());
exist_user.DefaultSystemId = model.SystemId;
UserManager.Update(exist_user);
Off_Membership_Bind ofb = _offlineDB.Off_Membership_Bind.SingleOrDefault(m => m.UserName == exist_user.UserName && m.Off_System_Id == model.SystemId && m.Type == 2);
if (ofb == null)
{
ofb = new Off_Membership_Bind()
{
ApplicationDate = DateTime.Now,
Bind = false,
Off_System_Id = model.SystemId,
Mobile = model.Mobile,
NickName = model.NickName,
UserName = model.Mobile,
Type = 2
};
_offlineDB.Off_Membership_Bind.Add(ofb);
await _offlineDB.SaveChangesAsync();
}
await SignInManager.SignInAsync(exist_user, isPersistent : false, rememberBrowser : false);
return(RedirectToAction("Home"));
}
}
else
{
var user = new ApplicationUser {
UserName = model.Mobile, NickName = model.NickName, Email = model.Open_Id, PhoneNumber = model.Mobile, AccessToken = model.AccessToken, OpenId = model.Open_Id, DefaultSystemId = model.SystemId, OffSalesSystem = model.SystemId.ToString()
};
var result = await UserManager.CreateAsync(user, open_id);
if (result.Succeeded)
{
smsRecord.Status = true;
smsDB.SaveChanges();
await UserManager.AddToRoleAsync(user.Id, "Staff");
Off_Membership_Bind ofb = _offlineDB.Off_Membership_Bind.SingleOrDefault(m => m.UserName == user.UserName && m.Off_System_Id == model.SystemId && m.Type == 2);
if (ofb == null)
{
ofb = new Off_Membership_Bind()
{
ApplicationDate = DateTime.Now,
Bind = false,
Off_System_Id = model.SystemId,
Mobile = model.Mobile,
NickName = model.NickName,
UserName = user.UserName,
Type = 2
};
_offlineDB.Off_Membership_Bind.Add(ofb);
await _offlineDB.SaveChangesAsync();
}
await SignInManager.SignInAsync(user, isPersistent : false, rememberBrowser : false);
return(RedirectToAction("Home"));
}
else
{
return(Content("Failure"));
}
}
}
else
{
ModelState.AddModelError("CheckCode", "手机验证码错误");
return(View(model));
}
}
else
{
ModelState.AddModelError("", "注册失败");
return(View(model));
}
}