public static ModelResult <DTO.Account.UserAccounts> DeleteUserAccount(DTO.Account.UserAccounts oUserAccount)
{
var oResult = new ModelResult <DTO.Account.UserAccounts>();
using (var conn = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SP_UserAccountDelete";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", oUserAccount.Id);
conn.Open();
oUserAccount.Id = Convert.ToInt32(cmd.ExecuteNonQuery());
oResult.HasResult = true;
oResult.Results = oUserAccount;
}
return(oResult);
}
}
public static ModelResult <List <DTO.News.News> > NewsByTag(DTO.News.NewskeyWords oKeyword, int langId)
{
var oResult = new ModelResult <List <DTO.News.News> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = @"SELECT TBL1.KeyWord,TBL2.*,TBL3.NameAr,TBL3.NameEn,TBL4.Name InsertedByName FROM NewskeyWords TBL1
LEFT JOIN News TBL2 ON TBL1.NewsId = TBL2.Id
JOIN Categories TBL3 ON TBL2.CategoryId = TBL3.Id
JOIN UserAccounts TBL4 ON TBL2.InsertedBy = TBL4.Id
WHERE 1=1 AND TBL2.IsDeleted = 0 ";
if (langId > 0)
{
command += " AND TBL2.LangId = @LangId";
cmd.Parameters.AddWithValue("@LangId", langId);
}
if (!string.IsNullOrEmpty(oKeyword.KeyWord))
{
command += " And TBL1.KeyWord Like @KeyWord ";
cmd.Parameters.AddWithValue("@KeyWord", "%" + oKeyword.KeyWord + "%");
}
if (!oKeyword.IsList)
{
command += " order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
command = command.Replace("@SortCol", oKeyword.SortCol);
command = command.Replace("@SortType", oKeyword.SortType);
command = command.Replace("@Page", oKeyword.Page.ToString());
command = command.Replace("@RowsPerPage", oKeyword.RowPerPage.ToString());
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstNews = new List <DTO.News.News>();
if (reader.HasRows)
{
while (reader.Read())
{
#region Constant Parms
var obNews = new DTO.News.News();
#region News Params
/*****************/
var oCategory = new DTO.News.Categories();
oCategory.Id = Convert.ToInt32(reader["Id"]);
oCategory.NameAr = Convert.ToString(reader["NameAr"]);
if (reader["NameEn"] != DBNull.Value)
{
oCategory.NameEn = Convert.ToString(reader["NameEn"]);
}
obNews.OCategory = oCategory;
/******************/
var oInsertedBy = new DTO.Account.UserAccounts();
oInsertedBy.Name = Convert.ToString(reader["InsertedByName"]);
obNews.OInsertedBy = oInsertedBy;
/*************/
obNews.Id = Convert.ToInt32(reader["Id"]);
obNews.Title = Convert.ToString(reader["Title"]);
obNews.Summary = Convert.ToString(reader["Summary"]);
obNews.Details = Convert.ToString(reader["Details"]);
obNews.PublishDate = Convert.ToDateTime(reader["PublishDate"]);
obNews.InsertedDate = Convert.ToDateTime(reader["InsertedDate"]);
obNews.InsertedBy = Convert.ToInt32(reader["InsertedBy"]);
if (reader["UpdatedBy"] != DBNull.Value)
{
obNews.UpdatedBy = Convert.ToInt32(reader["UpdatedBy"]);
}
if (reader["UpdatedDate"] != DBNull.Value)
{
obNews.UpdatedDate = Convert.ToDateTime(reader["UpdatedDate"]);
}
obNews.ViewsCount = Convert.ToInt32(reader["ViewsCount"]);
obNews.Status = Convert.ToInt32(reader["Status"]);
if (reader["Keywords"] != DBNull.Value)
{
obNews.Keywords = Convert.ToString(reader["Keywords"]);
}
obNews.LangId = Convert.ToInt32(reader["LangId"]);
obNews.Image = Convert.ToString(reader["Image"]);
obNews.IsActive = Convert.ToBoolean(reader["IsActive"]);
obNews.CategoryId = Convert.ToInt32(reader["CategoryId"]);
#endregion
#endregion
lstNews.Add(obNews);
}
}
int count = 0;
if (!oKeyword.IsList)
{
using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmdCount = new SqlCommand())
{
cmdCount.Connection = connCount;
command = @"SELECT COUNT(1) FROM NewskeyWords TBL1 LEFT JOIN News TBL2 ON TBL1.NewsId = TBL2.Id WHERE TBL2.IsDeleted = 0";
if (!string.IsNullOrEmpty(oKeyword.KeyWord))
{
command += " And TBL1.KeyWord Like @KeyWord ";
cmdCount.Parameters.AddWithValue("@KeyWord", "%" + oKeyword.KeyWord + "%");
}
cmdCount.CommandText = command;
if (connCount.State != ConnectionState.Open)
{
connCount.Open();
}
count = Convert.ToInt32(cmdCount.ExecuteScalar());
connCount.Close();
}
}
}
if (lstNews.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstNews;
oResult.RowCount = count;
}
}
}
}
//catch (Exception ex)
//{
// oResult.Message = ex.Message;
// oResult.HasResult = false;
//}
finally
{
conn.Close();
}
return(oResult);
}
public static ModelResult <List <DTO.News.News> > NewsHomePageListGet(int langId)
{
var oResult = new ModelResult <List <DTO.News.News> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = @"select * from (
SELECT Top 6 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=2 AND LangId= @LangId -- slider
order by PublishDate desc, Id desc
union ALL
SELECT TOP 8 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=3 AND N.CategoryId=1 AND LangId= @LangId
order by PublishDate desc, Id desc
union ALL
SELECT TOP 8 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=3 AND N.CategoryId=2 AND LangId= @LangId
order by PublishDate desc, Id desc
union ALL
SELECT TOP 8 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=3 AND N.CategoryId=3 AND LangId= @LangId
order by PublishDate desc, Id desc
union ALL
SELECT TOP 8 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=3 AND N.CategoryId=4 AND LangId= @LangId
order by PublishDate desc, Id desc
union ALL
SELECT TOP 8 N.*,C.NameAr,C.NameEn,UA.Name InsertedByName,splits FROM News N
JOIN Categories C ON N.CategoryId = C.Id
JOIN UserAccounts UA ON N.InsertedBy = UA.Id
cross apply dbo.SplitString(N.HomePosition,',')
WHERE 1=1 AND N.IsDeleted = 0 AND N.IsActive = 1 AND N.IsInHome = 1 AND splits=3 AND N.CategoryId=5 AND LangId= @LangId
order by PublishDate desc, Id desc
) HomeNews";
#endregion
cmd.Parameters.AddWithValue("@LangId", langId);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstNews = new List <DTO.News.News>();
if (reader.HasRows)
{
while (reader.Read())
{
#region Constant Parms
var obNews = new DTO.News.News();
#region News Params
/*****************/
var oCategory = new DTO.News.Categories();
oCategory.Id = Convert.ToInt32(reader["Id"]);
oCategory.NameAr = Convert.ToString(reader["NameAr"]);
if (reader["NameEn"] != DBNull.Value)
{
oCategory.NameEn = Convert.ToString(reader["NameEn"]);
}
obNews.OCategory = oCategory;
/******************/
var oInsertedBy = new DTO.Account.UserAccounts();
oInsertedBy.Name = Convert.ToString(reader["InsertedByName"]);
obNews.OInsertedBy = oInsertedBy;
/*************/
obNews.Id = Convert.ToInt32(reader["Id"]);
obNews.Title = Convert.ToString(reader["Title"]);
obNews.Summary = Convert.ToString(reader["Summary"]);
obNews.Details = Convert.ToString(reader["Details"]);
obNews.PublishDate = Convert.ToDateTime(reader["PublishDate"]);
obNews.InsertedDate = Convert.ToDateTime(reader["InsertedDate"]);
obNews.InsertedBy = Convert.ToInt32(reader["InsertedBy"]);
if (reader["UpdatedBy"] != DBNull.Value)
{
obNews.UpdatedBy = Convert.ToInt32(reader["UpdatedBy"]);
}
if (reader["UpdatedDate"] != DBNull.Value)
{
obNews.UpdatedDate = Convert.ToDateTime(reader["UpdatedDate"]);
}
obNews.ViewsCount = Convert.ToInt32(reader["ViewsCount"]);
obNews.Status = Convert.ToInt32(reader["Status"]);
if (reader["Keywords"] != DBNull.Value)
{
obNews.Keywords = Convert.ToString(reader["Keywords"]);
}
obNews.LangId = Convert.ToInt32(reader["LangId"]);
obNews.Image = Convert.ToString(reader["Image"]);
obNews.IsActive = Convert.ToBoolean(reader["IsActive"]);
obNews.CategoryId = Convert.ToInt32(reader["CategoryId"]);
#endregion
#endregion
lstNews.Add(obNews);
}
}
if (lstNews.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstNews;
oResult.RowCount = lstNews.Count;
}
}
}
}
finally
{
conn.Close();
}
return(oResult);
}
public static ModelResult <List <DTO.News.StaticPages> > StaticPagesGet(DTO.News.StaticPages oStaticPage)
{
var oResult = new ModelResult <List <DTO.News.StaticPages> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = @"SELECT TBL1.*,TBL2.Name UpdatedByUser FROM StaticPages TBL1 LEFT JOIN UserAccounts TBL2 ON TBL1.UpdatedBy = TBL2.Id WHERE 1=1 ";
if (oStaticPage.Id > 0)
{
command += " AND TBL1.Id = @Id";
cmd.Parameters.AddWithValue("@Id", oStaticPage.Id);
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstStaticPages = new List <DTO.News.StaticPages>();
if (reader.HasRows)
{
while (reader.Read())
{
var obStaticPages = new DTO.News.StaticPages();
var obUserAccount = new DTO.Account.UserAccounts();
if (reader["UpdatedByUser"] != DBNull.Value)
{
obUserAccount.Name = Convert.ToString(reader["UpdatedByUser"]);
}
obStaticPages.OUpdatedBy = obUserAccount;
obStaticPages.Id = Convert.ToInt32(reader["Id"]);
if (reader["Image"] != DBNull.Value)
{
obStaticPages.Image = Convert.ToString(reader["Image"]);
}
if (reader["PageName"] != DBNull.Value)
{
obStaticPages.PageName = Convert.ToString(reader["PageName"]);
}
if (reader["Title"] != DBNull.Value)
{
obStaticPages.Title = Convert.ToString(reader["Title"]);
}
obStaticPages.Description = Convert.ToString(reader["Description"]);
if (reader["Image2"] != DBNull.Value)
{
obStaticPages.Image2 = Convert.ToString(reader["Image2"]);
}
if (reader["Image3"] != DBNull.Value)
{
obStaticPages.Image3 = Convert.ToString(reader["Image3"]);
}
if (reader["Mobile"] != DBNull.Value)
{
obStaticPages.Mobile = Convert.ToString(reader["Mobile"]);
}
if (reader["Phone"] != DBNull.Value)
{
obStaticPages.Phone = Convert.ToString(reader["Phone"]);
}
if (reader["Email1"] != DBNull.Value)
{
obStaticPages.Email1 = Convert.ToString(reader["Email1"]);
}
if (reader["Email2"] != DBNull.Value)
{
obStaticPages.Email2 = Convert.ToString(reader["Email2"]);
}
if (reader["UpdatedBy"] != DBNull.Value)
{
obStaticPages.UpdatedBy = Convert.ToInt32(reader["UpdatedBy"]);
}
if (reader["UpdatedDate"] != DBNull.Value)
{
obStaticPages.UpdatedDate = Convert.ToDateTime(reader["UpdatedDate"]);
}
if (reader["IsImageView"] != DBNull.Value)
{
obStaticPages.IsImageView = Convert.ToBoolean(reader["IsImageView"]);
}
lstStaticPages.Add(obStaticPages);
}
}
if (lstStaticPages.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstStaticPages;
oResult.RowCount = lstStaticPages.Count;
}
}
}
}
//catch (Exception ex)
//{
// oResult.Message = ex.Message;
// oResult.HasResult = false;
//}
finally
{
conn.Close();
}
return(oResult);
}
public static ModelResult <List <DTO.Account.Pages> > GetSelectedUserShortcuts(DTO.Account.UserAccounts user)
{
using (SqlConnection conn = new SqlConnection(DbConnection.ConnectionString))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = @"SELECT * FROM Pages p
JOIN UserShortcuts ush
ON (p.Id = ush.PageId AND ush.UserId = @userId)
WHERE p.IsActive = 1 and p.IsDeleted = 0
order by ush.OrderNo ";
comm.Parameters.AddWithValue("@userId", user.Id);
comm.CommandType = CommandType.Text;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var oResult = new ModelResult <List <DTO.Account.Pages> >();
var lstPage = new List <DTO.Account.Pages>();
var reader = comm.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var opPage = new DTO.Account.Pages();
opPage.Id = Convert.ToInt32(reader["Id"]);
opPage.Name = reader["Name"].ToString();
opPage.Link = reader["Link"].ToString();
opPage.Icon = Convert.ToString(reader["Icon"]);
lstPage.Add(opPage);
}
if (lstPage.Any())
{
oResult.HasResult = true;
oResult.Results = lstPage;
}
else
{
oResult.HasResult = false;
oResult.Results = new List <DTO.Account.Pages>();
}
}
return(oResult);
}
}
}
public static ModelResult <List <DTO.News.EducationalResources> > AttachmentGet(DTO.News.EducationalResources oAttachment)
{
var oResult = new ModelResult <List <DTO.News.EducationalResources> >();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
#region SQLCOMMAND Builder
var command = @"SELECT TBL1.*,
TBL2.Name UserName,
TBL3.Name,
TBL3.Icon
FROM Attachment TBL1
Left JOIN UserAccounts TBL2 ON TBL1.InsertedBy = TBL2.Id
Left JOIN Constant TBL3 ON TBL3.Id = TBL1.FileType
WHERE TBL1.IsDeleted = 0 ";
if (oAttachment.Id > 0)
{
command += " AND TBL1.Id = @Id";
cmd.Parameters.AddWithValue("@Id", oAttachment.Id);
}
if (oAttachment.FileType > 0)
{
command += " AND TBL1.FileType = @FileType";
cmd.Parameters.AddWithValue("@FileType", oAttachment.FileType);
}
if (!string.IsNullOrEmpty(oAttachment.FileTitle))
{
command += " AND (TBL1.FileTitle Like @FileTitle OR TBL1.FileDescription Like @FileTitle ) ";
cmd.Parameters.AddWithValue("@FileTitle", "%" + oAttachment.FileTitle + "%");
}
if (!oAttachment.IsList)
{
command += " order by @SortCol @SortType OFFSET(@Page - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY";
command = command.Replace("@SortCol", oAttachment.SortCol);
command = command.Replace("@SortType", oAttachment.SortType);
command = command.Replace("@Page", oAttachment.Page.ToString());
command = command.Replace("@RowsPerPage", oAttachment.RowPerPage.ToString());
}
#endregion
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.CommandText = command;
SqlDataReader reader = cmd.ExecuteReader();
var lstAttachment = new List <DTO.News.EducationalResources>();
if (reader.HasRows)
{
while (reader.Read())
{
var obAttachment = new DTO.News.EducationalResources();
obAttachment.Id = Convert.ToInt32(reader["Id"]);
obAttachment.FilePath = Convert.ToString(reader["FilePath"]);
obAttachment.InsertedBy = Convert.ToInt32(reader["InsertedBy"]);
obAttachment.InsertedDate = Convert.ToDateTime(reader["InsertedDate"]);
obAttachment.FileType = Convert.ToInt32(reader["FileType"]);
//if (reader["CategoryTypeId"] != DBNull.Value)
// obAttachment.CategoryTypeId = Convert.ToInt32(reader["CategoryTypeId"]);
if (reader["FileTitle"] != DBNull.Value)
{
obAttachment.FileTitle = Convert.ToString(reader["FileTitle"]);
}
if (reader["FileDescription"] != DBNull.Value)
{
obAttachment.FileDescription = Convert.ToString(reader["FileDescription"]);
}
if (reader["Image"] != DBNull.Value)
{
obAttachment.Image = Convert.ToString(reader["Image"]);
}
var obUserAccount = new DTO.Account.UserAccounts();
obUserAccount.Name = Convert.ToString(reader["UserName"]);
obAttachment.OUserAccount = obUserAccount;
if (reader["Name"] != DBNull.Value)
{
obAttachment.OFileType.Name = Convert.ToString(reader["Name"]);
}
if (reader["Icon"] != DBNull.Value)
{
obAttachment.OFileType.Icon = Convert.ToString(reader["Icon"]);
}
//if (reader["CategoryType"] != DBNull.Value)
// obAttachment.OCategoryType.Name = Convert.ToString(reader["CategoryType"]);
lstAttachment.Add(obAttachment);
}
}
int count = 0;
if (!oAttachment.IsList)
{
using (SqlConnection connCount = new SqlConnection(DbConnection.ConnectionString))
{
using (var cmdCount = new SqlCommand())
{
cmdCount.Connection = connCount;
command = @"SELECT COUNT(1) FROM Attachment WHERE 1=1 AND IsDeleted = 0";
if (oAttachment.Id > 0)
{
command += " AND Id = @Id";
cmdCount.Parameters.AddWithValue("@Id", oAttachment.Id);
}
if (oAttachment.FileType > 0)
{
command += " AND FileType = @FileType";
cmdCount.Parameters.AddWithValue("@FileType", oAttachment.FileType);
}
if (!string.IsNullOrEmpty(oAttachment.FileTitle))
{
command += " AND (FileTitle Like @FileTitle OR FileDescription Like @FileTitle)";
cmdCount.Parameters.AddWithValue("@FileTitle", "%" + oAttachment.FileTitle + "%");
}
cmdCount.CommandText = command;
if (connCount.State != ConnectionState.Open)
{
connCount.Open();
}
count = Convert.ToInt32(cmdCount.ExecuteScalar());
connCount.Close();
}
}
}
if (lstAttachment.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstAttachment;
oResult.RowCount = count;
}
}
}
}
//catch (Exception ex)
//{
// oResult.Message = ex.Message;
// oResult.HasResult = false;
//}
finally
{
conn.Close();
}
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 <DTO.Account.UserAccounts> UserAccountUpdate(DTO.Account.UserAccounts oUserAccount)
{
var oResult = new ModelResult <DTO.Account.UserAccounts>();
var conn = new SqlConnection(DbConnection.ConnectionString);
try
{
using (conn)
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.CommandText = "SP_AccountAddEdit";
if (oUserAccount.Id > 0)
{
cmd.Parameters.AddWithValue("@Id", oUserAccount.Id);
}
if (oUserAccount.UserTypeId > 0)
{
cmd.Parameters.AddWithValue("@UserTypeId", oUserAccount.UserTypeId);
}
if (!string.IsNullOrEmpty(oUserAccount.Name))
{
cmd.Parameters.AddWithValue("@Name", oUserAccount.Name);
}
if (!string.IsNullOrEmpty(oUserAccount.Email))
{
cmd.Parameters.AddWithValue("@Email", oUserAccount.Email);
}
if (!string.IsNullOrEmpty(oUserAccount.Pass))
{
cmd.Parameters.AddWithValue("@Pass", Common.Md5(oUserAccount.Pass));
}
if (!string.IsNullOrEmpty(oUserAccount.Pass))
{
cmd.Parameters.AddWithValue("@EmailPassword", Common.Md5(oUserAccount.Pass));
}
if (!string.IsNullOrEmpty(oUserAccount.Mobile))
{
cmd.Parameters.AddWithValue("@Mobile", oUserAccount.Mobile);
}
cmd.Parameters.AddWithValue("@Avatar", oUserAccount.Avatar);
cmd.Parameters.AddWithValue("@IsActive", oUserAccount.IsActive);
cmd.Parameters.AddWithValue("@TraceUserActivity", oUserAccount.TraceUserActivity);
cmd.Parameters.AddWithValue("@ManagerGroupId", oUserAccount.ManagerGroupId);
cmd.Parameters.AddWithValue("@Gender", oUserAccount.Gender.Trim());
conn.Open();
oUserAccount.Id = Convert.ToInt32(cmd.ExecuteScalar());
oResult.HasResult = true;
oResult.Results = oUserAccount;
}
}
}
catch (Exception ex)
{
oResult.Message = ex.Message;
oResult.HasResult = false;
}
finally
{
conn.Close();
}
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);
}
//private static RijndaelCrypt rC = new RijndaelCrypt("ambiabhhm2883772");
public static ModelResult <List <DTO.Account.UserAccounts> > UserLogin(DTO.Account.LoginModel oUserAccount)
{
using (SqlConnection conn = new SqlConnection(DbConnection.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
//var command = @"select * from MD_Media where isDeleted = 0 ";
var command =
@"select a.*, t.Name As UserTypeName
from dbo.UserAccounts a
join UserType t On a.UserTypeId = t.Id
WHERE (a.Email=@Email OR a.Name=@Email OR a.Mobile=@Email)
and Pass=@Pass COLLATE SQL_Latin1_General_CP1_CS_AS";
if (!string.IsNullOrEmpty(oUserAccount.Email))
{
cmd.Parameters.AddWithValue("@Email", oUserAccount.Email);
}
if (!string.IsNullOrEmpty(oUserAccount.Password))
{
cmd.Parameters.AddWithValue("@Pass", Common.Md5(oUserAccount.Password));
}
//if (!string.IsNullOrEmpty(oUserAccount.Password))
//{
// cmd.Parameters.AddWithValue("@Pass", oUserAccount.Password);
//}
cmd.CommandText = command;
conn.Open();
var reader = cmd.ExecuteReader();
var lstUserAccount = new List <DTO.Account.UserAccounts>();
var oResult = new ModelResult <List <DTO.Account.UserAccounts> >();
if (reader.HasRows)
{
while (reader.Read())
{
var opUserAccount = new DTO.Account.UserAccounts();
if (reader["Avatar"] != DBNull.Value)
{
opUserAccount.Avatar = Convert.ToString(reader["Avatar"]);
}
if (reader["Email"] != DBNull.Value)
{
opUserAccount.Email = Convert.ToString(reader["Email"]);
}
if (reader["Gender"] != DBNull.Value)
{
opUserAccount.Gender = Convert.ToString(reader["Gender"]);
}
opUserAccount.Id = Convert.ToInt32(reader["Id"]);
if (reader["IsActive"] != DBNull.Value)
{
opUserAccount.IsActive = Convert.ToBoolean(reader["IsActive"]);
}
if (reader["IsDeleted"] != DBNull.Value)
{
opUserAccount.IsDeleted = Convert.ToBoolean(reader["IsDeleted"]);
}
if (reader["Mobile"] != DBNull.Value)
{
opUserAccount.Mobile = Convert.ToString(reader["Mobile"]);
}
if (reader["UserTypeId"] != DBNull.Value)
{
opUserAccount.UserTypeId = Convert.ToInt32(reader["UserTypeId"]);
}
if (reader["Name"] != DBNull.Value)
{
opUserAccount.Name = Convert.ToString(reader["Name"]);
}
if (reader["Pass"] != DBNull.Value)
{
opUserAccount.Pass = Convert.ToString(reader["Pass"]);
}
if (reader["EmailPassword"] != DBNull.Value)
{
opUserAccount.EmailPassword = Convert.ToString(reader["EmailPassword"]);
}
opUserAccount.ManagerGroupId = Convert.ToInt32(reader["ManagerGroupId"]);
if (reader["TraceUserActivity"] != DBNull.Value)
{
opUserAccount.TraceUserActivity = Convert.ToBoolean(reader["TraceUserActivity"]);
}
//if (reader["BenId"] != DBNull.Value)
// opUserAccount.BenId = Convert.ToInt32(reader["BenId"]);
//if (reader["BenTypeId"] != DBNull.Value)
// opUserAccount.BenTypeId = Convert.ToInt32(reader["BenTypeId"]);
lstUserAccount.Add(opUserAccount);
}
}
if (lstUserAccount.Count > 0)
{
oResult.HasResult = true;
oResult.Results = lstUserAccount;
//oResult.RowCount = count;
}
return(oResult);
}
}
}
