public DataTable LoadCategorys()
{
connection = new ConnectionClass();
string query = @"SELECT * FROM Categories";
cmd = new SqlCommand(query, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
connection.GetClose();
return(dataTable);
}
public int UpdateGridviewRow(Item item)
{
connection = new ConnectionClass();
int isExecuted = 0;
try
{
commandString = @"UPDATE Items SET Name = '" + item.ItemName + "' WHERE ID = " + item.Id + "";
sqlCommand = new SqlCommand(commandString, connection.GetConnection());
isExecuted = sqlCommand.ExecuteNonQuery();
connection.GetClose();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
return(isExecuted);
}
public int AvaiLavelQty(StockInModel stockinModel)
{
connection = new ConnectionClass();
string query = @"SELECT sum(StockIn.Quantity) AS totalStockInQty, sum(StockOut.Quantity) AS totalStockOutQty FROM StockIn
LEFT OUTER JOIN StockOut ON StockIn.ItemId = StockOut.ItemId WHERE StockIn.ItemId = '" + stockinModel.ItemId + "' GROUP BY StockIn.ItemId";
cmd = new SqlCommand(query, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
//int avaiLavelQty = 0;
int totalInStock = 0;
int totalOutStock = 0;
foreach (DataRow row in dataTable.Rows)
{
if (!row.IsNull("totalStockInQty"))
{
totalInStock = Convert.ToInt32(row["totalStockInQty"]);
}
if (!row.IsNull("totalStockOutQty"))
{
totalOutStock = Convert.ToInt32(row["totalStockOutQty"]);
}
}
int avilavelStock;
if (dataTable.Rows.Count > 0)
{
avilavelStock = totalInStock - totalOutStock;
}
else
{
avilavelStock = 0;
}
connection.GetClose();
return(avilavelStock);
}
public int InsertStock(StockInModel stockinModel)
{
connection = new ConnectionClass();
if (!string.IsNullOrEmpty(stockinModel.Id))
{
string query = @"Update StockIn set ItemId = '" + stockinModel.ItemId + "', Quantity = '" + stockinModel.StockInQty + "' WHERE Id='" + stockinModel.Id + "'";
cmd = new SqlCommand(query, connection.GetConnection());
}
else
{
string query = @"INSERT INTO StockIn (ItemId, Quantity, CreatedDate) VALUES ( " + stockinModel.ItemId + ", '" + stockinModel.StockInQty + "', '" + stockinModel.CreatedDate + "')";
cmd = new SqlCommand(query, connection.GetConnection());
}
int isExecuted;
isExecuted = cmd.ExecuteNonQuery();
connection.GetClose();
return(isExecuted);
}
public int InsertItem(Item item)
{
connection = new ConnectionClass();
int isExecuted = 0;
try
{
commandString = @"INSERT INTO Items VALUES('" + item.ItemName + "','" + item.ReorderLevel + "'," + item.CategoryId + "," + item.CompanyId + " ,'" + item.UserId + "','" + item.CreatedDate + "')";
sqlCommand = new SqlCommand(commandString, connection.GetConnection());
isExecuted = sqlCommand.ExecuteNonQuery();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
connection.GetClose();
}
return(isExecuted);
}
public DataTable GetCompanysInCompanyCombobox()
{
DataTable dataTable = new DataTable();
try
{
connection = new ConnectionClass();
commandString = @"SELECT * FROM Companys";
sqlCommand = new SqlCommand(commandString, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
dataAdapter.Fill(dataTable);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
connection.GetClose();
}
return(dataTable);
}
public DataTable ShowItemsInDatagridview()
{
connection = new ConnectionClass();
DataTable dataTable = new DataTable();
try
{
commandString = @"SELECT i.Id as SL,i.Name,ReOrderLevel,c.Name as Category, a.Name as Company From Items as i INNER JOIN Categories as c ON c.Id=i.CategoryId INNER JOIN Companys as a ON a.Id=i.CompanyId";
sqlCommand = new SqlCommand(commandString, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
dataAdapter.Fill(dataTable);
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
connection.GetClose();
}
return(dataTable);
}
public DataTable CatWiseItems(StockInModel stockInModel)
{
connection = new ConnectionClass();
string query = @"SELECT * FROM Items where CategoryId = '" + stockInModel.CatId + "' AND CompanyId='" + stockInModel.ComId + "'";
cmd = new SqlCommand(query, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.NewRow();
row["Id"] = 0;
row["Name"] = "Please Select Item";
dataTable.Rows.InsertAt(row, 0);
}
connection.GetClose();
return(dataTable);
}
public DataTable LoadCompanys()
{
connection = new ConnectionClass();
string query = @"SELECT * FROM Companys";
cmd = new SqlCommand(query, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
DataRow row = dataTable.NewRow();
row[0] = 0;
row[1] = "Please Select Company";
dataTable.Rows.InsertAt(row, 0);
connection.GetClose();
return(dataTable);
}
public DataTable ComWiseCat(StockInModel stockInModel)
{
connection = new ConnectionClass();
string query = @"select c.Id, c.Name from Items as i left join Categories as c on c.Id = i.CategoryId where i.CompanyId = '" + stockInModel.ComId + "' group by c.Id, c.Name";
cmd = new SqlCommand(query, connection.GetConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
if (dataTable.Rows.Count > 0)
{
DataRow row = dataTable.NewRow();
row["Id"] = 0;
row["Name"] = "Please Select Cartegory";
dataTable.Rows.InsertAt(row, 0);
}
connection.GetClose();
return(dataTable);
}
//Extra method added by group manager in below
public List <Item> GetItems()
{
List <Item> items = new List <Item>();
connection = new ConnectionClass();
string query = "Select i.Id AS ItemId,i.Name AS ItemName,i.ReOrderLevel,i.CreatedDate,i.UserId,co.Id as CompanyId,co.Name as CompanyName,ca.Id as CategoryId,ca.Name as CategoryName From Items AS i INNER JOIN Companys as co ON i.CompanyId=co.Id INNER JOIN Categories as ca ON i.CategoryId=ca.Id";
try
{
sqlCommand = new SqlCommand(query, connection.GetConnection());
reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
Item item = new Item();
item.Id = (int)(reader["ItemId"]);
item.ItemName = reader["ItemName"].ToString();
item.ReorderLevel = Convert.ToInt32(reader["ReOrderLevel"]);
item.CategoryId = Convert.ToInt32(reader["CategoryId"]);
item.CompanyId = Convert.ToInt32(reader["CompanyId"]);
item.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
item.CategoryName = reader["CategoryName"].ToString();
item.CompanyName = reader["CompanyName"].ToString();
item.UserId = Convert.ToInt32(reader["UserId"]);
items.Add(item);
}
}
catch (Exception exception)
{
items = null;
}
finally
{
connection.GetClose();
}
return(items);
}