ujycl.pas

行业应急系统 包括: 应急系统（待遇核发） 发放系统 开发环境:Delphi5 (sp1)+ Oracle 8.05 MultiExport是一个可以将数据按指定字段倒出

unit uJYCL;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, ComCtrls, Grids, DBGrids, Db, DBTables;

type
  TfrmJYCL = class(TForm)
    Panel1: TPanel;
    btnClose: TButton;
    btnPrior: TButton;
    Panel2: TPanel;
    pagMain: TPageControl;
    tabCondition: TTabSheet;
    tabSelect: TTabSheet;
    GroupBox1: TGroupBox;
    Label2: TLabel;
    edtSFZH: TEdit;
    Label3: TLabel;
    edtXM: TEdit;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    Label4: TLabel;
    souTemp: TDataSource;
    qryTemp: TQuery;
    btnNext: TButton;
    TabReason: TTabSheet;
    TabResult: TTabSheet;
    Label5: TLabel;
    Memo1: TMemo;
    DateSWSJ: TDateTimePicker;
    GroupBox2: TGroupBox;
    Label6: TLabel;
    lblSelect: TLabel;
    Label7: TLabel;
    rbtnSW: TCheckBox;
    rbtnQT: TCheckBox;
    stpTemp: TStoredProc;
    DBGrid2: TDBGrid;
    mmoResult: TMemo;
    souResult: TDataSource;
    qryResult: TQuery;
    procedure FormShow(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnPriorClick(Sender: TObject);
    procedure p_JYCL;
    procedure rbtnSWClick(Sender: TObject);
    procedure rbtnQTClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmJYCL: TfrmJYCL;

implementation

uses uCommon, dMain;

{$R *.DFM}

procedure TfrmJYCL.FormShow(Sender: TObject);
begin
  TabCondition.TabVisible := False;
  TabSelect.TabVisible := False;
  TabReason.TabVisible := False;
  TabResult.TabVisible := False;
  pagMain.ActivePageIndex := 0;

  btnPrior.Enabled := False;
  btnNext.Enabled := True;
end;

procedure TfrmJYCL.btnNextClick(Sender: TObject);
begin
  case pagMain.ActivePageIndex of
    0: //根据条件选出
      begin
        if (Trim(edtXM.Text) = '') and (Trim(edtSFZH.Text) = '') then
        begin
          gfShowError('请输入条件');
          Abort;
        end;

        pagMain.ActivePageIndex := 1;
        btnNext.Enabled := True;
        btnPrior.Enabled := True;

        qryTemp.SQL.Clear;
        qryTemp.SQL.Add('select ry.ryid_ff,ry.xm as "姓名",');
        qryTemp.SQL.Add('ry.sfzh as "身份证号",dw.dwmc as "单位名称",ryzt as "人员状态"');
        qryTemp.SQL.Add('from tff_ryxx ry ,tff_dwxx dw');
        qryTemp.SQL.Add('where dw.dwid_ff=ry.ssdwid and ry.ryzt=1');
        if Trim(edtXM.Text) <> '' then
          qryTemp.SQL.Add(' and ry.xm=''' + Trim(edtXM.Text) + '''');
        if Trim(edtSFZH.Text) <> '' then
          qryTemp.SQL.Add(' and ry.sfzh=''' + Trim(edtSFZH.Text) + '''');
        qryTemp.Open;
        qryTemp.Fields[0].Visible := False;
      end;
    1: //选出减员原因
      if qryTemp.Active and (qryTemp.RecordCount <> 0) then
      begin
        pagMain.ActivePageIndex := 2;
        btnNext.Enabled := True;
        btnPrior.Enabled := True;

        DateSWSJ.Date := StrToDate(DateToStr(Now)); //取当前时间
        lblSelect.Caption :=
          '姓名：' +
          qryTemp.FieldByName('姓名').AsString +
          '   身份证号:' +
          qryTemp.FieldByName('身份证号').AsString;
      end;
    2: //减员处理
      begin
        //判断减员处理
        if rbtnSW.Checked then
        begin

        end;

        if rbtnQT.Checked then
        begin

        end;

        p_JYCL();

        pagMain.ActivePageIndex := 3; //显示结果
        btnNext.Enabled := True;
        btnPrior.Enabled := True;

        btnNext.Caption := '减员完成';

      end;
    3:
      Close;
  end;
end;

procedure TfrmJYCL.btnPriorClick(Sender: TObject);
begin
  case pagMain.ActivePageIndex of
    3: //最后
      begin
        pagMain.ActivePageIndex := 2;
        btnNext.Caption := '下一步';
        btnPrior.Enabled := True;
        btnNext.Enabled := True;
      end;
    2: //最后
      begin
        pagMain.ActivePageIndex := 1;
        btnNext.Caption := '下一步';
        btnPrior.Enabled := True;
        btnNext.Enabled := True;
      end;
    1:
      begin
        pagMain.ActivePageIndex := 0;
        btnPrior.Enabled := False;
        btnNext.Enabled := True;
      end;
  end;
end;

procedure TfrmJYCL.p_JYCL;
//var
//  i: Integer;
begin
  if qryTemp.Active and (qryTemp.RecordCount <> 0) then
  begin
    if qryTemp.RecordCount > 1 then
    begin
      dfmMain.dbsMain.StartTransaction;

      //      for i := 0 to DBGrid1.SelectedRows.Count - 1 do
      //      begin
      //qryTemp.GotoBookmark(pointer(DBGrid1.SelectedRows.Items[i]));
      try
        with TQuery.Create(Self) do
        try
          DataBaseName := dfmMain.dbsMain.DatabaseName;
          if qryTemp.FieldByName('人员状态').AsInteger = 1 then
          begin
            SQL.Add('Update tff_ryxx');
            if rbtnSW.Checked then
              SQL.Add('Set ryzt=2,swsj=to_date('''
                + ''','''
                + ShortDateFormat
                + ''')')
            else
              SQL.Add('Set ryzt=2');
            SQL.Add('Where ryid_ff=''' +
              qryTemp.FieldByName('ryid_ff').AsString
              + '''');
          end;
          if qryTemp.FieldByName('人员状态').AsInteger = 3 then
          begin
            SQL.Add('Update tff_ryxx');
            if rbtnSW.Checked then
              SQL.Add('Set ryzt4,swsj=to_date('''
                + ''','''
                + ShortDateFormat
                + ''')')
            else
              SQL.Add('Set ryzt=4');
            SQL.Add('Where ryid_ff=''' +
              qryTemp.FieldByName('ryid_ff').AsString
              + '''');
          end;
          ExecSQL;
        finally
          Free;
        end;

        dfmMain.dbsMain.Commit;
        gfShowOK('操作成功');

      except
        dfmMain.dbsMain.Rollback;
      end;
      //      end;

            //执行死亡处理操作
      if rbtnSW.Checked then
      begin
        stpTemp.ParamByName('p_RYID').AsString :=
          qryTemp.FieldByName('ryid_ff').AsString;
        stpTemp.ParamByName('p_SWSJ').AsDateTime := DateSWSJ.Date;
        stpTemp.ExecProc;
        if stpTemp.ParamByName('p_ErrorCode').AsInteger <> 0 then
          raise Exception.Create('存储过程操作失败');
      end;

      mmoResult.Lines.Clear;
      mmoResult.Lines.Add('减员操作成功');

      //显示多发信息
      qryResult.Close;
      qryResult.SQL.Clear;
      with qryResult.SQL do
      begin
        Add('SELECT ny as "年月",ffje as "发放金额"');
        Add('FROM tff_swdfje');
        Add('WHERE ryid_ff =''' + qryTemp.FieldByName('ryid_ff').AsString +
          '''');
        Add('UNION');
        Add('SELECT to_number(null) as "年月",sum(nvl(ffje,0)) as "发放金额"');
        Add('FROM tff_swdfje');
        Add('WHERE ryid_ff =''' + qryTemp.FieldByName('ryid_ff').AsString +
          '''');
      end;
      qryResult.Open;
    end
    else
    begin
      dfmMain.dbsMain.StartTransaction;
      try
        with TQuery.Create(Self) do
        try
          DataBaseName := dfmMain.dbsMain.DatabaseName;
          if qryTemp.FieldByName('人员状态').AsInteger = 1 then
          begin
            SQL.Add('Update tff_ryxx');
            if rbtnSW.Checked then
              SQL.Add('Set ryzt=2,swsj=to_date('''
                + DateToStr(DateSWSJ.Date)
                + ''','''
                + ShortDateFormat
                + ''')')
            else
              SQL.Add('Set ryzt=2');
            SQL.Add('Where ryid_ff=''' +
              qryTemp.FieldByName('ryid_ff').AsString
              + '''');
          end;
          if qryTemp.FieldByName('人员状态').AsInteger = 3 then
          begin
            SQL.Add('Update tff_ryxx');
            if rbtnSW.Checked then
              SQL.Add('Set ryzt=4,swsj=to_date('''
                + DateToStr(DateSWSJ.Date)
                + ''','''
                + ShortDateFormat
                + ''')')
            else
              SQL.Add('Set ryzt=4');
            SQL.Add('Where ryid_ff=''' +
              qryTemp.FieldByName('ryid_ff').AsString
              + '''');
          end;
          ExecSQL;
        finally
          Free;
        end;

        //执行死亡处理操作
        if rbtnSW.Checked then
        begin
          stpTemp.ParamByName('p_RYID').AsString :=
            qryTemp.FieldByName('ryid_ff').AsString;
          stpTemp.ParamByName('p_SWSJ').AsDateTime := DateSWSJ.Date;
          stpTemp.ExecProc;
          if stpTemp.ParamByName('p_ErrorCode').AsInteger <> 0 then
            raise Exception.Create('存储过程操作失败');
        end;

        dfmMain.dbsMain.Commit;
        //      gfShowOK('减员操作成功');
        mmoResult.Lines.Clear;
        mmoResult.Lines.Add('减员操作成功');

      except
        dfmMain.dbsMain.Rollback;
        //        gfShowError('减员操作失败');

                //显示出错信息
        mmoResult.Lines.Clear;
        mmoResult.Lines.Add('减员操作失败');

        qryResult.Close;
      end;

      //显示多发信息
      qryResult.Close;
      qryResult.SQL.Clear;
      with qryResult.SQL do
      begin
        Add('SELECT ny as "年月",ffje as "发放金额"');
        Add('FROM tff_swdfje');
        Add('WHERE ryid_ff =''' + qryTemp.FieldByName('ryid_ff').AsString +
          '''');
        Add('UNION');
        Add('SELECT to_number(null) as "年月",sum(nvl(ffje,0)) as "发放金额"');
        Add('FROM tff_swdfje');
        Add('WHERE ryid_ff =''' + qryTemp.FieldByName('ryid_ff').AsString +
          '''');
      end;
      qryResult.Open;
    end;
    qryTemp.Close;
    qryTemp.Open;
    qryTemp.Fields[0].Visible := False;
  end;
end;

procedure TfrmJYCL.rbtnSWClick(Sender: TObject);
begin
  rbtnQT.Checked := not rbtnSW.Checked;
end;

procedure TfrmJYCL.rbtnQTClick(Sender: TObject);
begin
  rbtnSW.Checked := not rbtnQT.Checked;
end;

end.