Formel für Terminüberschneidung Datum/Uhrzeit

[Rob Green]

Quelle:
http://www.martinscott.com/DesignLibrary.nsf/scategory/844BD6BD3F7E00ED85256C08001DF281?opendocument


Source: Jamie Magee  , 08/18/1998
Implementation: Modify code Server:  
Code Type: Formula Required Client:  
 
 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;