/// <summary>
/// Returns a list of services performed based on vehicleID
/// </summary>
/// <param name="vehicleID"></param>
/// <returns></returns>
public static List <ServiceRecordType> GetListServicesPerformedByVehicle(int vehicleID)
{
List <ServiceRecordType> servicesPerformedList = new List <ServiceRecordType>();
string selectStatement = "SELECT SC.categoryName, SR.dateOfService, SR.mileage " +
"FROM serviceRecordType SRC " +
"JOIN serviceCategory SC ON SRC.serviceCategoryID = SC.serviceCategoryID " +
"JOIN serviceRecord SR ON SRC.serviceRecordID = SR.serviceRecordID " +
"WHERE SR.vehicleID = @vehicleID " +
"ORDER BY SR.dateOfService DESC";
SqlDataReader reader = null;
SqlConnection connection = null;
try
{
using (connection = DBConnection.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@vehicleID", vehicleID);
using (reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
ServiceRecordType src = new ServiceRecordType();
src.categoryName = reader["categoryName"].ToString();
src.dateOfService = (DateTime)reader["dateOfService"];
src.mileage = (int)reader["mileage"];
servicesPerformedList.Add(src);
}
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
if (reader != null)
{
reader.Close();
}
}
return(servicesPerformedList);
}
private void AddServiceRecord(ServiceRecordType type, TimeSpan addTime, string remark)
{
var now = DateTime.Now;
var uid = _User.Uid;
var day = (int)Math.Ceiling(addTime.TotalDays);
_UserServiceStateUpdateRecordRepository.Add(new UserServiceStateUpdateRecord()
{
uid = uid,
creted = now,
IncreaseDays = day,
remark = remark,
type = type
});
_UserServiceStateUpdateRecordRepository.SaveChanges();
}
/// <summary>
/// Associates a ServiceRecord with a ServiceCategory
/// </summary>
/// <param name="newServiceRecordType"></param>
/// <returns></returns>
public static int CreateServiceRecordType(ServiceRecordType newServiceRecordType)
{
int exitStatus = 0;
SqlConnection connection = DBConnection.GetConnection();
string insertStatement =
"INSERT serviceRecordType " +
"(serviceRecordID, serviceCategoryID)" +
"VALUES" +
"(@serviceRecordID, @serviceCategoryID)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@serviceRecordID", newServiceRecordType.serviceRecordID);
insertCommand.Parameters.AddWithValue("@serviceCategoryID", newServiceRecordType.serviceCategoryID);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
exitStatus = 0;
}
catch (SqlException ex)
{
exitStatus = 1;
StringBuilder errorDetails = new StringBuilder();
for (int i = 0; i < ex.Errors.Count; i++)
{
errorDetails.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"Error Number: " + ex.Errors[i].Number + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
MessageBox.Show(errorDetails.ToString(), "SQL Exception");
}
finally
{
connection.Close();
}
return(exitStatus);
}
public int AddServiceRecordType(ServiceRecordType newServiceRecordType)
{
return(ServiceRecordTypeDAL.CreateServiceRecordType(newServiceRecordType));
}
public int RemoveServiceRecordType(ServiceRecordType oldServiceRecordType)
{
return(ServiceRecordTypeDAL.RemoveServiceRecordType(oldServiceRecordType));
}
