dataanalyseformunit1.pas

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

        edit2.Enabled:=true
    else
    begin
        edit2.Text:='';
        edit2.Enabled:=false;
    end;
end;

procedure TDataAnalyseForm.CheckBox23Click(Sender: TObject);
begin
    if checkbox23.Checked then
        edit3.Enabled:=true
    else
    begin
        edit3.Text:='';
        edit3.Enabled:=false;
    end;
end;

procedure TDataAnalyseForm.ADOQuery1AfterScroll(DataSet: TDataSet);
begin
    if datasource1.DataSet=adoquery1 then
    begin
        if adoquery1.FieldByName('故障标记').AsString <>'000' then
        begin
            DataModule1.adoquery5.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery5.SQL.Clear;
            datamodule1.ADOQuery5.Filtered:=false;
            DataModule1.adoquery5.Close;
            DataModule1.adoquery5.SQL.Add('select * from 翔龙代码表 where 对外标识='''+trim(datasource1.DataSet.fieldbyname('故障标记').AsString)+'''');
            DataModule1.adoquery5.Prepared;
            DataModule1.adoquery5.open;
            if DataModule1.adoquery5.RecordCount <>0 then
                 dbgrid1.Hint:='['+'故障标记'+'--'+DataModule1.adoquery5.fieldbyname('对外标识').AsString+']'+':'+DataModule1.adoquery5.fieldbyname('代码说明').AsString
            else
                 dbgrid1.Hint:='未知';
        end
        else
        begin
            dbgrid1.Hint:='['+'故障标记'+'--'+'000'+']'+':'+'正常';
        end;
        if adoquery1.FieldByName('问题标记').AsString <>'000' then
        begin
            DataModule1.adoquery5.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery5.SQL.Clear;
            datamodule1.ADOQuery5.Filtered:=false;
            DataModule1.adoquery5.Close;
            DataModule1.adoquery5.SQL.Add('select * from 翔龙代码表 where 对外标识='''+trim(datasource1.DataSet.fieldbyname('问题标记').AsString)+'''');
            DataModule1.adoquery5.Prepared;
            DataModule1.adoquery5.open;
            if DataModule1.adoquery5.RecordCount <>0 then
                 dbgrid1.Hint:=dbgrid1.Hint+','+'['+'问题标记'+'--'+DataModule1.adoquery5.fieldbyname('对外标识').AsString+']'+':'+DataModule1.adoquery5.fieldbyname('代码说明').AsString
            else
                 dbgrid1.Hint:=dbgrid1.Hint+','+'未知';
        end
        else
        begin
            dbgrid1.Hint:=dbgrid1.Hint+','+'['+'问题标记'+'--'+'000'+']'+':'+'正常';
        end;
    end
    else
        toolbutton5.ImageIndex:=0;
end;

procedure TDataAnalyseForm.ListView1ColumnClick(Sender: TObject;
  Column: TListColumn);
begin
    if Abs(TListView(Sender).Tag) = Column.Index + 1 then
        TListView(Sender).Tag := -TListView(Sender).Tag
    else TListView(Sender).Tag := Column.Index + 1;
        TListView(Sender).AlphaSort;
end;

procedure TDataAnalyseForm.N13Click(Sender: TObject);
var
    sqlstr:string;
    //DbLinkProfile:tinifile;
    //dbusername,dbUserPassword:string;
    i:integer;
begin
    DataModule1.adocommand1.Connection:=datamodule1.ADOConnection1;
    DataModule1.adocommand1.CommandText:='drop table temp2';
    DataModule1.adocommand1.Execute;
    sqlstr:='select 设备ID号,注册号,本期指数,停转时间,电池电压,计数轮,定点时间,设备上传时间,设备当前时钟,设备类型,数据来源,问题标记,故障标记,上传模式,人工读数,';
    sqlstr:=sqlstr+'估收水量,停转开始时间,当前时间,是否导出,备注 into temp2 from 历史数据查询 where 注册号 in (select distinct 注册号 from 问题数据分析)';
    DataModule1.adocommand1.CommandText:=sqlstr;
    DataModule1.adocommand1.Execute;
    sqlstr:='insert into temp2(设备ID号,注册号,本期指数,停转时间,电池电压,计数轮,定点时间,设备上传时间,设备当前时钟,设备类型,数据来源,问题标记,故障标记,上传模式,人工读数,估收水量,停转开始时间,当前时间,是否导出,备注)';
    sqlstr:=sqlstr+'(select 设备ID号,注册号,本期指数,停转时间,电池电压,计数轮,定点时间,设备上传时间,设备当前时钟,设备类型,数据来源,问题标记,故障标记,上传模式,人工读数,估收水量,停转开始时间,当前时间,是否导出,备注 from 问题数据分析)';
    DataModule1.adocommand1.CommandText:=sqlstr;
    DataModule1.adocommand1.Execute;
    sqlstr:='select 设备上传时间,本期指数,人工读数,估收水量,问题标记,故障标记,注册号,left(用户名称,10) as 用户名称,地址,停转时间,电池电压,计数轮,定点时间,设备当前时钟,设备类型,数据来源,';
    sqlstr:=sqlstr+'上传模式,停转开始时间,当前时间,区号,本号,牌号,抄表员号,设备ID号,是否导出,备注 from tempview';
    adoquery3.Connection:=DataModule1.ADOConnection1;
    adoquery3.SQL.Clear;
    adoquery3.Filtered:=false;
    adoquery3.Close;
    adoquery3.SQL.Add(sqlstr);
    adoquery3.Prepared;
    adoquery3.open;
    //self.Caption:='检索数据'+'['+inttostr(adoquery3.RecordCount)+'条'+']'
    {if FileExists(extractfilepath(application.ExeName)+'\DbLinkProfile.ini') then
    begin
        DbLinkProfile:=tinifile.Create(extractfilepath(application.ExeName)+'\DbLinkProfile.ini');
        //servername:=DbLinkProfile.ReadString('服务器名','Data Source','');
        //dbname:= DbLinkProfile.ReadString('数据库名','Initial Catalog','');
        dbusername:=DbLinkProfile.ReadString('登录名称','User ID','');
        dbUserPassword:=DbLinkProfile.ReadString('登录密码','Password','');
        if FileExists(extractfilepath(application.ExeName)+'\database.sql') then
        begin
            ShellExecute(Handle,'open',pchar(extractfilepath(application.ExeName)+'\osql.exe'),pchar('-U'+stringofchar(chr(32),1)+dbusername+' -P'+stringofchar(chr(32),1)+dbUserPassword+' -i'+extractfilepath(application.ExeName)+'\createtable.sql'),'',SW_hide);
            adoquery3.Connection:=DataModule1.ADOConnection1;
            adoquery3.SQL.Clear;
            adoquery3.Filtered:=false;
            adoquery3.Close;
            adoquery3.SQL.Add('select * from 问题数据分析');
            adoquery3.Prepared;
            adoquery3.open;
            for i:=1 to adoquery3.RecordCount do
            begin
                DataModule1.adocommand1.Connection:=datamodule1.ADOConnection1;
                DataModule1.adocommand1.CommandText:='update 问题数据分析 set 问题标记='''+midbstr(trim(combobox23.Text),1,pos('-',trim(combobox23.Text))-1)+''' where 注册号='''+datasource1.DataSet.FieldValues['注册号']+'''';
                DataModule1.adocommand1.Execute;
            end;
        end
        else
        begin
            application.MessageBox('在默认路径下未找到SQL脚本！','错误',mb_iconSTOP);
            exit;
        end;
    end
    else
    begin
        application.MessageBox('在默认路径下未找到数据库连接所需的配置文件！','错误',mb_iconSTOP);
        exit;
    end;}
    {sqlstr:='select * from 历史数据查询 where';
    if checkbox31.Checked then
       sqlstr:=sqlstr+ ' 区号='''+trim(combobox20.Text)+''' and ';
    if checkbox32.Checked then
       sqlstr:=sqlstr+ ' 本号='''+stringofchar('0',4-length(trim(combobox21.Text)))+trim(combobox21.Text)+''' and ';
    if checkbox33.Checked then
       sqlstr:=sqlstr+ ' 牌号='''+stringofchar('0',3-length(trim(combobox22.Text)))+trim(combobox22.Text)+''' and ';
    if checkbox27.Checked then
    begin
        if combobox16.Text='=' then
            sqlstr:=sqlstr+ ' 注册号='''+trim(edit14.Text)+''' and '
        else if combobox16.Text='LIKE' then
            sqlstr:=sqlstr+ ' 注册号 like '''+'%'+trim(edit14.Text)+'%'+''' and ';
    end;
    if checkbox28.Checked then
    begin
        if combobox17.Text='=' then
            sqlstr:=sqlstr+ ' 用户名称='''+trim(edit15.Text)+''' and '
        else if combobox17.Text='LIKE' then
            sqlstr:=sqlstr+ ' 用户名称 like '''+'%'+trim(edit15.Text)+'%'+''' and ';
    end;
    if checkbox30.Checked then
       sqlstr:=sqlstr+' 问题标记'+trim(combobox19.Text)+''''+trim(edit16.Text)+''' and ';
    if checkbox36.Checked then
       sqlstr:=sqlstr+' 故障标记'+trim(combobox26.Text)+''''+trim(edit20.Text)+''' and ';
    if checkbox29.Checked then
       sqlstr:=sqlstr+' 设备类型='''+trim(combobox18.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 ';
    sqlstr:=sqlstr+ ' 设备ID号 like ''%''';
    adoquery3.Connection:=DataModule1.ADOConnection1;
    adoquery3.SQL.Clear;
    adoquery3.Filtered:=false;
    adoquery3.Close;
    adoquery3.SQL.Add(sqlstr);
    adoquery3.Prepared;
    adoquery3.open;
    self.Caption:='检索数据'+'['+inttostr(adoquery3.RecordCount)+'条'+']'}
end;

procedure TDataAnalyseForm.DBGrid2TitleClick(Column: TColumn);
var
  str:string;
begin
  str:=adoquery3.Sort;
  if length(str)=0 then
     adoquery3.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
              adoquery3.Sort:=column.Title.Caption+' ASC'
          else
              adoquery3.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
              adoquery3.Sort:=column.Title.Caption+' DESC'
          else
              adoquery3.Sort:=column.Title.Caption+' ASC';
      end;
  end;
end;
procedure TDataAnalyseForm.N14Click(Sender: TObject);
var
    eclApp,WorkBook,sheet:Variant;{声明为OLE Automation对象}
    i,j:integer;
begin
  try
    try
      eclApp:=CreateOleObject('Excel.Application');
    except
      ShowMessage('您的机器里未安装Microsoft Excel.');
      Exit;
    end;
    if not printdialog1.Execute then
        exit;
    //eclapp.visible:=true;
    eclApp.DisplayAlerts:=false;
    if adoquery1.State=dsInactive	then
    begin
        messagedlg('没有可供打印的数据！',mtinformation,[mbok],0);
        exit;
    end
    else
    begin
        if adoquery1.RecordCount <>0 then
        begin
            WorkBook:=eclApp.WorkBooks.Add(extractfilepath(application.ExeName)+'\派工单.xls');
            //WorkBook.Sheets[1].Cells[2,2]:='';
            WorkBook.Sheets[1].cells[2,2]:='';
            WorkBook.Sheets[1].cells[2,5]:='';
            WorkBook.Sheets[1].cells[3,2]:='';
            WorkBook.Sheets[1].cells[3,5]:='';
            WorkBook.Sheets[1].cells[4,2]:='';
            WorkBook.Sheets[1].cells[5,2]:='';
            WorkBook.Sheets[1].cells[5,8]:='';
            WorkBook.Sheets[1].cells[6,8]:='';
            WorkBook.Sheets[1].cells[20,2]:='';
            WorkBook.Sheets[1].cells[20,5]:='';
            WorkBook.Sheets[1].cells[21,2]:='';
            WorkBook.Sheets[1].cells[21,5]:='';
            WorkBook.Sheets[1].cells[22,2]:='';
            WorkBook.Sheets[1].cells[23,2]:='';
            WorkBook.Sheets[1].cells[23,8]:='';
            WorkBook.Sheets[1].cells[24,8]:='';
            WorkBook.Sheets[1].cells[2,11]:='';
            WorkBook.Sheets[1].cells[2,14]:='';
            WorkBook.Sheets[1].cells[3,11]:='';
            WorkBook.Sheets[1].cells[3,14]:='';
            WorkBook.Sheets[1].cells[4,11]:='';
            WorkBook.Sheets[1].cells[5,11]:='';
            WorkBook.Sheets[1].cells[5,17]:='';
            WorkBook.Sheets[1].cells[6,17]:='';
            WorkBook.Sheets[1].cells[20,11]:='';
            WorkBook.Sheets[1].cells[20,14]:='';
            WorkBook.Sheets[1].cells[21,11]:='';
            WorkBook.Sheets[1].cells[21,14]:='';
            WorkBook.Sheets[1].cells[22,11]:='';
            WorkBook.Sheets[1].cells[23,11]:='';
            WorkBook.Sheets[1].cells[23,17]:='';
            WorkBook.Sheets[1].cells[24,17]:='';
            adoquery1.First;
            while adoquery1.Eof =false do
            begin
                DataModule1.adoquery5.Connection:=DataModule1.ADOConnection1;
                DataModule1.adoquery5.SQL.Clear;
                DataModule1.ADOQuery5.Filtered:=false;
                DataModule1.adoquery5.Close;
                DataModule1.adoquery5.SQL.Add('select * from 设备信息表 where 注册号='''+adoquery1.FieldByName('注册号').AsString+'''');
                DataModule1.adoquery5.Prepared;
                DataModule1.adoquery5.open;
                if DataModule1.adoquery5.RecordCount <>0 then
                begin
                    WorkBook.Sheets[1].cells[1,1]:='问题表派工单';
                    WorkBook.Sheets[1].cells[2,2]:='市北营业';
                    WorkBook.Sheets[1].cells[2,5]:=adoquery1.FieldByName('区号').AsString;
                    WorkBook.Sheets[1].cells[3,2]:=adoquery1.FieldByName('注册号').AsString;
                    WorkBook.Sheets[1].cells[3,5]:=adoquery1.FieldByName('用户名称').AsString;
                    WorkBook.Sheets[1].cells[4,2]:=DataModule1.adoquery5.FieldByName('安装地点').AsString;
                    WorkBook.Sheets[1].cells[5,2]:=adoquery1.FieldByName('设备ID号').AsString;
                    WorkBook.Sheets[1].cells[5,8]:=DataModule1.adoquery5.FieldByName('水表口径').AsString;
                    WorkBook.Sheets[1].cells[6,8]:=DataModule1.adoquery5.FieldByName('井况').AsString;
                    WorkBook.Sheets[1].cells[8,2]:=adoquery1.FieldByName('问题标记').AsString;
                    adoquery1.Next;
                end;
                if adoquery1.eof=true then
                begin
                    WorkBook.Worksheets.PrintOut;
                    break;
                end;
                DataModule1.adoquery5.Connection:=DataModule1.ADOConnection1;
                DataModule1.adoquery5.SQL.Clear;
                DataModule1.ADOQuery5.Filtered:=false;
                DataModule1.adoquery5.Close;
                DataModule1.adoquery5.SQL.Add('select * from 设备信息表 where 注册号='''+adoquery1.FieldByName('注册号').AsString+'''');
                DataModule1.adoquery5.Prepared;
                DataModule1.adoquery5.open;
                if DataModule1.adoquery5.RecordCount <>0 then
                begin
                    WorkBook.Sheets[1].cells[19,1]:='问题表派工单';
                    WorkBook.Sheets[1].cells[20,2]:='市北营业';
                    WorkBook.Sheets[1].cells[20,5]:=adoquery1.FieldByName('区号').AsString;
                    WorkBook.Sheets[1].cells[21,2]:=adoquery1.FieldByName('注册号').AsString;
                    WorkBook.Sheets[1].cells[21,5]:=adoquery1.FieldByName('用户名称').AsString;
                    WorkBook.Sheets[1].cells[22,2]:=DataModule1.adoqu