Creating a Calendar

[Cardioes]

Greetings Sybase Community,

I am still a bit new to Powerbuilder, and coding in
general, and would like to ask some assistance. I am
attempting to create a calendar. Here is what I have done so
far, and the problems I am having.

-I created a table with just two columns; month and year.
-On the Datawindow, I made 35 square blocks, to simulate
the look of a calendar and placed a text block on each of
them.
-I made the two column's (Month and Year) drop down
lists, so that when I user selects them I can access the
entered value numerically.

-I then made a window and dragged the datawindow into it.
For now, I added a button in order to use the 'Clicked'
script, but will probably change the initialization once the
script itself is working.
-Here is what I have for the clicked script:

------------------------------------------
////Generate Month

Long Day_i, NextDay_i, Year_i, Month_i, row_i
String Date_i, DayName_i, mod_script_i


////Get Column Values
row_i = dw_1.getRow()
dw_1.setcolumn("dayname_c")
DayName_i = dw_1.getText()
dw_1.AcceptText()
messagebox("Day Name = ", DayName_i)

-------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
"Dayname_c" is a computed field on the datawindow with the following computation:


-------------------------------------------
dayname(date(String(year) +" -"+ String( month) +"-01"))
-------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I added the Messagebox to help me debug my progress. So far, it only returns the number for the month (2 for Feb, 3 for March), instead of the dayname that it is supposed to return.
However, when I test it in the Datawindow, the computed field works and displays correctly.

Once I get the above working, hopefully with some help, then I need change the text blocks on the datawindow to display the correct first day of the month:

---------------------------------------------
//if DayName_i = "Sunday" then
// // cell1 = "1"
//w_open_appointments.dw_1.Modify("cell1.text= 1" )
// Day_i = 2
//
//elseif DayName_i = "Monday" then
//// cell2 = "1"
//w_open_appointments.dw_1.Modify("cell2.text= 1" )
// Day_i = 3
---------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^(etc for all 7 days)
"Cell*.text" refers to the names of the text blocks on the datawindow.

Then after that, I want to create the rest of the month:

--------------------------------------------
//NextDay_i = 2
//While cell[Day_i] < 32
// cell[Day_i] = String(NextDay_i)
// Day_i = Day_i+1;
// NextDay_i = NextDay_i+1;
//
-------------------------------------------
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I know this loop is done incorrectly. I have some experience in Java, but as aforementioned I'm new to PB. I typed this quickly last night to give me a reference to work by.

But first things first. I The top code isn't working ^^.

Any assistance would be very much appreciated.

 

[Cardioes]

Alright, so I fixed the initial problem using:

------------------------------
////Generate Month

Long Day_i, NextDay_i, Year_i, Month_i, row_i
String Date_i, DayName_i, mod_script_i


////Get Column Values

row_i = dw_1.getRow()
dw_1.setcolumn("Month")
Month_i = dw_1.getItemNumber(row_i, "Month")
dw_1.AcceptText()
messagebox("Day Name = ", Month_i)

row_i = dw_1.getRow()
dw_1.setcolumn("Year")
Year_i = dw_1.getItemNumber(row_i, "Year")
dw_1.AcceptText()
messagebox("Day Name = ", Year_i)
------------------------------------------------
^^^^^^^^^^^^^(Again, Message Box's for Debugging)

However, I can't get the text to change on the Datawindow.
Here is the code I am using to create the first day of the month:

-----------------------------------------------
////Starting day of the month

Date_i = String(Year_i) + "-" + String(Month_i) + "-01"
DayName_i = dayname(date(Date_i))
messagebox("Day Name = ", DayName_i)

if DayName_i = "Sunday" then
// cell1 = "1"
messagebox("Day Name = ", "The day is Sunday")
w_open_appointments.dw_1.Modify("cell1.text= 1" )
Day_i = 2

elseif DayName_i = "Monday" then
// cell2 = "1"
messagebox("Day Name = ", "The day is Monday")
w_open_appointments.dw_1.Modify("cell2.text= 1" )
Day_i = 3

elseif DayName_i = "Tuesday" then
// cell3 = "1"
messagebox("Day Name = ", "The day is Tuesday")
w_open_appointments.dw_1.Modify("cell3.text= 1" )
Day_i = 4

elseif DayName_i = "Wednseday" then
// cell4 = "1"
messagebox("Day Name = ", "The day is Wednsday")
w_open_appointments.dw_1.Modify("cell4.text= 1" )
Day_i = 5

elseif DayName_i = "Thursday" then
// cell5 = "1"
messagebox("Day Name = ", "The day is Thursday")
w_open_appointments.dw_1.Modify("cell5.text= 1" )
Day_i = 6

elseif DayName_i = "Friday" then
// cell6 = "1"
messagebox("Day Name = ", "The day is Friday")
w_open_appointments.dw_1.Modify("cell6.text= 1" )
Day_i = 7

elseif DayName_i = "Saturday" then
// cell7 = "1"
messagebox("Day Name = ", "The day is Saturday")
w_open_appointments.dw_1.Modify("cell7.text= 1" )
Day_i = 8
end if
-------------------------------------------------

 

[Cardioes]

Never mind. I got it to work =)

 

[Cardioes]

Alright, so I made some adjustments to handle various exceptions, and the calendar works as intended; at least for now, hehe.

I'm starting to play with Select Statements, and I think I grasp the concept. However, trying to get the information from one column to check if it exists...then to loop a select statement, so that it grabs a specific value for a specific date...that exists in a record on another table...is confusing.

Essentially, this window will be utilizing information from two tables. One is for the calendar and mainly for display purposes.
The other is from a table with actual data. I need to check how many times a specific 'String' appears on a specific 'Date', and display the the number of occurrences on the calendar, for the appropriate date.

I hope I am typing all of this clear enough, because I know the end goal, but am confusing the hell out of myself -.-;

Here is what I have, and I know I butchered the SELECT statement, but it is a working in progress; with my mind in a bewildered state:

//Generate Month

Long Day_i, NextDay_i, Year_i, Month_i, row_i, Initialize_i
String Date_i, DayName_i, mod_script_i, LeapYear_i
Boolean DateCheck_i


////Get Column Values

//This simply opens a window that asks the user to "Please Wait While the Calendar is Constructed", in case it runs slower on older hardware.
open(w_please_wait)
//

//Obtain the Month from the User's input
row_i = dw_1.getRow()
dw_1.setcolumn("Month")
Month_i = dw_1.getItemNumber(row_i, "Month")
dw_1.AcceptText()

//Obtain the Year from the User's Input
row_i = dw_1.getRow()
dw_1.setcolumn("Year")
Year_i = dw_1.getItemNumber(row_i, "Year")
dw_1.AcceptText()

//Clear out the Calendar
//This was added so taht the '1's, '2's, etc from other months wouldn't display. This occured because some month's start on a later day than others.

Initialize_i = 1;
if Initialize_i = 1 then
LoopSection1:
if Initialize_i <= 35 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Initialize_i)+"")
dw_1.setItem(row_i, "cell" +String(Initialize_i)+"", "")
dw_1.AcceptText()

Initialize_i = Initialize_i +1;
Goto LoopSection1
End If
End If


//Finds the Starting "Day Name" based on the Day that the User Inputs.
//Then sets a '1' into the corresponding cell block.

Date_i = String(Year_i) + "-" + String(Month_i) + "-01"
DayName_i = dayname(date(Date_i))


if DayName_i = "Sunday" then
// cell1 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell1")
dw_1.setItem(row_i, "cell1", 1)
dw_1.AcceptText()
Day_i = 2

elseif DayName_i = "Monday" then
// cell2 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell2")
dw_1.setItem(row_i, "cell2", 1)
dw_1.AcceptText()
Day_i = 3

elseif DayName_i = "Tuesday" then
// cell3 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell3")
dw_1.setItem(row_i, "cell3", 1)
dw_1.AcceptText()
Day_i = 4

elseif DayName_i = "Wednesday" then
// cell4 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell4")
dw_1.setItem(row_i, "cell4", 1)
dw_1.AcceptText()
Day_i = 5

elseif DayName_i = "Thursday" then
// cell5 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell5")
dw_1.setItem(row_i, "cell5", 1)
dw_1.AcceptText()
Day_i = 6

elseif DayName_i = "Friday" then
// cell6 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell6")
dw_1.setItem(row_i, "cell6", 1)
dw_1.AcceptText()
Day_i = 7

elseif DayName_i = "Saturday" then
// cell7 = "1"
row_i = dw_1.getRow()
dw_1.setcolumn("cell7")
dw_1.setItem(row_i, "cell7", 1)
dw_1.AcceptText()
Day_i = 8
end if

// Creates a month with 31 Days.

if Month_i = 1 or Month_i = 3 or Month_i = 5 or Month_i = 7 or Month_i = 8 or Month_i = 10 or Month_i = 12 then

NextDay_i = 2

LoopSection31:
if NextDay_i <= 31 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", NextDay_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection31
end if
end if

//Creates a Month with 30 Days
if Month_i = 4 or Month_i = 6 or Month_i = 9 or Month_i = 11 then

NextDay_i = 2

LoopSection30:
if NextDay_i <= 30 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", NextDay_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection30
end if
end if

//For Feburary, Checks for a Leap Year. If the Date is Not Valid, then it is not a Leap year, and a Month with 28 days is created
//If the date is valid, then a Leap Year exists, and a month with 29 days is created.
if Month_i = 2 then

NextDay_i = 2
LeapYear_i = String(Year_i) + "-" + String(Month_i) + "-29"

if IsDate(LeapYear_i) then
LoopSection29:
if NextDay_i <= 29 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", NextDay_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection29
end if
else
LoopSection28:
if NextDay_i <= 28 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", NextDay_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection28
end if
end if
end if
//


///////////////////////////////////////////////

//Retrieve the number of OPEN Appointments

////Initialize Variables
//
//String Open_Appt_i, Status_Check_i
//Int Open_Count_i, DayNo_i, Open_Exists_i
//
////Initial Select Statement to see if any Appointments have an 'OPEN' status
//
//SELECT count(*)
// INTO :Open_Exists_i
// FROM "appointments"
// WHERE "apptmntstatus" LIKE '%OPEN%'
// USING SQLCA;
//
//
// DayNo_i = 1;
// LoopSection35:
// If Open_Exists_i > 0 then
//
////DayNo_i will increment so that all 35 cell blocks will be filled
// if DayNo_i < 35
// SELECT MAX("appointments")
// INTO :Open_Appt_i
// FROM "appointments"
//
////Each cell must display the number of 'OPEN' appointments, for each date on the calendar.
////The User select's Year_i and Month_i from a prompt when the window first opens.
////DayNo_i will increase by 1 each time the loop is processed, until all 35 blocks display the number of 'OPEN' status' per day.
// WHERE "appointments"."apptmntdate" LIKE %Date(String(Year_i) + "-" + String(Month_i) + String(DayNo_i))%
// USING SQLCA;
//
////Change the value for each cell to the number of 'Open' from the date above
// row_i = dw_1.getRow()
// dw_1.setcolumn("open_appts" +String(DayNo_i)+"")
// dw_1.setItem(row_i, "open_appts" +String(Dayno_i)+"", Open_Count_i)
// dw_1.AcceptText()
//
// DayNo_i = Dayno_i +1;
// Goto LoopSection35
// End If
// End If




//Closes the "Please Wait..." Window, once the Calendar has been created.
close(w_please_wait)

 

[Cardioes]

I think I cleared some of my own confusion =p, I just need help with the select statement now.

I added the Select Statement to the loop that creates each day on the Calendar.

The error I'm getting is:

"C0038: SQLSTATE = 37000[Sybase]ODBC DRIVER][Adaptive Server Anywhere]Syntax error near 'row_i' on line 1"

And the Code looks as follows:

-----------------------------
if Month_i = 1 or Month_i = 3 or Month_i = 5 or Month_i = 7 or Month_i = 8 or Month_i = 10 or Month_i = 12 then

NextDay_i = 2

LoopSection31:
if NextDay_i <= 31 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", NextDay_i)
dw_1.AcceptText()

Date_i = String(Year_i) + "-" + String(Month_i) + String(Day_i)

SELECT MAX("appointments")
INTO :Open_Count_i
FROM "appointments"
WHERE "apptmntdate" = ate_i and "apptmntstatus" = :Appt_Status_i

row_i = dw_1.getRow()
dw_1.setcolumn("open_appts" +String(Day_i)+"")
dw_1.setItem(row_i, "open_appts" +String(Day_i)+"", Open_Count_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection31
end if
end if

------------------------------------------
The code is the same for Months with 30 days, 29 days and 28 days, with the slight difference in the count.

 

[Cardioes]

I can not even get the Message box to pop up, so that I may debug it:

LoopSection31:
if NextDay_i <= 31 then
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", String(NextDay_i))
dw_1.AcceptText()

Date_i = String(Year_i) + "-" + String(Month_i) + String(Day_i)

SELECT MAX("appointments"."apptmntstatus")
INTO :Open_Count_i
FROM "appointments"
WHERE "appointments"."apptmntdate" = ate_i and "appointments"."apptmntstatus" = :Appt_Status_i
USING SQLCA;

MessageBox("Debug", "The number of Open Appointments is " + String(Open_Count_i))
row_i = dw_1.getRow()
dw_1.setcolumn("open_appts" +String(Day_i)+"")
dw_1.setItem(row_i, "open_appts" +String(Day_i)+"", Open_Count_i)
dw_1.AcceptText()

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
Goto LoopSection31
end if
end if

 

[mbalent]

First of all replace all your 'loopsection 1 ... goto loopsection 1' type code with DO WHILE loops. Example Syntax is 'DO WHILE NextDay_i <= 31 ... LOOP'. This also eliminates the need for your 'If NextDay_i <= 31' statements. This will make the code much easier to read/understand. You are already incrementing the NextDay_i counter so you are already in good shape.

Next, I assume you are still getting the SQL error. My assumption is your problem is you are trying to put the appointments.apptmntstatus into a numeric variable. I think you want to do a SELECT COUNT(*) INTO ... instead of SELECT MAX(... (this gives you the number of appointments of a certain status on a certain date).

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[Cardioes]

Matt,

Thank you again for your assistance! Any chance to clean up my code and learn more efficient methods is much appreciated =).

Your suggestion for using "SELECT COUNT(*)" did indeed solve the issue of my select statement now working in run-time. However, it is returning a value of 0 for each day, when there should be roughly 18 on each day.

---------------------------------------
//Create a Month with 31 Days

DO WHILE NextDay_i <= 31
row_i = dw_1.getRow()
dw_1.setcolumn("cell" +String(Day_i)+"")
dw_1.setItem(row_i, "cell" +String(Day_i)+"", String(NextDay_i))
dw_1.AcceptText()

Date_i = String(Year_i) + "-" + String(Month_i) + String(Day_i)

//Obtain the number of "Open" appointments in table //'appointments', column 'apptmntstatus', for each day of the //month; from column 'apptmntdate'. Store the number of "open" //in the variable Open_count_i. Then put that value into the //corresponding column "open_appts(*)" for each day on the //created calendar.
SELECT COUNT(*)
INTO :Open_Count_i
FROM "appointments"
WHERE "appointments"."apptmntdate" = ate_i and "appointments"."apptmntstatus" = :Appt_Status_i
USING SQLCA;


row_i = dw_1.getRow()
dw_1.setcolumn("open_appts" +String(Day_i)+"")
dw_1.setItem(row_i, "open_appts" +String(Day_i)+"", Open_Count_i)
dw_1.AcceptText()

MessageBox("Debug", "The number of Open Appointments is " + String(Open_Count_i))

Day_i = Day_i+1;
NextDay_i = NextDay_i+1;
LOOP
end if

 

[mbalent]

My assumption would be that your WHERE clause is excluding the data. Look again at the values in 'apptmntdate' - are they dates or some other data type? Try to run the same SQL you are generating against the database to see the results (leave out the 'INTO ...' portion).

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com