public ActionResult DcDev() { #region 查询 string sql = "select ord.o_id,ord.o_code,ord.o_bizcode,ord.o_tradeno,ord.o_paymode_id,ord.o_app_id,ord.o_goodsname,ord.o_term_key,ord.o_price,ord.o_payuser,ord.o_ctime,ord.o_ptime,ord.o_state,ord.o_times,ord.o_address,ord.o_noticestate,ord.o_noticetimes,ord.o_privateinfo,ord.o_interface_id,ord.o_showaddress,app.a_name, users.u_id,users.u_realname,users.DisplayName,users.relation_type,users.bpname,paymode.p_name,inn.l_corporatename ,app.a_platform_id from( "; //组装查询条件 string sql1 = ""; string TableName = ""; //表名 string order = " order by o_ctime desc"; //排序字段 int searchType = string.IsNullOrEmpty(Request["searchType"]) ? 0 : Int32.Parse(Request["searchType"]); //查询条件选择 string searchname = string.IsNullOrEmpty(Request["searchname"]) ? "" : Request["searchname"]; //查询类容 string stime = string.IsNullOrEmpty(Request["stime"]) ? DateTime.Now.ToString("yyyy-MM-dd") : Request["stime"]; //开始时间 string etime = string.IsNullOrEmpty(Request["etime"]) ? DateTime.Now.ToString("yyyy-MM-dd") : Request["etime"]; //结束时间 int paymode = string.IsNullOrEmpty(Request["paymode"]) ? 0 : Int32.Parse(Request["paymode"]); //支付类型 string paymentstate = string.IsNullOrEmpty(Request["paymentstate"]) ? "1" : Request["paymentstate"]; //支付状态 string noticestate = string.IsNullOrEmpty(Request["noticestate"]) ? "" : Request["noticestate"]; //通知状态 ArrayList sjfw = JMP.TOOL.WeekDateTime.WeekMonday(DateTime.Parse(stime), DateTime.Parse(etime)); //根据时间返回获取每周周一组装查询语句 string where = ""; for (int i = 0; i < sjfw.Count; i++) { TableName = "jmp_order_" + DateTime.Parse(sjfw[i].ToString()).ToString("yyyyMMdd"); sql += " SELECT o_id,o_code,o_bizcode,o_tradeno,o_paymode_id,o_app_id,o_goodsname,o_term_key,o_price,o_payuser,o_ctime,o_ptime,o_state,o_times,o_address,o_noticestate,o_noticetimes,o_privateinfo,o_interface_id, o_showaddress FROM " + TableName + " where 1=1 "; if (!string.IsNullOrEmpty(stime) && !string.IsNullOrEmpty(etime)) { sql += " and convert(varchar(10),o_ptime,120)>='" + stime + "' and convert(varchar(10),o_ptime,120)<='" + etime + "' "; } if (paymode > 0) { sql += " and o_paymode_id='" + paymode + "' "; } if (!string.IsNullOrEmpty(paymentstate)) { sql += " and o_state='" + paymentstate + "' "; } if (!string.IsNullOrEmpty(noticestate)) { sql += " and o_noticestate='" + noticestate + "' "; } sql += " UNION ALL "; } if (searchType > 0 && !string.IsNullOrEmpty(searchname)) { switch (searchType) { case 1: where += " and a.o_code='" + searchname + "' "; break; case 2: where += " and b.a_name='" + searchname + "' "; break; case 3: where += " and d.g_name='" + searchname + "' "; break; case 4: where += " and a.o_tradeno= '" + searchname + "' "; break; case 5: where += " and a.o_bizcode like '%" + searchname + "%' "; break; } } // sql = sql.Remove(sql.Length - 10);//去掉最后一个UNION ALL sql1 = "SELECT o_id,o_code,o_bizcode,o_tradeno,o_paymode_id,o_app_id,o_goodsname,o_term_key,o_price,o_payuser,o_ctime,o_ptime,o_state,o_times,o_address,o_noticestate,o_noticetimes,o_privateinfo,o_interface_id, o_showaddress FROM jmp_order where 1=1"; if (!string.IsNullOrEmpty(stime) && !string.IsNullOrEmpty(etime)) { sql1 += " and convert(varchar(10),o_ptime,120)>='" + stime + "' and convert(varchar(10),o_ptime,120)<='" + etime + "' "; } if (paymode > 0) { sql1 += " and o_paymode_id='" + paymode + "' "; } if (!string.IsNullOrEmpty(paymentstate)) { sql1 += " and o_state='" + paymentstate + "' "; } if (!string.IsNullOrEmpty(noticestate)) { sql1 += " and o_noticestate='" + noticestate + "' "; } sql = sql + sql1; sql += " ) ord , jmp_app app ,(select aa.u_id, aa.u_realname, aa.DisplayName, aa.bpname, aa.relation_type from( select a.u_id, a.u_realname, c.DisplayName, null as bpname, a.relation_type from jmp_user a left join dx_base.dbo.CoAgent c on c.Id = a.relation_person_id where a.relation_type = 2 and c.OwnerId = " + UserInfo.UserId + ") aa group by aa.u_id, aa.u_realname, aa.DisplayName, aa.relation_type, aa.bpname ) users, jmp_paymode as paymode, jmp_interface as inn where app.a_id = ord.o_app_id and users.u_id = app.a_user_id and paymode.p_id = ord.o_paymode_id and inn.l_id = ord.o_interface_id " + where + order; List <JMP.MDL.jmp_order> list = new List <JMP.MDL.jmp_order>(); JMP.BLL.jmp_order orderbll = new JMP.BLL.jmp_order(); list = orderbll.DcSelectList(sql); var lst = list.Select(x => new { x.o_code, x.a_name, x.o_goodsname, x.o_bizcode, x.o_tradeno, x.p_name, x.o_price, o_state = x.o_state.ConvertPayState(), o_ctime = x.o_ctime.ToString("yyyy-MM-dd HH:mm:ss"), o_ptime = x.o_ptime.ToString("yyyy-MM-dd HH:mm:ss"), o_times = x.o_noticestate == 0 ? "--" : x.o_times.ToString(), o_noticestate = x.o_noticestate.ConvertNoticeState(x.o_state), o_noticetimes = x.o_noticestate != 0 ? x.o_noticetimes.ToString("yyyy-MM-dd HH:mm:ss") : "--", o_privateinfo = x.o_privateinfo }); var caption = "订单列表"; byte[] fileBytes; //命名导出表格的StringBuilder变量 using (var pck = new ExcelPackage()) { var ws = pck.Workbook.Worksheets.Add(caption); ws.Cells["A1"].LoadFromCollection(lst, false); ws.InsertRow(1, 1); ws.Cells["A1"].Value = "订单编号"; ws.Cells["B1"].Value = "应用名称"; ws.Cells["C1"].Value = "商品名称"; ws.Cells["D1"].Value = "商家订单号"; ws.Cells["E1"].Value = "支付流水号"; ws.Cells["F1"].Value = "支付类型"; ws.Cells["G1"].Value = "支付金额"; ws.Cells["H1"].Value = "支付状态"; ws.Cells["I1"].Value = "创建时间"; ws.Cells["J1"].Value = "支付时间"; ws.Cells["K1"].Value = "通知次数"; ws.Cells["L1"].Value = "通知状态"; ws.Cells["M1"].Value = "通知时间"; ws.Cells["N1"].Value = "私有信息"; fileBytes = pck.GetAsByteArray(); } Session["daochu"] = DateTime.Now; string fileName = "订单列表" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; #endregion return(File(fileBytes, "application/vnd.ms-excel", fileName)); }