public static IDataReader GetUsersInRole(
int siteId,
int roleId,
int pageNumber,
int pageSize,
out int totalPages)
{
int pageLowerBound = (pageSize * pageNumber) - pageSize;
totalPages = 1;
int totalRows = GetCountOfUsersInRole(siteId, roleId);
if (pageSize > 0) totalPages = totalRows / pageSize;
if (totalRows <= pageSize)
{
totalPages = 1;
}
else
{
int remainder;
Math.DivRem(totalRows, pageSize, out remainder);
if (remainder > 0)
{
totalPages += 1;
}
}
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageSize.ToString(CultureInfo.InvariantCulture) + ") * FROM ");
sqlCommand.Append("(");
sqlCommand.Append("SELECT TOP (" + pageNumber.ToString(CultureInfo.InvariantCulture) + " * " + pageSize.ToString(CultureInfo.InvariantCulture) + ") ");
sqlCommand.Append("u.UserID, ");
sqlCommand.Append("u.[Name], ");
sqlCommand.Append("u.Email, ");
sqlCommand.Append("u.LoginName ");
sqlCommand.Append("FROM mp_Users u ");
sqlCommand.Append("JOIN mp_UserRoles ur ");
sqlCommand.Append("ON u.UserID = ur.UserID ");
sqlCommand.Append("AND ur.RoleID = @RoleID ");
sqlCommand.Append("WHERE u.SiteID = @SiteID ");
sqlCommand.Append("ORDER BY ");
sqlCommand.Append("u.[Name] ");
sqlCommand.Append(") AS t1 ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(") AS t2 ");
//sqlCommand.Append("WHERE ");
//sqlCommand.Append("ORDER BY ");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[2];
arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = siteId;
arParams[1] = new SqlCeParameter("@RoleID", SqlDbType.Int);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = roleId;
return SqlHelper.ExecuteReader(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
}
/// <summary>
/// Inserts a row in the mp_PaymentLog table. Returns rows affected count.
/// </summary>
/// <param name="rowGuid"> rowGuid </param>
/// <param name="createdUtc"> createdUtc </param>
/// <param name="siteGuid"> siteGuid </param>
/// <param name="userGuid"> userGuid </param>
/// <param name="storeGuid"> storeGuid </param>
/// <param name="cartGuid"> cartGuid </param>
/// <param name="provider"> provider </param>
/// <param name="rawResponse"> rawResponse </param>
/// <param name="responseCode"> responseCode </param>
/// <param name="responseReasonCode"> responseReasonCode </param>
/// <param name="reason"> reason </param>
/// <param name="avsCode"> avsCode </param>
/// <param name="ccvCode"> ccvCode </param>
/// <param name="cavCode"> cavCode </param>
/// <param name="transactionId"> transactionId </param>
/// <param name="transactionType"> transactionType </param>
/// <param name="method"> method </param>
/// <param name="authCode"> authCode </param>
/// <param name="amount"> amount </param>
/// <param name="tax"> tax </param>
/// <param name="duty"> duty </param>
/// <param name="freight"> freight </param>
/// <returns>int</returns>
public static int Create(
Guid rowGuid,
DateTime createdUtc,
Guid siteGuid,
Guid userGuid,
Guid storeGuid,
Guid cartGuid,
string provider,
string rawResponse,
string responseCode,
string responseReasonCode,
string reason,
string avsCode,
string ccvCode,
string cavCode,
string transactionId,
string transactionType,
string method,
string authCode,
decimal amount,
decimal tax,
decimal duty,
decimal freight)
{
StringBuilder sqlCommand = new StringBuilder();
sqlCommand.Append("INSERT INTO mp_PaymentLog ");
sqlCommand.Append("(");
sqlCommand.Append("RowGuid, ");
sqlCommand.Append("CreatedUtc, ");
sqlCommand.Append("SiteGuid, ");
sqlCommand.Append("UserGuid, ");
sqlCommand.Append("StoreGuid, ");
sqlCommand.Append("CartGuid, ");
sqlCommand.Append("Provider, ");
sqlCommand.Append("RawResponse, ");
sqlCommand.Append("ResponseCode, ");
sqlCommand.Append("ResponseReasonCode, ");
sqlCommand.Append("Reason, ");
sqlCommand.Append("AvsCode, ");
sqlCommand.Append("CcvCode, ");
sqlCommand.Append("CavCode, ");
sqlCommand.Append("TransactionId, ");
sqlCommand.Append("TransactionType, ");
sqlCommand.Append("Method, ");
sqlCommand.Append("AuthCode, ");
sqlCommand.Append("Amount, ");
sqlCommand.Append("Tax, ");
sqlCommand.Append("Duty, ");
sqlCommand.Append("Freight ");
sqlCommand.Append(")");
sqlCommand.Append(" VALUES ");
sqlCommand.Append("(");
sqlCommand.Append("@RowGuid, ");
sqlCommand.Append("@CreatedUtc, ");
sqlCommand.Append("@SiteGuid, ");
sqlCommand.Append("@UserGuid, ");
sqlCommand.Append("@StoreGuid, ");
sqlCommand.Append("@CartGuid, ");
sqlCommand.Append("@Provider, ");
sqlCommand.Append("@RawResponse, ");
sqlCommand.Append("@ResponseCode, ");
sqlCommand.Append("@ResponseReasonCode, ");
sqlCommand.Append("@Reason, ");
sqlCommand.Append("@AvsCode, ");
sqlCommand.Append("@CcvCode, ");
sqlCommand.Append("@CavCode, ");
sqlCommand.Append("@TransactionId, ");
sqlCommand.Append("@TransactionType, ");
sqlCommand.Append("@Method, ");
sqlCommand.Append("@AuthCode, ");
sqlCommand.Append("@Amount, ");
sqlCommand.Append("@Tax, ");
sqlCommand.Append("@Duty, ");
sqlCommand.Append("@Freight ");
sqlCommand.Append(")");
sqlCommand.Append(";");
SqlCeParameter[] arParams = new SqlCeParameter[22];
arParams[0] = new SqlCeParameter("@RowGuid", SqlDbType.UniqueIdentifier);
arParams[0].Direction = ParameterDirection.Input;
arParams[0].Value = rowGuid;
arParams[1] = new SqlCeParameter("@CreatedUtc", SqlDbType.DateTime);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = createdUtc;
arParams[2] = new SqlCeParameter("@SiteGuid", SqlDbType.UniqueIdentifier);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = siteGuid;
arParams[3] = new SqlCeParameter("@UserGuid", SqlDbType.UniqueIdentifier);
arParams[3].Direction = ParameterDirection.Input;
arParams[3].Value = userGuid;
arParams[4] = new SqlCeParameter("@StoreGuid", SqlDbType.UniqueIdentifier);
arParams[4].Direction = ParameterDirection.Input;
arParams[4].Value = storeGuid;
arParams[5] = new SqlCeParameter("@CartGuid", SqlDbType.UniqueIdentifier);
arParams[5].Direction = ParameterDirection.Input;
arParams[5].Value = cartGuid;
arParams[6] = new SqlCeParameter("@RawResponse", SqlDbType.NText);
arParams[6].Direction = ParameterDirection.Input;
arParams[6].Value = rawResponse;
arParams[7] = new SqlCeParameter("@ResponseCode", SqlDbType.NChar, 1);
arParams[7].Direction = ParameterDirection.Input;
arParams[7].Value = responseCode;
arParams[8] = new SqlCeParameter("@ResponseReasonCode", SqlDbType.NVarChar, 20);
arParams[8].Direction = ParameterDirection.Input;
arParams[8].Value = responseReasonCode;
arParams[9] = new SqlCeParameter("@Reason", SqlDbType.NText);
arParams[9].Direction = ParameterDirection.Input;
arParams[9].Value = reason;
arParams[10] = new SqlCeParameter("@AvsCode", SqlDbType.NVarChar, 50);
arParams[10].Direction = ParameterDirection.Input;
arParams[10].Value = avsCode;
arParams[11] = new SqlCeParameter("@CcvCode", SqlDbType.NChar, 1);
arParams[11].Direction = ParameterDirection.Input;
arParams[11].Value = ccvCode;
arParams[12] = new SqlCeParameter("@CavCode", SqlDbType.NChar, 1);
arParams[12].Direction = ParameterDirection.Input;
arParams[12].Value = cavCode;
arParams[13] = new SqlCeParameter("@TransactionId", SqlDbType.NVarChar, 50);
arParams[13].Direction = ParameterDirection.Input;
arParams[13].Value = transactionId;
arParams[14] = new SqlCeParameter("@TransactionType", SqlDbType.NVarChar, 50);
arParams[14].Direction = ParameterDirection.Input;
arParams[14].Value = transactionType;
arParams[15] = new SqlCeParameter("@Method", SqlDbType.NVarChar, 20);
arParams[15].Direction = ParameterDirection.Input;
arParams[15].Value = method;
arParams[16] = new SqlCeParameter("@AuthCode", SqlDbType.NVarChar, 50);
arParams[16].Direction = ParameterDirection.Input;
arParams[16].Value = authCode;
arParams[17] = new SqlCeParameter("@Amount", SqlDbType.Decimal);
arParams[17].Direction = ParameterDirection.Input;
arParams[17].Value = amount;
arParams[18] = new SqlCeParameter("@Tax", SqlDbType.Decimal);
arParams[18].Direction = ParameterDirection.Input;
arParams[18].Value = tax;
arParams[19] = new SqlCeParameter("@Duty", SqlDbType.Decimal);
arParams[19].Direction = ParameterDirection.Input;
arParams[19].Value = duty;
arParams[20] = new SqlCeParameter("@Freight", SqlDbType.Decimal);
arParams[20].Direction = ParameterDirection.Input;
arParams[20].Value = freight;
arParams[21] = new SqlCeParameter("@Provider", SqlDbType.NVarChar, 100);
arParams[21].Direction = ParameterDirection.Input;
arParams[21].Value = provider;
int rowsAffected = SqlHelper.ExecuteNonQuery(
GetConnectionString(),
CommandType.Text,
sqlCommand.ToString(),
arParams);
return(rowsAffected);
}
