SET @solde = 0;
SET @avant = IFNULL((SELECT SUM(net_a_payer) FROM pieces WHERE mov=1 AND codetier = '30165' AND date < '2000-01-01' ),0);
SET @avant = @avant - IFNULL((SELECT SUM(montant) FROM reglements WHERE mov=1 AND etat < 4 AND codetier = '30165' AND date < '2000-01-01' ),0);
SELECT piece as `Pièce`, num_piece as `Numéro de pièce`, date as `Date`, heure as `Heure`, credit as `Crédit`, debit as `Débit*`, @solde:=@solde+t1.credit-t1.debit as `Solde` FROM (SELECT 'SD' AS piece, 'Solde de départ' AS num_piece, NULL AS date, NULL as heure, IF(solde_depart>0,solde_depart,0) AS credit, IF(solde_depart<0, -solde_depart, 0) as debit FROM tiers WHERE codetier = '30165' AND solde_depart<>0 UNION SELECT '' as piece, 'Avant le 01/01/2000' AS num_piece, STR_TO_DATE('2000-01-01', '%Y-%m-%d') as date, NULL as heure, IF(@avant>0,@avant, 0) AS credit, IF(@avant<0, -@avant, 0) as debit WHERE @avant<>0 UNION SELECT piece, num_piece, date, heure, IF(net_a_payer>0, net_a_payer,0) AS credit, IF(net_a_payer<0, -net_a_payer,0) AS debit FROM pieces WHERE mov=1 AND codetier = '30165' AND date BETWEEN '2000-01-01' AND  '2022-08-28' UNION SELECT 'RG' AS piece, CONCAT(LEFT(UPPER(reglements.mode),3),'.',IF(reglements.num_cheque='',reglements.num_reg,reglements.num_cheque)) AS num_piece, date, heure, IF(montant<0, -montant,0) AS credit, IF(montant>0, montant,0) AS debit FROM reglements WHERE mov=1 AND etat < 4 AND codetier = '30165'AND date BETWEEN '2000-01-01' AND  '2022-08-28' ) t1 ORDER BY date, heure
