Untitled
unknown
plain_text
3 months ago
10 kB
3
Indexable
'use server'; import { prisma } from '@/lib/prisma'; import { Category } from '@/types/category'; import { Prisma } from '@prisma/client'; import axios from 'axios'; type Platform = 'GOOGLE' | 'META'; type SheetPlatform = 'Taboola' | 'Twitter'; function getOverAllSpendQuery(category: Category, from: string, to: string) { switch (category) { case 'Finance': return Prisma.sql` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a WHERE a."campaignName" ilike '%_TW%' and (a."campaignName" not ilike '%_HV%' or a."campaignName" not ilike '%_HL%' or a."campaignName" not ilike '%_AL%') and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp `; case 'Spirituality': return Prisma.sql` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a WHERE a."campaignName" ilike '%_AL%' --For Astro and a."campaignName" not like '%_TW%' and a."campaignName" not like '%_HV%' and a."campaignName" not like '%_HL%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp `; case 'HealoVed': return Prisma.sql` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a WHERE (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed and a."campaignName" not ilike '%_AL%' and a."campaignName" not ilike '%_TW%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp `; } } export async function overallSpend(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].Category_spend; } return 0; } catch (error) { console.error('Error in overallSpend', error); return 0; } } export async function categorySpend( from: string, to: string, category: Category, ) { try { const query = getOverAllSpendQuery(category, from, to); const data = await prisma.$queryRaw(query); prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error(error); return '0'; } } export async function spendPlatformFinance( from: string, to: string, platform: Platform, ): Promise<number> { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a WHERE a."campaignName" ilike '%_TW%' and (a."campaignName" not ilike '%_HV%' or a."campaignName" not ilike '%_HL%' or a."campaignName" not ilike '%_AL%') and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = ${platform}::"AdPlatformType" `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend; } return 0; } catch (error) { console.error(`Error in ${platform} Spend Finance`, error); return -1; } } export async function spendPlatformSpirituality( from: string, to: string, platform: Platform, ): Promise<number> { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a WHERE a."campaignName" ilike '%_AL%' --For Astro and a."campaignName" not like '%_TW%' and a."campaignName" not like '%_HV%' and a."campaignName" not like '%_HL%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = ${platform} :: "AdPlatformType" `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend; } return 0; } catch (error) { console.error(`Error in ${platform} Spend Spirituality`, error); return -1; } } export async function spendPlatformHealoVed( from: string, to: string, platform: Platform, ): Promise<number> { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed and a."campaignName" not ilike '%_AL%' and a."campaignName" not ilike '%_TW%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = ${platform} :: "AdPlatformType" `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return 0; } catch (error) { console.error(`Error in ${platform} Spend HealoVed`, error); return -1; } } export async function googlePaidSpendFinance(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where a."campaignName" ilike '%_TW%' and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and (a."campaignName" not ilike '%_HV%' or a."campaignName" not ilike '%_HL%' or a."campaignName" not ilike '%_AL%') and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'GOOGLE'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in googlePaidSpend', error); return '-1'; } } export async function facebookPaidSpendFinance(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where a."campaignName" ilike '%_TW%' and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and (a."campaignName" not ilike '%_HV%' or a."campaignName" not ilike '%_HL%' or a."campaignName" not ilike '%_AL%') and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'META'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in facebookPaidSpend', error); return '-1'; } } export async function googlePaidSpendSpirituality(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where a."campaignName" ilike '%_AL%' --For Astro and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and a."campaignName" not like '%_TW%' and a."campaignName" not like '%_HV%' and a."campaignName" not like '%_HL%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'GOOGLE'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in googlePaidSpendSpirituality', error); return '-1'; } } export async function facebookPaidSpendSpirituality(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where a."campaignName" ilike '%_AL%' --For Astro and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and a."campaignName" not like '%_TW%' and a."campaignName" not like '%_HV%' and a."campaignName" not like '%_HL%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'META'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in facebookPaidSpendSpirituality', error); return '-1'; } } export async function googlePaidSpendHealoVed(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and a."campaignName" not ilike '%_AL%' and a."campaignName" not ilike '%_TW%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'GOOGLE'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in googlePaidSpendHealoVed', error); return '-1'; } } export async function facebookPaidSpendHealoVed(from: string, to: string) { try { const data = await prisma.$queryRaw` SELECT SUM(a."spend") AS Category_spend FROM "AdStats" a Where (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%') and a."campaignName" not ilike '%_AL%' and a."campaignName" not ilike '%_TW%' and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp and a."platform" = 'META'; `; prisma.$disconnect(); if (Array.isArray(data) && data.length > 0) { return data[0].category_spend?.toFixed(0) || '0'; } return '0'; } catch (error) { console.error('Error in facebookPaidSpendHealoVed', error); return '-1'; } } export async function getSpendFromSheet( from: string, to: string, category: Category, platform: SheetPlatform, ) { const url = `${process.env.NEXT_PUBLIC_GOOGLE_SHEET_URL}?category=${category}&platform=${platform}&from=${from}&to=${to}`; try { const response = await axios.get(url); return response?.data?.totalSpend || '-1'; } catch (error) { console.error('Error in get Spend From Sheet', error); return '-1'; } }
Editor is loading...
Leave a Comment