/// <summary>
/// 获取工作指示信息
/// </summary>
/// <param name="workUser"></param>
/// <param name="ft"></param>
/// <param name="pagesize"></param>
/// <param name="page"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveGzzs_List(AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
string Zssj = ft.GetValue("Zssj");
ft.Remove("Zssj");
ft.Translate();
if (string.IsNullOrEmpty(orderby.Trim()))
{
orderby = "GzzsId desc";
}
;
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
//string sql = "select * from Szgkjc_Gzzs a where UserId=@UserId and ";
string sql = @"select *,stuff((select ','+ZshfrName+'【'+(case when Phone is null then '' else phone end)+'】' from Szgkjc_Gzhf a inner join dbo.Gwtz_Sjml b on a.ZshfrId=b.SjmlID
where a.GzzsId=c.GzzsId for xml path('')),1,1,'') zdhfr
from Szgkjc_Gzzs c where UserId=@UserId and ";
if (!string.IsNullOrEmpty(Zssj))
{
sql += " CONVERT(VARCHAR(50),Zssj,112)=@Zssj and";
sp.Add("@Zssj", DateTime.Parse(Zssj).ToString("yyyyMMdd"));
}
sp.Add("@UserId", workUser.UserID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pagesize, page, out allRecordCount));
}
/// <summary>
/// 指示回复列表
/// </summary>
/// <param name="workUser"></param>
/// <param name="ft"></param>
/// <param name="pagesize"></param>
/// <param name="page"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveZshf_all_List(AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
string DataState = ft.GetValue("DataState");
string Zssj = ft.GetValue("Zssj");
string Zshfsj = ft.GetValue("Zshfsj");
ft.Remove("DataState");
ft.Remove("Zssj");
ft.Remove("Zshfsj");
ft.Translate();
if (string.IsNullOrEmpty(orderby.Trim()))
{
orderby = "ZshfId desc";
}
;
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select * from (select a.*,b.Gzzszt,b.GzzsNr,b.ZsrName,b.ZsrPhone,b.ZsrEmail,b.Zssj from Szgkjc_Gzhf a
inner join Szgkjc_Gzzs b on a.GzzsId=b.GzzsId where a.UserId=@UserId) aa where 1=1 and ";
sp.Add("@UserId", workUser.UserID);
if (!string.IsNullOrEmpty(DataState))
{
sql += " DataState=@DataState and";
sp.Add("@DataState", DataState);
}
if (!string.IsNullOrEmpty(Zssj))
{
sql += " CONVERT(VARCHAR(50),Zssj,112)=@Zssj and";
sp.Add("@Zssj", DateTime.Parse(Zssj).ToString("yyyyMMdd"));
}
if (!string.IsNullOrEmpty(Zshfsj))
{
sql += " CONVERT(VARCHAR(50),Zshfsj,112)=@Zshfsj and";
sp.Add("@Zshfsj", DateTime.Parse(Zshfsj).ToString("yyyyMMdd"));
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", 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": //三个月内即将过期和已过期的企业证书
orderby = "zsyxzrq desc";
sql = @"select qyID,qymc,xxdd,lxr,lxdh,fddbr,zsyxqrq,zsyxzrq,zslx,zsbh from (
select distinct a.qyID ,a.qymc,a.xxdd,a.lxr,a.lxdh,a.fddbr,b.zslx,b.zsbh,convert(varchar(10),b.zsyxqrq,120) zsyxqrq,
convert(varchar(10),b.zsyxzrq,120) zsyxzrq from uepp_qyjbxx a inner join uepp_qyzs b on a.qyid=b.qyid
) qy where 1=1 and (convert(varchar(10),zsyxzrq,120) between convert(varchar(10),GETDATE(),120) and CONVERT(char(10), DATEADD(MM,3,GETDATE()), 120)
or convert(varchar(10),zsyxzrq,120)<=convert(varchar(10),GETDATE(),120)) and ";
break;
case "ryzsgq": //三个月内即将过期和已过期的人员证书
orderby = "zsyxzrq desc";
sql = @"select ryID,xm,zjhm,zcjb,ryzyzglx,lxdh,ryzslx,zsbh,zsyxqrq,zsyxzrq from (
select distinct a.ryID, a.xm,a.zjhm,a.zcjb,b.ryzyzglx,a.lxdh,b.ryzslx,b.zsbh,convert(varchar(10),b.zsyxqrq,120) zsyxqrq,
convert(varchar(10),b.zsyxzrq,120) zsyxzrq from uepp_ryjbxx a inner join uepp_ryzs b on a.ryid=b.ryid )
ry where 1=1 and (convert(varchar(10),zsyxzrq,120) between convert(varchar(10),GETDATE(),120) and CONVERT(char(10), DATEADD(MM,3,GETDATE()), 120)
or convert(varchar(10),zsyxzrq,120)<=convert(varchar(10),GETDATE(),120)) 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 ";
orderby = "CreateDate desc";
sql = @"select PKID,PrjName,BuildCorpCode,BuildCorpName,sgdw,xmjl,CountyNum,AllInvest,AllArea,County,CreateDate,BDate,EDate from (
select distinct a.PKID ,a.PrjName,a.BuildCorpCode,a.BuildCorpName,a.AllArea,
stuff((select ',' +ContractorCorpName from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as sgdw
,stuff((select ',' +PrjHead from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as xmjl
,a.AllInvest,a.CountyNum,(select CodeInfo from tbXzqdmDic where Code =a.CountyNum) County,convert(varchar(10),a.CreateDate,120) CreateDate,convert(varchar(10),a.BDate,120) BDate,convert(varchar(10),a.EDate,120) EDate
from TBProjectInfo a
inner join uepp_jsdw b on a.BuildCorpCode=b.jsdwID
where a.AllInvest>10000) xm where 1=1 and ";
break;
case "wzljd": //未质监
orderby = "CreateDate desc";
sql = @"select PKID,PrjName,BuildCorpCode,BuildCorpName,sgdw,xmjl,CountyNum,AllInvest,County,CreateDate,BDate,EDate,PrjNum from (
select distinct a.PKID ,a.PrjName,a.BuildCorpCode,a.BuildCorpName,a.PrjNum,
stuff((select ',' +ContractorCorpName from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as sgdw
,stuff((select ',' +PrjHead from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as xmjl
,a.AllInvest,a.CountyNum,(select CodeInfo from tbXzqdmDic where Code =a.CountyNum) County,convert(varchar(10),a.CreateDate,120) CreateDate,convert(varchar(10),a.BDate,120) BDate,convert(varchar(10),a.EDate,120) EDate
from TBProjectInfo a
inner join uepp_jsdw b on a.BuildCorpCode=b.jsdwID
where a.UpdateFlag='U' and a.PrjNum not in (
select prjNum from zj_gcjbxx where UpdateFlag='U'
)) xm where 1=1 and ";
break;
case "waqjd": //未安监
orderby = "CreateDate desc";
sql = @"select PKID,PrjName,BuildCorpCode,BuildCorpName,sgdw,xmjl,CountyNum,AllInvest,County,CreateDate,BDate,EDate,PrjNum from (
select distinct a.PKID ,a.PrjName,a.BuildCorpCode,a.BuildCorpName,a.PrjNum,
stuff((select ',' +ContractorCorpName from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as sgdw
,stuff((select ',' +PrjHead from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as xmjl
,a.AllInvest,a.CountyNum,(select CodeInfo from tbXzqdmDic where Code =a.CountyNum) County,convert(varchar(10),a.CreateDate,120) CreateDate,convert(varchar(10),a.BDate,120) BDate,convert(varchar(10),a.EDate,120) EDate
from TBProjectInfo a
inner join uepp_jsdw b on a.BuildCorpCode=b.jsdwID
where a.UpdateFlag='U' and a.PrjNum not in (
select xmbm from aj_gcjbxx where UpdateFlag='U'
)) xm where 1=1 and ";
break;
case "jgbaLcyj": //竣工备案流程预警
orderby = "CreateDate desc";
sql = @"select * from (
select b.PKID,a.CreateDate, b.PrjName,b.PrjNum,a.PrjFinishName,a.PrjFinishNum,(select CodeInfo from tbXzqdmDic where Code =b.CountyNum) County,
case when (select count(*) from TBContractRecordManage where PrjNum=a.PrjNum)=0 then '否' else '是' end as sfhtba,
case when (select count(*) from TBProjectCensorInfo where PrjNum=a.PrjNum)=0 then '否' else '是' end as sfsgtsc,
case when (select count(*) from TBBuilderLicenceManage where PrjNum=a.PrjNum)=0 then '否' else '是' end as sfsgxk,
case when (select count(*) from aj_gcjbxx where xmbm=a.PrjNum)=0 then '否' else '是' end as sfaj,
case when (select count(*) from zj_gcjbxx where PrjNum=a.PrjNum)=0 then '否' else '是' end as sfzj
from TBProjectFinishManage a
left join TBProjectInfo b on a.PrjNum=b.PrjNum
where a.UpdateFlag='U' ) xm where ( sfhtba='否' or sfsgtsc='否' or sfsgxk='否' or sfaj='否' or sfzj='否' ) 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 ";
orderby = "BDate desc";
sql = @"select PKID,PrjName,BuildCorpCode,BuildCorpName,sgdw,xmjl,CountyNum,AllInvest,County,CreateDate,BDate,EDate,PrjNum from (
select distinct a.PKID ,a.PrjName,a.BuildCorpCode,a.BuildCorpName,a.PrjNum,
stuff((select ',' +ContractorCorpName from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as sgdw
,stuff((select ',' +PrjHead from TBContractRecordManage where ContractNum in ('301','302','303','304','500','600','700') for xml path('')), 1 , 1 , '') as xmjl
,a.AllInvest,a.CountyNum,(select CodeInfo from tbXzqdmDic where Code =a.CountyNum) County,convert(varchar(10),a.CreateDate,120) CreateDate,convert(varchar(10),a.BDate,120) BDate,convert(varchar(10),a.EDate,120) EDate
from TBProjectInfo a
inner join uepp_jsdw b on a.BuildCorpCode=b.jsdwID
where a.UpdateFlag='U' and a.PrjNum not in (
select PrjNum from TBBuilderLicenceManage where UpdateFlag='U'
)) xm where 1=1 and ";
string kgrq1 = ft.GetValue("BDate");
string kgrq2 = ft.GetValue("EDate");
if (!string.IsNullOrEmpty(kgrq1))
{
sql += " convert(varchar(10),BDate,120) >= '" + kgrq1 + "' and ";
ft.Remove("BDate");
//ft.Translate();
}
if (!string.IsNullOrEmpty(kgrq2))
{
sql += "convert(varchar(10),EDate,120) <= '" + kgrq2 + "' and ";
ft.Remove("EDate");
//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("County");
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 += " County like @pp1 or County like @pp2";
sp.Add("@pp1", "%市区%");
sp.Add("@pp2", "%市辖区%");
}
else
{
sql += " County like @pp3";
sp.Add("@pp3", "%" + strSsdq[i] + "%");
}
}
sql += " ) and ";
}
else
{
if (ssdq == "市区")
{
sql += " (County like @pp1 or County like @pp2) ";
sp.Add("@pp1", "%市区%");
sp.Add("@pp2", "%市辖区%");
}
else
{
sql += " County like @pp3";
sp.Add("@pp3", "%" + ssdq + "%");
}
sql += " and ";
}
ft.Remove("County");
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount));
}
public DataTable RetrieveQyxykp(string qylx, AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = "";
switch (qylx)
{
case "sgdw":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=1 and ";
break;
case "jldw":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
case "kcdw":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
case "sjdw":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
case "zbdljg":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
case "zjzxjg":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
case "jcjg":
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=2 and ";
break;
default:
sql = @"select * from (select a.*,b.qyID from XykpImport a
left join UEPP_Qyjbxx b on a.zzjgdm=b.zzjgdm) as aaa
where 1=1 and ";
break;
}
string zzlb = ft.GetValue("zzlb");
if (!string.IsNullOrEmpty(zzlb))
{
sql += " zzlb in (" + AntiSqlInjection.ParameterizeInClause(zzlb, "@para", ref sp) + ") and ";
ft.Remove("zzlb");
}
string qysd = ft.GetValue("qysd");
if (!string.IsNullOrEmpty(qysd))
{
sql += " qysd in (" + AntiSqlInjection.ParameterizeInClause(qysd, "@pam", ref sp) + ") and ";
ft.Remove("qysd");
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pageSize, pageIndex, out allRecordCount));
}
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));
}
public DataTable RetrieveQyxxList(string qylx, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = "";
//string tagValue = "";
if (qylx == "jsdw")
{
sql = @" select * from (
select jsdwid,zzjgdm,jsdw,ISNULL(fddbr,'无') fddbr,dwflid,dwfl,ISNULL(dwdz,'无') dwdz,ISNULL(lxr,'无') lxr,lxdh,a.datastate,CONVERT(varchar(10),a.xgrqsj,120) xgrqsj,a.tag
from uepp_jsdw a
) as aaa where 1=1 and ";
//tagValue = ft.GetValue("tag");
//ft.Remove("tag");
}
else
{
//if (string.IsNullOrEmpty(orderby.Trim()))
// orderby = " qyid ";
string csywlxID = "";
switch (qylx)
{
case "sgdw":
csywlxID = "1,3,2,13,14";
break;
case "kcdw":
csywlxID = "5";
break;
case "sjdw":
csywlxID = "6,2";
break;
//case "jsdw":
// csywlxID = "11,12";
// break;
case "zjjg":
csywlxID = "7,4,8,9,15,16,17";
break;
case "qtdw":
csywlxID = "''";
break;
}
// sql = @" select * from (
// select a.rowid row_id,a.qyid,a.qymc,a.xxdd,ISNULL(a.lxdh,'0') lxdh,ISNULL(a.lxr,'无') lxr,a.sylxid,a.sylx,ISNULL(a.county,'无') county,a.datastate,b.csywlxid,b.csywlx
//from uepp_qyjbxx a inner join uepp_qycsyw b on a.qyid=b.qyid and b.csywlxid in(" + csywlxID + ")";
sql = @" select *,(province+','+city+','+county) as pcc,(case when SbToStState=0 then '已上报' when SbToStState=-1 then '未上报' when SbToStState=2 then '未更新' else '上报出错' end) as SbState from (
select a.qyid,a.zzjgdm,a.yyzzzch,a.qymc,a.zcdd,a.xxdd,a.lxdh lxdh,a.lxr lxr,a.sylxid,a.sylx,a.province, a.city, a.county ,a.datastate,a.tag,a.CountyID,CONVERT(varchar(10),xgrqsj,120) xgrqsj
,ISNULL((select SbToStState from SaveToStLog2 where TableName='uepp_qyjbxx' and PKID=a.qyid ),-1) as SbToStState
,(select SbToStMsg from SaveToStLog2 where TableName='uepp_qyjbxx' and PKID=a.qyid ) as SbToStMsg
from uepp_qyjbxx a where a.qyid in (select qyid from uepp_qycsyw where csywlxid in(" + csywlxID + ") and DataState != -1";
string _csywlxid = ft.GetValue("csywlxid");
if (!string.IsNullOrEmpty(_csywlxid))
{
sql += " and csywlxid in (" + _csywlxid + ") ";
ft.Remove("csywlxid");
//ft.Translate();
}
sql += ")) qyxx where 1=1 and "; //and xxdd is not null
string zhuxzz = ft.GetValue("zhuxzz");
if (!string.IsNullOrEmpty(zhuxzz))
{
sql += " qyid in (select qyid from uepp_qyzzmx where zzbz=@zzbz and zzlb=@zzlb and DataState<>-1 ) and ";
sp.Add("@zzbz", "主项");
sp.Add("@zzlb", zhuxzz);
ft.Remove("zhuxzz");
//ft.Translate();
}
string zengxzz = ft.GetValue("zengxzz");
if (!string.IsNullOrEmpty(zengxzz))
{
sql += " qyid in (select qyid from uepp_qyzzmx where zzbz=@zzbz1 and zzlb=@zzlb1 and DataState<>-1) and ";
sp.Add("@zzbz1", "增项");
sp.Add("@zzlb1", zengxzz);
ft.Remove("zengxzz");
//ft.Translate();
}
string countyID = ft.GetValue("CountyID");
if (!string.IsNullOrEmpty(countyID))
{
if (string.Equals(countyID, "320213"))
{
sql += " CountyID in (320202, 320203, 320204, 320213) and ";
}
else if (string.Equals(countyID, "省内企业"))
{
sql += " province='江苏省' and city!='无锡市' and";
}
else if (string.Equals(countyID, "省外企业"))
{
sql += " province!='江苏省' and ";
}
else
{
sql += " CountyID =@countyID and ";
sp.Add("@countyID", countyID);
}
ft.Remove("CountyID");
}
string county = ft.GetValue("county");
if (!string.IsNullOrEmpty(county))
{
if (string.Equals(county, "省内企业"))
{
sql += " province='江苏省' and city!='无锡市' and";
}
else if (string.Equals(county, "省外企业"))
{
sql += " province!='江苏省' and ";
}
else
{
sql += " county =@county and ";
sp.Add("@county", county);
}
ft.Remove("county");
}
//if (!string.IsNullOrEmpty(zhuxzz) || !string.IsNullOrEmpty(zengxzz))
// ft.Translate();
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText.Trim();
//if (!string.IsNullOrEmpty(tagValue))
//{
// string[] tagArr = tagValue.Split(',');
// string tagSql = "";
// for (int i = 0; i < tagArr.Length; i++)
// {
// if (i == 0)
// {
// tagSql += " tag = :pam" + (i + 1);
// sp.Add(":pam" + (i + 1), tagArr[i]);
// }
// else
// {
// tagSql += " or tag = :pam" + (i + 1);
// sp.Add(":pam" + (i + 1), tagArr[i]);
// }
// }
// sql += " and (" + tagSql + ") ";
//}
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount));
}
public DataTable RetrieveZyryJbxx(string rylx, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
//if (string.IsNullOrEmpty(orderby.Trim()))
// orderby = " ryID ";
string ryzyzglxID = "";
switch (rylx)
{
case "zczyry":
ryzyzglxID = "1,2,21,41,51,61";
break;
case "aqscglry":
ryzyzglxID = "4,5,6";
break;
case "qyjjry":
ryzyzglxID = "20";
break;
case "zygwglry":
ryzyzglxID = "7,8,9,11,12,14,15,16,17,18,22,42";
break;
default:
ryzyzglxID = "''";
break;
}
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
// string sql = @" select *,(case when SbToStState=1 then '上报失败' when SbToStState=0 then '上报成功' else '未上报' end) SbState from (
//select a.ryid,a.xm,a.zjlx,a.zjhm,a.AJ_EXISTINIDCARDS,a.AJ_IsRefuse,
//case when (a.AJ_EXISTINIDCARDS is null or a.AJ_EXISTINIDCARDS='0') then '作废' when a.AJ_EXISTINIDCARDS='-1' then '冲突' when a.AJ_EXISTINIDCARDS='1' then '未实名认证' when a.AJ_EXISTINIDCARDS='2' then '已实名认证' end sfsmrz,
//c.qyid,c.qymc,a.zczh,ISNULL(a.zcjb,'无') zcjb,ISNULL(ISNULL(a.lxdh,a.yddh),'') lxdh,a.datastate,ISNULL(c.county,'无') county,c.provinceid,c.province,a.xgrqsj
// ,ISNULL((select SbToStState from SaveToStLog2 where TableName='uepp_ryjbxx' and PKID=a.ryID ),-1) as SbToStState
// from uepp_ryjbxx a inner join (select distinct ryid,qyid,ryzyzglxid
// from uepp_qyry
// where ryzyzglxid in ( " + ryzyzglxID + @" )) b on a.ryid=b.ryid inner join uepp_qyjbxx c on b.qyid=c.qyid
// where 1=1 ";
// string sql = @" select *,(case when SbToStState=1 then '上报失败' when SbToStState=0 then '上报成功' else '未上报' end) SbState from (
//select a.ryid,a.xm,a.zjlx,a.zjhm,a.AJ_EXISTINIDCARDS,a.AJ_IsRefuse,
//case when a.AJ_EXISTINIDCARDS='1' then '未实名认证' when a.AJ_EXISTINIDCARDS='2' then '已实名认证' end sfsmrz,
//c.qyid,c.qymc,a.zczh,ISNULL(a.zcjb,'无') zcjb,ISNULL(ISNULL(a.lxdh,a.yddh),'') lxdh,a.datastate,ISNULL(c.county,'无') county,c.provinceid,c.province,a.xgrqsj
// ,ISNULL((select SbToStState from SaveToStLog2 where TableName='uepp_ryjbxx' and PKID=a.ryID ),-1) as SbToStState
// from uepp_ryjbxx a inner join (select distinct ryid,qyid
// from uepp_qyry
// where ryzyzglxid in ( " + ryzyzglxID + @" ) ) b on a.ryid=b.ryid inner join uepp_qyjbxx c on b.qyid=c.qyid
// where 1=1 ) AA ";
string sql = @" select *,(case when SbToStState=1 then '上报失败' when SbToStState=0 then '上报成功' else '未上报' end) SbState from (
select a.ryid,a.xm,a.zjlx,a.zjhm,a.AJ_EXISTINIDCARDS,a.AJ_IsRefuse,
case when a.AJ_EXISTINIDCARDS='1' then '未实名认证' when a.AJ_EXISTINIDCARDS='2' then '已实名认证' end sfsmrz,
c.qyid,c.qymc,a.zczh,ISNULL(a.zcjb,'无') zcjb,ISNULL(ISNULL(a.lxdh,a.yddh),'') lxdh,a.datastate,c.CountyID,ISNULL(c.county,'无') county,ISNULL(c.City,'无') city,c.provinceid,c.province,a.xgrqsj
,ISNULL((select SbToStState from SaveToStLog2 where TableName='uepp_ryjbxx' and PKID=a.ryID ),-1) as SbToStState
from uepp_ryjbxx a left join (select distinct ryid,qyid from uepp_qyry
where ryzyzglxid in ( " + ryzyzglxID + " ) ";
string _ryzyzglxID = ft.GetValue("ryzyzglxID");
if (!string.IsNullOrEmpty(_ryzyzglxID.Trim()))
{
sql += " and ryzyzglxid in (" + _ryzyzglxID + ")";
ft.Remove("ryzyzglxid");
}
sql += " ) b on a.ryid=b.ryid inner join uepp_qyjbxx c on b.qyid=c.qyid where 1=1 ";
string zsbh = ft.GetValue("zsbh");
if (!string.IsNullOrEmpty(zsbh))
{
sql += " and a.ryid in (select ryid from uepp_ryzs where zsbh like'%" + zsbh + "%') ";
ft.Remove("zsbh");
//ft.Translate();
}
sql += ") ryxx where 1=1 and ";
//处理所属地查询，保持跟市场主体一致
string countyID = ft.GetValue("CountyID");
if (!string.IsNullOrEmpty(countyID))
{
if (string.Equals(countyID, "320213"))
{
sql += " CountyID in (320202, 320203, 320204, 320213) and ";
}
else if (string.Equals(countyID, "省内企业"))
{
sql += " province='江苏省' and city!='无锡市' and";
}
else if (string.Equals(countyID, "省外企业"))
{
sql += " province!='江苏省' and ";
}
else
{
sql += " CountyID =@countyID and ";
sp.Add("@countyID", countyID);
}
ft.Remove("CountyID");
}
string county = ft.GetValue("county");
if (!string.IsNullOrEmpty(county))
{
if (string.Equals(county, "省内企业"))
{
sql += " province='江苏省' and city!='无锡市' and";
}
else if (string.Equals(county, "省外企业"))
{
sql += " province!='江苏省' and ";
}
else
{
sql += " county =@county and ";
sp.Add("@county", county);
}
ft.Remove("county");
}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText.Trim();
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount));
}