<?php

/**
 *	This file is part of Breeding4Rice.
 *
 *	Breeding4Rice is a free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.
 *
 *	Breeding4Rice is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.
 *
 *	You should have received a copy of the GNU General Public License
 *	along with Breeding4Rice.  If not, see <http://www.gnu.org/licenses/>
 */

Yii::import('gms.models.Germplasm');
Yii::import('gms.models.Names');

class CreateProgeniesCommand extends CConsoleCommand{

        public $userId;
        public $creationTimestamp;

        public function run(){

            $gmworker = new GearmanWorker();
            $gmworker->addServer();

            // start of function

            $gmworker->addFunction("createProgenies", function($job){

                $wJob = json_decode($job->workload());

                //set globals
                $this->userId = $wJob->userId;
                $this->creationTimestamp = 'now()';

                $params = $wJob->workload;
                $transaction = CreateProgenyTransaction::model()->findByPk($params->transactionId);
                $baseUrl = $wJob->baseUrl;

                //get transaction terminal records
                $sql = "
                    select * from operational_data_terminal.create_progeny_terminal
                    where transaction_id = $transaction->id
                    and is_void = false
                ";

                $terminalRecords = Yii::app()->db->createCommand($sql)->queryAll();

                $this->updateTransaction($transaction->id, 'In Progress', 'false');

                //loop through transaction terminal records
                foreach($terminalRecords as $terminalRecord){

                    $productId = '';
                    //create product
                    if($terminalRecord['will_create_progenies']){
                        $productId = $this->createProduct($terminalRecord);
                        $this->updateTerminal($terminalRecord, 'Product Created');
                    }

                    //create seed lot
                    if($terminalRecord['will_create_seedlots']){

                        if($productId == ''){
                            $productId = $this->getProductByIdDesignation(trim($terminalRecord['designation']));
                        }

                        $newGid = $this->createGermplasm($terminalRecord, $productId);
                        $this->createProductGid($terminalRecord,$productId,$newGid);
                        $seedStorageId = $this->createSeedStorage($terminalRecord, $productId, $newGid);
                        $this->updateTerminal($terminalRecord, 'Seedlot Created');

                    }

                    $this->addFamilyRecord($terminalRecord, $productId);

                    $product = Product::model()->findByPk($productId);

                    echo $product->designation." created! \n";

                    $this->updateTerminal($terminalRecord, 'Finished');

                }

                $this->updateTransaction($transaction->id, 'Finished', 'true');
            });


            while($gmworker->work())
            {
                if ($gmworker->returnCode() != GEARMAN_SUCCESS)
                {
                        echo "return_code: " . $gmworker->returnCode() . "\n";
                        break;
                }
            }
        }

        public function updateTerminal($terminalRecord, $status){
            $terminalRecordId = $terminalRecord['id'];

            $sql = "
                update
                    operational_data_terminal.create_progeny_terminal
                set
                    job_status = '$status'
                where
                    id = '$terminalRecordId'
            ";

            Yii::app()->db->createCommand($sql)->execute();

        }

        public function addFamilyRecord($terminalRecord, $productId){

            $plot = Plot::model()->findByPk($terminalRecord['plot_id']);
            $entry = Entry::model()->findByPk($plot->entry_id);
            $study = Study::model()->findByPk($entry->study_id);
            $oldProduct = Product::model()->findByPk($entry->product_id);
            $product = Product::model()->findByPk($productId);

            $crossId = $this->getCrossId($productId);
            if(!$crossId){
                $crossId = 'null';
            }

            $selectionNumber = $this->getSelectionNumberFromDesignation($product->designation);

            $sql = "
                insert into master.family
                (
                    cross_id,parent_product_id,child_product_id,
                    source_study_id,source_entry_id,source_plot_id,
                    selection_number, creator_id,notes
                )
                values
                (
                    $crossId, '$oldProduct->id', '$productId',
                    '$study->id', '$entry->id', '$plot->id',
                    '$selectionNumber', '$this->userId', 'created via create progenies tool'
                )
            ";

            Yii::app()->db->createCommand($sql)->execute();

        }

        public function getSelectionNumberFromDesignation($designation){

            $selectionNumber = substr($designation, -1);

            if(is_numeric($selectionNumber)){
                return $selectionNumber;
            }else{

                //bulk
                if($selectionNumber == 'B'){
                    return 0;
                }else{
                    if($this->endsWith(trim($designation), 'RGA')){
                         $sel = substr($designation, -5, 1);

                         if(is_numeric($sel)){
                             return $sel;
                         }else{
                             return 0;
                         }
                    }else{
                        return 0;
                    }
                }

            }

        }

        public function getCrossId($newProductId){

            $product = Product::model()->findByPk($newProductId);
            $desig = $product->designation;

            //get cross id
            $sql = "
                select id from operational.cross
                where '$desig' ilike cross_name || '%'
            ";

            $crossId = Yii::app()->db->createCommand($sql)->queryScalar();
            return $crossId;

        }

        public function updateTransaction($transactionId, $status, $isCommited){

            $sql = "
                update
                    operational_data_terminal.create_progeny_transaction
                set
                    job_status = '$status',
                    is_committed = '$isCommited'
                where
                    id = '$transactionId'
            ";

            Yii::app()->db->createCommand($sql)->execute();

        }

        public function createProductGid($terminalRecord, $productId, $newGid){

            $product = Product::model()->findByPk($productId);

            $sql = "
                insert into master.product_gid
                (
                    product_id, gid, gid_type,
                    creation_timestamp, creator_id,
                    notes, is_void
                )
                values
                (
                    '$productId', '$newGid', 'derivative',
                    '$this->creationTimestamp', '$this->userId',
                    'created via create progenies tool', 'false'
                )
                returning id
            ";

            $productGidId = Yii::app()->db->createCommand($sql)->queryScalar();

            //update product with new gid
            $gidUpdateSql = "
                update master.product
                set
                    gid = '$newGid',
                    product_gid_id = '$productGidId'
                where id = '$productId'
            ";

            Yii::app()->db->createCommand($gidUpdateSql)->execute();

            //create new name
            $nameSql = "
                insert into master.product_name
                (
                    product_id, name_type, value,
                    language_code, creation_timestamp, creator_id,
                    is_void, notes, product_gid_id
                )
                values
                (
                    '$productId', 'derivative_name', '$product->designation',
                    'eng','$this->creationTimestamp', '$this->userId',
                    'false', 'created via create progenies tool', '$productGidId'
                )
            ";

            Yii::app()->db->createCommand($nameSql)->execute();

        }

        public function createSeedStorage($terminalRecord, $productId, $newGid){

            //get plot,entry,study levels
            $plot = Plot::model()->findByPk($terminalRecord['plot_id']);
            $entry = Entry::model()->findByPk($plot['entry_id']);
            $study = Study::model()->findByPk($entry->study_id);
            $program = Program::model()->findByPk($study->program_id);
            $product = Product::model()->findByPk($productId);

            $volume_data = PlotData::model()->findByAttributes(array('is_void'=>false, 'plot_id' => $plot->id, 'variable_id' => Constants::AYLD_CONT));

            if (!empty($volume_data)){
                $volume = $volume_data->value;
            }else{
                $volume = 0;
            }

            //original seed storage id
            $original_storage_id = 'null';
            $seed_storage_log_obj = SeedStorageLog::model()->findByPk($entry->seed_storage_log_id);
            if (!empty($seed_storage_log_obj)){
                $original_storage_id = $seed_storage_log_obj->seed_storage_id;
            }

            //get unit based on harvest method
            $harvestMethod = $this->getHarvestMethod($plot->id);

            if (strpos(strtolower($harvestMethod), 'panicle') !== false) {
                $unit = 'panicles';
            }else{
                $unit = 'g';
            }

            $selectionNumber = $this->getSelectionNumberFromDesignation($product->designation);

            $containerId = 'null';
            $facilityId = 'null';
            if($terminalRecord['container_id'] != ''){
                $containerId = $terminalRecord['container_id'];
            }

            if($terminalRecord['facility_id'] != ''){
                $containerId = $terminalRecord['facility_id'];
            }


            $sql = "
                insert into operational.seed_storage
                (
                    product_id, seed_lot_id, key_type,
                    seed_manager, gid, volume, unit,
                    label, original_storage_id, creation_timestamp,
                    creator_id, notes, is_void, selection_number, container_id, facility_id
                )
                values
                (
                    '$productId', '$plot->key', 'plot_key',
                    '$program->abbrev', '$newGid', '$volume', '$unit',
                    '$plot->code','$original_storage_id','$this->creationTimestamp',
                    '$this->userId','created via create progenies tool', 'false' ,'$selectionNumber', $containerId, $facilityId
                )
                returning id
            ";

            $seedStorageId = Yii::app()->db->createCommand($sql)->queryScalar();

            //create log
            $this->createSeedStorageLog($plot->key, $seedStorageId, $volume, $unit);

            //create tons of metadata
            $this->createSeedStorageMetadata($plot, $seedStorageId);

            return $seedStorageId;

        }


        public function createSeedStorageMetadata($plot, $seedStorageId){

            //get plot,entry,study levels
            $entry = Entry::model()->findByPk($plot->entry_id);
            $study = Study::model()->findByPk($entry->study_id);
            $program = Program::model()->findByPk($study->program_id);

            //study short name
            $studyShortNameVariable = Constants::STUDY_NAME_VAR;
            $plotCodeVariable = Constants::PLOT_CODE;
            $sourceStudyVariable = Constants::SOURCE_STUDY_NAME;
            $sourceStudyShortNameVariable = Constants::SOURCE_STUDY;

            $seasonSql = "
                select name from master.season where id = (
                    select season_id from operational.study where id = $study->id
                )
            ";
            $season = Yii::app()->db->createCommand($seasonSql)->queryScalar();

            $studyShortNameSql = "
                select value from operational.study_metadata
                where study_id = $study->id and variable_id = $studyShortNameVariable and is_void = false
            ";

            $studyShortName =  Yii::app()->db->createCommand($studyShortNameSql)->queryScalar();

            $harvestDate = $this->getHarvestDate($plot->id);
            $harv = explode('-',$harvestDate);
            $harvestYear = $harv[0];


            //seed storage metadata
            $sql = "
                insert into operational.seed_storage_metadata
                (seed_storage_id,variable_id,value,creation_timestamp,creator_id,notes)
                values
                ('$seedStorageId','".Constants::SOURCE_STUDY_NAME."','$study->name','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::STUDY_NAME_VAR."','$studyShortName','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::SOURCE_STUDY_SEASON."','$season','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::SRC_HARV_YEAR."','$harvestYear','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::SRC_ENTRY."','$entry->entcode','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::SOURCE_ENTRY_NUMBER."','$entry->entno','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::PLOT_CODE."','$plot->code','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::REP_VAR."','$plot->rep','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::SOURCE_PLOT."','$plot->plotno','$this->creationTimestamp', '$this->userId','created via create progenies tool'),
                ('$seedStorageId','".Constants::HARVEST_DATE."','$harvestDate','$this->creationTimestamp', '$this->userId','created via create progenies tool')
            ";

            Yii::app()->db->createCommand($sql)->execute();

        }

        public function createSeedStorageLog($plotKey, $seedStorageId, $volume, $unit){

            $sql = "
                insert into operational.seed_storage_log
                (
                    sender,seed_storage_id,encoder_id,
                    encode_timestamp,transaction_type,volume,
                    unit,event_timestamp,creation_timestamp,
                    creator_id,notes
                )
                values
                (
                    '$plotKey', '$seedStorageId','$this->userId',
                    '$this->creationTimestamp','deposit','$volume',
                    '$unit','$this->creationTimestamp','$this->creationTimestamp',
                    '$this->userId','created via create progenies tool'
                )
            ";

            Yii::app()->db->createCommand($sql)->execute();

        }


        public function createGermplasm($terminalRecord, $productId){

            //get plot,entry,study levels
            $plot = Plot::model()->findByPk($terminalRecord['plot_id']);
            $entry = Entry::model()->findByPk($plot['entry_id']);
            $study = Study::model()->findByPk($entry->study_id);

            //new product
            $newProduct = Product::model()->findByPk($productId);

            //old product
            $oldProduct = Product::model()->findByPk($entry->product_id);

            //get gpid1 gpid2
            $gpid1 = null;
            $sql = "
                select gid
                from master.product_gid
                where product_id = {$oldProduct->id}
                and gid > 0 and is_void = false
            ";
            $gpid2 = Yii::app()->db->createCommand($sql)->queryScalar();

            //get the gid of F1
            $gpid1 = null;
            $sql1 = "
                select gpid1
                from gms.germplsm
                where gid = {$gpid2}
            ";
            $gpid1 = Yii::app()->db->createCommand($sql1)->queryScalar();

            if($newProduct->generation == 'F2'){
                $gpid1 = $gpid2;
            }

            //get gdate
            $harvestDate = $this->getHarvestDate($plot->id);

            $harvestYear = '';
            if(!empty($harvestDate)){
                $hvDate = explode('-',$harvestDate);
                $gdate = $hvDate[0].$hvDate[1].$hvDate[2];
                $harvestYear = $hvDate[0];
            }

            //get harvest method
            $harvestMethod = $this->getHarvestMethod($plot->id);

            //get iris id
            $userMetadata = UserMetadata::model()->findByAttributes(array('user_id' => $this->userId, 'variable_id' => Constants::IRIS_USER_ID));
            if (!empty($userMetadata)){
                $germuid = $userMetadata->value;
            }
            else{
                $germuid = 2; //guest user id
            }

            //get glocn
            $glocn = $this->getGlocnValue($study->program_id, $study->place_id);

            //special case for RGA
            if(($this->endsWith(trim($newProduct['designation']),'B RGA')) || ($this->endsWith(trim($newProduct['designation']),'B'))){
                $bulkMethNo = 207;
            }

            $data2 = array(
                'methn' => (isset($bulkMethNo))? $bulkMethNo : $this->getMethodNo($harvestMethod),
                'gnpgs' => -1,
                'gpid1' => $gpid1,
                'gpid2' => $gpid2,
                'germuid' => $germuid, //the person who do the knowledge work [user_id in GMS]
                'lgid' => 0, //default for now
                'glocn' => $glocn, //corresponding code of user's pipeline/group in GMS [location in GMS]
                'gdate' => $gdate, //this should be the harvest date
                'gref' => 0, //default for now
                'grplce' => 0, //default for now
                'mgid' => 0, //default for now
                'ntype' => 5,
                'nstat' => 1,
                'nuid' => $germuid,
                'nval' => $newProduct['designation'],
                'nlocn' => $glocn,
                'ndate' => date("Ymd"),
                'nref' => 0,
            );

            $gidCreateSuccessful = false;

            while(!$gidCreateSuccessful){

                try{
                    $get_gid = Germplasm::create($data2);
                    $gidCreateSuccessful = true;
                }catch(Exception $e) {
                    $gidCreateSuccessful = false;
                }
            }

            $newGid = ($get_gid != null) ? $get_gid : 'NULL';
            return $newGid;

        }


        public function getMethodNo($harvestMethod){

            $methNo = 0;

            switch(strtolower($harvestMethod)){

                case 'single plant selection':
                    $methNo =  205;
                    break;
                case 'single plant selection and bulk':
                    $methNo = 205;
                    break;
                case 'bulk':
                    $methNo = 207;
                    break;
                case 'plant-specific':
                    $methNo = 205;
                    break;
                case 'single plant seed increase':
                    $methNo = 301;
                    break;
                default:
                    $methNo = 205;
                    break;
            }

            return $methNo;

        }

        public function endsWith($haystack, $needle){

            $length = strlen($needle);
            if ($length == 0) {
                return true;
            }

            return (substr($haystack, -$length) === $needle);

        }

        public function getHarvestMethod($plotId){

            //get harvest METHOD
            $hvMethVariable = Constants::HARVEST_METHOD;

            $harvestMethodSql = "
                select value from
                    operational.plot_metadata
                where
                    variable_id = '$hvMethVariable' and
                    plot_id = '$plotId'
                    and is_void = 'false'
            ";
            $harvestMethod = Yii::app()->db->createCommand($harvestMethodSql)->queryScalar();

            return $harvestMethod;

        }

        public function getGlocnValue($programId, $placeId){

            //iris loc
            $irisVarId = Constants::IRIS_LOCATION_ID;

            $glocnSql = "
                select value from
                    master.program_place_metadata
                where program_place_id in (
                    select id from
                            master.program_place
                    where
                            program_id = $programId
                            and place_id = $placeId
                            and is_void = false
                ) and variable_id = $irisVarId and is_void = false
            ";

            $glocnValue = Yii::app()->db->createCommand($glocnSql)->queryScalar();
            if(!empty($glocnValue)){
                return $glocnValue;
            }else{
                return 0;
            }

        }


        public function getHarvestDate($plotId){

            //get harvest METHOD
            $hvMethVariable = Constants::HARVEST_DATE;

            $harvestDateSql = "
                select value from
                    operational.plot_metadata
                where
                    variable_id = '$hvMethVariable' and
                    plot_id = '$plotId'
                    and is_void = 'false'
            ";
            $harvestDate = Yii::app()->db->createCommand($harvestDateSql)->queryScalar();

            return $harvestDate;

        }

        public function getProductByIdDesignation($designation){

            $sql = "
                select id from master.product
                where id = (
                    select
                        product_id
                    from
                        master.product_name
                    where
                        trim(value) = trim('$designation')
                    and is_void = false
                    limit 1
                ) and is_void = false
            ";

            $productId = Yii::app()->db->createCommand($sql)->queryScalar();

            return $productId;
        }

        public function createProduct($terminalRecord){

            //get entry
            $plot = Plot::model()->findByPk($terminalRecord['plot_id']);
            $entry = Entry::model()->findByPk($plot['entry_id']);

            //old product
            $oldProduct = Product::model()->findByPk($entry->product_id);

            //new generation
            if(trim(strtolower($oldProduct->generation)) == 'unkown'){
                $newGeneration = 'UNKNOWN';
            }else{
                $gen = explode('F', $oldProduct->generation);
                $newGeneration = 'F'. (intval(trim($gen[1])) + 1);
            }

            $designation = $terminalRecord['designation'];

            //check if old product exists
            $checkProd = ProductName::model()->findByAttributes(array(
                'value'=>trim($designation),
                'is_void'=>false
            ));

            //product does not exist yet
            if(empty($checkProd)){

                $year = date('Y',time());
                $seasonId = ($oldProduct->season_id != NULL) ? $oldProduct->season_id : 'null';




                $irisPreferredId = isset($oldProduct->iris_preferred_id) ? pg_escape_string($oldProduct->iris_preferred_id) : 'null';
                $crossId = isset($oldProduct->cross_id) ? pg_escape_string($oldProduct->cross_id) : 'null';

                $sql = "
                    insert into master.product
                    (
                        program_id, designation, name_type,
                        product_type, year, season_id, mta_status,
                        parentage, cross_id, generation,
                        iris_preferred_id, breeding_line_name, derivative_name,
                        fixed_line_name, selection_method, description,
                        display_name, notes, creation_timestamp,
                        creator_id, is_void
                    )
                    values
                    (
                        '$oldProduct->program_id', '$designation', 'derivative_name',
                        'progeny', '$year', $seasonId, '$oldProduct->mta_status',
                        '$oldProduct->parentage',$crossId,'$newGeneration',
                        $irisPreferredId,'$oldProduct->breeding_line_name', '$oldProduct->derivative_name',
                        '$oldProduct->fixed_line_name','$oldProduct->selection_method','$oldProduct->description',
                        '$designation','created via create progenies tool','$this->creationTimestamp',
                        '$this->userId','false'
                    )
                    returning id
                ";
                $newProductId =  Yii::app()->db->createCommand($sql)->queryScalar();
                return $newProductId;

            }else{
                return $checkProd->product_id;

            }
        }

}