public string Post([FromBody] RequestFindShops req)
        {
            string json = "";

            try
            {
                ResponseFindShops resp = new ResponseFindShops();
                resp.GetFindShops(Constants.connectDB, req);
                json = JsonConvert.SerializeObject(resp);
            }
            catch (Exception ex)
            {
                json += " err " + ex.Message;
            }


            return(json);
        }
Esempio n. 2
0
        public void GetFindShops(string connectionString, RequestFindShops req)
        {
            //using (SqlConnection connection = new SqlConnection(connectionString))
            SqlConnection connection = new SqlConnection(connectionString);

            Shops_short = new List <Shop_short>();

            ////////////
            // ОБРАБОТКА ДАННЫХ ПЕРЕД ИНСЕРТОМ
            ///////////

            string idTypesShop    = "";
            int    intBuyCard     = 0;
            int    intDiscontCard = 0;
            int    TimeWayMetro   = req.TimeWayMetro;
            string idKategor      = "";
            string idMetro        = "";
            string id_City        = req.ID_City;//"6d0eb5f2-01fd-411b-9cf2-318a60b22604";// Москва

            if (req.geoPhone == null)
            {
                req.geoPhone = new Geo {
                    X = 0, Y = 0
                }
            }
            ;

            if (req.kategor != null)
            {
                idKategor = req.kategor.ID_KATEGOR;
            }

            // тип магазина
            //for (int i=0; i< req.type_shop.Length; i++)
            //{
            //    if (i > 0) idTypesShop += ",";
            //    idTypesShop += "'" + req.type_shop[i].ID_TYPE_SHOP + "'";
            //}

            switch (req.type_shop_012)
            {
            case 0: idTypesShop = "'340eb5f2-0ffd-411b-9cf2-318a60b22604','350eb5f2-0ffd-411b-9cf2-318a60b22604'"; break;

            case 1: idTypesShop = "'340eb5f2-0ffd-411b-9cf2-318a60b22604'"; break;

            case 2: idTypesShop = "'350eb5f2-0ffd-411b-9cf2-318a60b22604'"; break;
            }

            // станции метро, если список не пустой
            if (req.metro != null && req.metro.Count > 0)
            {
                for (int i = 0; i < req.metro.Count; i++)
                {
                    if (i > 0)
                    {
                        idMetro += ",";
                    }
                    idMetro += "'" + req.metro[i].ID_metro + "'";
                }
            }

            if (req.buy_card)
            {
                intBuyCard = 1;
            }
            if (req.DISCONT_CARD)
            {
                intDiscontCard = 1;
            }

            // подготовка к вставке в блок where
            string whereID_TYPE_SHOP = " AND sh.ID_TYPE_SHOP in (" + idTypesShop + ")";
            string whereTimeWayMetro = " AND sh.TimeWayMetro<=" + TimeWayMetro.ToString();
            string whereID_kategor   = " AND itk.ID_kategor = '" + idKategor + "'";
            string whereID_metro     = " AND met.ID_metro in (" + idMetro + ")";
            string whereBuy_card     = " AND sh.BuyCard=" + intBuyCard.ToString();
            string whereDISCONT_CARD = " AND sh.DISCONT_CARD=" + intDiscontCard.ToString();
            string whereID_CIty      = "AND sh.ID_City='" + id_City + "' -- Москва  ";

            // если флаг стоит что поиск по всем станциям, то выборку по поределенным станциям убираем
            if (req.AllMetro)
            {
                whereID_metro = "";
            }

            // параметр отвечает за кол-во минут ходьбы от метро до организации
            if (TimeWayMetro <= 0)
            {
                whereTimeWayMetro = "";
            }

            // если оплаты за безнал нет, то тогда выводим все магазины, если есть то выводим только магазины с оплатой по карте
            if (intBuyCard == 0)
            {
                whereBuy_card = "";
            }
            //если нету дисконтной карты, то выводим все магазины
            if (intDiscontCard == 0)
            {
                whereDISCONT_CARD = "";
            }

            if (idKategor == "")
            {
                whereID_kategor = "";
            }

            if (String.IsNullOrEmpty(id_City))
            {
                whereID_CIty = "";
            }

            #region SQL query
            string sqlFirstShopUnion = @"
                    SELECT 
                    sh.ID_shop,
                    sh.Type,
                    sh.Name,
                    sh.TimeWayMetro,
                    sh.Phone,
                    
                    met.ID_Metro,
                    met.station,
                    met.Color_hex,
                    
                    shgeo.X,
                    shgeo.Y,
                    
                    (
                        SELECT ISNULL(sum(CC.Count_star),0) AS avg_star FROM SPAVREMONT.Comment_client CC 
                        WHERE CC.ID_SHOP=sh.ID_Shop
                    ) AS AVG_Star,
                    (
                        SELECT count(*) AS count_feedback FROM SPAVREMONT.Comment_client CC 
                        WHERE CC.ID_SHOP=sh.ID_Shop
                            AND CC.visible=1
                    ) AS Count_feedback,

                    sh.IMG_LOGO

                    
                    

                    FROM SPAVREMONT.SHOP sh
                     JOIN SPAVREMONT.ITEMS_SHOP its ON sh.ID_Shop=its.ID_Shop
                     JOIN SPAVREMONT.ITEM_KATEGOR itk ON its.ID_ITEM_KATEGOR=itk.ID_ITEM_KATEGOR
                     JOIN SPAVREMONT.KATEGOR kat ON itk.ID_KATEGOR=kat.ID_KATEGOR
                     --JOIN SPAVREMONT.ITEM itm ON itk.ID_ITEM=itm.ID_ITEM
                     LEFT JOIN SPAVREMONT.Metro met ON met.ID_Metro=sh.ID_metro
                     LEFT JOIN SPAVREMONT.Geo shgeo ON shgeo.ID_Geo=sh.ID_Geo
                    WHERE sh.ID_SHOP='6d0eb5f2-0ffd-411b-9cf2-318260b22604'
                        AND sh.VISIBLE=1
                             " + whereID_CIty + @"
                        " + whereID_TYPE_SHOP + @"
                        " + whereBuy_card + @" 
                        " + whereTimeWayMetro + @" 
                        " + whereID_kategor + @"
                        " + whereID_metro + @"
                        " + whereDISCONT_CARD + @"
                    GROUP BY sh.ID_shop,
                    sh.Type,
                    sh.Name,
                    sh.TimeWayMetro, 
                    sh.Phone,
                    
                    met.ID_Metro,
                    met.station,
                    met.Color_hex,
                    
                    shgeo.X,
                    shgeo.Y,

                    sh.IMG_LOGO
                    
                    UNION ";

            string sqlExpression = sqlFirstShopUnion + @"
                    SELECT 
                    sh.ID_shop,
                    sh.Type,
                    sh.Name,
                    sh.TimeWayMetro,
                    sh.Phone,
                    
                    met.ID_Metro,
                    met.station,
                    met.Color_hex,

                    shgeo.X,
                    shgeo.Y,
                    
                    (
                        SELECT ISNULL(sum(CC.Count_star),0) AS avg_star FROM SPAVREMONT.Comment_client CC 
                        WHERE CC.ID_SHOP=sh.ID_Shop
                    ) AS AVG_Star,
                    (
                        SELECT count(*) AS count_feedback FROM SPAVREMONT.Comment_client CC 
                        WHERE CC.ID_SHOP=sh.ID_Shop
                            AND CC.visible=1
                    ) AS Count_feedback   ,

                    sh.IMG_LOGO                 
                    
                    FROM SPAVREMONT.SHOP sh
                     JOIN SPAVREMONT.ITEMS_SHOP its ON sh.ID_Shop=its.ID_Shop
                     JOIN SPAVREMONT.ITEM_KATEGOR itk ON its.ID_ITEM_KATEGOR=itk.ID_ITEM_KATEGOR
                     JOIN SPAVREMONT.KATEGOR kat ON itk.ID_KATEGOR=kat.ID_KATEGOR                     
                     LEFT JOIN SPAVREMONT.Metro met ON met.ID_Metro=sh.ID_metro
                     LEFT JOIN SPAVREMONT.Geo shgeo ON shgeo.ID_Geo=sh.ID_Geo
                    WHERE 1=1
                        AND sh.VISIBLE=1
                             " + whereID_CIty + @"
                        AND sh.ID_SHOP<>'6d0eb5f2-0ffd-411b-9cf2-318260b22604'
                        " + whereID_TYPE_SHOP + @"
                        " + whereBuy_card + @" 
                        " + whereTimeWayMetro + @" 
                        " + whereID_kategor + @"
                        " + whereID_metro + @"
                        " + whereDISCONT_CARD + @"

                    GROUP BY sh.ID_shop,
                      sh.Type,
                      sh.Name,
                      sh.TimeWayMetro,
                      sh.Phone,
                      
                      met.ID_Metro,
                      met.station,
                      met.Color_hex,

                      shgeo.X,
                      shgeo.Y,

                    sh.IMG_LOGO


                    ";

            #endregion

            connection.Open();
            SqlCommand command = new SqlCommand();
            command.CommandText = sqlExpression;
            command.Connection  = connection;
            SqlDataReader reader = command.ExecuteReader();

            Shop_short shop_ShortFirst = new Shop_short();
            int        tmp_count       = 0;

            if (reader.HasRows)     // если есть данные
            {
                //int genreIDIndex = reader.GetOrdinal("GenreID");
                //...
                //while...
                //GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
                int sID_shopIndex       = reader.GetOrdinal("ID_shop");
                int sTypeIndex          = reader.GetOrdinal("Type");
                int sNameIndex          = reader.GetOrdinal("Name");
                int sPhoneIndex         = reader.GetOrdinal("Phone");
                int sTimeWayMetroIndex  = reader.GetOrdinal("TimeWayMetro");
                int mID_MetroIndex      = reader.GetOrdinal("ID_Metro");
                int mstationIndex       = reader.GetOrdinal("station");
                int mColor_hexIndex     = reader.GetOrdinal("Color_hex");
                int AVG_StarIndex       = reader.GetOrdinal("AVG_Star");
                int Count_feedbackIndex = reader.GetOrdinal("Count_feedback");

                int shIMG_LOGO_index = reader.GetOrdinal("IMG_LOGO");

                //int katKategorIndex = reader.GetOrdinal("ID_KATEGOR");
                //int shgeoXIndex = reader.GetOrdinal("X");
                //int shgeoYIndex = reader.GetOrdinal("Y");


                while (reader.Read())     // построчно считываем данные
                {
                    Metro metro = new Metro
                    {
                        ID_metro  = reader.IsDBNull(mID_MetroIndex) ? "" : reader.GetString(mID_MetroIndex),
                        Station   = reader.IsDBNull(mstationIndex) ? "Не указано" : reader.GetString(mstationIndex),
                        Color_Hex = reader.IsDBNull(mColor_hexIndex) ? "#CFD8DC" : reader.GetString(mColor_hexIndex)
                    };


                    Geo geoShop = new Geo
                    {
                        X = 0,   //(float)reader.GetDouble(shgeoXIndex),
                        Y = 0    //(float)reader.GetDouble(shgeoYIndex)
                    };



                    // Вывести подсчет дистанции между двумя точками
                    float  distanse       = -1;
                    double distanseResult = 0;
                    //float xPhone = req.geoPhone.X;
                    // float yPhone = req.geoPhone.Y;

                    //distanse = (xPhone - geoShop.X) * (xPhone - geoShop.X) + (yPhone - geoShop.Y) * (yPhone - geoShop.Y);
                    //distanseResult = Math.Sqrt(distanse);


                    // подсчет среднего кол-во звезд
                    double _avg_star       = reader.IsDBNull(AVG_StarIndex) ? 0 : (double)reader.GetInt32(AVG_StarIndex);
                    int    _Count_feedback = reader.IsDBNull(Count_feedbackIndex) ? 0 : reader.GetInt32(Count_feedbackIndex);

                    int tmp = _Count_feedback;
                    if (_Count_feedback == 0)
                    {
                        tmp = 1;
                    }
                    _avg_star = _avg_star / tmp;    // округление до2  знаков


                    Shop_short item = new Shop_short
                    {
                        ID_shop        = reader.GetString(sID_shopIndex),
                        Type           = reader.GetString(sTypeIndex),
                        Name           = reader.GetString(sNameIndex),
                        Metro          = metro,
                        AVG_Star       = Math.Round(_avg_star, 1),
                        Count_feedback = _Count_feedback,
                        Geo            = geoShop,
                        distanse       = distanseResult,
                        // id_kateg = reader.GetString(katKategorIndex),
                        Phone        = reader.IsDBNull(sPhoneIndex) ? null : reader.GetString(sPhoneIndex),
                        TimeWayMetro = reader.GetInt32(sTimeWayMetroIndex),
                        IMG_LOGO     = reader.IsDBNull(shIMG_LOGO_index) ? "": reader.GetString(shIMG_LOGO_index)
                    };

                    if (tmp_count == 0)
                    {
                        shop_ShortFirst = item;
                    }
                    else
                    {
                        Shops_short.Add(item);
                    }

                    tmp_count++;
                }
            }


            //distanseSortComparer dsc = new distanseSortComparer();

            //Shops_short.Sort(dsc);

            if (tmp_count > 0)
            {
                Shops_short.Insert(0, shop_ShortFirst);
            }
            // по желанию заказчика, если магазин заказчика выводится в списке, он должен быть на первом месте



            //}//return shops;
        }
    }