unit U_WorkDeptFeeFKHXList;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls, ToolWin, cxStyles, cxCustomData,
  cxGraphics, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData, ADODB,
  cxGridCustomPopupMenu, cxGridPopupMenu, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, DBClient, cxCalendar, cxButtonEdit, cxSplitter,
  RM_Common, RM_Class, RM_e_Xls, RM_Dataset, RM_System, RM_GridReport,
  cxTextEdit, cxPC, cxCheckBox, Menus, BtnEdit;

type
  TfrmWorkDeptFeeFKHXList = class(TForm)
    ToolBar1: TToolBar;
    TBClose: TToolButton;
    cxGridPopupMenu1: TcxGridPopupMenu;
    ADOQueryCmd: TADOQuery;
    ADOQueryMain: TADOQuery;
    ADOQueryTemp: TADOQuery;
    DataSource1: TDataSource;
    Order_Sub: TClientDataSet;
    ToolButton2: TToolButton;
    Panel2: TPanel;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v2Column2: TcxGridDBColumn;
    v2Column3: TcxGridDBColumn;
    v2Column4: TcxGridDBColumn;
    v2Column1: TcxGridDBColumn;
    cxGridDBColumn1: TcxGridDBColumn;
    cxGridDBColumn2: TcxGridDBColumn;
    cxGridDBColumn3: TcxGridDBColumn;
    cxGridDBColumn4: TcxGridDBColumn;
    cxGridDBColumn5: TcxGridDBColumn;
    v1MSMoney: TcxGridDBColumn;
    v2Column5: TcxGridDBColumn;
    v2Column6: TcxGridDBColumn;
    v2Column7: TcxGridDBColumn;
    cxGridDBColumn6: TcxGridDBColumn;
    cxGridDBColumn7: TcxGridDBColumn;
    v1Column9: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    Panel3: TPanel;
    Label5: TLabel;
    FKDate: TDateTimePicker;
    Label7: TLabel;
    Label8: TLabel;
    FKMoney: TEdit;
    Label9: TLabel;
    FKType: TBtnEditA;
    Label10: TLabel;
    BankName: TBtnEditA;
    Label11: TLabel;
    BankNo: TBtnEditA;
    Label12: TLabel;
    Note: TRichEdit;
    v1Column1: TcxGridDBColumn;
    Worker: TComboBox;
    v1Column2: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FKTypeBtnClick(Sender: TObject);
    procedure BankNameBtnClick(Sender: TObject);
    procedure BankNoBtnClick(Sender: TObject);
    procedure WorkerChange(Sender: TObject);
    procedure v1Column1PropertiesEditValueChanged(Sender: TObject);
    procedure v2Column7PropertiesEditValueChanged(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
  private
    procedure InitGrid();
    procedure InitForm();
    function  SaveData():Boolean;
    { Private declarations }
  public
    FMainId:String;
    PState:Integer;
    { Public declarations }
  end;

var
  frmWorkDeptFeeFKHXList: TfrmWorkDeptFeeFKHXList;

implementation
uses
  U_DataLink,U_RTFun,U_ModuleNote,U_WorkerDeptFeeInPut,U_ZDYHelp;

{$R *.dfm}

procedure TfrmWorkDeptFeeFKHXList.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  Action:=caFree;
end;

procedure TfrmWorkDeptFeeFKHXList.TBCloseClick(Sender: TObject);
begin
  Close;
  WriteCxGrid('费用报销付款列表',Tv1,'费用管理');
end;

procedure TfrmWorkDeptFeeFKHXList.InitGrid();
begin
  try
    ADOQueryMain.DisableControls;
    with ADOQueryMain do
    begin
      Filtered:=False;
      Close;
      sql.Clear;
      sql.Add('select * from (');
      sql.Add(' select A.WFDate,A.Worker,A.WFType,A.BXNO,B.*,DHXMoney=isnull(B.Money,0)-isnull(B.BXMoney,0) ');
      sql.Add(' ,BCHXMoney=Cast(Null as decimal(18,2) )');
      SQL.Add(' from Worker_Fee_Main A ');
      sql.Add(' inner join Worker_Fee_Sub B on A.WFID=B.WFID');
      sql.Add(' where isnull(A.ChkStatus,'''')=''审核通过'' ');
      sql.Add(' and isnull(B.Money,0)<>isnull(B.BXMoney,0)');
      SQL.Add(' and isnull(A.Worker,'''')='''+Trim(Worker.Text)+'''');
      sql.Add(' and not exists(select * from Worker_Fee_HX C where C.WSID=B.WSID and C.FKID='''+Trim(FMainId)+''')');
      sql.Add(' Union all');
      sql.Add(' select A.WFDate,A.Worker,A.WFType,A.BXNO,B.* ,DHXMoney=isnull(B.Money,0)-isnull(B.BXMoney,0)');
      sql.Add(' ,BCHXMoney=(select isnull(HX.HXMoney,0) from Worker_Fee_HX HX where HX.WSID=B.WSID and HX.FKID='''+Trim(FMainId)+''')');
      SQL.Add(' from Worker_Fee_Main A ');
      sql.Add(' inner join Worker_Fee_Sub B on A.WFID=B.WFID');
      sql.Add(' where exists(select * from Worker_Fee_HX C where C.WSID=B.WSID and C.FKID='''+Trim(FMainId)+''')');
      sql.Add(')AA order by BCHXMoney desc');
      //ShowMessage(sql.Text);
      Open;
    end;
    SCreateCDS20(ADOQueryMain,Order_Sub);
    SInitCDSData20(ADOQueryMain,Order_Sub);
  finally
    ADOQueryMain.EnableControls;
  end;
end;



procedure TfrmWorkDeptFeeFKHXList.InitForm();
var
  fsj:string;
begin
  fsj:='select distinct(A.Worker) Name,Code='''' from Worker_Fee_Main A inner join Worker_Fee_Sub B on A.WFID=B.WFID'
      +' where isnull(A.ChkStatus,'''')=''审核通过'' and isnull(B.Money,0)<>isnull(B.BXMoney,0)';
  SInitComBoxBySql(ADOQueryTemp,Worker,False,fsj);

  with ADOQueryTemp do
  begin
    Close;
    sql.Clear;
    sql.Add('select * from Woker_Fee_FK where FKID='''+Trim(FMainId)+'''');
    Open;
  end;
  SCSHDataNew(ADOQueryTemp,Panel3,0);
  if PState=0 then
  begin
    Worker.Enabled:=True;
    FKDate.Date:=SGetServerDate(ADOQueryTemp);
  end else
  begin
    Worker.Enabled:=False;
    InitGrid();
    Order_Sub.DisableControls;
    with Order_Sub do
    begin
      First;
      while not Eof do
      begin
        if Trim(Order_Sub.fieldbyname('BCHXMoney').AsString)<>'' then
        begin
          Edit;
          FieldByName('SSel').Value:=True;
        end;
        Next;
      end;
    end;
    Order_Sub.EnableControls;
  end;
end;


procedure TfrmWorkDeptFeeFKHXList.FormShow(Sender: TObject);
begin
  ReadCxGrid('费用报销付款列表',Tv1,'费用管理');
  InitForm();
end;

function TfrmWorkDeptFeeFKHXList.SaveData():Boolean;
var
  maxno,maxSubNo,FHXID:String;
begin
  try
    Result:=False;
    ADOQueryCmd.Connection.BeginTrans;
    //保存主表
    if Trim(FMainId)='' then
    begin
      if GetLSNo(ADOQueryCmd,maxno,'FK','Woker_Fee_FK',4,1)=False then
      begin
        ADOQueryCmd.Connection.RollbackTrans;
        Application.MessageBox('取报销付款流水号失败!','提示',0);
        Exit;
      end;
    end else
    begin
      maxno:=Trim(FMainId);
    end;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      SQL.Add('select * from Woker_Fee_FK where FKID='''+Trim(maxno)+'''');
      Open;
    end;
    with ADOQueryCmd do
    begin
      if Trim(FMainId)='' then
      begin
        Append;
        FieldByName('Filler').Value:=Trim(DName);
      end
      else begin
        Edit;
        FieldByName('Editer').Value:=Trim(DName);
        FieldByName('EditTime').Value:=SGetServerDateTime(ADOQueryTemp);
      end;
      FieldByName('FKID').Value:=Trim(maxno);
      RTSetsavedata(ADOQueryCmd,'Woker_Fee_FK',Panel3,0);
      Post;
    end;
    //保存核销表
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update Worker_Fee_Sub Set BXMoney=');
      sql.Add('(select Sum(HXMoney) from Worker_Fee_HX A where A.WSID=Worker_Fee_Sub.WSID');
      sql.Add(' and A.FKID<>'''+Trim(maxno)+''')');
      sql.Add(' where WSID in');
      sql.Add('(select WSID from Worker_Fee_HX where FKID='''+Trim(maxno)+''')');
      ExecSQL;
    end;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('delete Worker_Fee_HX where FKID='''+Trim(maxno)+'''');
      ExecSQL;
    end;
    Order_Sub.DisableControls;
    with Order_Sub do
    begin
      First;
      while not Eof do
      begin
        if Order_Sub.FieldByName('SSel').AsBoolean=True then
        begin
          if Trim(Order_Sub.fieldbyname('BCHXMoney').AsString)<>'' then
          begin
            if GetLSNo(ADOQueryCmd,maxSubNo,'HX','Worker_Fee_HX',4,1)=False then
            begin
              Order_Sub.EnableControls;
              ADOQueryCmd.Connection.RollbackTrans;
              Application.MessageBox('取核销流水号失败!','提示',0);
              Exit;
            end;
            with ADOQueryCmd do
            begin
              Close;
              sql.Clear;
              SQL.Add('select * from Worker_Fee_HX where FKID='''+Trim(maxSubNo)+'''');
              Open;
            end;
            with ADOQueryCmd do
            begin
              Append;
              FieldByName('Filler').Value:=Trim(DName);
              FieldByName('FKID').Value:=Trim(maxno);
              FieldByName('HXID').Value:=Trim(maxSubNo);
              FieldByName('WSID').Value:=Trim(Order_Sub.fieldbyname('WSID').AsString);
              FieldByName('HXMoney').Value:=Order_Sub.fieldbyname('BCHXMoney').AsString;
              Post;
            end;
          end;
        end;
        Next;
      end;
    end;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('Update Worker_Fee_Sub Set BXMoney=');
      sql.Add('(select Sum(HXMoney) from Worker_Fee_HX A where A.WSID=Worker_Fee_Sub.WSID)');
      sql.Add(' where WSID in');
      sql.Add('(select WSID from Worker_Fee_HX where FKID='''+Trim(maxno)+''')');
      ExecSQL;
    end;
    with ADOQueryTemp do
    begin
      Close;
      sql.Clear;
      sql.Add('select * from Worker_Fee_Sub');
      sql.Add(' where WSID in');
      sql.Add('(select WSID from Worker_Fee_HX where FKID='''+Trim(maxno)+''')');
      sql.Add(' and isnull(Money,0)<isnull(BXMoney,0)');
      Open;
    end;
    if ADOQueryTemp.IsEmpty=False then
    begin
      Order_Sub.EnableControls;
      ADOQueryCmd.Connection.RollbackTrans;
      Application.MessageBox('报销金额大于待报销金额不能保存！','提示',0);
      Exit;
    end;
    ADOQueryCmd.Connection.CommitTrans;
    Order_Sub.EnableControls;
    Result:=True;
  except
    Result:=False;
    Order_Sub.EnableControls;
    ADOQueryCmd.Connection.RollbackTrans;
    Application.MessageBox('保存失败！','提示',0);
  end;
end;
procedure TfrmWorkDeptFeeFKHXList.FKTypeBtnClick(Sender: TObject);
begin
  try
    frmZDYHelp:=TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag:='FKType';
      flagname:='付款方式';
      if ShowModal=1 then
      begin
        Self.FKType.Text:=Trim(frmZDYHelp.ClientDataSet1.fieldbyname('ZdyName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmWorkDeptFeeFKHXList.BankNameBtnClick(Sender: TObject);
begin
  try
    frmZDYHelp:=TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag:='BankName';
      flagname:='银行名称';
      if ShowModal=1 then
      begin
        Self.BankName.Text:=Trim(frmZDYHelp.ClientDataSet1.fieldbyname('ZdyName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmWorkDeptFeeFKHXList.BankNoBtnClick(Sender: TObject);
begin
  try
    frmZDYHelp:=TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag:='BankNo';
      MainType:=Trim(Self.BankName.Text);
      flagname:='银行卡号';
      if ShowModal=1 then
      begin
        Self.BankNo.Text:=Trim(frmZDYHelp.ClientDataSet1.fieldbyname('ZdyName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmWorkDeptFeeFKHXList.WorkerChange(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmWorkDeptFeeFKHXList.v1Column1PropertiesEditValueChanged(
  Sender: TObject);
var
  mvalue:Boolean;
begin
  mvalue:=TcxCheckBox(Sender).EditingValue;
  with Order_Sub do
  begin
    Edit;
    FieldByName('SSel').Value:=mvalue;
    Post;
  end;
  if mvalue=True then
  begin
    with Order_Sub do
    begin
      Edit;
      FieldByName('BCHXMoney').Value:=Order_Sub.fieldbyname('DHXMoney').Value;
      Post;
    end;
  end else
  begin
    with Order_Sub do
    begin
      Edit;
      FieldByName('BCHXMoney').Value:=null;
      Post;
    end;
  end;
  FKMoney.Text:=Tv1.DataController.Summary.FooterSummaryTexts[4];
end;

procedure TfrmWorkDeptFeeFKHXList.v2Column7PropertiesEditValueChanged(
  Sender: TObject);
var
  mvalue:string;
begin
  mvalue:=TcxTextEdit(Sender).EditingText;
  if Trim(mvalue)<>'' then
  begin
    with Order_Sub do
    begin
      Edit;
      FieldByName('BCHXMoney').Value:=mvalue;
      Post;
    end;
  end else
  begin
    with Order_Sub do
    begin
      Edit;
      FieldByName('BCHXMoney').Value:=null;
      Post;
    end;
  end;
  FKMoney.Text:=Tv1.DataController.Summary.FooterSummaryTexts[4];
end;

procedure TfrmWorkDeptFeeFKHXList.ToolButton2Click(Sender: TObject);
begin
  ToolBar1.SetFocus;
  if Trim(Worker.Text)='' then
  begin
    Application.MessageBox('报销人不能为空!','提示',0);
    Exit;
  end;
  if Trim(FKMoney.Text)='' then
  begin
    Application.MessageBox('付款金额不能为空!','提示',0);
    Exit;
  end;
  if StrToFloat(FKMoney.Text)=0 then
  begin
    Application.MessageBox('付款金额不能为零!','提示',0);
    Exit;
  end;
  if Trim(FKType.Text)='' then
  begin
    Application.MessageBox('付款方式不能为空!','提示',0);
    Exit;
  end;
  if Application.MessageBox('确定要保存数据吗？','提示',32+4)<>IDYES then Exit;
  if SaveData() then
  begin
    ModalResult:=1;
  end;
end;

end.