Tips for making a weekly planner

[calida82]

I don't need the duration, I need this as a memo, I've seen the changes and I've seen the code comments this is awesome!!! You're great!! Nubuilder is fantastic, I tried it a few years ago but it seemed very complicated then I started to explore it a little better and it's wonderful. I have my work management system with access and I hope to migrate to nubuilder soon. You are very helpful and your highly commented code will help me experiment with new things for my management software. How can I repay?

[kev1n]

I added an optional duration field as well.
Attached is the sql to create the form and the table (Run the sql on an existing nuBuilder database)

[Drop me private message if you are interested]

I'm not expecting you to buy me a coffee, but it would appreciate. (Follow the link in my signature). I spent more than 10 hours on it.

Let me know if you have any questions.

[miasoft]

I created new db "wp" and imported tables from zip above. After run form "weekly_planner" I get empty list of appointments and error:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp.___nu164fec2b51e911___' doesn't exist

===SQL===========
SELECT appointment_id,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
 FROM ___nu164fec2b51e911___
WHERE 1 LIMIT 0, 10
===BACK TRACE====

D:\mia\xampp\htdocs\wp\core\nuform.php - line 1253 (nuRunQuery)
D:\mia\xampp\htdocs\wp\core\nuform.php - line 459 (nuBrowseRows)
D:\mia\xampp\htdocs\wp\core\nuapi.php - line 85 (nuGetFormObject)

New records add OK. I see new records in db.

[kev1n]

I've just created a new database and I can't seem to replicate the issue you're encountering.

[miasoft]

I created all from zero. The first SQL-error is:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY DAYOFWEEK(app_date) ORDER BY app_time) AS row_num
            FROM' at line 61

===SQL===========

CREATE TABLE ___nu164fecae823f01___ 
        SELECT
            MAX(CASE WHEN day_of_week = 2 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Monday,
            MAX(CASE WHEN day_of_week = 3 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Tuesday,
            MAX(CASE WHEN day_of_week = 4 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Wednesday,
            MAX(CASE WHEN day_of_week = 5 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Thursday,
            MAX(CASE WHEN day_of_week = 6 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Friday,
            MAX(CASE WHEN day_of_week = 7 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Saturday,
            MAX(CASE WHEN day_of_week = 1 THEN JSON_OBJECT(
                'appointment_id', appointment_id,
                'formatted_time', formatted_time,
                'formatted_duration', formatted_duration,
                'app_title', app_title,
                'app_description', IFNULL(NULL, app_description)
            ) END) AS Sunday,
            MAX(0) as appointment_id
        FROM (
            SELECT
                DAYOFWEEK(app_date) AS day_of_week,
                appointment_id,
                TIME_FORMAT(app_time, '%H:%i') AS formatted_time,
                IFNULL(TIME_FORMAT(app_duration, '%H:%i'), '') AS formatted_duration,
                app_title,
                app_description,
                ROW_NUMBER() OVER (PARTITION BY DAYOFWEEK(app_date) ORDER BY app_time) AS row_num
            FROM appointment
            WHERE app_date BETWEEN '2023-09-11' AND '2023-09-17'
        ) AS RankedAppointments
        GROUP BY row_num
        ORDER BY MIN(row_num);


===BACK TRACE====

D:\mia\xampp\htdocs\wp\core\nucommon.php(1406) : eval()'d code - line 135 (nuRunQuery)

D:\mia\xampp\htdocs\wp\core\nucommon.php - line 1406 (eval)

D:\mia\xampp\htdocs\wp\core\nuform.php - line 21 (nuEval)

D:\mia\xampp\htdocs\wp\core\nuform.php - line 1131 (nuBeforeBrowse)

D:\mia\xampp\htdocs\wp\core\nuform.php - line 458 (nuBrowseColumns)

D:\mia\xampp\htdocs\wp\core\nuapi.php - line 85 (nuGetFormObject)

[kev1n]

MySQL version? If your version doesn't support "PARTITION BY" you will need to modify the query to make it work with your version.
You can also ask ChatGPT

[miasoft]

MySQL version? If your version doesn't support "PARTITION BY" you will need to modify the query to make it work with your version.
You can also ask ChatGPT

my version 5.7.30-log

[kev1n]

Is there any chance of using a newer MySQL version that supports PARTITION BY and ROW_NUMBER()?

[miasoft]

I will try

[miasoft]

I will try

I installed MySql v8.1. All work fine! Tnx!