public static DataTable GetAllByUserID(Guid userID)
{
//DataTable dt = DBL.ExecuteQuery($"select * from Project inner join UserProject on Project.ProjectId=UserProject.FK_ProjectId inner join aspnet_Users on aspnet_Users.UserId=UserProject.FK_ProjectId where aspnet_Users.UserId = {userID}");
DataTable dt = DBL.ExecuteQuery($"select * from Project where FK_UserId='{userID}'");
return(dt);
}
public static DataTable GetByAwardId(string Id)
{
DataTable dt = DBL.ExecuteQuery("select * from Education where Education.EducationId = " + Id);
return(dt);
}
public static DataTable InsertCertNameDate(string CertificateName, string CertificateDate, Guid FK_UserId)
{
string sss = $"insert into Certificate(CertificateName , CertificateDate,FK_UserId) values('{CertificateName}', '{ CertificateDate }','{ FK_UserId }' )";
DataTable dt = DBL.ExecuteQuery(sss);
return(dt);
}
public static DataTable GetByAwardId(string Id)
{
DataTable dt = DBL.ExecuteQuery("select * from Award where Award.AwardId = " + Id);
return(dt);
}
public static DataTable GetByUserId(Guid Id)
{
DataTable dt = DBL.ExecuteQuery("select * from Award where Award.FK_UserId = '" + Id + "' ");
return(dt);
}
public static DataTable GetByCertificateId(int CertificateId)
{
DataTable dt = DBL.ExecuteQuery("select * from Certificate where Certificate.CertificateId = " + CertificateId);
return(dt);
}
public static DataTable GetAll()
{
DataTable dt = DBL.ExecuteQuery("select * from Certificate");
return(dt);
}
public static DataTable GetByVolExpID(int volExpID)
{
DataTable dt = DBL.ExecuteQuery($"select * from VolExperience where Id='{volExpID}'");
return(dt);
}
public static DataTable GetByUserID(Guid userID)
{
DataTable dt = DBL.ExecuteQuery($"select * from VolExperience where FK_UserId='{userID}'");
return(dt);
}
public static DataTable GetByFK_UserId_(Guid FK_UserId)
{
DataTable dt = DBL.ExecuteQuery($"select * from Certificate where FK_UserId= '{FK_UserId}'");
return(dt);
}
public static DataTable GetAllTableMsg()
{
DataTable dt = DBL.ExecuteQuery("select * from Message");
return(dt);
}
public static DataTable AddMsg(Guid FK_FromId, Guid FK_ToId, string msg, string date)
{
DataTable dt = DBL.ExecuteQuery($"insert into Message (FK_FromId,FK_ToId,[Text],[Date]) values ('{FK_FromId}','{FK_ToId}','{msg}','{date}')");
return(dt);
}
public static DataTable deleteCertificateName(string CertificateName)
{
DataTable dt = DBL.ExecuteQuery("delete Certificate where CertificateName= " + CertificateName);
return(dt);
}
public static DataTable GetMsgByFKToId(int Fk_FromId, int FK_ToId)
{
DataTable dt = DBL.ExecuteQuery("select Text from Message where Fk_FromId and FK_ToId='" + Fk_FromId + "', " + FK_ToId + ")");
return(dt);
}
public static DataTable GetMsgBySelection(int FK_FromId, int FK_ToId)
{
DataTable dt = DBL.ExecuteQuery("select Distinct UserName , Text,Date from aspnet_Users join Message on UserId = FK_FromId or UserId = FK_ToId='" + FK_FromId + "', " + FK_ToId + ")");
return(dt);
}
public static DataTable deleteMessage(string MessageId)
{
DataTable dt = DBL.ExecuteQuery("delete Message where MessageId= " + MessageId);
return(dt);
}
public static DataTable GetAllUsers()
{
DataTable dt = DBL.ExecuteQuery("select UserName ,UserId from aspnet_Users");
return(dt);
}
public static DataTable GetAllMsgBw(Guid UserId, Guid otherId)
{
DataTable dt = DBL.ExecuteQuery($"select UserName,Text ,Date from Message join aspnet_Users on Message.FK_FromId = aspnet_Users.UserId where(FK_FromId = '{UserId}' and FK_ToId = '{otherId}') or (FK_FromId = '{otherId}' and FK_ToId = '{UserId}')");
return(dt);
}
public static DataTable GetByCertificateName(string CertificateName)
{
DataTable dt = DBL.ExecuteQuery("select * from Certificate where Certificate.CertificateName = " + CertificateName);
return(dt);
}
public static DataTable GetAllCourse()
{
DataTable dt = DBL.ExecuteQuery("select * from Course");
return(dt);
}
public static DataTable GetByFK_UserId(Guid FK_UserId)
{
DataTable dt = DBL.ExecuteQuery("select UserId,CertificateId,CertificateName,CONVERT(Date,CertificateDate) AS Date from aspnet_Users join Certificate on aspnet_Users.UserId =Certificate.FK_UserId= " + FK_UserId);
return(dt);
}
public static DataTable GetAllCourseById(int CourseId)
{
DataTable dt = DBL.ExecuteQuery("select * from Course where Course.CourseId=" + CourseId);
return(dt);
}
// select user experience by id
public static DataTable GetAllUserExperienceById(Guid UserId)
{
DataTable dt = DBL.ExecuteQuery("select * from UserWorkExperience where FK_UserId='" + UserId + "' ");
return(dt);
}
public static bool CheckNameInserted(string countryName)
{
DataTable dt = DBL.ExecuteQuery($"select count(Name) as NCount from Country where Name = '{countryName}'");
return(Convert.ToInt32(dt.Rows[0]["NCount"]) > 0);
}
public static DataTable deleteCertificate(int CertificateId)
{
DataTable dt = DBL.ExecuteQuery("delete Certificate where CertificateId= " + CertificateId);
return(dt);
}
public static DataTable GetAll()
{
DataTable dt = DBL.ExecuteQuery("select * from SkillLevel");
return(dt);
}
public static DataTable GetAllCourseAssocWith()
{
DataTable dt = DBL.ExecuteQuery("select CourseId,CourseAssociatedWith from Course");
return(dt);
}
public static DataTable GetById(string Id)
{
DataTable dt = DBL.ExecuteQuery($"select * from SkillLevel where SkillLevelId = {Id}");
return(dt);
}
public static DataTable GetAllUserCourseById(Guid UserId)
{
DataTable dt = DBL.ExecuteQuery("select Course.CourseName,aspnet_Users.UserId from UserCourse left join aspnet_Users on UserId = FK_UserId left join Course on CourseId =FK_CourseId where UserId =" + UserId);
return(dt);
}
public static DataTable GetAll()
{
DataTable dt = DBL.ExecuteQuery("select * from VolExperience");
return(dt);
}