public List <PhysicalStockModel> GetPhysicalStockbyId(int id)
{
List <PhysicalStockModel> lstStock = new List <PhysicalStockModel>();
PhysicalStockModel objstock;
string Query = "SELECT * FROM Physical_Stock_Voucher WHERE Physical_Id=" + id;
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objstock = new PhysicalStockModel();
objstock.Phycial_Id = DataFormat.GetInteger(dr["Physical_ID"]);
objstock.Voucher_Number = DataFormat.GetInteger(dr["VoucherNo"]);
objstock.PSDate = DataFormat.GetDateTime(dr["Physical_Date"]);
objstock.MatCenter = dr["MatCenter"].ToString();
objstock.Narration = dr["Narration"].ToString();
objstock.SVSeries = DataFormat.GetInteger(dr["Journal Series"]);
objstock.StockJournalVoucher = DataFormat.GetBoolean(dr["Journal Voucher"]);
objstock.InputSubDetails = DataFormat.GetBoolean(dr["Input Sub"]);
objstock.ScannBarcode = DataFormat.GetBoolean(dr["Scann Barcode"]);
objstock.UpdateVoucher = DataFormat.GetBoolean(dr["Update Journal Narration"]);
objstock.Items = DataFormat.GetBoolean(dr["Items"]);
objstock.BCN = DataFormat.GetBoolean(dr["BCN"]);
objstock.SerialNo = DataFormat.GetBoolean(dr["SerialNo"]);
objstock.Batch = DataFormat.GetBoolean(dr["Batch"]);
string itemQuery = "SELECT * FROM Physical_Stock_Items WHERE Physical_Id=" + objstock.Phycial_Id;
System.Data.IDataReader drAcc = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objstock.StockItemsModel = new List <StockItemsModel>();
StockItemsModel obphy;
while (drAcc.Read())
{
obphy = new StockItemsModel();
obphy.item_Id = Convert.ToInt32(drAcc["Item_Id"]);
obphy.Parent_Id = DataFormat.GetInteger(drAcc["Physical_Id"]);
obphy.item = drAcc["Item"].ToString();
obphy.Pstock = Convert.ToDecimal(drAcc["Physical Stock"]);
obphy.Bstock = Convert.ToDecimal(drAcc["Book Stock"]);
obphy.Difference = Convert.ToDecimal(drAcc["Difference"]);
objstock.StockItemsModel.Add(obphy);
}
lstStock.Add(objstock);
}
return(lstStock);
}
public bool SavePhysicalStock(PhysicalStockModel objphysical)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNo", objphysical.Voucher_Number));
paramCollection.Add(new DBParameter("@Date", objphysical.PSDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Matcenter", objphysical.MatCenter));
paramCollection.Add(new DBParameter("@Narration", objphysical.Narration));
paramCollection.Add(new DBParameter("@Series", objphysical.SVSeries));
paramCollection.Add(new DBParameter("@JournalVoucher", objphysical.StockJournalVoucher, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@InputSub", objphysical.InputSubDetails, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@UpdateVoucher", objphysical.UpdateVoucher, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@ScannBarCode", objphysical.ScannBarcode, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@Items", objphysical.Items, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@BSN", objphysical.BCN, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@SerialNo", objphysical.SerialNo, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@Batch", objphysical.Batch, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
//paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now));
Query = "INSERT INTO Physical_Stock_Voucher ([VoucherNo],[Physical_Date],[MatCenter],[Narration],[Journal Series]," +
"[Journal Voucher],[Input Sub],[Scann Barcode],[Update Journal Narration],[Items],[BCN],[SerialNo],[Batch],[CreatedBy]) VALUES " +
"(@VoucherNo,@Date,@Matcenter,@Narration,@Series,@JournalVoucher,@InputSub,@UpdateVoucher,@ScannBarCode,@Items," +
" @BSN,@SerialNo,@Batch,@CreatedBy)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
SavePhysicalStockItems(objphysical.StockItemsModel);
isSaved = true;
}
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
//UPDATE Physical Stock Voucher
public bool UpdatePhysicalStock(PhysicalStockModel objphysical)
{
string Query = string.Empty;
bool isUpdated = true;
try
{
//UPDATE CREDIT NOTE TABLE - PARENT TABLE
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNo", objphysical.Voucher_Number));
paramCollection.Add(new DBParameter("@Date", objphysical.PSDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Matcenter", objphysical.MatCenter));
paramCollection.Add(new DBParameter("@Narration", objphysical.Narration));
paramCollection.Add(new DBParameter("@Series", objphysical.SVSeries));
paramCollection.Add(new DBParameter("@JournalVoucher", objphysical.StockJournalVoucher, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@InputSub", objphysical.InputSubDetails, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@ScannBarCode", objphysical.ScannBarcode, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@UpdateVoucher", objphysical.UpdateVoucher, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@Items", objphysical.Items, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@BSN", objphysical.BCN, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@SerialNo", objphysical.SerialNo, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@Batch", objphysical.Batch, System.Data.DbType.Boolean));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@id", objphysical.Phycial_Id));
Query = "UPDATE Physical_Stock_Voucher SET [VoucherNo]=@VoucherNo,[Physical_Date]=@Date,[MatCenter]=@Matcenter," +
"[Narration]=@Narration,[Journal Series]=@Series,[Journal Voucher]=@JournalVoucher,[Input Sub]=@InputSub,[Scann Barcode]=@ScannBarCode," +
"[Update Journal Narration]=@UpdateVoucher,[Items]=@Items,[BCN]=@BSN,[SerialNo]=@SerialNo,[Batch]=@Batch,[CreatedBy]=@CreatedBy " +
"WHERE Physical_Id=@id";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
List <StockItemsModel> lstAcct = new List <StockItemsModel>();
//UPDATE CREDIT NOTE ACCOUNT -CHILD TABLE UPDATES
foreach (StockItemsModel stock in objphysical.StockItemsModel)
{
if (stock.item_Id > 0)
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Item", (stock.item)));
paramCollection.Add(new DBParameter("@Pstock", stock.Pstock));
paramCollection.Add(new DBParameter("@Bstock", stock.Bstock));
paramCollection.Add(new DBParameter("@Difference", stock.Difference));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
paramCollection.Add(new DBParameter("@Item_ID", (stock.item_Id)));
Query = "UPDATE Physical_Stock_Items SET [Item]=@Item,[Physical Stock]=@Pstock,[Book Stock]=@Bstock,[Difference]=@Difference,[ModifiedBy]=@ModifiedBy " +
"WHERE [Item_Id]=Item_ID";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
isUpdated = true;
}
}
else
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@PS_ID", (objphysical.Phycial_Id)));
paramCollection.Add(new DBParameter("@Item", (stock.item)));
paramCollection.Add(new DBParameter("@Pstock", stock.Pstock));
paramCollection.Add(new DBParameter("@Bstock", stock.Bstock));
paramCollection.Add(new DBParameter("@Difference", stock.Difference));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
//paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now));
Query = "INSERT INTO Physical_Stock_Items ([Physical_Id],[Item],[Physical Stock],[Book Stock],[Difference],[CreatedBy])" +
" VALUES " +
"(@PS_ID,@Item,@Pstock,@Bstock,@Difference,@CreatedBy)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
}
;
}
}
}
}
catch (Exception ex)
{
isUpdated = false;
throw ex;
}
return(isUpdated);
}