sy_ndjhqlsb.pas

省级集邮品管理ERP

{*******************************************************}
{                                                       }
{                   上报年度请领计划}
{                                                       }
{            中软金马公司版权所有。2002.12前            }
{                                                       }
{            编制：中软金马邮资票品项目开发组           }
{                                                       }
{                                                       }
{*******************************************************}
(*
省级集邮品管理  ，地市集邮票管理

*)
unit SY_NDJHQLSB;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, ComCtrls, ImgList, ExtCtrls, StdCtrls, CheckLst, Grids,
  DBGrids, ActnList, Buttons, Spin, Mask, DBCtrls, RXCtrls, Menus,
  DBGridExport, ClipBrd;
type
  TDataState = (dsBrowse, dsInsert, dsEdit);

type
  TFrmY_NDJHQLSB = class(TForm)
    ILi_Edit: TImageList;
    Panel1: TPanel;
    BitBtn3: TBitBtn;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn6: TBitBtn;
    ActionList1: TActionList;
    Act_mod: TAction;
    Act_sav: TAction;
    Act_can: TAction;
    Act_rtn: TAction;
    Qry_super: TQuery;
    Act_snd: TAction;
    Panel5: TPanel;
    DWMC: TLabel;
    XttxTitle: TRxLabel;
    DBGrid_xq: TDBGrid;
    Qry_xq: TQuery;
    Qry_xqJHH: TStringField;
    Qry_xqSQDWDM: TStringField;
    Qry_xqND: TStringField;
    Qry_xqZT: TStringField;
    Qry_xqXQZTS: TFloatField;
    Qry_xqPPMC: TStringField;
    Qry_xqYDTS: TFloatField;
    Qry_xqLSTS: TFloatField;
    Qry_xqKFYPTS: TFloatField;
    Qry_xqZBR: TStringField;
    Qry_xqPZR: TStringField;
    Qry_xqZDRQ: TDateTimeField;
    Qry_xqBZ: TStringField;
    Qry_xqNCTS: TFloatField;
    Qry_xqTMC: TStringField;
    DS_xq: TDataSource;
    UpdateSQL2: TUpdateSQL;
    Label2: TLabel;
    SEd_nd: TSpinEdit;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Act_re: TAction;
    procedure Act_modExecute(Sender: TObject);
    procedure Act_canExecute(Sender: TObject);
    procedure Act_savExecute(Sender: TObject);
    procedure Qry_xqUpdateError(DataSet: TDataSet; E: EDatabaseError;
      UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);
    procedure Act_sndExecute(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure Act_rtnExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure DBGrid_ydKeyPress(Sender: TObject; var Key: Char);
    procedure SEd_ndChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Qry_xqYDTSChange(Sender: TField);
    procedure Act_reExecute(Sender: TObject);
  private
    { Private declarations }


    FDataEditState: TDataState;
    vlp_s_errmsg: string;
    function CheckExit(): boolean;
    procedure SetDataEdit(const state: TDataState);
    function ExistRecord(Tablenamestr, Conditionstr: string): boolean;
    procedure DoBDWXQTJ(const ReADD: boolean = false); //本单位需求统计
  public
    { Public declarations }
    function Execute(const DB: TDatabase): boolean;
  end;

var
  FrmY_NDJHQLSB: TFrmY_NDJHQLSB;

implementation

{$R *.DFM}
uses  datas, pub;

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

function TFrmY_NDJHQLSB.ExistRecord(Tablenamestr, Conditionstr: string): boolean;
begin
  with Qry_super do
  begin
    CLose;
    SQL.Clear;
    SQL.Add('Select count(*) from ' + Tablenamestr + ' Where ' + Conditionstr);
    Open;
    ExistRecord := (Fields[0].Asinteger > 0);
  end;
end;


procedure TFrmY_NDJHQLSB.SetDataEdit(const state: TDataState);
begin
  FDataEditState := state;
  DBGrid_xq.ReadOnly := (state = dsBrowse);
  SEd_nd.ReadOnly := (state <> dsBrowse);
  case state of
    dsEdit:
      Qry_xq.Edit;
    dsBrowse:
      begin
      end;
  end;

  Act_mod.Enabled := state = dsBrowse;
  Act_sav.Enabled := state <> dsBrowse;
  Act_can.Enabled := state <> dsBrowse;
  Act_snd.Enabled := state = dsBrowse;
end;


function TFrmY_NDJHQLSB.Execute(const DB: TDatabase): boolean;
begin
  Result := false;
  if not Assigned(DB) then
    exit;
  SEd_nd.OnChange := nil;
  SEd_nd.Text := inttostr(VG_Year);
  SEd_nd.OnChange := SEd_ndChange;
  DWMC.Caption := '使用单位：' + VG_UnitName;

  try
    Qry_super.Close;

    DoBDWXQTJ(); //本单位需求统计

    Qry_xq.Close;
    Qry_xq.DatabaseName := DB.DatabaseName;
    Qry_xq.ParamByName('ND').AsString := SEd_nd.Text;
    Qry_xq.Open;

    SetDataEdit(dsBrowse);
    Result := true;
  except
//    CHQMsgBox('不能打开 <集邮票预订需求> 表！');
    raise;
    exit;
  end;
end;

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

procedure TFrmY_NDJHQLSB.Act_canExecute(Sender: TObject);
var
  vl_s_year: string;
begin
  if (FDataEditState = dsBrowse) then
    exit;

  vl_s_year := SEd_nd.Text;
  data.DM.StartTransaction;
  try
    Qry_xq.CancelUpdates;
    data.DM.Commit;
  except
    data.DM.Rollback;
    raise exception.Create('数据访问错误！');
  end;
  Qry_xq.CommitUpdates;
  SetDataEdit(dsBrowse);
  SEd_nd.Text := vl_s_year;
end;

procedure TFrmY_NDJHQLSB.Act_savExecute(Sender: TObject);
var
  vl_s_year: string;
begin
  if (FDataEditState = dsBrowse) then
    exit;
  vl_s_year := SEd_nd.Text;
  data.DM.StartTransaction;
  try
    Qry_xq.ApplyUpdates;
    data.DM.Commit;
  except
    data.DM.Rollback;
    raise exception.Create(vlp_s_errmsg);
  end;

  Qry_xq.CommitUpdates;
  SetDataEdit(dsBrowse);
  SEd_nd.Text := vl_s_year;
end;

procedure TFrmY_NDJHQLSB.Qry_xqUpdateError(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_NDJHQLSB.Act_sndExecute(Sender: TObject);
var
  vl_s_jsdwdm: string;
begin
  if Qry_xq.IsEmpty then
    exit;

  if CHQMsgBox('确实要上报' + SEd_nd.Text + '年的需求信息吗？', 2) = IDNo then
    exit;

  with Qry_super 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 Qry_super do
    begin
      Close;
      SQL.Clear;
      SQL.Add('insert into TY_JJNXQB_CACO(JHH,PPMC,SQDWDM,ND,XQZTS,YDTS,LSTS,KFYPTS,NCTS,ZT,ZBR,PZR,ZDRQ,BZ,COMM_DEPT) ' +
        'select JHH,PPMC,SQDWDM,ND,XQZTS,YDTS,LSTS,KFYPTS,NCTS,''0'',ZBR,PZR,ZDRQ,BZ,''' + vl_s_jsdwdm + ''' from TY_JJNXQB ' +
        'where ND=''' + Qry_xq.FieldByName('ND').AsString + ''' and ZT=''2''');
      ExecSQL;
      Close;
      SQL.Clear;
      SQL.Add('update TY_JJNXQB set SDATE = (select sysdate from dual) ' +
        'where ND=''' + Qry_xq.FieldByName('ND').AsString + ''' and ZT=''2''');
      ExecSQL;
    end;
    data.DM.Commit;
    CHQMsgBox('需求信息发送成功！');
  except
    data.DM.RollBack;
    raise exception.Create('数据访问错误！');
  end;
  Qry_xq.Close;
  Qry_xq.Open;
end;

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

procedure TFrmY_NDJHQLSB.Act_rtnExecute(Sender: TObject);
begin
  Close;
end;

procedure TFrmY_NDJHQLSB.FormCreate(Sender: TObject);
begin
  GetAllColWidth(self);
end;

procedure TFrmY_NDJHQLSB.DBGrid_ydKeyPress(Sender: TObject;
  var Key: Char);
begin
  if Key = #13 then {回车键}
    if not (ActiveControl is TDBGrid) then {if not is a TDBGrid}
    begin
      Key := #0; {eat enter key}
      Perform(WM_NEXTDLGCTL, 0, 0); {move to next control}
    end
    else if (ActiveControl is TDBGrid) then {if it is a TDBGrid}
      with TDBGrid(ActiveControl) do
        if selectedindex < (fieldcount - 1) then {increment the field}
          selectedindex := selectedindex + 1
        else
          selectedindex := 0;
end;

procedure TFrmY_NDJHQLSB.DoBDWXQTJ(const ReADD: boolean = false); //本单位需求统计
var
  v_s_sql: string;
begin
  if ReAdd then
  begin
    v_s_sql := 'delete from TY_JJNXQB where ZT=''2'' and ND=''' + SEd_nd.Text + '''';
    AssignSQLstr(Qry_super, v_s_sql);
    Qry_super.ExecSQL;
  end;

  v_s_sql := 'insert into TY_JJNXQB(JHH, SQDWDM, ND, ZT, PPMC, XQZTS, YDTS, LSTS, NCTS, KFYPTS, ZBR, ZDRQ) ' +
    'select JHH, ''' + VG_UnitID + ''', ND, ''2'', PPMC, sum(XQZTS), sum(YDTS), sum(LSTS), sum(NCTS), sum(KFYPTS), ''' + VG_UserName + ''', sysdate ' +
    'from TY_JJNXQB where JHH not in (select JHH from TY_JJNXQB where ZT=''2'') ' +
    'group by JHH, ND, PPMC';
  AssignSQLstr(Qry_super, v_s_sql);
  Qry_super.ExecSQL;
end;

procedure TFrmY_NDJHQLSB.SEd_ndChange(Sender: TObject);
begin
  Qry_xq.Close;
  Qry_xq.ParamByName('ND').AsString := SEd_nd.Text;
  Qry_xq.Open;
  qry_xq.Last;
end;

procedure TFrmY_NDJHQLSB.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SaveAllColWidth(self);
end;

procedure TFrmY_NDJHQLSB.Qry_xqYDTSChange(Sender: TField);
begin
  with Qry_xq do
    FieldByName('XQZTS').Value := FieldByName('YDTS').Value + FieldByName('LSTS').Value + FieldByName('NCTS').Value + FieldByName('KFYPTS').Value;
end;

procedure TFrmY_NDJHQLSB.Act_reExecute(Sender: TObject);
var
  v_locate: string;
begin
  if CHQMsgBox('确定要重新生成年度请领计划吗？', 2) = mrNo then exit;
  v_locate := Qry_xq.FieldByName('JHH').AsString;
  DoBDWXQTJ(true);
  Qry_xq.Close;
  Qry_xq.Open;
  Qry_xq.Locate('JHH', v_locate, [loCaseInsensitive, loPartialKey]);
end;

end.