D10SZKaiXiYa/A09财务通用管理/U_BizCateSel.pas

unit U_BizCateSel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ToolWin, cxStyles, cxCustomData, cxGraphics, cxFilter,
  cxData, cxDataStorage, cxEdit, DB, cxDBData, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, ADODB, DBClient, ExtCtrls, cxTextEdit,
  cxLookAndFeels, cxLookAndFeelPainters, cxNavigator, dxBarBuiltInMenu,
  dxDateRanges, dxScrollbarAnnotations, cxContainer, dxSkinsCore,
  dxSkinsDefaultPainters, dxSkinWXI, StdCtrls;

const
  CBizCateType = 'FinBizCate';   //KH_ZDY中业务大类的Type标识
  CBizItemType = 'FinBizItem';   //KH_ZDY中明细项的Type标识

type
  TfrmBizCateSel = class(TForm)
    ToolBar1: TToolBar;
    TBAdd: TToolButton;
    TBDel: TToolButton;
    TBEdit: TToolButton;
    TBOK: TToolButton;
    TBClose: TToolButton;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    Tv1BizCate: TcxGridDBColumn;
    Tv1BizItem: TcxGridDBColumn;
    Tv1CateNo: TcxGridDBColumn;
    Tv1ItemNo: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    DS_1: TDataSource;
    CDS_1: TClientDataSet;
    ADOQueryTmp: TADOQuery;
    ADOQueryCmd: TADOQuery;
    procedure FormShow(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure TBAddClick(Sender: TObject);
    procedure TBDelClick(Sender: TObject);
    procedure TBEditClick(Sender: TObject);
    procedure TBOKClick(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure Tv1CellDblClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
    procedure Tv1BizCatePropertiesEditValueChanged(Sender: TObject);
    procedure Tv1BizItemPropertiesEditValueChanged(Sender: TObject);
  private
    procedure InitGrid();
    procedure SetEditMode(AEdit: Boolean);
    function FindCateNoByName(const ACateName: string; var ACateNo: string): Boolean;
    function ItemNameExists(const ACateNo, AItemName, AExcludeItemNo: string): Boolean;
    { Private declarations }
  public
    SelCateName, SelItemName: string;
    { Public declarations }
  end;

var
  frmBizCateSel: TfrmBizCateSel;

implementation

uses
  U_DataLink, U_RTFun;

{$R *.dfm}

{一行=一条明细项(BizItem)+其所属业务大类(BizCate)；
 KH_ZDY存储：大类 Type=FinBizCate，明细 Type=FinBizItem、MainType=大类ZDYNo；
 未点修改前表格锁定，点修改后单元格改动即自动保存。}

procedure TfrmBizCateSel.InitGrid();
begin
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select C.ZDYName as BizCate, I.ZDYName as BizItem');
    SQL.Add(',C.ZDYNo as CateNo, I.ZDYNo as ItemNo');
    SQL.Add('from KH_ZDY C join KH_ZDY I');
    SQL.Add(' on I.Type=''' + CBizItemType + ''' and I.MainType=C.ZDYNo and isnull(I.valid,''Y'')=''Y''');
    SQL.Add('where C.Type=''' + CBizCateType + ''' and isnull(C.valid,''Y'')=''Y''');
    SQL.Add('order by C.ZDYNo, I.ZDYNo');
    Open;
  end;
  SCreateCDS(ADOQueryTmp, CDS_1);
  SInitCDSData(ADOQueryTmp, CDS_1);
end;

procedure TfrmBizCateSel.SetEditMode(AEdit: Boolean);
begin
  Tv1.OptionsData.Editing := AEdit;
  Tv1.OptionsSelection.CellSelect := AEdit;
  if AEdit then
    TBEdit.Caption := #32467#26463#20462#25913   //结束修改
  else
    TBEdit.Caption := #20462#25913;              //修改
end;

function TfrmBizCateSel.FindCateNoByName(const ACateName: string;
  var ACateNo: string): Boolean;
begin
  Result := False;
  ACateNo := '';
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select ZDYNo from KH_ZDY where Type=''' + CBizCateType + '''');
    SQL.Add(' and isnull(valid,''Y'')=''Y'' and ZDYName=' + QuotedStr(Trim(ACateName)));
    Open;
    if not IsEmpty then
    begin
      Result := True;
      ACateNo := Trim(FieldByName('ZDYNo').AsString);
    end;
    Close;
  end;
end;

function TfrmBizCateSel.ItemNameExists(const ACateNo, AItemName,
  AExcludeItemNo: string): Boolean;
begin
  Result := False;
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select ZDYNo from KH_ZDY where Type=''' + CBizItemType + '''');
    SQL.Add(' and MainType=' + QuotedStr(Trim(ACateNo)));
    SQL.Add(' and ZDYName=' + QuotedStr(Trim(AItemName)));
    Open;
    while not Eof do
    begin
      if Trim(FieldByName('ZDYNo').AsString) <> Trim(AExcludeItemNo) then
      begin
        Result := True;
        Break;
      end;
      Next;
    end;
    Close;
  end;
end;

procedure TfrmBizCateSel.FormShow(Sender: TObject);
begin
  InitGrid();
  SetEditMode(False);
end;

procedure TfrmBizCateSel.FormDestroy(Sender: TObject);
begin
  frmBizCateSel := nil;
end;

procedure TfrmBizCateSel.TBAddClick(Sender: TObject);
begin
  SetEditMode(True);
  CDS_1.Append;
  CDS_1.Post;
  Tv1.Controller.FocusRecord(CDS_1.RecNo - 1, True);
end;

procedure TfrmBizCateSel.TBDelClick(Sender: TObject);
var
  mCateNo, mItemNo: string;
  mLast: Boolean;
begin
  if CDS_1.IsEmpty then
    Exit;
  mCateNo := Trim(CDS_1.FieldByName('CateNo').AsString);
  mItemNo := Trim(CDS_1.FieldByName('ItemNo').AsString);
  if mItemNo = '' then
  begin
    CDS_1.Delete;   //未落库的新行
    Exit;
  end;

  mLast := False;
  with ADOQueryTmp do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select count(*) as Cnt from KH_ZDY where Type=''' + CBizItemType + '''');
    SQL.Add(' and MainType=' + QuotedStr(mCateNo) + ' and isnull(valid,''Y'')=''Y''');
    Open;
    if FieldByName('Cnt').AsInteger <= 1 then
      mLast := True;
    Close;
  end;

  if mLast then
  begin
    if Application.MessageBox('该明细项是其业务大类下最后一条，删除后将同时删除该业务大类，确定删除吗？',
      '提示信息', 1) = 2 then
      Exit;
  end
  else
  begin
    if Application.MessageBox('确定要删除该明细项吗？', '提示信息', 1) = 2 then
      Exit;
  end;

  ADOQueryCmd.Connection.BeginTrans;
  try
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      SQL.Add('delete KH_ZDY where Type=''' + CBizItemType + ''' and ZDYNo=' + QuotedStr(mItemNo));
      ExecSQL;
      if mLast then
      begin
        Close;
        SQL.Clear;
        SQL.Add('delete KH_ZDY where Type=''' + CBizCateType + ''' and ZDYNo=' + QuotedStr(mCateNo));
        ExecSQL;
      end;
    end;
    ADOQueryCmd.Connection.CommitTrans;
  except
    ADOQueryCmd.Connection.RollbackTrans;
    Application.MessageBox('删除失败！', '提示信息', 0);
    Exit;
  end;
  CDS_1.Delete;
end;

procedure TfrmBizCateSel.TBEditClick(Sender: TObject);
begin
  SetEditMode(not Tv1.OptionsData.Editing);
end;

procedure TfrmBizCateSel.Tv1BizCatePropertiesEditValueChanged(Sender: TObject);
var
  mvalue, mCateNo, mItemNo, mFoundNo, mNewCateNo, maxno: string;
  mFound: Boolean;
begin
  mvalue := Trim(TcxTextEdit(Sender).EditingText);
  if mvalue = '' then
    Exit;
  if (not CDS_1.Active) or CDS_1.IsEmpty then
    Exit;
  mCateNo := Trim(CDS_1.FieldByName('CateNo').AsString);
  mItemNo := Trim(CDS_1.FieldByName('ItemNo').AsString);

  CDS_1.Edit;
  CDS_1.FieldByName('BizCate').Value := mvalue;
  CDS_1.Post;

  if mCateNo = '' then
    Exit;   //新行：只记录大类名称，待明细项录入时统一创建大类+明细

  if mItemNo = '' then
    Exit;

  //已有行：只调整当前这条明细的所属大类。
  //输入已有大类名=移动过去；全新名称=新建该名称的大类再移动过去（不做全大类重命名）
  mFound := FindCateNoByName(mvalue, mFoundNo);
  if mFound and (mFoundNo = mCateNo) then
    Exit;   //大类未变化

  ADOQueryCmd.Connection.BeginTrans;
  try
    if not mFound then
    begin
      if GetLSNo(ADOQueryTmp, maxno, 'SY', 'KH_ZDY', 3, 1) = False then
      begin
        ADOQueryCmd.Connection.RollbackTrans;
        Exit;
      end;
      with ADOQueryCmd do
      begin
        Close;
        SQL.Clear;
        SQL.Add('insert into KH_ZDY(ZDYNo,ZDYName,Type,valid) values(');
        SQL.Add(QuotedStr(Trim(maxno)) + ',' + QuotedStr(mvalue));
        SQL.Add(',''' + CBizCateType + ''',''Y'')');
        ExecSQL;
      end;
      mNewCateNo := Trim(maxno);
    end
    else
      mNewCateNo := mFoundNo;

    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      SQL.Add('update KH_ZDY set MainType=' + QuotedStr(mNewCateNo));
      SQL.Add(' where ZDYNo=' + QuotedStr(mItemNo) + ' and Type=''' + CBizItemType + '''');
      ExecSQL;
    end;

    //原大类被移空时一并删除，避免留下界面上看不到的空大类
    with ADOQueryTmp do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select count(*) as Cnt from KH_ZDY where Type=''' + CBizItemType + '''');
      SQL.Add(' and MainType=' + QuotedStr(mCateNo) + ' and isnull(valid,''Y'')=''Y''');
      Open;
      if FieldByName('Cnt').AsInteger = 0 then
      begin
        with ADOQueryCmd do
        begin
          Close;
          SQL.Clear;
          SQL.Add('delete KH_ZDY where Type=''' + CBizCateType + ''' and ZDYNo=' + QuotedStr(mCateNo));
          ExecSQL;
        end;
      end;
      Close;
    end;

    ADOQueryCmd.Connection.CommitTrans;
  except
    ADOQueryCmd.Connection.RollbackTrans;
    Application.MessageBox('保存业务大类调整失败！', '提示信息', 0);
    Exit;
  end;

  CDS_1.Edit;
  CDS_1.FieldByName('CateNo').Value := mNewCateNo;
  CDS_1.Post;
end;

procedure TfrmBizCateSel.Tv1BizItemPropertiesEditValueChanged(Sender: TObject);
var
  mvalue, mCateNo, mItemNo, mCateName, mFoundNo, mNewCateNo, mNewItemNo, maxno: string;
begin
  mvalue := Trim(TcxTextEdit(Sender).EditingText);
  if mvalue = '' then
    Exit;
  if (not CDS_1.Active) or CDS_1.IsEmpty then
    Exit;
  mCateNo := Trim(CDS_1.FieldByName('CateNo').AsString);
  mItemNo := Trim(CDS_1.FieldByName('ItemNo').AsString);
  mCateName := Trim(CDS_1.FieldByName('BizCate').AsString);

  if mItemNo = '' then
  begin
    //新行录入明细项：大类存在则挂靠，不存在则先建大类，再建明细项
    if mCateName = '' then
    begin
      Application.MessageBox('请先输入业务大类！', '提示信息', 0);
      Exit;
    end;

    ADOQueryCmd.Connection.BeginTrans;
    try
      mNewCateNo := mCateNo;
      if (mNewCateNo = '') and (not FindCateNoByName(mCateName, mFoundNo)) then
      begin
        if GetLSNo(ADOQueryTmp, maxno, 'SY', 'KH_ZDY', 3, 1) = False then
        begin
          ADOQueryCmd.Connection.RollbackTrans;
          Exit;
        end;
        with ADOQueryCmd do
        begin
          Close;
          SQL.Clear;
          SQL.Add('insert into KH_ZDY(ZDYNo,ZDYName,Type,valid) values(');
          SQL.Add(QuotedStr(Trim(maxno)) + ',' + QuotedStr(mCateName));
          SQL.Add(',''' + CBizCateType + ''',''Y'')');
          ExecSQL;
        end;
        mNewCateNo := Trim(maxno);
      end
      else if mNewCateNo = '' then
        mNewCateNo := mFoundNo;

      if ItemNameExists(mNewCateNo, mvalue, '') then
      begin
        ADOQueryCmd.Connection.RollbackTrans;
        Application.MessageBox('该业务大类下已存在同名明细项！', '提示信息', 0);
        Exit;
      end;

      if GetLSNo(ADOQueryTmp, maxno, 'SY', 'KH_ZDY', 3, 1) = False then
      begin
        ADOQueryCmd.Connection.RollbackTrans;
        Exit;
      end;
      mNewItemNo := Trim(maxno);
      with ADOQueryCmd do
      begin
        Close;
        SQL.Clear;
        SQL.Add('insert into KH_ZDY(ZDYNo,ZDYName,Type,MainType,valid) values(');
        SQL.Add(QuotedStr(mNewItemNo) + ',' + QuotedStr(mvalue));
        SQL.Add(',''' + CBizItemType + ''',' + QuotedStr(mNewCateNo) + ',''Y'')');
        ExecSQL;
      end;
      ADOQueryCmd.Connection.CommitTrans;

      CDS_1.Edit;
      CDS_1.FieldByName('BizItem').Value := mvalue;
      CDS_1.FieldByName('CateNo').Value := mNewCateNo;
      CDS_1.FieldByName('ItemNo').Value := mNewItemNo;
      CDS_1.Post;
    except
      ADOQueryCmd.Connection.RollbackTrans;
      Application.MessageBox('保存明细项失败！', '提示信息', 0);
    end;
  end
  else
  begin
    //重命名明细项
    if ItemNameExists(mCateNo, mvalue, mItemNo) then
    begin
      Application.MessageBox('该业务大类下已存在同名明细项！', '提示信息', 0);
      Exit;
    end;
    CDS_1.Edit;
    CDS_1.FieldByName('BizItem').Value := mvalue;
    CDS_1.Post;
    with ADOQueryCmd do
    begin
      Close;
      SQL.Clear;
      SQL.Add('update KH_ZDY set ZDYName=' + QuotedStr(mvalue));
      SQL.Add(' where ZDYNo=' + QuotedStr(mItemNo) + ' and Type=''' + CBizItemType + '''');
      ExecSQL;
    end;
  end;
end;

procedure TfrmBizCateSel.TBOKClick(Sender: TObject);
begin
  if CDS_1.IsEmpty or (Trim(CDS_1.FieldByName('BizCate').AsString) = '')
    or (Trim(CDS_1.FieldByName('BizItem').AsString) = '') then
  begin
    Application.MessageBox('请先选择业务大类和明细项！', '提示信息', 0);
    Exit;
  end;
  SelCateName := Trim(CDS_1.FieldByName('BizCate').AsString);
  SelItemName := Trim(CDS_1.FieldByName('BizItem').AsString);
  ModalResult := 1;
end;

procedure TfrmBizCateSel.Tv1CellDblClick(Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin
  if Tv1.OptionsData.Editing = False then
    TBOKClick(Sender);
end;

procedure TfrmBizCateSel.TBCloseClick(Sender: TObject);
begin
  ModalResult := 2;
  Close;
end;

end.