private void dtDate_ValueChanged(object sender, EventArgs e)
{
clsFormat objFormat = new clsFormat();
string Day = objFormat.formatDate(dtDate.Text);
getCurrentJob(ID, Day);
}
private string addMeetingSQL()
{
clsFormat objFormat = new clsFormat();
if (JobID == "" || JobID == null)
{
JobID = "null";
}
/* Todo - update this SQL statement to fit in with new logic within system */
string strSQL = "Insert into Review(Date,DesiredOutcome,Barriers,Note,Job_ID, Staff_ID, Meeting_ID, Funding_ID, Consumer_ID, TimeIn, TimeOut)";
strSQL = strSQL + " Select " + objFormat.formatDate(dtDate.Text) + ",'" + objFormat.removeApostrophe(txtOutcome.Text) + "','" + objFormat.removeApostrophe(txtBarriers.Text) + "',";
strSQL = strSQL + " '" + objFormat.removeApostrophe(txtDetails.Text) + "'," + JobID + ", Staff.ID, Meeting.ID, Consumer.Funding_ID," + ID;
strSQL = strSQL + ", '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "', '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
strSQL = strSQL + " FROM Staff, Consumer, Meeting ";
// strSQL = strSQL + " Join ConsumerStaff on Staff.ID = ConsumerStaff.Staff_ID";
// strSQL = strSQL + " Join Consumer on ConsumerStaff.Consumer_ID = Consumer.ID";
// strSQL = strSQL + " , Meeting";
strSQL = strSQL + " WHERE Staff.FirstName || ' ' || Staff.LastName = '" + Staff + "'";
strSQL = strSQL + " AND Meeting.Description = '" + cmbMeeting.Text + "'";
strSQL = strSQL + " AND Consumer.ID = " + ID;
return(strSQL);
}
private string updateAdmin_SQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Update Administrative";
SQL = SQL + " Set Date = " + objFormat.formatDate(dtDateAdmin.Text);
SQL = SQL + " , Description = '" + objFormat.removeApostrophe(txtDescription.Text) + "'";
SQL = SQL + " , StartTime = '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "'";
SQL = SQL + " , EndTime = '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " Where Administrative.ID = " + AdminID;
return(SQL);
}
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 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 writeActivityLog_XML(SQLiteDataReader objReader)
{
clsFormat objFormat = new clsFormat();
strActivityLogPath = strActivityLogPath + "ActivityLog.xml";
XmlWriter xmlWrite = XmlWriter.Create(strActivityLogPath);
xmlWrite.WriteStartDocument();
xmlWrite.WriteStartElement("ActivityLog");
xmlWrite.WriteElementString("Header", "");
xmlWrite.WriteStartElement("Body");
while (objReader.Read())
{
xmlWrite.WriteElementString("Consumer", objReader["Consumer"].ToString());
xmlWrite.WriteStartElement("Employer");
xmlWrite.WriteElementString("Name", objReader["Employer"].ToString());
xmlWrite.WriteStartElement("Location");
xmlWrite.WriteElementString("Address", objReader["Address"].ToString());
xmlWrite.WriteElementString("CityStateZip", objReader["CityStateZip"].ToString());
xmlWrite.WriteEndElement();
xmlWrite.WriteEndElement();
xmlWrite.WriteStartElement("Review");
xmlWrite.WriteStartElement("Info");
xmlWrite.WriteElementString("Date", objReader["Date"].ToString());
xmlWrite.WriteElementString("MeetingType", objReader["MeetingType"].ToString());
string Time = objFormat.convertTime_12hr(objReader.GetDateTime(objReader.GetOrdinal("TimeIn")));
xmlWrite.WriteElementString("TimeIn", Time);
Time = objFormat.convertTime_12hr(objReader.GetDateTime(objReader.GetOrdinal("TimeOut")));
xmlWrite.WriteElementString("TimeOut", Time);
xmlWrite.WriteEndElement();
//xmlWrite.WriteElementString("WaiverEnrolled", objReader["WaiverEnrolled"].ToString());
xmlWrite.WriteElementString("ValuedOutcome", objReader["ValuedOutcome"].ToString());
xmlWrite.WriteElementString("Barriers", objReader["Barriers"].ToString());
xmlWrite.WriteElementString("Notes", objReader["Notes"].ToString());
xmlWrite.WriteEndElement(); //Review
xmlWrite.WriteEndElement(); //Body
}
xmlWrite.WriteStartElement("Footer");
xmlWrite.WriteElementString("Date", DateTime.Now.ToShortDateString());
xmlWrite.WriteEndElement();
xmlWrite.WriteEndElement();
xmlWrite.Close();
objReader.Close();
}
public void LoadControl(string strID, string strConsumer, string strStaffID)
{
fillComboBox();
txtConsumer.Text = strConsumer;
ID = strID;
Staff = strStaffID;
clsFormat objFormat = new clsFormat();
string Day = objFormat.formatDate(dtDate.Text);
getCurrentJob(strID, Day);
LoadTimes();
txtOutcome.Focus();
}
private string addAdmin_SQL()
{
clsFormat objFormat = new clsFormat();
string SQL = "Insert Into Administrative(Date,Description,StartTime,EndTime, staff_ID)";
SQL = SQL + " Select " + objFormat.formatDate(dtDateAdmin.Text);
SQL = SQL + " ,'" + objFormat.removeApostrophe(txtDescription.Text) + "'";
SQL = SQL + " ,'" + objFormat.convertTime_24hr(dtTimeIn.Value) + "'";
SQL = SQL + " ,'" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " , Staff.ID";
SQL = SQL + " From Staff";
SQL = SQL + " Where FirstName || ' ' || LastName = '" + StaffID + "'";
return(SQL);
}
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);
}
}
// will take time and add 1/2 hour to out time, or remove 1/2 hour from output time
private string updateTime(string Update, string strTime)
{
TimeSpan NewTime;
if (Update == "out")
{
NewTime = Convert.ToDateTime(strTime).AddMinutes(60).TimeOfDay;
}
else
{
NewTime = Convert.ToDateTime(strTime).AddMinutes(-60).TimeOfDay;
}
clsFormat objFormat = new clsFormat();
string strOutput = objFormat.convertTime_12hr(NewTime);
return(strOutput);
}
private string updateMeetingSQL()
{
clsFormat objFormat = new clsFormat();
if (JobID == "" || JobID == null)
{
JobID = "null";
}
string SQL = "Update Review";
SQL = SQL + " Set Date = " + objFormat.formatDate(dtDate.Text) + ", DesiredOutcome = '" + objFormat.removeApostrophe(txtOutcome.Text) + "'";
SQL = SQL + ", Barriers ='" + objFormat.removeApostrophe(txtBarriers.Text) + "'";
SQL = SQL + ", Note ='" + objFormat.removeApostrophe(txtDetails.Text) + "'";
SQL = SQL + ",Job_ID = " + JobID + ", Staff_ID = " + StaffID + ", Meeting_ID ='" + MeetingID + "'";
SQL = SQL + ",TimeIn= '" + objFormat.convertTime_24hr(dtTimeIn.Value) + "', TimeOut = '" + objFormat.convertTime_24hr(dtTimeOut.Value) + "'";
SQL = SQL + " Where Review.ID = " + ReviewID;
return(SQL);
}
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);
}
private string returnAddConsumerSQL(clsDB objDB)
{
string SQL = "Insert into Consumer(SSN,AVR, VESID, FirstName, LastName, Disability_ID, Service_ID,Units, Funding_ID, ReferralDate,IntakeDate)";
SQL = SQL + " Values(";
SQL = SQL + "'" + txtSSN.Text + "'";
SQL = SQL + ",'" + txtAVR.Text + "'";
SQL = SQL + ",'" + txtVesid.Text + "'";
SQL = SQL + ",'" + txtFirst.Text + "'";
SQL = SQL + ",'" + txtLast.Text + "'";
SQL = SQL + ",'" + objDB.returnComboBox_Key("Disability", "Description", cmbDisability.Text) + "'";
SQL = SQL + ",'" + cmbService.Text + "'";
SQL = SQL + ",'" + txtUnits.Text + "'";
SQL = SQL + ",'" + objDB.returnComboBox_Key("Funding", "Description", cmbFunding.Text) + "'";
clsFormat objFormat = new clsFormat();
if (dtReferral.Checked == true)
{
SQL = SQL + ", " + objFormat.formatDate(dtReferral.Text);
}
else
{
SQL = SQL + ", null";
}
if (dtIntake.Checked == true)
{
SQL = SQL + ", " + objFormat.formatDate(dtIntake.Text);
}
else
{
SQL = SQL + ", null";
}
SQL = SQL + ")";
return(SQL);
}
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();
}
public void fillGrid(SQLiteDataReader objReader, int ColStart, ref DataGridView Grid, Graphics objGraphics)
{
clsFormat objFormat = new clsFormat();
int[] colW = new int[objReader.FieldCount];
//Setting rows and columns
Grid.Columns.Clear();
Grid.Rows.Clear();
int Row = 0;
Grid.ColumnCount = objReader.FieldCount;
//Adding Data
while (objReader.Read())
{
SizeF objSize;
Grid.Rows.Add();
for (int Col = 0; Col < objReader.FieldCount; Col++)
{
if (Row == 0)
{
string strHeader = objReader.GetName(Col);
Grid.Columns[Col].HeaderText = objReader.GetName(Col);
objSize = objGraphics.MeasureString(objReader.GetName(Col), Grid.Font);
if (Col >= ColStart)
{
colW[Col] = (int)objSize.Width;
}
}
string strValue = "";
if (objReader.GetDataTypeName(Col) == "Time")
{
strValue = objFormat.convertTime_12hr(objReader.GetDateTime(Col));
}
else
{
strValue = objReader[Col].ToString();
}
objSize = objGraphics.MeasureString(strValue.Trim(), Grid.Font);
if (objSize.Width > colW[Col] && Col > 0)
{
if (Col >= ColStart)
{
colW[Col] = (int)objSize.Width;
}
}
Grid[Col, Row].Value = strValue;
}
Row++;
}
// grdActivity.AutoResizeColumns();
AdjustGrid(colW, Grid.Columns.Count - ColStart, ColStart, ref Grid);
if (Grid.RowCount > 0)
{
Grid.Rows[0].Selected = false;
}
}
private void writeMonthlyActivityLog_XML(SQLiteDataReader objReader)
{
clsFormat objFormat = new clsFormat();
float fltTotalTime = 0;
strActivityLogPath = strActivityLogPath + "MonthlyActivityLog.xml";
ArrayList strBarriers = new ArrayList();
XmlWriter xmlWrite = XmlWriter.Create(strActivityLogPath);
xmlWrite.WriteStartDocument();
xmlWrite.WriteStartElement("MonthlyActivityLog");
bool blStart = true;
while (objReader.Read())
{
if (blStart == true)
{
xmlWrite.WriteStartElement("Header");
// getting Month Name from .net
int intMonth = Convert.ToInt16(objReader["Month"].ToString());
var LongMth = CultureInfo.CurrentCulture.DateTimeFormat;
string strMonth = LongMth.GetMonthName(intMonth);
xmlWrite.WriteElementString("Month", strMonth);
xmlWrite.WriteElementString("Year", objReader["Year"].ToString());
xmlWrite.WriteEndElement();
xmlWrite.WriteStartElement("Body");
xmlWrite.WriteStartElement("Consumer");
xmlWrite.WriteElementString("Name", objReader["Consumer"].ToString());
xmlWrite.WriteElementString("SSN", objReader["SSN"].ToString());
xmlWrite.WriteEndElement();
xmlWrite.WriteStartElement("Reviews");
blStart = false;
}
xmlWrite.WriteStartElement("Review");
xmlWrite.WriteElementString("Staff", objReader["Staff"].ToString());
xmlWrite.WriteElementString("Date", objReader["Date"].ToString());
xmlWrite.WriteElementString("Employer", objReader["Employer"].ToString());
xmlWrite.WriteElementString("MeetingType", objReader["MeetingType"].ToString());
xmlWrite.WriteElementString("Note", objReader["Note"].ToString());
string Time = objFormat.convertTime_12hr(objReader.GetDateTime(objReader.GetOrdinal("TimeIn")));
xmlWrite.WriteElementString("TimeIn", Time);
Time = objFormat.convertTime_12hr(objReader.GetDateTime(objReader.GetOrdinal("TimeOut")));
xmlWrite.WriteElementString("TimeOut", Time);
float fltTime = ReturnHours(objReader["TimeIn"].ToString(), objReader["TimeOut"].ToString());
xmlWrite.WriteElementString("Hours", fltTime.ToString());
fltTotalTime = fltTotalTime + fltTime;
// Adding to Barriers Collection, to be used later
if (objReader["Barriers"].ToString().Trim() != "" && objReader["Barriers"].ToString() != null)
{
strBarriers.Add(objReader["Barriers"].ToString());
}
xmlWrite.WriteEndElement();
}
xmlWrite.WriteElementString("TotalHours", fltTotalTime.ToString());
xmlWrite.WriteEndElement(); //Reviews
writeMonthlyActivityLog_BarriersXML(ref xmlWrite, strBarriers);
xmlWrite.WriteEndElement(); //Body
//footer
xmlWrite.WriteStartElement("Footer");
xmlWrite.WriteElementString("Date", System.DateTime.Today.ToShortDateString());
xmlWrite.WriteEndElement();
xmlWrite.Close();
objReader.Close();
}
