public int getDepartmentProjects(ref DataSet ds, string tableName,
string departmentcode, int GetLevel,
ReportType typeReport, SubLevelTypeReport sublevel,
DateTime beginDate, DateTime endDate)
{
#region SQL truy vấn
string sql = "";
sql = @"with FullName_cte as
(
select
charindex(' ', s.name) -1 as FirstNameEnd,
len(s.name) - charindex(' ',reverse(s.name)) +2 as LastNameStart,
s.code, s.name, departmentcode, researchdepartmentcode,
academiclevelcode, academictitlecode, degreecode
from staff s LEFT join staffstatus on staffstatus.code = s.staffstatus
left join academiclevel on academiclevel.code = s.academiclevelcode
left join academictitle on academictitle.code = s.academictitlecode
left join degree on degree.code = s.degreecode
where isnull(staffstatus.isstaff,0) <= 1 and isnull(academiclevel.comparelevel,0) < 10
and s.researchstatus > 0
)
select distinct upper(project.name) projectName, project.authorprint author, projecttype.codeview papertype,
isnull(B.codeview, '') academictitle, isnull(B.name, '') academictitlename,
isnull(C.codeview,'') academiclevel, isnull(D.codeview,'') degree, isnull(D.name,'') degreename,
isnull(B.comparelevel, '99') academictitlecomparelevel, isnull(D.comparelevel,'99') degreecomparelevel,
department.code departmentcode, department.parentcode facultycode,
(select name from department tmp where tmp.code = department.parentcode) facultyname,
project.name, project.authorprint author, projecttype.codeview projecttype,
projecttype.name level, projectrole.name role, projectrole.codeview rolecodeview,
project.numberauthor numberauthors,
isnull(project.begintime,'') begintime, isnull(project.endtime,'') endtime,
isnull(project.begintimeshow,'') begindate, isnull(project.endtimeshow,'') enddate,
isnull(project.finishdate,'') finishdate, isnull(project.finishdateshow,'') finishdateshow,
project.codeview, isnull(project.management,'') management,
isnull(project.projectvalue,0) projectvalue,
case
when project.finishdateshow <> '' then project.finishdateshow + '/' + projectstatus.name
else projectstatus.name
end finishdateshowAndResult, isnull(project.finishstatus, '') finishstatus,
department.name department, T.name fullname, T.code,
substring(T.name,0,FirstNameEnd+1) as FirstName,
case
when FirstNameEnd+2 = LastNameStart then ''
else substring(T.name, FirstNameEnd+2, LastNameStart-FirstNameEnd-2)
end as MiddleName,
substring(T.name, LastNameStart, len(T.name)) as LastName
from staffproject
inner join project on project.code = staffproject.projectcode
inner join projecttype on projecttype.code = project.projecttypecode
inner join projectrole on projectrole.code = staffproject.rolecode
inner join FullName_cte T on T.code = staffproject.staffcode
inner join projectstatus on projectstatus.codelink = project.status
inner join department on department.code = T.departmentcode
left JOIN academictitle B ON T.academictitlecode=B.code
LEFT JOIN academiclevel C ON T.academiclevelcode=C.code
LEFT JOIN degree D ON T.degreecode = D.code
where (project.approvedstatus = 2) and projectrole.codeview = 'CH'
and not (begintime >@endDate OR dbo.maxdate(dbo.maxdate(endtime,retime1),retime2)<@beginDate) ";
//Cấp bộ môn
if (GetLevel == 0)
{
sql += @" and T.departmentcode = @departmentcode ";
}
else
if (GetLevel == 1) //Cấp khoa
{
sql += @" and T.departmentcode in (select code from department where parentcode = @departmentcode) ";
}
sql += @" order by begintime desc, department.name, fullname ";
#endregion
DataTable result = new DataTable(tableName);
if (sql == "")
{
return(-1);
}
#region ADD THAM SỐ
List <fieldpara> li = new List <fieldpara>();
li.Add(new fieldpara("departmentcode", departmentcode, SqlDbType.VarChar, 0, 0));
li.Add(new fieldpara("year", beginDate.Year, SqlDbType.Int, 0, 0));
li.Add(new fieldpara("beginDate", beginDate, SqlDbType.DateTime, 0, 0));
li.Add(new fieldpara("endDate", endDate, SqlDbType.DateTime, 0, 0));
#endregion
int ret = getByQuery(ref ds, tableName, sql, li);
return(ret);
}
public int getDepartmentPapers(ref DataSet ds, string tableName,
string departmentcode, int GetLevel,
ReportType typeReport, SubLevelTypeReport sublevel,
DateTime beginDate, DateTime endDate)
{
#region SQL truy vấn
string sql = "";
sql = @"with FullName_cte as
(
select
charindex(' ', s.name) -1 as FirstNameEnd,
len(s.name) - charindex(' ',reverse(s.name)) +2 as LastNameStart,
s.code, s.name, departmentcode, researchdepartmentcode,
academiclevelcode, academictitlecode, degreecode
from staff s LEFT join staffstatus on staffstatus.code = s.staffstatus
left join academiclevel on academiclevel.code = s.academiclevelcode
left join academictitle on academictitle.code = s.academictitlecode
left join degree on degree.code = s.degreecode
where isnull(staffstatus.isstaff,0) <= 1 and isnull(academiclevel.comparelevel,0) < 10
and s.researchstatus > 0
)
select distinct upper(paper.name) tenbaibao, paper.authorprint author, papertype.codeview papertype,
isnull(B.codeview, '') academictitle, isnull(C.codeview,'') academiclevel, isnull(D.codeview,'') degree,
isnull(B.comparelevel, '99') academictitlecomparelevel, isnull(D.comparelevel,'99') degreecomparelevel,
department.code departmentcode, department.parentcode facultycode,
isnull(paper.ISBN,'') ISBN, isnull(paper.expertgroupcode, '') expertgroupcode,
isnull(paper.mark, 0) mark, isnull(staffpaper.researchsupport,0) researchsupport,
(select name from department tmp where tmp.code = department.parentcode) facultyname,
case
when paper.authorprint <> '' then paper.authorprint + ', '
else ''
end +
case
when paper.name <> '' then paper.name + ', '
else ''
end +
case
when paper.journalname <> '' then paper.journalname + ', '
else ''
end +
case
when paper.journalnumber <> '' then paper.journalnumber + ', '
else ''
end +
case
when paper.page <> '' then paper.page + ', '
else ''
end +
isnull(paper.publishdateshow,'') name,
papertype.name level, paperrole.name role, paper.numberauthor numberauthors,
department.name department, T.name fullname, T.code,
substring(T.name,0,FirstNameEnd+1) as FirstName,
case
when FirstNameEnd+2 = LastNameStart then ''
else substring(T.name, FirstNameEnd+2, LastNameStart-FirstNameEnd-2)
end as MiddleName,
substring(T.name, LastNameStart, len(T.name)) as LastName, publishdateshow publishdate
from staffpaper
inner join paper on paper.code = staffpaper.papercode
inner join papertype on papertype.code = paper.papertypecode
inner join paperrole on paperrole.code = staffpaper.rolecode
inner join FullName_cte T on T.code = staffpaper.staffcode
inner join department on department.code = T.departmentcode
left JOIN academictitle B ON T.academictitlecode=B.code
LEFT JOIN academiclevel C ON T.academiclevelcode=C.code
LEFT JOIN degree D ON T.degreecode = D.code
where paper.schoolyear = @year ";
//Cấp bộ môn
if (GetLevel == 0)
{
sql += @" and T.departmentcode = @departmentcode ";
}
else
if (GetLevel == 1) //Cấp khoa
{
sql += @" and T.departmentcode in (select code from department where parentcode = @departmentcode) ";
}
sql += @" order by publishdate desc, department.name, fullname, upper(paper.name) ";
#endregion
DataTable result = new DataTable(tableName);
if (sql == "")
{
return(-1);
}
#region ADD THAM SỐ
List <fieldpara> li = new List <fieldpara>();
li.Add(new fieldpara("departmentcode", departmentcode, SqlDbType.VarChar, 0, 0));
li.Add(new fieldpara("year", beginDate.Year, SqlDbType.Int, 0, 0));
li.Add(new fieldpara("beginDate", beginDate, SqlDbType.DateTime, 0, 0));
li.Add(new fieldpara("endDate", endDate, SqlDbType.DateTime, 0, 0));
#endregion
int ret = getByQuery(ref ds, tableName, sql, li);
return(ret);
}
