Symfony Profiler

<?php
namespace App\Controller;
use App\Service\SycoreService;
use Exception;
use Psr\Log\LoggerInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Serializer\Serializer;
use Symfony\Component\Serializer\Normalizer\GetSetMethodNormalizer;
use Symfony\Component\Serializer\Encoder\JsonEncoder;
use Symfony\Component\Serializer\Encoder\XmlEncoder;
use App\Entity\Chklst;
use App\Entity\ChklstTask;
use App\Entity\TaskServer;
use App\Entity\Queue;
use App\Entity\ChklstTemplateWebapp;
use App\Entity\User;
use Symfony\Component\Form\Extension\Core\Type\SubmitType;
use Symfony\Component\Form\Extension\Core\Type\IntegerType;
use App\Service\WebExFactorySymfonyService;
use App\Service\TelegramBCFactorySymfonyService;
use App\converger\rest_api_util\ResponseARC;
use Symfony\Component\Serializer\Normalizer\CustomNormalizer;
use phpseclib\Crypt\AES;
use App\Utility\ExcelFactory;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Symfony\Component\Security\Csrf\CsrfTokenManagerInterface;
use App\Command\libs\Tasks\GraphLinksModel;
use App\Repository\TaskRepository;
use Dompdf\Dompdf;
use Dompdf\Options;
use Symfony\Component\HttpFoundation\HeaderUtils;
use App\Command\libs\Functions;
use Doctrine\DBAL\Types\Types;
use App\Audit\WorkflowAuditLoggerFactory;
use App\Audit\WorkflowAuditLogger;
class ChecklistController extends AbstractController
{
// Temporaneo!
private $USE_NEW_IMPEXP = true;
private $sycoreService;
public function __construct(SycoreService $sycoreService) {
$this->sycoreService = $sycoreService;
}
private function getLogger()
{
$logger = $this->get('logger');
return $logger instanceof Logger ? $logger : null;
}
public function custom_addslashes($var, $params)
{
$db_driver = $params->get('db_driver');
if ($db_driver == "pdo_mysql") {
$textDecode = addslashes($var);
} else {
$textDecode = pg_escape_string($var);
}
return $textDecode;
}
/*** AGGIUNTA METODI PER DESIGNER **/
public function uploadImageFlowAction(Request $request, $cid, LoggerInterface $logger, ParameterBagInterface $params)
{
$target_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $cid . '/' . $request->getBasePath();
//$logger = $this->getLogger();
if (isset($_FILES['file'])) {
$logger->debug("_FILES set");
} else {
$logger->debug(json_encode($_FILES));
}
if (isset($_POST['file'])) {
$logger->debug("_POST set");
$logger->debug(json_encode($_POST));
} else {
$logger->debug(json_encode($_POST));
}
if (0 < $_POST['file']['error']) {
$logger->debug("Error: " . $_POST['file']['error'] . "<br>");
//echo 'Error: ' . $_FILES['file']['error'] . '<br>';
} else {
move_uploaded_file($_POST['file']['tmp_name'], $target_dir . $_POST['file']['name']);
}
}
public function getSybotWorkflows(Request $request, LoggerInterface $logger, ParameterBagInterface $params){
$em = $this->getDoctrine()->getManager();
$user = $this->getUser();
$userId = $user->getId();
//-----------------------------------------------------------------
// Recupera la lista dei workflow visibili per l’utente corrente
//-----------------------------------------------------------------
$listWorkflow = "
SELECT DISTINCT
ct.id,
name,
description,
CASE hidden WHEN 1 THEN 'NO' ELSE 'SI' END AS hidden,
CASE flag_type_checklist
WHEN 'A' THEN 'Azioni'
WHEN 'V' THEN 'Verifica'
WHEN 'G' THEN 'Generale'
WHEN 'TC' THEN 'Tars Collaudo'
WHEN 'TP' THEN 'Tars Produzione'
END AS flag_type_checklist,
TO_CHAR(ct.data_creazione, 'dd-mm-yyyy hh24:mi:ss') AS data_creazione,
TO_CHAR(ct.data_modifica, 'dd-mm-yyyy hh24:mi:ss') AS data_modifica,
ct.locked,
ct.modificato_da,
ct.lock_session_user_id,
ctc.nome AS categoria
FROM chklst_template ct
JOIN chklst_template_role cr ON ct.id = cr.id_template
JOIN chklst_template_categories ctc ON ct.id_category = ctc.id
JOIN (
SELECT ur.id_roles
FROM user_acm u
JOIN user_profile_roles ur ON u.id = ur.id_user
WHERE u.id = :userId
AND ur.id_profile IN (1, 2)
) ur ON cr.id_roles = ur.id_roles
ORDER BY name, hidden DESC, flag_type_checklist
";
$statement = $em->getConnection()->prepare($listWorkflow);
$statement->bindValue('userId', $userId);
$statement->execute();
$sqlListWorkflow = $statement->fetchAll();
$ListWorkflow =array();
foreach($sqlListWorkflow as $r)
{
//RECUPERA I PARAMETRI ASSOCIATI AL WORKFLOW
$sqlParams= "select * from chklst_template_parameters
where chklst_id = :id";
$statement = $em->getConnection()->prepare($sqlParams);
$statement->bindValue('id', $r['id']);
$statement->execute();
$ResulQueryParams = $statement->fetchAll();
//
//-------------------------------------------------
// Recupera i ruoli associati al template corrente
//-------------------------------------------------
$sqlRuoli = "
SELECT STRING_AGG(r.description_roles, '; ') AS ruoli
FROM chklst_template_role ctr
JOIN roles r ON ctr.id_roles = r.id_roles
WHERE ctr.id_template = :id
";
$statement = $em->getConnection()->prepare($sqlRuoli);
$statement->bindValue('id', $r['id']);
$statement->execute();
$ResulQueryRuoli = $statement->fetch();
$Functions = new Functions($this, $logger, $em);
$checkTemplateByRole = $Functions->chkOpenTemplateByRoleBpmn($r['id'],$userId) ;
$templateRole=null;
foreach($checkTemplateByRole as $c){
$templateRole=$c['result_role'];
}
//------------------------------------------------
// Ultima esecuzione del template
//------------------------------------------------
/*$sqlExec = "
SELECT TO_CHAR(MAX(run_start), 'dd-mm-yyyy hh24:mi:ss') AS max_exec
FROM chklst
WHERE from_template_id = :id
";
$statement = $em->getConnection()->prepare($sqlExec);
$statement->bindValue('id', $r['id']);
$statement->execute();
$ResulQueryMaxExec = $statement->fetch();*/
//------------------------------------------------
// Username dell'ultimo modificatore
//------------------------------------------------
$sqlUsername = "
SELECT username
FROM user_acm
WHERE id = :modificato_da
";
$statement = $em->getConnection()->prepare($sqlUsername);
$statement->bindValue('modificato_da', $r['modificato_da']);
$statement->execute();
$modificatoDa_Username = $statement->fetch();
$ListWorkflow_tmp = array('id' => $r['id'],
'name' => $r['name'],
'description_workflow' => $r['description'],
'visibilita_workflow' => $r['hidden'],
'tipologia_workflow' => $r['flag_type_checklist'],
//'validita_workflow' => $r['task_validate'],
'data_creazione' => $r['data_creazione'],
'data_modifica' => $r['data_modifica'],
'modificato_da' =>$r['modificato_da'],
'lock_session'=>$r['lock_session_user_id'],
'modificatoDa_Username' =>$modificatoDa_Username['username'] ?? '',
'locked' => $r['locked'],
'ruoli' => $ResulQueryRuoli['ruoli'],
//'MaxExec' => $ResulQueryMaxExec['max_exec'],
'categoria' => $r['categoria'],
'templateRole' => $templateRole,
'userId' => $userId ,
'wfl_params' => $ResulQueryParams
);
array_push($ListWorkflow,$ListWorkflow_tmp);
}
return new JsonResponse(['data' => $ListWorkflow]);
}
public function checklistSelectionFlowChartAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
//$logger->debug('[checklistSelectionFlowChart] PHP INI SIZE: ' . ini_get('upload_max_filesize'));
//$logger->debug('[checklistSelectionFlowChart] PHP FILE UPLOAD: ' . ini_get('max_file_uploads'));
$upload_max_filesize = ini_get('upload_max_filesize');
$upload_max_filesize_n = preg_replace('/[^0-9]/', '', $upload_max_filesize);
$upload_max_filesize_t = preg_replace('/[0-9]/', '', $upload_max_filesize);
//CONVERSIONE A BYTE
$upload_max_filesize = $this->toByteSize($upload_max_filesize_n, $upload_max_filesize_t);
$user = $this->getUser();
$userId = $user->getId();
$Functions = new Functions($this, $logger, $em);
$roles = $Functions->getAllUserRoles($userId) ;
$UserRole = $user->getUserRole();
$logger->debug("[serviceAssuranceDashboardAction] UserRole " . $UserRole);
$max_file_uploads = ini_get('max_file_uploads');
if (isset($_GET["selectionType"])) {
$selectionType = $_GET["selectionType"];
$logger->debug("[checklistSelectionFlowChart] selectionType " . $selectionType);
$selectedToOpen = 'ND';
if ($selectionType == "new") {
$selectedToOpen = $_GET["selectedToOpen"];
}
} else {
$selectionType = 'ND';
$selectedToOpen = 'ND';
}
/* export config*/
/*$sql = "select name_config, value from config";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$config = $stmt->fetchAll();*/
$sql = "select id, nome, descrizione from chklst_template_categories order by id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$categories = $stmt->fetchAll();
return $this->render(
'checklistSelectionFlowchart.html.twig',
array(
'upload_max_filesize' => $upload_max_filesize,
'max_file_uploads' => $max_file_uploads,
'logged_user' => $user,
'roles' => $roles,
'user_role' => $UserRole,
'selectionType' => $selectionType,
'selectedToOpen' => $selectedToOpen,
//'config' => $config,
'categories' => $categories
)
);
}
public function checklistVerifyNameAction(LoggerInterface $logger)
{
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('checklist_verify_name', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$checklistName = $_POST["checklist_name"];
if ($checklistName) {
$sql = "select count(1) from chklst_template where name = :name";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('name', $checklistName);
$stmt->execute();
$ResulQuery = $stmt->fetch();
$count = $ResulQuery;
$ret = ['data' => $count];
return new JsonResponse($ret);
}
return new JsonResponse(['data' => ['count' => 'null']]);
}
public function checklistImportSelectionAction(Request $request, LoggerInterface $logger)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('checklist_import_selection', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$user = $this->getUser();
$res_json = new JsonResponse;
$arrayListChecklist = array();
$user = $this->getUser();
$userId = $user->getId();
$logger->debug("[ChecklistImportSelection] Visualizzazione checklist esistenti " . $userId);
$q = "select distinct a.tipologia, a.id, a.name, a.description, a.hidden, a.route, a.flag_type_checklist, a.categoria, a.task_validate,
coalesce(to_char(a.data_creazione, 'DD-MM-YYYY'), '-') data_creazione,
coalesce(to_char(a.data_modifica,'DD-MM-YYYY'), '-') data_modifica
from
(select 'workflow' tipologia, ct.id, name, description, case hidden when 1 then 'NO' else 'SI' end hidden, '' route,
case flag_type_checklist when 'A' then 'Azioni'
when 'V' then 'Verifica'
when 'G' then 'Generale'
when 'TC' then 'Tars Collaudo'
when 'TP' then 'Tars Produzione'
end flag_type_checklist,
ctc.nome categoria,
cj.task_validate,
ct.data_creazione, ct.data_modifica
from chklst_template ct join chklst_jflow cj on (ct.id = cj.chklst_template)
join chklst_template_role cr on ct.id = cr.id_template
join chklst_template_categories ctc on ct.id_category = ctc.id
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = :userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles ) a
order by
a.name, a.hidden desc, a.flag_type_checklist";
$statement = $em->getConnection()->prepare($q);
$statement->bindValue('userId', $userId);
$statement->execute();
$ResulQuery = $statement->fetchAll();
$Ruoli = "";
foreach ($ResulQuery as $res) {
// $logger->debug("[ChecklistImportSelection] handling: ". $res["name"]);
//$logger->debug("[ChecklistImportSelection] array ".json_encode($res));
if ($res['tipologia'] == 'workflow') {
$sqlRuoli = "select string_agg( r.description_roles, '; ') AS ruoli
from chklst_template_role ctr, roles r
where ctr.id_roles = r.id_roles
and ctr.id_template = :cid ";
$statement = $em->getConnection()->prepare($sqlRuoli);
$statement->bindValue('cid', $res['id']);
$statement->execute();
$ResulQueryRuoli = $statement->fetchAll()[0];
$Ruoli = $ResulQueryRuoli['ruoli'];
$sqlImgPreview = "select flow_image from chklst_jflow where chklst_template = :cid ";
$statement = $em->getConnection()->prepare($sqlImgPreview);
$statement->bindValue('cid', $res['id']);
$statement->execute();
$ResulSqlImgPreview = $statement->fetchAll();
$imgPreviewPath = "#";
if ($ResulSqlImgPreview[0]["flow_image"]) {
$imgPreviewPath = $ResulSqlImgPreview[0]['flow_image'];
}
} else {
$imgPreviewPath = '';
}
$arrayListChecklistTmp = array(
'tipologia' => $res['tipologia'],
'idTemplate' => $res['id'],
'NomeChecklist' => $res['name'],
'Descrizione' => $res['description'],
'Visibilita' => $res['hidden'],
'TipoChecklist' => $res['flag_type_checklist'],
'Categoria' => $res['categoria'],
'Ruoli' => $Ruoli,
'Route' => $res['route'],
'ImgPreviewPath' => $imgPreviewPath,
'task_validate' => $res['task_validate'],
'DataCreazione' => $res['data_creazione'],
'DataModifica' => $res['data_modifica']
);
array_push($arrayListChecklist, $arrayListChecklistTmp);
}
$logger->debug("[ChecklistImportSelection] Ritorno al FrontEnd l'array");
$res_json->setData(array('arrayListChecklist' => $arrayListChecklist));
return $res_json;
}
public function checklistFlowChartLoadWebexAction(Request $request, LoggerInterface $logger, WebExFactorySymfonyService $webExFactorySymfonyService, ParameterBagInterface $params)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('checklist_flowchart_webex', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
//$logger = $this->getLogger();
$user = $this->getUser();
// qui la chiamata per recuperare la factory
$factoryWE = $webExFactorySymfonyService;
// ottengo il client dalla factory
$clientWE = $factoryWE->getClient();
// chiamo il servizio che mi da la lista delle rooms
$retMsg = $clientWE->listMyRooms();
// risultato ok, possiamo recuperare le info
if ($retMsg->getResult() == ResponseARC::OK) {
$rooms = $retMsg->getData();
} else {
$rooms = [];
}
// se > 0, ci sono updates da registrare, altrimenti no
if (count($rooms) > 0) {
// $chatId Ã¨ l'id che dobbiamo usare per contattare telegram
// hai un'occorrenza se
// bot aggiunto o rimosso da una chat oppure se Ã¨ stato cambiato nome alla chat
foreach ($rooms as $room) {
$room_id = $room["id"];
$room_title = $room["title"];
$sql = "INSERT INTO webex_rooms (id,title)
values ('$room_id', '$room_title')
ON CONFLICT (id) DO UPDATE
SET title = '$room_title'";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
} //hcaerof on $updates
} // fi count($updates)
$sql = "select id, title from webex_rooms";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$ResulQuery = $stmt->fetchAll();
$rooms = $ResulQuery;
$ret = ['data' => ['webex_rooms' => $rooms]];
return new JsonResponse($ret);
}
public function checklistFlowChartLoadSelectedWebexAction(Request $request, LoggerInterface $logger)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('checklist_flowchart_webex_selected', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
//$myId = $request->query->get('id');
$sql = "select id, title from webex_rooms";
$stmt = $em->getConnection()->prepare($sql);
//$stmt->bindValue('myId', $myId);
$stmt->execute();
$ResulQuery = $stmt->fetchAll();
//$IdProfile=$ResulQuery['id_profile'];
$ret = ['data' => ['webex_rooms' => $ResulQuery]];
return new JsonResponse($ret);
}
public function checklistFlowChartLoadTelegramAction(Request $request, LoggerInterface $logger, TelegramBCFactorySymfonyService $telegramBCFactorySymfonyService, ParameterBagInterface $params)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('checklist_flowchart_telegram', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
//$logger = $this->getLogger();
$user = $this->getUser();
$sql = "select * from anag_service where active = 'Y' and service_type='telegram' ";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$telegram_chat = $stmt->fetchAll();
$ret = ['data' => ['telegram_chat' => $telegram_chat]];
return new JsonResponse($ret);
// qui la chiamata al per ottenere la factory
$factoryTeleg = $telegramBCFactorySymfonyService;
// ottengo il client dalla factory
$botClient = $factoryTeleg->getClient();
// chiamo il getUpdates sul client
$ret = $botClient->getUpdates();
// check se il servizio Ã¨ andato bene
if ($ret->getResult() == ResponseARC::OK) {
$updates = $ret->getData();
/*
* array associativo organizzata come segue :
[ "$chatId" => ["active" => $val // flag Y|N che indica se il bot Ã¨ assegnato alla chat o no
,"title" => $val // titolo della chat
,"dtUpdate" => $val // DateTime dell'update fornito dal server Telegram
,"uxTimestamp" => $val // Timestamp Unix dell'update fornito dal server Telegram
,"updateId" => $val // Id Telegram assegnato all'update
]
,"$chatId" => [...],
... ]
*/
// se > 0, ci sono updates da registrare, altrimenti no
if (count($updates) > 0) {
// $chatId Ã¨ l'id che dobbiamo usare per contattare telegram
// hai un'occorrenza se
// bot aggiunto o rimosso da una chat oppure se Ã¨ stato cambiato nome alla chat
foreach ($updates as $chatId => $updInfo) {
$chat_id = $chatId;
$active = $updInfo["active"];
$title = $updInfo["title"];
$dt_update = $updInfo["dtUpdate"];
$ux_timestamp = $updInfo["uxTimestamp"];
$update_id = $updInfo["updateId"];
$service_type = "telegram";
$sql = "insert into anag_service (chat_id,active,title,dt_update,ux_timestamp,update_id,service_type)
values ('$chat_id', '$active', '$title', to_timestamp($ux_timestamp)::timestamp , $ux_timestamp, $update_id, '$service_type')
ON CONFLICT ('$chat_id') DO UPDATE set
active = '$active',
title = '$title',
dt_update = to_timestamp($ux_timestamp)::timestamp ,
ux_timestamp = $ux_timestamp,
update_id = $update_id,
service_type = '$service_type'";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
} //hcaerof on $updates
} // fi count($updates)
} // fi $ret->getResult();
$sql = "select * from anag_service where active = 'Y' and service_type='telegram' ";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$telegram_chat = $stmt->fetchAll();
$ret = ['data' => ['telegram_chat' => $telegram_chat]];
return new JsonResponse($ret);
}
private function getWorkflowParameters($id_workflow){
$conn = $this->getDoctrine()->getManager()->getConnection();
$query_parameters = 'SELECT * FROM chklst_template_parameters WHERE chklst_id = :id_workflow';
$statement = $conn-> prepare($query_parameters);
$statement->bindValue("id_workflow", $id_workflow);
$statement->execute();
$chklst_parameters = $statement->fetchAll();
return $chklst_parameters;
}
//----------------------------------------------------------------------------------
// Aggiorna i dati del Workflow (Nome, Descrizione, Ruoli, parametri, ecc...)
//----------------------------------------------------------------------------------
public function updateChktmplFlowAction(Request $request, $cid, LoggerInterface $logger, ParameterBagInterface $params, CsrfTokenManagerInterface $tokenManager)
{
$csrfToken = $request->request->get('csrftoken');
$forbiddenPattern = '/^(?:\$\$|#>)|[\'"]+/';
if (!$this->isCsrfTokenValid('chktmpl_flow_update', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$user = $this->getUser();
$userId = $user->getId();
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
//$db_driver = $params->get('db_driver');
$IdTemplate = $cid;
$id = $_POST["form_id_c"];
$name = trim($_POST["form_nome_c"]);
$description = $_POST["form_descrizione_c"];
//$hidden = $_POST["form_att_c"];
// -----------------------------------------------------------
// Nel caso in cui il nome è vuoto non procedo con l'update
// -----------------------------------------------------------
if($name == ""){
return new JsonResponse(['success' => false]);
}
if (isset($_POST["da_inattive" . '_' . $cid])) {
// $logger->debug("sto dentro isset da_inattive_" . $cid);
$q = "update chklst_template set name = :name, "
. " description = :description , data_modifica = now(), modificato_da = :modificato_da "
. " where id = :id;";
$statement = $connection->prepare($q);
$statement->bindValue('description', $description);
$statement->bindValue('modificato_da', $userId);
$statement->bindValue('name', $name);
$statement->bindValue('id', $id);
$statement->execute();
} else {
// $logger->debug("sto dentro ELSE isset da_inattive_" . $cid);
$flag_send_webex = "N";
$room_webex = "";
$text_send_webex = "Riepilogo esecuzione Checklist";
$to_send_message = "";
$cc_send_message = "";
$flag_send_mail = "N";
$sql = "select * from roles";
$stmt = $connection->prepare($sql);
$stmt->execute();
$roles = $stmt->fetchAll();
$lista_ruoli = preg_grep("/^ins_chk_2_*/", array_keys($_POST));
if (count($lista_ruoli) > 0) {
$sql = "delete from chklst_template_role where id_template = :cid ";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->execute();
foreach ($roles as $role) {
$id_role = $role['id_roles'];
foreach ($lista_ruoli as $l) {
if ($role['description_roles'] == $_POST[$l]) {
$sql = "insert into chklst_template_role (id_template,id_roles) values ($cid, $id_role)
ON CONFLICT (id_template,id_roles) DO NOTHING";
$statement = $connection->prepare($sql);
$statement->execute();
}
}
}
}
if (isset($_POST["form_category"])) {
$wflCategory = $_POST["form_category"];
}
$q = "update chklst_template set name = :name, "
. " description = :description , flag_send_webex= :flag_send_webex , room_webex= :room_webex , text_send_webex= :text_send_webex , "
. " to_send_message= :to_send_message , cc_send_message= :cc_send_message , flag_send_mail= :flag_send_mail , id_category= :id_category , data_modifica = now(), modificato_da = :modificato_da "
. " where id = :id;";
$statement = $connection->prepare($q);
$statement->bindValue('name', $name);
$statement->bindValue('description', $description);
$statement->bindValue('flag_send_webex', $flag_send_webex);
$statement->bindValue('room_webex', $room_webex);
$statement->bindValue('text_send_webex', $text_send_webex);
$statement->bindValue('to_send_message', $to_send_message);
$statement->bindValue('cc_send_message', $cc_send_message);
$statement->bindValue('flag_send_mail', $flag_send_mail);
$statement->bindValue('id_category', $wflCategory);
$statement->bindValue('modificato_da', $userId);
$statement->bindValue('id', $id);
$statement->execute();
/**************** INIZIO GESTIONE UPLOAD WORKFLOW FILES *********************/
$target_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $cid . '/' . $request->getBasePath();
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
$delete_parameters = "delete from chklst_template_workflow_files where chklst_id = '$cid'";
$statement = $connection->prepare($delete_parameters);
$statement->execute();
$em->flush();
$wf_description = "";
$param_name = "";
$param_file_name = "";
$param_file_path = "";
$param_file_type = "";
$em->flush();
/**************** FINE GESTIONE UPLAOD WORKFLOW FILES *********************/
/********************** INIZIO GESTIONE PARAMETRI **********************/
$delete_parameters = "delete from chklst_template_parameters where chklst_id = :cid ";
$statement = $connection->prepare($delete_parameters);
$statement->bindValue('cid', $cid);
$statement->execute();
$list_param_type = preg_grep("/^form_lista_param_type_.*/", array_keys($_POST));
$functions = new Functions($this, $logger, $em);
foreach ($list_param_type as $l) {
$exp = explode("_", $l);
$i = end($exp);
if (isset($_POST["form_lista_param_type_" . $i]) && isset($_POST["form_lista_param_name_" . $i]) && isset($_POST["form_lista_param_value_" . $i])) {
$param_type = $_POST["form_lista_param_type_" . $i];
$param_name = $_POST["form_lista_param_name_" . $i];
$param_value = $_POST["form_lista_param_value_" . $i];
// Gestione parametro di tipo Encrypt
if($param_type === "encrypt"){
$hidden_encrypt_param = $_POST["hidden_checklist_param_value_" . $i];
if($hidden_encrypt_param === "1"){
$param_value = $functions->encrypt($param_value);
}
}
// Se il nome parametro corrisponde al pattern proibito, salto l'inserimento
if (preg_match($forbiddenPattern, $param_name)) {
continue;
}
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type)
values (:cid, :param_name, :param_value, :param_type)
ON CONFLICT(chklst_id, param_name) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type ";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_value', $param_value);
$statement->bindValue('param_type', $param_type);
$statement->execute();
}
}
/******************* FINE GESTIONE PARAMETRI ***********************/
/**************** INIZIO GESTIONE UPLOAD FILES *********************/
$target_dir = $params->get('kernel.project_dir') . '/uploads/' . $cid . '/' . $request->getBasePath();
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
//$logger->debug("[updateChktmplAction] creta directory ".$target_dir);
$sql_old_parameters_files = "select param_file_name from chklst_template_parameters_files where chklst_id = '$cid'";
$statement = $connection->prepare($sql_old_parameters_files);
$statement->execute();
$old_parameters_files = $statement->fetchAll();
//$logger->debug("[updateChktmplAction] Old Parmeters: " . json_encode($old_parameters_files));
$delete_parameters = "delete from chklst_template_parameters_files where chklst_id = '$cid'";
$statement = $connection->prepare($delete_parameters);
$statement->execute();
$delete_parameters = "delete from chklst_template_parameters where chklst_id = '$cid' and flag_file='Y'";
$statement = $connection->prepare($delete_parameters);
$statement->execute();
$em->flush();
$list_fileToUpload = preg_grep("/^form_lista_files_param_name_*/", array_keys($_POST));
foreach ($list_fileToUpload as $l) {
$exp = explode("_", $l);
$i = end($exp);
if (isset($_FILES["fileToUpload_" . $i]) && isset($_POST["form_lista_files_param_name_" . $i])) {
//$logger->debug("[updateChktmplAction] STO DENTRO ISSET FILES");
$target_file = $target_dir . basename($_FILES["fileToUpload_" . $i]["name"]);
if (move_uploaded_file($_FILES["fileToUpload_" . $i]["tmp_name"], $target_file)) {
//$logger->debug("[updateChktmplAction] The file " . basename(htmlEntities($_FILES["fileToUpload_" . $i]["name"], ENT_QUOTES)) . " has been uploaded.");
$param_name = $this->custom_addslashes($_POST["form_lista_files_param_name_" . $i], $params);
if (isset($_FILES["fileToUpload_" . $i]["name"])) {
$param_file_name = basename($_FILES["fileToUpload_" . $i]["name"]);
$param_file_type = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
} else {
$param_file_name = $_POST["form_lista_files_param_file_name_" . $i];
$param_file_type = strtolower(pathinfo($param_file_name, PATHINFO_EXTENSION));
}
$param_file_path = $target_dir;
// Se il nome parametro corrisponde al pattern proibito, salto l'inserimento
if (preg_match($forbiddenPattern, $param_name)) {
continue;
}
$sql = "insert into chklst_template_parameters_files (chklst_id, param_name, param_file_name, param_file_path, param_file_type)
values (:cid, :param_name, :param_file_name, :param_file_path, :param_file_type)
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_file_name = :param_file_name,
param_file_path = :param_file_path,
param_file_type = :param_file_type";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_file_name', $param_file_name);
$statement->bindValue('param_file_path', $param_file_path);
$statement->bindValue('param_file_type', $param_file_type);
$statement->execute();
/***** Gestione file come parametri checklist *****/
// Parmetro param_name.filename (di tipo file) per il nome file da sostituire senza apici
$param_name_filename = $param_name . '.filename';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_filename, :param_file_name, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_name,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_filename', $param_name_filename);
$statement->bindValue('param_file_name', $param_file_name);
$statement->execute();
// Parmetro param_name.file (di tipo file) per il file con path da sostituire senza apici
$param_name_file = $param_name . '.file';
$param_file_value = $param_file_path . $param_file_name;
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_file, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_file', $param_name_file);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
// Parmetro param_name.content (di tipo file) per la lettura del contenuto del file
$param_name_content = $param_name . '.content';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_content, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_content', $param_name_content);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
} elseif (isset($_POST["form_lista_files_param_file_name_" . $i])) {
//$logger->debug("[updateChktmplAction] STO DENTRO ISSET FILE NAME");
$param_name = $this->custom_addslashes(htmlEntities($_POST["form_lista_files_param_name_" . $i], ENT_QUOTES), $params);
$param_file_name = $this->custom_addslashes(htmlEntities($_POST["form_lista_files_param_file_name_" . $i], ENT_QUOTES), $params);
$param_file_type = strtolower(pathinfo($param_file_name, PATHINFO_EXTENSION));
$param_file_path = $target_dir;
// Se il nome parametro corrisponde al pattern proibito, salto l'inserimento
if (preg_match($forbiddenPattern, $param_name)) {
continue;
}
$sql = "insert into chklst_template_parameters_files (chklst_id, param_name, param_file_name, param_file_path, param_file_type)
values (:cid, :param_name, :param_file_name, :param_file_path, :param_file_type)
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_name = :param_name,
param_file_name = :param_file_name,
param_file_path = :param_file_path,
param_file_type = :param_file_type";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_file_name', $param_file_name);
$statement->bindValue('param_file_path', $param_file_path);
$statement->bindValue('param_file_type', $param_file_type);
$statement->execute();
/***** Gestione file come parametri checklist *****/
// Parmetro param_name.filename (di tipo file) per il nome file da sostituire senza apici
$param_name_filename = $param_name . '.filename';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_filename, :param_file_name, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_name,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_filename', $param_name_filename);
$statement->bindValue('param_file_name', $param_file_name);
$statement->execute();
// Parmetro param_name.file (di tipo file) per il file con path da sostituire senza apici
$param_name_file = $param_name . '.file';
$param_file_value = $param_file_path . $param_file_name;
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_file, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_file', $param_name_file);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
// Parmetro param_name.content (di tipo file) per la lettura del contenuto del file
$param_name_content = $param_name . '.content';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_content, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_content', $param_name_content);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
}
}
}
$em->flush();
$sql_new_parameters_files = "select param_file_name from chklst_template_parameters_files where chklst_id = '$cid'";
$statement = $connection->prepare($sql_new_parameters_files);
$statement->execute();
$new_parameters_files = $statement->fetchAll();
//$logger->debug("[updateChktmplAction] New Parmeters: " . json_encode($new_parameters_files));
foreach ($old_parameters_files as $old_parameters_file) {
$cnt = 0;
foreach ($new_parameters_files as $new_parameters_file) {
if ($old_parameters_file['param_file_name'] == $new_parameters_file['param_file_name']) {
//$logger->debug("[updateChktmplAction] File " . $old_parameters_file['param_file_name'] . " presente nei nuovi parametri.");
$cnt += 1;
}
}
if ($cnt == 0) {
//$logger->debug("[updateChktmplAction] File " . $old_parameters_file['param_file_name'] . " NON presente nei nuovi parametri.");
if (file_exists($target_dir . $old_parameters_file['param_file_name']) && !is_dir($target_dir . $old_parameters_file['param_file_name'])) {
if (!unlink($target_dir . $old_parameters_file['param_file_name'])) {
//$logger->debug("[updateChktmplAction] Non Ã¨ stato possibile eliminare il file " . $old_parameters_file['param_file_name'] . ".");
} else {
$sql = "delete from chklst_template_parameters_files where chklst_id=:$cid and param_file_name= :param_file_name";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_file_name', $old_parameters_file['param_file_name']);
$statement->execute();
//$logger->debug("[updateChktmplAction] File " . $old_parameters_file['param_file_name'] . " eliminato correttamente.");
}
}
}
}
/**************** FINE GESTIONE UPLAOD FILES *********************/
}
$wflParams = $this->getWorkflowParameters($IdTemplate);
/*return $this->redirect($this->generateUrl('checklist_flowchart', array(
'idTemplate' => $IdTemplate,
'csrftoken' => $tokenManager->getToken('checklist_flowchart')->getValue()
)));*/
return new JsonResponse([
'success' => true,
'wflParams' => $wflParams,
]);
}
#Action che crea la chklst per il designer
public function insertChktmplFlowAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params, CsrfTokenManagerInterface $tokenManager)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('insert_chklst', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$user = $this->getUser();
$name =trim($_POST["form_nome"]);
$description = trim($_POST["form_descrizione"]);
$hidden = htmlEntities($_POST["form_att"], ENT_QUOTES);
// check nome del workflow è vuoto, assegna un nome di default
if($name == ""){
$name = "workflow_" . date("Ymd_His");
}
$category = 1;
if (isset($_POST["form_category"])) {
$category = htmlEntities($_POST["form_category"], ENT_QUOTES);
}
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$userId = $user->getId();
$q = "insert into chklst_template (name, description, hidden, flag_send_webex, room_webex, text_send_webex, to_send_message, cc_send_message, flag_send_mail, id_category, data_creazione, creato_da, modificato_da) "
. "values (:name, :description, :hidden, 'N', '', 'Riepilogo esecuzione Checklist', '', '','N', :category, now(), :userId, :userId2 ) ON CONFLICT (name) DO NOTHING;";
$statement = $connection->prepare($q);
$statement->bindValue('name', $name);
$statement->bindValue('description', $description);
$statement->bindValue('hidden', $hidden);
$statement->bindValue('category', $category);
$statement->bindValue('userId', $userId);
$statement->bindValue('userId2', $userId);
$statement->execute();
$q = "select id from chklst_template "
. " where name = :name ";
$statement = $connection->prepare($q);
$statement->bindValue('name', $name);
$statement->execute();
$res = $statement->fetchAll()[0];
$cid = $res['id'];
// INIZIO GESTIONE RUOLI CHECKLIST
$sql = "select * from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$roles = $stmt->fetchAll();
$lista_ruoli = preg_grep("/^ins_chk_2_*/", array_keys($_POST));
if (count($lista_ruoli) > 0) {
$sql = "delete from chklst_template_role where id_template = :cid";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->execute();
foreach ($roles as $role) {
$id_role = $role['id_roles'];
$logger->debug("ruolo: " . $id_role);
foreach ($lista_ruoli as $l) {
if ($role['description_roles'] == $_POST[$l]) {
$logger->debug("sto dentro isset " . $id_role);
$sql = "insert into chklst_template_role (id_template,id_roles) values ($cid, $id_role)";
$statement = $connection->prepare($sql);
$statement->execute();
}
}
}
}
// FINE GESTIONE RUOLI CHECKLIST
/**************** INIZIO INTEGRAZIONE DESIGNER *********************/
$sqld = "insert into chklst_jflow (chklst_template, json_flow) values($cid,'')";
$statement = $connection->prepare($sqld);
$statement->execute();
/**************** FINE INTEGRAZIONE DESIGNER *********************/
/**************** INIZIO GESTIONE UPLOAD WORKFLOW FILES *********************/
$target_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $cid . '/' . $request->getBasePath();
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
$wf_description = "";
$param_name = "";
$param_file_name = "";
$param_file_path = "";
$param_file_type = "";
$list_wf_files_param_name = preg_grep("/^form_lista_wf_files_param_name_0_*/", array_keys($_POST));
foreach ($list_wf_files_param_name as $l) {
$exp = explode("_", $l);
$i = end($exp);
if (isset($_POST["form_wf_text_0"])) {
$wf_description = $this->custom_addslashes(htmlEntities($_POST["form_wf_text_0"], ENT_QUOTES), $params);
}
if (isset($_FILES["wf_fileToUpload" . '_0_' . $i]) && isset($_POST["form_lista_wf_files_param_name" . '_0_' . $i])) {
//$logger->debug("[updateChktmplAction] STO DENTRO ISSET wf_FILES");
$target_file = $target_dir . basename(htmlEntities($_FILES["wf_fileToUpload" . '_0_' . $i]["name"], ENT_QUOTES));
if (move_uploaded_file(htmlEntities($_FILES["wf_fileToUpload" . '_0_' . $i]["tmp_name"], ENT_QUOTES), $target_file)) {
//$logger->debug("[updateChktmplAction] The wf_file " . basename(htmlEntities($_FILES["wf_fileToUpload" . '_0_' . $i]["name"], ENT_QUOTES)) . " has been uploaded.");
$param_name = $this->custom_addslashes(htmlEntities($_POST["form_lista_wf_files_param_name" . '_0_' . $i], ENT_QUOTES), $params);
if (isset($_FILES["wf_fileToUpload" . '_0_' . $i]["name"])) {
$param_file_name = basename(htmlEntities($_FILES["wf_fileToUpload" . '_0_' . $i]["name"], ENT_QUOTES));
$param_file_type = pathinfo($param_file_name, PATHINFO_EXTENSION);
} else {
$param_file_name = htmlEntities($_POST["form_lista_wf_files_param_file_name" . '_0_' . $i], ENT_QUOTES);
$param_file_type = pathinfo($param_file_name, PATHINFO_EXTENSION);
}
$param_file_path = $target_dir;
}
}
$sql = "insert into chklst_template_workflow_files (chklst_id, param_name, param_file_name, param_file_path, param_file_type)
values (:cid, :param_name, :param_file_name, :param_file_path, :param_file_type)
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_file_name = :param_file_name,
param_file_path = :param_file_path,
param_file_type = :param_file_type";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_file_name', $param_file_name);
$statement->bindValue('param_file_path', $param_file_path);
$statement->bindValue('param_file_type', $param_file_type);
$statement->execute();
}
$em->flush();
/**************** FINE GESTIONE UPLAOD WORKFLOW FILES *********************/
/********************** INIZIO GESTIONE PARAMETRI **********************/
$list_param_type_0 = preg_grep("/^form_lista_param_type_0_*/", array_keys($_POST));
foreach ($list_param_type_0 as $l) {
$exp = explode("_", $l);
$i = end($exp);
if (isset($_POST["form_lista_param_type" . '_0_' . $i]) && isset($_POST["form_lista_param_name" . '_0_' . $i]) && isset($_POST["form_lista_param_value" . '_0_' . $i])) {
$param_type = $_POST["form_lista_param_type" . '_0_' . $i];
$param_name = $this->custom_addslashes(htmlEntities($_POST["form_lista_param_name" . '_0_' . $i], ENT_QUOTES), $params);
$param_value = $this->custom_addslashes(htmlEntities($_POST["form_lista_param_value" . '_0_' . $i], ENT_QUOTES), $params);
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type)
values (:cid, :param_name, :param_value, :param_type)
ON CONFLICT(chklst_id, param_name) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type ";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_value', $param_value);
$statement->bindValue('param_type', $param_type);
$statement->execute();
}
}
/******************* FINE GESTIONE PARAMETRI ***********************/
/**************** INIZIO GESTIONE UPLOAD FILES *********************/
$target_dir = $params->get('kernel.project_dir') . '/uploads/' . $cid . '/' . $request->getBasePath();
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
$list_files_param_name = preg_grep("/^form_lista_files_param_name_0_*/", array_keys($_POST));
foreach ($list_files_param_name as $l) {
$exp = explode("_", $l);
$id = end($exp);
if (isset($_FILES["fileToUpload" . '_0_' . $id]) && isset($_POST["form_lista_files_param_name" . '_0_' . $id])) {
//$logger->debug("[updateChktmplAction] STO DENTRO ISSET FILES");
$target_file = $target_dir . basename(htmlEntities($_FILES["fileToUpload" . '_0_' . $id]["name"], ENT_QUOTES));
if (move_uploaded_file(htmlEntities($_FILES["fileToUpload" . '_0_' . $id]["tmp_name"], ENT_QUOTES), $target_file)) {
//$logger->debug("[updateChktmplAction] The file " . basename(htmlEntities($_FILES["fileToUpload" . '_0_' . $id]["name"], ENT_QUOTES)) . " has been uploaded.");
$param_name = $_POST["form_lista_files_param_name" . '_0_' . $id];
if (isset($_FILES["fileToUpload" . '_0_' . $id]["name"])) {
$param_file_name = basename(htmlEntities($_FILES["fileToUpload" . '_0_' . $id]["name"], ENT_QUOTES));
$param_file_type = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));
} else {
$param_file_name = $this->custom_addslashes(htmlEntities($_POST["form_lista_files_param_file_name" . '_0_' . $id], ENT_QUOTES), $params);
$param_file_type = strtolower(pathinfo($param_file_name, PATHINFO_EXTENSION));
}
$param_file_path = $target_dir;
$sql = "insert into chklst_template_parameters_files (chklst_id, param_name, param_file_name, param_file_path, param_file_type)
values (:cid, :param_name, :param_file_name, :param_file_path, :param_file_type)
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_file_name = :param_file_name,
param_file_path = :param_file_path,
param_file_type = :param_file_type";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_file_name', $param_file_name);
$statement->bindValue('param_file_path', $param_file_path);
$statement->bindValue('param_file_type', $param_file_type);
$statement->execute();
/***** Gestione file come parametri checklist *****/
// Parmetro param_name (di tipo stringa) per il nome file da sostituire con gli apici
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name, :param_file_name, 'string', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_name,
param_type = 'string',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name', $param_name);
$statement->bindValue('param_file_name', $param_file_name);
$statement->execute();
// Parmetro param_name.filename (di tipo file) per il nome file da sostituire senza apici
$param_name_filename = $param_name . '.filename';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_filename, :param_file_name, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_name,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_filename', $param_name_filename);
$statement->bindValue('param_file_name', $param_file_name);
$statement->execute();
// Parmetro param_name.file (di tipo file) per il file con path da sostituire senza apici
$param_name_file = $param_name . '.file';
$param_file_value = $param_file_path . $param_file_name;
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_file, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_file', $param_name_file);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
// Parmetro param_name.content (di tipo file) per la lettura del contenuto del file
$param_name_content = $param_name . '.content';
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values (:cid, :param_name_content, :param_file_value, 'file', 'Y')
ON CONFLICT (chklst_id, param_name) DO UPDATE SET
param_value = :param_file_value,
param_type = 'file',
flag_file = 'Y'";
$statement = $connection->prepare($sql);
$statement->bindValue('cid', $cid);
$statement->bindValue('param_name_content', $param_name_content);
$statement->bindValue('param_file_value', $param_file_value);
$statement->execute();
}
}
}
$em->flush();
/**************** FINE GESTIONE UPLAOD FILES *********************/
if ($request->request->get('isApi')) {
return $this->json(["wflId" => $cid]);
}
return $this->redirect($this->generateUrl('checklist_flowchart', array('idTemplate' => $cid, 'csrftoken' => $tokenManager->getToken('checklist_flowchart')->getValue())));
}
public function saveFlowJsonAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params, TaskRepository $taskRepository)
{
session_write_close();
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('saveFlowchartJson', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$em->getConnection()->beginTransaction(); // suspend auto-commit
$res_json = new JsonResponse;
$task_validate = false;
$json_flow = $request->request->get('json_flow');
$chklst_id = $request->request->get('chklst_id');
$flow_image = $request->files->get('flow_image');
$relativeFilename = "";
if ($flow_image != null) {
$relativeFilename = "img/flowchart/preview/preview_workflow_id_$chklst_id.png";
$targetFilename = "preview_workflow_id_$chklst_id.png";
$targetDirectory = $params->get('projectDir') . "/public/img/flowchart/preview/";
$flow_image->move($targetDirectory, $targetFilename);
}
//$json_flow = json_encode($datijson);
try {
$user = $this->getUser();
$userId = $user->getId();
$sql = "update chklst_template set data_modifica = now(), modificato_da = :userId where id = :chklst_id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('chklst_id', $chklst_id);
$stmt->bindValue('userId', $userId);
$stmt->execute();
$datijson = json_decode($json_flow, true);
//step 1 validità
$graphLinksModel = new GraphLinksModel($datijson, $logger, $em, $params, $taskRepository);
//$tasksWithNoLinks = $graphLinksModel->findTasksWithNoLinks();
$startPoints = $graphLinksModel->findMultipleStart();
$taskOrder = $graphLinksModel->findPaths();
$currentModel = $graphLinksModel->getModel();
$taskValidate = "Y";
$canRunAlone = true;
if (count($currentModel) == 3) {
$taskValidate = "N";
}
if (count($currentModel) == 4) {
$anagId = str_replace("]", "", explode("_", array_keys($currentModel)[3])[2]);
$sql = "select can_run_alone from task_anagrafica where id = :anagId";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('anagId', $anagId);
$stmt->execute();
$returnCanRunAlone = $stmt->fetchAll()[0]["can_run_alone"];
if ($returnCanRunAlone == 0) {
$canRunAlone = false;
}
}
if (!$canRunAlone || $startPoints > 1) {
$taskValidate = "N";
}
$sql = "update chklst_jflow set json_flow = :json_flow, flow_image = :relativeFilename ,
task_jorder = :task_jorder, task_validate = :task_validate
where chklst_template = :chklst_id";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('json_flow', $json_flow);
$stmt->bindValue('relativeFilename', $relativeFilename);
$stmt->bindValue('task_jorder', $taskOrder);
$stmt->bindValue('task_validate', $taskValidate);
$stmt->bindValue('chklst_id', $chklst_id);
$stmt->execute();
//se ci sono piu nodi che non hanno ingressi dal top, allora è invalido, perché ci sono più partenze
$em->getConnection()->commit();
//return 0;
$res_json->setData(array('task_validate' => $taskValidate));
return $res_json;
} catch (\Exception $e) {
$em->getConnection()->rollBack();
throw $e;
}
}
public function loadFlowJsonAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
session_write_close();
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('load_designer', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
//$logger = $this->getLogger();
$db_driver = $params->get('db_driver');
$res_json = new JsonResponse;
$em = $this->getDoctrine()->getManager();
$chklst_id = $request->request->get('chklst_id');
#dall id della checklist vedo se sulla chklst_jflow c e il flow altrimenti lo calcolo
#recuperando i task, il loro ordine e compongo il json
$sql = "select json_flow,task_validate from chklst_jflow where chklst_template = :cid ";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('cid', $chklst_id);
$stmt->execute();
$jsons = $stmt->fetchAll();
foreach ($jsons as $json) {
$json_flow[] = $json['json_flow'];
$task_validate[] = $json['task_validate'];
}
//per verificare i nodi terminali
$IdsFromArray = array();
$hiddenTasks = array();
if (strpos(json_encode($task_validate), "Y")) {
$logger->debug("task_validate" . json_encode($task_validate));
$task_validate_v = true;
} else {
$logger->debug("task_validate FALSE");
$task_validate_v = false;
}
$res_json->setData(array('json_flow' => $json_flow[0], 'task_validate' => $task_validate_v));
return $res_json;
}
/*** FINE AGGIUNTA METODI DESIGNER **/
public function dashboardAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
$tipologia = "tutti";
if (isset($_POST["periodo"])) {
$periodo = $_POST["periodo"];
} else {
$periodo = '7 day';
}
$dateSchedule = date_create_from_format('d/m/Y H:i', '31/12/2999 23:59');
$logger->debug("[dashboardAction] Data Schedulata " . json_encode($dateSchedule));
$logger->debug('[dashboardAction] Periodo: ' . $periodo);
$chktmpl2 = $em->getRepository('App\Entity\ChklstTemplate')
->findByHidden(0);
$now = new \DateTime();
$now->modify("+1 day");
$filtroPeriodo = new \DateTime();
$filtroPeriodo = $filtroPeriodo->modify('-' . $periodo);
//$logger->debug('filtroPeriodo: '. $filtroPeriodo);
$logger->debug('[dashboardAction] Filtro Periodo: ' . $periodo);
$logger->debug('*************** QUERY IdPerido ********************');
$idPeriodo = $em->createQueryBuilder()
->select('IDENTITY(c.fromTemplate) AS fromTemplate')
->from('App:Chklst', 'c')
->where('c.runStart between :filtroPeriodo and :now')
->setParameter('filtroPeriodo', $filtroPeriodo->format('Y-m-d'))
->setParameter('now', $now->format('Y-m-d'))
->distinct()
->getQuery()
->getResult();
$logger->debug('*************** FINE QUERY IdPerido ********************');
//$logger->debug('idPeriodo: '. $idPeriodo);
$user = $this->getUser();
$userId = $user->getId();
$UserRole = $user->getUserRole();
$logger->debug("[dashboardAction] UserRole " . $UserRole);
$logger->debug('Tiro Fuori i checklist id filtrati per utente');
$sql = "SELECT DISTINCT ct.id
FROM chklst_template ct";
//if ($UserRole <> "ROLE_SUPER_ADMIN") {
$sql .= " join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
";
//}
$sql .= " join chklsttmpl2task cw on ct.id = cw.chklsttmpl_id";
$logger->debug('Query Cehcklist id ' . $sql);
$sql = $em->getConnection()->prepare($sql);
$sql->execute();
$cklstId = $sql->fetchAll();
$logger->debug('*************** QUERY chktmpl ********************');
$chktmpl = $em
->getRepository('App:ChklstTemplate')
->createQueryBuilder('t')
->where('t.id in (:idPeriodo)')
->andWhere('t.id in (:cklstId)')
->andWhere('t.hidden = 0')
->setParameter('idPeriodo', $idPeriodo)
->setParameter('cklstId', $cklstId)
->getQuery()
->getResult();
$logger->debug("[dashboardAction] Prima Query " . $UserRole);
$sql = " select giorno, sum(cnt) cnt, sum(success) success, sum(error) error, sum(running) running, sum(stopped) stopped, sum(not_run) not_run
from (
select * from (select d.gg giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when c.run_start is not null and lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM (select to_char(i::date, 'yyyy-mm-dd') gg from generate_series(current_date - 90, current_date, '1 day'::interval) i) d
left join chklst c
on( d.gg = to_char(c.run_start, 'yyyy-mm-dd'))
left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < current_date
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on (c.id=c_max.id)
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on (cr.id_roles = ur.id_roles)
where d.gg between to_char(current_date - '$periodo'::interval , 'yyyy-mm-dd') and to_char(current_date, 'yyyy-mm-dd')
and (c.run_end < to_date('2999-12-31', 'yyyy-mm-dd') or c.run_end is null)
group by d.gg
order by giorno desc) a ) b
group by giorno
order by giorno"; //: "select giorno, sum(cnt) cnt, sum(success) success, sum(error) error, sum(running) running, sum(stopped) stopped, sum(not_run) not_run from( " . $webAppRunPerDay . "" . (($tipologia == "webapp") ? ") b group by giorno order by giorno asc" : "") . ""
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_run = $stmt->fetchAll();
$sql = "select sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop
from ( select * from (select to_char(c.run_start, 'yyyy-mm-dd') giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM chklst c left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < current_date
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on c.id=c_max.id
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template and ct.hidden=0
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
where to_char(c.run_start, 'yyyy-mm-dd') between to_char(current_date- '$periodo'::interval , 'yyyy-mm-dd')
and to_char(current_date, 'yyyy-mm-dd')
and c.run_end < to_date('2999-12-31', 'yyyy-mm-dd')
group by to_char(c.run_start, 'yyyy-mm-dd')
order by giorno desc) a) b"// : "select sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop from ( " . $webAppRunPerDay . "" . (($tipologia == "webapp") ? ") b " : "") . ""
;
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_run_t = $stmt->fetchAll()[0];
// $chklst_run_tot[] = array('label' =>'Totali', 'value' => $chklst_run_t['tot']);
$chklst_run_tot[] = array('label' => 'Completed', 'value' => $chklst_run_t['ok']);
$chklst_run_tot[] = array('label' => 'KO', 'value' => $chklst_run_t['ko']);
$chklst_run_tot[] = array('label' => 'Stopped', 'value' => $chklst_run_t['stop']);
// Query POSTGRESQL
$sql = //in_array($tipologia, ["tutti", "workflow"]) ? "" . ($tipologia == "tutti" ? $classificaWebApp : "") .
"select name, from_template_id, sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop
from (select * from
(select ct.name, c.from_template_id , to_char(c.run_start, 'yyyy-mm-dd') giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM chklst c
left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < current_date
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on c.id=c_max.id
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
where to_char(c.run_start, 'yyyy-mm-dd') between to_char(current_date - '$periodo'::interval , 'yyyy-mm-dd')
and to_char(current_date, 'yyyy-mm-dd')
and c.run_end < to_date('2999-12-31', 'yyyy-mm-dd')
group by ct.name, from_template_id, to_char(c.run_start, 'yyyy-mm-dd')
order by giorno desc) a) b
group by name, from_template_id
order by tot desc"// : $classificaWebApp
;
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_val = $stmt->fetchAll();
// Query POSTGRESQL
$sql = //in_array($tipologia, ["tutti", "workflow"]) ? "" . ($tipologia == "tutti" ? $classificaWebApp : "") .
"select name, from_template_id, sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop
from (select * from (select ct.name, c.from_template_id , to_char(c.run_start, 'yyyy-mm-dd') giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM chklst c
left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < sysdate()
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on c.id=c_max.id
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
where to_char(c.run_start, 'yyyy-mm-dd') between to_char(current_date - '$periodo'::interval , 'yyyy-mm-dd')
and to_char(current_date, 'yyyy-mm-dd')
and c.run_end < to_date('2999-12-31', 'yyyy-mm-dd')
group by ct.name, from_template_id,to_char(c.run_start, 'yyyy-mm-dd')
order by giorno desc) a) b
group by name, from_template_id
order by ok desc" // : $classificaWebApp
;
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_ok = $stmt->fetchAll();
$sql = //in_array($tipologia, ["tutti", "workflow"]) ? "" . ($tipologia == "tutti" ? $classificaWebApp : "") .
"select name, from_template_id, sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop
from (select * from (select ct.name, c.from_template_id , to_char(c.run_start, 'yyyy-mm-dd') giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM chklst c
left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < sysdate()
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on c.id=c_max.id
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
where to_char(c.run_start, 'yyyy-mm-dd') between to_char(current_date - '$periodo'::interval , 'yyyy-mm-dd')
and to_char(current_date, 'yyyy-mm-dd')
and c.run_end < to_date('2999-12-31', 'yyyy-mm-dd')
group by ct.name, from_template_id,to_char(c.run_start, 'yyyy-mm-dd')
order by giorno desc) a) b
group by name, from_template_id
order by ko desc" //: $classificaWebApp
;
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_ko = $stmt->fetchAll();
$sql = //in_array($tipologia, ["tutti", "workflow"]) ? "" . ($tipologia == "tutti" ? $classificaWebApp : "") .
"select name, from_template_id, sum(cnt) tot, sum(success) ok , sum(error) ko, sum(stopped) stop
from (select * from (select ct.name, c.from_template_id , to_char(c.run_start, 'yyyy-mm-dd') giorno, count(ct.name) cnt,
sum(case when lower(run_result)='success' then 1 else 0 end) success,
sum(case when lower(run_result)='failed' then 1 else 0 end) error,
sum(case when lower(run_result) is null then 1 else 0 end) running,
sum(case when lower(run_result)='stopped' then 1 else 0 end) stopped,
sum(case when c.name is null then 1 else 0 end) not_run
FROM chklst c
left join (select max(id) id, name, to_char(run_start, 'yyyy-mm-dd')
FROM chklst
where run_start < sysdate()
group by name, to_char(run_start, 'yyyy-mm-dd')) c_max
on c.id=c_max.id
right join chklst_template ct on c.from_template_id = ct.id and ct.hidden=0
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles
where to_char(c.run_start, 'yyyy-mm-dd') between to_char(current_date - '$periodo'::interval , 'yyyy-mm-dd')
and to_char(current_date, 'yyyy-mm-dd')
and c.run_end < to_date('2999-12-31', 'yyyy-mm-dd')
group by ct.name, from_template_id,to_char(c.run_start, 'yyyy-mm-dd')
order by giorno desc) a) b
group by name, from_template_id
order by stop desc" //: $classificaWebApp
;
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$chklst_int = $stmt->fetchAll();
$checklist = [];
$checklistArr = [];
foreach ($chktmpl as $c) {
if ($c->getId() < $params->get('webAppFromIndex') and (in_array($tipologia, ["tutti", "workflow"]))) {
$logger->debug('*************** QUERY checkrun ********************');
$checkrun = $em->createQueryBuilder()
->select('c')
->from('App:Chklst', 'c')
->where('c.fromTemplate = :template')
->andWhere('c.runEnd is null')
->setParameter('template', $c)
->getQuery()
->getResult();
$logger->debug('*************** QUERY history ********************');
$history = $em->createQueryBuilder()
->select('c')
->from('App:Chklst', 'c')
->where('c.fromTemplate = :template')
->andWhere('c.runStart < :today')
->andWhere('c.runEnd < :dateSchedule')
->setParameter('today', new \DateTime())
->setParameter('template', $c)
->setParameter('dateSchedule', $dateSchedule)
->orderBy('c.id', 'DESC')
->setMaxResults('1')
->getQuery()
->getResult();
$logger->debug('*************** QUERY historyArray ********************');
$historyArray = $em->createQueryBuilder()
->select('c')
->from('App:Chklst', 'c')
->where('c.fromTemplate = :template')
->andWhere('c.runStart < :today')
->andWhere('c.runEnd < :dateSchedule')
->setParameter('today', new \DateTime())
->setParameter('template', $c)
->setParameter('dateSchedule', $dateSchedule)
->orderBy('c.id', 'DESC')
->setMaxResults('1')
->getQuery()
->getArrayResult();
$logger->debug('*************** QUERY ctask_history ********************');
$ctask_history = [];
foreach ($history as $h) {
$ctask_history = $em->createQueryBuilder()
->select('c')
->from('App:ChklstTask', 'c')
->where('c.chklst_id = :chklst_id')
->setParameter('chklst_id', $h->getID())
->orderBy('c.taskOrder', 'DESC')
// ->setMaxResults('1')
->getQuery()
->getResult();
}
$status = 'idle';
if (!empty($checkrun)) {
$logger->debug("[dashboardAction] Valore Checkrun " . json_encode($checkrun));
if ($checkrun[0]->getRunStart() === null) {
$status = 'queued';
} else {
$status = 'running';
}
$logger->debug("[dashboardAction] Status " . $status);
$sql = "select count(*) tot,
sum(case when nullif(chk.run_result,'Queued') = 'Success' then 1 else 0 end ) as ok,
sum(case when nullif(chk.run_result,'Queued') = 'Failed' then 1 else 0 end ) as ko,
sum(case when chk.run_start is not null and chk.run_end is null then 1 else 0 end ) as running,
sum(case when nullif(chk.run_result,'Queued') = 'Queued' and chk.run_start is null then 1 else 0 end) as queue
from chklst_task chkt inner join chklst chk on (chkt.chklst_id = chk.id)";
if ($UserRole <> "ROLE_SUPER_ADMIN") {
$sql .= "inner join chklst_template ct on (chk.from_template_id = ct.id)
join chklst_template_role cr on ct.id = cr.id_template
join (SELECT ur.id_roles
FROM user_acm u
join user_profile_roles ur on u.id = ur.id_user
WHERE u.id = $userId and ur.id_profile in (1,2)) ur on cr.id_roles = ur.id_roles";
}
$sql .= " where chklst_id = " . $checkrun[0]->getId();
$logger->debug("[dashboardAction] Esecuzione Query POSTGRESQL " . $sql);
} else {
$sql = "select count(t.id) tot, 0 ok, 0 ko, 0 running, count(t.id) queue
from task t left join chklsttmpl2task ct on ct.task_id = t.id
left join chklst_template c on c.id = ct.chklsttmpl_id
where t.hidden='0' and c.id = " . $c->getId();
$logger->debug("[dashboardAction] Valore Checkrun (Else)");
}
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$task_status = $stmt->fetchAll();
if ($status == "running") {
$sql_last_task = "select id, name, description, run_start from chklst_task "
. "where chklst_id = " . $checkrun[0]->getId() . " "
. "and run_start is not null "
. "and run_end is null ";
$logger->debug("[dashboardAction] status operation : " . $status . ' id checklist ' . $checkrun[0]->getId());
} else {
$sql_last_task = "select 0 id, 'n/a' as name, 'nessuna esecuzione in corso' description, null run_start";
}
$stmt = $em->getConnection()->prepare($sql_last_task);
$stmt->execute();
$last_task = $stmt->fetchAll();
$logger->debug("[dashboardAction] Ultimo Task " . json_encode($last_task));
if (count($last_task) == 0) {
$last_task = [""];
$last_server = [];
} else {
$sql_last_server = "select id, hostname, ip_address, run_result from task_server where task_id = " . $last_task[0]['id'];
$stmt = $em->getConnection()->prepare($sql_last_server);
$stmt->execute();
$last_server = $stmt->fetchAll();
}
$parameters = array();
foreach ($history as $h) {
$sql_parameters = "select * from v_chklst_template_parameters where chklst_run_id = " . $h->getId();
$stmt = $em->getConnection()->prepare($sql_parameters);
$stmt->execute();
$chklst_parameters = $stmt->fetchAll();
$parameters[] = array($h->getId() => array("params" => $chklst_parameters));
}
$sql_parameters_all = "select * from chklst_template_parameters where chklst_id = " . $c->getId();
$stmt = $em->getConnection()->prepare($sql_parameters_all);
$stmt->execute();
$parameters_all = $stmt->fetchAll();
if (count($parameters_all) == 0) {
$parameters_all = [];
}
$chklst = array("template" => $c, "status" => $status, "history" => $history, "ctask_history" => $ctask_history, "task_status" => $task_status[0], "last_task" => $last_task[0], "last_server" => $last_server, "parameters" => $parameters, "parameters_all" => $parameters_all);
$logger->debug('checklist chklst:: ' . json_encode($chklst));
$checklist[$c->getName()] = $chklst;
//history array
$chklstArr = array("template" => $c, "status" => $status, "history" => $historyArray, "ctask_history" => $ctask_history, "task_status" => $task_status[0], "last_task" => $last_task[0], "last_server" => $last_server, "parameters" => $parameters, "parameters_all" => $parameters_all);
$logger->debug('checklist json:: ' . json_encode($chklstArr));
if (empty($chklstArr)) {
$logger->debug('Array chklstArr vuoto');
$checklistArr = null;
} else {
$logger->debug('Array chklstArr valorizzato');
$checklistArr[$c->getName()] = $chklstArr;
}
}
}
$sql_webex_rooms = "select id, title from webex_rooms ";
$stmt = $em->getConnection()->prepare($sql_webex_rooms);
$stmt->execute();
$webex_rooms = $stmt->fetchAll();
$colors = ['plum', 'cyan', 'aqua', 'red', 'blue', 'purple', 'green', 'pink', 'orange', 'brown'];
$logger->debug("dati graph: " . json_encode($chklst_run));
// $user = json_encode($this->getUser());
$user = $this->getUser();
$chklst_run_json = json_encode($chklst_run, JSON_NUMERIC_CHECK);
$logger->debug("dati graph json: " . $chklst_run_json);
$chklst_run_tot_json = json_encode($chklst_run_tot, JSON_NUMERIC_CHECK);
$logger->debug("dati donut json: " . $chklst_run_tot_json);
//$logger->debug('checklist json:: '. json_encode($chklstArr));
$chklst_val_json = json_encode($chklst_val, JSON_NUMERIC_CHECK);
$logger->debug("dati val checklist: " . $chklst_val_json);
$logger->debug("dati val checklist: " . json_encode($checklist));
//ordino la checklist in base all'ultimo run start in modo decrescente
usort($checklist, function ($a, $b) {
$aDateTime = "";
if (!empty($a["history"][0]) && !empty($b["history"][0])) {
if (gettype($a["history"][0]->getRunStart()) == "string") {
$aDateTime = $a["history"][0]->getRunStart();
} else {
$aDateTime = $a["history"][0]->getRunStart()->format('Y-m-d H:i:s');
}
$bDateTime = "";
if (gettype($b["history"][0]->getRunStart()) == "string") {
$bDateTime = $b["history"][0]->getRunStart();
} else {
$bDateTime = $b["history"][0]->getRunStart()->format('Y-m-d H:i:s');
}
return strtotime($bDateTime) - strtotime($aDateTime);
} else {
$aDateTime = date_create_from_format('d/m/Y H:i', '31/12/2999 23:59');
$bDateTime = date_create_from_format('d/m/Y H:i', '31/12/2999 23:59');
}
});
$categoriesSql = "select id, nome, descrizione from chklst_template_categories order by id";
$stmt = $em->getConnection()->prepare($categoriesSql);
$stmt->execute();
$categories = $stmt->fetchAll();
if (isset($_POST["from"])) {
// if ($_POST["from"] == 'dashboard'){
$res_json = new JsonResponse();
$res_json->setData(
array(
'chklst_run' => $chklst_run,
'chklst_run_json' => $chklst_run_json,
'chklst_run_tot' => $chklst_run_tot,
'chklst_run_tot_json' => $chklst_run_tot_json,
'chklst_val' => $chklst_val,
'chklst_ok' => $chklst_ok,
'chklst_ko' => $chklst_ko,
'chklst_int' => $chklst_int,
'chklst_val_json' => $chklst_val_json,
'chklst' => $checklistArr,
'categories' => $categories,
'dashboardTile' => $this->get('twig')->render('dashboardTile.html.twig', array('logged_user' => $user, 'chklst_run' => $chklst_run, 'chklst' => $checklist))
)
);
return $res_json;
} else {
return $this->render(
'dashboard.html.twig',
array(
'chklst' => $checklist,
'chktmpl' => $chktmpl,
'colors' => $colors,
'ws_address' => $_SERVER['SERVER_NAME'],
'logged_user' => $user,
'webex_rooms' => $webex_rooms,
'chklst_run' => $chklst_run,
'chklst_run_json' => $chklst_run_json,
'chklst_run_tot' => $chklst_run_tot,
'chklst_run_tot_json' => $chklst_run_tot_json,
'chklst_val' => $chklst_val,
'chklst_ok' => $chklst_ok,
'chklst_ko' => $chklst_ko,
'chklst_int' => $chklst_int,
'categories' => $categories,
)
);
}
}
public function showChecklistAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('checklist_detail', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
session_write_close();
$em = $this->getDoctrine()->getManager();
$chklst_id = $request->request->get('chklst_id');
$which = $request->request->get('which');
$requestType = $request->request->get('requestType');
$id_template = $request->request->get('id_template');
$task = [];
//if ($id_template < $params->get('webAppFromIndex')) {
$chklst = $em->find('App:Chklst', $chklst_id);
$sql = 'SELECT t.id , t.name "name", tanag.id "key_anag", tanag.path_image_task "path_image_task", tanag.tipologia_task, tanag.descrizione_task, t.description, t.cmd_type "cmdType",
t.cmd_text "cmdText", t.match_text "matchText",t.run_end as "runEnd",
t.run_start as "runStart", case when t.run_end is null then null else t.run_result end "runResult", t.run_result_action, t.ko_hint "koHint", (coalesce(t.run_end,now()) - t.run_start)::time as difference,
sum(case when coalesce(s.run_result, ' . "'queued'" . ') = ' . "'Failed'" . ' then 1 else 0 end) "failedServer",
ts.continue_task "Continue_Task",
t.result_api,
t.filename_spool_query, t.spool_on_file, ts.id task_id, t.chklst_id,
t.save_attachments_email, t.slave
FROM chklst_task t left join task_server s on s.task_id = t.id
join task ts on t.task_id=ts.id
join task_anagrafica tanag on ts.cmd_type = tanag.tipo_task
join pre_chklsttmpl2task t2t on ts.id=t2t.task_id
WHERE t.chklst_id = ' . $chklst_id . ' AND t.run_start is not null ';
switch ($which) {
case "OK":
$sql = $sql . "and coalesce(t.run_result, 'Queued') = 'Success' ";
break;
case "KO":
$sql = $sql . "and coalesce(t.run_result, 'Queued') = 'Failed' ";
break;
case "In coda":
$sql = $sql . "and t.run_start is null ";
break;
case "In esecuzione":
$sql = $sql . "and t.run_start is not null and t.run_end is null ";
break;
case "STOPPED":
$sql = $sql . "and t.run_result = 'STOPPED' ";
break;
default:
break;
}
$sql = $sql . "group by t.id, t.name, tanag.id, tanag.path_image_task, tanag.tipologia_task, tanag.descrizione_task, t.description, t.cmd_type, t.cmd_text, t.match_text, t.run_start, t.run_result,
t.run_result_action, t.ko_hint, t.run_end - t.run_start, ts.continue_task,
t.result_api,
t.filename_spool_query, t.spool_on_file, ts.id, t.chklst_id, t2t.task_order, t.slave "
. " order by t.run_start desc";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$detail = $stmt->fetchAll();
foreach ($detail as $d) {
$task[$d['id']]['detail'] = $d;
$sql = "SELECT s.id, s.hostname, s.ip_address, s.run_result, s.result_text "
. "FROM task_server s "
. "WHERE s.task_id = " . $d['id'] . " "
. "order by s.server_order";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$server = $stmt->fetchAll();
$task[$d['id']]['server'] = $server;
//
if ($d["cmdType"] == "checkemail")
if ($d["save_attachments_email"] == "Y") {
$sql = "select public_file_path from chklst_email_attachments
where id_run = :idRun";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue("idRun", $d["id"]);
$stmt->execute();
$zipEmailToDownload = $stmt->fetchAll();
$task[$d["id"]]['detail']["save_attachments_email_zip"] = null;
if (count($zipEmailToDownload) > 0)
$task[$d["id"]]['detail']["save_attachments_email_zip"] = $zipEmailToDownload[0]["public_file_path"];
}
}
// } else {
// $chklst = new Chklst();
// $sql = "
// select name
// from custom.webapp_anag
// where
// id = :id_template
// ";
// $stmt = $em->getConnection()->prepare($sql);
// $stmt->bindValue('id_template', $id_template);
// $stmt->execute();
// $ResultQuery = $stmt->fetchAll()[0];
// $chklst->setName($ResultQuery["name"]);
// $sql = "
// select wt.id,
// wt.\"name\" as name,
// wt.description description,
// id_chklst_run,
// tanag.path_image_task path_image_task,
// run_update \"runStart\",
// run_result \"runResult\",
// '' \"cmdType\",
// '' \"cmdText\",
// '' \"matchText\",
// '' \"failedServer\",
// '' \"koHint\",
// 'N' \"Continue_Task\",
// 'N' flag_send_sms,
// '' result_api,
// '' number_call,
// wt.id \"Id_Task\",
// 'N' flag_send_call_text_to_speech,
// 'N' flag_send_call,
// 'N' flag_send_call_uccx,
// 'N' flag_send_mail,
// 'N' flag_information,
// '' run_result_action,
// '' difference,
// wt.description information
// from custom.webapp_log wl join custom.webapp_task wt on (wl.id_task = wt.id)
// join task_anagrafica tanag on wt.cmd_type = tanag.tipo_task
// where wl.id_template = :id_template and id_chklst_run = :id_chklst_run
// ";
// $stmt = $em->getConnection()->prepare($sql);
// $stmt->bindValue('id_template', $id_template);
// $stmt->bindValue('id_chklst_run', $chklst_id);
// $stmt->execute();
// $ResultQuery = $stmt->fetchAll();
// foreach ($ResultQuery as $row) {
// $task[$row['id']]['detail'] = $row;
// $task[$row['id']]['server'] = null;
// }
// }
if ($requestType == "API") {
$sql = "select run_result from chklst where id = :id_chklst_run";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('id_chklst_run', $chklst_id);
$stmt->execute();
$resultQuery = $stmt->fetchAll();
$continue = 1;
if (!empty($resultQuery) && $resultQuery[0]["run_result"] != "") {
$continue = 0;
}
$listKeyTaskRan = array();
if (!empty($task)) {
foreach ($task as $key => $value) {
// $listKeyTaskRan[] = ["key" => "[TASK {$id_template}_{$value['detail']['task_id']}_{$value['detail']['key_anag']}]",
// "result" => array_map('utf8_encode', [strtolower($value['detail']['runResult'])])[0]];
$listKeyTaskRan[] = [
"key" => "[TASK {$id_template}_{$value['detail']['task_id']}_{$value['detail']['key_anag']}]",
"result" => mb_convert_encoding(strtolower($value['detail']['runResult']), 'UTF-8'),
"isSlave" => $value['detail']['slave']
];
}
}
$listParametersWorklfowRun = "
SELECT chklst_id,
param_name,
param_value,
param_type,
chklst_run_id
FROM v_chklst_template_parameters
WHERE chklst_run_id IN ($chklst_id) and param_name not like '%SYBOT_USER%'
ORDER BY chklst_id DESC, chklst_run_id DESC";
$statement = $em->getConnection()->prepare($listParametersWorklfowRun);
$statement->execute();
$ParameterWorkflowRun = $statement->fetchAll();
$ParameterWorkflow = array();
foreach ($ParameterWorkflowRun as $pr) {
$ParameterWorkflow_tmp = array(
'chklst_id' => $pr['chklst_id'],
'param_name' => $pr['param_name'],
'param_value' => $pr['param_value'],
'param_type' => $pr['param_type'],
'chklst_run_id' => $pr['chklst_run_id']
);
array_push($ParameterWorkflow, $ParameterWorkflow_tmp);
}
$myrender = $this->render(
'checklist-detail.html.twig',
array(
'title' => $chklst->getName(),
'task' => $task,
'ListaParameterWorkflow' => $ParameterWorkflow
)
);
return $this->json(["html" => $myrender->getContent(), "continue" => $continue, "listKeyTaskRan" => $listKeyTaskRan]);
}
$myrender = $this->render('checklist-detail.html.twig',
array('title' => $chklst->getName(),
'task' => $task,
'ListaParameterWorkflow' =>[]));
return $myrender;
}
public function runChecklistAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params, WorkflowAuditLoggerFactory $auditLoggerFactory, $method = null)
{
$csrfToken = isset($_REQUEST['csrftoken']) ? $_REQUEST['csrftoken'] : $request->request->get('csrftoken');
$method= isset($_REQUEST['method']) ? $_REQUEST['method'] : $method;
if( $method != 'internal'){
if (!$this->isCsrfTokenValid('run_save', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
}
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
if (isset($_REQUEST['template_id'])) {
$template_id = htmlEntities($_REQUEST['template_id'], ENT_QUOTES);
} else {
$template_id = htmlEntities($request->request->get('template_id'), ENT_QUOTES);
}
$CallType = $request->request->get('remote_api');
if (isset($_GET["remote_api"])) {
$CallType = $_GET["remote_api"];
}
$template = $em->find('App:ChklstTemplate', $template_id);
// --------------------------------------------------------------------
// Recuper $run_user_id in base al tipo di chiamata
// --------------------------------------------------------------------
if ($request->request->get('token')){
$token = $request->request->get('token');
$sql = "select id_user
from user_token
where token = :token";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue('token', $token);
$stmt->execute();
$run_user_id = $stmt->fetchOne();
} else if ($CallType == 'BPMN_API') {
$run_user_id = $request->request->get('run_user_id');
} else if ($CallType == 'SCHEDULER') {
$run_user_id = 1;
} else {
$run_user_id =$this->getUser()->getId();
}
// --------------------------------------------------------------------
//
// --------------------------------------------------------------------
$ins = "INSERT INTO chklst (name, description, run_start, run_result, run_end, run_user_id, ko_mail_task_id, ok_mail_task_id, from_template_id)
VALUES (:name,:description, null, null, null,:run_user_id,null, null, :from_template_id)
ON CONFLICT (from_template_id, name, run_start)
DO NOTHING";
$stmt = $em->getConnection()->prepare($ins);
$stmt->bindValue('name', $template->getName() );
$stmt->bindValue('description', $template->getDescription() );
$stmt->bindValue('run_user_id', $run_user_id );
$stmt->bindValue('from_template_id', $template_id);
$stmt->execute();
$chklst_new_id = $em->getConnection()->lastInsertId();
$logger->debug("[runChecklistAction] chklst_new_id: ".json_encode($chklst_new_id));
$checklist = $em->find(Chklst::class,$chklst_new_id);
$logger->debug("[runChecklistAction] checklis: ".json_encode($checklist));
$template_task = $em->getRepository('App:Chklsttmpl2task')->findByChklsttmpl($template);
foreach ($template_task as $tt) {
$config_json = $tt->getTask()->getConfigJson();
// Dal DB $config_json arriva come array vuoto [] invece di un oggetto {}.
// Lo trasformiamo in stdClass per avere un oggetto vuoto valido
// da usare con jsonb_set e altre operazioni su JSON come oggetto.
if (empty($config_json)){
$config_json = new \stdClass(); // {}
}
$ctask = new ChklstTask();
$ctask
->setChklst($checklist)
->setName($tt->getTask()->getName())
->setDescription($tt->getTask()->getDescription())
->setCmdType($tt->getTask()->getCmdType())
->setCmdText($tt->getTask()->getCmdText())
->setReturnType($tt->getTask()->getReturnType())
->setMatchOperator($tt->getTask()->getMatchOperator())
->setMatchText($tt->getTask()->getMatchValue())
->setCmdMaxRetry($tt->getTask()->getCmdMaxRetry())
->setCmdTimeRetry($tt->getTask()->getCmdTimeRetry())
->setKoHint($tt->getTask()->getKoHint())
->setTaskOrder($tt->getTaskOrder())
->setKoTask($tt->getKoTask())
->setSlave($tt->getSlave())
->setFlagBitmask($tt->getFlagBitmask())
->setRunBitmask($tt->getRunBitmask())
->setTypeDb($tt->getTask()->getTypeDb())
->setIpDb($tt->getTask()->getIpDb())
->setUserDb($tt->getTask()->getUserDb())
->setPwdDb($tt->getTask()->getPwdDb())
->setPortDb($tt->getTask()->getPortDb())
->setNameDb($tt->getTask()->getNameDb())
->setService($tt->getTask()->getService())
//->setSetTimeout($tt->getAction()->getSetTimeout())
->setFlagStopChk($tt->getTask()->getFlagStopChk())
->setFlagJumpTask($tt->getTask()->getFlagJumpTask())
->setNextTask($tt->getTask()->getNextTask())
//->setFlagJumpTaskAct($tt->getAction()->getFlagJumpTaskAct())
//->setNextTaskAct($tt->getAction()->getNextTaskAct())
//->setFlagJumpKoAct($tt->getAction()->getFlagJumpKoAct())
//->setNextTaskKoAct($tt->getAction()->getNextTaskKoAct())
->setConnectDirect($tt->getTask()->getConnectDirect())
->setStatementSelect($tt->getTask()->getStatementSelect())
->setAuthUsername($tt->getTask()->getAuthUsername())
->setAuthPassword($tt->getTask()->getAuthPassword())
->setAuthProtocol($tt->getTask()->getAuthProtocol())
->setAuthPort($tt->getTask()->getAuthPort())
->setAuthType($tt->getTask()->getAuthType())
->setAuthToken($tt->getTask()->getAuthToken())
->setAuthUrl($tt->getTask()->getAuthUrl())
->setFlagRunAs($tt->getTask()->getFlagRunAs())
->setRunAsUser($tt->getTask()->getRunAsUser())
->setFlagRunAsUserPassword($tt->getTask()->getFlagRunAsUserPassword())
->setRunAsPassword($tt->getTask()->getRunAsPassword())
->setFlagCheckExitStatus($tt->getTask()->getFlagCheckExitStatus())
->setFlagCheckCountQuery($tt->getTask()->getFlagCheckCountQuery())
->setFlagMailAttachment($tt->getTask()->getFlagMailAttachment())
->setMailAttachment($tt->getTask()->getMailAttachment())
->setSendToMail($tt->getTask()->getSendToMail())
->setSendCcMail($tt->getTask()->getSendCcMail())
->setSendTextMail($tt->getTask()->getSendTextMail())
->setSendObjectMail($tt->getTask()->getSendObjectMail())
->setSpoolOnFile($tt->getTask()->getSpoolOnFile())
->setFilenameSpoolQuery($tt->getTask()->getFilenameSpoolQuery())
->setFtpAnonymous($tt->getTask()->getFtpAnonymous())
->setFtpHostname($tt->getTask()->getFtpHostname())
->setFtpPort($tt->getTask()->getFtpPort())
->setFtpUser($tt->getTask()->getFtpUser())
->setFtpPassword($tt->getTask()->getFtpPassword())
->setFtpDirectory($tt->getTask()->getFtpDirectory())
->setFtpFilename($tt->getTask()->getFtpFilename())
->setConfigureTimeout($tt->getTask()->getConfigureTimeout())
->setServerCheckEmail($tt->getTask()->getServerCheckEmail())
->setPortCheckEmail($tt->getTask()->getPortCheckEmail())
->setUserCheckEmail($tt->getTask()->getUserCheckEmail())
->setPassCheckEmail($tt->getTask()->getPassCheckEmail())
->setFromCheckEmail($tt->getTask()->getFromCheckEmail())
->setSubjectCheckEmail($tt->getTask()->getSubjectCheckEmail())
->setBodyCheckEmail($tt->getTask()->getBodyCheckEmail())
->setAttemptCheckEmail($tt->getTask()->getAttemptCheckEmail())
->setPollingCheckEmail($tt->getTask()->getPollingCheckEmail())
->setUrlNavWeb($tt->getTask()->getUrlNavWeb())
->setUserNavWeb($tt->getTask()->getUserNavWeb())
->setPassNavWeb($tt->getTask()->getPassNavWeb())
->setObjectNameNavWeb($tt->getTask()->getObjectNameNavWeb())
->setNavWebAnonymous($tt->getTask()->getNavWebAnonymous())
->setNavWebSpoolObjectid($tt->getTask()->getNavWebSpoolObjectid())
->setIdUserNavWeb($tt->getTask()->getIdUserNavWeb())
->setIdPassNavWeb($tt->getTask()->getIdPassNavWeb())
->setTypeCheckEmail($tt->getTask()->getTypeCheckEmail())
->setFtpInAllDirectory($tt->getTask()->getFtpInAllDirectory())
->setObjectTypeNavWeb($tt->getTask()->getObjectTypeNavWeb())
->setUseCertificateDb($tt->getTask()->getUseCertificateDb())
->setTarsRunProjectName($tt->getTask()->getTarsRunProjectName())
->setTarsRunSuiteName($tt->getTask()->getTarsRunSuiteName())
->setTarsRunPathScenario($tt->getTask()->getTarsRunPathScenario())
->setTarsRunNomeScenario($tt->getTask()->getTarsRunNomeScenario())
//->setTarsRunIdentificativo($tt->getTask()->getTarsRunIdentificativo())
->setTarsRunParametroDefault($tt->getTask()->getTarsRunParametroDefault())
->setTarsRunUser($tt->getTask()->getTarsRunUser())
->setTarsRunIpaddress($tt->getTask()->getTarsRunIpaddress())
->setTarsRunHostname($tt->getTask()->getTarsRunHostname())
->setTarsRunPassword($tt->getTask()->getTarsRunPassword())
->setTarsRunUsePassword($tt->getTask()->getTarsRunUsePassword())
->setSaveAttachmentsEmail($tt->getTask()->getSaveAttachmentsEmail())
->setInformationText($tt->getTask()->getInformationText())
->setFlagSendCall($tt->getTask()->getFlagSendCall())
->setTextToSpeechFlagCall($tt->getTask()->getTextToSpeechFlagCall())
->setTextToSpeechNumberCall($tt->getTask()->getTextToSpeechNumberCall())
->setTextToSpeechTextSendCall($tt->getTask()->getTextToSpeechTextSendCall())
->setUccxFlagCall($tt->getTask()->getUccxFlagCall())
->setUccxNumberCall($tt->getTask()->getUccxPromptMessage())
->setUccxConfirmMessage($tt->getTask()->getUccxConfirmMessage())
->setSmsFlagSend($tt->getTask()->getSmsFlagSend())
->setSmsNumber($tt->getTask()->getSmsNumber())
->setSmsTextSend($tt->getTask()->getSmsTextSend())
->setTelegramFlagSend($tt->getTask()->getTelegramFlagSend())
->setTelegramChat($tt->getTask()->getTelegramChat())
->setTelegramTextSend($tt->getTask()->getTelegramTextSend())
->setTaskID($tt->getTaskId())
->setConfigJson($config_json);
// $logger->debug("[runChecklistAction] inserimento su ChklstTask - checklist");
$em->persist($ctask);
$server = $em->getRepository('App:Task2server')->findByTask($tt);
foreach ($server as $s) {
$tserver = new TaskServer();
$tserver
->setTask($ctask)
->setHostname($s->getServer()->getHostName())
->setIpAddress($s->getServer()->getIpAddress())
->setUsername($s->getServer()->getUsername())
->setGroupName($s->getServer()->getGroupName())
->setServerOrder($s->getServerOrder());
// $logger->debug("[runChecklistAction] inserimento su TaskServer");
$em->persist($tserver);
}
}
$sycore_info = $this->sycoreService->getSycoreInfo();
$queue = new Queue();
$queue->setChklst($checklist);
$queue->setSycoreInstanceUuid($sycore_info->instance_uuid);
if ($CallType == 'SCHEDULE') {
$date = date_create_from_format('d/m/Y H:i', '31/12/2999 23:59');
$queue
->setScheduleDate($request->request->get('schedule_date'))
->setIdSchedule($request->request->get('schedule_id'));
//->setRunStart($date);
}
$em->persist($queue);
$em->flush();
$chklst_run_id = $checklist->getId();
// $logger->debug("[runChecklistAction] Valore CheklistID " . json_encode($chklst_run_id));
$auditLogger = $auditLoggerFactory->createWorkflow(
$template->getName(),
$run_user_id,
$template_id,
$chklst_run_id
);
$sql = "select param_name, param_value , param_type
from chklst_template_parameters
where chklst_id = :template_id";
$statement = $connection->prepare($sql);
$statement->bindValue('template_id',$template_id);
$statement->execute();
$result = $statement->fetchAll();
$auditLogger->log(WorkflowAuditLogger::LEVEL_INFO,WorkflowAuditLogger::EVENT_WFL_START,[
'call_type'=> $CallType,
'params'=> $result
]);
// Gestione modifica parametri a runtime da chiamata API
if ($CallType == 'SCHEDULER') {
$logger->debug("[runChecklistAction] Chiamata da SCHEDULER2 - inizio gestione parametri");
try {
$parametersOfWorkflow = $result;
$parametersOfSchedule = [];
$tmp = json_decode($request->request->get("params"), true);
foreach ($tmp as $p) {
$parametersOfSchedule[$p['param_name']] = $p['param_value'];
}
foreach ($parametersOfWorkflow as $param) {
if (isset($parametersOfSchedule[$param['param_name']])) {
$param['param_value'] = $parametersOfSchedule[$param['param_name']];
}
$sql = <<<SQL
INSERT INTO chklst_template_parameters_api (chklst_id, param_name, param_value, param_type, chklst_run_id)
VALUES (:template_id, :param_name, :param_value, :param_type, :chklst_run_id)
ON CONFLICT (chklst_id, param_name, chklst_run_id) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type
SQL;
$em->getConnection()->executeQuery($sql, [
'template_id' => $template_id,
'param_name' => $param['param_name'],
'param_value' => $param['param_value'],
'param_type' => $param['param_type'],
'chklst_run_id' => $chklst_run_id
]);
}
} catch (\Exception $e) {
$logger->error("[runChecklistAction] Errore nella decodifica dei parametri da SCHEDULER2: " . $e->getMessage());
throw new \Exception('Errore nella decodifica dei parametri da SCHEDULER2: ' . $e->getMessage());
}
} else if ($CallType == 'BPMN_API') {
foreach ($result as $r) {
if (isset(json_decode($request->getContent(), true)['params'])) {
$input = json_decode($request->getContent(), true);
$flatParams = array_column($input['params'], 'value', 'name');//array_merge(...json_decode($request->getContent(), true)['params']);
if(isset($flatParams[$r['param_name']])){
$param_value = $flatParams[$r['param_name']];
} else {
$param_value = $r['param_value'];
// $logger->debug("[runChecklistAction] ELSE: valore Parameter_value " . $param_value);
}
} else {
$param_value = $r['param_value'];
// $logger->debug("[runChecklistAction] ELSE: valore Parameter_value " . $param_value);
}
$param_name = $r['param_name'];
$param_type = $r['param_type'];
$sql = "insert into chklst_template_parameters_api (chklst_id, param_name, param_value, param_type, chklst_run_id)
values (:template_id, :param_name, :param_value, :param_type, :chklst_run_id)
ON CONFLICT (chklst_id, param_name, chklst_run_id) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type ";
// $logger->debug("[runChecklistAction] Upsert " . $sql);
$statement = $connection->prepare($sql);
$statement->bindValue('template_id',$template_id);
$statement->bindValue('param_name',$param_name);
$statement->bindValue('param_value',$param_value);
$statement->bindValue('param_type',$param_type);
$statement->bindValue('chklst_run_id',$chklst_run_id);
$statement->execute();
}
} else {
foreach ($result as $r) {
if (isset($_GET[$r['param_name']])) {
$param_value = $_GET[$r['param_name']];
// $logger->debug("[runChecklistAction] valore Parameter_value " . $param_value);
} else {
$param_value = $r['param_value'];
// $logger->debug("[runChecklistAction] ELSE: valore Parameter_value " . $param_value);
}
$param_name = $r['param_name'];
$param_type = $r['param_type'];
// $logger->debug("[runChecklistAction] valore param_name " . $param_name);
// $logger->debug("[runChecklistAction] valore param_type " . $param_type);
// DA VERIFICARE
$sql = "insert into chklst_template_parameters_api (chklst_id, param_name, param_value, param_type, chklst_run_id)
values (:template_id, :param_name, :param_value, :param_type, :chklst_run_id)
ON CONFLICT (chklst_id, param_name, chklst_run_id) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type ";
// $logger->debug("[runChecklistAction] Upsert " . $sql);
$statement = $connection->prepare($sql);
$statement->bindValue('template_id',$template_id);
$statement->bindValue('param_name',$param_name);
$statement->bindValue('param_value',$param_value);
$statement->bindValue('param_type',$param_type);
$statement->bindValue('chklst_run_id',$chklst_run_id);
$statement->execute();
}
}
// Gestione modifica parametri a runtime da frontend
$list_param_type = preg_grep("/^form_lista_param_type_" . $template_id . "_*/", array_keys($_POST));
foreach ($list_param_type as $l) {
$exp = explode("_", $l);
$i = end($exp);
if (isset($_REQUEST["form_lista_param_type" . '_' . $template_id . '_' . $i]) && isset($_REQUEST["form_lista_param_name" . '_' . $template_id . '_' . $i]) && isset($_REQUEST["form_lista_param_value" . '_' . $template_id . '_' . $i])) {
$param_type = $_REQUEST["form_lista_param_type" . '_' . $template_id . '_' . $i];
$param_name = $_REQUEST["form_lista_param_name" . '_' . $template_id . '_' . $i];
$param_value =$_REQUEST["form_lista_param_value" . '_' . $template_id . '_' . $i];
// DA VERIFICARE
$sql = "insert into chklst_template_parameters_api (chklst_id, param_name, param_value, param_type, chklst_run_id)
values (:template_id, :param_name, :param_value, :param_type, :chklst_run_id)
ON CONFLICT (chklst_id, param_name, chklst_run_id) DO UPDATE SET
param_name = :param_name,
param_value = :param_value,
param_type = :param_type ";
// $logger->debug("[runChecklistAction] Upsert " . $sql);
$statement = $connection->prepare($sql);
$statement->bindValue('template_id',$template_id);
$statement->bindValue('param_name',$param_name);
$statement->bindValue('param_value',$param_value);
$statement->bindValue('param_type',$param_type);
$statement->bindValue('chklst_run_id',$chklst_run_id);
$statement->execute();
}
}
if ($CallType == 'API') {
$response = new JsonResponse();
$response->setData(array('template_id' => $template_id, 'cheklist_id' => $chklst_run_id));
return $response;
} else if ($CallType == 'BPMN_API' || $CallType == 'SCHEDULER') {
$response = new JsonResponse();
$response->setData(array('workflow_id' => $template_id, 'workflow_run_id' => $chklst_run_id));
return $response;
} else {
//return $this->redirect($this->generateUrl('home'));
return $this->redirect($this->generateUrl('home_old', array('template_id' => $template_id)));
}
}
public function getFullResult($id, LoggerInterface $logger) {
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$stmt = $conn->prepare("select full_result from task_server where task_id=:id");
$stmt->bindValue('id', $id);
$stmt->execute();
$text = $stmt->fetchOne();
$stmt = $conn->prepare("select replace(name,' ','-') as name from chklst_task where id=:id");
$stmt->bindValue('id', $id);
$stmt->execute();
$name = $stmt->fetchOne();
$response = new Response($text);
$disposition = HeaderUtils::makeDisposition(
HeaderUtils::DISPOSITION_ATTACHMENT,
"Task_" . $name . "_result.txt"
);
$response->headers->set('Content-Disposition', $disposition);
return $response;
}
public function toByteSize($iUnits, $sUnit)
{
$aUnits = array('B' => 0, 'KB' => 1, 'MB' => 2, 'GB' => 3, 'TB' => 4, 'PB' => 5, 'EB' => 6, 'ZB' => 7, 'YB' => 8, 'K' => 1, 'M' => 2, 'G' => 3, 'T' => 4, 'P' => 5, 'E' => 6, 'Z' => 7, 'Y' => 8);
// $sUnit = strtoupper(trim(substr($p_sFormatted, -2)));
if (intval($sUnit) !== 0) {
$sUnit = 'B';
}
if (!in_array($sUnit, array_keys($aUnits))) {
return false;
}
// $iUnits = trim(substr($p_sFormatted, 0, strlen($p_sFormatted) - 2));
if (!intval($iUnits) == $iUnits) {
return false;
}
return $iUnits * pow(1024, $aUnits[$sUnit]);
}
public function importChecklistAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
// Temporaneo, testing
if ($this->USE_NEW_IMPEXP) {
return $this->importChecklistAction_new($request, $logger, $params);
} else {
return $this->importChecklistAction_old($request, $logger, $params);
}
}
public function importChecklistAction_new(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
$res_json = new JsonResponse();
$upload_only = $request->request->get('upload_only', 'N');
$uploaded_file = $request->files->get('xml_fileToUpload_0_1');
$uploadDirectory = $this->getParameter('kernel.project_dir') . '/uploads/import/';
$uploaded_file->move($uploadDirectory, $uploaded_file->getClientOriginalName());
try
{
$target_file = $uploadDirectory . $uploaded_file->getClientOriginalName();
$importer =
ImpExp\ImpExp::createImporter(
$target_file,
$this->getDoctrine()->getManager()->getConnection(),
$logger
);
if ($upload_only === 'Y') {
$catalog = $importer->getCatalog();
$workflow_data = [];
foreach ($catalog['workflows'] as $w) {
$workflow_data[] = [
'id' => $w['id'],
'workflow' => $w['name']
];
}
$res_json->setData(array(
"id_workflow" => 0,
"workflow" => $workflow_data,
"extension" => 'sybot'
));
return $res_json;
}
else
{
$importer->import();
$res_json->setData(array(
"id_workflow" => 0,
"workflow" => [],
"extension" => 'sybot'
)
);
}
} catch (\Exception $e) {
$logger->error("Errore durante importazione checklist: " . $e->getMessage() . "trace: " . $e->getTraceAsString());
$res_json->setData(array(
"error" => "Errore durante importazione checklist: " . $e->getMessage()
));
$res_json->setStatusCode(Response::HTTP_INTERNAL_SERVER_ERROR);
}
return $res_json;
}
public function importChecklistAction_old(Request $request, LoggerInterface $logger, ParameterBagInterface $params)
{
try {
$csrfToken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('import_checklist', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$user = $this->getUser();
$userId = $user->getId();
//$logger = $this->getLogger();
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$functions = new Functions($this, $logger, $em);
$res_json = new JsonResponse();
$checklist = null;
if (isset($_POST["target_import_dir"])) {
$target_dir = $params->get('kernel.project_dir') . '/' . $_POST["target_import_dir"];
} else if (isset($_GET["target_import_dir"])) {
$target_dir = $params->get('kernel.project_dir') . '/' . $_GET["target_import_dir"];
} else {
$target_dir = $params->get('kernel.project_dir') . '/uploads/import/';
}
if (isset($_POST["upload_only"])) {
$fl_upload_only = $_POST["upload_only"];
} else if (isset($_GET["upload_only"])) {
$fl_upload_only = $_GET["upload_only"];
} else {
$fl_upload_only = "N";
}
if (isset($_POST["workflow_role"])) {
$checklist_role = $_POST["workflow_role"];
} else if (isset($_GET["workflow_role"])) {
$checklist_role = $_GET["workflow_role"];
} else {
$checklist_role = "SYBOT";
}
$cid = 0;
$check_files_cnt = 0;
if (isset($_FILES["xml_fileToUpload" . '_' . $cid . '_1'])) {
$logger->debug("[importChecklistAction] STO DENTRO ISSET xml_FILES");
$target_file = $target_dir . basename($_FILES["xml_fileToUpload" . '_' . $cid . '_1']["name"]);
if (move_uploaded_file($_FILES["xml_fileToUpload" . '_' . $cid . '_1']["tmp_name"], $target_file)) {
$logger->debug("[importChecklistAction] The xml_file " . basename($_FILES["xml_fileToUpload" . '_' . $cid . '_1']["name"]) . " has been uploaded.");
}
$check_files_cnt++;
$path_parts = pathinfo($target_file);
$logger->debug("estensione file: " . $path_parts['extension']);
$isSybot = $path_parts['extension'] == "sybot" ? 1 : 0;
if ($path_parts['extension'] == "sybot" || $path_parts['extension'] == "acm") {
//DECRITTO E SCOMPATTO IL PACCHETTO .acm
$cipher = new AES(); // could use AES::MODE_CBC
$cipher->setKey($_ENV["KEY_SYBOT"]);
$cipher->setIV($_ENV["IV_SYBOT"]);
$decrypted_contents = base64_decode($cipher->decrypt(file_get_contents($target_file)));
$file_zip = $isSybot? str_replace('.sybot', '.zip', $target_file) : str_replace('.acm', '.zip', $target_file);
file_put_contents($file_zip, $decrypted_contents);
$dir_zip = str_replace('.zip', '', $file_zip);
$zip = new \ZipArchive;
$res = $zip->open($file_zip);
if ($res === TRUE) {
$zip->extractTo($dir_zip);
$zip->close();
}
if (file_exists($file_zip))
unlink($file_zip);
if (file_exists($target_file))
unlink($target_file);
$checklists = json_decode(file_get_contents($dir_zip . '/export.json'), true);
// $logger -> info("[importChecklistAction] json import: ".json_encode($import_json));
} else if ($path_parts['extension'] == "xlsm") {
$excel = new ExcelFactory();
$checklist = $excel->loadTemplate($checklist_role, $target_file);
}
} else {
$path_parts['extension'] = 'xml';
}
$id_checklist = 0;
if ($fl_upload_only != "Y") {
$logger->debug("fl_upload_only diverso da Y");
if (isset($_POST["nome_file"])) {
$FileName = $_POST['nome_file'];
// $serializer = new Serializer(array(), array(new XmlEncoder()));
$file[] = file_get_contents($target_dir . $FileName, FILE_USE_INCLUDE_PATH);
$fl_json = 0;
// $checklists = $serializer->decode($file, 'xml');
} else if (isset($_GET["nome_file"])) {
$FileName = $_GET['nome_file'];
// $serializer = new Serializer(array(), array(new XmlEncoder()));
$file[] = file_get_contents($target_dir . $FileName, FILE_USE_INCLUDE_PATH);
$fl_json = 0;
// $checklists = $serializer->decode($file, 'xml');
} else if ($path_parts['extension'] == "sybot" || $path_parts['extension'] == "acm") { //PACCHETTO .ACM
$logger->debug("sto nel pacchetto acm: " . json_encode($checklists));
// $checklists=json_decode(json_encode($checklists));
foreach ($checklists as $c) {
$logger->debug("c: " . json_encode($c));
$isSybot = $path_parts['extension'] == "sybot" ? 1 : 0;
$d = $isSybot ? $c['workflow'] : $c['checklist'];
$logger->debug("file xml: " . $dir_zip . '/' . $d . '/xml/' . $c['xml']);
$file[] = file_get_contents($dir_zip . '/' . $d . '/xml/' . $c['xml'], FILE_USE_INCLUDE_PATH);
}
$fl_json = 0;
// $file = [];
} else if ($path_parts['extension'] == "xlsm") {
$excel = new ExcelFactory();
$checklist = $excel->loadTemplate($checklist_role, $target_file);
$logger->debug('CHECKLIST XLSM:: ' . json_encode($checklist));
$file['json'] = $checklist;
$fl_json = 1;
} else if ($check_files_cnt > 0) {
// for ($i = 1; $i <= 120; $i++) {
if (isset($_FILES["xml_fileToUpload" . '_' . $cid . '_1'])) {
// $logger -> info("[importChecklistAction] STO DENTRO ISSET xml_FILES");
$file[] = file_get_contents($target_dir . basename($_FILES["xml_fileToUpload" . '_' . $cid . '_1']["name"]), FILE_USE_INCLUDE_PATH);
$logger->debug("sto dentro xml_fileToUpload: " . json_encode($file));
// if (move_uploaded_file($_FILES["xml_fileToUpload".'_'.$cid.'_'.$i]["tmp_name"], $target_file)) {
// $logger -> info("[importChecklistAction] The xml_file ". basename( $_FILES["xml_fileToUpload".'_'.$cid.'_'.$i]["name"]). " has been uploaded.");
//
// }
// $check_files_cnt++;
}
// }
$fl_json = 0;
} else if (isset($_POST["dati_json"])) {
$checklist = json_decode($_POST["dati_json"]);
$file['json'] = $checklist;
$fl_json = 1;
} else if (isset($_GET["dati_json"])) {
$checklist = json_decode($_GET["dati_json"]);
$file['json'] = $checklist;
$fl_json = 1;
} else if ($request->request->get('dati_json') != null) {
$logger->debug('DATI JSON:: ' . $request->request->get('dati_json'));
$checklist = json_decode($request->request->get('dati_json'));
$file['json'] = $checklist;
$fl_json = 1;
} else {
$FileName = 0;
$checklist = [];
$id_checklist = 0;
$fl_json = 0;
$file = [];
}
// $logger->debug("[importChecklistAction] file[] " . json_encode($file));
$cnt = 0;
$templateToImportKeys = preg_grep("/^import_*/", array_keys($_POST));
$templateToImport = array_map(function ($val) {
return $_POST[$val];
}, $templateToImportKeys);
foreach ($file as $f) {
if ($fl_json == 0) {
$serializer = new Serializer(array(), array(new XmlEncoder()));
$checklist = $serializer->decode($f, 'xml');
} else {
$checklist = json_decode(json_encode($f), true);
$logger->debug('CHECKLIST:::: ' . json_encode($checklist));
}
if (!in_array($checklist['name'], $templateToImport)) {
continue;
}
$logger->debug("=============================================================================");
$logger->debug("[importChecklistAction] Nome workflow da importare: " . $checklist['name']);
$logger->debug("=============================================================================");
// qui ci sono tutte le colonne della tabella chklst_template
$wflTemplate = $isSybot ? $checklist['template'] : $checklist;
$oldTemplateId = $wflTemplate["id"];
// Se non è un template Sybot, impostiamo i campi di lock con valori di default
if (!$isSybot) {
if (!isset($wflTemplate['locked'])) $wflTemplate['locked'] = 0;
if (!isset($wflTemplate['lock_session_user_id'])) $wflTemplate['lock_session_user_id'] = '';
}
$sql = "insert into chklst_template (";
foreach ($wflTemplate as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if ($key == "roles") {
break;
}
$sql .= $key . ",";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ") values (";
foreach ($wflTemplate as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if ($key == "roles") {
break;
}
if ($key == 'data_creazione') {
$sql .= "now(),";
continue;
}
if ($key == 'data_modifica') {
$sql .= "now(),";
continue;
}
if ($key == 'creato_da') {
$sql .= "$userId,";
continue;
}
if ($key == 'modificato_da') {
$sql .= "$userId,";
continue;
}
if ($key == 'locked') {
if ($value == '') {
$sql .= "0,";
} else {
$sql .= $value . ",";
}
continue;
}
$sql .= "'" . $value . "',";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ")";
$sql .= " ON CONFLICT (name) DO UPDATE SET ";
foreach ($wflTemplate as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if ($key == "roles") {
break;
}
if ($key == 'data_modifica') {
$sql .= "data_modifica = now(),";
continue;
}
if ($key == 'data_creazione') {
if ($value == '') {
$sql .= "data_creazione = now(),";
} else {
$sql .= "data_creazione = '" . $value . "',";
}
continue;
}
if ($key == 'creato_da') {
if ($value == '') {
$sql .= "creato_da = $userId,";
} else {
$sql .= "creato_da = " . $value . ",";
}
continue;
}
if ($key == 'modificato_da') {
$sql .= "modificato_da = $userId,";
continue;
}
if ($key == 'locked') {
if ($value == '') {
$sql .= "locked = 0,";
} else {
$sql .= "locked = " . $value . ",";
}
continue;
}
$sql .= $key . " = '" . $value . "',";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$statement = $connection->prepare($sql);
$statement->execute();
//RECUPERO L'ID DELLA CHECKLIST APPENA INSERITA O AGGIORNATA
$sql = "select id
from chklst_template
where name = '" . $checklist['name'] . "'";
$statement = $connection->prepare($sql);
$statement->execute();
$id_checklist = $statement->fetchAll()[0]['id'];
// INSERISCO EX NOVO I RUOLI DELLA CHECKLIST VERIFICANDO CHE ESISTANO, ALTRIMENTI LI CREO
if (is_array($checklist['roles'])) {
$sql = "delete from chklst_template_role
where id_template = $id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($checklist['roles'] as $role) {
// VERIFICO SE IL RUOLO ESISTE NEL DB
$sql = "select count(1) cnt
from roles
where description_roles=:description_roles";
$statement = $connection->prepare($sql);
$statement->bindValue('description_roles', $role['description_roles']);
$statement->execute();
$cnt_role = $statement->fetchAll()[0]['cnt'];
$logger->debug("cnt_role: " . $cnt_role);
if ($cnt_role == 0) { //IL RUOLO NON ESISTE NEL DB, LO CREO
$sql = "insert into roles (description_roles, note)
values (:description_roles, 'imported')";
$statement = $connection->prepare($sql);
$statement->bindValue('description_roles', $role['description_roles']);
$statement->execute();
$em->flush();
}
// RECUPERO L'ID DEL RUOLO
$sql = "select id_roles
from roles
where description_roles=:description_roles";
$statement = $connection->prepare($sql);
$statement->bindValue('description_roles', $role['description_roles']);
$statement->execute();
$id_role = $statement->fetchAll()[0]['id_roles'];
$logger->debug("id_role: " . $id_role);
// Associa il ruolo importato all'utente corrente se non è già stato fatto
$sql = "select 1 from user_profile_roles where id_user = $userId and id_roles = $id_role";
$statement = $connection->prepare($sql);
$statement->execute();
if (!$statement->fetchOne()) {
$sql =
"insert into user_profile_roles (id_user, id_profile, id_roles)
values ($userId, 1, $id_role)";
$statement = $connection->prepare($sql);
$statement->execute();
}
// INSERISCO L'ASSOCIAZIONE RUOLO-CHECKLIST
$sql = "insert into chklst_template_role (id_template, id_roles)
values ($id_checklist, $id_role)";
$statement = $connection->prepare($sql);
$statement->execute();
$logger->debug("Importato ruolo " . $role['description_roles'] . " con ID $id_role per la checklist $id_checklist");
}
}
// INSERISCO EX NOVO I PARAMETRI DELLA CHECKLIST con flag_file='N'
if (is_array($checklist['parameters'])) {
$sql = "delete from chklst_template_parameters
where chklst_id = $id_checklist
and flag_file='N'";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($checklist['parameters'] as $parameter) {
// $parameter=$checklist['parameters'][$ip];
$sql = "insert into chklst_template_parameters (chklst_id,
param_name,
param_value,
param_type,
flag_file)
values (:id_checklist,
:param_name,
:param_value,
:param_type,
:flag_file)";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter['param_name']);
$statement->bindValue('param_value', $parameter['param_value']);
$statement->bindValue('param_type', $parameter['param_type']);
$statement->bindValue('flag_file', $parameter['flag_file']);
$statement->execute();
}
}
// INSERISCO EX NOVO I PARAMETRI FILE DELLA CHECKLIST con flag_file='Y'
if (is_array($checklist['parameters_files'])) {
$sql = "delete from chklst_template_parameters
where chklst_id = $id_checklist
and flag_file='Y'";
$statement = $connection->prepare($sql);
$statement->execute();
$sql = "delete from chklst_template_parameters_files
where chklst_id = $id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($checklist['parameters_files'] as $parameter_files) {
// $parameter_files=$checklist['parameters_files'][$ipf];
$sql = "insert into chklst_template_parameters_files (chklst_id,
param_name,
param_file_name,
param_file_path,
param_file_type)
values (:id_checklist,
:param_name,
:param_file_name,
:param_file_path,
:param_file_type)";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter_files['param_name']);
$statement->bindValue('param_file_name', $parameter_files['param_file_name']);
$statement->bindValue('param_file_path', $parameter_files['param_file_path']);
$statement->bindValue('param_file_type', $parameter_files['param_file_type']);
$statement->execute();
/*
//INSERISCO IL PARAMETRO DI TIPO STRINGA param_name
$sql = "insert into chklst_template_parameters (chklst_id,
param_name,
param_value,
param_type,
flag_file)
values (:id_checklist,
:param_name,
:param_file_name,
'string',
'Y')";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter_files['param_name']);
$statement->bindValue('param_file_name', $parameter_files['param_file_name']);
$statement->execute();*/
//INSERISCO IL PARAMETRO DI TIPO FILE param_name.filename
$sql = "insert into chklst_template_parameters (chklst_id,
param_name,
param_value,
param_type,
flag_file)
values (:id_checklist,
:param_name,
:param_file_name,
'file',
'Y')";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter_files['param_name'] . '.filename');
$statement->bindValue('param_file_name', $parameter_files['param_file_name']);
$statement->execute();
//INSERISCO IL PARAMETRO DI TIPO FILE param_name.file
$sql = "insert into chklst_template_parameters (chklst_id,
param_name,
param_value,
param_type,
flag_file)
values (:id_checklist,
:param_name,
:param_file_name,
'file',
'Y')";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter_files['param_name'] . '.file');
$statement->bindValue('param_file_name', $parameter_files['param_file_name']);
$statement->execute();
//INSERISCO IL PARAMETRO DI TIPO FILE param_name.content
$sql = "insert into chklst_template_parameters (chklst_id,
param_name,
param_value,
param_type,
flag_file)
values (:id_checklist,
:param_name,
:param_file_name,
'file',
'Y')";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $parameter_files['param_name'] . '.content');
$statement->bindValue('param_file_name', $parameter_files['param_file_name']);
$statement->execute();
// IMPORTO I FILE NEL CASO DI PACCHETTO .ACM
if ($path_parts['extension'] == "sybot" || $path_parts['extension'] == "acm") {
$imp_dir = $dir_zip . '/' . $checklist['name'] . '/uploads/';
$imp_file = $imp_dir . $parameter_files['param_file_name'];
$up_dir = $params->get('kernel.project_dir') . '/uploads/' . $id_checklist . '/';
if (!is_dir($up_dir)) {
mkdir($up_dir, 0777, true);
}
$up_file = $up_dir . $parameter_files['param_file_name'];
$logger->debug("file imp: " . $imp_file . " - file upload: " . $up_file);
copy($imp_file, $up_file);
}
}
}
// INSERISCO EX NOVO IL WORKFLOW FILE
if (is_array($checklist['workflow_file'])) {
$sql = "delete from chklst_template_workflow_files
where chklst_id = $id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($checklist['workflow_file'] as $workflow_file) {
// $workflow_file=$checklist['workflow_file'][$ip];
$sql = "insert into chklst_template_workflow_files (chklst_id,
param_name,
param_file_name,
param_file_path,
param_file_type,
wf_description)
values (:id_checklist,
:param_name,
:param_file_name,
:param_file_path,
:param_file_type,
:wf_description)";
$statement = $connection->prepare($sql);
$statement->bindValue('id_checklist', $id_checklist);
$statement->bindValue('param_name', $workflow_file['param_name']);
$statement->bindValue('param_file_name', $workflow_file['param_file_name']);
$statement->bindValue('param_file_path', $workflow_file['param_file_path']);
$statement->bindValue('param_file_type', $workflow_file['param_file_type']);
$statement->bindValue('wf_description', $workflow_file['param_file_type']);
$statement->execute();
// IMPORTO I WF_FILE NEL CASO DI PACCHETTO .ACM
if ($path_parts['extension'] == "sybot" || $path_parts['extension'] == "acm") {
$imp_wf_dir = $dir_zip . '/' . $checklist['name'] . '/wf_uploads/';
$imp_wf_file = $imp_wf_dir . $workflow_file['param_file_name'];
$up_wf_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $id_checklist . '/';
if (!is_dir($up_wf_dir)) {
mkdir($up_wf_dir, 0777, true);
}
$up_wf_file = $up_wf_dir . $workflow_file['param_file_name'];
if (file_exists($imp_wf_file)) {
$logger->debug("wf_file imp: " . $imp_wf_file . " - wf_file upload: " . $up_wf_file);
copy($imp_wf_file, $up_wf_file);
}
}
}
}
//====================================================
// INSERISCO O AGGIORNO I TASK
//====================================================
$taskList = [];
if (is_array($checklist['tasks'])) {
//PULISCO LA TABELLA pre_chklsttmpl2task PER LA CHECKLIST CHE STO IMPORTANDO
$sql = "delete
from pre_chklsttmpl2task
where chklsttmpl_id = $id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
$sql = "delete from task_conditional where id_template=$id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($checklist['tasks'] as $tasks) {
$task = $isSybot ? $tasks['task'] : $tasks;
$taskObj = $isSybot ? $tasks : $tasks;
$taskList[$task['id']] = [];
$taskList[$task['id']]["name"] = $task["name"];
$taskList[$task['id']]["cmdType"] = $task["cmd_type"];
// se Il Task è condiviso, salto delete/insert task e reinserisco solo in pre_
$sql = "DELETE FROM task
WHERE id = :taskId
AND NOT EXISTS (
SELECT *
FROM pre_chklsttmpl2task
WHERE task_id = :taskId
)";
$statement = $connection->prepare($sql);
$statement->bindValue(":taskId", $task['id'],);
$statement->execute();
if (!array_key_exists('config_json', $task) || $task['config_json'] == "") {
$task['config_json'] = [];
if(!$isSybot && $task['cmd_type'] == "ssh") {
if($task['cmd_text'] == "ssh <user>@<ip_address>"){
$task['config_json'] = ['operation'=> "connection"];
} else {
$task['config_json'] = ['operation'=> "command"];
}
}
}
$sql = "insert into task (";
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
$sql .= $key . ",";
}
if(!$isSybot){
$sql .= " config_json,"; //aggiungo config_json se sto importando da ACM
}
$sql = substr($sql, 0, -1);
$sql .= ") values (";
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
$sql .= ":" . $key . ",";
}
if(!$isSybot){
$sql .= " :config_json,"; //aggiungo placeholder :config_json se sto importando da ACM
}
$sql = substr($sql, 0, -1);
$sql .= ")";
$statement = $connection->prepare($sql);
//todo fare if con le colonne intero e se null mettere 0
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
if ($key == 'cmd_max_retry') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'cmd_time_retry') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'creato_da') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'modificato_da') {
$intV = (int)$userId;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'data_modifica') {
$dt = date('Y-m-d H:i:s');
$statement->bindValue($key, $dt);
continue;
}
if ($key == 'data_creazione') {
$dt = date('Y-m-d H:i:s');
$statement->bindValue($key, $dt);
continue;
}
if($key == "id_message_uccx") {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'servers') {
break;
}
$statement->bindValue($key, $value);
}
if(!$isSybot && $task["config_json"]) {
$statement->bindValue('config_json', $task['config_json'],Types::JSON);
}
// $logger->debug("[importChecklistAction] sql task: " . $sql);
$statement->execute();
//RECUPERO L'ID DELLA CHECKLIST APPENA INSERITA O AGGIORNATA
$id_task = $connection->lastInsertId();
$taskList[$task['id']]["newId"] = $id_task;
// INSERISCO I TASK NELLA pre_chklsttmpl2task
$sql = "insert into pre_chklsttmpl2task (chklsttmpl_id, task_id, task_order)
values ($id_checklist, $id_task, " . $task['task_order'] . ")";
$statement = $connection->prepare($sql);
$statement->execute();
if (is_array($taskObj['servers'])) {
//PULISCO LA TABELLA TASK2SERVER PER IL TASK CHE STO IMPORTANDO
$sql = "delete
from task2server
where task_id = :id_task";
$statement = $connection->prepare($sql);
$statement->bindValue('id_task', $id_task);
$statement->execute();
// INSERISCO O AGGIORNO I SERVER USANDO COME CHIAVE IP-USERNAME
foreach ($taskObj['servers'] as $server) {
//------------------------------------------------------------------------------------------
// Se l'importazione è di tipo acm, usiamo il vecchio metodo di decodificazione password
//------------------------------------------------------------------------------------------
if($isSybot){
$serverPassword = $server['password'];
} else {
$acmPassword = utf8_decode(hex2bin(utf8_decode(base64_decode($server['password']))));
$serverPassword = $functions->encrypt($acmPassword);
}
$sql = "insert into server (hostname, ip_address, username, group_name, password)
values (:hostname,
:ip_address,
:username,
:group_name,
:password)";
$statement = $connection->prepare($sql);
$statement->bindValue('hostname', $server['hostname'] );
$statement->bindValue('ip_address', $server['ip_address']);
$statement->bindValue('username', $server['username']);
$statement->bindValue('group_name', $server['group_name']);
$statement->bindValue('password', $serverPassword);
$statement->execute();
//RECUPERO L'ID DEL SERVER APPENA INSERITO O AGGIORNATO
$id_server = $connection->lastInsertId();
//INSERISCO NELLA TASK2SERVER LA RIGA RELATIVA AL TASK E AL SERVER CHE STO IMPORTANDO
$sql = "insert into task2server (task_id, server_id, server_order, flag_use_password)
values (:id_task,
:id_server,
:server_order,
:flag_use_password)";
$statement = $connection->prepare($sql);
$statement->bindValue('id_task', $id_task );
$statement->bindValue('id_server', $id_server );
$statement->bindValue('server_order', $server['server_order'] );
$statement->bindValue('flag_use_password', $server['flag_use_password'] );
$statement->execute();
}
}
if (is_array($taskObj['select']['fields'])) {
//CANCELLO I SELECT FIELDS PER IL TASK CHE STO IMPORTANDO
$sql = "delete from task_select_fields where task_id=$id_task";
$statement = $connection->prepare($sql);
$statement->execute();
//CANCELLO LA SELECT FROM PER IL TASK CHE STO IMPORTANDO
$sql = "delete from task_select_from where task_id=$id_task";
$statement = $connection->prepare($sql);
$statement->execute();
//INSERISCO I SELECT FIELDS PER IL TASK CHE STO IMPORTANDO
foreach ($taskObj['select']['fields'] as $field) {
if (count($taskObj['select']['fields']) < 1) {
break;
}
$sql = "insert into task_select_fields (task_id,
field_name,
field_value,
field_type,
field_precision,
field_id)
values (:id_task,
:field_name,
:field_value,
:field_type,
:field_precision,
:field_id)";
$statement = $connection->prepare($sql);
$statement->bindValue('id_task', $id_task );
$statement->bindValue('field_name', $field['field_name'] );
$statement->bindValue('field_value', $field['field_value'] );
$statement->bindValue('field_type', $field['field_type'] );
$statement->bindValue('field_precision', $field['field_precision'] );
$statement->bindValue('field_id', $field['field_id'] );
$statement->execute();
}
//INSERISCO LA SELECT FROM PER IL TASK CHE STO IMPORTANDO
if(!empty($taskObj['select']['from'])) {
$fromValue = $taskObj['select']['from']['from_value'];
$sql = "insert into task_select_from (task_id, from_value)
values (:id_task, :fromValue)";
$statement = $connection->prepare($sql);
$statement->bindValue("id_task", $id_task);
$statement->bindValue('fromValue', $fromValue);
$statement->execute();
}
}
if (isset($taskObj['conditions'])) {
try {
$logger->debug(json_encode($taskObj['conditions']) . "\n\n");
$conditions = $taskObj['conditions'];
foreach ($conditions as $condition)
{
$sql = "insert into task_conditional (id_task, nome, tipo, espressione, id_template, order_id) values (:id_task, :nome, :tipo, :espressione, :id_template, :order_id)";
$statement = $connection->prepare($sql);
$statement->bindValue("id_task", $id_task);
$statement->bindValue("nome", $condition['nome']);
$statement->bindValue("tipo", $condition['tipo']);
$statement->bindValue("espressione", $condition['espressione']);
$statement->bindValue("id_template", $id_checklist);
$statement->bindValue("order_id", $condition['order_id']);
$ok = $statement->execute();
$logger->debug("OK? " . ($ok ? 'true' : 'false'));
}
} catch (\Throwable $ex) {
$logger->debug(">>>>>> ERRORE: " . $ex->getMessage());
}
}
}
}
/**************** INIZIO INTEGRAZIONE DESIGNER *********************/
$sql = "delete from chklst_jflow where chklst_template = $id_checklist";
$statement = $connection->prepare($sql);
$statement->execute();
// $jflow = $isSybot ? $checklist['jflow'] : $checklist['json_flow'];
$jflow = $checklist['jflow'];
if (count($jflow) > 0) {
if(!$isSybot)
{
// Due array associativi ci aiuteranno tra poco nella
// handleACMJflowToSybot()
$tasksById = array();
$tasksByName = array();
foreach ($checklist['tasks'] as $task)
{
if($task['hidden']==1){
continue;
}
$tasksById[$task['id']] = $task;
$tasksByName[$task['name']] = $task;
}
$jflowData = $this->handleACMJflowToSybot($tasksById, $tasksByName, $logger, $jflow["json_flow"], $id_checklist, $taskList, $connection);
$jflow["json_flow"] = $jflowData;
}
if($isSybot) {
//$logger->debug(">>>>>>> json_flow [" . $jflow["json_flow"] . "]");
$j = json_decode($jflow["json_flow"], true);
$jflowRemapper = new JFlowRemapper();
$jflowRemapper->go($j, $logger, $connection);
$nda = $j["nodeDataArray"];
$lda = $j["linkDataArray"];
$newNda = [];
$newLda = [];
$index = 1;
foreach ($taskList as $it) {
$newTaskList[$it["newId"]] = $it;
}
foreach ($nda as $node) {
if($node["category"]=="CommentGroup"){
$newNda[] = $node;
continue;
}
$key = $node["key"];
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$split = explode("_", $key);
$oldIdTemplate = $split[0];
$oldIdTask = $split[1];
$cmdTypeId = $split[2];
$newIdTask = $taskList[$oldIdTask]["newId"];
$newIdTemplate = $id_checklist;
$node["key"] = "[TASK " . $newIdTemplate . "_" . $newIdTask . "_" . $cmdTypeId . "]";
$newTask = $newTaskList[$newIdTask];
$newRightArray = [];
foreach ($node["rightArray"] as $array) {
$portId = $array["portId"];
$split = explode("_", $portId);
$oldTaskId = $split[2];
$index = $split[3];
if(isset($taskList[$oldTaskId])) {
$newTaskId = $taskList[$oldTaskId]["newId"];
$array["portId"] = "port_" . $newIdTemplate . "_" . $newTaskId . "_" . $index;
}
$newRightArray[] = $array;
}
$node["rightArray"] = $newRightArray;
$newNda[] = $node;
}
$j["nodeDataArray"] = $newNda;
foreach ($lda as $key => $link) {
$key = $link["from"];
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$split = explode("_", $key);
$oldIdTemplate = $split[0];
$oldIdTask = $split[1];
$cmdTypeId = $split[2];
$newIdTask = $taskList[$oldIdTask]["newId"];
$newIdTemplate = $id_checklist;
$link["from"] = "[TASK " . $newIdTemplate . "_" . $newIdTask . "_" . $cmdTypeId . "]";
$key = $link["to"];
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$split = explode("_", $key);
$oldIdTemplate = $split[0];
$oldIdTask = $split[1];
$cmdTypeId = $split[2];
$newIdTask = $taskList[$oldIdTask]["newId"];
$newIdTemplate = $id_checklist;
$link["to"] = "[TASK " . $newIdTemplate . "_" . $newIdTask . "_" . $cmdTypeId . "]";
$portId = $link["fromPort"];
$split = explode("_", $portId);
$oldTaskId = $split[2];
$index = $split[3];
if(isset($taskList[$oldTaskId])) {
$newTaskId = $taskList[$oldTaskId]["newId"];
$link["fromPort"] = "port_" . $newIdTemplate . "_" . $newTaskId . "_" . $index;
}
$newLda[] = $link;
}
$j["linkDataArray"] = $newLda;
$jflow["json_flow"] = json_encode($j);
}
$sql = 'insert into chklst_jflow(';
foreach ($jflow as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'id_chklst_jflow') {
continue;
}
/*if ($key == 'task_validate') {
continue;
}*/
$sql .= $key . ',';
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ') values (';
foreach ($jflow as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'id_chklst_jflow') {
continue;
}
$sql .= ":" . $key . ",";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ')';
$statement = $connection->prepare($sql);
//bind parameters
foreach ($jflow as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'id_chklst_jflow') {
continue;
}
if ($key == 'chklst_template') {
$statement->bindValue($key, $id_checklist);
continue;
}
$statement->bindValue($key, $value);
}
$statement->execute();
}
/**************** FINE INTEGRAZIONE DESIGNER *********************/
}
}
if ($path_parts['extension'] == 'sybot' || $path_parts['extension'] == 'acm') {
$res_json->setData(array("id_workflow" => $id_checklist, "workflow" => $checklists, "extension" => $path_parts['extension']));
} else {
$res_json->setData(array("id_workflow" => $id_checklist, "workflow" => $checklist, "extension" => $path_parts['extension']));
}
} catch (\Throwable $ex) {
$logger->debug("ERRORE: " . $ex->getMessage() . " LINE " . $ex->getLine());
$res_json->setStatusCode(400);
return $res_json;
}
return $res_json;
}
private function handleACMJflowToSybot(
$acmTasksById, // id => task acm (secondo l'id nella checklist ACM)
$acmTasksByName, // nome => task acm
$logger,
$jflowRaw,
$idTemplate,
$acmTasksExtraInfo,
$connection) : string
{
$converter = new ACM2SybotJFlowConverter();
return $converter->go($acmTasksById, $acmTasksByName, $logger, $jflowRaw, $idTemplate, $acmTasksExtraInfo, $connection);
}
public function exportChecklist($id_workflow, LoggerInterface $logger, ParameterBagInterface $params)
{
// Temporaneo, testing - default al vecchio exporter
if ($this->USE_NEW_IMPEXP) {
return $this->exportChecklist_new($id_workflow, $logger, $params);
} else {
return $this->exportChecklist_old($id_workflow, $logger, $params);
}
}
public function exportChecklist_new($id_workflow, LoggerInterface $logger, ParameterBagInterface $params)
{
$target_dir = $params->get('kernel.project_dir') . '/public/chklst_exports/';
$basename = 'Sybot_Workflow_Export_' . time();
$export_file = $target_dir . $basename;
if (is_string($id_workflow)) {
$ids_of_workflows_to_export = explode(",", $id_workflow);
$ids_of_workflows_to_export = array_map('trim', $ids_of_workflows_to_export);
$ids_of_workflows_to_export = array_filter($ids_of_workflows_to_export, function($id) {
return is_numeric($id);
});
} else {
$ids_of_workflows_to_export = [ $id_workflow ];
}
try {
$exporter = ImpExp\ImpExp::createExporter($this->getDoctrine()->getManager()->getConnection(), $logger);
foreach ($ids_of_workflows_to_export as $id) {
$exporter->addWorkflowById($id);
}
$exporter->toFile($export_file);
} catch (\Throwable $ex) {
$logger->debug("ERRORE: " . $ex->getMessage() . " LINE " . $ex->getLine() . " FILE " . $ex->getFile() . " TRACE " . $ex->getTraceAsString());
throw $ex;
}
return $basename . '.sybot';
}
public function exportChecklist_old($id_workflow, LoggerInterface $logger, ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$res_json = new JsonResponse();
$target_dir = $params->get('kernel.project_dir') . '/public/chklst_exports/';
if (isset($id_workflow)) {
$idChecklist = $id_workflow;
} else {
$idChecklist = 'nn';
}
if ($idChecklist == 'nn') //ESPORTO TUTTE LE CHECKLIST
{
$logger->debug("[exportChecklistAction] id Checklist == nn");
$sql = "select *
from chklst_template
where hidden = 0";
$statement = $connection->prepare($sql);
$statement->execute();
$checklists['workflow'] = $statement->fetchAll(); //ARRAY CON TUTTE LE CHECKLIST
} else //ESPORTO LA SINGOLA CHECKLIST PASSATA IN INPUT
{
$logger->debug("[exportChecklistAction] id Checklist <> nn");
$sql = "select *
from chklst_template
where id in($idChecklist)
and hidden = 0";
$statement = $connection->prepare($sql);
$statement->execute();
$checklists['workflow'] = $statement->fetchAll(); //ARRAY CON LA SINGOLA CHECKLIST PASSATA IN INPUT
}
$zip = new \ZipArchive();
$zipName = 'Sybot_Workflow_Export_' . time() . ".zip";
$zip->open($target_dir . $zipName, \ZipArchive::CREATE);
$wflTemplates = $checklists['workflow'];
for ($i = 0; $i < count($wflTemplates); $i++) {
$currentTemplate = $wflTemplates[$i];
$currentTemplate["template"] = $currentTemplate;
$checklist_name = $currentTemplate['name'];
$FileName = "Sybot_Workflow_" . $checklist_name . ".xml";
// INIZIO GESTIONE RUOLI CHECKLIST
$sql = "select r.description_roles
from chklst_template_role cr join roles r on cr.id_roles = r.id_roles
where id_template=" . $currentTemplate['id'];
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$checklist_roles = $stmt->fetchAll();
$currentTemplate['roles'][] = $checklist_roles;
// FINE GESTIONE RUOLI CHECKLIST
// RECUPERO I PARAMETRI DELLA CHECKLIST
$sql = "select param_name,
param_value,
param_type,
flag_file
from chklst_template_parameters
where flag_file = 'N'
and chklst_id = " . $currentTemplate['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$parameters = $statement->fetchAll();
$currentTemplate['parameters'][] = $parameters;
// RECUPERO I PARAMETRI FILES DELLA CHECKLIST
$sql = "select param_name,
param_file_name,
param_file_path,
param_file_type
from chklst_template_parameters_files
where chklst_id = " . $currentTemplate['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$parameters_files = $statement->fetchAll();
$currentTemplate['parameters_files'][] = $parameters_files;
// RECUPERO IL WORKFLOW FILE DELLA CHECKLIST
$sql = "select param_name,
param_file_name,
param_file_path,
param_file_type,
wf_description
from chklst_template_workflow_files
where chklst_id = " . $currentTemplate['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$wf_file = $statement->fetchAll();
$currentTemplate['workflow_file'][] = $wf_file;
// RECUPERO I TASK DELLA CHECKLIST
$sql = "select ct.task_order, tsk.*
from pre_chklsttmpl2task ct
join task tsk on tsk.id = ct.task_id
where ct.chklsttmpl_id= " . $currentTemplate['id'] . "
order by ct.task_order";
$statement = $connection->prepare($sql);
$statement->execute();
$tasks = $statement->fetchAll();
// Recupero il disegno del workflow
$sql = "select cj.*
from chklst_jflow cj
where cj.chklst_template = " . $currentTemplate['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$Jflow = $statement->fetchAll();
$currentTemplate['jflow'] = $Jflow;
$taskServers = [];
$taskActions = [];
$taskSelect = [];
$taskSelect['fields'] = [];
$taskSelect['from'] = [];
// $currentTemplate['servers'] = [];
// $currentTemplate['action'] = [];
// $currentTemplate['select'] = [];
//$currentTemplate['tasks'] = [];
$myTask = [];
// RECUPERO I SERVER, LA ACTION, I SELECT FIELDS E LA SELECT FROM DI OGNI TASK
for ($it = 0; $it < count($tasks); $it++) {
$logger->debug("EXPORTO TASK " . json_encode($tasks[$it]));
$myTask[$it]["task"] = $tasks[$it];
//SERVER DEL TASK
$sql = "select ts.server_order, s.*, ts.flag_use_password
from task2server ts join task tsk on tsk.id = ts.task_id
join server s on ts.server_id=s.id
where tsk.id=" . $tasks[$it]['id'] . "
order by ts.server_order";
$statement = $connection->prepare($sql);
$statement->execute();
$servers = $statement->fetchAll();
$myTask[$it]['servers'][] = $servers;
//SELECT FIELDS
$sql = "select s.field_name,
s.field_value,
s.field_type,
s.field_precision,
s.field_id
from task_select_fields s
where s.task_id=" . $tasks[$it]['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$query_fields = $statement->fetchAll();
$myTask[$it]['select']['fields'][] = $query_fields;
//SELECT FROM
$sql = "select f.from_value
from task_select_from f
where f.task_id=" . $tasks[$it]['id'];
$statement = $connection->prepare($sql);
$statement->execute();
$query_from = $statement->fetchAll();
$myTask[$it]['select']['from'] = $query_from;
$sql = "select nome, tipo, espressione, order_id from task_conditional where id_task = :id_task AND id_template = :id_template";
$statement = $connection->prepare($sql);
$statement->bindValue("id_task", $tasks[$it]['id']);
$statement->bindValue("id_template", $currentTemplate['id']);
$statement->execute();
$conditions = $statement->fetchAll();
$myTask[$it]['conditions'][] = $conditions;
}
$currentTemplate['tasks'][] = $myTask;
$xmlEncoder = new XmlEncoder(["xml_root_node_name" => "workflow"]);
$normalizer = new CustomNormalizer();
$serializer = new Serializer(array($normalizer), array($xmlEncoder));
$output = $serializer->serialize($currentTemplate, 'xml');
$file = fopen($target_dir . $FileName, "w");
fwrite($file, $output);
fclose($file);
// $files[]=$target_dir . $FileName;
$zip->addFromString($currentTemplate['name'] . '/xml/' . basename($target_dir . $FileName), file_get_contents($target_dir . $FileName));
unlink($target_dir . $FileName);
// PARAMETER FILE UPLOADATI DELLA CHECKLIST
$target_uploads_dir = $params->get('kernel.project_dir') . '/uploads/' . $currentTemplate['id'] . '/';
$rootPath_uploads = realpath($target_uploads_dir);
$uploaded_files = [];
// Create recursive directory iterator
/** @var SplFileInfo[] $files */
// $logger->debug('rootPath_uploads '.$checklists['checklist'][$i]['id'].': ' . $rootPath_uploads);
if (!$rootPath_uploads) {
$files_uploads = [];
} else {
$files_uploads = new \RecursiveIteratorIterator(
new \RecursiveDirectoryIterator($rootPath_uploads),
\RecursiveIteratorIterator::LEAVES_ONLY
);
//INSERISCO NELLO ZIP I PARAMETER FILES DELLA CHECKLIST
foreach ($files_uploads as $name => $file) {
// Skip directories (they would be added automatically)
if (!$file->isDir()) {
// Get real and relative path for current file
$filePath = $file->getRealPath();
$relativePath = substr($filePath, strlen($rootPath_uploads) + 1);
// Add current file to archive
$zip->addFile($filePath, $currentTemplate['name'] . '/uploads/' . $relativePath);
$uploaded_files[] = $relativePath;
}
}
}
// PARAMETER WORKFLOW FILE UPLOADATI DELLA CHECKLIST
$target_wf_uploads_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $currentTemplate['id'] . '/';
$rootPath_wf_uploads = realpath($target_wf_uploads_dir);
$uploaded_wf_files = [];
// Create recursive directory iterator
/** @var SplFileInfo[] $files */
// $logger->debug('rootPath_wf_uploads '.$checklists['checklist'][$i]['id'].': ' . $rootPath_wf_uploads);
if (!$rootPath_wf_uploads) {
$files_wf_uploads = [];
} else {
$files_wf_uploads = new \RecursiveIteratorIterator(
new \RecursiveDirectoryIterator($rootPath_wf_uploads),
\RecursiveIteratorIterator::LEAVES_ONLY
);
//INSERISCO NELLO ZIP I PARAMETER WF FILES DELLA CHECKLIST
foreach ($files_wf_uploads as $name => $file) {
// Skip directories (they would be added automatically)
if (!$file->isDir()) {
// Get real and relative path for current file
$filePath = $file->getRealPath();
$relativePath = substr($filePath, strlen($rootPath_wf_uploads) + 1);
// Add current file to archive
$zip->addFile($filePath, $currentTemplate['name'] . '/wf_uploads/' . $relativePath);
$uploaded_wf_files[] = $relativePath;
}
}
}
// $logger->debug('files_uploaded json: ' . json_encode($uploaded_files));
//CREO LA STRUTTURA PER IL FILE export.json
$export[] = array(
'workflow' => $currentTemplate['name'],
'xml' => basename($target_dir . $FileName),
'uploads' => $uploaded_files,
'wf_uploads' => $uploaded_wf_files
);
}
$zip->addFromString('export.json', json_encode($export, JSON_PRETTY_PRINT));
$zip->close();
// $logger->debug('export json: ' . json_encode($export));
$cipher = new AES(); // could use AES::MODE_CBC
// keys are null-padded to the closest valid size
// longer than the longest key and it's truncated
//$cipher->setKeyLength(256);
// $cipher->setKey('abcdefghijklmnopqrstuvwxyz123456');
$cipher->setKey($_ENV["KEY_SYBOT"]);
// the IV defaults to all-NULLs if not explicitly defined
//$cipher->setIV(Random::string($cipher->getBlockLength() >> 3));
$cipher->setIV($_ENV["IV_SYBOT"]);
//$size = 10 * 1024;
//$plaintext = str_repeat('a', $size);
//$plaintext = base64_encode(file_get_contents($target_dir . $FileName));
$encrypted_contents = $cipher->encrypt(base64_encode(file_get_contents($target_dir . $zipName)));
$file_export = str_replace('.zip', '.sybot', $zipName);
file_put_contents($target_dir . $file_export, $encrypted_contents);
unlink($target_dir . $zipName); //ELIMINO LO ZIP NON CIFRATO
//$res_json->setData(array('nome_file' => $file_export));
return $file_export;
}
/***************************************************************************************************************************************
***************************************************** INIZIO GESTIONE API ACM **********************************************************
****************************************************************************************************************************************/
public function apiGetChecklistStatusDetailsByRunIdAction(Request $request, LoggerInterface $logger, ParameterBagInterface $params) //ritorna le informazioni dei task circa l'esecuzione passata in input con il parametro run_id (id tabella chklst)
{
$csrfToken = $_GET['csrftoken'];
if (!$this->isCsrfTokenValid('api_get_checklist_status_details_by_id', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
session_write_close();
$res_json = new JsonResponse();
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
//$logger = $this->getLogger();
$db_driver = $params->get('db_driver');
if (isset($_GET["run_id"])) {
$runIdChk = $_GET["run_id"];
} else {
/*Parametro run_id assente*/
$res_json->setData(array('checklist' => 'N/A', 'tasks' => 'N/A', 'code' => 10, 'status' => 'ERROR', 'error_msg' => 'Mandatory run_id parameter not found in request', 'run_id' => -1, 'response_date' => date("Y-m-d H:i:s")));
return $res_json;
}
/*controllo se run_id della checklist esiste*/
$sql = "select c.id run_id, c.from_template_id checklist_id, c.name checklist_name, c.description checklist_description,
c.run_start run_start, c.run_end run_end, c.run_result run_result
from chklst c
where c.id = '$runIdChk'";
$statement = $connection->prepare($sql);
$statement->execute();
$result = $statement->fetchAll();
if (count($result) == 0) {
/*runId della checklist inesistente*/
$res_json->setData(array('checklist' => 'N/A', 'tasks' => 'N/A', 'code' => 20, 'status' => 'ERROR', 'error_msg' => 'Invalid CheckList run_id: ' . $runIdChk . '', 'run_id' => -1, 'response_date' => date("Y-m-d H:i:s")));
return $res_json;
} else {
$runId = $result[0]['run_id'];
$run_checklist_name = $result[0]['checklist_name'];
}
//CONTROLLA SE TOKEN é VALIDO E SE I RUOLI DELLA CHECKLIST CORRISPONDONO
if (isset($_GET["token"])) {
$token = $_GET["token"];
$check_token = $this->checkToken($token, $logger, $params); //0: errore / 1:ok
} else {
$res_json->setData(array('checklist' => 'N/A', 'tasks' => 'N/A', 'code' => 80, 'status' => 'ERROR', 'error_msg' => 'Mandatory token parameter not found in request', 'run_id' => -1, 'response_date' => date("Y-m-d H:i:s")));
return $res_json;
}
$q = "select count(*) cnt
from chklst_template ct
join chklst c on c.from_template_id = ct.id
join chklst_template_role ctr on ctr.id_template = ct.id
join (select ur.id_roles
from user_token ut
join user_profile_roles ur on ut.id_user = ur.id_user
where (ut.token = '$token' or '$token'='010101010101010101010101')
) ur on ctr.id_roles = ur.id_roles
where c.id = $runIdChk";
$statement = $em->getConnection()->prepare($q);
$statement->execute();
$ResulQuery = $statement->fetchAll()[0];
$checklist_role = $ResulQuery['cnt'];
if ($check_token == 0) {
$res_json->setData(array('checklist' => 'N/A', 'tasks' => 'N/A', 'code' => 150, 'status' => 'ERROR', 'error_msg' => 'Invalid Token', 'run_id' => -1, 'response_date' => date("Y-m-d H:i:s")));
return $res_json;
} else if ($check_token != 0 && $checklist_role == 0) {
$res_json->setData(array('checklist' => 'N/A', 'tasks' => 'N/A', 'code' => 130, 'status' => 'ERROR', 'error_msg' => 'Invalid User role', 'run_id' => -1, 'response_date' => date("Y-m-d H:i:s")));
return $res_json;
} else {
try {
$sql = "select ct.name task_name, ct.description task_description, upper(ct.cmd_type) task_type,
ct.cmd_text task_cmd, to_char(ct.run_start, 'dd-mm-yyyy hh24:mi:ss') run_start,
coalesce(to_char(ct.run_end, 'dd-mm-yyyy hh24:mi:ss'), '') run_end, ct.run_result, ct.id, t.id task_id, tsf.param_file_name spool_file
,upper(ct.statement_select) statement_select
from chklst_task ct join task t on ct.task_id=t.id
left join task_spool_files tsf on t.id=tsf.task_id
where ct.chklst_id='$runId'
order by ct.run_start";
$statement = $connection->prepare($sql);
$statement->execute();
$result_task = $statement->fetchAll();
$Directory = $params->get('projectDir') . '/public/spool_file/checklist_status/' . $runId;
if (!is_dir($Directory)) {
$logger->debug(" Creazione Directory " . $Directory);
mkdir($Directory, 0775, true);
}
$FilenameSpool = $Directory . '/' . $run_checklist_name . '_' . $runId . '_details.csv';
$logger->debug('File salvataggio : ' . $FilenameSpool);
$file = fopen($FilenameSpool, "w") or die("Unable to open file!");
fwrite($file, 'task_name;task_description;task_type;task_cmd;run_start;run_end;run_result;id;task_id;spool_file;statement_select' . PHP_EOL);
foreach ($result_task as $row) {
//Write the row to the CSV file.
fputcsv($file, $row, ";");
}
fclose($file);
$data_task_db = [];
foreach ($result_task as $r) {
if ($r['task_type'] == 'DB') {
if ($r['statement_select'] == 'Y') {
$FilenameSpoolJsonQuery = [];
$DirectoryJson = $params->get('projectDir') . '/public/spool_file/' . $r['task_id'] . '/' . $result[0]['run_id'];
if (is_dir($DirectoryJson)) {
$FilenameSpoolJsonQuery = $DirectoryJson . '/spool.json';
$result_db_data_task = json_decode(file_get_contents($FilenameSpoolJsonQuery));
$data_task_db[$r['id']]['data'] = $result_db_data_task;
}
} else {
$data_task_db[$r['id']]['data'] = $r['run_result'];
}
}
}
} catch (\Doctrine\DBAL\Exception\TableNotFoundException $e) {
// $data_task_db[$r['id']]['data']=null;
$logger->debug('Errore nella select dei risultati del task db ' . $e);
}
$res_json->setData(array('checklist' => $result, 'tasks' => $result_task, 'code' => 0, 'status' => 'OK', 'error_msg' => '', 'run_id' => $runIdChk, 'response_date' => date("Y-m-d H:i:s"), 'data_db_task' => $data_task_db));
return $res_json;
}
}
/***************************************************************************************************************************************
***************************************************** FINE GESTIONE API ACM **********************************************************
****************************************************************************************************************************************/
public function checkToken($token, LoggerInterface $logger, ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
$connection = $em->getConnection();
$db_driver = $params->get('db_driver');
//$logger = $this->getLogger();
$q = "select count(*) cnt
from user_token ut
join user_acm u on ut.id_user = u.id
where sysdate() between ut.date_start_validity and ut.date_end_validity
and ut.check_validity = 'Y'
and u.status = 1::bool
and (ut.token = '$token' or '$token'='010101010101010101010101')";
$statement = $em->getConnection()->prepare($q);
$statement->execute();
$ResulQuery = $statement->fetchAll()[0];
$cnt = $ResulQuery['cnt'];
if ($cnt == 0) {
return 0;
} else {
return 1;
}
}
public function checklistLogAction(LoggerInterface $logger)
{
$em = $this->getDoctrine()->getManager();
$logger->debug('[checklistLogAction] Start');
$user = $this->getUser();
$sql = "select * from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$roles = $stmt->fetchAll();
return $this->render(
'logWorkflow.html.twig',
array(
'logged_user' => $user,
'roles' => $roles
)
);
}
public function clonaWorkflowAction(LoggerInterface $logger, ParameterBagInterface $params)
{
$user = $this->getUser();
$userId = $user->getId();
$res_json = new JsonResponse();
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$conn->beginTransaction();
$csrftoken= $_POST["csrftoken"];
if (!$this->isCsrfTokenValid('clonaWorkflow', $csrftoken)) {
throw new \Exception('Csrf token not valid');
}
if (isset($_POST["clona"]) & isset($_POST["workflowName"]) & isset($_POST["newWorkflowName"])) {
$clona = $_POST["clona"];
$workflowName = $_POST["workflowName"];
$newWorkflowName = trim($_POST["newWorkflowName"]);
// -------------------------------------------------------------
// Nel caso in cui il nome è vuoto, genero un nome di default.
// -------------------------------------------------------------
if($newWorkflowName == "") {
$newWorkflowName = $workflowName . "_copy_" . date("Ymd_His");
}
} else {
$res_json->setData(array("error" => true));
return $res_json;
}
if (!preg_match("/^[a-zA-Z0-9_ \-]*$/", $newWorkflowName)) {
throw new \Exception('Il campo può contenere solo lettere non accentate, numeri, underscore, spazi e trattini. La lunghezza massima consentita è di 45 caratteri');
}
$logger->debug("[clonaWorkflowAction] Clonazione Workflow: " . $workflowName . " in " . $newWorkflowName . " Con modalita' " . $clona);
try {
//----------------------------------------------------------
// Inizio copia workflow
//----------------------------------------------------------
$sql = "select * from chklst_template where name = :workflowName ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('workflowName', $workflowName);
$stmt->execute();
$templateToClone = $stmt->fetchAll()[0];
$idTemplate = $templateToClone["id"];
$sql = "insert into chklst_template (";
foreach ($templateToClone as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if($key == "roles") {
break;
}
$sql .= $key . ",";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ") values (";
foreach ($templateToClone as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if($key == "roles") {
break;
}
$sql .= ":" . $key . ",";
}
if (substr($sql, -1) == ",") {
$sql = substr($sql, 0, -1);
}
$sql .= ")";
$statement = $conn->prepare($sql);
foreach ($templateToClone as $key => $value) {
if ($key == 'id') {
continue;
}
//se è acm non ha template e scorro tutta la checklist fino ai ruoli.
if($key == "roles") {
break;
}
if($key == "name") {
$statement->bindValue('name', $newWorkflowName);
continue;
}
if ($key == 'data_creazione') {
if(!$value) {
$statement->bindValue('data_creazione', 'now()');
} else {
$statement->bindValue('data_creazione', $value);
}
continue;
}
if ($key == 'data_modifica') {
$statement->bindValue('data_modifica', 'now()');
continue;
}
if ($key == 'locked') {
if ($value == '') {
$statement->bindValue('locked', 0);
} else {
$statement->bindValue('locked', $value);
}
continue;
}
$statement->bindValue($key, $value);
}
$statement->execute();
$logger->debug("[clonaWorkflowAction] Insert into chklst_template andata a buon fine");
$sql = "select id from chklst_template where name = :workflowName ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('workflowName', $newWorkflowName);
$stmt->execute();
$newIdTemplate = $stmt->fetchAll()[0]["id"];
$sql = "select id_template,id_roles from chklst_template_role where id_template = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateRolesToClone = $stmt->fetchAll();
foreach ($templateRolesToClone as $role) {
$sql = "insert into chklst_template_role ( id_template,id_roles)
values ( :idNewTemplate, :idRole ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idNewTemplate', $newIdTemplate);
$stmt->bindValue('idRole', $role["id_roles"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into chklst_template_role andata a buon fine");
$sql = "select param_name, param_file_name, param_file_path, param_file_type, wf_description
from chklst_template_workflow_files where chklst_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateWFileToClone = $stmt->fetchAll();
foreach ($templateWFileToClone as $wfile) {
$sql = "insert into chklst_template_workflow_files ( chklst_id, param_name, param_file_name,
param_file_path, param_file_type, wf_description )
values (
:chklst_id,
:param_name,
:param_file_name,
:param_file_path,
:param_file_type,
:wf_description
) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklst_id', $newIdTemplate);
$stmt->bindValue('param_name', $wfile['param_name']);
$stmt->bindValue('param_file_name', $wfile['param_file_name']);
$stmt->bindValue('param_file_path', $wfile['param_file_path']);
$stmt->bindValue('param_file_type', $wfile['param_file_type']);
$stmt->bindValue('wf_description', $wfile['wf_description']);
$stmt->execute();
$target_dir = $params->get('kernel.project_dir') . '/public/wf_uploads/' . $newIdTemplate . '/';
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
if ($wfile['param_file_name']) {
$fileToCopy = $wfile['param_file_path'] . $wfile['param_file_name'];
if (trim($fileToCopy, " ") && file_exists(trim($fileToCopy, " ")))
copy($fileToCopy, $target_dir . $wfile['param_file_name']);
}
}
$sql = "select param_name, param_value, param_type, flag_file from chklst_template_parameters where chklst_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateParamsToClone = $stmt->fetchAll();
foreach ($templateParamsToClone as $parameter) {
$sql = "insert into chklst_template_parameters (chklst_id, param_name, param_value, param_type, flag_file)
values ( :chklst_id, :param_name, :param_value, :param_type, :flag_file ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklst_id', $newIdTemplate);
$stmt->bindValue('param_name', $parameter["param_name"]);
$stmt->bindValue('param_value', $parameter["param_value"]);
$stmt->bindValue('param_type', $parameter["param_type"]);
$stmt->bindValue('flag_file', $parameter["flag_file"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into chklst_template_parameters andata a buon fine");
$sql = "select param_name, param_file_name, param_file_path, param_file_type from chklst_template_parameters_files where chklst_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateParamsFilesToClone = $stmt->fetchAll();
//-----------------------------------------------------------
// Clonazione dei parametri di tipo file
//-----------------------------------------------------------
foreach ($templateParamsFilesToClone as $f) {
$sql = "insert into chklst_template_parameters_files ( chklst_id, param_name, param_file_name, param_file_path, param_file_type )
values ( :chklst_id, :param_name, :param_file_name, :param_file_path, :param_file_type ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklst_id', $newIdTemplate);
$stmt->bindValue('param_name', $f["param_name"]);
$stmt->bindValue('param_file_name', $f["param_file_name"]);
$stmt->bindValue('param_file_path', $f["param_file_path"]);
$stmt->bindValue('param_file_type', $f["param_file_type"]);
$stmt->execute();
$target_dir = $params->get('kernel.project_dir') . '/uploads/' . $newIdTemplate . '/';
if (!file_exists($target_dir)) {
mkdir($target_dir, 0777, true);
}
$fileToCopy = $f['param_file_path'] . $f['param_file_name'];
if ($f['param_file_name']) {
if (trim($fileToCopy, " ") && file_exists(trim($fileToCopy, " ")))
copy($fileToCopy, $target_dir . $f['param_file_name']);
}
$logger->debug("[clonaWorkflowAction] Clonazione parametri file andata a buon fine");
}
switch ($clona) {
case 'clonatutto':
//--------------------------------------------------------
// inizio copia task
//--------------------------------------------------------
// prendo tutti i task del vecchio workflow
$sql = "select task_id from pre_chklsttmpl2task where chklsttmpl_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$tasks = $stmt->fetchAll();
$mapTasksId = [];
$mapTasksId[""] = "";
$mapTasksName = [];
$mapTasksName[""] = "";
if (count($tasks) > 0) {
//inserisco i task e mappo i vecchi id con i nuovi id
$arrayTasks = array_column($tasks, 'task_id');
$inQuery = implode(',', array_fill(0, count($arrayTasks), '?'));
$sql = "select * from task where id in (" . $inQuery . ") ";
$stmt = $conn->prepare($sql);
foreach ($arrayTasks as $k => $id)
$stmt->bindValue(($k + 1), $id);
//$stmt->bindValue('tasks', array(array_column($tasks,'task_id')), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
$stmt->execute();
$allTasks = $stmt->fetchAll();
$i = 1;
foreach ($allTasks as $task) {
// Quick fix!
// TODO: Ci sarebbe da rivedere tutta la logica a valle ma per il momento risolviamo così
// $newTaskName = $newWorkflowName . "_" . $i;
$newTaskName = $task['name'];
$i += 1;
$sql = "insert into task (";
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
$sql .= $key . ",";
}
$sql = substr($sql, 0, -1);
$sql .= ") values (";
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
$sql .= ":" . $key . ",";
}
$sql = substr($sql, 0, -1);
$sql .= ")";
$sql .= " on conflict(id) do update SET ";
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
$sql .= $key . "=:" . $key . ",";
}
$sql = substr($sql, 0, -1);
$statement = $conn->prepare($sql);
foreach ($task as $key => $value) {
if ($key == '@key') {
continue;
}
if ($key == 'servers') {
break;
}
if ($key == 'id') {
continue;
}
if ($key == 'task_order') {
continue;
}
if ($key == 'cmd_max_retry') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'cmd_time_retry') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'creato_da') {
$intV = (int)$value;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'modificato_da') {
$intV = (int)$userId;
$statement->bindValue($key, $intV);
continue;
}
if ($key == 'data_modifica') {
$dt = date('Y-m-d H:i:s');
$statement->bindValue($key, $dt);
continue;
}
if($key == "name") {
$statement->bindValue($key, $newTaskName);
continue;
}
$statement->bindValue($key, $value);
}
$statement->execute();
$newTaskId = $conn->lastInsertId();
//prendo il nuovo id e lo mappo col vecchio
$mapTasksId[$task["id"]] = $newTaskId;
// Non è più necessario rimappare i nomi
// $mapTasksName[$task["name"]] = $newTaskName;
// Quick fix!
// TODO: Ci sarebbe da rivedere tutta la logica a valle ma per il momento risolviamo così
$mapTasksName[$task["name"]] = $task["name"];
}
$logger->debug("[clonaWorkflowAction] Insert into task andata a buon fine");
$columnToCheck = array_keys($allTasks[0]);
//per ogni task
foreach ($allTasks as $t) {
//per tutte le colonne del task
foreach ($columnToCheck as $col) {
$value = $t[$col];
//per tutti i valori da cercare
foreach ($mapTasksName as $findKey => $findValue) {
if (!$findKey)
continue;
if (strpos($value, $findKey)) {
//se lo trovo faccio update
$newVal = str_replace($findKey, $findValue, $value);
$sql = "update task set " . $col . " = :nextVal where id = :id ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('nextVal', $newVal);
$stmt->bindValue('id', $mapTasksId[$t["id"]]);
$stmt->execute();
}
}
}
}
$sql = "select task_id, server_id, server_order, flag_use_password from task2server where task_id in (" . $inQuery . ") ";
$stmt = $conn->prepare($sql);
foreach ($arrayTasks as $k => $id)
$stmt->bindValue(($k + 1), $id);
$stmt->execute();
$task2Server = $stmt->fetchAll();
foreach ($task2Server as $s) {
$sql = "insert into task2server (task_id, server_id, server_order, flag_use_password)
values ( :task_id, :server_id, :server_order, :flag_use_password ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('task_id', $mapTasksId[$s["task_id"]]);
$stmt->bindValue('server_id', $s["server_id"]);
$stmt->bindValue('server_order', $s["server_order"]);
$stmt->bindValue('flag_use_password', $s["flag_use_password"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into task2server andata a buon fine");
$sql = "select task_id, field_name, field_value, field_type,
field_precision, field_id
from task_select_fields where task_id in (" . $inQuery . ") ";
$stmt = $conn->prepare($sql);
foreach ($arrayTasks as $k => $id)
$stmt->bindValue(($k + 1), $id);
$stmt->execute();
$taskSelectFields = $stmt->fetchAll();
foreach ($taskSelectFields as $field) {
$sql = "insert into task_select_fields ( task_id, field_name, field_value, field_type, field_precision, field_id )
values ( :task_id, :field_name, :field_value, :field_type, :field_precision, :field_id ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('task_id', $mapTasksId[$field["task_id"]]);
$stmt->bindValue('field_name', $field["field_name"]);
$stmt->bindValue('field_value', $field["field_value"]);
$stmt->bindValue('field_type', $field["field_type"]);
$stmt->bindValue('field_precision', $field["field_precision"]);
$stmt->bindValue('field_id', $field["field_id"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into task_select_fields andata a buon fine");
$sql = "select task_id, from_value from task_select_from where task_id in (" . $inQuery . ") ";
$stmt = $conn->prepare($sql);
foreach ($arrayTasks as $k => $id)
$stmt->bindValue(($k + 1), $id);
$stmt->execute();
$taskSelectFrom = $stmt->fetchAll();
foreach ($taskSelectFrom as $selectFrom) {
$sql = "insert into task_select_from ( task_id, from_value )
values ( :task_id, :from_value )";
$stmt = $conn->prepare($sql);
$stmt->bindValue('task_id', $mapTasksId[$selectFrom["task_id"]]);
$stmt->bindValue('from_value', $selectFrom["from_value"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into task_select_from andata a buon fine");
} // fine if count task > 0
//clono la tabella task_conditional
$sql = "select id_task, nome, tipo, espressione, id_template from task_conditional where id_template = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$oldConditions = $stmt->fetchAll();
foreach ($oldConditions as $condition) {
if (!array_key_exists($condition["id_task"], $mapTasksId)) {
continue;
}
//make insert with new idtemplate and new tasks ids
$sql = "insert into task_conditional (id_task, nome, tipo, espressione, id_template) values (:idTask, :nome, :tipo, :espressione, :idTemplate) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTask', $mapTasksId[$condition["id_task"]]);
$stmt->bindValue('nome', $condition["nome"]);
$stmt->bindValue('tipo', $condition["tipo"]);
$stmt->bindValue('espressione', $condition["espressione"]);
$stmt->bindValue('idTemplate', $newIdTemplate);
$stmt->execute();
}
// clono tutto, workflow, parametri, task. ai task metto il suffisso numerico e come prefisso il nome del nuovo workflow.
$sql = "select param_name, param_file_name, param_file_path, param_file_type from task_spool_files where task_id in (" . $inQuery . ") ";
$stmt = $conn->prepare($sql);
foreach ($arrayTasks as $k => $id)
$stmt->bindValue(($k + 1), $id);
$stmt->execute();
$taskSpoolFiles = $stmt->fetchAll();
foreach ($taskSpoolFiles as $spoolFile) {
foreach ($mapTasksName as $taskKey => $taskName) {
if ($taskName)
if (str_contains($spoolFile["param_name"], $taskKey)) {
$spoolTaskName = str_replace(".queryspool.file", "", $spoolFile["param_name"]);
$pName = $taskName . ".queryspool.file";
$pFileName =
str_replace($spoolTaskName, $mapTasksName[$spoolTaskName], $spoolFile["param_file_name"]);
$spoolPath = pathinfo($spoolFile["param_file_path"]);
$pFilePath = $spoolPath["dirname"] . "/" . $mapTasksId[$spoolPath['basename']] . "/";
$sql = "insert into task_spool_files (task_id, param_name, param_file_name, param_file_path, param_file_type)
values (:task_id, :param_name, :param_file_name, :param_file_path, :param_file_type ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('task_id', $mapTasksId[$spoolPath['basename']]);
$stmt->bindValue('param_name', $pName);
$stmt->bindValue('param_file_name', $pFileName);
$stmt->bindValue('param_file_path', $pFilePath);
$stmt->bindValue('param_file_type', $spoolFile["param_file_type"]);
$stmt->execute();
break;
}
}
}
$logger->debug("[clonaWorkflowAction] Insert into task_spool_files andata a buon fine");
$sql = "select chklsttmpl_id, task_id, task_order, ko_task,
slave, flag_bitmask, run_bitmask
from pre_chklsttmpl2task where chklsttmpl_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$workflow2Task = $stmt->fetchAll();
//-----------------------------------------------------------
// Aggiungiamo i task clonati al nuovo workflow.
//-----------------------------------------------------------
foreach ($workflow2Task as $w2t) {
//---------------------------------------------------------------------------
// normalizza il campo slave, nel db è di tipo boolean
// bindValue() trasforma false in stringa vuota,
// causando errori in fase di insert quindi lo convertiamo in 1 o 0
//---------------------------------------------------------------------------
$slave = $w2t['slave'] ? 1 : 0;
$sql = "insert into pre_chklsttmpl2task ( chklsttmpl_id, task_id, task_order, ko_task,
slave, flag_bitmask, run_bitmask )
values ( :chklsttmpl_id, :task_id, :task_order, :ko_task,
:slave, :flag_bitmask, :run_bitmask ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklsttmpl_id', $newIdTemplate);
$stmt->bindValue('task_id', $mapTasksId[$w2t["task_id"]]);
$stmt->bindValue('task_order', $w2t["task_order"]);
$stmt->bindValue('ko_task', $w2t["ko_task"]);
$stmt->bindValue('slave', $slave);
$stmt->bindValue('flag_bitmask', $w2t["flag_bitmask"]);
$stmt->bindValue('run_bitmask', $w2t["run_bitmask"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into pre_chklsttmpl2task andata a buon fine");
//se clono tutto devo modificare i nomi dei task e gli id
$sql = "select chklst_template, json_flow,task_jorder, task_jump, task_action_ok,
task_action_ko, 'N' task_validate
from chklst_jflow where chklst_template = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateJFlowToClone = $stmt->fetchAll()[0];
if ($templateJFlowToClone["json_flow"] != "") {
$arrayJFlow = json_decode($templateJFlowToClone["json_flow"]);
foreach ($arrayJFlow->{"nodeDataArray"} as $nodeDataArray) {
if ($nodeDataArray->category == 'CommentGroup') {
continue;
}
$taskIdToReplace = str_replace("]", "", explode("_", $nodeDataArray->{"key"})[1]);
if (array_key_exists($taskIdToReplace, $mapTasksId))
$nodeDataArray->{"key"} = str_replace($idTemplate . "_" . $taskIdToReplace, $newIdTemplate . "_" . $mapTasksId[$taskIdToReplace], $nodeDataArray->{"key"});
if (array_key_exists($nodeDataArray->{"text"}, $mapTasksName))
$nodeDataArray->{"text"} = $mapTasksName[$nodeDataArray->{"text"}];
}
foreach ($arrayJFlow->{"linkDataArray"} as $linkDataArray) {
$taskIdToReplaceFrom = str_replace("]", "", explode("_", $linkDataArray->{"from"})[1]);
$taskIdToReplaceTo = str_replace("]", "", explode("_", $linkDataArray->{"to"})[1]);
if (array_key_exists($taskIdToReplaceFrom, $mapTasksId))
$linkDataArray->{"from"} = str_replace($idTemplate . "_" . $taskIdToReplaceFrom, $newIdTemplate . "_" . $mapTasksId[$taskIdToReplaceFrom], $linkDataArray->{"from"});
if (array_key_exists($taskIdToReplaceTo, $mapTasksId))
$linkDataArray->{"to"} = str_replace($idTemplate . "_" . $taskIdToReplaceTo, $newIdTemplate . "_" . $mapTasksId[$taskIdToReplaceTo], $linkDataArray->{"to"});
}
$stringJFlow = json_encode($arrayJFlow);
$sql = "insert into chklst_jflow ( chklst_template, json_flow, task_jorder, task_jump, task_action_ok,
task_action_ko, task_validate )
values ( :chklst_template, :json_flow, :task_jorder, :task_jump, :task_action_ok,
:task_action_ko, 'N' ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklst_template', $newIdTemplate);
$stmt->bindValue('json_flow', $stringJFlow);
$newTaskOrder = $this->getTaskSplit($templateJFlowToClone["task_jorder"], $mapTasksId + $mapTasksName);
$stmt->bindValue('task_jorder', $newTaskOrder);
$stmt->bindValue('task_jump', $this->getTaskSplit($templateJFlowToClone["task_jump"], $mapTasksId + $mapTasksName));
$stmt->bindValue('task_action_ok', $this->getTaskSplit($templateJFlowToClone["task_action_ok"], $mapTasksId + $mapTasksName));
$stmt->bindValue('task_action_ko', $this->getTaskSplit($templateJFlowToClone["task_action_ko"], $mapTasksId + $mapTasksName));
$stmt->execute();
}
break;
case 'clonasoloworkflow':
//--------------------------------------------------------------
// Clona e mantieni i task:
// - clono l’intero workflow con parametri e file
// - i task restano quelli dell’originale
// - copio il JSON del disegno
//-------------------------------------------------------------
//clono la tabella task_conditional
$sql = "select id_task, nome, tipo, espressione, id_template from task_conditional where id_template = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$oldConditions = $stmt->fetchAll();
foreach ($oldConditions as $condition) {
$sql = "insert into task_conditional (id_task, nome, tipo, espressione, id_template) values (:idTask, :nome, :tipo, :espressione, :idTemplate) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTask', $condition["id_task"]);
$stmt->bindValue('nome', $condition["nome"]);
$stmt->bindValue('tipo', $condition["tipo"]);
$stmt->bindValue('espressione', $condition["espressione"]);
$stmt->bindValue('idTemplate', $newIdTemplate);
$stmt->execute();
}
$sql = "select chklsttmpl_id, task_id, task_order, ko_task,
slave, flag_bitmask, run_bitmask
from pre_chklsttmpl2task where chklsttmpl_id = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$workflow2Task = $stmt->fetchAll();
foreach ($workflow2Task as $w2t) {
//---------------------------------------------------------------------------
// normalizza il campo slave, nel db è di tipo boolean
// bindValue() trasforma false in stringa vuota,
// causando errori in fase di insert quindi lo convertiamo in 1 o 0
//---------------------------------------------------------------------------
$slave = $w2t['slave'] ? 1 : 0;
$sql = "insert into pre_chklsttmpl2task ( chklsttmpl_id, task_id, task_order, ko_task,
slave, flag_bitmask, run_bitmask )
values ( :chklsttmpl_id, :task_id, :task_order, :ko_task,
:slave, :flag_bitmask, :run_bitmask ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklsttmpl_id', $newIdTemplate);
$stmt->bindValue('task_id', $w2t["task_id"]);
$stmt->bindValue('task_order', $w2t["task_order"]);
$stmt->bindValue('ko_task', $w2t["ko_task"]);
$stmt->bindValue('slave', $slave);
$stmt->bindValue('flag_bitmask', $w2t["flag_bitmask"]);
$stmt->bindValue('run_bitmask', $w2t["run_bitmask"]);
$stmt->execute();
}
$logger->debug("[clonaWorkflowAction] Insert into pre_chklsttmpl2task andata a buon fine");
$sql = "select chklst_template, json_flow,task_jorder, task_jump, task_action_ok,
task_action_ko, 'N' task_validate
from chklst_jflow where chklst_template = :idTemplate ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$templateJFlowToClone = $stmt->fetchAll()[0];
if ($templateJFlowToClone["json_flow"] != "") {
$arrayJFlow = json_decode($templateJFlowToClone["json_flow"]);
foreach ($arrayJFlow->{"nodeDataArray"} as $nodeDataArray) {
if ($nodeDataArray->category == 'CommentGroup') {
continue;
}
$splitKey = explode("_", explode(" ", $nodeDataArray->{"key"})[1]);
$taskIdToMantain = str_replace("]", "", $splitKey[1]);
$nodeDataArray->{"key"} = str_replace($idTemplate . "_" . $taskIdToMantain, $newIdTemplate . "_" . $taskIdToMantain, $nodeDataArray->{"key"});
}
foreach ($arrayJFlow->{"linkDataArray"} as $linkDataArray) {
$splitKeyFrom = explode("_", explode(" ", $linkDataArray->{"from"})[1]);
$taskIdToMantainFrom = str_replace("]", "", $splitKeyFrom[1]);
$splitKeyTo = explode("_", explode(" ", $linkDataArray->{"to"})[1]);
$taskIdToMantainTo = str_replace("]", "", $splitKeyTo[1]);
$linkDataArray->{"from"} = str_replace($idTemplate . "_" . $taskIdToMantainFrom, $newIdTemplate . "_" . $taskIdToMantainFrom, $linkDataArray->{"from"});
$linkDataArray->{"to"} = str_replace($idTemplate . "_" . $taskIdToMantainTo, $newIdTemplate . "_" . $taskIdToMantainTo, $linkDataArray->{"to"});
}
$stringJFlow = json_encode($arrayJFlow);
$sql = "insert into chklst_jflow ( chklst_template, json_flow, task_jorder, task_jump, task_action_ok,
task_action_ko, task_validate )
values ( :chklst_template, :json_flow, :task_jorder, :task_jump, :task_action_ok,
:task_action_ko, 'N' ) ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('chklst_template', $newIdTemplate);
$stmt->bindValue('json_flow', $stringJFlow);
$stmt->bindValue('task_jorder', $templateJFlowToClone["task_jorder"]);
$stmt->bindValue('task_jump', $templateJFlowToClone["task_jump"]);
$stmt->bindValue('task_action_ok', $templateJFlowToClone["task_action_ok"]);
$stmt->bindValue('task_action_ko', $templateJFlowToClone["task_action_ko"]);
$stmt->execute();
}
break;
default:
break;
}
$logger->debug("[clonaWorkflowAction] Commit");
$conn->commit();
return $this->json(['error' => false]);
} catch (\Exception $e) {
$conn->rollBack();
throw $e;
}
}
function getTaskSplit($incomeTask, $mapTasks): string
{
if ($incomeTask == "")
return "";
$newTaskOrderSplitPipe = explode("|", substr($incomeTask, 0, -1));
$newTaskOrderPipe = [];
foreach ($newTaskOrderSplitPipe as $splitPipe) {
$newTaskOrderSplitComma = explode(";", $splitPipe);
$newTaskComma = [];
foreach ($newTaskOrderSplitComma as $splitComma) {
if (array_key_exists($splitComma, $mapTasks))
$newTaskComma[] = $mapTasks[$splitComma];
else
$newTaskComma[] = $splitComma;
}
$newTaskOrderPipe[] = implode(";", $newTaskComma);
}
$newTaskOrder = implode("|", $newTaskOrderPipe);
$newTaskOrder .= "|";
return $newTaskOrder;
}
public function getWorkflowIdByNameAction(Request $request, LoggerInterface $logger)
{
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('checklistWorkflowIdByName', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$wName = $_POST['workflowName'];
$sql = "select id
from chklst_template where name = :workflowName ";
$stmt = $conn->prepare($sql);
$stmt->bindValue('workflowName', $wName);
$stmt->execute();
$wId = $stmt->fetchAll()[0]["id"];
return $this->json(['data' => ['workflowId' => $wId]]);
}
function checkCorrelatedWorkflowNamesByTaskAction(Request $request, LoggerInterface $logger)
{
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('checkCorrelatedWorkflowNamesByTask', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$taskId = $_POST['taskId'];
$sql = "
select
t.id,
t.name,
pct.chklsttmpl_id,
ct.name
from
task t
join pre_chklsttmpl2task pct on
(t.id = pct.task_id)
join chklst_template ct on
(pct.chklsttmpl_id = ct.id)
where
t.id = :taskId
";
$stmt = $conn->prepare($sql);
$stmt->bindValue('taskId', $taskId);
$stmt->execute();
$workflowRelated = $stmt->fetchAll();
if (count($workflowRelated) < 2) {
return $this->json(['data' => ['workflowNames' => ""]]);
}
$workflowNames = [];
foreach ($workflowRelated as $val) {
$workflowNames[] = $val["name"];
}
return $this->json(['data' => ['workflowNames' => $workflowNames]]);
}
//funzione che ritorna il prossimo id workflow per la creazione di un nuovo workflow
public function nextValNewWorkflowAction(Request $request, LoggerInterface $logger)
{
$logger->debug("[NextValNewWorkflow] start");
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$sql = "select nextval('chklst_template_id_seq') as id";
$statement = $conn->prepare($sql);
$statement->execute();
$nextval = $statement->fetchAll();
$logger->debug("[NextValNewWorkflow] end");
return $this->json(["id" => $nextval[0]["id"]]);
}
private function getParametersByWorkflowId($wflId)
{
$sqlParameters = "select chklst_id, param_name, param_value, param_type, flag_file from chklst_template_parameters where chklst_id = :wflId";
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$stmt = $conn->prepare($sqlParameters);
$stmt->bindValue('wflId', $wflId);
$stmt->execute();
$sqlParameters = $stmt->fetchAll();
if (empty($sqlParameters)) {
$sqlParameters = [];
}
return $sqlParameters;
}
public function checkWflLockStatusAction(Request $request, LoggerInterface $logger)
{
// $logger->debug("[checkWflLockStatusAction] start");
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('checkWflLockStatus', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$session = $request->getSession();
$sessionId = $session->getId();
$idTemplate = $request->request->get('idTemplate');
// $logger->debug("[checkWflLockStatusAction] idTemplate: " . $idTemplate);
if (empty($idTemplate)) {
// $logger->debug("[checkWflLockStatusAction] idTemplate is empty");
return $this->json(["status" => 0, "username" => ""]);
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$sql = "select ct.locked, ct.lock_session_user_id, ct.modificato_da, ua.username
from chklst_template ct join user_acm ua on ua.id = ct.modificato_da
where ct.id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$lockStatus = $stmt->fetchAll();
// $logger->debug("[checkWflLockStatusAction] end");
if (empty($lockStatus)) {
return $this->json(["status" => 0, "locked_by" => ""]);
}
$userId = $this->getUser()->getId();
$modificatoDa = $lockStatus[0]["modificato_da"];
$lockSessionUserId = $lockStatus[0]["lock_session_user_id"];
$message = "";
if($userId == $modificatoDa && $lockSessionUserId != $sessionId) {
$message = "stesso utente con una sessione diversa";
}
return $this->json(["status" => $lockStatus[0]["locked"], "locked_by" => $lockStatus[0]["username"], "message" => $message]);
}
public function lockWflAction(Request $request, LoggerInterface $logger)
{
// $logger->debug("[lockWflAction] start");
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('lockWfl', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$session = $request->getSession();
$sessionId = $session->getId();
$idTemplate = $request->request->get('idTemplate');
if (empty($idTemplate)) {
// $logger->debug("[lockWflAction] idTemplate is empty");
return $this->json(["status" => false]);
}
// $logger->debug("[lockWflAction] idTemplate: " . $idTemplate);
$sql = "select ct.id
from chklst_template ct
where ct.id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$currentTemplate = $stmt->fetchAll();
if (empty($currentTemplate) || count($currentTemplate) > 1) {
// $logger->debug("[lockWflAction] Not Found or more than one wfl");
return $this->json(["status" => false]);
}
$sql = "update chklst_template set locked = 1, modificato_da = :idUser, lock_session_user_id = :sessionId where id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->bindValue('idUser', $this->getUser()->getId());
$stmt->bindValue('sessionId', $sessionId);
$stmt->execute();
// $logger->debug("[lockWflAction] end");
return $this->json(["status" => true]);
}
public function unlockWflAction(Request $request, LoggerInterface $logger)
{
$user = $this->getUser();
$userId = $user->getId();
$session = $request->getSession();
$sessionId = $session->getId();
$logger->debug("[unlockWflAction] start");
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('unlockWfl', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$idTemplate = $request->request->get('idTemplate');
if (empty($idTemplate)) {
$logger->debug("[unlockWflAction] idTemplate is empty");
return $this->json(["status" => false]);
}
$logger->debug("[unlockWflAction] idTemplate: " . $idTemplate);
$sql = "select ct.id
from chklst_template ct
where ct.id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->execute();
$currentTemplate = $stmt->fetchAll();
if (empty($currentTemplate) || count($currentTemplate) > 1) {
$logger->debug("[lockWflAction] Not Found or more than one wfl");
return $this->json(["status" => false]);
}
$sql='select ct.modificato_da, ct.data_modifica, ct.lock_session_user_id
from chklst_template ct where ct.id = :idTemplate';
$statement=$conn -> prepare($sql);
$statement -> bindValue('idTemplate',$idTemplate);
$statement -> execute();
$infoTemplateLock= $statement->fetchAll()[0];
$id_user_lock= $infoTemplateLock['modificato_da'];
$data_lock= $infoTemplateLock['data_modifica'];
$id_session_lock= $infoTemplateLock['lock_session_user_id'];
$sql='insert into wfl_unlock_history (id_user_lock,data_lock,id_session_lock,id_user_unlock,id_session_unlock,id_wfl_template) values
(:id_user_lock,:data_lock,:id_session_lock,:id_user_unlock, :id_session_unlock, :idTemplate)';
$statement= $conn -> prepare($sql);
$statement -> bindValue('id_user_lock',$id_user_lock);
$statement -> bindValue('data_lock',$data_lock);
$statement -> bindValue('id_session_lock',$id_session_lock);
$statement -> bindValue('id_user_unlock',$userId);
$statement -> bindValue('id_session_unlock',$sessionId);
$statement -> bindValue('idTemplate',$idTemplate);
$statement -> execute();
$sql = "update chklst_template set locked = 0, modificato_da = :idUser, lock_session_user_id = '' where id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $idTemplate);
$stmt->bindValue('idUser', $this->getUser()->getId());
$stmt->execute();
$logger->debug("[unlockWflAction] end");
return $this->json(["status" => true]);
}
public function stopWflByRunIdAction(Request $request, LoggerInterface $logger, WorkflowAuditLoggerFactory $auditLoggerFactory)
{
$logger->debug("[stopWflByRunIdAction] start");
// Il controllo CSRF resta solo qui (punto di ingresso WEB)
$csrfToken = $_POST['csrftoken'] ?? '';
if (!$this->isCsrfTokenValid('stopWflByRunId', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$runId = $request->request->get('runId');
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
// Chiamata al metodo statico "sporco"
$result = self::_stopWflByRunId($runId, $conn, $logger, $auditLoggerFactory);
return $this->json($result);
}
public static function _stopWflByRunId($runId, $conn, $logger, $auditLoggerFactory)
{
if (empty($runId)) {
$logger->debug("[doStopLogic] runId is empty");
return ["stopped" => false];
}
try {
$sql = "SELECT id, run_user_id, from_template_id, name FROM chklst WHERE id = :runId";
$stmt = $conn->prepare($sql);
$stmt->bindValue('runId', $runId);
$stmt->execute();
$info = $stmt->fetch();
if (!$info) {
$logger->debug("[doStopLogic] Record non trovato per ID: " . $runId);
return ["stopped" => false, "error" => "not_found"];
}
$sql = "SELECT pid FROM chklst WHERE id = :run_id";
$stmt = $conn->prepare($sql);
$stmt->bindValue('run_id', $runId);
$stmt->execute();
$row = $stmt->fetch();
if ($row && $row['pid'] != 0) {
$pid = $row['pid'];
exec("kill -9 $pid");
}
// 3. Update task
$sql = "UPDATE chklst_task SET run_result='STOPPED', run_end=NOW() WHERE chklst_id=:run_id";
$stmt = $conn->prepare($sql);
$stmt->bindValue('run_id', $runId);
$stmt->execute();
// 4. Update testata
$sql = "UPDATE chklst SET run_end = SYSDATE(), run_result = 'STOPPED',
run_start = COALESCE(run_start, SYSDATE()) WHERE id = :runId";
$stmt = $conn->prepare($sql);
$stmt->bindValue('runId', $runId);
$stmt->execute();
// 5. Audit Log
$auditLogger = $auditLoggerFactory->createWorkflow(
$info['name'],
$info['run_user_id'],
$info['from_template_id'],
$runId
);
$auditLogger->log(WorkflowAuditLogger::LEVEL_INFO, WorkflowAuditLogger::EVENT_WFL_STOP, [
'status' => WorkflowAuditLogger::STATUS_STOPPED
]);
return ["stopped" => true];
} catch (\Throwable $ex) {
$logger->error("[doStopLogic] Errore critico: " . $ex->getMessage());
return ["stopped" => false, "exception" => $ex->getMessage()];
}
}
/*
public function stopWflByRunIdAction(Request $request, LoggerInterface $logger,WorkflowAuditLoggerFactory $auditLoggerFactory)
{
$logger->debug("[stopWflByRunIdAction] start");
$csrfToken = $_POST['csrftoken'];
if (!$this->isCsrfTokenValid('stopWflByRunId', $csrfToken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$runId = $request->request->get('runId');
if (empty($runId)) {
$logger->debug("[stopWflByRunIdAction] runId is empty");
return $this->json(["stopped" => false]);
}
$logger->debug("[stopWflByRunIdAction] idTemplate: " . $runId);
try {
$sql = "
SELECT
c.id,
c.run_user_id,
c.from_template_id,
c.name
FROM chklst c
where c.id= :runId
";
$stmt = $conn->prepare($sql);
$stmt->bindValue('runId', $runId);
$stmt->execute();
$info = $stmt->fetch();
$sql = "select pid from chklst where id = :run_id";
$stmt = $conn->prepare($sql);
$stmt->bindValue('run_id', $runId);
$stmt->execute();
$row = $stmt->fetch();
if ($row) {
$pid = $row['pid'];
if ($pid != 0) {
exec("kill -9 $pid");
}
} else {
$logger->debug("Attenzione: pid non trovato");
}
$sql = "update chklst_task set run_result='STOPPED', run_end=now() where chklst_id=:run_id";
$stmt = $conn->prepare($sql);
$stmt->bindValue('run_id', $runId);
$stmt->execute();
} catch (Exception $ex) {
$logger->debug("[stopWflByRunIdAction] Update failed:" . $ex->getMessage());
return $this->json(["stopped" => false, "exception" => $ex->getMessage()]);
}
try {
$sql = "
update chklst set run_end = sysdate(),
run_result = 'STOPPED',
run_start = coalesce(run_start, sysdate())
where id = :runId";
$stmt = $conn->prepare($sql);
$stmt->bindValue('runId', $runId);
$stmt->execute();
$logger->debug("[stopWflByRunIdAction] end");
$auditLogger = $auditLoggerFactory->createWorkflow(
$info['name'],
$info['run_user_id'],
$info['from_template_id'],
$runId
);
$auditLogger->log(WorkflowAuditLogger::LEVEL_INFO,WorkflowAuditLogger::EVENT_WFL_STOP,[
'status' => WorkflowAuditLogger::STATUS_STOPPED
]);
return $this->json(["stopped" => true]);
} catch (\Throwable $th) {
$logger->debug("[stopWflByRunIdAction] Update failed");
return $this->json(["stopped" => false]);
}
}
*/
function selectExportWorkflowDocuemntAction( Request $request, LoggerInterface $logger,ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$res_json = new JsonResponse();
$id_export_workflow = $request->request->get('id_export_workflow');
$id_export_document = $request->request->get('id_export_document');
$csrftoken = $request->request->get('csrftoken');
$csrftokenPDF = $request->request->get('csrftokenPDF');
if (!$this->isCsrfTokenValid('select_export_workflow_document', $csrftoken)) {
throw new \Exception('Csrf token not valid');
}
if ( isset($id_export_workflow) && !isset($id_export_document))
{
$logger->debug("workflow definito e documenti non definito");
$expWorkflow=$this->exportChecklist($id_export_workflow, $logger, $params);
$logger->debug("expWorkflow ".json_decode($expWorkflow));
$exDoc="Non Definito";
}
else if (!isset($id_export_workflow) && isset($id_export_document))
{
$logger->debug("workflow non definito e documenti definito ");
$exDoc=$this->downloadPDFAction($id_export_document, $csrftokenPDF, 'internal', $logger, $params);
$logger->debug("exDoc ".json_encode($exDoc));
$expWorkflow="Non Definito";
}
else if (isset($id_export_workflow) && isset($id_export_document))
{
$logger->debug("documenti e workflow definito ");
$expWorkflow=$this->exportChecklist($id_export_workflow, $logger, $params);
$exDoc=$this->downloadPDFAction($id_export_document, $csrftokenPDF, 'internal', $logger, $params);
$logger->debug("expWorkflow ".json_encode($expWorkflow));
$logger->debug("exDoc ".json_encode($exDoc));
}
return $res_json->setData(array('nome_file_workflow' => $expWorkflow,
'nome_file_documentazione' => $exDoc));
}
function generatePDFAction( Request $request, LoggerInterface $logger,ParameterBagInterface $params )
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$id = $request->request->get('id');
$csrftoken = $request->request->get('csrftoken');
if (!$this->isCsrfTokenValid('generate_pdf', $csrftoken)) {
throw new \Exception('Csrf token not valid');
}
$sql="select ct.id id_template, ct.name , ct.description, ct.flag_type_checklist, cj.flow_image
from chklst_template ct left join chklst_jflow cj on ( ct.id = cj.chklst_template)
where ct.id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $id);
$stmt->execute();
$NomeWorkflow =$stmt->fetchAll()[0];
$PathImg=$params->get('kernel.project_dir') .'/public/'. $NomeWorkflow['flow_image'];
$logger -> debug("PathImg ".$PathImg);
if (file_exists($PathImg)) {
$logger -> debug("The file $PathImg exists");
$Workflow[]=array('id_template' => $NomeWorkflow['id_template'],
'name' => $NomeWorkflow['name'],
'description' => $NomeWorkflow['description'],
'flow_image' => $NomeWorkflow['flow_image'],
'type' => $NomeWorkflow['flag_type_checklist'],
'file_exist' => 'Y');
} else {
$logger -> debug( "The file $PathImg does not exist" );
$Workflow[]=array('id_template' => $NomeWorkflow['id_template'],
'name' => $NomeWorkflow['name'],
'description' => $NomeWorkflow['description'],
'flow_image' => $NomeWorkflow['flow_image'],
'type' => $NomeWorkflow['flag_type_checklist'],
'file_exist' => 'N');
}
$sql1="select t.*, s.* ,ta.*
from task t left join pre_chklsttmpl2task pct on (t.id=pct.task_id )
left join chklst_template ct on (pct.chklsttmpl_id = ct.id )
left join task2server ts on (t.id = ts.task_id)
left join server s on (ts.server_id = s.id)
left join task_anagrafica ta on (t.cmd_type = ta.tipo_task)
where ct.id = :idTemplate
order by pct.task_order";
$stmt1 = $conn->prepare($sql1);
$stmt1->bindValue('idTemplate', $id);
$stmt1->execute();
$TaskWorkflow = $stmt1->fetchAll();
foreach($TaskWorkflow as $obj)
{
$ObjWlf[]=array("NomeTask" =>$obj['name'],
"description" =>$obj['description'],
"cmd_type" =>$obj['cmd_type'],
"cmd_text" =>$obj['cmd_text'],
"config_json" => json_decode($obj['config_json'], true),
"descrizione_task" =>$obj["descrizione_task"],
//task anagrafica
"tipologia_task" =>$obj['tipologia_task'],
"path_image_task" =>$obj['path_image_task'],
//db
"type_db" =>$obj['type_db'],
"ip_db" =>$obj['ip_db'],
"port_db" =>$obj['port_db'],
"name_db" =>$obj['name_db'],
"service" =>$obj['service'],
"connection_direct" => ($obj['connection_direct'] == 'Y' ? 'SI' : 'NO'),
"use_certificate_db" => $obj['use_certificate_db'],
//ssh
"ip_address" =>$obj['ip_address'],
"hostname" =>$obj['hostname'],
"username" =>$obj['username'],
"ssh_port" =>$obj['ssh_port'],
"flag_run_as" =>$obj['flag_run_as'],
"run_as_user" =>$obj['run_as_user'],
"flag_run_as_user_password" =>$obj['flag_run_as_user_password'],
"run_as_password" =>$obj['run_as_password'],
//mail
"flag_mail_attachment" =>$obj['flag_mail_attachment'],
"mail_attachment" =>$obj['mail_attachment'],
"send_to_mail" =>$obj['send_to_mail'],
"send_cc_mail" =>$obj['send_cc_mail'],
"send_text_mail" =>$obj['send_text_mail'],
"send_object_mail" =>$obj['send_object_mail']
);
}
$user = $this->getUser();
$sql="select * from roles";
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$roles = $stmt->fetchAll();
$logger->debug("---> ".json_encode($ObjWlf));
return $this->render('generatePDF.html.twig',
array(
"NomeWorkflow" => $Workflow,
"TaskWorkflow" => $ObjWlf,
'logged_user' => $user,
'roles' => $roles
) );
}
function createPdf($id, LoggerInterface $logger, ParameterBagInterface $params)
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$sql="select ct.id id_template, ct.name , ct.description, cj.flow_image
from chklst_template ct left join chklst_jflow cj on ( ct.id = cj.chklst_template)
where ct.id = :idTemplate";
$stmt = $conn->prepare($sql);
$stmt->bindValue('idTemplate', $id);
$stmt->execute();
$NomeWorkflow =$stmt->fetchAll()[0];
$date = new \DateTime();
$DataPdf = $date->format('Y-m-d');
$NomePDF=$NomeWorkflow['name'].'_'.$DataPdf.'.pdf';
$logger->debug("Nome PDF ".$NomePDF);
$PathImg=$params->get('kernel.project_dir') .'/public/'. $NomeWorkflow['flow_image'];
$logger -> info("PathImg ".$PathImg);
if (file_exists($PathImg)) {
$logger -> info("The file $PathImg exists");
$Workflow[]=array('id_template' => $NomeWorkflow['id_template'],
'name' => $NomeWorkflow['name'],
'description' => $NomeWorkflow['description'],
'flow_image' => $NomeWorkflow['flow_image'],
'file_exist' => 'Y');
} else {
$logger -> info( "The file $PathImg does not exist" );
$Workflow[]=array('id_template' => $NomeWorkflow['id_template'],
'name' => $NomeWorkflow['name'],
'description' => $NomeWorkflow['description'],
'flow_image' => $NomeWorkflow['flow_image'],
'file_exist' => 'N');
}
$logger -> info("Workflow ".json_encode($Workflow));
$sql1 = "select t.*, s.* ,ta.*
from task t left join pre_chklsttmpl2task pct on (t.id=pct.task_id )
left join chklst_template ct on (pct.chklsttmpl_id = ct.id )
left join task2server ts on (t.id = ts.task_id)
left join server s on (ts.server_id = s.id)
left join task_anagrafica ta on (t.cmd_type = ta.tipo_task)
where ct.id = :idTemplate
order by pct.task_order";
$stmt1 = $conn->prepare($sql1);
$stmt1->bindValue('idTemplate', $id);
$stmt1->execute();
$TaskWorkflow1 = $stmt1->fetchAll();
foreach($TaskWorkflow1 as $obj)
{
$ObjWlf[]=array("NomeTask" =>$obj['name'],
"description" =>$obj['description'],
"cmd_type" =>$obj['cmd_type'],
"cmd_text" =>$obj['cmd_text'],
"config_json" => json_decode($obj['config_json'], true),
"descrizione_task" =>$obj["descrizione_task"],
//task anagrafica
"tipologia_task" =>$obj['tipologia_task'],
"path_image_task" =>$obj['path_image_task'],
//db
"type_db" =>$obj['type_db'],
"ip_db" =>$obj['ip_db'],
"port_db" =>$obj['port_db'],
"name_db" =>$obj['name_db'],
"service" =>$obj['service'],
"connection_direct" => ($obj['connection_direct'] == 'Y' ? 'SI' : 'NO'),
"use_certificate_db" => $obj['use_certificate_db'],
//ssh
"ip_address" =>$obj['ip_address'],
"hostname" =>$obj['hostname'],
"username" =>$obj['username'],
"ssh_port" =>$obj['ssh_port'],
"flag_run_as" =>$obj['flag_run_as'],
"run_as_user" =>$obj['run_as_user'],
"flag_run_as_user_password" =>$obj['flag_run_as_user_password'],
"run_as_password" =>$obj['run_as_password'],
//mail
"flag_mail_attachment" =>$obj['flag_mail_attachment'],
"mail_attachment" =>$obj['mail_attachment'],
"send_to_mail" =>$obj['send_to_mail'],
"send_cc_mail" =>$obj['send_cc_mail'],
"send_text_mail" =>$obj['send_text_mail'],
"send_object_mail" =>$obj['send_object_mail']
);
}
$pdfOptions = new Options();
$logger->debug("renderView templatePDF");
$html = $this->renderView('templatePDF.html.twig', [
// 'title' => "Welcome to our PDF Test",
'NomeWorkflow' => $Workflow,
'TasksWorkflow' => $ObjWlf,
'BasePath' =>$params->get('kernel.project_dir').'/public'
]);
// Configura Dompdf e genera il PDF
$logger->debug("Configura Dompdf e genera il PDF");
//$pdfOptions->set('defaultFont', 'Helvetica');
//$pdfOptions->set('isRemoteEnabled', true);
//$pdfOptions->set('isHtml5ParserEnabled', true);
$pdfOptions->set('chroot', $params->get('kernel.project_dir')); // Assicurati che sia il percorso corretto
$dompdf = new Dompdf($pdfOptions);
$logger->debug("setBasePath");
$dompdf->setBasePath($params->get('kernel.project_dir').'/public'); // Imposta il percorso base dove risiedono i CSS
$logger->debug("carica html");
$dompdf->loadHtml($html);
$logger->debug("stampo html ".$html);
// (Opzionale) Imposta le dimensioni del foglio e l'orientamento
$logger->debug("setpaper");
$dompdf->setPaper('A4', 'landscape');
// Renderizza il PDF
$dompdf->render();
$logger->debug("render");
// Invia il PDF al browser
/*$dompdf->stream($NomePDF, [ "Attachment" => true // Per visualizzare il PDF nel browser
]);*/
// Dopo aver generato il PDF
$pdfOutput = $dompdf->output();
$response = new Response($pdfOutput);
$response->headers->set('Content-Type', 'application/pdf');
$response->headers->set('Content-Disposition', 'attachment; filename="'.$NomePDF.'"');
$publicDirectory = $params->get('kernel.project_dir') . '/public/chklst_exports/doc_pdf/';
// e.g /var/www/project/public/mypdf.pdf
$pdfFilepath = $publicDirectory . $NomePDF;
if (!file_exists($publicDirectory)) {
mkdir($publicDirectory, 0777, true);
}
// Write file to the desired path
file_put_contents($pdfFilepath, $pdfOutput);
return $NomePDF;
}
public function downloadPDFAction($id, $csrftoken, $typeCall, LoggerInterface $logger, ParameterBagInterface $params)
{
if (!$this->isCsrfTokenValid('download_pdf', $csrftoken)) {
throw new \Exception('Csrf token not valid');
}
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$res_json = new JsonResponse();
$logger->debug("[downloadPDFAction] idTemplate ".$id);
$arrayId = strpos($id, ',');
$logger->debug("[downloadPDFAction] arrayId ".$arrayId);
if (!$arrayId)
{
$logger->debug("[downloadPDFAction] Workflow Singolo ".$id);
$file=$this->createPdf($id, $logger, $params);
if ($typeCall == 'internal')
{
return $file;
}
else
{
return $res_json->setData(array('nome_file' => $file));
}
}
else
{
$logger->debug("[downloadPDFAction] Scarico piu workflow ".$arrayId);
$zip = new \ZipArchive();
$target_dir = $params->get('kernel.project_dir') . '/public/chklst_exports/doc_pdf/';
$logger->debug("[downloadPDFAction] Creo zip file su directory ".$target_dir );
$zipName = 'Sybot_Documentazione_Workflow_' . time() . ".zip";
$logger->debug("[downloadPDFAction] Nome Zip ".$zipName);
if ($zip->open($target_dir . $zipName, \ZipArchive::CREATE )!==TRUE) {
$logger->debug("[downloadPDFAction]non posso aprire ".$target_dir . $zipName);
}
else
{
$logger->debug("[downloadPDFAction] Apro lo zip");
$array = explode(',', $id);
foreach( $array as $a)
{
$logger->debug("[downloadPDFAction] Scarico piu workflow ".$a);
$file=$this->createPdf($a, $logger, $params);
$logger->debug("[downloadPDFAction] File creato ".$file);
if (file_exists($target_dir.$file))
{
$logger->debug("[downloadPDFAction] il file ".$file." esiste");
//$zip->addFromString($zipName, 'Aggiunto File '.$file);
$zip->addFile($target_dir.$file, $file );
$logger->debug("[downloadPDFAction] File aggiunto allo zip ");
}
else
{$logger->debug("[downloadPDFAction] il file ".$file." non esiste");}
}
$zip->close();
}
$logger->debug("[downloadPDFAction] Chiudo lo Zip");
if ($typeCall == 'internal')
{
return $zipName;
}
else
{
return $res_json->setData(array('nome_file' => $zipName));
}
}
}
public function getTaskResultJson($id, LoggerInterface $logger): Response
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$stmt = $conn->prepare("
SELECT name, result_json
FROM v_log_execution_task
WHERE chklst_task_id = :id
");
$stmt->bindValue('id', $id);
$stmt->execute();
$res = $stmt->fetch();
if (!$res) {
return new Response('Not found', 404);
}
//Format JSON con indentazione
$raw = trim($res['result_json']);
$raw = preg_replace('/^\xEF\xBB\xBF/', '', $raw);
$decoded = json_decode($raw, true);
// se non è JSON valido, lo restituisco così com’è
$pretty = $decoded !== null
? json_encode($decoded, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)
: $raw;
$response = new Response($pretty);
$disposition = HeaderUtils::makeDisposition(
HeaderUtils::DISPOSITION_ATTACHMENT,
sprintf('Task_%s_result.json', $res['name'])
);
$response->headers->set('Content-Type', 'application/json; charset=UTF-8');
$response->headers->set('Content-Disposition', $disposition);
return $response;
}
public function getWorkflowResultJson($id, LoggerInterface $logger): Response
{
$em = $this->getDoctrine()->getManager();
$conn = $em->getConnection();
$stmt = $conn->prepare("select name, result_json from v_log_execution_workflow where chklst_id=:id");
$stmt->bindValue('id', $id);
$stmt->execute();
$res = $stmt->fetch();
if (!$res) {
return new Response('Not found', 404);
}
//Format JSON con indentazione
$raw = trim($res['result_json']);
$raw = preg_replace('/^\xEF\xBB\xBF/', '', $raw);
$decoded = json_decode($raw, true);
// se non è JSON valido, lo restituisco così com’è
$pretty = $decoded !== null
? json_encode($decoded, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)
: $raw;
$response = new Response($pretty);
$disposition = HeaderUtils::makeDisposition(
HeaderUtils::DISPOSITION_ATTACHMENT,
sprintf('Workflow_%s_result.json', $res['name'])
);
$response->headers->set('Content-Type', 'application/json; charset=UTF-8');
$response->headers->set('Content-Disposition', $disposition);
return $response;
}
}
// ----------------------------------------------------------------------------
// JFlowRemapper
// ----------------------------------------------------------------------------
class JFlowRemapper {
private $logger;
public function go(&$jflow, $logger, $connection) {
$sql = "select id, tipo_task from task_anagrafica";
$statement = $connection->prepare($sql);
$statement->execute();
foreach ($statement->fetchAll() as $row) {
$taskType2Id[$row['tipo_task']] = $row['id'];
}
foreach ($jflow['nodeDataArray'] as &$node)
{
if($node["category"]=="CommentGroup"){
continue;
}
$key = $node["key"];
$origKey = $key;
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$parts = explode("_", $key);
$origAnagraficaId = $parts[2];
$taskType = $node["taskType"];
$currAnagraficaId = $taskType2Id[$taskType];
$orig2CurrAnagraficaId[$origAnagraficaId] = $currAnagraficaId;
$remappedKey = "[TASK $parts[0]_$parts[1]_$currAnagraficaId]";
$node["key"] = $remappedKey;
}
foreach ($jflow['linkDataArray'] as &$link)
{
$key = $link["from"];
$origKey = $key;
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$parts = explode("_", $key);
$currAnagraficaId = $orig2CurrAnagraficaId[$parts[2]];
$key = "[TASK $parts[0]_$parts[1]_$currAnagraficaId]";
$link["from"] = $key;
$key = $link["to"];
$origKey = $key;
$key = str_replace("[TASK ","", $key);
$key = str_replace("]","", $key);
$parts = explode("_", $key);
$currAnagraficaId = $orig2CurrAnagraficaId[$parts[2]];
$key = "[TASK $parts[0]_$parts[1]_$currAnagraficaId]";
$link["to"] = $key;
}
}
}
// ----------------------------------------------------------------------------
// ACM2SybotJFlowConverter
// ----------------------------------------------------------------------------
class ACM2SybotJFlowConverter {
private $logger;
private $connection;
private $idTemplate;
private $conditionIndex = 1;
private $nextFreeTopPortIndex = [];
private $sybotNodes = [];
private $sybotLinkDataArray = [];
public function go(
$acmTasksById, // id => task acm (secondo l'id nella checklist ACM)
$acmTasksByName, // nome => task acm
$logger,
$jflowRaw,
$idTemplate,
$acmTasksExtraInfo,
$connection)
{
$this->logger = $logger;
$this->connection = $connection;
$this->idTemplate = $idTemplate;
$jflow = json_decode($jflowRaw, true);
$jflow["linkFromPortIdProperty"] = "fromPort";
$jflow["linkToPortIdProperty"] = "toPort";
$acmNodeDataArray = $jflow["nodeDataArray"];
$acmLinkDataArray = $jflow["linkDataArray"];
// --------------------------------------------------------------------
//
// --------------------------------------------------------------------
// --------------------------------------------------------------------
// Fase 0 - Nodi (1° parte)
// --------------------------------------------------------------------
foreach ($acmNodeDataArray as $acmNode)
{
if($acmNode["category"] == "End") {
continue;
}
$acmKey = $acmNode["key"];
$acmKey = str_replace("[TASK ","", $acmKey);
$acmKey = str_replace("]","", $acmKey);
$parts = explode("_", $acmKey);
$acmTaskId = $parts[1];
$acmTask = $acmTasksById[$acmTaskId];
$currentTaskInfo = $acmTasksExtraInfo[$acmTaskId];
$sybotTaskId = $currentTaskInfo["newId"];
$cmdType = $currentTaskInfo["cmdType"];
$sql = "select id, tipologia_task, descrizione_task, tipo_task from task_anagrafica where tipo_task = :cmdType";
$statement = $connection->prepare($sql);
$statement->bindValue("cmdType", $cmdType);
$statement->execute();
$queryResult = $statement->fetchAll();
$idTaskAnagrafica = $queryResult[0]["id"];
$acmTasksExtraInfo[$acmTaskId]['cmdTypeId'] = $idTaskAnagrafica;
}
// --------------------------------------------------------------------
// Fase 1 - Nodi (2° parte)
// --------------------------------------------------------------------
$logger->debug("------- FASE 1");
foreach ($acmNodeDataArray as $acmNode)
{
if($acmNode["category"] == "End") {
continue;
}
$acmKey = $acmNode["key"];
$acmKey = str_replace("[TASK ","", $acmKey);
$acmKey = str_replace("]","", $acmKey);
$parts = explode("_", $acmKey);
$acmTaskId = $parts[1];
$acmTask = $acmTasksById[$acmTaskId];
$currentTaskInfo = $acmTasksExtraInfo[$acmTaskId];
$sybotTaskId = $currentTaskInfo["newId"];
$cmdType = $currentTaskInfo["cmdType"];
$idTaskAnagrafica = $acmTasksExtraInfo[$acmTaskId]['cmdTypeId'];
$sybotNode = array_merge(
$acmNode,
[
"key" => "[TASK " . $idTemplate . "_" . $sybotTaskId . "_" . $idTaskAnagrafica . "]",
"taskType" => $cmdType,
"descr_anagrafica" => $queryResult[0]["descrizione_task"],
"tipo_anagrafica" => $queryResult[0]["tipologia_task"],
"statusCode" => "success",
"statusText" => "Task salvato con successo",
"leftArray" => [],
"bottomArray" => [],
"topArray" => [],
"rightArray" => []
]
);
for ($i=0; $i<5; $i++) {
$sybotNode["topArray"][] = [
"portId" => "top$i",
"portColor" => "transparent"
];
}
for ($i=0; $i<5; $i++) {
$sybotNode["leftArray"][] = [
"portId" => "left$i",
"portColor" => "transparent"
];
}
$this->sybotNodes[$sybotTaskId] = $sybotNode;
// ----------------------------------------------------------------
// Cleanup
// ----------------------------------------------------------------
$sql = "delete from task_conditional where id_task = :idTask and id_template = :idTemplate";
$statement = $connection->prepare($sql);
$statement->bindValue("idTask", $sybotTaskId);
$statement->bindValue("idTemplate", $idTemplate);
$statement->execute();
// ----------------------------------------------------------------
// Condizioni da XML
// ----------------------------------------------------------------
$acmOKTaskName = $acmTask['next_task'];
$acmKOTaskName = $acmTask['action']['next_task_act'];
try {
if ($cmdType == 'db' && $acmTask['flag_check_count_query'] == 'Y')
{
$match_operator = $acmTask['match_operator'];
$match_value = $acmTask['match_value'];
if ($match_operator == "=") {
$match_operator = "==";
}
if ($acmOKTaskName)
{
$acmOKTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmOKTaskName]];
$expr = "#>" . $acmTask['name'] . ".count#< $match_operator $match_value";
$this->makeLink($currentTaskInfo, $acmOKTaskInfo, "custom", $expr);
}
if ($acmKOTaskName)
{
$expr = "!(#>" . $acmTask['name'] . ".count#< $match_operator $match_value)";
$acmKOTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmKOTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmKOTaskInfo, "custom", $expr);
}
}
else if ($cmdType == 'ssh') {
$match_operator = $acmTask['match_operator'];
$match_value = $acmTask['match_value'];
$return_type = $acmTask['return_type'];
if ($acmTask['flag_check_exit_status'] == 'Y')
{
if ($match_operator == "=") {
$match_operator = "==";
}
if ($acmOKTaskName)
{
$acmOKTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmOKTaskName]];
$expr = "#>" . $acmTask['name'] . ".exitStatus#< $match_operator $match_value";
$this->makeLink($currentTaskInfo, $acmOKTaskInfo, "custom", $expr);
}
if ($acmKOTaskName)
{
$expr = "!(#>" . $acmTask['name'] . ".exitStatus#< $match_operator $match_value)";
$acmKOTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmKOTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmKOTaskInfo, "custom", $expr);
}
} else {
if ($return_type == 'text') {
$str1 = "#>" . $acmTask['name'] . ".result<#";
switch ($match_operator)
{
case "starts with":
$expr = "startswith($str1, \"$match_value\")";
break;
case "contains":
$expr = "contains($str1, \"$match_value\")";
break;
case "ends with":
$expr = "endswith($str1, \"$match_value\")";
break;
case "equal to":
$expr = "$str1 == \"$match_value\"";
break;
case "regexp":
$expr = "regexp($str1, \"$match_value\")";
break;
default:
break;
}
if ($acmOKTaskName) {
$acmOKTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmOKTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmOKTaskInfo, "custom", $expr);
}
if ($acmKOTaskName) {
$acmKOTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmKOTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmKOTaskInfo, "custom", $expr);
}
} else if ($return_type == 'number') {
if ($match_operator == "=") {
$match_operator = "==";
}
if ($acmOKTaskName)
{
$acmOKTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmOKTaskName]['id']];
$expr = "#>" . $acmTask['name'] . ".result#< $match_operator $match_value";
$this->makeLink($currentTaskInfo, $acmOKTaskInfo, "custom", $expr);
}
if ($acmKOTaskName)
{
$expr = "!(#>" . $acmTask['name'] . ".result#< $match_operator $match_value)";
$acmKOTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmKOTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmKOTaskInfo, "custom", $expr);
}
}
}
}
else
{
if ($acmOKTaskName)
{
$acmOKTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmOKTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmOKTaskInfo, "default");
}
if ($acmKOTaskName)
{
$acmKOTaskInfo = $acmTasksExtraInfo[$acmTasksByName[$acmKOTaskName]['id']];
$this->makeLink($currentTaskInfo, $acmKOTaskInfo, "ko");
}
}
} catch (\Error $err) {
$logger->debug("Eccezione: " . $err->getMessage() . " line: " . $err->getLine());
}
}
// --------------------------------------------------------------------
// Fase 2 - Links
// --------------------------------------------------------------------
$logger->debug("------- FASE 2");
foreach ($acmLinkDataArray as $acmLink)
{
if(str_contains($acmLink["to"], "END")) {
continue;
}
if(str_contains($acmLink["from"], "END")) {
continue;
}
if(array_key_exists("color", $acmLink)) {
continue;
}
$keyFrom = $acmLink["from"];
$keyFrom = str_replace("[TASK ","", $keyFrom);
$keyFrom = str_replace("]","", $keyFrom);
$parts = explode("_", $keyFrom);
$acmIdOfTaskFrom = $parts[1];
$taskFromInfo = $acmTasksExtraInfo[$acmIdOfTaskFrom];
if (array_key_exists('default_done', $taskFromInfo)) {
continue;
}
$taskFromInfo['default_done'] = true;
$keyTo = $acmLink["to"];
$keyTo = str_replace("[TASK ","", $keyTo);
$keyTo = str_replace("]","", $keyTo);
$parts = explode("_", $keyTo);
$acmIdOfTaskTo = $parts[1];
$taskToInfo = $acmTasksExtraInfo[$acmIdOfTaskTo];
$this->makeLink($taskFromInfo, $taskToInfo, "default");
}
// --------------------------------------------------------------------
// Fase 3 - output
// --------------------------------------------------------------------
$lastTask = end($jflow["nodeDataArray"]);
$this->makeLastLink($lastTask, $acmTasksExtraInfo, $logger);
$jflow["nodeDataArray"] = array_values($this->sybotNodes);
$jflow["linkDataArray"] = $this->sybotLinkDataArray;
return json_encode($jflow);
}
private function makeLastLink($lastTask, $acmTasksExtraInfo, $logger) {
$logger->debug("[ACM] [END] Inserimento ultimo link [{$lastTask['text']}]");
$conditionIndex = $this->conditionIndex;
$taskKeyRaw = $lastTask['key']; // Es: [TASK 8_27]
$taskKeyClean = str_replace(["[TASK ", "]"], "", $taskKeyRaw); // Es: 8_27
$taskKeyParts = explode("_", $taskKeyClean); // Es: ["8", "27"]
$taskSequenceNumber = end($taskKeyParts); // Es: 27
$lastTaskId = $acmTasksExtraInfo[$taskSequenceNumber];
$node = &$this->sybotNodes[$lastTaskId['newId']];
$node["rightArray"][] = [
"portId" => "port_" . $this->idTemplate . "_" . $lastTaskId['newId'] . "_" . $conditionIndex,
"portColor" => "green",
"portConditionType" => "default"
];
}
private function makeLink($taskFromInfo, $taskToInfo, $conditionType, $expr=null) {
$conditionIndex = $this->conditionIndex;
$node = &$this->sybotNodes[$taskFromInfo['newId']];
switch ($conditionType) {
case "default":
$color = "green";
break;
case "ko":
$color = "red";
break;
case "custom":
$color = "dodgerblue";
break;
default:
$color = "gray";
break;
}
$node["rightArray"][] = [
"portId" => "port_" . $this->idTemplate . "_" . $taskFromInfo['newId'] . "_" . $conditionIndex,
"portColor" => $color,
"portConditionType" => $conditionType
];
$link = array();
$link["from"] = "[TASK " . $this->idTemplate . "_" . $taskFromInfo['newId'] . "_" . $taskFromInfo['cmdTypeId'] . "]";
$link["to"] = "[TASK " . $this->idTemplate . "_" . $taskToInfo['newId'] . "_" . $taskToInfo['cmdTypeId'] . "]";
$link["fromPort"] = "port_" . $this->idTemplate . "_" . $taskFromInfo['newId'] . "_" . $conditionIndex;
if (!isset($this->nextFreeTopPortIndex[$taskToInfo['newId']])) {
$this->nextFreeTopPortIndex[$taskToInfo['newId']] = 1;
}
$link["toPort"] = "top" . $this->nextFreeTopPortIndex[$taskToInfo['newId']];
if ($this->nextFreeTopPortIndex[$taskToInfo['newId']] < 5) {
$this->nextFreeTopPortIndex[$taskToInfo['newId']] ++;
}
if ($this->linkAlreadyExists($link)) {
$this->logger->debug("evito di creare link duplicato", json_encode($link));
return;
}
$sql =
"insert into task_conditional (id_task, nome, tipo, espressione, id_template)
values (:idTask, 'Condizione ". $conditionIndex . "', '$conditionType', :expr, :idTemplate)";
$statement = $this->connection->prepare($sql);
$statement->bindValue("idTask", $taskFromInfo['newId']);
$statement->bindValue("idTemplate", $this->idTemplate);
$statement->bindValue("expr", $expr);
$statement->execute();
$link["color"] = $color;
$link["text"] = "Condizione " . $conditionIndex;
$this->conditionIndex ++;
$this->sybotLinkDataArray[] = $link;
}
private function linkAlreadyExists($link) {
foreach ($this->sybotLinkDataArray as $existingLink) {
if ($existingLink['from'] == $link['from'] &&
$existingLink['to'] == $link['to'] &&
$existingLink['fromPort'] == $link['fromPort'] &&
$existingLink['toPort'] == $link['toPort']) {
return true;
}
}
return false;
}
}

src/Controller/ChecklistController.php line 2273