41 lines
1.1 KiB
MySQL
41 lines
1.1 KiB
MySQL
|
CREATE OR REPLACE DEFINER=`root`@`localhost`
|
||
|
|
SQL SECURITY DEFINER
|
||
|
|
VIEW `bi`.`customerRiskOverdue`
|
||
|
|
AS SELECT `cr`.`clientFk` AS `customer_id`,
|
||
|
|
`cr`.`amount` AS `amount`,
|
||
|
|
`cr`.`companyFk` AS `company_id`
|
||
|
|
FROM (
|
||
|
|
(
|
||
|
|
(
|
||
|
|
`vn`.`clientRisk` `cr`
|
||
|
|
JOIN `vn`.`company` `co` ON(`co`.`id` = `cr`.`companyFk`)
|
||
|
|
)
|
||
|
|
JOIN `vn`.`client` `c` ON(`cr`.`clientFk` = `c`.`id`)
|
||
|
|
)
|
||
|
|
JOIN `vn`.`payMethod` `pm` ON(`pm`.`id` = `c`.`payMethodFk`)
|
||
|
|
)
|
||
|
|
WHERE `cr`.`amount` <> 0
|
||
|
|
AND `co`.`isDefaulter` <> 0
|
||
|
|
AND `pm`.`outstandingDebt` <> 0
|
||
|
|
AND `c`.`typeFk` = 'normal'
|
||
|
|
UNION ALL
|
||
|
|
SELECT `i`.`clientFk` AS `Id_Cliente`,
|
||
|
|
- round(`i`.`amount`, 2) AS `importe`,
|
||
|
|
`i`.`companyFk` AS `empresa_id`
|
||
|
|
FROM (
|
||
|
|
(
|
||
|
|
(
|
||
|
|
`vn`.`invoiceOut` `i`
|
||
|
|
JOIN `vn`.`client` `c` ON(`i`.`clientFk` = `c`.`id`)
|
||
|
|
)
|
||
|
|
JOIN `vn`.`company` `co` ON(`co`.`id` = `i`.`companyFk`)
|
||
|
|
)
|
||
|
|
JOIN `vn`.`payMethod` `pm` ON(`pm`.`id` = `c`.`payMethodFk`)
|
||
|
|
)
|
||
|
|
WHERE `i`.`issued` > `util`.`VN_CURDATE`() + INTERVAL -101 DAY
|
||
|
|
AND `i`.`dued` + INTERVAL `pm`.`graceDays` DAY > `util`.`VN_CURDATE`()
|
||
|
|
AND `i`.`amount` > 0
|
||
|
|
AND `co`.`isDefaulter` <> 0
|
||
|
|
AND `pm`.`outstandingDebt` <> 0
|
||
|
|
AND `c`.`typeFk` = 'normal'
|