summaryrefslogtreecommitdiff
path: root/sql/modules/chart.sql
blob: 2e13637108d39c7df15b828d79af9dbbb9a34b80 (plain)
  1. -- TODO: Merge with account.sql -CT
  2. CREATE OR REPLACE FUNCTION chart_list_all()
  3. RETURNS SETOF chart AS
  4. $$
  5. DECLARE out_row chart%ROWTYPE;
  6. BEGIN
  7. FOR out_row IN
  8. SELECT * FROM chart ORDER BY accno
  9. LOOP
  10. RETURN next out_row;
  11. END LOOP;
  12. END;
  13. $$ LANGUAGE PLPGSQL;
  14. CREATE OR REPLACE FUNCTION chart_list_cash(in_account_class int)
  15. RETURNS SETOF chart AS
  16. $$
  17. DECLARE resultrow record;
  18. link_string text;
  19. BEGIN
  20. IF in_account_class = 1 THEN
  21. link_string := '%AP_paid%';
  22. ELSE
  23. link_string := '%AR_paid%';
  24. END IF;
  25. FOR resultrow IN
  26. SELECT * FROM chart
  27. WHERE link LIKE link_string
  28. ORDER BY accno
  29. LOOP
  30. return next resultrow;
  31. END LOOP;
  32. END;
  33. $$ language plpgsql;
  34. COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
  35. $$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;
  36. CREATE OR REPLACE FUNCTION chart_get_ar_ap(in_account_class int)
  37. RETURNS SETOF chart AS
  38. $$
  39. DECLARE out_row chart%ROWTYPE;
  40. BEGIN
  41. IF in_account_class NOT IN (1, 2) THEN
  42. RAISE EXCEPTION 'Bad Account Type';
  43. END IF;
  44. FOR out_row IN
  45. SELECT * FROM chart
  46. WHERE link = CASE WHEN in_account_class = 1 THEN 'AP'
  47. WHEN in_account_class = 2 THEN 'AR'
  48. END
  49. LOOP
  50. RETURN NEXT out_row;
  51. END LOOP;
  52. END;
  53. $$ LANGUAGE PLPGSQL;
  54. COMMENT ON FUNCTION chart_get_ar_ap(in_account_class int) IS
  55. $$ This function returns the cash account acording with in_account_class which must be 1 or 2 $$;
  56. CREATE OR REPLACE FUNCTION chart_list_overpayment(in_account_class int)
  57. RETURNS SETOF chart AS
  58. $$
  59. DECLARE resultrow record;
  60. link_string text;
  61. BEGIN
  62. IF in_account_class = 1 THEN
  63. link_string := '%AP_overpayment%';
  64. ELSE
  65. link_string := '%AR_overpayment%';
  66. END IF;
  67. FOR resultrow IN
  68. SELECT * FROM chart
  69. WHERE link LIKE link_string
  70. ORDER BY accno
  71. LOOP
  72. return next resultrow;
  73. END LOOP;
  74. END;
  75. $$ language plpgsql;
  76. COMMENT ON FUNCTION chart_list_cash(in_account_class int) IS
  77. $$ This function returns the overpayment accounts acording with in_account_class which must be 1 or 2 $$;