// /// <summary>
// /// 质监数据列表
// /// </summary>
// /// <param name="userInfo"></param>
// /// <param name="ft"></param>
// /// <param name="pageSize"></param>
// /// <param name="pageIndex"></param>
// /// <param name="orderby"></param>
// /// <param name="allRecordCount"></param>
// /// <returns></returns>
// public DataTable RetrieveZjxxList(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
// {
// SqlParameterCollection sp = DB.CreateSqlParameterCollection();
// string sql = @"select * from (
// SELECT
// a.PKID,
// a.gcmc,
// a.zljdbm,
// a.zljdjgmc,a.zjzbm,a.sgzbbm,a.jglx,
// a.gczj,
// convert(char(10),a.sbrq,20) as sbrq,
// (select CodeInfo from tbPrjStructureTypeDic where Code=a.jglx) as StructureType,
// b.PrjName,a.PrjNum,b.PKID as LxPKID
// ,(select count(*) from SaveToStLog where TableName='zj_gcjbxx' and PKID=a.PKID and OperateState=0) as HasSbToSt
// ,a.CreateDate
// FROM zj_gcjbxx a
// left join TBProjectInfo b on a.PrjNum=b.PrjNum
// where a.UpdateFlag='U' ) as aaa WHERE 1=1 and ";
// DALHelper.GetSearchClause(ref sp, ft);
// sql += ft.CommandText;
// return DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pageSize, pageIndex, out allRecordCount);
// }
/// <summary>
/// 立项项目信息列表
/// </summary>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveLxxmList(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @" select * from (
SELECT
a.PKID,
a.PrjNum,a.PrjInnerNum,
a.PrjName,
a.PrjApprovalNum,
a.BuildCorpName,
a.BuildCorpCode,
convert(varchar(10),a.BDate,120) as BDate,
convert(varchar(10),a.EDate,120) as EDate
,convert(varchar(19),a.CreateDate,120) as CreateDate,a.PrjTypeNum,
(select codeinfo from tbPrjTypeDic where code=a.PrjTypeNum ) as PrjType
FROM TBProjectInfo a
where a.UpdateFlag='U' ) as aaa WHERE 1=1 and ";
//string zzlb = ft.GetValue("zzlb");
//if (!string.IsNullOrEmpty(zzlb))
//{
// sql += " zzlb in (" + AntiSqlInjection.ParameterizeInClause(zzlb, "@para", ref sp) + ") and ";
// ft.Remove("zzlb");
//}
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pageSize, pageIndex, out allRecordCount));
}
/// <summary>
/// 获取安监信息数据列表
/// </summary>
/// <param name="type"></param>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveAjxxList(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select * from (
SELECT
a.PKID,
a.aqjdbm,
a.gcmc,
a.Aqjdjgmc,a.zbdw_dwmc,
a.jldw_dwmc,
convert(char(10),a.bjrq,20) as bjrq,
convert(char(10),a.gcgk_kgrq,20) as gcgk_kgrq,
a.PrjNum,b.PrjName,b.PKID as LxPKID ,c.qyID
,(select count(*) from SaveToStLog where TableName='aj_gcjbxx' and PKID=a.PKID and OperateState=0) as HasSbToSt
,a.CreateDate
FROM aj_gcjbxx a
left join TBProjectInfo b on a.PrjNum=b.PrjNum and b.UpdateFlag='U'
left join Uepp_Qyjbxx c on c.zzjgdm=a.zbdw_dwdm
where a.UpdateFlag='U' and a.JGID=@JGID
) as aaa WHERE 1=1 and ";
sp.Add("@JGID", userInfo.qyID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pageSize, pageIndex, 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 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="aqjdbm"></param>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveAjxx_RyxxList(string aqjdbm, AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"SELECT
a.PKID, /*业务编码 guid值*/
a.PrjNum, /*项目编号*/
a.BuilderLicenceNum, /*施工许可证编号 按住建部编码规则统一编号*/
a.CorpName, /*所属单位名称*/
a.CorpCode, /*所属单位组织机构代码*/
a.SafetyCerID, /*安全生产许可证编号*/
a.UserName, /*人员姓名*/
a.IDCardTypeNum, /*证件类型 见代码表*/
b.CodeInfo as IDCardType,
a.IDCard, /*人员证件号码*/
a.UserPhone, /*人员电话*/
a.CertID, /*安全生产考核合格证书编号*/
(CASE UserType WHEN 1 THEN '主要负责人' WHEN 2 THEN '主要负责人' WHEN '3' THEN '安全员' END ) as UserType
FROM TBProjectBuilderUserInfo as a
LEFT JOIN tbIDCardTypeDic AS b ON a.IDCardTypeNum = b.Code
LEFT JOIN TBBuilderLicenceManage AS c ON a.BuilderLicenceNum = c.BuilderLicenceNum
WHERE a.UpdateFlag='U' and a.aqjdbm = @aqjdbm and ";
sp.Add("@aqjdbm", aqjdbm);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pageSize, pageIndex, out allRecordCount));
}
/// <summary>
/// 行政处罚列表
/// </summary>
/// <param name="type"></param>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveXzcf(string type, AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select * from xzcf where updateFlag='U' and ";
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pageSize, pageIndex, out allRecordCount));
}
public DataTable Retrieve_g_user(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = "select * from g_user where USERSTATE=@UserState and ";
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
sp.Add("@UserState", "正常用户");
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pageSize, pageIndex, out allRecordCount));
}
public DataTable Retrieve_RoleRight_List(string userID, AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
// string sql = @"select a.*,b.ModuleName,c.OperateName from g_RoleRight a
//inner join g_Module b on a.ModuleCode=b.ModuleCode
//inner join g_Operate c on a.OperateCode=c.OperateCode and a.ModuleCode=c.ModuleCode
//where RoleID=@RoleID and ";
string sql = "select *,(select count(*) from g_UserRole where UserID=@userID and RoleID=a.RoleID) as HasRole from g_Role a where 1=1 and ";
sp.Add("@UserID", userID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pageSize, pageIndex, 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 RetrieveYzDxjb_List(AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
if (string.IsNullOrEmpty(orderby.Trim()))
{
orderby = "DxjbId desc";
}
;
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @" select * from Szgkjc_Dxjb a where a.DataState<>-1 and ";
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pagesize, page, out allRecordCount));
}
/// <summary>
/// 获取质量监督编码
/// </summary>
/// <param name="zljdbm"></param>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveZjxx_RyxxList(string zljdbm, AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"SELECT
a.PKID, a.zljdbm, a.sbdqbm, dwlx, xh, dwmc, dwdm, xmfzrxm, xmfzrdm, xmfzr_lxdh, jsfzr, jsfzr_lxdh, zly, zly_lxdh, qyy, qyy_lxdh
FROM zj_gcjbxx_zrdw as a
left join zj_gcjbxx b on a.zljdbm=b.zljdbm
WHERE a.zljdbm=@zljdbm and ";
sp.Add("@zljdbm", zljdbm);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", orderby, pageSize, pageIndex, out allRecordCount));
}
public DataTable RetrieveZyry(string qyID, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
// string sql = @" select rownum,a.qymc,b.*,c.xm,c.zjhm,d.ryzslx,d.zsbh,to_char(d.zsyxqrq,'yyyy-MM-dd') zsyxqrq,to_char(d.zsyxzrq,'yyyy-MM-dd') zsyxzrq
//from uepp_qyjbxx a inner join UEPP_QyRy b on a.qyid=b.qyid inner join uepp_ryjbxx c on b.ryid=c.ryid
//left join uepp_ryzs d on b.ryid=d.ryid and b.ryzyzglxID=d.ryzyzglxID
//where a.rowid=:pRowID";
/*
* string sql = @"select qymc,qyid,ryid,xm,zjhm,lxdh,ryzyzglxid,ryzyzglx,zsjlId,ryzslxid,ryzslx,zsbh,zsyxqrq,zsyxzrq,zsmx from(
* select distinct a.qymc,b.qyid,b.ryid,c.xm,c.zjhm,ISNULL(c.lxdh,c.yddh) lxdh,b.ryzyzglxid,b.ryzyzglx,d.zsjlId,d.ryzslxid,d.ryzslx,d.zsbh,
* CONVERT(varchar(10),d.zsyxqrq,120) zsyxqrq,CONVERT(varchar(10),d.zsyxzrq,120) zsyxzrq,'查看明细' zsmx
* from uepp_qyjbxx a
* inner join UEPP_QyRy b on a.qyid=b.qyid and b.DataState<>-1
* inner join uepp_ryjbxx c on b.ryid=c.ryid and c.DataState<>-1
* left join uepp_ryzs d on c.ryid=d.ryid and d.DataState<>-1 and b.ryzyzglxID=d.ryzyzglxID where a.qyID=@qyID
* ) ryxx where 1=1 and ";
*/
//and d.zsyxzrq>GETDATE() 20171109 从SQL移除
string sql = @"select
ROW_NUMBER() over(order by ryid,zsbh) as rowno,
qymc,qyid,ryid,xm,zjhm,lxdh,ryzyzglxid,ryzyzglx,zsjlId,ryzslxid,ryzslx,zsbh,zsyxqrq,zsyxzrq,zsmx from(
select distinct *
from
(
select a.qymc,b.qyid,b.ryid,c.xm,c.zjhm,ISNULL(c.lxdh,c.yddh) lxdh,b.ryzyzglxid,b.ryzyzglx,d.zsjlId,d.ryzslxid,d.ryzslx,d.zsbh,
CONVERT(varchar(10),d.zsyxqrq,120) zsyxqrq,CONVERT(varchar(10),d.zsyxzrq,120) zsyxzrq,'查看明细' zsmx
from uepp_qyjbxx a
inner join UEPP_QyRy b on a.qyid=b.qyid and b.DataState<>-1
inner join uepp_ryjbxx c on b.ryid=c.ryid and c.DataState<>-1
left join uepp_ryzs d on c.ryid=d.ryid and d.DataState<>-1 and b.ryzyzglxID=d.ryzyzglxID
where 1=1
and a.qyID=@qyID
) mid
) ryxx where 1=1 and ";
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
sp.Add("@qyID", qyID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText.Trim();
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pagesize, page, out allRecordCount));
}
public DataTable GetXmxx(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select PKID, PrjNum, PrjInnerNum, PrjName, PrjTypeNum, BuildCorpName, BuildCorpCode, ProvinceNum, CityNum, CountyNum, PrjApprovalNum, PrjApprovalLevelNum, BuldPlanNum, ProjectPlanNum, AllInvest, AllArea, PrjSize, PrjPropertyNum, PrjFunctionNum, BDate, EDate, CreateDate, UpdateFlag, sbdqbm, jd, wd
, (case when isSgbz is null then 0 when isSgbz=1 then 1 else -1 end) as isSgbz
,(case when isSgbz is null then '未标注' when isSgbz=1 then '已标注' else '未设置经纬度' end) as isSgbzState
,(select ContractorCorpName+';' from TBContractRecordManage where ContractTypeNum='301' and PropietorCorpCode=a.BuildCorpCode and PrjNum=a.PrjNum for xml path('')) as SgzcbCorpNames
,(select ContractorCorpName+';' from TBContractRecordManage where ContractTypeNum='100' and PrjNum=a.PrjNum for xml path('')) as EconCorpNames
,(select ContractorCorpName+';' from TBContractRecordManage where ContractTypeNum='200' and PrjNum=a.PrjNum for xml path('')) as DesignCorpNames
,(select ContractorCorpName+';' from TBContractRecordManage where ContractTypeNum='400' and PrjNum=a.PrjNum for xml path('')) as JLCorpNames
from TBProjectInfo a where a.UpdateFlag='U' and ";
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "t", orderby, pageSize, pageIndex, 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));
}
/// <summary>
/// 获取质监信息数据列表
/// </summary>
/// <param name="type"></param>
/// <param name="userInfo"></param>
/// <param name="ft"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="orderby"></param>
/// <param name="allRecordCount"></param>
/// <returns></returns>
public DataTable RetrieveZjxxList(AppUser userInfo, FilterTranslator ft, int pageSize, int pageIndex, string orderby, out int allRecordCount)
{
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @"select * from (
SELECT
a.PKID, zljdbm, gcmc, a.PrjNum, sgzbbm, zljdjgmc, zjzbm, gczj, jzmj, dlcd, jglx, cc, jzgm, xxjd, bz, a.CreateDate, a.UpdateFlag, a.sbdqbm,
convert(char(10),a.sbrq,20) as sbrq,
convert(char(10),a.kgrq,20) as kgrq,
convert(char(10),a.jhjgrq,20) as jhjgrq,
(select CodeInfo from tbPrjStructureTypeDic where Code=a.jglx) as StructureType,
b.PrjName,b.PKID as LxPKID
,(select count(*) from SaveToStLog where TableName='aj_gcjbxx' and PKID=a.PKID and OperateState=0) as HasSbToSt
FROM zj_gcjbxx a
left join TBProjectInfo b on a.PrjNum=b.PrjNum and b.UpdateFlag='U'
where a.UpdateFlag='U' and a.JGID=@JGID
) as aaa WHERE 1=1 and ";
sp.Add("@JGID", userInfo.qyID);
DALHelper.GetSearchClause(ref sp, ft);
sql += ft.CommandText;
return(DB.ExeSqlForDataTable(sql, sp, "dt", 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));
}
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 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));
}