public void updateRefNoWiseGTNDetailInStock(double Quant, int refNo, gtndetail gtnd, gtnheader gtnh)
{
//Boolean status = true;
string updateSQL = "";
string utString = "";
try
{
updateSQL = "update Stock set " +
" PresentStock=" + "( (select PresentStock from Stock where RowID = " + refNo + ")-" + Quant + ")" +
" where RowID=" + refNo;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "update Stock set " +
" IssueQuantity=" + "( (select isnull(IssueQuantity,0) from Stock where RowID = " + refNo + ")+" + Quant + ")" +
" where RowID=" + refNo;
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "insert into Stock " +
"(FYID,StockItemID,ModelNo,InwardDocumentID,InwardDocumentNo,InwardDocumentDate,InwardQuantity,PresentStock,MRNNo,MRNDate," +
"BatchNo,ExpiryDate,SerialNo,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,StoreLocation," +
"CreateTime,CreateUser)" +
" values (" +
"'" + Main.currentFY + "'," +
"'" + gtnd.StockitemID + "'," +
"'" + gtnd.ModelNo + "'," +
"'" + "GTN" + "'," +
gtnh.GTNNo + "," +
"'" + gtnh.GTNDate.ToString("yyyy-MM-dd") + "'," +
gtnd.Quantity + "," +
gtnd.Quantity + "," +
gtnd.MRNNo + "," +
"'" + gtnd.MRNDate.ToString("yyyy-MM-dd") + "'," +
"'" + gtnd.BatchNo + "'," +
"'" + gtnd.ExpiryDate.ToString("yyyy-MM-dd") + "'," +
"'" + gtnd.SerialNo + "'," +
gtnd.PurchaseQuantity + "," +
gtnd.PurchasePrice + "," +
gtnd.PurchaseTax + "," +
"'" + gtnd.SupplierID + "'," +
"'" + gtnh.ToLocation + "'," +
"GETDATE()" + "," +
"'" + Login.userLoggedIn + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "Stock", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
//status = false;
MessageBox.Show("failed to Update In Reference Number Wise GTNDetail in stock");
return;
}
}
catch (Exception ex)
{
MessageBox.Show("failed to Update In Reference Number Wise SIDetail in stock");
return;
}
//return status;
}
//---
public static List <gtndetail> getGTNDetail(gtnheader gtnh)
{
gtndetail gtnd;
List <gtndetail> GTNDetail = new List <gtndetail>();
try
{
string query = "";
SqlConnection conn = new SqlConnection(Login.connString);
query = "select RowID,DocumentID,TemporaryNo, TemporaryDate,StockItemID,StockItemName,ModelNo, ModelName,Quantity, " +
"MRNNo,MRNDate,BatchNo,SerialNo,ExpiryDate,PurchaseQuantity,PurchasePrice,PurchaseTax,SupplierID,SupplierName,StockReferenceNo " +
"from ViewGTNDetail " +
"where DocumentID='" + gtnh.DocumentID + "'" +
" and TemporaryNo=" + gtnh.TemporaryNo +
" and TemporaryDate='" + gtnh.TemporaryDate.ToString("yyyy-MM-dd") + "'" +
" order by StockItemID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
gtnd = new gtndetail();
gtnd.RowID = reader.GetInt32(0);
gtnd.DocumentID = reader.GetString(1);
gtnd.TemporaryNo = reader.GetInt32(2);
gtnd.TemporaryDate = reader.GetDateTime(3).Date;
gtnd.StockitemID = reader.GetString(4);
gtnd.StockItemName = reader.GetString(5);
gtnd.ModelNo = reader.IsDBNull(6)?"NA":reader.GetString(6);
gtnd.ModelName = reader.IsDBNull(7) ? "NA":reader.GetString(7);
gtnd.Quantity = reader.GetDouble(8);
gtnd.MRNNo = reader.IsDBNull(9)? 0:reader.GetInt32(9);
if (reader.IsDBNull(10))
{
gtnd.MRNDate = DateTime.Parse("01-01-1900");
}
else
{
gtnd.MRNDate = reader.GetDateTime(10).Date;
}
gtnd.BatchNo = reader.IsDBNull(11) ? "NA" : reader.GetString(11);
gtnd.SerialNo = reader.IsDBNull(12) ? "NA" : reader.GetString(12);
if (reader.IsDBNull(13))
{
gtnd.ExpiryDate = DateTime.Parse("01-01-1900");
}
else
{
gtnd.ExpiryDate = reader.GetDateTime(13);
}
gtnd.PurchaseQuantity = reader.GetDouble(14);
gtnd.PurchasePrice = reader.GetDouble(15);
gtnd.PurchaseTax = reader.GetDouble(16);
gtnd.SupplierID = reader.IsDBNull(17)? "NA" : reader.GetString(17);
gtnd.SupplierName = reader.IsDBNull(18)?"NA" : reader.GetString(17);
if (reader.IsDBNull(13))
{
gtnd.refNo = 0;
}
else
{
gtnd.refNo = reader.GetInt32(19);
}
GTNDetail.Add(gtnd);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying GTN Details");
}
return(GTNDetail);
}