search

jkimdon / cohotikimeals

fresh start on meal integration with tiki
GNU General Public License v3.0
0 stars 0 forks source link

create summary of meal over/under for 2019 through Feb 2020 #142

Closed stonylohr closed 4 years ago

stonylohr commented 4 years ago

Requested by Sustenance.

stonylohr commented 4 years ago

--create summary of meal over/under for past year --cohomeals_meal, cohomeals_meal_participant, cohomeals_meal_guest, cohomeals_financial_log, cohomeals_food_expenditures --cohomeals_pantry_food, cohomeals_pantry_purchases,

desc cohomeals_pantry_food | Field | Type | Null | Key | Default | Extra | +---------------------------+-------------+------+-----+-------------------+-------+ | cal_food_id | int(11) | NO | PRI | 0 | | | cal_description | varchar(80) | NO | | | | | cal_category | varchar(80) | NO | | | | | cal_unit | varchar(25) | NO | | | | | cal_unit_price | int(11) | NO | | 0 | | | cal_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | cal_available_meals | int(11) | NO | | 0 | | | cal_available_individuals | int(11) | NO | | 0 | | | cal_flags | varchar(4) | NO | | | | | cal_notes | varchar(80) | NO | | | |

desc cohomeals_pantry_purchases | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+-------------------+-------+ | cal_log_id | int(11) | NO | PRI | 0 | | | cal_food_id | int(11) | NO | | 0 | | | cal_number_units | decimal(10,2) | NO | | 0.00 | | | cal_total_price | int(11) | NO | | 0 | | | cal_type | int(11) | NO | | 0 | | | cal_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | cal_purchase_date | timestamp | YES | | NULL | | | cal_meal_id | int(11) | YES | | NULL | |

select basics., (0.01 (basics.income - basics.expenditures - basics.pantry_expenses)) as net_profit from ( select m.cal_id, m.meal_title, m.cal_date, m.cal_time, h.cal_login chef, m.cal_base_price, m.paperwork_done, m.diners_charged, (select count() from cohomeals_meal_participant p where m.cal_id = p.cal_id and p.cal_type<>'H' and p.cal_type<>'C') diner_count, (select count() from cohomeals_meal_guest g where m.cal_id = g.cal_meal_id) guest_count, (select -sum(cal_amount) from cohomeals_financial_log fl where fl.cal_meal_id=m.cal_id) income, (select sum(cal_amount) from cohomeals_food_expenditures fe where fe.cal_meal_id=m.cal_id) expenditures, (select sum(cal_total_price) from cohomeals_pantry_purchases pp where pp.cal_meal_id=m.cal_id) pantry_expenses from cohomeals_meal m inner join cohomeals_meal_participant h on m.cal_id = h.cal_id and h.cal_type='H' where 20190000 <= m.cal_date && m.cal_date <= 20200223 and m.cal_cancelled <> 1 ) basics order by cal_date, cal_time ;

--see attachment for results coho_meal_profitloss_2020-02-23.txt