<?php

namespace common\component\helpers;

use common\models\Action;
use common\models\Answer;
use common\models\AnswerSource;
use common\models\Biomarker;
use common\models\BiomarkerDefinition;
use common\models\BiomarkerRegion;
use common\models\BiomarkerSource;
use common\models\BiomarkerSubProject;
use common\models\BiomarkerSynonym;
use common\models\CoachingQuote;
use common\models\CoachingSource;
use common\models\CoachingStep;
use common\models\CoachingStepHint;
use common\models\CoachingStepImage;
use common\models\Country;
use common\models\Definition;
use common\models\DefinitionSource;
use common\models\Insight;
use common\models\InsightDefinition;
use common\models\InsightSource;
use common\models\InsightSubProject;
use common\models\InsightType;
use common\models\Lab;
use common\models\Language;
use common\models\LanguageSource;
use common\models\LanguageTranslate;
use common\models\Panel;
use common\models\PregnancySource;
use common\models\Question;
use common\models\QuestionSubProject;
use common\models\Region;
use common\models\SubProject;
use common\models\SymptomSource;
use common\models\Tag;
use common\models\Tooltip;
use common\models\User;
use common\models\WellbeingAnswer;
use common\models\WellbeingInsightSource;
use common\models\LrsMap;
use common\models\CatalogLrsRules;
use RecursiveDirectoryIterator;
use RecursiveIteratorIterator;
use Yii;
use yii\bootstrap\Html;
use yii\helpers\ArrayHelper;
use yii\helpers\FileHelper;
use yii\web\UploadedFile;
use ZipArchive;

//use PHPExcel_IOFactory;

error_reporting(E_ALL);
ini_set('include_path', Yii::getAlias('@common') . DIRECTORY_SEPARATOR . "component" . DIRECTORY_SEPARATOR . "phpexcel");
include Yii::getAlias('@common') . DIRECTORY_SEPARATOR . "component" . DIRECTORY_SEPARATOR . "phpexcel" . DIRECTORY_SEPARATOR . 'PHPExcel.php';
include Yii::getAlias('@common') . DIRECTORY_SEPARATOR . "component" . DIRECTORY_SEPARATOR . "phpexcel" . DIRECTORY_SEPARATOR . 'PHPExcel/Writer/Excel2007.php';


class ExcelHelper
{
    public static function newExcel()
    {
        $objPHPExcel = new \PHPExcel();
        // Set properties
        $objPHPExcel->getProperties()->setCreator("Medicus Metadata");
        $objPHPExcel->getProperties()->setLastModifiedBy("Medicus Metadata");
        $objPHPExcel->getProperties()->setTitle("Medicus Metadata Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setSubject("Medicus Metadata Office 2007 XLSX Test Document");
        $objPHPExcel->getProperties()->setDescription("Medicus Metadata Test document for Office 2007 XLSX, generated using PHP classes.");
        $objPHPExcel->setActiveSheetIndex(0);
        return $objPHPExcel;
    }

    public static function exportActionAnswers($path, $name, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "Answer ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Action");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Answer");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Answer Value");
        $actions = Action::find()->with('actionAnswers')->all();
        $row = 2;
        foreach ($actions as $action) {
            foreach ($action->actionAnswers as $answer) {
                $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $answer->id);
                $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $action->symbol);
                $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $answer->symbol);
                $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $answer->value);
                $row += 1;
            }
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name . ".xlsx");
    }

    public static function exportTemplate($path, $name, $columns, $example = [], $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $key = 'A';
        $count = 0;
        foreach ($columns as $column) {
            $objPHPExcel->getActiveSheet()->SetCellValue($key . 1, $column);
            if (count($example) > 0) {
                $objPHPExcel->getActiveSheet()->SetCellValue($key . 2, $example[0][$key]);
                $objPHPExcel->getActiveSheet()->SetCellValue($key . 3, $example[1][$key]);
            }
            $objPHPExcel->getActiveSheet()->getColumnDimension($key)->setWidth(30);
            if (strlen($key) > 1)
                $letterAscii = ord(substr($key, 1, 2)) + 26;
            else
                $letterAscii = ord($key);
            $letterAscii++;
            if ($letterAscii > 90) {
                $key = "A" . chr($letterAscii - 26);
            } else
                $key = chr($letterAscii);
            $count++;
        }
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name . ".xlsx");
    }

    public static function exportInsights($path, $name, $filteredData = null, $supportSubProjects = false, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Universal ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Introductory Statement");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Summary");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Detailed List");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Suggestion");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Color");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "More Details");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Missing Field");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Purpose");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Include Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Exclude Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "Is Positive");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Disclaimer");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "Is Pregnancy Insight");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Test Explained");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Image Category");
        $objPHPExcel->getActiveSheet()->SetCellValue("V" . 1, "Is Medical");
        $objPHPExcel->getActiveSheet()->SetCellValue("W" . 1, "Projects");
        $insights = isset($filteredData) ? $filteredData : Insight::find()->with('type')->all();
        $count = 2;
        foreach ($insights as $insight) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $insight->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $insight->universal_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $insight->introductory_statement);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $insight->summary);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $insight->detailed_list);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $insight->suggestion);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $insight->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $insight->type->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $insight->color);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $insight->more_details);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, $insight->missing_field);
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $insight->purpose);
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, $insight->include_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, $insight->exclude_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, isset($insight->is_positive) ? MedicusHelper::yesnoList()[$insight->is_positive] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, isset($insight->validity) ? MedicusHelper::validityValues()[$insight->validity] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, $insight->group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, $insight->disclaimer);
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, isset($insight->isPregnancyInsight) ? MedicusHelper::yesnoList()[$insight->isPregnancyInsight] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, $insight->test_explained);
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, $insight->image_category);
            $objPHPExcel->getActiveSheet()->SetCellValue("V" . $count, isset($insight->is_medical) ? MedicusHelper::yesnoList()[$insight->is_medical] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("W" . $count, $supportSubProjects === true ? Insight::getInsightSubProjectNames($insight->id, true) : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("X" . $count, $insight->keyword);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('Q' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('R' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('S' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('T' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('W' . $count)->getAlignment()->setWrapText(true);
            $count++;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(100);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(35);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportInsightsReplaced($path, $name, $region, $filteredData = null, $language = "en", $supportSubProjects = false, $category = "Sheet_1")
    {
        $biomarkers_variables = VariableHelper::getAllBiomarkersVariable($region, $language);

        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Universal ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Insight Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Insight Text Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Color");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "More details");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Missing field");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Purpose");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Include tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Is positive");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Exclude tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Summary Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Detailed list Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "Suggestion Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Disclaimer");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Image category");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "Is pregnancy nsight");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Test Explained Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Is Medical");
        $objPHPExcel->getActiveSheet()->SetCellValue("V" . 1, "Projects");
        $insights = isset($filteredData) ? $filteredData : Insight::find()->with('type')->all();
        $count = 2;
        foreach ($insights as $insight) {
            $statement = LanguageHelper::replaceTextAndVariables("insight", $insight->introductory_statement, $biomarkers_variables, $language);
            $summary = LanguageHelper::replaceTextAndVariables("insight", $insight->summary, $biomarkers_variables, $language);
            $detailed_list = LanguageHelper::replaceTextAndVariables("insight", $insight->detailed_list, $biomarkers_variables, $language);
            $suggestion = LanguageHelper::replaceTextAndVariables("insight", $insight->suggestion, $biomarkers_variables, $language);
            $test_explained = LanguageHelper::replaceTextAndVariables("insight", $insight->test_explained, $biomarkers_variables, $language);

            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $insight->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $insight->universal_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $insight->type->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $statement);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $insight->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $insight->color);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $insight->more_details);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $insight->missing_field);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $insight->purpose);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $insight->include_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, isset($insight->is_positive) ? MedicusHelper::yesnoList()[$insight->is_positive] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $insight->exclude_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, $summary);
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, $detailed_list);
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, $suggestion);
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, isset($insight->validity) ? MedicusHelper::validityValues()[$insight->validity] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, $insight->disclaimer);
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, $insight->image_category);
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, isset($insight->isPregnancyInsight) ? MedicusHelper::yesnoList()[$insight->isPregnancyInsight] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, $test_explained);
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, isset($insight->is_medical) ? MedicusHelper::yesnoList()[$insight->is_medical] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("V" . $count, $supportSubProjects === true ? $insight->id : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("W" . $count, $insight->keyword);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('P' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('Q' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('R' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('S' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('T' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('V' . $count)->getAlignment()->setWrapText(true);
            $count++;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(70);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(90);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(35);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportInsightSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Insight ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Region");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Doctor only");
        $sources = isset($filteredData) ? $filteredData : InsightSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $source->insight_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, isset($source->region) ? $source->region->name : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($source->for_doctor) ? MedicusHelper::yesnoList()[$source->for_doctor] : "No");

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(75);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkerSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Region");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Biomarker Region");
        $sources = isset($filteredData) ? $filteredData : BiomarkerSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->biomarker) ? $source->biomarker->symbol : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, isset($source->region) ? $source->region->name : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $source->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($source->biomarkerRegion) ? $source->biomarkerRegion->name : null);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(100);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(75);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportAnswerSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Answer");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $sources = isset($filteredData) ? $filteredData : AnswerSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->answer) ? $source->answer->value : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportPregnancySources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Pregnancy Test");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Order");
        $sources = isset($filteredData) ? $filteredData : PregnancySource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->pregnancyTest) ? $source->pregnancyTest->title : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->order);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportSymptomSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Symptom");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $sources = isset($filteredData) ? $filteredData : SymptomSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->symptom) ? $source->symptom->name : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportPregnancyDeliveryPreparationSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Pregnancy Delivery Preparation Topic");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $sources = isset($filteredData) ? $filteredData : AnswerSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->delivery) ? $source->delivery->topic : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkers($path, $name, $dataArray, $supportSubProjects = false, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Panel");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Days");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "What");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Why");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "pregnant why");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "How");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "HomeWell How");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Has Fixed Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Is Added Manually");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Allow Switch Detail");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "Parent Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "Related Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Visible");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Is Normal Range Mandatory");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "Show Parent");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Is Omitted");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Is Valid");
        $objPHPExcel->getActiveSheet()->SetCellValue("V" . 1, "Calculated From");
        $objPHPExcel->getActiveSheet()->SetCellValue("W" . 1, "Sample");
        $objPHPExcel->getActiveSheet()->SetCellValue("X" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("Y" . 1, "Release Group Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("Z" . 1, "Update Reference Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("AA" . 1, "Range Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AB" . 1, "Is Common");
        $objPHPExcel->getActiveSheet()->SetCellValue("AC" . 1, "Show Scale");
        $objPHPExcel->getActiveSheet()->SetCellValue("AD" . 1, "Time");
        $objPHPExcel->getActiveSheet()->SetCellValue("AE" . 1, "Method");
        $objPHPExcel->getActiveSheet()->SetCellValue("AF" . 1, "Is Activity Marker");
        $objPHPExcel->getActiveSheet()->SetCellValue("AG" . 1, "Has Abnormal Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("AH" . 1, "Show Lingual Reading");
        $objPHPExcel->getActiveSheet()->SetCellValue("AI" . 1, "Is Hidden");
        $objPHPExcel->getActiveSheet()->SetCellValue("AJ" . 1, "Is Smart");
        $objPHPExcel->getActiveSheet()->SetCellValue("AK" . 1, "Is Mandatory");
        $objPHPExcel->getActiveSheet()->SetCellValue("AL" . 1, "Medical Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AM" . 1, "Mandatory Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("AN" . 1, "Colors Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AO" . 1, "What Image");
        $objPHPExcel->getActiveSheet()->SetCellValue("AP" . 1, "Why Image");
        $objPHPExcel->getActiveSheet()->SetCellValue("AQ" . 1, "How Image");
        $objPHPExcel->getActiveSheet()->SetCellValue("AR" . 1, "Show History");
        $objPHPExcel->getActiveSheet()->SetCellValue("AS" . 1, "Positive Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("AT" . 1, "Parent Condition");
        $objPHPExcel->getActiveSheet()->SetCellValue("AU" . 1, "Projects");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->panel) ? $item->panel->name : $item->panel_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, !empty($item->type) ? \common\models\Biomarker::biomarkerTypes()[$item->type] : $item->type);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->days);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->what);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->why);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->pregnant_why);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $item->how);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $item->homewell_how);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, isset($item->has_fixed_ranges) ? MedicusHelper::yesnoList()[$item->has_fixed_ranges] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, isset($item->is_add_manual) ? MedicusHelper::yesnoList()[$item->is_add_manual] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, isset($item->allow_switch_detail) ? MedicusHelper::yesnoList()[$item->allow_switch_detail] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, isset($item->parentSymbol) ? $item->parentSymbol : $item->parent_biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, isset($item->relatedBiomarkerSymbol) ? $item->relatedBiomarkerSymbol : $item->related_biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, isset($item->visible) ? MedicusHelper::yesnoList()[$item->visible] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, isset($item->is_normal_range_mandatory) ? MedicusHelper::yesnoList()[$item->is_normal_range_mandatory] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, isset($item->show_parent) ? MedicusHelper::yesnoList()[$item->show_parent] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, isset($item->is_omitted) ? MedicusHelper::yesnoList()[$item->is_omitted] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, isset($item->is_valid) ? MedicusHelper::yesnoList()[$item->is_valid] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("V" . $count, $item->calculated_from);
            $objPHPExcel->getActiveSheet()->SetCellValue("W" . $count, isset($item->biomarkerSample) ? implode(" | ", ArrayHelper::map($item->biomarkerSample, 'id', 'sample.name')) : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("X" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("Y" . $count, $item->release_group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("Z" . $count, isset($item->update_reference_range) ? MedicusHelper::yesnoList()[$item->update_reference_range] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AA" . $count, isset($item->range_type) ? Biomarker::biomarkerRangeTypes()[$item->range_type] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AB" . $count, isset($item->is_common) ? MedicusHelper::yesnoList()[$item->is_common] : "No");
            $objPHPExcel->getActiveSheet()->SetCellValue("AC" . $count, isset($item->show_scale) ? MedicusHelper::yesnoList()[$item->show_scale] : "Yes");
            $objPHPExcel->getActiveSheet()->SetCellValue("AD" . $count, isset($item->timing) ? $item->timing->timing : $item->time);
            $objPHPExcel->getActiveSheet()->SetCellValue("AE" . $count, isset($item->biomarkerMethod) ? implode(" | ", ArrayHelper::map($item->biomarkerMethod, 'id', 'method.name')) : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AF" . $count, isset(MedicusHelper::yesnoList()[$item->is_activity_marker]) ? MedicusHelper::yesnoList()[$item->is_activity_marker] : $item->is_activity_marker);
            $objPHPExcel->getActiveSheet()->SetCellValue("AG" . $count, isset(MedicusHelper::yesnoList()[$item->hasAbnormalRange]) ? MedicusHelper::yesnoList()[$item->hasAbnormalRange] : $item->hasAbnormalRange);
            $objPHPExcel->getActiveSheet()->SetCellValue("AH" . $count, isset(MedicusHelper::yesnoList()[$item->showLingualReading]) ? MedicusHelper::yesnoList()[$item->showLingualReading] : $item->showLingualReading);
            $objPHPExcel->getActiveSheet()->SetCellValue("AI" . $count, isset($item->is_hidden) ? MedicusHelper::yesnoList()[$item->is_hidden] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AJ" . $count, isset($item->is_smart) ? MedicusHelper::yesnoList()[$item->is_smart] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AK" . $count, isset($item->is_mandatory) ? MedicusHelper::yesnoList()[$item->is_mandatory] : "No");
            $objPHPExcel->getActiveSheet()->SetCellValue("AL" . $count, !empty($item->medical_type) ? Biomarker::medicalTypes()[$item->medical_type] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AM" . $count, $item->mandatoryGroup);
            $objPHPExcel->getActiveSheet()->SetCellValue("AN" . $count, !empty($item->colors_type) ? Biomarker::colorsTypes()[$item->colors_type] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AO" . $count, $item->what_image);
            $objPHPExcel->getActiveSheet()->SetCellValue("AP" . $count, $item->why_image);
            $objPHPExcel->getActiveSheet()->SetCellValue("AQ" . $count, $item->how_image);
            $objPHPExcel->getActiveSheet()->SetCellValue("AR" . $count, !empty($item->show_history) ? MedicusHelper::yesnoList()[$item->show_history] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AS" . $count, $item->positive_range);
            $objPHPExcel->getActiveSheet()->SetCellValue("AT" . $count, $item->parent_condition);
            $objPHPExcel->getActiveSheet()->SetCellValue("AU" . $count, $supportSubProjects === true ? Biomarker::getBiomarkerSubProjectNames($item->id, true) : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("AV" . $count, isset(MedicusHelper::yesnoList()[$item->showLingualReadingStatement]) ? MedicusHelper::yesnoList()[$item->showLingualReadingStatement] : $item->showLingualReadingStatement);
            $objPHPExcel->getActiveSheet()->SetCellValue("AW" . $count, isset($item->customLingualReadingStatement)? $item->customLingualReadingStatement : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AX" . $count, isset($item->customTranslation)? $item->customTranslation : "");

            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('AU' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AI')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AJ')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AK')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AL')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AU')->setWidth(35);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportQuestions($path, $name, $dataArray, $supportSubProjects = false, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Text");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Answer Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Attribute");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Icon");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Gender");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Pregnant");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Min");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Max");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Info");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Mobile Only");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "Sub Question");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "Interval");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Hidden");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Min Age");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "Max Age");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Postpartum");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("V" . 1, "Breastfeeding");
        $objPHPExcel->getActiveSheet()->SetCellValue("W" . 1, "Menopause Restrictions");
        $objPHPExcel->getActiveSheet()->SetCellValue("X" . 1, "Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("Y" . 1, "Layout Template");
        $objPHPExcel->getActiveSheet()->SetCellValue("Z" . 1, "Projects");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->text);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->answer_type);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->attribute);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, isset($item->icon) ? basename($item->icon) : $item->icon);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($item->type->name) ? $item->type->name : $item->type_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, isset($item->gender) ? MedicusHelper::genderList()[$item->gender] : $item->gender);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->pregnant) ? MedicusHelper::yesnoList()[$item->pregnant] : $item->pregnant);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, $item->min);
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->max);
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, $item->info);
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, isset($item->mobile_only) ? MedicusHelper::yesnoList()[$item->mobile_only] : $item->mobile_only);
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, $item->sub_question);
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, $item->interval);
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, isset($item->hidden) ? MedicusHelper::yesnoList()[$item->hidden] : $item->hidden);
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, $item->min_age);
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, $item->max_age);
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, isset($item->postpartum) ? MedicusHelper::yesnoList()[$item->postpartum] : $item->postpartum);
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, isset($data->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("V" . $count, isset($item->breastfeeding) ? MedicusHelper::yesnoList()[$item->breastfeeding] : $item->breastfeeding);
            $objPHPExcel->getActiveSheet()->SetCellValue("W" . $count, isset($item->menopause_restrictions) ? MedicusHelper::yesnoList()[$item->menopause_restrictions] : $item->menopause_restrictions);
            $objPHPExcel->getActiveSheet()->SetCellValue("X" . $count, isset($item->group) ? $item->group->name : $item->group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("Y" . $count, $item->layout_template);
            $objPHPExcel->getActiveSheet()->SetCellValue("Z" . $count, $supportSubProjects === true ? Question::getQuestionSubProjectNames($item->id, true) : '');

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('Z' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportPanels($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Info");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Mobile Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Icon");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Mobile Only");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Group");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->info);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->mobile_order);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, isset($item->icon) ? basename(Yii::$app->homeUrl . $item->icon) : $item->icon);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset(MedicusHelper::yesnoList()[$item->mobile_only]) ? MedicusHelper::yesnoList()[$item->mobile_only] : $item->mobile_only);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->group) ? $item->group->name : $item->group_id);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportUnits($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "UCUM");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->ucum);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkerUnits($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Unit");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Digit");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Fraction");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "LOINC Code");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->biomarker) ? $item->biomarker->symbol : $item->biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, isset($item->unit) ? $item->unit->symbol : $item->unit_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->digit);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->fraction);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->loinc_code);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkerRegions($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Region");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Abbreviation");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "How shown in insight");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Unit");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->biomarker) ? $item->biomarker->symbol : $item->biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, isset($item->region_id) ? $item->region->name : $item->region_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->abbreviation);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->variable_name);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($item->unit) ? $item->unit->name : $item->unit_id);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCoachingPrograms($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Category");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Description");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Include Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Exclude Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Rank");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Icon");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Is Featured");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Image");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->coachingCategory) ? $item->coachingCategory->name : $item->coaching_category_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->description);

            $item->include_tag = !empty($item->include_tag) ? explode(",", $item->include_tag) : [];
            $item->exclude_tag = !empty($item->exclude_tag) ? explode(",", $item->exclude_tag) : [];

            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, implode(" | ", $item->include_tag));
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, implode(" | ", $item->exclude_tag));
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->rank);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, isset($item->icon) ? basename(Yii::$app->homeUrl . $item->icon) : $item->icon);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, is_null($item->is_featured) ? "No" : \common\component\helpers\MedicusHelper::yesnoList()[$item->is_featured]);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, isset($item->image) ? basename(Yii::$app->homeUrl . $item->image) : $item->image);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCoachingSteps($path, $name, $dataArray, $category = "Sheet_1")
    {
        $tags = ArrayHelper::map(Tag::find()->all(), 'id', 'name');
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Level");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Program: Level Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Description");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Period");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Repetition");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Health Kit Equivalent");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Icon");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Use Watch");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Use Health Kit");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Disclaimer");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Include Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Exclude Tag");
        $count = 2;

        foreach ($dataArray as $item) {
            $res = [];
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, "ID: " . $item->level_id . " Order: " . $item->coachingLevel->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, CoachingStep::programLevels()[$item->level_id]);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->description);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->period);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->repetition);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->health_kit_equivalent);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->icon) ? basename(Yii::$app->homeUrl . $item->icon) : $item->icon);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, isset($item->use_watch) ? MedicusHelper::yesnoList()[$item->use_watch] : $item->use_watch);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, isset($item->use_healthkit) ? MedicusHelper::yesnoList()[$item->use_healthkit] : $item->use_healthkit);
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->disclaimer);

            $item->include_tag = !empty($item->include_tag) ? explode(",", $item->include_tag) : [];
            foreach ($item->include_tag as $value) {
                $tag_id = array_key_exists($value, $tags);
                if ($tag_id !== false)
                    $res[] = $tags[$value];
            }
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, implode(" | ", $res));

            $res = [];
            $item->exclude_tag = !empty($item->exclude_tag) ? explode(",", $item->exclude_tag) : [];
            foreach ($item->exclude_tag as $value) {
                $tag_id = array_key_exists($value, $tags);
                if ($tag_id !== false)
                    $res[] = $tags[$value];
            }
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, implode(" | ", $res));

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCoachingStepHints($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Coaching Step ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Text");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->coaching_step_id) ? CoachingSource::programLevelSteps()[$item->coaching_step_id] : $item->coaching_step_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->text);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCoachingQuotes($path, $name, $dataArray, $category = "Sheet_1")
    {
        $tags = ArrayHelper::map(Tag::find()->all(), 'id', 'name');
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Level ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Step");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Text");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Quote Location");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Program: Level Order");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->level_id);

            $step = \common\models\CoachingStep::find()->where(['id' => $item->step])->one();
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, isset($item->step) ? $step->title : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->text);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->quote_location);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($item->level_id) ? CoachingStep::programLevels()[$item->level_id] : null);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportWellbeingAspects($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Text");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Sub Text");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Level");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Stage");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Is Mandatory");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Can Has Skip");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Info");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Answer Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "Element");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "Scorable");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "Layout Template");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "Icon");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "interval");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Regions");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Recommendation Text");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->text);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->subtext);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->is_biomarker ? 'Biomarker' : 'Question');
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->level);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->stage);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $item->is_mandatory ? 'Yes' : 'No');
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, !empty($item->can_be_skipped) ? MedicusHelper::yesnoList()[$item->can_be_skipped] : 'No');
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, $item->info);
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->answer_type);

            $element = \common\models\WellbeingElement::findOne(['id' => $item->element_id]);
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, isset($element) ? $element->name : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, isset($item->scorable) ? 'Yes' : 'No');
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, $item->layout_template);
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, $item->icon);
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, isset($item->group) ? $item->group->name : $item->group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, $item->interval);
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, isset($item->regions) ? Region::validateRegions($item->regions) : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, $item->recommendation_text);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('T' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('U' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(75);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportWellbeingAnswers($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Info");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Score");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Aspect");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Answer ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Mapped Answer");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Parent");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->info);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->score);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->order);

            $aspect = \common\models\WellbeingAspect::findOne(['id' => $item->question_id]);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($aspect) ? $aspect->symbol : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->answer_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->mapped_answer) ? WellbeingAnswer::parentList($item->mapped_answer) : $item->mapped_answer);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, isset($item->parent_id) ? WellbeingAnswer::parentList($item->parent_id) : $item->parent_id);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportWellbeingInsights($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Aspect");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Element");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Min Score");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Max Score");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Statement");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Include Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Exclude Tag");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Is Tip");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "Disclaimer");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);

            $aspect = \common\models\WellbeingAspect::findOne(['id' => $item->aspect_id]);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($aspect) ? $aspect->symbol : null);

            $element = \common\models\WellbeingElement::findOne(['id' => $item->element_id]);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, isset($element) ? $element->name : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->min_score);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->max_score);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->statement);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->include_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->exclude_tag);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $item->is_tip ? 'Yes' : 'No');
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, isset($item->group) ? $item->group->name : $item->group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->disclaimer);

            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkersWithVariables($path, $name, $dataArray, $region, $language = "en", $supportSubProjects = false, $category = "Sheet_1")
    {
        $biomarkers_variables = VariableHelper::getAllBiomarkersVariable($region, $language);

        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Panel");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Days");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Time");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Method");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "What");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "What Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Why");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "Why Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "How");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "How Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "HomeWell How");
        $objPHPExcel->getActiveSheet()->SetCellValue("O" . 1, "HomeWell How Replaced");
        $objPHPExcel->getActiveSheet()->SetCellValue("P" . 1, "pregnant why");
        $objPHPExcel->getActiveSheet()->SetCellValue("Q" . 1, "Has Fixed Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("R" . 1, "Is Added Manually");
        $objPHPExcel->getActiveSheet()->SetCellValue("S" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("T" . 1, "Allow Switch Detail");
        $objPHPExcel->getActiveSheet()->SetCellValue("U" . 1, "Parent Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("V" . 1, "Related Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("W" . 1, "Visible");
        $objPHPExcel->getActiveSheet()->SetCellValue("X" . 1, "Is Normal Range Mandatory");
        $objPHPExcel->getActiveSheet()->SetCellValue("Y" . 1, "Show Parent");
        $objPHPExcel->getActiveSheet()->SetCellValue("Z" . 1, "Is Omitted");
        $objPHPExcel->getActiveSheet()->SetCellValue("AA" . 1, "Is Valid");
        $objPHPExcel->getActiveSheet()->SetCellValue("AB" . 1, "Calculated From");
        $objPHPExcel->getActiveSheet()->SetCellValue("AC" . 1, "Sample");
        $objPHPExcel->getActiveSheet()->SetCellValue("AD" . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue("AE" . 1, "Update Reference Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("AF" . 1, "Range Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AG" . 1, "Is Common");
        $objPHPExcel->getActiveSheet()->SetCellValue("AH" . 1, "Show Scale");
        $objPHPExcel->getActiveSheet()->SetCellValue("AI" . 1, "Is Activity Marker");
        $objPHPExcel->getActiveSheet()->SetCellValue("AJ" . 1, "Has Abnormal Range");
        $objPHPExcel->getActiveSheet()->SetCellValue("AK" . 1, "Show Lingual Reading");
        $objPHPExcel->getActiveSheet()->SetCellValue("AL" . 1, "Is Hidden");
        $objPHPExcel->getActiveSheet()->SetCellValue("AM" . 1, "Is Smart");
        $objPHPExcel->getActiveSheet()->SetCellValue("AN" . 1, "Is Mandatory");
        $objPHPExcel->getActiveSheet()->SetCellValue("AO" . 1, "Medical Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AP" . 1, "Colors Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("AQ" . 1, "Show History");
        $objPHPExcel->getActiveSheet()->SetCellValue("AR" . 1, "Projects");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->panel) ? $item->panel->name : $item->panel_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, !empty($item->type) ? \common\models\Biomarker::biomarkerTypes()[$item->type] : $item->type);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->days);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, isset($item->timing) ? $item->timing->timing : $item->time);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($item->biomarkerMethod) ? implode(" | ", ArrayHelper::map($item->biomarkerMethod, 'id', 'method.name')) : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->what);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, LanguageHelper::replaceTextAndVariables("biomarker", $item->what, $biomarkers_variables, $language));
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $item->why);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, LanguageHelper::replaceTextAndVariables("biomarker", $item->why, $biomarkers_variables, $language));
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->how);
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, LanguageHelper::replaceTextAndVariables("biomarker", $item->how, $biomarkers_variables, $language));
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, $item->homewell_how);
            $objPHPExcel->getActiveSheet()->SetCellValue("O" . $count, LanguageHelper::replaceTextAndVariables("biomarker", $item->homewell_how, $biomarkers_variables, $language));
            $objPHPExcel->getActiveSheet()->SetCellValue("P" . $count, $item->pregnant_why);
            $objPHPExcel->getActiveSheet()->SetCellValue("Q" . $count, isset($item->has_fixed_ranges) ? MedicusHelper::yesnoList()[$item->has_fixed_ranges] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("R" . $count, isset($item->is_add_manual) ? MedicusHelper::yesnoList()[$item->is_add_manual] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("S" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("T" . $count, isset($item->allow_switch_detail) ? MedicusHelper::yesnoList()[$item->allow_switch_detail] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("U" . $count, isset($item->parentSymbol) ? $item->parentSymbol : $item->parent_biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("V" . $count, isset($item->relatedBiomarkerSymbol) ? $item->relatedBiomarkerSymbol : $item->related_biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("W" . $count, isset($item->visible) ? MedicusHelper::yesnoList()[$item->visible] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("X" . $count, isset($item->is_normal_range_mandatory) ? MedicusHelper::yesnoList()[$item->is_normal_range_mandatory] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("Y" . $count, isset($item->show_parent) ? MedicusHelper::yesnoList()[$item->show_parent] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("Z" . $count, isset($item->is_omitted) ? MedicusHelper::yesnoList()[$item->is_omitted] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AA" . $count, isset($item->is_valid) ? MedicusHelper::yesnoList()[$item->is_valid] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AB" . $count, $item->calculated_from);
            $objPHPExcel->getActiveSheet()->SetCellValue("AC" . $count, isset($item->biomarkerSample) ? implode(" | ", ArrayHelper::map($item->biomarkerSample, 'id', 'sample.name')) : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AD" . $count, isset($item->validity) ? \common\component\helpers\MedicusHelper::validityValues()[$item->validity] : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("AE" . $count, isset($item->update_reference_range) ? MedicusHelper::yesnoList()[$item->update_reference_range] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AF" . $count, isset($item->range_type) ? Biomarker::biomarkerRangeTypes()[$item->range_type] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AG" . $count, isset($item->is_common) ? MedicusHelper::yesnoList()[$item->is_common] : "No");
            $objPHPExcel->getActiveSheet()->SetCellValue("AH" . $count, isset($item->show_scale) ? MedicusHelper::yesnoList()[$item->show_scale] : "Yes");
            $objPHPExcel->getActiveSheet()->SetCellValue("AI" . $count, isset(MedicusHelper::yesnoList()[$item->is_activity_marker]) ? MedicusHelper::yesnoList()[$item->is_activity_marker] : $item->is_activity_marker);
            $objPHPExcel->getActiveSheet()->SetCellValue("AJ" . $count, isset(MedicusHelper::yesnoList()[$item->hasAbnormalRange]) ? MedicusHelper::yesnoList()[$item->hasAbnormalRange] : $item->hasAbnormalRange);
            $objPHPExcel->getActiveSheet()->SetCellValue("AK" . $count, isset(MedicusHelper::yesnoList()[$item->showLingualReading]) ? MedicusHelper::yesnoList()[$item->showLingualReading] : $item->showLingualReading);
            $objPHPExcel->getActiveSheet()->SetCellValue("AL" . $count, isset($item->is_hidden) ? MedicusHelper::yesnoList()[$item->is_hidden] : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AM" . $count, isset($item->is_smart) ? MedicusHelper::yesnoList()[$item->is_smart] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AN" . $count, isset($item->is_mandatory) ? MedicusHelper::yesnoList()[$item->is_mandatory] : "No");
            $objPHPExcel->getActiveSheet()->SetCellValue("AO" . $count, !empty($item->medical_type) ? Biomarker::medicalTypes()[$item->medical_type] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AP" . $count, !empty($item->colors_type) ? Biomarker::colorsTypes()[$item->colors_type] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AQ" . $count, !empty($item->show_history) ? MedicusHelper::yesnoList()[$item->show_history] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("AR" . $count, $supportSubProjects === true ? Biomarker::getBiomarkerSubProjectNames($item->id, true) : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("AS" . $count, isset(MedicusHelper::yesnoList()[$item->showLingualReadingStatement]) ? MedicusHelper::yesnoList()[$item->showLingualReadingStatement] : $item->showLingualReadingStatement);
            $objPHPExcel->getActiveSheet()->SetCellValue("AT" . $count, isset($item->customLingualReadingStatement) ?$item->customLingualReadingStatement : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("AU" . $count, isset($item->customTranslation) ?$item->customTranslation : "");

            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('AR' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AG')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AH')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AI')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AJ')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AK')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AL')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AM')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AN')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AO')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AQ')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AR')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('AS')->setWidth(20);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportMissingUnitConversions($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Biomarker");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "From");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "To");


        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item["biomarker"]);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item["from"]);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item["to"]);

            $objPHPExcel->getActiveSheet()->getStyle('A' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportInvalidInsights($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Introductory Statement");


        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->type) ? $item->type->name : $item->type_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->introductory_statement);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(60);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkerSynonyms($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Synonym ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Synonym Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Language");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Order");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->biomarker) ? $item->biomarker->symbol : $item->biomarker_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->language);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->order);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);

        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBacteriaSynonyms($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Synonym ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Bacteria Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Synonym Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Language");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($item->bacteria) ? $item->bacteria->name : $item->bacteria_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->order);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->language);


            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);

        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBacterias($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Bacteria ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Bacteria Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Bacteria Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Bacteria Parents");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Code");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Is Common");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "Group");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "Gender");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "Pregnancy");

        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->type);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->name);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->parentsNames);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->code);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, isset($item->is_common) ? MedicusHelper::yesnoList()[$item->is_common] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->group);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, isset($item->gender) ? MedicusHelper::genderList()[$item->gender] : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, isset($item->pregnancy) ? MedicusHelper::yesnoList()[$item->pregnancy] : null);


            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);

        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportBiomarkerStatistics($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Biomarker Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker Name");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Biomarker Abbreviation");
        if (isset($dataArray[0]['untranslated-to'])) {
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Untranslated To");
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        }
        $count = 2;

        foreach ($dataArray as $value) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $value['symbol']);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $value['name']);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $value['abbreviation']);
            if (isset($value['untranslated-to']))
                $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $value['untranslated-to']);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(100);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportSmartAlertRule($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Exam Type");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Alert Type");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->exam_type);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->alert_type);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportSmartAlertRulePart($path, $name, $dataArray, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Biomarker Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Smart alert rule ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Biomarker value");
        $count = 2;

        foreach ($dataArray as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->biomarker->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->smart_alert_rule_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->biomarker_value);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }
    
    public static function exportDefinitions($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Search");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Word");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Definition");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Synonyms");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Validity");
        $definitions = isset($filteredData) ? $filteredData : BiomarkerSource::find()->all();
        $count = 2;

        foreach ($definitions as $definition) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $definition->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $definition->search);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $definition->word);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $definition->definition);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $definition->synonyms);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, isset($definition->validity) ? MedicusHelper::validityValues()[$definition->validity] : '');

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(35);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportDefinitionSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Definition");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Order");
        $sources = isset($filteredData) ? $filteredData : DefinitionSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, isset($source->definition) ? $source->definition->word : null);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->order);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCoachingSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Step ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Step Program");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Step Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Step Description");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "Step Source Link");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "Order");
        $sources = isset($filteredData) ? $filteredData : CoachingSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $source->step_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->getStepProgram());
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->getStepTitle());
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->getStepDescription());
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $source->link);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $source->order);

            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(75);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(75);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportWellbeingSources($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Title");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Source");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Insight ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Order");
        $sources = isset($filteredData) ? $filteredData : WellbeingInsightSource::find()->all();
        $count = 2;

        foreach ($sources as $source) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $source->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $source->title);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $source->source);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $source->insight_id);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $source->order);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(100);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportData($items, $name, $path, $category = "Sheet_1", $lab_id = null)
    {
        $currentLab = $lab_id? Lab::findOne($lab_id):Lab::findOne(1);
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Note");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Field Name");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "English");
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . 1, "ar");
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . 1, "de");
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . 1, "fr");
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . 1, "zh-CN");
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . 1, "pt-PT");
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . 1, "pt-BR");
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . 1, "zh-HK");

        $objPHPExcel->getActiveSheet()->SetCellValue('L' . 1, "nl-NL");
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . 1, "tr-TR");
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . 1, "it-IT");
        $objPHPExcel->getActiveSheet()->SetCellValue('O' . 1, "es-LA");
        if ($currentLab->is_informal == 1 && isset($items[0]) && isset($items[0]["P"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('P' . 1, "es-LA_male");
        }
        if ($currentLab->is_informal == 1 && isset($items[0]) && isset($items[0]["Q"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('Q' . 1, "es-LA_female");
        }
        if (isset($items[0]) && isset($items[0]["R"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('R' . 1, "Insight ID");
        }
        if (isset($items[0]) && isset($items[0]["S"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('S' . 1, "Insight Type");
        }
        if (isset($items[0]) && isset($items[0]["T"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('T' . 1, "Insight Group");
        }
        if (isset($items[0]) && isset($items[0]["U"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('U' . 1, "Question Symbol");
        }
        if (isset($items[0]) && isset($items[0]["V"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('V' . 1, "Program Name");
        }
        if (isset($items[0]) && isset($items[0]["W"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('W' . 1, "Biomarker Symbol");
        }
        if (isset($items[0]) && isset($items[0]["X"])) {
            $objPHPExcel->getActiveSheet()->SetCellValue('X' . 1, "Biomarker Release Group");
        }
        $count = 2;
        foreach ($items as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $count, $item["A"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $count, $item["B"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $count, $item["C"]);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $count, $item["D"]);

            $objPHPExcel->getActiveSheet()->getStyle('A' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);


            if (isset($item["E"]) && isset($item["F"]) && isset($item["G"]) && isset($item["H"]) && isset($item["I"]) && isset($item["J"]) && isset($item["K"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('E' . $count, $item["E"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('F' . $count, $item["F"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('G' . $count, $item["G"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('H' . $count, $item["H"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('I' . $count, $item["I"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('J' . $count, $item["J"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('K' . $count, $item["K"]);
                $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            }
            if (isset($item["L"]) && isset($item["M"]) && isset($item["N"]) && isset($item["O"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('L' . $count, $item["L"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('M' . $count, $item["M"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('N' . $count, $item["N"]);
                $objPHPExcel->getActiveSheet()->SetCellValue('O' . $count, $item["O"]);
                $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyle('O' . $count)->getAlignment()->setWrapText(true);
            }
            if (isset($item["P"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('P' . $count, $item["P"]);
            }
            if (isset($item["Q"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('Q' . $count, $item["Q"]);
            }
            if (isset($item["R"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('R' . $count, $item["R"]);
            }
            if (isset($item["S"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('S' . $count, $item["S"]);
            }
            if (isset($item["T"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('T' . $count, $item["T"]);
            }
            if (isset($item["U"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('U' . $count, $item["U"]);
            }
            if (isset($item["V"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('V' . $count, $item["V"]);
            }
            if (isset($item["W"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('W' . $count, $item["W"]);
            }
            if (isset($item["X"])) {
                $objPHPExcel->getActiveSheet()->SetCellValue('X' . $count, $item["X"]);
            }

            $count++;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . DIRECTORY_SEPARATOR . $name . ".xlsx");
    }

    public static function columnList()
    {
        return [
            'biomarker' => ['why', 'what', 'how', 'homewell_how', 'pregnant_why'],
            'biomarker_region' => ['name', 'abbreviation'],
            'definition' => ['word', 'definition', 'synonyms', 'search'],
            'insight' => ['introductory_statement', 'title', 'missing_field'],
            'question' => ['text', 'attribute', 'sub_question', 'info'],
            'answer' => ['value', 'more_details'],
        ];
    }

    public static function searchColumn($table, $column)
    {

    }

    public static function insertIntoMissing(&$missing_file, $word, $file, $table, $ID)
    {
        $missing_file[$word]["file"] = $file;
        $missing_file[$word]["table"] = $table;
        $missing_file[$word]["ID"] = $ID;
    }

    public static function exportAll($path, $zip_name)
    {
        $missing_file = [];
        //insights
        $insight_types = \common\models\InsightType::find()->all();
        foreach ($insight_types as $insight_type) {

            $insights = Insight::find()->where(["type_id" => $insight_type->id])->all();
            $arr = [];
            $count = 1;
            foreach ($insights as $insight) {
                $tmp["A"] = "#" . $insight->id;
                $tmp["B"] = $count;
                $tmp["C"] = $insight->introductory_statement;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Insights_" . str_replace(" ", "_", $insight_type->name), "insight", $insight->id);
            }
            self::exportData($arr, "Insights_" . str_replace(" ", "_", $insight_type->name), $path, Insight::tableName());
        }


        //insights type
        $insight_types = \common\models\InsightType::find()->all();
        $arr = [];
        $count = 1;
        foreach ($insight_types as $insight_type) {
            $tmp["A"] = $insight_type->id;
            $tmp["B"] = $count;
            $tmp["C"] = $insight_type->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Insights_Types", "insight_type", $insight_type->id);
        }
        self::exportData($arr, "Insights_Types", $path, InsightType::tableName());


        //Custom
        $custom = LanguageSource::find()->where(["category" => "custom"])->orderBy(["category" => SORT_DESC])->all();
        $arr = [];
        $count = 1;
        foreach ($custom as $model) {
            $tmp["B"] = $count;
            $tmp["A"] = $model->category;
            $tmp["C"] = $model->message;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Viro_Content", "viro", "no_id");
        }
        self::exportData($arr, "Viro_Content", $path, InsightType::tableName());


        //insight titles
        $models = \common\models\Insight::find()->select(["insight.title", "insight.id", "insight.type_id"])->joinWith("type")->orderBy("insight.type_id")->all();
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            if (!empty($model->title)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $model->type->name;
                $tmp["C"] = $model->title;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Insights_Titles", "insight", $model->id);
            }
        }
        self::exportData($arr, "Insights_Titles", $path, Insight::tableName());


        //insight stats missing field
        $models = \common\models\insight::find()->where([/*"type_id" => 10*/])->all();//10 is statistics
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->missing_field;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Insights_stats_extra", "insight", $model->id);
        }
        self::exportData($arr, "Insights_stats_extra", $path, Insight::tableName());


        //question type
        $models = \common\models\QuestionType::find()->select(["id", "name"])->all();
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Question_Type", "question_type", $model->id);
        }
        self::exportData($arr, "Question_Type", $path, Question::tableName());


        //question attribute
        $questions = \common\models\Question::find()->select(["id", "attribute", "text", "info", 'sub_question'])->all();
        $arr = [];
        $count = 1;
        foreach ($questions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->attribute;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Question_Attribute", "question", $model->id);
        }
        self::exportData($arr, "Question_Attribute", $path, Question::tableName());


        //question text
        $arr = [];
        $count = 1;
        foreach ($questions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->text;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Question_Text", "question", $model->id);
        }
        self::exportData($arr, "Question_Text", $path, Question::tableName());

        //question info
        $arr = [];
        $count = 1;
        foreach ($questions as $model) {
            if (!empty($model->info)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $count;
                $tmp["C"] = $model->info;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Question_Info", "question", $model->id);
            }
        }
        self::exportData($arr, "Question_Info", $path, Question::tableName());


        //question sub_question
        $arr = [];
        $count = 1;
        foreach ($questions as $model) {
            if (!empty($model->sub_question)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $count;
                $tmp["C"] = $model->sub_question;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Question_Sub_Question", "question", $model->id);
            }
        }
        self::exportData($arr, "Question_Sub_Question", $path, Question::tableName());

        //answers
        $answers = \common\models\Answer::find()->select(["id", "value"])->where(["not in", "question_id", [41, 42]])->all();
        $arr = [];
        $count = 1;
        foreach ($answers as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->value;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Answer", "answer", $model->id);
        }
        self::exportData($arr, "Answer", $path, Answer::tableName());


        //answer more_details
        $answers_more_details = \common\models\Answer::find()->select(["id", "more_details"])->all();
        $arr = [];
        $count = 1;
        foreach ($answers_more_details as $model) {
            if (!empty($model->more_details)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $count;
                $tmp["C"] = $model->more_details;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Answer_More_Details", "answer", $model->id);
            }
        }
        self::exportData($arr, "Answer_More_Details", $path, Answer::tableName());

        //Medications
        $medication = \common\models\Answer::find()->select(["id", "value"])->where(["in", "question_id", [41, 42]])->orderBy("question_id")->all();
        $arr = [];
        $count = 1;
        foreach ($medication as $model) {
            if (!empty($model->value)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $count;
                $tmp["C"] = $model->value;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Medications", "answer", $model->id);
            }
        }
        self::exportData($arr, "Medications", $path, Answer::tableName());


        //panels name
        $panels = \common\models\Panel::find()->select(["id", "info", "name"])->all();
        $arr = [];
        $count = 1;
        foreach ($panels as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Panel_Name", "panel", $model->id);
        }
        self::exportData($arr, "Panel_Name", $path, Panel::tableName());


        //panels info
        $arr = [];
        $count = 1;
        foreach ($panels as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $model->name;
            $tmp["C"] = $model->info;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Panel_Info", "panel", $model->id);
        }
        self::exportData($arr, "Panel_Info", $path, Panel::tableName());

        //biomarker why
        \common\models\Biomarker::$static_region_id = 29;
        $biomarkers = \common\models\Biomarker::find()->all();
        $arr = [];
        $count = 1;
        foreach ($biomarkers as $model) {
            $tmp["A"] = $model->symbol;
            $tmp["B"] = $count;
            $tmp["C"] = $model->why;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_Why", "biomarker", $model->id);
        }
        self::exportData($arr, "Biomarker_Why", $path, Biomarker::tableName());


        //biomarker pregnant why ,pregnant_why
        $arr = [];
        $count = 1;
        foreach ($biomarkers as $model) {
            if (!empty($model->pregnant_why)) {
                $tmp["A"] = $model->symbol;
                $tmp["B"] = $count;
                $tmp["C"] = $model->pregnant_why;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_Pregnant_why", "biomarker", $model->id);
            }
        }
        self::exportData($arr, "Biomarker_Pregnant_why", $path, Biomarker::tableName());


        //biomarker what
        $arr = [];
        $count = 1;
        foreach ($biomarkers as $model) {
            $tmp["A"] = $model->symbol;
            $tmp["B"] = $count;
            $tmp["C"] = $model->what;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_What", "biomarker", $model->id);
        }
        self::exportData($arr, "Biomarker_What", $path, Biomarker::tableName());


        //biomarker how
        $arr = [];
        $count = 1;
        foreach ($biomarkers as $model) {
            $tmp["A"] = $model->symbol;
            $tmp["B"] = $count;
            $tmp["C"] = $model->how;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_How", "biomarker", $model->id);
        }
        self::exportData($arr, "Biomarker_How", $path, Biomarker::tableName());

        //biomarker homewell how
        $arr = [];
        $count = 1;
        foreach ($biomarkers as $model) {
            $tmp["A"] = $model->symbol;
            $tmp["B"] = $count;
            $tmp["C"] = $model->homewell_how;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_HomeWell_How", "biomarker", $model->id);
        }
        self::exportData($arr, "Biomarker_HomeWell_How", $path, Biomarker::tableName());


        //biomarker names
        $biomarker_regions = \common\models\BiomarkerRegion::find()->select(["name", "id"])->groupBy("name")->orderBy('id')->all();
        $arr = [];
        $count = 1;
        foreach ($biomarker_regions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_Name", "biomarker_region", $model->id);
        }
        self::exportData($arr, "Biomarker_Name", $path, BiomarkerRegion::tableName());


        //biomarker abbreviation
        $biomarker_regions = \common\models\BiomarkerRegion::find()->select(["abbreviation", "id"])->orderBy('id')->all();
        $arr = [];
        $count = 1;
        foreach ($biomarker_regions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->abbreviation;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_Abbreviation", "biomarker_region", $model->id);
        }
        self::exportData($arr, "Biomarker_Abbreviation", $path, BiomarkerRegion::tableName());


        //biomarker synonyms
        $models = \common\models\BiomarkerSynonym::find()->orderBy(['biomarker_id' => 'biomarker_id', 'language' => 'language'])->all();
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            $tmp["A"] = $model->biomarker->symbol;
            $tmp["B"] = $model->language;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            $missing_file[$tmp["C"]]["file"] = "Biomarker_Synonym";
            self::insertIntoMissing($missing_file, $tmp["C"], "Biomarker_Synonym", "biomarker_synonym", $model->id);
        }
        self::exportData($arr, "Biomarker_Synonym", $path, BiomarkerSynonym::tableName());


        //tooltip titles
        $tooltips = \common\models\Tooltip::find()->all();
        $arr = [];
        $count = 1;
        foreach ($tooltips as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->title;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Tooltip_Title", "tooltip", $model->id);
        }
        self::exportData($arr, "Tooltip_Title", $path, Tooltip::tableName());


        //tooltip text
        $arr = [];
        $count = 1;
        foreach ($tooltips as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->text;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Tooltip_Text", "tooltip", $model->id);

        }
        self::exportData($arr, "Tooltip_Text", $path, Tooltip::tableName());


        //definition word
        $definitions = \common\models\Definition::find()->all();
        $arr = [];
        $count = 1;
        foreach ($definitions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->word;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Definitions_Word", "definition", $model->id);
        }
        self::exportData($arr, "Definitions_Word", $path, Definition::tableName());


        //definition text description
        $arr = [];
        $count = 1;
        foreach ($definitions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $model->word;
            $tmp["C"] = $model->definition;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Definitions_Description", "definition", $model->id);
        }
        self::exportData($arr, "Definitions_Description", $path, Definition::tableName());


        //definition synonyms
        $arr = [];
        $count = 1;
        foreach ($definitions as $model) {
            if (!empty($model->synonyms)) {
                $tmp["A"] = $model->id;
                $tmp["B"] = $model->word;
                $tmp["C"] = $model->synonyms;
                $arr[] = $tmp;
                $count++;
                self::insertIntoMissing($missing_file, $tmp["C"], "Definitions_Synonyms", "definition", $model->id);
            }
        }
        self::exportData($arr, "Definitions_Synonyms", $path, Definition::tableName());

        //definition search
        $arr = [];
        $count = 1;
        foreach ($definitions as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $model->word;
            $tmp["C"] = $model->search;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Definitions_Search", "definition", $model->id);
        }
        self::exportData($arr, "Definitions_Search", $path, Definition::tableName());


        //regions
        $models = \common\models\Region::find()->all();
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Region", "region", $model->id);
        }
        self::exportData($arr, "Region", $path, Region::tableName());


        //Countries
        $models = \common\models\Country::find()->all();
        $arr = [];
        $count = 1;
        foreach ($models as $model) {
            $tmp["A"] = $model->id;
            $tmp["B"] = $count;
            $tmp["C"] = $model->name;
            $arr[] = $tmp;
            $count++;
            self::insertIntoMissing($missing_file, $tmp["C"], "Countries", "country", $model->id);
        }
        self::exportData($arr, "Countries", $path, Country::tableName());

        //missing
        $translated = LanguageTranslate::find()->select(["id"])->where(["language" => "fr"])->distinct()->all();
        $translated = ArrayHelper::map($translated, "id", "id");
        $missing_models = LanguageSource::find()->where(["NOT IN", "id", $translated])->andWhere(["NOT IN", "category", ["yii", "app"]])->orderBy(["category" => SORT_ASC])->all();
        $missing = [];
        foreach ($missing_models as $missing_model) {
            if (!empty($missing_model->message)) {
                $tmp["A"] = isset($missing_file[$missing_model->message]) ? $missing_file[$missing_model->message]['ID'] : "Missing ID:  " . $missing_model->category;//$missing_model->id;
                $tmp["B"] = isset($missing_file[$missing_model->message]) ? "Category: " . $missing_file[$missing_model->message]['table'] . ",  File: " . $missing_file[$missing_model->message]['file'] : "Missing Note: " . $missing_model->category;
                $tmp["C"] = $missing_model->message;
                $missing[] = $tmp;
            }
        }

        self::exportData($missing, "MISSING_fr", $path);

        //missing
        $translated = LanguageTranslate::find()->select(["id"])->where(["language" => "ar"])->distinct()->all();
        $translated = ArrayHelper::map($translated, "id", "id");
        $missing_models = LanguageSource::find()->where(["NOT IN", "id", $translated])->andWhere(["NOT IN", "category", ["yii", "app"]])->orderBy(["category" => SORT_ASC])->all();
        $missing = [];
        foreach ($missing_models as $missing_model) {
            if (!empty($missing_model->message)) {
                $tmp["A"] = isset($missing_file[$missing_model->message]) ? $missing_file[$missing_model->message]['ID'] : "Missing ID:  " . $missing_model->category;//$missing_model->id;
                $tmp["B"] = isset($missing_file[$missing_model->message]) ? "Category: " . $missing_file[$missing_model->message]['table'] . ",  File: " . $missing_file[$missing_model->message]['file'] : "Missing Note: " . $missing_model->category;
                $tmp["C"] = $missing_model->message;
                $missing[] = $tmp;
            }
        }

        self::exportData($missing, "MISSING_ar", $path);


        //missing
        $translated = LanguageTranslate::find()->select(["id"])->where(["language" => "de"])->distinct()->all();
        $translated = ArrayHelper::map($translated, "id", "id");
        $missing_models = LanguageSource::find()->where(["NOT IN", "id", $translated])->andWhere(["NOT IN", "category", ["yii", "app"]])->orderBy(["category" => SORT_ASC])->all();
        $missing = [];
        foreach ($missing_models as $missing_model) {
            if (!empty($missing_model->message)) {
                $tmp["A"] = isset($missing_file[$missing_model->message]) ? $missing_file[$missing_model->message]['ID'] : "Missing ID:  " . $missing_model->category;//$missing_model->id;
                $tmp["B"] = isset($missing_file[$missing_model->message]) ? "Category: " . $missing_file[$missing_model->message]['table'] . ",  File: " . $missing_file[$missing_model->message]['file'] : "Missing Note: " . $missing_model->category;
                $tmp["C"] = $missing_model->message;
                $missing[] = $tmp;
            }
        }

        self::exportData($missing, "MISSING_de", $path);


        return self::zipFiles($path, $zip_name);
    }

    public static function exportMissing($path, $sub_project, $validity = null, $category = null, $lab_id = null)
    {
        $languages = Language::find()->where(['status' => Language::STATUS_ACTIVE])->andWhere(['not', ['language_id' => 'en']])->all();
        if (!empty($sub_project)) {
            $project_biomarker_ids = ArrayHelper::map(BiomarkerSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'biomarker_id', 'biomarker_id');
            $project_insight_ids = ArrayHelper::map(InsightSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'insight_id', 'insight_id');
            $project_question_ids = ArrayHelper::map(QuestionSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'question_id', 'question_id');
        }
        foreach ($languages as $language) {
            $translated = LanguageTranslate::find()->select(["id"])->where(["language" => $language->language_id])->distinct()->all();
            $translated = ArrayHelper::map($translated, "id", "id");

            $missing_models = LanguageSource::find()->where(["NOT IN", "id", $translated]);
            $missing_models = $category === null ?
                $missing_models->andWhere(["NOT IN", "category", ["yii", "app", "doctor_cr"]])
                :
                $missing_models->andWhere(['category' => $category]);
            $missing_models = $validity === null ?
                $missing_models->andWhere(['validity' => 2])
                :
                $missing_models->andWhere(['validity' => $validity]);

            $missing_models = $missing_models->orderBy(["category" => SORT_ASC])->all();
            $missing = [];

            foreach ($missing_models as $missing_model) {
                if (!empty($missing_model->message)) {
                    $tmp["A"] = $missing_model->id;
                    $tmp["B"] = $missing_model->category;
                    $tmp["C"] = $missing_model->field_name;
                    $tmp["D"] = $missing_model->message;
                    $tmp["E"] = '';
                    $tmp["F"] = '';
                    $tmp["G"] = '';
                    $tmp["H"] = '';
                    $tmp["I"] = '';
                    $tmp["J"] = '';
                    $tmp["K"] = '';
                    $tmp["L"] = '';
                    $tmp["M"] = '';
                    $tmp["N"] = '';
                    $tmp["O"] = '';
                    $tmp["P"] = '';
                    $tmp["Q"] = '';
                    $tmp["R"] = '';
                    $tmp["S"] = '';
                    $tmp["T"] = '';
                    $tmp["U"] = '';
                    $tmp["V"] = '';
                    if ($missing_model->category == 'insight') {
                        if (!empty($project_insight_ids)) {
                            $ins = Insight::find()->where(
                                [$missing_model->field_name => $missing_model->message])->andWhere(['in', 'id', $project_insight_ids])->with('type')->one();
                        } else {
                            $ins = Insight::find()->where(
                                [$missing_model->field_name => $missing_model->message])->with('type')->one();
                        }
                        if ($ins) {
                            $tmp["P"] = 'insight_' . $ins->universal_id;
                            $tmp["Q"] = isset($ins->type) ? $ins->type->name : 'not found';
                            $tmp["R"] = isset($ins->group) ? $ins->group->name : "";
                        } else {
                            unset($tmp);
                            continue;
                        }
                    } elseif ($missing_model->category == 'doctor_cr') {
                        if (!empty($project_insight_ids)) {
                            $ins = Insight::find()->where(['REGEXP', 'detailed_list', '(\s*\n|^)' . $missing_model->message . '\n'])->andWhere(['in', 'id', $project_insight_ids])->with('type')->one();
                        } else {
                            $ins = Insight::find()->where(['REGEXP', 'detailed_list', '(\s*\n|^)' . $missing_model->message . '\n'])->with('type')->one();
                        }
                        if ($ins) {
                            $tmp["P"] = 'insight_' . $ins->id;
                            $tmp["Q"] = isset($ins->type) ? $ins->type->name : 'not found';
                            $tmp["R"] = isset($ins->group) ? $ins->group->name : "";
                        } else {
                            unset($tmp);
                            continue;
                        }
                    } elseif ($missing_model->category == 'biomarker') {
                        if (!empty($project_biomarker_ids)) {
                            $bio = Biomarker::find()->where([
                                'OR',
                                ['what' => $missing_model->message],
                                ['why' => $missing_model->message],
                                ['how' => $missing_model->message],
                                ['homewell_how' => $missing_model->message],
                                ['pregnant_why' => $missing_model->message],
                            ])->andWhere(['in', 'id', $project_biomarker_ids])->one();
                        } else {
                            $bio = Biomarker::find()->where([
                                'OR',
                                ['what' => $missing_model->message],
                                ['why' => $missing_model->message],
                                ['how' => $missing_model->message],
                                ['homewell_how' => $missing_model->message],
                                ['pregnant_why' => $missing_model->message],
                            ])->one();
                        }

                        if (!$bio) {
                            unset($tmp);
                            continue;
                        }
                    } elseif ($missing_model->category == 'question') {
                        if (!empty($project_question_ids)) {
                            $ques = Question::find()->where([
                                'OR',
                                ['text' => $missing_model->message],
                                ['attribute' => $missing_model->message],
                                ['sub_question' => $missing_model->message],
                                ['info' => $missing_model->message],
                            ])->andWhere(['hidden' => 0])->andWhere(['in', 'id', $project_question_ids])->one();

                        } else {
                            $ques = Question::find()->where([
                                'OR',
                                ['text' => $missing_model->message],
                                ['attribute' => $missing_model->message],
                                ['sub_question' => $missing_model->message],
                                ['info' => $missing_model->message],
                            ])->andWhere(['hidden' => 0])->one();
                        }

                        if (!$ques) {
                            unset($tmp);
                            continue;
                        }
                    } elseif ($missing_model->category == 'answer') {

                        $answer = Answer::find()->where([
                            'OR',
                            ['value' => $missing_model->message],
                            ['more_details' => $missing_model->message],
                            ['definition' => $missing_model->message],
                            ['occurrence' => $missing_model->message],
                        ])->one();
                        if ($answer) {
                            $tmp["S"] = isset($answer->question) ? $answer->question->symbol : 'not found';
                        }
                    } elseif ($missing_model->category == 'coaching_step') {

                        $step = CoachingStep::find()->where([
                            'OR',
                            ['description' => $missing_model->message],
                            ['title' => $missing_model->message],
                        ])->one();
                        if ($step) {
                            $tmp["T"] = isset($step->program) ? $step->program['name'] : '';
                        }
                    } elseif ($missing_model->category == 'coaching_step_hint') {

                        $step_hint = CoachingStepHint::find()->with('coachingStep')->where(['text' => $missing_model->message])->one();
                        if ($step_hint) {
                            $tmp["T"] = isset($step_hint->coachingStep) ?
                                "Program: " . $step_hint->coachingStep->program['name'] . " ,Step: " . $step_hint->coachingStep->title : '';
                        }
                    } elseif ($missing_model->category == 'coaching_step_image') {

                        $step_image = CoachingStepImage::find()->with('coachingStep')->where(['description' => $missing_model->message])->one();
                        if ($step_image) {
                            $tmp["T"] = isset($step_image->coachingStep) ?
                                "Program: " . $step_image->coachingStep->program['name'] . " ,Step: " . $step_image->coachingStep->title : '';
                        }
                    } elseif ($missing_model->category == 'coaching_quote') {

                        $quote = CoachingQuote::find()->where(
                            ['text' => $missing_model->message]
                        )->one();
                        if ($quote) {
                            $tmp["T"] = isset($quote->program) ? $quote->program['name'] : '';
                        }
                    } elseif ($missing_model->category == 'biomarker_region') {
                        $items = ArrayHelper::map(BiomarkerRegion::find()->where([
                            'OR',
                            ['name' => $missing_model->message],
                            ['abbreviation' => $missing_model->message],
                            ['variable_name' => $missing_model->message],
                        ])->all(), "id", "biomarker_id");
                        $biomarker_ids = array_values($items);
                        $biomarkers = Biomarker::find()->where(['id' => $biomarker_ids])->all();
                        $release_groups = ArrayHelper::map($biomarkers, 'id', 'release_group_id');
                        $release_groups = array_values(array_unique($release_groups));
                        $release_groups = implode("|", $release_groups);
                        $biomarker_symbols_arr = array_values(ArrayHelper::map($biomarkers, "id", "symbol"));
                        $biomarker_symbols = implode("|", $biomarker_symbols_arr);
                        $tmp["U"] = $biomarker_symbols;
                        $tmp["V"] = $release_groups;

                    }
                    $missing[] = $tmp;
                }
            }
            if (!empty($sub_project)) {
                $name = SubProject::findOne(['id' => (int)$sub_project])->name;
                $name = str_replace(' ', '_', trim($name));
                $name .= '_';
            } else {
                $name = '';
            }
            $name .= $category === null ? "Missing_" . mb_strtoupper($language->language_id) : "Missing_" . $category . "_" . mb_strtoupper($language->language_id);
            self::exportData($missing, $name, $path,"Sheet_1", $lab_id);
        }


    }

    public static function zipFiles($path, $zip_name)
    {
        if (file_exists($path . DIRECTORY_SEPARATOR . $zip_name))
            unlink($path . DIRECTORY_SEPARATOR . $zip_name);

        $zip = new \ZipArchive();
        $zip->open($path . DIRECTORY_SEPARATOR . $zip_name, ZipArchive::CREATE | ZipArchive::OVERWRITE);

        // Create recursive directory iterator
        $rootPath = $path;
        $files = new RecursiveIteratorIterator(
            new RecursiveDirectoryIterator($rootPath),
            RecursiveIteratorIterator::LEAVES_ONLY
        );

        foreach ($files as $name => $file) {
            // Skip directories (they would be added automatically)
            if (!$file->isDir() && strpos($name, ".xlsx") > 0) {
                $zip->addFile($name, str_replace($path . DIRECTORY_SEPARATOR, "", $name));
            }
        }
        $zip->close();

        $full_path = Yii::$app->urlManager->hostInfo . Yii::$app->urlManager->baseUrl . DIRECTORY_SEPARATOR . $path . DIRECTORY_SEPARATOR . $zip_name . "";

        return $full_path;

    }

    public static function openExcelFile($path)
    {
        $excelReader = \PHPExcel_IOFactory::createReaderForFile($path);
        $excelReader->setReadDataOnly();
        $sheet_name = isset($excelReader->listWorksheetNames($path)[0]) ? $excelReader->listWorksheetNames($path)[0] : "__" . var_export($path, true) . "__";
        $excelObj = $excelReader->load($path);
        $obj = $excelObj->getActiveSheet()->toArray(null, true, true, true);
        return ["rows" => $obj, "sheet" => $sheet_name];
    }

    public static function emptyExcel($obj)
    {
        $first_key = key($obj);
        $first_key_child = key($obj[$first_key]);
        if (count($obj) == 1 && isset($obj[$first_key]) && empty($obj[$first_key][$first_key_child]))
            return true;
        return false;

    }

    public static function validateExcel($path, $file_names)
    {
        $error = [];
        if (count($file_names) == 0) {
            $error[] = "Zip file has no Excel files";
        }
        $categories = ExcelHelper::translationCategories();
        foreach ($file_names as $file_name) {
            $excel_file = self::openExcelFile($path . $file_name);
            $excel_sheet_name = $excel_file["sheet"];
            $excel_rows = $excel_file["rows"];
            $first_key = $excel_rows[key($excel_rows)];

            if (self::emptyExcel($excel_rows)) {
                $error[] = "Empty file '" . $file_name . "', Please open it and enable editing and save it.";
            }

            if ($first_key["A"] != "ID" || $first_key["B"] != "Note" || $first_key["C"] != "Field Name" || $first_key["D"] != "English" ||
                $first_key["E"] != "ar" || $first_key["F"] != "de" || $first_key["G"] != "fr" || $first_key["H"] != "zh-CN" ||
                $first_key["I"] != "pt-PT" || $first_key["J"] != "pt-BR" || $first_key["K"] != "zh-HK" || $first_key["L"] != "nl-NL" || $first_key["M"] != "tr-TR" || $first_key["N"] != "it-IT" || $first_key["O"] != "es-LA") {
                $error[] = "Different structure '" . $file_name . "',Header must be like ID, Note, Field Name, English, ar, de, fr, zh-CN, pt-PT, pt-BR, zh-HK, nl-NL, tr-TR, it-IT, es-LA." . var_export($first_key, true);
            }

            if (!ArrayHelper::isIn($excel_sheet_name, $categories)) {
                unset($excel_rows[key($excel_rows)]);
                foreach ($excel_rows as $excel_row) {
                    if (!empty($excel_row["B"]) && !ArrayHelper::isIn($excel_row["B"], $categories))
                        $error[] = "No category named '" . $excel_row["B"] . "' please change the Note field to an existing category in '" . $file_name . "' or delete the row if its added by mistake.";
                }
            };


        }
        return $error;
    }

    public static function validateDataImporterExcel($path, $file_names)
    {
        $error = [];
        if (count($file_names) == 0) {
            $error[] = "Zip file has no Excel files";
        }
        foreach ($file_names as $file_name) {
            $excel_file = self::openExcelFile($path . $file_name);
            $excel_rows = $excel_file["rows"];
            if (self::emptyExcel($excel_rows)) {
                $error[] = "Empty file '" . $file_name . "', Please open it and enable editing and save it.";
            }
        }
        return $error;
    }

    public static function languageTranslationSourceArray()
    {
        $models = LanguageSource::find()->where(["not in", "category", ["yii", "app", "javascript"]])->all();
        $res = [];
        foreach ($models as $row) {
            $res[self::cleanIndex($row["message"]) . "_" . $row["category"] . "_" . $row["field_name"]] = $row;
        }
        return $res;
    }

    public static function cleanIndex($word)
    {
        $res = trim($word);
        $res = str_replace("\n\r", "", $res);
        $res = str_replace("\r\n", "", $res);
        $res = str_replace("\r", "", $res);
        $res = str_replace("\n", "", $res);

        return $res;
    }

    public static function languageTranslationModels()
    {
        $models = LanguageTranslate::find()->all();
        $res = [];
        foreach ($models as $row) {
            $res[$row["id"] . "_" . $row["language"]] = $row;
        }
        return $res;
    }

    public static function importTranslationFromExcel($path, $models, $languageTranslationSourceArray, $database = null, $isServerSide = false, $lab_id = null)
    {
        $currentLab = ($lab_id)?Lab::findOne(['id' => $lab_id]) : Lab::findOne(1);
        $added = [];
        $missing = [];
        $excel_file = self::openExcelFile($path);
        $sheet_name = $excel_file["sheet"];
        $excel = $excel_file["rows"];
        unset($excel[key($excel)]);//remove file header
        $updated_insights = [];
        $updated_biomarkers = [];
        $updated_biomarker_regions = [];
        foreach ($excel as $row) {
            $word = $row["D"];
            $category = $row["B"];//trim($sheet_name, " ");
            $field_name = $row["C"];
            if ($category == "insight" && $field_name == "introductory_statement") {
                self::updatedInsights($updated_insights, $row["L"]);
            }
            if ($category == "insight" && $field_name == "detailed_list") {
                $word = preg_replace("/\s*\t*\n/", "\n", $word);
            }
            if ($category == "biomarker" && array_search($field_name, ['what', 'why', 'how', 'homewell_how', 'pregnant_why']) !== false) {
                self::updatedBiomarkers($updated_biomarkers, $word, $field_name);
            }
            if ($category == "biomarker_region" && array_search($field_name, ['name', 'abbreviation']) !== false) {
                self::updatedBiomarkerRegions($updated_biomarker_regions, $word, $field_name);
            }
            self::translateWord(self::columnLanguage("E"), $category, $word, $field_name, $row["E"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("F"), $category, $word, $field_name, $row["F"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("G"), $category, $word, $field_name, $row["G"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("H"), $category, $word, $field_name, $row["H"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("I"), $category, $word, $field_name, $row["I"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("J"), $category, $word, $field_name, $row["J"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("K"), $category, $word, $field_name, $row["K"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("L"), $category, $word, $field_name, $row["L"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("M"), $category, $word, $field_name, $row["M"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("N"), $category, $word, $field_name, $row["N"], $models, $languageTranslationSourceArray, $added, $missing);
            self::translateWord(self::columnLanguage("O"), $category, $word, $field_name, $row["O"], $models, $languageTranslationSourceArray, $added, $missing);
            if ($currentLab->is_informal == 1) {
                self::translateWord(self::columnLanguage("P"), $category, $word, $field_name, $row["P"], $models, $languageTranslationSourceArray, $added, $missing);
                self::translateWord(self::columnLanguage("Q"), $category, $word, $field_name, $row["Q"], $models, $languageTranslationSourceArray, $added, $missing);
            }
        }
        if (count($updated_insights) > 0) {
            if ($isServerSide === true && !empty($database)) {
                InsightDefinition::addTranslatedInsightDefinition($updated_insights, $database);
            } else {
                InsightDefinition::addTranslatedInsightDefinitionToAutoGenerate($updated_insights);
            }
        }

        if (count($updated_biomarkers) > 0) {
            if ($isServerSide == true && !empty($database)) {
                BiomarkerDefinition::addTranslatedBiomarkerDefinition($updated_biomarkers, $database);
            } else {
                BiomarkerDefinition::addTranslatedBiomarkerDefinitionToAutoGenerate($updated_biomarkers);
            }
        }
        if (count($updated_biomarker_regions) > 0) {
            if ($isServerSide === true) {
                foreach ($updated_biomarker_regions as $field_name => $list) {
                    foreach ($list as $source) {
                        VariableHelper::addVariablesTranslation($source, $field_name);
                    }
                }
            } else {
                BiomarkerRegion::addTranslatedBiomarkerRegionVariablesToAutoGenerate($updated_biomarker_regions);
            }
        }
        return ["added" => $added, "missing" => $missing];
    }

    public static function updatedInsights(&$updated_insights, $insight)
    {
        $insight_id = str_replace('insight_', '', $insight);
        $updated_insights[$insight_id] = $insight_id;
    }

    public static function updatedBiomarkers(&$updated_biomarkers, $source, $field_name)
    {
        if (!isset($updated_biomarkers[$field_name]))
            $updated_biomarkers[$field_name] = [];
        $updated_biomarkers[$field_name][] = $source;
    }

    public static function updatedBiomarkerRegions(&$updated_biomarker_regions, $source, $field_name)
    {
        if (!isset($updated_biomarker_regions[$field_name]))
            $updated_biomarker_regions[$field_name] = [];
        $updated_biomarker_regions[$field_name][] = $source;
    }

    public static function columnLanguage($column_letter)
    {
        $tmp = [
            "E" => "ar",
            "F" => "de",
            "G" => "fr",
            "H" => "zh-CN",
            "I" => "pt-PT",
            "J" => "pt-BR",
            "K" => "zh-HK",
            "L" => "nl-NL",
            "M" => "tr-TR",
            "N" => "it-IT",
            "O" => "es-LA",
            "P" => "es-LA_male",
            "Q" => "es-LA_female",
        ];
        return isset($tmp[$column_letter]) ? $tmp[$column_letter] : false;
    }

    public static function translateWord($language, $category, $word, $field_name, $translation, &$models, $languageTranslationSourceArray, &$added, &$missing)
    {
        if (!empty($language) && !empty($category) && !empty($word) && !empty($translation)) {
            if (isset($languageTranslationSourceArray[self::cleanIndex($word) . "_" . $category . "_" . $field_name])) {//it exist as a source
                $source_id = $languageTranslationSourceArray[self::cleanIndex($word) . "_" . $category . "_" . $field_name]["id"];

                if (isset($models[$source_id . "_" . $language]))  //it exist and has a model
                    $model = $models[$source_id . "_" . $language];
                else
                    $model = LanguageTranslate::find()->where(["id" => $source_id, "language" => $language/*, "translation" => $translation*/])->one();


                if (empty($model)) {
                    $model = new LanguageTranslate();
                    $model->id = $source_id;
                    $model->language = $language;
                    $model->created_date = time();
                } else {
                    $model->modified_date = time();
                }
                $model->translation = (string)$translation;

                if ($model->save()) {
                    $models[$source_id . "_" . $language] = $model;
                    $added[] = $translation;
                } else {
                    echo Html::tag("br");
                    echo Html::errorSummary($model);
                    Yii::$app->session->setFlash("error", Html::errorSummary($model) . Html::tag("br") . "translation_source_id:    " . $model->id . " translation:   " . $model->translation . " language:   " . $model->language);
                }
            } else {
                $missing[] = $word;
            }
        }

    }

    public static function removeAllFilesFromDirectory($dir)
    {
        //remove all existing files
        $files = new \DirectoryIterator($dir);
        foreach ($files as $file) {
            if (!$file->isDot() || !$file->isDir()) {
                unlink($file->getPathname());
            }
        }
    }

    public static function removeAllFilesAndDirectoryFromDirectory($dir)
    {
        if(is_dir($dir)) {
            //remove all existing files
            $files = new \DirectoryIterator($dir);
            foreach ($files as $file) {
                if (!$file->isDot() || !$file->isDir()) {
                    DirectoryHelper::deleteDirectory($file->getPathname());
                }
            }
        }else{
            FileHelper::createDirectory($dir);
        }
    }

    public static function getExcelFileNamesFromDirectory($dir)
    {
        $file_names = [];
        $files = new \DirectoryIterator($dir);
        foreach ($files as $file) {
            $file_name = $file->getFilename();
            if (!$file->isDot() && (strpos($file_name, ".xlsx") !== false)) {
                $file_names[] = $file_name;
            }
        }
        return $file_names;
    }

    public static function importZip($uploadedFile)
    {
        $errors = [];
        MedicusHelper::scan();
        $file = explode(".", $uploadedFile->name);
        if (strtolower($file[count($file) - 1]) != "zip") {
            $errors[] = Yii::t("app", "Only zip files are allowed");
            return $errors;
        } else {
            $path = "export/import/";
            $file_name = "File.zip";
            ExcelHelper::removeAllFilesFromDirectory('export/import/');
            if ($uploadedFile->saveAs($path . $file_name)) {
                $zip = new ZipArchive();
                if ($zip->open($path . $file_name) === TRUE) {
                    $zip->extractTo($path);
                    $zip->close();
                    unlink($path . $file_name);
                    $excel_files = ExcelHelper::getExcelFileNamesFromDirectory($path);
                    $err = ExcelHelper::validateExcel($path, $excel_files);
                    if (count($err) == 0) {
                        $languageTranslationSourceArray = ExcelHelper::languageTranslationSourceArray();
                        $models = ExcelHelper::languageTranslationModels();
                        $test_res = [];
                        foreach ($excel_files as $excel_file) {
                            $test_res[] = ExcelHelper::importTranslationFromExcel($path . $excel_file, $models, $languageTranslationSourceArray);
                        }
                    } else {
                        $errors[] = implode("<br>", $err);
                        return $errors;
                    }
                } else {
                    $errors[] = Yii::t("app", "Unable to extract archive");
                    return $errors;
                }
            } else {
                $errors[] = Yii::t("app", "Unable to save zip file");
                return $errors;
            }
        }
        return $errors;
    }

    public static function translationCategories()
    {
        return array_keys(LanguageSource::find()->select("category")->distinct()->indexBy("category")->orderBy("category")->all());
    }

    public static function exportAllTranslationContent($db_name, $validity, $sub_project, $file_name = "All", $path = "export", $category = null, $lab_id = null)
    {
        $currentLab = $lab_id? Lab::findOne($lab_id): Lab::findOne(1);
        if (MedicusHelper::changeDbConnection($db_name)) {
            $validity = $validity === null ? 2 : $validity;
            $sources = $category == null ? LanguageSource::find()
                ->where(['validity' => $validity])
                ->andWhere(['not', ['category' => 'doctor_cr']])
                ->orderBy(["category" => "category", "message" => "message"])
                ->indexBy("id")
                ->all() :
                LanguageSource::find()
                    ->where(['validity' => $validity])
                    ->andWhere(['category' => $category])
                    ->orderBy(["category" => "category", "message" => "message"])
                    ->indexBy("id")
                    ->all();

            $categories = ArrayHelper::map($sources, 'category', 'category');
            $translation = LanguageTranslate::find()->all();
            foreach ($translation as $item) {
                $translation_index[$item["id"] . "_" . $item["language"]] = $item["translation"];
            }
            $allItems = [];

            if (!empty($sub_project)) {
                $project_biomarker_ids = ArrayHelper::map(BiomarkerSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'biomarker_id', 'biomarker_id');
                $project_insight_ids = ArrayHelper::map(InsightSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'insight_id', 'insight_id');
                $project_question_ids = ArrayHelper::map(QuestionSubProject::find()->where(['sub_project_id' => $sub_project])->all(), 'question_id', 'question_id');
            }

            foreach ($categories as $category) {
                $items = [];
                foreach ($sources as $source) {
                    if ($source->category == $category) {
                        $i = [
                            "A" => $source->id,
                            "B" => $source->category,
                            "C" => $source->field_name,
                            "D" => $source->message,
                            "E" => isset($translation_index[$source->id . "_ar"]) ? $translation_index[$source->id . "_ar"] : "",
                            "F" => isset($translation_index[$source->id . "_de"]) ? $translation_index[$source->id . "_de"] : "",
                            "G" => isset($translation_index[$source->id . "_fr"]) ? $translation_index[$source->id . "_fr"] : "",
                            "H" => isset($translation_index[$source->id . "_zh-CN"]) ? $translation_index[$source->id . "_zh-CN"] : "",
                            "I" => isset($translation_index[$source->id . "_pt-PT"]) ? $translation_index[$source->id . "_pt-PT"] : "",
                            "J" => isset($translation_index[$source->id . "_pt-BR"]) ? $translation_index[$source->id . "_pt-BR"] : "",
                            "K" => isset($translation_index[$source->id . "_zh-HK"]) ? $translation_index[$source->id . "_zh-HK"] : "",

                            "L" => isset($translation_index[$source->id . "_nl-NL"]) ? $translation_index[$source->id . "_nl-NL"] : "",
                            "M" => isset($translation_index[$source->id . "_tr-TR"]) ? $translation_index[$source->id . "_tr-TR"] : "",
                            "N" => isset($translation_index[$source->id . "_it-IT"]) ? $translation_index[$source->id . "_it-IT"] : "",
                            "O" => isset($translation_index[$source->id . "_es-LA"]) ? $translation_index[$source->id . "_es-LA"] : "",

                            "P" => ($currentLab->is_informal && isset($translation_index[$source->id . "_es-LA_male"]))?$translation_index[$source->id . "_es-LA_male"] : "",
                            "Q" => ($currentLab->is_informal && isset($translation_index[$source->id . "_es-LA_female"]))?$translation_index[$source->id . "_es-LA_female"] : "",

                            "R" => '',
                            "S" => '',
                            "T" => '',
                            "U" => '',
                            "V" => '',
                            "X" => '',
                            "Y" => '',
                        ];
                        if ($source->category == 'insight') {
                            if (!empty($project_insight_ids)) {
                                $ins = Insight::find()->where([$source->field_name => $source->message])->andWhere(['in', 'id', $project_insight_ids])->with('type')->one();
                            } else {
                                $ins = Insight::find()->where([$source->field_name => $source->message])->with('type')->one();
                            }
                            if ($ins) {
                                $i["R"] = 'insight_' . $ins->universal_id;
                                $i["S"] = isset($ins->type) ? $ins->type->name : 'not found';
                                $i["T"] = isset($ins->group) ? $ins->group->name : "";
                            } else {
                                unset($i);
                                continue;
                            }
                        } elseif ($source->category == 'doctor_cr') {
                            if (!empty($project_insight_ids)) {
                                $ins = Insight::find()->where(['REGEXP', 'detailed_list', '(\s*\n|^)' . $source->message . '\n'])->andWhere(['in', 'id', $project_insight_ids])->with('type')->one();
                            } else {
                                $ins = Insight::find()->where(['REGEXP', 'detailed_list', '(\s*\n|^)' . $source->message . '\n'])->with('type')->one();
                            }
                            if ($ins) {
                                $i["R"] = 'insight_' . $ins->id;
                                $i["S"] = isset($ins->type) ? $ins->type->name : 'not found';
                                $i["T"] = isset($ins->group) ? $ins->group->name : "";
                            } else {
                                unset($i);
                                continue;
                            }
                        } elseif ($source->category == 'biomarker') {
                            if (!empty($project_biomarker_ids)) {
                                $bio = Biomarker::find()->where([
                                    'OR',
                                    ['what' => $source->message],
                                    ['why' => $source->message],
                                    ['how' => $source->message],
                                    ['homewell_how' => $source->message],
                                    ['pregnant_why' => $source->message]
                                ])->andWhere(['in', 'id', $project_biomarker_ids])->one();

                            } else {
                                $bio = Biomarker::find()->where([
                                    'OR',
                                    ['what' => $source->message],
                                    ['why' => $source->message],
                                    ['how' => $source->message],
                                    ['homewell_how' => $source->message],
                                    ['pregnant_why' => $source->message]
                                ])->one();
                            }

                            if (!$bio) {
                                unset($i);
                                continue;
                            }
                        } elseif ($source->category == 'question') {
                            if (!empty($project_question_ids)) {
                                $ques = Question::find()->where([
                                    'OR',
                                    ['text' => $source->message],
                                    ['attribute' => $source->message],
                                    ['sub_question' => $source->message],
                                    ['info' => $source->message]
                                ])->andWhere(['hidden' => 0])->andWhere(['in', 'id', $project_question_ids])->one();
                            } else {
                                $ques = Question::find()->where([
                                    'OR',
                                    ['text' => $source->message],
                                    ['attribute' => $source->message],
                                    ['sub_question' => $source->message],
                                    ['info' => $source->message]
                                ])->andWhere(['hidden' => 0])->one();
                            }

                            if (!$ques) {
                                unset($i);
                                continue;
                            }
                        } elseif ($source->category == 'answer') {
                            $answer = Answer::find()->where([
                                'OR',
                                ['value' => $source->message],
                                ['more_details' => $source->message],
                                ['definition' => $source->message],
                                ['occurrence' => $source->message],
                            ])->one();
                            if ($answer) {
                                $i["U"] = isset($answer->question) ? $answer->question->symbol : 'not found';
                            }
                        } elseif ($source->category == 'coaching_step') {
                            $step = CoachingStep::find()->where([
                                'OR',
                                ['description' => $source->message],
                                ['title' => $source->message],
                            ])->one();
                            if ($step) {
                                $i["V"] = isset($step->program) ? $step->program['name'] : '';
                            }
                        } elseif ($source->category == 'coaching_step_hint') {
                            $step_hint = CoachingStepHint::find()->with('coachingStep')->where(['text' => $source->message])->one();
                            if ($step_hint) {
                                $i["U"] = isset($step_hint->coachingStep) ?
                                    "Program: " . $step_hint->coachingStep->program['name'] . " ,Step: " . $step_hint->coachingStep->title : '';
                            }
                        } elseif ($source->category == 'coaching_step_image') {
                            $step_image = CoachingStepImage::find()->with('coachingStep')->where(['description' => $source->message])->one();
                            if ($step_image) {
                                $i["V"] = isset($step_image->coachingStep) ?
                                    "Program: " . $step_image->coachingStep->program['name'] . " ,Step: " . $step_image->coachingStep->title : '';
                            }
                        } elseif ($source->category == 'coaching_quote') {
                            $quote = CoachingQuote::find()->where(['text' => $source->message])->one();
                            if ($quote) {
                                $i["V"] = isset($quote->program) ? $quote->program['name'] : '';
                            }
                        } elseif ($source->category == 'biomarker_region') {

                            $biomarker_ids = ArrayHelper::map(BiomarkerRegion::find()->where([$source->field_name => $source->message])->all(), "id", "biomarker_id");
                            $biomarkers = Biomarker::find()->where(['id' => $biomarker_ids])->all();
                            $release_groups = ArrayHelper::map($biomarkers, 'id', 'release_group_id');
                            $release_groups = array_values(array_unique($release_groups));
                            $release_groups = implode("|", $release_groups);
                            $biomarker_symbols_arr = array_values(ArrayHelper::map($biomarkers, "id", "symbol"));
                            $biomarker_symbols = implode("|", $biomarker_symbols_arr);
                            $i["W"] = $biomarker_symbols;
                            $i["X"] = $release_groups;

                        }

                        $items[] = $i;
                        if (count($categories) > 1) {
                            $allItems [] = $i;
                        }
                    }

                }
                if (!empty($sub_project)) {
                    $name = SubProject::findOne(['id' => (int)$sub_project])->name;
                    $name = str_replace(' ', '_', trim($name));
                    $file_name = $name . "_All_" . $category;
                } else {
                    $file_name = "All_" . $category;
                }
                self::exportData($items, $file_name, $path, "All", $lab_id);
            }
            if (count($categories) > 1) {
                if (!empty($sub_project)) {
                    $name = SubProject::findOne(['id' => (int)$sub_project])->name;
                    $name = str_replace(' ', '_', trim($name));
                    $file_name = $name . "_All";
                } else {
                    $file_name = "All";
                }
                self::exportData($allItems, $file_name, $path, "All", $lab_id);
            }
        }
        return false;
    }

    public static function exportAnswers($path, $name, $data = [], $category = "Sheet_1")
    {
        if (empty($data)) {
            $data = Answer::find()->all();
        }
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "Question");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Parent ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Value");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Order");
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . 1, "Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . 1, "More Details");
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . 1, "Gender");
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . 1, "Hide from family condition");
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . 1, "Mapped Answer ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . 1, "Absolute Value");
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . 1, "Validity");
        $objPHPExcel->getActiveSheet()->SetCellValue('L' . 1, "Group Name");
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . 1, "Definition");
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . 1, "Occurrence");
        $objPHPExcel->getActiveSheet()->SetCellValue('O' . 1, "Start Week");
        $objPHPExcel->getActiveSheet()->SetCellValue('P' . 1, "End Week");
        $objPHPExcel->getActiveSheet()->SetCellValue('Q' . 1, "Icon");
        $row = 2;
        foreach ($data as $answer) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $answer->question->text);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, isset($answer->parent_id) ? Answer::parentList($answer->parent_id) : $answer->parent_id);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $answer->value);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $answer->order);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $row, $answer->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $row, $answer->more_details);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $row, (!empty($answer->gender) || $answer->gender == 0) ? MedicusHelper::genderList()[$answer->gender] : $answer->gender);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $row, isset($answer->family_condition) ? MedicusHelper::yesnoList()[$answer->family_condition] : $answer->family_condition);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $row, isset($answer->mapped_answer) ? Answer::parentList($answer->mapped_answer) : $answer->mapped_answer);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $row, $answer->absolute_value);
            $objPHPExcel->getActiveSheet()->SetCellValue('K' . $row, isset($answer->validity) ? MedicusHelper::validityValues()[$answer->validity] : $answer->validity);
            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $row, isset($answer->group) ? $answer->group->name : $answer->group_id);
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $row, $answer->definition);
            $objPHPExcel->getActiveSheet()->SetCellValue('N' . $row, $answer->occurrence);
            $objPHPExcel->getActiveSheet()->SetCellValue('O' . $row, $answer->startWeek);
            $objPHPExcel->getActiveSheet()->SetCellValue('P' . $row, $answer->endWeek);
            $objPHPExcel->getActiveSheet()->SetCellValue('Q' . $row, $answer->icon);

            $objPHPExcel->getActiveSheet()->getStyle('C' . $row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $row)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('Q' . $row)->getAlignment()->setWrapText(true);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportFilteredActionAnswers($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Action");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Answer");
        $row = 2;
        foreach ($data as $answer) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $answer->id);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $answer->action->symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $answer->value);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name . ".xlsx");
    }

    public static function exportLrsMap($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Medicus Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "Client Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "Medicus LR");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "Client LR");
        $data = isset($filteredData) ? $filteredData : LrsMap::find()->all();
        $count = 2;

        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->medicus_symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->client_symbol);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->medicus_lr);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->client_lr);

            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportCatalogLrsRules($path, $name, $filteredData = null, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue("A" . 1, "Original LRs");
        $objPHPExcel->getActiveSheet()->SetCellValue("B" . 1, "Original Colors");
        $objPHPExcel->getActiveSheet()->SetCellValue("C" . 1, "New LRs (Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("D" . 1, "New Colors (Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("E" . 1, "New Normal Range (Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("F" . 1, "New LRs (Min = Min-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("G" . 1, "New Colors (Min = Min-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("H" . 1, "New Normal Range (Min = Min-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("I" . 1, "New LRs (Max = Max-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("J" . 1, "New Colors (Max = Max-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("K" . 1, "New Normal Range (Max = Max-Normal)");
        $objPHPExcel->getActiveSheet()->SetCellValue("L" . 1, "New LRs (Min = Max)");
        $objPHPExcel->getActiveSheet()->SetCellValue("M" . 1, "New Colors (Min = Max)");
        $objPHPExcel->getActiveSheet()->SetCellValue("N" . 1, "New Normal Range (Min = Max)");
        $data = isset($filteredData) ? $filteredData : CatalogLrsRules::find()->all();
        $count = 2;

        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $count, $item->original_lrs);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $count, $item->original_colors);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $count, $item->type == 'normal' ? $item->new_lrs : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $count, $item->type == 'normal' ? $item->new_colors : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $count, $item->type == 'normal' ? $item->new_normal_range : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $count, $item->type == 'min_equal_min_normal' ? $item->new_lrs : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $count, $item->type == 'min_equal_min_normal' ? $item->new_colors : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $count, $item->type == 'min_equal_min_normal' ? $item->new_normal_range : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $count, $item->type == 'max_equal_max_normal' ? $item->new_lrs : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $count, $item->type == 'max_equal_max_normal' ? $item->new_colors : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $count, $item->type == 'max_equal_max_normal' ? $item->new_normal_range : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("L" . $count, $item->type == 'min_equal_max' ? $item->new_lrs : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("M" . $count, $item->type == 'min_equal_max' ? $item->new_colors : '');
            $objPHPExcel->getActiveSheet()->SetCellValue("N" . $count, $item->type == 'min_equal_max' ? $item->new_normal_range : '');

            $objPHPExcel->getActiveSheet()->getStyle('A' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('B' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('C' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('D' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('E' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('F' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('G' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('H' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('I' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('J' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('K' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('L' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('M' . $count)->getAlignment()->setWrapText(true);
            $objPHPExcel->getActiveSheet()->getStyle('N' . $count)->getAlignment()->setWrapText(true);

            $count++;

        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name);
    }

    public static function exportLoinc($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Loinc Version");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Code");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Biomarker Symbol");
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . 1, "Component");
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . 1, "Property");
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . 1, "Time");
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . 1, "System");
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . 1, "Scale");
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . 1, "Method");
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . 1, "Name");
        $row = 2;
        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $row, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $row, isset($item->version) ? $item->version->name : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $row, $item->code);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $row, isset($item->biomarker) ? $item->biomarker->symbol : "");
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $row, $item->component);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $row, $item->property);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $row, $item->time);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $row, $item->system);
            $objPHPExcel->getActiveSheet()->SetCellValue("I" . $row, $item->scale);
            $objPHPExcel->getActiveSheet()->SetCellValue("J" . $row, $item->method);
            $objPHPExcel->getActiveSheet()->SetCellValue("K" . $row, $item->name);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name );
    }

    public static function exportTextValuesListTemplate($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "Clips");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "En");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "De");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Fr");
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . 1, "PT-Pt");
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . 1, "Ar");
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . 1, "ZH-CN");
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . 1, "ZH-HK");
        $row = 2;
        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $row, $item['A']);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $row, $item['B']);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $row, $item['C']);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $row, $item['D']);
            $objPHPExcel->getActiveSheet()->SetCellValue("E" . $row, $item['E']);
            $objPHPExcel->getActiveSheet()->SetCellValue("F" . $row, $item['F']);
            $objPHPExcel->getActiveSheet()->SetCellValue("G" . $row, $item['G']);
            $objPHPExcel->getActiveSheet()->SetCellValue("H" . $row, $item['H']);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name );
    }

    public static function exportLoincComponentSynonym($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Loinc Version");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Loinc Component");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Synonym");
        $row = 2;
        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $item->loincVersion->name);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $item->loinc_component);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $item->synonym);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name );
    }

    public static function exportLoincSystemSynonym($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Loinc Version");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Loinc System");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Synonym");
        $row = 2;
        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $item->loincVersion->name);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $item->loinc_system);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $item->synonym);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name );
    }

    public static function exportLoincPropertySynonym($path, $name, $data)
    {
        $objPHPExcel = self::newExcel();
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . 1, "ID");
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . 1, "Loinc Version");
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . 1, "Loinc Property");
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . 1, "Synonym");
        $row = 2;
        foreach ($data as $item) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $item->id);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $item->loincVersion->name);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $item->loinc_property);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $item->synonym);
            $row += 1;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name );
    }
    
    public static function importGroupsFromExcel($file_name, $type)
    {
        $result = [];
        $db_name = isset(\Yii::$app->user) ? \Yii::$app->user->identity->getCurrentLabDbName() : MedicusHelper::$DB_name;
        MedicusHelper::changeDbConnection($db_name);

        if ($uploadedFile = UploadedFile::getInstanceByName($file_name)) {
            $file = explode(".", $uploadedFile->name);

            $success = $type . ' Group : "';

            if (strtolower($file[count($file) - 1]) != "zip") {
                $result['error_message'][] = "Only zip files are allowed";
                return $result;
            } else {
                $file_name = $type . "Group.zip";
                $path = "export/dataImporter/";
                ExcelHelper::removeAllFilesAndDirectoryFromDirectory($path);

                if ($uploadedFile->saveAs($path . $file_name)) {
                    $zip = new \ZipArchive();

                    if ($zip->open($path . $file_name) === TRUE) {
                        $zip->extractTo($path);
                        $zip->close();
                        unlink($path . $file_name);

                        $excel_files = ExcelHelper::getExcelFileNamesFromDirectory($path);

                        if (count($excel_files) == 0) {
                            $result['error_message'][] = "Zip file has no Excel files";
                        }

                        foreach ($excel_files as $file_name) {
                            $excel_file = ExcelHelper::openExcelFile($path . $file_name);
                            $excel_rows = $excel_file["rows"];
                            $first_key = $excel_rows[key($excel_rows)];

                            if (ExcelHelper::emptyExcel($excel_rows)) {
                                $result['error_message'][] = "Empty file '" . $file_name . "', Please open it and enable editing and save it.";
                            }

                            if ($first_key["A"] != "$type Group Name" || $first_key["B"] != "Description") {
                                $result['error_message'][] = "Different structure '" . $file_name . "',Header must be like $type Group Name, Description" . str_replace('array', ' not', var_export($first_key, true));
                            }
                        }
                        if (array_key_exists('error_message', $result) === false) {
                            foreach ($excel_files as $excel_file) {
                                $excel_file = ExcelHelper::openExcelFile($path . $excel_file);
                                $excel = $excel_file["rows"];

                                $added = [];

                                unset($excel[key($excel)]);//remove file header
                                $insert = [];

                                foreach ($excel as $rowKey => $row) {
                                    $name = trim((string)$row["A"]);
                                    $description = trim((string)$row["B"]);

                                    if (!empty($name)) {
                                        $insert [] = [
                                            'name' => $name,
                                            'description' => $description
                                        ];
                                        $added [] = $rowKey;
                                    }

                                }
                                if (count($insert) > 0) {
                                    $columnNameArray = array_keys($insert[0]);
                                    Yii::$app->dbMaster->createCommand()
                                        ->batchInsert(
                                            str_replace(' ', '_', strtolower($type)) . "_group", $columnNameArray, $insert
                                        )->execute();
                                }
                                $success .= count($added) > 0 ? implode(', ', $added) : '';
                            }
                        } else {
                            return $result;
                        }
                    }
                }
            }
            if ($success != $type . ' Group : "') {
                $result["success"] = $success . '" have been added';
            }
            return $result;
        } else {
            $result['error_message'][] = "Choose file before import";
            return $result;
        }

    }

    public static function exportArray($items, $name, $path, $category = "Sheet_1")
    {
        $objPHPExcel = self::newExcel();
        foreach ($items as $key => $inner_items) {
            foreach ($inner_items as $num => $item) {
                $num = $num + 1;
                $objPHPExcel->getActiveSheet()->SetCellValue($key . $num, $item);
            }
            $objPHPExcel->getActiveSheet()->getColumnDimension($key)->setWidth(20);
        }
        $objPHPExcel->getActiveSheet()->setTitle($category);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($path . $name . ".xlsx");
    }


}