context: using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Reflection;
  using System.Text;
  
  namespace OpenDentBusiness{
  	///<summary></summary>
  	public class ApptComms {
  		//public const string ApptReminderMsgUS = @"Appointment Reminder: [nameF] is scheduled for [apptTime] on [apptDate] at [clinicName]. Call [clinicPhone] if issue. No Reply";
  
  		///<summary></summary>
  		public static List<ApptComm> Refresh(long patNum){
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<List<ApptComm>>(MethodBase.GetCurrentMethod(),patNum);
  			}
  			string command="SELECT * FROM apptcomm WHERE PatNum = "+POut.Long(patNum);
  			return Crud.ApptCommCrud.SelectMany(command);
  		}
  
  		///<summary>Gets one ApptComm from the db.</summary>
  		public static ApptComm GetOne(long apptCommNum){
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb){
  				return Meth.GetObject<ApptComm>(MethodBase.GetCurrentMethod(),apptCommNum);
  			}
  			return Crud.ApptCommCrud.SelectOne(apptCommNum);
  		}
  
  		///<summary>Gets all ApptComm entries from the db.</summary>
  		public static List<ApptComm> GetAll() {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<List<ApptComm>>(MethodBase.GetCurrentMethod());
  			}
  			string command="SELECT * FROM apptcomm";
  			return Crud.ApptCommCrud.SelectMany(command);
  		}
  
  		///<summary>Retrieves all ApptComm entries that are scheduled to be sent between the specified times and the present.</summary>
  		public static List<ApptComm> GetToSend(DateTime dateTimeStart,DateTime dateTimeEnd) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				return Meth.GetObject<List<ApptComm>>(MethodBase.GetCurrentMethod(),dateTimeStart,dateTimeEnd);
  			}
  			string command="SELECT * FROM apptcomm WHERE DateTimeSend BETWEEN "+POut.DateT(dateTimeStart)+" AND "+POut.DateT(dateTimeEnd);
  			return Crud.ApptCommCrud.SelectMany(command);
  		}
  
  		///<summary></summary>
  		public static long Insert(ApptComm apptComm){
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb){
  				apptComm.ApptCommNum=Meth.GetLong(MethodBase.GetCurrentMethod(),apptComm);
  				return apptComm.ApptCommNum;
  			}
  			return Crud.ApptCommCrud.Insert(apptComm);
  		}
  
  		///<summary></summary>
  		public static void Delete(long apptCommNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),apptCommNum);
  				return;
  			}
  			Crud.ApptCommCrud.Delete(apptCommNum);
  		}
  
  		///<summary></summary>
  		public static void TruncateAll() {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod());
  				return;
  			}
  			//Truncate also resets autoincrement, this is ok for the apptcomm table.
  			string command="TRUNCATE TABLE apptcomm";
  			Db.NonQ(command);
  		}
  
  		///<summary></summary>
  		public static void DeleteForAppt(long apptNum) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),apptNum);
  				return;
  			}
  			string command="DELETE FROM apptcomm WHERE ApptNum="+POut.Long(apptNum);
  			Db.NonQ(command);
  		}
  
  		///<summary></summary>
  		public static void DeleteForAppts(List<long> apptNums) {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod(),apptNums);
  				return;
  			}
  			if(apptNums==null || apptNums.Count<1) {
  				return;
  			}
  			string command="DELETE FROM apptcomm WHERE ApptNum IN("+String.Join(",",apptNums)+")";
  			Db.NonQ(command);
  		}
  
  		///<summary>Creates two ApptComm items, one to send using dayInterval, and one to send using hourInterval.</summary>
  		public static void InsertForAppt(Appointment appt,double dayInterval,double hourInterval,DateTime automationBeginPref,DateTime automationEndPref) {
  			if(appt.AptStatus!=ApptStatus.Scheduled && appt.AptStatus!=ApptStatus.ASAP) {
  				return;//Do nothing unless it's scheduled or ASAP.
  			}
  			ApptComm apptComm;
  			DateTime daySend=appt.AptDateTime.Subtract(TimeSpan.FromDays(dayInterval));
  			bool isAutomationWindowDisabled=automationBeginPref.TimeOfDay==automationEndPref.TimeOfDay;
  			//This prevents a UE while pre-inserting new appointments and prevents adding reminder if the interval can't be reached.
  			if(dayInterval > 0 && appt.AptNum!=0 && daySend > DateTime.Now) {
  				apptComm=new ApptComm();
  				apptComm.ApptNum=appt.AptNum;
  				apptComm.ApptCommType=IntervalType.Daily;
  				DateTime automationBegin=daySend.Date.AddSeconds(automationBeginPref.TimeOfDay.TotalSeconds);
  				//automationEnd is the last moment of the day we are allowed to send.(date of automationEnd, time of automationEndPref - 30 minutes.)
  				DateTime automationEnd=daySend.Date.AddSeconds(automationEndPref.TimeOfDay.TotalSeconds).AddMinutes(-30);
  				if(daySend.TimeOfDay<automationBegin.TimeOfDay && !isAutomationWindowDisabled) {
  					//Apptcomm scheduled before the day starts, Send it at the end of the previous day.
  					apptComm.DateTimeSend=automationEnd.AddDays(-1);
  				}
  				else if(daySend.TimeOfDay>automationEnd.TimeOfDay && !isAutomationWindowDisabled) {
  					//Apptcomm scheduled after the day ends, Send it at the end of the current day.
  					apptComm.DateTimeSend=automationEnd;
  				}
  				else { 
  					apptComm.DateTimeSend=daySend;//Setting the ApptComm reminder to be sent dayInterval days before the appt.
  				}
  				ApptComms.Insert(apptComm);
  			}
  			DateTime hourSend=appt.AptDateTime.Subtract(TimeSpan.FromHours(hourInterval));
  			if(hourInterval > 0 && appt.AptNum!=0 && hourSend > DateTime.Now) {//This prevents a UE while pre-inserting new appointments.
  				apptComm=new ApptComm();
  				apptComm.ApptNum=appt.AptNum;
  				apptComm.ApptCommType=IntervalType.Hourly;
  				DateTime automationBegin=hourSend.Date.AddSeconds(automationBeginPref.TimeOfDay.TotalSeconds);
  				//automationEnd is the last moment of the day we are allowed to send.(date of automationEnd, time of automationEndPref - 30 minutes.)
  				DateTime automationEnd=hourSend.Date.AddSeconds(automationEndPref.TimeOfDay.TotalSeconds).AddMinutes(-30);
  				if(hourSend.TimeOfDay<automationBegin.TimeOfDay && !isAutomationWindowDisabled) {
  					//Apptcomm scheduled before the day starts, Send it at the end of the previous day.
  					apptComm.DateTimeSend=automationEnd.AddDays(-1);
  				}
  				else if(hourSend.TimeOfDay>automationEnd.TimeOfDay && !isAutomationWindowDisabled) {
  					//Apptcomm scheduled after the day ends, Send it at the end of the current day.
  					apptComm.DateTimeSend=automationEnd;
  				}
  				else { 
  					apptComm.DateTimeSend=hourSend;//Setting the ApptComm reminder to be sent HourInterval hours before the appt.
  				}
  				ApptComms.Insert(apptComm);
  			}
  		}
  
  		///<summary>"Resets" the ApptComm table. Truncates all existing ApptComms and inserts appointment reminders for all future appointments.  
  		///Used when automated appt reminder settings are changed so we can make sure all future appointments have appropriate reminders.</summary>
  		public static void InsertForFutureAppts() {
  			if(RemotingClient.RemotingRole==RemotingRole.ClientWeb) {
  				Meth.GetVoid(MethodBase.GetCurrentMethod());
  				return;
  			}
  			//Doing the remotingrole check here to save a lot of traffic over the wire in the Delete and Insert statements below.
  			//DO NOT USE METHODS THAT USE THE CACHE AFTER THIS POINT
  			List<Appointment> listFutureAppts=Appointments.GetFutureSchedApts();
  			double dayInterval=PrefC.GetDouble(PrefName.ApptReminderDayInterval);
  			double hourInterval=PrefC.GetDouble(PrefName.ApptReminderHourInterval);
  			DateTime automationBeginPref=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeStart);
  			DateTime automationEndPref=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeEnd);
  			TruncateAll();
  			foreach(Appointment appt in listFutureAppts) {
  				//Get the DateTime automation end
  				InsertForAppt(appt,dayInterval,hourInterval,automationBeginPref,automationEndPref);
  			}
  		}
  
  		///<summary>First deletes then re-inserts ApptComm items for an appointment that has been moved.</summary>
  		public static void UpdateForAppt(Appointment appt) {
  			DeleteForAppt(appt.AptNum);
  			if(appt.AptDateTime > DateTime.Now) {//Prevents UE's when updating pre-inserted appointments with no scheduled time as well as appointments updated that were in the past.
  				double dayInterval=PrefC.GetDouble(PrefName.ApptReminderDayInterval);
  				double hourInterval=PrefC.GetDouble(PrefName.ApptReminderHourInterval);
  				DateTime automationBeginPref=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeStart);
  				DateTime automationEndPref=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeEnd);
  				InsertForAppt(appt,dayInterval,hourInterval,automationBeginPref,automationEndPref);
  			}
  		}
  
  		///<summary>Send Appointment reminders for all ApptComm items.</summary>
  		public static string SendReminders() {
  			string errorText="";
  			DateTime automationStart=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeStart);
  			DateTime automationEnd=PrefC.GetDateT(PrefName.AutomaticCommunicationTimeEnd);
  			if(automationStart.TimeOfDay!=automationEnd.TimeOfDay && (DateTime.Now.TimeOfDay<automationStart.TimeOfDay || DateTime.Now.TimeOfDay>automationEnd.TimeOfDay)) {
  				//Not currently within send window and automation start/end is enabled.
  				return "";
  			}
  			DateTime dateTimeAutomationStart=new DateTime();
  			DateTime dateTimeAutomationEnd=new DateTime();
  			if(automationStart.TimeOfDay!=automationEnd.TimeOfDay) {
  				//Get all entries for today, from automation start to automation end.
  				dateTimeAutomationStart=new DateTime(DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day
  					,automationStart.Hour,automationStart.Minute,automationStart.Second);
  				dateTimeAutomationEnd=new DateTime(DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day
  					,automationEnd.Hour,automationEnd.Minute,automationEnd.Second);
  			}
  			else {
  				//Get all entries for today, from midnight to midnight.
  				dateTimeAutomationStart=new DateTime(DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day,0,0,0);
  				dateTimeAutomationEnd=dateTimeAutomationStart.AddDays(1);
  			}
  			List<ApptComm> listApptComms=GetToSend(dateTimeAutomationStart,dateTimeAutomationEnd);
  			foreach(ApptComm apptComm in listApptComms) {//Foreach loops are faster than For loops.  All reminders in the list are for appointments that haven't come yet.
  				if(apptComm.ApptCommType==IntervalType.Daily && (apptComm.DateTimeSend-DateTime.Now).TotalDays > 0) {//Send if Now is <= 0 days prior to the send day.
  					continue;//It's not currently enough days prior to the appointment to send a reminder.
  				}
  				if(apptComm.ApptCommType==IntervalType.Hourly && (apptComm.DateTimeSend-DateTime.Now).TotalHours > 0) {//Send if Now <= 0 hours prior to the send hour.
  					continue;//It's not the correct number of hours prior to the appointment to send a reminder.
  				}
  				//Check for entries that should have already been sent.  Our send interval is set at 10 minutes, so 30 minutes leeway was deemed enough to 
  				//catch edge cases (2 attempted sends on average).  If the DateTime the apptComm was supposed to be sent is older than 30 minutes ago, just delete it.
  				if(apptComm.DateTimeSend < (DateTime.Now-new TimeSpan(0,30,0))) {//Only entries that were skipped due to the listener being down should be deleted here.
  					Delete(apptComm.ApptCommNum);
  					continue;
  				}
  				bool sendAll=PrefC.GetBool(PrefName.ApptReminderSendAll);
  				string[] arraySendPriorities=PrefC.GetString(PrefName.ApptReminderSendOrder).Split(',');
  				Appointment appt=Appointments.GetOneApt(apptComm.ApptNum);
  				Family family=Patients.GetFamily(appt.PatNum);
  				Patient pat=family.GetPatient(appt.PatNum);
  				if(sendAll) {
  					//Attempt to send both email and text reminder no matter what.
  					//First we'll attempt to send via email.
  					string emailError=SendEmail(pat,family,appt,apptComm.ApptCommType);
  					if(emailError=="") {
  
gt: 						Commlog comm=new Commlog();