//客户端查询
public void pgsearchclient(HttpContext context, string starttime, string endtime, int page, int rows, string phone,string province)
{
endtime = Convert.ToDateTime(endtime).AddDays(1).ToString("yyyy-MM-dd");
string sql = "SELECT ";
sql += " CASE WHEN ffoi.city_id in(SELECT id from fc_city where fccc.p_id=0) then fccc.short_name when ffoi.city_id=0 then '全国' ELSE fcc.short_name end as province, ";
sql += " CASE WHEN ffoi.city_id in(SELECT id from fc_city where fccc.p_id=0) then fccc.short_name when ffoi.city_id=0 then '全国' ELSE fc.short_name end as city, ";
sql += " CASE WHEN ffoi.operators=0 THEN '电信' WHEN ffoi.operators=1 THEN '移动' WHEN ffoi.operators=2 THEN '联通' END as operatorsname,";
sql += " ffo.mobile,";
sql += " to_char(ffo.updatetime,'yyyy-MM-dd hh24:mi:ss') as updatetime";
sql += " ,ffoi.display_name";
sql += " ,ffoi.product_name";
sql += " ,ffoi.product_code,";
sql += " ffoi.tag_type";
sql += " ,ffoi.fee";
sql += " ,ffoi.flow_size";
sql += " ,ffoi.product_id";
sql += " ,ffoi.flow_type";
sql += " ,ffo.cash_fee";
sql += " ,ffo.app_code";
sql += " ,ffo.status";
sql += " ,'客户端' as channel";
sql += " ,ffoi.operators";
sql += " ,ffo.id as zongkuid";
sql += " ,ffo.order_no";
sql += " ,to_char(ffo.createtime,'yyyy-MM-dd hh24:mi:ss') as createtime";
sql += " ,ffo.unique_id";
sql += " ,to_char(ffo.success_time,'yyyy-MM-dd hh24:mi:ss') as success_time";
sql += " ,ffo.send_sms_time";
sql += " ,ffoi.city_id";
sql += " FROM fc_flow_order as ffo ";
sql += " LEFT JOIN fc_flow_order_item as ffoi on ffoi.order_id=ffo.id ";
sql += " LEFT JOIN fc_city as fc on ffoi.city_id=fc.id and fc.is_city=1 ";
sql += " LEFT JOIN fc_city as fcc on fcc.id=fc.p_id and fcc.depth=2 ";
sql += "LEFT JOIN fc_city as fccc on ffoi.city_id=fccc.id and fccc.p_id=0 where 1=1 ";
if (starttime != "")
{
sql += " and ffo.updatetime>='" + starttime + "'";
}
if (endtime != "")
{
sql += " and ffo.updatetime<='" + endtime + "'";
}
if (phone != "")
{
sql += " and ffo.mobile='" + phone + "'";
} if (province != "")
{
sql += " and fcc.short_name='" + province + "'";
}
DataSet ds = ExecuteQuery2(sql);
DataTable dt = ds.Tables[0];
int count = dt.Rows.Count;
DataSet ds2 = comh.SplitDataSet(ds, page, rows);
context.Session["clientpgsearchTable"] = dt;
string strJson = "{\"total\":" + count + ",\"rows\":" + Newtonsoft.Json.JsonConvert.SerializeObject(ds2.Tables[0]) + "}";//DataSet数据转化为Json数据
context.Response.Write(strJson);//返回给前台页面
context.Response.End();
}
public DataSet ExecuteQuery2(string StrText)
{
string StrConnection = ConfigurationManager.ConnectionStrings["sqlConnection5"].ConnectionString;
using (NpgsqlConnection conn = new NpgsqlConnection(StrConnection))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(StrText, conn);
NpgsqlDataAdapter NpgDa = new NpgsqlDataAdapter(cmd);
DataSet ds = new DataSet();
NpgDa.Fill(ds, "ds");
cmd.Parameters.Clear();
return ds;
}
}