L'outil de gestion de stock de composants électroniques du Tetalab.
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

data_migration.sh 5.7KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135
  1. #!/bin/bash
  2. DB_SRC_HOST=marian.local.tetalab.org
  3. DB_SRC=/var/www/stock.tetalab.org/stock
  4. DB_DST=tetastock
  5. DB_DST_USER=tetastock
  6. DB_DST_HOST=sonny.local.tetalab.org
  7. PSQL_OPTS="-t -U ${DB_DST_USER} -h ${DB_DST_HOST} -d ${DB_DST}"
  8. function log {
  9. echo -e "\033[93m$@\033[0m"
  10. }
  11. function red {
  12. echo -e "\033[91m$@\033[0m"
  13. }
  14. function out {
  15. echo -e "\033[91m$@\033[0m"
  16. exit 1
  17. }
  18. log "[+] Suppression des données existantes"
  19. psql -t ${PSQL_OPTS} -c "delete from stock_kit_compositions where id > 0;"
  20. psql -t ${PSQL_OPTS} -c "delete from stock_componants where id > 0;"
  21. psql -t ${PSQL_OPTS} -c "delete from stock_kits where id > 0;"
  22. log "[+] Récupération des composants"
  23. IFS_BAK=${IFS}
  24. IFS=$'\n'
  25. 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;'")
  26. do
  27. IFS=${IFS_BAK}
  28. COMP_ID=$(echo ${LINE} | awk -F'|' '{print $1}')
  29. RAND=$(head -n40 /dev/urandom | md5sum | cut -d' ' -f1)
  30. REF=$(echo ${LINE} | awk -F'|' '{print $2}')
  31. REF=$(echo "${REF:0:3}-${RAND:0:5}" | tr [a-z] [A-Z] | sed 's/É/E/')
  32. GROUP=$(echo ${LINE} | awk -F'|' '{print $2}' | sed 's/^ *//;s/ *$//')
  33. TYPE=$(echo ${LINE} | awk -F'|' '{print $3}' | sed 's/^ *//;s/ *$//')
  34. SIZE=$(echo ${LINE} | awk -F'|' '{print $4}' | sed 's/^ *//;s/ *$//')
  35. COLOR=$(echo ${LINE} | awk -F'|' '{print $5}' | sed 's/^ *//;s/ *$//')
  36. if [ "${COLOR}" == "0" ]; then
  37. COLOR=''
  38. fi
  39. VALUE=$(echo ${LINE} | awk -F'|' '{print $6}' | sed 's/^ *//;s/ *$//')
  40. QUANTITY=$(echo ${LINE} | awk -F'|' '{print $7}' | sed 's/^ *//;s/ *$//')
  41. DESIGNATION=$(echo "${GROUP} ${TYPE} ${SIZE} ${COLOR} ${VALUE}" | sed 's/^ *//;s/ *$//;s/ +*/ /g;s/ */ /g')
  42. if [ ${#QUANTITY} -eq 0 ]; then
  43. QUANTITY=0
  44. fi
  45. PLACE=$(echo ${LINE} | awk -F'|' '{print $8}')
  46. log " [+] Vérification de l'existence d'un doublon avec ${DESIGNATION}"
  47. SQLREQ="select id from stock_componants where designation='${DESIGNATION}';"
  48. EXIST=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
  49. if [ ! ${#EXIST} -eq 0 ]; then
  50. red " => Composant ${EXIST} a déjà une désignation égale à ${DESIGNATION}"
  51. continue
  52. fi
  53. log " [+] Enregistrement du nouveau composant: ${REF} / ${DESIGNATION}"
  54. SQLREQ="insert into stock_componants (reference, designation, place, quantity) values ('${REF}', '${DESIGNATION}', '${PLACE}', ${QUANTITY});"
  55. psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
  56. IFS=$'\n'
  57. done
  58. log "[+] Récupération des ID des kits"
  59. for KIT_ID in $(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'select distinct id from kit;'")
  60. do
  61. IFS=${IFS_BAK}
  62. # On recupere le kit
  63. log " [+] Récupération du nom pour KIT_ID: ${KIT_ID}"
  64. SQLREQ="select description from kit where ID=${KIT_ID};"
  65. NAME=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
  66. if [ ${#NAME} -lt 1 ]; then
  67. log "[-] Trace: ${SQLREQ}"
  68. out "Nom introuvable pour ${KIT_ID}"
  69. fi
  70. DESIGNATION=${NAME}
  71. # On cree le kit
  72. log " [+] Enregistrement du kit ${NAME} / ${DESIGNATION}"
  73. SQLREQ="insert into stock_kits (name, designation) values ('${NAME}', '${DESIGNATION}');"
  74. psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
  75. log " [+] Récupération du nouvel ID du kit"
  76. # On recupere l'ID du nouveau kit
  77. SQLREQ="select id from stock_kits where name='${NAME}' and designation='${DESIGNATION}';"
  78. NKIT_ID=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
  79. if [ ${#NKIT_ID} -lt 1 ]; then
  80. log "[-] Trace: ${SQLREQ}"
  81. out "Pas de NKIT_ID pour ${NAME}"
  82. fi
  83. log " => ${NKIT_ID}"
  84. # On recupere les composants du kit
  85. log " [+] Récupération de la liste des composants du kit"
  86. SQLREQ="select id_composant, quantite from composants_kit where id_kit='${KIT_ID}';"
  87. RECS=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
  88. IFS=$'\n'
  89. for REC in ${RECS}
  90. do
  91. IFS=${IFS_BAK}
  92. COMP_ID=$(echo ${REC} | awk -F'|' '{print $1}')
  93. QUANTITY=$(echo ${REC} | awk -F'|' '{print $2}')
  94. IFS=${IFS_BAK}
  95. log " [+] Récupération du composant: ${COMP_ID}"
  96. 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;"
  97. COMP=$(ssh ${DB_SRC_HOST} sqlite3 ${DB_SRC} "'${SQLREQ}'")
  98. if [ ${#COMP} -lt 1 ]; then
  99. red "[-] Trace: ${SQLREQ}"
  100. red "Pas de composant avec ID ${COMP_ID}"
  101. continue
  102. fi
  103. GROUP=$(echo ${COMP} | awk -F'|' '{print $2}' | sed 's/^ *//;s/ *$//')
  104. TYPE=$(echo ${COMP} | awk -F'|' '{print $3}' | sed 's/^ *//;s/ *$//')
  105. SIZE=$(echo ${COMP} | awk -F'|' '{print $4}' | sed 's/^ *//;s/ *$//')
  106. COLOR=$(echo ${COMP} | awk -F'|' '{print $5}' | sed 's/^ *//;s/ *$//')
  107. if [ "${COLOR}" == "0" ]; then
  108. COLOR=''
  109. fi
  110. VALUE=$(echo ${COMP} | awk -F'|' '{print $6}' | sed 's/^ *//;s/ *$//')
  111. DESIGNATION=$(echo "${GROUP} ${TYPE} ${SIZE} ${COLOR} ${VALUE}" | sed 's/^ *//;s/ *$//;s/ +*/ /g;s/ */ /g')
  112. log " [+] Récupération du nouvel ID du composant"
  113. SQLREQ="select ID from stock_componants where designation='${DESIGNATION}';"
  114. NCOMP_ID=$(psql ${PSQL_OPTS} -c "${SQLREQ}")
  115. if [ ${#NCOMP_ID} -lt 1 ]; then
  116. log "[-] Trace: ${SQLREQ}"
  117. out "Pas de composant avec NCOMP_ID ${NCOMP_ID}"
  118. fi
  119. log " [+] ${NCOMP_ID}"
  120. log " [+] Enregistrement du composant ${NCOMP_ID} dans la composition du kit ${NKIT_ID} (q: ${QUANTITY})"
  121. SQLREQ="insert into stock_kit_compositions (kit_id, componant_id, quantity) values (${NKIT_ID}, ${NCOMP_ID}, ${QUANTITY})"
  122. psql ${PSQL_OPTS} -c "${SQLREQ}" || out "${SQLREQ}"
  123. IFS=$'\n'
  124. done
  125. IFS=$'\n'
  126. done
  127. IFS=${IFS_BAK}