summaryrefslogtreecommitdiff
path: root/sql/modules/Payment.sql
blob: 7f2d16e708c253c736df5d2e7ea9cc4df71efe5a (plain)
  1. CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
  2. returns SETOF entity AS
  3. $$
  4. DECLARE out_entity entity%ROWTYPE;
  5. BEGIN
  6. FOR out_entity IN
  7. SELECT * FROM entity
  8. WHERE id IN (SELECT entity_id FROM entity_credit_account
  9. WHERE entity_class = in_account_class)
  10. AND CASE WHEN in_account_class = 1 THEN
  11. id IN (SELECT entity_id FROM ap
  12. WHERE amount <> paid
  13. GROUP BY entity_id)
  14. WHEN in_account_class = 2 THEN
  15. id IN (SELECT entity_id FROM ar
  16. WHERE amount <> paid
  17. GROUP BY entity_id)
  18. END
  19. LOOP
  20. RETURN NEXT out_entity;
  21. END LOOP;
  22. END;
  23. $$ LANGUAGE PLPGSQL;
  24. COMMENT ON FUNCTION payment_get_open_accounts(int) IS
  25. $$ This function takes a single argument (1 for vendor, 2 for customer as
  26. always) and returns all entities with open accounts of the appropriate type. $$;
  27. CREATE OR REPLACE FUNCTION payment_get_all_accounts(in_account_class int)
  28. RETURNS SETOF entity AS
  29. $$
  30. DECLARE out_entity entity%ROWTYPE;
  31. BEGIN
  32. FOR out_entity IN
  33. SELECT * FROM entity
  34. WHERE id IN (seLECT entity_id FROM entity_credit_account
  35. WHERE entity_class = in_account_class)
  36. LOOP
  37. RETURN NEXT out_entity;
  38. END LOOP;
  39. END;
  40. $$ LANGUAGE PLPGSQL;
  41. COMMENT ON FUNCTION payment_get_open_accounts(int) IS
  42. $$ This function takes a single argument (1 for vendor, 2 for customer as
  43. always) and returns all entities with accounts of the appropriate type. $$;
  44. CREATE TYPE payment_invoice AS (
  45. invoice_id int,
  46. invnumber text,
  47. invoice_date date,
  48. amount numeric,
  49. discount numeric,
  50. due numeric
  51. );
  52. CREATE OR REPLACE FUNCTION payment_get_open_invoices
  53. (in_account_class int, in_entity_id int, in_curr char(3))
  54. RETURNS SETOF payment_invoice AS
  55. $$
  56. DECLARE payment_inv payment_invoice;
  57. BEGIN
  58. FOR payment_inv IN
  59. SELECT a.id AS invoice_id, a.invnumber,
  60. a.transdate AS invoice_date, a.amount,
  61. CASE WHEN discount_terms
  62. > extract('days' FROM age(a.transdate))
  63. THEN 0
  64. ELSE (a.amount - a.paid) * c.discount / 100
  65. END AS discount,
  66. a.amount - a.paid -
  67. CASE WHEN discount_terms
  68. > extract('days' FROM age(a.transdate))
  69. THEN 0
  70. ELSE (a.amount - a.paid) * c.discount / 100
  71. END AS due
  72. FROM (SELECT id, invnumber, transdate, amount, entity_id,
  73. 1 as invoice_class, paid, curr
  74. FROM ap
  75. UNION
  76. SELECT id, invnumber, transdate, amount, entity_id,
  77. 2 AS invoice_class, paid, curr
  78. FROM ar
  79. ) a
  80. JOIN entity_credit_account c USING (entity_id)
  81. WHERE a.invoice_class = in_account_class
  82. AND c.entity_class = in_account_class
  83. AND a.curr = in_curr
  84. LOOP
  85. RETURN NEXT payment_inv;
  86. END LOOP;
  87. END;
  88. $$ LANGUAGE PLPGSQL;
  89. COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3)) IS
  90. $$ This function takes three arguments:
  91. Type: 1 for vendor, 2 for customer
  92. Entity_id: The entity_id of the customer or vendor
  93. Currency: 3 characters for currency ('USD' for example).
  94. Returns all open invoices for the entity in question. $$;
  95. CREATE TYPE payment_contact_invoice AS (
  96. contact_id int,
  97. contact_name text,
  98. account_number text,
  99. total_due numeric,
  100. invoices text[]
  101. );
  102. CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
  103. (in_account_class int, in_business_type int, in_currency char(3),
  104. in_date_from date, in_date_to date, in_batch_id int,
  105. in_ar_ap_accno text)
  106. RETURNS SETOF payment_contact_invoice AS
  107. $$
  108. DECLARE payment_item payment_contact_invoice;
  109. BEGIN
  110. FOR payment_item IN
  111. SELECT e.id AS contact_id, e.name AS contact_name,
  112. c.meta_number AS account_number,
  113. sum(a.amount - a.paid) AS total_due,
  114. compound_array(ARRAY[[
  115. a.id::text, a.invnumber, a.transdate::text,
  116. a.amount::text,
  117. (CASE WHEN c.discount_terms
  118. > extract('days' FROM age(a.transdate))
  119. THEN 0
  120. ELSE (a.amount - a.paid) * c.discount / 100
  121. END)::text,
  122. (a.amount - a.paid -
  123. CASE WHEN c.discount_terms
  124. > extract('days' FROM age(a.transdate))
  125. THEN 0
  126. ELSE (a.amount - a.paid) * c.discount / 100
  127. END)::text]])
  128. FROM entity e
  129. JOIN entity_credit_account c ON (e.id = c.entity_id)
  130. JOIN (SELECT id, invnumber, transdate, amount, entity_id,
  131. paid, curr, 1 as invoice_class
  132. FROM ap
  133. UNION
  134. SELECT id, invnumber, transdate, amount, entity_id,
  135. paid, curr, 2 as invoice_class
  136. FROM ar
  137. ) a USING (entity_id)
  138. WHERE a.invoice_class = in_account_class
  139. AND ((a.transdate >= in_date_from
  140. AND a.transdate <= in_date_to)
  141. OR a.id IN (select voucher.trans_id FROM voucher
  142. WHERE batch_id = in_batch_id))
  143. AND c.entity_class = in_account_class
  144. AND a.curr = in_currency
  145. AND EXISTS (select trans_id FROM acc_trans
  146. WHERE trans_id = a.id AND
  147. chart_id = (SELECT id frOM chart
  148. WHERE accno
  149. = in_ar_ap_accno)
  150. )
  151. GROUP BY e.id, e.name, c.meta_number, c.threshold
  152. HAVING sum(a.amount - a.paid) > c.threshold
  153. LOOP
  154. RETURN NEXT payment_item;
  155. END LOOP;
  156. END;
  157. $$ LANGUAGE plpgsql;
  158. COMMENT ON FUNCTION payment_get_all_contact_invoices
  159. (in_account_class int, in_business_type int, in_currency char(3),
  160. in_date_from date, in_date_to date, in_batch_id int,
  161. in_ar_ap_accno text) IS
  162. $$
  163. This function takes the following arguments (all prefaced with in_ in the db):
  164. account_class: 1 for vendor, 2 for customer
  165. business_type: integer of business.id.
  166. currency: char(3) of currency (for example 'USD')
  167. date_from, date_to: These dates are inclusive.
  168. batch_id: For payment batches, where fees are concerned.
  169. ar_ap_accno: The AR/AP account number.
  170. This then returns a set of contact information with a 2 dimensional array
  171. cnsisting of outstanding invoices.
  172. $$;
  173. CREATE OR REPLACE FUNCTION payment_post
  174. (in_trans_id int, in_source text, in_amount numeric, in_ar_ap_accno text,
  175. in_cash_accno text, in_approved bool, in_payment_date date,
  176. in_account_class int)
  177. RETURNS INT AS
  178. $$
  179. DECLARE out_entry_id int;
  180. BEGIN
  181. INSERT INTO acc_trans (chart_id, amount,
  182. trans_id, transdate, approved, source)
  183. VALUES ((SELECT id FROM chart WHERE accno = in_ar_ap_accno),
  184. CASE WHEN in_account_class = 1 THEN in_amount * -1
  185. ELSE amount
  186. END,
  187. in_trans_id, in_payment_date, in_approved, in_source);
  188. INSERT INTO acc_trans (chart_id, amount,
  189. trans_id, transdate, approved, source)
  190. VALUES ((SELECT id FROM chart WHERE accno = in_cash_accno),
  191. CASE WHEN in_account_class = 2 THEN in_amount * -1
  192. ELSE amount
  193. END,
  194. in_trans_id, in_payment_date, coalesce(in_approved, true),
  195. in_source);
  196. SELECT currval('acc_trans_entry_id_seq') INTO out_entry_id;
  197. RETURN out_entry_id;
  198. END;
  199. $$ LANGUAGE PLPGSQL;
  200. COMMENT ON FUNCTION payment_post
  201. (in_trans_id int, in_source text, in_amount numeric, in_ar_ap_accno text,
  202. in_cash_accno text, in_approved bool, in_payment_date date,
  203. in_account_class int)
  204. IS $$
  205. This function takes the following arguments (prefaced with in_ in the db):
  206. trans_id: Id for ar/ap transaction.
  207. source: text for source documnet identifier (for example, check number)
  208. amount: numeric for the amount of the transaction
  209. ar_ap_accno: AR/AP account number
  210. cash_accno: Cash Account number, i.e. the account where the payment will be
  211. held
  212. approved: False, for a voucher.
  213. This function posts the payment or saves the payment voucher.
  214. $$;
  215. -- Move this to the projects module when we start on that. CT
  216. CREATE OR REPLACE FUNCTION project_list_open(in_date date)
  217. RETURNS SETOF project AS
  218. $$
  219. DECLARE out_project project%ROWTYPE;
  220. BEGIN
  221. FOR out_project IN
  222. SELECT * from project
  223. WHERE startdate <= in_date AND enddate >= in_date
  224. AND completed = 0
  225. LOOP
  226. return next out_project;
  227. END LOOP;
  228. END;
  229. $$ language plpgsql;
  230. comment on function project_list_open(in_date date) is
  231. $$ This function returns all projects that were open as on the date provided as
  232. the argument.$$;
  233. -- Move this to the projects module when we start on that. CT
  234. CREATE OR REPLACE FUNCTION department_list(in_role char)
  235. RETURNS SETOF department AS
  236. $$
  237. DECLARE out_department department%ROWTYPE;
  238. BEGIN
  239. FOR out_department IN
  240. SELECT * from department
  241. WHERE role = in_role
  242. LOOP
  243. return next out_department;
  244. END LOOP;
  245. END;
  246. $$ language plpgsql;
  247. comment on function department_list(in_role char) is
  248. $$ This function returns all department that match the role provided as
  249. the argument.$$;
  250. CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)
  251. RETURNS SETOF char(3) AS
  252. $$
  253. DECLARE resultrow record;
  254. BEGIN
  255. FOR resultrow IN
  256. SELECT curr FROM ar
  257. WHERE amount <> paid
  258. OR paid IS NULL
  259. AND in_account_class=2
  260. UNION
  261. SELECT curr FROM ap
  262. WHERE amount <> paid
  263. OR paid IS NULL
  264. AND in_account_class=1
  265. ORDER BY curr
  266. LOOP
  267. return next resultrow.curr;
  268. END LOOP;
  269. END;
  270. $$ language plpgsql;
  271. CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int)
  272. RETURNS NUMERIC AS
  273. $$
  274. DECLARE
  275. out_exrate exchangerate.buy%TYPE;
  276. BEGIN
  277. IF in_account_class = 1 THEN
  278. SELECT INTO out_exrate buy
  279. FROM exchangerate
  280. WHERE transdate = in_date AND curr = in_currency;
  281. ELSE
  282. SELECT INTO out_exrate sell
  283. FROM exchangerate
  284. WHERE transdate = in_date AND curr = in_currency;
  285. END IF;
  286. RETURN out_exrate;
  287. END;
  288. $$ language plpgsql;
  289. COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS
  290. $$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$;
  291. CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_id int)
  292. RETURNS SETOF entity AS
  293. $$
  294. DECLARE
  295. out_info entity%ROWTYPE;
  296. BEGIN
  297. FOR out_info IN
  298. SELECT e.id, e.name FROM entity e
  299. JOIN company c ON (e.id = c.entity_id)
  300. WHERE e.id = in_entity_id
  301. --SELECT e.id, c.legal_name, l.line_one, l.city_province, cy.name FROM entity e
  302. --JOIN company c ON (e.id = c.entity_id)
  303. --JOIN company_to_location cl ON (c.id = cl.company_id)
  304. --JOIN location l ON (l.id = cl.location_id)
  305. --JOIN country cy ON (cy.id = l.country_id)
  306. LOOP
  307. return next out_info;
  308. END LOOP;
  309. IF NOT FOUND THEN
  310. RAISE EXCEPTION 'ID % not found!!!!!', in_entity_id;
  311. END IF;
  312. END;
  313. $$ language plpgsql;
  314. COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int) IS
  315. $$ This function return vendor or customer info, its under construction $$;