From a19ed6db506c67c555051ac2613f2ebf03d06ba3 Mon Sep 17 00:00:00 2001 From: Alexander Smirnov Date: Fri, 16 Feb 2024 13:10:14 +0300 Subject: [PATCH] replace pdo with ar in shipment/fields-data2 --- erp24/inc/functionsFiedlsData.php | 492 +++++++++++++++++++------- erp24/records/StoreOrderStatusLog.php | 52 +++ erp24/records/StoreProductsFact.php | 64 ++++ erp24/services/SupportService.php | 24 ++ erp24/views/shipment/fields-data2.php | 70 +++- 5 files changed, 562 insertions(+), 140 deletions(-) create mode 100644 erp24/records/StoreOrderStatusLog.php create mode 100644 erp24/records/StoreProductsFact.php create mode 100644 erp24/services/SupportService.php diff --git a/erp24/inc/functionsFiedlsData.php b/erp24/inc/functionsFiedlsData.php index bc3aef8..8073998 100644 --- a/erp24/inc/functionsFiedlsData.php +++ b/erp24/inc/functionsFiedlsData.php @@ -1,10 +1,34 @@ select(['id', 'name'])->where(['in', 'id', [7, 30, 17, 70, 71, 9, 51, 1, 10]])->all(), 'id', 'name'); +//$roles=$db::mapping("SELECT id, name FROM admin_group WHERE id=7 or id=30 or id=17 or id=70 or id=71 or id=9 or id=51 or id=1 or id=10"); $fields=array( "show"=>array( @@ -56,19 +81,27 @@ $fields=array( ) ); -$users_group=$db::mapping("SELECT id,name FROM admin_group order by name ASC"); +$users_group = ArrayHelper::map(AdminGroup::find()->select(['id', 'name'])->orderBy(['name' => SORT_ASC])->all(), 'id', 'name'); +//$users_group=$db::mapping("SELECT id,name FROM admin_group order by name ASC"); -$storesArray=$db::mapping("SELECT id,name FROM products_1c WHERE tip='city_store' $whereGuid AND view='1' order by name ASC"); +$storesArray = ArrayHelper::map(Products1c::find()->select(['id', 'name'])->where(['tip' => 'city_store', 'view' => 1]) + ->andWhere(['in', 'id', $_SESSION["store_arr_guid_dostup"]]) + ->orderBy(['name' => SORT_ASC])->all(), 'id', 'name'); +//$storesArray=$db::mapping("SELECT id,name FROM products_1c WHERE tip='city_store' $whereGuid AND view='1' order by name ASC"); if (empty($providers)) { - $providers = $db::mapping("SELECT id,name FROM shipment_providers "); + $providers = ArrayHelper::map(ShipmentProviders::find()->select(['id', 'name'])->all(), 'id', 'name'); +// $providers = $db::mapping("SELECT id,name FROM shipment_providers "); } -$storesArrayAll=$db::mapping("SELECT id,name FROM products_1c WHERE tip='city_store' AND view='1'"); +$storesArrayAll = ArrayHelper::map(Products1c::find()->select(['id', 'name']) + ->where(['tip' => 'city_store', 'view' => 1])->all(), 'id', 'name'); +//$storesArrayAll=$db::mapping("SELECT id,name FROM products_1c WHERE tip='city_store' AND view='1'"); -// статусы закупок - массивы с доступами к полям -$data=$db::getRows("SELECT * FROM store_orders_statuses"); +// статусы закупок - массивы с доступами к полям +$data = StoreOrdersStatuses::find()->asArray()->all(); +//$data=$db::getRows("SELECT * FROM store_orders_statuses"); foreach($data as $row) { $store_orders_statuses[$row["id"]]=$row["name"]; $store_orders_status_description[$row["id"]]=$row["description"]; @@ -82,7 +115,8 @@ foreach($data as $row) { // готовим массим со всеми данными по столбцам по каждому товару магазину и цвету Если NULL - то это сумма по магазинам и по цветам -$dataF=$db::getRows("SELECT * FROM store_orders_fields_data WHERE order_id=?",[$orderId]); +$dataF = StoreOrdersFieldsData::find()->where(['order_id' => $orderId])->asArray()->all(); +//$dataF=$db::getRows("SELECT * FROM store_orders_fields_data WHERE order_id=?",[$orderId]); foreach($dataF as $row0) { if (!empty($fieldsRows[$row0["field_name"]])) { //если тип поля текст то заменяем @@ -111,13 +145,15 @@ foreach($dataF as $row0) { // создаем массив со свойствами подсветки ячеек store_orders_fields_property -$data=$db::getRows("SELECT * FROM store_orders_fields_property WHERE 1 order by id ASC"); +$data = StoreOrdersFieldsProperty::find()->orderBy(['id' => SORT_ASC])->asArray()->all(); +//$data=$db::getRows("SELECT * FROM store_orders_fields_property WHERE 1 order by id ASC"); foreach($data as $row) { $fieldsPropertyArray[$row["field_id"]][]=$row; } // достаем поля в массив -$data=$db::getRows("SELECT * FROM store_orders_fields WHERE 1 order by position ASC"); +$data = StoreOrdersFields::find()->orderBy(['position' => SORT_ASC])->asArray()->all(); +//$data=$db::getRows("SELECT * FROM store_orders_fields WHERE 1 order by position ASC"); foreach($data as $row) { $fieldsRows[$row["name_eng"]]=$row; if (array_key_exists($row["id"], $fieldsPropertyArray)) { @@ -136,14 +172,15 @@ foreach($data as $row) { // данные по закупке - достаем параметры закупки - дата старта продаж - от нее считаем продажи списания и закупки прошлой недели -$OrderData=$db::getRow("SELECT name,id,providers_arr, status, DATE_FORMAT(date_start, '%Y-%m-%d') as date_start, DATE_FORMAT(date_add, '%Y-%m-%d') as date_add, -DATE_FORMAT(division_date, '%Y-%m-%d') as division_date, UNIX_TIMESTAMP(date_update) as date_update, parent_id FROM store_orders WHERE id=?",[$orderId]); +$OrderData = SupportService::storeOrdersSelect001($orderId); +//$OrderData=$db::getRow("SELECT name,id,providers_arr, status, DATE_FORMAT(date_start, '%Y-%m-%d') as date_start, DATE_FORMAT(date_add, '%Y-%m-%d') as date_add, +//DATE_FORMAT(division_date, '%Y-%m-%d') as division_date, UNIX_TIMESTAMP(date_update) as date_update, parent_id FROM store_orders WHERE id=?",[$orderId]); $providersIdInThisOrder=explode(",",$OrderData["providers_arr"]); $status_order_id=$OrderData["status"]; $date_start_sale=$OrderData["date_start"]; $date_update=$OrderData["date_update"]; $date_start_division=$OrderData["division_date"]; -$order_date_add=$OrderData["date_add"]; +$order_date_add=$OrderData["date_add"]; if (empty($OrderData["parent_id"])) { //если это материнская - главная закупка @@ -151,7 +188,9 @@ if (empty($OrderData["parent_id"])) { //если это материнская - } else { // иначе идем к родителю и узнаем дату матери к которой будем привязываться if (!empty($row["parent_id"])) { - $date_start=$db::getValue("SELECT DATE_FORMAT(date_start, '%Y-%m-%d') as date_start FROM store_orders WHERE id=?",[$row["parent_id"]]); + $storeOrders = StoreOrders::find()->select(["DATE_FORMAT(date_start, '%Y-%m-%d') as date_start"])->where(['id' => $row["parent_id"]])->asArray()->one(); + $date_start = $storeOrders['date_start']; +// $date_start=$db::getValue("SELECT DATE_FORMAT(date_start, '%Y-%m-%d') as date_start FROM store_orders WHERE id=?",[$row["parent_id"]]); } } @@ -204,16 +243,19 @@ $FiledsData[$row["field_name"]][$row["product_id"]][$storeId][$color]=$v; // если не указан магазин или надо просуммировать для какого-то поля -$dataF=$db::getRows("SELECT field_name,product_id,sum(value) as sum FROM store_orders_fields_data WHERE order_id=? AND -store_id IS NULL AND color IS NULL group by field_name,product_id",[$orderId]); +$dataF = StoreOrdersFieldsData::find()->select(['field_name', 'product_id', 'sum(value) as sum'])->where(['order_id' => $orderId]) + ->andWhere(['IS', 'store_id', new Expression('null')])->andWhere(['IS', 'color', new Expression('null')])->groupBy(['field_name', 'product_id'])->asArray()->all(); +//$dataF=$db::getRows("SELECT field_name,product_id,sum(value) as sum FROM store_orders_fields_data WHERE order_id=? AND +//store_id IS NULL AND color IS NULL group by field_name,product_id",[$orderId]); foreach($dataF as $row) { $FiledsDataSumm[$row["field_name"]][$row["product_id"]] = $row["sum"]; } - -$dataF=$db::getRows("SELECT field_name,product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND -store_id IS NOT NULL group by field_name,product_id, color",[$orderId]); +$dataF = StoreOrdersFieldsData::find()->select(['field_name', 'product_id', 'sum(value) as sum', 'color'])->where(['order_id' => $orderId]) + ->andWhere(['IS NOT', 'store_id', new Expression('null')])->groupBy(['field_name', 'product_id', 'color'])->asArray()->all(); +//$dataF=$db::getRows("SELECT field_name,product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND +//store_id IS NOT NULL group by field_name,product_id, color",[$orderId]); foreach($dataF as $row) { $FiledsDataSummStats[$row["field_name"]][$row["product_id"]][$row["color"]] = $row["sum"]; } @@ -240,9 +282,11 @@ $products=['product_id'=>"name"]; // формируем массив с товарами и часть условия для запросов $whereInProductsId - вхождение ID продуктов if (empty($whereInProductsId)) { $whereInProductsId=" in ("; - $z="SELECT p.id, p.name, o.provider_id, o.price_zakup, p.parent_id FROM products_1c_options as o, products_1c as p - WHERE o.provider_id in($whereProvidersId) AND p.id=o.id order by o.provider_id ASC, p.name ASC"; - $data2=$db::getRows($z); +// $z="SELECT p.id, p.name, o.provider_id, o.price_zakup, p.parent_id FROM products_1c_options as o, products_1c as p +// WHERE o.provider_id in($whereProvidersId) AND p.id=o.id order by o.provider_id ASC, p.name ASC"; +// $data2=$db::getRows($z); + $data2 = SupportService::products1cOptionsProducts1cSelect001($providersIdInThisOrder); + $whereInProductsIdArr = ArrayHelper::getColumn($data2, 'id'); $k=0; foreach($data2 as $row) { if ($k!=0) { @@ -263,16 +307,21 @@ if (empty($whereInProductsId)) { //глобальный массив с переменными - только по определнным товарам - лишнего не берем -$da=$db::getRows("SELECT id,min_lot,min_order FROM products_1c_options WHERE id $whereInProductsId"); +$da = Products1cOptions::find()->select(['id', 'min_lot', 'min_order'])->where(['in', 'id', $whereInProductsIdArr])->asArray()->all(); +//$da=$db::getRows("SELECT id,min_lot,min_order FROM products_1c_options WHERE id $whereInProductsId"); foreach($da as $r) { $FiledsData["min_order"][$r["id"]]=$r["min_order"]; $FiledsData["min_lot"][$r["id"]]=$r["min_lot"]; } - -$data2=$db::getRows("SELECT p.id, p.name, o.provider_id, o.colors FROM products_1c_options as o, products_1c as p -WHERE p.id $whereInProductsId AND p.id=o.id order by o.provider_id ASC, p.name ASC"); +$data2 = Products1cOptions::find()->alias('o')->select(['p.id', 'p.name', 'o.provider_id', 'o.colors']) + ->innerJoin(['products_1c as p', 'p.id = o.id']) + ->where(['in', 'p.id', $whereInProductsIdArr]) + ->orderBy(['o.provider_id' => SORT_ASC, 'p.name' => SORT_ASC]) + ->asArray()->all(); +//$data2=$db::getRows("SELECT p.id, p.name, o.provider_id, o.colors FROM products_1c_options as o, products_1c as p +//WHERE p.id $whereInProductsId AND p.id=o.id order by o.provider_id ASC, p.name ASC"); $k=0; foreach($data2 as $row) { $providers_products[$row["provider_id"]][]=$row["id"]; @@ -323,8 +372,12 @@ if (!empty($_GET["update1113"])) { //$db::sql("DELETE FROM store_orders_fields_data WHERE field_name='$fieldName' AND order_id='$orderId'"); $pole=trim($fieldsRows[$fieldName]["func_content"]); $data_up=array(); - $dataF=$db::getRows("SELECT product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND - store_id!='' AND store_id!='NULL' AND field_name='$pole' group by product_id, color",[$orderId]); + $dataF = StoreOrdersFieldsData::find()->select(['product_id', 'sum(value) as sum', 'color'])->where(['order_id' => $orderId]) + ->andWhere(['NOT IN', 'store_id', ['', 'NULL']])->andWhere(['field_name' => $pole]) + ->groupBy(['product_id', 'color']) + ->asArray()->all(); +// $dataF=$db::getRows("SELECT product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND +// store_id!='' AND store_id!='NULL' AND field_name='$pole' group by product_id, color",[$orderId]); foreach($dataF as $row) { if (empty($row["color"])) @@ -343,8 +396,11 @@ if (!empty($_GET["update1113"])) { ////// - $data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values - FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); + $data = StoreOrdersFields::find()->select(['id,name_eng as field_name', 'position', 'field_type', 'description', 'sql_table_values']) + ->orderBy(['position' => SORT_ASC, 'id' => SORT_ASC]) + ->asArray()->all(); +// $data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values +// FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); $html .=""; foreach($data as $row) { @@ -490,7 +546,8 @@ if (!empty($_GET["update1113"])) { } - $db::sql("UPDATE store_orders SET date_update=NOW() WHERE id='$orderId'"); + StoreOrders::updateAll(['date_update' => new Expression('NOW()')], ['id' => $orderId]); +// $db::sql("UPDATE store_orders SET date_update=NOW() WHERE id='$orderId'"); if (!empty($_REQUEST["html_print"])) echo $html; @@ -688,7 +745,8 @@ if (0 and $date_start_sale!='000-00-00') { if (!empty($_GET["insert"])) { // массив соответствия ID магазинов и GUID - $data=$db::getRows("SELECT entity_id, export_val FROM export_import_table WHERE entity='city_store' AND export_id='1'"); + $data = ExportImportTable::find()->select(['entity_id', 'export_val'])->where(['entity' => 'city_store', 'export_id' => 1])->asArray()->all(); +// $data=$db::getRows("SELECT entity_id, export_val FROM export_import_table WHERE entity='city_store' AND export_id='1'"); foreach($data as $row) { $export[$row["entity_id"]]=$row["export_val"]; $export_revers[$row["export_val"]]=$row["entity_id"]; @@ -725,7 +783,8 @@ if (!empty($_GET["insert"])) { } - $db::sql("DELETE FROM store_orders_fields_data WHERE (value='0.000' or value='0.00') AND value_text=''",[$orderId]); + StoreOrdersFieldsData::deleteAll(['and', ['or', ['value' => '0.000'], ['value' => '0.00']], ['value_text' => '']]); // ??? order id +// $db::sql("DELETE FROM store_orders_fields_data WHERE (value='0.000' or value='0.00') AND value_text=''",[$orderId]); } @@ -891,14 +950,19 @@ function showOrderStoresPanel() { //показываем магазины если статус закупки подразумевает разбивку данных по магазинам if (true) {//if ($statuses_stores_show[$status_order_id]) { - $stores_arr=$db::mapping("SELECT name,id FROM products_1c WHERE tip='city_store' AND view='1' order by name ASC"); + $stores_arr = ArrayHelper::map(Products1c::find()->where(['tip' => 'city_store', 'view' => 1])->orderBy(['name' => SORT_ASC])->all(), 'id', 'name'); +// $stores_arr=$db::mapping("SELECT name,id FROM products_1c WHERE tip='city_store' AND view='1' order by name ASC"); $stores_cnt=count($stores_arr); - $vnesli=$db::getValue("SELECT count(*) as cnt FROM store_order_status - WHERE order_id=? AND status_id=? AND status=1 group BY order_id",[$orderId,$status_order_id]); + $vnesli = StoreOrderStatus::find()->where(['order_id' => $orderId, 'status_id' => $status_order_id, 'status' => 1]) + ->groupBy(['order_id', 'store_id', 'status_id'])->count(); +// $vnesli=$db::getValue("SELECT count(*) as cnt FROM store_order_status +// WHERE order_id=? AND status_id=? AND status=1 group BY order_id",[$orderId,$status_order_id]); $store_order_status=array(); - $data2=$db::getRows("SELECT store_id,status FROM store_order_status WHERE order_id=? AND status_id=? ",[$orderId,$status_order_id]); + $data2 = StoreOrderStatus::find()->select(['store_id', 'status'])->where(['order_id' => $orderId, 'status_id' => $status_order_id]) + ->asArray()->all(); +// $data2=$db::getRows("SELECT store_id,status FROM store_order_status WHERE order_id=? AND status_id=? ",[$orderId,$status_order_id]); foreach($data2 as $row2) $store_order_status_value[$row2["store_id"]]=$row2["status"]; @@ -942,11 +1006,24 @@ function showOrderStoresPanel() { if (!empty($store_id)) { if (!empty($_GET["status"]) and !empty($store_id) and !empty($orderId)) { - $db::sql("INSERT IGNORE INTO store_order_status SET order_id=?, store_id=?, status_id=?, status=?, date=NOW(), admin_id=? ", - [$orderId,$store_id,$status_order_id,1,$_SESSION["admin_id"]]); + $storeOrderStatus = StoreOrderStatus::find()->where(['order_id' => $orderId, 'store_id' => $store_Id, 'status_id' => $status_order_id])->one(); + if (!$storeOrderStatus) { + $storeOrderStatus = new StoreOrderStatus; + $storeOrderStatus->order_id = $orderId; + $storeOrderStatus->store_id = $store_id; + $storeOrderStatus->status_id = $status_order_id; + $storeOrderStatus->status = 1; + $storeOrderStatus->date = date('Y-m-d H:i:s'); + $storeOrderStatus->admin_id = $_SESSION["admin_id"]; + $storeOrderStatus->save(); + } +// $db::sql("INSERT IGNORE INTO store_order_status SET order_id=?, store_id=?, status_id=?, status=?, date=NOW(), admin_id=? ", +// [$orderId,$store_id,$status_order_id,1,$_SESSION["admin_id"]]); } - $store_order_status_value=$db::getValue("SELECT status FROM store_order_status WHERE order_id=? AND store_id=? AND status_id=?",[$orderId,$store_id,$status_order_id]); + $sosStatus = StoreOrderStatus::find()->select(['status'])->where(['order_id' => $orderId, 'store_id' => $store_id, 'status_id' => $status_order_id])->asArray()->one(); + $store_order_status_value = $sosStatus['status']; +// $store_order_status_value=$db::getValue("SELECT status FROM store_order_status WHERE order_id=? AND store_id=? AND status_id=?",[$orderId,$store_id,$status_order_id]); $status_store=" ".$storesArray[$store_id].":"; @@ -1465,7 +1542,8 @@ insert_store_orders_fields($data_uper,"division_auto"); // вторая формула автозаказа if ($type=="auto_purchase_formula") { if (empty($storesAll)) { - $storesAll = $db::mapping("SELECT id, name FROM products_1c WHERE tip='city_store'"); + $storesAll = ArrayHelper::map(Products1c::find()->select(['id', 'name'])->where(['tip' => 'city_store'])->all(), 'id', 'name'); +// $storesAll = $db::mapping("SELECT id, name FROM products_1c WHERE tip='city_store'"); } $value_arr=function_auto_purchase_formula($param); @@ -1940,10 +2018,27 @@ function insert_store_orders_fields($massivSQL,$field_name,$massivTitle=[], $pri if (($type!="string") or ($type=="string" and $value_text!="")) { $title=$massivTitle[$productId][$storeIdData][$colorData]; - - $db::sql("INSERT IGNORE INTO store_orders_fields_data (product_id, order_id, store_id, field_name, field_id, value, value_text, color,hand,date_update, title) - VALUES (?,?,?,?,?,?,?,?,'-1',NOW(),?) ON DUPLICATE KEY UPDATE value=?, hand=0, value_text=?, date_update=NOW(), title=?", - [$productId, $orderId, $storeId, $field_name, $field_id, $value, $value_text, $color, $title, $value, $value_text , $title]); + $storeOrdersFieldsData = StoreOrdersFieldsData::find()->where(['order_id' => $orderId, 'product_id' => $productId, 'store_id' => $storeId, + 'field_id' => $field_id, 'field_name' => $field_name, 'color' => $color])->one(); + if (!$storeOrdersFieldsData) { + $storeOrdersFieldsData = new StoreOrdersFieldsData; + $storeOrdersFieldsData->order_id = $orderId; + $storeOrdersFieldsData->product_id = $productId; + $storeOrdersFieldsData->store_id = $storeId; + $storeOrdersFieldsData->field_id = $field_id; + $storeOrdersFieldsData->field_name = $field_name; + $storeOrdersFieldsData->color = $color; + $storeOrdersFieldsData->hand = -1; + } else { + $storeOrdersFieldsData->hand = 0; + } + $storeOrdersFieldsData->value = $value; + $storeOrdersFieldsData->value_text = $value_text; + $storeOrdersFieldsData->date_update = date('Y-m-d H:i:s'); + $storeOrdersFieldsData->title = $title; +// $db::sql("INSERT IGNORE INTO store_orders_fields_data (product_id, order_id, store_id, field_name, field_id, value, value_text, color,hand,date_update, title) +// VALUES (?,?,?,?,?,?,?,?,'-1',NOW(),?) ON DUPLICATE KEY UPDATE value=?, hand=0, value_text=?, date_update=NOW(), title=?", +// [$productId, $orderId, $storeId, $field_name, $field_id, $value, $value_text, $color, $title, $value, $value_text , $title]); } @@ -2006,10 +2101,11 @@ function insert_store_orders_fields($massivSQL,$field_name,$massivTitle=[], $pri // работает на перенос данных из старого формата в новый обнуляя введенную информацию! аккуратно с применением. применяем 1 раз перед переходом на новые талицы // собираем массив с данными по названию поля - входной параметр Имя поля function getArrayByFiledName($field_name,$date1,$date2) { - global $db,$providers,$products,$ProductsOptions,$whereInProductsId,$date_start_sale,$order_date_add,$date_start_division, $printArray,$orderId,$fieldsRows; + global $db,$providers,$products,$ProductsOptions,$whereInProductsId,$whereInProductsIdArr, $date_start_sale,$order_date_add,$date_start_division, $printArray,$orderId,$fieldsRows; $products_class=[]; - $data=$db::getRows("SELECT * FROM products_class WHERE tip in ('potted','wrap')"); + $data = ProductsClass::find()->where(['in', 'tip', ['potted', 'wrap']])->asArray()->all(); +// $data=$db::getRows("SELECT * FROM products_class WHERE tip in ('potted','wrap')"); foreach($data as $row) { $products_class[$row["category_id"]] = $row["tip"]; } @@ -2022,7 +2118,8 @@ function getArrayByFiledName($field_name,$date1,$date2) { // сколько на складе по 1с if ($field_name=="quantity_storage") { - $data2=$db::getRows("SELECT * FROM balances WHERE quantity>0 AND product_id $whereInProductsId "); + $data2 = Balances::find()->where(['>', 'quantity', 0])->andWhere(['in', 'product_id', $whereInProductsIdArr])->asArray()->all(); +// $data2=$db::getRows("SELECT * FROM balances WHERE quantity>0 AND product_id $whereInProductsId "); foreach($data2 as $row2) { $data_values[$row2["product_id"]][$row2["store_id"]][0]+=$row2["quantity"]; $data_values[$row2["product_id"]][0][0]+=$row2["quantity"]; @@ -2031,7 +2128,9 @@ function getArrayByFiledName($field_name,$date1,$date2) { //полнограмма if ($field_name=="quantity") { - $store_planogram=$db::getRows("SELECT product_id,quantity, store_id,color FROM store_planogram WHERE quantity>0 AND product_id $whereInProductsId"); + $store_planogram = StorePlanogram::find()->select(['product_id', 'quantity', 'store_id', 'color'])->where(['>', 'quantity', 0]) + ->andWhere(['in', 'product_id', $whereInProductsIdArr])->asArray()->all(); +// $store_planogram=$db::getRows("SELECT product_id,quantity, store_id,color FROM store_planogram WHERE quantity>0 AND product_id $whereInProductsId"); foreach($store_planogram as $row) { // echo"
$field_name {$row["product_id"]} = {$row["store_id"]} g ={$row["quantity"]}"; @@ -2045,10 +2144,16 @@ function getArrayByFiledName($field_name,$date1,$date2) { //плнограмма факт quantity_fact if ($field_name=="quantity_fact") { - $db::sql("DELETE FROM store_orders_fields_data WHERE order_id='$orderId' AND field_name='quantity_fact'"); - - $date_id=$db::getValue("SELECT date_id FROM store_products_fact order by date_id DESC LIMIT 1"); - $store_products_fact=$db::getRows("SELECT product_id,quantity, store_id,color FROM store_products_fact WHERE quantity>0 AND date_id=? AND product_id $whereInProductsId",[$date_id]); + StoreOrdersFieldsData::deleteAll(['order_id' => $orderId, 'field_name' => 'quantity_fact']); +// $db::sql("DELETE FROM store_orders_fields_data WHERE order_id='$orderId' AND field_name='quantity_fact'"); + + $spf = StoreProductsFact::find()->select(['date_id'])->orderBy(['date_id' => SORT_DESC])->asArray()->one(); + $date_id = $spf['date_id']; +// $date_id=$db::getValue("SELECT date_id FROM store_products_fact order by date_id DESC LIMIT 1"); + $store_products_fact = StoreProductsFact::find()->select(['product_id', 'quantity', 'store_id', 'color']) + ->where(['>', 'quantity', 0])->andWhere(['date_id' => $date_id]) + ->andWhere(['in', 'product_id', $whereInProductsIdArr])->asArray()->all(); +// $store_products_fact=$db::getRows("SELECT product_id,quantity, store_id,color FROM store_products_fact WHERE quantity>0 AND date_id=? AND product_id $whereInProductsId",[$date_id]); foreach($store_products_fact as $row) { // echo"
$field_name {$row["product_id"]} = {$row["store_id"]} quantity ={$row["quantity"]}"; @@ -2110,10 +2215,18 @@ function getArrayByFiledName($field_name,$date1,$date2) { foreach($pArray as $prodId) {if ($j>0) $whereInProductsId_in .=","; $whereInProductsId_in .="'$prodId'"; $j++;} if (!empty($whereInProductsId_in)) { - $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id,sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt - FROM sales as s, sales_products as i - WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) - AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval $day_sales day group BY i.product_id, s.store_id, s.operation "); + $data2 = Sales::find()->alias('s')->select(["i.product_id", "s.store_id_1c as store_id", + "sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt", "sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt"]) + ->innerJoin('sales_products as i', 's.id = i.check_id') + ->where(['in', 'i.product_id', $pArray]) + ->andWhere(['<=', 's.date', "$date_start_sale 00:00:00"]) + ->andWhere(['>=', 's.date', new Expression($date_start_sale . ' - INTERVAL ' . $day_sales . ' day')]) + ->groupBy(['i.product_id', 's.store_id', 's.operation']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id,sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt +// FROM sales as s, sales_products as i +// WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) +// AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval $day_sales day group BY i.product_id, s.store_id, s.operation "); foreach($data2 as $row2) { $cnt=$row2["sale_cnt"]-$row2["vozvr_cnt"]; $cnt=(int)$cnt; @@ -2130,10 +2243,18 @@ function getArrayByFiledName($field_name,$date1,$date2) { //продажи количество штук привязанные к дате старта продаж if ($field_name=="sales_cnt111") { - $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id,sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt - FROM sales as s, sales_products as i - WHERE s.id=i.check_id AND i.product_id $whereInProductsId - AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval 7 day group BY i.product_id, s.store_id, s.operation "); + $data2 = Sales::find()->alias('s')->select(["i.product_id", "s.store_id_1c as store_id", + "sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt", "sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt"]) + ->innerJoin('sales_products as i', 's.id = i.check_id') + ->where(['in', 'i.product_id', $pArray]) + ->andWhere(['<=', 's.date', "$date_start_sale 00:00:00"]) + ->andWhere(['>=', 's.date', new Expression($date_start_sale . ' - INTERVAL 7 day')]) + ->groupBy(['i.product_id', 's.store_id', 's.operation']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id,sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt +// FROM sales as s, sales_products as i +// WHERE s.id=i.check_id AND i.product_id $whereInProductsId +// AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval 7 day group BY i.product_id, s.store_id, s.operation "); foreach($data2 as $row2) { $cnt=$row2["sale_cnt"]-$row2["vozvr_cnt"]; $cnt=(int)$cnt; @@ -2161,9 +2282,17 @@ function getArrayByFiledName($field_name,$date1,$date2) { if (!empty($whereInProductsId_in)) { - $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id, sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt - FROM sales as s, sales_products as i - WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_division 00:00:00' AND s.date>='$date_start_division' -interval $day_sales day group BY i.product_id, s.store_id, s.operation "); + $data2 = Sales::find()->alias('s')->select(["i.product_id", "s.store_id_1c as store_id", + "sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt", "sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt"]) + ->innerJoin('sales_products as i', 's.id = i.check_id') + ->where(['in', 'i.product_id', $pArray]) + ->andWhere(['<=', 's.date', "$date_start_sale 00:00:00"]) + ->andWhere(['>=', 's.date', new Expression($date_start_sale . ' - INTERVAL ' . $day_sales . ' day')]) + ->groupBy(['i.product_id', 's.store_id', 's.operation']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT i.product_id, s.store_id_1c as store_id, sum(if (s.operation='Продажа',i.quantity,0)) as sale_cnt, sum(if (s.operation='Возврат',i.quantity,0)) as vozvr_cnt +// FROM sales as s, sales_products as i +// WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_division 00:00:00' AND s.date>='$date_start_division' -interval $day_sales day group BY i.product_id, s.store_id, s.operation "); foreach($data2 as $row2) { $cnt=$row2["sale_cnt"]-$row2["vozvr_cnt"]; $cnt=(int)$cnt; @@ -2195,9 +2324,17 @@ function getArrayByFiledName($field_name,$date1,$date2) { foreach($pArray as $prodId) {if ($j>0) $whereInProductsId_in .=","; $whereInProductsId_in .="'$prodId'"; $j++;} if (!empty($whereInProductsId_in)) { - $data2=$db::getRows("SELECT i.product_id, sum(if (s.operation='Продажа',i.summ,0)) as sale, s.store_id_1c as store_id, sum(if (s.operation='Возврат', i.summ ,0)) as vozvr - FROM sales as s, sales_products as i - WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_division 00:00:00' AND s.date>='$date_start_division' -interval $day_sales day group BY i.product_id, s.store_id, s.operation"); + $data2 = Sales::find()->alias('s')->select(["i.product_id", "s.store_id_1c as store_id", + "sum(if (s.operation='Продажа',i.summ,0)) as sale", "sum(if (s.operation='Возврат',i.summ,0)) as vozvr"]) + ->innerJoin('sales_products as i', 's.id = i.check_id') + ->where(['in', 'i.product_id', $pArray]) + ->andWhere(['<=', 's.date', "$date_start_division 00:00:00"]) + ->andWhere(['>=', 's.date', new Expression($date_start_division . ' - INTERVAL ' . $day_sales . ' day')]) + ->groupBy(['i.product_id', 's.store_id', 's.operation']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT i.product_id, sum(if (s.operation='Продажа',i.summ,0)) as sale, s.store_id_1c as store_id, sum(if (s.operation='Возврат', i.summ ,0)) as vozvr +// FROM sales as s, sales_products as i +// WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_division 00:00:00' AND s.date>='$date_start_division' -interval $day_sales day group BY i.product_id, s.store_id, s.operation"); foreach($data2 as $row2) { $sale=$row2["sale"]-$row2["vozvr"]; $sale=(int)$sale; @@ -2224,9 +2361,17 @@ function getArrayByFiledName($field_name,$date1,$date2) { foreach($pArray as $prodId) {if ($j>0) $whereInProductsId_in .=","; $whereInProductsId_in .="'$prodId'"; $j++;} if (!empty($whereInProductsId_in)) { - $data2=$db::getRows("SELECT i.product_id,sum(if (s.operation='Продажа',i.summ,0)) as sale, s.store_id_1c as store_id, sum(if (s.operation='Возврат', i.summ ,0)) as vozvr - FROM sales as s, sales_products as i - WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval $day_sales day group BY i.product_id, s.store_id_1c, s.operation"); + $data2 = Sales::find()->alias('s')->select(["i.product_id", "s.store_id_1c as store_id", + "sum(if (s.operation='Продажа',i.summ,0)) as sale", "sum(if (s.operation='Возврат',i.summ,0)) as vozvr"]) + ->innerJoin('sales_products as i', 's.id = i.check_id') + ->where(['in', 'i.product_id', $pArray]) + ->andWhere(['<=', 's.date', "$date_start_sale 00:00:00"]) + ->andWhere(['>=', 's.date', new Expression($date_start_sale . ' - INTERVAL ' . $day_sales . ' day')]) + ->groupBy(['i.product_id', 's.store_id', 's.operation']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT i.product_id,sum(if (s.operation='Продажа',i.summ,0)) as sale, s.store_id_1c as store_id, sum(if (s.operation='Возврат', i.summ ,0)) as vozvr +// FROM sales as s, sales_products as i +// WHERE s.id=i.check_id AND i.product_id in($whereInProductsId_in) AND s.date<='$date_start_sale 00:00:00' AND s.date>='$date_start_sale' -interval $day_sales day group BY i.product_id, s.store_id_1c, s.operation"); foreach($data2 as $row2) { $sale=$row2["sale"]-$row2["vozvr"]; $sale=(int)$sale; @@ -2266,12 +2411,20 @@ function getArrayByFiledName($field_name,$date1,$date2) { if ($periodDaysStats==7) $periodDaysStats_where=14; else $periodDaysStats_where=$periodDaysStats; - $data2=$db::getRows("SELECT p.product_id,sum(p.quantity) as cnt, w.store_id - FROM write_offs as w, write_offs_products as p - WHERE p.write_offs_id=w.id AND w.type='Брак' AND - - w.date<='$date_start_sale 00:00:00' AND w.date>='$date_start_sale' -interval $periodDaysStats_where day - AND p.product_id in($whereInProductsId_in) group BY p.product_id,w.store_id"); + $data2 = WriteOffs::find()->alias('w')->select(["p.product_id", "sum(p.quantity) as cnt", "w.store_id"]) + ->innerJoin('write_offs_products as p', 'p.write_offs_id = w.id') + ->where(['w.type' => 'Брак']) + ->andWhere(['in', 'p.product_id', $pArray]) + ->andWhere(['<=', 'w.date', "$date_start_sale 00:00:00"]) + ->andWhere(['>=', 'w.date', new Expression($date_start_sale . ' - INTERVAL ' . $periodDaysStats_where . ' day')]) + ->groupBy(['p.product_id', 'w.store_id']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT p.product_id,sum(p.quantity) as cnt, w.store_id +// FROM write_offs as w, write_offs_products as p +// WHERE p.write_offs_id=w.id AND w.type='Брак' AND +// +// w.date<='$date_start_sale 00:00:00' AND w.date>='$date_start_sale' -interval $periodDaysStats_where day +// AND p.product_id in($whereInProductsId_in) group BY p.product_id,w.store_id"); echo "
SELECT p.product_id,sum(p.quantity) as cnt, w.store_id @@ -2322,9 +2475,17 @@ function getArrayByFiledName($field_name,$date1,$date2) { } if (!empty($whereInProductsId_in)) { - $data2=$db::getRows("SELECT p.product_id,sum(p.quantity) as cnt, w.store_id - FROM write_offs as w, write_offs_products as p - WHERE p.write_offs_id=w.id AND w.type='Брак' AND p.product_id in($whereInProductsId_in) AND w.date<='$date_start_division 00:00:00' AND w.date>='$date_start_division' -interval $periodDaysStats day group BY p.product_id,w.store_id"); + $data2 = WriteOffs::find()->alias('w')->select(["p.product_id", "sum(p.quantity) as cnt", "w.store_id"]) + ->innerJoin('write_offs_products as p', 'p.write_offs_id = w.id') + ->where(['w.type' => 'Брак']) + ->andWhere(['in', 'p.product_id', $pArray]) + ->andWhere(['<=', 'w.date', "$date_start_division 00:00:00"]) + ->andWhere(['>=', 'w.date', new Expression($date_start_division . ' - INTERVAL ' . $periodDaysStats . ' day')]) + ->groupBy(['p.product_id', 'w.store_id']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT p.product_id,sum(p.quantity) as cnt, w.store_id +// FROM write_offs as w, write_offs_products as p +// WHERE p.write_offs_id=w.id AND w.type='Брак' AND p.product_id in($whereInProductsId_in) AND w.date<='$date_start_division 00:00:00' AND w.date>='$date_start_division' -interval $periodDaysStats day group BY p.product_id,w.store_id"); $valAll=0; foreach ($data2 as $row2) { @@ -2378,15 +2539,25 @@ function getArrayByFiledName($field_name,$date1,$date2) { $date_start_sale2=date("Y-m-d"); - $data2=$db::getRows("SELECT p.product_id,sum(c.quantity) as cnt, w.store_id, c.comment_id - FROM write_offs as w, write_offs_products as p, write_offs_comments as c - WHERE p.write_offs_id=w.id AND w.type='Брак' AND c.write_offs_id=p.write_offs_id AND p.product_id=c.product_id AND c.quantity>0 - - AND p.product_id in($whereInProductsId_in) - - AND w.date<='$date_start_sale2 00:00:00' AND w.date>='$date_start_sale2' -interval $periodDaysStats_where day - - group BY p.product_id,w.store_id, c.comment_id"); + $data2 = WriteOffs::find()->alias('w')->select(["p.product_id", "sum(c.quantity) as cnt", "w.store_id", "c.comment_id"]) + ->innerJoin('write_offs_products as p', 'p.write_offs_id = w.id') + ->innerJoin('write_offs_comments as c', 'c.write_offs_id = p.write_offs_id AND p.product_id = c.product_id') + ->where(['w.type' => 'Брак']) + ->andWhere(['>', 'c.quantity', 0]) + ->andWhere(['in', 'p.product_id', $pArray]) + ->andWhere(['<=', 'w.date', "$date_start_sale2 00:00:00"]) + ->andWhere(['>=', 'w.date', new Expression($date_start_sale2 . ' - INTERVAL ' . $periodDaysStats_where . ' day')]) + ->groupBy(['p.product_id', 'w.store_id', 'c.comment_id']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT p.product_id,sum(c.quantity) as cnt, w.store_id, c.comment_id +// FROM write_offs as w, write_offs_products as p, write_offs_comments as c +// WHERE p.write_offs_id=w.id AND w.type='Брак' AND c.write_offs_id=p.write_offs_id AND p.product_id=c.product_id AND c.quantity>0 +// +// AND p.product_id in($whereInProductsId_in) +// +// AND w.date<='$date_start_sale2 00:00:00' AND w.date>='$date_start_sale2' -interval $periodDaysStats_where day +// +// group BY p.product_id,w.store_id, c.comment_id"); $valAll=0; @@ -2410,10 +2581,19 @@ function getArrayByFiledName($field_name,$date1,$date2) { $date_14day=date('Y-m-d 00:00:00', $time2-86400*$periodDaysStats); /////// списание за 14 дней AND ($whereInProductsId) w.date>=NOW() - INTERVAL 14 DAY //echo"Дата старта $date_start Считаем списания от даты $date1 до $date_14day и делим на 2 = среднее арифметическое списание за 1 неделю"; //echo"
$field_name $date1 - $date_14day"; - $data2=$db::getRows("SELECT p.product_id,sum(c.quantity) as cnt, c.comment_id - FROM write_offs as w, write_offs_products as p, write_offs_comments as c - WHERE p.write_offs_id=w.id AND w.type='Брак' AND c.write_offs_id=p.write_offs_id AND p.product_id=c.product_id AND c.quantity>0 AND w.date>=? AND w.date<=? - group BY p.product_id, c.comment_id",[$date_14day,$date2]); + $data2 = WriteOffs::find()->alias('w')->select(["p.product_id", "sum(c.quantity) as cnt", "c.comment_id"]) + ->innerJoin('write_offs_products as p', 'p.write_offs_id = w.id') + ->innerJoin('write_offs_comments as c', 'c.write_offs_id = p.write_offs_id AND p.product_id = c.product_id') + ->where(['w.type' => 'Брак']) + ->andWhere(['>', 'c.quantity', 0]) + ->andWhere(['>=', 'w.date', $date_14day]) + ->andWhere(['<=', 'w.date', $date2]) + ->groupBy(['p.product_id', 'c.comment_id']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT p.product_id,sum(c.quantity) as cnt, c.comment_id +// FROM write_offs as w, write_offs_products as p, write_offs_comments as c +// WHERE p.write_offs_id=w.id AND w.type='Брак' AND c.write_offs_id=p.write_offs_id AND p.product_id=c.product_id AND c.quantity>0 AND w.date>=? AND w.date<=? +// group BY p.product_id, c.comment_id",[$date_14day,$date2]); $valAll=0; foreach($data2 as $row2) { @@ -2431,8 +2611,13 @@ function getArrayByFiledName($field_name,$date1,$date2) { // старый формат и старая таблица данных - нужно заменить на данные из таблицы store_orders_fields_data - сначала берем старые //echo"Дата старта проаж $date_start_sale от нее вычитаем 7 дней и получаем Закупки для формирования суммарного заказа от предыдущих недель."; - $dataOrders=$db::getRows("SELECT id, name,date_start FROM store_orders WHERE date_start<='$date_start_sale 00:00:00' AND date_start>='$date_start_sale' -interval 7 day"); + $dataOrders = StoreOrders::find()->select(['id', 'name', 'date_start']) + ->where(['<=', 'date_start', $date_start_sale . ' 00:00:00']) + ->andWhere(['>=', 'date_start', new Expression($date_start_sale . ' - INTERVAL 7 day')]) + ->asArray()->all(); +// $dataOrders=$db::getRows("SELECT id, name,date_start FROM store_orders WHERE date_start<='$date_start_sale 00:00:00' AND date_start>='$date_start_sale' -interval 7 day"); + $orders_whereArr = ArrayHelper::getColumn($dataOrders, 'id'); $orders_where=""; $i=0; echo"В этих закупках считаем закупленный товар"; @@ -2450,7 +2635,10 @@ function getArrayByFiledName($field_name,$date1,$date2) { $orders_where = " AND order_id in($orders_where)"; } - $data3=$db::getRows("SELECT sum(quantity_purchase) as quantity_purchase, product_id, store_id FROM store_orders_item WHERE quantity_purchase>0 $orders_where group by product_id, store_id"); + $data3 = StoreOrdersItem::find()->select(['sum(quantity_purchase) as quantity_purchase', 'product_id', 'store_id']) + ->where(['>', 'quantity_purchase', 0])->andWhere(['in', 'order_id', $orders_whereArr]) + ->groupBy(['product_id', 'store_id'])->asArray()->all(); +// $data3=$db::getRows("SELECT sum(quantity_purchase) as quantity_purchase, product_id, store_id FROM store_orders_item WHERE quantity_purchase>0 $orders_where group by product_id, store_id"); foreach($data3 as $row2) { $data_values[$row2["product_id"]][$row2["store_id"]][0]=$row2["quantity_purchase"]; @@ -2476,9 +2664,14 @@ function getArrayByFiledName($field_name,$date1,$date2) { } } - $dataOrders2=$db::getRows("SELECT id,date_add FROM store_orders - WHERE date_add<='$order_date_add' AND date_add>='$order_date_add' - interval 7 day"); + $dataOrders2 = StoreOrders::find()->select(['id', 'date_add']) + ->where(['<=', 'date_add', $order_date_add]) + ->andWhere(['>=', 'date_add', new Expression($order_date_add . ' - INTERVAL 7 day')]) + ->asArray()->all(); +// $dataOrders2=$db::getRows("SELECT id,date_add FROM store_orders +// WHERE date_add<='$order_date_add' AND date_add>='$order_date_add' - interval 7 day"); + $idssArr = ArrayHelper::getColumn($dataOrders2, 'id'); $idss=""; $k=0; @@ -2494,8 +2687,14 @@ function getArrayByFiledName($field_name,$date1,$date2) { echo"order_date_add = $order_date_add orders_where=$orders_where "; - $data2=$db::getRows("SELECT sum(value) as value, product_id, color FROM - store_orders_fields_data WHERE field_name='quantity_zakup_fact' AND value>0 $orders_where group by product_id,color"); + $data2 = StoreOrdersFieldsData::find()->select(['sum(value) as value', 'product_id', 'color']) + ->where(['field_name' => 'quantity_zakup_fact']) + ->andWhere(['>', 'value', 0])->andWhere(['in', 'order_id', $idssArr]) + ->andWhere(['!=', 'order_id', $orderId]) + ->groupBy(['product_id', 'color']) + ->asArray()->all(); +// $data2=$db::getRows("SELECT sum(value) as value, product_id, color FROM +// store_orders_fields_data WHERE field_name='quantity_zakup_fact' AND value>0 $orders_where group by product_id,color"); // echo"
SQL SELECT sum(value) as value, product_id, color FROM store_orders_fields_data WHERE field_name='quantity_zakup_fact' AND value>0 $orders_where group by product_id,color "; @@ -2516,7 +2715,8 @@ function getArrayByFiledName($field_name,$date1,$date2) { if (in_array($field_name,$inFieldTable)) { /// echo"+$field_name+ order_id=$orderId"; - $data=$db::getRows("SELECT product_id,$field_name FROM store_orders_prices WHERE order_id=?",[$orderId]); + $data = StoreOrdersPrices::find()->select(['product_id', $field_name])->where(['order_id' => $orderId])->asArray()->all(); +// $data=$db::getRows("SELECT product_id,$field_name FROM store_orders_prices WHERE order_id=?",[$orderId]); foreach($data as $row) { //foreach($inFieldTable as $field) $data_values[$row["product_id"]][0][0]=$row[$field_name]; @@ -2527,7 +2727,10 @@ function getArrayByFiledName($field_name,$date1,$date2) { $inFieldTableColor=["quantity_zakup","quantity_zakup_fact","comment_zakup","comment_discrepancy_polnogramm","quantity_warehouseman_fact","quantity_rejection","quantity_zakup_info","additional_quantity"]; $field_id=$fieldsRows[$field_name]["id"]; if (!empty($field_id) and in_array($field_name,$inFieldTableColor)) { - $data=$db::getRows("SELECT product_id, store_id, quantity, color FROM store_orders_colors WHERE order_id=? AND field_id=? AND color!='' AND store_id!='undefined'",[$orderId,$field_id]); + $data = StoreOrdersColors::find()->select(['product_id', 'store_id', 'quantity', 'color']) + ->where(['order_id' => $orderId, 'field_id' => $field_id])->andWhere(['!=', 'color', '']) + ->andWhere(['!=', 'store_id', 'undefined'])->asArray()->all(); +// $data=$db::getRows("SELECT product_id, store_id, quantity, color FROM store_orders_colors WHERE order_id=? AND field_id=? AND color!='' AND store_id!='undefined'",[$orderId,$field_id]); foreach($data as $row) { if (empty($row["store_id"])) $data_values[$row["product_id"]][$row["store_id"]][$row["color"]]=$row["quantity"]; } @@ -2540,7 +2743,10 @@ function getArrayByFiledName($field_name,$date1,$date2) { if (in_array($field_name,$inFieldTable)) { //echo"+!!!!!$field_name+ order_id=$orderId"; - $data=$db::getRows("SELECT product_id, store_id, $field_name FROM store_orders_item WHERE order_id=? AND store_id!='undefined'",[$orderId]); + $data = StoreOrdersItem::find()->select(['product_id', 'store_id', $field_name]) + ->where(['order_id' => $orderId]) + ->andWhere(['!=', 'store_id', 'undefined'])->asArray()->all(); +// $data=$db::getRows("SELECT product_id, store_id, $field_name FROM store_orders_item WHERE order_id=? AND store_id!='undefined'",[$orderId]); foreach($data as $row) { $data_values[$row["product_id"]][$row["store_id"]][0]=$row[$field_name]; } @@ -2548,7 +2754,10 @@ function getArrayByFiledName($field_name,$date1,$date2) { $inFieldTableColor=["quantity_purchase","goods_in_transit122","division_quantity","division_fact"]; $field_id=$fieldsRows[$field_name]["id"]; if (!empty($field_id) and in_array($field_name,$inFieldTableColor)) { - $data=$db::getRows("SELECT product_id, store_id, quantity, color FROM store_orders_colors WHERE order_id=? AND field_id=? AND color!='' AND store_id!='undefined'",[$orderId,$field_id]); + $data = StoreOrdersColors::find()->select(['product_id', 'store_id', 'quantity', 'color']) + ->where(['order_id' => $orderId, 'field_id' => $field_id])->andWhere(['!=', 'color', '']) + ->andWhere(['!=', 'store_id', 'undefined'])->asArray()->all(); +// $data=$db::getRows("SELECT product_id, store_id, quantity, color FROM store_orders_colors WHERE order_id=? AND field_id=? AND color!='' AND store_id!='undefined'",[$orderId,$field_id]); foreach($data as $row) { $data_values[$row["product_id"]][$row["store_id"]][$row["color"]]=$row["quantity"]; } @@ -2564,7 +2773,10 @@ function getArrayByFiledName($field_name,$date1,$date2) { // функция заносит в массив данные по полям в заказеЗакупщика исходные данные ID заказа Дата 1 Дата2 И магазин Может быть =0 function data_values_insert_sql($date1,$date2) { global $db,$providers,$products,$ProductsOptions,$whereInProductsId,$date_start_sale,$date_start_division,$orderId,$fieldsRows; -$data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); +$data = StoreOrdersFields::find()->select(['id', 'name_eng as field_name', 'position', 'field_type', 'description', 'sql_table_values']) + ->orderBy(['position' => SORT_ASC, 'id' => SORT_ASC]) + ->asArray()->all(); +//$data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); echo"
"; foreach($data as $row) { echo "
".$row["position"]." ".$row["field_name"]." ".$row["field_type"]."".$row["sql_table_values"]." @@ -2655,18 +2867,22 @@ function returnRowCssClassByFieldValue($fieldName,$value){ } function getDataFiledsData() { - global $orderId, $db,$whereInProductsId,$fieldsRows; + global $orderId, $db, $whereInProductsId, $whereInProductsIdArr, $fieldsRows; //глобальный массив с переменными - только по определнным товарам - лишнего не берем - $da=$db::getRows("SELECT id,min_lot,min_order FROM products_1c_options WHERE id $whereInProductsId"); + $da = Products1cOptions::find()->select(['id', 'min_lot', 'min_order']) + ->where(['in', 'id', $whereInProductsIdArr ?? []]) + ->asArray()->all(); +// $da=$db::getRows("SELECT id,min_lot,min_order FROM products_1c_options WHERE id $whereInProductsId"); foreach($da as $r) { $FiledsData["min_order"][$r["id"]]=$r["min_order"]; $FiledsData["min_lot"][$r["id"]]=$r["min_lot"]; } // готовим массим со всеми данными по столбцам по каждому товару магазину и цвету Если NULL - то это сумма по магазинам и по цветам - $dataF=$db::getRows("SELECT * FROM store_orders_fields_data WHERE order_id=?",[$orderId]); + $dataF = StoreOrdersFieldsData::find()->where(['order_id' => $orderId])->asArray()->all(); +// $dataF=$db::getRows("SELECT * FROM store_orders_fields_data WHERE order_id=?",[$orderId]); foreach($dataF as $row) { //если тип поля текст то заменяем if ($fieldsRows[$row["field_name"]]["tip"]=="string") @@ -2693,7 +2909,7 @@ function getDataFiledsData() { //функция обновления данных function updatefieldsRows() { - global $db,$fieldsRows,$whereInProductsId,$ProductsOptions,$date_start_sale, $order_date_add, $date_start_division, $DataFieldStats ,$orderId, $FiledsData,$storesArr, $storesArrayAll, + global $db,$fieldsRows,$whereInProductsId,$whereInProductsIdArr,$ProductsOptions,$date_start_sale, $order_date_add, $date_start_division, $DataFieldStats ,$orderId, $FiledsData,$storesArr, $storesArrayAll, $products, $productsColorsArray; $html=""; @@ -2719,8 +2935,12 @@ function updatefieldsRows() { //$db::sql("DELETE FROM store_orders_fields_data WHERE field_name='$fieldName' AND order_id='$orderId'"); $pole=trim($fieldsRows[$fieldName]["func_content"]); $data_up=array(); - $dataF=$db::getRows("SELECT product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND -store_id!='' AND store_id!='NULL' AND field_name='$pole' group by product_id, color",[$orderId]); + $dataF = StoreOrdersFieldsData::find()->select(['product_id', 'sum(value) as sum', 'color']) + ->where(['order_id' => $orderId])->andWhere(['not in', 'store_id', ['', 'NULL']]) + ->andWhere(['field_name' => $pole])->groupBy(['product_id', 'color']) + ->asArray()->all(); +// $dataF=$db::getRows("SELECT product_id,sum(value) as sum, color FROM store_orders_fields_data WHERE order_id=? AND +//store_id!='' AND store_id!='NULL' AND field_name='$pole' group by product_id, color",[$orderId]); foreach($dataF as $row) { if (empty($row["color"])) $row["color"]="NULL"; $data_up[$row["product_id"]]["NULL"][$row["color"]]=$row["sum"]; @@ -2736,9 +2956,15 @@ store_id!='' AND store_id!='NULL' AND field_name='$pole' group by product_id, c // полнограммы факт по всем магазинам - $date_id=$db::getValue("SELECT date_id FROM store_products_fact order by date_id desc LIMIT 1"); - $data3=$db::getRows("SELECT store_id,product_id,quantity,color FROM store_products_fact WHERE date_id=? AND quantity>0 AND product_id -$whereInProductsId",[$date_id]); + $spf005 = StoreProductsFact::find()->select(['date_id'])->orderBy(['date_id' => SORT_DESC])->one(); + $date_id = $spf005['date_id']; +// $date_id=$db::getValue("SELECT date_id FROM store_products_fact order by date_id desc LIMIT 1"); + $data3 = StoreProductsFact::find()->select(['store_id', 'product_id', 'quantity,color']) + ->where(['date_id' => $date_id])->andWhere(['>', 'quantity', 0]) + ->andWhere(['in', 'product_id', $whereInProductsIdArr]) + ->asArray()->all(); +// $data3=$db::getRows("SELECT store_id,product_id,quantity,color FROM store_products_fact WHERE date_id=? AND quantity>0 AND product_id +//$whereInProductsId",[$date_id]); foreach($data3 as $row2) { if ($row2["color"]=="" or $row2["color"]=="NULL") $polnogrammaFact[$row2["product_id"]][$row2["store_id"]]["NULL"]=$row2["quantity"]; @@ -2748,7 +2974,10 @@ $whereInProductsId",[$date_id]); $html .="

Считаем коэффиценты по цветам для полнограммы

"; - $data=$db::getRows("SELECT product_id, store_id,quantity, color FROM store_planogram WHERE quantity>0 AND product_id $whereInProductsId"); + $data = StorePlanogram::find()->select(['product_id', 'store_id', 'quantity', 'color'])->where(['>', 'quantity', 0]) + ->andWhere(['in', 'product_id', $whereInProductsIdArr]) + ->asArray()->all(); +// $data=$db::getRows("SELECT product_id, store_id,quantity, color FROM store_planogram WHERE quantity>0 AND product_id $whereInProductsId"); foreach($data as $row) { if (empty($row["color"])) $store_planogram[$row["product_id"]][$row["store_id"]]=$row["quantity"]; else $store_planogram_colors[$row["product_id"]][$row["store_id"]][$row["color"]]=$row["quantity"]; @@ -2763,7 +2992,10 @@ $whereInProductsId",[$date_id]); - $data4=$db::getRows("SELECT id,colors FROM products_1c_options WHERE provider_id>0 AND colors!='' AND id $whereInProductsId group by id order by id"); + $data4 = Products1cOptions::find()->select(['id', 'colors'])->where(['>', 'provider_id', 0])->andWhere(['!-', 'colors', '']) + ->andWhere(['in', 'id', $whereInProductsIdArr])->groupBy(['id'])->orderBy(['id' => SORT_DESC]) + ->asArray()->all(); +// $data4=$db::getRows("SELECT id,colors FROM products_1c_options WHERE provider_id>0 AND colors!='' AND id $whereInProductsId group by id order by id"); foreach($data4 as $row2) { $colors=explode(";",$row2["colors"]); @@ -2811,8 +3043,11 @@ $whereInProductsId",[$date_id]); ////// - $data=$db::getRows("SELECT id,name_eng as field_name, name, position, field_type, description, sql_table_values -FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); + $data = StoreOrdersFields::find()->select(['id', 'name_eng as field_name', 'name', 'position', 'field_type', 'description', 'sql_table_values']) + ->orderBy(['position' => SORT_ASC, 'id' => SORT_ASC]) + ->asArray()->all(); +// $data=$db::getRows("SELECT id,name_eng as field_name, name, position, field_type, description, sql_table_values +//FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); $html .=""; foreach($data as $row) { $html .= "
".$row["position"]."".$row["name"].""; @@ -2999,7 +3234,10 @@ FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); // вычисляем массив по цветам привязанным к товару - $data4=$db::getRows("SELECT id,colors FROM products_1c_options WHERE provider_id>0 AND colors!='' AND id $whereInProductsId group by id order by id"); + $data4 = Products1cOptions::find()->select(['id', 'colors'])->where(['>', 'provider_id', 0])->andWhere(['!=', 'colors', '']) + ->andWhere(['in', 'id', $whereInProductsIdArr])->groupBy(['id'])->orderBy(['id' => SORT_DESC]) + ->asArray()->all(); +// $data4=$db::getRows("SELECT id,colors FROM products_1c_options WHERE provider_id>0 AND colors!='' AND id $whereInProductsId group by id order by id"); foreach($data4 as $row2) { $colors=explode(";",$row2["colors"]); $productId=$row2["id"]; @@ -3041,7 +3279,8 @@ FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); // exit("+++++++exit +++++"); $rowFactName="quantity_warehouseman_fact"; // old quantity_warehouseman_fact quantity_zakup_fact - $db::sql("DELETE FROM store_orders_fields_data WHERE field_name in ('division_auto','division_summ') AND order_id='$orderId'"); + StoreOrdersFieldsData::deleteAll(['and', ['in', 'field_name', ['division_auto', 'division_summ']], ['order_id' => $orderId]]); +// $db::sql("DELETE FROM store_orders_fields_data WHERE field_name in ('division_auto','division_summ') AND order_id='$orderId'"); @@ -3261,9 +3500,11 @@ FROM store_orders_fields WHERE 1 order by position ASC, id ASC"); echo $html.$rp; - $db::sql("DELETE FROM store_orders_fields_data WHERE value='0.000' AND value_text=''",[$orderId]); + StoreOrdersFieldsData::deleteAll(['value' => '0.000', 'value_text' => '']); // ??? +// $db::sql("DELETE FROM store_orders_fields_data WHERE value='0.000' AND value_text=''",[$orderId]); - $db::sql("UPDATE store_orders SET date_update=NOW(), update_html=? WHERE id=?",["$html.$rp",$orderId]); + StoreOrders::updateAll(['date_update' => new Expression("NOW()"), 'update_html' => "%html.$rp"], ['id' => $orderId]); +// $db::sql("UPDATE store_orders SET date_update=NOW(), update_html=? WHERE id=?",["$html.$rp",$orderId]); if (!empty($_REQUEST["html_print"])) echo $html; @@ -3291,8 +3532,11 @@ function updateProductArrayDataField($fieldsNamesArr) { echo"

Обновляем данные по выбранным полям и перезаписываем их в таблицу

"; - $data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values FROM store_orders_fields - WHERE $whereF order by position ASC, id ASC"); + $data = StoreOrdersFields::find()->select(['id', 'name_eng as field_name', 'position', 'field_type', 'description', 'sql_table_values']) + ->where(['in', 'name_eng', $fieldsNamesArr])->orderBy(['position' => SORT_ASC, 'id' => SORT_ASC]) + ->asArray()->all(); +// $data=$db::getRows("SELECT id,name_eng as field_name, position, field_type, description, sql_table_values FROM store_orders_fields +// WHERE $whereF order by position ASC, id ASC"); echo""; foreach($data as $row) { echo"
== ".$row["field_name"]." ".$row["field_type"]." "; diff --git a/erp24/records/StoreOrderStatusLog.php b/erp24/records/StoreOrderStatusLog.php new file mode 100644 index 0000000..c5d52eb --- /dev/null +++ b/erp24/records/StoreOrderStatusLog.php @@ -0,0 +1,52 @@ + 36], + ]; + } + + /** + * {@inheritdoc} + */ + public function attributeLabels() + { + return [ + 'id' => 'ID', + 'order_id' => 'Order ID', + 'status' => 'Status', + 'date' => 'Date', + 'admin_id' => 'Admin ID', + ]; + } +} diff --git a/erp24/records/StoreProductsFact.php b/erp24/records/StoreProductsFact.php new file mode 100644 index 0000000..236c053 --- /dev/null +++ b/erp24/records/StoreProductsFact.php @@ -0,0 +1,64 @@ + 36], + [['color'], 'string', 'max' => 55], + [['date_id', 'product_id', 'color', 'store_id'], 'unique', 'targetAttribute' => ['date_id', 'product_id', 'color', 'store_id']], + ]; + } + + /** + * {@inheritdoc} + */ + public function attributeLabels() + { + return [ + 'date_id' => 'Date ID', + 'product_id' => 'Product ID', + 'color' => 'Color', + 'store_id' => 'Store ID', + 'quantity' => 'Quantity', + 'quantity_polnogramm' => 'Quantity Polnogramm', + 'difference' => 'Difference', + 'difference_percent' => 'Difference Percent', + 'comment_type_id' => 'Comment Type ID', + 'comments' => 'Comments', + ]; + } +} diff --git a/erp24/services/SupportService.php b/erp24/services/SupportService.php new file mode 100644 index 0000000..f4968c2 --- /dev/null +++ b/erp24/services/SupportService.php @@ -0,0 +1,24 @@ +select(['name', 'id', 'providers_arr', 'status', + "DATE_FORMAT(date_start, '%Y-%m-%d') as date_start", "DATE_FORMAT(date_add, '%Y-%m-%d') as date_add", + "DATE_FORMAT(division_date, '%Y-%m-%d') as division_date", "UNIX_TIMESTAMP(date_update) as date_update", 'parent_id']) + ->where(['id' => $orderId])->asArray()->one(); + } + + public static function products1cOptionsProducts1cSelect001($providersIdInThisOrder) { + return Products1cOptions::find()->alias('o')->select(['p.id', 'p.name', 'o.provider_id', 'o.price_zakup', 'p.parent_id']) + ->innerJoin('products_1c as p', 'p.id = o.id') + ->where(['in', 'o.provider_id', $providersIdInThisOrder]) + ->orderBy(['o.provider_id' => SORT_ASC, 'p.name' => SORT_ASC]) + ->asArray()->all(); + } +} \ No newline at end of file diff --git a/erp24/views/shipment/fields-data2.php b/erp24/views/shipment/fields-data2.php index 511216d..c020b49 100644 --- a/erp24/views/shipment/fields-data2.php +++ b/erp24/views/shipment/fields-data2.php @@ -1,5 +1,18 @@ (int)$_POST["status_new"]], ['id' => $orderId]); +// $db::sql("UPDATE store_orders SET status=? WHERE id=?",[(int)$_POST["status_new"],$orderId]); + $storeOrderStatusLog = new StoreOrderStatusLog; + $storeOrderStatusLog->status = (int)$_POST["status_new"]; + $storeOrderStatusLog->order_id = $orderId; + $storeOrderStatusLog->date = date('Y-m-d H:i:s'); + $storeOrderStatusLog->admin_id = $_SESSION["admin_id"]; + $storeOrderStatusLog->save(); +// $db::sql("INSERT IGNORE INTO store_order_status_log (status, order_id, date, admin_id) VALUES (?,?,NOW(),?)",[(int)$_POST["status_new"],$orderId, $_SESSION["admin_id"]]); mess("Статус изменен"); } @@ -65,7 +86,9 @@ if(!empty($_POST["edit_status"]) and !empty($_REQUEST["id"])) { include_once(dirname(__DIR__, 2) . "/inc/functionsFiedlsData.php"); -$data=$db::mapping("SELECT id,name FROM admin_group WHERE id in ('1','10','7','51','9','17','70')"); +$data = ArrayHelper::map(AdminGroup::find()->select(['id', 'name']) + ->where(['in', 'id', ['1','10','7','51','9','17','70']])->all(), 'id', 'name'); +//$data=$db::mapping("SELECT id,name FROM admin_group WHERE id in ('1','10','7','51','9','17','70')"); @@ -136,7 +159,8 @@ if($status_order_id==1) { if(!empty($_REQUEST["load"])) { - $data=$db::getRows("SELECT product_id, price FROM `prices` WHERE product_id $whereInProductsId"); + $data = Prices::find()->select(['product_id', 'price'])->where(['in', 'product_id', $whereInProductsIdArr])->asArray()->all(); +// $data=$db::getRows("SELECT product_id, price FROM `prices` WHERE product_id $whereInProductsId"); foreach($data as $row) $productsPrices[$row["product_id"]]=$row["price"]; $data_uper=[]; foreach($products as $productId => $nameProduct) { @@ -144,9 +168,10 @@ if($status_order_id==1) { $price=$productsPrices[$productId]; $data_uper[$productId]["NULL"]["NULL"]=$price; - - $db::sql("UPDATE `store_orders_prices` SET purchase_price='$price' -WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purchase_price!='0.00'"); + StoreOrdersPrices::updateAll(['purchase_price' => $price], + ['and', ['product_id' => $productId], ['order_id' => $orderId], ['provider_id' => 1], ['!=', 'purchase_price', '0.00']]); +// $db::sql("UPDATE `store_orders_prices` SET purchase_price='$price' +//WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purchase_price!='0.00'"); } @@ -158,8 +183,10 @@ WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purc foreach($products as $productId => $nameProduct) { $price=$ProductsOptions[$productId]["price_zakup"]; $data_uper[$productId]["NULL"]["NULL"]=$ProductsOptions[$productId]["price_zakup"]; - $db::sql("UPDATE `store_orders_prices` SET purchase_price_zakup='$price' -WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purchase_price_zakup!='0.00'"); + StoreOrdersPrices::updateAll(['purchase_price_zakup' => $price], + ['and', ['product_id' => $productId], ['order_id' => $orderId], ['provider_id' => 1], ['!=', 'purchase_price_zakup', '0.00']]); +// $db::sql("UPDATE `store_orders_prices` SET purchase_price_zakup='$price' +//WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purchase_price_zakup!='0.00'"); } insert_store_orders_fields($data_uper,"purchase_price_zakup"); @@ -171,7 +198,8 @@ WHERE product_id='$productId' AND order_id='$orderId' AND provider_id=1 AND purc $products_varieties=[]; -$data=$db::getRows("SELECT id,product_id,color,name FROM `products_varieties`"); +$data = ProductsVarieties::find()->select(['id', 'product_id', 'color', 'name'])->asArray()->all(); +//$data=$db::getRows("SELECT id,product_id,color,name FROM `products_varieties`"); foreach($data as $row) $products_varieties[$row["product_id"]][$row["color"]][$row["id"]]=$row["name"]; @@ -202,10 +230,17 @@ if(!empty($group_id)) { //извлекаем сортировку полей в зависимости от группы сотрудника //$sort=$db::getValue("SELECT fields_sort FROM store_orders_fields_sort WHERE group_id=?",[$group_id]); - $sort=$db::getValue("SELECT fields_sort FROM store_orders_statuses WHERE id=?",[$status_order_id]); - if(!empty($sort)) $sort="ORDER BY FIELD(`id`, $sort) "; + $storeOrdersStatuses = StoreOrdersStatuses::find()->select(['fields_sort'])->where(['id' => $status_order_id])->asArray()->one(); + $sort = $storeOrdersStatuses['fields_sort'] ?? ''; +// $sort=$db::getValue("SELECT fields_sort FROM store_orders_statuses WHERE id=?",[$status_order_id]); +// if(!empty($sort)) $sort="ORDER BY FIELD(`id`, $sort) "; - $data=$db::getRows("SELECT * FROM store_orders_fields $sort"); + $dataQuery = StoreOrdersFields::find(); + if (!empty($sort)) { + $dataQuery->orderBy([new Expression('FIELD(id, ' . $sort . ')')]); + } + $data = $dataQuery->asArray()->all(); +// $data=$db::getRows("SELECT * FROM store_orders_fields $sort"); } foreach($data as $row) { $dostup=$dostup_fields[$row["id"]]; @@ -232,12 +267,15 @@ echo ''.$thead.''; // полнограммы по всем магазинам if(!empty($store_id)) { - $data3=$db::getRows("SELECT store_id,product_id,quantity FROM store_planogram WHERE color='' -AND store_id!='' AND quantity>0 AND store_id=?",[$store_id]); + $data3 = StorePlanogram::find()->select(['store_id', 'product_id', 'quantity'])->where(['color' => '']) + ->andWhere(['!=', 'store_id', ''])->andWhere(['>', 'quantity', '0'])->andWhere(['store_id' => $store_id])->asArray()->all(); +// $data3=$db::getRows("SELECT store_id,product_id,quantity FROM store_planogram WHERE color='' +//AND store_id!='' AND quantity>0 AND store_id=?",[$store_id]); foreach($data3 as $row2) $store_planogramAllStores[$row2["store_id"]][$row2["product_id"]]=$row2["quantity"]; } -$data3=$db::getRows("SELECT * FROM products_class WHERE tip in ('potted','wrap')"); +$data3 = ProductsClass::find()->where(['in', 'tip', ['potted','wrap']])->asArray()->all(); +//$data3=$db::getRows("SELECT * FROM products_class WHERE tip in ('potted','wrap')"); foreach($data3 as $row2) $products_class[$row2["category_id"]]=$row2["tip"]; -- 2.39.5