/// <summary>
/// Gets the list of states for the specified country.
/// </summary>
/// <param name="countryId">The identifier of the country of the states to get.</param>
/// <returns>List of states for the specified country.</returns>
public List <StateInfo> GetStates(int countryId)
{
List <StateInfo> states = new List <StateInfo>();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_STATES");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@CountryId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_STATES", parameters);
}
parameters[0].Value = countryId;
// Execute a query to read the states.
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_STATES"),
parameters))
{
while (reader.Read())
{
StateInfo state = new StateInfo(reader.GetInt32(0), reader.GetString(1));
states.Add(state);
}
}
return(states);
}
private void UpdateEventInfoInDB(AccaInfo accaInfo, SqlConnection sqlCon, SqlTransaction sqlTran)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SP_UPDATE_STATUS_EVENT");
if (parameters == null)
{
parameters = new SqlParameter[] { new SqlParameter("@status", SqlDbType.Int),
new SqlParameter("@remarks", SqlDbType.NVarChar, 500),
new SqlParameter("@event_id", SqlDbType.Int),
new SqlParameter("@order_id", SqlDbType.Int),
new SqlParameter("@order_value", SqlDbType.Decimal) };
}
SQLHelper.CacheParameters("SP_UPDATE_STATUS_EVENT", parameters);
parameters[0].Value = Convert.ToInt32(accaInfo.EventStatus);
parameters[1].Value = accaInfo.Remarks;
parameters[2].Value = accaInfo.EventId;
if (accaInfo.EventStatus == ScheduleEventStatus.InProgress)
{
parameters[3].Value = accaInfo.AccaOrderInfo.OrderId;
parameters[4].Value = accaInfo.AccaOrderInfo.Amount;
}
else
{
parameters[3].Value = DBNull.Value;
parameters[4].Value = DBNull.Value;
}
SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure,
SQLHelper.GetSQLStatement(MODULE_NAME, "SP_UPDATESTATUSSCHEDULEDEVENTS"), parameters);
}
/// <summary>
/// Gets the lookup values for the specified category.
/// </summary>
/// <param name="category">The category of the lookup to get.</param>
/// <returns>List of lookup values for the specified category.</returns>
public List <LookupInfo> GetLookups(string category)
{
List <LookupInfo> lookups = new List <LookupInfo>();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_LOOKUP_VALUES");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@Category", SqlDbType.NVarChar, 250)
};
SQLHelper.CacheParameters("SQL_GET_LOOKUP_VALUES", parameters);
}
parameters[0].Value = category;
// Execute a query to read the lookup values.
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_LOOKUP_VALUES"),
parameters))
{
while (reader.Read())
{
LookupInfo lookup = new LookupInfo(reader.GetInt32(0), reader.GetString(1));
lookups.Add(lookup);
}
}
return(lookups);
}
/// <summary>
/// Get the items of the specified order.
/// </summary>
/// <param name="orderId">Internal identifier of the order.</param>
/// <returns>Items of the specified order.</returns>
public List <OrderItemInfo> GetItems(int orderId)
{
List <OrderItemInfo> items = new List <OrderItemInfo>();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_ORDER_DETAILS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@OrderId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_ORDER_DETAILS", parameters);
}
parameters[0].Value = orderId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_ORDER_DETAILS"),
parameters))
{
while (reader.Read())
{
string desc = new Product().GetProductDetails(reader.GetString(0)).BriefDescWithQuantity;
OrderItemInfo item = new OrderItemInfo(reader.GetString(0), desc,
OrderItemType.Product, Convert.ToInt32(reader[1]), Math.Round(reader.GetDecimal(2), 2));
items.Add(item);
}
}
return(items);
}
/// <summary>
/// Gets a value indicating whether the specified Email already exists
/// </summary>
/// <param name="Email">Email Id</param>
/// <returns>true if the specified Email exists
/// otherwise, false.</returns>
public bool IsEmailExists(string email, int userId)
{
bool isEmailExists = false;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_IS_EMAIL_EXISTS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@email", SqlDbType.NVarChar, 256),
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_IS_EMAIL_EXISTS", parameters);
}
parameters[0].Value = email;
parameters[1].Value = userId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_IS_EMAIL_EXISTS"),
parameters))
{
if (reader.Read())
{
if (reader.GetInt32(0) > 0)
{
isEmailExists = true;
}
}
}
return(isEmailExists);
}
private static void UpdateInventoryInDB(string designCategory, int quantity, int userId, SqlTransaction sqlTran)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_DEDUCTINVENTORY");
if (parameters == null)
{
parameters = new SqlParameter[] { new SqlParameter("@category_id", SqlDbType.Int),
new SqlParameter("@quantity", SqlDbType.Int),
new SqlParameter("@user_id", SqlDbType.Int) };
}
SQLHelper.CacheParameters("SQL_DEDUCTINVENTORY", parameters);
string[] enumNames = Enum.GetNames(typeof(ProductCategory));
int index = 0;
while (index < enumNames.Length)
{
if (enumNames[index] == designCategory)
{
break;
}
index++;
}
Array enumValues = Enum.GetValues(typeof(ProductCategory));
parameters[0].Value = Convert.ToInt32(enumValues.GetValue(index));
parameters[1].Value = quantity; //accaInfo.ContactCount;
parameters[2].Value = userId; // accaInfo.UserId;
SQLHelper.ExecuteNonQuery(sqlTran, CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_DEDUCTINVENTORY"), parameters);
}
/// <summary>
/// Gets the status of the specified registration account.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>Status of the specified registration account.</returns>
public RegistrationStatus GetStatus(int userId)
{
RegistrationStatus status = RegistrationStatus.Inactive;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_USER_STATUS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_USER_STATUS", parameters);
}
parameters[0].Value = userId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_STATUS"),
parameters))
{
if (reader.Read())
{
status = (RegistrationStatus)reader.GetInt32(0);
}
}
return(status);
}
/// <summary>
/// Writes an entry to the Audit Trail.
/// </summary>
/// <param name="auditEntry">Audit entry to be wriiten to the Audit Trail.</param>
public static void WriteEntry(AuditEntryInfo auditEntry, SqlTransaction transaction)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_WRITE_ENTRY");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@module", SqlDbType.NVarChar, 255),
new SqlParameter("@record", SqlDbType.NVarChar, 255),
new SqlParameter("@action", SqlDbType.NVarChar, 255),
new SqlParameter("@owner_id", SqlDbType.Int),
new SqlParameter("@modified_by", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_WRITE_ENTRY", parameters);
}
parameters[0].Value = auditEntry.ModuleName;
parameters[1].Value = auditEntry.Record;
parameters[2].Value = auditEntry.Action;
parameters[3].Value = auditEntry.OwnerId;
parameters[4].Value = auditEntry.ModifiedBy;
SQLHelper.ExecuteNonQuery(transaction,
CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_WRITE_ENTRY"),
parameters);
}
/// <summary>
/// Get the credit card details of the specified registration account.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>Credit card details of the specified registration account.</returns>
public CreditCardInfo GetCreditCard(int userId)
{
CreditCardInfo creditCard = null;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_CREDIT_CARD");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_CREDIT_CARD", parameters);
}
parameters[0].Value = userId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_CREDIT_CARD"),
parameters))
{
if (reader.Read())
{
creditCard = new CreditCardInfo(new LookupInfo(reader.GetInt32(0), reader.GetString(1)),
reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetInt32(5), reader.GetInt32(6), new AddressInfo(
reader.GetString(7), reader.GetString(8), reader.GetString(9),
new CountryInfo(reader.GetInt32(10), reader.GetString(11), false),
new StateInfo(reader.GetInt32(12), reader.GetString(13)),
reader.GetString(14), "", "", ""));
}
}
return(creditCard);
}
/// <summary>
/// Gets the lookup
/// </summary>
/// <param name="LookupId">The LookupId</param>
/// <returns>Lookup Name</returns>
public LookupInfo GetLookupDetails(int lookupId)
{
LookupInfo lookupdetails = new LookupInfo();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_LOOKUPDETAILS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@LookupId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_LOOKUPDETAILS", parameters);
}
parameters[0].Value = lookupId;
// Execute a query to read the lookup values.
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_LOOKUPDETAILS"),
parameters))
{
while (reader.Read())
{
lookupdetails = new LookupInfo(reader.GetInt32(0), reader.GetString(1));
}
}
return(lookupdetails);
}
/// <summary>
/// Searches for the application property with the specified name.
/// </summary>
/// <param name="name">The System.String containing the name of the property to get.</param>
/// <returns>An Irmac.MailingCycle.Model.PropertyInfo object representing the
/// application property with the specified name, if found; otherwise, null.</returns>
public PropertyInfo GetProperty(string name)
{
PropertyInfo property = null;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_PROPERTY");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@PropertyName", SqlDbType.NVarChar, 50)
};
SQLHelper.CacheParameters("SQL_GET_PROPERTY", parameters);
}
parameters[0].Value = name;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_PROPERTY"),
parameters))
{
if (reader.Read())
{
property = new PropertyInfo(reader.GetString(0), reader.GetString(1));
}
}
return(property);
}
/// <summary>
/// Gets the database parameters for Registration.
/// </summary>
/// <returns>Parameter array.</returns>
private static SqlParameter[] GetRegistrationParameters()
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_INSERT_ACCOUNT");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserName", SqlDbType.NVarChar, 256),
new SqlParameter("@Password", SqlDbType.NVarChar, 128),
new SqlParameter("@Email", SqlDbType.NVarChar, 256),
new SqlParameter("@PasswordQuestion", SqlDbType.NVarChar, 255),
new SqlParameter("@PasswordAnswer", SqlDbType.NVarChar, 256),
new SqlParameter("@FirstName", SqlDbType.NVarChar, 50),
new SqlParameter("@MiddleName", SqlDbType.NVarChar, 50),
new SqlParameter("@LastName", SqlDbType.NVarChar, 50),
new SqlParameter("@CompanyName", SqlDbType.NVarChar, 200),
new SqlParameter("@Status", SqlDbType.Int),
new SqlParameter("@SignupDate", SqlDbType.DateTime),
new SqlParameter("@RoleId", SqlDbType.Int),
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_INSERT_ACCOUNT", parameters);
}
return(parameters);
}
/// <summary>
/// Deletes the specified design.
/// </summary>
/// <param name="designId">Internal identifier of the design to delete.</param>
/// <param name="userId">Internal identifier of the user.</param>
public void Delete(int designId, int userId)
{
// Get the design details.
DesignInfo design = Get(designId);
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SP_DELETE_DESIGN");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@design_id", SqlDbType.Int)
};
SQLHelper.CacheParameters("SP_DELETE_DESIGN", parameters);
}
parameters[0].Value = designId;
using (SqlConnection connection = new SqlConnection(SQLHelper.CONNECTION_STRING))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
SQLHelper.ExecuteNonQuery(transaction,
CommandType.StoredProcedure,
SQLHelper.GetSQLStatement(MODULE_NAME, "SP_DELETE_DESIGN"),
parameters);
// Write entry into audit trail.
string action = string.Empty;
if (design.Status.LookupId == 23)
{
action = "Rejected design";
}
else
{
action = "Deleted design";
}
AuditEntryInfo auditEntry = new AuditEntryInfo(
Module.DesignManagement, design.Category.Name,
action, design.UserId, userId);
AuditTrail.WriteEntry(auditEntry, transaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
/// <summary>
/// Get the design statuses of the specified parameters.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <param name="category">Category of the designs.</param>
/// <param name="status">Status of the designs.</param>
/// <returns>The design statuses of the specified parameters.</returns>
public List <DesignStatusInfo> GetDesignStatuses(int userId, int category,
int status)
{
List <DesignStatusInfo> designs = new List <DesignStatusInfo>();
SqlParameter[] parameters =
SQLHelper.GetCachedParameters("SQL_DESIGN_STATUS_REPORT");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@user_id", SqlDbType.Int),
new SqlParameter("@category", SqlDbType.Int),
new SqlParameter("@status", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_DESIGN_STATUS_REPORT", parameters);
}
parameters[0].Value = userId;
parameters[1].Value = category;
parameters[2].Value = status;
// Execute the query to read the designs.
using (SqlDataReader reader =
SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_DESIGN_STATUS_REPORT"),
parameters))
{
while (reader.Read())
{
DesignStatusInfo design = new DesignStatusInfo();
design.AgentId = reader.GetInt32(0);
design.AgentName = reader.GetString(1);
design.Phone = reader.GetString(2);
design.Category = reader.GetString(3);
design.Status = reader.GetString(4);
if (!reader.IsDBNull(5))
{
design.LastModifyDate = reader.GetDateTime(5).ToString("MM/dd/yyyy");
}
if (!reader.IsDBNull(6))
{
design.DaysInStatus = reader.GetInt32(6);
}
designs.Add(design);
}
}
return(designs);
}
private static SqlParameter[] GetPropertyParams()
{
SqlParameter[] propertyParams = SQLHelper.GetCachedParameters("SP_INSERTUPDATE_PROPERTY");
if (propertyParams == null)
{
propertyParams = new SqlParameter[] {
new SqlParameter("@property_name", SqlDbType.Text),
new SqlParameter("@property_values", SqlDbType.Text)
};
SQLHelper.CacheParameters("SP_INSERTUPDATE_PROPERTY", propertyParams);
}
return(propertyParams);
}
private static SqlParameter[] GetCartItemParams()
{
SqlParameter[] cartItemParams = SQLHelper.GetCachedParameters("SQL_INSERT_GetCartItem");
if (cartItemParams == null)
{
cartItemParams = new SqlParameter[] {
new SqlParameter("@user_id", SqlDbType.Int),
new SqlParameter("@product_id", SqlDbType.NVarChar, 30),
new SqlParameter("@quantity", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_INSERT_GetCartItem", cartItemParams);
}
return(cartItemParams);
}
private static SqlParameter[] GetCartItemParams()
{
SqlParameter[] cartItemParams = SQLHelper.GetCachedParameters("SQL_INSERT_INVENTORY");
if (cartItemParams == null)
{
cartItemParams = new SqlParameter[] {
new SqlParameter("@user_id", SqlDbType.Int),
new SqlParameter("@category_code", SqlDbType.Int),
new SqlParameter("@quantity", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_INSERT_INVENTORY", cartItemParams);
}
return(cartItemParams);
}
private static SqlParameter[] GetProductDetailsParams()
{
SqlParameter[] messageParams = SQLHelper.GetCachedParameters("SQL_INSERT_GetProductDetails");
if (messageParams == null)
{
messageParams = new SqlParameter[] {
new SqlParameter("@product_id", SqlDbType.NVarChar, 30),
new SqlParameter("@item_no", SqlDbType.Int),
new SqlParameter("@category_code", SqlDbType.Int),
new SqlParameter("@quantity", SqlDbType.Int),
new SqlParameter("@size", SqlDbType.Text),
new SqlParameter("@agent_userid", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_INSERT_GetProductDetails", messageParams);
}
return(messageParams);
}
private static SqlParameter[] GetProductHeaderParams()
{
SqlParameter[] messageParams = SQLHelper.GetCachedParameters("SQL_INSERT_GetProductHeader");
if (messageParams == null)
{
messageParams = new SqlParameter[] {
new SqlParameter("@product_id", SqlDbType.NVarChar, 30),
new SqlParameter("@short_desc", SqlDbType.NVarChar, 255),
new SqlParameter("@long_desc", SqlDbType.NVarChar, 255),
new SqlParameter("@status", SqlDbType.Int),
new SqlParameter("@price", SqlDbType.SmallMoney),
new SqlParameter("@user_id", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_INSERT_GetProductHeader", messageParams);
}
return(messageParams);
}
/// <summary>
/// Updates the credit card details of the specified registration account.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <param name="creditCard">Credit card details of the user.</param>
/// <param name="transaction">Transaction under which the updation should occur.</param>
internal void UpdateCreditCard(int userId, ref CreditCardInfo creditCard,
SqlTransaction transaction)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SP_UPDATE_CREDIT_CARD");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int),
new SqlParameter("@CreditCardTypeId", SqlDbType.Int),
new SqlParameter("@CreditCardNo", SqlDbType.NVarChar, 20),
new SqlParameter("@CVVNo", SqlDbType.NVarChar, 4),
new SqlParameter("@HolderName", SqlDbType.NVarChar, 50),
new SqlParameter("@ExpirationMonth", SqlDbType.Int),
new SqlParameter("@ExpirationYear", SqlDbType.Int),
new SqlParameter("@BillingAddress1", SqlDbType.NVarChar, 255),
new SqlParameter("@BillingAddress2", SqlDbType.NVarChar, 255),
new SqlParameter("@BillingCity", SqlDbType.NVarChar, 100),
new SqlParameter("@BillingStateId", SqlDbType.Int),
new SqlParameter("@BillingZip", SqlDbType.NVarChar, 15),
new SqlParameter("@BillingCountryId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SP_UPDATE_CREDIT_CARD", parameters);
}
parameters[0].Value = userId;
parameters[1].Value = creditCard.Type.LookupId;
parameters[2].Value = creditCard.Number;
parameters[3].Value = creditCard.CvvNumber;
parameters[4].Value = creditCard.HolderName;
parameters[5].Value = creditCard.ExpirationMonth;
parameters[6].Value = creditCard.ExpirationYear;
parameters[7].Value = creditCard.Address.Address1;
parameters[8].Value = creditCard.Address.Address2;
parameters[9].Value = creditCard.Address.City;
parameters[10].Value = creditCard.Address.State.StateId;
parameters[11].Value = creditCard.Address.Zip;
parameters[12].Value = creditCard.Address.Country.CountryId;
SQLHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure,
SQLHelper.GetSQLStatement(MODULE_NAME, "SP_UPDATE_CREDIT_CARD"),
parameters);
}
/// <summary>
/// Get the list of orders for the specified user.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>List of orders for the specified user.</returns>
public List <OrderInfo> GetList(int userId)
{
List <OrderInfo> orders = new List <OrderInfo>();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_ORDERS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_ORDERS", parameters);
}
parameters[0].Value = userId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_ORDERS"),
parameters))
{
while (reader.Read())
{
List <OrderItemInfo> orderItems = GetItems(reader.GetInt32(1));
CreditCardInfo cardInfo = new CreditCardInfo(new LookupInfo(reader.GetInt32(3), reader.GetString(4)), reader.GetString(5), string.Empty, string.Empty, Int32.MinValue, Int32.MinValue,
new AddressInfo(reader.GetString(8), reader.GetString(9), reader.GetString(10), new CountryInfo(reader.GetInt32(13), reader.GetString(14), false),
new StateInfo(reader.GetInt32(11), reader.GetString(12)), reader.GetString(15), null, null, null));
OrderInfo order = new OrderInfo(reader.GetInt32(1), null,
(OrderType)reader.GetInt32(16), reader.GetDateTime(2), Math.Round(reader.GetDecimal(6), 2),
reader.GetInt32(7), null, cardInfo, orderItems);
if (reader[17] != DBNull.Value)
{
order.RefundAmount = reader.GetDecimal(17);
}
orders.Add(order);
}
}
return(orders);
}
/// <summary>
/// Get all the designs of the specified user.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>All the designs of the specified user.</returns>
public List <DesignInfo> GetListAll(int userId)
{
List <DesignInfo> designs = new List <DesignInfo>();
SqlParameter[] parameters =
SQLHelper.GetCachedParameters("SQL_GET_DESIGNS_ALL");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@user_id", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_DESIGNS_ALL", parameters);
}
parameters[0].Value = userId;
// Execute the query to read the designs.
using (SqlDataReader reader =
SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_DESIGNS_ALL"),
parameters))
{
while (reader.Read())
{
DesignInfo design = new DesignInfo(reader.GetInt32(0),
new LookupInfo(reader.GetInt32(1), reader.GetString(2)),
new LookupInfo(reader.GetInt32(3), reader.GetString(4)),
new SizeF((float)reader.GetDecimal(5), (float)reader.GetDecimal(6)),
reader.GetString(9),
new LookupInfo(reader.GetInt32(7), reader.GetString(8)));
design.LastModifyDate = reader.GetDateTime(10);
design.UserId = reader.GetInt32(11);
designs.Add(design);
}
}
return(designs);
}
private static SqlParameter[] GetStdMessageParams()
{
SqlParameter[] messageParams = SQLHelper.GetCachedParameters("SQL_INSERT_STDMESSAGE");
if (messageParams == null)
{
messageParams = new SqlParameter[] {
new SqlParameter("@message_id", SqlDbType.Int),
new SqlParameter("@type", SqlDbType.Int),
new SqlParameter("@short_desc", SqlDbType.Text),
new SqlParameter("@message_text", SqlDbType.Text),
new SqlParameter("@status", SqlDbType.Int),
new SqlParameter("@owner_id", SqlDbType.Int),
new SqlParameter("@is_image", SqlDbType.Bit),
new SqlParameter("@file_name", SqlDbType.NVarChar, 255),
new SqlParameter("@is_default_message", SqlDbType.Bit)
};
SQLHelper.CacheParameters("SQL_INSERT_STDMESSAGE", messageParams);
}
return(messageParams);
}
/// <summary>
/// Get the users of the specified roles.
/// </summary>
/// <param name="roles">Comma-delimited string containg the roles.</param>
/// <returns>Users of the specified roles.</returns>
public List <RegistrationInfo> GetUsersByRole(string roles)
{
List <RegistrationInfo> users = new List <RegistrationInfo>();
SqlParameter[] parameters =
SQLHelper.GetCachedParameters("SQL_GET_USERS_BY_ROLE");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@roles", SqlDbType.NVarChar, 4000)
};
SQLHelper.CacheParameters("SQL_GET_USERS_BY_ROLE", parameters);
}
parameters[0].Value = roles;
// Execute the query to read the users.
using (SqlDataReader reader =
SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_USERS_BY_ROLE"),
parameters))
{
while (reader.Read())
{
RegistrationInfo user = new RegistrationInfo(reader.GetInt32(0),
reader.GetString(1), string.Empty, reader.GetString(2),
string.Empty, string.Empty, reader.GetString(3), string.Empty,
reader.GetString(4), string.Empty, null, null,
RegistrationStatus.Active, new DateTime(),
(UserRole)reader.GetInt32(5));
users.Add(user);
}
}
return(users);
}
/// <summary>
/// Updates the status of the specified registration account.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <param name="status">Status of the user.</param>
/// <param name="transaction">Transaction under which the updation should occur.</param>
internal void UpdateStatus(int userId, RegistrationStatus status,
SqlTransaction transaction)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_UPDATE_STATUS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int),
new SqlParameter("@Status", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_UPDATE_STATUS", parameters);
}
parameters[0].Value = userId;
parameters[1].Value = status;
SQLHelper.ExecuteNonQuery(transaction, CommandType.Text,
SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_UPDATE_STATUS"),
parameters);
}
/// <summary>
/// Get the details of the specified registration account.
/// </summary>
/// <param name="userName">User name of the user.</param>
/// <returns>Details of the specified registration account.</returns>
public RegistrationInfo GetDetails(string userName)
{
RegistrationInfo registration = null;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_ACCOUNTNAME_DETAILS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserName", SqlDbType.NVarChar, 256)
};
SQLHelper.CacheParameters("SQL_GET_ACCOUNTNAME_DETAILS", parameters);
}
parameters[0].Value = userName;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_ACCOUNTNAME_DETAILS"),
parameters))
{
if (reader.Read())
{
registration = new RegistrationInfo(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5),
reader.GetString(6), reader.GetString(7), reader.GetString(8),
reader.GetString(9), new AddressInfo(
reader.GetString(10), reader.GetString(11), reader.GetString(12),
new CountryInfo(reader.GetInt32(13), reader.GetString(14), false),
new StateInfo(reader.GetInt32(15), reader.GetString(16)),
reader.GetString(17), reader.GetString(18), reader.GetString(19),
reader.GetString(20)),
null, (RegistrationStatus)reader.GetInt32(21), reader.GetDateTime(22), (UserRole)reader.GetInt32(23));
}
}
return(registration);
}
/// <summary>
/// Gets the database parameters for Registration address.
/// </summary>
/// <returns>Parameter array.</returns>
private static SqlParameter[] GetRegistrationAddressParameters()
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_INSERT_ACCOUNT_SHIPPINGADDRESS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int),
new SqlParameter("@Address1", SqlDbType.NVarChar, 255),
new SqlParameter("@Address2", SqlDbType.NVarChar, 255),
new SqlParameter("@City", SqlDbType.NVarChar, 100),
new SqlParameter("@StateId", SqlDbType.Int),
new SqlParameter("@Zip", SqlDbType.NVarChar, 15),
new SqlParameter("@CountryId", SqlDbType.Int),
new SqlParameter("@Phone", SqlDbType.NVarChar, 20),
new SqlParameter("@Fax", SqlDbType.NVarChar, 20),
new SqlParameter("@Mobile", SqlDbType.NVarChar, 20),
};
SQLHelper.CacheParameters("SQL_INSERT_ACCOUNT_SHIPPINGADDRESS", parameters);
}
return(parameters);
}
/// <summary>
/// Gets the login details of the specified registration account.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>Validate user details of the specified registration account.</returns>
public bool ValidateUser(string userName, string password)
{
bool loginStatus = false;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_VALIDATE_USER");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserName", SqlDbType.NVarChar, 256),
new SqlParameter("@Password", SqlDbType.NVarChar, 128)
};
SQLHelper.CacheParameters("SQL_VALIDATE_USER", parameters);
}
parameters[0].Value = userName;
parameters[1].Value = password;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_VALIDATE_USER"),
parameters))
{
if (reader.Read())
{
if (reader.GetInt32(0) != 0)
{
loginStatus = true;
}
}
}
return(loginStatus);
}
public List <InventoryInfo> GetInventoryList(int userId)
{
List <InventoryInfo> items = new List <InventoryInfo>();
InventoryInfo inventoryInfo = new InventoryInfo();
InventoryItemInfo inventoryItemInfo;
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_INVENTORY_DETAILS");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@user_id", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_INVENTORY_DETAILS", parameters);
}
parameters[0].Value = userId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.StoredProcedure, SQLHelper.GetSQLStatement(MODULE_NAME, "SP_GET_INVENTORY_DETAILS"),
parameters))
{
while (reader.Read())
{
bool isExists = false;
foreach (InventoryInfo inventoryAddedInfo in items)
{
if ((int)inventoryAddedInfo.CategoryType == (int)reader["category_code"])
{
isExists = true;
inventoryInfo = inventoryAddedInfo;
break;
}
}
if (!isExists)
{
inventoryInfo = new InventoryInfo();
inventoryInfo.CategoryType = (ProductCategory)(Convert.ToInt32(reader["category_code"]));
inventoryInfo.InventoryItems = new List <InventoryItemInfo>();
}
inventoryItemInfo = new InventoryItemInfo();
inventoryItemInfo.OrderTransactionType = (TransactionType)(Convert.ToInt32(reader["order_type"]));
inventoryItemInfo.Quantity = Convert.ToInt32(reader["Quantity"]);
inventoryItemInfo.Remarks = (string)reader["cc_trxn_message"];
inventoryItemInfo.TransactionDate = Convert.ToDateTime(reader["order_date"]);
inventoryItemInfo.TransactionId = Convert.ToInt32(reader["cc_trxn_code"]);
inventoryInfo.InventoryItems.Add(inventoryItemInfo);
if (!isExists)
{
items.Add(inventoryInfo);
}
}
}
Product product = new Product();
List <ProductItemInfo> products = product.GetInventoryTotalCount(userId);
foreach (ProductItemInfo itemInfo in products)
{
foreach (InventoryInfo inventoryInfoItem in items)
{
if (itemInfo.ProductType == inventoryInfoItem.CategoryType.ToString().Replace("_", " "))
{
inventoryInfoItem.QuantityOnHand = itemInfo.Quantity;
}
}
}
return(items);
}
/// <summary>
/// Get the list of orders for the specified user.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <returns>List of orders for the specified user.</returns>
public List <InventoryItemReportInfo> GetSearchInventory(int userId, DateTime startDate, DateTime endDate)
{
List <InventoryItemReportInfo> items = new List <InventoryItemReportInfo>();
InventoryItemReportInfo inventoryInfo = new InventoryItemReportInfo();
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SQL_GET_SEARCH_INVENTORY");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int)
};
SQLHelper.CacheParameters("SQL_GET_SEARCH_INVENTORY", parameters);
}
parameters[0].Value = userId;
string sql = SQLHelper.GetSQLStatement(MODULE_NAME, "SQL_GET_INVETORY");
if (startDate != DateTime.MinValue)
{
sql += " AND convert(datetime,order_date) >= '" + startDate.ToString() + "'";
}
if (endDate != DateTime.MinValue)
{
sql += " AND convert(datetime,order_date) <= '" + endDate.AddDays(1).ToString() + "'";
}
sql += " order by order_date";
int sumQuantityPK = 0;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.Text, sql, parameters))
{
while (reader.Read())
{
if (reader["quantity"] != DBNull.Value)
{
bool isExists = false;
foreach (InventoryItemReportInfo inventoryAddedInfo in items)
{
if (inventoryAddedInfo.TransactionDate == Convert.ToDateTime(reader["order_date"]))
{
if (inventoryAddedInfo.OrderTransactionType == (TransactionType)Convert.ToInt32(reader["order_type"]))
{
isExists = true;
if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.PowerKard)
{
inventoryAddedInfo.QuantityPK += Convert.ToInt32(reader["Quantity"]);
}
else if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.Brochure)
{
inventoryAddedInfo.QuantityBR += Convert.ToInt32(reader["Quantity"]);
}
else if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.Envelope)
{
inventoryAddedInfo.QuantityEN += Convert.ToInt32(reader["Quantity"]);
}
break;
}
}
}
if (!isExists)
{
inventoryInfo = new InventoryItemReportInfo();
inventoryInfo.TransactionDate = Convert.ToDateTime(reader["order_date"]);
inventoryInfo.OrderTransactionType = (TransactionType)Convert.ToInt32(reader["order_type"]);
if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.PowerKard)
{
inventoryInfo.QuantityPK = Convert.ToInt32(reader["Quantity"]);
}
else if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.Brochure)
{
inventoryInfo.QuantityBR = Convert.ToInt32(reader["Quantity"]);
}
else if (((ProductCategory)(Convert.ToInt32(reader["category_code"]))) == ProductCategory.Envelope)
{
inventoryInfo.QuantityEN = Convert.ToInt32(reader["Quantity"]);
}
items.Add(inventoryInfo);
}
}
}
}
if (items.Count > 0)
{
int rowCount = 0;
items[rowCount].SumQuantityPK = items[rowCount].QuantityPK;
items[rowCount].SumQuantityBR = items[rowCount].QuantityBR;
items[rowCount].SumQuantityEN = items[rowCount].QuantityEN;
rowCount++;
while (rowCount < items.Count)
{
if (items[rowCount].OrderTransactionType == TransactionType.Purchased)
{
items[rowCount].SumQuantityPK = items[rowCount - 1].SumQuantityPK + items[rowCount].QuantityPK;
items[rowCount].SumQuantityBR = items[rowCount - 1].SumQuantityBR + items[rowCount].QuantityBR;
items[rowCount].SumQuantityEN = items[rowCount - 1].SumQuantityEN + items[rowCount].QuantityEN;
}
else
{
items[rowCount].SumQuantityPK = items[rowCount - 1].SumQuantityPK - items[rowCount].QuantityPK;
items[rowCount].SumQuantityBR = items[rowCount - 1].SumQuantityBR - items[rowCount].QuantityBR;
items[rowCount].SumQuantityEN = items[rowCount - 1].SumQuantityEN - items[rowCount].QuantityEN;
}
rowCount++;
}
}
return(items);
}
