private void fillGridAdmin()
{
string SQL = AdminSQL();
clsDB objDB = new clsDB(resPinnacle.liveDB);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
clsGridUtils objGrid = new clsGridUtils();
objGrid.fillGrid(objReader, 0, ref grdAdministrative, this.CreateGraphics());
}
private void makeInactive(string strConsumer, string strID)
{
clsDB objDB = new clsDB(strDB);
bool blPass = objDB.makeInActive(strID);
if (blPass == true)
{
MessageBox.Show(strConsumer + " was successfully made Inactive.");
LoadControl();
}
}
private void setConsumerID()
{
string SQL = "SELECT max(ID) as ID FROM Consumer";
clsDB objDB = new clsDB(resPinnacle.liveDB);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
ID = objReader[0].ToString();
}
}
private void loadConsumerList()
{
clsDB objDB = new clsDB(strDB);
string SQL = allConsumers_SQL(StaffID);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
txtConsumer.AutoCompleteCustomSource.Add(objReader["Consumer"].ToString());
}
}
private void loadSelectedConsumer()
{
string strSQL = selectedConsumer_SQL();
clsDB objDB = new clsDB(strDB);
clsGridUtils objGrid = new clsGridUtils();
SQLiteDataReader objReader = objDB.returnDataReader(strSQL);
ConsumerID = objReader[0].ToString();
Consumer = objReader[2].ToString();
objGrid.fillGrid(objReader, 3, ref grdConsumers, this.CreateGraphics());
}
private void updateMeetingID(string strValue)
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Select ID FROM Meeting Where Description = '" + strValue + "'";
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
MeetingID = objReader["ID"].ToString();
}
}
private void addMeeting()
{
//checkNewJob();
string strSQL = addMeetingSQL();
clsDB objDB = new clsDB(strDB);
bool blPass = objDB.executeNonQuery(strSQL);
if (blPass == false)
{
MessageBox.Show("Your review was not entered into the database, please consult IT if problem persists.");
}
}
public void EditReview(string strReviewID)
{
clsDB objDB = new clsDB(strDB);
ReviewID = strReviewID;
string SQL = "Select Review.ID as ReviewID,Consumer.ID as ConsumerID, strftime('%m/%d/%Y',Date) as Date, DesiredOutcome, Barriers, Note, Meeting.Description as Meeting, Meeting.ID as MeetingID";
SQL = SQL + " , Job_ID, Job.Title as Job, Job.Description as JobDescription, Job.Employer, Job.Address, Job.City, Job.Zip, strftime('%m/%d/%Y',Job.PlacementDate) as PlacementDate, Staff_ID";
SQL = SQL + " , Review.TimeIn, Review.TimeOut, Consumer.FirstName || ' ' || Consumer.LastName as Consumer";
SQL = SQL + " From Review";
SQL = SQL + " Join Meeting on Review.Meeting_ID = Meeting.ID";
SQL = SQL + " Left Join Job on Review.Job_ID = Job.ID";
SQL = SQL + " Join Consumer on Review.Consumer_ID=Consumer.ID";
SQL = SQL + " WHERE Review.Id =" + strReviewID;
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
clsFormat objFormat = new clsFormat();
while (objReader.Read())
{
ReviewID = objReader["ReviewID"].ToString();
StaffID = objReader["Staff_ID"].ToString();
JobID = objReader["Job_ID"].ToString();
MeetingID = objReader["MeetingID"].ToString();
ID = objReader["ConsumerID"].ToString();
dtDate.Text = objReader["Date"].ToString();
txtOutcome.Text = objReader["DesiredOutcome"].ToString();
txtBarriers.Text = objReader["Barriers"].ToString();
txtDetails.Text = objReader["Note"].ToString();
cmbMeeting.Text = objReader["Meeting"].ToString();
txtJob.Text = objReader["Job"].ToString();
txtDescription.Text = objReader["JobDescription"].ToString();
txtEmployer.Text = objReader["Employer"].ToString();
txtAddress.Text = objReader["Address"].ToString();
txtCity.Text = objReader["City"].ToString();
txtZip.Text = objReader["Zip"].ToString();
txtPlacement.Text = objReader["PlacementDate"].ToString();
DateTime dtTime = objReader.GetDateTime(objReader.GetOrdinal("TimeIn"));
dtTimeIn.Text = objFormat.convertTime_12hr(dtTime);
dtTime = objReader.GetDateTime(objReader.GetOrdinal("TimeOut"));
dtTimeOut.Text = objFormat.convertTime_12hr(dtTime);
txtConsumer.Text = objReader["Consumer"].ToString();
butEnter.Text = "Update";
}
}
private void returnHours()
{
clsDB objDB = new clsDB(strDB);
string SQL = returnHours_SQL();
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
if (objReader[0] != null)
{
lblHours.Text = "Hours: " + objReader[0].ToString();
}
}
}
private void fillGrdConsumers()
{
clsDB objDB = new clsDB(strDB);
clsGridUtils objGrid = new clsGridUtils();
//SQLiteDataReader objReader = objDB.returnConsumers("Consumers", cmbStaff.Text, blActiveOnly);
string SQL = returnAllActivity_SQL();
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
objGrid.fillGrid(objReader, 1, ref grdConsumers, this.CreateGraphics());
objReader.Close();
returnHours();
}
private void addNewVoucher()
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Insert Into Voucher(Start,End,Consumer_ID)";
clsFormat objFormat = new clsFormat();
SQL = SQL + " Values('" + objFormat.formatDate(dtVoucherStart.Text) + "','" + objFormat.formatDate(dtVoucherEnd.Text) + "','" + ID + "')";
bool blPass = objDB.executeNonQuery(SQL);
fillGridVoucher();
}
private void fillGridVoucher()
{
string SQL = "Select id";
SQL = SQL + ", strftime('%m/%d/%Y',Start) as Start";
SQL = SQL + ", strftime('%m/%d/%Y',End) as End ";
SQL = SQL + " From Voucher";
SQL = SQL + " Where Consumer_ID = " + ID;
clsDB objDB = new clsDB(resPinnacle.liveDB);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
clsGridUtils objGrid = new clsGridUtils();
objGrid.fillGrid(objReader, 1, ref grdVouchers, this.CreateGraphics());
}
private void DeleteConsumer(string strConsumer, string strID)
{
clsDB objDB = new clsDB(strDB);
DialogResult objResult = MessageBox.Show("Are you sure you want to delete " + strConsumer + " from the database? This permanently deletes the user from the database.", "Delete - " + strConsumer, MessageBoxButtons.YesNo);
if (objResult == DialogResult.Yes)
{
string SQL = "Delete from Consumer where Consumer.ID = " + strID;
bool blPass = objDB.executeNonQuery(SQL);
if (blPass == true)
{
MessageBox.Show(strConsumer + " was successfully deleted from the system");
LoadControl();
}
}
}
private void fillCombobox(ComboBox objCombo, string strTable)
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
SQLiteDataReader objReader = objDB.returnComboBox(strTable);
while (objReader.Read())
{
objCombo.Items.Add(objReader[0]);
}
objCombo.SelectedIndex = -1;
objReader.Close();
// adding empty space
objCombo.Items.Add("");
}
private void updateMeeting()
{
clsDB objDB = new clsDB(strDB);
string SQL = updateMeetingSQL();
bool blPass = objDB.executeNonQuery(SQL);
if (blPass == true)
{
MessageBox.Show(txtConsumer.Text + "'s review was successfully updated.");
}
else
{
MessageBox.Show(txtConsumer.Text + "'s review was NOT updated. Please try again if problem persists contact IT.");
}
}
private void getAssignedVRC()
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Select VRC.FirstName || ' ' || VRC.LastName as VRC";
SQL = SQL + " From VRC";
SQL = SQL + " Join Consumer on VRC.ID = Consumer.VRC_ID";
SQL = SQL + " Where Consumer.ID = " + ID;
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
cmbCounselor.SelectedIndex = cmbCounselor.FindString(objReader[0].ToString());
}
}
private void LoadControl()
{
clsDB objDB = new clsDB(strDB);
// Loading combobox cmbStaff
cmbStaff.SelectedIndex = -1;
SQLiteDataReader objReader = objDB.returnComboBox("Staff");
cmbStaff.Items.Add("All Staff");
while (objReader.Read())
{
cmbStaff.Items.Add(objReader["Staff"]);
}
cmbStaff.SelectedIndex = 0;
objReader.Close();
Status = "All";
}
private void DeleteReview(string strConsumer, string strReviewID)
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Delete from Review where ID = " + strReviewID;
bool blPass = objDB.executeNonQuery(SQL);
if (blPass == false)
{
MessageBox.Show("Your review was not deleted. If problem persists please contact IT.");
}
SQL = selectedConsumer_SQL();
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
clsGridUtils objGrid = new clsGridUtils();
objGrid.fillGrid(objReader, 3, ref grdConsumers, this.CreateGraphics());
}
private void loadConsumerDetail(string strConsumer, string ID)
{
clsDB objDB = new clsDB(strDB);
string SQL = consumerDetail_SQL(strConsumer, ID);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
//PassConsumerID(strConsumer);
while (objReader.Read())
{
txtFunding.Text = objReader["Funding"].ToString();
txtDisability.Text = objReader["Disability"].ToString();
grpDetails.Visible = true;
EnableButton("butMonthlyReport");
}
}
private void fillComboBox()
{
clsDB objDB = new clsDB(strDB);
string SQL = "Select Description From Meeting";
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
int j = 0;
while (objReader.Read())
{
cmbMeeting.Items.Add(objReader["Description"].ToString());
}
cmbMeeting.SelectedIndex = 0;
}
private void checkNewJob()
{
if ((txtEmployer.Text != Employer || txtJob.Text != Job) && txtJob.Text != "")
{
clsDB objDB = new clsDB(strDB);
clsFormat objFormat = new clsFormat();
string strInsert = "Insert Into Job(Title, Description, Employer, Address,City, Zip,PlacementDate,Consumer_ID)";
string strValues = " Values('" + txtJob.Text + "','" + objFormat.removeApostrophe(txtDescription.Text) + "','" + objFormat.removeApostrophe(txtEmployer.Text) + "','" + txtAddress.Text + "'";
strValues = strValues + ",'" + txtCity.Text + "','" + objFormat.formatZip(txtZip.Text) + "'," + objFormat.formatDate(txtPlacement.Text) + "," + ID + ")";
objDB.assignJob(strInsert + strValues);
// logic to email Kristen.
clsEmail objEmail = new clsEmail();
objEmail.MailInfo("*****@*****.**", txtConsumer.Text);
}
}
private bool removeStaff_DB()
{
string SQL = "";
clsDB objDB = new clsDB(resPinnacle.liveDB);
bool blPass = true; // if value is not updated it will return false
for (int j = 0; j < deleteStaff.Count; j++)
{
SQL = "Delete from ConsumerStaff Where ConsumerStaff.ID = (";
SQL = SQL + " Select ConsumerStaff.ID from ConsumerStaff";
SQL = SQL + " Join Staff on ConsumerStaff.Staff_ID = Staff.ID";
SQL = SQL + " Where Consumer_ID = " + ID;
SQL = SQL + " and Staff.FirstName || ' ' || Staff.LastName = '" + deleteStaff[j] + "')";
blPass = objDB.executeNonQuery(SQL);
}
return(blPass);
}
private bool updateStaff()
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
bool blPass = true; // if value is not updated it will return false
// Creating SQL to update ConsumerSTaff
for (int j = 0; j < StaffIDs.Count; j++)
{
string SQL = "Insert into ConsumerStaff(Staff_ID, Consumer_ID)";
SQL = SQL + " Values(" + StaffIDs[j].ID + "," + ID + ")";
blPass = objDB.executeNonQuery(SQL);
if (blPass != true)
{
break;
}
}
return(blPass);
}
private void addStaff(string strStaff)
{
StaffID objStaff = new StaffID();
lstStaff.Items.Add(cmbStaff.Text);
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Select ID";
SQL = SQL + " From Staff";
SQL = SQL + " Where Staff.FirstName || ' ' || LastName = '" + strStaff + "'";
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
while (objReader.Read())
{
objStaff.ID = objReader[0].ToString();
}
objStaff.Name = strStaff;
StaffIDs.Add(objStaff);
}
private void DeleteVoucher()
{
string strVoucherID = grdVouchers.SelectedRows[0].Cells[0].Value.ToString();
string SQL = "Delete From Voucher";
SQL = SQL + " Where ID = " + strVoucherID;
clsDB objDB = new clsDB(resPinnacle.liveDB);
bool blPass = objDB.executeNonQuery(SQL);
if (blPass == true)
{
fillGridVoucher();
}
else
{
MessageBox.Show("There was an error, your voucher was not deleted. If problem persists please notify IT.");
}
}
private void loadSelectedConsumer()
{
string strSQL = selectedConsumer_SQL();
clsDB objDB = new clsDB(strDB);
clsGridUtils objGrid = new clsGridUtils();
SQLiteDataReader objReader = objDB.returnDataReader(strSQL);
if (objReader.HasRows == true)
{
ConsumerID = objReader[0].ToString();
objGrid.fillGrid(objReader, 2, ref grdActivity, this.CreateGraphics());
}
else
{
MessageBox.Show("The user has no meetings currently in the system");
}
blAllConsumers = false;
}
private void DeleteReview()
{
DialogResult objResult;
objResult = MessageBox.Show("Are you sure you want to delete this review", "Delete Review", MessageBoxButtons.YesNo);
if (objResult == DialogResult.Yes)
{
string SQL = "DELETE from Review where ID=" + grdActivity[1, grdActivity.CurrentRow.Index].Value.ToString();
clsDB objDB = new clsDB(resPinnacle.liveDB);
bool blDelete = objDB.executeNonQuery(SQL);
if (blDelete == false)
{
MessageBox.Show("The review was not deleted. Please try again. If problem persists, please contact IT.");
}
else
{
loadSelectedConsumer();
}
}
}
private bool updateVRC()
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = "Update Consumer";
if (cmbCounselor.Text == "")
{
SQL = SQL + " Set VRC_ID = null";
}
else
{
SQL = SQL + " Set VRC_ID = (Select VRC.ID From VRC Where VRC.FirstName || ' ' || VRC.LastName = '" + cmbCounselor.Text + "'";
SQL = SQL + " and Consumer.ID = " + ID + ")";
}
SQL = SQL + " Where Consumer.ID = " + ID;
bool blPass = objDB.executeNonQuery(SQL);
return(blPass);
}
private string returnConsumerID(string strConsumer)
{
clsDB objDB = new clsDB(strDB);
string SQL = returnConsumerID_SQL(strConsumer);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
string ID = "";
int iCount = 0;
while (objReader.Read())
{
if (iCount < 1)
{
ID = objReader["ID"].ToString();
PassConsumerID(ID, strConsumer);
}
iCount++;
}
return(ID);
}
private string returnUpdateConsumerSQL(clsDB objDB)
{
string strSQL = "Update Consumer";
strSQL = strSQL + " Set FirstName = '" + txtFirst.Text + "'";
strSQL = strSQL + ", LastName= '" + txtLast.Text + "'";
strSQL = strSQL + ", SSN = '" + txtSSN.Text + "'";
strSQL = strSQL + ", AVR = '" + txtAVR.Text + "'";
strSQL = strSQL + ", VESID = '" + txtVesid.Text + "'";
strSQL = strSQL + ", Units = '" + txtUnits.Text + "'";
strSQL = strSQL + ", Service_ID = '" + cmbService.Text + "'";
strSQL = strSQL + ", Disability_ID = '" + objDB.returnComboBox_Key("Disability", "Description", cmbDisability.Text) + "'";
strSQL = strSQL + ", Funding_ID = '" + objDB.returnComboBox_Key("Funding", "Description", cmbFunding.Text) + "'";
clsFormat objFormat = new clsFormat();
if (dtReferral.Checked == true)
{
strSQL = strSQL + ", ReferralDate = " + objFormat.formatDate(dtReferral.Text);
}
else
{
strSQL = strSQL + ", ReferralDate = null";
}
if (dtIntake.Checked == true)
{
strSQL = strSQL + ", IntakeDate = " + objFormat.formatDate(dtIntake.Text);
}
else
{
strSQL = strSQL + ", IntakeDate = null";
}
strSQL = strSQL + " Where Consumer.ID = " + ID;
strSQL = strSQL.Replace("' - -'", "null");
strSQL = strSQL.Replace("''", "null");
return(strSQL);
}