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