Audit Management System

[hasynjo]

Rebuild of a Microsoft Access based Audit Management System

Home

• Avatar/Photo and full name of logged in user (users with admin role can select each user with select field in the upper right corner)
• 4 week preview of your schedule
• List of your messages (most of them are generated automatically after completion of workitems)
• Planner showing your 10 most urgent todos (overdue tasks are highlighted in red)
• Today's section informing about colleagues in late shift, having birthday or which are present at the office

Audit

• List of current audits with their milestones (users with audit lead can edit their audits, admins have full access)

• Detailed view of an individual audit with its milestones (derived from the tab Workflow)
• Team schedule (data + chart)

• List of workitems

Schedule

• Timeline chart of current schedule
• Entries for users with audit lead are surrounded with asterisks
• External remarks are appended in brackets

• Schedule list (users can only edit their own entries, admins have full access)

User

• List of all users

• Personal data of the selected user (users can only edit their own data, admins have full access)

• List of selected user's roles

Screenshots

AMS_Screenshots.zip

• Zip-archive of all screenshots

Conclusion

Rebuilding this application with SQLPage was fun. As someone with no experience in web development I was excited how easy and fast the development progressed. Thank you so much @lovasoa for this fantastic SQL-only data application builder!

[lovasoa]

I'm impressed with what you built ! How long did it take you? What were the biggest pain points while creating it?

[hasynjo]

Do you mean linking to files on the user's computer? You could use file:// links, but you'd have to know which files exist in the user's folders

Yes, this is what I meant. While testing I link to files and folders on my C:-Drive outside SQLPage folder, in production mode files and folders would be stored on network shares. I already tried file:// links, but on WIndows 11 nothing happens when I click on those links (tested with Firefox, Chrome and Edge):

select 
    'text' as component,
    '[MyFolder](file://C:/MyFolder/1.txt) as contents_md -> http://localhost:8080/test' as contents_md,
    '[MyFolder](file://C:/MyFolder/1.txt) as unsafe_contents_md using MD -> file:///C:/MyFolder/1.txt (Open link in new tab via context menu)' as unsafe_contents_md,
    '<a href="file://C:/MyFolder/1.txt">MyFolder</a> as unsafe_contents_md using HTML -> file:///C:/MyFolder/1.txt (Open link in new tab via context menu)' as html;

select 
    'steps'  as component;
select 
    'MyFolder' as title,
    'folder' as icon,
    'file://C:/MyFolder/1.txt' as link,
    'Open link in new tab via context menu' as description;

• link as contents_md reopens current SQLPage sql-file
• link as contents_md, unsafe_contents_md or as part of a component doesn't do anything when left-clicking on it
  • when selecting Open link in new tab via context menu
    • Firefox: a new tab file:///C:/MyFolder/1.txt opens, which opens file/folder after pressing enter
    • Chrome & Edge: a new tab about:blank#blocked opens

When opening a static html-file directly in the browser (without SQLPage), the file/folder opens instantly. As a workaround a run a small python server (python -m http.server -d C:). This solution is inconvenient to me, as all linked files have to be downloaded first.

Does it need actix-files for serving static files from disk?

[kryskool]

Hi @hasynjo

can you try with 3 slash like file:///C:/MyFolder/1.txt (or file://localhost/C:/MyFolder/1.txt)

you can found some information on the File URI handler on wikipedia

Regards,

[hasynjo]

Salut @kryskool,

thanks for your tip. All variants file://, file:/// and file://localhost/ are translated in my browser into file:/// with the same result:

• link as contents_md reopens current SQLPage sql-file
• link as contents_md, unsafe_contents_md or as part of a component doesn't do anything when left-clicking on it
  • when selecting Open link in new tab via context menu
    • Firefox: a new tab file:///C:/MyFolder/1.txt opens, which opens file/folder after pressing enter
    • Chrome & Edge: a new tab about:blank#blocked opens

When opening a static html-file directly in the browser (without SQLPage), the file/folder opens instantly.

Kind regards

[lovasoa]

in production mode files and folders would be stored on network shares

What I would do is host SQLPage on a server that has access to the network folder, mount the folder inside sqlpage's directory, and serve the files directly from sqlpage.

If it's a microsoft environment, and the files are available in sharepoint, then I would use the sharepoint url

[hasynjo]

mount the folder inside sqlpage's directory, and serve the files directly from sqlpage

Good idea 👍 This works like a charm 🙂

How to do it on Windows:

Open the Command Prompt as an administrator. Then type the following command:

mklink /D C:\LinkName \\NetworkLocation\LocationName

Unfortunately linking to a folder instead of a file, produces a 404-page (which I understand is a feature and not a bug).