databrowseformunit1.pas

delphi开发的抄表数据管理系统

procedure TDataBrowseForm.ToolButton21Click(Sender: TObject);
begin
    if toolbutton21.Down then
    begin
        pagecontrol1.Visible:=true;
    end
    else
    begin
        pagecontrol1.Visible:=false;
    end;
end;

procedure TDataBrowseForm.ToolButton9Click(Sender: TObject);
var
    eclApp,WorkBook,sheet:Variant;{声明为OLE Automation对象}
    i,j:integer;
begin
    try
      eclApp:=CreateOleObject('Excel.Application');
    except
      ShowMessage('您的机器里未安装Microsoft Excel.');
      Exit;
    end;
    if datasource1.DataSet.State=dsbrowse then
    begin
        if datasource1.DataSet.RecordCount<>0 then
        begin
            screen.Cursors[crMyCursor]:=LoadCursorFromFile('bgwork.ani');
            screen.Cursor :=crMyCursor;
            WorkBook:=eclApp.WorkBooks.Add(extractfilepath(application.ExeName)+'模版.xls');
            eclApp.WorkSheets[7].Activate;
            eclApp.displayAlerts:=false;
            eclApp.ScreenUpdating:=false;
            j:=1;
             datasource1.DataSet.First;
             for i:=0 to datasource1.DataSet.RecordCount-1 do
            begin
                WorkBook.Sheets[7].cells[j+1,1].value:=inttostr(i+1);
                WorkBook.Sheets[7].cells[j+1,2].value:=datasource1.DataSet.FieldByName('注册号').AsString;
                WorkBook.Sheets[7].cells[j+1,3].value:=datasource1.DataSet.FieldByName('用户名称').AsString;
                WorkBook.Sheets[7].cells[j+1,4].value:=datasource1.DataSet.FieldByName('本期指数').AsString;
                WorkBook.Sheets[7].cells[j+1,5].value:=datasource1.DataSet.FieldByName('本期流量').AsString;
                WorkBook.Sheets[7].cells[j+1,6].value:=formatdatetime('yyyy-mm-dd',datasource1.DataSet.FieldByName('设备上传时间').AsDateTime);
                WorkBook.Sheets[7].cells[j+1,7].value:=datasource1.DataSet.FieldByName('区号').AsString;
                WorkBook.Sheets[7].cells[j+1,8].value:=datasource1.DataSet.FieldByName('本号').AsString;
                WorkBook.Sheets[7].cells[j+1,9].value:=datasource1.DataSet.FieldByName('设备类型').AsString;
                WorkBook.Sheets[7].cells[j+1,10].value:=datasource1.DataSet.FieldByName('问题标记').AsString;
                WorkBook.Sheets[7].cells[j+1,11].value:=datasource1.DataSet.FieldByName('故障标记').AsString;
                datasource1.DataSet.Next;
                inc(j);
            end;
            Screen.Cursors[crMyCursor]:=crdefault;
            Screen.Cursor:=crdefault;
            eclApp.Visible := True;
            eclApp.ActiveSheet.PrintPreview;
            WorkBook.Close;
            eclApp.Quit;//退出Excel Application
            eclApp:=Unassigned;
        end
        else
        begin
            application.MessageBox('无可用数据！','提示',mb_iconinformation);
            exit;
        end;
    end
    else
        application.MessageBox('记录集尚未打开！','错误',mb_iconerror);
end;

procedure TDataBrowseForm.ToolButton11Click(Sender: TObject);
var
    eclApp,WorkBook,sheet:Variant;{声明为OLE Automation对象}
    i,j:integer;
begin
    try
      eclApp:=CreateOleObject('Excel.Application');
    except
      ShowMessage('您的机器里未安装Microsoft Excel.');
      Exit;
    end;
    if datasource1.DataSet.State=dsbrowse then
    begin
        if datasource1.DataSet.RecordCount<>0 then
        begin
            screen.Cursors[crMyCursor]:=LoadCursorFromFile('bgwork.ani');
            screen.Cursor :=crMyCursor;
            WorkBook:=eclApp.WorkBooks.Add(extractfilepath(application.ExeName)+'抄表数据查询.xls');
            eclApp.WorkSheets[1].Activate;
            eclApp.displayAlerts:=false;
            eclApp.ScreenUpdating:=true;
            j:=1;
            datasource1.DataSet.First;
             for i:=0 to datasource1.DataSet.RecordCount-1 do
            begin
                WorkBook.Sheets[1].cells[j+1,1].value:=inttostr(i+1);
                WorkBook.Sheets[1].cells[j+1,2].value:=datasource1.DataSet.FieldByName('注册号').AsString;
                WorkBook.Sheets[1].cells[j+1,3].value:=datasource1.DataSet.FieldByName('用户名称').AsString;
                WorkBook.Sheets[1].cells[j+1,4].value:=datasource1.DataSet.FieldByName('本期指数').AsString;
                WorkBook.Sheets[1].cells[j+1,5].value:=datasource1.DataSet.FieldByName('本期流量').AsString;
                WorkBook.Sheets[1].cells[j+1,6].value:=formatdatetime('yyyy-mm-dd',datasource1.DataSet.FieldByName('设备上传时间').AsDateTime);
                WorkBook.Sheets[1].cells[j+1,7].value:=datasource1.DataSet.FieldByName('区号').AsString;
                WorkBook.Sheets[1].cells[j+1,8].value:=datasource1.DataSet.FieldByName('本号').AsString;
                WorkBook.Sheets[1].cells[j+1,9].value:=datasource1.DataSet.FieldByName('设备类型').AsString;
                WorkBook.Sheets[1].cells[j+1,10].value:=datasource1.DataSet.FieldByName('问题标记').AsString;
                WorkBook.Sheets[1].cells[j+1,11].value:=datasource1.DataSet.FieldByName('故障标记').AsString;
                datasource1.DataSet.Next;
                inc(j);
            end;
            {savedialog1.Filter:='Microsoft Excel 工作簿 (*.xls)|*.xls';
            if savedialog1.Execute then
            begin
                eclapp.ActiveSheet.saveas(savedialog1.FileName+'.xls')
            end;}
            Screen.Cursors[crMyCursor]:=crdefault;
            Screen.Cursor:=crdefault;
            //WorkBook.Close;
            //eclApp.Quit;//退出Excel Application
            eclApp.visible:=true;
            eclApp:=Unassigned;
        end
        else
        begin
            application.MessageBox('无可用数据！','提示',mb_iconinformation);
            exit;
        end;
    end
    else
        application.MessageBox('记录集尚未打开！','错误',mb_iconerror);
end;

procedure TDataBrowseForm.DBGrid1TitleClick(Column: TColumn);
var
  str:string;
begin
  str:=adoquery1.Sort;
  if length(str)=0 then
     adoquery1.Sort:=column.Title.Caption+' DESC'
  else
  begin
      if pos('DESC',str)<>0 then
      begin
          if trim(leftbstr(str,pos('DESC',str)-1))=column.Title.Caption  then
              adoquery1.Sort:=column.Title.Caption+' ASC'
          else
              adoquery1.Sort:=column.Title.Caption+' DESC';
      end
      else if pos('ASC',str)<>0 then
      begin
          if trim(leftbstr(str,pos('ASC',str)-1))=column.Title.Caption  then
              adoquery1.Sort:=column.Title.Caption+' DESC'
          else
              adoquery1.Sort:=column.Title.Caption+' ASC';
      end;
  end;
end;

procedure TDataBrowseForm.SpeedButton4Click(Sender: TObject);
var
    sqlstr:string;
begin
    sqlstr:='select 注册号,设备类型,left(用户名称,10) as 用户名称,本期指数,本期流量,人工读数,估收水量,问题标记,故障标记,上期指数,上期流量,停转时间,电池电压,计数轮,定点时间,设备上传时间,';
    sqlstr:=sqlstr+'设备当前时钟,数据来源,停转开始时间,当前时间,上传模式,区号,本号,牌号,抄表员号,设备ID号,地址,是否导出,备注 from 问题数据分析 where';
    if checkbox31.Checked then
       sqlstr:=sqlstr+ ' 区号='''+getNum(trim(combobox20.Text))+''' and ';
    if checkbox32.Checked then
       sqlstr:=sqlstr+ ' 本号='''+getNum(stringofchar('0',4-length(trim(combobox21.Text)))+trim(combobox21.Text))+''' and ';
    if checkbox33.Checked then
       sqlstr:=sqlstr+ ' 抄表员号='''+getNum(stringofchar('0',4-length(trim(combobox22.Text)))+trim(combobox22.Text))+''' and ';
    if datetimepicker6.Checked then
       sqlstr:=sqlstr+' 设备上传时间>='''+formatdatetime('yyyy-mm-dd hh:nn:ss',strtodatetime(datetostr(datetimepicker6.Date)+' 00:00:00'))+''' and ';
    if datetimepicker7.checked then
       sqlstr:=sqlstr+' 设备上传时间<='''+formatdatetime('yyyy-mm-dd hh:nn:ss',strtodatetime(datetostr(datetimepicker7.Date)+' 23:59:59'))+''' and ';
    if checkbox1.Checked then
    begin
        if combobox1.ItemIndex=0 then
           sqlstr:=sqlstr+' 问题标记=''000'' and 故障标记=''000'' and '
        else if combobox1.ItemIndex=1 then
        begin
           if length(trim(combobox2.Text))<>0 then
              sqlstr:=sqlstr+' 问题标记='''+leftstr(trim(combobox2.Text),3)+''' and '
           else
              sqlstr:=sqlstr+' (问题标记=''106'' or 问题标记=''114'') and ';
        end
        else if combobox1.ItemIndex=2 then
        begin
           if length(trim(combobox2.Text))<>0 then
              sqlstr:=sqlstr+' 问题标记=''000'' and 故障标记='''+leftstr(trim(combobox2.Text),3)+''' and '
           else
              sqlstr:=sqlstr+' 问题标记=''000'' and 故障标记<>''000'' and ';
        end
        else if combobox1.ItemIndex=3 then
        begin
            if length(trim(combobox2.Text))<>0 then
              sqlstr:=sqlstr+' 问题标记='''+leftstr(trim(combobox2.Text),3)+''' and '
            else
              sqlstr:=sqlstr+' (问题标记=''107'' or 问题标记=''108'' or 问题标记=''109'' or 问题标记=''110'' or 问题标记=''999'') and ';
        end
        else if combobox1.ItemIndex=4 then
            sqlstr:=sqlstr+' 电池电压=''低压'' and '
        else if combobox1.ItemIndex=5 then
            sqlstr:=sqlstr+' 停转时间>=48 and ';
    end;
    if checkbox3.Checked then
    begin
        if trim(combobox3.Text)<>'between' then
            sqlstr:=sqlstr+' 本期流量 '+trim(combobox3.Text)+' '''+trim(edit1.Text)+''' and '
        else
            sqlstr:=sqlstr+' 本期流量 between '''+trim(edit1.Text)+''' and '''+trim(edit2.Text)+''' and ';
    end;
    sqlstr:=sqlstr+ ' 设备ID号 like ''%''';
    adoquery1.Connection:=DataModule1.ADOConnection1;
    adoquery1.SQL.Clear;
    adoquery1.Filtered:=false;
    adoquery1.Close;
    adoquery1.SQL.Add(sqlstr);
    adoquery1.Prepared;
    adoquery1.open;
    edit21.Text:='位置:'+inttostr(adoquery1.RecNo)+'/'+inttostr(adoquery1.RecordCount);

end;

procedure TDataBrowseForm.FormShow(Sender: TObject);
var
    i:integer;
begin
    DataModule1.adoquery4.Connection :=datamodule1.ADOConnection1;
    DataModule1.adoquery4.SQL.Clear;
    DataModule1.ADOQuery4.Filtered:=false;
    DataModule1.adoquery4.Close;
    DataModule1.adoquery4.SQL.Add('select * from 区域档案表');
    DataModule1.adoquery4.Prepared;
    DataModule1.adoquery4.open;
    if DataModule1.adoquery4.RecordCount <>0 then
    begin
        combobox20.Items.Clear;
        for i:=1 to DataModule1.adoquery4.RecordCount do
        begin
            combobox20.Items.Add(datamodule1.ADOQuery4.fieldbyname('区号').AsString+'-'+datamodule1.ADOQuery4.fieldbyname('区域名称').AsString);
            DataModule1.adoquery4.Next;
        end;
    end;
end;

procedure TDataBrowseForm.ComboBox20Change(Sender: TObject);
var
    i:integer;
begin
    DataModule1.adoquery4.Connection :=datamodule1.ADOConnection1;
    DataModule1.adoquery4.SQL.Clear;
    DataModule1.ADOQuery4.Filtered:=false;
    DataModule1.adoquery4.Close;
    DataModule1.adoquery4.SQL.Add('select * from 本档案管理 where 区号='''+getNum(trim(combobox20.Text))+'''');
    DataModule1.adoquery4.Prepared;
    DataModule1.adoquery4.open;
    combobox21.Items.Clear;
    combobox21.Text:='';
    if DataModule1.adoquery4.RecordCount <>0 then
    begin
        for i:=1 to DataModule1.adoquery4.RecordCount do
        begin
            combobox21.Items.Add(datamodule1.ADOQuery4.fieldbyname('本号').AsString+'-'+datamodule1.ADOQuery4.fieldbyname('本名').AsString);
            DataModule1.adoquery4.Next;
        end;
    end;
    DataModule1.adoquery4.Connection :=datamodule1.ADOConnection1;
    DataModule1.adoquery4.SQL.Clear;
    DataModule1.ADOQuery4.Filtered:=false;
    DataModule1.adoquery4.Close;
    DataModule1.adoquery4.SQL.Add('select * from 抄表员档案 where 区号='''+getNum(trim(combobox20.Text))+'''');
    DataModule1.adoquery4.Prepared;
    DataModule1.adoquery4.open;
    combobox22.Items.Clear;
    combobox22.Text:='';
    if DataModule1.adoquery4.RecordCount <>0 then
    begin
        for i:=1 to DataModule1.adoquery4.RecordCount do
        begin
            combobox22.Items.Add(datamodule1.ADOQuery4.fieldbyname('抄表员号').AsString+'-'+datamodule1.ADOQuery4.fieldbyname('姓名').AsString);
            DataModule1.adoquery4.Next;
        end;
    end;
end;

end.