private SQLiteDataReader returnMeetingData(string SQL)
{
clsDB objDb = new clsDB(resPinnacle.liveDB);
SQLiteDataReader objReader = objDb.returnDataReader(SQL);
return(objReader);
}
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 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();
}
}
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 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 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 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 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 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 void fillGrid_AllActivity()
{
string SQL = returnAllActivity_SQL();
clsDB objDB = new clsDB(strDB);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
clsGridUtils objGrid = new clsGridUtils();
bool Loaded = false;
if (objReader.HasRows == true)
{
objGrid.fillGrid(objReader, 1, ref grdAllActivity, this.CreateGraphics());
returnHours();
}
else
{
grdAllActivity.Rows.Clear();
lblHours.Text = "Hours: 0";
}
}
public void LoadControl(string strReviewID, string strStaff)
{
fillComboBox();
clsDB objDB = new clsDB(strDB);
string SQL = "Select Consumer.ID as Consumer_ID, Consumer.FirstName || ' ' || Consumer.LastName as Consumer,Staff_ID, strftime('%m/%d/%Y',Date)";
SQL = SQL + " From Review Join Consumer on Review.Consumer_ID = Consumer.ID";
SQL = SQL + " Where Review.ID = " + strReviewID;
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
txtConsumer.Text = objReader["Consumer"].ToString();
ID = strReviewID;
StaffID = objReader["Staff_ID"].ToString();
clsFormat objFormat = new clsFormat();
string Day = objFormat.formatDate(dtDate.Text);
getCurrentJob(ID, Day);
LoadTimes();
txtOutcome.Focus();
}
private void getCurrentJob(string ID, string Day)
{
string SQL = "SELECT Job.ID, Title, Description,Employer,Address,City,Zip,strftime('%m/%d/%Y',PlacementDate) as PlacementDate";
SQL = SQL + " FROM Job";
SQL = SQL + " Where Consumer_ID =" + ID;
SQL = SQL + " and (Job.PlacementDate <=" + Day;
SQL = SQL + " or Job.PlacementDate is null)";
SQL = SQL + " and (Job.EndDate>=" + Day;
SQL = SQL + " or Job.EndDate is null)";
clsDB objDB = new clsDB(strDB);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
if (objReader.HasRows == true)
{
chkEmployed.Checked = true;
while (objReader.Read())
{
JobID = objReader["ID"].ToString();
Employer = objReader["Employer"].ToString();
Job = objReader["Title"].ToString();
txtJob.Text = objReader["Title"].ToString();
txtDescription.Text = objReader["Description"].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();
}
}
else
{
chkEmployed.Checked = false;
}
}
public string CreateMonthlyActivityLog(string ConsumerID, string Month, string Year)
{
clsDB objDB = new clsDB(resPinnacle.liveDB);
string SQL = returnMonthlyActivityLog_SQL(ConsumerID, Month, Year);
SQLiteDataReader objReader = objDB.returnDataReader(SQL);
string strReturn = "";
if (objReader.HasRows == true)
{
writeMonthlyActivityLog_XML(objReader);
CreateFO("Monthly Activity Log");
string strPath = Path.GetTempPath() + "MonthlyActivityLog.pdf";
if (File.Exists(strPath) == true)
{
System.Diagnostics.Process.Start(strPath);
}
}
else
{
strReturn = "No rows were returned, for the month requested.";
}
return(strReturn);
}
