public async Task <ResultObject> DeleteLocation(M_Location loc)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = loc
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strId", loc.Id),
new MySqlParameter("strDelete_By", loc.Updated_By)
};
//Output Parameter no need to define. @`strId`
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_location_delete( ?, ?)", parameters : sqlParams);
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public async Task <IActionResult> Edit([Bind("LocationCode,LocationName,LocationDesc,WarehouseId,CompanyCode,Id,Is_Active,Created_Date,Created_By,Updated_Date,Updated_By")] M_Location m_Location)
{
if (ModelState.IsValid)
{
m_Location.Updated_By = await base.CurrentUserId();
ResultObject resultObj;
try
{
using (var locationBll = new LocationBLL())
{
resultObj = await locationBll.UpdateLocation(m_Location);
_cache.Remove("CACHE_MASTER_LOCATION");
}
return(Json(new { success = true, data = (M_Location)resultObj.ObjectValue, message = "Location Update." }));
}
catch (Exception ex)
{
return(Json(new { success = false, data = m_Location, message = ex.Message }));
}
}
var err = ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage).ToList();
return(Json(new { success = false, errors = err, data = m_Location, message = "Update Failed" }));
}
public void Add(M_Location Location)
{
var db = new DatabaseContext();
Location.IsOld = false;
db.Location.Where(c => c.PersonelId == Location.PersonelId).Load();
foreach (var li in db.Location.Local)
{
li.IsOld = true;
}
db.SaveChanges();
db.Location.Add(Location);
db.SaveChanges();
}
public string GetReceiveOnTime(string nodeID, string timeUpdate)
{
string str = "{\"ret\":\"success\",";
using (SqlConnection DBConn = new SqlConnection(DBConstr))
{
DBConn.Open();
using (SqlCommand cmd = DBConn.CreateCommand())
{
string where = "";
where = " where Time > '" + timeUpdate + "' and Time <> '" + timeUpdate + "' ";
cmd.CommandText = "select ID,Longitude,Latitude,Time,Temperature,Humidity,SO,PM25,PM10 from T_Location_" + nodeID + where + " order by Time desc";
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
M_Location loc = new M_Location();
loc.ID = reader.GetInt32(reader.GetOrdinal("ID"));
loc.longitude = reader.GetDouble(reader.GetOrdinal("Longitude"));
loc.latitude = reader.GetDouble(reader.GetOrdinal("Latitude"));
loc.time = reader.GetDateTime(reader.GetOrdinal("Time"));
loc.temperature = reader.GetString(reader.GetOrdinal("Temperature"));
loc.humidity = reader.GetString(reader.GetOrdinal("Humidity"));
loc.so = reader.GetString(reader.GetOrdinal("SO"));
loc.pm25 = reader.GetString(reader.GetOrdinal("PM25"));
loc.pm10 = reader.GetString(reader.GetOrdinal("PM10"));
str += "\"Lng\":\"" + loc.longitude +
"\",\"Lat\":\"" + loc.latitude +
"\",\"Tim\":\"" + loc.time +
"\",\"PM25\":\"" + loc.pm25 +
"\",\"PM10\":\"" + loc.pm10 +
"\",\"SO\":\"" + loc.so +
"\",\"Hum\":\"" + loc.humidity +
"\",\"Tem\":\"" + loc.temperature + "\"";
}
}
}
}
str += "}";
return(str);
}
public async Task <ResultObject> UpdateLocation(M_Location loc)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = loc
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Entry(loc).State = EntityState.Modified;
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strId", loc.Id),
new MySqlParameter("strLocationCode", loc.LocationCode),
new MySqlParameter("strLocationName", loc.LocationName),
new MySqlParameter("strLocationDesc", loc.LocationDesc),
new MySqlParameter("strWarehouseId", loc.WarehouseId),
new MySqlParameter("strCompanyCode", loc.CompanyCode),
new MySqlParameter("strIs_Active", loc.Is_Active),
new MySqlParameter("strCreated_By", loc.Updated_By)
};
//Output Parameter no need to define. @`strId`
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_location_update(?, ?, ?, ?, ?, ?, ?, ?)", parameters : sqlParams);
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public async Task <ResultObject> InsertLocation(M_Location loc)
{
var resultObj = new ResultObject {
RowAffected = -1, ObjectValue = loc
};
using (var context = new MasterDbContext(contextOptions))
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
MySqlParameter[] sqlParams = new MySqlParameter[] {
new MySqlParameter("strLocationCode", loc.LocationCode),
new MySqlParameter("strLocationName", loc.LocationName),
new MySqlParameter("strLocationDesc", loc.LocationDesc),
new MySqlParameter("strWarehouseId", loc.WarehouseId),
new MySqlParameter("strCompanyCode", loc.CompanyCode),
new MySqlParameter("strIs_Active", loc.Is_Active),
new MySqlParameter("strCreated_By", loc.Created_By)
};
resultObj.RowAffected = await context.Database.ExecuteSqlCommandAsync("call sp_location_insert(@`strId`, ?, ?, ?, ?, ?, ?, ?)", parameters : sqlParams);
//new Location after insert.
var newLoc = context.Location.FromSql("SELECT * FROM m_location WHERE Id = @`strId`;").ToListAsync();
resultObj.ObjectValue = newLoc.Result[0];
transaction.Commit();
return(resultObj);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public string GetPoint(double Lng, double Lat)
{
string str = "";
List <M_Location> locList = new List <M_Location>();
ditujiupian.com.GetMessage get = ditujiupian.com.Convert.BD2Wgs(Lng, Lat);
Lng = get.Lng;
Lat = get.Lat;
double LngMax = Lng + 0.001; //经度范围扩大100m
double LngMin = Lng - 0.001; //经度范围扩大100m
double LatMax = Lat + 0.001; //纬度范围扩大100m
double LatMin = Lat - 0.001; //纬度范围扩大100m
using (SqlConnection DBConn = new SqlConnection(DBConstr))
{
DBConn.Open();
using (SqlCommand cmd = DBConn.CreateCommand())
{
string where = " where Longitude > " + LngMin.ToString() + " and Longitude < " + LngMax.ToString() + " and Latitude > " +
LatMin.ToString() + " and Latitude < " + LatMax.ToString();
//where = " where Time >= '" + date + " 16:00:00' and Time <= '" + date + " 19:00:00'";
//where = " where convert(varchar(10), Time, 120) = @date ";
cmd.CommandText = "select ID,Longitude,Latitude,Time,Temperature,Humidity,SO,PM25,PM10 from T_Location_1" + where + " order by Time desc";
//cmd.Parameters.Add(new SqlParameter("date", date));
using (SqlDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
count++;
if (count == 10)
{
M_Location loc = new M_Location();
loc.ID = reader.GetInt32(reader.GetOrdinal("ID"));
loc.longitude = reader.GetDouble(reader.GetOrdinal("Longitude"));
loc.latitude = reader.GetDouble(reader.GetOrdinal("Latitude"));
loc.time = reader.GetDateTime(reader.GetOrdinal("Time"));
loc.temperature = reader.GetString(reader.GetOrdinal("Temperature"));
loc.humidity = reader.GetString(reader.GetOrdinal("Humidity"));
loc.so = reader.GetString(reader.GetOrdinal("SO"));
loc.pm25 = reader.GetString(reader.GetOrdinal("PM25"));
loc.pm10 = reader.GetString(reader.GetOrdinal("PM10"));
count = 0;
locList.Add(loc);
}
}
}
if (locList.Count != 0)
{
str = "{\"Points\":[";
int i = 0;
for (i = 0; i < locList.Count - 1; i++)
{
str += "{\"Lng\":\"" + locList[i].longitude +
"\",\"Lat\":\"" + locList[i].latitude +
"\",\"Tim\":\"" + locList[i].time +
"\",\"PM25\":\"" + locList[i].pm25 +
"\",\"PM10\":\"" + locList[i].pm10 +
"\",\"SO\":\"" + locList[i].so +
"\",\"Hum\":\"" + locList[i].humidity +
"\",\"Tem\":\"" + locList[i].temperature +
"\"},";
}
str += "{\"Lng\":\"" + locList[i].longitude +
"\",\"Lat\":\"" + locList[i].latitude +
"\",\"Tim\":\"" + locList[i].time +
"\",\"PM25\":\"" + locList[i].pm25 +
"\",\"PM10\":\"" + locList[i].pm10 +
"\",\"SO\":\"" + locList[i].so +
"\",\"Hum\":\"" + locList[i].humidity +
"\",\"Tem\":\"" + locList[i].temperature +
"\"}";
str += "]};";
}
}
}
return(str);
}