// ============================================
// CRYSTAL POWER INVESTMENTS - AUTOMATED NOTIFICATIONS
// Email: [email protected]
// ============================================
// CONFIGURATION
const CONFIG = {
primaryEmail: "[email protected]",
backupEmail: "[email protected]",
companyName: "Crystal Power Investments",
timezone: "Africa/Cairo",
dailyReportHour: 9, // 9 AM
alerts: {
highPenaltyThreshold: 500, // EGP
urgentOverdueDays: 7,
upcomingDueDays: 3
}
};
// ============================================
// MAIN FUNCTIONS
// ============================================
/**
* Send daily email report at 9 AM Cairo time
*/
function sendDailyReport() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const checksSheet = ss.getSheetByName('الشيكات');
const paymentsSheet = ss.getSheetByName('المدفوعات');
if (!checksSheet || !paymentsSheet) {
Logger.log('Error: Required sheets not found');
return;
}
const today = new Date();
const data = analyzeData(checksSheet, paymentsSheet, today);
// Only send if there are alerts or upcoming items
if (data.overdueChecks.length > 0 || data.overduePayments.length > 0 ||
data.upcomingChecks.length > 0 || data.upcomingPayments.length > 0) {
const emailBody = buildDailyEmail(data, today);
MailApp.sendEmail({
to: CONFIG.primaryEmail,
cc: CONFIG.backupEmail,
subject: "🔔 تقرير الشيكات والمدفوعات اليومي - " + formatDate(today),
body: emailBody,
name: CONFIG.companyName
});
Logger.log('Daily report sent successfully');
} else {
Logger.log('No alerts to send today');
}
}
/**
* Check for overdue items when sheet is edited
*/
function onEditTrigger(e) {
const sheet = e.source.getActiveSheet();
const sheetName = sheet.getName();
// Only check الشيكات and المدفوعات sheets
if (sheetName !== 'الشيكات' && sheetName !== 'المدفوعات') return;
const range = e.range;
const row = range.getRow();
// Skip header row
if (row === 1) return;
checkForInstantAlert(sheet, row);
}
/**
* Setup time-based trigger for daily reports
*/
function setupDailyTrigger() {
// Delete existing triggers
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'sendDailyReport') {
ScriptApp.deleteTrigger(trigger);
}
});
// Create new trigger at 9 AM Cairo time
ScriptApp.newTrigger('sendDailyReport')
.timeBased()
.atHour(CONFIG.dailyReportHour)
.everyDays(1)
.inTimezone(CONFIG.timezone)
.create();
Logger.log('Daily trigger set up successfully for ' + CONFIG.dailyReportHour + ' AM ' + CONFIG.timezone);
}
/**
* Setup edit trigger
*/
function setupEditTrigger() {
// Delete existing edit triggers
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'onEditTrigger') {
ScriptApp.deleteTrigger(trigger);
}
});
// Create new edit trigger
ScriptApp.newTrigger('onEditTrigger')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
Logger.log('Edit trigger set up successfully');
}
// ============================================
// ANALYSIS FUNCTIONS
// ============================================
/**
* Analyze data from sheets
*/
function analyzeData(checksSheet, paymentsSheet, today) {
const checksData = checksSheet.getDataRange().getValues();
const paymentsData = paymentsSheet.getDataRange().getValues();
let overdueChecks = [];
let upcomingChecks = [];
let overduePayments = [];
let upcomingPayments = [];
let totalPenalties = 0;
// Analyze checks (skip header row)
for (let i = 1; i < checksData.length; i++) {
const row = checksData[i];
const checkNumber = row[0];
const dueDate = new Date(row[6]);
const amount = row[7];
const collectionDate = row[8];
const status = row[12];
if (!collectionDate && checkNumber) {
const daysDiff = Math.floor((today - dueDate) / (1000 * 60 * 60 * 24));
if (daysDiff > 0) {
const penalty = amount * (3.5/100/30) * daysDiff;
totalPenalties += penalty;
overdueChecks.push({
number: checkNumber,
days: daysDiff,
amount: amount,
penalty: penalty,
beneficiary: row[1],
drawer: row[2],
collector: row[3]
});
} else if (daysDiff >= -CONFIG.alerts.upcomingDueDays && daysDiff <= 0) {
upcomingChecks.push({
number: checkNumber,
dueDate: dueDate,
amount: amount,
daysUntilDue: Math.abs(daysDiff)
});
}
}
}
// Analyze payments (skip header row)
for (let i = 1; i < paymentsData.length; i++) {
const row = paymentsData[i];
const paymentId = row[0];
const dueDate = new Date(row[1]);
const amount = row[2];
const collectionDate = row[3];
const status = row[8];
if (!collectionDate && paymentId) {
const daysDiff = Math.floor((today - dueDate) / (1000 * 60 * 60 * 24));
if (daysDiff > 0) {
const penalty = amount * (3.5/100/30) * daysDiff;
totalPenalties += penalty;
overduePayments.push({
id: paymentId,
days: daysDiff,
amount: amount,
penalty: penalty
});
} else if (daysDiff >= -CONFIG.alerts.upcomingDueDays && daysDiff <= 0) {
upcomingPayments.push({
id: paymentId,
dueDate: dueDate,
amount: amount,
daysUntilDue: Math.abs(daysDiff)
});
}
}
}
return {
overdueChecks,
upcomingChecks,
overduePayments,
upcomingPayments,
totalPenalties
};
}
/**
* Build daily email content
*/
function buildDailyEmail(data, today) {
let email = "مرحباً مؤمن،\n\n";
email += "📊 تقرير يوم " + formatDate(today) + ":\n\n";
// Urgent alerts
if (data.overdueChecks.length > 0 || data.overduePayments.length > 0) {
email += "🔴 تنبيهات عاجلة:\n";
if (data.overdueChecks.length > 0) {
email += "• " + data.overdueChecks.length + " شيك متأخر\n";
}
if (data.overduePayments.length > 0) {
email += "• " + data.overduePayments.length + " دفعة متأخرة\n";
}
email += "• إجمالي الغرامات: " + formatCurrency(data.totalPenalties) + " جنيه\n\n";
}
// Overdue checks details
if (data.overdueChecks.length > 0) {
email += "📋 الشيكات المتأخرة:\n";
data.overdueChecks.forEach(check => {
email += "• شيك #" + check.number + "\n";
email += " المستفيد: " + check.beneficiary + "\n";
email += " المبلغ: " + formatCurrency(check.amount) + " جنيه\n";
email += " متأخر: " + check.days + " يوم\n";
email += " الغرامة: " + formatCurrency(check.penalty) + " جنيه\n\n";
});
}
// Overdue payments details
if (data.overduePayments.length > 0) {
email += "💰 المدفوعات المتأخرة:\n";
data.overduePayments.forEach(payment => {
email += "• سند #" + payment.id + " - " + formatCurrency(payment.amount) + " جنيه\n";
email += " متأخر: " + payment.days + " يوم - الغرامة: " + formatCurrency(payment.penalty) + " جنيه\n\n";
});
}
// Upcoming checks
if (data.upcomingChecks.length > 0) {
email += "⏰ شيكات مستحقة قريباً:\n";
data.upcomingChecks.forEach(check => {
email += "• شيك #" + check.number + " - " + formatCurrency(check.amount) + " جنيه\n";
email += " الاستحقاق: " + formatDate(check.dueDate) + " (خلال " + check.daysUntilDue + " يوم)\n\n";
});
}
// Upcoming payments
if (data.upcomingPayments.length > 0) {
email += "📅 دفعات مستحقة قريباً:\n";
data.upcomingPayments.forEach(payment => {
email += "• سند #" + payment.id + " - " + formatCurrency(payment.amount) + " جنيه\n";
email += " الاستحقاق: " + formatDate(payment.dueDate) + " (خلال " + payment.daysUntilDue + " يوم)\n\n";
});
}
// Footer
email += "\n━━━━━━━━━━━━━━━━━━━━━━\n";
email += "فتح الملف: " + SpreadsheetApp.getActiveSpreadsheet().getUrl() + "\n\n";
email += "تم الإرسال تلقائياً من نظام " + CONFIG.companyName;
return email;
}
/**
* Check for instant alerts when data is edited
*/
function checkForInstantAlert(sheet, row) {
const sheetName = sheet.getName();
const today = new Date();
if (sheetName === 'الشيكات') {
const checkNumber = sheet.getRange(row, 1).getValue();
const dueDate = new Date(sheet.getRange(row, 7).getValue());
const amount = sheet.getRange(row, 8).getValue();
const collectionDate = sheet.getRange(row, 9).getValue();
if (!collectionDate && today > dueDate) {
const daysDiff = Math.floor((today - dueDate) / (1000 * 60 * 60 * 24));
const penalty = amount * (3.5/100/30) * daysDiff;
// Send instant alert if penalty exceeds threshold
if (penalty >= CONFIG.alerts.highPenaltyThreshold) {
MailApp.sendEmail({
to: CONFIG.primaryEmail,
subject: "🚨 تنبيه: غرامة شيك عالية!",
body: "⚠️ شيك #" + checkNumber + "\n\n" +
"المبلغ: " + formatCurrency(amount) + " جنيه\n" +
"متأخر: " + daysDiff + " يوم\n" +
"الغرامة الحالية: " + formatCurrency(penalty) + " جنيه\n" +
"المبلغ الإجمالي: " + formatCurrency(amount + penalty) + " جنيه\n\n" +
"راجع الشيك: " + SpreadsheetApp.getActiveSpreadsheet().getUrl(),
name: CONFIG.companyName
});
}
}
}
}
// ============================================
// HELPER FUNCTIONS
// ============================================
/**
* Format number as Egyptian currency
*/
function formatCurrency(amount) {
if (!amount || isNaN(amount)) return "0.00";
return amount.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}
/**
* Format date in Arabic
*/
function formatDate(date) {
if (!date) return "";
const months = ['يناير', 'فبراير', 'مارس', 'أبريل', 'مايو', 'يونيو',
'يوليو', 'أغسطس', 'سبتمبر', 'أكتوبر', 'نوفمبر', 'ديسمبر'];
return date.getDate() + ' ' + months[date.getMonth()] + ' ' + date.getFullYear();
}
/**
* Test function - send test email
*/
function sendTestEmail() {
MailApp.sendEmail({
to: CONFIG.primaryEmail,
subject: "✅ اختبار نظام التنبيهات",
body: "تم إعداد نظام التنبيهات بنجاح!\n\n" +
"سيتم إرسال التقارير اليومية في تمام الساعة " + CONFIG.dailyReportHour + " صباحاً بتوقيت القاهرة.\n\n" +
"Crystal Power Investments",
name: CONFIG.companyName
});
Logger.log('Test email sent');
}