apputil.~pas

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

unit AppUtil;

interface
uses
Dialogs,Forms,XMLDoc,ADODB,SysUtils,Windows,comobj,Controls,Variants,StrUtils;
function openConn(pwd:string):integer;
procedure delay(t:integer);
procedure report;
function getNum(str:string):string;
procedure viewReport();
implementation

uses DataModuleUnit1, SelectDateFormUnit1, SelectOperatorFormUnit,
  Pack_CRCUnit1;
function openConn(pwd:string):integer;
var
   ServerName,dbname,dbUsername,dbUserPassword,sys:string;
   xml:TXMLDocument;
   str:widestring; 
begin
  try
     xml:=TXMLDocument.Create(application);
     xml.FileName:=extractfilepath(application.ExeName)+'\zssetting.xml';
     xml.Active:=true;
     servername:=xml.DocumentElement.ChildNodes['connection'].ChildNodes['server'].Text;
     dbname:=xml.DocumentElement.ChildNodes['connection'].ChildNodes['db'].Text;
     dbusername:=xml.DocumentElement.ChildNodes['connection'].ChildNodes['username'].Text;
     dbUserPassword:='qwe';//pwd;//xml.DocumentElement.ChildNodes['connection'].ChildNodes['password'].Text;
     sys:=xml.DocumentElement.ChildNodes['connection'].ChildNodes['sys'].Text;
     xml.Free;
     DataModule1:=TDataModule1.Create(application);
     DataModule1.ADOConnection1.Close;
     if sys='no' then
        DataModule1.ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Password='+trim(dbuserpassword)+';Persist Security Info=false;User ID='+trim(dbusername)+';Initial Catalog='+trim(dbname)+';Data Source='+trim(servername)
     else
        DataModule1.ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog='+trim(dbusername)+';Data Source='+trim(servername);
     DataModule1.ADOConnection1.CursorLocation:=clUseClient;
     DataModule1.ADOConnection1.Open;
  except
    on e:exception do
    begin
       showmessage(e.Message);
    end;
  end;
end;

procedure delay(t:integer);
var
    t0:integer;
    n:integer;
begin
    t0:=GetTickCount;
    while  (GetTickCount-t0)<t do
    begin
       n:=n+1;
       application.ProcessMessages;
    end;
end;
//TMainForm.ddddddddd1Click(Sender: TObject)
procedure report;
var
    eclApp,WorkBook,sheet:Variant;{声明为OLE Automation对象}
    i,j,k:integer;
    filterstr,tempstrop,QuNoStr:string;
    temp:integer;
begin
    try
    eclApp:=CreateOleObject('Excel.Application');
    except
      ShowMessage('您的机器里未安装Microsoft Excel.');
      Exit;
    end;
    application.ProcessMessages;
    if SelectOperatorForm=nil then
       SelectOperatorForm:=TSelectOperatorForm.Create(nil);
    SelectOperatorForm.ShowModal;
    if SelectOperatorForm.ModalResult=mrcancel then
        exit
    else if SelectOperatorForm.ModalResult=mrok then
    begin
        application.ProcessMessages;
        WorkBook:=eclApp.WorkBooks.Add(extractfilepath(application.ExeName)+'井表水表抄表工作汇总月报.xls');
        eclApp.WorkSheets[1].Activate;
        eclApp.displayAlerts:=false;
        eclApp.ScreenUpdating:=false;
        eclApp.ScreenUpdating:=true;
        WorkBook.Sheets[1].cells[1,1].value:='井表水表抄表工作汇总月报';
        WorkBook.Sheets[1].cells[2,1].value:=formatdatetime('yyyy-mm-dd',now);
        for i:=low(selectoperatorform.operatorlist) to high(selectoperatorform.operatorlist) do
        begin
            application.ProcessMessages;
            screen.Cursors[crMyCursor]:=LoadCursorFromFile('bgwork.ani');
            screen.Cursor :=crMyCursor;
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            datamodule1.ADOQuery6.SQL.Add('select * from 问题数据分析');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            if datamodule1.ADOQuery6.RecordCount=0 then
            begin
                messagedlg('无可用数据！',mterror,[mbok],0);
                Screen.Cursors[crMyCursor]:=crdefault;
                Screen.Cursor:=crdefault;
                WorkBook.Close;
                eclApp.Quit;
                eclApp:=Unassigned;
                exit;
            end;
            QuNoStr:=selectoperatorform.tempquno;
            tempstrop:=selectoperatorform.operatorlist[i];
            //******抄表员号
            WorkBook.Sheets[1].cells[i+7,1].value:=tempstrop;
            //******应抄数量(正路)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            //datamodule1.ADOQuery6.SQL.Add('SELECT COUNT(*) AS num FROM 任务分配表 WHERE (区号 ='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''')');
            datamodule1.ADOQuery6.SQL.Add('select count(*) as num from 问题数据分析 where 问题标记!=''999'' and (区号='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[1].cells[i+7,2].value:=inttostr(temp+DataModule1.adoquery6.fieldbyname('num').AsInteger);
            //******应抄数量(散牌)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            //datamodule1.ADOQuery6.SQL.Add('SELECT COUNT(*) AS num FROM 任务分配表 WHERE (区号 ='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''' and 标记=''0'')');
            datamodule1.ADOQuery6.SQL.Add('select count(*) as num from 问题数据分析 where 问题标记=''999'' and (区号='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[1].cells[i+7,3].value:=inttostr(temp+DataModule1.adoquery6.fieldbyname('num').AsInteger);
            //********************
            WorkBook.Sheets[1].cells[i+7,4].value:=inttostr(strtoint(WorkBook.Sheets[1].cells[i+7,2])+strtoint(WorkBook.Sheets[1].cells[i+7,3]));
            //********************
            //******正常(电池正常)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            datamodule1.ADOQuery6.SQL.Add('SELECT COUNT(*) AS num FROM 问题数据分析 WHERE (区号 ='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''') AND (问题标记 = ''000'') AND (故障标记 = ''000'' or 故障标记=''100'') AND (电池电压 = ''正常'')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[1].cells[i+7,5].value:=inttostr(DataModule1.adoquery6.fieldbyname('num').AsInteger);
            //******正常(电池低压)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            datamodule1.ADOQuery6.SQL.Add('SELECT COUNT(*) AS num FROM 问题数据分析 WHERE (区号 ='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''') AND (问题标记 = ''000'') AND (故障标记 = ''000'' or 故障标记=''100'') AND (电池电压 = ''低压'')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[1].cells[i+7,6].value:=inttostr(DataModule1.adoquery6.fieldbyname('num').AsInteger);
            //********************
            WorkBook.Sheets[1].cells[i+7,7].value:=inttostr(strtoint(WorkBook.Sheets[1].cells[i+7,5])+strtoint(WorkBook.Sheets[1].cells[i+7,6]));
            //showmessage(inttostr(strtoint(WorkBook.Sheets[1].cells[i+7,7]))+'-'+inttostr(strtoint(WorkBook.Sheets[1].cells[i+7,4])));
            if(strtoint(WorkBook.Sheets[1].cells[i+7,4])<>0) then
              WorkBook.Sheets[1].cells[i+7,8]:=floattostr(strtoint(WorkBook.Sheets[1].cells[i+7,7]) / strtoint(WorkBook.Sheets[1].cells[i+7,4]))
            else
              WorkBook.Sheets[1].cells[i+7,8]:='0';
            //********************
            //******设备故障(103)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            datamodule1.ADOQuery6.SQL.Add('SELECT COUNT(*) AS num FROM 问题数据分析 WHERE (区号 ='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''') AND (问题标记=''000'' and 故障标记 = ''103'')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[1].cells[i+7,9].value:=inttostr(DataModule1.adoquery6.fieldbyname('num').AsInteger);
            {//******水量为零(112)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;
            DataModule1.adoquery6.Close;
            datamodule1.ADOQuery6.Filtered:=false;
            datamodule1.ADOQuery6.SQL.Add('SELECT count(*) as num FROM 问题数据分析 where (区号='''+QuNoStr+''' and 抄表员号 ='''+tempstrop+''') and (故障标记=''112'')');
            DataModule1.adoquery6.Prepared;
            DataModule1.adoquery6.open;
            WorkBook.Sheets[5].cells[i+7,10].value:=inttostr(DataModule1.adoquery6.fieldbyname('num').AsInteger);
            //******水量为负(113)
            DataModule1.adoquery6.Connection:=DataModule1.ADOConnection1;
            DataModule1.adoquery6.SQL.Clear;