示例#1
0
 /// <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;
 }
示例#2
0
        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
            });
        }
示例#3
0
        /// <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"));
        }
示例#4
0
 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));
 }
示例#5
0
        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));
        }