Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- MONTH(from_date) AS MONTH, `their_party_name`, `their_party_id`,
- REPLACE(FORMAT(SUM(IF(TYPE="THEIR", -SUM, SUM)),0),',','') AS Result
- FROM
- (SELECT`view_payments_by_party`
- 'invoice' AS `description`,
- `our_party`.`id` AS `our_party_id`,
- `our_party`.`name` AS `our_party_name`,IF((`tbl_account`.`tbl_contract2party_tbl_party_id` = `our_party`.`id`),'OUR','THEIR') AS `type`,
- `their_party`.`id` AS `their_party_id`,
- `their_party`.`name` AS `their_party_name`,
- `tbl_account`.`id` AS `account_id`,
- `tbl_contract`.`id` AS `tbl_contract_id`,
- `tbl_bank`.`account_number` AS `account_number`,
- `tbl_bank`.`note` AS `bank_note`,
- `tbl_currency`.`description` AS `currency`,
- `tbl_invoice`.`id` AS `tbl_invoice_id`,
- `tbl_invoice`.`tbl_status_id` AS `tbl_status_id`,
- `tbl_invoice`.`number` AS `number`,
- `tbl_invoice`.`sum` AS `sum`,
- `tbl_invoice`.`invoice_date` AS `value_date`,
- `tbl_invoice`.`due_date` AS `due_date`,
- `tbl_invoice`.`from_date` AS `from_date`,
- `tbl_invoice`.`to_date` AS `to_date`,
- `tbl_invoice`.`otrs_ticket_id` AS `otrs_ticket_id`,
- `tbl_invoice`.`tbl_attachment_id` AS `tbl_attachment_id`,
- `tbl_attachment`.`filename` AS `filename`
- FROM ((((((((((`tbl_contract` JOIN `tbl_contract_type` ON((`tbl_contract`.`tbl_contract_type_id` = `tbl_contract_type`.`id`))) JOIN `tbl_currency` ON((`tbl_contract`.`tbl_currency_id` = `tbl_currency`.`id`))) JOIN `tbl_contract2party` `our_c2p` ON((`tbl_contract`.`id` = `our_c2p`.`tbl_contract_id`))) JOIN `tbl_party` `our_party` ON((`our_c2p`.`tbl_party_id` = `our_party`.`id`))) JOIN `tbl_contract2party` `their_c2p` ON(((`tbl_contract`.`id` = `their_c2p`.`tbl_contract_id`) AND (`their_c2p`.`tbl_party_id` <> `our_c2p`.`tbl_party_id`)))) JOIN `tbl_party` `their_party` ON((`their_c2p`.`tbl_party_id` = `their_party`.`id`))) JOIN `tbl_account` ON((`our_c2p`.`tbl_contract_id` = `tbl_account`.`tbl_contract2party_tbl_contract_id`))) JOIN `tbl_bank_account_data` `tbl_bank` ON((`tbl_account`.`tbl_bank_account_data_id` = `tbl_bank`.`id`))) JOIN `tbl_invoice` ON((`tbl_invoice`.`tbl_account_id` = `tbl_account`.`id`))) LEFT JOIN `tbl_attachment` ON((`tbl_invoice`.`tbl_attachment_id` = `tbl_attachment`.`id`)))
- WHERE `our_party`.`id` = 1 AND (`tbl_invoice`.`from_date` BETWEEN '2018-01-01' AND '2018-12-31')
- AND (`tbl_invoice`.`to_date` BETWEEN '2018-01-01' AND '2018-12-31')
- ORDER BY `tbl_invoice`.`invoice_date`) AS base
- GROUP BY MONTH(from_date), `their_party_id`;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement