· 5 years ago · Feb 14, 2020, 07:32 PM
1var esriGeometriTypes = {
2 esriGeometryPoint: 'point',
3 esriGeometryPolyline: 'line',
4 esriGeometryPolygon: 'polygon',
5 esriGeometryMultipoint: 'multipoint',
6 esriGeometryMultipolyline: 'multiline',
7 esriGeometryMultipolygon: 'multipolygon'
8}
9var esriFieldTypes = {
10 esriFieldTypeDate: 'date',
11 esriFieldTypeDouble: 'double',
12 esriFieldTypeInteger: 'integer',
13 esriFieldTypeOID: 'OID',
14 esriFieldTypeSingle: 'shortnumeric',
15 esriFieldTypeSmallInteger: 'integer',
16 esriFieldTypeString: 'string',
17 esriFieldTypeFloat: 'float'
18}
19var psqlFieldTypes = {
20 '-': {txt: '-', def: false},
21 'date': {txt: 'Date', def: 'date'},
22 'double': {txt: 'Numeric(9,2)', def: 'NUMERIC(9,2)'},
23 'double2': {txt: 'Bigserial', def: 'BIGSERIAL'},
24 'integer': {txt: 'Numeric(30,0)', def: 'NUMERIC(30,0)'},
25 'shortnumeric': {txt: 'Numeric(4,0)', def: 'NUMERIC(4,0)'},
26 'string': {txt: 'Varchar(255)', def: 'character varying(255)'},
27 'text': {txt: 'Text', def: 'TEXT'},
28 'float': {txt: 'Numeric(12,8)', def: 'NUMERIC(12,8)'},
29 'point': {txt: 'Geom Point', def: 'GEOMETRY(POINT,5514)'},
30 'line': {txt: 'Geom Line', def: 'GEOMETRY(LINESTRING,5514)'},
31 'polygon': {txt: 'Geom Polygon', def: 'GEOMETRY(POLYGON,5514)'},
32 'multipoint': {txt: 'Geom MultiPoint', def: 'GEOMETRY(MultiPoint,5514)'},
33 'multiline': {txt: 'Geom MultiLine', def: 'GEOMETRY(MultiLine,5514)'},
34 'multipolygon': {txt: 'Geom MultiPolygon', def: 'GEOMETRY(MultiPolygon,5514)'}
35}
36
37var datum = new Date();
38var m = (datum.getMonth()+1);
39var d = datum.getDate()
40var dnes = datum.getFullYear()+'-'+(m<10?'0':'')+m+'-'+(d<10?'0':'')+d;
41
42var psqlData = {
43vlozil: 'Markéta Papaková',
44fileName: false,
45datumvlozeni: dnes,
46datasourcename: "\\\\PC32\\x\\AS3\\",
47psqluser: 'mpapakova',
48psqlpass: 'gis',
49psqldbname: '',
50schemas: [],
51schemasMap: {},
52fieldsMap: {},
53layersMap: {}
54};
55
56var chybneTabulky = [];
57
58function clog(txt) {
59 return;
60 console.log(txt);
61}
62
63var fieldId = 1;
64var layerId = 1;
65
66$(document).ready(function() {
67
68 $('input[name=datumvlozeni]').on('blur', function() {
69 var val = $(this).val();
70 if(val) {
71 var parseDate = Date.parse(val);
72 if(!parseDate) {
73 alert('Datum není ve správném formátu!');
74 parseDate = new Date();
75 }
76
77 var datum = new Date(parseDate);
78 var m = (datum.getMonth()+1);
79 var d = datum.getDate()
80 val = datum.getFullYear()+'-'+(m<10?'0':'')+m+'-'+(d<10?'0':'')+d;
81 $(this).val(val);
82 }
83 });
84
85 var inputs = $('form').find('input');
86 $.each(inputs, function(i, inp) {
87 var name = inp.name;
88 if(typeof psqlData[name] != 'undefined') {
89 inp.value = psqlData[name];
90 }
91 });
92
93
94$('#gdbxml').on('change', function(evt) {
95 chybneTabulky = [];
96 var f = evt.target.files[0];
97 if(f) {
98 psqlData.fileName = f['name'];
99 psqlData.gdbDbName = f['name'].replace('.xml', '');
100 psqlData.gdbFileName = psqlData.gdbDbName+'.gdb';
101
102 var r = new FileReader();
103 r.onload = function(e){
104 var xmlDoc = $.parseXML(e.target.result);
105 var datasets = $(xmlDoc).find('DatasetDefinitions');
106 var schemasElements = datasets.first().children('[xsi\\:type="esri\\:DEFeatureDataset"]');
107 var detTables = datasets.first().children('[xsi\\:type="esri\\:DETable"]');
108 $.each(schemasElements, function( indexSchemaElement, schemaElement ) {
109 var schemaName = $(schemaElement).find('Name').first().text();
110 var schemaData = {name: schemaName, layers: []};
111 var layers = $(this).find('[xsi\\:type="esri\\:DEFeatureClass"]');
112 clog('Schema: '+schemaName+' (počet vrstev '+layers.length+')');
113 parseXmlLayers(schemaData, layers);
114 psqlData.schemas.push(schemaData);
115 psqlData.schemasMap[schemaName] = schemaData;
116
117 });//EOF each schemasElement
118 parseXmlLayers(false, detTables);
119 createTable();
120 if(chybneTabulky.length) {
121 alert('Tyto tabulky nejsou zařazené do žádného schématu:\n'+chybneTabulky.join('\n'));
122 }
123 };
124 r.readAsText(f);
125 } else
126 {
127 alert("Chyba načtení XML souboru");
128 }
129
130});
131
132})
133
134function parseXmlLayers(schemaData, layers) {
135
136 $.each(layers, function(indexLayer, layer) {
137 var layerName = $(this).find('Name').first().text();
138 var schemaName = false;
139 if(schemaData) {
140 schemaName = schemaData.name;
141 }
142 else {
143 var mtch = layerName.match(new RegExp('^([^_]*_[^_]*)'));
144 if(mtch) {
145 schemaName = mtch[1];
146 }
147 else {
148 console.error('Nezname schema: '+layerName);
149 return;
150 }
151 }
152
153 var catalogPath = $(this).find('CatalogPath').first().text();
154 var layerData = {name: layerName, schemaName: schemaName, fields: [], metadata: {vytvoril: '', datum_vzniku: '', sdeleni: '', poznamka: ''}, id: 'layer_'+layerId}
155 clog(' LayerName: '+layerName+' / '+catalogPath);
156 var fields = $(this).find('Field[xsi\\:type="esri\\:Field"]');
157 var shapeSeen = false;
158 $.each(fields, function(indexField, field) {
159 var name = $(this).find('Name').first().text();
160 var type = $(this).find('Type').first().text();
161 var geomType = false;
162 if(['OBJECTID'].indexOf(name) > -1) return;
163 if(esriFieldTypes[type] == 'OID') return;
164 //if(name.toLowerCase() == 'shape') {
165 if(type == 'esriFieldTypeGeometry') {
166 if(shapeSeen) return;
167 geomType = $(this).find('GeometryType').first().text();
168 clog(' Field name: '+name+' geometryType '+esriGeometriTypes[geomType]);
169 shapeSeen = true;
170 name = 'the_geom';
171 }
172 else {
173 clog(type);
174 }
175 var def = (psqlFieldTypes[esriFieldTypes[type]])?psqlFieldTypes[esriFieldTypes[type]].def:undefined;
176 var type = esriFieldTypes[type];
177 if(geomType && esriGeometriTypes[geomType]) {
178 def = psqlFieldTypes[esriGeometriTypes[geomType]].def;
179 type = esriGeometriTypes[geomType];
180 }
181 if(name == 'id') {
182 type = 'double2';
183 def = psqlFieldTypes['double2'].def;
184 }
185 var fieldData = {name: name, esriType: (geomType?geomType:type), type: type, id: 'field_'+fieldId, def: def}
186 layerData.fields.push(fieldData);
187 psqlData['fieldsMap'][fieldData.id] = fieldData;
188 fieldId++
189 })/**/
190 if(!schemaData) {
191 if(typeof psqlData.schemasMap[schemaName] == 'undefined') {
192 chybneTabulky.push(layerName);
193 }
194 else {
195 psqlData.schemasMap[schemaName].layers.push(layerData);
196 }
197 }
198 else {
199 schemaData.layers.push(layerData);
200 }
201 psqlData['layersMap'][layerData.id] = layerData;
202 layerId++;
203 });
204}
205
206function createCombo(name, value) {
207 var content = '';
208 content = '<select name="'+name+'" class="combo">';
209 $.each(psqlFieldTypes, function(k, val) {
210 var sel = (k == value)?' selected':'';
211 content += '<option value="'+k+'"'+sel+'>'+val.txt+'</option>';
212 })
213 content += '</select>';
214 return content;
215}
216
217function createTable() {
218
219 var content = "<table border='1'>"
220 $.each(psqlData.schemas, function(index, schema) {
221 content += '<tr><td colspan="3"><b>Schéma: '+schema.name+'</b></td></tr>';
222 $.each(schema.layers, function(indexLayer, layer) {
223 content += '<tr><td colspan="3"><b>Vrstva: '+layer.name+'</b></td></tr>';
224 content += '<tr><td>Metadata</td>';
225
226 content += '<td><input type="text" name="vytvoril" placeholder="vytvořil" data-schema="'+schema.name+'" data-layer="'+layer.id+'" /><button class="metadata" title="Vyplnit">X</button></td>';
227 content += '<td><input type="text" name="datum_vzniku" placeholder="datum vzniku" data-schema="'+schema.name+'" data-layer="'+layer.id+'" /><button class="metadata" title="Vyplnit">X</button></td></tr>';
228
229 content += '<tr><td>Metadata</td>';
230 content += '<td><input type="text" name="sdeleni" placeholder="sdělení" data-schema="'+schema.name+'" data-layer="'+layer.id+'" /><button class="metadata" title="Vyplnit">X</button></td>';
231 content += '<td><input type="text" name="poznamka" placeholder="poznámka" data-schema="'+schema.name+'" data-layer="'+layer.id+'" /><button class="metadata" title="Vyplnit">X</button></td></tr>';
232
233 content += '<tr><td><i>Pole</i></td><td><i>ESRI typ</i></td><td><i>PSQL typ</i></td></tr>';
234 $.each(layer.fields, function(indexField, field) {
235 content += '<tr><td>'+field.name+'</td><td>'+field.esriType+'</td><td>'+createCombo(field.id, field.type)+'</td></tr>';
236 });
237 });
238 });
239 content += '</table><br /><button onclick="generateZip();">Vygenerovat ZIP</button>';
240 $('#output').empty();
241 $('#output').append(content);
242 $('.combo').on('change', function() {
243 psqlData['fieldsMap'][$(this).attr('name')]['def'] = psqlFieldTypes[$(this).val()]['def'];
244 });
245 $('button.metadata').on('click', function() {
246 var schema = $(this).prev().attr('data-schema');
247 var name = $(this).prev().attr('name');
248 var val = $(this).prev().val();
249 var inp = $('[data-schema='+schema+'][name='+name+']');
250 $.each(inp, function(idx, f) {
251 $(this).val(val);
252 });
253 });
254 $('[data-layer]').on('blur', function() {
255 var layerId = $(this).attr('data-layer');
256 var name = $(this).attr('name');
257 if(psqlData.layersMap[layerId]) {
258 var val = $(this).val()
259 if(name == 'datum_vzniku' && val) {
260 if(!Date.parse(val)) {
261 val = '';
262 $(this).val(val);
263 alert('Datum není ve správném formátu!');
264 }
265 else {
266 var datum = new Date(Date.parse(val));
267 var m = (datum.getMonth()+1);
268 var d = datum.getDate()
269 val = datum.getFullYear()+'-'+(m<10?'0':'')+m+'-'+(d<10?'0':'')+d;
270 $(this).val(val);
271 }
272 }
273 var layerData = psqlData.layersMap[layerId];
274 layerData.metadata[name] = val;
275 }
276 });
277}
278
279function createTxtPumpFile(schemaName) {
280 var schemaData = psqlData.schemasMap[schemaName];
281 if(schemaData) {
282 var lines = [];
283 var carky = '-------------';
284 lines.push('Script Name');
285 lines.push(carky);
286 lines.push('GDB -> PostGIS, '+psqlData.gdbDbName+', '+schemaName);
287 lines.push('');
288 lines.push(carky);
289 lines.push('');
290 lines.push('Data Source');
291 lines.push(carky);
292 lines.push('dbtype=ogr');
293 lines.push('drivername=OpenFileGDB');
294 lines.push('datasourcename='+psqlData.datasourcename);
295 lines.push('');
296 lines.push(carky);
297 lines.push('');
298 lines.push('Copy Content');
299 lines.push(carky);
300 $.each(schemaData.layers, function(index, layer) {
301 lines.push(layer.name);
302 });
303 lines.push('');
304 lines.push(carky);
305 lines.push('');
306 lines.push('Copy Destination');
307 lines.push(carky);
308 lines.push('dbtype=postgis');
309 lines.push('host=cougar');
310 lines.push('port=5432');
311 lines.push('database='+psqlData.psqldbname);//
312 lines.push('schema='+schemaName);
313 lines.push('user='+psqlData.psqluser);//
314 lines.push('password='+psqlData.psqlpass);//
315 lines.push('');
316 lines.push(carky);
317 lines.push('');
318 lines.push('Parameters');
319 lines.push(carky);
320 lines.push('when_target_exists=clear');
321 lines.push('shapefile_charset=UTF-8');
322 lines.push('crs=EPSG:5514');
323 lines.push('dbl_quot=source');
324
325 var blob = new Blob([lines.join('\n')], {type: "text/plain;charset=utf-8"});
326 return blob;
327 }
328}
329
330function createSqlTable(layerId) {
331 var layer = psqlData['layersMap'][layerId];
332 var content = '';
333 if(layer) {
334 if(chybneTabulky.indexOf(layer.name) > -1 || typeof psqlData.schemasMap[layer.schemaName] == 'undefined') {
335 return content;
336 }
337 content = 'CREATE TABLE IF NOT EXISTS '+layer.schemaName+'.'+layer.name+' (\n';
338 var fields = [];
339 $.each(layer.fields, function(index, field) {
340 var def = field.name+' '+psqlData['fieldsMap'][field.id]['def'];
341 if(field.name == 'id') {
342 def += ' PRIMARY KEY';
343 fields.unshift(def);
344 return;
345 }
346 fields.push(def);
347 });
348 content += fields.join(",\n");
349 content += ');\n\n';
350 }
351 return content;
352}
353
354function createSchema(schemaName) {
355 var content = '';
356 if(typeof psqlData.schemasMap[schemaName] != 'undefined') {
357 content = 'CREATE SCHEMA IF NOT EXISTS '+schemaName+';';//AUTHORIZATION';
358 }
359 return content;
360}
361
362function metaDataTable(schemaName) {
363 var content = '';
364 if(typeof psqlData.schemasMap[schemaName] != 'undefined') {
365 content = 'CREATE TABLE IF NOT EXISTS '+schemaName+'.metadata (\n';
366 content += 'id SERIAL PRIMARY KEY,\n';
367 content += 'vrstva VARCHAR (255),\n';
368 content += 'vlozil VARCHAR (255),\n';
369 content += 'datum_vlozeni DATE,\n';
370 content += 'vytvoril VARCHAR (255),\n';
371 content += 'datum_vzniku DATE,\n';
372 content += 'sdeleni VARCHAR (255),\n';
373 content += 'poznamka VARCHAR (255));\n\n';
374 var ins = [];
375 content += 'INSERT INTO '+schemaName+'.metadata\n (vrstva, vlozil, datum_vlozeni, vytvoril, datum_vzniku, sdeleni, poznamka) values \n';
376 $.each(psqlData.schemasMap[schemaName].layers, function(index, layer) {
377 var metadata = layer.metadata;
378 var datum_vzniku = (metadata.datum_vzniku)?"'"+metadata.datum_vzniku+"'":'NULL';
379 ins.push("('"+layer['name']+"', '"+psqlData['vlozil']+"', '"+psqlData['datumvlozeni']+"', '"+metadata.vytvoril+"', "+datum_vzniku+", '"+metadata.sdeleni+"', '"+metadata.poznamka+"')");
380 })
381 content += ins.join(",\n");
382 content += ';\n\n';
383 }
384 return content;
385}
386
387function generateZip() {
388 var zip = new JSZip();
389 var schemaSql = [];
390 var metadataSql = [];
391
392 var inputs = $('form').find('input');
393 $.each(inputs, function(i, inp) {
394 var name = inp.name;
395 if(typeof psqlData[name] != 'undefined') {
396 psqlData[name] = inp.value;
397 }
398 });
399
400 var tables = [];
401 var chyba = [];
402 $.each(psqlData.layersMap, function(layerId, layer) {
403 $.each(layer.fields, function(index, field) {
404 if(!field.def || field.def == '-') {
405 chyba.push(layer['name']+': '+field['name']);
406 }
407 });
408 tables.push(createSqlTable(layerId));
409 });
410 if(chyba.length) {
411 alert('Nejsou vyplnene DB typy:\n'+chyba.join("\n"));
412 return;
413 }
414
415 $.each(psqlData.schemas, function(index, schema) {
416 var file = createTxtPumpFile(schema['name']);
417 if(file) {
418 zip.file(psqlData.gdbDbName+'_'+schema['name']+'.txt', file);
419 }
420 schemaSql.push(createSchema(schema['name']));
421
422 var metadata = metaDataTable(schema['name']);
423 if(metadata) {
424 metadataSql.push(metadata);
425 zip.file('metadata_'+schema['name']+'_sql.txt', metadata);
426 }
427 });
428
429 if(schemaSql.length) {
430 schemaSql.push("\n");
431 var blob = new Blob([schemaSql.join('\n')], {type: "text/plain;charset=utf-8"});
432 zip.file('createschemas_sql.txt', blob);
433 }
434
435
436 if(tables.length) {
437 var blob = new Blob([tables.join('\n')], {type: "text/plain;charset=utf-8"});
438 zip.file('createtables_sql.txt', blob);
439 }
440
441 //vsechny sql sveta
442 var vsechnySqlSveta = schemaSql.concat(tables);
443 vsechnySqlSveta = vsechnySqlSveta.concat(metadataSql);
444 if(vsechnySqlSveta.length) {
445 var blob = new Blob([vsechnySqlSveta.join('\n')], {type: "text/plain;charset=utf-8"});
446 zip.file('_vsechna_sql_sveta.txt', blob);
447 }
448
449 zip.generateAsync({type:"blob"}).then(function (blob) { // 1) generate the zip file
450 saveAs(blob, psqlData.gdbDbName+'_pumpfiles.zip'); // 2) trigger the download
451 }, function (err) {
452 jQuery("#blob").text(err);
453 });
454
455}