asked 1 day ago
###### This is my code
const paymentDetails = async (req, res) => {
try {
const {
from_date = null,
to_date = null,
location = "",
provider = "",
date_option = null,
practice = "",
payerType = null,
dates = null,
showBal = null,
paymentMethod = null,
batch = null,
selectedType,
groupBy,
check_flag
} = req.query || {};
let payment_Method_Filter = paymentMethod?.value || null;
if (location?.id) {
// conditions.push(APSC.hospital_id = ${location?.id});
// Location_filter = location?.id;
}
if (provider) {
// conditions.push(APSC.provider_id = ${provider?.id}
);
// Provider_filter = provider?.id;
}
let clickedField = null;
let groupPayerType = null;
if (groupBy == "Payment Method") {
clickedField = selectedType;
} else if (groupBy == "Payer Type") {
groupPayerType = selectedType;
}
// const results = await sequelize.query(
// SELECT * FROM generate_payment_detail_report(
// :date_option::TEXT,
// :from_date::DATE,
// :to_date::DATE,
// :payment_method_option::TEXT,
// :payer_type_option::TEXT,
// :practice_id_option ::NUMERIC,
// :show_balance::TEXT,
// :param_batch_id::TEXT),
// {
// replacements: {
// date_option: dates || null,
// from_date: from_date || null,
// to_date: to_date || null,
// // practice_id: practice_id || null,
// param_batch_id: batch || null,
// payment_method_option:
// paymentMethod === "Other" || paymentMethod === "Unknown"
// ? paymentMethod
// : paymentMethod
// ? paymentMethod.toLowerCase()
// : "All",
// payer_type_option:
// payerType === "All"
// ? payerType
// : payerType
// ? payerType.toLowerCase()
// : null,
// show_balance: showBal || "All",
// practice_id_option: practice?.id || null,
// },
// type: sequelize.QueryTypes.SELECT,
// }
// );
const results = await sequelize.query(
`SELECT * FROM payment_detail_from_summary_with_flag(
:date_option::TEXT,
:from_date::DATE,
:to_date::DATE,
:payment_method_option::TEXT,
:payer_type_option::TEXT,
:practice_id_option ::NUMERIC,
:show_balance::TEXT,
:batch_option::TEXT,
:check_flag::BOOLEAN)`,
{
replacements: {
date_option: dates ? "Custom" : null,
from_date: from_date || null,
to_date: to_date || null,
// practice_id: practice_id || null,
batch_option: batch || null,
payment_method_option: clickedField
? clickedField
: paymentMethod
? payment_Method_Filter.toLowerCase()
: null,
payer_type_option: groupPayerType
? groupPayerType
: payerType === "All"
? payerType
: payerType
? payerType.toLowerCase()
: null,
show_balance: showBal || null,
practice_id_option: practice?.id || null,
check_flag: check_flag
},
type: sequelize.QueryTypes.SELECT,
}
);
const flexMonsterData = {
mapping: {
payment_type: {
type: "string",
caption: "Payment Type",
},
payment_id: { type: "string", caption: "Payment ID" },
post_date: {
type: "date string",
format: "MM-dd-yyyy",
caption: "Post Date",
},
payer_name: {
type: "string",
caption: "Payer Name",
},
ref_number: {
type: "string",
caption: "Ref No.",
},
notes: {
type: "string",
caption: "Notes",
},
amount: {
type: "number",
caption: "Applied Amount",
},
unapplied: { type: "number", label: "Unapplied" },
total_amount: { type: "number", label: "Total Amount" },
},
slice: {
rows: [
{ uniqueName: "payer_type", caption: "Payer Type" },
{ uniqueName: "payment_method", caption: "Payment Method" },
],
columns: [{ uniqueName: "[Measures]" }],
measures: [
{
uniqueName: "amount",
aggregation: "sum",
caption: "Applied Amount",
},
{
uniqueName: "unapplied",
aggregation: "sum",
caption: "Unapplied Amount",
},
{
uniqueName: "total_amount",
aggregation: "sum",
caption: "Total Amount",
},
],
drillThrough: [ // 👈 Predefined visible fields in drill-through
"payer_name",
"payment_type",
"payment_id",
"payer_type",
"payment_method",
"post_date",
"ref_number",
"total_amount",
"amount",
"unapplied"
],
},
options: {
viewType: "grid",
showAggregationLabels: false,
grid: {
type: "compact",
showTotals: false,
},
datePattern: "MM-dd-yyyy",
showFilter: false,
showHeaders: true,
configuratorButton: false,
showGrandTotals: true, // Disable grand totals
drillThrough: true,
drillThroughMaxRows: 1000, // Increase if needed
formats: [
{
name: "",
thousandsSeparator: ",",
decimalSeparator: ".",
decimalPlaces: 2,
nullValue: "-",
},
{
name: "currency",
currencySymbol: "$",
currencySymbolAlign: "left",
decimalPlaces: 2,
},
{
name: "date string",
format: "MM-dd-yyyy",
nullValue: "-",
applyTo: "post_date",
},
],
},
};
const data = map(results, (row) => ({
...row || {},
post_date: moment(row.post_date).format("MM-DD-YYYY"),
amount: formatNumber(row.amount),
unapplied: formatNumber(row.unapplied),
total_amount: formatNumber(row.total_amount)
}))
const HeaderData = {
PracticeName : practice?.practice_name || '',
fromDate : from_date || '',
toDate : to_date || '',
dateType : 'Posting Date',
}
return res.status(200).send({ data: data, flexMonsterData ,HeaderData : HeaderData});
} catch (error) {
console.log(error);
console.error("Error fetching report:", error.message);
return res.status(500).send({ message: "Internal Server Error", Error: error.message });
}
};
### Frontend Side
### Exported Excel
I’m encountering an issue with date formatting in exported Excel files using Flexmonster.
On the backend, I'm sending date strings in the format: MM-dd-yyyy (e.g., 07-11-2025).
On the Flexmonster grid (frontend), the dates are displayed correctly as MM-dd-yyyy.
However, when I export the grid to Excel, the dates in the Excel file appear as dd-MM-yyyy or dd/MM/yyyy, depending on the system's locale or Excel settings.
This mismatch between displayed format and exported format is causing confusion for end users.
What I’ve tried:
Explicitly setting the field type to "date string" in the mapping.
Confirming the correct format in the grid.
Using applyFormatting: false in the export config (no change to date format).
Expected behavior:
The exported Excel file should preserve the MM-dd-yyyy format as displayed in the grid.
Please let me know:
How can I enforce a consistent MM-dd-yyyy format in the exported Excel file?
Is there a setting or workaround to ensure exported date formats match the frontend view?
