Smopp_v2/api/models/procedure/AsusgAddJobToProjectForSectionByEmployeeModel.php

<?php

namespace app\models\procedure;

use Yii;
use yii\base\Model;
use yii\db\Expression;
//use app\models\entity\Tasks;
use app\models\entity\AsusgJobplan;
use app\models\entity\AsusgProject;
//use app\models\entity\Accounts;
//use app\models\entity\Tasktypes;
//use app\models\entity\Projecttypes;
//use app\models\entity\ProjectsLocotech;
//use app\models\entity\ProjecttypesTasktypes;

class AsusgAddJobToProjectForSectionByEmployeeModel extends Model 
{
    private $aid1; 
    private $aid2; 
    private $aid3;
    private $jid1;
    private $jid2;
    private $jid3;
    private $taskId;
    private $tmpjtId;
    private $cur_aid;
    private $cur_jid;
    private $section;
    private $tasktype;
    private $jobcount;
    private $initiator;
    private $projectId;
    private $jobtypeId;
    private $companyId;
    private $origorder;
    private $tasktypeId;
    private $cur_status;
    private $whilecount;
    private $project_id;
    private $taskCreated;
    private $cur_priority;
    private $tmpText = '';
    private $tmpUuid = '';
    private $projecttypeId;
    private $job2launch_id;
    private $jobtypeName = '';
    private $additionalText = '';
    
    private $assignedTime = ;
    
    public function execute( $initiator, $project_id, $job2launch_id, $section )
    {
        $this->initiator = $initiator; 
        $this->project_id = $project_id;
        $this->job2launch_id = $job2launch_id;
        $this->section = $section;
    
        // 1,
        if( !$this->getCompanyId() ) {
            return false;
        }
        // 2,
        if( !$this->getMappingTasktype() ) {
            return false;
        }
        
        // 3,

        if( !$this->getJobTypeId() ) {
            return false;
        }
        
        // 4,
        
        if( !$this->getProjectId() ) {
            return false;
        }
        
        // 5,
        
        if( !$this->getAsusgProgecttypeId() ) {
            return false;
        }
        
        // 6,
        
        if( !$this->getAdditionalText() ) {
            return false;
        }
        
        // 7,
        if( !$this->declareProperies() ) {
            return false;
        }
        
        // 8,
        if( !$this->createTasks() ) {
            return false;
        }
        
        return true;
        
        
    }
    
    private function getListJobTypes( $projecttypeId )
    {
        $result = [];
        $query = 'select aj2c.smopp_cmd_id AS tasktypeId, aj2c.jobtype_id AS jobtypeId, aj2c.cnt AS jobcount, aj2c.original_order AS origorder
                    from 
            
                        (select distinct max(id) maxid, asusg_job_id, smopp_cmd_id, jobtype_id, count(jobtype_id) cnt, original_order
                            from asusg_job2command
                            where asusg_projecttype_id = ' . $projecttypeId . '
                            group by asusg_job_id, smopp_cmd_id, jobtype_id, job_order
                            order by jobtype_id, id, job_order) aj2c
                            
                    left join asusg_job2launch ajl
                    
                        on aj2c.asusg_job_id = ajl.asusg_jobplan_id
                        where ajl.id = '.$this->job2launch_id.'
                        order by aj2c.maxid, aj2c.jobtype_id, ajl.id';
            
        $resultQuery = Yii::$app->db->createCommand($query )->queryAll();  
        if ( is_array($resultQuery) and !empty($resultQuery)){
        $result = $resultQuery;
        }
        return $result; 
    }
    
// 1.

    private function getCompanyId()
    {
        $accountsEntity = Accounts::find(['id' => $this->initiator]):
        $this->companyId = $accountsEntity->id;
        if ( null == $this->companyId) {
            return false;
        }
        return true;
    }

// 2.    
    
    private function getMappingTasktype()
    {
        $tasktypesEntity = Tasktypes::find()->select('max(id) as id')->where(['description' => 'Мэппинг.', 'company' => $this->companyId , 'letter' => $this->section])->asArray()->one();
        $this->tasktype = $tasktypesEntity['id'];
        if ( null == $this->tasktype) {
            return false;
        }
        return true;
    }
    
// 3.    

    private function getJobTypeId()
    {
        $asusgProjectJoinEntity = AsusgJobplan::find()
                                        ->leftJoin('asusg_job2launch as ajl', 'asusg_jobplan.id = ajl.asusg_jobplan_id')
                                        ->select(['asusg_jobplan.id'])
                                        ->where(['ajl.id' => $this->job2launch_id])
                                        ->andWhere(['like', 'asusg_jobplan.sections',  new Expression('concat("%", coalesce("АБ", "Z"), "%")') ])
                                        ->asArray()
                                        ->one();
        $this->jobtypeId = $asusgProjectJoinEntity['id'];
        if ( null == $this->jobtypeId ) {
            return false;
        }
        return true;

    }
    
// 4.   

    private function getProjectId()
    {
        $projectsLocotechEntity = ProjectsLocotech::find()->select(['max(id) as id'])->where(['asusg_project_id' => $this->project_id])->asArray()->one();
        $this->projectId = $projectsLocotechEntity['id'];
        if ( null == $this->projectId){
            return false;
        }
        return true;
    }
    
//  5. 

    private function getAsusgProgecttypeId()
    {
        $asusgProjectEntity = AsusgProject::find()
                                ->leftJoin('asusg_projecttype AS apt','asusg_project.repair_type = apt.repair_type and asusg_project.loco_serie_id = apt.loco_serie_id')
                                ->select(['apt.id'])
                                ->where(['asusg_project.id' => $this->project_id])
                                ->one();
                        
        $this->projecttypeId = $asusgProjectEntity['id'];
        if ( null == $this->projecttypeId){
            return false;
        }
        return true;
    }

// 6.    
  
    private function getAdditionalText()
    {
        $asusgProjectEntity = AsusgJob2Launch::find()
                                ->leftJoin('asusg_jobplan AS ajp','asusg_job2launch.asusg_jobplan_id = ajp.id')
                                ->select(['ajp.id'])
                                ->where(['asusg_job2launch.id' => $this->project_id])
                                ->asArray()
                                ->one();
        $this->additionalText = $asusgProjectEntity['id'];
        if ( null == $this->additionalText){
            return false;
        }
        return true;
    }
    
// 7.     
    
    private function declareProperies()
    {
        $asusgProjectEntity = AsusgJob2Launch::find()
                                    ->select(['asusg_employee_id1', 'jobtype_id1','asusg_employee_id2', 'jobtype_id2','asusg_employee_id3', 'jobtype_id3'])
                                    ->where(['id' => $this->job2launch_id])
                                    ->one();
        if ( null == $asusgProjectEntity){
            return false;
        }
                                   
        $this->aid1 = $asusgProjectEntity->asusg_employee_id1;
        $this->jid1 = $asusgProjectEntity->jobtype_id1;
        $this->aid2 = $asusgProjectEntity->asusg_employee_id2;
        $this->jid2 = $asusgProjectEntity->jobtype_id2;
        $this->aid3 = $asusgProjectEntity->asusg_employee_id3;
        $this->jid3 = $asusgProjectEntity->jobtype_id3; 
        return true;                             
            
    }
  
// 8.   
    
    private function createTasks()
    {
        $listJobTypes = $this->getListJobTypes($this->projecttypeId);
        foreach( $listJobTypes as $job) {
            if( null = $job['jobtypeId'] ) {
                $job['jobtypeId'] = 0;
            }
            $this->jobtypeName = $this->getJobtypeName();           // 8.1 
            $whilecount = 1;
            while( $whilecount <= $job['jobcount']) {
            
                $this->switchJob($whilecount);                      // 8.2
                $this->tmpText = $this->jobtypeName . ' ' . $whilecount . '/' . $this->section);
                $this->cur_status = 1;
                if ( 0 <> $this->cur_aid ) {
                    $this->tmpText = $this->jobtypeName . ' ' . $this->cur_aid . '/' . $this->section);
                    $this->cur_status = 2;
                }
                $this->taskId = $this->getTaskId();                 // 8.3
                if ( null == $this->taskId ) {
                    $this->tmpUuid = $this->setLabel();             // 8.4
                    $this->createJobTasks($job);                    // 8.5
                
                    $tasksEntityTemp = Tasks::find()->select(['max(id) AS id'])->where(['input_id' => $this->projectId, 'text' => $this->tmpUuid, 'parent_id' => 0)])-asArray()->one();
                    $this->taskId = $tasksEntityTemp['id']
                
                    $tasksEntity = Tasks::findOne($this->taskId);
                    $tasksEntity->text = $this->tmpText;
                    $tasksEntity->save();
                    
                    $projectsLocotechEntity = ProjectsLocotech::findOne($this->projectId);
                    $projectsLocotechEntity->tasks = $projectsLocotechEntity->tasks . ',' . $this->taskId; 
                    $projectsLocotechEntity->save();
                    
                }
                
                $tasksEntityPr = Tasks::find()->select(['1 + coalesce(max(priority),0) AS priority'])->where(['input_id' => $this->projectId,'parent_id' => $this->taskId])->asArray()->one();
                $this->cur_priority = $tasksEntityPr['priority'];
                
                $this->tmpText = $this->tmpText .', '. $this->additionalText;
                
                $this->createTasksExtra($job);                      // 8.6
                
                $tasksEntityCreated = Tasks::find()->select('max(id) AS id')->where(['parent_id' => $this->taskId,'type' => $job['tasktypeId'],'text' => tmpText])->asArray()->one();
                $this->taskCreated = $tasksEntityCreated['id'];
                $asusgAddCheckpointsToTaskModel = AsusgAddCheckpointsToTaskModel::execute($this->initiator, $this->taskCreated);
                $whilecount++;
            }
        }
    }
 
// 8.1 
    private function getJobtypeName()
    {
        $result = '';
        $jobtypesEntity = Jobtypes::find()
                        ->select(["concat(name, ' #', cast(id as char), '#') AS name"])->where(['id' => $this->jobtypeId])->asArray()->one();
        if ( null != $jobtypesEntity['name']) {
            $result = $jobtypesEntity['name'];     
        }
        return $result;
    }
    
// 8.2 

    private function switchJob($whilecount)
    {
        switch ($whilecount) {
            case 1:
                $this->cur_aid = $this->aid1;
                $this->cur_jid = $this->jid1;
                $this->assignedTime = date('Y-m-d H:i:s');
                break;
            case 2:
                $this->cur_aid = $this->aid2;
                $this->cur_jid = $this->jid2;
                $this->assignedTime = date('Y-m-d H:i:s');
                break;
            case 3:
                $this->cur_aid = $this->aid3;
                $this->cur_jid = $this->jid3;
                $this->assignedTime = date('Y-m-d H:i:s');
                break;
            default:
                $this->cur_aid = 0;
                $this->cur_jid = 0;
                $this->assignedTime = null;
        }
    } 
    
// 8.3 
   
    private function getTaskId()
    {
        $result = '';
        $tasksEntity = Tasks::find()
                        ->select(['max(id) AS id'])
                        ->where(['input_id' => $this->projectId,'text' => $this->tmpText,'parent_id' => 0])
                        ->asArray()
                        ->one();
                        
        if ( null != $tasksEntity['id']) {
            $result = $tasksEntity['id'];     
        }
        return $result;
    }
    
// 8.4 
    
    private function setLabel()
    {
        $uuid = Yii::$app->db->createCommand('select UUID() as uuid')->queryOne();
        if ( $uuid ) {
            return $uuid["uuid"];
        }
    }

// 8.5 

    private function createJobTasks($job) 
    {
        $tasksEntity  = new Tasks();
        $tasksEntity->status              = $this->cur_status;
        $tasksEntity->parent_id           = 0;
        $tasksEntity->type                = $this->tasktype; 
        $tasksEntity->input_id            = $this->projectId;
        $tasksEntity->text                = $this->tmpUuid;
        $tasksEntity->project_id          = 0;
        $tasksEntity->assignees_arr       = $this->cur_aid;
        $tasksEntity->priority            = 0;
        $tasksEntity->assignedby          = 0;
        $tasksEntity->created             = date('Y-m-d H:i:s');
        $tasksEntity->asusg_job_mapped_id = $job['jobtypeId'];
        $tasksEntity->assigned            = $this->assignedTime;
        $tasksEntity->preferred_assignee  = $this->cur_aid;
        $tasksEntity->save();
    }
    
// 8.6

    private function createTasksExtra($job)
    {
        $tasksEntityCr = new Tasks();
        $tasksEntityCr->status              = $this->cur_status;
        $tasksEntityCr->parent_id           = $this->taskId;
        $tasksEntityCr->type                = $job['tasktypeId'];
        $tasksEntityCr->input_id            = $this->projectId;
        $tasksEntityCr->text                = $this->tmpText;
        $tasksEntityCr->project_id          = $job['origorder'];
        $tasksEntityCr->assignees_arr       = $this->cur_aid;
        $tasksEntityCr->priority            = $this->cur_priority
        $tasksEntityCr->assignedby          = 0
        $tasksEntityCr->created             = date('Y-m-d H:i:s');
        $tasksEntityCr->asusg_job_mapped_id = $job['job2launch_id'];
        $tasksEntityCr->assigned            = $this->assignedTime;
        $tasksEntityCr->preferred_assignee  = $this->cur_aid;
        $tasksEntityCr->save();
    }
}

/*
addjobs: BEGIN
	declare projectId integer;
    
    declare projecttypeId integer;
    declare jobtypeId integer;
	declare tasktype integer;
    declare companyId integer;
    declare origorder integer;
    
    declare taskId integer;
    declare jobtypeName varchar(255);
    declare taskCreated integer;
    
    declare tmpText varchar(255);
    declare additionalText varchar(255);
    declare tmpUuid varchar(255);

	declare tasktypeId integer;
    declare tmpjtId integer;
    declare jobcount integer;
    declare whilecount integer;
    declare cur_priority integer;

	declare cur_aid integer;
    declare cur_jid integer;
    declare cur_status integer;
    declare aid1, aid2, aid3 integer;
    declare jid1, jid2, jid3 integer;
    
    declare assignedTime datetime;
    
    declare done integer;


	declare cur_tasklist cursor for


    select aj2c.smopp_cmd_id, aj2c.jobtype_id, aj2c.cnt, aj2c.original_order
	from (select distinct max(id) maxid, asusg_job_id, smopp_cmd_id, jobtype_id, count(jobtype_id) cnt, original_order
			from asusg_job2command
			where asusg_projecttype_id = projecttypeId
            group by asusg_job_id, smopp_cmd_id, jobtype_id, job_order
            order by jobtype_id, id, job_order) aj2c
		left join asusg_job2launch ajl
            on aj2c.asusg_job_id = ajl.asusg_jobplan_id
    where 
		ajl.id = job2launch_id
    order by aj2c.maxid, aj2c.jobtype_id, ajl.id;

    
    declare continue handler for not found set done = 1;
    
1    
	#set tasktype = 734;
	select company into companyId
		from accounts
        where id = initiator;
	if companyId is null then leave addjobs; end if;
	
2	
    select max(id) into tasktype
        from tasktypes
        where description = 'Мэппинг.'
            and company = companyId
            and letter = section;
    if (tasktype is null) then leave addjobs; end if;

3
    select ajp.id into jobtypeId
        from asusg_jobplan ajp
            left join asusg_job2launch ajl on ajp.id = ajl.asusg_jobplan_id
        where ajl.id = job2launch_id
            and ajp.sections like concat('%', coalesce(section, 'Z'), '%');
    if jobtypeId is null then leave addjobs; end if;

4
	select max(p.id) into projectId
		from projects_locotech p
        where p.asusg_project_id = project_id;
	if (projectId is null) then leave addjobs; end if;

5
	select apt.id into projecttypeId
		from asusg_project ap
			left join asusg_projecttype apt on ap.repair_type = apt.repair_type and ap.loco_serie_id = apt.loco_serie_id
		where ap.id = project_id;
		
6
	#select concat(ajp.name, ' (', coalesce(ajl.asusg_job_uuid, ''), ')') into additionalText
	select ajp.name into additionalText
		from asusg_job2launch ajl
			left join asusg_jobplan ajp on ajl.asusg_jobplan_id = ajp.id
		where ajl.id = job2launch_id;
    
7    
    select asusg_employee_id1, jobtype_id1,
		asusg_employee_id2, jobtype_id2,
        asusg_employee_id3, jobtype_id3
        into aid1, jid1, aid2, jid2, aid3, jid3
        from asusg_job2launch
        where id = job2launch_id;
8
        
    open cur_tasklist;
    tl_loop: loop
		set done = 0;
		fetch cur_tasklist into tasktypeId, jobtypeId, jobcount, origorder;
        #insert into text_log (msg) values (concat(cast(tasktypeId as char), ' ', cast(jobtypeId as char), ' ', cast(jobcount as char), ' ', section));
        if done = 1 then leave tl_loop; end if;
        
        set jobtypeId = coalesce(jobtypeId, 0);
        set jobtypeName = (select concat(name, ' #', cast(id as char), '#') from jobtypes where id = jobtypeId);

		set whilecount = 1;
        while whilecount <= jobcount do
			case whilecount
				when 1 then
					set cur_aid = aid1;
					set cur_jid = jid1;
                    set assignedTime = NOW();
				when 2 then
					set cur_aid = aid2;
					set cur_jid = jid2;
                    set assignedTime = NOW();
				when 3 then
					set cur_aid = aid3;
					set cur_jid = jid3;
                    set assignedTime = NOW();
                else
					set cur_aid = 0;
					set cur_jid = 0;
                    set assignedTime = null;
			end case;

			if cur_aid <> 0 then
				set tmpText = concat(jobtypeName, ' №', cur_aid, '/', section);
                set cur_status = 2;
			else
				set tmpText = concat(jobtypeName, ' ', whilecount, '/', section);
                set cur_status = 1;
            end if;
            set taskId = (select max(t.id)
				from tasks t
                where t.input_id = projectId
					and t.text = tmpText
					and t.parent_id = 0);
			if (taskId is null) then
				set tmpUuid = UUID();
				insert into tasks
					(status, parent_id, type, input_id, text, project_id, assignees_arr, priority, assignedby, created, asusg_job_mapped_id, assigned, preferred_assignee)
					values (cur_status, 0, tasktype, projectId, tmpUuid, 0, cur_aid, 0, 0, NOW(), jobtypeId, assignedTime, cur_aid);
				set taskId = (select max(t.id)
					from tasks t
					where t.input_id = projectId
						and t.text = tmpUuid
						and t.parent_id = 0);
                update tasks
					set text = tmpText
                    where id = taskId;
				update projects_locotech
					set tasks = concat(tasks, ',', cast(taskId as char))
					where id = projectId;
            end if;
            set cur_priority = (select 1 + coalesce(max(priority), 0)
				from tasks t
                where t.input_id = projectId
						and t.parent_id = taskId);
			set tmpText = concat(tmpText, ', ', additionalText);
			insert into tasks
				(status, parent_id, type, input_id, text, project_id, assignees_arr, priority, assignedby, created, asusg_job_mapped_id, assigned, preferred_assignee)
				values (cur_status, taskId, tasktypeId, projectId, tmpText, origorder, cur_aid, cur_priority, 0, NOW(), job2launch_id, assignedTime, cur_aid);
			select max(id) into taskCreated
				from tasks
				where parent_id = taskId
					and type = tasktypeId
                    and text = tmpText;
            call asusg_add_checkpoints_to_task(initiator, taskCreated);
			set whilecount = whilecount + 1;
        end while;
        
    end loop;
    close cur_tasklist;
END
*/