A opti (terrible code)

"use strict";
const ExcelJS = require("exceljs");

module.exports = ({ strapi }) => ({
  async getDropDownData() {
    let excel = strapi.config.get("excel");
    let dropDownValues = [];
    let array = Object.keys(excel?.config);

    strapi?.db?.config?.models?.forEach((element) => {
      if (element?.kind == "collectionType") {
        array?.forEach((data) => {
          if (element?.uid?.startsWith(data)) {
            dropDownValues.push({
              label: element?.info?.displayName,
              value: element?.uid,
            });
          }
        });
      }
    });
    // Sort dropDownValues alphabetically by label in ascending order
    dropDownValues.sort((a, b) => a.label.localeCompare(b.label));

    return {
      data: dropDownValues,
    };
  },
  async getTableData(ctx) {
    let excel = strapi.config.get("excel");
    let uid = ctx?.query?.uid;
    let limit = ctx?.query?.limit;
    let offset = ctx?.query?.offset;
    let query = await this.restructureObject(
      excel?.config[uid],
      uid,
      limit,
      offset
    );

    let response = await strapi.db.query(uid).findMany(query);

    let header = [
      ...excel?.config[uid]?.columns,
      ...Object.keys(excel?.config[uid]?.relation),
    ];

    let where = {};

    if (excel?.config[uid]?.locale == "true") {
      where = {
        locale: "en",
      };
    }

    let count = await strapi.db.query(uid).count(where);

    let tableData = await this.restructureData(response, excel?.config[uid]);

    // Sort dropDownValues alphabetically by label in ascending order

    return {
      data: tableData,
      count: count,
      columns: header,
    };
  },
  async downloadExcel(ctx) {
    try {
      let excel = strapi.config.get("excel");
      let uid = ctx?.query?.uid;
      let query = await this.restructureObject(excel?.config[uid], uid);
      let response = await strapi.db.query(uid).findMany(query);
      let excelData = await this.restructureData(response, excel?.config[uid]);

      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet("info commandes"); // Nouveau sheet

      const headers = [
        ...excel?.config[uid]?.columns,
        ...Object.keys(excel?.config[uid]?.relation).flatMap((relation) => {
          return excel?.config[uid]?.relation[relation].column.map((column) => {
            if (column === "quantity") return "Quantity";
            if (column === "produit") return "Produit";
            if (column === "createdAt") return "CreatedAt";
            return `${relation}_${column}`;
          });
        }),
      ];

      // Ajout titre au worksheet
      worksheet.columns = headers.map((header) => ({
        header,
        key: header,
        width: 20,
        style: {
          numFmt: header === "createdAt" ? "yyyy/mm/dd" : (header === "totalOrderAmount" ? "#,##0.00 €" : undefined)
        }
      }));

      // Ajout data au worksheet
      excelData.forEach((row) => {
        const produitValues = row["Produit"] ? row["Produit"].split(", ") : [];
        const quantityValues = row["Quantity"] ? row["Quantity"].split(", ") : [];

        const maxItemCount = Math.max(produitValues.length, quantityValues.length);

        for (let i = 0; i < maxItemCount; i++) {
          const newRow = {};

          headers.forEach((header) => {
            if (header === "Quantity" && quantityValues[i]) {
              newRow["Quantity"] = quantityValues[i];
            } else if (header === "Produit" && produitValues[i]) {
              newRow["Produit"] = produitValues[i];
            } else if (header === "createdAt" && row["createdAt"]) {
              // Convertion date
              newRow["createdAt"] = row["createdAt"].split("T")[0].replace(/-/g, "/"); //  .replace(/g, "\");
            } else {
              newRow[header] = row[header];
            }
          });

          worksheet.addRow(newRow);
        }
      });

      worksheet.eachRow({ includeEmpty: false }, function (row, rowNumber) {
        if (rowNumber > 1) { // PAS LE TITRE
          const currentCell = row.getCell("createdAt");
          if (typeof currentCell.value === "string") {
            const dateParts = currentCell.value.split("/");
            if (dateParts.length === 3) {
              const month = parseInt(dateParts[1], 10);
              let fillColor = "FFFFFFFF";
              if ([1, , 5, 7, 9, 11].includes(month)) fillColor = "EEECE1";


              row.eachCell({ includeEmpty: true, reverse: true }, function (cell, colNumber) {
                cell.fill = {
                  type: "pattern",
                  pattern: "solid",
                  fgColor: { argb: fillColor },
                };
                // Bordure cellule
                cell.border = {
                  top: { style: "thin", color: { argb: "FF000000" } },
                  left: { style: "thin", color: { argb: "FF000000" } },
                  bottom: { style: "thin", color: { argb: "FF000000" } },
                  right: { style: "thin", color: { argb: "FF000000" } }
                };
              });
            }
          }
        }
      });

// ---------------------------------------------------------- TOTAL STATS

const totalWorksheet = workbook.addWorksheet('Total Produits');
let months = {};

worksheet.eachRow({ includeEmpty: false }, function (row, rowNumber) {
  if (rowNumber > 1) { // pas le titre
    const currentCell = row.getCell("createdAt");
    if (typeof currentCell.value === "string") {
      const dateParts = currentCell.value.split("/");
      if (dateParts.length === 3) {
        const year = parseInt(dateParts[0], 10);
        const month = parseInt(dateParts[1], 10);
        months[year] = months[year] || {};
        months[year][month] = months[year][month] || {};
        months[year][month].products = months[year][month].products || new Set();
        months[year][month].quantities = months[year][month].quantities || {};
        const produits = row.getCell("Produit").value;
        const quantities = row.getCell("Quantity").value;
        if (produits && quantities) {
          const productArray = produits.split(", ");
          const quantityArray = quantities.split(", ");
          productArray.forEach((product, index) => {
            const quantity = parseInt(quantityArray[index], 10);
            months[year][month].products.add(product);
            months[year][month].quantities[product] = (months[year][month].quantities[product] || 0) + quantity;
          });
        }
      }
    }
  }
});

// vvvvv A OPTI mais fonctionne vvvvv

// Tri décroissant
const sortedYears = Object.keys(months).sort((a, b) => b - a);

function getFillColor(month, isHeader = false) {
  const colors = {
    1: "EEECE1",
    5: "EEECE1",
    7: "EEECE1",
    9: "EEECE1",
    11: "EEECE1"

  };

  return isHeader ? "D9D9D9" : (colors[month] || "FFFFFF");
}

const monthNames = {
  1: "janvier",
  2: "février",
  3: "mars",
  4: "avril",
  5: "mai",
  6: "juin",
  7: "juillet",
  8: "août",
  9: "septembre",
  10: "octobre",
  11: "novembre",
  12: "décembre"
};

let currentColumn = headers.length;
sortedYears.forEach(year => {
  const yearData = months[year];
  const sortedMonths = Object.keys(yearData).sort((a, b) => b - a);
  sortedMonths.forEach(month => {
    const monthName = monthNames[parseInt(month)];
    const productHeader = `${year}_${monthName}_Products`;
    const quantityHeader = `${year}_${monthName}_Quantities`;
    if (!headers.includes(productHeader)) {
      headers.push(productHeader);
      headers.push(quantityHeader);
      headers.push("");
      totalWorksheet.getColumn(currentColumn + 1).key = productHeader;
      totalWorksheet.getColumn(currentColumn + 2).key = quantityHeader;
      totalWorksheet.getColumn(currentColumn + 3).key = "";
      totalWorksheet.getColumn(currentColumn + 1).width = 20;
      totalWorksheet.getColumn(currentColumn + 2).width = 10;
      totalWorksheet.getCell(1, currentColumn + 1).fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: getFillColor(parseInt(month), true) }
      };
      totalWorksheet.getCell(1, currentColumn + 2).fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: getFillColor(parseInt(month), true) }
      };
    }
    totalWorksheet.getCell(1, currentColumn + 1).value = `${monthName} ${year} Produits`;
    totalWorksheet.getCell(1, currentColumn + 2).value = `Quantité`;
    const productArray = Array.from(yearData[month].products).sort(); // Tri par alphab
    productArray.forEach((product, index) => {
      const rowIndex = index + 2;
      totalWorksheet.getCell(rowIndex, currentColumn + 1).value = product;
      totalWorksheet.getCell(rowIndex, currentColumn + 2).value = yearData[month].quantities[product] || 0;


      totalWorksheet.getCell(rowIndex, currentColumn + 1).fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: getFillColor(parseInt(month)) }
      };

      totalWorksheet.getCell(rowIndex, currentColumn + 2).fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: getFillColor(parseInt(month)) }
      };
    });
    currentColumn += 3;
  });
});





// ------------------------------------ END TOTAL STATS


      // Write the workbook to a buffer
      const buffer = await workbook.xlsx.writeBuffer();

      return buffer;
    } catch (error) {
      console.error("Error writing buffer:", error);
    }
  },









  async restructureObject(inputObject, uid, limit, offset) {
    let excel = strapi.config.get("excel");

    let where = {};

    if (excel?.config[uid]?.locale == "true") {
      where = {
        locale: "en",
      };
    }
    let orderBy = {
      id: "asc",
    };

    const restructuredObject = {
      select: inputObject.columns || "*",
      populate: {},
      where,
      orderBy,
      limit: limit,
      offset: offset,
    };

    for (const key in inputObject.relation) {
      restructuredObject.populate[key] = {
        select: inputObject.relation[key].column,
      };
    }

    return restructuredObject;
  },
  async restructureData(data, objectStructure) {
    return data.map((item) => {
      const restructuredItem = {};

      // Restructure main data based on columns
      for (const key of objectStructure.columns) {
        if (key in item) {
          restructuredItem[key] = item[key];
        }
      }

      // Restructure relation data based on the specified structure
      for (const key in objectStructure.relation) {
        if (key in item && item[key]) {
          const relationData = item[key];
          if (Array.isArray(relationData) && relationData.length > 0) {
            // pas opti (mais fonctionne)
            relationData.forEach((obj, index) => {
              for (const column of objectStructure.relation[key].column) {
                if (column === 'quantity') {
                  if (!restructuredItem['Quantity']) {
                    restructuredItem['Quantity'] = `${obj[column]}`;
                  } else {
                    restructuredItem['Quantity'] += `, ${obj[column]}`;
                  }
                }
                if (column === 'produit') {
                  if (!restructuredItem['Produit']) {
                    restructuredItem['Produit'] = `${obj[column]}`;
                  } else {
                    restructuredItem['Produit'] += `, ${obj[column]}`;
                  }
                }
                restructuredItem[`${key}_${column}_${index + 1}`] = obj[column];
              }
            });
          } else {
            for (const column of objectStructure.relation[key].column) {
              if (column === 'produit') {
                restructuredItem['Produit'] = item[key][column];
              }
              if (column === 'quantity') {
                restructuredItem['Quantity'] = item[key][column];
              }
              restructuredItem[`${key}_${column}`] = item[key][column];
            }
          }
        }
      }

      return restructuredItem;
    });
  },
});