public bool UpdateSalesVoucher(eSunSpeedDomain.TransSalesModel objSales)
{
string Query = string.Empty;
bool isUpdated = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Series", objSales.Series));
paramCollection.Add(new DBParameter("@SaleDate", objSales.SaleDate));
paramCollection.Add(new DBParameter("@VoucherNumber", objSales.VoucherNumber));
paramCollection.Add(new DBParameter("@SalesType", objSales.SalesType));
paramCollection.Add(new DBParameter("@Party", objSales.Party));
paramCollection.Add(new DBParameter("@MatCentre", objSales.MatCentre));
paramCollection.Add(new DBParameter("@Narration", objSales.Narration));
paramCollection.Add(new DBParameter("@TotalQty", objSales.TotalQty, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalAmount", objSales.TotalAmount, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSales.BSTotalAmount, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@ModifiedBy", objSales.ModifiedBy));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now));
paramCollection.Add(new DBParameter("@SalesVoucher_ID", objSales.Trans_Sales_Id));
Query = "UPDATE Trans_Sales SET [Series]=@Series,[SaleDate]=@SaleDate," +
"[VoucherNumber]=@VoucherNumber,[SalesType]=@SalesType," +
"[Party]=@Party,[MatCentre]=@MatCentre," +
"[Narration]=@Narration,[TotalQty]=@TotalQty," +
"[TotalAmount]=@TotalAmount,[BSTotalAmount]=@BSTotalAmount," +
"[ModifiedBy]=@ModifiedBy,[ModifiedDate]=@ModifiedDate " +
"WHERE Trans_Sales_Id=@SalesVoucher_ID;";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
UpdateItemandBS(objSales);
isUpdated = true;
}
}
catch (Exception ex)
{
isUpdated = false;
throw ex;
}
return(isUpdated);
}
public List <TransSalesModel> GetAllSalesVouchers()
{
List <TransSalesModel> lstSalesVouchers = new List <TransSalesModel>();
TransSalesModel objsales;
string Query = "SELECT * FROM Trans_Sales";
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objsales = new eSunSpeedDomain.TransSalesModel();
objsales.Trans_Sales_Id = DataFormat.GetInteger(dr["Trans_Sales_Id"]);
objsales.Series = dr["Series"].ToString();
objsales.SaleDate = DataFormat.GetDateTime(dr["SaleDate"]);
objsales.VoucherNumber = DataFormat.GetInteger(dr["VoucherNumber"]);
objsales.SalesType = dr["SalesType"].ToString();
objsales.Party = dr["Party"].ToString();
objsales.MatCentre = dr["MatCentre"].ToString();
objsales.Narration = dr["Narration"].ToString();
objsales.TotalQty = Convert.ToDecimal(dr["TotalQty"]);
objsales.TotalAmount = Convert.ToDecimal(dr["TotalAmount"]);
objsales.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
//SELECT Sales Items
string itemQuery = "SELECT * FROM Trans_Sales_Item WHERE TransSalesId=" + objsales.Trans_Sales_Id;
System.Data.IDataReader drItem = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objsales.SalesItem_Voucher = new List <Item_VoucherModel>();
Item_VoucherModel objItemModel;
while (drItem.Read())
{
objItemModel = new Item_VoucherModel();
objItemModel.ParentId = DataFormat.GetInteger(drItem["TransSalesId"]);
objItemModel.Item_ID = DataFormat.GetInteger(drItem["ItemId"]);
objItemModel.Item = drItem["Item"].ToString();
objItemModel.Price = Convert.ToDecimal(drItem["Price"]);
objItemModel.Qty = Convert.ToDecimal(drItem["Qty"]);
objItemModel.Unit = drItem["Unit"].ToString();
objItemModel.Amount = Convert.ToDecimal(drItem["Amount"]);
objItemModel.TotalQty = Convert.ToDecimal(drItem["TotalQty"]);
objItemModel.TotalAmount = Convert.ToDecimal(drItem["TotalAmount"]);
objsales.SalesItem_Voucher.Add(objItemModel);
}
//SELECT Bill Sundry Voucher items
string bsQuery = "SELECT * FROM Trans_Sales_BS WHERE TransSalesId=" + objsales.Trans_Sales_Id;
System.Data.IDataReader drBS = _dbHelper.ExecuteDataReader(bsQuery, _dbHelper.GetConnObject());
objsales.SalesBillSundry_Voucher = new List <BillSundry_VoucherModel>();
BillSundry_VoucherModel objBSModel;
while (drBS.Read())
{
objBSModel = new BillSundry_VoucherModel();
objBSModel.ParentId = DataFormat.GetInteger(drBS["TransSalesId"]);
objBSModel.BSId = DataFormat.GetInteger(drBS["BSId"]);
objBSModel.BillSundry = drBS["BillSundry"].ToString();
objBSModel.Percentage = Convert.ToDecimal(drBS["Percentage"]);
objBSModel.Amount = Convert.ToDecimal(drBS["Amount"]);
objBSModel.TotalAmount = Convert.ToDecimal(drBS["TotalAmount"]);
objsales.SalesBillSundry_Voucher.Add(objBSModel);
}
lstSalesVouchers.Add(objsales);
}
return(lstSalesVouchers);
}
//Update Sales Voucher
public bool UpdateSalesVoucherMaster(eSunSpeedDomain.TransSalesModel objSales)
{
string Query = string.Empty;
bool isUpdated = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherType", objSales.VoucherType));
paramCollection.Add(new DBParameter("@SaleDate", objSales.SaleDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Terms", objSales.Terms));
paramCollection.Add(new DBParameter("@VoucherNumber", objSales.VoucherNumber));
paramCollection.Add(new DBParameter("@BillNumber", objSales.BillNo));
paramCollection.Add(new DBParameter("@LedgerId", objSales.LedgerId));
paramCollection.Add(new DBParameter("@SalesType", objSales.SalesType));
paramCollection.Add(new DBParameter("@MatCentre", objSales.MatCentre));
paramCollection.Add(new DBParameter("@Narration", objSales.Narration));
paramCollection.Add(new DBParameter("@TotalAmount", objSales.TotalAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalQty", objSales.TotalQty, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalFree", objSales.TotalFree, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalBasicAmount", objSales.TotalBasicAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalDisAmount", objSales.TotalDisAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalTaxAmount", objSales.TotalTaxAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSales.BSTotalAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@PriceList", objSales.PriceList));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@Trans_Sales_Id", objSales.Trans_Sales_Id));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spUpdateSalesVoucherMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
//Update Sale Item Details
foreach (Item_VoucherModel item in objSales.SalesItem_Voucher)
{
item.ParentId = objSales.Trans_Sales_Id;
if (item.Item_ID > 0)
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Trans_Sales_Id", item.ParentId));
paramCollection.Add(new DBParameter("@ChalidId", item.Item_ID));
paramCollection.Add(new DBParameter("@ItemMastid", item.ITM_Id));
paramCollection.Add(new DBParameter("@LedgerId", item.LedgerId));
paramCollection.Add(new DBParameter("@Qty", item.Qty, DbType.Decimal));
paramCollection.Add(new DBParameter("@Unit", item.Unit));
paramCollection.Add(new DBParameter("@Per", item.Per));
paramCollection.Add(new DBParameter("@Price", item.Price, DbType.Decimal));
paramCollection.Add(new DBParameter("@Batch", item.Batch));
paramCollection.Add(new DBParameter("@Free", item.Free, DbType.Decimal));
paramCollection.Add(new DBParameter("@BasicAmt", item.BasicAmt, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountPercentage", item.DiscountPercentage, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountAmount", item.DiscountAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TaxAmount", item.TaxAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@Amount", item.Amount, DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
System.Data.IDataReader Idr =
_dbHelper.ExecuteDataReader("spUpdateSalesVoucherItemDetails", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
else
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Trans_Sales_Id", item.ParentId));
paramCollection.Add(new DBParameter("@Itemid", item.ITM_Id));
paramCollection.Add(new DBParameter("@LedgerId", item.LedgerId));
paramCollection.Add(new DBParameter("@Qty", item.Qty, DbType.Decimal));
paramCollection.Add(new DBParameter("@Unit", item.Unit));
paramCollection.Add(new DBParameter("@Per", item.Per));
paramCollection.Add(new DBParameter("@Price", item.Price, DbType.Decimal));
paramCollection.Add(new DBParameter("@Batch", item.Batch));
paramCollection.Add(new DBParameter("@Free", item.Free, DbType.Decimal));
paramCollection.Add(new DBParameter("@BasicAmt", item.BasicAmt, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountPercentage", item.DiscountPercentage, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountAmount", item.DiscountAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TaxAmount", item.TaxAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@Amount", item.Amount, DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
System.Data.IDataReader Idr =
_dbHelper.ExecuteDataReader("spInsertSalesVoucherItemDetails", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
}
//Update Sales Bill Sundary
foreach (BillSundry_VoucherModel bs in objSales.SalesBillSundry_Voucher)
{
bs.ParentId = objSales.Trans_Sales_Id;
if (bs.BSId > 0)
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Trans_Sales_Id", bs.ParentId));
paramCollection.Add(new DBParameter("@BS_Id", bs.BSId));
paramCollection.Add(new DBParameter("@BSMastID", bs.BS_Id));
paramCollection.Add(new DBParameter("@Percentage", bs.Percentage));
paramCollection.Add(new DBParameter("@Extra", bs.Extra));
paramCollection.Add(new DBParameter("@Amount", bs.Amount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, DbType.DateTime));
System.Data.IDataReader drbs =
_dbHelper.ExecuteDataReader("spUpdateSalesVoucherBS", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
else
{
paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Trans_Sales_Id", bs.ParentId));
paramCollection.Add(new DBParameter("@BSId", bs.BS_Id));
paramCollection.Add(new DBParameter("@Percentage", bs.Percentage));
paramCollection.Add(new DBParameter("@Extra", bs.Extra));
paramCollection.Add(new DBParameter("@Amount", bs.Amount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, DbType.DateTime));
System.Data.IDataReader drbs =
_dbHelper.ExecuteDataReader("spInsertSalesVoucherBS", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
}
}
catch (Exception ex)
{
isUpdated = false;
//throw ex;
}
return(isUpdated);
}