internal static void AddParam(NpgsqlCommand cmd, string name, Point2 <double> location)
{
if (location != null)
{
NpgsqlTypes.PostgisPoint geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(location);
if (geom != null)
{
cmd.Parameters.AddWithValue(name, geom);
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam(name, NpgsqlTypes.NpgsqlDbType.Geometry));
}
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam(name, NpgsqlTypes.NpgsqlDbType.Geometry));
}
}
// GET: Home/AddPoint
/*public ActionResult AddPoint(PointPlusUser point)
* {
* List<string> addresslist = new List<string>();
* bool logged = false;
* using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" +
* "User Id=postgres;Password=goralt;Database=postgis;"))
* {
* conn.Open();
* string loginQuery = "(SELECT \"user\", pass, latestactivity FROM users)";
* var command = new NpgsqlCommand(loginQuery, conn);
* using (NpgsqlDataReader dr = command.ExecuteReader())
* {
* while (dr.Read())
* {
*
* if (dr[0].ToString() == point.user && dr[1].ToString() == point.password && DateTime.Compare(((DateTime)dr[2]).Date, DateTime.Now.Date) < 0)
* {
* logged = true;
* }
* }
* }
*
* }
* if (logged)
* {
* string name = "unknown";
* Int32 saveResult;
*
*
* using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" +
* "User Id=postgres;Password=goralt;Database=postgis;"))
* {
*
* string punkt = "ST_Transform(ST_GeomFromText('POINT(" +
* point.Lon.ToString(CultureInfo.InvariantCulture) + " " + point.Lat.ToString(CultureInfo.InvariantCulture) + ")',3857),2180)";
*
* conn.Open();
*
* string areaQuery = "(SELECT geom FROM warszawa WHERE ST_Contains(warszawa.geom," + punkt + " ))";
* string addressQuery = "SELECT adres FROM adresy WHERE ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180))";
* var command = new NpgsqlCommand(addressQuery, conn);
* int i = 0;
*
* using (NpgsqlDataReader dr = command.ExecuteReader())
* {
* while (dr.Read())
* {
* addresslist.Add(dr.GetValue(0).ToString());
* }
* }
*
* if (name != "undefined")
* {
*
* //int w = 1;
* //string Query = "(SELECT address FROM points WHERE address='" + name + "')";
* //var com = new NpgsqlCommand(Query, conn);
* //string firstrecord="";
* //using (NpgsqlDataReader dr = com.ExecuteReader())
* //{
* // if(dr.Read())
* // {
* // while(dr.GetValue(i++)!=null)
* // string s;
* // addresslist.Add(dr.GetValue(0).ToString());
* // }
* //}
* //if (firstrecord == "")
* //{
* // string insertQuery = "insert into points(address,geom,p) values (\'" + name + "\'," + punkt + "," + w.ToString() + ")";
* // var insertComm = new NpgsqlCommand(insertQuery, conn);
* // saveResult = insertComm.ExecuteNonQuery();
* //}
* //else
* //{
* // string insertQuery = "update points set p=p+" + w.ToString() + " where address='" + name + "'";
* // var insertComm = new NpgsqlCommand(insertQuery, conn);
* // saveResult = insertComm.ExecuteNonQuery();
* //}
* }
*
*
* string loginUpdateQuery = "UPDATE users SET latestactivity = NOW() WHERE \"user\" ='" + point.user + "' AND pass='******'";
* var upcommand = new NpgsqlCommand(loginUpdateQuery, conn);
* Int32 save = upcommand.ExecuteNonQuery();
*
* }
*
* var commune = new
* {
* Name = name,
* //Saved = saveResult
* };
* }
* return Json(addresslist);
* }*/
public ActionResult AddAddress(_address name)
{
bool logged = false;
using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" +
"User Id=postgres;Password=goralt;Database=postgis;"))
{
conn.Open();
string loginQuery = "(SELECT \"user\", pass, latestactivity FROM users)";
var command = new NpgsqlCommand(loginQuery, conn);
using (NpgsqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0].ToString() == name.User && dr[1].ToString() == name.Password && DateTime.Compare(((DateTime)dr[2]).Date, DateTime.Now.Date) < 0)
{
logged = true;
}
}
}
}
_address response = new _address {
Address = "", Lat = 0, Lon = 0, Points = 0, User = "", Password = ""
};
if (logged)
{
using (NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;" +
"User Id=postgres;Password=goralt;Database=postgis;"))
{
if (name.Address != "")
{
conn.Open();
double w = name.Points;
/*string areaQuery = "(SELECT geom FROM warszawa WHERE ST_Contains(warszawa.geom," + point + " ))";
* string addressQuery = "SELECT adres FROM adresy WHERE ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180))";
* string Query = "(SELECT address FROM points WHERE address='" + name.Address + "' and ST_Contains(" + areaQuery + ",points.geom))";
* string Querygeom = "(SELECT geom FROM adresy WHERE adres='" + name.Address + "' and ST_Contains(" + areaQuery + ",ST_Transform(adresy.geom,2180)))";*/
string Query = "SELECT address from points where address ='" + name.Address + "'";
var com = new NpgsqlCommand(Query, conn);
string firstrecord = "";
NpgsqlTypes.PostgisPoint geometry = null;
Int32 saveResult;
using (NpgsqlDataReader dr = com.ExecuteReader())
{
if (dr.Read())
{
firstrecord = dr.GetValue(0).ToString();
}
}
/*com = new NpgsqlCommand(Querygeom, conn);
* using (NpgsqlDataReader dr = com.ExecuteReader())
* {
* if (dr.Read())
* {
* geometry = (NpgsqlTypes.PostgisPoint)dr.GetValue(0);
* }
* }*/
if (firstrecord == "")
{
/*string punkt = "ST_Transform(ST_GeomFromText('POINT(" +
* geometry.X.ToString(CultureInfo.InvariantCulture) + " " + geometry.Y.ToString(CultureInfo.InvariantCulture) + ")',2178),2180)";*/
string point = "ST_Transform(ST_GeomFromText('POINT(" +
name.Lon.ToString(CultureInfo.InvariantCulture) + " " + name.Lat.ToString(CultureInfo.InvariantCulture) + ")',3857),2180)";
string insertQuery = "insert into points(address,geom,p,count) values ('" + name.Address + "'," + point + "," + w.ToString() + ",1)";
var insertComm = new NpgsqlCommand(insertQuery, conn);
response.Address = name.Address;
response.Lat = name.Lat;
response.Lon = name.Lon;
saveResult = insertComm.ExecuteNonQuery();
}
else
{
string insertQuery = "update points set p=p+" + w.ToString() + ", count=count+1 where address='" + name.Address + "'";
var insertComm = new NpgsqlCommand(insertQuery, conn);
saveResult = insertComm.ExecuteNonQuery();
response.Address = "Update";
}
string loginUpdateQuery = "UPDATE users SET latestactivity = NOW() WHERE \"user\" ='" + name.User + "' AND pass='******'";
var upcommand = new NpgsqlCommand(loginUpdateQuery, conn);
Int32 save = upcommand.ExecuteNonQuery();
}
}
}
return(Json(response));
}
public override bool Update(Site site)
{
if (site != null && this.CanUpdate(site))
{
try
{
NpgsqlCommand cmd = Db.GetCmd(Db.ConnectionString);
cmd.CommandText = Db.UpdateSite;
cmd.Parameters.AddWithValue("sid", site.Identity.DataStoreIdentity);
cmd.Parameters.AddWithValue("id", site.Identity.Identity);
cmd.Parameters.AddWithValue("osid", site.OwningOrganizationIdentity.DataStoreIdentity);
cmd.Parameters.AddWithValue("oid", site.OwningOrganizationIdentity.Identity);
cmd.Parameters.AddWithValue("name", site.Name);
if (string.IsNullOrEmpty(site.Description))
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("desc", NpgsqlTypes.NpgsqlDbType.Varchar));
}
else
{
cmd.Parameters.AddWithValue("desc", site.Description);
}
if (site.Location != null)
{
NpgsqlTypes.PostgisGeometry geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(site.Location);
if (geom != null)
{
cmd.Parameters.AddWithValue("loc", geom);
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("loc", NpgsqlTypes.NpgsqlDbType.Geometry));
}
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("loc", NpgsqlTypes.NpgsqlDbType.Geometry));
}
if (site.LocationMark != null)
{
NpgsqlTypes.PostgisPoint geom = Osrs.Numerics.Spatial.Postgres.NpgSpatialUtils.ToPGis(site.LocationMark);
if (geom != null)
{
cmd.Parameters.AddWithValue("poi", geom);
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("poi", NpgsqlTypes.NpgsqlDbType.Geometry));
}
}
else
{
cmd.Parameters.Add(NpgSqlCommandUtils.GetNullInParam("poi", NpgsqlTypes.NpgsqlDbType.Geometry));
}
cmd.Parameters.AddWithValue("start", DateTime.UtcNow);
Db.ExecuteNonQuery(cmd);
return(true);
}
catch
{ }
}
return(false);
}
