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 = string.Format(_sqlPattern, begin, end); DataTable dt = WholeWebConfig.GetDrvIDataAccessDecode().SelectDataTable(sql, "tmp"); if (dt != null) { this.DataGrid1.DataSource = dt; this.DataGrid1.DataBind(); } }
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(); } }
protected void btnSearch_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtCode.Text)) { return; } string sql = string.Format(sqlPattern, txtCode.Text); DataTable dt = WholeWebConfig.GetDrvIDataAccessDecode().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; } } } } }
protected void cbLxzsxzqhValue_SelectedIndexChanged(object sender, EventArgs e) { if (string.IsNullOrEmpty(cbLxzsxzqhValue.SelectedValue)) { txtLxzsxxdz.Text = "广东省"; return; } string sql = string.Format(sqlPattern, cbLxzsxzqhValue.SelectedValue); DataTable dt = WholeWebConfig.GetDrvIDataAccessDecode().SelectDataTable(sql, "tmp"); if (dt == null) { return; } try { string sf = dt.Rows[0]["sf"].ToString(); string cs = dt.Rows[0]["cs"].ToString(); string dmsm1 = dt.Rows[0]["dmsm1"].ToString(); txtLxzsxxdz.Text = string.Format("{0}{1}{2}", sf, cs, dmsm1); } catch (Exception ex) { } }
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; } } } } }