<?php
function getTdUid($elm){
    if($elm["articleNumber"])
        return $elm["dataSupplierId"]."|".str_replace(" ", "", $elm["articleNumber"])."|".implode(",",array_column($elm["genericArticles"],'genericArticleId'));
    else return "";
}
function getTdUid2($elm){
    if($elm["articleNumber"])
        return $elm["dataSupplierId"]."|".str_replace(" ", "", $elm["articleNumber"]);
    else return "";
}
function transformTree($treeArray, $parentId = null)
{
    $output = [];
    foreach ($treeArray as $node) {
        if ($node['parentNodeId'] == $parentId) {
            $children = transformTree($treeArray, $node['assemblyGroupNodeId']);
            if ($children) {
                $node['children'] = $children;
            }
            $output[] = $node;
            unset($node);
        }
    }
    return $output;
}
function parseJsonAsHTMLTree($jsn,$id,$w,$z){
    $result = "";
    if($jsn["assemblyGroupName"]){
                      
        if($jsn["children"]){
            $result .="<li><i class=\"fas fa-angle-right rotate ifas\"></i><span class=\"spfas\"><a class=\"ahref\" id=\"$id-".$jsn["assemblyGroupNodeId"]."\" href=\"javascript:getpcsloz($id,".$jsn["assemblyGroupNodeId"].",'$w','$z')\">".$jsn["assemblyGroupName"]."</a></span><ul class=\"nested\">";
            foreach($jsn["children"] as $child)
                $result .= parseJsonAsHTMLTree($child,$id,$w,$z); 
            $result .="</ul>";
        }
        else{
            $result .= "<li><a class=\"ahref\" id=\"$id-".$jsn["assemblyGroupNodeId"]."\" href=\"javascript:getpcsloz($id,".$jsn["assemblyGroupNodeId"].",'$w','$z')\">".$jsn["assemblyGroupName"]."</a>";  
        }       
        $result .= "</li>";
    }
    return $result;    
}
function parseJsonAsHTMLTree1($jsn,$id,$w){
    $result = "";
    if($jsn["assemblyGroupName"]){
                      
        if($jsn["children"]){
            $result .="<li><i class=\"fas fa-angle-right rotate ifas\"></i><span class=\"spfas\"><a class=\"ahref\" id=\"$id-".$jsn["assemblyGroupNodeId"]."\" href=\"javascript:getpcsloc($id,".$jsn["assemblyGroupNodeId"].",'$w')\">".$jsn["assemblyGroupName"]."</a></span><ul class=\"nested\">";
            foreach($jsn["children"] as $child)
                $result .= parseJsonAsHTMLTree1($child,$id,$w); 
            $result .="</ul>";
        }
        else{
            $result .= "<li><a class=\"ahref\" id=\"$id-".$jsn["assemblyGroupNodeId"]."\" href=\"javascript:getpcsloc($id,".$jsn["assemblyGroupNodeId"].",'$w')\">".$jsn["assemblyGroupName"]."</a>";  
        }       
        $result .= "</li>";
    }
    return $result;    
}
function graphquery($url,$raw,$method="POST"){
    $curl = curl_init();
    curl_setopt_array($curl, array(
        CURLOPT_URL => 'https://webservice.tecalliance.services/rmi-proxy/rest/Graphics/'.$url,
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_CUSTOMREQUEST => $method,
        CURLOPT_POSTFIELDS => $raw,
        CURLOPT_HTTPHEADER => array(
            'X-Api-Key: '.TecAll2::getApiKey(),
            'Content-Type: application/json'
        ),
    ));
    $response = curl_exec($curl);
    curl_close($curl);
    return $response;
}
api::getCountries(function ($data){
    return json_decode('[{"countryCode":"TN","countryName":"Tunisie","usage":7},{"countryCode":"FR","countryName":"France","usage":7},{"countryCode":"CN","countryName":"Chine","usage":7}]',true);
    return TecAll2::countries("fr")["data"]["array"]??(json_decode('[{"countryCode":"TN","countryName":"Tunisie","usage":7},{"countryCode":"FR","countryName":"France","usage":7},{"countryCode":"CN","countryName":"Chine","usage":7}]',true));
});
api::parcformsteq(function ($data) {
    $result=file_get_contents("https://b2b-steq.com/web/?api=getv1api&MEDELE_TEC=".$data['i']);
	$pre=json_decode($result)->master;
    $ww="";
    $rr=0;
    $zz=array();
    $dw=array("CHASSIS","genre","CARROSSERIE","DMC","ENERGIE","Puissance","GOV","GAUCHE","CD_SERIE","DROIT_MIL");
    for($i=1;$i<count($dw);$i++){
        $zz[]=$dw[$i]."=VALUES(".$dw[$i].")";
    }
    $upd=implode(",",$zz);
	foreach($pre as $pr){
        $rr++;
        $vls=array();
        foreach ($pr as $key => $value) {
            if($value==null){
                $vls[]='NULL';
            }
            else{
                $vls[]="'".trim(str_replace("'","\'",str_replace("\'","'",$value)))."'";
            }
        }
        $ww.="(".implode(",",$vls)."),";
        if($rr%1000==0){
            $ww=rtrim($ww,",");
            db::query("INSERT INTO API__MAT(".implode(",",$dw).") VALUES $ww ON DUPLICATE KEY UPDATE $upd");
            $ww="";
        }
    }
    $ww=rtrim($ww,",");
    if($ww!=""){
        db::query("INSERT INTO API__MAT(".implode(",",$dw).") VALUES $ww ON DUPLICATE KEY UPDATE $upd");
    }
    return $rr;
});
api::gtSelect(function ($data) {
    $rr=array();
    foreach($data['from'] as $key => $value){
        $x=$value['tab'];
        $y=$value['col'];
        $rows = db::table("SELECT $y FROM $x WHERE $y is not NULL and $y!='' GROUP BY $y ORDER BY COUNT(*) DESC");
        $rr[$key] = "";
        foreach ($rows as $row) {
            $rr[$key] .= '<option value="' . $row[$y] . '">' . $row[$y] . '</option>';
        }
    }
    return $rr;
});
api::getDBS(function ($data) {
    return db::table("SELECT id,titre FROM API__DBS WHERE etat=1");
});
api::getb2bs(function ($data) {
   return db::table("SELECT titre,dbname,type,url FROM API__DBS WHERE etat=1 and public=1");  
});
api::cgetclient(function ($data) {
    return db::table("SELECT id,titre FROM (select id,titre FROM ¤CLIENT UNION select id,nom as titre FROM ¤USERS) u");
});
api::allff(function ($data) {
    return get_included_files();
});
api::getvinx(function ($data) {
    $vin = strtoupper($data["vin"]);
    return vinx::query($vin);
});
api::getvin(function ($data) {
    $vin = strtoupper($data["vin"]);
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $res2 = TecAll2::vin($vin,$country);
    /* $vinx = vinx::query($vin);
    foreach($vinx as &$vx){
        ksort($vx);
    } */
    $r=db::row("SELECT * FROM API__MAT WHERE CHASSIS LIKE '$vin'");
    $w=null;
    $z=null;
    if($r["CD_SERIE"]=="TU")$w=$r["GAUCHE"]." ".$r["CD_SERIE"]." ".$r["DROIT_MIL"];
    else if($r["CD_SERIE"]=="RS")$w=$r["CD_SERIE"]." ".$r["DROIT_MIL"];
    if($r["DMC"])$z=$r["DMC"];
    return [
        "res" => [
        "imma" => $w,
        "dmc" => $z,
        "vindv" => vindv::query($vin),
        "tecall2" => $res2["data"],
        //"vinx" => $vinx,
        "partsouq" => null //partsouq::search($vin)
    ]];
});
api::getgraph(function ($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $raw=array();
    foreach ($data['data'] as $key => $value) {
        $raw[]="\"$key\":\"$value\"";
    }
    $r=implode(",",$raw);
    if($data["id"]){
        $zz=null;
        if($data['data']["subGroupId"]){
            $list=graphquery('GenArtManualList','{"BodyQualColId":"'.($data['cap']??0).'","CountryCode":"'.$country.'","LanguageCode":"fr","TypeId":"'.$data["id"].'"}');
            $list=json_decode($list);
            foreach($list as $l){
                foreach($l->SubGroups as $s){
                    if($s->SubGroupId==$data['data']["subGroupId"]){
                        $raw[]='"subGroupQualCol":"'.$s->SubGroupQualColId.'"';
                        $r=implode(",",$raw);
                        $zz=$s->ItemMps;
                        break 2;
                    }
                }
            }
        }
        $graph=graphquery($data['req'],'{"CountryCode":"'.$country.'","LanguageCode":"fr","TypeId":"'.$data["id"].'","bodyQualColId":"'.($data['cap']??0).'"'.($r?",$r":"").'}');
        preg_match('/\<title\>(.*)\<\/title\>/i', substr($graph,400,200), $matches);
        $nav=$data['nav'];
        if($matches[1]){
            if($data['data']["mainGroupId"]){
                $nav=['t1'=>$data['nav']['t1'],'t2'=>['id'=>"h".$data['data']["mainGroupId"],'title'=>$matches[1]]];
            }
            else if($data['data']["subGroupId"]){
                $nav=['t1'=>$data['nav']['t1'],'t2'=>$data['nav']['t2'],'t3'=>['id'=>"s".$data['data']["subGroupId"],'title'=>$matches[1]]];
            }
            
        }
        return ['nav'=>$nav,'list'=>$zz,'graph'=>$graph];
    }
    return  ['nav'=>null,'list'=>null,'graph'=>404];    
});
api::getTree2(function ($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $rmiTypeId=TecAll2::getCarx($data["id"],$country)['data']['array'][0]['vehicleDetails']['rmiTypeId']??null;
    if($rmiTypeId){
        $has=graphquery('VehicleHasGraphics?typeId='.$rmiTypeId.'&countryCode='.$country.'','','GET');
        if($has=="true"||true){
            $cap=graphquery('BodiesForGraphics?typeId='.$rmiTypeId.'&countryCode='.$country.'&languageCode=fr','','GET');
            $cap=json_decode($cap);
            $capid=$cap[0]->QualColId;
            if($data['cap']){
                foreach($cap as $ca){
                    if($data['cap']==$ca->QualColId)
                        $capid=$ca->QualColId;
                }
            }
            $graph=graphquery('GenArtVehicleHtml','{"CountryCode":"'.$country.'","LanguageCode":"fr","TypeId":"'.$rmiTypeId.'","bodyQualColId":"'.$capid.'"}');
            preg_match('/\<title\>(.*)\<\/title\>/i', substr($graph,400,200), $matches);
            $nav=null;
            if($matches[1]){
                $nav=['t1'=>['title'=>$matches[1]]];
            }
            return ['res'=>['nav'=>$nav,'capid'=>$capid,'cap'=>$cap,'rmiTypeId'=>$rmiTypeId,'graph'=>$graph]];
        }
        else return ['res'=>['rmiTypeId'=>$rmiTypeId,'graph'=>404]];
    }
    return ['res'=>['graph'=>404]];
});
api::getTree1(function ($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $row=db::row("SELECT carType FROM API__VEH_$country WHERE carId=".$data["id"]);
    $s=TecAll2::searchTree($data["id"],($row["carType"]?$row["carType"]:"P"),$country);
    $ww=array_replace([], (current($s["data"])?current($s["data"]):[]));
    $ee=transformTree($ww);
    $ch="<input id=\"srh\" class=\"form-control avs\" placeholder=\"Que cherchez-vous?\" style=\"margin-bottom: 10px;width: 260px;\"><div class=\"treeview\"><ul class=\"ful\">";
    foreach($ee as $e){
        $ch.=parseJsonAsHTMLTree1($e,$data["id"],"search");
    }
    $ch.="</ul></div>";
    return ["res" => [
        "car" => $data["id"],
        "to" => "search",
        "ee" => $ww,
        "ww" => $ee,
        "tree" => $ch
    ]];
});
api::getTree(function ($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $row=db::row("SELECT carType FROM API__VEH_$country WHERE carId=".$data["id"]);
    $s=TecAll2::searchTree($data["id"],($row["carType"]?$row["carType"]:"P"),$country);
    $ww=array_replace([], (current($s["data"])?current($s["data"]):[]));
    $ee=transformTree($ww);
    $ch="<input id=\"srh\" class=\"form-control avs\" placeholder=\"Que cherchez-vous?\" style=\"margin-bottom: 10px;width: 260px;\"><div class=\"treeview\"><ul class=\"ful\">";
    foreach($ee as $e){
        $ch.=parseJsonAsHTMLTree($e,$data["id"],"searchdb",$data["db"]);
    }
    $ch.="</ul></div>";
    return ["res" => [
        "car" => $data["id"],
        "to" => "searchdb",
        "ee" => $ww,
        "ww" => $ee,
        "tree" => $ch
    ]];
});
api::getCarx(function ($data) {
    $country="TN";
    if($data["country"]) $country=$data["country"];
    return TecAll2::getCarx($data["carId"],$country);
});
api::getCars(function ($data) {
    $country="TN";
    if($data["country"]) $country=$data["country"];
    if(!isset($data["mfrId"])&&isset($data["manuId"]))$data["mfrId"]=$data["manuId"];
    return TecAll2::getCars($data["articleId"], $data["mfrId"],$country);
});
api::getpiece1(function ($data) {
    $qx=explode("¤",$data['id']);
    $country="TN";
    if($data["country"]) $country=$data["country"];
    if(count($qx)==2) $res2 = TecAll2::search($qx[0],"",$qx[1],1,0,$country);
    else $res2 = TecAll2::searchId($data['id'],$country);
    return ["res" => [
        "tecall2" => $res2,
    ]];
});
api::getpiece2(function ($data) {
    $piece = strtoupper($data["piece"]);
    $country="TN";
    if($data["country"]) $country=$data["country"];
    if($data["search"]!="Id"){
        $d0="Any";
        $d1="";
        $d2="";
        $d3="";
        $d4="";
        if ($data["search"] == "")
            $data["search"] = 10;
        else{
            switch($data["seach"]){
                case 0:$d0="Référence";break;
                case 1:$d0="OE Number";break;
                case 6:$d0="Code-barres";break;
                default:$d0="Any";
            }
        }
        if($data["groupe"] != ""){
            $d1=explode("¤",$data["groupe"])[0];
            $d2=explode("¤",$data["groupe"])[1];
        }
        if($data["marque"] != ""){
            $d3=explode("¤",$data["marque"])[0];
            $d4=explode("¤",$data["marque"])[1];
        }
        $res2 = TecAll2::search($piece, $d1, $d3, 1, $data["search"],$country);
    }
    else{
        $res2 = TecAll2::searchId($piece,$country);
    }
    return ["res" => [
        "tecall2" => $res2,
    ]];
});
api::getpiece(function ($data) {
    $piece = strtoupper($data["piece"]);
    $country="TN";
    if($data["country"]) $country=$data["country"];
    if($data["search"]!="Id"){
        $d0="Any";
        $d1="";
        $d2="";
        $d3="";
        $d4="";
        if ($data["search"] == "")
            $data["search"] = 10;
        else{
            switch($data["seach"]){
                case 0:$d0="Référence";break;
                case 1:$d0="OE Number";break;
                case 6:$d0="Code-barres";break;
                default:$d0="Any";
            }
        }
        if($data["groupe"] != ""){
            $d1=explode("¤",$data["groupe"])[0];
            $d2=explode("¤",$data["groupe"])[1];
        }
        if($data["marque"] != ""){
            $d3=explode("¤",$data["marque"])[0];
            $d4=explode("¤",$data["marque"])[1];
        }
        $res2 = TecAll2::search($piece, $d1, $d3, 1, $data["search"],$country);
    }
    else{
        $res2 = TecAll2::searchId($piece,$country);
    }
    return ["res" => [
        "tecall2" => $res2,
    ]];
});
api::getMarqueCars(function ($data) {
    $country="TN";
    if($data["country"]) $country=$data["country"];
    return TecAll2::getMarqueCars($data["articleId"],$country);
});
api::getimma(function($data){
    $imm = strtoupper($data["imm"]);
    $fd=Fadtn::query($imm);
    foreach($fd["data"]["res"] as &$f){
        $f["Annee"]=$f["Année"];
        unset($f["Année"]);
    }
    $s=explode(" ",$imm);
    if(count($s)==3)
        $r=db::row("SELECT CHASSIS,DMC FROM API__MAT WHERE GAUCHE=".$s[0]." and CD_SERIE LIKE '".$s[1]."' and DROIT_MIL=".$s[2]);
    else if(count($s)==2)
        $r=db::row("SELECT CHASSIS,DMC FROM API__MAT WHERE CD_SERIE LIKE '".$s[0]."' and DROIT_MIL=".$s[1]);
    $w=null;
    $z=null;
    if($r["CHASSIS"])$w=strtoupper($r["CHASSIS"]);
    if($r["DMC"])$z=$r["DMC"];
    return [
        "res" => [
            "vin" => $w,
            "dmc" => $z,
            "fad" => $fd["data"]["res"]
        ]
    ];
});
api::getpiece7(function ($data) {
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $row=db::row("SELECT carType,manuId FROM API__VEH_$country WHERE carId=".$data["carId"]);
    $res2 = TecAll2::search2($data["group"],$data["carId"],($row["carType"]?$row["carType"]:"P"),$country);
    return $res2;
});
api::getpiece9(function ($data) {
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $row=db::row("SELECT carType,manuId FROM API__VEH_$country WHERE carId=".$data["carId"]);
    $res2 = TecAll2::search3($data["list"],$data["carId"],($row["carType"]?$row["carType"]:"P"),$country);
    return $res2;
});
api::getpiece10(function ($data) {
    $res2=array();
    $res=array();
    $cols=array();
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $db=db::row("SELECT * FROM API__DBS WHERE titre='".strtoupper($data["db"])."' and etat=1");
    if($db){
        $tab=strtoupper($db["prefix"]."__".$db["tab"]);
        $pdo=db::connect("mysql:host=".$db["host"].";".(empty($db["port"])?"":"port=".$db["port"].";")."dbname=".$db["dbname"],$db["user"],$db["pass"]);
        $xcols=db::table("SELECT COLUMN_NAME as s FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$tab'",null,$pdo);
        foreach($xcols as $xcol){
            array_push($cols,$xcol["s"]."");
        }
        $lcol=null;
        $tcol=null;
        if(in_array("legacyArticleId",$cols)) $lcol="legacyArticleId";
        if(in_array("tduid",$cols)) $tcol="tduid";
        $row = db::row("SELECT carType,manuId FROM API__VEH_$country WHERE carId=".$data["carId"]);
        $res = TecAll2::search3($data["list"],$data["carId"],($row["carType"]?$row["carType"]:"P"),$country);
        $arr=array();
        $arr2=array();
        $arrl=array();
        $arrt=array();
        foreach ($res["articles"] as $item){
            if($lcol){
                $k=$item["genericArticles"][0]["legacyArticleId"];
                if($k){
                    array_push($arr,$k);
                    $arr = array_unique($arr);
                    $arrl[$k.""]=getTdUid($item);
                }
            }
            if($tcol){
                $k2=getTdUid2($item);
                if($k2){
                    array_push($arr2,"'$k2'");
                    $arr2 = array_unique($arr2);
                    $arrt[getTdUid($item)]=$item["genericArticles"][0]["legacyArticleId"];
                }
            }
        }
        $s="";$s2="";
        $s=implode(",",$arr);
        $s2=implode(",",$arr2);
        $wh=array();
        $z="";
        if($s!="") {
            array_push($wh,"(legacyArticleId is not null and legacyArticleId!='' and legacyArticleId in ($s))");
        }
        if($s2!="") {
            array_push($wh,"(tduid is not null and tduid!='' and SUBSTRING_INDEX(tduid,'|',2) in ($s2))");
        }
        $z=implode(" OR ",$wh);
        if($z!=""){
            $z="WHERE ".$z;
            $res2=db::table("SELECT a.* FROM $tab a ".$z,null,$pdo);
        }
        foreach($res2 as &$r){
            if(!$r["tduid"])
                $r["tduid"]=$arrl[$r["legacyArticleId"].""];
            if(!$r["legacyArticleId"])
                $r["legacyArticleId"]=$arrt[$r["tduid"].""];
        }
    }
    return [
        "type" => $db["type"],
		"loc" => $res2,
        "tecall2" => $res
    ];
});
api::getpiece8(function ($data) {
    $res2=array();
    $res=array();
    $cols=array();
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $db=db::row("SELECT * FROM API__DBS WHERE titre='".strtoupper($data["db"])."' and etat=1");
    if($db){
        $tab=strtoupper($db["prefix"]."__".$db["tab"]);
        $pdo=db::connect("mysql:host=".$db["host"].";".(empty($db["port"])?"":"port=".$db["port"].";")."dbname=".$db["dbname"],$db["user"],$db["pass"]);
        $xcols=db::table("SELECT COLUMN_NAME as s FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$tab'",null,$pdo);
        foreach($xcols as $xcol){
            array_push($cols,$xcol["s"]."");
        }
        $lcol=null;
        $tcol=null;
        if(in_array("legacyArticleId",$cols)) $lcol="legacyArticleId";
        if(in_array("tduid",$cols)) $tcol="tduid";
        $row = db::row("SELECT carType,manuId FROM API__VEH_$country WHERE carId=".$data["carId"]);
        $res = TecAll2::search2($data["group"],$data["carId"],($row["carType"]?$row["carType"]:"P"),$country);
        $arr=array();
        $arr2=array();
        $arrl=array();
        $arrt=array();
        foreach ($res["articles"] as $item){
            if($lcol){
                $k=$item["genericArticles"][0]["legacyArticleId"];
                if($k){
                    array_push($arr,$k);
                    $arr = array_unique($arr);
                    $arrl[$k.""]=getTdUid($item);
                }
            }
            if($tcol){
                $k2=getTdUid2($item);
                if($k2){
                    array_push($arr2,"'$k2'");
                    $arr2 = array_unique($arr2);
                    $arrt[getTdUid($item)]=$item["genericArticles"][0]["legacyArticleId"];
                }
            }
        }
        $s="";$s2="";
        $s=implode(",",$arr);
        $s2=implode(",",$arr2);
        $wh=array();
        $z="";
        if($s!="") {
            array_push($wh,"(legacyArticleId is not null and legacyArticleId!='' and legacyArticleId in ($s))");
        }
        if($s2!="") {
            array_push($wh,"(tduid is not null and tduid!='' and SUBSTRING_INDEX(tduid,'|',2) in ($s2))");
        }
        $z=implode(" OR ",$wh);
        if($z!=""){
            $z="WHERE ".$z;
            $res2=db::table("SELECT a.* FROM $tab a ".$z,null,$pdo);
        }
        foreach($res2 as &$r){
            if(!$r["tduid"])
                $r["tduid"]=$arrl[$r["legacyArticleId"].""];
            if(!$r["legacyArticleId"])
                $r["legacyArticleId"]=$arrt[$r["tduid"].""];
        }
    }
    return [
        "type" => $db["type"],
		"loc" => $res2,
        "tecall2" => $res
    ];
});
api::getImm(function($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $imm = strtoupper($data["imm"]);
    $gg=db::row("SELECT * FROM API__VOITURE WHERE mat LIKE '$imm' ORDER BY FIELD(de, '".$data['de']."') DESC, id DESC LIMIT 1");
    if(empty($gg['vin'])){
        $s=explode(" ",$imm);
        if(count($s)==3)
            $r=db::row("SELECT CHASSIS,DMC FROM API__MAT WHERE GAUCHE=".$s[0]." and CD_SERIE LIKE '".$s[1]."' and DROIT_MIL=".$s[2]);
        else if(count($s)==2)
            $r=db::row("SELECT CHASSIS,DMC FROM API__MAT WHERE CD_SERIE LIKE '".$s[0]."' and DROIT_MIL=".$s[1]);
        $w=null;
        if($r["CHASSIS"])$w=strtoupper($r["CHASSIS"]);
        $gg=db::row("SELECT * FROM API__VOITURE WHERE vin LIKE '$w' OR vin like '".substr($w, 0, -6)."______' ORDER BY (vin LIKE '$w') DESC,FIELD(de, '".$data['de']."') DESC, id DESC LIMIT 1");
    }else $w=$gg['vin'];
    if($gg){
        return [
            "vin" => $w,
            "manuid" => $gg["manuid"],
            "modid" => $gg["modid"],
            "id" => $gg["carid"],
            "lst1" => $gg['manulst'],
            "lst2" => $gg['modlst'],
            "lst3" => $gg['typlst']
        ];
    }
    $lst1=array();
    $lst2=array();
    $lst3=array();
    if(count($lst3)!=1){
        $lst4=array();
        $lst5=array();
        $lst6=array();
        $fd=Fadtn::query($imm);
        $res3=$fd["data"]["res"];
        if(is_array($res3)){
            foreach($res3 as $res){
                $qq=db::row("SELECT carId,modelId,manuId FROM API__VEH_$country WHERE carId=".($res["TYPE"]??0));
                if($qq["carId"]){
                    array_push($lst4,$qq["manuId"]);
                    array_push($lst5,$qq["modelId"]);
                    array_push($lst6,$qq["carId"]);
                }
            }
        }
        if((count($lst3)==0&&count($lst6)>0)||(count($lst3)>1&&count($lst6)<=count($lst3))){
            $lst1=$lst4;
            $lst2=$lst5;
            $lst3=$lst6;
        }
    }
    $lst1=array_unique($lst1);
    $lst2=array_unique($lst2);
    $lst3=array_unique($lst3);
    return [
        "vin" => $w,
        "lst1" => (count($lst1)>0?$lst1:null),
        "lst2" => (count($lst2)>0?$lst2:null),
        "lst3" => (count($lst3)>0?$lst3:null)
    ];
});
api::getLsts(function($data){
    $vin = strtoupper($data["vin"]);
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $gg=db::row("SELECT * FROM API__VOITURE WHERE vin LIKE '$vin' OR vin like '".substr($vin, 0, -6)."______' ORDER BY (vin LIKE '$vin') DESC,FIELD(de, '".$data['de']."') DESC, id DESC LIMIT 1");
    if(empty($gg['mat'])){
        $r=db::row("SELECT * FROM API__MAT WHERE CHASSIS LIKE '$vin'");
        $w=null;
        if($r["CD_SERIE"]=="TU")$w=$r["GAUCHE"]." ".$r["CD_SERIE"]." ".$r["DROIT_MIL"];
        else if($r["CD_SERIE"]=="RS")$w=$r["CD_SERIE"]." ".$r["DROIT_MIL"];
    }else $w=$gg['mat'];
    if($gg){
        return [
            "imm" => $w,
            "manuid" => $gg["manuid"],
            "modid" => $gg["modid"],
            "id" => $gg["carid"],
            "lst1" => $gg['manulst'],
            "lst2" => $gg['modlst'],
            "lst3" => $gg['typlst']
        ];
    }
    $res2 = TecAll2::vin($vin,$country);
    $manus=$res2["data"]["matchingManufacturers"];
    $mods=$res2["data"]["matchingModels"];
    $vehs=$res2["data"]["matchingVehicles"];
    $lst1=array();
    $lst2=array();
    $lst3=array();
    $ff=array();
    if(is_array($manus)){
        foreach(current($manus) as $manu){
            $ff["b".$manu["manuId"]]=trim(str_replace("'","\'",str_replace("\'","'",$manu["manuName"])));
            array_push($lst1,$manu["manuId"]);
        }
        if(is_array($mods))
        foreach(current($mods) as $mod){
            $ff["m".$mod["modelId"]]=trim(str_replace("'","\'",str_replace("\'","'",$mod["modelName"])));
            array_push($lst2,$mod["modelId"]);
        }
        if(is_array($vehs))
        foreach(current($vehs) as $veh){
            $ff["v".$veh["carId"]]=trim(str_replace("'","\'",str_replace("\'","'",$veh["vehicleTypeDescription"])));
            db::query("INSERT INTO API__VEH_$country(carId, carName, modelId, modelname, manuId, manuName, carType) VALUES ('".$veh["carId"]."','".$ff["v".$veh["carId"]]."','".$veh["modelId"]."','".$ff["m".$veh["modelId"]]."','".$veh["manuId"]."','".$ff["b".$veh["manuId"]]."','P') ON DUPLICATE KEY UPDATE carName=VALUES(carName),modelId=VALUES(modelId),modelname=VALUES(modelname),manuId=VALUES(manuId),manuName=VALUES(manuName)");
            array_push($lst3,$veh["carId"]);
        }
    }
    if(count($lst3)!=1){
        $lst4=array();
        $lst5=array();
        $lst6=array();
        $res3=[];//vindv::query($vin);
        if($res3){
            $qq=db::row("SELECT carId,modelId,manuId FROM API__VEH_$country WHERE carId=".$res3->carId);
            if($qq["carId"]){
                array_push($lst4,$qq["manuId"]);
                array_push($lst5,$qq["modelId"]);
                array_push($lst6,$qq["carId"]);
            }
        }
        if((count($lst3)==0&&count($lst6)>0)||(count($lst3)>1&&count($lst6)<=count($lst3)&&count($lst6)>0)){
            $lst1=$lst4;
            $lst2=$lst5;
            $lst3=$lst6;
        }
    }
    $lst1=array_unique($lst1);
    $lst2=array_unique($lst2);
    $lst3=array_unique($lst3);
    return [
        "imm" => $w,
        "xres" => $res2,
        "lst1" => (count($lst1)>0?$lst1:null),
        "lst2" => (count($lst2)>0?$lst2:null),
        "lst3" => (count($lst3)>0?$lst3:null)
    ];
});
api::getManux(function($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];

    if(!$data["lst"]){
        return db::table("SELECT DISTINCT(manuId) as id ,manuName as name FROM API__VEH_$country ORDER BY manuName ASC");
    }else{
        return db::table("SELECT DISTINCT(manuId) as id ,manuName as name FROM API__VEH_$country WHERE manuId in (".implode(",",$data["lst"]).") ORDER BY manuName ASC");
    }
});
api::getModx(function($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];

    if(!$data["lst"]){
        return db::table("SELECT DISTINCT(modelId) as id ,modelname as name FROM API__VEH_$country WHERE manuId=".$data["manuid"]." ORDER BY modelname ASC");
    }else{
        return db::table("SELECT DISTINCT(modelId) as id ,modelname as name FROM API__VEH_$country WHERE modelId in (".implode(",",$data["lst"]).") and manuId=".$data["manuid"]." ORDER BY modelname ASC");
    }
});
api::getTypex(function($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];

    if(!$data["lst"]){
        return db::table("SELECT carId as id ,carName as name FROM API__VEH_$country WHERE modelId=".$data["modid"]." ORDER BY carName ASC");
    }else{
        return db::table("SELECT carId as id ,carName as name FROM API__VEH_$country WHERE carId in (".implode(",",$data["lst"]).") and modelId=".$data["modid"]." ORDER BY carName ASC");
    }
});
api::getTypez(function($data){
    $country="TN";
    if($data["country"]) $country=$data["country"];
    return db::row("SELECT carId,manuName,modelname,carName FROM API__VEH_$country WHERE carId=".$data["id"]);
});
api::getpiecedbs(function ($data) {
    if($data['fav']){
        $db=db::row("SELECT * FROM API__DBS WHERE type='MO' and titre='".$data['fav']."' and titre!='UAD' LIMIT 1");
        $ww="";
        if($db) $ww=",'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE tduid='".$data['ref']."'";
        if($ww!=""){
            $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
            $pp=db::row("SELECT *,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price $ww and stockTotal>0 ORDER BY prix_vente_ttc ASC LIMIT 1",null,$pdo);
            db::closepdo($pdo);
            if(!empty($pp)) return $pp;
        }
        if(!$data['plus']) return null;
    }
    $dbs=db::table("SELECT * FROM API__DBS WHERE type='MO' and etat=1 and public=1 and titre!='UAD'");
    $w=array();
    foreach($dbs as $db){
        $w[]="(SELECT *,'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE tduid='".$data['ref']."')";
    }
    $ww=implode(" UNION ",$w);
    if($ww!=""){
        $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
        $pp=db::row("SELECT *,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price FROM ($ww) ax WHERE ax.stockTotal>0 ORDER BY ax.prix_vente_ttc ASC LIMIT 1",null,$pdo);
        db::closepdo($pdo);
        return $pp;
    }
    return null;
});
api::getpiecedbz(function ($data) {
    if($data['list']){
        $list=implode(",",$data['list']);
        if($data['fav']){
            $db=db::row("SELECT * FROM API__DBS WHERE type='MO' and titre='".$data['fav']."' and titre!='UAD' LIMIT 1");
            $ww="";
            if($db) $ww= ",'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE stockTotal>0";
            if($ww!=""){
                $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
                $pp=db::table("SELECT *,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price $ww and tduid in ($list)",null,$pdo);
                db::closepdo($pdo);
                if(!empty($pp)) return $pp;
            }
            if(!$data['plus']) return null;
        }
        $dbs=db::table("SELECT * FROM API__DBS WHERE type='MO' and etat=1 and public=1 and titre!='UAD'");
        $w=array();
        foreach($dbs as $db){
            $w[]="(SELECT *,'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE stockTotal>0 and tduid in ($list))";
        }
        $ww=implode(" UNION ",$w);
        if($ww!=""){
            $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
            $pp=db::table("SELECT *,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price FROM ($ww) gg WHERE gg.prix_vente_ttc=(SELECT min(prix_vente_ttc) as minx FROM ($ww) ae where ae.tduid=gg.tduid)",null,$pdo);
            db::closepdo($pdo);
            return $pp;
        }
    }
    return null;
});
api::getpiecedbastd(function ($data) {
    if($data['fav']){
        $db=db::row("SELECT * FROM API__DBS WHERE type='MO' and titre='".$data['fav']."' LIMIT 1");
        $ww="";
        if($db) $ww= ",'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE stockTotal>0";
        if($ww!=""){
            $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
            if($data["query"]){
                $data["query"] = str_replace("'","\'",$data["query"]);
                $x = "%".preg_replace("/-+|\s+/i", "%", $data["query"])."%";
                $f[]="(articleNumber like '".$data["query"]."' or ref like '$x' or titre like '$x' or titrelocal like '$x')";
            }
            if($data["category_id"]){
                $f[]="(genericArticleId in (".$data["category_id"].") or (100000+id_categorie) in (".$data["category_id"].")) or genericArticleId in (SELECT genericArticleId FROM (WITH RECURSIVE unwound AS ( SELECT * FROM (SELECT id,SUBSTRING_INDEX(tduid,'|',-1) as genericArticleId1,SUBSTRING_INDEX(tduid,'|',-1) as genericArticleId FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE tduid like '%,%') x UNION ALL SELECT id, regexp_replace(genericArticleId1, '^[^,]*,', '') genericArticleId1,genericArticleId FROM unwound WHERE genericArticleId1 LIKE '%,%' ) SELECT genericArticleId FROM unwound where genericArticleId1 in (".$data["category_id"].")) t GROUP by genericArticleId)";
            }
            if($data["brand_id"]){
                $f[]="(dataSupplierId = ".$data["brand_id"]." or (10000+id_marque) in (".$data["brand_id"]."))";
            }
            $ff="1";
            if($f)$ff=implode(" and ",$f);
            if($data['list']){
                $list=implode(",",$data['list']);
                $ff="tduid in ($list)";
            }
            $sq="SELECT *,titre_marque as mfrName,'[]' as articleCriteria FROM (SELECT *,IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(tduid,'|',-2),'|',1),ref) as articleNumber,IFNULL(SUBSTRING_INDEX(tduid,'|',1),(id_marque+10000)) as dataSupplierId,IFNULL(SUBSTRING_INDEX(tduid,'|',-1),id_categorie+100000) as genericArticleId,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price $ww) a WHERE $ff";
            $pp=db::table($sq,null,$pdo);
            db::closepdo($pdo);
            if(!empty($pp)) return ['articles'=>$pp,'sq'=>$sq];
        }
        return null;
    }
});
api::getpiecedbsx(function ($data) {
    if($data['fav']){
        $db=db::row("SELECT * FROM API__DBS WHERE type='MO' and titre='".$data['fav']."' LIMIT 1");
        $ww="";
        if($db) $ww=",'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE id='".$data['id']."'";
        if($ww!=""){
            $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
            $pp=db::row("SELECT *,titre_marque as mfrName,'[]' as articleCriteria,IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(tduid,'|',-2),'|',1),ref) as articleNumber,IFNULL(SUBSTRING_INDEX(tduid,'|',1),(id_marque+10000)) as dataSupplierId,IFNULL(SUBSTRING_INDEX(tduid,'|',-1),id_categorie+100000) as genericArticleId,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price $ww and stockTotal>0 ORDER BY prix_vente_ttc ASC LIMIT 1",null,$pdo);
            db::closepdo($pdo);
            if(!empty($pp)) return $pp;
        }
        if(!$data['plus']) return null;
    }
    $dbs=db::table("SELECT * FROM API__DBS WHERE type='MO' and etat=1 and public=1");
    $w=array();
    foreach($dbs as $db){
        $w[]="(SELECT *,'".$db["dbname"]."' as db FROM ".$db["dbname"].".".$db["prefix"]."__ARTICLE WHERE id='".$data['id']."')";
    }
    $ww=implode(" UNION ",$w);
    if($ww!=""){
        $pdo=db::connect("mysql:host=zr31295-001.dbaas.ovh.net;port=35470;dbname=DEMO","MOSAIQUEWEB","MOSAmo2022");
        $pp=db::row("SELECT *,titre_marque as mfrName,'[]' as articleCriteria FROM (SELECT *,IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(tduid,'|',-2),'|',1),ref) as articleNumber,IFNULL(SUBSTRING_INDEX(tduid,'|',1),(id_marque+10000)) as dataSupplierId,IFNULL(SUBSTRING_INDEX(tduid,'|',-1),id_categorie+100000) as genericArticleId,prix_vente_ttc*(1+(".($data['margin']??0)."/100)) as shop_price FROM ($ww) ax WHERE ax.stockTotal>0 ORDER BY ax.prix_vente_ttc ASC LIMIT 1",null,$pdo);
        db::closepdo($pdo);
        return $pp;
    }
    return null;
});

api::getpiecedb(function ($data) {
	$piece = strtoupper($data["piece"]);
    $res2=array();
    $res=array();
    $cols=array();
    $country="TN";
    if($data["country"]) $country=$data["country"];
    $db=db::row("SELECT * FROM API__DBS WHERE titre='".strtoupper($data["db"])."' and etat=1 and titre!='UAD'");
    if($db){
        $tab=strtoupper($db["prefix"]."__".$db["tab"]);
        $pdo=db::connect("mysql:host=".$db["host"].";".(empty($db["port"])?"":"port=".$db["port"].";")."dbname=".$db["dbname"],$db["user"],$db["pass"]);
        $xcols=db::table("SELECT COLUMN_NAME as s FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$tab'",null,$pdo);
        foreach($xcols as $xcol){
            array_push($cols,$xcol["s"]."");
        }
        $lcol=null;
        $tcol=null;
        if(in_array("legacyArticleId",$cols)) $lcol="legacyArticleId";
        if(in_array("tduid",$cols)) $tcol="tduid";
        if($data["search"]!="Id"){
            $d0="Any";
            $d1="";
            $d2="";
            $d3="";
            $d4="";
            if ($data["search"] == "")
                $data["search"] = 10;
            else{
                switch($data["seach"]){
                    case 0:$d0="Référence";break;
                    case 1:$d0="OE Number";break;
                    case 6:$d0="Code-barres";break;
                    default:$d0="Any";
                }
            }
            if($data["groupe"] != ""){
                $d1=explode("¤",$data["groupe"])[0];
                $d2=explode("¤",$data["groupe"])[1];
            }
            if($data["marque"] != ""){
                $d3=explode("¤",$data["marque"])[0];
                $d4=explode("¤",$data["marque"])[1];
            }
            $res = TecAll2::search($piece, $d1, $d3, 1, $data["search"],$country);
            $arr=array();
            $arr2=array();
            $arrl=array();
            $arrt=array();
            foreach ($res["articles"] as $item){
                if($lcol){
                    $k=$item["genericArticles"][0]["legacyArticleId"];
                    if($k){
                        array_push($arr,$k);
                        $arr = array_unique($arr);
                        $arrl[$k.""]=getTdUid($item);
                    }
                }
                if($tcol){
                    $k2=getTdUid2($item);
                    if($k2){
                        array_push($arr2,"'$k2'");
                        $arr2 = array_unique($arr2);
                        $arrt[getTdUid($item)]=$item["genericArticles"][0]["legacyArticleId"];
                    }
                }
            }
            $s="";$s2="";
            $s=implode(",",$arr);
            $s2=implode(",",$arr2);
            $wh=array();
            $z="";
            if($s!="") {
                array_push($wh,"(legacyArticleId is not null and legacyArticleId!='' and legacyArticleId in ($s))");
            }
            if($s2!="") {
                array_push($wh,"(tduid is not null and tduid!='' and SUBSTRING_INDEX(tduid,'|',2) in ($s2))");
            }
            $z=implode(" OR ",$wh);
            if($z!=""){
                $z="WHERE ".$z;
                $res2=db::table("SELECT a.* FROM $tab a ".$z,null,$pdo);
            }
            foreach($res2 as &$r){
                if(!$r["tduid"])
                    $r["tduid"]=$arrl[$r["legacyArticleId"].""];
                if(!$r["legacyArticleId"])
                    $r["legacyArticleId"]=$arrt[$r["tduid"].""];
            }
        }
        else{
            $res = TecAll2::searchId($piece,$country);
            if($lcol) {
                $res2 = db::table("SELECT a.* FROM $tab a WHERE legacyArticleId=$piece",null,$pdo);
                foreach($res2 as &$r){
                    if(!$r["tduid"])
                        $r["tduid"]=getTdUid($res["articles"][0]);
                }
            }
        }
        db::closepdo($pdo);
    }
    return ["res" => [
        "type" => $db["type"],
		"loc" => $res2,
        "tecall2" => $res
    ]];
});
api::chartpe(function($data){
    $where="";
    if($data["x"]>0){
        $where=file_get_contents('./public/where/API__MAT.txt');
    }
    $eng=db::table("SELECT COUNT(*) AS a, ENERGIE as b FROM API__MAT $where GROUP BY ENERGIE ORDER BY a DESC LIMIT 100");
    $car=db::table("SELECT COUNT(*) AS a, CARROSSERIE as b FROM API__MAT $where GROUP BY CARROSSERIE ORDER BY a DESC LIMIT 100");
    $gen=db::table("SELECT COUNT(*) AS a, genre as b FROM API__MAT $where GROUP BY genre ORDER BY a DESC LIMIT 100");
    $gov=db::table("SELECT COUNT(*) AS a, GOV as b FROM API__MAT $where GROUP BY GOV ORDER BY a DESC LIMIT 100");
    $marq=db::table("SELECT COUNT(*) AS a, MARQUE as b FROM API__MAT $where GROUP BY MARQUE ORDER BY a DESC LIMIT 100");
   
    $eng1=array();
    $eng1["data"]=array();
    $eng1["label"]=array();
    $eng1["title"]="****** ENERGIE ******";
    foreach ($eng as $value) {
        array_push($eng1["data"],$value["a"]);
        array_push($eng1["label"],($value["b"]!=""?$value["b"]:'Vide'));
    }
    $car1=array();
    $car1["data"]=array();
    $car1["label"]=array();
    $car1["title"]="****** CARROSSERIE ******";
    foreach ($car as $value) {
        array_push($car1["data"],$value["a"]);
        array_push($car1["label"],($value["b"]!=""?$value["b"]:'Vide'));
    }
    $gen1=array();
    $gen1["data"]=array();
    $gen1["label"]=array();
    $gen1["title"]="****** GENRE ******";
    foreach ($gen as $value) {
        array_push($gen1["data"],$value["a"]);
        array_push($gen1["label"],($value["b"]!=""?$value["b"]:'Vide'));
    }
    $gov1=array();
    $gov1["data"]=array();
    $gov1["label"]=array();
    $gov1["title"]="****** GOV ******";
    foreach ($gov as $value) {
        array_push($gov1["data"],$value["a"]);
        array_push($gov1["label"],($value["b"]!=""?$value["b"]:'Vide'));
    }
    $marq1=array();
    $marq1["data"]=array();
    $marq1["label"]=array();
    $marq1["title"]="****** MARQUE ******";
    foreach ($marq as $value) {
        array_push($marq1["data"],$value["a"]);
        array_push($marq1["label"],($value["b"]!=""?$value["b"]:'Vide'));
    }
    $pp=array();
    array_push($pp,$eng1);
    array_push($pp,$car1);
    array_push($pp,$gen1);
    array_push($pp,$gov1);
    array_push($pp,$marq1);
    return $pp;
});