Untitled
plain_text
19 days ago
7.0 kB
1
Indexable
Never
import { date, string, z } from "zod"; import { createTRPCRouter, publicProcedure, protectedProcedure, } from "@/server/api/trpc"; import { contactSchema } from "@/utils/data"; import axios from "axios"; const { google } = require("googleapis"); import type { Contact } from "@/utils/data"; import { authorize, SubArray } from "@/utils/sheetsAuth"; async function findRef(auth: any, input: Contact) { const sheets = google.sheets({ version: "v4", auth }); const res = await sheets.spreadsheets.values.get({ spreadsheetId: "1-24R-krNZHXwKrV0x_R45iAgNcMLs6CHW27u2SRTowY", range: "Sheet1", }); console.log(res); const rows = res.data.values; const resultIndex = rows.findIndex( (subArray: SubArray) => subArray[1] === input["Referral Partner Mobile Phone:"] ); const result = rows.find( (subArray: SubArray) => subArray[1] === input["Referral Partner Mobile Phone:"] ); if (resultIndex < 1) { let num = 0; if ( input["Lead Status"] === "Plan Canceled" || input["Lead Status"] === "Closed ACA & Life" || input["Lead Status"] === "Closed ACA And Life" || input["Lead Status"] === "Closed Life Only" || input["Lead Status"] === "Closed ACA & Life & Dental-Vision" || input["Lead Status"] === "Close Private Health & Life" || input["Lead Status"] === "Closed ACA Only" ) { num++; } const updateRes = await sheets.spreadsheets.values.append({ spreadsheetId: "1-24R-krNZHXwKrV0x_R45iAgNcMLs6CHW27u2SRTowY", range: "Sheet1", // Or where you need the data to go valueInputOption: "RAW", resource: { values: [ [ input["Referral Partner Full Name:"], input["Referral Partner Mobile Phone:"], 1, num, ], ], }, }); } else { let all = result[2]; let closed = result[3]; if ( (input["Lead Status"] === "Plan Canceled" || input["Lead Status"] === "Closed ACA & Life" || input["Lead Status"] === "Closed ACA And Life" || input["Lead Status"] === "Closed Life Only" || input["Lead Status"] === "Closed ACA & Life & Dental-Vision" || input["Lead Status"] === "Close Private Health & Life" || input["Lead Status"] === "Closed ACA Only") && result[3] ) { closed = (parseInt(result[3]) + 1).toString(); } if (result[2]) { all = (parseInt(result[2]) + 1).toString(); } console.log(all, closed, result, input["full_name"]); const range = `Sheet1!A${resultIndex + 1}:D${resultIndex + 1}`; const res = await sheets.spreadsheets.values.update({ spreadsheetId: "1-24R-krNZHXwKrV0x_R45iAgNcMLs6CHW27u2SRTowY", range: range, // Or where you need the data to go valueInputOption: "RAW", resource: { values: [ [ input["Referral Partner Full Name:"], input["Referral Partner Mobile Phone:"], all, closed, ], ], }, }); console.log(res.data); } return { resultIndex: resultIndex, rowData: result }; } async function appendRef(auth: any, input: Contact) { const sheets = google.sheets({ version: "v4", auth }); let num = 0; if ( input["Lead Status"] === "Plan Canceled" || input["Lead Status"] === "Closed ACA & Life" || input["Lead Status"] === "Closed ACA And Life" || input["Lead Status"] === "Closed Life Only" || input["Lead Status"] === "Closed ACA & Life & Dental-Vision" || input["Lead Status"] === "Close Private Health & Life" || input["Lead Status"] === "Closed ACA Only" ) { num++; } console.log(input); const res = await sheets.spreadsheets.values.append({ spreadsheetId: "1-24R-krNZHXwKrV0x_R45iAgNcMLs6CHW27u2SRTowY", range: "Sheet1", // Or where you need the data to go valueInputOption: "RAW", resource: { values: [ [ input["Referral Partner Full Name:"], input["Referral Partner Mobile Phone:"], 1, num, ], ], }, }); // const rows = res.data.values; // const result = rows.find((subArray: SubArray) => subArray[0] === "dyl"); } async function updateRef( auth: any, input: Contact, rowIndex: number, rowData: SubArray ) { const sheets = google.sheets({ version: "v4", auth }); console.log(rowData); let all = rowData[2]; let closed = rowData[3]; if ( (input["Lead Status"] === "Plan Canceled" || input["Lead Status"] === "Closed ACA & Life" || input["Lead Status"] === "Closed ACA And Life" || input["Lead Status"] === "Closed Life Only" || input["Lead Status"] === "Closed ACA & Life & Dental-Vision" || input["Lead Status"] === "Close Private Health & Life" || input["Lead Status"] === "Closed ACA Only") && rowData[3] ) { closed = (parseInt(rowData[3]) + 1).toString(); } if (rowData[2]) { all = (parseInt(rowData[2]) + 1).toString(); } console.log(all, closed, rowData); const range = `Sheet1!A${rowIndex + 1}:D${rowIndex + 1}`; const res = await sheets.spreadsheets.values.update({ spreadsheetId: "1-24R-krNZHXwKrV0x_R45iAgNcMLs6CHW27u2SRTowY", range: range, // Or where you need the data to go valueInputOption: "RAW", resource: { values: [ [ input["Referral Partner Full Name:"], input["Referral Partner Mobile Phone:"], all, closed, ], ], }, }); // const rows = res.data.values; // const result = rows.find((subArray: SubArray) => subArray[0] === "dyl"); } export const sheetRouter = createTRPCRouter({ masterSheet: publicProcedure .meta({ openapi: { method: "POST", path: "/ian", tags: ["ian"], summary: "Receives sheet", }, }) .input(contactSchema) .output(z.object({ greeting: z.string() })) .query(async ({ input, ctx }) => { console.log("test"); const data = input as Contact; const authClient = await authorize(); const { resultIndex, rowData } = await findRef(authClient, data); // if (resultIndex < 1) { // await appendRef(authClient, data).catch(console.error); // } else { // // await appendRef(authClient, data).catch(console.error); // await updateRef(authClient, data, resultIndex, rowData).catch( // console.error // ); // } // try { // const sheets = google.sheets({ version: "v4", authClient }); // const res = await sheets.spreadsheets.values.get({ // spreadsheetId: "102lJIWwzhAfu4keuL7a28WDNktSmhDUw8OG2rPe87pw", // range: "Sheet1", // }); // console.log(res); // console.log(res.context); // console.log(res.values); // } catch (error) { // console.log(error); // } return { greeting: "s" }; }), });