/// <summary>
///
/// </summary>
/// <returns></returns>
public IEnumerable <DataModels.DictRegions> GetRegions()
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictRegions.ToList());
}
//List<Region> regions = new List<Region>();
//string cmd_text = @"
// SELECT [region_id]
// ,[country_id]
// ,[name]
// FROM [dbo].[dict_regions]
// ";
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// regions.Add(new Region {
// region_id = Convert.ToInt32(reader[0]),
// country_id = reader[1].ToString(),
// name = reader[2].ToString()
// });
// }
// connection.Close();
//}
//return regions;
}
public int GetCountPointsByMarketRadius(int market_id, int radius)
{
using (var db = new DataModels.GeolocationDB())
{
LinqToDB.Data.DataConnection connect = new LinqToDB.Data.DataConnection();
DataModels.GeolocationDBStoredProcedures.PGetCountPointsByMarketRadiusResult re =
DataModels.GeolocationDBStoredProcedures.PGetCountPointsByMarketRadius(connect, market_id, radius).FirstOrDefault();
return(re.qty.Value);
}
//int CardCount = 0;
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = "dbo.p_get_count_points_by_market_stat";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@market_google_coordinates_id", SqlDbType.Int).Value = market_id;
// cmd.Parameters.Add("@radius", SqlDbType.Int).Value = radius;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// CardCount = Convert.ToInt32(reader[0]);
// }
// connection.Close();
//}
//return CardCount;
}
public long SetDistrict(long potamus_district_id, long region_id, string name, string poligon)
{
//long district_id = 0;
SqlString sqlstr = new SqlString(poligon);
SqlChars polychars = new SqlChars(sqlstr);
using (var db = new DataModels.GeolocationDB())
{
object id = db.DictDistricts.InsertWithIdentity(() => new DataModels.DictDistricts
{
Name = name,
Poligon = SqlGeography.STPolyFromText(polychars, 4326),
RegionId = region_id,
PotamusDistrictId = potamus_district_id
});
return(Convert.ToInt64(id));
}
// string cmd_text = @"
// INSERT INTO [dbo].[dict_districts]
// ([potamus_district_id]
// ,[region_id]
// ,[name]
// ,[poligon])
// VALUES
// (@potamus_district_id
// ,@region_id
// ,@name
// ,geography::STPolyFromText('POLYGON((%poligon%))', 4326))
// SELECT scope_identity() id
// ";
//cmd_text = cmd_text.Replace("%poligon%", poligon);
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// cmd.Parameters.AddWithValue("@potamus_district_id", potamus_district_id);
// cmd.Parameters.AddWithValue("@region_id", region_id);
// cmd.Parameters.AddWithValue("@name", name);
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// DataTable tb = new DataTable();
// tb.Load(reader);
// reader.Close();
// connection.Close();
// district_id = Convert.ToInt64(tb.Rows[0]["id"]);
//}
//return district_id;
}
public GeolocationDBStoredProcedures.PGetUnverifiedAddressResult GetUnverifiedAddress()
{
using (var db = new DataModels.GeolocationDB())
{
LinqToDB.Data.DataConnection connect = new LinqToDB.Data.DataConnection();
connect.CommandTimeout = 100000;
GeolocationDBStoredProcedures.PGetUnverifiedAddressResult re =
DataModels.GeolocationDBStoredProcedures.PGetUnverifiedAddress(connect).FirstOrDefault();
return(re);
}
}
public IEnumerable <GeolocationDBStoredProcedures.PGetPointsByMarketRadiusResult> GetPointsByMarketRadius(int market_id, int radius)
{
//List<CardPoint> cp = new List<CardPoint>();
using (var db = new DataModels.GeolocationDB())
{
LinqToDB.Data.DataConnection connect = new LinqToDB.Data.DataConnection();
IEnumerable <DataModels.GeolocationDBStoredProcedures.PGetPointsByMarketRadiusResult> re =
(IEnumerable <DataModels.GeolocationDBStoredProcedures.PGetPointsByMarketRadiusResult>)
DataModels.GeolocationDBStoredProcedures.PGetPointsByMarketRadius(connect, market_id, radius).ToList();
//if (re.Count() > 0)
//{
// foreach (DataModels.GeolocationDBStoredProcedures.PGetPointsByMarketRadiusResult r in re)
// {
// cp.Add(new CardPoint {
// formatted_address = r.formatted_address,
// lat = r.lat.Value,
// lng = r.lng.Value,
// point_id = r.point_id.Value,
// search_engine_status = r.search_engine_status
// });
// }
//}
return(re);
}
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = "dbo.p_get_points_by_market_stat";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("@market_google_coordinates_id", SqlDbType.Int).Value = market_id;
// cmd.Parameters.Add("@radius", SqlDbType.Int).Value = radius;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// cardpoints.Add(new CardPoint
// {
// google_point_id = Convert.ToInt32(reader[0]),
// lat = Convert.ToDecimal(reader[1]),
// lng = Convert.ToDecimal(reader[2]),
// google_status = reader[3].ToString(),
// formatted_address = reader[4].ToString()
// });
// }
// connection.Close();
//}
//return cardpoints;
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <DataModels.DictDistricts> GetDistrictsByRegionId(long id)
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictDistricts.Where(w => w.RegionId == id).ToList());
}
//List<District> districts = new List<District>();
//string cmd_text = @"
// SELECT
// [district_id]
// ,[potamus_district_id]
// ,[region_id]
// ,[name]
// ,[poligon].EnvelopeCenter().Long as lat
// ,[poligon].EnvelopeCenter().Lat as lng
// ,is_centre_region
// FROM [dbo].[dict_districts] where [region_id] = {0}
// ";
//cmd_text = string.Format(cmd_text, id.ToString());
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// districts.Add(new District
// {
// district_id = Convert.ToInt32(reader[0]),
// potamus_district_id = Convert.ToInt32(reader[1]),
// region_id = Convert.ToInt32(reader[2]),
// name = reader[3].ToString(),
// lat = Convert.ToDecimal(reader[4]),
// lng = Convert.ToDecimal(reader[5]),
// is_region_center = Convert.ToBoolean(reader[6])
// });
// }
// connection.Close();
//}
//return districts;
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DataModels.DictDistricts GetDistrictById(long id)
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictDistricts.Where(w => w.DistrictId == id).FirstOrDefault());
}
//District district = new District();
//string cmd_text = @"
// SELECT Top 1
// [district_id]
// ,[potamus_district_id]
// ,[region_id]
// ,[name]
// ,[poligon].EnvelopeCenter().Long as lat
// ,[poligon].EnvelopeCenter().Lat as lng
// FROM [dbo].[dict_districts] where [district_id] = {0}
// ";
//cmd_text = string.Format(cmd_text, id.ToString());
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// district = new District
// {
// district_id = Convert.ToInt32(reader[0]),
// potamus_district_id = Convert.ToInt32(reader[1]),
// region_id = Convert.ToInt32(reader[2]),
// name = reader[3].ToString(),
// lat = Convert.ToDecimal(reader[4]),
// lng = Convert.ToDecimal(reader[5])
// };
// }
// connection.Close();
//}
//return district;
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public DataModels.DictTradeMarkets GetMarketById(long id)
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictTradeMarkets.Where(w => w.MarketId == id).FirstOrDefault());
}
//Market market = new Market();
//string cmd_text = @"
// SELECT [market_google_coordinates_id]
// ,name_short
// ,[address]
// ,[lat]
// ,[lng]
// ,[label]
// FROM [dbo].[google_market_coordinates] where market_google_coordinates_id = {0}
// ";
//cmd_text = string.Format(cmd_text, id.ToString());
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// market = new Market
// {
// market_google_coordinates_id = Convert.ToInt32(reader[0]),
// name_short = reader[1].ToString().Trim(),
// address = reader[2].ToString(),
// lat = Convert.ToDecimal(reader[3]),
// lng = Convert.ToDecimal(reader[4]),
// label = Convert.ToString(reader[5])
// };
// }
// connection.Close();
//}
//return market;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public IEnumerable <DataModels.DictTradeMarkets> GetMarkets()
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictTradeMarkets.Where(w => w.TradingNetwork == "EpicentrK" && w.PotamusMarketId != null).ToList());
}
//List<Market> markets = new List<Market>();
//string cmd_text = @"
// SELECT [market_google_coordinates_id]
// ,name_short
// ,[address]
// ,[lat]
// ,[lng]
// ,[label]
// FROM [dbo].[google_market_coordinates]
// ";
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// markets.Add(new Market
// {
// market_google_coordinates_id = Convert.ToInt32(reader[0]),
// name_short = reader[1].ToString().Trim(),
// address = reader[2].ToString(),
// lat = Convert.ToDecimal(reader[3]),
// lng = Convert.ToDecimal(reader[4]),
// label = Convert.ToString(reader[5])
// });
// }
// connection.Close();
//}
//return markets;
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public IEnumerable <DataModels.DictDistrictsCoordinates> GetDistrictCoordinates(long id)
{
using (var db = new DataModels.GeolocationDB())
{
return(db.DictDistrictsCoordinates.Where(w => w.DistrictId == id).ToList());
}
//List<DistrictCoordinate> Coordinates = new List<DistrictCoordinate>();
//string cmd_text = @"
// SELECT
// [coordinate_id]
// ,[lat]
// ,[lng]
// ,[district_id]
// FROM [dbo].[dict_districts_coordinates] where district_id = {0}
// ";
//cmd_text = string.Format(cmd_text, id.ToString());
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// connection.Open();
// SqlDataReader reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// Coordinates.Add(new DistrictCoordinate
// {
// coordinate_id = Convert.ToInt32(reader[0]),
// lat = Convert.ToDecimal(reader[1]),
// lng = Convert.ToDecimal(reader[2]),
// district_id = Convert.ToInt64(reader[3])
// });
// }
// connection.Close();
//}
//return Coordinates;
}
/// <summary>
///
/// </summary>
/// <param name="district_id"></param>
/// <param name="lat"></param>
/// <param name="lng"></param>
public void SetDistrictCoordinates(long district_id, decimal lat, decimal lng)
{
using (var db = new DataModels.GeolocationDB())
{
db.DictDistrictsCoordinates.Insert(() => new DataModels.DictDistrictsCoordinates
{
DistrictId = district_id,
Lat = lat,
Lng = lng
});
}
// string cmd_text = @"
// INSERT INTO [dbo].[dict_districts_coordinates]
// ([lat]
// ,[lng]
// ,[district_id])
// VALUES
// (@lat
// ,@lng
// ,@district_id)
// ";
//using (SqlConnection connection = new SqlConnection(this.DbConnectString))
//{
// SqlCommand cmd = connection.CreateCommand();
// cmd.CommandText = cmd_text;
// cmd.CommandType = CommandType.Text;
// cmd.Parameters.AddWithValue("@district_id", district_id);
// cmd.Parameters.AddWithValue("@lat", lat);
// cmd.Parameters.AddWithValue("@lng", lng);
// connection.Open();
// cmd.ExecuteNonQuery();
// connection.Close();
//}
}
/// <summary>
///
/// </summary>
/// <param name="market_id"></param>
/// <returns></returns>
public IEnumerable <DataModels.VCustomerPositions> GetPointsByMarketId(int market_id)
{
using (var db = new DataModels.GeolocationDB())
{
//var market = db.DictTradeMarkets.Where(w => w.MarketId == market_id).FirstOrDefault();
return(db.VCustomerPositions.Where(w => w.IssuedMarketId == market_id && w.SearchEngineStatus == "OK").ToList());
// .Select(x => new CardPoint {
// formatted_address = x.FormattedAddress,
// lat = x.Lat.Value,
// lng = x.Lng.Value,
// point_id = x.PointId.Value,
// search_engine_status = x.SearchEngineStatus
//});
}
/*
* List<CardPoint> cardpoints = new List<CardPoint>();
* string cmd_text = @"
* declare
* @potamus_market_id bigint;
* declare
* @visited int
*
* select top 1
* @potamus_market_id = potamus_market_id
* from [dbo].[google_market_coordinates] where market_google_coordinates_id={0}
*
* set @visited = {1}
*
* select
* b.[google_point_id]
* ,b.[lat]
* ,b.[lng]
* ,b.[google_status]
* ,isnull(b.[formatted_address],'') formatted_address
* from [dbo].[v_card_customers_true] a
* left join [dbo].[google_card_points] b on a.card_id = b.card_id
* inner join dbo.v_card_visited vc on a.card_id = vc.card_id
* where
* b.[google_status] = 'OK'
* and a.issued_market_id = @potamus_market_id
* and vc.visit >= iif(@visited = 0, vc.visit, @visited)
* ";
* cmd_text = string.Format(cmd_text, market_id.ToString(), visited.ToString());
* using (SqlConnection connection = new SqlConnection(this.DbConnectString))
* {
* SqlCommand cmd = connection.CreateCommand();
* cmd.CommandText = cmd_text;
* cmd.CommandType = CommandType.Text;
*
* connection.Open();
* SqlDataReader reader = cmd.ExecuteReader();
*
* while (reader.Read())
* {
* cardpoints.Add(new CardPoint
* {
* google_point_id = Convert.ToInt32(reader[0]),
* lat = Convert.ToDecimal(reader[1]),
* lng = Convert.ToDecimal(reader[2]),
* google_status = reader[3].ToString(),
* formatted_address = reader[4].ToString()
* });
* }
* connection.Close();
* }
* return cardpoints;
*/
}