GetPontsNearby.ashx

GetPontsNearby.ashx

<%@ WebHandler Language="C#" Class="GetPointsNearby" %>

using System;
using System.Web;
using System.Web.Script.Serialization;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

public class GetPointsNearby : IHttpHandler
{
    public const string connectionString = "Server='192.168.0.241'; Database='PickingPointsProviders'; User Id='fastcheckoutuser'; Password='qdeF!fiU5Ir7gO8kP9jP2qN5eG7khj';";

    public void ProcessRequest(HttpContext context)
    {
        double lat = -1;
        double lon = -1;
        double dist = -1;
        int maxResults = -1;
        bool isPostamat = false;
        string providerString = "";

        if (!string.IsNullOrEmpty(context.Request["latitude"]))
        {
            double.TryParse(context.Request["latitude"], out lat);
        }
        if (!string.IsNullOrEmpty(context.Request["longtitude"]))
        {
            double.TryParse(context.Request["longtitude"], out lon);
        }
        if (!string.IsNullOrEmpty(context.Request["distance"]))
        {
            double.TryParse(context.Request["distance"], out dist);
        }
        if (!string.IsNullOrEmpty(context.Request["maxResults"]))
        {
            int.TryParse(context.Request["maxResults"], out maxResults);
        }
        if (!string.IsNullOrEmpty(context.Request["provider"]))
        {
        	string sqlProviderQueryString;

        	providerString = context.Request["provider"];
        	string[] providerArray = providerString.Split('|');
		
			for (int i = 0; i < providerArray.Length; i++) 
			{
				providerArray[i] = "company = " + providerArray[i];
			}

			sqlProviderQueryString = string.Join(" OR ", providerArray);
        }

        if (lat == -1) return;
        if (lon == -1) return;
        if (dist == -1) return;
        if (maxResults == -1) return;

        if (!string.IsNullOrEmpty(context.Request["postamat"]))
        {
            isPostamat = true;
        }

        /*var lonMin = lon - dist / Math.Abs(Math.Cos(ConvertToRadians(lat)) * 111.0); //1 градус широты = 111 км
        var lonMax = lon + dist / Math.Abs(Math.Cos(ConvertToRadians(lat)) * 111.0);
        var latMin = lat - (dist / 111.0);
        var latMax = lat + (dist / 111.0);*/

        var points = new List<object>();
        object pointContent = new object();

        //Наша БД
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            var cmd = @"SELECT TOP(@maxResults) id, latitude, longtitude, company, postamat, station, address, worktime, phone, payment, limit, LILOC1, CODPOS FROM [PickingPointsProviders].[dbo].[Pvz_list] WHERE " + (isPostamat == true ? "postamat = 1 AND " : "postamat = 0 AND ") + (provider == "" ? "" : "@sqlProviderQueryString AND ") +" (((ACOS(SIN(RADIANS(@searchLat)) * SIN(RADIANS(latitude)) + COS(RADIANS(@searchLat)) * COS(RADIANS(latitude)) * COS(RADIANS(longtitude) - RADIANS(@searchLng)) ) * 3963.1676) <= @distance) OR (latitude = @searchLat AND longtitude = @searchLng)) ORDER BY (ACOS(SIN(RADIANS(@searchLat)) * SIN(RADIANS(latitude)) + COS(RADIANS(@searchLat)) * COS(RADIANS(latitude)) * COS(RADIANS(longtitude) - RADIANS(@searchLng)) ) * 3963.1676)";

            con.Open();
            using (SqlCommand command = new SqlCommand(cmd))
            {
                command.Connection = con;

                command.Parameters.Add(new SqlParameter("@maxResults", maxResults));
                command.Parameters.Add(new SqlParameter("@searchLat", lat));
                command.Parameters.Add(new SqlParameter("@searchLng", lon));
                command.Parameters.Add(new SqlParameter("@distance", dist));
                command.Parameters.Add(new SqlParameter("@provider", provider));

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var id = reader.GetString(reader.GetOrdinal("id"));
                        var latitude = reader.GetDouble(reader.GetOrdinal("latitude"));
                        var longtitude = reader.GetDouble(reader.GetOrdinal("longtitude"));
                        var company = reader.GetString(reader.GetOrdinal("company"));
                            
                        var station = reader.GetString(reader.GetOrdinal("station"));
                        var address = reader.GetString(reader.GetOrdinal("address"));
                        var worktime = reader.GetString(reader.GetOrdinal("worktime"));
                        string worktimeNew = string.Empty;
                        foreach(var time in Regex.Split(worktime, @"\s*,\s*|\s*\.\s+")) {
                            worktimeNew += time + "<br>";
                        }

                        var phone = reader.GetString(reader.GetOrdinal("phone"));
                        var payment = reader.GetString(reader.GetOrdinal("payment"));
                        var limit = reader.GetString(reader.GetOrdinal("limit"));
                        var postIndex = reader.GetString(reader.GetOrdinal("CODPOS"));
                        var city = reader.GetString(reader.GetOrdinal("LILOC1"));
                        
                        var CompanyName = string.Empty;
                        switch (company)
                        {
                            case "BBY":
                                CompanyName = "BoxBerry";
                                break;
                            case "CDK":
                                CompanyName = "CDEK";
                                break;
                            case "HMS":
                                CompanyName = "Hermes";
                                break;
                            case "EUR":
                                CompanyName = "Евросеть";
                                break;
                            case "SVZ":
                                CompanyName = "Связной";
                                break;
                            case "PP":
                                CompanyName = "PickPoint";
                                break;
                            case "PPP":
                                CompanyName = "PickPoint";
                                break;
                            case "PPK":
                                CompanyName = "Qiwi";
                                break;
                        }


                        points.Add(new
                        {
                            id = id,
                            latitude = latitude,
                            longtitude = longtitude,
                            city = city.Trim(),
                            postIndex = postIndex.Trim(),
                            company = company,
                            companyName = CompanyName,
                            station = (string.IsNullOrEmpty(station) ? "" : station),
                            address = (string.IsNullOrEmpty(address) ? "" : address),
                            worktime = (string.IsNullOrEmpty(worktimeNew) ? "" : worktimeNew),
                            phone = (string.IsNullOrEmpty(phone) ? "" : phone),
                            payment = (string.IsNullOrEmpty(payment) ? "" : payment),
                            limit = (string.IsNullOrEmpty(limit) ? "" : limit)
                        });
                    }
                }
            }
        }

        context.Response.ContentType = "text/json";

        var jsonSerializer = new JavaScriptSerializer();
        jsonSerializer.MaxJsonLength = Int32.MaxValue;

        context.Response.Write(jsonSerializer.Serialize(points));
    }

    public static double ConvertToRadians(double angle)
    {
        return (Math.PI / 180) * angle;
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}