Exemple #1
0
        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);
        }