//public DriverTrackerDL()
//{
// try
// {
// con = new OracleConnection(Constants.connectionstring);
// con.Open();
// Console.WriteLine("Connected to Oracle Database {0}", con.ServerVersion);
// }
// catch (Exception e)
// {
// Console.WriteLine("Error on Connection");
// }
//}
//~DriverTrackerDL()
//{
// con.Close();
// con.Dispose();
// Console.WriteLine("Connected to Oracle Database {0} is closed now", con.ServerVersion);
//}
public DriverTracker Add(DriverTracker drivertrack)
{
try
{
con = new OracleConnection(Constants.connectionstring);
con.Open();
string guid = Convert.ToString(Guid.NewGuid());
string SQLStatement = "Insert into DriverTracker values('" + guid + "', " + drivertrack.weeknumber + ",'" + drivertrack.driverid + "','" + drivertrack.scheduleid + "'," + drivertrack.shiftid + "," + drivertrack.totaltrip + "," + drivertrack.daytrip + "," + drivertrack.nighttrip + ",'" + drivertrack.weekday + "',sysdate)";
//Console.WriteLine(SQLStatement);
if (con.State.ToString().Equals("Open"))
{
OracleCommand sqlCommandOracle = new OracleCommand(SQLStatement, con);
sqlCommandOracle.ExecuteScalar();
}
return(Alone("TRACK_ID", guid));
}
catch (Exception e)
{
return(null);
}
finally
{
con.Close();
}
}
/// <summary>
/// To modify driver tracker
/// </summary>
public DriverTracker EditDriverTracker(DriverTracker objdrivertracker)
{
try
{
return(drivertrackerdl.Amend(objdrivertracker));
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// Save driver tracker information
/// </summary>
public DriverTracker SaveDriverTracker(DriverTracker objdrivertracker)
{
try
{
return(drivertrackerdl.Add(objdrivertracker));
}
catch (Exception e)
{
return(null);
}
}
public List <DriverTracker> All(string property = null, int value = 0)
{
try
{
con = new OracleConnection(Constants.connectionstring);
con.Open();
List <DriverTracker> listdriver = new List <DriverTracker>();
string SQLStatement = "Select * from DriverTracker";
if (property != null)
{
SQLStatement += " where " + property + " =" + value;
}
if (con.State.ToString().Equals("Open"))
{
OracleCommand sqlCommandOracle = new OracleCommand(SQLStatement, con);
OracleDataReader reader = sqlCommandOracle.ExecuteReader();
while (reader.Read())
{
DriverTracker objdrivertracker = new DriverTracker();
objdrivertracker.trackid = Convert.ToString(reader["TRACK_ID"]);
objdrivertracker.weeknumber = Convert.ToInt16(reader["WEEK_NUMBER"]);
objdrivertracker.driverid = Convert.ToString(reader["DRIVER_ID"]);
objdrivertracker.scheduleid = Convert.ToString(reader["SCHEDULE_ID"]);
objdrivertracker.totaltrip = Convert.ToInt16(reader["TOTALTRIP"]);
objdrivertracker.daytrip = Convert.ToInt16(reader["DAYTRIP"]);
objdrivertracker.nighttrip = Convert.ToInt16(reader["NIGHTTRIP"]);
objdrivertracker.weekday = Convert.ToString(reader["WEEK_DAY"]);
listdriver.Add(objdrivertracker);
}
}
return(listdriver);
}
catch (Exception e)
{
return(null);
}
finally
{
con.Close();
}
}
public DriverTracker Amend(DriverTracker drivertrack)
{
try
{
con = new OracleConnection(Constants.connectionstring);
con.Open();
string SQLStatement = "Update DriverTracker SET ";
SQLStatement += " WEEK_NUMBBER =" + drivertrack.weeknumber;
SQLStatement += " DRIVER_ID =" + drivertrack.driverid;
SQLStatement += " SCHEDULE_ID =" + drivertrack.scheduleid;
SQLStatement += " SHIFT_ID =" + drivertrack.shiftid;
SQLStatement += " TOTALTRIP = " + drivertrack.totaltrip;
SQLStatement += " DAYTRIP =" + drivertrack.daytrip;
SQLStatement += " NIGHTTRIP =" + drivertrack.nighttrip;
SQLStatement += " WEEK_DAY =" + drivertrack.weekday;
SQLStatement += " CREATE_DATE = sysdate";
SQLStatement += " WHERE ID=" + drivertrack.trackid;
//Console.WriteLine(SQLStatement);
if (con.State.ToString().Equals("Open"))
{
OracleCommand sqlCommandOracle = new OracleCommand(SQLStatement, con);
sqlCommandOracle.ExecuteScalar();
}
return(drivertrack);
}
catch (Exception e)
{
return(null);
}
finally
{
con.Close();
}
}
/// <summary>
/// Create schedule for current week
/// </summary>
/// <param name="lstdriver"> List of all drivers</param>
/// <param name="routeid"> Selected root </param>
/// <returns></returns>
public List <Schedule> CreateSchedule(List <Driver> lstdriver, string routeid)
{
try
{
int initialvalue = 0;
Schedule objschedule = new Schedule();
List <Schedule> lstschedule = new List <Schedule>();
List <Shift> lstshift = new List <Shift>();
List <DriverTracker> lstdrivertrackers = new List <DriverTracker>();
List <DriverTracker> alldrivertrackers = new List <DriverTracker>();
List <Availability> availability = new List <Availability>();
lstshift = shiftbl.GetAllShift();
// DriverTracker : All inforamtion for that driver like driver route, driver shift, etc;
alldrivertrackers = drivertrackerbl.GetAllDriverTrackers("WEEK_NUMBBER", Constants.week);
//If user selected auto option at that time we assign driver and availability randomly.
if (lstdriver == null || lstdriver.Count < 1)
{
lstdriver = driverbl.SelectDriverAuto(routeid);
if (lstdriver == null || lstdriver.Count < 1)
{
Console.WriteLine("There are no enough driver to assign for this route");
return(null);
}
foreach (Driver drv in lstdriver)
{
if (!availabilitybl.SaveAvailabilityAuto(drv.empid))
{
Console.WriteLine("Error while inserting availabbility");
}
}
}
//Get availability for each driver for each day so we can assign shift according to that
availability = availabilitybl.GetAllAvailabilities("WEEK_NUMBER", Constants.week);
// Create Schedule and use this schedule id in drivertracker table
string scheduleid = Convert.ToString(Guid.NewGuid());
objschedule.scheduleid = scheduleid;
objschedule.week = Constants.week;
scheduledl.Add(objschedule);
//Assign driver for current week on their availability
foreach (Driver driver in lstdriver)
{
foreach (Shift shift in lstshift.FindAll(x => x.shiftid < 10)) // we need to assign first shift and second shift only
{
for (int i = initialvalue; i < Constants.days.Length; i++)
{
if (Array.IndexOf(Constants.holidays, Constants.days[i]) < 0) // To skip holiday
{
// If we have already asign that driver and want to modify their shift
DriverTracker drivertracker = (alldrivertrackers != null)? alldrivertrackers.Find(x => x.driverid == driver.driverid &&
x.shiftid == shift.shiftid &&
x.weekday == Constants.days[i]) : null;
// Get driver availability for that day
Availability avail = new Availability();
avail = availability.Find(x => x.weekday == Constants.days[i] && x.driverid == driver.driverid);
/* A driver cannot work more than one shift in a day.
* The schedules should be randomly generated to ensure fairness.
* A driver can only work a maximum of two shifts per week.
*/
if (!(lstdrivertrackers.Exists(x => x.weeknumber == Constants.week && x.weekday == Constants.days[i] && x.shiftid == shift.shiftid)) &&
(avail.driveravailabbility == lstshift.Find(x => x.shift.ToUpper() == "ALL").shiftid ||
avail.driveravailabbility == shift.shiftid) &&
!(lstdrivertrackers.Exists(x => x.driverid == driver.driverid &&
x.weeknumber == Constants.week &&
(x.weekday == Constants.days[i] || x.shiftid == shift.shiftid))))
{
if (!string.IsNullOrEmpty(objschedule.scheduleid))
{
if (drivertracker == null)
{
drivertracker = new DriverTracker();
drivertracker.scheduleid = scheduleid;
drivertracker.weeknumber = Constants.week;
drivertracker.driverid = driver.driverid;
drivertracker.shiftid = shift.shiftid;
drivertracker.totaltrip = 2;
drivertracker.weekday = Constants.days[i];
drivertrackerbl.SaveDriverTracker(drivertracker);
}
else
{
drivertracker.scheduleid = scheduleid;
drivertracker.weeknumber = Constants.week;
drivertracker.driverid = driver.driverid;
drivertracker.shiftid = shift.shiftid;
drivertracker.totaltrip = 2;
drivertracker.weekday = Constants.days[i];
drivertrackerbl.EditDriverTracker(drivertracker);
}
lstdrivertrackers.Add(drivertracker);
}
initialvalue = i;
break;
}
else if (lstdrivertrackers.Count == (Constants.days.Length - Constants.holidays.Length) * Constants.maxshift)
{
break;
}
}
if (i == Constants.days.Length - 1)
{
i = 0;
initialvalue = 0;
}
}
}
}
return(scheduledl.All("SCHEDULE_ID", Convert.ToString(scheduleid)));
}
catch (Exception e)
{
return(null);
}
}
