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();
        }
Beispiel #3
0
        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();
        }
Beispiel #4
0
        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();
        }
Beispiel #5
0
        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();
        }
Beispiel #8
0
        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();
        }
Beispiel #12
0
        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();
        }
Beispiel #13
0
        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();
        }