public virtual int GetCount(int main = 0)
{
var sql = "SELECT COUNT(1) AS CNT FROM PROJECT AS P WHERE PRJECT_GRADE='S' OR PRJECT_GRADE='A'"; //NOT EXISTS(SELECT IDEA_ID FROM PROJECT_PLAN AS PL WHERE P.IDEA_ID=PL.IDEA_ID AND COMPLETE_YN=1)
if (main == 1)
{
sql = sql.Replace("WHERE PRJECT_GRADE='S' OR PRJECT_GRADE='A'", "");
}
return((int)DBManager <PROJECT> .ExecuteScalar(sql));
}
public int GetReplySumCount(string from = "", string to = "")
{
var sql = string.Format(@"
SELECT count(*)
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)
");
return((int)DBManager <PRJ_REPLY> .ExecuteScalar(sql, new { FROM = from, TO = to }));
}
public virtual int GetCount(string div = "", string dep = "")
{
var sql = "SELECT COUNT(1) AS CNT FROM IDEA AS I INNER JOIN EMPLOYEE as E ON E.EMP_ID=I.EMP_ID WHERE NOT EXISTS(SELECT IDEA_ID FROM PROJECT WHERE IDEA_ID=ID) ";
if (string.IsNullOrWhiteSpace(div) && string.IsNullOrWhiteSpace(dep))
{
return((int)DBManager <IDEA> .ExecuteScalar(sql));
}
sql += "AND(@div='' OR DIVISION LIKE '%'+@div+'%') AND(@dep='' OR DEPARTMENT LIKE '%'+@dep+'%')";
return((int)DBManager <IDEA> .ExecuteScalar(sql, new
{
div = div,
dep = dep
}));
}
public int GetReplySumCount(string from = "", string to = "")
{
var sql = string.Format(@"
SELECT COUNT(*)
FROM IDEA AS P
LEFT JOIN IDEA_REPLY AS PR
ON P.ID=PR.IDEA_ID
INNER JOIN EMPLOYEE AS E
ON P.EMP_ID=E.EMP_ID
WHERE NOT EXISTS(SELECT IDEA_ID FROM PROJECT AS PJ WHERE PJ.IDEA_ID=P.ID ) AND (@FROM='' OR P.DATE BETWEEN @FROM AND @TO)
");
return((int)DBManager <PRJ_REPLY> .ExecuteScalar(sql, new { FROM = from, TO = to }));
}
public int GetSearchCount(string div, string dep, string grade)
{
var condition = string.Empty;
if (!string.IsNullOrWhiteSpace(div))
{
condition += "AND DIVISION LIKE '%" + div + "%' ";
}
if (!string.IsNullOrWhiteSpace(dep))
{
condition += "AND DEPARTMENT LIKE '%" + dep + "%' ";
}
if (!string.IsNullOrWhiteSpace(grade))
{
condition += "AND PRJECT_GRADE LIKE '%" + grade + "%' ";
}
var sql = string.Format(@"SELECT COUNT(1) AS CNT
FROM PROJECT AS P INNER JOIN EMPLOYEE AS E ON E.EMP_ID=P.EMP_ID WHERE 1=1 {0}", condition);
return((int)DBManager <PROJECT> .ExecuteScalar(sql));
}
public int GetReplyCount(string IDEA_ID)
{
var sql = "SELECT COUNT(1) FROM PRJ_REPLY WHERE IDEA_ID=@IDEA_ID";
return((int)DBManager <PRJ_REPLY> .ExecuteScalar(sql, new { IDEA_ID = IDEA_ID }));
}
public virtual int GetCount()
{
var sql = "SELECT COUNT(1) AS CNT FROM KPI;";
return((int)DBManager <KPI> .ExecuteScalar(sql));
}
public int GetSearchCount(string Key)
{
var sql = "SELECT COUNT(1) AS CNT FROM EMPLOYEE WHERE EMP_ID LIKE @KEY +'%' OR EMP_NAME LIKE '%' +@KEY+ '%';";
return((int)DBManager <EMPLOYEE> .ExecuteScalar(sql, new { KEY = Key }));
}
public int CountLike(string IDEA_ID)
{
var sql = "SELECT COUNT(1) AS CNT FROM PRJ_LIKE WHERE IDEA_ID=@IDEA_ID";
return((int)DBManager <PRJ_LIKE> .ExecuteScalar(sql, new{ IDEA_ID = IDEA_ID }));
}
public virtual int GetCount()
{
var sql = "SELECT COUNT(1) AS CNT FROM PROJECT_PLAN;";
return((int)DBManager <PROJECT_PLAN> .ExecuteScalar(sql));
}
public virtual int GetCount()
{
var sql = "SELECT COUNT(1) AS CNT FROM IDEA_REPLY;";
return((int)DBManager <IDEA_REPLY> .ExecuteScalar(sql));
}
