se/SE/se-lib/Route/UrlAction/WmsGenerate.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('FoldersConfig');
Lib::loadClass('FileUploader');
Lib::loadClass('ProcesHelper');

class Route_UrlAction_WmsGenerate extends RouteBase {

	public function handleAuth() {
		if (!User::logged()) {
			throw new HttpException('Unauthorized', 401);
		}
	}

	private function getDirectory($table,$id) {
		$folderConf = FoldersConfig::getAll($table.'_COLUMN');
		$record=new stdClass;
		$record->ID=$id;
		$uploader = new FileUploader($table.'_COLUMN', $record);
		$errMsg="";
		if (!$uploader->setConfig($folderConf, $errMsg)) throw new HttpException("Błąd danych konfiguracyjnych" . '<br>' . "\n" . $errMsg, 404);
		$uploader->findFolder();
		return $uploader->getDestLocalPath(true);
	}

	private function getMainDirectory() {
		$folderConf = FoldersConfig::getAll('WMS_LAYERS_COLUMN');
		if (!isset($folderConf['mount_point'])) throw new HttpException("Błąd danych konfiguracyjnych" . '<br>' . "\n" . $errMsg, 404);
		return $folderConf['mount_point'];
	}

	private function defaultAction_WMS_LAYERS($ID_WMS_LAYERS,$REFERER) {
		try {
			$layer=DB::getPDO()->fetchall("select LAYER_TYPE,FILE_STATUS from WMS_LAYERS where ID='".$ID_WMS_LAYERS."'");
			if (!$layer) throw new Exception('Błąd - nie ma takiej warstwy!');

			if ($layer[0]['FILE_STATUS']=="IN_PROGRESS") throw new Exception('Błąd - plik w trakcie przetwarzania!');

			$destFolderPath = $this->getDirectory('WMS_LAYERS',$ID_WMS_LAYERS);
			$files=array();
			
			if ($layer[0]['LAYER_TYPE']=='GeoTIFF') {

				foreach (glob($destFolderPath."/*.tif",GLOB_BRACE) as $node) {
					if (is_file($node)) $files[]=basename($node);
				}
				if (count($files)==0) throw new Exception('Nie znaleziono żadnego pliku tif!');
				if (count($files)>1) throw new Exception('Znaleziono zbyt dużo plików tif!');

				$validateGeoTifJson=shell_exec("/opt/local/bin/gdalinfo -json ".$destFolderPath."/*".$files[0]." 2>/dev/null");
				$validateGeoTif=json_decode($validateGeoTifJson);
				if (!$validateGeoTif) throw new Exception('Znaleziony plik '.$files[0].' nie jest poprawnym plikiem tif!');
				if (!$validateGeoTif->coordinateSystem->wkt) throw new Exception('Znaleziony plik '.$files[0].' nie jest poprawnym plikiem GeoTif!');

			} elseif ($layer[0]['LAYER_TYPE']=='QGIS') {

				foreach (glob($destFolderPath."/*.qgs",GLOB_BRACE) as $node) {
					if (is_file($node)) $files[]=basename($node);
				}
				if (count($files)==0) throw new Exception('Nie znaleziono żadnego pliku qgs!');
				if (count($files)>1) throw new Exception('Znaleziono zbyt dużo plików qgs!');
				$validateXml=simplexml_load_file($destFolderPath."/".$files[0]);
				if (!$validateXml) throw new Exception('Znaleziony plik '.$destFolderPath.'/'.$files[0].' nie jest poprawnym plikiem qgs!');
				if (!$validateXml->mapcanvas->destinationsrs->spatialrefsys->srid) throw new Exception('Znaleziony plik '.$files[0].' nie jest poprawnym plikiem projektu QGIS!');

				$DataSources=$validateXml->xpath('/qgis/projectlayers/maplayer/datasource');
				if (!$DataSources) throw new Exception('Plik projektu QGIS nie zawiera żadnej warstwy!');
				$suffixes=array('.shp','.dbf','.shx');
				$filesToCopy=array($files[0]);
				$missingFiles=array();
				foreach ($DataSources as $DataSource) {
					$DataSourceArr=explode('|',$DataSource);
					$DataSource=$DataSourceArr[0];
					$file=basename($DataSource,".shp");
					foreach ($suffixes as $suffix) {
						if(is_file($destFolderPath."/".$file.$suffix)) $filesToCopy[]=$file.$suffix;
						else $missingFiles[]=$file.$suffix;
					}
				}
				if ($missingFiles) throw new Exception('Nie znaleziono poniższych plików wymaganych przez plik projetku QGIS:<br/>'.implode('<br/>',$missingFiles));

				if (is_dir($destFolderPath.'/.wms')) shell_exec('rm -rf '.$destFolderPath.'/.wms');
				mkdir($destFolderPath.'/.wms');
				foreach ($filesToCopy as $file) copy($destFolderPath."/".$file,$destFolderPath."/.wms/".$file);

			} else throw new Exception('Nieznany typ warstwy!');

			$sqlObj = new stdClass();
			$sqlObj->ID = $ID_WMS_LAYERS;
			$sqlObj->FILE_STATUS='TO_GENERATE';
			$sqlObj->FILE_STATUS_INFO='Plik oczekuje na przetworzenie';
			$sqlObj->FILE_NAME=$files[0];
			$sqlObj->OLD_LAYER_TYPE=$layer[0]['LAYER_TYPE'];
			$affected = DB::getDB()->UPDATE_OBJ('WMS_LAYERS', $sqlObj);
			if ($affected) SE_Layout::alert('success','Oznaczono plik '.$files[0].' do przetworzenia.');
			else SE_Layout::alert('warning','Plik '.$files[0].' został już wcześniej oznaczony do przetworzenia.');

		} catch (Exception $e) {
			SE_Layout::alert('danger',$e->getMessage());
		}
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
	}

	private function defaultAction_WMS_MAPS($ID_WMS_MAPS,$REFERER) {
		try {
			$affected = DB::getDB()->UPDATE_OBJ('WMS_MAPS',(object) array('ID'=>$ID_WMS_MAPS,'MAP_STATUS'=>'TO_GENERATE','MAP_STATUS_INFO'=>'Mapa oczekuje na przetworzenie'));
			if ($affected) SE_Layout::alert('success','Oznaczono mapę do ponownego wygenerowania.');
			else SE_Layout::alert('warning','Mapa została już wcześniej oznaczona do ponownego wygenerowania.');
		} catch (Exception $e) {
			SE_Layout::alert('danger',$e->getMessage());
		}
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
	}

	private function defaultAction_WIZJE_LOKALNE($ID_WIZJE_LOKALNE,$REFERER) {
		try {
			$wizje=DB::getPDO()->fetchall("select WMS_LAYER_ID,LAYER_NAME from WIZJE_LOKALNE where ID='".$ID_WIZJE_LOKALNE."'");
			if (!$wizje) throw new Exception("Błąd danych");

			$displayForm=true;
			$dirWizja=$this->getDirectory('WIZJE_LOKALNE',$ID_WIZJE_LOKALNE);
			exec('find '.$dirWizja.' -type f -name "*.tif" 2>/dev/null',$files);
			if (!$files) {
				SE_Layout::alert('danger',"Wybrana wizja lokalna nie zawiera żadnych plików GeoTIFF!");
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $REFERER?>" class="btn btn-primary">Powrót</a>
</div>
<?php
				$displayForm=false;
			} else {
				$ID_WMS_LAYERS=V::get('ID_WMS_LAYERS',0,$_POST,'int');
				$form_file=V::get('file','',$_POST);
				$form_ID_WMS_MAPS=V::get('ID_WMS_MAPS',0,$_POST,'int');

				if ($ID_WMS_LAYERS!=0) {
					if ($form_file=='') SE_Layout::alert('danger','Nie wybrano żadnego pliku!');
					else {
						$displayForm=false;
						$resMsg="";
						if ($ID_WMS_LAYERS==-1) {
							$ID_WMS_LAYERS=DB::getDB()->ADD_NEW_OBJ('WMS_LAYERS',(object) array('LAYER_NAME'=>$wizje[0]['LAYER_NAME'],'LAYER_TYPE'=>'GeoTIFF','ID_WMS_MAPS'=>$form_ID_WMS_MAPS));
							$resMsg.="Utworzono nową warstwę WMS - ID ".$ID_WMS_LAYERS."<br/>\n";
						}
						$WmsLayerDir=$this->getDirectory('WMS_LAYERS',$ID_WMS_LAYERS);
						if (!file_exists($WmsLayerDir)) @mkdir($WmsLayerDir);
						if (!is_dir($WmsLayerDir)) {
							DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$ID_WMS_LAYERS,'FILE_STATUS_INFO'=>'Nie można utworzyć katalogu warstwy'));
							throw new Exception("Wystąpił nieoczekiwany błąd - nie można utworzyć katalogu ".$WmsLayerDir);
						}
						@chmod($WmsLayerDir, 0777);
						exec("rm -f ".$WmsLayerDir."/*.tif 2>/dev/null");
						symlink($form_file,$WmsLayerDir."/".basename($form_file));

						DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$ID_WMS_LAYERS,'FILE_STATUS_INFO'=>'Utoworzono link symboliczny z poziomu tabeli WIZJE_LOKALNE'));

						$sqlObj = new stdClass();
						$sqlObj->ID = $ID_WIZJE_LOKALNE;
						$sqlObj->WMS_LAYER_UPLOADED='Y';
						$sqlObj->WMS_LAYER_ID=$ID_WMS_LAYERS;
						if (V::get('WIZJE_LOKALNE_OFF_HARD',0,$_POST,'int')==1) $sqlObj->A_STATUS='OFF_HARD';
						DB::getDB()->UPDATE_OBJ('WIZJE_LOKALNE', $sqlObj);

						SE_Layout::alert('success',$resMsg.'Pomyślnie dodano plik GeoTIFF do warstwy.');

						$WmsLayerTableZasobId=ProcesHelper::getZasobTableID('WMS_LAYERS');
						$editWmsLayerUrl="index.php?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID=".$WmsLayerTableZasobId."#EDIT/".$ID_WMS_LAYERS;
?>
<div class="container" style="text-align:center">
  <a href="<?php echo $editWmsLayerUrl?>" class="btn btn-primary">Edytuj warstwę WMS</a>
  <a href="<?php echo $REFERER?>" class="btn btn-default">Powrót</a>
</div>
<?php
					}
				}
				elseif ($_POST) {
					$msgErr='Nie wybrano żadnej warstwy!';
					if (!$form_file) $msgErr.="<br/>Nie wybrano żadnego pliku!";
					SE_Layout::alert('danger',$msgErr);
				}
			}
			if ($displayForm) {
				if ($wizje[0]['WMS_LAYER_ID']) {
					$layers=DB::getPDO()->fetchall("select l.LAYER_NAME,l.LAYER_TYPE,l.ID_WMS_MAPS,m.MAP_NAME from WMS_LAYERS l left join WMS_MAPS m on l.ID_WMS_MAPS=m.ID where l.ID='".$wizje[0]['WMS_LAYER_ID']."'");
					if ($layers[0]['LAYER_TYPE']!="GeoTIFF") throw new Exception("Niespójny typ wartstwy - należy zmienić typ warstwy ID ".$wizje[0]['WMS_LAYER_ID']." na GeoTIFF");
					$LAYER_NAME=$layers[0]['LAYER_NAME'];
					if (!$LAYER_NAME) $LAYER_NAME="Brak nazwy warstwy";
					$ID_WMS_MAPS=$layers[0]['ID_WMS_MAPS'];
					$MAP_NAME=$layers[0]['MAP_NAME'];
					if ($ID_WMS_MAPS && !$MAP_NAME) $MAP_NAME="Brak nazwy mapy";
				}
				if (!$wizje) throw new Exception("Błędne ID z tabeli WIZJE_LOKALNE");
?>
<div class="container" style="margin-top:20px">
<form class="form-horizontal" method="post">
  <legend>Dodawanie pliku GeoTIFF do wartstwy WMS</legend>
  <div class="form-group">
    <label class="col-sm-1 control-label">Warstwa</label>
    <div class="col-sm-11">
<?php
				if ($wizje[0]['WMS_LAYER_ID']) echo '<p class="form-control-static">'.$wizje[0]['WMS_LAYER_ID'].": ".$LAYER_NAME.'</p><p style="color:red;">Uwaga! Ten rekord wizji lokalnej został już powiazany z daną warstwą i nie ma możliwości zmiany powiązania.<br/>Ponowne dodanie pliku GeoTIFF spowoduje usunięcie dotychczasowego pliku GeoTIFF z danej warstwy.</p>'."\n".'<input type="hidden" name="ID_WMS_LAYERS" value="'.$wizje[0]['WMS_LAYER_ID'].'"/>'."\n";
				else {
?>
      <select id="warstwa" class="form-control" name="ID_WMS_LAYERS">
        <option value="-1">(Utwórz nową warstwę) <?php echo $wizje[0]['LAYER_NAME']?></option>
<?php
					$layers=DB::getPDO()->fetchall("select ID,LAYER_NAME,ID_WMS_MAPS from WMS_LAYERS where LAYER_TYPE='GeoTIFF' and A_STATUS='WAITING'");
					foreach ($layers as $layer) echo '<option value="'.$layer['ID'].'" data-map_id="'.$layer['ID_WMS_MAPS'].'"'.($layer['ID']==$ID_WMS_LAYERS?" selected":"").'>(Oczekująca warstwa) '.$layer['ID'].': '.$layer['LAYER_NAME']."</option>\n";
				}
?>
      </select>
    </div>
  </div>
  <div class="form-group">
    <label class="col-sm-1 control-label">Mapa</label>
    <div class="col-sm-11">
<?php
				if ($wizje[0]['WMS_LAYER_ID']) echo '<p class="form-control-static">'.(($ID_WMS_MAPS)?$ID_WMS_MAPS.": ".$MAP_NAME:'Brak').'</p>';
				else {
?>
      <select id="mapa" class="form-control" name="ID_WMS_MAPS">
        <option value="0">Brak</option>
<?php
					$maps=DB::getPDO()->fetchall("select ID,MAP_NAME from WMS_MAPS");
					foreach ($maps as $map) echo '<option value="'.$map['ID'].'"'.($map['ID']==$form_ID_WMS_MAPS?" selected":"").">".$map['ID'].': '.$map['MAP_NAME']."</option>\n";
				}
?>
       </select>
    </div>
  </div>
  <div class="form-group">
    <label class="col-sm-1 control-label">Status wizji lokalnej</label>
    <div class="col-sm-11">
      <label class="checkbox-inline">
        <input type="checkbox" name="WIZJE_LOKALNE_OFF_HARD" value="1" checked> Ustaw automatycznie status wizji lokalnej na OFF_HARD
      </label>
    </div>
  </div>
  <div class="form-group">
    <label class="col-sm-1 control-label">Plik</label>
    <div class="col-sm-11" style="max-height:400px; overflow:auto;">
      <table class="table table-bordered table-hover">
        <thead>
          <tr style="text-align:center;"><td width="1"></td><td>Ścieżka/nazwa</td><td width="1">Wielkość</td><td width="1">Data</td></tr>
        </thead>
        <tbody>
<?php
				foreach ($files as $file)
					echo '<tr><td><input style="margin:0; position:relative;" type="radio" name="file" value="'.$file.'"'.(($file==$form_file)?' checked':'').'/></td><td>'.substr($file,strlen($dirWizja)+1).'</td><td style="text-align:right;" nowrap>'.V::humanFileSize(filesize($file))."</td><td nowrap>".date("Y-m-d H:i:s",filectime($file))."</td></tr>\n";
?>
        </tbody>
      </table>
    </div>
  </div>
  <div class="form-group">
    <div class="col-sm-offset-1 col-sm-11">
      <button type="submit" class="btn btn-primary">Zapisz</button>
      <a href="<?php echo $REFERER?>" class="btn btn-default">Anuluj</a>
    </div>
  </div>
  <input type="hidden" name="WMS_REFERER" value="<?php echo $REFERER?>"/>
</form>
</div>

<script>
$('#warstwa').selectize({
create: false,
openOnFocus: false
});
$('#mapa').selectize({
create: false,
openOnFocus: false
});
</script>

<?php
			}
		} catch (Exception $e) {
			SE_Layout::alert('danger',$e->getMessage());
		}
	}

	public function defaultAction() {
		SE_Layout::gora();
		SE_Layout::menu();
		if (V::get('WMS_REFERER','',$_POST)!='') $REFERER=V::get('WMS_REFERER','',$_POST);
		else $REFERER=$_SERVER['HTTP_REFERER'];
		if (V::get('ID_WMS_LAYERS',0,$_GET,'int')>0) $this->defaultAction_WMS_LAYERS(V::get('ID_WMS_LAYERS',0,$_GET,'int'),$REFERER);
		elseif (V::get('ID_WMS_MAPS',0,$_GET,'int')>0) $this->defaultAction_WMS_MAPS(V::get('ID_WMS_MAPS',0,$_GET,'int'),$REFERER);
		elseif (V::get('ID_WIZJE_LOKALNE',0,$_GET,'int')>0) $this->defaultAction_WIZJE_LOKALNE(V::get('ID_WIZJE_LOKALNE',0,$_GET,'int'),$REFERER);
		else SE_Layout::alert('danger','Błąd parametru');
		SE_Layout::dol();
	}

	public function doGenerate() {

		try {
			### Ustawiamy zmienną sesji nazwy użytkownika, aby w rekordach historycznych było widać, że autorem systemu jest moduł WmsGenerate
			$_SESSION['ADM_ACCOUNT']="WmsGenerate";


			### Sprawdzenie czy modul WMS jest aktywny na danej instalacji Procesy5
			$moduleActive=DB::getPDO()->fetchall("select count(*) as cnt from CRM_CONFIG where CONF_KEY='module_WMS' and CONF_VAL='on'");
			if (!$moduleActive[0]['cnt']) throw new Exception("Module WMS disabled/not installed.");


			### Utworzenie niezbędnych katalogów i plików
			$wmsDirLocation=$this->getMainDirectory();
			if (!file_exists($wmsDirLocation.'/.maps/.single')) mkdir($wmsDirLocation.'/.maps/.single',0755,true);
			if (!file_exists($wmsDirLocation.'/.maps/.single')) throw new Exception('Error during creating maps directory.');
			if (!file_exists($wmsDirLocation.'/.maps/.template.html')) file_put_contents($wmsDirLocation.'/.maps/.template.html','');


			### Tablica rozszerzeń plików warstw
			$mapFileSuffix=array('GeoTIFF'=>'.map','QGIS'=>'.qgs');


			### Definicja tablicy przechowującej numery map do wygenerowania
			$mapsToGenerate=Array();


			### Weryfikacja zmiany typu warstwy
			$layers=DB::getPDO()->fetchall("select ID,OLD_LAYER_TYPE from WMS_LAYERS where LAYER_TYPE!=OLD_LAYER_TYPE AND (MAP_STATUS='ACTIVE' or FILE_STATUS in ('TO_GENERATE','IN_PROGRESS'))");
			foreach ($layers as $layer) DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'LAYER_TYPE'=>$layer['OLD_LAYER_TYPE']));


			### Przetworzenie projektów QGIS
                        $layers=DB::getPDO()->fetchall("select ID,FILE_NAME,LAYER_NAME,A_STATUS,ID_WMS_MAPS from WMS_LAYERS where LAYER_TYPE='QGIS' and FILE_STATUS='TO_GENERATE'");
                        foreach ($layers as $layer) {
                                $destFolderPath = $this->getDirectory('WMS_LAYERS',$layer['ID']);
                                if(!is_file($destFolderPath.'/'.$layer['FILE_NAME'])) {
                                        DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'ERROR','FILE_STATUS_INFO'=>'Wystąpił błąd pliku qgs'));
                                        throw new Exception("Błąd pliku: ".$layer['FILE_NAME']);
                                }

                                $xml=simplexml_load_file($destFolderPath.'/.wms/'.$layer['FILE_NAME']);
				if (!$xml) {
					DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'ERROR','FILE_STATUS_INFO'=>'Wystąpił błąd pliku qgs'));
					throw new Exception("Błąd pliku: ".$layer['FILE_NAME']);
				}

				$extents=array();
				$mapLayers=$xml->xpath('/qgis/projectlayers/maplayer');
				foreach ($mapLayers as $mapLayer) {
					$dataSourceArr=explode('|',$mapLayer->datasource);
					$dataSourceArr[0]=$destFolderPath.'/.wms/'.basename($dataSourceArr[0]);
					$dataSource=implode('|',$dataSourceArr);
					$mapLayer->datasource=$dataSource;
					//$mapLayer->datasource=$destFolderPath.'/.wms/'.basename($mapLayer->datasource);
					$bash='
tmp=`mktemp -d`
/opt/local/bin/ogr2ogr -s_srs EPSG:'.$mapLayer->srs->spatialrefsys->srid.' -t_srs EPSG:4236 "$tmp/tmp.shp" "'.$mapLayer->datasource.'" >/dev/null 2>&1
extent=`/opt/local/bin/ogrinfo -al -so "$tmp/tmp.shp" | grep Extent | sed "s/[(),\-]//g;s/  / /g;s/Extent: //g"`
echo "${extent} "
rm -rf $tmp
';
					$extent=explode(" ",shell_exec($bash));
					$extents['xmin'][]=$extent[0];
					$extents['ymin'][]=$extent[1];
					$extents['xmax'][]=$extent[2];
					$extents['ymax'][]=$extent[3];
				}
				$xmin=min($extents['xmin']);
				$ymin=min($extents['ymin']);
				$xmax=max($extents['xmax']);
				$ymax=max($extents['ymax']);
				$geom="POLYGON(({$xmin} {$ymax},{$xmax} {$ymax},{$xmax} {$ymin},{$xmin} {$ymin},{$xmin} {$ymax}))";

				if ($layer['LAYER_NAME']) $LAYER_NAME=V::convert($layer['LAYER_NAME'],'url');
				else $LAYER_NAME="QGIS_".$layer['ID'];
				$xml->properties->WMSRootName=$LAYER_NAME;
				$xml->properties->WMSRootName->addAttribute('type','QString');
				$xml->properties->WMSServiceTitle=$LAYER_NAME;
				$xml->properties->WMSServiceTitle->addAttribute('type','QString');
				$xml->properties->WMSServiceCapabilities="true";
				$xml->properties->WMSServiceCapabilities->addAttribute('type','bool');
				$xml->properties->WMSAddWktGeometry="true";
				$xml->properties->WMSAddWktGeometry->addAttribute('type','bool');
				$xml->properties->Paths->Absolute="true";
				$xml->properties->WMSUrl="http://127.0.0.1:34580/wms/qgis/".$layer['ID'];
				$xml->properties->WMSUrl->addAttribute('type','QString');
				$epsg=$xml->mapcanvas->destinationsrs->spatialrefsys->srid;
				$extent=implode(" ",(array) $xml->mapcanvas->extent);

				$xml->asXml($destFolderPath.'/.wms/wms.qgs');
				unlink($destFolderPath.'/.wms/'.$layer['FILE_NAME']);

				$sqlArr=array('ID'=>$layer['ID'],'FILE_STATUS'=>'GENERATED','FILE_STATUS_INFO'=>'Plik przetworzony poprawnie','the_geom'=>"GeomFromText('{$geom}')",'EPSG'=>"{$epsg}",'EXTENT'=>"{$extent}");
				if ($layer['A_STATUS']=="WAITING") $sqlArr['A_STATUS']='NORMAL';
				DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) $sqlArr);

				if ($layer['A_STATUS']=='WAITING' || $layer['A_STATUS']=='NORMAL') $mapsToGenerate[$layer['ID_WMS_MAPS']]=1;
			}


			### Znalezienie nowo przetworzonych warstw GeoTIFF i utworzenie pojedynczych map do mapserver
			$mapSchema='MAP

  NAME "__NAME__"
  STATUS ON
  SIZE 400 300
  EXTENT __EXTENT__
  UNITS METERS
  IMAGECOLOR 255 255 0
  IMAGETYPE PNG

  WEB
    METADATA
      WMS_TITLE "__NAME__"
      WMS_ENABLE_REQUEST "*"
#     WMS_ONLINERESOURCE "https://__SERVER_NAME__/wms/.single/__ID__"
      MS_ONLINERESOURCE "http://127.0.0.1:34580/wms/.single/__ID__"
      WMS_SRS "EPSG:__EPSG__"
    END
  END

  PROJECTION
    "init=epsg:__EPSG__"
  END

  LAYER
    NAME         "__NAME__"
    STATUS       OFF
    TYPE         RASTER
    DATA        "__FILE__"
    EXTENT      __EXTENT__

    PROJECTION
      "init=epsg:__EPSG__"
    END

    METADATA
      WMS_TITLE "__NAME__"
      WMS_SRS "EPSG:__EPSG__"
      WMS_EXTENT "__EXTENT__"
    END
  END

END
';
			$search=array('__NAME__','__EXTENT__','__SERVER_NAME__','__ID__','__EPSG__','__FILE__');

			$layers=DB::getPDO()->fetchall("select ID,LAYER_NAME,EPSG,ID_WMS_MAPS,A_STATUS,FILE_NAME from WMS_LAYERS where LAYER_TYPE='GeoTIFF' and FILE_STATUS='IN_PROGRESS'");
			foreach ($layers as $layer) {
				if (!file_exists('/tmp/generate_wms-'.$layer['ID'].'.lock')) {
					unlink('/tmp/generate_wms-'.$layer['ID'].'.sh');
					$destFolderPath = $this->getDirectory('WMS_LAYERS',$layer['ID']);
					if (file_exists($destFolderPath.'/.wms/.completed')) {
						if (file_exists($destFolderPath.'/.wms/.error')) {
							DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'ERROR','FILE_STATUS_INFO'=>'Wystąpił błąd przetwarzania, przekaż problem administratorowi systemu'));
						} elseif (!file_exists($destFolderPath.'/.wms/.data')) {
							DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'TO_GENERATE','FILE_STATUS_INFO'=>'Wystąpił błąd przetwarzania, ale nastąpi kolejna próba jego przetworzenia'));
						} else {
							$data_arr=explode('|',file_get_contents($destFolderPath.'/.wms/.data'));
							unlink($destFolderPath.'/.wms/.data');
							$geom=$data_arr[0];
							$extent=$data_arr[1];
							if ($layer['LAYER_NAME']) $LAYER_NAME=V::convert($layer['LAYER_NAME'],'url');
							else $LAYER_NAME="GeoTIFF_".$layer['ID'];
							$replace=array($LAYER_NAME,$extent,$_SERVER['SERVER_NAME'],$layer['ID'],$layer['EPSG'],$destFolderPath.'/.wms/'.$layer['FILE_NAME']);
							$map = str_replace($search,$replace,$mapSchema);
							file_put_contents($destFolderPath.'/.wms/wms.map',$map);

							$sqlArr=array('ID'=>$layer['ID'],'FILE_STATUS'=>'GENERATED','FILE_STATUS_INFO'=>'Plik przetworzony poprawnie','the_geom'=>"GeomFromText('{$geom}')",'EXTENT'=>"{$extent}");
							if ($layer['A_STATUS']=="WAITING") $sqlArr['A_STATUS']='NORMAL';
							DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) $sqlArr);
							unlink($destFolderPath.'/.wms/.completed');

							if ($layer['A_STATUS']=='WAITING' || $layer['A_STATUS']=='NORMAL') $mapsToGenerate[$layer['ID_WMS_MAPS']]=1;
						}
					} else {
						DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'TO_GENERATE','FILE_STATUS_INFO'=>'Wystąpił błąd przetwarzania, ale nastąpi kolejna próba jego przetworzenia'));
					}
				}
			}


			### Weryfikacja czy jakas aktywna warstwa nie zostala przesunieta do innej mapy
			$layers=DB::getPDO()->fetchall("select ID,ID_WMS_MAPS,OLD_ID_WMS_MAPS from WMS_LAYERS where MAP_STATUS='ACTIVE' and ID_WMS_MAPS!=OLD_ID_WMS_MAPS");
			foreach ($layers as $layer) {
				DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'OLD_ID_WMS_MAPS'=>$layer['ID_WMS_MAPS']));
				$mapsToGenerate[$layer['ID_WMS_MAPS']]=1;
				$mapsToGenerate[$layer['OLD_ID_WMS_MAPS']]=1;
			}


			### Aktywacja nieaktywnych warstw, ktore nalezy zaktywowac
			$layers=DB::getPDO()->fetchall("select ID,ID_WMS_MAPS,LAYER_TYPE,A_STATUS from WMS_LAYERS where A_STATUS='NORMAL' and FILE_STATUS='GENERATED' and MAP_STATUS='INACTIVE'");
			foreach ($layers as $layer) {
				if (!file_exists($wmsDirLocation.'/.maps/.single/'.$layer['ID'].$mapFileSuffix[$layer['LAYER_TYPE']])) {
					$destFolderPath = $this->getDirectory('WMS_LAYERS',$layer['ID']);
					symlink($destFolderPath.'/.wms/wms'.$mapFileSuffix[$layer['LAYER_TYPE']],$wmsDirLocation.'/.maps/.single/'.$layer['ID'].$mapFileSuffix[$layer['LAYER_TYPE']]);
				}
				DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'MAP_STATUS'=>'ACTIVE'));
				$mapsToGenerate[$layer['ID_WMS_MAPS']]=1;
			}


			### Dezaktywacja aktywnych warstw, ktore nalezy zdezaktywowac
			$layers=DB::getPDO()->fetchall("select ID,ID_WMS_MAPS,LAYER_TYPE from WMS_LAYERS where MAP_STATUS='ACTIVE' and (A_STATUS!='NORMAL' or FILE_STATUS!='GENERATED')");
			foreach ($layers as $layer) {
				if (is_link($wmsDirLocation.'/.maps/.single/'.$layer['ID'].$mapFileSuffix[$layer['LAYER_TYPE']])) $res=unlink($wmsDirLocation.'/.maps/.single/'.$layer['ID'].$mapFileSuffix[$layer['LAYER_TYPE']]);
				DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'MAP_STATUS'=>'INACTIVE'));
				$mapsToGenerate[$layer['ID_WMS_MAPS']]=1;
			}


			### Wyszukanie map do wygenerowania
			$maps=DB::getPDO()->fetchall("select ID from WMS_MAPS where MAP_STATUS='NONE' and A_STATUS='NORMAL'");
			foreach ($maps as $map) $mapsToGenerate[$map['ID']]=1;


			### Oznaczenie map do generowania
			if ($mapsToGenerate) {
				foreach ($mapsToGenerate as $ID_WMS_MAPS => $x) {
					$mapsCount=DB::getPDO()->fetchall("select ID from WMS_MAPS where ID='".$ID_WMS_MAPS."' and A_STATUS='NORMAL'");
					if ($mapsCount)	DB::getDB()->UPDATE_OBJ('WMS_MAPS',(object) array('ID'=>$ID_WMS_MAPS,'MAP_STATUS'=>'TO_GENERATE','MAP_STATUS_INFO'=>'Mapa oczekuje na przetworzenie'));
				}
			}


			### Generowanie map
			$layerSchema['GeoTIFF']='
  LAYER
    NAME	"__NAME__"
    STATUS	OFF
    TYPE	RASTER
    CONNECTION	"http://127.0.0.1:34580/wms/__TYPE__/__ID__"
    CONNECTIONTYPE WMS
    EXTENT	__EXTENT__

    PROJECTION
      "init=epsg:__EPSG__"
    END

    METADATA
      WMS_TITLE	"__NAME__"
      WMS_SRS "EPSG:__EPSG__"
      WMS_EXTENT "__EXTENT__"
      WMS_NAME "__NAME__"
      WMS_FORMAT "image/png"
      WMS_SERVER_VERSION "1.1.1"
    END
  END
';

			$layerSchema['QGIS']='
  LAYER
    NAME        "__NAME__"
    STATUS      OFF
    TYPE        RASTER
    CONNECTION  "http://127.0.0.1:34580/wms/__TYPE__/__ID__"
    CONNECTIONTYPE WMS
    EXTENT      __EXTENT__

    PROJECTION
      "init=epsg:__EPSG__"
    END

    CLASS
      TEMPLATE "__TEMPLATE__"
    END

    METADATA
      WMS_TITLE "__NAME__"
      WMS_SRS "EPSG:__EPSG__"
      WMS_EXTENT "__EXTENT__"
      WMS_NAME "__NAME__"
      WMS_FORMAT "image/png"
      WMS_SERVER_VERSION "1.1.1"
    END
  END
';

			$layerSearch=array('__NAME__','__TYPE__','__ID__','__EXTENT__','__EPSG__','__TEMPLATE__');

			$mapHeadSchema='MAP

  NAME "__NAME__"
  STATUS ON
  SIZE 400 300
  EXTENT -180 -90 180 90
  UNITS METERS
  IMAGECOLOR 255 255 0
  IMAGETYPE PNG

  WEB
    METADATA
      WMS_TITLE "__NAME__"
      WMS_ENABLE_REQUEST "*"
      WMS_ONLINERESOURCE "__MAP_URL__"
      WMS_SRS "__EPSGS__"
    END
  END

  PROJECTION
    "init=epsg:4236"
  END
';
			$mapHeadSearch=array('__NAME__','__MAP_URL__','__EPSGS__');

			$mapTailSchema='
END
';

			$maps=DB::getPDO()->fetchall("select ID,MAP_NAME,MAP_FILE,MAP_URL from WMS_MAPS where MAP_STATUS='TO_GENERATE'");
			$urlLayerType=array('GeoTIFF'=>'.single','QGIS'=>'qgis');
			foreach ($maps as $map) {
				if ($map['MAP_FILE']) unlink($wmsDirLocation.'/.maps/'.$map['MAP_FILE']);
				$MAP_FILE=strtolower(V::convert($map['MAP_NAME'],'url'));
				if ($MAP_FILE=="") $MAP_FILE=$map['ID'];
				$MAP_URL='https://'.$_SERVER['SERVER_NAME'].'/wms/'.$MAP_FILE;
				$MAP_FILE.=".map";
				$otherMaps=DB::getPDO()->fetchall("select ID from WMS_MAPS where ID!='".$map['ID']."' and MAP_FILE='".$MAP_FILE."'");
				if ($otherMaps) {
					DB::getDB()->UPDATE_OBJ('WMS_MAPS',(object) array('ID'=>$map['ID'],'MAP_STATUS'=>'ERROR','MAP_STATUS_INFO'=>'Nieunikalna nazwa mapy','MAP_URL'=>'','MAP_FILE'=>''));
				} else {
					$layers=DB::getPDO()->fetchall("select ID,LAYER_NAME,FILE_NAME,EPSG,EXTENT,LAYER_TYPE from WMS_LAYERS where MAP_STATUS='ACTIVE' and ID_WMS_MAPS='".$map['ID']."'");
					$MAP_LAYERS="";
					foreach ($layers as $layer) {
						if ($layer['LAYER_NAME']) $LAYER_NAME=V::convert($layer['LAYER_NAME'],'url');
						else $LAYER_NAME=$layer['LAYER_TYPE']."_".$layer['ID'];
						$replace=array($LAYER_NAME,$urlLayerType[$layer['LAYER_TYPE']],$layer['ID'],$layer['EXTENT'],$layer['EPSG'],$wmsDirLocation.'/.maps/.template.html');
						$MAP_LAYERS .= str_replace($layerSearch,$replace,$layerSchema[$layer['LAYER_TYPE']]);
						$EPSGS['EPSG:'.$layer['EPSG']]=1;
					}

					$replace=array($map['MAP_NAME'],$MAP_URL,implode(" ",array_keys($EPSGS)));
					$MAP_TXT=str_replace($mapHeadSearch,$replace,$mapHeadSchema).$MAP_LAYERS.$mapTailSchema;
					file_put_contents($wmsDirLocation.'/.maps/'.$MAP_FILE,$MAP_TXT);
					DB::getDB()->UPDATE_OBJ('WMS_MAPS',(object) array('ID'=>$map['ID'],'MAP_STATUS'=>'GENERATED','MAP_STATUS_INFO'=>'Mapa wygenerowana poprawnie','MAP_URL'=>$MAP_URL,'MAP_FILE'=>$MAP_FILE));
				}
			}


			### Usunięcie zbędnych map
			$maps=DB::getPDO()->fetchall("select ID,MAP_FILE from WMS_MAPS where MAP_STATUS='GENERATED' and A_STATUS!='NORMAL'");
			foreach ($maps as $map) {
				if (file_exists($wmsDirLocation.'/.maps/'.$map['MAP_FILE'])) unlink($wmsDirLocation.'/.maps/'.$map['MAP_FILE']);
				DB::getDB()->UPDATE_OBJ('WMS_MAPS',(object) array('ID'=>$map['ID'],'MAP_STATUS'=>'NONE','MAP_STATUS_INFO'=>'Mapa usunięta','MAP_URL'=>'','MAP_FILE'=>''));
			}


			### Wygenerowanie plików tif warstw
			$bashSchema='
rm -rf __PATH__/.wms
mkdir __PATH__/.wms
lock="/tmp/generate_wms-__ID__.lock"
touch ${lock}
/opt/local/bin/gdaltindex -t_srs epsg:4326 /tmp/generate_wms-__ID__-shp_wgs84.shp "__PATH__/__FILE__" >/dev/null 2>&1
/opt/local/bin/ogr2ogr -f CSV /tmp/generate_wms-__ID__-shp_wgs84.csv /tmp/generate_wms-__ID__-shp_wgs84.shp -lco GEOMETRY=AS_WKT
wgs84=`cat /tmp/generate_wms-__ID__-shp_wgs84.csv |tr -d "\n" | cut -d "\"" -f 2`
/opt/local/bin/gdaltindex /tmp/generate_wms-__ID__-shp_orig.shp "__PATH__/__FILE__" >/dev/null 2>&1
extent=`/opt/local/bin/ogrinfo -al -so /tmp/generate_wms-__ID__-shp_orig.shp | grep Extent | sed "s/[(),\-]//g;s/  / /g;s/Extent: //g"`
rm -f /tmp/generate_wms-__ID__-shp_*
echo "${wgs84}|${extent}|" > __PATH__/.wms/.data
ok=0
/opt/local/bin/gdal_translate -of GTiff -co "TILED=YES" -co "COMPRESS=DEFLATE" -co "ZLEVEL=9" -co "BIGTIFF=YES" "__PATH__/__FILE__" "__PATH__/.wms/__FILE__" && /opt/local/bin/gdaladdo -r average "__PATH__/.wms/__FILE__" 2 4 8 16 32 64 128 && ok=1
rm -f ${lock}
touch __PATH__/.wms/.completed
if [ "$ok" -eq 1 ]; then
	echo "Successfully completed."
else
	cp tmp/generate_wms-__ID__.log __PATH__/.wms/.error
	echo "Error creating tif file."
fi
';
			$search=array('__ID__','__PATH__','__FILE__');

			$layers=DB::getPDO()->fetchall("select ID,FILE_NAME from WMS_LAYERS where LAYER_TYPE='GeoTIFF' and FILE_STATUS='TO_GENERATE'");
			foreach ($layers as $layer) {
echo $layer['ID']."\n";
				$destFolderPath = $this->getDirectory('WMS_LAYERS',$layer['ID']);
				if(!is_file($destFolderPath.'/'.$layer['FILE_NAME'])) {
					DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'ERROR','FILE_STATUS_INFO'=>'Wystąpił błąd pliku tif'));
					throw new Exception("Błąd pliku: ".$layer['FILE_NAME']);
				}
				$xml=@simplexml_load_string(shell_exec('/opt/local/bin/gdalsrsinfo -o xml '.$destFolderPath.'/'.$layer['FILE_NAME']));
				$epsg=$xml->{'gml:srsID'}->{'gml:name'};
				if (!$epsg) {
					DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'ERROR','FILE_STATUS_INFO'=>'Wystąpił błąd pliku tif - brak informacji o układzie współrzędnych'));
					throw new Exception("Błąd pliku: ".$layer['FILE_NAME']);
				}
				DB::getDB()->UPDATE_OBJ('WMS_LAYERS',(object) array('ID'=>$layer['ID'],'FILE_STATUS'=>'IN_PROGRESS','FILE_STATUS_INFO'=>'Plik w trakcie przetwarzania','EPSG'=>$epsg));
				$replace = array($layer['ID'],$destFolderPath,$layer['FILE_NAME']);
				$bash = str_replace($search,$replace,$bashSchema);
				file_put_contents('/tmp/generate_wms-'.$layer['ID'].'.sh',$bash);
				shell_exec('su - root -c "/bin/sh /tmp/generate_wms-'.$layer['ID'].'.sh > /tmp/generate_wms-'.$layer['ID'].'.log 2>&1 &"');
			}
				
		} catch (Exception $e) {
			echo $e->getMessage()."\n";
		}
	}

	public function doGenerateAction() {
		$this->doGenerate();
//		echo "OK\n";
	}

	public function reinstallAction() {
		$this->reinstall();
		die('OK');
	}

	public function reinstall() {
		$sqlList = array();
		//$sqlList['RemoveTable'] = "DROP TABLE IF EXISTS `CRM_CONFIG`";
		$sqlList['InstallLayerTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_LAYERS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `the_geom` polygon NOT NULL,
  `ID_PROJECT` int(11) NOT NULL,
  `LAYER_NAME` varchar(100) NOT NULL,
  `LAYER_TYPE` enum('GeoTIFF','QGIS') NOT NULL DEFAULT 'GeoTIFF',
  `OLD_LAYER_TYPE` enum('GeoTIFF','QGIS') NOT NULL,
  `LAYER_DESC` text NOT NULL,
  `ID_WMS_MAPS` int(11) NOT NULL,
  `OLD_ID_WMS_MAPS` int(11) NOT NULL,
  `FILE_NAME` varchar(255) NOT NULL,
  `FILE_STATUS` enum('NONE','TO_GENERATE','IN_PROGRESS','GENERATED','ERROR') NOT NULL DEFAULT 'NONE',
  `FILE_STATUS_INFO` varchar(255) NOT NULL,
  `EPSG` int(11) NOT NULL,
  `EXTENT` varchar(100) NOT NULL,
  `MAP_STATUS` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'INACTIVE',
  `FILE_MD5` varchar(32) NOT NULL,
  `A_RECORD_CREATE_DATE` datetime NOT NULL,
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL,
  `A_RECORD_UPDATE_DATE` varchar(18) NOT NULL,
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL,
  `L_APPOITMENT_DATE` datetime NOT NULL,
  `L_APPOITMENT_USER` varchar(40) NOT NULL,
  `L_APPOITMENT_PERIOD` varchar(30) NOT NULL,
  `L_APPOITMENT_INFO` varchar(200) NOT NULL,
  `L_APPOITMENT_TYPE` enum('','ARRANGED','TO_ARRANGE','CANCELLED','TO_CANCEL','RELATE','WAIT') NOT NULL,
  `A_PROBLEM` enum('','WARNING','PROBLEM','SERIOUS','UNVERIFIED') NOT NULL,
  `A_PROBLEM_DESC` varchar(200) NOT NULL,
  `A_PROBLEM_DATE` datetime NOT NULL,
  `A_STATUS` enum('DELETED','MONITOR','NORMAL','OFF_HARD','OFF_SOFT','WAITING','WARNING') NOT NULL DEFAULT 'WAITING',
  `A_STATUS_INFO` varchar(100) NOT NULL,
  `A_STATUS_LASTCHANGE_DATE` datetime NOT NULL,
  `A_ADM_COMPANY` varchar(100) NOT NULL,
  `A_CLASSIFIED` varchar(100) NOT NULL,
  `SYNC_SQIX_STATUS` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
SQL;

                $sqlList['InstallLayerHistTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_LAYERS_HIST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USERS2` int(11) NOT NULL,
  `the_geom` text,
  `ID_PROJECT` varchar(20) DEFAULT 'N/S;',
  `LAYER_NAME` varchar(100) DEFAULT 'N/S;',
  `LAYER_TYPE` varchar(10) DEFAULT 'N/S;',
  `OLD_LAYER_TYPE` varchar(10) DEFAULT 'N/S;',
  `LAYER_DESC` varchar(10000) DEFAULT 'N/S;',
  `ID_WMS_MAPS` varchar(20) DEFAULT 'N/S;',
  `OLD_ID_WMS_MAPS` varchar(20) DEFAULT 'N/S;',
  `FILE_NAME` varchar(255) DEFAULT 'N/S;',
  `FILE_STATUS` varchar(20) DEFAULT 'N/S;',
  `FILE_STATUS_INFO` varchar(255) DEFAULT 'N/S;',
  `EPSG` varchar(10) DEFAULT 'N/S;',
  `EXTENT` varchar(100) DEFAULT 'N/S;',
  `MAP_STATUS` varchar(20) DEFAULT 'N/S;',
  `FILE_MD5` varchar(32) DEFAULT 'N/S;',
  `A_RECORD_CREATE_DATE` varchar(30) DEFAULT 'N/S;',
  `A_RECORD_CREATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_DATE` varchar(18) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_DATE` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_USER` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_PERIOD` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_INFO` varchar(200) DEFAULT 'N/S;',
  `L_APPOITMENT_TYPE` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM_DESC` varchar(200) DEFAULT 'N/S;',
  `A_PROBLEM_DATE` varchar(255) DEFAULT 'N/S;',
  `A_STATUS` varchar(255) DEFAULT 'N/S;',
  `A_STATUS_INFO` varchar(100) DEFAULT 'N/S;',
  `A_STATUS_LASTCHANGE_DATE` varchar(255) DEFAULT 'N/S;',
  `A_ADM_COMPANY` varchar(100) DEFAULT 'N/S;',
  `A_CLASSIFIED` varchar(100) DEFAULT 'N/S;',
  `SYNC_SQIX_STATUS` varchar(100) DEFAULT 'N/S;',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin2;
SQL;

		$sqlList['InstallMapTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_MAPS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `MAP_NAME` varchar(100) NOT NULL,
  `MAP_DESC` text NOT NULL,
  `MAP_STATUS` enum('NONE','TO_GENERATE','IN_PROGRESS','GENERATED','ERROR') NOT NULL DEFAULT 'NONE',
  `MAP_STATUS_INFO` varchar(255) NOT NULL,
  `MAP_URL` varchar(100) NOT NULL,
  `MAP_FILE` varchar(100) NOT NULL,
  `A_RECORD_CREATE_DATE` datetime NOT NULL,
  `A_RECORD_CREATE_AUTHOR` varchar(40) NOT NULL,
  `A_RECORD_UPDATE_DATE` varchar(18) NOT NULL,
  `A_RECORD_UPDATE_AUTHOR` varchar(40) NOT NULL,
  `L_APPOITMENT_DATE` datetime NOT NULL,
  `L_APPOITMENT_USER` varchar(40) NOT NULL,
  `L_APPOITMENT_PERIOD` varchar(30) NOT NULL,
  `L_APPOITMENT_INFO` varchar(200) NOT NULL,
  `L_APPOITMENT_TYPE` enum('','ARRANGED','TO_ARRANGE','CANCELLED','TO_CANCEL','RELATE','WAIT') NOT NULL,
  `A_PROBLEM` enum('','WARNING','PROBLEM','SERIOUS','UNVERIFIED') NOT NULL,
  `A_PROBLEM_DESC` varchar(200) NOT NULL,
  `A_PROBLEM_DATE` datetime NOT NULL,
  `A_STATUS` enum('DELETED','MONITOR','NORMAL','OFF_HARD','OFF_SOFT','WAITING','WARNING') NOT NULL DEFAULT 'WAITING',
  `A_STATUS_INFO` varchar(100) NOT NULL,
  `A_STATUS_LASTCHANGE_DATE` datetime NOT NULL,
  `A_ADM_COMPANY` varchar(100) NOT NULL,
  `A_CLASSIFIED` varchar(100) NOT NULL,
  `SYNC_SQIX_STATUS` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;

		$sqlList['InstallMapHistTable'] = <<<SQL
CREATE TABLE IF NOT EXISTS `WMS_MAPS_HIST` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USERS2` int(11) NOT NULL,
  `MAP_NAME` varchar(100) DEFAULT 'N/S;',
  `MAP_DESC` varchar(10000) NOT NULL DEFAULT 'N/S;',
  `MAP_STATUS` varchar(20) NOT NULL DEFAULT 'N/S;',
  `MAP_STATUS_INFO` varchar(255) NOT NULL DEFAULT 'N/S;',
  `MAP_URL` varchar(100) NOT NULL DEFAULT 'N/S;',
  `MAP_FILE` varchar(100) NOT NULL DEFAULT 'N/S;',
  `A_RECORD_CREATE_DATE` varchar(30) DEFAULT 'N/S;',
  `A_RECORD_CREATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_DATE` varchar(18) DEFAULT 'N/S;',
  `A_RECORD_UPDATE_AUTHOR` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_DATE` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_USER` varchar(40) DEFAULT 'N/S;',
  `L_APPOITMENT_PERIOD` varchar(30) DEFAULT 'N/S;',
  `L_APPOITMENT_INFO` varchar(200) DEFAULT 'N/S;',
  `L_APPOITMENT_TYPE` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM` varchar(255) DEFAULT 'N/S;',
  `A_PROBLEM_DESC` varchar(200) DEFAULT 'N/S;',
  `A_PROBLEM_DATE` varchar(255) DEFAULT 'N/S;',
  `A_STATUS` varchar(255) DEFAULT 'N/S;',
  `A_STATUS_INFO` varchar(100) DEFAULT 'N/S;',
  `A_STATUS_LASTCHANGE_DATE` varchar(255) DEFAULT 'N/S;',
  `A_ADM_COMPANY` varchar(100) DEFAULT 'N/S;',
  `A_CLASSIFIED` varchar(100) DEFAULT 'N/S;',
  `SYNC_SQIX_STATUS` varchar(100) DEFAULT 'N/S;',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;
SQL;

		$sqlList['ActivateWms'] = <<<SQL
REPLACE INTO `CRM_CONFIG` (`CONF_KEY`,`CONF_VAL`) VALUES ('module_WMS','on');
SQL;

		$db = DB::getDB();
		if ($db->has_errors()) {
			throw new Exception("DB Errors: " . implode("\n<br>", $db->get_errors()));
		}
		foreach ($sqlList as $sqlName => $sql) {
			$res = $db->query($sql);
			if ($db->has_errors()) {
				throw new Exception("DB Errors at sql '{$sqlName}': " . implode("\n<br>", $db->get_errors()));
			}
		}
	}


}