<?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

R::ext('xdispense', function ($type) {
   return R::getRedBean()->dispense($type);
});
readDoctorsExcel();
//  readPatientsExcel();
//readRaysDiscountExcel();
//readDetectDiscountExcel(1, '../excel_backups/detect_2017.xlsx');
//readDetectDiscountExcel(2, '../excel_backups/detect_2021.xlsx');
// readDetectDiscountExcel(3, '../excel_backups/detect_others.xlsx');
// readDetectDiscountExcel(3, '../excel_backups/eyes.xlsx');
$i = 0;

#######################################################

function readDetectDiscountExcel($list_id, $inputFileName) {
   $x = 0;

   ##   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());
   }


   $sheet = $objPHPExcel->getSheet(0);
   $highestRow = $sheet->getHighestRow();

   ##   Loop through each row of the worksheet in turn
   for ($row = 2; $row <= $highestRow; $row++) {
      $x++;
      ##  Read a row of data into an array
      $rowData = $sheet->rangeToArray('A' . $row . ':' . 'C' . $row, NULL, TRUE, FALSE);
      $res = $rowData[0];
      if (!empty($res[0]) && $res[0] != "") {
         $rayId = checkExist2('detections', trim($res[0]), 0, '');
         if ($rayId > 0) {
            $org_discount = listDiscountObj(trim($res[1]), $list_id, $rayId, 1);
            print_r('<pre>');
            print_r($row . ' - ' . $org_discount->id);
            print_r('</pre>');
         }
      }
   }
}

function checkExist2($tableName, $name, $catId, $catName) {
   $obj = R::findOne($tableName, ' title = ? ', [$name]);
   if (count($obj) > 0) {
      $id = $obj->id;
   } else {
      $id = addToTable2($tableName, $name, $catId, $catName);
   }
   return $id;
}

function addToTable2($tableName, $name, $catId, $catName) {
   try {
      $obj = R::dispense($tableName);
      $obj->title = $name;
      if ($catId > 0) {
         $obj->$catName = $catId;
      }
      $id = R::store($obj);
      return $id;
   } catch (Exception $e) {
      print_r($e);
   }
}

#######################################################

function readRaysDiscountExcel() {
   $list_id = 4;
   $inputFileName = '../excel_backups/rays_others.xlsx';
   $x = 0;

   ##   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());
   }


   $sheet = $objPHPExcel->getSheet(0);
   $highestRow = $sheet->getHighestRow();

   ##   Loop through each row of the worksheet in turn
   for ($row = 2; $row <= $highestRow; $row++) {
      $x++;
      ##  Read a row of data into an array
      $rowData = $sheet->rangeToArray('A' . $row . ':' . 'C' . $row, NULL, TRUE, FALSE);
      $res = $rowData[0];
      if (!empty($res[0]) && $res[0] != "") {
         $rayId = checkExist('rays', trim($res[0]), 1, 'rayscatid');
         if ($rayId > 0) {
            $org_discount = listDiscountObj(trim($res[2]), $list_id, $rayId, 3);
            print_r('<pre>');
            print_r($row . ' - ' . $org_discount->id);
            print_r('</pre>');
         }
      }
   }
}

function checkExist($tableName, $name, $catId, $catName) {
   $obj = R::findOne($tableName, ' name = ? ', [$name]);
   if (count($obj) > 0) {
      $id = $obj->id;
   } else {
      $id = addToTable($tableName, $name, $catId, $catName);
   }
   return $id;
}

function addToTable($tableName, $name, $catId, $catName) {
   try {
      $obj = R::dispense($tableName);
      $obj->name = $name;
      if ($catId > 0) {
         $obj->$catName = $catId;
      }
      $id = R::store($obj);
      return $id;
   } catch (Exception $e) {
      print_r($e);
   }
}

function listDiscountObj($price, $list_id, $detect_id, $detect_cat, $offerId = 0) {
   $org_discount = R::findOne('organization_discount', ' list_id = ? and detect_id = ?
            and detect_cat = ? and offer_id = ?
                and deleted = ?  ', [$list_id, $detect_id, $detect_cat, $offerId, 0]);
   if (!($org_discount)) {
      $org = r::xdispense('organization_discount');
      $org->list_id = $list_id;
      $org->detect_id = $detect_id;
      $org->detect_cat = $detect_cat;
      $org->offer_id = $offerId;
      $org->contract_price = $price;
      $org->patient_hold = 0;
      r::store($org);
      $org_discount = R::findOne('organization_discount', ' list_id = ? and detect_id = ?    and detect_cat = ?
                 and offer_id = ?   and deleted = ?  ', [$list_id, $detect_id, $detect_cat, $offerId, 0]);
   } else {
      $org_discount->contract_price = $price;
      $org_discount->patient_hold = 0;
      r::store($org_discount);
   }


   $org_discount = R::findOne('organization_discount', ' list_id = ? and detect_id = ?    and detect_cat = ?
                 and offer_id = ?   and deleted = ?  ', [$list_id, $detect_id, $detect_cat, $offerId, 0]);

   return $org_discount;
}

#######################################################

function readPatientsExcel() {

   $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;
}

#######################################################

function readDoctorsExcel() {

   $inputFileName = '../excel_backups/doctors.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
   try {
      for ($row = 2; $row <= 327; $row++) {
         //  Read a row of data into an array
         $rowData = $sheet->rangeToArray('A' . $row . ':' . 'A' . $row, NULL, TRUE, FALSE);
         $res = $rowData[0];
         //  Insert row data array into your database of choice here
         if (!empty($res[0]) && $res[0] != "") {
            addToDrTable($res[0]);
         }
      }
   } catch (Exception $ex) {
      print_r($e);
   }
}

function readInvestsExcel() {

   $inputFileName = '../excel_backups/invests.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
   try {
      for ($row = 2; $row <= 52; $row++) {
         //  Read a row of data into an array
         $rowData = $sheet->rangeToArray('A' . $row . ':' . 'C' . $row, NULL, TRUE, FALSE);
         $res = $rowData[0];
         //  Insert row data array into your database of choice here
         if (!empty($res[1]) && $res[1] != "") {
            $invests = R::dispense('invests');
            $invests->investcatid = 1;
            $invests->name = $res[1];
            $invests->price = $res[2];
            r::store($invests);
         }
      }
   } catch (Exception $ex) {
      print_r($e);
   }
}
