PersSRV

VM Config

1. 1GB RAM
2. 200GB disk

CentOS 7 with encrypted disks

Disk Name	Size
/boot	1 GB
swap	2 GB
/	The rest
/opt (optional - encrypted disk)	100 GB

For the Encrypted Disk

In grub on boot Ctrl-e - Remove rhgb Remove rhgb from /boot/grub2/grub.cfg

vi /etc/crypttab
restorecon -r /opt/www/html

If cloned VM

hostname PersSrv.ianculovici.com
vi /etc/sysconfig/network

install the vi enhancements

yum install -y vim-enhanced

Setup Firewall Rules and user permissions

groupadd dev
usermod -a -G dev chris

/etc/sudoers
%dev            ALL=(ALL)       NOPASSWD: ALL

Installing Perl (Needded for VMware Tools) - maybe - tools are now installed directly from CentOS repository

yum install -y perl

yum install -y open-vm-tools # not needed in CentOS 8

Nginx

yum install -y epel-release # not needed in CentOS 8
yum install -y nginx

systemctl start nginx
firewall-cmd --zone=public --add-service=http --permanent
firewall-cmd --zone=public --add-service=https --permanent
firewall-cmd --reload

systemctl enable nginx

Install PHP

yum install -y php php-fpm php-opcache php-cli php-zip

yum install -y unzip

yum install -y yum-utils

vi /etc/php.ini

cgi.fix_pathinfo=0

vi /etc/php-fpm.d/www.conf

listen = /run/php-fpm/www.sock

listen.owner = nginx
listen.group = nginx
listen.mode = 0660

user = nginx
group = nginx

systemctl start php-fpm
systemctl enable php-fpm

Configure PHP with Nginx

vi /etc/nginx/default.d/all.conf

      error_page 404 /404.html;
      location = /40x.html {
          root /usr/share/nginx/html;
      }

  error_page 500 502 503 504 /50x.html;
      location = /50x.html {
          root /usr/share/nginx/html;
      }

  index index.php index.html index.htm;

  location ~ \.php$ {
          try_files $uri =404;
          fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;
          fastcgi_index index.php;
          fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
          include fastcgi_params;
      }

vi /etc/nginx/nginx.conf

Add

  server {
      listen       80 default_server;
      listen       [::]:80 default_server;
      server_name  PersSrv.ianculovici.com;
      root         /opt/www/fin/public;

      # Load configuration files for the default server block.
      include /etc/nginx/default.d/*.conf;

      location / {
              try_files $uri $uri/ /index.php?$query_string;
      }

  }

 server {
      listen       80;
      listen       [::]:80;
      server_name  PersSrv.ianculovici.com;
      root         /opt/www/fin/public;

      # Load configuration files for the default server block.
      include /etc/nginx/default.d/*.conf;

      location / {
              try_files $uri $uri/ /index.php?$query_string;
      }

  }

systemctl restart nginx

PostgreSQL

Install

yum install postgresql-contrib postgresql-server  
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl start postgresql-9.6
systemctl enable postgresql-9.6

Create fin database

su - postgres
psql -c "create user chris with password 'chris1' CREATEDB CREATEROLE SUPERUSER;"
su - chris
psql template1 -c "create database fin"

yum install -y php-pgsql

firewall-cmd --permanent --zone=public --add-port=5432/tcp
firewall-cmd --reload

su - postgres
vi /var/lib/pgsql/data/pg_hba.conf
pg_ctl reload

Replace "ident" with "trust" as in the following line

host    all             all             127.0.0.1/32            md5

Note: The following is not needed if default layout/configuration is used:

Edit /etc/systemd/system/multi-user.target.wants/postgresql-9.4.service

Replace

Environment=PGDATA=/var/lib/pgsql/9.4/data/

with

Environment=PGDATA=/opt/pgsql/9.4/data/

Install Laravel

yum install php-json -y

Install Composer

curl -sS https://getcomposer.org/installer | php
chmod +x composer.phar
mv composer.phar /usr/local/bin/composer
composer -V

Install Laravel and Create New App

yum install php-mbstring php-dom -y

su - chris  
composer global require "laravel/installer"

cd /opt/www
~/.config/composer/vendor/bin/laravel new fin

Edit .env to setup the database

groupadd www-data
usermod -a -G www-data chris 
usermod -a -G www-data nginx
cd fin
chgrp -R www-data storage bootstrap/cache
chmod -R g+w storage bootstrap/cache

Install node-js to compile code

yum install npm -y

CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 

SELECT EXTRACT(MONTH FROM $1)::INTEGER;

$$ LANGUAGE SQL IMMUTABLE;


CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$

SELECT EXTRACT(MONTH FROM $1)::INTEGER;

$$ LANGUAGE SQL STABLE;


CREATE OR REPLACE FUNCTION MONTH(DATE) RETURNS INTEGER
AS $$

SELECT EXTRACT(MONTH FROM $1)::INTEGER;

$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 

SELECT EXTRACT(YEAR FROM $1)::INTEGER;

$$ LANGUAGE SQL IMMUTABLE;


CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$

SELECT EXTRACT(YEAR FROM $1)::INTEGER;

$$ LANGUAGE SQL STABLE;


CREATE OR REPLACE FUNCTION YEAR(DATE) RETURNS INTEGER
AS $$

SELECT EXTRACT(YEAR FROM $1)::INTEGER;

$$ LANGUAGE SQL IMMUTABLE;

SELinux Package

sudo yum -y install checkpolicy
sudo setsebool -P httpd_can_network_connect 1
chcon -R -t httpd_sys_rw_content_t storage

DB

sudo setsebool -P httpd_can_network_connect_db on

Backups

mkdir /opt/BACKUP

Install NFS

# mount -t nfs persnas:/nas/Recovery.Backup/ /opt/BACKUP/
mount /opt/BACKUP/

yum install -y rsync

DB Objects

The scripts project should have the most current scripts. Examples are below.

create table stage(label varchar(32),accountid varchar(32), tx_date date, post_date date, description varchar (128), amount decimal(20,2), category varchar(32));

CREATE TABLE transaction_record(
    id serial primary key,
    label varchar(32),
    accountid char(5),  
    txdate date, 
    postdate date, 
    description varchar (128), 
    amount decimal(20,2), 
    categoryid char(5)
);

INSERT INTO transaction_record (label,accountid, txdate, postdate, description, amount, categoryid) 
SELECT s.label,s.accountid, s.tx_date, s.post_date, s.description, s.amount, s.category from stage s;

CREATE TABLE account(
    id char(5),
    name varchar(128),
    bank_name varchar(128),
    account_type varchar(32),
    account_num varchar(64),
    owner_name varchar(128),
    details varchar(256)
);

INSERT INTO account (id, name, bank_name, account_type, account_num, owner_name)
VALUES
('AMEXA', 'AMEX (Anca)'     , 'American Express'    , '', 'Credit card' , 'Anca'),
('CITIC', 'Citi'            , 'Citi Bank'           , '', 'Credit card' , 'Chris'),
('BFCU' , 'BFCU'            , 'Bethpage FCU'        , '', 'Checking'    , 'Chris'),
('CAP1C', 'CapitalOne'      , 'Capital One'         , '', 'Credit card' , 'Chris'),
('CASH' , 'Cash'            , `                 , `, 'Cash'     , 'Chris & Anca'),
('DISCA', 'Discover (Anca)' , 'Discover Card'       , '', 'Credit card' , 'Anca'),
('CHACK', 'Chase (Check)'   , 'Chase'               , '', 'Checking'    , 'Chris & Anca'),
('CHACC', 'Chase (CC)'      , 'Chase'               , '', 'Credit card' , 'Chris')



copy stage from '/opt/stage/w.csv' with csv delimiter ',' escape '"';

CREATE TABLE category(
    id CHAR(5) primary key,
    name varchar(32),
    longname varchar(128),
    parent integer not null default 0
);

CREATE TABLE ignore_records(accountid char(5), description varchar(128));

COPY ignore_records FROM '/opt/stage/ignore_records.csv' WITH CSV DELIMITER ',' ESCAPE '"';

ignore_records.csv:
BFCU,DISCOVER DC PYMNTS
BFCU,MACYS WEB PYMT
BFCU,EMIGRANTDIRECT - TRANSFER
BFCU,CITI AUTOPAY - PAYMENT
BFCU,CHASE - AUTOPAY
BFCU,AMEX EPayment
CHACC,AUTOMATIC PAYMENT - THANK
AMEXA,AUTOPAY PAYMENT RECEIVED
DISCA,DIRECTPAY FULL BALANCE
CITIC,AUTOPAY


CREATE TABLE category_match(accountid char(5), categoryid char(5), description varchar(128));
COPY category_match FROM '/opt/stage/category_match.csv' WITH CSV DELIMITER ',' ESCAPE '"';
category_match.csv:
--------------------
BFCU,GAS,CPENERGY
BFCU,CHARO,COUNTRYSIDE MINI
BFCU,CHARO,ROMANIAN MEDIA M
BFCU,MORTG,Bank of America - MORTGAGE
BFCU,INCOM,IRS TREAS
BFCU,INCOM,ACTIAN CORPORATI
BFCU,INCOM,Angela Earhart
CHACC,PEST,ECO SHIELD HOUSTON
CHACC,WATER,PEARLAND UTILITIES WEB
CHACC,ENT,CHR*CHRISTIANBOOK.COM
CHACC,?????,Amazon
CHACC,VACAT,SOUTHWES
CHACC,VACAT,GALVESTON SEAWALL
CHACC,VACAT,FRONTIER
CHACC,GROCE,HEB  #
AMEXA,GROCE,HEB #
CHACC,GASOL,HEB GAS
CHACC,PHONH,OOMA
CHACC,ELECT,Reliant
CHACC,CLOTH,ROSS STORES
CHACC,GROCE,WAL-MART
CHACC,HOMEN,HOMEGOODS
CHACC,EZTAG,Harris Co Tollq
AMEXA,CAMNT,AUTO MAX
AMEXA,CAMNT,DISCOUNT TIRE
AMEXA,CLOTH,GYMBOREE
AMEXA,EATOU,FIELDINGS
AMEXA,ENT,BARNES & NOBLE
AMEXA,EATOU,LA MADELEINE
AMEXA,HEALT,CVS/PHARMACY
AMEXA,CLOTH,KOHL'S
AMEXA,EATOU,PIZZA HUT
AMEXA,EATOU,EINSTEIN BROS
AMEXA,HOMEN,HOBBY LOBBY
AMEXA,HOMEN,THE HOME DEPOT 
AMEXA,EATOU,CHICK-FIL-A
AMEXA,EATOU,RED ROBIN
AMEXA,EATOU,TWO GUYS PIZZERIA
AMEXA,EATOU,HUNGRYS
AMEXA,EATOU,OLIVE GARDEN
AMEXA,EATOU,PEI WEI
AMEXA,CLOTH,OLD NAVY
AMEXA,EATOU,IN-N-OUT BURGER
DISCA,GASOL,Gasoline
CITIC,INET,COMCAST OF HOU
CHACK,INCOM,ACTIAN CORPORATI
AMEXA,GROCE,WHOLEFDS
AMEXA,HEALT,TGF PRECISION HAIR
------------------------


common.sql
--------------
\set account_type_val '\` :account_type '\`
\set import_file '\` '/opt/stage/' :load_ym '_' :account_type '.csv' '\`
\set load_table :account_type '_csv'
\set label '\` :load_ym '\`


\i common.sql

CREATE TEMP TABLE :load_table (tx_date varchar(15),dummy1 varchar(15),description varchar(128),cardowner varchar(64), card varchar(20),dummy2 varchar(15),dummy3 varchar(15),amount decimal(20,2),dummy4 varchar(15),dummy5 varchar(15),dummy6 varchar(15),dummy7 varchar(15),dummy8 varchar(15),dummy9 varchar(15),dummy10 varchar(15),dummy11 varchar(15));
COPY :load_table  FROM :import_file WITH CSV DELIMITER ',' ESCAPE '"';

INSERT INTO stage
SELECT :label AS label, :account_type_val AS type, left(csv.tx_date,10)::date AS tx_date, left(csv.tx_date,10)::date AS post_date,csv.description, -coalesce(csv.amount,0) AS amount, m.categoryid as category
FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.description ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table  WHERE description ilike ANY (select description || '%' from ignore_records))

;
\i common.sql

CREATE TEMP TABLE :load_table (Date varchar(15),Check_no varchar(10),Type varchar(20),Description varchar(128),Debit decimal(20,2),Credit decimal(20,2),Balance  decimal(20,2));
copy bfcu_csv from :import_file with csv delimiter ',' escape '"' header;

INSERT INTO stage
SELECT :label AS label, :account_type_val AS type, csv.date::date as tx_date, csv.date::date as post_date, csv.description, coalesce(csv.credit,0) - coalesce(csv.debit,0) AS amount, m.categoryid as category
FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.description ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table WHERE description ilike ANY (select description || '%' from ignore_records))

;
\i common.sql

CREATE TEMP TABLE :load_table (type varchar(20),tx_date varchar(15),post_date varchar(15),description varchar(128),amount decimal(20,2));
COPY :load_table  FROM :import_file WITH CSV DELIMITER ',' ESCAPE '"' HEADER;

INSERT INTO stage
SELECT :label AS label, :account_type_val AS accountid, csv.tx_date::date, csv.post_date::date,csv.description, coalesce(csv.amount,0) AS amount, m.categoryid as category
FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.description ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table  WHERE description ilike ANY (select description || '%' from ignore_records))

;




\i common.sql

CREATE TEMP TABLE :load_table (details varchar(15),tx_date varchar(15),description varchar(128),amount decimal(20,2),type varchar(21),balance decimal(20,2),check_no varchar(8),dummy varchar(1));
COPY :load_table  FROM :import_file WITH CSV DELIMITER ',' ESCAPE '"' HEADER;

INSERT INTO stage
SELECT

  :label AS label,
  :account_type_val AS accountid, csv.tx_date::date AS tx_date, csv.tx_date::date AS post_date, csv.description as description,
  csv.amount, m.categoryid as category

FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.description ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table  WHERE description ilike ANY (select description || '%' from ignore_records))

;



\i common.sql

CREATE TEMP TABLE :load_table (status varchar(15),tx_date varchar(15),description varchar(128),debit varchar(21),credit varchar(21));
COPY :load_table  FROM :import_file WITH CSV DELIMITER ',' ESCAPE '"' HEADER;

INSERT INTO stage
SELECT

  :label as label, :account_type_val AS type, csv.tx_date::date AS tx_date, csv.tx_date::date AS post_date, left(csv.description,-1) as description,
  CASE csv.credit WHEN ` THEN 0 ELSE cast(csv.credit as decimal(20,2)) END - CASE csv.debit WHEN ` THEN 0 ELSE CAST(csv.debit as decimal(20,2)) END AS amount, m.categoryid as category

FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.description ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table  WHERE description ilike ANY (select description || '%' from ignore_records))

;


\i common.sql

CREATE TEMP TABLE :load_table (tx_date varchar(15),post_date varchar(15),description varchar(128),amount decimal(20,2),category varchar(32));
COPY :load_table  FROM :import_file WITH CSV DELIMITER ',' ESCAPE '"' HEADER;

INSERT INTO stage
SELECT :label as label, :account_type_val AS type, right(csv.tx_date,10)::date AS tx_date, csv.post_date::date AS post_date,csv.description, -coalesce(csv.amount,0) AS amount, m.categoryid as category
FROM

  :load_table csv
  LEFT JOIN category_match m ON csv.category ILIKE m.description || '%'

WHERE

  csv.description not in (SELECT description from :load_table  WHERE description ilike ANY (select description || '%' from ignore_records))

;


recreate_categ_ignore.sql
----------------------------
DROP TABLE ignore_records;
DROP TABLE category_match;
CREATE TABLE ignore_records(accountid char(5), description varchar(128));
COPY ignore_records FROM '/opt/stage/ignore_records.csv' WITH CSV DELIMITER ',' ESCAPE '"';
CREATE TABLE category_match(accountid char(5), categoryid char(5), description varchar(128));
COPY category_match FROM '/opt/stage/category_match.csv' WITH CSV DELIMITER ',' ESCAPE '"';






alter sequence transaction_record_id_seq restart 1530;

copy transaction_record(id,label,accountid,txdate,postdate,description,amount,categoryid) to '/tmp/test.csv' csv;
copy transaction_record1 from '/tmp/test.csv' csv;

Other Services

Mail

yum install -y mailx