public virtual CrystalDecisions.CrystalReports.Engine.ReportDocument CreateReport() { cryRepDialyActivity rpt = new cryRepDialyActivity(); rpt.Site = this.Site; return(rpt); }
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(); }