summaryrefslogtreecommitdiff
path: root/sql/modules/Voucher.sql
blob: 6ed8fa7d98c1488b7e71b7796cada08d6fd685be (plain)
  1. CREATE OR REPLACE FUNCTION voucher_get_batch (in_batch_id integer)
  2. RETURNS batches AS
  3. $$
  4. DECLARE
  5. batch_out batches%ROWTYPE;
  6. BEGIN
  7. SELECT * INTO batch_out FROM batches b WHERE b.id = in_batch_id;
  8. RETURN batch_out;
  9. END;
  10. $$ language plpgsql;
  11. CREATE TYPE batch_list AS (
  12. id integer,
  13. batch_number text,
  14. description text,
  15. entered date,
  16. approved date,
  17. amount numeric,
  18. employee text,
  19. manager text);
  20. CREATE FUNCTION batch_search
  21. (in_batch text, in_description text, in_batch_number text, in_date_from date,
  22. in_date_to date, in_date_include date, in_approved boolean)
  23. RETURNS SETOF batch_list
  24. AS $$
  25. DECLARE
  26. batch_out batch_list;
  27. BEGIN
  28. FOR batch_out IN
  29. SELECT b.id, b.batch, b.batch_number, b.description,
  30. b.entered, b.approved, b.amount,
  31. e.name AS employee, m.name AS manager
  32. FROM batches b
  33. LEFT JOIN employees e ON (b.employee_id = e.id)
  34. LEFT JOIN employees m ON (b.managerid = m.id)
  35. WHERE supplied_and_equal(in_batch, b.batch)
  36. AND supplied_and_like(in_description, description)
  37. AND supplied_and_like(in_batch_number, batch_number)
  38. AND supplied_and_later(in_date_from, entered)
  39. AND supplied_and_earlier(in_date_to, entered)
  40. AND (coalesce(in_approved, 'f') = (approved IS NULL))
  41. LOOP
  42. RETURN NEXT batch_out;
  43. END LOOP;
  44. END;
  45. $$ language PLPGSQL;
  46. CREATE FUNCTION batch_post (in_batch text, in_login varchar, in_entered date,
  47. in_batch_number text, in_description text, in_id integer)
  48. RETURNS integer AS
  49. $$
  50. BEGIN
  51. UPDATE batches
  52. SET batch_number = in_batch_number,
  53. description = in_description,
  54. entered = in_entered
  55. WHERE id = in_id;
  56. IF FOUND THEN
  57. RETURN in_id;
  58. END IF;
  59. INSERT INTO batches (batch, employee_id, batch_number, description,
  60. entered)
  61. VALUES (in_batch, (SELECT id FROM employees WHERE login = in_login),
  62. in_batch_number, description);
  63. RETURN currval('id');
  64. END;
  65. $$ LANGUAGE PLPGSQL;
  66. CREATE TYPE voucher_list AS (
  67. id int,
  68. reference text,
  69. description text,
  70. batch_id int,
  71. transaction_id integer,
  72. amount numeric,
  73. transaction_date date,
  74. voucher_number text
  75. );
  76. CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer)
  77. RETURNS SETOF voucher_list AS
  78. $$
  79. DECLARE
  80. voucher_out voucher_list%ROWTYPE;
  81. BEGIN
  82. FOR voucher_out IN SELECT v.id, a.invnumber AS reference,
  83. c.name ||' -- ' || c.vendornumber AS description,
  84. v.batch_id, a.id AS transaction_id,
  85. a.amount, v.voucher_number
  86. FROM vouchers v
  87. JOIN ap a ON (a.id = v.trans_id)
  88. JOIN vendor c ON (c.id = a.vendor_id)
  89. WHERE v.br_id = in_batch_id
  90. LOOP
  91. RETURN NEXT voucher_out;
  92. END LOOP;
  93. END;
  94. $$ LANGUAGE PLPGSQL;
  95. CREATE OR REPLACE FUNCTION voucher_list_payment (in_batch_id integer)
  96. RETURNS SETOF voucher_list AS
  97. $$
  98. DECLARE
  99. voucher_out voucher_list%ROWTYPE;
  100. BEGIN
  101. FOR voucher_out IN SELECT v.id, c.vendornumber AS reference,
  102. c.name AS description, in_batch_id AS batch_id,
  103. v.transaction_id AS transaction_id, sum(ac.amount) AS amount,
  104. v.voucher_number
  105. FROM acc_trans ac
  106. JOIN vouchers v ON (v.id = ac.vr_id AND v.transaction_id = ac.trans_id)
  107. JOIN chart ch ON (ch.id = ac.chart_id)
  108. JOIN ap a ON (a.id = ac.trans_id)
  109. JOIN vendor c ON (c.id = a.vendor_id)
  110. WHERE v.br_id = in_batch_id
  111. AND ch.link LIKE '%AP_paid%'
  112. GROUP BY v.id, c.name, c.vendornumber, v.voucher_number,
  113. a.vendor_id, v.transaction_id
  114. LOOP
  115. RETURN NEXT voucher_out;
  116. END LOOP;
  117. END;
  118. $$ LANGUAGE PLPGSQL;
  119. CREATE OR REPLACE FUNCTION voucher_list_payment_reversal (in_batch_id integer)
  120. RETURNS SETOF voucher_list AS
  121. $$
  122. DECLARE
  123. voucher_out voucher_list%ROWTYPE;
  124. BEGIN
  125. FOR voucher_out IN
  126. SELECT v.id, ac.source AS reference,
  127. c.vendornumber || ' -- ' || c.name AS description,
  128. sum(ac.amount) * -1 AS amount, in_batch_id AS batch_id,
  129. v.transaction_id AS transaction_id, v.voucher_number
  130. FROM acc_trans ac
  131. JOIN vr v ON (v.id = ac.vr_id AND v.trans_id = ac.trans_id)
  132. JOIN chart ch ON (ch.id = ac.chart_id)
  133. JOIN ap a ON (a.id = ac.trans_id)
  134. JOIN vendor c ON (c.id = a.vendor_id)
  135. WHERE vr.br_id = in_batch_id
  136. AND c.link LIKE '%AP_paid%'
  137. GROUP BY v.id, c.name, c.vendornumber, v.voucher_number,
  138. a.vendor_id, ac.source
  139. LOOP
  140. RETURN NEXT voucher_out;
  141. END LOOP;
  142. END;
  143. $$ LANGUAGE PLPGSQL;
  144. CREATE OR REPLACE FUNCTION voucher_list_ap (in_batch_id integer)
  145. RETURNS SETOF voucher_list AS
  146. $$
  147. DECLARE
  148. voucher_out voucher_list%ROWTYPE;
  149. BEGIN
  150. FOR voucher_out IN
  151. SELECT v.id, g.reference, g.description, in_batch_id AS batch_id,
  152. SUM(ac.amount) AS amount, g.id AS transaction_id,
  153. v.vouchernumber
  154. FROM acc_trans ac
  155. JOIN gl g ON (g.id = ac.trans_id)
  156. JOIN vouchers v ON (v.trans_id = g.id)
  157. WHERE v.batch_id = in_batch_id
  158. AND ac.amount >= 0
  159. GROUP BY g.id, g.reference, g.description, v.id,
  160. v.voucher_number
  161. LOOP
  162. RETURN NEXT voucher_out;
  163. END LOOP;
  164. END;
  165. $$ LANGUAGE PLPGSQL;
  166. CREATE OR REPLACE FUNCTION batch_post (in_batch_id integer[], in_batch text,
  167. in_control_amount NUMERIC)
  168. RETURNS BOOL AS
  169. $$
  170. DECLARE
  171. control_amount NUMERIC;
  172. voucher vouchers%ROWTYPE;
  173. incriment NUMERIC;
  174. BEGIN
  175. -- CHECK CONTROL NUMBERS
  176. IF in_batch = 'gl' THEN
  177. SELECT sum(amount) INTO control_amount
  178. FROM acc_trans
  179. WHERE trans_id IN (
  180. SELECT id FROM gl
  181. WHERE coalesce(approved, false) != true)
  182. AND trans_id IN (
  183. SELECT transaction_id FROM vouchers
  184. WHERE batch_id = ANY (in_batch_id))
  185. AND coalesce(approved, false) != true
  186. AND amount > 0
  187. FOR UPDATE;
  188. ELSE IF in_batch like '%payment%' THEN
  189. SELECT sum(ac.amount) INTO control_amount
  190. FROM acc_trans ac
  191. JOIN vouchers v ON (v.transaction_id = ac.trans_id)
  192. WHERE v.batch_id = ANY (in_batch_id)
  193. AND ac.vr_id = v.id
  194. AND coalesce(approved, false) = false
  195. FOR UPDATE;
  196. ELSE
  197. SELECT sum(amount) INTO control_amount
  198. FROM acc_trans
  199. WHERE trans_id IN
  200. (SELECT transaction_id FROM vouchers
  201. WHERE batch_id = ANY (in_batch_id))
  202. AND trans_id IN
  203. (SELECT trans_id FROM ap
  204. WHERE coalesce(approved, false) = false)
  205. AND amount > 0
  206. FOR UPDATE;
  207. END IF;
  208. IF control_amount != in_control_amount THEN
  209. RETURN FALSE;
  210. END IF;
  211. -- TODO: POST TRANSACTIONALLY
  212. IF in_batch like '%payment%' THEN
  213. ELSE
  214. UPDATE acc_trans
  215. SET approved = true
  216. WHERE trans_id IN
  217. (SELECT transaction_id FROM vouchers
  218. WHERE batch_id = ANY (in_batch_id));
  219. IF in_batch = 'gl' THEN
  220. UPDATE gl SET approved = true
  221. WHERE trans_id IN
  222. (SELECT transaction_id FROM vouchers
  223. WHERE batch_id = ANY (in_batch_id));
  224. ELSE
  225. UPDATE ap SET approved = true
  226. WHERE trans_id IN
  227. (SELECT transaction_id FROM vouchers
  228. WHERE batch_id = ANY (in_batch_id));
  229. END IF;
  230. END IF;
  231. RETURN TRUE;
  232. END;
  233. $$ LANGUAGE PLPGSQL;