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