summaryrefslogtreecommitdiff
path: root/sql/modules/chart.sql
blob: 53460766a66f2d807fb6c050492a738e0a8c3f09 (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 := '%AR_paid%';
  22. ELSE
  23. link_string := '%AP_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 $$;