unit U_SetMessage;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, bsSkinCtrls, ExtCtrls, dxExEdtr, dxTL, dxDBCtrl, dxDBGrid,
  dxCntner, DB, ADODB, StdCtrls, Mask, DBCtrls, bsdbctrls, DBCtrlsEh,
  Grids, DBGridEh, Menus, Psock, NMsmtp, IdMessage, IdBaseComponent,
  IdComponent, IdTCPConnection, IdTCPClient, IdMessageClient, IdSMTP,
  IdExplicitTLSClientServerBase, IdSMTPBase,IdFTP,inifiles;

type
  TfrmMessageSet = class(TForm)
    Panel2: TPanel;
    bsSkinButton7: TbsSkinButton;
    bsSkinButton6: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    bsSkinButton1: TbsSkinButton;
    bsSkinButton11: TbsSkinButton;
    bsSkinButton2: TbsSkinButton;
    bsSkinButton4: TbsSkinButton;
    bsSkinButton3: TbsSkinButton;
    bsSkinButton5: TbsSkinButton;
    bsSkinButton38: TbsSkinButton;
    bsSkinPanel1: TbsSkinPanel;
    t_sys_awoke: TADOQuery;
    t_sys_awoke1: TDataSource;
    Panel1: TPanel;
    Notebook1: TNotebook;
    dxDBGrid2: TdxDBGrid;
    dxDBGrid2Column1: TdxDBGridColumn;
    dxDBGrid2Column2: TdxDBGridColumn;
    dxDBGrid2Column3: TdxDBGridColumn;
    dxDBGrid2Column5: TdxDBGridColumn;
    dxDBGrid2Column6: TdxDBGridColumn;
    dxDBGrid2Column7: TdxDBGridColumn;
    dxDBGrid2Column8: TdxDBGridColumn;
    bsSkinPanel2: TbsSkinPanel;
    bsSkinGroupBox1: TbsSkinGroupBox;
    bsSkinGroupBox4: TbsSkinGroupBox;
    DBGridEh1: TDBGridEh;
    Label1: TLabel;
    DBMemo1: TDBMemo;
    Label2: TLabel;
    DBEdit1: TDBEdit;
    Label8: TLabel;
    DBMemo2: TDBMemo;
    dxDBGrid2Column9: TdxDBGridColumn;
    dxDBGrid2Column10: TdxDBGridColumn;
    dxDBGrid2Column11: TdxDBGridColumn;
    dxDBGrid2Column12: TdxDBGridColumn;
    t_sys_awoke_emp: TADOQuery;
    t_sys_awoke_emp1: TDataSource;
    bsSkinButton8: TbsSkinButton;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    N4: TMenuItem;
    N5: TMenuItem;
    Panel3: TPanel;
    bsSkinGroupBox2: TbsSkinGroupBox;
    Label3: TLabel;
    Label4: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    DBEdit3: TDBEdit;
    DBComboBox2: TDBComboBox;
    DBComboBox4: TDBComboBox;
    DBDateTimeEditEh1: TDBDateTimeEditEh;
    DBDateTimeEditEh2: TDBDateTimeEditEh;
    DBEdit4: TDBEdit;
    DBEdit5: TDBEdit;
    bsSkinGroupBox3: TbsSkinGroupBox;
    bsSkinDBRadioGroup2: TbsSkinDBRadioGroup;
    bsSkinGroupBox5: TbsSkinGroupBox;
    Label5: TLabel;
    Label6: TLabel;
    Label9: TLabel;
    DBNumberEditEh1: TDBNumberEditEh;
    DBNumberEditEh2: TDBNumberEditEh;
    DBNumberEditEh3: TDBNumberEditEh;
    Label7: TLabel;
    DBEdit2: TDBEdit;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton12: TbsSkinButton;
    N6: TMenuItem;
    N7: TMenuItem;
    DBComboBoxEh1: TDBComboBoxEh;
    PopupMenu2: TPopupMenu;
    N8: TMenuItem;
    N9: TMenuItem;
    N10: TMenuItem;
    N11: TMenuItem;
    N12: TMenuItem;
    N13: TMenuItem;
    N14: TMenuItem;
    dxDBGrid2Column13: TdxDBGridColumn;
    SMTP1: TIdSMTP;
    IdMsg: TIdMessage;
    dxDBGrid2Column14: TdxDBGridColumn;
    Label15: TLabel;
    DBNumberEditEh4: TDBNumberEditEh;
    bsSkinButton13: TbsSkinButton;
    Label16: TLabel;
    DBComboBox1: TDBComboBox;
    procedure t_sys_awokeAfterInsert(DataSet: TDataSet);
    procedure bsSkinButton11Click(Sender: TObject);
    procedure bsSkinButton38Click(Sender: TObject);
    procedure bsSkinButton3Click(Sender: TObject);
    procedure bsSkinButton2Click(Sender: TObject);
    procedure bsSkinButton7Click(Sender: TObject);
    procedure bsSkinButton6Click(Sender: TObject);
    procedure t_sys_awoke_empAfterInsert(DataSet: TDataSet);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure bsSkinButton5Click(Sender: TObject);
    procedure bsSkinButton8Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure N5Click(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure dxDBGrid2DblClick(Sender: TObject);
    procedure Panel2Resize(Sender: TObject);
    procedure bsSkinButton4Click(Sender: TObject);
    procedure t_sys_awokeBeforePost(DataSet: TDataSet);
    procedure bsSkinButton12Click(Sender: TObject);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure N7Click(Sender: TObject);
    procedure t_sys_awokeBeforeInsert(DataSet: TDataSet);
    procedure t_sys_awokeBeforeDelete(DataSet: TDataSet);
    procedure NMSMTP1Connect(Sender: TObject);
    procedure N8Click(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure N10Click(Sender: TObject);
    procedure N12Click(Sender: TObject);
    procedure N14Click(Sender: TObject);
    procedure bsSkinButton13Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  function GetStrChar(str:String):string;




  function CreateIgengzongxml(SOI:string):TStringlist;

  end;

var
  frmMessageSet: TfrmMessageSet;

implementation

uses u_data_share, u_main, my_sys_function, u_message_query,
  u_message_guide, u_messageset, u_addemp;

{$R *.dfm}

procedure TfrmMessageSet.t_sys_awokeAfterInsert(DataSet: TDataSet);
begin
  DataSet.FieldByName('录入人').AsString:=employee;
  DataSet.FieldByName('录入日期').AsDateTime:=date;
  DataSet.FieldByName('提醒类型').AsString:='不提醒';
  DataSet.FieldByName('提醒方式').AsString:='邮件提醒';
  DataSet.FieldByName('提醒状态').AsString:='准备执行';
end;

procedure TfrmMessageSet.bsSkinButton11Click(Sender: TObject);
var
  answer:TModalResult;
begin

  if bsSkinButton11.Caption='编辑修改' then
  begin
    bsSkinButton11.Caption:='返回列表';
//    t_sys_awoke.Edit;
    Notebook1.PageIndex:=1;
    exit;
  end;
  if bsSkinButton11.Caption='返回列表' then
  begin
    if (t_sys_awoke.State=dsinsert)or(t_sys_awoke.State=dsedit) then
    BEGIN
      answer:=messagedlg( '数据已经修改，是否保存？',mtWarning,mbYesNoCancel,0);
      if answer=mrYes then
      begin
        t_sys_awoke.post;
      end;
      if answer=mrNo then
      begin
        t_sys_awoke.cancel;
      end;
      if answer=mrCancel then
      begin
        exit;
      end; 
    END;
    bsSkinButton11.Caption:='编辑修改';
    Notebook1.PageIndex:=0;
    exit;
  end;


end;

procedure TfrmMessageSet.bsSkinButton38Click(Sender: TObject);
begin
  t_sys_awoke.Refresh;
end;

procedure TfrmMessageSet.bsSkinButton3Click(Sender: TObject);
begin
  if t_sys_awoke.State in [dsedit,dsinsert] then t_sys_awoke.Cancel;
end;

procedure TfrmMessageSet.bsSkinButton2Click(Sender: TObject);
begin
  if t_sys_awoke.State in [dsedit,dsinsert] then t_sys_awoke.Post;
end;

procedure TfrmMessageSet.bsSkinButton7Click(Sender: TObject);
begin
  if not t_sys_awoke.Bof then t_sys_awoke.Prior;
end;

procedure TfrmMessageSet.bsSkinButton6Click(Sender: TObject);
begin
  if not t_sys_awoke.Eof  then t_sys_awoke.Next;
end;

procedure TfrmMessageSet.t_sys_awoke_empAfterInsert(DataSet: TDataSet);
begin
  DataSet.FieldByName('是否提醒').AsBoolean:=false;
end;

procedure TfrmMessageSet.FormShow(Sender: TObject);
begin

  if not if_open('244') then begin
    N3.Visible:=false;
    N5.Visible:=false;
    N12.Visible:=false;
  end;

  if not if_open('245') then begin
    N5.Visible:=false;
    N12.Visible:=false;
  end;


  if t_sys_awoke.Active then t_sys_awoke.Close;
  t_sys_awoke.sql.clear;
  t_sys_awoke.sql.add('Select * from t_sys_awoke where 1=1 ');
  t_sys_awoke.sql.add(' and '+open_data('9005','录入人','no','no','no','no'));
  t_sys_awoke.Open;

  if not t_sys_awoke_emp.Active then t_sys_awoke_emp.Open;

end;

procedure TfrmMessageSet.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  frmMessageSet.Hide;
  frmMessageSet.ManualFloat(frmMessageSet.BoundsRect );
  frm_main.freeTabs('frmMessageSet');
  action:=cafree;
  frmMessageSet:=nil;

end;

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

procedure TfrmMessageSet.bsSkinButton8Click(Sender: TObject);
begin
//  if not (t_sys_awoke.State in [dsedit,dsinsert]) then exit;
  frm_message_guide:=Tfrm_message_guide.Create(self);
  try
    frm_message_guide.showmodal;
  finally
    FreeAndNil(frm_message_guide);
  end;
end;

procedure TfrmMessageSet.N1Click(Sender: TObject);
begin
  if t_sys_awoke.State in [dsinsert,dsedit] then t_sys_awoke.Post;
  if not t_sys_awoke_emp.Locate('人员',employee,[]) then begin
    t_sys_awoke_emp.Insert;
    t_sys_awoke_emp.FieldByName('awoke_id').AsInteger:=t_sys_awoke.fieldByName('awoke_id').AsInteger;
    t_sys_awoke_emp.FieldByName('数据范围').AsString:='个人数据';
    t_sys_awoke_emp.FieldByName('人员').AsString:=employee;
    t_sys_awoke_emp['是否提醒']:=1;
    t_sys_awoke_emp.Post;
  end;
end;

procedure TfrmMessageSet.N3Click(Sender: TObject);
var
  aQuery:TADOQuery;
begin
  if t_sys_awoke.State in [dsinsert,dsedit] then t_sys_awoke.Post;
  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
      close;SQL.Clear;
      SQL.Add('Select 姓名 from t_sys_employee where 部门名称='''+department+'''');
      Open;
      first;
      while not eof do begin
        if not t_sys_awoke_emp.Locate('人员',FieldByName('姓名').AsString,[]) then begin
          t_sys_awoke_emp.Insert;
          t_sys_awoke_emp.FieldByName('awoke_id').AsInteger:=t_sys_awoke.fieldByName('awoke_id').AsInteger;
          t_sys_awoke_emp.FieldByName('数据范围').AsString:='个人数据';
          t_sys_awoke_emp.FieldByName('人员').AsString:=FieldByName('姓名').AsString;
          t_sys_awoke_emp['是否提醒']:=1;
          t_sys_awoke_emp.Post;
        end;
        Next;
      end;
    end;
  finally
    FreeAndNil(aQuery);
  end;

end;

procedure TfrmMessageSet.N5Click(Sender: TObject);
var
  aQuery:TADOQuery;
begin
  if t_sys_awoke.State in [dsinsert,dsedit] then t_sys_awoke.Post;
  aQuery:=CreateAdoQuery;
  try
    with aQuery do begin
      close;SQL.Clear;
      SQL.Add('Select 姓名 from t_sys_employee ');
      Open;
      first;
      while not eof do begin
        if not t_sys_awoke_emp.Locate('人员',FieldByName('姓名').AsString,[]) then begin
          t_sys_awoke_emp.Insert;
          t_sys_awoke_emp.FieldByName('awoke_id').AsInteger:=t_sys_awoke.fieldByName('awoke_id').AsInteger;
          t_sys_awoke_emp.FieldByName('数据范围').AsString:='个人数据';
          t_sys_awoke_emp.FieldByName('人员').AsString:=FieldByName('姓名').AsString;
          t_sys_awoke_emp['是否提醒']:=1;
          t_sys_awoke_emp.Post;
        end;
        Next;
      end;
    end;
  finally
    FreeAndNil(aQuery);
  end;

end;

procedure TfrmMessageSet.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  if t_sys_awoke_emp.State in [dsedit,dsinsert] then t_sys_awoke_emp.Post;
  if t_sys_awoke.State in [dsedit,dsinsert] then t_sys_awoke.Post;
end;

procedure TfrmMessageSet.dxDBGrid2DblClick(Sender: TObject);
begin
  if bsSkinButton11.Caption='编辑修改' then
  begin
    bsSkinButton11.Caption:='返回列表';
//    t_sys_awoke.Edit;
    Notebook1.PageIndex:=1;
    exit;
  end;
end;

procedure TfrmMessageSet.Panel2Resize(Sender: TObject);
var
  i:integer;
begin
  i:=round(Panel2.Width/13);
  bsSkinButton7.Width:=i;
  bsSkinButton6.Width:=i;
  bsSkinButton9.Width:=i;
  bsSkinButton1.Width:=i;
  bsSkinButton11.Width:=i;
  bsSkinButton12.Width:=i;
  bsSkinButton2.Width:=i;
  bsSkinButton4.Width:=i;
  bsSkinButton3.Width:=i;
  bsSkinButton38.Width:=i;
  bsSkinButton5.Width:=i;
  bsSkinButton13.Width:=i;

end;

procedure TfrmMessageSet.bsSkinButton4Click(Sender: TObject);
begin
  if t_sys_awoke.FieldByName('提醒状态').AsString='准备执行' then exit;
  if t_sys_awoke.IsEmpty then exit;
  t_sys_awoke.Delete;
end;

procedure TfrmMessageSet.t_sys_awokeBeforePost(DataSet: TDataSet);
begin
  table_before_post(t_sys_awoke,'提醒名称');
  table_before_post(t_sys_awoke,'提醒方式');
  table_before_post(t_sys_awoke,'提醒状态');
  table_before_post(t_sys_awoke,'开始日期');
  table_before_post(t_sys_awoke,'结束日期');

end;

procedure TfrmMessageSet.bsSkinButton12Click(Sender: TObject);
begin
  if not if_open('242') then
  begin
    showmessage('对不起你没有此模块的操作权限！');
    exit;
  end;
  frmmessageparam:=Tfrmmessageparam.Create(SELF);
  try
    frmmessageparam.ShowModal;
  finally
    FreeAndNil(frmmessageparam);
  end;
end;

procedure TfrmMessageSet.bsSkinButton10Click(Sender: TObject);
var
  A1,A2,A3,A4,S,str_bill,mail_s,str_emp,startday,endday:String;
  aQuery:TADOQuery;
begin
  aQuery:=CreateAdoQuery;
  t_sys_awoke_emp.First;
  while not t_sys_awoke_emp.Eof do begin
    if t_sys_awoke_emp['是否提醒'] then begin
      A1:='';
      A2:='';
      A3:='';
      A4:='';
      str_bill:='';
      mail_s:='';
      str_emp:='';
      S:=Trim(t_sys_awoke.FieldByName('提醒内容').AsString);
      if t_sys_awoke_emp.FieldByName('数据范围').AsString='部门数据' then begin
        aQuery.Close;aQuery.sql.Clear;
        aQuery.SQL.Add('Select 姓名 from t_sys_employee where 部门名称=(select 部门名称 from t_sys_employee where 姓名='''+t_sys_awoke_emp.FieldByName('人员').AsString+''')');
        aQuery.Open;
        if not aQuery.IsEmpty  then begin
          aQuery.first;
          while not aQuery.Eof do begin
            if str_emp='' then
              str_emp:=''''+t_sys_awoke_emp.FieldByName('人员').AsString+''''
            else
              str_emp:=str_emp+','+''''+t_sys_awoke_emp.FieldByName('人员').AsString+'''';
            aQuery.Next;
          end;
        end;
      end;

      if Trim(t_sys_awoke.FieldByName('提醒类别').AsString)='欠费提醒' then begin
        A1:=Copy(s,6,pos(',',s)-6);
        A2:=Copy(s,pos('欠费构成=',s)+9,length(s)-pos('欠费构成=',s)-7);
          if t_sys_awoke_emp.FieldByName('数据范围').AsString='公司数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+'''';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='部门数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+''''
            +' and v_op_bscard.揽货人 in ('+str_emp+')';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='个人数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+''''
            +' and v_op_bscard.揽货人='+''''+t_sys_awoke_emp.FieldByName('人员').asstring+'''';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='权限数据' then begin
            Str_bill:='where '+open_data('0033','v_op_bscard.揽货人','v_op_bscard.操作员','v_op_bscard.客服员','no','no')
            +' and t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+'''';
          end;
        if A2='只超过账期欠费' Then begin
          Str_bill:=Str_bill+' and t_ch_fee.应结日期<='+''''+FormatDateTime('YYYY-MM-DD',NOW)+'''';
        end;
        if A1='业务明细' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT t_ch_fee.编号, t_ch_fee.客户名称,'
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,'
             +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
             +'v_op_bscard.分提单号, v_op_bscard.委托编号,'
             +'v_op_bscard.委托单位 '
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill
             +' GROUP BY t_ch_fee.编号, t_ch_fee.客户名称,'
             +'v_op_bscard.业务编号, v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
             +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
             +'v_op_bscard.委托单位 ';
             aQuery.Open;
             if not aQuery.IsEmpty then begin
               aQuery.first;
               mail_s:='欠费明细';
               while not aQuery.eof do begin
                 mail_s:=mail_s+#13#10+'业务编号:'+aQuery.FieldByName('业务编号').AsString+';'+'委托编号:'+aQuery.FieldByName('委托编号').AsString+';'+'客户名称:'+aQuery.FieldByName('客户名称').AsString+';'+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
                 aQuery.next;
               end;
             end;
        end else if A1='客户合计' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT t_ch_fee.客户名称,'
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计'
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill
             +' GROUP BY t_ch_fee.客户名称';
             aQuery.Open;

             if not aQuery.IsEmpty then begin
               aQuery.first;
               mail_s:='客户欠费';
               while not aQuery.eof do begin
                 mail_s:=mail_s+#13#10+'客户名称:'+aQuery.FieldByName('客户名称').AsString+';'+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
                 aQuery.next;
               end;
             end;
        end else if A1='金额总计' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT '
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计'
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill;
             aQuery.Open;

             if not aQuery.IsEmpty then begin
               mail_s:='欠费总计';
               mail_s:=mail_s+#13#10+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
             end;

        end;

      end else if Trim(t_sys_awoke.FieldByName('提醒类别').AsString)='未完成费用提醒' then begin
        if (dayofweek(now)=2) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW+3);
        end;

        if (dayofweek(now)=3) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW-1);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW+2);
        end;

        if (dayofweek(now)=4) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW-2);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW+1);
        end;

        if (dayofweek(now)=5) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW-3);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW);
        end;
        if (dayofweek(now)=6) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW+2);
        end;

        if (dayofweek(now)=7) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW-1);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW+1);
        end;
        if (dayofweek(now)=1) then begin
          startday:=FormatDateTime('YYYY-MM-DD',NOW-2);
          ENDday:=FormatDateTime('YYYY-MM-DD',NOW);
        end;


        if t_sys_awoke_emp.FieldByName('数据范围').AsString='公司数据' then begin
          Str_bill:=' where v_op_bscard.开船日期>='+''''+startday+''''+' AND v_op_bscard.开船日期<='+''''+endday+'''';
        end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='部门数据' then begin
          Str_bill:=' where v_op_bscard.开船日期>='+''''+startday+''''+' AND v_op_bscard.开船日期<='+''''+endday+''''
          +' and v_op_bscard.操作员 in ('+str_emp+')';
        end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='个人数据' then begin
          Str_bill:=' where v_op_bscard.开船日期>='+''''+startday+''''+' AND v_op_bscard.开船日期<='+''''+endday+''''
          +' and v_op_bscard.操作员='+''''+t_sys_awoke_emp.FieldByName('人员').asstring+'''';
        end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='权限数据' then begin
          Str_bill:=' where v_op_bscard.开船日期>='+''''+startday+''''+' AND v_op_bscard.开船日期<='+''''+endday+''''
          +' and '+open_data('0001','v_op_bscard.揽货人','v_op_bscard.操作员','v_op_bscard.客服员','no','no');
        end;


        if pos('驳回的费用=',s)>0 then
          A1:='1'
        else
          A1:='0';
        if pos('开船未录入费用=',s)>0 then
          A2:='1'
        else
          A2:='0';
        if pos('开船当天未提交费用=',s)>0 then
          A3:='1'
        else
          A3:='0';
        if pos('票结客户当天未开发票=',s)>0 then
          A4:='1'
        else
          A4:='0';

         if A1='1' then begin
           aQuery.Close;aQuery.SQL.Clear;
           aQuery.SQL.Add('select count(distinct 编号) as 票数 from t_ch_fee where 费用状态=''驳回提交'' and 编号 in (select 编号 from v_op_bscard '+str_bill+')');
           aQuery.Open;
           if aQuery.FieldByName('票数').AsInteger<>0 then begin
             mail_s:='驳回费用:'+aQuery.fieldByName('票数').AsString+'票';
           end;
         end;
         if A2='1' then begin
         end;
         if A3='1' then begin
           aQuery.Close;aQuery.SQL.Clear;
           aQuery.SQL.Add('select count(distinct 编号) as 票数 from t_ch_fee where 费用状态=''录入状态'' and 编号 in (select 编号 from v_op_bscard '+str_bill+' and v_op_bscard.开船日期<='''+FormatDateTime('YYYY-MM-DD',NOW)+''')');
           aQuery.Open;
           if aQuery.FieldByName('票数').AsInteger<>0 then begin
             mail_s:=mail_s+#13#10+'未提交费用:'+aQuery.fieldByName('票数').AsString+'票';
           end;
         end;
         if A4='1' then begin
           aQuery.Close;aQuery.SQL.Clear;
           aQuery.SQL.Add('select count(distinct 编号) as 票数 from t_ch_fee where 开票金额=0 and 类型=''收'' and 客户名称 not in (select 客户简称 from t_crm_client_sales) and 编号 in (select 编号 from v_op_bscard '+str_bill+' and v_op_bscard.开船日期<='''+FormatDateTime('YYYY-MM-DD',NOW)+''')');
           aQuery.Open;
           if aQuery.FieldByName('票数').AsInteger<>0 then begin
             mail_s:=mail_s+#13#10+'票结客户未开发票:'+aQuery.fieldByName('票数').AsString+'票';
           end;
         end;

      end else if Trim(t_sys_awoke.FieldByName('提醒类别').AsString)='死帐提醒' then begin
        A1:=Copy(S,6,POS(',天数',S)-6);
        A2:=Copy(S,POS('天数=',S)+5,POS(',提醒方式=',S)-POS('天数=',S)-5);
        A3:=Copy(s,pos('提醒方式=',s)+9,length(s)-pos('提醒方式=',s)-7);

          if t_sys_awoke_emp.FieldByName('数据范围').AsString='公司数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+'''';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='部门数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+''''
            +' and v_op_bscard.揽货人 in ('+str_emp+')';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='个人数据' then begin
            Str_bill:=' where t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+''''
            +' and v_op_bscard.揽货人='+''''+t_sys_awoke_emp.FieldByName('人员').asstring+'''';
          end else if t_sys_awoke_emp.FieldByName('数据范围').AsString='权限数据' then begin
            Str_bill:='where '+open_data('0033','v_op_bscard.揽货人','v_op_bscard.操作员','v_op_bscard.客服员','no','no')
            +' and t_ch_fee.金额<>t_ch_fee.结算金额'+' and t_ch_fee.类型='+''''+'收'+'''';
          end;

         if A1='开船后' then begin
           str_bill:=str_bill+' and v_op_bscard.开船日期<='+''''+FormatDateTime('YYYY-MM-DD',now-strtointdef(A1,0))+'''';
         end else if A1='账期后' then begin
           str_bill:=str_bill+' and t_ch_fee.应结日期<='+''''+FormatDateTime('YYYY-MM-DD',now-strtointdef(A1,0))+'''';
         end;

        if A3='业务明细' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT t_ch_fee.编号, t_ch_fee.客户名称,'
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计,'
             +'v_op_bscard.业务编号,v_op_bscard.业务类型, v_op_bscard.会计期间, v_op_bscard.主提单号,'
             +'v_op_bscard.分提单号, v_op_bscard.委托编号,'
             +'v_op_bscard.委托单位 '
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill
             +' GROUP BY t_ch_fee.编号, t_ch_fee.客户名称,'
             +'v_op_bscard.业务编号, v_op_bscard.业务类型,v_op_bscard.会计期间, v_op_bscard.主提单号,'
             +'v_op_bscard.分提单号, v_op_bscard.委托编号, '
             +'v_op_bscard.委托单位 ';
             aQuery.Open;
             if not aQuery.IsEmpty then begin
               aQuery.first;
               mail_s:='死帐明细';
               while not aQuery.eof do begin
                 mail_s:=mail_s+#13#10+'业务编号:'+aQuery.FieldByName('业务编号').AsString+';'+'委托编号:'+aQuery.FieldByName('委托编号').AsString+';'+'客户名称:'+aQuery.FieldByName('客户名称').AsString+';'+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
                 aQuery.next;
               end;
             end;
        end else if A3='客户合计' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT t_ch_fee.客户名称,'
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计'
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill
             +' GROUP BY t_ch_fee.客户名称';
             aQuery.Open;
             if not aQuery.IsEmpty then begin
               aQuery.first;
               mail_s:='客户死帐';
               while not aQuery.eof do begin
                 mail_s:=mail_s+#13#10+'客户名称:'+aQuery.FieldByName('客户名称').AsString+';'+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
                 aQuery.next;
               end;
             end;
        end else if A3='总金额' then begin
            aQuery.Close;aQuery.SQL.Clear;
            aQuery.SQL.text:='SELECT '
             +'SUM(CASE t_ch_fee.类型 WHEN '+''''+'收'+''''
             +' THEN (t_ch_fee.金额-t_ch_fee.结算金额)*t_ch_fee.汇率 ELSE 0 END) as 未收合计'
             +' FROM t_ch_fee inner join v_op_bscard on v_op_bscard.编号=t_ch_fee.编号 '
             +Str_bill;
             aQuery.Open;
             if not aQuery.IsEmpty then begin
               mail_s:='死帐总计';
               mail_s:=mail_s+#13#10+'欠费金额:'+aQuery.FieldByName('未收合计').AsString;
             end;

        end;



      end else if Trim(t_sys_awoke.FieldByName('提醒类别').AsString)='协议账期提醒' then begin
        A1:=Copy(s,pos('天数=',s)+5,length(s)-pos('天数=',s)-3);
        aQuery.Close;
        aQuery.SQL.Clear;
        aQuery.SQL.Add('Select * from t_crm_client_contract where 有效日期<='''+FormatDateTime('YYYY-MM-DD',NOW+Strtointdef(A1,0))+''' AND 有效日期>'''+FormatDateTime('YYYY-MM-DD',NOW)+'''');
        aQuery.Open;
        if not aQuery.IsEmpty then begin
          aQuery.First;
          while not aQuery.Eof do begin
             mail_s:='超过协议账期协议';
             mail_s:=mail_s+#13#10+'客户名称:'+aQuery.FieldByName('客户简称').AsString;
            aQuery.Next;
          end;
        end;

      end else if Trim(t_sys_awoke.FieldByName('提醒类别').AsString)='SQL语句设置' then begin


      end;
    end;
    if Trim(mail_s)<>'' then begin
        SMTP1.AuthType := satDefault;       //(satNone, satDefault, satSASL);
//      SMTP1.AuthenticationType := atLogin;
      SMTP1.username:= frm_data_share.t_sys_company.FieldByName('登录用户名').AsString;
      SMTP1.Password :=frm_data_share.t_sys_company.FieldByName('登录密码').AsString;

      SMTP1.Host :=frm_data_share.t_sys_company.FieldByName('SMTP服务器地址').AsString;
      SMTP1.Port :=Strtointdef(Trim(frm_data_share.t_sys_company.FieldByName('SMTP服务器端口').AsString),25);
      try
        SMTP1.Connect;
      except
        Showmessage('连接SMTP服务器失败!');
        Exit;
      end;
      try
      with IdMsg do
      begin
        body.Clear;
        Body.Text :=mail_s;
        From.address :=frm_data_share.t_sys_company.FieldByName('SMTP邮箱名称').AsString;
        Recipients.EMailAddresses :=GetEmail(t_sys_awoke_emp.FieldByName('人员').asstring);
        Subject:=t_sys_awoke.fieldByName('提醒标题').AsString;
       end;
       SMTP1.Send(IdMsg);
       finally
         showmessage(t_sys_awoke_emp.FieldByName('人员').asstring+'的信件已成功发送');
         SMTP1.Disconnect;
        end;

{
      NMSMTP1.Host:=frm_data_share.t_sys_company.FieldByName('SMTP服务器地址').AsString;
      NMSMTP1.Port:=Strtointdef(Trim(frm_data_share.t_sys_company.FieldByName('SMTP服务器端口').AsString),25);
      if NMSMTP1.Connected then
      begin
         NMSMTP1.Disconnect;
      end;
      NMSMTP1.Connect;
      NMSMTP1.PostMessage.FromAddress :=frm_data_share.t_sys_company.FieldByName('SMTP邮箱名称').AsString;
      NMSMTP1.PostMessage.FromName :='邮件自动提醒';
      NMSMTP1.PostMessage.ToAddress.Text :=GetEmail(t_sys_awoke_emp.FieldByName('人员').asstring);
      NMSMTP1.PostMessage.Body.Text :=mail_s;
      NMSMTP1.PostMessage.Subject :=t_sys_awoke.fieldByName('提醒标题').AsString;
      NMSMTP1.SendMail;
}
//      showmessage(t_sys_awoke_emp.FieldByName('人员').asstring);
    end;
    t_sys_awoke_emp.Next;
  end;
  FreeAndNil(aQuery);
end;

procedure TfrmMessageSet.N7Click(Sender: TObject);
begin
  if MessageDlg('确实要删除所以人员信息吗?',mtWarning,[mbYes,mbNo],0)<>mrYes then
    exit;
  t_sys_awoke_emp.First;
  while not t_sys_awoke_emp.Eof do begin
    t_sys_awoke_emp.Delete;
  end;
end;

procedure TfrmMessageSet.t_sys_awokeBeforeInsert(DataSet: TDataSet);
begin
  if not if_open('241') then
  begin
    showmessage('对不起你没有此模块的操作权限！');
    Abort;
  end;
end;

procedure TfrmMessageSet.t_sys_awokeBeforeDelete(DataSet: TDataSet);
begin
   if not if_open('243') then
  begin
    showmessage('对不起你没有此模块的操作权限！');
    Abort;
  end;

end;

procedure TfrmMessageSet.NMSMTP1Connect(Sender: TObject);
var
  StrUserName,strPassword:String;
begin
{
  strUserName:=EncodeString(frm_data_share.t_sys_company.FieldByName('登录用户名').AsString);
  strPassword:=EncodeString(frm_data_share.t_sys_company.FieldByName('登录密码').AsString);
  nmsmtp1.Transaction('EHLO')   ;
  nmsmtp1.Transaction('AUTH   LOGIN');
  nmsmtp1.Transaction(strUserName);
  nmsmtp1.Transaction(strPassword);
  }
end;

procedure TfrmMessageSet.N8Click(Sender: TObject);
begin
  t_sys_awoke.Insert;
  Notebook1.PageIndex:=1;
end;

procedure TfrmMessageSet.bsSkinButton1Click(Sender: TObject);
var
  Pnt:Tpoint;
begin
  Pnt:=bsSkinButton1.ClientToScreen(Point(0,bsSkinButton1.Height));
  PopupMenu2.Popup(Pnt.x,Pnt.y);

end;

procedure TfrmMessageSet.N10Click(Sender: TObject);
var
  afield : variant;
  i: Integer;
begin
  if t_sys_awoke.IsEmpty then
  begin
    showmessage('没有复制母体，不能复制新建！');
    exit;
  end;
  afield := vararraycreate([0,t_sys_awoke.fieldcount-1],varvariant);
  for i := 0 to (t_sys_awoke.fieldcount-1) do
  begin
    afield[i] :=t_sys_awoke.fields[i].value ;
  end;
  t_sys_awoke.append;
  for i := 0 to (t_sys_awoke.fieldcount-1) do
  begin
    if t_sys_awoke.fields[i].FieldName<>'AWOKE_ID' then
      t_sys_awoke.fields[i].value := afield[i] ;
  end;
  t_sys_awoke.FieldByName('提醒名称').AsString:='';
  Notebook1.PageIndex:=1;

//  t_sys_awoke.Post;
end;

procedure TfrmMessageSet.N12Click(Sender: TObject);
begin
  if t_sys_awoke.State in [dsinsert] then t_sys_awoke.Post;
  frmaddemp:=Tfrmaddemp.Create(self);
  try
    frmaddemp.ShowModal;
  finally
    FreeAndNil(frmaddemp);
  end;
end;

procedure TfrmMessageSet.N14Click(Sender: TObject);
begin
  if MessageDlg('确实要删除此人员信息吗?',mtWarning,[mbYes,mbNo],0)<>mrYes then
    exit;
   t_sys_awoke_emp.Delete;

end;



function TfrmMessageSet.GetStrChar(str:String):string;
var
  i:integer;
begin
  result:='';
  if str='' then exit;
  for i:=1 to length(Str) do begin
    if str[i] in ['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']
    then
    else
      result:=result+str[i];
  end;
end;




function TfrmMessageSet.CreateIgengzongxml(SOI:string):TStringlist;
var
  str,alist:Tstringlist;
  ScriptPath,StlType,UpPath:string;
  s:widestring;
  aQuery:TAdoQuery;
  i:integer;
  IdFTP1:TIdFTP;
  sourcestr,str_file:widestring;
  hh:Word;
begin
  i:=1;
  aQuery:=CreateAdoQuery;
  str:=Tstringlist.Create;
  alist:=Tstringlist.Create;
  with aQuery do
  try
    close;
    sql.Clear;
    sql.Add('Select t_op_seae.船公司,t_crm_client.IGZ代码 EDI代码, t_op_seae.装港代码,t_op_seae.主提单号');
    sql.Add('from t_op_seae');
    sql.Add('left join  t_crm_client on t_crm_client.客户简称=t_op_seae.船公司');
    sql.Add('where t_op_seae.开船日期>=GetDate()-100 and t_op_seae.开船日期<=GetDate() ');
    open;First;
    if not IsEmpty then
    begin
      str.Clear;
      str.Add('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
        str.Add('<Message>');
          str.Add('<Header>');
            str.Add('<MessageType MessageVersion="2.0">QueryCondition');
            str.Add('</MessageType>');
            str.Add('<DateTime DateType="Document">'+FormatDateTime('YYYYMMDD',Date)+'000000');
            str.Add('</DateTime>');
          str.Add('</Header>');
          str.Add('<MessageBody>');
      while not eof do
      begin
         if GetStrChar(trim(fieldbyname('主提单号').asstring))='' then
         begin
           str.Add('<Trace Type="BLNo" CarrierCode="'+FieldByName('EDI代码').AsString+'" PortCode="'+FieldByName('装港代码').AsString+'" DockCode="" EndDate="">');
           str.Add('<Index Count="'+IntTostr(i)+'">'+trim(FieldByName('主提单号').AsString)+'</Index>');
           str.Add('</Trace>');
         end
         else
         begin
           showmessage('非法提单号:'+fieldbyname('主提单号').asstring);
         end;
        Next;
      end;
    end;
    if not IsEmpty then
    begin
          str.Add('</MessageBody>');
       str.Add('</Message>');
    end;

    IdFTP1:=TIdFTP.Create(application);
    with idftp1 do
    begin
     idftp1.ListenTimeout:=6000;
     idftp1.ReadTimeout:=6000;
      Host:=frm_data_share.t_sys_company.FieldByName('FTP服务器地址').AsString;
      Username:=frm_data_share.t_sys_company.FieldByName('FTP用户名').AsString;
      Password:=frm_data_share.t_sys_company.FieldByName('FTP密码').AsString;
      Port:=frm_data_share.t_sys_company.FieldByName('FTP端口').AsInteger;

      Connect;
      Passive:=true;
      if idftp1.Connected then
      begin
        showmessage('FTP上传连接成功'+' '+Host+' '+Username+' '+Password+' '+IntToStr(Port));
      end
      else
      begin
        showmessage('FTP上传连接失败');
      end;


    end;
   // Sleep(2000);
    UpPath:='/';
    idftp1.ChangeDir(UpPath);
    //idftp1.TransferType:=ftASCII;
    //idftp1.TransferType:=ftBinary;

    hh:=24;
  finally
    if str.Count>0 then
    begin
      str.SaveToFile('d:\YT_'+FormatDateTime('YYYYMMDD',Date)+'_'+IntTostr(hh)+'.xml');

      str_file:='YT_'+FormatDateTime('YYYYMMDD',Date)+'_'+IntTostr(hh)+'.xml';
      sourcestr:='d:\YT_'+FormatDateTime('YYYYMMDD',Date)+'_'+IntTostr(hh)+'.xml';
      if idftp1.Connected then
      begin
         idftp1.Put(sourcestr,str_file);
         showmessage('FTP上传文件成功')
      end;
    end;
    freeandnil(aQuery);
    str.Free;
    alist.Free;
    if idftp1.Connected then
      idftp1.Disconnect;
  end;
end;



procedure TfrmMessageSet.bsSkinButton13Click(Sender: TObject);
begin
  CreateIgengzongxml('SO');
end;

end.