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 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 forgotUserPassword(string phoneno)
{
MessagingModel otpGeneration = new MessagingModel();
string otp = otpGeneration.GenerateOTP();
try
{
GenericInitialization gen = new GenericInitialization();
gen.queryString = "UPDATE [ReachHelp].[dbo].[User_Register] SET [Password]=@password,[Status]='New' WHERE (Phone_Number=@phoneno OR Email_Id=@phoneno)";
using (gen.sqlConnection = new SqlConnection(gen.connectionString))
{
gen.sqlConnection.Open();
using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection))
{
gen.sqlCommand.Parameters.AddWithValue("@phoneno", phoneno);
gen.sqlCommand.Parameters.AddWithValue("@password", MD5Hash(otp));
if (gen.sqlCommand.ExecuteNonQuery() > 0)
{
/*if(phoneno.Contains('@')){*/
string destEmail = phoneno;
otpGeneration.TriggerEmail(destEmail, "User", "Password Reset for Forgot Password", "<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p> <br> <p> Your password has been reset. Your Temporary Password is \"<b>" + otp + "</b>\". Kindly visit <a href=\"http://reachelp.in\">ReachHelp<a> to login.</p> </body></html>");
/*}
* else{
* otpGeneration.TriggerSMS("Your Temporary Password is " + otp + ". Login with your Email/Phone No. using this Temporary Password .", phoneno);
* }*/
return(true);
}
}
}
}
catch (SqlException ex)
{
return(false);
}
catch (Exception ex)
{
return(false);
}
return(false);
}
public bool RespondToRequest(RequestsModel requestResponse)
{
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) INSERT INTO [ReachHelp].[dbo].[Offer_Request_Mapping](Offer_Id,Request_Id,[Responded_Quantity],[Responded_Unit],[Status],[Responded_Date]) VALUES(@offerId,@requestId,@respondedQuantity,@respondedUnit,@status,GETDATE()); ELSE UPDATE [ReachHelp].[dbo].[Offer_Request_Mapping] SET [Responded_Date]=GETDATE(),[Responded_Quantity]=@respondedQuantity,[Responded_Unit]=@respondedUnit,[Status]=CASE WHEN @status='Reject' THEN 'Rejected' WHEN @status='Respond' THEN 'Respond' ELSE [Status] END WHERE Request_Id=@requestId AND Offer_Id=@offerId; UPDATE [ReachHelp].[dbo].[My_Requests] SET [Status] = CASE WHEN @status = 'Reject' THEN 'Pending' ELSE 'Respond' END WHERE My_Request_Id=@requestId";
using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection))
{
gen.sqlCommand.Parameters.AddWithValue("@requestId", requestResponse.requestId);
gen.sqlCommand.Parameters.AddWithValue("@offerId", requestResponse.offerId);
gen.sqlCommand.Parameters.AddWithValue("@respondedQuantity", requestResponse.requestQuantity);
gen.sqlCommand.Parameters.AddWithValue("@respondedUnit", requestResponse.requestUnit);
gen.sqlCommand.Parameters.AddWithValue("@status", requestResponse.requestStatus);
if (gen.sqlCommand.ExecuteNonQuery() > 0)
{
email.TriggerEmail(GetRequestorDetails(requestResponse.requestId), "Response for your request", "<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p><br><p> A response has been made for your request. 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);
}
/// <summary>
/// Description: To add or update a user details
/// </summary>
/// <param name="userDetails"></param>
/// <returns></returns>
public bool RegisterUser(UserModel userDetails)
{
try
{
GenericInitialization gen = new GenericInitialization();
MessagingModel otpGeneration = new MessagingModel();
string otp = otpGeneration.GenerateOTP();
//gen.connectionString = "Data Source=202.141.20.10; User Id=apcoglocal; Password=apcoglocal123; Initial Catalog =ReachHelp;";
//gen.queryString = "IF EXISTS(SELECT User_Id FROM [ReachHelp].[dbo].[User_Register] WHERE Phone_Number=@phoneNumber) BEGIN UPDATE [ReachHelp].[dbo].[User_Register] SET User_Name=@userName,Entity_Name=@entityName,Designation=@designation,Address=@address,Country_Id=@countryId,State_Id=@stateId,City_Id=@cityId,Pincode=@pinCode,Latitude=@latitude,Longitude=@longitude WHERE Phone_Number=@phoneNumber;SELECT 'Update' AS [Status];END ELSE BEGIN INSERT INTO [ReachHelp].[dbo].[User_Register](User_Name,Phone_Number,Password,Role_Id,Entity_Name,Designation,Address,Country_Id,State_Id,City_Id,Pincode,Latitude,Longitude,Status) VALUES(@userName,@phoneNumber,@password,@roleId,@entityName,@designation,@address,@countryId,@stateId,@cityId,@pinCode,@latitude,@longitude,'New');SELECT 'Insert' AS [Status]; END";
//gen.queryString = "IF EXISTS(SELECT User_Id FROM [ReachHelp].[dbo].[User_Register] WHERE Phone_Number=@phoneNumber) BEGIN UPDATE [ReachHelp].[dbo].[User_Register] SET User_Name=@userName,Entity_Name=@entityName,Designation=@designation,Address=@address,Country_Id=@countryId,State_Id=@stateId,City_Id=@cityId,Pincode=@pinCode,Latitude=@latitude,Longitude=@longitude WHERE Phone_Number=@phoneNumber;SELECT 'Update' AS [Status];END ELSE BEGIN INSERT INTO [ReachHelp].[dbo].[User_Register](User_Name,Phone_Number,Password,Role_Id,Entity_Name,Designation,Address,Country_Id,State_Id,City_Id,Pincode,Latitude,Longitude,Status) VALUES(@userName,@phoneNumber,@password,@roleId,@entityName,@designation,@address,@countryId,@stateId,@cityId,@pinCode,@latitude,@longitude,'New');SELECT 'New' AS [Status]; END";
gen.queryString = "IF EXISTS(SELECT User_Id FROM [ReachHelp].[dbo].[User_Register] WHERE Phone_Number=@phoneNumber) BEGIN UPDATE [ReachHelp].[dbo].[User_Register] SET User_Name=@userName,Entity_Name=@entityName,Designation=@designation,Street_Address_1=@streetAddress1,Street_Address_2=@streetAddress2,Country_Id=@countryId,State_Id=@stateId,City_Name=@cityName,Pincode=@pinCode,Latitude=@latitude,Longitude=@longitude, Email_Id=@emailId, Middle_Name = @middleName, Last_Name=@lastName, Country_Code=@countryCode WHERE Phone_Number=@phoneNumber;SELECT 'Update' AS [Status];END ELSE BEGIN INSERT INTO [ReachHelp].[dbo].[User_Register](User_Name,Middle_Name,Last_Name,Country_Code,Phone_Number,Password,Role_Id,Entity_Name,Designation,Street_Address_1,Street_Address_2,Country_Id,State_Id,City_Name,Pincode,Latitude,Longitude,Status,Email_Id) VALUES(@userName,@middleName, @lastName,@countryCode,@phoneNumber,@password,@roleId,@entityName,@designation,@streetAddress1,@streetAddress2,@countryId,@stateId,@cityName,@pinCode,@latitude,@longitude,'New',@emailId);SELECT 'New' AS [Status]; END";
using (gen.sqlConnection = new SqlConnection(gen.connectionString))
{
gen.sqlConnection.Open();
using (gen.sqlCommand = new SqlCommand(gen.queryString, gen.sqlConnection))
{
gen.sqlCommand.Parameters.AddWithValue("@userName", userDetails.userName);
gen.sqlCommand.Parameters.AddWithValue("@middleName", userDetails.middleName);
gen.sqlCommand.Parameters.AddWithValue("@lastName", userDetails.lastName);
gen.sqlCommand.Parameters.AddWithValue("@entityName", userDetails.entityName); // Optional
gen.sqlCommand.Parameters.AddWithValue("@countryCode", userDetails.countryCode);
gen.sqlCommand.Parameters.AddWithValue("@phoneNumber", userDetails.phoneNumber);
gen.sqlCommand.Parameters.AddWithValue("@password", MD5Hash(otp));
gen.sqlCommand.Parameters.AddWithValue("@roleId", userDetails.roleId); // 1 - SysAdmin, 2 - Monitor, 3 - User
gen.sqlCommand.Parameters.AddWithValue("@designation", userDetails.designation); // Optional
//gen.sqlCommand.Parameters.AddWithValue("@address", userDetails.address);
gen.sqlCommand.Parameters.AddWithValue("@streetAddress1", userDetails.streetAddress1);
gen.sqlCommand.Parameters.AddWithValue("@streetAddress2", userDetails.streetAddress2);
gen.sqlCommand.Parameters.AddWithValue("@countryId", userDetails.countryId);
gen.sqlCommand.Parameters.AddWithValue("@stateId", userDetails.stateId);
//gen.sqlCommand.Parameters.AddWithValue("@cityId", userDetails.cityId);
gen.sqlCommand.Parameters.AddWithValue("@cityName", userDetails.cityName);
gen.sqlCommand.Parameters.AddWithValue("@pincode", userDetails.pincode);
gen.sqlCommand.Parameters.AddWithValue("@latitude", userDetails.latitude); // Optional
gen.sqlCommand.Parameters.AddWithValue("@longitude", userDetails.longitude); // Optional
gen.sqlCommand.Parameters.AddWithValue("@emailId", userDetails.emailId); // Optional
using (gen.sqlDataReader = gen.sqlCommand.ExecuteReader())
{
while (gen.sqlDataReader.Read())
{
if (gen.sqlDataReader["Status"].ToString() == "New")
{
//Trigger Email
//otpGeneration.TriggerEmail(userDetails.emailId, userDetails.userName,"Welcome to ReacHelp","<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p> <br> <p> You have been registered at ReachHelp. Your OTP is \"" + otp + "\". Kindly Login to <a href=\"http://reachelp.in\">ReachHelp<a> to login. </p> </body></html>");
/*if (userDetails.emailId != "")
* {*/
otpGeneration.TriggerEmail(userDetails.emailId, userDetails.userName, "Welcome to ReacHelp", "<!DOCTYPE html><html> <head> <meta charset=\"utf-8\"> </head> <body> <p> Dear User, </p> <br> <p>You have successfully registered with ReacHelp. Your Temporary Password is <strong>“" + otp + "”</strong>. Please return to <a href=\"http://reachelp.in\">ReacHelp</a> to login and complete the enrollment process. Your user name can be either your cell phone number or your email ID. Your initial password will be the Temporary Password. Once you have logged in, please change your password to something you will remember</p> </body></html>");
/*}
* else
* {
* //Trigger SMS
* string fullMobileNumber = (userDetails.countryCode == null ? "" : userDetails.countryCode) + userDetails.phoneNumber;
* otpGeneration.TriggerSMS("You have been registered at ReachHelp. Your Temporary Password is " + otp + ". Please Login with your phone number.", fullMobileNumber); //Incomplete
* }*/
updateGeography(userDetails.latitude, userDetails.longitude, userDetails.emailId);
return(true);
}
else if (gen.sqlDataReader["Status"].ToString() == "Update")
{
updateGeography(userDetails.latitude, userDetails.longitude, userDetails.emailId);
return(true);
}
}
}
}
}
}
catch (SqlException ex)
{
return(false);
}
catch (Exception ex)
{
return(false);
}
return(false);
}