public DriverLicenseTypes getDriverLicenseType(int driverID, string licenseORCertification)
{
#region DB functions
string query = "";
if (licenseORCertification == "רישיון")
{
query = "select * from DriverLicenseTypes dlt inner join Drivers d on dlt.DriverLicenseTypeID = d.DriverLicenseID where d.DriverID =" + driverID + "";
}
else
{
query = "select * from DriverLicenseTypes dlt inner join Drivers d on dlt.DriverLicenseTypeID = d.DriverCertificationID where d.DriverID =" + driverID + "";
}
DriverLicenseTypes dlt = new DriverLicenseTypes();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
dlt.DriverLicenseTypeName = dr["DriverLicenseTypeName"].ToString();
dlt.DriverLicenseTypeDescription = dr["DriverLicenseDescription"].ToString();
dlt.LicenseOrCertification = dr["PermissionORCertification"].ToString();
}
#endregion
return(dlt);
}
public string getDriverLicenseTypes(string LicenseORCertification)
{
JavaScriptSerializer j = new JavaScriptSerializer();
DriverLicenseTypes dlt = new DriverLicenseTypes();
List <DriverLicenseTypes> driverLicenseTypesList = dlt.getDriverLicenseTypesList(LicenseORCertification);
return(j.Serialize(driverLicenseTypesList));
}
public string getDriverLicenseTypesDDL()
{
JavaScriptSerializer j = new JavaScriptSerializer();
DriverLicenseTypes dlt = new DriverLicenseTypes();
List <DriverLicenseTypes> driverLicenseTypesList = dlt.getDriverLicenseTypesList();
return(j.Serialize(driverLicenseTypesList));
}
public Drivers getDriver()
{
#region DB functions
string query = "select * from Drivers d inner join City c on d.CityID = c.CityID inner join Trucks t on d.TruckID=t.TruckID inner join TruckTypes tp on t.TruckTypeID = tp.TruckTypeID where d.DriverID = " + DriverID + "";
Drivers d = new Drivers();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
d.DriverID = (int)dr["DriverID"];
d.DriverNumber = dr["DriverNumber"].ToString();
d.FirstName = dr["FirstName"].ToString();
d.LastName = dr["LastName"].ToString();
d.Phone = dr["Phone"].ToString();
d.Active = dr["Active"].ToString();
d.Email = dr["Email"].ToString();
d.AccountID = dr["AccountID"].ToString();
d.AppPassword = dr["AppPassword"].ToString();
d.CityLiving = new City((int)dr["CityID"], dr["City"].ToString(), dr["Area"].ToString(), (int)dr["Zone"]);
d.DriverTruck = new Trucks((int)dr["TruckID"], (int)dr["TruckLicense"], dr["Manufacturer"].ToString(), dr["Model"].ToString(), dr["KMToDate"].ToString(), dr["Hand"].ToString(), float.Parse(dr["PurchaseCost"].ToString()), dr["PurchaseYear"].ToString(), new TruckTypes((int)dr["TruckTypeID"], dr["TruckType"].ToString()), dr["Urea"].ToString(), (DateTime)dr["OnRoadDate"], (DateTime)dr["InsuranceExpiredDate"]);
try
{
d.DateOfBirth = (DateTime)dr["DateOfBirth"];
}
catch
{
d.DateOfBirth = new DateTime(1900, 01, 01);
}
try
{
d.DriverLicenseExpiredDate = (DateTime)dr["DriverLicenseExpiredDate"];
}
catch
{
d.DriverLicenseExpiredDate = new DateTime(1900, 01, 01);
}
try
{
d.DriverCertificationExpiredDate = (DateTime)dr["DriverCertificationExpiredDate"];
}
catch
{
d.DriverCertificationExpiredDate = new DateTime(1900, 01, 01);
}
DriverLicenseTypes dlt = new DriverLicenseTypes();
d.CertificationType = dlt.getDriverLicenseType(d.DriverID, "היתר");
d.LicenseType = dlt.getDriverLicenseType(d.DriverID, "רישיון");
}
#endregion
return(d);
}
public Orders getOrder()
{
#region DB functions
string query = "select * from Orders o inner join OrderStatus os on o.OrderStatusID=os.StatusID where OrderID =" + OrderID + "";
Orders o = new Orders();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
o.OrderID = (int)dr["OrderID"];
o.OrderName = dr["OrderName"].ToString();
o.OrderDate = (DateTime)dr["OrderDate"];
o.OrderStatus = new OrderStatus((int)dr["OrderStatusID"], dr["Status"].ToString());
o.Comments = dr["Comments"].ToString();
o.TotalPrice = Convert.ToSingle(dr["TotalPrice"]);
o.AddTime = Convert.ToSingle(dr["AddTime"]);
o.Container = dr["Container"].ToString();
o.DeliveryDuration = Convert.ToSingle(dr["DeliveryDuration"]);
Customers c = new Customers();
c.CustomerID = (int)dr["CustomerID"];
o.Customer = c.getCustomer();
Services s = new Services();
s.ServiceID = (int)dr["ServiceID"];
o.OrderService = s.getService();
Addresses sf = new Addresses();
sf.AddressID = (int)dr["ShipFromID"];
o.ShipFrom = sf.getAddress();
Addresses st = new Addresses();
st.AddressID = (int)dr["ShipToID"];
o.ShipTo = st.getAddress();
DriverLicenseTypes dlt = new DriverLicenseTypes();
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
o.OrderLicenseType = dlt.getDriverLicenseType();
DriverLicenseTypes dct = new DriverLicenseTypes();
dct.DriverLicenseTypeID = (int)dr["DriverCertificationTypeID"];
o.OrderCertificationType = dct.getDriverLicenseType();
//DriverLicenseTypes dlt = new DriverLicenseTypes();
//o.OrderCertificationType = dlt.getDriverLicenseType(o.OrderCertificationType.DriverLicenseTypeID, "היתר");
//o.OrderLicenseType = dlt.getDriverLicenseType(o.OrderLicenseType.DriverLicenseTypeID, "רישיון");
}
#endregion
return(o);
}
public void setOrder(int orderID, string orderName, int customerID, string orderDate, int orderStatusID, string comments, int orderServiceID, int shipFromID, int shipToID, float totalPrice, float addTime, string container, float deliveryDuration, int orderLicenseTypeID, int orderCertificationTypeID, string func)
{
//, int inShift, int shiftSort, int truckID, int driverID,
DateTime dt;
if (orderDate.Contains("."))
{
dt = DateTime.Parse(orderDate);
}
else
{
dt = DateTime.ParseExact(orderDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
Customers customer = new Customers();
customer.CustomerID = customerID;
OrderStatus orderStatus = new OrderStatus();
orderStatus.OrderStatusID = orderStatusID;
Services orderService = new Services();
orderService.ServiceID = orderServiceID;
Addresses shipFrom = new Addresses();
shipFrom.AddressID = shipFromID;
Addresses shipTo = new Addresses();
shipTo.AddressID = shipToID;
DriverLicenseTypes orderLicenseType = new DriverLicenseTypes();
orderLicenseType.DriverLicenseTypeID = orderLicenseTypeID;
DriverLicenseTypes orderCertificationType = new DriverLicenseTypes();
orderCertificationType.DriverLicenseTypeID = orderCertificationTypeID;
//Drivers noDriver = new Drivers();
//noDriver.DriverID = driverID;
//Trucks noTruck = new Trucks();
//noTruck.TruckID = truckID;
//, inShift, shiftSort, noTruck, noDriver
Orders o = new Orders(orderID, orderName, customer, dt, orderStatus, comments, orderService, shipFrom, shipTo, totalPrice, addTime, container, deliveryDuration, orderLicenseType, orderCertificationType);
o.setOrder(func);
}
public List <DriverLicenseTypes> getDriverLicenseTypesList()
{
#region DB functions
string query = "select * from DriverLicenseTypes";
List <DriverLicenseTypes> list = new List <DriverLicenseTypes>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
DriverLicenseTypes tmp = new DriverLicenseTypes((int)dr["DriverLicenseTypeID"], dr["DriverLicenseTypeName"].ToString(), dr["DriverLicenseDescription"].ToString(), dr["PermissionORCertification"].ToString());
list.Add(tmp);
}
#endregion
return(list);
}
public Orders(int orderID, string orderName, Customers customer, DateTime orderDate, OrderStatus orderStatus, string comments, Services orderService, Addresses shipFrom, Addresses shipTo, float totalPrice, float addTime, string container, float deliveryDuration, DriverLicenseTypes orderLicenseType, DriverLicenseTypes orderCertificationType)
{
OrderID = orderID;
OrderName = orderName;
Customer = customer;
OrderDate = orderDate;
OrderStatus = orderStatus;
Comments = comments;
OrderService = orderService;
ShipFrom = shipFrom;
ShipTo = shipTo;
TotalPrice = totalPrice;
AddTime = addTime;
Container = container;
OrderLicenseType = orderLicenseType;
OrderCertificationType = orderCertificationType;
DeliveryDuration = deliveryDuration;
}
public Drivers(int driverID, string driverNumber, string firstName, string lastName, string phone, string email, string accountID, string appPassword, DateTime dateOfBirth, DateTime driverLicenseExpiredDate, DateTime driverCertificationExpiredDate, City cityLiving, DriverLicenseTypes licenseType, DriverLicenseTypes certificationType, Trucks driverTruck)
{
DriverID = driverID;
DriverNumber = driverNumber;
FirstName = firstName;
LastName = lastName;
Phone = phone;
Email = email;
AccountID = accountID;
AppPassword = appPassword;
CityLiving = cityLiving;
LicenseType = licenseType;
CertificationType = certificationType;
DriverTruck = driverTruck;
DateOfBirth = dateOfBirth;
DriverLicenseExpiredDate = driverLicenseExpiredDate;
DriverCertificationExpiredDate = driverCertificationExpiredDate;
}
public List <Drivers> getDriversList(bool active)
{
#region DB functions
string query = "select * from Drivers d inner join City c on d.CityID = c.CityID inner join Trucks t on d.TruckID=t.TruckID inner join TruckTypes tp on t.TruckTypeID = tp.TruckTypeID where d.FirstName != 'בחר'";
if (active)
{
query += " and d.Active = 'Y'";
}
query += " order by FirstName";
List <Drivers> list = new List <Drivers>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Drivers tmp = new Drivers();
tmp.DriverID = (int)dr["DriverID"];
tmp.DriverNumber = dr["DriverNumber"].ToString();
tmp.FirstName = dr["FirstName"].ToString();
tmp.LastName = dr["LastName"].ToString();
tmp.Phone = dr["Phone"].ToString();
tmp.Active = dr["Active"].ToString();
tmp.Email = dr["Email"].ToString();
tmp.AccountID = dr["AccountID"].ToString();
tmp.AppPassword = dr["AppPassword"].ToString();
tmp.CityLiving = new City((int)dr["CityID"], dr["City"].ToString(), dr["Area"].ToString(), (int)dr["Zone"]);
tmp.DriverTruck = new Trucks((int)dr["TruckID"], (int)dr["TruckLicense"], dr["Manufacturer"].ToString(), dr["Model"].ToString(), dr["KMToDate"].ToString(), dr["Hand"].ToString(), float.Parse(dr["PurchaseCost"].ToString()), dr["PurchaseYear"].ToString(), new TruckTypes((int)dr["TruckTypeID"], dr["TruckType"].ToString()), dr["Urea"].ToString(), (DateTime)dr["OnRoadDate"], (DateTime)dr["InsuranceExpiredDate"]);
DriverLicenseTypes dlt = new DriverLicenseTypes();
tmp.CertificationType = dlt.getDriverLicenseType(tmp.DriverID, "היתר");
tmp.LicenseType = dlt.getDriverLicenseType(tmp.DriverID, "רישיון");
list.Add(tmp);
}
#endregion
return(list);
}
public DriverLicenseTypes getDriverLicenseType()
{
#region DB functions
string query = "";
query = "select * from DriverLicenseTypes dlt where dlt.DriverLicenseTypeID =" + driverLicenseTypeID + "";
DriverLicenseTypes dlt = new DriverLicenseTypes();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
dlt.DriverLicenseTypeName = dr["DriverLicenseTypeName"].ToString();
dlt.DriverLicenseTypeDescription = dr["DriverLicenseDescription"].ToString();
dlt.LicenseOrCertification = dr["PermissionORCertification"].ToString();
}
#endregion
return(dlt);
}
public Orders(int orderID, string orderName, Customers customer, DateTime orderDate, OrderStatus orderStatus, string comments, Services orderService, Addresses shipFrom, Addresses shipTo, float totalPrice, float addTime, string container, float deliveryDuration, DriverLicenseTypes orderLicenseType, DriverLicenseTypes orderCertificationType, string inShift, int shiftSort, Trucks truck, string active, Drivers driver)
{
OrderID = orderID;
OrderName = orderName;
Customer = customer;
OrderDate = orderDate;
OrderStatus = orderStatus;
Comments = comments;
OrderService = orderService;
ShipFrom = shipFrom;
ShipTo = shipTo;
TotalPrice = totalPrice;
AddTime = addTime;
Container = container;
OrderLicenseType = orderLicenseType;
OrderCertificationType = orderCertificationType;
InShift = inShift;
ShiftSort = shiftSort;
Truck = truck;
Active = active;
Driver = driver;
DeliveryDuration = deliveryDuration;
}
public List <Orders> getOrdersList(bool active, int selectedOrdersStatus, DateTime startDate, DateTime endDate)
{
#region DB functions
string sqlStartDate = startDate.ToString("yyyy-MM-dd");
string sqlEndtDate = endDate.ToString("yyyy-MM-dd");
string query = "select * from Orders o inner join OrderStatus os on o.OrderStatusID=os.StatusID";
if (active)
{
query += " where os.Status != 'מבוטלת'";
}
if (selectedOrdersStatus != -1)
{
query += " and os.StatusID =" + selectedOrdersStatus;
}
if (startDate.Year != 1)
{
query += " and o.OrderDate >= '" + sqlStartDate + "'";
if (endDate.Year != 1)
{
query += " and o.OrderDate <= '" + sqlEndtDate + "'";
}
}
List <Orders> list = new List <Orders>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Orders tmp = new Orders();
tmp.OrderID = (int)dr["OrderID"];
tmp.OrderName = dr["OrderName"].ToString();
tmp.OrderDate = (DateTime)dr["OrderDate"];
tmp.OrderStatus = new OrderStatus((int)dr["OrderStatusID"], dr["Status"].ToString());
tmp.Comments = dr["Comments"].ToString();
tmp.TotalPrice = Convert.ToSingle(dr["TotalPrice"]);
tmp.AddTime = Convert.ToSingle(dr["AddTime"]);
tmp.Container = dr["Container"].ToString();
tmp.DeliveryDuration = Convert.ToSingle(dr["DeliveryDuration"]);
Customers c = new Customers();
c.CustomerID = (int)dr["CustomerID"];
tmp.Customer = c.getCustomer();
Services s = new Services();
s.ServiceID = (int)dr["ServiceID"];
tmp.OrderService = s.getService();
Addresses sf = new Addresses();
sf.AddressID = (int)dr["ShipFromID"];
tmp.ShipFrom = sf.getAddress();
Addresses st = new Addresses();
st.AddressID = (int)dr["ShipToID"];
tmp.ShipTo = st.getAddress();
DriverLicenseTypes dlt = new DriverLicenseTypes();
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
tmp.OrderLicenseType = dlt.getDriverLicenseType();
DriverLicenseTypes dct = new DriverLicenseTypes();
dct.DriverLicenseTypeID = (int)dr["DriverCertificationTypeID"];
tmp.OrderCertificationType = dct.getDriverLicenseType();
list.Add(tmp);
}
#endregion
return(list);
}
public List <Orders> getOrdersForShiftsList(bool active, int selectedOrdersStatus, DateTime startDate, DateTime endDate, int selectedCustomer, int selectedService)
{
#region DB functions
string sqlStartDate = startDate.ToString("yyyy-MM-dd");
string sqlEndtDate = endDate.ToString("yyyy-MM-dd");
string query = "select o.OrderID, o.OrderName, o.AddTime,o.DeliveryDuration, o.Container, o.InShift, o.ShiftSort, o.DriverID, o.TruckID, o.OrderDate, o.OrderStatusID, os.Status, o.Comments, o.CustomerID, c.CustomerName, o.ServiceID, s.Service, o.TotalPrice,o.ShipFromID,orig.AddressName 'ShipFrom',origc.Zone 'ShipFromZone',o.ShipToID,dest.AddressName 'ShipTo',destc.Zone 'ShipToZone',o.DriverLicenseTypeID,dl.DriverLicenseTypeName as 'dlName',o.DriverCertificationTypeID,dc.DriverLicenseTypeName as 'dcName' ,dl.DriverLicenseDescription as 'dlDesc',dc.DriverLicenseDescription as 'dcDesc' ,c.PreferedDriverID from Orders o inner join OrderStatus os on o.OrderStatusID=os.StatusID inner join Customers c on c.CustomerID=o.CustomerID inner join Services s on s.ServiceID=o.ServiceID inner join Addresses orig on orig.AddressID = o.ShipFromID inner join City origc on orig.CityID = origc.CityID inner join Addresses dest on dest.AddressID = o.ShipToID inner join City destc on orig.CityID = destc.CityID inner join DriverLicenseTypes dl on o.DriverLicenseTypeID = dl.DriverLicenseTypeID inner join DriverLicenseTypes dc on o.DriverCertificationTypeID = dc.DriverLicenseTypeID inner join Drivers pd on pd.DriverID = c.PreferedDriverID where 1=1";
if (active)
{
query += " and os.Status != 'מבוטלת'";
}
if (selectedOrdersStatus != -1)
{
query += " and os.StatusID =" + selectedOrdersStatus;
}
if (selectedCustomer != -1)
{
query += " and o.CustomerID =" + selectedCustomer;
}
if (selectedService != -1)
{
query += " and o.ServiceID =" + selectedService;
}
if (startDate.Year != 1)
{
query += " and o.OrderDate >= '" + sqlStartDate + "'";
if (endDate.Year != 1)
{
query += " and o.OrderDate <= '" + sqlEndtDate + "'";
}
}
//query += "and o.OrderDate = '2017-06-01'";
List <Orders> list = new List <Orders>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Orders tmp = new Orders();
tmp.OrderID = (int)dr["OrderID"];
tmp.OrderName = dr["OrderName"].ToString();
tmp.OrderDate = (DateTime)dr["OrderDate"];
tmp.OrderStatus = new OrderStatus((int)dr["OrderStatusID"], dr["Status"].ToString());
tmp.Comments = dr["Comments"].ToString();
tmp.AddTime = Convert.ToSingle(dr["AddTime"]);
tmp.DeliveryDuration = Convert.ToSingle(dr["DeliveryDuration"]);
tmp.Container = dr["Container"].ToString();
tmp.InShift = dr["InShift"].ToString();
if (tmp.InShift == "Y")
{
tmp.ShiftSort = (int)dr["ShiftSort"];
Drivers d = new Drivers();
d.DriverID = (int)dr["DriverID"];
tmp.Driver = d;
Trucks t = new Trucks();
t.TruckID = (int)dr["TruckID"];
tmp.Truck = t;
}
Customers c = new Customers();
c.CustomerName = dr["CustomerName"].ToString();
c.CustomerID = (int)dr["CustomerID"];
Drivers pd = new Drivers();
pd.DriverID = (int)dr["PreferedDriverID"];
c.PreferedDrivers = pd;
tmp.Customer = c;
Services s = new Services();
s.Service = dr["Service"].ToString();
s.ServiceID = (int)dr["ServiceID"];
tmp.OrderService = s;
Addresses sf = new Addresses();
sf.AddressID = (int)dr["ShipFromID"];
sf.AddressName = dr["ShipFrom"].ToString();
City csf = new City();
csf.Zone = (int)dr["ShipFromZone"];
sf.CustomerCity = csf;
tmp.ShipFrom = sf;
Addresses st = new Addresses();
st.AddressID = (int)dr["ShipToID"];
st.AddressName = dr["ShipTo"].ToString();
City cst = new City();
cst.Zone = (int)dr["ShipToZone"];
st.CustomerCity = cst;
tmp.ShipTo = st;
DriverLicenseTypes dl = new DriverLicenseTypes();
dl.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
dl.DriverLicenseTypeName = dr["dlName"].ToString();
dl.DriverLicenseTypeDescription = dr["dlDesc"].ToString();
tmp.OrderLicenseType = dl;
DriverLicenseTypes dc = new DriverLicenseTypes();
dc.DriverLicenseTypeID = (int)dr["DriverCertificationTypeID"];
dc.DriverLicenseTypeName = dr["dcName"].ToString();
dc.DriverLicenseTypeDescription = dr["dcDesc"].ToString();
tmp.OrderCertificationType = dl;
tmp.TotalPrice = Convert.ToSingle(dr["TotalPrice"]);
list.Add(tmp);
}
#endregion
return(list);
}
public List <Drivers> getAvailableDriversList(DateTime selecetdDate)
{
#region DB functions
string sqlDate = selecetdDate.ToString("yyyy-MM-dd");
string query = "select * "
+ "from Drivers d left join "
+ "(select * "
+ "from DriverConstraints "
+ "where Date ='" + sqlDate + "' "
+ "and Active = 'Y') as dc on d.DriverID = dc.DriverID "
+ "inner join City c on d.CityID = c.CityID inner join Trucks t on d.TruckID=t.TruckID inner join TruckTypes tp on t.TruckTypeID = tp.TruckTypeID "
+ "where d.Active = 'Y' "
+ "and dc.Date is null "
+ "and d.FirstName != 'בחר'";
List <Drivers> list = new List <Drivers>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Drivers tmp = new Drivers();
tmp.DriverID = (int)dr["DriverID"];
tmp.DriverNumber = dr["DriverNumber"].ToString();
tmp.FirstName = dr["FirstName"].ToString();
tmp.LastName = dr["LastName"].ToString();
tmp.Phone = dr["Phone"].ToString();
tmp.Active = dr["Active"].ToString();
tmp.Email = dr["Email"].ToString();
tmp.AccountID = dr["AccountID"].ToString();
tmp.AppPassword = dr["AppPassword"].ToString();
tmp.CityLiving = new City((int)dr["CityID"], dr["City"].ToString(), dr["Area"].ToString(), (int)dr["Zone"]);
tmp.DriverTruck = new Trucks((int)dr["TruckID"], (int)dr["TruckLicense"], dr["Manufacturer"].ToString(), dr["Model"].ToString(), dr["KMToDate"].ToString(), dr["Hand"].ToString(), float.Parse(dr["PurchaseCost"].ToString()), dr["PurchaseYear"].ToString(), new TruckTypes((int)dr["TruckTypeID"], dr["TruckType"].ToString()), dr["Urea"].ToString(), (DateTime)dr["OnRoadDate"], (DateTime)dr["InsuranceExpiredDate"]);
try
{
tmp.DateOfBirth = (DateTime)dr["DateOfBirth"];
}
catch
{
tmp.DateOfBirth = new DateTime(1900, 01, 01);
}
try
{
tmp.DriverLicenseExpiredDate = (DateTime)dr["DriverLicenseExpiredDate"];
}
catch
{
tmp.DriverLicenseExpiredDate = new DateTime(1900, 01, 01);
}
try
{
tmp.DriverCertificationExpiredDate = (DateTime)dr["DriverCertificationExpiredDate"];
}
catch
{
tmp.DriverCertificationExpiredDate = new DateTime(1900, 01, 01);
}
DriverLicenseTypes dlt = new DriverLicenseTypes();
tmp.CertificationType = dlt.getDriverLicenseType(tmp.DriverID, "היתר");
tmp.LicenseType = dlt.getDriverLicenseType(tmp.DriverID, "רישיון");
list.Add(tmp);
}
#endregion
return(list);
}
public List <Orders> getDriverOrdersList(int driverID, int func)
{
#region DB functions
string query = "select *"
+ " from Orders o"
+ " inner join OrderStatus os on o.OrderStatusID=os.StatusID"
+ " where 1=1"
+ " and o.InShift='Y'"
+ " and o.DriverID=" + driverID;
#region selecting Period
if (func == 1)
{
query += " and o.OrderDate = convert(date, GETDATE())";
}
if (func == 2)
{
query += " and o.OrderDate = convert(date,DATEADD(day, 1, GETDATE()))";
}
if (func == 3)
{
query += " and o.OrderDate >= convert(date, DATEADD(day, -7, GETDATE())) and o.OrderDate <= convert(date, GETDATE()) ";
}
if (func == 4)
{
query += " and o.OrderDate >= convert(date, DATEADD(day, -30, GETDATE())) and o.OrderDate <= convert(date, GETDATE()) ";
}
#endregion
query += " order by o.OrderDate desc, o.ShiftSort asc";
List <Orders> list = new List <Orders>();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Orders tmp = new Orders();
tmp.OrderID = (int)dr["OrderID"];
tmp.OrderName = dr["OrderName"].ToString();
tmp.OrderDate = (DateTime)dr["OrderDate"];
tmp.OrderStatus = new OrderStatus((int)dr["OrderStatusID"], dr["Status"].ToString());
tmp.Comments = dr["Comments"].ToString();
tmp.TotalPrice = Convert.ToSingle(dr["TotalPrice"]);
tmp.AddTime = Convert.ToSingle(dr["AddTime"]);
tmp.Container = dr["Container"].ToString();
tmp.InShift = dr["InShift"].ToString();
tmp.DeliveryDuration = Convert.ToSingle(dr["DeliveryDuration"]);
if (tmp.InShift == "Y")
{
tmp.ShiftSort = (int)dr["ShiftSort"];
Drivers d = new Drivers();
d.DriverID = (int)dr["DriverID"];
tmp.Driver = d.getDriver();
Trucks t = new Trucks();
t.TruckID = (int)dr["TruckID"];
tmp.Truck = t.getTruck();
}
Customers c = new Customers();
c.CustomerID = (int)dr["CustomerID"];
tmp.Customer = c.getCustomer();
Services s = new Services();
s.ServiceID = (int)dr["ServiceID"];
tmp.OrderService = s.getService();
Addresses sf = new Addresses();
sf.AddressID = (int)dr["ShipFromID"];
tmp.ShipFrom = sf.getAddress();
Addresses st = new Addresses();
st.AddressID = (int)dr["ShipToID"];
tmp.ShipTo = st.getAddress();
DriverLicenseTypes dlt = new DriverLicenseTypes();
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
tmp.OrderLicenseType = dlt.getDriverLicenseType();
DriverLicenseTypes dct = new DriverLicenseTypes();
dct.DriverLicenseTypeID = (int)dr["DriverCertificationTypeID"];
tmp.OrderCertificationType = dct.getDriverLicenseType();
list.Add(tmp);
}
#endregion
return(list);
}
public Orders getOrderLean()
{
#region DB functions
string query = "select o.OrderID, o.OrderName, o.OrderDate, o.OrderStatusID, os.Status, o.Comments, o.TotalPrice, o.AddTime, o.Container, o.DeliveryDuration, o.CustomerID, c.CustomerName, o.ServiceID, s.Service, o.ShipFromID, orig.AddressName as 'Origin', o.ShipToID, dest.AddressName as 'Destination', o.DriverLicenseTypeID, lic.DriverLicenseTypeName as 'License', lic.DriverLicenseDescription as 'LicenseDescription', o.DriverCertificationTypeID, cert.DriverLicenseTypeName as 'Certification', cert.DriverLicenseDescription as 'CertificationDescription' from Orders o inner join OrderStatus os on o.OrderStatusID=os.StatusID inner join Customers c on o.CustomerID = c.CustomerID inner join Services s on o.ServiceID = s.ServiceID inner join Addresses orig on o.ShipFromID = orig.AddressID inner join Addresses dest on o.ShipToID = dest.AddressID inner join DriverLicenseTypes lic on o.DriverLicenseTypeID = lic.DriverLicenseTypeID inner join DriverLicenseTypes cert on o.DriverCertificationTypeID = cert.DriverLicenseTypeID where OrderID =" + OrderID + "";
Orders o = new Orders();
DbService db = new DbService();
DataSet ds = db.GetDataSetByQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
o.OrderID = (int)dr["OrderID"];
o.OrderName = dr["OrderName"].ToString();
o.OrderDate = (DateTime)dr["OrderDate"];
o.OrderStatus = new OrderStatus((int)dr["OrderStatusID"], dr["Status"].ToString());
o.Comments = dr["Comments"].ToString();
o.TotalPrice = Convert.ToSingle(dr["TotalPrice"]);
o.AddTime = Convert.ToSingle(dr["AddTime"]);
o.Container = dr["Container"].ToString();
try { o.DeliveryDuration = Convert.ToSingle(dr["DeliveryDuration"]); }
catch { o.DeliveryDuration = 0; }
Customers c = new Customers();
c.CustomerID = (int)dr["CustomerID"];
c.CustomerName = dr["CustomerName"].ToString();
o.Customer = c;
Services s = new Services();
s.ServiceID = (int)dr["ServiceID"];
s.Service = dr["Service"].ToString();
o.OrderService = s;
Addresses sf = new Addresses();
sf.AddressID = (int)dr["ShipFromID"];
sf.AddressName = dr["Origin"].ToString();
o.ShipFrom = sf;
Addresses st = new Addresses();
st.AddressID = (int)dr["ShipToID"];
st.AddressName = dr["Destination"].ToString();
o.ShipTo = st;
DriverLicenseTypes dlt = new DriverLicenseTypes();
dlt.DriverLicenseTypeID = (int)dr["DriverLicenseTypeID"];
dlt.DriverLicenseTypeName = dr["License"].ToString();
dlt.DriverLicenseTypeDescription = dr["LicenseDescription"].ToString();
o.OrderLicenseType = dlt;
DriverLicenseTypes dct = new DriverLicenseTypes();
dct.DriverLicenseTypeID = (int)dr["DriverCertificationTypeID"];
dct.DriverLicenseTypeName = dr["Certification"].ToString();
dct.DriverLicenseTypeDescription = dr["CertificationDescription"].ToString();
o.OrderCertificationType = dct;
//DriverLicenseTypes dlt = new DriverLicenseTypes();
//o.OrderCertificationType = dlt.getDriverLicenseType(o.OrderCertificationType.DriverLicenseTypeID, "היתר");
//o.OrderLicenseType = dlt.getDriverLicenseType(o.OrderLicenseType.DriverLicenseTypeID, "רישיון");
}
#endregion
return(o);
}