public DataTable GetUserLoginsCountByMonthInRange(int userId, DateTime minDate, DateTime maxDate)
{
string sql =
@"
SELECT
ul.UserId,
-- how often user logged in by year and month
YEAR(ul.UserLoginTime) AS LoginYear,
MONTH(ul.UserLoginTime) AS LoginMonth,
COUNT(ul.UserLoginTime) AS LoginCount
FROM UserLogins ul
WHERE
-- restrict to user
ul.UserId = @UserId
AND
-- restrict to logins within past year
ul.UserLoginTime >= @StartDate AND ul.UserLogintime <= @EndDate
GROUP BY
ul.UserId,
-- group by year and month
YEAR(ul.UserLoginTime),
MONTH(ul.UserLoginTime)
ORDER BY LoginYear DESC, LoginMonth DESC
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddDateTimeInputParam(com, "StartDate", minDate);
DataAccessHelper.AddDateTimeInputParam(com, "EndDate", maxDate);
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
public int InsertEFormsRecord(int userId, int patientId, string eformName, string eformXml, string currentStatus, object eformApptTime, string eformApptPhysician, string userName)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertEFormsRecord");
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddStringInputParam(com, "EformName", eformName);
DataAccessHelper.AddTextInputParam(com, "EformXml", eformXml);
DataAccessHelper.AddStringInputParam(com, "CurrentStatus", currentStatus);
DataAccessHelper.AddDateTimeInputParam(com, "EformApptTime", DataAccessHelper.ToDBDate(eformApptTime));
DataAccessHelper.AddStringInputParam(com, "EformApptPhysician", eformApptPhysician);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", userName);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddStringInputParam(com, "EnteredBy", userName);
DataAccessHelper.AddDateTimeInputParam(com, "EnteredTime", DateTime.Now);
DataAccessHelper.AddIntOutputParam(com, "NewPrimaryKey");
Hashtable outParams = DataAccessHelper.ExecuteScalar(com);
if (outParams["NewPrimaryKey"] != System.DBNull.Value)
{
return((int)outParams["NewPrimaryKey"]);
}
else
{
return(-1);
}
}
public void LogLoginAttempt(string userName, int loginId, string errorMessage, string IP, string userAgent, string browser, string platform, string javascript, string cookies, string screenResolution, string colorDepth)
{
if (errorMessage.Length > 50)
{
errorMessage = errorMessage.Substring(0, 50);
}
if (userAgent.Length > 255)
{
userAgent = userAgent.Substring(0, 255);
}
SqlCommand com = DataAccessHelper.CreateCommand("spInsertUserLoginAttempt");
DataAccessHelper.AddStringInputParam(com, "UserName", userName);
DataAccessHelper.AddDateTimeInputParam(com, "UserLoginAttemptTime", DateTime.Now);
DataAccessHelper.AddIntInputParam(com, "UserLoginId", loginId);
DataAccessHelper.AddStringInputParam(com, "UserErrorMessage", errorMessage);
DataAccessHelper.AddStringInputParam(com, "UserIP", IP);
DataAccessHelper.AddStringInputParam(com, "UserUserAgent", userAgent);
DataAccessHelper.AddStringInputParam(com, "UserBrowser", browser);
DataAccessHelper.AddStringInputParam(com, "UserPlatform", platform);
DataAccessHelper.AddStringInputParam(com, "UserJavascript", javascript);
DataAccessHelper.AddStringInputParam(com, "UserCookies", cookies);
DataAccessHelper.AddStringInputParam(com, "UserScreenResolution", screenResolution);
DataAccessHelper.AddStringInputParam(com, "UserColorDepth", colorDepth);
DataAccessHelper.ExecuteScalar(com);
}
public int InsertUserRecord(string userName, string hashedPassword, string firstName, string lastName, string email, string updatedBy)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertUsersRecord");
DataAccessHelper.AddStringInputParam(com, "UserName", userName);
DataAccessHelper.AddStringInputParam(com, "UserPassword", hashedPassword);
DataAccessHelper.AddStringInputParam(com, "UserFirstName", firstName);
DataAccessHelper.AddStringInputParam(com, "UserLastName", lastName);
DataAccessHelper.AddStringInputParam(com, "UserEmail", email);
DataAccessHelper.AddDateTimeInputParam(com, "EnteredTime", DateTime.Now);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddDateTimeInputParam(com, "DeactivatedTime", null);
DataAccessHelper.AddStringInputParam(com, "EnteredBy", updatedBy);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.AddIntOutputParam(com, "NewPrimaryKey");
Hashtable outParams = DataAccessHelper.ExecuteScalar(com);
if (outParams["NewPrimaryKey"] != System.DBNull.Value)
{
return((int)outParams["NewPrimaryKey"]);
}
else
{
return(-1);
}
}
public DataSet GetAppointments(string apptPhysician, string orderBy, string apptClinicDate, int startRow, int numRows)
{
SqlCommand com = DataAccessHelper.CreateCommand("spListAppointments");
DataAccessHelper.AddStringInputParam(com, "ApptPhysician", apptPhysician);
DataAccessHelper.AddStringInputParam(com, "OrderByField", orderBy);
DataAccessHelper.AddDateTimeInputParam(com, "ApptClinicDate", Convert.ToDateTime(apptClinicDate));
return(DataAccessHelper.GetList(com, startRow, numRows));
}
public DataSet GetBrachyImplants(string beginDate, string endDate)
{
SqlCommand com = DataAccessHelper.CreateCommand("spRptBrachyImplants");
DataAccessHelper.AddDateTimeInputParam(com, "BeginDate", Convert.ToDateTime(beginDate));
DataAccessHelper.AddDateTimeInputParam(com, "EndDate", Convert.ToDateTime(endDate));
return(DataAccessHelper.GetList(com));
}
public DataSet GetEformsClinicList(string datasetSql, string apptPhysician, string apptClinicDate)
{
SqlCommand com = DataAccessHelper.CreateCommand("spListEFormClinic");
DataAccessHelper.AddLongStringInputParam(com, "Dataset", datasetSql);
DataAccessHelper.AddStringInputParam(com, "ApptPhysician", apptPhysician);
//DataAccessHelper.AddStringInputParam(com, "ApptClinicDate", apptClinicDate);
DataAccessHelper.AddDateTimeInputParam(com, "ApptClinicDate", DataAccessHelper.ToDBDate(apptClinicDate));
return(DataAccessHelper.GetRecord(com));
}
public void InsertPatientInstitution(int patientId, int institutionId, SqlTransaction trans)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertPatientInstitutionsRecord", trans);
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddIntInputParam(com, "InstitutionId", institutionId);
DataAccessHelper.AddDateTimeInputParam(com, "EnteredTime", DateTime.Now);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
}
public void InsertEFormsLogRecord(int eformId, string status, string updatedBy, DateTime updatedTime)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertEFormLogRecord");
DataAccessHelper.AddIntInputParam(com, "EFormId", eformId);
DataAccessHelper.AddStringInputParam(com, "Status", status);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", updatedTime);
DataAccessHelper.ExecuteScalar(com);
}
/// <summary>
/// Updates the status of the eform and pushes last status to the eform log
/// </summary>
/// <param name="eformId"></param>
/// <param name="currentStatus"></param>
/// <param name="userName"></param>
/// <returns></returns>
public bool UpdateEFormStatus(int eformId, string currentStatus, string userName)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateEFormsStatus");
DataAccessHelper.AddIntInputParam(com, "EformId", eformId);
DataAccessHelper.AddStringInputParam(com, "CurrentStatus", currentStatus);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", userName);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
public void InsertPatientDisease(int patientId, int diseaseId)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertPatientDiseasesRecord");
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddIntInputParam(com, "DiseaseId", diseaseId);
DataAccessHelper.AddDateTimeInputParam(com, "EnteredTime", DateTime.Now);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
}
public bool RecordUserLogout(int userId)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateUserLogoutTime");
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddDateTimeInputParam(com, "UserLogoutTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
public bool UpdateSeedsAssignedNumber(int seedsAssignedId, string seedNum, string updatedBy)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateSeedsAssignedSeedNum");
DataAccessHelper.AddIntInputParam(com, "SeedsAssignedId", seedsAssignedId);
DataAccessHelper.AddStringInputParam(com, "SeedNum", seedNum);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
/// <summary>
/// Gets a list of efroms emailed on LastNotificationSent Date
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public DataTable GetEformsSurgeryAppointmentsSentByDate(DateTime date)
{
string sql = "SELECT * FROM SurgeryAppointment WHERE MONTH(LastNotificationSent) = MONTH(@DateCompare) AND DAY(LastNotificationSent) = DAY(@DateCompare) AND YEAR(LastNotificationSent) = YEAR(@DateCompare)";
SqlCommand cmd = DataAccessHelper.CreateCommand(sql);
DataAccessHelper.AddDateTimeInputParam(cmd, "DateCompare", date);
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataAccessHelper.GetList(cmd, dt);
return(dt);
}
public bool UpdateForgotPassword(string userEmail, string userNewPassword, string updatedBy)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateUserPasswordEmail");
DataAccessHelper.AddStringInputParam(com, "UserPassword", userNewPassword);
DataAccessHelper.AddStringInputParam(com, "UserEmail", userEmail);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
/*
* public DataSet GetByPassword(string password)
* {
* SqlCommand com = DataAccessHelper.CreateCommand("spGetUsersRecordByPassword");
* DataAccessHelper.AddStringInputParam(com, "userName", userName);
*
* return DataAccessHelper.GetRecord(com);
*
* }
*/
public bool LogPatientView(string userName, int patientId)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertUserPatientViewsRecord");
DataAccessHelper.AddStringInputParam(com, "userName", userName);
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
DataAccessHelper.AddDateTimeInputParam(com, "ViewTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
/* for use of sql strings
* public DataSet GetAdminReport(string sqlString)
* {
* DataSet ds = new DataSet();
* DataTable dt = new DataTable();
* DataAccessHelper.ExecuteSqlSelect(sqlString, dt);
* ds.Tables.Add(dt);
* return ds;
* }
*/
private DataSet AddTimePeriodParams(string dataset, SqlCommand com, string FromDate, string ToDate)
{
if (isDate(FromDate))
{
DataAccessHelper.AddDateTimeInputParam(com, "FromDate", DateTime.Parse(FromDate));
}
if (isDate(ToDate))
{
DataAccessHelper.AddDateTimeInputParam(com, "ToDate", DateTime.Parse(ToDate));
}
return(GetRecord(com, dataset));
}
public DataSet FormGetPatientAppointments(int PatientId, string ApptClinicDate, string ApptPhysician)
{
SqlCommand com = DataAccessHelper.CreateCommand("spFormGetPatientAppointments");
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
//DataAccessHelper.AddStringInputParam(com, "ApptClinicDate", ApptClinicDate);
DataAccessHelper.AddDateTimeInputParam(com, "ApptClinicDate", DataAccessHelper.ToDBDate(ApptClinicDate));
DataAccessHelper.AddStringInputParam(com, "ApptPhysician", ApptPhysician);
DataSet ds = DataAccessHelper.GetList(com);
return(ds);
}
public bool UpdateGroupAccessCode(int groupId, string groupAccessCode)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateGroupAccessCode");
DataAccessHelper.AddIntInputParam(com, "GroupId", groupId);
DataAccessHelper.AddLongStringInputParam(com, "GroupAccessCode", groupAccessCode);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
public DataSet FormGetRecords(int PatientId, string FormName, string FormType, string StopDate)
{
SqlCommand com = DataAccessHelper.CreateCommand("spFormListMedications");
DataAccessHelper.AddIntInputParam(com, "PatientId", PatientId);
DataAccessHelper.AddStringInputParam(com, "FormName", FormName);
DataAccessHelper.AddStringInputParam(com, "FormType", FormType);
//DataAccessHelper.AddStringInputParam(com, "StopDate", StopDate);
DataAccessHelper.AddDateTimeInputParam(com, "StopDate", DataAccessHelper.ToDBDate(StopDate));
DataSet ds = DataAccessHelper.GetList(com);
return(ds);
}
public bool LogDatasetView(int userLoginId, int dataSetId, string purpose)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertUserDatasetViewsRecord");
DataAccessHelper.AddIntInputParam(com, "userLoginId", userLoginId);
DataAccessHelper.AddIntInputParam(com, "dataSetId", dataSetId);
DataAccessHelper.AddStringInputParam(com, "purpose", purpose);
DataAccessHelper.AddDateTimeInputParam(com, "ViewTime", DateTime.Now);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
public bool UpdateUserPassword(int userId, string userNewPassword, string updatedBy)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateUserPassword");
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddStringInputParam(com, "UserPassword", userNewPassword);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.AddIntOutputParam(com, "Valid");
DataAccessHelper.ExecuteScalar(com);
return(true);
}
public int RecordUserLogin(string userName)
{
SqlCommand com = DataAccessHelper.CreateCommand("spInsertUserLoginsRecord");
DataAccessHelper.AddStringInputParam(com, "userName", userName);
DataAccessHelper.AddDateTimeInputParam(com, "UserLoginTime", DateTime.Now);
DataAccessHelper.AddIntOutputParam(com, "NewPrimaryKey");
Hashtable ht = DataAccessHelper.ExecuteScalar(com);
return((int)ht["NewPrimaryKey"]);
}
//public int InsertRole(string role, string roleDescription)
//{
// SqlCommand com = DataAccessHelper.CreateCommand("spInsertRolesRecord");
// DataAccessHelper.AddStringInputParam(com, "Role", role);
// DataAccessHelper.AddLongStringInputParam(com, "RoleDescription", roleDescription);
// DataAccessHelper.AddDateTimeInputParam(com, "EnteredTime", DateTime.Now);
// DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
// DataAccessHelper.AddDateTimeInputParam(com, "DeactivatedTime", null);
// DataAccessHelper.AddIntOutputParam(com, "NewPrimaryKey");
// Hashtable outParams = DataAccessHelper.ExecuteScalar(com);
// if(outParams["NewPrimaryKey"] != System.DBNull.Value)
// {
// return (int)outParams["NewPrimaryKey"];
// }
// else
// {
// return -1;
// }
//}
public bool UpdateRole(int roleId, string role, string roleDescription)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateRolesRecord");
DataAccessHelper.AddIntInputParam(com, "RoleId", roleId);
//DataAccessHelper.AddIntInputParam(com, "RoleId", roleId);
DataAccessHelper.AddStringInputParam(com, "Role", role);
DataAccessHelper.AddLongStringInputParam(com, "RoleDescription", roleDescription);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddDateTimeInputParam(com, "DeactivatedTime", null);
DataAccessHelper.ExecuteScalar(com);
return(true);
}
/// <summary>
/// Gets User Exports by UserId, restricted to Export Type, and which occur after ExportDate, ordered by ExportDate DESC.
/// </summary>
/// <param name="userId"></param>
/// <param name="exportType"></param>
/// <param name="exportDate"></param>
/// <returns></returns>
public DataTable GetRecentUserExports(int userId, string exportType, DateTime exportDate)
{
string sql = GET_USER_EXPORTS_SQl +
@" AND ExportType = @ExportType AND ExportDate >= @ExportDate
ORDER BY ExportDate DESC
";
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddStringInputParam(com, "ExportType", exportType);
DataAccessHelper.AddDateTimeInputParam(com, "ExportDate", exportDate);
DataTable dt = DataAccessHelper.GetRecord(com).Tables[0];
return(dt);
}
/// <summary>
/// For the specific labs, get the closests labs to specified date (by ABS days)
/// </summary>
/// <param name="patientId"></param>
/// <param name="date"></param>
/// <param name="labs"></param>
/// <returns></returns>
public DataTable GetClosestLabs(int patientId, DateTime date, params string[] labs)
{
string sqlFormat =
@"
SELECT
b.*
FROM
(
SELECT
LabTestId,
-- closest # of days to test date
ROW_NUMBER() OVER(Partition BY LabTest ORDER BY ABS(DATEDIFF(DAY, LabDate, @LabDate)) ASC) AS RowNum
FROM LabTests
WHERE
PatientId = @PatientId
AND LabDate BETWEEN @StartLabDate AND @EndLabDate
AND LabTest IN ({0})
) a
INNER JOIN LabTests b
ON a.LabTestId = b.LabTestId
WHERE
a.RowNum = 1
";
string[] labParams = new string[labs.Length];
// build param names
for (int i = 0; i < labs.Length; i++)
{
labParams[i] = "@LabTest_" + i;
}
string sql = string.Format(sqlFormat, string.Join(",", labParams));
SqlCommand com = DataAccessHelper.CreateSqlCommand(sql);
DataAccessHelper.AddIntInputParam(com, "PatientId", patientId);
// date params
DataAccessHelper.AddDateTimeInputParam(com, "LabDate", date);
// add buffer restriction : +/- 30 days
DataAccessHelper.AddDateTimeInputParam(com, "StartLabDate", date.AddDays(-60));
DataAccessHelper.AddDateTimeInputParam(com, "EndLabDate", date.AddDays(60));
// add param values
for (int i = 0; i < labs.Length; i++)
{
DataAccessHelper.AddStringInputParam(com, "LabTest_" + i, labs[i]);
}
DataTable dt = DataAccessHelper.GetList(com).Tables[0];
return(dt);
}
public DataTable GetEformsByEnteredDate(DateTime date)
{
string sql = "SELECT EForms.*, Patients.PtMrn, Patients.PtLastName + ',' + PtFirstName as Name ";
sql += "FROM EForms INNER JOIN Patients ON Patients.PatientId = Eforms.PatientId ";
sql += "WHERE MONTH(EForms.EnteredTime) = MONTH(@DateCompare) AND DAY(EForms.EnteredTime) = DAY(@DateCompare) AND YEAR(EForms.EnteredTime) = YEAR(@DateCompare) ";
sql += "ORDER BY EForms.EFormId ASC";
SqlCommand cmd = DataAccessHelper.CreateCommand(sql);
DataAccessHelper.AddDateTimeInputParam(cmd, "DateCompare", date);
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataAccessHelper.GetList(cmd, dt);
return(dt);
}
public bool UpdateUserRecord(int userId, string userName, string userFirstName, string userLastName, string userEmail, object deactivateDate, string updatedBy)
{
SqlCommand com = DataAccessHelper.CreateCommand("spUpdateUsersRecord");
DataAccessHelper.AddIntInputParam(com, "UserId", userId);
DataAccessHelper.AddStringInputParam(com, "UserName", userName);
DataAccessHelper.AddStringInputParam(com, "UserFirstName", userFirstName);
DataAccessHelper.AddStringInputParam(com, "UserLastName", userLastName);
DataAccessHelper.AddStringInputParam(com, "UserEmail", userEmail);
DataAccessHelper.AddDateTimeInputParam(com, "UpdatedTime", DateTime.Now);
DataAccessHelper.AddStringInputParam(com, "UpdatedBy", updatedBy);
DataAccessHelper.AddDateTimeInputParam(com, "DeactivatedTime", DataAccessHelper.ToDBDate(deactivateDate));
DataAccessHelper.ExecuteScalar(com);
return(true);
}
/// <summary>
/// Updates the Last Notification sent date for a list of Surgeries (via SurgeryAppointmentId)
/// </summary>
/// <param name="listOfApptIds"></param>
/// <param name="date"></param>
public void UpdateLastNotificationDate(IEnumerable <int> listOfApptIds, DateTime date)
{
if (listOfApptIds.Count() > 0)
{
// create update sql format
string updateSQLFormat = "UPDATE SurgeryAppointment SET LastNotificationSent = @NotifyDate WHERE SurgeryAppointmentId IN ({0})";
// create csv list of ids to update
string sqlInStatement = string.Join(",", listOfApptIds.Select(id => id.ToString()).ToArray());
// fill in csv list into sql statement
string doUpdateSQL = string.Format(updateSQLFormat, sqlInStatement);
SqlCommand cmd = DataAccessHelper.CreateCommand(doUpdateSQL);
DataAccessHelper.AddDateTimeInputParam(cmd, "NotifyDate", date);
cmd.CommandType = CommandType.Text;
DataAccessHelper.ExecuteScalar(cmd);
}
}
/// <summary>
/// Gets a list of Surgery Appointments for a particular Date
/// </summary>
/// <param name="date">The ApptSurgeryDate of the appointment</param>
/// <param name="excludeSentItems">exclude items which have already been sent</param>
/// <returns></returns>
public DataTable GetSurgeryAppointments(DateTime date, bool excludeSentItems)
{
string sql = "SELECT * FROM SurgeryAppointment WHERE MONTH(ApptSurgeryDate) = MONTH(@DateCompare) AND DAY(ApptSurgeryDate) = DAY(@DateCompare) AND YEAR(ApptSurgeryDate) = YEAR(@DateCompare)";
if (excludeSentItems)
{
sql += " AND LastNotificationSent IS NULL";
}
SqlCommand cmd = DataAccessHelper.CreateCommand(sql);
DataAccessHelper.AddDateTimeInputParam(cmd, "DateCompare", date);
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataAccessHelper.GetList(cmd, dt);
return(dt);
}