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(); }