Export csv for smart collection

Feature(s) impacted

import list of contacts on csv file

Observed behavior

Throws an error :
image
Log:


returns a 404 not found,
I already added a route on routes/contacts.js

Expected behavior

download csv file of list contacts.

Context

  • Project name: Clevermate
  • Environment name: TEST
  • Agent technology: nodejs
  • Agent (forest package) name & version: v9
  • Database type: postges

Hi @Adel_de_Clevermate,

Could you share the entire routes/contacts.js please and your app.js file :pray: ?

Hello @vince

Here is routes/contacts.js

const { RecordSerializer } = require("forest-express-sequelize");
const express = require("express");
const { PermissionMiddlewareCreator } = require("forest-express-sequelize");
const router = express.Router();
const { Op } = require("sequelize");
const axios = require("axios");
const {
  connections,
  cUserChilds,
  cUserTransactions,
  cUsers,
  cBesoins,
  cUsersConnections,
  cUsersConnectionsArchive,
  distancematrix,
  cMailsLog,
} = require("../models");
const models = require("../models");
const moment = require("moment");
const { Vonage } = require("@vonage/server-sdk");
const sgMail = require("@sendgrid/mail");

sgMail.setApiKey(process.env.SENDGRID_API_KEY_FOREST);

const sequelize = connections.default;
const permissionMiddlewareCreator = new PermissionMiddlewareCreator("cUsers");

function formaterPhoneNumber_(phone) {
  if (typeof phone !== "string") {
    return phone; // Return as is if not a string
  }

  // Remove inner spaces if they exist
  phone = phone.replace(/\s/g, "");

  if (phone.startsWith("0")) {
    phone = "33" + phone.substring(1);
  }

  return phone;
}

async function _sendSMS_(to, text) {
  const vonage = new Vonage({
    apiKey: process.env.NEXMO_KEY,
    apiSecret: process.env.NEXMO_SECRET,
  });
  await vonage.sms
    .send({
      to: formaterPhoneNumber_(to),
      from: process.env.NEXMO_SENDER,
      text: text + "\n\n\n",
      type: "text",
    })
    .then(async(resp) => {
      console.log("Message sent successfully");
      console.log(resp);
      await models.cMailsLog.create({
        emailFrom: process.env.NEXMO_SENDER,
        emailTo: formaterPhoneNumber_(to),
        templateId:"",
        emailId: "sms",
        emailVars: text,
        emailStatus: "OK",
        receiverId: null,
      });
    })
    .catch((err) => {
      console.log("There was an error sending the messages.");
      console.error(err);
    });
}

router.get("/contacts", (req, res, next) => {
  const limit = parseInt(req.query.page.size) || 20;
  const offset = (parseInt(req.query.page.number) - 1) * limit;
  const queryType = sequelize.QueryTypes.SELECT;
  let conditionSearch = "";

  if (req.query.search) {
    if (isNaN(req.query.search))
      conditionSearch = ` c_users.user_email LIKE '%${req.query.search.replace(
        /\'/g,
        "''"
      )}%' OR c_users.phone_1 LIKE '%${
        req.query.search
      }%' OR c_users.user_first_name LIKE '%${
        req.query.search
      }%' OR c_users.user_last_name LIKE '%${req.query.search}%' `;
    else
      conditionSearch = ` c_users.phone_1 LIKE '%${req.query.search}%' OR id= ${req.query.search}`;
  }

  if (req.query.filters) {
    const filters = JSON.parse(req.query.filters);

    // Make sure 'filters' has the required structure
    if (filters.field && filters.operator && filters.value !== undefined) {
        const { field, operator, value } = filters;

        // Adjust condition based on the operator
        if (operator === "equal") {
            conditionSearch += ` c_users.${field} = '${value}' `;
        } else if (operator === "not_equal") {
            conditionSearch += ` c_users.${field} != '${value}' `;
        } else if (operator === "like") {
            conditionSearch += ` c_users.${field} LIKE '%${value}%' `;
        } else if (operator === "greater_than") {
            conditionSearch += ` c_users.${field} > '${value}' `;
        } else if (operator === "less_than") {
            conditionSearch += ` c_users.${field} < '${value}' `;
        }
        // Add more conditions as needed for other operators
    }
}


 

  const isContactCondition = "user_tutor=0 and status='Client'"; //removed  and desinscrit_at is null
  const queryData = `
    SELECT *, ${contactCustomColumns}
    FROM c_users WHERE ${isContactCondition}
    ${conditionSearch ? `AND (${conditionSearch})` : ""}
    ORDER BY CASE WHEN c_users.due_date IS NOT NULL THEN 0 ELSE 1 END, c_users.due_date ASC, c_users.updated_at DESC
    LIMIT ${limit}
    OFFSET ${offset}
  `;

  const queryCount = `
    SELECT COUNT(*)
    FROM c_users WHERE ${isContactCondition}
      ${conditionSearch ? `AND ${conditionSearch}` : ""}
  `;

  Promise.all([
    sequelize.query(queryData, { type: queryType }),
    sequelize.query(queryCount, { type: queryType }),
  ])
    .then(async ([customerStatsList, customerStatsCount]) => {
      const customerStatsSerializer = new RecordSerializer({
        name: "contacts",
      });
      const customerStats = await customerStatsSerializer.serialize(
        customerStatsList
      );
      const count = customerStatsCount[0].count;
      res.send({ ...customerStats, meta: { count: count } });
    })
    .catch((err) => {
      console.log("error ==", err);
      next(err);
    });
});

router.get("/contacts/:recordId", async (req, res, next) => {
  const recordId = parseInt(req.params.recordId);
  const queryType = sequelize.QueryTypes.SELECT;

  const queryData = `
    SELECT *, ${contactCustomColumns}
    FROM c_users
    WHERE c_users.id=${recordId}
  `;

  try {
    const [recordInfos] = await sequelize.query(queryData, { type: queryType });

    const customerStatsSerializer = new RecordSerializer({ name: "contacts" });
    const customerStats = await customerStatsSerializer.serialize(recordInfos);

    res.send(customerStats);
  } catch (err) {
    next(err);
  }
});

router.put("/contacts/:recordId", (req, res, next) => {
  const recordId = parseInt(req.params.recordId);
  const queryType = sequelize.QueryTypes.UPDATE;
  var values = req.body.data.attributes;
  if (!Object.keys(values).length) return;

  if (Object.keys(values).includes("action") && values.action == null) {
    values = { ...values, due_date: null };
  }
  console.log("updating values ======", values);
  var params = {};
  const queryData = `
    UPDATE c_users set ${Object.keys(values)
      .map((k) => {
        let v = values[k];
        let attr = k;
        if (Object.keys(attributes_).includes(k)) attr = attributes_[k];
        if (typeof v === "string") {
          v = `${v}`;
        }
        params = { ...params, [attr]: v };
        return `${attr}=:${attr}`;
      })
      .toString()}
    WHERE c_users.id=${recordId}
  `;
  Promise.all([
    sequelize.query(queryData, { type: queryType, replacements: params }),
  ])
    .then(async (recordInfos) => {
      res.send(true);
    })
    .catch((err) => next(err));
});

//..
router.post(
  "/actions/addbesoin",
  // permissionMiddlewareCreator.smartAction(),
  async (req, res) => {
    let userId = req.body.data.attributes.ids[0];

    let attrs = req.body.data.attributes.values;
    const comment = attrs["Contraintes & Remarques"];
    const type = attrs["Format"];
    const niveau = attrs["Niveau"];
    const niveauTarifaire = attrs["Niveau tarifaire"];
    const autreMatieres = attrs["Autre matière"];
    const matieres_ =
      !!autreMatieres && autreMatieres.trim() != ""
        ? attrs["Matières"].concat(autreMatieres.split(","))
        : attrs["Matières"];
    const matieres = JSON.stringify(matieres_);
    const details = attrs["Détails"];
    const childId = attrs["Child ID"];
    const nbSeancesHebdo = attrs["Nombre de séances hebdomadaires"];
    const dureeSeance = attrs["Durée par séance (heures)"];
    const dureeEngag = attrs["Durée du suivi (mois)"];

    // let userId = undefined;
    // if (!childId) userId = user_id;

    const remus = {
      supérieur: { presentiel: 38, visio: 32 },
      superieur: { presentiel: 38, visio: 32 },
      lycee: { presentiel: 26, visio: 20 },
      lycée: { presentiel: 26, visio: 20 },
      college: { presentiel: 24, visio: 18 },
      collège: { presentiel: 24, visio: 18 },
      primaire: { presentiel: 22, visio: 16 },
    };
    if (!niveauTarifaire) {
      res.status(400).send({ error: "niveauTarifaire du besoin non spécifié" });
    }
    const remuPres = remus[niveauTarifaire.toLowerCase()].presentiel;
    const remuVisio = remus[niveauTarifaire.toLowerCase()].visio;

    models.cBesoins
      .create({
        comment,
        type,
        niveau,
        niveauTarifaire,
        matieres,
        details,
        childId,
        nbSeancesHebdo,
        dureeSeance,
        dureeEngag,
        userId,
        remuPres,
        remuVisio,
      })
      .then(() => {
        res.send({ success: "Besoin is created succefully" });
      });
  }
);


router.post(
  "/actions/set-ai-associe",
  // permissionMiddlewareCreator.smartAction(),
  async (req, res) => {
    let user_id = req.body.data.attributes.ids[0];
    let attrs = req.body.data.attributes.values;
    let email_ai = attrs["Email SAI"];

    try {
      // get the token
      const response = await executeUrl(
        "https://data.mongodb-api.com/app/apiais-xmjrj/endpoint/token",
        "POST",
        {
          keyPublic: process.env.SAI_PUBLIC_KEY,
          keyPrivate: process.env.SAI_PRIVATE_KEY,
        },
        {}
      );
      const token = response.data;

      console.log({ token });
      // const user = await models.cUsers.findByPk(user_id);
      // const email =user.dataValues.email;
      const response2 = await executeUrl(
        "https://data.mongodb-api.com/app/apiais-xmjrj/endpoint/customerUpdate",
        "POST",
        { token },
        {
          lineConsidered: {
            adresseMail: email_ai,
          },
          newValue: {
            cleExterneClient: user_id + "",
          },
        }
      );

      console.log("update response", response2);
      res.send({ success: "Info affected successfuly" });
    } catch (error) {
      res.status(400).send({ error: error.message });
      throw error;
    }
  }
);

// Export a list of Contacts
router.get(
  "/contacts.csv",
  permissionMiddlewareCreator.export(),
  (request, response, next) => {
    // Learn what this route does here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/routes/default-routes#export-a-list-of-records
    next();
  }
);

module.exports = router;

app.js :

const express = require('express');
const requireAll = require('require-all');
const path = require('path');
const cookieParser = require('cookie-parser');
const bodyParser = require('body-parser');
const cors = require('cors');
const jwt = require('express-jwt');
const morgan = require('morgan');
const {
  errorHandler,
  ensureAuthenticated,
  PUBLIC_ROUTES,
} = require('forest-express-sequelize');

const app = express();

let allowedOrigins = [/\.forestadmin\.com$/, /localhost:\d{4}$/];

if (process.env.CORS_ORIGINS) {
  allowedOrigins = allowedOrigins.concat(process.env.CORS_ORIGINS.split(','));
}

const corsConfig = {
  origin: allowedOrigins,
  maxAge: 86400, // NOTICE: 1 day
  credentials: true,
};

app.use(morgan('tiny'));
// Support for request-private-network as the `cors` package
// doesn't support it by default
// See: https://github.com/expressjs/cors/issues/236
app.use((req, res, next) => {
  if (req.headers['access-control-request-private-network']) {
    res.setHeader('access-control-allow-private-network', 'true');
  }
  next(null);
});
app.use('/forest/authentication', cors({
  ...corsConfig,
  // The null origin is sent by browsers for redirected AJAX calls
  // we need to support this in authentication routes because OIDC
  // redirects to the callback route
  origin: corsConfig.origin.concat('null')
}));
app.use(cors(corsConfig));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));



app.use('/forest', (request, response, next) => {
  if (PUBLIC_ROUTES.includes(request.url)) {
    return next();
  }
  return ensureAuthenticated(request, response, next);
});

requireAll({
  dirname: path.join(__dirname, 'routes'),
  recursive: true,
  resolve: (Module) => app.use('/forest', Module),
});

requireAll({
  dirname: path.join(__dirname, 'middlewares'),
  recursive: true,
  resolve: (Module) => Module(app),
});

app.use(errorHandler());

module.exports = app;

Okey could you put a console.log inside the /contacts.csv to make sure it passes inside.

But on the agent you are using, export must be implemented by you for smart collection.
You can check the following documentation which documents how it looks like on a non-smart collection.

The other solution would be to migrate to our new nodejs-agent which is way more powerful and easier to maintain.
You can find the documentation here on how to do the migration: Migrating legacy agents | Node.js Developer Guide

1 Like