/*
-----------------------------------------------------------------------------
This source file is part of OpenSpace3D
For the latest info, see http://www.openspace3d.com

Copyright (c) 2018 I-maginer

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU Lesser General Public License as published by the Free Software
Foundation; either version 2 of the License, or (at your option) any later
version.

This program is distributed in the hope that it will be useful, but WITHOUT
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place - Suite 330, Boston, MA 02111-1307, USA, or go to
http://www.gnu.org/copyleft/lesser.txt

-----------------------------------------------------------------------------
*/

struct PSOdbcQuery = [
  PODBCQ_sName      : S,
  PODBCQ_sQuery     : S,
  PODBCQ_sOutput    : S
  
] mkSOdbcQuery;;

struct PlugOdbc = [
  PODBC_inst       : PInstance,
  PODBC_sName      : S,
  PODBC_sLogin     : S,
  PODBC_sPassword  : S,  
  PODBC_db         : SqlDB,
  PODBC_lQuery     : [[S PSOdbcQuery] r1],
  PODBC_bUtf8      : I 
  
] mkPlugOdbc;;


/*
//Tests
_sqliteExec obstr.PODBC_db "SELECT * FROM myTable";    
_sqliteExec obstr.PODBC_db "CREATE TABLE `NewTable` (`id`	INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL, `value` TEXT NOT NULL);";

let _sqliteExecResult obstr.PODBC_db "SELECT * FROM myTable" -> lraw in
while (lraw != nil) do
(
  addLogMessage ">> raw";
  let hd lraw -> lfield in
  while (lfield != nil) do
  (
    let hd lfield -> [n v] in
      addLogMessage strcatn "Name: "::n::" > Value: "::v::nil;
    set lfield = tl lfield;
  );
  set lraw = tl lraw;
);
*/

fun deleteOb(inst, obstr)=
  SqlDestroy obstr.PODBC_db;
  0;;


fun writeData(obstr, data)=
  if (obstr.PODBC_bUtf8) then
    strtoutf8 data
  else
    data;;


fun readData(obstr, data)=
  if (obstr.PODBC_bUtf8) then
    utf8tostr data
  else
    data;;


fun cbExecQuery(inst, from, action, param, rep, obstr)=
  let SqlRequestEx obstr.PODBC_db (writeData obstr param) nil -> lraw in
  while (lraw != nil) do
  (
    //addLogMessage ">> raw";
    let hd lraw -> lfield in
    let "" -> out in
    (
      while (lfield != nil) do
      (
        let hd lfield -> [n v] in
        (
          if ((strlen out) == 0) then
            set out = v
          else
            set out = strcatn out::" "::v::nil;     
        
          //addLogMessage strcatn "Name: "::n::" > Value: "::v::nil;
        );
        set lfield = tl lfield;
      );
    
      SendPluginEvent inst "Result" (readData obstr out) nil;
    );
    set lraw = tl lraw;
  );
  0;;
 
 
 fun cbExecThisQuery(inst, from, action, param, rep, obstr, qstr)=
  // replace $0, $1.. and %var%
  let _DMSreadParam param qstr.PODBCQ_sQuery -> squery in
  let SqlRequestEx obstr.PODBC_db (writeData obstr squery) nil -> lraw in
  let if (strfindi "$" qstr.PODBCQ_sOutput 0) == nil then 0 else 1 -> template in  
  while (lraw != nil) do
  (
    //addLogMessage ">> raw";
    let hd lraw -> lfield in
    let if (template) then qstr.PODBCQ_sOutput else "" -> out in
    (
      while (lfield != nil) do
      (
        let hd lfield -> [n v] in
        (
          if (template) then
          (
            set out = strreplace out (strcat "$" n) v;
          )
          else
          (
            if ((strlen out) == 0) then
              set out = v
            else
              set out = strcatn out::" "::v::nil;
          );        
        
          //addLogMessage strcatn "Name: "::n::" > Value: "::v::nil;
        );
        set lfield = tl lfield;
      );
    
      SendPluginEvent inst (strcat "Result " qstr.PODBCQ_sName) (readData obstr out) nil;
    );
    set lraw = tl lraw;
  );
  0;;


fun loadQueries(inst, obstr)=
  let nil -> l in
  let nil -> query in
  let 0 -> i in
  (
    while ((set query = getPluginInstanceParam inst (strcat "queryName" (itoa i))) != nil) do
    (
      let getPluginInstanceParam inst (strcat "querySql" (itoa i)) -> sql in
      let getPluginInstanceParam inst (strcat "queryOut" (itoa i)) -> out in
      
      let mkSOdbcQuery[query sql out] -> qstr in
      (
        set l = [query qstr]::l;
        PluginRegisterAction inst (strcat "Exec " query) mkfun6 mkfun7 @cbExecThisQuery qstr obstr;
      );
      
      set i = i + 1;
    );
    set obstr.PODBC_lQuery = revertlist l;
  );;


fun connectDb(inst, from, action, param, rep, obstr)=
  if ((param == nil) || (!strcmp param "")) then nil else
  let lineextr param -> lp in
  (
    set obstr.PODBC_sName = hd lp;
    set obstr.PODBC_sLogin = hd tl lp;
    set obstr.PODBC_sPassword = hd tl tl lp;
  );
  
  if (obstr.PODBC_db == nil) then nil else
  (
    SqlDestroy obstr.PODBC_db;
    set obstr.PODBC_db = nil;
  );
  
  let SqlCreate _channel obstr.PODBC_sName obstr.PODBC_sLogin obstr.PODBC_sPassword -> db in
  (
    set obstr.PODBC_db = db;
    
    if (db == nil) then
    (
      SendPluginEvent inst "Connection error" nil nil;      
      addLogMessage strcat "ODBC connection failed on " obstr.PODBC_sName
    )
    else
    (
      SendPluginEvent inst "Connected" nil nil;
      addLogMessage strcat "ODBC connected on " obstr.PODBC_sName;
    );
    
    let SqlDescErr db -> [state err mess nb] in
      addLogMessage strcatn "ODBC "::obstr.PODBC_sName::" "::mess::nil;
  );
  0;;


fun newOb(inst)=
  let getPluginInstanceParam inst "dbname" -> dbname in
  let getPluginInstanceParam inst "dblogin" -> dblogin in
  let getPluginInstanceParam inst "dbpass" -> dbpass in
  let atoi getPluginInstanceParam inst "dbutf8" -> dbutf8 in
  let if (dbutf8 == nil) then 0 else dbutf8 -> dbutf8 in
  
  let mkPlugOdbc [inst dbname dblogin dbpass nil nil dbutf8] -> obstr in
  (
    PluginRegisterAction inst "Change database" mkfun6 @connectDb obstr;
    PluginRegisterAction inst "Exec" mkfun6 @cbExecQuery obstr;
    
    if ((dbname == nil) || (!strcmp dbname "")) then nil else
      connectDb inst nil nil nil nil obstr;
    
    loadQueries inst obstr;
    
    setPluginInstanceCbDel inst mkfun2 @deleteOb obstr;
  );
  0;;


fun IniPlug(file)=
  PlugRegister @newOb nil;
  setPluginEditor @dynamicedit;
  0;;