// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// ! This script is provided "as is" by Applied Maths.              !
// ! You are free to use and modify this script for your own needs. !
// ! Redistribution or reproduction of the script is prohibited.    !
// ! DISCLAIMER:                                                    !
// ! Improper use of scripts may corrupt your database.             !
// ! Running this script is entirely at your own responsibility.    !
// ! Applied Maths accepts no lialibility for any consequences      !
// ! resulting from its use.                                        !
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

integer i,ok1,ok2,found,reccount,addexist,addnew;
ODBC db;
string tablelist,tablestring,st1,st2,st3;
string charsetlist,charsetname,cfname;
DIALOG tbdlg,chdlg,dlg,dlg1;
string bnfieldlist,bnlinkfield,field,lockey;
string extfieldlist,extlinkfield,fieldlist;
string howaddlist,howadd,maxval;
integer charcount,charidx[],extfieldidx[];
integer extlinkfieldidx;
INDEX idx;
CHARSET cs;

//Prompt for database & table
OdbcOpen(db,"","");
tablelist="";tablestring="";
for i=1 to OdbcGetTableCount(db) do {
   tablelist=tablelist+OdbcGetTableName(db,i)+"	";
   if length(tablestring)<=0 then tablestring=OdbcGetTableName(db,i);
   }
if length(tablestring)<=0 then {
   message("ERROR: Not table found in the database");
   stop;
}
DlgAddList(tbdlg,tablelist,tablestring,15,15,180,200,"LIST");
if not(DlgShow(tbdlg,"Select a table",320,240)) then stop;
if length(tablestring)<=0 then stop;
if substring(tablestring,1,1)="[" then tablestring=substring(tablestring,2,length(tablestring)-1);
OdbcOpen(db,"",tablestring);


//prompts for character set
charsetlist="";charsetname="";
for i=1 to DbGetExperCount do
   if DbGetExperClass(i)="CHR" then {
      charsetlist=charsetlist+DbGetExperName(i)+"	";
      }
      charsetlist = charsetlist+"<Create new experiment>";
      charsetname = charsetlist;
      charsetname = splitString(charsetname,"	");
      if length(charsetname)<=0 then charsetname=DbGetExperName(i);
   
DlgAddList(chdlg,charsetlist,charsetname,15,15,180,200,"LIST");
if not(DlgShow(chdlg,"Select a character set",320,240)) then stop;
if length(charsetname)<=0 then stop;

//create new character type if needed
if charsetname = "<Create new experiment>" then {
   charsetname="";
   DlgAddText(dlg1,"Enter a character set name",20,20,150,15);
   DlgAddEdit(dlg1,charsetname,20,40,200,18);
   if not(DlgShow(dlg1,"Create Character Type",250,200)) then stop;
   DbCreateExperType(charsetname,"CHR");
} 
 
//prompts for characters to import + various parameters

bnfieldlist="KEY	";bnlinkfield="KEY";
for i=1 to DbGetFieldCount do
   bnfieldlist=bnfieldlist+DbGetFieldName(i)+"	";
   
extfieldlist="";
for i=1 to OdbcGetFieldCount(db) do {
   OdbcGetFieldInfo(db,i,st1,st2);
   extfieldlist=extfieldlist+st1+"	";
}

fieldlist="";
for i=1 to ChrSetGetCount(charsetname) do
   fieldlist=fieldlist+ChrSetGetChar(charsetname,i)+"	";


DlgAddText(dlg,"Select field(s) to download",20,20,150,15);
DlgAddList(dlg,extfieldlist,fieldlist,20,40,160,200,"MULTI");
   
DlgAddText(dlg,"Enter character filename",200,20,150,15);
DlgAddEdit(dlg,cfname,200,40,150,20);

DlgAddText(dlg,"Link local field",200,70,150,15);
DlgAddList(dlg,bnfieldlist,bnlinkfield,200,90,150,150,"DROP");

DlgAddText(dlg,"to external field",200,120,150,15);
DlgAddList(dlg,extfieldlist,extlinkfield,200,140,150,150,"DROP");

howaddlist="Import all entries	Import existing entries only	Import new entries only";
howadd="Import all entries";
DlgAddList(dlg,howaddlist,howadd,20,240,160,100,"DROP");

maxval="100";
DlgAddText(dlg,"Max. value of new characters",200,170,150,15);
DlgAddEdit(dlg,maxval,200,190,80,20);

if not(DlgShow(dlg,"Import character data",370,330)) then stop;

addnew=1;addexist=1;
if howadd="Import existing entries only" then addnew=0;
if howadd="Import new entries only" then addexist=0;



//Determines what fields will be imported, and makes links to local characters
//create new characters if necessary
charcount=0;
while length(fieldlist)>0 do {
   st1=splitstring(fieldlist,"	");
   charcount=charcount+1;
   charidx[charcount]=ChrSetFindChar(charsetname,st1);
   if (charidx[charcount]<=0) then charidx[charcount]=ChrSetAddChar(charsetname,st1,val(maxval));
   extfieldidx[charcount]=1;
   for i=1 to OdbcGetFieldCount(db) do {
      OdbcGetFieldInfo(db,i,st2,st3);
      if st2=st1 then extfieldidx[charcount]=i;
   }
}


//determine index of external link field
extlinkfieldidx=0;
for i=1 to OdbcGetFieldCount(db) do {
   OdbcGetFieldInfo(db,i,st2,st3);
   if st2=extlinkfield then extlinkfieldidx=i;
}

//determine whether local link field is correct!!! (no empties, no duplicates)
if bnlinkfield<>"KEY" then
{
   for i=1 to DbGetEntryCount do {
      field=DbGetField(DbGetEntryKey(i),bnlinkfield);
      if length(field)<=0 then {
         message("ERROR: Some of the local link fields are empty");
         stop;
      }
      if IdxGet(idx,field)>0 then {
         message("ERROR: The local link field is not unique");
         stop;
      }
      IdxSet(idx,field,i);
   }
}


//loops through all external database records
reccount=0;
while not(OdbcIsEnd(db)) do {
   if floor(reccount/10)=reccount/10 then
      setbusy("Importing using ODBC - "+str(reccount,0,0)+" finished");
   reccount=reccount+1;
   
//determine key of BN entry for association
   lockey="";
   if extlinkfieldidx>0 then {
      OdbcGetString(db,extlinkfieldidx,extlinkfield);
      if bnlinkfield="KEY" then lockey=extlinkfield;
      if bnlinkfield<>"KEY" then {
         found=IdxGet(idx,extlinkfield);
         if found>0 then lockey=DbGetEntryKey(found);
      }
   }

//check if allowed to create new entry   
   ok1=1;
   if (length(lockey)>0) and (DbIsKeyPresent(lockey)) then {
      if not(addexist) then ok1=0;
   }
  
//create new entry if necessary and if allowed
   ok2=1;
   if (length(lockey)<=0) or not(DbIsKeyPresent(lockey)) then {
      ok2=0;
      if addnew then {
         lockey=DbAddEntry(lockey);
         ok2=1;
      }
   }

//download the character info if allowed
   if ok1 and ok2 then {
      if not(ChrLoad(cs,lockey,charsetname)) then
         ChrCreate(cs,charsetname,cfname,lockey);
      for i=1 to charcount do {
         OdbcGetString(db,extfieldidx[i],field);
         ChrSetVal(cs,charidx[i],-1);
         if length(field)>0 then ChrSetVal(cs,charidx[i],val(field));
         }
      ChrSave(cs);
   }
   
   OdbcNextRecord(db);
}

OdbcClose(db);
setbusy("");