/// <summary>
/// Authenticating Login credentials and return Success/Failed
/// </summary>
/// <param name="email"></param>
/// <param name="password"></param>
/// <returns></returns>
public RequestStatus checkLogin(string email, string password)
{
RequestStatus requestStatus = new RequestStatus();
try
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd.CommandText = "Select * From account Where email='" + email + "' And password='******'";
cmd.Connection = cnn;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows) //validate if user exist or not
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
dr.Close();
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "checkLogin function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// To upload image/file based on Complaint
/// </summary>
/// <param name="jsonComplaintDetail"></param>
/// <returns></returns>
public RequestStatus complaintFileUpload(string jsonComplaintDetail)
{
RequestStatus requestStatus = new RequestStatus();
try
{
#region Convertion from JSON to Generic Object
ComplaintDetail complaintDetail = null;
if (!string.IsNullOrEmpty(jsonComplaintDetail))
complaintDetail = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<ComplaintDetail>(jsonComplaintDetail);
#endregion
if (complaintDetail != null)
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Complaint_FileUpload", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@complaintId", complaintDetail.ComplaintId);
cmd.Parameters.AddWithValue("@fileName", complaintDetail.UserFile.FileName);
cmd.Parameters.AddWithValue("@extension", complaintDetail.UserFile.Extension);
cmd.Parameters.AddWithValue("@fileContent", complaintDetail.UserFile.FileContent);
var insertStatus = cmd.ExecuteScalar();
if (insertStatus != null)
{
string statusString = (string)insertStatus;
if (statusString.Contains("SUCCESS")) //successfully added complaint file entry
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "complaintFileUpload function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// SignUp new users
/// </summary>
/// <param name="jsonStreamUserDetail"></param>
/// <returns></returns>
public RequestStatus signUp(string jsonUserDetail)
{
RequestStatus requestStatus = new RequestStatus();
try
{
#region Convertion from JSON to Generic Object
UserDetail userDetail = null;
if (!string.IsNullOrEmpty(jsonUserDetail))
userDetail = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<UserDetail>(jsonUserDetail);
#endregion
if (userDetail != null)
{
#region Basic Validations
if (string.IsNullOrEmpty(userDetail.FirstName) || string.IsNullOrEmpty(userDetail.LastName) || string.IsNullOrEmpty(userDetail.Email) ||
string.IsNullOrEmpty(userDetail.Password) || string.IsNullOrEmpty(userDetail.Question1) || string.IsNullOrEmpty(userDetail.Answer1) ||
string.IsNullOrEmpty(userDetail.Question2) || string.IsNullOrEmpty(userDetail.Answer2))
{
requestStatus.Code = AppConstants.REQUIRED_FIELDS_CODE;
requestStatus.Message = AppConstants.REQUIRED_FIELDS_MESSAGE;
return requestStatus;
}
if (!IsValidMail(userDetail.Email))
{
requestStatus.Code = AppConstants.INVALID_EMAIL_CODE;
requestStatus.Message = AppConstants.INVALID_EMAIL_MESSAGE;
return requestStatus;
}
if (userDetail.Password != userDetail.ConfirmPassword)
{
requestStatus.Code = AppConstants.PASSWORD_MISMATCH_CODE;
requestStatus.Message = AppConstants.PASSWORD_MISMATCH_MESSAGE;
return requestStatus;
}
#endregion
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Account_New", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@firstname", userDetail.FirstName);
cmd.Parameters.AddWithValue("@lastname", userDetail.LastName);
cmd.Parameters.AddWithValue("@alias", userDetail.Alias);
cmd.Parameters.AddWithValue("@email", userDetail.Email);
cmd.Parameters.AddWithValue("@password", StringCipher.Encrypt(userDetail.Password));
cmd.Parameters.AddWithValue("@phone", userDetail.Phone);
cmd.Parameters.AddWithValue("@address", userDetail.Address);
cmd.Parameters.AddWithValue("@question1", userDetail.Question1);
cmd.Parameters.AddWithValue("@answer1", StringCipher.Encrypt(userDetail.Answer1));
cmd.Parameters.AddWithValue("@question2", userDetail.Question2);
cmd.Parameters.AddWithValue("@answer2", StringCipher.Encrypt(userDetail.Answer2));
var insertStatus = cmd.ExecuteScalar();
if (insertStatus != null)
{
string statusString = (string)insertStatus;
if (statusString.Contains("SUCCESS")) //successfully created account
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else if (statusString.Contains("FAIL")) //Email or Alias already exist
{
requestStatus.Code = AppConstants.EMAIL_EXIST_CODE;
requestStatus.Message = AppConstants.EMAIL_EXIST_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "signUp function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// POST a posting like
/// </summary>
/// <param name="jsonPostingDetail"></param>
/// <returns></returns>
public RequestStatus postingLike(string jsonPostingDetail)
{
RequestStatus requestStatus = new RequestStatus();
try
{
#region Convertion from JSON to Generic Object
PostingDetail postingDetail = null;
if (!string.IsNullOrEmpty(jsonPostingDetail))
postingDetail = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<PostingDetail>(jsonPostingDetail);
#endregion
if (postingDetail != null)
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Posting_Like", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@postingId", postingDetail.PostingId);
cmd.Parameters.AddWithValue("@accountId", postingDetail.AccountId);
var insertStatus = cmd.ExecuteScalar();
if (insertStatus != null)
{
string statusString = (string)insertStatus;
if (statusString.Contains("SUCCESS")) //successfully added posting like entry
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "postingLike function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// POST a new posting
/// </summary>
/// <param name="jsonPostingDetail"></param>
/// <returns></returns>
public RequestStatus newPosting(string jsonPostingDetail)
{
RequestStatus requestStatus = new RequestStatus();
try
{
#region Convertion from JSON to Generic Object
PostingDetail postingDetail = null;
if (!string.IsNullOrEmpty(jsonPostingDetail))
postingDetail = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<PostingDetail>(jsonPostingDetail);
#endregion
if (postingDetail != null)
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Posting_New", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@postingTypeId", postingDetail.PostingTypeId);
cmd.Parameters.AddWithValue("@complaintId", postingDetail.ComplaintId);
if (postingDetail.ParentPostingId == 0) //handling null value
cmd.Parameters.AddWithValue("@parentPostingId", DBNull.Value);
else
cmd.Parameters.AddWithValue("@parentPostingId", postingDetail.ParentPostingId);
cmd.Parameters.AddWithValue("@accountId", postingDetail.AccountId);
if (postingDetail.EntityId == 0) //handling null value
cmd.Parameters.AddWithValue("@entityId", DBNull.Value);
else
cmd.Parameters.AddWithValue("@entityId", postingDetail.EntityId);
cmd.Parameters.AddWithValue("@Description", postingDetail.Description);
int postingInsertId = (int)cmd.ExecuteScalar();
if (postingInsertId > 0) //successfully added posting entry
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "newPosting function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// POST a new complaint
/// </summary>
/// <param name="jsonComplaintDetail"></param>
/// <returns></returns>
public RequestStatus newComplaint(string jsonComplaintDetail)
{
RequestStatus requestStatus = new RequestStatus();
try
{
#region Convertion from JSON to Generic Object
ComplaintDetail complaintDetail = null;
if (!string.IsNullOrEmpty(jsonComplaintDetail))
complaintDetail = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<ComplaintDetail>(jsonComplaintDetail);
#endregion
if (complaintDetail != null)
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Complaint_New", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@accountId", complaintDetail.AccountId);
cmd.Parameters.AddWithValue("@entityId", complaintDetail.EntityId);
cmd.Parameters.AddWithValue("@locationTypeId", complaintDetail.LocationTypeId);
cmd.Parameters.AddWithValue("@location", complaintDetail.Location);
cmd.Parameters.AddWithValue("@subject", complaintDetail.Subject);
cmd.Parameters.AddWithValue("@description", complaintDetail.Description);
if (complaintDetail.UserFile != null) //if user also pass image/file detail then upload image/file as "should also allow uploaded files to be added."
{
cmd.Parameters.AddWithValue("@fileName", complaintDetail.UserFile.FileName);
cmd.Parameters.AddWithValue("@extension", complaintDetail.UserFile.Extension);
cmd.Parameters.AddWithValue("@fileContent", complaintDetail.UserFile.FileContent);
}
else
{
cmd.Parameters.AddWithValue("@fileName", DBNull.Value);
cmd.Parameters.AddWithValue("@extension", DBNull.Value);
cmd.Parameters.Add("@fileContent", SqlDbType.VarBinary, -1);
cmd.Parameters["@fileContent"].Value = DBNull.Value;
}
int complaintInsertId = (int)cmd.ExecuteScalar();
if (complaintInsertId > 0) //successfully added entity entry
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Data = complaintInsertId.ToString();
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
//New Posting - it must have a Posting (of type 1 [solution]) with it
cmd = new SqlCommand("sp_Posting_New", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@postingType", 1); //1 - solution, 2 - Comment, 3 - Response
cmd.Parameters.AddWithValue("@complaintId", complaintInsertId);
cmd.Parameters.AddWithValue("@parentPostingId", DBNull.Value); //If it is null, that means the comment/posting is associated with a complaint (complaint_id)
cmd.Parameters.AddWithValue("@accountId", complaintDetail.AccountId);
cmd.Parameters.AddWithValue("@entityId", complaintDetail.EntityId);
cmd.Parameters.AddWithValue("@Description", "");
int postingInsertId = (int)cmd.ExecuteScalar();
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "newComplaint function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// To upload image for Account
/// </summary>
/// <param name="imageData"></param>
/// <returns></returns>
public RequestStatus imageUpload(string imageData)
{
RequestStatus requestStatus = new RequestStatus();
int image_id = 0;
try
{
#region Convertion from JSON to Generic Object
UserFile userFile = null;
if (!string.IsNullOrEmpty(imageData))
userFile = new System.Web.Script.Serialization.JavaScriptSerializer().Deserialize<UserFile>(imageData);
#endregion
if (userFile != null)
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd.CommandText = "Insert Into upload(filename, extension, file_content, account_id, timestamp) Values(@filename, @extension, @file_content, @account_id, @current_datetime); Select CAST(scope_identity() AS int)";
cmd.Parameters.Add("@filename", SqlDbType.VarChar).Value = userFile.FileName;
cmd.Parameters.Add("@extension", SqlDbType.VarChar).Value = userFile.Extension;
cmd.Parameters.Add("@file_content", SqlDbType.VarBinary).Value = userFile.FileContent;
cmd.Parameters.Add("@account_id", SqlDbType.Int).Value = userFile.AccountId;
cmd.Parameters.Add("@current_datetime", SqlDbType.DateTime).Value = userFile.TimeStamp;
cmd.Connection = cnn;
int imageInsertId = (int)cmd.ExecuteScalar();
if (imageInsertId > 0) //successfully added image entry
{
image_id = imageInsertId;
cmd = new SqlCommand();
cmd.CommandText = "Update account Set image_id = " + imageInsertId + " Where id=" + userFile.AccountId;
cmd.Connection = cnn;
int updateStatus = cmd.ExecuteNonQuery();
if (updateStatus > 0) //successfully updated account
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
}
catch (Exception ex)
{
requestStatus.Code = AppConstants.FATAL_ERROR_CODE;
requestStatus.Message = AppConstants.FATAL_ERROR_MESSAGE;
requestStatus.Data = "imageUpload function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
/// <summary>
/// Delete Account with related information from other tables
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public RequestStatus deleteAccount(string id)
{
RequestStatus requestStatus = new RequestStatus();
try
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
cmd = new SqlCommand("sp_Account_Delete", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
var deleteStatus = cmd.ExecuteScalar();
if (deleteStatus != null)
{
string statusString = (string)deleteStatus;
if (statusString.Contains("SUCCESS")) //successfully deleted account
{
requestStatus.Code = AppConstants.SUCCESS_CODE;
requestStatus.Message = AppConstants.SUCCESS_MESSAGE;
}
else
{
requestStatus.Code = AppConstants.FAILED_CODE;
requestStatus.Message = AppConstants.FAILED_MESSAGE;
}
}
}
catch (Exception ex)
{
requestStatus.Data = "deleteAccount function : " + ex.Message.ToString();
}
finally
{
cnn.Close();
}
return requestStatus;
}
