public async Task <List <ProcessForPanel> > ProcessList(SipUser CurrentUser, int PageSectionId)
{
string ProcessConditionSQLFrom = " SELECT Processes.ProcessId, ISNULL(ProcessFieldSubject.StringValue,'') As Subject, ProcessTypeLanguages.Name ProcessTypeName FROM Processes JOIN ProcessFields ProcessFieldSubject ON ProcessFieldSubject.ProcessId = Processes.ProcessId " +
" JOIN ProcessTypefields ProcessTypeFieldSubject ON ProcessTypeFieldSubject.ProcessTypefieldId = ProcessFieldSubject.ProcessTypefieldId " +
" JOIN ProcessTypes ON Processes.ProcessTypeId = ProcessTypes.ProcessTypeId JOIN ProcessTypeStages ON Processes.ProcessTypeStageId = ProcessTypeStages.ProcessTypeStageID ";
string ProcessConditionSQLWhere = " WHERE ProcessTypeFieldSubject.ProcessTypeFieldTypeId = 1 AND ProcessTypeStages.InToDo = 1 ";
string ProcessConditionSQLContains = "";
var ProcessConditions = await _processProvider.PanelCondition(PageSectionId);
foreach (var ProcessCondition in ProcessConditions)
{
switch (ProcessCondition.PageSectionProcessConditionTypeId)
{
case 1: // Template
ProcessConditionSQLWhere += " AND TemplateId = " + ProcessCondition.PageSectionProcessConditionInt;
break;
case 2: // Template group
ProcessConditionSQLFrom += " JOIN ProcessTypeGroups ON ProcessTypes.ProcessTypeGroupId = ProcessTypeGroups.ProcessTypeGroupId ";
ProcessConditionSQLWhere += " AND ProcessTypeGroups.TemplateGroupId = " + ProcessCondition.PageSectionProcessConditionInt;
break;
case 3: // My calendar
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldMyCalendar ON Processes.ProcessId = ProcessFieldMyCalendar.ProcessId JOIN ProcessTypeFields ProcessTypeFieldMyCalendar ON ProcessTypeFieldMyCalendar.ProcessTypeFieldId = ProcessFieldMyCalendar.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInPersonalCalendar = 1 AND ProcessFieldMyCalendar.StringValue = '" + CurrentUser.Id + "' AND ProcessTypeFieldMyCalendar.ProcessTypefieldTypeId = 12 ";
break;
case 4: // Personal calendar
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInPersonalCalendar = 1 ";
break;
case 5: // Organization calendar
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInOrganizationCalendar = 1 ";
break;
case 6: // Project calendar
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInProjectCalendar = 1 ";
break;
case 7: // Event calendar
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInEventCalendar = 1 ";
break;
case 8: // Is personal
ProcessConditionSQLWhere += " AND ProcessTypes.IsPersonal = 1 ";
break;
case 9: // Show new
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInNew = 1 ";
break;
case 10: // Show search
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInSearch = 1 ";
break;
case 11: // show report
ProcessConditionSQLWhere += " AND ProcessTypes.ShowInReport = 1 ";
break;
case 12: // Organization
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldOrganization ON Processes.ProcessId = ProcessFieldOrganization.ProcessId JOIN ProcessTypeFields ProcessTypeFieldOrganization ON ProcessTypeFieldOrganization.ProcessTypeFieldId = ProcessFieldOrganization.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldOrganization.IntValue = " + ProcessCondition.PageSectionProcessConditionInt + " AND (ProcessTypeFieldOrganization.ProcessTypefieldTypeId = 14 OR ProcessTypeFieldOrganization.ProcessTypefieldTypeId = 15 ) ";
break;
case 13: // Project
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldProject ON Processes.ProcessId = ProcessFieldProject.ProcessId JOIN ProcessTypeFields ProcessTypeFieldProject ON ProcessTypeFieldProject.ProcessTypeFieldId = ProcessFieldProject.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldProject.IntValue = " + ProcessCondition.PageSectionProcessConditionInt + "' AND (ProcessTypeFieldProject.ProcessTypefieldTypeId = 16 OR ProcessTypeFieldProject.ProcessTypefieldTypeId = 17) ";
break;
case 14: // creator is user
ProcessConditionSQLWhere += " AND Processes.CreatorId = '" + CurrentUser.Id + "' ";
break;
case 15: // role
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldRole ON Processes.ProcessId = ProcessFieldRole.ProcessId JOIN ProcessTypeFields ProcessTypeFieldRole ON ProcessTypeFieldRole.ProcessTypeFieldId = ProcessFieldRole.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldRole.stringValue = '" + ProcessCondition.PageSectionProcessConditionString + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 30 ";
break;
case 16: // securitylevel
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldSecurityLevel ON Processes.ProcessId = ProcessFieldSecurityLevel.ProcessId JOIN ProcessTypeFields ProcessTypeFieldSecurityLevel ON ProcessTypeFieldSecurityLevel.ProcessTypeFieldId = ProcessFieldSecurityLevel.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldSecurityLevel.IntValue <= '" + ProcessCondition.PageSectionProcessConditionInt + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 28 ";
break;
case 18: // Default Organization
ProcessConditionSQLWhere += " AND ProcessFieldOrganization.IntValue = @DefaultOrganization AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 14 ";
break;
case 19: // UserOrganization
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldUserOrganization ON Processes.ProcessId = ProcessFieldUserOrganization.ProcessId JOIN ProcessTypeFields ProcessTypeFieldUserOrganization ON ProcessTypeFieldUserOrganization.ProcessTypeFieldId = ProcessFieldUserOrganization.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldUserOrganization.IntValue IN (SELECT OrganizationId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId WHERE UserId = '" + CurrentUser.Id + "') AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 14 ";
break;
case 20: // Specific Organization
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldOrganizationRole ON Processes.ProcessId = ProcessFieldOrganizationRole.ProcessId JOIN ProcessTypeFields ProcessTypeFieldOrganizationRole ON ProcessTypeFieldOrganizationRole.ProcessTypeFieldId = ProcessFieldOrganizationRole.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldOrganizationRole.StringValue = '" + ProcessCondition.PageSectionProcessConditionString + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 30 ";
break;
case 21: // UserProject
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldUserProject ON Processes.ProcessId = ProcessFieldUserProject.ProcessId JOIN ProcessTypeFields ProcessTypeFieldUserProject ON ProcessTypeFieldUserProject.ProcessTypeFieldId = ProcessFieldUserProject.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldUserProject.IntValue IN (SELECT ProjectId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId WHERE UserId = '" + CurrentUser.Id + "') AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 16 ";
break;
case 22: // Specific project
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldProjectRole ON Processes.ProcessId = ProcessFieldProjectRole.ProcessId JOIN ProcessTypeFields ProcessTypeFieldProjectRole ON ProcessFieldProjectRole.ProcessTypeFieldId = ProcessTypeFieldProjectRole.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldProjectRole.StringValue = '" + ProcessCondition.PageSectionProcessConditionString + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 30 ";
break;
case 25: // Content
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldContent ON Processes.ProcessId = ProcessFieldContent.ProcessId JOIN ProcessTypeFields ProcessTypeFieldContent ON ProcessFieldContent.ProcessTypeFieldId = ProcessTypeFieldContent.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldContent.IntValue = " + ProcessCondition.PageSectionProcessConditionInt + " AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 24 ";
break;
case 26: // Content type
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldContentType ON Processes.ProcessId = ProcessFieldContentType.ProcessId JOIN ProcessTypeFields ProcessTypeFieldContentType ON ProcessFieldContentType.ProcessTypeFieldId = ProcessTypeFieldContentType.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldContentType.IntValue IN (SELECT ContentId FROM Contents WHERE ContentTypeID = " + ProcessCondition.PageSectionProcessConditionInt + ") AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 24 ";
break;
case 27: // User is user
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldUserIsUser ON Processes.ProcessId = ProcessFieldUserIsUser.ProcessId JOIN ProcessTypeFields ProcessTypeFieldUserIsUser ON ProcessFieldUserIsUser.ProcessTypeFieldId = ProcessTypeFieldUserIsUser.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldUserIsUser.StringValue = '" + ProcessCondition.PageSectionProcessConditionString + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 12 ";
break;
case 32: // From date
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldFromDate ON Processes.ProcessId = ProcessFieldFromDate.ProcessId JOIN ProcessTypeFields ProcessTypeFieldFromDate ON ProcessFieldFromDate.ProcessTypeFieldId = ProcessTypeFieldFromDate.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldFromDate.DateTimeValue >= '" + ProcessCondition.PageSectionProcessConditionDate + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 4 ";
//PETER TO DO the date time and date time range and date range
break;
case 33: // To date
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldToDate ON Processes.ProcessId = ProcessFieldToDate.ProcessId JOIN ProcessTypeFields ProcessTypeFieldToDate ON ProcessFieldToDate.ProcessTypeFieldId = ProcessTypeFieldToDate.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldToDate.DateTimeValue <= '" + ProcessCondition.PageSectionProcessConditionDate + "' AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 4 ";
break;
case 37: // Person
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldPerson ON Processes.ProcessId = ProcessFieldPerson.ProcessId JOIN ProcessTypeFields ProcessTypeFieldPerson ON ProcessFieldPerson.ProcessTypeFieldId = ProcessTypeFieldPerson.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldPerson.IntValue = " + ProcessCondition.PageSectionProcessConditionInt + " AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 36 ";
break;
case 38: // Content type group
ProcessConditionSQLFrom += " JOIN ProcessFields ProcessFieldContentTypeGroup ON Processes.ProcessId = ProcessFieldContentTypeGroup.ProcessId JOIN ProcessTypeFields ProcessTypeFieldContentTypeGroup ON ProcessFieldContentTypeGroup.ProcessTypeFieldId = ProcessTypeFieldContentTypeGroup.ProcessTypeFieldId ";
ProcessConditionSQLWhere += " AND ProcessFieldContentTypeGroup.IntValue IN (SELECT ContentId FROM Contents JOIN ContentTypes ON Contents.ContentTypeId = ContentTypes.ContentTypeId WHERE ContentTypeGroupID = " + ProcessCondition.PageSectionProcessConditionInt + ") AND ProcessTypeFieldUser.ProcessTypefieldTypeId = 24 ";
break;
}
}
var ProcessList = await _processProvider.Panel(CurrentUser.Id, ProcessConditionSQLFrom, ProcessConditionSQLWhere, ProcessConditionSQLContains);
return(ProcessList);
}