<?php
require_once($_SERVER['DOCUMENT_ROOT'] . "/aipma2025/include/config.php");
require_once(INCLUDE_PATH . "startup.php");
$alltables = new AllTables();

define('MAX_STRING', 8);
define('FIX_COUNTRY', true);                 
define('TOP_10_INDIA_EXPORTS_VIEW','all_hsn_exports_india_pivot_new');

$labels = [];
$year2021 = [];
$year2022 = [];
$year2023 = [];
$totalValues = [];


if (isset($_REQUEST['hsn_code']) || isset($_REQUEST['chapeter_code'])) 
{
    $to_country          = sanitize($_REQUEST['to_country'], SQL + HTML);
    $chaptercode          = intval($_REQUEST['chapeter_code']);
    
    $where = [];
    empty($to_country)          ? null : $where['import_to_country'] = $to_country;
    empty($chaptercode)         ? null : $where['Chapter'] = $chaptercode;


    $i = 0;
    $wherecondition = '';
    $newwherecondition = '';
    $imagearray = array();

    foreach ($where as $key => $value) 
    {
        if ($i == 0) 
        {
            $i++;        
            $wherecondition .= " WHERE $key = '$value' ";
        } 
        else 
        {
            $wherecondition .= " AND $key = '$value' ";
        }
    }



    if ($to_country) {
        $searchText = " Import in $to_country,";
    }

    if ($chaptercode) {
        $searchText .= " Chapter :  $chaptercode,";
    }

   
    $searchText = rtrim($searchText, ', ');


    if ($wherecondition)                                                                           
    {
        $searchsql = "SELECT * FROM " . TOP_10_INDIA_EXPORTS_VIEW . " $wherecondition limit 0,10";
    }

    $searchdata = $alltables->getInfo("", $searchsql);
    $searchdatacount = count($searchdata);


    if ($searchdatacount) 
    {
        
        $rowheader .=" <tr>
                 
                      <th   class='align-middle'>Sr. No.</th>
                      <th   class='align-middle'>HSN Product Code</th>
                      <th   class='align-middle'>Product Label</th>
                      <th    class='align-middle'>Country</th>
                      <th  class='hsn-header-blue'>Value in US $ (2021)</th>
                      <th  class='hsn-header-yellow'>Value in US $ (2022)</th>
                      <th  class='hsn-header-green'>Value in US $ (2023)</th>
                    </tr>";
        
        $imagearray = getHsnCodeImage($chaptercode, $hsn_imagecode);

        for ($i = 0; $i < $searchdatacount; $i++) 
        {
            $hsncode              = $searchdata[$i]["HSNcode"];
            $hsncode_name              = $searchdata[$i]["name"];
            $chapter              = $searchdata[$i]["chapter"];
            $import_to_country  = $searchdata[$i]["import_to_country"];
            
            $import_year_2021   = $searchdata[$i]["import_year2021"];
            $import_value_2021  = $searchdata[$i]["import_value2021"];


            $import_year_2022   = $searchdata[$i]["import_year2022"];
            $import_value_2022  = $searchdata[$i]["import_value2022"];
            
            $import_year_2023   = $searchdata[$i]["import_year2023"];
            $import_value_2023  = $searchdata[$i]["import_value2023"];
        
        
            $rows .= "<tr>
            
                        <td>".($i+1)."</td>
                        <td>$hsncode</td>
                               
                        <td>$hsncode_name</td>
                        
                        <td>$import_to_country</td>
                        
                        <td  class='hsn-header-blue'> ".number_format($import_value_2021, 0)."</td>
                        
                        <td class='hsn-header-yellow'>". number_format($import_value_2022, 0)."</td>
                        
                        <td class='hsn-header-green'>". number_format($import_value_2023, 0)."</td>
                    </tr>";
                    
                    
                    
             // Add data for Chart.js
            $labels[]   = $hsncode;
            $year2021[] = $import_value_2021;
            $year2022[] = $import_value_2022;
            $year2023[] = $import_value_2023;
        }
        
        
        // Output Chart.js
   $javascriptCode = '
<script>
new Chart(document.getElementById("topHSNCodeBarChart"), {
    type: "bar",
    data: {
        labels: ' . json_encode($labels) . ',
        datasets: [
            {
                label: "Value Year 2021",
                data: ' . json_encode($year2021) . ',
                backgroundColor: "rgba(75, 192, 192, 0.7)",
                borderColor: "rgba(75, 192, 192, 1)",
                borderWidth: 1
            },
            {
                label: "Value Year 2022",
                data: ' . json_encode($year2022) . ',
                backgroundColor: "rgba(255, 99, 132, 0.7)",
                borderColor: "rgba(255, 99, 132, 1)",
                borderWidth: 1
            },
            {
                label: "Value Year 2023",
                data: ' . json_encode($year2023) . ',
                backgroundColor: "rgba(54, 162, 235, 0.7)",
                borderColor: "rgba(54, 162, 235, 1)",
                borderWidth: 1
            },
            {
                label: "Extra Series",
                data: ' . json_encode($extraData ?? []) . ',
                backgroundColor: "rgba(153, 102, 255, 0.7)",
                borderColor: "rgba(153, 102, 255, 1)",
                borderWidth: 1
            }
        ]
    },
    options: {
        responsive: true,
        plugins: {
            title: {
                display: true,
                text: "Values by HSNCodes for ' . htmlspecialchars($to_country) . ' (2021-2023)"
            }
        },
        scales: {
            x: {
                title: {
                    display: true,
                    text: "HSNCodes"
                }
            },
            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: "Import Value (USD Thousands)"
                },
                ticks: {
                    callback: function(value) {
                        return "$" + value.toLocaleString("en-US");
                    }
                }
            }
        }
    }
});
</script>
';


  
    }
}
                                     
                                     
                                     
                                     
                                  
                                     
                                     
                                     

require_once(REQUIRED_PATH . 'header.php');
require_once(VIEWS_PATH . "hsn-code-view1.html");
require_once(REQUIRED_PATH . 'footer.php');

function getHsnCodeImage($chapter = '', $hsncode = '')
{  
    global $alltables;

    if ($chapter && $hsncode) {
        $wherecondition = "WHERE hscode = '$hsncode' && chapterno = '$chapter'";
    } else if ($chapter) {
        $wherecondition = " WHERE chapterno = '$chapter'";
    } else {
        $wherecondition = "WHERE hscode = '$hsncode'";
    }

    $sqlhsncodeimg = "SELECT chapterno,image FROM " . HSNCODE_IMAGE_TABLE . " $wherecondition LIMIT 0,4";


    $hsncodeImgData = $alltables->getInfo("", $sqlhsncodeimg);
    $hsncodeCount = count($hsncodeImgData);

    $base = DOC_ROOT . 'assets/img/hsn';

    if ($hsncodeCount) {
        for ($i = 0; $i < $hsncodeCount; $i++) {
            $chapterno = $hsncodeImgData[$i]['chapterno'];
            $imageval = $hsncodeImgData[$i]['image'];

            $imageurl =   $base . "/" . $chapterno . "/" . $imageval;

            $imagearray[$i] = $imageurl;
        }
    }

    return $imagearray;
}
function getHSNCodeChapter($selectChapterCode = '')
{
    $alltables = new AllTables();
    $sql = "SELECT DISTINCT Chapter FROM " . MAIN_IMPORT_TABLE . " WHERE Chapter > 0  ORDER BY Chapter ASC";
    $data = $alltables->getInfo("", $sql);
    $count = is_array($data) ? count($data) : 0;

    if ($count) {

        $options .= "<option value='all'> Select Chapter</option>";

        for ($i = 0; $i < $count; $i++) {
            $value = $data[$i]["Chapter"];

            if ($value == $selectChapterCode && $selectChapterCode > 0) {
                $options .= "<option value='" . $value . "' data-search='" . $value . "' selected>$value</option>";
            } else {
                $options .= "<option value='" . $value . "' data-search='" . $value . "'>$value</option>";
            }
        }
    }
    return $options;
}
function getHSNCode($chapter = '', $selectCode = '')
{
    $alltables = new AllTables();
    $wherecondition = '';
    if ($chapter) {
        $wherecondition = "WHERE Chapter = '$chapter' AND CHAR_LENGTH(HSNcode) > 6";
    } else {
        $wherecondition = "WHERE CHAR_LENGTH(HSNcode) > 6";
    }

    $sqlhsncode = "SELECT DISTINCT(HSNcode), name  FROM " . MAIN_IMPORT_TABLE . " $wherecondition ORDER BY HSNcode ASC";
    $hsncodedata = $alltables->getInfo("", $sqlhsncode);
    $hsncodecount = count($hsncodedata);
    if ($hsncodecount) {
        $hsncodeoptions .= "<option value=''> Select HSNCode </option>";

        for ($i = 0; $i < $hsncodecount; $i++) {
            $hsncode = $hsncodedata[$i]["HSNcode"];
            $name = $hsncodedata[$i]["name"];
            $value = $hsncode . "-" . $name;
            $code = $hsncode;

            if ($code == $selectCode && $selectCode > 0) {
                $hsncodeoptions .= "<option value='" . $code . "' data-search='" . $value . "' selected>$value</option>";
            } else {
                $hsncodeoptions .= "<option value='" . $code . "' data-search='" . $value . "'>$value</option>";
            }
        }
    }
    return $hsncodeoptions;
}
function getFromCountry($fromCountry = [])
{
    $alltables = new AllTables();
    $sqlimportfrom = "SELECT DISTINCT  import_from_country FROM " . MAIN_IMPORT_TABLE . ";";
    $sqlimportfromdata = $alltables->getInfo("", $sqlimportfrom);
    $importfromdatacount = count($sqlimportfromdata);
    if ($importfromdatacount) {
        $importfromoptions .= "<option value=''> Select Country</option>";

        if (!is_array($fromCountry)) {
            $fromCountry = []; // force it to be an array
        }
        for ($i = 0; $i < $importfromdatacount; $i++) {
            $importfrom = $sqlimportfromdata[$i]["import_from_country"];
            $selected = in_array($importfrom, $fromCountry) ? "selected" : "";
            $importfromoptions .= "<option value='$importfrom' $selected>$importfrom</option>";
        }
    }
    return $importfromoptions;
}
function getToCountry($to_country = '')
{
    $alltables = new AllTables();
    // $sqlimportto = "SELECT DISTINCT import_to_country FROM " .      MAIN_IMPORT_TABLE. " where import_to_country = 'USA'";  
    $sqlimportto = "SELECT DISTINCT import_to_country FROM " . MAIN_IMPORT_TABLE;
    $sqlimporttodata = $alltables->getInfo("", $sqlimportto);
    $sqlimporttodatacount = count($sqlimporttodata);

    if ($sqlimporttodatacount) {
        for ($i = 0; $i < $sqlimporttodatacount; $i++) {
            $importto = $sqlimporttodata[$i]["import_to_country"];

            if ($importto == $to_country) {
                $importtoptions .= "<option value='" . $importto . "' selected>$importto</option>";
            } else {

                $importtoptions .= "<option value='" . $importto . "'>$importto</option>";
            }
        }
    }
    return $importtoptions;
}


function getHSCodeName($hsncodename)
{
    $alltables = new AllTables();
    $value = '';
    $sqlhsncode = "SELECT DISTINCT(HSNcode), name FROM " . MAIN_IMPORT_TABLE . " WHERE HSNcode = '$hsncodename'";
    $hsncodedata = $alltables->getInfo("", $sqlhsncode);


    if (is_array($hsncodedata)) {
        $hsncode = $hsncodedata[0]["HSNcode"];
        $name = $hsncodedata[0]["name"];
        $value = $hsncode . "-" . $name;
    }

    return $value;
}
