paolo@bimodesign.com | +34 608 61 64 10

E-Commerce

        

Couponic - Generare numeri di fattura (Bill) - Parte II

In questo articolo vi illustro una maniera diversa di generare le fatture per Couponic, aggiungendo inoltre la creazione di una procedure MySql nel caso in ci sia bisogno di generare i codici pregressi

A differenza dell'articolo precedente, questa volta il risultato sara', leggermente più completto, perche' le fatture avranno questo formato

A0000001/12

Dove "A" cambiera' alla lettera successiva dell'alfabeto, quando si arrivera' al numero 9999999 e si riazzera sia la lettera che il numero, ripartendo da A0000001 quando cambia l'anno

Lascio inalterata la struttura delle tabelle, anche se, aggiungendo il campo anno, sarebbe piu' semplice gestire poi la stored procedure e il trigger

La tabella

drop table cpns_PaymentBill;

CREATE TABLE  `cpns_PaymentBill` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `orderId` bigint(20) NOT NULL,
  `billNumber` varchar(8) NOT NULL,
  PRIMARY KEY (`id`,`orderId`)
) ENGINE=MyISAM;

La Stored Procedure

DELIMITER $$

CREATE PROCEDURE crearBillRegreso()
BEGIN
   DECLARE billnumber INTEGER(11);
   DECLARE billnumbernew VARCHAR(11);
   DECLARE billnumberfirstletter CHAR(1);
   DECLARE iId BIGINT(20) unsigned;
   DECLARE done INT DEFAULT FALSE;

   DECLARE cPaymentOrder CURSOR FOR
   SELECT `id` 
     FROM `cpns_PaymentOrder`
    WHERE `status` = 2;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   SET billnumber = 0;
   SET billnumberfirstletter = 'A';

   OPEN cPaymentOrder;

   read_loop: LOOP
   FETCH cPaymentOrder into iId;
    SET billnumber = billnumber+1;

    IF done THEN
          LEAVE read_loop;
    END IF;

    SET billnumbernew = CONCAT(billnumberfirstletter,LPAD(billnumber,7,'0'),'/12');

        INSERT INTO `cpns_PaymentBill` (`orderId`,`billNumber`) VALUES (iId, billnumbernew);

   END LOOP;

   CLOSE cPaymentOrder;
END$$
delimiter ;

Questo codice e' piuttosto semplice. Tra l'altro, visto che va eseguita solo una volta, e sapendo che tutte le fatture sono state emesse per l'anno in corso (nel mio caso il 2012), non c'e' bisogno di gestire l'anno.

Il Trigger

Dopo aver creato ed eseguito la Stored Procedure, creiamo il trigger che si attivera' quando il campo cpns_PaymentOrder.status cambia a "2". Da notare che tale campo puo' assumere vari valori, ma solo questo indica che il pagamento, sia con Paypal o con Carta di Credito (nel mio caso Adyen) e' andato a buon fine.

DROP TRIGGER IF EXISTS createbill;

DELIMITER $$
    CREATE TRIGGER createbill BEFORE UPDATE ON cpns_PaymentOrder
    FOR EACH ROW BEGIN

      DECLARE billnumberactual VARCHAR(11);
      DECLARE billnumberactualfirstletter CHAR(1);
      DECLARE billnumberactualnumero CHAR(7);
      DECLARE billnumberactualyear CHAR(2);
      DECLARE actualyear VARCHAR(2);
      DECLARE separador CHAR(1);

      DECLARE billnumbernew VARCHAR(11);

      if(NEW.status = 2) THEN
          SET billnumberactual = (select billNumber from cpns_PaymentBill ORDER BY id desc limit 0,1); 

          SET separador        = '/';
              
          SET billnumberactualfirstletter = SUBSTRING(billnumberactual,1,1);
          SET billnumberactualnumero      = SUBSTRING(billnumberactual,2,7);
          SET billnumberactualyear        = SUBSTRING(billnumberactual,10,2);

          SET actualyear                  = DATE_FORMAT(CURDATE(),'%y');

              if(billnumberactualyear = actualyear) THEN
                  if(billnumberactualnumero='9999999') THEN
                    SET billnumberactualfirstletter = CHAR(ORD(billnumberactualfirstletter)+1);
                    SET billnumberactualnumero      = LPAD('1',7,'0');
                    SET billnumberactualyear        = actualyear;
                  ELSE
                    SET billnumberactualnumero  = LPAD(1 + billnumberactualnumero,7,'0');
                  END IF;

                  SET billnumbernew = CONCAT(billnumberactualfirstletter,billnumberactualnumero,'/', billnumberactualyear);
              ELSE
                  SET billnumbernew = CONCAT('A0000001','/', actualyear);
              END IF;

          INSERT INTO cpns_PaymentBill (orderId,billNumber) VALUES (NEW.id, billnumbernew);
      END IF;

          
    END;
$$
DELIMITER ;

Rispetto alla versione precedente, oltre a mantenere inalterato il calcolo della lettera successiva a quella attuale, ovvero questa istruzione

CHAR(ORD(billnumberactualfirstletter)+1);

occorre gestire il cambio dell'anno e quindi la corretta sequenza delle fatture.
Se usiamo un ORDER BY sul numero della fattura e, se immaginiamo sia il 2013 e abbiamo gia' fatture del 2012 e che l'ultima sia ad esempio C0000123/12, allora verrebbe estratta proprio questa come ultima, rispetto ad esempio a A1234567/13. E chiaramwnte e' sbagliato
Come scritto in precedenza, aggiungere un campo che contiene le ultime due cifre dell'anno in corso, sarebbe la soluzione migliore, ma visto che non potevo modificare la tabella, ho trovato queste due possibili soluzioni.
La prima e' quella che poi ho utilizzato, ovvero ordinare per ID di inserimento, anche se questo potrebbe comportare problemi nel caso di export/import dei dati, mentre l'altra sarebbe questa:

SELECT MAX(CONCAT(SUBSTRING(billNumber,10,2), SUBSTRING(billNumber,1,1) , SUBSTRING(billNumber,2,7)))
FROM cpns_PaymentBill;

che per questioni di performance, preferisco non usare.