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