<?php
require_once 'ApiGlobalFunctions.php';
$usuario=$_POST['uid'];
$password=$_POST['key'];
$url=$_POST['url'];
$estado=$_POST['estado'];
$bodega=$_POST['wID'];
$include=includec($url);
include $include;
$validate=loginkey($usuario,$password,$url);
error_log("LIST OS: ".$usuario."--".$password."--".$url."--".$estado."--".$bodega."\n", 3, "php.log");
if($validate==1){
    $docs=array();
    mysqli_set_charset($link, "utf8");
    $campo1=diccionario($url,'completadoos');
    $campo2=diccionario($url,'traslados');
    $campo3=diccionario($url,'borigen');
    $campo4=diccionario($url,'bodestino');
    
   if($estado==1){
		if (strpos($url, 'solucorp') !== false) {
            $query=mysqli_query($link, "SELECT vtiger_salesorder.salesorderid, subject, salesorder_no,vtiger_crmentity.createdtime,sostatus,concat(first_name,' ',last_name) as asignado,accountname as cuenta,
            concat(firstname,' ',lastname) as contacto,
            vtiger_soshipads.ship_street as direccion,vtiger_soshipads.ship_city as comuna,vtiger_soshipads.ship_state as region,vtiger_soshipads.ship_country as pais,
            vtiger_crmentity.description  as descripcion
            FROM vtiger_salesorder 
            INNER JOIN vtiger_salesordercf ON vtiger_salesordercf.salesorderid=vtiger_salesorder.salesorderid 
            INNER JOIN vtiger_inventoryproductrel ON vtiger_inventoryproductrel.id=vtiger_salesorder.salesorderid
            INNER JOIN vtiger_products ON vtiger_products.productid=vtiger_inventoryproductrel.productid
            INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_salesorder.salesorderid 
            INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid 
            INNER JOIN vtiger_account ON vtiger_account.accountid=vtiger_salesorder.accountid 
            INNER JOIN vtiger_soshipads ON vtiger_soshipads.soshipaddressid=vtiger_salesorder.salesorderid
            LEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid=vtiger_salesorder.contactid     
            INNER JOIN vtiger_paquetes ON vtiger_paquetes.paquetes_tks_ordendesalida=vtiger_salesorder.salesorderid
            INNER JOIN vtiger_crmentity CRM2 ON CRM2.crmid=vtiger_paquetes.paquetesid
            WHERE vtiger_crmentity.deleted='0' AND CRM2.deleted='0'  AND ".$campo1."='1' AND ".$campo2."='0' AND vtiger_crmentity.smownerid='$usuario' GROUP BY vtiger_salesorder.salesorderid ORDER BY createdtime DESC limit 100");
        }else{
    		$query=mysqli_query($link, "SELECT vtiger_salesorder.salesorderid, subject, salesorder_no,vtiger_crmentity.createdtime,sostatus,concat(first_name,' ',last_name) as asignado,accountname as cuenta,
    	concat(firstname,' ',lastname) as contacto,
    	vtiger_soshipads.ship_street as direccion,vtiger_soshipads.ship_city as comuna,vtiger_soshipads.ship_state as region,vtiger_soshipads.ship_country as pais,
      	vtiger_crmentity.description  as descripcion
    	FROM vtiger_salesorder 
    	INNER JOIN vtiger_salesordercf ON vtiger_salesordercf.salesorderid=vtiger_salesorder.salesorderid 
    	INNER JOIN vtiger_inventoryproductrel ON vtiger_inventoryproductrel.id=vtiger_salesorder.salesorderid
    	INNER JOIN vtiger_products ON vtiger_products.productid=vtiger_inventoryproductrel.productid
    	INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_salesorder.salesorderid 
    	INNER JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid 
    	INNER JOIN vtiger_account ON vtiger_account.accountid=vtiger_salesorder.accountid 
    	INNER JOIN vtiger_soshipads ON vtiger_soshipads.soshipaddressid=vtiger_salesorder.salesorderid
    	LEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid=vtiger_salesorder.contactid     
    	INNER JOIN vtiger_paquetes ON vtiger_paquetes.paquetes_tks_ordendesalida=vtiger_salesorder.salesorderid
    	INNER JOIN vtiger_crmentity CRM2 ON CRM2.crmid=vtiger_paquetes.paquetesid
    	WHERE vtiger_crmentity.deleted='0' AND CRM2.deleted='0'  AND ".$campo1."='1' AND ".$campo2."='0' GROUP BY vtiger_salesorder.salesorderid ORDER BY createdtime DESC");
		}
	}
    if($estado==0){
        if (strpos($url, 'solucorp') !== false) {
            $query=mysqli_query($link,"CALL obtener_ordenes_venta_estado_0('$usuario')");
        }else{    
            $query=mysqli_query($link,"CALL obtener_ordenes_venta_estado_0()");
        }    
        //Limpiar todos los resultados del CALL
        do {
            if ($res = mysqli_store_result($link)) {
                mysqli_free_result($res);
            }
        } while (mysqli_more_results($link) && mysqli_next_result($link));
    }
    if($estado==2 && $bodega>0){
	$query=mysqli_query($link, "SELECT vtiger_salesorder.salesorderid,salesorder_no, subject, CRM1.createdtime, sostatus,concat(US1.first_name,' ',US1.last_name) as asignado, destino.bodegas_tks_nombre as cuenta, concat(US2.first_name,' ',US2.last_name) as contacto, destino.bodegas_tks_direccioncomercial as direccion, destino.bodegas_tks_comunacomercial as comuna, destino.bodegas_tks_regioncomercial as region, destino.bodegas_tks_pais as pais, CRM1.description as descripcion
        FROM vtiger_salesorder 
        INNER JOIN vtiger_salesordercf ON vtiger_salesordercf.salesorderid=vtiger_salesorder.salesorderid 
        INNER JOIN vtiger_inventoryproductrel ON vtiger_inventoryproductrel.id=vtiger_salesorder.salesorderid
        INNER JOIN vtiger_products ON vtiger_products.productid=vtiger_inventoryproductrel.productid
        INNER JOIN vtiger_crmentity CRM1 ON CRM1.crmid=vtiger_salesorder.salesorderid 
        INNER JOIN vtiger_users US1 ON US1.id=CRM1.smownerid 
        LEFT JOIN  vtiger_bodegas origen ON origen.bodegasid=vtiger_salesordercf.$campo3
        LEFT JOIN vtiger_bodegas  destino ON destino.bodegasid=vtiger_salesordercf.$campo4
        LEFT JOIN vtiger_crmentity CRM2 ON CRM2.crmid=destino.bodegasid
        LEFT JOIN vtiger_users US2 ON US2.id=CRM2.smownerid 
        WHERE CRM1.deleted='0' and sostatus='Crear caja' AND ".$campo1."='0'  AND ".$campo3."='$bodega'    GROUP BY vtiger_salesorder.salesorderid  ORDER BY CRM1.createdtime DESC");
    }
    $num=mysqli_num_rows($query);    
    $z=0;                  
    //print_r(mysqli_fetch_array($query));
    $AssetResv=diccionario($url,'AssetResv');
    $EstadoAsset=diccionario($url,'EstadoAsset');
    while ($row = mysqli_fetch_array($query)) {  
        $Reservado=0;
        $osid=$row['salesorderid'];            
        $queryc=mysqli_query($link,"SELECT * 
            FROM vtiger_paquetes 
            INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_paquetes.paquetesid 
            WHERE deleted='0' AND paquetes_tks_ordendesalida='$osid' AND paquetes_tks_estado IN ('Almacenada','Nueva')");
        $numc=mysqli_num_rows($queryc);
        error_log(" LIST OS CAJAS?: ".$osid."--".$numc."\n", 3, "php.log");
        if($numc<=0){
        $asunto=$row['subject'];
        $osnum=$row['salesorder_no'];
        $fecha=$row['createdtime'];
        $fecha=explode(" ",$fecha);
        $fecha=$fecha[0]; 
        $status=$row['sostatus'];
        $asignado=$row['asignado']; 
        $cuenta=$row['cuenta'];
        $contacto=$row['contacto']; 
        $direccion=$row['direccion']; 
        $comuna=$row['comuna']; 
        $region=$row['region']; 
        $pais=$row['pais']; 
        $descripcion=$row['descripcion']; 

        $queryres=mysqli_query($link,"SELECT *
            FROM vtiger_assets
            INNER JOIN vtiger_assetscf ON vtiger_assetscf.assetsid=vtiger_assets.assetsid
            INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_assets.assetsid
            WHERE deleted='0' AND ".$AssetResv."='$osid' AND assetstatus='$EstadoAsset'");
        $numres=mysqli_num_rows($queryres);
        if($numres>=1){
            $Reservado=1;
        }else{
            $Reservado=0;
        }
       
        $docs[$z]=array('osid'=>$osid,'asuntoOS'=>$asunto,'OSNum'=>$osnum,'FechaOS'=>$fecha,'EstadOS'=>$status,'osUser'=>$asignado,'osCuenta'=>$cuenta,'osContacto'=>$contacto,'osDirEnvio'=>$direccion,'osComuna'=>$comuna,'osRegion'=>$region,'osPais'=>$pais,'osDescripcion'=>$descripcion,'Reservado'=>$Reservado);
        $z++;
        }            
    }
     error_log(" RETORNO LIS OS: ".print_r($docs,true)."\n", 3, "php.log");
     echo json_encode(setDataJSON($docs,1));
}