Untitled
unknown
plain_text
a year ago
20 kB
14
Indexable
/******************************
* Pet Best – מערכת יומנים מרובים
* יומן חריגים | בלתמים | ממתינים לסחורה | חוסרי ספק | ספקים
* גרסה מאוחדת – 2025-09
******************************/
/** שמות טבלאות **/
const SHEETS = {
MAIN: { name: 'יומן חריגים', archive: 'ארכיון חריגים' },
UNPLANNED: { name: 'בלתמים', archive: 'ארכיון בלתמים' },
WAITING: { name: 'ממתינים לסחורה', archive: 'ארכיון ממתינים' },
SUPPLIERS: { name: 'ספקים' }, // מאסטר (ללא ארכיון)
SUP_SHORT: { name: 'חוסרי ספק', archive: 'ארכיון חוסרי ספק' },
};
const HEADER_ROW = 1;
const FIRST_DATA_ROW = 2;
/** צבעים – מקרא ברור ואחיד **/
const COLORS = {
WAITING: '#FFE082', // ממתין – צהוב
IN_PROGRESS: '#FFB74D', // בטיפול – כתום
DONE: '#66BB6A', // טופל – ירוק
ON_HOLD: '#9575CD', // בהשהייה – סגול
UNPLANNED: '#29B6F6', // בלתם – תכלת
OVERDUE: '#D32F2F', // חריגה – אדום
};
/** ולידציות כלליות **/
const VALID_AGENTS = ['אבישי','דוד','יניב','מי','אנסטסיה','ונויז'];
const VALID_CHANNELS = ['טלפון','וואטסאפ','מייל'];
/** תצורת כל יומן **/
const JOURNALS = {
[SHEETS.MAIN.name]: {
archive: SHEETS.MAIN.archive,
headers: {
TS_OPEN : 'תאריך ושעה',
NAME : 'שם לקוח',
PHONE : 'מספר טלפון לקוח',
CHANNEL : 'סוג פנייה',
SUBJECT : 'נושא / מה נאמר',
NOTES : 'המשך טיפול / הערות',
AGENT : 'נציג מטפל',
STATUS : 'סטטוס',
TS_CLOSE: 'תאריך סגירה',
},
statusColors: {
'ממתין' : COLORS.WAITING,
'בטיפול' : COLORS.IN_PROGRESS,
'טופל' : COLORS.DONE,
'בהשהייה' : COLORS.ON_HOLD,
'בלתם' : COLORS.UNPLANNED,
},
validations: [
{ key: 'STATUS', type: 'list', values: ['ממתין','בטיפול','טופל','בהשהייה','בלתם'] },
{ key: 'CHANNEL', type: 'list', values: VALID_CHANNELS },
{ key: 'AGENT', type: 'list', values: VALID_AGENTS },
],
duplicate: {
copyKeys: ['NAME','PHONE','CHANNEL','SUBJECT','STATUS'], // B–E + H
clearKeys: ['NOTES','AGENT','TS_CLOSE'], // F,G,I
newStamp: 'TS_OPEN',
},
archiveOnStatus: 'טופל',
restoreStatus: 'בהשהייה',
overdueRule: { activeOn: ['ממתין','בטיפול'], thresholdHours: 1, color: COLORS.OVERDUE }, // שעה+
},
[SHEETS.UNPLANNED.name]: {
archive: SHEETS.UNPLANNED.archive,
headers: {
TS_OPEN : 'תאריך ושעה',
PARTY : 'לקוח/גורם',
CATEGORY: 'קטגוריה',
DETAILS : 'תיאור',
AGENT : 'נציג מטפל',
STATUS : 'סטטוס',
TS_CLOSE: 'תאריך סגירה',
},
statusColors: {
'ממתין' : COLORS.WAITING,
'בטיפול' : COLORS.IN_PROGRESS,
'טופל' : COLORS.DONE,
'בהשהייה' : COLORS.ON_HOLD,
},
validations: [
{ key: 'STATUS', type: 'list', values: ['ממתין','בטיפול','טופל','בהשהייה'] },
{ key: 'CATEGORY', type: 'list', values: ['גניבה','איחור מסניף','ספק לא סיפק','פג תוקף','אבד','אחר'] },
{ key: 'AGENT', type: 'list', values: VALID_AGENTS },
],
duplicate: {
copyKeys: ['PARTY','CATEGORY','DETAILS','STATUS'],
clearKeys: ['AGENT','TS_CLOSE'],
newStamp: 'TS_OPEN',
},
archiveOnStatus: 'טופל',
restoreStatus: 'בהשהייה',
overdueRule: null,
},
[SHEETS.WAITING.name]: {
archive: SHEETS.WAITING.archive,
headers: {
TS_OPEN : 'תאריך פתיחה',
PARTY : 'שם לקוח/סניף',
ITEM : 'מוצר/פריט',
SUPPLIER: 'ספק/מקור',
ETA : 'תאריך יעד',
AGENT : 'נציג מטפל',
STATUS : 'סטטוס',
TS_CLOSE: 'תאריך סגירה',
},
statusColors: {
'ממתין' : COLORS.WAITING,
'הגיע' : COLORS.DONE,
'לא סופק' : COLORS.ON_HOLD,
'בהשהייה' : COLORS.ON_HOLD,
'טופל' : COLORS.DONE,
},
validations: [
{ key: 'STATUS', type: 'list', values: ['ממתין','הגיע','לא סופק','בהשהייה','טופל'] },
{ key: 'AGENT', type: 'list', values: VALID_AGENTS },
],
duplicate: {
copyKeys: ['PARTY','ITEM','SUPPLIER','ETA','STATUS'],
clearKeys: ['AGENT','TS_CLOSE'],
newStamp: 'TS_OPEN',
},
archiveOnStatus: 'טופל',
restoreStatus: 'בהשהייה',
overdueRule: null,
},
[SHEETS.SUP_SHORT.name]: {
archive: SHEETS.SUP_SHORT.archive,
headers: {
TS_OPEN : 'תאריך פתיחה',
SUPPLIER: 'ספק',
ITEM : 'מוצר/פריט',
QTY : 'כמות',
PO : 'מספר הזמנה/אסמכתא',
ETA : 'תאריך יעד',
CONTACT : 'איש קשר',
STATUS : 'סטטוס',
TS_CLOSE: 'תאריך סגירה',
},
statusColors: {
'ממתין' : COLORS.WAITING,
'הוזמן' : COLORS.IN_PROGRESS,
'בדרך' : COLORS.IN_PROGRESS,
'הגיע' : COLORS.DONE,
'לא סופק' : COLORS.ON_HOLD,
'בהשהייה' : COLORS.ON_HOLD,
'טופל' : COLORS.DONE,
},
validations: [
{ key: 'STATUS', type: 'list', values: ['ממתין','הוזמן','בדרך','הגיע','לא סופק','בהשהייה','טופל'] },
{ key: 'SUPPLIER', type: 'range', rangeName: 'SUPPLIERS_LIST' }, // מרשימת ספקים בטאב "ספקים"
],
duplicate: {
copyKeys: ['SUPPLIER','ITEM','QTY','PO','ETA','CONTACT','STATUS'],
clearKeys: ['TS_CLOSE'],
newStamp: 'TS_OPEN',
},
archiveOnStatus: 'טופל',
restoreStatus: 'בהשהייה',
// חריגה: אם TODAY()>ETA והסטטוס לא "הגיע/טופל" → אדום
overdueRule: { custom: 'ETA_PAST', color: COLORS.OVERDUE },
},
};
/** ===== תפריט ===== **/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('יומן ניהול')
.addItem('שכפל שורה', 'duplicateSelectedRow')
.addSeparator()
.addItem('העבר לארכיון', 'moveActiveRowToArchive')
.addItem('החזר מהארכיון', 'moveActiveRowToSource')
.addSeparator()
.addItem('רענן עיצובים', 'setupConditionalFormatting')
.addItem('רענן עיצובים (כפוי)', 'forceSetupConditionalFormatting')
.addItem('רענן ולידציות', 'setupAllValidations')
.addSeparator()
.addItem('תקן כותרות עכשיו', 'fixHeaderNow')
.addToUi();
// הקמות לכל היומנים
Object.keys(JOURNALS).forEach(name => {
ensureHeaders_(name);
protectHeaderRow_(name);
});
ensureSuppliersMaster_();
refreshSuppliersNamedRange_();
setupConditionalFormatting();
setupAllValidations();
}
/** ===== onEdit: חותמות/ארכיון/החזרה ===== **/
function onEdit(e) {
const shName = e.range.getSheet().getName();
const cfg = JOURNALS[shName];
if (!cfg) return;
const row = e.range.getRow();
if (row < FIRST_DATA_ROW) return;
const map = getHeaderMap_(shName);
const sh = e.range.getSheet();
// חותמת פתיחה: נגיעה ראשונה בשדות העבודה
const touchCols = [map.STATUS,map.AGENT,map.NAME,map.PHONE,map.CHANNEL,map.SUBJECT,map.NOTES,map.PARTY,map.CATEGORY,map.DETAILS,map.ITEM,map.SUPPLIER,map.ETA].filter(Boolean);
if (touchCols.includes(e.range.getColumn())) {
if (map.TS_OPEN && !sh.getRange(row, map.TS_OPEN).getValue()) {
sh.getRange(row, map.TS_OPEN).setValue(new Date());
}
}
// ניקוי חותמות כשכל שדות העבודה ריקים
if (isRowBusinessEmpty_(shName, row)) {
if (map.TS_OPEN) sh.getRange(row, map.TS_OPEN).clearContent();
if (map.TS_CLOSE) sh.getRange(row, map.TS_CLOSE).clearContent();
return;
}
// חותמת סגירה כשסטטוס=טופל
const status = map.STATUS ? String(sh.getRange(row, map.STATUS).getDisplayValue()).trim() : '';
if (status === 'טופל' && map.TS_CLOSE) {
const closeCell = sh.getRange(row, map.TS_CLOSE);
if (!closeCell.getValue()) closeCell.setValue(new Date());
}
// העברה אוטומטית לארכיון
if (e.range.getColumn() === map.STATUS && status === cfg.archiveOnStatus) {
moveRowToArchive_(shName, row);
}
// החזרה מהארכיון (עריכת סטטוס בארכיון)
if (shName === cfg.archive && e.range.getColumn() === map.STATUS && status !== cfg.archiveOnStatus) {
moveRowToSource_(shName, row, /*setOnHold=*/true);
}
}
/** ===== שכפול שורה לפי היומן הפעיל ===== **/
function duplicateSelectedRow() {
const sh = SpreadsheetApp.getActiveSheet();
const cfg = JOURNALS[sh.getName()];
if (!cfg) return SpreadsheetApp.getUi().alert('שכפול זמין רק ביומנים מנוהלים.');
const map = getHeaderMap_(sh.getName());
const row = sh.getActiveRange().getRow();
if (row < FIRST_DATA_ROW) return;
sh.insertRowsAfter(row, 1);
const newRow = row + 1;
if (map[cfg.duplicate.newStamp]) sh.getRange(newRow, map[cfg.duplicate.newStamp]).setValue(new Date());
(cfg.duplicate.copyKeys || []).forEach(k => map[k] && sh.getRange(newRow, map[k]).setValue(sh.getRange(row, map[k]).getValue()));
(cfg.duplicate.clearKeys || []).forEach(k => map[k] && sh.getRange(newRow, map[k]).clearContent());
}
/** ===== תפריט: העברה/החזרה ידנית ===== **/
function moveActiveRowToArchive() {
const sh = SpreadsheetApp.getActiveSheet();
const row = sh.getActiveRange().getRow();
if (row < FIRST_DATA_ROW) return;
const name = sh.getName();
if (!JOURNALS[name]) return;
moveRowToArchive_(name, row);
}
function moveActiveRowToSource() {
const sh = SpreadsheetApp.getActiveSheet();
const row = sh.getActiveRange().getRow();
if (row < FIRST_DATA_ROW) return;
const archiveName = sh.getName();
const journalName = Object.keys(JOURNALS).find(n => JOURNALS[n].archive === archiveName);
if (!journalName) return SpreadsheetApp.getUi().alert('החזרה זמין מטאבי ארכיון בלבד.');
moveRowToSource_(archiveName, row, true);
}
/** ===== ארכיון: יצירה/העברה/החזרה ===== **/
function ensureArchiveSheet_(journalName) {
const cfg = JOURNALS[journalName];
const ss = SpreadsheetApp.getActive();
let archive = ss.getSheetByName(cfg.archive);
if (!archive) {
archive = ss.insertSheet(cfg.archive);
const headers = Object.values(cfg.headers);
archive.getRange(HEADER_ROW, 1, 1, headers.length).setValues([headers]);
}
return archive;
}
function moveRowToArchive_(journalName, rowInSource) {
const ss = SpreadsheetApp.getActive();
const source = ss.getSheetByName(journalName);
const archive = ensureArchiveSheet_(journalName);
const lastCol = source.getLastColumn();
const values = source.getRange(rowInSource, 1, 1, lastCol).getValues()[0];
archive.appendRow(values);
source.deleteRow(rowInSource);
}
function moveRowToSource_(archiveName, rowInArchive, setOnHold) {
const ss = SpreadsheetApp.getActive();
const archive = ss.getSheetByName(archiveName);
const journalName = Object.keys(JOURNALS).find(n => JOURNALS[n].archive === archiveName);
if (!journalName) return;
const source = ss.getSheetByName(journalName);
const cfg = JOURNALS[journalName];
const lastCol = archive.getLastColumn();
const values = archive.getRange(rowInArchive, 1, 1, lastCol).getValues()[0];
source.appendRow(values);
const newRow = source.getLastRow();
const map = getHeaderMap_(journalName);
if (setOnHold && map.STATUS) source.getRange(newRow, map.STATUS).setValue(cfg.restoreStatus || 'בהשהייה');
archive.deleteRow(rowInArchive);
}
/** ===== עיצוב מותנה – רגיל/כפוי ===== **/
function setupConditionalFormatting() { buildConditionalFormatting_(false); }
function forceSetupConditionalFormatting() { buildConditionalFormatting_(true); }
function buildConditionalFormatting_(forceClear) {
const ss = SpreadsheetApp.getActive();
Object.keys(JOURNALS).forEach(name => {
const cfg = JOURNALS[name];
const sh = ss.getSheetByName(name);
if (!sh) return;
const map = getHeaderMap_(name);
const rangeA1 = 'A2:' + colToA1_(sh.getLastColumn());
const target = sh.getRange(rangeA1);
const statusColLetter = map.STATUS ? colToA1_(map.STATUS) : null;
let rules = sh.getConditionalFormatRules();
rules = forceClear
? rules.filter(r => r.getRanges().every(rg => !rangesOverlap_(rg, target)))
: rules.filter(r => r.getRanges().every(rg => !rangesEqual_(rg, target)));
const newRules = [];
// חריגה ביומן חריגים (שעה+)
if (cfg.overdueRule && cfg.overdueRule.thresholdHours && map.TS_OPEN && statusColLetter) {
const hrs = cfg.overdueRule.thresholdHours;
const tsCol = colToA1_(map.TS_OPEN);
const overdueFormula =
`=AND(OR($${statusColLetter}2="${cfg.overdueRule.activeOn[0]}",$${statusColLetter}2="${cfg.overdueRule.activeOn[1]}"),`+
`ISDATE($${tsCol}2),(NOW()-$${tsCol}2)>TIME(${hrs},0,0))`;
newRules.push(
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(overdueFormula)
.setBackground(cfg.overdueRule.color || COLORS.OVERDUE)
.setBold(true)
.setRanges([target]).build()
);
}
// חריגת ETA ביומן "חוסרי ספק"
if (cfg.overdueRule && cfg.overdueRule.custom === 'ETA_PAST' && map.ETA && statusColLetter) {
const etaCol = colToA1_(map.ETA);
const overdueETA =
`=AND($${statusColLetter}2<>"הגיע",$${statusColLetter}2<>"טופל",ISDATE($${etaCol}2),TODAY()>$${etaCol}2)`;
newRules.push(
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(overdueETA)
.setBackground(cfg.overdueRule.color || COLORS.OVERDUE)
.setBold(true)
.setRanges([target]).build()
);
}
// צבעי סטטוס רגילים
if (statusColLetter) {
Object.entries(cfg.statusColors).forEach(([st, color]) => {
newRules.push(
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(`=$${statusColLetter}2="${st}"`)
.setBackground(color)
.setRanges([target]).build()
);
});
}
sh.setConditionalFormatRules([].concat(rules, newRules));
});
}
/** ===== ולידציות ===== **/
function setupAllValidations() {
Object.keys(JOURNALS).forEach(name => {
const sh = SpreadsheetApp.getActive().getSheetByName(name);
if (!sh) return;
const cfg = JOURNALS[name];
const map = getHeaderMap_(name);
(cfg.validations || []).forEach(v => {
if (!map[v.key]) return;
const colA1 = colToA1_(map[v.key]);
const range = sh.getRange(`${colA1}${FIRST_DATA_ROW}:${colA1}`);
if (v.type === 'list') {
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(v.values, true)
.setAllowInvalid(true)
.build();
range.setDataValidation(rule);
} else if (v.type === 'range') {
const ss = SpreadsheetApp.getActive();
const named = ss.getRangeByName(v.rangeName);
if (named) {
const rule = SpreadsheetApp.newDataValidation()
.requireValueInRange(named, true)
.setAllowInvalid(true)
.build();
range.setDataValidation(rule);
}
}
});
});
}
/** ===== ספקים – מאסטר + Named Range ===== **/
function ensureSuppliersMaster_() {
const ss = SpreadsheetApp.getActive();
let sh = ss.getSheetByName(SHEETS.SUPPLIERS.name);
if (!sh) sh = ss.insertSheet(SHEETS.SUPPLIERS.name);
const headers = ['ספק','איש קשר','טלפון','מייל','יום הגעה עיקרי','ימי הגעה נוספים','חלון אספקה','הערות'];
sh.getRange(1,1,1,headers.length).setValues([headers]);
// ולידציית יום הגעה עיקרי
const days = ['ראשון','שני','שלישי','רביעי','חמישי','שישי'];
const dayRule = SpreadsheetApp.newDataValidation()
.requireValueInList(days, true).setAllowInvalid(true).build();
sh.getRange('E2:E').setDataValidation(dayRule);
// יצירת Named Range SUPPLIERS_LIST (A2:A)
refreshSuppliersNamedRange_();
}
function refreshSuppliersNamedRange_() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName(SHEETS.SUPPLIERS.name);
if (!sh) return;
const lastRow = Math.max(sh.getLastRow(), 2);
const rng = sh.getRange(2,1,Math.max(lastRow-1,1),1);
ss.getNamedRanges().filter(n => n.getName() === 'SUPPLIERS_LIST').forEach(n => n.remove());
ss.setNamedRange('SUPPLIERS_LIST', rng);
}
/** ===== כותרות – שחזור והגנה ===== **/
function ensureHeaders_(journalName) {
const sh = SpreadsheetApp.getActive().getSheetByName(journalName);
if (!sh) return;
const headers = Object.values(JOURNALS[journalName].headers);
sh.getRange(HEADER_ROW, 1, 1, headers.length).setValues([headers]);
}
function protectHeaderRow_(journalName) {
const sh = SpreadsheetApp.getActive().getSheetByName(journalName);
if (!sh) return;
sh.getProtections(SpreadsheetApp.ProtectionType.RANGE)
.filter(p => p.getRange().getRow() === HEADER_ROW && p.getRange().getNumRows() === 1)
.forEach(p => p.remove());
const lastCol = Math.max(sh.getLastColumn(), Object.values(JOURNALS[journalName].headers).length);
const prot = sh.getRange(HEADER_ROW, 1, 1, lastCol).protect();
prot.setDescription('הגנה על כותרות');
prot.removeEditors(prot.getEditors());
if (prot.canDomainEdit()) prot.setDomainEdit(false);
}
function fixHeaderNow() {
const ui = SpreadsheetApp.getUi();
try {
Object.keys(JOURNALS).forEach(name => {
const sh = SpreadsheetApp.getActive().getSheetByName(name);
if (!sh) return;
sh.getProtections(SpreadsheetApp.ProtectionType.RANGE)
.filter(p => p.getRange().getRow() === HEADER_ROW && p.getRange().getNumRows() === 1)
.forEach(p => p.remove());
ensureHeaders_(name);
protectHeaderRow_(name);
});
ui.alert('כותרות שוחזרו והוגנו בכל היומנים.');
} catch (err) {
console.error(err);
ui.alert('שגיאה בתיקון כותרות: ' + err);
}
}
/** ===== עזרי מיפוי/טווחים ===== **/
function getHeaderMap_(journalName) {
const sh = SpreadsheetApp.getActive().getSheetByName(journalName);
const lastCol = sh.getLastColumn();
const row = sh.getRange(HEADER_ROW, 1, 1, lastCol).getDisplayValues()[0];
const headers = JOURNALS[journalName].headers;
const map = {};
Object.entries(headers).forEach(([key, label]) => {
const pos = row.findIndex(v => String(v || '').trim() === label);
if (pos !== -1) map[key] = pos + 1;
});
return map;
}
function colToA1_(n) {
let s=''; while(n>0){const m=(n-1)%26; s=String.fromCharCode(65+m)+s; n=Math.floor((n-1)/26);} return s;
}
function rangesEqual_(a, b) {
return a.getRow()===b.getRow() && a.getColumn()===b.getColumn() &&
a.getNumRows()===b.getNumRows() && a.getNumColumns()===b.getNumColumns();
}
function rangesOverlap_(a, b) {
const r1=a.getRow(), c1=a.getColumn(), r2=b.getRow(), c2=b.getColumn();
const r1e=r1+a.getNumRows()-1, c1e=c1+a.getNumColumns()-1;
const r2e=r2+b.getNumRows()-1, c2e=c2+b.getNumColumns()-1;
const rowsOverlap = !(r1e < r2 || r2e < r1);
const colsOverlap = !(c1e < c2 || c2e < c1);
return rowsOverlap && colsOverlap;
}
function isRowBusinessEmpty_(journalName, row) {
const sh = SpreadsheetApp.getActive().getSheetByName(journalName);
const cfg = JOURNALS[journalName];
const map = getHeaderMap_(journalName);
const businessKeys = Object.keys(map).filter(k => !['TS_OPEN','TS_CLOSE'].includes(k));
const vals = businessKeys.map(k => sh.getRange(row, map[k]).getDisplayValue());
return vals.every(v => String(v).trim() === '');
}Editor is loading...
Leave a Comment