<?php
class Files extends Zend_Db_Table
{
    protected $_name = 'files';
	protected $_primary = 'file_id';
    private $min_phone_length = 9;
    private $max_phone_length = 13;

    public function getFile($file_id)
    {
        return $this->fetchRow($this->select()->where('file_id = ?', $file_id));
    }

    public function getAllowedFileTypes($organisation_id)
    {
        $sql = "select ft.* from file_type ft inner join organisation o using (organisation_type_id) ";
        $sql .= "where o.organisation_id=".$organisation_id;
        return $this->_db->query($sql)->fetchAll();
    }

    public function getAllowedDestinations($organisation_id)
    {
        if ($organisation_id>1) { //can only send to wyndham
            $sql = "select * from organisation where organisation_id=1 ";
        } else { // if no restrictions get all users - ie. internal staff user can email all organisations
            $sql = "select * from organisation";
        }
        $result = $this->_db->query($sql)->fetchAll();
        return $result;
    }

    public function validateUploadFile($file,$file_type_id)
    {
        $error = '';
        // if no file type - no validation needed - return empty string (no error)
        if ($file_type_id) {
            $handle = fopen($file,"r");
            // check csv columns
            $column_names = fgetcsv($handle, 8096, ",");
            $error = $this->validateCSVColumns($column_names,$file_type_id);
            if (empty($error)) {
                // check CSV data
                $row_count = 0;
                while ($csv_row = fgetcsv($handle,8096,",")) {
                    $row_count++;
                    if (!empty($csv_row)) {
                        $result = $this->validateCSVDataRow($csv_row,$file_type_id);
                        if (!empty($result)) $error .= "<br>Line ".($row_count+1).": ".$result;
                    }
                }
            }
        }
        return $error;
    }

    public function validateCSVColumns($column_names,$file_type_id)
    {
        switch ($file_type_id) {
            case 1: // vendor return data
                $valid_columns = array('LEADNUMBER','FIRST_NAME','LAST_NAME','PHONE','WORK_PHONE','CODE','SITE_NO','EXPIRY DATE','STATUS');
                $valid_columns2 = array('LEADNUMBER','FIRST_NAME','LAST_NAME','PHONE','WORK_PHONE','CODE','SITE_NO','EXPIRY DATE','COMMENT','STATUS');
                break;
            case 2: // dnc exclusions
                $valid_columns = array('VENDOR NAME','PHONE','PHONE 2','LEAD NUMBER','CHASE CODE','SITE');
                $valid_columns2 = array('VENDOR NAME','PHONE','PHONE 2','LEAD NUMBER','CHASE CODE','SITE','COMMENT');
                break;
        }
        if (count($column_names)<>count($valid_columns) && count($column_names)<>count($valid_columns2)) {
            if ($file_type_id==1) {
                return "Invalid CSV Column Count: The following columns are required: ".implode(',',$valid_columns)."\n OR \n".implode(',',$valid_columns2);
            } else {                
                return "Invalid CSV Column Count: The following columns are required: ".implode(',',$valid_columns);
            }
        } else {
            $template_columns = count($column_names)==count($valid_columns) ? $valid_columns : $valid_columns2;
            foreach($template_columns as $index=>$valid_column) {
                if (trim(strtolower($column_names[$index]))!=strtolower($template_columns[$index])) {
                    return "Invalid CSV Column: The CSV column '".$column_names[$index]."' should be named '".$valid_columns[$index]."'";
                }
            }
        }
    }

    public function validateCSVDataRow($csv_row,$file_type_id)
    {
        switch ($file_type_id) {
            case 1:
                // TODO check status call dispositions against database
                $lead_number_column = 0;
                $phone_number_column = 3;
                $phone2_column = 4;
                $chase_number_column = 5;
                $chase_column_name = "CODE";
                $site_number_column = 6;
                break;
            case 2:
                $lead_number_column = 3;
                $phone_number_column = 1;
                $phone2_column = 2;
                $chase_number_column = 4;
                $chase_column_name = "CHASE CODE";
                $site_number_column = 5;
                break;
        }
        // check for missing or incorrect lead number
        if (trim($csv_row[$lead_number_column])=='' || trim($csv_row[$lead_number_column])=='0') return "LEAD NUMBER missing.";
        //if (strlen(trim($csv_row[$lead_number_column]))!=7) return "Lead Number must be 7 characters";
        // check for phone number 1
        if (trim($csv_row[$phone_number_column])=='' || trim($csv_row[$phone_number_column])=='0') return "PHONE missing.";
        $phone_length = strlen(str_replace(" ","",str_replace("-","",str_replace(")","",str_replace("(","",trim($csv_row[$phone_number_column]))))));
        if ($phone_length < $this->min_phone_length || $phone_length > $this->max_phone_length) return "PHONE must be between $this->min_phone_length and $this->max_phone_length digits";
        // check for phone number 2 correct if exists
        $phone_length = strlen(str_replace(" ","",str_replace("-","",str_replace(")","",str_replace("(","",trim($csv_row[$phone2_column]))))));
        if (trim($csv_row[$phone2_column])!='' && ($phone_length < $this->min_phone_length || $phone_length > $this->max_phone_length)) return "PHONE 2 must be between $this->min_phone_length and $this->max_phone_length digits";
        // check code (chase number)
        if (trim($csv_row[$chase_number_column])=='' || trim($csv_row[$chase_number_column])=='0') return "'$chase_column_name' missing.";
        if (strlen(trim($csv_row[$chase_number_column]))!=6) return "'$chase_column_name' must be 6 characters";
        // check site
        if (trim($csv_row[$site_number_column])=='' || trim($csv_row[$site_number_column])=='0') return "SITE missing.";
        if (strlen(trim($csv_row[$site_number_column]))!=2) return "SITE must be 2 characters";
        // success no errors
        return '';
    }

    public function sendFile($from_user,$organisation_id,$file_id)
    {
        // sends a file to an organisation - replace null parameter with destination_user_id if you need to send to a specific person within an organisation
        $this->addTransfer($from_user->organisation_id,$from_user->user_id,$organisation_id,null,$file_id);
        // email destination to let them know that file is ready for download
        // get all users for that organisation
        $destination_users = $this->getOrganisationUsers($organisation_id);
        $url = (ENV == 'production') ? ('https://'.$_SERVER["HTTP_HOST"]) : ('http://'.$_SERVER["HTTP_HOST"]);
        foreach($destination_users as $destination_user) {
            $message = 'Dear '.$destination_user->first_name.' '.$destination_user->last_name.',<br><br>';
            $message .= 'You have received file(s) from '.$from_user->first_name." ".$from_user->last_name.' at '.$from_user->short_name.'. ';
            $message .= '<a href="'.$url.'">Click here to login</a> ';
            $message .= 'to the Data File Transfer system and download your files.<br><br>';
            $message .= 'From,<br>Data File Transfer System<br>Wyndham Vacation Resorts Asia Pacific.';
            $this->sendEmail($destination_user->email,'File(s) waiting to be collected - Data File Transfer',$message);
            $this->view->message = "File has been sent successfully.";
        }
    }

    public function sendEmail($to_email,$subject,$message)
    {
        $configuration = Zend_Registry::get('configuration');
        $mailConfig = $configuration->Mail;
        $mailer = new Wyndham_Mailer($mailConfig);
        $emails = explode(',',$to_email);
        foreach($emails as $email) {
            $data = array(
                'fromEmail' => 'data_file_transfer@wyndhamvrap.com',
                'toEmail' => $email,
                'subject' => $subject,
                'htmlMsg' => $message,
            );
            $attachments = array();
            $mailer->send($data, null, $attachments);
        }
    }

    public function addFile($file_type_id,$upload_filename)
    {
        $this->_db->insert('files', array('file_type_id'=>$file_type_id,'filename'=>basename($upload_filename)));
        return $this->getAdapter()->lastInsertId();
    }

    public function addTransfer($from_organisation_id,$from_user_id,$to_organisation_id,$to_user_id,$file_id)
    {
        $data = array(  'file_id'=>$file_id,
                        'from_organisation_id'=>$from_organisation_id,
                        'from_user_id'=>$from_user_id,
                        'to_organisation_id'=>$to_organisation_id,
                        'to_user_id'=>$to_user_id,
                    );
        $this->_db->insert('transfers', $data);
        return $this->getAdapter()->lastInsertId();
    }

    public function getFilesToCollect($user_id,$organisation_id)
    {
        $sql = "select t.*,f.filename,u.first_name,u.last_name from transfers t inner join user u on t.from_user_id=u.user_id ";
        $sql .= " inner join files f using (file_id) ";
        $sql .= " where t.to_user_id = $user_id or (t.to_organisation_id = $organisation_id and t.to_user_id is null)";
        $sql .= " and t.collected=0";
        return $this->_db->query($sql)->fetchAll();
    }

    public function getTransferHistory($organisation_id)
    {
        $sql = "select t.*,o1.short_name as from_organisation,o2.short_name as to_organisation,f.filename,";
        $sql .= "concat(u.first_name,' ',u.last_name) as sent_by_name, concat(cu.first_name,' ',cu.last_name) as collected_by_name ";
        $sql .= "from transfers t inner join user u on t.from_user_id=u.user_id ";
        $sql .= "inner join organisation o1 on t.from_organisation_id=o1.organisation_id ";
        $sql .= "inner join organisation o2 on t.to_organisation_id=o2.organisation_id ";
        $sql .= "left join user cu on t.collected_user_id=cu.user_id ";
        $sql .= "inner join files f using (file_id)";
        $sql .= " where ".$this->getAdapter()->quoteInto("t.to_organisation_id = ?",$organisation_id);
        $sql .= $this->getAdapter()->quoteInto(" or t.from_organisation_id = ?",$organisation_id);
        $sql .= " order by sent_ts desc";
        return $this->_db->query($sql)->fetchAll();
    }

    // checks if passed organisation_id is allowed to download this file sent as file_id
    // returns a value greater than zero if allowed or zero if not allowed
    public function allowedToDownload($file_id,$organisation_id)
    {
        $sql = "select count(*) as file_count ";
        $sql .= "from transfers t inner join user u on t.from_user_id=u.user_id ";
        $sql .= "inner join organisation o1 on t.from_organisation_id=o1.organisation_id ";
        $sql .= "inner join organisation o2 on t.to_organisation_id=o2.organisation_id ";
        $sql .= " where (".$this->getAdapter()->quoteInto("t.to_organisation_id = ?",$organisation_id);
        $sql .= $this->getAdapter()->quoteInto(" or t.from_organisation_id = ?",$organisation_id).")";
        $sql .= " and ".$this->getAdapter()->quoteInto("t.file_id = ?",$file_id);
        $row = $this->_db->fetchRow($sql);
        return ($row->file_count);
    }

    public function getLatestSftpFiles($last_id,$file_type_id)
    {
        $sql = "select t.file_id,f.file_type_id,o.organisation_code,t.sent_ts from transfers t ";
        $sql .= "inner join organisation o on t.from_organisation_id=o.organisation_id inner join files f using (file_id) ";
        $sql .= " where t.file_id>".$this->getAdapter()->quote($last_id)." and f.file_type_id=".$file_type_id;
        $sql .= " and t.to_organisation_id=1 and t.from_organisation_id<>1";
        return $this->_db->query($sql)->fetchAll();
    }

    public function deleteFile($file_id)
    {
        $where = $this->getAdapter()->quoteInto('file_id = ?', $file_id);
        $this->_db->delete('files',$where);
    }

    public function getCallCentreVendorUsers()
    {
        // organisation_type_id of 2 is call centre organisations
        $sql = "select u.user_id from `user` u inner join organisation o using (organisation_id) ";
        $sql .= "where o.organisation_type_id=2";
        return $this->_db->query($sql)->fetchAll();
    }

    public function getOrganisationUsers($organisation_id)
    {
        // organisation_type_id of 2 is call centre organisations
        $sql = "select u.*,o.short_name from `user` u inner join organisation o using (organisation_id) ";
        $sql .= "where u.active=1 and ".$this->getAdapter()->quoteInto('o.organisation_id = ?', $organisation_id);
        return $this->_db->query($sql)->fetchAll();
    }

    // set the sent file as "collected" by setting the collected flag, the collected timestamp and collected by user id.
    // note that if the current logged in organisation id is not the receiver of the file, this update won't occur
    public function setFileCollected($file_id,$user_id,$organisation_id)
    {
        $data = array(
                        "collected"=>1,
                        "collected_ts"=>date('Y-m-d H:i:s'),
                        "collected_user_id"=>$user_id,
                     );
        $this->_db->update('transfers',$data,"collected=0 and file_id=$file_id and to_organisation_id=$organisation_id");
    }

    public function getOrganisation($organisation_id)
    {
        $sql = "select * from organisation where ".$this->getAdapter()->quoteInto('organisation_id = ?', $organisation_id);
        return $this->_db->fetchRow($sql);
    }
}