public ActionResult Index(MsgUser MsgUser, EFPagingInfo <MsgUser> p, int IsFirst = 0) { if (IsFirst == 0) { PageOfItems <MsgUser> MsgUserList1 = new PageOfItems <MsgUser>(new List <MsgUser>(), 0, 10, 0, new Hashtable()); ViewBag.MsgUserList = MsgUserList1; ViewBag.MsgUser = MsgUser; ViewBag.UsersList = new List <Users>(); ViewBag.IsMyDelete = checkPower("Delete"); ViewBag.IsMyEdit = checkPower("Edit"); ViewBag.IsMySave = checkPower("Save"); ViewBag.IsMyAdd = checkPower("Add"); return(View()); } if (!MsgUser.Info.IsNullOrEmpty()) { if (MsgUser.Info == "全体商户") { p.SqlWhere.Add(f => f.UId == 0); } else { IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(MsgUser.Info) || n.NeekName.Contains(MsgUser.Info) || n.UserName == MsgUser.Info).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 (!MsgUser.Name.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Name.Contains(MsgUser.Name)); } if (!MsgUser.State.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.State == (MsgUser.State == 99 ? 0 : MsgUser.State)); } p.OrderByList.Add("Id", "DESC"); IPageOfItems <MsgUser> MsgUserList = Entity.Selects <MsgUser>(p); ViewBag.MsgUserList = MsgUserList; ViewBag.MsgUser = MsgUser; IList <MsgUser> List = MsgUserList.GroupBy(n => n.UId).Select(n => n.First()).ToList(); List <int> UId = new List <int>(); foreach (var pp in List) { UId.Add(pp.UId); } ViewBag.UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList(); ViewBag.IsMyDelete = checkPower("Delete"); ViewBag.IsMyEdit = checkPower("Edit"); ViewBag.IsMySave = checkPower("Save"); ViewBag.IsMyAdd = checkPower("Add"); return(View()); }
public FileResult XLSDo(OrderProfitLog OrderProfitLog, EFPagingInfo <OrderProfitLog> p, string TName, DateTime?STime, DateTime?ETime, bool?IsShowSupAgent) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } if (STime.IsNullOrEmpty()) { STime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); } if (ETime.IsNullOrEmpty()) { //JobOrders.ETime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")); ETime = DateTime.Now; } p.PageSize = 9999999; p = this.Condition(OrderProfitLog, p, TName, STime, ETime, IsShowSupAgent); IPageOfItems <OrderProfitLog> OrderProfitLogList = Entity.Selects <OrderProfitLog>(p); IList <OrderProfitLog> List = OrderProfitLogList.GroupBy(n => n.UId).Select(n => n.First()).ToList(); List <int> UId = new List <int>(); foreach (var pp in List) { UId.Add(pp.UId); } var UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList(); DataTable table = new DataTable(); DataRow row = null; // 创建 datatable 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))); table.Columns.Add(new DataColumn("分润金额", typeof(decimal))); table.Columns.Add(new DataColumn("分润时间", typeof(string))); // 填充数据 foreach (var item in OrderProfitLogList) { var u = UsersList.FirstOrNew(o => o.Id == item.UId); row = table.NewRow(); row[0] = item.TNum; row[1] = item.Amoney.ToString("F2"); row[2] = u.UserName; row[3] = LogTypeName(item.LogType); row[4] = item.Profit.ToString("F2"); row[5] = item.AddTime.ToString("yyyy-MM-dd HH:mm:ss"); table.Rows.Add(row); } return(this.ExportExcelBase(table, "自动刷还分润")); }
public ActionResult Index(UserPayChange UserPayChange, EFPagingInfo <UserPayChange> p, int IsFirst = 0) { if (IsFirst == 0) { PageOfItems <UserPayChange> UserPayChangeList1 = new PageOfItems <UserPayChange>(new List <UserPayChange>(), 0, 10, 0, new Hashtable()); ViewBag.UserPayChangeList = UserPayChangeList1; ViewBag.UserPayChange = UserPayChange; ViewBag.UsersList = new List <Users>(); ViewBag.Info = this.checkPower("Info"); return(View()); } //用户名搜索 if (!UserPayChange.Remark.IsNullOrEmpty()) { IList <Users> UList = Entity.Users.Where(n => n.TrueName.Contains(UserPayChange.Remark) || n.NeekName.Contains(UserPayChange.Remark) || n.UserName == UserPayChange.Remark).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 (!UserPayChange.State.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.State == UserPayChange.State); } p.SqlWhere.Add(f => f.SId == BasicAgent.Id); p.SqlWhere.Add(f => f.SAId == AdminUser.Id); p.OrderByList.Add("Id", "DESC"); IPageOfItems <UserPayChange> UserPayChangeList = Entity.Selects <UserPayChange>(p); ViewBag.UserPayChangeList = UserPayChangeList; ViewBag.UserPayChange = UserPayChange; //ViewBag.UsersList = Entity.Users.Where(n => n.State == 1 && n.Agent == AdminUser.AgentId).ToList(); IList <UserPayChange> List = UserPayChangeList.GroupBy(n => n.UId).Select(n => n.First()).ToList(); List <int> UId = new List <int>(); foreach (var pp in List) { UId.Add(pp.UId); } ViewBag.UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList(); ViewBag.Info = this.checkPower("Info"); return(View()); }
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(Orders Orders, EFPagingInfo <Orders> p, bool?IsShowSupAgent, int TimeType = 1) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } p = this.Condition(Orders, p, IsShowSupAgent, TimeType); p.PageSize = 99999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <Orders> OrdersList = Entity.Selects <Orders>(p); if (OrdersList.Count() > 0) { #region 导出 string file = Server.MapPath("/template") + "\\finorders.xlsx"; ExcelPackage package = new ExcelPackage(new FileInfo(file), true); var sheet = package.Workbook.Worksheets[1]; var cells = sheet.Cells; int Rows = OrdersList.Count(); //设置数据开始行 int Befor = 2; sheet.InsertRow(Befor + 1, Rows - 1, Befor); int i = Befor; sheet.Row(i - 1).Height = 22;//设置行高 int maxCol = 0; //获取机构及管理员 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 <PayConfig> PayConfigList = Entity.PayConfig.Where(n => n.State == 1).ToList(); IList <SysAgent> SysAgentList = Entity.SysAgent.ToList(); foreach (var item in OrdersList) { #region MyRegion string AName = "--"; if (item.AId > 0) { SysAgent SA = SysAgentList.FirstOrNew(n => n.Id == item.Agent); if (!SA.Name.IsNullOrEmpty()) { AName = SA.Name; } } string BName = "--"; Users OU = UsersList.FirstOrNew(n => n.Id == item.UId); if (!OU.TrueName.IsNullOrEmpty()) { BName = OU.TrueName; } PayConfig PayConfig = PayConfigList.FirstOrNew(n => n.Id == item.PayWay); sheet.Row(i).Height = 22;//设置行高 //机构 cells["A" + i].Value = AName; //交易号 cells["B" + i].Value = item.TNum; //交易商户 cells["C" + i].Value = OU.NeekName + "[" + OU.TrueName + "]"; //交易类型 cells["D" + i].Value = item.GetTTName(); //交易内容 cells["E" + i].Value = item.GetPayNameWithTName(); //交易金额 cells["F" + i].Value = item.Amoney.ToString("f2"); //交易时间 cells["G" + i].Value = item.PayTime; //入账时间 cells["H" + i].Value = (item.InTimed != null?Convert.ToDateTime(item.InTimed).ToString():""); //支付方式 cells["I" + i].Value = PayConfig.Name; //订单状态 cells["J" + i].Value = item.GetState(); string temp = string.Empty; if (item.TType == 2 || item.TType == 5) { temp = "T+" + item.TrunType; } if (item.TType == 1 || item.TType == 7 || item.TType == 8 || item.TType == 9) { temp = "T+" + item.LagEntryDay; } //接收方式 cells["K" + i].Value = temp; double I = 0, K = 0, N = 0; decimal J = 0, L = 0, M = 0; string Is = ""; #region MyRegion if (item.TType == 1) { //银联卡支付订单 OrderRecharge OrderRecharge = Entity.OrderRecharge.FirstOrNew(n => n.OId == item.TNum); I = OrderRecharge.UserRate; //商户费率 J = OrderRecharge.Poundage; //手续费 K = OrderRecharge.SysRate; //第三方费率 L = ((decimal)OrderRecharge.SysRate * OrderRecharge.Amoney).Ceiling(); //第三方手续费 N = OrderRecharge.AgentPayGet; //分支机构提成 M = OrderRecharge.Poundage - (decimal)OrderRecharge.SysRate * OrderRecharge.Amoney - (decimal)OrderRecharge.AgentPayGet; //利润 } if (item.TType == 2) { //提现订单 OrderCash OrderCash = Entity.OrderCash.FirstOrNew(n => n.OId == item.TNum); I = 0; if (!OrderCash.ECash.IsNullOrEmpty() && !OrderCash.Cash.IsNullOrEmpty()) { Is = OrderCash.ECash.ToMoney() + "+" + (OrderCash.Cash * 100).ToString("F2") + "%"; } else if (!OrderCash.ECash.IsNullOrEmpty()) { Is = OrderCash.ECash.ToMoney(); } else if (!OrderCash.Cash.IsNullOrEmpty()) { Is = (OrderCash.Cash * 100).ToString("F2") + "%"; } else { Is = "0"; } J = (decimal)OrderCash.UserRate; //手续费 K = 0; //第三方费率 L = OrderCash.CashRate; //第三方手续费 N = OrderCash.AgentCashGet; //分支机构提成 M = (decimal)OrderCash.UserRate - OrderCash.CashRate - (decimal)OrderCash.AgentCashGet; //利润 } if (item.TType == 3) { //转帐订单 OrderTransfer OrderTransfer = Entity.OrderTransfer.FirstOrNew(n => n.OId == item.TNum); I = OrderTransfer.UserRate; //商户费率 J = OrderTransfer.Poundage; //手续费 K = OrderTransfer.SysRate; //第三方费率 L = ((decimal)OrderTransfer.SysRate * OrderTransfer.Amoney).Ceiling(); //第三方手续费 N = OrderTransfer.AgentPayGet; //分支机构提成 M = OrderTransfer.Amoney - OrderTransfer.PayMoney - L - (decimal)OrderTransfer.AgentPayGet; //利润 } if (item.TType == 5) { //付房租订单 OrderHouse OrderHouse = Entity.OrderHouse.FirstOrNew(n => n.OId == item.TNum); I = OrderHouse.UserRate + (double)OrderHouse.CashRate; //商户费率 J = OrderHouse.Poundage; //手续费 K = OrderHouse.SysRate; //第三方费率 L = ((decimal)OrderHouse.SysRate * OrderHouse.Amoney).Ceiling(); //第三方手续费 N = OrderHouse.AgentPayGet; //分支机构提成 M = OrderHouse.Amoney - OrderHouse.PayMoney - L - (decimal)OrderHouse.AgentPayGet; //利润 } if (item.TType == 6) { //升级订单 PayConfigOrder PayConfigOrder = Entity.PayConfigOrder.FirstOrNew(n => n.OId == item.TNum); I = 0; //商户费率 J = PayConfigOrder.Poundage; //手续费 K = PayConfigOrder.SysRate; //第三方费率 L = ((decimal)PayConfigOrder.SysRate * PayConfigOrder.Amoney).Ceiling(); //第三方手续费 N = PayConfigOrder.AgentGet; //分支机构提成 M = PayConfigOrder.Amoney - (decimal)PayConfigOrder.AgentGet; //利润 } if (item.TType == 7 || item.TType == 8 || item.TType == 9) { //当面付 OrderF2F OrderF2F = Entity.OrderF2F.FirstOrNew(n => n.OId == item.TNum); I = OrderF2F.UserRate; //商户费率 J = OrderF2F.Poundage; //手续费 K = OrderF2F.SysRate; //第三方费率 L = ((decimal)OrderF2F.SysRate * OrderF2F.Amoney).Ceiling(); //第三方手续费 N = OrderF2F.AgentPayGet; //分支机构提成 M = OrderF2F.Poundage - (decimal)OrderF2F.SysRate * OrderF2F.Amoney - (decimal)OrderF2F.AgentPayGet; //利润 } if (item.TType == 10) { //升级订单 DaiLiOrder DaiLiOrder = Entity.DaiLiOrder.FirstOrNew(n => n.OId == item.TNum); I = 0; //商户费率 J = 0; //手续费 K = 0; //第三方费率 L = 0; //第三方手续费 N = DaiLiOrder.AgentGet; //分支机构提成 M = DaiLiOrder.Amoney - (decimal)DaiLiOrder.AgentGet; //利润 } #endregion //用户费率9 if (item.TType == 2) { cells["L" + i].Value = Is; } else { cells["L" + i].Value = I; } //用户手续费10 cells["M" + i].Value = J; //支出费率11 系统费率 cells["N" + i].Value = K; //支出手续费12 cells["O" + i].Value = L; //利润13 cells["P" + i].Value = M; //结算金额14 cells["Q" + i].Value = N; i++; #endregion } sheet.Row(i).Height = 40;//设置行高 //交易总额 cells["F" + i].Formula = "SUM(F" + Befor + ":F" + (i - 1) + ")"; //用户手续费10 cells["M" + i].Formula = "SUM(M" + Befor + ":M" + (i - 1) + ")"; //支出手续费12 cells["O" + i].Formula = "SUM(O" + Befor + ":O" + (i - 1) + ")"; //利润13 cells["P" + i].Formula = "SUM(P" + Befor + ":P" + (i - 1) + ")"; //结算金额14 cells["Q" + i].Formula = "SUM(Q" + Befor + ":Q" + (i - 1) + ")"; i--; maxCol = 17; //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, 7, i, 7].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; sheet.Cells[Befor, 8, i, 8].Style.Numberformat.Format = "yyyy-mm-dd HH:mm:ss"; sheet.Cells[Befor, 12, i, 12].Style.Numberformat.Format = "#0.00%"; sheet.Cells[Befor, 14, i, 14].Style.Numberformat.Format = "#0.00%"; i++; sheet.Cells[Befor, 6, i, 6].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 13, i, 13].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 15, i, 15].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 16, i, 16].Style.Numberformat.Format = "\"¥\"#,##0.00_);[Red](\"¥\"#,##0.00)"; sheet.Cells[Befor, 17, i, 17].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"); #endregion } else { Response.Write("暂无符合条件数据"); } }
public ActionResult Index(ApplyLoan ApplyLoan, EFPagingInfo <ApplyLoan> p, bool?IsShowSupAgent, int IsFirst = 0) { if (IsShowSupAgent == null) { IsShowSupAgent = false; } 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 (IsFirst == 0) { PageOfItems <ApplyLoan> ApplyLoanList1 = new PageOfItems <ApplyLoan>(new List <ApplyLoan>(), 0, 10, 0, new Hashtable()); ViewBag.ApplyLoanList = ApplyLoanList1; ViewBag.ApplyLoan = ApplyLoan; ViewBag.SysAgentList = Entity.SysAgent.Where(n => n.Tier == 1).ToList(); ViewBag.IsShowSupAgent = IsShowSupAgent; ViewBag.Xls = this.checkPower("Xls"); return(View()); } 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()) { //是否要显示下级 if ((bool)IsShowSupAgent) { SysAgent LowerLevelAgent = Entity.SysAgent.Where(s => s.Id == ApplyLoan.AgentAId).FirstOrNew(); IList <SysAgent> SysAgentList = LowerLevelAgent.GetSupAgent(Entity); IList <int> UID = new List <int>(); foreach (var s in SysAgentList) { UID.Add(s.Id); } p.SqlWhere.Add(f => UID.Contains(f.AgentAId)); } else { 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); ViewBag.ApplyLoanList = ApplyLoanList; ViewBag.ApplyLoan = ApplyLoan; 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); } ViewBag.SysAgentList = Entity.SysAgent.Where(n => AgentId.Contains(n.Id) && n.Tier == 1).ToList(); ViewBag.IsShowSupAgent = IsShowSupAgent; ViewBag.Xls = this.checkPower("Xls"); return(View()); }
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 ActionResult XLSDo(OrderProfitLog OrderProfitLog, EFPagingInfo <OrderProfitLog> p) { p.SqlWhere.Add(f => f.LogType == 3); if (!OrderProfitLog.TNum.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.TNum == OrderProfitLog.TNum); } if (!OrderProfitLog.LogType.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.LogType == OrderProfitLog.LogType); } if (!OrderProfitLog.Agent.IsNullOrEmpty()) { p.SqlWhere.Add(f => f.Agent == OrderProfitLog.Agent); } if (!OrderProfitLog.STime.IsNullOrEmpty() && !OrderProfitLog.ETime.IsNullOrEmpty()) { DateTime ETime = OrderProfitLog.ETime; p.SqlWhere.Add(f => f.AddTime > OrderProfitLog.STime && f.AddTime < ETime); } p.PageSize = 999999; p.OrderByList.Add("Id", "DESC"); IPageOfItems <OrderProfitLog> OrderProfitLogList = null; OrderProfitLogList = Entity.Selects <OrderProfitLog>(p); IList <OrderProfitLog> List = OrderProfitLogList.GroupBy(n => n.UId).Select(n => n.First()).ToList(); List <int> UId = OrderProfitLogList.Select(o => o.UId).Distinct().ToList(); IList <Users> UsersList = Entity.Users.Where(n => n.State == 1 && UId.Contains(n.Id)).ToList(); List <int> AgentId = OrderProfitLogList.Select(o => o.Agent).Distinct().ToList(); IList <SysAgent> AgentList = Entity.SysAgent.Where(n => n.State == 1 && AgentId.Contains(n.Id)).ToList(); DataTable table = new DataTable(); DataRow row = null; // 创建 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(string))); 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(string))); string state = ""; //订单按照支付时间排序 foreach (var item in OrderProfitLogList) { Users Users = UsersList.FirstOrNew(n => n.Id == item.UId); SysAgent SysAgent = AgentList.FirstOrNew(o => o.Id == item.Agent); if (item.OrderType == 21) { state = "直通车交易"; } else if (item.OrderType == 10) { state = "自助开通代理"; } else if (item.OrderType == 31) { state = "刷还交易"; } row = table.NewRow(); row[0] = item.TNum; row[1] = Users.TrueName; row[2] = state; row[3] = item.Amoney; row[4] = item.Profit; row[5] = item.AddTime.ToString("yyyy-MM-dd HH:mm"); row[6] = SysAgent.Name; row[7] = item.Tier + "级"; table.Rows.Add(row); } string Time = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(10, 99); return(this.ExportExcelBase(table, "同级分润明细-" + Time)); }