/// <summary>
/// Carga un alumno
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Id = Convert.ToInt32(result.Rows[0]["id"]);
this.Precio = Convert.ToDecimal(result.Rows[0]["precio"]);
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Carga un alumno
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Id = Convert.ToInt32(result.Rows[0]["id"]);
this.Descripcion = result.Rows[0]["descripcion"].ToString();
this.Valor = Convert.ToInt32(result.Rows[0]["valor"].ToString());
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Carga un alumno
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Id = Convert.ToInt32(result.Rows[0]["id"]);
this.Nombre = result.Rows[0]["nombre"].ToString();
this.FechaInicio = Convert.ToDateTime(result.Rows[0]["fechaInicio"].ToString());
this.FechaFin = Convert.ToDateTime(result.Rows[0]["fechaFin"].ToString());
return(true);
}
else
{
return(false);
}
}
public static bool AddProject(Project project, int userId)
{
string queryChecking = $" select * from tasks.userkind_to_access where(access_id=2 and user_kind_id=(select user_kind_id from tasks.users where (user_id={userId})))";
var isAbleTo = DBaccess.RunScalar(queryChecking);
if (isAbleTo != null)
{
string query = $"INSERT INTO tasks.projects " +
$"(`project_name`, `client_name`, `team_leader_id`, `develope_hours`,`qa_hours`,`ui/ux_hours`,`start_date`,`finish_date`)" +
$" VALUES ('{project.ProjectName}','{project.ClientName}'," +
$"'{project.TeamLeaderId}',{project.DevelopersHours},{project.QaHours},{project.UiUxHours}," +
$"'{project.StartDate.Year}-{project.StartDate.Month}-{project.StartDate.Day}','{project.FinishDate.Year}-{project.FinishDate.Month}-{project.FinishDate.Day}')";
return(DBaccess.RunNonQuery(query) == 1);
}
else
{
return(false);
}
}
public static List <WorkerToProject> GetWorkersToProjectByProjectId(int projectId)
{
string query = $"SELECT * FROM tasks.worker_to_project WHERE project_id={projectId}";
Func <MySqlDataReader, List <WorkerToProject> > func = (reader) =>
{
List <WorkerToProject> workerToProjects = new List <WorkerToProject>();
while (reader.Read())
{
workerToProjects.Add(new WorkerToProject
{
WorkerToProjectId = reader.GetInt32(0),
UserId = reader.GetInt32(1),
ProjectId = reader.GetInt32(2),
Hours = reader.GetInt32(3),
});
}
return(workerToProjects);
};
return(DBaccess.RunReader(query, func));
}
public static List <Project> GetAllProjects()
{
try
{
string query = $"SELECT p.*,user_id,user_name FROM tasks.projects P JOIN tasks.users u ON u.user_id=p.team_leader_id ";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> projects = new List <Project>();
while (reader.Read())
{
projects.Add(new Project
{
ProjectId = reader.GetInt32(0),
ProjectName = reader.GetString(1),
ClientName = reader.GetString(2),
TeamLeaderId = reader.GetInt32(3),
DevelopersHours = reader.GetInt32(4),
QaHours = reader.GetInt32(5),
UiUxHours = reader.GetInt32(6),
StartDate = reader.GetDateTime(7),
FinishDate = reader.GetDateTime(8),
IsActive = reader.GetBoolean(9),
User = new User()
{
UserId = reader.GetInt32(10), UserName = reader.GetString(11)
}
});
}
return(projects);
};
return(DBaccess.RunReader(query, func));
}
catch (Exception ex)
{
var x = ex.StackTrace;
throw ex;
}
}
/// <summary>
/// Agrega registros de alumnos
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool UpSert()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_UPSERT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DBaccess.ParameterAdd(cmd, "@profesor", SqlDbType.VarChar, this.Profesor);
DBaccess.ParameterAdd(cmd, "@grado", SqlDbType.VarChar, this.Grado);
DBaccess.ParameterAdd(cmd, "@tipoGrado", SqlDbType.VarChar, this.TipoGrado);
DBaccess.ParameterAdd(cmd, "@cedula", SqlDbType.VarChar, this.Cedula);
DBaccess.ParameterAdd(cmd, "@institucion", SqlDbType.VarChar, this.Institucion);
DBaccess.ParameterAdd(cmd, "@fecha", SqlDbType.DateTime, this.Fecha);
if (this._id == 0)
{
this._id = Convert.ToInt32(DBaccess.EjecutarSQLScalar(cmd));
return(this._id > 0);
}
else
{
bool s = DBaccess.EjecutarSQLNonQuery(cmd) > 0;
return(s);
}
}
/// <summary>
/// Carga un alumno
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Id = Convert.ToInt32(result.Rows[0]["id"]);
this.IdContacto = Convert.ToInt32(result.Rows[0]["idContacto"]);
this.IdEmpresa = Convert.ToInt32(result.Rows[0]["idEmpresa"]);
this.FechaInicio = Convert.ToDateTime(result.Rows[0]["fechaRegistro"].ToString());
this.Status = Convert.ToBoolean(result.Rows[0]["status"]);
return(true);
}
else
{
return(false);
}
}
override protected ValidationResult IsValid(object value, ValidationContext validationContext)
{
object instance = validationContext.ObjectInstance;
Type type = instance.GetType();
PropertyInfo property = type.GetProperty("ProjectId");
object propertyValue = property.GetValue(instance);
int.TryParse(propertyValue.ToString(), out int ProjectId);
string query = $"SELECT project_id FROM tasks.projects WHERE project_name='{value}'";
var q1 = DBaccess.RunScalar(query);
string query2 = $"SELECT project_id FROM tasks.projects WHERE (project_id={ProjectId} AND project_name='{value}')";
var q2 = DBaccess.RunScalar(query2);
if (q1 != null && q2 == null)
{
return(new ValidationResult("the ProjectName is already exist"));
}
else
{
return(null);
}
}
public static List <ActualHours> GetActualHoursByUserId(int userId)
{
string query = $"SELECT * FROM tasks.actual_hours where user_id={userId}";
Func <MySqlDataReader, List <ActualHours> > func = (reader) =>
{
List <ActualHours> actualHours = new List <ActualHours>();
while (reader.Read())
{
actualHours.Add(new ActualHours
{
ActualHoursId = reader.GetInt32(0),
UserId = reader.GetInt32(1),
ProjectId = reader.GetInt32(2),
CountHours = reader.GetInt32(3),
date = reader.GetDateTime(4),
});
}
return(actualHours);
};
return(DBaccess.RunReader(query, func));
}
/// <summary>
/// Agrega registros de alumnos
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool UpSert()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_UPSERT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DBaccess.ParameterAdd(cmd, "@nombre", SqlDbType.VarChar, this.Nombre);
DBaccess.ParameterAdd(cmd, "@apellidoP", SqlDbType.VarChar, this.ApPaterno);
DBaccess.ParameterAdd(cmd, "@apellidoM", SqlDbType.VarChar, this.ApMaterno);
DBaccess.ParameterAdd(cmd, "@celular", SqlDbType.VarChar, this.Celular);
DBaccess.ParameterAdd(cmd, "@radio", SqlDbType.VarChar, this.Radio);
DBaccess.ParameterAdd(cmd, "@email", SqlDbType.VarChar, this.Email);
DBaccess.ParameterAdd(cmd, "@status", SqlDbType.Bit, this.Status);
if (this._id == 0)
{
this._id = Convert.ToInt32(DBaccess.EjecutarSQLScalar(cmd));
return(this._id > 0);
}
else
{
bool s = DBaccess.EjecutarSQLNonQuery(cmd) > 0;
return(s);
}
}
/// <summary>
/// Agrega registros de alumnos
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool UpSert()
{
int n = 0;
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_UPSERT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DBaccess.ParameterAdd(cmd, "@idContacto", SqlDbType.Int, this.IdContacto);
DBaccess.ParameterAdd(cmd, "@idEmpresa", SqlDbType.Int, this.IdEmpresa);
DBaccess.ParameterAdd(cmd, "@fechaRegistro", SqlDbType.DateTime, this.FechaInicio);
DBaccess.ParameterAdd(cmd, "@status", SqlDbType.Bit, this.Status);
if (this._id == 0)
{
this._id = Convert.ToInt32(DBaccess.EjecutarSQLScalar(cmd));
return(this._id > 0);
}
else
{
n = DBaccess.EjecutarSQLNonQuery(cmd);
//return s;
}
return(n > 0);
}
public static List <User> SignIn(string userName, string password)
{
string query = $"SELECT * FROM tasks.users WHERE user_name='{userName}'and password='******'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(new User
{
UserId = reader.GetInt32(0),
UserName = reader.GetString(1),
UserEmail = reader.GetString(2),
Password = reader.GetString(3),
TeamLeaderId = reader.GetInt32(4),
UserKindId = reader.GetInt32(5),
});
}
return(users);
};
return(DBaccess.RunReader(query, func));
}
public static List <User> GetWorkerbyProjectName(string projectName)
{
string query = $"SELECT * FROM tasks.users u JOIN tasks.worker_to_project w on u.user_id = w.user_id WHERE w.project_id =(SELECT project_id FROM tasks.projects WHERE project_name='{projectName}' )";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(new User
{
UserId = reader.GetInt32(0),
UserName = reader.GetString(1),
UserEmail = reader.GetString(2),
Password = reader.GetString(3),
TeamLeaderId = reader.GetInt32(4),
UserKindId = reader.GetInt32(5),
});
}
return(users);
};
return(DBaccess.RunReader(query, func));
}
/// <summary>
/// Carga un usuario
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIX + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this._id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Name = result.Rows[0]["name"].ToString();
this.UserName = result.Rows[0]["username"].ToString();
this.Password = result.Rows[0]["password"].ToString();
this.RegistrationDate = Convert.ToDateTime(result.Rows[0]["regDate"]);
this.ExpirationDate = Convert.ToDateTime(result.Rows[0]["expDate"]);
this.Status = Convert.ToBoolean(result.Rows[0]["status"]);
return(true);
}
else
{
return(false);
}
}
public static List <User> getUsersByTeamLeaderId(int teamLeaderId)
{
string query = $"SELECT * FROM tasks.users WHERE team_leader_id={teamLeaderId}";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(new User
{
UserId = reader.GetInt32(0),
UserName = reader.GetString(1),
UserEmail = reader.GetString(2),
Password = reader.GetString(3),
TeamLeaderId = reader.GetInt32(4),
UserKindId = reader.GetInt32(5),
});
}
return(users);
};
return(DBaccess.RunReader(query, func));
}
/// <summary>
/// Carga un alumno
/// </summary>
/// <returns>'true' si fue correcto, 'false' si fue incorrecto</returns>
public bool Load()
{
SqlCommand cmd = DBaccess.CreateSQLCommand(K_PREFIJO + "_SELECT");
DBaccess.ParameterAdd(cmd, "@id", SqlDbType.Int, this.Id);
DataTable result = DBaccess.ExecuteSQLSelect(cmd);
if (result.Rows.Count > 0)
{
this.Id = Convert.ToInt32(result.Rows[0]["id"]);
this.Profesor = Convert.ToInt32(result.Rows[0]["profesor"].ToString());
this.Grado = result.Rows[0]["grado"].ToString();
this.TipoGrado = Convert.ToInt32(result.Rows[0]["tipoGrado"].ToString());
this.Cedula = result.Rows[0]["cedula"].ToString();
this.Institucion = result.Rows[0]["institucion"].ToString();
this.Fecha = Convert.ToDateTime(result.Rows[0]["fecha"].ToString());
return(true);
}
else
{
return(false);
}
}
public static bool RemoveUser(int id, int userId)
{
string queryChecking = $" select * from tasks.userkind_to_access where(access_id=2 and user_kind_id=(select user_kind_id from tasks.users where (user_id={userId})))";
var isAbleTo = DBaccess.RunScalar(queryChecking);
if (isAbleTo != null)
{
//delete the rows in actualHours
string query1 = $"DELETE FROM tasks.actual_hours WHERE user_id={id}";
DBaccess.RunNonQuery(query1);
//delete the rows in worker to project table
query1 = $"DELETE FROM tasks.worker_to_project WHERE user_id={id}";
DBaccess.RunNonQuery(query1);
string query = $"DELETE FROM tasks.users WHERE user_id={id}";
return(DBaccess.RunNonQuery(query) == 1);
}
else
{
return(false);
}
}
public static List <Project> GetProjectsByUserId(int userId)
{
try
{
string query = $"SELECT * FROM tasks.projects p JOIN TASKS.worker_to_project w ON p.project_id=w.project_id WHERE user_id={userId} AND is_active=1";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> projects = new List <Project>();
while (reader.Read())
{
projects.Add(new Project
{
ProjectId = reader.GetInt32(0),
ProjectName = reader.GetString(1),
ClientName = reader.GetString(2),
TeamLeaderId = reader.GetInt32(3),
DevelopersHours = reader.GetInt32(4),
QaHours = reader.GetInt32(5),
UiUxHours = reader.GetInt32(6),
StartDate = reader.GetDateTime(7),
FinishDate = reader.GetDateTime(8),
IsActive = reader.GetBoolean(9),
});
}
return(projects);
};
return(DBaccess.RunReader(query, func));
}
catch (Exception ex)
{
var x = ex.StackTrace;
throw ex;
}
}
public static List <TreeTable> GetAllTreeTable()
{
string query = $"SELECT p.*,user_id,user_name FROM tasks.projects P JOIN tasks.users u ON u.user_id=p.team_leader_id ";
Func <MySqlDataReader, List <TreeTable> > func = (reader) =>
{
treeTables = new List <TreeTable>();
while (reader.Read())
{
treeTables.Add(new TreeTable
{
Project = new Project()
{
ProjectId = reader.GetInt32(0),
ProjectName = reader.GetString(1),
ClientName = reader.GetString(2),
TeamLeaderId = reader.GetInt32(3),
DevelopersHours = reader.GetInt32(4),
QaHours = reader.GetInt32(5),
UiUxHours = reader.GetInt32(6),
StartDate = reader.GetDateTime(7),
FinishDate = reader.GetDateTime(8),
IsActive = reader.GetBoolean(9),
User = new User()
{
UserId = reader.GetInt32(10), UserName = reader.GetString(11)
}
}
});
}
return(treeTables);
};
DBaccess.RunReader(query, func);
FillHoursToTreeTable();
return(treeTables);
}
public static bool CheckUserIp(string userIp)
{
string query = $"select user_ip from tasks.users where user_ip={userIp}";
return(DBaccess.RunNonQuery(query) == 1);
}
public static int getProjectId(string projectName)
{
string query = $"SELECT project_id FROM tasks.projects WHERE project_name='{projectName}'";
return((int)DBaccess.RunScalar(query));
}
// ==================================|
// Methods dealing with transactions |
// ==================================|
public void Sell(int transactionNumber, string date)
{
DBaccess.DecrementInventory(TableNames.INVENTORY, this);
DBaccess.AddToTransactionTable(TableNames.TRANSACTION, this, TransactionTypes.SALE, transactionNumber, date);
}
public static bool UpdateUserIp(int userId)
{
string query = $"UPDATE tasks.users SET user_ip='0' WHERE user_id={userId}";
return(DBaccess.RunNonQuery(query) == 1);
}
public static bool UpdateUser(User user, int userId)
{
string query = $"UPDATE tasks.users SET user_name='{user.UserName}', user_email='{user.UserEmail}',password='******',team_leader_id={user.TeamLeaderId},user_kind_id={user.UserKindId},user_ip='{user.UserIP}', verify_password='******' WHERE user_id={user.UserId}";
return(DBaccess.RunNonQuery(query) == 1);
}
public static bool UpdatePassword(User user)
{
string query = $"UPDATE tasks.users SET password='******'WHERE user_id={user.UserId}";
return(DBaccess.RunNonQuery(query) == 1);
}
public static string GetUserId(string userName, string password)
{
string query = $"SELECT user_id FROM tasks.users WHERE user_name='{userName}'&&password='******'";
return(DBaccess.RunScalar(query).ToString());
}
public static bool UpdateWorkerToProject(WorkerToProject workerToProject)
{
string query = $"UPDATE tasks.worker_to_project SET user_id='{workerToProject.UserId}',project_id='{workerToProject.ProjectId}',hours={workerToProject.Hours} WHERE worker_to_project_id={workerToProject.WorkerToProjectId}";
return(DBaccess.RunNonQuery(query) == 1);
}
public static bool AddWorkersByTeamLeaderId(int projectId, int userId)
{
string query = $" INSERT INTO `tasks`.`worker_to_project` (`user_id`, `project_id`) VALUES ('{userId}','{projectId}')";
return(DBaccess.RunNonQuery(query) == 1);
}
public static bool RemoveWorkerToProject(int id)
{
string query = $"DELETE FROM tasks.worker_to_project WHERE project_id={id}";
return(DBaccess.RunNonQuery(query) == 1);
}
