unit u_ch_invoice_expitems;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  BusinessSkinForm, bsSkinCtrls, ExtCtrls, dxExEdtr, dxCntner, dxTL,Dialogs,
  dxDBCtrl, dxDBGrid, dxDBTLCl, dxGrClms, DB, ADODB, Grids, Wwdbigrd,
  Wwdbgrid, StdCtrls, wwdblook;

type
  Tfrm_ch_invoice_expitems = class(TForm)
    Panel1: TPanel;
    bsSkinButton1: TbsSkinButton;
    bsSkinButton2: TbsSkinButton;
    bsSkinButton4: TbsSkinButton;
    bsSkinButton3: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    bsBusinessSkinForm1: TbsBusinessSkinForm;
    dsrt_ch_invoice_expitem: TDataSource;
    t_ch_invoice_expitem: TADOQuery;
    bsSkinGroupBox1: TbsSkinGroupBox;
    wwDBGrid2: TwwDBGrid;
    bsSkinButton8: TbsSkinButton;
    t_ch_invoice_expitem_sum: TADOQuery;
    Label1: TbsSkinLabel;
    bsSkinButton6: TbsSkinButton;
    bsSkinButton7: TbsSkinButton;
    bsSkinLabel1: TbsSkinLabel;
    bsSkinButton9: TbsSkinButton;
    t_crm_client_Invoice: TADOQuery;
    t_crm_client_Invoice1: TDataSource;
    wwDBLookupCombo2: TwwDBLookupCombo;
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure bsSkinButton5Click(Sender: TObject);
    procedure bsSkinButton3Click(Sender: TObject);
    procedure bsSkinButton2Click(Sender: TObject);
    procedure bsSkinButton4Click(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure bsSkinButton6Click(Sender: TObject);
    procedure bsSkinButton7Click(Sender: TObject);
    procedure bsSkinButton8Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure t_ch_invoice_expitemBeforeEdit(DataSet: TDataSet);
    procedure t_ch_invoice_expitemAfterInsert(DataSet: TDataSet);
    procedure t_ch_invoice_expitemAfterDelete(DataSet: TDataSet);
    procedure t_ch_invoice_expitemAfterEdit(DataSet: TDataSet);
    procedure t_ch_invoice_expitemAfterPost(DataSet: TDataSet);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bsSkinButton9Click(Sender: TObject);
  private
    function GetIsClose():Boolean;
    procedure  ComputeAmount;
    procedure invtitlecharge(Sender:TField);

    { Private declarations }

  public
    { Public declarations }
  end;


var
  frm_ch_invoice_expitems: Tfrm_ch_invoice_expitems;
  invduino,invduicust,invcurr:string;


implementation

uses u_data_share, my_sys_function, u_main;

{$R *.dfm}

procedure Tfrm_ch_invoice_expitems.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  CanClose:=frm_close_query(t_ch_invoice_expitem);
  if (invduicust<>'') then
    CanClose:=GetIsClose();
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton5Click(Sender: TObject);
begin
  close;
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton3Click(Sender: TObject);
begin
  table_cancel(t_ch_invoice_expitem);
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton2Click(Sender: TObject);
begin
  table_post(t_ch_invoice_expitem);
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton4Click(Sender: TObject);
var
  i:integer;
  str_list:Tstringlist;
begin

  if wwDBGrid2.SelectedList.Count>1 then
  begin
   try
      str_list:=Tstringlist.create;
      for i:=0 to wwDBGrid2.SelectedList.Count-1 do
      begin
        t_ch_invoice_expitem.GotoBookmark(pointer(wwDBGrid2.SelectedList[i]));
        str_list.Add(t_ch_invoice_expitem.fieldbyname('ITEMID').asstring);
      end;
      for i:=0 to str_list.Count-1 do
      begin
        if t_ch_invoice_expitem.locate('ITEMID',str_list[i],[]) then
          t_ch_invoice_expitem.Delete;
      end;
    finally
      str_list.free;
    end;
  end
  else
  begin
     table_delete(t_ch_invoice_expitem);
  end;


end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton1Click(Sender: TObject);
begin
  t_ch_invoice_expitem.Insert;
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton6Click(Sender: TObject);
var
  aQuery:TADOQuery;
  mblstr:string;
begin

  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
       close;SQL.Text:='SELECT distinct dbo.v_op_bscard_all.主提单号 '
       +' FROM dbo.t_ch_dui_bill INNER JOIN '
       +' dbo.v_op_bscard_all ON dbo.t_ch_dui_bill.编号 = dbo.v_op_bscard_all.编号  '
       +' WHERE dbo.t_ch_dui_bill.对帐编号='''+invduino+'''';
       open;
       if not IsEmpty then begin
         First;
         while not eof do begin

           mblstr:=mblstr+' '+aQuery.FieldByName('主提单号').AsString;
           next;
         end;

       end;
    end;
    t_ch_invoice_expitem.Edit;
    t_ch_invoice_expitem.FieldByName('备注').AsString:=mblstr;
    t_ch_invoice_expitem.Post;

  finally
    FreeAndNil(aQuery);
  end;

end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton7Click(Sender: TObject);
var
  invtitle:string;
begin
//  if sender.DataSet.State in [dsedit,dsinsert] then begin
    invtitle:=t_ch_invoice_expitem.FieldByName('发票抬头').AsString;
    while not t_ch_invoice_expitem.Eof do begin
      t_ch_invoice_expitem.Edit;
      t_ch_invoice_expitem.FieldByName('发票抬头').AsString:=invtitle;
      t_ch_invoice_expitem.Post;
      t_ch_invoice_expitem.Next;
    end;
//  end;

end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton8Click(Sender: TObject);
var
  aQuery:TADOQuery;
  custfullname:string;
begin
{
   if t_ch_invoice_expitem.RecordCount<>0 then begin
      if MessageDlg('单开明细中已有数据,确实要清空,重新生成单开数据吗？',mtWarning,[mbyes,mbno],0)<>mryes then
            Exit;
   end;
 }

  custfullname:=invduicust;
  if frm_data_share.t_crm_client_all.Locate('客户简称',invduicust,[]) then
     custfullname:=frm_data_share.t_crm_client_all.fieldbyname('客户全称').AsString;


  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
       close;SQL.Text:='SELECT dbo.t_ch_dui_bill.对帐编号, dbo.v_op_bscard_all.主提单号, dbo.v_op_bscard_all.编号 '
       +',allduifee.RMB开票金额,allduifee.USD开票金额 FROM dbo.t_ch_dui_bill INNER JOIN '
       +' dbo.v_op_bscard_all ON dbo.t_ch_dui_bill.编号 = dbo.v_op_bscard_all.编号 INNER JOIN '
       +' (select 编号,对帐编号,SUM(case when 币别=''RMB'' THEN 金额-开票金额 else 0 end) RMB开票金额 '
       +' ,SUM(case when 币别=''USD'' THEN 金额-开票金额 else 0 end) USD开票金额 '
       +' from v_dui_fee where 类型=''收'' GROUP BY 编号,对帐编号)  allduifee '
       +' ON dbo.t_ch_dui_bill.对帐编号 = allduifee.对帐编号 '
       +' AND dbo.t_ch_dui_bill.编号 = allduifee.编号 WHERE dbo.t_ch_dui_bill.对帐编号='''+invduino+'''';
       open;
       if not IsEmpty then begin
         First;
         while not eof do begin
           if aQuery.FieldByName('RMB开票金额').AsFloat<>0 then begin
            if (t_ch_invoice_expitem.Locate('主提单号;对帐币别',VarArrayOf([aQuery.FieldByName('主提单号').AsString,'RMB']),[])) then begin
           t_ch_invoice_expitem.Edit;
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=t_ch_invoice_expitem.FieldByName('金额').AsFloat+aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat:=t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat+aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.Post;

            end else begin
           t_ch_invoice_expitem.Insert;
           t_ch_invoice_expitem.FieldByName('对帐编号').AsString:=aQuery.FieldByName('对帐编号').AsString;
           t_ch_invoice_expitem.FieldByName('编号').AsString:=aQuery.FieldByName('编号').AsString;
           t_ch_invoice_expitem.FieldByName('主提单号').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.FieldByName('发票抬头').AsString:=custfullname;
           t_ch_invoice_expitem.FieldByName('币别').AsString:='RMB';
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('对帐币别').AsString:='RMB';
           t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat:=aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('费用名称').AsString:='代理运杂费';
           t_ch_invoice_expitem.FieldByName('备注').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.Post;
           end;
           end;
{
           if aQuery.FieldByName('USD开票金额').AsFloat<>0 then begin
           t_ch_invoice_expitem.Insert;
           t_ch_invoice_expitem.FieldByName('对帐编号').AsString:=aQuery.FieldByName('对帐编号').AsString;
           t_ch_invoice_expitem.FieldByName('编号').AsString:=aQuery.FieldByName('编号').AsString;
           t_ch_invoice_expitem.FieldByName('主提单号').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.FieldByName('发票抬头').AsString:=custfullname;
           t_ch_invoice_expitem.FieldByName('币别').AsString:='USD';
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=aQuery.FieldByName('USD开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('费用名称').AsString:='代理海运费';
           t_ch_invoice_expitem.FieldByName('备注').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.Post;
           end;
 }
           next;
         end;

       end;
    end;
  finally
    FreeAndNil(aQuery);
  end;
end;

procedure Tfrm_ch_invoice_expitems.FormShow(Sender: TObject);
var
  aQuery:TADOQuery;
begin
  t_ch_invoice_expitem.close;
  t_ch_invoice_expitem.SQL.Clear;
  t_ch_invoice_expitem.SQL.Add('select * from t_ch_invoice_expitem where 对帐编号='''+invduino+''' ');
  if invcurr<>'' then
    t_ch_invoice_expitem.SQL.Add('  and 币别='''+invcurr+''' ');
  t_ch_invoice_expitem.SQL.Add(' order by ITEMID ');

//  t_ch_invoice_expitem.Parameters.ParamByName('对帐编号').Value:=invduino;
  t_ch_invoice_expitem.Open;

  t_crm_client_Invoice.Close;
  t_crm_client_Invoice.Parameters.ParamByName('客户简称').Value:=invduicust;
  t_crm_client_Invoice.Open;

   ComputeAmount;
   
  if invduicust='' then begin
    bsSkinButton8.Enabled:=false;
    bsSkinButton1.Enabled:=false;
    bsSkinButton2.Enabled:=false;
    bsSkinButton3.Enabled:=false;
    bsSkinButton4.Enabled:=false;
  end;
// loadwwgrid(wwDBGrid2,'发票单开明细');
end;

procedure  Tfrm_ch_invoice_expitems.ComputeAmount;
var
  aQuery:TADOQuery;
  rmbamt,usdamt,cermb,ceusd:Double;
  bok:Tbookmark;
begin
  rmbamt:=0;
  usdamt:=0;
  Label1.Caption:='';
  aQuery:=CreateAdoQuery;
  with aQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select a.对帐编号, b.USDAmount,a.RMBAmount from (select t.对帐编号,sum(t.RMB) AS RMBAmount from  '
           +' (select 对帐编号, case when 对帐币别=''RMB'' THEN 对帐金额  else 0 end as RMB FROM t_ch_invoice_expitem '
           +' where 对帐编号='''+invduino+''') t '
           +' group by t.对帐编号) a inner join (select s.对帐编号,sum(s.USD) AS USDAmount from (select 对帐编号,'
           +' case when 对帐币别=''USD'' THEN 对帐金额  else 0 end as USD FROM t_ch_invoice_expitem where 对帐编号='''
           +invduino+''') s group by s.对帐编号) b  on a.对帐编号=b.对帐编号   ') ;
    Open;
    Label1.Caption:='对帐编号:'+fieldbyname('对帐编号').Asstring+'   RMB合计:'+fieldbyname('RMBAmount').Asstring+'元'
                    +'   USD合计:'+ fieldbyname('USDAmount').Asstring+'美元';

    Close;
    SQL.Clear;
    SQL.Add('select SUM(CASE WHEN 对帐币别=''RMB'' THEN 对帐金额 ELSE 0 END) RMB,SUM(CASE WHEN 对帐币别=''USD'' THEN 对帐金额 ELSE 0 END) USD from  '
           +'  t_ch_invoice_expitem '
           +' where 对帐编号='''+invduino+''' ') ;
    Open;
    rmbamt:=fieldbyname('RMB').AsFloat;
    usdamt:=fieldbyname('USD').AsFloat;

  end;
  aQuery.Free;






  t_ch_invoice_expitem_sum.Close;
  t_ch_invoice_expitem_sum.Parameters.ParamByName('对帐编号').Value:=invduino;
  t_ch_invoice_expitem_sum.Open;
  cermb:=t_ch_invoice_expitem_sum.FieldByName('RMB开票金额').AsFloat-rmbamt;
  ceusd:=t_ch_invoice_expitem_sum.FieldByName('USD开票金额').AsFloat-usdamt;

  bsSkinLabel1.Caption:='开票合计   RMB合计:'+floattostr(rmbamt)+'元'
                    +'   USD合计:'+ floattostr(usdamt)+'美元        差额：RMB合计:'+floattostr(cermb)+'元'
                    +'   USD合计:'+ floattostr(ceusd)+'美元';

end;

procedure Tfrm_ch_invoice_expitems.t_ch_invoice_expitemBeforeEdit(
  DataSet: TDataSet);
begin
  if (invduicust='') then Abort;
end;

function Tfrm_ch_invoice_expitems.GetIsClose: Boolean;
var
  rmbamt,usdamt,cermb,ceusd:Double;
  bok:Tbookmark;
begin
   rmbamt:=0;
  usdamt:=0;
  result:=True;
  bok:=t_ch_invoice_expitem.GetBookmark;
  try
    t_ch_invoice_expitem.DisableControls;

  t_ch_invoice_expitem.First;
  while not t_ch_invoice_expitem.Eof do begin
    if t_ch_invoice_expitem.FieldByName('对帐币别').AsString='RMB' THEN begin
      rmbamt:=rmbamt+t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat;
    end;
    if t_ch_invoice_expitem.FieldByName('对帐币别').AsString='USD' THEN begin
      usdamt:=usdamt+t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat;
    end;
    t_ch_invoice_expitem.Next;
  end;
  finally
    t_ch_invoice_expitem.EnableControls;
    t_ch_invoice_expitem.GotoBookmark(bok);
  end;



  t_ch_invoice_expitem_sum.Close;
  t_ch_invoice_expitem_sum.Parameters.ParamByName('对帐编号').Value:=invduino;
  t_ch_invoice_expitem_sum.Open;
  {
  cermb:=t_ch_invoice_expitem_sum.FieldByName('RMB开票金额').AsFloat-rmbamt;
  ceusd:=t_ch_invoice_expitem_sum.FieldByName('USD开票金额').AsFloat-usdamt;

  bsSkinLabel1.Caption:='开票合计   RMB合计:'+floattostr(rmbamt)+'元'
                    +'   USD合计:'+ floattostr(usdamt)+'美元        差额：RMB合计:'+floattostr(cermb)+'元'
                    +'   USD合计:'+ floattostr(ceusd)+'美元';
                    }

  if ((rmbamt-0.1)>t_ch_invoice_expitem_sum.FieldByName('RMB开票金额').AsFloat) then begin
    ShowMessage('RMB开票金额为:'+floattostr(rmbamt)+',RMB开票金额不能大于'+t_ch_invoice_expitem_sum.FieldByName('RMB开票金额').AsString);
    result:=false;
  end;
  if ((usdamt-0.1)>t_ch_invoice_expitem_sum.FieldByName('USD开票金额').AsFloat) then begin
    ShowMessage('USD开票金额为:'+floattostr(usdamt)+',USD开票金额不能大于'+t_ch_invoice_expitem_sum.FieldByName('USD开票金额').AsString);
    result:=false;
  end;
end;

procedure Tfrm_ch_invoice_expitems.t_ch_invoice_expitemAfterInsert(
  DataSet: TDataSet);
begin
  t_ch_invoice_expitem.FieldByName('对帐编号').AsString:=invduino;
  ComputeAmount;
end;

procedure Tfrm_ch_invoice_expitems.t_ch_invoice_expitemAfterDelete(
  DataSet: TDataSet);
begin
ComputeAmount;
end;

procedure Tfrm_ch_invoice_expitems.t_ch_invoice_expitemAfterEdit(
  DataSet: TDataSet);
begin
ComputeAmount;
end;

procedure Tfrm_ch_invoice_expitems.t_ch_invoice_expitemAfterPost(
  DataSet: TDataSet);
begin
ComputeAmount;
end;

procedure Tfrm_ch_invoice_expitems.invtitlecharge(Sender: TField);

begin

end;

procedure Tfrm_ch_invoice_expitems.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  savewwgrid(wwDBGrid2,'发票单开明细');
end;

procedure Tfrm_ch_invoice_expitems.bsSkinButton9Click(Sender: TObject);
var
  aQuery:TADOQuery;
  custfullname:string;
begin
  {
   if t_ch_invoice_expitem.RecordCount<>0 then begin
      if MessageDlg('单开明细中已有数据,确实要清空,重新生成单开数据吗？',mtWarning,[mbyes,mbno],0)<>mryes then
            Exit;
   end;
   }

  custfullname:=invduicust;
 if frm_data_share.t_crm_client_all.Locate('客户简称',invduicust,[]) then
     custfullname:=frm_data_share.t_crm_client_all.fieldbyname('客户全称').AsString;
 // if (not t_crm_client_Invoice.IsEmpty) then custfullname:=t_crm_client_Invoice.fieldbyname('发票抬头').AsString;


  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
       close;SQL.Text:='SELECT dbo.t_ch_dui_bill.对帐编号, dbo.v_op_bscard_all.主提单号, dbo.v_op_bscard_all.编号 '
       +',allduifee.RMB开票金额,allduifee.USD开票金额 FROM dbo.t_ch_dui_bill INNER JOIN '
       +' dbo.v_op_bscard_all ON dbo.t_ch_dui_bill.编号 = dbo.v_op_bscard_all.编号 INNER JOIN '
       +' (select 编号,对帐编号,SUM(case when 币别=''RMB'' THEN 金额-开票金额 else 0 end) RMB开票金额 '
       +' ,SUM(case when 币别=''USD'' THEN 金额-开票金额 else 0 end) USD开票金额 '
       +' from v_dui_fee where 类型=''收'' GROUP BY 编号,对帐编号)  allduifee '
       +' ON dbo.t_ch_dui_bill.对帐编号 = allduifee.对帐编号 '
       +' AND dbo.t_ch_dui_bill.编号 = allduifee.编号 WHERE dbo.t_ch_dui_bill.对帐编号='''+invduino+'''';
       open;
       if not IsEmpty then begin
         First;
         while not eof do begin
{
           if aQuery.FieldByName('RMB开票金额').AsFloat<>0 then begin
           t_ch_invoice_expitem.Insert;
           t_ch_invoice_expitem.FieldByName('对帐编号').AsString:=aQuery.FieldByName('对帐编号').AsString;
           t_ch_invoice_expitem.FieldByName('编号').AsString:=aQuery.FieldByName('编号').AsString;
           t_ch_invoice_expitem.FieldByName('主提单号').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.FieldByName('发票抬头').AsString:=custfullname;
           t_ch_invoice_expitem.FieldByName('币别').AsString:='RMB';
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('对帐币别').AsString:='RMB';
           t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat:=aQuery.FieldByName('RMB开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('费用名称').AsString:='代理运杂费';
           t_ch_invoice_expitem.FieldByName('备注').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.Post;
           end;
           }

           if aQuery.FieldByName('USD开票金额').AsFloat<>0 then begin

            if (t_ch_invoice_expitem.Locate('主提单号;对帐币别',VarArrayOf([aQuery.FieldByName('主提单号').AsString,'USD']),[])) then begin
           t_ch_invoice_expitem.Edit;
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=t_ch_invoice_expitem.FieldByName('金额').AsFloat+aQuery.FieldByName('USD开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat:=t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat+aQuery.FieldByName('USD开票金额').AsFloat;
           t_ch_invoice_expitem.Post;

            end else begin

           t_ch_invoice_expitem.Insert;
           t_ch_invoice_expitem.FieldByName('对帐编号').AsString:=aQuery.FieldByName('对帐编号').AsString;
           t_ch_invoice_expitem.FieldByName('编号').AsString:=aQuery.FieldByName('编号').AsString;
           t_ch_invoice_expitem.FieldByName('主提单号').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.FieldByName('发票抬头').AsString:=custfullname;
           t_ch_invoice_expitem.FieldByName('币别').AsString:='USD';
           t_ch_invoice_expitem.FieldByName('金额').AsFloat:=aQuery.FieldByName('USD开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('对帐币别').AsString:='USD';
           t_ch_invoice_expitem.FieldByName('对帐金额').AsFloat:=aQuery.FieldByName('USD开票金额').AsFloat;
           t_ch_invoice_expitem.FieldByName('费用名称').AsString:='代理海运费';
           t_ch_invoice_expitem.FieldByName('备注').AsString:=aQuery.FieldByName('主提单号').AsString;
           t_ch_invoice_expitem.Post;
           END;
           end;

           next;
         end;

       end;
    end;
  finally
    FreeAndNil(aQuery);
  end;
end;

end.