public DataSet ProfitAndLossAnalysisUpToaDateForPreviousYears(DateTime toDate)
        {
            DataSet dset = new DataSet();

            try
            {
                dset = spFinancialStatement.ProfitAndLossAnalysisUpToaDateForPreviousYears(toDate);
            }
            catch (Exception ex)
            {
                MessageBox.Show("FS5:" + ex.Message, "OpenMiracle", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            return(dset);
        }
예제 #2
0
 /// <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);
     }
 }