summaryrefslogtreecommitdiff
path: root/sql/modules/Voucher.sql
blob: c9d6cc603075cc06331067a41715fd6cf044d23c (plain)
  1. CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer)
  2. RETURNS batch AS
  3. $$
  4. DECLARE
  5. batch_out batch%ROWTYPE;
  6. BEGIN
  7. SELECT * INTO batch_out FROM batch b WHERE b.id = in_batch_id;
  8. RETURN batch_out;
  9. END;
  10. $$ language plpgsql;
  11. CREATE OR REPLACE FUNCTION batch_update (in_batch text, in_login varchar, in_entered date,
  12. in_batch_number text, in_description text, in_id integer)
  13. RETURNS integer AS
  14. $$
  15. BEGIN
  16. UPDATE batch
  17. SET batch_number = in_batch_number,
  18. description = in_description,
  19. entered = in_entered
  20. WHERE id = in_id;
  21. IF FOUND THEN
  22. RETURN in_id;
  23. END IF;
  24. INSERT INTO batch (batch, employee_id, batch_number, description,
  25. entered)
  26. VALUES (in_batch, (SELECT id FROM employees WHERE login = in_login),
  27. in_batch_number, description);
  28. RETURN currval('id');
  29. END;
  30. $$ LANGUAGE PLPGSQL;
  31. CREATE TYPE voucher_list AS (
  32. id int,
  33. reference text,
  34. description text,
  35. batch_id int,
  36. transaction_id integer,
  37. amount numeric,
  38. transaction_date date,
  39. batch_class text
  40. );
  41. CREATE OR REPLACE FUNCTION voucher_list (in_batch_id integer)
  42. RETURNS SETOF voucher_list AS
  43. $$
  44. declare voucher_item record;
  45. BEGIN
  46. FOR voucher_item IN
  47. SELECT v.id, a.invnumber, e.name,
  48. v.batch_id, v.trans_id,
  49. a.amount, a.transdate, 'Payable'
  50. FROM voucher v
  51. JOIN ap a ON (v.trans_id = a.id)
  52. JOIN entity e ON (a.entity_id = e.id)
  53. WHERE v.batch_id = in_batch_id
  54. AND v.batch_class = (select id from batch_class
  55. WHERE class = 'ap')
  56. UNION
  57. SELECT v.id, a.invnumber, e.name,
  58. v.batch_id, v.trans_id,
  59. a.amount, a.transdate, 'Receivable'
  60. FROM voucher v
  61. JOIN ar a ON (v.trans_id = a.id)
  62. JOIN entity e ON (a.entity_id = e.id)
  63. WHERE v.batch_id = in_batch_id
  64. AND v.batch_class = (select id from batch_class
  65. WHERE class = 'ar')
  66. UNION ALL
  67. -- TODO: Add the class labels to the class table.
  68. SELECT v.id, a.source,
  69. cr.meta_number || '--' || co.legal_name ,
  70. v.batch_id, v.trans_id,
  71. CASE WHEN bc.class LIKE 'payment%' THEN a.amount * -1
  72. ELSE a.amount END, a.transdate,
  73. CASE WHEN bc.class = 'payment' THEN 'Payment'
  74. WHEN bc.class = 'payment_reversal'
  75. THEN 'Payment Reversal'
  76. END
  77. FROM voucher v
  78. JOIN acc_trans a ON (v.id = a.voucher_id)
  79. JOIN batch_class bc ON (bc.id = v.batch_class)
  80. JOIN chart c ON (a.chart_id = c.id)
  81. JOIN ap ON (ap.id = a.trans_id)
  82. JOIN entity_credit_account cr
  83. ON (ap.entity_credit_account = cr.id)
  84. JOIN company co ON (cr.entity_id = co.entity_id)
  85. WHERE v.batch_id = in_batch_id
  86. AND a.voucher_id = v.id
  87. AND (bc.class like 'payment%' AND c.link = 'AP')
  88. UNION ALL
  89. SELECT v.id, a.source, a.memo,
  90. v.batch_id, v.trans_id,
  91. CASE WHEN bc.class LIKE 'receipt%' THEN a.amount * -1
  92. ELSE a.amount END, a.transdate,
  93. CASE WHEN bc.class = 'receipt' THEN 'Receipt'
  94. WHEN bc.class = 'receipt_reversal'
  95. THEN 'Receipt Reversal'
  96. END
  97. FROM voucher v
  98. JOIN acc_trans a ON (v.trans_id = a.trans_id)
  99. JOIN batch_class bc ON (bc.id = v.batch_class)
  100. JOIN chart c ON (a.chart_id = c.id)
  101. JOIN ar ON (ar.id = a.trans_id)
  102. JOIN entity_credit_account cr
  103. ON (ar.entity_credit_account = cr.id)
  104. JOIN company co ON (cr.entity_id = co.entity_id)
  105. WHERE v.batch_id = in_batch_id
  106. AND a.voucher_id = v.id
  107. AND (bc.class like 'receipt%' AND c.link = 'AR')
  108. UNION ALL
  109. SELECT v.id, g.reference, g.description,
  110. v.batch_id, v.trans_id,
  111. sum(a.amount), g.transdate, 'gl'
  112. FROM voucher v
  113. JOIN gl g ON (g.id = v.trans_id)
  114. JOIN acc_trans a ON (v.trans_id = a.trans_id)
  115. WHERE a.amount > 0
  116. AND v.batch_id = in_batch_id
  117. AND v.batch_class IN (select id from batch_class
  118. where class = 'gl')
  119. GROUP BY v.id, g.reference, g.description, v.batch_id,
  120. v.trans_id, g.transdate
  121. ORDER BY 7, 1
  122. LOOP
  123. RETURN NEXT voucher_item;
  124. END LOOP;
  125. END;
  126. $$ language plpgsql;
  127. CREATE TYPE batch_list_item AS (
  128. id integer,
  129. batch_class text,
  130. control_code text,
  131. description text,
  132. created_by text,
  133. created_on date,
  134. transaction_total numeric,
  135. payment_total numeric
  136. );
  137. CREATE OR REPLACE FUNCTION
  138. batch_search(in_class_id int, in_description text, in_created_by_eid int,
  139. in_amount_gt numeric,
  140. in_amount_lt numeric, in_approved bool)
  141. RETURNS SETOF batch_list_item AS
  142. $$
  143. DECLARE out_value batch_list_item;
  144. BEGIN
  145. FOR out_value IN
  146. SELECT b.id, c.class, b.control_code, b.description, u.username,
  147. b.created_on,
  148. sum(
  149. CASE WHEN vc.id = 5 AND al.amount < 0 -- GL
  150. THEN al.amount
  151. WHEN vc.id = 1
  152. THEN ap.amount
  153. WHEN vc.id = 2
  154. THEN ap.amount
  155. ELSE 0
  156. END) AS transaction_total,
  157. sum(
  158. CASE WHEN alc.link = 'AR' AND vc.id IN (6, 7)
  159. THEN al.amount
  160. WHEN alc.link = 'AP' AND vc.id IN (3, 4)
  161. THEN al.amount * -1
  162. ELSE 0
  163. END
  164. ) AS payment_total
  165. FROM batch b
  166. JOIN batch_class c ON (b.batch_class_id = c.id)
  167. LEFT JOIN users u ON (u.entity_id = b.created_by)
  168. JOIN voucher v ON (v.batch_id = b.id)
  169. JOIN batch_class vc ON (v.batch_class = vc.id)
  170. LEFT JOIN ar ON (vc.id = 2 AND v.trans_id = ar.id)
  171. LEFT JOIN ap ON (vc.id = 1 AND v.trans_id = ap.id)
  172. LEFT JOIN acc_trans al ON
  173. ((vc.id = 5 AND v.trans_id = al.trans_id) OR
  174. (vc.id IN (3, 4, 6, 7)
  175. AND al.voucher_id = v.id))
  176. LEFT JOIN chart alc ON (al.chart_id = alc.id)
  177. WHERE (c.id = in_class_id OR in_class_id IS NULL) AND
  178. (b.description LIKE
  179. '%' || in_description || '%' OR
  180. in_description IS NULL) AND
  181. (in_created_by_eid = b.created_by OR
  182. in_created_by_eid IS NULL) AND
  183. ((in_approved = false OR in_approved IS NULL AND
  184. approved_on IS NULL) OR
  185. (in_approved = true AND approved_on IS NOT NULL)
  186. )
  187. GROUP BY b.id, c.class, b.description, u.username, b.created_on,
  188. b.control_code
  189. HAVING
  190. (in_amount_gt IS NULL OR
  191. sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
  192. al.amount))
  193. >= in_amount_gt)
  194. AND
  195. (in_amount_lt IS NULL OR
  196. sum(coalesce(ar.amount - ar.paid, ap.amount - ap.paid,
  197. al.amount))
  198. <= in_amount_lt)
  199. LOOP
  200. RETURN NEXT out_value;
  201. END LOOP;
  202. END;
  203. $$ LANGUAGE PLPGSQL;
  204. CREATE OR REPLACE FUNCTION batch_post(in_batch_id INTEGER)
  205. returns date AS
  206. $$
  207. BEGIN
  208. UPDATE ar SET approved = true
  209. WHERE id IN (select trans_id FROM voucher
  210. WHERE batch_id = in_batch_id
  211. AND batch_class = 2);
  212. UPDATE ap SET approved = true
  213. WHERE id IN (select trans_id FROM voucher
  214. WHERE batch_id = in_batch_id
  215. AND batch_class = 1);
  216. UPDATE gl SET approved = true
  217. WHERE id IN (select trans_id FROM voucher
  218. WHERE batch_id = in_batch_id
  219. AND batch_class = 5);
  220. UPDATE acc_trans SET approved = true
  221. WHERE voucher_id IN (select id FROM voucher
  222. WHERE batch_id = in_batch_id
  223. AND batch_class IN (3, 4, 7, 8));
  224. UPDATE batch
  225. SET approved_on = now(),
  226. approved_by = (select entity_id FROM users
  227. WHERE username = SESSION_USER)
  228. WHERE id = in_batch_id;
  229. RETURN now()::date;
  230. END;
  231. $$ LANGUAGE PLPGSQL;
  232. CREATE OR REPLACE FUNCTION batch_list_classes() RETURNS SETOF batch_class AS
  233. $$
  234. DECLARE out_val record;
  235. BEGIN
  236. FOR out_val IN select * from batch_class
  237. LOOP
  238. return next out_val;
  239. END LOOP;
  240. END;
  241. $$ language plpgsql;
  242. CREATE OR REPLACE FUNCTION batch_get_users() RETURNS SETOF users AS
  243. $$
  244. DECLARE out_record users%ROWTYPE;
  245. BEGIN
  246. FOR out_record IN
  247. SELECT * from users WHERE entity_id IN (select created_by from batch)
  248. LOOP
  249. RETURN NEXT out_record;
  250. END LOOP;
  251. END;
  252. $$ LANGUAGE PLPGSQL;
  253. CREATE OR REPLACE FUNCTION batch_create(
  254. in_batch_number text, in_description text, in_batch_class text) RETURNS int AS
  255. $$
  256. BEGIN
  257. INSERT INTO
  258. batch (batch_class_id, description, control_code, created_by)
  259. VALUES ((SELECT id FROM batch_class WHERE class = in_batch_class),
  260. in_description, in_batch_number,
  261. (select entity_id FROM users WHERE username = session_user));
  262. return currval('batch_id_seq');
  263. END;
  264. $$ LANGUAGE PLPGSQL;
  265. CREATE OR REPLACE FUNCTION batch_delete(in_batch_id int) RETURNS int AS
  266. $$
  267. DECLARE
  268. t_transaction_ids int[];
  269. BEGIN
  270. -- Adjust AR/AP tables for payment and payment reversal vouchers
  271. -- voucher_id is only set in acc_trans on payment/receipt vouchers and
  272. -- their reversals. -CT
  273. update ar set paid = amount +
  274. (select sum(amount) from acc_trans
  275. join chart ON (acc_trans.chart_id = chart.id)
  276. where link = 'AR' AND trans_id = ar.id
  277. AND (voucher_id IS NULL OR voucher_id NOT IN
  278. (select id from voucher
  279. WHERE batch_id = in_batch_id)))
  280. where id in (select trans_id from acc_trans where voucher_id IN
  281. (select id from voucher where batch_id = in_batch_id));
  282. update ap set paid = amount - (select sum(amount) from acc_trans
  283. join chart ON (acc_trans.chart_id = chart.id)
  284. where link = 'AP' AND trans_id = ap.id
  285. AND (voucher_id IS NULL OR voucher_id NOT IN
  286. (select id from voucher
  287. WHERE batch_id = in_batch_id)))
  288. where id in (select trans_id from acc_trans where voucher_id IN
  289. (select id from voucher where batch_id = in_batch_id));
  290. DELETE FROM acc_trans WHERE voucher_id IN
  291. (select id FROM voucher where batch_id = in_batch_id);
  292. -- The rest of this function involves the deletion of actual
  293. -- transactions, vouchers, and batches, and jobs which are in progress.
  294. -- -CT
  295. SELECT as_array(trans_id) INTO t_transaction_ids
  296. FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5);
  297. DELETE FROM acc_trans WHERE trans_id = ANY(t_transaction_ids);
  298. DELETE FROM ap WHERE id = ANY(t_transaction_ids);
  299. DELETE FROM gl WHERE id = ANY(t_transaction_ids);
  300. DELETE FROM voucher WHERE batch_id = in_batch_id;
  301. DELETE FROM payments_queue WHERE batch_id = in_batch_id;
  302. DELETE FROM pending_job WHERE batch_id = in_batch_id;
  303. DELETE FROM batch WHERE id = in_batch_id;
  304. DELETE FROM transactions WHERE id = ANY(t_transaction_ids);
  305. RETURN 1;
  306. END;
  307. $$ language plpgsql SECURITY DEFINER;
  308. REVOKE ALL ON FUNCTION batch_delete(int) FROM PUBLIC;
  309. CREATE OR REPLACE FUNCTION voucher__delete(in_voucher_id int)
  310. RETURNS int AS
  311. $$
  312. DECLARE
  313. voucher_row RECORD;
  314. BEGIN
  315. SELECT * INTO voucher_row FROM voucher WHERE id = in_voucher_id;
  316. IF voucher_row.batch_class IN (1, 2, 5) THEN
  317. DELETE from acc_trans WHERE trans_id = voucher_row.trans_id;
  318. DELETE FROM ar WHERE id = voucher_row.trans_id;
  319. DELETE FROM ap WHERE id = voucher_row.trans_id;
  320. DELETE FROM gl WHERE id = voucher_row.trans_id;
  321. DELETE FROM voucher WHERE id = voucher_row.id;
  322. DELETE FROM transactions WHERE id = voucher_row.trans_id;
  323. ELSE
  324. update ar set paid = amount +
  325. (select sum(amount) from acc_trans
  326. join chart ON (acc_trans.chart_id = chart.id)
  327. where link = 'AR' AND trans_id = ar.id
  328. AND (voucher_id IS NULL
  329. OR voucher_id <> voucher_row.id))
  330. where id in (select trans_id from acc_trans
  331. where voucher_id = voucher_row.id);
  332. update ap set paid = amount - (select sum(amount) from acc_trans
  333. join chart ON (acc_trans.chart_id = chart.id)
  334. where link = 'AP' AND trans_id = ap.id
  335. AND (voucher_id IS NULL
  336. OR voucher_id <> voucher_row.id))
  337. where id in (select trans_id from acc_trans
  338. where voucher_id = voucher_row.id);
  339. DELETE FROM acc_trans where voucher_id = voucher_row.id;
  340. END IF;
  341. RETURN 1;
  342. END;
  343. $$ LANGUAGE PLPGSQL SECURITY DEFINER;
  344. REVOKE ALL ON FUNCTION voucher__delete(int) FROM public;