/packages/intranet-trans-invoices/sql/postgresql/upgrade/upgrade-3.2.9.0.0-3.3.0.0.0.sql

https://github.com/cognovis/project-open · SQL · 276 lines · 235 code · 36 blank · 5 comment · 0 complexity · 02e34ad7846b26ca3841bbfe94272288 MD5 · raw file

  1. -- upgrade-3.2.9.0.0-3.3.0.0.0.sql
  2. SELECT acs_log__debug('/packages/intranet-trans-invoices/sql/postgresql/upgrade/upgrade-3.2.9.0.0-3.3.0.0.0.sql','');
  3. create or replace function inline_0 ()
  4. returns integer as '
  5. DECLARE
  6. v_count integer;
  7. BEGIN
  8. select count(*) into v_count from user_tab_columns
  9. where lower(table_name) = ''im_trans_prices'' and lower(column_name) = ''file_type_id'';
  10. IF v_count > 0 THEN return 0; END IF;
  11. alter table im_trans_prices add file_type_id integer
  12. constraint im_trans_prices_file_type_fk references im_categories;
  13. return 0;
  14. end;' language 'plpgsql';
  15. select inline_0();
  16. drop function inline_0();
  17. create or replace function inline_0 ()
  18. returns integer as '
  19. DECLARE
  20. v_count integer;
  21. BEGIN
  22. select count(*) into v_count from user_tab_columns
  23. where lower(table_name) = ''im_trans_prices'' and lower(column_name) = ''min_price'';
  24. IF v_count > 0 THEN return 0; END IF;
  25. alter table im_trans_prices add min_price numeric(12,4);
  26. return 0;
  27. end;' language 'plpgsql';
  28. select inline_0();
  29. drop function inline_0();
  30. -- Create a new index to incorporate file_type
  31. create or replace function inline_0 ()
  32. returns integer as '
  33. DECLARE
  34. v_count integer;
  35. BEGIN
  36. select count(*) into v_count from user_tab_columns
  37. where lower(table_name) = ''im_trans_price_idx'';
  38. IF v_count = 0 THEN return 0; END IF;
  39. drop index im_trans_price_idx;
  40. return 0;
  41. end;' language 'plpgsql';
  42. select inline_0();
  43. drop function inline_0();
  44. -- make sure the same price doesn't get defined twice
  45. create unique index im_trans_price_idx on im_trans_prices (
  46. uom_id, company_id, task_type_id, target_language_id,
  47. source_language_id, subject_area_id, file_type_id, currency
  48. );
  49. SELECT im_category_new (600, 'MS-Word', 'Intranet Translation File Type');
  50. update im_categories set aux_string1 = 'doc' where category_id = 600;
  51. SELECT im_category_new (602, 'MS-Excel', 'Intranet Translation File Type');
  52. update im_categories set aux_string1 = 'xls' where category_id = 602;
  53. SELECT im_category_new (604, 'MS-PowerPoint', 'Intranet Translation File Type');
  54. update im_categories set aux_string1 = 'ppt' where category_id = 604;
  55. create or replace function im_file_type_from_trans_task (integer)
  56. returns integer as '
  57. DECLARE
  58. p_task_id alias for $1;
  59. v_task_name varchar;
  60. v_extension varchar;
  61. v_result integer;
  62. BEGIN
  63. select task_filename into v_task_name
  64. from im_trans_tasks
  65. where task_id = p_task_id;
  66. v_extension := lower(substring(v_task_name from length(v_task_name)-2));
  67. -- RAISE NOTICE ''%'', v_extension;
  68. select min(category_id) into v_result
  69. from im_categories
  70. where category_type = ''Intranet Translation File Type''
  71. and aux_string1 = v_extension;
  72. return v_result;
  73. end;' language 'plpgsql';
  74. -- Compatibility with previous version
  75. create or replace function im_trans_prices_calc_relevancy (
  76. integer, integer, integer, integer, integer, integer, integer, integer, integer, integer
  77. ) returns numeric as '
  78. DECLARE
  79. v_price_company_id alias for $1;
  80. v_item_company_id alias for $2;
  81. v_price_task_type_id alias for $3;
  82. v_item_task_type_id alias for $4;
  83. v_price_subject_area_id alias for $5;
  84. v_item_subject_area_id alias for $6;
  85. v_price_target_language_id alias for $7;
  86. v_item_target_language_id alias for $8;
  87. v_price_source_language_id alias for $9;
  88. v_item_source_language_id alias for $10;
  89. BEGIN
  90. return im_trans_prices_calc_relevancy(
  91. v_price_company_id,
  92. v_item_company_id,
  93. v_price_task_type_id,
  94. v_item_task_type_id,
  95. v_price_subject_area_id,
  96. v_item_subject_area_id,
  97. v_price_target_language_id,
  98. v_item_target_language_id,
  99. v_price_source_language_id,
  100. v_item_source_language_id,
  101. 0, 0
  102. );
  103. end;' language 'plpgsql';
  104. -- New procedure with added filetype
  105. create or replace function im_trans_prices_calc_relevancy (
  106. integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer, integer
  107. ) returns numeric as '
  108. DECLARE
  109. v_price_company_id alias for $1;
  110. v_item_company_id alias for $2;
  111. v_price_task_type_id alias for $3;
  112. v_item_task_type_id alias for $4;
  113. v_price_subject_area_id alias for $5;
  114. v_item_subject_area_id alias for $6;
  115. v_price_target_language_id alias for $7;
  116. v_item_target_language_id alias for $8;
  117. v_price_source_language_id alias for $9;
  118. v_item_source_language_id alias for $10;
  119. v_price_file_type_id alias for $11;
  120. v_item_file_type_id alias for $12;
  121. match_value numeric;
  122. v_internal_company_id integer;
  123. v_price_target_language varchar(100);
  124. v_item_target_language varchar(100);
  125. v_price_source_language varchar(100);
  126. v_item_source_language varchar(100);
  127. BEGIN
  128. match_value := 0;
  129. select company_id
  130. into v_internal_company_id
  131. from im_companies
  132. where company_path=''internal'';
  133. -- Hard matches for task type
  134. if v_price_task_type_id = v_item_task_type_id then
  135. match_value := match_value + 8;
  136. end if;
  137. if not(v_price_task_type_id is null) and v_price_task_type_id != v_item_task_type_id then
  138. match_value := match_value - 8;
  139. end if;
  140. -- Default matching for source language:
  141. -- "de" <-> "de_DE" = + 1
  142. -- "de_DE" <-> "de_DE" = +3
  143. -- "es" <-> "de_DE" = -10
  144. if (v_price_source_language_id is not null) and (v_item_source_language_id is not null) then
  145. -- only add or subtract match_values if both are defined...
  146. select category
  147. into v_price_source_language
  148. from im_categories
  149. where category_id = v_price_source_language_id;
  150. select category
  151. into v_item_source_language
  152. from im_categories
  153. where category_id = v_item_source_language_id;
  154. if substr(v_price_source_language,1,2) = substr(v_item_source_language,1,2) then
  155. -- the main part of the language have matched
  156. match_value := match_value + 2;
  157. if v_price_source_language_id = v_item_source_language_id then
  158. -- the main part have matched and the country variants are the same
  159. match_value := match_value + 1;
  160. end if;
  161. else
  162. match_value := match_value - 20;
  163. end if;
  164. end if;
  165. -- Default matching for target language:
  166. if (v_price_target_language_id is not null) and (v_item_target_language_id is not null) then
  167. -- only add or subtract match_values if both are defined...
  168. select category
  169. into v_price_target_language
  170. from im_categories
  171. where category_id = v_price_target_language_id;
  172. select category
  173. into v_item_target_language
  174. from im_categories
  175. where category_id = v_item_target_language_id;
  176. if substr(v_price_target_language,1,2) = substr(v_item_target_language,1,2) then
  177. -- the main part of the language have matched
  178. match_value := match_value + 1;
  179. if v_price_target_language_id = v_item_target_language_id then
  180. -- the main part have matched and the country variants are the same
  181. match_value := match_value + 1;
  182. end if;
  183. else
  184. match_value := match_value - 20;
  185. end if;
  186. end if;
  187. -- Subject Area
  188. if v_price_subject_area_id = v_item_subject_area_id then
  189. match_value := match_value + 1;
  190. end if;
  191. if not(v_price_subject_area_id is null) and v_price_subject_area_id != v_item_subject_area_id then
  192. match_value := match_value - 20;
  193. end if;
  194. -- Company logic - "Internal" doesnt give a penalty
  195. -- but doesnt count as high as an exact match
  196. --
  197. if v_price_company_id = v_item_company_id then
  198. match_value := (match_value + 6)*2;
  199. end if;
  200. if v_price_company_id = v_internal_company_id then
  201. match_value := match_value + 1;
  202. end if;
  203. if v_price_company_id != v_internal_company_id and v_price_company_id != v_item_company_id then
  204. match_value := match_value -100;
  205. end if;
  206. -- File Type
  207. if v_price_file_type_id = v_item_file_type_id then
  208. match_value := match_value + 1;
  209. end if;
  210. if not(v_price_file_type_id is null) and v_price_file_type_id != v_item_file_type_id then
  211. match_value := match_value - 10;
  212. end if;
  213. return match_value;
  214. end;' language 'plpgsql';