summaryrefslogtreecommitdiff
path: root/sql/modules/Report.sql
blob: c63641db0419760f479df4729478958c3926b704 (plain)
  1. CREATE TYPE report_aging_item AS (
  2. entity_id int,
  3. meta_number varchar(24),
  4. name text,
  5. address1 text,
  6. address2 text,
  7. address3 text,
  8. city_province text,
  9. mail_code text,
  10. country text,
  11. contact_name text,
  12. email text,
  13. phone text,
  14. fax text,
  15. invnumber text,
  16. transdate date,
  17. till varchar(20),
  18. ordnumber text,
  19. ponumber text,
  20. notes text,
  21. c0 numeric,
  22. c30 numeric,
  23. c60 numeric,
  24. c90 numeric,
  25. duedate date,
  26. id int,
  27. curr varchar(3),
  28. exchangerate numeric,
  29. line_items text[][]
  30. );
  31. CREATE AGGREGATE as_array (
  32. BASETYPE = ANYELEMENT,
  33. STYPE = ANYARRAY,
  34. SFUNC = ARRAY_APPEND,
  35. INITCOND = '{}'
  36. );
  37. CREATE AGGREGATE compound_array (
  38. BASETYPE = ANYARRAY,
  39. STYPE = ANYARRAY,
  40. SFUNC = ARRAY_CAT,
  41. INITCOND = '{}'
  42. );
  43. CREATE OR REPLACE FUNCTION
  44. report_invoice_aging(in_entity_id int, in_entity_class int)
  45. RETURNS SETOF report_aging_item
  46. AS
  47. $$
  48. DECLARE
  49. item report_aging_item;
  50. BEGIN
  51. IF in_entity_class = 1 THEN
  52. FOR item IN
  53. SELECT c.entity_id,
  54. c.meta_number, e.name,
  55. l.line_one as address1, l.line_two as address2,
  56. l.line_three as address3,
  57. l.city_province, l.mail_code,
  58. country.name as country,
  59. '' as contact_name, '' as email,
  60. '' as phone, '' as fax,
  61. a.invnumber, a.transdate, a.till, a.ordnumber,
  62. a.ponumber, a.notes,
  63. CASE WHEN
  64. EXTRACT(days FROM age(a.transdate)/30)
  65. = 0
  66. THEN (a.amount - a.paid) ELSE 0 END
  67. as c0,
  68. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  69. = 1
  70. THEN (a.amount - a.paid) ELSE 0 END
  71. as c30,
  72. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  73. = 2
  74. THEN (a.amount - a.paid) ELSE 0 END
  75. as c60,
  76. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  77. > 2
  78. THEN (a.amount - a.paid) ELSE 0 END
  79. as c90,
  80. a.duedate, a.id, a.curr,
  81. COALESCE((SELECT sell FROM exchangerate ex
  82. WHERE a.curr = ex.curr
  83. AND ex.transdate = a.transdate), 1)
  84. AS exchangerate,
  85. (SELECT compound_array(ARRAY[[p.partnumber,
  86. i.description, i.qty::text]])
  87. FROM parts p
  88. JOIN invoice i ON (i.parts_id = p.id)
  89. WHERE i.trans_id = a.id) AS line_items
  90. FROM ap a
  91. JOIN entity_credit_account c USING (entity_id)
  92. JOIN entity e ON (e.id = c.entity_id)
  93. CROSS JOIN location l
  94. JOIN country ON (country.id = l.country_id)
  95. WHERE a.entity_id like coalesce(in_entity_id::text, '%')
  96. AND l.id = (SELECT min(location_id)
  97. FROM company_to_location
  98. WHERE company_id = (select min(id)
  99. FROM company
  100. WHERE entity_id = c.entity_id))
  101. ORDER BY entity_id, curr, transdate, invnumber
  102. LOOP
  103. return next item;
  104. END LOOP;
  105. ELSIF in_entity_class = 2 THEN
  106. FOR item IN
  107. SELECT c.entity_id,
  108. c.meta_number, e.name,
  109. l.line_one as address1, l.line_two as address2,
  110. l.line_three as address3,
  111. l.city_province, l.mail_code,
  112. country.name as country,
  113. '' as contact_name, '' as email,
  114. '' as phone, '' as fax,
  115. a.invnumber, a.transdate, a.till, a.ordnumber,
  116. a.ponumber, a.notes,
  117. CASE WHEN
  118. EXTRACT(days FROM age(a.transdate)/30)
  119. = 0
  120. THEN (a.amount - a.paid) ELSE 0 END
  121. as c0,
  122. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  123. = 1
  124. THEN (a.amount - a.paid) ELSE 0 END
  125. as c30,
  126. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  127. = 2
  128. THEN (a.amount - a.paid) ELSE 0 END
  129. as c60,
  130. CASE WHEN EXTRACT(days FROM age(a.transdate)/30)
  131. > 2
  132. THEN (a.amount - a.paid) ELSE 0 END
  133. as c90,
  134. a.duedate, a.id, a.curr,
  135. (SELECT buy FROM exchangerate ex
  136. WHERE a.curr = ex.curr
  137. AND ex.transdate = a.transdate)
  138. AS exchangerate,
  139. (SELECT compound_array(ARRAY[[p.partnumber,
  140. i.description, i.qty::text]])
  141. FROM parts p
  142. JOIN invoice i ON (i.parts_id = p.id)
  143. WHERE i.trans_id = a.id) AS line_items
  144. FROM ar a
  145. JOIN entity_credit_account c USING (entity_id)
  146. JOIN entity e ON (e.id = c.entity_id)
  147. CROSS JOIN location l
  148. JOIN country ON (country.id = l.country_id)
  149. WHERE a.entity_id like coalesce(in_entity_id::text, '%')
  150. AND l.id = (SELECT min(location_id)
  151. FROM company_to_location
  152. WHERE company_id = (select min(id)
  153. FROM company
  154. WHERE entity_id = c.entity_id))
  155. ORDER BY entity_id, curr, transdate, invnumber
  156. LOOP
  157. return next item;
  158. END LOOP;
  159. ELSE
  160. RAISE EXCEPTION 'Entity Class % unsupported in aging report',
  161. in_entity_class;
  162. END IF;
  163. END;
  164. $$ language plpgsql;