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));
}
