/// <summary>
/// 通过姓名搜索DKP信息
/// </summary>
/// <param name="context"></param>
public void DKP_sc_info(HttpContext context)
{
string name = context.Request["name"];
string SQL = "SELECT TOP 10 [日期],[积分],[说明] FROM [CNGTZ].[dbo].[TZ_人员_积分] where 姓名=@姓名 ORDER BY 日期 desc";
string info = " {\"top\":@top@,\"row\":@row@}";
string row = "[]", top = "[]";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("姓名", name.Trim());
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1 });
if (dt != null)
{
row = Json.TableToJson(dt);
}
SQL = @"SELECT a.[姓名],isnull(b.累计,0) as 累计,isnull(c.消费,0)as 消费,isnull(d.当前,0)as 当前 FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN (
SELECT [姓名],sum([积分]) as 当前 FROM [CNGTZ].[dbo].[TZ_人员_积分] group BY 姓名
) d ON a.姓名=d.姓名 LEFT JOIN (
SELECT [姓名],sum([积分]) as 累计 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分>0 group BY 姓名
) b ON a.姓名=b.姓名 LEFT JOIN (
SELECT [姓名],sum([积分]) as 消费 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分<0 group BY 姓名
) c ON a.姓名=c.姓名
WHERE a.姓名=@姓名";
dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1 });
if (dt != null)
{
top = Json.TableToJson(dt);
}
info = info.Replace("@top@", top);
info = info.Replace("@row@", row);
context.Response.Write(info);
}
/// <summary>
/// 检查站点数据是否有错误数据
/// </summary>
public void Checking(HttpContext context)
{
string SQL = @"SELECT 站点 as zd,b.简称 as 站点
,[日期]
,[枪号]
,[流水号]
,[气量]
,[金额]
,[单价]
,[用户号]
,[卡名]
,[车类型]
,[车牌号]
,[消费方式]
FROM [CNGQT].[dbo].[CNG_充装检查] left JOIN cngtz.dbo.TZ_站点 b
ON 站点=b.ID
where 卡名='班组卡' AND 消费方式<>'记帐金额'";
string row = "[]";
int n = 0;
bool ok = false;
string retext = "{\"ok\":@ok@,\"数量\":@数量@,\"row\":@row@}";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt != null && dt.Rows.Count > 0)
{
ok = true;
n = dt.Rows.Count;
row = Json.TableToJson(dt);
}
retext = retext.Replace("@ok@", ok.ToString().ToLower());
retext = retext.Replace("@数量@", n.ToString());
retext = retext.Replace("@row@", row);
context.Response.Write(retext);
}
public void getgas(HttpContext context)
{
string time1 = context.Request["time1"];
string time2 = context.Request["time2"];
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable("EXECUTE [WebApp].[dbo].[CC_每小时气量分站点] '" + time1 + "','" + time2 + "'");
if (dt == null) { context.Response.Write("{}"); return; };
string str1 = "", str2 = "", str3 = "", str4 = "";
//double r1=0,r2=0,r3=0;
int n = 0;
foreach (DataRow dr in dt.Rows)
{
n++;
str1 = str1 + dr["南高"].ToString() + ",";
str2 = str2 + dr["西路"].ToString() + ",";
str3 = str3 + dr["麻柳沱"].ToString() + ",";
str4 = str4 + "\" " + n.ToString() + " \",";
}
str1 = str1.Substring(0, str1.Length - 1);
str2 = str2.Substring(0, str2.Length - 1);
str3 = str3.Substring(0, str3.Length - 1);
str4 = str4.Substring(0, str4.Length - 1);
string text = "{\"南高\":[" + str1 + "],\"西路\":[" + str2 + "],\"麻柳沱\":[" + str3 + "],\"x\":[" + str4 + "]}";
//Json.TableToJson(dt);
context.Response.Write(text);
}
/// <summary>
/// 返回 站点 设备列表 json
/// </summary>
/// <param name="context"></param>
public void TZ_SBlist(HttpContext context)
{
string zd = context.Request["zd"];
string SQL = "SELECT [id],[设备名称] as text FROM [CNGCL].[dbo].[WX_设备台账] where 站点=" + zd + " order by 编号";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
context.Response.Write(Json.TableToJson(dt));
}
/// <summary>
/// 取当前气量 分小时
/// </summary>
/// <param name="context"></param>
public void getgasdq(HttpContext context)
{
string time1 = DateTime.Now.ToShortDateString();
string time2 = Convert.ToDateTime(time1).AddDays(1).ToShortDateString();
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable("EXECUTE [WebApp].[dbo].[CC_每小时气量分站点] '" + time1 + "','" + time2 + "'");
if (dt == null) { context.Response.Write("{}"); return; };
string str1 = "", str2 = "", str3 = "", str4 = "";
//double r1=0,r2=0,r3=0;
int n = 0;
foreach (DataRow dr in dt.Rows)
{
n++;
//r1=Math.Round( Convert.ToDouble( dr["南高"].ToString ()));
//r2=Math.Round( Convert.ToDouble( dr["西路"].ToString ()));
//r3=Math.Round( Convert.ToDouble( dr["麻柳沱"].ToString ()));
//str1 = str1 + r1.ToString() + ",";
//str2 = str2 + r2.ToString() + ",";
//str3 = str3 + r3.ToString() + ",";
str1 = str1 + dr["南高"].ToString() + ",";
str2 = str2 + dr["西路"].ToString() + ",";
str3 = str3 + dr["麻柳沱"].ToString() + ",";
str4 = str4 + "\" " + n.ToString() + " \",";
}
str1 = str1.Substring(0, str1.Length - 1);
str2 = str2.Substring(0, str2.Length - 1);
str3 = str3.Substring(0, str3.Length - 1);
str4 = str4.Substring(0, str4.Length - 1);
string text = "{\"南高\":[" + str1 + "],\"西路\":[" + str2 + "],\"麻柳沱\":[" + str3 + "],\"x\":[" + str4 + "]}";
//Json.TableToJson(dt);
context.Response.Write(text);
}
public void getlog(HttpContext context)
{
int row = int.Parse(context.Request["rows"].ToString());
int page = int.Parse(context.Request["page"].ToString());
string name = context.Request["name"];
DAL dal = new DAL();
string SQLtext = "SELECT [ID],[日期],[用户名],[模块程序] as 操作模块,[操作记录] FROM [WebApp].[dbo].[DL_操作日志] where 用户名='"+name+"' AND 是否隐藏=0 ORDER BY 日期 DESC ";
DataTable dtab = dal.RunSqlDataTable(SQLtext);
int n = dtab.Rows.Count;
if (n <= 0)
{
string txtno = "{ \"total\": 0, \"rows\": [] }";
context.Response.Write(txtno);
return;
}
dtab = BLL.tools.GetPagedTable(dtab, page, row);
string txt = "{\"total\":" + n + ",\"rows\":" + Json.TableToJson(dtab) + "}";
Sys.DebugMes(txt);
context.Response.Write(txt);
}
/// <summary>
/// 初始化 部门树
/// </summary>
/// <param name="context"></param>
public void BM_tree(HttpContext context)
{
string SQL = "SELECT [部门编号],[部门] FROM [CNGTZ].[dbo].[TZ_部门] WHERE 上级部门='0' order by 排序";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
string node = "{{\"id\":\"{0}\",\"text\":\"{1}\",\"attributes\":false,\"state\":\"closed\",\"iconCls\":\"ico_tree_folder_close\",\"children\":[{{\"text\":\"正在加载......\"}}] }}"; //\"iconCls\":\"ico_tree_folder_close\"
string temp = "";
string txt = "";
if (dt.Rows.Count > 0)
{
foreach (DataRow i in dt.Rows)
{
temp = string.Format(node, i["部门编号"].ToString(), i["部门"].ToString());
txt += temp + ",";
}
txt = "[" + txt.Substring(0, txt.Length - 1) + "]";
}
else
{
txt = "[]";
}
context.Response.Write(txt);
}
/// <summary>
/// 取得人员积分明细
/// </summary>
/// <param name="context"></param>
public void JF_getMX(HttpContext context)
{
string name = context.Request["name"];
int row = int.Parse(context.Request["rows"]);
int page = int.Parse(context.Request["page"]);
string SQL = @"SELECT [ID]
,[姓名]
,convert(varchar(20),[日期],120) as 日期
,[积分]
,[说明]
,[操作人]
FROM [CNGTZ].[dbo].[TZ_人员_积分]
where 姓名=@姓名 ORDER BY 日期 DESC ";
DAL dal = new DAL();
SqlParameter par1 = new SqlParameter("姓名", name);
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]";
DataTable dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1 });
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
//dt = BLL.tools.GetPagedTable(dt, page, row);
dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
private void button5_Click(object sender, EventArgs e)
{
DateTime dt = DateTime.Parse("2012-2-21");
int zd = 3;//麻柳沱
int n = 12;//枪数目
List<ZDGAS.gunSum> gunS = new List<ZDGAS.gunSum>();
List<ZDGAS.gunGas> gunG = new List<ZDGAS.gunGas>();
DAL dal = new DAL();
for (int i = 0; i < n; i++)
{
ZDGAS.gunSum gs = new ZDGAS.gunSum();
ZDGAS.gunGas gg = new ZDGAS.gunGas();
string SQL1 = ZDGAS.getBZstarttime(dt, i + 1, zd, false, true);
string SQL2 = ZDGAS.getBZendtime(dt, i + 1, zd, false, true);
DataTable dt1 = dal.RunSqlDataTable(SQL1);
DataTable dt2 = dal.RunSqlDataTable(SQL2);
gs.枪号 = i + 1; gg.枪号 = i + 1;
gg.站点 = zd;
if (dt1.Rows.Count <= 0)
{
gs.错误信息 = "未接班";
gg.接班时间 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
gg.接班时间 = dt1.Rows[0]["time"].ToString();
gs.停止起 = Convert.ToDouble(dt1.Rows[0]["stopsumgas"].ToString());
gs.接班总气量 = Convert.ToDouble(dt1.Rows[0]["SuMGas"].ToString());
gs.接班总金额 = Convert.ToDouble(dt1.Rows[0]["SuMMOney"].ToString());
}
if (dt2.Rows.Count <= 0)
{
gs.错误信息 = "未交班";
gg.交班时间 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
gg.交班时间 = dt2.Rows[0]["time"].ToString();
gs.停止止 = Convert.ToDouble(dt1.Rows[0]["stopsumgas"].ToString());
gs.交班总气量 = Convert.ToDouble(dt2.Rows[0]["SuMGas"].ToString());
gs.交班总金额 = Convert.ToDouble(dt2.Rows[0]["SuMMOney"].ToString());
}
gg.FF_计算数据();
gs.系统班累气量 = Math.Round(gg.记帐刷卡气量 + gg.现金消费气量 + gg.预购刷卡气量, 2);
gs.系统班累金额 = Math.Round(gg.记帐刷卡金额 + gg.现金消费金额 + gg.预购刷卡金额, 2);
gs.FF_计算数值();
if (gs.错误信息 != "")
{
switch (gs.错误信息)
{
case "未交班": gg.交班时间 = "未交班"; break;
case "未接班": gg.接班时间 = "未接班"; break;
default:
break;
}
}
gunG.Add(gg);
gunS.Add(gs);
}
//string dataFh = "{\"top\":头对象,\"sum\":累计对象,\"gas\":销售对象,\"gasjz\":记帐对象}";
string fotS1 = "[{\"交班时间\":\"合计：\",\"现金消费气量\":{0},\"现金消费金额\":{1},\"记帐刷卡气量\":{2},\"记帐刷卡金额\":{3},\"预购刷卡气量\":{4},\"预购刷卡金额\":{5}}]";
//string fotS1 = "{{0}-{1}-{2}-{3}-{4}-{5}}";
fotS1 = fotS1.Replace("{0}", "11111");
fotS1 = fotS1.Replace("{1}", "22222");
fotS1 = fotS1.Replace("{2}", "3333");
fotS1 = fotS1.Replace("{3}", "4444");
fotS1 = fotS1.Replace("{4}", "5555");
fotS1 = fotS1.Replace("{5}", "6666");
string text = "{\"total\":12,\"rows\":行数据对象,\"footer\":合计对象}";
text = text.Replace("行数据对象", Json.Json_转换到json(gunG));
text = text.Replace("合计对象", fotS1);
string Txt = text + "\r\n\r\n";
Txt += "{\"total\":12,\"rows\":" + Json.Json_转换到json(gunS) + "}";
textBox1.Text = Txt;
}
/// <summary>
/// 获取班组人员 和 IC 数据
/// </summary>
/// <param name="context"></param>
public void IcGetBzUser(HttpContext context)
{
string zdcode = context.Request["zdcode"];
string bzcode = context.Request["bzcode"];
string bz = "";
switch (bzcode)
{
case "010501": bz = "1,2"; break;
case "010502": bz = "3,4"; break;
case "010503": bz = "2,3"; break;
default:
bz = bzcode.Substring(bzcode.Length - 1, 1);
break;
}
string SQL1 = "SELECT [卡号],[班组],[人员] FROM [CNGTZ].[dbo].[IC_绑定] where 所属站点='" + zdcode + "' and 班组 in (" + bz + ") ORDER BY 卡号";
string SQL2 = "SELECT[姓名]FROM [CNGTZ].[dbo].[TZ_人员]where 所属部门='" + bzcode + "' order by [姓名]";
string retext = "{\"name\":@name@,\"ic\":@ic@}", names = "", ics = "";
var dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL1);
ics = dt.Rows.Count > 0 ? Json.TableToJson(dt) : "[]";
dt = dal.RunSqlDataTable(SQL2);
names = dt.Rows.Count > 0 ? Json.TableToJson(dt) : "[]";
retext = retext.Replace("@name@", names);
retext = retext.Replace("@ic@", ics);
context.Response.Write(retext);
}
/// <summary>
/// 取得积分人员列表
/// </summary>
public void JF_get(HttpContext context)
{
string zd = context.Request["zd"];
string bz = context.Request["bz"];
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]";
string SQL = @"SELECT a.[姓名],isnull(b.累计,0) as 累计,isnull(c.消费,0)as 消费,isnull(d.当前,0)as 当前 FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN (
SELECT [姓名],sum([积分]) as 当前 FROM [CNGTZ].[dbo].[TZ_人员_积分] group BY 姓名
) d ON a.姓名=d.姓名 LEFT JOIN (
SELECT [姓名],sum([积分]) as 累计 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分>0 group BY 姓名
) b ON a.姓名=b.姓名 LEFT JOIN (
SELECT [姓名],sum([积分]) as 消费 FROM [CNGTZ].[dbo].[TZ_人员_积分] where 积分<0 group BY 姓名
) c ON a.姓名=c.姓名
WHERE a.所属部门 like '@替换@' ORDER BY 当前 desc,a.姓名";
SQL = SQL.Replace("@替换@", bz);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
//dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
/// <summary>
/// 根据部门 获取人员列表
/// </summary>
/// <param name="context"></param>
public void getuserlist(HttpContext context)
{
string code = context.Request["code"];
bool IScheck = bool.Parse(context.Request["IScheck"]);
string SQL = @"SELECT a.[ID],a.[姓名],a.[拼音],b.[部门],a.[所属部门] FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN [CNGTZ].dbo.TZ_部门 b ON a.所属部门=b.部门编号 where a.[所属部门]='" + code + "' order by a.[姓名]";
if (IScheck)
{
SQL = @"SELECT a.[ID],a.[姓名],a.[拼音],b.[部门],a.[所属部门] FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN [CNGTZ].dbo.TZ_部门 b ON a.所属部门=b.部门编号 where a.[所属部门] like '" + code + "%' order by a.[姓名]";
}
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@}";
string rowdx = "[]";
if (dt.Rows.Count > 0)
{
rowdx = Json.TableToJson(dt);
}
rowtext = rowtext.Replace("@行数据对象@", rowdx);
Sys.DebugMes(rowtext);
context.Response.Write(rowtext);
}
/// <summary>
/// 获取 所属班组的人员name
/// </summary>
/// <param name="context"></param>
public void IcGetBzcode(HttpContext context)
{
string code = context.Request["code"];
string SQL = "SELECT[姓名] FROM [CNGTZ].[dbo].[TZ_人员]where 所属部门='" + code + "' order by [姓名]";
var dal = new DAL();
var dt = dal.RunSqlDataTable(SQL);
context.Response.Write(Json.TableToJson(dt));
}
//通过部门分类 搜索人员信息
public void ListScanUserInfo(HttpContext context)
{
string bm = context.Request["bm"];
int row = int.Parse(context.Request["rows"]);
int page = int.Parse(context.Request["page"]);
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]";
string SQL = @"SELECT a.ID,a.姓名,e.部门
,isnull(b.性别,'') as 性别
,isnull(convert (varchar(10), b.出生年月,120),'') as 出生年月
,isnull(datediff(year,b.出生年月,getdate()),'') as 年龄
,isnull(b.婚姻,'') as 婚姻
,isnull(b.学历,'') as 学历
,isnull(b.政治面貌,'') as 政治面貌
,isnull(b.身份证号,'') as 身份证号
,isnull(c.手机,'') as 手机
,isnull(c.联系电话,'') as 联系电话
,isnull(c.家庭住址,'') as 家庭住址
,isnull(c.银行卡号,'') as 银行卡号
,isnull(c.电子邮件,'') as 电子邮件
,isnull(d.毕业院校,'') as 毕业院校
,isnull(convert (varchar(10), d.毕业时间,120),'') as 毕业时间
,isnull(d.专业,'') as 所学专业
,isnull(convert (varchar(10), d.参加工作日期,120),'') as 参加工作日期
FROM [CNGTZ].[dbo].[TZ_人员] a
LEFT JOIN [CNGTZ].[dbo].TZ_人员_基本信息 b ON a.ID=b.ID
LEFT JOIN [CNGTZ].[dbo].TZ_人员_档案1 c ON a.ID = c.ID
LEFT JOIN [CNGTZ].[dbo].TZ_人员_档案2 d ON a.ID = d.ID
LEFT JOIN [CNGTZ].[dbo].TZ_部门 e ON a.所属部门 = e.部门编号
WHERE a.所属部门 LIKE '@bm@%'
ORDER BY 姓名";
SQL = SQL.Replace("@bm@", bm);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
private string Chart_站点年度生产(string year ,string zd)
{
string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }";
string SQL = @"SELECT sum(生产气量) as 生产气量,
'输差率'=
CASE sum([购进数]+[修正值]) when 0 then 0
else round((sum([购进数])+sum([修正值])-sum([生产气量])-sum([库存数]))/sum([购进数]+[修正值])*100,2) end FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace(":true", ":false");
return retext;
}
double[] sc1 = new double[12];
double[] sc2 = new double[12];
for (int i = 0; i < dt.Rows.Count; i++)
{
sc1[i] = double.Parse(dt.Rows[i]["生产气量"].ToString());
sc2[i] = double.Parse(dt.Rows[i]["输差率"].ToString());
}
SQL = "SELECT sum(生产气量) as 生产气量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
string yearSum = dal.RunSqlGetID(SQL);
string sc1Json = Json.Json_转换到json<double[]>(sc1);
string sc2Json = Json.Json_转换到json<double[]>(sc2);
retext = retext.Replace("@替换对象1@", sc1Json);
retext = retext.Replace("@替换对象2@", sc2Json);
retext = retext.Replace("@替换对象3@", yearSum);
return retext;
}
//返回站点列表
public void KQ_ListZd(HttpContext context)
{
string SQL = "SELECT [id],[站点名称] as text FROM [CNGTZ].[dbo].[TZ_站点] ORDER BY id";
var dal = new DAL();
var dt = dal.RunSqlDataTable(SQL);
Sys.DebugMes(Json.TableToJson(dt));
context.Response.Write(Json.TableToJson(dt));
}
//通过 部门 或 分类 搜索人员资质
public void ListScan(HttpContext context)
{
string bm = context.Request["bm"];
string zztype = context.Request["zztype"];
int row = int.Parse(context.Request["rows"]);
int page = int.Parse(context.Request["page"]);
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]";
string SQL = @"SELECT a.人员ID
,b.姓名
,a.[自分类]
,a.[作业类别]
,a.[作业代号]
,a.[资格类型]
,a.[项目名称]
,a.[准操项目]
,a.[工种]
,a.[证书编号]
,convert(varchar(10), a.[发证时间],120) as 发证时间
,convert(varchar(10), isnull(a.[有效时间],'2099-12-31'),120) as 有效时间
,a.[发证单位]
,a.[更新时间]
,a.[备注]
FROM [CNGTZ].[dbo].[TZ_人员_资质证书] a
LEFT JOIN [CNGTZ].[dbo].TZ_人员 b ON a.人员ID=b.ID
where a.[人员ID] IN (SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 所属部门 LIKE '@bm@%')
AND 自分类='@zztype@' ORDER BY 有效时间 ";
SQL = SQL.Replace("@bm@", bm);
if (zztype == "所有")
{
SQL = SQL.Replace("AND 自分类='@zztype@'", " ");
}
else
{
SQL = SQL.Replace("@zztype@", zztype);
}
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
//取考勤表
public void KQ_get(HttpContext context)
{
string zd = context.Request["zd"];
string bz = context.Request["bz"];
string time1 = context.Request["time1"];
string time2 = context.Request["time2"];
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]";
string SQL = @"SELECT [姓名],[班组],sum([应到]) AS 应到,sum([实到]) AS 实到 FROM [CNGTZ].[dbo].[TZ_人员_考勤表]
WHERE 站点 =@站点@ AND 班组=@班组@ AND 日期 BETWEEN '@起始@' and '@结束@' GROUP BY 班组,姓名 order by 班组,姓名";
SQL = SQL.Replace("@站点@", zd);
SQL = SQL.Replace("@起始@", time1);
SQL = SQL.Replace("@结束@", time2);
if (bz == "0")
{
SQL = SQL.Replace("AND 班组=@班组@", "");
}
else
{
SQL = SQL.Replace("@班组@", bz);
}
Sys.DebugMes(SQL);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
//dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
/// <summary>
/// 返回 车辆信息
/// </summary>
/// <param name="context"></param>
public void GetCarInfo(HttpContext context)
{
string code = context.Request["code"];
string retext = "{\"carinfo\":@车辆对象@,\"qpinfo\":@气瓶对象@}";
string SQL = @"SELECT TOP 1 [ID]
,[车牌号]
,[使用登记证]
,[安装气瓶数量]
,[发证单位]
,convert(varchar(10),[发证时间],120) as 发证时间
,[应用类型]
,[厂牌型号]
,[所属单位]
,[负责人]
,[车主姓名]
,[联系电话]
,[邮编]
,[联系地址]
,[发动机号]
,[车架号]
,[汽车厂家]
,[最后更新]
FROM [CNGQT].[dbo].[DZ_全市车辆]
where 车牌号='' AND 负责人<> '手工录入' ORDER BY 发证时间";
DAL dal = new DAL();
CarInfo car = new CarInfo();
string[] info = dal.RunSqlStrings(SQL);
if (info != null)
{
car.车牌号 = info[1];
car.使用登记证号 = info[2];
car.发证单位 = info[4];
car.发证时间 = info[5];
car.应用类型 = info[6];
car.厂牌型号 = info[7];
car.所属单位 = info[8];
car.负责人 = info[9];
car.车主姓名 = info[10];
car.联系电话 = info[11];
car.邮编 = info[12];
car.联系地址 = info[13];
car.发动机号 = info[14];
car.车架号 = info[15];
car.汽车厂家 = info[16];
}
SQL = @"SELECT [所属车辆]
,isnull([气瓶使用编号],'') as 气瓶使用编号
,isnull([生产厂家],'') as 生产厂家
,isnull([气瓶出厂编号],'') as 气瓶出厂编号
,isnull([气瓶类型],'') as 气瓶类型
,isnull([生产年月],'') as 生产年月
,isnull([型号],'') as 型号
,isnull([使用状态],'') as 使用状态
,isnull(convert(varchar(10),[下次检验日期],120),'') as 下次检验日期
,isnull(convert(varchar(10),[最后检验日期],120),'') as 最后检验日期
,isnull(convert(varchar(10),[开始投用日期],120),'') as 开始投用日期
,isnull(convert(varchar(10),[安装日期],120),'') as 安装日期
,isnull([使用年限],0) as 使用年限
,isnull([可充装次数],0) as 可充装次数
,isnull([已充装次数],0) as 气瓶使用编号
,isnull([检验单位],'') as 检验单位
,isnull([检验状态],'') as 检验状态
,isnull([安装单位],'') as 安装单位
,isnull([安装许可],'') as 安装许可
,isnull([安装监检单位],'') as 安装监检单位
,isnull([安装监检许可],'') as 安装监检许可
,isnull([重量],0) as 重量
,isnull([容积],0) as 容积
,isnull([壁厚],0) as 壁厚
,isnull([压力],0) as 压力
,isnull([材质],'') as 材质
,isnull([安装位置],'') as 安装位置
,[最后更新]
FROM [CNGQT].[dbo].[DZ_全市气瓶]
where 所属车辆='@所属车辆@'";
string rows = "{\"total\":28,\"rows\":[]}";
if (car.车牌号 != "")
{
SQL = SQL.Replace("@所属车辆@", car.车牌号);
var dt = dal.RunSqlDataTable(SQL);
if (dt != null)
{
rows = rows.Replace("[]", Json.TableToJson(dt));
}
}
retext = retext.Replace("@车辆对象@", Json.Json_转换到json(car));
retext = retext.Replace("@气瓶对象@", rows);
context.Response.Write(retext);
}
/// <summary>
/// 返回 扫描记录程序
/// </summary>
/// <param name="context"></param>
public void IcCaSan(HttpContext context)
{
string zd = context.Request["zd"];
var date = DateTime.Parse(context.Request["date"]);
bool IsYb = bool.Parse(context.Request["IsYb"]);
int row = int.Parse(context.Request["rows"]);
int page = int.Parse(context.Request["page"]);
int bz = 0;
ZDGAS.BZtime bztime = new ZDGAS.BZtime();
switch (zd)
{
case "4":
bztime = ZDGAS.getBZtime(date, 3);
bz = ZDGAS.getBZno(date);
break;
case "2":
case "3":
case "5":
bztime = ZDGAS.getBZtime(date, 3, IsYb);
bz = ZDGAS.getBZno(date, int.Parse(zd), IsYb);
break;
}
string SQL = @"SELECT convert(varchar(20),[日期],120) as 充装时间
,[枪号]
,[气量]
,[车牌号]
,[更改车牌号]
,[车类型]
,[消费方式]
,[气瓶数]
,[使用登记证]
,[气瓶使用编号]
,[气瓶类型]
,convert(varchar(10),[下次检验日期],120) as 下次检验日期
,[充装情况]
,[班组]
,[员工号]
,[员工姓名]
,[备注]
,[是否完成]
FROM [CNGQT].[dbo].[CNG_充装检查]
where [站点]=@站点 and [班组]=@班组 and 日期 between @起始时间 and @结束时间 order by 日期 ";
var dal = new DAL();
var par1 = new SqlParameter("@站点", int.Parse(zd));
var par2 = new SqlParameter("@班组", bz);
var par3 = new SqlParameter("@起始时间", DateTime.Parse(bztime.start));
var par4 = new SqlParameter("@结束时间", DateTime.Parse(bztime.end));
var dt = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2, par3, par4 });
string retext = "{ \"total\": @数量@, \"rows\": @行对象@ }";
if (dt.Rows.Count > 0)
{
retext = retext.Replace("@数量@", dt.Rows.Count.ToString());
retext = retext.Replace("@行对象@", Json.TableToJson(BLL.tools.GetPagedTable(dt, page, row)));
}
else
{
retext = retext.Replace("@数量@", "0");
retext = retext.Replace("@行对象@", "[]");
}
context.Response.Write(retext);
}
/// <summary>
/// 创建 财务附表表Excel
/// </summary>
private bool CreateCWExcel(string year, string yue, HttpContext context, CountCWGas Cw)
{
string template = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/template/单位统计模板.xls");//模板文件
string newFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.xls");//报表文件文件
string pdfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.pdf");//报表文件文件
string swfFile = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue + "/财务附表.swf");//报表文件文件
string viewPaht = HttpContext.Current.Server.MapPath("~/CNGGAS/BB/" + year + "/" + yue);//报表文件文件目录
if (System.IO.Directory.Exists(viewPaht) == false)
{
FileManager.CreateFolder(viewPaht);
}
bool ok = FileManager.CopyFile(template, newFile);
if (ok == false)
{
return false;
}
MyExcel ME = new MyExcel();
ME.Open(newFile);
#region 第一步 IC卡充值,消费 写入
ME.ActivateSheet("IC卡消费、充值统计");
string txt = string.Format("系统统计时间：{0} 当班(白班)接班 至 {1} 当班(夜班)交班", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 3, 1);//时间范围
txt = string.Format("系统统计时间：{0} 至 {1} ", Cw.Str.ToShortDateString(),
Cw.End.ToShortDateString());
ME.WriteStr(txt, 33, 1);//时间范围
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
string sheetName = "";
int row = 0;//储存底数报表对应行号
ME.ActivateSheet("IC卡消费、充值统计");
if (my.Zd == 2)
{
sheetName = "月度汇总(南高)";
row = 10;
ME.WriteStr(my.充值金额.ToString(), 36, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 36, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 36, 4);//结束时间
}
if (my.Zd == 3)
{
row = 17;
sheetName = "月度汇总(西路)";
ME.WriteStr(my.充值金额.ToString(), 37, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 37, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 37, 4);//结束时间
}
if (my.Zd == 4)
{
row = 5;
sheetName = "月度汇总(麻柳沱)";
ME.WriteStr(my.充值金额.ToString(), 35, 5);//充值金额
ME.WriteStr(Cw.Str.ToShortDateString() + " 0:00:00", 35, 3);//起始时间
ME.WriteStr(Cw.End.ToShortDateString() + " 23:59:59", 35, 4);//结束时间
}
//每枪 预购金额
for (int i = 0; i < my.Guns; i++)
{
ME.WriteStr(my.GunYuGou[i].GunStr, row + i, 3);//起始时间
ME.WriteStr(my.GunYuGou[i].GunEnd, row + i, 4);//结束时间
ME.WriteStr(my.GunYuGou[i].预购金额.ToString(), row + i, 5);//预购金额
}
}
#endregion
#region 第二部 公交车数据写入
foreach (CW_zdGunYuGouMod my in Cw.myGunYuGou)
{
ME.ActivateSheet("公交月度");
if (my.Zd == 2)
{
continue;
}
if (my.Zd == 3 || my.Zd == 4)
{
for (int i = 0; i < my.GongJiao.Length; i++)
{
int col = 0;
if (my.Zd == 3)
{
ME.WriteStr(my.GongJiao[i].日期, 5 + i, 1);//写入日期
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 2);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 3);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 5);//写入手工记账
}
if (my.Zd == 4)
{
ME.WriteStr(my.GongJiao[i].手工记账.ToString(), 5 + i, 6);//写入手工记账
ME.WriteStr(my.GongJiao[i].刷卡记账.ToString(), 5 + i, 7);//写入手工记账
ME.WriteStr(my.GongJiao[i].停业时间.ToString(), 5 + i, 9);//写入手工记账
}
}
}
}
ME.WriteStr(Cw.Days.ToString(), 43, 2);//写入天数
#endregion
#region 第三步 记账单位合计数写入 , 记账单位 分站合计数
string sheet = "";
CW_月度统计Mod d = null;
CW_JiZhangDanWen[] jz = null;
for (int i = 0; i < 4; i++)
{
switch (i)
{
case 0: { sheet = "月度汇总"; d = Cw.月度汇总; jz = Cw.记账单位合计; } break;//总表合计
case 1: { sheet = "月度汇总(南高)"; d = Cw.myData[0]; jz = Cw.myGunYuGou[0].JiZhang; } break;//南高
case 2: { sheet = "月度汇总(西路)"; d = Cw.myData[1]; jz = Cw.myGunYuGou[1].JiZhang; } break;//西路
case 3: { sheet = "月度汇总(麻柳沱)"; d = Cw.myData[2]; jz = Cw.myGunYuGou[2].JiZhang; } break;//麻柳沱
}
ME.ActivateSheet(sheet);
ME.WriteStr(d.预购刷卡气量.ToString(), 4, 4);
ME.WriteStr(d.刷卡金额.ToString(), 4, 5);
ME.WriteStr(d.交款气量.ToString(), 4, 6);
ME.WriteStr(d.交款金额.ToString(), 4, 7);
for (int j = 0; j < jz.Length; j++)
{
ME.WriteStr(jz[j].手工.ToString(), 4 + (j + 1) * 2, 3);
ME.WriteStr(jz[j].刷卡.ToString(), 5 + (j + 1) * 2, 3);
if (jz[j].单位名称 == "公交公司")
{
continue;
}
ME.WriteStr(jz[j].价格.ToString(), 4 + (j + 1) * 2, 5);
}
}
#endregion
#region 第四步 记账单位表统计
string SQL = @"SELECT a.[日期]
,a.[记账单位名称]
,sum(a.[刷卡记账气量]) as 刷卡
,sum(a.[手工记账气量]) as 手工
,b.编号
FROM [CNGQT].[dbo].[SC_记账明细] a LEFT JOIN cngqt.dbo.SC_记账单位名称 b
on b.记账单位名称=a.[记账单位名称]
where 日期 between '@起始日期@' and '@结束日期@'
group by a.日期,a.记账单位名称,b.编号 order by 日期";
SQL = SQL.Replace("@起始日期@", Cw.Str.ToShortDateString());
SQL = SQL.Replace("@结束日期@", Cw.End.ToShortDateString());
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
ME.ActivateSheet("记账单位统计表");
// ME.WriteStr(Cw.Days.ToString(), 33, 5); //插入统计天数
for (int i = 0; i < Cw.Days; i++)//写入日期标题)
{
string dtext = Cw.Str.AddDays(i).ToString("MM月dd日");
ME.WriteStr(dtext, 1, 3 + i);
}
foreach (DataRow row in dt.Rows)
{
DateTime rq = DateTime.Parse(row["日期"].ToString());
int col = (rq - Cw.Str).Days;//取日期间隔 确定列数
int r = int.Parse(row["编号"].ToString());
double sk = double.Parse(row["刷卡"].ToString());
double sg = double.Parse(row["手工"].ToString());
ME.WriteStr(sk.ToString(), r * 2 + 1, 3 + col);
ME.WriteStr(sg.ToString(), r * 2, 3 + col);
}
ME.HideSheet("记账单位统计表");
#endregion
#region 最后一步:生成 excel,pdf,Swf 文档
ME.Save();
ME.SavePdf(pdfFile);//保存为pdf文档
Sys.Pdf2Swf(pdfFile, swfFile);//生成 swf文件
//转换为图片
//myConvert.ConvertPDF2Image(viewPaht + "\\" + "me.pdf", viewPaht + "\\", "me", 1, gg,
// ImageFormat.Jpeg, myConvert.pdfType.Two);
ME.Save();
ME.Close();
#endregion
return true;
}
private string Chart_站点年度电量(string year, string zd)
{
string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象1@,\"sc2\":@替换对象2@,\"sum\":@替换对象3@ }";
string SQL = @"SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量
,'单位电耗'=
CASE sum([生产气量]) when 0 then 0
else round(sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) /sum([生产气量]),3) end
FROM [CNGQT].[dbo].[CW_月度统计]
where 年=@年度@ and 站点=@站点@ GROUP BY 月 order by 月";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace(":true", ":false");
return retext;
}
double[] sc1 = new double[12];//耗电量
double[] sc2 = new double[12];//单位电耗
for (int i = 0; i < dt.Rows.Count; i++)
{
sc1[i] = double.Parse(dt.Rows[i]["耗电量"].ToString());
sc2[i] = double.Parse(dt.Rows[i]["单位电耗"].ToString());
}
SQL = "SELECT sum(([电量总峰]+[电量总平]+[电量总谷])*[电系数]) as 耗电量 FROM [CNGQT].[dbo].[CW_月度统计] where 年=@年度@ and 站点=@站点@";
SQL = SQL.Replace("@年度@", year);
SQL = SQL.Replace("@站点@", zd);
string yearSum = dal.RunSqlGetID(SQL);
string sc1Json = Json.Json_转换到json<double[]>(sc1);
string sc2Json = Json.Json_转换到json<double[]>(sc2);
retext = retext.Replace("@替换对象1@", sc1Json);
retext = retext.Replace("@替换对象2@", sc2Json);
retext = retext.Replace("@替换对象3@", yearSum);
return retext;
}
//取考勤明细
public void KQ_getMX(HttpContext context)
{
string name = context.Request["name"];
string zd = context.Request["zd"];
string bz = context.Request["bz"];
string time1 = context.Request["time1"];
string time2 = context.Request["time2"];
string retext = "{\"total\":@行数@,\"rows\":@行对象@,\"footer\":@脚对象@}", rows = "[]";
string footdx = "[{{ \"下班签退\":\"累计:\",\"应到\":\"{0}\",\"实到\":\"{1}\" }}]";
string SQL = @"SELECT [姓名]
,convert(varchar(10),[日期],120) as 日期
,[班组]
,[白班夜班]
,isnull(convert(varchar(20),[上班签到],120),'') as 上班签到
,isnull(convert(varchar(20),[下班签退],120),'') as 下班签退
,[实到]
,[应到]
FROM [CNGTZ].[dbo].[TZ_人员_考勤表]
where 姓名='@姓名@' AND 站点=@站点@ AND 班组=@班组@ AND 日期 BETWEEN '@起始@' AND '@结束@' ORDER BY 姓名,日期";
if (name == "所有")
{
SQL = SQL.Replace("姓名='@姓名@' AND", "");
}
else
{
SQL = SQL.Replace("@姓名@", name);
}
if (bz == "0")
{
SQL = SQL.Replace("AND 班组=@班组@", "");
}
else
{
SQL = SQL.Replace("@班组@", bz);
}
SQL = SQL.Replace("@站点@", zd);
SQL = SQL.Replace("@起始@", time1);
SQL = SQL.Replace("@结束@", time2);
//用于生成页脚 累计数
string SQL1 = @"SELECT sum(实到) as 实到,sum(应到) as 应到 FROM [CNGTZ].[dbo].[TZ_人员_考勤表]
where 姓名='@姓名@' AND 站点=@站点@ AND 班组=@班组@ AND 日期 BETWEEN '@起始@' AND '@结束@' ";
if (name == "所有")
{
SQL1 = SQL1.Replace("姓名='@姓名@' AND", "");
}
else
{
SQL1 = SQL1.Replace("@姓名@", name);
}
if (bz == "0")
{
SQL1 = SQL1.Replace("AND 班组=@班组@", "");
}
else
{
SQL1 = SQL1.Replace("@班组@", bz);
}
SQL1 = SQL1.Replace("@站点@", zd);
SQL1 = SQL1.Replace("@起始@", time1);
SQL1 = SQL1.Replace("@结束@", time2);
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
//dt = BLL.tools.GetPagedTable(dt, page, row);
rows = Json.TableToJson(dt);
}
dt = dal.RunSqlDataTable(SQL1);
DataRow ro = dt.Rows[0];
footdx = string.Format(footdx, ro["应到"].ToString(), ro["实到"].ToString());
retext = retext.Replace("@行对象@", rows);
retext = retext.Replace("@脚对象@", footdx);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
/// <summary>
/// 返回部门列表
/// </summary>
/// <param name="context"></param>
public void SS_listBuMen(HttpContext context)
{
string SQL = "SELECT [部门] as text,[部门编号] as id FROM [CNGTZ].[dbo].[TZ_部门] where len(部门编号)<7 ORDER BY 部门编号";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
context.Response.Write(Json.TableToJson(dt));
}
//返回班组列表
public void ListBz(HttpContext context)
{
string zdbh = context.Request["zd"];
string SQL = "SELECT [部门] as text,[部门编号] as id FROM [CNGTZ].[dbo].[TZ_部门] where [上级部门] ='" + zdbh + "' ORDER BY 排序";
var dal = new DAL();
var dt = dal.RunSqlDataTable(SQL);
context.Response.Write(Json.TableToJson(dt));
}
/// <summary>
/// 返回自分类列表
/// </summary>
/// <param name="context"></param>
public void SS_listZiFenLei(HttpContext context)
{
bool SS = bool.Parse(context.Request["SS"]);
string SQL = "SELECT [自分类] as text , 自分类 as id FROM [CNGTZ].[dbo].[TZ_人员_资质证书] GROUP BY 自分类 ORDER BY 自分类";
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (SS)
{
DataRow row = dt.NewRow();
row["text"] = "所有"; row["id"] = "所有";
dt.Rows.InsertAt(row, 0);
}
context.Response.Write(Json.TableToJson(dt));
}
//通过姓名 或 拼音 搜索人员资质
public void ListScanName(HttpContext context)
{
string val = context.Request["val"];
int row = int.Parse(context.Request["rows"]);
int page = int.Parse(context.Request["page"]);
string retext = "{\"total\":@行数@,\"rows\":@行对象@}", rows = "[]", nameID = "";
string SQL = @"SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名 LIKE '%{0}%' OR 拼音 LIKE '{0}%'";
SQL = string.Format(SQL, val.Trim());
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
if (dt == null)
{
retext = retext.Replace("@行数@", "0");
retext = retext.Replace("@行对象@", rows);
context.Response.Write(retext);
return;
}
retext = retext.Replace("@行数@", dt.Rows.Count.ToString());
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
nameID += dr["ID"].ToString() + ",";
}
nameID = nameID.Substring(0, nameID.Length - 1);
SQL = @"SELECT a.人员ID
,b.姓名
,a.[自分类]
,a.[作业类别]
,a.[作业代号]
,a.[资格类型]
,a.[项目名称]
,a.[准操项目]
,a.[工种]
,a.[证书编号]
,convert(varchar(10), a.[发证时间],120) as 发证时间
,convert(varchar(10), isnull(a.[有效时间],'2099-12-31'),120) as 有效时间
,a.[发证单位]
,a.[更新时间]
,a.[备注]
FROM [CNGTZ].[dbo].[TZ_人员_资质证书] a
LEFT JOIN [CNGTZ].[dbo].TZ_人员 b ON a.人员ID=b.ID
where a.[人员ID] IN (@人员ID@) ORDER BY 有效时间 ";
SQL = SQL.Replace("@人员ID@", nameID);
DataTable zidt = dal.RunSqlDataTable(SQL);
zidt = BLL.tools.GetPagedTable(zidt, page, row);
rows = Json.TableToJson(zidt);
}
else
{
retext = retext.Replace("@行数@", "0");
}
retext = retext.Replace("@行对象@", rows);
Sys.DebugMes(retext);
context.Response.Write(retext);
}
/// <summary>
/// 搜索 人员列表
/// </summary>
/// <param name="context"></param>
public void user_scan(HttpContext context)
{
string val = context.Request["val"];
string SQL = @"SELECT a.[ID],a.[姓名],a.[拼音],b.[部门],a.[所属部门] FROM [CNGTZ].[dbo].[TZ_人员] a LEFT JOIN [CNGTZ].dbo.TZ_部门 b ON a.所属部门=b.部门编号 where a.姓名 LIKE '%{0}%' OR a.拼音 LIKE '{0}%'";
SQL = string.Format(SQL, val.Trim());
DAL dal = new DAL();
DataTable dt = dal.RunSqlDataTable(SQL);
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@}";
string rowdx = "[]";
if (dt.Rows.Count > 0)
{
rowdx = Json.TableToJson(dt);
}
rowtext = rowtext.Replace("@行数据对象@", rowdx);
Sys.DebugMes(rowtext);
context.Response.Write(rowtext);
}
//返回站点列表
public void ListZd(HttpContext context)
{
string SQL = "SELECT [部门] as text,[部门编号] as id FROM [CNGTZ].[dbo].[TZ_部门] where [特别编号] between 2 and 9 ORDER BY 排序";
var dal = new DAL();
var dt = dal.RunSqlDataTable(SQL);
context.Response.Write(Json.TableToJson(dt));
}
/// <summary>
/// 获取单个人员 的资质信息 简单版
/// </summary>
/// <param name="context"></param>
public void ZZ_read(HttpContext context)
{
string userID = context.Request["ID"];
DAL dal = new DAL();
string SQL = @"SELECT ID,[自分类],[证书编号],[发证单位],
isnull(convert(varchar(10),[发证时间],120),'') as 发证时间 ,
isnull(convert(varchar(10),[有效时间],120),'') as 有效时间
FROM [CNGTZ].[dbo].[TZ_人员_资质证书] where 人员ID=" + userID;
DataTable dt = dal.RunSqlDataTable(SQL);
string rowtext = "{\"total\":" + dt.Rows.Count.ToString() + ",\"rows\":@行数据对象@}";
string rowdx = "[]";
if (dt.Rows.Count > 0)
{
rowdx = Json.TableToJson(dt);
}
rowtext = rowtext.Replace("@行数据对象@", rowdx);
Sys.DebugMes(rowtext);
context.Response.Write(rowtext);
}
