summaryrefslogtreecommitdiff
path: root/sql/Pg-functions.sql
blob: ee70b0e70d4ad76a0a1baad4c046d7e467bf5420 (plain)
  1. --
  2. CREATE FUNCTION del_yearend() RETURNS OPAQUE AS '
  3. begin
  4. delete from yearend where trans_id = old.id;
  5. return NULL;
  6. end;
  7. ' language 'plpgsql';
  8. -- end function
  9. --
  10. CREATE TRIGGER del_yearend AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_yearend();
  11. -- end trigger
  12. --
  13. CREATE FUNCTION del_department() RETURNS OPAQUE AS '
  14. begin
  15. delete from dpt_trans where trans_id = old.id;
  16. return NULL;
  17. end;
  18. ' language 'plpgsql';
  19. -- end function
  20. --
  21. CREATE TRIGGER del_department AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_department();
  22. -- end trigger
  23. CREATE TRIGGER del_department AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_department();
  24. -- end trigger
  25. CREATE TRIGGER del_department AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_department();
  26. -- end trigger
  27. CREATE TRIGGER del_department AFTER DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_department();
  28. -- end trigger
  29. --
  30. CREATE FUNCTION del_customer() RETURNS OPAQUE AS '
  31. begin
  32. delete from shipto where trans_id = old.id;
  33. delete from customertax where customer_id = old.id;
  34. delete from partscustomer where customer_id = old.id;
  35. return NULL;
  36. end;
  37. ' language 'plpgsql';
  38. -- end function
  39. --
  40. CREATE TRIGGER del_customer AFTER DELETE ON customer FOR EACH ROW EXECUTE PROCEDURE del_customer();
  41. -- end trigger
  42. --
  43. CREATE FUNCTION del_vendor() RETURNS OPAQUE AS '
  44. begin
  45. delete from shipto where trans_id = old.id;
  46. delete from vendortax where vendor_id = old.id;
  47. delete from partsvendor where vendor_id = old.id;
  48. return NULL;
  49. end;
  50. ' language 'plpgsql';
  51. -- end function
  52. --
  53. CREATE TRIGGER del_vendor AFTER DELETE ON vendor FOR EACH ROW EXECUTE PROCEDURE del_vendor();
  54. -- end trigger
  55. --
  56. CREATE FUNCTION del_exchangerate() RETURNS OPAQUE AS '
  57. declare
  58. t_transdate date;
  59. t_curr char(3);
  60. t_id int;
  61. d_curr text;
  62. begin
  63. select into d_curr substr(curr,1,3) from defaults;
  64. if TG_RELNAME = ''ar'' then
  65. select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  66. end if;
  67. if TG_RELNAME = ''ap'' then
  68. select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  69. end if;
  70. if TG_RELNAME = ''oe'' then
  71. select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  72. end if;
  73. if d_curr != t_curr then
  74. select into t_id a.id from acc_trans ac
  75. join ar a on (a.id = ac.trans_id)
  76. where a.curr = t_curr
  77. and ac.transdate = t_transdate
  78. except select a.id from ar a where a.id = old.id
  79. union
  80. select a.id from acc_trans ac
  81. join ap a on (a.id = ac.trans_id)
  82. where a.curr = t_curr
  83. and ac.transdate = t_transdate
  84. except select a.id from ap a where a.id = old.id
  85. union
  86. select o.id from oe o
  87. where o.curr = t_curr
  88. and o.transdate = t_transdate
  89. except select o.id from oe o where o.id = old.id;
  90. if not found then
  91. delete from exchangerate where curr = t_curr and transdate = t_transdate;
  92. end if;
  93. end if;
  94. return old;
  95. end;
  96. ' language 'plpgsql';
  97. -- end function
  98. --
  99. CREATE TRIGGER del_exchangerate BEFORE DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  100. -- end trigger
  101. --
  102. CREATE TRIGGER del_exchangerate BEFORE DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  103. -- end trigger
  104. --
  105. CREATE TRIGGER del_exchangerate BEFORE DELETE ON oe FOR EACH ROW EXECUTE PROCEDURE del_exchangerate();
  106. -- end trigger
  107. --
  108. CREATE FUNCTION check_inventory() RETURNS OPAQUE AS '
  109. declare
  110. itemid int;
  111. row_data inventory%rowtype;
  112. begin
  113. if not old.quotation then
  114. for row_data in select * from inventory where trans_id = old.id loop
  115. select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
  116. if itemid is null then
  117. delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
  118. end if;
  119. end loop;
  120. end if;
  121. return old;
  122. end;
  123. ' language 'plpgsql';
  124. -- end function
  125. --
  126. CREATE TRIGGER check_inventory AFTER UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_inventory();
  127. -- end trigger
  128. --
  129. --
  130. CREATE FUNCTION check_department() RETURNS OPAQUE AS '
  131. declare
  132. dpt_id int;
  133. begin
  134. if new.department_id = 0 then
  135. delete from dpt_trans where trans_id = new.id;
  136. return NULL;
  137. end if;
  138. select into dpt_id trans_id from dpt_trans where trans_id = new.id;
  139. if dpt_id > 0 then
  140. update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
  141. else
  142. insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
  143. end if;
  144. return NULL;
  145. end;
  146. ' language 'plpgsql';
  147. -- end function
  148. --
  149. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ar FOR EACH ROW EXECUTE PROCEDURE check_department();
  150. -- end trigger
  151. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON ap FOR EACH ROW EXECUTE PROCEDURE check_department();
  152. -- end trigger
  153. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON gl FOR EACH ROW EXECUTE PROCEDURE check_department();
  154. -- end trigger
  155. CREATE TRIGGER check_department AFTER INSERT OR UPDATE ON oe FOR EACH ROW EXECUTE PROCEDURE check_department();
  156. -- end trigger
  157. --
  158. CREATE FUNCTION del_recurring() returns opaque as '
  159. BEGIN
  160. DELETE FROM recurring WHERE id = old.id;
  161. DELETE FROM recurringemail WHERE id = old.id;
  162. DELETE FROM recurringprint WHERE id = old.id;
  163. RETURN NULL;
  164. END;
  165. ' language 'plpgsql';
  166. --end function
  167. CREATE TRIGGER del_recurring AFTER DELETE ON ar FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  168. -- end trigger
  169. CREATE TRIGGER del_recurring AFTER DELETE ON ap FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  170. -- end trigger
  171. CREATE TRIGGER del_recurring AFTER DELETE ON gl FOR EACH ROW EXECUTE PROCEDURE del_recurring();
  172. -- end trigger
  173. --
  174. CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
  175. DECLARE
  176. v_cost float;
  177. v_qty float;
  178. v_parts_id alias for $1;
  179. BEGIN
  180. SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
  181. FROM invoice i
  182. JOIN ap a ON (a.id = i.trans_id)
  183. WHERE i.parts_id = v_parts_id;
  184. IF v_cost IS NULL THEN
  185. v_cost := 0;
  186. END IF;
  187. IF NOT v_qty IS NULL THEN
  188. IF v_qty = 0 THEN
  189. v_cost := 0;
  190. ELSE
  191. v_cost := v_cost/v_qty;
  192. END IF;
  193. END IF;
  194. RETURN v_cost;
  195. END;
  196. ' language 'plpgsql';
  197. -- end function
  198. --
  199. CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
  200. DECLARE
  201. v_cost float;
  202. v_parts_id alias for $1;
  203. BEGIN
  204. SELECT INTO v_cost sellprice FROM invoice i
  205. JOIN ap a ON (a.id = i.trans_id)
  206. WHERE i.parts_id = v_parts_id
  207. ORDER BY a.transdate desc, a.id desc
  208. LIMIT 1;
  209. IF v_cost IS NULL THEN
  210. v_cost := 0;
  211. END IF;
  212. RETURN v_cost;
  213. END;
  214. ' language 'plpgsql';
  215. -- end function
  216. --
  217. CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
  218. AS
  219. '
  220. CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
  221. AS
  222. BEGIN
  223. IF NEW.onhand >= NEW.rop THEN
  224. NOTIFY parts_short;
  225. END IF;
  226. RETURN NEW;
  227. END;
  228. ' LANGUAGE PLPGSQL;
  229. CREATE TRIGGER parts_short AFTER UPDATE ON parts
  230. FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
  231. CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
  232. RETURNS BOOL AS
  233. '
  234. DECLARE
  235. table_name ALIAS FOR $1;
  236. new_field_name ALIAS FOR $2;
  237. field_datatype ALIAS FOR $3;
  238. BEGIN
  239. EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
  240. WHERE extends = '''''' || table_name || '''''' '';
  241. IF NOT FOUND THEN
  242. BEGIN
  243. INSERT INTO custom_table_catalog (extends)
  244. VALUES (table_name);
  245. EXECUTE ''CREATE TABLE custom_''||table_name ||
  246. '' (row_id INT)'';
  247. EXCEPTION WHEN duplicate_table THEN
  248. -- do nothing
  249. END;
  250. END IF;
  251. EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
  252. VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
  253. WHERE extends = ''''''|| table_name || ''''''))'';
  254. EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
  255. || new_field_name || '' '' || field_datatype;
  256. RETURN TRUE;
  257. END;
  258. ' LANGUAGE PLPGSQL;
  259. CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
  260. RETURNS BOOL AS
  261. '
  262. DECLARE
  263. table_name ALIAS FOR $1;
  264. custom_field_name ALIAS FOR $2;
  265. BEGIN
  266. DELETE FROM custom_field_catalog
  267. WHERE field_name = custom_field_name AND
  268. table_id = (SELECT table_id FROM custom_table_catalog
  269. WHERE extends = table_name);
  270. EXECUTE ''ALTER TABLE custom_'' || table_name ||
  271. '' DROP COLUMN '' || custom_field_name;
  272. RETURN TRUE;
  273. END;
  274. ' LANGUAGE PLPGSQL;