public bool AddEnglishWords(string englishWord) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Id FROM [ReachHelp].[dbo].[Language_Dictionary] WHERE Word=@englishWord)INSERT INTO [ReachHelp].[dbo].[Language_Dictionary](Word,Language_Id,Map_Id)SELECT @englishWord,1,CASE WHEN MAX(Map_Id) IS NULL THEN 1 ELSE MAX(Map_Id)+1 END FROM [ReachHelp].[dbo].[Language_Dictionary]"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@englishWord", englishWord); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { } catch (Exception ex) { } return(false); }
public UInt64 DonatedValue() { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT SUM(Amount) AS Total_Sum FROM [ReachHelp].[dbo].[Donations] WHERE Status='Success'"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { return(UInt64.Parse(gen.sqlDataReader["Total_Sum"].ToString())); //Total Sum } } } } } } catch (SqlException ex) { return(0); //Error } catch (Exception ex) { return(0); //Error } return(0); // No Value }
public bool LanguageTranslation(string englishWord, string languageId, string languageWord) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "DECLARE @mapId INT;IF NOT EXISTS(SELECT Id FROM [ReachHelp].[dbo].[Language_Dictionary] WHERE Word=@englishWord) BEGIN INSERT INTO [ReachHelp].[dbo].[Language_Dictionary](Word,Language_Id,Map_Id) SELECT @englishWord,1,CASE WHEN MAX(Map_Id) IS NULL THEN 1 ELSE MAX(Map_Id)+1 END FROM [ReachHelp].[dbo].[Language_Dictionary] SELECT @mapId = Map_Id FROM [ReachHelp].[dbo].[Language_Dictionary] WHERE Word=@englishWord INSERT INTO [ReachHelp].[dbo].[Language_Dictionary] VALUES(@languageWord,@languageId,@mapId)END ELSE BEGIN IF NOT EXISTS(SELECT Id FROM [ReachHelp].[dbo].[Language_Dictionary] WHERE Word=@languageWord AND Language_Id=@languageId)BEGIN SELECT @mapId = Map_Id FROM [ReachHelp].[dbo].[Language_Dictionary] WHERE Word=@englishWord INSERT INTO [ReachHelp].[dbo].[Language_Dictionary] VALUES(@languageWord,@languageId,@mapId) END END"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@englishWord", englishWord); gen.sqlCommand.Parameters.AddWithValue("@languageId", languageId); gen.sqlCommand.Parameters.Add("@languageWord", SqlDbType.NVarChar, 80).Value = languageWord; //gen.sqlCommand.Parameters.AddWithValue("@languageWord", languageWord); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { } catch (Exception ex) { } return(false); }
public bool AddSMSMaster(MessagingModel smsDetails) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Sender_Id FROM [ReachHelp].[dbo].[SMS_Master] WHERE Sender_Id=@senderId) BEGIN UPDATE [ReachHelp].[dbo].[SMS_Master] SET [Status]=CASE WHEN @status='Active' THEN 'Inactive' ELSE [Status] END;INSERT INTO [ReachHelp].[dbo].[SMS_Master](SMS_Provider,User_Name,Password,Sender_Id,Base_Url,Status) VALUES(@provider,@userName,@password,@senderId,@baseUrl,@status); END ELSE UPDATE [ReachHelp].[dbo].[SMS_Master] SET [Status]=@status WHERE Sender_Id=@senderId;"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@provider", smsDetails.provider); gen.sqlCommand.Parameters.AddWithValue("@baseUrl", smsDetails.baseUrl); gen.sqlCommand.Parameters.AddWithValue("@userName", smsDetails.userName); gen.sqlCommand.Parameters.AddWithValue("@senderId", smsDetails.senderId); gen.sqlCommand.Parameters.AddWithValue("@password", smsDetails.password); gen.sqlCommand.Parameters.AddWithValue("@status", smsDetails.status); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool CancelOfferRequest(OffersModel offerList) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Request_Id]=@requestId AND [Offer_Id] = @offerId) INSERT INTO [ReachHelp].[dbo].[Offer_Request_Mapping]([Request_Id],[Offer_Id],[Status]) VALUES(@requestId,@offerId,@transactionStatus);ELSE UPDATE [ReachHelp].[dbo].[Offer_Request_Mapping] SET [Status]=@status WHERE Request_Id=@requestId AND Offer_Id=@offerId;UPDATE [ReachHelp].[dbo].[My_Requests] SET [Status]=@requestStatus WHERE My_Request_Id = @requestId"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@offerId", offerList.offerId); gen.sqlCommand.Parameters.AddWithValue("@requestId", offerList.requestId); gen.sqlCommand.Parameters.AddWithValue("@transactionStatus", "Cancelled"); gen.sqlCommand.Parameters.AddWithValue("@requestStatus", "Pending"); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool InsertDonation(DonationModel donationDetails) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "INSERT INTO [ReachHelp].[dbo].[Donations] VALUES(GETDATE(),@donatedBy,@amount,@transactionId,@status)"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@donatedBy", donationDetails.donatedBy); gen.sqlCommand.Parameters.AddWithValue("@amount", donationDetails.amount); gen.sqlCommand.Parameters.AddWithValue("@transactionId", donationDetails.transactionId); gen.sqlCommand.Parameters.AddWithValue("@status", donationDetails.status); //gen.sqlCommand.Parameters.AddWithValue("@remarks", donationDetails.remarks); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool ConfigureUnits(UnitModel unitConfig) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] WHERE Sub_Category_Id=@subCategoryId AND Unit_Id=@unitId) INSERT INTO [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] VALUES(@subCategoryId,@unitId)"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@subCategoryId", unitConfig.subCategoryId); gen.sqlCommand.Parameters.AddWithValue("@unitId", unitConfig.unitId); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool RequestOffer(OffersModel offerResponse) { GenericInitialization gen = new GenericInitialization(); MessagingModel email = new MessagingModel(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Request_Id]=@requestId AND [Offer_Id] = @offerId) BEGIN INSERT INTO [ReachHelp].[dbo].[Offer_Request_Mapping]([Request_Id],[Offer_Id],[Status],[Requested_Date]) VALUES(@requestId,@offerId,@status,GETDATE());UPDATE [ReachHelp].[dbo].[My_Requests] SET [Status]=@status WHERE My_Request_Id = @requestId END"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@offerId", offerResponse.offerId); gen.sqlCommand.Parameters.AddWithValue("@requestId", offerResponse.requestId); gen.sqlCommand.Parameters.AddWithValue("@status", "Requested"); if (gen.sqlCommand.ExecuteNonQuery() > 0) { email.TriggerEmail(GetOffererDetails(offerResponse.offerId), "Request for HELP", "<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p><br><p> A request has been made for your Offer. Kindly Login to <a href=\"http://reachelp.in\">ReachHelp<a> to respond. </p></body></html>"); return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool ReviewOffer(int requestId, float rating) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "UPDATE [ReachHelp].[dbo].[Offer_Request_Mapping] SET [Rating]=@rating WHERE Request_Id=@requestId AND [Status]='Accepted'"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@requestId", requestId); gen.sqlCommand.Parameters.AddWithValue("@rating", rating); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
/// <summary> /// Description: To update the status of a user /// </summary> /// <param name="userId"></param> /// <param name="status"></param> /// <returns></returns> public bool UpdateUserStatus(string userId, string status) { try { GenericInitialization gen = new GenericInitialization(); gen.queryString = "UPDATE [ReachHelp].[dbo].[User_Register] SET [Status]=@status WHERE User_Id=@userId"; using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@userId", userId); gen.sqlCommand.Parameters.AddWithValue("@status", status); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool CancelRequest(string requestId) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "UPDATE [ReachHelp].[dbo].[My_Requests] SET [Status]='Cancelled' WHERE My_Request_Id=@requestId"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@requestId", requestId); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool ConfirmOffer(OffersModel offerResponse) { GenericInitialization gen = new GenericInitialization(); MessagingModel email = new MessagingModel(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "IF NOT EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Request_Id]=@requestId AND [Offer_Id] = @offerId) BEGIN INSERT INTO [ReachHelp].[dbo].[Offer_Request_Mapping]([Request_Id],[Offer_Id],[Status],[Confirmed_Date]) VALUES(@requestId,@offerId,@status,GETDATE());END ELSE BEGIN UPDATE [ReachHelp].[dbo].[My_Requests] SET [Status]= CASE WHEN @status='Reject' THEN 'Pending' ELSE 'Accepted' END WHERE My_Request_Id = @requestId ; UPDATE [ReachHelp].[dbo].[Offer_Request_Mapping] SET [Confirmed_Date]=GETDATE(),[Status]=CASE WHEN @status='Reject' THEN 'Pending' ELSE 'Accepted' END WHERE [Request_Id]=@requestId AND [Offer_Id] = @offerId; DECLARE @finalQuantity INT;SELECT @finalQuantity = CASE WHEN SCM.Measurable = 'Yes' AND @status='Confirm' THEN ORM.[Responded_Quantity] WHEN SCM.Measurable = 'Yes' AND @status='Reject' THEN 0 WHEN SCM.Measurable = 'No' AND @status='Confirm' THEN 0 WHEN SCM.Measurable = 'No' AND @status='Reject' THEN 0 ELSE 0 END FROM [ReachHelp].[dbo].[My_Offers] MO JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Offer_Id = MO.My_Offer_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master]SCM ON SCM.Sub_Category_Id = MO.Sub_Category_Id WHERE My_Offer_Id=@offerId AND Request_Id=@requestId; UPDATE [ReachHelp].[dbo].[My_Offers] SET Quantity = Quantity - CASE WHEN (@finalQuantity IS NOT NULL OR @finalQuantity!=0) THEN @finalQuantity ELSE 0 END WHERE My_Offer_Id=@offerId;UPDATE [ReachHelp].[dbo].[My_Offers] SET [Status] = CASE WHEN Quantity<=0 THEN 'Accepted' ELSE [Status] END WHERE My_Offer_Id=@offerId; END"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@offerId", offerResponse.offerId); gen.sqlCommand.Parameters.AddWithValue("@requestId", offerResponse.requestId); gen.sqlCommand.Parameters.AddWithValue("@status", offerResponse.offerStatus); if (gen.sqlCommand.ExecuteNonQuery() > 0) { email.TriggerEmail(GetOffererDetails(offerResponse.offerId), "Offer Confirmed", "<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p><br><p>Well Done! Your Offer has been confirmed by the requestor. Login to <a href=\"http://reachelp.in\">ReachHelp<a> for more info. </p></body></html>"); return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public bool ChangeUserPassword(string userId, string password) { try { GenericInitialization gen = new GenericInitialization(); gen.queryString = "UPDATE [ReachHelp].[dbo].[User_Register] SET [Password]=@password WHERE User_Id=@userId"; using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@userId", userId); gen.sqlCommand.Parameters.AddWithValue("@password", MD5Hash(password)); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public int GetCreditsOfUser(string userId) { GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT COUNT(Mapping_Id) AS Credits FROM [ReachHelp].[dbo].[User_Register]UR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Offered_By = UR.User_Id JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Offer_Id = MO.My_Offer_Id WHERE User_Id=@userId AND ORM.[Status]='Accepted'"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@userId", userId); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { return(Convert.ToInt32(gen.sqlDataReader["Credits"].ToString())); } } } } } catch (SqlException ex) { } return(0); }
public string sendMessage(ChatMessageModel chatMessage) { GenericInitialization gen = new GenericInitialization(); string response = "failed"; try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "INSERT INTO [ReachHelp].[dbo].[ChatMessages] (Offer_Id, Requestor_User_Id, Offerer_User_Id, Message,Sent_By,Date_Time, Status) VALUES(@offerId, @requestorUserId, @offererUserId, @message,@sentBy,GETDATE(), @status)"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@offerId", chatMessage.offerId); gen.sqlCommand.Parameters.AddWithValue("@requestorUserId", chatMessage.requestorUserId); gen.sqlCommand.Parameters.AddWithValue("@offererUserId", chatMessage.offererUserId); gen.sqlCommand.Parameters.AddWithValue("@sentBy", chatMessage.sentBy); gen.sqlCommand.Parameters.AddWithValue("@message", chatMessage.message); gen.sqlCommand.Parameters.AddWithValue("@status", "unread"); if (gen.sqlCommand.ExecuteNonQuery() > 0) { response = "success"; } } } } catch (Exception ex) { response = "failed"; } return(response); }
public List <OffersModel> AvailableOffersList(string requestId, int startValue, int noOfRows) { List <OffersModel> offersList = new List <OffersModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); //gen.queryString = "DECLARE @userId BIGINT;SELECT @userId = Requested_By FROM [ReachHelp].[dbo].[My_Requests] WHERE My_Request_Id=@requestId;IF EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Cancelled' INTERSECT SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Accepted') SELECT MO.My_Offer_Id,UR.User_Name Offered_By,ORM.Responded_Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, ORM.[Status],UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Request_Id=MR.My_Request_Id JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.My_Offer_Id=ORM.Offer_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON ORM.Responded_Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND ORM.Request_Id=@requestId AND MO.Offered_By!=@userId; ELSE SELECT MO.My_Offer_Id,UR.User_Name Offered_By,MO.Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, MO.Status,UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Category_Id=MR.Category_Id AND MO.Sub_Category_Id = MR.Sub_Category_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON MO.Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND MR.My_Request_Id=@requestId AND MR.[Status] = 'Pending' AND MO.[Status] = 'Pending' AND MO.Offered_By!=@userId;"; //gen.queryString = "DECLARE @userId BIGINT;DECLARE @tempTable TABLE(My_Offer_Id BIGINT,Offered_By NVARCHAR(MAX),Offered_By_Id BIGINT,Offered_Quantity INT,Offered_Unit VARCHAR(50),Delivery_Mode VARCHAR(50),Delivery_Address NVARCHAR(MAX),Status VARCHAR(10),Phone_Number VARCHAR(20),Email_Id VARCHAR(100));DECLARE @creditsTable TABLE(Credits INT,Offered_By BIGINT);SELECT @userId = Requested_By FROM [ReachHelp].[dbo].[My_Requests] WHERE My_Request_Id=@requestId;IF EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Cancelled' INTERSECT SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Accepted') INSERT INTO @tempTable SELECT MO.My_Offer_Id,UR.User_Name Offered_By,Offered_By AS Offered_By_Id,ORM.Responded_Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, ORM.[Status],UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Request_Id=MR.My_Request_Id JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.My_Offer_Id=ORM.Offer_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON ORM.Responded_Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND ORM.Request_Id=@requestId AND MO.Offered_By!=@userId; ELSE INSERT INTO @tempTable SELECT MO.My_Offer_Id,UR.User_Name Offered_By,Offered_By AS Offered_By_Id,MO.Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, MO.Status,UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Category_Id=MR.Category_Id AND MO.Sub_Category_Id = MR.Sub_Category_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON MO.Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND MR.My_Request_Id=@requestId AND MR.[Status] = 'Pending' AND MO.[Status] = 'Pending' AND MO.Offered_By!=@userId; INSERT INTO @creditsTable SELECT COUNT(Offered_By)AS Credits,Offered_By FROM [ReachHelp].[dbo].[User_Register]UR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Offered_By = UR.User_Id JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Offer_Id = MO.My_Offer_Id WHERE /*User_Id=2 AND*/ ORM.[Status]='Accepted' GROUP BY Offered_By;SELECT TT.*,CT.Credits FROM @tempTable TT JOIN @creditsTable CT ON TT.Offered_By_Id = CT.Offered_By"; gen.queryString = "DECLARE @userId BIGINT;DECLARE @tempTable TABLE(My_Offer_Id BIGINT,Offered_By NVARCHAR(MAX),Offered_By_Id BIGINT,Offered_Quantity INT,Offered_Unit VARCHAR(50),Delivery_Mode VARCHAR(50),Delivery_Address NVARCHAR(MAX),Status VARCHAR(10),Phone_Number VARCHAR(20),Email_Id VARCHAR(100));DECLARE @creditsTable TABLE(Credits INT,Offered_By BIGINT);SELECT @userId = Requested_By FROM [ReachHelp].[dbo].[My_Requests] WHERE My_Request_Id=@requestId;IF EXISTS(SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Cancelled' INTERSECT SELECT Mapping_Id FROM [ReachHelp].[dbo].[Offer_Request_Mapping] ORM WHERE Request_Id=@requestId AND ORM.[Status]!='Accepted') INSERT INTO @tempTable SELECT MO.My_Offer_Id,UR.User_Name Offered_By,Offered_By AS Offered_By_Id,ORM.Responded_Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, ORM.[Status],UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Request_Id=MR.My_Request_Id JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.My_Offer_Id=ORM.Offer_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON ORM.Responded_Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND ORM.Request_Id=@requestId AND MO.Offered_By!=@userId; ELSE INSERT INTO @tempTable SELECT MO.My_Offer_Id,UR.User_Name Offered_By,Offered_By AS Offered_By_Id,MO.Quantity AS Offered_Quantity,Unit_Name AS Offered_Unit,Delivery_Mode,MO.Delivery_Address, MO.Status,UR.Phone_Number,UR.Email_Id FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Category_Id=MR.Category_Id AND MO.Sub_Category_Id = MR.Sub_Category_Id JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON MO.Unit = UM.Unit_Id JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.User_Id = MO.Offered_By WHERE MO.Quantity!=0 AND MR.My_Request_Id=@requestId AND MR.[Status] = 'Pending' AND MO.[Status] = 'Pending' AND MO.Offered_By!=@userId; INSERT INTO @creditsTable SELECT COUNT(Offered_By)AS Credits,Offered_By FROM [ReachHelp].[dbo].[User_Register]UR JOIN [ReachHelp].[dbo].[My_Offers]MO ON MO.Offered_By = UR.User_Id JOIN [ReachHelp].[dbo].[Offer_Request_Mapping] ORM ON ORM.Offer_Id = MO.My_Offer_Id WHERE /*User_Id=2 AND*/ ORM.[Status]='Accepted' GROUP BY Offered_By;SELECT TT.*,CT.Credits,COUNT(*) OVER (PARTITION BY 1) AS Count FROM @tempTable TT LEFT JOIN @creditsTable CT ON TT.Offered_By_Id = CT.Offered_By ORDER BY My_Offer_Id DESC OFFSET @startValue ROWS FETCH NEXT @noOfRows ROWS ONLY"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@requestId", requestId); gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@noOfRows", noOfRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { offersList.Add(new OffersModel { offerId = gen.sqlDataReader["My_Offer_Id"].ToString(), //categoryId = gen.sqlDataReader["Category_Id"].ToString(), //categoryName = gen.sqlDataReader["Category_Name"].ToString(), //subCategoryId = gen.sqlDataReader["My_Offer_Id"].ToString(), //subCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString(), offerUnit = gen.sqlDataReader["Offered_Unit"].ToString(), offerQuantity = gen.sqlDataReader["Offered_Quantity"].ToString(), offeredByName = gen.sqlDataReader["Offered_By"].ToString(), offeredById = gen.sqlDataReader["Offered_By_Id"].ToString(), offerDeliveryMode = gen.sqlDataReader["Delivery_Mode"].ToString(), // offerDeliveryAddress = gen.sqlDataReader["Delivery_Address"].ToString(), offerStatus = gen.sqlDataReader["Status"].ToString(), phoneNumber = gen.sqlDataReader["Phone_Number"].ToString(), emailId = gen.sqlDataReader["Email_Id"].ToString(), credits = gen.sqlDataReader["Credits"].ToString(), count = gen.sqlDataReader["Count"].ToString() }); } } } } } catch (SqlException ex) { } catch (Exception ex) { } return(offersList); }
public UserModel GetUserDetails(string userId) { userId = HttpContext.Current.Session["UserId"].ToString(); UserModel userDetails = new UserModel(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT UR.[User_Id],UR.[User_Name],UR.[Middle_Name],UR.[Last_Name],UR.[Country_Code],UR.[Phone_Number],UR.[Role_Id],RM.[Role_Name],UR.[Entity_Name],UR.[Designation],UR.[Street_Address_1],UR.[Street_Address_2],UR.[Country_Id],UR.[State_Id],UR.[City_Name],UR.[Pincode],UR.[Latitude],UR.[Longitude],UR.[Registration_Date],UR.[Status],Email_Id FROM [ReachHelp].[dbo].User_Register UR LEFT JOIN [dbo].[Role_Master] RM ON UR.[Role_Id] = RM.[Role_Id] WHERE User_Id = @userId"; //SELECT UR.[User_Id],UR.[User_Name],UR.[Middle_Name],UR.[Last_Name],UR.[Country_Code],UR.[Phone_Number],UR.[Role_Id],RM.[Role_Name],UR.[Entity_Name],UR.[Designation],UR.[Street_Address_1],UR.[Street_Address_2],UR.[Country_Id],UR.[State_Id],UR.[City_Id],UR.[Pincode],UR.[Latitude],UR.[Longitude],UR.[Registration_Date],UR.[Status],Email_Id FROM [ReachHelp].[dbo].User_Register UR LEFT JOIN [dbo].[Role_Master] RM ON UR.[Role_Id] = RM.[Role_Id] WHERE User_Id = @userId using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@userId", userId); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { userDetails.userId = gen.sqlDataReader["User_Id"].ToString(); userDetails.userName = gen.sqlDataReader["User_Name"].ToString(); userDetails.middleName = gen.sqlDataReader["Middle_Name"].ToString(); userDetails.lastName = gen.sqlDataReader["Last_Name"].ToString(); userDetails.phoneNumber = gen.sqlDataReader["Phone_Number"].ToString(); userDetails.countryCode = gen.sqlDataReader["Country_Code"].ToString(); userDetails.roleId = gen.sqlDataReader["Role_Id"].ToString(); userDetails.roleName = gen.sqlDataReader["Role_Name"].ToString(); userDetails.entityName = gen.sqlDataReader["Entity_Name"].ToString(); userDetails.designation = gen.sqlDataReader["Designation"].ToString(); //userDetails.address = gen.sqlDataReader["Address"].ToString(); userDetails.streetAddress1 = gen.sqlDataReader["Street_Address_1"].ToString(); userDetails.streetAddress2 = gen.sqlDataReader["Street_Address_2"].ToString(); userDetails.countryId = gen.sqlDataReader["Country_Id"].ToString(); userDetails.stateId = gen.sqlDataReader["State_Id"].ToString(); //userDetails.cityId = gen.sqlDataReader["City_Id"].ToString(); userDetails.cityName = gen.sqlDataReader["City_Name"].ToString(); userDetails.pincode = gen.sqlDataReader["Pincode"].ToString(); userDetails.registrationDate = gen.sqlDataReader["Registration_Date"].ToString(); // status = gen.sqlDataReader["Status"].ToString() userDetails.status = gen.sqlDataReader["Status"].ToString(); userDetails.emailId = gen.sqlDataReader["Email_Id"].ToString(); userDetails.latitude = gen.sqlDataReader["Latitude"].ToString(); userDetails.longitude = gen.sqlDataReader["Longitude"].ToString(); } } } } } catch (SqlException ex) { userDetails.error = "Error"; } catch (Exception ex) { userDetails.error = "Error"; } return(userDetails); }
public List <ILatency> DetailedAverageLatency(string startDate, string endDate, string type) { List <ILatency> latency = new List <ILatency>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); if (type == "Monthly") { gen.queryString = "SELECT DATEPART(MONTH, Confirmed_Date) Number,AVG(DateDiff(s, Requested_Date, Confirmed_Date)/86400) as [Total_Days],AVG(DateDiff(s, Requested_Date,Confirmed_Date)%86400/3600) as [Total_Hours],AVG(DateDiff(s,Requested_Date,Confirmed_Date)%3600/60)as [Total_Minutes],AVG((DateDiff(s, Requested_Date, Confirmed_Date)%60)) as [Total_Seconds],YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping]WHERE Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(MONTH, Confirmed_Date),YEAR(Confirmed_Date)"; } else if (type == "Weekly") { gen.queryString = "SELECT DATEPART(wk, Confirmed_Date) Number,AVG(DateDiff(s, Requested_Date, Confirmed_Date)/86400) as [Total_Days],AVG(DateDiff(s, Requested_Date,Confirmed_Date)%86400/3600) as [Total_Hours],AVG(DateDiff(s,Requested_Date,Confirmed_Date)%3600/60)as [Total_Minutes],AVG((DateDiff(s, Requested_Date, Confirmed_Date)%60)) as [Total_Seconds],YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping]WHERE Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(wk, Confirmed_Date),YEAR(Confirmed_Date)"; } else if (type == "Daily") { gen.queryString = "SELECT DATEPART(DAY, Confirmed_Date) Number,AVG(DateDiff(s, Requested_Date, Confirmed_Date)/86400) as [Total_Days],AVG(DateDiff(s, Requested_Date,Confirmed_Date)%86400/3600) as [Total_Hours],AVG(DateDiff(s,Requested_Date,Confirmed_Date)%3600/60)as [Total_Minutes],AVG((DateDiff(s, Requested_Date, Confirmed_Date)%60)) as [Total_Seconds],YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping]WHERE Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(DAY, Confirmed_Date),YEAR(Confirmed_Date)"; } using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startDate", startDate); gen.sqlCommand.Parameters.AddWithValue("@endDate", endDate); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { latency.Add(new IndicatorsModel { number = UInt16.Parse(gen.sqlDataReader["Number"].ToString()), hours = UInt16.Parse(gen.sqlDataReader["Total_Hours"].ToString()), days = UInt16.Parse(gen.sqlDataReader["Total_Days"].ToString()), minutes = UInt16.Parse(gen.sqlDataReader["Total_Minutes"].ToString()), seconds = UInt16.Parse(gen.sqlDataReader["Total_Seconds"].ToString()) }); } } } } } } catch (SqlException ex) { return(latency); //Error } catch (Exception ex) { return(latency); //Error } return(latency); }
public List <CategoryModel> GetSubCategoryForAdmin(int startValue, int noOfRows) { List <CategoryModel> subCategoryList = new List <CategoryModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); //gen.queryString = " SELECT DISTINCT SCM.Sub_Category_Id,Sub_Category_Name,CM.Category_Id,CM.Category_Name, STUFF ((SELECT ','+UM1.Unit_Name FROM Sub_Category_Master SCM1 JOIN Category_Master CM ON CM.Category_Id = SCM1.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SCU ON SCU.Sub_Category_Id = SCM1.Sub_Category_Id JOIN [ReachHelp].[dbo].[Unit_Master] UM1 ON UM1.Unit_Id = SCU.Unit_Id WHERE SCM1.Sub_Category_Id = SCM.Sub_Category_Id FOR XML PATH ('')),1,1,'') Units, SCM.Status FROM Sub_Category_Master SCM JOIN Category_Master CM ON CM.Category_Id = SCM.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SCU ON SCU.Sub_Category_Id = SCM.Sub_Category_Id JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id = SCU.Unit_Id "; //gen.queryString = "SELECT Sub_Category_Id,Sub_Category_Name,CM.Category_Id,CM.Category_Name,SCM.Status FROM Sub_Category_Master SCM JOIN Category_Master CM ON CM.Category_Id = SCM.Category_Id"; gen.queryString = "SELECT DISTINCT SCM.Sub_Category_Id,Sub_Category_Name,CM.Category_Id,CM.Category_Name,SCM.Measurable, STUFF ((SELECT ','+UM1.Unit_Name FROM Sub_Category_Master SCM1 JOIN Category_Master CM ON CM.Category_Id = SCM1.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SCU ON SCU.Sub_Category_Id = SCM1.Sub_Category_Id JOIN [ReachHelp].[dbo].[Unit_Master] UM1 ON UM1.Unit_Id = SCU.Unit_Id WHERE SCM1.Sub_Category_Id = SCM.Sub_Category_Id FOR XML PATH ('')),1,1,'') Units, SCM.Status,COUNT(SCM.Sub_Category_Id) OVER (PARTITION BY 1) AS Count FROM Sub_Category_Master SCM JOIN Category_Master CM ON CM.Category_Id = SCM.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SCU ON SCU.Sub_Category_Id = SCM.Sub_Category_Id JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id = SCU.Unit_Id ORDER BY SCM.Sub_Category_Id DESC OFFSET @startValue ROWS FETCH NEXT @noOfRows ROWS ONLY"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { //gen.sqlCommand.Parameters.AddWithValue("@categoryId", categoryId); gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@noOfRows", noOfRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { subCategoryList.Add(new CategoryModel { subCategoryId = gen.sqlDataReader["Sub_Category_Id"].ToString(), subCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString(), categoryId = gen.sqlDataReader["Category_Id"].ToString(), categoryName = gen.sqlDataReader["Category_Name"].ToString(), units = gen.sqlDataReader["Units"].ToString(), measurable = gen.sqlDataReader["Measurable"].ToString(), subCategoryStatus = gen.sqlDataReader["Status"].ToString(), count = gen.sqlDataReader["Count"].ToString(), error = "" }); } } } } } catch (SqlException ex) { subCategoryList.Add(new CategoryModel { error = "Error" }); } catch (Exception ex) { subCategoryList.Add(new CategoryModel { error = "Error" }); } return(subCategoryList); }
public List <OffersModel> ListOffers(string categoryId, string subCategoryId) { GenericInitialization gen = new GenericInitialization(); List <OffersModel> myOffers = new List <OffersModel>(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT My_Offer_Id,Category_Name,Sub_Category_Name,Quantity,Unit,Delivery_Mode,Delivery_Address,Posted_Date,MO.[Status] FROM [ReachHelp].[dbo].[My_Offers] MO JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id=MO.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MO.Sub_Category_Id WHERE Quantity!=0 AND MO.[Status]!='Cancelled' AND MO.Category_Id=@categoryId AND MO.Sub_Category_Id=@subCategoryId"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@categoryId", categoryId); gen.sqlCommand.Parameters.AddWithValue("@subCategoryId", subCategoryId); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { myOffers.Add(new OffersModel { offerId = gen.sqlDataReader["My_Offer_Id"].ToString(), offerCategoryName = gen.sqlDataReader["Category_Name"].ToString(), offerSubCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString(), offerQuantity = gen.sqlDataReader["Quantity"].ToString(), offerUnit = gen.sqlDataReader["Unit"].ToString(), offerDeliveryMode = gen.sqlDataReader["Delivery_Mode"].ToString(), offerDeliveryAddress = gen.sqlDataReader["Delivery_Address"].ToString(), offerPostedDate = gen.sqlDataReader["Posted_Date"].ToString(), imagePath = gen.sqlDataReader["Image_Path"].ToString().Split(',').ToList(), description = gen.sqlDataReader["Description"].ToString(), offerStatus = gen.sqlDataReader["Status"].ToString(), }); } } } } } } catch (SqlException ex) { myOffers.Add(new OffersModel { error = "Error" }); } catch (Exception ex) { myOffers.Add(new OffersModel { error = "Error" }); } return(myOffers); }
public List <OffersModel> OffersReportBySubCategory(string fromDate, string toDate, string subCategoryId, int startValue, int noOFRows, int cityId) { List <OffersModel> offersReport = new List <OffersModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "DECLARE @count INT;SELECT @count=COUNT(Category_Name) FROM [ReachHelp].[dbo].[My_Offers]MO JOIN [ReachHelp].[dbo].[Category_Master]CM ON CM.Category_Id=MO.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MO.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id=MO.Unit JOIN [ReachHelp].[dbo].[User_Register]UR ON UR.User_Id = MO.Offered_By LEFT JOIN [ReachHelp].[dbo].[Offer_Request_Mapping]ORM ON ORM.Offer_Id=MO.My_Offer_Id WHERE SCM.Sub_Category_Id = @subCategoryId UR.City_Id=@cityId AND Posted_Date BETWEEN @fromDate AND @toDate;SELECT Category_Name,Sub_Category_Name,CASE WHEN ORM.Responded_Quantity IS NULL THEN MO.Quantity ELSE Responded_Quantity END Quantity,Unit_Name,User_Name AS Offered_By,MO.Status,Posted_Date,@count AS [Count] FROM [ReachHelp].[dbo].[My_Offers]MO JOIN [ReachHelp].[dbo].[Category_Master]CM ON CM.Category_Id=MO.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id=MO.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id=MO.Unit JOIN [ReachHelp].[dbo].[User_Register]UR ON UR.User_Id = MO.Offered_By LEFT JOIN [ReachHelp].[dbo].[Offer_Request_Mapping]ORM ON ORM.Offer_Id=MO.My_Offer_Id WHERE SCM.Sub_Category_Id = @subCategoryId AND UR.City_Id=@cityId AND Posted_Date BETWEEN @fromDate AND @toDate ORDER BY MO.My_Offer_Id OFFSET @startValue ROWS FETCH NEXT @noOFRows ROWS ONLY "; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@fromDate", fromDate); gen.sqlCommand.Parameters.AddWithValue("@toDate", toDate); gen.sqlCommand.Parameters.AddWithValue("@subCategoryId", subCategoryId); gen.sqlCommand.Parameters.AddWithValue("@cityId", cityId); gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@noOFRows", noOFRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { offersReport.Add(new OffersModel { offerCategoryName = gen.sqlDataReader["Category_Name"].ToString(), offerSubCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString(), offerQuantity = gen.sqlDataReader["Quantity"].ToString(), offerUnit = gen.sqlDataReader["Unit_Name"].ToString(), offeredByName = gen.sqlDataReader["Offered_By"].ToString(), offerStatus = gen.sqlDataReader["Status"].ToString(), offerPostedDate = gen.sqlDataReader["Posted_Date"].ToString(), count = gen.sqlDataReader["Count"].ToString(), }); } } } } } catch (SqlException ex) { offersReport.Add(new OffersModel { error = "Error" }); } catch (Exception ex) { offersReport.Add(new OffersModel { error = "Error" }); } return(offersReport); }
public List <RequestsModel> RequestReportBySubCategory(string fromDate, string toDate, string subCategoryId, int startValue, int noOFRows, int cityId) { List <RequestsModel> requestReport = new List <RequestsModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "DECLARE @count INT;SELECT @count=COUNT(Category_Name) FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id = MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id = MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id = MR.Unit JOIN [ReachHelp].[dbo].[User_Register]UR ON UR.User_Id = MR.Requested_By WHERE SCM.Sub_Category_Id=@subCategoryId AND UR.City_Id=@cityId AND Posted_Date BETWEEN @fromDate AND @toDate;SELECT Category_Name,Sub_Category_Name,Quantity,Unit_Name,User_Name,Posted_Date,MR.Status,@count AS [Count] FROM [ReachHelp].[dbo].[My_Requests] MR JOIN [ReachHelp].[dbo].[Category_Master] CM ON CM.Category_Id = MR.Category_Id JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Sub_Category_Id = MR.Sub_Category_Id LEFT JOIN [ReachHelp].[dbo].[Unit_Master] UM ON UM.Unit_Id = MR.Unit JOIN [ReachHelp].[dbo].[User_Register]UR ON UR.User_Id = MR.Requested_By WHERE SCM.Sub_Category_Id=@subCategoryId AND UR.City_Id=@cityId AND Posted_Date BETWEEN @fromDate AND @toDate ORDER BY MR.My_Request_Id OFFSET @startValue ROWS FETCH NEXT @noOFRows ROWS ONLY "; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@fromDate", fromDate); gen.sqlCommand.Parameters.AddWithValue("@toDate", toDate); gen.sqlCommand.Parameters.AddWithValue("@subCategoryId", subCategoryId); gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@cityid", cityId); gen.sqlCommand.Parameters.AddWithValue("@noOFRows", noOFRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { requestReport.Add(new RequestsModel { requestCategoryName = gen.sqlDataReader["Category_Name"].ToString(), requestSubCategoryName = gen.sqlDataReader["Category_Name"].ToString(), //offerSubCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString(), requestQuantity = gen.sqlDataReader["Quantity"].ToString(), requestUnit = gen.sqlDataReader["Unit_Name"].ToString(), requestedByName = gen.sqlDataReader["User_Name"].ToString(), requestStatus = gen.sqlDataReader["Status"].ToString(), requestPostedDate = gen.sqlDataReader["Posted_Date"].ToString(), count = gen.sqlDataReader["Count"].ToString() }); } } } } } catch (SqlException ex) { requestReport.Add(new RequestsModel { error = "Error" }); } catch (Exception ex) { requestReport.Add(new RequestsModel { error = "Error" }); } return(requestReport); }
public bool AddSubCategory(CategoryModel subCategoryDetails) { GenericInitialization gen = new GenericInitialization(); try { if (subCategoryDetails.subCategoryId == "" || subCategoryDetails.subCategoryId == null) // A new sub Category { gen.queryString = "DECLARE @subCategoryId INT IF NOT EXISTS (SELECT Sub_Category_Id FROM [ReachHelp].[dbo].[Sub_Category_Master] WHERE Sub_Category_Name=@subCategoryName AND Category_Id=@categoryId) BEGIN INSERT INTO [ReachHelp].[dbo].[Sub_Category_Master] VALUES(@subcategoryName,@categoryId,@measurable,'Active') SELECT @subCategoryId=SCOPE_IDENTITY() INSERT INTO [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SELECT @subCategoryId,CAST(TT.VALUE AS INT) FROM [dbo].[udf_split_first_delimiter_and_groupthem](@units,',') TT END"; } else //Update a sub category { //gen.queryString = "IF EXISTS (SELECT Sub_Category_Id FROM [ReachHelp].[dbo].[Sub_Category_Master] WHERE Sub_Category_Name=@subCategoryName AND Category_Id=@categoryId) UPDATE [ReachHelp].[dbo].[Sub_Category_Master] SET Sub_Category_Name=@subCategoryName,Category_Id=@categoryId,[Status]=@subCategoryStatus WHERE Sub_Category_Id=@subCategoryId"; gen.queryString = "DECLARE @count INT,@counter INT=1,@unitId INT; DECLARE @tempTable TABLE(Id INT IDENTITY(1,1),Unit_Id INT); IF EXISTS (SELECT Sub_Category_Id FROM [ReachHelp].[dbo].[Sub_Category_Master] WHERE Category_Id=@categoryId) UPDATE [ReachHelp].[dbo].[Sub_Category_Master] SET Sub_Category_Name=@subCategoryName,Category_Id=@categoryId,[Measurable]=@measurable,[Status]=@subCategoryStatus WHERE Sub_Category_Id=@subCategoryId; SELECT @count=COUNT(TT.VALUE) FROM [ReachHelp].[dbo].[udf_split_first_delimiter_and_groupthem](@units,',') TT; INSERT INTO @tempTable SELECT TT.VALUE FROM [ReachHelp].[dbo].[udf_split_first_delimiter_and_groupthem](@units,',') TT; WHILE(@counter<=@count) BEGIN SELECT @unitId = TT.Unit_Id FROM @tempTable TT WHERE Id=@counter; IF NOT EXISTS (SELECT mapping_id FROM [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] SCM WHERE Sub_Category_Id=@subCategoryId AND Unit_Id=@unitId) INSERT INTO [ReachHelp].[dbo].[Sub_Category_Unit_Mapping] VALUES(@subCategoryId,@unitId); SET @counter=@counter+1;END"; } using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { if (subCategoryDetails.subCategoryId == "" || subCategoryDetails.subCategoryId == null) { gen.sqlCommand.Parameters.AddWithValue("@subCategoryName", subCategoryDetails.subCategoryName); gen.sqlCommand.Parameters.AddWithValue("@subCategoryStatus", subCategoryDetails.subCategoryStatus); gen.sqlCommand.Parameters.AddWithValue("@categoryId", subCategoryDetails.categoryId); gen.sqlCommand.Parameters.AddWithValue("@units", subCategoryDetails.units); gen.sqlCommand.Parameters.AddWithValue("@measurable", subCategoryDetails.measurable); } else { gen.sqlCommand.Parameters.AddWithValue("@subCategoryId", subCategoryDetails.subCategoryId); gen.sqlCommand.Parameters.AddWithValue("@subCategoryName", subCategoryDetails.subCategoryName); gen.sqlCommand.Parameters.AddWithValue("@subCategoryStatus", subCategoryDetails.subCategoryStatus); gen.sqlCommand.Parameters.AddWithValue("@units", subCategoryDetails.units); gen.sqlCommand.Parameters.AddWithValue("@categoryId", subCategoryDetails.categoryId); gen.sqlCommand.Parameters.AddWithValue("@measurable", subCategoryDetails.measurable); } LanguageModel addLanguage = new LanguageModel(); addLanguage.AddEnglishWords(subCategoryDetails.subCategoryName); if (gen.sqlCommand.ExecuteNonQuery() > 0) { return(true); } } } } catch (SqlException ex) { return(false); } catch (Exception ex) { return(false); } return(false); }
public List <CategoryModel> ListCategoryAndSubCategory() { // Hashtable category = new Hashtable(); List <CategoryModel> categoryList = new List <CategoryModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT CM.Category_Id,CM.Category_Name,SCM.Sub_Category_Id,SCM.Sub_Category_Name FROM [ReachHelp].[dbo].[Category_Master] CM JOIN [ReachHelp].[dbo].[Sub_Category_Master] SCM ON SCM.Category_Id=CM.Category_Id WHERE SCM.Status='Active' AND CM.Status='Active' ORDER BY CM.Category_Id"; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { int index = -1; while (gen.sqlDataReader.Read()) { if (!Array.Exists(categoryList.ToArray(), category => category.categoryName == gen.sqlDataReader["Category_Name"].ToString())) { index++; categoryList.Add(new CategoryModel { categoryId = gen.sqlDataReader["Category_Id"].ToString(), categoryName = gen.sqlDataReader["Category_Name"].ToString() }); } categoryList[index].subCategoryList.Add(new SubCategory { subCategoryId = gen.sqlDataReader["Sub_Category_Id"].ToString(), subCategoryName = gen.sqlDataReader["Sub_Category_Name"].ToString() }); } } } } } catch (SqlException ex) { categoryList.Add(new CategoryModel { error = "Error" }); } catch (Exception ex) { categoryList.Add(new CategoryModel { error = "Error" }); } return(categoryList); }
public List <CategoryModel> ListCategoryForAdmin(int startValue, int noOfRows) { List <CategoryModel> categoryList = new List <CategoryModel>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT [Category_Id],[Category_Name],[Status],COUNT(Category_Id) OVER (PARTITION BY 1) AS Count FROM [ReachHelp].[dbo].[Category_Master]"; if (noOfRows > 0) { gen.queryString += " ORDER BY Category_Id DESC OFFSET @startValue ROWS FETCH NEXT @noOfRows ROWS ONLY"; } using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@noOfRows", noOfRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { categoryList.Add(new CategoryModel { categoryId = gen.sqlDataReader["Category_Id"].ToString(), categoryName = gen.sqlDataReader["Category_Name"].ToString(), categoryStatus = gen.sqlDataReader["Status"].ToString(), count = gen.sqlDataReader["Count"].ToString() }); } } } } } catch (SqlException ex) { categoryList.Add(new CategoryModel { error = "Error" }); } catch (Exception ex) { categoryList.Add(new CategoryModel { error = "Error" }); } return(categoryList); }
public List <IRatings> DetailedRatings(string startDate, string endDate, string type) { List <IRatings> ratings = new List <IRatings>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); if (type == "Monthly") { gen.queryString = "SELECT DATEPART(MONTH, Confirmed_Date) Number,AVG(Rating) AS Rating,YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Status]='Accepted' AND Rating IS NOT NULL AND Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(MONTH, Confirmed_Date),YEAR(Confirmed_Date) "; } else if (type == "Weekly") { gen.queryString = "SELECT DATEPART(WK, Confirmed_Date) Number,AVG(Rating) AS Rating,YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Status]='Accepted' AND Rating IS NOT NULL AND Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(WK, Confirmed_Date),YEAR(Confirmed_Date)"; } else if (type == "Daily") { gen.queryString = "SELECT DATEPART(DAY, Confirmed_Date) Number,AVG(Rating) AS Rating,YEAR(Confirmed_Date) AS YEAR FROM [ReachHelp].[dbo].[Offer_Request_Mapping] WHERE [Status]='Accepted' AND Rating IS NOT NULL AND Confirmed_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(DAY, Confirmed_Date),YEAR(Confirmed_Date) "; } using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startDate", startDate); gen.sqlCommand.Parameters.AddWithValue("@endDate", endDate); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { ratings.Add(new IndicatorsModel { number = UInt16.Parse(gen.sqlDataReader["Number"].ToString()), ratingCount = UInt16.Parse(gen.sqlDataReader["Rating"].ToString()) }); } } } } } } catch (SqlException ex) { return(ratings); //Error } catch (Exception ex) { return(ratings); //Error } return(ratings); }
public List <IDonations> DonationDetails(string startDate, string endDate, string type) { List <IDonations> donation = new List <IDonations>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); if (type == "Monthly") { gen.queryString = " SELECT DATEPART(MONTH, Donation_Date) Number,SUM(Amount) AS Amount,YEAR(Donation_Date) AS Year FROM [ReachHelp].[dbo].[Donations] WHERE [Status]='Accepted' AND Amount IS NOT NULLAND Donation_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(month, Donation_Date),YEAR(Donation_Date) "; } else if (type == "Weekly") { gen.queryString = " SELECT DATEPART(WK, Donation_Date) Number,SUM(Amount) AS Amount,YEAR(Donation_Date) AS Year FROM [ReachHelp].[dbo].[Donations] WHERE [Status]='Accepted' AND Amount IS NOT NULLAND Donation_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(WK, Donation_Date),YEAR(Donation_Date)"; } else if (type == "Daily") { gen.queryString = " SELECT DATEPART(DAY, Donation_Date) Number,SUM(Amount) AS Amount,YEAR(Donation_Date) AS Year FROM [ReachHelp].[dbo].[Donations] WHERE [Status]='Accepted' AND Amount IS NOT NULLAND Donation_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(DAY, Donation_Date),YEAR(Donation_Date)"; } using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startDate", startDate); gen.sqlCommand.Parameters.AddWithValue("@endDate", endDate); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { donation.Add(new DonationModel { number = UInt16.Parse(gen.sqlDataReader["Number"].ToString()), amount = float.Parse(gen.sqlDataReader["Amount"].ToString()) }); } } } } } } catch (SqlException ex) { return(donation); //Error } catch (Exception ex) { return(donation); //Error } return(donation); }
public List <IUsers> UserDetails(string startDate, string endDate, string type) { List <IUsers> users = new List <IUsers>(); GenericInitialization gen = new GenericInitialization(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); if (type == "Monthly") { gen.queryString = "SELECT DATEPART(MONTH, Registration_Date) Number,COUNT(User_Id) AS User_Count,YEAR(Registration_Date) AS Year FROM [ReachHelp].[dbo].[User_Register] WHERE Registration_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(MONTH, Registration_Date),YEAR(Registration_Date)"; } else if (type == "Weekly") { gen.queryString = "SELECT DATEPART(WK, Registration_Date) Number,COUNT(User_Id) AS User_Count,YEAR(Registration_Date) AS Year FROM [ReachHelp].[dbo].[User_Register] WHERE Registration_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(WK, Registration_Date),YEAR(Registration_Date)"; } else if (type == "Daily") { gen.queryString = "SELECT DATEPART(WK, Registration_Date) Number,COUNT(User_Id) AS User_Count,YEAR(Registration_Date) AS Year FROM [ReachHelp].[dbo].[User_Register] WHERE Registration_Date BETWEEN CAST(@startDate AS DATE) AND CAST(@endDate AS DATE)GROUP BY DATEPART(WK, Registration_Date),YEAR(Registration_Date)"; } using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startDate", startDate); gen.sqlCommand.Parameters.AddWithValue("@endDate", endDate); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { users.Add(new UserModel { number = UInt16.Parse(gen.sqlDataReader["Number"].ToString()), userCount = UInt16.Parse(gen.sqlDataReader["User_Count"].ToString()) }); } } } } } } catch (SqlException ex) { return(users); //Error } catch (Exception ex) { return(users); //Error } return(users); }
public List <IDonations> DonationDetails(DonationModel donationDetails) { GenericInitialization gen = new GenericInitialization(); List <IDonations> donationInfo = new List <IDonations>(); try { using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); gen.queryString = "SELECT [Sl_No],[Donation_Date],UR.[User_Id],UR.[User_Name],[Amount],[Transaction_Id],[Status] FROM [ReachHelp].[dbo].[Donations]D JOIN [ReachHelp].[dbo].[User_Register]UR ON UR.User_Id=D.User_Id WHERE [User_Id] = CASE @donatedBy='' THEN [User_Id] ELSE @userId END "; using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@donatedBy", donationDetails.donatedBy); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { if (gen.sqlDataReader.HasRows) { while (gen.sqlDataReader.Read()) { donationInfo.Add(new DonationModel { donationDate = gen.sqlDataReader["Donation_Date"].ToString(), donatedBy = Int64.Parse(gen.sqlDataReader["User_Id"].ToString()), amount = Int64.Parse(gen.sqlDataReader["Amount"].ToString()), transactionId = gen.sqlDataReader["Transaction_Id"].ToString(), status = gen.sqlDataReader["Status"].ToString(), userName = gen.sqlDataReader["User_Name"].ToString(), donationId = gen.sqlDataReader["Sl_No"].ToString() }); } } } } } } catch (SqlException ex) { donationInfo.Add(new DonationModel { error = "Error" }); } catch (Exception ex) { donationInfo.Add(new DonationModel { error = "Error" }); } return(donationInfo); }
public List <InfoLinksModel> ListInfoLinksAdmin(int startValue, int noOFRows) { GenericInitialization gen = new GenericInitialization(); List <InfoLinksModel> infoLinkList = new List <InfoLinksModel>(); try { gen.queryString = "DECLARE @count INT;SELECT @count=COUNT(Info_Links_Id) FROM Info_Links;SELECT Info_Links_Id,Title,URL,Added_By,User_Name AS Added_By_Name,IL.[Status], IL.Added_Date,@count AS [Count] FROM Info_Links IL JOIN [ReachHelp].[dbo].[User_Register] UR ON UR.user_Id=IL.Added_By ORDER BY Info_Links_Id DESC OFFSET @startValue ROWS FETCH NEXT @noOFRows ROWS ONLY"; using (gen.sqlConnection = new SqlConnection(gen.connectionString)) { gen.sqlConnection.Open(); using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection)) { gen.sqlCommand.Parameters.AddWithValue("@startValue", startValue); gen.sqlCommand.Parameters.AddWithValue("@noOFRows", noOFRows); using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader()) { while (gen.sqlDataReader.Read()) { infoLinkList.Add(new InfoLinksModel { infoLinkId = gen.sqlDataReader["Info_Links_Id"].ToString(), infoLinkTitle = gen.sqlDataReader["Title"].ToString(), infoLinkUrl = gen.sqlDataReader["URL"].ToString(), infoLinkAddedById = gen.sqlDataReader["Added_By"].ToString(), infoLinkAddedByName = gen.sqlDataReader["Added_By_Name"].ToString(), infoLinkstatus = gen.sqlDataReader["Status"].ToString(), infoLinkDate = gen.sqlDataReader["Added_Date"].ToString(), count = gen.sqlDataReader["Count"].ToString() }); } } } } } catch (SqlException ex) { infoLinkList.Add(new InfoLinksModel { error = "Error" }); } catch (Exception ex) { infoLinkList.Add(new InfoLinksModel { error = "Error" }); } return(infoLinkList); }