protected void doData1Rpt() { string person_id, date, report; person_id = Request["PERSON_ID"].ToString(); date = Request["DATE"].ToString(); report = Request["REPORT"].ToString(); DataSet1.Data2_HDataTable dtH = new DataSet1.Data2_HDataTable(); DataSet1.Data2_DDataTable dtD = new DataSet1.Data2_DDataTable(); DataRow dr; DataSet ds = new DataSet(); string sqlcmd = ""; sqlcmd = sqlcmd + "select c.pif_name ,a.person_id ,a.floor_no,a.bed_no, DATE_FORMAT(a.dialysis_date,'%Y-%m-%d') as dialysis_date, a.dialysis_time,a.column_1,a.column_2,a.column_3,a.column_4,a.column_5,a.column_6,a.column_7,"; sqlcmd = sqlcmd + " a.column_8 ,a.column_9 ,a.column_10,a.column_41,b.maxtime from data_list a,"; sqlcmd = sqlcmd + " (select person_id,floor_no,bed_no,dialysis_date,max(dialysis_time) as maxtime from data_list "; sqlcmd = sqlcmd + " group by person_id,floor_no,bed_no,dialysis_date) b, pat_info c "; sqlcmd = sqlcmd + " where a.person_id= b.person_id "; sqlcmd = sqlcmd + " and a.floor_no=b.floor_no "; sqlcmd = sqlcmd + " and a.bed_no=b.bed_no "; sqlcmd = sqlcmd + " and a.dialysis_date = b.dialysis_date "; sqlcmd = sqlcmd + " and a.dialysis_time = b.maxtime "; sqlcmd = sqlcmd + " and a.person_id = c.pif_ic "; sqlcmd = sqlcmd + " and a.person_id ='" + person_id + "' and a.dialysis_date ='" + date + "' "; DataTable dt_H = db.Query(sqlcmd); for (int i = 0; i < dt_H.Rows.Count; i++) { dr = dtH.NewRow(); dr.ItemArray = dt_H.Rows[i].ItemArray; dtH.Rows.Add(dr); } sqlcmd = " select c.pif_name , a.person_id ,a.floor_no,a.bed_no,a.dialysis_date,a.dialysis_time,a.column_7,a.column_8,a.column_9,a.column_41, (hour(dialysis_time)*60 + minute(dialysis_time)) - b.col_min Elapsedtime from data_list a, "; sqlcmd = sqlcmd + " (select person_id,floor_no,bed_no,dialysis_date,(hour(MIN(dialysis_time))*60 + MINUTE(MIN(dialysis_time))) as col_min from data_list "; sqlcmd = sqlcmd + " group by person_id,floor_no,bed_no,dialysis_date) b ,pat_info c"; sqlcmd = sqlcmd + " where a.person_id= b.person_id "; sqlcmd = sqlcmd + " and a.floor_no=b.floor_no"; sqlcmd = sqlcmd + " and a.bed_no=b.bed_no"; sqlcmd = sqlcmd + " and a.dialysis_date = b.dialysis_date"; sqlcmd = sqlcmd + " and a.person_id = c.pif_ic"; sqlcmd = sqlcmd + " and a.person_id ='" + person_id + "' and a.dialysis_date ='" + date + "' "; sqlcmd = sqlcmd + " order by a.dialysis_date,a.dialysis_time"; DataTable dt_D = db.Query(sqlcmd); for (int i = 0; i < dt_D.Rows.Count; i++) { dr = dtD.NewRow(); dr.ItemArray = dt_D.Rows[i].ItemArray; dtD.Rows.Add(dr); } ds.Tables.Add(dtH); ds.Tables.Add(dtD); ReportDocument objRpt = new ReportDocument(); //objRpt.Load(Server.MapPath("data1.rpt")); objRpt.Load(Server.MapPath("data1.rpt")); //以下順序一定要對,先令報表中參數都為靜態,就是拋轉但報表不接 //參數都在sqlcmd兜好一切 objRpt.SetDataSource(ds); //只塞第二個DataTable就好 //objRpt.SetDataSource(dt_D); CRViewer.ReportSource = objRpt; objRpt.SetParameterValue(0, person_id.ToString()); ParameterDiscreteValue pdv = new ParameterDiscreteValue(); pdv.Value = date; objRpt.ParameterFields["dialysis_date"].CurrentValues.Clear(); objRpt.ParameterFields["dialysis_date"].DefaultValues.Clear(); objRpt.ParameterFields["dialysis_date"].CurrentValues.Add(pdv); }
protected void doData22HDFRpt(string person_id, string date) { DataSet1.Data2_HDataTable dtH = new DataSet1.Data2_HDataTable(); DataSet1.Data2_DDataTable dtD = new DataSet1.Data2_DDataTable(); DataRow dr; DataSet ds = new DataSet(); string sqlcmd = null; sqlcmd = "select c.pif_name, a.person_id, a.floor_no, a.bed_no, DATE_FORMAT(a.dialysis_date,'%Y-%m-%d') AS dialysis_date, a.dialysis_time, " + "a.column_1, a.column_2, a.column_3, a.column_4, a.column_5, " + "a.column_6, a.column_7, a.column_8, a.column_9, a.column_10, a.column_41, b.maxtime,a.column_47,a.column_48,a.column_49 " + "from data_list a, " + "(select person_id, floor_no, bed_no, dialysis_date, max(dialysis_time) as maxtime " + " from data_list " + " where person_id = '" + person_id + "' " + " group by person_id, floor_no, bed_no, dialysis_date) b, pat_info c " + "where a.person_id=b.person_id " + "and a.floor_no=b.floor_no " + "and a.bed_no=b.bed_no " + "and a.dialysis_date=b.dialysis_date " + "and a.dialysis_time=b.maxtime " + "and a.person_id=c.pif_ic " + "and a.person_id='" + person_id + "' " + "and a.dialysis_date='" + date + "' "; DataTable dt_H = db.Query(sqlcmd); for (int i = 0; i < dt_H.Rows.Count; i++) { dr = dtH.NewRow(); dr.ItemArray = dt_H.Rows[i].ItemArray; dtH.Rows.Add(dr); } sqlcmd = null; sqlcmd = "select c.pif_name, a.person_id, a.floor_no, a.bed_no, DATE_FORMAT(a.dialysis_date,'%Y-%m-%d') AS dialysis_date, a.dialysis_time, " + "a.column_7, a.column_8, a.column_9, a.column_41, " + "(hour(dialysis_time)*60 + minute(dialysis_time)) - b.col_min Elapsedtime " + "from data_list a, " + "(select person_id, floor_no, bed_no, dialysis_date, " + "(hour(MIN(dialysis_time))*60 + MINUTE(MIN(dialysis_time))) as col_min " + "from data_list " + " where person_id = '" + person_id + "' " + " group by person_id, floor_no, bed_no, dialysis_date) b, pat_info c " + "where a.person_id=b.person_id " + "and a.floor_no=b.floor_no " + "and a.bed_no=b.bed_no " + "and a.dialysis_date=b.dialysis_date " + "and a.person_id=c.pif_ic " + "and a.person_id='" + person_id + "' " + "and a.dialysis_date='" + date + "' " + "order by a.dialysis_date, a.dialysis_time "; DataTable dt_D = db.Query(sqlcmd); for (int i = 0; i < dt_D.Rows.Count; i++) { dr = dtD.NewRow(); dr.ItemArray = dt_D.Rows[i].ItemArray; dtD.Rows.Add(dr); } ds.Tables.Add(dtH); ds.Tables.Add(dtD); ReportDocument objRpt = new ReportDocument(); objRpt.Load(Server.MapPath("data22HDF.rpt")); //以下順序一定要對,先令報表中參數都為靜態,就是拋轉但報表不接 //參數都在sqlcmd兜好一切 objRpt.SetDataSource(ds); //只塞第二個DataTable就好 CRViewer.ReportSource = objRpt; }
private void Page_Init(object sender, EventArgs e) { try { person_id = Request["PERSON_ID"].ToString(); date = Request["DATE"].ToString(); report = Request["REPORT"].ToString(); Label1.Text = Request["patient_name"].ToString(); ReportDocument rpt = new ReportDocument(); switch (report) { case "1": rpt.Load(Server.MapPath("dialysis.rpt")); break; case "2": rpt.Load(Server.MapPath("data2.rpt")); break; default: Console.WriteLine("Default case"); break; } DataSet1.Data2_HDataTable dtH = new DataSet1.Data2_HDataTable(); DataSet1.Data2_DDataTable dtD = new DataSet1.Data2_DDataTable(); DataRow dr; DataSet ds = new DataSet(); if (report != "2") { ParameterDiscreteValue pdv = new ParameterDiscreteValue(); pdv.Value = person_id; rpt.ParameterFields["person_id"].CurrentValues.Clear(); rpt.ParameterFields["person_id"].DefaultValues.Clear(); rpt.ParameterFields["person_id"].CurrentValues.Add(pdv); pdv.Value = date; rpt.ParameterFields["dialysis_date"].CurrentValues.Clear(); rpt.ParameterFields["dialysis_date"].DefaultValues.Clear(); rpt.ParameterFields["dialysis_date"].CurrentValues.Add(pdv); } else { string sql = ""; sql = "select c.pif_name, a.person_id, a.floor_no, a.bed_no, DATE_FORMAT(a.dialysis_date,'%Y-%m-%d') AS dialysis_date, a.dialysis_time, " + "a.column_1, a.column_2, a.column_3, a.column_4, a.column_5, " + "a.column_6, a.column_7, a.column_8, a.column_9, a.column_10, a.column_41, b.maxtime " + "from data_list a, " + "(select person_id, floor_no, bed_no, dialysis_date, max(dialysis_time) as maxtime " + "from data_list group by person_id, floor_no, bed_no, dialysis_date) b, pat_info c " + "where a.person_id=b.person_id " + "and a.floor_no=b.floor_no " + "and a.bed_no=b.bed_no " + "and a.dialysis_date=b.dialysis_date " + "and a.dialysis_time=b.maxtime " + "and a.person_id=c.pif_ic " + "and a.person_id='" + person_id + "' " + "and a.dialysis_date='" + date + "' "; DataTable dt_H = db.Query(sql); for (int i = 0; i < dt_H.Rows.Count; i++) { dr = dtH.NewRow(); dr.ItemArray = dt_H.Rows[i].ItemArray; dtH.Rows.Add(dr); } sql = "select c.pif_name, a.person_id, a.floor_no, a.bed_no, DATE_FORMAT(a.dialysis_date,'%Y-%m-%d') AS dialysis_date, a.dialysis_time, " + "a.column_7, a.column_8, a.column_9, a.column_41, " + "(hour(dialysis_time)*60 + minute(dialysis_time)) - b.col_min Elapsedtime " + "from data_list a, " + "(select person_id, floor_no, bed_no, dialysis_date, " + "(hour(MIN(dialysis_time))*60 + MINUTE(MIN(dialysis_time))) as col_min " + "from data_list " + "group by person_id, floor_no, bed_no, dialysis_date) b, pat_info c " + "where a.person_id=b.person_id " + "and a.floor_no=b.floor_no " + "and a.bed_no=b.bed_no " + "and a.dialysis_date=b.dialysis_date " + "and a.person_id=c.pif_ic " + "and a.person_id='" + person_id + "' " + "and a.dialysis_date='" + date + "' " + "order by a.dialysis_date, a.dialysis_time "; DataTable dt_D = db.Query(sql); for (int i = 0; i < dt_D.Rows.Count; i++) { dr = dtD.NewRow(); dr.ItemArray = dt_D.Rows[i].ItemArray; dtD.Rows.Add(dr); } ds.Tables.Add(dtH); ds.Tables.Add(dtD); rpt.SetDataSource(ds); } CRViewer.ReportSource = rpt; //ConnectionInfo connInfo = new ConnectionInfo(); //connInfo.ServerName = "192.168.1.130"; //connInfo.DatabaseName = "mysql"; //connInfo.UserID = "root"; //connInfo.Password = ""; //TableLogOnInfos tableLogOnInfos = CRViewer.LogOnInfo; //foreach (TableLogOnInfo tableLogOnInfo in tableLogOnInfos) //{ // tableLogOnInfo.ConnectionInfo = connInfo; //} } catch (Exception ex) { //_ErrorMsgShow(ex.Message.ToString()); string s = ex.ToString(); } }