Selecting Full Weeks or Full Months

[aschoijett]

Hi,
I’m facing two challenges that I don’t know how to solve.

Challenge 1:
I need the user to be able to select a full week in the year BUT the “offered weeks” needs to start on a Sunday and end on the following Saturday (e.g. from 2020-01-05 to 2020-01-11). I would like to save both the start and the end dates of the chosen week in the database.

Challenge 2:
Similarly to the above, I need the user to be able to select any month in the year. The “offered months” needs to start on Day 1 of the month and need to end on month end (e.g. 2020-02-01 to 2020-02-29) . Once again, I need to save the start and end dates of the chosen months.

I focused on the first, assuming that a solution for the second will follow the same or very similar pattern, and tried these alternatives:

1. Use an Input field of type Week. I hit two problems: the week starts on a Monday, not on a Sunday and the saved value looks like 2020-W02. I could not figure out how to extract the start and end dates from this.

2. Use a Date Range Picker. Although this widget seems to have a good way to segregate start and end date on the server side (I think Teodor wrote an excellent article for it), I can’t seem to be able to force the selection of full weeks that start on a Sunday.

3. Two input types of type Date. Call them StartDate and EndDate. If only I could force StartDate to accept only Sundays, I can easily force EndDate be 6 days later. But I don’t know how to do that.

Unfortunately, none of them got me to the end line, so any guidance on how to attack these challenges would be really appreciated!

Alex

[psweb]

I don’t know if this will help at all but if you try the date range solution, you can change the framework of moment.js to moment with locals js, and choosing the correct locale will allow you to change to s Sunday start.

[aschoijett]

Thanks for the prompt response Paul, but unfortunately, I have no idea of what is needed to do what you suggest. I’m a beginner , so you’ll have to speak slower and louder

Furthermore, with which alternative should I use what you suggest (1, 2, or 3 of the ones I mentioned above or a different one)?

[psweb]

Before i try guide you, quick question, using the date range picker as you have it now, does the week already start on a Sunday, or not?

However this is how you get to the locales versions anyway just for reference

[psweb]

Sorry, i just spent a bit of time trying a few things, and I am unsure how to do this myself, going to need someone a little more experienced with the date range picker to assist with this one.

[aschoijett]

Hi Paul,
Yes, the date picker shows weeks starting on a Sunday. The problem I have with the dp is to force the user to only “select” a Sunday.

[psweb]

There is only one way to really do this but it is not perfect.

Instead of using a date range picker you would need to use a standard date picker, and only allow the user to select the actual Sunday, which would then allow that one single value to be stored in the database.
With that one single date inside a database insert you could easily use the formatters available to add 6 days to it, which would give you the ending date. something like date1.value.addDays(6)

There is one issue though, to disable all the dates other than Sundays, I would have to go against every good practise and adjust the core Wappler files, namely dmxDatePicker.js and adjusting the disableweekends parameter to actually disable all dates other than Sundays. 0!==t

Honestly I would not suggest you do that, as when Wappler updates every single week, your site will break, so lets rather ask the Wappler team if they could add functionality to disable an array of days like [1,2,3,4,5,6] only leaving day 0 which is in essence Sunday. Although I think this would only work on a DatePicker and not a date range picker anyway.

[Teodor]

Hello @aschoijett
So what should initially be displayed to the user? A dropdown/list with the weeks in the year, or a date picker?

[Hyperbytes]

SORRY - PUT A HOLD ON THAT IDEA.
the query runs perfectly in Navicat but fails as a wappler custom query. Looking for a fix.
UPDATE: looks like you cant DECLARE variables directly like that in Wappler, need to be via wappler parameters
Any ideas @mebeingken, you use custom procedures a lot in wappler?

Personally i would approach this via a data table and a HTML select input

In this example i have a table called ‘datelist’ which has a single field called ‘mydate’ type Date

This routine inserts every Sunday date from the current date to 2 years hence (range easily changed)

Not sure how often this would have to be updated but calling this server action at any time will add any new dates needed at the end of that period. If needed it is an easy delete query to remove expired dates.

So the custom query is as below

BEGIN
DECLARE adate date;
DECLARE dateStart date;
DECLARE dateEnd date;
SET dateStart = CURDATE();
SET dateEnd = date_add(dateStart, INTERVAL 2 YEAR);
    WHILE dateStart <= dateEnd DO
		IF DAYOFWEEK(dateStart) = 1 THEN BEGIN
        SET adate = (SELECT mydate FROM datelist WHERE mydate = dateStart);
        IF adate IS NULL THEN BEGIN
            INSERT INTO datelist (mydate) VALUES (dateStart);
			END;
		END IF;
        END;
		END IF;
        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
    END WHILE;
END

The routine sets a start and end date, loops through all dates between them and checks if the day is a Sunday with the DAYOFWEEK MySQL function
if it is a Sunday then it checks to see if it is in the table and if not adds it

This can be used as a dynamic source for a select (dropdown) input

[aschoijett]

@Teodor, I’m kind of indifferent so I’ll probably use a an approach based on dropdown as per Hyperbytes’ post. Many thanks to all!

PS: It’s a really good feeling to know that the Community is there to help those of us who get stuck. I wish that some day I can be one of those who provides good answers and not just questions.

[Hyperbytes]

I have and alternative solution but sorry, no time to document it until tomorrow

[Hyperbytes]

Still need this solution?

[UKRiggers]

It would be useful for me if you have an easy solution

[aschoijett]

Hi Brian,
Although it is not the ideal because a dropping calendar like the one for or looks a lot better, for now I am planning to use a drop down loaded from a data table.

This is because
a) it’s not obvious (to me) how to extract the start and the end dates from the value of the <INPUT …> field
b) the shows a calendar that highlights the selected week in a manner that starts on a Monday and ends the next Sunday. I need the “offered” week to start them on a Sunday. (As a curiosity, and by design of their respective manufacturers, showing a calendar with a month is the behaviour of the Goggle Chrome browser. MS Edge does not show a calendar.)

Following your idea, I created a couple of tables, one for Monthly Periods and the other for Weekly Periods. These tables have records for monthly and weekly periods for a few years before and a few years after today. I also put in them not only the Period name but also the start and end dates of each. Since my SQL is rusty at best, I used MS Excel to create the contents of these tables and uploaded them to the DB using mysql workbench.

Now I have to figure out how to create the “right” queries to populate the widgets to ensure they include N1 records before and N2 records after the period that contains a central date, which will typically be today’s.

[Hyperbytes]

I will be posting two tutorials tomorrow

Tutorial 1 will be generating a table of dates between two given values or adding missing dates to an existing date range with a standard server action

Tutorial 2 will be to use that table in conjunction with a Dataview to filter that table to give subsets of the table i.e. only Sundays, only January etc

I will also look to add x weeks before and after a given date

[Hyperbytes]

Tutorials here

Generating or adding to a list of dates in a table

Many applications need date lists such as calendars or lists to form the basis of custom date pickers In this simple tutorial i will show you how to generate a list of all dates within a specified date range While there are undoubtedly more efficient ways to do this using custom queries i have stayed with the inbuilt Wappler visual tools This routine will check for the presence of each date and will only add if missing, This allows the routine to be run on a regular basis or even as a schedul…

Filtering a date list using a Dataview to show only a selected day of the week in the list

This guide follows on from the previous guide: Before we can use the previously created table we need to make a basic query server action So we create a server action, in this case i called it ‘getdate’ we add database connection [image] and a Database query I have not added any conditions,. the query returns all records. To hide expired entries simple add a condition to ensure the date is => NOW following the method shown in the previous tutorial [image] That’s all which is needed fo…