zcfzsjdr.pas

本程序功能是将银行系统的月计表转换为所需要的资产负债表

unit ZCFZSjDr;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ComObj, Mask, ComCtrls, DB, variants, ExtCtrls,
  bsSkinBoxCtrls, bsSkinCtrls, BusinessSkinForm, bsSkinShellCtrls,
  bsfilectrl;

type
  TZCFZSjDrFrm = class(TForm)
    bsBusinessSkinForm1: TbsBusinessSkinForm;
    Button1: TbsSkinButton;
    Button2: TbsSkinButton;
    bsSkinStdLabel1: TbsSkinStdLabel;
    bsSkinStdLabel2: TbsSkinStdLabel;
    bsSkinRadioGroup1: TbsSkinRadioGroup;
    bsSkinDirectoryListBox1: TbsSkinDirectoryListBox;
    bsSkinFileListBox1: TbsSkinFileListBox;
    bsSkinDriveComboBox1: TbsSkinDriveComboBox;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  ZCFZSjDrFrm: TZCFZSjDrFrm;
  j: integer;

implementation

uses ZCFZ_DataModel, ZCFZ_BS, FunctionModel;

{$R *.DFM}

procedure TZCFZSjDrFrm.Button1Click(Sender: TObject);
var
  s, s1, s2, fileLj1: string;
begin
  with ZCFZ_DM do
  begin
    if bsSkinRadioGroup1.ItemIndex = 0 then
    begin
      s := Format('01%d%2.2d.21', [year, month]);
      fileLj1 := bsSkinFileListBox1.Directory + '\';
      if not FileExists(filelj1 + s) then
      begin
        bsSkinMessage1.MessageDlg(
          Format('你选择的%d年%2.2d月的核算系统月计表文件“%s”在该目录中不存在。',
          [year, month, s]), mtWarning, [mbOk], 0);
        Exit;
      end
    end;
    if bsSkinRadioGroup1.ItemIndex = 0 then
    begin
      s1 := 'delete from zcfzHSYjb';
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    end;
    //将资产负债的备份文件写入到相应表中
    try
      {$I-}
      MkDir('c:\zcfzTmpData');
      CopyFile(PChar(FileLj1 + s), pchar('c:\zcfzTmpData\' + s), True);
      s1 := replacesub(
        format('LOAD DATA LOCAL INFILE ''%s'' INTO TABLE %s ',
        ['c:\zcfzTmpData\' + s, 'zcfzHSYjb']), '\', '/');
      s1 := s1 + ' FIELDS TERMINATED BY ''\t'' LINES TERMINATED BY ''\r\n''';
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    finally
      Deletefile('c:\zcfzTmpData\' + s);
      RmDir('c:\zcfzTmpData');
    end;
    ZCFZ_SQLPrc.Script.Text := 'delete from zcfzyjb';
    ZCFZ_SQLPrc.Execute;

    ZCFZ_SQLPrc.Script.Text := 'delete from yjb';
    ZCFZ_SQLPrc.Execute;

    ZCFZ_QryEdit.SQL.Clear;
    ZCFZ_QryEdit.SQL.Add('select * from zcfzyjb');
    ZCFZ_QryEdit.Active := True;

    ZCFZ_QueryEdit1.SQL.Clear;
    ZCFZ_QueryEdit1.SQL.Add('select * from yjb');
    ZCFZ_QueryEdit1.Active := True;

    s:= 'select SUM((kmdh>44003 AND kmdh<510)*bqfsejf) AS bqfsejf50,'+
               'SUM((kmdh>502 AND kmdh<600)*bqfsedf) AS bqfsedf51 '+
               ' from zcfzHSYjb';
    ZCFZ_Qry1.SQL.Clear;
    ZCFZ_Qry1.SQL.Add(s);
    ZCFZ_Qry1.Active := True;

    if ZCFZ_Qry1['bqfsejf50'] <> 0 then
    begin
        bsSkinMessage1.MessageDlg(
          Format('损益类收入科目借方有发生额，请审查。',
          [year, month, s]), mtWarning, [mbOk], 0);
        Exit;
    end;
    if ZCFZ_Qry1['bqfsedf51'] <> 0 then
    begin
        bsSkinMessage1.MessageDlg(
          Format('损益类支出科目贷方有发生额，请审查。',
          [year, month, s]), mtWarning, [mbOk], 0);
        Exit;
    end;

    ZCFZ_Qry.SQL.Clear;
    ZCFZ_Qry.SQL.Add(Format('select * from %s', ['zcfzHSYjb']));
    ZCFZ_Qry.Active := True;
    while not ZCFZ_Qry.Eof do
    begin
      if ZCFZ_Qry['bqyeJf'] <> 0 then
      begin
        ZCFZ_QryEdit.Insert;
        ZCFZ_QryEdit['kmdh'] := ZCFZ_Qry['kmdh'];
        ZCFZ_QryEdit['bqye'] := ZCFZ_Qry['bqyeJf'];
        ZCFZ_QryEdit['jd'] := '0';
        ZCFZ_QryEdit.Post;
      end;
      if ZCFZ_Qry['bqyeDf'] <> 0 then
      begin
        ZCFZ_QryEdit.Insert;
        ZCFZ_QryEdit['kmdh'] := ZCFZ_Qry['kmdh'];
        ZCFZ_QryEdit['bqye'] := ZCFZ_Qry['bqyeDf'];
        ZCFZ_QryEdit['jd'] := '1';
        ZCFZ_QryEdit.Post;
      end;
      ZCFZ_Qry1.SQL.Clear;
      if ZCFZ_Qry['kmdh'] <> null then
      begin
        ZCFZ_Qry1.SQL.Add(Format('select * from xkmb where kmdh=%s',
          [ZCFZ_Qry['kmdh']]));
        ZCFZ_Qry1.Active := True;
        ZCFZ_QueryEdit1.Insert;
        ZCFZ_QueryEdit1['bh'] := ZCFZ_Qry1['xh'];
        ZCFZ_QueryEdit1['kmdh'] := ZCFZ_Qry['kmdh'];
        ZCFZ_QueryEdit1['sqyeJF'] := ZCFZ_Qry['sqyeJF'];
        ZCFZ_QueryEdit1['sqyeDF'] := ZCFZ_Qry['sqyeDF'];
        ZCFZ_QueryEdit1['bqfseJF'] := ZCFZ_Qry['bqfseJF'];
        ZCFZ_QueryEdit1['bqfseDF'] := ZCFZ_Qry['bqfseDF'];
        ZCFZ_QueryEdit1['bqyeJF'] := ZCFZ_Qry['bqyeJF'];
        ZCFZ_QueryEdit1['bqyeDF'] := ZCFZ_Qry['bqyeDF'];
        ZCFZ_QueryEdit1.Post;
      end;
      ZCFZ_Qry.Next;
    end;
    bsSkinMessage1.MessageDlg('数据导入完毕！', mtInformation, [mbOk], 0);
    Close;
  end;
end;

procedure TZCFZSjDrFrm.Button2Click(Sender: TObject);
begin
  Close;
end;

procedure TZCFZSjDrFrm.FormShow(Sender: TObject);
var
  s: string;
begin
  s := Format('核算系统生成的月计表，文件名：01%d%2.2d.21', [Year, Month]);
  bsSkinRadioGroup1.Items.Add(s);
  bsSkinRadioGroup1.ItemIndex:=0;
  bsSkinFileListBox1.Mask := Format('01%d%2.2d.21', [Year, Month]);
end;

end.