<?php

namespace app\models;

use Yii;
use yii\base\Model;
use app\models\TasksModel;
use app\models\entity\Accounts;
use app\models\entity\Projecttypes;
use app\models\entity\AsusgProject;
use app\models\entity\ProjectsLocotech;


/*
build_project: BEGIN
	declare projectId integer;
    declare locoserie varchar(255);
    declare loconumber int;
    declare projecttypeId integer;
    declare projectcomment varchar(255);

    declare tmpText varchar(255);
    declare taskGuid varchar(255);
    declare parentId integer;
    declare taskId integer;
    declare commandId integer;
    declare counter integer;
    
    declare jobId integer;

    declare projecttype integer;
    declare companyId integer;
    
    declare s1 varchar(1);
    declare s2 varchar(1);
    declare s3 varchar(1);
    declare tmps varchar(1);
    declare s1v integer;
    declare s2v integer;
    declare s3v integer;
    declare tmpsv integer;
    
    declare e1 integer;
    declare e2 integer;
    declare e3 integer;
    
    declare done integer;

    declare cur_job cursor for
    select aj2l.id, coalesce(aj2l.asusg_employee_id1, 0), coalesce(aj2l.asusg_employee_id2, 0), coalesce(aj2l.asusg_employee_id3, 0)
		from asusg_job2launch aj2l
        where aj2l.asusg_project_id = project_id;
	
    declare cur_task cursor for
	select id
		from tasks
        where parent_id = 0
			and input_id = projectId;
	
	declare cur_command cursor for
    select t.id
		from tasks t
        where t.input_id = projectId
			and t.parent_id = taskId
        order by t.project_id, t.priority;
	
    declare continue handler for not found set done = 1;


1 Определяем ид компании  ( companyId )


        select company into companyId
            from accounts
            where id = initiator;

1.1 если нет то крах

        if companyId is null then leave build_project; end if;
    

2 Определяем тип роекта ( projecttype )

        select id into projecttype
            from projecttypes
            where name = 'Проект из мэппинга'
                and company = companyId;


3. Определяем ид проекта из asusg_project  ( projectId )
	set projectId = (select id from asusg_project where id = project_id and status = 1);

3.1 если не то крах

    if (projectId is null) then leave build_project; end if;


4. Определяем ид типа проекта ( projecttypeId )

	select ap.asusg_projecttype_id into projecttypeId
		from asusg_project ap
        where ap.id = projectId;

4.1 если нет джойним asusg_project и asusg_projecttype ( projecttypeId )

    if (projecttypeId is null) then
		select apt.id into projecttypeId
			from asusg_project ap
				left join asusg_projecttype apt on apt.loco_serie_id = ap.loco_serie_id and apt.repair_type = ap.repair_type
			where ap.id = project_id;

4.2 если нет то крах

		if (projecttypeId is null) then leave build_project; end if;
	end if;


5 определяем переменные locoserie, loconumber, tmpText

	select ls.name, ap.loco_number, rt.name into locoserie, loconumber, tmpText
		from asusg_project ap left join locomotive_series ls on ap.loco_serie_id = ls.id left join repairtypes rt on rt.id = ap.repair_type
        where ap.id = project_id;

6 получаем  строку коммента

    set projectcomment = concat('Проект АСУ СГ №', convert(project_id, char), ': ', tmpText, ', ', locoserie);

7 собираем все переменные initiator, projecttype, projectId, locoserie, loconumber, projectcomment и фигачим проект магия

	call start_project_for_loco(initiator, projecttype, projectId, locoserie, loconumber, projectcomment);

7.1  если projectId  нет то крах

    if (projectId is null) then leave build_project; end if;

8 прикручиваем к созданному проекту asusg_project_id

    update projects_locotech
		set asusg_project_id = project_id
        where id = projectId;

	set s1 = 'А';
    set s2 = 'Б';
    set s3 = 'В';
 
9  
 
    select coalesce(section_a, 0), coalesce(section_b, 0), coalesce(section_c, 0)
		into s1v, s2v, s3v
        from asusg_project
        where id = project_id;
	
    if (s2v < s1v) then
		set tmpsv = s1v;
        set s1v = s2v;
        set s2v = tmpsv;
		set tmps = s1;
        set s1 = s2;
        set s2 = tmps;
	end if;

    if (s3v < s2v) then
		set tmpsv = s2v;
        set s2v = s3v;
        set s3v = tmpsv;
		set tmps = s2;
        set s2 = s3;
        set s3 = tmps;
	end if;

    if (s2v < s1v) then
		set tmpsv = s1v;
        set s1v = s2v;
        set s2v = tmpsv;
		set tmps = s1;
        set s1 = s2;
        set s2 = tmps;
	end if;

10

	if s1v > 0 then
		open cur_job;
		cj_loop1: loop
			set done = 0;
			fetch cur_job into jobId, e1, e2, e3;
			if done = 1 then leave cj_loop1; end if;
            if (e1 <> 0) or (e2 <> 0) or (e3 <> 0) then
				call asusg_add_job_to_project_for_section_by_employee(initiator, project_id, jobId, s1);
			else
				call asusg_add_job_to_project_for_section(initiator, project_id, jobId, s1);
			end if;
		end loop;
		close cur_job;
	end if;
    
	if s2v > 0 then
		open cur_job;
		cj_loop2: loop
			set done = 0;
			fetch cur_job into jobId, e1, e2, e3;
			if done = 1 then leave cj_loop2; end if;
            if (e1 <> 0) or (e2 <> 0) or (e3 <> 0) then
				call asusg_add_job_to_project_for_section_by_employee(initiator, project_id, jobId, s2);
			else
				call asusg_add_job_to_project_for_section(initiator, project_id, jobId, s2);
			end if;
		end loop;
		close cur_job;
	end if;
    
	if s3v > 0 then
		open cur_job;
		cj_loop3: loop
			set done = 0;
			fetch cur_job into jobId, e1, e2, e3;
			if done = 1 then leave cj_loop3; end if;
            if (e1 <> 0) or (e2 <> 0) or (e3 <> 0) then
				call asusg_add_job_to_project_for_section_by_employee(initiator, project_id, jobId, s3);
			else
				call asusg_add_job_to_project_for_section(initiator, project_id, jobId, s3);
			end if;
		end loop;
		close cur_job;
	end if;
    
11 //  установка статуса проект в работе

    update asusg_project
		set status = 2,
			started = NOW()
        where id = project_id;
        
12 //  установка работ для проекта

	update projects_locotech
		set tasks = coalesce(right(tasks, length(tasks) - 1), '')
        where id = projectId;

	insert into text_log (msg) values (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' подготовлен. Упорядочиваю команды.'));
    
13 // Упорядочить команды в задачах по project_id, установить project_id = 0.     
	
    open cur_task;
    ct_loop: loop
		set done = 0;
        fetch cur_task into taskId;
        if done = 1 then leave ct_loop; end if;

		set counter = 0;
        open cur_command;
        cc_loop: loop
			set done = 0;
            fetch cur_command into commandId;
            if done = 1 then leave cc_loop; end if;
            set counter = counter + 1;
            update tasks
				#set project_id = 0, priority = counter
				set priority = counter, project_id = 0
                where id = commandId;
        end loop;
        close cur_command;
    end loop;
    close cur_task;

	insert into text_log (msg) values (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' запущен в работу.'));
END
*/


class AsusgBuildAndStartProjectModel extends Model
{
    
    private $e1;
    private $e2;
    private $e3;
    private $s1v;
    private $s2v;
    private $s3v;
    private $done;
    private $tmps;
    private $tmpsv;
    private $jobId;
    private $taskId;
    private $cur_job;
    private $counter;
    private $tmpText;
    private $taskGuid;
    private $parentId;
    private $s1 = 'А';
    private $s2 = 'Б';
    private $s3 = 'В';
    private $cur_task;
    private $commandId;
    private $companyId;
    private $projectId;
    private $locoserie;
    private $initiator;
    private $project_id;
    private $loconumber;
    private $projecttype;
    private $cur_command;
    private $projecttypeId;
    private $projectcomment;
    private $asusgProjectId;

    public function asusg_build_and_start_project_auto($initiator,$project_id)
    {
        $initiator = '27';
        //$project_id = '27';
        $this->initiator = $initiator;
        $this->project_id = $project_id;
        
        
        // 1 Определяем ид компании  ( companyId )
        // 1.1 если нет то крах 
        
        $this->getCompanyId();
        
        // 2 Определяем тип роекта ( projecttype )       

        $this->GetProjecttype();
        
        // 3. Определяем ид проекта из asusg_project  ( projectId )        

        $this->GetProjectId();
        
        // 4. Определяем ид типа проекта ( projecttypeId )      
        // 4.1 если нет джойним asusg_project и asusg_projecttype ( projecttypeId )

        $this->GetProjecttypeId();

        // 5 определяем переменные locoserie, loconumber, tmpText        
        
        $this->GetLocoProperties();
  
        // 6 получаем  строку коммента   

        $this->projectcomment = 'Проект АСУ СГ №' .  $hit->project_id . ': ' .  $this->tmpText . ', ' . $this->locoserie);
        
        // 7 собираем все переменные initiator, projecttype, projectId, locoserie, loconumber, projectcomment и фигачим проект магия  !!!! ВЫЗОВ ДРУГОЙ ПРОЦЕДУРЫ

        $this->CallProcedureStartProject();
	   
        // 8 прикручиваем к созданному проекту asusg_project_id

        $this->addAsusgProjectId();
        
        // 9 
        
        $this->daclareProperties();
        
        // 10
	
        // 11  установка статуса проект в работе
        
        if(!$this->SetStatusProject()) {
            return false;
        }
        // 12  установка работ для проекта
        if(!$this->SetTasksToProject()) {
            return false;
        }
        
        // 13  Упорядочить команды в задачах по project_id, установить project_id = 0.
        if(!$this->RegularizeTasks()) {
            return false;
        }
        
        
        echo "<pre>";
        var_dump($this->CallProcedureStartProject());
        echo "</pre>";
        exit;
        $result = $this->AsusgCurJob();
        
        //$this->AsusgCurCommand();
        //var_dump($this->cur_command);
        
        //$this->startProjectForLoco();
        $asusgProjectEntity = AsusgProject::find()->where(['id' => $this->project_id , 'status' => 1])->one();
        if ( null == $asusgProjectEntity) {
            return true;
        }
        $this->projectId = $asusgProjectEntity->id;//select id from asusg_project where id = project_id and status = 1
        $this->setUpdateProjects(); 
        $this->organizeСommands(); // протестил работает!
        //var_dump($this->companyId); exit;
    }

    
    // получение cur_job !!!!!!!!
    
    public function AsusgCurJob()
    {

        //declare cur_job cursor for
        $result = [];

        $query = "select aj2l.id, coalesce(aj2l.asusg_employee_id1, 0), coalesce(aj2l.asusg_employee_id2, 0), coalesce(aj2l.asusg_employee_id3, 0)
            from asusg_job2launch aj2l
            where aj2l.asusg_project_id =" . $this->project_id;

        $this->cur_job = Yii::$app->db->createCommand($query)->queryAll();

        if ( count($result) ) {
            //$this->logger->setLog();
        }
        return $result;
    }

    public function AsusgCurTask(){

        $result = [];
        
        $query = 'select id from tasks where parent_id = 0 and input_id = '. $this->project_id;
        $result = Yii::$app->db->createCommand($query)->queryAll();

        if ( count($result) ) {
            //$this->logger->setLog();
        }
        return $result;
        
	}

    public function AsusgCurCommand($taskId){

        $result = [];
        $query = "select t.id from tasks t
                                where t.input_id = " . $this->project_id . "
                                and t.parent_id = " . $taskId . "
                                order by t.project_id, t.priority";
        $result = Yii::$app->db->createCommand($query)->queryAll();
        if ( count($result) ) {
            //$this->logger->setLog();
        }
        $this->cur_command = $result;
        return $result;
    }

// 1. ========================================================================================================================================    
    
    private function getCompanyId()
    {
        $result = [];
        $accountsEntity = Accounts::findOne($this->initiator);
        $this->companyId = $accountsEntity->company;
        if( null == $this->companyId ) {
            return false;
        }
        if ( count($result) ) {
            //$this->logger->setLog();
        }
    }

// 2. ========================================================================================================================================    
    
    private function GetProjecttype(){
    
        // part #8 
    
        //$query = "select id from projecttypes where name = 'Проект из мэппинга' and company = " . $this->companyId;
        $this->projecttype = Projecttypes::find()->select(['id'])->where(['name' => 'Проект из мэппинга','company' => $this->companyId])->all();
        //$this->projecttype = Yii::$app->db->createCommand($query)->queryAll();
    }
    
// 3. ========================================================================================================================================     
    
    private function GetProjectId(){

        $asusgProjectEntity = AsusgProject::find()->where(['id' => $this->project_id , 'status' => 1])->one();
        if ( null == $asusgProjectEntity) {
            return false;
        }
        $this->projectId = $asusgProjectEntity->id;
    }
    
// 4. ========================================================================================================================================         
    
    private function GetProjecttypeId(){
    
        $asusgProjectEntity = AsusgProject::find()->select(['asusg_projecttype_id'])->where(['id' => $this->projectId])->one();
        $this->projecttypeId = $asusgProjectEntity->asusg_projecttype_id;
        if ( null == $this->projecttypeId) {
            
            $asusgProjectJoinEntity = AsusgProject::find()
                ->leftJoin('asusg_projecttype', 'asusg_project.loco_serie_id = asusg_projecttype.id and asusg_projecttype.repair_type = asusg_project.repair_type')
                ->select(['asusg_projecttype.id'])
                ->where(['asusg_project.id' => $this->project_id])
                ->one();
            $this->projecttypeId = $asusgProjectJoinEntity->id;    
        }
        if ( null == $this->projecttypeId) {
            return false;
        }
    }

// 5. ======================================================================================================================================== 
    
    private function GetLocoProperties(){
    
        $asusgProjectJoinEntity = AsusgProject::find()
                ->leftJoin('locomotive_series', 'asusg_project.loco_serie_id = locomotive_series.id')
                ->leftJoin('repairtypes', 'repairtypes.id = asusg_project.repair_type')
                ->select(['locomotive_series.name as lsname', 'asusg_project.loco_number', 'repairtypes.name as rtname'])
                ->where(['asusg_project.id' => $this->project_id])
                ->asArray()
                ->one();
              $this->locoserie = $asusgProjectJoinEntity['lsname'];
              $this->loconumber = $asusgProjectJoinEntity['loco_number'];
              $this->tmpText = $asusgProjectJoinEntity['rtname'];       
    }
    
// 7 . ======================================================================================================================================== 

    private function CallProcedureStartProject() {
    
        $startProjectForLocoModel = new StartProjectForLocoModel();
        $this->asusgProjectId = $startProjectForLocoModel->execute($this->initiator, $this->projecttype, $this->locoserie, $this->loconumber, $this->projectcomment);
        
        if ( null == $this->asusgProjectId) {
            return false;
        }
        return true;;
        /*
        $result = \Yii::$app->db->createCommand('CALL coverage_dis_prof(:initiator, :projecttype , @projectId , :loconumber ,:projectcomment);')
            ->bindValue(':initiator' , $this->initiator)
            ->bindValue(':projecttype', $this->projecttype)
            ->bindValue(':loconumber', $this->loconumber)
            ->bindValue(':projectcomment', $this->projectcomment)
            ->execute();
        $rez=Yii::$app->db->createCommand("SELECT @projectId;")->queryScalar();
        */
        
    }
   
// 8 . =========================================================================================================================================

    private function addAsusgProjectId(){
    
        $projectsLocotechEntity = ProjectsLocotech::findOne($this->asusgProjectId);
        $projectsLocotechEntity->asusg_project_id = $this->project_id;
        $projectsLocotechEntity->save();
    }
   

// 9 . =========================================================================================================================================
    
    public function daclareProperties() {
    
        $asusgProjectEntity = AsusgProject::findOne()
                                ->select(['coalesce(section_a, 0) as section_a', 'coalesce(section_b, 0) as section_b', 'coalesce(section_c, 0) as section_c'])
                                ->where(['id' => 116])
                                ->asArray()
                                ->one();
        $this->s1v = $asusgProjectEntity['section_a'];
        $this->s2v = $asusgProjectEntity['section_b'];
        $this->s3v = $asusgProjectEntity['section_c'];
        
        
        if ($this->s2v < $this->s1v) {
            $this->tmpsv = $this->s1v;
            $this->s1v = $this->s2v;
            $this->s2v = $this->tmpsv;
            $this->tmps = $this->s1;
            $this->s1 = $this->s2;
            $this->s2 = $this->tmps;
        }

        if ($this->s3v < $this->s2v) {
            $this->tmpsv = $this->s2v;
            $this->s2v = $this->s3v;
            $this->s3v = $this->tmpsv;
            $this->tmps = $this->s2;
            $this->s2 = $this->s3;
            $this->s3 = $this->tmps;
        }

        if ($this->s2v < $this->s1v) {
            $this->tmpsv = $this->s1v;
            $this->s1v = $this->s2v;
            $this->s2v = $this->tmpsv;
            $this->tmps = $this->s1;
            $this->s1 = $this->s2;
            $this->s2 = $this->tmps;
        }
    }
    
// 10. =========================================================================================================================================
 
    public function wwwer() {
    
        if ($this->s1v > 0 ) {
            
            $done = 0;
            foreach ( $this->cur_job as $jobId) {
                
                if ( $done = 1 ) {
                    break;
                }
                if ( $jobId->asusg_employee_id1 != 0 || $jobId->asusg_employee_id2 != 0 || $jobId->asusg_employee_id3 != 0) {
                    $asusgAddJobToProjectForSectionByEmployeeModel = new AsusgAddJobToProjectForSectionByEmployeeModel();
                    $asusgAddJobToProjectForSectionByEmployeeModel->execute($this->initiator, $this->project_id, $this->id, $this->asusg_employee_id1);
                } else {
                    AsusgAddJobToProjectForSectionModel::execute($this->initiator, $this->project_id, $this->id, $this->asusg_employee_id1);
                }
            }
        }
        if ( $this->s2v > 0 ) {
        
            $done = 0;
            foreach( $this->cur_job as $jobId) {
                if ( $done = 1 ) {
                    break;
                }
                if ( $jobId->asusg_employee_id1 != 0 || $jobId->asusg_employee_id2 != 0 || $jobId->asusg_employee_id3 != 0) {
                    $asusgAddJobToProjectForSectionByEmployeeModel = new AsusgAddJobToProjectForSectionByEmployeeModel();
                    $asusgAddJobToProjectForSectionByEmployeeModel->execute($this->initiator,$this->project_id,$jobId->id,$jobId->asusg_employee_id2);
                } else {
                    AsusgAddJobToProjectForSectionModel::execute($this->initiator,$this->project_id,$jobId->id,$jobId->asusg_employee_id2);
                }
            }
        }
        
        if ( $this->s3v > 0 ) {
        
            $done = 0;
            foreach( $this->cur_job as $jobId) {
                if ( $done = 1 ) {
                    break;
                }
                if ( $jobId->asusg_employee_id1 != 0 || $jobId->asusg_employee_id2 != 0 || $jobId->asusg_employee_id3 != 0) {
                    $query = "call asusg_add_job_to_project_for_section_by_employee(" . $this->initiator . ", " . $this->project_id . ", " . $jobId->id . ", " . $jobId->asusg_employee_id3 .")";
                    $asusgAddJobToProjectForSectionByEmployeeModel = new AsusgAddJobToProjectForSectionByEmployeeModel();
                    $asusgAddJobToProjectForSectionByEmployeeModel->execute($this->initiator,$this->project_id,$jobId->id,$jobId->asusg_employee_id3);
                } else {
                    AsusgAddJobToProjectForSectionModel::execute($this->initiator,$this->project_id,$jobId->id,$jobId->asusg_employee_id3);
                }
            }
        }
        
    
    }
    
    private function SetStatusProject() {
        $AsusgProject = AsusgProject::findOne($this->project_id);
        $AsusgProject->status = 2;
        $AsusgProject->started = date('Y-m-d H:i:s');
        if($AsusgProject->save()) {
            return false;
        }
        return true;
    }
    
    private function SetTasksToProject() {
        ProjectsLocotech::findOne($this->project_id);
        update projects_locotech
		set tasks = coalesce(right(tasks, length(tasks) - 1), '')
        where id = projectId;

	insert into text_log (msg) values (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' подготовлен. Упорядочиваю команды.'));
    
    }
    
    private function RegularizeTasks() {
    
        open cur_task;
        ct_loop: loop
            set done = 0;
            fetch cur_task into taskId;
            if done = 1 then leave ct_loop; end if;

            set counter = 0;
            open cur_command;
            cc_loop: loop
                set done = 0;
                fetch cur_command into commandId;
                if done = 1 then leave cc_loop; end if;
                set counter = counter + 1;
                update tasks
                    #set project_id = 0, priority = counter
                    set priority = counter, project_id = 0
                    where id = commandId;
            end loop;
            close cur_command;
        end loop;
        close cur_task;
    }
    
    public function startProjectForLoco() {
    
        /*
        call start_project_for_loco(initiator, projecttype, projectId, locoserie, loconumber, projectcomment);
        if ( null == $projectId) {
            return false; 
        }
        if (projectId is null) then leave build_project; end if;
        update projects_locotech
            set asusg_project_id = project_id
            where id = projectId;
            */
    }
    
    public function setUpdateProjects() {
    
    
        $result = [];
        $query  = "UPDATE asusg_project SET status = 2, started = NOW()
                                       WHERE id = " . $this->project_id;
                                       
        $query2 = "UPDATE projects_locotech SET tasks = coalesce(right(tasks, length(tasks) - 1), '')
                                            WHERE id = " . $this->projectId;
                                            
        $query3 = "INSERT INTO text_log (msg) VALUES (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' подготовлен. Упорядочиваю команды.'))";                                    
        
        var_dump($query,'<br>');
        var_dump($query2,'<br>');
        var_dump($query3,'<br>');
        //Yii::$app->db->createCommand($query)->queryAll();
        //Yii::$app->db->createCommand($query2)->queryAll();
        //Yii::$app->db->createCommand($query3)->queryAll();
        
        
        return $result;

    }
    
    public function organizeСommands(){
        
        #Упорядочить команды в задачах по project_id, установить project_id = 0.
        foreach ($this->AsusgCurTask() as $taskId) {
            
            $counter = 0;
            foreach ($this->AsusgCurCommand($taskId["id"]) as $commandId) {
                
                $query = "UPDATE tasks
                        SET priority = " . $counter . ", project_id = 0
                        WHERE id = " . $commandId['id'];
                var_dump($query,'<br>');
                //$res = Yii::$app->db->createCommand($query)->queryAll();
                $counter++;
            }
        }
        
        $query = "INSERT INTO text_log (msg) VALUES (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' запущен в работу.'))";
        /*
        Yii::$app->db->createCommand($query)->queryAll();
        
        open cur_task;
        ct_loop: loop
            set done = 0;
            fetch cur_task into taskId;
            if done = 1 then leave ct_loop; end if;

            set counter = 0;
            open cur_command;
            cc_loop: loop
                set done = 0;
                fetch cur_command into commandId;
                if done = 1 then leave cc_loop; end if;
                set counter = counter + 1;
                update tasks
                    #set project_id = 0, priority = counter
                    set priority = counter, project_id = 0
                    where id = commandId;
            end loop;
            close cur_command;
        end loop;
        close cur_task;

        */
        
    }
    
    public function text_log(){
    
    // part #12 
    
        $query = "insert into text_log (msg) values (concat(cast(initiator as char), ': Проект ', cast(project_id as char), '/', cast(projectId as char), ' запущен в работу.'))";
        $result = Yii::$app->db->createCommand($query)->queryAll();
    }

}

?>