summaryrefslogtreecommitdiff
path: root/sql/modules/Voucher.sql
blob: f4a30595b512fce3dcf0e04e71790a556b8deb2d (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_get_class_id (in_type text) returns int AS
  205. $$
  206. SELECT id FROM batch_class WHERE class = $1;
  207. $$ language sql;
  208. CREATE OR REPLACE FUNCTION
  209. batch_search_mini
  210. (in_class_id int, in_description text, in_created_by_eid int, in_approved bool)
  211. RETURNS SETOF batch_list_item AS
  212. $$
  213. DECLARE out_value batch_list_item;
  214. BEGIN
  215. FOR out_value IN
  216. SELECT b.id, c.class, b.control_code, b.description, u.username,
  217. b.created_on, NULL
  218. FROM batch b
  219. JOIN batch_class c ON (b.batch_class_id = c.id)
  220. LEFT JOIN users u ON (u.entity_id = b.created_by)
  221. WHERE (c.id = in_class_id OR in_class_id IS NULL) AND
  222. (b.description LIKE
  223. '%' || in_description || '%' OR
  224. in_description IS NULL) AND
  225. (in_created_by_eid = b.created_by OR
  226. in_created_by_eid IS NULL) AND
  227. ((in_approved = false OR in_approved IS NULL AND
  228. approved_on IS NULL) OR
  229. (in_approved = true AND approved_on IS NOT NULL)
  230. )
  231. GROUP BY b.id, c.class, b.description, u.username, b.created_on,
  232. b.control_code
  233. LOOP
  234. RETURN NEXT out_value;
  235. END LOOP;
  236. END;
  237. $$ LANGUAGE PLPGSQL;
  238. CREATE OR REPLACE FUNCTION batch_post(in_batch_id INTEGER)
  239. returns date AS
  240. $$
  241. BEGIN
  242. UPDATE ar SET approved = true
  243. WHERE id IN (select trans_id FROM voucher
  244. WHERE batch_id = in_batch_id
  245. AND batch_class = 2);
  246. UPDATE ap SET approved = true
  247. WHERE id IN (select trans_id FROM voucher
  248. WHERE batch_id = in_batch_id
  249. AND batch_class = 1);
  250. UPDATE gl SET approved = true
  251. WHERE id IN (select trans_id FROM voucher
  252. WHERE batch_id = in_batch_id
  253. AND batch_class = 5);
  254. UPDATE acc_trans SET approved = true
  255. WHERE voucher_id IN (select id FROM voucher
  256. WHERE batch_id = in_batch_id
  257. AND batch_class IN (3, 4, 7, 8));
  258. UPDATE batch
  259. SET approved_on = now(),
  260. approved_by = (select entity_id FROM users
  261. WHERE username = SESSION_USER)
  262. WHERE id = in_batch_id;
  263. RETURN now()::date;
  264. END;
  265. $$ LANGUAGE PLPGSQL;
  266. CREATE OR REPLACE FUNCTION batch_list_classes() RETURNS SETOF batch_class AS
  267. $$
  268. DECLARE out_val record;
  269. BEGIN
  270. FOR out_val IN select * from batch_class
  271. LOOP
  272. return next out_val;
  273. END LOOP;
  274. END;
  275. $$ language plpgsql;
  276. CREATE OR REPLACE FUNCTION batch_get_users() RETURNS SETOF users AS
  277. $$
  278. DECLARE out_record users%ROWTYPE;
  279. BEGIN
  280. FOR out_record IN
  281. SELECT * from users WHERE entity_id IN (select created_by from batch)
  282. LOOP
  283. RETURN NEXT out_record;
  284. END LOOP;
  285. END;
  286. $$ LANGUAGE PLPGSQL;
  287. CREATE OR REPLACE FUNCTION batch_create(
  288. in_batch_number text, in_description text, in_batch_class text) RETURNS int AS
  289. $$
  290. BEGIN
  291. INSERT INTO
  292. batch (batch_class_id, description, control_code, created_by)
  293. VALUES ((SELECT id FROM batch_class WHERE class = in_batch_class),
  294. in_description, in_batch_number,
  295. (select entity_id FROM users WHERE username = session_user));
  296. return currval('batch_id_seq');
  297. END;
  298. $$ LANGUAGE PLPGSQL;
  299. CREATE OR REPLACE FUNCTION batch_delete(in_batch_id int) RETURNS int AS
  300. $$
  301. DECLARE
  302. t_transaction_ids int[];
  303. BEGIN
  304. -- Adjust AR/AP tables for payment and payment reversal vouchers
  305. -- voucher_id is only set in acc_trans on payment/receipt vouchers and
  306. -- their reversals. -CT
  307. update ar set paid = amount +
  308. (select sum(amount) from acc_trans
  309. join chart ON (acc_trans.chart_id = chart.id)
  310. where link = 'AR' AND trans_id = ar.id
  311. AND (voucher_id IS NULL OR voucher_id NOT IN
  312. (select id from voucher
  313. WHERE batch_id = in_batch_id)))
  314. where id in (select trans_id from acc_trans where voucher_id IN
  315. (select id from voucher where batch_id = in_batch_id));
  316. update ap set paid = amount - (select sum(amount) from acc_trans
  317. join chart ON (acc_trans.chart_id = chart.id)
  318. where link = 'AP' AND trans_id = ap.id
  319. AND (voucher_id IS NULL OR voucher_id NOT IN
  320. (select id from voucher
  321. WHERE batch_id = in_batch_id)))
  322. where id in (select trans_id from acc_trans where voucher_id IN
  323. (select id from voucher where batch_id = in_batch_id));
  324. DELETE FROM acc_trans WHERE voucher_id IN
  325. (select id FROM voucher where batch_id = in_batch_id);
  326. -- The rest of this function involves the deletion of actual
  327. -- transactions, vouchers, and batches, and jobs which are in progress.
  328. -- -CT
  329. SELECT as_array(trans_id) INTO t_transaction_ids
  330. FROM voucher WHERE batch_id = in_batch_id AND batch_class IN (1, 2, 5);
  331. DELETE FROM acc_trans WHERE trans_id = ANY(t_transaction_ids);
  332. DELETE FROM ap WHERE id = ANY(t_transaction_ids);
  333. DELETE FROM gl WHERE id = ANY(t_transaction_ids);
  334. DELETE FROM voucher WHERE batch_id = in_batch_id;
  335. DELETE FROM payments_queue WHERE batch_id = in_batch_id;
  336. DELETE FROM pending_job WHERE batch_id = in_batch_id;
  337. DELETE FROM batch WHERE id = in_batch_id;
  338. DELETE FROM transactions WHERE id = ANY(t_transaction_ids);
  339. RETURN 1;
  340. END;
  341. $$ language plpgsql SECURITY DEFINER;
  342. REVOKE ALL ON FUNCTION batch_delete(int) FROM PUBLIC;
  343. CREATE OR REPLACE FUNCTION voucher__delete(in_voucher_id int)
  344. RETURNS int AS
  345. $$
  346. DECLARE
  347. voucher_row RECORD;
  348. BEGIN
  349. SELECT * INTO voucher_row FROM voucher WHERE id = in_voucher_id;
  350. IF voucher_row.batch_class IN (1, 2, 5) THEN
  351. DELETE from acc_trans WHERE trans_id = voucher_row.trans_id;
  352. DELETE FROM ar WHERE id = voucher_row.trans_id;
  353. DELETE FROM ap WHERE id = voucher_row.trans_id;
  354. DELETE FROM gl WHERE id = voucher_row.trans_id;
  355. DELETE FROM voucher WHERE id = voucher_row.id;
  356. DELETE FROM transactions WHERE id = voucher_row.trans_id;
  357. ELSE
  358. update ar set paid = amount +
  359. (select sum(amount) from acc_trans
  360. join chart ON (acc_trans.chart_id = chart.id)
  361. where link = 'AR' AND trans_id = ar.id
  362. AND (voucher_id IS NULL
  363. OR voucher_id <> voucher_row.id))
  364. where id in (select trans_id from acc_trans
  365. where voucher_id = voucher_row.id);
  366. update ap set paid = amount - (select sum(amount) from acc_trans
  367. join chart ON (acc_trans.chart_id = chart.id)
  368. where link = 'AP' AND trans_id = ap.id
  369. AND (voucher_id IS NULL
  370. OR voucher_id <> voucher_row.id))
  371. where id in (select trans_id from acc_trans
  372. where voucher_id = voucher_row.id);
  373. DELETE FROM acc_trans where voucher_id = voucher_row.id;
  374. END IF;
  375. RETURN 1;
  376. END;
  377. $$ LANGUAGE PLPGSQL SECURITY DEFINER;
  378. REVOKE ALL ON FUNCTION voucher__delete(int) FROM public;