private void DisplayServiceHistory(Account accountToView)
{
//Get the list of existing service work
ServiceWorkList servWorkList = ServiceWorkManager.GetListByAccountID(accountToView.AccountID);
gviewServiceHistory.DataSource = servWorkList;
gviewServiceHistory.DataBind();
}
private void PopulateGridviewSchedule(DateTime myDate)
{
List <string> workHoursList = WorkHourDB.GetWorkHoursList();
List <DateTime> daysList; //contains the list of dates starting from sunday that fall within the week relative to the myDate variable
DateTimeFormatInfo dtfi;
DisplayGridviewHeader(myDate, out daysList, out dtfi);
int cellNumberToUse = 0;
foreach (DateTime d in daysList)
{
cellNumberToUse = GetTheCellNumberToUse(cellNumberToUse, d);
// Prevent the user from scheduling previous days
// disable linkbuttons where the date is < todays date
if (d < DateTime.Now.ToLocalTime().AddDays(-1))
{
foreach (GridViewRow row in gviewSchedule.Rows)
{
row.Cells[cellNumberToUse].Text = "-";
}
}
// get all appointments that fall on this day
// STG_ServiceWorkList stg_servWorkList = STG_ServiceWorkManager.GetListByDate(d.Date);
ServiceWorkList existingServiceWorkList = ServiceWorkManager.GetListByDate(d.Date);
if (existingServiceWorkList != null)
{
foreach (ServiceWork s in existingServiceWorkList)
{
// find the start time of this service work
string myStartTime = s.ServiceStartTime.ToShortTimeString();
int index = workHoursList.IndexOf(myStartTime.Replace(" ", ""));
// determine how long the appointment is, i.e from 10:00AM to 11:00AM and then
// calculate the number of 30 minute interval from that range.
int myInterval = STG_ServiceWork.CalculateNumberOfIntervals(s.ServiceStartTime, s.ServiceEndTime);
if (index != -1)
{
//found a match
for (int i = index; i < (myInterval + index); i++)
{
gviewSchedule.Rows[i].Cells[cellNumberToUse].Text = "Service Work ID: " + s.ServiceWorkID.ToString();
gviewSchedule.Rows[i].Cells[cellNumberToUse].ToolTip = "Service Work ID: " + s.ServiceWorkID.ToString();
}
}
}
}
}
// Display the reference date in the textbox
txtRefDate.Text = myDate.ToString("d", dtfi);
}
private void SaveSTG_ServiceWork(STG_ServiceWork stg_servWork)
{
//
// Before saving, check for conflicts with existing service works based on service date
//
// Retrieve existing service works for a specific date
ServiceWorkList existingServiceWorkList = ServiceWorkManager.GetListByDate(Convert.ToDateTime(actualServiceDate), ddlServiceStartTime.SelectedValue.ToString());
bool foundConflict = false;
bool recordSaved = false;
if (existingServiceWorkList != null)
{
foreach (ServiceWork existingServiceWork in existingServiceWorkList)
{
while ((foundConflict == false) && (recordSaved == false))
{
if ((stg_servWork.ServiceEndTime.TimeOfDay > existingServiceWork.ServiceStartTime.TimeOfDay))
{
//do not allow the save
foundConflict = true;
lblBookingMessage.Text = "Cannot book the time. A conflict will occur with booking# " + existingServiceWork.ServiceWorkID;
LoadWorkHousInGridview();
PopulateGridviewSchedule(Convert.ToDateTime(actualServiceDate));
//show the scheduler
this.mPopupSetServiceDateTime.Show();
}
else
{
//save the service appointment
stg_servWork.STG_servID = STG_ServiceWorkManager.Save(stg_servWork);
ShowTheConfirmedDateTime();
recordSaved = true;
}
} //end while loop
} //end foreach loop
}
else
{
//there's no conflict so saving can be made
//save the service appointment
stg_servWork.STG_servID = STG_ServiceWorkManager.Save(stg_servWork);
ShowTheConfirmedDateTime();
}
}
public static ServiceWorkList GetListByDate(DateTime theDate, string startTime)
{
ServiceWorkList aList = null;
MyDBConnection myConn = new MyDBConnection();
SqlConnection conn = new SqlConnection();
SqlDataReader dr;
SqlCommand cmd = null;
//string sql = "Select * from AquaOne.dbo.ServiceWork where serviceDate = @ServiceDate and serviceStartTime > @ServiceStartTime ";
string sql = "Select * from AquaOne.dbo.ServiceWork where serviceDate = @ServiceDate and serviceStartTime > @ServiceStartTime order by serviceStartTime";
try
{
// Open the connection
conn = myConn.OpenDB();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@ServiceDate", SqlDbType.DateTime).Value = theDate.Date;
DateTime serviceStartTime = Convert.ToDateTime(startTime);
cmd.Parameters.Add("@ServiceStartTime", SqlDbType.Time).Value = new TimeSpan(serviceStartTime.Hour, serviceStartTime.Minute, 0);
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
aList = new ServiceWorkList();
while (dr.Read())
{
aList.Add(FillDataRecord(dr));
}
}
cmd.Dispose();
dr.Close();
}
finally
{
myConn.CloseDB(conn);
}
return(aList);
}
public static ServiceWorkList GetListByAccountID(int accountID)
{
ServiceWorkList aList = null;
MyDBConnection myConn = new MyDBConnection();
SqlConnection conn = new SqlConnection();
SqlDataReader dr;
SqlCommand cmd = null;
string sql = "Select * from AquaOne.dbo.ServiceWork where accountID = @accountID";
try
{
// Open the connection
conn = myConn.OpenDB();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@accountID", SqlDbType.Int).Value = accountID;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
aList = new ServiceWorkList();
while (dr.Read())
{
aList.Add(FillDataRecord(dr));
}
}
cmd.Dispose();
dr.Close();
}
finally
{
myConn.CloseDB(conn);
}
return(aList);
}
