/// <summary>
/// Updates a registration account.
/// </summary>
/// <param name="registration">Registration Information of the user.</param>
public void Update(RegistrationInfo registration, int userLoginId)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SP_UPDATE_ACCOUNT");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@UserId", SqlDbType.Int),
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("@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),
new SqlParameter("@Email", SqlDbType.NVarChar, 256),
new SqlParameter("@Role", SqlDbType.Int),
new SqlParameter("@Status", SqlDbType.Int)
};
}
SQLHelper.CacheParameters("SP_UPDATE_ACCOUNT", parameters);
parameters[0].Value = registration.UserId;
parameters[1].Value = registration.FirstName;
parameters[2].Value = registration.MiddleName;
parameters[3].Value = registration.LastName;
parameters[4].Value = registration.CompanyName;
parameters[5].Value = registration.Address.Address1;
parameters[6].Value = registration.Address.Address2;
parameters[7].Value = registration.Address.City;
parameters[8].Value = registration.Address.State.StateId;
parameters[9].Value = registration.Address.Zip;
parameters[10].Value = registration.Address.Country.CountryId;
parameters[11].Value = registration.Address.Phone;
parameters[12].Value = registration.Address.Fax;
parameters[13].Value = registration.Address.Mobile;
parameters[14].Value = registration.Email;
parameters[15].Value = registration.Role;
parameters[16].Value = registration.Status;
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_UPDATE_ACCOUNT"),
parameters);
AuditEntryInfo auditEntry = new AuditEntryInfo(Module.UpdateProfile, registration.UserName, "Updated Profile", registration.UserId, userLoginId);
AuditTrail.WriteEntry(auditEntry, transaction);
transaction.Commit();
}
catch (SqlException ex)
{
transaction.Rollback();
throw;
}
}
}
}
/// <summary>
/// Updates the design of the specified user.
/// </summary>
/// <param name="userId">Internal identifier of the user.</param>
/// <param name="design">Design of the user.</param>
public void Update(int userId, DesignInfo design)
{
SqlParameter[] parameters = SQLHelper.GetCachedParameters("SP_UPDATE_DESIGN");
if (parameters == null)
{
parameters = new SqlParameter[] {
new SqlParameter("@design_id", SqlDbType.Int),
new SqlParameter("@user_id", SqlDbType.Int),
new SqlParameter("@category", SqlDbType.Int),
new SqlParameter("@type", SqlDbType.Int),
new SqlParameter("@length", SqlDbType.Decimal),
new SqlParameter("@width", SqlDbType.Decimal),
new SqlParameter("@gender", SqlDbType.NVarChar, 50),
new SqlParameter("@on_design_name", SqlDbType.NVarChar, 50),
new SqlParameter("@justification", SqlDbType.Int),
new SqlParameter("@gutter", SqlDbType.NVarChar, 50),
new SqlParameter("@msg_locn_x", SqlDbType.Decimal),
new SqlParameter("@msg_locn_y", SqlDbType.Decimal),
new SqlParameter("@msg_size_length", SqlDbType.Decimal),
new SqlParameter("@msg_size_width", SqlDbType.Decimal),
new SqlParameter("@low_res_file", SqlDbType.NVarChar, 255),
new SqlParameter("@high_res_file", SqlDbType.NVarChar, 255),
new SqlParameter("@extra_file", SqlDbType.NVarChar, 255),
new SqlParameter("@status", SqlDbType.Int),
new SqlParameter("@last_modify_by", SqlDbType.Int),
new SqlParameter("@approve_by", SqlDbType.Int),
new SqlParameter("@comments", SqlDbType.Text)
};
SQLHelper.CacheParameters("SP_UPDATE_DESIGN", parameters);
}
parameters[0].Value = design.DesignId;
parameters[1].Value = design.UserId;
parameters[2].Value = design.Category.LookupId;
parameters[3].Value = design.Type.LookupId;
parameters[4].Value = design.Size.Width;
parameters[5].Value = design.Size.Height;
parameters[6].Value = design.Gender;
parameters[7].Value = design.OnDesignName;
parameters[8].Value = design.Justification;
parameters[9].Value = design.Gutter;
parameters[10].Value = design.MessageRectangle.X;
parameters[11].Value = design.MessageRectangle.Y;
parameters[12].Value = design.MessageRectangle.Width;
parameters[13].Value = design.MessageRectangle.Height;
parameters[14].Value = design.LowResolutionFile;
parameters[15].Value = design.HighResolutionFile;
parameters[16].Value = design.ExtraFile;
parameters[17].Value = design.Status.LookupId;
parameters[18].Value = design.LastModifyBy;
parameters[19].Value = design.ApproveBy;
parameters[20].Value = design.Comments;
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_UPDATE_DESIGN"),
parameters);
// Write entry into audit trail.
string action = string.Empty;
if (design.DesignId == 0)
{
if (design.Status.LookupId == 23)
{
action = "Submitted design";
}
else
{
action = "Added design";
}
}
else
{
if (design.Status.LookupId == 23)
{
if (design.UserId == design.LastModifyBy)
{
action = "Submitted design";
}
else
{
action = "Modified design";
}
}
else if (design.Status.LookupId == 25)
{
action = "Archived design";
}
else if (design.Status.LookupId == 26)
{
action = "Approved design";
}
else
{
action = "Modified design";
}
}
AuditEntryInfo auditEntry = new AuditEntryInfo(
Module.DesignManagement, design.Category.Name,
action, design.UserId, design.LastModifyBy);
AuditTrail.WriteEntry(auditEntry, transaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public List <ProductInfo> GetProducts(int productTypeId, int agentUserId)
{
List <ProductInfo> products = new List <ProductInfo>();
ProductInfo itemInfo;
SqlParameter[] sqlParam = new SqlParameter[] { new SqlParameter("@product_type", SqlDbType.Int),
new SqlParameter("@agent_userid", SqlDbType.Int) };
if (productTypeId != Int32.MinValue)
{
sqlParam[0].Value = productTypeId;
}
if (agentUserId != Int32.MinValue)
{
sqlParam[1].Value = agentUserId;
}
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.StoredProcedure, SQLHelper.GetSQLStatement("Product", "SP_GET_PRODUCTS"), sqlParam))
{
itemInfo = new ProductInfo();
while (reader.Read())
{
bool productExists = false;
foreach (ProductInfo product in products)
{
if (product.ProductId == (string)reader["product_id"])
{
productExists = true;
itemInfo = product;
break;
}
}
if (!productExists)
{
itemInfo = new ProductInfo();
}
itemInfo.ProductId = (string)reader["product_id"];
itemInfo.TotalPrice = Math.Round((decimal)reader["price"], 2);
itemInfo.ProductStatus = (ProductStatus)((int)reader["status"]);
if (reader["short_desc"] != DBNull.Value)
{
itemInfo.BriefDesc = (string)reader["short_desc"];
}
if (itemInfo.BriefDescWithQuantity == null || itemInfo.BriefDescWithQuantity == string.Empty)
{
itemInfo.BriefDescWithQuantity = itemInfo.BriefDesc + "<br><br><I> Contains </I>";
}
if (reader["agent_user_id"] != DBNull.Value)
{
itemInfo.ProductCatalog = ProductCatalogType.Custom;
}
List <ProductItemInfo> productItemInfo = new List <ProductItemInfo>();
ProductItemInfo productItem = new ProductItemInfo();
productItem.Quantity = (int)reader["quantity"];
List <string> size = new List <string>();
if (reader["size"] != DBNull.Value)
{
size.Add((string)reader["size"]);
}
productItem.Size = size;
productItemInfo.Add(productItem);
itemInfo.ProductItems = productItemInfo;
if (agentUserId == Int32.MinValue || (agentUserId != Int32.MinValue && productTypeId != Int32.MinValue))
{
if (productTypeId == (int)ProductCategory.Generic_Product && (int)reader["Product_Count"] > 1)
{
AddToGenericProduct(products, itemInfo, reader);
}
else if ((int)reader["Product_Count"] == 1 && productTypeId == (int)reader["category_code"])
{
AddToDesc(itemInfo, reader);
products.Add(itemInfo);
itemInfo = new ProductInfo();
}
}
if (productTypeId == Int32.MinValue)
{
if ((int)reader["Product_Count"] > 1)
{
AddToGenericProduct(products, itemInfo, reader);
}
else if ((int)reader["Product_Count"] == 1)
{
AddToDesc(itemInfo, reader);
products.Add(itemInfo);
itemInfo = new ProductInfo();
}
}
}
}
return(products);
}
public bool UpdateProduct(ProductInfo productInfo)
{
bool success = false;
SqlParameter[] messageParameters = GetProductHeaderParams();
messageParameters[0].Value = productInfo.ProductId;
if (productInfo.BriefDesc != string.Empty)
{
messageParameters[1].Value = productInfo.BriefDesc;
}
messageParameters[2].Value = productInfo.ExtDesc;
messageParameters[3].Value = (int)productInfo.ProductStatus;
messageParameters[4].Value = productInfo.TotalPrice;
messageParameters[5].Value = productInfo.OwnerId;
using (SqlConnection conn = new SqlConnection(SQLHelper.CONNECTION_STRING))
{
conn.Open();
using (SqlTransaction sqlTran = conn.BeginTransaction())
{
bool isCustom = false;
try
{
int noOfRecords = SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure,
SQLHelper.GetSQLStatement("Product", "SP_UPDATE_PRODUCTHEADER"), messageParameters);
AuditEntryInfo auditEntry = new AuditEntryInfo(Module.ProductCatalog, productInfo.ProductId,
"Updated product header", productInfo.OwnerId, productInfo.OwnerId);
AuditTrail.WriteEntry(auditEntry, sqlTran);
success = true;
foreach (ProductItemInfo itemInfo in productInfo.ProductItems)
{
SqlParameter[] messageDetailsParameters = GetProductDetailsParams();
messageDetailsParameters[0].Value = productInfo.ProductId;
messageDetailsParameters[1].Value = 0;
messageDetailsParameters[2].Value = itemInfo.ProductTypeId;
messageDetailsParameters[3].Value = itemInfo.Quantity;
if (itemInfo.Size.Count > 0)
{
messageDetailsParameters[4].Value = itemInfo.Size[0];
}
if (itemInfo.IsCustomSize || isCustom)
{
messageDetailsParameters[5].Value = itemInfo.AgentUserId;
isCustom = true;
}
else
{
messageDetailsParameters[5].Value = DBNull.Value;
}
noOfRecords = SQLHelper.ExecuteNonQuery(sqlTran, CommandType.StoredProcedure,
SQLHelper.GetSQLStatement("Product", "SP_UPDATE_PRODUCTDETAILS"), messageDetailsParameters);
auditEntry = new AuditEntryInfo(Module.ProductCatalog, productInfo.ProductId + "-" + itemInfo.ProductTypeId,
"Updated product details", productInfo.OwnerId, isCustom ? itemInfo.AgentUserId : productInfo.OwnerId);
AuditTrail.WriteEntry(auditEntry, sqlTran);
}
sqlTran.Commit();
success = true;
}
catch
{
sqlTran.Rollback();
success = false;
throw;
}
}
}
return(success);
}
public ProductInfo GetProductDetails(string productId)
{
ProductInfo info = new ProductInfo();
ProductItemInfo itemInfo;
SqlParameter[] sqlParam = new SqlParameter[] { new SqlParameter("@product_id", SqlDbType.NVarChar, 30) };
sqlParam[0].Value = productId;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.StoredProcedure, SQLHelper.GetSQLStatement("Product", "SP_GET_SINGLEPRODUCTDETAILS"), sqlParam))
{
if (reader.HasRows)
{
reader.Read();
info.ProductId = (string)reader["product_id"];
info.TotalPrice = Math.Round((decimal)reader["price"], 2);
if (reader["short_desc"] != DBNull.Value)
{
info.BriefDesc = (string)reader["short_desc"];
}
info.BriefDescWithQuantity = info.BriefDesc + "<br><br><I> Contains </I>";
info.ExtDesc = (string)reader["long_desc"];
info.ProductStatus = (ProductStatus)(Convert.ToInt32(reader["status"]));
info.TotalPrice = Math.Round(Convert.ToDecimal(reader["price"]), 2);
List <ProductItemInfo> productItems = new List <ProductItemInfo>();
do
{
itemInfo = new ProductItemInfo();
if (reader["agent_user_id"] != DBNull.Value)
{
itemInfo.AgentUserId = (int)reader["agent_user_id"];
itemInfo.IsCustomSize = true;
info.ProductCatalog = ProductCatalogType.Custom;
}
itemInfo.ProductTypeId = (int)reader["category_code"];
itemInfo.ProductType = (string)reader["category_name"];
itemInfo.Quantity = (int)reader["quantity"];
itemInfo.Size = new List <string>();
if (reader["size"] != DBNull.Value)
{
itemInfo.Size.Add((string)reader["size"]);
}
AddToDesc(info, reader);
productItems.Add(itemInfo);
} while (reader.Read());
info.ProductItems = productItems;
}
else
{
info = null;
}
}
return(info);
}
public List <ProductItemInfo> GetInventoryTotalCount(int agentUserId)
{
List <ProductItemInfo> products = new List <ProductItemInfo>();
ProductItemInfo itemInfo;
SqlParameter[] sqlParam = new SqlParameter[] { new SqlParameter("@agent_userid", SqlDbType.Int) };
if (agentUserId != 0)
{
sqlParam[0].Value = agentUserId;
}
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.StoredProcedure, SQLHelper.GetSQLStatement("Product", "SP_GET_INVENTORYCOUNT"), sqlParam))
{
while (reader.Read())
{
itemInfo = new ProductItemInfo();
itemInfo.ProductType = (string)reader["category_name"];
itemInfo.ProductTypeId = (int)reader["category_code"];
itemInfo.Quantity = (int)reader["Quantity"];
products.Add(itemInfo);
}
}
return(products);
}
public List <ProductItemInfo> GetProductItems()
{
List <ProductItemInfo> products = new List <ProductItemInfo>();
ProductItemInfo itemInfo;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONNECTION_STRING,
CommandType.StoredProcedure, SQLHelper.GetSQLStatement("Product", "SP_GET_PRODUCTCATEGORIES"), null))
{
while (reader.Read())
{
itemInfo = new ProductItemInfo();
string avlSize = string.Empty;
if (reader["avl_size"] != DBNull.Value)
{
avlSize = (string)reader["avl_size"];
}
List <string> size = new List <string>();
if (avlSize != string.Empty)
{
char[] delim = new char[] { '|' };
if (avlSize != DBNull.Value.ToString() && avlSize != string.Empty)
{
foreach (string splitSize in avlSize.Split(delim))
{
size.Add(splitSize.Trim());
}
}
}
itemInfo.Size = size;
itemInfo.ProductType = (string)reader["category_name"];
itemInfo.ProductTypeId = (int)reader["category_code"];
products.Add(itemInfo);
}
}
return(products);
}
