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