summaryrefslogtreecommitdiff
path: root/sql/modules/Vendor.sql
blob: aac2f4a22cdf981baec8e63c47b23a53f440d005 (plain)
  1. BEGIN;
  2. CREATE TYPE vendor_search_return AS (
  3. legal_name text,
  4. id int,
  5. entity_id int,
  6. entity_class int,
  7. discount numeric,
  8. taxincluded bool,
  9. creditlimit numeric,
  10. terms int2,
  11. vendornumber int,
  12. cc text,
  13. bcc text,
  14. business_id int,
  15. language_code text,
  16. pricegroup_id int,
  17. curr char,
  18. startdate date,
  19. enddate date,
  20. bic varchar,
  21. iban varchar,
  22. note text
  23. );
  24. CREATE OR REPLACE FUNCTION vendor_save (
  25. in_id int,
  26. in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
  27. in_discount_terms int, in_threshold numeric,
  28. in_terms int, in_vendornumber varchar(32), in_cc text, in_bcc text,
  29. in_business_id int, in_language varchar(6), in_pricegroup_id int,
  30. in_curr char, in_startdate date, in_enddate date,
  31. in_bic text, in_iban text,
  32. in_notes text,
  33. in_name text, in_tax_id TEXT
  34. ) returns INT as $$
  35. -- does not require entity_class, as entity_class is a known given to be 1
  36. DECLARE
  37. t_entity_class int;
  38. new_entity_id int;
  39. v_row company;
  40. l_id int;
  41. BEGIN
  42. t_entity_class := 1;
  43. SELECT INTO v_row * FROM company WHERE id = in_id;
  44. IF NOT FOUND THEN
  45. -- do some inserts
  46. new_entity_id := nextval('entity_id_seq');
  47. insert into entity (id, name, entity_class)
  48. VALUES (new_entity_id, in_name, t_entity_class);
  49. INSERT INTO company ( id, entity_id, legal_name, tax_id )
  50. VALUES ( in_id, new_entity_id, in_name, in_tax_id );
  51. INSERT INTO entity_credit_account (
  52. entity_id,
  53. entity_class,
  54. discount,
  55. taxincluded,
  56. creditlimit,
  57. terms,
  58. cc,
  59. bcc,
  60. business_id,
  61. language_code,
  62. pricegroup_id,
  63. curr,
  64. startdate,
  65. enddate,
  66. meta_number,
  67. discount_terms,
  68. threshold
  69. )
  70. VALUES (
  71. new_entity_id,
  72. t_entity_class,
  73. in_discount,
  74. in_taxincluded,
  75. in_creditlimit,
  76. in_terms,
  77. in_cc,
  78. in_bcc,
  79. in_business_id,
  80. in_language,
  81. in_pricegroup_id,
  82. in_curr,
  83. in_startdate,
  84. in_enddate,
  85. in_vendornumber,
  86. in_discount_terms,
  87. in_threshold
  88. );
  89. INSERT INTO entity_bank_account (
  90. entity_id,
  91. bic,
  92. iban
  93. )
  94. VALUES (
  95. new_entity_id,
  96. in_bic,
  97. in_iban
  98. );
  99. -- entity note class
  100. insert into entity_note (note_class, note, ref_key, vector) VALUES (
  101. 1, in_notes, new_entity_id, '');
  102. ELSIF FOUND THEN
  103. update company set tax_id = in_tax_id where id = in_id;
  104. update entity_credit_account SET
  105. discount = in_discount,
  106. taxincluded = in_taxincluded,
  107. creditlimit = in_creditlimit,
  108. terms = in_terms,
  109. cc = in_cc,
  110. bcc = in_bcc,
  111. business_id = in_business_id,
  112. language_code = in_language,
  113. pricegroup_id = in_pricegroup_id,
  114. curr = in_curr,
  115. startdate = in_startdate,
  116. enddate = in_enddate,
  117. meta_number = in_vendornumber,
  118. threshold = in_threshold,
  119. discount_terms = in_discount_terms
  120. where entity_id = v_row.entity_id;
  121. UPDATE entity_bank_account SET
  122. bic = in_bic,
  123. iban = in_iban
  124. WHERE entity_id = v_row.entity_id;
  125. UPDATE entity_note SET
  126. note = in_note
  127. WHERE ref_key = v_row.entity_id;
  128. END IF;
  129. return in_id;
  130. END;
  131. $$ language 'plpgsql';
  132. CREATE OR REPLACE FUNCTION vendor_location_save (
  133. in_company_id int,
  134. in_location_class int, in_line_one text, in_line_two text,
  135. in_city_province TEXT, in_mail_code text, in_country_code int,
  136. in_created date
  137. ) returns int AS $$
  138. BEGIN
  139. return _entity_location_save(
  140. in_company_id,
  141. in_location_class, in_line_one, in_line_two,
  142. in_city_province , in_mail_code, in_country_code,
  143. in_created);
  144. END;
  145. $$ language 'plpgsql';
  146. create or replace function _entity_location_save(
  147. in_company_id int,
  148. in_location_class int, in_line_one text, in_line_two text,
  149. in_city_province TEXT, in_mail_code text, in_country_code int,
  150. in_created date
  151. ) returns int AS $$
  152. DECLARE
  153. l_row location;
  154. l_id INT;
  155. BEGIN
  156. SELECT l.* INTO l_row FROM location l
  157. JOIN company_to_location ctl ON ctl.location_id = l.id
  158. JOIN company c on ctl.company_id = c.id
  159. where c.id = in_company_id;
  160. IF NOT FOUND THEN
  161. l_id := nextval('location_id_seq');
  162. INSERT INTO location (id, location_class, line_one, line_two,
  163. city_province, country_id, mail_code, created)
  164. VALUES (
  165. l_id,
  166. in_location_class,
  167. in_line_one,
  168. in_line_two,
  169. in_city_province,
  170. in_country_code,
  171. in_mail_code,
  172. in_created
  173. );
  174. INSERT INTO company_to_location (location_id, company_id)
  175. VALUES (l_id, in_company_id);
  176. ELSIF FOUND THEN
  177. l_id := l.id;
  178. update location SET
  179. location_class = in_location_class,
  180. line_one = in_line_one,
  181. line_two = in_line_two,
  182. city_province = in_city_province,
  183. country_id = in_country_code,
  184. mail_code = in_mail_code
  185. WHERE id = l_id;
  186. END IF;
  187. return l_id;
  188. END;
  189. $$ language 'plpgsql';
  190. CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
  191. CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
  192. CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
  193. CREATE INDEX location_address_three_gist__idx ON location USING gist(line_three gist_trgm_ops);
  194. CREATE INDEX location_city_prov_gist_idx ON location USING gist(city_province gist_trgm_ops);
  195. CREATE INDEX entity_name_gist_idx ON entity USING gist(name gist_trgm_ops);
  196. CREATE OR REPLACE FUNCTION vendor_search(in_name TEXT, in_address TEXT,
  197. in_city_prov TEXT)
  198. RETURNS SETOF vendor_search_return AS $$
  199. -- searches vendor name, account number, street address, city, state,
  200. -- other location-based stuff
  201. declare
  202. v_row vendor_search_return;
  203. query text;
  204. begin
  205. for v_row in select c.legal_name, v.* from vendor v
  206. join company c on c.entity_id = v.entity_id
  207. join entity e on e.id = v.entity_id
  208. join company_to_location ctl on c.id = ctl.company_id
  209. join location l on l.id = ctl.location_id
  210. where (
  211. l.line_one % in_address
  212. OR l.line_two % in_address
  213. OR l.line_three % in_address
  214. )
  215. OR l.city_province % in_city_prov
  216. OR (
  217. c.legal_name % in_name
  218. OR e.name % in_name
  219. )
  220. LOOP
  221. RETURN NEXT v_row;
  222. END LOOP;
  223. RETURN;
  224. end;
  225. $$ language 'plpgsql';
  226. CREATE OR REPLACE FUNCTION vendor_retrieve(INT) returns setof vendor as $$
  227. select v.* from vendor v
  228. join company c on c.entity_id = v.entity_id
  229. where v.id = $1;
  230. $$ language 'sql';
  231. COMMIT;
  232. CREATE OR REPLACE FUNCTION vendor_next_vendor_id() returns int as $$
  233. select nextval('company_id_seq');
  234. $$ language 'sql';