commonfunc.~pas

一个电力企业的后台管理程序

unit CommonFunc;

interface
uses dbtables,Sysutils,WordReport;
const
 UserSatStr = '用户满意情况,数量';
 UserFeedBack = '回访率,业务类型';
 GzCountStr = '故障类型,故障数';
 GzUnitStr = '受理单位名称,故障数';
 GzTypeStr = '处理单位,未处理数,及时处理数,超期处理数,其中，超期未处理数,合计数';
 FXStr = '月份,本月发生数,累计数,同比上月增长率,同比去年增长率,本月占百分比,累计占百分比';
 CompTypeStr = '投诉类型,数量';
 CompUnitStr = '投诉单位,数量';
 CompCountStr = '投诉处理单位,未处理数,及时处理数,超期处理数,合计数';
 AskTypeStr = '咨询类型,数量';
 AskUnitStr = '咨询单位,数量';
 AskCountStr = '咨询处理单位,未处理数,及时处理数,超期处理数,合计数';
 AskElecTypeStr = '用电类型,数量';
 AskElecUnitStr = '用电办理单位,数量';
 AgentWorkIdStr = '工号,数量';
 AgentTypeStr = '业务类型,数量';
 Procedure SetFieldTitle(var Qry :TQuery; sTtl :String);
 Procedure MakeWordReport(WordRep: TWordReport;SqlStr,TitStr,CapStr: String);
 procedure IniTable(Str:String);
 function GetAllCount: Real;
 procedure InsertCount(Query:TQuery;LastQuery: TQuery);
 procedure FillAllCount(Query,LastQuery: TQuery);
implementation

uses SystemDm;
 Procedure MakeWordReport(WordRep: TWordReport;SqlStr,TitStr,CapStr: String);
 var
  Query: TQuery;
 begin
  Query := TQuery.Create(nil);
  try
    Query.DatabaseName := SysDM.DBMain.DatabaseName;
    WordRep.MasterDataset := nil;
    WordRep.DetailDataset := nil;
    WordRep.DetailChild := nil;
    WordRep.SingleDataset := nil;
    WordRep.SingleDataset := Query;
    with Query do
     begin
      Close;
      Sql.Text := SqlStr;
      Open;
      SetFieldTitle(Query,TitStr);
     end;
    WordRep.TitleString :=CapStr;
    WordRep.Execute ;
  finally
   Query.Free;
  end;
 end;
Procedure SetFieldTitle(var Qry :TQuery; sTtl :String);
var
  ii,iPos :Integer;
  sFldTtl :String;
begin
   for ii :=0 to Qry.FieldCount -1 do
   begin
     iPos := Pos(',',sTtl);
     if iPos =0 then sFldTtl := sTtl
     else
     begin
        sFldTtl := Copy(sTtl,1,iPos-1);
        Delete(sTtl,1,iPos);
     end;
     Qry.Fields[ii].DisplayLabel := sFldTtl;
   end;
end;

procedure InsertCount(Query:TQuery;LastQuery: TQuery);
var
 I,j,k:Integer;

 Str1,Str2: String;
begin

  with TQuery.Create(nil) do
   try
    DataBaseName := SysDm.DBMain.DataBaseName;
    Close;
    Sql.Text := 'TRUNCATE TABLE ELECOUTCOUNT;TRUNCATE TABLE TEMLASTYEAR';
    ExecSql;
    for I := 0 to 11 do
     begin
      Close;
      Str1 := Query.Fields[0].AsString;
      J := Query.Fields[1].AsInteger;
      Str2 := LastQuery.Fields[0].AsString;
      k := LastQuery.Fields[1].AsInteger;
      Sql.Text := Format('INSERT INTO ELECOUTCOUNT (MON,MONCOUNT) VALUES(''%S'',%s);' +
                         'INSERT INTO TEMLASTYEAR (MON,MONCOUNT) VALUES(''%S'',%s)',
                         [Str1,IntToStr(j),Str2,IntToStr(k)]);
      //Sql.SaveToFile('C:\temp.tem');
      ExecSql;
      Query.Next;
      LastQuery.Next;
     end;
   finally
    Free;
   end;

end;

function GetAllCount: Real;
begin
 with TQuery.Create(nil)do
 try
  DataBaseName := SysDM.DBMain.DatabaseName;
  Close;
  Sql.Text := ('SELECT SUM(MONCOUNT)  FROM ELECOUTCOUNT') ;
  OPen;
  Result := Fields[0].AsFloat;
 finally
  Free;
 end;
end;

procedure FillAllCount(Query,LastQuery: TQuery);
var
 I,TempCount: Integer;
 LastMonCount,RSyZzl,LastCount,RQnZzl,AllCount,RZbfl: Real;
 SyZzl,QnZzl,MonStr,Zbfl: String;
begin
 Query.First;
 LastQuery.Last;
 LastMonCount := LastQuery.Fields[1].AsFloat;
 LastQuery.First;
 AllCount := GetAllCount;
 TempCount := 0;
 with TQuery.Create(nil)do
  try
   DataBaseName := SysDM.DBMain.DatabaseName;
   Close;
   for I := 0 to Query.RecordCount - 1 do
    begin
     MonStr := Query.Fields[0].AsString;
     TempCount := TempCount + Query.Fields[1].AsInteger;
     if LastMonCount = 0 then
      SyZzl := '上月没数据'
     else
      begin
       RSyZzl := (Query.fields[1].AsFloat - LastMonCount) / LastMonCount * 100;
       SyZzl := Format('%f',[RSyZzl]) + '%';
      end;
     LastCount := LastQuery.Fields[1].AsFloat;
     if LastCount = 0 then
      QnZzl := '去年该月没数据'
     else
      begin
       RQnZzl := (Query.fields[1].AsFloat - LastCount) / LastCount * 100;
       QnZzl := Format('%f',[RQnZzl]) + '%';
      end;
     if AllCount = 0 then
      Zbfl := '本年度没数据'
     else
      begin
       RZbfl := Query.fields[1].AsFloat / AllCount * 100;
       Zbfl := Format('%f',[Rzbfl]) + '%';
      end;
     SQL.Text := Format('UPDATE ELECOUTCOUNT SET ALLCOUNT = %s , '+
                        'SYZZL = ''%S'', ' +
                        'QNZZL = ''%S'', '  +
                        'ZBFL = ''%S''' +
                        ' WHERE MON = ''%S'''
                   ,[IntToStr(TempCount),SyZzl,QnZzl,Zbfl,MonStr]);
     ExecSql;
     LastMonCount := Query.Fields[1].AsFloat;
     Query.Next;
     LastQuery.Next;
    end;
  finally
   Free;
  end;
end;
procedure IniTable(Str:String);
var
 MonStr,LastMonStr : String;
 I: Integer;
begin
 with TQuery.Create(nil) do
  try
   DataBaseName := SysDm.DBMain.DatabaseName;
   Sql.Text := 'TRUNCATE TABLE ELECOUTCOUNT;TRUNCATE TABLE TEMLASTYEAR';
   ExecSql;
   for I := 1 to 12 do
    begin
     if I < 10 then
      begin
       MonStr := Str + '-0' + IntToStr(I);
       LastMonStr := IntToStr(StrToInt(Str) - 1) + '-0' + IntToStr(I);
      end
     else
      begin
       MonStr := Str + '-' + IntToStr(I);
       LastMonStr := IntToStr(StrToInt(Str) - 1) + '-' + IntToStr(I);
      end;
     Close;
     Sql.Text := Format('INSERT INTO ELECOUTCOUNT (MON) VALUES (''%S'');' +
                        'INSERT INTO TEMLASTYEAR (MON) VALUES (''%S'')',
                        [MonStr,LastMonStr]);
     ExecSql;                    
    end;
  finally
   Free;
  end;
end;

end.