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));
}
