caldbgrid.pas

p2pdbanywhere 可以通过之udp方式远程连接数据库的组件,这个组件非常好,是Delphi7,9,2006能够使用,包含源码.

    begin
      //if self.SelectedField.Value=FCalvalue then 
      //找到对应字段以后,按照计算公式进行计算
      if self.FCalItems[i].FCalType=CTCalExpression then
      begin
        for j:=0 to self.FCalItems[i].FCalExpression.Count-1 do
        begin
          //解析公式
          FCE.VarLists.Text:='';
          expression:=self.FCalItems[i].FCalExpression.ValueFromIndex[j];
          fce.Expression:=expression;
          //设置参数值
          for k:=0 to FCE.VarLists.Count-1 do
          begin
            //showmessage(floattostr(self.FieldColumns[FCE.VarLists.Names[k]].Field.AsFloat));
            FCE.SetValue(FCE.VarLists.Names[k],
            self.FieldColumns[FCE.VarLists.Names[k]].Field.AsFloat);
          end;
          //计算字段
          self.FieldColumns[self.FCalItems[i].FCalExpression.Names[j]].Field.AsFloat:=self.FCE.Calculate;
          //showmessage(self.FCalItems[i].FCalExpression.Names[j]);
          //if self.FieldColumns[self.FCalItems[j].FCalExpression.Names[j]].Field.AsFloat<=0 then
          //self.FieldColumns[self.FCalItems[j].FCalExpression.Names[j]].Field.Value:=null;
        end;
      end;
      //找到字段按照查询赋值的方式计算
      if self.FCalItems[i].FCalType=CTSelectdatabase then
      begin
        if self.SelectedField.Value=FCalValue then exit;
        //先进行查询
        //temp_sql:=replace(self.FCalItems[i].FSQL.Text,'<value>',
        //                             ''''+self.SelectedField.AsString+'''',false);
        temp_sql:=self.FCalItems[i].FSQL.Text;
        //解析sql语句
        //////////////////////////////////////////////////////////////////////////
        if self.FCalItems[i].FRemoteDataSet<>nil then
        begin
        //*************************//
        self.FCalItems[i].FRemoteDataSet.CommandText:=self.FCalItems[i].FSQL.Text;
        //解析参数
        for h:=0 to self.FCalItems[i].FRemoteDataSet.Params.Count-1 do
        begin
          //showmessage(vartostr(self.DataSource.DataSet.FieldByName(
          //self.FCalItems[i].FRemoteDataSet.Params[h].Name).DataType));
          //根据参数类型来替换
          if self.DataSource.DataSet.FieldByName(
          self.FCalItems[i].FRemoteDataSet.Params[h].Name).DataType in
             [ftdate,ftdatetime,fttime,ftstring,ftmemo,ftFixedChar,ftWideString] then
          begin
            temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FRemoteDataSet.Params[h].Name,
            ''''+self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FRemoteDataSet.Params[h].Name).AsString+'''',false);
          end else if self.DataSource.DataSet.FieldByName(
                      self.FCalItems[i].FRemoteDataSet.Params[h].Name).DataType=ftboolean then
          begin
            if self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FRemoteDataSet.Params[h].Name).AsBoolean then
              temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FRemoteDataSet.Params[h].Name,
              '1',false)
            else
              temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FRemoteDataSet.Params[h].Name,
              '0',false);
          end else
          begin
            temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FRemoteDataSet.Params[h].Name,
            self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FRemoteDataSet.Params[h].Name).AsString,false);
          end;
        end;
        //****************************//
        end;
        if self.FCalItems[i].FADOQuery<>nil then
        begin
        //*************************//
        self.FCalItems[i].FADOQuery.SQL.Text:=self.FCalItems[i].FSQL.Text;
        //解析参数
        for h:=0 to self.FCalItems[i].FADOQuery.Parameters.Count-1 do
        begin
          //showmessage(vartostr(self.DataSource.DataSet.FieldByName(
          //self.FCalItems[i].FRemoteDataSet.Params[h].Name).DataType));
          //根据参数类型来替换
          if self.DataSource.DataSet.FieldByName(
          self.FCalItems[i].FADOQuery.Parameters[h].Name).DataType in
             [ftdate,ftdatetime,fttime,ftstring,ftmemo,ftFixedChar,ftWideString] then
          begin
            temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FADOQuery.Parameters[h].Name,
            ''''+self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FADOQuery.Parameters[h].Name).AsString+'''',false);
          end else if self.DataSource.DataSet.FieldByName(
                      self.FCalItems[i].FADOQuery.Parameters[h].Name).DataType=ftboolean then
          begin
            if self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FADOQuery.Parameters[h].Name).AsBoolean then
              temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FADOQuery.Parameters[h].Name,
              '1',false)
            else
              temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FADOQuery.Parameters[h].Name,
              '0',false);
          end else
          begin
            temp_sql:=replace(temp_sql,':'+self.FCalItems[i].FADOQuery.Parameters[h].Name,
            self.DataSource.DataSet.fieldbyname(
            self.FCalItems[i].FADOQuery.Parameters[h].Name).AsString,false);
          end;
        end;
        //判断是否需要同步
        if self.FCalItems[i].FCanSyn then
        begin
          if length(trim(self.FCalItems[i].FSynTableName))=0 then
          begin
            showmessage('请设定需要数据同步的表名！');
            exit;
          end;
          //先同步本地的
          self.FSyn.ADOConnection:=self.FCalItems[i].FADOConnection;
          self.FSyn.RemoteHttpConnection:=self.FCalItems[i].RemoteHttpConnection;
          self.FSyn.RemoteUdpConnection:=self.FCalItems[i].RemoteUdpConnection;
          if self.FCalItems[i].RemoteUdpConnection.IsOnLine then
             self.FSyn.SynDataBase(self.FCalItems[i].FSynTableName);  
        end;
        //****************************//
        end;
        ////////////////////////////////////////////////////////////////////////////////////
        //showmessage(temp_sql);
        //执行
        if self.FCalItems[i].FRemoteDataSet<>nil then
        begin
          self.FCalItems[i].FRemoteDataSet.Remoteclose;
          self.FCalItems[i].FRemoteDataSet.RTSQL.Text:=temp_sql;
          //self.FCalItems[i].FRemoteDataSet.RTSQL.SaveToFile('c:\ok.txt');
          self.FCalItems[i].FRemoteDataSet.RemoteOpen;
          if self.FCalItems[i].FRemoteDataSet.RecordCount=0 then
          begin
            self.SelectedField.AsString:='';
            //showmessage('输入内容不正确！');
          end;
        end;
        if self.FCalItems[i].FADOQuery<>nil then
        begin
          self.FCalItems[i].FADOQuery.Close;
          self.FCalItems[i].FADOQuery.SQL.Text:=temp_sql;
          self.FCalItems[i].FADOQuery.Open;
          if self.FCalItems[i].FADOQuery.RecordCount=0 then
          begin
            self.SelectedField.AsString:='';
            //showmessage('输入内容不正确！');
          end;
        end;
        for j:=0 to self.FCalItems[i].FCalExpression.Count-1 do
        begin
          //解析公式
          fce.VarLists.Text:='';
          expression:=self.FCalItems[i].FCalExpression.ValueFromIndex[j];
          fce.Expression:=expression;    
          //判断参数个数，一个的话直接是查询赋值，多于一个是计算
          if FCE.VarLists.Count=1 then
          begin
            //通过查询结果赋值
            if self.FCalItems[i].FRemoteDataSet<>nil then
            begin
              self.DataSource.DataSet.Edit;
              //self.FieldColumns[self.FCalItems[i].FCalExpression.Names[j]].Field.asstring:=
              self.DataSource.DataSet.FieldByName(self.FCalItems[i].FCalExpression.Names[j]).asstring:=
              self.FCalItems[i].FRemoteDataSet.fieldbyname(self.FCalItems[i].FCalExpression.ValueFromIndex[j]).AsString;
            end;
            if self.FCalItems[i].FADOQuery<>nil then
            begin
              self.DataSource.DataSet.edit;
              //self.FieldColumns[self.FCalItems[i].FCalExpression.Names[j]].Field.asstring:=
              self.DataSource.DataSet.FieldByName(self.FCalItems[i].FCalExpression.Names[j]).asstring:=
              self.FCalItems[i].FADOQuery.fieldbyname(self.FCalItems[i].FCalExpression.ValueFromIndex[j]).AsString;
            end;
          end else
          begin
            //设置参数值
            for k:=0 to FCE.VarLists.Count-1 do
            begin
              //showmessage(floattostr(self.FieldColumns[FCE.VarLists.Names[k]].Field.AsFloat));
              FCE.SetValue(FCE.VarLists.Names[k],
              self.FieldColumns[FCE.VarLists.Names[k]].Field.AsFloat);
            end;
            //计算字段
            //self.FieldColumns[self.FCalItems[i].FCalExpression.Names[j]].Field.AsFloat:=self.FCE.Calculate;
            self.DataSource.DataSet.FieldByName(self.FCalItems[i].FCalExpression.Names[j]).AsFloat:=self.FCE.Calculate;
          end;
        end;
      end;
    end;
  end;
  //self.SumList.Activate(true);
end;
procedure TCalDbGrid.keypress(var Key: Char);
//var
   //i:integer;
begin
  if key=#13 then
  begin
    self.DataSource.Edit;
    self.DataSource.DataSet.Post;
    cur_index:=self.SelectedIndex;
    //if length(trim(self.SelectedField.DisplayText))>0 then
    begin
      //showmessage(inttostr(self.SelectedIndex));
      //self.Canvas.TextOut(0,0,inttostr(self.SelectedIndex));
      //合计最大
      {num:=0;
      for i:=0 to self.Columns.Count-1 do
      begin
        if self.Columns[i].Visible then num:=num+1;
      end;
      if self.SelectedIndex<num then }
      self.SelectedIndex:=self.SelectedIndex+1;
      if cur_index=self.SelectedIndex then
      begin
        key:=#0;
        cur_index:=0;
        sendkeys(#9,false);
        //self.SelectedIndex:=0;
        exit;
      end;
      if self.Columns[self.SelectedIndex].ReadOnly then
      begin
        self.keypress(key);
      end;    
    end;
    {if (length(trim(self.SelectedField.DisplayText))>0)or
       (self.SelectedField.ReadOnly) then sendkeys(#9,false); }
  end;
end;
procedure TCalDbGrid.colenter;
begin
  if (self.DataSource<>nil)and(self.DataSource.DataSet<>nil) then
  begin
    self.DataSource.OnUpdateData:=self.DataChange;
    self.DataSource.DataSet.AfterScroll:=self.afterScroll;
  end;
  //showmessage(self.SelectedField.AsString);
  FCalValue:=self.SelectedField.Value;
end;
procedure TCalDbGrid.DataChange(Sender: TObject);
begin
  //showmessage(vartostr(self.SelectedField.Value));
  //FCalValue:=self.SelectedField.Value;
  self.calculate;
end;
procedure TCalDbGrid.afterScroll(DataSet: TDataSet);
begin
  FCalValue:=self.SelectedField.Value;
end;
procedure TCalDbGrid.colexit;
begin
  //showmessage(self.SelectedField.AsString);
  self.calculate;    
end;

end.