protected void AddItemToProductSortDLL() { clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string sql = "select product_ja, product_id from [kaigoryoku].[dbo].[TM_ProductName]"; SqlDataReader reader = clsdb.GetReader(sql); while (reader.Read()) { ListItem li = new ListItem(reader.GetValue(0).ToString(), reader.GetValue(1).ToString()); DDL_ProductSort.Items.Add(li); } clsdb.closedb(); }
public Tuple <int, int, DataTable> GetLatestBarthelInfo() { DataTable dt = GetTableHedder(); int max_score = 0; int act_score = 0; clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string sql = "select bi.index_item ,bi.max_score , ba.score , ba.index_question "; sql = sql + "from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementScore] bs "; sql = sql + "inner join [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] bl "; sql = sql + "on bs.barthel_list_id = bl.barthel_list_id "; sql = sql + "inner join[kaigoryoku].[dbo].[TM_BarthelIndexAnser] ba "; sql = sql + "on bs.index_id = ba.index_id and bs.anser_id = ba.anser_id "; sql = sql + "inner join[kaigoryoku].[dbo].[TM_BarthelIndexItem] bi "; sql = sql + "on bs.index_id = bi.index_id "; sql = sql + "where bs.barthel_list_id = "; sql = sql + "(select top 1 barthel_list_id from[kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] "; sql = sql + "where riyousya_id = " + user_id + " order by measure_dt desc)"; try { SqlDataReader reader = clsdb.GetReader(sql); while (reader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i <= reader.FieldCount - 1; i++) { dr[i] = reader.GetValue(i).ToString(); } max_score = max_score + int.Parse(reader.GetValue(1).ToString()); act_score = act_score + int.Parse(reader.GetValue(2).ToString()); dt.Rows.Add(dr); } } catch (Exception ex) { } finally { clsdb.closedb(); } var result = Tuple.Create(max_score, act_score, dt); return(result); }
private void InitUserTable() { clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string sql = "select riyousya_name from [kaigoryoku].[dbo].[TM_Riyousya] "; sql = sql + "where riyousya_id =" + user_id; SqlDataReader reader = clsdb.GetReader(sql); reader.Read(); la_username.Text = "名前:=" + reader.GetValue(0).ToString() + "測定日:="; clsdb.closedb(); }
public DataTable GetLatestAnserDataTableTotal() { DataTable dt = GetTableHedder(); clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string sql = "select bi.index_item , ba.score, "; sql = sql + "case bs.anser_id when ba.anser_id then cast(ba.score as nvarchar(2)) else '' end as 回答, ba.index_question "; sql = sql + "from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementScore] bs "; sql = sql + "inner join [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] bl "; sql = sql + "on bs.barthel_list_id = bl.barthel_list_id "; sql = sql + "inner join [kaigoryoku].[dbo].[TM_BarthelIndexAnser] ba "; sql = sql + "on bs.index_id = ba.index_id "; sql = sql + "inner join [kaigoryoku].[dbo].[TM_BarthelIndexItem] bi "; sql = sql + "on bs.index_id = bi.index_id "; sql = sql + "where bs.barthel_list_id = "; sql = sql + "(select top 1 barthel_list_id from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] "; sql = sql + "where riyousya_id = " + user_id; sql = sql + " order by measure_dt desc)"; try { SqlDataReader reader = clsdb.GetReader(sql); while (reader.Read()) { DataRow dr = dt.NewRow(); for (int i = 0; i <= reader.FieldCount - 1; i++) { dr[i] = reader.GetValue(i).ToString(); } dt.Rows.Add(dr); } } catch (Exception ex) { } finally { clsdb.closedb(); } return(dt); }
private bool IsInsertBarthelIndexList(int id, DateTime dt) { bool flg = false; clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); SqlDataReader reader = clsdb.GetReader("select * from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] " + "where riyousya_id = " + id + " and measure_dt = '" + dt + "'"); if (reader.Read()) { flg = false; } else { flg = true; } clsdb.closedb(); return(flg); }
protected void Btn_SerchNameFromBScore_Click(object sender, EventArgs e) { int min_score = int.Parse(TxtBx_MinBarthelScore.Text); int max_score = int.Parse(TxtBx_MaxBarthelScore.Text); lb_searchedname.Items.Clear(); string str_sql = " select r.riyousya_name , base_data.riyousya_id, sum(ba.score) 得点 from "; str_sql = str_sql + "[kaigoryoku].[dbo].[T_BarthelIndexMeasurementScore] bs "; str_sql = str_sql + " inner join (select barthel_list_id, bl.riyousya_id from "; str_sql = str_sql + "[kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] bl "; str_sql = str_sql + "inner join (select riyousya_id, max(measure_dt) measure_dt from "; str_sql = str_sql + "[kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] "; str_sql = str_sql + "group by riyousya_id ) saisin on bl.riyousya_id = saisin.riyousya_id "; str_sql = str_sql + "and bl.measure_dt = saisin.measure_dt) base_data "; str_sql = str_sql + "on bs.barthel_list_id = base_data.barthel_list_id "; str_sql = str_sql + "inner join [kaigoryoku].[dbo].[TM_BarthelIndexAnser] ba "; str_sql = str_sql + "on bs.index_id = ba.index_id and bs.anser_id = ba.anser_id "; str_sql = str_sql + "inner join [kaigoryoku].[dbo].[TM_Riyousya] r on r.riyousya_id = base_data.riyousya_id "; str_sql = str_sql + "group by r.riyousya_name , base_data.riyousya_id"; clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); SqlDataReader reader = clsdb.GetReader(str_sql); while (reader.Read()) { int score = int.Parse(reader.GetValue(2).ToString()); if (min_score <= score && score <= max_score) { ListItem list = new ListItem(reader.GetValue(0).ToString(), reader.GetValue(1).ToString()); lb_searchedname.Items.Add(list); } } clsdb.closedb(); this.La_SearchCondition.Text = "検索結果<バーサルインデクス:" + min_score + "~" + max_score + ">"; }
protected DataTable getBarthelInfoHeader() { DataTable dt = new DataTable(); clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string str_sql = "SELECT [index_item] "; str_sql = str_sql + "FROM[kaigoryoku].[dbo].[TM_barthelIndexItem] "; str_sql = str_sql + "order by index_id "; SqlDataReader reader = clsdb.GetReader(str_sql); while (reader.Read()) { int i = 0; dt.Columns.Add(reader.GetValue(i).ToString()); } clsdb.closedb(); return(dt); }
private void WriteUserName(string initial_code) { lb_searchedname.Items.Clear(); string str_sql = "SELECT [riyousya_name], [riyousya_id] "; str_sql = str_sql + "FROM[kaigoryoku].[dbo].[TM_Riyousya] "; str_sql = str_sql + "WHERE left([furigana_ja],1) IN (" + initial_code + ") "; str_sql = str_sql + "order by furigana_ja"; clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); SqlDataReader reader = clsdb.GetReader(str_sql); while (reader.Read()) { ListItem list = new ListItem(reader.GetValue(0).ToString(), reader.GetValue(1).ToString()); lb_searchedname.Items.Add(list); } clsdb.closedb(); }
public Tuple <string, int> GetMeasureInfo(int id) { clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string sql = "select top 1 measure_dt , count(*) from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] "; sql = sql + "where riyousya_id =" + id + " order by measure_dt desc"; var result = Tuple.Create("", 0); SqlDataReader reader = clsdb.GetReader(sql); if (reader.Read()) { result = Tuple.Create(reader.GetValue(0).ToString(), int.Parse(reader.GetValue(1).ToString())); } else { result = Tuple.Create("", 0); } clsdb.closedb(); return(result); }
protected void AddBedInfo(int id) { DataTable dt = new DataTable(); dt.Columns.Add("メーカー"); dt.Columns.Add("型番"); dt.Columns.Add("購入日"); clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); string str_sql = "SELECT pm.maker_ja , f.lot_no , f.purchase_dt "; str_sql = str_sql + "FROM [kaigoryoku].[dbo].[T_Furnitures] f "; str_sql = str_sql + "inner join [kaigoryoku].[dbo].[TM_ProductName] pn on f.product_id = pn.product_id "; str_sql = str_sql + "inner join [kaigoryoku].[dbo].[TM_ProductMaker] pm on f.maker_id = pm.maker_id "; str_sql = str_sql + "where pn.product_ja = 'ベッド' and f.riyousya_id = " + id; SqlDataReader reader = clsdb.GetReader(str_sql); while (reader.Read()) { DataRow dr = dt.NewRow(); dr["メーカー"] = main.TrimString(reader.GetValue(reader.GetOrdinal("maker_ja"))); dr["型番"] = main.TrimString(reader.GetValue(reader.GetOrdinal("lot_no"))); dr["購入日"] = main.TrimString(reader.GetValue(reader.GetOrdinal("purchase_dt"))); dt.Rows.Add(dr); } gv_userBedInfo.DataSource = dt; gv_userBedInfo.DataBind(); clsdb.closedb(); }
private void DrowWaterGraph(int calc_count, int user_id) { DateTime end_dt = DateTime.Today; DateTime start_dt = end_dt.AddDays(-1 * calc_count); string[] legends = new string[] { "g1", "g2", "g3", "g4", "g5", "g6", "g7", "g8", "g9", "g10", "g11", "g12" }; //凡例 Chart1.Series.Clear(); //グラフ初期化 foreach (var item in legends) { Chart1.Series.Add(item); Chart1.Series[item].ChartType = System.Web.UI.DataVisualization.Charting.SeriesChartType.StackedColumn; //Chart1.Series[item].Name = item; } while (start_dt != end_dt) { string str = string.Format("select intake_time_start , intake_time_end , intake_value from [kaigoryoku].[dbo].[T_WaterIntake] " + "where riyousya_id = {0} and intake_day = '{1}' order by intake_time_start", user_id, start_dt); clsDataBase clsdb = new clsDataBase(main.GetConnectionString()); SqlDataReader reader = clsdb.GetReader(str); int total_value = 0; int i = 1; System.Web.UI.DataVisualization.Charting.DataPoint dp = new System.Web.UI.DataVisualization.Charting.DataPoint(); while (reader.Read()) { //凡例が無い場合ココで設定 if (string.IsNullOrEmpty(Chart1.Series["g" + i.ToString()].LegendText)) { Chart1.Series["g" + i.ToString()].LegendText = reader.GetValue(0).ToString(); } dp = new System.Web.UI.DataVisualization.Charting.DataPoint(); dp.SetValueXY(start_dt.ToString(), reader.GetValue(2)); if (reader.GetValue(2).ToString() == "0") { dp.IsValueShownAsLabel = false; } else { dp.IsValueShownAsLabel = true; } Chart1.Series["g" + i.ToString()].Points.Add(dp); total_value = total_value + int.Parse(reader.GetValue(2).ToString()); i++; } clsdb.closedb(); start_dt = start_dt.AddDays(1); } /* * clsDataBase db = new clsDataBase(main.GetConnectionString()); * SqlDataReader reader = db.GetReader(str); * while (reader.Read()) * { * string tmp_dt = reader.GetValue(0).ToString(); * Chart1.Series["series1"].Points.AddXY(tmp_dt.Substring(1, 10), reader.GetValue(1)); * * * } * * db.closedb(); */ }