• R/O
  • SSH
  • HTTPS

dietmemorer: Commit


Commit MetaInfo

Revision18 (tree)
Time2008-01-22 15:18:28
Authoryaggy

Log Message

コメントちょっぴり付けた。
マイナスのエネルギーを入力できるようにした。
体重・体脂肪を入力するようにした。

Change Summary

Incremental Difference

--- trunk/db/DDL.txt (revision 17)
+++ trunk/db/DDL.txt (revision 18)
@@ -1,4 +1,5 @@
11 DROP TABLE denergy;
2+DROP TABLE dfat;
23 DROP TABLE dweight;
34 DROP TABLE dmailhistory;
45
@@ -7,7 +8,6 @@
78 DROP TABLE muser;
89
910
10-
1111 CREATE TABLE muser (
1212 mailadd VARCHAR(128) PRIMARY KEY,
1313 adddate DATETIME
@@ -18,7 +18,7 @@
1818 mailadd VARCHAR(128),
1919 porpname VARCHAR(128),
2020 propvaluestr VARCHAR(128),
21- propvalueint INT UNSIGNED,
21+ propvalueint INT,
2222 CONSTRAINT pkey_duserprop PRIMARY KEY (
2323 mailadd,
2424 porpname
@@ -28,7 +28,7 @@
2828
2929 CREATE TABLE menergy (
3030 food VARCHAR(128),
31- energy INT UNSIGNED,
31+ energy INT,
3232 adddate DATETIME
3333 );
3434
@@ -38,7 +38,7 @@
3838 CREATE TABLE denergy (
3939 mailadd VARCHAR(128),
4040 food VARCHAR(128),
41- energy INT UNSIGNED,
41+ energy INT,
4242 adddate DATETIME
4343 );
4444
@@ -48,12 +48,21 @@
4848 CREATE TABLE dweight (
4949 mailadd VARCHAR(128),
5050 weight FLOAT,
51- adddate DATETIME
51+ adddate DATE
5252 );
5353
5454 CREATE INDEX idx_dweight_userdate ON dweight (mailadd, adddate);
5555
5656
57+CREATE TABLE dfat (
58+ mailadd VARCHAR(128),
59+ fat FLOAT,
60+ adddate DATE
61+);
62+
63+CREATE INDEX idx_dfat_userdate ON dfat (mailadd, adddate);
64+
65+
5766 CREATE TABLE dmailhistory (
5867 mailadd VARCHAR(128),
5968 mailsubject TEXT,
--- trunk/db/csvimport.sql (revision 17)
+++ trunk/db/csvimport.sql (revision 18)
@@ -1,2 +1,9 @@
11 load data local infile "./kcal.csv" into table menergy fields terminated by ',';
22
3+load data local infile "/tmp/denergy.csv" into table denergy fields terminated by ',';
4+load data local infile "/tmp/dfat.csv" into table dfat fields terminated by ',';
5+load data local infile "/tmp/dmailhistory.csv" into table dmailhistory fields terminated by ',';
6+load data local infile "/tmp/dweight.csv" into table dweight fields terminated by ',';
7+load data local infile "/tmp/menergy.csv" into table menergy fields terminated by ',';
8+load data local infile "/tmp/muser.csv" into table muser fields terminated by ',';
9+load data local infile "/tmp/muserprop.csv" into table muserprop fields terminated by ',';
--- trunk/db/csvexport.sql (nonexistent)
+++ trunk/db/csvexport.sql (revision 18)
@@ -0,0 +1,7 @@
1+SELECT * FROM denergy INTO OUTFILE "/tmp/denergy.csv" FIELDS TERMINATED BY ',';
2+SELECT * FROM dfat INTO OUTFILE "/tmp/dfat.csv" FIELDS TERMINATED BY ',';
3+SELECT * FROM dmailhistory INTO OUTFILE "/tmp/dmailhistory.csv" FIELDS TERMINATED BY ',';
4+SELECT * FROM dweight INTO OUTFILE "/tmp/dweight.csv" FIELDS TERMINATED BY ',';
5+SELECT * FROM menergy INTO OUTFILE "/tmp/menergy.csv" FIELDS TERMINATED BY ',';
6+SELECT * FROM muser INTO OUTFILE "/tmp/muser.csv" FIELDS TERMINATED BY ',';
7+SELECT * FROM muserprop INTO OUTFILE "/tmp/muserprop.csv" FIELDS TERMINATED BY ',';
--- trunk/src/mail_analizer.rb (revision 17)
+++ trunk/src/mail_analizer.rb (revision 18)
@@ -11,10 +11,10 @@
1111
1212 # メール解析および返信クラス
1313 class MailAnalizer
14-
14+
1515 # 無題の題名
1616 @@sub_nosubject = ["", "無題", "no subject", "nosubject", "登録"]
17-
17+
1818 # 今日の内訳機能のキーワード
1919 @@sub_today = "今日"
2020 # 今月の合計機能のキーワード
@@ -29,30 +29,57 @@
2929 @@sub_lastmonth = "詳細先月"
3030 # アンドゥ機能のキーワード
3131 @@sub_undo = "まちがい"
32-
32+
3333 # ヘルプのキーワード
3434 @@sub_help = ["ヘルプ", "へるぷ", "使い方", "つかいかた"]
35-
35+
3636 # 体重遷移機能のキーワード
3737 @@sub_weight = ["やせた?", "やせた?", "やせた"]
38-
38+
3939 # 体重記録のキーワード
40- @@body_weight="体重"
41-
40+ @@body_weight = ["体重", "たいじゅう", "おもさ"]
41+
42+ # 体脂肪記録のキーワード
43+ @@body_fat = ["体脂肪", "脂肪", "たいしぼう"]
44+
4245 # 一日最大エネルギーのキーワード
43- @@body_limit="最大"
44-
46+ @@body_limit = "最大"
47+
48+ # 登録時メール返信文
4549 @@repmail_first="mail/dmr_first.txt"
50+
51+ # フッタ文
4652 @@repmail_footer="mail/dmr_footer.txt"
53+
54+ # メモ時メール返信文
4755 @@repmail_memo="mail/dmr_memo.txt"
56+
57+ # ヘルプメール返信文
4858 @@repmail_help="mail/dmr_help.txt"
49-
59+
60+ # 曜日配列
5061 @@wdays = ["日", "月", "火", "水", "木", "金", "土"]
51-
62+
63+ # コンストラクタ
64+ # _dbh_ :: mysqlへのデータベースハンドル
5265 def initialize(dbh)
5366 @dbh = dbh
5467 end
55-
68+
69+ # 配列の中に含まれているかを返すメソッド
70+ # _ary_ :: 調べたい配列
71+ # _val_ :: 調べたい値
72+ # Return :: あれば true
73+ def self.in_ary(ary, val)
74+ if ary.index(val) == nil
75+ return false
76+ end
77+ return true
78+ end
79+
80+ # ファイルを読み込む
81+ # _path_ :: ファイルのパス
82+ # Return :: ファイルの内容
5683 def fileread(path)
5784 f = open(path)
5885 body = f.read
@@ -59,10 +86,13 @@
5986 f.close
6087 return body
6188 end
62-
89+
90+ # 読み込んだファイルの内容からサブジェクト(1行目)だけを読み込む
91+ # _fileall_ :: ファイルの内容
92+ # Return :: サブジェクト
6393 def getfilesubject(fileall)
6494 linenum = 1
65-
95+
6696 fileall.each_line {|line|
6797 if 1 == linenum then
6898 return line.chomp
@@ -70,11 +100,14 @@
70100 linenum = linenum + 1
71101 }
72102 end
73-
103+
104+ # 読み込んだファイルの内容からボディ(1行目以外)だけを読み込む
105+ # _fileall_ :: ファイルの内容
106+ # Return :: ボディ
74107 def getfilebody(fileall)
75108 linenum = 1
76109 ret = ""
77-
110+
78111 fileall.each_line {|line|
79112 if 1 != linenum then
80113 ret = ret + line
@@ -83,63 +116,75 @@
83116 }
84117 return ret
85118 end
86-
119+
120+ # すべての行の前後の空白を取り除く
121+ # _fileall_ :: 文字列
122+ # Return :: 取り除いた後の文字列
87123 def strip_lines(lines)
88124 newlines = ""
89-
125+
90126 lines.each_line {|line|
91127 newlines.concat(line.strip + "\n")
92128 }
93129 return newlines
94130 end
95-
96- def send_help(mailfrom, smtpserver, smtpfrom)
131+
132+ # ヘルプメッセージの送信
133+ # _to_ :: メール送信先アドレス
134+ # _smtpserver_ :: SMTPサーバ名
135+ # _from_ :: メール送信元アドレス
136+ def send_help(to, smtpserver, from)
97137 rep = fileread(@@repmail_help)
98138 repsub = getfilesubject(rep)
99139 repbody = getfilebody(rep) + fileread(@@repmail_footer)
100-
140+
101141 Debug.p "filebody = '" + repbody + "'"
102-
103- MailControl.sendmail(smtpserver, mailfrom, smtpfrom, repsub, repbody)
142+
143+ MailControl.sendmail(smtpserver, to, from, repsub, repbody)
104144 end
105-
145+
146+ # メールの解析
147+ # _mail_ :: メール内容の入った TMail::Mail オブジェクト
148+ # _smtpserver_ :: SMTPサーバ名
149+ # _smtpfrom_ :: メール送信元アドレス
106150 def analize(mail, smtpserver, smtpfrom)
107-
151+
108152 Debug.p "mailsubject = "
109153 Debug.p mail.subject
110-
154+
111155 mailbody = mail.body
112-
156+
113157 mailsub = mail.subject
114-
158+
115159 if mailsub then
116160 mailsub = Kconv::kconv(mailsub, Kconv::UTF8)
117161 else
118162 mailsub = ""
119163 end
120-
164+
121165 mailfrom = mail.from
122-
123-
166+
167+ # エラーメールっぽかったら無視
124168 if mailsub[/DAEMON/] or mailsub[/Daemon/] or mailsub[/daemon/] \
125169 or mailsub[/RETURN/] or mailsub[/Return/] or mailsub[/Return/] then
126170 Debug.p "Invalid mail..."
127171 return
128172 end
129-
173+
130174 Debug.p "from"
131175 Debug.p mailfrom
132-
176+
133177 sth = @dbh.prepare("SELECT mailadd, adddate FROM muser WHERE mailadd = ?")
134178 sth.execute(mailfrom)
135-
179+
180+ # ユーザ登録されていなければ登録
136181 if 0 != sth.rows then
137182 sth.fetch do |row|
138183 printf "mailadd %s, adddate %s\n", row[0], row[1]
139184 end
140-
141- if mailsub == @@sub_help[0] or mailsub == @@sub_help[1] \
142- or mailsub == @@sub_help[2] or mailsub == @@sub_help[3] \
185+
186+ # ヘルプ要求っぽかったら、ヘルプ送信
187+ if in_ary(@@sub_help, mailsub) \
143188 or mailbody == nil or mailbody == "" then
144189 sth.finish
145190 Debug.p "send help."
@@ -146,61 +191,100 @@
146191 send_help(mailfrom, smtpserver, smtpfrom)
147192 return
148193 end
149-
150-
194+
151195 Debug.p "subject"
152196 Debug.p mailsub
153-
197+
154198 Debug.p "body"
155199 utf8body = Kconv::kconv(mailbody, Kconv::UTF8)
156200 Debug.p utf8body
157-
201+
202+ # 邪魔な文字列を除去
158203 utf8body = utf8body.tr(" ", " ")
159204 utf8body = utf8body.tr("\t", " ")
160205 utf8body = utf8body.squeeze(" ")
161206 utf8body = strip_lines(utf8body)
162-
207+
163208 Debug.p "parse fields"
164209 nowtime = Time.now
210+
211+ # スペースで分割
165212 CSV::Reader.parse(utf8body, " ") {|field|
166213 if 1 == field.size then
167214 Debug.p field
168-
215+
216+ # 入力項目がひとつしかない場合は、そのもののいままでの平均値をあてにする
169217 if "" != field[0] and nil != field[0] then
170218 sql = "SELECT food, ROUND(AVG(energy), 0) as ave " + \
171219 " FROM menergy WHERE food = ? GROUP BY food "
172-
220+
173221 isth = @dbh.prepare(sql)
174222 isth.execute(field[0])
175-
223+
176224 newene = nil
177-
225+
178226 if 0 != isth.rows then
179-
227+
180228 isth.fetch do |irow|
181229 newene = irow[1]
182230 end
183231 end
184-
232+
185233 isth = @dbh.prepare("INSERT INTO denergy (mailadd, food, energy, adddate) values(?, ?, ?, ?)")
186234 isth.execute(mailfrom, field[0], newene, nowtime)
187235 isth.finish
188236 end
189-
237+
190238 elsif 2 == field.size then
191239 Debug.p field
192-
193- isth = @dbh.prepare("INSERT INTO denergy (mailadd, food, energy, adddate) values(?, ?, ?, ?)")
194- isth.execute(mailfrom, field[0], field[1], nowtime)
195- isth.finish
196-
197- isth = @dbh.prepare("INSERT INTO menergy (food, energy, adddate) values(?, ?, ?)")
198- isth.execute(field[0], field[1], nowtime)
199- isth.finish
200-
240+
241+ if in_ary(@@body_weight, field[0]) then
242+
243+ # 体重入力っぽい場合
244+ tsth = @dbh.prepare("SELECT mailadd, adddate FROM dweight WHERE mailadd = ? AND adddate = ?")
245+ tsth.execute(mailfrom, nowtime)
246+ if 0 != sth.rows then
247+ isth = @dbh.prepare("UPDATE FROM dweight SET mailadd = ?, weight = ?, adddate = ?")
248+ isth.execute(mailfrom, field[1], nowtime)
249+ isth.finish
250+ else
251+ isth = @dbh.prepare("INSERT INTO dweight (mailadd, weight, adddate) values(?, ?, ?)")
252+ isth.execute(mailfrom, field[1], nowtime)
253+ isth.finish
254+ end
255+ tsth.finish
256+
257+ elsif in_ary(@@body_fat, field[0]) then
258+ # 体脂肪入力っぽい場合
259+ tsth = @dbh.prepare("SELECT mailadd, adddate FROM dfat WHERE mailadd = ? AND adddate = ?")
260+ tsth.execute(mailfrom, nowtime)
261+ if 0 != sth.rows then
262+ isth = @dbh.prepare("UPDATE FROM dfat SET mailadd = ?, fat = ?, adddate = ?")
263+ isth.execute(mailfrom, field[1], nowtime)
264+ isth.finish
265+ else
266+ isth = @dbh.prepare("INSERT INTO dfat (mailadd, fat, adddate) values(?, ?, ?)")
267+ isth.execute(mailfrom, field[1], nowtime)
268+ isth.finish
269+ end
270+ tsth.finish
271+
272+ else
273+ # 入力項目がふたつの場合は、そのものとエネルギーを入力する
274+ isth = @dbh.prepare("INSERT INTO denergy (mailadd, food, energy, adddate) values(?, ?, ?, ?)")
275+ isth.execute(mailfrom, field[0], field[1], nowtime)
276+ isth.finish
277+
278+ # マスタにも登録
279+ isth = @dbh.prepare("INSERT INTO menergy (food, energy, adddate) values(?, ?, ?)")
280+ isth.execute(field[0], field[1], nowtime)
281+ isth.finish
282+ end
283+
201284 end
202285 }
203-
286+
287+ # 返信文を作成
204288 sql = "SELECT d.mailadd, d.food, d.energy, m.ave, d.adddate, s.ssum " + \
205289 " FROM denergy d LEFT JOIN " + \
206290 " (SELECT food, ROUND(AVG(energy), 0) as ave " + \
@@ -208,18 +292,18 @@
208292 " (SELECT DATE_FORMAT(adddate, '%Y-%m-%d') as fd, SUM(energy) as ssum FROM denergy WHERE mailadd = ? GROUP BY fd) s" + \
209293 " WHERE s.fd = DATE_FORMAT(d.adddate, '%Y-%m-%d') AND d.adddate > DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND d.mailadd = ?" + \
210294 " ORDER BY d.mailadd, d.adddate desc"
211-
295+
212296 isth = @dbh.prepare(sql)
213297 isth.execute(mailfrom, mailfrom)
214-
298+
215299 if 0 != isth.rows then
216-
300+
217301 detail = ""
218302 befdate = Time.local(1975, 1, 1, 0, 0, 0)
219303 daysum = 0
220-
304+
221305 isth.fetch do |irow|
222-
306+
223307 nfood = irow[1]
224308 nenergy = irow[2]
225309 nave = irow[3]
@@ -226,68 +310,96 @@
226310 ndate = irow[4]
227311 nsum = irow[5]
228312 rndate = Time.local(ndate.year, ndate.month, ndate.day, 0, 0, 0)
229-
313+
230314 if rndate.to_f != befdate.to_f then
231-
315+ # 日替わり表示
232316 detail.concat("\n" + rndate.month.to_s + "/" + rndate.day.to_s + "(" + @@wdays[rndate.wday] + ") 計" + nsum.to_s + "kcal\n")
233317 befdate = rndate
234-
318+
319+ w = false;
320+ kg = 0;
321+
322+ # 体重
323+ tsth = @dbh.prepare("SELECT weight FROM dweight WHERE mailadd = ? AND adddate = ?")
324+ tsth.execute(mailfrom, rndate)
325+ if 0 != sth.rows then
326+ kg = weight[0]
327+ tsth.fetch do |weight|
328+ detail.concat("体重 " + kg + "kg\n")
329+ end
330+ end
331+ tsth.finish
332+
333+ # 体脂肪
334+ tsth = @dbh.prepare("SELECT fat FROM dfat WHERE mailadd = ? AND adddate = ?")
335+ tsth.execute(mailfrom, rndate)
336+ if 0 != sth.rows then
337+ tsth.fetch do |fat|
338+ detail.concat("体脂肪率 " + fat[0] + "% ")
339+ if 0 != kg then
340+ detail.concat("(体脂肪 " + fat[0] * kg / 100 + "kg)")
341+ end
342+ detail.concat("\n")
343+ end
344+ end
345+ tsth.finish
346+
235347 end
236-
348+
237349 if nenergy then
238350 senergy = nenergy.to_s
239351 else
240352 senergy = "?"
241353 end
242-
354+
243355 if nave then
244356 save = nave.to_s
245357 else
246358 save = "?"
247359 end
248-
360+
249361 detail.concat(" " + nfood + " " + senergy + "kcal (" + save + ")\n")
250-
362+
251363 end
252-
364+
253365 rep = Kconv::kconv(fileread(@@repmail_memo), Kconv::UTF8)
254366 repsub = getfilesubject(rep)
255367 repbody = getfilebody(rep) + detail + Kconv::kconv(fileread(@@repmail_footer), Kconv::UTF8)
256-
368+
257369 Debug.p "filebody = '" + repbody + "'"
258-
370+
259371 MailControl.sendmail(smtpserver, mailfrom, smtpfrom, repsub, repbody)
260-
372+
261373 end
262-
374+
263375 isth.finish
264-
376+
265377 else
378+ # ユーザ登録
266379 isth = @dbh.prepare("INSERT INTO muser (mailadd, adddate) values(?, now())")
267380 isth.execute(mailfrom)
268381 isth.finish
269-
382+
270383 isth = @dbh.prepare("DELETE FROM muserprop WHERE mailadd = ?")
271384 isth.execute(mailfrom)
272385 isth.finish
273-
386+
274387 isth = @dbh.prepare("INSERT INTO muserprop (mailadd, porpname, propvaluestr, propvalueint) values(?, 'limitenergy', null, 2000)")
275388 isth.execute(mailfrom)
276389 isth.finish
277-
390+
278391 rep = fileread(@@repmail_first)
279392 repsub = getfilesubject(rep)
280393 repbody = getfilebody(rep) + fileread(@@repmail_footer)
281-
394+
282395 Debug.p "filebody = '" + repbody + "'"
283-
396+
284397 MailControl.sendmail(smtpserver, mailfrom, smtpfrom, repsub, repbody)
285-
398+
286399 end
287-
400+
288401 sth.finish
289-
402+
290403 end
291-
404+
292405 end
293-
Show on old repository browser