fetchAll(" SELECT * FROM `CRM_PROCES` WHERE FIND_IN_SET(`ID`, ( SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM ( SELECT @Ids := ( SELECT GROUP_CONCAT(`ID` SEPARATOR ',') FROM `CRM_PROCES` WHERE FIND_IN_SET(`PARENT_ID`, @Ids) AND `TYPE` <> 'PROCES_INIT' ) Level FROM `CRM_PROCES` JOIN (SELECT @Ids := 0) temp1 WHERE FIND_IN_SET(`PARENT_ID`, @Ids) ) temp2 )) AND `PARENT_ID` >= 0 AND `A_STATUS` <> 'DELETED' "); $state = array(); foreach ($rows as $key => $value) { $id = intval($value["ID"]); $parent_id = intval($value["PARENT_ID"]); $state[$id]["ID"] = $value["ID"]; $state[$id]["DESC"] = $value["DESC"]; $state[$id]["OPIS"] = $value["OPIS"]; $state[$id]["TYPE"] = $value["TYPE"]; $state[$id]["IF_TRUE_GOTO"] = intval($value["IF_TRUE_GOTO"]); $state[$id]["IF_TRUE_GOTO_FLAG"] = $value["IF_TRUE_GOTO_FLAG"]; $state[$id]["PARENT_ID"] = (intval($value["PARENT_ID"]) != 0) ? intval($value["PARENT_ID"]) : "#"; $state[$id]["SORT_PRIO"] = $value["SORT_PRIO"]; if (!isset($state[$id]["childs"])) $state[$id]["childs"] = array(); if (!isset($state[$parent_id]["childs"])) $state[$parent_id]["childs"] = array(); $temp = array(); $temp["ID"] = $id; array_push($state[$parent_id]["childs"], $temp); } Response::sendJsonExit($state); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getPathAjaxAction(){ try { $video_id = V::get('id', 0, $_GET, 'int'); $rows = DB::getPDO()->fetchAll(" SELECT f.ID, f.DESC FROM ( SELECT @id AS _id, (SELECT @id := PARENT_ID FROM CRM_PROCES WHERE ID = _id) FROM (SELECT @id := {$video_id}) tmp1 JOIN CRM_PROCES ON @id <> 0 ) tmp2 JOIN CRM_PROCES f ON tmp2._id = f.ID "); $rows = array_reverse($rows); Response::sendJsonExit($rows); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } private function addWatermark($url){ $watermark_file = "/Library/Server/Web/Data/Sites/Default/SE/stuff/images/play.png"; //to change $image = new Imagick(); $image->readImage($url); $watermark = new Imagick(); $watermark->readImage($watermark_file); $image->resizeImage(500,500,Imagick::FILTER_LANCZOS,1); $image->compositeImage($watermark, imagick::COMPOSITE_OVER, 0, 0); $image->writeImage($url); $image->clear(); $image->destroy(); $watermark->clear(); $watermark->destroy(); } public function uploadVideoAction() { if (!User::logged()) return; $CRM_VIDEO = "/Library/Server/Web/Data/Sites/Default/PLIKI/bartosz_procesy5_pl/CRM_VIDEO/"; $url = $_FILES["files"]["tmp_name"][0]; $size = $_FILES["files"]["size"][0]; $name = $_FILES["files"]["name"][0]; $finfo = new finfo(FILEINFO_MIME_TYPE); $type = $finfo->file($url); $ext = pathinfo($name, PATHINFO_EXTENSION); $imgExtList = array("image/png", "image/jpeg"); $vidExtList = array("video/mp4"); $info = new stdClass(); $statement = DB::getPDO()->prepare("INSERT INTO CRM_IMAGE(NAME, TYPE, REMOTE_TABLE, REMOTE_ID, IMAGE, SIZE) VALUES(:NAME, :TYPE, :REMOTE_TABLE, :REMOTE_ID, :IMAGE, :SIZE)"); if(in_array($type, $imgExtList)){ // $content = 'data:'.$type.';base64,'.base64_encode(file_get_contents($url));// $content = file_get_contents($url); $statement->bindParam(':NAME', $name, PDO::PARAM_STR); $statement->bindParam(':TYPE', $type, PDO::PARAM_STR); $imageType = "ADMIN_USERS"; $statement->bindParam(':REMOTE_TABLE', $imageType, PDO::PARAM_STR); $idUser = User::getID(); $statement->bindParam(':REMOTE_ID', $idUser, PDO::PARAM_INT); $statement->bindParam(':IMAGE', $content, PDO::PARAM_LOB); $statement->bindParam(':SIZE', $size, PDO::PARAM_INT); $statement->execute(); $info->statusCode = 0; $info->statusText = "Plik załadowany pomyślnie"; }else if(false && in_array($type, $vidExtList)){ //Only image files $statement_video = DB::getPDO()->prepare("INSERT INTO CRM_VIDEO(NAME, TYPE, REMOTE_TABLE, REMOTE_ID, PARENT_ID, HAS_FILE, EXT, VIDEO_START, VIDEO_END) VALUES(:NAME, :TYPE, :REMOTE_TABLE, :REMOTE_ID, :PARENT_ID, :HAS_FILE, :EXT, :VIDEO_START, :VIDEO_END)"); $cmd_duration = "/usr/local/bin/ffmpeg -i ".$url." 2>&1 | grep \"Duration\" | cut -d, -f1 | sed 's#Duration\:##' | sed 's# ##' | cut -d. -f1"; $str_time = exec($cmd_duration); $str_time = preg_replace("/^([\d]{1,2})\:([\d]{2})$/", "00:$1:$2", $str_time); sscanf($str_time, "%d:%d:%d", $hours, $minutes, $seconds); $time_seconds = $hours * 3600 + $minutes * 60 + $seconds; $statement_video->execute(array( ":NAME" => $name, ":TYPE" => $type, ":REMOTE_TABLE" => "ADMIN_USERS", ":PARENT_ID" => 0, ":REMOTE_ID" => User::getID(), ":EXT" => $ext, ":HAS_FILE" => 1, ":VIDEO_START" => 0, ":VIDEO_END" => $time_seconds, )); $id = DB::getPDO()->lastInsertId(); $temp_file = tempnam(sys_get_temp_dir(), 'thumbnail_'); rename($temp_file, $temp_file .= '.png'); $cmd = '/usr/local/bin/ffmpeg -y -i '.$url.' -ss 00:00:1 -vframes 1 '.$temp_file.' 2>&1'; shell_exec($cmd); $this->addWatermark($temp_file); $img_content = 'data:'."image/png".';base64,'.base64_encode(file_get_contents($temp_file)); $img_size = filesize($temp_file); $statement->execute(array( ":NAME" => "Miniaturka dla ".$name, ":TYPE" => "image/png", ":REMOTE_TABLE" => "CRM_VIDEO", ":REMOTE_ID" => $id, ":IMAGE" => $img_content, ":SIZE" => $img_size )); move_uploaded_file ( $url, $CRM_VIDEO.$id.".".$ext ); $info->statusCode = 0; $info->statusText = "Plik załadowany pomyślnie" ; }else{ $info->statusCode = 1; $info->statusText = "Nieobsługiwany format pliku ".$type ; } Response::sendJsonExit($info); } public function getVideoFileAjaxAction(){ try { $video_id = V::get('id', 0, $_GET, 'int'); if ($video_id <= 0) throw new Exception("ID is not set."); $sqlVideoId = DB::getPDO()->quote($video_id, PDO::PARAM_INT); $rows = DB::getPDO()->fetchAll(" SELECT f.VIDEO_ID FROM ( SELECT @id AS _id, (SELECT @id := PARENT_ID FROM CRM_VIDEO WHERE VIDEO_ID = _id) FROM (SELECT @id := {$sqlVideoId}) tmp1 JOIN CRM_VIDEO ON @id <> 0 ) tmp2 JOIN CRM_VIDEO f ON tmp2._id = f.VIDEO_ID WHERE f.HAS_FILE = 1 and f.TYPE = 'video/mp4' "); $rows_time = DB::getPDO()->fetchAll(" SELECT `VIDEO_START`, `VIDEO_END` FROM `CRM_VIDEO` WHERE `VIDEO_ID` = {$sqlVideoId} "); $id = $rows[0]["VIDEO_ID"]; $CRM_VIDEO = "/PLIKI/bartosz_procesy5_pl/CRM_VIDEO/"; $result["src"] = $CRM_VIDEO.$id.".mp4"; $result["VIDEO_START"] = $rows_time[0]["VIDEO_START"]; $result["VIDEO_END"] = $rows_time[0]["VIDEO_END"]; Response::sendJsonExit($result); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getThumbnailAjaxAction($video_id = -1){ try { $doReturn = false; if($video_id <= 0) $video_id = V::get('id', 0, $_GET, 'int'); else $doReturn = true; if ($video_id <= 0) throw new Exception("ID is not set."); $sqlVideoId = DB::getPDO()->quote($video_id, PDO::PARAM_INT); $rows = DB::getPDO()->fetchAll(" SELECT f.VIDEO_ID FROM ( SELECT @id AS _id, (SELECT @id := PARENT_ID FROM CRM_VIDEO WHERE VIDEO_ID = _id) FROM (SELECT @id := {$sqlVideoId}) tmp1 JOIN CRM_VIDEO ON @id <> 0 ) tmp2 JOIN CRM_VIDEO f ON tmp2._id = f.VIDEO_ID WHERE f.PARENT_ID = 0 "); if(isset($rows[0]["VIDEO_ID"])){ $video_parent_id = $rows[0]["VIDEO_ID"]; $sqlVideoParentId = DB::getPDO()->quote($video_parent_id , PDO::PARAM_INT); $rows = DB::getPDO()->fetchAll(" SELECT * FROM CRM_IMAGE WHERE REMOTE_TABLE = 'CRM_VIDEO' AND REMOTE_ID = {$sqlVideoParentId} "); if($doReturn) return $rows; else Response::sendJsonExit($rows); } } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getVideosClipboardAjaxAction(){ try { $id = User::getID(); if ($id <= 0) throw new Exception("ID is not set."); $sqlId = DB::getPDO()->quote($id, PDO::PARAM_INT); $rows = DB::getPDO()->fetchAll(" SELECT * FROM CRM_VIDEO WHERE REMOTE_TABLE = 'ADMIN_USERS' and REMOTE_ID = {$sqlId} "); foreach($rows as $key=>$value){ $rows[$key]["THUMBNAIL"] = $this->getThumbnailAjaxAction($value["VIDEO_ID"])[0]; } Response::sendJsonExit($rows); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getResByParentAjaxAction() { try { $idParent = V::get('parent_id', 0, $_GET, 'int'); if ($idParent <= 0) throw new Exception("ID is not set."); $sqlIdParent = DB::getPDO()->quote($idParent, PDO::PARAM_INT); $rows = DB::getPDO()->fetchAll(" select g.*, p1.ID as p1_ID, p1.DESC as p1_DESC,p2.ID as p2_ID, p2.DESC as p2_DESC, p3.ID as p3_ID, p3.DESC as p3_DESC from CRM_LISTA_ZASOBOW g left join CRM_LISTA_ZASOBOW p1 on(p1.ID = g.PARENT_ID) left join CRM_LISTA_ZASOBOW p2 on(p2.ID = p1.PARENT_ID) left join CRM_LISTA_ZASOBOW p3 on(p3.ID = p2.PARENT_ID) where g.`PARENT_ID` = {$sqlIdParent} ORDER BY g.SORT_PRIO ASC, g.ID DESC "); Response::sendJsonExit($rows); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getResTreeAjaxAction() { try { $id = V::get('id', '', $_GET); $sql = " select p1.ID as p1_ID, p1.DESC as p1_DESC,p2.ID as p2_ID, p2.DESC as p2_DESC, p3.ID as p3_ID, p3.DESC as p3_DESC from CRM_LISTA_ZASOBOW g left join CRM_LISTA_ZASOBOW p1 on(p1.ID = g.PARENT_ID) left join CRM_LISTA_ZASOBOW p2 on(p2.ID = p1.PARENT_ID) left join CRM_LISTA_ZASOBOW p3 on(p3.ID = p2.PARENT_ID) where g.`A_STATUS` not in ('DELETED') and (p1.ID is null or p1.`A_STATUS` not in ('DELETED')) and (p2.ID is null or p2.`A_STATUS` not in ('DELETED')) and (p3.ID is null or p3.`A_STATUS` not in ('DELETED')) and g.ID = {$id} limit 1 "; DBG::_('DBG_SQL', '>1', 'sql', $sql, __CLASS__, __FUNCTION__, __LINE__); $rows = DB::getPDO()->fetchAll($sql); Response::sendJsonExit($rows); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getResAjaxAction() { try { $word = V::get('word', '', $_GET); $filter = isset($_GET['filter']) ? (int)$_GET['filter'] : 0; $sqlFilter = ""; if (!empty($word)) { $sqlWord = DB::getPDO()->quote("%{$word}%", PDO::PARAM_STR); if (is_numeric($word)) { $sqlFilter .= "AND (g.`DESC` LIKE {$sqlWord} OR g.`ID` LIKE {$sqlWord})" . "\n"; } else { $sqlFilter .= "AND g.`DESC` LIKE {$sqlWord}" . "\n"; } } switch ($filter) { case 2: $sqlFilter .= "AND (g.`TYPE` = 'TABELA' OR g.`TYPE` = 'KOMORKA')" . "\n"; break; case 1: $sqlFilter .= "AND (g.`TYPE` = 'STANOWISKO' OR g.`TYPE` = 'DZIAL' OR g.`TYPE` = 'PODMIOT')" . "\n"; break; } $sql = " select g.*, p1.ID as p1_ID, p1.DESC as p1_DESC,p2.ID as p2_ID, p2.DESC as p2_DESC, p3.ID as p3_ID, p3.DESC as p3_DESC from CRM_LISTA_ZASOBOW g left join CRM_LISTA_ZASOBOW p1 on(p1.ID = g.PARENT_ID) left join CRM_LISTA_ZASOBOW p2 on(p2.ID = p1.PARENT_ID) left join CRM_LISTA_ZASOBOW p3 on(p3.ID = p2.PARENT_ID) where g.`A_STATUS` not in ('DELETED') and (p1.ID is null or p1.`A_STATUS` not in ('DELETED')) and (p2.ID is null or p2.`A_STATUS` not in ('DELETED')) and (p3.ID is null or p3.`A_STATUS` not in ('DELETED')) {$sqlFilter} limit 100 "; DBG::_('DBG_SQL', '>1', 'sql', $sql, __CLASS__, __FUNCTION__, __LINE__); $rows = DB::getPDO()->fetchAll($sql); Response::sendJsonExit($rows); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } } public function getSingleResAjaxReponseCallback() { if (!isset($_POST['data'])) throw new Exception("data is not set."); $ids = V::get('data', null, $_POST, 'uint_array'); if (empty($ids)) throw new Exception("data is not correct."); $sqlWhereIdIn = array(); foreach ($ids as $value) { $sqlWhereIdIn[] = DB::getPDO()->quote($value, PDO::PARAM_INT); } $sqlWhereIdIn = "g.ID in(" . implode(", ", $sqlWhereIdIn) . ")"; $rows = DB::getPDO()->fetchAll(" select g.*, p1.ID as p1_ID, p1.DESC as p1_DESC,p2.ID as p2_ID, p2.DESC as p2_DESC, p3.ID as p3_ID, p3.DESC as p3_DESC from CRM_LISTA_ZASOBOW g left join CRM_LISTA_ZASOBOW p1 on(p1.ID = g.PARENT_ID) left join CRM_LISTA_ZASOBOW p2 on(p2.ID = p1.PARENT_ID) left join CRM_LISTA_ZASOBOW p3 on(p3.ID = p2.PARENT_ID) where {$sqlWhereIdIn} "); return $rows; } public function getSingleResAjaxAction() { Response::sendTryCatchJson(array($this, 'getSingleResAjaxReponseCallback')); } public function handleAuth() { if (!User::logged()) { //throw new HttpException('Unauthorized', 401); User::authByRequest(); } // zapisać jsona w sesji } public function reinstallAction(){ DB::getPDO()->execSql(" CREATE TABLE IF NOT EXISTS `CRM_IMAGE` ( `ID` int(11) NOT NULL, `NAME` varchar(50) NOT NULL, `SIZE` int(11) NOT NULL, `TYPE` varchar(25) NOT NULL, `IMAGE` mediumblob NOT NULL, `ICON` tinyblob, `WIDTH` int(11) NOT NULL, `HEIGHT` int(11) NOT NULL, `REMOTE_TABLE` enum('CRM_LISTA_ZASOBOW','CRM_PROCES','CRM_WSKAZNIK','ADMIN_USERS','CRM_VIDEO') NOT NULL, `REMOTE_ID` int(11) NOT NULL, `DEST` varchar(16) NOT NULL, `A_CREATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `A_RECORD_CREATE_AUTHOR` varchar(100) NOT NULL, `SYNC_SQIX_STATUS` varchar(255) NOT NULL, `A_RECORD_CREATE_DATE` varchar(20) NOT NULL DEFAULT '0000-00-00 00:00:00', `A_RECORD_UPDATE_DATE` varchar(255) NOT NULL, `A_RECORD_UPDATE_AUTHOR` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin2 "); try { DB::getPDO()->execSql(" ALTER table `CRM_IMAGE` CHANGE `REMOTE_TABLE` `REMOTE_TABLE` enum('CRM_LISTA_ZASOBOW','CRM_PROCES','CRM_WSKAZNIK','ADMIN_USERS','CRM_VIDEO') NOT NULL "); } catch (Exception $e) { DBG::log($e); } } public function defaultAction() { UI::gora(); UI::menu(); try { $id = V::get('id', 0, $_REQUEST, 'int'); if ($id <= 0) $id = -1; if($id >= 0) UI::setTitleJsTag("Edytor Procesu {{$id}}"); else UI::setTitleJsTag("Tworzenie nowego procesu}"); $this->showEditor($id); } catch (Exception $e) { UI::alert('danger', "Error: " . $e->getMessage()); } UI::dol(); } public function showEditor($id) { echo ''; echo ''; echo ''; echo ''; $this->showEditorCss(); ?>
Uruchamianie aplikacji.