public FileResult ExcelExport(string UserName, DateTime Date, int show)
{
if (Date == null)
{
return(null);
}
var fileName = "跑批" + Date.ToString("yyyyMMdd");
string file = Server.MapPath("/template") + "\\CheckUser.xls";
var export = new ExcelExport();
var workbook = export.GetWorkbook(file);
//var CellStyle = export.CellStyle(workbook);
// var UserList = this.Entity.Users.ToList();
List <CheckUserModel> UsersList = new List <CheckUserModel>();
int uid = 0;
#region 商户汇总
if (true)
{
DataTable table = new DataTable();
DataRow row = null;
var query = this.Entity.CheckUser.AsQueryable();
if (!UserName.IsNullOrEmpty())
{
uid = this.Entity.Users.Where(o => o.UserName == UserName).Select(o => o.Id).FirstOrDefault();
query = query.Where(o => o.UId == uid);
}
if (show == 1)
{
query = query.Where(o => o.NowMoney != o.CheckMoney || o.NowFrozen != o.CheckFrozen);
}
var CheckUserList = query.Where(o => o.TaskDate == Date).ToList();
var uids = CheckUserList.Select(o => o.UId).ToList();
UsersList = this.Entity.Users.Where(o => uids.Contains(o.Id))
.Select(o => new CheckUserModel()
{
Id = o.Id, TrueName = o.TrueName, UserName = o.UserName
}).ToList();
// 创建 datatable
table.Columns.Add(new DataColumn("商户", typeof(string)));
table.Columns.Add(new DataColumn("手机号", typeof(string)));
table.Columns.Add(new DataColumn("原始余额", typeof(decimal)));
table.Columns.Add(new DataColumn("原始冻结金额", typeof(decimal)));
table.Columns.Add(new DataColumn("模拟余额", typeof(decimal)));
table.Columns.Add(new DataColumn("模拟冻结金额", typeof(decimal)));
table.Columns.Add(new DataColumn("记录余额", typeof(decimal)));
table.Columns.Add(new DataColumn("记录冻结金额", typeof(decimal)));
table.Columns.Add(new DataColumn("差额余额", typeof(decimal)));
table.Columns.Add(new DataColumn("差额冻结金额", typeof(decimal)));
// 填充数据
foreach (var item in CheckUserList)
{
var u = UsersList.FirstOrDefault(o => o.Id == item.UId);
row = table.NewRow();
row[0] = u.TrueName;
row[1] = u.UserName;
row[2] = item.PastMoney.ToString("n2");
row[3] = item.PastFrozen.ToString("n2");
row[4] = item.CheckMoney.ToString("n2");
row[5] = item.CheckFrozen.ToString("n2");
row[6] = item.NowMoney.ToString("n2");
row[7] = item.NowFrozen.ToString("n2");
row[8] = (item.CheckMoney - item.NowMoney).ToString("n2");
row[9] = (item.CheckFrozen - item.NowFrozen).ToString("n2");
table.Rows.Add(row);
}
var Sheet = workbook.GetSheetAt(0);
Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd"));
int Befor = 3;
foreach (DataRow item in table.Rows)
{
var dataRow = Sheet.CreateRow(Befor);
foreach (DataColumn column in table.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = item[column].ToString();
newCell = export.Assign(newCell, column.DataType.ToString(), drValue);
}
Befor++;
}
}
#endregion
#region 跑批过程
if (true)
{
DataTable table = new DataTable();
DataRow row = null;
var CheckUserMoneyList = this.Entity.CheckUserMoney.Where(o => o.TaskDate == Date).OrderBy(o => o.Progress).ToList();
// 创建 datatable
table.Columns.Add(new DataColumn("商户", typeof(string)));
table.Columns.Add(new DataColumn("手机号", typeof(string)));
table.Columns.Add(new DataColumn("操作", typeof(string)));
table.Columns.Add(new DataColumn("变动余额", typeof(decimal)));
table.Columns.Add(new DataColumn("变动冻结金额", typeof(decimal)));
table.Columns.Add(new DataColumn("运算操作金额", typeof(decimal)));
table.Columns.Add(new DataColumn("记录操作金额", typeof(decimal)));
table.Columns.Add(new DataColumn("差额", typeof(decimal)));
// 填充数据
foreach (var item in CheckUserMoneyList)
{
var u = UsersList.FirstOrNew(o => o.Id == item.UId);
row = table.NewRow();
row[0] = u.TrueName;
row[1] = u.UserName;
row[2] = CheckExtensions.GetProgressName(item.Progress);
row[3] = item.ChangeMoney.ToString("n2");
row[4] = item.ChangeFrozen.ToString("n2");
row[5] = item.ProgressMoney.ToString("n2");
row[6] = item.RecordMoney.ToString("n2");
row[7] = (item.ProgressMoney - item.RecordMoney).ToString("n2");
table.Rows.Add(row);
}
var Sheet = workbook.GetSheetAt(1);
Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd"));
int Befor = 2;
foreach (DataRow item in table.Rows)
{
var dataRow = Sheet.CreateRow(Befor);
foreach (DataColumn column in table.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = item[column].ToString();
newCell = export.Assign(newCell, column.DataType.ToString(), drValue);
}
Befor++;
}
}
#endregion
#region 异常订单
if (true)
{
DataTable table = new DataTable();
DataRow row = null;
var CheckResultList = this.Entity.CheckResult.Where(o => o.TaskDate == Date).OrderByDescending(o => o.Id).ToList();
// 创建 datatable
table.Columns.Add(new DataColumn("商户", typeof(string)));
table.Columns.Add(new DataColumn("手机号", typeof(string)));
table.Columns.Add(new DataColumn("订单号", typeof(string)));
table.Columns.Add(new DataColumn("金额", typeof(decimal)));
table.Columns.Add(new DataColumn("操作", typeof(string)));
table.Columns.Add(new DataColumn("异常内容", typeof(string)));
// 填充数据
foreach (var item in CheckResultList)
{
var u = UsersList.FirstOrNew(o => o.Id == item.UId);
row = table.NewRow();
row[0] = u.TrueName;
row[1] = u.UserName;
row[2] = item.TNum;
row[3] = item.Amount.ToString("n2");
row[4] = CheckExtensions.GetProgressName(item.CheckType);
row[5] = item.CheckMsg;
table.Rows.Add(row);
}
var Sheet = workbook.GetSheetAt(2);
Sheet.GetRow(0).GetCell(0).SetCellValue(Date.ToString("yyyy-MM-dd"));
int Befor = 2;
foreach (DataRow item in table.Rows)
{
var dataRow = Sheet.CreateRow(Befor);
foreach (DataColumn column in table.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = item[column].ToString();
newCell = export.Assign(newCell, column.DataType.ToString(), drValue);
}
Befor++;
}
}
#endregion
return(ExportExcelBase(workbook, fileName));
}
