private void xxtj()
{
// string sql = string.Format(@"select u.*,z.编码,z.客户名称,z.所属工业园,z.所属房产,z.合同开始时间,z.合同结束时间 from sq8szxlx.user_sf_zb u left join sq8szxlx.zpgl z on
// u.合同编号=z.编码 where u.缴费状态='已缴费' and not (z.所属工业园 is null)");
string select = "select u.*,z.编码,z.客户名称,z.所属工业园,z.所属房产,z.合同开始时间,z.合同结束时间 ";
string from = " from sq8szxlx.user_sf_zb u left join sq8szxlx.zpgl z on u.合同编号=z.编码 ";
string where = string.Format(@" where u.日期年='{0}' and u.日期月='{1}' and u.缴费状态='已缴费' and not (z.所属工业园 is null) ", Request.Params["nian"], Request.Params["yue"]);
if (Common.hasValue(Request.Params["iField"]))
{
where += string.Format(" and z.客户名称 like '%{0}%'", Request.Params["iField"]);
}
if (Common.hasValue(Request.Params["gyy"]))
{
where += string.Format(" and z.所属工业园='{0}'", Request.Params["gyy"]);
}
if (Common.hasValue(Request.Params["gyy_lx"]))
{
where += string.Format(" and z.房产类型='{0}'", Request.Params["gyy_lx"]);
}
// 2. 获取总数
//string count = DBHelper.GetVar("select count(*) " + from + where).ToString();
//if (count == null) return;
// 3. 获取数据
//string sql = string.Format(@"{0} {1} {2} and u.id not in (select top {3} u.id {1} {2}) order by u.日期年,u.日期月,日期日",
// select, from, where, Request.Params["start"]);
string sql = select + from + where;
ResultObject user_sf_zb = DBHelper.GetResult(sql);
JSONArray ja = new JSONArray();
for (int i = 0; i < user_sf_zb.Count; i++)
{
RowObject row = user_sf_zb[i];
JSONObject jo = new JSONObject();
jo.Add("序号", i + 1);
jo.Add("编码", row["编码"].ToString());
jo.Add("客户名称", row["客户名称"].ToString());
jo.Add("所属工业园", row["所属工业园"].ToString());
jo.Add("所属房产", row["所属房产"].ToString());
jo.Add("合同开始时间", row["合同开始时间"].ToString());
jo.Add("合同结束时间", row["合同结束时间"].ToString());
jo.Add("月份", row["日期月"].ToString());
jo.Add("费用", row["总费用"].ToString());
ja.Add(jo);
}
Response.Write(JSONConvert.SerializeArray(ja));
}
private void edit_gdxfx()
{
string responseError = "{success: false}";
string sql1 = string.Format("select * from sq8szxlx.zpgl where id='{0}'", Request.Params["id"]);
RowObject r1 = DBHelper.GetRow(sql1);
if (r1 == null)
{
Response.Write(responseError);
Response.End();
return;
}
string gyy_mc = r1["所属工业园"].ToString();
string fclx = r1["房产类型"].ToString();
string htbh = r1["编码"].ToString();
string sql2 = string.Format("select * from sq8szxlx.gyy_lb_fclx_lb_xflx where 工业园名称='{0}' and 房产类型='{1}' order by 序号 asc",
gyy_mc, fclx);
ResultObject r2 = DBHelper.GetResult(sql2);
JSONArray ja = new JSONArray();
int i = 1;
foreach (RowObject item in r2)
{
JSONObject jo = new JSONObject();
string xfxm = item["消费项目"].ToString();
string rq = r1["合同开始时间"].ToString();
string sql3 = string.Format("select * from sq8szxlx.zpgl_lx_lb where 合同编号='{0}' and 消费项目='{1}'", htbh, xfxm);
RowObject r3 = DBHelper.GetRow(sql3);
string sql4 = string.Format("select * from sq8szxlx.user_sf_lb where 合同编号='{0}' and 收费项目='{1}' and 日期='{2}'", htbh, xfxm, rq);
RowObject r4 = DBHelper.GetRow(sql4);
jo.Add("id", item["id"]);
jo.Add("编号", i);
jo.Add("消费项目", xfxm);
jo.Add("消费类型", item["消费类型"]);
jo.Add("值", (r3 == null) ? item["值"] : r3["值"]);
jo.Add("倍率", (r3 == null) ? item["倍率"] : r3["倍率"]);
jo.Add("损耗", (r3 == null) ? item["损耗"] : r3["损耗"]);
jo.Add("滞纳金", (r3 == null) ? item["滞纳金"] : r3["滞纳金"]);
jo.Add("前期读数", item["消费类型"].ToString() == "动态" ? r4["读数"] : "-");
jo.Add("说明", (r3 == null) ? item["说明"] : r3["说明"]);
jo.Add("读数导入", (r4 != null && r4["录入状态"].ToString() == "已录入") ? "√" : "×");
jo.Add("项目导入", (r4 != null && r4["值"] != "") ? "√" : "×");
i++;
ja.Add(jo);
}
Response.Write(string.Format("{{'success': true, 'data':{0}}}", JSONConvert.SerializeArray(ja)));
}
protected void JSCall(string method, params object[] argv)
{
//把参数转换成json
// JSONObject jObj = new JSONObject();
JSONArray param = new JSONArray();
foreach (object o in argv)
{
param.Add(o);
}
//jObj.Add("Cmd", method);
// jObj.Add("Param", argv);
string script = string.Format("{0}({1})", method, JSONConvert.SerializeArray(param));
//使用后台工作线程
BackgroundWorker JSCallWorker = new BackgroundWorker();
JSCallWorker.DoWork += new DoWorkEventHandler(JSCallWorker_DoWork);
JSCallWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(JSCallWorker_RunWorkerCompleted);
m_InvokeQueue.Enqueue(script);
JSCallWorker.RunWorkerAsync();
}
private void fltj()
{
string gyy = Request.Params["gyy"];
string gyy_fclx = Request.Params["gyy_lx"];
string gyy_xfxm = Request.Params["xfxm"];
string nian = Request.Params["nian"];
string yue = Request.Params["yue"];
bool xfxtj = Request.Params["xfxtj"] == "true"?true:false;
// 1. 查询工业园
string sql = string.Format("select * from sq8szxlx.gyy_lb ");
if (Common.hasValue(gyy))
{
sql = string.Format("select * from sq8szxlx.gyy_lb where 工业园名称='{0}'", gyy);
}
string where_fclx = Common.hasValue(gyy_fclx) ? string.Format(" and z.房产类型='{0}'", gyy_fclx) : "";
string where_xfxm = Common.hasValue(gyy_xfxm) ? string.Format(" and u.收费项目='{0}'", gyy_xfxm) : "";
string where_nian = Common.hasValue(nian) ? string.Format(" and u.日期年='{0}'", nian) : "";
string where_yue = Common.hasValue(yue) ? string.Format(" and u.日期月='{0}'", yue) : "";
ResultObject gyy_lb = DBHelper.GetResult(sql);
JSONArray ja = new JSONArray();
int k = 0;
for (int i = 0; i < gyy_lb.Count; i++)
{
RowObject row = gyy_lb[i];
string gyy_mc = row["工业园名称"].ToString();
string fclx = Common.hasValue(gyy_fclx) ?gyy_fclx:"(全部)";
string xfxm = Common.hasValue(gyy_xfxm) ? gyy_xfxm : "(全部)";
string yf = Common.hasValue(yue) ? yue : "(全部)";
if (xfxtj && !Common.hasValue(gyy_xfxm))
{
// 查询工业园所有消费项
sql = string.Format(" select distinct 消费项目 as sfxm from sq8szxlx.zpgl_lx_lb where 房产类型='厂房' and 所属工业园='{0}'", gyy_mc);
ResultObject zpgl_lx_lb = DBHelper.GetResult(sql);
for (int j = 0; j < zpgl_lx_lb.Count; j++) //详细统计
{
k++;
RowObject row2 = zpgl_lx_lb[j];
where_xfxm = string.Format(" and u.收费项目='{0}'", row2["sfxm"]);
string sql_sf = string.Format(@"select sum(u.费用) as total from sq8szxlx.user_sf_lb u left join sq8szxlx.zpgl z on
u.合同编号=z.编码 left join sq8szxlx.user_sf_zb zb on zb.合同编号=u.合同编号 and zb.日期年=u.日期年 and zb.日期月=u.日期月 where zb.缴费状态<>'未缴费' and z.所属工业园='{0}' {1} {2} {3} {4}" , gyy_mc, where_fclx, where_xfxm, where_nian, where_yue);
object total = DBHelper.GetVar(sql_sf);
JSONObject jo = new JSONObject();
jo.Add("序号", k);
jo.Add("工业园名称", gyy_mc);
jo.Add("房产类型", fclx);
jo.Add("消费项目", row2["sfxm"]);
jo.Add("月份", yf);
jo.Add("费用", Math.Round(Convert.ToDouble(total), 0));
ja.Add(jo);
}
}
else
{
//分类统计
// 2. 查询合同
string sql_sf = string.Format(@"select sum(u.费用) as total from sq8szxlx.user_sf_lb u left join sq8szxlx.zpgl z on
u.合同编号=z.编码 left join sq8szxlx.user_sf_zb zb on zb.合同编号=u.合同编号 and zb.日期年=u.日期年 and zb.日期月=u.日期月 where zb.缴费状态<>'未缴费' and z.所属工业园='{0}' {1} {2} {3} {4}" , gyy_mc, where_fclx, where_xfxm, where_nian, where_yue);
object total = DBHelper.GetVar(sql_sf);
JSONObject jo = new JSONObject();
jo.Add("序号", i + 1);
jo.Add("工业园名称", gyy_mc);
jo.Add("房产类型", fclx);
jo.Add("消费项目", xfxm);
jo.Add("月份", yf);
jo.Add("费用", Math.Round(Convert.ToDouble(total), 0));
ja.Add(jo);
}
}
Response.Write(JSONConvert.SerializeArray(ja));
}
private void list_zb()
{
//取得合同开始结束时间
RowObject r = DBHelper.GetRow(string.Format(@"select * from sq8szxlx.zpgl where 编码='{0}'", Request.Params["htbh"]));
if (r == null)
{
Response.Write("success:false");
Response.End();
return;
}
int year1 = Int32.Parse(r["合同开始时间_年"].ToString());
int year2 = Int32.Parse(r["合同结束时间_年"].ToString());
int month1 = Int32.Parse(r["合同开始时间_月"].ToString());
int month2 = Int32.Parse(r["合同结束时间_月"].ToString());
int no = 1;
JSONArray ja = new JSONArray();
for (int i = year1; i <= year2; i++)
{
int j = 1;
if (i == year1)
{
j = month1;
}
int j_max = 12;
if (i == year2)
{
j_max = month2;
}
for (; j <= j_max; j++)
{
JSONObject jo = new JSONObject();
jo.Add("序号", no);
no++;
// 读取总表数据
string sql = string.Format(@"select * from sq8szxlx.user_sf_zb where 合同编号='{0}' and 日期年='{1}' and 日期月='{2}' order by id desc",
Request.Params["htbh"], i, j);
r = DBHelper.GetRow(sql);
jo.Add("年份月份", string.Format("{0}/{1}", i, j));
if (r != null)
{
jo.Add("id", r["id"]);
jo.Add("单据编号", r["单据编号"]);
jo.Add("总费用", (r["总费用"] is DBNull)?0: Math.Round(Convert.ToDecimal(r["总费用"]), 0));
jo.Add("缴费金额", r["缴费金额"]);
jo.Add("余额", r["余额"]);
jo.Add("录入状态", r["录入状态"]);
jo.Add("缴费状态", r["缴费状态"]);
}
else
{
jo.Add("单据编号", "-");
jo.Add("总费用", "-");
jo.Add("缴费金额", "-");
jo.Add("余额", "-");
if (i == DateTime.Now.Year && j == DateTime.Now.Month)
{
jo.Add("录入状态", "未录入");
jo.Add("缴费状态", "未缴费");
}
else if (i > DateTime.Now.Year || (i == DateTime.Now.Year && j > DateTime.Now.Month))
{
jo.Add("录入状态", "不能录入");
jo.Add("缴费状态", "不能缴费");
}
else
{
jo.Add("录入状态", "未录入");
jo.Add("缴费状态", "未缴费");
}
}
ja.Add(jo);
}
}
string ret = JSONConvert.SerializeArray(ja);
Response.Write(ret);
}