public IPeople GetByLoginDetails(string emailId, string password)
{
IPeople user = new MyDiary.Domain.Domains.People();
user.UserRoles = new List <IRole>()
{
new Role()
};
user.UserImages = new List <IImage>()
{
new Image()
};
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USERLOGIN_SELECT);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, emailId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.Password, password);
DataTable dtUser = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd));
if (dtUser != null && dtUser.Rows.Count > 0)
{
user = Map_PeopleDataRow_To_PeopleDomain(dtUser.Rows[0]);
}
return(user);
}
public int AddUser(IPeople user)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USER_ADD);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.FirstName, user.FirstName);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.MiddleName, user.MiddleName == null ? string.Empty : user.MiddleName);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.LastName, user.LastName);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, user.EmailId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteUserId, user.SiteUserId); //optional
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteId, user.SiteId); //optional
var userRole = user.UserRoles.FirstOrDefault();
if (userRole != null)
{
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.RoleId, userRole.RoleId);
var userImage = user.UserImages.FirstOrDefault();
if (userImage != null)
{
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.ImageId, userImage.ImageId);
}
}
object result = cmd.ExecuteScalar(); //if image exists 3 tables has to be affected otherwise two
return(int.Parse(result.ToString()));
}
public IPeople GetByOpenSiteDetails(int siteId, string siteUserId)
{
IPeople user = new MyDiary.Domain.Domains.People();
user.UserRoles = new List <IRole>()
{
new Role()
};
user.UserImages = new List <IImage>()
{
new Image()
};
try
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.DRY_USER_GET_BYOPENSITEDETAILS);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteId, siteId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.SiteUserId, siteUserId);
DataTable dtUser = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd));
if (dtUser != null && dtUser.Rows.Count > 0)
{
user = Map_PeopleDataRow_To_PeopleDomain(dtUser.Rows[0]);
}
return(user);
}
catch (Exception)
{
return(user);
}
}
private DataTable GetAllIncomeTypesAsDataTable(int userId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.IncomeTypes.INCOMETYPES_SELECTBYUSERID);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId);
SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd);
conn.Close();
return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter));
}
public void Delete(UserDO user)
{
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.CommandText = $"DELETE FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Id} = @id";
command.Parameters.Add(new SqlParameter("@id", user.Id));
command.ExecuteNonQuery();
connection.Close();
}
}
private DataTable GetFilteredIncomesDatatTable(int userId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.DRY_GETFILTEREDINCOMES);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId);
SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd);
conn.Close();
return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter));
}
private DataTable GetAllIncomesDatatTable(int userId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.Incomes_SelectByUserId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, userId);
SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd);
conn.Close();
return(SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter));
}
public List <UserDO> GetAll()
{
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM {DbSchema.Users.Table}";
var result = ExecuteRead(command);
connection.Close();
return(result);
}
}
public List <UserDO> GetById(long id)
{
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Id} = @id";
command.Parameters.Add(new SqlParameter("@id", id));
var result = ExecuteRead(command);
connection.Close();
return(result);
}
}
public void Delete(int expenseId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Delete);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseId, expenseId);
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
}
public void AddUserLoginInformation(IUserLogin userLogin)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.USERLOGIN_ADD);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.UserId, userLogin.UserId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.EmailId, userLogin.EmailId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.People.Parameters.Password, userLogin.Password);
cmd.ExecuteNonQuery(); //if image exists 3 tables has to be affected otherwise two
}
public List <UserDO> GetByUsername(string usrname)
{
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM {DbSchema.Users.Table} WHERE {DbSchema.Users.Alias} = @usrname";
command.Parameters.Add(new SqlParameter("@usrname", usrname));
var result = ExecuteRead(command);
connection.Close();
return(result);
}
}
private DataTable GetFilteredExpensesDataTable(string date, string type, int userId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.ExpenseTypes.DRY_GETALLEXPENSES_BYDATEANDTYPE);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, date);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseType, type);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, userId);
SqlDataAdapter sDataAdapter = SQLDbConnection.GetNewSqlDataAdapterObject(cmd);
DataTable dtExpenses = SQLDbConnection.FillDataSetFromDataAdapter(sDataAdapter);
conn.Close();
return(dtExpenses);
}
public bool AddExpenseType(string expenseType, int userId)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.ExpenseTypes.EXPENSETYPE_ADD);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.ExpenseTypes.Parameters.ExpenseType, expenseType);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.ExpenseTypes.Parameters.UserId, userId);
bool result = cmd.ExecuteNonQuery() == 1 ? true : false;
conn.Close();
return(bool.Parse(result.ToString()));
}
public int Add(IIncomeType incomeType)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.IncomeTypes.INCOMETYPE_ADD);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.IncomeTypes.Parameters.INCOMETYPE, incomeType.Type);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.IncomeTypes.Parameters.USERID, incomeType.UserId);
int incomeTypeId = int.Parse(cmd.ExecuteScalar().ToString());
conn.Close();
return(incomeTypeId);
}
public List <IPeople> GetAll()
{
List <IPeople> users = new List <IPeople>();
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.People.DRY_GETALLUSERS);
DataTable dtUsers = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd));
if (dtUsers != null && dtUsers.Rows.Count > 0)
{
users.AddRange(this.Map_PeopleDataTable_To_PeopleList(dtUsers));
}
return(users);
}
public IImage GetUploadImageById(int imageId)
{
IImage uploadImage = new MyDiary.Domain.Domains.Image(null);
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Image.Image_SelectById);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.ImageId, imageId);
DataTable dtUploadImage = SQLDbConnection.FillDataSetFromDataAdapter(SQLDbConnection.GetNewSqlDataAdapterObject(cmd));
if (dtUploadImage != null && dtUploadImage.Rows.Count > 0)
{
uploadImage = GetImageFromDataTable(dtUploadImage);
}
return(uploadImage);
}
public int AddIncome(IIncome income)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.INCOME_ADD);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, income.UserId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMETYPEID, income.IncomeType.TypeId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.AMOUNT, income.Amount);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEDATE, income.IncomeDate);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.DESCRIPTION, income.Description);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.COMMENTS, income.Comments);
int incomeTypeId = int.Parse(cmd.ExecuteScalar().ToString());
conn.Close();
return(incomeTypeId);
}
public int Add(IExpense expenseDomain)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Add);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseTypeId, expenseDomain.ExpenseType.TypeId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, expenseDomain.ExpenseDate);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Description, expenseDomain.Description);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Comments, expenseDomain.Comments);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Amount, expenseDomain.Amount);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, expenseDomain.CurrentUser.UserId);
object result = cmd.ExecuteScalar();
conn.Close();
return(int.Parse(result.ToString()));
}
public void Insert(UserDO user)
{
string hashedPassword = EncriptadorIrreversible.EncriptarIrreversible(user.Password);
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.Parameters.Add(new SqlParameter("@username", user.Alias));
command.Parameters.Add(new SqlParameter("@password", hashedPassword));
command.Parameters.Add(new SqlParameter("@name", user.Nombre));
command.Parameters.Add(new SqlParameter("@lastname", user.Apellido));
command.Parameters.Add(new SqlParameter("@email", user.Email));
command.CommandText = $"INSERT INTO {DbSchema.Users.Table} ({DbSchema.Users.ListColumns()}) " +
$"VALUES(@username, @password, @name, @lastname, @email)";
command.ExecuteNonQuery();
connection.Close();
}
}
public void Update(IIncome incomeDomain)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Incomes.Income_Update);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEID, incomeDomain.IncomeId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.USERId, incomeDomain.UserId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMETYPEID, incomeDomain.IncomeType.TypeId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.INCOMEDATE, incomeDomain.IncomeDate);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.DESCRIPTION, incomeDomain.Description);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.COMMENTS, incomeDomain.Comments);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.AMOUNT, incomeDomain.Amount);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Incomes.Parameters.MODIFIEDBY, incomeDomain.UserId);
cmd.ExecuteNonQuery();
cmd.Dispose(); //ToDo ==> If this is success ,implement this in all methods...
conn.Close();
}
public void Update(IExpense expenseDomain)
{
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Expenses.Expense_Update);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseId, expenseDomain.Id);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.UserId, expenseDomain.CurrentUser.UserId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseTypeId, expenseDomain.ExpenseType.TypeId);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.ExpenseDate, expenseDomain.ExpenseDate);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Description, expenseDomain.Description);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Comments, expenseDomain.Comments);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.Amount, expenseDomain.Amount);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Expenses.Parameters.MODIFIEDBY, expenseDomain.CurrentUser.UserId);
cmd.ExecuteNonQuery();
cmd.Dispose(); //ToDo ==> If this is success ,implement this in all methods...
conn.Close();
}
public void Update(UserDO user)
{
string hashedPassword = EncriptadorIrreversible.EncriptarIrreversible(user.Password);
using (var connection = SQLDbConnection.GetConnection())
{
var command = connection.CreateCommand();
command.Parameters.Add(new SqlParameter("@id", user.Id));
command.Parameters.Add(new SqlParameter("username", user.Alias));
command.Parameters.Add(new SqlParameter("@password", hashedPassword));
command.Parameters.Add(new SqlParameter("@name", user.Nombre));
command.Parameters.Add(new SqlParameter("@lastname", user.Apellido));
command.Parameters.Add(new SqlParameter("@email", user.Email));
command.CommandText = $"UPDATE {DbSchema.Users.Table} " +
$"SET {DbSchema.Users.Nombre} = @username, {DbSchema.Users.Password} = @password, " +
$"{DbSchema.Users.Nombre} = @name, {DbSchema.Users.Apellido} = @lastname, " +
$"{DbSchema.Users.Email} = @email " +
$"WHERE {DbSchema.Users.Id} = @id";
command.ExecuteNonQuery();
connection.Close();
}
}
public int UploadImage(IImage image)
{
int uploadedImageId = -1;
if (image == null)
{
return(uploadedImageId);
}
SqlConnection conn = SQLDbConnection.GetNewSqlConnectionObject();
conn.Open();
SqlCommand cmd = SQLDbConnection.GetNewSqlCommandObject(conn, Constants.StoredProcedures.Image.Image_Add);
cmd.Parameters.AddWithValue(Constants.StoredProcedures.Image.Parameters.IMAGE, image.UserImage);
object result = cmd.ExecuteScalar();
if (result != null)
{
uploadedImageId = int.Parse(result.ToString());
}
;
return(uploadedImageId);
}
