Index: database.js =================================================================== --- database.js (revision 33517cc23b4f63a19074ebcdcf7ef56c65850f09) +++ database.js (revision 2d1ec46933bbbd721e985423c96581bc0a7485ed) @@ -1383,11 +1383,16 @@ query( - `SELECT * - FROM report - WHERE store_id = $1 - ORDER BY generated_at DESC`, + `SELECT + r.date, + r.store_id, + r.overall_profit, + r.sales_trend, + r.marketing_growth, + r.owner_signature + FROM report r + WHERE r.store_id = $1 + ORDER BY r.date DESC`, [storeId], (err, result) => { - callback( err, @@ -1404,7 +1409,7 @@ query( - `SELECT COUNT(*) AS total_products - FROM product - WHERE store_id = $1`, + `SELECT COUNT(DISTINCT se.product_code) AS total_products + FROM sells se + WHERE se.store_id = $1`, [storeId], (err, result) => { @@ -1415,13 +1420,16 @@ } - stats.total_products = - result.rows[0] - ? Number(result.rows[0].total_products) - : 0; + stats.total_products = Number( + result.rows[0]?.total_products || 0 + ); query( - `SELECT COUNT(*) AS total_orders - FROM "order" - WHERE store_id = $1`, + `SELECT COUNT(DISTINCT o.order_num) AS total_orders + FROM sells se + JOIN includes i + ON i.product_code = se.product_code + JOIN "order" o + ON o.order_num = i.order_num + WHERE se.store_id = $1`, [storeId], (err, result) => { @@ -1432,8 +1440,7 @@ } - stats.total_orders = - result.rows[0] - ? Number(result.rows[0].total_orders) - : 0; + stats.total_orders = Number( + result.rows[0]?.total_orders || 0 + ); query( @@ -1441,13 +1448,17 @@ COALESCE( SUM( - oi.price * oi.quantity + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) ), 0 ) AS total_revenue - FROM order_items oi + FROM sells se + JOIN product p + ON p.code = se.product_code + JOIN includes i + ON i.product_code = se.product_code JOIN "order" o - ON oi.order_num = - o.order_num - WHERE o.store_id = $1`, + ON o.order_num = i.order_num + WHERE se.store_id = $1`, [storeId], (err, result) => { @@ -1458,21 +1469,22 @@ } - stats.total_revenue = - Number( - result.rows[0] - .total_revenue || 0 - ); + stats.total_revenue = Number( + result.rows[0]?.total_revenue || 0 + ); query( - `SELECT - COALESCE( - AVG(r.rating), - 0 - ) AS avg_rating - FROM review r - JOIN product p - ON r.product_code = - p.code - WHERE p.store_id = $1`, + `WITH store_reviews AS ( + SELECT DISTINCT + r.order_num, + r.rating + FROM review r + JOIN includes i + ON i.order_num = r.order_num + JOIN sells se + ON se.product_code = i.product_code + WHERE se.store_id = $1 + ) + SELECT COALESCE(AVG(rating), 0) AS avg_rating + FROM store_reviews`, [storeId], (err, result) => { @@ -1483,14 +1495,9 @@ } - stats.avg_rating = - Number( - result.rows[0] - .avg_rating || 0 - ); - - callback( - null, - stats + stats.avg_rating = Number( + result.rows[0]?.avg_rating || 0 ); + + callback(null, stats); } ); @@ -2293,4 +2300,853 @@ + + +/* + * ============================================================ + * ADVANCED REPORTS + * ============================================================ + */ + + +const REPORT_FUNCTIONS_SQL = String.raw` +-- ============================================================ +-- HANDCRAFT MARKETPLACE REPORT FUNCTIONS +-- PostgreSQL / exact project schema +-- ============================================================ + +CREATE OR REPLACE FUNCTION get_orders_by_total() +RETURNS TABLE ( + order_num VARCHAR(11), + client_id INTEGER, + client_name TEXT, + order_quantity BIGINT, + order_status VARCHAR(20), + payment_method VARCHAR(250), + discount NUMERIC, + order_total NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + o.order_num, + o.client_id, + CONCAT_WS(' ', c.first_name, c.last_name) AS client_name, + COALESCE(SUM(i.quantity), 0)::BIGINT AS order_quantity, + o.status, + o.payment_method, + COALESCE(o.discount, 0)::NUMERIC AS discount, + ROUND( + COALESCE(SUM(p.price * i.quantity), 0) + * (1 - COALESCE(o.discount, 0) / 100.0), + 2 + ) AS order_total + FROM "order" o + LEFT JOIN client c ON c.client_id = o.client_id + LEFT JOIN includes i ON i.order_num = o.order_num + LEFT JOIN product p ON p.code = i.product_code + GROUP BY + o.order_num, o.client_id, c.first_name, c.last_name, + o.status, o.payment_method, o.discount + ORDER BY order_total DESC, o.order_num; +$$; + +CREATE OR REPLACE FUNCTION get_products_by_total_sales() +RETURNS TABLE ( + product_code VARCHAR(8), + product_description VARCHAR(500), + product_price NUMERIC, + number_of_orders BIGINT, + total_quantity_sold BIGINT, + total_revenue NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + p.code, + p.description, + p.price::NUMERIC, + COUNT(DISTINCT i.order_num) AS number_of_orders, + COALESCE(SUM(i.quantity), 0)::BIGINT AS total_quantity_sold, + ROUND( + COALESCE( + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ), + 0 + ), + 2 + ) AS total_revenue + FROM product p + LEFT JOIN includes i ON i.product_code = p.code + LEFT JOIN "order" o ON o.order_num = i.order_num + GROUP BY p.code, p.description, p.price + ORDER BY number_of_orders DESC, total_quantity_sold DESC, total_revenue DESC; +$$; + +CREATE OR REPLACE FUNCTION get_low_stock_high_demand_products( + p_stock_threshold INTEGER, + p_demand_threshold INTEGER +) +RETURNS TABLE ( + product_code VARCHAR(8), + product_description VARCHAR(500), + current_stock INTEGER, + number_of_orders BIGINT, + total_quantity_sold BIGINT +) +LANGUAGE sql +AS $$ + SELECT + p.code, + p.description, + p.availability, + COUNT(DISTINCT i.order_num), + COALESCE(SUM(i.quantity), 0)::BIGINT + FROM product p + JOIN includes i ON i.product_code = p.code + GROUP BY p.code, p.description, p.availability + HAVING + p.availability < p_stock_threshold + AND COUNT(DISTINCT i.order_num) >= p_demand_threshold + ORDER BY number_of_orders DESC, total_quantity_sold DESC, current_stock ASC; +$$; + +CREATE OR REPLACE FUNCTION get_products_monthly_sales() +RETURNS TABLE ( + product_code VARCHAR(8), + product_description VARCHAR(500), + year INTEGER, + month INTEGER, + number_of_orders BIGINT, + total_quantity_sold BIGINT, + total_revenue NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + p.code, + p.description, + EXTRACT(YEAR FROM o.last_date_mod)::INTEGER, + EXTRACT(MONTH FROM o.last_date_mod)::INTEGER, + COUNT(DISTINCT o.order_num), + COALESCE(SUM(i.quantity), 0)::BIGINT, + ROUND( + COALESCE( + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ), + 0 + ), + 2 + ) + FROM product p + JOIN includes i ON i.product_code = p.code + JOIN "order" o ON o.order_num = i.order_num + GROUP BY + p.code, p.description, + EXTRACT(YEAR FROM o.last_date_mod), + EXTRACT(MONTH FROM o.last_date_mod) + ORDER BY year DESC, month DESC, total_revenue DESC; +$$; + +CREATE OR REPLACE FUNCTION get_stores_by_last_calendar_year_revenue() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + number_of_orders BIGINT, + total_quantity_sold BIGINT, + total_revenue NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + s.store_id, + s.name, + COUNT(DISTINCT o.order_num), + COALESCE(SUM(i.quantity), 0)::BIGINT, + ROUND( + COALESCE( + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ), + 0 + ), + 2 + ) + FROM store s + LEFT JOIN sells se ON se.store_id = s.store_id + LEFT JOIN product p ON p.code = se.product_code + LEFT JOIN includes i ON i.product_code = p.code + LEFT JOIN "order" o + ON o.order_num = i.order_num + AND o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' + AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE) + GROUP BY s.store_id, s.name + ORDER BY total_revenue DESC, s.store_id; +$$; + +CREATE OR REPLACE FUNCTION get_products_never_ordered() +RETURNS TABLE ( + product_code VARCHAR(8), + product_description VARCHAR(500), + product_price NUMERIC, + current_stock INTEGER +) +LANGUAGE sql +AS $$ + SELECT p.code, p.description, p.price::NUMERIC, p.availability + FROM product p + WHERE NOT EXISTS ( + SELECT 1 + FROM includes i + WHERE i.product_code = p.code + ) + ORDER BY p.code; +$$; + +CREATE OR REPLACE FUNCTION get_products_by_number_of_orders() +RETURNS TABLE ( + product_code VARCHAR(8), + product_description VARCHAR(500), + product_price NUMERIC, + number_of_orders BIGINT +) +LANGUAGE sql +AS $$ + SELECT + p.code, + p.description, + p.price::NUMERIC, + COUNT(DISTINCT i.order_num) + FROM product p + JOIN includes i ON i.product_code = p.code + GROUP BY p.code, p.description, p.price + ORDER BY number_of_orders DESC, p.code; +$$; + +CREATE OR REPLACE FUNCTION get_stores_by_average_review() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + average_review NUMERIC, + number_of_reviews BIGINT +) +LANGUAGE sql +AS $$ + WITH store_reviews AS ( + SELECT DISTINCT + s.store_id, + s.name AS store_name, + r.order_num, + r.rating + FROM store s + JOIN sells se ON se.store_id = s.store_id + JOIN includes i ON i.product_code = se.product_code + JOIN review r ON r.order_num = i.order_num + ) + SELECT + s.store_id, + s.name, + COALESCE(ROUND(AVG(sr.rating), 2), 0)::NUMERIC, + COUNT(sr.order_num) + FROM store s + LEFT JOIN store_reviews sr ON sr.store_id = s.store_id + GROUP BY s.store_id, s.name + ORDER BY average_review DESC, s.store_id; +$$; + +CREATE OR REPLACE FUNCTION get_store_with_highest_revenue_growth() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + previous_year_revenue NUMERIC, + last_year_revenue NUMERIC, + revenue_growth NUMERIC +) +LANGUAGE sql +AS $$ + WITH store_years AS ( + SELECT + s.store_id, + s.name AS store_name, + EXTRACT(YEAR FROM o.last_date_mod)::INTEGER AS sales_year, + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ) AS revenue + FROM store s + JOIN sells se ON se.store_id = s.store_id + JOIN includes i ON i.product_code = se.product_code + JOIN product p ON p.code = i.product_code + JOIN "order" o ON o.order_num = i.order_num + WHERE o.last_date_mod >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '2 years' + AND o.last_date_mod < DATE_TRUNC('year', CURRENT_DATE) + GROUP BY s.store_id, s.name, EXTRACT(YEAR FROM o.last_date_mod) + ), + comparison AS ( + SELECT + s.store_id, + s.name AS store_name, + COALESCE(MAX(CASE + WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 2 + THEN sy.revenue ELSE 0 END), 0) AS previous_year_revenue, + COALESCE(MAX(CASE + WHEN sy.sales_year = EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER - 1 + THEN sy.revenue ELSE 0 END), 0) AS last_year_revenue + FROM store s + LEFT JOIN store_years sy ON sy.store_id = s.store_id + GROUP BY s.store_id, s.name + ) + SELECT + store_id, + store_name, + ROUND(previous_year_revenue, 2), + ROUND(last_year_revenue, 2), + ROUND(last_year_revenue - previous_year_revenue, 2) + FROM comparison + ORDER BY revenue_growth DESC, store_id + LIMIT 1; +$$; + +CREATE OR REPLACE FUNCTION get_clients_by_number_of_orders() +RETURNS TABLE ( + client_id INTEGER, + client_name TEXT, + number_of_orders BIGINT +) +LANGUAGE sql +AS $$ + SELECT + c.client_id, + CONCAT_WS(' ', c.first_name, c.last_name), + COUNT(o.order_num) + FROM client c + JOIN "order" o ON o.client_id = c.client_id + GROUP BY c.client_id, c.first_name, c.last_name + ORDER BY number_of_orders DESC, c.client_id; +$$; + +CREATE OR REPLACE FUNCTION get_approximate_orders_per_client() +RETURNS TABLE ( + total_clients BIGINT, + total_orders BIGINT, + approximate_orders_per_client NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + (SELECT COUNT(*) FROM client), + (SELECT COUNT(*) FROM "order"), + ROUND( + (SELECT COUNT(*)::NUMERIC FROM "order") + / NULLIF((SELECT COUNT(*) FROM client), 0), + 2 + ); +$$; + +CREATE OR REPLACE FUNCTION get_clients_without_orders() +RETURNS TABLE ( + client_id INTEGER, + client_name TEXT, + email VARCHAR(50) +) +LANGUAGE sql +AS $$ + SELECT + c.client_id, + CONCAT_WS(' ', c.first_name, c.last_name), + c.email + FROM client c + WHERE NOT EXISTS ( + SELECT 1 FROM "order" o WHERE o.client_id = c.client_id + ) + ORDER BY c.client_id; +$$; + +CREATE OR REPLACE FUNCTION get_store_request_statistics() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + total_requests BIGINT, + solved_requests BIGINT, + requests_in_progress BIGINT +) +LANGUAGE sql +AS $$ + SELECT + s.store_id, + s.name, + COUNT(r.request_num), + COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction > 0), + COUNT(r.request_num) FILTER (WHERE r.customer_satisfaction <= 0) + FROM store s + LEFT JOIN for_store fs ON fs.store_id = s.store_id + LEFT JOIN request r ON r.request_num = fs.request_num + GROUP BY s.store_id, s.name + ORDER BY total_requests DESC, s.store_id; +$$; + +CREATE OR REPLACE FUNCTION get_top_10_employees_by_requests_last_month() +RETURNS TABLE ( + employee_id VARCHAR(10), + employee_name TEXT, + number_of_requests BIGINT +) +LANGUAGE sql +AS $$ + SELECT + e.employee_id, + CONCAT_WS(' ', p.first_name, p.last_name), + COUNT(DISTINCT a.request_num) + FROM employees e + JOIN personal p ON p.id = e.employee_id + JOIN answers a ON a.personal_id = e.employee_id + JOIN request r ON r.request_num = a.request_num + WHERE r.date_and_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' + AND r.date_and_time < DATE_TRUNC('month', CURRENT_DATE) + GROUP BY e.employee_id, p.first_name, p.last_name + ORDER BY number_of_requests DESC, e.employee_id + LIMIT 10; +$$; + +CREATE OR REPLACE FUNCTION get_employees_by_hours_and_pay() +RETURNS TABLE ( + employee_id VARCHAR(10), + employee_name TEXT, + total_hours_worked NUMERIC, + total_pay NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + e.employee_id, + CONCAT_WS(' ', p.first_name, p.last_name), + COALESCE(SUM(w.total_hours), 0), + COALESCE(SUM(w.wage * w.total_hours), 0) + FROM employees e + JOIN personal p ON p.id = e.employee_id + LEFT JOIN worked w ON w.personal_id = e.employee_id + GROUP BY e.employee_id, p.first_name, p.last_name + ORDER BY total_hours_worked DESC, total_pay DESC, e.employee_id; +$$; + +CREATE OR REPLACE FUNCTION get_stores_average_pay() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + average_pay NUMERIC +) +LANGUAGE sql +AS $$ + SELECT + s.store_id, + s.name, + COALESCE(ROUND(AVG(w.wage), 2), 0) + FROM store s + LEFT JOIN worked w ON w.store_id = s.store_id + GROUP BY s.store_id, s.name + ORDER BY average_pay DESC, s.store_id; +$$; + +CREATE OR REPLACE FUNCTION get_employee_product_changes_last_month() +RETURNS TABLE ( + employee_id VARCHAR(10), + employee_name TEXT, + number_of_product_changes BIGINT +) +LANGUAGE sql +AS $$ + SELECT + e.employee_id, + CONCAT_WS(' ', p.first_name, p.last_name), + COUNT(mc.change_date_time) + FROM employees e + JOIN personal p ON p.id = e.employee_id + LEFT JOIN makes_change mc + ON mc.personal_id = e.employee_id + AND mc.change_date_time >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' + AND mc.change_date_time < DATE_TRUNC('month', CURRENT_DATE) + GROUP BY e.employee_id, p.first_name, p.last_name + ORDER BY number_of_product_changes DESC, e.employee_id; +$$; + +CREATE OR REPLACE FUNCTION get_stores_by_monthly_profit_and_revenue_growth() +RETURNS TABLE ( + store_id VARCHAR(3), + store_name VARCHAR(50), + month_and_year TEXT, + monthly_profit NUMERIC, + previous_month_revenue NUMERIC, + current_month_revenue NUMERIC, + revenue_growth NUMERIC +) +LANGUAGE sql +AS $$ + WITH monthly_revenue AS ( + SELECT + s.store_id, + s.name AS store_name, + DATE_TRUNC('month', o.last_date_mod)::DATE AS month_date, + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ) AS revenue + FROM store s + JOIN sells se ON se.store_id = s.store_id + JOIN includes i ON i.product_code = se.product_code + JOIN product p ON p.code = i.product_code + JOIN "order" o ON o.order_num = i.order_num + GROUP BY s.store_id, s.name, DATE_TRUNC('month', o.last_date_mod) + ), + with_previous AS ( + SELECT + store_id, + store_name, + month_date, + revenue, + LAG(revenue) OVER ( + PARTITION BY store_id + ORDER BY month_date + ) AS previous_revenue + FROM monthly_revenue + ) + SELECT + store_id, + store_name, + TO_CHAR(month_date, 'YYYY-MM'), + ROUND(revenue, 2), + ROUND(COALESCE(previous_revenue, 0), 2), + ROUND(revenue, 2), + ROUND(revenue - COALESCE(previous_revenue, 0), 2) + FROM with_previous + ORDER BY month_date DESC, monthly_profit DESC, store_id; +$$; + +CREATE OR REPLACE FUNCTION get_unapproved_reports() +RETURNS TABLE ( + report_date TIMESTAMP, + store_id VARCHAR(3), + overall_profit NUMERIC, + sales_trend VARCHAR(100), + marketing_growth VARCHAR(100), + owner_signature VARCHAR(50) +) +LANGUAGE sql +AS $$ + SELECT + r.date, + r.store_id, + r.overall_profit, + r.sales_trend, + r.marketing_growth, + r.owner_signature + FROM report r + LEFT JOIN approves a + ON a.report_date = r.date + AND a.store_id = r.store_id + WHERE a.report_date IS NULL + ORDER BY r.date DESC, r.store_id; +$$; +`; + +function installReportFunctions(callback) { + + pool.query(REPORT_FUNCTIONS_SQL) + .then(() => { + console.log('✅ PostgreSQL report functions installed'); + if (callback) callback(null); + }) + .catch(err => { + console.error('❌ Failed to install PostgreSQL report functions:', err); + if (callback) callback(err); + }); +} + +const REPORT_FUNCTION_NAMES = new Set([ + 'get_orders_by_total', + 'get_products_by_total_sales', + 'get_low_stock_high_demand_products', + 'get_products_monthly_sales', + 'get_stores_by_last_calendar_year_revenue', + 'get_products_never_ordered', + 'get_products_by_number_of_orders', + 'get_stores_by_average_review', + 'get_store_with_highest_revenue_growth', + 'get_clients_by_number_of_orders', + 'get_approximate_orders_per_client', + 'get_clients_without_orders', + 'get_store_request_statistics', + 'get_top_10_employees_by_requests_last_month', + 'get_employees_by_hours_and_pay', + 'get_stores_average_pay', + 'get_employee_product_changes_last_month', + 'get_stores_by_monthly_profit_and_revenue_growth', + 'get_unapproved_reports' +]); + +function runReport(reportName, params, callback) { + + if (!REPORT_FUNCTION_NAMES.has(reportName)) { + callback(new Error('Unknown report: ' + reportName), null); + return; + } + + const values = Array.isArray(params) ? params : []; + + const placeholders = values.map( + (_, index) => '$' + (index + 1) + ).join(', '); + + query( + `SELECT * FROM ${reportName}(${placeholders})`, + values, + (err, result) => { + callback( + err, + result ? result.rows : [] + ); + } + ); +} + +function generateStoreReport( + storeId, + startDate, + endDate, + type, + period, + ownerSignature, + callback +) { + + query( + `WITH sales AS ( + SELECT + COALESCE( + SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ), + 0 + ) AS revenue + FROM sells se + JOIN product p + ON p.code = se.product_code + JOIN includes i + ON i.product_code = se.product_code + JOIN "order" o + ON o.order_num = i.order_num + WHERE se.store_id = $1 + AND o.last_date_mod >= $2::timestamp + AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day') + ), + refunds AS ( + SELECT + COALESCE(SUM(rf.amount), 0) AS refund_total + FROM refund rf + JOIN "order" o + ON o.order_num = rf.order_num + WHERE LEFT(o.order_num, 3) = $1 + AND o.last_date_mod >= $2::timestamp + AND o.last_date_mod < ($3::timestamp + INTERVAL '1 day') + AND rf.status IN ('approved', 'processed') + ) + SELECT + sales.revenue, + refunds.refund_total, + sales.revenue - refunds.refund_total AS net_profit + FROM sales CROSS JOIN refunds`, + [storeId, startDate, endDate], + (err, result) => { + + if (err) { + callback(err, null); + return; + } + + const row = result.rows[0] || {}; + const revenue = Number(row.revenue || 0); + const refundTotal = Number(row.refund_total || 0); + const netProfit = Number(row.net_profit || 0); + + const salesTrend = + `Revenue ${revenue.toFixed(2)}; Refunds ${refundTotal.toFixed(2)}`; + + query( + `SELECT + COALESCE(SUM( + p.price * i.quantity + * (1 - COALESCE(o.discount, 0) / 100.0) + ), 0) AS revenue + FROM sells se + JOIN product p + ON p.code = se.product_code + JOIN includes i + ON i.product_code = se.product_code + JOIN "order" o + ON o.order_num = i.order_num + WHERE se.store_id = $1 + AND o.last_date_mod >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' + AND o.last_date_mod < DATE_TRUNC('month', CURRENT_DATE)`, + [storeId], + (previousErr, previousResult) => { + + if (previousErr) { + callback(previousErr, null); + return; + } + + const previousRevenue = + Number(previousResult.rows[0]?.revenue || 0); + + const growth = + previousRevenue === 0 + ? (revenue > 0 ? 100 : 0) + : ((revenue - previousRevenue) / previousRevenue) * 100; + + const marketingGrowth = + `${growth.toFixed(2)}%`; + + query( + `INSERT INTO report + ( + date, + store_id, + overall_profit, + sales_trend, + marketing_growth, + owner_signature + ) + VALUES + ( + CURRENT_TIMESTAMP, + $1, + $2, + $3, + $4, + $5 + ) + RETURNING + date, + store_id, + overall_profit, + sales_trend, + marketing_growth, + owner_signature`, + [ + storeId, + Math.max(0, netProfit), + salesTrend.slice(0, 100), + marketingGrowth.slice(0, 100), + ownerSignature || 'Not signed yet' + ], + (insertErr, insertResult) => { + + if (insertErr) { + callback(insertErr, null); + return; + } + + const report = insertResult.rows[0]; + + /* + * monthly_profit has a composite primary key of + * (report_date, store_id), so it can contain one + * summary row per generated report without changing + * the project database structure. + */ + query( + `INSERT INTO monthly_profit + ( + report_date, + store_id, + month_and_year, + profit + ) + VALUES + ( + $1, + $2, + DATE_TRUNC('month', $3::timestamp)::DATE, + $4 + ) + ON CONFLICT (report_date, store_id) + DO UPDATE SET + month_and_year = EXCLUDED.month_and_year, + profit = EXCLUDED.profit`, + [ + report.date, + storeId, + endDate, + Math.max(0, netProfit) + ], + (monthlyErr) => { + + if (monthlyErr) { + console.error( + 'Warning inserting monthly profit:', + monthlyErr + ); + } + + query( + `INSERT INTO exchanges_data + ( + report_date, + store_id, + monthly_profit, + date, + sales, + damages + ) + VALUES + ( + $1, + $2, + $3, + CURRENT_TIMESTAMP, + $4, + $5 + ) + ON CONFLICT (report_date, store_id) + DO UPDATE SET + monthly_profit = EXCLUDED.monthly_profit, + date = EXCLUDED.date, + sales = EXCLUDED.sales, + damages = EXCLUDED.damages`, + [ + report.date, + storeId, + Math.max(0, netProfit), + revenue, + -refundTotal + ], + (exchangeErr) => { + + if (exchangeErr) { + console.error( + 'Warning inserting exchange data:', + exchangeErr + ); + } + + callback( + null, + report + ); + } + ); + } + ); + } + ); + } + ); + } + ); +} /* * ============================================================ @@ -2340,4 +3196,8 @@ getStoreStats, + installReportFunctions, + runReport, + generateStoreReport, + createOrderNew, getOrdersByClient,