private void btnView_Click(object sender, EventArgs e)
{
try
{
AddGrdListRows();
DateTime dt = UpdateTable.getSQLDateTime();
if (txtBankCode.Text.Trim().Length == 0 || dtRecDate.Value > dt || dtRecDate.Value < FYStartTIme)
{
MessageBox.Show("Fill Bank Ac code or Date Properly");
return;
}
decimal bookbal = LedgerDB.getTotalBookBalanceOfBankAc(FYStartTIme, dtRecDate.Value, txtBankCode.Text);
decimal TotDr = 0;
decimal TotCr = 0;
if (bookbal < 0)
{
grdList.Rows[0].Cells["Credit"].Value = bookbal * (-1);
TotCr = bookbal * (-1);
}
else
{
grdList.Rows[0].Cells["Debit"].Value = bookbal;
TotDr = bookbal;
}
grdList.Rows[1].Cells["Debit"].Value = ReceiptVoucherDB.getTotalNotClearedDepositeOfBankAcForBRReport(FYStartTIme, dtRecDate.Value, txtBankCode.Text);
TotDr = TotDr + Convert.ToDecimal(grdList.Rows[1].Cells["Debit"].Value);
grdList.Rows[2].Cells["Credit"].Value = PaymentVoucherDB.getTotalNotClearedPaymentOfBankAcForBRReport(FYStartTIme, dtRecDate.Value, txtBankCode.Text);
TotCr = TotCr + Convert.ToDecimal(grdList.Rows[2].Cells["Credit"].Value);
//string str = grdList.Rows[3].Cells["Credit"].Value.ToString();
decimal tot = TotDr - TotCr;
if (tot < 0)
{
grdList.Rows[3].Cells["Credit"].Value = tot;
}
else
{
grdList.Rows[3].Cells["Debit"].Value = tot;
}
grdList.Visible = true;
grdVoucherList.Rows.Clear();
grdVoucherList.Visible = false;
btnClose.Visible = false;
}
catch (Exception ex)
{
}
}
private void btnVieDetails_Click(object sender, EventArgs e)
{
//--------
try
{
if (cmbFYID.SelectedIndex == -1)
{
MessageBox.Show("Select Financial Year");
return;
}
string[] str = cmbFYID.SelectedItem.ToString().Split(':');
string s = str[0];
string ss = str[1];
string sss = str[2];
DateTime FYstartDate = Convert.ToDateTime(ss.Trim());
DateTime FYEndDate = Convert.ToDateTime(sss.Trim());
if (dtFromDate.Value.Date < FYstartDate.Date || dtToDate.Value.Date > FYEndDate.Date ||
dtToDate.Value.Date < FYstartDate.Date || dtFromDate.Value.Date > FYEndDate.Date)
{
MessageBox.Show("Dates should be within the FY");
return;
}
if (dtFromDate.Value.Date > dtToDate.Value.Date)
{
MessageBox.Show("From / To Date mismatch");
return;
}
balance = "";
grdList.Visible = true;
grdList.Rows.Clear();
btnExportToExcel.Visible = true;
TxtSearchNamr.Text = "";
lblSearch.Visible = true;
TxtSearchNamr.Visible = true;
btnCancel.Visible = true;
btnClose.PerformClick();
List <string> testList = new List <string>();
btnExportToExcel.Visible = true;
// btnExportToPDF.Visible = true;
btnCancel.Visible = true;
LedgerDB ldb = new LedgerDB();
//get all OB for all account at FY start
List <AccountOBdetail> accObList = AccountOBDB.getAccountOBListForPericualrFY(s); //10 rows
//Accountwise debit total and credit total till from date
List <ledger> ledgerListFromdt = LedgerDB.getAccountWiseDrCrTotTillFromDate(FYstartDate, dtFromDate.Value); //12rows
foreach (ledger ldg in ledgerListFromdt)
{
AccountOBdetail accList = accObList.FirstOrDefault(acc => acc.AccountCode == ldg.AccountCode);
if (accList == null) //If New Account found
{
AccountOBdetail accNew = new AccountOBdetail();
accNew.AccountCode = ldg.AccountCode;
accNew.AccountName = ldg.AccountName;
accNew.OBValue = (ldg.DebitAmnt - ldg.CreditAmnt);
accObList.Add(accNew);
testList.Add(ldg.AccountCode);
}
}
foreach (AccountOBdetail acc in accObList)
{
ledger ldg = ledgerListFromdt.FirstOrDefault(ldger => ldger.AccountCode == acc.AccountCode);
if (ldg != null && !testList.Contains(ldg.AccountCode))
{
acc.OBValue = acc.OBValue + (ldg.DebitAmnt - ldg.CreditAmnt); //updating new ob value till from date
}
}
List <ledger> ledgerListwithinperiod = LedgerDB.getAccountWiseDrCrTotWithinPeriod(dtFromDate.Value, dtToDate.Value);
//List<ledger> LedgerList = ldb.getFilteredledger(FYstartDate, dtFromDate.Value, dtToDate.Value, txtBankAccountCode.Text);
TotDrAmntBal = 0;
TotCrAmntBal = 0;
TotOBValue = 0;
TotDebAmnt = 0;
TotCredAmnt = 0;
int i = 0;
//List<AccountOBdetail> acclIst = accObList.OrderBy(acc => acc.AccountName).ToList();
foreach (AccountOBdetail acc in accObList)
{
i++;
grdList.Rows.Add();
grdList.Rows[grdList.RowCount - 1].Cells["SerialNo"].Value = grdList.RowCount;
grdList.Rows[grdList.RowCount - 1].Cells["AccountCode"].Value = acc.AccountCode;
grdList.Rows[grdList.RowCount - 1].Cells["AccountName"].Value = acc.AccountName;
grdList.Rows[grdList.RowCount - 1].Cells["OpeningBalance"].Value = acc.OBValue;
TotOBValue = TotOBValue + acc.OBValue;
ledger ldgInPeriod = ledgerListwithinperiod.FirstOrDefault(ldgr => ldgr.AccountCode == acc.AccountCode);
if (ldgInPeriod != null)
{
grdList.Rows[grdList.RowCount - 1].Cells["DebitAmount"].Value = ldgInPeriod.DebitAmnt; //withing period total debit amount
grdList.Rows[grdList.RowCount - 1].Cells["CreditAmout"].Value = ldgInPeriod.CreditAmnt; ////withing period total credit amount
TotDebAmnt = TotDebAmnt + ldgInPeriod.DebitAmnt;
TotCredAmnt = TotCredAmnt + ldgInPeriod.CreditAmnt;
grdList.Rows[grdList.RowCount - 1].Cells["gBalance"].Value = acc.OBValue + (ldgInPeriod.DebitAmnt - ldgInPeriod.CreditAmnt);
}
else
{
//No transaction found within time period
grdList.Rows[grdList.RowCount - 1].Cells["DebitAmount"].Value = Convert.ToDecimal(0);
grdList.Rows[grdList.RowCount - 1].Cells["CreditAmout"].Value = Convert.ToDecimal(0);
grdList.Rows[grdList.RowCount - 1].Cells["gBalance"].Value = acc.OBValue;
}
decimal balancetst = Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["gBalance"].Value);
if (balancetst > 0)
{
grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value = balancetst;
grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value = Convert.ToDecimal(0);
}
else
{
grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value = balancetst * (-1);
grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value = Convert.ToDecimal(0);
}
TotDrAmntBal = TotDrAmntBal + Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value);
TotCrAmntBal = TotCrAmntBal + Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value);
}
foreach (ledger ldgrnew in ledgerListwithinperiod)
{
AccountOBdetail accList = accObList.FirstOrDefault(acc => acc.AccountCode == ldgrnew.AccountCode);
if (accList == null) //If New Account found OB is nill
{
i++;
grdList.Rows.Add();
grdList.Rows[grdList.RowCount - 1].Cells["SerialNo"].Value = grdList.RowCount;
grdList.Rows[grdList.RowCount - 1].Cells["AccountCode"].Value = ldgrnew.AccountCode;
grdList.Rows[grdList.RowCount - 1].Cells["AccountName"].Value = ldgrnew.AccountName;
grdList.Rows[grdList.RowCount - 1].Cells["OpeningBalance"].Value = Convert.ToDecimal(0);
grdList.Rows[grdList.RowCount - 1].Cells["DebitAmount"].Value = ldgrnew.DebitAmnt; //withing period total debit amount
grdList.Rows[grdList.RowCount - 1].Cells["CreditAmout"].Value = ldgrnew.CreditAmnt; ////withing period total credit amount
//TotOBValue = TotOBValue + acc.OBValue;
TotDebAmnt = TotDebAmnt + ldgrnew.DebitAmnt;
TotCredAmnt = TotCredAmnt + ldgrnew.CreditAmnt;
grdList.Rows[grdList.RowCount - 1].Cells["gBalance"].Value = (ldgrnew.DebitAmnt - ldgrnew.CreditAmnt);
decimal balancetst = Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["gBalance"].Value);
if (balancetst > 0)
{
grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value = balancetst;
grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value = Convert.ToDecimal(0);
}
else
{
grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value = balancetst * (-1);
grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value = Convert.ToDecimal(0);
}
TotDrAmntBal = TotDrAmntBal + Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value);
TotCrAmntBal = TotCrAmntBal + Convert.ToDecimal(grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value);
}
}
if (i == grdList.Rows.Count)
{
grdList.Rows.Add();
grdList.Rows[grdList.RowCount - 1].Cells["AccountName"].Value = "Total";
grdList.Rows[grdList.RowCount - 1].Cells["OpeningBalance"].Value = TotOBValue;
grdList.Rows[grdList.RowCount - 1].Cells["DebitAmount"].Value = TotDebAmnt;
grdList.Rows[grdList.RowCount - 1].Cells["CreditAmout"].Value = TotCredAmnt;
grdList.Rows[grdList.RowCount - 1].Cells["CreditBalance"].Value = TotCrAmntBal < 0 ? TotCrAmntBal * (-1) : TotCrAmntBal;
grdList.Rows[grdList.RowCount - 1].Cells["DebitBalance"].Value = TotDrAmntBal < 0 ? TotDrAmntBal * (-1) : TotDrAmntBal;
}
chktransacted.Checked = false;
chktransacted.Checked = true;
}
catch (Exception ex)
{
MessageBox.Show("Check All ");
}
}
private void grdList_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
try
{
string colName = grdList.Columns[e.ColumnIndex].Name;
if (colName == "Details" && e.RowIndex != grdList.Rows.Count - 1)
{
string accode = grdList.Rows[e.RowIndex].Cells["AccountCode"].Value.ToString();
string acname = grdList.Rows[e.RowIndex].Cells["AccountName"].Value.ToString();
List <ledger> ledgerList = LedgerDB.GetLedgerDetailsPerCustomer(accode, dtFromDate.Value, dtToDate.Value);
txtAcCode.Text = accode;
txtAcName.Text = acname;
txtFromDate.Text = dtFromDate.Value.ToString("dd-MM-yyyy");
txtToDate.Text = dtToDate.Value.ToString("dd-MM-yyyy");
decimal TotDrAmnt = 0;
decimal TotCrAmnt = 0;
int i = 0;
//For OB
balance = "";
grdLedger.Rows.Add();
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Sno"].Value = grdLedger.RowCount;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDocumentID"].Value = "OB";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherNo"].Value = 0;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherDate"].Value = dtFromDate.Value;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["TransactionACName"].Value = "Opening Balance";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Narration"].Value = "";
decimal ob = Convert.ToDecimal(grdList.Rows[e.RowIndex].Cells["OpeningBalance"].Value.ToString());
if (ob >= 0)
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDebitAmount"].Value = ob;
TotDrAmnt = TotDrAmnt + ob;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value = Convert.ToDecimal(0);
}
else
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDebitAmount"].Value = Convert.ToDecimal(0);
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value = ob * (-1);
TotCrAmnt = TotCrAmnt + (ob * (-1));
}
foreach (ledger ldg in ledgerList)
{
i++;
grdLedger.Rows.Add();
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Sno"].Value = grdLedger.RowCount;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDocumentID"].Value = ldg.DocumentID;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherNo"].Value = ldg.VoucherNo;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherDate"].Value = ldg.VoucherDate;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["TransactionACName"].Value = ldg.TransactionACName;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Narration"].Value = ldg.Narration;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDebitAmount"].Value = ldg.DebitAmnt;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value = ldg.CreditAmnt;
TotCrAmnt = TotCrAmnt + ldg.CreditAmnt;
TotDrAmnt = TotDrAmnt + ldg.DebitAmnt;
}
if (i == ledgerList.Count)
{
grdLedger.Rows.Add();
//////var BtnCell = grdLedger.Rows[grdLedger.RowCount - 1].Cells["Ledger"];
//////BtnCell.Value = "";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherDate"].Value = dtToDate.Value.ToString("yyyy-MM-dd");
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Narration"].Value = "Closing Balance";
if (TotDrAmnt > TotCrAmnt)
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value = TotDrAmnt - TotCrAmnt;
balance = balance + "Credit:" + (TotDrAmnt - TotCrAmnt);
}
else if (TotDrAmnt < TotCrAmnt)
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDebitAmount"].Value = TotCrAmnt - TotDrAmnt;
balance = balance + "Debit:" + (TotCrAmnt - TotDrAmnt);
}
grdLedger.Rows.Add();
grdLedger.Rows[grdLedger.RowCount - 1].Cells["TransactionACName"].Value = "Total";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDebitAmount"].Value = TotCrAmnt > TotDrAmnt ? TotCrAmnt : TotDrAmnt;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value = TotCrAmnt > TotDrAmnt ? TotCrAmnt : TotDrAmnt;
balance = balance + ";" + grdLedger.Rows[grdLedger.RowCount - 1].Cells["gCreditAmount"].Value.ToString();
}
pnlLedger.Visible = true;
grdLedger.Visible = true;
btnExportToExcelLdg.Visible = true;
btnExprtToPDFLdg.Visible = true;
btnClose.Visible = true;
grdList.Visible = false;
chktransacted.Visible = false;
lblSearch.Visible = false;
TxtSearchNamr.Visible = false;
btnCancel.Visible = false;
btnExportToExcel.Visible = false;
// btnExportToPDF.Visible = false;
btnExit.Visible = false;
}
}
catch (Exception ex)
{
MessageBox.Show("cellclick error");
}
}
private void grdList_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
{
//--------
string[] str = cmbFY.SelectedItem.ToString().Split(':');
string s = str[0];
string ss = str[1];
string sss = str[2];
DateTime FYstartDate = Convert.ToDateTime(ss.Trim());
DateTime FYEndDate = Convert.ToDateTime(sss.Trim());
//--------
string colName = grdTrialBalance.Columns[e.ColumnIndex].Name;
if (colName.Equals("Ledger") && e.RowIndex != (grdTrialBalance.Rows.Count - 1))
{
try
{
pnlsearch.Visible = false;
btnExit.Visible = false;
//txtSearchPu.Text = "";
string[] yearStr = cmbFY.SelectedItem.ToString().Split(':');
DateTime fromDate = Convert.ToDateTime(yearStr[1].Trim());
DateTime todate = dtTrialBalanceDate.Value;
string AcCode = grdTrialBalance.Rows[e.RowIndex].Cells["AccountCode"].Value.ToString();
string AcName = grdTrialBalance.Rows[e.RowIndex].Cells["AccountName"].Value.ToString();
grdLedger.Rows.Clear();
LedgerDB ldb = new LedgerDB();
List <ledger> LedgerList = ldb.getFilteredledger(FYstartDate, fromDate, todate, AcCode);
decimal TotDrAmnt = 0;
decimal TotCrAmnt = 0;
int i = 0;
foreach (ledger ldg in LedgerList)
{
i++;
grdLedger.Rows.Add();
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Sno"].Value = grdLedger.RowCount;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["gDocumentID"].Value = ldg.DocumentID;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherNo"].Value = ldg.VoucherNo;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherDate"].Value = ldg.VoucherDate;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["TransactionACName"].Value = ldg.TransactionACName;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Narration"].Value = ldg.Narration;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["DebitAmount"].Value = ldg.DebitAmnt;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["CreditAmount"].Value = ldg.CreditAmnt;
TotCrAmnt = TotCrAmnt + ldg.CreditAmnt;
TotDrAmnt = TotDrAmnt + ldg.DebitAmnt;
}
if (i == LedgerList.Count)
{
grdLedger.Rows.Add();
//////var BtnCell = grdLedger.Rows[grdLedger.RowCount - 1].Cells["Ledger"];
//////BtnCell.Value = "";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["VoucherDate"].Value = todate;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["Narration"].Value = "Closing Balance";
if (TotDrAmnt > TotCrAmnt)
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["CreditAmount"].Value = TotDrAmnt - TotCrAmnt;
balance = balance + "Credit:" + (TotDrAmnt - TotCrAmnt);
}
else if (TotDrAmnt < TotCrAmnt)
{
grdLedger.Rows[grdLedger.RowCount - 1].Cells["DebitAmount"].Value = TotCrAmnt - TotDrAmnt;
balance = balance + "Debit:" + (TotCrAmnt - TotDrAmnt);
}
grdLedger.Rows.Add();
grdLedger.Rows[grdLedger.RowCount - 1].Cells["TransactionACName"].Value = "Total";
grdLedger.Rows[grdLedger.RowCount - 1].Cells["DebitAmount"].Value = TotCrAmnt > TotDrAmnt ? TotCrAmnt : TotDrAmnt;
grdLedger.Rows[grdLedger.RowCount - 1].Cells["CreditAmount"].Value = TotCrAmnt > TotDrAmnt ? TotCrAmnt : TotDrAmnt;
balance = balance + ";" + grdLedger.Rows[grdLedger.RowCount - 1].Cells["CreditAmount"].Value.ToString();
}
pnlLedger.Visible = true;
grdLedger.Visible = true;
btnClose.Visible = true;
txtAcCode.Text = AcCode;
txtAcName.Text = AcName;
txtFromDate.Text = fromDate.ToString("dd-MM-yyyy");
txtToDate.Text = todate.ToString("dd-MM-yyyy");
grdTrialBalance.Visible = false;
btnCancel.Visible = false;
btnExportToExcel.Visible = false;
btnExportToPDF.Visible = false;
}
catch (Exception ex)
{
}
}
}