/// <summary>
/// Function to Update values in BankReconciliation Table
/// </summary>
/// <param name="bankreconciliationinfo"></param>
public void BankReconciliationEdit(BankReconciliationInfo bankreconciliationinfo)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("BankReconciliationEdit", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@reconcileId", SqlDbType.Decimal);
sprmparam.Value = bankreconciliationinfo.ReconcileId;
sprmparam = sccmd.Parameters.Add("@ledgerPostingId", SqlDbType.Decimal);
sprmparam.Value = bankreconciliationinfo.LedgerPostingId;
sprmparam = sccmd.Parameters.Add("@statementDate", SqlDbType.DateTime);
sprmparam.Value = bankreconciliationinfo.StatementDate;
sprmparam = sccmd.Parameters.Add("@extraDate", SqlDbType.DateTime);
sprmparam.Value = bankreconciliationinfo.ExtraDate;
sprmparam = sccmd.Parameters.Add("@extra1", SqlDbType.VarChar);
sprmparam.Value = bankreconciliationinfo.Extra1;
sprmparam = sccmd.Parameters.Add("@extra2", SqlDbType.VarChar);
sprmparam.Value = bankreconciliationinfo.Extra2;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlcon.Close();
}
}
public void BankReconciliationAdd(BankReconciliationInfo bankreconciliationinfo)
{
try
{
SpBankReconciliation.BankReconciliationAdd(bankreconciliationinfo);
}
catch (Exception ex)
{
MessageBox.Show("BR:5" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Checking invalid entries for Save or Update and Save and Update function
/// </summary>
public void saveOrEdit()
{
try
{
BankReconciliationInfo infoBankReconciliation = new BankReconciliationInfo();
BankReconciliationSP spBankReconciliation = new BankReconciliationSP();
foreach (DataGridViewRow dgv in dgvBankReconciliation.Rows)
{
if (dgv.Cells["dgvtxtStatementDate"].Value != null && Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()) != 0 && dgv.Cells["dgvtxtStatementDate"].Value.ToString() != string.Empty)
{
infoBankReconciliation.LedgerPostingId = decimal.Parse(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString());
infoBankReconciliation.StatementDate = Convert.ToDateTime((dgv.Cells["dgvtxtStatementDate"].Value.ToString()));
infoBankReconciliation.Extra1 = string.Empty;
infoBankReconciliation.Extra2 = string.Empty;
infoBankReconciliation.ExtraDate = PublicVariables._dtCurrentDate;
decimal decReconcileId = spBankReconciliation.BankReconciliationLedgerPostingId(Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()));
if (decReconcileId != 0)
{
infoBankReconciliation.ReconcileId = decReconcileId;
spBankReconciliation.BankReconciliationEdit(infoBankReconciliation);
}
else
{
spBankReconciliation.BankReconciliationAdd(infoBankReconciliation);
}
}
else
{
if (Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()) != 0)
{
decimal decReconcileId = spBankReconciliation.BankReconciliationLedgerPostingId(Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()));
if (decReconcileId != 0)
{
spBankReconciliation.BankReconciliationDelete(decReconcileId);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("BR:4" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to get particular values from account BankReconciliation based on the parameter
/// </summary>
/// <param name="reconcileId"></param>
/// <returns></returns>
public BankReconciliationInfo BankReconciliationView(decimal reconcileId)
{
BankReconciliationInfo bankreconciliationinfo = new BankReconciliationInfo();
SqlDataReader sdrreader = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand sccmd = new SqlCommand("BankReconciliationView", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = sccmd.Parameters.Add("@reconcileId", SqlDbType.Decimal);
sprmparam.Value = reconcileId;
sdrreader = sccmd.ExecuteReader();
while (sdrreader.Read())
{
bankreconciliationinfo.ReconcileId = decimal.Parse(sdrreader[0].ToString());
bankreconciliationinfo.LedgerPostingId = decimal.Parse(sdrreader[1].ToString());
bankreconciliationinfo.StatementDate = DateTime.Parse(sdrreader[2].ToString());
bankreconciliationinfo.ExtraDate = DateTime.Parse(sdrreader[3].ToString());
bankreconciliationinfo.Extra1 = sdrreader[4].ToString();
bankreconciliationinfo.Extra2 = sdrreader[5].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sdrreader.Close();
sqlcon.Close();
}
return(bankreconciliationinfo);
}
/// <summary>
/// BankReconciliation Grid Fill function
/// </summary>
public void BankReconciliationFill()
{
try
{
BankReconciliationInfo infoBankReconciliation = new BankReconciliationInfo();
BankReconciliationBll BllBankReconciliation = new BankReconciliationBll();
dgvBankReconciliation.Rows.Clear();
List<DataTable> listObj = new List<DataTable>();
if (cmbBankAccount.SelectedIndex > -1)
{
if (cmbStatus.Text == "Reconciled")
{
listObj = BllBankReconciliation.BankReconciliationFillReconcile(Convert.ToDecimal(cmbBankAccount.SelectedValue.ToString()), Convert.ToDateTime(txtStatementFrom.Text), Convert.ToDateTime(txtStatementTo.Text));
if (listObj[0].Rows.Count > 0)
{
for (int i = 0; i < listObj[0].Rows.Count; i++)
{
dgvBankReconciliation.Rows.Add();
dgvBankReconciliation.Rows[i].Cells["dgvtxtSlNo"].Value = listObj[0].Rows[i]["Sl No"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDate"].Value = listObj[0].Rows[i]["date"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtParticular"].Value = listObj[0].Rows[i]["ledgerName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherType"].Value = listObj[0].Rows[i]["voucherTypeName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherNo"].Value = listObj[0].Rows[i]["voucherNo"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeNo"].Value = listObj[0].Rows[i]["chequeNo"].ToString();
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeDate"].Value = listObj[0].Rows[i]["chequeDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDeposit"].Value = listObj[0].Rows[i]["debit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtWithdraw"].Value = listObj[0].Rows[i]["credit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtStatementDate"].Value = listObj[0].Rows[i]["statementDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtLedgerPostingId"].Value = listObj[0].Rows[i]["ledgerPostingId"];
}
}
}
else
{
listObj = BllBankReconciliation.BankReconciliationUnrecocile(Convert.ToDecimal(cmbBankAccount.SelectedValue.ToString()), Convert.ToDateTime(txtStatementFrom.Text), Convert.ToDateTime(txtStatementTo.Text));
if (listObj[0].Rows.Count > 0)
{
for (int i = 0; i < listObj[0].Rows.Count; i++)
{
dgvBankReconciliation.Rows.Add();
dgvBankReconciliation.Rows[i].Cells["dgvtxtSlNo"].Value = listObj[0].Rows[i]["Sl No"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDate"].Value = listObj[0].Rows[i]["date"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtParticular"].Value = listObj[0].Rows[i]["ledgerName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherType"].Value = listObj[0].Rows[i]["voucherTypeName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherNo"].Value = listObj[0].Rows[i]["voucherNo"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeNo"].Value = listObj[0].Rows[i]["chequeNo"].ToString();
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeDate"].Value = listObj[0].Rows[i]["chequeDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDeposit"].Value = listObj[0].Rows[i]["debit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtWithdraw"].Value = listObj[0].Rows[i]["credit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtStatementDate"].Value = string.Empty;
dgvBankReconciliation.Rows[i].Cells["dgvtxtLedgerPostingId"].Value = listObj[0].Rows[i]["ledgerPostingId"];
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("BR:3" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Checking invalid entries for Save or Update and Save and Update function
/// </summary>
public void saveOrEdit()
{
try
{
BankReconciliationInfo infoBankReconciliation = new BankReconciliationInfo();
BankReconciliationBll BllBankReconciliation = new BankReconciliationBll();
foreach (DataGridViewRow dgv in dgvBankReconciliation.Rows)
{
if (dgv.Cells["dgvtxtStatementDate"].Value != null && Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()) != 0 && dgv.Cells["dgvtxtStatementDate"].Value.ToString() != string.Empty)
{
infoBankReconciliation.LedgerPostingId = decimal.Parse(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString());
infoBankReconciliation.StatementDate = Convert.ToDateTime((dgv.Cells["dgvtxtStatementDate"].Value.ToString()));
infoBankReconciliation.Extra1 = string.Empty;
infoBankReconciliation.Extra2 = string.Empty;
infoBankReconciliation.ExtraDate = PublicVariables._dtCurrentDate;
decimal decReconcileId = BllBankReconciliation.BankReconciliationLedgerPostingId(Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()));
if (decReconcileId != 0)
{
infoBankReconciliation.ReconcileId = decReconcileId;
BllBankReconciliation.BankReconciliationEdit(infoBankReconciliation);
}
else
BllBankReconciliation.BankReconciliationAdd(infoBankReconciliation);
}
else
{
if (Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()) != 0)
{
decimal decReconcileId = BllBankReconciliation.BankReconciliationLedgerPostingId(Convert.ToDecimal(dgv.Cells["dgvtxtLedgerPostingId"].Value.ToString()));
if (decReconcileId != 0)
{
BllBankReconciliation.BankReconciliationDelete(decReconcileId);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("BR:4" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// BankReconciliation Grid Fill function
/// </summary>
public void BankReconciliationFill()
{
try
{
BankReconciliationInfo infoBankReconciliation = new BankReconciliationInfo();
BankReconciliationSP spBankReconciliation = new BankReconciliationSP();
dgvBankReconciliation.Rows.Clear();
DataTable dtblBank = new DataTable();
if (cmbBankAccount.SelectedIndex > -1)
{
if (cmbStatus.Text == "Reconciled")
{
dtblBank = spBankReconciliation.BankReconciliationFillReconcile(Convert.ToDecimal(cmbBankAccount.SelectedValue.ToString()), Convert.ToDateTime(txtStatementFrom.Text), Convert.ToDateTime(txtStatementTo.Text));
if (dtblBank.Rows.Count > 0)
{
for (int i = 0; i < dtblBank.Rows.Count; i++)
{
dgvBankReconciliation.Rows.Add();
dgvBankReconciliation.Rows[i].Cells["dgvtxtSlNo"].Value = dtblBank.Rows[i]["Sl No"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDate"].Value = dtblBank.Rows[i]["date"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtParticular"].Value = dtblBank.Rows[i]["ledgerName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherType"].Value = dtblBank.Rows[i]["voucherTypeName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherNo"].Value = dtblBank.Rows[i]["voucherNo"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeNo"].Value = dtblBank.Rows[i]["chequeNo"].ToString();
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeDate"].Value = dtblBank.Rows[i]["chequeDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDeposit"].Value = dtblBank.Rows[i]["debit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtWithdraw"].Value = dtblBank.Rows[i]["credit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtStatementDate"].Value = dtblBank.Rows[i]["statementDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtLedgerPostingId"].Value = dtblBank.Rows[i]["ledgerPostingId"];
}
}
}
else
{
dtblBank = spBankReconciliation.BankReconciliationUnrecocile(Convert.ToDecimal(cmbBankAccount.SelectedValue.ToString()), Convert.ToDateTime(txtStatementFrom.Text), Convert.ToDateTime(txtStatementTo.Text));
if (dtblBank.Rows.Count > 0)
{
for (int i = 0; i < dtblBank.Rows.Count; i++)
{
dgvBankReconciliation.Rows.Add();
dgvBankReconciliation.Rows[i].Cells["dgvtxtSlNo"].Value = dtblBank.Rows[i]["Sl No"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDate"].Value = dtblBank.Rows[i]["date"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtParticular"].Value = dtblBank.Rows[i]["ledgerName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherType"].Value = dtblBank.Rows[i]["voucherTypeName"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtVoucherNo"].Value = dtblBank.Rows[i]["voucherNo"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeNo"].Value = dtblBank.Rows[i]["chequeNo"].ToString();
dgvBankReconciliation.Rows[i].Cells["dgvtxtChequeDate"].Value = dtblBank.Rows[i]["chequeDate"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtDeposit"].Value = dtblBank.Rows[i]["debit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtWithdraw"].Value = dtblBank.Rows[i]["credit"];
dgvBankReconciliation.Rows[i].Cells["dgvtxtStatementDate"].Value = string.Empty;
dgvBankReconciliation.Rows[i].Cells["dgvtxtLedgerPostingId"].Value = dtblBank.Rows[i]["ledgerPostingId"];
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("BR:3" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
