so..eknori..mir klingelte was in den Ohren, daß ich vor Jahren meiner Kollegin eine Formel für Terminüberschneidungen freudejauchzend vor die Nase gehalten hatte (sie mußte so ne kleine Autoreservierungs DB schreibseln...)
und siehe da...ich habs beim guten alten Martin Scott gefunden:
Description
Given lists of StartTimes, EndTimes, Persons, and EventNames, determines which Persons/Events intersect a proposed event with StartTimeProposed, EndTimeProposed.
Comments
at the end of the code, if TextSet is "", then there is no intersection between the existing event times and the proposed event time.
Code
REM "======================================================";
REM "Date Range Intersection detection.";
REM "Jamie Magee 8/18/98";
REM "Martin Scott Consulting LLC, American Management Systems, Inc.";
REM "Given a list of StartTimes, list of EndTimes, Persons, and EventNames ";
REM "this formula determines which Persons/Events intersect a proposed event ";
REM "with StartTimeProposed, EndTimeProposed.";
REM "======================================================";
REM "any unlikely-to-occur string, used for internal data separation";
sep := "~!@";
REM "interface with field data values on form";
REM "...Who and When (day)";
t_ProposedPersons := Invitees+sep+@Text(@Date(MeetingDate));
REM "...When (time)";
t_StartTimeProposed := @TextToTime( @Left(@Text(MeetingTime); " -"));
t_EndTimeProposed := @TextToTime( @Right(@Text(MeetingTime); "- "));
@If(t_StartTimeProposed !="" & t_EndTimeProposed !="" & @Trim(Invitees) !=""; ""; @Return(@Success));
REM "...lookup location";
t_EventView := "MeetingsByPerson-ConflictChecking";
t_EventDataColumnNum := 2;
REM "formatting - keep in mind that only 256 characters show up in an @Prompt message";
EventNameMaxDisplayLength := 60;
REM "============== DO NOT CHANGE CODE BELOW =============";
t_EventData := @DbLookup("":"NoCache"; ""; t_EventView; t_ProposedPersons; t_EventDataColumnNum);
@If(@IsError(t_EventData); @Do(@Set("ConflictMessage"; ""); @Return(@Prompt([OK]; "Conflict checking"; "No conflicts found."))); "");
t_StartTimes := @TextToTime(@Word(t_EventData; sep; 1));
t_EndTimes := @TextToTime(@Word(t_EventData; sep; 2));
t_EventNames := @Word(t_EventData; sep; 3);
REM "used later to exclude the current doc from the list of conflicts...";
t_NoteIDs := @Word(t_EventData; sep; 4);
REM "lookup the list of persons again, so we can accurately pair them up with their respective meetings on that day.";
t_Persons := @Word(@DbLookup("":"NoCache"; ""; t_EventView; t_ProposedPersons; 1); sep; 1);
REM "used for text message at end of this routine. Removing leading zeros saves output space.";
t_EventTimes := @Text(t_StartTimes; "T1" ) + " - " + @Text(t_EndTimes; "T1" );
@Set("t_EventTimes"; @ReplaceSubstring(t_EventTimes; "01:":"02:":"03:":"04:":"05:":"06:":"07:":"08:":"09:"; "1:":"2:":"3:":"4:":"5:":"6:":"7:":"8:":"9:"));
REM "calculate the differences between start and end times";
sas := t_StartTimes - t_StartTimeProposed ;
sbe := t_EndTimeProposed - t_StartTimes ;
eas := t_EndTimes - t_StartTimeProposed ;
ebe := t_EndTimeProposed - t_EndTimes ;
sbs := t_StartTimeProposed - t_StartTimes ;
eae := t_EndTimes - t_EndTimeProposed ;
sas_1 := (@Abs(sas)-sas)+1;
sbe2 := @TextToNumber(@Replace(@Text(sbe); "0"; "-1"));
sbe_1 := (@Abs(sbe2)-sbe2)+1;
eas2 := @TextToNumber(@Replace(@Text(eas); "0"; "-1"));
eas_1 := (@Abs(eas2)-eas2)+1;
ebe_1 := (@Abs(ebe)-ebe)+1;
sbs_1 := (@Abs(sbs)-sbs)+1;
eae_1 := (@Abs(eae)-eae)+1;
test1 := @Replace(@Text(SAS_1 * SBE_1); "1"; "<<<");
test2 := @Replace(@Text(EAS_1 * EBE_1); "1"; "<<<");
test3 := @Replace(@Text(SBS_1 * EAE_1); "1"; "<<<");
REM "match up known conflict row numbers with event info for an informative message detailing the conflicts.";
Set1 := @Trim(@Left(t_Persons + sep + t_EventTimes + sep + t_EventNames + test1; "<<<"));
Set2 := @Trim(@Left(t_Persons + sep + t_EventTimes + sep + t_EventNames + test2; "<<<"));
Set3 := @Trim(@Left(t_Persons + sep + t_EventTimes + sep + t_EventNames + test3; "<<<"));
TextSet := @Trim(@Unique(Set1 : Set2 : Set3) );
REM "if this doc is already saved, make sure to exclude it from the list of found conflicts, else it will conflict with itself.";
@If(@IsNewDoc; ""; @Set("TextSet"; @Trim(@Replace(TextSet ; @Right(t_NoteIDs + t_Persons + sep + t_EventTimes + sep + t_EventNames; @NoteID); ""))) );
message := @If(TextSet = ""; @Return(@Prompt([OK]; "Conflict checking"; "No conflicts found.")); @Implode(" " + @Left(@Word(TextSet; sep; 1); 1) + ". " + @Right(@Word(TextSet; sep; 1); " ") + " - " + @Word(TextSet; sep; 2) + ": " + @Left(@Left(@Word(TextSet; sep; 3); "|||"); EventNameMaxDisplayLength); @NewLine));
FIELD ConflictMessage := message;
Quelle:
http://www.martinscott.com/DesignLibrary.nsf/scategory/844BD6BD3F7E00ED85256C08001DF281?opendocument
