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 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); } }