462 lines
13 KiB
ObjectPascal
462 lines
13 KiB
ObjectPascal
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.
|