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