<?php
class Consult {

    public static  function search($input)
    {

        $OPFGlobal = new OPFGlobal();
        $OPFGlobal->initOPF();

        switch ($input["Type"]) {
            case 'OS':

                                        
                    

                    $sql="SELECT vtiger_salesorder.salesorderid,salesorder_no,sostatus,cf_1897,subject FROM vtiger_salesorder
                        INNER JOIN vtiger_salesordercf on vtiger_salesordercf.salesorderid=vtiger_salesorder.salesorderid
                        INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid=vtiger_salesorder.salesorderid and deleted='0'
                        ";

                    $where = "where";

                    if (isset($input["No_OS"]))
                    {
                        $where .= "  vtiger_salesorder.salesorder_no='".$input["No_OS"]."'";
                    } elseif (isset(($input["subject"])))
                    {
                        $where .= "  vtiger_salesorder.subject='".$input["subject"]."'";
                    }

                    if ($where == "where")
                    {
                        throw new Exception("Error campo de búsqueda no detectado", 500);
                    }

                    $sql .= $where;
                    $result = mysqli_query($OPFGlobal->link,$sql); 

                    if (!$result) {
                        throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                    }

                    $SoData = mysqli_fetch_assoc($result);

                    if ($SoData == null) {
                        throw new Exception("No se encontró la orden de salida  ",404);
                    }
                
                    $record=$SoData['salesorderid'];
                    $sale_no=$SoData['salesorder_no'];
                    $so_status=$SoData['sostatus'];
                    $subject=$SoData['subject'];
                    $cf_1897=$SoData['cf_1897'];
                    if($cf_1897 == 1){
                        $cf_1897="Si";
                    }else{
                        $cf_1897="No";
                    }

                    $sqlLines="SELECT productname,vtiger_inventoryproductrel.productid,productcode,SUM(vtiger_inventoryproductrel.quantity) AS quantity,
                                SUM(vtiger_inventoryproductrel.quantity_storage) AS quantity_storage , procesado ,productcategory
                             FROM vtiger_inventoryproductrel
                            INNER JOIN vtiger_salesorder on vtiger_salesorder.salesorderid=vtiger_inventoryproductrel.id
                            INNER JOIN vtiger_products on vtiger_products.productid=vtiger_inventoryproductrel.productid
                            where salesorderid='$record' 
                            GROUP BY 
                                vtiger_inventoryproductrel.productid, 
                                vtiger_products.productcode";

                    $resultLines = mysqli_query($OPFGlobal->link,$sqlLines); 

                    if (!$resultLines) {
                        throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                    }


                    while($LineData = mysqli_fetch_assoc($resultLines)){

                        
                        $product_id=$LineData['productid'];
                        $product_no=$LineData['productcode'];
                        $quantity=intval($LineData['quantity']);
                        $quantity_storage=$LineData['quantity_storage'];
                        $procesado=$LineData['procesado'];
                        $name=$LineData['productname'];
                        $category=$LineData['productcategory'];
                        $ncantidad=intval($quantity);
                        $faltante=$ncantidad-$procesado;
                        

                        $SQLMov = "select createdtime , movimientos_tks_cantidad
                        from vtiger_movimientos 
                        INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid=vtiger_movimientos.movimientosid and deleted='0'
                        where vtiger_movimientos.movimientos_tks_producto='$product_no' and vtiger_movimientos.movimientos_tks_ordendesalida='$record'
                        order by createdtime desc limit 1;";

                        $resultMov = mysqli_query($OPFGlobal->link,$SQLMov); 

              
                        if (!$resultMov) {
                            throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                        }

                        $MovData = mysqli_fetch_assoc($resultMov);

                        if ($MovData == null) {
                            
                            $createdtime = null;
                            $conversionProcesado= null;
                            $ultimaCantidad= null;
                            $procesado_gp_total= null;
                        } else {
                            $datetime = new DateTime($MovData['createdtime']);
                            $ultimaCantidad = $MovData['movimientos_tks_cantidad'];
                            $datetime->sub(new DateInterval('PT4H')); // Restar 4 horas
                            $createdtime = $datetime->format('Y-m-d H:i:s');
                            $conversionProcesado=$quantity_storage/$ultimaCantidad;
                            $procesado_gp_total=$quantity_storage/$procesado;
                        }
                        
                        $mov[]=array("Product No"=>$product_no,"Producto"=>$name,"Categoria"=>$category,"Total"=>$ncantidad,"Procesado"=>$procesado,"Faltante"=>$faltante,"FechaUltMov"=>$createdtime,"quantity"=>$quantity_storage,"quantity_storage"=>$quantity,'procesado_gp_ultimo'=>$conversionProcesado,'procesado_gp_total'=>$procesado_gp_total);

                    }

                    return array('Orden No'=>$sale_no,'Estado'=>$so_status,'Completado'=>$cf_1897,'subject'=>$subject,'Movimientos'=>$mov);


                break;
            case 'OC':

        
                    $sql="SELECT vtiger_purchaseorder.purchaseorderid,purchaseorder_no,postatus,cf_1905,subject FROM vtiger_purchaseorder 
                        INNER JOIN vtiger_purchaseordercf on vtiger_purchaseordercf.purchaseorderid=vtiger_purchaseorder.purchaseorderid
                        INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid=vtiger_purchaseorder.purchaseorderid and deleted='0'
                        ";

                     $where = "where";

                    if (isset($input["No_OC"]))
                    {
                        $where .= "  vtiger_purchaseorder.purchaseorder_no='".$input["No_OC"]."'";
                    } elseif (isset(($input["subject"])))
                    {
                        $where .= "  vtiger_purchaseorder.subject='".$input["subject"]."'";
                    }

                    if ($where == "where")
                    {
                        throw new Exception("Error campo de búsqueda no detectado", 500);
                    }

                    $sql .= $where;

                    $result = mysqli_query($OPFGlobal->link,$sql); 

                    if (!$result) {
                        throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                    }

                    $OcData = mysqli_fetch_assoc($result);

                    if ($OcData == null) {
                        throw new Exception("No se encontro la orden de compra  ",404);
                    }

                    $record = $OcData['purchaseorderid'];
                    $purchaseorder_no=$OcData['purchaseorder_no'];
                    $po_status=$OcData['postatus'];
                    $subject=$OcData['subject'];
                    $cf_1905=$OcData['cf_1905'];
                    if($cf_1905 == 1){
                        $cf_1905="Si";
                    }else{
                        $cf_1905="No";
                    }

                    $sqlLines = "SELECT productname,productcategory,vtiger_inventoryproductrel.productid,productcode,SUM(vtiger_inventoryproductrel.quantity) AS quantity,
                         SUM(vtiger_inventoryproductrel.quantity_storage) AS quantity_storage  , procesado 
                        FROM vtiger_inventoryproductrel
                        INNER JOIN vtiger_purchaseorder on vtiger_purchaseorder.purchaseorderid=vtiger_inventoryproductrel.id
                        INNER JOIN vtiger_products on vtiger_products.productid=vtiger_inventoryproductrel.productid
                        where purchaseorderid='$record' 
                        GROUP BY 
                        vtiger_inventoryproductrel.productid, 
                        vtiger_products.productcode ";

                    $resultLines = mysqli_query($OPFGlobal->link,$sqlLines); 

                    if (!$resultLines) {
                        throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                    }


                    while($LineData = mysqli_fetch_assoc($resultLines)){

                        $product_id=$LineData['productid'];
                        $product_no=$LineData['productcode'];
                        $quantity=intval($LineData['quantity']);
                        $quantity_storage=$LineData['quantity_storage'];
                        $procesado=$LineData['procesado'];
                        $category=$LineData['productcategory'];
                        $name=$LineData['productname'];
                        $ncantidad=intval($quantity);
                        $faltante=$ncantidad-$procesado;
                        $conversionProcesado=$procesado/$quantity_storage;

                        $SQLMov = "select createdtime , movimientos_tks_cantidad
                        from vtiger_movimientos 
                        INNER JOIN vtiger_crmentity on vtiger_crmentity.crmid=vtiger_movimientos.movimientosid and deleted='0'
                        where vtiger_movimientos.movimientos_tks_producto='$product_no' and vtiger_movimientos.movimientos_tks_ordendecompra='$record'
                        order by createdtime desc limit 1;";

                        $resultMov = mysqli_query($OPFGlobal->link,$SQLMov); 

              
                        if (!$resultMov) {
                            throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                        }

                        $MovData = mysqli_fetch_assoc($resultMov);

                        if ($MovData == null) {
                            
                            $createdtime = null;
                            $conversionProcesado= null;
                            $ultimaCantidad= null;
                            $procesado_gp_total= null;
                        } else {
                            $datetime = new DateTime($MovData['createdtime']);
                            $ultimaCantidad = $MovData['movimientos_tks_cantidad'];
                            $datetime->sub(new DateInterval('PT4H')); // Restar 4 horas
                            $createdtime = $datetime->format('Y-m-d H:i:s');
                            $conversionProcesado=$quantity_storage/$ultimaCantidad;
                            $procesado_gp_total=$quantity_storage/$procesado;
                        }

                        $mov[]=array("Product No"=>$product_no,"Producto"=>$name,"Categoria"=>$category,"Total"=>$ncantidad,"Procesado"=>$procesado,"Faltante"=>$faltante,"FechaUltMov"=>$createdtime,"quantity"=>$quantity_storage,"quantity_storage"=>$quantity,'procesado_gp_ultimo'=>$conversionProcesado,'procesado_gp_total'=>$procesado_gp_total);

                    }

                    return array('Orden No'=>$purchaseorder_no,'Estado'=>$po_status,'Completado'=>$cf_1905,'subject'=>$subject,'Movimientos'=>$mov);

                break;

            case 'Product':
                
                if (!isset($input["productcode"]) or $input["productcode"] == "" ) {
                        throw new Exception("productcode invalido",500);  
                }

                $productcode = $input["productcode"];

                $sql="SELECT qtyinstock, cf_2739
                        FROM vtiger_products 
                        INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_products.productid
                        INNER JOIN vtiger_productcf ON vtiger_productcf.productid=vtiger_products.productid
                        WHERE deleted='0' AND  productcode ='$productcode'";

                $result = mysqli_query($OPFGlobal->link,$sql); 

                if (!$result) {
                    throw new Exception("Error obteniendo los datos de la BD ".mysqli_error($OPFGlobal->link));
                }

                $ProductData = mysqli_fetch_assoc($result);

                if ($ProductData == null) {
                    throw new Exception("No se encontro el producto ($productcode) ",404);
                }
                $stock=explode(".",$ProductData["qtyinstock"]);
                return array('qtyinstock'=>$stock[0],'StorageUnity'=>$ProductData["cf_2739"]);

                break;
            
            default:
                throw new Exception("Type invalido",500);  
                break;
        }



       return ['productid' => $productid];

    }

}
