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();
}
}
