new>
Config.gs — database-ID, rollen en testaccounts
Code.gs — login, sessies, rolrechten, boekingen en databasefuncties
Index.html — login en vier rolgebonden schermen
/** PadelSchoolOS V1.1. Copy Config.gs, Code.gs, Index.html into one Apps Script project.
* Deploy as: execute as owner; access restricted to intended users. First run setupPadelOS as owner.
* Never share editor access to the backing spreadsheet with ordinary users.
*/
const PS_HEADERS = {
SYSTEM_CONFIG:['ConfigID','SystemName','Version','Build','Brand','AdministratorEmail','AdministratorPhone','GabelerURL','GODOGOODURL','DatabaseSpreadsheetID','CreatedAt','UpdatedAt'],
USERS:['UserID','Email','Name','Role','Active','Phone','Language','LastLogin','CreatedAt','UpdatedAt'],
PERSONS:['PersonID','UserID','FirstName','LastName','DisplayName','Email','Phone','DateOfBirth','Gender','Address','PostalCode','City','Country','EmergencyContact','EmergencyPhone','Notes','Active','CreatedAt','UpdatedAt'],
LOCATIONS:['LocationID','Name','Description','Address','PostalCode','City','Province','Country','Latitude','Longitude','ContactName','ContactEmail','ContactPhone','Website','BookingURL','OpeningHours','Timezone','Currency','Active','CreatedAt','UpdatedAt'],
LOCATION_USERS:['ID','LocationID','UserID','Role','Active','StartDate','EndDate','CreatedAt','UpdatedAt'],
COURT_TYPES:['CourtTypeID','Name','Description','IndoorOutdoor','GlassType','SurfaceType','Lights','Accessible','Active','CreatedAt','UpdatedAt'],
COURTS:['CourtID','LocationID','CourtTypeID','Name','CourtNumber','CourtType','IndoorOutdoor','Lights','SurfaceType','BookingURL','Active','CreatedAt','UpdatedAt'],
COURT_AVAILABILITY:['AvailabilityID','LocationID','CourtID','Date','StartTime','EndTime','Status','Visibility','PriorityAccess','Source','SourceID','ExternalBookingID','ImportedAt','UpdatedAt'],
COURT_PRICES:['PriceID','LocationID','CourtID','DayOfWeek','StartTime','EndTime','Price','Currency','PriceType','MinimumDuration','Active','CreatedAt','UpdatedAt'],
BOOKING_RULES:['RuleID','LocationID','Name','Description','Role','Priority','DaysInAdvance','HoursInAdvance','MaxDuration','MinDuration','AllowLesson','AllowSeries','AllowHold','Active','CreatedAt','UpdatedAt'],
BOOKING_PRIORITIES:['PriorityID','LocationID','Name','Role','PriorityValue','EarlyVisibilityHours','EarlyBookingHours','Description','Active','CreatedAt','UpdatedAt'],
IMPORT_SOURCES:['ImportID','LocationID','Name','Type','URL','FileID','SpreadsheetID','SheetName','Format','Frequency','Active','LastImport','LastStatus','LastError','RecordsImported','CreatedAt','UpdatedAt'],
IMPORT_RUNS:['ImportRunID','ImportID','LocationID','StartedAt','FinishedAt','Status','RecordsRead','RecordsImported','RecordsSkipped','ErrorMessage','CreatedAt'],
BOOKINGS:['BookingID','LocationID','CourtID','UserID','PersonID','Date','StartTime','EndTime','Type','Priority','Visibility','Status','Source','ExternalBookingID','Notes','CreatedAt','UpdatedAt'],
BOOKING_HOLDS:['HoldID','BookingID','LocationID','CourtID','UserID','Date','StartTime','EndTime','ExpiresAt','Priority','Status','CreatedAt','UpdatedAt'],
LESSONS:['LessonID','SeriesID','TrainerUserID','LocationID','CourtID','Date','StartTime','EndTime','LessonType','Level','PlayerCount','Goal','Status','Notes','CreatedAt','UpdatedAt'],
LESSON_SERIES:['SeriesID','TrainerUserID','LocationID','Name','Description','StartDate','EndDate','DayOfWeek','StartTime','EndTime','NumberOfLessons','Players','Level','Goal','Priority','Status','Notes','CreatedAt','UpdatedAt'],
LESSON_COURTS:['ID','LessonID','SeriesID','CourtID','BookingID','Date','StartTime','EndTime','Status','CreatedAt','UpdatedAt'],
TRAINER_AVAILABILITY:['AvailabilityID','TrainerUserID','LocationID','Date','StartTime','EndTime','Status','LessonType','MaxPlayers','Notes','CreatedAt','UpdatedAt'],
NOTIFICATIONS:['NotificationID','UserID','Type','Title','Message','Entity','EntityID','Read','CreatedAt','ReadAt'],
MESSAGES:['MessageID','SenderUserID','ReceiverUserID','LocationID','Subject','Message','RelatedEntity','RelatedEntityID','Read','CreatedAt','ReadAt'],
AUDIT_LOG:['LogID','UserEmail','UserID','Action','Entity','EntityID','Details','CreatedAt']
};
function doGet() { return HtmlService.createHtmlOutputFromFile('Index').setTitle('PadelSchoolOS V1.1'); }
function db_() { return SpreadsheetApp.openById(PS.DATABASE_ID); }
function now_() { return new Date().toISOString(); }
function uid_(prefix) { return prefix + '-' + Utilities.getUuid(); }
function email_(value) { return String(value||'').trim().toLowerCase(); }
function flag_(value) { return value === true || String(value).toLowerCase() === 'true'; }
function check_(condition,message) { if (!condition) throw new Error(message); }
function lock_(fn) { const lock=LockService.getScriptLock(); lock.waitLock(20000); try{return fn();}finally{lock.releaseLock();} }
function sheet_(name) { const sh=db_().getSheetByName(name); check_(sh,'Ontbrekende tab: '+name+'. Voer setupPadelOS uit.'); return sh; }
function headers_(name) { const sh=sheet_(name); return sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0].map(String); }
function rows_(name) {
const sh=sheet_(name), last=sh.getLastRow(); if(last<2)return [];
const h=headers_(name), data=sh.getRange(2,1,last-1,h.length).getValues();
return data.map((a,i)=>{const o={_row:i+2};h.forEach((key,j)=>o[key]=a[j] instanceof Date?Utilities.formatDate(a[j],'UTC',"yyyy-MM-dd'T'HH:mm:ss'Z'"):a[j]);return o;});
}
function put_(name,obj) { const sh=sheet_(name), h=headers_(name); sh.appendRow(h.map(k=>obj[k]===undefined?'':obj[k])); return obj; }
function update_(name,row,obj) { const sh=sheet_(name),h=headers_(name);check_(row>=2,'Ongeldige rij'); sh.getRange(row,1,1,h.length).setValues([h.map(k=>obj[k]===undefined?'':obj[k])]);return obj; }
function find_(name,key,value) { return rows_(name).find(r=>String(r[key])===String(value)); }
function safe_(value,n) {return String(value||'').trim().slice(0,n||500);}
function audit_(actor,action,entity,id) {put_('AUDIT_LOG',{LogID:uid_('LOG'),UserID:actor&&actor.UserID||'',UserEmail:actor&&actor.Email||'',Action:action,Entity:entity,EntityID:id,CreatedAt:now_()});}
function publicUser_(u){return {userId:u.UserID,email:u.Email,name:u.Name,role:u.Role};}
/** Idempotent installer: append only missing tabs/rows; never renames existing tabs. Run as owner. */
function setupPadelOS(){
const caller=email_(Session.getActiveUser().getEmail());
check_(caller===PS.ADMIN_EMAIL,'Alleen de administrator mag initialiseren. Voer de functie handmatig uit in de editor met het beheerdersaccount.');
return lock_(()=>{
const ss=db_();Object.assign(PS_HEADERS,PS.EXTRA_HEADERS);
Object.keys(PS_HEADERS).forEach(name=>{
let sh=ss.getSheetByName(name);if(!sh)sh=ss.insertSheet(name);
if(sh.getLastRow()===0)sh.getRange(1,1,1,PS_HEADERS[name].length).setValues([PS_HEADERS[name]]);
else {const existing=headers_(name);check_(PS_HEADERS[name].every(h=>existing.indexOf(h)>=0),'Kolommen ontbreken in '+name+'. Controleer eerst de tab.');}
sh.setFrozenRows(1);
});
PS.TEST_USERS.forEach(([email,name,role])=>{
if(!rows_('USERS').some(u=>email_(u.Email)===email))put_('USERS',{UserID:uid_('USR'),Email:email,Name:name,Role:role,Active:true,CreatedAt:now_(),UpdatedAt:now_()});
});
const owner=rows_('USERS').find(u=>email_(u.Email)==='gabeler.eu@gmail.com');
let location=rows_('LOCATIONS').find(l=>l.Name==='Gabeler Padel Testlocatie');
if(!location)location=put_('LOCATIONS',{LocationID:uid_('LOC'),Name:'Gabeler Padel Testlocatie',Description:'Testlocatie zonder echt adres',Timezone:'Europe/Amsterdam',Currency:'EUR',Active:true,CreatedAt:now_(),UpdatedAt:now_()});
if(!rows_('LOCATION_USERS').some(x=>x.LocationID===location.LocationID&&x.UserID===owner.UserID))put_('LOCATION_USERS',{ID:uid_('MEM'),LocationID:location.LocationID,UserID:owner.UserID,Role:PS.ROLES.LOCATION,Active:true,CreatedAt:now_(),UpdatedAt:now_()});
for(let i=1;i<=4;i++)if(!rows_('COURTS').some(c=>c.LocationID===location.LocationID&&c.Name==='Baan '+i))put_('COURTS',{CourtID:uid_('CRT'),LocationID:location.LocationID,Name:'Baan '+i,CourtNumber:i,Active:true,CreatedAt:now_(),UpdatedAt:now_()});
const conf=rows_('SYSTEM_CONFIG')[0];if(!conf)put_('SYSTEM_CONFIG',{ConfigID:uid_('CFG'),SystemName:'PadelSchoolOS',Version:PS.VERSION,Build:PS.BUILD,Brand:'PadelSchool',AdministratorEmail:PS.ADMIN_EMAIL,DatabaseSpreadsheetID:PS.DATABASE_ID,CreatedAt:now_(),UpdatedAt:now_()});
formatSpreadsheet_(50);return {ok:true,spreadsheetId:ss.getId(),tabs:ss.getSheets().length};
});
}
function formatSpreadsheet_(maxChars){const ss=db_(),max=Math.max(10,Math.min(50,Number(maxChars)||50)); ss.getSheets().forEach(sh=>{
const cols=sh.getLastColumn();if(!cols)return;sh.setFrozenRows(1);const count=Math.min(sh.getLastRow(),500);
const sample=count?sh.getRange(1,1,count,cols).getDisplayValues():[];
const range=sh.getRange(1,1,Math.max(1,sh.getLastRow()),cols);range.setWrap(true).setVerticalAlignment('top');
for(let j=0;j<cols;j++){let length=10;sample.forEach(row=>String(row[j]||'').split(/\r?\n/).forEach(line=>length=Math.max(length,Math.min(max,line.length))));sh.setColumnWidth(j+1,Math.min(420,Math.max(90,Math.ceil(length*8+20))));}
if(sh.getLastRow()>0)sh.autoResizeRows(1,sh.getLastRow());
});return {ok:true,tabs:ss.getSheets().length,maxChars:max};}
function adminFormatSpreadsheet(token,maxChars){requireRole_(token,[PS.ROLES.ADMIN]);return formatSpreadsheet_(maxChars);}
// Login: OTP is required on every new session, including returning accounts.
function hash_(text){return Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256,String(text)).map(x=>('0'+(x&255).toString(16)).slice(-2)).join('');}
function passwordHash_(salt,password){let v=salt+'|'+password;for(let i=0;i<12000;i++)v=hash_(v);return v;}
function codeHash_(userId,code){return hash_(PropertiesService.getScriptProperties().getProperty('PS_AUTH_PEPPER')+'|'+userId+'|'+code);}
function otpCode_(){return String(100000+(parseInt(hash_(Utilities.getUuid()).slice(0,10),16)%900000));}
function requestLoginCode(address){
const email=email_(address);check_(/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email),'Ongeldig e-mailadres.');
return lock_(()=>{
const u=rows_('USERS').find(x=>email_(x.Email)===email&&flag_(x.Active));
// Generic answer prevents account enumeration.
if(!u)return {ok:true,message:'Als het account actief is, wordt een code verstuurd.'};
const recent=rows_('LOGIN_CODES').filter(x=>x.UserID===u.UserID&&Date.now()-Date.parse(x.CreatedAt)<3600000);
check_(recent.length<5,'Te veel codes aangevraagd. Probeer over een uur opnieuw.');
const code=otpCode_();let pepper=PropertiesService.getScriptProperties().getProperty('PS_AUTH_PEPPER');
if(!pepper){pepper=uid_('SECRET');PropertiesService.getScriptProperties().setProperty('PS_AUTH_PEPPER',pepper);}
const record=put_('LOGIN_CODES',{CodeID:uid_('OTP'),UserID:u.UserID,CodeHash:codeHash_(u.UserID,code),ExpiresAt:new Date(Date.now()+10*60000).toISOString(),Attempts:0,CreatedAt:now_()});
try{MailApp.sendEmail(email,'PadelSchoolOS inlogcode','Je inlogcode is '+code+'. Geldig gedurende 10 minuten. Deel deze code met niemand.');}
catch(e){record.UsedAt=now_();update_('LOGIN_CODES',rows_('LOGIN_CODES').find(x=>x.CodeID===record.CodeID)._row,record);throw new Error('De e-mail kon niet worden verzonden. Probeer later opnieuw.');}
return {ok:true,message:'Als het account actief is, wordt een code verstuurd.'};
});
}
function verifyLoginCode(address,code){return lock_(()=>{
const u=rows_('USERS').find(x=>email_(x.Email)===email_(address)&&flag_(x.Active));check_(u,'Code ongeldig of verlopen.');
const recent=rows_('LOGIN_CODES').filter(x=>x.UserID===u.UserID&&!x.UsedAt&&Date.parse(x.ExpiresAt)>Date.now()).pop();
check_(recent&&Number(recent.Attempts)<5,'Code ongeldig of verlopen.');
recent.Attempts=Number(recent.Attempts||0)+1;update_('LOGIN_CODES',recent._row,recent);
check_(/^\d{6}$/.test(String(code))&&recent.CodeHash===codeHash_(u.UserID,String(code)),'Code ongeldig of verlopen.');
recent.UsedAt=now_();update_('LOGIN_CODES',recent._row,recent);
const auth=find_('AUTH_USERS','UserID',u.UserID);const ticket=uid_('TICKET');
CacheService.getScriptCache().put('ticket:'+hash_(ticket),u.UserID,300);
return {ticket:ticket,firstTime:!auth||!auth.PasswordHash};
});}
function finishLogin(address,ticket,password,newPassword){return lock_(()=>{
const u=rows_('USERS').find(x=>email_(x.Email)===email_(address)&&flag_(x.Active));
const cache=CacheService.getScriptCache(),key='ticket:'+hash_(ticket);check_(u&&ticket&&cache.get(key)===u.UserID,'Verificatie verlopen. Vraag opnieuw een code aan.');
let auth=find_('AUTH_USERS','UserID',u.UserID);
if(!auth||!auth.PasswordHash){
check_(typeof newPassword==='string'&&newPassword.length>=12&&newPassword.length<=128,'Kies een wachtwoord van 12 tot 128 tekens.');
check_(newPassword!=='1234','1234 is geen veilig wachtwoord.');
const salt=uid_('SALT');const obj={UserID:u.UserID,Email:u.Email,PasswordSalt:salt,PasswordHash:passwordHash_(salt,newPassword),EmailVerified:true,CreatedAt:auth&&auth.CreatedAt||now_(),UpdatedAt:now_()};
if(auth)update_('AUTH_USERS',auth._row,obj);else put_('AUTH_USERS',obj);
}else check_(typeof password==='string'&&auth.PasswordHash===passwordHash_(auth.PasswordSalt,password),'Onjuist wachtwoord.');
cache.remove(key);const token=uid_('SESSION')+uid_('RANDOM');put_('LOGIN_SESSIONS',{SessionID:uid_('SES'),UserID:u.UserID,TokenHash:hash_(token),ExpiresAt:new Date(Date.now()+7*86400000).toISOString(),CreatedAt:now_()});
u.LastLogin=now_();u.UpdatedAt=now_();update_('USERS',u._row,u);audit_(u,'LOGIN','USERS',u.UserID);
return {token:token,user:publicUser_(u)};
});}
function requireUser_(token){check_(typeof token==='string'&&token.length>30,'Aanmelding vereist.');
const s=find_('LOGIN_SESSIONS','TokenHash',hash_(token));check_(s&&!s.RevokedAt&&Date.parse(s.ExpiresAt)>Date.now(),'Sessie verlopen.');
const u=find_('USERS','UserID',s.UserID);check_(u&&flag_(u.Active),'Account niet actief.');return u;
}
function requireRole_(token,roles){const u=requireUser_(token);check_(roles.includes(u.Role),'Geen toegang.');return u;}
function logout(token){return lock_(()=>{const s=find_('LOGIN_SESSIONS','TokenHash',hash_(token));if(s){s.RevokedAt=now_();update_('LOGIN_SESSIONS',s._row,s);}return {ok:true};});}
function getProfile(token){return publicUser_(requireUser_(token));}
function ownLocations_(u){return rows_('LOCATION_USERS').filter(x=>x.UserID===u.UserID&&flag_(x.Active)).map(x=>x.LocationID);}
function canManage_(u,id){return u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.LOCATION&&ownLocations_(u).includes(id));}
function manageLocation_(u,id){check_(canManage_(u,id),'Geen beheerrecht voor deze locatie.');}
function getDashboard(token){const u=requireUser_(token);const locs=rows_('LOCATIONS').filter(x=>flag_(x.Active));
const visible=u.Role===PS.ROLES.ADMIN?locs:locs.map(l=>({LocationID:l.LocationID,Name:l.Name,City:l.City,Active:l.Active}));
const bookingRows=getBookings(token),series=getLessonSeries(token),lessons=getLessons(token),conversations=getConversations(token);
return {version:PS.VERSION,user:publicUser_(u),locations:visible,ownLocationIds:ownLocations_(u),courts:getCourts(token),bookings:bookingRows.slice(-100),lessonSeries:series.slice(-100),lessons:lessons.slice(-100),conversations:conversations.slice(-100),counts:{locations:visible.length,bookings:bookingRows.length,lessonSeries:series.length,lessons:lessons.length}};
}
function getLocations(token){const u=requireUser_(token);return rows_('LOCATIONS').filter(x=>flag_(x.Active)).map(x=>u.Role===PS.ROLES.ADMIN?x:{LocationID:x.LocationID,Name:x.Name,City:x.City,Active:x.Active});}
function getCourts(token){const u=requireUser_(token);return rows_('COURTS').filter(x=>flag_(x.Active)).map(x=>u.Role===PS.ROLES.ADMIN||canManage_(u,x.LocationID)?x:{CourtID:x.CourtID,LocationID:x.LocationID,Name:x.Name,CourtNumber:x.CourtNumber,Active:x.Active});}
function getUsers(token){const u=requireUser_(token);if(u.Role===PS.ROLES.ADMIN)return rows_('USERS').map(publicUser_);
if(u.Role===PS.ROLES.LOCATION)return rows_('USERS').filter(x=>x.Role===PS.ROLES.TRAINER&&flag_(x.Active)).map(publicUser_);
if(u.Role===PS.ROLES.TRAINER){const ids=new Set(rows_('CONVERSATIONS').filter(c=>c.TrainerUserID===u.UserID).map(c=>c.PlayerUserID));return rows_('USERS').filter(x=>ids.has(x.UserID)).map(publicUser_);}return [publicUser_(u)];}
function getBookings(token){const u=requireUser_(token);return rows_('BOOKINGS').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.LOCATION?ownLocations_(u).includes(x.LocationID):x.UserID===u.UserID)).map(x=>u.Role===PS.ROLES.LOCATION?{BookingID:x.BookingID,LocationID:x.LocationID,CourtID:x.CourtID,Date:x.Date,StartTime:x.StartTime,EndTime:x.EndTime,Type:x.Type,Status:x.Status}:x);}
function getLessonSeries(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];
return rows_('LESSON_SERIES').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.TRAINER&&x.TrainerUserID===u.UserID));}
function getLessons(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];
const own=rows_('CONVERSATIONS').filter(x=>x.PlayerUserID===u.UserID).map(x=>x.TrainerUserID);
return rows_('LESSONS').filter(x=>u.Role===PS.ROLES.ADMIN||(u.Role===PS.ROLES.TRAINER&&x.TrainerUserID===u.UserID)||(u.Role===PS.ROLES.PLAYER&&own.includes(x.TrainerUserID)&&rows_('BOOKINGS').some(b=>b.UserID===u.UserID&&b.Date===x.Date&&b.CourtID===x.CourtID)));
}
function getAvailability(token,locationId,date){const u=requireUser_(token);check_(rows_('LOCATIONS').some(l=>l.LocationID===locationId&&flag_(l.Active)),'Locatie onbekend.');
return rows_('COURT_AVAILABILITY').filter(a=>a.LocationID===locationId&&(!date||a.Date===date)).map(a=>u.Role===PS.ROLES.ADMIN||canManage_(u,locationId)?a:{CourtID:a.CourtID,Date:a.Date,StartTime:a.StartTime,EndTime:a.EndTime,Status:a.Status});}
function saveAvailability(token,data){const u=requireUser_(token),d=data||{};manageLocation_(u,d.LocationID);
check_(find_('COURTS','CourtID',d.CourtID)?.LocationID===d.LocationID,'Baan hoort niet bij locatie.');validateSlot_(d);
const obj={AvailabilityID:uid_('AVL'),LocationID:d.LocationID,CourtID:d.CourtID,Date:d.Date,StartTime:d.StartTime,EndTime:d.EndTime,Status:'AVAILABLE',Visibility:'PUBLIC',Source:'MANUAL',ImportedAt:now_(),UpdatedAt:now_()};put_('COURT_AVAILABILITY',obj);audit_(u,'AVAILABILITY_CREATED','COURT_AVAILABILITY',obj.AvailabilityID);return obj;}
function validateSlot_(d){check_(/^\d{4}-\d{2}-\d{2}$/.test(d.Date||'')&&/^([01]\d|2[0-3]):[0-5]\d$/.test(d.StartTime||'')&&/^([01]\d|2[0-3]):[0-5]\d$/.test(d.EndTime||'')&&d.StartTime<d.EndTime,'Ongeldige datum of tijd.');}
function bookingDays_(id,role){const rule=rows_('BOOKING_RULES').filter(x=>x.LocationID===id&&x.Role===role&&flag_(x.Active)).pop();return rule?Number(rule.DaysInAdvance):PS.DEFAULT_BOOKING_DAYS[role];}
function slotAvailable_(d){const slots=rows_('COURT_AVAILABILITY').filter(a=>a.CourtID===d.CourtID&&a.LocationID===d.LocationID&&a.Date===d.Date);
return slots.some(a=>a.Status==='AVAILABLE'&&a.StartTime<=d.StartTime&&a.EndTime>=d.EndTime)&&!slots.some(a=>a.Status==='BLOCKED'&&a.StartTime<d.EndTime&&a.EndTime>d.StartTime);}
function overlaps_(a,d){return a.Date===d.Date&&a.CourtID===d.CourtID&&a.StartTime<d.EndTime&&a.EndTime>d.StartTime;}
function createBooking(token,data){const u=requireUser_(token),d=data||{};validateSlot_(d);
const court=find_('COURTS','CourtID',d.CourtID);check_(court&&flag_(court.Active)&&court.LocationID===d.LocationID,'Baan niet beschikbaar.');
const day=Math.floor((Date.parse(d.Date+'T00:00:00Z')-Date.parse(Utilities.formatDate(new Date(),'Europe/Amsterdam','yyyy-MM-dd')+'T00:00:00Z'))/86400000);
check_(day>=0&&day<=bookingDays_(d.LocationID,u.Role),'Deze datum valt buiten jouw boekingsvenster.');
check_(u.Role!==PS.ROLES.LOCATION||canManage_(u,d.LocationID),'Geen beheerrecht voor deze locatie.');
check_(u.Role!==PS.ROLES.PLAYER||d.Type!=='LESSON','Alleen trainers beheren lesboekingen.');
return lock_(()=>{check_(slotAvailable_(d),'Er is geen vrijgegeven tijdslot voor deze baan.');
check_(!rows_('BOOKINGS').some(b=>!['CANCELLED','EXPIRED'].includes(b.Status)&&overlaps_(b,d)),'Baan is al geboekt.');
check_(!rows_('BOOKING_HOLDS').some(b=>b.Status==='ACTIVE'&&Date.parse(b.ExpiresAt)>Date.now()&&overlaps_(b,d)),'Baan is tijdelijk gereserveerd.');
const obj={BookingID:uid_('BKG'),LocationID:d.LocationID,CourtID:d.CourtID,UserID:u.UserID,Date:d.Date,StartTime:d.StartTime,EndTime:d.EndTime,Type:u.Role===PS.ROLES.TRAINER?'LESSON':'PLAY',Priority:({ADMINISTRATOR:100,TRAINER:90,LOCATIEBEHEERDER:80,SPELER:20})[u.Role],Visibility:'PRIVATE',Status:'CONFIRMED',Source:'PADELOS',CreatedAt:now_(),UpdatedAt:now_()};put_('BOOKINGS',obj);audit_(u,'BOOKING_CREATED','BOOKINGS',obj.BookingID);return obj;
});
}
function createLessonSeries(token,data){const u=requireRole_(token,[PS.ROLES.ADMIN,PS.ROLES.TRAINER]),d=data||{};
check_(find_('LOCATIONS','LocationID',d.LocationID)&&safe_(d.Name,120)&&/^\d{4}-\d{2}-\d{2}$/.test(d.StartDate||'')&&/^\d{4}-\d{2}-\d{2}$/.test(d.EndDate||''),'Vul locatie, naam en datums in.');validateSlot_({Date:d.StartDate,StartTime:d.StartTime,EndTime:d.EndTime});
const obj={SeriesID:uid_('SER'),TrainerUserID:u.UserID,LocationID:d.LocationID,Name:safe_(d.Name,120),Description:safe_(d.Description,1000),StartDate:d.StartDate,EndDate:d.EndDate,DayOfWeek:safe_(d.DayOfWeek,30),StartTime:d.StartTime,EndTime:d.EndTime,NumberOfLessons:Number(d.NumberOfLessons)||1,Players:Number(d.Players)||1,Level:safe_(d.Level,50),Goal:safe_(d.Goal,500),Status:'REQUESTED',CreatedAt:now_(),UpdatedAt:now_()};put_('LESSON_SERIES',obj);return obj;}
function getConversations(token){const u=requireUser_(token);if(u.Role===PS.ROLES.LOCATION)return [];
return rows_('CONVERSATIONS').filter(c=>u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID);}
function getConversationMessages(token,conversationId){const u=requireUser_(token),c=find_('CONVERSATIONS','ConversationID',conversationId);
check_(c&&(u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID),'Geen toegang tot gesprek.');
return rows_('CONVERSATION_MESSAGES').filter(m=>m.ConversationID===conversationId).slice(-200);}
function startConversation(token,playerUserId,subject){const u=requireRole_(token,[PS.ROLES.ADMIN,PS.ROLES.TRAINER]);
const p=find_('USERS','UserID',playerUserId);check_(p&&p.Role===PS.ROLES.PLAYER,'Speler onbekend.');
if(u.Role===PS.ROLES.TRAINER)check_(rows_('CONVERSATIONS').some(c=>c.TrainerUserID===u.UserID&&c.PlayerUserID===p.UserID),'De administrator moet deze speler eerst aan jou koppelen.');
const c=put_('CONVERSATIONS',{ConversationID:uid_('CON'),TrainerUserID:u.UserID,PlayerUserID:p.UserID,Subject:safe_(subject,120),CreatedAt:now_(),UpdatedAt:now_()});return c;}
function createTrainerConversation(token,trainerId,playerId,subject){const u=requireRole_(token,[PS.ROLES.ADMIN]);
const t=find_('USERS','UserID',trainerId),p=find_('USERS','UserID',playerId);
check_(t&&t.Role===PS.ROLES.TRAINER&&p&&p.Role===PS.ROLES.PLAYER,'Kies een trainer en een speler.');
const c=put_('CONVERSATIONS',{ConversationID:uid_('CON'),TrainerUserID:t.UserID,PlayerUserID:p.UserID,Subject:safe_(subject,120),CreatedAt:now_(),UpdatedAt:now_()});audit_(u,'CONVERSATION_CREATED','CONVERSATIONS',c.ConversationID);return c;}
function sendConversationMessage(token,conversationId,message){const u=requireUser_(token),c=find_('CONVERSATIONS','ConversationID',conversationId);
check_(c&&(u.Role===PS.ROLES.ADMIN||c.TrainerUserID===u.UserID||c.PlayerUserID===u.UserID),'Geen toegang tot gesprek.');
check_(safe_(message,4000).length>0,'Bericht ontbreekt.');const m=put_('CONVERSATION_MESSAGES',{MessageID:uid_('MSG'),ConversationID:conversationId,SenderUserID:u.UserID,Message:safe_(message,4000),CreatedAt:now_()});return m;}
function getAdminData(token){requireRole_(token,[PS.ROLES.ADMIN]);return {users:rows_('USERS').map(publicUser_),locations:rows_('LOCATIONS'),audit:rows_('AUDIT_LOG').slice(-100),config:rows_('SYSTEM_CONFIG'),importSources:rows_('IMPORT_SOURCES')};}
function setBookingRule(token,d){const u=requireRole_(token,[PS.ROLES.ADMIN]),loc=find_('LOCATIONS','LocationID',d.LocationID);
check_(loc&&Object.values(PS.ROLES).includes(d.Role)&&Number.isInteger(Number(d.DaysInAdvance))&&Number(d.DaysInAdvance)>=0&&Number(d.DaysInAdvance)<=365,'Ongeldige regel.');
return lock_(()=>{let r=rows_('BOOKING_RULES').find(x=>x.LocationID===d.LocationID&&x.Role===d.Role&&flag_(x.Active));
if(r){r.DaysInAdvance=Number(d.DaysInAdvance);r.UpdatedAt=now_();update_('BOOKING_RULES',r._row,r);}
else r=put_('BOOKING_RULES',{RuleID:uid_('RULE'),LocationID:d.LocationID,Name:'Boekingsvenster '+d.Role,Role:d.Role,DaysInAdvance:Number(d.DaysInAdvance),Active:true,CreatedAt:now_(),UpdatedAt:now_()});audit_(u,'RULE_UPDATED','BOOKING_RULES',r.RuleID);return r;});}
function saveCourt(token,d){const u=requireUser_(token);manageLocation_(u,d.LocationID);check_(find_('LOCATIONS','LocationID',d.LocationID),'Locatie onbekend.');check_(safe_(d.Name,80),'Baan heeft een naam nodig.');
return put_('COURTS',{CourtID:uid_('CRT'),LocationID:d.LocationID,Name:safe_(d.Name,80),CourtNumber:safe_(d.CourtNumber,20),Active:true,CreatedAt:now_(),UpdatedAt:now_()});}
function savePrice(token,d){const u=requireUser_(token);manageLocation_(u,d.LocationID);check_(find_('COURTS','CourtID',d.CourtID)?.LocationID===d.LocationID,'Baan onbekend.');
const p=Number(d.Price);check_(Number.isFinite(p)&&p>=0,'Ongeldige prijs.');return put_('COURT_PRICES',{PriceID:uid_('PRC'),LocationID:d.LocationID,CourtID:d.CourtID,DayOfWeek:safe_(d.DayOfWeek,20),StartTime:safe_(d.StartTime,5),EndTime:safe_(d.EndTime,5),Price:p,Currency:'EUR',Active:true,CreatedAt:now_(),UpdatedAt:now_()});}
// CSV and JSON are mapped to canonical availability fields. URL import is admin-only and source host must be allowlisted separately.
function importAvailabilityCsv(token,locationId,csv){const u=requireUser_(token);manageLocation_(u,locationId);check_(typeof csv==='string'&&csv.length<=300000,'CSV is te groot (maximaal 300 KB).');
const matrix=Utilities.parseCsv(csv),header=(matrix.shift()||[]).map(x=>x.trim());
check_(['CourtID','Date','StartTime','EndTime'].every(k=>header.includes(k)),'CSV verwacht CourtID,Date,StartTime,EndTime.');
const records=matrix.filter(r=>r.some(Boolean)).map(r=>Object.fromEntries(header.map((h,i)=>[h,r[i]||''])));
return importAvailabilityRecords_(u,locationId,records,'CSV');}
function importAvailabilityJson(token,locationId,json){const u=requireUser_(token);manageLocation_(u,locationId);check_(typeof json==='string'&&json.length<=300000,'JSON is te groot.');
const records=JSON.parse(json);check_(Array.isArray(records),'JSON moet een lijst zijn.');return importAvailabilityRecords_(u,locationId,records,'JSON');}
function importAvailabilitySheet(token,locationId,spreadsheetId,tabName){const u=requireUser_(token);manageLocation_(u,locationId);
check_(/^[\w-]{20,}$/.test(spreadsheetId)&&safe_(tabName,100),'Ongeldige Sheet-bron.');
const sh=SpreadsheetApp.openById(spreadsheetId).getSheetByName(tabName);check_(sh,'Brontab niet gevonden.');check_(sh.getLastRow()<=1001,'Maximaal 1000 rijen per import.');
const values=sh.getDataRange().getDisplayValues(),headers=values.shift(),records=values.map(r=>Object.fromEntries(headers.map((h,i)=>[h,r[i]])));
return importAvailabilityRecords_(u,locationId,records,'SHEET');}
function importAvailabilityRecords_(u,locationId,records,source){check_(records.length<=1000,'Maximaal 1000 rijen per import.');
const courtIds=new Set(rows_('COURTS').filter(c=>c.LocationID===locationId).map(c=>c.CourtID));
records.forEach(r=>{check_(courtIds.has(r.CourtID),'Onbekende CourtID in import.');validateSlot_(r);check_(!r.Status||['AVAILABLE','BLOCKED'].includes(r.Status),'Status moet AVAILABLE of BLOCKED zijn.');});
return lock_(()=>{const existing=new Set(rows_('COURT_AVAILABILITY').filter(a=>a.LocationID===locationId).map(a=>[a.CourtID,a.Date,a.StartTime,a.EndTime,a.Source].join('|')));let added=0;
records.forEach(r=>{const key=[r.CourtID,r.Date,r.StartTime,r.EndTime,source].join('|');if(existing.has(key))return;existing.add(key);put_('COURT_AVAILABILITY',{AvailabilityID:uid_('AVL'),LocationID:locationId,CourtID:r.CourtID,Date:r.Date,StartTime:r.StartTime,EndTime:r.EndTime,Status:r.Status||'AVAILABLE',Visibility:'PUBLIC',Source:source,ImportedAt:now_(),UpdatedAt:now_()});added++;});
put_('IMPORT_RUNS',{ImportRunID:uid_('RUN'),LocationID:locationId,StartedAt:now_(),FinishedAt:now_(),Status:'SUCCESS',RecordsRead:records.length,RecordsImported:added,RecordsSkipped:records.length-added,CreatedAt:now_()});audit_(u,'AVAILABILITY_IMPORTED','LOCATIONS',locationId);return {ok:true,read:records.length,imported:added};});}
function getPadelOSSystemInfo(token){requireRole_(token,[PS.ROLES.ADMIN]);const ss=db_();return {version:PS.VERSION,spreadsheetId:ss.getId(),sheets:ss.getSheets().map(s=>({name:s.getName(),rows:s.getLastRow(),columns:s.getLastColumn()}))};}
<!doctype html>
<html lang="nl"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>PadelSchoolOS V1.1</title>
<style>
:root{font-family:system-ui,-apple-system,Segoe UI,sans-serif;color:#152b3a;background:#eef6f3}*{box-sizing:border-box}body{margin:0}header{background:#083b42;color:white;padding:1rem max(1rem,calc((100vw - 1160px)/2));display:flex;justify-content:space-between;align-items:center;gap:1rem;flex-wrap:wrap}h1{margin:0;font-size:1.3rem}h2{margin-top:0}main{max-width:1160px;margin:1.4rem auto;padding:0 1rem}section,.card{background:white;border-radius:16px;padding:1.2rem;margin-bottom:1rem;box-shadow:0 3px 16px #103d3114}button{border:0;border-radius:9px;background:#087568;color:white;padding:.7rem 1rem;cursor:pointer;font:inherit}button:hover{background:#065d53}button.secondary{background:#e3efeb;color:#173a38}button.danger{background:#ad3443}button:disabled{opacity:.5;cursor:wait}input,select,textarea{width:100%;padding:.7rem;border:1px solid #b8cbc7;border-radius:9px;font:inherit;background:white}label{display:block;margin:.65rem 0;font-weight:600}label small{font-weight:400}textarea{min-height:110px}.grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(220px,1fr));gap:1rem}.row{display:flex;gap:.6rem;align-items:center;flex-wrap:wrap}.row>*{flex:1}.nav{display:flex;gap:.4rem;flex-wrap:wrap;margin:1rem 0}.nav button{flex:none}.muted{color:#546b69}.error{color:#a32634}.good{color:#086448}#status{min-height:1.5em}.stat{font-size:1.7rem;font-weight:800;color:#075e56}.small{font-size:.9rem}table{width:100%;border-collapse:collapse;display:block;overflow:auto}th,td{text-align:left;padding:.6rem;border-bottom:1px solid #d9e5e1;vertical-align:top;white-space:nowrap}th{background:#edf6f2}.hidden{display:none!important}pre{white-space:pre-wrap;overflow-wrap:anywhere}.pill{border-radius:20px;background:#e6f2ec;padding:.2rem .6rem}.account{display:flex;justify-content:space-between;gap:.6rem;align-items:center;margin:.5rem 0;padding:.6rem;border:1px solid #dce7e2;border-radius:9px}
</style></head><body>
<header><h1>🎾 PadelSchoolOS <small>V1.1</small></h1><div id="who">Veilig aanmelden</div></header>
<main><p id="status" role="status" aria-live="polite"></p>
<section id="loginPanel"><h2>Aanmelden</h2><p class="muted">Een nieuwe sessie begint altijd met een code per e-mail. Daarna stel je de eerste keer een wachtwoord in, of voer je jouw bestaande wachtwoord in.</p>
<div id="saved"></div>
<form id="emailForm"><label>E-mailadres<input id="email" type="email" autocomplete="username" required></label><button>Mailcode aanvragen</button></form>
<form id="codeForm" class="hidden"><label>Code uit e-mail<input id="code" inputmode="numeric" pattern="[0-9]{6}" maxlength="6" required></label><button>Code controleren</button></form>
<form id="passwordForm" class="hidden"><label id="passLabel">Wachtwoord<input id="password" type="password" autocomplete="current-password"></label><button id="passButton">Inloggen</button></form>
</section>
<div id="app" class="hidden"><section><div class="row"><div><strong id="roleTitle"></strong><div class="muted" id="accountName"></div></div><button class="secondary" id="switch">Wissel account</button><button class="secondary" id="addAccount">+ Account toevoegen</button><button class="danger" id="logout">Uitloggen</button></div></section>
<nav class="nav" id="navigation"></nav><div id="view"></div></div>
</main><script>
'use strict';
const roles={ADMINISTRATOR:'Administrator',LOCATIEBEHEERDER:'Locatiebeheerder',TRAINER:'Trainer',SPELER:'Speler'};
const menus={ADMINISTRATOR:['Dashboard','Locaties','Banen','Beschikbaarheid','Boekingen','Lessen','Lesreeksen','Conversaties','Gebruikers','Import','Configuratie'],LOCATIEBEHEERDER:['Dashboard','Locaties','Banen','Beschikbaarheid','Boekingen','Trainerslijst','Import'],TRAINER:['Dashboard','Locaties','Beschikbaarheid','Boekingen','Lessen','Lesreeksen','Conversaties','Spelers'],SPELER:['Dashboard','Locaties','Beschikbaarheid','Boekingen','Lessen','Conversaties','Profiel']};
const $=id=>document.getElementById(id), esc=x=>String(x==null?'':x).replace(/[&<>"']/g,c=>({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]));
let activeToken='',profile=null,dashboard=null,emailForLogin='',ticket='',firstTime=false,currentView='Dashboard';
const key='padelos.v11.accounts';
function accounts(){try{return JSON.parse(localStorage.getItem(key)||'[]').filter(a=>a&&a.token&&a.email)}catch(e){return []}}
function saveAccount(a){const list=accounts().filter(x=>x.email!==a.email);list.push(a);localStorage.setItem(key,JSON.stringify(list));}
function removeAccount(token){localStorage.setItem(key,JSON.stringify(accounts().filter(a=>a.token!==token)));}
function status(message,bad=false){$('status').textContent=message;$('status').className=bad?'error':'good';}
function api(method,...args){return new Promise((resolve,reject)=>{google.script.run.withSuccessHandler(resolve).withFailureHandler(e=>reject(new Error(e&&e.message||String(e))))[method](...args)})}
async function run(fn){try{status('Bezig…');await fn();status('Gereed.')}catch(e){status(e.message,true)}}
function resetLogin(){ticket='';$('loginPanel').classList.remove('hidden');$('app').classList.add('hidden');$('emailForm').classList.remove('hidden');$('codeForm').classList.add('hidden');$('passwordForm').classList.add('hidden');$('code').value='';$('password').value='';$('who').textContent='Veilig aanmelden';drawAccounts()}
function drawAccounts(){const a=accounts();$('saved').innerHTML=a.length?'<h3>Accounts in deze browser</h3>'+a.map((x,i)=>`<div class="account"><span>${esc(x.name)} · ${esc(roles[x.role]||x.role)}<br><small>${esc(x.email)}</small></span><button type="button" class="secondary" data-switch="${i}">Open</button></div>`).join(''):'<p class="muted">Er zijn nog geen accounts in deze browser opgeslagen.</p>';
$('saved').querySelectorAll('[data-switch]').forEach(b=>b.onclick=()=>activate(a[Number(b.dataset.switch)].token));}
async function activate(token){try{const u=await api('getProfile',token);activeToken=token;profile=u;dashboard=await api('getDashboard',token);saveAccount({token,email:u.email,name:u.name,role:u.role});$('loginPanel').classList.add('hidden');$('app').classList.remove('hidden');$('who').textContent=u.name+' · '+roles[u.role];$('roleTitle').textContent=roles[u.role];$('accountName').textContent=u.email;showView('Dashboard');status('Welkom, '+u.name+'.');}catch(e){removeAccount(token);resetLogin();status('Deze sessie is verlopen. Vraag opnieuw een mailcode aan.',true)}}
$('emailForm').onsubmit=e=>{e.preventDefault();run(async()=>{emailForLogin=$('email').value.trim().toLowerCase();const res=await api('requestLoginCode',emailForLogin);$('emailForm').classList.add('hidden');$('codeForm').classList.remove('hidden');$('code').focus();status(res.message)})};
$('codeForm').onsubmit=e=>{e.preventDefault();run(async()=>{const res=await api('verifyLoginCode',emailForLogin,$('code').value.trim());ticket=res.ticket;firstTime=res.firstTime;$('codeForm').classList.add('hidden');$('passwordForm').classList.remove('hidden');$('passLabel').firstChild.textContent=firstTime?'Stel een persoonlijk wachtwoord in (minimaal 12 tekens)':'Jouw wachtwoord';$('password').autocomplete=firstTime?'new-password':'current-password';$('passButton').textContent=firstTime?'Wachtwoord instellen en aanmelden':'Inloggen';$('password').focus()})};
$('passwordForm').onsubmit=e=>{e.preventDefault();run(async()=>{const password=$('password').value;const res=await api('finishLogin',emailForLogin,ticket,firstTime?'':password,firstTime?password:'');$('password').value='';await activate(res.token)})};
$('switch').onclick=()=>{activeToken='';resetLogin();status('Kies een account of vraag een nieuwe mailcode aan.')};$('addAccount').onclick=()=>{activeToken='';resetLogin();$('email').value='';$('email').focus()};
$('logout').onclick=()=>run(async()=>{const token=activeToken;await api('logout',token);removeAccount(token);activeToken='';resetLogin();status('Uitgelogd. Andere opgeslagen sessies blijven beschikbaar.')});
function nav(){const list=menus[profile.role]||['Dashboard'];$('navigation').innerHTML=list.map(v=>`<button type="button" class="${v===currentView?'':'secondary'}" data-view="${esc(v)}">${esc(v)}</button>`).join('');$('navigation').querySelectorAll('button').forEach(b=>b.onclick=()=>showView(b.dataset.view))}
function options(rows,id,label){return rows.map(r=>`<option value="${esc(r[id])}">${esc(r[label])}</option>`).join('')}
function table(rows,fields){if(!rows.length)return '<p class="muted">Nog geen gegevens zichtbaar.</p>';return `<table><thead><tr>${fields.map(f=>`<th>${esc(f)}</th>`).join('')}</tr></thead><tbody>${rows.map(row=>`<tr>${fields.map(f=>`<td>${esc(row[f])}</td>`).join('')}</tr>`).join('')}</tbody></table>`}
function section(title,html){return `<section><h2>${esc(title)}</h2>${html}</section>`}
function showView(view){if(!(menus[profile.role]||[]).includes(view))return;currentView=view;nav();const u=profile,d=dashboard;let html='';
if(view==='Dashboard')html=section('Overzicht',`<div class="grid">${Object.entries(d.counts).map(([k,v])=>`<div class="card"><div class="stat">${v}</div>${esc(({locations:'Locaties',bookings:'Boekingen',lessonSeries:'Lesreeksen',lessons:'Lessen'})[k]||k)}</div>`).join('')}</div><p>Rol: <span class="pill">${esc(roles[u.role])}</span></p>`);
if(view==='Locaties')html=section('Locaties',table(d.locations,['LocationID','Name','City']));
if(view==='Banen')html=section('Banen',table(d.courts,['CourtID','LocationID','Name','CourtNumber'])+(u.role==='ADMINISTRATOR'||u.role==='LOCATIEBEHEERDER'?`<h3>Baan toevoegen</h3><form id="courtForm"><div class="grid"><label>Mijn locatie<select name="LocationID">${options(d.locations.filter(l=>u.role==='ADMINISTRATOR'||d.ownLocationIds.includes(l.LocationID)),'LocationID','Name')}</select></label><label>Naam<input name="Name" required></label><label>Baannummer<input name="CourtNumber"></label></div><button>Baan toevoegen</button></form><h3>Prijs instellen</h3><form id="priceForm"><div class="grid"><label>Mijn locatie<select name="LocationID" id="priceLocation">${options(d.locations.filter(l=>u.role==='ADMINISTRATOR'||d.ownLocationIds.includes(l.LocationID)),'LocationID','Name')}</select></label><label>Baan<select name="CourtID" id="priceCourt"></select></label><label>Prijs in euro<input name="Price" type="number" min="0" step=".01" required></label><label>Weekdag<input name="DayOfWeek" placeholder="bijv. maandag"></label><label>Van<input name="StartTime" type="time"></label><label>Tot<input name="EndTime" type="time"></label></div><button>Prijs bewaren</button></form>`:''));
if(view==='Boekingen')html=section('Boekingen',`<p class="muted">Boekingen worden per rol gefilterd.</p>${table(d.bookings,['BookingID','LocationID','CourtID','Date','StartTime','EndTime','Type','Status'])}<h3>Nieuwe baanboeking</h3><form id="bookingForm"><div class="grid"><label>Locatie<select name="LocationID" id="bookingLocation">${options(d.locations,'LocationID','Name')}</select></label><label>Baan<select name="CourtID" id="bookingCourt"></select></label><label>Datum<input type="date" name="Date" required></label><label>Van<input type="time" name="StartTime" required></label><label>Tot<input type="time" name="EndTime" required></label></div><button>Boeken</button></form>`);
if(view==='Beschikbaarheid')html=section('Beschikbaarheid',`<p>Toon vrijgegeven tijdsloten per locatie.</p><div class="row"><select id="avLocation">${options(d.locations,'LocationID','Name')}</select><input type="date" id="avDate"><button id="avLoad">Zoeken</button></div><div id="avResult"></div>${[roles.ADMINISTRATOR,roles.LOCATIEBEHEERDER].includes(roles[u.role])?`<h3>Tijdslot vrijgeven</h3><form id="avForm"><div class="grid"><label>Mijn locatie<select name="LocationID" id="avOwnLocation">${options(d.locations.filter(l=>u.role==='ADMINISTRATOR'||d.ownLocationIds.includes(l.LocationID)),'LocationID','Name')}</select></label><label>Baan<select name="CourtID" id="avCourt"></select></label><label>Datum<input name="Date" type="date" required></label><label>Van<input name="StartTime" type="time" required></label><label>Tot<input name="EndTime" type="time" required></label></div><button>Vrijgeven</button></form>`:''}`);
if(view==='Lessen')html=section('Lessen',table(d.lessons,['LessonID','SeriesID','TrainerUserID','Date','StartTime','EndTime','Status']));
if(view==='Lesreeksen')html=section('Lesreeksen',table(d.lessonSeries,['SeriesID','Name','StartDate','EndDate','Status']));
if(view==='Conversaties')html=section('Gesprekken',`${table(d.conversations,['ConversationID','Subject','TrainerUserID','PlayerUserID'])}<label>Gesprek<select id="conversationId">${options(d.conversations,'ConversationID','Subject')}</select></label><button id="messageLoad">Lees berichten</button><div id="messageList"></div><form id="messageForm"><label>Bericht<textarea name="Message" required maxlength="4000"></textarea></label><button>Verstuur bericht</button></form>${u.role==='TRAINER'||u.role==='ADMINISTRATOR'?'<h3>Gesprek starten</h3><form id="newConversation"><label>Speler<select name="PlayerUserID" id="playerSelect"></select></label><label>Onderwerp<input name="Subject" required></label><button>Start gesprek</button></form>':''}`);
if(view==='Gebruikers'||view==='Trainerslijst'||view==='Spelers')html=section(view,'<div id="usersResult">Gegevens laden…</div>');
if(view==='Profiel')html=section('Mijn profiel',`<p>${esc(u.name)}<br>${esc(u.email)}<br>${esc(roles[u.role])}</p>`);
if(view==='Import')html=section('Beschikbaarheid importeren',`<p>CSV-kolommen: <code>CourtID,Date,StartTime,EndTime,Status</code>. Gebruik de CourtID uit het banenoverzicht. Ook JSON en een bestaande Google Sheet zijn beschikbaar.</p><form id="importForm"><label>Mijn locatie<select name="LocationID">${options(d.locations.filter(l=>u.role==='ADMINISTRATOR'||d.ownLocationIds.includes(l.LocationID)),'LocationID','Name')}</select></label><label>Formaat<select name="Format"><option>CSV</option><option>JSON</option><option>SHEET</option></select></label><label>CSV of JSON tekst (bij SHEET: spreadsheet-ID)<textarea name="Payload"></textarea></label><label>Tabnaam voor SHEET<input name="SheetName"></label><button>Valideren en importeren</button></form><p class="muted">XLS/XLSX, Drive-bestandsimport en externe API hebben hier nog geen directe uitvoering. Exporteer die eerst als CSV of gebruik een Google Sheet.</p>`);
if(view==='Configuratie')html=section('Administrator · configuratie',`<h3>Spreadsheet-weergave</h3><p>Kolombreedte volgens tekst, maximaal 50 tekens; regelterugloop, automatische rijhoogte en vaste kopregel.</p><button id="format">Pas toe op alle tabs</button><h3>Boekingsvenster per locatie</h3><form id="ruleForm"><div class="grid"><label>Locatie<select name="LocationID">${options(d.locations,'LocationID','Name')}</select></label><label>Rol<select name="Role">${Object.keys(roles).map(r=>`<option value="${r}">${esc(roles[r])}</option>`).join('')}</select></label><label>Dagen vooruit<input name="DaysInAdvance" type="number" min="0" max="365" value="30" required></label></div><button>Bewaar boekingsregel</button></form><button class="secondary" id="info">Toon systeeminformatie</button><pre id="infoResult"></pre>`);
$('view').innerHTML=html;wire(view);
}
function formData(form){return Object.fromEntries(new FormData(form).entries())}
function courtPicker(locationId,selectId){const element=$(selectId);if(element)element.innerHTML=options(dashboard.courts.filter(x=>x.LocationID===locationId),'CourtID','Name')}
async function refresh(){dashboard=await api('getDashboard',activeToken);showView(currentView)}
function wire(view){
if(view==='Banen'&&$('courtForm')){$('courtForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('saveCourt',activeToken,formData(e.target));await refresh()})};const l=$('priceLocation');l.onchange=()=>courtPicker(l.value,'priceCourt');l.onchange();$('priceForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('savePrice',activeToken,formData(e.target));status('Prijs opgeslagen.')})}}
if(view==='Boekingen'){const l=$('bookingLocation');l.onchange=()=>courtPicker(l.value,'bookingCourt');l.onchange();$('bookingForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('createBooking',activeToken,formData(e.target));await refresh()})}}
if(view==='Beschikbaarheid'){$('avLoad').onclick=()=>run(async()=>{const rows=await api('getAvailability',activeToken,$('avLocation').value,$('avDate').value);$('avResult').innerHTML=table(rows,['CourtID','Date','StartTime','EndTime','Status'])});const l=$('avOwnLocation');if(l){l.onchange=()=>courtPicker(l.value,'avCourt');l.onchange();$('avForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('saveAvailability',activeToken,formData(e.target));await refresh()})}}}
if(view==='Conversaties'){const load=async()=>{const id=$('conversationId').value;if(!id)return;$('messageList').innerHTML=table(await api('getConversationMessages',activeToken,id),['SenderUserID','Message','CreatedAt'])};$('messageLoad').onclick=()=>run(load);$('messageForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('sendConversationMessage',activeToken,$('conversationId').value,formData(e.target).Message);e.target.reset();await load()})};if($('newConversation')){run(async()=>{const users=await api('getUsers',activeToken);$('playerSelect').innerHTML=options(users.filter(x=>x.role==='SPELER'),'userId','email')});$('newConversation').onsubmit=e=>{e.preventDefault();run(async()=>{const d=formData(e.target);await api('startConversation',activeToken,d.PlayerUserID,d.Subject);await refresh()})}}}
if(['Gebruikers','Trainerslijst','Spelers'].includes(view))run(async()=>{$('usersResult').innerHTML=table(await api('getUsers',activeToken),['userId','name','email','role'])});
if(view==='Import')$('importForm').onsubmit=e=>{e.preventDefault();run(async()=>{const d=formData(e.target);const result=d.Format==='SHEET'?await api('importAvailabilitySheet',activeToken,d.LocationID,d.Payload.trim(),d.SheetName.trim()):d.Format==='JSON'?await api('importAvailabilityJson',activeToken,d.LocationID,d.Payload):await api('importAvailabilityCsv',activeToken,d.LocationID,d.Payload);status(`${result.imported} van ${result.read} tijdsloten geïmporteerd.`)})};
if(view==='Configuratie'){$('format').onclick=()=>run(async()=>{const r=await api('adminFormatSpreadsheet',activeToken,50);status(`${r.tabs} tabs opgemaakt.`)});$('ruleForm').onsubmit=e=>{e.preventDefault();run(async()=>{await api('setBookingRule',activeToken,formData(e.target));status('Boekingsregel opgeslagen.')})};$('info').onclick=()=>run(async()=>{$('infoResult').textContent=JSON.stringify(await api('getPadelOSSystemInfo',activeToken),null,2)})}
}
drawAccounts();const remembered=accounts();if(remembered.length===1)activate(remembered[0].token);
</script></body></html>
/** PadelSchoolOS V1.1 — install alongside Code.gs and Index.html. */
const PS = Object.freeze({
VERSION: '1.1', BUILD: '2026-09-26',
DATABASE_ID: '10Ek9wIHF_eneU5LAsG3QE3w0Gx61Xf1WMfAjYbmSBlo',
ADMIN_EMAIL: 'padelstijn@gmail.com',
ROLES: Object.freeze({ADMIN:'ADMINISTRATOR', LOCATION:'LOCATIEBEHEERDER', TRAINER:'TRAINER', PLAYER:'SPELER'}),
TEST_USERS: Object.freeze([
['padelstijn@gmail.com','PadelStijn','ADMINISTRATOR'],
['gabeler.eu@gmail.com','Gabeler','LOCATIEBEHEERDER'],
['padellessons@gmail.com','PadelLessons','TRAINER'],
['godogood@gmail.com','Godogood','SPELER'],
['184840582uwv@gmail.com','Testspeler','SPELER'],
['stijn.gabeler@gmail.com','Stijn','SPELER'],
['godogood.eu@gmail.com','GODOGOOD.eu','SPELER']
]),
EXTRA_HEADERS: Object.freeze({
AUTH_USERS:['UserID','Email','PasswordSalt','PasswordHash','EmailVerified','CreatedAt','UpdatedAt'],
LOGIN_CODES:['CodeID','UserID','CodeHash','ExpiresAt','Attempts','UsedAt','CreatedAt'],
LOGIN_SESSIONS:['SessionID','UserID','TokenHash','ExpiresAt','RevokedAt','CreatedAt'],
CONVERSATIONS:['ConversationID','TrainerUserID','PlayerUserID','Subject','CreatedAt','UpdatedAt'],
CONVERSATION_MEMBERS:['ID','ConversationID','UserID','CreatedAt'],
CONVERSATION_MESSAGES:['MessageID','ConversationID','SenderUserID','Message','CreatedAt']
}),
DEFAULT_BOOKING_DAYS: Object.freeze({ADMINISTRATOR:90, TRAINER:60, LOCATIEBEHEERDER:30, SPELER:14})
});
Ik heb PadelSchoolOS V1.1 als drie afzonderlijke Apps Script-bestanden gemaakt:
Config.gs — database-ID, rollen en testaccounts
Code.gs — login, sessies, rolrechten, boekingen en databasefuncties
Index.html — login en vier rolgebonden schermen
De code sluit aan op de 23 tabs en kolommen van je meegestuurde spreadsheet. setupPadelOS() voegt ontbrekende tabs en testgegevens toe en past op alle tabs kolombreedte tot maximaal 50 tekens, tekstterugloop, rijhoogte en een vaste kopregel toe. Een nieuwe login vereist een mailcode; 1234 geeft geen toegang. Boekingsvensters zijn per locatie en rol instelbaar. CSV, JSON en Google Sheets zijn als beschikbaarheidsimport uitgewerkt.
Nog niet live: ik heb de Google Sheet of het Apps Script-project niet gewijzigd of gepubliceerd. De meegestuurde bijlage bevatte geen bestaande Code.gs of Index.html; daardoor kan ik niet bevestigen dat functies uit die huidige scripts behouden zijn. XLS/XLSX-, Drive-bestands- en API-import hebben nog geen directe uitvoering. De bestanden zijn op syntaxis gecontroleerd, maar de mailcode en boekingen kunnen pas in het Apps Script-project met de echte Sheet worden getest.
Plaats de drie bestanden samen in het project en voer als padelstijn@gmail.com eerst setupPadelOS() uit. Vervang bestaande projectbestanden pas nadat je ze hebt bewaard en vergeleken.
/************************************************************
* PADELSCHOOLOS V1
* Version: 26092026
*
* PADELOS by Gabeler.eu ~ GODOGOOD.eu
*
* Administrator:
* PadelStijn@gmail.com
* +31622869860
*
* Roles:
* ADMINISTRATOR
* LOCATIEBEHEERDER
* TRAINER
* SPELER
*
* Architecture:
*
* Google Account
* ↓
* USERS
* ↓
* ROLE
* ↓
* SERVER-SIDE AUTHORIZATION
* ↓
* PadelSchoolOS
* ↓
* Google Sheets Database
*
* IMPORTANT:
* - This file uses the fixed PadelSchoolOS database.
* - Existing sheets are NOT deleted.
* - Existing columns are NOT deleted.
* - Missing columns are added.
* - Existing data is preserved.
************************************************************/
/* ==========================================================
SYSTEM CONFIGURATION
========================================================== */
const PADELOS = {
VERSION: '1.0.0',
BUILD: '26092026',
NAME: 'PadelSchoolOS',
BRAND:
'PADELOS by Gabeler.eu ~ GODOGOOD.eu',
ADMIN_EMAIL:
'PadelStijn@gmail.com',
ADMIN_PHONE:
'+31622869860',
GABELER_URL:
'https://gabeler.eu',
GODOGOOD_URL:
'https://godogood.eu',
DATABASE_ID:
'10Ek9wIHF_eneU5LAsG3QE3w0Gx61Xf1WMfAjYbmSBlo',
ROLES: {
ADMIN:
'ADMINISTRATOR',
LOCATION:
'LOCATIEBEHEERDER',
TRAINER:
'TRAINER',
PLAYER:
'SPELER'
},
STATUS: {
ACTIVE:
'ACTIVE',
INACTIVE:
'INACTIVE',
AVAILABLE:
'AVAILABLE',
BOOKED:
'BOOKED',
BLOCKED:
'BLOCKED',
UNKNOWN:
'UNKNOWN',
REQUESTED:
'REQUESTED',
HELD:
'HELD',
CONFIRMED:
'CONFIRMED',
CANCELLED:
'CANCELLED',
EXPIRED:
'EXPIRED'
}
};
/* ==========================================================
DATABASE TABLE DEFINITIONS
========================================================== */
const PADELOS_TABLES = {
SYSTEM_CONFIG: [
'ConfigID',
'SystemName',
'Version',
'Build',
'Brand',
'AdministratorEmail',
'AdministratorPhone',
'GabelerURL',
'GODOGOODURL',
'DatabaseSpreadsheetID',
'CreatedAt',
'UpdatedAt'
],
USERS: [
'UserID',
'Email',
'Name',
'Role',
'Active',
'Phone',
'Language',
'LastLogin',
'CreatedAt',
'UpdatedAt'
],
PERSONS: [
'PersonID',
'UserID',
'FirstName',
'LastName',
'DisplayName',
'Email',
'Phone',
'DateOfBirth',
'Gender',
'Address',
'PostalCode',
'City',
'Country',
'EmergencyContact',
'EmergencyPhone',
'Notes',
'Active',
'CreatedAt',
'UpdatedAt'
],
LOCATIONS: [
'LocationID',
'Name',
'Description',
'Address',
'PostalCode',
'City',
'Province',
'Country',
'Latitude',
'Longitude',
'ContactName',
'ContactEmail',
'ContactPhone',
'Website',
'BookingURL',
'OpeningHours',
'Timezone',
'Currency',
'Active',
'CreatedAt',
'UpdatedAt'
],
LOCATION_USERS: [
'ID',
'LocationID',
'UserID',
'Role',
'Active',
'StartDate',
'EndDate',
'CreatedAt',
'UpdatedAt'
],
COURT_TYPES: [
'CourtTypeID',
'Name',
'Description',
'IndoorOutdoor',
'GlassType',
'SurfaceType',
'Lights',
'Accessible',
'Active',
'CreatedAt',
'UpdatedAt'
],
COURTS: [
'CourtID',
'LocationID',
'CourtTypeID',
'Name',
'CourtNumber',
'CourtType',
'IndoorOutdoor',
'Lights',
'SurfaceType',
'BookingURL',
'Active',
'CreatedAt',
'UpdatedAt'
],
COURT_AVAILABILITY: [
'AvailabilityID',
'LocationID',
'CourtID',
'Date',
'StartTime',
'EndTime',
'Status',
'Visibility',
'PriorityAccess',
'Source',
'SourceID',
'ExternalBookingID',
'ImportedAt',
'UpdatedAt'
],
COURT_PRICES: [
'PriceID',
'LocationID',
'CourtID',
'DayOfWeek',
'StartTime',
'EndTime',
'Price',
'Currency',
'PriceType',
'MinimumDuration',
'Active',
'CreatedAt',
'UpdatedAt'
],
BOOKING_RULES: [
'RuleID',
'LocationID',
'Name',
'Description',
'Role',
'Priority',
'DaysInAdvance',
'HoursInAdvance',
'MaxDuration',
'MinDuration',
'AllowLesson',
'AllowSeries',
'AllowHold',
'Active',
'CreatedAt',
'UpdatedAt'
],
BOOKING_PRIORITIES: [
'PriorityID',
'LocationID',
'Name',
'Role',
'PriorityValue',
'EarlyVisibilityHours',
'EarlyBookingHours',
'Description',
'Active',
'CreatedAt',
'UpdatedAt'
],
IMPORT_SOURCES: [
'ImportID',
'LocationID',
'Name',
'Type',
'URL',
'FileID',
'SpreadsheetID',
'SheetName',
'Format',
'Frequency',
'Active',
'LastImport',
'LastStatus',
'LastError',
'RecordsImported',
'CreatedAt',
'UpdatedAt'
],
IMPORT_RUNS: [
'ImportRunID',
'ImportID',
'LocationID',
'StartedAt',
'FinishedAt',
'Status',
'RecordsRead',
'RecordsImported',
'RecordsSkipped',
'ErrorMessage',
'CreatedAt'
],
BOOKINGS: [
'BookingID',
'LocationID',
'CourtID',
'UserID',
'PersonID',
'Date',
'StartTime',
'EndTime',
'Type',
'Priority',
'Visibility',
'Status',
'Source',
'ExternalBookingID',
'Notes',
'CreatedAt',
'UpdatedAt'
],
BOOKING_HOLDS: [
'HoldID',
'BookingID',
'LocationID',
'CourtID',
'UserID',
'Date',
'StartTime',
'EndTime',
'ExpiresAt',
'Priority',
'Status',
'CreatedAt',
'UpdatedAt'
],
LESSONS: [
'LessonID',
'SeriesID',
'TrainerUserID',
'LocationID',
'CourtID',
'Date',
'StartTime',
'EndTime',
'LessonType',
'Level',
'PlayerCount',
'Goal',
'Status',
'Notes',
'CreatedAt',
'UpdatedAt'
],
LESSON_SERIES: [
'SeriesID',
'TrainerUserID',
'LocationID',
'Name',
'Description',
'StartDate',
'EndDate',
'DayOfWeek',
'StartTime',
'EndTime',
'NumberOfLessons',
'Players',
'Level',
'Goal',
'Priority',
'Status',
'Notes',
'CreatedAt',
'UpdatedAt'
],
LESSON_COURTS: [
'ID',
'LessonID',
'SeriesID',
'CourtID',
'BookingID',
'Date',
'StartTime',
'EndTime',
'Status',
'CreatedAt',
'UpdatedAt'
],
TRAINER_AVAILABILITY: [
'AvailabilityID',
'TrainerUserID',
'LocationID',
'Date',
'StartTime',
'EndTime',
'Status',
'LessonType',
'MaxPlayers',
'Notes',
'CreatedAt',
'UpdatedAt'
],
NOTIFICATIONS: [
'NotificationID',
'UserID',
'Type',
'Title',
'Message',
'Entity',
'EntityID',
'Read',
'CreatedAt',
'ReadAt'
],
MESSAGES: [
'MessageID',
'SenderUserID',
'ReceiverUserID',
'LocationID',
'Subject',
'Message',
'RelatedEntity',
'RelatedEntityID',
'Read',
'CreatedAt',
'ReadAt'
],
AUDIT_LOG: [
'LogID',
'UserEmail',
'UserID',
'Action',
'Entity',
'EntityID',
'Details',
'CreatedAt'
]
};
/* ==========================================================
WEB APP
========================================================== */
function doGet() {
initializeDatabase_();
return HtmlService
.createHtmlOutputFromFile(
'Index'
)
.setTitle(
'PadelSchoolOS'
)
.setXFrameOptionsMode(
HtmlService.XFrameOptionsMode.ALLOWALL
);
}
/* ==========================================================
DATABASE CONNECTION
========================================================== */
function getDatabase_() {
return SpreadsheetApp.openById(
PADELOS.DATABASE_ID
);
}
/* ==========================================================
INITIALIZE DATABASE
========================================================== */
function initializeDatabase_() {
const ss =
getDatabase_();
Object.keys(
PADELOS_TABLES
).forEach(function(tableName) {
ensureSheetAndColumns_(
ss,
tableName,
PADELOS_TABLES[tableName]
);
});
configureSpreadsheet_(
ss
);
ensureSystemConfig_(
ss
);
ensureAdministrator_(
ss
);
return {
success: true,
spreadsheetId: ss.getId(),
spreadsheetName: ss.getName(),
version: PADELOS.VERSION,
build: PADELOS.BUILD
};
}
/* ==========================================================
ENSURE SHEET + COLUMNS
========================================================== */
function ensureSheetAndColumns_(
ss,
sheetName,
requiredColumns
) {
let sheet =
ss.getSheetByName(
sheetName
);
if (!sheet) {
sheet =
ss.insertSheet(
sheetName
);
}
const lastColumn =
Math.max(
sheet.getLastColumn(),
1
);
let headers = [];
if (
sheet.getLastRow() >= 1 &&
sheet.getLastColumn() >= 1
) {
headers =
sheet
.getRange(
1,
1,
1,
sheet.getLastColumn()
)
.getValues()[0]
.map(function(value) {
return String(
value
).trim();
})
.filter(Boolean);
}
if (!headers.length) {
sheet
.getRange(
1,
1,
1,
requiredColumns.length
)
.setValues([
requiredColumns
]);
} else {
const missing =
requiredColumns.filter(
function(column) {
return headers.indexOf(
column
) === -1;
}
);
if (missing.length) {
const startColumn =
headers.length + 1;
sheet
.getRange(
1,
startColumn,
1,
missing.length
)
.setValues([
missing
]);
}
}
sheet.setFrozenRows(
1
);
sheet
.getRange(
1,
1,
1,
sheet.getLastColumn()
)
.setFontWeight(
'bold'
);
sheet.autoResizeColumns(
1,
sheet.getLastColumn()
);
}
/* ==========================================================
SPREADSHEET CONFIGURATION
========================================================== */
function configureSpreadsheet_(
ss
) {
try {
ss.setSpreadsheetLocale(
'nl_NL'
);
ss.setSpreadsheetTimeZone(
'Europe/Amsterdam'
);
} catch (error) {
console.log(
error.message
);
}
}
/* ==========================================================
SYSTEM CONFIG
========================================================== */
function ensureSystemConfig_(
ss
) {
const sheet =
ss.getSheetByName(
'SYSTEM_CONFIG'
);
const rows =
getRowsFromSheet_(
sheet
);
const existing =
rows.find(
function(row) {
return row.ConfigID ===
'PADELOS_SYSTEM';
}
);
const record = {
ConfigID:
'PADELOS_SYSTEM',
SystemName:
PADELOS.NAME,
Version:
PADELOS.VERSION,
Build:
PADELOS.BUILD,
Brand:
PADELOS.BRAND,
AdministratorEmail:
PADELOS.ADMIN_EMAIL,
AdministratorPhone:
PADELOS.ADMIN_PHONE,
GabelerURL:
PADELOS.GABELER_URL,
GODOGOODURL:
PADELOS.GODOGOOD_URL,
DatabaseSpreadsheetID:
PADELOS.DATABASE_ID,
CreatedAt:
existing
? existing.CreatedAt
: now_(),
UpdatedAt:
now_()
};
if (!existing) {
appendObjectToSheet_(
sheet,
record
);
} else {
updateObjectInSheet_(
sheet,
'ConfigID',
'PADELOS_SYSTEM',
record
);
}
}
/* ==========================================================
ADMINISTRATOR
========================================================== */
function ensureAdministrator_(
ss
) {
const sheet =
ss.getSheetByName(
'USERS'
);
const rows =
getRowsFromSheet_(
sheet
);
const existing =
rows.find(
function(row) {
return String(
row.Email
).toLowerCase() ===
PADELOS.ADMIN_EMAIL.toLowerCase();
}
);
if (!existing) {
appendObjectToSheet_(
sheet,
{
UserID:
'USR_ADMIN_PADelSTIJN',
Email:
PADELOS.ADMIN_EMAIL,
Name:
'PadelStijn',
Role:
PADELOS.ROLES.ADMIN,
Active:
true,
Phone:
PADELOS.ADMIN_PHONE,
Language:
'nl',
LastLogin:
'',
CreatedAt:
now_(),
UpdatedAt:
now_()
}
);
} else {
updateObjectInSheet_(
sheet,
'UserID',
existing.UserID,
{
UserID:
existing.UserID,
Email:
PADELOS.ADMIN_EMAIL,
Name:
existing.Name ||
'PadelStijn',
Role:
PADELOS.ROLES.ADMIN,
Active:
true,
Phone:
existing.Phone ||
PADELOS.ADMIN_PHONE,
Language:
existing.Language ||
'nl',
LastLogin:
existing.LastLogin ||
'',
CreatedAt:
existing.CreatedAt ||
now_(),
UpdatedAt:
now_()
}
);
}
}
/* ==========================================================
CURRENT USER
========================================================== */
function getCurrentUser() {
initializeDatabase_();
const email =
getCurrentEmail_();
if (!email) {
return {
authenticated:
false,
active:
false,
email:
'',
name:
'',
role:
'',
message:
'Google-accountgegevens zijn niet beschikbaar.'
};
}
let user =
findUserByEmail_(
);
if (!user) {
user = {
UserID:
id_('USR'),
Email:
email,
Name:
email.split('@')[0],
Role:
PADELOS.ROLES.PLAYER,
Active:
true,
Phone:
'',
Language:
'nl',
LastLogin:
now_(),
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'USERS',
user
);
audit_(
'USER_CREATED',
'USERS',
user.UserID,
'Automatic first login registration'
);
} else {
user.LastLogin =
now_();
user.UpdatedAt =
now_();
updateObject_(
'USERS',
'UserID',
user.UserID,
user
);
}
if (
String(
user.Active
).toLowerCase() !==
'true'
) {
return {
authenticated:
true,
active:
false,
email:
email,
name:
user.Name,
role:
user.Role,
message:
'Je PadelOS-account is gedeactiveerd.'
};
}
return {
authenticated:
true,
active:
true,
email:
user.Email,
name:
user.Name,
role:
user.Role,
userId:
user.UserID
};
}
/* ==========================================================
========================================================== */
function getCurrentEmail_() {
return String(
Session
.getActiveUser()
.getEmail() || ''
)
.trim()
.toLowerCase();
}
/* ==========================================================
AUTHORIZATION
========================================================== */
function requireUser_() {
const user =
getCurrentUser();
if (!user.authenticated) {
throw new Error(
'Niet ingelogd.'
);
}
if (user.active === false) {
throw new Error(
'Account is gedeactiveerd.'
);
}
return user;
}
function requireRole_(
allowedRoles
) {
const user =
requireUser_();
if (
allowedRoles.indexOf(
user.role
) === -1
) {
throw new Error(
'Geen toestemming voor deze actie.'
);
}
return user;
}
function isAdmin_(
user
) {
return user.role ===
PADELOS.ROLES.ADMIN;
}
/* ==========================================================
GENERIC DATABASE HELPERS
========================================================== */
function getSheet_(
sheetName
) {
const sheet =
getDatabase_()
.getSheetByName(
sheetName
);
if (!sheet) {
throw new Error(
'Database-tab ontbreekt: ' +
sheetName
);
}
return sheet;
}
function getRows_(
sheetName
) {
return getRowsFromSheet_(
getSheet_(
sheetName
)
);
}
function getRowsFromSheet_(
sheet
) {
if (!sheet) {
return [];
}
if (
sheet.getLastRow() < 2 ||
sheet.getLastColumn() < 1
) {
return [];
}
const values =
sheet
.getRange(
1,
1,
sheet.getLastRow(),
sheet.getLastColumn()
)
.getValues();
const headers =
values[0];
return values
.slice(1)
.map(
function(row) {
const object = {};
headers.forEach(
function(header, index) {
object[
header
] =
row[index];
}
);
return object;
}
);
}
function appendObject_(
sheetName,
object
) {
appendObjectToSheet_(
getSheet_(
sheetName
),
object
);
}
function appendObjectToSheet_(
sheet,
object
) {
const headers =
sheet
.getRange(
1,
1,
1,
sheet.getLastColumn()
)
.getValues()[0];
const row =
headers.map(
function(header) {
return object[
header
] !== undefined
? object[header]
: '';
}
);
sheet.appendRow(
row
);
}
function updateObject_(
sheetName,
keyField,
keyValue,
object
) {
updateObjectInSheet_(
getSheet_(
sheetName
),
keyField,
keyValue,
object
);
}
function updateObjectInSheet_(
sheet,
keyField,
keyValue,
object
) {
const values =
sheet
.getDataRange()
.getValues();
if (
values.length < 2
) {
throw new Error(
'Geen records aanwezig.'
);
}
const headers =
values[0];
const keyIndex =
headers.indexOf(
keyField
);
if (keyIndex === -1) {
throw new Error(
'Key field ontbreekt: ' +
keyField
);
}
for (
let rowIndex = 1;
rowIndex < values.length;
rowIndex++
) {
if (
String(
values[rowIndex][keyIndex]
) ===
String(keyValue)
) {
const row =
headers.map(
function(header, index) {
return object[
header
] !== undefined
? object[header]
: values[rowIndex][index];
}
);
sheet
.getRange(
rowIndex + 1,
1,
1,
headers.length
)
.setValues([
row
]);
return true;
}
}
return false;
}
function findById_(
sheetName,
field,
value
) {
const rows =
getRows_(
sheetName
);
return rows.find(
function(row) {
return String(
row[field]
) ===
String(value);
}
) || null;
}
function findUserByEmail_(
) {
const rows =
getRows_(
'USERS'
);
return rows.find(
function(row) {
return String(
row.Email
)
.toLowerCase() ===
email.toLowerCase();
}
) || null;
}
/* ==========================================================
IDs
========================================================== */
function id_(
prefix
) {
return prefix +
'_' +
Utilities
.getUuid()
.replace(
/-/g,
''
);
}
/* ==========================================================
TIME
========================================================== */
function now_() {
return new Date();
}
/* ==========================================================
USERS
========================================================== */
function getUsers() {
requireRole_([
PADELOS.ROLES.ADMIN
]);
return getRows_(
'USERS'
);
}
function updateUserRole(
userId,
role
) {
requireRole_([
PADELOS.ROLES.ADMIN
]);
const validRoles =
Object.values(
PADELOS.ROLES
);
if (
validRoles.indexOf(
role
) === -1
) {
throw new Error(
'Ongeldige rol.'
);
}
const user =
findById_(
'USERS',
'UserID',
userId
);
if (!user) {
throw new Error(
'Gebruiker niet gevonden.'
);
}
user.Role =
role;
user.UpdatedAt =
now_();
updateObject_(
'USERS',
'UserID',
userId,
user
);
audit_(
'USER_ROLE_CHANGED',
'USERS',
userId,
role
);
return getUsers();
}
/* ==========================================================
LOCATIONS
========================================================== */
function getLocations() {
const user =
requireUser_();
const locations =
getRows_(
'LOCATIONS'
);
if (
isAdmin_(
user
)
) {
return locations;
}
if (
user.role ===
PADELOS.ROLES.TRAINER ||
user.role ===
PADELOS.ROLES.PLAYER
) {
return locations.filter(
function(location) {
return String(
location.Active
).toLowerCase() ===
'true';
}
);
}
const memberships =
getRows_(
'LOCATION_USERS'
);
const locationIds =
memberships
.filter(
function(row) {
return String(
row.UserID
) ===
String(
user.userId
) &&
String(
row.Active
).toLowerCase() ===
'true';
}
)
.map(
function(row) {
return String(
row.LocationID
);
}
);
return locations.filter(
function(location) {
return locationIds.indexOf(
String(
location.LocationID
)
) !== -1;
}
);
}
function createLocation(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
if (
!data ||
!data.Name
) {
throw new Error(
'Locatienaam is verplicht.'
);
}
const location = {
LocationID:
id_('LOC'),
Name:
data.Name,
Description:
data.Description || '',
Address:
data.Address || '',
PostalCode:
data.PostalCode || '',
City:
data.City || '',
Province:
data.Province || '',
Country:
data.Country ||
'Nederland',
Latitude:
data.Latitude || '',
Longitude:
data.Longitude || '',
ContactName:
data.ContactName || '',
ContactEmail:
data.ContactEmail || '',
ContactPhone:
data.ContactPhone || '',
Website:
data.Website || '',
BookingURL:
data.BookingURL || '',
OpeningHours:
data.OpeningHours || '',
Timezone:
data.Timezone ||
'Europe/Amsterdam',
Currency:
data.Currency ||
'EUR',
Active:
true,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'LOCATIONS',
location
);
if (
user.role ===
PADELOS.ROLES.LOCATION
) {
addLocationUser_(
location.LocationID,
user.userId,
PADELOS.ROLES.LOCATION
);
}
audit_(
'LOCATION_CREATED',
'LOCATIONS',
location.LocationID,
location.Name
);
return location;
}
/* ==========================================================
LOCATION USERS
========================================================== */
function addLocationUser_(
locationId,
userId,
role
) {
const existing =
getRows_(
'LOCATION_USERS'
).find(
function(row) {
return String(
row.LocationID
) ===
String(
locationId
) &&
String(
row.UserID
) ===
String(
userId
);
}
);
if (existing) {
return existing;
}
const membership = {
ID:
id_('LU'),
LocationID:
locationId,
UserID:
userId,
Role:
role,
Active:
true,
StartDate:
now_(),
EndDate:
'',
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'LOCATION_USERS',
membership
);
return membership;
}
function canManageLocation_(
userId,
locationId
) {
const memberships =
getRows_(
'LOCATION_USERS'
);
return memberships.some(
function(row) {
return String(
row.UserID
) ===
String(
userId
) &&
String(
row.LocationID
) ===
String(
locationId
) &&
String(
row.Active
).toLowerCase() ===
'true';
}
);
}
/* ==========================================================
COURT TYPES
========================================================== */
function getCourtTypes() {
requireUser_();
return getRows_(
'COURT_TYPES'
);
}
function createCourtType(
data
) {
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
const record = {
CourtTypeID:
id_('CT'),
Name:
data.Name ||
'PADEL',
Description:
data.Description || '',
IndoorOutdoor:
data.IndoorOutdoor ||
'Onbekend',
GlassType:
data.GlassType ||
'',
SurfaceType:
data.SurfaceType ||
'',
Lights:
data.Lights ||
'Onbekend',
Accessible:
data.Accessible ||
'Onbekend',
Active:
true,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'COURT_TYPES',
record
);
return record;
}
/* ==========================================================
COURTS
========================================================== */
function getCourts(
locationId
) {
requireUser_();
const courts =
getRows_(
'COURTS'
);
if (!locationId) {
return courts;
}
return courts.filter(
function(court) {
return String(
court.LocationID
) ===
String(
locationId
);
}
);
}
function createCourt(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
if (
!data.LocationID ||
!data.Name
) {
throw new Error(
'LocationID en baannaam zijn verplicht.'
);
}
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
data.LocationID
)
) {
throw new Error(
'Je beheert deze locatie niet.'
);
}
const court = {
CourtID:
id_('CRT'),
LocationID:
data.LocationID,
CourtTypeID:
data.CourtTypeID ||
'',
Name:
data.Name,
CourtNumber:
data.CourtNumber ||
'',
CourtType:
data.CourtType ||
'PADEL',
IndoorOutdoor:
data.IndoorOutdoor ||
'Indoor/Outdoor',
Lights:
data.Lights ||
'Ja',
SurfaceType:
data.SurfaceType ||
'',
BookingURL:
data.BookingURL ||
'',
Active:
true,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'COURTS',
court
);
audit_(
'COURT_CREATED',
'COURTS',
court.CourtID,
court.Name
);
return court;
}
/* ==========================================================
AVAILABILITY
========================================================== */
function getAvailability(
filters
) {
requireUser_();
filters =
filters || {};
let rows =
getRows_(
'COURT_AVAILABILITY'
);
if (
filters.locationId
) {
rows =
rows.filter(
function(row) {
return String(
row.LocationID
) ===
String(
filters.locationId
);
}
);
}
if (
filters.courtId
) {
rows =
rows.filter(
function(row) {
return String(
row.CourtID
) ===
String(
filters.courtId
);
}
);
}
if (
filters.date
) {
rows =
rows.filter(
function(row) {
return String(
row.Date
) ===
String(
filters.date
);
}
);
}
return rows;
}
function addAvailability(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
const court =
findById_(
'COURTS',
'CourtID',
data.CourtID
);
if (!court) {
throw new Error(
'Baan niet gevonden.'
);
}
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
court.LocationID
)
) {
throw new Error(
'Je beheert deze locatie niet.'
);
}
const record = {
AvailabilityID:
id_('AVL'),
LocationID:
court.LocationID,
CourtID:
data.CourtID,
Date:
data.Date,
StartTime:
data.StartTime,
EndTime:
data.EndTime,
Status:
data.Status ||
PADELOS.STATUS.AVAILABLE,
Visibility:
data.Visibility ||
'PUBLIC',
PriorityAccess:
data.PriorityAccess ||
'',
Source:
data.Source ||
'MANUAL',
SourceID:
data.SourceID ||
'',
ExternalBookingID:
data.ExternalBookingID ||
'',
ImportedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'COURT_AVAILABILITY',
record
);
return record;
}
/* ==========================================================
PRICES
========================================================== */
function addCourtPrice(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
const court =
findById_(
'COURTS',
'CourtID',
data.CourtID
);
if (!court) {
throw new Error(
'Baan niet gevonden.'
);
}
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
court.LocationID
)
) {
throw new Error(
'Je beheert deze locatie niet.'
);
}
const record = {
PriceID:
id_('PRC'),
LocationID:
court.LocationID,
CourtID:
data.CourtID,
DayOfWeek:
data.DayOfWeek ||
'*',
StartTime:
data.StartTime ||
'',
EndTime:
data.EndTime ||
'',
Price:
Number(
data.Price || 0
),
Currency:
data.Currency ||
'EUR',
PriceType:
data.PriceType ||
'STANDARD',
MinimumDuration:
Number(
data.MinimumDuration ||
60
),
Active:
true,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'COURT_PRICES',
record
);
return record;
}
/* ==========================================================
BOOKING PRIORITIES
========================================================== */
function getBookingPriorities(
locationId
) {
requireUser_();
const rows =
getRows_(
'BOOKING_PRIORITIES'
);
if (!locationId) {
return rows;
}
return rows.filter(
function(row) {
return String(
row.LocationID
) ===
String(
locationId
);
}
);
}
function createBookingPriority(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
data.LocationID
)
) {
throw new Error(
'Je beheert deze locatie niet.'
);
}
const record = {
PriorityID:
id_('PRI'),
LocationID:
data.LocationID,
Name:
data.Name ||
data.Role,
Role:
data.Role,
PriorityValue:
Number(
data.PriorityValue || 0
),
EarlyVisibilityHours:
Number(
data.EarlyVisibilityHours ||
0
),
EarlyBookingHours:
Number(
data.EarlyBookingHours ||
0
),
Description:
data.Description ||
'',
Active:
true,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'BOOKING_PRIORITIES',
record
);
return record;
}
/* ==========================================================
IMPORT SOURCES
========================================================== */
function getImportSources(
locationId
) {
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
let sources =
getRows_(
'IMPORT_SOURCES'
);
if (
locationId
) {
sources =
sources.filter(
function(source) {
return String(
source.LocationID
) ===
String(
locationId
);
}
);
}
return sources;
}
function createImportSource(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
if (
!data.LocationID
) {
throw new Error(
'Locatie is verplicht.'
);
}
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
data.LocationID
)
) {
throw new Error(
'Je beheert deze locatie niet.'
);
}
const source = {
ImportID:
id_('IMP'),
LocationID:
data.LocationID,
Name:
data.Name ||
'Availability import',
Type:
data.Type ||
'CSV_URL',
URL:
data.URL ||
'',
FileID:
data.FileID ||
'',
SpreadsheetID:
data.SpreadsheetID ||
'',
SheetName:
data.SheetName ||
'',
Format:
data.Format ||
'CSV',
Frequency:
data.Frequency ||
'DAILY',
Active:
true,
LastImport:
'',
LastStatus:
'NEVER',
LastError:
'',
RecordsImported:
0,
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'IMPORT_SOURCES',
source
);
return source;
}
/* ==========================================================
CSV IMPORT
========================================================== */
function runImport(
importId
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION
]);
const source =
findById_(
'IMPORT_SOURCES',
'ImportID',
importId
);
if (!source) {
throw new Error(
'Importbron niet gevonden.'
);
}
if (
user.role ===
PADELOS.ROLES.LOCATION &&
!canManageLocation_(
user.userId,
source.LocationID
)
) {
throw new Error(
'Geen toegang tot deze importbron.'
);
}
const run = {
ImportRunID:
id_('RUN'),
ImportID:
importId,
LocationID:
source.LocationID,
StartedAt:
now_(),
FinishedAt:
'',
Status:
'RUNNING',
RecordsRead:
0,
RecordsImported:
0,
RecordsSkipped:
0,
ErrorMessage:
'',
CreatedAt:
now_()
};
appendObject_(
'IMPORT_RUNS',
run
);
try {
if (
source.Type !==
'CSV_URL'
) {
throw new Error(
'Deze V1 importeert momenteel CSV_URL. ' +
'Google Sheet/API worden in de volgende importlaag toegevoegd.'
);
}
if (!source.URL) {
throw new Error(
'Import URL ontbreekt.'
);
}
const response =
UrlFetchApp.fetch(
source.URL,
{
muteHttpExceptions:
true
}
);
const code =
response.getResponseCode();
if (
code < 200 ||
code >= 300
) {
throw new Error(
'Importbron antwoordde met HTTP ' +
code
);
}
const csv =
response.getContentText();
const rows =
Utilities.parseCsv(
csv
);
if (
rows.length < 2
) {
throw new Error(
'CSV bevat geen gegevens.'
);
}
const headers =
rows[0]
.map(
function(header) {
return String(
header
)
.trim()
.toLowerCase();
}
);
let imported = 0;
let skipped = 0;
for (
let i = 1;
i < rows.length;
i++
) {
run.RecordsRead++;
const row =
rows[i];
const record = {};
headers.forEach(
function(header, index) {
record[header] =
row[index];
}
);
if (
!record.date ||
!record.start ||
!record.end ||
!record.court
) {
skipped++;
continue;
}
let court =
getRows_(
'COURTS'
).find(
function(courtRow) {
return String(
courtRow.LocationID
) ===
String(
source.LocationID
) &&
String(
courtRow.Name
).toLowerCase() ===
String(
record.court
).toLowerCase();
}
);
if (!court) {
court =
createCourt({
LocationID:
source.LocationID,
Name:
record.court,
CourtType:
'PADEL',
IndoorOutdoor:
'Onbekend',
Lights:
'Onbekend'
});
}
addAvailability({
CourtID:
court.CourtID,
Date:
record.date,
StartTime:
record.start,
EndTime:
record.end,
Status:
record.status ||
PADELOS.STATUS.AVAILABLE,
Visibility:
record.visibility ||
'PUBLIC',
PriorityAccess:
record.priorityaccess ||
'',
Source:
'IMPORT',
SourceID:
source.ImportID,
ExternalBookingID:
record.externalbookingid ||
''
});
imported++;
}
run.RecordsImported =
imported;
run.RecordsSkipped =
skipped;
run.Status =
'SUCCESS';
run.FinishedAt =
now_();
updateObject_(
'IMPORT_RUNS',
'ImportRunID',
run.ImportRunID,
run
);
updateImportStatus_(
importId,
'SUCCESS',
'',
imported
);
audit_(
'IMPORT_SUCCESS',
'IMPORT_SOURCES',
importId,
imported +
' records imported'
);
return {
success:
true,
count:
imported,
skipped:
skipped,
importRunId:
run.ImportRunID
};
} catch (
error
) {
run.Status =
'ERROR';
run.FinishedAt =
now_();
run.ErrorMessage =
error.message;
updateObject_(
'IMPORT_RUNS',
'ImportRunID',
run.ImportRunID,
run
);
updateImportStatus_(
importId,
'ERROR',
error.message,
0
);
audit_(
'IMPORT_ERROR',
'IMPORT_SOURCES',
importId,
error.message
);
throw error;
}
}
function updateImportStatus_(
importId,
status,
error,
recordsImported
) {
const source =
findById_(
'IMPORT_SOURCES',
'ImportID',
importId
);
if (!source) {
return;
}
source.LastImport =
now_();
source.LastStatus =
status;
source.LastError =
error || '';
source.RecordsImported =
Number(
recordsImported || 0
);
source.UpdatedAt =
now_();
updateObject_(
'IMPORT_SOURCES',
'ImportID',
importId,
source
);
}
/* ==========================================================
BOOKING PRIORITY CALCULATION
========================================================== */
function calculatePriority_(
role,
locationId
) {
const priorities =
getRows_(
'BOOKING_PRIORITIES'
);
const matching =
priorities
.filter(
function(row) {
return (
String(
row.LocationID
) ===
String(
locationId
) &&
String(
row.Role
) ===
String(
role
) &&
String(
row.Active
).toLowerCase() ===
'true'
);
}
);
if (matching.length) {
return Math.max.apply(
null,
matching.map(
function(row) {
return Number(
row.PriorityValue ||
0
);
}
)
);
}
const defaults = {
ADMINISTRATOR:
100,
TRAINER:
90,
LOCATIEBEHEERDER:
80,
SPELER:
20
};
return defaults[
role
] || 0;
}
/* ==========================================================
BOOKING CONFLICT CHECK
========================================================== */
function hasBookingConflict_(
locationId,
courtId,
date,
startTime,
endTime,
ignoreBookingId
) {
const bookings =
getRows_(
'BOOKINGS'
);
const availability =
getRows_(
'COURT_AVAILABILITY'
);
const overlaps =
function(
existingStart,
existingEnd
) {
return (
String(
startTime
) <
String(
existingEnd
) &&
String(
endTime
) >
String(
existingStart
)
);
};
const bookingConflict =
bookings.some(
function(booking) {
if (
ignoreBookingId &&
String(
booking.BookingID
) ===
String(
ignoreBookingId
)
) {
return false;
}
if (
String(
booking.LocationID
) !==
String(
locationId
)
) {
return false;
}
if (
String(
booking.CourtID
) !==
String(
courtId
)
) {
return false;
}
if (
String(
booking.Date
) !==
String(
date
)
) {
return false;
}
if (
[
'CANCELLED',
'EXPIRED'
].indexOf(
String(
booking.Status
)
) !== -1
) {
return false;
}
return overlaps(
booking.StartTime,
booking.EndTime
);
}
);
if (bookingConflict) {
return true;
}
return false;
}
/* ==========================================================
BOOKINGS
========================================================== */
function createBooking(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.LOCATION,
PADELOS.ROLES.TRAINER,
PADELOS.ROLES.PLAYER
]);
if (
!data.LocationID ||
!data.CourtID ||
!data.Date ||
!data.StartTime ||
!data.EndTime
) {
throw new Error(
'Niet alle boekingsvelden zijn ingevuld.'
);
}
const court =
findById_(
'COURTS',
'CourtID',
data.CourtID
);
if (!court) {
throw new Error(
'Baan bestaat niet.'
);
}
if (
String(
court.LocationID
) !==
String(
data.LocationID
)
) {
throw new Error(
'Baan hoort niet bij deze locatie.'
);
}
const lock =
LockService
.getScriptLock();
lock.waitLock(
10000
);
try {
if (
hasBookingConflict_(
data.LocationID,
data.CourtID,
data.Date,
data.StartTime,
data.EndTime
)
) {
throw new Error(
'Deze baan is al geboekt voor dit tijdstip.'
);
}
const priority =
calculatePriority_(
user.role,
data.LocationID
);
const booking = {
BookingID:
id_('BKG'),
LocationID:
data.LocationID,
CourtID:
data.CourtID,
UserID:
user.userId,
PersonID:
data.PersonID ||
'',
Date:
data.Date,
StartTime:
data.StartTime,
EndTime:
data.EndTime,
Type:
data.Type ||
'LESSON',
Priority:
priority,
Visibility:
data.Visibility ||
'PRIVATE',
Status:
PADELOS.STATUS.REQUESTED,
Source:
data.Source ||
'PADELOS',
ExternalBookingID:
data.ExternalBookingID ||
'',
Notes:
data.Notes ||
'',
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'BOOKINGS',
booking
);
audit_(
'BOOKING_CREATED',
'BOOKINGS',
booking.BookingID,
booking.Type
);
return booking;
} finally {
lock.releaseLock();
}
}
function getBookings() {
const user =
requireUser_();
const rows =
getRows_(
'BOOKINGS'
);
if (
isAdmin_(
user
)
) {
return rows;
}
if (
user.role ===
PADELOS.ROLES.LOCATION
) {
const memberships =
getRows_(
'LOCATION_USERS'
);
const locationIds =
memberships
.filter(
function(row) {
return String(
row.UserID
) ===
String(
user.userId
) &&
String(
row.Active
).toLowerCase() ===
'true';
}
)
.map(
function(row) {
return String(
row.LocationID
);
}
);
return rows.filter(
function(row) {
return locationIds.indexOf(
String(
row.LocationID
)
) !== -1;
}
);
}
return rows.filter(
function(row) {
return String(
row.UserID
) ===
String(
user.userId
);
}
);
}
/* ==========================================================
LESSON SERIES
========================================================== */
function createLessonSeries(
data
) {
const user =
requireRole_([
PADELOS.ROLES.ADMIN,
PADELOS.ROLES.TRAINER
]);
if (
!data.LocationID ||
!data.Name ||
!data.StartDate ||
!data.EndDate ||
!data.StartTime ||
!data.EndTime
) {
throw new Error(
'Vul minimaal naam, locatie, periode en tijden in.'
);
}
const priority =
calculatePriority_(
user.role,
data.LocationID
);
const series = {
SeriesID:
id_('SER'),
TrainerUserID:
user.userId,
LocationID:
data.LocationID,
Name:
data.Name,
Description:
data.Description ||
'',
StartDate:
data.StartDate,
EndDate:
data.EndDate,
DayOfWeek:
data.DayOfWeek ||
'',
StartTime:
data.StartTime,
EndTime:
data.EndTime,
NumberOfLessons:
Number(
data.NumberOfLessons ||
1
),
Players:
Number(
data.Players ||
1
),
Level:
data.Level ||
'',
Goal:
data.Goal ||
'',
Priority:
priority,
Status:
PADELOS.STATUS.REQUESTED,
Notes:
data.Notes ||
'',
CreatedAt:
now_(),
UpdatedAt:
now_()
};
appendObject_(
'LESSON_SERIES',
series
);
audit_(
'LESSON_SERIES_CREATED',
'LESSON_SERIES',
series.SeriesID,
series.Name
);
return series;
}
function getLessonSeries() {
const user =
requireUser_();
const rows =
getRows_(
'LESSON_SERIES'
);
if (
isAdmin_(
user
)
) {
return rows;
}
return rows.filter(
function(row) {
return String(
row.TrainerUserID
) ===
String(
user.userId
);
}
);
}
/* ==========================================================
DASHBOARD
========================================================== */
function getDashboard() {
const user =
requireUser_();
const locations =
getLocations();
const courts =
getCourts();
const bookings =
getBookings();
let lessonSeries =
[];
try {
lessonSeries =
getLessonSeries();
} catch (error) {
console.log(
error.message
);
}
return {
version:
PADELOS.VERSION,
build:
PADELOS.BUILD,
brand:
PADELOS.BRAND,
user:
user,
counts: {
locations:
locations.length,
courts:
courts.length,
bookings:
bookings.length,
lessonSeries:
lessonSeries.length
},
locations:
locations,
bookings:
bookings.slice(
-20
),
lessonSeries:
lessonSeries.slice(
-20
)
};
}
/* ==========================================================
AUDIT
========================================================== */
function audit_(
action,
entity,
entityId,
details
) {
try {
const email =
getCurrentEmail_();
let userId =
'';
const user =
findUserByEmail_(
);
if (user) {
userId =
user.UserID;
}
appendObject_(
'AUDIT_LOG',
{
LogID:
id_('LOG'),
UserEmail:
email,
UserID:
userId,
Action:
action,
Entity:
entity,
EntityID:
entityId,
Details:
details ||
'',
CreatedAt:
now_()
}
);
} catch (
error
) {
console.log(
error.message
);
}
}
/* ==========================================================
ADMIN SETUP
========================================================== */
function setupPadelOS() {
const result =
initializeDatabase_();
const email =
getCurrentEmail_();
if (!email) {
throw new Error(
'Google-accountgegevens zijn niet beschikbaar.'
);
}
const user =
findUserByEmail_(
);
if (
email.toLowerCase() ===
PADELOS.ADMIN_EMAIL.toLowerCase()
) {
if (user) {
user.Role =
PADELOS.ROLES.ADMIN;
user.Active =
true;
user.UpdatedAt =
now_();
updateObject_(
'USERS',
'UserID',
user.UserID,
user
);
}
}
return {
success:
true,
message:
'PadelSchoolOS ' +
PADELOS.BUILD +
' is geïnitialiseerd.',
spreadsheetId:
PADELOS.DATABASE_ID,
administrator:
PADELOS.ADMIN_EMAIL
};
}
/* ==========================================================
DATABASE SETUP / INSPECTION
========================================================== */
function getPadelOSSystemInfo() {
initializeDatabase_();
const ss =
getDatabase_();
return {
name:
PADELOS.NAME,
version:
PADELOS.VERSION,
build:
PADELOS.BUILD,
brand:
PADELOS.BRAND,
databaseId:
ss.getId(),
databaseName:
ss.getName(),
administrator:
PADELOS.ADMIN_EMAIL,
roles:
PADELOS.ROLES,
sheets:
ss.getSheets()
.map(
function(sheet) {
return {
name:
sheet.getName(),
rows:
sheet.getLastRow(),
columns:
sheet.getLastColumn()
};
}
)
};
}