Cacher is the code snippet organizer for pro developers

//CAMBIAR REFERENCIA DE PEDIDOS
In PrestaShop 1.6 (tested and confirmed working in v1.6.0.14) you can accomplish this by the following method.

Copy the file /classes/PaymentModule.php to /override/classes/PaymentModule.php.
Edit the file /override/classes/PaymentModule.php as follows.

At lines 337-341 is a code block that should read like this:
if (!result)
{
  PrestaShopLogger::addLog('PaymentModule::validateOrder - Order cannot be created',
    3, null, 'Cart', (int)$id_cart, true);
  throw new PrestaShopException('Can\'t save Order');
}

mmediately after that code block, insert the following two lines of code:
$order->reference = str_pad($order->id, 9, '0', STR_PAD_LEFT);
$order->update();

	1. Delete the file /cache/class_index.php so that Prestashop automatically re-creates this file taking into account the new override file.
	2. Any existing records in your PrestaShop database can be updated to use a numerical reference manually using a tool such as phpMyAdmin.

I would imagine the steps would be very similar if not identical to these for PrestaShop v1.5 but at this time I have not tested this solution with PrestaShop v1.5. If someone finds this solution works on v1.5 perhaps they could confirm this in the comments. Thank you.

//FACTURAS DEL TRIMESTRE
SELECT d.id_order AS NºPedido, os.name AS Estado, o.invoice_date AS Fecha, o.invoice_number AS NºFactura, o.total_paid_tax_excl AS SinIva, o.total_paid_real-o.total_paid_tax_excl AS Iva,o.total_paid_real AS Total,
a.firstname AS Nombre, a.lastname AS Apellido,a.company AS Empresa, a.dni AS "NIF-CIF",  a.address1 AS Dirección, a.postcode AS "C.P.", a.city AS Ciudad, a.phone AS Teléfono, a.phone_mobile AS Móbil
FROM ps_orders d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order )
LEFT JOIN ps_address a ON ( o.id_address_invoice = a.id_address )
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer )
LEFT JOIN ps_order_history h ON ( h.id_order = o.id_order )
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group )
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state )
WHERE o.date_upd
BETWEEN  '2016-01-01'
AND  '2016-04-01'
AND os.id_lang = 1
GROUP BY d.id_order

//Client List
SELECT id_customer, id_default_group, firstname, lastname, email, newsletter FROM ps_customer 

//Client Addresses
SELECT `address1`,`address2`,`company`,`firstname`,`lastname`,`city`, `alias`,`id_address` FROM `ps_address`

//Client DNIs
SELECT `firstname`,`lastname`,`postcode`, dni FROM `ps_address` 

//Clients for TrustedShops
SELECT c.email, o.reference, c.firstname, c.lastname from ps_orders o LEFT JOIN ps_customer c on o.id_customer=c.id_customer ORDER BY o.date_add DESC LIMIT 0, 5500

SELECT 
  p.id_product as id, 
  pl.name, 
  REGEXP_REPLACE(pl.description_short, '<[^>]*>+', '') as description, 
  GROUP_CONCAT(DISTINCT(al.name) SEPARATOR ", ") as combination,
  IFNULL(p.price, 0) + IFNULL(pa.price, 0) as price
  
FROM ps_product p 

LEFT JOIN ps_product_lang pl                   ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa              ON (p.id_product = pa.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pac.id_product_attribute = pa.id_product_attribute)
LEFT JOIN ps_attribute_lang al                 ON (al.id_attribute = pac.id_attribute)
LEFT JOIN ps_attribute a                       ON (a.id_attribute = pac.id_attribute)
LEFT JOIN ps_attribute_group ag                ON (a.id_attribute_group = ag.id_attribute_group)
LEFT JOIN ps_attribute_group_lang agl          ON (ag.id_attribute_group = agl.id_attribute_group)

WHERE 
  p.active=1 AND 
  p.id_manufacturer=1 AND 
  pl.id_shop=1 AND 
  pl.id_lang=1 AND 
  al.id_lang=1
  
GROUP BY 
  p.id_product, 
  pac.id_product_attribute

# Lista de clientes por grupos
SELECT id_customer, id_default_group, firstname, lastname, email, newsletter 
FROM ps_customer 
ORDER BY id_default_group 

# Lista de direcciones
SELECT `address1`,`address2`,`company`,`firstname`,`lastname`,`city`, `alias`,`id_address` 
FROM `ps_address` 

# Listado dnis 	
SELECT `firstname`,`lastname`,`postcode`, dni 
FROM `ps_address` 

# Listado para Trusted Shops 	
SELECT c.email, o.reference, c.firstname, c.lastname 
FROM ps_orders o 
LEFT JOIN ps_customer c ON o.id_customer=c.id_customer 
ORDER BY o.date_add 
DESC LIMIT 0, 5500 	

# Listado completo de clientes por ID
SELECT id_customer, id_default_group, firstname, lastname, email, newsletter 
FROM ps_customer 

# Saldo Clientes 	
SELECT c.name, cb.balance 
FROM ps_customer c 
RIGHT JOIN ps_lpp_customer_balance cb ON c.id_customer = cb.id_customer 
WHERE cb.valid = 1 	

# Listado Primer Trimestre 2018 Grupo 4
SELECT c.firstname, c.lastname, c.email, sum(j.total_paid_tax_incl) AS total 
FROM ps_customer c 
LEFT JOIN (
    SELECT * FROM ps_orders o WHERE o.date_add > '2018-01-01' AND o.date_add<'2018-04-01'
) j ON c.id_customer = j.id_customer 
WHERE c.id_default_group=4 
GROUP BY c.id_customer 
ORDER BY sum(j.total_paid_tax_incl) 	

# Listado clientes con ultima compra y pedidos
SELECT c.firstname, c.lastname, c.email, c.date_add AS account_created, max(o.date_add) AS last_order, count(o.id_customer) AS orders ,sum(o.total_paid_tax_incl) AS total 
FROM ps_customer c LEFT JOIN ps_orders o ON c.id_customer = o.id_customer 
GROUP BY c.id_customer 
ORDER BY sum(o.total_paid_tax_incl) DESC 	

# ventas <año> 	
SELECT c.firstname, c.lastname, c.email, c.date_add AS account_created, count(o.id_customer) AS orders ,sum(o.total_paid_tax_incl) AS total 
FROM ps_customer c LEFT JOIN ps_orders o ON c.id_customer = o.id_customer 
WHERE YEAR(o.date_add)=<año> 
GROUP BY c.id_customer 
ORDER BY sum(o.total_paid_tax_incl) DESC 	

# Listado Productos
SELECT p.id_product, l.name, l.url 
FROM ps_product p LEFT JOIN ps_product_lang l ON p.id_product = l.id_product 
WHERE l.id_lang = 1 	

# CONNECTIF Listado de clientes
SELECT c.id_customer, c.firstname, c.lastname, c.email, 
    COALESCE(SUM(j.total_paid_tax_incl),0) AS total , 
    COUNT(j.date_add) AS num_orders, 
    MIN(j.date_add) AS first_order_2018, 
    MAX(j.date_add) AS last_order 
FROM ps_customer c LEFT JOIN ( 
    SELECT * FROM ps_orders o WHERE o.date_add >= '2018-01-01'
) j ON c.id_customer = j.id_customer 
WHERE c.id_default_group IN (4,7,15) 
GROUP BY c.id_customer 
ORDER BY COUNT(j.total_paid_tax_incl) 	

# CONNECTIF Listado de clientes por pedidos
SELECT c.firstname, c.lastname, c.email, o.orders , cast(o.total AS DECIMAL(20,2)) AS total, o.date_add AS date_last_purchase 
FROM ps_customer c 
RIGHT JOIN (
    SELECT id_customer, count(id_customer) as orders, sum(total_paid_tax_incl) as total, max(date_add) as date_add
    FROM ps_orders 
    WHERE date_add >= '2019-01-01' AND valid = 1
    GROUP BY id_customer
) as o ON c.id_customer = o.id_customer 
ORDER BY o.total DESC

# Listado clientes compras ultimo año
SELECT c.firstname, c.lastname, c.email, c.id_default_group, count(o.id_customer) as num_orders, o.date_add 
FROM ps_customer c LEFT JOIN ps_orders o ON c.id_customer = o.id_customer 
WHERE o.date_add > '2019-11-16' AND c.id_default_group in (4,7,15) 
GROUP BY c.id_customer 
ORDER BY c.firstname, c.lastname 	

# Mejores Clientes (Último mes)
SELECT c.firstname, c.lastname, c.email, c.date_add AS account_created, c.id_default_group AS client_group, 
    count(o.id_customer) AS orders ,
    sum(o.total_paid_tax_incl) as total 
FROM ps_customer c LEFT JOIN ps_orders o ON c.id_customer = o.id_customer 
WHERE c.id_default_group IN (2, 3) AND o.date_add >= DATE_ADD( now( ) , INTERVAL -1 MONTH )
GROUP BY c.id_customer 
ORDER BY sum(o.total_paid_tax_incl) 
DESC LIMIT 100 

# Mejores Clientes (Último mes) con direccion facturacion
SELECT 
    c.firstname, c.lastname, c.email, c.date_add AS account_created, c.id_default_group AS client_group, 
    COUNT(o.id_customer) AS orders , 
    CAST(SUM(o.total_paid_tax_incl) AS DECIMAL(20,2)) AS total, 
	a.alias, a.company, a.firstname, a.lastname, a.address1, a.postcode, a.city, a.dni
FROM ps_customer c 
LEFT JOIN ps_orders o ON c.id_customer = o.id_customer 
LEFT JOIN ps_address a ON a.id_address = o.id_address_invoice
WHERE c.id_default_group IN (2, 3)
AND o.date_add >= DATE_ADD( now( ) , INTERVAL -12 MONTH )  AND o.valid = 1
GROUP BY c.id_customer 
ORDER BY SUM(o.total_paid_tax_incl) DESC 
LIMIT 300

CREATE TRIGGER `change_active_after_update` AFTER UPDATE ON `ps_stock_available` 

FOR EACH ROW 
BEGIN 
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0); 
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0); 
END

SELECT 
p.id_product AS 'ID du produit',
p.active AS 'Actif (0/1)',
pl.name AS 'Nom',
GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ',') AS 'Catégories (x,y,z...)',
p.price AS 'Prix TTC ou HT à choix',
p.id_tax_rules_group AS 'ID règle de taxes',
p.wholesale_price AS 'Prix d_achat',
p.on_sale AS 'En soldes (0/1)',
IF(pr.reduction_type = 'amount', pr.reduction, '') AS 'Montant de la remise',
IF(pr.reduction_type = 'percentage', pr.reduction, '') AS 'Pourcentage de réduction',
pr.from AS 'Réduction de (aaaa-mm-jj)',
pr.to AS 'Réduction à (aaaa-mm-jj)',
p.reference AS 'Référence',
p.supplier_reference AS 'Référence fournisseur',
ps.name AS 'Fournisseurs',
pm.name AS 'Marque',
p.ean13 AS 'EAN-13',
p.upc AS 'UPC',
p.ecotax AS 'Eco-participation',
p.width AS 'Largeur',
p.height AS 'Hauteur',
p.depth AS 'Profondeur',
p.weight AS 'Poids',
pl.delivery_in_stock AS 'Délai de livraison_produits en stock :',
pl.delivery_out_stock AS 'Délai de livraison_produits épuisés_commande autorisée:',
sa.quantity AS 'Quantité',
p.minimal_quantity AS 'Quantité minimale',
2 AS 'N.Stock bas',
0 AS 'Email quand la quantité est en dessous de ce niveau',
p.visibility AS 'Visibilité',
p.additional_shipping_cost AS 'Frais de port supplémentaire',
p.unity AS 'Unité pour le prix unitaire',
p.unit_price_ratio AS 'Prix unitaire',
pl.description_short AS 'Récapitulatif',
pl.description AS 'Description',
IF(t.name IS NOT NULL, GROUP_CONCAT(DISTINCT(t.name) SEPARATOR ','), '') AS 'Mot_clés (x,y,z...)',
pl.meta_title AS 'Balise titre',
pl.meta_keywords AS 'Meta mots_clés',
pl.meta_description AS 'Meta description',
pl.link_rewrite AS 'URL réécrite',
pl.available_now AS 'Libellé si en stock',
pl.available_later AS 'Libellé quand précommande activée',
p.available_for_order AS 'Disponible à la commande (0 = Non, 1 = Oui)',
p.available_date AS 'Date de disponibilité du produit',
p.date_add  AS 'Date d_ajout du produit',
p.show_price AS 'Afficher le prix (0 = Non, 1 = Oui)',
--  AS image_url,
GROUP_CONCAT(DISTINCT(CONCAT('https://',
    -- get the shop domain
    IFNULL(conf.value, 'undefined_domain'),
    -- the path to the pictures folder
    '/img/p/',
    -- now take all the digits separetly as MySQL doesn't support loops in SELECT statements
    -- assuming we have smaller image id than 100'000 ;)
    IF(CHAR_LENGTH(pi.id_image) >= 5, 
        -- if we have 5 digits for the image id
        CONCAT(
        -- take the first digit
        SUBSTRING(pi.id_image, -5, 1),
        -- add a slash
        '/'),
        ''),
    -- repeat for the next digits
    IF(CHAR_LENGTH(pi.id_image) >= 4, CONCAT(SUBSTRING(pi.id_image, -4, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 3, CONCAT(SUBSTRING(pi.id_image, -3, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 2, CONCAT(SUBSTRING(pi.id_image, -2, 1), '/'), ''),
    IF(CHAR_LENGTH(pi.id_image) >= 1, CONCAT(SUBSTRING(pi.id_image, -1, 1), '/'), ''),
    -- add the image id
    pi.id_image,
    -- put the image extension
    '.jpg')) SEPARATOR ', ') AS 'URL des images (x,y,z, etc.)',
'' AS 'Textes alternatif des images (x,y,z...)',
1 AS 'Supprimer les images existantes (0 = Non, 1 = Oui)',
GROUP_CONCAT(DISTINCT(CONCAT((fl.name),
':',
(fvl.value),
':0')) SEPARATOR ',') AS 'Caractéristique (Nom:Valeur:Position:Personnalisé)',
0 AS 'Disponible en ligne uniquement (0 = Non, 1 = Oui)',
p.condition AS 'État'

FROM ps_product p
LEFT JOIN ps_product_lang pl ON(p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
LEFT JOIN ps_category_product cp ON(p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON(cp.id_category = cl.id_category)
LEFT JOIN ps_specific_price pr ON(p.id_product = pr.id_product)
LEFT JOIN ps_product_tag pt ON(p.id_product = pt.id_product)
LEFT JOIN ps_tag t ON(pt.id_tag = t.id_tag)
LEFT JOIN ps_image pi ON(p.id_product = pi.id_product)
LEFT JOIN ps_manufacturer pm ON(p.id_manufacturer = pm.id_manufacturer)
LEFT JOIN ps_supplier ps ON(p.id_supplier = ps.id_supplier)
LEFT JOIN ps_configuration conf ON conf.name = 'PS_SHOP_DOMAIN'
LEFT JOIN ps_feature_product fp ON p.id_product = fp.id_product
LEFT JOIN ps_feature_lang fl ON fp.id_feature = fl.id_feature
LEFT JOIN ps_feature_value_lang fvl ON fp.id_feature_value = fvl.id_feature_value
LEFT JOIN ps_feature f ON fp.id_feature = f.id_feature
LEFT JOIN ps_feature_value fv ON fp.id_feature_value = fv.id_feature_value
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
GROUP BY p.id_product;