	<?php

// $con1 = mysqli_connect('localhost', 'root', '68965','vl');
// $con2 = mysqli_connect('localhost', 'root','68965', 'vl2');
//
//
// if ($con2->connect_error) {
//   die("Connection failed: " . $con2->connect_error);
// }
//
// echo "\n****Starting vl_rejected_samples_release************************ \n";
//
// $b = "SELECT r.id,r.released, r.released_at, r.comments, r.reject_released_by_id, r.sample_id FROM `vl_rejected_samples_release` r WHERE r.released_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH";
// $result = $con1->query($b);
// $con1->close();
//
// if ($result->num_rows > 0) {
//   // output data of each row
//   while($row = $result->fetch_assoc()) {
//
//     $row['released']?$released = $row['released']:$released = 'NULL';
//     $row['released_at']?$released_at = "'".$row['released_at']."'":$released_at = 'NULL';
//     $row['comments']?$comments = "'".$row['comments']."'":$comments = 'NULL';
//     $row['reject_released_by_id']?$reject_released_by_id = "'".$row['reject_released_by_id']."'":$reject_released_by_id = 'NULL';
//
//     // insertion into vl_rejected_samples_release db2
//     $b1 = "INSERT INTO `vl_rejected_samples_release` (id, released, released_at,comments, reject_released_by_id,sample_id)
//     VALUES(".$row['id'].",'".$row['released']."','".$row['released_at']."','".$row['comments']."','".$row['reject_released_by_id']."','".$row['sample_id']."')
//
//     ON DUPLICATE KEY UPDATE
//     vl_rejected_samples_release.id = ".$row['id'].",
//     vl_rejected_samples_release.released = ".$released.",
//     vl_rejected_samples_release.released_at = ".$released_at.",
//     vl_rejected_samples_release.comments = ".$comments.",
//     vl_rejected_samples_release.reject_released_by_id = ".$reject_released_by_id.",
//     vl_rejected_samples_release.sample_id = '".$row['sample_id']."'";
// print($b1);exit();
//     $res = $con2->query($b1);
//   }
// }
// $con2->close();
// echo "\n****ENDING MIGRATION #2 \n";

  //
  // $con1 = mysqli_connect('localhost', 'root', '68965','vl');
  // $con2 = mysqli_connect('localhost', 'root','68965', 'vl2');
  //
  //
  //
  // if ($con2->connect_error) {
  //   die("Connection failed: " . $con2->connect_error);
  // }
  //
  // echo "\n**********START MIGRATION #3********************\n";
  // $c="SELECT r.id,r.repeat_test,r.result1,r.result2,r.result3,r.result4,r.result5,r.result_numeric,r.result_alphanumeric,r.method,r.test_date,r.authorised_at,r.created_at,r.updated_at,r.authorised_by_id,r.sample_id,r.test_by_id,r.suppressed,r.authorised,r.result_upload_date FROM `vl_results` r WHERE id =  4785885";
  // $result = $con1->query($c);
  // $con1->close();
  //
  // if ($result->num_rows > 0) {
  //
  //   // output data of each row
  //   while($row = $result->fetch_assoc()) {
  //     //print_r($row);exit();
  //
  //     $row['repeat_test']?$repeat_test = $row['repeat_test']:$repeat_test = 'NULL';
  //     $row['result1']?$result1 = $row['result1']:$result1 = 'NULL';
  //     $row['result2']?$result2 = $row['result2']:$result2 = 'NULL';
  //     $row['result3']?$result3 = $row['result3']:$result3 = 'NULL';
  //     $row['result4']?$result4 = $row['result4']:$result4 = 'NULL';
  //     $row['result5']?$result5 = $row['result5']:$result5 = 'NULL';
  //     $row['result_numeric']?$result_numeric = "'".$row['result_numeric']."'":$result_numeric = 'NULL';
  //     $row['result_alphanumeric']?$result_alphanumeric = "'".$row['result_alphanumeric']."'":$result_alphanumeric = 'NULL';
  //     $row['method']?$method = "'".$row['method']."'":$method = 'NULL';
  //     $row['test_date']?$test_date = "'".$row['test_date']."'":$test_date = 'NULL';
  //     $row['authorised_at']?$authorised_at = "'".$row['authorised_at']."'":$authorised_at = 'NULL';
  //     $row['authorised_by_id']?$authorised_by_id = "'".$row['authorised_by_id']."'":$authorised_by_id = 'NULL';
  //     $row['test_by_id']?$test_by_id = "'".$row['test_by_id']."'":$test_by_id = 'NULL';
  //     $row['result_upload_date']?$result_upload_date = "'".$row['result_upload_date']."'":$result_upload_date = 'NULL';
  //
  //     // insertion into vl_results table of archived-DB
  //     $c1 = "INSERT INTO `vl_results` (id,repeat_test,result1,result2,result3,result4,result5,result_numeric,result_alphanumeric,method,test_date,authorised_at,created_at,updated_at,authorised_by_id,sample_id,test_by_id,suppressed,authorised,result_upload_date)
  //
  //     VALUES(".$row['id'].",".$repeat_test.",'".$row['result1']."','".$row['result2']."','".$row['result3']."','".$row['result4']."','".$row['result5']."',".$result_numeric.",".$result_alphanumeric.",".$method.",".$test_date.",".$authorised_at.",'".$row['created_at']."','".$row['updated_at']."',".$authorised_by_id.",'".$row['sample_id']."',".$test_by_id.", '".$row['suppressed']."','".$row['authorised']."',".$result_upload_date.")
  //
  //     ON DUPLICATE KEY UPDATE
  //     vl_results.id = ".$row['id'].",
  //     vl_results.repeat_test = ".$repeat_test.",
  //     vl_results.result1 =  '".$row['result1']."',
  //     vl_results.result2 =  '".$row['result2']."',
  //     vl_results.result3 =  '".$row['result3']."',
  //     vl_results.result4 =  '".$row['result4']."',
  //     vl_results.result5 =  '".$row['result5']."',
  //     vl_results.result_numeric = '".$result_numeric."',
  //     vl_results.result_alphanumeric = ".$result_alphanumeric.",
  //     vl_results.method = ".$method.",
  //     vl_results.test_date = ".$test_date.",
  //     vl_results.authorised_at = '".$authorised_at."',
  //     vl_results.created_at = '".$row['created_at']."',
  //     vl_results.updated_at = '".$row['updated_at']."',
  //     vl_results.authorised_by_id = ".$authorised_by_id.",
  //     vl_results.sample_id = '".$row['sample_id']."',
  //     vl_results.test_by_id = ".$test_by_id.",
  //     vl_results.suppressed = '".$row['suppressed']."',
  //     vl_results.authorised = '".$row['authorised']."',
  //     vl_results.result_upload_date = '".$result_upload_date."'";
  //     print_r($c1);exit();
  //     $res = $con2->query($c1);
  //   }
  // }
  // $con2->close();
  // echo "\n*********STOP MIGRATION #3************\n";


  //This works, need further scrutiny on NULL values. E.g some entries have 'N' while others need to have NULL.
    $con1 = new mysqli('localhost', 'root', '68965','vl');
    $con2 = new mysqli('localhost', 'root','68965', 'vl2');

    $f = "SELECT s.id,s.patient_unique_id, s.locator_category, s.locator_position, s.vl_sample_id, s.form_number,
    s.pregnant, s.anc_number, s.breast_feeding, s.active_tb_status, s.date_collected, s.date_received, s.treatment_initiation_date,
    s.sample_type, s.treatment_indication_other, s.last_test_date, s.last_value, s.last_sample_type, s.verified, s.in_worksheet,
    s.created_at, s.updated_at, s.arv_adherence_id, s.created_by_id, s.current_regimen_id, s.facility_id, s.failure_reason_id, s.patient_id,
    s.tb_treatment_phase_id, s.treatment_indication_id, s.treatment_line_id, s.updated_by_id, s.viral_load_testing_id, s.envelope_id, s.other_regimen,
    s.clinician_id, s.lab_tech_id, s.treatment_duration, s.treatment_care_approach, s.current_who_stage, s.is_study_sample, s.barcode
    FROM `vl_samples` s WHERE s.pregnant IS NOt NULL &&
     s.created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH ";
    $result = $con1->query($f);

    $con1->close();

    if ($con2->connect_error) {
      die("Connection failed: " . $con2->connect_error);
    }
    if ($result->num_rows > 0) {

      // output data of each row
      while($row = $result->fetch_assoc()) {

        $row['anc_number']?$anc_number = $row['anc_number']:$anc_number = 'NULL';
        $row['last_test_date']?$last_test_date = $row['last_test_date']:$last_test_date = 'NULL';
        $row['treatment_indication_other']?$treatment_indication_other = $row['treatment_indication_other']:$treatment_indication_other = 'NULL';
        $row['last_value']?$last_value = $row['last_value']:$last_value = 'NULL';
        $row['last_sample_type']?$last_sample_type = $row['last_sample_type']:$last_sample_type = 'NULL';
        $row['current_regimen_id']?$current_regimen_id = $row['current_regimen_id']:$current_regimen_id = 'NULL';
        $row['facility_id']?$facility_id = $row['facility_id']:$facility_id = 'NULL';
        $row['failure_reason_id']?$failure_reason_id = $row['failure_reason_id']:$failure_reason_id = 'NULL';
        $row['patient_id']?$patient_id = $row['patient_id']:$patient_id = 'NULL';
        $row['tb_treatment_phase_id']?$tb_treatment_phase_id = $row['tb_treatment_phase_id']:$tb_treatment_phase_id = 'NULL';
        $row['treatment_indication_id']?$treatment_indication_id = $row['treatment_indication_id']:$treatment_indication_id = 'NULL';
        $row['treatment_line_id']?$treatment_line_id = $row['treatment_line_id']:$treatment_line_id = 'NULL';
        $row['updated_by_id']?$updated_by_id = $row['updated_by_id']:$updated_by_id = 'NULL';
        $row['viral_load_testing_id']?$viral_load_testing_id = $row['viral_load_testing_id']:$viral_load_testing_id = 'NULL';
        $row['envelope_id']?$envelope_id = $row['envelope_id']:$envelope_id = 'NULL';
        $row['other_regimen']?$other_regimen = $row['other_regimen']:$other_regimen = 'NULL';
        $row['clinician_id']?$clinician_id = $row['clinician_id']:$clinician_id = 'NULL';
        $row['lab_tech_id']?$lab_tech_id = $row['lab_tech_id']:$lab_tech_id = 'NULL';
        $row['treatment_duration']?$treatment_duration = $row['treatment_duration']:$treatment_duration = 'NULL';
        $row['treatment_care_approach']?$treatment_care_approach = $row['treatment_care_approach']:$treatment_care_approach = 'NULL';
        $row['current_who_stage']?$current_who_stage = $row['current_who_stage']:$current_who_stage = 'NULL';
        $row['is_study_sample']?$is_study_sample = $row['is_study_sample']:$is_study_sample = 'NULL';
        $row['barcode']?$barcode = $row['barcode']:$barcode = 'NULL';
        $row['pregnant']?$pregnant = $row['pregnant']:$pregnant = 'NULL';
        $row['breast_feeding']?$breast_feeding = $row['breast_feeding']:$breast_feeding = 'NULL';


        // insertion into vl_results_dispatch table of archived-DB
        $f1 = "INSERT INTO `vl_samples` (id,patient_unique_id,locator_category,locator_position,vl_sample_id,form_number,pregnant,anc_number,breast_feeding,active_tb_status,date_collected,date_received,treatment_initiation_date,sample_type,treatment_indication_other,last_test_date,last_value,last_sample_type,verified,in_worksheet,created_at,updated_at,arv_adherence_id,created_by_id,current_regimen_id,facility_id,failure_reason_id,patient_id,tb_treatment_phase_id,treatment_indication_id,treatment_line_id,updated_by_id,viral_load_testing_id,envelope_id,other_regimen,clinician_id,lab_tech_id,treatment_duration,treatment_care_approach,current_who_stage,is_study_sample,barcode)

        VALUES(".$row['id'].",
        '".$row['patient_unique_id']."',
        '".$row['locator_category']."',
        '".$row['locator_position']."',
        '".$row['vl_sample_id']."',
        '".$row['form_number']."',
          '".$row['pregnant']."',
        '".$row['anc_number']."',
        '".$row['breast_feeding']."',
        '".$row['active_tb_status']."',
        '".$row['date_collected']."',
        '".$row['date_received']."',
        '".$row['treatment_initiation_date']."',
        '".$row['sample_type']."',
        ".$treatment_indication_other.",
        ".$last_test_date.",
        ".$last_value.",
        ".$last_sample_type.",
        '".$row['verified']."',
        '".$row['in_worksheet']."',
        '".$row['created_at']."',
        '".$row['updated_at']."',
        '".$row['arv_adherence_id']."',
        '".$row['created_by_id']."',
        '".$row['current_regimen_id']."',
        '".$row['facility_id']."',
        '".$row['failure_reason_id']."',
        '".$row['patient_id']."',
        ".$tb_treatment_phase_id.",
        ".$treatment_indication_id.",
        '".$row['treatment_line_id']."',
          ".$updated_by_id.",
        '".$row['viral_load_testing_id']."',
        '".$row['envelope_id']."',
        ".$other_regimen.",
        ".$clinician_id.",
        ".$lab_tech_id.",
        ".$treatment_duration.",
        ".$treatment_care_approach.",
        ".$current_who_stage.",
        '".$row['is_study_sample']."',
        ".$barcode.")

        ON DUPLICATE KEY UPDATE
        vl_samples.id = ".$row['id'].",
        `vl_samples`.patient_unique_id = '".$row['patient_unique_id']."',
        `vl_samples`.locator_category = '".$row['locator_category']."',
        `vl_samples`.locator_position = '".$row['locator_position']."',
        `vl_samples`.vl_sample_id = ".$row['vl_sample_id'].",
        `vl_samples`.form_number = ".$row['form_number'].",
        `vl_samples`.pregnant = '".$row['pregnant']."',
        `vl_samples`.anc_number = ".$anc_number.",
        `vl_samples`.breast_feeding = '".$row['breast_feeding']."',
        `vl_samples`.active_tb_status = '".$row['active_tb_status']."',
        `vl_samples`.date_collected = '".$row['date_collected']."',
        `vl_samples`.date_received = '".$row['date_received']."',
        `vl_samples`.treatment_initiation_date = '".$row['treatment_initiation_date']."',
        `vl_samples`.sample_type = '".$row['sample_type']."',
        `vl_samples`.treatment_indication_other = ".$treatment_indication_other.",
        `vl_samples`.last_test_date = ".$last_test_date.",
        `vl_samples`.last_value = ".$last_value.",
        `vl_samples`.last_sample_type = ".$last_sample_type.",
        `vl_samples`.verified = ".$row['verified'].",
        `vl_samples`.in_worksheet = ".$row['in_worksheet'].",
        `vl_samples`.created_at = '".$row['created_at']."',
        `vl_samples`.updated_at = '".$row['updated_at']."',
        `vl_samples`.arv_adherence_id = ".$row['arv_adherence_id'].",
        `vl_samples`.created_by_id = ".$row['created_by_id'].",
        `vl_samples`.current_regimen_id = ".$current_regimen_id.",
        `vl_samples`.facility_id = ".$facility_id.",
        `vl_samples`.failure_reason_id = ".$failure_reason_id.",
        `vl_samples`.patient_id = ".$patient_id.",
        `vl_samples`.tb_treatment_phase_id = ".$tb_treatment_phase_id.",
        `vl_samples`.treatment_indication_id = ".$treatment_indication_id.",
        `vl_samples`.treatment_line_id = ".$treatment_line_id.",
        `vl_samples`.updated_by_id = ".$updated_by_id.",
        `vl_samples`.viral_load_testing_id = ".$viral_load_testing_id.",
        `vl_samples`.envelope_id = ".$envelope_id.",
        `vl_samples`.other_regimen = ".$other_regimen.",
        `vl_samples`.clinician_id = ".$clinician_id.",
        `vl_samples`.lab_tech_id = ".$lab_tech_id.",
        `vl_samples`.treatment_duration = ".$treatment_duration.",
        `vl_samples`.treatment_care_approach = ".$treatment_care_approach.",
        `vl_samples`.current_who_stage = ".$current_who_stage.",
        `vl_samples`.is_study_sample = ".$row['is_study_sample'].",
        `vl_samples`.barcode = ".$barcode."";

        print($f1); exit();
        $res = $con2->query($f1);
      }
    }
    $con2->close();
    echo "\n*********END MIGRATION #6************\n";

  ?>