// 2009-01-15

按需定制設(shè)計可以根據(jù)自己的需求進行定制，成都做網(wǎng)站、成都網(wǎng)站設(shè)計構(gòu)思過程中功能建設(shè)理應(yīng)排到主要部位公司成都做網(wǎng)站、成都網(wǎng)站設(shè)計的運用實際效果公司網(wǎng)站制作網(wǎng)站建立與制做的實際意義

///

///

///

///

///

///

///

private DataSet GetSuperSeniorDownlineList(string companyCode, string userCode, LeeSoft.Model.Extend.PageInfo page, Dictionary keywords)

{

// 要查詢的級別

string CurrentLevelString = ((int)CompanyUserType.Senior).ToString();

StringBuilder strSql = new StringBuilder();

strSql.Append("select * from (");

strSql.Append("select UserID,UserCompanyCode,UserLoginId,UserIsDefault,UserIsMaster,UserLv,UserLoginPass,UserName,UserRemark1,UserRemark2,UserIsActive,UserHasTran,UserLv2UserCode,UserLv3UserCode,UserLv4UserCode,UserCreditLimit,UserCreditLimitBal,UserCreditLimitKO,UserUplineShare,UserDownlineShare,UserCreateBy,UserCreateDate,UserLastUpdateBy,UserLastUpdateDate,UserLastLoginIP,UserLastLoginDate");

strSql.Append(", ROW_NUMBER() Over(order by [UserID] desc) as rowNum");

strSql.Append(" FROM UserMaster where UserCompanyCode = @UserCompanyCode");

strSql.Append(" and UserLv = " + CurrentLevelString);

strSql.Append(" and UserLv2UserCode = @ParentUserCode");

int beforeCount = 4;// 原參數(shù)個數(shù)

int beforeCountParams = 2;// 原參數(shù)個數(shù)

int allParamCount = beforeCount;

int allParamCountTotal = beforeCountParams;

SqlParameter[] parameters;

SqlParameter[] countParams;

string likeResult = string.Empty;

if (keywords != null)

{

// 查詢參數(shù)設(shè)置

Dictionary likeParams = new Dictionary();

Dictionary likeFields = new Dictionary();

Dictionary likeRemove = new Dictionary();

likeParams.Add("UserLoginId", new SqlParameter("@LikeUserLoginId", SqlDbType.VarChar, 50));

likeFields.Add("UserLoginId", @" UserLoginId like N'%' + @LikeUserLoginId + '%'");

likeParams.Add("UserName", new SqlParameter("@LikeUserName", SqlDbType.VarChar, 50));

likeFields.Add("UserName", @" UserName like N'%' + @LikeUserName + '%'");

// 初始化查詢參數(shù)

StringBuilder strLike = new StringBuilder();

foreach (KeyValuePair kp in likeParams)

{

if (keywords.ContainsKey(kp.Key) && (keywords[kp.Key] != string.Empty))

{

kp.Value.Value = keywords[kp.Key];

continue;

}

likeRemove.Add(kp.Key, "1");

}

// 移除未設(shè)置的條件

foreach (KeyValuePair kp in likeRemove)

{

likeParams.Remove(kp.Key);

likeFields.Remove(kp.Key);

}

int likeCount = likeParams.Count; // like 參數(shù)個數(shù)

allParamCount = likeCount + beforeCount;// 總參數(shù)個數(shù)

allParamCountTotal = likeCount + beforeCountParams;

// 開始構(gòu)造查詢參數(shù)

parameters = new SqlParameter[allParamCount];

countParams = new SqlParameter[allParamCountTotal];

// 初始化Like參數(shù)及sql字符串

int i = 0;

string[] tmp = new string[likeCount];

foreach (KeyValuePair kp in likeParams)

{

parameters[i + beforeCount] = kp.Value;

countParams[i + beforeCountParams] = kp.Value;

tmp[i] = likeFields[kp.Key];

i++;

}

// 構(gòu)造最終參數(shù)化 WHERE 語句

likeResult = string.Join(" or ", tmp);

if (likeResult != string.Empty) likeResult = " and (" + likeResult + ")";

}

else

{

// 開始構(gòu)造查詢參數(shù)

parameters = new SqlParameter[allParamCount];

countParams = new SqlParameter[allParamCountTotal];

}

string sql = "select count(1) from [UserMaster]";

sql += "where UserCompanyCode = @UserCompanyCode and UserLv = " + CurrentLevelString + " and UserLv2UserCode = @ParentUserCode";

sql += likeResult;

countParams[0] = new SqlParameter("@UserCompanyCode", SqlDbType.VarChar, 10);

countParams[0].Value = companyCode;

countParams[1] = new SqlParameter("@ParentUserCode", SqlDbType.VarChar, 30);

countParams[1].Value = userCode;

int recordCount = (int)SqlHelper.GetSingle(sql, countParams);

page.RecordCount = recordCount;

page.MathPageInfo();

// 設(shè)置當(dāng)前查詢的分頁記錄

parameters[0] = new SqlParameter("@UserCompanyCode", SqlDbType.VarChar, 10);

parameters[0].Value = companyCode;

parameters[1] = new SqlParameter("@ParentUserCode", SqlDbType.VarChar, 30);

parameters[1].Value = userCode;

parameters[2] = new SqlParameter("@startIndex", SqlDbType.Int, 4);

parameters[2].Value = (page.PageNow - 1) * page.PageSize + 1;

parameters[3] = new SqlParameter("@endIndex", SqlDbType.Int, 4);

parameters[3].Value = page.PageNow * page.PageSize;

strSql.Append(likeResult);

strSql.Append(") [tab] where rowNum between @startIndex and @endIndex");

return SqlHelper.Query(strSql.ToString(), parameters);

}