tradeimportunit.~pas

公交行业的管理系统

unit TradeImportUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, StdCtrls, Menus, Buttons, ComCtrls, ExtCtrls,
  DB, ADODB, DBClient;

type
  TTradeImportFrm = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    PageControl1: TPageControl;
    TabSheet2: TTabSheet;
    GroupBox1: TGroupBox;
    Label3: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label21: TLabel;
    Label22: TLabel;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    BitBtn7: TBitBtn;
    BitBtn9: TBitBtn;
    TabSheet3: TTabSheet;
    GroupBox2: TGroupBox;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    ComboBox5: TComboBox;
    ComboBox6: TComboBox;
    BitBtn8: TBitBtn;
    BitBtn10: TBitBtn;
    TabSheet4: TTabSheet;
    GroupBox3: TGroupBox;
    Label6: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    ComboBox7: TComboBox;
    ComboBox8: TComboBox;
    BitBtn11: TBitBtn;
    BitBtn12: TBitBtn;
    TabSheet5: TTabSheet;
    GroupBox4: TGroupBox;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label23: TLabel;
    Label24: TLabel;
    ComboBox9: TComboBox;
    ComboBox10: TComboBox;
    ComboBox11: TComboBox;
    ComboBox12: TComboBox;
    BitBtn13: TBitBtn;
    BitBtn14: TBitBtn;
    TabSheet6: TTabSheet;
    GroupBox5: TGroupBox;
    Label16: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    ComboBox13: TComboBox;
    ComboBox14: TComboBox;
    BitBtn15: TBitBtn;
    BitBtn16: TBitBtn;
    TabSheet7: TTabSheet;
    BitBtn17: TBitBtn;
    BitBtn20: TBitBtn;
    GroupBox6: TGroupBox;
    Label25: TLabel;
    Label26: TLabel;
    Label27: TLabel;
    ComboBox15: TComboBox;
    ComboBox16: TComboBox;
    TabSheet8: TTabSheet;
    GroupBox7: TGroupBox;
    Label28: TLabel;
    Label29: TLabel;
    Label30: TLabel;
    ComboBox17: TComboBox;
    ComboBox18: TComboBox;
    BitBtn23: TBitBtn;
    BitBtn24: TBitBtn;
    TabSheet9: TTabSheet;
    GroupBox8: TGroupBox;
    Label31: TLabel;
    Label32: TLabel;
    Label33: TLabel;
    ComboBox19: TComboBox;
    ComboBox20: TComboBox;
    BitBtn25: TBitBtn;
    BitBtn26: TBitBtn;
    TabSheet1: TTabSheet;
    RichEdit1: TRichEdit;
    BitBtn19: TBitBtn;
    Panel4: TPanel;
    Label1: TLabel;
    SpeedButton1: TSpeedButton;
    Edit1: TEdit;
    ADOConnection1: TADOConnection;
    OpenDialog1: TOpenDialog;
    ClientDataSet1: TClientDataSet;
    procedure SpeedButton1Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
    procedure BitBtn8Click(Sender: TObject);
    procedure BitBtn17Click(Sender: TObject);
    procedure BitBtn14Click(Sender: TObject);
    procedure BitBtn16Click(Sender: TObject);
    procedure BitBtn12Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn9Click(Sender: TObject);
    procedure BitBtn13Click(Sender: TObject);
    procedure BitBtn10Click(Sender: TObject);
    procedure BitBtn11Click(Sender: TObject);
    procedure BitBtn15Click(Sender: TObject);
    procedure BitBtn18Click(Sender: TObject);
    procedure BitBtn19Click(Sender: TObject);
    procedure BitBtn20Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure BitBtn21Click(Sender: TObject);
    procedure BitBtn22Click(Sender: TObject);
    procedure BitBtn23Click(Sender: TObject);
    procedure BitBtn24Click(Sender: TObject);
    procedure BitBtn25Click(Sender: TObject);
    procedure BitBtn26Click(Sender: TObject);
  private
    { Private declarations }
    judge:boolean;
    YearFrom,YearTo:string;
    MonthFrom,MonthTo:string;
    sString:string;
    temp:integer;
  public
    procedure TableToTableMonth(src:string;srcdata:string);
    procedure TableToTableYear(src:string;srcdata:string);
    procedure TableToTableMonthHY(src:string;srcdata:string);
    procedure TableToTableYearHY(src:string;srcdata:string);
    procedure TableToTableEpr(src:string;srcdata:string);
    procedure TableToTableJL(src:string;srcdata:string);
  end;

var
  TradeImportFrm: TTradeImportFrm;

implementation

uses HYLookImportDataUnit, HYLookImportDataUnit2,
  HYLookImportDataUnit3, BusTradeDataModuleUnit, Epr_SelectUnit,
  SelectDirectoryUnit;

{$R *.dfm}

procedure TTradeImportFrm.SpeedButton1Click(Sender: TObject);
var
        judge:boolean;
        temp:String;
        Sfilename,Dfilename:String;
begin
        //if(not opendialog1.Execute)then exit;
        if(SelectDirectoryForm.showmodal<>mrok)then exit;
        Edit1.Text:=SelectDirectoryForm.ShellTreeView1.SelectedFolder.PathName;
        if(copy(edit1.text,Length(edit1.text),1)<>'\')then
        begin
                edit1.Text:=edit1.Text+'\';
        end;
        ADOConnection1.Connected:=false;
        setcurrentdirectory(pchar(ExtractFilepath(Application.ExeName)+'database\'));
        copyfile(Pchar('ImportOutput.mdb'),pchar('InOut.mdb'),false);
        judge:=false;
        try
        ADOConnection1.ConnectionString:=
                'Provider=Microsoft.Jet.OLEDB.4.0;'+
                'Data Source=InOut.mdb;'+
                'Persist Security Info=False';
        ADOConnection1.Connected:=true;
        except
                showmessage('不能连接到导出文件');
        end;
        //Edit1.Text:=opendialog1.FileName;
        RichEdit1.Lines.Clear;
end;

procedure TTradeImportFrm.BitBtn7Click(Sender: TObject);
begin
        judge:=true;
        if Edit1.Text='' then
        begin
               showmessage('请输入导入数据的位置');
               exit;
        end;
        if Edit1.Text='' then
        begin
                showmessage('请输入导入数据的位置');
                exit;
        end;
        if ComboBox1.Text='' then
        begin
                showmessage('对不起！起始年份不能为空！请重新选择条件！');
                exit;
        end;
        if ComboBox2.Text='' then
        begin
                showmessage('对不起！终止年份不能为空！请重新选择条件！');
                exit;
        end;
        if (Length(ComboBox1.Text)<>4) or ((Length(ComboBox2.Text)<>4)) then
        begin
                showmessage('对不起！您选择的年份非法！请重新选择！');
                exit;
        end;
        try
                temp:=StrToInt(ComboBox1.Text);
        except
                showmessage('起始年份非法！请重新选择！');
                exit;
        end;
        try
                temp:=StrToInt(ComboBox2.Text);
        except
                showmessage('终止年份非法！请重新选择！');
                exit;
        end;
        if (StrToInt(ComboBox1.Text)<1900) or (StrToInt(ComboBox1.Text)>3000) then
        begin
                showmessage('对不起！您选择的"起始年份"非法！请重新选择！');
                exit;
        end;
        if (StrToInt(ComboBox2.Text)<1900) or (StrToInt(ComboBox2.Text)>3000) then
        begin
                showmessage('对不起！您选择的"终止年份"非法！请重新选择！');
                exit;
        end;
        if StrToInt(ComboBox1.Text)>StrToInt(ComboBox2.Text) then
        begin
                showmessage('对不起！起始年份不能大于终止年份！请重新选择条件！');
                exit;
        end;
        if ComboBox3.Text='' then
        begin
                showmessage('对不起！起始月份不能为空！请重新选择条件！');
                exit;
        end;
        if ComboBox4.Text='' then
        begin
                showmessage('对不起！终止月份不能为空！请重新选择条件！');
                exit;
        end;
        if StrToInt(ComboBox3.Text)>StrToInt(ComboBox4.Text) then
        begin
                showmessage('对不起！起始月份不能大于终止月份！请重新选择条件！');
                exit;
        end;
        try
                YearFrom:=ComboBox1.Text;
                YearTo:=ComboBox2.Text;
                MonthFrom:=ComboBox3.Text;
                MonthTo:=ComboBox4.Text;
                Screen.Cursor:=crHourGlass;
                TableToTableMonth('gjmonth.data','公交企业统计月报表');
                if judge=false then StrToInt('sf');
                TableToTableMonth('gjmonth_z.data','公交企业统计月报表_z');
                if judge=false then StrToInt('sf');
                showmessage('数据导入成功！');
                RichEdit1.Lines.Add('公交企业统计月报表导入成功');
        except
                showmessage('数据导入失败！');
                RichEdit1.Lines.Add('公交企业统计月报表导入失败');
        end;
        Screen.Cursor:=crDefault;
end;

procedure TTradeImportFrm.TableToTableMonth(src:string;srcdata:string);
var
SrcQuery,DesQuery:TADOQuery;
i,j:integer;
begin
        judge:=false;
        SrcQuery:=TADOQuery.create(self);
        SrcQuery.Connection:=ADOConnection1;
        SrcQuery.SQL.Clear;
        SrcQuery.SQL.Add('delete from '+srcdata);
        SrcQuery.ExecSQL;
        SrcQuery.SQL.Clear;
        SrcQuery.SQL.Add('select * from '+srcdata+' where 统计年份>='+YearFrom+
                         ' and 统计年份<='+YearTo+' and 统计月份>='+MonthFrom
                         +' and 统计月份<='+MonthTo);
        SrcQuery.Open;
       //------------------------
       try
                Clientdataset1.Close;
                Clientdataset1.FileName:=Edit1.Text+src;
                Clientdataset1.Open;
        except
                 MessageBox(self.Handle,PCHAR(srcdata+'不能被导入，请检查导入文件'+src+'是否存在或格式是否正确。'),pchar('消息'),MB_OK);
                 judge:=false;
                 exit;
        end;
        Clientdataset1.First;
        while not Clientdataset1.Eof do
        begin
                SrcQuery.Append;
                for i:=0 to SrcQuery.FieldCount-1 do
                        for j:=0 to Clientdataset1.FieldCount-1 do
                                if(SrcQuery.Fields[i].FieldName=Clientdataset1.Fields[j].FieldName)then
                                begin
                                        if(SrcQuery.Fields[i].FieldName<>'ID')then
                                        begin
                                               SrcQuery.Fields[i].Value:=Clientdataset1.Fields[j].Value;
                                        end;
                                end;
                SrcQuery.Post;
                clientdataset1.Next;
        end;
        //------------------------
    if (SrcQuery.RecordCount=0) then
    begin
         showmessage('导入的源数据库没有所需数据！');
         judge:=false;
         exit;
    end;
    SrcQuery.Close;
    SrcQuery.SQL.Clear;
    SrcQuery.SQL.Add('select distinct 企业代码 from '+srcdata+' where 统计年份>='+YearFrom
                     +' and 统计年份<='+YearTo+' and 统计月份>='+MonthFrom
                     +' and 统计月份<='+MonthTo);