136 lines
5.7 KiB
Bash
136 lines
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}
|