se/SE/superedit-view_L_APPOITMENT_OVERWIEV.php

<?php


//funkcja przygotowuje widok view_L_APPOITMENT_OVERWIEV , sladajacy sie z wielu tabel zawierajacy wylacznie zagregowane spotkania

function view_L_APPOITMENT_OVERWIEV() {

$transform['S_ADDRESS_STREET']='ADRES';
$transform['S_PHONE_NUMBER']='ADRES';


$tables_ignore[]='BILLS_FVAT_POS';
$tables_ignore[]='BILLS_FVAT';
$tables_ignore[]='CRM_LISTA_ZASOBOW_test';
$tables_ignore[]='IN7_MK_BAZA_DYSTRYBUCJI_copy_20140329';




$trans['ID']='ID_TABLE';
$trans['A_STATUS']='A_STATUS';
$trans['A_PROBLEM']='A_PROBLEM';
$trans['A_PROBLEM_DESC']='A_PROBLEM_DESC';
$trans['L_APPOITMENT_USER']='L_APPOITMENT_USER';
$trans['L_APPOITMENT_DATE']='L_APPOITMENT_DATE';
$trans['L_APPOITMENT_PERIOD']='L_APPOITMENT_PERIOD';
$trans['L_APPOITMENT_INFO']='L_APPOITMENT_INFO';
$trans['L_APPOITMENT_TYPE']='L_APPOITMENT_TYPE';
$trans['A_ADM_COMPANY']='A_ADM_COMPANY';
$trans['A_CLASSIFIED']='A_CLASSIFIED';
$trans['A_RECORD_UPDATE_DATE']='A_RECORD_UPDATE_DATE';

$sql_drop="drop view if exists view_L_APPOITMENT_OVERVIEW ; SET @cnt = 0; ";
$sql_create="create VIEW `view_L_APPOITMENT_OVERVIEW` AS";

$conn = DB::getDB();
Lib::loadClass('DB_Util');
$tbls = DB_Util::get_table_list($conn,null,'BASE TABLE');

		DEBUG_S(-3,'tbls',$tbls,__FILE__,__FUNCTION__,__LINE__);

foreach($tbls as $tbl) {
	if(!strstr($tbl, '_HIST')
	&&(!strstr($tbl, '_tmp'))
	&&(!strstr($tbl, 'view'))
	&&(!strstr($tbl, '_OLD'))
	&&(!strstr($tbl, 'temp_'))

	&&(!in_array($tbl, $tables_ignore))
	) {
		
		$keys=DB_Util::get_table_keys($conn,$tbl);
		DEBUG_S(-3,'keys'.$tbl,$keys,__FILE__,__FUNCTION__,__LINE__);
		
		
		
		$struct=DB_Util::get_table_struct($conn,$tbl);
		DEBUG_S(-3,'Struct'.$tbl,$struct,__FILE__,__FUNCTION__,__LINE__);
		$ok=true;
		foreach($trans as $fld=>$trans_) {
			if(!isset($struct[$fld])) $ok=false;
			if(!isset($keys['`'.$fld.'`']) && $fld!='ID' ) $conn->add_index($tbl,$fld);
		}
		if($ok) { //sa wszystkie kolumny do utworzenia widoku
			$transform_=", '' as `ADRES` ";
			foreach($transform as $src=>$dst) {
				if(isset($struct[$src])) {
					$transform_=", `".$src."` as `".$dst."`";
					echo "<br> Jest transform!";
				} else echo "<br> Nie ma ".$src." w tabeli".$tbl;
			}
			
			$sql_=array();
			if(isset($sql)) $sql[]=" union ";
			foreach($trans as $fld=>$as) {
			 $sql_[]="`".$fld."` as `".$as."`";
			}
			$sql[]=" ( select  `ID`, '".$tbl."' as `TABLE`,".implode(',', $sql_).$transform_." from `".$tbl."` where ( L_APPOITMENT_DATE!='' )   order by A_RECORD_UPDATE_DATE DESC  ) ";
			
			
			$msc=microtime(true);
			$conn->query("select  `ID`, '".$tbl."' as `TABLE`,".implode(',', $sql_).$transform_." from `".$tbl."` where L_APPOITMENT_DATE!=''   order by A_RECORD_UPDATE_DATE DESC   ");
			$msc=microtime(true)-$msc;
			$czas[]="Czas ".$msc." dla tabeli ".$tbl;
			
		}
		
		
	}
	
}




		DEBUG_S(3,'sql',$sql,__FILE__,__FUNCTION__,__LINE__);

		$sql_alter=$sql_drop." ".$sql_create." ".implode(" ", $sql);
		DEBUG_S(-3,'sql_alter',$sql_alter,__FILE__,__FUNCTION__,__LINE__);
		DEBUG_S(-3,'czasy',$czas,__FILE__,__FUNCTION__,__LINE__);
		$res=$conn->query($sql_alter);
		DEBUG_S(-3,'res',$res);
//$struct=$conn->STRUCTURE_GENERATE($tbls);






}