/// <summary>
/// Function to print report
/// </summary>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
public void Print(DateTime fromDate, DateTime toDate)
{
try
{
FinancialStatementSP spFinance = new FinancialStatementSP();
AccountGroupSP spAccountGroup = new AccountGroupSP();
DataSet dsAccountGroup = new DataSet();
DataTable dtblAccountGroup = spAccountGroup.AccountGroupReportFill(fromDate, toDate);
string strSum = lblBalanceTotal.Text;
DataTable dtblSum = new DataTable();
dtblSum.Columns.Add("Sum", typeof(string));
DataRow dr = dtblSum.NewRow();
dr[0] = strSum;
dtblSum.Rows.InsertAt(dr, 0);
DataTable dtblCompany = spFinance.FundFlowReportPrintCompany(1);
dsAccountGroup.Tables.Add(dtblAccountGroup);
dsAccountGroup.Tables.Add(dtblCompany);
dsAccountGroup.Tables.Add(dtblSum);
////frmReport //frmReport = new frmReport();
//frmReport.MdiParent = formMDI.MDIObj;
// frmreport.AccountGroup(dsAccountGroup);
}
catch (Exception ex)
{
MessageBox.Show("AGR:1" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to fill the report in grid
/// </summary>
public void GridFill()
{
decimal decBalanceTotal = 0;
try
{
AccountGroupSP spAccountGroup = new AccountGroupSP();
DataTable dtbl = new DataTable();
dtbl = spAccountGroup.AccountGroupWiseReportViewAll(decAccountGroupId, Convert.ToDateTime(txtFromDate.Text), Convert.ToDateTime(txtToDate.Text));
if (decAccountGroupId == 6)
{
FinancialStatementSP spFinace = new FinancialStatementSP();
CalculationMethod();
decimal dcOpeninggStock = spFinace.StockValueGetOnDate(PublicVariables._dtFromDate, calculationMethod, true, true);
dcOpeninggStock = Math.Round(dcOpeninggStock, PublicVariables._inNoOfDecimalPlaces);
int inRowCount = dtbl.Rows.Count;
DataRow dr = dtbl.NewRow();
dr["SlNo"] = inRowCount + 1;
dr["accountGroupId"] = -2;
dr["ledgerId"] = 0;
dr["name"] = "Opening Stock";
if (dcOpeninggStock >= 0)
{
dr["OpeningBalance"] = dcOpeninggStock + "Dr";
dr["Balance"] = dcOpeninggStock + "Dr";
}
else
{
dr["OpeningBalance"] = dcOpeninggStock + "Cr";
dr["Balance"] = dcOpeninggStock + "Cr";
}
dr["debit"] = Math.Round(Convert.ToDecimal(0.00000), PublicVariables._inNoOfDecimalPlaces).ToString() + ".00";
dr["credit"] = Math.Round(Convert.ToDecimal(0.00000), PublicVariables._inNoOfDecimalPlaces).ToString() + ".00";
dr["balance1"] = dcOpeninggStock;
dtbl.Rows.InsertAt(dr, inRowCount);
}
dgvAccountGroupWiseReport.DataSource = dtbl;
if (dtbl.Rows.Count > 0)
{
for (int i = 0; i < dgvAccountGroupWiseReport.RowCount; i++)
{
decBalanceTotal += Convert.ToDecimal(dgvAccountGroupWiseReport.Rows[i].Cells["balance1"].Value.ToString());
}
}
if (decBalanceTotal < 0)
{
decBalanceTotal = -1 * decBalanceTotal;
lblBalanceTotal.Text = decBalanceTotal.ToString() + "Cr";
}
else
{
lblBalanceTotal.Text = decBalanceTotal.ToString() + "Dr";
}
}
catch (Exception ex)
{
MessageBox.Show("AGWREP02:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function for Print
/// </summary>
/// <param name="toDate"></param>
public void Print(DateTime toDate)
{
try
{
FinancialStatementSP spFinancial = new FinancialStatementSP();
DataSet destBalance = getdataset();
////frmReport //frmReport = new frmReport();
//frmReport.MdiParent = formMDI.MDIObj;
// frmreport.BalanceSheetReportPrint(destBalance);
}
catch (Exception ex)
{
MessageBox.Show("BS :4" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to convert datatable to dataset
/// </summary>
/// <returns></returns>
public DataSet GetDataSet()
{
DataSet dsProfit = new DataSet();
try
{
FinancialStatementSP spfinancial = new FinancialStatementSP();
DataTable dtblProfit = GetDataTable();
DataTable dtblCompany = new DataTable();
dtblCompany = spfinancial.ProfitAndLossReportPrintCompany(1);//(PublicVariables._decCurrentCompanyId);
dsProfit.Tables.Add(dtblProfit);
dsProfit.Tables.Add(dtblCompany);
}
catch (Exception ex)
{
MessageBox.Show("PAL :03" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(dsProfit);
}
/// <summary>
/// Function to get dataset for printing the report
/// </summary>
/// <returns></returns>
public DataSet getdataset1()
{
DataSet dsFundFlow = new DataSet();
try
{
FinancialStatementSP spfinancial = new FinancialStatementSP();
DataTable dtblFund = dtblageing1();
DataTable dtblCompany = new DataTable();
dtblCompany = spfinancial.FundFlowReportPrintCompany(1);//(PublicVariables._decCurrentCompanyId);
dsFundFlow.Tables.Add(dtblFund);
dsFundFlow.Tables.Add(dtblCompany);
}
catch (Exception ex)
{
MessageBox.Show("AR7:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(dsFundFlow);
}
/// <summary>
/// Function to Add Dataset
/// </summary>
/// <returns></returns>
public DataSet GetDataSet()
{
DataSet dsCashflow = new DataSet();
try
{
FinancialStatementSP spFinancial = new FinancialStatementSP();
DataTable dtblCashflow = GetDataTable();
DataTable dtblCompany = new DataTable();
dtblCompany = spFinancial.CashflowReportPrintCompany(1);
dsCashflow.Tables.Add(dtblCashflow);
dsCashflow.Tables.Add(dtblCompany);
}
catch (Exception ex)
{
MessageBox.Show("CF:04" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(dsCashflow);
}
/// <summary>
/// Function to create datasets
/// </summary>
/// <returns></returns>
public DataSet getdataset()
{
FinancialStatementSP spfinancial = new FinancialStatementSP();
DataSet dsBudget = new DataSet();
try
{
DataTable dtblbudget = GetDataTable();
DataTable dtblCompany = new DataTable();
dtblCompany = spfinancial.FundFlowReportPrintCompany(1);
dsBudget.Tables.Add(dtblbudget);
dsBudget.Tables.Add(dtblCompany);
}
catch (Exception ex)
{
MessageBox.Show("BV:04" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(dsBudget);
}
/// <summary>
/// On 'Print' button click to print
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPrint_Click(object sender, EventArgs e)
{
try
{
if (dgvStockJournalReport.RowCount > 0)
{
//frmReport frmReportObj = new frmReport();
//frmReport frmReportOpen = Application.OpenForms["frmReport"] as frmReport;
DataSet ds = new DataSet();
FinancialStatementSP spfinancial = new FinancialStatementSP();
StockJournalMasterSP spStockJournal = new StockJournalMasterSP();
DataTable dtblCompany = new DataTable();
DataTable dtblStockJournal = new DataTable();
dtblCompany = spfinancial.DayBookReportPrintCompany();
dtblStockJournal = spStockJournal.StockJournalReportGrideFill(Convert.ToDateTime(txtFromDate.Text), Convert.ToDateTime(txtToDate.Text), Convert.ToDecimal(cmbVoucherType.SelectedValue.ToString()), txtVoucherNo.Text, txtProductCode.Text, txtProductName.Text);
ds.Tables.Add(dtblCompany);
ds.Tables.Add(dtblStockJournal);
//if (frmReportOpen == null)
//{
// //frmReportObj.MdiParent = formMDI.MDIObj;
// // frmReportObj.StockJournalReportPrint(ds);
//}
//else
//{
// frmReportOpen.MdiParent = formMDI.MDIObj;
// frmReportOpen.StockJournalReportPrint(ds);
// if (frmReportOpen.WindowState == FormWindowState.Minimized)
// {
// frmReportOpen.WindowState = FormWindowState.Normal;
// }
//}
}
else
{
Messages.InformationMessage("No data found");
}
}
catch (Exception ex)
{
MessageBox.Show("SRR10:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to Convert DataTable to DataSet
/// </summary>
/// <returns></returns>
public DataSet GetdataSet()
{
FinancialStatementSP spfinancial = new FinancialStatementSP();
DataSet dsFundFlow = new DataSet();
try
{
DataTable dtblFund = GetDataTable();
DataTable dtblWC = GetDatagrid2();
DataTable dtblCompany = new DataTable();
dtblCompany = spfinancial.FundFlowReportPrintCompany(1);
dsFundFlow.Tables.Add(dtblFund);
dsFundFlow.Tables.Add(dtblCompany);
dsFundFlow.Tables.Add(dtblWC);
}
catch (Exception ex)
{
MessageBox.Show("FF:03" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
return(dsFundFlow);
}
/// <summary>
/// Function to fill Datagridview after calculation
/// </summary>
public void GridFill()
{
try
{
if (!isFormLoad)
{
DateValidation objValidation = new DateValidation();
objValidation.DateValidationFunction(txtFundFlowFromDate);
if (txtFundFlowFromDate.Text == string.Empty)
{
txtFundFlowFromDate.Text = PublicVariables._dtFromDate.ToString("dd-MMM-yyyy");
}
objValidation.DateValidationFunction(txtFundflowToDate);
if (txtFundflowToDate.Text == string.Empty)
{
txtFundflowToDate.Text = PublicVariables._dtToDate.ToString("dd-MMM-yyyy");
}
//-------------------------------------First gridfill-------------------------------------//
DateTime strFromDate = Convert.ToDateTime(txtFundFlowFromDate.Text.ToString());
DateTime strTodate = Convert.ToDateTime(txtFundflowToDate.Text.ToString());
FinancialStatementSP spFinancial = new FinancialStatementSP();
DataSet dsetFinancial = new DataSet();
SettingsInfo InfoSettings = new SettingsInfo();
SettingsSP SpSettings = new SettingsSP();
//--------------- Selection Of Calculation Method According To Settings ------------------//
if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "FIFO")
{
strCalculationMethod = "FIFO";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Average Cost")
{
strCalculationMethod = "Average Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "High Cost")
{
strCalculationMethod = "High Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Low Cost")
{
strCalculationMethod = "Low Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Last Purchase Rate")
{
strCalculationMethod = "Last Purchase Rate";
}
dsetFinancial = spFinancial.FundFlow(strFromDate, strTodate);
DataTable dtbl = new DataTable();
Font newFont = new Font(dgvFundFlow.Font, FontStyle.Bold);
CurrencyInfo InfoCurrency = new CurrencyInfo();
CurrencySP SpCurrency = new CurrencySP();
InfoCurrency = SpCurrency.CurrencyView(1);
int inDecimalPlaces = InfoCurrency.NoOfDecimalPlaces;
dgvFundFlow.Rows.Clear();
////-------------------Source--------------------------------------------------------------
dtbl = dsetFinancial.Tables[0];
foreach (DataRow rw in dtbl.Rows)
{
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtSource"].Value = rw["Name"].ToString();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = rw["Balance"].ToString();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtgroupId1"].Value = rw["ID"].ToString();
}
decimal dcTotalAsset = 0;
if (dtbl.Rows.Count > 0)
{
dcTotalAsset = decimal.Parse(dtbl.Compute("Sum(Balance)", string.Empty).ToString());
}
////-----------------Application------------------------------------------------------------
dtbl = new DataTable();
dtbl = dsetFinancial.Tables[1];
int index = 0;
foreach (DataRow rw in dtbl.Rows)
{
if (index < dgvFundFlow.Rows.Count)
{
dgvFundFlow.Rows[index].Cells["dgvtxtApplication"].Value = rw["Name"].ToString();
dgvFundFlow.Rows[index].Cells["dgvtxtAmount2"].Value = rw["Balance"].ToString();
dgvFundFlow.Rows[index].Cells["dgvtxtgroupId2"].Value = rw["ID"].ToString();
}
else
{
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtApplication"].Value = rw["Name"].ToString();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = rw["Balance"].ToString();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtgroupId2"].Value = rw["ID"].ToString();
}
index++;
}
decimal dcTotalLiability = 0;
if (dtbl.Rows.Count > 0)
{
dcTotalLiability = Convert.ToDecimal(dtbl.Compute("Sum(Balance)", string.Empty).ToString());
}
//-------------------- Closing Stock -----------------------// With Calculation
dcClosingStock = spFinancial.StockValueGetOnDate(Convert.ToDateTime(txtFundflowToDate.Text), strCalculationMethod, false, false);
dcClosingStock = Math.Round(dcClosingStock, inDecimalPlaces);
//---------------------Opening Stock-----------------------
decimal dcOpeninggStock = spFinancial.StockValueGetOnDate(Convert.ToDateTime(txtFundFlowFromDate.Text), strCalculationMethod, true, false);
//------------- Profit Or Loss -----------// With Calculation
decimal dcProfit = 0;
DataSet dsetProfitAndLoss = new DataSet();
dsetProfitAndLoss = spFinancial.ProfitAndLossAnalysisUpToaDateForBalansheet(Convert.ToDateTime(txtFundFlowFromDate.Text), Convert.ToDateTime(txtFundflowToDate.Text));
DataTable dtblProfit = new DataTable();
dtblProfit = dsetProfitAndLoss.Tables[0];
for (int i = 0; i < dsetProfitAndLoss.Tables.Count; ++i)
{
dtbl = dsetProfitAndLoss.Tables[i];
decimal dcSum = 0;
if (i == 0 || (i % 2) == 0)
{
if (dtbl.Rows.Count > 0)
{
dcSum = Convert.ToDecimal(dtbl.Compute("Sum(Debit)", string.Empty).ToString());
dcProfit = dcProfit - dcSum;
}
}
else
{
if (dtbl.Rows.Count > 0)
{
dcSum = Convert.ToDecimal(dtbl.Compute("Sum(Credit)", string.Empty).ToString());
dcProfit = dcProfit + dcSum;
}
}
}
//---------------------NetProfit/NetLoss Calculation--------------------------
dcProfit = dcProfit + dcClosingStock - dcOpeninggStock;
if (dcProfit > 0)
{
//------------ Liability ------------//
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtSource"].Value = "Net Profit";
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = Math.Round(dcProfit, inDecimalPlaces);
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount1"].Style.ForeColor = Color.Green;
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtSource"].Style.ForeColor = Color.Green;
//dcTotalLiability += dcProfit;
}
else
{
//-------------- Asset ---------------//
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtApplication"].Value = "Net Loss";
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = -Math.Round(dcProfit, inDecimalPlaces);
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount2"].Style.ForeColor = Color.Red;
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtApplication"].Style.ForeColor = Color.Red;
}
if (dcProfit > 0)
{
dcTotalAsset += (dcProfit);
}
else
{
dcTotalLiability += (-dcProfit);
}
decimal dcTotalValue = dcTotalAsset;
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = "_______________________";
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = "_______________________";
dgvFundFlow.Rows.Add();
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtApplication"].Value = "Total";
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtSource"].Value = "Total";
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = Math.Round((dcTotalAsset), inDecimalPlaces);
dgvFundFlow.Rows[dgvFundFlow.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = Math.Round((dcTotalLiability), inDecimalPlaces);
//----------------------------------------Second gridfill----------------------------------------------
//--------------------------------------Current Assets-------------------------------------------
Font newFont2 = new Font(dgvFundFlow2.Font, FontStyle.Bold);
dgvFundFlow2.Rows.Clear();
dtbl = dsetFinancial.Tables[3];
decimal decWC = 0;
foreach (DataRow rw in dtbl.Rows)
{
dgvFundFlow2.Rows.Add();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtgroupId"].Value = rw["ID"].ToString();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtParticulars"].Value = rw["Name"].ToString();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtOpeningBalance"].Value = rw["OpeningBalance"].ToString() + "Dr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtop"].Value = rw["OpeningBalance"].ToString();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = rw["ClosingBalance"].ToString();
decimal decOB = Convert.ToDecimal(rw["OpeningBalance"].ToString());
decimal decCB = Convert.ToDecimal(rw["ClosingBalance"].ToString());
decCB = decCB + dcClosingStock;
if (decCB > 0)
{
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = Math.Round(decCB, inDecimalPlaces).ToString() + "Dr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = Math.Round(decCB, inDecimalPlaces).ToString();
}
else
{
decCB = -1 * decCB;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = Math.Round(decCB, inDecimalPlaces).ToString() + "Dr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = Math.Round(decCB, inDecimalPlaces).ToString();
}
decWC = decOB - decCB;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtWorkingCapitalIncrease"].Value = Math.Round(decWC, inDecimalPlaces).ToString();
}
//--------------------------------------Current Liability-------------------------------------------
dtbl = dsetFinancial.Tables[5];
decimal decWCCL = 0;
foreach (DataRow rw in dtbl.Rows)
{
dgvFundFlow2.Rows.Add();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtgroupId"].Value = rw["ID"].ToString();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtParticulars"].Value = rw["Name"].ToString();
decimal decOp = Convert.ToDecimal(rw["OpeningBalance"].ToString());
decimal decCb = Convert.ToDecimal(rw["ClosingBalance"].ToString());
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtOpeningBalance"].Value = decOp.ToString() + "Cr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtop"].Value = rw["OpeningBalance"].ToString();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = rw["ClosingBalance"].ToString();
if (decCb > 0)
{
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = decCb.ToString() + "Cr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = decCb.ToString();
}
else
{
decCb = -1 * decCb;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = decCb.ToString() + "Cr";
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtcb"].Value = decCb.ToString();
}
decimal decOB = Convert.ToDecimal(rw["OpeningBalance"].ToString());
decimal decCB = Convert.ToDecimal(rw["ClosingBalance"].ToString());
decWCCL = decOp - decCb;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtWorkingCapitalIncrease"].Value = decWCCL.ToString();
}
//-----------------Calculating Working capital---------------
decimal decOpen = 0;
decimal decClose = 0;
decimal decWork = 0;
decOpen = Convert.ToDecimal(dgvFundFlow2.Rows[0].Cells[3].Value.ToString()) - Convert.ToDecimal(dgvFundFlow2.Rows[1].Cells[3].Value.ToString());
decClose = Convert.ToDecimal(dgvFundFlow2.Rows[0].Cells[5].Value.ToString()) - Convert.ToDecimal(dgvFundFlow2.Rows[1].Cells[5].Value.ToString());
decWork = Convert.ToDecimal(dgvFundFlow2.Rows[0].Cells[6].Value.ToString()) - Convert.ToDecimal(dgvFundFlow2.Rows[1].Cells[6].Value.ToString());
decimal decW1 = Convert.ToDecimal(dgvFundFlow2.Rows[0].Cells[6].Value.ToString());
decimal decW2 = Convert.ToDecimal(dgvFundFlow2.Rows[1].Cells[6].Value.ToString());
dgvFundFlow2.Rows.Add();
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtParticulars"].Value = "Working Capital";
if (decOpen > 0)
{
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtOpeningBalance"].Value = decOpen.ToString() + "Dr";
}
else
{
decOpen *= -1;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtOpeningBalance"].Value = decOpen.ToString() + "Cr";
}
if (decClose > 0)
{
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = decClose.ToString() + "Dr";
}
else
{
decClose *= -1;
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtClosingBalance"].Value = decClose.ToString() + "Cr";
}
dgvFundFlow2.Rows[dgvFundFlow2.Rows.Count - 1].Cells["dgvtxtWorkingCapitalIncrease"].Value = decWork.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("FF:06" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to fill Datagridview
/// </summary>
public void Gridfill()
{
try
{
if (!isFormLoad)
{
DateValidation objValidation = new DateValidation();
objValidation.DateValidationFunction(txtFromDate);
if (txtFromDate.Text == string.Empty)
{
txtFromDate.Text = PublicVariables._dtFromDate.ToString("dd-MMM-yyyy");
}
objValidation.DateValidationFunction(txtToDate);
if (txtToDate.Text == string.Empty)
{
txtToDate.Text = PublicVariables._dtToDate.ToString("dd-MMM-yyyy");
}
Font newFont = new Font(dgvProfitAndLoss.Font, FontStyle.Bold);
CurrencyInfo InfoCurrency = new CurrencyInfo();
CurrencySP SpCurrency = new CurrencySP();
InfoCurrency = SpCurrency.CurrencyView(1);
int inDecimalPlaces = InfoCurrency.NoOfDecimalPlaces;
dgvProfitAndLoss.Rows.Clear();
FinancialStatementSP spFinancial = new FinancialStatementSP();
DataTable dtblFinancial = new DataTable();
DataSet DsetProfitAndLoss = new DataSet();
SettingsInfo infoSettings = new SettingsInfo();
SettingsSP SpSettings = new SettingsSP();
//---------check calculation method
if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "FIFO")
{
calculationMethod = "FIFO";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Average Cost")
{
calculationMethod = "Average Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "High Cost")
{
calculationMethod = "High Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Low Cost")
{
calculationMethod = "Low Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Last Purchase Rate")
{
calculationMethod = "Last Purchase Rate";
}
DsetProfitAndLoss = spFinancial.ProfitAndLossAnalysis(DateTime.Parse(txtFromDate.Text), DateTime.Parse(txtToDate.Text));
//---- Opening Stock
dgvProfitAndLoss.Rows.Add();
decimal dcOpeningStock = spFinancial.StockValueGetOnDate(DateTime.Parse(txtFromDate.Text), DateTime.Parse(txtToDate.Text), calculationMethod, true, false);
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Opening Stock";
if (dcOpeningStock > 0)
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = Math.Round(dcOpeningStock, inDecimalPlaces);
}
else
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = -(Math.Round(dcOpeningStock, inDecimalPlaces));
}
//Closing Stock
decimal dcClosingStock = 0;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Closing Stock";
dcClosingStock = spFinancial.StockValueGetOnDate(DateTime.Parse(txtToDate.Text), calculationMethod, false, false);
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = Math.Round(dcClosingStock, inDecimalPlaces);
/// ---Purchase Account - Debit
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[0];
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Purchase Accounts";
decimal dcPurchaseAccount = 0m;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = decimal.Parse(rw["Debit"].ToString().ToString());
dcPurchaseAccount += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId1"].Value = "11";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcPurchaseAccount.ToString();
//---Sales Account -Credit
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[1];
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Sales Accounts";
decimal dcSalesAccount = 0m;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = decimal.Parse(rw["Credit"].ToString().ToString());
dcSalesAccount += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId2"].Value = "10";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcSalesAccount.ToString();
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows.Add();
//---Direct Expense
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[2];
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Direct Expenses";
decimal dcDirectExpense = 0m;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = Convert.ToDecimal(rw["Debit"].ToString());
dcDirectExpense += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId1"].Value = "13";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcDirectExpense.ToString();
//----Direct Income
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[3];
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Direct Incomes";
decimal dcDirectIncoome = 0m;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = Convert.ToDecimal(rw["Credit"].ToString());
dcDirectIncoome += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId2"].Value = "12";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcDirectIncoome.ToString();
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = "_______________________";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = "_______________________";
dgvProfitAndLoss.Rows.Add();
decimal dcTotalExpense = 0;
decimal dcTotalIncome = 0;
dcTotalExpense = dcOpeningStock + dcPurchaseAccount + dcDirectExpense;
dcTotalIncome = dcClosingStock + dcSalesAccount + dcDirectIncoome;
dcTotalExpense = Math.Round(dcTotalExpense, inDecimalPlaces);
dcTotalIncome = Math.Round(dcTotalIncome, inDecimalPlaces);
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Total";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Total";
decimal dcGrossProfit = 0;
decimal dcGrossLoss = 0;
if (dcTotalExpense > dcTotalIncome)
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcTotalExpense.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcTotalExpense.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtIncome"].Value = "Gross Loss b/d ";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount2"].Value = dcTotalExpense - dcTotalIncome;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount2"].Style.ForeColor = Color.Red;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtIncome"].Style.ForeColor = Color.Red;
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Gross Loss b/d ";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcTotalExpense - dcTotalIncome;
dcGrossLoss = dcTotalExpense - dcTotalIncome;
}
else
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcTotalIncome.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcTotalIncome.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtExpenses"].Value = "Gross Profit c/d ";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount1"].Value = dcTotalIncome - dcTotalExpense;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount1"].Style.ForeColor = Color.Green;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtExpenses"].Style.ForeColor = Color.Green;
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Gross Profit c/d ";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcTotalIncome - dcTotalExpense;
dcGrossProfit = dcTotalIncome - dcTotalExpense;
}
dgvProfitAndLoss.Rows.Add();
///------Indirect Expense
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[4];
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Indirect Expenses";
decimal dcIndirectExpense = 0;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = Convert.ToDecimal(rw["Debit"].ToString());
dcIndirectExpense += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId1"].Value = "15";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcIndirectExpense.ToString();
///---Indirect Income
dtblFinancial = new DataTable();
dtblFinancial = DsetProfitAndLoss.Tables[5];
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Indirect Incomes";
decimal dcIndirectIncome = 0m;
if (dtblFinancial.Rows.Count > 0)
{
foreach (DataRow rw in dtblFinancial.Rows)
{
decimal dcBalance = Convert.ToDecimal(rw["Credit"].ToString());
dcIndirectIncome += dcBalance;
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtGroupId2"].Value = "14";
}
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcIndirectIncome.ToString();
//---- Calculating Grand total
decimal dcGrandTotalExpense = dcGrossLoss + dcIndirectExpense;
decimal dcGrandTotalIncome = dcGrossProfit + dcIndirectIncome;
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = "_______________________";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = "_______________________";
dgvProfitAndLoss.Rows.Add();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtExpenses"].Value = "Grand Total";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtIncome"].Value = "Grand Total";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].DefaultCellStyle.Font = newFont;
if (dcGrandTotalExpense > dcGrandTotalIncome)
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcGrandTotalExpense.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcGrandTotalExpense.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtIncome"].Value = "Net Loss ";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount2"].Value = dcGrandTotalExpense - dcGrandTotalIncome;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtIncome"].Style.ForeColor = Color.Red;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount2"].Style.ForeColor = Color.Red;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].DefaultCellStyle.Font = newFont;
decgranExTotal = dcGrandTotalExpense;
}
else
{
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount1"].Value = dcGrandTotalIncome.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 1].Cells["dgvtxtAmount2"].Value = dcGrandTotalIncome.ToString();
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtExpenses"].Value = "Net Profit";
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount1"].Value = dcGrandTotalIncome - dcGrandTotalExpense;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtExpenses"].Style.ForeColor = Color.Green;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].Cells["dgvtxtAmount1"].Style.ForeColor = Color.Green;
dgvProfitAndLoss.Rows[dgvProfitAndLoss.Rows.Count - 3].DefaultCellStyle.Font = newFont;
decgranIncTotal = dcGrandTotalIncome;
}
if (dgvProfitAndLoss.Columns.Count > 0)
{
dgvProfitAndLoss.Columns["dgvtxtAmount1"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvProfitAndLoss.Columns["dgvtxtAmount2"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
if (inCurrenRowIndex >= 0 && dgvProfitAndLoss.Rows.Count > 0 && inCurrenRowIndex < dgvProfitAndLoss.Rows.Count)
{
if (dgvProfitAndLoss.Rows[inCurrenRowIndex].Cells[inCurrentColunIndex].Visible)
{
dgvProfitAndLoss.CurrentCell = dgvProfitAndLoss.Rows[inCurrenRowIndex].Cells[inCurrentColunIndex];
}
else
{
dgvProfitAndLoss.CurrentCell = dgvProfitAndLoss.Rows[inCurrenRowIndex].Cells["dgvtxtExpenses"];
}
dgvProfitAndLoss.CurrentCell.Selected = true;
}
inCurrenRowIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show("PAL :01" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to fill Datagridview
/// </summary>
public void FillGrid()
{
try
{
if (!isFormLoad)
{
DateValidation objValidation = new DateValidation();
objValidation.DateValidationFunction(txtToDate);
if (txtToDate.Text == string.Empty)
{
txtToDate.Text = PublicVariables._dtToDate.ToString("dd-MMM-yyyy");
}
Font newFont = new Font(dgvReport.Font, FontStyle.Bold);
CurrencyInfo InfoCurrency = new CurrencyInfo();
CurrencySP SpCurrency = new CurrencySP();
InfoCurrency = SpCurrency.CurrencyView(1);
int inDecimalPlaces = InfoCurrency.NoOfDecimalPlaces;
dgvReport.Rows.Clear();
FinancialStatementSP SpFinance = new FinancialStatementSP();
DataSet DsetBalanceSheet = new DataSet();
DataTable dtbl = new DataTable();
SettingsInfo InfoSettings = new SettingsInfo();
SettingsSP SpSettings = new SettingsSP();
//--------------- Selection Of Calculation Method According To Settings ------------------//
if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "FIFO")
{
calculationMethod = "FIFO";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Average Cost")
{
calculationMethod = "Average Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "High Cost")
{
calculationMethod = "High Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Low Cost")
{
calculationMethod = "Low Cost";
}
else if (SpSettings.SettingsStatusCheck("StockValueCalculationMethod") == "Last Purchase Rate")
{
calculationMethod = "Last Purchase Rate";
}
DsetBalanceSheet = SpFinance.BalanceSheet(PublicVariables._dtFromDate, DateTime.Parse(txtToDate.Text));
//------------------- Asset -------------------------------//
dtbl = DsetBalanceSheet.Tables[0];
foreach (DataRow rw in dtbl.Rows)
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = rw["Name"].ToString();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = rw["Balance"].ToString();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["GroupId1"].Value = rw["ID"].ToString();
}
decimal dcTotalAsset = 0;
if (dtbl.Rows.Count > 0)
{
dcTotalAsset = decimal.Parse(dtbl.Compute("Sum(Balance)", string.Empty).ToString());
}
//------------------------ Liability ---------------------//
dtbl = new DataTable();
dtbl = DsetBalanceSheet.Tables[1];
int index = 0;
foreach (DataRow rw in dtbl.Rows)
{
if (index < dgvReport.Rows.Count)
{
dgvReport.Rows[index].Cells["dgvtxtLiability"].Value = rw["Name"].ToString();
dgvReport.Rows[index].Cells["Amount2"].Value = rw["Balance"].ToString();
dgvReport.Rows[index].Cells["GroupId2"].Value = rw["ID"].ToString();
}
else
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = rw["Name"].ToString();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = rw["Balance"].ToString();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["GroupId2"].Value = rw["ID"].ToString();
}
index++;
}
decimal dcTotalLiability = 0;
if (dtbl.Rows.Count > 0)
{
dcTotalLiability = decimal.Parse(dtbl.Compute("Sum(Balance)", string.Empty).ToString());
}
decimal dcClosingStock = SpFinance.StockValueGetOnDate(Convert.ToDateTime(txtToDate.Text), calculationMethod, false, false);
dcClosingStock = Math.Round(dcClosingStock, inDecimalPlaces);
//---------------------Opening Stock---------------------------------------------------------------------------------------------------------------
decimal dcOpeninggStock = SpFinance.StockValueGetOnDate(PublicVariables._dtFromDate, calculationMethod, true, true);
decimal dcProfit = 0;
DataSet dsetProfitAndLoss = new DataSet();
dsetProfitAndLoss = SpFinance.ProfitAndLossAnalysisUpToaDateForBalansheet(PublicVariables._dtFromDate, DateTime.Parse(txtToDate.Text));
DataTable dtblProfit = new DataTable();
dtblProfit = dsetProfitAndLoss.Tables[0];
for (int i = 0; i < dsetProfitAndLoss.Tables.Count; ++i)
{
dtbl = dsetProfitAndLoss.Tables[i];
decimal dcSum = 0;
if (i == 0 || (i % 2) == 0)
{
if (dtbl.Rows.Count > 0)
{
dcSum = decimal.Parse(dtbl.Compute("Sum(Debit)", string.Empty).ToString());
dcProfit = dcProfit - dcSum;
}
}
else
{
if (dtbl.Rows.Count > 0)
{
dcSum = decimal.Parse(dtbl.Compute("Sum(Credit)", string.Empty).ToString());
dcProfit = dcProfit + dcSum;
}
}
}
decimal decCurrentProfitLoss = 0;
decCurrentProfitLoss = dcProfit + (dcClosingStock - dcOpeninggStock);
decimal dcProfitOpening = 0;
DataSet dsetProfitAndLossOpening = new DataSet();
dsetProfitAndLossOpening = SpFinance.ProfitAndLossAnalysisUpToaDateForPreviousYears(PublicVariables._dtFromDate);
DataTable dtblProfitOpening = new DataTable();
dtblProfitOpening = dsetProfitAndLossOpening.Tables[0];
for (int i = 0; i < dsetProfitAndLossOpening.Tables.Count; ++i)
{
dtbl = dsetProfitAndLossOpening.Tables[i];
decimal dcSum = 0;
if (i == 0 || (i % 2) == 0)
{
if (dtbl.Rows.Count > 0)
{
dcSum = decimal.Parse(dtbl.Compute("Sum(Debit)", string.Empty).ToString());
dcProfitOpening = dcProfitOpening - dcSum;
}
}
else
{
if (dtbl.Rows.Count > 0)
{
dcSum = decimal.Parse(dtbl.Compute("Sum(Credit)", string.Empty).ToString());
dcProfitOpening = dcProfitOpening + dcSum;
}
}
}
DataTable dtblProfitLedgerOpening = new DataTable();
dtblProfitLedgerOpening = DsetBalanceSheet.Tables[3];
decimal decProfitLedgerOpening = 0;
foreach (DataRow dRow in dtblProfitLedgerOpening.Rows)
{
decProfitLedgerOpening += decimal.Parse(dRow["Balance"].ToString());
}
DataTable dtblProf = new DataTable();
dtblProf = DsetBalanceSheet.Tables[2];
decimal decProfitLedger = 0;
if (dtblProf.Rows.Count > 0)
{
decProfitLedger = decimal.Parse(dtblProf.Compute("Sum(Balance)", string.Empty).ToString());
}
decimal decTotalProfitAndLoss = 0;
if (dcProfitOpening >= 0)
{
decTotalProfitAndLoss = decProfitLedger;
}
else if (dcProfitOpening < 0)
{
decTotalProfitAndLoss = decProfitLedger;
}
index = 0;
if (dcClosingStock >= 0)
{
//---------- Asset ----------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "Closing Stock";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round(dcClosingStock, inDecimalPlaces);
dcTotalAsset += dcClosingStock;
}
else
{
//--------- Liability ---------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "Closing Stock";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = -(Math.Round(dcClosingStock, inDecimalPlaces));
dcTotalLiability += -dcClosingStock;
}
dgvReport.Rows.Add();
decimal decOpeningOfProfitAndLoss = decProfitLedgerOpening + dcProfitOpening;
decimal decTotalProfitAndLossOverAll = decTotalProfitAndLoss + decOpeningOfProfitAndLoss + decCurrentProfitLoss;
if (decTotalProfitAndLossOverAll <= 0)
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "----------------------------------------";
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
foreach (DataRow dRow in dtblProf.Rows)
{
if (dRow["Name"].ToString() == "Profit And Loss Account")
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = dRow["Name"].ToString();
if (decCurrentProfitLoss < 0)
{
decCurrentProfitLoss = decCurrentProfitLoss * -1;
}
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round(decTotalProfitAndLoss + decCurrentProfitLoss, PublicVariables._inNoOfDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["GroupId1"].Value = dRow["ID"].ToString();
}
}
//-------------- Asset ---------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "Profit And Loss (Opening)";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round(decTotalProfitAndLoss, PublicVariables._inNoOfDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Style.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Style.ForeColor = Color.DarkSlateGray;
//-------------- Asset ---------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "Current Period";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round(decCurrentProfitLoss, PublicVariables._inNoOfDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Style.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Style.ForeColor = Color.DarkSlateGray;
dcTotalAsset = dcTotalAsset + (decCurrentProfitLoss + decTotalProfitAndLoss);
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "----------------------------------------";
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
}
else if (decTotalProfitAndLossOverAll > 0)
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "----------------------------------------";
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
foreach (DataRow dRow in dtblProf.Rows)
{
if (dRow["Name"].ToString() == "Profit And Loss Account")
{
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = dRow[1].ToString();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = Math.Round(decTotalProfitAndLoss + decCurrentProfitLoss, PublicVariables._inNoOfDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["GroupId2"].Value = dRow[0].ToString();
}
}
//------------ Liability ------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "Profit And Loss (Opening)";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = Math.Round(decTotalProfitAndLoss, inDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Style.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Style.ForeColor = Color.DarkSlateGray;
dcTotalLiability += decOpeningOfProfitAndLoss;
//------------ Liability ------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "Current Period";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = Math.Round(decCurrentProfitLoss, inDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Style.ForeColor = Color.DarkSlateGray;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Style.ForeColor = Color.DarkSlateGray;
dcTotalLiability = dcTotalLiability + (decCurrentProfitLoss + decTotalProfitAndLoss); //dcProfit;
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "----------------------------------------";
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
}
dgvReport.Rows.Add();
decimal dcDiffAsset = 0;
decimal dcDiffLiability = 0;
decimal dcTotalValue = dcTotalAsset;
if (dcTotalAsset != dcTotalLiability)
{
if (dcTotalAsset > dcTotalLiability)
{
//--------------- Liability exceeds so in asset side ----------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "Difference";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = Math.Round((dcTotalAsset - dcTotalLiability), inDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.ForeColor = Color.DarkRed;
dcDiffLiability = dcTotalAsset - dcTotalLiability;
}
else
{
//--------------- Asset exceeds so in liability side ----------------//
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "Difference";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round((dcTotalLiability - dcTotalAsset), inDecimalPlaces);;
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.ForeColor = Color.DarkRed;
dcDiffAsset = dcTotalLiability - dcTotalAsset;
}
}
dgvReport.Rows.Add();
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = "__________________________";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = "__________________________";
dgvReport.Rows.Add();
dgvReport.Rows[dgvReport.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtLiability"].Value = "Total";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["dgvtxtAsset"].Value = "Total";
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount1"].Value = Math.Round((dcTotalAsset + dcDiffAsset), inDecimalPlaces);
dgvReport.Rows[dgvReport.Rows.Count - 1].Cells["Amount2"].Value = Math.Round((dcTotalLiability + dcDiffLiability), inDecimalPlaces);
if (dgvReport.Columns.Count > 0)
{
dgvReport.Columns["Amount1"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvReport.Columns["Amount2"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
decPrintOrNot = dcTotalAsset + dcDiffAsset;
decPrintOrNot1 = dcTotalLiability + dcDiffLiability;
if (inCurrenRowIndex >= 0 && dgvReport.Rows.Count > 0 && inCurrenRowIndex < dgvReport.Rows.Count)
{
if (dgvReport.Rows[inCurrenRowIndex].Cells[inCurentcolIndex].Visible)
{
dgvReport.CurrentCell = dgvReport.Rows[inCurrenRowIndex].Cells[inCurentcolIndex];
}
if (dgvReport.CurrentCell != null && dgvReport.CurrentCell.Visible)
{
dgvReport.CurrentCell.Selected = true;
}
}
inCurrenRowIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show("BS :1" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to fill the report in grid
/// </summary>
public void AccountGroupGridFill()
{
decimal decBalanceTotal = 0;
DateTime dtmFromDate = DateTime.Now;
DateTime dtmToDate = DateTime.Now;
AccountGroupSP spAccountGroup = new AccountGroupSP();
DataTable dtbl = new DataTable();
try
{
if (txtFromDate.Text != string.Empty)
{
dtmFromDate = Convert.ToDateTime(txtFromDate.Text);
}
if (txtToDate.Text != string.Empty)
{
dtmToDate = Convert.ToDateTime(txtToDate.Text);
}
dtbl = spAccountGroup.AccountGroupReportFill(dtmFromDate, dtmToDate);
for (int i = 0; i < dtbl.Rows.Count; i++)
{
if (Convert.ToDecimal(dtbl.Rows[i]["accountGroupId"].ToString()) == 6)
{
FinancialStatementSP spFinace = new FinancialStatementSP();
CalculationMethod();
decimal dcOpeninggStock = spFinace.StockValueGetOnDate(PublicVariables._dtFromDate, calculationMethod, true, true);
dcOpeninggStock = Math.Round(dcOpeninggStock, PublicVariables._inNoOfDecimalPlaces);
decimal decOpBalance = Convert.ToDecimal(dtbl.Rows[i]["OpBalance"].ToString()) + dcOpeninggStock;
decimal decClosing = Convert.ToDecimal(dtbl.Rows[i]["balance1"].ToString()) + dcOpeninggStock;
if (decOpBalance >= 0)
{
dtbl.Rows[i]["OpeningBalance"] = decOpBalance.ToString() + "Dr";
}
else
{
dtbl.Rows[i]["OpeningBalance"] = decOpBalance.ToString() + "Cr";
}
if (decClosing >= 0)
{
dtbl.Rows[i]["balance"] = decClosing.ToString() + "Dr";
}
else
{
dtbl.Rows[i]["balance"] = decClosing.ToString() + "Cr";
}
dtbl.Rows[i]["balance1"] = decClosing.ToString();
}
}
dgvAccountGroupReport.DataSource = dtbl;
if (dgvAccountGroupReport.RowCount > 0)
{
for (int i = 0; i < dgvAccountGroupReport.RowCount; i++)
{
decBalanceTotal = decBalanceTotal + Convert.ToDecimal(dgvAccountGroupReport.Rows[i].Cells["dgvtxtBalance1"].Value.ToString());
}
}
if (decBalanceTotal < 0)
{
decBalanceTotal = -1 * decBalanceTotal;
lblBalanceTotal.Text = decBalanceTotal.ToString() + "Cr";
}
else
{
lblBalanceTotal.Text = decBalanceTotal.ToString() + "Dr";;
}
}
catch (Exception ex)
{
MessageBox.Show("AGR:4" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
/// <summary>
/// Function to fill Datagridview
/// </summary>
public void GridFill()
{
try
{
if (!isFormLoad)
{
DateValidation objValidation = new DateValidation();
objValidation.DateValidationFunction(txtFromDate);
if (txtFromDate.Text == string.Empty)
{
txtFromDate.Text = PublicVariables._dtFromDate.ToString("dd-MMM-yyyy");
}
objValidation.DateValidationFunction(txttoDate);
if (txttoDate.Text == string.Empty)
{
txttoDate.Text = PublicVariables._dtToDate.ToString("dd-MMM-yyyy");
}
DateTime strFromDate = DateTime.Parse(txtFromDate.Text.ToString());
DateTime strTodate = DateTime.Parse(txttoDate.Text.ToString());
FinancialStatementSP spFinancial = new FinancialStatementSP();
DataSet dsetFinancial = new DataSet();
SettingsInfo InfoSettings = new SettingsInfo();
SettingsSP SpSettings = new SettingsSP();
dsetFinancial = spFinancial.CashFlow(strFromDate, strTodate);
DataTable dtbl = new DataTable();
Font newFont = new Font(dgvCashflow.Font, FontStyle.Bold);
CurrencyInfo InfoCurrency = new CurrencyInfo();
CurrencySP SpCurrency = new CurrencySP();
InfoCurrency = SpCurrency.CurrencyView(1);
int inDecimalPlaces = InfoCurrency.NoOfDecimalPlaces;
dgvCashflow.Rows.Clear();
for (int i = 0; i < 8; i++)
{
dtbl = dsetFinancial.Tables[i];
foreach (DataRow rw in dtbl.Rows)
{
dgvCashflow.Rows.Add();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtParticulars"].Value = rw["accountGroupName"].ToString();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtinflow"].Value = rw["Balance"].ToString();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtID1"].Value = rw["accountGroupId"].ToString();
}
}
//-------------------------------Calculating TotalInflow-----------------------------------------
decimal dcTotalInflow = 0m;
if (dtbl.Rows.Count > 0)
{
for (int i = 0; i < dgvCashflow.Rows.Count; i++)
{
decimal dcTotalIn = decimal.Parse(dgvCashflow.Rows[i].Cells["dgvtxtinflow"].Value.ToString());
dcTotalInflow += dcTotalIn;
}
dcTotInflow = dcTotalInflow;
}
//-----------------Outflow------------------------------
int index = 0;
for (int i = 8; i < 15; i++)
{
dtbl = new DataTable();
dtbl = dsetFinancial.Tables[i];
foreach (DataRow rw in dtbl.Rows)
{
if (index < dgvCashflow.Rows.Count)
{
dgvCashflow.Rows[index].Cells["dgvtxtParticulars1"].Value = rw["accountGroupName1"].ToString();
dgvCashflow.Rows[index].Cells["dgvtxtoutflow"].Value = rw["Balance1"].ToString();
dgvCashflow.Rows[index].Cells["dgvtxtID2"].Value = rw["accountGroupId"].ToString();
}
else
{
dgvCashflow.Rows.Add();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtParticulars1"].Value = rw["accountGroupName1"].ToString();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtoutflow"].Value = rw["Balance1"].ToString();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtID2"].Value = rw["accountGroupId"].ToString();
}
index++;
}
}
//-------------------------------Calculating TotalOutflow-----------------------------------------
decimal dcTotalOutflow = 0m;
if (dtbl.Rows.Count > 0)
{
for (int i = 0; i < dgvCashflow.Rows.Count - 1; i++)
{
decimal dcTotalIn = decimal.Parse(dgvCashflow.Rows[i].Cells["dgvtxtoutflow"].Value.ToString());
dcTotalOutflow += dcTotalIn;
}
dcTotOutflow = dcTotalOutflow;
}
dgvCashflow.Rows.Add();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtinflow"].Value = "_______________________";
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtoutflow"].Value = "_______________________";
dgvCashflow.Rows.Add();
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].DefaultCellStyle.Font = newFont;
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtParticulars"].Value = "Total";
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtParticulars1"].Value = "Total";
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtinflow"].Value = Math.Round((dcTotalInflow), inDecimalPlaces);
dgvCashflow.Rows[dgvCashflow.Rows.Count - 1].Cells["dgvtxtoutflow"].Value = Math.Round((dcTotalOutflow), inDecimalPlaces);
if (dgvCashflow.Columns.Count > 0)
{
dgvCashflow.Columns["dgvtxtinflow"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
dgvCashflow.Columns["dgvtxtoutflow"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
}
if (inCurrenRowIndex >= 0 && dgvCashflow.Rows.Count > 0 && inCurrenRowIndex < dgvCashflow.Rows.Count)
{
if (dgvCashflow.Rows[inCurrenRowIndex].Cells[inCurentcolIndex].Visible)
{
dgvCashflow.CurrentCell = dgvCashflow.Rows[inCurrenRowIndex].Cells[inCurentcolIndex];
}
if (dgvCashflow.CurrentCell != null && dgvCashflow.CurrentCell.Visible)
{
dgvCashflow.CurrentCell.Selected = true;
}
}
inCurrenRowIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show("CF:01" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}