Map Proc

USE [pickaschool]
GO
/****** Object:  StoredProcedure [dbo].[uspSchools]    Script Date: 7/12/2015 11:37:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER Proc [dbo].[uspSchools]



@QueryType nvarchar(50)=null,
@City nvarchar(50)=null,
@Region int = null,
@SchoolID int = null,
@Type nvarchar(50)=null,
@DateTimeFinish datetime = null,
@UserID bigint = null,
@Payment numeric(18, 2) = null,
@Latitude float = null,
@KiloMeter Float = null,
@Longitude Float = null,
@Status nvarchar(50) = null,
@SchoolIDS nvarchar(50) = null,
@Search nvarchar(50) = null,
@AlotEmpID bigint = null,
@AlotdateTime datetime = null
As
begin

 if(@QueryType='Read_AllSchoolByRegion')
begin
SELECT        tblSchools.SchoolName +', ' +ISNULL( tblLocation.Location, '') AS SchoolName, tblSchools.Longitude, tblSchools.Latitude,
                         tblSchools.Teliphone, tblSchools.Region, tblSchools.SchoolType, tblLocation.Location as SchoolDescription
FROM            tblSchools LEFT OUTER JOIN
                         tblLocation ON tblSchools.LocationID = tblLocation.LocationID
WHERE        (tblSchools.Region = @Region)
end
else if(@QueryType='GetDataSchoolLocation')
begin
SELECT DISTINCT TOP (3) SchoolName AS data, '1' AS c
FROM            tblSchools
WHERE        (SchoolName LIKE '%' + @Search + '%')
union all



SELECT DISTINCT TOP (3)  City,'2' AS c
FROM            tblSchools
WHERE        (City LIKE '%' + @Search + '%')
union all



SELECT DISTINCT TOP (3)  PinCode,'3' AS c
FROM            tblSchools
WHERE        (PinCode LIKE '%' + @Search + '%')
end
else if(@QueryType='GetDataSchoolLocation')
begin
SELECT  distinct  top 3    tblSchools.SchoolName  AS data, '1' as c
FROM            tblSchools LEFT OUTER JOIN
                         tblLocation ON tblSchools.LocationID = tblLocation.LocationID
WHERE        (tblSchools.SchoolName like '%'+@Search+'%')
union all



SELECT  distinct top 3      tblLocation.Location AS data,'2' as c
FROM            tblSchools LEFT OUTER JOIN
                         tblLocation ON tblSchools.LocationID = tblLocation.LocationID
WHERE        (tblLocation.Location like '%'+@Search+'%')

end
else if(@QueryType='GetCurrentLatLong')
begin
SELECT tblSchools.LocationID
FROM            tblLocation LEFT OUTER JOIN
                         tblSchools ON tblLocation.LocationID = tblSchools.LocationID
WHERE   
                         tblSchools.SchoolName= @Search
end
--else if(@QueryType='GetCurrentLatLong')
--begin
--SELECT        top 1 tblLocation.Longitude+'~'+ tblLocation.Latiude
--FROM            tblLocation LEFT OUTER JOIN
--                         tblSchools ON tblLocation.LocationID = tblSchools.LocationID
--WHERE        (tblLocation.Location LIKE '%' +@Search + '%') OR
--                         (tblSchools.SchoolName LIKE '%' + @Search + '%')
--end
else if(@QueryType='GetDataLocation')
begin
SELECT        TOP (5) Location AS data
FROM            tblLocation
WHERE        (Location LIKE '%' + @Search + '%')
end
--else if(@QueryType='GetDataLocation')
--begin
--SELECT        TOP (5) Location AS data
--FROM            tblLocation
--WHERE        (Location LIKE '%' + @Search + '%')
--end
else if(@QueryType='GetCurrentLocation')
begin
SELECT top 1 Location, min( 3956 * 2 * ASIN(SQRT(POWER(SIN((Latiude -abs(@Latitude)) * pi()/180 / 2),2)
+ COS(Latiude * pi()/180 ) * COS(abs(@Latitude) *  pi()/180) * POWER(SIN((Longitude - abs(@Longitude)) *  pi()/180 / 2), 2))
)*1.60934) as a
FROM tblLocation
GROUP BY Location
ORDER BY a
end
else if(@QueryType='GetDataSchool')
begin
if(@Type<>'0' and @City<>'')
begin
SELECT        Longitude, Latitude, SchoolName, SchoolDescription, Address,City, Website, SchoolType, Image1, Teliphone AS MobileNo, EmailID, Image2, SID
FROM            dbo.tblSchools
                        where (City=@City or PinCode=@City) and SchoolType=@Type
end
else
 if(@Type<>'0')
begin
SELECT        Longitude, Latitude, SchoolName, SchoolDescription, Address,City, Website, SchoolType, Image1, Teliphone AS MobileNo, EmailID, Image2, SID
FROM            dbo.tblSchools
                        where SchoolType=@Type --and (City=@City or PinCode=@City)
end
else
begin
SELECT        Longitude, Latitude, SchoolName, SchoolDescription, Address,City, Website, SchoolType, Image1, Teliphone AS MobileNo, EmailID, Image2, SID
FROM            dbo.tblSchools
where City=@City or PinCode=@City or SchoolName=@City
end




--else if(@QueryType='GetDataSchool')
--begin
--SELECT        tblLocation.Location, 3956 * 2 * ASIN(SQRT(POWER(SIN((tblLocation.Latiude - ABS(@Latitude)) * PI() / 180 / 2), 2) + COS(tblLocation.Latiude * PI() / 180)
--                         * COS(ABS(@Latitude) * PI() / 180) * POWER(SIN((tblLocation.Longitude - ABS(@Longitude)) * PI() / 180 / 2), 2))) * 1.60934 AS Distance, tblSchools.Longitude,
--                         tblSchools.Latitude, tblSchools.SchoolName, tblSchools.SchoolDescription, tblSchools.Address, tblSchools.Website, tblSchools.SchoolType, tblSchools.Image1,
--                         tblSchools.Teliphone AS MobileNo, tblSchools.EmailID, tblSchools.Image2, tblSchools.SID
--FROM            tblLocation INNER JOIN
--                         tblSchools ON tblLocation.LocationID = tblSchools.LocationID
--WHERE        (3956 * 2 * ASIN(SQRT(POWER(SIN((tblLocation.Latiude - ABS(@Latitude)) * PI() / 180 / 2), 2) + COS(tblLocation.Latiude * PI() / 180) * COS(ABS(@Latitude) * PI() / 180)
--                         * POWER(SIN((tblLocation.Longitude - ABS(@Longitude)) * PI() / 180 / 2), 2))) * 1.60934 < @KiloMeter)
--ORDER BY Distance
end
else if(@QueryType='GetDataMicroSchool')
begin
SELECT        tblMicroSchool.ID, tblMicroSchool.SID, tblMicroSchool.AboutSchool, tblMicroSchool.Amenities, tblMicroSchool.Admission, tblMicroSchool.Curriculum,
                         tblMicroSchool.Teachers, tblMicroSchool.BusRoute, tblMicroSchool.More, tblMicroSchool.Image1, tblMicroSchool.Image2, tblMicroSchool.Image3,
                         tblMicroSchool.Image4, tblMicroSchool.Contact, tblSchools.SchoolName, tblSchools.SchoolDescription, tblSchools.Address, tblSchools.Website,
                         tblSchools.Teliphone  AS MobileNo, tblSchools.EmailID, tblSchools.Image3 AS Expr1, tblSchools.Image2 AS Expr2, tblSchools.Image1 AS Expr3, tblSchools.SchoolType
FROM            tblMicroSchool INNER JOIN
                         tblSchools ON tblMicroSchool.SID = tblSchools.SID
                         where  tblSchools.SID=@SchoolID
end
else if(@QueryType='GetDataCompareSchool')
begin
SELECT        dbo.tblMicroSchool.ID, dbo.tblMicroSchool.SID, dbo.tblMicroSchool.AboutSchool, dbo.tblMicroSchool.Amenities, dbo.tblMicroSchool.Admission,
                         dbo.tblMicroSchool.Curriculum, dbo.tblMicroSchool.Teachers, dbo.tblMicroSchool.BusRoute, dbo.tblMicroSchool.More, dbo.tblMicroSchool.Contact,
                         dbo.tblSchools.SchoolName, dbo.tblSchools.SchoolDescription, dbo.tblSchools.Address, dbo.tblSchools.Website, dbo.tblSchools.Teliphone AS MobileNo,
                         dbo.tblSchools.EmailID, dbo.tblSchools.Image3, dbo.tblSchools.Image2, dbo.tblSchools.Image1, dbo.tblSchools.SchoolType, dbo.tblSchools.Board,
                         dbo.tblSchools.MobileNo AS Expr1
FROM            dbo.tblMicroSchool RIGHT OUTER JOIN
                         dbo.tblSchools ON dbo.tblMicroSchool.SID = dbo.tblSchools.SID
WHERE        (dbo.tblSchools.SID IN
                             (SELECT        CONVERT(int, Value) AS Expr1
                               FROM            dbo.Split(@SchoolIDS, ',') AS Split_1))
                       
                       
                       
                         -- (SUBSTRING(@SchoolIDS, 2, LEN(@SchoolIDS)-2))
end
end