public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport()
{
cryRepTrialBalance rpt = new cryRepTrialBalance();
rpt.Site = this.Site;
return(rpt);
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepTrialBalance report = new cryRepTrialBalance();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
string strDate = "";
string strPrevDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (ckbClosingEntry.Checked == false)
{
strStat += " and h.trans_name!='سند اقفال'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " and h.trans_name!='قيد افتتاحي' and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
if (dtpFrom.Value.ToString("dd/MM") == "01/01")
{
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
else
{
strPrevDate = " and jour_date between str_to_date('01/01/" + dtpFrom.Value.ToString("yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpFrom.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
}
}
if (strPrevDate == "")
{
// strPrevDate = " and jour_date between str_to_date('01/01/1999', '%d/%m/%Y') and str_to_date('02/01/1999', '%d/%m/%Y')";
strDate = " and h.trans_name!='قيد افتتاحي'";
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
dtReport.Clear();
//dtReport = cnn.GetDataTable("SELECT Acc_no,acc_name ," +
// "(select sum(main_value) " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value > 0 " +
// " and acc_no like concat(acc.acc_no, '%') " + strDate + strStat + ") dept, " +
// "(select sum(main_value) " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value > 0 " +
// " and acc_no like concat(acc.acc_no, '%') " + strPrevDate + strStat + ") Prevdept, " +
// " (select sum(main_value) * -1 " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value < 0 " +
// " and acc_no like concat(acc.acc_no,'%')" + strDate + strStat + ") credit, " +
// " (select sum(main_value) * -1 " +
// " from journal_header h " +
// " join journal_details d on (d.header_id = h.Pkid) " +
// " join accounts a on (a.pkid = d.acc_id) " +
// " where main_value < 0 " +
// " and acc_no like concat(acc.acc_no,'%')" + strPrevDate + strStat + ") Prevcredit, " +
// " 0.00 balance_dept,0.00 balance_credit " +
// " FROM accounts acc " +
// " order by acc_no desc");
dtReport = cnn.GetDataTable("select a.pkid,acc_no,acc_name ,a.parent_id, sum(if (d.main_value > 0 " + strPrevDate + strStat + ",d.main_value,0)) Prevdept, " +
" sum( if (d.main_value < 0 " + strPrevDate + strStat + ",d.main_value *-1,0)) Prevcredit, " +
" sum(if (d.main_value > 0 " + strDate + strStat + ", d.main_value,0)) Dept,sum(if (d.main_value < 0 " + strDate + strStat + ",d.main_value*-1,0) ) credit," +
" 0.00 balance_dept,0.00 balance_credit " +
"from journal_header h " +
" join journal_details d on(h.Pkid = d.header_id) " +
" join accounts a on(a.pkid = d.acc_id) " +
" group by a.pkid,acc_no,acc_name ,a.parent_id " +
" order by acc_no " +
"");
decimal dDept = 0;
decimal dCredit = 0;
decimal dPreDept = 0;
decimal dPreCredit = 0;
decimal dBalance = 0;
decimal dTotalDept = 0;
decimal dTotalCredit = 0;
decimal dTotalPreDept = 0;
decimal dTotalPreCredit = 0;
decimal dTotalBalanceDept = 0;
decimal dTotalBalanceCredit = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
if (dtReport.Rows[i]["dept"].ToString() == "")
{
dDept = 0;
dtReport.Rows[i]["dept"] = "0";
}
else
{
dDept = Convert.ToDecimal(dtReport.Rows[i]["dept"].ToString());
}
if (dtReport.Rows[i]["Prevdept"].ToString() == "")
{
dtReport.Rows[i]["Prevdept"] = "0";
dPreDept = 0;
}
else
{
dPreDept = Convert.ToDecimal(dtReport.Rows[i]["Prevdept"].ToString());
}
if (dtReport.Rows[i]["credit"].ToString() == "")
{
dCredit = 0;
dtReport.Rows[i]["credit"] = "0";
}
else
{
dCredit = Convert.ToDecimal(dtReport.Rows[i]["credit"].ToString());
}
if (dtReport.Rows[i]["Prevcredit"].ToString() == "")
{
dtReport.Rows[i]["Prevcredit"] = "0";
dPreCredit = 0;
}
else
{
dPreCredit = Convert.ToDecimal(dtReport.Rows[i]["Prevcredit"].ToString());
}
string stracc = dtReport.Rows[i]["acc_no"].ToString();
// if ((dDept + dPreDept) - (dCredit + dPreCredit) == 0 )
// if ((dDept- dPreDept)==0 && (dCredit- dPreCredit )== 0)
if ((dDept - dCredit) == 0 && (dPreDept - dPreCredit) == 0)
{
dtReport.Rows.Remove(dtReport.Rows[i]);
i--;
}
else
{
dBalance = (dDept + dPreDept) - (dCredit + dPreCredit);
if (dBalance < 0)
{
dtReport.Rows[i]["balance_credit"] = (dBalance * -1).ToString();
}
else
{
dtReport.Rows[i]["balance_dept"] = (dBalance).ToString();
}
}
}
DataTable dtMainAcc = cnn.GetDataTable("select pkid,acc_no,acc_name ,a.parent_id " +
" from accounts a " +
" where a.level < 5 " +
" order by Acc_no desc");
decimal sumDept = 0;
decimal sumCredit = 0;
decimal sumPrevDept = 0;
decimal sumPrevCredit = 0;
decimal sumBalDept = 0;
decimal sumBalCredit = 0;
int icount = 0;
int iReportCount = dtReport.Rows.Count;
decimal dBalanceTotal = 0;
for (int i = 0; i < dtMainAcc.Rows.Count; i++)
{
sumDept = 0;
sumCredit = 0;
sumPrevDept = 0;
sumPrevCredit = 0;
sumBalDept = 0;
sumBalCredit = 0;
icount = 0;
for (int j = 0; j < iReportCount; j++)
{
if (dtMainAcc.Rows[i]["pkid"].ToString() == dtReport.Rows[j]["parent_id"].ToString())
{
sumDept = sumDept + Convert.ToDecimal(dtReport.Rows[j]["Dept"].ToString());
sumCredit = sumCredit + Convert.ToDecimal(dtReport.Rows[j]["credit"].ToString());
sumPrevDept = sumPrevDept + Convert.ToDecimal(dtReport.Rows[j]["Prevdept"].ToString());
sumPrevCredit = sumPrevCredit + Convert.ToDecimal(dtReport.Rows[j]["Prevcredit"].ToString());
decimal dSumBalanc = (sumDept + sumPrevDept) - (sumCredit + sumPrevCredit);
if (dSumBalanc > 0)
{
sumBalDept = dSumBalanc;
sumBalCredit = 0;
}
else
{
sumBalDept = 0;
sumBalCredit = dSumBalanc * -1;
}
icount = 1;
}
}
if (icount <= 0)
{
continue;
}
DataRow repRow = dtReport.NewRow();
repRow["pkid"] = dtMainAcc.Rows[i]["pkid"].ToString();
repRow["acc_no"] = dtMainAcc.Rows[i]["acc_no"].ToString();
repRow["acc_name"] = dtMainAcc.Rows[i]["acc_name"].ToString();
if (dtMainAcc.Rows[i]["parent_id"].ToString() == "" || dtMainAcc.Rows[i]["parent_id"].ToString() == "0")
{
repRow["parent_id"] = "0";
dTotalDept = dTotalDept + sumDept;
dTotalCredit = dTotalCredit + sumCredit;
dTotalPreDept = dTotalPreDept + sumPrevDept;
dTotalPreCredit = dTotalPreCredit + sumPrevCredit;
dTotalBalanceDept = dTotalBalanceDept + sumBalDept;
dTotalBalanceCredit = dTotalBalanceCredit + sumBalCredit;
}
else
{
repRow["parent_id"] = dtMainAcc.Rows[i]["parent_id"].ToString();
}
repRow["Dept"] = sumDept;
repRow["credit"] = sumCredit;
repRow["Prevdept"] = sumPrevDept;
repRow["Prevcredit"] = sumPrevCredit;
dBalanceTotal = (sumDept + sumPrevDept) - (sumCredit + sumPrevCredit);
if (dBalanceTotal < 0)
{
repRow["balance_credit"] = (dBalanceTotal * -1).ToString();
repRow["balance_dept"] = "0";
}
else
{
repRow["balance_dept"] = (dBalanceTotal).ToString();
repRow["balance_credit"] = "0";
}
dtReport.Rows.Add(repRow);
iReportCount = iReportCount + 1;
}
DataView dv = dtReport.DefaultView;
dv.Sort = "acc_no";
DataTable sortedDT = dv.ToTable();
report.SetDataSource(sortedDT);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("dTotalDept", dTotalDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalCredit", dTotalCredit.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalPreDept", dTotalPreDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalPreCredit", dTotalPreCredit.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalBalanceDept", dTotalBalanceDept.ToString("###,###,###,###.##"));
report.SetParameterValue("dTotalBalanceCredit", dTotalBalanceCredit.ToString("###,###,###,###.##"));
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnTrailBalance_Click(object sender, EventArgs e)
{
cryRepTrialBalance report = new cryRepTrialBalance();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
dtReport.Columns.Add("pkid");
dtReport.Columns.Add("acc_no");
dtReport.Columns.Add("acc_name");
dtReport.Columns.Add("parent_id");
dtReport.Columns.Add("Prevdept");
dtReport.Columns.Add("Prevcredit");
dtReport.Columns.Add("Dept");
dtReport.Columns.Add("credit");
dtReport.Columns.Add("balance_dept");
dtReport.Columns.Add("balance_credit");
for (int i = 0; i < myDataGrid1.Rows.Count; i++)
{
DataRow repRow = dtReport.NewRow();
repRow["pkid"] = "";
repRow["acc_no"] = myDataGrid1[0, i].Value.ToString();
repRow["acc_name"] = myDataGrid1[1, i].Value.ToString();
repRow["parent_id"] = "";
repRow["Prevdept"] = myDataGrid1[2, i].Value.ToString();
repRow["Prevcredit"] = myDataGrid1[3, i].Value.ToString();
repRow["Dept"] = myDataGrid1[4, i].Value.ToString();
repRow["credit"] = myDataGrid1[5, i].Value.ToString();
repRow["balance_dept"] = myDataGrid1[6, i].Value.ToString();
repRow["balance_credit"] = myDataGrid1[7, i].Value.ToString();
dtReport.Rows.Add(repRow);
}
decimal dTotalDept = 0;
decimal dTotalCredit = 0;
decimal dTotalPreDept = 0;
decimal dTotalPreCredit = 0;
decimal dTotalBalanceDept = 0;
decimal dTotalBalanceCredit = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
if (dtReport.Rows[i]["acc_no"].ToString().Trim().Length == 1)
{
dTotalDept = dTotalDept + Convert.ToDecimal(dtReport.Rows[i]["Dept"].ToString().Trim());
dTotalCredit = dTotalCredit + Convert.ToDecimal(dtReport.Rows[i]["credit"].ToString().Trim());
dTotalPreDept = dTotalPreDept + Convert.ToDecimal(dtReport.Rows[i]["Prevdept"].ToString().Trim());
dTotalPreCredit = dTotalPreCredit + Convert.ToDecimal(dtReport.Rows[i]["Prevcredit"].ToString().Trim());
dTotalBalanceDept = dTotalBalanceDept + Convert.ToDecimal(dtReport.Rows[i]["balance_dept"].ToString().Trim());
dTotalBalanceCredit = dTotalBalanceCredit + Convert.ToDecimal(dtReport.Rows[i]["balance_credit"].ToString().Trim());
}
}
report.SetDataSource(dtReport);
report.SetParameterValue("From", dtpFrom.Value.ToString("dd/MM/yyyy"));
report.SetParameterValue("To", dtpFrom.Value.ToString("dd/MM/yyyy"));
report.SetParameterValue("dTotalDept", dTotalDept.ToString("###,###,###,###.#########"));
report.SetParameterValue("dTotalCredit", dTotalCredit.ToString("###,###,###,###.#########"));
report.SetParameterValue("dTotalPreDept", dTotalPreDept.ToString("###,###,###,###.#########"));
report.SetParameterValue("dTotalPreCredit", dTotalPreCredit.ToString("###,###,###,###.#########"));
report.SetParameterValue("dTotalBalanceDept", dTotalBalanceDept.ToString("###,###,###,###.#########"));
report.SetParameterValue("dTotalBalanceCredit", dTotalBalanceCredit.ToString("###,###,###,###.#########"));
//report.SetParameterValue("dTotalDept","0");
//report.SetParameterValue("dTotalCredit", "0");
//report.SetParameterValue("dTotalPreDept", "0");
//report.SetParameterValue("dTotalPreCredit", "0");
//report.SetParameterValue("dTotalBalanceDept", "0");
//report.SetParameterValue("dTotalBalanceCredit", "0");
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepTrialBalance report = new cryRepTrialBalance();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
string strDate = "";
string strPrevDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (ckbClosingEntry.Checked == false)
{
strStat += " and h.trans_name!='سند اقفال'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " and h.trans_name!='قيد افتتاحي' and jour_date between str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpTo.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
if (dtpFrom.Value.ToString("dd/MM") == "01/01")
{
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
else
{
strPrevDate = " and jour_date between str_to_date('01/01/" + dtpFrom.Value.ToString("yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpFrom.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y')";
}
}
if (strPrevDate == "")
{
// strPrevDate = " and jour_date between str_to_date('01/01/1999', '%d/%m/%Y') and str_to_date('02/01/1999', '%d/%m/%Y')";
strDate = " and h.trans_name!='قيد افتتاحي'";
strPrevDate = " and h.trans_name='قيد افتتاحي'";
}
dtReport.Clear();
dtReport = cnn.GetDataTable("SELECT Acc_no,acc_name ," +
"(select sum(main_value) " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value > 0 " +
" and acc_no like concat(acc.acc_no, '%') " + strDate + strStat + ") dept, " +
"(select sum(main_value) " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value > 0 " +
" and acc_no like concat(acc.acc_no, '%') " + strPrevDate + strStat + ") Prevdept, " +
" (select sum(main_value) * -1 " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value < 0 " +
" and acc_no like concat(acc.acc_no,'%')" + strDate + strStat + ") credit, " +
" (select sum(main_value) * -1 " +
" from journal_header h " +
" join journal_details d on (d.header_id = h.Pkid) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where main_value < 0 " +
" and acc_no like concat(acc.acc_no,'%')" + strPrevDate + strStat + ") Prevcredit, " +
" 0.00 balance_dept,0.00 balance_credit " +
" FROM accounts acc " +
" order by acc_no desc");
decimal dDept = 0;
decimal dCredit = 0;
decimal dPreDept = 0;
decimal dPreCredit = 0;
decimal dBalance = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
if (dtReport.Rows[i]["Acc_no"].ToString() == "11102")
{
int x = 1;
}
if (dtReport.Rows[i]["dept"].ToString() == "")
{
dDept = 0;
dtReport.Rows[i]["dept"] = "0";
}
else
{
dDept = Convert.ToDecimal(dtReport.Rows[i]["dept"].ToString());
}
if (dtReport.Rows[i]["Prevdept"].ToString() == "")
{
dtReport.Rows[i]["Prevdept"] = "0";
dPreDept = 0;
}
else
{
dPreDept = Convert.ToDecimal(dtReport.Rows[i]["Prevdept"].ToString());
}
if (dtReport.Rows[i]["credit"].ToString() == "")
{
dCredit = 0;
dtReport.Rows[i]["credit"] = "0";
}
else
{
dCredit = Convert.ToDecimal(dtReport.Rows[i]["credit"].ToString());
}
if (dtReport.Rows[i]["Prevcredit"].ToString() == "")
{
dtReport.Rows[i]["Prevcredit"] = "0";
dPreCredit = 0;
}
else
{
dPreCredit = Convert.ToDecimal(dtReport.Rows[i]["Prevcredit"].ToString());
}
if ((dDept + dPreDept) - (dCredit + dPreCredit) == 0)
{
dtReport.Rows.Remove(dtReport.Rows[i]);
i--;
}
else
{
dBalance = (dDept + dPreDept) - (dCredit + dPreCredit);
if (dBalance < 0)
{
dtReport.Rows[i]["balance_credit"] = (dBalance * -1).ToString();
}
else
{
dtReport.Rows[i]["balance_dept"] = (dBalance).ToString();
}
}
}
report.SetDataSource(dtReport);
report.SetParameterValue("From", (ckbSelectDate.Checked == false ? "" : dtpFrom.Value.ToString("dd/MM/yyyy")));
report.SetParameterValue("To", (ckbSelectDate.Checked == false ? DateTime.Now.ToString("dd/MM/yyyy") : dtpTo.Value.ToString("dd/MM/yyyy")));
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
