sy_fpndqljh.pas

来自「省级集邮品管理ERP」· PAS 代码 · 共 508 行

{*******************************************************}
{                                                       }
{                封片年度请领计划补录                   }
{                                                       }
{            中软金马公司版权所有。2002.12前            }
{                                                       }
{               编制：中软金马项目开发组                }
{                                                       }
{                                                       }
{*******************************************************}
(*
本模块在省级零枚票管理模块 需求管理部分的 封片年度请领计划 菜单调用。
*)

unit SY_FPNDQLJH;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, DBCtrls, EHGrids, DBGridEh, Buttons, Spin, RXCtrls,
  Db, DBTables, Grids, DBGrids, Menus, DBGridEhExport;

type
  TDataState = (dsBrowse, dsInsert, dsEdit);

type
  TFrmY_FPNDQLJH = class(TForm)
    Panel5: TPanel;
    XttxTitle: TRxLabel;
    DWMC: TLabel;
    Label8: TLabel;
    SE_year: TSpinEdit;
    Panel1: TPanel;
    BBt_edt: TBitBtn;
    BBt_sav: TBitBtn;
    BBt_can: TBitBtn;
    BBt_rtn: TBitBtn;
    DBGE_qljh: TDBGridEh;
    BBt_re: TBitBtn;
    BBt_snd: TBitBtn;
    Panel4: TPanel;
    RG_JB: TRadioGroup;
    DS_dw: TDataSource;
    Qry_dw: TQuery;
    Label3: TLabel;
    DBLCBo_dw: TDBLookupComboBox;
    Qry_qljh: TQuery;
    DS_qljh: TDataSource;
    USQL_qljh: TUpdateSQL;
    Label1: TLabel;
    CB_dl: TComboBox;
    Qry_qljhND: TStringField;
    Qry_qljhYZLDM: TStringField;
    Qry_qljhSQDWDM: TStringField;
    Qry_qljhXQZTS: TFloatField;
    Qry_qljhZT: TStringField;
    Qry_qljhZBR: TStringField;
    Qry_qljhZDRQ: TDateTimeField;
    Qry_qljhBZ: TStringField;
    Qry_qljhMC: TStringField;
    procedure Qry_dwAfterScroll(DataSet: TDataSet);
    procedure SE_yearChange(Sender: TObject);
    procedure RG_JBClick(Sender: TObject);
    procedure BBt_reClick(Sender: TObject);
    procedure BBt_edtClick(Sender: TObject);
    procedure Qry_qljhUpdateError(DataSet: TDataSet; E: EDatabaseError;
      UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
    procedure BBt_savClick(Sender: TObject);
    procedure BBt_canClick(Sender: TObject);
    procedure BBt_sndClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure DBGE_qljhDrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
    procedure DBGE_qljhGetFooterParams(Sender: TObject; DataCol,
      Row: Integer; Column: TColumnEh; AFont: TFont;
      var Background: TColor; var Alignment: TAlignment;
      State: TGridDrawState; var Text: string);
    procedure BBt_rtnClick(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure CB_dlChange(Sender: TObject);
    procedure Qry_qljhXQZTSSetText(Sender: TField; const Text: String);
    procedure Qry_qljhBeforeInsert(DataSet: TDataSet);
  private
    FDataEditState: TDataState;
    vlp_s_errmsg: string;
    procedure SetParams(APname, APval: string);
    function DoCXSCTJ(B_IsAPP: boolean = false): boolean;
    procedure SetDataEdit(const state: TDataState);
    function CheckExit(): boolean;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmY_FPNDQLJH: TFrmY_FPNDQLJH;

implementation
uses datas, pub;

{$R *.DFM}

procedure TFrmY_FPNDQLJH.SetParams(APname, APval: string);
begin
  Qry_qljh.Close;
  Qry_qljh.ParamByName(APname).AsString := APval;
  Qry_qljh.Open;
end;

procedure TFrmY_FPNDQLJH.Qry_dwAfterScroll(DataSet: TDataSet);
begin
  SetParams('SQDWDM', Qry_dw.FieldByName('DWDM').AsString);
end;

procedure TFrmY_FPNDQLJH.SE_yearChange(Sender: TObject);
begin
  SetParams('ND', SE_year.Text);
end;

procedure TFrmY_FPNDQLJH.RG_JBClick(Sender: TObject);
begin
  case RG_JB.ItemIndex of
    0:
      begin
        DBLCBo_dw.Enabled := false;
        DBLCBo_dw.Color := clBtnFace;
        SetParams('SQDWDM', VG_UnitID);
        BBt_re.Caption := '重新生成';
      end;
    1:
      begin
        DBLCBo_dw.Enabled := true;
        DBLCBo_dw.Color := clWindow;
        SetParams('SQDWDM', DBLCBo_dw.keyValue);
        BBt_re.Caption := '增加';
      end;
  end;
  BBt_snd.Enabled := (RG_JB.ItemIndex = 0);

end;

procedure TFrmY_FPNDQLJH.BBt_reClick(Sender: TObject);
var
  v_locate: string;
begin
  case RG_JB.ItemIndex of
    0:
      v_locate := VG_UserName;
    1:
      v_locate := qry_dw.FieldbyName('DWMC').AsString;
  end;

  v_locate := Qry_qljh.FieldByName('YZLDM').AsString;
  DoCXSCTJ;
end;

function TFrmY_FPNDQLJH.DoCXSCTJ(B_IsAPP: boolean = false): boolean;
const
  c_s_del = 'delete from TY_JJNXQB_FP where ND=''%s'' and SQDWDM=''%s''';
  c_s_ins1 = 'insert into TY_JJNXQB_FP(ND, YZLDM, SQDWDM, XQZTS, ZT, ZBR, ZDRQ) ';
  c_s_ins2 = 'select ''%s'', YZLDM, ''%s'', 0 XQZTS, 0, ''%s'', sysdate, MC from TG_YZPPLDMB ';
  c_s_ins3 = 'where DL=''%s'' ' +
    'and YZLDM not in (select YZLDM from TY_JJNXQB_FP where ND=''%s'' and SQDWDM=''%s'')';

  c_s_hz = 'select a.ND, a.YZLDM, ''%s'', sum(a.XQZTS) XQZTS, 0, ''%s'', sysdate, b.MC '
    + 'from TY_JJNXQB_FP a, TG_YZPPLDMB b where a.YZLDM=b.YZLDM and a.ND=''%s'' and b.DL=''%s'' and (a.YZLDM) not in (select YZLDM from TY_JJNXQB_FP where ND=''%s'' and SQDWDM=''%s'') group by a.ND, a.YZLDM, b.MC';

var
  v_YZLDM: string;
begin
  result := true;

  try
    data.qrytmp.Close;
    data.qrytmp.SQL.Clear;

    if (RG_JB.ItemIndex = 1) then //下级单位app（数量0）
    begin
      data.qrytmp.SQL.Add(format(c_s_ins2, [SE_year.Text, Qry_qljh.ParamByName('SQDWDM').AsString, VG_UserName]));
      data.qrytmp.SQL.Add(format(c_s_ins3, [CB_dl.Text, SE_year.Text, Qry_qljh.ParamByName('SQDWDM').AsString]));
    end
    else //本单位汇总app(数量0)
    begin
      data.qrytmp.SQL.Add(format(c_s_hz, [VG_UnitID, VG_UserName, SE_year.Text, CB_dl.Text, SE_year.Text, VG_UnitID]));
    end;

    data.qrytmp.Open;
    if not data.qrytmp.IsEmpty then
    begin
      data.qrytmp.First;
      v_YZLDM := data.qrytmp.FieldByName('YZLDM').AsString;
      Qry_qljh.BeforeInsert := nil;
      while not data.qrytmp.Eof do
      begin
        Qry_qljh.Append;
        Qry_qljh.FieldByName('ND').AsString := SE_year.Text;
        Qry_qljh.FieldByName('YZLDM').AsString := data.qrytmp.FieldByName('YZLDM').AsString;
        Qry_qljh.FieldByName('SQDWDM').AsString := Qry_qljh.ParamByName('SQDWDM').AsString;
        Qry_qljh.FieldByName('XQZTS').AsInteger := data.qrytmp.FieldByName('XQZTS').AsInteger;
        Qry_qljh.FieldByName('MC').AsString := data.qrytmp.FieldByName('MC').AsString;
        Qry_qljh.FieldByName('ZT').AsString := '0';
        Qry_qljh.FieldByName('ZBR').AsString := VG_UserName;
        Qry_qljh.FieldByName('ZDRQ').AsDateTime := data.qrytmp.FieldByName('sysdate').AsDateTime;
        data.qrytmp.Next;
      end;
      SetDataEdit(dsEdit);
      Qry_qljh.BeforeInsert := Qry_qljhBeforeInsert;
      Qry_qljh.Locate('YZLDM', v_YZLDM, [loCaseInsensitive, loPartialKey]);
    end;
  except
    result := false;
  end;
end;

procedure TFrmY_FPNDQLJH.BBt_edtClick(Sender: TObject);
begin
  if Qry_qljh.IsEmpty then
  begin
    CHQMsgBox('没有可修改的数据！');
    exit;
  end;
  SetDataEdit(dsEdit);
end;

procedure TFrmY_FPNDQLJH.SetDataEdit(const state: TDataState);
begin
  FDataEditState := state;
  DBGE_qljh.ReadOnly := (state = dsBrowse);
  SE_year.ReadOnly := (state <> dsBrowse);
  RG_JB.Enabled := (state = dsBrowse);
  DBLCBo_dw.Enabled := (RG_JB.ItemIndex = 1) and (state = dsBrowse);
  CB_dl.Enabled := (state = dsBrowse);
  DBGE_qljh.ReadOnly := (State = dsBrowse);

  case state of
    dsEdit:
      Qry_qljh.Edit;
    dsBrowse:
      begin
      end;
  end;

  BBt_re.Enabled := (state = dsBrowse);
  BBt_snd.Enabled := (RG_JB.ItemIndex = 0) and (state = dsBrowse);
  BBt_edt.Enabled := state = dsBrowse;
  BBt_sav.Enabled := state <> dsBrowse;
  BBt_can.Enabled := state <> dsBrowse;

end;

procedure TFrmY_FPNDQLJH.Qry_qljhUpdateError(DataSet: TDataSet;
  E: EDatabaseError; UpdateKind: TUpdateKind;
  var UpdateAction: TUpdateAction);
begin
  if pos('Key violation', e.Message) <> 0 then
    vlp_s_errmsg := '该单位的计划内需求已经存在！请重新选择申请单位！'
  else if pos('Field value required', e.Message) <> 0 then
    vlp_s_errmsg := '申请单位、制表人不能为空！请重新确定再保存！'
  else
    vlp_s_errmsg := E.Message; // '数据访问错误！';

end;

procedure TFrmY_FPNDQLJH.BBt_savClick(Sender: TObject);
var
  v_locate: string;
begin
  if (FDataEditState = dsBrowse) then
    exit;
  v_locate := Qry_qljh.FieldByName('YZLDM').AsString;

  Qry_qljh.First;
  while not Qry_qljh.Eof do
  begin
    if Qry_qljh.FieldByName('XQZTS').IsNull then
    begin
      Qry_qljh.Delete;
      continue;
    end;
    if Qry_qljh.FieldByName('XQZTS').AsInteger = 0 then
    begin
      Qry_qljh.Delete;
      continue;
    end;
    Qry_qljh.Next;
  end;

  data.DM.StartTransaction;
  try
    Qry_qljh.ApplyUpdates;
    data.DM.Commit;
  except
    data.DM.Rollback;
    raise exception.Create(vlp_s_errmsg);
  end;

  Qry_qljh.CommitUpdates;
  SetDataEdit(dsBrowse);
  DBGE_qljh.SumList.Active := false;
  DBGE_qljh.SumList.Active := true;
  if v_locate <> '' then
    Qry_qljh.Locate('YZLDM', v_locate, [loCaseInsensitive, loPartialKey]);

end;

procedure TFrmY_FPNDQLJH.BBt_canClick(Sender: TObject);
begin
  if (FDataEditState = dsBrowse) then
    exit;

  data.DM.StartTransaction;
  try
    Qry_qljh.CancelUpdates;
    data.DM.Commit;
  except
    data.DM.Rollback;
    raise exception.Create('数据访问错误！');
  end;
  Qry_qljh.CommitUpdates;
  SetDataEdit(dsBrowse);
  DBGE_qljh.SumList.Active := false;
  DBGE_qljh.SumList.Active := true;
end;

procedure TFrmY_FPNDQLJH.BBt_sndClick(Sender: TObject);
var
  vl_s_jsdwdm: string;
begin
  if Qry_qljh.IsEmpty then
    exit;

  if CHQMsgBox('确实要上报<' + SE_year.Text + '年度>请领计划信息吗？', 2) = IDNo then
    exit;

  with data.qrytmp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select DWDM from TGS_GXDWSJB where ZJSJ=''1''');
    Open;
    if not IsEmpty then
      vl_s_jsdwdm := Fields[0].AsString
    else
    begin
      CHQMsgBox('请先设置上级单位再发送！');
      exit;
    end;
  end;

  data.DM.StartTransaction;
  try
    with data.qrytmp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('insert into TY_JJNXQB_FP_CACO(ND, YZLDM, SQDWDM, XQZTS, ZT, ZBR, PZR, ZDRQ, BZ, SDATE, COMM_DEPT) ' +
        'select ND, YZLDM, SQDWDM, XQZTS, ''0'', ZBR, PZR, ZDRQ, BZ, sysdate,''' + vl_s_jsdwdm + ''' from TY_JJNXQB_FP ' +
        'where XQZTS>0 and ND=''' + SE_year.Text + ''' and SQDWDM=''' + VG_UnitID + '''');
      ExecSQL;
      Close;
      SQL.Clear;
      SQL.Add('update TY_JJNXQB_FP set SDATE = sysdate ' +
        'where XQZTS>0 and ND=''' + SE_year.Text + ''' and SQDWDM=''' + VG_UnitID + '''');
      ExecSQL;
    end;
    data.DM.Commit;
    CHQMsgBox('请领计划信息发送成功！');
  except
    data.DM.RollBack;
    raise exception.Create('数据访问错误！');
  end;
  Qry_qljh.Close;
  Qry_qljh.Open;
end;

procedure TFrmY_FPNDQLJH.FormShow(Sender: TObject);
  procedure DoSetYZDL;
  begin
    AssignSQLstr(data.qrytmp, 'select distinct substr(YZLDM,1,1),DL from TG_YZPPLDMB where substr(YZLDM,1,1) not in (1,8,9)');
    data.qrytmp.Open;
    while not data.qrytmp.eof do
    begin
      CB_dl.Items.Add(data.qrytmp.FieldByName('DL').asstring);
      data.qrytmp.Next;
    end;
  end;
begin
  DWMC.Caption := '使用单位：' + VG_UnitName;
  Qry_dw.AfterScroll := nil;
  Qry_dw.Close;
  Qry_dw.Open;
  DBLCBo_dw.KeyValue := Qry_dw.FieldByName('DWDM').Value;
  Qry_qljh.ParamByName('SQDWDM').AsString := DBLCBo_dw.KeyValue;
  Qry_dw.AfterScroll := Qry_dwAfterScroll;
  CB_dl.OnChange := nil;
  DoSetYZDL;
  CB_dl.ItemIndex := 0;
  Qry_qljh.ParamByName('DL').AsString := CB_dl.Text;
  CB_dl.OnChange := CB_dlChange;
  SE_year.value := VG_Year;
  SetDataEdit(dsBrowse);
end;


procedure TFrmY_FPNDQLJH.DBGE_qljhDrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumnEh;
  State: TGridDrawState);
begin
  if (gdSelected in State) and (TDBGridEh(Sender).Focused = True) then
    TDBGridEh(Sender).Canvas.Font.Color := clWhite
  else
    TDBGridEh(Sender).Canvas.Font.Color := clBlack;

  TDBGridEh(Sender).DefaultDrawColumnCell(Rect, DataCol, Column, State);

end;

procedure TFrmY_FPNDQLJH.DBGE_qljhGetFooterParams(Sender: TObject; DataCol,
  Row: Integer; Column: TColumnEh; AFont: TFont; var Background: TColor;
  var Alignment: TAlignment; State: TGridDrawState; var Text: string);
begin
  try
    if (UpperCase(Column.FieldName) = 'XQZTS') then
      if Text = '' then
        Text := '0'
      else
        Text := FormatFloat('#,##', StrToFloat(Text))
  except
  end;

  if (UpperCase(Column.FieldName) = 'MC') then
    Text := '合 计:  ' + Text;
end;

procedure TFrmY_FPNDQLJH.BBt_rtnClick(Sender: TObject);
begin
  Close;
end;

procedure TFrmY_FPNDQLJH.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  CanCLose := CheckExit;
end;

function TFrmY_FPNDQLJH.CheckExit(): boolean;
begin
  result := true;
  if (FDataEditState <> dsBrowse) then
    case CHQMsgBox('数据已经被更改，是否保存所做修改？', 3) of
      IDYes:
        begin
          BBt_savClick(nil);
          Qry_qljh.Close;
          close;
        end;
      IDNo:
        begin
          BBt_canClick(nil);
          Qry_qljh.Close;
          close;
        end;
      IDCancel:
        result := false;
    end
  else
  begin
    Qry_qljh.Close;
    close;
  end;
end;

procedure TFrmY_FPNDQLJH.CB_dlChange(Sender: TObject);
begin
  SetParams('DL', CB_dl.Text);
end;

procedure TFrmY_FPNDQLJH.Qry_qljhXQZTSSetText(Sender: TField;
  const Text: String);
var
  TextValue: Integer;
begin
  try
    TextValue := StrToInt(Text);
    if TextValue < 0 then
    begin
      CHQMsgBox('非法数值！');
      abort;
    end;

    Sender.AsInteger := TextValue;
  except
    if not (TextValue< 0) then
      chqmsgbox('非法数值');
    abort;
  end;
end;

procedure TFrmY_FPNDQLJH.Qry_qljhBeforeInsert(DataSet: TDataSet);
begin
  Abort;
end;

end.