Starting a new program this summer


#1

I’m revising my previous question here as I’ve worked on it some more on my own.

We’ll be doing another Adult Program this summer & I can see that all the badges can be edited for re-use. How do I handle the old patrons and what other data do I need to clear out? (& how?)

updating my own post 4/26 - If an old patron logs in, their account is associated with last summer’s program & all their points, etc. are still linked. So - I will have to delete all old patrons and require everyone to get a new avatar, to start fresh. I can do this one by one in the Control Room. Is there any other data I need to worry about when editing my Adult Program for this year? (creating a new Program requires revising all the Static Text, too and I wonder what else, so don’t want to have to redo it each year.)


#2

I’d like to hear how to RESET or Zero points and or activity, or at least archive it?

I saw a Delete Patron procedure in the code posted on GitHub. I don’t know how to access/use that code, and really would prefer not deleting the records, but rather zeroing progress…

I’m going to try to “dig in” to the SQL code and try to figure out if I can do this… I suspect someone already knows how.

TIM


#3

@Tammy The most efficient way to do what you’re trying to do would be to directly clear out the users from the SQL database. You can go one by one, but if you have many users, that would take a long time. I think the only other data you would be concerned about is your events data (if you’re using that module).


#4

You’d want to do this in the SQL database. The Delete Patron procedure you’re referring to (if I am correct), is the stored procedure the app uses to clear a single user from the database. Every time I’ve ever cleared users from the database for a new year, it’s been done at the SQL level.


#5

@Tammy @birchard All that said, you can delete a program and its associated patrons from within the app. But you’d have to create the program a second time, which it sounds like Tammy doesn’t want to do.


#6

@carisomalley @birchard
Could anyone tell me the steps to do this through the SQL database? If it’s too complicated, I may just do it in the Control Room - I only have a dozen users in there to delete. Hopefully deleting the users will clear out all the associated information and then I can just edit the rest of the Program to be “new” for this summer.

I’m still unsure if I should just start a new Program instead, but it looks like I’ll have to delete the old patrons regardless since there’s old activity showing in the Feed and Activity points.


#7

Here’s what I did.

Open SQL Management Studio.

I connect to the DB using Windows Authentication
[New Query]
query is:

select * from SRP.dbo.PatronPoints where AwardDate < ‘2016-12-31’;
delete from SRP.dbo.PatronPoints where AwardDate < ‘2016-12-31’;

It doesn’t happen until you [ ! Execute ]

TIM


#8

@birchard Thanks so much for the screenshot & how-to. It may be a week or so until I try this though since I’m off for a few days.
I really appreciate the help.


#9

Following up here… @carisomalley @tammy

I need to age and promote my participants. That is if John Doe was an 8-year-old-3rd-grader … I need to move him to 4th grade, and increase his age to 9. (or make him responsible to do so.?)
@harald


#10

Counting/Reporting/Tracking participation.

I can create (control room, ad-hoc report) a roster of participation where “Point” is FROM: 01/01/17 TO: blank and points FROM: 0 TO: blank

Wonder if that is enough to count (only) 2017 Participants.

{I saved that ad-hoc as a TEMPLATE, but the Point FROM 0 doesn’t stick??}


#11

Sure. I can post the rest of the screen shots… I’m not that good with SQL yet.


#12

Sorry I’ve been away.

One note: you will probably want to take a SQL Database backup before you do a DELETE in case you make a mistake.

That DELETE looks right but I’m not sure how the software will like it - the database tables don’t have a lot of foreign key constraint relationships so there may be other data in the database that is relying on the user table. It’s certainly worth a shot.

The reason this is so difficult is that I don’t believe the software was intended to be used year-over-year in this fashion by the original author. We’re trying to take this into account as we work on the next version.


#13

@birchard You might be able to accomplish this with the following:

For age:

UPDATE [Patron] SET [Age] = [Age] + 1 WHERE [Age] IS NOT NULL

Keep in mind that will increase the age of every person in the table - if you only want to do kids you’ll have to improve the WHERE clause to something like:

UPDATE [Patron] SET [Age] = [Age] + 1 WHERE [Age] IS NOT NULL AND [AGE] <= 18

It’s a little more tricky for grades since apparently they are being stored as text and not numbers. Our data from last year does not have grades entered so I’m supplying this query without having tested it first - Take a database back up before you try it in case it doesn’t work right!

UPDATE [Patron] SET [SchoolGrade] = [SchoolGrade] + 1 WHERE ISNUMERIC([SchoolGrade]) = 1

Since grade is stored alphanumerically it’s possible there is non-numeric data in the field, this query won’t help with that.

Of course, that math will put people into the next grade no matter what grade they entered. If you want to clear out the grade column for everyone over a certain grade you could use something like:

UPDATE [Patron] SET [SchoolGrade] = '' WHERE ISNUMERIC([SchoolGrade]) = 1 AND [SchoolGrade] > 12

Let me know how this works for you!


#14

@harald So, it wouldn’t matter whether I’m starting a new Program for Adults this summer, or editing the old one to revamp it for this year, would it? I still have to delete the patrons, though… maybe through the Control Panel one by one will be best for me. (such a small # it will be ok)


#15

@harald @Tammy @carisomalley

It worked SWELL! thanks!! ( have to clear those now in grade 13 … but may just let it ride…)

We also need to switch programs for kids moving from CHILDRENS to TEENS …


#16

So the delete patron stored procedure just deletes patron data from a few different tables. Rather than clicking delete by each patron you could run the following in SQL Server which would have the same effect:

SET NOCOUNT ON
DECLARE @PID INT = 0

WHILE (1 = 1)
BEGIN
	SELECT TOP 1 @PID = PID
	FROM [Patron]

	IF @@ROWCOUNT = 0
		BREAK;

	PRINT 'Deleting ' + CAST(@PID AS NVARCHAR)

	EXEC app_Patron_Delete @PID
END

This is running a little slow for me but I’m in a test environment. If you click the ‘Messages’ tab you’ll see that it’s deleting each Patron based on the Patron ID.

As always I’m going to recommend you do a SQL Server database backup first. :slight_smile:


#17

So if you wanted to change the program for teens you could do something similar to:

SELECT [PID], [AdminName] FROM [Programs];

Figure out which PID is the program you want to move them to.

Then a query such as:

UPDATE [Patron] SET [ProgID] = XXX WHERE [Age] > 12 AND [Age] < 20

Replacing XXX with the PID that you got from the first query.


#18

My Childrens SRP-Admin recycled Badges from last year. (changed the picture, possibly tweaked award trigger…)

When she logged in as her test patron, she sees this years badges because she put the new graphic on last years awarded badge. The test patron has yet to earn any points (remember I zeroed out all points to start the new program).

Remedy:

  1. take a Database backup
  2. remove PatronBadges earned prior to this year:
    select * from SRP.dbo.PatronBadges where DateEarned < ‘2016-12-31’;
    delete from SRP.dbo.PatronBadges where DateEarned < ‘2016-12-31’;

#19

Is there a way (I’m thinking it’s going to be from SQL Mgmt) to list or count patrons whose record has been visited? Specifically- if we set a password, or if a patron only logs in (ie. knows their password) we want to count that patron as having “started” the program.

Is there a way to tell if the “Save” button has been clicked on the Patron Details page?


#20

Worked great, thank you.