Chris Taylor Developer

Create a table for the bank statement entries

CREATE TABLE `bank_statement_entries` (
  `id` int(11) NOT NULL,
  `cheque_id` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `amount` decimal(13,4) DEFAULT NULL,
  `subcategory` varchar(50) DEFAULT NULL,
  `memo` varchar(100) DEFAULT NULL,
  `my_notes` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SQL script for importing batch of csv statement entries into table `bank_statement_entries`

DELIMITER $$

/* remove any tables created by a previous run */
DROP TABLE IF EXISTS `bank_statement_entries_raw`$$
DROP TABLE IF EXISTS `bank_statement_entries_batch`$$

/* make an empty copy of the master table to put the batch of statements into */
/* but get rid of the id column */
CREATE TABLE `bank_statement_entries_batch` LIKE `bank_statement_entries`$$
ALTER TABLE `bank_statement_entries_batch` DROP `id`$$

/* prepare a table ready for the raw csv import */
CREATE TABLE `bank_statement_entries_raw` (
  `Number` varchar(50) DEFAULT NULL,
  `Date` varchar(100) DEFAULT NULL,
  `Account` varchar(100) DEFAULT NULL,
  `Amount` decimal(13,4) DEFAULT NULL,
  `Subcategory` varchar(50) DEFAULT NULL,
  `Memo` varchar(100) DEFAULT NULL,
  `Count` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (Count)
)$$

/* upload the local csv file */
LOAD DATA LOCAL INFILE '/home/chris/2018-07-31_to_2018-09-03.csv'                        
INTO TABLE `bank_statement_entries_raw`
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS$$

/* populate the batch table */
/* do any transformations here */
INSERT INTO bank_statement_entries_batch (
  cheque_id,
  date,
  amount,
  subcategory,
  memo) 
SELECT 
   `Number`
	,DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%Y-%m-%d')
	,Amount
	,Subcategory
	,Memo
FROM `bank_statement_entries_raw`
ORDER BY `Count` DESC$$

/* now insert the newly prepared batch of statements in to the master table */
INSERT INTO `bank_statement_entries` (
  cheque_id,
  date,
  amount,
  subcategory,
  memo,
  my_notes
)
SELECT 
  cheque_id,
  date,
  amount,
  subcategory,
  memo,
  my_notes
FROM `bank_statement_entries_batch`$$

DELIMITER ;

Getting the basics working first i.e. a simple index.php file inside laravel/public. Install Laravel framework after this setup.

Directory structure:

.vscode/
    └── launch.json
laradock/
laravel/
    └── xdebug.log
    └── public/
            └── index.php

With a fresh install of Laradock
cp env-example .env

In .env
APP_CODE_PATH_HOST=../laravel/
WORKSPACE_INSTALL_XDEBUG=true
PHP_FPM_INSTALL_XDEBUG=true

In laradock/selenium/Dockerfile, use the latest chrome debug image and expose port 5900 for vnc.

FROM selenium/standalone-chrome-debug

LABEL maintainer="Edmund Luong <edmundvmluong@gmail.com>"

EXPOSE 4444
EXPOSE 5900

In laradock/docker-compose.yml add vnc port mapping for selenium service

selenium:
    build: ./selenium
    ports:
    - "${SELENIUM_PORT}:4444"
    - "5900:5900"
    volumes:
    - /dev/shm:/dev/shm
    networks:
    - frontend

laradock/nginx/default.conf needs to be:

server {

    listen 80;
    listen [::]:80;

    # For https
    listen 443 ssl;
    listen [::]:443 ssl ipv6only=on;
    ssl_certificate /etc/nginx/ssl/default.crt;
    ssl_certificate_key /etc/nginx/ssl/default.key;

    server_name localhost;
    root /var/www/public;
    index index.php index.html index.htm;

    location / {
         try_files $uri $uri/ /index.php$is_args$args;
    }

    location ~ \.php$ {
        try_files $uri /index.php =404;
        fastcgi_pass php-upstream;
        fastcgi_index index.php;
        fastcgi_buffers 16 16k;
        fastcgi_buffer_size 32k;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        #fixes timeouts
        fastcgi_read_timeout 600;
        include fastcgi_params;
    }

    location ~ /\.ht {
        deny all;
    }

    location /.well-known/acme-challenge/ {
        root /var/www/letsencrypt/;
        log_not_found off;
    }

    error_log /var/log/nginx/laravel_error.log;
    access_log /var/log/nginx/laravel_access.log;
}

Change permissions on the ssl files
cd laradock/nginx
sudo chown -R chris:root ssl/

.vscode/launch.json

    "version": "0.2.0",
    "configurations": [
        {
            "name": "Listen for XDebug",
            "type": "php",
            "request": "launch",
            "port": 9000,
            "pathMappings": {
                "/var/www": "/home/chris/laravel",
            }
        }
    ]
}

Edit BOTH workspace/xdebug.ini and php-fpm/xdebug.ini to

xdebug.remote_enable=1
xdebug.remote_autostart=1
xdebug.remote_log=/var/www/xdebug.log
xdebug.remote_connect_back=1
xdebug.remote_port=9000
xdebug.idekey=VSCODE

Run the docker project (will also start php-fpm and workspace)
docker-compose up -d nginx mysql

Workspace container might error "…maybe run apt-get update". If so try adding apt-get update && \ to workspace Dockerfile e.g.

RUN if [ ${INSTALL_XDEBUG} = true ]; then \
    apt-get update && \
    # Load the xdebug extension only with phpunit commands
    ...
    ...

Re-build the containers as required
docker-compose build workspace php-fpm

Enter workspace container, for example
docker-compose exec workspace bash

Test http://localhost in a browser and check vscode debugger will stop on a breakpoint in index.php

Install Laravel and bring up all required containers
docker-compose up -d nginx mysql phpmyadmin redis selenium