Пример #1
0
 /// <summary>
 /// 根据指定条件查询黑、白名单数据
 /// </summary>
 /// <param name="query"></param>
 /// <param name="order"></param>
 /// <param name="currentPage"></param>
 /// <param name="pageSize"></param>
 /// <param name="totalCount"></param>
 /// <returns></returns>
 public DataTable GetBlackWhiteData(QueryBlackWhite query, string order, int currentPage, int pageSize, out int totalCount)
 {
     return(Dal.BlackWhiteList.Instance.GetBlackWhiteData(query, order, currentPage, pageSize, out totalCount));
 }
        /// <summary>
        /// 根据指定条件查询黑、白名单数据
        /// </summary>
        /// <param name="query"></param>
        /// <param name="order"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public DataTable GetBlackWhiteData(QueryBlackWhite query, string order, int currentPage, int pageSize, out int totalCount)
        {
            string where = string.Empty;

            if (query.Type != Constant.INT_INVALID_VALUE)
            {
                where += " AND a.[Type] = '" + query.Type + "'";
            }
            if (query.CallType != Constant.INT_INVALID_VALUE)
            {
                if (query.Type == 0)
                {
                    where += " AND (a.CallType = " + query.CallType + ")";
                }
                else
                {
                    where += " AND (a.CallType = " + query.CallType + " or a.CallType = 3)";
                }
            }
            if (query.QueryCallTypes != Constant.STRING_INVALID_VALUE && query.QueryCallTypes != "")
            {
                if (query.Type == 0)
                {
                    where += " AND a.CallType in (" + Dal.Util.SqlFilterByInCondition(query.QueryCallTypes) + ")";
                }
                else
                {
                    where += " AND (a.CallType in (" + Dal.Util.SqlFilterByInCondition(query.QueryCallTypes) + ") or a.CallType = 3)";
                }
            }
            if (query.CDIDS != Constant.INT_INVALID_VALUE)
            {
                where += " AND a.CDIDS = " + query.CDIDS;
            }
            if (query.QueryCDIDs != Constant.STRING_INVALID_VALUE && query.QueryCDIDs != "")
            {
                where += " AND (";
                string[] arrCDID  = Util.SqlFilterByInCondition(query.QueryCDIDs).Split(',');
                string   strcdidq = "";
                for (int i = 0; i < arrCDID.Length; i++)
                {
                    strcdidq += "or (a.CDIDS & " + arrCDID[i] + ") = " + arrCDID[i];
                }
                where += strcdidq.Substring(2) + ")";
            }
            if (query.CreateUserId != Constant.INT_INVALID_VALUE)
            {
                where += " AND a.CreateUserId = '" + query.CreateUserId + "'";
            }
            if (query.PhoneNum != Constant.STRING_INVALID_VALUE && query.PhoneNum != "")
            {
                where += " AND a.PhoneNum = '" + SqlFilter(query.PhoneNum) + "'";
            }
            if (query.QueryCreateStartDate != Constant.DATE_INVALID_VALUE)
            {
                where += " AND a.CreateDate >= '" + query.QueryCreateStartDate + "'";
            }
            if (query.QueryCreateEndDate != Constant.DATE_INVALID_VALUE)
            {
                where += " AND a.CreateDate < '" + query.QueryCreateEndDate.AddDays(1) + "'";
            }
            if (query.EffectiveDate != Constant.DATE_INVALID_VALUE)
            {
                where += " AND a.EffectiveDate >= '" + query.EffectiveDate + "'";
            }
            if (query.ExpiryDate != Constant.DATE_INVALID_VALUE)
            {
                where += " AND a.ExpiryDate < '" + query.ExpiryDate.AddDays(1) + "'";
            }
            if (query.QueryLoginUserId != Constant.INT_INVALID_VALUE)
            {
                //数据权限控制:当前登录人的所属分组和管辖分组
                //                where += @" and a.BGID in(select BGID from UserGroupDataRigth where UserID=" + query.QueryLoginUserId + @"
                //                union all SELECT EmployeeAgent.BGID FROM    EmployeeAgent INNER JOIN dbo.BusinessGroup ON EmployeeAgent.BGID = BusinessGroup.BGID
                //                                    WHERE BusinessGroup.Status=0 AND EmployeeAgent.UserID=" + query.QueryLoginUserId + @")";
                //数据权限控制:创建人+管辖分组
                where += Dal.UserGroupDataRigth.Instance.GetSqlRightstr("a", "BGID", "CreateUserId", query.QueryLoginUserId);
            }
            SqlParameter[] parameters =
            {
                new SqlParameter("@where",         SqlDbType.NVarChar, 4000),
                new SqlParameter("@order",         SqlDbType.NVarChar,  200),
                new SqlParameter("@pagesize",      SqlDbType.Int,         4),
                new SqlParameter("@indexpage",     SqlDbType.Int,         4),
                new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
            };
            parameters[0].Value     = where;
            parameters[1].Value     = order;
            parameters[2].Value     = pageSize;
            parameters[3].Value     = currentPage;
            parameters[4].Direction = ParameterDirection.Output;

            DataSet ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_BlackWhiteList_Select", parameters);

            totalCount = (int)(parameters[4].Value);
            return(ds.Tables[0]);
        }