<?php

//require_once("imp/_imp.php");
require_once("../public/aw_config.php");
//include PHPExcel library
require_once "Classes/PHPExcel/IOFactory.php";
set_time_limit(0);    ## unlimited excution time as this script take a lot of time 
readEx();
$i = 0;

function readEx() {

    $inputFileName = '../excel_backups/newcases.xlsx';

//  Read your Excel workbook
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } catch (Exception $e) {
        die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
    }

//  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0);
    $highestRow = $sheet->getHighestRow();
    $highestColumn = $sheet->getHighestColumn();



//  Loop through each row of the worksheet in turn
    for ($row = 2; $row <= 8789; $row++) {
        //  Read a row of data into an array
        $rowData = $sheet->rangeToArray('A' . $row . ':' . 'I' . $row, NULL, TRUE, FALSE);
        $res = $rowData[0];

        //  Insert row data array into your database of choice here
        //  
        //  
        if (!empty($res[1]) && $res[1] != "" && $res[0] > 0) {

            $patient = R::dispense('patients');

            $patient->userid = 0;
            $patient->done = 1;


            ##   col = statusno
            if ($res[0] > 0) {
                $patient->statusno = $res[0];
            } else {
                // print_r('serial missed </br>');
            }

            ##   col = wifename
            if (!empty($res[1]) && $res[1] != "") {

                $xxxxx = explode("-", $res[1]);
                $patient->wifename = $xxxxx[0];
            } else {
                $patient->wifename = "";
                // print_r('w name missed </br>');
            }

            ##   col = husdandname
            if (!empty($res[2]) && $res[2] != "") {
                $patient->husdandname = $res[2];
            } else {
                $patient->husdandname = "";
                //    print_r('h name  missed </br>');
            }

            ##   col = phone
            if (!empty($res[3]) && $res[3] != "") {
                $patient->phone = $res[3];
            } else {
                $patient->phone = "";
                //print_r('phone missed </br>');
            }

            ##   col = mobile
            if (!empty($res[4]) && $res[4] != "") {
                $patient->mobile = $res[4];
            } else {
                $patient->mobile = "";
                // print_r('mobile missed </br>');
            }

//            ##   col = 
//            if (!empty($res[5]) && $res[5] != "") {
//                $date = date('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($res[5]));
//            } else {
//                print_r('date missed </br>');
//            }

            if (!empty($res[6]) && $res[6] != "") {
                $drs = explode("-", $res[6]);
                $dr1 = trim($drs[0]);
                $dr2 = trim($drs[1]);

                ##   col = doctor1
                if (!empty($dr1) && $dr1 != "") {
                    $dr1_id = checkDrTable(trim($dr1));
                    $patient->doctor1 = $dr1_id;
                } else {
                    //  print_r('dr1 name  missed </br>');
                }

                ##   col = doctor2
                if (!empty($dr2) && $dr2 != "") {
                    $dr2_id = checkDrTable(trim($dr2));
                    $patient->doctor2 = $dr2_id;
                } else {
                    //print_r('dr2 name  missed </br>');
                }
            } else {
                // print_r('drs name  missed </br>');
            }


            ##   col = rfc
            if (!empty($res[7]) && $res[7] != "") {
                if (trim($res[7]) == "RFC") {
                    $patient->rfc = 1;
                } else {
                    $patient->rfc = 2;
                }
            } else {
                $patient->rfc = 2;
                //print_r('rfc missed </br>');
            }

            ##   col = nationality
            if (!empty($res[8]) && $res[8] != "") {
                $nat_id = checkNatTable(trim($res[8]));
                $patient->nationality = $nat_id;
            } else {
                // print_r('nationality missed </br>');
            }
        }

        try {
            r::store($patient);
        } catch (Exception $e) {
            print_r($e);
        }

        echo $patient->statusno . ' - ';
    }
}

function checkDrTable($drName) {
    $user = R::getRow('select user_id from awusers where name = ?', [$drName]);
    if (count($user) > 0) {
        $id = $user['user_id'];
    } else {
        $id = addToDrTable($drName);
    }
    return $id;
}

function addToDrTable($drName) {
    global $i;
    $i++;

    //   $email = '';
    $role = '4';
    // $special = '';
    // $position = '';
    $username = 'user' . $i;
    $password = '123';


    $user_name = trim($username);
    $hash_cost_factor = (defined('HASH_COST_FACTOR') ? HASH_COST_FACTOR : null);
    $user_password_hash = password_hash($password, PASSWORD_DEFAULT, array('cost' => $hash_cost_factor));
    $user_activation_hash = sha1(uniqid(mt_rand(), true));
    $user_email = trim($email);
    $user_registration_ip = $_SERVER['REMOTE_ADDR'];


    try {
        R::exec('INSERT INTO awusers (user_name, user_password_hash,user_active, user_activation_hash, user_registration_ip, user_registration_datetime,role_id,name  ) VALUES(?, ?, ?,?, ?, now(),?,?)', [$user_name, $user_password_hash, 1, $user_activation_hash, $user_registration_ip, $role, $drName]);
        $id = R::getInsertID();
        return $id;
    } catch (Exception $e) {
        print_r($e);
    }
}

function checkNatTable($natName) {
    $nationality = R::findOne('nationality', ' name = ?', [$natName]);

    if (count($nationality) > 0) {
        $id = $nationality->id;
    } else {
        try {
            $nationality = R::dispense('nationality');
            $nationality->name = $natName;
            $id = r::store($nationality);
        } catch (Exception $e) {
            print_r($e);
        }
    }

    return $id;
}
