public IEnumerable <Company> GetCompanies(CompanyFilter filter)
{
//FormattableString SQL = $"SELECT * FROM UserLocations,LocationInfo WHERE UserLocations.ID_Location = LocationInfo.ID_Location AND ID_User = {ID_User}";
// FormattableString SQL = $@"SELECT * FROM LocationInfo
// WHERE ID_Location %{filter.filterLocations}%
// AND Latitude>={filter.filterLatitudeStart}
// AND Latitude<={filter.filterLatitudeEnd}
// AND Longitude>={filter.filterLongitudeStart}
// AND Longitude<={filter.filterLongitudeEnd}";
FormattableString SQL = $@"SELECT * FROM LocationInfo WHERE STATUS <> 0";
DataSet data = db.Query(SQL);
foreach (DataRow dr in data.Tables[0].Rows)
{
yield return(new Company()
{
IdLocation = dr.Field <int>("ID_Location"),
BusinessName = dr.Field <string>("Business_Name"),
Address = dr.Field <string>("Address"),
PostalCode = dr.Field <string>("PostalCode"),
City = dr.Field <string>("City"),
Latitude = dr.Field <double>("Latitude"),
Longitude = dr.Field <double>("Longitude"),
Opening = dr.Field <int>("Opening"),
Closing = dr.Field <int>("Closing")
});
}
}
public async Task <List <CourseViewModel> > GetCoursesAsync()
{
String query = "SELECT * FROM Corsi";
DataSet dataSet = db.Query(query);
throw new NotImplementedException();
}
public IEnumerable <DeviceProbe> GetProbes(int idDevice, DateTime startDate, DateTime endDate)
{
FormattableString SQL = $"SELECT * FROM Probes WHERE ID_Device_FK={idDevice} AND Date>={startDate} AND Date<={endDate}";
DataSet dt = db.Query(SQL);
foreach (DataRow dr in dt.Tables[0].Rows)
{
yield return(new DeviceProbe()
{
ID_Device = dr.Field <int>("ID_Device_FK"),
Delta = dr.Field <short>("Delta"),
Date = dr.Field <DateTime>("Date")
});
}
}
public IEnumerable <FlowData> getFlowData()
{
FormattableString SQL = $"SELECT * FROM LocationInfo";
DataSet data = db.Query(SQL);
foreach (DataRow dr in data.Tables[0].Rows)
{
yield return(new FlowData()
{
Name = dr["Business_Name"].ToString(),
PeopleNumber = dr.Field <long?>("People_Count"),
//LastUpdate = DateTime.Parse(dr.Field<string>("Last_Seen"))
Info = "Aperto fino alle " + dr.Field <DateTime>("Closing").ToString("HH:mm")
});
}
}
public Device GetDevice(string otp)
{
FormattableString SQL = $"SELECT * FROM DeviceInfo WHERE OTP_Key={otp}";
DataSet data = db.Query(SQL);
if (data.Tables[0].Rows.Count == 0)
{
return(null);
}
DataRow dr = data.Tables[0].Rows[0];
return(new Device()
{
ID_Device = dr.Field <int>("ID_Device"),
MacAddress = dr.Field <string>("Mac_Address"),
OTP_Key = dr.Field <string>("OTP_Key")
});
}
public void CreateMovent(int idDevice, int idLocation, int type = 0)
{
FormattableString SQL = $"INSERT INTO Movements(ID_Device_FK,ID_Location_FK,Type) VALUES({idDevice},{idLocation},{type})";
db.Query(SQL);
}