CategoriesCodeSQL

Exclude Weekends from SQL Datetime Query

A localisation proof solution for excluding weekends (Saturday, Sunday) from a date range in an SQL query.

Example

You want to get the total number of hours worked in a month, but the working week excludes the weekend days. Using this script you can pick the start and end of the month as your datetime parameters, and in the where clause this query will filter out any days that fall on a weekend.

Instructions

Exlude weekends from any datetime query. This solution is tolerant to any localisation settings on target server.

  1. Work out the difference in days between the two dates. +1 to be inclusive of the first date.
  2. Work out the difference in weeks between the two dates. *2 for the two weekend days.
  3. Check for an edge case where if the first date is a sunday it is counted.
  4. Check for an edge case where if the last date is a saturday it is missed.

Code

DECLARE @StartQuery DATETIME = '10/01/2022';
DECLARE @EndQuery DATETIME = '10/31/2022';

--Work out total days between start and end, exluding weekends.
SELECT (datediff(dd, @StartQuery, @EndQuery)+1) - (datediff(wk, @StartQuery, dateadd(dd,1,@EndQuery)) * 2) 
    - CASE WHEN datename(WEEKDAY, @StartQuery) = 'Sunday' THEN 1 ELSE 0 END -- This includes for start date edge case
    + CASE WHEN DATEname(WEEKDAY, @EndQuery) = 'Saturday'  THEN 1 ELSE 0 END -- This includes for  end date edge case.

References

Bitbucket snippet.
Stack overflow thread.

CategoriesWordPressCodePHP

Show Recent Blog Posts

When using WordPress as your CMS of choice, there often becomes a need to display a list of recent blog posts on a non-blog page (such as a static homepage). The recent blog posts list is useful as it enable users to get a snapshot of what is going on in your blog, without actually having to visit the page.

There are two ways I explored to solve this particular problem:

  • Short code that can be placed anywhere
  • Snippet of code that sits directly in your template page file

This article will explore both ways.

Shortcode

The shortcode way is useful. The shortcode will enable you to insert the blog summary list anywhere you wish, just by typing the shortcode in.

First stage is to create a new plug-in. To do this, create a new folder in your plug-ins directory and give it a nice name such as “recent-posts”. Inside this directory, create a new .php file and call it the same as the directory name.

Inside this .php file we need to define the module. Copy in the following lines:

/*
Plugin Name: adamrob-recent-posts
Plugin URI: https://www.adamrob.co.uk
Description: Shows recent posts on home page
Version: 0.1
Author: adamrob.co.uk
Author URI: https://www.adamrob.co.uk
*/

This tells wordpress what the module is, who the author is and some extra meta type data.

Next, we want to create the function that will generate our recent blogs list HTML. Copy the following code in:

function adamrob_recentposts_fc( $atts ) {
  
    // Attributes
    extract( shortcode_atts(
        array(
            'noposts' =>; '3',
            'blogpageid' => '81',
        ), $atts )
    );
  
    // Code
    $output = '<div id="outer" style="width:100%; text-align:center; margin: 15px 0px 15px 0px;">';
    $output = $output . '<h2><a href="' . get_page_link($blogpageid) . '" title="Visit the blog">From The Blog</a></h2>';
  
    $args = array( 'numberposts' => $noposts );
    $recent_posts = wp_get_recent_posts( $args );
    foreach( $recent_posts as $recent ){
        $output = $output . '<div style="width:60%; margin: 0px 0px 0px 0px; display: inline-block; clear:left;">' . '<hr />';
        $output = $output . '<div id="img" style="float:left; margin-right:15px;">' . get_the_post_thumbnail($recent['ID'], 'thumbnail') . '</div>';
        $output = $output . '<p><strong>' . '<a href="' . get_permalink($recent["ID"]) . '" title=' . $recent["post_title"] . ' >' . $recent["post_title"] . '</a> ' . '</strong></p>';
        $output = $output . '<p>' . mysql2date('j M Y', $recent["post_date"]) . '</p>';
  
        $output = $output . substr($recent["post_content"], 0 , 350) . '...';
        $output = $output . '<a href="' . get_permalink($recent["ID"]) . '" title="Read More '.$recent["post_title"].'" >' . 'Read More'.'</a>';
  
        $output = $output . '</div><br />';
    }
    $output = $output . '</div>';
  
    //Return the result
    return $output;
}

This function does the following:

  • Gets the attributes (if any) that were passed into the function via the shortcode. This also writes default values if no parameter was passed in, or less then specified.
  • Uses the $output variable to store the generated HTML. This variable will then be returned at the end.
  • Set up a
     &lt;div>
    to style the layout. My layout is rather basic, but you can experiment here to achieve the style you require.
  • Get the array of blog posts using the WordPress function
    wp_get_recent_posts( $args )
  • Loop through the posts and extract the data that we want, then render it in some html tags. In my code, I am using the following WordPress functions:
    • get_the_post_thumbnail($recent[‘ID’], ‘thumbnail’) to return the posts thumbnail image.
    • get_permalink($recent[“ID”]) returns the posts ID number for linking to.
    • $recent[“post_title”] returns the posts title.
    • mysql2date(‘j M Y’, $recent[“post_date”]) returns the posts datetime and displays it in a format of choice.
    • substr($recent[“post_content”], 0 , 350) returns the posts content, but limits it to 350 characters. this gives us the “summary”.
  • The $output variable (that contains all our generated HTML code) is returned

Thats all the hard stuff done. Now to make it work, we just need to register our shortcode:

//Add the shortcode trigger 
add_shortcode( 'adamrob-recentposts', 'adamrob_recentposts_fc' );

This line of code tells WordPress that when it sees the code “adamrob-recentposts” in any of its pages/post, it should execute the function “adamrobrecent_posts_fc”

Activating the plug-in

Now you have completed the code required, we need to enable the plug-in.

In your WordPress dashboard, click the plug-in link. In your list of plug-ins, you will see a new entry for the code you just created. Go ahead and activate it.

Your plug-in is now active, so the final piece of the jigsaw is to find a page where you wish to display the list, and copy in the shortcode

[adamrob-recentposts noposts="3" blogpageid="81"]

In-Line code

Another way to accomplish the exact same task is to code it ‘in-line’ in your template. A simple example is to put the code into ‘footer.php’ so the summary always shows on every page that shows the templates footer.

Using this method you have greater control over styling, as you know exactly where it is going to go, and how you will want it to look.

Implementation is arguably simpler too. Just copy the following code where you wish the summary to be displayed:

<!--
** Recent Posts display on page
** 19JUN2014 by adamrob.co.uk
-->
<!-- Start recent posts implentation -->
<div id="outer" style="width:100%; text-align:center; margin: 15px 0px 15px 0px;">
    <h2><a href="<?php echo get_page_link(81); ?>" title="Visit the blog">From The Blog</a></h2>
    <?php
        $args = array( 'numberposts' => '3' );
        $recent_posts = wp_get_recent_posts( $args );
        foreach( $recent_posts as $recent ){?>
            <div class="blog" style="width:50%; margin: 0px 0px 0px 0px; display: inline-block;">
                <hr />
                <div id="img" style="float:left; margin-right:15px;">
                    <?php echo get_the_post_thumbnail($recent['ID'], 'thumbnail'); ?>
                </div>
                <p><strong>
                    <?php echo '<a href="' . get_permalink($recent["ID"]) . '" title=' . $recent["post_title"] . ' >' . $recent["post_title"] . '</a> '; ?>
                </strong></p>
                <p><?php echo mysql2date('j M Y', $recent["post_date"]); ?></p>
 
                <?php echo '' . substr($recent["post_content"], 0 , 250) . '...'; ?>
                <span class="link"><?php echo '<a href="' . get_permalink($recent["ID"]) . '" title="Read More '.$recent["post_title"].'" >' . 'Read More'.'</a> '; ?></span>
            </div>
    <?php } ?>
</div>
<!-- End recent posts implementation -->

Those with a keen eye will see the above code is very similar to the code in the shortcode section. That’s because it is the same, with just a few subtle differences. The code will generate the exact same output, but the way it is generated is slightly different. The difference here is that the code is HTML and not PHP. The PHP functions are performed using in-line PHP commands, rather than the shortcode sample that is entirely PHP with the HTML encased into an output variable.

Conclusion

Both ways are just different implementations of the same code. Which way you go entirely depends on your needs. You may prefer the greater control of embedding it into your template, or prefer the flexibility of having it as a shortcode.

The code featured in this article is free to use providing you link back to my site.

Edit – 20SEP2014

It was brought to our attention that the above code will actual show all the most recent blog posts… even if they have not yet been published! The last thing you would want is to show draft posts on your published site.

There is a simple fix however, simply add the post status parameter to the wp_get_recent_posts function:

$args = array(  'numberposts' => '3',
        'post_status' => 'publish' );
$recent_posts = wp_get_recent_posts( $args );

There are a raft of other options availble to. Check out the official documentation for more info.

CategoriesCorona SDKLUA

Authenticating Saved Data in Apps

Cheating is becoming a bigger and bigger problem for developers that arent authenticating their saved data. You only have to look at the leader boards for the ‘Flappy Bird’ game and you will find hundreds of users on 999999. It’s near impossible to actually achieve that in the game, but users can easily access the files used to save this data and modify it to meet their needs.


It’s not only high scores that are an issue. Developers need a way of keeping track of in-app purchases, and this is often saved on the device in the form of JSON or SQL files. This obviously opens the door up to users activating features or items without paying for them.

There is however, a simple solution to check if the data being saved, then loaded back into the app is genuine or not. This is in the form of a checksum. A simple calculation using the data you want to save, a random key, and the resultant. Simply calculate a checksum at data save and store the result with the saved data. When it comes to load the data back in, perform the same calculation and ensure that the result matches the result that was saved.

Authenticating Data

The following steps are used to save data with a checksum:

  • Save the score.
    You want to save a high score value to the phone. Save the value to a JSON file.
  • Create a random key.
    This random key can be generated however you wish. This should also be saved to the JSON file
  • Perform the checksum calculation.
    The checksum can be any calculation or formula that you like. The more complicated the formula the harder it will be to crack
    This example uses MOD instruction. In lua code that is achieved by
    a % b
    So in this instance it would be
    High score value % Random Key
  • Save the resultant to the JSON file.

Now when you are ready to load the data back into the app, use the following steps:

  • Load JSON File.
    Load the JSON file back into the app.
  • Perform the checksum calculation.
    Re-perform the checksum calculation made in the saving procedure, using the values you have loaded in from the JSON file.
  • Check the checksums match.
    Now check if the resultant from the calculation matches the resultant saved in the JSON file. If it matches everything is great. If it is different, you know that one of the values in the JSON file has been modified. At this point you can get the app to reset a score or display a message etc.

Thats all there is to it. You can expand it a bit more to incorporate more saved fields, or even a different calculation, but that is the basic principle of creating a checksum.

Example

In the example below, I will integrate a checksum for a high score. The app will automatically check the checksum when you load the data, if it finds the checksum is wrong it will reset the players score. The following example is written in Lua for Corona SDK; however the same principle can be used in any SDK or platform. Likewise the example below uses a JSON file for saving data, this will however work with any system including mySQL.

To make everything simple, I will create a function for saving data, and a function to load data. That way whenever we want to save or load data we just call the corresponding function.

1. Create a save data function

local function saveGameSettings() 
    --Check game settings actually exist 
    if (gameGlobalData.gameSettings ~= nil) then 
        --Create the unique key value 
        --This can be anything. In this example i am using a random number
        gameGlobalData.gameSettings.key = math.random(30); 
        --Create the checksum
        gameGlobalData.gameSettings.checksum = gameGlobalData.gameSettings.HighScore % gameGlobalData.gameSettings.key
        --Save the settings using your own function.
        loadsave.saveTable(gameGlobalData.gameSettings, "gamesettings.json"); 
    end 
end

This function does the following:

  1. Checks the gamesettings table exists
  2. Generates a random key. This can be generated however you wish.
  3. Create the checksum by performing the calculation
  4. Saves the gamesettings table back out to a JSON file using an external function. This should be replaced with your own function

2. Create a load data function

To make life simple, create a single function that will load all the data into the application typically on app start up:

local function getGameSettings()
    --Load the data in from a JSON file using you own json reading function 
    gameGlobalData.gameSettings = loadsave.loadTable("gamesettings.json")
    --Now Check if the settings were loaded OK 
    if( gameGlobalData.gameSettings == nil ) then 
        --There are no settings present. 
        --This is the first time the user has launched the game 
        --Create the default settings 
        gameGlobalData.gameSettings = {} 
        gameGlobalData.gameSettings.HighScore = 0 
        gameGlobalData.gameSettings.key = 0 
        gameGlobalData.gameSettings.checksum = 0 
        --Save the settings 
        gameGlobalData.gameSettingsSaveFC(); 
    end 

    --Perform the checksum calculation 
    local calcChecksum = gameGlobalData.gameSettings.HighScore % gameGlobalData.gameSettings.key 
    --Check if any tampering with the score has taken place 
    if (calcChecksum ~= gameGlobalData.gameSettings.checksum) then 
        --Checksums do not match!! 
        native.showAlert( "High Score Error", "It appears there is something not quite right with your saved high score, so we have reset it.", { "OK" } ) 
        --Reset score 
        gameGlobalData.gameSettings.HighScore=0 
        --Save the new score 
        gameGlobalData.gameSettingsSaveFC(); 
    end 
end

This function does the following:

  1. Loads the JSON saved data file into a global variable. (You will need your own function to load the json into a Lua table)
  2. Checks to make sure data was loaded. If not, default data will be stored, then saved. In this default data we create the high score variable, and also the key and checksum.
  3. No we have data in our global variables, we need to check the integrity of the data.

Conclusion

The above method is a simple and easy way to add a layer of security to your saved data. There are also other ways such as encryption. However, as it has been pointed out, as with anything, the protection is defeat-able if the end user goes to as much trouble as to decompile the source code to reveal your formula, or cipher keys. So, although this method is perfectly fine for saving high scores and IAP tracking, you wouldn’t want to use it (in this form) with highly sensitive data.

CategoriesSCADAAutomationCodeInTouch

InTouch Word Wrap Function

With this code you can create multiple lines of text in InTouch using one input string.
The code is defined as a quick function, and can be called from within a code block, or on an animation event.
Simply pass in the text string to split, the number of characters per line, and which line you want to return.

Example

You have a text string that is 50 characters long, and you want to display it over 3 lines on a screen, but are limited to 20 characters per line.
Draw 3 lines of text on the screen.
Animate the text as follows:

  • Text 1: string input animation: Call SplitStringIntoLines(‘*YOUR STRING TAG*’, 20, 1)
  • Text 2: string input animation: Call SplitStringIntoLines(‘*YOUR STRING TAG*’, 20, 2)
  • Text 3: string input animation: Call SplitStringIntoLines(‘*YOUR STRING TAG*’, 20, 3)

Code

The following should be placed in a quick function called “SplitStringIntoLines”

{**********************************************************
*** Split the input string into a specified line
**
** adamrob.co.uk
** 5FEB2014 1428
**
** Inputs:
** sText = The text to split to multiple lines
** iLineLength = The amount of characters allowed per line
** iLineNumber = The line number to return
**
**********************************************************}
 
DIM initialString AS MESSAGE;
DIM finalString AS MESSAGE;
DIM nextString AS MESSAGE;
DIM currIndex AS INTEGER;
DIM lastIndex AS INTEGER;
DIM bDone AS INTEGER;
DIM iIndex AS INTEGER;
 
{** Set default next string value}
nextString = sText;
 
{**Check if the string is greater than max per line}
IF StringLen( sText ) > iLineLength THEN
 
    {** Loop around all lines}
    FOR iIndex=1 TO iLineNumber
 
        {** Check if its line 1}
        IF iIndex == 1 THEN
 
            {* Reset vars}
            currIndex=0;
            lastIndex=0;
            bDone=0;
 
            {** Loop through remaining text}
            FOR bDone = 0 TO iLineLength
 
                currIndex=currIndex+1;
                currIndex=StringInString(sText, " ", currIndex, 0);
 
                IF currIndex >= iLineLength THEN
                    initialString=StringLeft(sText,lastIndex);
                    EXIT FOR;
                ELSE
                    lastIndex=currIndex;
                ENDIF;
            NEXT;
 
            {** Check for a value. StringInString will loop around the string.
            ** So if there is no value assume we have reached the end of the string}
            IF (initialString == "") THEN
                initialString = sText;
            ENDIF;
 
        ELSE
 
            IF StringLen(nextString) - StringLen(initialString) > 0 THEN
                {** Build up the remaining string}
                nextString = StringRight(nextString,StringLen(nextString) - StringLen(initialString));
                initialString="";
                {* Reset vars}
                currIndex=0;
                lastIndex=0;
                bDone=0;
 
                {** Loop through remaining text}
                FOR bDone = 0 TO iLineLength
 
                    currIndex=currIndex+1;
                    currIndex=StringInString(nextString, " ", currIndex, 0);
 
                    IF currIndex >= iLineLength THEN
                        initialString=StringLeft(nextString,lastIndex);
                        EXIT FOR;
                    ELSE
                        lastIndex=currIndex;
                    ENDIF;
                NEXT;
 
                {** Check for a value. StringInString will loop around the string.
                ** So if there is no value assume we have reached the end of the string}
                IF (initialString == "") THEN
                    initialString = nextString;
                ENDIF;
 
            ELSE
                initialString="";
                EXIT FOR;
            ENDIF;
        ENDIF;
 
        {** Check if the current line is the line we need}
        IF iIndex == iLineNumber THEN
 
            finalString=initialString;
            EXIT FOR;
 
        ENDIF;
 
    NEXT;
 
ELSE
 
    IF iLineNumber == 1 THEN
        finalString=sText;
    ENDIF;
 
ENDIF;
 
RETURN finalString;

The arguments are:

  • sText as Message
  • iLineLength as Integer
  • iLineNumber as integer