selectlist.pas

DbAnyWhere开发套件

   //替换后
   th_list:Tstringlist;
   //分析customlist
   cur_cus:string;
   cur_list:Tstringlist;
   ifgoto:boolean;
   label nocus;
begin
  str:='';
  sql:='';
  temp_str:='';
  //判断
  if self.FRemoteDataSet=nil then
  begin
    showmessage('请指定执行代码的数据集组件！');
    abort;
  end;
  if length(trim(fsql.Text))=0 then
  begin
    showmessage('请输入要执行的查询代码！');
    abort;
  end;
  //showmessage(fsql.Text);
  //判断是否选定的内容录入了信息
  for i:=0 to FSelectList.Count-1 do
  begin
    if TFieldPanel(FindComponent(FselectList.Names[i])).FSelectCheck.Checked then
    begin
      //判断内容是否录入了
      if copy(FselectList.ValueFromIndex[i],2,1)='0' then
      begin
        if length(trim(TFieldPanel(FindComponent(FselectList.Names[i])).FLuruEdit1.Text))=0 then
        begin
          showmessage(TFieldPanel(FindComponent(FselectList.Names[i])).FSelectCheck.Caption+'不能够为空！');
          TFieldPanel(FindComponent(FselectList.Names[i])).FLuruEdit1.setfocus;
          exit;
        end;
        if TFieldPanel(FindComponent(FselectList.Names[i])).FCalComboBox.Text='between' then
        if length(trim(TFieldPanel(FindComponent(FselectList.Names[i])).FLuruEdit2.Text))=0 then
        begin
          showmessage(TFieldPanel(FindComponent(FselectList.Names[i])).FSelectCheck.Caption+'不能够为空！');
          TFieldPanel(FindComponent(FselectList.Names[i])).FLuruEdit2.setfocus;
          exit;
        end;
      end;
    end;
  end;
  //分析sql语句
  str:=FSQL.Text;
  ifgoto:=true;
  //if pos('<<',str)=0 then str:='<<'+str+'>>';
  if (pos('<<',str)=0)and(pos('[',str)=0) then goto nocus;
  ifgoto:=false;
  if (pos('<<',str)=0)and(pos('[',str)>0) then str:='<<'+str+'>>';
  cuslist:=Tstringlist.Create;
  while pos('<<',str)>0 do
  begin
    pos1:=pos('<<',str);
    pos2:=pos('>>',str);
    if pos1>0 then
    begin
      cur_str:=copy(str,pos1,pos2-pos1+2);
      cur_str1:=replace(cur_str,'<<','  ',false);
      cur_str1:=replace(cur_str1,'>>','  ',false);
      cuslist.Add(cur_str);
      str:=replace(str,cur_str,cur_str1,false);
    end;
  end;
  for i:=0 to cuslist.Count-1 do
  begin
    str:=cuslist.Strings[i];
    while pos('[',str)>0 do
    begin
      pos1:=pos('[',str);
      pos2:=pos(']',str);
      if pos1>0 then
      begin
        cur_str:=copy(str,pos1,pos2-pos1+1);
        cur_str1:=replace(cur_str,'[','  ',false);
        cur_str1:=replace(cur_str1,']','  ',false);
        cuslist.Strings[i]:=cur_str+'='+str;
        str:=replace(str,cur_str,cur_str1,false);
      end;
    end;
  end;
  //分析完毕
  //showmessage('分析完毕'+cuslist.Text);
  th_list:=Tstringlist.Create;
  //需替换的列表
  th_list.Assign(cuslist);
  //cuslist.Free;
  //分析customlist
  cur_list:=tstringlist.Create;
  //showmessage(self.FCustomList.Text);
  for j:=self.FCustomList.Count-1 downto 0 do
  begin
    if copy(trim(self.FCustomList[j]),1,1)='[' then
    begin
      cur_cus:=self.FCustomList[j];
      //showmessage(cur_cus+#13+cur_list.Text);
      //已经获得一组对照内容,开始组合sql语句并替换生成一个cus
      str:='';
      //showmessage('提取:'+#13+cur_list.Text);
      for i:=0 to cur_list.Count-1 do
      begin
        //生成附加sql语句
        //showmessage(cur_list.ValueFromIndex[i]);
        if (TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FSelectCheck.Checked) then
        begin
          if TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FCalComboBox.Text<>'between' then
          begin
            temp_str:=TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FLuruEdit1.Text ;
            if TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FCalComboBox.Text='like' then
            if leftstr(temp_str,1)<>'%' then
            TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FLuruEdit1.Text:='%'+
            TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FLuruEdit1.Text;
            if TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FCalComboBox.Text='like' then
            if rightstr(temp_str,1)<>'%' then
            TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FLuruEdit1.Text:=
            TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FLuruEdit1.Text+'%';
            str:=str+cur_list.Names[i]+' '+TFieldPanel(FindComponent(cur_list.ValueFromIndex[i])).FCalComboBox.Text+
            ' :'+cur_list.ValueFromIndex[i]+'*'+inttostr(j)+' and ';
          end else
          str:=str+'('+cur_list.Names[i]+' between :'+ trim(cur_list.ValueFromIndex[i])+'*'+inttostr(j)+'^1 and :'+
          trim(cur_list.ValueFromIndex[i])+'*'+inttostr(j)+'^2) and ';
        end;
      end;
      //替换th_list
      //语句处理
      if length(trim(str))>0 then str:=leftstr(str,length(str)-4);
      str1:=trim(str);
      str:=replace(th_list.Values[cur_cus],cur_cus,str,false);
      //showmessage(sql);
      if length(trim(str1))=0 then
      begin
        str:=replace(str,'&','',false);
        str:=replace(str,'$','',false);
      end else
      begin
        str:=replace(str,'&','and',false);
        str:=replace(str,'$','where',false);
      end;
      str:=replace(str,'<<','  ',false);
      str:=replace(str,'>>','  ',false);
      th_list.Values[cur_cus]:=str;
      //showmessage(str);
      /////////////////
      cur_list.Clear;
    end else
    begin
      cur_list.Add(self.FCustomList[j]);
    end;
  end;
  //替换完毕
  //showmessage('替换完毕'+th_list.Text);
  cur_list.Free;
  //替换原来的sql语句
  str:=self.SQL.Text;
  if pos('<<',str)=0 then str:='<<'+str+'>>';
  for i:=0 to cuslist.Count-1 do
  begin
    sql:=replace(str,cuslist.ValueFromIndex[i],th_list.ValueFromIndex[i],false);
  end;
  th_list.Free;
  cuslist.Free;
  //showmessage('最终SQL语句:'+sql);
  nocus:
  self.FRemoteDataSet.Remoteclose;
  if ifgoto then self.FRemoteDataSet.RTSQL.Text:=self.SQL.Text else
  self.FRemoteDataSet.RTSQL.Text:=sql;
  //参数
  self.FRemoteDataSet.PrepareParams;
  //循环处理参数
  //showmessage(FselectList.Text);
  //if (pos('<<',str)=0)and(pos('[',str)=0) then

  for i:=0 to self.FRemoteDataSet.RTParams.Count-1 do
  begin
    tsn:=pos('*',self.FRemoteDataSet.RTParams[i].Name);
    ssn:=pos('^',self.FRemoteDataSet.RTParams[i].Name);
    TFieldPanel(FindComponent(copy(self.FRemoteDataSet.RTParams[i].Name,1,tsn-1))).FSelectCheck.Checked:=true;
    if ssn=0 then
    begin
      if tsn>0 then
      begin
        //类型
        //showmessage(self.FRemoteDataSet.RTParams[i].Name);
        self.FRemoteDataSet.RTParams.SetParamType(
        self.FRemoteDataSet.RTParams[i].Name,
          TFieldtype(strtoint(copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],3,2))));
        //参数值
        if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='0' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruEdit1.Text;
        if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='1' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruDateTime1.Date;
        if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='2' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruCheck.Checked;
      end else
      begin
        //类型
        self.FRemoteDataSet.RTParams.SetParamType(
        FselectList.Names[FselectList.indexofname(self.FRemoteDataSet.RTParams[i].Name)],
          TFieldtype(strtoint(copy(FselectList.Values[self.FRemoteDataSet.RTParams[i].Name],3,2))));
        //参数值
        if copy(FselectList.Values[self.FRemoteDataSet.RTParams[i].Name],2,1)='0' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(self.FRemoteDataSet.RTParams[i].Name)).FLuruEdit1.Text;
        if copy(FselectList.Values[self.FRemoteDataSet.RTParams[i].Name],2,1)='1' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(self.FRemoteDataSet.RTParams[i].Name)).FLuruDateTime1.Date;
        if copy(FselectList.Values[self.FRemoteDataSet.RTParams[i].Name],2,1)='2' then
           self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
           TFieldPanel(FindComponent(self.FRemoteDataSet.RTParams[i].Name)).FLuruCheck.Checked;
      end;
    end else
    begin
        //类型
        self.FRemoteDataSet.RTParams.SetParamType(
        self.FRemoteDataSet.RTParams[i].Name,
          TFieldtype(strtoint(copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],3,2))));
        //showmessage(FselectList.Names[FselectList.indexofname(
        //leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))]);
        //参数值
        if rightstr(self.FRemoteDataSet.RTParams[i].Name,1)='2' then
        begin
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='0' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruEdit2.Text;
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='1' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruDateTime2.Date+1;
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='2' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruCheck.Checked;
        end;
        if rightstr(self.FRemoteDataSet.RTParams[i].Name,1)='1' then
        begin
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='0' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruEdit1.Text;
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='1' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruDateTime1.Date;
          if copy(FselectList.Values[leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1)],2,1)='2' then
             self.FRemoteDataSet.RTParams.ParamValues[self.FRemoteDataSet.RTParams[i].Name]:=
             TFieldPanel(FindComponent(leftstr(self.FRemoteDataSet.RTParams[i].Name,tsn-1))).FLuruCheck.Checked;
        end;
    end;
  end;
  //执行语句  
  //showmessage(self.FRemoteDataSet.RTSQL.Text);
  {if length(trim(self.FPageId))=0 then
  begin
    self.FRemoteDataSet.RTSelectType:=selectall;
  end else
  begin
    self.FRemoteDataSet.RTSelectType:=selectpage;
    self.FRemoteDataSet.RTTableID:=self.FPageId;
    self.FRemoteDataSet.RTPageSize:=100;
  end;}
  self.FRemoteDataSet.RemoteOpen;
  //self.FRemoteDataSet.RTSelectType:=selectall;
end;
procedure TSelectList.setdataselect(value:TDataSelect);
begin
  FDataSelect:=Value;
end;

end.