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; }