protected void WriteBaseUseInfo(int id)
{
string str_sql = "SELECT [riyousya_name],[furigana_ja], [location_ja], [birth_dt] ";
str_sql = str_sql + "FROM[kaigoryoku].[dbo].[TM_Riyousya] ";
str_sql = str_sql + "WHERE riyousya_id = " + id;
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
SqlDataReader reader = clsdb.GetReader(str_sql);
reader.Read();
DateTime birth_dt = (DateTime)reader.GetValue(reader.GetOrdinal("birth_dt"));
int age = DateTime.Today.Year - birth_dt.Year;
if (birth_dt > DateTime.Today.AddYears(-age))
{
age--;
}
this.la_userfurigana.Text = reader.GetValue(reader.GetOrdinal("furigana_ja")).ToString();
this.la_username.Text = reader.GetValue(reader.GetOrdinal("riyousya_name")).ToString();
this.TB_HeyaBan.Text = reader.GetValue(reader.GetOrdinal("location_ja")).ToString();
this.TB_BirthDay.Text = birth_dt.ToString().Substring(0, 10);
this.TB_Age.Text = age.ToString();
clsdb.closedb();
img_userface.ImageUrl = "/image/" + id.ToString() + ".jpg";
}
private void DrowWaterGraph(int calc_count, int user_id, string start_time, System.Web.UI.DataVisualization.Charting.Chart cht)
{
DateTime end_dt = DateTime.Today;
DateTime start_dt = end_dt.AddDays(-1 * calc_count);
cht.Series.Add("series1");
cht.Series["series1"].XValueType = System.Web.UI.DataVisualization.Charting.ChartValueType.Int32;
//cht.Series["series1"].ChartType = System.Web.UI.DataVisualization.Charting.SeriesChartType.Line;
cht.ChartAreas[0].AxisX.LabelStyle.Enabled = false;
string str = "select w.intake_day , sum(w.intake_value) from [kaigoryoku].[dbo].[T_WaterIntake] w ";
str = str + "group by w.riyousya_id , w.intake_day, w.intake_time_start ";
str = str + "having w.riyousya_id = " + user_id;
str = str + " and w.intake_day between '" + start_dt + "' and '" + end_dt + "' ";
str = str + " and w.intake_time_start = '" + start_time + "'";
str = str + "order by w.intake_day ";
clsDataBase db = new clsDataBase(main.GetConnectionString());
SqlDataReader reader = db.GetReader(str);
while (reader.Read())
{
string tmp_dt = reader.GetValue(0).ToString();
cht.Series["series1"].Points.AddXY(tmp_dt.Substring(1, 10), reader.GetValue(1));
}
db.closedb();
}
public int InsertBarthelIndexList(DateTime measure_dt, int user_id, ref string mes)
{
if (IsInsertBarthelIndexList(user_id, measure_dt) == false)
{
mes = "すでにデータが存在しています";
return(0);
}
else
{
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
string sql = "insert into [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] ";
sql = sql + "(measure_dt, barthel_list_id, riyousya_id) ";
sql = sql + "values('" + measure_dt + "',(select case when max(barthel_list_id) is null then 1 else max(barthel_list_id) + 1 end ";
sql = sql + "from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList]), " + user_id + ")";
clsdb.ExecuteSQL(sql);
SqlDataReader reader = clsdb.GetReader("select barthel_list_id from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] " +
"where riyousya_id =" + user_id + " and measure_dt = '" + measure_dt + "'");
reader.Read();
int tmp_id = int.Parse(reader.GetValue(0).ToString());
clsdb.closedb();
return(tmp_id);
}
}
public DataTable GetPastBarthelDataTable(int id)
{
int times = 1;
DataTable dt = new DataTable();
dt.Columns.Add("回数");
dt.Columns.Add("測定日");
dt.Columns.Add("点数");
string sql = "select bl.measure_dt, sum(ba.score) as 点数 from [kaigoryoku].[dbo].[T_BarthelIndexMeasurementList] bl ";
sql = sql + "inner join [kaigoryoku].[dbo].[T_BarthelIndexMeasurementScore] bs on bl.barthel_list_id = bs.barthel_list_id ";
sql = sql + "inner join [kaigoryoku].[dbo].[TM_BarthelIndexAnser] ba on bs.index_id = ba.index_id and bs.anser_id = ba.anser_id ";
sql = sql + "where bl.riyousya_id = " + id + " group by bl.measure_dt order by bl.measure_dt ";
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
SqlDataReader reader = clsdb.GetReader(sql);
while (reader.Read())
{
DataRow dr = dt.NewRow();
dr[0] = times.ToString();
dr[1] = reader.GetValue(0).ToString();
dr[2] = reader.GetValue(1).ToString();
dt.Rows.Add(dr);
times++;
}
clsdb.closedb();
return(dt);
}
private void InitQuestionList()
{
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
string sql = "select bi.index_id ,ba.anser_id ,bi.index_item, ba.index_question ";
sql = sql + "from [kaigoryoku].[dbo].[TM_BarthelIndexAnser] ba ";
sql = sql + "inner join [kaigoryoku].[dbo].[TM_BarthelIndexItem] bi ";
sql = sql + "on ba.index_id = bi.index_id order by bi.index_id, ba.anser_id desc";
SqlDataReader reader = clsdb.GetReader(sql);
string question_ja = "";
while (reader.Read())
{
int question_id = int.Parse(reader.GetValue(0).ToString());
if (reader.GetValue(2).ToString() != question_ja)
{
question_ja = reader.GetValue(2).ToString();
WriteQuestionLabel(question_id, question_ja);
}
AddAnserToListbox(int.Parse(reader.GetValue(0).ToString()), int.Parse(reader.GetValue(1).ToString()), reader.GetValue(3).ToString());
}
clsdb.closedb();
}
protected void AddBedMakerToList()
{
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
clsdb.closedb();
}
public void InsertBarthelIndexScore(int barthel_id, int index_id, int anser_id)
{
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
string sql = "insert into [kaigoryoku].[dbo].[T_BarthelIndexMeasurementScore] ";
sql = sql + "(barthel_list_id , index_id , anser_id) ";
sql = sql + "values(" + barthel_id + "," + index_id + "," + anser_id + " )";
clsdb.ExecuteSQL(sql);
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);
}
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();
}
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);
}
protected void bt_UpdateBaseUserInfo_Click(object sender, EventArgs e)
{
ListItem list = (ListItem)lb_searchedname.SelectedItem;
int id = int.Parse(list.Value.ToString());
DateTime birth_dt = DateTime.Parse(this.TB_BirthDay.Text.ToString());
string location = this.TB_HeyaBan.Text.ToString();
clsDataBase clsdb = new clsDataBase(main.GetConnectionString());
string sql = string.Format("update [kaigoryoku].[dbo].[TM_Riyousya] " +
"set birth_dt = '{0}' , location_ja = '{1}'" +
"where riyousya_id = {2}", birth_dt, location, id);
clsdb.ExecuteSQL(sql);
clsdb.closedb();
WriteBaseUseInfo(id);
this.CB_UnlockBaseUseInfo.Checked = true;
lockBaseUserInfo();
}
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();
*/
}