stock/data_migration.sh

136 regels
5.7 KiB
Bash

#!/bin/bash
DB_SRC_HOST=marian.local.tetalab.org
DB_SRC=/var/www/stock.tetalab.org/stock
DB_DST=tetastock
DB_DST_USER=tetastock
DB_DST_HOST=sonny.local.tetalab.org
PSQL_OPTS="-t -U ${DB_DST_USER} -h ${DB_DST_HOST} -d ${DB_DST}"
function log {
echo -e "\033[93m$@\033[0m"
}
function red {
echo -e "\033[91m$@\033[0m"
}
function out {
echo -e "\033[91m$@\033[0m"
exit 1
}
log "[+] Suppression des données existantes"
psql -t ${PSQL_OPTS} -c "delete from stock_kit_compositions where id > 0;"
psql -t ${PSQL_OPTS} -c "delete from stock_componants where id > 0;"
psql -t ${PSQL_OPTS} -c "delete from stock_kits where id > 0;"
log "[+] Récupération des composants"
IFS_BAK=${IFS}
IFS=$'\n'
for LINE in $(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'select distinct composants.id, composants.groupe, composants.type, composants.taille, composants.couleur, composants.valeur, composants.quantité , composants.emplacement from composants order by composants.groupe;'")
do
IFS=${IFS_BAK}
COMP_ID=$(echo ${LINE} | awk -F'|' '{print $1}')
RAND=$(head -n40 /dev/urandom | md5sum | cut -d' ' -f1)
REF=$(echo ${LINE} | awk -F'|' '{print $2}')
REF=$(echo "${REF:0:3}-${RAND:0:5}" | tr [a-z] [A-Z] | sed 's/É/E/')
GROUP=$(echo ${LINE} | awk -F'|' '{print $2}' | sed 's/^ *//;s/ *$//')
TYPE=$(echo ${LINE} | awk -F'|' '{print $3}' | sed 's/^ *//;s/ *$//')
SIZE=$(echo ${LINE} | awk -F'|' '{print $4}' | sed 's/^ *//;s/ *$//')
COLOR=$(echo ${LINE} | awk -F'|' '{print $5}' | sed 's/^ *//;s/ *$//')
if [ "${COLOR}" == "0" ]; then
COLOR=''
fi
VALUE=$(echo ${LINE} | awk -F'|' '{print $6}' | sed 's/^ *//;s/ *$//')
QUANTITY=$(echo ${LINE} | awk -F'|' '{print $7}' | sed 's/^ *//;s/ *$//')
DESIGNATION=$(echo "${GROUP} ${TYPE} ${SIZE} ${COLOR} ${VALUE}" | sed 's/^ *//;s/ *$//;s/ +*/ /g;s/ */ /g')
if [ ${#QUANTITY} -eq 0 ]; then
QUANTITY=0
fi
PLACE=$(echo ${LINE} | awk -F'|' '{print $8}')
log " [+] Vérification de l'existence d'un doublon avec ${DESIGNATION}"
SQLREQ="select id from stock_componants where designation='${DESIGNATION}';"
EXIST=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
if [ ! ${#EXIST} -eq 0 ]; then
red " => Composant ${EXIST} a déjà une désignation égale à ${DESIGNATION}"
continue
fi
log " [+] Enregistrement du nouveau composant: ${REF} / ${DESIGNATION}"
SQLREQ="insert into stock_componants (reference, designation, place, quantity) values ('${REF}', '${DESIGNATION}', '${PLACE}', ${QUANTITY});"
psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
IFS=$'\n'
done
log "[+] Récupération des ID des kits"
for KIT_ID in $(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'select distinct id from kit;'")
do
IFS=${IFS_BAK}
# On recupere le kit
log " [+] Récupération du nom pour KIT_ID: ${KIT_ID}"
SQLREQ="select description from kit where ID=${KIT_ID};"
NAME=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
if [ ${#NAME} -lt 1 ]; then
log "[-] Trace: ${SQLREQ}"
out "Nom introuvable pour ${KIT_ID}"
fi
DESIGNATION=${NAME}
# On cree le kit
log " [+] Enregistrement du kit ${NAME} / ${DESIGNATION}"
SQLREQ="insert into stock_kits (name, designation) values ('${NAME}', '${DESIGNATION}');"
psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
log " [+] Récupération du nouvel ID du kit"
# On recupere l'ID du nouveau kit
SQLREQ="select id from stock_kits where name='${NAME}' and designation='${DESIGNATION}';"
NKIT_ID=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
if [ ${#NKIT_ID} -lt 1 ]; then
log "[-] Trace: ${SQLREQ}"
out "Pas de NKIT_ID pour ${NAME}"
fi
log " => ${NKIT_ID}"
# On recupere les composants du kit
log " [+] Récupération de la liste des composants du kit"
SQLREQ="select id_composant, quantite from composants_kit where id_kit='${KIT_ID}';"
RECS=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
IFS=$'\n'
for REC in ${RECS}
do
IFS=${IFS_BAK}
COMP_ID=$(echo ${REC} | awk -F'|' '{print $1}')
QUANTITY=$(echo ${REC} | awk -F'|' '{print $2}')
IFS=${IFS_BAK}
log " [+] Récupération du composant: ${COMP_ID}"
SQLREQ="select composants.id, composants.groupe, composants.type, composants.taille, composants.couleur, composants.valeur, composants.quantité , composants.emplacement from composants where id=${COMP_ID} order by composants.groupe;"
COMP=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
if [ ${#COMP} -lt 1 ]; then
red "[-] Trace: ${SQLREQ}"
red "Pas de composant avec ID ${COMP_ID}"
continue
fi
GROUP=$(echo ${COMP} | awk -F'|' '{print $2}' | sed 's/^ *//;s/ *$//')
TYPE=$(echo ${COMP} | awk -F'|' '{print $3}' | sed 's/^ *//;s/ *$//')
SIZE=$(echo ${COMP} | awk -F'|' '{print $4}' | sed 's/^ *//;s/ *$//')
COLOR=$(echo ${COMP} | awk -F'|' '{print $5}' | sed 's/^ *//;s/ *$//')
if [ "${COLOR}" == "0" ]; then
COLOR=''
fi
VALUE=$(echo ${COMP} | awk -F'|' '{print $6}' | sed 's/^ *//;s/ *$//')
DESIGNATION=$(echo "${GROUP} ${TYPE} ${SIZE} ${COLOR} ${VALUE}" | sed 's/^ *//;s/ *$//;s/ +*/ /g;s/ */ /g')
log " [+] Récupération du nouvel ID du composant"
SQLREQ="select ID from stock_componants where designation='${DESIGNATION}';"
NCOMP_ID=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
if [ ${#NCOMP_ID} -lt 1 ]; then
log "[-] Trace: ${SQLREQ}"
out "Pas de composant avec NCOMP_ID ${NCOMP_ID}"
fi
log " [+] ${NCOMP_ID}"
log " [+] Enregistrement du composant ${NCOMP_ID} dans la composition du kit ${NKIT_ID} (q: ${QUANTITY})"
SQLREQ="insert into stock_kit_compositions (kit_id, componant_id, quantity) values (${NKIT_ID}, ${NCOMP_ID}, ${QUANTITY})"
psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
IFS=$'\n'
done
IFS=$'\n'
done
IFS=${IFS_BAK}