public HttpResponseMessage getTeams([FromUri] TicketSettingCriteria criteria)
{
var list = ticketService.getTeams(criteria);
var re = new
{
data = list,
totalCount = list.Count(),
};
return(Request.CreateResponse(HttpStatusCode.OK, re));
}
public IEnumerable <TicketItemDto> getTicketItems(TicketSettingCriteria criteria)
{
string sqlString = @"SELECT
i.[ID] id
,i.[SUB_CATE_ID] subCateId
,i.[ITEM_NAME] itemName
,C.CATE_NAME+' -> '+sc.SUB_CATE_NAME+' -> '+ i.[ITEM_NAME] itemDisplayName
,i.[TEAM_ID] teamId
,i.[SLA_ID] slaId
,i.[DESCRIPTION] description
,i.[CREATED_DATE] createdDate
,i.[MODIFIED_DATE] modifiedDate
,sc.SUB_CATE_NAME subCateName
,sc.[DESCRIPTION] subCateDescription
,t.TEAM_NAME teamName
,t.DESCRIPTION teamDescription
,i.STATUS status
,L.ID statusId
FROM TICKET.ITEM i
INNER JOIN TICKET.SUB_CATEGORY sc ON i.SUB_CATE_ID=sc.ID
INNER JOIN TICKET.CATEGORY C ON sc.CATE_ID = c.ID
INNER JOIN TICKET.TEAM t ON i.TEAM_ID = t.ID
INNER JOIN TICKET.LOOKUP L ON L.ACTIVE = 1 AND i.STATUS = L.LOOKUP_CODE AND L.LOOKUP_KEY = 'ITEM_STATUS'
WHERE (@query IS NULL OR (
ISNULL(i.ITEM_NAME, '')+ ' '+ ISNULL(i.[DESCRIPTION],'')
+ ' '+ ISNULL(sc.SUB_CATE_NAME,'')+ ' '+ ISNULL(sc.[DESCRIPTION],'')
+ ' '+ ISNULL(C.CATE_NAME,'')+ ' '+ ISNULL(C.[DESCRIPTION],'')
+ ' '+ ISNULL(t.TEAM_NAME,'')+ ' '+ ISNULL(t.[DESCRIPTION],'')
) LIKE @query)
AND (@status IS NULL OR @status = 'ALL' OR LOWER(i.STATUS) = LOWER(@status))
ORDER BY i.[ITEM_NAME]"
;
object queryParam = "%" + criteria.query + "%";
if (criteria.query == null)
{
queryParam = DBNull.Value;
}
object statusParam = criteria.status;
if (criteria.status == null)
{
statusParam = DBNull.Value;
}
return(SqlQuery <TicketItemDto>(sqlString, new object[] { new SqlParameter("query", queryParam), new SqlParameter("status", statusParam) }).ToList());
}
public IEnumerable <TicketSubCategoryDto> getSubCategories(TicketSettingCriteria criteria)
{
string sqlString = @"SELECT
SC.[ID] id
,SC.[CATE_ID] cateId
,sc.[SUB_CATE_NAME] subCateName
,sc.[DESCRIPTION] description
,sc.[CREATED_DATE] createdDate
,sc.[MODIFIED_DATE] modifiedDate
,c.CATE_NAME cateName
,c.[DESCRIPTION] cateDescription
,SC.STATUS status
,L.ID statusId
FROM [TICKET].[SUB_CATEGORY] SC
INNER JOIN TICKET.CATEGORY C ON SC.CATE_ID=C.ID
INNER JOIN TICKET.LOOKUP L ON L.ACTIVE = 1 AND sc.STATUS = L.LOOKUP_CODE AND L.LOOKUP_KEY = 'SUBCATEGORY_STATUS'
WHERE
(@query IS NULL OR (ISNULL(SC.SUB_CATE_NAME, '')+ ' '+ ISNULL(SC.[DESCRIPTION],'')+ ' '+ ISNULL(C.CATE_NAME,'')+ ' '+ ISNULL(C.[DESCRIPTION],'')) LIKE @query)
AND (@status IS NULL OR @status = 'ALL' OR LOWER(SC.STATUS) = LOWER(@status))
ORDER BY sc.[SUB_CATE_NAME] "
;
object queryParam = "%" + criteria.query + "%";
if (criteria.query == null)
{
queryParam = DBNull.Value;
}
object statusParam = criteria.status;
if (criteria.status == null)
{
statusParam = DBNull.Value;
}
return(SqlQuery <TicketSubCategoryDto>(sqlString, new object[] { new SqlParameter("query", queryParam), new SqlParameter("status", statusParam) }).ToList());
}
public IEnumerable <TicketTeamDto> getTeams(TicketSettingCriteria criteria)
{
string sqlString = @"SELECT
T.ID id
,T.TEAM_NAME teamName
,T.ALERT_ALL_MEMBERS alertAllMembers
,T.ALERT_ASSIGNED_AGENT alertAssignedAgent
,T.DIRECTORY_LISTING directoryListing
,T.STATUS status
,L.ID statusId
,T.DESCRIPTION description
,T.[CREATED_DATE] createdDate
,T.[MODIFIED_DATE] modifiedDate
FROM TICKET.TEAM T
INNER JOIN TICKET.LOOKUP L ON L.ACTIVE = 1 AND T.STATUS = L.LOOKUP_CODE AND L.LOOKUP_KEY = 'TEAM_STATUS'
WHERE
(@query IS NULL OR (ISNULL(T.TEAM_NAME, '')+ ' '+ ISNULL(T.[DESCRIPTION],'')+ ' '+ ISNULL(L.LOOKUP_NAME,'')) LIKE @query)
AND (@status IS NULL OR @status = 'ALL' OR LOWER(T.STATUS) = LOWER(@status))
ORDER BY T.TEAM_NAME "
;
object queryParam = "%" + criteria.query + "%";
if (criteria.query == null)
{
queryParam = DBNull.Value;
}
object statusParam = criteria.status;
if (criteria.status == null)
{
statusParam = DBNull.Value;
}
return(SqlQuery <TicketTeamDto>(sqlString, new object[] { new SqlParameter("query", queryParam), new SqlParameter("status", statusParam) }).ToList());
}
public IEnumerable <TicketDepartmentDto> getDepartments(TicketSettingCriteria criteria)
{
string sqlString = @"SELECT
D.ID id
,D.DEPT_NAME deptName
,D.AUTOMATION_EMIAL automationEmail
,D.DESCRIPTION description
,D.[CREATED_DATE] createdDate
,D.[MODIFIED_DATE] modifiedDate
,D.[DEFAULT_ITEM_ID] defaultItemId
,D.STATUS status
,L.ID statusId
FROM TICKET.DEPARTMENT D
INNER JOIN TICKET.LOOKUP L ON L.ACTIVE = 1 AND D.STATUS = L.LOOKUP_CODE AND L.LOOKUP_KEY = 'DEPARTMENT_STATUS'
WHERE
(@query IS NULL OR (ISNULL(d.DEPT_NAME, '')+ ' '+ ISNULL(D.[DESCRIPTION],'')+ ' '+ ISNULL(D.AUTOMATION_EMIAL,'')) LIKE @query)
AND (@status IS NULL OR @status = 'ALL' OR LOWER(D.STATUS) = LOWER(@status))
ORDER BY D.DEPT_NAME "
;
object queryParam = "%" + criteria.query + "%";
if (criteria.query == null)
{
queryParam = DBNull.Value;
}
object statusParam = criteria.status;
if (criteria.status == null)
{
statusParam = DBNull.Value;
}
return(SqlQuery <TicketDepartmentDto>(sqlString, new object[] { new SqlParameter("query", queryParam), new SqlParameter("status", statusParam) }).ToList());
}
public IEnumerable <TicketItemDto> getTicketItems(TicketSettingCriteria criteria)
{
return(ticketItemRepo.getTicketItems(criteria));
}
public IEnumerable <TicketDepartmentDto> getDepartments(TicketSettingCriteria criteria)
{
return(deptRepo.getDepartments(criteria));
}
public IEnumerable <TicketCategoryDto> getCategories(TicketSettingCriteria criteria)
{
return(categoryRepo.getCategories(criteria));
}
public IEnumerable <TicketTeamDto> getTeams(TicketSettingCriteria criteria)
{
return(teamRepo.getTeams(criteria));
}
