udm.pas

a voice guide client ,it is the second part of voice guide center

var
  SqlCmdStr  :string;
begin
   if not scGpsDB.Connected then OpenDB(gDataHost, gDataPort);
   SqlCmdStr := Format('Select * from GpsMsgInfo where carid = %d '+
                ' and GpsTime = %s '+
                ' and MsgId = %s',
                [LastEmCarId, QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',LastEmTime)),
                 QuotedStr(LastAreaId)]);
   cdsLookup.Close;
   cdsLookup.CommandText:= SqlCmdStr;
   cdsLookup.Open;
   If cdsLookup.IsEmpty then
   begin
    {$IFDEF _POLICE}
          SqlCmdStr := 'INSERT INTO GpsMsgInfo (CarId,RecvTime,GpsTime,MsgId,MsgType,MsgStr,MsgDescrible) VALUES ('
                   + inttostr(LastEmCarId)+','
                   + QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',Now))+','
                   + QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',LastEmTime))+','
                   + QuotedStr(LastAreaId)+','
                   + '2,'
                   + QuotedStr('迟到报警')+','
                   + QuotedStr('警车车未在指定时间:'+FormatDateTime('yyyy-mm-dd hh:mm:ss',LastEmTime)+'到达设定区域:'+LastAreaId)
                   +')';
    {$ELSE}
      SqlCmdStr := 'INSERT INTO GpsMsgInfo (CarId,RecvTime,GpsTime,MsgId,MsgType,MsgStr,MsgDescrible) VALUES ('
                   + inttostr(LastEmCarId)+','
                   + QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',Now))+','
                   + QuotedStr(FormatDateTime('yyyy-mm-dd hh:mm:ss',LastEmTime))+','
                   + QuotedStr(LastAreaId)+','
                   + '2,'
                   + QuotedStr('迟到报警')+','
                   + QuotedStr('车辆未在指定时间:'+FormatDateTime('yyyy-mm-dd hh:mm:ss',LastEmTime)+'到达设定区域:'+LastAreaId)
                   +')';
    {$ENDIF}
      cdsLookup.Close;
      cdsLookup.CommandText := SqlCmdStr;
      cdsLookup.Execute;
   end;
   cdsLookup.Close;
end;

procedure TDataModule1.SavePriceInfoList;
var
  PriceInfo: TPriceInfo;
  i: Integer;
begin
  if not scGpsDB.Connected then OpenDB(gDataHost, gDataPort);
  for i:= gPriceInfoList.Count -1 downto 0 do begin
    PriceInfo:= TPriceInfo(gPriceInfoList.Objects[i]);
    if not Assigned(PriceInfo) then Continue;
    case PriceInfo.EditState of
      desNone: Continue;
      desAdd:
        with PriceInfo do begin
          cdsLookup.Close;
          cdsLookup.CommandText:=
            Format('INSERT INTO TabCarPrice '+
              '(UserID, PriceName, StartPoint, StartPointX1, StartPointY1, '+
              'StartPointX2, StartPointY2, EndPoint, EndPointX1, EndPointY1, '+
              'EndPointX2, EndPointY2, Distance, RoadPrice, BridgePrice, OilPrice) '+
              'VALUES (%d,%s,%s,%3.6f,%3.6f,'+
              '%3.6f,%3.6f,%s,%3.6f,%3.6f,'+
              '%3.6f,%3.6f,%3.6f,%3.2f,%3.2f,%3.2f)',
            [gCurUsrInfo.UsrId, QuotedStr(PriceInfo.Name), QuotedStr(StartPtName), StartPtLong1, StartPtLat1,
             StartPtLong2, StartPtLat2, QuotedStr(EndPtName), EndPtLong1, EndPtLat1,
             EndPtLong2, EndPtLat2, Distance, RoadPrice, BridgePrice, OilPrice]);
          cdsLookup.Execute;
          PriceInfo.EditState:= desNone;
        end;
      desDel:
        begin
          cdsLookup.Close;
          cdsLookup.CommandText:= Format('DELETE FROM TabCarPrice WHERE (Id = %d)', [PriceInfo.Id]);
          cdsLookup.Execute;
        end;
      desEdit:
        with PriceInfo do begin
          cdsLookup.Close;
          cdsLookup.CommandText:=
            Format('UPDATE TabCarPrice '+
              'SET PriceName = %s, StartPoint = %s, '+
              'StartPointX1 = %3.6f, StartPointY1 = %3.6f, StartPointX2 = %3.6f, '+
              'StartPointY2 = %3.6f, EndPoint = %s, EndPointX1 = %3.6f, '+
              'EndPointY1 = %3.6f, EndPointX2 = %3.6f, EndPointY2 = %3.6f, '+
              'Distance = %3.6f, RoadPrice = %3.2f, BridgePrice = %3.2f, OilPrice = %3.2f '+
              'WHERE (Id = %d)',
            [QuotedStr(PriceInfo.Name), QuotedStr(StartPtName),
             StartPtLong1, StartPtLat1, StartPtLong2,
             StartPtLat2, QuotedStr(EndPtName), EndPtLong1,
             EndPtLat1, EndPtLong2, EndPtLat2,
             Distance, RoadPrice, BridgePrice, OilPrice, PriceInfo.Id]);
          cdsLookup.Execute;
          PriceInfo.EditState:= desNone;
        end;
    end;
  end;
  CloseDB;
end;

procedure TDataModule1.SaveTaxiDispatch(AFn: string);
begin
  if not scGpsDB.Connected then OpenDB(gDataHost, gDataPort);
  // 业务量，日期
  cdsLookup.Close;
  cdsLookup.CommandText:= 'SELECT COUNT(*) AS Flux, CAST(PhoneInTime AS int) AS Expr1 '+
    'FROM TaxiDispatch GROUP BY CAST(PhoneInTime AS int)';
  cdsLookup.Open;
  cdsLookup.SaveToFile(AFn+ '00.xml', dfXML);
  //业务量，时间
  cdsLookup.Close;
  cdsLookup.CommandText:= 'SELECT COUNT(*) AS Flux, DATEPART(hour, PhoneInTime) AS Expr1 '+
    'FROM TaxiDispatch GROUP BY DATEPART(hour, PhoneInTime)';
  cdsLookup.Open;
  cdsLookup.SaveToFile(AFn+ '01.xml', dfXML);
  //业务量，处理时间
  cdsLookup.Close;
  cdsLookup.CommandText:= 'SELECT COUNT(*) AS Flux, DATEDIFF(s, PhoneInTime, DispatchTime) AS Expr1 '+
    'FROM TaxiDispatch '+
    'GROUP BY DATEDIFF(s, PhoneInTime, DispatchTime) '+
    'ORDER BY DATEDIFF(s, PhoneInTime, DispatchTime)';
  cdsLookup.Open;
  cdsLookup.SaveToFile(AFn+ '02.xml', dfXML);

//  cdsLookup.Close;
//  cdsLookup.CommandText:='SELECT UsrId, CarId, PhoneInTime, DispatchTime FROM TaxiDispatch';
//  cdsLookup.Open;
//  cdsLookup.SaveToFile(AFn+ '03.xml', dfXML);
  CloseDB;
end;

function TDataModule1.TabExits(TabName: string): Boolean;
//判断数据表是否存在
//select *
//from sysobjects
//where id = object_id(N'[dbo].[phone]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
begin
  cdsLookup.Close;
  cdsLookup.CommandText:= Format(
    'select * from sysobjects where id = object_id(N''[dbo].[%s]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1',
    [TabName]);
  cdsLookup.Open;
  Result:= not cdsLookup.IsEmpty;
end;

{-----------------------------------------------------------------------------
  Procedure: TDataModule1.TaskClear
  Author:    Administrator
  Date:      14-八月-2003
  Arguments: CarIds: string
  Result:    None
    清除相关车辆的任务；
-----------------------------------------------------------------------------}
//procedure TDataModule1.TaskClear(CarIds: string);
//begin
//  cdsLookup.Close;
//  cdsLookup.CommandText:=
//    Format('DELETE FROM NavigateTask WHERE CarId IN (%s)',
//      [CarIds]);
//  cdsLookup.Execute;
//  CloseDB;
//end;

{-----------------------------------------------------------------------------
  Procedure: TDataModule1.TaskSet
  Author:    Administrator
  Date:      14-八月-2003
  Arguments: CarIds: string
  Result:    None
-----------------------------------------------------------------------------}
//procedure TDataModule1.TaskSet(CarIds, TaskName: string; WalkLineId: Cardinal);
//var
//  i: Integer;
//  iCarId: Cardinal;
//  slTemp: TStringList;
//  StartX, StartY, EndX, EndY: Double;
//  StartRanger, EndRanger: Integer;
//begin
//  slTemp:= TStringList.Create;
//  try
//    slTemp.CommaText:= CarIds;
//    //起始点
//    cdsLookup.Close;
//    cdsLookup.CommandText:=
//      Format('SELECT X, Y, Distance FROM WalkLineListTab '+
//        'WHERE (WalkLineID = %d) AND (PointIndex IN '+
//          '(SELECT MIN(PointIndex) AS Expr1 FROM WalkLineListTab '+
//          'GROUP BY WalkLineID HAVING (WalkLineID = %d)))',
//        [WalkLineId, WalkLineId]);
//    cdsLookup.Open;
//    if cdsLookup.IsEmpty then EGpsDBError.Create('航线对应的定位点数据不存在。');
//    StartX:= cdsLookup.FieldValues['X'];
//    StartY:= cdsLookup.FieldValues['Y'];
//    StartRanger:= cdsLookup.FieldValues['Distance'];
//    //终止点
//    cdsLookup.Close;
//    cdsLookup.CommandText:=
//      Format('SELECT X, Y, Distance FROM WalkLineListTab '+
//        'WHERE (WalkLineID = %d) AND (PointIndex IN '+
//          '(SELECT MIN(PointIndex) AS Expr1 FROM WalkLineListTab '+
//          'GROUP BY WalkLineID HAVING (WalkLineID = %d)))',
//        [WalkLineId, WalkLineId]);
//    cdsLookup.Open;
//    if cdsLookup.IsEmpty then EGpsDBError.Create('航线对应的定位点数据不存在。');
//    EndX:= cdsLookup.FieldValues['X'];
//    EndY:= cdsLookup.FieldValues['Y'];
//    EndRanger:= cdsLookup.FieldValues['Distance'];
//    //更新数据
//    for i:= 0 to slTemp.Count- 1 do begin
//      iCarId:= StrToInt(slTemp[i]);
//      cdsLookup.CommandText:=
//        Format('INSERT INTO NavigateTask(TaskName, CarId, TaskState, WalkLineId, CreateTime, '+
//          'StartPointX, StartPointY, StartRange, EndPointX, EndPointY, EndRange) '+
//          'VALUES(%s, %d, %d, %d, GETDATE(), %3.7f, %3.7f, %d, %3.7f, %3.7f, %d)',
//          [QuotedStr(TaskName), iCarId, 0, WalkLineId,
//           StartX, StartY, StartRanger, EndX, EndY, EndRanger]);
//      cdsLookup.Execute;
//      //PlanTaskShowProgress(i+1, slTemp.Count);
//    end;
//    CloseDB;
//  finally
//    slTemp.Free;
//  end;
//end;

function TDataModule1.TrackQry(CarIds: string; StartTime,
  EndTime: TDateTime; var RecCount: Integer): TClientDataSet;
//轨迹查询
var
  sSql: string;
begin
  sSql:= Format('SELECT Count(*) as TempRecCount '+
      'FROM GpsInfo '+
      'WHERE (CarId in (%s)) and (GPsTime > %s) AND (gpsTime < %s)', // and (GpsValid=1)',
      [CarIDs,
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime))]);
  cdsLookup.Close;
  cdsLookup.CommandText:= sSql;
  cdsLookup.Open;
  RecCount:= cdsLookup.FieldValues['TempRecCount'];

  sSql:= Format('SELECT Count(*) as TempRecCount '+
      'FROM GpsInfoPack '+
      'WHERE (CarId in (%s)) and (GPsTime > %s) AND (gpsTime < %s)', // and (GpsValid=1)',
      [CarIDs,
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime))]);
  cdsLookup.Close;
  cdsLookup.CommandText:= sSql;
  cdsLookup.Open;
  RecCount:= RecCount+ cdsLookup.FieldValues['TempRecCount'];

  sSql:= Format('SELECT CarId, GpsTime, RecvTime, Longitude, Latitude, Speed, Head, GpsValid '+
      'FROM GpsInfo '+
      'WHERE (CarId in (%s)) and (GPsTime > %s) AND (gpsTime < %s)'+ // and (GpsValid=1)'+
      'UNION '+
      'SELECT CarId, GpsTime, RecvTime, Longitude, Latitude, Speed, Head, GpsValid '+
      'FROM GpsInfoPack '+
      'WHERE (CarId in (%s)) and (GPsTime > %s) AND (gpsTime < %s)', // and (GpsValid=1)',
      [CarIDs,
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime)),
       CarIDs,
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime))]);
  //计算总数
  cdsLookup.Close;
  cdsLookup.CommandText:= sSql;
  cdsLookup.Open;
  Result:= cdsLookup;
end;

function TDataModule1.TrackStat(CarIds: string; StartTime,
  EndTime: TDateTime): TClientDataSet;
{-----------------------------------------------------------------------------
  Date:      23-二月-2004
-----------------------------------------------------------------------------}
var
  sSql: string;
  db_name: string;
begin
//  sSql:= Format('SELECT CarId, COUNT(*) AS RecCount, MIN(GpsTime) AS MinTime, MAX(GpsTime) AS MaxTime, MAX(Speed) AS MaxSpeed, AVG(Speed) AS AvgSpeed '+
//      'FROM GpsInfo '+
//      'WHERE (GpsValid = 1) AND (GpsTime > %s) AND (GpsTime < %s) AND (CarId IN (%s)) '+
//      'GROUP BY CarId '+
//      'UNION '+
//      'SELECT CarId, COUNT(*) AS RecCount, MIN(GpsTime) AS MinTime, MAX(GpsTime) AS MaxTime, MAX(Speed) AS MaxSpeed, AVG(Speed) AS AvgSpeed '+
//      'FROM GpsInfoPack '+
//      'WHERE (GpsValid = 1) AND (GpsTime > %s) AND (GpsTime < %s) AND (CarId IN (%s)) '+
//      'GROUP BY CarId ',
//      [QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
//       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime)),
//       CarIDs,
//       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
//       QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime)),
//       CarIDs ]);

//  sSql:= Format('DECLARE @CarIds varchar(5000) '+
//      'set @CarIds = %s '+
//      'DECLARE @StartTime varchar(20) '+
//      'set @StartTime = %s '+
//      'DECLARE @EndTime varchar(20) '+
//      'set @EndTime = %s '+
//      'EXEC GetGpsStat @CarIds, @StartTime, @EndTime ',
//      [QuotedStr(CarIDs),
//        QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
//        QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime))
//        ]);

  sSql := Format('EXEC GetGpsStat %s, %s, %s ',
      [QuotedStr(CarIDs),
        QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', StartTime)),
        QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', EndTime))
        ]);

  db_name := getDBName;

  if GIsBaseDBAuth then
  begin
    Result := TBaseDBForm.OpenSelectFromDataset(db_name, sSql);
  end
  else begin
    //计算总数
    cdsLookup.Close;
    cdsLookup.CommandText:= sSql;
    cdsLookup.Open;
    Result:= cdsLookup;
  end;
end;

procedure TDataModule1.UserAdd(UserInfo: TUsrInfo);
begin
  cdsLookup.Close;
  cdsLookup.CommandText:=
    Format('INSERT INTO UsrInfo(UsrName, UsrPwd, UsrPurview, WatchRangeId, Ranger) '+
      'VALUES(%s, %s, %d, %d, %s)',
      [QuotedStr(UserInfo.UsrName), QuotedStr(UserInfo.UsrPwd),
       UserInfo.UsrPurview, UserInfo.WatchRangeId, QuotedStr(UserInfo.Ranger)]);
  cdsLookup.Execute;
end;

procedure TDataModule1.UserDel(UserInfo: TUsrInfo);
begin
  cdsLookup.Close;
  cdsLookup.CommandText:=
    Format('DELETE FROM UsrInfo WHERE UsrId=%d', [UserInfo.UsrId]);
  cdsLookup.Execute;
end;

procedure TDataModule1.UserEdit(UserInfo: TUsrInfo);
begin
  cdsLookup.Close;
  cdsLookup.CommandText:=
    Format('UPDATE UsrInfo '+
      'SET UsrName=%s, UsrPwd=%s, UsrPurview=%d, WatchRangeId=%d, Ranger=%s '+
      'WHERE UsrId=%d',
      [QuotedStr(UserInfo.UsrName), QuotedStr(UserInfo.UsrPwd),
       UserInfo.UsrPurview, UserInfo.WatchRangeId, QuotedStr(UserInfo.Ranger),
       UserInfo.UsrId]);
  cdsLookup.Execute;
end;

procedure TDataModule1.UsrInfoEdit(UsrInfo: TUsrInfo;
  EditState: TEditState);
begin
  if not scGpsDB.Connected then OpenDB(gDa