public DS_Employee.NameListDataTable GetAvalibleBus(int scheduleID)
{
string sql = @"
declare @fromDate DateTime
declare @toDate DateTime
select @fromDate=startDate, @toDate=endDate
from BusSchedule
where scheduleID=@scheduleID;
SELECT vehicleName as username, vehicleId as userId
FROM vehicles
where status=1 and vehicleId in (
select vehicleId
from vehicleUsage
where startDate<=@fromDate and endDate>=@toDate
) and vehicleId not in (
select DISTINCT vehicleId
from busSchedule
where not (endDate<@fromDate or startDate>@toDate) and (scheduleId<>@scheduleId)
)";
SqlParameter[] para = {
new SqlParameter("@scheduleID", scheduleID)
};
DS_Employee ds = new DS_Employee();
m_dao.FillDataSet(ds, "NameList", sql, para);
return ds.NameList;
}
public DS_Employee.NameListDataTable GetAvalibleDriver(int scheduleID)
{
string sql = @"
declare @fromDate DateTime
declare @toDate DateTime
select @fromDate=startDate, @toDate=endDate
from BusSchedule
where scheduleID=@scheduleID;
SELECT userId, firstName as username
FROM userInfo
WHERE userType=3 and
userId not in
(
select DISTINCT driverId as userId
from busSchedule
where not (endDate<@fromDate or startDate>@toDate) and (scheduleId<>@scheduleId)
union
select DISTINCT driverAssistanceId as userId
from busSchedule
where not (endDate<@fromDate or startDate>@toDate) and (scheduleId<>@scheduleId)
)";
SqlParameter[] para = {
new SqlParameter("@scheduleID", scheduleID)
};
DS_Employee ds = new DS_Employee();
m_dao.FillDataSet(ds, "NameList", sql, para);
return ds.NameList;
}
public DS_Employee.NameListDataTable GetAvailableTourGuideList(int scheduleID)
{
// --jimmy, 2006.12.22
// in schedule, use UserID for tourGuide, not employeeID. just for easier to get username.
// TODO: change to employeeID??
string sql = @"
declare @fromDate DateTime
declare @toDate DateTime
select @fromDate=startDate, @toDate=endDate
from BusSchedule
where scheduleID=@scheduleID;
SELECT employeeID as userId, nickname as username
FROM EmployeeInfo
WHERE
(@fromDate is not null and @toDate is not null ) and
(status = 1) and
(employeeID in
(select employeeID from userRole where roleType=2 and roleID=1))
and
employeeID not in
(
select DISTINCT tourGuideId as employeeID
from busSchedule
where not (endDate<@fromDate or startDate>@toDate) and (scheduleId<>@scheduleId)
union
select DISTINCT tourGuideAssistanceId as employeeID
from busSchedule
where not (endDate<@fromDate or startDate>@toDate) and (scheduleId<>@scheduleId)
)";
SqlParameter[] para = {
new SqlParameter("@scheduleID", scheduleID)
};
DS_Employee ds = new DS_Employee();
m_dao.FillDataSet(ds, "NameList", sql, para);
return ds.NameList;
}