// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// ! 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;
string bnfieldlist,extfieldlist,st1,st2,st3;
string howaddlist,howadd;
DIALOG tbdlg,dlg;
string bnlinkfield,extlinkfield,fieldlist;
integer fieldcount,extfieldidx[];
string fieldname[];
integer extlinkfieldidx;
string lockey,field;
string impfield[];
INDEX idx;

//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);

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 DbGetFieldCount do
   fieldlist=fieldlist+DbGetFieldName(i)+"	";

DlgAddText(dlg,"Select field(s) to download",20,20,150,15);
DlgAddList(dlg,extfieldlist,fieldlist,20,40,160,150,"MULTI");

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

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

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

if not(DlgShow(dlg,"Import database fields",360,250)) 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 fields
//create new database fields if necessary
fieldcount=0;
while length(fieldlist)>0 do {
   st1=splitstring(fieldlist,"	");
   fieldcount=fieldcount+1;
   fieldname[fieldcount]=st1;
   found=0;
   for i=1 to DbGetFieldCount do
      if st1=DbGetFieldName(i) then found=i;
   if found<=0 then DbAddField(st1);
   extfieldidx[fieldcount]=1;
   for i=1 to OdbcGetFieldCount(db) do {
      OdbcGetFieldInfo(db,i,st2,st3);
      if st2=st1 then extfieldidx[fieldcount]=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;

//load external fields
   ok1=0;
   for i=1 to fieldcount do {
      OdbcGetString(db,extfieldidx[i],impfield[i]);
      if extfieldidx[i]=extlinkfieldidx then { ok1=1; extlinkfield=impfield[i]; }
   }
   if not(ok1) then
      OdbcGetString(db,extlinkfieldidx,extlinkfield);
      
//determine key of BN entry for association
   lockey="";
   if extlinkfieldidx>0 then {
      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;
      }
   }

   if ok1 and ok2 then {
      for i=1 to fieldcount do
         DbSetField(lockey,fieldname[i],impfield[i]);
   }
   
   OdbcNextRecord(db);
}

setbusy("Saving database fields");
DbSaveFields;
OdbcClose(db);
setbusy("");