sfmanage.pas

一个信息查询系统,主要功能文件上传下载,信息浏览,打印等

unit sfmanage;

interface

uses Windows, SysUtils, Classes,Variants, Graphics, Forms, Controls, StdCtrls, 
  Buttons, ExtCtrls, ComCtrls, Grids, DBGrids, DB, Dialogs, Excel2000,
  OleServer;

type
  TOKBottomDlg9 = class(TForm)
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    DateTimePicker1: TDateTimePicker;
    Label3: TLabel;
    DateTimePicker2: TDateTimePicker;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    Panel2: TPanel;
    DBGrid2: TDBGrid;
    Label4: TLabel;
    Label5: TLabel;
    Edit1: TEdit;
    RadioButton2: TRadioButton;
    SpeedButton3: TSpeedButton;
    Bevel1: TBevel;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ExcelApplication1: TExcelApplication;
    ExcelWorkbook1: TExcelWorkbook;
    ExcelWorksheet1: TExcelWorksheet;
    SaveDialog1: TSaveDialog;
    SpeedButton5: TSpeedButton;
    Panel3: TPanel;
    SpeedButton6: TSpeedButton;
    SpeedButton4: TSpeedButton;
    SpeedButton7: TSpeedButton;
    Label6: TLabel;
    Panel4: TPanel;
    Memo2: TMemo;
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton5Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure DBGrid2DblClick(Sender: TObject);
    procedure SpeedButton6Click(Sender: TObject);
    procedure SpeedButton4Click(Sender: TObject);
    procedure SpeedButton7Click(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure Edit1KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  OKBottomDlg9: TOKBottomDlg9;

implementation

uses cgmain, selectdata, man_check;

{$R *.dfm}

procedure TOKBottomDlg9.SpeedButton1Click(Sender: TObject);
begin
    dbgrid1.Enabled :=true;
    ///////////////delete out_temp
    form1.tempquery1.Close;
    form1.tempquery1.SQL.Clear;
    form1.tempquery1.SQL.Add('delete from out_temp');
    form1.tempquery1.ExecSQL;
    ///////////////////////////////////select wf_zl_info
    form1.tempquery1.Close;
    form1.tempquery1.SQL.Clear;
    form1.tempquery1.SQL.Add('select * from "wf_zl_info" a where  cfqr='''+'N'+''''+' and sfbz='''+'N'+'''' +' and zlbc='''+'完整'+''' and a.wfsj between :p1 and :p2  order by  a.TZSHM ');
    form1.tempquery1.ParamByName('p1').Value:=datetostr(datetimepicker1.Date);
    form1.tempquery1.ParamByName('p2').Value:=datetostr(datetimepicker2.Date);
    form1.tempquery1.Open;
    ///////////////////// insert temp table <out_temp>
    Form1.Table1.Close;
    form1.Table1.TableName:='out_temp';
    form1.Table1.Open;
    form1.Table1.Edit;
    while not form1.tempquery1.Eof do
    begin
    form1.Table1.Append;
    form1.Table1['tzshm']:=form1.tempquery1['tzshm'];
    form1.Table1['bb']:='N';
    form1.Table1.Post; 
    form1.tempquery1.Next;
    end;
    form1.tempquery1.Close;
    form1.Table1.Close;
    form1.query5.Close;
    form1.query5.SQL.Clear;
    form1.Query5.UnPrepare;
    form1.query5.SQL.Add('select * from "wf_zl_info" a,"out_temp" b where a.tzshm=b.tzshm and cfqr='''+'N'+''''+' and sfbz='''+'N'+'''' +' and zlbc='''+'完整'+''' and a.wfsj between :p1 and :p2  order by  a.TZSHM ');
    form1.query5.ParamByName('p1').Value:=datetostr(datetimepicker1.Date);
    form1.query5.ParamByName('p2').Value:=datetostr(datetimepicker2.Date);
    form1.Query5.Prepare;
    form1.query5.Open;
   if  form1.Query5.RecordCount>0 then OKBottomDlg9.SpeedButton2.Enabled:=true
    else  OKBottomDlg9.SpeedButton2.enabled:=false;
     label6.Caption:=inttostr(form1.Query5.RecordCount)+'条记录';
end;

procedure TOKBottomDlg9.SpeedButton2Click(Sender: TObject);
var
bExcelvisible:boolean;
i,j,k:integer;
titlestr,fvalue,dispstr,filedna:string;
ch:char;
count:integer;
A:array of integer;  //输出列到excel中
begin
   if form1.Query5.Active<>true then  begin application.MessageBox('未进行查询','错误提示',MB_OK);exit;end;
   if form1.Query5.RecordCount=0 then exit;
   if application.MessageBox('上报到法院的记录进行司法标志，是否继续?','提示',MB_YesNo)=7 then exit;
    while not form1.Query5.Eof do
    begin
          if form1.query5['bb']='Y' then begin form1.Query5.Next;  continue;end;
          form1.tempquery1.Close;
          form1.tempquery1.SQL.Clear;
          form1.tempquery1.SQL.Add('update wf_zl_info');
          form1.tempquery1.SQL.Add('SET SFBZ='''+'Y'+''''+',scbz='''+'N'+'''');
          form1.tempquery1.SQL.Add('where TZSHM=:p1 and CFQR=:p2');
          form1.tempquery1.ParamByName('p1').AsString:=form1.Query5['TZSHM'];
          form1.tempquery1.ParamByName('p2').AsString:='N';                 //进行司法标志后，上传标志为NO
          form1.tempquery1.ExecSQL;
          form1.Query5.Next;
     end;

   if  application.MessageBox('是否输出到EXCEL中?','提示',MB_YesNo)=7 then
   begin
       form1.Query5.Active:=false;
       form1.Query5.Active:=true;
       if form1.query5.RecordCount=0 then  speedbutton2.Enabled := false ;
       form1.tempquery1.SQL.Clear;
       form1.tempquery1.SQL.Add('delete from out_temp where bb='''+'N'+'''');
       form1.tempquery1.ExecSQL;
       exit;
    end;
    bExcelvisible:=false;
    try
    Excelapplication1.Connect;
    except
    application.MessageBox('Excel可能未安装','提示',MB_OK);
    abort;
    end;
    excelapplication1.Caption:='从数据库中导出的数据';
    excelapplication1.Workbooks.Add(null,0);
    excelworkbook1.ConnectTo(excelapplication1.Workbooks[1]);
    if bexcelvisible=true then
         excelapplication1.visible[0]:=true;
    //add one page
    {try
    excelworkbook1.Worksheets.Add(null,excelworkbook1.Worksheets[excelworkbook1.Worksheets.Count],null,null,0);
    except
    application.MessageBox('创建EXCEL新页失败','错误提示',MB_OK);
    exit;
    end; }
    excelworksheet1.ConnectTo(excelworkbook1.Worksheets[1]as _worksheet);

    // page modify
    excelworksheet1.Range['A1','E1'].Merge(true);
    excelworksheet1.Range['A1','D2'].HorizontalAlignment:=$FFFFEFF4;
    excelworksheet1.Cells.Item[1,1].value:='长沙市车辆人行道违章停车抄牌数据';
    i:=2;
    count:=form1.Query5.FieldDefs.Count;
    setlength(A,count);//分配动态数给长度
    for j:=0 to (count-1) do
    begin      //初始化数组
    A[j]:=0;
      if (form1.Query5.Fields[j].DisplayLabel='WFSJ') then A[j]:=1;
      if (form1.Query5.Fields[j].DisplayLabel='TZSHM') then A[j]:=1;
      if (form1.Query5.Fields[j].DisplayLabel='WFDD') then A[j]:=1;
      if (form1.Query5.Fields[j].DisplayLabel='CPHM') then A[j]:=1;
      if (form1.Query5.Fields[j].DisplayLabel='ZLBC') then A[j]:=1;
      A[0]:=1;
   end;
    ////////////////////////////////////
     k:=1;
    for j:=0 to count-1 do
    begin
      if  A[j]=0 then continue;
      if  (form1.Query5.Fields[j].DisplayLabel='WFSJ') then filedna:='违章时间';
      if  (form1.Query5.Fields[j].DisplayLabel='ZFBM') then filedna:='执法中队名称';
      if  (form1.Query5.Fields[j].DisplayLabel='ZXR')  then filedna:='执法队员';
      if  (form1.Query5.Fields[j].DisplayLabel='CFFS') then filedna:='处罚方式';
      if  (form1.Query5.Fields[j].DisplayLabel='WFSY') then filedna:='违章事由';
      if  (form1.Query5.Fields[j].DisplayLabel='WFDD') then filedna:='违章地点';
      if  (form1.Query5.Fields[j].DisplayLabel='SFJE') then filedna:='实罚金额';
      if  (form1.Query5.Fields[j].DisplayLabel='CFSJ') then filedna:='处罚时间';
      if  (form1.Query5.Fields[j].DisplayLabel='SPR')  then filedna:='审批领导';
      if  (form1.Query5.Fields[j].DisplayLabel='TZSHM') then filedna:='陈述通知书编号';
      if  (form1.Query5.Fields[j].DisplayLabel='CPHM') then filedna:='车牌号码';
      if  (form1.Query5.Fields[j].DisplayLabel='CLLX') then filedna:='车辆类型';
      if  (form1.Query5.Fields[j].DisplayLabel='ZCCFSJ') then filedna:='最迟有效时间';
      if  (form1.Query5.Fields[j].DisplayLabel='CFJE') then filedna:='处罚金额';
      if  (form1.Query5.Fields[j].DisplayLabel='DSR')  then filedna:='当事人';
      if  (form1.Query5.Fields[j].DisplayLabel='ZLBC') then filedna:='资料完整性';
      if  (form1.Query5.Fields[j].DisplayLabel='CFJDSBM') then filedna:= '处罚决定书编号';
      if  (form1.Query5.Fields[j].DisplayLabel='BZ') then filedna:='备注';
      if  (form1.Query5.Fields[j].DisplayLabel='CFQR') then filedna:='处罚情况';
      // 设置标 题字体颜色
    excelworksheet1.Cells.Item[i,k].value:=filedna;
    excelworksheet1.Cells.Item[i,1].value:='陈述通知书编号';
    excelworksheet1.Cells.Item[i,k].Font.Color:=clblue;
    //excelworksheet1.Cells.Item[1,k].Color:=clskyblue;
    inc(k);
    end;
    excelworksheet1.Range['A1','D1'].Font.Color:=clskyblue;
    excelworksheet1.Range['A1','D1'].Font.Name:='隶书';
    excelworksheet1.Range['A1','D1'].Font.Size:=18;

    form1.Query5.First;
    i:=0;
    while not form1.Query5.Eof do
    begin
     k:=0;
     if form1.query5['bb']='Y' then begin form1.Query5.Next;  continue;end;
     for j:=0 to form1.Query5.FieldDefs.Count-1 do
     begin
     //if (form1.Query5.Fields[j].FieldKind=fkCalculated) then  continue;
     if  A[j]=0 then continue;

      dispstr:=form1.Query5.Fields[j].AsString;
      if  j=0 then dispstr:='No:'+dispstr;
      excelworksheet1.Cells.Item[i+3,k+1].value:=dispstr;
      inc(k);
     end;
     form1.Query5.next;
     inc(i);
     end;

      if bexcelvisible=false then
     begin
        if savedialog1.Execute then
         begin
        try
        excelworksheet1.SaveAs(savedialog1.FileName);
        application.MessageBox('保存成功！','提示',MB_OK);
        except
         application.MessageBox('保存失败！','提示',MB_OK);
         end;
         end;
         excelapplication1.Disconnect;
         excelapplication1.Quit;
       end;