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