#!/bin/bash

# depuraCH2016
# Wed Dec 18 14:51:20 MST 2024 
# jorgericarte@asuni.com.mx
# Depuracion de registros anteriores a $LANIO
# Ultimo cambio: Fri 20 Dec 2024 10:25:32 AM MST

# Debug
# set -x

# Var
LANIO="2016"
LEMP="CH"
LDIR="/tmp/"$LEMP
LFIL=$LDIR`date +%Y-%m-%d`".log"
echo $LFIL >$LFIL

echo "Inicial Reindex Database ficnx" >>$LFIL
psql -c "REINDEX DATABASE ficnx ; " -d ficnx

echo "Datos de DB antes depuracion"  >>$LFIL
psql -c "\i /var/www/ficnx/psql/dbsizetables.sql" -d ficnx >>$LFIL

<< Comenta
psql -c " 
SELECT                                           
nspname AS schemaname,relname,reltuples::integer
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r' AND left(relname,2)='fn'
ORDER BY reltuples DESC; " -d ficnx >>$LFIL

psql -c "
SELECT relname, n_tup_ins - n_tup_del as rowcount
FROM pg_stat_all_tables 
WHERE schemaname = 'public' AND left(relname,2)='fn'
ORDER BY rowcount desc;
" -d ficnx >>$LFIL

LTXT=$LDIR`date +%Y-%m-%d`".txt"
psql -c "\copy (SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables WHERE schemaname = 'public' and left(relname,2)='fn' ORDER BY rowcount desc) to '/tmp/tablesficnx.txt' with CSV;" -d ficnx 
Comenta

# Tablas a depurar
echo "fnhmin:kd_fecha
fntcov:fc_fecha
fnsatcfd:cffecha
fnhmcc:hc_fecha
fntkco:fc_fecha
fnsatcan:caaficf
fnertim:et_fecha "  > /tmp/tablasficnx.txt

# conteo de registros antes de eliminar
echo "Conteo antes depuracion"  >>$LFIL
while IFS=: read ltable lfield;
do 
  LROWTOT=$(psql -t -c "SELECT count(*) FROM $ltable " -d ficnx)
  echo $ltable,$lfield,$LROWTOT >>$LFIL
done < /tmp/tablasficnx.txt
echo "**************"  >>$LFIL

while IFS=: read ltable lfield;
do 
  LTIMI=`date +%rH:%M:%S`
  echo "Inica depuracion "$ltable" - "$LTIMI >>$LFIL
  LCOMM=$(psql -c "DELETE FROM $ltable WHERE extract(year from $lfield)<=$LANIO and extract(month from $lfield)<=12" -d ficnx)
  LTIMF=`date +%rH:%M:%S`
  echo "Fin depuracion "$ltable" - "$LTIMF >>$LFIL
  echo "**************"  >>$LFIL
done < /tmp/tablasficnx.txt

# Vacuum
LTIMF=`date +%rH:%M:%S`
echo "Vacuum Database ficnx "$LTIMF >>$LFIL
vacuumdb -f -d ficnx

# conteo de registros despus de eliminar
LTIMF=`date +%rH:%M:%S`
echo "Conteo despues depuracion "$LTIMF  >>$LFIL
while IFS=: read ltable lfield;
do 
  LROWTOT=$(psql -t -c "SELECT count(*) FROM $ltable " -d ficnx)
  echo $ltable,$lfield,$LROWTOT >>$LFIL
done < /tmp/tablasficnx.txt

LTIMF=`date +%rH:%M:%S`
echo "Final Reindex Database ficnx "$LTIMF >>$LFIL
psql -c "REINDEX DATABASE ficnx ; " -d ficnx
LTIMF=`date +%rH:%M:%S`
echo "Datos de DB despues depuracion "$LTIMF  >>$LFIL
psql -c "\i /var/www/ficnx/psql/dbsizetables.sql" -d ficnx >>$LFIL

more $LFIL