public void QueryCloseOrder(SqlConnection conn, SessionQh session, FineQh fine)
{
string sql;
if (fine != null)
{
sql = "update Session set End_datetime = @dateclosed where Session_ID = @sessionid;" +
"insert into Car_Return(Session_ID, Fine_ID) Values (@sessionid, @fineid);" +
"insert into Payments(Session_ID, Date_time) Values (@sessionid, @dateclosed);";
}
else
{
sql = "update Session set End_datetime = @dateclosed where Session_ID = @sessionid;" +
"insert into Car_Return(Session_ID) Values (@sessionid);" +
"insert into Payments(Session_ID, Date_time) Values (@sessionid, @dateclosed);";
}
var cmd = new SqlCommand()
{
Connection = conn,
CommandText = sql
};
if (fine != null)
{
cmd.Parameters.Add("@fineid", SqlDbType.Int).Value = fine.Fine_ID;
}
cmd.Parameters.Add("@dateclosed", SqlDbType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@sessionid", SqlDbType.Int).Value = session.Session_ID;
cmd.ExecuteNonQuery();
}
public List <SessionQh> QueryPastSessions(SqlConnection conn)
{
var sessions = new List <SessionQh>();
var sql = "select * from Session where End_datetime is not null";
var cmd = new SqlCommand
{
Connection = conn,
CommandText = sql
};
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var sessionID = reader.GetInt32(0);
var startDt = reader.GetDateTime(1);
var endDt = reader.GetValue(2) as DateTime?;
if (endDt != null)
{
endDt = reader.GetDateTime(2);
}
var clientID = reader.GetInt32(3);
var carID = reader.GetInt32(4);
var endLocationID = reader.GetInt32(5);
var startLocationID = reader.GetInt32(6);
var managerID = reader.GetInt32(7);
var tempSession = new SessionQh()
{
Session_ID = sessionID,
Start_datetime = startDt,
End_datetime = endDt,
Client_ID = clientID,
Car_ID = carID,
End_location_ID = endLocationID,
Start_location_ID = startLocationID,
Manager_ID = managerID
};
sessions.Add(tempSession);
}
}
}
return(sessions);
}