public static IPageOfItems <T> Since <T>(this IPageOfItems <T> pageOfItems, Func <T, DateTime> getCompareDateTime, DateTime?sinceDate)
{
if (sinceDate.HasValue)
{
return(new PageOfItems <T>(pageOfItems.Where(i => getCompareDateTime(i) > sinceDate.Value), pageOfItems.PageIndex, pageOfItems.PageSize, pageOfItems.TotalItemCount));
}
return(pageOfItems);
}
public ActionResult Index(QRCode QRCode, EFPagingInfo <QRCode> p, int IsFirst = 0)
{
if (IsFirst == 0)
{
PageOfItems <QRCode> QRCodeList1 = new PageOfItems <QRCode>(new List <QRCode>(), 0, 10, 0, new Hashtable());
ViewBag.QRCodeList = QRCodeList1;
ViewBag.QRCode = QRCode;
ViewBag.UsersList = new List <Users>();
return(View());
}
if (!QRCode.UId.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.UId == QRCode.UId);
p.PageSize = 99999;
}
if (!QRCode.Num.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.Num == QRCode.Num);
}
if (!QRCode.State.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.State == (QRCode.State == 99 ? 0 : QRCode.State));
}
p.OrderByList.Add("Id", "DESC");
IPageOfItems <QRCode> QRCodeList = Entity.Selects <QRCode>(p);
ViewBag.QRCodeList = QRCodeList;
ViewBag.QRCode = QRCode;
IList <int> Ids = new List <int>();
foreach (var P in QRCodeList.Where(n => n.UId > 0))
{
Ids.Add(P.UId);
}
IList <Users> UsersList = new List <Users>();
if (Ids.Count() > 0 && QRCode.UId.IsNullOrEmpty())
{
UsersList = Entity.Users.Where(n => Ids.Contains(n.Id)).ToList();
}
ViewBag.UsersList = UsersList;
return(View());
}
public void XLSDo(OrderHouse OrderHouse, EFPagingInfo <OrderHouse> p)
{
if (OrderHouse.AddTime.IsNullOrEmpty())
{
OrderHouse.AddTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
}
if (OrderHouse.FTime.IsNullOrEmpty())
{
//OrderHouse.FTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
OrderHouse.FTime = DateTime.Now;
}
if (!OrderHouse.AddTime.IsNullOrEmpty() && !OrderHouse.FTime.IsNullOrEmpty())
{
// OrderHouse.FTime = ((DateTime)OrderHouse.FTime).AddDays(1);
p.SqlWhere.Add(f => f.AddTime > OrderHouse.AddTime && f.AddTime < OrderHouse.FTime);
}
//if (!OrderHouse.UId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.UId == OrderHouse.UId); }
if (!OrderHouse.HouseOwner.IsNullOrEmpty())
{
IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(OrderHouse.HouseOwner) || n.NeekName.Contains(OrderHouse.HouseOwner) || n.UserName == OrderHouse.HouseOwner).ToList();
List <int> UIds = new List <int>();
foreach (var pp in UList)
{
UIds.Add(pp.Id);
}
p.SqlWhere.Add(f => UIds.Contains(f.UId));
}
if (!OrderHouse.OId.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.OId == OrderHouse.OId);
}
if (!OrderHouse.Agent.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.Agent == OrderHouse.Agent);
}
if (!OrderHouse.TrunType.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.TrunType == (OrderHouse.TrunType == 99 ? 0 : OrderHouse.TrunType));
}
if (!OrderHouse.AId.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.AId == OrderHouse.AId);
}
if (!OrderHouse.FId.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.FId == OrderHouse.FId);
}
if (!OrderHouse.AgentState.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.AgentState == OrderHouse.AgentState);
}
p.SqlWhere.Add(f => f.OrderState == 2 && f.PayState == 1 && f.FState == 0);
p.PageSize = 99999999;
p.OrderByList.Add("Id", "DESC");
IPageOfItems <OrderHouse> OrderHouseList = Entity.Selects <OrderHouse>(p);
if (OrderHouseList.Count() > 0)
{
//当前导出缩合表，民生跨行，工行，民生同行
int BankIdMS = 13; //民生ID
int BankIdGS = 1; //工商Id
string BankNameMS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdMS).Name;
string BankNameGS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdGS).Name;
//加载对应银行模板
string tempname = "bank.xlsx";
string file = Server.MapPath("/template") + "\\" + tempname;
ExcelPackage package = new ExcelPackage(new FileInfo(file), true);
//民生
IList <OrderHouse> ListMS = OrderHouseList.Where(n => n.Bank == BankNameMS).ToList();
//工商
IList <OrderHouse> ListGS = OrderHouseList.Where(n => n.Bank == BankNameGS).ToList();
//其它
IList <OrderHouse> ListOT = OrderHouseList.Where(n => n.Bank != BankNameMS && n.Bank != BankNameGS).ToList();
//设置数据开始行
int Befor = 0;
int maxCol = 0;//最大列数
if (ListOT.Count > 0)
{
//加载模板第一张表
var sheet = package.Workbook.Worksheets[1];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListOT.Count();
Befor = 5; //民生他行从5开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
foreach (var item in ListOT)
{
string zdlx = "2";
decimal Money = item.PayMoney;
TotalMoney += Money;
//制单类型
cells["A" + i].Value = zdlx.ToString();
//企业自制凭证号
cells["B" + i].Value = item.Id.ToString().PadLeft(8, '0');
//客户号
cells["C" + i].Value = "2200488356";
//预约标志
cells["D" + i].Value = "0";
//付款账号
cells["E" + i].Value = "613111800";
//交易金额
cells["F" + i].Value = Money;
//收款账号
cells["G" + i].Value = item.CardNum;
//收款人姓名
cells["H" + i].Value = item.HouseOwner;
//收款账户类型
cells["I" + i].Value = "1";
//子客户号
cells["J" + i].Value = "";
//子付款账号
cells["K" + i].Value = "";
//子付款账户名
cells["L" + i].Value = "";
//子付款账户开户行名
cells["M" + i].Value = "";
//用途
cells["N" + i].Value = "房租";
//汇路
cells["O" + i].Value = Money > 50000 ? "7" : "6";
//是否通知收款人
cells["P" + i].Value = "0";
//手机
cells["Q" + i].Value = item.Mobile;
//邮箱
cells["R" + i].Value = "";
//支付行号&支付行名称
cells["S" + i].Value = item.Bin + "&" + item.Deposit;
maxCol = 19;
i++;
}
i--;
//审核方式（文件类型）
cells["B1"].Value = "1";
//总金额
cells["B2"].Value = TotalMoney.ToString("F2");
//总交易数
cells["B3"].Value = Rows.ToString();
sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
}
if (ListMS.Count > 0)
{
//加载模板第二张表
var sheet = package.Workbook.Worksheets[2];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListMS.Count();
Befor = 9; //民生本行从9开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
foreach (var item in ListMS)
{
decimal Money = item.PayMoney;
TotalMoney += Money;
//收款账号
cells["A" + i].Value = item.CardNum;
//交易金额
cells["B" + i].Value = Money.ToString("F2");
//收款人姓名
cells["C" + i].Value = item.HouseOwner;
//用途
cells["D" + i].Value = "349";
maxCol = 4;
i++;
}
i--;
//总金额
cells["B6"].Value = TotalMoney.ToString();
//总交易数
cells["B7"].Value = Rows.ToString();
}
if (ListGS.Count > 0)
{
//加载模板第三张表
var sheet = package.Workbook.Worksheets[3];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListGS.Count();
Befor = 2; //工行本行从2开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
IList <BasicProvince> PList = Entity.BasicProvince.Where(n => n.State == 1).ToList();
IList <BasicCity> CList = Entity.BasicCity.Where(n => n.State == 1).ToList();
foreach (var item in ListGS)
{
decimal Money = item.PayMoney;
TotalMoney += Money;
//币种
cells["A" + i].Value = "RMB";
//日期
cells["B" + i].Value = DateTime.Now.ToString("yyyyMMdd");
//明细标志
cells["C" + i].Value = "";
//顺序号
cells["D" + i].Value = i - Befor + 1;
//付款账号开户行
cells["E" + i].Value = "工商银行";
//付款账号
cells["F" + i].Value = "4000051709100125887";
//付款账号名称
cells["G" + i].Value = "好付支付（深圳）有限公司";
//收款账号开户行
cells["H" + i].Value = "工商银行";
//收款账号省份
cells["I" + i].Value = PList.FirstOrNew(n => n.Id == item.Province).Name;
//收款账号地市
cells["J" + i].Value = CList.FirstOrNew(n => n.Id == item.City).Name;
//收款账号地区码
cells["K" + i].Value = "";
//收款账号
cells["L" + i].Value = item.CardNum;
//收款账号名称
cells["M" + i].Value = item.HouseOwner;
//金额
cells["N" + i].Value = Money.ToString("F2");
//汇款用途
cells["O" + i].Value = "房租";
//备注信息
cells["P" + i].Value = "";
//汇款方式
cells["Q" + i].Value = 1;
//收款账户短信通知手机号码
cells["R" + i].Value = "";
//自定义序号
cells["S" + i].Value = item.Id;
maxCol = 19;
i++;
}
i--;
}
//无数据表删除
if (ListGS.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[3]);
}
if (ListMS.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[2]);
}
if (ListOT.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[1]);
}
Response.BinaryWrite(package.GetAsByteArray());//输出
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx");
}
else
{
Response.Write("暂无符合条件数据");
}
}
public void XLSDo(Orders Orders, EFPagingInfo <Orders> p)
{
if (Orders.STime.IsNullOrEmpty())
{
Orders.STime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
}
if (Orders.ETime.IsNullOrEmpty())
{
Orders.ETime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
}
p.SqlWhere.Add(f => f.PayState == 1);
p.SqlWhere.Add(f => f.TState == 2);
if (!Orders.TNum.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.TNum == Orders.TNum);
}
//if (!Orders.UId.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.UId == Orders.UId); }
if (!Orders.TName.IsNullOrEmpty())
{
IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(Orders.TName) || n.NeekName.Contains(Orders.TName) || n.UserName == Orders.TName).ToList();
List <int> UIds = new List <int>();
foreach (var pp in UList)
{
UIds.Add(pp.Id);
}
p.SqlWhere.Add(f => UIds.Contains(f.UId));
}
if (!Orders.TType.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.TType == Orders.TType);
}
if (!Orders.Agent.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.Agent == Orders.Agent);
}
if (!Orders.AgentState.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.AgentState == (Orders.AgentState == 99 ? 0 : Orders.AgentState));
}
if (!Orders.STime.IsNullOrEmpty() && !Orders.ETime.IsNullOrEmpty())
{
DateTime ETime = Orders.ETime;
p.SqlWhere.Add(f => f.PayTime > Orders.STime && f.PayTime < ETime);
}
p.PageSize = 99999999;
p.OrderByList.Add("Id", "DESC");
IPageOfItems <Orders> OrdersList = Entity.Selects <Orders>(p);
IList <Orders> List = OrdersList.GroupBy(n => n.UId).Select(n => n.First()).ToList();
List <int> UId = new List <int>();
foreach (var pp in List)
{
UId.Add(pp.UId);
}
IList <Users> UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList();
IList <Orders> List1 = OrdersList.GroupBy(n => n.Agent).Select(n => n.First()).ToList();
List <int> AgentId = new List <int>();
foreach (var pp in List1)
{
AgentId.Add(pp.Agent);
}
if (this.TempData["SysAgentList"] == null)
{
Response.Write("当前页面数据已过期");
}
this.TempData.Keep("SysAgentList");
IList <SysAgent> SysAgentList = this.TempData["SysAgentList"] as IList <SysAgent>;//Entity.SysAgent.Where(n => n.State == 1 && AgentId.Contains(n.Id)).ToList();
if (SysAgentList != null && SysAgentList.Count > 0)
{
//加载对应模板
string tempname = "order.xlsx";
string file = Server.MapPath("/template") + "\\" + tempname;
ExcelPackage package = new ExcelPackage(new FileInfo(file), true);
//设置数据开始行
int Befor = 2;
int maxCol = 8;//最大列数
//加载模板第一张表
var sheet = package.Workbook.Worksheets[1];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
double TotalPrice = 0; //计算总金额
int Rows = OrdersList.Count() + SysAgentList.Count();
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
foreach (var item in SysAgentList)
{
IList <Orders> ListSub = OrdersList.Where(n => n.Agent == item.Id).ToList();
decimal AgentMoney = 0; //计算供应商金额
double AgentPrice = 0; //计算供应商金额
foreach (var s in ListSub)
{
sheet.Row(i).Height = 20;//设置行高
Users U = UsersList.FirstOrNew(n => n.Id == s.UId);
//分支机构
cells["A" + i].Value = item.Name;
//交易号
cells["B" + i].Value = s.TNum;
//交易商户
cells["C" + i].Value = U.TrueName;
//交易类型
cells["D" + i].Value = s.GetTTName();
//交易内容
cells["E" + i].Value = s.GetPayNameWithTName();
//交易金额
cells["F" + i].Value = s.Amoney;
//交易时间
cells["G" + i].Value = s.PayTime;
//结算金额
double agentmoney = s.GetAgentMoney(Entity);
cells["H" + i].Value = agentmoney;
AgentMoney += s.Amoney;
AgentPrice += agentmoney;
i++;
}
sheet.Row(i).Height = 28;//设置行高
//交易金额汇总
cells["F" + i].Value = AgentMoney;
//结算金额汇总
cells["H" + i].Value = AgentPrice;
Color bgColor = ColorTranslator.FromHtml("#DDDDDD");
Color fColor = ColorTranslator.FromHtml("#FF0000");
sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor);
sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 16;
sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true;
sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor);
TotalMoney += AgentMoney;
TotalPrice += AgentPrice;
i++;
}
Color bgColor_ = ColorTranslator.FromHtml("#7030A0");
Color fColor_ = ColorTranslator.FromHtml("#FFFFFF");
sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor_);
sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 20;
sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true;
sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor_);
sheet.Row(i).Height = 40;//设置行高
//交易金额汇总
cells["F" + i].Value = TotalMoney;
//结算金额汇总
cells["H" + i].Value = TotalPrice;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
sheet.Cells[Befor, 7, i, 7].Style.Numberformat.Format = "yyyy-mm-dd hh:mm";
sheet.Cells[Befor, 8, i, 8].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
Response.BinaryWrite(package.GetAsByteArray());//输出
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx");
}
else
{
Response.Write("暂无符合条件数据");
}
}
public void XLSDo(ApplyLoan ApplyLoan, EFPagingInfo <ApplyLoan> p)
{
if (ApplyLoan.STime.IsNullOrEmpty())
{
ApplyLoan.STime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
}
if (ApplyLoan.ETime.IsNullOrEmpty())
{
//ApplyLoan.ETime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
ApplyLoan.ETime = DateTime.Now;
}
if (!ApplyLoan.TrueName.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.TrueName == ApplyLoan.TrueName);
}
if (!ApplyLoan.Education.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.Education.Contains(ApplyLoan.Education));
}
if (!ApplyLoan.HasSheBao.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.HasSheBao == (ApplyLoan.HasSheBao == 99 ? 0 : ApplyLoan.HasSheBao));
}
if (!ApplyLoan.Marry.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.Marry == ApplyLoan.Marry);
}
if (!ApplyLoan.HasCar.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.HasCar == (ApplyLoan.HasCar == 99 ? 0 : ApplyLoan.HasCar));
}
if (!ApplyLoan.House.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.House == ApplyLoan.House);
}
if (!ApplyLoan.HasCredit.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.HasCredit == ApplyLoan.HasCredit);
}
if (!ApplyLoan.State.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.State == ApplyLoan.State);
}
if (!ApplyLoan.AgentPay.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.AgentPay == (ApplyLoan.AgentPay == 99 ? 0 : ApplyLoan.AgentPay));
}
if (!ApplyLoan.AgentAId.IsNullOrEmpty())
{
p.SqlWhere.Add(f => f.AgentAId == ApplyLoan.AgentAId);
}
if (!ApplyLoan.STime.IsNullOrEmpty() && !ApplyLoan.ETime.IsNullOrEmpty())
{
DateTime ETime = ApplyLoan.ETime;
p.SqlWhere.Add(f => f.PayTime > ApplyLoan.STime && f.PayTime < ETime);
}
p.SqlWhere.Add(f => f.PayState == 1);
p.PageSize = 99999999;
p.OrderByList.Add("Id", "DESC");
IPageOfItems <ApplyLoan> ApplyLoanList = Entity.Selects <ApplyLoan>(p);
IList <ApplyLoan> List = ApplyLoanList.GroupBy(n => n.AgentId).Select(n => n.First()).ToList();
List <int> AgentId = new List <int>();
foreach (var pp in List)
{
AgentId.Add(pp.AgentId);
}
IList <SysAgent> SysAgentList = Entity.SysAgent.Where(n => n.State == 1 && AgentId.Contains(n.Id)).ToList();
if (SysAgentList.Count > 0)
{
//加载对应银行模板
string tempname = "AgentLoan.xlsx";
string file = Server.MapPath("/template") + "\\" + tempname;
ExcelPackage package = new ExcelPackage(new FileInfo(file), true);
//设置数据开始行
int Befor = 2;
int maxCol = 6;//最大列数
//加载模板第一张表
var sheet = package.Workbook.Worksheets[1];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
decimal TotalPrice = 0; //计算总金额
int Rows = ApplyLoanList.Count() + SysAgentList.Count();
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
foreach (var item in SysAgentList)
{
IList <ApplyLoan> ListSub = ApplyLoanList.Where(n => n.AgentId == item.Id).ToList();
decimal AgentMoney = 0; //计算供应商金额
decimal AgentPrice = 0; //计算供应商金额
foreach (var s in ListSub)
{
sheet.Row(i).Height = 20;//设置行高
//分支机构
cells["A" + i].Value = item.Name;
//编号
cells["B" + i].Value = s.Id;
//姓名
cells["C" + i].Value = s.TrueName;
//售出金额
cells["D" + i].Value = s.Amoney;
//售出时间
cells["E" + i].Value = s.PayTime;
//结算金额
cells["F" + i].Value = s.AgentMoney;
AgentMoney += s.Amoney;
AgentPrice += s.AgentMoney;
i++;
}
sheet.Row(i).Height = 28;//设置行高
//交易金额汇总
cells["D" + i].Value = AgentMoney;
//结算金额汇总
cells["F" + i].Value = AgentPrice;
Color bgColor = ColorTranslator.FromHtml("#DDDDDD");
Color fColor = ColorTranslator.FromHtml("#FF0000");
sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor);
sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 16;
sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true;
sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor);
TotalMoney += AgentMoney;
TotalPrice += AgentPrice;
i++;
}
Color bgColor_ = ColorTranslator.FromHtml("#7030A0");
Color fColor_ = ColorTranslator.FromHtml("#FFFFFF");
sheet.Cells[i, 1, i, maxCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[i, 1, i, maxCol].Style.Fill.BackgroundColor.SetColor(bgColor_);
sheet.Cells[i, 1, i, maxCol].Style.Font.Size = 20;
sheet.Cells[i, 1, i, maxCol].Style.Font.Bold = true;
sheet.Cells[i, 1, i, maxCol].Style.Font.Color.SetColor(fColor_);
sheet.Row(i).Height = 40;//设置行高
//交易金额汇总
cells["D" + i].Value = TotalMoney;
//结算金额汇总
cells["F" + i].Value = TotalPrice;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
sheet.Cells[Befor, 4, i, 4].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
sheet.Cells[Befor, 5, i, 5].Style.Numberformat.Format = "yyyy-mm-dd hh:mm";
sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
Response.BinaryWrite(package.GetAsByteArray());//输出
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx");
}
else
{
Response.Write("暂无符合条件数据");
}
}
public void XLSDo(OrderCash OrderCash, EFPagingInfo <OrderCash> p, bool?IsShowSupAgent)
{
if (IsShowSupAgent == null)
{
IsShowSupAgent = false;
}
p = this.Condition(OrderCash, p, IsShowSupAgent);
p.PageSize = 99999999;
p.OrderByList.Add("Id", "ASC");
IPageOfItems <OrderCash> OrderCashList = Entity.Selects <OrderCash>(p);
if (OrderCashList.Count() > 0)
{
//当前银行名称
//string thisBankName = Entity.BasicBank.FirstOrNew(n => n.Id == OrderCash.Id).Name;
//当前导出缩合表，民生跨行，工行，民生同行
int BankIdMS = 13; //民生ID
int BankIdGS = 1; //工商Id
string BankNameMS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdMS).Name;
string BankNameGS = Entity.BasicBank.FirstOrNew(n => n.Id == BankIdGS).Name;
//加载对应银行模板
string tempname = "bank.xlsx";
string file = Server.MapPath("/template") + "\\" + tempname;
ExcelPackage package = new ExcelPackage(new FileInfo(file), true);
//民生
IList <OrderCash> ListMS = OrderCashList.Where(n => n.Bank == BankNameMS).ToList();
//工商
IList <OrderCash> ListGS = OrderCashList.Where(n => n.Bank == BankNameGS).ToList();
//其它
IList <OrderCash> ListOT = OrderCashList.Where(n => n.Bank != BankNameMS && n.Bank != BankNameGS).ToList();
//设置数据开始行
int Befor = 0;
int maxCol = 0;//最大列数
if (ListOT.Count > 0)
{
//加载模板第一张表
var sheet = package.Workbook.Worksheets[1];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListOT.Count();
Befor = 5; //民生他行从5开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
//sheet.Row(i - 1).Height = 18;//设置行高
foreach (var item in ListOT)
{
string zdlx = "2";
//sheet.Row(i).Height = 18;//设置行高
decimal Money = item.Amoney - (decimal)item.UserRate;
TotalMoney += Money;
//制单类型
cells["A" + i].Value = zdlx.ToString();
//企业自制凭证号
cells["B" + i].Value = item.Id.ToString().PadLeft(8, '0');
//客户号
cells["C" + i].Value = "2200488356";
//预约标志
cells["D" + i].Value = "0";
//付款账号
cells["E" + i].Value = "613111800";
//交易金额
cells["F" + i].Value = Money;
//收款账号
cells["G" + i].Value = item.CardNum;
//收款人姓名
cells["H" + i].Value = item.Owner;
//收款账户类型
cells["I" + i].Value = "1";
//子客户号
cells["J" + i].Value = "";
//子付款账号
cells["K" + i].Value = "";
//子付款账户名
cells["L" + i].Value = "";
//子付款账户开户行名
cells["M" + i].Value = "";
//用途
cells["N" + i].Value = "提现";
//汇路
cells["O" + i].Value = Money > 50000 ? "7" : "6";
//是否通知收款人
cells["P" + i].Value = "0";
//手机
cells["Q" + i].Value = item.Mobile;
//邮箱
cells["R" + i].Value = "";
//支付行号&支付行名称
cells["S" + i].Value = item.Bin + "&" + item.Deposit;
maxCol = 19;
i++;
}
i--;
//审核方式（文件类型）
cells["B1"].Value = "1";
//总金额
cells["B2"].Value = TotalMoney.ToString("F2");
//总交易数
cells["B3"].Value = Rows.ToString();
sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)";
//cells["B" + (i + 2)].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
//sheet.Cells[Befor, 1, i, maxCol].Style.Border.Left.Style = ExcelBorderStyle.Thin;
//sheet.Cells[Befor, 1, i, maxCol].Style.Border.Right.Style = ExcelBorderStyle.Thin;
//sheet.Cells[Befor, 1, i, maxCol].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//sheet.Cells[Befor, 1, i, maxCol].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//sheet.Cells[Befor, 1, i, maxCol].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//居中
//sheet.Cells[Befor, 5, i, 5].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss";
//for (int j = i + 1; j <= i + 2; j++)
//{
// sheet.Row(j).Height = 20;//设置行高
//}
}
if (ListMS.Count > 0)
{
//加载模板第二张表
var sheet = package.Workbook.Worksheets[2];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListMS.Count();
Befor = 9; //民生本行从9开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
foreach (var item in ListMS)
{
decimal Money = item.Amoney - (decimal)item.UserRate;
TotalMoney += Money;
//收款账号
cells["A" + i].Value = item.CardNum;
//交易金额
cells["B" + i].Value = Money.ToString("F2");
//收款人姓名
cells["C" + i].Value = item.Owner;
//用途
cells["D" + i].Value = "349";
maxCol = 4;
i++;
}
i--;
//总金额
cells["B6"].Value = TotalMoney.ToString();
//总交易数
cells["B7"].Value = Rows.ToString();
}
if (ListGS.Count > 0)
{
//加载模板第三张表
var sheet = package.Workbook.Worksheets[3];
var cells = sheet.Cells;
decimal TotalMoney = 0; //计算总金额
int Rows = ListGS.Count();
Befor = 2; //工行本行从2开始
sheet.InsertRow(Befor + 1, Rows - 1, Befor);
int i = Befor;
IList <BasicProvince> PList = Entity.BasicProvince.Where(n => n.State == 1).ToList();
IList <BasicCity> CList = Entity.BasicCity.Where(n => n.State == 1).ToList();
foreach (var item in ListGS)
{
decimal Money = item.Amoney - (decimal)item.UserRate;
TotalMoney += Money;
//币种
cells["A" + i].Value = "RMB";
//日期
cells["B" + i].Value = DateTime.Now.ToString("yyyyMMdd");
//明细标志
cells["C" + i].Value = "";
//顺序号
cells["D" + i].Value = i - Befor + 1;
//付款账号开户行
cells["E" + i].Value = "工商银行";
//付款账号
cells["F" + i].Value = "4000051709100125887";
//付款账号名称
cells["G" + i].Value = "好付支付（深圳）有限公司";
//收款账号开户行
cells["H" + i].Value = "工商银行";
//收款账号省份
cells["I" + i].Value = PList.FirstOrNew(n => n.Id == item.Province).Name;
//收款账号地市
cells["J" + i].Value = CList.FirstOrNew(n => n.Id == item.City).Name;
//收款账号地区码
cells["K" + i].Value = "";
//收款账号
cells["L" + i].Value = item.CardNum;
//收款账号名称
cells["M" + i].Value = item.Owner;
//金额
cells["N" + i].Value = Money.ToString("F2");
//汇款用途
cells["O" + i].Value = "提现";
//备注信息
cells["P" + i].Value = "";
//汇款方式
cells["Q" + i].Value = 1;
//收款账户短信通知手机号码
cells["R" + i].Value = "";
//自定义序号
cells["S" + i].Value = item.Id;
maxCol = 19;
i++;
}
i--;
}
//无数据表删除
if (ListGS.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[3]);
}
if (ListMS.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[2]);
}
if (ListOT.Count == 0)
{
package.Workbook.Worksheets.Delete(package.Workbook.Worksheets[1]);
}
Response.BinaryWrite(package.GetAsByteArray());//输出
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99) + ".xlsx");
}
else
{
Response.Write("暂无符合条件数据");
}
}
public void Post()
{
string Data = DataObj.GetData();
if (Data.IsNullOrEmpty())
{
DataObj.OutError("1000");
return;
}
JObject json = new JObject();
try
{
json = (JObject)JsonConvert.DeserializeObject(Data);
}
catch (Exception Ex)
{
Log.Write("[MsgUser]:", "【Data】" + Data, Ex);
}
if (json == null)
{
DataObj.OutError("1000");
return;
}
MsgUser MsgUser = new MsgUser();
MsgUser = JsonToObject.ConvertJsonToModel(MsgUser, json);
Users baseUsers = Entity.Users.FirstOrDefault(n => n.Token == MsgUser.Token);
if (baseUsers == null)//用户令牌不存在
{
DataObj.OutError("2004");
return;
}
if (baseUsers.State != 1)//用户被锁定
{
DataObj.OutError("2003");
return;
}
//if (baseUsers.CardStae != 2)//未实名认证
//{
// DataObj.OutError("2006");
// return;
//}
//if (baseUsers.MiBao != 1)//未设置支付密码
//{
// DataObj.OutError("2008");
// return;
//}
string uid = string.Format(",{0},", baseUsers.Id);
EFPagingInfo <MsgUser> p = new EFPagingInfo <MsgUser>();
if (!MsgUser.Pg.IsNullOrEmpty())
{
p.PageIndex = MsgUser.Pg;
}
if (!MsgUser.Pgs.IsNullOrEmpty())
{
p.PageSize = MsgUser.Pgs;
}
//p.SqlWhere.Add(f => f.UId == baseUsers.Id || ( !f.DeleteUsers.Contains(uid) && f.SendUsers.Contains(uid)) );群发功能检索
p.SqlWhere.Add(f => f.UId == baseUsers.Id);
p.SqlWhere.Add(f => f.State > 0 && f.AddTime > baseUsers.AddTime);
p.OrderByList.Add("Id", "DESC");
IPageOfItems <MsgUser> List = Entity.Selects <MsgUser>(p);
foreach (var P in List.Where(n => n.UId == 0))
{ //处理已读
if (P.ReadUsers.IsNullOrEmpty())
{
P.ReadUsers = string.Empty;
}
if (P.ReadUsers.IndexOf(uid) != -1)
{
P.State = 2;
}
P.Info = Utils.RemoveHtml(P.Info);
P.Info = P.Info.Replace(" ", "");
}
IList <MsgUser> iList = List.ToList();
StringBuilder sb = new StringBuilder("");
sb.Append("{");
sb.Append(List.PageToString());
sb.Append(",");
sb.Append(iList.EntityToString());
sb.Append("}");
DataObj.Data = sb.ToString();
DataObj.Code = "0000";
DataObj.OutString();
}
