// GET: api/Employees
public IHttpActionResult GetRequests()
{
var res = new List <Reservation>(dbRes.Reservations);
var req = new List <Request>(dbReq.Requests);
var veh = new List <Vehicle>(dbVeh.Vehicles);
var emp = new List <Person>(dbEmp.Persons);
var request = new List <SelectRequest>();
IList <object> sourceList = new List <object>();
string sqlString = @"select TerritorySummary.isim,
TerritorySummary.PhotoUrl,
TerritorySummary.TelephoneNumber,
TerritorySummary.Plaka,
TerritorySummary.DurationCheck,
TerritorySummary.CreationDate
from
(select per.FirstName + ' ' + per.LastName isim ,
per.TelephoneNumber,
per.PhotoUrl,
veh.Plaka,
req.DurationCheck,
req.CreationDate,
ROW_NUMBER() OVER(Partition by veh.plaka ORDER BY req.CreationDate desc) xs
from dbo.Persons per,
dbo.Requests req,
dbo.Vehicles veh
where
per.PersonID = req.PersonID
and req.VehicleID = veh.VehicleID
and veh.EnabledFlag = 0
)AS TerritorySummary
where TerritorySummary.xs = 1";
using (var context = new arabaDBEntities10())
{
request = context.Database.SqlQuery <SelectRequest>(sqlString).ToList();
}
foreach (SelectRequest requ in request)
{
DateTimeOffset localTime, otherTime, universalTime;
// Define local time in local time zone
TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Turkey Standard Time");
DateTime UpdatedTime = requ.CreationDate ?? DateTime.Now;
DateTime cstTime = TimeZoneInfo.ConvertTimeFromUtc(UpdatedTime, cstZone);
requ.CreationDateStr = cstTime.ToString().Split(' ')[1].Split(':')[0] + ":" + cstTime.ToString().Split(' ')[1].Split(':')[1];
}
sourceList.Add(request);
var reservations = (from ep in emp
join e in res on ep.PersonID equals e.PersonID
join t in veh on e.VehicleID equals t.VehicleID
where e.BeginDate >= TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "Turkey Standard Time") ||
(e.BeginDate < TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "Turkey Standard Time") &&
TimeZoneInfo.ConvertTimeBySystemTimeZoneId(DateTime.Now, "Turkey Standard Time") < e.EndDate)
orderby e.BeginDate ascending
select new
{
Name = ep.FirstName + " " + ep.LastName,
BeginDate = e.BeginDate.ToString(),
PhotoUrl = ep.PhotoUrl,
Plaka = t.Plaka,
TelephoneNumber = ep.TelephoneNumber,
EndDate = e.EndDate.ToString(),
Aim = e.Aim,
ReservationId = e.ReservationID,
PersonId = e.PersonID
}).ToList();
sourceList.Add(reservations);
return(Json(sourceList));
}
