Как сделать таск-трекер под себя на low-code конструкторе12.07.2022 11:31

SELECT
    /* User view type */
		@type = 'board',
 		/* User view title */                                      
    @title = 'Задачи',   
    subject @{
				/* Material design icon */
        icon = 'subject' 
    },
    status @{
				/* Group entries by this field */
        board_group = true,
				/* Do not display status field on the card */ 
        visible = false     
    },
    start_date @{
        icon = 'date_range'
    },
    due_date @{
        icon = 'flag',
        cell_variant = CASE 
            WHEN due_date < $$transaction_time 
            THEN 'warning' 
        END
    },
    priority @{
        icon = 'priority_high'
    },
    responsible_contact @{
        icon = 'person'
    },
    "order" @{
				/* Use the value from the "order" field 
        as a sorting number for all entries */
        board_order = true,         
        visible = false
    }
FROM 
    pm.tasks

{   /* Arguments of the user view */ 
    $responsible array(reference(base.people)) null @{
        caption = 'Ответственный',
        /* Available values in popup will be restricted 
        with the results of the specified user view */
        options_view = &base.ref_base_people_view    
    },
    $status array(enum('backlog', 'new', 'in_progress', 'done')) null @{
        caption = 'Статус',
        text = array mapping
           WHEN 'backlog' THEN 'Идеи'
           WHEN 'new' THEN 'Новое'
           WHEN 'in_progress' THEN 'В работе'
           WHEN 'done' THEN 'Завершено'
       END,
    },
    $priority array(enum('low', 'medium', 'high', 'urgent')) null @{
        caption = 'Приоритет',
        text = array mapping
           WHEN 'low' THEN 'Низкий'
           WHEN 'medium' THEN 'Средний'
           WHEN 'high' THEN 'Высокий'
           WHEN 'urgent' THEN 'Критичный'
       END    
    },
    $is_archived bool null @{
        caption = 'Архив'
    },
    /* другие аргументы */
}:
SELECT /* ... */
 FROM pm.tasks
WHERE ($responsible IS NULL 
       OR responsible_contact = ANY($responsible))
	AND ($status IS NULL 
       OR status = ANY($status))
  AND ($priority IS NULL 
       OR priority = ANY($priority))
  AND ($is_archived IS NULL 
       OR is_archived = $is_archived)
		

@buttons = [
   {
   			/* Caption of the button */
				caption: 'Статус',
        /* Material design icon code */
        icon: 'filter_alt',
        /* Show button at the top right corner on PCs */
        display: 'desktop',
        buttons: [
        		{
            		caption: 'Просрочено',
                icon: 'flag',
                /* Reference to the user view */
                ref: &pm.tasks_board,
                /* New values for arguments */
                args: {
                		status: ['backlog', 'new', 'in_progress'],
                    due_date_to: $$transaction_time,
                    is_archived: false,
                    responsible: $responsible,
                    parent_task: $parent_task
                },
                target: 'top'
            },
            {
            		caption: 'Не завершено',
                icon: 'notifications',
                ref: &pm.tasks_board,
                args: {
                		status: ['new', 'in_progress'],
                    is_archived: false,
                    responsible: $responsible,
                    parent_task: $parent_task
                },
                target: 'top'
            },
            {
            		caption: 'Все',
                icon: 'filter_list_alt',
                ref: &pm.tasks_board,
                args: {
                		is_archived: false,
                    responsible: $responsible,
                    parent_task: $parent_task
                },
                target: 'top'
            }
        ]
    } 
]

/* Reference to the user view opened by clicking the "Open entry in modal" button */
@row_link = &pm.task_form, 
/* Set the user view used for creating new entries */
@card_create_view = {
		/* Reference to the user view */
		ref: &pm.task_form,
		/* Default values for the entry being created */
		default_values: {                             
				responsible_contact: $responsible,
				parent_task: $parent_task
		}
}

{   
    /* Arguments of the user view */ 
    $responsible array(reference(base.people)) null @{
    		caption = 'Ответственный',
        /* Available values in popup will be restricted 
        with the results of the specified user view */
        options_view = &base.ref_base_people_view    
    },
    $status array(enum('backlog', 'new', 'in_progress', 'done')) null @{
        caption = 'Статус',
        text = array mapping
           WHEN 'backlog' THEN 'Идеи'
           WHEN 'new' THEN 'Новое'
           WHEN 'in_progress' THEN 'В работе'
           WHEN 'done' THEN 'Завершено'
       END
    },
    $priority array(enum('low', 'medium', 'high', 'urgent')) null @{
        caption = 'Приоритет',
        text = array mapping
           WHEN 'low' THEN 'Низкий'
           WHEN 'medium' THEN 'Средний'
           WHEN 'high' THEN 'Высокий'
           WHEN 'urgent' THEN 'Критичный'
       END
    },
    $parent_task reference(pm.tasks) null @{
        caption = 'Надзадача'
    },
    $start_date_from datetime null @{
        caption = 'Дата начала с'
    },
    $start_date_to datetime null @{
        caption = 'Дата начала до'
    },
    $due_date_from datetime null @{
        caption = 'Срок с'
    },
    $due_date_to datetime null @{
        caption = 'Срок до'
    },
    $is_archived bool null @{
        caption = 'Архив'
    }
}:
SELECT
    /* User view type. See also: https://wiki.ozma.io/ru/docs/funapp/board */    
    @type = 'board', 
    /* User view title */
    @title = 'Задачи',
    /* "Filters" panel is hidden by default */
    @show_argument_editor = false,  
    /* "Filters" button will be showed on top of the board */
    @show_argument_button = true,   
    /* Reference to the user view opened by clicking the "Open entry in modal" button */
    @row_link = &pm.task_form,  
    /* Set the user view used for creating new entries */
    @card_create_view = { 
        /* Reference to the user view */
        ref: &pm.task_form,
        /* Default values for the entry being created */
        default_values: {                              
            responsible_contact: $responsible,
            parent_task: $parent_task
        }
    },
    /* Specifications of the interface buttons */
    @buttons = [ /* ... */ ],

    subject @{
        /* Material design icon */
        icon = 'subject' 
    },
    status @{
        /* Group entries by this field */
        board_group = true,   
        /* Do not display status field on the card */               
        visible = false,  
    },
    start_date @{
        icon = 'date_range',
    },
    due_date @{
        icon = 'flag'
    },
    priority as priority_raw @{
        /* Display the icon specified in the default attributes for the "priority" field */
       icon = priority.@icon,
    },
    responsible_contact @{
        icon = 'person'
    },
    parent_task @{
        icon = 'account_tree'
    },
    "order" @{
         /* Use the value from the "order" field as a sorting number for all entries */
        board_order = true,  
        visible = false,
    },
     FROM pm.tasks
    WHERE ($is_archived IS NULL OR is_archived = $is_archived)
      AND ($responsible IS NULL OR responsible_contact = ANY($responsible))
      AND (($parent_task IS NULL AND parent_task IS NULL) OR parent_task = $parent_task)
      AND ($status IS NULL OR status = ANY($status))
      AND ($priority IS NULL OR priority = ANY($priority))
      AND ($start_date_to IS NULL OR start_date <= $start_date_to)
      AND ($start_date_from IS NULL OR start_date >= $start_date_from)
      AND ($due_date_to IS NULL OR due_date <= $due_date_to)
      AND ($due_date_from IS NULL OR due_date <= $due_date_to)
 ORDER BY "order",
          is_archived,
          completed_datetime DESC NULLS FIRST,
          due_date NULLS LAST,
          id

{
    $id reference(pm.tasks)
}:
SELECT 
    /* User view type */
    @type = 'form',
    /* User view title */
    @title = $id=>__main,
    /* Form block sizes. This form has three blocks - two first row blocks with the ratio of 7 to 5, and the third block occupying the second row */                      
    @block_sizes = array[
        7, 5,
        12
    ],
    @buttons = [
        {
            caption: 'Архивировать запись',
            /* Show button at the top right corner on PCs */
            display: 'desktop',
            /* Show button only if the record is not archived */
            visible: NOT $id=>is_archived,
            /* Set material design icon */
            icon: 'archive',
            /* Color variant for the button */
            variant: 'outline-danger',
            action: { 
                schema: 'user', 
                name: 'archive_record'
            },
            args: { 
                entity: { 
                    schema: 'pm', 
                    name: 'tasks'
                }, 
                is_archived: true, 
                id: $id 
            }
        },
        {
            caption: 'Разархивировать запись',
            display: 'desktop',
            visible: $id=>is_archived,
            icon: 'archive',
            variant: 'outline-success',
            action: { 
                schema: 'user', 
                name: 'archive_record'
            },
            args: { 
                entity: { 
                    schema: 'pm', 
                    name: 'tasks'
                }, 
                is_archived: false, 
                id: $id 
            }
        }
    ],
    subject @{
        /* Display input control in the first block */ 
        form_block = 0
    },
    description @{
        form_block = 0,
        /* Use multiline WYSIWYG editor */
        text_type = 'wysiwyg',
        /* Set control height to 400 px */
        control_height = 400
    },
    parent_task @{
        form_block = 0,
    },
    status @{
        form_block = 1
    },
    responsible_contact @{
        form_block = 1
    },
    priority @{
        form_block = 1
    },
    start_date @{
        form_block = 1,
        default_value = $$transaction_time
    },
    due_date @{
        form_block = 1
    },
    /* Arrange nested user views on the form */
    {
        /* Reference to the nested user view */
        ref: &pm.tasks_board,
        /* Arguments that will be passed to the nested user view */                             
        args: {
            parent_task: $id
        }
    } as subtasks @{
        /* Display user view control on the form  */
        control = 'user_view',
        /* Display user View control in the first block */
        form_block = 4,
        /* Caption for the nested User View. User view @title will be replaced with this caption */
        caption = 'Подзадачи'
    },
    {
        ref: &pm.notes_for_task_timeline,
        args: {
            id: $id
        }
    } as notes @{
        control = 'user_view',
        form_block = 1,
        caption = 'Комментарии'
    }
FROM 
    pm.tasks 
WHERE 
    id = $id 
FOR INSERT INTO 
    pm.tasks

{     
    /* Arguments of the user view */
    $is_archived bool null @{
        caption = 'Архив'
    },
    $responsible array(reference(base.people)) null @{
        caption = 'Ответственный',
        /* Available values in popup will be restricted with the results of the specified user view */
        options_view = &base.ref_base_people_view 
    },
    $parent_task reference(pm.tasks) null @{
        caption = 'Надзадача'
    },
    $status array(enum('backlog', 'new', 'in_progress', 'done')) null @{
        caption = 'Статус',
        text = array mapping
            WHEN 'backlog' THEN 'Идеи'
            WHEN 'new' THEN 'Новое'
            WHEN 'in_progress' THEN 'В работе'
            WHEN 'done' THEN 'Завершено'
        END,
    },
    $priority array(enum('low', 'medium', 'high', 'urgent')) null @{
        caption = 'Приоритет',
        text = array mapping
            WHEN 'low' THEN 'Низкий'
            WHEN 'medium' THEN 'Средний'
            WHEN 'high' THEN 'Высокий'
            WHEN 'urgent' THEN 'Критичный'
        END
    },
    $start_date_from datetime null @{
        caption = 'Дата начала с'
    },
    $start_date_to datetime null @{
        caption = 'Дата начала до'
    },
    $due_date_from datetime null @{
        caption = 'Срок с'
    },
    $due_date_to datetime null @{
        caption = 'Срок до'
    }
}:
SELECT
    /* User view type. See also: https://wiki.ozma.io/ru/docs/funapp/table */  
    /* This table can be displayed as a tree is case of all filters are null. More info about tree view: https://wiki.ozma.io/ru/docs/funapp/tree */ 
    @type = 'table',    
    /* User view title */                                    
    @title = 'Задачи',
    /* "Filters" panel is hidden by default */
    @show_argument_editor = false,  
    /* "Filters" button will be showed on the top of the board */                     
    @show_argument_button = true,
    /* Set the user view used for creating new entries */
    @create_link = &pm.task_form,
    /* Reference to the user view opened by clicking the "Open entry in modal" button */
    @row_link = &pm.task_form,
    /* Disable ability to create new child record if parent task is not null and it was archived */
    @soft_disabled = $parent_task=>is_archived,  
    /* Set pagination options */
    @lazy_load = {                                          
        pagination: { 
            /* Show 25 rows per page */
            per_page: 25
        }
    },
    /*  Specifications of the interface buttons */                
    @buttons = [                                        
        {
            /* Caption of the button */
            caption: 'Ответственный',
            /* Material design icon code */
            icon: 'people', 
             /* Show button at the top right corner on PCs */                   
            display: 'desktop',
            buttons: [
                {
                    caption: 'Я',
                    ref: &pm.tasks_table,
                    icon: 'person',
                    /* Apply a "light gray" color variant to the button if there is no person associated with the current user */
                    /* $$user_id returns ID of the current user */
                    variant: CASE WHEN (SELECT COUNT(1) FROM base.people WHERE user = $$user_id) = 0 THEN 'light' END, 
                    args: {
                        responsible: (
                            SELECT array_agg(id) 
                            FROM base.people 
                            WHERE user = $$user_id
                        ),
                        status: $status,
                        start_date_to: $start_date_to,
                        start_date_from: $start_date_from,
                        due_date_to: $due_date_to,
                        due_date_from: $due_date_from,
                        is_archived: $is_archived,
                        parent_task: $parent_task,
                        priority: $priority
                    },
                    /* Open link full screen */
                    target: 'top'
                },
                {
                    caption: 'Все',
                    ref: &pm.tasks_table,
                    icon: 'people',
                    args: {
                        responsible: null,
                        status: $status,
                        start_date_to: $start_date_to,
                        start_date_from: $start_date_from,
                        due_date_to: $due_date_to,
                        due_date_from: $due_date_from,
                        is_archived: $is_archived,
                        parent_task: $parent_task,
                        priority: $priority
                    },
                    target: 'top'
                }
            ]
        },
        {
            caption: 'Статус',
            icon: 'filter_alt',
            display: 'desktop',
            buttons: [
                {
                    caption: 'Просрочено',
                    icon: 'flag',
                    ref: &pm.tasks_table,
                    args: {
                        status: ['backlog', 'new', 'in_progress'],
                        due_date_to: $$transaction_time,
                        is_archived: false,
                        responsible: $responsible,
                        parent_task: $parent_task
                    },
                    target: 'top'
                },
                {
                    caption: 'Не завершено',
                    icon: 'notifications',
                    ref: &pm.tasks_table,
                    args: {
                        status: ['new', 'in_progress'],
                        is_archived: false,
                        responsible: $responsible,
                        parent_task: $parent_task
                    },
                    target: 'top'
                },
                {
                    caption: 'Завершено',
                    icon: 'done_all',
                    ref: &pm.tasks_table,
                    args: {
                        status: ['done'],
                        is_archived: false,
                        responsible: $responsible,
                        parent_task: $parent_task
                    },
                    target: 'top'
                },
                {
                    caption: 'Все',
                    icon: 'filter_list_alt',
                    ref: &pm.tasks_table,
                    args: {
                        is_archived: false,
                        responsible: $responsible,
                        parent_task: $parent_task
                    },
                    target: 'top'
                }, 
                {
                    caption: 'Архив',
                    icon: 'delete',
                    ref: &pm.tasks_table,
                    args: {
                        is_archived: true,
                        responsible: $responsible,
                        parent_task: $parent_task
                    },
                    target: 'top'
                }
            ]
        },
        {   
            /* Open board with tasks instead of tasks table */   
            caption: 'Доска',  
            tooltip: 'Отображать задачи в виде канбан-доски',
            icon: 'sticky_note_2',
            variant: 'dark', 
            display: 'desktop',
            ref: &pm.tasks_board,  
            /* Use filters from tasks table for tasks board */
            args: {
                is_archived: $is_archived,
                parent_task: $parent_task,
                status: $status,
                priority: $priority,
                start_date_from: $start_date_from,
                start_date_to: $start_date_to,
                due_date_from: $due_date_from,
                due_date_to: $due_date_to,
                responsible: $responsible
            },   
            target: 'top'
        },
    ],

    subject @{
        /* Set column width to 350 px */
        column_width = 350,  
    },
    status @{
        column_width = 100
    },
    priority @{
        column_width = 100
    },
    start_date @{
        column_width = 100,
        default_value = $$transaction_time /* $$transaction_time returns current timestamp */
    },
    due_date @{
        column_width = 100
    },
    description @{
        column_width = 300
    },
    responsible_contact @{
        column_width = 150
    },
    parent_task @{
        /* Set $parent_task argument value as a default value for "parent_task" field */
        default_value = $parent_task, 
        /* Display column "parent_task" only if $parent_task argument value is null */
        visible = $parent_task IS NULL             
    },
    completed_datetime @{
        column_width = 125,
        visible = ($status = array['done'])
    },
    completed_person @{
        column_width = 125,
        visible = ($status = array['done'])
    }
    FROM pm.tasks
   WHERE ($is_archived IS NULL OR is_archived = $is_archived)
     AND ($responsible IS NULL OR responsible_contact = ANY($responsible))
     AND ($parent_task IS NULL OR parent_task = $parent_task)
     AND ($status IS NULL OR status = ANY($status))
     AND ($priority IS NULL OR priority = ANY($priority))
     AND ($start_date_to IS NULL OR start_date <= $start_date_to)
     AND ($start_date_from IS NULL OR start_date >= $start_date_from)
     AND ($due_date_to IS NULL OR due_date <= $due_date_to)
     AND ($due_date_from IS NULL OR due_date <= $due_date_to)
ORDER BY is_archived,
         status.@order_number, /* Return an attribute "order_numer" value for the "status" column field */
         completed_datetime DESC NULLS FIRST,
         start_date NULLS LAST,
         id
 /* Set the main entity */
FOR INSERT INTO pm.tasks

SELECT
   /* User view type.See also: https://wiki.ozma.io/ru/docs/funapp/menu */
   @type = 'menu',
   /* Menu title */
   @title = 'Главное меню',           
   @menu_centered = true,                    
   menu.blocks
FROM (
   VALUES ([
       {
           /* Menu block name */
           name: 'Все задачи', 
           /* Block width size = 3/12 */
           size: 3,
           /* Block content */                               
           content: [{
               /* Caption for the link */
               name: 'Отчеты',
               /* Reference to the linked user view */
               ref: &pm.dashboard,
               /* Icon for the link */
               icon: 'monitor_heart'                      
           },
           {
               name: 'Доска: Все задачи',
               ref: &pm.tasks_board,
               /* Arguments passed to the user view */
               args: {
                   is_archived: false,
               },
               icon: 'sticky_note_2',
           },
           {
               name: 'Таблица: Все задачи',
               ref: &pm.tasks_table,
               args: {
                   is_archived: false,
               },
               icon: 'table_chart',
           }]
       },
       {
           name: 'Мои задачи',
           size: 3,
           content: [{
               name: 'Доска: Мои задачи',
               ref: &pm.tasks_board,
               args: {
                   is_archived: false,
                   responsible: (SELECT array_agg(id) FROM base.people WHERE user = $$user_id) /* $$user_id returns ID of the current user */
               },
               icon: 'sticky_note_2',
           },
           {
               name: 'Таблица: Мои задачи',
               ref: &pm.tasks_table,
               args: {
                   is_archived: false,
                   responsible: (SELECT array_agg(id) FROM base.people WHERE user = $$user_id)
               },
               icon: 'table_chart',
               badge: {
                   value: (SELECT COUNT(id) FROM pm.tasks WHERE responsible_contact = ANY((SELECT id FROM base.people WHERE user = $$user_id))),
                   variant: 'danger'
               }
           }]
       },
       {
           name: 'Другое',
           size: 3,
           content: [{
               name: 'Люди',
               ref: &base.people_table,
               icon: 'person',
               badge: {
                   value: (SELECT COUNT(1) FROM base.people WHERE NOT is_archived),
                   variant: 'info'
               }
           },
           {
               name: 'Настройки',
               ref: &admin.main,
               icon: 'settings',
           }]
       },
   ])
) AS menu(blocks)

export default async function archiveRecord(args) {
   const ids = args.ids ?? [args.id];

   if (!('is_archived' in args))
       throw 'Неправильно задано действие';

   for (const id of ids) {
       try {                                        // FunDB API function call
           await FunDB.updateEntity(                //  updateEntity: (
               args.entity,                         //     ref: IEntityRef, 
               id,                                  //     id: number,
               { is_archived: args.is_archived }    //     args: Record
           );                                       //  ) => Promise;
       } catch(e) {
           throw 'Неправильно задано действие';
       }
   }

   return { ok: true }
}

/* pm.task_form */
@buttons = [ 
		{     
  			caption: 'Архивировать запись',
  			display: 'desktop',
        visible: NOT $id=>is_archived,
        icon: 'archive',
        variant: 'outline-danger',
        action: {
        		schema: 'user',
        		name: 'archive_record'
        },
        args: {
        		entity: { schema: 'pm', name: 'tasks' },
        		is_archived: true,
        		id: $id
        }
  	},
  	{
        caption: 'Разархивировать запись',
        display: 'desktop',
        visible: $id=>is_archived,
        icon: 'archive',
        variant: 'outline-success',
        action: {
            schema: 'user',
            name: 'archive_record'
        },
        args: {
        		entity: { schema: 'pm', name: 'tasks' },
        		is_archived: false,
        		id: $id
        }
  	}
]

А потом то же самое — из таблицы задач

/* pm.tasks_table */
@buttons = [                
		{ /* Set is_archived = true for all selected records */ 
    		caption: 'Архивировать выделенные записи', 
      	/* Display button when records are selected with a check mark */
        display: 'selectionPanel',  
  			/* Display button only if $is_archived argument value is false  */
        visible: NOT $is_archived,                      
  			icon: 'archive',                                
        variant: 'danger',                             
        action: {                                 
        		schema: 'user',
            name: 'archive_record'
        },
        args: {                                      
        		entity: {
            		schema: 'pm',
                name: 'tasks'
            },
            is_archived: true
        },
  	},
    {
				caption: 'Разархивировать выделенные записи',
        display: 'selectionPanel',
        visible: $is_archived,
        icon: 'unarchive',
        variant: 'success',
        action: {
						schema: 'user',
            name: 'archive_record'
        },
        args: {
						entity: {
								schema: 'pm',
                name: 'tasks'
						},
            is_archived: false
				}
		}
]

import { getPersonName } from 'admin/user_info.mjs';

export default async function insertNote(event, args) {
   const taskId = event.source.id;
   const now = new Date();
   const author = await getPersonName() ?? args.author;

   /* append status change event */
   if ('status' in args) {
       const msg = args.status
           ? `изменил(а) статус задачи на \"${args.status}\"`
           : `убрал(а) статус задачи`;

       await addNote(taskId, now, author, msg);
   }

   /* append priority change event */
   if ('priority' in args) {
       const msg = args.priority
           ? `изменил(а) приоритет задачи на \"${args.priority}\"`
           : `убрал(а) приоритет задачи`;

       await addNote(taskId, now, author, msg);
   }

   /* append archive change event */
   if ('is_archived' in args) {
       const msg = args.is_archived
           ? `переместил(а) задачу в Архив`
           : `вернул(а) задачу из Архива`;

       await addNote(taskId, now, author, msg);
   }

   return true;
}

/* add entity into "pm"."notes_for_tasks" table */
async function addNote(taskId, date, author, message) {
    await FunDB.insertEntity({
           schema: 'pm', name: 'notes_for_tasks'
       },
       {
           note_datetime: date,
           author: author,
           message: message,
           type: 'event',
           task: taskId
       }
   );
}

Теперь при изменении, например, статуса задачи с id = 101 с "Новое” на "В работе” сработает триггер, который вставит в таблицу notes_for_tasks новую запись:

{
       note_datetime:`2022-01-01 10:00:00`,
       author: `Петя`,
       message:`изменил(а) статус задачи на "В работе"`,
       type: 'event',
       task: 101
}