cargarBase

#!/usr/bin/tclsh
#Leon Ramos 2015
#SOPORTEYA.MX
#@fulvous

package require Tk
package require csv
package require mysqltcl

#set initial values
set filename ""
set catFilename ""
set rc [catch {exec -- /usr/bin/whoami} sys_usr]
if { $rc != "0" } {
  errorMsg $sys_usr   
}
proc getScriptDir {} {
  set scriptFile [file normalize [info script]]
  set Path [file dirname $scriptFile]
  return $Path
}
set wd [getScriptDir]
set db_h ""
set db_root "jittermysql"
set db_name ""
set db_usr "sistemas"
set db_pass "123qweobp"
set pos_usr ""
set pos_pass ""
set disccount "Deshabilitado"
set category_en "Sin categorías"
set ticket_number_en "Deshabilitado"
set ticket_number "1"
array set csv {}
array set catCsv {}

#Window and container properties
wm title . "Configurador de punto de Venta"
grid [ttk::frame .container -padding "30"] -column 0 -row 0 -sticky nswe
grid columnconfigure . 0 -weight 1; grid rowconfigure . 0 -weight 1

#Creating labels
ttk::label .container.instructions  \
  -text "Este programa carga una base de datos nueva en éste punto de venta.\n
Es necesario:\n
1) Base de datos de categorías y productos en formato CSV.\n
2) Haber respaldado información de ventas anteriores."
ttk::label .container.l_cat_en -text "Uso de categorías:"
ttk::label .container.l_cat_file -text "Categorías a cargar(CSV):"
ttk::label .container.l_db_file -text "Productos a cargar(CSV):"
ttk::label .container.l_db_name -text "Nombre de la base (campaña):"
ttk::label .container.l_pos_usr -text "Usuario del POS:"
ttk::label .container.l_pos_pass -text "Clave en el ticket:"
ttk::label .container.l_pos_dsct -text "Botón de descuento:"
ttk::label .container.l_pos_tckt -text "Tickets a imprimir:"
ttk::label .container.l_pos_dsct2 -textvariable disccount 
ttk::label .container.l_cat_en2 -textvariable category_en 
ttk::label .container.l_tckt_num -text "Iniciar tickets en:"
ttk::label .container.l_tckt_en -text "Cambiar inicio de tickets"
ttk::label .container.l_tckt_en2 -textvariable ticket_number_en

#Creating entries and buttons
ttk::checkbutton .container.c_cat_en -variable category_en \
  -onvalue "Con categorías" -offvalue "Sin categorías" -command enableCatFile
ttk::entry .container.e_cat_file -width 15 -textvariable catFilename
ttk::button .container.b_cat_file -text "Buscar" -command findCatFile 
ttk::entry .container.e_db_file -width 15 -textvariable filename
ttk::button .container.b_db_file -text "Buscar" -command findFile
ttk::entry .container.e_db_name -width 25 -textvariable db_name 
.container.e_db_name configure -validate key -validatecommand  {validName %P}
ttk::entry .container.e_pos_usr -width 25 -textvariable pos_usr
.container.e_pos_usr configure -validate key -validatecommand  {validName %P}
ttk::entry .container.e_pos_pass -width 25 -textvariable pos_pass
.container.e_pos_pass configure -validate key -validatecommand  {validName %P}
ttk::checkbutton .container.c_pos_dsct -variable disccount \
  -onvalue Habilitado -offvalue Deshabilitado
ttk::combobox .container.co_pos_tckt -width 15 -textvariable tickets 
ttk::button .container.b_load_db -text "Cargar Base" -command confirm
ttk::checkbutton .container.c_tckt_en -variable ticket_number_en \
  -onvalue "Habilitado" -offvalue "Deshabilitado" -command enableTicketNumber
ttk::entry .container.e_tckt_num -width 25 -textvariable ticket_number
.container.e_tckt_num configure -validate key -validatecommand {validNumber %P}

#Distributing elements
#labels
grid .container.instructions -column 0 -row 0 -columnspan 3
grid .container.l_cat_en -column 0 -row 1 -sticky e
grid .container.l_cat_file -column 0 -row 2 -sticky e
grid .container.l_db_file -column 0 -row 3 -sticky e
grid .container.l_db_name -column 0 -row 4 -sticky e
grid .container.l_pos_usr -column 0 -row 5 -sticky e
grid .container.l_pos_pass -column 0 -row 6 -sticky e
grid .container.l_pos_dsct -column 0 -row 7 -sticky e
grid .container.l_pos_tckt -column 0 -row 8 -sticky e
grid .container.l_tckt_en -column 0 -row 9 -sticky e
grid .container.l_tckt_num -column 0 -row 10 -sticky e
grid .container.l_cat_en2 -column 2 -row 1 -sticky w
grid .container.l_pos_dsct2 -column 2 -row 7 -sticky w
grid .container.l_tckt_en2 -column 2 -row 9 -sticky e
#entries and buttons
grid .container.c_cat_en -column 1 -row 1 -sticky w 
grid .container.e_cat_file -column 1 -row 2 -sticky we 
grid .container.b_cat_file -column 2 -row 2 -sticky we 
grid .container.e_db_file -column 1 -row 3 -sticky we 
grid .container.b_db_file -column 2 -row 3 -sticky we 
grid .container.e_db_name -column 1 -row 4 -sticky w -columnspan 2
grid .container.e_pos_usr -column 1 -row 5 -sticky w -columnspan 2
grid .container.e_pos_pass -column 1 -row 6 -sticky w -columnspan 2
grid .container.c_pos_dsct -column 1 -row 7 -sticky w 
grid .container.co_pos_tckt -column 1 -row 8 -sticky w -columnspan 2
grid .container.c_tckt_en -column 1 -row 9 -sticky w
grid .container.e_tckt_num -column 1 -row 10 -sticky w -columnspan 2
grid .container.b_load_db -column 1 -row 11 -columnspan 2 -sticky wens

#Disable file entry
.container.e_db_file state disabled
.container.e_cat_file state disabled
.container.b_cat_file state disabled
.container.e_tckt_num state disabled

#Set ticket printing number options
set tickets 2
.container.co_pos_tckt configure -values [list 2 3] 
.container.co_pos_tckt state readonly

#set padding to all elements
foreach w [winfo children .container] {grid configure $w -padx 5 -pady 5}
grid configure .container.e_cat_file -padx 0
grid configure .container.e_db_file -padx 0
grid configure .container.b_cat_file -padx 0
grid configure .container.b_db_file -padx 0
focus .container.b_db_file

proc errorMsg {msg} {
  tk_messageBox -message $msg -icon error -title "Error"
  exit 1
}

proc enableCatFile {} {
  global category_en
  if { $category_en == "Sin categorías" } {
    .container.b_cat_file configure -state disabled
  } else {
    .container.b_cat_file configure -state normal
  }
}

proc enableTicketNumber {} {
  global ticket_number_en
  if { $ticket_number_en == "Deshabilitado" } {
    .container.e_tckt_num configure -state disabled
  } else {
    .container.e_tckt_num configure -state normal
  }
}

proc validName {P} {
  if {[regexp {[^a-zA-Z0-9_]} $P test]} {
    tk_messageBox -title "Advertencia" \
       -message "Utilice sólo letras, números y guiones bajos.\n(No permitidos: ñ,acentos y espacios)" \
       -icon warning
    set result 0
  } else {
    set result 1
  }
  return $result
}

proc validNumber {P} {
  if {[regexp {[^0-9]} $P test]} {
    tk_messageBox -title "Advertencia" \
       -message "Utilice sólo números." \
       -icon warning
    set result 0
  } else {
    set result 1
  }
  return $result
}

proc findCatFile {} {
  global catFilename 
  set type {
    {{Archivo CSV} {.csv .CSV} }
  }
  set catFilename [tk_getOpenFile -filetypes $type]
  if {$catFilename ne ""} {
    checkCatCSV
  }
}
proc findFile {} {
  global pos_usr
  global db_name
  global filename 
  set type {
    {{Archivo CSV} {.csv .CSV} }
  }
  set filename [tk_getOpenFile -filetypes $type]
  if {$filename ne ""} {
    regexp {([a-zA-Z0-9_]+).(csv|CSV)$} $filename all first
    if {$first ne ""} {
      set db_name $first
      set pos_usr $first
    }
    checkCSV
  }
}

proc confirm {} {
  #Creating the window
  tk::toplevel .confirm 
  grid [ttk::frame .confirm.f -padding "30 20 30 20"] -column 0 -row 0
  wm title .confirm "Confirme por favor" 
  #Creating the label
  ttk::label .confirm.f.l_warn -text "¡Los datos existentes se borrarán!"
  #Creating the buttons
  ttk::button .confirm.f.b_cancel -text "Cancelar" -command cancel
  ttk::button .confirm.f.b_ok -text "Aceptar" -command process
  #Place the labels
  grid .confirm.f.l_warn -column 0 -row 0 -columnspan 2
  #Place buttons
  grid .confirm.f.b_cancel -column 0 -row 1 
  grid .confirm.f.b_ok -column 1 -row 1
  #Set window padding
  foreach w [winfo children .confirm.f] {grid configure $w -padx 5 -pady 5}
  #Set window logic
  focus .confirm.f.b_ok
  bind . <Return> {process}
}

proc cancel {} {
  destroy .confirm
}

proc process {} {
  destroy .confirm
  processWindow
  global filename
  global db_name
  global pos_usr
  global pos_pass
  set data [list "filename" "db_name" "pos_usr" "pos_pass"]
  set ilabel [list "Archivo no seleccionado" "Nombre de base de datos vacío" "Nombre de usuario vacío" "Falta clave de ticket"]
  set num 0
  set result "ok"

  foreach field $data {
    if {[set $field] eq ""} {
      tk_messageBox -title "Error" \
       -message [lindex $ilabel $num] \
       -icon error
      set result "error"
    }
    incr num
    if {$result eq "error"} {
      break
    }
  }
  
  #writeLog2 "file: $filename"
  #writeLog2 "db_name: $db_name"
  #writeLog2 "pos_usr: $pos_usr"
  #writeLog2 "pos_pass: $pos_pass"
 
  writeLog2 "Comenzando el proceso de instalación de base de datos"
  prepareDBSeed
  loadProducts
  buttonDisccount
  changeTicket
  ticketNumber
  changeProperties
  writeLog2 "CARGA TERMINADA EXITOSAMENTE"
  tk_messageBox -message "Base de datos de productos cargada correctamente" -icon info -title "Base Cargada"
}
proc checkCatCSV {} {
  #Creating the window
  tk::toplevel .catResults
  grid [ttk::frame .catResults.f -padding "30 20 30 20"] -column 0 -row 0
  wm title .catResults "Revisión de archivo de categorías"
  #Creating elements
  ttk::label .catResults.f.l_result -text "Resultados de revisión de archivo CSV\nCATEGORÍAS"
  tk::text .catResults.f.textarea -state disabled -width 100 -height 24 -wrap word -yscrollcommand ".catResults.f.s_text set"
  ttk::scrollbar .catResults.f.s_text -command ".catResults.f.textarea yview" -orient vertical
  ttk::button .catResults.f.b_ok -text "Cerrar" -command closeCatCSV
  #Placing elements
  grid .catResults.f.l_result -column 0 -row 0 -columnspan 2
  grid .catResults.f.textarea -column 0 -row 1 -sticky nswe
  grid .catResults.f.s_text -column 1 -row 1 -sticky nsw
  grid .catResults.f.b_ok -column 0 -row 2 -sticky e -columnspan 2
  #set padding to all elements
  foreach w [winfo children .catResults.f] {grid configure $w -padx 5 -pady 5}
  focus .catResults.f.b_ok
  puts "antes de parseCat"
  parseCatCSV
}
proc checkCSV {} {
  #Creating the window
  tk::toplevel .results
  grid [ttk::frame .results.f -padding "30 20 30 20"] -column 0 -row 0
  wm title .results "Revisión de archivo de productos"
  #Creating elements
  ttk::label .results.f.l_result -text "Resultados de revisión de archivo CSV\nPRODUCTOS"
  tk::text .results.f.textarea -state disabled -width 100 -height 24 -wrap word -yscrollcommand ".results.f.s_text set"
  ttk::scrollbar .results.f.s_text -command ".results.f.textarea yview" -orient vertical
  ttk::button .results.f.b_ok -text "Cerrar" -command closeCSV
  #Placing elements
  grid .results.f.l_result -column 0 -row 0 -columnspan 2
  grid .results.f.textarea -column 0 -row 1 -sticky nswe
  grid .results.f.s_text -column 1 -row 1 -sticky nsw
  grid .results.f.b_ok -column 0 -row 2 -sticky e -columnspan 2
  #set padding to all elements
  foreach w [winfo children .results.f] {grid configure $w -padx 5 -pady 5}
  focus .results.f.b_ok
  parseCSV
}
proc processWindow {} {
  #Creating the window
  tk::toplevel .r2
  grid [ttk::frame .r2.f -padding "30 20 30 20"] -column 0 -row 0
  wm title .r2 "Resultados del proceso"
  #Creating elements
  ttk::label .r2.f.l_result -text "Resultados de la carga de base de datos nueva"
  tk::text .r2.f.textarea -state disabled -width 100 -height 24 -wrap word -yscrollcommand ".r2.f.s_text set"
  ttk::scrollbar .r2.f.s_text -command ".r2.f.textarea yview" -orient vertical
  ttk::button .r2.f.b_ok -text "Terminar" -command closeProcessWindow
  #Placing elements
  grid .r2.f.l_result -column 0 -row 0 -columnspan 2
  grid .r2.f.textarea -column 0 -row 1 -sticky nswe
  grid .r2.f.s_text -column 1 -row 1 -sticky nsw
  grid .r2.f.b_ok -column 0 -row 2 -sticky e -columnspan 2
  #set padding to all elements
  foreach w [winfo children .r2.f] {grid configure $w -padx 5 -pady 5}
  focus .r2.f.b_ok
}

proc writeLog {msg} {
  .results.f.textarea configure -state normal 
  .results.f.textarea insert end "$msg\n"
  .results.f.textarea configure -state disabled
}

proc writeLog3 {msg} {
  .catResults.f.textarea configure -state normal 
  .catResults.f.textarea insert end "$msg\n"
  .catResults.f.textarea configure -state disabled
}
proc writeLog2 {msg} {
  .r2.f.textarea configure -state normal 
  .r2.f.textarea insert end "$msg\n"
  .r2.f.textarea configure -state disabled
}

proc parseCatCSV {} {
  global catFilename
  global catCsv
  global .catResults
  if { $catFilename ne "" } {
    set fh [open $catFilename]
    fconfigure $fh -encoding utf-8
    set num 1 
    set line_err 0
    set err 0
    set errorTxt ""

    while {[gets $fh line] != -1} {
      set catCsv($num) [csv::split $line]
      if { [llength $catCsv($num)] != "2" } {
        append errorTxt "Campos insuficientes o excedentes. La estructura es: 'Número de categoría,Nombre de la categoría'"
        incr line_err
      }
      lassign $catCsv($num) lnum catName
      if {! [regexp {^[0-9a-zA-Z ]{1,80}$} $catName]} {
        append errorTxt "Descripción menor a 80 caracteres, sólo alfanuméricos. "
        incr line_err
      }
      if { $line_err > 0 } {
        set catCsv($num) ""
        writeLog3 "Línea $num: $errorTxt"
        incr err
      } else {
         writeLog3 "Categoría agregada: $lnum,$catName"
      }
      incr num
      set line_err 0
      set errorTxt ""
    }
    close $fh 
  }
  if { $err > 0 } {
    writeLog3 "======================\nATENCIÓN: $err líneas con error, verifique."
  } else {
    writeLog3 "======================\nCSV de categorías Correcto!"
  }
}


proc parseCSV {} {
  global filename
  global csv
  global category_en
  global catCsv
  global .results
  if { $filename ne "" } {
    set fh [open $filename]
    fconfigure $fh -encoding utf-8
    set num 1 
    set line_err 0
    set err 0
    set errorTxt ""

    array set products_isbn {}
    array set products_descr {}
   
    while {[gets $fh line] != -1} {
      set csv($num) [csv::split $line]
      if { [llength $csv($num)] != "8" } {
        append errorTxt "Campos insuficientes o excedentes. La estructura es: 'Número de producto,ISBN,código de barras,Nombre del libro,0,precio,categoría,impuestos'"
        incr line_err
      }
      lassign $csv($num) lnum isbn code descr idy price group taxcat
      #writeLog "No: $lnum, ISBN: $isbn, Code: $code, ID: $idy, $$price, Group: $group, Tax: $taxcat"
      if {! [string is digit $lnum]} {
        append errorTxt "Número de línea '$lnum' no es un dígito. "
        incr line_err
      }
      if {! [regexp {^[0-9A-Z]{13}?$} $isbn]} {
        append errorTxt "ISBN '$isbn' debe tener 13 dígitos. "
        incr line_err
      }
      if { [ info exists products_isbn($isbn) ] } {
        append errorTxt "ISBN '$isbn' repetidos, saltando línea."
        incr line_err
      } else {
        set products_isbn($isbn) 1
      }
      if {! [regexp {^[0-9A-Z]{13}?$} $code]} {
        append errorTxt "El código '$code' debe tener 13 dígitos. "
        incr line_err
      }
      regsub -all {[^0-9a-zA-Z ]} $descr "" descr 
      while { [info exists products_descr($descr) ] } {
        set descr "$descr.d"
      } 
      set products_descr($descr) 1
      #if {! [regexp {^[0-9a-zA-Z ]{1,80}$} $descr]} {
      #  append errorTxt "La descripción debe ser menor a 80 caracteres, sólo alfanuméricos. "
      #  incr line_err
      #}
      if {$idy != 0} {
        append errorTxt "ID '$idy' debe de ser 0. "
        incr line_err
      }
      if {! [regexp {[0-9]+\.*[0-9]{0,2}} $price]} {
        append errorTxt "Precio '$price' mal formado. "
        incr line_err
      }
      if {! [regexp {[0-9]{1,3}} $group]} {
        append errorTxt "Categoría mal formada: $group"
        incr line_err
      }
      if {! [regexp {00[1|0]} $taxcat]} {
        append errorTxt "Categoría de impuesto erronea: $taxcat, debe de ser '000' o '001'"
        incr line_err
      }
      #check if categoríes selected
      if { $category_en == "Con categorías" } {
        set found "no"
        foreach {key lineCat} [array get catCsv] { 
          lassign $lineCat catNum catName 
          if { $group == $catNum } {
            set found "yes"
          }
        }
        if { $found == "no" } {
          append errorTxt "Categoría no encontrada en el archivo de categorías."
          incr line_err
        }
      }
      set csv($num) [ list $lnum $isbn $code $descr $idy $price $group $taxcat ]
      puts $csv($num)
      if { $line_err > 0 } {
        set csv($num) ""
        writeLog "Línea $num: $errorTxt"
        incr err
      }
      incr num
      set line_err 0
      set errorTxt ""
    }
    close $fh 
  }
  if { $err > 0 } {
    writeLog "======================\nATENCIÓN: $err líneas con error, verifique."
  } else {
    writeLog "======================\nCSV Correcto!"
  }
}

proc closeCSV {} {
  destroy .results
}

proc closeCatCSV {} {
  destroy .catResults
}
proc closeProcessWindow {} {
  destroy .r2
  exit 0
}

proc prepareDBSeed {} {
  global db_name
  global db_usr
  global db_pass
  global pos_usr
  global pos_pass
  global db_root
  global sys_usr
  global wd
  writeLog2 "Preparando semilla de base de datos"
  set rc [catch {exec -- sed -e "s/{MYSQL_DB}/$db_name/g" -e "s/{MYSQL_USR}/$db_usr/g" -e "s/{MYSQL_PASS}/$db_pass/g" -e "s/{POS_USR}/$pos_usr/g" -e "s/{POS_PASS}/$pos_pass/g" $wd/VDSEMILLA.sql > /home/$sys_usr/$db_name.sql } msg ]
  if { $rc != "0" } {
    errorMsg $msg   
  }
  writeLog2 "Cargando base de datos..."
  set rc [catch { exec -- mysql -u root --password=$db_root < /home/$sys_usr/$db_name.sql} msg ]
  if { $rc != "0" } {
    errorMsg $msg
  }
  writeLog2 "Eliminando archivo temporal $db_name.sql"
  set rc [ catch {exec -- rm /home/$sys_usr/$db_name.sql } msg ]
  if { $rc != "0" } {
    errorMsg $msg 
  }
  writeLog2 "OK -  Creación de base de datos lista"
}

proc connectDB {} {
  global db_usr
  global db_name
  global db_pass
  global db_h
  set db_h [mysqlconnect -user $db_usr -db $db_name -password $db_pass]
}

proc execDB {sql} {
  global db_h
  return [::mysql::exec $db_h $sql]
}

proc loadProducts {} {
  global csv
  global category_en
  global catCsv
  global db_h
  writeLog2 "Cargando los productos a la base de datos"
  connectDB
  set num 1  
  set num_cat 1
  set RE {[][{}\$\s\u0100-\uffff]}

  if { $category_en == "Con categorías" } {
    foreach {key line} [array get catCsv] {
      if { $line != "" } { 
        lassign $line catNum catName
        execDB "INSERT INTO CATEGORIES (ID, NAME) VALUES (\"$catNum\",\"$catName\");"
        incr num_cat
        writeLog2 "Categoría agregada: $catNum,$catName"
      }
    }
    writeLog2 "Categorías importadas: $num_cat"
  }


  foreach {key line} [array get csv] {
    if { $line != "" } {
      lassign $line lnum isbn code descr idy price group taxcat
     
     puts "procesando: '$line'"    
     execDB "INSERT INTO PRODUCTS (ID,REFERENCE,CODE,NAME,PRICEBUY,PRICESELL,CATEGORY,TAXCAT) VALUES ($lnum,\"$isbn\",\"$code\",\"$descr\",\"$idy\",\"$price\",\"$group\",\"$taxcat\")"
     if { $category_en == "Con categorías" } {
       execDB "INSERT INTO PRODUCTS_CAT (PRODUCT,CATORDER) VALUES ($lnum,NULL)"
     }
     incr num
    }
  }
  writeLog2 "Productos importados: $num"
}

proc buttonDisccount {} {
  global disccount
  global wd
  if { $disccount == "Habilitado" } {
    writeLog2 "Habilitando botón de descuento"
    #Reading Ticket.Buttons to memory
    set fp [open "$wd/resources/Ticket.Buttons.disccount" r]
    fconfigure $fp -encoding utf-8
    set file_data [read $fp]
    close $fp
    #Editing existing Ticket.Buttons resource
    execDB "UPDATE RESOURCES SET CONTENT='$file_data' WHERE NAME='Ticket.Buttons'"
  } 
}

proc changeTicket {} {
  global pos_usr
  global pos_pass
  global tickets
  global wd
  writeLog2 "Tickets a imprimir $tickets"
  if { $tickets == 3 } {
    writeLog2 "Cambiando tickets a 3"
    #Reading Printer.Ticket.3 to memory
    set fp [open "$wd/resources/Printer.Ticket.3" r]
    fconfigure $fp -encoding utf-8
    set file_data [read $fp]
    close $fp
    #Replacing data
    regsub -all {{POS_USR}} $file_data $pos_usr file_data
    regsub -all {{POS_PASS}} $file_data $pos_pass file_data
    #Editing existing Printer.Ticket
    execDB "UPDATE RESOURCES SET CONTENT='$file_data' WHERE NAME='Printer.Ticket'"
  }
}

proc ticketNumber {} {
  global ticket_number_en
  global ticket_number
  global wd
  if { $ticket_number_en == "Habilitado" } {
    writeLog2 "Cambiando número de inicio del ticket"
    #Editing existing Ticket.Buttons resource
    execDB "UPDATE TICKETSNUM SET ID = '$ticket_number' WHERE ID =  '1'"
  }
}

proc changeProperties {} {
   global db_usr
   global db_name
   global db_pass
   global sys_usr
   global wd
   set db_pass_c ""
   writeLog2 "Arreglando openbravopos.properties"
   writeLog2 "Codificando clave"
   set rc [catch { exec -- $wd/utils/encrypt.sh $wd $db_usr $db_pass } msg]
   if { $rc != "0" } {
     errorMsg $msg 
   }
   set db_pass_c $msg
   #Reading openbravopos.properties to memory
   set fp [open "$wd/openbravopos.properties" r]
   fconfigure $fp -encoding utf-8
   set file_data [read $fp]
   close $fp
   #Replacing data
   regsub -all {{MYSQL_USR}} $file_data $db_usr file_data
   regsub -all {{MYSQL_DB}} $file_data $db_name file_data
   regsub -all {{MYSQL_PASS}} $file_data $db_pass_c file_data
   set filename "/home/$sys_usr/openbravopos.properties"
   exec -- rm -f $filename
   set fp [open $filename "w"]
   puts $fp $file_data
   close $fp
}