private DataTable BindProfitLoss()
{
DataTable dtN = new DataTable();
ProfitLossBookDAL objPLDAL = new ProfitLossBookDAL();
DataSet dsGrossPL, dsExpend, dsReven;
dsGrossPL = dsExpend = dsReven = null;
//dsGrossPL = objPLDAL.SelectFinalBookPL1(conString, "SelectOpeningBal", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlSeelction.SelectedValue), icompid);
dsExpend = objPLDAL.SelectFinalBookProfitLosswtDtRnge(ApplicationFunction.ConnectionString(), "SelectExpnsWDtRng", Datefrom.Text.Trim(), txtDateTo.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dsReven = objPLDAL.SelectFinalBookProfitLosswtDtRnge(ApplicationFunction.ConnectionString(), "SelectRevWDtRng", Datefrom.Text.Trim(), txtDateTo.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
#region Create Row-Column...
dtN.Columns.Add("Item_Name");
dtN.Columns.Add("Open_Amnt");
dtN.Columns.Add("Item_Namee");
dtN.Columns.Add("Amount");
dtN.Columns.Add("LH_AGRP_IDNO");
dtN.Columns.Add("LHTyp");
dtN.Columns.Add("RH_AGRP_IDNO");
dtN.Columns.Add("RHTyp");
dtN.Columns.Add("LTyp");
dtN.Columns.Add("RTyp");
var row = dtN.NewRow();
row["Item_Name"] = "";
row["Open_Amnt"] = "";
row["Item_Namee"] = "";
row["Amount"] = "";
row["LH_AGRP_IDNO"] = "";
row["LHTyp"] = "";
row["RH_AGRP_IDNO"] = "";
row["RHTyp"] = "";
row["LTyp"] = "";
row["RTyp"] = "";
dtN.Rows.Add(row);
#endregion
double grosPrft, groslos; grosPrft = groslos = 0;
if ((dsGrossPL != null) && (dsGrossPL.Tables.Count > 0) && (dsGrossPL.Tables[0].Rows.Count > 0))
{
if (Convert.ToDouble(dsGrossPL.Tables[0].Rows[0][0]) >= 0)
{
dtN.Rows[0][2] = "Gross Profit";
dtN.Rows[0][3] = Convert.ToDouble(dsGrossPL.Tables[0].Rows[0][0]).ToString("N2");
grosPrft = Convert.ToDouble(dsGrossPL.Tables[0].Rows[0][0]); groslos = 0;
}
else
{
dtN.Rows[0][0] = "Gross Loss";
dtN.Rows[0][1] = Convert.ToDouble(Math.Abs(Convert.ToDouble(dsGrossPL.Tables[0].Rows[0][0]))).ToString("N2");
grosPrft = 0; groslos = (Math.Abs(Convert.ToDouble(dsGrossPL.Tables[0].Rows[0][0])));
}
}
else
{
dtN.Rows[0][2] = " Gross Profit";
dtN.Rows[0][3] = "0.00";
}
#region Expenses
counter = 2;
dtN.Rows.Add();
if ((dsExpend != null) && (dsExpend.Tables.Count > 0) && (dsExpend.Tables[0].Rows.Count > 0))
{
dsExpend.Tables[0].TableName = "Expense";
ExpBal = Convert.ToDouble(dsExpend.Tables[0].Rows[dsExpend.Tables[0].Rows.Count - 1][1]);
dtN.Rows.Add(dsExpend.Tables[0].Rows.Count + 2);
dtN.Rows[counter - 1][0] = "Expenses"; dtN.Rows[counter - 1][1] = "";
for (int i = 0; i < dsExpend.Tables[0].Rows.Count; i++)
{
dtN.Rows.Add();
dtN.Rows[counter][0] = dsExpend.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[counter][1] = dsExpend.Tables[0].Rows[i]["Amount"];
dtN.Rows[counter][1] = (Convert.ToString(dsExpend.Tables[0].Rows[i]["Amount"]) == "" ? "" : Convert.ToDouble(dsExpend.Tables[0].Rows[i]["Amount"]).ToString("N2"));
dtN.Rows[counter][4] = dsExpend.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[counter][5] = "P";
dtN.Rows[counter][8] = dsExpend.Tables[0].Rows[i]["Typ"];
counter++;
}
counter += 2;
}
#endregion
#region Revenue
counter = 2;
if ((dsReven != null) && (dsReven.Tables.Count > 0) && (dsReven.Tables[0].Rows.Count > 0))
{
dsReven.Tables[0].TableName = "Revenue";
RevenBal = Convert.ToDouble(dsReven.Tables[0].Rows[dsReven.Tables[0].Rows.Count - 1]["Amount"]);
int row1 = (dsReven.Tables[0].Rows.Count - dtN.Rows.Count) + 1;
if (row1 > 2)
{
dtN.Rows.Add(row);
}
dtN.Rows[counter - 1][2] = "Revenue"; dtN.Rows[counter - 1][3] = "";
for (int i = 0, j = counter; i < dsReven.Tables[0].Rows.Count; i++, j++)
{
//dtN.Rows.Add();
dtN.Rows[j][2] = dsReven.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[j][3] = dsReven.Tables[0].Rows[i]["Amount"];
dtN.Rows[j][3] = (Convert.ToString(dsReven.Tables[0].Rows[i]["Amount"]) == "" ? "" : Convert.ToDouble(dsReven.Tables[0].Rows[i]["Amount"]).ToString("N2"));
dtN.Rows[j][6] = dsReven.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[j][7] = "S";
dtN.Rows[j][9] = dsReven.Tables[0].Rows[i]["Typ"];
counter = j + 1;
}
}
#endregion
#region calac..
dtN.Rows.Add(); double prof, los; prof = los = 0;
profit = (RevenBal + grosPrft) - (ExpBal + groslos);
//profit = RevenBal - ExpBal;
if (profit > 0)
{
dtN.Rows[dtN.Rows.Count - 1][0] = "Net Profit";
dtN.Rows[dtN.Rows.Count - 1][1] = profit.ToString("N2");
prof = profit; los = 0;
dtN.Rows.Add();
}
else
{
dtN.Rows[dtN.Rows.Count - 1][2] = "Net Loss";
dtN.Rows[dtN.Rows.Count - 1][3] = Convert.ToDouble(Math.Abs(profit)).ToString("N2");
prof = 0; los = (Math.Abs(profit));
dtN.Rows.Add();
}
dtN.Rows[dtN.Rows.Count - 1][0] = "Net Total";
dtN.Rows[dtN.Rows.Count - 1][1] = Convert.ToDouble(ExpBal + prof + groslos).ToString("N2");
dtN.Rows[dtN.Rows.Count - 1][2] = "Net Total";
dtN.Rows[dtN.Rows.Count - 1][3] = Convert.ToDouble(RevenBal + los + grosPrft).ToString("N2");
#endregion
return(dtN);
}
private DataTable BindBalanceSheet()
{
DataTable dtN = new DataTable();
ProfitLossBookDAL objBSDAL = new ProfitLossBookDAL();
DataSet dsCurLiab, dsCurAsset, dsLoans, dsCapAc,
dsCapAcPnL, dsExpRev;
Double dCapAcPnL = 0, dExpRev = 0;
double profit = 0; PurBal = SaleBal = counter = 0;
dsCurLiab = objBSDAL.SelectFinalBookwtDtRngeBS(ApplicationFunction.ConnectionString(), "SelectCurLiabWDtRng", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dsCurAsset = objBSDAL.SelectFinalBookwtDtRngeBS(ApplicationFunction.ConnectionString(), "SelectCurAssetWDtRng", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dsLoans = objBSDAL.SelectFinalBookwtDtRngeBS(ApplicationFunction.ConnectionString(), "SelectLoansWDtRng", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dsCapAc = objBSDAL.SelectFinalBookwtDtRngeBS(ApplicationFunction.ConnectionString(), "SelectCapAccWDtRng", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dsCapAcPnL = objBSDAL.SelectFinalBookBS(ApplicationFunction.ConnectionString(), "SelectCapAcPnL", txtdatefrm.Text.Trim(), txtdateto.Text.Trim(), Convert.ToInt32(ddlDateRange.SelectedValue));
dCapAcPnL = Convert.ToDouble(dsCapAcPnL.Tables[0].Rows[0]["Open_Purchase_Sale"]);
counter = 1;
#region Colum nd row created
dtN.Rows.Add();
dtN.Columns.Add("Item_Name");
dtN.Columns.Add("Amount");
dtN.Columns.Add("Item_Name1");
dtN.Columns.Add("Amount1");
dtN.Columns.Add("LH_AGRP_IDNO");
dtN.Columns.Add("LHTyp");
dtN.Columns.Add("RH_AGRP_IDNO");
dtN.Columns.Add("RHTyp");
dtN.Columns.Add("LTyp");
dtN.Columns.Add("RTyp");
var row1 = dtN.NewRow();
row1["Item_Name"] = "";
row1["Amount"] = "";
row1["Item_Name1"] = "";
row1["Amount1"] = "";
row1["LH_AGRP_IDNO"] = "";
row1["LHTyp"] = "";
row1["RH_AGRP_IDNO"] = "";
row1["RHTyp"] = "";
row1["LTyp"] = "";
row1["RTyp"] = "";
dtN.Rows.Add(row1);
#endregion
#region Current Liability
if (dsCurLiab != null)
{
if (dsCurLiab.Tables.Count > 0)
{
dsCurLiab.Tables[0].TableName = "CurrentLiability";
if (dsCurLiab.Tables[0].Rows.Count > 0)
{
PurBal = Convert.ToDouble(dsCurLiab.Tables[0].Rows[dsCurLiab.Tables[0].Rows.Count - 1][1]);
dtN.Rows.Add();
dtN.Rows[counter][0] = "Current Liabilities";
dtN.Rows[counter][1] = "";
counter += 1;
for (int i = 0; i < dsCurLiab.Tables[0].Rows.Count; i++)
{
dtN.Rows.Add();
dtN.Rows[counter][0] = dsCurLiab.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[counter][1] = dsCurLiab.Tables[0].Rows[i]["Amount"];
dtN.Rows[counter][4] = dsCurLiab.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[counter][5] = "P";
dtN.Rows[counter][8] = dsCurLiab.Tables[0].Rows[i]["Typ"];
counter++;
}
counter += 1;
}
}
}
#endregion
#region Loans Received
if (dsLoans != null)
{
if (dsLoans.Tables.Count > 0)
{
dsLoans.Tables[0].TableName = "LoansReceived";
if (dsLoans.Tables[0].Rows.Count > 0)
{
PurBal = PurBal + Convert.ToDouble(dsLoans.Tables[0].Rows[dsLoans.Tables[0].Rows.Count - 1][1]);
//if (Convert.ToDouble(dsLoans.Tables[0].Rows[dsLoans.Tables[0].Rows.Count - 1][1]) > 0)
//{
dtN.Rows.Add();
dtN.Rows.Add(dsLoans.Tables[0].Rows.Count + 4);
dtN.Rows[counter][0] = "Loans Received";
dtN.Rows[counter][1] = "";
counter += 1;
for (int i = 0; i < dsLoans.Tables[0].Rows.Count; i++)
{
dtN.Rows.Add();
dtN.Rows[counter][0] = dsLoans.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[counter][1] = dsLoans.Tables[0].Rows[i]["Amount"];
dtN.Rows[counter][4] = dsLoans.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[counter][5] = "P";
dtN.Rows[counter][8] = dsLoans.Tables[0].Rows[i]["Typ"];
counter++;
}
counter += 1;
//}
}
}
}
#endregion
#region Capital Account
if (dsCapAc != null && dsCapAc.Tables.Count > 0)
{
dsCapAc.Tables[0].TableName = "Capitals";
if (dsCapAc.Tables[0].Rows.Count > 0)
{
PurBal = PurBal + Convert.ToDouble(dsCapAc.Tables[0].Rows[dsCapAc.Tables[0].Rows.Count - 1][1]);
dtN.Rows.Add();
dtN.Rows[counter][0] = "Capitals";
dtN.Rows[counter][1] = "";
counter += 1;
for (int i = 0; i < dsCapAc.Tables[0].Rows.Count; i++)
{
dtN.Rows.Add();
dtN.Rows[counter][0] = dsCapAc.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[counter][1] = dsCapAc.Tables[0].Rows[i]["Amount"];
dtN.Rows[counter][4] = dsCapAc.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[counter][5] = "P";
dtN.Rows[counter][8] = dsCapAc.Tables[0].Rows[i]["Typ"];
counter++;
}
counter += 1;
}
}
#endregion
dtN.Rows.Add();
dtN.Rows.Add();
dtN.Rows[counter][0] = "Profit & Loss Account";
dtN.Rows[counter][1] = dCapAcPnL.ToString("N2").Replace(",", "");
counter += 1;
#region Current Assest
counter = 1;
if (dsCurAsset != null && dsCurAsset.Tables.Count > 0)
{
dsCurAsset.Tables[0].TableName = "CurrentAssets";
if (dsCurAsset.Tables[0].Rows.Count > 0)
{
SaleBal = Convert.ToDouble(dsCurAsset.Tables[0].Rows[dsCurAsset.Tables[0].Rows.Count - 1]["Amount"]);
int row = (dsCurAsset.Tables[0].Rows.Count - dtN.Rows.Count) + 1;
if (row > 0)
{
dtN.Rows.Add(row + 1);
}
dtN.Rows[counter][2] = "Current Assets"; dtN.Rows[counter][3] = "";
for (int i = 0, j = 2; i < dsCurAsset.Tables[0].Rows.Count; i++, j++)
{
//dtN.Rows.Add();
dtN.Rows[j][2] = dsCurAsset.Tables[0].Rows[i]["Item_Name"];
dtN.Rows[j][3] = dsCurAsset.Tables[0].Rows[i]["Amount"];
dtN.Rows[j][6] = dsCurAsset.Tables[0].Rows[i]["AGRP_IDNO"];
dtN.Rows[j][7] = "S";
dtN.Rows[j][9] = dsCurAsset.Tables[0].Rows[i]["Typ"];
counter = j + 1;
}
}
}
#endregion
#region calac..
dtN.Rows.Add();
dtN.Rows.Add(); double prof, los; prof = los = 0;
//profit = (RevenBal + grosPrft) - (ExpBal + groslos);
profit = SaleBal - PurBal - dCapAcPnL;
if (profit > 0)
{
dtN.Rows[dtN.Rows.Count - 1][0] = "Difference";
dtN.Rows[dtN.Rows.Count - 1][1] = profit.ToString("N2");
prof = profit; los = 0;
dtN.Rows.Add();
}
else
{
dtN.Rows[dtN.Rows.Count - 1][2] = "Difference";
dtN.Rows[dtN.Rows.Count - 1][3] = Convert.ToDouble(Math.Abs(profit)).ToString("N2");
prof = 0; los = (Math.Abs(profit));
dtN.Rows.Add();
}
dtN.Rows[dtN.Rows.Count - 1][0] = "Net Total";
dtN.Rows[dtN.Rows.Count - 1][1] = Convert.ToDouble(PurBal + dCapAcPnL + prof).ToString("N2");
dtN.Rows[dtN.Rows.Count - 1][2] = "Net Total";
dtN.Rows[dtN.Rows.Count - 1][3] = Convert.ToDouble(SaleBal + los).ToString("N2");
#endregion
return(dtN);
}
