public void CreateWShelfItemDetailsAndWItemQuantity(DisplayWShelfItemDetails obj)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"INSERT INTO WShelfItemDetails(OEMCode, Name,LocationId, CreatedUserId,Length,
Width,Height,Weight,Boxing,Damaged,CreatedDate,Notes,ItemId,Type,Brand)
VALUES(@oEMCode, @name,@locationId,@createdUserId,@length,@width,
@height,@weight,@boxing,@damaged,CURRENT_TIMESTAMP,@notes,@itemId,@type,@brand)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@oEMCode", SqlDbType.NVarChar).Value = obj.OEMCode;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = obj.Name;
cmd.Parameters.Add("@locationId", SqlDbType.Int).Value = obj.LocationId;
cmd.Parameters.Add("@createdUserId", SqlDbType.Int).Value = obj.CreatedUserId;
cmd.Parameters.Add("@length", SqlDbType.NVarChar).Value = obj.Length;
cmd.Parameters.Add("@width", SqlDbType.NVarChar).Value = obj.Width;
cmd.Parameters.Add("@height", SqlDbType.NVarChar).Value = obj.Height;
cmd.Parameters.Add("@weight", SqlDbType.NVarChar).Value = obj.Weight;
cmd.Parameters.Add("@boxing", SqlDbType.NVarChar).Value = obj.Boxing;
cmd.Parameters.Add("@damaged", SqlDbType.Bit).Value = obj.Damaged;
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = obj.Notes;
cmd.Parameters.Add("@itemId", SqlDbType.Int).Value = obj.ItemId;
cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = obj.Type;
cmd.Parameters.Add("@brand", SqlDbType.NVarChar).Value = obj.Brand;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}
public void UpdateWShelfItemDetailsAndWItemQuantity(DisplayWShelfItemDetails obj)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"UPDATE WShelfItemDetails SET OEMCode=@oEMCode, Name=@name ,LocationId=@locationId,
Length=@length,Width=@width,Height=@height,Weight=@weight ,
Boxing=@boxing,Damaged=@damaged,Notes=@notes,Type=@type,Brand=@brand, ModifiedDate=CURRENT_TIMESTAMP WHERE Id=@Id";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = obj.Id;
cmd.Connection = conn;
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = obj.Name;
cmd.Parameters.Add("@oEMCode", SqlDbType.NVarChar).Value = obj.OEMCode;
cmd.Parameters.Add("@locationId", SqlDbType.Int).Value = obj.LocationId;
cmd.Parameters.Add("@length", SqlDbType.NVarChar).Value = obj.Length;
cmd.Parameters.Add("@width", SqlDbType.NVarChar).Value = obj.Width;
cmd.Parameters.Add("@weight", SqlDbType.NVarChar).Value = obj.Weight;
cmd.Parameters.Add("@height", SqlDbType.NVarChar).Value = obj.Height;
cmd.Parameters.Add("@boxing", SqlDbType.NVarChar).Value = obj.Boxing;
cmd.Parameters.Add("@damaged", SqlDbType.Bit).Value = obj.Damaged;
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = obj.Notes;
cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = obj.Type;
cmd.Parameters.Add("@brand", SqlDbType.NVarChar).Value = obj.Brand;
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
}
conn.Close();
}
public IList <DisplayWShelfItemDetails> GetAllShelfItems()
{
var list = new List <DisplayWShelfItemDetails>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT ssfit.Id, ssfit.OEMCode,ssfit.Name ,ssfit.Brand,ssfit.Type,ssfit.LocationId,ssfit.Length,ssfit.Width,ssfit.Height,ssfit.ItemId,
itm.SupplierItemCode,itm.Description,itm.COG,itm.DSB,itm.RepUnitPrice,itm.COG,itm.Quantity,itm.Active,ssfit.Notes,
ssfit.Weight,ssfit.Boxing,ssfit.Damaged, ssfit.CreatedUserId ,ssfit.CreatedDate,wsfl.ColumnName,wsfl.RowNumber
FROM dbo.WShelfItemDetails ssfit JOIN [dbo].[WShelf] wsfl ON ssfit.LocationId=wsfl.Id
join Items itm ON itm.ItemId=ssfit.ItemId";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var obj = new DisplayWShelfItemDetails();
obj.Id = Convert.ToInt32(reader["Id"]);
obj.ItemId = Convert.ToInt32(reader["ItemId"]);
obj.OEMCode = Convert.ToString(reader["OEMCode"]);
obj.Name = Convert.ToString(reader["Name"]);
obj.LocationId = Convert.ToInt32(reader["LocationId"]);
obj.CreatedUserId = Convert.ToInt32(reader["CreatedUserId"]);
obj.Length = Convert.ToString(reader["Length"]);
obj.Width = Convert.ToString(reader["Width"]);
obj.Height = Convert.ToString(reader["Height"]);
obj.Weight = Convert.ToString(reader["Weight"]);
obj.Boxing = Convert.ToString(reader["Boxing"]);
obj.Damaged = Convert.ToBoolean(reader["Damaged"]);
obj.CreatedDate = Convert.ToDateTime(reader["CreatedDate"].ToString());
obj.Notes = reader["Notes"].ToString();
if (reader["DSB"] != DBNull.Value)
{
obj.DSB = reader["DSB"].ToString();
}
obj.LocationColumnName = Convert.ToString(reader["ColumnName"]);
obj.LocationRowNumber = Convert.ToString(reader["RowNumber"]);
obj.SupplierItemCode = reader["SupplierItemCode"].ToString();
obj.Description = reader["Description"].ToString();
obj.COG = reader["COG"].ToString();
obj.RepUnitPrice = reader["RepUnitPrice"].ToString();
obj.ManagerUnitPrice = reader["RepUnitPrice"].ToString();
obj.Quantity = reader["Quantity"].ToString();
obj.Brand = reader["Brand"].ToString();
obj.Type = reader["Type"].ToString();
list.Add(obj);
}
}
conn.Close();
return(list);
}