/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ArrayDataProvider
*/
public function search($params = [])
{
$caloriesQuery = RecipeRepository::getCaloriesQuery('p.recipe_id')->createCommand()->sql;
$proteinsQuery = RecipeRepository::getProteinsQuery('p.recipe_id')->createCommand()->sql;
$fatsQuery = RecipeRepository::getFatsQuery('p.recipe_id')->createCommand()->sql;
$carbohydratesQuery = RecipeRepository::getCarbohydratesQuery('p.recipe_id')->createCommand()->sql;
$query = IngredientEntity::find()->select(['(\'' . IngredientEntity::TYPE_COUNT . '\') AS type', 'p.id', 'p.name', 'dp.count', "({$caloriesQuery}) * p.weight * dp.count AS calories", "({$proteinsQuery}) * `p`.`weight` AS `protein`", "({$fatsQuery}) * `p`.`weight` AS `fat`", "({$carbohydratesQuery}) * `p`.`weight` AS `carbohydrate`"])->from(['dp' => Diary::diary2portionsTableName()])->leftJoin(['p' => Portion::tableName()], 'p.id = dp.portion_id')->where(['dp.diary_id' => $this->id])->orderBy('calories DESC');
$dataProvider = new ArrayDataProvider(['allModels' => $query->all(), 'sort' => ['attributes' => ['name', 'count', 'calories', 'protein', 'fat', 'carbohydrate'], 'defaultOrder' => ['name' => SORT_ASC]], 'pagination' => false]);
return $dataProvider;
}
/**
* @param integer $id
* @return Query
*/
public static function getPortionsWeightQuery($id)
{
return (new Query())->select('SUM(`dp`.`count`*`p`.`weight`) AS `weight`')->from(Diary::diary2portionsTableName() . ' `dp`')->leftJoin(Portion::tableName() . ' `p`', '`p`.`id` = `dp`.`portion_id`')->where("`dp`.`diary_id` = {$id}");
}
/**
* @return \yii\db\ActiveQuery
*/
public function getPortions()
{
return $this->hasMany(Portion::className(), ['id' => 'portion_id'])->viaTable(self::menu2PortionTableName(), ['menu_id' => 'id']);
}
/**
* @param integer|string $id
* @return Query
*/
public static function getPortionsCountQuery($id)
{
//portionsCount
return (new Query())->select('COUNT(*) AS `portionsCount`')->from(Portion::tableName() . ' `p`')->leftJoin(Recipe::tableName() . ' `r`', '`r`.`id` = `p`.`recipe_id`')->where("`r`.`category_id` = {$id}");
}
/**
* @return \yii\db\ActiveQuery
*/
public function getPortions()
{
return $this->hasMany(Portion::className(), ['id' => 'portion_id'])->viaTable('portion_recipes', ['recipe_id' => 'id']);
}
/**
* Finds the Portion model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return Portion the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Portion::findById($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
/**
* @param array|string|integer $params
* @return Portion
*/
public static function searchOne($params)
{
$where = is_array($params) ? $params : ['p.id' => $params];
return Portion::find()->select(['p.*', 'p.weight', 'categoryName' => 'c.name', 'recipeName' => 'r.name', 'calories' => 'SUM(`rp`.`weight`*`prod`.`calories`)/SUM(`rp`.`weight`)*`p`.`weight`', 'proteins' => 'SUM(`rp`.`weight`*`prod`.`protein`)/SUM(`rp`.`weight`)*`p`.`weight`', 'fats' => 'SUM(`rp`.`weight`*`prod`.`fat`)/SUM(`rp`.`weight`)*`p`.`weight`', 'carbohydrates' => 'SUM(`rp`.`weight`*`prod`.`carbohydrate`)/SUM(`rp`.`weight`)*`p`.`weight`'])->from(['p' => Portion::tableName()])->innerJoin(['r' => Recipe::tableName()], '`r`.`id` = `p`.`recipe_id`')->innerJoin(['rp' => Recipe::recipe2productsTableName()], '`rp`.`recipe_id` = `r`.`id`')->leftJoin(['c' => RecipeCategory::tableName()], '`c`.`id` = `r`.`category_id`')->leftJoin(['prod' => Product::tableName()], '`prod`.`id` = `rp`.`product_id`')->where($where)->one();
}