Smopp_v2/api/models/procedure/AsusgAddJobToProjectForSectionModel.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 AsusgAddJobToProjectForSectionModel extends Model 
{

    private $aid1;
    private $aid2; 
    private $aid3;
    private $jid1;
    private $jid2;
    private $jid3;
    private $taskId;
    private $cur_aid;
    private $cur_jid;
    private $tmpjtId;
    private $section;
    private $jobcount;
    private $tasktype;
    private $projectId;
    private $jobtypeId;
    private $companyId;
    private $origorder;
    private $initiator;
    private $tasktypeId;
    private $whilecount;
    private $cur_status;
    private $project_id;
    private $taskCreated;
    private $cur_priority;
    private $tmpUuid = '';
    private $tmpText = '';
    private $assignedTime;
    private $job2launch_id;
    private $projecttypeId;
    private $jobtypeName = '';
    private $additionalText = '';
    

    public static function execute($initiator, $project_id, $job2launch_id, $section) {
        
        self::$_instance = new self();
        
        self::$_instance->project_id = $project_id;
        self::$_instance->initiator; = $initiator;
        self::$_instance->job2launch_id = $job2launch_id;
        self::$_instance->section = $section;
        
        // 1.
        
        if( !self::$_instance->getCompanyId() ) {
            return false;
        }
        
        // 2.
        
        if( !self::$_instance->getTasktype() ) {
            return false;
        }
        
        // 3.
        
        if( !self::$_instance->getJobTypeId() ) {
            return false;
        }
        
        // 4.
        
        if( !self::$_instance->getProjectId() ) {
            return false;
        }
        
        // 5.
        
        if( !self::$_instance->getAsusgProgecttypeId() ) {
            return false;
        }
        
        // 6.
        
        if( !self::$_instance->getAdditionalText() ) {
            return false;
        }
        
        // 7.
        
        if( !self::$_instance->declareProperies() ) {
            return false;
        }
        
        // 8 .
        
        if( !self::$_instance->createTasks() ) {
            return false;
        }
        
        
        return true;
    }
    
 // declare   
    
    private function getListJobTypes( $projecttypeId ) 
    {
        $result = [];
        $query = '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 = ' . self::$_instance->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' => self::$_instance->initiator]):
        self::$_instance->companyId = $accountsEntity->id;
        if ( null == self::$_instance->companyId) {
            return false;
        }
        return true;
    }

// 2.    
    
    private function getTasktype() 
    {
       $tasktypesEntity = Tasktypes::find()
                    ->select('max(id) AS id')
                    ->where(['description' => 'Мэппинг.', 'company' => self::$_instance->companyId, 'letter' => self::$_instance->section])
                    ->asArray()
                    ->one();
        self::$_instance->tasktype = $tasktypesEntity['id'];
        if ( null == self::$_instance->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' => self::$_instance->job2launch_id])
                                        ->andWhere(['like', 'asusg_jobplan.sections',  new Expression("concat('%', coalesce(" . self::$_instance->section . ", 'Z'), '%')") ])
                                        ->asArray()
                                        ->one();
        self::$_instance->jobtypeId = $asusgProjectJoinEntity['id'];
        if ( null == self::$_instance->jobtypeId ) {
            return false;
        }
        return true;
    }
    
// 4.    
    
    private function () 
    {
        $projectsLocotechEntity = ProjectsLocotech::find()->select(['max(id) as id'])->where(['asusg_project_id' => self::$_instance->project_id])->asArray()->one();
        self::$_instance->projectId = $projectsLocotechEntity['id'];
        if ( null == self::$_instance->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' => self::$_instance->project_id])
                                ->one();
                        
        self::$_instance->projecttypeId = $asusgProjectEntity['id'];
        if ( null == self::$_instance->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.name'])
                                ->where(['asusg_job2launch.id' => self::$_instance->job2launch_id])
                                ->asArray()
                                ->one();
        self::$_instance->additionalText = $asusgProjectEntity['id'];
        if ( null == self::$_instance->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' => self::$_instance->job2launch_id])
                                    ->one();
        if ( null == $asusgProjectEntity){
            return false;
        }
                                   
        self::$_instance->aid1 = $asusgProjectEntity->asusg_employee_id1;
        self::$_instance->jid1 = $asusgProjectEntity->jobtype_id1;
        self::$_instance->aid2 = $asusgProjectEntity->asusg_employee_id2;
        self::$_instance->jid2 = $asusgProjectEntity->jobtype_id2;
        self::$_instance->aid3 = $asusgProjectEntity->asusg_employee_id3;
        self::$_instance->jid3 = $asusgProjectEntity->jobtype_id3; 
        return true; 

    }
    
// 8.
    
    private function createTasks() 
    {
        $listJobTypes = self::$_instance->getListJobTypes(self::$_instance->projecttypeId);
        
        foreach( $listJobTypes as $job) {
        
            if( null = $job['jobtypeId'] ) {
                $job['jobtypeId'] = 0;
            }
            
            self::$_instance->jobtypeName = self::$_instance->getJobtypeName();           // 8.1 
            $whilecount = 1;
            while( $whilecount <= $job['jobcount']) {
                
                self::$_instance->switchJob( $whilecount, $job['jobtypeId'] );                      // 8.2
                self::$_instance->tmpText = self::$_instance->jobtypeName . ' ' . $whilecount . '/' . self::$_instance->section);
                self::$_instance->cur_status = 1;
                if ( 0 <> self::$_instance->cur_aid ) {
                    self::$_instance->tmpText = self::$_instance->jobtypeName . ' ' . self::$_instance->cur_aid . '/' . self::$_instance->section);
                    self::$_instance->cur_status = 2;
                }
                self::$_instance->taskId = self::$_instance->getTaskId();                 // 8.3
                if ( null == self::$_instance->taskId ) {
                    self::$_instance->tmpUuid = self::$_instance->setLabel();             // 8.4
                    self::$_instance->createJobTasks($job);                    // 8.5 !!!
                
                    $tasksEntityTemp = Tasks::find()->select(['max(id) AS id'])->where(['input_id' => self::$_instance->projectId, 'text' => self::$_instance->tmpUuid, 'parent_id' => 0)])-asArray()->one();
                    self::$_instance->taskId = $tasksEntityTemp['id']
                
                    $tasksEntity = Tasks::findOne(self::$_instance->taskId);
                    $tasksEntity->text = self::$_instance->tmpText;
                    $tasksEntity->save();
                    
                    $projectsLocotechEntity = ProjectsLocotech::findOne(self::$_instance->projectId);
                    $projectsLocotechEntity->tasks = $projectsLocotechEntity->tasks . ',' . self::$_instance->taskId; 
                    $projectsLocotechEntity->save();
                    
                }
                
                $tasksEntityPr = Tasks::find()->select(['1 + coalesce(max(priority),0) AS priority'])->where(['input_id' => self::$_instance->projectId,'parent_id' => self::$_instance->taskId])->asArray()->one();
                self::$_instance->cur_priority = $tasksEntityPr['priority'];
                
                self::$_instance->tmpText = self::$_instance->tmpText .', '. self::$_instance->additionalText;
                
                self::$_instance->createTasksExtra($job);                      // 8.6 !!!!
                
                $tasksEntityCreated = Tasks::find()->select('max(id) AS id')->where(['parent_id' => self::$_instance->taskId,'type' => $job['tasktypeId'],'text' => tmpText])->asArray()->one();
                self::$_instance->taskCreated = $tasksEntityCreated['id'];
                $asusgAddCheckpointsToTaskModel = AsusgAddCheckpointsToTaskModel::execute(self::$_instance->initiator, self::$_instance->taskCreated);
                $whilecount++;
                
            }
        }
    }
}

// 8.1 
    private function getJobtypeName()
    {
        $result = '';
        $jobtypesEntity = Jobtypes::find()
                        ->select(["concat(name, ' #', cast(id as char), '#') AS name"])->where(['id' => self::$_instance->jobtypeId])->asArray()->one();
        if ( null != $jobtypesEntity['name']) {
            $result = $jobtypesEntity['name'];     
        }
        return $result;
    }

// 8.2 

    private function switchJob( $whilecount, $jobtypeId )
    {
        if ( 1 == $whilecount) {
            switch ($whilecount) {
                case self::$_instance->jid1:
                    self::$_instance->cur_aid = self::$_instance->aid1;
                    self::$_instance->cur_jid = self::$_instance->jid1;
                    self::$_instance->assignedTime = date('Y-m-d H:i:s');
                case self::$_instance->jid2:
                    self::$_instance->cur_aid = self::$_instance->aid2;
                    self::$_instance->cur_jid = self::$_instance->jid2;
                    set assignedTime = date('Y-m-d H:i:s');
                case self::$_instance->jid3:
                    self::$_instance->cur_aid = self::$_instance->aid3;
                    self::$_instance->cur_jid = self::$_instance->jid3;
                    self::$_instance->assignedTime = date('Y-m-d H:i:s');
                default:
                    self::$_instance->cur_aid = 0;
                    self::$_instance->cur_jid = 0;
                    self::$_instance->assignedTime = null;
            }
            
        } else {
            switch ($whilecount) {
                case self::$_instance->jid3 then
                    self::$_instance->cur_aid = self::$_instance->aid3;
                    self::$_instance->cur_jid = self::$_instance->jid3;
                    set assignedTime = date('Y-m-d H:i:s');
                case self::$_instance->jid2:
                    self::$_instance->cur_aid = self::$_instance->aid2;
                    self::$_instance->cur_jid = self::$_instance->jid2;
                    set assignedTime = date('Y-m-d H:i:s');
                case self::$_instance->jid1:
                    self::$_instance->cur_aid = self::$_instance->aid1;
                    self::$_instance->cur_jid = self::$_instance->jid1;
                    self::$_instance->assignedTime = date('Y-m-d H:i:s');
                default:
                    self::$_instance->cur_aid = 0;
                    self::$_instance->cur_jid = 0;
                    self::$_instance->assignedTime = null;
            }       
            
        }
        
    } 
    
    // 8.3 
   
    private function getTaskId()
    {
        $result = '';
        $tasksEntity = Tasks::find()
                        ->select(['max(id) AS id'])
                        ->where(['input_id' => self::$_instance->projectId,'text' => self::$_instance->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              = self::$_instance->cur_status;
        $tasksEntity->parent_id           = 0;
        $tasksEntity->type                = self::$_instance->tasktype; 
        $tasksEntity->input_id            = self::$_instance->projectId;
        $tasksEntity->text                = self::$_instance->tmpUuid;
        $tasksEntity->project_id          = 0;
        $tasksEntity->assignees_arr       = self::$_instance->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            = self::$_instance->assignedTime;
        
        $tasksEntity->save();
    }
    
// 8.6  

    private function createTasksExtra($job)
    {
        $tasksEntityCr = new Tasks();
        $tasksEntityCr->status              = self::$_instance->cur_status;
        $tasksEntityCr->parent_id           = self::$_instance->taskId;
        $tasksEntityCr->type                = $job['tasktypeId'];
        $tasksEntityCr->input_id            = self::$_instance->projectId;
        $tasksEntityCr->text                = self::$_instance->tmpText;
        $tasksEntityCr->project_id          = $job['origorder'];
        $tasksEntityCr->assignees_arr       = self::$_instance->cur_aid;
        $tasksEntityCr->priority            = self::$_instance->cur_priority
        $tasksEntityCr->assignedby          = 0
        $tasksEntityCr->created             = date('Y-m-d H:i:s');
        $tasksEntityCr->asusg_job_mapped_id = $job['job2launch_id'];
        $tasksEntityCr->assigned            = self::$_instance->assignedTime;
        $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    
	
	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;
	#select concat(ajp.name, ' (', coalesce(ajl.asusg_job_uuid, ''), ')') into additionalText
	
6
	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
			if whilecount = 1 then
			case jobtypeId
				when jid1 then
					set cur_aid = aid1;
					set cur_jid = jid1;
                    set assignedTime = NOW();
				when jid2 then
					set cur_aid = aid2;
					set cur_jid = jid2;
                    set assignedTime = NOW();
				when jid3 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;
            else
			case jobtypeId
				when jid3 then
					set cur_aid = aid3;
					set cur_jid = jid3;
                    set assignedTime = NOW();
				when jid2 then
					set cur_aid = aid2;
					set cur_jid = jid2;
                    set assignedTime = NOW();
				when jid1 then
					set cur_aid = aid1;
					set cur_jid = jid1;
                    set assignedTime = NOW();
                else
					set cur_aid = 0;
					set cur_jid = 0;
                    set assignedTime = null;
			end case;
            end if;

			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)
					values (cur_status, 0, tasktype, projectId, tmpUuid, 0, cur_aid, 0, 0, NOW(), jobtypeId, assignedTime);
				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)
				values (cur_status, taskId, tasktypeId, projectId, tmpText, origorder, cur_aid, cur_priority, 0, NOW(), job2launch_id, assignedTime);
			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

*/