public function stats($userId, array $options)
{
$limit = 10;
$skipDates = false;
if (isset($options['limit'])) {
$limit = $options['limit'];
}
if (isset($options['skipDates'])) {
$skipDates = $options['skipDates'];
}
/*
Women: BMR = 655 + ( 9.6 x weight in kilos ) + ( 1.8 x height in cm ) - ( 4.7 x age in years )
Men: BMR = 66 + ( 13.7 x weight in kilos ) + ( 5 x height in cm ) - ( 6.8 x age in years )
*/
$parameters = array($userId, $limit);
$stats = array();
if (!$skipDates) {
$end_date_limit = sprintf("-%d day", $limit);
$end_date = date('Y-m-d', strtotime($end_date_limit, time()));
$date = date("Y-m-d");
while (strtotime($date) >= strtotime($end_date)) {
$stat = array();
$stat["date"] = $date;
$stat["weight"] = null;
$stat["cal_out"] = null;
$stat["cal_in"] = null;
$stat["gender"] = null;
$stat["bmr_m"] = null;
$stat["bmr_f"] = null;
$stats[$date] = $stat;
$date = date("Y-m-d", strtotime("-1 day", strtotime($date)));
}
}
// EXERCISE - WEIGHT
DB::setFetchMode(\PDO::FETCH_ASSOC);
$exercises = DB::select("\n SELECT\n DATE(w.date) as date,\n w.weight,\n SUM(((w.weight*3.5*2.20462262*et.met_rate)/200)*e.duration) as cal_out,\n lcase(p.gender) as gender,\n 66 + (13.7*w.weight) + (5 * p.height*100) - (6.8*((TO_DAYS(CURRENT_DATE()) - TO_DAYS(p.birth))/365.244)) as bmr_m,\n 665 + (9.6*w.weight) + (1.8 * p.height*100) - (4.7*((TO_DAYS(CURRENT_DATE()) - TO_DAYS(p.birth))/365.244)) as bmr_f\n FROM weights w\n LEFT JOIN activities e ON w.user_id = e.user_id AND DATE(e.activity_date) = DATE(w.date)\n LEFT JOIN activity_types et ON e.activity_type_id = et.id\n LEFT JOIN users p ON w.user_id = p.id\n WHERE\n w.user_id = ? AND\n w.date >= DATE_SUB(NOW(), INTERVAL ? DAY)\n GROUP BY DATE(w.date)\n ORDER BY DATE(w.date) DESC;\n ", $parameters);
DB::setFetchMode(\PDO::FETCH_CLASS);
foreach ($exercises as $exercise) {
$stats[$exercise['date']] = $exercise;
$stats[$exercise['date']]['cal_in'] = null;
}
// EATS
DB::setFetchMode(\PDO::FETCH_ASSOC);
$eats = DB::select("\n SELECT\n DATE(eatn.eaten) as `date`,\n SUM(IF(\n STRCMP(eatn.amount_type,'P'),\n ((eatn.amount)/f.`size`)*f.calories,\n ((eatn.amount*f.serving_size)/f.`size`)*f.calories\n )) as eatn_calories\n FROM eats eatn\n LEFT JOIN foods f ON eatn.food_id = f.id\n WHERE\n eatn.user_id = ? AND\n eatn.eaten >= DATE_SUB(NOW(), INTERVAL ? DAY)\n GROUP BY `date`\n ORDER BY `date` DESC\n ", $parameters);
DB::setFetchMode(\PDO::FETCH_CLASS);
foreach ($eats as $eat) {
if (isset($stats[$eat['date']])) {
$stats[$eat['date']]['cal_in'] = $eat['eatn_calories'];
}
}
return $stats;
}
public function getExcelData($weeknr, $providerId)
{
$data = [];
DB::setFetchMode(\PDO::FETCH_ASSOC);
$sql = 'SELECT allId, tblCompany.comName, tblCompany.comStreet, tblCompany.comNumber, tblCompany.comZipCode, tblCompany.comCity,
allOrderNumber, allWeekNumber FROM tblAllocatie';
$sql .= ' LEFT JOIN tblCompany on comId = allCompanyId';
$sql .= ' WHERE allWeekNumber = ' . $weeknr;
$sql .= ' AND allProviderId = ' . $providerId;
$results = DB::select($sql);
foreach ($results as $company) {
$sql = 'SELECT lnkProductId, lnkAmount FROM lnkAllocatiePivot';
$sql .= ' WHERE lnkAllocatieId = ' . $company['allId'];
$rows = DB::select($sql);
$company['rows'] = $rows;
array_push($data, $company);
}
DB::setFetchMode(\PDO::FETCH_CLASS);
return $data;
}
