Sathira Sri Sathara
5158c52db5
- Introduced new endpoints for account overview, analytics, and metrics. - Implemented authorization matrix and backup/restore documentation. - Added smoke test script and updated package dependencies. - Created detailed production checklist and runbook for deployment. - Established cron operations and notification event matrix documentation. - Enhanced security and validation audits for analytics and metrics services. - Added unit tests for analytics and metrics functionalities.
13 lines
7.0 KiB
JavaScript
13 lines
7.0 KiB
JavaScript
const{Op,fn,col,literal}=require("sequelize"),db=require("../../models");const PAID=["PAID","PARTIALLY_REFUNDED","REFUNDED"],range=(query={},maxDays=366)=>{const to=query.to?new Date(`${query.to}T23:59:59.999Z`):new Date(),from=query.from?new Date(`${query.from}T00:00:00.000Z`):new Date(to.getTime()-29*86400000);if(Number.isNaN(+from)||Number.isNaN(+to)||from>to||to-from>maxDays*86400000)throw Object.assign(new Error(`Date range must be valid and at most ${maxDays} days`),{status:400,code:"INVALID_DATE_RANGE"});return{from,to,where:{[Op.between]:[from,to]}};},sum=x=>String(x||"0.00"),countBy=async(Model,column,where)=>Model.findAll({where,attributes:[column,[fn("COUNT",col(column)),"count"]],group:[column],raw:true});
|
|
async function overview(){const[orders,customers,businesses,products,openTickets,deliveries,lowStock]=await Promise.all([db.Order.count({where:{status:{[Op.ne]:"CANCELLED"}}}),db.User.count({where:{accountType:"customer",accountStatus:"ACTIVE"}}),db.BusinessCustomer.count({where:{status:"ACTIVE"}}),db.Product.count({where:{status:"ACTIVE",visibility:"PUBLIC"}}),db.SupportTicket.count({where:{status:{[Op.notIn]:["RESOLVED","CLOSED","CANCELLED"]}}}),db.Shipment.count({where:{status:{[Op.in]:["ASSIGNED","PICKUP_PENDING","PICKED_UP","IN_TRANSIT","OUT_FOR_DELIVERY"]}}}),db.InventoryBalance.count({where:{[Op.and]:[literal("on_hand - reserved > 0"),literal("on_hand - reserved <= low_stock_threshold")]}})]);const paid=await db.Payment.sum("amount",{where:{status:{[Op.in]:PAID}}});return{orders,paidRevenue:sum(paid),activeCustomers:customers,activeBusinessCustomers:businesses,activeProducts:products,lowStockProducts:lowStock,openSupportTickets:openTickets,activeDeliveries:deliveries};}
|
|
async function sales(q){const r=range(q),where={status:{[Op.in]:PAID},paid_at:r.where},[gross,refunds,orderCount,units]=await Promise.all([db.Payment.sum("amount",{where}),db.Refund.sum("amount",{where:{status:"COMPLETED",processed_at:r.where}}),db.Payment.count({where}),db.OrderItem.sum("quantity",{where:{createdAt:r.where}})]),g=Number(gross||0),f=Number(refunds||0);return{range:{from:r.from,to:r.to,timezone:"UTC"},grossPaidSales:g.toFixed(2),refunds:f.toFixed(2),netSales:(g-f).toFixed(2),paidPaymentCount:orderCount,averageOrderValue:(orderCount?g/orderCount:0).toFixed(2),unitsSold:Number(units||0),formula:"paid payment amount - completed refund amount"};}
|
|
async function orders(q){const r=range(q),where={createdAt:r.where},[byStatus,total,cancelled,returns,refunds]=await Promise.all([countBy(db.Order,"status",where),db.Order.count({where}),db.Order.count({where:{...where,status:"CANCELLED"}}),db.ReturnRequest.count({where:{requested_at:r.where}}),db.Refund.count({where:{requested_at:r.where}})]);return{range:r,byStatus,total,cancelled,cancellationRate:total?cancelled/total:0,returns,returnRate:total?returns/total:0,refunds,refundRate:total?refunds/total:0};}
|
|
async function customers(q){const r=range(q),[newCustomers,activeCustomers,retail,business]=await Promise.all([db.User.count({where:{createdAt:r.where,accountType:"customer"}}),db.Order.count({where:{createdAt:r.where,status:{[Op.ne]:"CANCELLED"},user_id:{[Op.ne]:null}},distinct:true,col:"user_id"}),db.User.count({where:{accountType:"customer",accountStatus:"ACTIVE"}}),db.BusinessCustomer.count({where:{status:"ACTIVE"}})]);return{range:r,newCustomers,activeCustomers,activeDefinition:"unique customers with a non-cancelled order in range",activeRetailAccounts:retail,activeBusinessAccounts:business};}
|
|
async function products(q){const r=range(q);return{range:r,topSelling:await db.OrderItem.findAll({where:{createdAt:r.where},attributes:["product_id",[fn("SUM",col("quantity")),"units"],[fn("SUM",col("line_total")),"revenue"]],group:["product_id"],order:[[literal("units"),"DESC"]],limit:20,raw:true}),lowStock:await db.InventoryBalance.findAll({where:{[Op.and]:[literal("on_hand - reserved > 0"),literal("on_hand - reserved <= low_stock_threshold")]},attributes:["variant_id","warehouse_id","on_hand","reserved","low_stock_threshold"],limit:100,raw:true})};}
|
|
async function inventory(){return{byWarehouse:await db.InventoryBalance.findAll({attributes:["warehouse_id",[fn("SUM",col("on_hand")),"onHand"],[fn("SUM",col("reserved")),"reserved"]],group:["warehouse_id"],limit:100,raw:true}),outOfStock:await db.InventoryBalance.count({where:literal("on_hand - reserved <= 0")}),valuation:null,valuationReason:"Cost of goods is not modeled"};}
|
|
async function payments(q){const r=range(q),[byStatus,byProvider,successfulAmount,refundAmount]=await Promise.all([countBy(db.Payment,"status",{createdAt:r.where}),countBy(db.Payment,"provider",{createdAt:r.where}),db.Payment.sum("amount",{where:{paid_at:r.where,status:{[Op.in]:PAID}}}),db.Refund.sum("amount",{where:{processed_at:r.where,status:"COMPLETED"}})]);return{range:r,byStatus,byProvider,successfulAmount:sum(successfulAmount),refundAmount:sum(refundAmount)};}
|
|
async function delivery(q){const r=range(q);return{range:r,byStatus:await countBy(db.Shipment,"status",{createdAt:r.where}),riderWorkload:await db.Shipment.findAll({where:{assigned_rider_id:{[Op.ne]:null},status:{[Op.notIn]:["DELIVERED","CANCELLED"]}},attributes:["assigned_rider_id",[fn("COUNT",col("id")),"active"]],group:["assigned_rider_id"],limit:100,raw:true})};}
|
|
async function loyalty(q){const r=range(q),[issued,redeemed,expired,accounts,tiers,rewards]=await Promise.all([db.LoyaltyLedgerEntry.sum("points_delta",{where:{occurred_at:r.where,type:"EARN"}}),db.LoyaltyLedgerEntry.sum("points_delta",{where:{occurred_at:r.where,type:"REDEEM"}}),db.LoyaltyLedgerEntry.sum("points_delta",{where:{occurred_at:r.where,type:"EXPIRE"}}),db.LoyaltyAccount.count({where:{status:"ACTIVE"}}),countBy(db.LoyaltyAccount,"current_tier_id",{}),db.LoyaltyRedemption.count({where:{redeemed_at:r.where,status:"COMPLETED"}})]);return{range:r,pointsIssued:Number(issued||0),pointsRedeemed:Math.abs(Number(redeemed||0)),pointsExpired:Math.abs(Number(expired||0)),activeAccounts:accounts,membershipDistribution:tiers,rewardRedemptions:rewards};}
|
|
async function support(q){const r=range(q),[byStatus,byPriority,breaches,agents]=await Promise.all([countBy(db.SupportTicket,"status",{createdAt:r.where}),countBy(db.SupportTicket,"priority",{createdAt:r.where}),db.SupportEscalation.count({where:{createdAt:r.where}}),db.SupportTicket.findAll({where:{assigned_agent_id:{[Op.ne]:null},status:{[Op.notIn]:["CLOSED","CANCELLED"]}},attributes:["assigned_agent_id",[fn("COUNT",col("id")),"tickets"]],group:["assigned_agent_id"],limit:100,raw:true})]);return{range:r,byStatus,byPriority,slaBreaches:breaches,agentWorkload:agents};}
|
|
async function recommendations(q){const r=range(q);return{range:r,byEventType:await countBy(db.ProductInteractionEvent,"event_type",{occurred_at:r.where}),topViewed:await db.ProductInteractionEvent.findAll({where:{occurred_at:r.where,event_type:"PRODUCT_VIEW"},attributes:["product_id",[fn("COUNT",col("id")),"views"]],group:["product_id"],order:[[literal("views"),"DESC"]],limit:20,raw:true}),conversionAttribution:null};}module.exports={range,overview,sales,orders,customers,products,inventory,payments,delivery,loyalty,support,recommendations};
|