//Save Sales Order Voucher
public bool SaveSalesOrder(TransSalesModel objSales)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objSales.VoucherNumber));
paramCollection.Add(new DBParameter("@Series", objSales.Series));
paramCollection.Add(new DBParameter("@SaleDate", objSales.SaleDate, System.Data.DbType.DateTime));
//paramCollection.Add(new DBParameter("@BillNo", objSales.BillNo));
//paramCollection.Add(new DBParameter("@DueDate", objSales.DueDate));
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("@ItemTotalAmount", objSales.TotalAmount));
paramCollection.Add(new DBParameter("@ItemTotalQty", objSales.TotalQty));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSales.BSTotalAmount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertSalesVoucher", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SaveSalesVoucherItems(objSales.SalesItem_Voucher, id);
SaveSalesBillSundryVoucher(objSales.SalesBillSundry_Voucher, id);
//Query = "INSERT INTO Trans_Sales([Series],[SaleDate],[VoucherNumber],[BillNumber],[DueDate],[SalesType],[Party]," +
//"[MatCentre],[Narration],[TotalQty],[TotalAmount],[BSTotalAmount],[CreatedBy]) VALUES " +
//"(@Series,@SaleDate,@VoucherNumber,@BillNo,@DueDate,@SalesType,@Party,@MatCentre,@Narration,@TotalQty,@TotalAmount,@BSTotalAmount,@CreatedBy)";
//if (_dbHelper.ExecuteScalar(Query, paramCollection) > 0)
//{
// SaveSalesVoucherItems(objSales.SalesItem_Voucher);
// SaveSalesBillSundryVoucher(objSales.SalesBillSundry_Voucher);
// isSaved = true;
//}
}
catch (Exception ex)
{
isSaved = false;
// throw ex;
}
return(isSaved);
}
public bool SaveSalesVoucher(TransSalesModel objSales)
{
string Query = string.Empty;
bool isSaved = 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("@BSTotalAmount", objSales.BSTotalAmount, 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("@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));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertSalesVoucherMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SaveSalesVoucherItems(objSales.SalesItem_Voucher, id);
SaveSalesBillSundryVoucher(objSales.SalesBillSundry_Voucher, id);
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
// SAVE Sales Quotation
public bool SaveSalesQuotation(TransSalesModel objSalesQut)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objSalesQut.VoucherNumber));
paramCollection.Add(new DBParameter("@Series", objSalesQut.Series));
paramCollection.Add(new DBParameter("@SQDate", objSalesQut.SaleDate, System.Data.DbType.DateTime));
//paramCollection.Add(new DBParameter("@BillNo", objSales.BillNo));
//paramCollection.Add(new DBParameter("@DueDate", objSales.DueDate));
paramCollection.Add(new DBParameter("@SalesType", objSalesQut.SalesType));
paramCollection.Add(new DBParameter("@Party", objSalesQut.Party));
paramCollection.Add(new DBParameter("@MatCentre", objSalesQut.MatCentre));
paramCollection.Add(new DBParameter("@Narration", objSalesQut.Narration));
paramCollection.Add(new DBParameter("@ItemTotalAmount", objSalesQut.TotalAmount));
paramCollection.Add(new DBParameter("@ItemTotalQty", objSalesQut.TotalQty));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSalesQut.BSTotalAmount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertSalesQuotationMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SaveSalesQuotationItems(objSalesQut.SalesItem_Voucher, id);
SaveSalesQuotationBillSundryVoucher(objSalesQut.SalesBillSundry_Voucher, id);
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
public bool SaveSalesVoucher(TransSalesModel objSales)
{
string Query = string.Empty;
bool isSaved = 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("@BillNo", objSales.BillNo));
paramCollection.Add(new DBParameter("@DueDate", objSales.DueDate));
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));
paramCollection.Add(new DBParameter("@TotalAmount", objSales.TotalAmount));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSales.BSTotalAmount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
Query = "INSERT INTO Trans_Sales([Series],[SaleDate],[VoucherNumber],[BillNumber],[DueDate],[SalesType],[Party]," +
"[MatCentre],[Narration],[TotalQty],[TotalAmount],[BSTotalAmount],[CreatedBy]) VALUES " +
"(@Series,@SaleDate,@VoucherNumber,@BillNo,@DueDate,@SalesType,@Party,@MatCentre,@Narration,@TotalQty,@TotalAmount,@BSTotalAmount,@CreatedBy)";
if (_dbHelper.ExecuteNonQuery(Query, paramCollection) > 0)
{
SaveSalesVoucherItems(objSales.SalesItem_Voucher);
SaveSalesBillSundryVoucher(objSales.SalesBillSundry_Voucher);
isSaved = true;
}
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
private bool UpdateItemandBS(TransSalesModel objSales)
{
try
{
//UPDATE Item voucher -CHILD TABLE UPDATES
foreach (Item_VoucherModel item in objSales.SalesItem_Voucher)
{
if (item.Item_ID > 0)
{
UpdateSalesVoucherItems(item);
}
else
{
SaveSalesVoucherItem(item);
}
}
//Update Bill Sundry Items
foreach (BillSundry_VoucherModel bs in objSales.SalesBillSundry_Voucher)
{
if (bs.BSId > 0)
{
UpdateSalesBillSundryVoucher(bs);
}
else
{
SaveBillSundryVoucher(bs);
}
}
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public TransSalesModel GetSalesById(String ID)
{
//List<TransSalesModel> lstSalesVouchers = new List<TransSalesModel>();
TransSalesModel objsales = new TransSalesModel();
string query = string.Empty;
query = "SELECT count(*) FROM Trans_Sales";
//int i=_dbHelper.ExecuteNonQuery(query);
if (ID == "first")
{
query = "SELECT TOP 1 * FROM Trans_Sales";
}
ID = "1";
if (ID == "last")
{
query = "SELECT TOP 1 * FROM Trans_Sales order by Trans_Sales_Id desc";
}
if (Convert.ToInt32(ID) > 0)
{
query = "SELECT * FROM Trans_Sales WHERE Trans_sales_id=" + ID;
}
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(query, _dbHelper.GetConnObject());
while (dr.Read())
{
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"]);
objsales.Trans_Sales_Id = objsales.Trans_Sales_Id + 1;
//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(objsales);
}
public TransSalesModel GetAllSalesbyId(int id)
{
TransSalesModel objSales = new TransSalesModel();
string Query = "SELECT * FROM Trans_Sales WHERE trans_sales_Id=" + id;
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objSales.Trans_Sales_Id = Convert.ToInt32(dr["Trans_Sales_Id"]);
objSales.Series = dr["series"].ToString();
objSales.SaleDate = DataFormat.GetDateTime(dr["SaleDate"]);
objSales.VoucherNumber = DataFormat.GetInteger(dr["VoucherNumber"]);
objSales.BillNo = Convert.ToInt32(dr["BillNumber"].ToString());
objSales.DueDate = Convert.ToDateTime(dr["DueDate"]);
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"].ToString());
objSales.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
//SELECT Credit Note Accounts
string itemQuery = "SELECT * FROM Trans_Sales_Item WHERE Trans_Sales_Id=" + id;
System.Data.IDataReader drItems = _dbHelper.ExecuteDataReader(itemQuery, _dbHelper.GetConnObject());
objSales.SalesItem_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["Trans_Sales_Id"]);
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.TotalAmount = Convert.ToDecimal(drItems["TotalAmount"]);
objitem.TotalQty = Convert.ToInt32(drItems["TotalQty"].ToString());
objSales.SalesItem_Voucher.Add(objitem);
}
string BSQuery = "SELECT * FROM Trans_Sales_BS WHERE Trans_Sales_Id=" + id;
System.Data.IDataReader drbs = _dbHelper.ExecuteDataReader(BSQuery, _dbHelper.GetConnObject());
objSales.SalesBillSundry_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["Trans_Sales_Id"]);
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());
objSales.SalesBillSundry_Voucher.Add(objbs);
}
}
return(objSales);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
TransSalesModel objSaleVch = new TransSalesModel();
if (tbxVoucherNumber.Text.Trim() == "")
{
MessageBox.Show("Voucher Number Can Not Be Blank!");
return;
}
objSaleVch.VoucherType = tbxVoucherType.Text.Trim();
objSaleVch.SaleDate = Convert.ToDateTime(dtDate.Text);
objSaleVch.Terms = cbxTerms.SelectedItem.ToString();
objSaleVch.VoucherNumber = Convert.ToInt64(tbxVoucherNumber.Text.Trim() == string.Empty ? "0" : tbxVoucherNumber.Text.Trim());
objSaleVch.BillNo = Convert.ToInt64(tbxBillNo.Text.Trim() == string.Empty ? "0" : tbxBillNo.Text.Trim());
objSaleVch.LedgerId = objAccBL.GetLedgerIdByAccountName(tbxParty.Text.Trim());
objSaleVch.SalesType = tbxSaleType.Text.Trim();
objSaleVch.MatCentre = tbxMatcenter.Text.Trim() == null ? string.Empty : tbxMatcenter.Text.Trim();
objSaleVch.PriceList = cbxPriceList.Text.Trim() == null ? string.Empty : cbxPriceList.Text.Trim();
objSaleVch.Narration = tbxNarration.Text.Trim() == null ? string.Empty : tbxNarration.Text.Trim();
objSaleVch.TotalAmount = Convert.ToDecimal(Amount.SummaryItem.SummaryValue);
objSaleVch.TotalFree = Convert.ToDecimal(colFree.SummaryItem.SummaryValue);
objSaleVch.TotalBasicAmount = Convert.ToDecimal(colBasicAmt.SummaryItem.SummaryValue);
objSaleVch.TotalDisAmount = Convert.ToDecimal(colDisAmt.SummaryItem.SummaryValue);
objSaleVch.TotalTaxAmount = Convert.ToDecimal(colTaxAmont.SummaryItem.SummaryValue);
objSaleVch.TotalQty = Convert.ToDecimal(Qty.SummaryItem.SummaryValue);
objSaleVch.BSTotalAmount = Convert.ToDecimal(BSAmount.SummaryItem.SummaryValue);
//Items Details
Item_VoucherModel objSaleItem;
List <Item_VoucherModel> lstSaleItems = new List <Item_VoucherModel>();
for (int i = 0; i < dvgItemDetails.DataRowCount; i++)
{
DataRow row = dvgItemDetails.GetDataRow(i);
objSaleItem = new Item_VoucherModel();
objSaleItem.ITM_Id = objIMBL.GetItemIdByItemName(row["Item"].ToString() == null ? string.Empty : row["Item"].ToString());
objSaleItem.Qty = Convert.ToDecimal(row["Qty"].ToString() == string.Empty ? "0.00" : row["Qty"]);
objSaleItem.LedgerId = objAccBL.GetLedgerIdByAccountName(row["Particulars"].ToString() == null ? string.Empty : row["Particulars"].ToString());
objSaleItem.Unit = row["Unit"].ToString() == null ? string.Empty : row["Unit"].ToString();
objSaleItem.Per = row["Per"].ToString() == null ? string.Empty : row["Per"].ToString();
objSaleItem.Price = Convert.ToDecimal(row["Price"].ToString() == string.Empty ? "0.00" : row["Price"].ToString());
objSaleItem.Batch = row["Batch"].ToString() == null ? string.Empty : row["Batch"].ToString();
objSaleItem.Free = Convert.ToDecimal(row["Free"].ToString() == string.Empty ? "0.00" : row["Free"].ToString());
objSaleItem.BasicAmt = Convert.ToDecimal(row["BasicAmt"].ToString() == string.Empty ? "0.00" : row["BasicAmt"].ToString());
objSaleItem.DiscountPercentage = Convert.ToDecimal(row["DiscountPercentage"].ToString() == string.Empty ? "0.00" : row["DiscountPercentage"].ToString());
objSaleItem.DiscountAmount = Convert.ToDecimal(row["DiscountAmount"].ToString() == string.Empty ? "0.00" : row["DiscountAmount"].ToString());
objSaleItem.TaxAmount = Convert.ToDecimal(row["TaxAmount"].ToString() == string.Empty ? "0.00" : row["TaxAmount"].ToString());
objSaleItem.Amount = Convert.ToDecimal(row["Amount"].ToString() == string.Empty ? "0.00" : row["Amount"].ToString());
objSaleItem.Item_ID = Convert.ToInt64(row["Item_ID"].ToString() == string.Empty ? "0" : row["Item_ID"].ToString());
objSaleItem.ParentId = Convert.ToInt64(row["ParentId"].ToString() == string.Empty ? "0" : row["ParentId"].ToString());
lstSaleItems.Add(objSaleItem);
}
objSaleVch.SalesItem_Voucher = lstSaleItems;
//Bill Sundry Details
BillSundry_VoucherModel objSaleBS;
List <BillSundry_VoucherModel> lstSaleBS = new List <BillSundry_VoucherModel>();
for (int i = 0; i < dvgBsDetails.DataRowCount; i++)
{
DataRow row = dvgBsDetails.GetDataRow(i);
objSaleBS = new BillSundry_VoucherModel();
objSaleBS.BS_Id = objBSBL.GetBSIdByBSName(row["BillSundry"].ToString() == null ? string.Empty : row["BillSundry"].ToString());
objSaleBS.Percentage = Convert.ToDecimal(row["Percentage"].ToString() == string.Empty ? "0.00" : row["Percentage"].ToString());
objSaleBS.Extra = row["Extra"].ToString() == null ? string.Empty : row["Extra"].ToString();
objSaleBS.Amount = Convert.ToDecimal(row["Amount"].ToString() == string.Empty ? "0.00" : row["Amount"].ToString());
objSaleBS.BSId = Convert.ToInt64(row["BSId"].ToString() == string.Empty ? "0" : row["BSId"].ToString());
objSaleBS.ParentId = Convert.ToInt64(row["ParentId"].ToString() == string.Empty ? "0" : row["ParentId"].ToString());
lstSaleBS.Add(objSaleBS);
}
objSaleVch.SalesBillSundry_Voucher = lstSaleBS;
objSaleVch.Trans_Sales_Id = SalesId;
bool isSuccess = objSVBL.UpdateSalesVoucherMaster(objSaleVch);
if (isSuccess)
{
MessageBox.Show("Update Successfully!");
SalesId = 0;
ClearControls();
Transaction.List.SalesVouchersList frmSaleVchList = new Transaction.List.SalesVouchersList();
frmSaleVchList.StartPosition = FormStartPosition.CenterParent;
frmSaleVchList.ShowDialog();
FillSalesVoucherInfo();
}
}
private void FillSalesVoucherInfo()
{
if (SalesId == 0)
{
tbxVoucherType.FindForm();
laCtrlUpdate.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
laCtrlDelete.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
laCtrlSave.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
return;
}
TransSalesModel objSaleVch = objSVBL.GetAllSalesbyId(SalesId);
tbxVoucherType.Text = objSaleVch.VoucherType.ToString();
dtDate.Text = objSaleVch.SaleDate.ToString();
cbxTerms.SelectedItem = objSaleVch.Terms.ToString();
tbxVoucherNumber.Text = objSaleVch.VoucherNumber.ToString();
tbxBillNo.Text = objSaleVch.BillNo.ToString();
tbxSaleType.Text = objSaleVch.SalesType.ToString();
tbxParty.Text = objSaleVch.Party;
tbxMatcenter.Text = objSaleVch.MatCentre.ToString();
tbxNarration.Text = objSaleVch.Narration.ToString();
//Qty.SummaryItem= objSaleVch.TotalQty.ToString();
//objSaleVch.TotalAmount = Convert.ToDecimal(dr["TotalAmount"].ToString());
//objSaleVch.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
cbxPriceList.Text = objSaleVch.PriceList.ToString();
//dvgMainItem.DataSource = objSaleVch.SalesItem_Voucher;
//dvgBSMain.DataSource = objSaleVch.SalesBillSundry_Voucher;
dtItem.Rows.Clear();
DataRow idr;
foreach (Item_VoucherModel objItems in objSaleVch.SalesItem_Voucher)
{
idr = dtItem.NewRow();
idr["Item"] = objItems.Item;
idr["Particulars"] = objItems.Particulars;
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);
if (objItems.ITM_Id != 0)
{
ItemMode();
}
else
{
AccountMode();
}
}
dtbs.Rows.Clear();
DataRow bsdr;
foreach (BillSundry_VoucherModel objbs in objSaleVch.SalesBillSundry_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 TransSalesModel GetAllSalesbyId(long id)
{
TransSalesModel objSaleVch = new TransSalesModel();
StringBuilder sbQuery = new StringBuilder();
sbQuery.AppendLine("SELECT m.*,am.ACC_NAME FROM `salesvoucher_master` AS m");
sbQuery.AppendLine("INNER JOIN accountmaster AS am ON m.LedgerId= am.Ac_ID");
sbQuery.AppendLine("WHERE m.`SalesVoucher_Id`='" + id + "'");
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(sbQuery.ToString(), _dbHelper.GetConnObject());
while (dr.Read())
{
objSaleVch.Trans_Sales_Id = Convert.ToInt64(dr["SalesVoucher_Id"]);
objSaleVch.VoucherType = dr["VoucherType"].ToString();
objSaleVch.SaleDate = DataFormat.GetDateTime(dr["SaleDate"]);
objSaleVch.Terms = dr["Terms"].ToString();
objSaleVch.VoucherNumber = Convert.ToInt64(dr["VoucherNumber"]);
objSaleVch.BillNo = Convert.ToInt64(dr["BillNumber"].ToString());
objSaleVch.SalesType = dr["SalesType"].ToString();
objSaleVch.Party = dr["ACC_NAME"].ToString();
objSaleVch.MatCentre = dr["MatCentre"].ToString();
objSaleVch.Narration = dr["Narration"].ToString();
objSaleVch.TotalQty = Convert.ToDecimal(dr["TotalQty"]);
objSaleVch.TotalAmount = Convert.ToDecimal(dr["TotalAmount"].ToString());
objSaleVch.BSTotalAmount = Convert.ToDecimal(dr["BSTotalAmount"]);
objSaleVch.PriceList = dr["PriceList"].ToString();
//SELECT Item Details
StringBuilder sbitemQuery = new StringBuilder();
sbitemQuery.AppendLine("SELECT i.*,im.ITEM_Name,ia.ACC_NAME FROM salesvoucher_itemdetails as i");
sbitemQuery.AppendLine("left join itemmaster as im on i.ITM_ID=im.ITM_ID");
sbitemQuery.AppendLine("left join accountmaster as ia on i.LedgerId=ia.AC_ID");
sbitemQuery.AppendLine("WHERE SalesVoucher_Id='" + id + "'");
System.Data.IDataReader drItems = _dbHelper.ExecuteDataReader(sbitemQuery.ToString(), _dbHelper.GetConnObject());
objSaleVch.SalesItem_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["SalesVoucher_Id"]);
objitem.ITM_Id = Convert.ToInt64(drItems["ITM_ID"].ToString() == string.Empty?"0": drItems["ITM_ID"].ToString());
objitem.LedgerId = Convert.ToInt64(drItems["LedgerId"].ToString() == string.Empty?"0":drItems["LedgerId"].ToString());
objitem.Item = drItems["ITEM_Name"].ToString();
objitem.Particulars = drItems["ACC_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());
objSaleVch.SalesItem_Voucher.Add(objitem);
}
//Select BS Details
StringBuilder sbBSQuery = new StringBuilder();
sbBSQuery.AppendLine("SELECT pbs.*,mbs.Name FROM salesvoucher_bsdetails as pbs");
sbBSQuery.AppendLine("inner join billsundarymaster as mbs on pbs.BS_Id=mbs.BS_Id");
sbBSQuery.AppendLine("WHERE SalesVoucher_Id='" + id + "'");
System.Data.IDataReader drbs = _dbHelper.ExecuteDataReader(sbBSQuery.ToString(), _dbHelper.GetConnObject());
objSaleVch.SalesBillSundry_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["SalesVoucher_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()));
objSaleVch.SalesBillSundry_Voucher.Add(objbs);
}
}
return(objSaleVch);
}
private void btnSave_Click(object sender, EventArgs e)
{
TransSalesModel objSales = new TransSalesModel();
if (tbxVchNo.Text.Trim() == "")
{
MessageBox.Show("Voucher Number Can Not Be Blank!");
return;
}
objSales.Series = tbxSeries.Text.Trim();
objSales.SalesType = tbxSalesType.Text.Trim();
objSales.SaleDate = Convert.ToDateTime(dtDate.Text);
objSales.VoucherNumber = Convert.ToInt32(tbxVchNo.Text.Trim());
objSales.Party = tbxParty.Text.Trim();
objSales.MatCentre = tbxMatCentre.Text.Trim();
objSales.Narration = tbxNarration.Text.Trim();
objSales.TotalAmount = Convert.ToDecimal(Amount.SummaryItem.SummaryValue);
objSales.TotalQty = Convert.ToInt32(Qty.SummaryItem.SummaryValue);
//Bill Number and Due date not captured- check with Ravi if these are required
//Items
Item_VoucherModel objItem;
List <Item_VoucherModel> lstItems = new List <Item_VoucherModel>();
for (int i = 0; i < gdvItem.DataRowCount; i++)
{
DataRow row = gdvItem.GetDataRow(i);
objItem = new Item_VoucherModel();
objItem.Item = row["Item"].ToString();
objItem.Qty = Convert.ToDecimal(row["Qty"]);
objItem.Unit = row["Unit"].ToString();
objItem.Amount = Convert.ToDecimal(row["Amount"].ToString());
objItem.Price = Convert.ToDecimal(row["Price"].ToString());
lstItems.Add(objItem);
}
objSales.SalesItem_Voucher = lstItems;
//Bill Sundry
BillSundry_VoucherModel objBS;
List <BillSundry_VoucherModel> lstBS = new List <BillSundry_VoucherModel>();
for (int i = 0; i < gridBs.DataRowCount; i++)
{
DataRow row = gridBs.GetDataRow(i);
objBS = new BillSundry_VoucherModel();
objBS.BillSundry = row["BillSundry"].ToString();
objBS.Percentage = Convert.ToDecimal(row["Percentage"]);
objBS.Amount = Convert.ToDecimal(row["Amount"]);
objBS.Type = row["Extra"].ToString();
lstBS.Add(objBS);
}
objSales.BSTotalAmount = Convert.ToDecimal(BSAmount.SummaryItem.SummaryValue);
objSales.SalesBillSundry_Voucher = lstBS;
objSalesVoucher = new SalesVoucherBL();
bool isSuccess = objSalesVoucher.SaveSalesVoucher(objSales);
if (isSuccess)
{
MessageBox.Show("Saved Successfully!");
// Dialogs.PopUPDialog d = new Dialogs.PopUPDialog("Saved Successfully!");
// d.ShowDialog();
}
}
//Update Sales Return Voucher
public bool UpdateSalesReturnMaster(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, 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));
paramCollection.Add(new DBParameter("@Trans_Sales_Id", objSales.Trans_Sales_Id));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spUpdateSalesReturnMaster", _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("@Item_ID", item.Item_ID));
paramCollection.Add(new DBParameter("@ItemMastid", item.ITM_Id));
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("spUpdateSalesReturnItemDetails", _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("@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("spInsertSalesReturnItemDetails", _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("spUpdateSalesReturnBS", _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("spInsertSalesReturnBS", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
}
}
catch (Exception ex)
{
isUpdated = false;
//throw ex;
}
return(isUpdated);
}
