summaryrefslogtreecommitdiff
path: root/sql/modules/Drafts.sql
blob: 9e8a95c2baf540372f2b7419b8746cb5fd88d880 (plain)
  1. CREATE TYPE draft_search_result AS (
  2. id int,
  3. transdate date,
  4. reference text,
  5. description text,
  6. amount numeric
  7. );
  8. CREATE OR REPLACE FUNCTION draft__search(in_type text, in_with_accno text,
  9. in_from_date date, in_to_date date, in_amount_le numeric, in_amount_ge numeric)
  10. returns setof draft_search_result AS
  11. $$
  12. DECLARE out_row RECORD;
  13. BEGIN
  14. FOR out_row IN
  15. SELECT trans.id, trans.transdate, trans.reference,
  16. trans.description,
  17. sum(case when in_type = 'ap' AND chart.link = 'AP'
  18. THEN line.amount
  19. WHEN in_type = 'ar' AND chart.link = 'AR'
  20. THEN line.amount * -1
  21. WHEN in_type = 'gl' AND line.amount > 0
  22. THEN line.amount
  23. ELSE 0
  24. END) as amount
  25. FROM (
  26. SELECT id, transdate, reference, description,
  27. approved from gl
  28. WHERE in_type = 'gl'
  29. UNION
  30. SELECT id, transdate, invnumber as reference,
  31. description::text,
  32. approved from ap
  33. WHERE in_type = 'ap'
  34. UNION
  35. SELECT id, transdate, invnumber as reference,
  36. description,
  37. approved from ar
  38. WHERE in_type = 'ar'
  39. ) trans
  40. JOIN acc_trans line ON (trans.id = line.trans_id)
  41. JOIN chart ON (line.chart_id = chart.id)
  42. WHERE (in_from_date IS NULL or trans.transdate >= in_from_date)
  43. AND (in_to_date IS NULL
  44. or trans.transdate <= in_to_date)
  45. AND trans.approved IS FALSE
  46. AND trans.id NOT IN (select trans_id from voucher)
  47. GROUP BY trans.id, trans.transdate, trans.description, trans.reference
  48. HAVING (in_with_accno IS NULL or in_with_accno =
  49. ANY(as_array(chart.accno)))
  50. LOOP
  51. RETURN NEXT out_row;
  52. END LOOP;
  53. END;
  54. $$ language plpgsql;
  55. CREATE OR REPLACE FUNCTION draft_approve(in_id int) returns bool as
  56. $$
  57. declare
  58. t_table text;
  59. begin
  60. SELECT table_name into t_table FROM transactions where id = in_id;
  61. IF (t_table = 'ar') THEN
  62. UPDATE ar set approved = true where id = in_id;
  63. ELSIF (t_table = 'ap') THEN
  64. UPDATE ap set approved = true where id = in_id;
  65. ELSIF (t_table = 'gl') THEN
  66. UPDATE gl set approved = true where id = in_id;
  67. ELSE
  68. raise exception 'Invalid table % in draft_approve for transaction %', t_table, in_id;
  69. END IF;
  70. IF NOT FOUND THEN
  71. RETURN FALSE;
  72. END IF;
  73. UPDATE transactions
  74. SET approved_by =
  75. (select entity_id FROM users
  76. WHERE username = SESSION_USER),
  77. approved_at = now()
  78. WHERE id = in_id;
  79. RETURN TRUE;
  80. END;
  81. $$ LANGUAGE PLPGSQL SECURITY DEFINER;
  82. CREATE OR REPLACE FUNCTION draft_delete(in_id int) returns bool as
  83. $$
  84. declare
  85. t_table text;
  86. begin
  87. SELECT table_name into t_table FROM transactions where id = in_id;
  88. IF t_table = 'ar' THEN
  89. DELETE FROM ar WHERE id = in_id AND approved IS FALSE;
  90. ELSIF t_table = 'ap' THEN
  91. DELETE FROM ap WHERE id = in_id AND approved IS FALSE;
  92. ELSIF t_table = 'gl' THEN
  93. DELETE FROM gl WHERE id = in_id AND approved IS FALSE;
  94. ELSE
  95. raise exception 'Invalid table % in draft_delete for transaction %', t_table, in_id;
  96. END IF;
  97. RETURN FOUND;
  98. END;
  99. $$ LANGUAGE PLPGSQL SECURITY DEFINER;