billing sql: SELECT null as uid, '' as id, '' as date, 'balance' as 'type', 'Balance' as description, '' as address, balance as 'amount' FROM sr_contractor WHERE uid='10' UNION SELECT i.uid as uid, p.uid as id, pc.accepted_date as date, 'lead' as 'type', concat(cl.firstname, ' ', cl.lastname) as description, concat(cl.firstname, ' ', cl.lastname, ' ', IF(a.uid IS NULL, '', CONCAT(a.street_no, ' ', a.street, ', ', IF(c.name_fr IS NOT NULL, c.name_fr, a.city), ', ', a.province, ' ', a.zip))) as address, i.amount as 'amount' FROM sr_invoice i INNER JOIN sr_project_contractor pc ON pc.uid = i.uid_project_contractor INNER JOIN sr_project p ON p.uid = pc.uid_project INNER JOIN sr_client cl ON cl.uid = p.uid_client LEFT JOIN sr_address a ON a.uid = p.uid_address LEFT JOIN sr_city c ON c.uid = a.uid_city WHERE i.uid_pivotal = '10' UNION SELECT cr.uid as uid, p.uid as id, cr.sn_cdate as date, 'credit' as 'type', 'Crédit' as description, '' as address, (pc.lead_price*-1) as 'amount' FROM sr_credit cr INNER JOIN sr_project_contractor pc ON pc.uid = cr.uid_project_contractor INNER JOIN sr_project p ON p.uid = pc.uid_project LEFT JOIN sr_address a ON a.uid = p.uid_address LEFT JOIN sr_city c ON c.uid = a.uid_city WHERE cr.accepted = 'yes' AND cr.uid_pivotal = '10' UNION SELECT uid, '' as id, sn_cdate as date, 'transaction' as 'type', CONCAT('Transaction', ' ', DATE_FORMAT(sn_cdate, '%Y-%m-%d') , ' ', uniqueref) as description, '' as address, (amount*-1) as 'amount' FROM sr_transaction t WHERE t.uid_pivotal = '10' UNION SELECT sc.uid as uid, '' as id, sc.start_date as date, 'subscription' as type, sp.name_fr as description, '' as address, (sc.price - ifnull(sc.discount,0)) as amount FROM sr_subscription_contractor sc INNER JOIN sr_subscription_plan sp ON sp.uid = sc.uid_subscription_plan WHERE sc.uid_pivotal = '10' ORDER BY (type = 'transaction') ASC, date ASC, type DESC

facture_78_9464.pdf


billing sql: SELECT null as uid, '' as id, '' as date, 'balance' as 'type', 'Balance' as description, '' as address, balance as 'amount' FROM sr_contractor WHERE uid='10' UNION SELECT i.uid as uid, p.uid as id, pc.accepted_date as date, 'lead' as 'type', concat(cl.firstname, ' ', cl.lastname) as description, concat(cl.firstname, ' ', cl.lastname, ' ', IF(a.uid IS NULL, '', CONCAT(a.street_no, ' ', a.street, ', ', IF(c.name_fr IS NOT NULL, c.name_fr, a.city), ', ', a.province, ' ', a.zip))) as address, i.amount as 'amount' FROM sr_invoice i INNER JOIN sr_project_contractor pc ON pc.uid = i.uid_project_contractor INNER JOIN sr_project p ON p.uid = pc.uid_project INNER JOIN sr_client cl ON cl.uid = p.uid_client LEFT JOIN sr_address a ON a.uid = p.uid_address LEFT JOIN sr_city c ON c.uid = a.uid_city WHERE i.uid_pivotal = '10' UNION SELECT cr.uid as uid, p.uid as id, cr.sn_cdate as date, 'credit' as 'type', 'Crédit' as description, '' as address, (pc.lead_price*-1) as 'amount' FROM sr_credit cr INNER JOIN sr_project_contractor pc ON pc.uid = cr.uid_project_contractor INNER JOIN sr_project p ON p.uid = pc.uid_project LEFT JOIN sr_address a ON a.uid = p.uid_address LEFT JOIN sr_city c ON c.uid = a.uid_city WHERE cr.accepted = 'yes' AND cr.uid_pivotal = '10' UNION SELECT uid, '' as id, sn_cdate as date, 'transaction' as 'type', CONCAT('Transaction', ' ', DATE_FORMAT(sn_cdate, '%Y-%m-%d') , ' ', uniqueref) as description, '' as address, (amount*-1) as 'amount' FROM sr_transaction t WHERE t.uid_pivotal = '10' UNION SELECT sc.uid as uid, '' as id, sc.start_date as date, 'subscription' as type, sp.name_fr as description, '' as address, (sc.price - ifnull(sc.discount,0)) as amount FROM sr_subscription_contractor sc INNER JOIN sr_subscription_plan sp ON sp.uid = sc.uid_subscription_plan WHERE sc.uid_pivotal = '10' ORDER BY (type = 'transaction') ASC, date ASC, type DESC

facture_78_9465.pdf