Make Gadgets Work

On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date. I was convinced that it will be possible to achieve this with minor tweaks to original logic and so I only concentrated on original code and how to tweak it to achieve the result. It is very likely that there might exist a more elegant solution but frankly I did not have a usecase I did find a usecase afterall - Clue was in redefining the problem description for this. Presented below is the function that with my minimal testing seems to give correct results. Feel free to try it and as always any feedback is welcome. :) Problem Description: Find out the WorkDay date by when a ticket must be closed given: Country of site for which incident was logged. Time and Date when Incident was logged. Elapsed working hours in decimal. (Ten and a half hours as 10.5 and so on) Opening time of the site for which the incident was logged. Closing Time of the site for which incident was logged. SLA Type - Is the output date to be based purely on number of hours or based on SLA in days (eg: Next Business Day). Assumption: It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends. Example of problem Let's say an incident was logged on "Friday 23rd Feb 2018 at 15:00" for a site in the "UK" which opens between 08:00 to 16:00. Now if the SLA is purely based on hours i.e. Tickets must be closed withing 16 hours then for the above incident function should calculate the closing date as: 2018-02-27 15:00 = 1 hour on Friday, then skip Saturday and Sunday, 8 hours on Monday and 7 hours on Tuesday the 27th of Feb 2018. However, if the SLA is based on number of days, then 16 hours will translate to an SLA of next business day hence in this case function should return 2018-02-26 16:00 which is a Monday end of day and the next business day for this incident. The use case really is in creating a report that shows the target closed date based on SLA. Specifically Next Business Day SLA, which isn't direct hours calculation and is really always the next working day but based on cut-off time when the call is logged. So a call logged on a working day 1 after cut-off will be counted as logged on working day 2 and hence will need to be closed by working day 3 whereas a call logged within cut-off of working day 1 will need to be closed by close of business working day 2. This is turn can allow for a comparison between Target Closed Date from this function and Actual Closed Date to show SLA failures where Actual Closed Date is greater than Target Closed Date. Pre-Requisites: Follow from previous post. Function code and explanation CREATE DEFINER=`ankit`@`%` FUNCTION `get_closed_date_given_start_time`( `param_country` VARCHAR(20), `assigneddatetime` VARCHAR(20), `param_elapsedhours` VARCHAR(10), `starttime` VARCHAR(20), `endtime` VARCHAR(20), `sla_type` VARCHAR(10) ) RETURNS TEXT LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN Set @starttime = starttime; Set @endtime = endtime; Select time_to_sec(timediff(@endtime,@starttime))/3600 into @maxhoursaday; Set @assigneddate = assigneddatetime; Set @timecount = param_elapsedhours; Set @timevar1 = @assigneddate; Set @nextdate = @assigneddate; Set @timevar2 = null; Set @param_country = param_country; ############ Check if the assigned time was before the starttime or closed time was after the endtime provided ############# Set @checkstart = null; Set @checkend = null; Set @slatype = sla_type; Select CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@starttime), CONCAT(SUBSTRING_INDEX(@assigneddate, ' ', 1), ' ',@endtime) into @checkstart, @checkend; if (@slatype = 'H') then if (@assigneddate < @checkstart) then SET @nextdate = @checkstart; end if; else if (@assigneddate < @checkend) then SET @nextdate = @checkstart; end if; end if; Set @count = @timecount; while @count>0 do select weekday(@nextdate) into @weekday; # Assign the weekday value to @weekday. Weekday returns o for Monday, 2 for Tuesday ...5 for Saturday and 6 for Sunday Select sum(if(date_format(holiday_date,'%Y-%m-%d') = substring_index(@nextdate,' ',1),1,0)) from holiday_table where Country_codes = 'ALL' or instr(Country_codes,@param_country)>0 into @holidayflag; #Check if the date stored in nextdate (which is assigneddate on first run of while loop) is a holiday and set the holiday flag if ( @weekday<5 and @holidayflag=0) then #Proceed if the date in nextdate variable is neither weekend nor a holiday if (@count = @timecount) then #Check if it is first run.ie. if nextdate is assigneddate Set @timevar1 = @nextdate; #assign assigndate to variable timevar1 SELECT CONCAT(SUBSTRING_INDEX(@nextdate, ' ', 1), ' ',@endtime) INTO @timevar2;#get site closing time on assigned date and store it on to timevar2 else Select CONCAT(substring_index(@nextdate,' ',1),' ',@starttime) into @timevar1; SELECT CONCAT(substring_index(@nextdate,' ',1), ' ', @endtime) INTO @timevar2; end if; SELECT LEAST(Greatest(((TIME_TO_SEC(TIMEDIFF(@timevar2, @timevar1))) / 3600),0),@maxhoursaday) INTO @timecounttemp; Set @count = @count - @timecounttemp; end if; if @count > 0 then Select adddate(concat(substring_index(@nextdate,' ',1),' ',@starttime),1) INTO @nextdate; else Select SUBSTRING_INDEX(@nextdate, ' ', 1) INTO @nextdate; end if; end while; Set @finaldate = null; Select concat(@nextdate,' ',substring_index(addtime(@endtime,sec_to_time(3600*@count)),':',2)) INTO @finaldate; RETURN @finaldate; END Calling the function Function will expect 6 parameters and with specific format as explained below: param_country - This is the country code as specified in holiday table assigneddatetime - This must be provided in the format %Y-%m-%d %H-%i-%s. So for our example it will be 2018-02-23 15:00:00 param_elapsedhours - This must be provided in decimal hours format. Bear in mind that if SLA Type is not H, you need to understand the number of working hours in allowed number of days in SLA. So for 8 working hours in a day: Same day SLA will have 8 hours Next Business Day will have 16 hours(2*8) A 2 day SLA will have 24 hours and so on starttime - This must be in the format %H:%i. So for our example it will be 08:00 endtime - This must be in the format %H:%i. So for our example it will be 16:00 sla_type - This must be 'H' if the sla is based on hours or anything else (say 'D') if SLA is based on days. The call for this function will be as below: For hours SLA: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','H'); This will give an output of 2018-02-27 15:00 For Next Business Day SLA: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','16','08:00','16:00','D'); This will give an output of 2018-02-26 16:00 For just calculating next working day date given number of hours: Select get_closed_date_given_start_time('ALL','2018-02-23 15:00:00','10.5','08:00','16:00','H'); This will give an output of 2018-02-27 09:30

MySQL Function to calculate closing WorkDay date given elapsed working time

On my post MySQL function to calculate elapsed working time I was asked in comments if the assumptions can be reversed such that given start date, starting time and closing time of site and the elapsed working hours, function should return the closed date. I was convinced that it will
I have found the recent ghost upgrades quite painless but there have been few hiccups for last two times so I kept a record of what helped and it is as listed below: General Update should be as simple as: cd /var/www/html/ghost/ ghost update If after this step, there are any errors or an indication to update ghost-cli, following command should be used. sudo npm install -g ghost-cli After this if there are issues accessing the blog over internet, we may need to do a bit of checks. Logical sequence is to first check that access for all folders is right and proper. If it needs to be updated, command to be used is: cd ghost sudo chown -R <your username>:www-data . cd content/ sudo chown -R ghost:ghost images/ If there are any errors when starting ghost, following command is indicated by the ghost-cli and it does help. ghost setup linux-user systemd If still accessing the blog is an issue following command will list the ghost log file: ghost log If there are database migration errors on log file, following commands may help: sudo npm install -g knex-migrator ghost setup migrate ghost setup migrate does work very well and must be remembered. If you have digitalocean setup such as mine, memory can be an issue you may want to restart the virtual machine and reload the swap files. sudo reboot now sudo swapon /swapfile1 sudo swapon /swapfile2 If update if failing with the message not enough memory try running the following: ghost update --no-check-mem If there are still issues accessing the blog with error 503, check the apache logs: sudo nano /var/log/apache2/error.log If issue is with accessing the upstream ghost server. Try changing the port on ghost config and updating the apache conf files. ##Change Directory to ghost install cd /var/www/html/ghost ##Stop the ghost server ghost stop ##Change port to another number nano config.production.json ##Change directory apache2 server cd /etc/apache2/sites-available/ ##Open the ghost.conf file and change the localhost port to same number that was changed in config.production.json sudo nano ghost.conf ##Open the ghost-le-ssl.conf file and change the localhost port to same number that was changed in config.production.json sudo nano ghost-le-ssl.conf ##Disable and enable the conf files on apache. sudo a2dissite ghost.conf ghost-le-ssl.conf sudo a2ensite ghost-le-ssl.conf ghost.conf sudo service apache2 reload ##Finally change directory to your ghost install and start ghost server. cd /var/www/html/ghost ghost start

Ghost Upgrade errors and fixes (1.19.x)

I have found the recent ghost upgrades quite painless but there have been few hiccups for last two times so I kept a record of what helped and it is as listed below: General Update should be as simple as: cd /var/www/html/ghost/ ghost update If after this
A friend of mine today had an issue. He had created a template for some really complex calculations and to ensure he does not mess up with the forumlae by mistake he had password protected sheets and cells. He had done this back in 2012 and now he wanted change something in there but he had forgotten the password so he asked me if I can help. Now I do not know much about how it could be done on windows or on excel but I knew a small trick on LibreOffice so I asked him to send me that excel file by email. I then took following steps: Opened the excel file Locked.xls in LibreCalc. Saved it as an Locked.ods file. From file browser, right click on newly saved Locked.ods file -> Open With -> Archive Manager as shown below. Now open the content.xml Find table:protected="true" and Replace All with table:protected="false" Save the xml file. Now open the Locked.ods in LibreCalc and save it as Unlocked.xlsx This should do the trick and unlock all password protected sheets and cells to be freely modified.

Unprotect Sheets in Libre Calc, Excel

A friend of mine today had an issue. He had created a template for some really complex calculations and to ensure he does not mess up with the forumlae by mistake he had password protected sheets and cells. He had done this back in 2012 and now he wanted change
Right then, the Ghost V1.0 was out a while back and they made Ghost 0.11.x an LTS so I was not in any rush to upgrade too. I have not had much time to sort this out for a while and two days back when I finally came around to check how to upgrade, my first moment of concern was that officially supported stack is for NGINX. I have moved my blog to the Apache Stack on DigitalOcean and while on my sandbox environment I still have NGINX, that is not a place I want to host my blog from. Anyhoo, I realised soon enough that while not officially supported it s easy to bypass the restrictions so I went ahead. The upgrade itself couldn't have been simpler considering the major version bump. The answer to the question "Was it worth it?" is something we will have to wait and see although I am liking what I see except for the initial hiccups. EDITED AFTER THE POST Boy oh boy - just after I finished this post I saw the latest version of Ghost V1.12 is out and it was such a painless process compared to past. Just a simple command 'ghost update' and job done. That itself makes this whole pain kind of worth it. OK so the steps I took are as below: Backup Download and Install Setup Wizard Configure Apache Restore Tweak Backup We will take the back-up from front end for all the posts and we will also backup on the server the entire directory where old instance of the blog is residing. To take backup of all the content and download it in a json file, open your ghost site on a browser, navidate to "Settings" and then click on "Export". Next for the backup of folder on the server itself. To do this issue the following commands on the terminal. #Updateand upgrade the OS repo sudo apt-get update sudo apt-get upgrade #Stop the ghost server pm2 stop Ghost "assuming Ghost is the pm2 id for the site" # Change directory to web-server root cd /var/www/html/"path to your ghost directory say 'ghost' " # Create a new directory for backup sudo mkdir old_ghost_bkp sudo mv ghost old_ghost_bkp # Recreate the ghost directory sudo mkdir ghost cd ghost # Give right privileges to the new directory sudo chown -R <your username>:www-data . Download and Install As we are already in the right directory lets get on with installing the latest version of Ghost using npm. sudo npm install -g ghost-cli #Make sure you are in the directory where new ghost is to be installed. #If you have followed all commands so far, you will already be in #required the directory ghost install It is at this point that you will have to deviate from official guide if you have Apache instead of NGINX. You will be prompted by the installer that it could not find NGINX and do you still want to continue. Default is "No" so make sure you enter "Y" and then press enter. For me rest of the install went smoothly. Setup Wizard Immediately after the install is complete, you will be presented with following questions: Please note that if you have configured SSL using LetsEncrypt as explained in previous posts on this blog then even if you are using https, the answer to blog url must be the with http and not https. For example: I gave http://mgw.dumatics.com and not https://mgw.dumatics.com Enter your blog url: http://your.blog.url Enter your MySQL Hostname: localhost Enter your MySQL Username: root Enter your MySQL Password: "your mysql root password" Enter your Ghost database Name: "a relevant name - for security reasons you may want to keep it different from your blog name" Do you wish to set up Nginx: no Do you wish to set uo Ghost MySQL User: yes Do you wish to set up Systemd: yes Do you want to start Ghost: yes Please do note that the response on line 6 above to "Setup Nginx" must be "no" After the questions are complete you will get a notification You can access you blog at http://your.blog.url. At this point, it is best to see which port is configured by ghost CLI for this installation. you can do so by checking the configuration file like so: nano config.production.json You can change the port if you like but if it is different than the port you originally had for old version of ghost you can either change it here or you need to change Apache conf file in next step. If you do decide to change the port here, then there should be no need to carry out the next step - Configure Apache. Configure Apache Assuming that the port in Ghost config file was 1234, there will be some changes that you will need to make in Apache conf files like so: cd /etc/apache2/sites-available/ sudo nano ghost.conf Now change the port on ProxyPass and ProxyPassReverse to be same as what is in the config.production.json file and save it by pressing Ctrl+x and y.- so for this example it will be changed to 1234 and change will look as below: ProxyRequests off ProxyPass / http://localhost:1234/ ProxyPassReverse / http:/localhost:1234/ Now open the ssl config file for the site using commands below and make the same changes as above. sudo nano ghost-le-ssl.conf TIP: If done using LetsEncrypt, it will be named something like `ghost-le-ssl.conf`. Once the changes are saved, disable and enable the configurations using following commands: sudo a2dissite ghost.conf sudo a2dissite ghost-le-ssl.conf sudo a2ensite ghost.conf sudo a2ensite ghost-le-ssl #Restart the server sudo service apache2 restart Now if you enter you blog url in a browser, you should be presented with vanilla Ghost site. If not, something in server set-up has not worked and you will need to troubleshoot it and fix - luckily for me all worked like a charm. Restore Right, so you are now on the browser looking at the Vanilla Ghost install. First thing you need to do now is create the user with same credentials you had on your old version of ghost. To do this you will first need to enter the admin url for ghost and follow the steps to create your user. Once you are into the admin interface, navigate to "Settings" -> "Labs" and click on "Choose File" button, select the json backup that was exported from your old version of the blog and then click on "Import" button. Now to restore the images from your old blog on the server issue following commands: cd /var/www/html/ghost/content sudo rm images sudo cp /var/www/html/old_ghost_bkp/ghost/content/images /var/www/html/ghost/content/ ### Make the new image directory is writable or image uploads will fail sudo chown -R ghost:ghost images/ ### Restart Ghost cd .. ghost restart 1. The ghost CLI commands like stop, start and restart will require you to be in the directory where ghost is installed. 2. While start and stop commands of ghost specifically ask for sudo credentials, restart command just keeps rotating and hence it is better to issue a command with sudo before you issue `ghost restart`. This is it. Your old blog is now fully restored. Tweak This section is a bit of a pain because there are quite a few things that break with this version. So if you have heavily used html, you will painstakingly need to go through posts and add a new line between markdown and html content for it to be parsed properly or else it will display quite wiered outputs on your blog. If you have used code blocks with syntax highlighting, another change is with older version you would have given three backticks followed by language-sql but now you just need to give three backticks followed by sql. If you have used line numbering using prism.js, it just wont work and you will need to apply changes to your theme the way I did. Without going too much in detail on that, you can get the copy of prism.js, prism-custom-line-number.js, prism.css, prism-line-number.css using the github links for my theme and place them in assets directory of your theme. Then make sure you include them in relevant files where your theme calls the javascripts. Once done, issue the command ghost restart and things should look pretty again. Happy Migrating !!!

Ghost V1.0 Upgrade on Apache stack, related quirks and fixes

Right then, the Ghost V1.0 was out a while back and they made Ghost 0.11.x an LTS so I was not in any rush to upgrade too. I have not had much time to sort this out for a while and two days back when I finally
Install R using following commands: sudo apt-get install r-base libapparmor1 gdebi-core # Check that R is installed R #quit R q() Install Rstudio IDE server cd Downloads/ wget https://download2.rstudio.org/rstudio-server-1.0.136-amd64.deb sudo gdebi rstudio-server-1.0.136-amd64.deb At this point if all goes well you can check the status of rstudio server by issuing the command: sudo systemctl status rstudio-server.service The server is started automatically at port 8787 and can be accessed using <ip_address:8787> in browser of your choice, provided all firewall settings have been taken care of. However, when you open the Rstudio server you will be presented with a logon screen and while you can access this using the users for the machine it is hosted on, it will be prudent to create a lower privilege user as explained in next section. Add User to access the RStudio sudo adduser rstudio Set up SSL and reverse proxy for R-Studio Server Now important thing to note is that community version of Rstudio server does not come with SSL enabled but just to run it on a secure socket layer you don't necessarily need the pro version. By following the steps below, your communication with the server will be on SSL. However to achieve the objective we need to accomplish following steps: Enable modules on Apache to help set up proxy Configure a proxy to control access to RStudio Server Use LetsEncrypt to enable SSL Restrict access to Rstudio server only through proxy Restart both Rstudio and Apache servers Step 1: Enable modules on Apache to help set up proxy There is guidance on how to do this on Rstudio Support. However, there was a bit of hair pulling and head scratching involved to get all the steps above work together so stick with me but keep that link in back pocket for variations or when you are stuck. With head scratching and hair pulling I mean that I encountered errors such as these - AH01102 error reading status line from remote server, Rstudio Proxy redirect changing the URL to localhost and many others which can be avoided by following steps as explained below. Anyway so we need to enable mod_proxy and mod_proxy_wstunnel modules. As Apache is already installed and mod_proxy already enabled I did not have to install the module itself, but if it needs to be done the commands are: sudo apt-get install libapache2-mod-proxy-html sudo apt-get install libxml2-dev Issuing the following commands should enable the relevant modules: sudo a2enmod proxy sudo a2enmod proxy_http sudo a2enmod proxy_wstunnel Step 2: Configure a proxy to control access to RStudio Server # Change directory to sites-available cd /etc/apache2/sites-available # create a rstudio conf file sudo nano rstudio.conf Paste the following in the conf file but make sure to change details relevant to your set-up for each entry (line numbers 2, 3, 4, 15 and 16 below): <VirtualHost *:80> ServerAdmin user@yoursite.com ServerName yoursite.com ServerAlias whatever.yoursite.com #Specify path for Logs ErrorLog ${APACHE_LOG_DIR}/error.log CustomLog ${APACHE_LOG_DIR}/access.log combined RewriteEngine on #Rewrite the url supplied to ensure https is applied RewriteCond %{SERVER_NAME} =yoursite.com [OR] RewriteCond %{SERVER_NAME} =whatever.yoursite.com RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,QSA,R=permanent] # Following lines should open rstudio directly from the url RewriteCond %{HTTP:Upgrade} =websocket RewriteRule /(.*) ws://localhost:8787/$1 [P,L] RewriteCond %{HTTP:Upgrade} !=websocket RewriteRule /(.*) http://localhost:8787/$1 [P,L] ProxyPass / http://localhost:8787/ ProxyPassReverse / http://localhost:8787/ </VirtualHost> # vim: syntax=apache ts=4 sw=4 sts=4 sr noet Press Ctrl+x and save the file. TIP: If you just want reverse proxy and no SSL, you can just comment out line 15, 16 and 17 in above conf file and you are all set. If you do want to enable SSL, enabling the site with commands below won't probably work just yet and subsequent steps will need to be completed. Anything else Now enable the new site by issuing the commands: sudo a2ensite rstudio.conf sudo service apache2 restart Step 3: Use LetsEncrypt to enable SSL Follow the instructions here for specific usecase but one way or the other using Certbot you will be able to obtain the LetsEncrypt SSL certificate and enable it on your server. Once certbot has completed doing it's thing you would find an additional conf file in /etc/apache2/sites-available named rstudio-le-ssl.conf. It will be pretty much same content as in rstudio.conf with very minor changes. The first line will be listening on 443 instead of 80 and the ssl certificates will be included. Normally you would not need to tweak anything in the resultant file but just for reference the contenst of this file will look as below: <IfModule mod_ssl.c> <VirtualHost *:443> ServerAdmin user@yoursite.com ServerName yoursite.com ServerAlias whatever.yoursite.com ErrorLog ${APACHE_LOG_DIR}/error.log CustomLog ${APACHE_LOG_DIR}/access.log combined RewriteEngine on # Following lines should open rstudio directly from the url RewriteCond %{HTTP:Upgrade} =websocket RewriteRule /(.*) ws://localhost:8787/$1 [P,L] RewriteCond %{HTTP:Upgrade} !=websocket RewriteRule /(.*) http://localhost:8787/$1 [P,L] ProxyPass / http://localhost:8787/ ProxyPassReverse / http://localhost:8787/ SSLCertificateFile /etc/letsencrypt/live/whatever.yoursite.com/fullchain.pem SSLCertificateKeyFile /etc/letsencrypt/live/whatever.yoursite.com/privkey.pem Include /etc/letsencrypt/options-ssl-apache.conf </VirtualHost> # vim: syntax=apache ts=4 sw=4 sts=4 sr noet </IfModule> Restrict access to Rstudio server only through proxy Finally, we want to ensure that access to the Rstudio server is only through the proxy we configured and to do that we just need to specify this in the rstudio server configuration the attribute that tells it to only serve requests from localhost. sudo nano /etc/rstudio/rserver.conf Now on the opened file type www-address=127.0.0.1 and press Ctrl+x and save the file. Restart both Rstudio and Apache servers Finally issue the following commands to restart both the servers: sudo systemctl restart rstudio-server.service sudo service apache2 restart This is it. Now your new Rstudio server is ready to be used through secure socket layer.

Rstudio Server Setup with SSL behind Apache proxy server

Install R using following commands: sudo apt-get install r-base libapparmor1 gdebi-core # Check that R is installed R #quit R q() Install Rstudio IDE server cd Downloads/ wget https://download2.rstudio.org/rstudio-server-1.0.136-amd64.deb sudo gdebi rstudio-server-1.0.136-amd64.deb At this point if all goes well you can