private DataSet GetAllTerms(string isqudao, string key = "")
{
try
{
if (isqudao == "1")//渠道单位列表
{
if (key == "")
{
return(ExcelSqlHelper.ExecuteDataset("select issuetype, id,companyname as title,companystate from Member_Channel_company where com_id=" + comid + " and companystate in (" + isrun + ") order by issuetype,id desc"));
}
else
{
return(ExcelSqlHelper.ExecuteDataset("select issuetype,id,companyname as title,companystate from Member_Channel_company where com_id=" + comid + " and companystate in (" + isrun + ") and (companyname like '%" + key + "%' or id in (select companyid from member_channel where name='" + key + "' or mobile='" + key + "' )) order by issuetype,id desc"));
}
}
else //活动列表
{
if (key == "")
{
return(ExcelSqlHelper.ExecuteDataset("select 0 as issuetype, id,title,runstate as companystate from Member_Activity where runstate in (" + isrun + ") and com_id=" + comid + " order by id desc"));
}
else
{
return(ExcelSqlHelper.ExecuteDataset("select 0 as issuetype, id,title,runstate as companystate from Member_Activity where runstate in (" + isrun + ") and com_id=" + comid + " and title like '%" + key + "%' order by id desc"));
}
}
}
catch
{
return(null);
}
}
public static string Changestaffpwd(int staffid, string password, B2b_company_manageuser manageruser = null)
{
try
{
int result = 0;
if (manageruser != null)
{
result = B2bCompanyManagerUserData.InsertOrUpdate(manageruser);
}
else
{
result = ExcelSqlHelper.ExecuteNonQuery("update b2b_company_manageuser set passwords ='" + password + "' where id=" + staffid);
}
return(JsonConvert.SerializeObject(new { type = 100, msg = result }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { type = 1, msg = ex.Message }));
throw;
}
}
protected void Page_Load(object sender, EventArgs e)
{
var comid = Request["comid"];
var startime = Request["startime"].ConvertTo <string>("");
var endtime = Request["endtime"].ConvertTo <string>("");
//如果没有日期，只读取当天数据
if (startime == "")
{
startime = DateTime.Now.ToString("yyyy-MM-dd");
}
if (endtime == "")
{
endtime = DateTime.Now.ToString("yyyy-MM-dd");
}
string endtime_temp = endtime;
var condition = " a.comid=" + comid;
if (startime != "")
{
condition += " and a.usetime>='" + startime + "'";
}
if (endtime != "")
{
endtime = DateTime.Parse(endtime).AddDays(1).ToString();
condition += " and a.usetime<'" + endtime + "'";
}
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select a.orderid [编号],b.Pro_name [产品名称],c.U_name [姓名],c.U_phone [手机],d.company [出票单位],a.wlorderid [WL订单号],c.Pay_price [结算价],a.usedQuantity [使用数量],b.Pro_end[有效期],a.usetime[使用时间] from wl_uselog as a left join b2b_com_pro as b on a.proid=b.id left join b2b_order as c on a.orderid=c.id left join agent_company as d on c.agentid=d.id where " + condition + " order by a.id desc"),
Context, "WLinterfaceuselog" + startime + "--" + endtime_temp + ".xls");
}
protected void Page_Load(object sender, EventArgs e)
{
//创建支付应答对象
ResponseHandler resHandler = new ResponseHandler(Context);
resHandler.init();
int comid = 0;
string RequestUrl = Request.ServerVariables["SERVER_NAME"].ToLower();
string u = Request.ServerVariables["HTTP_USER_AGENT"];
if (Domain_def.Domain_yanzheng(RequestUrl))//如果符合shop101.etown.cn的格式
{
//先通过正则表达式获取COMid
comid = Int32.Parse(Domain_def.Domain_Huoqu(RequestUrl).ToString());
B2b_finance_paytype modelfinance = new B2b_finance_paytypeData().GetFinancePayTypeByComid(comid);
resHandler.setKey(modelfinance.Wx_partnerkey, modelfinance.Wx_paysignkey);
}
else
{
Response.Write("获取商家支付参数失败");
return;
}
////判断签名
//if (resHandler.isTenpaySign())
//{
// if (resHandler.isWXsign())
// {
if (Request.HttpMethod.ToLower() == "post")
{
byte[] ar;
ar = new byte[this.Request.InputStream.Length];
this.Request.InputStream.Read(ar, 0, ar.Length);
string sXML = this.Request.ContentEncoding.GetString(ar);
//回复服务器处理成功
string sql = "insert into wxwarning(responsexml,comid) values('" + sXML + "'," + comid + ")";
ExcelSqlHelper.ExecuteNonQuery(sql);
Response.Write("success");
Response.Write("success:" + resHandler.getDebugInfo());
// }
//}
//else
//{
// //sha1签名失败
// Response.Write("fail");
// Response.Write("fail:" + resHandler.getDebugInfo());
//}
Response.End();
}
}
public int InsReqUrlLog(ReqUrl_Log requrllog)
{
try
{
object o = ExcelSqlHelper.ExecuteScalar("insert into ReqUrl_Log(urlstr,hoststr,paramstr,subtime) values('" + requrllog.urlstr + "','" + requrllog.hoststr + "','" + requrllog.paramstr + "','" + requrllog.subtime + "');select @@identity;");
return(int.Parse(o.ToString()));
}
catch
{
return(0);
}
}
public static string Editwxauthorfocus(int comid, string author, string url)
{
try
{
int result = ExcelSqlHelper.ExecuteNonQuery("update b2b_company_info set wxfocus_author='" + author + "' ,wxfocus_url='" + url + "' where com_id=" + comid);
return(JsonConvert.SerializeObject(new { type = 100, msg = result }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { type = 1, msg = ex.Message }));
}
}
private DataSet GetHeadOffice()
{
string sql = "select 0 as issuetype, '" + comname + "' as companyname, 0 as id,1 as companystate, COUNT(c.id) AS Expr1,SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney,c.Com_id,0 AS Expr2,COUNT(d.ID) AS companynum " +
" from Member_Channel as c left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name where c.id in (" +
" select id from Member_Channel where Com_id=" + comid + " and mobile in (select tel from b2b_company_manageuser where com_id=" + comid + " and channelcompanyid=0))" +
" group by c.Com_id";
DataSet ds = ExcelSqlHelper.ExecuteDataset(sql);
return(ds);
}
public static string Editwxauthorfocus(int comid, int istransfer_customer_service)
{
try
{
int result = ExcelSqlHelper.ExecuteNonQuery("update b2b_company_info set Istransfer_customer_service='" + istransfer_customer_service + "' where com_id=" + comid);
return(JsonConvert.SerializeObject(new { type = 100, msg = result }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { type = 1, msg = ex.Message }));
}
}
public string GetChannelCompanyNameById(int?Channelcompanyid)
{
if (Channelcompanyid == 0)
{
return("");
}
else
{
object o = ExcelSqlHelper.ExecuteScalar("select companyname from Member_Channel_company where id=" + Channelcompanyid);
return(o == null ? "" : o.ToString());
}
}
public string GetCompanyNameById(int comid)
{
if (comid == 0)
{
return("");
}
else
{
object o = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
return(o == null ? "" : o.ToString());
}
}
void BindGrid()
{
GridView1.DataSource = ExcelSqlHelper.ExecuteReader("select * from Out_MemberCardCodeImLog where comid=" + comid + " order by id desc");
GridView1.DataBind();
object o1 = ExcelSqlHelper.ExecuteScalar("select count(1) from Out_MemberCardCodeImLog where comid=" + comid);
upNum = int.Parse(o1.ToString());
object o2 = ExcelSqlHelper.ExecuteScalar("select count(1) from Out_MemberCardCode where comid=" + comid + " and isused=0");
surplusNum = int.Parse(o2.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
comid = Request["comid"].ConvertTo <int>(0);
md5info = Request["md5info"].ConvertTo <string>("");
string companyName = new B2bCompanyData().GetCompanyNameById(comid);
string Returnmd5 = EncryptionHelper.ToMD5(comid.ToString() + "lixh1210", "UTF-8");
if (Returnmd5 == md5info)//验证MD5
{
//ExcelRender.RenderToExcel(
// ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select idcard [会员卡号], name [姓名],phone [手机],email [邮件],imprest [预付款],Integral [积分],regidate [注册时间] from b2b_crm where com_id=" + comid),
// Context, companyName + "会员信息" + ".xls");
DataTable crmdt = ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select idcard [会员卡号], name [姓名],phone [手机],email [邮件],imprest [预付款],Integral [积分],regidate [注册时间] from b2b_crm where com_id=" + comid);
DataTable dt = new DataTable();
dt.Columns.Add("会员卡号");
dt.Columns.Add("姓名");
dt.Columns.Add("手机");
dt.Columns.Add("邮件");
dt.Columns.Add("预付款");
dt.Columns.Add("积分");
dt.Columns.Add("注册时间");
dt.Columns.Add("渠道单位");
dt.Columns.Add("推荐人");
DataRow dr = dt.NewRow();
foreach (DataRow rr in crmdt.Rows)
{
dr = dt.NewRow();
dr["会员卡号"] = rr["会员卡号"].ToString();
dr["姓名"] = rr["姓名"].ToString();
dr["手机"] = rr["手机"].ToString();
dr["邮件"] = rr["邮件"].ToString();
dr["预付款"] = rr["预付款"].ToString();
dr["积分"] = rr["积分"].ToString();
dr["注册时间"] = rr["注册时间"].ToString();
dr["渠道单位"] = new MemberChannelcompanyData().UpCompanyById(rr["会员卡号"].ToString());
dr["推荐人"] = MemberChannelData.SearchNamestring(rr["会员卡号"].ToString());
dt.Rows.Add(dr);
}
ExcelRender.RenderToExcel(dt, Context, companyName + "会员信息" + ".xls");
}
}
public static string EditLp(int comid, int lp, int lp_agentlevel)
{
try
{
int result = ExcelSqlHelper.ExecuteNonQuery("update b2b_company set lp =" + lp + ",lp_agentlevel=" + lp_agentlevel + " where id=" + comid);
return(JsonConvert.SerializeObject(new { type = 100, msg = result }));
}
catch (Exception ex)
{
return(JsonConvert.SerializeObject(new { type = 1, msg = ex.Message }));
throw;
}
}
public int operlogid = 0; //操作日志id
protected void Page_Load(object sender, EventArgs e)
{
proid = Context.Request["proid"].ConvertTo <int>(0);
gooutdate = Context.Request["gooutdate"].ConvertTo <string>("");
if (proid == 0 || gooutdate == "")
{
Response.Redirect("/default.aspx");
}
if (proid != 0 && gooutdate != "")
{
//获取 产品名称
object o = ExcelSqlHelper.ExecuteScalar("select pro_name from b2b_com_pro where id=" + proid);
proname = o == null ? "" : o.ToString();
try
{
//获取 支付成功订单的统计人数
object q = ExcelSqlHelper.ExecuteScalar("select sum(u_num) from b2b_order where pro_id=" + proid + " and u_traveldate='" + gooutdate + "' and pay_state=" + (int)PayStatus.HasPay);
paysucnum = q == null ? 0 : int.Parse(q.ToString());
//获取 结团人数
q = ExcelSqlHelper.ExecuteScalar("select sum(u_num) from b2b_order where pro_id=" + proid + " and u_traveldate='" + gooutdate + "' and order_state=" + orderstatus_hasfin);
closeteamnum = q == null ? 0 : int.Parse(q.ToString());
}
catch
{
paysucnum = 0;
}
//判断是否有操作日志
object qq = ExcelSqlHelper.ExecuteScalar("select count(1) from travelbusorder_operlog where proid=" + proid + " and traveldate='" + gooutdate + "'");
int logcount = qq == null ? 0 : int.Parse(qq.ToString());
if (logcount > 0)//如果含有操作日志
{
//操作日志备注
qq = ExcelSqlHelper.ExecuteScalar("select operremark from travelbusorder_operlog where proid=" + proid + " and traveldate='" + gooutdate + "'");
operremark = qq == null ? "" : qq.ToString();
//操作日志id
qq = ExcelSqlHelper.ExecuteScalar("select id from travelbusorder_operlog where proid=" + proid + " and traveldate='" + gooutdate + "'");
operlogid = qq == null ? 0 : int.Parse(qq.ToString());
}
}
else
{
Response.Redirect("/default.aspx");
}
}
protected void Page_Load(object sender, EventArgs e)
{
comid = Request["comid"].ConvertTo <int>(0);
agentid = Request["agentid"].ConvertTo <int>(0);
orderid = Request["orderid"].ConvertTo <int>(0);
md5info = Request["md5info"].ConvertTo <string>("");
string Returnmd5 = EncryptionHelper.ToMD5(orderid.ToString() + comid.ToString() + agentid.ToString() + "lixh1210", "UTF-8");
if (Returnmd5 == md5info)//验证MD5
{
B2bOrderData orderdate = new B2bOrderData();
B2b_order ordermodel = orderdate.GetOrderById(orderid);
if (ordermodel != null)
{
B2bComProData prodata = new B2bComProData();
B2b_com_pro promodel = prodata.GetProById(ordermodel.Pro_id.ToString());
if (promodel != null)
{
if (ordermodel.Agentid == agentid && ordermodel.Comid == comid && ordermodel.Warrant_type == 2)
{
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select e_proname [产品名称],pno [票号],pnum [数量] from b2b_eticket where oid=" + orderid),
Context, promodel.Pro_name + ordermodel.U_subdate.Date.ToString("yyyyMMdd") + "-" + ordermodel.Id.ToString() + ".xls");
}
else
{
err = "订单参数错误";
}
}
else
{
err = "产品错误";
}
}
else
{
err = "订单错误";
}
}
else
{
err = "验证错误";
}
}
private DataSet getammebytermid(string termid)
{
try
{
if (isqudao == "1")
{
return(ExcelSqlHelper.ExecuteDataset("select id,channelcompanyid,activityid,comid,title,qrcodeurl,createtime,onlinestatus,channelid from WxSubscribeSource where comid=" + comid + " and channelcompanyid=" + termid + " order by id desc"));
}
{
return(ExcelSqlHelper.ExecuteDataset("select id,channelcompanyid,activityid,comid,title,qrcodeurl,createtime,onlinestatus,channelid from WxSubscribeSource where comid=" + comid + " and activityid=" + termid + " order by id desc"));
}
//return null;
}
catch
{
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
var comid = Request["comid"];
var startime = Request["startime"].ConvertTo <string>("");
var endtime = Request["endtime"].ConvertTo <string>("");
var projectid = Request["projectid"].ConvertTo <int>(0);
var proid = Request["proid"].ConvertTo <int>(0);
var key = Request["key"].ConvertTo <string>("");//电子码
object o1 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
var condition = " a.action=1 and a.com_id=" + comid + " and a.a_state=" + (int)ECodeOperStatus.OperSuc + " and a.pno in (select pno from b2b_eticket where 1=1 ";
if (projectid != 0)
{
condition += " and pro_id in (select id from b2b_com_pro where projectid=" + projectid + ")";
}
if (proid != 0)
{
condition += " and pro_id=" + proid;
}
condition += ")";
if (key != "")
{
condition += " and a.pno='" + key + "' ";
}
if (startime != "")
{
condition += " and a.actiondate>='" + startime + "'";
}
if (endtime != "")
{
endtime = DateTime.Parse(endtime).AddDays(1).ToString();
condition += " and a.actiondate<'" + endtime + "'";
}
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select b.e_proname [产品名称],a.pno [电子码],b.pnum [订购数量],a.use_pnum [本次使用数量],a.actiondate [验证时间],a.posid [验证pos],b.e_face_price [门市价],b.e_sale_price [结算价],c.company[出票方] from b2b_etcket_log as a left join b2b_eticket as b on a.com_id=b.com_id and a.pno=b.pno and a.eticket_id=b.id left join agent_company as c on b.agent_id=c.id where " + condition + " order by a.id desc"),
Context, o1.ToString() + "验证电子票列表" + ".xls");
}
internal ReqUrl_Log GetReqUrlLogById(string mid)
{
DataTable dt = ExcelSqlHelper.ExecuteDataTable("select * from ReqUrl_Log where mid=" + mid);
if (dt != null)
{
ReqUrl_Log m = new ReqUrl_Log
{
mid = int.Parse(dt.Rows[0]["mid"].ToString()),
urlstr = dt.Rows[0]["urlstr"].ToString(),
hoststr = dt.Rows[0]["hoststr"].ToString(),
paramstr = dt.Rows[0]["paramstr"].ToString(),
subtime = DateTime.Parse(dt.Rows[0]["subtime"].ToString())
};
return(m);
}
else
{
return(null);
}
}
private DataSet getammebytermid(string termid, string key = "")//渠道单位下边的渠道人列表
{
try
{
string sql = "SELECT [id] ,[Com_id] ,[Issuetype],[companyid] ,[name],[mobile],[cardcode] ,[Chaddress] ,[ChObjects] ,[RebateOpen],[RebateConsume] ,[RebateConsume2] ,[rebatelevel],[opencardnum],[firstdealnum],[summoney],[whetherdefaultchannel] ,[runstate] FROM [EtownDB].[dbo].[Member_Channel] where companyid in (" + termid + ") and com_id=" + comid;
if (key != "")
{
//sql += " and ( name ='"+ key +"' or mobile ='"+key+"')";
}
if (termid == "0")//如果渠道公司id为0，则为总部渠道
{
sql = "SELECT [id] ,[Com_id] ,[Issuetype],[companyid] ,[name],[mobile],[cardcode] ,[Chaddress] ,[ChObjects] ,[RebateOpen],[RebateConsume] ,[RebateConsume2] ,[rebatelevel],[opencardnum],[firstdealnum],[summoney],[whetherdefaultchannel] ,[runstate] FROM [EtownDB].[dbo].[Member_Channel] where companyid =0 and Issuetype=0 and com_id=" + comid;
}
DataSet ds = ExcelSqlHelper.ExecuteDataset(sql);
return(ds);
}
catch
{
return(null);
}
}
void BindGrid(int comid, int obtainno)
{
GridView1.DataSource = ExcelSqlHelper.ExecuteReader("select * from ObtainGzListLog where comid=" + comid + " and obtainno=" + obtainno);
GridView1.DataBind();
}
private void BindGrid2(int comid, int SplitNo, int obtainno)
{
GridView2.DataSource = ExcelSqlHelper.ExecuteReader("select * from [ObtainGzList_Split] where comid=" + comid + " and [splitno]=" + SplitNo + " and obtainno=" + obtainno);
GridView2.DataBind();
}
private DataSet GetAllTerms(string channelcompanytype, string channelcompanyid, string key = "")
{
try
{
if (channelcompanyid != "0")//显示特定渠道单位信息
{
string sql = @"SELECT a.issuetype, a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.id=" + channelcompanyid +
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
if (key != "")
{
sql = "SELECT a.issuetype, a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.id=" + channelcompanyid + " and (a.companyname like '%" + key + "%'" + " or a.id in (select companyid from Member_Channel where name ='" + key + "' or mobile='" + key + "'))" +
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
}
//SqlParameter[] para = { new SqlParameter("@channelcompanyid", SqlDbType.VarChar, 255) };
//para[0].Value = channelcompanyid;
DataSet ds = ExcelSqlHelper.ExecuteDataset(sql);
return(ds);
}
else //显示渠道列表
{
if (channelcompanytype == "inner")//所属门店
{
string sql = "SELECT a.issuetype, a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.Issuetype=0 and a.com_id=" + comid + " and a.companystate in (" + isrun + ")" +//最后 and a.id=0默认不显示
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
if (key != "")
{
sql = "SELECT a.issuetype,a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.Issuetype=0 and a.com_id=" + comid + " and a.companystate in (" + isrun + ") and (a.companyname like '%" + key + "%'" + " or a.id in (select companyid from Member_Channel where name ='" + key + "' or mobile='" + key + "'))" +
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
}
//SqlParameter[] para = { new SqlParameter("@comid", SqlDbType.Int, 32), new SqlParameter("@companystate", SqlDbType.VarChar, 255) };
//para[0].Value = comid;
//para[1].Value = isrun;
DataSet ds = ExcelSqlHelper.ExecuteDataset(sql);
return(ds);
}
else
{
string sql = @"SELECT a.issuetype,a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.Issuetype=1 and a.com_id=" + comid + " and a.companystate in (" + isrun + ") " +
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
if (key != "")
{
sql = @"SELECT a.issuetype, a.companyname, a.id,a.companystate, COUNT(c.id) AS Expr1, SUM(isnull(c.opencardnum,0)) AS opencardnum, SUM(isnull(c.firstdealnum,0)) AS firstdealnum, SUM(isnull(c.summoney,0)) " +
" AS summoney, a.Com_id, a.id AS Expr2,COUNT(d.ID) AS companynum " +
" FROM dbo.Member_Channel_company AS a LEFT OUTER JOIN " +
" dbo.Member_Channel AS c ON a.id = c.companyid left join " +
" dbo.Member_Activity_Log AS d ON d.sales_admin = c.name " +
" where a.Issuetype=1 and a.com_id=" + comid + " and a.companystate in (" + isrun + ") and (a.companyname like '%" + key + "%'" + " or a.id in (select companyid from Member_Channel where name ='" + key + "' or mobile='" + key + "'))" +
" GROUP BY a.issuetype,a.companyname, a.Com_id, a.id,a.companystate";
}
//SqlParameter[] para = { new SqlParameter("@comid", SqlDbType.Int, 32), new SqlParameter("@companystate", SqlDbType.VarChar, 255) };
//para[0].Value = comid;
//para[1].Value = isrun;
DataSet ds = ExcelSqlHelper.ExecuteDataset(sql);
return(ds);
}
}
}
catch
{
return(null);
}
}
//private void BindRunCompanyList()
//{
// int totalcount = 0;
// string comstate = "1";//公司状态1:正常运行
// List<B2b_company> list = new B2bCompanyData().GetAllCompanys(comstate, out totalcount);
// ddlcompany.DataSource = list;
// ddlcompany.DataValueField = "ID";
// ddlcompany.DataTextField = "Com_name";
// ddlcompany.DataBind();
//}
void BindGrid(int comid, int importnum)
{
GridView1.DataSource = ExcelSqlHelper.ExecuteReader("select * from excelimportlog where comid=" + comid + " and importnum=" + importnum);
GridView1.DataBind();
}
//导入项目列表，不用后删除或者隐藏
protected void Button2_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
byte[] fileBytes = FileUpload1.FileBytes;
if (ExcelRender.HasData(new MemoryStream(fileBytes)))
{
Stream excelFileStream = new MemoryStream(fileBytes);
int sheetIndex = 0;
int headerRowIndex = 0;
using (excelFileStream)
{
using (IWorkbook workbook = new HSSFWorkbook(excelFileStream))
{
using (ISheet sheet = workbook.GetSheetAt(sheetIndex))
{
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum; //LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum; //LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
string name = GetCellValue(row.GetCell(0));//项目名称
//判断项目中是否有同名的项目，有的话不在录入
object o = ExcelSqlHelper.ExecuteScalar("select count(1) from b2b_com_project where projectname='" + name + "'");
int c = o == null ? 0 : int.Parse(o.ToString());
if (c > 0)
{
}
else
{
B2b_com_project model = new B2b_com_project
{
Id = 0,
Projectname = name,
Projectimg = 0,
Province = "",
City = "",
Industryid = 0,
Briefintroduce = "",
Address = "",
Mobile = "",
Coordinate = "",
Serviceintroduce = "",
Onlinestate = "0",
Comid = 106,
Createuserid = UserHelper.CurrentUser().Id,
Createtime = DateTime.Now
};
int d = new B2b_com_projectData().EditProject(model);
}
}
if (i == rowCount)
{
Literal1.Text = "导入完成";
}
}
}
}
}
}
}
}
public static string HotelOrderStat(int comid, string begindate, string enddate, int projectid, int productid, string key, string orderstate)
{
var condition = " where a.order_state in (2,4,8,22) and a.pro_id in (select id from b2b_com_pro where server_type=9 and comid=" + comid + ") and a.comid=" + comid;
if (begindate != "" && enddate != "")
{
begindate = DateTime.Parse(begindate).ToString("yyyy-MM-dd 00:00:00");
enddate = DateTime.Parse(enddate).ToString("yyyy-MM-dd 23:59:59");
condition = condition + " and b.start_date between '" + begindate + "' and '" + enddate + "'";
}
if (projectid != 0)
{
condition = condition + " and a.pro_id in (select id from b2b_com_pro where projectid=" + projectid + ")";
}
if (productid != 0)
{
condition = condition + " and a.pro_id =" + productid;
}
if (key != "")
{
try
{
int intkey = int.Parse(key);
condition = condition + " and a.pro_id=" + intkey;
}
catch
{
condition = condition + " and a.pro_id in (select id from b2b_com_pro where pro_name='" + key + "' and comid=" + comid + ")";
}
}
string sql = "select sum(a.u_num*b.bookdaynum) as jianye,a.pro_id ,count(1) as ordertcount,c.pro_name,sum(a.u_num*b.bookdaynum*a.pay_price) as price from b2b_order as a left join b2b_order_hotel as b on a.id=b.orderid left join b2b_com_pro as c on a.pro_id=c.id " + condition + " group by a.pro_id,c.pro_name order by a.pro_id";
DataTable dt = ExcelSqlHelper.ExecuteDataTable(sql);
if (dt != null)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("jianye", Type.GetType("System.Int32"));
dc = tblDatas.Columns.Add("pro_id", Type.GetType("System.Int32"));
dc = tblDatas.Columns.Add("pro_name", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("ordertcount", Type.GetType("System.Int32"));
dc = tblDatas.Columns.Add("projectname", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("price", Type.GetType("System.String"));
DataRow newRow;
foreach (DataRow row in dt.Rows)
{
newRow = tblDatas.NewRow();
newRow["jianye"] = row["jianye"].ToString().ConvertTo <int>(9999);//出错的话间夜返回9999
newRow["pro_id"] = int.Parse(row["pro_id"].ToString());
newRow["pro_name"] = row["pro_name"].ToString();
newRow["ordertcount"] = row["ordertcount"].ToString().ConvertTo <int>(9999);//出错的话间夜返回9999
newRow["projectname"] = new B2bComProData().GetProjectNameByProid(row["pro_id"].ToString());
newRow["price"] = row["price"].ToString();
tblDatas.Rows.Add(newRow);
}
return(JsonConvert.SerializeObject(new { type = 100, msg = tblDatas }));
}
else
{
return(JsonConvert.SerializeObject(new { type = 1, msg = "订房信息为空" }));
}
}
protected void Page_Load(object sender, EventArgs e)
{
string phone = Request["etmobile"].ConvertTo <string>("");
string duanxintext = Request["duanxintext1"].ConvertTo <string>("");
int comid = Request["comid"].ConvertTo <int>(0);
int userid = Request["userid"].ConvertTo <int>(0);
string isqunfa = Request["isqunfa"].ConvertTo <string>("no");
if (isqunfa == "yes")
{
isqunfa = "1";
}
else
{
isqunfa = "0";
}
string msg = "";
int sendstate = 0; //发送结果默认为0
string Invitecode = ""; //验证码
//获取公司微信号
B2b_company com = B2bCompanyData.GetAllComMsg(comid);
if (com.B2bcompanyinfo.Weixinname == "")
{
msg = phone + "公司微信号为空";
}
else
{
duanxintext = duanxintext.Replace("$comweixin$", com.B2bcompanyinfo.Weixinname);
//判断手机格式是否正确
bool isphone = Regex.IsMatch(phone, @"^1[358]\d{9}$", RegexOptions.IgnoreCase);
if (isphone)
{
//获得随机码
Invitecode = MemberCardData.GetRandomCode().ToString();
//使用随机码时，标记为已使用 防止重复码
ExcelSqlHelper.ExecuteNonQuery("update RandomCode set state = 1 where code = " + Invitecode);
duanxintext = duanxintext.Replace("$invitecode$", Invitecode);
sendstate = SendSmsHelper.SendSms(phone, duanxintext, comid, out msg);
}
else
{
msg = phone + "手机格式不正确";
}
}
//录入发送邀请码日志
B2b_invitecodesendlog log = new B2b_invitecodesendlog
{
Id = 0,
Phone = phone,
Smscontent = duanxintext,
Invitecode = Invitecode,
Senduserid = userid,
Sendtime = DateTime.Now,
Issendsuc = 0,
Isqunfa = int.Parse(isqunfa),
Remark = msg,
Comid = comid
};
if (sendstate > 0)
{
log.Issendsuc = 1;
}
else
{
log.Issendsuc = 0;
}
int result = new B2b_invitecodesendlogData().Inslog(log);
}
protected void Page_Load(object sender, EventArgs e)
{
string oper = Request["oper"].ConvertTo <string>("");
#region 导出卡号
if (oper == "outcardno")//导出卡号
{
int crid = Request["crid"].ConvertTo <int>(0);
int comid = Request["comid"].ConvertTo <int>(0);
if (crid > 0 && comid > 0)
{
DataTable table = ExcelSqlHelper.ExecuteDataTable("select cardcode from member_card where crid=" + crid + " and com_id=" + comid);
ExcelRender.RenderToExcel(table, Context, "卡号列表.xls");
}
}
#endregion
#region 导出旅游大巴产品结单名单
if (oper == "outlvyoubusorderlistbyorderstate")//导出旅游大巴产品结单名单
{
int proid = Request["proid"].ConvertTo <int>(0);
string gooutdate = Request["gooutdate"].ConvertTo <string>("");
int orderstate = Request["orderstate"].ConvertTo <int>(0);
object o = ExcelSqlHelper.ExecuteScalar("select pro_name from b2b_com_pro where id=" + proid);
//DataTable table = ExcelSqlHelper.ExecuteDataTable("select id as 订单号,u_name as 预订人,u_num as 预订人数,CONVERT(varchar(10),u_traveldate,120) as 出行时间,u_subdate as 预订时间 FROM b2b_order where u_traveldate='" + gooutdate + "' and pro_id=" + proid + " and order_state=" + orderstate);
//DataTable table = ExcelSqlHelper.ExecuteDataTable("SELECT [orderid] as 订单号,[name] as 乘车人姓名,[IdCard] as 乘车人身份证,[Nation] as 乘车人民族,CONVERT(varchar(10),[travel_time],120) as 乘车日期,[yuding_name] as 预订人姓名,[yuding_phone] as 预订人电话,[yuding_time] as 预订时间,pickuppoint as 上车地点,dropoffpoint as 下车地点 FROM [b2b_order_busNamelist] where orderid in (select id FROM b2b_order where u_traveldate='" + gooutdate + "' and pro_id=" + proid + " and order_state=" + orderstate + ")");
DataTable table = ExcelSqlHelper.ExecuteDataTable("SELECT a.[orderid] as 订单号,a.[name] as 乘车人姓名,a.[contactphone] as 乘车人电话,a.[IdCard] as 乘车人身份证,a.[Nation] as 乘车人民族,CONVERT(varchar(10),a.[travel_time],120) as 乘车日期,a.[yuding_name] as 预订人姓名,a.[yuding_phone] as 预订人电话,a.[yuding_time] as 预订时间,a.pickuppoint as 上车地点,a.dropoffpoint as 下车地点,b.company as 购票地点,a.contactremark as 备注 FROM agent_company as b right join [b2b_order_busNamelist] as a on a.agentid=b.id left join b2b_order as c on a.orderid=c.id where a.orderid in (select id FROM b2b_order where u_traveldate='" + gooutdate + "' and pro_id=" + proid + " and order_state=" + orderstate + ")");
ExcelRender.RenderToExcel(table, Context, gooutdate + o.ToString() + "结单名单.xls");
}
#endregion
#region 分销订单导入到excel
if (oper == "agentordertoexcel")
{
var comid = Request["comid"].ConvertTo <int>(0);
int agentid = Request["agentid"].ConvertTo <int>(0);
var key = Request["key"].ConvertTo <string>("");
var account = Request["account"].ConvertTo <string>("");
var order_state = Request["order_state"].ConvertTo <int>(0);
var begindate = Request["beginDate"].ConvertTo <string>("");
var enddate = Request["endDate"].ConvertTo <string>("");
var servertype = Request["servertype"].ConvertTo <int>(0);
object o1 = ExcelSqlHelper.ExecuteScalar("select company from agent_company where id=" + agentid);
object o2 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
List <B2b_order> agentorderlist = new B2bOrderData().GetAgentOrderList(comid, agentid, key, order_state, begindate, enddate, servertype);
if (agentorderlist.Count > 0)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("订单号", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单时间", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("产品名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单人", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("单价", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("数量", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("实收", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("状态", Type.GetType("System.String"));
DataRow newRow;
foreach (B2b_order morder in agentorderlist)
{
if (morder != null)
{
newRow = tblDatas.NewRow();
newRow["订单号"] = morder.Id;
newRow["提单时间"] = morder.U_subdate;
newRow["产品名称"] = morder.Order_type == 2 ? "预付款充值" : new B2bComProData().GetProById(morder.Pro_id.ToString(), morder.Speciid).Pro_name;
newRow["提单人"] = morder.U_name + "(" + morder.U_phone + ")";
newRow["单价"] = morder.Pay_price;
newRow["数量"] = morder.U_num;
newRow["实收"] = (morder.Pay_price) * (morder.U_num) + morder.Express - morder.Integral1 - morder.Imprest1;
newRow["状态"] = EnumUtils.GetName((OrderStatus)morder.Order_state);
tblDatas.Rows.Add(newRow);
}
}
ExcelRender.RenderToExcel(tblDatas, Context, o1.ToString() + "-" + o2.ToString() + "-订单列表.xls");
}
}
#endregion
#region 商户产品分销销售订单导入到excel
if (oper == "agentsalescodetoexcel")
{
var comid = Request["comid"].ConvertTo <int>(0);
var order_state = Request["order_state"].ConvertTo <int>(0);
var servertype = Request["servertype"].ConvertTo <int>(0);
var begindate = Request["beginDate"].ConvertTo <string>("");
var enddate = Request["endDate"].ConvertTo <string>("");
var key = Request["key"].ConvertTo <string>("");
var ordertype = Request["ordertype"].ConvertTo <int>(0);
var userid = Request["userid"].ConvertTo <int>(0);
int orderIsAccurateToPerson = 0;
int channelcompanyid = 0;
if (userid > 0)
{
Sys_Group group = new Sys_GroupData().GetGroupByUserId(userid);
if (group != null)
{
//判断订单是否要求精确到渠道人
orderIsAccurateToPerson = group.OrderIsAccurateToPerson;
Member_Channel_company channelcom = new MemberChannelcompanyData().GetChannelCompanyByUserId(userid);
if (channelcom != null)
{
channelcompanyid = channelcom.Id;
}
}
}
object o2 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
List <B2b_order> directorderlist = new B2bOrderData().GetOrderList(comid, order_state, servertype, begindate, enddate, key, userid, orderIsAccurateToPerson, channelcompanyid, ordertype);
if (directorderlist.Count > 0)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("订单号", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单时间", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("产品名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单人", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("单价", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("数量", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("实收", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("收货地址", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("状态", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("分销商", Type.GetType("System.String"));
DataRow newRow;
foreach (B2b_order morder in directorderlist)
{
if (morder != null)
{
newRow = tblDatas.NewRow();
newRow["订单号"] = morder.Id;
newRow["提单时间"] = morder.U_subdate;
newRow["产品名称"] = morder.Order_type == 2 ? "预付款充值" : new B2bComProData().GetProById(morder.Pro_id.ToString(), morder.Speciid).Pro_name;
newRow["提单人"] = morder.U_name + "(" + morder.U_phone + ")";
newRow["单价"] = morder.Pay_price;
newRow["数量"] = morder.U_num;
newRow["实收"] = (morder.Pay_price) * (morder.U_num) + morder.Express - morder.Integral1 - morder.Imprest1;
newRow["收货地址"] = morder.Province + "-" + morder.City + "-" + morder.Address;
newRow["状态"] = EnumUtils.GetName((OrderStatus)morder.Order_state);
newRow["分销商"] = new Agent_companyData().GetAgentName(morder.Agentid);
tblDatas.Rows.Add(newRow);
}
}
ExcelRender.RenderToExcel(tblDatas, Context, o2.ToString() + "-后台销售订单列表.xls");
}
}
#endregion
#region 商户产品分销倒码订单导入到excel
if (oper == "agentbackcodetoexcel")
{
var comid = Request["comid"].ConvertTo <int>(0);
var order_state = Request["order_state"].ConvertTo <int>(0);
var servertype = Request["servertype"].ConvertTo <int>(0);
var begindate = Request["beginDate"].ConvertTo <string>("");
var enddate = Request["endDate"].ConvertTo <string>("");
var key = Request["key"].ConvertTo <string>("");
var ordertype = Request["ordertype"].ConvertTo <int>(0);
var userid = Request["userid"].ConvertTo <int>(0);
int orderIsAccurateToPerson = 0;
int channelcompanyid = 0;
if (userid > 0)
{
Sys_Group group = new Sys_GroupData().GetGroupByUserId(userid);
if (group != null)
{
//判断订单是否要求精确到渠道人
orderIsAccurateToPerson = group.OrderIsAccurateToPerson;
Member_Channel_company channelcom = new MemberChannelcompanyData().GetChannelCompanyByUserId(userid);
if (channelcom != null)
{
channelcompanyid = channelcom.Id;
}
}
}
object o2 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
List <B2b_order> directorderlist = new B2bOrderData().GetOrderList(comid, order_state, servertype, begindate, enddate, key, userid, orderIsAccurateToPerson, channelcompanyid, ordertype);
if (directorderlist.Count > 0)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("订单号", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单时间", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("产品名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单人", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("单价", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("数量", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("实收", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("收货地址", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("状态", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("分销商", Type.GetType("System.String"));
DataRow newRow;
foreach (B2b_order morder in directorderlist)
{
if (morder != null)
{
newRow = tblDatas.NewRow();
newRow["订单号"] = morder.Id;
newRow["提单时间"] = morder.U_subdate;
newRow["产品名称"] = morder.Order_type == 2 ? "预付款充值" : new B2bComProData().GetProById(morder.Pro_id.ToString(), morder.Speciid).Pro_name;
newRow["提单人"] = morder.U_name + "(" + morder.U_phone + ")";
newRow["单价"] = morder.Pay_price;
newRow["数量"] = morder.U_num;
newRow["实收"] = (morder.Pay_price) * (morder.U_num) + morder.Express - morder.Integral1 - morder.Imprest1;
newRow["收货地址"] = morder.Province + "-" + morder.City + "-" + morder.Address;
newRow["状态"] = EnumUtils.GetName((OrderStatus)morder.Order_state);
newRow["分销商"] = new Agent_companyData().GetAgentName(morder.Agentid);
tblDatas.Rows.Add(newRow);
}
}
ExcelRender.RenderToExcel(tblDatas, Context, o2.ToString() + "-倒码订单列表.xls");
}
}
#endregion
#region 直销订单导入到excel
if (oper == "directordertoexcel")
{
var comid = Request["comid"].ConvertTo <int>(0);
var order_state = Request["order_state"].ConvertTo <int>(0);
var servertype = Request["servertype"].ConvertTo <int>(0);
var begindate = Request["beginDate"].ConvertTo <string>("");
var enddate = Request["endDate"].ConvertTo <string>("");
var key = Request["key"].ConvertTo <string>("");
var ordertype = Request["ordertype"].ConvertTo <int>(0);
var userid = Request["userid"].ConvertTo <int>(0);
int orderIsAccurateToPerson = 0;
int channelcompanyid = 0;
if (userid > 0)
{
Sys_Group group = new Sys_GroupData().GetGroupByUserId(userid);
if (group != null)
{
//判断订单是否要求精确到渠道人
orderIsAccurateToPerson = group.OrderIsAccurateToPerson;
Member_Channel_company channelcom = new MemberChannelcompanyData().GetChannelCompanyByUserId(userid);
if (channelcom != null)
{
channelcompanyid = channelcom.Id;
}
}
}
object o2 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
List <B2b_order> directorderlist = new B2bOrderData().GetOrderList(comid, order_state, servertype, begindate, enddate, key, userid, orderIsAccurateToPerson, channelcompanyid, ordertype);
if (directorderlist.Count > 0)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("订单号", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单时间", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("产品名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("提单人", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("身份证", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("手机", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("单价", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("数量", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("实收", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("收货地址", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("状态", Type.GetType("System.String"));
DataRow newRow;
foreach (B2b_order morder in directorderlist)
{
if (morder != null)
{
newRow = tblDatas.NewRow();
newRow["订单号"] = morder.Id;
newRow["提单时间"] = morder.U_subdate;
newRow["产品名称"] = morder.Order_type == 2 ? "预付款充值" : new B2bComProData().GetProById(morder.Pro_id.ToString(), morder.Speciid).Pro_name;
newRow["提单人"] = morder.U_name;
newRow["身份证"] = morder.U_idcard;
newRow["手机"] = morder.U_phone;
newRow["单价"] = morder.Pay_price;
newRow["数量"] = morder.U_num;
newRow["实收"] = (morder.Pay_price) * (morder.U_num) + morder.Express - morder.Integral1 - morder.Imprest1;
newRow["收货地址"] = morder.Province + "-" + morder.City + "-" + morder.Address;
newRow["状态"] = EnumUtils.GetName((OrderStatus)morder.Order_state);
tblDatas.Rows.Add(newRow);
}
}
ExcelRender.RenderToExcel(tblDatas, Context, o2.ToString() + "-直销订单列表.xls");
}
}
#endregion
#region 订房信息导入到excel
if (oper == "hotelordertoexcel")
{
int comid = Request["comid"].ConvertTo <int>(0);
string begindate = Request["begindate"].ConvertTo <string>("");
string enddate = Request["enddate"].ConvertTo <string>("");
int productid = Request["productid"].ConvertTo <int>(0);
string orderstate = Request["orderstate"].ConvertTo <string>("");
try
{
object o1 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
object o2 = ExcelSqlHelper.ExecuteScalar("select pro_name from b2b_com_pro where id=" + productid);
object o3 = ExcelSqlHelper.ExecuteScalar("select projectname from b2b_com_project where id=(select projectid from b2b_com_pro where id=" + productid + ")");
DataTable tblDatas = new B2bOrderData().HotelOrderlist(comid, begindate, enddate, productid, orderstate, 1);
if (begindate == "" || enddate == "")
{
ExcelRender.RenderToExcel(tblDatas, Context, o1.ToString() + "-" + o2.ToString() + "-" + o3.ToString() + "-订房订单列表.xls");
}
else
{
ExcelRender.RenderToExcel(tblDatas, Context, o1.ToString() + "-" + o2.ToString() + "-" + o3.ToString() + "(" + DateTime.Parse(begindate).ToString("yyyy-MM-dd") + "到" + DateTime.Parse(enddate).ToString("yyyy-MM-dd") + ")" + "-订房订单列表.xls");
}
}
catch { }
}
#endregion
#region 载分销特定项目下的验票记录
if (oper == "downagentpro_yplist")
{
int proid = Request["proid"].ConvertTo <int>(0);
string startime = Request["startime"].ConvertTo <string>("");
string endtime = Request["endtime"].ConvertTo <string>("");
int agentid = 0;
if (Session["Agentid"] != null)
{
agentid = Int32.Parse(Session["Agentid"].ToString());
}
object o1 = ExcelSqlHelper.ExecuteScalar("select company from agent_company where id=" + agentid);
object o2 = ExcelSqlHelper.ExecuteScalar("select pro_name from b2b_com_pro where id=" + proid);
int comid = new B2bComProData().GetComidByProid(proid);
var condition = " a.action=1 and a.com_id=" + comid + " and a.a_state=" + (int)ECodeOperStatus.OperSuc + " and a.pno in (select pno from b2b_eticket where 1=1 ";
if (proid != 0)
{
condition += " and pro_id=" + proid;
}
if (agentid != 0)
{
condition += " and pro_id in (select id from b2b_com_pro where projectid in (select projectid from b2b_project_agent where agentid=" + agentid + ") )";
}
condition += ")";
if (startime != "")
{
condition += " and a.actiondate>='" + startime + "'";
}
if (endtime != "")
{
endtime = DateTime.Parse(endtime).AddDays(1).ToString();
condition += " and a.actiondate<'" + endtime + "'";
}
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select b.e_proname [产品名称],a.pno [电子码],b.pnum [订购数量],a.use_pnum [本次使用数量],a.actiondate [验证时间],a.posid [验证pos],b.e_face_price [门市价],b.e_sale_price [结算价],c.company[出票方] from b2b_etcket_log as a left join b2b_eticket as b on a.com_id=b.com_id and a.pno=b.pno and a.eticket_id=b.id left join agent_company as c on b.agent_id=c.id where " + condition + " order by a.id desc"),
Context, "分销:" + o1.ToString() + "-" + "产品:" + o2.ToString() + "-验证电子票列表(" + startime + "--" + endtime + ")" + ".xls");
}
#endregion
#region 财务导出到excel
if (oper == "Financelisttoexcel")
{
var comid = Request["comid"].ConvertTo <int>(0);
var begindate = Request["beginDate"].ConvertTo <string>("");
var enddate = Request["endDate"].ConvertTo <string>("");
var financedata = new B2bFinanceData();
var directorderlist = financedata.FinanceallList(comid, begindate, enddate);
if (directorderlist.Count > 0)
{
DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false;
dc = tblDatas.Columns.Add("订单号", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("时间", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("产品名称", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("类型", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("金额", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("余额", Type.GetType("System.String"));
dc = tblDatas.Columns.Add("渠道", Type.GetType("System.String"));
DataRow newRow;
foreach (B2b_Finance morder in directorderlist)
{
if (morder != null)
{
newRow = tblDatas.NewRow();
newRow["订单号"] = morder.Order_id;
newRow["时间"] = morder.Subdate;
newRow["产品名称"] = morder.Servicesname;
newRow["类型"] = morder.Payment_type;
newRow["金额"] = morder.Money;
newRow["余额"] = morder.Over_money;
newRow["渠道"] = morder.Money_come == "wx"?"微信支付":morder.Money_come;
tblDatas.Rows.Add(newRow);
}
}
ExcelRender.RenderToExcel(tblDatas, Context, comid.ToString() + DateTime.Now.ToString("yyyyMMddHHmmss") + "-财务明细列表.xls");
}
}
#endregion
#region 注释内容
//int splitid = Request["splitid"].ConvertTo<int>(0);//微信拆分表id
//if (splitid == 0)//下载excel导入样例操作,暂时没有用到
//{
// DataTable table = new DataTable();
// table.Columns.Add("姓名", typeof(string));
// table.Columns.Add("手机", typeof(string));
// table.Columns.Add("邮箱", typeof(string));
// table.Columns.Add("微信号", typeof(string));
// table.Columns.Add("预付款", typeof(string));
// table.Columns.Add("积分", typeof(string));
// table.Columns.Add("目的地", typeof(string));
// table.Columns.Add("年龄段", typeof(string));
// table.Columns.Add("会员级别", typeof(string));
// for (int i = 0; i < 10; i++)
// {
// string name = "张三" + i.ToString();
// Thread.Sleep(1);
// string phone = "手机号" + i.ToString();
// Thread.Sleep(1);
// string email = "邮箱地址" + i.ToString();
// Thread.Sleep(1);
// string weixin = "微信" + i.ToString();
// Thread.Sleep(1);
// string imprest = i.ToString();
// Thread.Sleep(1);
// string integral = i.ToString();
// Thread.Sleep(1);
// string destination = "目的地" + i.ToString();
// Thread.Sleep(1);
// string agegroup = "年龄段" + i.ToString();
// Thread.Sleep(1);
// string crmlevel = "1";
// Thread.Sleep(1);
// table.Rows.Add(name, phone, email, weixin, imprest, integral, destination, agegroup, crmlevel);
// }
// ExcelRender.RenderToExcel(table, Context, "Excel导入样例.xls");
//}
//else//下载获取的微信关注用户表 的拆分用户列表,暂时没有到
//{
// ObtainGzListSplit model = new ObtainGzListSplitData().GetObtainGzListSplit(splitid);
// if (model != null)
// {
// string openidstr = model.Splitopenid;
// string[] str = openidstr.Split(',');
// DataTable table = new DataTable();
// table.Columns.Add("姓名", typeof(string));
// table.Columns.Add("手机", typeof(string));
// table.Columns.Add("邮箱", typeof(string));
// table.Columns.Add("微信号", typeof(string));
// for (int i = 0; i < str.Length; i++)
// {
// string name = "";
// Thread.Sleep(1);
// string phone = "";
// Thread.Sleep(1);
// string email = "";
// Thread.Sleep(1);
// string weixin = str[i];
// Thread.Sleep(1);
// table.Rows.Add(name, phone, email, weixin);
// }
// ExcelRender.RenderToExcel(table, Context, model.Comid + "-" + splitid + ".xls");
// }
// else
// {
// Label1.Text = "获取拆分用户列表为空";
// }
//}
#endregion
}
