public SearchResult <CourseList> GetTotalTopic()
{
SearchParameter search = new SearchParameter();
search.ClearFilterList();
search.SortTable = "CourseList";
search.SortColumn = "CourseID";
search.SortAscending = true;
const string column = @"SELECT CourseList.CourseID,count(1) as TotalTopic";
const string source = @"FROM CourseList GROUP BY CourseID";
var result = new SearchResult <CourseList>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <CourseList>(true);
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public SearchResult <CourseList> GetCourseListByID(SearchParameter search)
{
const string column = @"SELECT CourseList.*,MSContentType.Id AS MSContentTypeID, MSContentType.*";
const string source = @"FROM CourseList LEFT JOIN MSContentType
ON MSContentType.ID = CourseList.ContentID";
var result = new SearchResult <CourseList>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <CourseList, MSContentType, CourseList>((courseList, masterContentType) =>
{
courseList.ContentMaster = masterContentType;
return(courseList);
}, "MSContentTypeID");
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public SearchResult <MSCategory> GetMSCategoryBySearch(SearchParameter search)
{
const string column = @"SELECT MSCATEGORY.ID
,MSCATEGORY.NAME_L1 AS NAME_L1
,MSCATEGORY.NAME_L2 AS NAME_L2
,MSCATEGORY.NAME_L3 AS NAME_L3
,MSCATEGORY.NAME_L4 AS NAME_L4
,MSCATEGORY.DESCRIPTION_L1 AS DESCRIPTION_L1
,MSCATEGORY.DESCRIPTION_L2 AS DESCRIPTION_L2
,MSCATEGORY.DESCRIPTION_L3 AS DESCRIPTION_L3
,MSCATEGORY.DESCRIPTION_L4 AS DESCRIPTION_L4
,MSCATEGORY.EFFDATE AS EFFDATE
,MSCATEGORY.EXPDATE AS EXPDATE
,CONVERT(VARCHAR, MSCATEGORY.EFFDATE, 101) AS EffDateShow
,CONVERT(VARCHAR, MSCATEGORY.EXPDATE, 101) AS ExpDateShow
,CONVERT(VARCHAR, MSCATEGORY.UPDATEDATE, 101) AS UpdateDate
,EMPLOYEE.Id AS EmpId
,(EMPLOYEE.FIRSTNAME_L1 + EMPLOYEE.LASTNAME_L1) AS UpdateBy_L1
,(EMPLOYEE.FIRSTNAME_L2 + EMPLOYEE.LASTNAME_L2) AS UpdateBy_L2
,(EMPLOYEE.FIRSTNAME_L3 + EMPLOYEE.LASTNAME_L3) AS UpdateBy_L3
,(EMPLOYEE.FIRSTNAME_L4 + EMPLOYEE.LASTNAME_L4) AS UpdateBy_L4";
const string source = @" FROM MSCATEGORY
LEFT JOIN EMPLOYEE
ON MSCATEGORY.UPDATEBY = EMPLOYEE.ID" ;
var result = new SearchResult <MSCategory>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <MSCategory, Employee, MSCategory>((masterCategory, Employee) =>
{
return(masterCategory);
}, "EmpId");
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public SearchResult <Employee> GetEmployee(SearchParameter search)
{
const string column = @"SELECT Employee.*
,MSDivision.Id AS MSDivisionID
,MSDivision.*
,MSDepartment.Id AS MSDepartmentID
,MSDepartment.*
,MSPosition.Id AS MSPositionID
,MSPosition.*";
const string source = @"FROM Employee LEFT JOIN MSDepartment
ON MSDepartment.ID = Employee.DeptID
LEFT JOIN MSDivision
ON MSDivision.ID = Employee.DivID
LEFT JOIN MSPosition
ON MSPosition.ID = Employee.PositionID";
var result = new SearchResult <Employee>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <Employee, MSDepartment, MSDivision, MSPosition, Employee>((employee, department, division, position) =>
{
employee.Department = department;
employee.Division = division;
employee.Position = position;
return(employee);
}, "MSDivisionID,MSDepartmentID,MSPositionID");
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public SearchResult <MSWorkExperience> GetWorkExp(SearchParameter search)
{
const string column = @"SELECT MSWorkExperience.*";
const string source = @"FROM MSWorkExperience ";
var result = new SearchResult <MSWorkExperience>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <MSWorkExperience>(true);
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public SearchResult <Course> GetCourseJoin(SearchParameter search)
{
const string column = @"SELECT (SELECT Count(1) FROM CourseList WHERE CourseList.CourseID = Course.ID) AS TotalTopic
,Course.*
,MSCategory.Id AS MSCategoryID
, MSCategory.*
,Employee.Id AS EmployeeID
,Employee.*";
const string source = @"FROM Course LEFT JOIN MSCategory
ON MSCategory.ID = Course.CategoryID
LEFT JOIN Employee
ON Employee.ID = Course.UpdateBy";
var result = new SearchResult <Course>();
var query = search.CreateQuery(column, source);
try
{
using (var multi = this.DbFactory.Connection.QueryMultiple(query))
{
var count = multi.Read <int>().FirstOrDefault();
var resultList = multi.Read <Course, MSCategory, Employee, Course>((course, masterCategory, masterEmployee) =>
{
course.CatMaster = masterCategory;
course.EmpMaster = masterEmployee;
return(course);
}, "MSCategoryID,EmployeeID");
result.ResultList = resultList.AsQueryable();
result.TotalRecord = count;
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}