<?php
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\Exception;

    global $ambiente;

    $percorso           = dirname(__DIR__);
    $export_tot         = array();
    $today              = date("Y-m-d");
    $ambiente           = 1;
    
    set_time_limit(3600);

    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/library/php/db.class.php");
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/library/php/db.check.php");
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/library/php/mail.class.php");
    
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/phpmailer/src/SMTP.php");
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/phpmailer/src/PHPMailer.php");
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/phpmailer/src/Exception.php");
    require_once $percorso.str_replace("/",DIRECTORY_SEPARATOR,"/assets/xlsxgen/SimpleXLSXGen.php");
    
    $temp       = $percorso .DIRECTORY_SEPARATOR. "temp" .DIRECTORY_SEPARATOR;
    $fname      = $temp."report_campioni_".$today.".xlsx";
    
    // 08/04/2024 :: Rilevo le scadenze impostate e scopro la prossima scadenza
    //$Query      = "select Valore2,Valore3 from TAB_VARI where lower(Valore1) = 'campioni_report_data'";
    //$stmt       = $db->prepare($Query);
    //$stmt->execute();
    //$row	    = $stmt->fetch();
    $Mese       = str_pad($row[0], 2, "0", STR_PAD_LEFT); 
    $Anno       = $row[1];

    $Data_init  = $Anno."-".$Mese."-01";
    $Data_fine  = date("Y-m-t", strtotime($Data_init)); 
    
    $Data_init_w= date("d/m/Y",strtotime($Data_init));
    $Data_fine_w= date("t/m/Y",strtotime($Data_fine));
    $z          = 0;

    // 08/04/2024 :: Query principale

    $Query ="SELECT DataMovimento, Protocollo, Causale, Tipo, MagazzinoIn,DescrizioneMagazzinoIn,ShCustomer,ShName,ShRegione,ShQualifica,Da_Titolo,Da_Descrizione,Da_Email,
    Da_Reparto,Da_Stanza,CodiceArticoloSAP,NomeArticoloShort,Hyperion1,Hyperion2,Hyperion3,StratPack,QtaScarico,round(PrezzoPosizionamento,3) AS PrezzoPosizionamento
    FROM MOVIMENTI_MAGAZZINO, MOVIMENTI_MAGAZZINO_DETTAGLIO WHERE MOVIMENTI_MAGAZZINO.Id = MOVIMENTI_MAGAZZINO_DETTAGLIO.IdMovimento AND upper(Hyperion1) IN ('HOME CARE','HOSPITAL UROLOGY') 
    AND upper(StratPack) NOT IN ('STOMIA','CONVESSO') AND MOVIMENTI_MAGAZZINO.Protocollo IS NOT NULL AND Tipo <> 'C' AND DataMovimento BETWEEN '".$Data_init_w."' AND '".$Data_fine_w."' 
    ORDER BY DataMovimento";

    $stmt       = $db->prepare($Query);
    $stmt->execute();

    while ($row	= $stmt->fetch()) {
        $export = array();

        if($z == 0) {
            array_push($export,"DataMovimento","Protocollo","Causale","Tipo","MagazzinoIn","DescrizioneIn","SHCustomer","SHName","SHRegione","SHQualifica","DA_Titolo",
            "DA_Descrizione","DA_Email","DA_Reparto","DA_Stanza","CodiceArticoloSAP","NomeArticoloShort","Hyperion1","Hyperion2","Hyperion3","StratPack","QtaScarico","PrezzoPosizionamento"); 	
            array_push($export_tot,$export);
            $z++;
        }

        else if ($z > 0) {
            $dm = date("d/m/Y",strtotime($row[0]));
            array_push($export,$dm,utf8_encode($row[1]),utf8_encode($row[2]),utf8_encode($row[3]),utf8_encode($row[4]),utf8_encode($row[5]),utf8_encode($row[6]),utf8_encode($row[7]),utf8_encode($row[8]),utf8_encode($row[9]),
            utf8_encode($row[10]),utf8_encode($row[11]),utf8_encode($row[12]),utf8_encode($row[13]),utf8_encode($row[14]),utf8_encode($row[15]),utf8_encode($row[16]),utf8_encode($row[17]),
            utf8_encode($row[18]),utf8_encode($row[19]),utf8_encode($row[20]),utf8_encode($row[21]),utf8_encode(number_format($row[22],3)));
            array_push($export_tot,$export);
        }
    }

    $xlsx = Shuchkin\SimpleXLSXGen::fromArray($export_tot)->saveAs($fname);

    // 29/11/2022 :: Testo del report - tra <<>> i testi da sostituire
    $Soggetto   = "Campioni Report :: <<data_init>> - <<data_fine>>";
    $CorpoIta   = "Si trasmette, in allegato, il report Campioni per il periodo <<data_init>> - <<data_fine>>";
    $CorpoEng   = "Please find enclosed Sample report for the period <<data_init>> - <<data_fine>>";
    $CodaIta    = "<small>Questo indirizzo di posta elettronica fa riferimento ad una casella non presidiata ed è utilizzata esclusivamente per funzioni di servizio</small><br>";
    $CodaEng    = "<small>This email address refers to an unattended mailbox and is used exclusively for service functions</small>";

    $search     = array("<<data_init>>","<<data_fine>>");
    $replace    = array($Data_init_w,$Data_fine_w);
    
    $Soggetto   = str_replace($search,$replace,$Soggetto);
    $CorpoIta   = str_replace($search,$replace,$CorpoIta);
    $CorpoEng   = str_replace($search,$replace,$CorpoEng);

    $Corpo      = $CorpoIta."<br><br>".$CorpoEng."<br><br><br>Dataflex Tender - Servizi di supporto<br><br><br><br>".$CodaIta.$CodaEng;

    // 29/11/2022 :: Recupero indirizzi dei destinatari e ciclo per destinatari multipli stessa BU
    $Query  = "select Descrizione from TAB_VARI where Valore1 = 'campioni_report_send'";
    $stmt   = $db->prepare($Query);
    $stmt->execute();
    $row	= $stmt->fetch();
    $dest   = explode(";",$row[0]);

    try {
        $ma                 = new MAILprog;
        $mail               = new PHPMailer(true);
        $mail->CharSet      = $ma->char;
        $mail->SMTPDebug    = 0;
        $mail->IsSMTP();
        $mail->SMTPAuth     = true;
        $mail->SMTPOptions  = array ('ssl' => array ('verify_peer' => false,'verify_peer_name' => false,'allow_self_signed' => true));    
        $mail->Username     = $ma->user;
        $mail->Password     = $ma->pass;
        $mail->SMTPSecure   = $ma->secu;
        $mail->Host         = $ma->host;
        $mail->Port         = $ma->port;
        $mail->From         = $ma->from;
        $mail->FromName     = $ma->name;
        $mail->addAttachment($fname);

        for ($y = 0; $y < count($dest); $y++) {
            $mail->AddAddress($dest[$y]);
        }
        $mail->Subject      = $Soggetto;
        $mail->IsHTML(true);
        $mail->Body         = $Corpo;
        if(!$mail->Send())  $send = 1;
        $mail->clearAllRecipients();
    }
    catch (Exception $e) {
        echo $e;
    }

    // 16/2022 30/11/2022
    // 16/11/2022 :: Imposta la prossima scadenza
    if ($Mese < 12) {
        $Mese++;
    }
    else {
        $Mese = 1;
        $Anno++;
    }
    $Query  = "update TAB_VARI set Valore2 = '".$Mese."', Valore3 = '".$Anno."' where lower(Valore1) = 'campioni_report_data'";
    $stmt   = $db->prepare($Query);
    $stmt->execute();

    $db = null;
?>