Coder's Space

  ALTER PROCEDURE [dbo].[SP_UPDATEUSERLEVEL] @Userid INT AS BEGIN Declare @CountOfUserReportedTweets INT Declare @LevelBadgeId INT SET @CountOfUserReportedTweets=(select count(*) from OnGoingReports where UserId=@Userid) --CHECKS WHETHER THE LEVEL EXIST OR NOT IF EXISTS(select Top 1 * from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) BEGIN --IF LEVEL EXISTS UPDATE OR INSERT WITH THAT LEVEL ID SET @LevelBadgeId=(select Top 1 Id from LevelBadge where NoofTweets<=@CountOfUserReportedTweets order by NoofTweets desc) IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLevel SET LevelBadgeId=@LevelBadgeId WHERE UserId=@Userid END ELSE BEGIN INSERT INTO UserLevel(UserId,LevelBadgeId) VALUES (@Userid,@LevelBadgeId) END END ELSE BEGIN --IF NO LEVEL EXISTS THEN INSERTING DEFAULT LEVEL IF EXISTS(select * from UserLevel where UserId=@Userid) BEGIN UPDATE UserLeve...

 ALTER PROCEDURE [dbo].[SP_GETUSERPROFILE] @UserId int AS BEGIN --checks whether user reported any tweet or not IF EXISTS(SELECT * FROM OnGoingReports WHERE UserId=@UserId) BEGIN --if user already reported tweets then gets the user details and rank select UserId,UserName,Name,UserReportedTweetCount,Rank      from  (select UserId,UserName,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from      (select ogr.UserId,uc.UserName,Name,Count(*) UserReportedTweetCount from UserCredentials uc       inner join OnGoingReports ogr       on       ogr.UserId=uc.UserId        GROUP BY ogr.UserId,uc.Name,uc.UserName) as TopList ) as UserRank      where UserId=@UserId END ELSE BEGIN --if user not reported any tweets then it get the default value as 0 and rank based on tweets reported select UserId,UserName,Nam...

 ALTER PROCEDURE [dbo].[SP_GETTWEETSBYTOPIC_REPORTEDSTATUS] @TopicId INT, @UserId INT AS BEGIN select distinct tw.TweetId,tw.Id,tw.CreatedOn, tw.TwitterHandle,tw.TwitterProfileLink,tw.Tweet,tw.TweetedOn,tw.Rating, tw.NoofComments,tw.NoofLikes,tw.NoofReTweets,tw.IsActive, (select case when ogr.userid IS NOT NULL and ogr.TweetId IS NOT NULL then CAST(1 AS BIT) else CAST(0 AS BIT) end as isreported ) IsReported from tweets tw inner join tweettopic tt on tt.twtid=tw.id left join ongoingreports ogr on ogr.TweetId=tw.TweetId and ogr.UserId=@UserId where tt.TopicId=@TopicId and tw.IsActive=1 and  (UPPER(tw.Rating)='NEGATIVE' OR UPPER(tw.Rating)='FALSE') END

  CREATE PROCEDURE [dbo].[SP_GETLEADERBOARD]  @UserId INT  AS  BEGIN   DECLARE @IsExist Int IF EXISTS(Select * from OnGoingReports where UserId=@UserId) BEGIN SET @IsExist=1 END ELSE BEGIN SET @IsExist=0 END --gets the user name,tweetsreported,rank of top 10 users from ongoingreports table select Top 10 UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank  from (select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc inner join OnGoingReports ogr on ogr.UserId=uc.UserId  where uc.IsActive=1 GROUP BY ogr.UserId,uc.Name) TopList   UNION    --if user reported then the if block executes and calculates rank based on tweets reported select UserId,Name,UserReportedTweetCount,Rank from (select UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Ran...

  public List<CityMaster> getcities()         {             List<CityMaster> cities = new List<CityMaster>();             try             {                 var res = new List<CityMaster>();                 string path1 = $"{Directory.GetCurrentDirectory()}{@"\wwwroot\assets\citieslist.json"}";                 using (StreamReader r = new StreamReader(path1))                 {                     string json = r.ReadToEnd();                     res = JsonConvert.DeserializeObject<List<CityMaster>>(json);                 }      ...

 SELECT City, COUNT (OrgId) as NoofOrgs FROM Organisation_Address oa inner join Organisations org  on org.Id=oa.OrgId Where City!='' and Org.IsActive=1 and org.IsApproved=1 GROUP BY City 

SELECT ChildUserType.Id as CategoryId,ChildUserType.Category as CategoryName, ChildUserType.IsActive,ChildUserType.CategoryURL as CategoryURL, ChildUserType.CreatedOn,ChildUserType.ParentId, ParentUserType.Category as ChildParentname,ChildUserType.NoofOrgs FROM  [dbo].[Categories] AS ChildUserType LEFT JOIN  [dbo].[Categories] AS ParentUserType  ON  ChildUserType.ParentId = ParentUserType.Id