-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Versione server:              5.7.24-log - MySQL Community Server (GPL)
-- S.O. server:                  Win64
-- HeidiSQL Versione:            11.2.0.6213
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dump della struttura di vista v_box_do
-- Creazione di una tabella temporanea per risolvere gli errori di dipendenza della vista
CREATE TABLE `v_box_do` (
	`id` INT(11) NOT NULL,
	`item_id` INT(11) NOT NULL,
	`quantity` INT(11) NOT NULL,
	`creator_id` INT(11) NOT NULL,
	`creation_date` DATETIME NOT NULL,
	`modify_id` INT(11) NOT NULL,
	`modify_date`  DATETIME NOT NULL,
	`trasporti_id` INT(11) NOT NULL,
	`trasporti_prog` INT(11) NOT NULL
) ENGINE=MyISAM;

-- Dump della struttura di vista v_box_do
-- Rimozione temporanea di tabella e creazione della struttura finale della vista
DROP TABLE IF EXISTS `v_box_do`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_box_do` 
AS 

SELECT 
    r.id, r.item_id, r.quantity, r.creator_id, r.creation_date, r.modify_id, r.modify_date, r.trasporti_id, r.trasporti_prog 
FROM (
    SELECT
          id        
        , qty AS quantity
        , item_id
        , trasporti_id
        , creator_id, creation_date, modify_id, modify_date
        , CASE agg WHEN 
            @curType THEN @curRow := @curRow + 1
          ELSE 
            @curRow := 1 
          END AS trasporti_prog
        , @curType := agg 
    FROM (
        SELECT
              MIN(id) AS id 
            , COUNT(id) AS qty
            , item_id
            , trasporti_id
            , uid
            , CONCAT(item_id, '-', trasporti_id) AS agg 
            , MIN(creator_id) AS creator_id
            , MIN(creation_date) AS creation_date
            , MIN(modify_id) AS modify_id
            , MIN(modify_date) AS modify_date
        FROM 
            soluzioni_salvate 
        WHERE 
            tipo_testata = 'DO' 
        GROUP BY
            item_id, trasporti_id, uid
    ) AS p 
        JOIN (SELECT @curRow := 0, @curType := '') AS q 
    ORDER BY agg
) AS r;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;






