public bool UpdatePurchaseReturnVoucher(PurchaseReturnVoucherModel objpurchaseReturn)
{
string Query = string.Empty;
bool isUpdated = false;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Series", objpurchaseReturn.Series));
paramCollection.Add(new DBParameter("@PurchaseType", objpurchaseReturn.PurchaseType));
paramCollection.Add(new DBParameter("@PurchaseDate", objpurchaseReturn.PR_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@VoucherNumber", objpurchaseReturn.Voucher_Number));
paramCollection.Add(new DBParameter("@BillNumber", objpurchaseReturn.BillNo));
paramCollection.Add(new DBParameter("@DueDate", objpurchaseReturn.DueDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Party", objpurchaseReturn.Party));
paramCollection.Add(new DBParameter("@MatCentre", objpurchaseReturn.MatCenter));
paramCollection.Add(new DBParameter("@Narration", objpurchaseReturn.Narration));
paramCollection.Add(new DBParameter("@TotalQty", objpurchaseReturn.TotalQty, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalAmount", objpurchaseReturn.TotalAmount, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@BSTotalAmount", objpurchaseReturn.BSTotalAmount, System.Data.DbType.Decimal));
paramCollection.Add(new DBParameter("@ModifiedBy", "Admin"));
//paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now));
paramCollection.Add(new DBParameter("@PurchaseVoucher_ID", objpurchaseReturn.PR_Id));
Query = "UPDATE Trans_PurchaseReturn SET [Series]=@Series,[PurchaseType]=@PurchaseType, " +
"[PR_Date]=@PurchaseDate,[VoucherNo] = @VoucherNumber,[BillNo]=@BillNumber,[DueDate]=@DueDate," +
"[Party]=@Party,[MatCenter]=@MatCentre," +
"[Narration]=@Narration,[TotalQty]=@TotalQty," +
"[TotalAmount]=@TotalAmount,[BSTotalAmount]=@BSTotalAmount," +
"[ModifiedBy]=@ModifiedBy " +
"WHERE TransPRId=@PurchaseVoucher_ID;";
//Query = "UPDATE Trans_PurchaseReturn SET [Series]=@Series,[PurchaseType]=@PurchaseType,[PR_Date]=@PurchaseDate," +
// "[VoucherNo]=@VoucherNumber,[BillNo]=@BillNumber,DueDate=@DueDate," +
// "[Party]=@Party,[MatCenter]=@MatCentre," +
// "[Narration]=@Narration,[TotalQty]=@TotalQty," +
// "[TotalAmount]=@TotalAmount,[BSTotalAmount]=@BSTotalAmount," +
// "[ModifiedBy]=@ModifiedBy " +
// "WHERE TransPRId=@PurchaseVoucher_ID;";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
UpdatePurchaseReturnItemandBS(objpurchaseReturn);
isUpdated = true;
}
}
catch (Exception ex)
{
isUpdated = false;
throw ex;
}
return(isUpdated);
}
//This BL Is Used in Purchase Voucher Return
#region SAVE PURCHASE Return VOUCHER
public bool SavePurcahseReturnVoucher(PurchaseReturnVoucherModel objPurcRet)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherType", objPurcRet.VoucherType));
paramCollection.Add(new DBParameter("@PurcRetDate", objPurcRet.PR_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Terms", objPurcRet.Terms));
paramCollection.Add(new DBParameter("@VoucherNumber", objPurcRet.VoucherNumber));
paramCollection.Add(new DBParameter("@BillNumber", objPurcRet.BillNo));
paramCollection.Add(new DBParameter("@LedgerId", objPurcRet.LedgerId));
paramCollection.Add(new DBParameter("@PurcType", objPurcRet.PurchaseType));
paramCollection.Add(new DBParameter("@MatCentre", objPurcRet.MatCenter));
paramCollection.Add(new DBParameter("@Narration", objPurcRet.Narration));
paramCollection.Add(new DBParameter("@TotalAmount", objPurcRet.TotalAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalQty", objPurcRet.TotalQty, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalFree", objPurcRet.TotalFree, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalBasicAmount", objPurcRet.TotalBasicAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalDisAmount", objPurcRet.TotalDisAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalTaxAmount", objPurcRet.TotalTaxAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@BSTotalAmount", objPurcRet.BSTotalAmount, DbType.Decimal));
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 dr =
_dbHelper.ExecuteDataReader("spInsertPurchaseReturnMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SavePurchaseReturnItems(objPurcRet.Item_Voucher, id);
SavePurchaseReturnBillSundry(objPurcRet.BillSundry_Voucher, id);
}
catch (Exception ex)
{
isSaved = false;
//throw ex;
}
return(isSaved);
}
public bool SavePRVoucher(PurchaseReturnVoucherModel objPRRet)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@PRSeries", objPRRet.Series));
paramCollection.Add(new DBParameter("@PRType", objPRRet.PurchaseType));
paramCollection.Add(new DBParameter("@PR_Date", objPRRet.PR_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Voucher_Number", objPRRet.Voucher_Number));
paramCollection.Add(new DBParameter("@PRBillNo", objPRRet.BillNo));
paramCollection.Add(new DBParameter("@PRDueDate", objPRRet.DueDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@PRV_Party", objPRRet.Party));
paramCollection.Add(new DBParameter("@PRV_MatCenter", objPRRet.MatCenter));
paramCollection.Add(new DBParameter("@PRNarration", objPRRet.Narration));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
//paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now));
Query = "INSERT INTO Trans_PurchaseReturn([Series],[PurchaseType],[PR_Date],[VoucherNo],[BillNo],[DueDate],[Party],[MatCenter],[Narration]," +
"[CreatedBy]) VALUES " +
"(@PRSeries,@PRType,@PR_Date,@Voucher_Number,@PRBillNo,@PRDueDate, " +
" @PRV_Party,@PRV_MatCenter,@PRNarration,@CreatedBy)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
SavePRItems(objPRRet.Item_Voucher);
SavePRBillSundry(objPRRet.BillSundry_Voucher);
isSaved = true;
}
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
private bool UpdatePurchaseReturnItemandBS(PurchaseReturnVoucherModel objprv)
{
try
{
//UPDATE Item voucher -CHILD TABLE UPDATES
foreach (Item_VoucherModel item in objprv.Item_Voucher)
{
if (item.Item_ID > 0)
{
UpdatePurchaseReturnItems(item);
}
else
{
SavePurchaseReturntems(item);
}
}
//Update Bill Sundry Items
foreach (BillSundry_VoucherModel bs in objprv.BillSundry_Voucher)
{
if (bs.BSId > 0)
{
UpdatePurchaseReturnBillSundryVoucher(bs);
}
else
{
SavePurchaseReturnBillSundryVoucher(bs);
}
}
}
catch (Exception ex)
{
return(false);
throw ex;
}
return(true);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
PurchaseReturnVoucherModel objPurcRet = new PurchaseReturnVoucherModel();
if (tbxVoucherNumber.Text.Trim() == "")
{
MessageBox.Show("Voucher Number Can Not Be Blank!");
return;
}
objPurcRet.VoucherType = cbxVoucherType.Text.Trim();
objPurcRet.PR_Date = Convert.ToDateTime(dtDate.Text);
objPurcRet.Terms = cbxTerms.SelectedItem.ToString();
objPurcRet.VoucherNumber = Convert.ToInt64(tbxVoucherNumber.Text.Trim() == string.Empty ? "0" : tbxVoucherNumber.Text.Trim());
objPurcRet.BillNo = Convert.ToInt64(tbxBillNo.Text.Trim() == string.Empty ? "0" : tbxBillNo.Text.Trim());
objPurcRet.LedgerId = objAccBL.GetLedgerIdByAccountName(cbxParty.Text.Trim());
objPurcRet.PurchaseType = cbxPurcRetType.Text.Trim() == null ? string.Empty : cbxPurcRetType.Text.Trim();
objPurcRet.MatCenter = cbxMatcenter.Text.Trim();
objPurcRet.Narration = tbxNarration.Text.Trim() == null ? string.Empty : tbxNarration.Text.Trim();
objPurcRet.TotalQty = Convert.ToDecimal(colQty.SummaryItem.SummaryValue);
objPurcRet.TotalFree = Convert.ToDecimal(colFree.SummaryItem.SummaryValue);
objPurcRet.TotalBasicAmount = Convert.ToDecimal(colBasicAmt.SummaryItem.SummaryValue);
objPurcRet.TotalDisAmount = Convert.ToDecimal(colDisAmt.SummaryItem.SummaryValue);
objPurcRet.TotalTaxAmount = Convert.ToDecimal(colTaxAmt.SummaryItem.SummaryValue);
objPurcRet.TotalAmount = Convert.ToDecimal(colAmount.SummaryItem.SummaryValue);
objPurcRet.BSTotalAmount = Convert.ToDecimal(colBSAmount.SummaryItem.SummaryValue);
//Items Details
Item_VoucherModel objPurcRetItem;
List <Item_VoucherModel> lstPurcRetItems = new List <Item_VoucherModel>();
for (int i = 0; i < dvgItemDetails.DataRowCount; i++)
{
DataRow row = dvgItemDetails.GetDataRow(i);
objPurcRetItem = new Item_VoucherModel();
objPurcRetItem.ITM_Id = objIMBL.GetItemIdByItemName(row["Item"].ToString() == null ? string.Empty : row["Item"].ToString());
objPurcRetItem.Qty = Convert.ToDecimal(row["Qty"].ToString() == string.Empty ? "0.00" : row["Qty"]);
objPurcRetItem.Unit = row["Unit"].ToString() == null ? string.Empty : row["Unit"].ToString();
objPurcRetItem.Per = row["Per"].ToString() == null ? string.Empty : row["Per"].ToString();
objPurcRetItem.Price = Convert.ToDecimal(row["Price"].ToString() == string.Empty ? "0.00" : row["Price"].ToString());
objPurcRetItem.Batch = row["Batch"].ToString() == null ? string.Empty : row["Batch"].ToString();
objPurcRetItem.Free = Convert.ToDecimal(row["Free"].ToString() == string.Empty ? "0.00" : row["Free"].ToString());
objPurcRetItem.BasicAmt = Convert.ToDecimal(row["BasicAmt"].ToString() == string.Empty ? "0.00" : row["BasicAmt"].ToString());
objPurcRetItem.DiscountPercentage = Convert.ToDecimal(row["DiscountPercentage"].ToString() == string.Empty ? "0.00" : row["DiscountPercentage"].ToString());
objPurcRetItem.DiscountAmount = Convert.ToDecimal(row["DiscountAmount"].ToString() == string.Empty ? "0.00" : row["DiscountAmount"].ToString());
objPurcRetItem.TaxAmount = Convert.ToDecimal(row["TaxAmount"].ToString() == string.Empty ? "0.00" : row["TaxAmount"].ToString());
objPurcRetItem.Amount = Convert.ToDecimal(row["Amount"].ToString() == string.Empty ? "0.00" : row["Amount"].ToString());
objPurcRetItem.Item_ID = Convert.ToInt64(row["Item_ID"].ToString() == string.Empty ? "0" : row["Item_ID"].ToString());
objPurcRetItem.ParentId = Convert.ToInt64(row["ParentId"].ToString() == string.Empty ? "0" : row["ParentId"].ToString());
lstPurcRetItems.Add(objPurcRetItem);
}
objPurcRet.Item_Voucher = lstPurcRetItems;
//Bill Sundry Details
BillSundry_VoucherModel objPurcRetBS;
List <BillSundry_VoucherModel> lstPurcRetBS = new List <BillSundry_VoucherModel>();
for (int i = 0; i < dvgBSDetails.DataRowCount; i++)
{
DataRow row = dvgBSDetails.GetDataRow(i);
objPurcRetBS = new BillSundry_VoucherModel();
objPurcRetBS.BS_Id = objBSBL.GetBSIdByBSName(row["BillSundry"].ToString() == null ? string.Empty : row["BillSundry"].ToString());
objPurcRetBS.Percentage = Convert.ToDecimal(row["Percentage"].ToString() == string.Empty ? "0.00" : row["Percentage"].ToString());
objPurcRetBS.Extra = row["Extra"].ToString() == null ? string.Empty : row["Extra"].ToString();
objPurcRetBS.Amount = Convert.ToDecimal(row["Amount"].ToString() == string.Empty ? "0.00" : row["Amount"].ToString());
objPurcRetBS.BSId = Convert.ToInt64(row["BSId"].ToString() == string.Empty ? "0" : row["BSId"].ToString());
objPurcRetBS.ParentId = Convert.ToInt64(row["ParentId"].ToString() == string.Empty ? "0" : row["ParentId"].ToString());
lstPurcRetBS.Add(objPurcRetBS);
}
objPurcRet.BillSundry_Voucher = lstPurcRetBS;
objPurcRet.PR_Id = PurcRetId;
bool isSuccess = objPurcBL.UpdatePurchaseReturn(objPurcRet);
if (isSuccess)
{
MessageBox.Show("Update Successfully!");
PurcRetId = 0;
ClearControls();
Transaction.List.PurchaseReturnVouchersList frmPurcRetList = new Transaction.List.PurchaseReturnVouchersList();
frmPurcRetList.StartPosition = FormStartPosition.CenterParent;
frmPurcRetList.ShowDialog();
FillPurchaseReturnInfo();
}
}
private void FillPurchaseReturnInfo()
{
if (PurcRetId == 0)
{
cbxTerms.Focus();
ClearControls();
lactrlUpdate.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
lactrlDelete.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
lactrlSave.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
return;
}
PurchaseReturnVoucherModel objPurcRet = objPurcBL.GetAllPurchaseVchDetailsbyId(PurcRetId);
cbxVoucherType.Text = objPurcRet.VoucherType.ToString();
dtDate.Text = objPurcRet.PR_Date.ToString();
cbxTerms.SelectedItem = objPurcRet.Terms.ToString();
tbxVoucherNumber.Text = objPurcRet.VoucherNumber.ToString();
tbxBillNo.Text = objPurcRet.BillNo.ToString();
cbxPurcRetType.Text = objPurcRet.PurchaseType.ToString();
cbxParty.Text = objPurcRet.Party;
cbxMatcenter.Text = objPurcRet.MatCenter.ToString();
tbxNarration.Text = objPurcRet.Narration.ToString();
dtItem.Rows.Clear();
DataRow idr;
foreach (Item_VoucherModel objItems in objPurcRet.Item_Voucher)
{
idr = dtItem.NewRow();
idr["Item"] = objItems.Item;
idr["Qty"] = objItems.Qty;
idr["Unit"] = objItems.Unit;
idr["Per"] = objItems.Per;
idr["Price"] = objItems.Price;
idr["Batch"] = objItems.Batch;
idr["Free"] = objItems.Free;
idr["BasicAmt"] = objItems.BasicAmt;
idr["DiscountPercentage"] = objItems.DiscountPercentage;
idr["DiscountAmount"] = objItems.DiscountAmount;
idr["TaxAmount"] = objItems.TaxAmount;
idr["Amount"] = objItems.Amount;
idr["Item_ID"] = objItems.Item_ID;
idr["ParentId"] = objItems.ParentId;
dtItem.Rows.Add(idr);
}
dtbs.Rows.Clear();
DataRow bsdr;
foreach (BillSundry_VoucherModel objbs in objPurcRet.BillSundry_Voucher)
{
bsdr = dtbs.NewRow();
bsdr["BillSundry"] = objbs.BillSundry;
bsdr["Percentage"] = objbs.Percentage;
bsdr["Extra"] = objbs.Extra;
bsdr["Amount"] = objbs.Amount;
bsdr["BSId"] = objbs.BSId;
bsdr["ParentId"] = objbs.ParentId;
dtbs.Rows.Add(bsdr);
}
lactrlUpdate.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
lactrlDelete.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
lactrlSave.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
}
public PurchaseReturnVoucherModel GetAllPurchaseReturnById(int id)
{
PurchaseReturnVoucherModel objPRVoucher = new PurchaseReturnVoucherModel();
string Query = "SELECT * FROM Trans_purchaseReturn WHERE transprId=" + id;
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objPRVoucher.PR_Id = DataFormat.GetInteger(dr["TransPrId"]);
objPRVoucher.Series = dr["series"].ToString();
objPRVoucher.PurchaseType = dr["PurchaseType"].ToString();
objPRVoucher.PR_Date = DataFormat.GetDateTime(dr["pr_Date"]);
objPRVoucher.Voucher_Number = DataFormat.GetInteger(dr["Voucherno"]);
objPRVoucher.BillNo = DataFormat.GetInteger(dr["BillNo"]);
objPRVoucher.DueDate = DataFormat.GetDateTime(dr["DueDate"]);
objPRVoucher.Party = dr["party"].ToString();
objPRVoucher.MatCenter = dr["MatCenter"].ToString();
objPRVoucher.Narration = dr["Narration"].ToString();
//objpurchase.t = Convert.ToDecimal(dr["TotalQty"]);
//objpurchase.TotalAmount = Convert.ToDecimal(dr["TotalAmount"].ToString());
//objpurchase.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
//SELECT Purchase Items
string itemQuery = "SELECT * FROM Trans_pr_items WHERE TransprId=" + id;
System.Data.IDataReader drItems = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objPRVoucher.Item_Voucher = new List <Item_VoucherModel>();
Item_VoucherModel objitem;
while (drItems.Read())
{
objitem = new Item_VoucherModel();
objitem.Item_ID = Convert.ToInt32(drItems["ItemId"]);
objitem.ParentId = DataFormat.GetInteger(drItems["TransprId"]);
objitem.Item = drItems["item"].ToString();
objitem.Batch = drItems["Batch"].ToString();
objitem.Qty = Convert.ToInt32(drItems["qty"].ToString());
objitem.Unit = (drItems["unit"].ToString());
objitem.Price = Convert.ToDecimal(drItems["price"]);
objitem.Amount = Convert.ToInt32(drItems["amount"].ToString());
objitem.TotalQty = Convert.ToInt32(drItems["TotalQty"].ToString());
objitem.TotalAmount = Convert.ToDecimal(drItems["TotalAmount"]);
objPRVoucher.Item_Voucher.Add(objitem);
}
string BSQuery = "SELECT * FROM Trans_pr_BS WHERE TransprId=" + id;
System.Data.IDataReader drbs = _dbHelper.ExecuteDataReader(BSQuery, _dbHelper.GetConnObject());
objPRVoucher.BillSundry_Voucher = new List <BillSundry_VoucherModel>();
BillSundry_VoucherModel objbs;
while (drbs.Read())
{
objbs = new BillSundry_VoucherModel();
objbs.BSId = Convert.ToInt32(drbs["BSId"]);
objbs.ParentId = DataFormat.GetInteger(drbs["TransprId"]);
objbs.BillSundry = drbs["BillSundry"].ToString();
objbs.Percentage = Convert.ToDecimal(drbs["Percentage"].ToString());
objbs.Amount = Convert.ToDecimal((drbs["Amount"].ToString()));
objbs.TotalAmount = Convert.ToDecimal(drbs["TotalAmount"].ToString());
objPRVoucher.BillSundry_Voucher.Add(objbs);
}
}
return(objPRVoucher);
}
public List <PurchaseReturnVoucherModel> GetAllPurchaseReturn()
{
List <PurchaseReturnVoucherModel> lstPurchase = new List <PurchaseReturnVoucherModel>();
PurchaseReturnVoucherModel objPReturn;
string Query = "SELECT * FROM Trans_PurchaseReturn";
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objPReturn = new PurchaseReturnVoucherModel();
objPReturn.PR_Id = DataFormat.GetInteger(dr["PR_Id"]);
objPReturn.Series = dr["Series"].ToString();
objPReturn.PR_Date = DataFormat.GetDateTime(dr["PR_Date"]);
objPReturn.Voucher_Number = DataFormat.GetInteger(dr["VoucherNo"]);
objPReturn.PurchaseType = dr["PurchaseType"].ToString();
objPReturn.Party = dr["Party"].ToString();
objPReturn.MatCenter = dr["MatCentre"].ToString();
objPReturn.Narration = dr["Narration"].ToString();
//objPReturn. = Convert.ToDecimal(dr["TotalQty"]);
//objPReturn.TotalAmount = Convert.ToDecimal(dr["TotalAmount"]);
//objPReturn.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
//SELECT Purchase Items
string itemQuery = "SELECT * FROM Trans_PR_Items WHERE Pro_Id=" + objPReturn.PR_Id;
System.Data.IDataReader drItem = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objPReturn.Item_Voucher = new List <Item_VoucherModel>();
Item_VoucherModel objItemModel;
while (drItem.Read())
{
objItemModel = new Item_VoucherModel();
objItemModel.ParentId = DataFormat.GetInteger(drItem["TransPRId"]);
objItemModel.Item_ID = DataFormat.GetInteger(drItem["Id"]);
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"]);
objPReturn.Item_Voucher.Add(objItemModel);
}
//SELECT Bill Sundry Voucher items
string bsQuery = "SELECT * FROM Trans_PR_BS WHERE TransPVId=" + objPReturn.PR_Id;
System.Data.IDataReader drBS = _dbHelper.ExecuteDataReader(bsQuery, _dbHelper.GetConnObject());
objPReturn.BillSundry_Voucher = new List <BillSundry_VoucherModel>();
BillSundry_VoucherModel objBSModel;
while (drBS.Read())
{
objBSModel = new BillSundry_VoucherModel();
objBSModel.ParentId = DataFormat.GetInteger(drBS["TransPRId"]);
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"]);
objPReturn.BillSundry_Voucher.Add(objBSModel);
}
lstPurchase.Add(objPReturn);
}
return(lstPurchase);
}
//Get All Purchase Return Details By ID
public PurchaseReturnVoucherModel GetAllPurchaseVchDetailsbyId(long id)
{
PurchaseReturnVoucherModel objPurcRet = new PurchaseReturnVoucherModel();
StringBuilder sbQuery = new StringBuilder();
sbQuery.AppendLine("SELECT m.*,am.ACC_NAME FROM `purchasereturn_master` AS m");
sbQuery.AppendLine("INNER JOIN accountmaster AS am ON m.LedgerId= am.Ac_ID");
sbQuery.AppendLine("WHERE m.`PurcReturn_Id`='" + id + "'");
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(sbQuery.ToString(), _dbHelper.GetConnObject());
while (dr.Read())
{
objPurcRet.PR_Id = Convert.ToInt64(dr["PurcReturn_Id"]);
objPurcRet.VoucherType = dr["VoucherType"].ToString();
objPurcRet.PR_Date = DataFormat.GetDateTime(dr["PurcReturnDate"]);
objPurcRet.Terms = dr["Terms"].ToString();
objPurcRet.VoucherNumber = Convert.ToInt64(dr["VoucherNumber"]);
objPurcRet.BillNo = Convert.ToInt64(dr["BillNumber"].ToString());
objPurcRet.PurchaseType = dr["PurcType"].ToString();
objPurcRet.Party = dr["ACC_NAME"].ToString();
objPurcRet.MatCenter = dr["MatCentre"].ToString();
objPurcRet.Narration = dr["Narration"].ToString();
objPurcRet.TotalQty = Convert.ToDecimal(dr["TotalQty"]);
objPurcRet.TotalFree = Convert.ToDecimal(dr["TotalFree"]);
objPurcRet.TotalBasicAmount = Convert.ToDecimal(dr["TotalBasicAmount"]);
objPurcRet.TotalDisAmount = Convert.ToDecimal(dr["TotalDisAmount"]);
objPurcRet.TotalTaxAmount = Convert.ToDecimal(dr["TotalTaxAmount"]);
objPurcRet.TotalAmount = Convert.ToDecimal(dr["TotalAmount"].ToString());
objPurcRet.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
//SELECT Item Details
StringBuilder sbitemQuery = new StringBuilder();
sbitemQuery.AppendLine("SELECT i.*,im.ITEM_Name FROM purchasereturn_itemdetails as i");
sbitemQuery.AppendLine("inner join itemmaster as im on i.ITM_ID=im.ITM_ID");
sbitemQuery.AppendLine("WHERE PurcReturn_Id='" + id + "'");
System.Data.IDataReader drItems = _dbHelper.ExecuteDataReader(sbitemQuery.ToString(), _dbHelper.GetConnObject());
objPurcRet.Item_Voucher = new List <Item_VoucherModel>();
Item_VoucherModel objitem;
while (drItems.Read())
{
objitem = new Item_VoucherModel();
objitem.Item_ID = Convert.ToInt32(drItems["Id"]);
objitem.ParentId = DataFormat.GetInteger(drItems["PurcReturn_Id"]);
objitem.Item = drItems["ITEM_Name"].ToString();
objitem.Qty = Convert.ToDecimal(drItems["qty"].ToString());
objitem.Unit = (drItems["Unit"].ToString());
objitem.Per = (drItems["Per"].ToString());
objitem.Batch = drItems["Batch"].ToString();
objitem.Price = Convert.ToDecimal(drItems["Price"]);
objitem.Amount = Convert.ToDecimal(drItems["Amount"].ToString());
objitem.Free = Convert.ToDecimal(drItems["Free"]);
objitem.BasicAmt = Convert.ToDecimal(drItems["BasicAmt"].ToString());
objitem.DiscountPercentage = Convert.ToDecimal(drItems["DiscountPercentage"].ToString());
objitem.DiscountAmount = Convert.ToDecimal(drItems["DiscountAmount"].ToString());
objitem.TaxAmount = Convert.ToDecimal(drItems["TaxAmount"].ToString());
objPurcRet.Item_Voucher.Add(objitem);
}
//Select BS Details
StringBuilder sbBSQuery = new StringBuilder();
sbBSQuery.AppendLine("SELECT pbs.*,mbs.Name FROM purchasereturn_bsdetails as pbs");
sbBSQuery.AppendLine("inner join billsundarymaster as mbs on pbs.BS_Id=mbs.BS_Id");
sbBSQuery.AppendLine("WHERE PurcReturn_Id='" + id + "'");
System.Data.IDataReader drbs = _dbHelper.ExecuteDataReader(sbBSQuery.ToString(), _dbHelper.GetConnObject());
objPurcRet.BillSundry_Voucher = new List <BillSundry_VoucherModel>();
BillSundry_VoucherModel objbs;
while (drbs.Read())
{
objbs = new BillSundry_VoucherModel();
objbs.BSId = Convert.ToInt32(drbs["Id"]);
objbs.ParentId = DataFormat.GetInteger(drbs["PurcReturn_Id"]);
objbs.BillSundry = drbs["Name"].ToString();
objbs.Percentage = Convert.ToDecimal(drbs["Percentage"].ToString());
objbs.Extra = drbs["Extra"].ToString();
objbs.Amount = Convert.ToDecimal((drbs["Amount"].ToString()));
objPurcRet.BillSundry_Voucher.Add(objbs);
}
}
return(objPurcRet);
}
