/// <summary>
/// 解析where并返回翻译对象(请确保UI层查询条件与数据库的模型一致,否则请自行进行对where的条件进行参数转化解析)
/// </summary>
/// <param name="where">查询条件where(与数据库模型一致的查询对象的where)</param>
/// <returns></returns>
public static string GetFilterTanslate(string where)
{
string commandText = "";
if (string.IsNullOrEmpty(@where)) return commandText;
FilterTranslator translate = new FilterTranslator();
translate.Group = Utility.JsonHelper.FromJson<FilterGroup>(@where);
translate.Translate();
commandText = FilterParam.AddParameters(translate.CommandText, translate.Parms);
return commandText;
}
public FunctionResult <DataTable> RetrieveSzgc(string xmlx, AppUser workUser, FilterTranslator ft, int pagesize, int page, string orderby, out int allRecordCount)
{
//if (xmlx == "aqjd")
// orderby = " sortid,row_id desc ";
//else
// orderby = "row_id desc";
switch (xmlx)
{
case "lxxm":
orderby = "lxwh, lxrq desc ";
break;
case "aqjd":
orderby = " aqjddabh ";
break;
case "zljd":
orderby = " zljdslsj desc ";
break;
case "sgxk":
orderby = " sgxkslsj desc ";
break;
case "gcxm":
orderby = " operatedate desc ";
break;
default:
orderby = "row_id desc";
break;
}
DataTable dt = DAL.RetrieveSzgc(xmlx, workUser, ft, pagesize, page, orderby, out allRecordCount);
return(new FunctionResult <DataTable>()
{
Result = dt
});
}
/// <summary>
/// 获取人员专业明细信息
/// </summary>
/// <param name="ryid"></param>
/// <param name="ryzyzglxid"></param>
/// <param name="ryzslxid"></param>
/// <param name="ft"></param>
/// <returns></returns>
public DataTable RetrieveRyzymx(string ryid, string ryzyzglxid, string ryzslxid, FilterTranslator ft)
{
//if (string.IsNullOrEmpty(orderby.Trim()))
// orderby = " zzbz ";
SqlParameterCollection sp = DB.CreateSqlParameterCollection();
string sql = @" select id,ryzyzglx,ryzslx,zzbz,zyzglb,zyzgdj,gzfw from UEPP_Ryzymx where ryid=@ryID and ryzyzglxID=@RyzyzglxID and ryzslxid=@RyzslxID ";
sp.Add("@ryID", ryid);
sp.Add("@RyzyzglxID", ryzyzglxid);
sp.Add("@RyzslxID", ryzslxid);
return(DB.ExeSqlForDataTable(sql, sp, "t"));
}
public virtual List <T> GetPage(string predicate, int page, int page_size, string order, string asc, out int total)
{
//total =GetAllNoCache().Where(p=>p.IsDelete==false).Count();
//predicate= ;
return(SearchSqLFor_Page(FilterTranslator.ToSql(predicate), page, page_size, order, asc, out total));
}
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));
}