public StockCradDetails createStockCardDetail(string ItemID) { StockCradDetails sc = new StockCradDetails(); using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select s.ItemID, s.ItemName, s.UOM, c.CategoryName from Stationery s, Category c where s.CategoryID = c.CategoryID and s.ItemID='" + ItemID + "'" + "group by s.ItemID, s.ItemName, s.UOM, c.CategoryName"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { sc.ItemID = (string)reader["ItemID"]; sc.ItemName = (string)reader["ItemName"]; sc.UOM = (string)reader["UOM"]; sc.CategoryName = (string)reader["CategoryName"]; //scDetailList.Add(sc); } } return(sc); }
public void createDisbursementTransaction(string stockCardID, string ItemID) { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select dd.DisbursementDetailsID, scDetails.StockCardID,dd.DisbursementID, scDetails.Balance-dd.DeliveredQty as Balance, d.DepartmentName, dd.DeliveredQty from DisbursementDetails dd, StockCardDetails scDetails, Department d where dd.DisbursementDetailsStatus ='pending'and dd.ItemID = '" + ItemID + "' and scDetails.StockCardID ='" + stockCardID + "'" + "and d.DepartmentID = dd.DepID and scDetails.Balance in(select top 1 scD.Balance"+ " from StockCardDetails scD" + " where scD.StockCardID='" + stockCardID + "' order by scD.StockCardDetailsID desc)"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { StockCradDetails sc = new StockCradDetails(); sc.DisbursementDetailsID = (int)reader["DisbursementDetailsID"]; sc.StockCardID = (string)reader["StockCardID"]; sc.DisbursementID = (int)reader["DisbursementID"]; sc.Balance = (int)reader["Balance"]; sc.Departmentname = (string)reader["Departmentname"]; sc.DeliveredQty = (int)reader["DeliveredQty"]; addDisTranIntoStockCard(sc); } } }
public List <StockCradDetails> showDisbursementTransaction(string stockCardID, string ItemID) { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select d.Departmentname, scDetails.Balance, dd.DeliveredQty from StockCardDetails scDetails, DisbursementDetails dd, DisbursementList dl, Department d where scDetails.DisbursementID = dd.DisbursementID and dl.DisbursementID= dd.DisbursementID and dl.DepID = d.DepartmentID and dd.ItemID='" + ItemID + "' and scDetails.StockCardID='" + stockCardID + "'"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { StockCradDetails sc = new StockCradDetails(); sc.Departmentname = (string)reader["Departmentname"]; sc.DeliveredQty = (int)reader["DeliveredQty"]; sc.Balance = (int)reader["Balance"]; scDetailList.Add(sc); } } return(scDetailList); }
public ActionResult StockCardDetail(string stockCardID, string id) { StockCradDetails scDetails = sc.createStockCardDetail(id); sc.createDisbursementTransaction(stockCardID, id); // List<StockCradDetails> scDisTran = sc.showDisbursementTransaction(stockCardID, id); sc.createSupplierTransaction(stockCardID, id); List <IncomingCode> incomingTran = sc.showSupplierTransaction(stockCardID); int balance = sc.getLatestBalance(stockCardID, id); int reorderLevel = sc.getReorderLevel(id); if (balance <= reorderLevel) { Users u = ds.GetUserInfo((int)Session["UserID"]); string EmailID = u.EmailID; SendEmailNotification send = new SendEmailNotification(); String EmailSubject = "Reorder level low at ItemID#" + id; String EmailBody = "<p> Dear SuSu,</p>"; EmailBody += "<p>Your reorder level is low in ItemID" + id + ". For your reference.</p>"; EmailBody += "<p>Thank you<br/>Logic University Staionery Store</p>"; EmailBody += "<p> Please do not reply to this email it is auto-generated.</p>"; send.SendEmailHTML("*****@*****.**", EmailSubject, EmailBody); } ViewBag.stockCardDetail = scDetails; // ViewBag.scDisbursementTran = scDisTran; ViewBag.incomingTran = incomingTran; return(View()); }
public void createDisbursementTransaction(string ItemID) { using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"select dl.DisbursementID, scDetails.Balance- dd.DeliveredQty as Balance, d.Departmentname from DisbursementDetails dd, DisbursementList dl, Department d, StockCardDetails scDetails where dl.DisbursementID = dd.DisbursementID and d.DepartmentID= dd.DepID and dl.DisbursementStatus = 'delivered' and dd.DisbursementDetailsStatus ='pending'and dd.ItemID = '" + ItemID + "' and " + "scDetails.Balance in(select scDetails.Balance from StockCardDetails scDetails, StockCard sc " + "where sc.StockCardID = scDetails.StockCardID and sc.ItemID = '" + ItemID + "')"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { StockCradDetails sc = new StockCradDetails(); sc.DisbursementID = (int)reader["DisbursementID"]; sc.Balance = (int)reader["Balance"]; sc.Departmentname = (string)reader["Departmentname"]; addDisTranIntoStockCard(sc); } } }
public void addDisTranIntoStockCard(StockCradDetails sc) { string isStatus = "delivered"; using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"insert into StockCardDetails( StockCardID, DisbursementID, Balance, IncomingQty, SupplierName) values('" + sc.StockCardID + "','" + sc.DisbursementID + "'," + sc.Balance + "," + sc.DeliveredQty + ",'" + sc.Departmentname + "')"; string updateItems = @"Update DisbursementDetails Set DisbursementDetailsStatus= '" + isStatus + "' WHERE DisbursementDetailsID ='" + sc.DisbursementDetailsID + "'"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlCommand cmd1 = new SqlCommand(updateItems, connection); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); } }
public void addDisTranIntoStockCard(StockCradDetails sc) { System.Guid scDetailsID = System.Guid.NewGuid(); string isStatus = "delivered"; using (SqlConnection connection = new SqlConnection(DataLink.connectionString)) { connection.Open(); string getItems = @"insert into StockCardDetails(StockCardDetailsID, DisbursementID, Balance) values('" + scDetailsID + "','" + sc.DisbursementID + "'," + sc.Balance + ")"; string updateItems = @"Update DisbursementDetails Set DisbursementDetailsStatus= '" + isStatus + "' WHERE DisbursementID ='" + sc.DisbursementID + "'"; SqlCommand cmd = new SqlCommand(getItems, connection); SqlCommand cmd1 = new SqlCommand(updateItems, connection); cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); } }