public RequestRemarks CancelRequest(PostRequest cancelRequest)
{
var objReturn = new RequestRemarks();
if (IsRandomCodeValid(cancelRequest.trip_id, cancelRequest.random))
{
var eecEmployee = new EECEmployee();
var employeeInfo = eecEmployee.SearchOnDirectory(cancelRequest.emp_id);
var info = employeeInfo.Count != 0 ? employeeInfo[0] : null;
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("DELETE FROM seat WHERE random=@random AND trip_id=@trip_id", con);
cmd.Parameters.AddWithValue("@trip_id", cancelRequest.trip_id);
cmd.Parameters.AddWithValue("@random", cancelRequest.random);
if (cmd.ExecuteNonQuery() > 0)
{
objReturn = new RequestRemarks
{
remarks = "You have cancelled your reserved seat!",
subject = "Cancelled"
};
AppendLog(info.e_Mail.Split('@')[0], " cancelled ", cancelRequest.trip_id + " reservation");
}
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Trip not found.",
subject = "Notice"
};
}
return(objReturn);
}
public List <FleetRequestData> GetApprovalsData(string line_manager_id)
{
var eecEmployees = new EECEmployee();
if (!line_manager_id.Contains("@"))
{
line_manager_id = line_manager_id + "@kaec.net";
}
line_manager_id = eecEmployees.GetEmployeeDetails(line_manager_id)[0].emp_ID;
var listReturn = new List <FleetRequest>();
var tempList = new List <FleetRequest>();
using (var con = new MySqlConnection(ConnectionHelper.MyDirectoryConnection()))
{
con.Open();
var cmd = new MySqlCommand();
cmd = new MySqlCommand("SELECT * FROM fleet_request " +
"WHERE code='0' AND randomize!='9' ORDER BY from_date DESC", con);
cmd.Parameters.AddWithValue("@line_manager_id", line_manager_id);
using (var dr = cmd.ExecuteReader())
{
tempList = PopulateFleetRequest(dr);
dr.Close();
}
con.Close();
}
foreach (var items in tempList)
{
var result = new List <EECEmployee>();
result = eecEmployees.GetEmployeeDetails(items.emp_id);
var fleetServeDetails = GetFleetServeDetails(items.service_ref);
if (result.Count == 0)
{
var nonEEC = new NonEECEmployee();
var nonEECEmployeeDetails = nonEEC.GetEmployeeDetails(items.emp_id);
result.Add(new EECEmployee
{
cost_Center = nonEECEmployeeDetails.cost_center,
department = nonEECEmployeeDetails.department,
e_Mail = nonEECEmployeeDetails.email,
line_Manager_Name = nonEECEmployeeDetails.manager,
line_Manager_No = nonEECEmployeeDetails.manager_id
});
}
else
{
result = eecEmployees.GetEmployeeDetails(items.emp_id);
}
if (result.Count != 0)
{
foreach (var employeeDetails in result)
{
if (employeeDetails.line_Manager_No == line_manager_id)
{
var driverName = string.Empty;
var driverMobile = string.Empty;
var driverDetails = eecEmployees.GetEmployeeDetails(items.driver_id);
foreach (var details in driverDetails)
{
driverName = items.nature.ToLower().Contains("chauffeur") ? "WPS Driver" : details.employee_Name_English;
driverMobile = details.mobile;
}
items.cost_center = employeeDetails.cost_Center;
items.department = employeeDetails.department;
items.driver = driverName;
items.driver_mobile = driverMobile;
items.email = employeeDetails.e_Mail;
items.line_manager = employeeDetails.line_Manager_Name;
items.line_manager_email = employeeDetails.line_Manager_Email;
items.line_manager_id = employeeDetails.line_Manager_No;
items.complaint = fleetServeDetails.complaint;
items.end_km = fleetServeDetails.end_km;
items.note = fleetServeDetails.note;
items.plate = fleetServeDetails.plate;
items.sar = fleetServeDetails.sar;
listReturn.Add(items);
break;
}
}
}
}
var request = new List <FleetRequestData>();
request.Add(new FleetRequestData
{
data = listReturn
});
return(request);
}
public FleetRequest GetRequestsDetails(string service_ref)
{
var objReturn = new FleetRequest();
var eecEmployees = new EECEmployee();
using (var con = new MySqlConnection(ConnectionHelper.MyDirectoryConnection()))
{
con.Open();
var cmd = new MySqlCommand();
cmd = new MySqlCommand("SELECT * FROM fleet_request " +
"WHERE service_ref=@service_ref", con);
cmd.Parameters.AddWithValue("@service_ref", service_ref);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var fleetServeDetails = GetFleetServeDetails(dr["service_ref"].ToString());
var result = new List <EECEmployee>();
result = eecEmployees.GetEmployeeDetails(dr["emp_id"].ToString());
if (result.Count == 0)
{
var nonEEC = new NonEECEmployee();
var nonEECEmployeeDetails = nonEEC.GetEmployeeDetails(dr["emp_id"].ToString());
result.Add(new EECEmployee
{
cost_Center = nonEECEmployeeDetails.cost_center,
department = nonEECEmployeeDetails.department,
e_Mail = nonEECEmployeeDetails.email,
line_Manager_Name = nonEECEmployeeDetails.manager,
line_Manager_No = nonEECEmployeeDetails.manager_id
});
}
else
{
result = eecEmployees.GetEmployeeDetails(dr["emp_id"].ToString());
}
if (result.Count != 0)
{
foreach (var employeeDetails in result)
{
//if (employeeDetails.line_Manager_No == line_manager_id)
//{
var driverName = string.Empty;
var driverMobile = string.Empty;
var driverDetails = eecEmployees.GetEmployeeDetails(dr["driver_id"].ToString());
foreach (var details in driverDetails)
{
driverName = dr["nature"].ToString().ToLower().Contains("chauffeur") ? "WPS Driver" : details.employee_Name_English;
driverMobile = details.mobile;
}
objReturn = new FleetRequest
{
cost_center = employeeDetails.cost_Center,
department = employeeDetails.department,
driver = driverName,
driver_mobile = driverMobile,
email = employeeDetails.e_Mail,
line_manager = employeeDetails.line_Manager_Name,
line_manager_email = employeeDetails.line_Manager_Email,
line_manager_id = employeeDetails.line_Manager_No,
code = dr["code"].ToString(),
contact = dr["contact"].ToString(),
driver_id = dr["driver_id"].ToString(),
drop = dr["drop"].ToString(),
emp_id = dr["emp_id"].ToString(),
from_date = Convert.ToDateTime(dr["from_date"].ToString()),
grade = int.Parse(dr["grade"].ToString()),
id = int.Parse(dr["id"].ToString()),
ip = dr["ip"].ToString(),
justification = dr["justification"].ToString(),
name = dr["name"].ToString(),
nature = dr["nature"].ToString(),
pax = dr["pax"].ToString(),
pick = dr["pick"].ToString(),
random = dr["random"].ToString(),
randomize = dr["randomize"].ToString(),
remarks = dr["remarks"].ToString(),
service_ref = dr["service_ref"].ToString(),
session = dr["session"].ToString(),
specific_pick = dr["specific_pick"].ToString(),
timestamp = dr["timestamp"].ToString(),
to_date = nature.ToLower().Contains("chauffeur") ? Convert.ToDateTime(dr["from_date"].ToString()) : Convert.ToDateTime(dr["to_date"].ToString()),
type = dr["type"].ToString(),
complaint = fleetServeDetails.complaint,
end_km = fleetServeDetails.end_km,
note = fleetServeDetails.note,
plate = fleetServeDetails.plate,
sar = fleetServeDetails.sar,
};
break;
//}
}
}
}
}
//var request = new TemplateRequest();
////request.registration_ids = listReturn.ToArray();
//request.data = new TemplateRequest.Data(listReturn);
//string json = new JavaScriptSerializer().Serialize(request);
return(objReturn);
}
public List <EECEmployee> get_direct_reports(string empId)
{
var eecEmployee = new EECEmployee();
return(eecEmployee.GetDirectReports(empId));
}
public List <EECEmployee> get_shares_line_manager(string lineManagerNo)
{
var eecEmployee = new EECEmployee();
return(eecEmployee.GetSharesLineManager(lineManagerNo));
}
public List <EECEmployee> search_on_directory(string keyWord)
{
var eecEmployee = new EECEmployee();
return(eecEmployee.SearchOnDirectory(keyWord));
}
public RequestRemarks CreateUpdateRequest(PostRequest newRequest)
{
var objReturn = new RequestRemarks();
var eecEmployee = new EECEmployee();
var employeeInfo = eecEmployee.SearchOnDirectory(newRequest.emp_id);
var info = employeeInfo.Count != 0 ? employeeInfo[0] : null;
if (info != null)
{
if (IsTripValid(newRequest.trip_id))
{
if (newRequest.seat_no >= 0 && newRequest.seat_no <= 6)
{
if (!IsSeatAvailable(newRequest.trip_id, newRequest.seat_no))
{
objReturn = new RequestRemarks
{
remarks = "Seat selected is no longer available!",
subject = "Already Taken"
};
}
else
{
if (IsUpdateSeat(info.emp_ID, newRequest.trip_id, newRequest.seat_no))
{
if (IsRandomCodeValid(newRequest.trip_id, newRequest.random))
{
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("UPDATE seat SET seat=@seat,date_submit=@date_submit WHERE pax_id=@pax_id AND trip_id=@trip_id", con);
cmd.Parameters.AddWithValue("@seat", newRequest.seat_no);
cmd.Parameters.AddWithValue("@date_submit", DateTime.Now);
cmd.Parameters.AddWithValue("@trip_id", newRequest.trip_id);
cmd.Parameters.AddWithValue("@pax_id", info.emp_ID);
if (cmd.ExecuteNonQuery() > 0)
{
objReturn = new RequestRemarks
{
remarks = "You have updated your reserved seat!",
subject = "Updated"
};
AppendLog(info.e_Mail.Split('@')[0], " updated to " + newRequest.seat_no + " on ", newRequest.trip_id);
}
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Trip not found.",
subject = "Notice"
};
}
}
else
{
var tripDetails = new TripDetails();
var details = tripDetails.GetTripDetails(newRequest.trip_id);
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("INSERT INTO seat ( trip_id, trip, pax_name, gender, pax_email, pax_id, seat, cost_center, ip, date_submit, code, random) VALUES(@trip_id,@trip,@pax_name,@gender,@pax_email,@pax_id,@seat,@cost_center,@ip,@date_submit,@code,@random)", con);
cmd.Parameters.AddWithValue("@trip_id", newRequest.trip_id);
cmd.Parameters.AddWithValue("@trip", details.trip_date);
cmd.Parameters.AddWithValue("@pax_name", info.employee_Name_English);
cmd.Parameters.AddWithValue("@gender", info.gender);
cmd.Parameters.AddWithValue("@pax_email", info.e_Mail);
cmd.Parameters.AddWithValue("@pax_id", info.emp_ID);
cmd.Parameters.AddWithValue("@seat", newRequest.seat_no);
cmd.Parameters.AddWithValue("@cost_center", info.cost_Center);
cmd.Parameters.AddWithValue("@ip", "EEC Mobility");
cmd.Parameters.AddWithValue("@date_submit", DateTime.Now);
cmd.Parameters.AddWithValue("@code", 1);
cmd.Parameters.AddWithValue("@random", RandomizeHelper.GenerateRandomString());
if (cmd.ExecuteNonQuery() > 0)
{
objReturn = new RequestRemarks
{
remarks = "You have reserved a seat!",
subject = "Reserved"
};
AppendLog(info.e_Mail.Split('@')[0], " reserved " + newRequest.seat_no + " on ", newRequest.trip_id);
}
}
}
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Seat not found.",
subject = "Notice"
};
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Trip not found.",
subject = "Notice"
};
}
}
else
{
objReturn = new RequestRemarks
{
remarks = "Request should be initiated by an EEC Employee.",
subject = "Notice"
};
}
return(objReturn);
}
public List <SeatArrangementData> GetSeatArrangement(string paxId, string tripId)
{
var employeeInfo = new EECEmployee();
var listReturn = new List <SeatArrangement>();
var listSeat = new List <int>();
var tripDate = DateTime.Now;
using (var con = new MySqlConnection(ConnectionHelper.ServicesConnection()))
{
con.Open();
var cmd = new MySqlCommand("SELECT id, trip_id, trip, pax_name, gender, pax_email, pax_id, cast(seat as int) as seat, cost_center,ip, date_submit, cast(code as int) as code, random FROM seat WHERE trip_id=@trip_id", con);
cmd.Parameters.AddWithValue("@trip_id", tripId);
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var type = string.Empty;
var type_image_location = string.Empty;
if (dr["gender"].ToString() == "Driver")
{
type = "Driver";
type_image_location = "http://ez.kaec.net/assets/images/transport/chauffeur.png";
}
else
{
if (dr["pax_id"].ToString() == paxId)
{
type = "you";
type_image_location = "http://ez.kaec.net/assets/images/transport/you.png";
}
else if (dr["pax_email"].ToString().ToLower().Contains(paxId.ToLower()))
{
type = "you";
type_image_location = "http://ez.kaec.net/assets/images/transport/you.png";
}
else
{
type = "pax";
type_image_location = "http://ez.kaec.net/assets/images/transport/pax.png";
}
}
tripDate = Convert.ToDateTime(dr["trip"].ToString());
listReturn.Add(new SeatArrangement
{
id = int.Parse(dr["id"].ToString()),
code = int.Parse(dr["code"].ToString()),
cost_center = dr["cost_center"].ToString(),
reserved_on = Convert.ToDateTime(dr["date_submit"].ToString()),
gender = dr["gender"].ToString(),
pax_email = dr["pax_email"].ToString(),
pax_id = dr["pax_id"].ToString(),
pax_name = dr["pax_name"].ToString(),
phone = type == "Driver" ? dr["ip"].ToString() : employeeInfo.GetMobile(dr["pax_id"].ToString()).mobile,
random = dr["random"].ToString(),
seat_no = int.Parse(dr["seat"].ToString()),
trip = tripDate,
trip_id = dr["trip_id"].ToString(),
type = type,
type_image_location = type_image_location
});
listSeat.Add(seat_no = int.Parse(dr["code"].ToString()));
}
}
if (listReturn.Count != 0)
{
for (var seat = 0; seat < 7; seat++)
{
if (!listSeat.Any(o => o == seat))
{
listReturn.Add(new SeatArrangement
{
seat_no = seat,
trip = tripDate,
trip_id = tripId,
type = "seat",
type_image_location = "http://ez.kaec.net/assets/images/transport/seat.png",
});
}
}
}
var data = new List <SeatArrangementData>();
data.Add(new SeatArrangementData
{
data = listReturn.OrderBy(o => o.seat_no).ToList()
});
return(data);
}
public FleetNotification GetRequestsForApprovals(string line_manager_id)
{
var notificationReturn = new FleetNotification();
var ctr = 0;
if (!line_manager_id.Contains("@"))
{
line_manager_id = line_manager_id + "@kaec.net";
}
var eecEmployees = new EECEmployee();
line_manager_id = eecEmployees.GetEmployeeDetails(line_manager_id)[0].emp_ID;
var tempList = new List <FleetRequest>();
using (var con = new MySqlConnection(ConnectionHelper.MyDirectoryConnection()))
{
con.Open();
var cmd = new MySqlCommand();
cmd = new MySqlCommand("SELECT * FROM fleet_request " +
"WHERE code='0' AND randomize!='9' ORDER BY from_date DESC", con);
cmd.Parameters.AddWithValue("@line_manager_id", line_manager_id);
using (var dr = cmd.ExecuteReader())
{
tempList = FleetRequest.PopulateFleetRequest(dr);
dr.Close();
}
con.Close();
}
foreach (var items in tempList)
{
var result = new List <EECEmployee>();
result = eecEmployees.GetEmployeeDetails(items.emp_id);
if (result.Count == 0)
{
var nonEEC = new NonEECEmployee();
var nonEECEmployeeDetails = nonEEC.GetEmployeeDetails(items.emp_id);
result.Add(new EECEmployee
{
line_Manager_No = nonEECEmployeeDetails.manager_id
});
}
else
{
result = eecEmployees.GetEmployeeDetails(items.emp_id);
}
if (result.Count != 0)
{
foreach (var employeeDetails in result)
{
if (employeeDetails.line_Manager_No == line_manager_id)
{
ctr = ctr + 1;
break;
}
}
}
}
notificationReturn = new FleetNotification
{
Notifications = ctr,
Tag = "fleet approvals",
};
return(notificationReturn);
}
