1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
/*
Custom script to add next meal automatically. Invoke it thus:
.read sequence.sqlite3
This is something NUTsqlite can't do, complicated meal ordering with
automatic changes to personal options!
The basic idea here is 5 low-fat starch meals followed by 3 low-carb meals.
Within the low-fat meals, starch source is alternated. Modulo arithmetic
selects which meal and personal options are to be chosen by defining a little
view which can be repeatedly be selected from by each subsequent SQL
statement in the procedure.
*/
begin;
with newcm (meal_id) as (
with cm1 (base, meal) as (
with cm (base, meal) as (
select currentmeal / 100, currentmeal % 100 from options
)
select case when meal = 3 then date(substr(base, 1, 4) || '-' || substr(base, 5, 2) || '-' || substr(base, 7, 2), '+1 day') else substr(base, 1, 4) || '-' || substr(base, 5, 2) || '-' || substr(base, 7, 2) end, case when meal = 3 then 1 else meal + 1 end from cm
)
select cast( substr(base, 1, 4) || substr(base, 6, 2) || substr(base, 9, 2) as int) * 100 + meal from cm1
)
update options set currentmeal = (select meal_id from newcm);
drop view if exists z_mn;
create temp view z_mn as
with meal (m, mod2, mod8) as (
select options.currentmeal % 100, maxmeal % 2, maxmeal % 8 from options, am_analysis_header
)
select case when mod8 not in (1, 2, 3) then case when mod2 = 1 then case when m = 1 then 'R Breakfast' when m = 2 then 'R Dinner' else 'R Supper' end else case when m = 1 then 'P Breakfast' when m = 2 then 'P Dinner' else 'P Supper' end end else case when m = 1 then 'K Breakfast' when m = 2 then 'K Dinner' else 'K Supper' end end as mn, mod8 from meal;
insert into currentmeal select NDB_No, Gm_Wgt, NutrDesc from theusual where meal_name = (select mn from z_mn);
update nutr_def set nutopt = case when (select mod8 from z_mn) not in (1, 2, 3) then -1 else 0.0 end where nutrdesc = 'Total Fat';
update nutr_def set nutopt = case when (select mod8 from z_mn) not in (1, 2, 3) then 0.0 else -1 end where nutrdesc = 'Non-Fiber Carb';
update options set defanal_am = case when (select mod8 from z_mn) in (2, 5) then 1 else defanal_am + 1 end;
drop view z_mn;
commit;
|
