internal List <int> GetPartnerIds(int FileId)
{
List <int> PartnerIds = new List <int>();
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("Select UserId From FilePartners Where FileId=@FileId AND Isdeleted=0", con);
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
PartnerIds.Add(Convert.ToInt32(rdr[0]));
}
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(PartnerIds);
}
internal void GenerateOTP(List <int> GetAllPartnerId, int RequestId)
{
foreach (var PartnerId in GetAllPartnerId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SpPartnerOtpInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RequestId", RequestId);
cmd.Parameters.AddWithValue("@OTP", RandomGenerate.Otp);
cmd.Parameters.AddWithValue("@PartnerId", PartnerId);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
}
internal int GenerateRequest(int FileId, int UserId)
{
int RequestId = 0;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SpFileRequest", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileId", FileId);
cmd.Parameters.AddWithValue("@UserId", UserId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
RequestId = Convert.ToInt32(rdr["RESPONSE"]);
}
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(RequestId);
}
public static bool AuthenticateUser(string username, string password)
{
// ConfigurationManager class is in System.Configuration namespace
string CS = ConnectSQL.GetConnectionString();
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spAuthenticateUser", con);
cmd.CommandType = CommandType.StoredProcedure;
// FormsAuthentication is in System.Web.Security
string EncryptedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
// SqlParameter is in System.Data namespace
SqlParameter paramUsername = new SqlParameter("@UserName", username);
SqlParameter paramPassword = new SqlParameter("@Password", EncryptedPassword);//we are not using authentiacated password ,use EncryptedPassword to use authenticated password
cmd.Parameters.Add(paramUsername);
cmd.Parameters.Add(paramPassword);
con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
return(ReturnCode == 1);
}
}
private int GetFileId()
{
int FileId = 0;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT TOP 1 FileID from inz_file Order by FileID DESC", con);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
FileId = (int)rdr[0];
}
}
}
catch (System.Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(FileId);
}
//select OTP from RequestTransaction where RequestId=@RequestId AND PartnerId=@PartnerId
public int GetOtp(int RequestId, int PartnerId)
{
int Otp = 0;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select OTP from RequestTransaction where RequestId=@RequestId AND PartnerId=@PartnerId", con);
cmd.Parameters.AddWithValue("@RequestId", RequestId);
cmd.Parameters.AddWithValue("@PartnerId", PartnerId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Otp = Convert.ToInt32(rdr["Otp"]);
}
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(Otp);
}
internal static int GetUserIDByEmailId(string EmailID)
{
int Userid = 0;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select UserId from inz_USERS Where EmailID=@EmailID", con);
cmd.Parameters.AddWithValue("@EmailID", EmailID);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Userid = (int)rdr["UserId"];
}
return(Userid);
}
catch
{
return(Userid);
}
finally
{
con.Close();
}
}
}
internal string GetFileName(int FileId)
{
string Filename = "NIL";
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select [FileName] from inz_Post Where PostID=@FileId AND IsDeleted=0", con);
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
Filename = rdr["FileName"].ToString();
}
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(Filename);
}
internal string GetEmailbyPartnerId(string PartnerId)
{
string EmailId = "";
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select EmailID from inz_USERS Where UserID=@PartnerId", con);
cmd.Parameters.AddWithValue("@PartnerId", PartnerId);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
EmailId = (string)rdr["EmailID"];
}
return(EmailId);
}
catch
{
throw;
}
finally
{
con.Close();
}
}
}
public bool UserDisableEnable(string Email)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SpUserEnableDisable", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Email", Email);
cmd.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
finally
{
con.Close();
}
}
}
public List <User> ListUsers()
{
List <User> _listUsers = new List <User>();
SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString());
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select EmailID,Rowstatus from inz_USERS", con);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
User _user = new User();
_user.EmailID = rdr["EmailID"].ToString();
_user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
_listUsers.Add(_user);
}
return(_listUsers);
}
catch
{
return(_listUsers);
}
finally
{
con.Close();
}
}
public ActionResult Index(HttpPostedFileBase _File)
{
byte[] File = new byte[_File.ContentLength];
Files FileModel = new Files();
FileModel.Document = File;
FileModel.Name = _File.FileName;
_File.InputStream.Read(FileModel.Document, 0, _File.ContentLength);
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO Movies ([File],FileId) VALUES (@File,@FileId)", con);
cmd.Parameters.AddWithValue("@File", FileModel.Document);
cmd.Parameters.AddWithValue("@FileId", GetFileId());
cmd.ExecuteNonQuery();
}
catch (System.Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(View());
}
public static bool UserRegister(User _user)
{
string CS = ConnectSQL.GetConnectionString();
// SqlConnection is in System.Data.SqlClient namespace
using (SqlConnection con = new SqlConnection(CS))
{
SqlCommand cmd = new SqlCommand("spRegisterUser", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter username = new SqlParameter("@UserName", _user.Username);
// FormsAuthentication calss is in System.Web.Security namespace
string encryptedPassword = FormsAuthentication.
HashPasswordForStoringInConfigFile(_user.Password, "SHA1");
SqlParameter password = new SqlParameter("@Password", encryptedPassword);
SqlParameter email = new SqlParameter("@Email", _user.EmailID);
SqlParameter Category = new SqlParameter("@Category", _user.Category);
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
cmd.Parameters.Add(Category);
con.Open();
int ReturnCode = (int)cmd.ExecuteScalar();
return(ReturnCode == 1);
}
}
public static int GetUserID(string USername)
{
int Userid = 0;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select UserId from inz_USERS Where Username=@Username", con);
cmd.Parameters.AddWithValue("@Username", USername);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Userid = (int)rdr["UserId"];
}
return(Userid);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
public FileResponse SaveFile(int UserId, int FileId)
{
FileResponse fileResponse;
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("FileSave", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileId", FileId);
cmd.Parameters.AddWithValue("@UserId", UserId);
int Response = int.Parse(cmd.ExecuteScalar().ToString());
if (Response == (int)FileStatus.Sucess)
{
fileResponse = new FileResponse
{
Message = "Partner added sucessfully",
Status = FileStatus.Sucess
};
}
else if (Response == (int)FileStatus.Exists)
{
fileResponse = new FileResponse
{
Message = "Partner already exists",
Status = FileStatus.Exists
};
}
else
{
fileResponse = new FileResponse
{
Message = "Some thing went wrong",
Status = FileStatus.Error
};
}
}
catch (Exception ex)
{
fileResponse = new FileResponse
{
Message = ex.Message.ToString(),
Status = FileStatus.Error
};
}
finally
{
con.Close();
}
}
return(fileResponse);
}
public static List <User> UserAllUser(int FileId)
{
List <User> userlist = new List <User>();
SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString());
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SpFileAccess", con);
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
userlist.Add(user);
}
}
else
{
rdr.Dispose();
cmd.Dispose();
cmd = new SqlCommand("select UserID,Username,Rowstatus from inz_USERS", con);
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
userlist.Add(user);
}
}
}
return(userlist);
}
catch
{
return(userlist);
}
finally
{
con.Close();
}
}
internal void DeleteRequest(int RequestId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("UPDATE RequestFile SET IsDeleted=1 Where RequestId=@RequestId", con);
cmd.Parameters.AddWithValue("@RequestId", RequestId);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
internal void RestoreRequestTransactions(int RequestId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("UPDATE RequestTransaction SET IsOTPVerified=0,OTP=@OTP Where RequestId=@RequestId UPDATE RequestFile SET IsDeleted=0 Where RequestId=@RequestId", con);
cmd.Parameters.AddWithValue("@RequestId", RequestId);
cmd.Parameters.AddWithValue("@OTP", RandomGenerate.Otp);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
public static List <User> getAllUsers()
{
List <User> userlist = new List <User>();
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from inz_USERS Where Rowstatus='A'", con);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
User user = new User();
user.UserID = (int)rdr["UserID"];
user.Username = rdr["Username"].ToString();
user.Rowstatus = Convert.ToChar(rdr["Rowstatus"]);
user.EmailID = rdr["EmailID"].ToString();
userlist.Add(user);
}
}
return(userlist);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
internal void AuthenticateOTP(int RequestId, int UserId, string OTP)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("UPDATE RequestTransaction Set IsOTPVerified=1 Where PartnerId=@PartnerId AND RequestId=@RequestId AND OTP=@OTP", con);
cmd.Parameters.AddWithValue("@RequestId", RequestId);
cmd.Parameters.AddWithValue("@PartnerId", UserId);
cmd.Parameters.AddWithValue("@OTP", OTP);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
public bool FileUserAccess(int FileId, int UserId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("select PartnerId from FilePartners Where UserId=@UserId AND FileId=@FileId AND IsDeleted=0", con);
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
return(rdr.HasRows);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
public List <User> GetPartnersbyFileId(int FileId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
List <User> ListUser = new List <User>();
SqlCommand cmd = new SqlCommand("GetPartners", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileId", FileId);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
User userObj = new User()
{
Username = rdr["Username"].ToString(),
EmailID = rdr["EmailID"].ToString(),
Rowstatus = int.Parse(rdr["Isdeleted"].ToString()) == 0?'A':'D',
UserID = int.Parse(rdr["UserId"].ToString())
};
ListUser.Add(userObj);
}
return(ListUser);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}
internal string AuthorizeOTP(int RequestId)
{
string UserName = "";
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("SPFileAuthorizeStatus", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RequestId", RequestId);
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
UserName = rdr["Name"].ToString();
}
}
else
{
UserName = "******";
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
return(UserName);
}
public bool UpdatePartnerStatus(int FileId, int UserId)
{
using (SqlConnection con = new SqlConnection(ConnectSQL.GetConnectionString()))
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("PartnerStatusUpdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@FileId", FileId);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
}