public function traitementLots()
{
    try {

        $IDAGENCE = 4; // A remplacer par l'agence concernée

        $ZONESTOCKAGE_RESERVE = ZONESTOCKAGE::select("IDZONESTOCKAGE")
            ->where("ReservePrincipale", true)
            ->where("IDAGENCE", $IDAGENCE)
            ->first();

        $ZONESTOCKAGE_DEBALLE = ZONESTOCKAGE::select("IDZONESTOCKAGE")
            ->where("DeballePrincipal", true)
            ->where("IDAGENCE", $IDAGENCE)
            ->first();

        if ($ZONESTOCKAGE_RESERVE == null && $ZONESTOCKAGE_DEBALLE == null) {
            return response()->json(["error" => true, "messages" => "Merci de vérifier les zones de stockage !"], 500);
        }

        // $STOCKAGENCE_LOTS = STOCKAGENCE_LOTS::whereRaw('COALESCE("IDZONESTOCKAGE", 0) = 0')
        $STOCKAGENCE_LOTS = STOCKAGENCE_LOTS::where('CreeLe', "<", "2023-11-01 00:00:00")
            ->leftJoin("STOCKAGENCE")
            ->where("LotTraiteGpharmaWeb", false)
            ->where("IDAGENCE", $IDAGENCE)
            ->orderBy("IDAGENCE")
            ->orderBy("CreeLe")
            ->orderBy("IDSTOCKAGENCE_LOTS")
            ->limit(5000)
            ->get();

        $IDZONESTOCKAGE_RESERVE = $ZONESTOCKAGE_RESERVE->IDZONESTOCKAGE;
        $IDZONESTOCKAGE_DEBALLE = $ZONESTOCKAGE_DEBALLE->IDZONESTOCKAGE;

        foreach ($STOCKAGENCE_LOTS as $key => $items) {

            DB::beginTransaction();

            Log::channel('logPerso')->info("[$key] IDSTOCKAGENCE_LOTS " . $items->IDSTOCKAGENCE_LOTS . " - CreeLe " . $items->CreeLe);

            if ($items->IDAGENCE == 1) {
                $items->IDZONESTOCKAGE = $IDZONESTOCKAGE_RESERVE;
                $items->save();

                $IDSTOCKAGENCE_LOTS_DEBALLE = $items->IDSTOCKAGENCE_LOTS;
                $IDSTOCKAGENCE_LOTS_RESERVE = $items->IDSTOCKAGENCE_LOTS;

                $IDZONESTOCKAGE_RESERVE = $IDZONESTOCKAGE_RESERVE;
                $IDZONESTOCKAGE_DEBALLE = null;
            } else {

                $items->IDZONESTOCKAGE = $IDZONESTOCKAGE_DEBALLE;
                $items->save();

                $items_reserve                      = $items->replicate();
                $items_reserve->StockTransit        = 0;
                $items_reserve->StockTotal_Lot      = 0;
                $items_reserve->StockTransitTIZ     = 0;
                $items_reserve->StockEnReception    = 0;
                $items_reserve->QteEnCoursVente     = 0;
                $items_reserve->QtecoursDeRoute_Lot = 0;
                $items_reserve->IDZONESTOCKAGE      = $IDZONESTOCKAGE_RESERVE;
                $items_reserve->ID_Old              = $items->IDSTOCKAGENCE_LOTS;
                $items_reserve->LotTraiteGpharmaWeb = true;
                // unset($items_reserve->IDSTOCKAGENCE_LOTS);
                // $items_reserve->IDSTOCKAGENCE_LOTS = null;
                $items_reserve->save();

                $IDSTOCKAGENCE_LOTS_DEBALLE = $items->IDSTOCKAGENCE_LOTS;
                $IDSTOCKAGENCE_LOTS_RESERVE = $items_reserve->IDSTOCKAGENCE_LOTS;
            }

            /**:::::::::::::::::::: BON LIVRAISON ::::::::::::::::::::::: */

            $BONDELIVRAISON = BONDELIVRAISON::select([
                "BONLIVRAISONLIGNE.IDZoneEntreeStock",
                "BONLIVRAISONLIGNE.ZoneEntreeStock",
                "BONLIVRAISONLIGNE.IDBONLIVRAISONLIGNE",
                "BONLIVRAISON.IDBONLIVRAISON",
            ])
                ->leftJoin("BONLIVRAISONLIGNE", "BONLIVRAISON.IDBONLIVRAISON", "BONLIVRAISONLIGNE.IDBONLIVRAISON")
                ->where("BONLIVRAISON.AgenceReceptionnee", $IDAGENCE)
                ->whereRaw('COALESCE("BONLIVRAISONLIGNE"."IDZoneEntreeStock", 0) = 0')
                ->where("BONLIVRAISONLIGNE.IDSTOCKAGENCE_LOTS", $IDSTOCKAGENCE_LOTS_DEBALLE)
                ->get();

            // Log::channel('logPerso')->info("BONDELIVRAISON " .  $BONDELIVRAISON->count());

            foreach ($BONDELIVRAISON as $key => $ITEM_BL) {
                if ($ITEM_BL->ZoneEntreeStock == 0 || $ITEM_BL->ZoneEntreeStock == 1) {

                    DB::table('BONLIVRAISONLIGNE')
                        ->where("IDBONLIVRAISONLIGNE", $ITEM_BL->IDBONLIVRAISONLIGNE)
                        ->update([
                            "IDZoneEntreeStock"  => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {
                    DB::table('BONLIVRAISONLIGNE')
                        ->where("IDBONLIVRAISONLIGNE", $ITEM_BL->IDBONLIVRAISONLIGNE)
                        ->update([
                            "IDZoneEntreeStock"  => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("BONDELIVRAISON terminee");

            /**:::::::::::::::::::: TRANSFERT INTER-SITE ::::::::::::::::::::::: */

            $IDSTL          = $items->IDSTOCKAGENCE_LOTS;
            $TRANSFERT_SITE = TRANSFERT_SITE::leftJoin('TRANSFERT_SITE_LIGNE', 'TRANSFERT_SITE.IDTRANSFERT_SITE', '=', 'TRANSFERT_SITE_LIGNE.IDTRANSFERT_SITE')
                ->where(function ($query) use ($IDAGENCE) {
                    $query->where('IDAGENCE', $IDAGENCE)
                        ->orWhere('IDAGENCEDESTINATION', $IDAGENCE);
                })
                ->where(function ($query) use ($IDSTL) {
                    $query->where('IDSTOCKAGENCE_LOTS', $IDSTL)
                        ->orWhere('IDSTOCKAGENCE_LOTS_RECEPTION', $IDSTL);
                })
                ->select(
                    'TRANSFERT_SITE_LIGNE.IDZONESTOCKAGE_EXPEDITION',
                    'TRANSFERT_SITE_LIGNE.IDZONESTOCKAGE_RECEPTION',
                    'TRANSFERT_SITE_LIGNE.IDTRANSFERT_SITE_LIGNE',
                    'TRANSFERT_SITE_LIGNE.ZonePrelevementTransfert',
                    'TRANSFERT_SITE_LIGNE.ZoneReceptionTransfert'
                )
                ->get();

            // Log::channel('logPerso')->info("TRANSFERT_SITE " .  $TRANSFERT_SITE->count());

            foreach ($TRANSFERT_SITE as $key => $ITEMS_TRS) {

                if ($ITEMS_TRS->ZonePrelevementTransfert == 0 || $ITEMS_TRS->ZonePrelevementTransfert == 1) {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS"        => $IDSTOCKAGENCE_LOTS_RESERVE,
                            "IDZONESTOCKAGE_EXPEDITION" => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDZONESTOCKAGE_EXPEDITION" => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS"        => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }

                if ($ITEMS_TRS->ZoneReceptionTransfert == 0 || $ITEMS_TRS->ZoneReceptionTransfert == 1) {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS_RECEPTION" => $IDSTOCKAGENCE_LOTS_RESERVE,
                            "IDZONESTOCKAGE_RECEPTION"     => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDZONESTOCKAGE_RECEPTION"     => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS_RECEPTION" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }
            Log::channel('logPerso')->info("TRANSFERT_SITE terminee");

            /**:::::::::::::::::::: TRANSFERT INTER ZONE ::::::::::::::::::::::: */

            $TRANSFERT_INTERNE_LIGNE = DB::table('TRANSFERT_INTERNE_LIGNE')
                ->where("IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->where("IDAGENCE", $IDAGENCE)
                ->get();

            // Log::channel('logPerso')->info("TRANSFERT_INTERNE_LIGNE " .  $TRANSFERT_INTERNE_LIGNE->count());

            foreach ($TRANSFERT_INTERNE_LIGNE as $key => $ITEMS_INTERNE_LIGNE) {

                $IDSTOCKAGENCE_LOTS_EXPEDITION = 0;
                $IDZONESTOCKAGE_EXPEDITION     = 0;
                $IDSTOCKAGENCE_LOTS_RECEPTION  = 0;
                $IDZONESTOCKAGE_RECEPTION      = 0;

                if ($ITEMS_INTERNE_LIGNE->RetourEnReserve == false) {
                    $IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_RESERVE;
                    $IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_RESERVE;
                    $IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_DEBALLE;
                    $IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_DEBALLE;
                    $StockAvantExpedition          = $ITEMS_INTERNE_LIGNE->StockReserve;
                } else {
                    $IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_DEBALLE;
                    $IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_DEBALLE;
                    $IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_RESERVE;
                    $IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_RESERVE;
                    $StockAvantExpedition          = $ITEMS_INTERNE_LIGNE->StockDetail;
                }

                if ($ITEMS_INTERNE_LIGNE->IDOperateurDepart <= 0) {
                    $StatutTransfert = 1;
                } else if ($ITEMS_INTERNE_LIGNE->IDOperateurDepart > 0 && $ITEMS_INTERNE_LIGNE->IDOperateurReception <= 0) {
                    $StatutTransfert = 2;
                } else {
                    $StatutTransfert = 3;
                }

                $TRANSFERT_INTER_ZONE                                = new TRANSFERT_INTER_ZONE();
                $TRANSFERT_INTER_ZONE->IDAGENCE                      = $ITEMS_INTERNE_LIGNE->IDAGENCE;
                $TRANSFERT_INTER_ZONE->IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_EXPEDITION;
                $TRANSFERT_INTER_ZONE->IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_RECEPTION;
                $TRANSFERT_INTER_ZONE->IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_EXPEDITION;
                $TRANSFERT_INTER_ZONE->IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_RECEPTION;
                $TRANSFERT_INTER_ZONE->StockAvantExpedition          = $StockAvantExpedition;
                $TRANSFERT_INTER_ZONE->QteTransferee                 = $ITEMS_INTERNE_LIGNE->QteTransferee;
                $TRANSFERT_INTER_ZONE->QteSuggeree                   = $ITEMS_INTERNE_LIGNE->QteATransferer;
                $TRANSFERT_INTER_ZONE->IDPRODUIT                     = $ITEMS_INTERNE_LIGNE->IDPRODUIT;
                $TRANSFERT_INTER_ZONE->IDEMPLACEMENT_EXPEDITION      = 0;
                $TRANSFERT_INTER_ZONE->IDEMPLACEMENT_RECEPTION       = 0;
                $TRANSFERT_INTER_ZONE->ID_Old                        = $ITEMS_INTERNE_LIGNE->IDTRANSFERT_INTERNE_LIGNE;

                $TRANSFERT_INTER_ZONE->ExpedieLe          = $ITEMS_INTERNE_LIGNE->DateHeureDepartTransfert;
                $TRANSFERT_INTER_ZONE->ExpediePar         = $ITEMS_INTERNE_LIGNE->IDOperateurDepart;
                $TRANSFERT_INTER_ZONE->ReceptionneLe      = $ITEMS_INTERNE_LIGNE->DateHeureReceptionTransfert;
                $TRANSFERT_INTER_ZONE->ReceptionnePar     = $ITEMS_INTERNE_LIGNE->IDOperateurReception;
                $TRANSFERT_INTER_ZONE->ReceptionWS        = $ITEMS_INTERNE_LIGNE->ReceptionWS;
                $TRANSFERT_INTER_ZONE->created_at         = $ITEMS_INTERNE_LIGNE->CreeLe;
                $TRANSFERT_INTER_ZONE->updated_at         = $ITEMS_INTERNE_LIGNE->ModifieLe;
                $TRANSFERT_INTER_ZONE->ModeTransfert      = $ITEMS_INTERNE_LIGNE->ModeDemandeTransfert;
                $TRANSFERT_INTER_ZONE->StatutTransfert    = $StatutTransfert;
                $TRANSFERT_INTER_ZONE->IDPaysProprietaire = $ITEMS_INTERNE_LIGNE->IDPaysProprietaire;
                $TRANSFERT_INTER_ZONE->CreeLe             = $ITEMS_INTERNE_LIGNE->CreeLe;
                $TRANSFERT_INTER_ZONE->CreePar            = $ITEMS_INTERNE_LIGNE->CreePar;
                $TRANSFERT_INTER_ZONE->ModifieLe          = $ITEMS_INTERNE_LIGNE->ModifieLe;
                $TRANSFERT_INTER_ZONE->ModifiePar         = $ITEMS_INTERNE_LIGNE->ModifiePar;
                $TRANSFERT_INTER_ZONE->save();
            }

            Log::channel('logPerso')->info("TRANSFERT_INTERNE_LIGNE terminee");

            /**:::::::::::::::::::: MOUVEMENT DE STOCKS ::::::::::::::::::::::: */

            $CORRECTION = CORRECTION::select([
                "ZoneStockageCorrigee",
                "IDZONESTOCKAGE",
                "IDSTOCKAGENCE_LOTS",
                "CORRECTION.IDCORRECTION",
                "CORRECTION_LIGNE.IDCORRECTION_LIGNE",
            ])
                ->leftJoin("CORRECTION_LIGNE", "CORRECTION.IDCORRECTION", "CORRECTION_LIGNE.IDCORRECTION")
                ->where("CORRECTION.IDAGENCE", $IDAGENCE)
                ->where("CORRECTION_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            // Log::channel('logPerso')->info("CORRECTION " .  $CORRECTION->count());

            foreach ($CORRECTION as $key => $ITEMS_COR) {
                if ($ITEMS_COR->ZoneStockageCorrigee == 0 || $ITEMS_COR->ZoneStockageCorrigee == 1) {
                    DB::table('CORRECTION_LIGNE')
                        ->where("IDCORRECTION_LIGNE", $ITEMS_COR->IDCORRECTION_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);

                    DB::table('CORRECTION')
                        ->where("IDCORRECTION", $ITEMS_COR->IDCORRECTION)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('CORRECTION_LIGNE')
                        ->where("IDCORRECTION_LIGNE", $ITEMS_COR->IDCORRECTION_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);

                    DB::table('CORRECTION')
                        ->where("IDCORRECTION", $ITEMS_COR->IDCORRECTION)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("CORRECTION terminee");

            /**:::::::::::::::::::: FACTURE CLIENT ::::::::::::::::::::::: */

            $FACTURECLIENT = DB::table('FACTURECLIENT')
                ->select([
                    "IDSTOCKAGENCE_LOTS",
                    "IDZONESTOCKAGE",
                    "ZoneFacturation",
                    "FACTURECLIENT_LIGNE.IDFACTURECLIENT_LIGNE",
                    "FACTURECLIENT.IDFACTURECLIENT",
                ])
                ->leftJoin("FACTURECLIENT_LIGNE", "FACTURECLIENT.IDFACTURECLIENT", "FACTURECLIENT_LIGNE.IDFACTURECLIENT")
                ->where("IDAGENCE", $IDAGENCE)
                ->where("IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->orderBy("DateFactureClient")
                ->orderBy("IDFACTURECLIENT")
                ->get();

            // Log::channel('logPerso')->info("FACTURECLIENT " .  $FACTURECLIENT->count());

            $IDFACTURECLIENT_OLD = "";

            foreach ($FACTURECLIENT as $key => $ITEMS_FC) {
                if ($ITEMS_FC->ZoneFacturation == 4) {

                    if ($ITEMS_FC->IDFACTURECLIENT != $IDFACTURECLIENT_OLD) {
                        DB::table('FACTURECLIENT')
                            ->where("IDFACTURECLIENT", $ITEMS_FC->IDFACTURECLIENT)
                            ->update([
                                "IDZONESTOCKAGE" => $IDZONESTOCKAGE_RESERVE,
                            ]);
                    }

                    DB::table('FACTURECLIENT_LIGNE')
                        ->where("IDFACTURECLIENT_LIGNE", $ITEMS_FC->IDFACTURECLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {
                    if ($ITEMS_FC->IDFACTURECLIENT != $IDFACTURECLIENT_OLD) {
                        DB::table('FACTURECLIENT')
                            ->where("IDFACTURECLIENT", $ITEMS_FC->IDFACTURECLIENT)
                            ->update([
                                "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                            ]);
                    }
                }

                $IDFACTURECLIENT_OLD = $ITEMS_FC->IDFACTURECLIENT;
            }

            Log::channel('logPerso')->info("FACTURECLIENT terminee");

            /**:::::::::::::::::::: RETOUR FSEUR ::::::::::::::::::::::: */

            $RETOURFSEUR = RETOURFSEUR::where("RETOURFSEUR.AgenceConcernee", $IDAGENCE)
                ->where("RETOURFSEUR_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->leftJoin("RETOURFSEUR_LIGNE", "RETOURFSEUR.IDRETOURFSEUR", "RETOURFSEUR_LIGNE.IDRETOURFSEUR")
                ->select("IDSTOCKAGENCE_LOTS", "ZoneEntreeStock", "IDRETOURFSEURLIGNE")
                ->get();

            // Log::channel('logPerso')->info("RETOURFSEUR " .  $RETOURFSEUR->count());

            foreach ($RETOURFSEUR as $key => $ITEMS_RTF) {
                if ($ITEMS_RTF->ZoneEntreeStock == 0 || $ITEMS_RTF->ZoneEntreeStock == 1) {
                    DB::table('RETOURFSEUR_LIGNE')
                        ->where("IDRETOURFSEURLIGNE", $ITEMS_RTF->IDRETOURFSEURLIGNE)
                        ->update([
                            "IDZONESTOCKAGE"     => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {

                    DB::table('RETOURFSEUR_LIGNE')
                        ->where("IDRETOURFSEURLIGNE", $ITEMS_RTF->IDRETOURFSEURLIGNE)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                            // "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("RETOURFSEUR terminee");

            /**:::::::::::::::::::: RETOUR CLIENT ::::::::::::::::::::::: */

            $RETOURCLIENT = DB::table('RETOURCLIENT')
                ->select([
                    "FACTURECLIENT.IDZONESTOCKAGE",
                    "FACTURECLIENT.ZoneFacturation",
                    "RETOURCLIENT_LIGNE.IDSTOCKAGENCE_LOTS",
                    "RETOURCLIENT_LIGNE.IDRETOURCLIENT_LIGNE",
                ])
                ->leftJoin("RETOURCLIENT_LIGNE", "RETOURCLIENT.IDRETOURCLIENT", "RETOURCLIENT_LIGNE.IDRETOURCLIENT")
                ->leftJoin("FACTURECLIENT_LIGNE", "RETOURCLIENT_LIGNE.IDFACTURECLIENTLIGNE", "FACTURECLIENT_LIGNE.IDFACTURECLIENT_LIGNE")
                ->leftJoin("FACTURECLIENT", "FACTURECLIENT_LIGNE.IDFACTURECLIENT", "FACTURECLIENT.IDFACTURECLIENT")
                ->where("FACTURECLIENT.IDAGENCE", $items->IDAGENCE)
                ->where("RETOURCLIENT_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            // Log::channel('logPerso')->info("RETOURCLIENT " .  $RETOURCLIENT->count());

            foreach ($RETOURCLIENT as $key => $ITEMS_RTC) {
                if ($ITEMS_RTC->ZoneFacturation == 4) {
                    DB::table('RETOURCLIENT_LIGNE')
                        ->where("IDRETOURCLIENT_LIGNE", $ITEMS_RTC->IDRETOURCLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {
                    DB::table('RETOURCLIENT_LIGNE')
                        ->where("IDRETOURCLIENT_LIGNE", $ITEMS_RTC->IDRETOURCLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("RETOURCLIENT terminee");

            /**:::::::::::::::::::: BLOCAGE LOT ::::::::::::::::::::::: */

            $STOCKAGENCE_LOTS_BLOQUE = STOCKAGENCE_LOTS_BLOQUE::where("IDAGENCE", $IDAGENCE)
                ->select("IDSTOCKAGENCE_LOTS", "ZoneStockageBloque", "IDSTOCKAGENCE_LOTS_BLOQUE")
                ->where("STOCKAGENCE_LOTS_BLOQUE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            Log::channel('logPerso')->info("STOCKAGENCE_LOTS_BLOQUE " . $STOCKAGENCE_LOTS_BLOQUE->count());

            foreach ($STOCKAGENCE_LOTS_BLOQUE as $key => $STA_LOTS_BLOQUE) {
                if ($STA_LOTS_BLOQUE->ZoneStockageBloque == 0 || $STA_LOTS_BLOQUE->ZoneStockageBloque == 1) {
                    DB::table('STOCKAGENCE_LOTS_BLOQUE')
                        ->where("IDSTOCKAGENCE_LOTS_BLOQUE", $STA_LOTS_BLOQUE->IDSTOCKAGENCE_LOTS_BLOQUE)
                        ->update([
                            "IDZONESTOCKAGE"     => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {

                    DB::table('STOCKAGENCE_LOTS_BLOQUE')
                        ->where("IDSTOCKAGENCE_LOTS_BLOQUE", $STA_LOTS_BLOQUE->IDSTOCKAGENCE_LOTS_BLOQUE)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("STOCKAGENCE_LOTS_BLOQUE terminee");

            $items->LotTraiteGpharmaWeb = true;
            $items->save();

            DB::commit();

            Log::channel('logPerso')->info("Site : " . $IDAGENCE . " Lot :" . $items->IDSTOCKAGENCE_LOTS);
        }

        return response()->json(["error" => false, "messages" => "Opération terminée avec success !"], 200);
    } catch (\Throwable $th) {
        DB::rollback();
        Log::channel('logPerso')->info($th->getMessage(), ["line" => $th->getLine(), "file" => $th->getFile()]);
        return response()->json(["error" => true, "messages" => "Une erreur est survenue !! "], 400);
    }
}

// 10/09/2023
public function traitementLots()
{
    try {

        $IDAGENCE = 2; // A remplacer par l'agence concernée

        $ZONESTOCKAGE_RESERVE = ZONESTOCKAGE::select("IDZONESTOCKAGE")
            ->where("ReservePrincipale", true)
            ->where("IDAGENCE", $IDAGENCE)
            ->first();

        $ZONESTOCKAGE_DEBALLE = ZONESTOCKAGE::select("IDZONESTOCKAGE")
            ->where("DeballePrincipal", true)
            ->where("IDAGENCE", $IDAGENCE)
            ->first();

        if ($ZONESTOCKAGE_RESERVE == null && $ZONESTOCKAGE_DEBALLE == null) {
            return response()->json(["error" => true, "messages" => "Merci de vérifier les zones de stockage !"], 500);
        }

        // $STOCKAGENCE_LOTS = STOCKAGENCE_LOTS::whereRaw('COALESCE("IDZONESTOCKAGE", 0) = 0')
        $STOCKAGENCE_LOTS = STOCKAGENCE_LOTS::where('CreeLe', "<", "2023-11-01 00:00:00")
            // ->leftJoin("STOCKAGENCE")
            ->where("LotTraiteGpharmaWeb", false)
            ->where("IDAGENCE", $IDAGENCE)
            ->where("IDSTOCKAGENCE_LOTS", 281474977343643)
            ->orderBy("IDAGENCE")
            ->orderBy("CreeLe")
            ->orderBy("IDSTOCKAGENCE_LOTS")
            ->limit(5000)
            ->get();

        $IDZONESTOCKAGE_RESERVE = $ZONESTOCKAGE_RESERVE->IDZONESTOCKAGE;
        $IDZONESTOCKAGE_DEBALLE = $ZONESTOCKAGE_DEBALLE->IDZONESTOCKAGE;

        foreach ($STOCKAGENCE_LOTS as $key => $items) {

            DB::beginTransaction();

            Log::channel('logPerso')->info("[$key] IDSTOCKAGENCE_LOTS " . $items->IDSTOCKAGENCE_LOTS . " - CreeLe " . $items->CreeLe);
            $Stockagence = Stockagence::find($items->IDSTOCKAGENCE);

            $ZoneStockageProduit = $Stockagence->ZoneStockageProduit < 0 ? 1 : $Stockagence->ZoneStockageProduit;

            if ($Stockagence != null && $ZoneStockageProduit > 1) {
                $IDZONESTOCKAGE_DEBALLE_OLD = $IDZONESTOCKAGE_DEBALLE;
                $IDZONESTOCKAGE_DEBALLE = DB::table("ZONESTOCKAGE")
                    ->select("IDZONESTOCKAGE")
                    ->where('IDAGENCE', $items->IDAGENCE)
                    ->where("TypeZoneStockage", $ZoneStockageProduit)
                    ->first()->IDZONESTOCKAGE ?? 0;

                if ($IDZONESTOCKAGE_DEBALLE == 0) {

                    switch ($items->IDAGENCE) {
                        case 2:
                            if ($ZoneStockageProduit == 4) {
                                $IDZONESTOCKAGE_DEBALLE = 17; // ZONE-01/VRI
                            } else if ($ZoneStockageProduit == 5) {
                                $IDZONESTOCKAGE_DEBALLE = 18; // ZONE-02/VRI
                            } else if ($ZoneStockageProduit == 6) {
                                $IDZONESTOCKAGE_DEBALLE = 19; // ZONE-03/VRI
                            }
                            break;
                        case 3:
                            if ($ZoneStockageProduit == 4) {
                                $IDZONESTOCKAGE_DEBALLE = 20; // ZONE-01/GNA
                            } else if ($ZoneStockageProduit == 5) {
                                $IDZONESTOCKAGE_DEBALLE = 21; // ZONE-02/GNA
                            } else if ($ZoneStockageProduit == 6) {
                                $IDZONESTOCKAGE_DEBALLE = 22; // ZONE-03/GNA
                            }
                            break;
                        case 4:
                            if ($ZoneStockageProduit == 4) {
                                $IDZONESTOCKAGE_DEBALLE = 23; // ZONE-01/BKE
                            } else if ($ZoneStockageProduit == 5) {
                                $IDZONESTOCKAGE_DEBALLE = 24; // ZONE-02/BKE
                            } else if ($ZoneStockageProduit == 6) {
                                $IDZONESTOCKAGE_DEBALLE = 25; // ZONE-03/BKE
                            }
                            break;
                        default:
                            $IDZONESTOCKAGE_DEBALLE = $IDZONESTOCKAGE_DEBALLE_OLD;
                            break;
                    }
                }
            }

            if ($items->IDAGENCE == 1) {
                
                $items->IDZONESTOCKAGE = $IDZONESTOCKAGE_RESERVE;
                $items->save();

                $IDSTOCKAGENCE_LOTS_DEBALLE = $items->IDSTOCKAGENCE_LOTS;
                $IDSTOCKAGENCE_LOTS_RESERVE = $items->IDSTOCKAGENCE_LOTS;

                $IDZONESTOCKAGE_RESERVE = $IDZONESTOCKAGE_RESERVE;
                $IDZONESTOCKAGE_DEBALLE = null;
            } else {

                
                $items->IDZONESTOCKAGE = $IDZONESTOCKAGE_DEBALLE;
                
                $items->save();

                $items_reserve                      = $items->replicate();
                $items_reserve->StockTransit        = 0;
                $items_reserve->StockTotal_Lot      = $items->StockReserve;
                $items_reserve->StockTransitTIZ     = 0; // a revoir
                $items_reserve->StockEnReception    = 0; // a revoir
                $items_reserve->QteEnCoursVente     = 0; // a revoir
                $items_reserve->QtecoursDeRoute_Lot = 0; // a revoir
                $items_reserve->IDZONESTOCKAGE      = $IDZONESTOCKAGE_RESERVE;
                $items_reserve->ID_Old              = $items->IDSTOCKAGENCE_LOTS;
                $items_reserve->LotTraiteGpharmaWeb = true;
                // unset($items_reserve->IDSTOCKAGENCE_LOTS);
                // $items_reserve->IDSTOCKAGENCE_LOTS = null;
                $items_reserve->save();

                $IDSTOCKAGENCE_LOTS_DEBALLE = $items->IDSTOCKAGENCE_LOTS;
                $IDSTOCKAGENCE_LOTS_RESERVE = $items_reserve->IDSTOCKAGENCE_LOTS;
            }

            /**:::::::::::::::::::: BON LIVRAISON ::::::::::::::::::::::: */

            $BONDELIVRAISON = BONDELIVRAISON::select([
                "BONLIVRAISONLIGNE.IDZoneEntreeStock",
                "BONLIVRAISONLIGNE.ZoneEntreeStock",
                "BONLIVRAISONLIGNE.IDBONLIVRAISONLIGNE",
                "BONLIVRAISON.IDBONLIVRAISON",
            ])
                ->leftJoin("BONLIVRAISONLIGNE", "BONLIVRAISON.IDBONLIVRAISON", "BONLIVRAISONLIGNE.IDBONLIVRAISON")
                ->where("BONLIVRAISON.AgenceReceptionnee", $IDAGENCE)
                ->whereRaw('COALESCE("BONLIVRAISONLIGNE"."IDZoneEntreeStock", 0) = 0')
                ->where("BONLIVRAISONLIGNE.IDSTOCKAGENCE_LOTS", $IDSTOCKAGENCE_LOTS_DEBALLE)
                ->get();

            // Log::channel('logPerso')->info("BONDELIVRAISON " .  $BONDELIVRAISON->count());

            foreach ($BONDELIVRAISON as $key => $ITEM_BL) {
                if ($ITEM_BL->ZoneEntreeStock == 0 || $ITEM_BL->ZoneEntreeStock == 1) {

                    DB::table('BONLIVRAISONLIGNE')
                        ->where("IDBONLIVRAISONLIGNE", $ITEM_BL->IDBONLIVRAISONLIGNE)
                        ->update([
                            "IDZoneEntreeStock"  => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {
                    DB::table('BONLIVRAISONLIGNE')
                        ->where("IDBONLIVRAISONLIGNE", $ITEM_BL->IDBONLIVRAISONLIGNE)
                        ->update([
                            "IDZoneEntreeStock"  => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("BONDELIVRAISON terminee");

            /**:::::::::::::::::::: TRANSFERT INTER-SITE ::::::::::::::::::::::: */

            $IDSTL          = $items->IDSTOCKAGENCE_LOTS;
            $TRANSFERT_SITE = TRANSFERT_SITE::leftJoin('TRANSFERT_SITE_LIGNE', 'TRANSFERT_SITE.IDTRANSFERT_SITE', '=', 'TRANSFERT_SITE_LIGNE.IDTRANSFERT_SITE')
                ->where(function ($query) use ($IDAGENCE) {
                    $query->where('IDAGENCE', $IDAGENCE)
                        ->orWhere('IDAGENCEDESTINATION', $IDAGENCE);
                })
                ->where(function ($query) use ($IDSTL) {
                    $query->where('IDSTOCKAGENCE_LOTS', $IDSTL)
                        ->orWhere('IDSTOCKAGENCE_LOTS_RECEPTION', $IDSTL);
                })
                ->select(
                    'TRANSFERT_SITE_LIGNE.IDZONESTOCKAGE_EXPEDITION',
                    'TRANSFERT_SITE_LIGNE.IDZONESTOCKAGE_RECEPTION',
                    'TRANSFERT_SITE_LIGNE.IDTRANSFERT_SITE_LIGNE',
                    'TRANSFERT_SITE_LIGNE.ZonePrelevementTransfert',
                    'TRANSFERT_SITE_LIGNE.ZoneReceptionTransfert'
                )
                ->get();

            // Log::channel('logPerso')->info("TRANSFERT_SITE " .  $TRANSFERT_SITE->count());

            foreach ($TRANSFERT_SITE as $key => $ITEMS_TRS) {

                if ($ITEMS_TRS->ZonePrelevementTransfert == 0 || $ITEMS_TRS->ZonePrelevementTransfert == 1) {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS"        => $IDSTOCKAGENCE_LOTS_RESERVE,
                            "IDZONESTOCKAGE_EXPEDITION" => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDZONESTOCKAGE_EXPEDITION" => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS"        => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }

                if ($ITEMS_TRS->ZoneReceptionTransfert == 0 || $ITEMS_TRS->ZoneReceptionTransfert == 1) {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS_RECEPTION" => $IDSTOCKAGENCE_LOTS_RESERVE,
                            "IDZONESTOCKAGE_RECEPTION"     => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('TRANSFERT_SITE_LIGNE')
                        ->where('IDTRANSFERT_SITE_LIGNE', $ITEMS_TRS->IDTRANSFERT_SITE_LIGNE)
                        ->update([
                            "IDZONESTOCKAGE_RECEPTION"     => $IDZONESTOCKAGE_DEBALLE,
                            "IDSTOCKAGENCE_LOTS_RECEPTION" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }
            Log::channel('logPerso')->info("TRANSFERT_SITE terminee");

            /**:::::::::::::::::::: TRANSFERT INTER ZONE ::::::::::::::::::::::: */

            $TRANSFERT_INTERNE_LIGNE = DB::table('TRANSFERT_INTERNE_LIGNE')
                ->where("IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->where("IDAGENCE", $IDAGENCE)
                ->get();

            // Log::channel('logPerso')->info("TRANSFERT_INTERNE_LIGNE " .  $TRANSFERT_INTERNE_LIGNE->count());

            foreach ($TRANSFERT_INTERNE_LIGNE as $key => $ITEMS_INTERNE_LIGNE) {

                $IDSTOCKAGENCE_LOTS_EXPEDITION = 0;
                $IDZONESTOCKAGE_EXPEDITION     = 0;
                $IDSTOCKAGENCE_LOTS_RECEPTION  = 0;
                $IDZONESTOCKAGE_RECEPTION      = 0;

                if ($ITEMS_INTERNE_LIGNE->RetourEnReserve == false) {
                    $IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_RESERVE;
                    $IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_RESERVE;
                    $IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_DEBALLE;
                    $IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_DEBALLE;
                    $StockAvantExpedition          = $ITEMS_INTERNE_LIGNE->StockReserve;
                } else {
                    $IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_DEBALLE;
                    $IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_DEBALLE;
                    $IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_RESERVE;
                    $IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_RESERVE;
                    $StockAvantExpedition          = $ITEMS_INTERNE_LIGNE->StockDetail;
                }

                if ($ITEMS_INTERNE_LIGNE->IDOperateurDepart <= 0) {
                    $StatutTransfert = 1;
                } else if ($ITEMS_INTERNE_LIGNE->IDOperateurDepart > 0 && $ITEMS_INTERNE_LIGNE->IDOperateurReception <= 0) {
                    $StatutTransfert = 2;
                } else {
                    $StatutTransfert = 3;
                }

                $TRANSFERT_INTER_ZONE                                = new TRANSFERT_INTER_ZONE();
                $TRANSFERT_INTER_ZONE->IDAGENCE                      = $ITEMS_INTERNE_LIGNE->IDAGENCE;
                $TRANSFERT_INTER_ZONE->IDZONESTOCKAGE_EXPEDITION     = $IDZONESTOCKAGE_EXPEDITION;
                $TRANSFERT_INTER_ZONE->IDZONESTOCKAGE_RECEPTION      = $IDZONESTOCKAGE_RECEPTION;
                $TRANSFERT_INTER_ZONE->IDSTOCKAGENCE_LOTS_EXPEDITION = $IDSTOCKAGENCE_LOTS_EXPEDITION;
                $TRANSFERT_INTER_ZONE->IDSTOCKAGENCE_LOTS_RECEPTION  = $IDSTOCKAGENCE_LOTS_RECEPTION;
                $TRANSFERT_INTER_ZONE->StockAvantExpedition          = $StockAvantExpedition;
                $TRANSFERT_INTER_ZONE->QteTransferee                 = $ITEMS_INTERNE_LIGNE->QteTransferee;
                $TRANSFERT_INTER_ZONE->QteSuggeree                   = $ITEMS_INTERNE_LIGNE->QteATransferer;
                $TRANSFERT_INTER_ZONE->IDPRODUIT                     = $ITEMS_INTERNE_LIGNE->IDPRODUIT;
                $TRANSFERT_INTER_ZONE->IDEMPLACEMENT_EXPEDITION      = 0;
                $TRANSFERT_INTER_ZONE->IDEMPLACEMENT_RECEPTION       = 0;
                $TRANSFERT_INTER_ZONE->ID_Old                        = $ITEMS_INTERNE_LIGNE->IDTRANSFERT_INTERNE_LIGNE;

                $TRANSFERT_INTER_ZONE->ExpedieLe          = $ITEMS_INTERNE_LIGNE->DateHeureDepartTransfert;
                $TRANSFERT_INTER_ZONE->ExpediePar         = $ITEMS_INTERNE_LIGNE->IDOperateurDepart;
                $TRANSFERT_INTER_ZONE->ReceptionneLe      = $ITEMS_INTERNE_LIGNE->DateHeureReceptionTransfert;
                $TRANSFERT_INTER_ZONE->ReceptionnePar     = $ITEMS_INTERNE_LIGNE->IDOperateurReception;
                $TRANSFERT_INTER_ZONE->ReceptionWS        = $ITEMS_INTERNE_LIGNE->ReceptionWS;
                $TRANSFERT_INTER_ZONE->created_at         = $ITEMS_INTERNE_LIGNE->CreeLe;
                $TRANSFERT_INTER_ZONE->updated_at         = $ITEMS_INTERNE_LIGNE->ModifieLe;
                $TRANSFERT_INTER_ZONE->ModeTransfert      = $ITEMS_INTERNE_LIGNE->ModeDemandeTransfert;
                $TRANSFERT_INTER_ZONE->StatutTransfert    = $StatutTransfert;
                $TRANSFERT_INTER_ZONE->IDPaysProprietaire = $ITEMS_INTERNE_LIGNE->IDPaysProprietaire;
                $TRANSFERT_INTER_ZONE->CreeLe             = $ITEMS_INTERNE_LIGNE->CreeLe;
                $TRANSFERT_INTER_ZONE->CreePar            = $ITEMS_INTERNE_LIGNE->CreePar;
                $TRANSFERT_INTER_ZONE->ModifieLe          = $ITEMS_INTERNE_LIGNE->ModifieLe;
                $TRANSFERT_INTER_ZONE->ModifiePar         = $ITEMS_INTERNE_LIGNE->ModifiePar;
                $TRANSFERT_INTER_ZONE->save();
            }

            Log::channel('logPerso')->info("TRANSFERT_INTERNE_LIGNE terminee");

            /**:::::::::::::::::::: MOUVEMENT DE STOCKS ::::::::::::::::::::::: */

            $CORRECTION = CORRECTION::select([
                "ZoneStockageCorrigee",
                "IDZONESTOCKAGE",
                "IDSTOCKAGENCE_LOTS",
                "CORRECTION.IDCORRECTION",
                "CORRECTION_LIGNE.IDCORRECTION_LIGNE",
            ])
                ->leftJoin("CORRECTION_LIGNE", "CORRECTION.IDCORRECTION", "CORRECTION_LIGNE.IDCORRECTION")
                ->where("CORRECTION.IDAGENCE", $IDAGENCE)
                ->where("CORRECTION_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            // Log::channel('logPerso')->info("CORRECTION " .  $CORRECTION->count());

            foreach ($CORRECTION as $key => $ITEMS_COR) {
                if ($ITEMS_COR->ZoneStockageCorrigee == 0 || $ITEMS_COR->ZoneStockageCorrigee == 1) {
                    DB::table('CORRECTION_LIGNE')
                        ->where("IDCORRECTION_LIGNE", $ITEMS_COR->IDCORRECTION_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);

                    DB::table('CORRECTION')
                        ->where("IDCORRECTION", $ITEMS_COR->IDCORRECTION)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_RESERVE,
                        ]);
                } else {
                    DB::table('CORRECTION_LIGNE')
                        ->where("IDCORRECTION_LIGNE", $ITEMS_COR->IDCORRECTION_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);

                    DB::table('CORRECTION')
                        ->where("IDCORRECTION", $ITEMS_COR->IDCORRECTION)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("CORRECTION terminee");

            /**:::::::::::::::::::: FACTURE CLIENT ::::::::::::::::::::::: */

            $FACTURECLIENT = DB::table('FACTURECLIENT')
                ->select([
                    "IDSTOCKAGENCE_LOTS",
                    "IDZONESTOCKAGE",
                    "ZoneFacturation",
                    "FACTURECLIENT_LIGNE.IDFACTURECLIENT_LIGNE",
                    "FACTURECLIENT.IDFACTURECLIENT",
                    "FACTURECLIENT.TypeRayons",
                ])
                ->leftJoin("FACTURECLIENT_LIGNE", "FACTURECLIENT.IDFACTURECLIENT", "FACTURECLIENT_LIGNE.IDFACTURECLIENT")
                ->where("IDAGENCE", $IDAGENCE)
                ->where("IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->where("IDZONESTOCKAGE", "<=", 0)
                ->orderBy("DateFactureClient")
                ->orderBy("IDFACTURECLIENT")
                ->get();

            // ListeAjoute(MoiMême,"DEBALLE"+gLien(1))
            // ListeAjoute(MoiMême,"FROID"+gLien(2))
            // ListeAjoute(MoiMême,"SOUS CLE"+gLien(3))
            // ListeAjoute(MoiMême,"RESERVE"+gLien(4))
            // ListeAjoute(MoiMême,"LAIT ET FARINE"+gLien(5))
            // ListeAjoute(MoiMême,"ZONE-01"+gLien(6))
            // ListeAjoute(MoiMême,"ZONE-02"+gLien(7))
            // ListeAjoute(MoiMême,"ZONE-03"+gLien(8))

            // Log::channel('logPerso')->info("FACTURECLIENT " .  $FACTURECLIENT->count());

            $IDFACTURECLIENT_OLD = "";

            foreach ($FACTURECLIENT as $key => $ITEMS_FC) {
                if ($ITEMS_FC->TypeRayons == 4) {

                    if ($ITEMS_FC->IDFACTURECLIENT != $IDFACTURECLIENT_OLD) {
                        DB::table('FACTURECLIENT')
                            ->where("IDFACTURECLIENT", $ITEMS_FC->IDFACTURECLIENT)
                            ->update([
                                "IDZONESTOCKAGE" => $IDZONESTOCKAGE_RESERVE,
                            ]);
                    }

                    DB::table('FACTURECLIENT_LIGNE')
                        ->where("IDFACTURECLIENT_LIGNE", $ITEMS_FC->IDFACTURECLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {

                    $zoneParTypeRayons = $this->zoneParTypeRayons($ITEMS_FC->TypeRayons, $IDAGENCE);
                    $IDZONESTOCKAGE_DEBALLE = $zoneParTypeRayons == 0 ? $IDZONESTOCKAGE_DEBALLE : $zoneParTypeRayons;

                    if ($ITEMS_FC->IDFACTURECLIENT != $IDFACTURECLIENT_OLD) {
                        DB::table('FACTURECLIENT')
                            ->where("IDFACTURECLIENT", $ITEMS_FC->IDFACTURECLIENT)
                            ->update([
                                "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                            ]);
                    }
                }

                $IDFACTURECLIENT_OLD = $ITEMS_FC->IDFACTURECLIENT;
            }

            Log::channel('logPerso')->info("FACTURECLIENT terminee");

            /**:::::::::::::::::::: RETOUR FSEUR ::::::::::::::::::::::: */

            $RETOURFSEUR = RETOURFSEUR::where("RETOURFSEUR.AgenceConcernee", $IDAGENCE)
                ->where("RETOURFSEUR_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->leftJoin("RETOURFSEUR_LIGNE", "RETOURFSEUR.IDRETOURFSEUR", "RETOURFSEUR_LIGNE.IDRETOURFSEUR")
                ->select("IDSTOCKAGENCE_LOTS", "ZoneEntreeStock", "IDRETOURFSEURLIGNE")
                ->get();

            // Log::channel('logPerso')->info("RETOURFSEUR " .  $RETOURFSEUR->count());

            foreach ($RETOURFSEUR as $key => $ITEMS_RTF) {
                if ($ITEMS_RTF->ZoneEntreeStock == 0 || $ITEMS_RTF->ZoneEntreeStock == 1) {
                    DB::table('RETOURFSEUR_LIGNE')
                        ->where("IDRETOURFSEURLIGNE", $ITEMS_RTF->IDRETOURFSEURLIGNE)
                        ->update([
                            "IDZONESTOCKAGE"     => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {

                    DB::table('RETOURFSEUR_LIGNE')
                        ->where("IDRETOURFSEURLIGNE", $ITEMS_RTF->IDRETOURFSEURLIGNE)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                            // "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("RETOURFSEUR terminee");

            /**:::::::::::::::::::: RETOUR CLIENT ::::::::::::::::::::::: */

            $RETOURCLIENT = DB::table('RETOURCLIENT')
                ->select([
                    "FACTURECLIENT.IDZONESTOCKAGE",
                    "FACTURECLIENT.ZoneFacturation",
                    "RETOURCLIENT_LIGNE.IDSTOCKAGENCE_LOTS",
                    "RETOURCLIENT_LIGNE.IDRETOURCLIENT_LIGNE",
                ])
                ->leftJoin("RETOURCLIENT_LIGNE", "RETOURCLIENT.IDRETOURCLIENT", "RETOURCLIENT_LIGNE.IDRETOURCLIENT")
                ->leftJoin("FACTURECLIENT_LIGNE", "RETOURCLIENT_LIGNE.IDFACTURECLIENTLIGNE", "FACTURECLIENT_LIGNE.IDFACTURECLIENT_LIGNE")
                ->leftJoin("FACTURECLIENT", "FACTURECLIENT_LIGNE.IDFACTURECLIENT", "FACTURECLIENT.IDFACTURECLIENT")
                ->where("FACTURECLIENT.IDAGENCE", $items->IDAGENCE)
                ->where("RETOURCLIENT_LIGNE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            // Log::channel('logPerso')->info("RETOURCLIENT " .  $RETOURCLIENT->count());

            foreach ($RETOURCLIENT as $key => $ITEMS_RTC) {
                if ($ITEMS_RTC->ZoneFacturation == 4) {
                    DB::table('RETOURCLIENT_LIGNE')
                        ->where("IDRETOURCLIENT_LIGNE", $ITEMS_RTC->IDRETOURCLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {
                    DB::table('RETOURCLIENT_LIGNE')
                        ->where("IDRETOURCLIENT_LIGNE", $ITEMS_RTC->IDRETOURCLIENT_LIGNE)
                        ->update([
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("RETOURCLIENT terminee");

            /**:::::::::::::::::::: BLOCAGE LOT ::::::::::::::::::::::: */

            $STOCKAGENCE_LOTS_BLOQUE = STOCKAGENCE_LOTS_BLOQUE::where("IDAGENCE", $IDAGENCE)
                ->select("IDSTOCKAGENCE_LOTS", "ZoneStockageBloque", "IDSTOCKAGENCE_LOTS_BLOQUE")
                ->where("STOCKAGENCE_LOTS_BLOQUE.IDSTOCKAGENCE_LOTS", $items->IDSTOCKAGENCE_LOTS)
                ->get();

            Log::channel('logPerso')->info("STOCKAGENCE_LOTS_BLOQUE " . $STOCKAGENCE_LOTS_BLOQUE->count());

            foreach ($STOCKAGENCE_LOTS_BLOQUE as $key => $STA_LOTS_BLOQUE) {
                if ($STA_LOTS_BLOQUE->ZoneStockageBloque == 0 || $STA_LOTS_BLOQUE->ZoneStockageBloque == 1) {
                    DB::table('STOCKAGENCE_LOTS_BLOQUE')
                        ->where("IDSTOCKAGENCE_LOTS_BLOQUE", $STA_LOTS_BLOQUE->IDSTOCKAGENCE_LOTS_BLOQUE)
                        ->update([
                            "IDZONESTOCKAGE"     => $IDZONESTOCKAGE_RESERVE,
                            "IDSTOCKAGENCE_LOTS" => $IDSTOCKAGENCE_LOTS_RESERVE,
                        ]);
                } else {

                    DB::table('STOCKAGENCE_LOTS_BLOQUE')
                        ->where("IDSTOCKAGENCE_LOTS_BLOQUE", $STA_LOTS_BLOQUE->IDSTOCKAGENCE_LOTS_BLOQUE)
                        ->update([
                            "IDZONESTOCKAGE" => $IDZONESTOCKAGE_DEBALLE,
                        ]);
                }
            }

            Log::channel('logPerso')->info("STOCKAGENCE_LOTS_BLOQUE terminee");

            $items->LotTraiteGpharmaWeb = true;
            $items->save();

            DB::commit();

            Log::channel('logPerso')->info("Site : " . $IDAGENCE . " Lot :" . $items->IDSTOCKAGENCE_LOTS);
        }

        return response()->json(["error" => false, "messages" => "Opération terminée avec success !"], 200);
    } catch (\Throwable $th) {
        DB::rollback();
        Log::channel('logPerso')->info($th->getMessage(), ["line" => $th->getLine(), "file" => $th->getFile()]);
        return response()->json(["error" => true, "messages" => "Une erreur est survenue !! "], 400);
    }
}