public DataTable RetrieveSzgc(string xmlx, AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount) { SqlParameterCollection sp = DB.CreateSqlParameterCollection(); string sql = ""; switch (xmlx) { case "lxxm": sql = @"select * from ( select a.rowid row_id,a.xmmc,a.jsdw,a.ssdqid, nvl(a.ssdq,'无') ssdq,a.tzlx,a.lxbm, nvl(a.lxwh,'无') lxwh, to_char(a.lxrq,'yyyy-MM-dd') lxrq,b.rowid jsdwrowid from uepp_lxxm a left join uepp_jsdw b on a.jsdwid=b.jsdwid ) lxxm where 1=1 and jsdw is not null and "; break; case "gcxm": sql = @"select rownum,row_id,sgxmtybh,xmmc,dd,nvl(ssdq,'无') ssdq,jsdw,sgdw,cblx,xmjl,qymc,jsdwrowid,qyrowid,ryrowid,operatedate from ( select distinct a.rowid row_id,a.sgxmtybh,a.xmmc,a.dd,a.ssdq,a.jsdw,a.sgdw,a.cblx,a.xmjl,case when a.operatedate is null then to_date('1900-01-01','yyyy-MM-dd') else a.operatedate end operatedate, c.qymc,b.rowid jsdwrowid,c.rowid qyrowid,d.rowid ryrowid from uepp_xmjbxx a left join uepp_jsdw b on a.jsdwid=b.jsdwid left join uepp_qyjbxx c on a.sgdwid=c.qyid left join uepp_ryjbxx d on a.xmjlid=d.ryid) xm where 1=1 and jsdw is not null and "; string bhxm = ft.GetValue("bhxm"); if (!string.IsNullOrEmpty(bhxm)) { if (bhxm.Contains(',')) { sql += "("; string querySQL = ""; if (bhxm.Contains("已安监")) { querySQL += " sgxmtybh in (select distinct sgxmtybh from uepp_aqjdxx ) "; } if (bhxm.Contains("已质监")) { if (!string.IsNullOrEmpty(querySQL.TrimString())) { querySQL += " and "; } querySQL += " sgxmtybh in (select distinct sgxmtybh from uepp_zljdxx ) "; } if (bhxm.Contains("已施工许可")) { if (!string.IsNullOrEmpty(querySQL.TrimString())) { querySQL += " and "; } querySQL += " sgxmtybh in (select distinct a.sgxmtybh from UEPP_SgxkAndBdRelation a inner join uepp_sgxkxx b on a.sgxkid=b.sgxkid ) "; } if (bhxm.Contains("已竣工验收")) { if (!string.IsNullOrEmpty(querySQL.TrimString())) { querySQL += " and "; } querySQL += " sgxmtybh in (select distinct sgxmtybh from uepp_zljdxx where isjg='是' ) "; } sql += querySQL + " ) and "; } else { switch (bhxm) { case "已安监": sql += " sgxmtybh in (select distinct sgxmtybh from uepp_aqjdxx ) "; break; case "已质监": sql += " sgxmtybh in (select distinct sgxmtybh from uepp_zljdxx ) "; break; case "已施工许可": sql += " sgxmtybh in (select distinct a.sgxmtybh from UEPP_SgxkAndBdRelation a inner join uepp_sgxkxx b on a.sgxkid=b.sgxkid ) "; break; case "已竣工验收": sql += " sgxmtybh in (select distinct sgxmtybh from uepp_zljdxx where isjg='是' ) "; break; } sql += " and "; } ft.Remove("bhxm"); //ft.Translate(); } break; case "aqjd": sql = @"select distinct * from( select a.cblx,a.rowid row_id,b.xmmc,b.jsdwid,b.sgdwid,b.sgdw,b.jsdw,a.ssdqid,nvl(a.ssdq,'无') ssdq,b.aqjdid,b.ryid,nvl(b.xmjl,'无') xmjl,b.aqjddabh, to_char(case when b.aqjdslsj is null then to_date('1900-01-01','yyyy-MM-dd') else b.aqjdslsj end ,'yyyy-MM-dd') aqjdslsj,b.datastate aqjdstate, b.aqjdflag ,b.aqjd_status,c.rowid jsdwrowid,d.rowid qyrowid,e.rowid ryrowid, case b.aqjdflag when '4' then '新增待审' when '0' then '在建' when '3' then '竣工' when '2' then '完工未办手续' when '1' then '停工' end sfjg, case b.aqjd_status when '-1' then '退回' when '0' then '未审' when '1' then '信用审核通过' when '2' then '报监待审' when '3' then '报监审核通过' end sfsh from uepp_aqjdxx b left join uepp_xmjbxx a on a.sgxmtybh=b.sgxmtybh left join uepp_jsdw c on a.jsdwid=c.jsdwid left join uepp_qyjbxx d on a.sgdwid=d.qyid left join uepp_ryjbxx e on a.xmjlid=e.ryid ) xmxx where 1=1 and jsdw is not null and "; //string aqbjsj1 = ft.GetValue("aqjdslsj1"); //string aqbjsj2 = ft.GetValue("aqjdslsj2"); //if (!string.IsNullOrEmpty(aqbjsj1)) //{ // sql += " to_date(aqjdslsj,'yyyy-MM-dd') >= to_date(" + aqbjsj1 + ",'yyyy-MM-dd') and "; // ft.Remove("aqjdslsj1"); //} //if (!string.IsNullOrEmpty(aqbjsj2)) //{ // sql += " to_date(aqjdslsj,'yyyy-MM-dd') <= to_date(" + aqbjsj2 + ",'yyyy-MM-dd') and "; // ft.Remove("aqjdslsj2"); //} string cblx = ft.GetValue("cblx"); if (!string.IsNullOrEmpty(cblx)) { if (cblx == "总承包") { sql += " cblx =:cblx "; } else { sql += " cblx <>:cblx "; } sql += " and "; sp.Add(":cblx", "总承包"); ft.Remove("cblx"); } break; case "zljd": sql = @"select distinct * from( select a.rowid row_id,b.xmmc,b.jsdwid,b.sgdwid,b.sgdw,b.jsdw,b.ssdqid,nvl(a.ssdq,'无') ssdq,b.xmjlid,b.xmjl,b.zljdid,b.zljdbh,b.datastate zljdstate, to_char(b.zljdslsj, 'yyyy-MM-dd') zljdslsj,c.rowid jsdwrowid,d.rowid qyrowid,e.rowid ryrowid,b.isjg from uepp_zljdxx b left join uepp_xmjbxx a on a.sgxmtybh=b.sgxmtybh left join uepp_jsdw c on a.jsdwid=c.jsdwid left join uepp_qyjbxx d on a.sgdwid=d.qyid left join uepp_ryjbxx e on a.xmjlid=e.ryid ) xmxx where 1=1 and jsdw is not null and "; string zlbjsj1 = ft.GetValue("zljdslsj1"); string zlbjsj2 = ft.GetValue("zljdslsj2"); if (!string.IsNullOrEmpty(zlbjsj1)) { sql += " to_date(zljdslsj,'yyyy-MM-dd') >= to_date('" + zlbjsj1 + "','yyyy-MM-dd') and "; ft.Remove("zljdslsj1"); //ft.Translate(); } if (!string.IsNullOrEmpty(zlbjsj2)) { sql += " to_date(zljdslsj,'yyyy-MM-dd') <= to_date('" + zlbjsj2 + "','yyyy-MM-dd') and "; ft.Remove("zljdslsj2"); //ft.Translate(); } break; case "sgxk": // sql = @"select distinct * from( //select a.xmid,a.xmmc,a.jsdwid,a.sgdwid,a.sgdw,a.jsdw,a.ssdqid,a.ssdq,c.sgxkid,c.sgxkglbm,c.sgxkzbh,to_char(c.sgxkslsj,'yyyy-mm-dd') sgxkslsj //from UEPP_SgxkAndBdRelation b inner join uepp_sgxkxx c on b.sgxkid=c.sgxkid and b.datastate=0 left join uepp_xmjbxx a on a.sgxmtybh=b.sgxmtybh //) xmxx where 1=1 and "; sql = @"select distinct * from( select a.rowid row_id,c.xmmc,c.sgdw,c.jsdw,a.ssdqid,nvl(a.ssdq,'无') ssdq,c.sgxkid,c.sgxkglbm,c.sgxkzbh, to_char(case when c.sgxkslsj is null then to_date('1900-01-01','yyyy-MM-dd') else c.sgxkslsj end ,'yyyy-MM-dd') sgxkslsj,c.datastate sgxkstate, d.rowid jsdwrowid,e.rowid qyrowid,f.rowid ryrowid from UEPP_SgxkAndBdRelation b inner join uepp_sgxkxx c on b.sgxkid=c.sgxkid and b.datastate=0 left join uepp_xmjbxx a on a.sgxmtybh=b.sgxmtybh left join uepp_jsdw d on a.jsdwid=d.jsdwid left join uepp_qyjbxx e on a.sgdwid =e.qyid left join uepp_ryjbxx f on a.xmjlid=f.ryid ) xmxx where 1=1 and "; string bjsj1 = ft.GetValue("sgxkslsj1"); string bjsj2 = ft.GetValue("sgxkslsj2"); if (!string.IsNullOrEmpty(bjsj1)) { sql += " to_date(sgxkslsj,'yyyy-MM-dd') >= to_date('" + bjsj1 + "','yyyy-MM-dd') and "; ft.Remove("sgxkslsj1"); //ft.Translate(); } if (!string.IsNullOrEmpty(bjsj2)) { sql += " to_date(sgxkslsj,'yyyy-MM-dd') <= to_date('" + bjsj2 + "','yyyy-MM-dd') and "; ft.Remove("sgxkslsj2"); //ft.Translate(); } break; } string ssdq = ft.GetValue("ssdq"); if (!string.IsNullOrEmpty(ssdq)) { if (ssdq.Contains(',')) { string[] strSsdq = ssdq.Split(','); sql += "("; for (int i = 0; i < strSsdq.Length; i++) { if (i != 0) { sql += " or "; } if (strSsdq[i] == "市区") { sql += " ssdq like :pp1" + i.ToString() + " or ssdq like :pp2" + i.ToString() + ""; sp.Add(":pp1" + i.ToString(), "%市区%"); sp.Add(":pp2" + i.ToString(), "%市辖区%"); } else { sql += " ssdq like :pp3" + i.ToString(); sp.Add(":pp3" + i.ToString(), "%" + strSsdq[i] + "%"); } } sql += " ) and "; } else { if (ssdq == "市区") { sql += " (ssdq like :pp1 or ssdq like :pp2) "; sp.Add(":pp1", "%市区%"); sp.Add(":pp2", "%市辖区%"); } else { sql += " ssdq like :pp3"; sp.Add(":pp3", "%" + ssdq + "%"); } sql += " and "; } ft.Remove("ssdq"); //ft.Translate(); } DALHelper.GetSearchClause(ref sp, ft); sql += ft.CommandText; return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount)); }
public DataTable Retrieve(string fromwhere, AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount) { SqlParameterCollection sp = DB.CreateSqlParameterCollection(); string sql = ""; switch (fromwhere) { case "qyzsgq": //三个月内即将过期和已过期的企业证书 sql = @"select rownum,row_id,qymc,xxdd,lxr,lxdh,fddbr,zsyxqrq,zsyxzrq,zslx,zsbh from ( select distinct a.rowid row_id ,a.qymc,a.xxdd,a.lxr,a.lxdh,a.fddbr,b.zslx,b.zsbh,to_char(b.zsyxqrq,'yyyy-mm-dd') zsyxqrq, to_char(b.zsyxzrq,'yyyy-mm-dd') zsyxzrq from uepp_qyjbxx a inner join uepp_qyzs b on a.qyid=b.qyid ) qy where 1=1 and (zsyxzrq between to_char(sysdate,'yyyy-mm-dd') and to_char((select add_months(sysdate,3) from dual),'yyyy-mm-dd' ) or zsyxzrq<=to_char(sysdate,'yyyy-mm-dd')) and "; break; case "ryzsgq": //三个月内即将过期和已过期的人员证书 sql = @"select rownum,row_id,xm,zjhm,zcjb,ryzyzglx,lxdh,ryzslx,zsbh,zsyxqrq,zsyxzrq from ( select distinct a.rowid row_id, a.xm,a.zjhm,a.zcjb,b.ryzyzglx,a.lxdh,b.ryzslx,b.zsbh,to_char(b.zsyxqrq,'yyyy-mm-dd') zsyxqrq, to_char(b.zsyxzrq,'yyyy-mm-dd') zsyxzrq from uepp_ryjbxx a inner join uepp_ryzs b on a.ryid=b.ryid ) ry where 1=1 and (zsyxzrq between to_char(sysdate,'yyyy-mm-dd') and to_char((select add_months(sysdate,3) from dual),'yyyy-mm-dd' ) or zsyxzrq<=to_char(sysdate,'yyyy-mm-dd')) and "; break; case "zjgyxm": //造价过亿项目 sql = @"select rownum,row_id,xmmc,jsdw,sgdw,xmjl,zj,ssdqid,ssdq,jsdwrowid,qyrowid,ryrowid from ( select distinct a.rowid row_id,a.xmmc,a.jsdw,a.sgdw,a.xmjl,a.zj,a.ssdqid,a.ssdq,b.rowid jsdwrowid,c.rowid qyrowid,d.rowid ryrowid from uepp_xmjbxx a inner join uepp_jsdw b on a.jsdwid=b.jsdwid left join uepp_qyjbxx c on a.sgdwid=c.qyid left join uepp_ryjbxx d on a.xmjlid=d.ryid where a.zj>10000) xm where 1=1 and "; break; case "wbsgxkz": //未办施工许可证项目 sql = @"select rownum,row_id,xmmc,ssdq,jsdw,sgdw,xmjl,sgxmtybh,kgrq,jsdwrowid,qyrowid,ryrowid from ( select distinct a.rowid row_id,a.xmmc,a.ssdq,a.jsdw,a.sgdw,a.xmjl, a.sgxmtybh,to_char(b.kgrq,'yyyy-mm-dd') kgrq,e.rowid jsdwrowid,f.rowid qyrowid,g.rowid ryrowid from uepp_xmjbxx a inner join uepp_aqjdxx b on a.sgxmtybh=b.sgxmtybh and to_char(b.kgrq,'yyyy-mm-dd') <=to_char(sysdate,'yyyy-mm-dd') left join uepp_jsdw e on a.jsdwid=e.jsdwid left join uepp_qyjbxx f on a.sgdwid=f.qyid left join uepp_ryjbxx g on a.xmjlid=g.ryid where a.sgxmtybh not in (select distinct d.sgxmtybh from uepp_sgxkxx c inner join UEPP_SgxkAndBdRelation d on c.sgxkid=d.sgxkid ) ) xm where 1=1 and "; string kgrq1 = ft.GetValue("kgrq1"); string kgrq2 = ft.GetValue("kgrq2"); if (!string.IsNullOrEmpty(kgrq1)) { sql += " to_char(kgrq,'yyyy-MM-dd') >= '" + kgrq1 + "' and "; ft.Remove("kgrq1"); //ft.Translate(); } if (!string.IsNullOrEmpty(kgrq2)) { sql += " to_char(kgrq,'yyyy-MM-dd') <= '" + kgrq2 + "' and "; ft.Remove("kgrq2"); //ft.Translate(); } break; case "gcbgxm": sql = @"select rownum, row_id,xmmc,jsdw,sgdw,xmbgmc,xmbgbw,bggsje,jsdwrowid,qyrowid from ( select distinct b.rowid row_id,a.xmmc,a.jsdw,a.sgdw,b.xmbgmc,b.xmbgbw,b.bggsje,c.rowid jsdwrowid, d.rowid qyrowid from uepp_xmjbxx a inner join UEPP_xmBgjl b on a.sgxmtybh =b.sgxmtybh left join uepp_jsdw c on a.jsdwid=c.jsdwid left join uepp_qyjbxx d on a.sgdwid=d.qyid ) xm where 1=1 and "; break; } string ssdq = ft.GetValue("ssdq"); if (!string.IsNullOrEmpty(ssdq)) { if (ssdq.Contains(',')) { string[] strSsdq = ssdq.Split(','); sql += "("; for (int i = 0; i < strSsdq.Length; i++) { if (i != 0) { sql += " or "; } if (strSsdq[i] == "市区") { sql += " ssdq like :pp1 or ssdq like :pp2"; sp.Add(":pp1", "%市区%"); sp.Add(":pp2", "%市辖区%"); } else { sql += " ssdq like :pp3"; sp.Add(":pp3", "%" + strSsdq[i] + "%"); } } sql += " ) and "; } else { if (ssdq == "市区") { sql += " (ssdq like :pp1 or ssdq like :pp2) "; sp.Add(":pp1", "%市区%"); sp.Add(":pp2", "%市辖区%"); } else { sql += " ssdq like :pp3"; sp.Add(":pp3", "%" + ssdq + "%"); } sql += " and "; } ft.Remove("ssdq"); } DALHelper.GetSearchClause(ref sp, ft); sql += ft.CommandText; return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount)); }