// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// ! 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.                                        !
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

DIALOG dlg;
string fieldlist,field,st,st2;
string formatlist,format,separator;
integer i,dayfirst,year4,pass,ok,vl1,vl2,vl3,day,month,year,totcount,goodcount,savechanges;

formatlist=formatlist+"dd / mm / yy"+"	";
formatlist=formatlist+"dd / mm / yyyy"+"	";
formatlist=formatlist+"mm / dd / yy"+"	";
formatlist=formatlist+"mm / dd / yyyy"+"	";
formatlist=formatlist+"dd - mm - yy"+"	";
formatlist=formatlist+"dd - mm - yyyy"+"	";
formatlist=formatlist+"mm - dd - yy"+"	";
formatlist=formatlist+"mm - dd - yyyy"+"	";

for i=1 to DbGetFieldCount do fieldlist=fieldlist+DbGetFieldName(i)+"	";

DlgAddText(dlg,"Select the field that contains the date you want to convert",15,15,150,30);
DlgAddList(dlg,fieldlist,field,15,55,150,150,"LIST");

DlgAddText(dlg,"Select the existing format:",190,25,130,15);
DlgAddList(dlg,formatlist,format,190,55,130,150,"LIST");

DlgAddText(dlg,"Standard date format after conversion:",15,220,200,15);
DlgAddText(dlg,"yyyy-mm-dd",15,236,130,15);

savechanges=1;
DlgAddCheck(dlg,"Save changes",savechanges,15,260,130,15);

while (field="") or (format="") do {
   if not(DlgShow(dlg,"Convert date to standard format",340,320)) then stop;
   if field="" then message("You must select a field!");
   if (format="") and (field<>"") then message("You must specify the existing format of the field!");
}

separator=substring(format,4,4);
dayfirst=(substring(format,1,2)="dd");
year4=(substring(format,length(format)-3,1000)="yyyy");

for pass=1 to 2 do {
   totcount=0;
   goodcount=0;
   for i=1 to DbGetEntryCount do {
      if i/200=floor(i/200) then setbusy("Scanning database - "+str(i,0,0));
      st=DbGetField(DbGetEntryKey(i),field);
      if st<>"" then {
         ok=0;
         if find(st,separator,1)>0 then {
            vl1=val(splitstring(st,separator));
            if find(st,separator,1)>0 then {
               vl2=val(splitstring(st,separator));
               if st<>"" then {
                  vl3=val(st);
                  ok=1;
               }
            }
         }
         if ok then {
            if dayfirst then { day=vl1; month=vl2; }
            if not(dayfirst) then { day=vl2; month=vl1; }
            year=vl3;
            if not(year4) then {
               if year>50 then year=year+1900;
               if year<=50 then year=year+2000;
            }
            if (day<1) or (day>31) or (month<1) or (month>12) or (year<1900) or (year>2100) then ok=0;
         }
         totcount=totcount+1;
         if (pass=1) and ok then goodcount=goodcount+1;
         if (pass=2) and ok then {
            st2=str(year,0,0);while length(st2)<4 do st2="0"+st2; st=st2;
            st2=str(month,0,0);while length(st2)<2 do st2="0"+st2; st=st+"-"+st2;
            st2=str(day,0,0);while length(st2)<2 do st2="0"+st2; st=st+"-"+st2;
            DbSetField(DbGetEntryKey(i),field,st);
         }
      }
   }
   if (pass=1) and (goodcount<totcount) then {
      DlgReset(dlg);
      DlgAddText(dlg,str(totcount-goodcount,0,0)+" date(s) have an invalid format (total: "+str(totcount,0,0)+")",15,15,270,15);
      DlgAddText(dlg,"Are you sure you want to continue?",15,40,200,15);
      if not(DlgShow(dlg,"WARNING",300,200)) then stop;
   }
}

setbusy("");
if savechanges then DbSaveFields;