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