<?php
include('includes/config/config.php');
include('includes/functions.php');
//$conn = mysqli_connect( 'localhost', 'root', '', 'phpsamples' );
require_once('excel/vendor/php-excel-reader/excel_reader2.php');
require_once('excel/vendor/SpreadsheetReader.php');

$inter_procurement_id = array();

$response_array = array();
$response_mssages = '';

begin();

$allowedFileType = ['application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

if (in_array($_FILES['file']['type'], $allowedFileType)) {

    $targetPath = 'excel/uploads/' . $_FILES['file']['name'];
    move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

    $Reader = new SpreadsheetReader($targetPath);

    $sheetCount = count($Reader->sheets());

    for ($i = 0; $i < 1; $i++) {

        $Reader->ChangeSheet($i);
        $j = 0;
        foreach ($Reader as $Row) {
            if ($j == 0) {
                $j = 1;
                continue;
            }

            error_log('again here');
            $csr_program_name = '';
            if (isset($Row[0])) {
                $csr_program_name = mysqli_real_escape_string($conn, $Row[0]);
            }

            $program_details = '';
            if (isset($Row[1])) {
                $program_details = mysqli_real_escape_string($conn, $Row[1]);
            }
            $program_location = '';
            if (isset($Row[2])) {
                $program_location = mysqli_real_escape_string($conn, $Row[2]);
            }
            $start_date = '';
            if (isset($Row[3])) {
                $start_date = mysqli_real_escape_string($conn, $Row[3]);
            }
            $start_time = '';
            if (isset($Row[4])) {
                $start_time = mysqli_real_escape_string($conn, $Row[4]);
            }
            $end_date = '';
            if (isset($Row[5])) {
                $end_date = mysqli_real_escape_string($conn, $Row[5]);
            }

            $end_time = '';
            if (isset($Row[6])) {
                $end_time = mysqli_real_escape_string($conn, $Row[6]);
            }

            $coordinator_name = '';
            if (isset($Row[7])) {
                $coordinator_name = mysqli_real_escape_string($conn, $Row[7]);
            }

            $sub_coordinator_name = '';
            if (isset($Row[8])) {
                $sub_coordinator_name = mysqli_real_escape_string($conn, $Row[8]);
            }
            $total_participate = '';
            if (isset($Row[9])) {
                $total_participate = mysqli_real_escape_string($conn, $Row[9]);
            }
            $program_remarks = '';
            if (isset($Row[10])) {
                $program_remarks = mysqli_real_escape_string($conn, $Row[10]);
            }

            $participate_type = '';
            if (isset($Row[11])) {
                $participate_type = mysqli_real_escape_string($conn, $Row[11]);
            }

            $participate_name = '';
            if (isset($Row[12])) {
                $participate_name = mysqli_real_escape_string($conn, $Row[12]);
            }

            $p_address = '';
            if (isset($Row[13])) {
                $p_address = mysqli_real_escape_string($conn, $Row[13]);
            }

            $contact = '';
            if (isset($Row[14])) {
                $contact = mysqli_real_escape_string($conn, $Row[14]);
            }

            $vitals_checked = '';
            if (isset($Row[15])) {
                $vitals_checked = mysqli_real_escape_string($conn, $Row[15]);
            }

            $item_given = '';
            if (isset($Row[16])) {
                $item_given = mysqli_real_escape_string($conn, $Row[16]);
            }

            $total_cost = '';
            if (isset($Row[17])) {
                $total_cost = mysqli_real_escape_string($conn, $Row[17]);
            }

            $p_remarks = '';
            if (isset($Row[18])) {
                $p_remarks = mysqli_real_escape_string($conn, $Row[18]);
            }

            $ohc_id = $_SESSION['current_ohcttype'];

            if (!empty($csr_program_name) && !empty($start_date) && !empty($program_location) && !empty($participate_name) && !empty($coordinator_name)) {

                $csr_id_sql = "SELECT temp_csr_id FROM temporary_csr_program WHERE program_name='" . $csr_program_name . "' AND coordinator_name='" . $coordinator_name . "' AND date_format(date(start_date_time),'%d-%m-%Y') ='" . $start_date . "' AND date_format(date(end_date_time),'%d-%m-%Y') ='" . $end_date . "'";

                $result_id_sql = mysqli_query($conn, $csr_id_sql);
                $row_id = mysqli_fetch_array($result_id_sql);
                $temp_csr_id = $row_id['temp_csr_id'];

                error_log('TEMP CSR ID::' . $temp_csr_id);

                $initquery = '';
                $endquery = '';

                if ($temp_csr_id != '' && $temp_csr_id != null) {
                    $initquery = 'UPDATE temporary_csr_program set  ';
                    $endquery = " WHERE temp_csr_id = '" . $temp_csr_id . "'";
                    $update_counter++;
                } else {
                    $initquery = 'INSERT INTO temporary_csr_program set ';
                    $endquery = '';
                    $insert_counter++;
                }
                $StartDateTime = date('Y-m-d H:i:s', strtotime("$start_date $start_time"));
                $EndDateTime = date('Y-m-d H:i:s', strtotime("$end_date $end_time"));

                $query_csr = $initquery . "  start_date_time = '" . $StartDateTime . "', end_date_time = '" . $EndDateTime . "',total_participate = '$total_participate',program_name = '$csr_program_name', program_details='$program_details', location='$program_location', coordinator_name='$coordinator_name', sub_coordinator_name='$sub_coordinator_name', remarks ='$program_remarks' $endquery ";
                error_log('CSR QUERY::' . $query_csr);
                $result1 = mysqli_query($conn, $query_csr);
                if ($temp_csr_id == null or $temp_csr_id == '')
                    $temp_csr_id = mysqli_insert_id($conn);

                array_push($inter_procurement_id, $temp_csr_id);

                $temp_participate_csr_program_id = getTableFieldValue('temporary_participate_csr_program', 'temp_participate_csr_program_id', 'temp_csr_id', $temp_csr_id, 'participate_name', $participate_name, 'contact', $contact);
                error_log('procurement item id ' . $temp_participate_csr_program_id);
                $initquery_item = '';
                $final_item = '';
                $qty = 0.0;
                if ($temp_participate_csr_program_id != null && $temp_participate_csr_program_id != '') {
                    $query2 = "UPDATE temporary_participate_csr_program set participate_type = '$participate_type', participate_name='" . $participate_name . "',`address`='" . $p_address . "' , contact = '$contact' , vitals_check = '$vitals_checked' , total_cost = '$total_cost' , remarks = '$p_remarks' where temp_participate_csr_program_id=$temp_participate_csr_program_id";
                } else {
                    $query2 = "INSERT into temporary_participate_csr_program set participate_type = '$participate_type',temp_csr_id=$temp_csr_id,participate_name='$participate_name',`address`='$p_address',`contact`='" . $contact . "',vitals_check='" . $vitals_checked . "',item_id='" . $item_given . "',total_cost='" . $total_cost . "',	remarks='" . $p_remarks . "'";
                }
                error_log('UPLOAD PROCUREMENT::' . $query2);
                $result2 = mysqli_query($conn, $query2);
            }
        }
    }
}
$type = '';
if (!empty($result1)  &&  !empty($result2)) {
    $ids = array_unique($inter_procurement_id);
    error_log('iidd' . max(array_keys($ids)) . 'iidd' . print_r($ids, true));
    for (
        $i = 0;
        $i <= max(array_keys($ids));
        $i++
    ) {
        if ($ids[$i] != null || $ids[$i] != '' || $ids[$i] != 0) {
            $type .= $ids[$i] . ',';
        }
    }
    //$message = 'Excel Data Imported Into the Database';
} else {
    $type = 'error';
    //$message = 'Problem In Importing Excel Data';
}
commit();
error_log('datas====' . $type);
echo json_encode($type);