From 7688b0e1a8e21af05fad32d67f89d1ce95c217d6 Mon Sep 17 00:00:00 2001
From: Alexander Smirnov <fredeom@mail.ru>
Date: Thu, 25 Jan 2024 11:05:49 +0300
Subject: [PATCH] replace pdo with ar in polnogram

---
 erp24/records/StorePlanogramColorsSort.php |  50 +++++++
 erp24/records/StorePlanogramLogi.php       |  58 ++++++++
 erp24/views/shipment/polnogramm.php        | 162 +++++++++++++++------
 3 files changed, 228 insertions(+), 42 deletions(-)
 create mode 100644 erp24/records/StorePlanogramColorsSort.php
 create mode 100644 erp24/records/StorePlanogramLogi.php

diff --git a/erp24/records/StorePlanogramColorsSort.php b/erp24/records/StorePlanogramColorsSort.php
new file mode 100644
index 0000000..b933571
--- /dev/null
+++ b/erp24/records/StorePlanogramColorsSort.php
@@ -0,0 +1,50 @@
+<?php
+
+namespace yii_app\records;
+
+use Yii;
+
+/**
+ * This is the model class for table "store_planogram_colors_sort".
+ *
+ * @property string $store_id GUID магазина
+ * @property string $product_id GUID товара
+ * @property string $color название цвета
+ * @property int $posit номер позиции
+ */
+class StorePlanogramColorsSort extends \yii\db\ActiveRecord
+{
+    /**
+     * {@inheritdoc}
+     */
+    public static function tableName()
+    {
+        return 'store_planogram_colors_sort';
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function rules()
+    {
+        return [
+            [['store_id', 'product_id', 'color', 'posit'], 'required'],
+            [['posit'], 'integer'],
+            [['store_id', 'product_id', 'color'], 'string', 'max' => 36],
+            [['store_id', 'product_id', 'color'], 'unique', 'targetAttribute' => ['store_id', 'product_id', 'color']],
+        ];
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function attributeLabels()
+    {
+        return [
+            'store_id' => 'Store ID',
+            'product_id' => 'Product ID',
+            'color' => 'Color',
+            'posit' => 'Posit',
+        ];
+    }
+}
diff --git a/erp24/records/StorePlanogramLogi.php b/erp24/records/StorePlanogramLogi.php
new file mode 100644
index 0000000..84b3dee
--- /dev/null
+++ b/erp24/records/StorePlanogramLogi.php
@@ -0,0 +1,58 @@
+<?php
+
+namespace yii_app\records;
+
+use Yii;
+
+/**
+ * This is the model class for table "store_planogram_logi".
+ *
+ * @property int $date_id ID дата Ymd
+ * @property string $store_id
+ * @property string $product_id
+ * @property float $quantity минимальное кол-во по полнограмме
+ * @property int $quantity_max максимальное кол-во по полнограмме
+ * @property int $comment_id ID комментария 1- передарка 2 недотарка
+ * @property string $color
+ */
+class StorePlanogramLogi extends \yii\db\ActiveRecord
+{
+    /**
+     * {@inheritdoc}
+     */
+    public static function tableName()
+    {
+        return 'store_planogram_logi';
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function rules()
+    {
+        return [
+            [['date_id', 'store_id', 'product_id', 'quantity', 'quantity_max', 'color'], 'required'],
+            [['date_id', 'quantity_max', 'comment_id'], 'integer'],
+            [['quantity'], 'number'],
+            [['store_id', 'product_id'], 'string', 'max' => 36],
+            [['color'], 'string', 'max' => 55],
+            [['date_id', 'store_id', 'product_id', 'color'], 'unique', 'targetAttribute' => ['date_id', 'store_id', 'product_id', 'color']],
+        ];
+    }
+
+    /**
+     * {@inheritdoc}
+     */
+    public function attributeLabels()
+    {
+        return [
+            'date_id' => 'Date ID',
+            'store_id' => 'Store ID',
+            'product_id' => 'Product ID',
+            'quantity' => 'Quantity',
+            'quantity_max' => 'Quantity Max',
+            'comment_id' => 'Comment ID',
+            'color' => 'Color',
+        ];
+    }
+}
diff --git a/erp24/views/shipment/polnogramm.php b/erp24/views/shipment/polnogramm.php
index 99dfbcb..7900ea7 100644
--- a/erp24/views/shipment/polnogramm.php
+++ b/erp24/views/shipment/polnogramm.php
@@ -1,5 +1,11 @@
 <?php
 
+use \yii_app\records\StorePlanogram;
+use \yii_app\records\StorePlanogramLogi;
+use \yii_app\records\StorePlanogramColorsSort;
+use \yii_app\records\Products1cOptions;
+use \yii_app\records\ProductsClass;
+
 include_once(dirname(__DIR__, 2) . "/startup.php");
 include_once(dirname(__DIR__, 2) . "/inc/db.php");
 include_once(dirname(__DIR__, 2) . "/inc/base_new.php");
@@ -41,7 +47,9 @@ $massiver[]=["name"=>"П-ма Макс.","title"=>"Полнограмма Мак
 
 echo'<h1 class="page-title mb-0 text-primary">полнограмма</h1>';
 
-$stores=$db::mapping("SELECT name,id FROM products_1c WHERE tip='city_store' AND view='1' $where  order by name ASC");
+$stores = yii\helpers\ArrayHelper::map(\yii_app\records\Products1c::find()->select(['name', 'id'])->where(['and', ['tip' => 'city_store'], ['view' => '1']])
+    ->andWhere(['in', 'id', $_SESSION["store_arr_guid_dostup"]])->orderBy(['name' => SORT_ASC])->all(), 'id', 'name');
+//$stores=$db::mapping("SELECT name,id FROM products_1c WHERE tip='city_store' AND view='1' $where  order by name ASC");
 
 
 
@@ -52,7 +60,8 @@ foreach($stores as $strid=>$namestore){
 
 
 //$w="";
-$providers=$db::mapping("SELECT id,name FROM shipment_providers ");
+$providers = yii\helpers\ArrayHelper::map(\yii_app\records\ShipmentProviders::find()->select(['name', 'id'])->all(), 'id', 'name');
+//$providers=$db::mapping("SELECT id,name FROM shipment_providers ");
 //$k=0;
 //foreach($in as $pid) { echo'<span class="tag m-1">'.$providers[$pid].'</span>'; if($k!=0) $w .=",";  $w .=" '$pid'"; $k++; }
 
@@ -86,7 +95,7 @@ if(!empty($_POST["save"])) {
 
 
 
-
+    $log = [];
     foreach($sql_param as $prod_id => $massi) {
 
         $dataRow=$db::getRows("SELECT date_id, color, quantity, quantity_max FROM store_planogram_logi WHERE
@@ -106,21 +115,48 @@ store_id='$store_id' AND product_id='$prod_id'  order by date_id DESC LIMIT 12")
 
 
 
-        if($log["NULL"]["quantity"]!=$massi["quantity"])
+        if(($log["NULL"]["quantity"] ?? 0) != $massi["quantity"])
         {
 
             $comment_id=(int)$_POST["comment_id"][$prod_id];
             // echo" <br>".$log["NULL"]["quantity"]." new_valiue= ".$massi["quantity"]." comment_id=$comment_id ";
-            $sql="INSERT IGNORE INTO store_planogram SET  ";
-            $up= "store_id='$store_id', product_id='$prod_id', color=''"; foreach($massi as $pole =>$value)  $up .=", $pole='$value' ";
-            $sql .="$up ON DUPLICATE KEY UPDATE $up";
-            $db::sql($sql);
+            $storePlanogram = StorePlanogram::find()->where(['store_id' => $store_id, 'product_id' => $prod_id, 'color' => ''])->one();
+            if (!$storePlanogram) {
+                $storePlanogram = new StorePlanogram;
+                $storePlanogram->store_id = $store_id;
+                $storePlanogram->product_id = $prod_id;
+                $storePlanogram->color = '';
+            }
+            foreach ($massi as $pole => $value) {
+                $storePlanogram->$pole = $value;
+            }
+            $storePlanogram->save();
+            if ($storePlanogram->getErrors()) {
+                var_dump($storePlanogram->getErrors());
+            }
+//            $sql="INSERT IGNORE INTO store_planogram SET  ";
+//            $up= "store_id='$store_id', product_id='$prod_id', color=''"; foreach($massi as $pole =>$value)  $up .=", $pole='$value' ";
+//            $sql .="$up ON DUPLICATE KEY UPDATE $up";
+//            $db::sql($sql);
             // echo"<br>$sql ";
-
-            $up .=", date_id='".date("Ymd")."', comment_id='$comment_id'";
-            $sql="INSERT IGNORE INTO store_planogram_logi SET  ";
-            $sql .="$up ON DUPLICATE KEY UPDATE $up";
-            $db::sql($sql);
+            $storePlanogramLogi = StorePlanogramLogi::find()
+                ->where(['date_id' => date("Ymd"), 'store_id' => $store_id, 'product_id' => $prod_id, 'color' => ''])->one();
+            if (!$storePlanogramLogi) {
+                $storePlanogramLogi = new StorePlanogramLogi;
+                $storePlanogramLogi->date_id = date("Ymd");
+                $storePlanogramLogi->store_id = $store_id;
+                $storePlanogramLogi->product_id = $prod_id;
+                $storePlanogramLogi->color = '';
+            }
+            foreach ($massi as $pole => $value) {
+                $storePlanogramLogi->$pole = $value;
+            }
+            $storePlanogramLogi->comment_id = $comment_id;
+            $storePlanogramLogi->save();
+//            $up .=", date_id='".date("Ymd")."', comment_id='$comment_id'";
+//            $sql="INSERT IGNORE INTO store_planogram_logi SET  ";
+//            $sql .="$up ON DUPLICATE KEY UPDATE $up";
+//            $db::sql($sql);
 
 
 
@@ -129,16 +165,35 @@ store_id='$store_id' AND product_id='$prod_id'  order by date_id DESC LIMIT 12")
                 $color=trim($color);
                 $quan=(int)$quan;
                 if(!empty($color)) {
-                    $sql="INSERT IGNORE INTO store_planogram SET  ";
-                    $up= " store_id='$store_id', product_id='$prod_id', color='$color',  quantity='$quan' ";
-                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
-                    $db::sql($sql);
-
-
-                    $up .=", date_id='".date("Ymd")."', comment_id='$comment_id'";
-                    $sql="INSERT IGNORE INTO store_planogram_logi SET  ";
-                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
-                    $db::sql($sql);
+                    $storePlanogram = StorePlanogram::find()->where(['store_id' => $store_id, 'product_id' => $prod_id, 'color' => $color])->one();
+                    if (!$storePlanogram) {
+                        $storePlanogram = new StorePlanogram;
+                        $storePlanogram->store_id = $store_id;
+                        $storePlanogram->product_id = $prod_id;
+                        $storePlanogram->color = $color;
+                    }
+                    $storePlanogram->quantity = $quan;
+                    $storePlanogram->save();
+//                    $sql="INSERT IGNORE INTO store_planogram SET  ";
+//                    $up= " store_id='$store_id', product_id='$prod_id', color='$color',  quantity='$quan' ";
+//                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
+//                    $db::sql($sql);
+
+                    $storePlanogramLogi = StorePlanogramLogi::find()
+                        ->where(['date_id' => date("Ymd"), 'store_id' => $store_id, 'product_id' => $prod_id, 'color' => $color])->one();
+                    if (!$storePlanogramLogi) {
+                        $storePlanogramLogi = new StorePlanogramLogi;
+                        $storePlanogramLogi->date_id = date("Ymd");
+                        $storePlanogramLogi->store_id = $store_id;
+                        $storePlanogramLogi->product_id = $prod_id;
+                        $storePlanogramLogi->color = $color;
+                    }
+                    $storePlanogramLogi->comment_id = $comment_id;
+                    $storePlanogramLogi->save();
+//                    $up .=", date_id='".date("Ymd")."', comment_id='$comment_id'";
+//                    $sql="INSERT IGNORE INTO store_planogram_logi SET  ";
+//                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
+//                    $db::sql($sql);
 
 
 
@@ -150,10 +205,19 @@ store_id='$store_id' AND product_id='$prod_id'  order by date_id DESC LIMIT 12")
                 $color=trim($color);
                 $quan=(int)$quan;
                 if(!empty($color)) {
-                    $sql="INSERT IGNORE INTO store_planogram SET ";
-                    $up= " store_id='$store_id', product_id='$prod_id', color='$color', quantity_max='$quan' ";
-                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
-                    $db::sql($sql);
+                    $storePlanogram = StorePlanogram::find()->where(['store_id' => $store_id, 'product_id' => $prod_id, 'color' => $color])->one();
+                    if (!$storePlanogram) {
+                        $storePlanogram = new StorePlanogram;
+                        $storePlanogram->store_id = $store_id;
+                        $storePlanogram->product_id = $prod_id;
+                        $storePlanogram->color = $color;
+                    }
+                    $storePlanogram->quantity = $quan;
+                    $storePlanogram->save();
+//                    $sql="INSERT IGNORE INTO store_planogram SET ";
+//                    $up= " store_id='$store_id', product_id='$prod_id', color='$color', quantity_max='$quan' ";
+//                    $sql .="$up ON DUPLICATE KEY UPDATE $up";
+//                    $db::sql($sql);
                     //echo"<br> $sql";
                 }
             }
@@ -183,14 +247,16 @@ echo'</thead><tbody>';
 
 
 if (isset($store_id)) {
-    $data3 = $db::getRows("SELECT * FROM store_planogram WHERE store_id=? AND color=''", [$store_id]);
+    $data3 = StorePlanogram::find()->where(['store_id' => $store_id, 'color' => ''])->asArray()->all();
+//    $data3 = $db::getRows("SELECT * FROM store_planogram WHERE store_id=? AND color=''", [$store_id]);
     foreach ($data3 as $row2) {
         $values_in[$row2["product_id"]]["quantity"] = $row2["quantity"];
         $values_in[$row2["product_id"]]["quantity_max"] = $row2["quantity_max"];
 
     }
 
-    $data3 = $db::getRows("SELECT * FROM store_planogram WHERE store_id=? AND color!=''", [$store_id]);
+    $data3 = StorePlanogram::find()->where(['store_id' => $store_id])->andWhere(['!=', 'color', ''])->asArray()->all();
+//    $data3 = $db::getRows("SELECT * FROM store_planogram WHERE store_id=? AND color!=''", [$store_id]);
     foreach ($data3 as $row2) {
         $colors_in[$row2["product_id"]][$row2["color"]] = $row2["quantity"];
         $colors_in_max[$row2["product_id"]][$row2["color"]] = $row2["quantity_max"];
@@ -202,7 +268,9 @@ if (isset($store_id)) {
 
 $colorSort=[];
 if (isset($store_id)) {
-    $data = $db::getRows("SELECT product_id,color,posit FROM store_planogram_colors_sort WHERE store_id=?  order by product_id ASC, posit ASC", [$store_id]);
+    $data = StorePlanogramColorsSort::find()->select(['product_id', 'color', 'posit'])->where(['store_id' => $store_id])
+        ->orderBy(['product_id' => SORT_ASC, 'posit' => SORT_ASC])->asArray()->all();
+//    $data = $db::getRows("SELECT product_id,color,posit FROM store_planogram_colors_sort WHERE store_id=?  order by product_id ASC, posit ASC", [$store_id]);
     foreach ($data as $row) $colorSort[$row["product_id"]][] = $row["color"];
 }
 
@@ -218,18 +286,28 @@ echo 'Память для скрипта: ' . ini_get('memory_limit') . '<br>';
 
 $providers[0]="-без поставщика-";
 
-
-$data=$db::getRows("SELECT p.id, p.name, o.provider_id, o.colors,p.view  FROM products_1c_options as o, products_1c as p  WHERE
-p.id=o.id AND o.provider_id>0 order by o.provider_id ASC, p.view DESC, p.name ASC");
-
-
-$products_group=$db::mapping("SELECT id, name FROM products_1c   WHERE tip='products_group'");
-
-$data2=$db::getRows("SELECT p.id, p.name, p.parent_id,  o.provider_id, o.colors, p.parent_id as category_id FROM
-   products_class as cl
-  LEFT JOIN products_1c as p    ON (p.view='1' AND p.parent_id=cl.category_id ) 
-   LEFT  JOIN products_1c_options as o ON (p.id=o.id) 
-WHERE cl.tip='related' group by p.id order by o.provider_id, p.parent_id, p.name ASC");
+$data = Products1cOptions::find()->alias('o')->select(['p.id', 'p.name', 'o.provider_id', 'o.colors', 'p.view'])
+    ->innerJoin('products_1c p', 'p.id = o.id AND o.provider_id > 0')
+    ->orderBy(['o.provider_id' => SORT_ASC, 'p.view' => SORT_DESC, 'p.name' => SORT_ASC])->asArray()->all();
+//$data=$db::getRows("SELECT p.id, p.name, o.provider_id, o.colors,p.view  FROM products_1c_options as o, products_1c as p  WHERE
+//p.id=o.id AND o.provider_id>0 order by o.provider_id ASC, p.view DESC, p.name ASC");
+
+
+$products_group = yii\helpers\ArrayHelper::map(\yii_app\records\Products1c::find()->select(['name', 'id'])->where(['tip' => 'products_group'])->all(), 'id', 'name');
+//$products_group=$db::mapping("SELECT id, name FROM products_1c   WHERE tip='products_group'");
+
+$data2 = ProductsClass::find()->alias('cl')->select(['p.id', 'p.name', 'p.parent_id',  'o.provider_id', 'o.colors', 'p.parent_id as category_id'])
+    ->leftJoin('products_1c p', 'p.view =1 AND p.parent_id=cl.category_id')
+    ->leftJoin('products_1c_options o', 'p.id = o.id')
+    ->where(['cl.tip' => 'related'])
+    ->groupBy(['p.id'])
+    ->orderBy(['o.provider_id' => SORT_DESC, 'p.parent_id' => SORT_DESC, 'p.name' => SORT_ASC])
+    ->asArray()->all();
+//$data2=$db::getRows("SELECT p.id, p.name, p.parent_id,  o.provider_id, o.colors, p.parent_id as category_id FROM
+//   products_class as cl
+//  LEFT JOIN products_1c as p    ON (p.view='1' AND p.parent_id=cl.category_id )
+//   LEFT  JOIN products_1c_options as o ON (p.id=o.id)
+//WHERE cl.tip='related' group by p.id order by o.provider_id, p.parent_id, p.name ASC");
 $data = array_merge($data, $data2);
 
 
-- 
2.39.5