void setOutofStockandLowStock()
{
try
{
int LowStockcount = 0;
int outOfStockcount = 0;
BusinessObjects.stock_product stoProduct = new stock_product();
outOfStockcount = stoProduct.GetOutOfStock(con);
BusinessObjects.GetProductStock spObj = new GetProductStock();
List <GetProductStock> avalibilityList = new List <GetProductStock>();
avalibilityList = spObj.getProdAlongWithStockQuantity_All(con);
foreach (var item in avalibilityList)
{
if (item.TotalQuantity < 5)
{
LowStockcount++;
}
}
lblOutOfStock.Text = outOfStockcount.ToString() + " Prods";
lblLowStock.Text = LowStockcount.ToString() + " Prods";
}
catch (Exception ex)
{
MetroMessageBox.Show(this, "System Error " + ex.Message, "MetroMessageBox", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void updateStock(int productID, int StockID, int Quanityt)
{
int existenceStockTable = 0;
int existenceStockProductTable = 0;
BusinessObjects.SalesReturn salesReturnObject = new BusinessObjects.SalesReturn();
string queryExistenseStockTable = "select count (*) from _Stokc where stock_id=" + StockID + " ";
object objTemp = BusinessObjects.SalesReturn.getScalar(con, queryExistenseStockTable);
if (objTemp != null)
{
existenceStockTable = (int)objTemp;
}
if (existenceStockTable > 0)
{
string queryExistenseStockProductTable = @"select count (*) from _stock_product where stock_id=" + StockID + " and pid=" + productID + " ";
object objTemp2 = BusinessObjects.SalesReturn.getScalar(con, queryExistenseStockProductTable);
if (objTemp2 != null)
{
existenceStockProductTable = (int)objTemp;
}
if (existenceStockProductTable > 0)
{
//update the stoc_product table
string updateQuerySP1 = @"update _stock_product set quantity=quantity+" + Quanityt + " where pid=" +
ProdID + " and stock_id =" + StockID + "";
salesReturnObject.ExecuteNonQuery(updateQuerySP1, con);
}
else
{
//add to the stockProduct table
decimal cost = 0;
string selectQuerySotkcProductDetails = @"select cost from purchase_product where stock_id=" +
StockID + " and pid=" + productID + " ";
object objTemp3 = BusinessObjects.SalesReturn.getScalar(con, selectQuerySotkcProductDetails);
if (objTemp3 != null)
{
cost = (decimal)objTemp3;
}
string stockProductInsert = @"insert _stock_product (pid,quantity,stock_id,price) values(" + ProdID
+ "," + Quanityt + "," + StockID + ",'" + cost + "') ";
salesReturnObject.ExecuteNonQuery(stockProductInsert, con);
}
}
else
{
//add to stock
Purchase pm = new Purchase();
pm = pm.GetPurchaseAndPProdBySID(con, StockID);
string queryGetSupID = @"select Id from Supplier where Name='" + pm.sup_name + "'";
int supID = 1;
object objTemp4 = BusinessObjects.SalesReturn.getScalar(con, queryGetSupID);
if (objTemp4 != null)
{
supID = (int)objTemp4;
}
string o = "(return)";
string insertStock = @"insert _Stokc (sup_name,s_date,invoice_no,Sup_id) values('" + pm.sup_name + o
+ "','" + pm.s_date.ToString("G", DateTimeFormatInfo.InvariantInfo) + "','" + pm.invoice_no
+ "'," + supID + ") ";
salesReturnObject.ExecuteNonQuery(insertStock, con);
//getting last inserted ID
string queryx = @"select IDENT_CURRENT('_Stokc')";
int lastInsertedID = Convert.ToInt32(Sales_BM.getScalar(con, queryx));
//adding stock product
stock_product sp = new stock_product();
sp.price = pm.StockProductList.Find(prod => prod.pid == productID).cost;
sp.quantity = Quanityt;
sp.stock_id = lastInsertedID;
sp.pid = ProdID;
sp.Add(con);
}
}
private void btnupdate_Click(object sender, EventArgs e)
{
try
{
if (txtPName.Text == "" || txtPName.Text == string.Empty || txtDesc.Text == string.Empty ||
txtQuantity.Text == string.Empty || txtAdjustedQuantity.Text == string.Empty)
{
MessageBox.Show("Please enter valid data!");
return;
}
if (chkStockReturn.Checked)
{
if (Convert.ToInt32(txtQuantity.Text) <= quantity)
{
//if so reduce from the current stock and enter data to the database to stockreturn table
int amount = Convert.ToInt32(txtQuantity.Text);
stock_product stock = new stock_product();
stock.stock_id = stock_id;
stock.pid = ProdID;
stock.quantity = Convert.ToInt32(txtAdjustedQuantity.Text) - amount;
if (stock.Update(con))
{
StockReturn_BS sr = new StockReturn_BS();
sr.prd_id = ProdID;
sr.stock_id = stock_id;
sr.quantity = amount;
sr.descrip = txtDesc.Text;
sr.adjusted = 1;
if (sr.Add(con))
{
MessageBox.Show("Stock Updated Sucessfully");
}
else
{
MetroMessageBox.Show(this, "System Error ", "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MetroMessageBox.Show(this, "System Error ", "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Invalid Quantity!");
return;
}
}
else
{
//if so increase from the current stock and enter data to the database to stockreturn table
int amount = Convert.ToInt32(txtQuantity.Text);
stock_product stock = new stock_product();
stock.stock_id = stock_id;
stock.pid = ProdID;
stock.quantity = Convert.ToInt32(txtAdjustedQuantity.Text) + amount;
if (stock.Update(con))
{
StockReturn_BS sr = new StockReturn_BS();
sr.prd_id = ProdID;
sr.stock_id = stock_id;
sr.quantity = amount;
sr.descrip = txtDesc.Text;
sr.adjusted = 0;
if (sr.Add(con))
{
MessageBox.Show("Stock Updated Sucessfully");
}
else
{
MetroMessageBox.Show(this, "System Error, ! Please contact administrator for more information. ", "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MetroMessageBox.Show(this, "System Error ", "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, "System Error " + ex.Message, "System Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public ActionResult Store(int id, int[] check, int[] all, int[] quantity)
{
int[] abc = check;
int[] cdf = all;
int[] qwe = quantity;
WebApplication2.Models.product oneUser = db.products.Find(id);
ViewBag.UserName = oneUser.name;
List <quantity_stockModel> qs_Model = new List <quantity_stockModel>();
foreach (int StockId in cdf)
{
quantity_stockModel qs = new quantity_stockModel();
qs.stockID = StockId;
qs.quantity = qwe[0];
qwe = qwe.Where(t => t != qwe[0]).ToArray();
qs_Model.Add(qs);
}
if (abc != null)
{
foreach (int StockId in all)
{
if (check.Contains(StockId))
{
if (db.stock_product.Where(t => t.stockId == StockId && t.productID == id).FirstOrDefault() == null)
{
stock_product StPr = new stock_product();
StPr.productID = id;
StPr.stockId = StockId;
quantity_stockModel qs = (from model in qs_Model where model.stockID == StockId select model).FirstOrDefault();
StPr.quantity = qs.quantity;
StPr.timeStamp = DateTime.Now.AddDays(30).ToString("yyyyMMdd");
db.stock_product.Add(StPr);
db.SaveChanges();
}
}
else
{
stock_product roleUser = (from t in db.stock_product where t.productID == id && t.stockId == StockId select t).FirstOrDefault();
if (roleUser != null)
{
db.stock_product.Remove(roleUser);
db.SaveChanges();
}
}
}
}
else
{
foreach (int StockId in all)
{
stock_product roleUser = (from t in db.stock_product where t.productID == id && t.stockId == StockId select t).FirstOrDefault();
if (roleUser != null)
{
db.stock_product.Remove(roleUser);
db.SaveChanges();
}
}
}
List <stock> li = db.stocks.ToList();
List <int> checkItem = new List <int>();
List <int> checkQuantity = new List <int>();
foreach (var item in li)
{
var isInItem = (from ur in db.stock_product where ur.productID == id && ur.stockId == item.id select ur).FirstOrDefault();
if (isInItem != null)
{
checkItem.Add(item.id);
checkQuantity.Add(isInItem.quantity);
}
}
ViewBag.CheckList = checkItem;
ViewBag.CheckQuantity = checkQuantity;
return(View(li));
}
private void tblProduct_CellClick(object sender, DataGridViewCellEventArgs e)
{
try
{
int rw = e.RowIndex;
int cl = e.ColumnIndex;
if (cl == 6)
{
foreach (Form form in Application.OpenForms)
{
if (form.GetType().Name == "StockReturn")
{
MetroMessageBox.Show(this, "Form is already opened", "System Message!", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
return;
}
}
if (Convert.ToString(tblProduct.Rows[rw].Cells[0].Value) == string.Empty)
{
MessageBox.Show("No Stock data found");
return;
}
int sid = Convert.ToInt32(tblProduct.Rows[rw].Cells[0].Value.ToString());
//getting relevant product list of the selected stock
stock_product stpr = new stock_product();
stpr.stock_id = sid;
stock_product_list = stpr.Get_stock_by_sid(con);
if (stock_product_list.Count == 0)
{
MetroMessageBox.Show(this, "No products were found for this purchase data ", "System Message!", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
return;
}
foreach (var productlist in ProductList)
{
foreach (var PSlist in stock_product_list)
{
if (productlist.ID == PSlist.pid)
{
PSlist.p_name = productlist.name;
}
}
}
//MessageBox.Show(sid.ToString());
using (StockReturn sr = new StockReturn(this, sid))
{
sr.ShowDialog();
}
}
else if (cl == 5)
{
if (Convert.ToString(tblProduct.Rows[rw].Cells[0].Value) == string.Empty)
{
MessageBox.Show("No Stock data found");
return;
}
if (Convert.ToString(tblProduct.Rows[rw].Cells[0].Value) != string.Empty)
{
txtStockID.Text = tblProduct.Rows[rw].Cells[0].Value.ToString();
}
if (Convert.ToString(tblProduct.Rows[rw].Cells[2].Value) != string.Empty)
{
txtSupName.Text = tblProduct.Rows[rw].Cells[2].Value.ToString();
}
if (Convert.ToString(tblProduct.Rows[rw].Cells[1].Value) != string.Empty)
{
txtInvoiceNo.Text = tblProduct.Rows[rw].Cells[1].Value.ToString();
}
if (Convert.ToString(tblProduct.Rows[rw].Cells[3].Value) != string.Empty)
{
cmbDate.Text = tblProduct.Rows[rw].Cells[3].Value.ToString();
}
enableContent();
}
else if (cl == 2)
{
if (Convert.ToString(tblProduct.Rows[rw].Cells[0].Value) == string.Empty)
{
MessageBox.Show("No Stock data found");
return;
}
foreach (Form form in Application.OpenForms)
{
if (form.GetType().Name == "V_Supplier")
{
MetroMessageBox.Show(this, "Form is already opened", "System Message!", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
return;
}
}
int stid = Convert.ToInt32(tblProduct.Rows[rw].Cells[0].Value.ToString());
using (V_Supplier ms = new V_Supplier(stid))
{
ms.ShowDialog();
}
}
}
catch (Exception ex)
{
MetroMessageBox.Show(this, "System Error " + ex.Message, "System Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
