*-------------------------------------------------------------------------------
* %NAME%
*     dbf2sql.prg
* %DESCRIPTION%
*     Przenoszenie danych z plik�w dbf do sql
* %HISTORY%
*     FS 2018.08.31 Dla Daty i Datetime domyslna wartosc to NULL
*     RK 2017.09.18 ob�uga typu T i @
*     RK 2007.08.28
*-------------------------------------------------------------------------------

#define PL_ASC "ACELNOSZZacelnoszz"
#define PL_ISO "��ʣ�Ӧ������󶼿"
#define PL_WIN "��ʣ�ӌ������󜟿"
#define PL_LAT "�������������䢘��"
#define PL_MAZ "������������������"

#define PL_SRC PL_LAT

#define EOL chr(13)+chr(10)

#define ILE_PARAM 9

*-------------------------------------------------------------------------------
func main(p_sql, p_app, p_dbf, p_table, p_charset, p_create, p_drop, p_data, p_del)
*-------------------------------------------------------------------------------
local h := 0
local l_listapol, l_1st, l_pole, l_name, l_typ, l_len, l_dec, i
local t_stru := {}

if pcount() # ILE_PARAM
   ? " Wydanie z "+__DATE__+" "+__TIME__+" (kodowanie DBF: CP852) "
   ? "   dbf2sql.exe <sql> <app> <dbf> <table> <charset> <create> <drop> <data> <del>"
   ?
   ? "      <sql>     - nazwa zbioru, w ktorym zostanie zapisany SQL"
   ? "      <app>     - NEW - tworz zbior <sql> na nowo"
   ? "                  APP - dopisz do <sql>"
   ? "      <dbf>     - nazwa konwertowanego zbioru DBF"
   ? "      <table>   - nazwa tabeli SQL"
   ? "      <charset> - kodowanie PL-znakow w SQL: "
   ? "                     ISO=iso-8859-2    LAT=cp852   WIN=cp1250 ASC=asci"
   ? "      <create>  = CREATE   - dodaj'CREATE TABLE IF EXISTS'"
   ? "                  NOCREATE - nie dodawaj"
   ? "      <drop>    - DROP   - dodaj: 'DROP TABLE IF EXISTS'"
   ? "                  NODROP - nie dodawaj"
   ? "      <data>    - DATA   - dodaj 'INSERT INTO' z danymi z <dbf>"
   ? "                  NODATA - nie dodawaj"
   ? "      <del>     - DEL   - dodaj: 'TRUNCATE TABLE'"
   ? "                  NODEL - nie dodawaj"
   ? "   dbf2sql.exe db.sql NEW NA00ODFP.DBF Odbiorcy ISO NOCREATE NODROP DATA DEL"
   wait
   return 1
endif

if upper(p_app) == "NEW"
   if file(p_sql)
      ferase(p_sql)
      if ferror() # 0
         ? "BLAD: Nie mozna usunac zbioru: "+p_sql
         return 0
      endif
   endif
endif

if !file(p_sql)
   h := fcreate(p_sql)
else
   h := fopen(p_sql, 2)
   fseek(h, 0, 2)
endif
if ferror() # 0
   ? "BLAD: Nie mozna otworzyc zbioru: "+p_sql
   return 1
endif

if !file(p_dbf)
   ? "BLAD: Brak zbioru: "+p_dbf
   return 1
endif

select 1
use (p_dbf) shared
if neterr()
   ? "BLAD: Nie mozna otworzyc zbioru: "+p_dbf
   return 1
endif

// pomijaj usuniete rekordy
set deleted on

p_charset := upper(p_charset)
t_stru := dbstruct()

SET DATE FORMAT TO "yyyy-mm-dd"

if upper(p_drop) == "DROP"
   fwrite(h, "DROP TABLE IF EXISTS `"+p_table+"`;"+EOL)
endif

if upper(p_create) == "CREATE"
   fwrite(h, "CREATE TABLE IF NOT EXISTS `"+p_table+"` ("+EOL)

   for i := 1 to len(t_stru)
      l_pole := ""
      l_name := alltrim(lower(t_stru[i][1]))
      l_typ  := t_stru[i][2]
      l_len  := alltrim(str(t_stru[i][3]))
      l_dec  := alltrim(str(t_stru[i][4]))
      do case
      case l_typ == "C"
         l_pole := "`"+l_name+"` VARCHAR("+l_len+") NOT NULL DEFAULT ''"
      case l_typ == "M"
        l_pole := "`"+l_name+"` TEXT "
        * l_pole := "`"+l_name+"` TEXT NOT NULL DEFAULT ''"
      case l_typ == "N"
         l_pole := "`"+l_name+"` DECIMAL("+l_len+","+l_dec+") NOT NULL DEFAULT 0"
      case l_typ == "D" 
        * FS 08/2018
        l_pole :=   "`"+l_name+"` DATE " 
      case l_typ == "T" .or. l_typ == "@" 
         l_pole := "`"+l_name+"` DATETIME" 
        * l_pole := "`"+l_name+"` DATETIME DEFAULT '0000-00-00'"
        *l_pole := "`"+l_name+"` DATE NOT NULL DEFAULT '0000-00-00'"
      case l_typ == "L"
         l_pole := "`"+l_name+"` CHAR(1) NOT NULL DEFAULT 'N'"
      endcase
      if i < len(t_stru)
         l_pole += ","
      endif
      fwrite(h, space(3)+l_pole+EOL)
   next

   fwrite(h, ");"+EOL)
endif

if upper(p_del) == "DEL"
   fwrite(h, "TRUNCATE TABLE `"+p_table+"`;"+EOL)
   fwrite(h, "ALTER TABLE `"+p_table+"` AUTO_INCREMENT=1;"+EOL)
endif

if upper(p_data) == "DATA"
   go top
   l_listapol := ""
   for i := 1 to len(t_stru)
      l_listapol += if(empty(l_listapol),"",",")+"`"+lower(alltrim(t_stru[i][1]))+"`"
   next
   do while !eof()
      l_1st := .t.
      fwrite(h, "INSERT INTO `"+p_table+"` ("+l_listapol+") "+EOL)
      fwrite(h, "   VALUES (")
      for i := 1 to len(t_stru)
         l_pole := ""
         l_name := alltrim(lower(t_stru[i][1]))
         l_typ  := t_stru[i][2]
         l_len  := alltrim(str(t_stru[i][3]))
         l_dec  := alltrim(str(t_stru[i][4]))
         do case
         case l_typ == "C"
            * Pole binarne
            if upper(fieldname(i))=="CZKCENA"
               l_pole := ""
            else
               l_pole := rtrim(fieldget(i))
            endif
         case l_typ == "M"
            l_pole := strtran(rtrim(fieldget(i)), chr(141)+chr(10), chr(32))
         case l_typ == "N"
            l_pole := alltrim(str(fieldget(i), t_stru[i][3], t_stru[i][4]))
         case l_typ == "D"
            * FS 08/2018
            *l_pole := strtran(dtoc(fieldget(i))," ","0")
            if empty(fieldget(i))
               l_pole := "NULL"
            else
               l_pole := dtoc(fieldget(i))
            endif
         case l_typ == "T" .or. l_typ == "@"
            * FS 08/2018
            if empty(fieldget(i))
               l_pole := "NULL"
            else
               l_pole := hb_ttoc(fieldget(i))
            endif
         case l_typ == "L"
            l_pole := if(fieldget(i),"T","N")
         endcase
         l_pole := strtran(l_pole, '\', '\\')
         l_pole := strtran(l_pole, chr(13)+chr(10), "\n")
         l_pole := strtran(l_pole, chr(10), "\n")
         l_pole := strtran(l_pole, "'", "\'")
         l_pole := strtran(l_pole, '"', '\"')
         if l_typ # "N"
            if p_charset == "ISO"
               l_pole := charrepl(PL_SRC, l_pole, PL_ISO, .T.)
            endif
            if p_charset == "LAT"
               l_pole := charrepl(PL_SRC, l_pole, PL_LAT, .T.)
            endif
            if p_charset == "WIN"
               l_pole := charrepl(PL_SRC, l_pole, PL_WIN, .T.)
            endif
             if p_charset == "ASC"
               l_pole := charrepl(PL_SRC, l_pole, PL_ASC, .T.)
            endif 
            * FS 08/2018
            * Dla daty pozostaw NULL
            if !(l_typ$"DT@".and.l_pole="NULL")
               l_pole := "'"+l_pole+"'"
            endif
         endif
         if !l_1st
            l_pole := ","+l_pole
         endif
         l_1st := .f.
         fwrite(h, l_pole)
      next
      fwrite(h, ");"+EOL)
      skip 1
      if recno() % 1000 == 0
         ? "Rekord: #",recno()
      endif
   enddo
endif

fclose(h)
? "KONIEC."
return 0


// EOF