80FqM2P7

1create table if not exists vk_parser_dev.export_finance.nalog_export_cache  (subject text, predicate text, object text);
2create table if not exists vk_parser_dev.export_finance.nalog_export_main (subject text, predicate text, object text);
3
4---- Создает триплы в triples_cash ----
5create or replace function triple_fft(p_subject text, p_predicate text, p_object text, v_table text)
6 returns void as $$
7 declare 
8  v_singletonPropertyOf_uri text;
9  v_singletonPropertyOf text :=  'rdf:singletonPropertyOf';
10begin
11 --insert into triples_cash values (p_subject, p_predicate, p_object);
12 execute 'insert into ' || v_table || '_cache values(''' || REPLACE(p_subject, '''', '''''') || ''', ''' || REPLACE(p_predicate, '''', '''''') || ''', ''' || REPLACE(p_object, '''', '''''') || ''')';
13 v_singletonPropertyOf_uri := get_uri_text(concat(p_subject, '_', p_object));
14 execute 'insert into ' || v_table || '_main values(''' || REPLACE(p_subject, '''', '''''') || ''', ''' || REPLACE(v_singletonPropertyOf_uri, '''', '''''') || ''', ''' || REPLACE(p_object, '''', '''''') || ''')';
15 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_singletonPropertyOf_uri, '''', '''''') || ''', ''' || v_singletonPropertyOf || ''', ''' || REPLACE(p_predicate, '''', '''''') || ''')';
16end;
17$$
18language plpgsql;
19
20
21---- Создает тириплы с литералами на разных языкамх ----
22create or replace function create_str_fft(v_subject text, v_predicate text, v_text text, v_table text)
23 returns void as $$
24 declare
25 v_text_ru text;
26 v_text_all text;
27 v_singletonPropertyOf_uri_ru text;
28 v_singletonPropertyOf_uri_all text;
29 v_singletonPropertyOf text :=  'rdf:singletonPropertyOf';
30begin
31 v_text_ru := concat('"', v_text, '"@ru');    
32 v_text_all := concat('"', v_text, '"@all');
33 execute 'insert into ' || v_table || '_cache values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''', ''' || REPLACE(v_text_ru, '''', '''''') || ''')';
34 execute 'insert into ' || v_table || '_cache values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''', ''' || REPLACE(v_text_all, '''', '''''') || ''')';
35 v_singletonPropertyOf_uri_ru := get_uri_text(concat(v_subject, '_', v_text_ru));
36 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_singletonPropertyOf_uri_ru, '''', '''''') || ''', ''' || REPLACE(v_text_ru, '''', '''''') || ''')';
37 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_singletonPropertyOf_uri_ru, '''', '''''') || ''', ''' || v_singletonPropertyOf || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''')';
38 v_singletonPropertyOf_uri_all := get_uri_text(concat(v_subject, '_', v_text_all));
39 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_singletonPropertyOf_uri_all, '''', '''''') || ''', ''' || REPLACE(v_text_all, '''', '''''') || ''')';
40 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_singletonPropertyOf_uri_all, '''', '''''') || ''', ''' || v_singletonPropertyOf || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''')';
41end;
42$$
43language plpgsql;
44
45
46---- Создает тириплы с литералами на языке all ----
47create or replace function create_str_all_fft(v_subject text, v_predicate text, v_text text, v_table text)
48 returns void as $$
49 declare
50 v_text_all text;
51 v_singletonPropertyOf_uri_all text;
52 v_singletonPropertyOf text := 'rdf:singletonPropertyOf';
53begin
54 --insert into triples_cash values (v_object, v_predicate, concat('"', v_text, '"@all'));
55 v_text_all := concat('"', v_text, '"@all');
56 execute 'insert into ' || v_table || '_cache values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''', ''' || REPLACE(v_text_all, '''', '''''') || ''')';
57 v_singletonPropertyOf_uri_all := get_uri_text(concat(v_subject, '_', v_text_all));
58 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_subject, '''', '''''') || ''', ''' || REPLACE(v_singletonPropertyOf_uri_all, '''', '''''') || ''', ''' || REPLACE(v_text_all, '''', '''''') || ''')';
59 execute 'insert into ' || v_table || '_main values(''' || REPLACE(v_singletonPropertyOf_uri_all, '''', '''''') || ''', ''' || v_singletonPropertyOf || ''', ''' || REPLACE(v_predicate, '''', '''''') || ''')';
60end;
61$$
62language plpgsql;
63
64
65create or replace function get_uri_str(p_dataset_name text, p_table_name text, p_id text)
66 returns text as $$
67begin
68 return concat('crm2:nalog_fin_', md5(concat(p_dataset_name, '_', p_table_name, '_', p_id)));
69end;
70$$
71language plpgsql;
72
73---- Создает Дату ----
74create or replace function crate_date(bdate_day int, bdate_month int, bdate_year int, v_table text)
75 returns text as $$
76declare
77 v_date_uri text;
78 v_bdate text;
79 v_bdate_month_format text;
80 v_bdate_day_format text;
81 v_bdate_sortdate text;
82begin
83  if (( bdate_day is not null ) and ( bdate_month is not null ) and ( bdate_year is not null ))  then 
84	  if ( bdate_month < 10 ) then 
85	     v_bdate_month_format := concat('0',bdate_month);
86	    else v_bdate_month_format := bdate_month;
87	  end if;
88	  if ( bdate_day < 10 ) then 
89	     v_bdate_day_format := concat('0',bdate_day);
90	    else v_bdate_day_format := bdate_day;
91	  end if; 
92	  v_bdate = concat(v_bdate_day_format, '.', v_bdate_month_format, '.', bdate_year);
93	  v_date_uri := get_uri_str('vk', 'date', v_bdate);
94	  perform triple_fft(v_date_uri, 'rdf:type', 'crm2:Date', v_table);
95	  perform create_str_all_fft(v_date_uri, 'rdfs:label', v_bdate, v_table);
96	  perform create_str_all_fft(v_date_uri, 'crm2:year', text(bdate_year), v_table);
97	  perform create_str_all_fft(v_date_uri, 'crm2:month', text(bdate_month), v_table);
98	  perform create_str_all_fft(v_date_uri, 'crm2:day', text(bdate_day), v_table);
99	  --sortdate: "1990-10-10T00:00:00+01:00"@all
100	  v_bdate_sortdate := concat(v_bdate_day_format, '-', v_bdate_month_format, '-', bdate_year, 'T00:00:00+01:00');
101	  perform create_str_all_fft(v_date_uri, 'crm2:sortdate', v_bdate_sortdate, v_table);
102 elseif (( bdate_day is not null ) and ( bdate_month is not null ) and ( bdate_year is null ))  then 
103 	  if ( bdate_month < 10 ) then 
104	     v_bdate_month_format := concat('0',bdate_month);
105	    else v_bdate_month_format := bdate_month;
106	  end if;
107	  if ( bdate_day < 10 ) then 
108	     v_bdate_day_format := concat('0',bdate_day);
109	    else v_bdate_day_format := bdate_day;
110	  end if; 
111	  v_bdate = concat(v_bdate_day_format, '.', v_bdate_month_format);
112	  v_date_uri := get_uri_str('finance', 'date', v_bdate);
113	  perform triple_fft(v_date_uri, 'rdf:type', 'crm2:Date', v_table);
114	  perform create_str_all_fft(v_date_uri, 'rdfs:label', v_bdate, v_table);
115	  perform create_str_all_fft(v_date_uri, 'crm2:month', text(bdate_month), v_table);
116	  perform create_str_all_fft(v_date_uri, 'crm2:day', text(bdate_day), v_table);
117 end if;
118return v_date_uri;
119end;
120$$
121language plpgsql;
122
123
124---- Создает тириплы финансовых показателей  ----
125create or replace function ttl_map_finance(v_table text)
126 returns void as $$
127declare
128 v_ooos cursor for 
129 select 
130   n.dohod, n.rashod, n.ogrn
131 from vk_parser_dev.ooo.nalog_dohod_rashod as n
132 limit 100;
133 v_import_date_uri text;
134 v_ooo_uri text;
135 v_index_dohod_uri text;
136 v_index_rashod_uri text;
137
138 v_date_from_uri text;
139 v_date_to_uri text;
140begin
141 truncate vk_parser_dev.export_finance.nalog_export_main;
142 truncate vk_parser_dev.export_finance.nalog_export_cache;
143 v_date_from_uri :=  crate_date(1, 1, 2017, v_table);
144 v_date_to_uri :=  crate_date(31, 12, 2017, v_table);
145  perform triple_fft('crm2:fin_revenue', 'rdf:type', 'crm2:IndexType', v_table);
146  perform create_str_fft('crm2:fin_revenue', 'rdfs:label', 'Доход', v_table);
147  perform triple_fft('crm2:fin_spending', 'rdf:type', 'crm2:IndexType', v_table);
148  perform create_str_fft('crm2:fin_revenue', 'rdfs:label', 'Расход', v_table);
149 for v_ooo in v_ooos loop
150   --CONTINUE WHEN v_user.first_name LIKE '%"%' or v_user.last_name LIKE '%"%';
151  
152   v_ooo_uri := concat('vk', v_ooo.ogrn, '_rus');
153   --Создает метрику дохода
154   v_index_dohod_uri := get_uri_str('nalog', 'dohod/rashod', concat(v_ooo_uri, '_dohod'));
155   perform triple_fft(v_index_dohod_uri, 'rdf:type', 'crm2:Index', v_table);
156   perform triple_fft(v_index_dohod_uri, 'crm2:about', v_ooo_uri, v_table);
157   perform triple_fft(v_index_dohod_uri, 'crm2:indexForm', 'crm2:res2_724209', v_table);
158   perform triple_fft(v_index_dohod_uri, 'crm2:type', 'crm2:fin_revenue', v_table);
159   perform triple_fft(v_index_dohod_uri, 'crm2:unit', 'crm2:res2_397153', v_table);
160   perform triple_fft(v_index_dohod_uri, 'crm2:value', text(v_ooo.dohod), v_table);
161   perform triple_fft(v_index_dohod_uri, 'crm2:date_from', v_date_from_uri, v_table);
162   perform triple_fft(v_index_dohod_uri, 'crm2:date_to', v_date_to_uri, v_table);
163  
164   --Создает метрику расхода
165   v_index_rashod_uri := get_uri_str('nalog', 'fin', concat(v_ooo_uri, '_rashod'));
166   perform triple_fft(v_index_rashod_uri, 'rdf:type', 'crm2:Index', v_table);
167   perform triple_fft(v_index_rashod_uri, 'crm2:about', v_ooo_uri, v_table);
168   perform triple_fft(v_index_rashod_uri, 'crm2:indexForm', 'crm2:res2_724209', v_table);
169   perform triple_fft(v_index_rashod_uri, 'crm2:type', 'crm2:fin_spending', v_table);
170   perform triple_fft(v_index_rashod_uri, 'crm2:unit', 'crm2:res2_397153', v_table);
171   perform triple_fft(v_index_rashod_uri, 'crm2:value', text(v_ooo.dohod), v_table);
172   perform triple_fft(v_index_rashod_uri, 'crm2:date_from', v_date_from_uri, v_table);
173   perform triple_fft(v_index_rashod_uri, 'crm2:date_to', v_date_to_uri, v_table);
174  
175   --v_index_pribl_uri := get_uri_str('nalog', 'fin', concat(v_ooo_uri, '_pribl'));
176
177 end loop;
178end;
179$$
180language plpgsql;
181
182
183
184select ttl_map_finance('vk_parser_dev.export_finance.nalog_export');