PK     $��Z�=Y�  �  
  System.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class System extends Model
{
    use HasFactory;

    public function getSystemQuery(){
        return "SELECT %s
        FROM systems 
        WHERE 1
        AND `status` = 1
        AND `active` = 1
        %s
        %s
        %s
        %s
        ";
    }

    public function getSystems($array_data){
        // dd($array_data);
        $fields = " * ";
        $query = sprintf(
            $this->getSystemQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getSystemsCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getSystemQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getSystemsFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getSystemQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getSystemsReportQuery(){
        return "SELECT %s
        FROM systems 
        WHERE 1
        AND `status` = 1
        AND active = 1
        %s
        ";
    }

    public function reports($array_data){
        $fields = " system_name, DATE_FORMAT(`created_at`,'%M %e, %Y') as created_at ";
        $query = sprintf(
            $this->getSystemsReportQuery(),
            $fields,
            $array_data['where'],
        );
        // dd($query);
        return DB::select($query);
    }

    public function pdf($results){
        $data = [];

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data, $grpData);

        $report_title = "Systems Masterfile";

        $reportData = [
            'data'  =>  $data,
            'webpage_title' =>  "Systems Report",
            'report_title'  =>  $report_title,
            'table_headers' =>  ['No.','System Name', 'Date Created'],
            'table_body'    =>  ['system_name','created_at']
        ];

        return $reportData;
    }

    public function csv($results){
        // dd($results);
        $systems = [];
        $systems[] = ['No.','System Name', 'Date Created'];
        $inc = 0;
        foreach($results as $row){
            $systems[] = [
                $inc+=1,
                $row->system_name,
                $row->created_at
            ];
        }
        $filename = "Systems_Masterfile"."-". date('Y-m-d').'.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($systems[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f, $arr);
        } else {
            foreach ($systems as $row) {
                fputcsv($f, $row, ',');
            }
        }        
        
        fclose($f);    
    }

}
PK     $��Z;��)�  �    Questionnaire.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

use Illuminate\Support\Facades\DB;

class Questionnaire extends Model
{
    use HasFactory;

    protected $fillable = ['s_id','t_id','q_id','status','active'];

    public function getQuestionnaireQuery(){
        return " SELECT %s 
        FROM `templates` `tmp`
        INNER JOIN `questionnaires` `qst` ON
            `tmp`.`id` = `qst`.`t_id`
        INNER JOIN `systems` `sys` ON
            `qst`.`s_id` = `sys`.`id`
        WHERE
            1 AND `qst`.`status` = 1
        -- GROUP BY `tmp`.`id`
        %s
        %s
        %s
        %s
        ";
    }

    public function getQuestionnaires($array_data){
        // dd($array_data);
        $fields = " 
        `qst`.`id` as `q_id`,
        `tmp`.`id` as `tmp_id`,
        `tmp`.`title` AS `title`,
        JSON_LENGTH(`tmp`.`q_id`) as `q_count`,
        `sys`.`id` as `sys_id`,
        `sys`.`system_name` as `system_name`,
        `qst`.`created_at` ";
        // ANY_VALUE(`tmp`.`id`) AS `tmp_id`,
        // ANY_VALUE(`tmp`.`title`) AS `title`,
        // ANY_VALUE(`qst`.`id`) AS `id`,
        // ANY_VALUE(`sys`.`id`) AS `sys_id`,
        // ANY_VALUE(`sys`.`system_name`) AS `system_name` ";
        $query = sprintf(
            $this->getQuestionnaireQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );
        // dd($query);
        return DB::select($query);
    }

    public function getQuestionnairesCount($array_data){
        // $fields = " tmp.id ";
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getQuestionnaireQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        //dd($query);
        // return DB::select("SELECT COUNT(1) as Count FROM (".$query.") foo");
        return DB::select($query);
    }

    public function getQuestionnairesFilteredCount($array_data){
        // $fields = " tmp.id ";
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getQuestionnaireQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getQuestionnairesReportQuery(){
        return "SELECT %s
        FROM questionnaires 
        INNER JOIN templates ON questionnaires.t_id = templates.id
        INNER JOIN systems ON questionnaires.s_id = systems.id
        INNER JOIN links ON templates.id = links.tmp_id AND systems.id = links.sys_id
        WHERE 1
        AND `questionnaires`.`status` = 1
        AND questionnaires.active = 1
        %s
        ";
    }

    public function reports($array_data){
        $fields = " CONCAT(templates.title,' (',JSON_LENGTH(templates.q_id),' question/s)') as title, systems.system_name,questionnaires.*,links.link ";
        $query = sprintf(
            $this->getQuestionnairesReportQuery(),
            $fields,
            $array_data['where'],
        );
        // dd($query);
        return DB::select($query);
    }

    public function pdf($results, $type, $filter_data){
        $data = [];
        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data, $grpData);

        $report_title = "Survey Form Masterfile";
        $reportData = [
            'data'  =>  $data,
            'date_from' => $filter_data[0], 
            'date_to' => $filter_data[1], 
            'webpage_title' =>  "Survey Form Report",
            'report_title'  =>  $report_title,
            'table_headers' =>  ['No.','Template','System','Date Created','Link','Instruction'],
            'table_body'    =>  ['title','system_name','created_at','link','instruction']
        ];

        return $reportData;
    }

    public function csv($results){
        $questionnaire = [];
        $questionnaire[] = ['No.','Template','System','Date Created','Link','Instruction'];
        $inc = 0;
        foreach($results as $row){
            $questionnaire[] = [
                $inc+=1,
                $row->title,
                $row->system_name,
                $row->created_at,
                $row->link,
                $row->instruction
            ];
        }
        $filename = "SurveyForm_Masterfile"."-".date('Y-m-d').'.csv';

        // $filename = "Questions_Masterfile"."-". date('Y-m-d').'.csv';
        // dd($questions);
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($questionnaire[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($questionnaire as $row){
                fputcsv($f, $row, ',');
            }
        }

        fclose($f);
    }
}
PK     $��Z>X=�/7  /7  
  Answer.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Answer extends Model
{
    use HasFactory;

    public function getTitles($data){
        // dd($data);
        $q =  "SELECT %s
        FROM questionnaires `qstnn`
        INNER JOIN `systems` `s` on `s`.`id` = `qstnn`.`s_id`
        INNER JOIN `templates` `t` on `t`.`id` = `qstnn`.`t_id`
        WHERE 1
        %s
        ";
        $fields = " `t`.`title`, `s`.`system_name` ";
        $query = sprintf(
            $q,
            $fields,
            $data
        );

        // dd($query);

        return DB::select($query);
    }

    public function getQuestionnairesReportQuery(){
        return "SELECT %s
        FROM answers 
        INNER JOIN (
        SELECT syst_id
        FROM answers 
        WHERE 1
        GROUP BY syst_id
        ) as subquery ON answers.syst_id = subquery.syst_id
        INNER JOIN `systems` ON answers.syst_id = systems.id
        INNER JOIN `templates` ON answers.tmpt_id = templates.id
        INNER JOIN `users` ON answers.user_id = users.id
        %s
        GROUP BY answers.user_id, answers.qstnn_id, answers.tmpt_id, answers.qst_id, answers.syst_id, answers.rating, `answers`.`created_at`
        ";           
    }

    public function reports($array_data){
        $fields = " answers.user_id, CONCAT(users.f_name,' ',users.l_name) as user_name, answers.tmpt_id, templates.title as template_title, systems.system_name as system_name, answers.qst_id, answers.qstnn_id, answers.syst_id, answers.rating, `answers`.`created_at` ";
        $query = sprintf(
            $this->getQuestionnairesReportQuery(),
            $fields,
            $array_data['where']
        );
        // dd($query);

        return DB::select($query);
    }

    public function countsQuery(){
        return "SELECT %s
        FROM answers `ans`
        INNER JOIN `systems` `s` on `s`.`id` = `ans`.`syst_id`
        INNER JOIN `templates` `t` on `t`.`id` = `ans`.`tmpt_id`
        INNER JOIN `users` `u` on `u`.`id` = `ans`.`user_id`        
        WHERE 1
        %s
        -- GROUP BY qstnn_id, syst_id, tmpt_id, user_name
        ";
    }

    public function reports2($array_data){
        // dd($array_data);
        // $fields = " qstnn_id, syst_id, tmpt_id, CONCAT(users.f_name,' ',users.l_name) as user_name, systems.system_name, templates.title, AVG(SUBSTRING(answers.rating, 2, LENGTH(answers.rating)-2)) as average";
        $fields = " `t`.`title`, `s`.`system_name`,CONCAT(`u`.`f_name`,' ',`u`.`l_name`) as `user_name`, `ans`.`rating` as `average`, `ans`.`created_at` ";
        $query = sprintf(
            $this->countsQuery(),
            $fields,
            $array_data['where']
        );

        // dd($query);

        return DB::select($query);
    }

    public function commentsQuery(){
        return "SELECT %s
        FROM answers `ans`
        INNER JOIN `systems` `s` on `s`.`id` = `ans`.`syst_id`
        INNER JOIN `templates` `t` on `t`.`id` = `ans`.`tmpt_id`
        INNER JOIN `users` `u` on `u`.`id` = `ans`.`user_id`        
        WHERE 1
        %s
        ";
    }

    public function reports3($array_data){
        // dd("dito");
        $fields = " `t`.`title`, `s`.`system_name`, CONCAT(`u`.`f_name`,' ',`u`.`l_name`) as `user_name`, `ans`.`comment`, `ans`.`created_at` ";

        $query = sprintf(
            $this->commentsQuery(),
            $fields,
            $array_data['where']
        );

        return DB::select($query);
    }

    public function pdf($results,$type,$comp){
        $data = [];

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);
        // dd($results[0]['template_title']);
        $report_title = "Rating Summary";
        // ,'Template','System'
        // ,'template_title','system_name'
        $reportData = [
            'data'  =>  $data,
            'webpage_title'  => "Reviews Report",
            'company' => $comp->comp_name ?? "",
            'template_name' =>  $results[0]['template_title'] ?? "",
            'system_name'   =>  $results[0]['system_name'] ?? "",
            'report_title'  =>  $report_title,
            'table_headers' =>  ['No.','User','Response'],
            'table_body'    =>  ['user_name']
        ];
        // dd($data);
        return $reportData;
    }
    public function avgPerQstpdf($results,$result,$comp){
        $data = [];
        // dd($results);

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);
        // dd($results[0]['template_title']);
        $report_title = "Average per Question Summary";

        $table_body = [];

        foreach ($results as $average) {
            $table_body[] = [
                $average['id'],        // Question Id
                $average['question'],  // Question
                $average['count'],     // Count
                number_format($average['average'], 2),   // Average
            ];
        }
        $reportData = [
            'data'  =>  $data,
            'webpage_title'  => "Reviews Report",
            'company' => $comp->comp_name ?? "",
            'template_name' =>  $result[0]['template_title'] ?? "",
            'system_name'   =>  $result[0]['system_name'] ?? "",
            'report_title'  =>  $report_title,
            'table_headers' =>  ['No.','Question Id', 'Question', 'Count', 'Average'],
            // 'table_body'    =>  ['id','question','count','average']
            'table_body'    =>  $table_body
        ];
        // dd($reportData);
        return $reportData;
    }

    public function pdf2($results,$titles){

        // dd($titles);
        $data = [];

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);

        $report_title = ($results[0]['system_name'] ?? ' ') . " Respondents Count";

        $reportData = [
            'data'  =>  $data,
            'webpage_title'  => "Reviews Report",
            'report_title'  =>  $report_title,
            'system_name'   =>  $titles[0]->system_name,
            'template_name' =>  $titles[0]->title,
            'table_headers' =>  ['No.','Name','Average'],
            'table_body'    =>  ['user_name','rating']
        ];
        return $reportData;
    }

    public function pdf3($results,$titles){
        $data = [];

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);

        $report_title = ($results[0]['system_name'] ?? ' ') . " Comment Summary Report";

        $reportData = [
            'data'  =>  $data,
            'webpage_title'  => "Comments Summary Report",
            'report_title'  =>  $report_title,
            'system_name'   =>  $titles[0]->system_name,
            'template_name' =>  $titles[0]->title,
            'table_headers' =>  ['No.','Name','Comment'],
            'table_body'    =>  ['user_name','comment']
        ];
        return $reportData;
    }

    // public function csv($result){
    //     // dd($result);
    //     $inc = 0;
    //     $response = [];
    //     $response[] = [
    //         'No.','User','Template Name','System Name', 'Question', 'Rating'
    //     ];
    //     foreach($result as $key => $row){
    //         for($i=0; $i < count($row['ratings']); $i++){
    //             // print_r($row['ratings'][$i]['questions']);
    //             // exit;
    //             $response[] = [
    //                 $inc+=1,
    //                 $row['user_name'],
    //                 $row['template_title'],
    //                 $row['system_name'],
    //                 $row['ratings'][$i]['questions'],
    //                 $row['ratings'][$i]['rating']
    //             ];
    //         }
    //         // foreach($row['ratings'] as $r => $question){
    //     }

    //     // dd($response);

    //     $file_name = ($result[0]->system_name ?? ' ')  . " Respondents_Count-".date('Y-m-d').'.csv';

    //     // dd($file_name);
    //     header('Content-Type: text/csv');
    //     header('Content-Disposition: attachment; filename="'.$file_name.'"');

    //     $f = fopen('php://output', 'wb');

    //     if($f === false){
    //         die('Error opening the file '.$file_name);
    //     }

    //     if(empty($response[1])){
    //         $arr = [
    //             "No data to show"
    //         ];
    //         fputcsv($f,$arr);
    //     } else {
    //         foreach($response as $row){
    //             fputcsv($f, $row, ',');
    //         }
    //     }

    //     fclose($f);

    // }
    public function csv($result){
        $inc = 0;
        $response = [];
        $response[] = [
            'No.','User','Template Name','System Name', 'Question', 'Rating'
        ];
    
        $totalRatings = 0;
        $totalCount = 0;
    
        foreach($result as $key => $row){
            for($i=0; $i < count($row['ratings']); $i++){
                $rating = $row['ratings'][$i]['rating'];
                $totalRatings += $rating;
                $totalCount++;
                $response[] = [
                    $inc+=1,
                    $row['user_name'],
                    $row['template_title'],
                    $row['system_name'],
                    $row['ratings'][$i]['questions'],
                    $rating
                ];
            }
        }
    
        // Calculate average rating
        $averageRating = ($totalCount > 0) ? ($totalRatings / $totalCount) : 0;
    
        // Add row for average rating
        $response[] = [
            '',
            '',
            '',
            '',
            'Average Rating',
            round($averageRating,0)
        ];
    
        $file_name = ($result[0]->system_name ?? ' ')  . " Respondents_Count-".date('Y-m-d').'.csv';
    
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$file_name.'"');
    
        $f = fopen('php://output', 'wb');
    
        if($f === false){
            die('Error opening the file '.$file_name);
        }
    
        if(empty($response[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($response as $row){
                fputcsv($f, $row, ',');
            }
        }
    
        fclose($f);
    }

    public function csv2($results){
        // dd($results);
        $response = [];
        $response[] = [
            'No.','User','Average'
        ];
        foreach($results as $key => $result){
            $ave = json_decode($result->average);
            $average = array_reduce($ave, function($carry, $number) {
                return $carry + $number;
            }, 0) / count($ave);

            $round = round($average,0);
            $response[] = [
                $key+=1,
                $result->user_name,
                $round
            ];
        }

        $file_name = ($results[0]->system_name ?? ' ')  . " Respondents_Count-".date('Y-m-d').'.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$file_name.'"');

        $f = fopen('php://output', 'wb');

        if($f === false){
            die('Error opening the file '.$file_name);
        }

        if(empty($response[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($response as $row){
                fputcsv($f, $row, ',');
            }
        }

        fclose($f);
    }

    public function csv3($results, $titles){
        $response = [];
        $response[] = [
            'No.','User','Comment'
        ];

        foreach($results as $key => $row){
            $response[] =   [
                $key+=1,
                $row->user_name,
                $row->comment
            ];
        }
        $file_name = "Comment_Summary-".date('Y-m-d').'.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$file_name.'"');

        $f = fopen('php://output', 'wb');

        if($f === false){
            die('Error opening the file '.$file_name);
        }

        if(empty($response[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($response as $row){
                fputcsv($f, $row, ',');
            }
        }

        fclose($f);
    }

    public function avgPerQstcsv($array_data,$result,$comp){
        // dd($array_data);
        $response = [];
        $response[] = ['No.','Question Id', 'Question', 'Count', 'Average'];
        $inc = 0;

        foreach($array_data as $key => $row){
            $inc++;
            $response[] = [
                $inc,
                $row['id'],
                $row['question'],
                $row['count'],
                number_format($row['average'],2),
            ];
        }
        $file_name = "Average_Per_Question_Summary-".date('Y-m-d').'.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$file_name.'"');

        $f = fopen('php://output', 'wb');

        if($f === false){
            die('Error opening the file '.$file_name);
        }
        fputcsv($f,["Template Name:","System Name:","Company:"],',');
        fputcsv($f,[$result[0]['template_title'],$result[0]['system_name'],$comp->comp_name ?? "None"],',');

        if(empty($response[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($response as $row){
                fputcsv($f, $row, ',');
            }
        }

        fclose($f);
    }
}
PK     $��ZO��ʙ  �    Auth.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Auth extends Model
{
    use HasFactory;

    public function getQuestions(){
        return count(Questions::all());
    }
    
    public function getTemplates(){
        return count(Template::all());
    }

    public function getSystems(){
        return count(System::all());
    }

    public function getQuestionnaires(){
        return count(Questionnaire::all());
    }

    public function reports($array_data){

    }

    public function getCompanyQuery(){
        return "SELECT %s
        FROM companies 
        WHERE 1
        AND `status` = 1
        AND `active` = 1
        %s
        %s
        %s
        %s
        ";
    }

    public function getCompanies($array_data){
        $fields = " * ";
        $query = sprintf(
            $this->getCompanyQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getCompaniesCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getCompanyQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getCompaniesFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getCompanyQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getEmailsQuery(){    
            return "SELECT %s
            FROM email_responses 
            INNER JOIN users `u` on `u`.`id` = email_responses.user
            WHERE 1
            %s
            %s
            %s
            %s
            ";
    }

    public function getEmails($array_data){
        // dd($array_data);
        $fields = " `email_responses`.`id`, CONCAT(`u`.`f_name`, ' ', `u`.`l_name`) as `user_name`, `email_responses`.`email`, `email_responses`.`created_at`, `email_responses`.`status` ";
        $query = sprintf(
            $this->getEmailsQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getEmailsCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getEmailsQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getEmailsFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getEmailsQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getRolesQuery(){
        return "SELECT %s
        FROM `roles` 
        WHERE 1        
        %s
        %s
        %s
        %s
        ";
    }

    public function getRoles($array_data){
        $fields = " `roles`.`id`, `roles`.`name`, `roles`.`title` ";
        $query = sprintf(
            $this->getRolesQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getRolesCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getRolesQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getRolesFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getRolesQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }
}
PK     $��Z����t  t    Questions.phpnu �[���        <?php

namespace App\Models;

use App\Http\Controllers\AuthController;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use PDF;
// use Dompdf;
use Illuminate\Support\Facades\App;

class Questions extends Model
{
    use HasFactory;

    public function getQuestionsQuery(){
        return "SELECT %s
        FROM questions 
        WHERE 1
        AND `status` = 1
        AND active = 1
        %s
        %s
        %s
        %s
        ";
    }

    public function getQuestions($array_data){
        // dd($array_data);
        $fields = " * ";
        $query = sprintf(
            $this->getQuestionsQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getQuestionsCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getQuestionsQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getQuestionsFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getQuestionsQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getQuestionsReportQuery(){
        return "SELECT %s
        FROM questions 
        WHERE 1
        AND `status` = 1
        AND active = 1
        %s
        ";
    }

    public function reports($array_data){
        $fields = " id, title, DATE_FORMAT(`created_at`,'%M %e, %Y') as created_at ";
        $query = sprintf(
            $this->getQuestionsReportQuery(),
            $fields,
            $array_data['where'],
        );
        // dd($query);
        return DB::select($query);
    }

    public function pdf($results,$type){

            $data = [];
            $grpData = new \stdClass();

            $grpData->list = $results;
            $grpData->total = sizeOf($results);
            array_push($data, $grpData);

            $report_title = "Questions Masterfile";
            $reportData = [
                'data'  =>  $data,
                'webpage_title' =>  "Questions Report",
                'report_title'  =>  $report_title,
                'table_headers' =>  ['No.','Question', 'Date Created'],
                'table_body'    =>  ['title','created_at'],
                // 'img'           =>  $base64
            ];

            return $reportData;
    }

    public function pdf2($results){
        $data = [];
        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data, $grpData);

        $report_title = "Question per Template Masterfile";
        $reportData = [
            'data'  =>  $data,
            'webpage_title' =>  "Questions Report",
            'report_title'  =>  $report_title,
            // 'table_headers' =>  ['No.','Question','Templates', 'Date Created'],
            // 'table_body'    =>  ['title','templateTitles','created_at'],
            'table_headers' =>  ['No.','Question', 'Date Created'],
            'table_body'    =>  ['title','created_at'],
            // 'img'           =>  $base64
        ];

        return $reportData;
    }

    public function csv($results){
        // dd($results);
        $questions = [];
        $questions[] = ['No.','Question','Date Created'];
        $inc = 0;
        foreach ($results as $row) {
            $questions[] = [
                $inc+=1,
                $row->title,
                $row->created_at
            ];
        }
        $filename = "Questions_Masterfile"."-". date('Y-m-d').'.csv';
        // dd($questions);
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($questions[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f, $arr);
        } else {
            foreach ($questions as $row) {
                fputcsv($f, $row, ',');
            }
        }
        
        fclose($f);
    }

    public function csv2($results){
        // dd($results);
        $questions = [];
        $questions[] = ['No.','Question Title', 'Template', 'Date Created'];
        $inc = 0;
        foreach ($results as $row) {
            $templateTitles = implode(', ', $row->templateTitles);
            $questions[] = [
                $inc+=1,
                $row->title,
                $templateTitles,
                $row->created_at
            ];
        }
        $filename = "Questions_per_Template_Masterfile"."-". date('Y-m-d').'.csv';
        // dd($questions);
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($questions[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f, $arr);
        } else {
            foreach ($questions as $row) {
                fputcsv($f, $row, ',');
            }
        }
        
        fclose($f);
    }
}
PK     $��Z��֮Y	  Y	    User.phpnu �[���        <?php

namespace App\Models;

// use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Laravel\Sanctum\HasApiTokens;
use Silber\Bouncer\Database\HasRolesAndAbilities;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasRolesAndAbilities;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'asa_id',
        'f_name',
        'l_name',
        'email',
        'password',
        'comp_id',
        'type'
    ];

    /**
     * The attributes that should be hidden for serialization.
     *
     * @var array<int, string>
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast.
     *
     * @var array<string, string>
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function getUsersQuery(){
        $id = Auth::user()->id;
        return "SELECT %s
        FROM `users` 
        WHERE 1
        AND `id` != $id 
        %s
        %s
        %s
        %s
        ";
    }

    public function getUsers($array_data){
        $fields = " `users`.`id`, `users`.`f_name`, `users`.`l_name` ";
        $query = sprintf(
            $this->getUsersQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getUsersCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getUsersQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getUsersFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getUsersQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }
}
PK     $��ZBg6�  �    Template.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Template extends Model
{
    use HasFactory;

    protected $fillable = ['title','q_id'];

    public function getTemplateQuery(){
        return "SELECT %s
        FROM templates
        WHERE 1
        AND `status` = 1
        AND `active` = 1
        %s
        %s
        %s
        %s
        ";
    }

    public function getTemplates($array_data){
        $fields = " * ";
        $query = sprintf(
            $this->getTemplateQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            $array_data['sort'],
            $array_data['offset_limit']
        );

        return DB::select($query);
    }

    public function getTemplatesCount($array_data){
        $fields = " COUNT(1) as Count ";
        $query = sprintf(
            $this->getTemplateQuery(),
            $fields,
            '',
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getTemplatesFilteredCount($array_data){
        $fields = " COUNT(1) as FilteredCount ";
        $query = sprintf(
            $this->getTemplateQuery(),
            $fields,
            $array_data['search'],
            $array_data['where'],
            '',
            ''
        );
        return DB::select($query);
    }

    public function getTemplatesReportQuery(){
        return "SELECT %s
        FROM templates 
        WHERE 1
        AND `status` = 1
        AND active = 1
        %s
        ";  
    }

    public function reports($array_data){
        $fields = " * ";
        $query = sprintf(
            $this->getTemplatesReportQuery(),
            $fields,
            $array_data['where']
        );

        return DB::select($query);
    }

    public function pdf($results){
        $data = [];

        $grpData = new \stdClass();

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);

        $report_title = "Templates Masterfile";

        $reportData = [
            'data'  =>  $data,
            'webpage_title' =>  "Templates Report",
            'report_title'  =>  $report_title,
            'table_headers'  =>  ['No.', 'Template Name', 'Date Created'],
            'table_body'    =>  ['title','created_at']
        ];

        return $reportData;
    }

    public function csv($results){
        $templates = [];
        $templates[] = ['No.','Title', 'Date Created'];
        $inc = 0;
        foreach($results as $row){
            $templates[] = [
                $inc+=1,
                $row->title,
                $row->created_at,
            ];
        }
        $filename = "Templates_Masterfile." . date('Y-m-d H-i-sA').'.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($templates[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f, $arr);
        } else {
            foreach ($templates as $row) {
                fputcsv($f, $row, ',');
            }
        }
        
        fclose($f);
    }

    public function reportsTmpQstn($array_data){
        $fields = " * ";
        $query = sprintf(
            $this->getTemplatesReportQuery(),
            $fields,
            $array_data['where']
        );

        return DB::select($query);
    }

    // public function pdfTmpQstn($results){
    //     // dd($results);
    //     $data = [];

    //     $grpData = new \stdClass();

    //     $grpData->list = $results;
    //     $grpData->total = sizeOf($results);
    //     array_push($data,$grpData);

    //     $report_title = "Templates Masterfile";

    //     $reportData = [
    //         'data'  =>  $data,
    //         'webpage_title' =>  "Templates Report",
    //         'report_title'  =>  $report_title,
    //         'table_headers'  =>  ['No.', 'Questions', 'Date Created'],
    //         'table_body'    =>  ['title','created_at']
    //     ];

    //     return $reportData;
    // }
    public function pdfTmpQstn($results) {
        $data = [];
    
        $grpData = new \stdClass();
        // $grpData->no = 1;

        $grpData->list = $results;
        $grpData->total = sizeOf($results);
        array_push($data,$grpData);

        // $grpData->title = $results[0]->title;
        // $grpData->created_at = $results[0]->created_at;
        // $grpData->questions = $results[0]->questions;
        // array_push($data, $grpData);
    
        $report_title = "Template Questions Masterfile";
    
        $reportData = [
            'data' => $data,
            'webpage_title' => "Template Questions Masterfile",
            'template_title'    =>  $results[0]->title,
            'report_title' => $report_title,
            'table_headers' => ['No.', 'Questions', 'Date Created'],
            'table_body' => ['title', 'created_at'],
        ];
    
        return $reportData;
    }
    
    

    public function csvTmpQstn($results){
        $templates = [];
        $templates[] = ['No.','Questions','Date Created'];
        $inc = 0;
        foreach($results as $row){
            $templates[] = [
                $inc+=1,
                $row->title,
                $row->created_at
            ];
        }
        $filename = "Template_Questions_Masterfile"."-".date('Y-m-d').'.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        
        $f = fopen('php://output', 'wb');
        
        if ($f === false) {
            die('Error opening the file ' .$filename);
        }

        if(empty($templates[1])){
            $arr = [
                "No data to show"
            ];
            fputcsv($f,$arr);
        } else {
            foreach($templates as $row){
                fputcsv($f, $row, ',');
            }
            // fputcsv($f,$templates[0]->title,',');
        }

        fclose($f);
    }
}
PK     $��Z�P�̯   �     Link.phpnu �[���        <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Link extends Model
{
    use HasFactory;
}
PK       $��Z�=Y�  �  
                System.phpnu �[���        PK       $��Z;��)�  �                Questionnaire.phpnu �[���        PK       $��Z>X=�/7  /7  
            �"  Answer.phpnu �[���        PK       $��ZO��ʙ  �              JZ  Auth.phpnu �[���        PK       $��Z����t  t              l  Questions.phpnu �[���        PK       $��Z��֮Y	  Y	              ̂  User.phpnu �[���        PK       $��ZBg6�  �              ]�  Template.phpnu �[���        PK       $��Z�P�̯   �               P�  Link.phpnu �[���        PK      V  7�    