public void createSupplierTransaction(string stockCardID, string id)
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
//Update into database
string getItems = @" select iss.StockCardID, iss.IncomingQty+scDetails.Balance as Balance, iss.IncomingQty, s.SupplierName
from IncomingStock iss, StockCard sc, StockCardDetails scDetails, Supplier s
where iss.StockCardID=" + stockCardID + " and sc.ItemID='" + id + "' and iss.StockCardID=sc.StockCardID and" +
" s.SupplierID = iss.SupplierID and " +
" sc.StockCardID = scDetails.StockCardID and iss.IncomingStockStatus='pending' 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())
{
IncomingCode ic = new IncomingCode();
ic.StockCardID = (string)reader["StockCardID"];
ic.Balance = (int)reader["Balance"];
ic.IncomingQty = (int)reader["IncomingQty"];
ic.SupplierName = (string)reader["SupplierName"];
addIntoStockCard(ic);
}
}
}
public List <IncomingCode> showSupplierTransaction(string ItemID)
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
//only for display
string displayItems = @"select s.SupplierName, iss.IncomingQty, scDetails.Balance, sc.StockCardID
from Supplier s, IncomingStock iss, StockCardDetails scDetails, StockCard sc
where sc.StockCardID = scDetails.StockCardID and iss.StockCardID = sc.StockCardID and iss.SupplierID = s.SupplierID
and sc.ItemID='" + ItemID + "'";
SqlCommand cmd1 = new SqlCommand(displayItems, connection);
SqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
IncomingCode ic = new IncomingCode();
ic.SupplierName = (string)reader["SupplierName"];
ic.IncomingQty = (int)reader["IncomingQty"];
ic.Balance = (int)reader["Balance"];
ic.StockCardID = (string)reader["StockCardID"];
incomingList.Add(ic);
}
}
return(incomingList);
}
public void addIntoStockCard(IncomingCode ic)
{
string isStatus = "delivered";
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
string getItems = @"insert into StockCardDetails( StockCardID, Balance, IncomingQty, SupplierName) values('" + ic.StockCardID + "'," + ic.Balance + "," + ic.IncomingQty + ",'" + ic.SupplierName + "')";
string updateItems = @"Update IncomingStock Set IncomingStockStatus= '" + isStatus + "' WHERE StockCardID ='" + ic.StockCardID + "'";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlCommand cmd1 = new SqlCommand(updateItems, connection);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
}
}
public void createSupplierTransaction(string ItemID)
{
using (SqlConnection connection = new SqlConnection(DataLink.connectionString))
{
connection.Open();
//Update into database
string getItems = @"select s.SupplierName, iss.IncomingQty+scDetail.Balance as Balance, sc.StockCardID
from Supplier s, IncomingStock iss, StockCard sc, PurchaseOrder po, StockCardDetails scDetail
where sc.StockCardID = iss.StockCardID and s.SupplierID = iss.SupplierID and po.SupplierID = s.SupplierID
and po.PurchaseOrderStatus= 1 and iss.IncomingStockStatus='pending' and po.ItemID='" + ItemID + "'";
SqlCommand cmd = new SqlCommand(getItems, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
IncomingCode ic = new IncomingCode();
ic.SupplierName = (string)reader["SupplierName"];
ic.Balance = (int)reader["Balance"];
ic.StockCardID = (string)reader["StockCardID"];
addIntoStockCard(ic);
}
}
}