summaryrefslogtreecommitdiff
path: root/sql/modules/Company.sql
blob: df6967a454dd6f02127bbb565d9a9c7836cad692 (plain)
  1. -- BEGIN;
  2. CREATE TYPE company_search_result AS (
  3. entity_id int,
  4. company_id int,
  5. entity_credit_id int,
  6. meta_number text,
  7. entity_class int,
  8. legal_name text,
  9. sic_code text,
  10. business_type text,
  11. curr text
  12. );
  13. CREATE OR REPLACE FUNCTION company__search
  14. (in_account_class int, in_contact text, in_contact_info text[],
  15. in_meta_number text, in_address text, in_city text, in_state text,
  16. in_mail_code text, in_country text, in_date_from date, in_date_to date,
  17. in_business_id int, in_legal_name text)
  18. RETURNS SETOF company_search_result AS $$
  19. DECLARE
  20. out_row company_search_result;
  21. loop_count int;
  22. t_contact_info text[];
  23. BEGIN
  24. t_contact_info = in_contact_info;
  25. FOR out_row IN
  26. SELECT e.id, c.id, ec.id, ec.meta_number, ec.entity_class,
  27. c.legal_name, c.sic_code, b.description , ec.curr
  28. FROM entity e
  29. JOIN company c ON (e.id = c.entity_id)
  30. JOIN entity_credit_account ec ON (ec.entity_id = e.id)
  31. LEFT JOIN business b ON (ec.business_id = b.id)
  32. WHERE ec.entity_class = in_account_class
  33. AND (c.id IN (select company_id FROM company_to_contact
  34. WHERE contact LIKE ALL(t_contact_info))
  35. OR '' LIKE ALL(t_contact_info))
  36. AND ec.meta_number =
  37. coalesce(in_meta_number, ec.meta_number)
  38. AND c.legal_name like '%' || coalesce(in_legal_name, '') || '%'
  39. -- AND ((in_address IS NULL AND in_city IS NULL
  40. -- AND in_state IS NULL
  41. -- AND in_country IS NULL)
  42. -- OR (c.id IN
  43. -- (select company_id FROM company_to_location
  44. -- WHERE location_id IN
  45. -- (SELECT id FROM location
  46. -- WHERE line_one
  47. -- ilike '%' ||
  48. -- coalesce(in_address, '')
  49. -- || '%'
  50. -- AND city ILIKE
  51. -- '%' ||
  52. -- coalesce(in_city, '')
  53. -- || '%'
  54. -- AND state ILIKE
  55. -- '%' ||
  56. -- coalesce(in_state, '')
  57. -- || '%'
  58. -- AND mail_code ILIKE
  59. -- '%' ||
  60. -- coalesce(in_mail_code,
  61. -- '')
  62. -- || '%'
  63. -- AND country_id IN
  64. -- (SELECT id FROM country
  65. -- WHERE name LIKE '%' ||
  66. -- in_country ||'%'
  67. -- OR short_name
  68. -- ilike
  69. -- in_country)))))
  70. AND ec.business_id =
  71. coalesce(in_business_id, ec.business_id)
  72. -- AND ec.startdate <= coalesce(in_date_to,
  73. -- ec.startdate)
  74. -- AND ec.enddate >= coalesce(in_date_from, ec.enddate)
  75. LOOP
  76. RETURN NEXT out_row;
  77. END LOOP;
  78. END;
  79. $$ language plpgsql;
  80. CREATE OR REPLACE FUNCTION entity__save_notes(in_entity_id int, in_note text)
  81. RETURNS INT AS
  82. $$
  83. DECLARE out_id int;
  84. BEGIN
  85. -- TODO, change this to create vector too
  86. INSERT INTO entity_note (ref_key, note_class, entity_id, note, vector)
  87. VALUES (in_entity_id, 1, in_entity_id, in_note, '');
  88. SELECT currval('note_id_seq') INTO out_id;
  89. RETURN out_id;
  90. END;
  91. $$ LANGUAGE PLPGSQL;
  92. CREATE OR REPLACE FUNCTION entity_credit_get_id_by_meta_number
  93. (in_meta_number text, in_account_class int)
  94. returns int AS
  95. $$
  96. DECLARE out_credit_id int;
  97. BEGIN
  98. SELECT id INTO out_credit_id
  99. FROM entity_credit_account
  100. WHERE meta_number = in_meta_number
  101. AND entity_class = in_account_class;
  102. RETURN out_credit_id;
  103. END;
  104. $$ LANGUAGE plpgsql;
  105. CREATE OR REPLACE FUNCTION entity_list_contact_class()
  106. RETURNS SETOF contact_class AS
  107. $$
  108. DECLARE out_row RECORD;
  109. BEGIN
  110. FOR out_row IN
  111. SELECT * FROM contact_class ORDER BY id
  112. LOOP
  113. RETURN NEXT out_row;
  114. END LOOP;
  115. END;
  116. $$ language plpgsql;
  117. CREATE TYPE entity_credit_search_return AS (
  118. legal_name text,
  119. id int,
  120. entity_id int,
  121. entity_class int,
  122. discount numeric,
  123. taxincluded bool,
  124. creditlimit numeric,
  125. terms int2,
  126. meta_number text,
  127. business_id int,
  128. language_code text,
  129. pricegroup_id int,
  130. curr char(3),
  131. startdate date,
  132. enddate date,
  133. ar_ap_account_id int,
  134. cash_account_id int,
  135. tax_id text,
  136. threshold numeric
  137. );
  138. COMMENT ON TYPE entity_credit_search_return IS
  139. $$ This may change in 1.4 and should not be relied upon too much $$;
  140. CREATE OR REPLACE FUNCTION entity__retrieve_credit
  141. (in_entity_id int, in_entity_class int)
  142. RETURNS entity_credit_search_return AS
  143. $$
  144. DECLARE out_row entity_credit_search_return;
  145. BEGIN
  146. SELECT c.legal_name, c.id, e.id, ec.entity_class, ec.discount,
  147. ec.taxincluded, ec.creditlimit, ec.terms, ec.meta_number,
  148. ec.business_id, ec.language_code, ec.pricegroup_id,
  149. ec.curr::char(3), ec.startdate, ec.enddate, ec.ar_ap_account_id,
  150. ec.cash_account_id, c.tax_id, ec.threshold
  151. INTO out_row
  152. FROM company c
  153. JOIN entity e ON (c.entity_id = e.id)
  154. JOIN entity_credit_account ec ON (c.entity_id = ec.entity_id)
  155. WHERE e.id = in_entity_id
  156. AND ec.entity_class = CASE WHEN in_entity_class = 3 THEN 2
  157. ELSE in_entity_class END;
  158. RETURN out_row;
  159. END;
  160. $$ LANGUAGE PLPGSQL;
  161. CREATE OR REPLACE FUNCTION entity_credit_save (
  162. in_id int, in_entity_class int,
  163. in_discount numeric, in_taxincluded bool, in_creditlimit numeric,
  164. in_discount_terms int,
  165. in_terms int, in_meta_number varchar(32), in_business_id int,
  166. in_language varchar(6), in_pricegroup_id int,
  167. in_curr char, in_startdate date, in_enddate date,
  168. in_name text, in_tax_id TEXT,
  169. in_threshold NUMERIC,
  170. in_ar_ap_account_id int,
  171. in_cash_account_id int
  172. ) returns INT as $$
  173. DECLARE
  174. t_entity_class int;
  175. new_entity_id int;
  176. v_row company;
  177. l_id int;
  178. BEGIN
  179. -- TODO: Move every table to an upsert mode independantly.
  180. SELECT INTO v_row * FROM company WHERE legal_name = in_name;
  181. IF NOT FOUND THEN
  182. -- do some inserts
  183. select nextval('entity_id_seq') into new_entity_id;
  184. insert into entity (id, name, entity_class)
  185. VALUES (new_entity_id, in_name, in_entity_class);
  186. INSERT INTO company ( entity_id, legal_name, tax_id )
  187. VALUES ( new_entity_id, in_name, in_tax_id );
  188. IF in_entity_class NOT IN (1, 2) THEN
  189. RETURN new_entity_id;
  190. END IF;
  191. INSERT INTO entity_credit_account (
  192. entity_id,
  193. entity_class,
  194. discount,
  195. taxincluded,
  196. creditlimit,
  197. terms,
  198. meta_number,
  199. business_id,
  200. language_code,
  201. pricegroup_id,
  202. curr,
  203. startdate,
  204. enddate,
  205. discount_terms,
  206. threshold,
  207. ar_ap_account_id,
  208. cash_account_id
  209. )
  210. VALUES (
  211. new_entity_id,
  212. in_entity_class,
  213. in_discount / 100,
  214. in_taxincluded,
  215. in_creditlimit,
  216. in_terms,
  217. in_meta_number,
  218. in_business_id,
  219. in_language,
  220. in_pricegroup_id,
  221. in_curr,
  222. in_startdate,
  223. in_enddate,
  224. in_discount_terms,
  225. in_threshold,
  226. in_ar_ap_account_id,
  227. in_cash_account_id
  228. );
  229. -- entity note class
  230. return new_entity_id;
  231. ELSIF FOUND THEN
  232. update company set tax_id = in_tax_id where id = v_row.id;
  233. update entity_credit_account SET
  234. discount = in_discount,
  235. taxincluded = in_taxincluded,
  236. creditlimit = in_creditlimit,
  237. terms = in_terms,
  238. ar_ap_account_id = in_ar_ap_account_id,
  239. cash_account_id = in_cash_account_id,
  240. meta_number = in_meta_number,
  241. business_id = in_business_id,
  242. language_code = in_language,
  243. pricegroup_id = in_pricegroup_id,
  244. curr = in_curr,
  245. startdate = in_startdate,
  246. enddate = in_enddate,
  247. threshold = in_threshold,
  248. discount_terms = in_discount_terms
  249. where entity_id = v_row.entity_id;
  250. return v_row.entity_id;
  251. END IF;
  252. END;
  253. $$ language 'plpgsql';
  254. CREATE OR REPLACE FUNCTION company__list_locations(in_entity_id int)
  255. RETURNS SETOF location_result AS
  256. $$
  257. DECLARE out_row RECORD;
  258. BEGIN
  259. FOR out_row IN
  260. SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
  261. l.state, l.mail_code, c.name, lc.class
  262. FROM location l
  263. JOIN company_to_location ctl ON (ctl.location_id = l.id)
  264. JOIN company cp ON (ctl.company_id = cp.id)
  265. JOIN location_class lc ON (ctl.location_class = lc.id)
  266. JOIN country c ON (c.id = l.country_id)
  267. WHERE cp.entity_id = in_entity_id
  268. ORDER BY lc.id, l.id, c.name
  269. LOOP
  270. RETURN NEXT out_row;
  271. END LOOP;
  272. END;
  273. $$ LANGUAGE PLPGSQL;
  274. CREATE TYPE contact_list AS (
  275. class text,
  276. description text,
  277. contact text
  278. );
  279. CREATE OR REPLACE FUNCTION company__list_contacts(in_entity_id int)
  280. RETURNS SETOF contact_list AS $$
  281. DECLARE out_row contact_list;
  282. BEGIN
  283. FOR out_row IN
  284. SELECT cl.class, c.description, c.contact
  285. FROM company_to_contact c
  286. JOIN contact_class cl ON (c.contact_class_id = cl.id)
  287. WHERE company_id =
  288. (select id FROM company
  289. WHERE entity_id = in_entity_id)
  290. LOOP
  291. return next out_row;
  292. END LOOP;
  293. END;
  294. $$ language plpgsql;
  295. CREATE OR REPLACE FUNCTION company__list_bank_account(in_entity_id int)
  296. RETURNS SETOF entity_bank_account AS
  297. $$
  298. DECLARE out_row entity_bank_account%ROWTYPE;
  299. BEGIN
  300. FOR out_row IN
  301. SELECT * from entity_bank_account where entity_id = in_entity_id
  302. LOOP
  303. RETURN NEXT out_row;
  304. END LOOP;
  305. END;
  306. $$ LANGUAGE PLPGSQL;
  307. CREATE OR REPLACE FUNCTION entity__save_bank_account
  308. (in_entity_id int, in_bic text, in_iban text)
  309. RETURNS int AS
  310. $$
  311. DECLARE out_id int;
  312. BEGIN
  313. INSERT INTO entity_bank_account(entity_id, bic, iban)
  314. VALUES(in_entity_id, in_bic, in_iban);
  315. SELECT CURRVAL('entity_bank_account_id_seq') INTO out_id ;
  316. RETURN out_id;
  317. END;
  318. $$ LANGUAGE PLPGSQL;
  319. CREATE OR REPLACE FUNCTION company__save_contact
  320. (in_entity_id int, in_contact_class int, in_description text, in_contact text)
  321. RETURNS INT AS
  322. $$
  323. DECLARE out_id int;
  324. BEGIN
  325. INSERT INTO company_to_contact(company_id, contact_class_id,
  326. description, contact)
  327. SELECT id, in_contact_class, in_description, in_contact FROM company
  328. WHERE entity_id = in_entity_id;
  329. RETURN 1;
  330. END;
  331. $$ LANGUAGE PLPGSQL;
  332. CREATE TYPE entity_note_list AS (
  333. id int,
  334. note text
  335. );
  336. CREATE OR REPLACE FUNCTION company__list_notes(in_entity_id int)
  337. RETURNS SETOF entity_note AS
  338. $$
  339. DECLARE out_row record;
  340. BEGIN
  341. FOR out_row IN
  342. SELECT *
  343. FROM entity_note
  344. WHERE ref_key = in_entity_id
  345. LOOP
  346. RETURN NEXT out_row;
  347. END LOOP;
  348. END;
  349. $$ LANGUAGE PLPGSQL;
  350. CREATE OR REPLACE FUNCTION company__next_id() returns bigint as $$
  351. select nextval('company_id_seq');
  352. $$ language 'sql';
  353. CREATE OR REPLACE FUNCTION company__location_save (
  354. in_entity_id int, in_location_id int,
  355. in_location_class int, in_line_one text, in_line_two text,
  356. in_city TEXT, in_state TEXT, in_mail_code text, in_country_code int,
  357. in_created date
  358. ) returns int AS $$
  359. BEGIN
  360. return _entity_location_save(
  361. in_entity_id, in_location_id,
  362. in_location_class, in_line_one, in_line_two,
  363. '', in_city , in_state, in_mail_code, in_country_code);
  364. END;
  365. $$ language 'plpgsql';
  366. create or replace function _entity_location_save(
  367. in_entity_id int, in_location_id int,
  368. in_location_class int, in_line_one text, in_line_two text,
  369. in_line_three text, in_city TEXT, in_state TEXT, in_mail_code text,
  370. in_country_code int
  371. ) returns int AS $$
  372. DECLARE
  373. l_row location;
  374. l_id INT;
  375. t_company_id int;
  376. BEGIN
  377. SELECT id INTO t_company_id
  378. FROM company WHERE entity_id = in_entity_id;
  379. DELETE FROM company_to_location
  380. WHERE company_id = t_company_id
  381. AND location_class = in_location_class
  382. AND location_id = in_location_id;
  383. SELECT location_save(in_line_one, in_line_two, in_line_three, in_city,
  384. in_state, in_mail_code, in_country_code)
  385. INTO l_id;
  386. INSERT INTO company_to_location
  387. (company_id, location_class, location_id)
  388. VALUES (t_company_id, in_location_class, l_id);
  389. RETURN l_id;
  390. END;
  391. $$ language 'plpgsql';
  392. -- COMMIT;