/// <summary>
/// Insert shipinto the database
/// </summary>
/// <param name="ship"></param>
public void InsertShip(ContainerShip ship)
{
string query = $"INSERT INTO SHIP" +
$"(Id,Name,ATISCode,Origin,Destination,DepartureTime,ArrivalTime,Crew,MaxWeight,MaxSpeed,MaxWidth,MaxHeight,MaxLength) " +
$"VALUES(" +
$"{ship.Id}, " +
$"'" + ship.Name + "', " +
$"'" + ship.ATISCode + "', " +
$"{ship.Origin.Id}, " +
$"{ship.Destination.Id}, " +
$"'{ship.DepartureTime.ToString("yyyy-MM-dd HH:mm:ss.fff")}', " +
$"'{ship.ArrivalTime.ToString("yyyy-MM-dd HH:mm:ss.fff")}', " +
$"{ship.Crew}, " +
$"{ship.MaxWeight}, " +
$"{ship.MaxSpeed}, " +
$"{ship.MaxWidth}, " +
$"{ship.MaxHeight}, " +
$"{ship.MaxLength}" +
$");";
using (_connexion = new SQLiteConnection(_connString))
{
_connexion.Open();
using (SQLiteTransaction transaction = _connexion.BeginTransaction())
{
using (SQLiteCommand command = _connexion.CreateCommand())
{
command.CommandText = query;
command.ExecuteNonQuery();
}
transaction.Commit();
}
_connexion.Close();
}
}
/// <summary>
/// Delete the given ship
/// </summary>
/// <param name="ship"></param>
public void DeleteShip(ContainerShip ship)
{
string query = $"DELETE FROM SHIP " +
$"WHERE ATISCode = '{ship.ATISCode}' ";
using (_connexion = new SQLiteConnection(_connString))
{
_connexion.Open();
using (SQLiteCommand command = _connexion.CreateCommand())
{
command.CommandText = query;
command.ExecuteNonQuery();
}
_connexion.Close();
}
}
public void t4_insert_ship()
{
//Arrange
ShipQueries sut = new ShipQueries();
ContainerShip testShip = new ContainerShip()
{
Id = 61,
ATISCode = "gyugyctrcfcft456",
Name = "dsfdsfv",
Origin = new Harbor()
{
Id = 100
},
Destination = new Harbor()
{
Id = 101
},
DepartureTime = new System.DateTime(1992, 08, 12),
ArrivalTime = new System.DateTime(1992, 08, 12),
Cargo = new List <Container>(),
Crew = 6,
MaxHeight = 30,
MaxLength = 220,
MaxSpeed = 20,
MaxWeight = 33000,
MaxWidth = 20
};
//Act
sut.InsertShip(testShip);
var control = sut.GetShipById(testShip.Id);
//Assert
Assert.NotNull(control);
Assert.AreEqual(testShip.Id, control.Id);
Assert.AreEqual(testShip.ATISCode, control.ATISCode);
Assert.AreEqual(testShip.Name, control.Name);
Assert.AreEqual(testShip.DepartureTime, control.DepartureTime);
Assert.AreEqual(testShip.ArrivalTime, control.ArrivalTime);
Assert.AreEqual(testShip.Crew, control.Crew);
Assert.AreEqual(testShip.MaxHeight, control.MaxHeight);
Assert.AreEqual(testShip.MaxLength, control.MaxLength);
Assert.That(testShip.MaxSpeed, Is.EqualTo(control.MaxSpeed).Within(0.001));
Assert.AreEqual(testShip.MaxWeight, control.MaxWeight);
Assert.AreEqual(testShip.MaxWidth, control.MaxWidth);
}
/// <summary>
/// Return the name of ships ordered by crew size
/// </summary>
/// <returns></returns>
public List <ContainerShip> GetShipsByCrew()
{
string query = "SELECT * " +
"FROM SHIP " +
"ORDER BY CREW";
List <ContainerShip> result = new List <ContainerShip>();
using (_connexion = new SQLiteConnection(_connString))
{
_connexion.Open();
using (SQLiteCommand command = _connexion.CreateCommand())
{
command.CommandText = query;
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ContainerShip ship = new ContainerShip
{
Id = Convert.ToInt32(reader["Id"]),
Name = reader["Name"].ToString(),
ATISCode = reader["ATISCode"].ToString(),
Origin = harborQueries.GetHarborById(Convert.ToInt32(reader["Origin"])),
Destination = harborQueries.GetHarborById(Convert.ToInt32(reader["Destination"])),
DepartureTime = DateTime.Parse(reader["DepartureTime"].ToString()),
ArrivalTime = DateTime.Parse(reader["ArrivalTime"].ToString()),
Crew = Convert.ToInt32(reader["Crew"]),
MaxWeight = Convert.ToInt32(reader["MaxWeight"]),
MaxSpeed = Convert.ToDouble(reader["MaxSpeed"]),
MaxWidth = Convert.ToInt32(reader["MaxWidth"]),
MaxHeight = Convert.ToInt32(reader["MaxHeight"]),
MaxLength = Convert.ToInt32(reader["MaxLength"])
};
result.Add(ship);
}
}
}
_connexion.Close();
}
return(result);
}
/// <summary>
/// Return a ship by it's Id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ContainerShip GetShipById(int id)
{
string query = "SELECT *" +
"FROM SHIP S " +
$"WHERE S.ID = {id}";
ContainerShip result = null;
using (_connexion = new SQLiteConnection(_connString))
{
_connexion.Open();
using (SQLiteCommand command = _connexion.CreateCommand())
{
command.CommandText = query;
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result = new ContainerShip();
result.Id = Convert.ToInt32(reader["Id"]);
result.ATISCode = reader["ATISCode"].ToString();
result.Name = reader["Name"].ToString();
result.Origin = harborQueries.GetHarborById(Convert.ToInt32(reader["Origin"]));
result.Destination = harborQueries.GetHarborById(Convert.ToInt32(reader["Destination"]));
result.DepartureTime = DateTime.Parse(reader["DepartureTime"].ToString());
result.ArrivalTime = DateTime.Parse(reader["ArrivalTime"].ToString());
result.Crew = Convert.ToInt32(reader["Crew"]);
result.MaxWeight = Convert.ToInt32(reader["MaxWeight"]);
result.MaxSpeed = Convert.ToDouble(reader["MaxSpeed"]);
result.MaxWidth = Convert.ToInt32(reader["MaxWidth"]);
result.MaxHeight = Convert.ToInt32(reader["MaxHeight"]);
result.MaxLength = Convert.ToInt32(reader["MaxLength"]);
}
}
}
_connexion.Close();
}
return(result);
}
public void t5_delete_ship()
{
//Arrange
ContainerShip testShip = new ContainerShip()
{
Id = 50,
ATISCode = Guid.NewGuid().ToString(),
Name = generator.GetRandomName(),
Origin = new Harbor()
{
Id = 100
},
Destination = new Harbor()
{
Id = 101
},
DepartureTime = new System.DateTime(1992, 08, 12),
ArrivalTime = new System.DateTime(1992, 08, 12),
Cargo = new List <Container>(),
Crew = 6,
MaxHeight = 30,
MaxLength = 220,
MaxSpeed = 20,
MaxWeight = 33000,
MaxWidth = 20
};
ShipQueries sut = new ShipQueries();
//Act
sut.InsertShip(testShip);
sut.DeleteShip(testShip);
var control = sut.GetShipById(testShip.Id);
//Assert
Assert.Null(control);
}
public bool DetermineShipLimits()
{
var containerList = new List <Container>();
for (var i = 0; i <= 10; i++)
{
var container = new Container();
container.Weight = 7.5 * i;
containerList.Add(container);
}
var ship = new ContainerShip(500.00);
//ship.MaxWeight = 500.00;
ship.Containers = containerList;
var val = ship.CalculateHarborFee(ContainerClass.FourtyFoot, FeeExempt.Variable);
Calculate cal = new Calculate();
_ = cal.ShippingCost(0, 0, 0, Calculate.ShippingType.Overnight);
return(ship.ShipOverweight());
}
public void t2_get_correct_ship_by_id()
{
//Arrange
ShipQueries sut = new ShipQueries();
//Act
ContainerShip genData = generator.ContainerShips.FirstOrDefault();
ContainerShip data = sut.GetShipById(genData.Id);
//Assert
Assert.AreEqual(genData.Id, data.Id);
Assert.AreEqual(genData.ATISCode, data.ATISCode);
Assert.AreEqual(genData.Name, data.Name);
Assert.AreEqual(genData.DepartureTime, data.DepartureTime);
Assert.AreEqual(genData.ArrivalTime, data.ArrivalTime);
Assert.AreEqual(genData.Crew, data.Crew);
Assert.AreEqual(genData.MaxHeight, data.MaxHeight);
Assert.AreEqual(genData.MaxLength, data.MaxLength);
Assert.That(genData.MaxSpeed, Is.EqualTo(data.MaxSpeed).Within(0.001));
Assert.AreEqual(genData.MaxWeight, data.MaxWeight);
Assert.AreEqual(genData.MaxWidth, data.MaxWidth);
}
/// <summary>
/// Delete the given ship
/// </summary>
/// <param name="ship"></param>
public void DeleteShip(ContainerShip ship)
{
throw new NotImplementedException();
}
/// <summary>
/// Insert shipinto the database
/// </summary>
/// <param name="ship"></param>
public void InsertShip(ContainerShip ship)
{
throw new NotImplementedException();
}
