zcfzmain.pas

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

unit ZCFZMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, ExtCtrls, StdCtrls, ImgList, SUIDlg, ComCtrls, Grids,
  RexCalendar, RM_Dataset, RM_Common, RM_Class, DB, BusinessSkinForm,
  bsSkinCtrls;

type
  TZCFZMainFrm = class(TForm)
    bsBusinessSkinForm1: TbsBusinessSkinForm;
    bsSkinMainMenuBar1: TbsSkinMainMenuBar;
    Panel1: TbsSkinPanel;
    ImgMain: TImage;
    StatusBar1: TbsSkinStatusBar;
    bsSkinStatusPanel1: TbsSkinStatusPanel;
    bsSkinMainMenu1: TbsSkinMainMenu;
    ShuJuGuanLi: TMenuItem;
    JieZhuanYuE: TMenuItem;
    ShuJuLuRuDaoRu: TMenuItem;
    YueJiBiaoShuJuDaoRu: TMenuItem;
    ShuJuLuRu: TMenuItem;
    mniShuJuGuiBin: TMenuItem;
    mniDaYinShuChu: TMenuItem;
    mniDaYinKeMuFaShengE: TMenuItem;
    mniDaYinXiangMuGuiBin: TMenuItem;
    mniReturn: TMenuItem;
    N11: TMenuItem;
    N31: TMenuItem;
    N32: TMenuItem;
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure JieZhuanYuEClick(Sender: TObject);
    procedure mniReturnClick(Sender: TObject);
    procedure ShuJuLuRuClick(Sender: TObject);
    procedure YueJiBiaoShuJuDaoRuClick(Sender: TObject);
    procedure mniShuJuGuiBinClick(Sender: TObject);
    procedure mniDaYinXiangMuGuiBinClick(Sender: TObject);
    procedure mniDaYinKeMuFaShengEClick(Sender: TObject);
    procedure N11Click(Sender: TObject);
    procedure N31Click(Sender: TObject);
    procedure N32Click(Sender: TObject);
  private
  protected

  public
    lhcDate: TRexCalendar;
  end;

var
  ZCFZMainFrm: TZCFZMainFrm;

implementation

uses ZCFZ_DataModel, FunctionModel, ZCFZShuJuLuRu, ZCFZSjhf, ZCFZSjDr,
  ZCFZSjSc;

{$R *.dfm}

procedure TZCFZMainFrm.FormShow(Sender: TObject);
var
  loop: Integer;
  s: string;
begin
  if (screen.width <> orignwidth) then
  begin
    width := screen.width;
    height := screen.height - 23;
    with ZCFZMainFrm do
    begin
      for loop := 0 to ControlCount - 1 do
      begin
        Controls[loop].top := Controls[loop].top * round(screen.height /
          orignheight);
        Controls[loop].left := Controls[loop].left * round(screen.width /
          orignwidth);
        Controls[loop].width := Controls[loop].width * round(screen.width /
          orignwidth);
        Controls[loop].height := Controls[loop].height * round(screen.height /
          orignheight);
      end;
    end;
  end;
  PanelHeight := Panel1.height - StatusBar1.height - 2;
  PanelWidth := Panel1.width;
  PanelTop := Panel1.top;
  PanelLeft := Panel1.left;
  s := Format('现在处理的是%d年%2.2d月数据', [Year, month]);
  SetWindowText(Handle, PChar(s));

  ImgMain.Picture.LoadFromFile(ExtractFilePath(Application.ExeName) +
    '风景这边独好.jpg');
  lhcDate := TRexCalendar.Create(self);
  lhcDate.RunPath := workPath;
  lhcDate.ReadJRIni;
  try
    s := '系统日期:' + FormatDateTime('yyyy年mm月dd日,', now);
    s := s + ' 农历：' + lhcDate.GanZhiYear + ' ' + lhcDate.cnYear;
    s := s + lhcDate.cnMonth + lhcDate.cnDay + ' ' + lhcDate.FuOrJiuName;
    s := s + format('，距“%s”还有%d天。', [lhcDate.JieRiDaysName.JieRiName,
      lhcDate.JieRiDaysName.JieRiDays]);
  finally
    FreeAndNil(lhcDate);
  end;
  bsSkinStatusPanel1.Caption := s;
end;

procedure TZCFZMainFrm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action := caFree;
  ZCFZMainFrm := nil;
end;

procedure TZCFZMainFrm.JieZhuanYuEClick(Sender: TObject);
var
  s, s1: string;
  BakFlg: Boolean;
begin
  with ZCFZ_DM do
  begin
    s1 := Format('select * from zcfzxmb_bak where ny=%d%2.2d', [Year, Month]);
    ZCFZ_Qry.SQL.Clear;
    ZCFZ_Qry.SQL.Add(s1);
    ZCFZ_Qry.Active := True;
    if not ZCFZ_Qry.Eof then //本期数据已经备份，元须再次备份
      BakFlg := True
    else
      BakFlg := False;
    ZCFZ_Qry.SQL.Clear;
    ZCFZ_Qry.SQL.Add('select sum(bjye) as hzbjye from zcfzxmb');
    ZCFZ_Qry.Active := True;
    if ZCFZ_Qry['hzbjye'] <> 0 then //本季余额为0　时不需要结转
    begin
      //将本季资产负债表转和历史库
      s1 := Format('INSERT INTO zcfzxmb_bak (ny, hh, xmdh, xmmc, xmfl,' +
        'sjye, bjzje, bjye) ' +
        'SELECT %d%2.2d,hh, xmdh, xmmc, xmfl,' +
        'sjye, bjzje, bjye FROM zcfzxmb ', [Year, Month]);
      if not BakFlg then
      begin
        ZCFZ_SQLPrc.Script.Text := s1;
        ZCFZ_SQLPrc.Execute;
      end;

      //将本季余额结转到上季余额
      s1 := 'UPDATE zcfzxmb SET sjye=bjye';
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;

      //将本季余额和本季增减额全部置为0
      s1 := 'UPDATE zcfzxmb SET bjye=0,bjzje=0';
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
      //将资产负债归并表本报余额置为0
      s1 := 'UPDATE zcfzgbb SET bjye=0';
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    end;
    //将月计表转和历史库
    s1 := Format('INSERT INTO yjb_bak (ny, bh, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF) ' +
      'SELECT %d%2.2d, bh, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF FROM yjb ', [Year, Month]);
    if not BakFlg then
    begin
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    end;
    //将zcfzyjb清空
    s1 := 'delete from zcfzyjb';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;
    //将yjb清空
    s1 := 'delete from yjb';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;
    //将yjb_hj清空
    s1 := 'delete from yjb_hj';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;

    //将营业月计表转和历史库
    s1 := Format('INSERT INTO zcfzhsyjb_bak (ny, zw, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF) ' +
      'SELECT %d%2.2d, zw, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF FROM zcfzhsyjb ', [Year, Month]);
    if not BakFlg then
    begin
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    end;
    //将zcfzHSyjb清空
    s1 := 'delete from zcfzhsyjb';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;

    //将国库月计表的本期余额转为上期余额
    ZCFZ_Qry.SQL.Clear;
    ZCFZ_Qry.SQL.Add('select sum(bqyejf) as bqyejf, sum(bqyedf) as bqyedf ' +
      ' from gkk_yjb where left(kmdh,1)<>''6'' and length(kmdh)<=3 ');
    ZCFZ_Qry.Active := True;
    if ZCFZ_Qry['bqyejf'] = 0 then Exit; //本季余额为0　时不需要结转
    if ZCFZ_Qry['bqyejf'] <> ZCFZ_Qry['bqyedf'] then
    begin
      s := '国库月计表本期余额借贷方数据不平，请审核!';
      bsSkinMessage1.MessageDlg(s, mtWarning, [mbOk], 0);
      Exit;
    end;
    //将国库月计表转和历史库
    s1 := Format('INSERT INTO gkk_yjb_bak (ny, zw, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF) ' +
      'SELECT %d%2.2d, zw, kmdh, sqyeJF, sqyeDF,' +
      'bqfseJF, bqfseDF, bqyeJF, bqyeDF FROM gkk_yjb ', [Year, Month]);
    if not BakFlg then
    begin
      ZCFZ_SQLPrc.Script.Text := s1;
      ZCFZ_SQLPrc.Execute;
    end;
    //将上期余额全部置为0
    s1 := 'UPDATE gkk_yjb SET sqyejf=0,sqyedf=0';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;

    //将本季余额结转到上季余额
    s1 := 'UPDATE gkk_yjb SET sqyejf=bqyejf,sqyedf=bqyedf';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;

    //将本季余额和本季发生额全部置为0
    s1 := 'UPDATE gkk_yjb SET bqyejf=0,bqyedf=0,bqfsejf=0,bqfsedf=0';
    ZCFZ_SQLPrc.Script.Text := s1;
    ZCFZ_SQLPrc.Execute;

  end;
end;

procedure TZCFZMainFrm.mniReturnClick(Sender: TObject);
begin
  Close;
end;

procedure TZCFZMainFrm.ShuJuLuRuClick(Sender: TObject);
begin
  ZCFZShuJuWeiHuFrm := TZCFZShuJuWeiHuFrm.Create(application);
  ZCFZShuJuWeiHuFrm.ShowModal;
  ZCFZShuJuWeiHuFrm.Free;
end;

procedure TZCFZMainFrm.YueJiBiaoShuJuDaoRuClick(Sender: TObject);
begin
  ZCFZSjDrFrm := TZCFZSjDrFrm.Create(application);
  ZCFZSjDrFrm.ShowModal;
  ZCFZSjDrFrm.Free;
end;

procedure TZCFZMainFrm.mniShuJuGuiBinClick(Sender: TObject);
var
  s, yefx: string;
  bz4100: Integer;
  lhztzk, lhztzj: Double;
begin
  with ZCFZ_DM do
  begin
    ZCFZ_SQLPrc.Script.Text := 'delete from yjb';
    ZCFZ_SQLPrc.Execute;
    ZCFZ_Qry.SQL.Clear;
    ZCFZ_Qry.SQL.Add('select * from zcfzhsyjb');
    ZCFZ_Qry.Active := True;
    if ZCFZ_Qry.Eof then
    begin
      s := '你还没有导入核算系统月计表!';
      bsSkinMessage1.MessageDlg(s, mtWarning, [mbOk], 0);
      Exit;
    end;
    ZCFZ_Qry1.SQL.Clear;
    s := 'select sum(bqfsejf) as jf,sum(bqfsedf) as df from gkk_yjb';
    ZCFZ_Qry1.SQL.Add(s);
    ZCFZ_Qry1.Active := True;
    if (ZCFZ_Qry1['jf'] = 0) and (ZCFZ_Qry1['df'] = 0) then
    begin
      s := '你还没有录入国库月计表数据!';
      bsSkinMessage1.MessageDlg(s, mtWarning, [mbOk], 0);
      Exit;
    end;
    ZCFZ_QryEdit.SQL.Clear;
    ZCFZ_QryEdit.SQL.Add('select * from yjb');
    ZCFZ_QryEdit.Active := True;

    ZCFZ_Qry2.SQL.Clear;
    ZCFZ_Qry2.SQL.Add('select * from xkmb');
    ZCFZ_Qry2.Active := True;
    while not ZCFZ_Qry2.Eof do
    begin
      s := Format('select * from zcfzhsyjb where kmdh=%s',
        [ZCFZ_Qry2['kmdh']]);
      ZCFZ_Qry.SQL.Clear;
      ZCFZ_Qry.SQL.Add(s);
      ZCFZ_Qry.Active := True;
      s := Format('select * from gkk_yjb where kmdh=%s',
        [ZCFZ_Qry2['kmdh']]);
      ZCFZ_Qry1.SQL.Clear;
      ZCFZ_Qry1.SQL.Add(s);
      ZCFZ_Qry1.Active := True;
      if (not ZCFZ_Qry.Eof) and (not ZCFZ_Qry1.Eof) then
      begin
        ZCFZ_QryEdit.Insert;
        ZCFZ_QryEdit['bh'] := ZCFZ_Qry2['xh'];
        ZCFZ_QryEdit['kmdh'] := ZCFZ_Qry2['kmdh'];
        lhztzk := ZCFZ_Qry['sqyejf'] + ZCFZ_Qry1['sqyejf'] -
          ZCFZ_Qry['sqyedf'] - ZCFZ_Qry1['sqyedf'];
        if lhztzk > 0 then
          ZCFZ_QryEdit['sqyejf'] := lhztzk
        else
          ZCFZ_QryEdit['sqyedf'] := Abs(lhztzk);
        ZCFZ_QryEdit['bqfsejf'] := ZCFZ_Qry['bqfsejf'] + ZCFZ_Qry1['bqfsejf'];
        ZCFZ_QryEdit['bqfsedf'] := ZCFZ_Qry['bqfsedf'] + ZCFZ_Qry1['bqfsedf'];
        lhztzk := ZCFZ_Qry['bqyejf'] + ZCFZ_Qry1['bqyejf'] -
          ZCFZ_Qry['bqyedf'] - ZCFZ_Qry1['bqyedf'];
        if lhztzk > 0 then
          ZCFZ_QryEdit['bqyejf'] := lhztzk
        else
          ZCFZ_QryEdit['bqyedf'] := Abs(lhztzk);
        ZCFZ_QryEdit.Post;
      end;
      if (not ZCFZ_Qry.Eof) and (ZCFZ_Qry1.Eof) then
      begin
        ZCFZ_QryEdit.Insert;
        ZCFZ_QryEdit['bh'] := ZCFZ_Qry2['xh'];
        ZCFZ_QryEdit['kmdh'] := ZCFZ_Qry2['kmdh'];
        ZCFZ_QryEdit['sqyejf'] := ZCFZ_Qry['sqyejf'];
        ZCFZ_QryEdit['sqyedf'] := ZCFZ_Qry['sqyedf'];
        ZCFZ_QryEdit['bqfsejf'] := ZCFZ_Qry['bqfsejf'];
        ZCFZ_QryEdit['bqfsedf'] := ZCFZ_Qry['bqfsedf'];
        ZCFZ_QryEdit['bqyejf'] := ZCFZ_Qry['bqyejf'];
        ZCFZ_QryEdit['bqyedf'] := ZCFZ_Qry['bqyedf'];
        ZCFZ_QryEdit.Post;
      end;
      if (ZCFZ_Qry.Eof) and (not ZCFZ_Qry1.Eof) then
      begin
        ZCFZ_QryEdit.Insert;
        ZCFZ_QryEdit['bh'] := ZCFZ_Qry2['xh'];
        ZCFZ_QryEdit['kmdh'] := ZCFZ_Qry2['kmdh'];
        ZCFZ_QryEdit['sqyejf'] := ZCFZ_Qry1['sqyejf'];
        ZCFZ_QryEdit['sqyedf'] := ZCFZ_Qry1['sqyedf'];
        ZCFZ_QryEdit['bqfsejf'] := ZCFZ_Qry1['bqfsejf'];
        ZCFZ_QryEdit['bqfsedf'] := ZCFZ_Qry1['bqfsedf'];
        ZCFZ_QryEdit['bqyejf'] := ZCFZ_Qry1['bqyejf'];
        ZCFZ_QryEdit['bqyedf'] := ZCFZ_Qry1['bqyedf'];
        ZCFZ_QryEdit.Post;
      end;
      ZCFZ_Qry2.Next;
    end;
   end;
end;

procedure TZCFZMainFrm.mniDaYinXiangMuGuiBinClick(Sender: TObject);
var
  i:Integer;
begin
  with ZCFZ_DM do
  begin
    ZCFZ_SQLPrc.Script.Text := 'delete from yjb_hj';
    ZCFZ_SQLPrc.Execute;