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);
}