function subquery(flg)
%SUBQUERY Visual Query Builder subquery dialog and callbacks.
%   SUBQUERY(FLG) builds the Visual Query Builder subquery dialog and runs
%   its associated callbacks.   The input argument FLG determines which of
%   of the subquery routines to run.    FLG can be input as 'dialog',

%   Author(s): C.F.Garvin, 10-13-98
%   Copyright (c) 1984-2000 The MathWorks, Inc.
%   $Revision: 1.11 $   $Date: 2000/01/20 14:06:54 $

%figure spacing parameters
dfp = get(0,'Defaultfigureposition');
mfp = [560 420];
bspc = mean([5/mfp(2)*dfp(4) 5/mfp(1)*dfp(3)]);
bwid = 80/mfp(1) * dfp(3);;
bhgt = 20/mfp(2) * dfp(4);
fwid = 7*bspc+6*bwid;
fhgt1 = 2*bspc+3*bhgt;
fhgt2 = 12*bhgt-bspc;
fhgt3 = 4*bhgt;
fhgt4 = 6*bhgt;

if nargin == 0
  flg = get(gcbo,'Tag');
end

wcobj = findobj('Tag','swhereclauses');  %Whereclauses object
wfobj = findobj('Tag','swherefields');   %Wherefields object
tobj = findobj('Tag','stables');   %Tables object
fobj = findobj('Tag','sfields');   %Fields object
sourcestr = get(findobj('Tag','ssource'),'String');   %Datasource string
sqobj = findobj('Tag','subquerystr');  %Subquery string object

switch flg
  
  case 'Apply'
    
    %Get userdata of listbox to store whereclause settings 
    wcind = get(gcbo,'Userdata');    %Edit exist clause
    wcdat = get(wcobj,'Userdata');
    if isempty(wcdat)
      wind = 1;
      wcdat = cell(1,5);
    elseif ~isempty(wcind)
      wind = wcind;
    else
      [m,n] = size(wcdat);
      wind = m+1;
    end
    
    set(gcbo,'Userdata',[])   %Reset whereclause index
    
    %Build where clause
    robj = findobj('Tag','srcond','Value',1); %Get condition
    rstr = get(robj,'String');
        wstr = get(wfobj,'String');
    if isempty(wstr), return, end
    wval = get(wfobj,'Value');
    wfld = wstr{wval};
    oobj = findobj('Tag','swops','Value',1); %Get clause operator
    opstr = get(oobj,'String');
    if strcmp(opstr,'None')
      opstr = [];
    else
      opstr = [opstr ' '];
    end
    wcdat{wind,1} = wval;   %Fill general clause data cell array
    wcdat{wind,2} = get(findobj('Tag','srcond'),'Value');
    wcdat{wind,5} = get(findobj('Tag','swops'),'Value');
    
    switch rstr   %Extract for given condition
      
      case 'Relation'
        
        pobj = findobj('Tag','srelop');  %Get relational operator
        pstr = get(pobj,'String');
        pval = get(pobj,'Value');
        relop = pstr{pval};
        vobj = findobj('Tag','srelstring'); %Get constraint value
        vstr = get(vobj,'String');                
        clstr = [wfld ' ' relop ' ' vstr ' ' opstr]; %Build clause
        wcdat{wind,3} = pval; %Fill clause data cell array with Relation specific data
        wcdat{wind,4} = vstr;
        
      case 'Between'
        
        lstr = get(findobj('Tag','slwstring'),'String'); %Get constraints
        ustr = get(findobj('Tag','supstring'),'String');
        clstr = [wfld ' BETWEEN ' lstr ' AND ' ustr ' ' opstr]; %Build clause        
        wcdat{wind,3} = lstr; %Fill clause data cell array with Between specific data
        wcdat{wind,4} = ustr;
        
      case 'In'
        
        istr = get(findobj('Tag','sinstring'),'String'); %Get constraints
        clstr = [wfld ' IN ' istr ' ' opstr];  %Build clause
        wcdat{wind,3} = istr; %Fill clause data cell array with In specific data
        
      case 'Is Null'
        
        clstr = [wfld ' IS NULL ' opstr];  %Build clause
        
      case 'Like'
        
        mstr = get(findobj('Tag','smatchstring'),'String'); %Get constraints
        clstr = [wfld ' LIKE ' mstr ' ' opstr];  %Build clause  
        wcdat{wind,3} = mstr; %Fill clause data cell array with Like specific data
        
      end
      
      %Update where clauses
      wcstr = get(wcobj,'String');
      if ~isempty(wcind)   %Edit existing string
        wcstr{wind} = clstr;
      else
        wcstr = [wcstr;{clstr}];
      end
      set(wcobj,'String',wcstr,'Value',[],'Userdata',wcdat)
      
      %Reset operator radiobuttons and edit boxes
      set(findobj('Tag','swops'),'Value',0)
      set(findobj('Tag','swops','String','None'),'Value',1)
      set(findobj('Userdata','whereedit'),'String',[])
      
      if ~isempty(get(sqobj,'String'))
        subquery('fields')
      end
      
  case 'Cancel'
    
    close
    
  case 'Delete'
    
    %Delete where clauses
    wcstr = get(wcobj,'String');
    wcval = get(wcobj,'Value');
    wcdat = get(wcobj,'Userdata');
    if ~isempty(wcstr)
      wcstr(wcval) = [];
      wcdat(wcval,:) = [];
    end
    set(wcobj,'String',wcstr,'Value',[],'Userdata',wcdat)
    subquery('fields')
    
  case 'dialog'
    
    %Build subquery dialog box or focus if already exists
    sqobj = findobj('Tag','SUBQDLG');
    if ~isempty(sqobj)
      figure(sqobj);
      return
    end

    F = figure('NumberTitle','off','Name','Subquery','Menubar','none',...
      'Resizefcn','querycallbacks(''cleanupdlg'')','Tag','SUBQDLG');
    pos = get(F,'Position');
    set(F,'Position',[pos(1) pos(2) 2*bspc+fwid pos(4)])
    pos = get(F,'Position');
    rgt = pos(3); 
    top = pos(4);

    %Build subquery statement and dialog pushbuttons frame
    uicontrol('Enable','off','Position',[bspc bspc fwid fhgt1]);
    uicontrol('String','Cancel','Callback','subquery','Tag','Cancel',...
      'Tooltip','Close dialog without changes',...
      'Position',[2.5*bspc+1.5*bwid 2*bspc bwid bhgt]);
    uicontrol('String','Help','Callback','subquery(''Help'')',...
      'Position',[3.5*bspc+2.5*bwid 2*bspc bwid bhgt]);
    uicontrol('String','OK','Callback','subquery','Tag','OK',...
      'Tooltip','Accept subquery',...
      'Position',[4.5*bspc+3.5*bwid 2*bspc bwid bhgt]);
    uicontrol('Style','edit','Tag','subquerystr',...
      'Tooltip','Current subquery',...
      'Position',[2*bspc 3*bspc+bhgt 5*bspc+6*bwid bhgt]);
    uicontrol('Style','text','String','SQL subquery statement',...
      'Position',[2*bspc 4*bspc+2*bhgt bwid bhgt]);
        
    %Build advanced subquery uicontrols
    uicontrol('Enable','off','Position',[bspc 3*bspc+fhgt1 fwid fhgt2]);
    uicontrol('Enable','off',...
      'Position',[2*bspc 4*bspc+fhgt1 fwid-2*bspc fhgt3]);
    uicontrol('Style','listbox','Tag','swhereclauses','Max',2,...
      'Callback','subquery(''Edit'')','Tooltip','List of subquery clauses',...
      'Position',[3*bspc 5*bspc+fhgt1 4*bwid 3*bhgt]);
    uicontrol('Style','text','String','Current subquery WHERE clauses',...
      'Position',[3*bspc 5*bspc+fhgt1+3*bhgt bwid bhgt]);
    uicontrol('String','Delete','Tag','Delete','Callback','subquery',...
      'Tooltip','Delete clause',...
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1 bwid bhgt]);
    uicontrol('String','Edit','Tag','Edit','Callback','subquery',...
      'Userdata',0,'Tooltip','Edit Clause',...
      'Position',[6*bspc+5*bwid 8*bspc+fhgt1+bhgt bwid bhgt]);
    uicontrol('String','Group','Tag','Group','Callback','querycallbacks(''groupclauses'')',...
      'Tooltip','Group clause',...
      'Position',[4*bspc+4*bwid 8*bspc+fhgt1+bhgt bwid bhgt]);
    uicontrol('String','Ungroup','Tag','Group','Callback','querycallbacks(''ungroupclauses'')',...
      'Tooltip','Ungroup clauses',...
      'Position',[4*bspc+4*bwid 7*bspc+fhgt1 bwid bhgt]);
    
    uicontrol('Enable','off',...
      'Position',[2*bspc 6*bspc+fhgt1+fhgt3 fwid-2*bspc fhgt4]);
    uicontrol('Style','listbox','Tag','swherefields','Max',2,...
      'Tooltip','Fields in selected table(s)',...
      'Position',[3*bspc 7*bspc+fhgt1+fhgt3 2*bwid 5*bhgt]);
    uicontrol('Style','text','String','Fields',...
      'Position',[3*bspc 7*bspc+fhgt1+fhgt3+5*bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','Like','Tag','srcond',...
      'Callback','subquery',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3 bwid bhgt]);
    uicontrol('Style','radiobutton','String','Is null','Tag','srcond',...
      'Callback','subquery',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3+bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','In','Tag','srcond',...
      'Callback','subquery',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3+2*bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','Between','Tag','srcond',...
      'Callback','subquery',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3+3*bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','Relation','Tag','srcond','Value',1,...
      'Callback','subquery',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3+4*bhgt bwid bhgt]);
    uicontrol('Style','text','String','Condition',...
      'Position',[4*bspc+2*bwid 7*bspc+fhgt1+fhgt3+5*bhgt bwid bhgt]);
    uicontrol('Style','edit','Tag','smatchstring','Userdata','whereedit',...
      'Tooltip','Comparison string or value ie ''abc'' or 1',...
      'Position',[4*bspc+3*bwid 7*bspc+fhgt1+fhgt3 2*bwid bhgt]);
    uicontrol('Style','edit','Tag','sinstring','Userdata','whereedit',...
      'Tooltip','List of comparison strings or values ie (''abc'',''cde'') or (1,2)',...
      'Position',[4*bspc+3*bwid 7*bspc+fhgt1+fhgt3+2*bhgt 2*bwid bhgt]);
    uicontrol('Style','edit','Tag','slwstring','Userdata','whereedit',...
      'Tooltip','Lower bound comparison string or value ie ''abc'' or 1',...
      'Position',[4*bspc+3*bwid 7*bspc+fhgt1+fhgt3+3*bhgt bwid bhgt]);
    uicontrol('Style','edit','Tag','supstring','Userdata','whereedit',...
      'Tooltip','Upper bound comparison string or value ie ''cde'' or 2',...
      'Position',[4*bspc+4*bwid 7*bspc+fhgt1+fhgt3+3*bhgt bwid bhgt]);
    relstring = {'=';'<';'>';'>=';'<=';'<>'};
    uicontrol('Style','popupmenu','Tag','srelop','String',relstring,...
      'Tooltip','Relational operator',...
      'Position',[4*bspc+3*bwid 7*bspc+fhgt1+fhgt3+4*bhgt bwid bhgt]);
    uicontrol('Style','edit','Tag','srelstring','Userdata','whereedit',...
      'Tooltip','Comparison string or value ie ''abc'' or 1',...
      'Position',[4*bspc+4*bwid 7*bspc+fhgt1+fhgt3+4*bhgt bwid bhgt]);
    uicontrol('String','Apply','Tag','Apply','Callback','subquery','Userdata',[],...
      'Tooltip','Add clause',...
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1+fhgt3 bwid bhgt]);
    uicontrol('Style','radiobutton','String','None','Value',1,'Tag','swops',...
      'Callback','subquery','Tooltip','No operator',...
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1+fhgt3+2*bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','OR','Tag','swops','Callback','subquery',...
      'Tooltip','OR operator',...
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1+fhgt3+3*bhgt bwid bhgt]);
    uicontrol('Style','radiobutton','String','AND','Tag','swops','Callback','subquery',...
      'Tooltip','AND operator',...  
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1+fhgt3+4*bhgt bwid bhgt]);
    uicontrol('Style','text','String','Operator',...
      'Position',[6*bspc+5*bwid 7*bspc+fhgt1+fhgt3+5*bhgt bwid bhgt]);
    uicontrol('Style','text','String','Subquery WHERE clauses',...
      'Position',[2*bspc 7*bspc+fhgt1+fhgt3+6*bhgt bwid bhgt]);
    
    %Build source, tables, and fields uicontrols
    uicontrol('Enable','off','Position',[bspc 5*bspc+fhgt1+fhgt2 fwid bspc+4*bhgt]);
    uicontrol('Style','text','String','Data source','Position',[2*bspc top-bhgt bwid bhgt]);
    sobj = findobj('Tag','sources');
    sstr = get(sobj,'String');
    sval = get(sobj,'Value');
    if isempty(sobj) | isempty(sval)
      datasource = [];
    else
      datasource = sstr{sval};
    end  
    uicontrol('Style','text','Fontweight','bold','String',datasource,'Tag','ssource',...
      'Position',[2*bspc top-(bspc+2*bhgt) bwid bhgt]);
    uicontrol('Style','text','String','Tables',...
      'Position',[3*bspc+2*bwid top-bhgt bwid bhgt]);
    tobj = findobj('Tag','tables');
    tstr = get(tobj,'String');
    uicontrol('Style','listbox','String',tstr,'Tag','stables','Max',2,...
      'Value',[],'Callback','subquery(''tables'')','Tooltip','Data source tables',...
      'Position',[3*bspc+2*bwid 6*bspc+fhgt1+fhgt2 bspc+2*bwid bspc+3*bhgt]);
    uicontrol('Style','listbox','Tag','sfields','Max',2,'Value',[],...
      'Callback','subquery(''fields'')','Tooltip','Fields in selected table(s)',...
      'Position',[5*bspc+4*bwid 6*bspc+fhgt1+fhgt2 2*bspc+2*bwid bspc+3*bhgt]);
    uicontrol('Style','text','String','Fields','Position',[5*bspc+4*bwid top-bhgt bwid bhgt]);    
    
    %Retrieve subquery data if editing existing subquery
    tmp = findobj('Tag','WHERE');   %Determine if WHERE or HAVING clause
    if isempty(tmp)
      dlgtag = 'HAVING';
    else
      dlgtag = 'WHERE';
    end
    
    sqdat = getuprop(findobj('Tag',dlgtag),'SUBQUERYDATA');
    sqstr = getuprop(findobj('Tag',dlgtag),'SUBQUERYSTRING');
    tval = getuprop(findobj('Tag',dlgtag),'TABLEVALUES');
    fval = getuprop(findobj('Tag',dlgtag),'FIELDVALUES');
    
    if isempty(sqdat), querycallbacks('cleanupdlg'), return, end
    
    %Update tables, fields and subquery string
    set(findobj('Tag','swhereclauses'),'Userdata',sqdat,'String',sqstr)
    set(findobj('Tag','stables'),'Value',tval)
    subquery('tables')
    set(findobj('Tag','sfields'),'Value',fval)
    subquery('fields')
        
    querycallbacks('cleanupdialog')
    
  case 'Edit'
    
    %Edit selected whereclause
    %Enable listbox double click behavior
    if strcmp(get(gcbo,'Style'),'listbox')
      pbobj = findobj(gcf,'Tag','Edit','Style','pushbutton');
      if (now - get(pbobj,'Userdata')) > 5e-6
        set(pbobj,'Userdata',now)
        set(findobj('Type','figure'),'Pointer','arrow')
        return
      end
    end
                 
    %Clear edit boxes
    set(findobj('Userdata','whereedit'),'String',[])
        
    %Fill boxes with data from selected clause
    wcdat = get(wcobj,'Userdata');
    wcval = get(wcobj,'Value');
    if isempty(wcval), return, end
    if length(wcval) > 1
      wcval = min(wcval);
      set(wcobj,'Value',wcval)
    end
    set(findobj(gcf,'String','Apply'),'Userdata',wcval) %Store index of edit line
    set(wfobj,'Value',wcdat{wcval,1})
    set(findobj(gcf,'Tag','srcond'),{'Value'},wcdat{wcval,2})
    set(findobj(gcf,'Tag','swops'),{'Value'},wcdat{wcval,5})
    
    %Fill condition specific uicontrols
    tmp = wcdat{wcval,2};
    cind = find([tmp{:}]);
    
    switch cind
      
      case 1  %Relation
        set(findobj(gcf,'Tag','srelop'),'Value',wcdat{wcval,3})
        set(findobj(gcf,'Tag','srelstring'),'String',wcdat{wcval,4})
        
      case 2  %Between
        set(findobj(gcf,'Tag','slwstring'),'String',wcdat{wcval,3})
        set(findobj(gcf,'Tag','supstring'),'String',wcdat{wcval,4})
        
      case 3  %In
        set(findobj(gcf,'Tag','sinstring'),'String',wcdat{wcval,3})
        
      case 4  %Is Null
        
      case 5  %Like
        set(findobj(gcf,'Tag','smatchstring'),'String',wcdat{wcval,3})
        
    end
    
  case 'fields'
    
    %Clear current subquery
    set(sqobj,'String',[])
    
    %Get selected tables
    tstr = get(tobj,'String');
    tval = get(tobj,'Value');
    tablez = tstr(tval);
    table = [];
    for i = 1:length(tablez)
      table = [table tablez{i} ', '];
    end
    L = length(table);
    table = table(1:L-2);
    
    %Get selected fields
    fstr = get(fobj,'String');
    fval = get(fobj,'Value');
    fields = fstr(fval);
    if isempty(fields), return, end  %Cannot build query without selected fields
    field = [];
    for i = 1:length(fields)
      field = [field fields{i} ', '];
    end
    L = length(field);
    field = field(1:L-2);
    
    %Get current subquery where clauses
    wstr = get(wcobj,'String');
    whereclause = [];
    for i = 1:length(wstr)
      whereclause = [whereclause wstr{i}];
    end
    
    %Build subquery without WHERE
    subquerystr = ['SELECT ' field ' FROM ' table];
    
    %Add where clauses
    if ~isempty(whereclause)
      subquerystr = [subquerystr ' WHERE ' whereclause];
    end
    
    %Display subquery
    set(sqobj,'String',subquerystr)
    
  case 'Help'
    
    qbhelp('Subquery')
    
  case 'OK'
    
    %Test if subquery executes without error
    conn = loginconnect(sourcestr);
    subquerystr = get(sqobj,'String');
    e = exec(conn,subquerystr);
    if ~isempty(e.Message)
      errordlg(e.Message)
      try
        close(e)
      catch
      end
      close(conn)
      return
    end
    close(e)
    close(conn)
    
    %Get subquery userdata and string and store in WHERE or HAVING clause dialog
    dobj = findobj('Tag','WHERE');
    if isempty(dobj)
      dobj = findobj('Tag','HAVING');
    end
    if isempty(dobj)
      errordlg('WHERE or HAVING clauses dialog not found.  Unable to save subquery.'), return
    end
    
    wcdat = get(wcobj,'Userdata');
    wcstr = get(wcobj,'String');
    if ~isempty(subquerystr)
      subquerystr = ['(' subquerystr ')'];
    end
    
    %Store userdata, sql string, table listbox and field listbox values
    setuprop(dobj,'SUBQUERYDATA',wcdat)
    setuprop(dobj,'SUBQUERYSTRING',wcstr) 
    setuprop(dobj,'TABLEVALUES',get(tobj,'Value'));
    setuprop(dobj,'FIELDVALUES',get(fobj,'Value'));
    
    condstr = get(findobj('Tag','rcond','Value',1),'String');
    switch condstr
      case 'Relation'
        set(findobj('Tag','relstring'),'String',subquerystr)
      case 'In'
        set(findobj('Tag','instring'),'String',subquerystr)
    end
    
    close(findobj('Tag','SUBQDLG'))  %close subquery dialog
    figure(dobj) %Focus on where clause dialog
    
  case {'swops','srcond'} 
    
    %Radio button toggling
    robj = findobj('Tag',flg);
    set(robj,'Value',0)
    set(gcbo,'Value',1)
    
    %Clear edit boxes if Condition box is checked
    if strcmp(flg,'srcond')
      set(findobj('Userdata','whereedit'),'String',[])
    end
        
  case 'tables'
    
    %Make connection to datasource
    conn = loginconnect(sourcestr);
    
    %Find field names for selected tables
    tstr = get(tobj,'String');
    tval = get(tobj,'Value');
    tablez = tstr(tval);
    
    %Check for spaces in table name
    for i = 1:length(tablez)
      tmptab = tablez{i};
      L = length(tmptab);
      i = find(tmptab == ' ');
      if any(i < L)
        errordlg('Blank spaces in tablenames are not supported.'), return
      end
    end
    
    %Get metadata of connection and get column names
    d = dmd(conn);
    L = length(tablez);
    fields = {};
    for i = 1:L
      
      table = tablez{i};  %Columns method returns more info, but is much slower
      ex = exec(conn,['select * from ' table]);
      f = fetch(ex,1);
      tmp = columnnames(f);
      eval(['tabfields = {' tmp '};'])
      close(f);close(ex); 
      
      %Prepend tablename if JOIN operation
      if length(tval) > 1
        for j = 1:length(tabfields)
          tabfields{j} = [table '.' tabfields{j}];
        end
        fields = [fields;tabfields'];
      else
        fields = tabfields';
      end
    end
     
    %Display fieldnames
    set([fobj;wfobj],'String',fields,'Value',[]);
    set(wfobj,'Value',1,'Max',1)
    
    %Close connection
    close(conn);  
    
    %Clear current subquery
    set(sqobj,'String',[])
    
end
