public static ModelResult <DTO.Account.UserType> AddEditUserType(DTO.Account.UserType userType)
{
var oResult = new ModelResult <DTO.Account.UserType>();
using (var conn = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SP_UserTypeAddEdit";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", userType.Name);
if (userType.Id > 0)
{
cmd.Parameters.AddWithValue("@Id", userType.Id);
}
conn.Open();
userType.Id = Convert.ToInt32(cmd.ExecuteScalar());
oResult.HasResult = true;
oResult.Results = userType;
}
return(oResult);
}
}
public static ModelResult <List <DTO.Account.UserAccounts> > GetUsersForGroup(DTO.Account.UserAccounts oUserAccount, int groupId)
{
using (SqlConnection conn = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = conn;
var command = @"select * from (select U.*,UT.Name as UserTypeName from UserAccounts U
Join UserType UT on UT.Id=U.UserTypeId
) as myTable where 1=1 and IsDeleted=0 ";
if (groupId > 0)
{
command += "and Id not in (select ContactId from SMSGroupsMembers where Con_TypeId in(128) and GroupId=@GroupId ) ";
cmd.Parameters.AddWithValue("@GroupId", groupId);
}
if (oUserAccount.Id > 0)
{
command += "and Id =@Id ";
cmd.Parameters.AddWithValue("@Id", oUserAccount.Id);
}
if (!String.IsNullOrEmpty(oUserAccount.Name))
{
command += "and Name like @Name ";
cmd.Parameters.AddWithValue("@Name", "%" + oUserAccount.Name + "%");
}
if (oUserAccount.IsActive.HasValue)
{
command += "and IsActive = @IsActive ";
cmd.Parameters.AddWithValue("@IsActive", oUserAccount.IsActive.Value);
}
if (oUserAccount.UserTypeId > 0)
{
command += "and UserTypeId = @UserTypeId ";
cmd.Parameters.AddWithValue("@UserTypeId", oUserAccount.UserTypeId);
}
if (!oUserAccount.IsList)
{
command +=
"order by @SortCol @SortType OFFSET (@Page -1 )* @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
cmd.CommandText = command;
cmd.CommandText = cmd.CommandText.Replace("@SortCol", oUserAccount.SortCol);
cmd.CommandText = cmd.CommandText.Replace("@SortType", oUserAccount.SortType);
cmd.CommandText = cmd.CommandText.Replace("@Page", oUserAccount.Page.ToString());
cmd.CommandText = cmd.CommandText.Replace("@RowsPerPage", oUserAccount.RowPerPage.ToString());
}
conn.Open();
var reader = cmd.ExecuteReader();
var lstResult = new List <DTO.Account.UserAccounts>();
var oResult = new ModelResult <List <DTO.Account.UserAccounts> >();
if (reader.HasRows)
{
while (reader.Read())
{
var obUsers = new DTO.Account.UserAccounts();
obUsers.Id = Convert.ToInt32(reader["Id"]);
obUsers.Name = reader["Name"].ToString();
obUsers.Pass = reader["Pass"].ToString();
obUsers.Email = reader["Email"].ToString();
obUsers.EmailPassword = reader["EmailPassword"].ToString();
obUsers.Mobile = reader["Mobile"].ToString();
obUsers.Gender = reader["Gender"].ToString();
obUsers.Avatar = reader["Avatar"].ToString();
obUsers.IsActive = Convert.ToBoolean(reader["IsActive"].ToString());
obUsers.UserTypeId = Convert.ToInt32(reader["UserTypeId"].ToString());
#region UserType Parms
var obUserType = new DTO.Account.UserType();
if (reader["UserTypeName"] != DBNull.Value)
{
obUserType.Name = Convert.ToString(reader["UserTypeName"]);
}
obUsers.OUserType = obUserType;
#endregion
lstResult.Add(obUsers);
}
}
int Count = 0;
using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString))
{
//try
//{
using (System.Data.SqlClient.SqlCommand cmdCount = new System.Data.SqlClient.SqlCommand())
{
command =
@"select count(1) from (select U.*,UT.Name as UserTypeName from UserAccounts U
Join UserType UT on UT.Id=U.UserTypeId
) as myTable where 1=1 and IsDeleted=0 ";
if (groupId > 0)
{
command += "and Id not in (select ContactId from SMSGroupsMembers where Con_TypeId in(128) and GroupId=@GroupId ) ";
cmdCount.Parameters.AddWithValue("@GroupId", groupId);
}
if (oUserAccount.Id > 0)
{
command += "and Id=@Id ";
cmdCount.Parameters.AddWithValue("@Id", oUserAccount.Id);
}
if (!String.IsNullOrEmpty(oUserAccount.Name))
{
command += "and Name like @Name ";
cmdCount.Parameters.AddWithValue("@Name", "%" + oUserAccount.Name + "%");
}
if (oUserAccount.IsActive.HasValue)
{
command += "and IsActive = @IsActive ";
cmdCount.Parameters.AddWithValue("@IsActive", oUserAccount.IsActive.Value);
}
if (oUserAccount.UserTypeId > 0)
{
command += "and UserTypeId = @UserTypeId ";
cmdCount.Parameters.AddWithValue("@UserTypeId", oUserAccount.UserTypeId);
}
cmdCount.CommandText = command;
cmdCount.Connection = connCount;
connCount.Open();
Count = Convert.ToInt32(cmdCount.ExecuteScalar());
}
//}
//catch
//{
// connCount.Close();
//}
}
if (lstResult.Count > 0)
{
oResult.HasResult = true;
}
oResult.Results = lstResult;
oResult.RowCount = Count;
return(oResult);
}
}
}
public static ModelResult <List <DTO.Account.UserType> > GetUserType(DTO.Account.UserType userType)
{
var oResult = new ModelResult <List <DTO.Account.UserType> >();
var lsUserType = new List <DTO.Account.UserType>();
using (var conn = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
var command = @"select * from UserType where 1=1 and IsDeleted=0 ";
if (userType.Id > 0)
{
command += "and Id = @Id ";
cmd.Parameters.AddWithValue("@Id", userType.Id);
}
if (!string.IsNullOrEmpty(userType.Name))
{
command += "and Name like @Name ";
cmd.Parameters.AddWithValue("@Name", "%" + userType.Name + "%");
}
if (!userType.IsList) // isList=false get DataTable with paging ----- isList=True get list without paging
{
command +=
" order by @SortCol @SortType OFFSET (@Page -1 )* @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
command = command.Replace("@SortCol", userType.SortCol);
command = command.Replace("@SortType", userType.SortType);
command = command.Replace("@Page", userType.Page.ToString());
command = command.Replace("@RowsPerPage", userType.RowPerPage.ToString());
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = command;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var oUserType = new DTO.Account.UserType();
oUserType.Id = Convert.ToInt32(reader["Id"]);
oUserType.Name = reader["Name"].ToString();
oUserType.IsDeleted = Convert.ToBoolean(reader["IsDeleted"]);
lsUserType.Add(oUserType);
}
}
int count;
using (var connCount = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmdCount = new SqlCommand())
{
command = @"select count(*) from UserType where 1=1 and IsDeleted=0 ";
if (userType.Id > 0)
{
command += "and Id = @Id ";
cmdCount.Parameters.AddWithValue("@Id", userType.Id);
}
if (!string.IsNullOrEmpty(userType.Name))
{
command += "and Name like @Name ";
cmdCount.Parameters.AddWithValue("@Name", userType.Name);
}
cmdCount.CommandType = CommandType.Text;
cmdCount.CommandText = command;
cmdCount.Connection = connCount;
connCount.Open();
count = Convert.ToInt32(cmdCount.ExecuteScalar());
}
}
if (lsUserType.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lsUserType;
oResult.RowCount = count;
}
return(oResult);
}
}
}
//***************************************//
public static ModelResult <List <DTO.Account.UserAccounts> > UserAccountGet(DTO.Account.UserAccounts oUserAccount)
{
var oResult = new ModelResult <List <DTO.Account.UserAccounts> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = "";
command = @"SELECT TBL1.* , TBL2.Name Parm1
FROM UserAccounts TBL1
LEFT JOIN UserType TBL2 ON TBL1.UserTypeId = TBL2.Id
WHERE 1=1 AND TBL1.IsDeleted = 0 ";
if (oUserAccount.Id > 0)
{
command += " AND TBL1.Id = @Id";
cmd.Parameters.AddWithValue("@Id", oUserAccount.Id);
}
if (oUserAccount.UserTypeId >= 0)
{
command += " And TBL1.UserTypeId =@UserTypeId ";
cmd.Parameters.AddWithValue("@UserTypeId", oUserAccount.UserTypeId);
}
if (!string.IsNullOrEmpty(oUserAccount.Name))
{
command += " And TBL1.Name Like @Name ";
cmd.Parameters.AddWithValue("@Name", "%" + oUserAccount.Name + "%");
}
if (!oUserAccount.IsList)
{
command +=
" order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
command = command.Replace("@SortCol", oUserAccount.SortCol);
command = command.Replace("@SortType", oUserAccount.SortType);
command = command.Replace("@Page", oUserAccount.Page.ToString());
command = command.Replace("@RowsPerPage", oUserAccount.RowPerPage.ToString());
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstUsers = new List <DTO.Account.UserAccounts>();
if (reader.HasRows)
{
while (reader.Read())
{
#region User Account Parms
var obUsers = new DTO.Account.UserAccounts();
obUsers.Id = Convert.ToInt32(reader["Id"]);
if (reader["UserTypeId"] != DBNull.Value)
{
obUsers.UserTypeId = Convert.ToInt32(reader["UserTypeId"]);
}
if (reader["Name"] != DBNull.Value)
{
obUsers.Name = Convert.ToString(reader["Name"]);
}
if (reader["Email"] != DBNull.Value)
{
obUsers.Email = Convert.ToString(reader["Email"]);
}
if (reader["Pass"] != DBNull.Value)
{
obUsers.Pass = Convert.ToString(reader["Pass"]);
}
if (reader["EmailPassword"] != DBNull.Value)
{
obUsers.EmailPassword = Convert.ToString(reader["EmailPassword"]);
}
if (reader["Mobile"] != DBNull.Value)
{
obUsers.Mobile = Convert.ToString(reader["Mobile"]);
}
if (reader["Gender"] != DBNull.Value)
{
obUsers.Gender = Convert.ToString(reader["Gender"]);
}
if (reader["ManagerGroupId"] != DBNull.Value)
{
obUsers.ManagerGroupId = Convert.ToInt32(reader["ManagerGroupId"]);
}
if (reader["IsActive"] != DBNull.Value)
{
obUsers.IsActive = Convert.ToBoolean(reader["IsActive"]);
}
if (reader["Avatar"] != DBNull.Value)
{
obUsers.Avatar = Convert.ToString(reader["Avatar"]);
}
if (reader["TraceUserActivity"] != DBNull.Value)
{
obUsers.TraceUserActivity = Convert.ToBoolean(reader["TraceUserActivity"]);
}
#region UserType Parms
var obUserType = new DTO.Account.UserType();
if (reader["Parm1"] != DBNull.Value)
{
obUserType.Name = Convert.ToString(reader["Parm1"]);
}
obUsers.OUserType = obUserType;
#endregion
#endregion
lstUsers.Add(obUsers);
}
}
int count = 0;
if (!oUserAccount.IsList)
{
using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmdCount = new SqlCommand())
{
cmdCount.Connection = connCount;
command = @"SELECT COUNT(1) FROM UserAccounts WHERE 1=1 AND IsDeleted = 0";
if (oUserAccount.Id > 0)
{
command += " AND Id = @Id";
cmdCount.Parameters.AddWithValue("@Id", oUserAccount.Id);
}
cmdCount.CommandText = command;
if (connCount.State != ConnectionState.Open)
{
connCount.Open();
}
count = Convert.ToInt32(cmdCount.ExecuteScalar());
connCount.Close();
}
}
}
if (lstUsers.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstUsers;
oResult.RowCount = count;
}
}
}
}
catch (Exception ex)
{
oResult.Message = ex.Message;
oResult.HasResult = false;
}
finally
{
conn.Close();
}
return(oResult);
}
