sql Archivi - Petar Karan

11 Maggio 2020 1 commento

[mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali)

Con il MySQL è possibile generare tabelle pivot dinamiche, ovvero che contengano nomi di colonne automatiche, in base ad una tabella di riferimento.

Quello che vogliamo ottenere sarà un risultato come il seguente:

Prima di cominciare creiamo un database con un po’ di dati in modo da poterci lavorare.

1. Creazione dati di partenza

Creiamo 3 tabelle: prodotti, agenti e vendite. Di seguito riporto le query per creare le singole tabelle ed inserire i dati di esempio.

PRODOTTI

Per creare la tabella prodotti eseguiamo la seguente query.

CREATE TABLE `prodotti` (
  `id` int(255) NOT NULL,
  `nome` varchar(1024) NOT NULL,
  `prezzo_unitario` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `prodotti` (

`id` int(255) NOT NULL,

`nome` varchar(1024) NOT NULL,

`prezzo_unitario` double NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inseriamo i dati nel modo seguente:

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES
(1, 'Mele', 600),
(2, 'Pere', 900),
(3, 'Banane', 750),
(4, 'Pesche', 1100),
(5, 'Kiwi', 1200);

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES

(1, 'Mele', 600),

(2, 'Pere', 900),

(3, 'Banane', 750),

(4, 'Pesche', 1100),

(5, 'Kiwi', 1200);

AGENTI

Andiamo adesso a creare la tabella degli agenti, in modo analogo:

CREATE TABLE `agenti` (
  `id` int(255) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `cognome` varchar(100) NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `agenti` (

`id` int(255) NOT NULL,

`nome` varchar(100) NOT NULL,

`cognome` varchar(100) NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ed inseriamo i dati:

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES
(1, 'Mario', 'Rossi', 1),
(2, 'Luigi', 'Verdi', 1),
(3, 'Luisa', 'Neri', 1),
(4, 'Chiara', 'Celesti', 1),
(5, 'Paolo', 'Bianchi', 1),
(6, 'Anna', 'Rossi', 1);

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES

(1, 'Mario', 'Rossi', 1),

(2, 'Luigi', 'Verdi', 1),

(3, 'Luisa', 'Neri', 1),

(4, 'Chiara', 'Celesti', 1),

(5, 'Paolo', 'Bianchi', 1),

(6, 'Anna', 'Rossi', 1);

VENDITE

Per le vendite andremo a creare la tabella in questo modo:

CREATE TABLE `vendite` (
  `id` int(255) NOT NULL,
  `idAgente` int(255) NOT NULL,
  `idProdotto` int(255) NOT NULL,
  `data` int(11) NOT NULL,
  `qta` int(255) NOT NULL DEFAULT '0',
  `importo` double NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vendite` (

`id` int(255) NOT NULL,

`idAgente` int(255) NOT NULL,

`idProdotto` int(255) NOT NULL,

`data` int(11) NOT NULL,

`qta` int(255) NOT NULL DEFAULT '0',

`importo` double NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A differenza delle precedenti due, qui voglio inserire dei dati casuali. Per inserire una riga di dati casuali, pescati dalle precedenti due tabelle, potremmo scrivere:

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

1 2	INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()4,0),ROUND(1+RAND()4,0),UNIX_TIMESTAMP()-ROUND(RAND()365360024,0),ROUND(RAND()10000,2),1);

Faccio notare che con l’istruzione ROUND(1+RAND()*4,0) inseriamo un valore intero casuale tra 1 e 5. Se volessimo generalizzare il procedimento potremmo scrivere ROUND(n+RAND()*(m-n),0) dove n è il minimo, mentre m è il massimo.

Con n = 3 e m = 8 avremmo infatti le seguenti operazioni:

RAND() genera un valore casuale decimale da 0 a 1
Moltiplicando per (8-3) il valore casuale sarà tra 0 e 5.
Sommano il risultato a 3 il valore casuale sarà un decimale tra 3 e 8. Potrebbe essere per esempio 6,4.
Usando ROUND() arrotondiamo a 0 cifre decimali ottenendo quindi un intero.

Se volessimo inserire più di una riga in una singola operazione, possiamo farlo scrivendo una procedura.

Dichiariamo quindi una procedura nel modo seguente, che inserisca 1000 valore casuali per volta.

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()
BEGIN
	DECLARE tot INT UNSIGNED DEFAULT 0;
    WHILE tot <= 1000 DO
		INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
                VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);
        SET tot = tot + 1;
	END WHILE;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()

BEGIN

DECLARE tot INT UNSIGNED DEFAULT 0;

WHILE tot <= 1000 DO

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`)

VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

SET tot = tot + 1;

END WHILE;

END//

DELIMITER ;

Chiamiamo la procedura digitando:

CALL inserimenti_casuali();

1	CALL inserimenti_casuali();

2. Creazione tabella pivot

Per creare una tabella pivot manualmente utilizziamo CASE WHEN ... END. A mano potremmo scrivere una query come quella di seguito:

SELECT 
	a.nome, a.cognome,
	ROUND(SUM(v.importo),2) AS totale,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 1
			THEN v.importo
			ELSE 0
		END
	),2) AS mele,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 2
			THEN v.importo
			ELSE 0
		END
	),2) AS pere
FROM
	agenti a LEFT JOIN vendite v ON v.idAgente = a.id
GROUP BY
	a.id

SELECT

a.nome, a.cognome,

ROUND(SUM(v.importo),2) AS totale,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS pere

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

Ogni colonna della pivot sarà generata da:

ROUND(SUM(
	CASE
		WHEN v.idProdotto = 1
		THEN v.importo
		ELSE 0
	END
),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

In questo caso noi abbiamo una tabella dei prodotti, dove sappiamo esserci all’id = 1 il prodotto chiamato mele.

Con SUM() sommiamo i valori (avremmo potuto usare COUNT(), AVG(), MAX(), MIN() ecc.) e con ROUND() arrotondiamo. Quest’ultima funzione in particolare non sarebbe necessaria, ma la utilizzo solo perché, visto che si parla di importi in euro, non ha senso calcolare un risultato al di sotto dei centesimi.

Quello che vogliamo fare ora è poter scrivere, quel pezzo di query, in modo iterativo, ripetendola per CIASCUN valore della tabella prodotti.

In particolare potremmo eseguire una query come la seguente:

SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
        	'ROUND(SUM(
                CASE
                    WHEN v.idProdotto = ', p.id,'
                    THEN v.importo
                    ELSE 0
                END
            ),2) AS `', p.nome,'` '
        )
    )
FROM
	prodotti p;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

FROM

prodotti p;

Il risultato di questa query sarebbe un singolo campo contenente i seguenti valori:

ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 1
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Mele` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 2
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pere` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 3
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Banane` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 4
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pesche` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 5
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Kiwi`

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS `Mele` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS `Pere` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 3

THEN v.importo

ELSE 0

END

),2) AS `Banane` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 4

THEN v.importo

ELSE 0

END

),2) AS `Pesche` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 5

THEN v.importo

ELSE 0

END

),2) AS `Kiwi`

Faccio notare che grazie a GROUP_CONCAT() vengono inserite le virgole tra le singole righe.

Andiamo a creare quindi una procedura come la seguente:

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()
BEGIN
	SET @sql = NULL;
	SELECT
		GROUP_CONCAT(DISTINCT
			CONCAT(
				'ROUND(SUM(
					CASE
						WHEN v.idProdotto = ', p.id,'
						THEN v.importo
						ELSE 0
					END
				),2) AS `', p.nome,'` '
			)
		)
	INTO @sql
	FROM
		prodotti p;
		
	SET @sql = CONCAT('
		SELECT 
			a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
			', @sql, '
		FROM
			agenti a LEFT JOIN vendite v ON v.idAgente = a.id
		GROUP BY
			a.id
	');

	PREPARE esecuzione FROM @sql;
	EXECUTE esecuzione;
	DEALLOCATE PREPARE esecuzione;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()

BEGIN

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

END//

DELIMITER ;

Eseguiamo, come prima, la procedura:

CALL pivot_vendite_casuali();

1	CALL pivot_vendite_casuali();

Otterremo il risultato cercato.

Dobbiamo usare una procedura per evitare di incorrere in una serie di incongruenze legate all’interrogazione del database.

DIfatti, usando il PHPMyAdmin e lanciando solamente l’istruzione:

SET @sql = NULL;
SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'ROUND(SUM(
				CASE
					WHEN v.idProdotto = ', p.id,'
					THEN v.importo
					ELSE 0
				END
			),2) AS `', p.nome,'` '
		)
	)
INTO @sql
FROM
	prodotti p;
	
SET @sql = CONCAT('
	SELECT 
		a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
		', @sql, '
	FROM
		agenti a LEFT JOIN vendite v ON v.idAgente = a.id
	GROUP BY
		a.id
');

PREPARE esecuzione FROM @sql;
EXECUTE esecuzione;
DEALLOCATE PREPARE esecuzione;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

Si incorrere nel seguente errore:

Fatal error: Uncaught Error: Call to a member function getClauses() on null in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php:564 Stack trace: #0 D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php(681): PhpMyAdmin\SqlParser\Utils\Query::getClause(NULL, NULL, 'ORDER BY', -1, false) #1 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(1385): PhpMyAdmin\SqlParser\Utils\Query::replaceClause(NULL, NULL, 'ORDER BY', '') #2 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(4376): PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(Array, '') #3 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1689): PMA\libraries\DisplayResults->getTable(Object(mysqli_result), Array, Array, false) #4 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1980): PMA_getHtmlForSqlQueryResultsTable(Object(PMA\libraries\DisplayResults), './themes/pmahom...', NULL, Array, false, 6, 6, NULL, Object(mysqli_result), Array) #5 D:\xampp\phpMyAdmin\libraries\sql.lib.php(2199): PMA_getQueryResponseForResultsReturned(Object(my in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php on line 564

3. Utilizzo della tabella nel PHP

Leggiamo la tabella tramite il PHP. Per farlo utilizzerò la classe MySQLdb come in questo esercizio sul MySQL Cluster.

Creiamo nella root del nostro sito un file MySQLdb.php contenente il seguente codice:

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");


class MySQLdb {

	private $mysql = NULL;

	function __construct($cfg = NULL) {
	
		if( $cfg != NULL ) {
			
			$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);
			
			$this->connect_db();
			
		}
	
	}
	
	function __destruct() {
	
		$this->close_db();
	
	}
	
	function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {
	
		$this->dbname = $dbname;
		$this->dbhost = $dbhost;
		$this->dbuser = $dbuser;
		$this->dbport = $dbport;
		$this->dbpass = $dbpass;
	
	}
	
	function select_db($dbname = NULL) {
	
		$this->mysql->select_db($dbname ? $dbname : $this->dbname);
	
	}
	
	function connect_db() {
	
		if( $this->mysql == NULL ) {
		
			$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);
			$this->select_db();
			$this->errorHandler();
		
		}
	
	}
	
	function close_db() {
	
		if( $this->mysql != NULL ) {
		
			$this->mysql->close();
			$this->mysql = NULL;
		
		}
	
	}
	
	function errorHandler() {
	
		if( $this->mysql->connect_errno ) {
		
			printf( "Connect failed: %s\n", $this->mysql->connect_error );
			die();
			
		}
	
	}
	
	function clean( $val ) {
		return $this->mysql->real_escape_string($val);
	}
	
	function in( $query ) {
	
		$status = $this->mysql->query( $query );
		
		if( $this->mysql->insert_id ) 
			return $this->mysql->insert_id;
		else 
			return $status;
	
	}
	
	function out( $query , $autoReduce = true ) {
	
		$result = $this->mysql->query( $query );
			
		if( !is_object($result) )
			return array();
		
		while ($linea = $result->fetch_assoc()) {
		
			$i = $i !== NULL ? $i + 1 : 0;
			
			foreach( $linea as $key => $value ) {
				$dati[$i][$key] = $value;
			}
			
		}
		
		$result->close();
		
		if( count($dati) == 1 && $autoReduce )
			return $dati[0];
		
		return $dati;
	
	}


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");

class MySQLdb {

private $mysql = NULL;

function __construct($cfg = NULL) {

if( $cfg != NULL ) {

$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);

$this->connect_db();

}

function __destruct() {

$this->close_db();

}

function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {

$this->dbname = $dbname;

$this->dbhost = $dbhost;

$this->dbuser = $dbuser;

$this->dbport = $dbport;

$this->dbpass = $dbpass;

}

function select_db($dbname = NULL) {

$this->mysql->select_db($dbname ? $dbname : $this->dbname);

}

function connect_db() {

if( $this->mysql == NULL ) {

$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);

$this->select_db();

$this->errorHandler();

}

function close_db() {

if( $this->mysql != NULL ) {

$this->mysql->close();

$this->mysql = NULL;

}

function errorHandler() {

if( $this->mysql->connect_errno ) {

printf( "Connect failed: %s\n", $this->mysql->connect_error );

die();

}

function clean( $val ) {

return $this->mysql->real_escape_string($val);

}

function in( $query ) {

$status = $this->mysql->query( $query );

if( $this->mysql->insert_id )

return $this->mysql->insert_id;

else

return $status;

}

function out( $query , $autoReduce = true ) {

$result = $this->mysql->query( $query );

if( !is_object($result) )

return array();

while ($linea = $result->fetch_assoc()) {

$i = $i !== NULL ? $i + 1 : 0;

foreach( $linea as $key => $value ) {

$dati[$i][$key] = $value;

}

$result->close();

if( count($dati) == 1 && $autoReduce )

return $dati[0];

return $dati;

}

Creiamo adesso un file index.php, nella stessa posizione, contenente il seguente codice:

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(
	"dbname" => "esempio_pivot",
	"dbuser" => "root",
	"dbhost" => "127.0.0.1",
	"dbpass" => "",
	"dbport" => 3306
));

$prodotti = $mysql->out("SELECT * FROM prodotti");
$dati = $mysql->out("CALL pivot_vendite_casuali()");

?>
<table>
	<thead>
		<tr>
			<th>Nome</th>
			<th>Cognome</th>
			<th>Totale</th>
			<?php foreach( $prodotti as $p ) { ?>
			<th><?php echo $p['nome'] ?></th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
		<?php foreach( $dati as $d ) { ?>
		<tr>
			<td><?php echo $d['nome'] ?></td>
			<td><?php echo $d['cognome'] ?></td>
			<td><?php echo $d['totale'] ?></td>
			<?php foreach( $prodotti as $p ) { ?>
			<td><?php echo $d[$p['nome']] ?></td>
			<?php } ?>
		</tr>
		<?php } ?>
	</tbody>
</table>

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(

"dbname" => "esempio_pivot",

"dbuser" => "root",

"dbhost" => "127.0.0.1",

"dbpass" => "",

"dbport" => 3306

));

$prodotti = $mysql->out("SELECT * FROM prodotti");

$dati = $mysql->out("CALL pivot_vendite_casuali()");

<table>

<thead>

<tr>

<th>Cognome</th>

<th>Totale</th>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

</thead>

<tbody>

<?php foreach( $dati as $d ) { ?>

<tr>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

<?php } ?>

</tbody>

</table>

Vediamo in particolare come nel vettore $prodotti abbiamo l’elenco di tutti i prodotti e quindi delle colonne della pivot, che si trovano in $dati. Il risultato che otterremo sarà simile a questo:

Vedi articolo

28 Novembre 2018 Nessun commento

[magento] Errore SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0009032’ for key ‘UNQ_SALES_FLAT_ORDER_INCREMENT_ID’

Soluzione1: Questo errore si presenta quando si effettua un ordine su Magento, si viene indirizzati al pagamento, ma il pagamento non va a buon fine e si viene rimandati indietro sul negozio. Per risolvere questo problema è sufficiente modificare il file app/code/core/Mage/Sales/Model/Resource/Quote.php come indicato di seguito

Per risolvere il problema posizioniamoci nella cartella principale del negozio in Magento. Dopodiché andiamo a modificare il file app/code/core/Mage/Sales/Model/Resource/Quote.php

Apriamolo e cerchiamo la seguente parte di codice

    public function isOrderIncrementIdUsed($orderIncrementId)
    {
        $adapter   = $this->_getReadAdapter();
        $bind      = array(':increment_id' => (int)$orderIncrementId);
        $select    = $adapter->select();
        $select->from($this->getTable('sales/order'), 'entity_id')
            ->where('increment_id = :increment_id');
        $entity_id = $adapter->fetchOne($select, $bind);
        if ($entity_id > 0) {
            return true;
    }

        return false;
    }

public function isOrderIncrementIdUsed($orderIncrementId)

{

$adapter = $this->_getReadAdapter();

$bind = array(':increment_id' => (int)$orderIncrementId);

$select = $adapter->select();

$select->from($this->getTable('sales/order'), 'entity_id')

->where('increment_id = :increment_id');

$entity_id = $adapter->fetchOne($select, $bind);

if ($entity_id > 0) {

return true;

}

return false;

}

Alla linea quattro modifichiamo

$bind      = array(':increment_id' => (int)$orderIncrementId);

1	$bind = array(':increment_id' => (int)$orderIncrementId);

In modo che diventi:

$bind      = array(':increment_id' => $orderIncrementId);

1	$bind = array(':increment_id' => $orderIncrementId);

Salviamo il tutto.

Soluzione2: Questo errore si presenta quando per qualche ragione sono stati inseriti ordini sopra l’ultimo incrementale registrato in eav_entity_type. Per risolvere il problema è sufficiente incrementare sopra l’ultimo valore registrato il valore dell’incrementale.

Di seguito discutiamo il problema e la soluzione nel dettaglio.

Anzitutto l’errore che si presenta è simile a questo:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0009032' for key 'UNQ_SALES_FLAT_ORDER_INCREMENT_ID', query was: INSERT INTO sales_flat_order (coupon_code, protect_code, shipping_description, is_virtual, store_id, customer_id, base_discount_amount, base_grand_total, base_shipping_amount, base_shipping_tax_amount, base_subtotal, base_tax_amount, base_to_global_rate, base_to_order_rate, discount_amount, grand_total, shipping_amount, shipping_tax_amount, store_to_base_rate, store_to_order_rate, subtotal, tax_amount, total_qty_ordered, customer_is_guest, customer_note_notify, customer_group_id, quote_id, base_shipping_discount_amount, base_subtotal_incl_tax, shipping_discount_amount, subtotal_incl_tax, weight, customer_dob, increment_id, applied_rule_ids, base_currency_code, customer_email, customer_firstname, customer_lastname, customer_middlename, customer_prefix, customer_suffix, customer_taxvat, discount_description, global_currency_code, order_currency_code, remote_ip, shipping_method, store_currency_code, store_name, x_forwarded_for, customer_note, created_at, updated_at, total_item_count, customer_gender, hidden_tax_amount, base_hidden_tax_amount, shipping_hidden_tax_amount, base_shipping_hidden_tax_amnt, shipping_incl_tax, base_shipping_incl_tax, gift_message_id, payment_fee_amount, base_payment_fee_amount, payment_installment_fee_amount, base_payment_installment_fee_amount, payment_tax_amount, base_payment_tax_amount, referral_code, ebizmarts_abandonedcart_flag, payment_fee_tax, base_payment_fee_tax, payment_percentage_fee, base_payment_percentage_fee) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '1975-10-01 00:00:00', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '2018-11-28 11:14:46', '2018-11-28 11:14:46', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Questo errore si presenta perché si sta cercando di inserire una chiave duplicata in sales_flat_order. Prima di tutto verifichiamo la cosa spostandoci in sales_flat_order ed effettuando la seguente query:

SELECT MAX(`entity_id`) FROM `sales_flat_order`

1	SELECT MAX(`entity_id`) FROM `sales_flat_order`

Nel mio caso il risultato della query era 10.009. Adesso spostiamoci su eav_entity_store. Qui dovremmo vedere una tabella con tutti gli incrementali. Cerchiamo quello che ci interessa. Per trovarlo guardiamo i campi entity_type_id e store_id. Gli entity_type_id hanno come riferimento la tabella eav_entity_type.

Per esempio il codice 5 corrisponde agli ordini, mentre il codice 6 alle fatture/ordini.

Nel mio caso mi accorgo che increment_last_id è su 9033, mentre il massimo valore inserito in sales_flat_order è di 10.009. Quindi mi è sufficiente portare questo valore al di sopra di quello attuale, per esempio impostandolo su 10.010.

Una volta fatto il problema è risolto.

Vedi articolo

2 Marzo 2018 Nessun commento

[vba] Classe per interrogare in SQL un foglio excel

Obiettivo: creare una classe per poter interrogare tramite query SQL un foglio excel, leggendo e scrivendo i dati come se lavorassimo con tabelle SQL

Questo esercizio riprende il punto direttamente da questo precedente: Interrogare foglio di Excel con SQL in VBA

Dopo aver visto come poter interrogare con query SQL un foglio excel, vediamo come creare una classe ad hoc che ci permetta di leggere e scrivere i dati mediante query SQL. Per farlo anzitutto aggiungiamo una nuova classe:

Premendo ALT+F11 apriamo l’editor Visual Basic, ci posizioniamo su un qualunque punto del progetto a sinistra e clicchiamo col destro (per esempio su Form), poi scegliamo Inserisci ⇒ Modulo classe. A questo punto verrà creata una nuova classe. Per rinominarla è sufficiente selezionarla e poi spostarsi nel riquadro delle proprietà.

A questo punto incolliamo dentro la classe appena creata (tenendo presente poi il nome scelto) il seguente codice:

' funzione in input per il database, restituisce un vettore variant di dimensione n x m, dove
' n è il numero di righe lette e m il numero di colonne per riga
Public Function dbout(ByVal query As String) As Variant

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strFileName As String
    Dim ConnectionString As String
    Dim pos As Integer
    
    ' creiamo il vettore con il risultato
    Dim result() As Variant
    
    strFileName = ThisWorkbook.FullName
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & strFileName & ";Extended Properties=""Excel 12.0 xml;HDR=YES;IMEX=1;Readonly=True"";"
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open ConnectionString
        
    rs.Open query, conn
    
    ' adesso dobbiamo anzitutto contare la dimensione del risultato
    righe = 0
    colonne = 0
    
    If Not rs.EOF Then
        Do
            righe = righe + 1
            colonne = rs.Fields.Count
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    ' riposizioniamo il recordset alla partenza
    rs.MoveFirst
    
    ' ridimensioniamo il vettore risultato sulle righe e colonne appena contate
    ReDim result(righe, colonne)
    
    ' leggiamo tutti i dati ed inseriamoli nel vettore
    righe = 0
    If Not rs.EOF Then
        Do
            For j = 0 To rs.Fields.Count - 1
                result(righe, j) = rs(j)
            Next j
            righe = righe + 1
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    rs.Close
    
    conn.Close
    
    ' restituiamo il vettore
    dbout = result

End Function

' questo metodo accetta in input la query e la esegue sul foglio prescelto
Public Function dbin(ByVal query As String)

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strFileName As String
    Dim ConnectionString As String
    
    strFileName = ThisWorkbook.FullName
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & strFileName & ";Extended Properties=""Excel 12.0 xml;"";"
    
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open ConnectionString
    
    ' eseguiamo la query
    conn.Execute query
    
    conn.Close

End Function

' funzione in input per il database, restituisce un vettore variant di dimensione n x m, dove

' n è il numero di righe lette e m il numero di colonne per riga

Public Function dbout(ByVal query As String) As Variant

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strFileName As String

Dim ConnectionString As String

Dim pos As Integer

' creiamo il vettore con il risultato

Dim result() As Variant

strFileName = ThisWorkbook.FullName

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _

& strFileName & ";Extended Properties=""Excel 12.0 xml;HDR=YES;IMEX=1;Readonly=True"";"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open ConnectionString

rs.Open query, conn

' adesso dobbiamo anzitutto contare la dimensione del risultato

righe = 0

colonne = 0

If Not rs.EOF Then

righe = righe + 1

colonne = rs.Fields.Count

rs.MoveNext

Loop Until rs.EOF

End If

' riposizioniamo il recordset alla partenza

rs.MoveFirst

' ridimensioniamo il vettore risultato sulle righe e colonne appena contate

ReDim result(righe, colonne)

' leggiamo tutti i dati ed inseriamoli nel vettore

righe = 0

If Not rs.EOF Then

For j = 0 To rs.Fields.Count - 1

result(righe, j) = rs(j)

Next j

righe = righe + 1

rs.MoveNext

Loop Until rs.EOF

End If

rs.Close

conn.Close

' restituiamo il vettore

dbout = result

End Function

' questo metodo accetta in input la query e la esegue sul foglio prescelto

Public Function dbin(ByVal query As String)

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strFileName As String

Dim ConnectionString As String

strFileName = ThisWorkbook.FullName

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _

& strFileName & ";Extended Properties=""Excel 12.0 xml;"";"

Set conn = CreateObject("ADODB.Connection")

conn.Open ConnectionString

' eseguiamo la query

conn.Execute query

conn.Close

End Function

Faccio notare come nel metodo dbout dobbiamo fare un’operazione un po’ superflua in apparenza, ovvero contare prima le righe e le colonne, per poi ridimensionare il vettore result. Questa operazione è necessaria perché ReDim, in VBA, non permette di ridimensionare l’intera matrice (vettore di vettori), ma solo l’ultima dimensione. Quindi, per esempio, result(4,10) potrebbe diventare result(4,20), ma non result(5,10) oppure result(5,20). Un’alternativa sarebbe quella di creare una funzione che trasponga il vettore e lo modifichi, ritrasponendolo di nuovo, ma dal punto di vista del calcolo credo sarebbe molto più oneroso che fare così.

Fatto questo potremo utilizzare la nostra nuova classe nella maniera seguente.

' inserimento
Dim query As String
query = "INSERT INTO [DATI$] (ID,Nome,Città) Values (1,'Mario','Palermo')"
Dim db As New DbSQL
db.dbin query

' lettura
query = "SELECT * FROM [DATI$A:C]"
Dim db As New DbSQL
Dim righe As Variant
righe = db.dbout(query)
For i = 0 To UBound(righe, 1) - 1
    Debuig.Print righe(i, 1) ' 1 = prima colonna, 2 = seconda colonna, ecc.
Next i

' inserimento

Dim query As String

query = "INSERT INTO [DATI$] (ID,Nome,Città) Values (1,'Mario','Palermo')"

Dim db As New DbSQL

db.dbin query

' lettura

query = "SELECT * FROM [DATI$A:C]"

Dim db As New DbSQL

Dim righe As Variant

righe = db.dbout(query)

For i = 0 To UBound(righe, 1) - 1

Debuig.Print righe(i, 1) ' 1 = prima colonna, 2 = seconda colonna, ecc.

Next i

Una volta creata la classe può essere esportata, oppure importata nel modo seguente:

Per chi volesse è possibile scaricare la classe già pronta cliccando qui: DbSQL.zip

Hash MD5: A2A18A42BB11AEA86DF1AB4F547B4414

La classe è stata testata su Excel 2016.

Vedi articolo