private void btnPrint_Click(object sender, EventArgs e)
{
if (txtPkid.Text == "")
{
glb_function.MsgBox("الرجاء اختيار القيد");
return;
}
cryRepVoucher report = new cryRepVoucher();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
TextObject txtTitel = (TextObject)report.ReportDefinition.ReportObjects["txtReportTitel"];
txtTitel.Text = "قيد افتتاحي";
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note, a.Acc_no,a.acc_name, " +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details " +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id) " +
" join accounts a on(a.pkid= d.acc_id) " +
" where h.pkid= " + txtPkid.Text);
report.SetDataSource(dtReport);
////// report.SetParameterValue("P_Date", (ckbSelectDate.IsChecked == true ? " من تاريخ : " + dtpFrom.SelectedDate.Value.ToString("dd/MM/yyyy") + " إلى تاريخ : " + dtpTo.SelectedDate.Value.ToString("dd/MM/yyyy") : ""));
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (txtPkid.Text == "")
{
glb_function.MsgBox("الرجاء اختيار القيد");
return;
}
cryRepCashPayments report = new cryRepCashPayments();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note,h.Person, a.Acc_no,a.acc_name, " +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details,trans_no " +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id) " +
" join accounts a on(a.pkid= d.acc_id) " +
" where d.main_value>0 and h.pkid= " + txtPkid.Text);
string strTotal = N2C.ConvertN2C.ConvertNow(Convert.ToDouble(txtDeptTotal.Text.Trim()), "ريال", "فلس");
report.SetDataSource(dtReport);
report.SetParameterValue("strCasher", "الصندوق الرئيسي");
report.SetParameterValue("strValueAlpha", strTotal);
report.SetParameterValue("strValue", txtDeptTotal.Text);
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (txtPkid.Text == "")
{
glb_function.MsgBox("الرجاء اختيار الفاتورة");
return;
}
cryRepSalesInvoice report = new cryRepSalesInvoice();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport = cnn.GetDataTable("select h.pkid, h.stat,date_format(h.created_date,'%d/%m/%Y') created_date, h.created_user, h.invoice_no, h.warehouse_id, " +
" h.pump_emp, h.pump_id, h.emp_id, h.old_counter, h.new_counter, " +
" h.invoice_note, h.item_id, h.qty, h.unitsellingPrice, h.TotalSellingPrice, " +
" i.itemno, i.itemname, " +
" w.warehouse_name, " +
" emp.empname " +
" from invoice_header h " +
" join items i on (i.pkid = h.item_id) " +
" join warehouse w on (w.pkid = h.warehouse_id) " +
" join emp on (emp.pkid = h.emp_id) " +
" where h.pkid = " + txtPkid.Text);
report.SetDataSource(dtReport);
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (txtPkid.Text == "")
{
glb_function.MsgBox("الرجاء اختيار الفاتورة");
return;
}
cryRepSalesInvoice report = new cryRepSalesInvoice();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport = cnn.GetDataTable("SELECT h.pkid pkheader,h.invoice_no,h.invoice_note,h.invoice_value,date_format(h.invoice_date,'%d/%m/%Y') invoice_date , " +
" d.warehouse_id, d.pump_emp, d.pump_id, p.PumpNo, p.PumpName, d.emp_id, e.empname, d.old_counter, d.new_counter, " +
" d.item_id, i.itemname, d.qty, d.unitsellingPrice, d.TotalSellingPrice " +
" FROM invoice_header h " +
" join invoice_details d on(h.pkid = d.header_id) " +
" join pumps p on(p.pkid = d.pump_id) " +
" join emp e on(e.pkid = d.emp_id) " +
" join items i on(i.pkid = d.item_id) " +
" where h.pkid = " + txtPkid.Text);
report.SetDataSource(dtReport);
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (lstWarehouses.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار المخزن");
lstWarehouses.Focus();
return;
}
if (lstItems.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار الصنف");
lstItems.Focus();
return;
}
cryRepItemTrans report = new cryRepItemTrans();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport.Clear();
dtReport = cnn.GetDataTable("SELECT pkid,date_format(trans_date,'%d/%m/%Y') created_date, " +
" trans_name, trans_no, qty, 0 Balance " +
" FROM item_trans " +
" where warehouse_id = " + lstWarehouses.SelectedValue.ToString() + " and item_id =" + lstItems.SelectedValue.ToString() +
" order by trans_date");
double dBalance = 0;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
dtReport.Rows[i]["Balance"] = Convert.ToDouble(dtReport.Rows[i]["qty"].ToString()) + dBalance;
dBalance = Convert.ToDouble(dtReport.Rows[i]["Balance"].ToString());
}
report.SetDataSource(dtReport);
report.SetParameterValue("From", lstWarehouses.Text);
report.SetParameterValue("To", lstItems.Text);
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (txtPkid.Text == "")
{
glb_function.MsgBox("الرجاء اختيار الأمر");
return;
}
string strTitel = "";
if (strStackadj_type == "امر توريد")
{
strTitel = "أمر توريد مخزني";
}
else
{
strTitel = "أمر صرف مخزني";
}
cryRepInventoryIn report = new cryRepInventoryIn();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
TextObject txtCurrency = (TextObject)report.ReportDefinition.ReportObjects["txtReportTitel"];
txtCurrency.Text = strTitel;
ConnectionToMySQL cnn = new ConnectionToMySQL();
dtReport = cnn.GetDataTable("SELECT h.pkid,h.stockadj_no,h.warehosue_id,h.stackadj_note,date_format(h.created_date,'%d/%m/%Y') created_date, " +
" d.pkid dpkid, d.itemid, d.qty, " +
" i.itemno, i.itemname, i.UnitSellingPrice, i.unitCost, " +
" a.pkid acc_id, a.Acc_no, a.acc_name, " +
" w.warehouse_name " +
" FROM stockadjust_header h " +
" join stockadjust_details d on(h.pkid = d.header_id) " +
" join items i on(d.itemid = i.pkid) " +
" join accounts a on(a.pkid = h.acc_id) " +
" join warehouse w on(w.pkid = h.warehosue_id) " +
" and h.pkid = " + txtPkid.Text);
report.SetDataSource(dtReport);
frm.CrystalReportsViewer1.ReportSource = report;
frm.ShowDialog();
}
private void btnPrint_Click(object sender, EventArgs e)
{
cryRepSalesInvoice report = new cryRepSalesInvoice();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
string strDate = "";
if (ckbSelectDate.Checked == true)
{
strDate = " and h.invoice_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')";
}
dtReport.Clear();
dtReport = cnn.GetDataTable("SELECT h.pkid,date_format(h.invoice_date,'%d/%m/%Y') created_date,h.invoice_no, " +
" p.PumpName, d.old_counter, d.new_counter, d.qty, d.TotalSellingPrice " +
" FROM invoice_header h " +
" join invoice_details d on (h.pkid=d.header_id)" +
" join pumps p on(p.pkid = d.pump_id) " +
" where 1 = 1 " + strDate +
" order by h.pkid");
//DataSet ds= new DataSet();
//ds.Tables.Add(dtReport);
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();
}
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)
{
cryRepJournals report = new cryRepJournals();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
double dBalance = 0;
string strDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
if (txtJourNo.Text.Trim() != "")
{
strStat += " and jour_no='" + txtJourNo.Text.Trim() + "'";
}
if (ckbSelectDate.Checked == true)
{
strDate = " 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')";
dtReport = cnn.GetDataTable("select ifnull(sum(main_value) ,0) balance " +
" from journal_header h " +
" join journal_details d on (h.pkid = d.header_id) " +
" where h.created_date < str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y') " + strStat);
dBalance = Convert.ToDouble(dtReport.Rows[0][0].ToString());
}
else
{
dBalance = 0;
}
dtReport.Clear();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note, " +
" d.acc_id,a.Acc_no,a.acc_name," +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details,0 Balance, 'مدين' BalStat" +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id)" +
" join accounts a on (d.acc_id=a.pkid)" +
" where 1=1 " + strStat + strDate + " order by h.pkid,main_value desc");
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();
}
private void AccSheetSummary()
{
if (lstAccNo.SelectedIndex == -1)
{
glb_function.MsgBox("الرجاء اختيار الحساب");
return;
}
cryRepAccSheetSummary report = new cryRepAccSheetSummary();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
ConnectionToMySQL cnn = new ConnectionToMySQL();
TextObject txtArTitel = (TextObject)report.ReportDefinition.ReportObjects["txtArTitel"];
txtArTitel.Text = glb_function.strArabicTitel;
TextObject txtEnTitel = (TextObject)report.ReportDefinition.ReportObjects["txtEnTitel"];
txtEnTitel.Text = glb_function.strEnglishTitel;
double dBalance = 0;
string strDate = "";
string strStat = "";
if (ckbPosting.Checked == true)
{
strStat = " and h.stat in ('فعال', 'مرحل')";
}
else
{
strStat = " and h.stat in ( 'مرحل')";
}
strStat = strStat + " and ifnull(d.profitCenter,'') like '%" + lstCostCent.Text.Trim() + "%'";
if (ckbSelectDate.Checked == true)
{
strDate = " 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')";
dtReport = cnn.GetDataTable("select ifnull(sum(main_value) ,0) balance " +
" from journal_header h " +
" join journal_details d on (h.pkid = d.header_id) " +
" where d.acc_id=" + lstAccNo.SelectedValue.ToString() + " and h.created_date < str_to_date('" + dtpFrom.Value.ToString("dd/MM/yyyy") + "','%d/%m/%Y') " + strStat);
dBalance = Convert.ToDouble(dtReport.Rows[0][0].ToString());
}
else
{
dBalance = 0;
}
dtReport.Clear();
dtReport = cnn.GetDataTable("select h.pkid,h.jour_no,h.trans_no,h.trans_name,date_format(h.jour_date,'%d/%m/%Y') jour_date,h.jour_note, " +
" if (d.main_value > 0,d.main_value,0) Dept,if (d.main_value < 0,d.main_value * -1,0) Credit,d.jour_details,0 Balance, 'مدين' BalStat" +
" from journal_header h " +
" join journal_details d on(h.pkid= d.header_id)" +
" where d.acc_id=" + lstAccNo.SelectedValue.ToString() + strStat + strDate + " order by h.jour_date ,d.pkid ");
for (int i = 0; i < dtReport.Rows.Count; i++)
{
dBalance = dBalance + Convert.ToDouble(dtReport.Rows[i]["Dept"].ToString()) - Convert.ToDouble(dtReport.Rows[i]["Credit"].ToString());
if (dBalance < 0)
{
dtReport.Rows[i]["Balance"] = dBalance * -1;
dtReport.Rows[i]["BalStat"] = "دائن";
}
else
{
dtReport.Rows[i]["Balance"] = dBalance;
dtReport.Rows[i]["BalStat"] = "مدين";
}
// dtReport.Rows[i]["Balance"]
}
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("AccNo", lstAccNo.Text);
report.SetParameterValue("AccName", lstAccName.Text);
// report.SetParameterValue("strValueAlpha", new ConvertNumbersToArabicAlphabet(txtCreditTotal.Text).GetNumberAr());
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");
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 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();
}
private void mainButton2_Click(object sender, EventArgs e)
{
ConnectionToMySQL cnn = new ConnectionToMySQL();
frmReportContainer frm = new frmReportContainer();
DataTable dtReport = new DataTable();
dtReport = cnn.GetDataTable("select item_id,itemname,warehouse_id, warehouse_name, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty > 0),0) TodayInQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty < 0),0) TodayOutQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date < str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty > 0),0) PostingInQty, " +
" ifnull((select sum(qty) from item_trans PrevT where PrevT.item_id = trans.item_id and PrevT.warehouse_id = trans.warehouse_id and PrevT.trans_date < str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and PrevT.qty < 0),0) PostingOutQty," +
"0 TotalInQty,0 TotalOutQty,0 RemaindQty,'1' linkR" +
" from " +
" (SELECT item_id, warehouse_id " +
" FROM item_trans " +
" group by item_id, warehouse_id) trans " +
" join warehouse w on(w.pkid = trans.warehouse_id) " +
" join items i on(i.pkid= trans.item_id)");
for (int i = 0; i < dtReport.Rows.Count; i++)
{
dtReport.Rows[i]["TodayOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayOutQty"].ToString()) * -1;
dtReport.Rows[i]["PostingOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["PostingOutQty"].ToString()) * -1;
dtReport.Rows[i]["TotalInQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayInQty"].ToString()) + Convert.ToDecimal(dtReport.Rows[i]["PostingInQty"].ToString());
dtReport.Rows[i]["TotalOutQty"] = Convert.ToDecimal(dtReport.Rows[i]["TodayOutQty"].ToString()) + Convert.ToDecimal(dtReport.Rows[i]["PostingOutQty"].ToString());
dtReport.Rows[i]["RemaindQty"] = Convert.ToDecimal(dtReport.Rows[i]["TotalInQty"].ToString()) - Convert.ToDecimal(dtReport.Rows[i]["TotalOutQty"].ToString());
}
DataTable dtSubReport = new DataTable();
DataTable dtAccount = new DataTable();
dtSubReport.Columns.Add("acc_name");
dtSubReport.Columns.Add("accType");
dtSubReport.Columns.Add("TodayAcc");
dtSubReport.Columns.Add("PosingAcc");
dtSubReport.Columns.Add("TotalAcc");
dtSubReport.Columns.Add("linkR");
dtSubReport.Columns.Add("TodayNetAmount");
dtSubReport.Columns.Add("PosingNetAmount");
dtAccount = cnn.GetDataTable("select acc.acc_name ,'الايرادات' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '6110100001%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '6110100001%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
int iStart = 0;
int iEnd = 0;
decimal dTodayNetAmount = 0;
decimal dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
dtAccount.Rows.Clear();
dtAccount = cnn.GetDataTable("select acc.acc_name ,'المصروفات' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '5%' and a.Acc_no not in ('5110100001','5120100001','5140100001','5130100001') and a.Acc_no not like '52213%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no like '5%' and a.Acc_no not in ('5110100001','5120100001','5140100001','5130100001') and a.Acc_no not like '52213%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
dTodayNetAmount = 0;
dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
string str1 = dtAccount.Rows[i]["TodayAcc"].ToString();
string str2 = dtAccount.Rows[i]["PosingAcc"].ToString();
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
dtAccount.Rows.Clear();
dtAccount = cnn.GetDataTable("select acc.acc_name ,'أخرى' accType, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no not like '5%' and a.Acc_no not like '6%' " +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) TodayAcc, " +
" ifnull((select if (sum(d.main_value) < 0,(sum(d.main_value) * -1),sum(d.main_value)) " +
" from journal_header h " +
" join journal_details d on (h.Pkid = d.header_id) " +
" join accounts a on (a.pkid = d.acc_id) " +
" where h.jour_date between str_to_date('01/" + dtpDialyDate.Value.ToString("MM/yyyy") + "', '%d/%m/%Y') and str_to_date('" + dtpDialyDate.Value.AddDays(-1).ToString("dd/MM/yyyy") + "', '%d/%m/%Y') " +
" and a.Acc_no not like '5%' and a.Acc_no not like '6%' and a.acc_no not like '4%'" +
" and d.acc_id = acc.pkid " +
" group by d.acc_id " +
" having sum(d.main_value) != 0),0) PosingAcc,'0' TotalAcc,'1' linkR,0 TodayNetAmount, 0 PosingNetAmount " +
" from(" +
" select pkid, acc_name " +
" from accounts acc " +
" where level = 5) acc " +
"");
dTodayNetAmount = 0;
dPosingNetAmount = 0;
for (int i = 0; i < dtAccount.Rows.Count; i++)
{
if (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) != 0 || Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString()) != 0)
{
string str1 = dtAccount.Rows[i]["TodayAcc"].ToString();
string str2 = dtAccount.Rows[i]["PosingAcc"].ToString();
dtAccount.Rows[i]["TotalAcc"] = (Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString()) + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString())).ToString();
dtSubReport.Rows.Add();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["acc_name"] = dtAccount.Rows[i]["acc_name"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["accType"] = dtAccount.Rows[i]["accType"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TodayAcc"] = dtAccount.Rows[i]["TodayAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["PosingAcc"] = dtAccount.Rows[i]["PosingAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["TotalAcc"] = dtAccount.Rows[i]["TotalAcc"].ToString();
dtSubReport.Rows[dtSubReport.Rows.Count - 1]["linkR"] = dtAccount.Rows[i]["linkR"].ToString();
dTodayNetAmount = dTodayNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["TodayAcc"].ToString());
dPosingNetAmount = dPosingNetAmount + Convert.ToDecimal(dtAccount.Rows[i]["PosingAcc"].ToString());
iEnd++;
}
}
for (int i = iStart; i < iEnd; i++)
{
dtSubReport.Rows[i]["TodayNetAmount"] = dTodayNetAmount.ToString();
dtSubReport.Rows[i]["PosingNetAmount"] = dPosingNetAmount.ToString();
}
iStart = iEnd;
cryRepDialyActivity report = new cryRepDialyActivity();
//يجب اسناد التقرير الفرعي قبل التقرير الرئيسي
// ليتم ظهور البيانات
report.Subreports[0].SetDataSource(dtSubReport);
report.SetDataSource(dtReport);
// report.Refresh();
frm.CrystalReportsViewer1.ReportSource = report;
report.SetParameterValue("From", dtpDialyDate.Value.ToString("dd/MM/yyyy"));
// frm.CrystalReportsViewer1.Refresh();
frm.ShowDialog();
}