public dynamic SearchNewPrj(string div, string dep, string grade, int start = 0, int end = 10)
{
var condition = string.Empty;
if (!string.IsNullOrWhiteSpace(div))
{
condition += "AND DIVISION='" + div + "' ";
}
if (!string.IsNullOrWhiteSpace(dep))
{
condition += "AND DEPARTMENT='" + dep + "' ";
}
if (!string.IsNullOrWhiteSpace(grade))
{
condition += "AND PRJECT_GRADE='" + grade + "' ";
}
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by IDEA_ID desc) AS ROWNUM, P.IDEA_ID,P.EMP_ID,P.IDEA_TITLE,P.NAME,
P.INS_DT,
(SELECT COUNT(1) FROM PRJ_REPLY AS PR WHERE PR.IDEA_ID=P.IDEA_ID) AS REP,
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS L
,(SELECT TOP 1 TARGET_VALUE FROM KPI AS K WHERE K.IDEA_ID=P.IDEA_ID ORDER BY PRJ_MONTH DESC) AS FINAL
FROM PROJECT AS P INNER JOIN EMPLOYEE AS E ON E.EMP_ID=P.EMP_ID WHERE 1=1 condition) as u
WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
if (!string.IsNullOrEmpty(condition))
{
sql = sql.Replace("condition", condition);
}
return(DBManager <PROJECT> .ExecuteDynamic(sql, new { start = start, end = end }));
}
public virtual dynamic SelectPaging(int start = 0, int end = 10)
{
var sql = string.Format(@"
SELECT * FROM(SELECT ROW_NUMBER() OVER (order by IDEA_ID desc) AS ROWNUM, IDEA_ID,IDEA_TITLE,NAME,EMP_ID,
INS_DT,
(SELECT COUNT(1) FROM PRJ_REPLY AS PR WHERE PR.IDEA_ID=P.IDEA_ID) AS REP,
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS L
FROM PROJECT AS P ) as u
WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <PROJECT> .ExecuteDynamic(sql, new { start = start, end = end }));
}
public dynamic GetMainProject(int start = 0, int end = 10)
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by IDEA_ID desc) AS ROWNUM, P.IDEA_ID,P.EMP_ID,P.IDEA_TITLE,P.PRJECT_GRADE,
P.KPI_NAME,P.KPI_UNIT,E.DIVISION, E.DEPARTMENT,
(SELECT COUNT(1) FROM PRJ_REPLY AS PR WHERE PR.IDEA_ID=P.IDEA_ID) AS REP,
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS L,
(SELECT TOP 1 TARGET_VALUE FROM KPI AS K WHERE K.IDEA_ID=P.IDEA_ID ORDER BY PRJ_MONTH DESC) AS FINAL
FROM PROJECT AS P INNER JOIN EMPLOYEE AS E ON E.EMP_ID=P.EMP_ID WHERE PRJECT_GRADE='S' OR PRJECT_GRADE='A') as u
WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <PROJECT> .ExecuteDynamic(sql, new { start = start, end = end }));
}
public virtual dynamic SelectPaging(int start = 0, int end = 10, string div = "", string dep = "")
{
var sql = string.Format(@"
SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id desc) AS ROWNUM, I.*, E.EMP_NAME,E.DIVISION,E.DEPARTMENT,
(SELECT COUNT(1) FROM IDEA_REPLY WHERE IDEA_ID=I.ID) AS REP,
(SELECT COUNT(1) FROM IDEA_LIKE WHERE IDEA_ID=I.ID) AS L
FROM IDEA AS I INNER JOIN EMPLOYEE AS E ON I.EMP_ID=E.EMP_ID
WHERE NOT EXISTS(SELECT IDEA_ID FROM PROJECT WHERE IDEA_ID=I.ID) AND(@div='' OR DIVISION LIKE '%'+@div+'%') AND(@dep='' OR DEPARTMENT LIKE '%'+@dep+'%'))
as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <IDEA> .ExecuteDynamic(sql, new
{
start = start,
end = end,
div = div,
dep = dep
}));
}
public dynamic GetReplySummaryExport(string from = "", string to = "")
{
var sql = string.Format(@"
SELECT * FROM( SELECT
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS numLike,
(SELECT COUNT(1) FROM PRJ_REPLY AS R WHERE R.IDEA_ID=P.IDEA_ID) AS REP,
P.IDEA_TITLE,E.DEPARTMENT,E.DIVISION,E.EMP_NAME,PR.REP_EMP_NAME,PR.COMMENTS
FROM PROJECT AS P
LEFT JOIN PRJ_REPLY AS PR
ON P.IDEA_ID=PR.IDEA_ID
INNER JOIN EMPLOYEE AS E
ON PR.REP_EMP_ID=E.EMP_ID
WHERE (@FROM ='' OR P.INS_DT BETWEEN @FROM AND @TO)
) AS U ORDER BY numLike,REP DESC
");
return(DBManager <PRJ_REPLY> .ExecuteDynamic(sql, new { FROM = from, TO = to }));
}
public dynamic GetReplySummary(string from = "", string to = "", int start = 0, int end = 10)
{
var sql = string.Format(@"
SELECT * FROM(SELECT ROW_NUMBER() OVER (order by P.IDEA_ID) AS ROWNUM,
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS numLike,
(SELECT COUNT(1) FROM PRJ_REPLY AS R WHERE R.IDEA_ID=P.IDEA_ID) AS REP,
P.IDEA_TITLE,E.DEPARTMENT,E.DIVISION,PR.REP_EMP_NAME,PR.COMMENTS,E.EMP_NAME
FROM PROJECT AS P
LEFT JOIN PRJ_REPLY AS PR
ON P.IDEA_ID=PR.IDEA_ID
INNER JOIN EMPLOYEE AS E
ON PR.REP_EMP_ID=E.EMP_ID
WHERE (@FROM='' OR P.INS_DT BETWEEN @FROM AND @TO)
) AS U
WHERE RowNum >= @start AND RowNum < @end ORDER BY numLike,REP DESC
");
return(DBManager <PRJ_REPLY> .ExecuteDynamic(sql, new { FROM = from, TO = to, start = start, end = end }));
}
public dynamic GetProject(string div = "", string dept = "", string grade = "", int page = 0)
{
var start = (page - 1) * 5 + 1;
var end = start + 5 - 1;
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by IDEA_ID desc) AS ROWNUM, P.IDEA_ID,P.EMP_ID,E.EMP_NAME,E.DIVISION,E.DEPARTMENT,P.IDEA_TITLE,P.NAME,
P.PRJECT_GRADE,P.KPI_UNIT,P.KPI_NAME,P.INS_DT,
(SELECT COUNT(1) FROM PRJ_REPLY AS PR WHERE PR.IDEA_ID=P.IDEA_ID) AS REP,
(SELECT COUNT(1) FROM PRJ_LIKE AS L WHERE L.IDEA_ID=P.IDEA_ID) AS L
FROM PROJECT AS P INNER JOIN EMPLOYEE AS E ON E.EMP_ID=P.EMP_ID WHERE
(@div='' OR DIVISION LIKE '%'+@div+'%')
AND (@dept='' OR DEPARTMENT LIKE '%'+@dept+'%')
AND (@grade='' OR PRJECT_GRADE=@grade) ) as u
WHERE RowNum >= @start AND RowNum <= @end ORDER BY RowNum");
return(DBManager <dynamic> .ExecuteDynamic(sql, new
{
div = div,
dept = dept,
grade = grade,
start = start,
end = end
}));
}
public dynamic GetReply(string IDEA_ID)
{
var sql = "SELECT PR.*,E.DEPARTMENT,E.EMP_NAME FROM PRJ_REPLY AS PR INNER JOIN EMPLOYEE AS E ON PR.REP_EMP_ID=E.EMP_ID WHERE IDEA_ID=@IDEA_ID ORDER BY INS_DATE DESC";
return(DBManager <PRJ_REPLY> .ExecuteDynamic(sql, new { IDEA_ID = IDEA_ID }));
}
public dynamic GetReplyDetail(string ID)
{
var sql = "SELECT E.EMP_NAME FROM IDEA_REPLY AS R INNER JOIN EMPLOYEE AS E ON R.REP_EMP_ID=E.EMP_ID WHERE IDEA_ID=@ID";
return(DBManager <dynamic> .ExecuteDynamic(sql, new { ID = ID }));
}
public dynamic GetReplyByIdeaId(string ID)
{
var sql = "SELECT E.EMP_NAME,E.DEPARTMENT,R.COMMENTS,R.INS_DATE FROM IDEA_REPLY AS R INNER JOIN EMPLOYEE AS E ON R.REP_EMP_ID=E.EMP_ID WHERE IDEA_ID=@ID ORDER BY INS_DATE DESC";
return(DBManager <dynamic> .ExecuteDynamic(sql, new { ID = ID }));
}
