private void SecondQuery()
{
string sql = "select dmmc1 jxmc,sum(decode(kskm,1,1,0)) km1hg,sum(decode(kskm,2,1,0)) zkhg,sum(decode(kskm,3,1,0)) km3hg " +
" from (select distinct d.lsh, b.dmmc1, d.sfzmhm,d.kskm,d.ksrq from drv_admin.drv_grade_log d left join " +
"drv_admin.drv_code b on d.jxdm = b.dmz and b.dmlb = '42' where d.glbm like '" + WholeWebConfig.Glbm + "%' and d.zjcx <> 'E' and d.ksrq between to_date('" + begin + "'," +
"'yyyy-MM-dd') and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') and d.zt = 1" +
" and not exists(select 1 from drv_admin.drv_preasign m where m.lsh=d.lsh)) g group by dmmc1";
DataTable dttmp = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmpdb");
if (dttmp != null)
{
DataRow drsrc;
DataRow drdest;
for (int i = 0; i < dttmp.Rows.Count; i++)
{
drsrc = dttmp.Rows[i];
for (int j = 0; j < dt.Rows.Count; j++)
{
drdest = dt.Rows[j];
if (drsrc["jxmc"] == null && drdest["jxmc"] != null)
{
continue;
}
else if ((drsrc["jxmc"] == null && drdest["jxmc"] == null) || drsrc["jxmc"].ToString() == drdest["jxmc"].ToString())
{
drdest["km1hg"] = Convert.ToInt32(drdest["km1hg"].ToString()) + Convert.ToInt32(drsrc["km1hg"].ToString());
drdest["zkhg"] = Convert.ToInt32(drdest["zkhg"].ToString()) + Convert.ToInt32(drsrc["zkhg"].ToString());
//drdest["cdhg"] = Convert.ToInt32(drdest["cdhg"].ToString()) + Convert.ToInt32(drsrc["cdhg"].ToString());
drdest["km3hg"] = Convert.ToInt32(drdest["km3hg"].ToString()) + Convert.ToInt32(drsrc["km3hg"].ToString());
break;
}
}
}
}
}
private void BindData(string begin, string end)
{
string sql = "select p.zzzm,count(*) num from drv_admin.person p, drv_admin.drivinglicense"
+ " d where p.sfzmhm=d.sfzmhm and d.ly in ('B') and d.fzjg='" + WholeWebConfig.Fzjg + "'"
+ " and d.cclzrq between to_date('" + begin + "','yyyy-MM-dd')"
+ " and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') group by p.zzzm having count(*)>3";
DataTable dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmp");
if (dt != null)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
private void BindData(string begin, string end)
{
string sql = "select m.* from (select fzjg,km3ksy1 ,count(*) num "
+ " from drv_admin.drv_grade t where km3ksrq between to_date('" + begin + "','yyyy-MM-dd')"
+ " and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') and "
+ " kscx like 'C%' and fzjg='" + WholeWebConfig.Fzjg + "' group by fzjg,km3ksy1 having count(*)>50 ) m order by num desc";
DataTable dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmp");
if (dt != null)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
private void BindData(string begin, string end)
{
string sql = "select fzjg,count(*) num from "
+ " drv_admin.drivinglicense t where cclzrq between to_date('" + begin + " 00:00:00','yyyy-MM-dd') "
+ " and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') and t.ly in ('B') and "
+ " zjcx like 'C%' and glbm like '" + WholeWebConfig.Glbm + "%' and fzjg='" + WholeWebConfig.Fzjg + "' group by fzjg order by fzjg";
DataTable dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmp");
if (dt != null)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
private void BindData(string begin, string end)
{
string sql = "select m.* from ( select to_char(km3ksrq,'yyyy-MM-dd')"
+ " km3ksrq,to_char(km3ksrq,'DY') wk,count(*) num from drv_admin.drv_grade t,drv_admin.person p,"
+ " drv_admin.drivinglicense d where p.sfzmhm=t.sfzmhm and p.sfzmhm=d.sfzmhm and d.cclzrq"
+ " between to_date('" + begin + "','yyyy-MM-dd') and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss')"
+ " and kscx like 'C%' and d.ly='B' and d.fzjg='" + WholeWebConfig.Fzjg + "' group by d.fzjg, km3ksrq,to_char(km3ksrq,'DY') "
+ " having count(*)>100 Order By num Desc ) m order by num desc";
DataTable dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmp");
if (dt != null)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
private void BindData(string idCard, string hmhp)
{
string sql = "select distinct c.hphm,c.clpp ,c.jxmc ,d.jlzh ,d.sj ,d.xm ,d.sfzmhm"
+ " ,d.jlcxh,d.zjcx ,to_char(c.cjsj,'yyyy-MM-dd HH24:mi:ss') regdate"
+ " from (select a.cjsj,b.jxmc,b.xh,a.jlcxh,a.hphm,a.clpp from drv_admin.drv_learner_vehicle a"
+ " left join drv_admin.drv_schoolinfo b on a.jxxh=b.xh where a.fzjg='" + WholeWebConfig.Fzjg + "' ) c left join"
+ " drv_admin.drv_instructor d on d.jxxh=c.xh and d.jlcxh=c.jlcxh where d.sfzmhm like '%" + idCard + "%'"
+ " or c.hphm like '%" + hmhp + "%'";
DataTable dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmp");
if (dt != null)
{
this.DataGrid1.DataSource = dt;
this.DataGrid1.DataBind();
}
}
private void FirstQuery()
{
string sql = "select dmmc1 jxmc," +
" '' countDate, 0 onStudy,'' km1hgl,'' zkhgl,'' cdhgl,'' km3hgl,'' zhgl,0 km1c1dk,0 km1nc1dk,0 zkc1dk,0 zknc1dk,0 cdc1dk,0 cdnc1dk,0 km3c1dk,0 km3nc1dk, sum(decode(kskm, 1, 1, 0)) km1," +
"sum(decode(kskm, 1, 1, 0) * decode(zt, 1, 1, 0)) km1hg," +
"sum(decode(kskm, 1, 1, 0) * decode(zt, 2, 1, 0)) km1bhg," +
"sum(decode(kskm, 2, 1, 0) * decode(ckyy, 1, 0, 1)) zk," +
" sum(decode(kskm, 2, 1, 0) * decode(ckyy, 1, 1, 0)) cd," +
"sum(decode(kskm, 3, 1, 0)) km3,sum(decode(zksfhg, 1, 1, 0) * decode(kskm, 2, 1, 0) *decode(ckyy, 1, 0, 1)) zkhg," +
"sum(decode(zksfhg, 1, 0, 1) * decode(kskm, 2, 1, 0) *decode(ckyy, 1, 0, 1)) zkbhg," +
"sum(decode(zksfhg, 1, 1, 0) * decode(kskm, 2, 1, 0) *decode(ckyy, 1, 1, 0)) cdhg," +
"sum(decode(zksfhg, 1, 0, 1) * decode(kskm, 2, 1, 0) *decode(ckyy, 1, 1, 0)) cdbhg," +
"sum(decode(zt, 2, 1, 0) * decode(kskm, 3, 1, 0)) km3bhg," +
"sum(decode(zt, 1, 1, 0) * decode(kskm, 3, 1, 0)) km3hg" +
" from (select distinct b.dmmc1,a.sfzmhm,a.zt,a.kskm,a.kscx,a.ksrq,a.zksfhg,a.ckyy" +
" from drv_admin.drv_preasign a left join drv_admin.drv_code b on a.dlr = b.dmz and b.dmlb = '42'" +
" where a.glbm like '" + WholeWebConfig.Glbm + "%' and a.kscx <> 'E' and a.ksrq between to_date('" + begin + "'," +
"'yyyy-MM-dd') and to_date('" + end + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss')) g " +
"group by g.dmmc1";
this.dt = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmpdb");
}
private void Km1DkQuery()
{
string sql = "select dmmc1 jxmc,0 zkc1dk,0 zknc1dk,0 cdc1dk,0 cdnc1dk,0 km3c1dk,0" +
" km3nc1dk,sum(decode(zjcx,'C1',1,0)) km1c1dk,sum(decode(zjcx,'C1',0,1)) km1nc1dk from " +
" ((select distinct q.lsh,c.dmmc1 " +
" as dmmc1,q.sfzmhm,q.zjcx from " +
"(select distinct a.lsh,b.jxmc,a.sfzmhm,a.zjcx from drv_admin.drv_flow a left join drv_admin.drv_examcard b on a.sfzmhm=b.sfzmhm" +
" where a.glbm like '" + WholeWebConfig.Glbm + "%' and a.zjcx<>'E' and a.ywlx in ('A','B')) q left join " +
"drv_admin.drv_code c on q.jxmc=c.dmz and c.dmlb='42' ) minus ( select distinct d.lsh,b.dmmc1,d.sfzmhm,d.zjcx" +
" from drv_admin.drv_grade_log d left join drv_admin.drv_code b on d.jxdm=b.dmz and b.dmlb='42' where d.glbm like '" + WholeWebConfig.Glbm + "%'" +
" and d.zjcx<>'E' and d.kskm=1 and d.zt=1)) e" +
" where exists ( select 1 from drv_admin.drv_examcard c where c.sfzmhm=e.sfzmhm and c.yxqz>sysdate-1)" +
" group by dmmc1";
DataTable dttmp = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmpdb");
if (dttmp != null)
{
DataRow drsrc;
DataRow drdest;
for (int i = 0; i < dttmp.Rows.Count; i++)
{
drsrc = dttmp.Rows[i];
for (int j = 0; j < dt.Rows.Count; j++)
{
drdest = dt.Rows[j];
if (drsrc["jxmc"] == null && drdest["jxmc"] != null)
{
continue;
}
else if ((drsrc["jxmc"] == null && drdest["jxmc"] == null) || drsrc["jxmc"].ToString() == drdest["jxmc"].ToString())
{
drdest["km1c1dk"] = drsrc["km1c1dk"];
drdest["km1nc1dk"] = drsrc["km1nc1dk"];
break;
}
}
}
}
}
private void ZkDkQuery()
{
string sql = "select dmmc1 jxmc,0 km1c1dk,0 km1nc1dk,0 cdc1dk,0 cdnc1dk,0 km3c1dk," +
"0 km3nc1dk,sum(decode(kscx,'C1',1,0)) zkc1dk,sum(decode(kscx,'C1',0,1)) zknc1dk from ((select" +
" distinct a.lsh,b.dmmc1,a.sfzmhm,a.kscx from drv_admin.drv_preasign a left join " +
"drv_admin.drv_code b on a.dlr=b.dmz and b.dmlb='42' where exists ( select 1 from drv_admin.drv_flow f where f.sfzmhm=a.sfzmhm" +
" and f.ywlx in ('A','B') ) and a.glbm like '" + WholeWebConfig.Glbm + "%' and a.kscx<>'E' and a.kskm=1 and a.zt=1 ) minus (" +
" select distinct a.lsh,b.dmmc1,a.sfzmhm,a.kscx from drv_admin.drv_preasign a left join " +
"drv_admin.drv_code b on a.dlr=b.dmz and b.dmlb='42' where a.glbm like '" + WholeWebConfig.Glbm + "%' and a.kscx<>'E' and a.kskm=2 and ((a.ckyy<>1" +
" and a.zksfhg=1) or a.zt=1) )) " +
" e where exists (select 1 from drv_admin.drv_examcard c where c.sfzmhm=e.sfzmhm and c.yxqz>sysdate-1)" +
" group by dmmc1";
DataTable dttmp = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmpdb");
if (dttmp != null)
{
DataRow drsrc;
DataRow drdest;
for (int i = 0; i < dttmp.Rows.Count; i++)
{
drsrc = dttmp.Rows[i];
for (int j = 0; j < dt.Rows.Count; j++)
{
drdest = dt.Rows[j];
if (drsrc["jxmc"] == null && drdest["jxmc"] != null)
{
continue;
}
else if ((drsrc["jxmc"] == null && drdest["jxmc"] == null) || drsrc["jxmc"].ToString() == drdest["jxmc"].ToString())
{
drdest["zkc1dk"] = drsrc["zkc1dk"];
drdest["zknc1dk"] = drsrc["zknc1dk"];
break;
}
}
}
}
}
private void OnStudyQuery()
{
String sql = "select dmmc1 jxmc,count(1) onStudy from(select distinct q.lsh," +
" c.dmmc1 as dmmc1, q.sfzmhm, q.zjcx from ( select distinct a.lsh, b.jxmc, a.sfzmhm, a.zjcx" +
" from drv_admin.drv_flow a " +
" left join drv_admin.drv_examcard b on a.lsh = b.lsh" +
" where a.glbm like '" + WholeWebConfig.Glbm + "%'" +
" and a.zjcx <> 'E' and a.KSSJ between to_date('" + begin.Substring(0, 4) + "-1-1','yyyy-MM-dd') and sysdate" +
" and a.ywlx in ('A', 'B') ) q" +
" left join drv_admin.drv_code c on q.jxmc = c.dmz" +
" and c.dmlb = '42') m group by dmmc1";
DataTable dttmp = WholeWebConfig.GetDrvIDataAccess().SelectDataTable(sql, "tmpdb");
if (dttmp != null)
{
DataRow drsrc;
DataRow drdest;
for (int i = 0; i < dttmp.Rows.Count; i++)
{
drsrc = dttmp.Rows[i];
for (int j = 0; j < dt.Rows.Count; j++)
{
drdest = dt.Rows[j];
if (drsrc["jxmc"] == null && drdest["jxmc"] != null)
{
continue;
}
else if ((drsrc["jxmc"] == null && drdest["jxmc"] == null) || drsrc["jxmc"].ToString() == drdest["jxmc"].ToString())
{
drdest["onStudy"] = drsrc["onStudy"];
break;
}
}
}
}
}