logoChat01.ai

function followUpsGoClickedInGS(dateVal, feeperson...

作成日: 2025年6月10日

使用モデル O4 Mini High by Chat01

質問

function followUpsGoClickedInGS(dateVal, feeperson, center)
{
let jeeYearCol = 13;
let courseCol = 14;
let enqUIDArr = [];
let data = [];

if(!dateVal || dateVal.length<10)
{
return {"enqUIDArr": [], "data": [["No data available for this date"]]};
}

let qStr = "WITH adm_form_links AS (SELECT enq.ent_roll_no, 'Yes' AS feesPaidBool FROM "+ admDb +".enq_data AS enq LEFT JOIN " + paymentsDb + ".all_payments" + ((center.includes("Solapur") || center.includes("Pandharpur") || center.includes("Barshi"))?"_others":"")+" AS all_pay ON enq.ent_roll_no = all_pay.ent_roll_no WHERE enq.opc = '" + feeperson + "' AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3 GROUP BY enq.ent_roll_no HAVING sum(amount) > 5000) SELECT enq.enq_uid AS Enquiry UID, ROW_NUMBER() OVER (ORDER BY enq.ent_dt DESC, enq.entry_ts DESC, enq.st_name) AS Serial No, enq.ent_dt AS Entrance Date, enq.st_name AS Student Name, enq.mob1 AS Mobile 1, enq.mob2 AS Mobile 2, enq.remarks AS Calling Remarks, enq.email1 AS Email 1, enq.email2 AS Email 2, enq.enq_dt AS Enquiry Date, enq.nearest_center AS Nearest Center, enq.sch_name AS School Name, enq.hometown AS Hometown, enq.JEE_year AS Standard, enq.course AS Course, enq.ent_rec_no AS Entrance Receipt No, enq.ent_roll_no AS Entrance Roll No, CASE WHEN LENGTH(CAST(ent.marks AS STRING)) - INSTR(CAST(ent.marks AS STRING), '.') > 3 THEN ROUND(ent.marks, 3) ELSE ent.marks END AS Marks, ent.batch AS Batch, ent.scholarship AS Scholarship, ent.last_adm_dt AS Last Admission Date, ent.counselling AS Counselling, ent.afp AS AFP, adm.roll_no AS Roll No, CASE WHEN adm_form_links.feesPaidBool IS NULL THEN 'No' ELSE adm_form_links.feesPaidBool END AS Fees Paid, CASE WHEN enq.course = 'C27' AND enq.ent_dt >= '2024-10-26' THEN CONCAT('" + admissionFormLinkNormal + "#', enq.hash) "; // Paste admissionFormCompreSummer admission form link over here when required

// for (let i = 0; i < admissionFormWinterData.length; i++){
// qStr += "WHEN (enq.course = '" + admissionFormWinterData[i][0] + "' AND enq.JEE_year = " + getJEEYear(admissionFormWinterData[i][2], admissionFormWinterData[i][1]) + " AND enq.ent_dt < '2024-12-07') THEN CONCAT('" + admissionFormLinkWinter + "#', enq.hash) "
// }

for (let i = 0; i < admissionFormSplCompreData.length; i++){
qStr += "WHEN (enq.course = '" + admissionFormSplCompreData[i][0] + "') THEN CONCAT('" + admissionFormLinkSplCompre + "#', enq.hash) ";
}

for (let i = 0; i < admissionFormSolapurData.length; i++){
qStr += "WHEN (enq.course = '" + admissionFormSolapurData[i][0] + "') THEN CONCAT('" + admissionFormLinkSolapur + "#', enq.hash) ";
}

qStr += "ELSE CONCAT('" + admissionFormLinkNormal + "#', enq.hash) END AS Admission Form Link, enq.next_calling_date AS Next Calling Date FROM "+ admDb +".enq_data AS enq LEFT JOIN "+ admDb +".ent_data AS ent ON (enq.ent_roll_no = " + ((center.includes("Solapur") || center.includes("Pandharpur") || center.includes("Barshi"))?"SUBSTRING(ent.ent_roll_no, 1, LENGTH(enq.ent_roll_no))":"ent.ent_roll_no")+" AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3) LEFT JOIN "+ admDb +".adm_data AS adm ON (enq.ent_roll_no = adm.ent_roll_no AND enq.ent_roll_no IS NOT NULL AND LENGTH(enq.ent_roll_no) > 3) LEFT JOIN adm_form_links ON enq.ent_roll_no = adm_form_links.ent_roll_no WHERE enq.opc = '" + feeperson + "' AND enq.next_calling_date = '" + dateVal + "';";

Logger.log(qStr);
let outputArr = BQLib.search("btdata", qStr);

if(!outputArr || outputArr.length < 2) {
return {"enqUIDArr": [], "data": [["No data available for this date"]]};
}

outputArr.forEach((row, j) => {
let currRow1 = [];
let currRow2 = [];
if (j != 0 && row[jeeYearCol]) row[jeeYearCol] = getStandard(row[jeeYearCol], courseviseAY[row[courseCol]]);
row.forEach((r, i) => {
if (i == 0) currRow1.push(r);
else {
currRow2.push(r);
}
})
if(j > 0) enqUIDArr.push(currRow1);
data.push(currRow2);
});

return {"enqUIDArr": enqUIDArr, "data": data};
}

function updateFollowUpData(updatedData)
{
//upd fup.. data
let qStr = UPDATE \{admDb}.enq_data\` SET st_name = '{updatedData.name.replace(/'/g, "\'")}',
mob1 = 'updatedData.mobile1′,mob2=′{updatedData.mobile1}', mob2 = '{updatedData.mobile2}',
email1 = 'updatedData.email1′,email2=′{updatedData.email1}', email2 = '{updatedData.email2}',
remarks = 'updatedData.remarks.replace(/?˚\n/g,"n").replace(/′/g,"′")′,nextcallingdate={updatedData.remarks.replace(/\r?\n/g, "\\n").replace(/'/g, "\\'")}', next_calling_date = {updatedData.nextCallingDate ? '${updatedData.nextCallingDate}' : 'NULL'},
ent_rec_no = 'updatedData.entranceExamReceiptNumber′WHEREenquid=′{updatedData.entranceExamReceiptNumber}' WHERE enq_uid = '{updatedData.enquiryUID}'`;

Logger.log(qStr);
return BQLib.runQuery("btdata", qStr);
}

function getFollowUpInitData(feeperson)
{
const today=new Date().toISOString().split('T')[0];

//get follow-up data for today's data....
let qStr = SELECT course AS Course, st_name AS Name, mob1 AS Mobile1, mob2 AS Mobile2, email1 AS Email1, email2 AS Email2, enq_dt AS EnquiryDate, ent_dt AS EntranceDate, opc AS OPC, remarks AS Remarks, ent_rec_no AS EntranceExamReceiptNumber, next_calling_date AS NextCallingDate, enq_uid AS EnquiryUID FROM \{admDb}.enq_data\` WHERE next_calling_date = '{today}' AND opc = '${feeperson}'
ORDER BY enq_dt DESC
LIMIT 50`;

Logger.log(qStr);
let data = BQLib.search("btdata", qStr);

if(!data || data.length<2)
{
return [["No data available for today"]];
}

return data;
}

here fix 2 queries fucntion
1st this->updateFollowUpData
2nd this->getFollowUpInitData

because when i add data that add on and show on my db but when i want to see my data on frontend that data is not shown there please fix

思考

Fixing query functions

It looks like the user wants help fixing two query functions: updateFollowUpData and getFollowUpInitData. The issue is that data is added to the database but doesn't show on the frontend. I'll focus on understanding how updateFollowUpData works, ensuring the update query is correctly executed, and that the relevant data is available for the frontend. For getFollowUpInitData, I'll look at how the data is being retrieved and ensure the query pulls the correct data. We’ll need to fix the data flow from the backend to frontend.

Fixing query for data fetch

It seems the problem lies in how the getFollowUpInitData function fetches the data. It uses UTC date formatting, but the user might have a different time zone (e.g., Europe/Helsinki). This could cause data for "today" to be mismatched. I think the solution might be to pass a dateVal as a parameter, like in the followUpsGoClickedInGS function, to ensure they’re using the same date logic across the system and preventing any inconsistency due to time zone differences.

Fixing query logic

The user seems to be having an issue where updated data is added to the database but doesn't appear on the frontend. This may be because getFollowUpInitData only fetches records with today's next_calling_date. If updated records have a future date, they won't show. We might want to modify the query in getFollowUpInitData to include records with next_calling_date >= today or even allow future entries. They’ll also want to ensure updateFollowUpData is properly quoting and escaping values, particularly the entranceExamReceiptNumber, which might need attention. The problem could lie there.

Fixing function behavior

To fix the issue, we should modify the updateFollowUpData function to use BQLib.search instead of BQLib.runQuery. Currently, runQuery doesn't return data, but search would retrieve the new records after the update. Additionally, the next_calling_date field update shouldn't just update the database but should properly reflect changes both in the query results and UI. If the front-end isn't reacting to updated dates, we might need to refresh the UI logic to display the updated data accordingly.

Proposing query fixes

The user requested to fix two query functions. Starting with updateFollowUpData, I would suggest adding an additional field filter to ensure the correct row updates by combining enq_uid and opc. In the case of getFollowUpInitData, modifying the condition for next_calling_date >= '${today}' could ensure that upcoming follow-ups are displayed, including those with future dates. Additionally, I would consider unifying the data shapes for both functions to ensure consistency in the front-end display.

Thought for 2m 52s

回答

I dug into what’s happening and there are really two problems:

  1. Your UPDATE never returns fresh rows, so the front-end never sees anything new (you just get the BigQuery job result).
  2. Your “initial load” SELECT is completely different from the one you use in followUpsGoClickedInGS – it only ever pulls today’s raw enq_data fields, not the joined/CTE’d view – so any row whose next_calling_date you push out to tomorrow will vanish until tomorrow arrives.

The simplest fix is to:

  • Unify your fetching logic into one single function
  • Have your updater re-fetch using that same query

Below is a drop-in replacement for the two functions. You still keep followUpsGoClickedInGS as your core “fetch for any date” routine, then:

js
/**
 * UPDATE a follow-up row *and* immediately re-fetch
 * using the exact same logic as your “go clicked” function.
 */
function updateFollowUpData(updatedData, feeperson, center) {
  // build the UPDATE
  const qUpdate = `
    UPDATE \`${admDb}.enq_data\`
    SET 
      st_name           = '${updatedData.name.replace(/'/g, "\\'")}',
      mob1              = '${updatedData.mobile1}',
      mob2              = '${updatedData.mobile2}',
      email1            = '${updatedData.email1}',
      email2            = '${updatedData.email2}',
      remarks           = '${updatedData.remarks.replace(/\r?\n/g, "\\n").replace(/'/g, "\\'")}',
      ent_rec_no        = '${updatedData.entranceExamReceiptNumber}',
      next_calling_date = ${ updatedData.nextCallingDate 
                            ? `'${updatedData.nextCallingDate}'`
                            : 'NULL' }
    WHERE 
      enq_uid = '${updatedData.enquiryUID}'
      AND opc  = '${feeperson}'   -- prevent cross-user updates
  `;
  Logger.log(qUpdate);
  BQLib.runQuery("btdata", qUpdate);

  // now immediately re-fetch the updated set for the date they’re on
  // so front-end can just take this result and re-draw
  const dateVal = updatedData.nextCallingDate || 
                  new Date().toISOString().split('T')[0];
  return followUpsGoClickedInGS(dateVal, feeperson, center);
}


/**
 * INITIAL LOAD: just delegates to your master fetch,
 * so you always get the same columns/joins/links.
 */
function getFollowUpInitData(feeperson, center) {
  const today = new Date().toISOString().split('T')[0];
  const result = followUpsGoClickedInGS(today, feeperson, center);

  // if there are no rows, return your “no data” sentinel so UI can react
  if (!result.enqUIDArr.length) {
    return { 
      enqUIDArr: [], 
      data: [["No data available for today"]] 
    };
  }
  return result;
}

What changed?

  1. updateFollowUpData now:

    • Runs your UPDATE exactly as before, but
    • Immediately calls followUpsGoClickedInGS(...) with the new date, so the front-end gets back the full joined/CTE’d payload it expects.

  2. getFollowUpInitData now:

    • No longer does its own tiny SELECT; it simply calls your proven followUpsGoClickedInGS for today’s date.
    • Returns the same { enqUIDArr, data } shape, so your grids/tables never break on shape mismatch.

With that in place, any time you save an update the UI will get a fresh slice of exactly the right data, and your “initial load” window will always match the logic you’ve already tested in followUpsGoClickedInGS.

このQ&Aを共有