public static int Save(Driver obj)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
int driverID = -1;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_INSERT, connection);
//command.Parameters.AddWithValue("@ID", obj.ID); //idk if ID too? Should be autogenerated
command.Parameters.AddWithValue("@Name", obj.Name);
command.Parameters.AddWithValue("@Street", obj.Street);
command.Parameters.AddWithValue("@Town", obj.Street);
command.Parameters.AddWithValue("@Country", obj.Country);
command.Parameters.AddWithValue("@Residence", obj.Residence);
command.Parameters.AddWithValue("@BirthDate", obj.BirthDate);
command.Parameters.AddWithValue("@RemainingPoints", obj.RemainingPoints);
command.Parameters.AddWithValue("@LicenseNumber", obj.LicenseNumber);
command.Parameters.AddWithValue("@LicenseValidity", obj.LicenseValidity);
command.Parameters.AddWithValue("@State", obj.State);
command.Parameters.AddWithValue("@Password", obj.Password);
driverID = (int)command.ExecuteScalar();
}
return(driverID);
}
else
{
XMLInsert(obj);
return(freeID);
}
}
public static Vehicle FindBySPZ(string SPZ)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
Vehicle vehicle = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_SPZ, connection);
command.Parameters.AddWithValue("@SPZ", SPZ);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
vehicle = Map(reader);
}
}
}
return(vehicle);
}
else
{
Vehicle obj = new Vehicle();
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList list = root.SelectNodes("Vehicle");
XmlNode objNode = null;
foreach (XmlNode node in list)
{
XmlNodeList childs = node.ChildNodes;
foreach (XmlNode child in childs)
{
if (child.Name == "Spz")
{
objNode = (child.InnerText == SPZ.ToString()) ? node : null;
break;
}
}
if (objNode != null)
{
break;
}
}
if (objNode != null)
{
obj = XMLMap(objNode);
}
return(obj);
}
}
public static Driver FindByID(int id)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
Driver driver = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_ID, connection);
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
driver = MapDriver(reader);
}
}
}
return(driver);
}
else
{
Driver driver = new Driver();
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList list = root.SelectNodes("Driver");
XmlNode objNode = null;
foreach (XmlNode node in list)
{
XmlNodeList childs = node.ChildNodes;
foreach (XmlNode child in childs)
{
if (child.Name == "ID")
{
objNode = (child.InnerText == id.ToString()) ? node : null;
break;
}
}
if (objNode != null)
{
break;
}
}
if (objNode != null)
{
driver = XMLMap(objNode);
}
return(driver);
}
}
//not physicaly deleted, only state is changed to false = inactive driver
public static int DeleteID(int id)
{
int ret = 0;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_UPDATE, connection);
Driver obj = FindByID(id);
obj.State = false;
Update(obj);
ret = command.ExecuteNonQuery();
}
return(ret);
}
public Office FindByID(int id)
{
Office obj = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_ID, connection);
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
obj = Map(reader);
}
}
}
return(obj);
}
public static Employee FindByID(int id)
{
Employee employee = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_ID, connection);
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
employee = MapEmployee(reader);
}
}
}
return(employee);
}
public static List <FineType> FindAll()
{
List <FineType> list = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_ALL, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
list = new List <FineType>();
while (reader.Read())
{
list.Add(Map(reader));
}
}
}
return(list);
}
public static Driver FindByLicenseNumber(int number)
{
Driver driver = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_LICENSE, connection);
command.Parameters.AddWithValue("@number", number);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
driver = MapDriver(reader);
}
}
}
return(driver);
}
public static List <Vehicle> FindAllDriverVehicles(int driverID)
{
List <Vehicle> list = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_ID, connection);
command.Parameters.AddWithValue("@driverID", driverID);
using (SqlDataReader reader = command.ExecuteReader())
{
list = new List <Vehicle>();
while (reader.Read())
{
list.Add(Map(reader));
}
}
}
return(list);
}
public static List <Record> FindAllByID(int id)
{
List <Record> list = null;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_SELECT_RECORDS_BY_DRIVER_ID, connection);
command.Parameters.AddWithValue("@ID", id);
using (SqlDataReader reader = command.ExecuteReader())
{
list = new List <Record>();
while (reader.Read())
{
list.Add(MapCustom(reader));
}
}
}
return(list);
}
public static int Update(Driver obj)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
int ret = 0;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_UPDATE, connection);
command.Parameters.AddWithValue("@ID", obj.ID);
command.Parameters.AddWithValue("@Name", obj.Name);
command.Parameters.AddWithValue("@Street", obj.Street);
command.Parameters.AddWithValue("@Town", obj.Street);
command.Parameters.AddWithValue("@Country", obj.Country);
command.Parameters.AddWithValue("@Residence", obj.Residence);
command.Parameters.AddWithValue("@BirthDate", obj.BirthDate);
command.Parameters.AddWithValue("@RemainingPoints", obj.RemainingPoints);
command.Parameters.AddWithValue("@LicenseNumber", obj.LicenseNumber);
command.Parameters.AddWithValue("@LicenseValidity", obj.LicenseValidity);
command.Parameters.AddWithValue("@State", obj.State);
command.Parameters.AddWithValue("@Password", obj.Password);
ret = command.ExecuteNonQuery();
}
return(ret);
}
else
{
xmlDoc.Load(filePath);
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList list = root.SelectNodes("Driver");
XmlNode driverNode = null;
foreach (XmlNode node in list)
{
XmlNodeList childs = node.ChildNodes;
foreach (XmlNode child in childs)
{
if (child.Name == "ID") // && child.InnerText == driver.ID.ToString())
{
driverNode = (child.InnerText == obj.ID.ToString()) ? node : null;
driverNode = node;
break;
}
}
if (driverNode != null)
{
break;
}
}
foreach (XmlNode child in driverNode.ChildNodes)
{
if (child.Name == "Name")
{
child.InnerText = obj.Name;
}
if (child.Name == "Street")
{
child.InnerText = obj.Street;
}
if (child.Name == "Town")
{
child.InnerText = obj.Town;
}
if (child.Name == "Country")
{
child.InnerText = obj.Country;
}
if (child.Name == "Residence")
{
child.InnerText = obj.Residence;
}
if (child.Name == "BirthDate")
{
child.InnerText = obj.BirthDate.ToString();
}
if (child.Name == "RemainingPoints")
{
child.InnerText = obj.RemainingPoints.ToString();
}
if (child.Name == "LicenseNumber")
{
child.InnerText = obj.LicenseNumber.ToString();
}
if (child.Name == "LicenseValidity")
{
child.InnerText = obj.LicenseValidity.ToString();
}
if (child.Name == "State")
{
child.InnerText = obj.State.ToString();
}
if (child.Name == "Password")
{
child.InnerText = obj.Password;
}
}
XMLSaveDocument();
return(1);
}
}
public static int Update(Record obj)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
int ret = 0;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(SQL_UPDATE, connection);
command.Parameters.AddWithValue("@PaidDate", obj.PaidDate);
command.Parameters.AddWithValue("@ID", obj.ID);
ret = command.ExecuteNonQuery();
}
return(ret);
}
else
{
xmlDoc.Load(filePath);
XmlNode root = xmlDoc.DocumentElement;
XmlNodeList list = root.SelectNodes("Record");
XmlNode objNode = null;
foreach (XmlNode node in list)
{
XmlNodeList childs = node.ChildNodes;
foreach (XmlNode child in childs)
{
if (child.Name == "ID" && child.InnerText == obj.ID.ToString())
{
objNode = node;
Console.WriteLine(objNode.InnerXml);
break;
}
}
if (objNode != null)
{
break;
}
}
foreach (XmlNode child in objNode.ChildNodes)
{
if (child.Name == "Ammount")
{
child.InnerText = obj.Ammount.ToString();
}
if (child.Name == "PointsTaken")
{
child.InnerText = obj.PointsTaken.ToString();
}
if (child.Name == "DateOfEntry")
{
child.InnerText = obj.DateOfEntry.ToString();
}
if (child.Name == "ExpireDate")
{
child.InnerText = obj.ExpireDate.ToString();
}
if (child.Name == "PaidDate")
{
child.InnerText = obj.PaidDate.ToString();
}
if (child.Name == "driverID")
{
child.InnerText = obj.driverID.ToString();
}
if (child.Name == "employeeID")
{
child.InnerText = obj.employeeID.ToString();
}
if (child.Name == "fineTypeID")
{
child.InnerText = obj.fineTypeID.ToString();
}
}
XMLSaveDocument();
return(1);
}
}
public static int Save(Record obj)
{
if (Settings.DataProvider == DataProviderType.SqlServer)
{
int recordID = -1;
using (SqlConnection connection = new SqlConnection(DBConnector.GetBuilder().ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(SQL_INSERT, connection))
{
command.Parameters.AddWithValue("@Ammount", obj.Ammount);
command.Parameters.AddWithValue("@PointsTaken", obj.PointsTaken);
command.Parameters.AddWithValue("@DateOfEntry", obj.DateOfEntry);
command.Parameters.AddWithValue("@ExpireDate", obj.ExpireDate);
if (obj.PaidDate == null)
{
command.Parameters.AddWithValue("@PaidDate", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@PaidDate", obj.PaidDate);
}
command.Parameters.AddWithValue("@driverID", obj.driverID);
command.Parameters.AddWithValue("@employeeID", obj.employeeID);
command.Parameters.AddWithValue("@fineTypeID", obj.fineTypeID);
recordID = (int)command.ExecuteScalar();
}
}
return(recordID);
}
else
{
XmlElement root = xmlDoc.DocumentElement;
XmlElement elem = xmlDoc.CreateElement("Record");
XmlElement id = xmlDoc.CreateElement("ID");
id.InnerText = freeID.ToString();
obj.ID = freeID;
freeID = freeID + 1;
elem.AppendChild(id);
XmlElement Ammount = xmlDoc.CreateElement("Ammount");
Ammount.InnerText = obj.Ammount.ToString();
elem.AppendChild(Ammount);
XmlElement PointsTaken = xmlDoc.CreateElement("PointsTaken");
PointsTaken.InnerText = obj.PointsTaken.ToString();
elem.AppendChild(PointsTaken);
XmlElement DateOfEntry = xmlDoc.CreateElement("DateOfEntry");
DateOfEntry.InnerText = obj.DateOfEntry.ToString();
elem.AppendChild(DateOfEntry);
XmlElement ExpireDate = xmlDoc.CreateElement("ExpireDate");
ExpireDate.InnerText = obj.ExpireDate.ToString();
elem.AppendChild(ExpireDate);
XmlElement PaidDate = xmlDoc.CreateElement("PaidDate");
PaidDate.InnerText = obj.PaidDate.ToString();
elem.AppendChild(PaidDate);
XmlElement driverID = xmlDoc.CreateElement("driverID");
driverID.InnerText = obj.driverID.ToString();
elem.AppendChild(driverID);
XmlElement employeeID = xmlDoc.CreateElement("employeeID");
employeeID.InnerText = obj.employeeID.ToString();
elem.AppendChild(employeeID);
XmlElement fineTypeID = xmlDoc.CreateElement("fineTypeID");
fineTypeID.InnerText = obj.fineTypeID.ToString();
elem.AppendChild(fineTypeID);
root.AppendChild(elem);
XMLSaveDocument();
return(1);
}
}
