<?php

require_once("imp/_imp.php");

class Controllers {

//public vars
   public $smarty;
   public $hosturl;
   public $controllname;
   public $shcontrollname;
   public $active;
   public $patientid;

// it will run in everytime with
// default requried
   public function __construct() {
      require_once("../public/aw_config.php");
      require_once("imp/_autho.php"); //general functions
      $this->smarty = $smarty;
      $this->hosturl = $hosturl;

//here name of controll to known
      $this->controllname = 'setup';
      $this->shcontrollname = 'Setup';
      $this->smarty->assign('controllname', $this->controllname);
      $this->smarty->assign('shcontrollname', $this->shcontrollname);

//breadcrumbs
      require_once("../../_library/plugins/Breadcrumbs/Breadcrumbs.php");
      $acty = filter_input(INPUT_GET, 'ac');
      $bc = new breadcrumbs($hosturl, '', '', $acty);
      $bc->crumbs();
      $breadcrumbs = $bc->breadcrumbs;
      $smarty->assign('breadcrumbs', $breadcrumbs);

# here show the active page in menu
// array called active[] with level of active element
      $active[0] = 'setup'; //controller name or tab name
      $active[1] = 'setup'; //controller name
// do name  // switch($do)
      switch ($acty) {
         case '':$active[2] = 'index';
            break;
         case 'show':$active[2] = 'index';
            break;
         default :$active[2] = 'index';
            break;
      }
      $this->active = $active;
//here will call all php pages
      require_once("_header.php");
      require_once("_sidebar.php");
      require_once("_leftsidebar.php");
      require_once("_footer.php");
   }

//here the frist page and
//the frist action in this class
   public function index() {
//check login
      autho::checkautho($this->hosturl);
//check authoize
      autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

// $this->settingdatabse();
//Html files
      header::headershow($this->smarty, $this->hosturl);
      sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
      $data = 0;
      $this->smarty->assign('checked', $data);
      $this->smarty->display('databasesetting.html');
      leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
      footer::footershow($this->smarty, $this->hosturl);
   }

   public function settingdb() {

//check login
      autho::checkautho($this->hosturl);
//check authoize
      autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);

      $setupPassword = '123456';
      $_SESSION['pass'] = filter_input(INPUT_POST, 'pass');
//Html files

      if ($setupPassword == $_SESSION['pass']) {

         header::headershow($this->smarty, $this->hosturl);
         sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);

         $programesetting = R::load('programesetting', 1);
         //if there is save load it
         if (isset($programesetting->erpdbsave) && $programesetting->erpdbsave > 0) {
            R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
            R::selectDatabase('erpDB');
            if (R::testConnection()) {
               R::exec('SET NAMES latin1');
               $saveData = R::getRow('select savename from save where saveid = ' . $programesetting->erpdbsave);
               $visaAccountData = R::getRow('select accountname,bankname
                        from bankaccount
                        join bank on bank.bankid = bankaccount.bankid
                        where accountid = ' . $programesetting->erpdbvisaaccount);
               R::selectDatabase('default');
               $programesetting->erpdbsavename = $saveData["savename"];
               $programesetting->erpdbvisaaccountname = $visaAccountData["accountname"] . " / " . $visaAccountData["bankname"];
            } else {
               //throw new Exception('Unable to connect to erpDB,make sure database name is ok and connected or clear its name in setup');
               $programesetting->erpdbsave = 0;
               $programesetting->erpdbsavename = '';
               $programesetting->erpdbvisaaccountname = '';
            }
         }
         $this->smarty->assign('programesetting', $programesetting);

         $this->smarty->display('setupsetting.html');
         leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
         footer::footershow($this->smarty, $this->hosturl);
      } else {
         header::headershow($this->smarty, $this->hosturl);
         sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
         $data = 1;

         $this->smarty->assign('checked', $data);
         $this->smarty->display('databasesetting.html');
         leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
         footer::footershow($this->smarty, $this->hosturl);
      }
   }

   //
   public function saveErpDB() {
      $db_name = DB_NAME;

      //$data = R::getcol("SHOW tables FROM " . $db_name . " WHERE Tables_in_" . $db_name . " NOT IN ('awcontroll','awcontrollprop','awmenu' ,'awrole','awrolecontrollprop','awrolemenu','awusers' , 'education','husbandjobs','husbandtypes','programesetting','wifejobs','wifestatus','wifetypes')");
      $erpDB = filter_input(INPUT_POST, 'erpDB');
//        if (!empty($erpDB)) {
      R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $erpDB . '', DB_USER, DB_PASS);
      R::selectDatabase('default');
      //
      $programesetting = R::load('programesetting', 1);
      $programesetting->erpdb = $erpDB;
      $_SESSION["erpDB"] = $erpDB;
      if (empty($erpDB)) {//saveErpDB
         $programesetting->erpdbsave = 0;
      }
      echo R::store($programesetting);
//        }
   }

   //
   public function getErpSave() {
      $row_array = array();
      $return_arr = array();

      $programesetting = R::load('programesetting', 1);
      if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
         R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
         R::selectDatabase('erpDB');
         if (R::testConnection()) {
            $name = $_GET['term']; //It could be product name or category name
            //var_dump(R::getDatabaseAdapter()->getDatabase()->getMysqlEncoding());
            R::exec('SET NAMES latin1');
            $allSave = R::getAll('select saveid,savename from save where conditions = 0 and savename LIKE "%' . $name . '%"');
            R::selectDatabase('default');

            if (count($allSave) > 0) {
               foreach ($allSave as $save) {
                  $row_array['id'] = $save["saveid"];
                  $row_array['text'] = $save["savename"];
                  array_push($return_arr, $row_array);
               }
            }
         } else {
            $row_array['id'] = "";
            $row_array['text'] = "Loading failed ... check Erp Database Connection";
            array_push($return_arr, $row_array);
         }
      } else {
         $row_array['id'] = "";
         $row_array['text'] = "Loading failed ... Erp Database Name Is Empty";
         array_push($return_arr, $row_array);
      }

      echo json_encode($return_arr);
   }

   public function erpDBSave() {
      $erpDBSave = filter_input(INPUT_POST, 'erpDBSave');
      if (!empty($erpDBSave)) {
         $programesetting = R::load('programesetting', 1);
         if (isset($programesetting->erpdb) && !empty($programesetting->erpdb)) {
            $programesetting->erpdbsave = $erpDBSave;
            echo R::store($programesetting);
         }
      }
   }

   //
   public function geterpVisaAccounts() {
      $row_array = array();
      $return_arr = array();

      $programesetting = R::load('programesetting', 1);
      if (!empty($programesetting->erpdb)) {//if exist addDatabase erpDB
         R::addDatabase('erpDB', 'mysql:host=' . DB_HOST . ';dbname=' . $programesetting->erpdb . '', DB_USER, DB_PASS);
         R::selectDatabase('erpDB');
         if (R::testConnection()) {
            $name = $_GET['term']; //It could be product name or category name
            R::exec('SET NAMES latin1');
            $allVisaAccounts = R::getAll('select accountid,accountname,bankname
                        from bankaccount
                        join bank on bank.bankid = bankaccount.bankid
                        where bankaccount.conditions = 0 and visa = 1 and (bankname LIKE "%' . $name . '%"  or accountname LIKE "%' . $name . '%")');
            R::selectDatabase('default');

            if (count($allVisaAccounts) > 0) {
               foreach ($allVisaAccounts as $vAccount) {
                  $row_array['id'] = $vAccount["accountid"];
                  $row_array['text'] = $vAccount["accountname"] . " / " . $vAccount["bankname"];
                  array_push($return_arr, $row_array);
               }
            }
         } else {
            $row_array['id'] = "";
            $row_array['text'] = "Loading failed ... check Erp Database Connection";
            array_push($return_arr, $row_array);
         }
      } else {
         $row_array['id'] = "";
         $row_array['text'] = "Loading failed ... Erp Database Name Is Empty";
         array_push($return_arr, $row_array);
      }

      echo json_encode($return_arr);
   }

   public function saveERPDBVisaAccount() {
      $erpDBVisaAccount = filter_input(INPUT_POST, 'erpDBVisaAccount');
      if (!empty($erpDBVisaAccount)) {
         $programesetting = R::load('programesetting', 1);
         if (isset($programesetting->erpdb) && !empty($programesetting->erpdb)) {
            $programesetting->erpdbvisaaccount = $erpDBVisaAccount;
            echo R::store($programesetting);
         }
      }
   }

   public function settingdatabase() {
      $tables = $this->tablesToClean();

      $db_name = DB_NAME;
      //    ini_set("display_errors", "on");

      $data = R::getcol("SHOW tables FROM " . $db_name . " WHERE Tables_in_" . $db_name . "  IN ($tables)");
      foreach ($data as $row) {
         echo R::exec("TRUNCATE TABLE $row");
      }

      $programesetting = R::findOne('programesetting');
      $programesetting->last_serial = 0;
      $programesetting->last_refund_serial = 0;
      r::store($programesetting);

      //    r::debug(true, 2);
      $pw = '$2y$10$G.ZYzcWYKP8xNJ82cMXMbeC.W.7gV72cMO7KdMre/NdBqASsID8DO';

      $sql = "INSERT INTO `awusers` (`user_id`, `user_name`, `user_password_hash`, `user_email`
                                                                , `user_active`, `user_activation_hash`, `user_password_reset_hash`
                                                                , `user_password_reset_timestamp`, `user_rememberme_token`
                                                                , `user_failed_logins`, `user_last_failed_login`, `user_registration_datetime`
                                                                , `user_registration_ip`, `role_id`, `name`, `specialid`, `positionid`, `attend`
                                                                , `hour_price`, `normal_per`, `pulse_per`, `session_per`) VALUES
                   (1, 'manager', '$pw' 
                , 'ahmed.freedeveloper@gmail.com', 1, '1ebf1664a5d6077504fcb60caccefc2d88b964e4'
                , '36e688b8ef85a659b3ec39646df92a809f2d6a28', 1422433469, NULL, 0, NULL
                , '2015-01-25 12:41:59', '127.0.0.1', 1, 'Manager', 0, 0, NULL, 0, 10, 10, 10)";
      r::exec($sql);
   }

   function tablesToClean() {
      $tablesAllowedToDelete .= '
            "andcontraception","antenalvisit","andvisitsus" ,"andvisitssemen","andvisitsrays"  ,"andvisitsficils" ,"andvisitsdrugs","andvisits"  ,"andvisitsinvestigation"
           ,"andmedicalproblems","andhistory","andexamination" ,"anderection","andejaculation"  ,"gtdetail","follow_pre"   ,"embryofreezingreport"
           ,"excelinfopatients","examination","endvisitreports" ,"embryotransferovum","embryotransfer" ,"recorddrugs" ,"embryo" ,"drugmovements"
           ,"embryothawing","embryoslab","embryoselection" ,"embryoscoring","embryologyreport" ,"operationids"  ,"recepittmp" ,"thawing" ,"embryofreezing"
           ,"doctors_reserves","device_tracking","devices" ,"councilstaff","clinic_rooms","followup_operation" ,"gynadrugs" ,"gyna","gtimage" ,"gynainvestigation"
           ,"clinic_reserves","bsessionmembers","bsession" ,"brequests","bmemberopenion" ,"mainantenentalus" ,"raysresults_img","raysresults"   ,"operations_main" 
           ,"followupvisit","followupinvest","followupexam" ,"followupdrugs","followupdiagnosis" ,"mainantenentalinvest","mainantenentaldrugs" ,"doc_instruction"
           ,"followupcarddrugs","followupcard","followup" ,"operativedetails","operation_residence"  ,"residence_reserves" ,"mainantenentalrays"  ,"gynarays"
           ,"aparoscopy","invofferdetails","invoffer","investigationresults_urine"  ,"investigationresults_times","investigationresults_stool"  ,"mainantenental"
           ,"investigationresults_culture_ls" ,"investigationresults_culture_hs","investigationresults_culture" , "awusers" ,"investigationresults_cross_donners" ,"sonar"
           ,"investigationresults_aborh","investigationresults" ,"instruction" ,"infertilitynotes" ,"storedrugs"  ,"visits_updates" ,"ultrasounddetail" ,"follow_instruction" 
           ,"infertilitylmp","infertilityinvest","infertilitygyna" ,"infertilitydrugs","infertilitydiagnosis" ,"ultrasoundgyna", "gynainfertility" , "gynainfertilityplan", "hystroscopic"
           ,"infertility","importtdetails","importdrugs" ,"importbill","hysteroscopy"  ,"investigationresults_esr", "operation_form", "operations_rooms"  ,"follow_post" 
          ,"phsexual","phpersonal","phpastsurgical" ,"phpastmedical","phpasticsi"   ,"tankcells","storedrugsvalidation" , "operations_rooms_cal" ,"receiptdrugs" ,"mrict"
            ,"phpasthx","phpastgynecological","phpastart" ,"phobstetric","phmenstrual"   ,"ultrasoundobst"  ,"bill_paying" ,"investigationresults_cross","operation_data"
           ,"phfamily","phcontraception","patients_updates" ,"patients_tmp","patients_complaints" ,"visits" ,"ustv" ,"investigationresults_blood","ultrasound" ,"semen"
           ,"patients_childs","patients","patientfiles" ,"patientdecleration","pathology" ,"ultrasoundobstdetail" , "bill_paying" ,"investigationresults_culture_ms" ,"records"
           ,"ovumpickup","otherinvestigationsvalues","otherinvestigationsrows" ,"operativedetailsdrugs"  ,"visit_hours", "icsi_fresh" , "icsi_frozen"  , "intra_anaesthetic"
             , "votes", "vote_answers" , "vote_devices" , "archive_tracking" , "login_tacking" , "other_hormon" , "patient_hystroscopic"  ,"investigationresults_pt"  
             , "residence_rooms" , "semen_cryopreservation"  , "semen_analysis"  , "patients_notify" , "organization_discount" , "organizations" , "oscopic_report"   
             , "manual_operation"   , "ivf_records" , "ivf_drugs" , "ivf_records_drugs"  , "ivf_report" , "sms_control_setting"   ,"investigationresults_culture_rs" ,"hormon" 
             , "advance_payment" , "archive_request"   , "beauty_visits"   , "branches"     , "clients_votes"  , "patient_laparoscopic", "prepared_prescriptions" 
             , "clients_votes_answers"  , "colonoscopy" , "colonoscopy_images"    , "colonoscopy_template"  , "obstetric_case"   , "obstetric_case_ex"  ,"bcomments"
              , "decleration" , "detections", "dna_results", "embryoslab_tank_cells", "exit_summary" , "external_labs","investigationresults_custom" ,"gynausficils"
             , "knownfrom" , "laparoscopic" , "laparoscopy" , "maingyna" , "manual_operation_updates", "suppliescats", "tankcellhistory" , "tanks" ,"gynaus" ,"hsg"
            , "prescription_details" , "pre_anaesthetic", "previous_pregnancies" , "previous_puerperium"  , "summary_obstetric_abortion", "supplies" ,"lastvisit"
            , "refer" , "semen_processing" , "short_urls", "sperm_extract"   , "stores", "summary_obstetric" , "prepared_prescriptions_drugs" , "prescription" 
            , "visit_services" , "visits_follows" , "vital_sign" , "vote_questions" , "semen_aggregation" , "entry_data", "educate_health"  ,"andvisitsgenetictesting"
            , "investigationresults_semen", "medication_med", "medication_sheet", "falling_sheet", "adult_assessment", "ped_med" , "lab_devices_ranges"
            , "ped_assessment", "chloral_med", "chloral_sheet" , "floors"  , "investigationresults_egfr" , "investigationresults_lipid" , "labdevices"  , "lab_devices" 
              , "organization_discount" , "residence_room", "gynachronic_cashing_drugs" , "gynachronic_pres_cashing"  , "gynachronic_pres"  ,"invests_supplies"
                , "gynachronicdrugs"  , "external_transfer_updates"  , "external_transfer"  , "instruction_med", "visit_periods" ,"spermfreezingreport" , "adult_med" 
                ,"doctors_fees" , "entry_data2" , "gynachronicdrugs_updates" , "gynadrugs_updates" , "mainantenentaldrugs_updates" , "organizations_patient_no" 
                , "organization_discount"  , "refer_doctors_fees" , "refer_updates" , "saveresultslog" , "visits_limit" , "visits_vacation"   , "detections_updates"   ';

      return $tablesAllowedToDelete;
   }

   function tablesNotToClean() {
      $tables = '  
            "abdominal"  , "anasthesa" , "andcomplaint" , "andcontratype" , "anddiagnosis" 
            , "anesthesia_types" , "ant_liquor" , "ant_presentation" , "antibiotic_sensitivity" , "antidotes" , "appearance" 
            , "asp_select" , "avf" , "awcontroll" , "awcontrollprop" , "awmenu" , "awrole" , "awrolebtn" 
            , "awrolecontrollprop" , "awrolemenu" , "awroleposition" , "awrolespecialize" , "awusermenu" , "back" 
             , "blood_transfusion" , "bloodtypes"  , "breast" , "cancel_reason" , "cathetr" 
            , "chest" 
            , "colors" , "complaint" , "complaint_analysis" , "complaintant" , "complications" 
            , "contour" , "cross_abo" , "cross_compatibility" , "cross_hbsag" , "cross_hcv" , "cross_hv" , "cross_rh" 
            , "cs" , "culture_colony_count" , "culture_condition" , "culture_condition1" , "culture_organism_a" 
            , "culture_organism_b" , "culture_organism_c" , "culture_sample" , "culture_sensitivity_cat" , "custom_select" 
            , "day_times" , "deep_palpation"  , "device" , "diagnosis" , "diagnosisant" , "difficults" 
            , "discharge" , "divarication"  , "drug_sensitivity" , "drug_therapy" , "drugs" , "easydifficult" 
            , "education" , "ejaculationdisease" , "embryo_catheter_select" , "embryo_device" , "embryo_emb" 
            , "embryo_kit_type" , "embryo_lot_select" , "embryo_result" , "embryo_tank" , "embryoblood" 
            , "embryocathetertype" , "embryodifficulty" , "embryojetplace" , "embryolah" , "embryologist" 
             , "embryotype" , "endotracheal_tube" , "erectiondisease" , "esr_hour1" , "esr_hour2"
            , "examinationabdomen" , "examinationchest" , "examinationextremitis" , "examinationhead" 
            , "examinationlocalse" , "examinationpelvis" , "excelinfo"  , "fastoptions" 
            , "fet_no" , "fetal_gender" , "fetal_pulse" , "floors" , "follow_drain" , "follow_drugs" , "follow_rout" , "follow_semen" 
            , "follow_uop" , "follow_vpack" , "followup_app" , "freezingmedia" , "freezingprotocol" , "ftnds" , "fundal_grip" 
            , "fundal_level" , "gaint" , "generalanasthesa" , "governorate" , "governorate_centers" , "grade" , "growthco2" 
            , "growthincubator" , "growthmedia" , "growthoil"  , "gynainfertilitytype" 
            , "hair" , "head" , "help" , "hnationality" , "hospitalnames" , "hreligion" , "husbandjobs" , "husbandtypes" 
            , "husstatus"  , "icsi_cather"  , "icsi_number" , "icsi_protocol" 
            , "icsi_trigday" , "icsi_trigname" , "index_redirect" , "infertileduration" , "infusions" , "injection" , "injection_location" 
            , "introduction" , "inv_ranges" , "invest_elements" , "investcats" , "invests" , "jncision" , "kit" 
            , "limbs" , "liquor" , "locations" , "m_relaxants"
            , "maintenance"  , "medicaldisease" , "mention" , "menu" , "midlinejncision" 
            , "mtb_select" , "nationality" , "neck" , "no_fetal" , "no_soc" , "no_title" , "obs_no"
            , "oocytecytoplasm" , "oocyteother" , "oocytepvs" , "oocytequality" , "oocytezona" 
            , "operation"  , "operation_room" , "operation_type" , "operationinstructions" 
            , "operationotherth",
            , "oscopic_operations"  , "oscopic_required_examinations" , "oscopic_specimens" 
            , "otherinvestigations"  , "pelvicf" , "pelvics" 
            , "phamount" , "phcontracttype" , "phdrugs" , "phdysmenorrhoea" , "phfamilydisease" , "phfamilyrelative" 
            , "phobstericterminate" , "phobstericterplace" , "phpastartcenter" , "phpastartresult" , "phpastarttype" 
            , "phpastgynecologicaloperation" , "phpasthxtypes" , "phpastmedicaldisease" , "phpastsurgicaloperation" 
            , "phregularity" , "phsexualtypes" , "placenta_abnormalites" , "placenta_site" , "post_diagnoses" , "posture" 
            , "pre_history"  , "presentation" , "preterm" 
            , "programesetting" , "programesetting2" , "programesettingex" , "pt_concentration" , "rays" , "rayscats" 
            , "recommend" , "regions" , "religion" , "residence_room" , "sampletype" , "semen_process" 
             , "skin" , "sms_control" , "sperum_source" , "stage"
            , "still_birth" , "stool_blood" , "stool_ciliate" , "stool_color" , "stool_consistency" , "stool_cysts" , "stool_cysts2" 
            , "stool_flagellates" , "stool_flagellates2" , "stool_food" , "stool_larva" , "stool_larva2" , "stool_mucus" 
            , "stool_odor" , "stool_ova" , "stool_ova2" , "stool_parasitology" , "stool_parasitology2" , "stool_prog" 
            , "stool_prog2" , "stool_rbc" , "stool_rbc2" , "stool_reaction" , "stool_trophozoite" , "stool_trophozoite2" 
            , "stool_trophozoite3" , "stool_undigested" , "stool_undigested2" , "stool_undigested3" , "stool_undigested4" 
            , "stool_wbc" , "stool_wbc2" , "sub_regions" , "subcostal"  , "superficial"  , "symptoms" 
            , "table2" , "table3" , "tablename"  , "tanks_general" , "tb_select" 
            , "testtbl1" , "testtbl2" , "trans_cather" , "trans_to" , "treatprotocol" , "umbilical_grip" , "umbilicus" 
            , "urine_acetone" , "urine_amorphous" , "urine_amorphous2" , "urine_artifacts" , "urine_aspect" 
            , "urine_bacteria" , "urine_bacteria2" , "urine_bilirubin" , "urine_blood" , "urine_casts" , "urine_casts2" 
            , "urine_color" , "urine_crystal" , "urine_crystal2" , "urine_deposit" , "urine_epithelial" , "urine_fungi" 
            , "urine_gram" , "urine_gravity" , "urine_leucocytes" , "urine_mucus" , "urine_nitrite" , "urine_odor" 
            , "urine_other" , "urine_other1" , "urine_other2" , "urine_other3" , "urine_other4" , "urine_ova" 
            , "urine_pbcs" , "urine_pbcs2" , "urine_protein" , "urine_pus" , "urine_pus2" , "urine_reaction" 
            , "urine_sugar" , "urine_trophazite" , "urine_urobilinigen" , "urine_volume" , "urine_zn_film" , "ut" 
            , "varic_select" , "ventilation" , "viability" , "visibility"  , "wifejobs" , "wifestatus" , "wifetypes" ,  ';
   }

// at the end of class call
   public function __destruct() {
//close connection
      R::close();
   }
}
