/// <summary>
/// Function to insert values to JournalDetails Table
/// </summary>
/// <param name="journaldetailsinfo"></param>
/// <returns></returns>
public decimal JournalDetailsAdd(JournalDetailsInfo journaldetailsinfo)
{
decimal decId = 0;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("JournalDetailsAdd", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@journalMasterId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.JournalMasterId;
sprmparam = sccmd.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.LedgerId;
sprmparam = sccmd.Parameters.Add("@credit", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.Credit;
sprmparam = sccmd.Parameters.Add("@debit", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.Debit;
sprmparam = sccmd.Parameters.Add("@exchangeRateId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.ExchangeRateId;
sprmparam = sccmd.Parameters.Add("@chequeNo", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.ChequeNo;
sprmparam = sccmd.Parameters.Add("@chequeDate", SqlDbType.DateTime);
sprmparam.Value = journaldetailsinfo.ChequeDate;
sprmparam = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam.Value = journaldetailsinfo.ExtraDate;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.Extra2;
decId = Convert.ToDecimal(sccmd.ExecuteScalar());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
return(decId);
}
/// <summary>
/// Function to insert values to JournalDetails Table
/// </summary>
/// <param name="journaldetailsinfo"></param>
/// <returns></returns>
public decimal JournalDetailsAdd(JournalDetailsInfo journaldetailsinfo)
{
decimal decId = 0;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("JournalDetailsAdd", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@journalMasterId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.JournalMasterId;
sprmparam = sccmd.Parameters.Add("@ledgerId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.LedgerId;
sprmparam = sccmd.Parameters.Add("@credit", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.Credit;
sprmparam = sccmd.Parameters.Add("@debit", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.Debit;
sprmparam = sccmd.Parameters.Add("@exchangeRateId", SqlDbType.Decimal);
sprmparam.Value = journaldetailsinfo.ExchangeRateId;
sprmparam = sccmd.Parameters.Add("@chequeNo", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.ChequeNo;
sprmparam = sccmd.Parameters.Add("@chequeDate", SqlDbType.DateTime);
sprmparam.Value = journaldetailsinfo.ChequeDate;
sprmparam = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam.Value = journaldetailsinfo.ExtraDate;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = journaldetailsinfo.Extra2;
decId = Convert.ToDecimal(sccmd.ExecuteScalar());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
return decId;
}
/// <summary>
/// Function to get particular values from JournalDetails table based on the parameter
/// </summary>
/// <param name="journalDetailsId"></param>
/// <returns></returns>
public JournalDetailsInfo JournalDetailsView(decimal journalDetailsId)
{
JournalDetailsInfo journaldetailsinfo = new JournalDetailsInfo();
SqlDataReader sdrreader = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("JournalDetailsView", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@journalDetailsId", SqlDbType.Decimal);
sprmparam.Value = journalDetailsId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
journaldetailsinfo.JournalDetailsId = decimal.Parse(sdrreader[0].ToString());
journaldetailsinfo.JournalMasterId = decimal.Parse(sdrreader[1].ToString());
journaldetailsinfo.LedgerId = decimal.Parse(sdrreader[2].ToString());
journaldetailsinfo.Credit = decimal.Parse(sdrreader[3].ToString());
journaldetailsinfo.Debit = decimal.Parse(sdrreader[4].ToString());
journaldetailsinfo.ExchangeRateId = decimal.Parse(sdrreader[5].ToString());
journaldetailsinfo.ChequeNo = sdrreader[6].ToString();
journaldetailsinfo.ChequeDate = DateTime.Parse(sdrreader[7].ToString());
journaldetailsinfo.ExtraDate = DateTime.Parse(sdrreader[8].ToString());
journaldetailsinfo.Extra1 = sdrreader[9].ToString();
journaldetailsinfo.Extra2 = sdrreader[10].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{ sdrreader.Close();
sqlcon.Close(); }
return(journaldetailsinfo);
}
/// <summary>
/// Function to get particular values from JournalDetails table based on the parameter
/// </summary>
/// <param name="journalDetailsId"></param>
/// <returns></returns>
public JournalDetailsInfo JournalDetailsView(decimal journalDetailsId )
{
JournalDetailsInfo journaldetailsinfo =new JournalDetailsInfo();
SqlDataReader sdrreader =null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("JournalDetailsView", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@journalDetailsId", SqlDbType.Decimal);
sprmparam.Value = journalDetailsId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
journaldetailsinfo.JournalDetailsId=decimal.Parse(sdrreader[0].ToString());
journaldetailsinfo.JournalMasterId=decimal.Parse(sdrreader[1].ToString());
journaldetailsinfo.LedgerId=decimal.Parse(sdrreader[2].ToString());
journaldetailsinfo.Credit=decimal.Parse(sdrreader[3].ToString());
journaldetailsinfo.Debit=decimal.Parse(sdrreader[4].ToString());
journaldetailsinfo.ExchangeRateId = decimal.Parse(sdrreader[5].ToString());
journaldetailsinfo.ChequeNo= sdrreader[6].ToString();
journaldetailsinfo.ChequeDate=DateTime.Parse(sdrreader[7].ToString());
journaldetailsinfo.ExtraDate=DateTime.Parse(sdrreader[8].ToString());
journaldetailsinfo.Extra1= sdrreader[9].ToString();
journaldetailsinfo.Extra2= sdrreader[10].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{ sdrreader.Close();
sqlcon.Close();
}
return journaldetailsinfo;
}
/// <summary>
/// Function to Save the voucher
/// </summary>
public void Save()
{
try
{
decimal decTotalDebit = 0;
decimal decTotalCredit = 0;
decTotalDebit = Convert.ToDecimal(txtDebitTotal.Text.Trim());
decTotalCredit = Convert.ToDecimal(txtCreditTotal.Text.Trim());
JournalMasterSP spJournalMaster = new JournalMasterSP();
JournalDetailsSP spJournalDetails = new JournalDetailsSP();
JournalMasterInfo infoJournalMaster = new JournalMasterInfo();
JournalDetailsInfo infoJournalDetails = new JournalDetailsInfo();
PartyBalanceSP SpPartyBalance = new PartyBalanceSP();
PartyBalanceInfo InfopartyBalance = new PartyBalanceInfo();
ExchangeRateSP spExchangeRate = new ExchangeRateSP();
infoJournalMaster.VoucherNo = strVoucherNo;
infoJournalMaster.InvoiceNo = txtVoucherNo.Text;
infoJournalMaster.SuffixPrefixId = decJournalSuffixPrefixId;
infoJournalMaster.Date = Convert.ToDateTime(txtDate.Text);
infoJournalMaster.Narration = txtNarration.Text.Trim();
infoJournalMaster.UserId = PublicVariables._decCurrentUserId;
infoJournalMaster.VoucherTypeId = decJournalVoucherTypeId;
infoJournalMaster.FinancialYearId = Convert.ToDecimal(PublicVariables._decCurrentFinancialYearId.ToString());
infoJournalMaster.Extra1 = string.Empty;
infoJournalMaster.Extra2 = string.Empty;
infoJournalMaster.ExtraDate = DateTime.Now;
infoJournalMaster.TotalAmount = decTotalDebit;
decimal decJournalMasterId = spJournalMaster.JournalMasterAdd(infoJournalMaster);
/*******************JournalDetailsAdd and LedgerPosting*************************/
infoJournalDetails.JournalMasterId = decJournalMasterId;
infoJournalDetails.ExtraDate = DateTime.Now;
infoJournalDetails.Extra1 = string.Empty;
infoJournalDetails.Extra2 = string.Empty;
decimal decLedgerId = 0;
decimal decDebit = 0;
decimal decCredit = 0;
int inRowCount = dgvJournalVoucher.RowCount;
for (int inI = 0; inI < inRowCount - 1; inI++)
{
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value.ToString() != string.Empty)
{
infoJournalDetails.LedgerId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value.ToString());
decLedgerId = infoJournalDetails.LedgerId;
}
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value.ToString() != string.Empty)
{
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString() != string.Empty)
{
//--------Currency conversion--------------//
decSelectedCurrencyRate = spExchangeRate.GetExchangeRateByExchangeRateId(Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbCurrency"].Value.ToString()));
decAmount = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
decConvertRate = decAmount * decSelectedCurrencyRate;
//===========================================//
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value.ToString() == "Dr")
{
infoJournalDetails.Debit = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
infoJournalDetails.Credit = 0;
decDebit = decConvertRate;
decCredit = infoJournalDetails.Credit;
}
else
{
infoJournalDetails.Credit = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
infoJournalDetails.Debit = 0;
decDebit = infoJournalDetails.Debit;
decCredit = decConvertRate;
}
}
infoJournalDetails.ExchangeRateId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbCurrency"].Value.ToString());
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value.ToString() != string.Empty)
{
infoJournalDetails.ChequeNo = dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value.ToString();
}
else
{
infoJournalDetails.ChequeNo = string.Empty;
}
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value.ToString() != string.Empty)
{
infoJournalDetails.ChequeDate = Convert.ToDateTime(dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value.ToString());
}
else
{
infoJournalDetails.ChequeDate = DateTime.Now;
}
decimal decJournalDetailsId = spJournalDetails.JournalDetailsAdd(infoJournalDetails);
if (decJournalDetailsId != 0)
{
PartyBalanceAddOrEdit(inI);
LedgerPosting(decLedgerId, decCredit, decDebit, decJournalDetailsId, inI);
}
}
}
Messages.SavedMessage();
//----------------If print after save is enable-----------------------//
if (cbxPrintAfterSave.Checked)
{
if (spSettings.SettingsStatusCheck("Printer") == "Dot Matrix")
{
PrintForDotMatrix(decJournalMasterId);
}
else
{
Print(decJournalMasterId);
}
}
//===================================================================//
}
catch (Exception ex)
{
MessageBox.Show("JV14:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to edit the debitnote voucher
/// </summary>
/// <param name="decJournalMasterId"></param>
public void Edit(decimal decJournalMasterId)
{
try
{
JournalMasterSP spJournalMaster = new JournalMasterSP();
JournalMasterInfo infoJournalMaster = new JournalMasterInfo();
JournalDetailsSP spJournalDetails = new JournalDetailsSP();
JournalDetailsInfo infoJournalDetails = new JournalDetailsInfo();
ExchangeRateSP spExchangeRate = new ExchangeRateSP();
/*****************Update in JournalMaster table *************/
decimal decTotalDebit = 0;
decimal decTotalCredit = 0;
infoJournalMaster.JournalMasterId = decJournalMasterId;
infoJournalMaster.VoucherNo = strVoucherNo;
infoJournalMaster.InvoiceNo = txtVoucherNo.Text.Trim();
infoJournalMaster.SuffixPrefixId = decJournalSuffixPrefixId;
infoJournalMaster.Date = Convert.ToDateTime(txtDate.Text);
infoJournalMaster.Narration = txtNarration.Text.Trim();
infoJournalMaster.UserId = PublicVariables._decCurrentUserId;
infoJournalMaster.VoucherTypeId = decJournalVoucherTypeId;
infoJournalMaster.FinancialYearId = Convert.ToDecimal(PublicVariables._decCurrentFinancialYearId.ToString());
infoJournalMaster.ExtraDate = DateTime.Now;
infoJournalMaster.Extra1 = string.Empty;
infoJournalMaster.Extra2 = string.Empty;
decTotalDebit = Convert.ToDecimal(txtDebitTotal.Text.Trim());
decTotalCredit = Convert.ToDecimal(txtCreditTotal.Text.Trim());
infoJournalMaster.TotalAmount = decTotalDebit;
decimal decEffectRow = spJournalMaster.JournalMasterEdit(infoJournalMaster);
/**********************JournalDetails Edit********************/
if (decEffectRow > 0)
{
infoJournalDetails.JournalMasterId = decJournalMasterId;
infoJournalDetails.ExtraDate = DateTime.Now;
infoJournalDetails.Extra1 = string.Empty;
infoJournalDetails.Extra2 = string.Empty;
//-----------to delete details, LedgerPosting and bankReconciliation of removed rows--------------//
LedgerPostingSP spLedgerPosting = new LedgerPostingSP();
foreach (object obj in arrlstOfRemove)
{
string str = Convert.ToString(obj);
spJournalDetails.JournalDetailsDelete(Convert.ToDecimal(str));
spLedgerPosting.LedgerPostDeleteByDetailsId(Convert.ToDecimal(str), strVoucherNo, decJournalVoucherTypeId);
}
spLedgerPosting.LedgerPostingDeleteByVoucherNoVoucherTypeIdAndLedgerId(strVoucherNo, decJournalVoucherTypeId, 12);
//=============================================================================================//
decimal decLedgerId = 0;
decimal decDebit = 0;
decimal decCredit = 0;
decimal decJournalDetailsId = 0;
int inRowCount = dgvJournalVoucher.RowCount;
for (int inI = 0; inI < inRowCount; inI++)
{
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value.ToString() != string.Empty)
{
infoJournalDetails.LedgerId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbAccountLedger"].Value.ToString());
decLedgerId = infoJournalDetails.LedgerId;
}
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value.ToString() != string.Empty)
{
//------------------Currency conversion------------------//
decSelectedCurrencyRate = spExchangeRate.GetExchangeRateByExchangeRateId(Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbCurrency"].Value));
decAmount = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
decConvertRate = decAmount * decSelectedCurrencyRate;
//======================================================//
if (dgvJournalVoucher.Rows[inI].Cells["dgvcmbDrOrCr"].Value.ToString() == "Dr")
{
infoJournalDetails.Debit = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
infoJournalDetails.Credit = 0;
decDebit = decConvertRate;
decCredit = infoJournalDetails.Credit;
}
else
{
infoJournalDetails.Credit = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtAmount"].Value.ToString());
infoJournalDetails.Debit = 0;
decDebit = infoJournalDetails.Debit;
decCredit = decConvertRate;
}
infoJournalDetails.ExchangeRateId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvcmbCurrency"].Value.ToString());
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value.ToString() != string.Empty)
{
infoJournalDetails.ChequeNo = dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeNo"].Value.ToString();
}
else
{
infoJournalDetails.ChequeNo = string.Empty;
}
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value.ToString() != string.Empty)
{
infoJournalDetails.ChequeDate = Convert.ToDateTime(dgvJournalVoucher.Rows[inI].Cells["dgvtxtChequeDate"].Value.ToString());
}
else
{
infoJournalDetails.ChequeDate = DateTime.Now;
}
if (dgvJournalVoucher.Rows[inI].Cells["dgvtxtDetailsId"].Value != null && dgvJournalVoucher.Rows[inI].Cells["dgvtxtDetailsId"].Value.ToString() != string.Empty)
{
infoJournalDetails.JournalDetailsId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtDetailsId"].Value.ToString());
spJournalDetails.JournalDetailsEdit(infoJournalDetails);
PartyBalanceAddOrEdit(inI);
decJournalDetailsId = infoJournalDetails.JournalDetailsId;
decimal decLedgerPostId = Convert.ToDecimal(dgvJournalVoucher.Rows[inI].Cells["dgvtxtLedgerPostingId"].Value.ToString());
LedgerPostingEdit(decLedgerPostId, decLedgerId, decCredit, decDebit, decJournalDetailsId, inI);
}
else
{
decJournalDetailsId = spJournalDetails.JournalDetailsAdd(infoJournalDetails);
PartyBalanceAddOrEdit(inI);
LedgerPosting(decLedgerId, decCredit, decDebit, decJournalDetailsId, inI);
}
}
}
DeletePartyBalanceOfRemovedRow();
Messages.UpdatedMessage();
if (cbxPrintAfterSave.Checked)
{
if (spSettings.SettingsStatusCheck("Printer") == "Dot Matrix")
{
PrintForDotMatrix(infoJournalMaster.JournalMasterId);
}
else
{
Print(infoJournalMaster.JournalMasterId);
}
}
if (journalRegisterObj != null)
{
this.Close();
journalRegisterObj.Enabled = true;
}
else if (frmJournalReportObj != null)
{
this.Close();
frmJournalReportObj.Enabled = true;
}
else if (frmDayBookObj != null)
{
this.Close();
}
else if (frmBillallocationObj != null)
{
this.Close();
}
}
//----------------If print after save is enable-----------------------//
//===================================================================//
}
catch (Exception ex)
{
MessageBox.Show("JV16:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}