private string returnTPDateSQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Select Patient.Account, Patient.LastName || ', ' || Patient.FirstName as Patient";
SQL = SQL + " , strftime('%m/%d/%Y',max(TP.Date)) as TP_Date, Resource.FirstName || ' ' || Resource.LastName as Resource, TP.Location";
SQL = SQL + " From Patient";
SQL = SQL + " Join TP on TP.Patient_Account = Patient.Account";
SQL = SQL + " left Join Resource on TP.Resource_ID = Resource.ID";
if (cmbRange.Text == "Greater Than Today")
{
SQL = SQL + " Where TP.Date >= date('now')";
}
else if (cmbRange.Text == "Monthly")
{
DateTime dtDate = dtMonth.Value;
SQL = SQL + " Where strftime('%m',TP.Date)='" + objFormat.formatMonth(dtDate.Month) + "'";
SQL = SQL + " and strftime('%Y',TP.Date)='" + dtDate.Year.ToString() + "'";
}
else
{
SQL = SQL + " Where TP.Date >= " + objFormat.formatDate(dtFrom.Text);
SQL = SQL + " and TP.Date <= " + objFormat.formatDate(dtTo.Text);
}
SQL = SQL + " Group By Patient.Account, Patient.FirstName, Patient.LastName";
SQL = SQL + ", Resource.FirstName, Resource.LastName Order by Resource.LastName,TP.Date,TP.Location, Patient.LastName";
return(SQL);
}
private string returnWaitListSQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Select Patient_Account as Account, Patient.LastName || ', ' || Patient.FirstName as Patient";
SQL = SQL + " , strftime('%m/%d/%Y',WaitList.StartDate) as StartDate, strftime('%m/%d/%Y',WaitList.EndDate) as EndDate, Resource.FirstName || ' ' || Resource.LastName as Resource";
SQL = SQL + " From WaitList";
SQL = SQL + " Join Patient on WaitList.Patient_Account = Patient.Account";
SQL = SQL + " Join Resource on WaitList.Resource_ID = Resource.ID";
if (cmbRange.Text == "Greater Than Today")
{
SQL = SQL + " Where WaitList.EndDate >= date('now')";
}
else if (cmbRange.Text == "Monthly")
{
DateTime dtDate = dtMonth.Value;
SQL = SQL + " Where strftime('%m',WaitList.EndDate)='" + objFormat.formatMonth(dtDate.Month) + "'";
SQL = SQL + " and strftime('%Y',WaitList.EndDate)='" + dtDate.Year.ToString() + "'";
}
else
{
SQL = SQL + " Where WaitList.StartDate >= " + objFormat.formatDate(dtFrom.Text);
SQL = SQL + " and WaitList.EndDate <= " + objFormat.formatDate(dtTo.Text);
}
SQL = SQL + " Order by Patient.LastName";
return(SQL);
}
private string addPerson_SQL()
{
string strFirst = txtFirst.Text.Replace("'", "''");
string strLast = txtLast.Text.Replace("'", "''");
clsFormat objFormat = new clsFormat();
string SQL = "Insert into Patient(Account,FirstName,LastName,Resource_ID, DOB, Phone)";
SQL = SQL + " Select " + txtAccount.Text + ",'" + strFirst + "','" + strLast + "',";
SQL = SQL + " Resource.ID";
SQL = SQL + " ,'" + dtDOB.Value.ToString("yyyy-MM-dd") + "','" + txtPhone.Text + "'";
SQL = SQL + " From Resource";
SQL = SQL + " Where FirstName || ' ' || LastName = '" + cmbResource.Text + "'";
return(SQL);
}
private string addDate_SQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "";
if (TP_ID == 0)
{
SQL = "Insert into TP(Patient_Account,Date, Resource_ID, Location)";
SQL = SQL + " Values (" + strAccount + "," + objFormat.formatDate(dtDate.Text);
if (cmbCounselor.SelectedIndex >= 0)
{
SQL = SQL + ",(Select ID From Resource Where FirstName || ' ' || LastName ='" + cmbCounselor.Text + "')";
}
else
{
SQL = SQL + ",null";
}
if (cmbLocation.SelectedIndex >= 0)
{
SQL = SQL + ",'" + cmbLocation.Text + "')";
}
else
{
SQL = SQL + ",null)";
}
}
else
{
SQL = "Update TP";
SQL = SQL + " Set Date = " + objFormat.formatDate(dtDate.Text);
if (cmbLocation.SelectedIndex >= 0)
{
SQL = SQL + ", Location = '" + cmbLocation.Text + "'";
}
if (cmbCounselor.SelectedIndex >= 0)
{
SQL = SQL + " , Resource_ID=(Select ID From Resource Where FirstName || ' ' || LastName ='" + cmbCounselor.Text + "')";
}
SQL = SQL + " Where TP.ID = " + TP_ID.ToString();
}
return(SQL);
}
private void deleteDate()
{
clsFormat objFormat = new clsFormat();
string strDelete = objFormat.formatDate(dtDate.Text);
string SQL = returnDeleteDate_SQL(strDelete);
clsDatabase objDatabase = new clsDatabase(strDB);
bool blPass = objDatabase.ExecuteNonQuery(SQL);
if (blPass != true)
{
MessageBox.Show("The date was not deleted, if problem persists please contact IT.");
}
else
{
loadTP_Dates(strAccount);
}
}
