<?php
require_once("imp/_imp.php");

class Controllers {

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

// 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 = 'merge';
        $this->shcontrollname = 'Merge';
        $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);
        
        set_time_limit(0);    ## unlimited excution time as this script take a lot of time 

# here show the active page in menu
// array called active[] with level of active element
        $active[0] = 'merge'; //controller name or tab name
        $active[1] = 'merge'; //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;
        
        $this->suggestStatusNo = 1 ;
//here will call all php pages
        require_once("_header.php");
        require_once("_sidebar.php");
        require_once("_leftsidebar.php");
        require_once("_footer.php");
        
        //find loading time
        $this->time = microtime(true);
        $this->today = date('Y-m-d');
    }

//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']);


        
        //call other class with other content
        header::headershow($this->smarty, $this->hosturl);
        sidebar::sidebarshow($this->smarty, $this->hosturl, $this->active);
         $this->smarty->display('merge/add.html');
        leftsidebar::leftsidebarshow($this->smarty, $this->hosturl);
        footer::footershow($this->smarty, $this->hosturl);
    }

    public function mergedb() 
    {
        //check login
        autho::checkautho($this->hosturl);
        //check authoize
        autho::checkauthoize($this->controllname, $this->hosturl, $_SESSION['role_id']);
          
        $oldDbHost = filter_input(INPUT_POST, "oldDbHost");
        $oldDbName = filter_input(INPUT_POST, "oldDbName");
        $oldDbUser = filter_input(INPUT_POST, "oldDbUser");
        $oldDbPassword = filter_input(INPUT_POST, "oldDbPass");
        
        ## define another database
        R::addDatabase("old", 'mysql:host=' . $oldDbHost . ';dbname=' . $oldDbName . '', '' . $oldDbUser . '', '' . $oldDbPassword . '');

        ## switch to the new database
        R::selectDatabase("old");
        
        ### done 
        
        $this->wifetypes();
          $this->wifejobs();
          $this->education();
          $this->bloodtypes();
          $this->husbandjobs();
          //        #####################################################
          $this->husbandtypes();
          $this->patients(); 
        $this->visits();
          $this->phregularity();
          $this->phamount();
          //        #####################################################
          $this->phdysmenorrhoea();
          $this->phmenstrual();
          $this->phcontracttype();
          $this->phcontraception();
          $this->phobstericterplace();
          //        #####################################################
          $this->phobstetric();
          $this->phpastmedicaldisease();
          $this->phpastmedical();
          $this->phpastsurgicaloperation();
          $this->phpastsurgical();
          //        #####################################################
          $this->phpastgynecologicaloperation();
          $this->phpastart();
          $this->phfamilydisease();
          $this->phfamilyrelative();
          $this->phfamily();
          //        #####################################################
          $this->examination();
          $this->phpastgynecological();
          $this->phpastartcenter();
          $this->phpastarttype();
          $this->semen();
          //        #####################################################
          $this->hsg();
          $this->ustv();
          $this->laparoscopy();
          $this->hysteroscopy();
          $this->mrict();
          //        #####################################################
          $this->hormon();
          $this->Pathology();
          $this->gyna();
          $this->mainantenental();
          $this->antenalvisit();
          //        #####################################################
          $this->drugs();
          $this->sonar();
          $this->gynaussimple();
          $this->programesetting();
          $this->investcats();
          //        #####################################################
          $this->invests();
          $this->gynainvestigation();
          $this->mainantenentalinvest();
          $this->mainantenentalus();
          $this->gynadrugs();
          $this->mainantenentaldrugs();
          $this->gtimage(); 

        R::selectDatabase('default');
        $this->fixProblems();

        echo "Time Elapsed: ".((microtime(true) - $this->time)/60)."m";
        
        
    }
    
    private function fixProblems() 
    {
        ### if drugid is null but has  drug name insert it in drug table and save inserted id in drugid (gyna & antenatal )
        
        ####################
        ## antenatal drugs
        ####################
        $allEmptyDrugs = R::getAll("select * from mainantenentaldrugs where drugid is null and drugname is not null and drugname != ''");
        foreach ($allEmptyDrugs as $emoDrug) 
        {
            ## check if drug exist
            $drug = R::getRow("select id from drugs where drugname = ?" , [$emoDrug['drugname']]);
            if(count($drug) == 0)    ## this drug doesnot exist in drug table
            {
                $newDrug = R::dispense('drugs');
                $newDrug->drugname = $emoDrug['drugname']  ;
                $newDrug->drugtype = $emoDrug['drugtype']  ;
                $newDrug->drugdos = $emoDrug['drugdos']  ;
                $newDrug->initialbalance = 0  ;
                $newDrug->currentbalance = 0  ;
                $id = R::store($newDrug);
                        
                R::exec("update mainantenentaldrugs set drugid = ? where id = ?",[$id , $emoDrug['id']]);
            }
            else    ## this drug  exist in drug table
            {
                R::exec("update mainantenentaldrugs set drugid = ? where id = ?",[$drug['id'] , $emoDrug['id']]);
            }
        }
        
        ####################
        ## gyna drugs
        ####################
        
        
        $allEmptyDrugsG = R::getAll("select * from gynadrugs where drugid is null and drugname is not null and drugname != ''");
        foreach ($allEmptyDrugsG as $emoDrug) 
        {
            ## check if drug exist
            $drug = R::getRow("select id from drugs where drugname = ?" , [$emoDrug['drugname']]);
            if(count($drug) == 0)    ## this drug doesnot exist in drug table
            {
                $newDrug = R::dispense('drugs');
                $newDrug->drugname = $emoDrug['drugname']  ;
                $newDrug->drugtype = $emoDrug['drugtype']  ;
                $newDrug->drugdos = $emoDrug['drugdos']  ;
                $newDrug->initialbalance = 0  ;
                $newDrug->currentbalance = 0  ;
                $id = R::store($newDrug);;          
                R::exec("update gynadrugs set drugid = ? where id = ?",[$id , $emoDrug['id']]);
            }
            else    ## this drug  exist in drug table
            {
                R::exec("update gynadrugs set drugid = ? where id = ?",[$drug['id'] , $emoDrug['id']]);
            }
        }
        
        
        ####################
        ## patientss status No 
        ####################
        
        $this->setstatusno();
    }
    
    private function setstatusno()
    {
        $usedStatusNoArray = array();
        $NotUsedStatusNoArray = array(); 
        
        ## count of  all Patients in database
        $allPatients = R::getRow("select count(id) from patients ");
        
        $from = 1 ;
        $to = $allPatients['count(id)'] ;
         
        ## available range of status no     = all - used
        $allStatusNoArray = range($from, $to);
        
        $usedStatusNo = R::getAll("select id , statusno from patients where statusno BETWEEN  ?  AND  ? ORDER BY statusno ASC " , [$from, $to]);
        
        foreach ($usedStatusNo as $sss) {
            array_push($usedStatusNoArray, $sss['statusno']);
        }
        
        $NotUsedStatusNoArray = array_diff($allStatusNoArray, $usedStatusNoArray);         
        
        ## all Patients With Empty Status number
        $allEmpStatusPat = R::getAll("select id , statusno from patients where statusno = 0 or statusno is null ");
        
        $index = key($NotUsedStatusNoArray);       // First Element's Key
        foreach ($allEmpStatusPat as $patient) {
            R::exec("update patients set statusno = ? where id = ?",[$NotUsedStatusNoArray[$index] , $patient['id']]);
            $index++ ;
        }
    }

    
    private function wifetypes()     ## alltypes table to wifetypes table 
    {
        $Except = '' ;
        $alltypes = R::findAll("alltype");
        R::exec("TRUNCATE TABLE obgy_app.wifetypes");
        foreach ($alltypes as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.wifetypes(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in alltypes transfer data<br/>';
            }
            
            
        }
        print $Except;
    }
    
    private function wifejobs()        ## alljob table to wifejobs table 
    {
        $Except = '' ;
        $allJobs = R::findAll("alljob");
        R::exec("TRUNCATE TABLE obgy_app.wifejobs");
        foreach ($allJobs as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.wifejobs(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in alljob transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function education()        ## education table to education table 
    {
        $Except = '' ;
        $allEducation = R::findAll("education");
        R::exec("TRUNCATE TABLE obgy_app.education");
        foreach ($allEducation as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.education(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in education transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function bloodtypes()        ## bltype table to bloodtypes table 
    {
        $Except = '' ;
        $allBltype = R::findAll("bltype");
        R::exec("TRUNCATE TABLE obgy_app.bloodtypes");
        foreach ($allBltype as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.bloodtypes(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in bltype transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function husbandjobs()        ## alljobhusband table to husbandjobs table 
    {
        $Except = '' ;
        $allHusbandjobs = R::findAll("alljobhusband");
        R::exec("TRUNCATE TABLE obgy_app.husbandjobs");
        foreach ($allHusbandjobs as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.husbandjobs(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in alljobhusband transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function husbandtypes()        ## alltypehusband table to husbandtypes table 
    {
        $Except = '' ;
        $allHusbandtypes = R::findAll("alltypehusband");
        R::exec("TRUNCATE TABLE obgy_app.husbandtypes");
        foreach ($allHusbandtypes as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.husbandtypes(id , title) VALUES(?,?)', [$row->id , $row->name]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in alltypehusband transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function visits()        ## visit table to visits table 
    {
        $Except = '' ;
        $allvisits = R::findAll("visit");
        R::exec("TRUNCATE TABLE obgy_app.visits");
        foreach ($allvisits as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.visits(patientid, visitdate, detectionid, detectionvalue, visitorder, enterordered, view, deleted) 
                                VALUES(?,?,?,?,?,?,?,?)', [$row->patientid , $row->visitdate, '', 0,0 , 0, $row->visitorder , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in visit transfer data<br/>';
            }
        }
        print $Except;
    }
    
    
    private function patients()        ## patientinformation table to patients table 
    {
        $Except = '' ;
        $allpatients = R::findAll("patientinformation");
        R::exec("TRUNCATE TABLE obgy_app.patients");
        foreach ($allpatients as $row) 
        {
            $row = $this->modifypatientstatus($row);
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.patients(
                                  id, wifename, wiftypeid, dateofbirth, wifebl, wifeage
                                , wifeeducation, wifejob, wifenationalid, mirragefrom, mirragefromdate,boyson
                                , girlson, statuesid, husdandname, husbandtypeid, husbandage, husbanddateofbirth
                                , husbandbl, husbandjob, husbandeducation,husbandnationalid, address,phone
                                , mobile, importantnote, risk,risktype,spacialnotes,statusno
                                , done,pno, ab, ectopic, vmodel, deleted
                                ) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                $row->id , $row->wifename,$row->wiftypeid , $row->dateofbirth , $row->wifebl,$row->wifeage
                               ,$row->wifeeducation , $row->wifejob,$row->wifenationalid , $row->mirragefrom , $row->mirragefromdate,$row->boyson
                               ,$row->girlson , $row->statuesid,$row->husdandname , $row->husbandtypeid , $row->husbandage,$row->husbanddateofbirth
                               ,$row->husbandbl , $row->husbandjob,$row->husbandeducation , $row->husbandnationalid , $row->address,$row->phone
                               ,$row->mobile , $row->importantnote,$row->risk ,0 , $row->spacialNotes,$row->statusno
                               ,1 , $row->pno,$row->ab , $row->ectopic , $row->vmodel,$row->tempdelete
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patientinformation transfer data<br/>';
            }
        }
        print $Except;
    }
    
     private function modifypatientstatus($row)     ## modify old.patientinformation.statusid  accordind to new records
    {
        switch ($row->statuesid) 
        {
            case 1:
                $row->statuesid = 2 ;
                break;
            
            case 2:
                $row->statuesid = 5 ;
                break;
            
            case 3:
                $row->statuesid = 3 ;
                break;
            
            case 4:
                $row->statuesid = 4 ;
                break;
            
            case 5:
                $row->statuesid = 1 ;
                break;

            default:
                break;
        }
        return $row ;
    }   
    
    

    private function phregularity()        ## mensregularity table to phregularity table 
    {
        $Except = '' ;
        $allRegularity = R::findAll("mensregularity");
        R::exec("TRUNCATE TABLE obgy_app.phregularity");
        foreach ($allRegularity as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phregularity(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in mensregularity transfer data<br/>';
            }
        }
        print $Except;
        
       
    }
    
    private function phamount()      ## mensamount table to phamount table 
    {
        $Except = '' ;
        $allAmount = R::findAll("mensamount");
        R::exec("TRUNCATE TABLE obgy_app.phamount");
        foreach ($allAmount as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phamount(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in mensamount transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phdysmenorrhoea()            ## mensdysmo table to phdysmenorrhoea table 
    {
        $Except = '' ;
        $allDysmen = R::findAll("mensdysmo");
        R::exec("TRUNCATE TABLE obgy_app.phdysmenorrhoea");
        foreach ($allDysmen as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phdysmenorrhoea(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in mensdysmo transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phmenstrual()            ## mensturalhistory table to phmenstrual table 
    {
        $Except = '' ;
        $allMenstural = R::findAll("mensturalhistory");
        R::exec("TRUNCATE TABLE obgy_app.phmenstrual");
        foreach ($allMenstural as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phmenstrual
                    (patientid,sysdate,menarche,duration,length,phregularity,phamount,phdysmenorrhoea,phl_mp,del,doctorid) VALUES(?,?,?,?,?,?,?,?,?,?,?)', 
                    [$row->patientid , date('Y-m-d') , $row->menarche, $row->duration, $row->length, $row->regularity, $row->amount, $row->dysmenorrhoea, $row->lmp, $row->tempdelete, 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in mensturalhistory transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phcontracttype()            ## contertype table to phcontracttype table 
    {
        $Except = '' ;
        $allContracttype = R::findAll("contertype");
        R::exec("TRUNCATE TABLE obgy_app.phcontracttype");
        foreach ($allContracttype as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phcontracttype(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in contertype transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phcontraception()            ## contrgeneral table to phcontraception table 
    {
        $Except = '' ;
        $allcontraception = R::findAll("contrgeneral");
        R::exec("TRUNCATE TABLE obgy_app.phcontraception");
        foreach ($allcontraception as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phcontraception
                    (`patientid`, `sysdate`, `contype`, `contduration`, `contstopped`, `contranote`, `del`, `doctorid`) VALUES(?,?,?,?,?,?,?,?)', 
                    [$row->patientid , date('Y-m-d') , $row->contertypeid, $row->duration, $row->stoped, $row->note, $row->tempdelete, 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in contrgeneral transfer data<br/>';
            }
        }
        print $Except;
    }
    
    
    
    private function phobstericterplace()            ## obtetricplace table to phobstericterplace table 
    {
        $Except = '' ;
        $allObtetricplace = R::findAll("obtetricplace");
        R::exec("TRUNCATE TABLE obgy_app.phobstericterplace");
        foreach ($allObtetricplace as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phobstericterplace(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in obtetricplace transfer data<br/>';
            }
        }
        print $Except;
    }
    
    
    
    
    private function phobstetric()            ## obstetrichistorydetails table to phobstetric table 
    {
        $Except = '' ;
        $allObstric = R::findAll("obstetrichistorydetails");
        R::exec("TRUNCATE TABLE obgy_app.phobstetric");
        foreach ($allObstric as $row) 
        {
            $row = $this->modifyobstrictermination($row);
            $duration = $row->duration;      // in months new prog need it in weeks
            $newDuration = floor((30 * $duration ) / 7);      // in months new prog need it in weeks
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phobstetric(
                                `patientid`, `sysdate`, `obstrduration`, `obstermination`, `obsplace`
                                , `obsdate`, `obsnote`, `del`, `doctorid`,`outcomeid`, `babytype`
                                , `babygendernew`, `babyweight`, `babyname`, `babytype1`, `babygendernew1`, `babyweight1`
                                , `babyname1`, `babytype2`, `babygendernew2`, `babyweight2`, `babyname2`
                                ) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                $row->patientid, date('Y-m-d'), $newDuration, $row->terminatedid, $row->place
                            ,$row->obstradate , $row->notes,$row->tempdelete , 0, $row->outcomeid, $row->babytype
                               ,$row->babygender , $row->babyweight,$row->babyname , $row->babytype1 , $row->babygender1 , $row->babyweight1
                               ,$row->babyname1 , $row->babytype2,$row->babygender2 , $row->babyweight2 , $row->babyname2
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in obstetrichistorydetails transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function modifyobstrictermination($row)            ## modify old.patientinformation.statusid  accordind to new records
    {
       switch ($row->terminatedid) 
        {
            case 1:
                $row->statuesid = 1 ;
                break;
            
            case 2:
                $row->statuesid = 2 ;
                break;
            
            case 3:
                $row->statuesid = 4 ;
                break;
            
            case 4:
                $row->statuesid = 3 ;
                break;
            
            case 6:
                $row->statuesid = 5 ;
                break;

            default:
                break;
        }
        return $row ;
    }
    
    
    
    private function phpastmedicaldisease()            ## disease table to phpastmedicaldisease table 
    {
        $Except = '' ;
        $allMedicaldisease = R::findAll("disease");
        R::exec("TRUNCATE TABLE obgy_app.phpastmedicaldisease");
        foreach ($allMedicaldisease as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastmedicaldisease(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in disease transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phpastmedical()            ## pasthistorymedical table to phpastmedical table 
    {
        $Except = '' ;
        $allPastmedical = R::findAll("pasthistorymedical");
        R::exec("TRUNCATE TABLE obgy_app.phpastmedical");
        foreach ($allPastmedical as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastmedical(`patientid`, `sysdate`, `medicaldisease`, `medicalnote`, `del`, `doctorid`) VALUES(?,?,?,?,?,?)',
                                [$row->patientid , date('Y-m-d'),$row->diseasid , $row->note , $row->tempdelete, 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in pasthistorymedical transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function phpastsurgicaloperation()            ## operation table to phpastsurgicaloperation table 
    {
        $Except = '' ;
        $allSurgicaloperation = R::findAll("operation");
        R::exec("TRUNCATE TABLE obgy_app.phpastsurgicaloperation");
        foreach ($allSurgicaloperation as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastsurgicaloperation(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in operation transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phpastsurgical()            ## pasthistorysurgical table to phpastsurgical table 
    {
        $Except = '' ;
        $allPastSurgical = R::findAll("pasthistorysurgical");
        R::exec("TRUNCATE TABLE obgy_app.phpastsurgical");
        foreach ($allPastSurgical as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastsurgical
                        (`patientid`, `doctorid`, `sysdate`, `surgicaloperation`, `phsurgicalyear`, `phsurgicalsurgeon`, `surgicalnote`, `del`) VALUES(?,?,?,?,?,?,?,?)',
                        [$row->patientid ,0 ,  date('Y-m-d'),$row->operationid , $row->year , $row->surgean, $row->note, $row->tempdelete]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in pasthistorysurgical transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function phpastgynecologicaloperation()            ## operationgyna table to phpastgynecologicaloperation table 
    {
        $Except = '' ;
        $allOperationGyna = R::findAll("operationgyna");
        R::exec("TRUNCATE TABLE obgy_app.phpastgynecologicaloperation");
        foreach ($allOperationGyna as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastgynecologicaloperation(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in operationgyna transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phpastgynecological()            ## pasthistorygynencological table to phpastgynecological table 
    {
        $Except = '' ;
        $allPastGynencological = R::findAll("pasthistorygynencological");
        R::exec("TRUNCATE TABLE obgy_app.phpastgynecological");
        foreach ($allPastGynencological as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastgynecological
                    (`patientid`, `doctorid`, `sysdate`, `gynecologicaloperation`, `gynecologicalyear`, `gynecologicalsurgeon`, `gynecologicalnote`, `del`) VALUES(?,?,?,?,?,?,?,?)',
                    [$row->patientid ,0 ,  date('Y-m-d'),$row->operationid , $row->year , $row->syrgon, $row->note, $row->tempdelete]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in pasthistorygynencological transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function phpastartcenter()            ## conter table to phpastartcenter table 
    {
        $Except = '' ;
        $allArtcenter = R::findAll("conter");
        R::exec("TRUNCATE TABLE obgy_app.phpastartcenter");
        foreach ($allArtcenter as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastartcenter(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in conter transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phpastarttype()            ##  phpastarttype table 
    {
        $Except = '' ;
        R::exec("TRUNCATE TABLE obgy_app.phpastarttype");
        try 
            {
                $res = R::exec("INSERT INTO `obgy_app`.`phpastarttype` (`id`, `name`, `del`) VALUES ('1', 'IUI', '0'), ('2', 'IVF', '0'), ('3', 'ICSI', '0')");
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in phpastarttype transfer data<br/>';
            }
            print $Except;
    }
    
    private function phpastart()            ## pasthistoryart table to phpastart table 
    {
        $Except = '' ;
        $allPastArt = R::findAll("pasthistoryart");
        R::exec("TRUNCATE TABLE obgy_app.phpastart");
        foreach ($allPastArt as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phpastart
                    (`patientid`, `sysdate`, `arttype`, `artcenter`, `artyear`, `artnote`, `del`, `doctorid`) VALUES(?,?,?,?,?,?,?,?)',
                    [$row->patientid , date('Y-m-d'),$row->type , $row->conterid , $row->year, $row->note, $row->tempdelete , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in pasthistoryart transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function phfamilydisease()            ## diseasefamily table to phfamilydisease table 
    {
        $Except = '' ;
        $allFamilyDisease = R::findAll("diseasefamily");
        R::exec("TRUNCATE TABLE obgy_app.phfamilydisease");
        foreach ($allFamilyDisease as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phfamilydisease(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in diseasefamily transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phfamilyrelative()            ## relative table to phfamilyrelative table 
    {
        $Except = '' ;
        $allFamilyRelative = R::findAll("relative");
        R::exec("TRUNCATE TABLE obgy_app.phfamilyrelative");
        foreach ($allFamilyRelative as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phfamilyrelative(id , name, del) VALUES(?,?,?)', [$row->id , $row->name , 0]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in relative transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function phfamily()            ## familyhistory table to phfamily table 
    {
        $Except = '' ;
        $allPastFamily = R::findAll("familyhistory");
        R::exec("TRUNCATE TABLE obgy_app.phfamily");
        foreach ($allPastFamily as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.phfamily
                    (`patientid`, `doctorid`, `sysdate`, `family_disease`, `family_relative`, `familynote`, `del`) VALUES(?,?,?,?,?,?,?)',
                    [$row->patientid , 0 , date('Y-m-d'),$row->diseaseid , $row->relativeid , $row->note, $row->tempdelete]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in familyhistory transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function examination()            ## examination table to examination table 
    {
        $Except = '' ;
        $allExamination = R::findAll("examination");
        R::exec("TRUNCATE TABLE obgy_app.examination");
        foreach ($allExamination as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.examination
                    (`patientid`, `doctorid`, `sysdate`, `examinationdate`, `weight`, `height`, `bmi`, `b_p`, `pulse`, `headandneck`, `chestandheart`, `abdomen`, `pelvis`, `extremitis`, `del`)
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                    [$row->patientid , 0 , date('Y-m-d'),$row->examinationdate , $row->wt , $row->ht, $row->bmi, $row->bp, $row->pulse
                    ,$row->head , $row->chest , $row->abdeminal, $row->pelric,$row->extra , $row->tempdelete ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in examination transfer data<br/>';
            }
        }
        print $Except;
    }
    
    
    
    private function semen()            ## investigation table to semen table 
    {
        $Except = '' ;
        $allSemen = R::getAll("SELECT  result1, result2, result3,result4,result5, result6,result7, result8, result9, patientid,  investdate, tempdelete, notes
                                  FROM investigation WHERE investtypeid = 1");
        R::exec("TRUNCATE TABLE obgy_app.semen");
        foreach ($allSemen as $row) 
        {
            switch ($row['result6'] ) 
            {
                case 4:
                    $result6 = 1 ;
                    break;
                default:
                    $result6 = '' ;
                    break;
            }
            switch ($row['result8'] ) 
            {
                case 2:
                    $result8 = 1 ;
                    break;
                default:
                    $result8 = '' ;
                    break;
            }
            switch ($row['result9'] ) 
            {
                case 3:
                    $result9 = 1 ;
                    break;
                default:
                    $result9 = '' ;
                    break;
            }
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.semen(
                                sdate, patientid, del, count, motilitya
                                , motilityab, normal, puscell, notes, normaltype, oligo
                                , astheno, tetrato, doctorid
                                ) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2']
                                   ,$row['result3'] , $row['result4'],$row['result5'] , $row['notes'], $result6 , $row['result7']
                                   ,$result8 , $result9 ,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function hsg()            ## investigation table to hsg table 
    {
        $Except = '' ;
        $allHsg = R::getAll("SELECT  result2, result4, result5, result6, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 2");
        
        R::exec("TRUNCATE TABLE obgy_app.hsg");
        foreach ($allHsg as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.hsg(sdate, patientid, del, uterus, ltube, notes, smear, rtube, doctorid)
                                 VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                $row['investdate'] , $row['patientid'] ,$row['tempdelete'] , $row['result2'] , $row['result5']
                               ,$row['notes'] , $row['result6'] ,$row['result4'] , 0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function ustv()            ## investigation table to ustv table 
    {
        $Except = '' ;
        $allUstv = R::getAll("SELECT  result1, result2, result3, result4, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 3");
        R::exec("TRUNCATE TABLE obgy_app.ustv");
        foreach ($allUstv as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.ustv(sdate, patientid, del,uterus, radnexa, ladnexa,pelvis, notes, doctorid)
                               VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2']
                               ,$row['result3']  , $row['result4'] ,$row['notes'] ,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except;
    }
    
    private function laparoscopy()            ## investigation table to laparoscopy table 
    {
        $Except = '' ;
        $allLaparoscopy = R::getAll("SELECT  result1, result2, result3, result4, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 4");
        
        R::exec("TRUNCATE TABLE obgy_app.laparoscopy");
        
        foreach ($allLaparoscopy as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.laparoscopy(
                                sdate, patientid, del,uterus, radnexa, ladnexa,pelvis, notes, doctorid)
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2']
                                   ,$row['result3'] , $row['result4'] , $row['notes'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function hysteroscopy()            ## investigation table to hysteroscopy table 
    {
        $Except = '' ;
        $allHysteroscopy = R::getAll("SELECT  result1, result2, result3, result4, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 5");
        R::exec("TRUNCATE TABLE obgy_app.hysteroscopy");
        foreach ($allHysteroscopy as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.hysteroscopy(sdate, patientid, del,cervix, uterine, rostium, lostium,notes, doctorid)
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2']
                                   ,$row['result3'] , $row['result4'] , $row['notes'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function mrict()            ## investigation table to mrict table 
    {
        $Except = '' ;
        $allMrict = R::getAll("SELECT  result1, result2, result3, result4, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 20");
        R::exec("TRUNCATE TABLE obgy_app.mrict");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.mrict(sdate, patientid, del,uterus, radnexa, ladnexa, pelvis,notes, doctorid)
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2']
                                   ,$row['result3'] , $row['result4'] , $row['notes'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function hormon()            ## investigation table to hormon table 
    {
        $Except = '' ;
        $allHormones = R::getAll("SELECT  result1, result2, result3, result4, result5, result6, result7, result8, result9, result10, result11, result12, result13, result14,
                                            result15, result16, patientid, investdate, tempdelete, notes   FROM investigation WHERE investtypeid = 6");
        R::exec("TRUNCATE TABLE obgy_app.hormon");
        foreach ($allHormones as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.hormon(
                                sdate, patientid, del, lh, prl,etwo, amh,
                                 freet, totalt, pfour, tsh, tthree, dht, bhcg,
                                 inhibinb, asd, tfour, dheas, fsh, notes, doctorid
                                ) 
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [$row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result2'] , $row['result3'], $row['result4'] , $row['result5']
                                   ,$row['result6'] , $row['result7'],$row['result8'] , $row['result9'], $row['result10'] , $row['result13'], $row['result14']
                                   ,$row['result15'] , $row['result16'],$row['result11'] , $row['result12'],$row['result1'] , $row['notes'],0]);
                
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function Pathology()            ## investigation table to Pathology table 
    {
        $Except = '' ;
        $allPathology = R::getAll("SELECT  result1, result2,  patientid, investdate, tempdelete   FROM investigation WHERE investtypeid = 12");
        R::exec("TRUNCATE TABLE obgy_app.Pathology");
        foreach ($allPathology as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.Pathology
                                (sdate, patientid, del, cytology, histopathology,  doctorid)
                                VALUES(?,?,?,?,?,?)', 
                                [
                                    $row['investdate'] , $row['patientid'],$row['tempdelete'] , $row['result1'] , $row['result2'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    

    private function gyna()            ## followup table to gyna table 
    {
        $Except = '' ;
        $allGyna = R::getAll("SELECT `id`, `patientid`, `followdate`,`complaint`, `diagnosis_id`, `note`,`tempdelete` FROM followup ");
        R::exec("TRUNCATE TABLE obgy_app.gyna");
        foreach ($allGyna as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.gyna(patientid, gynadate, complaint, diagnosisid, notes, conditions, doctorid)
                                VALUES(?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , $row['followdate'],$row['complaint'] , 0 , $row['note'] ,$row['tempdelete'] ,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in followup transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    private function mainantenental()            ## antenatalsheet table to mainantenental table 
    {
        $Except = '' ;
        $allMain = R::getAll("SELECT `id`, `patientid`, `lmp`, `edd`,`note`,`pregno` FROM  antenatalsheet");
        R::exec("TRUNCATE TABLE obgy_app.mainantenental");
        foreach ($allMain as $row) 
        {
            //get current g of patient
                $myallobsteric = R::getAll("select id  from  obstetrichistorydetails where tempdelete = 0  and patientid = ?" , [$row['patientid']]);
                $directValues = R::getRow("select pno ,ab , ectopic ,vmodel   from  patientinformation where  id = ?" , [$row['patientid']]);
                $g = count($myallobsteric) + $directValues['pno'] + $directValues['ab'] +$directValues['ectopic']+$directValues['vmodel'] ;         
                $done = 1;
                if($row['pregno']  > $g)
                {
                        $done = 0;
                }

                $ivf = date("Y-m-d", strtotime("- 7 days", strtotime($row['lmp'] . ' +9 months ')));    //this is php code
        
        
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.mainantenental(id, patientid, doctorid, lmp, eed, notes, done, ivf,g) 
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [$row['id'] , $row['patientid'],0 , $row['lmp'] , $row['edd'],$row['note'] ,$done , $ivf,$row['pregno']]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in antenatalsheet transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    
    private function antenalvisit()            ## antenataldetails table to  antenalvisit table
    {
        $Except = '' ;
        $allAnt = R::getAll("SELECT `id`, `patientid`, `dateofexamination`, `complaint`, `weight`, `bp`,`note`,`type`,`tempdelete` FROM  antenataldetails");
        R::exec("TRUNCATE TABLE obgy_app.antenalvisit");
        foreach ($allAnt as $row) 
        {
            $mainantenentalid = R::getRow("SELECT `id` FROM  antenatalsheet WHERE patientid = ?  and pregno = ?  limit 1" , [$row['patientid'] ,$row['type'] ]);
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.antenalvisit( patientid, doctorid, antenaldate, complaint, wt, bp, notes, conditions, mainantenentalid)
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , 0,$row['dateofexamination'] , $row['complaint'] , $row['weight'],$row['bp'] , $row['note'] , $row['tempdelete'],$mainantenentalid['id']
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in antenataldetails transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    private function drugs()            ## drugs table to drugs table 
    {
        $Except = '' ;
        $allDrugs = R::getAll("SELECT  id, drugCat,drugName ,drugType , drugDos  FROM drugs");
        R::exec("TRUNCATE TABLE obgy_app.drugs");
        foreach ($allDrugs as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.drugs( id,drugcat , drugname,drugtype ,drugdos ,initialbalance , currentbalance )
                                VALUES(?,?,?,?,?,?,?)', 
                                [
                                    $row['id'] , $row['drugCat'],$row['drugName'] , $row['drugType'] , $row['drugDos'] ,0 , 0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in drugs transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    private function sonar()            ## sonar table to sonar table 
    {
        $Except = '' ;
        $allSonar = R::getAll("SELECT  patientid, sonardate,imagename ,notes , vediourl , tempdelete   FROM sonar");
        R::exec("TRUNCATE TABLE obgy_app.sonar");
        foreach ($allSonar as $row) 
        {
            if($row['imagename'] != '.' || $row['vediourl'] != '.')
            {
                if($row['imagename'] != '.')
                {
                    $type = 0 ;
                }
                elseif ($row['vediourl'] != '.')
                {
                    $type = 1 ;
                }
                try 
                {
                    $res = R::exec('INSERT INTO obgy_app.sonar( patientid, sonardate,imagename ,notes , vediourl , tempdelete , type ,  originalname)
                                    VALUES(?,?,?,?,?,?,?,?)', 
                                    [
                                        $row['patientid'] , $row['sonardate'], $row['imagename'],$row['notes'] , $row['vediourl'] , $row['tempdelete'] ,$type , 'file name'
                                    ]);
                    $Except = '' ;

                }  
                catch (Exception $e) 
                {
                    $Except = 'An error has been occured in sonar transfer data<br/>';
                }
            }
            
        }
        print $Except; 
    }
    
    private function gynausccomplete()            ## ultrasounfenhace table to gynaus table 
    {
        $Except = '' ;
        $allDrugs = R::getAll("SELECT  id, fdate,menstrualdate ,ulerusl , ulerusw ,ulerusv, cervixl,cervixw ,cervixv , ovaryltl ,
                                        ovaryltw, ovaryltv,ovaryrtl ,ovaryrtw , ovaryrtv ,patientid, ultendo
                                          FROM ultrasounfenhace");
        R::exec("TRUNCATE TABLE obgy_app.gynausficils");
        foreach ($allDrugs as $row) 
        {
            $lfollicles = R::getAll("SELECT `id` , name,length ,width , volume,ultrasounfenhaceidq,type FROM  ultrafollicle WHERE ultrasounfenhaceidq = ?  and type = ?  " , [$row['id'] ,1 ]);
            $rfollicles = R::getAll("SELECT `id` , name,length ,width , volume,ultrasounfenhaceidq,type FROM  ultrafollicle WHERE ultrasounfenhaceidq = ?  and type = ?  " , [$row['id'] ,0 ]);
            $lfolliclesno = count($lfollicles);
            $rfolliclesno = count($rfollicles);
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.gynaus(
                                    patientid, doctorid, deleted, Date, day,
                                    endo, uterusl, uterusw,uterusv, cervixl,
                                    cervixw, cervixv, ovrayll,ovraylw, ovraylv,
                                    ovrayrl, ovrayrw, ovrayrv,lfolliclesno, rfolliclesno
                                )
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , 0 , 0 , $row['fdate'] , $row['menstrualdate'] ,
                                    $row['ultendo'] , $row['ulerusl'],$row['ulerusw'] , $row['ulerusv'] , $row['cervixl'] ,
                                    $row['cervixw'] , $row['cervixv'],$row['ovaryltl'] , $row['ovaryltw'] , $row['ovaryltv'] ,
                                    $row['ovaryrtl'] , $row['ovaryrtw'],$row['ovaryrtv'] , $lfolliclesno , $rfolliclesno
                                ]);
                $Except = '' ;
                $gid = R::getInsertID();
                try 
                {
                        foreach ($lfollicles as $ff) 
                        {
                            $res = R::exec('INSERT INTO obgy_app.gynausficils(gynausid,name ,length , width,volume,type
                                        )
                                        VALUES(?,?,?,?,?,?)', 
                                        [
                                            $gid , $ff['name'],$ff['length'] , $ff['width'] , $ff['volume'] ,0                                  
                                        ]);
                        }

                        foreach ($rfollicles as $rf) 
                        {
                            $res = R::exec('INSERT INTO obgy_app.gynausficils(gynausid,name ,length , width,volume,type
                                        )
                                        VALUES(?,?,?,?,?,?)', 
                                        [
                                            $gid , $rf['name'],$rf['length'] , $rf['width'] , $rf['volume'] ,1                                  
                                        ]);
                        }
                }  
                catch (Exception $e) 
                {
                    $Except = 'An error has been occured in ultrafollicle transfer data<br/>';
                }

            }  
            catch (Exception $e) 
            {
                $Except .= 'An error has been occured in ultrasounfenhace transfer data<br/>';
            }
        }

        print $Except; 
    }
    
    private function gynaussimple()            ## folliculometry table to gynaus table 
    {
        $Except = '' ;
        $allDrugs = R::getAll("SELECT  patientid,fdate ,menstrualdate , fno,
                                        rsize, lno,lsize ,endo , notes
                                          FROM folliculometry");
        
        R::exec("TRUNCATE TABLE obgy_app.gynaus");
        
        foreach ($allDrugs as $row) 
        {
            try 
            {
                //R::debug();
                $res = R::exec('INSERT INTO obgy_app.gynaus(
                                    date, day, ovraylno, ovraylsize, ovrayrno,
                                    ovrayrsize, endo, notes, patientid, doctorid,deleted
                                )
                                VALUES(?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['fdate'] , $row['menstrualdate'],$row['fno'] , $row['rsize'] , $row['lno'] ,
                                    $row['lsize'] , $row['endo'],$row['notes'] , $row['patientid'] , 0 ,0                                  
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in folliculometry transfer data<br/>';
            }
        }
        $this->gynausccomplete();
        print $Except; 
    }
    
    
    
     private function programesetting()            ## programesetting table to gynausficils table 
    {
         $Except = '' ;
        $allDrugs = R::getAll("SELECT  centername,doctorname ,spacial , address,phone, logo,color ,above , buttom , 
                                        pleft,pright ,center , gynalink,antenatal, fontsizeen,fontsizear ,alignttt , ultrasound , addshours , patientnoaut
                                          FROM programesetting");
        R::exec("TRUNCATE TABLE obgy_app.programesetting");
        foreach ($allDrugs as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.programesetting(
                                    centername,doctorname ,spacial , address,phone,
                                    logo,color ,above , buttom , pleft,
                                    pright ,center , gynalink,antenatal, fontsizeen,
                                    fontsizear ,alignttt , ultrasound , addshours , patientnoaut ,
                                    ishide,simpleview,displaydoctorname,entryorder,investshow,
                                    prescriptioncashing,diagnosshow,visitsform
                                )
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['centername'] , $row['doctorname'],$row['spacial'] , $row['address'] , $row['phone'] ,
                                    $row['logo'] , $row['color'],$row['above'] , $row['buttom'] , $row['pleft'] ,
                                    $row['pright'] , $row['center'],$row['gynalink'] , $row['antenatal'] , $row['fontsizeen'] ,
                                    $row['fontsizear'] , $row['alignttt'],$row['ultrasound'] , $row['addshours'] , $row['patientnoaut'] ,
                                    1 , 0,0 , 0 , 0 ,0 , 1,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in programesetting transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
     private function investcats()            ## allinveshgaharir table to investcats table 
    {
         $Except = '' ;
        R::exec("TRUNCATE TABLE obgy_app.investcats");
        try 
        {
            $res = R::exec("INSERT INTO obgy_app.investcats(id, name, deleted,displayorder)
                                VALUES ('1', 'Chemistry', '0', '2'), 
                                        ('2', 'Urine & Stool', '0', '7'), 
                                        ('3', 'Hematology', '0', '1'),
                                        ('4', 'Infections', '0', '5'),
                                        ('5', 'Hormons', '0', '3'),
                                        ('6', 'Thrombophilia Package', '0', '8'),
                                        ('7', 'Immunology', '0', '4'),
                                        ('8', 'Oncology', '0', '6'),
                                        ('9', 'Genetics', '0', '9'),
                                        ('10', 'For operations', '0', '0'),
                                        ('11', 'Male(Husband)', '0', '10'),
                                        ('12', 'Others', '0', '0'),
                                        ('13', 'Old Favorite', '0', '-1')");
                $Except = '' ;
        }  
        catch (Exception $e) 
        {
            $Except = 'An error has been occured in allinveshgaharir transfer data<br/>';
        }

        print $Except; 
    }
    
     private function invests()            ## investresult table to invests table 
    {
         $Except = '' ;
        $allMrict = R::getAll("SELECT  id, investid,resultname  FROM investresult ");
        R::exec("TRUNCATE TABLE obgy_app.invests");
        foreach ($allMrict as $row) 
        {
            $investCatId = $this->getinvestcatid($row['investid']);     
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.invests(id, investcatid, name,favorite, deleted)
                                VALUES(?,?,?,?,?)', 
                                [
                                    $row['id'] , $investCatId ,$row['resultname'] , 0 ,0
                                ]);
                $Except = '' ;
            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in investresult transfer data<br/>';
            }
        }
        print $Except; 
    }

    private function getinvestcatid($id)          ## switch between old cat id and new ones
    { 
        $Except = '' ;
        $investCatId = '';
        switch ($id) 
        {
            case 1:
                $investCatId = 1 ;
                break;
            
            case 2:
                $investCatId = 2 ;
                break;
            
            case 3:
                $investCatId = 3 ;
                break;
            
            case 4:
                $investCatId = 4 ;
                break;
            
            case 5:
                $investCatId = 5 ;
                break;
            case 6:
                $investCatId = 6 ;
                break;
            
            case 7:
                $investCatId = 7 ;
                break;
            
            case 8:
                $investCatId = 8 ;
                break;
            
            case 9:
                $investCatId = 9 ;
                break;
            
            case 11:
                $investCatId = 11 ;
                break;
            
            case 21:
                $investCatId = 12 ;
                break;
            
            case 22:
                $investCatId = 13 ;
                break;
            
            case 27:
                $investCatId = 10 ;
                break;
            
            default:
                break;
        }
        return $investCatId ;
    }
    
    private function gynainvestigation()            ## patientinvestigation table to gynainvestigation table 
    {
        $Except = '' ;
        $allMrict = R::getAll("SELECT  patientid,investid ,investdate ,result   FROM patientinvestigation ");
        R::exec("TRUNCATE TABLE obgy_app.gynainvestigation");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $res = R::exec('INSERT INTO obgy_app.gynainvestigation(patientid, date, investid,investresult, deleted, doctorid ,forhusband )
                                VALUES(?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , $row['investdate'],$row['investid'] , $row['result'] , 0,0 ,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patientinvestigation transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    
    private function mainantenentalinvest()            ## patientinvestigationantenatel table to mainantenentalinvest table 
    {
        $Except = '' ;
        $allMrict = R::getAll("SELECT  patientid,investid ,investdate ,result , pregno   FROM patientinvestigationantenatel ");
        R::exec("TRUNCATE TABLE obgy_app.mainantenentalinvest");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $mainantenatalid = R::getRow("SELECT id FROM antenatalsheet where  pregno = ? and patientid = ?" , [$row['pregno'] , $row['patientid']]);
                $res = R::exec('INSERT INTO obgy_app.mainantenentalinvest(patientid,mainantenatalid ,date, investid,investresult, deleted, doctorid ,forhusband )
                                VALUES(?,?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , $mainantenatalid['id'] , $row['investdate'],$row['investid'] , $row['result'] , 0,0 ,0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patientinvestigationantenatel transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    
    private function mainantenentalus()            ## patientultrasoundnew table to mainantenentalus table 
    {
        $Except = '' ;
        $allMrict = R::getAll("SELECT patientid , usdate, ncal,gs ,cpl ,fhr , bpd ,fl  , placenta ,fwt ,notes , pregno  FROM patientultrasoundnew ");
        R::exec("TRUNCATE TABLE obgy_app.mainantenentalus");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $mainantenatalid = R::getRow("SELECT id FROM antenatalsheet where  pregno = ? and patientid = ?" , [$row['pregno'] , $row['patientid']]);
                $res = R::exec('INSERT INTO obgy_app.mainantenentalus(mainantenatalid, doctorid, patientid,date, nga, gs, crl,fhr, bpd ,fl , placenta,ai , fwt,notes ,deleted)
                                VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $mainantenatalid['id'] , 0 ,$row['patientid'] , $row['usdate'] , $row['ncal']
                                    ,$row['gs'] , $row['cpl'],$row['fhr'] , $row['bpd'] , $row['fl']
                                    ,$row['placenta'] , '',$row['fwt'] , $row['notes'] , 0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patientultrasoundnew transfer data<br/>';
            }
        }
        print $Except; 
    }

    
    private function gynadrugs()            ## patienttreatment table to gynadrugs table 
    {
        $Except = 'empty' ;
        $allMrict = R::getAll("SELECT  patientid,drugid ,drugtypeid ,drugdoseid ,treatmentdate   FROM patienttreatment ");
        R::exec("TRUNCATE TABLE obgy_app.gynadrugs");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $drugId = R::getRow("SELECT id FROM drugs where  drugName = ?" , [$row['drugid']]);
                $res = R::exec('INSERT INTO obgy_app.gynadrugs(patientid, date, drugid,drugtype, drugdos, deleted, doctorid,drugname, forhusband)
                                VALUES(?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , $row['treatmentdate'],$drugId['id'] , $row['drugtypeid'] , $row['drugdoseid']
                                   ,0 , 0 , $row['drugid'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patienttreatment transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function mainantenentaldrugs()            ## patienttreatmentantenal table to mainantenentaldrugs table 
    {
        $Except = '' ;
        $allMrict = R::getAll("SELECT  patientid,drugid ,drugtypeid ,drugdoseid ,treatmentdate ,pregno    FROM patienttreatmentantenal ");
        R::exec("TRUNCATE TABLE obgy_app.mainantenentaldrugs");
        foreach ($allMrict as $row) 
        {
            try 
            {
                $drugId = R::getRow("SELECT id FROM drugs where  drugName = ?" , [$row['drugid']]);
                $mainantenatalid = R::getRow("SELECT id FROM antenatalsheet where  pregno = ? and patientid = ?" , [$row['pregno'] , $row['patientid']]);
                $res = R::exec('INSERT INTO obgy_app.mainantenentaldrugs(patientid,mainantenatalid ,  date, drugid,drugtype, drugdos, deleted, doctorid,drugname, forhusband)
                                VALUES(?,?,?,?,?,?,?,?,?,?)', 
                                [
                                    $row['patientid'] , $mainantenatalid['id'] , $row['treatmentdate'], $drugId['id'],$row['drugtypeid']  , $row['drugdoseid']
                                   ,0 , 0 , $row['drugid'],0
                                ]);
                $Except = '' ;

            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in patienttreatmentantenal transfer data<br/>';
            }
        }
        print $Except; 
    }
    
    private function gtimage()            ## drag table to gtimage table 
    {
        $Except = '' ;
        R::exec("TRUNCATE TABLE obgy_app.gtimage");
        R::exec("TRUNCATE TABLE obgy_app.gtdetail");
        
        $allPatients = R::getAll('SELECT DISTINCT patientid  FROM drag');
        
        foreach ($allPatients as $patient) 
        {
            try 
            {
                R::exec('INSERT INTO obgy_app.gtimage(patientid ,doctorid)VALUES(? , ?)',[$patient['patientid'] , 0]);
                $gid = R::getInsertID();
                $comment='';
                $allImage = R::getAll('SELECT comment,xPoint,yPoint,imageNo,height,width from drag where patientid = ?' , [$patient['patientid']]);
                foreach ($allImage as $row) 
                {
                    $x = $row['xPoint'] + 459 ;
                    $y = $row['yPoint'] + 64 ;
                    $comment= $row['comment'] ;
                    try 
                    {
                        R::exec('INSERT INTO obgy_app.gtdetail(gtid,posx,posy,width,height,imgno , del)
                                        VALUES(?,?,?,?,?,? ,?)', 
                                        [
                                            $gid , $x,$y , $row['width'] , $row['height'], $row['imageNo'] , 0
                                        ]);
                        $Except = '' ;

                    }  
                    catch (Exception $e) 
                    {
                        $Except = 'An error has been occured in drag transfer data<br/>';
                    }
                }
                R::exec('update obgy_app.gtimage set comment = ? where patientid = ?',[$comment , $patient['patientid']]);
            }  
            catch (Exception $e) 
            {
                $Except = 'An error has been occured in drag transfer data<br/>';
            }
        }
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        print $Except; 
    }
    
    
// at the end of class call
    public function __destruct() 
    {
        //echo "Time Elapsed: ".(microtime(true) - $this->time)."s";
        ##close connection
        R::close();
    }

}
