unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, StdCtrls, ADODB, ExtCtrls,inifiles, Grids, DBGrids,
  RxMemDS, ComCtrls, Menus, DBCtrls, kbmMemTable, kbmMemBinaryStreamFormat;

type
  TForm1 = class(TForm)
    Database1: TDatabase;
    busicard: TQuery;
    Button1: TButton;
    t_op_seae: TADOQuery;
    Panel1: TPanel;
    Memo1: TMemo;
    conAdoServer: TADOConnection;
    Panel2: TPanel;
    Memo2: TMemo;
    Button2: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    t_ch_fee: TADOQuery;
    t_ch_fee_do: TADOQuery;
    carriage: TQuery;
    stlitems: TQuery;
    temp: TADOQuery;
    p_id: TADOStoredProc;
    p_no_get: TADOStoredProc;
    t_sum_ctn: TRxMemoryData;
    t_code_ctn_de: TADOQuery;
    t_code_ctn: TADOQuery;
    t_code_ctn_kind: TADOQuery;
    bscntrqty: TQuery;
    t_op_ctn: TADOQuery;
    qrytemp: TQuery;
    t_op_seae1: TDataSource;
    ProgressBar1: TProgressBar;
    Button3: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    t_crm_client: TADOQuery;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    SaveDialog1: TSaveDialog;
    t_code_fee: TADOQuery;
    p_op_gain: TADOStoredProc;
    qrytemp2: TQuery;
    t_code_trade: TADOQuery;
    t_code_source: TADOQuery;
    t_code_source_dl: TADOQuery;
    t_sys_employee: TADOQuery;
    t_sys_employee1: TDataSource;
    Button4: TButton;
    t_sys_code: TADOQuery;
    t_sys_code_data_all: TADOQuery;
    t_sys_code_data: TADOQuery;
    t_sys_code_emp: TADOQuery;
    carriage1: TDataSource;
    memtblDetail: TkbmMemTable;
    kbmThreadDataSet1: TkbmThreadDataSet;
    kbmBinaryStreamFormat1: TkbmBinaryStreamFormat;
    t_sys_department: TADOQuery;
    t_sys_code_sql: TADOQuery;
    memtblDetailField: TStringField;
    DataSource2: TDataSource;
    t_ch_balance: TADOQuery;
    Button8: TButton;
    t_ch_invoice: TADOQuery;
    invitem: TQuery;
    DataSource3: TDataSource;
    procedure conAdoServerBeforeConnect(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure t_crm_clientAfterInsert(DataSet: TDataSet);
    procedure Button6Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
  private
    function  numtoc(c:char):string;
    Function  numtoen(c:char):String;
    Function  spart(s:String;k:integer;Isnil:boolean):String ;

    procedure update_ctn(table:Tadoquery);//更新箱量
    Function change( data:extended; mode: Boolean): String;
    function ntoc(s:real;bool:boolean):string;
    function get_id:string;
    function get_no(d1:Tdatetime;no:string):string;
    function connect_string(file_name:string):string;
    function WeekofYear(Date: TDate): integer;
    function getcust(custtype,custid:string):string;
    function getemp(emptype,empid:string):string;
    function getdept(depttype,empid:string):string;
    function getdeptname(depttype,emp:string):string;
    function getvessl(vslid:string):string;
    function getsub(subtype,empid:string):string;
    function getFeename(feeid:string):string;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function TForm1.getFeename(feeid:string):string;
begin
  if trim(feeid)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    close;sql.Clear;
    sql.Add('select 中文名 from t_code_fee where 代码='''+feeid+'''');
    open;
    if IsEmpty then begin
      qrytemp.close;qrytemp.SQL.Clear;
      qrytemp.SQL.Add('select cname from accounts where accid='''+feeid+'''');
      qrytemp.Open;
      if not IsEmpty then begin
        result:=qrytemp.fieldbyname('cname').AsString;
      end else begin
        Memo1.Lines.Add('费用名称不存在--'':'+feeid);
        result:=feeid;
      end;
    end else
      result:=Fieldbyname('中文名').asstring;;
  end;

end;
function TForm1.getsub(subtype,empid:string):string;
begin
  if trim(empid)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    close;sql.Clear;
    sql.Add('select 分部名称 from t_sys_employee where 代码='''+empid+'''');
    open;
    if IsEmpty then begin
      Memo1.Lines.Add('分部资料不存在--'+subtype+':'+empid);
      result:=empid;
    end else
      result:=Fieldbyname('分部名称').asstring;;
  end;
end;


function TForm1.getdept(depttype,empid:string):string;
begin
  if trim(empid)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    close;sql.Clear;
    sql.Add('select 部门名称 from t_sys_employee where 代码='''+empid+'''');
    open;
    if IsEmpty then begin
      Memo1.Lines.Add('部门资料不存在--'+depttype+':'+empid);
      result:=empid;
    end else
      result:=Fieldbyname('部门名称').asstring;;
  end;
end;

function TForm1.getdeptname(depttype,emp:string):string;
begin
  if trim(emp)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    close;sql.Clear;
    sql.Add('select 部门名称 from t_sys_employee where 姓名='''+emp+'''');
    open;
    if IsEmpty then begin
      Memo1.Lines.Add('部门资料不存在--'+depttype+':'+emp);
      result:=emp;
    end else
      result:=Fieldbyname('部门名称').asstring;;
  end;
end;
function TForm1.getemp(emptype, empid: string): string;
begin
  if trim(empid)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    close;sql.Clear;
    sql.Add('select 姓名 from t_sys_employee where 代码='''+empid+'''');
    open;
    if IsEmpty then begin
      Memo1.Lines.Add('员工资料不存在--'+emptype+':'+empid);
      result:=empid;
    end else
      result:=Fieldbyname('姓名').asstring;;
  end;
end;


function TForm1.getvessl(vslid:string):string;
begin
  if trim(vslid)='' then begin
    result:='';
    exit;
  end;
  with qrytemp do begin
    close;sql.clear;
    sql.Add('select ename from vesselvoy where vslid='''+VSLID+'''');
    open;
    result:=fieldbyname('ename').AsString;
  end;
end;

function TForm1.getcust(custtype,custid:string):string;
begin
  if trim(custid)='' then begin
    result:='';
    exit;
  end;
  with temp do begin
    qrytemp.close;qrytemp.sql.Clear;
    qrytemp.sql.Add('select sname from customer where custid='''+custid+'''');
    qrytemp.open;
    Close;SQL.Clear;
    sql.Add('select 客户简称 from t_crm_client where 代码='''+custid+'''');
    open;
    if IsEmpty then begin
      if qrytemp.IsEmpty then begin
        Memo1.Lines.Add('客户资料不存在--'+custtype+':'+custid);
        result:=custid;
      end else
        result:=qrytemp.fieldbyname('sname').AsString;  
    end else
      result:=Fieldbyname('客户简称').asstring;;
  end;
end;
function TForm1.WeekofYear(Date: TDate): integer;
var
  FirstDay, FirstWeekEnd, NowWeekEnd: TDate;
  Year, Month, Day: word;
begin
  date:=date-1;
  DecodeDate(Date, Year, Month, Day);
  FirstDay := EncodeDate(Year, 1, 1);
  FirstWeekEnd := FirstDay +  7 - DayofWeek(FirstDay);
  NowWeekEnd := Date + 7 - DayofWeek(Date);
  Result := Round(NowWeekEnd - FirstWeekEnd) div 7 + 1;
end;

function TForm1.spart(s:String;k:integer;Isnil:boolean):String ;
var i:Integer;
begin
  if s[2] <> '0' Then
    if s[2] = '1' Then
    begin
      i:=strtoint(s[2]+s[3]);
      case i of
        10 :spart := 'ten';
        11 :spart := 'eleven';
        12 :spart := 'twelve';
        13 :spart := 'thirteen';
        14 :spart := 'fourteen';
        15 :spart := 'fifteen';
        16 :spart := 'sixteen';
        17 :spart := 'seventeen';
        18 :spart := 'eighteen';
        19 :spart := 'nineteen'
      end
    end
    else
    begin
      case s[2] of
        '2':spart := 'twenty';
        '3':spart := 'thirty';
        '4':spart := 'forty';
        '5':spart := 'fifty';
        '6':spart := 'sixty' ;
        '7':spart := 'seventy';
        '8':spart := 'eighty';
        '9':spart := 'ninety'
      end ;
      if s[3] <> '0' Then
        if k=4 then
          result:= result + '-' + numtoen(s[3])
        else
          result:= result+ ' ' + numtoen(s[3])
    end
  else
    result := numtoen(s[3]);


  if result <> '' Then
  begin
    if s[1]<>'0' then
      result := numtoen(s[1]) + ' hundred and ' + result
    else
      if Isnil=false then
        result:=' and '+result
  end
  else
    if s[1]<>'0' then
      result := numtoen(s[1])+ ' hundred '
end;

function TForm1.numtoc(c:char):string;
var s: string;
begin
  case c of
    '0' : s:= '零';
    '1' : s:= '壹';
    '2' : s:= '贰' ;
    '3' : s:= '叁' ;
    '4' : s:= '肆' ;
    '5' : s:= '伍' ;
    '6' : s:= '陆' ;
    '7' : s:= '柒' ;
    '8' : s:= '捌' ;
    '9' : s:= '玖' ;
  end ;
  numtoc:=s;
end;
function TForm1.numtoen(c:char):String;
var s:String;
begin
  case c of
    '0' : s:= '' ;
    '1' : s:= 'one';
    '2' : s:= 'two';
    '3' : s:= 'three';
    '4' : s:= 'four';
    '5' : s:= 'five';
    '6' : s:= 'six';
    '7' : s:= 'seven';
    '8' : s:= 'eight';
    '9' : s:= 'nine' ;
  end ;
  result:=s;
end;

function TForm1.get_id:string;
begin
  p_id.ExecProc;
  result:=p_id.Parameters.ParamByName('str').value;
end;

Function TForm1.change( data:extended; mode: Boolean): String;
const cunit:Array[0..12] of string=('万', '仟', '佰', '拾', '亿', '仟', '佰', '拾', '万', '仟', '佰', '拾', '圆');
      Eunit:array[0..3] of string=(' billion ', ' million ', ' thousand ','');
var
  Sint,sdec,txtdec,txtint,s,intpart,decpart:String;
  i,jj,lentxt:Integer;
  tt:Boolean;
  Sth:array[1..5] of string;
begin
  if data>999999999999.99 then exit;
  If mode = false then    //选中中文模式
  begin
    txtint:=inttostr(trunc(data));
    txtdec:=formatfloat('00',frac(data)*100);
    lentxt := Length(txtint);
    for i := 1 to lentxt  do
      s:= txtint[i] + s;
    txtint := s;
    tt := False ;
    jj := 0;
    //处理整数部分
    if txtint <> '0' then
      for i := lentxt downto 1  do
        if txtint[i] = '0' Then
          if i = 1 then
            Sint := Sint + cunit[12]
          else if i = 9 then
            Sint := Sint + cunit[4]
          else if (i = 5) and (jj < 3) Then
            Sint := Sint + cunit[8]
          else if tt = False then
          begin
            tt := True;
            jj := 1;
          end
          else
            jj := jj + 1
    else
      if tt = True Then
      begin
        Sint := Sint + '零' + numtoc(txtint[i]) + cunit[13 - i];
        tt := False;
      end
      else
        Sint := Sint + numtoc(txtint[i]) + cunit[13 - i];


    //处理小数部分并输出
    if txtdec[2] = '0' Then
      if txtdec[1] = '0' Then
        if Sint = '0' Then
          result := '零圆整'
        else
          result := Sint + '整'
      else
      begin
        sdec := numtoc(txtdec[1]) + '角整';
        result := Sint + sdec;
      end
    else If txtdec[1] = '0' Then
      If Sint[2]= '0' Then
      begin
        sdec := numtoc(txtdec[2]) + '分';
        result:= sdec;
      end
      else
      begin
        sdec := '零' + numtoc(txtdec[2]) + '分';
        result := Sint + sdec;
      end
    else
    begin
      sdec := numtoc(txtdec[1]) + '角' + numtoc(txtdec[2]) + '分';
      result := Sint + sdec;
    end
  end
  // 英文模式
  else If mode = True Then
  begin
    //处理整数
    txtint:=formatfloat('000000000000.00',data);
    tt:=true;
    for i:=1 to 4 do
    begin
      Sth[i]:=txtint[3*i-2]+txtint[3*i-1]+txtint[3*i];
      if Sth[i]<>'000' then
      begin
        intpart:=intpart+spart(Sth[i],i,tt)+eunit[i-1];
        tt:=false
      end
    end;
    if intpart='' then
      intpart:='zero';
    //处理小数
    txtint[13]:='0';
    sdec:=txtint[13]+txtint[14]+txtint[15];
    decpart:=spart(sdec,1,true);
    if decpart='' then
      result:=intpart
    else
      result:=trim(intpart+' point '+decpart)
  end
end;


function TForm1.get_no(d1:Tdatetime;no:string):string;
var
  get_year,get_month,get_day:word;
  str:string;
begin
  decodedate(d1,get_year,get_month,get_day);
  if get_month<10 then
    str:=no+inttostr(get_year)+'0'+inttostr(get_month)
  else
    str:=no+inttostr(get_year)+inttostr(get_month);

  p_no_get.Parameters.ParamByName('Code').value:=str;
  p_no_get.ExecProc;
  result:=p_no_get.Parameters.ParamByName('Num').value;
end;

function TForm1.ntoc(s:real;bool:boolean):string;
begin
  if (s=0) and (bool=true) then
  begin
    result:='ZERO';
    exit;
  end;
  if (s=0) and (bool=false) then
  begin
    result:='零元整';
    exit;
  end;
  result:=trim(uppercase(change(s,bool)))+' ';
end;

procedure TForm1.update_ctn(table:Tadoquery);//更新箱量
var
  num_ctn:integer;
  ctn_string,str:widestring;
  if_first:boolean;
  str_list:Tstringlist;
  bok:Tbookmark;
begin

    str_list:=Tstringlist.Create();
    t_sum_ctn.close;
    t_sum_ctn.open;
    bok:=table.GetBookmark;
      t_op_seae.Edit;
      t_op_seae['箱型1']:=0;
      t_op_seae['箱型2']:=0;
      t_op_seae['箱型3']:=0;
      t_op_seae['箱型4']:=0;
      t_op_seae['箱型5']:=0;
      table.DataSource.DataSet['箱型6']:=0;
      t_op_seae['箱型7']:=0;
      t_op_seae['箱型8']:=0;
      t_op_seae['箱型9']:=0;
      t_op_seae['箱型10']:=0;
      t_op_seae['其他箱型']:=0;
      t_op_seae['箱TEU']:=0;
      t_op_seae['集装箱']:='';
      t_op_seae['计费标准']:='';
      t_op_seae['箱数大写']:='';
      table.first;
      while not table.eof do
      begin
        if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型1').asstring then
        begin
          t_op_seae['箱型1']:=t_op_seae['箱型1']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型2').asstring then
        begin
          t_op_seae['箱型2']:=t_op_seae['箱型2']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型3').asstring then
        begin
          t_op_seae['箱型3']:=t_op_seae['箱型3']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型4').asstring then
        begin
          t_op_seae['箱型4']:=t_op_seae['箱型4']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型5').asstring then
        begin
          t_op_seae['箱型5']:=t_op_seae['箱型5']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型6').asstring then
        begin
          t_op_seae['箱型6']:=t_op_seae['箱型6']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型7').asstring then
        begin
          t_op_seae['箱型7']:=t_op_seae['箱型7']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型8').asstring then
        begin
          t_op_seae['箱型8']:=t_op_seae['箱型8']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型9').asstring then
        begin
          t_op_seae['箱型9']:=t_op_seae['箱型9']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else if table.fieldbyname('代码').asstring=t_code_ctn_de.fieldbyname('箱型10').asstring then
        begin
          t_op_seae['箱型10']:=t_op_seae['箱型10']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end
        else
        begin
          t_op_seae['其他箱型']:=t_op_seae['其他箱型']+table.fieldbyname('数量').asinteger;
          t_op_seae['箱TEU']:=t_op_seae['箱TEU']+table.fieldbyname('TEU').asinteger;
        end;
        if not t_sum_ctn.locate('代码',table.fieldbyname('代码').asstring,[]) then
        begin
          t_sum_ctn.insert;
          t_sum_ctn['代码']:=table.fieldbyname('代码').asstring;
          t_sum_ctn['表现形式']:=table.fieldbyname('表现形式').asstring;
          t_sum_ctn['数量']:=table.fieldbyname('数量').asinteger;
          t_sum_ctn.post;
        end
        else
        begin
          t_sum_ctn.edit;
          t_sum_ctn['数量']:=t_sum_ctn['数量']+table.fieldbyname('数量').asinteger;
          t_sum_ctn.post;
        end;
        table.next;
      end;

      ctn_string:='';
      if_first:=true;
      num_ctn:=0;
      str:='';

      t_sum_ctn.first;
      while not t_sum_ctn.eof do
      begin
        if ctn_string='' then
        begin
          ctn_string:=t_sum_ctn.fieldbyname('表现形式').asstring+'*'+t_sum_ctn.fieldbyname('数量').asstring;
        end
        else
        begin
          ctn_string:=ctn_string+' '+t_sum_ctn.fieldbyname('表现形式').asstring+'*'+t_sum_ctn.fieldbyname('数量').asstring;
        end;
        if if_first then
        begin
          str:=ntoc(t_sum_ctn.fieldbyname('数量').asfloat,true)+'('
            +t_sum_ctn.fieldbyname('表现形式').asstring
            +'*'+t_sum_ctn.fieldbyname('数量').asstring+')';
          if_first:=false;
        end
        else
        begin
          str:=str+' AND '+ntoc(t_sum_ctn.fieldbyname('数量').asfloat,true)+'('
            +t_sum_ctn.fieldbyname('表现形式').asstring
            +'*'+t_sum_ctn.fieldbyname('数量').asstring+')';
        end;
        num_ctn:=num_ctn+t_sum_ctn.fieldbyname('数量').asinteger;
        str_list.Add(t_sum_ctn.fieldbyname('表现形式').asstring+'*'+t_sum_ctn.fieldbyname('数量').asstring
                      +'-'+t_sum_ctn.fieldbyname('表现形式').asstring);
        t_sum_ctn.next;
      end;

      t_op_seae['集装箱']:=ctn_string;
      t_op_seae['计费标准']:=str_list.Text;
      if num_ctn>1 then
      begin
        str:='SAY: '+str+' CONTAINERS ONLY.'
      end
      else
      begin
        str:='SAY: '+str+' CONTAINER ONLY.'
      end;
      t_op_seae['箱数大写']:=str;
      t_op_seae.post;
    str_list.free;
    table.GotoBookmark(bok);

end;


procedure TForm1.conAdoServerBeforeConnect(Sender: TObject);
begin
  conAdoServer.ConnectionString:=connect_string('server.ini');

end;

function TForm1.connect_string(file_name:string):string;
var
  str:string;
  inifile1:Tinifile;
begin
  inifile1:=Tinifile.Create(ExtractFilePath(ParamStr(0))+file_name);
  str:='';
  str:='Provider='+inifile1.ReadString('database','Provider','');
  str:=str+'Password='+inifile1.ReadString('database','Password','');
  str:=str+'Persist Security Info='+inifile1.ReadString('database','Persist Security Info','');
  str:=str+'User ID='+inifile1.ReadString('database','User ID','');
  str:=str+'Initial Catalog='+inifile1.ReadString('database','Initial Catalog','');
  str:=str+'Data Source='+inifile1.ReadString('database','Data Source','');
  result:=str;
  inifile1.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  Database1.Connected:=true;
  conAdoServer.Connected:=true;
  t_sys_employee.open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  with busicard do begin
    Close;sql.Clear;
    sql.Text:=Memo2.Text;
    open;
  end;
  Memo1.Lines.Add('共计'+inttostr(busicard.RecordCount)+'条数据'+datetimetostr(now));
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  i:integer;
  str,cntr,balno,cust:string;
  bal:double;
begin
  Memo1.Lines.Add('开始导入数据,共计'+inttostr(busicard.RecordCount)+'条数据,开始时间'+datetimetostr(now));
  ProgressBar1.Max:=busicard.RecordCount;
  ProgressBar1.Position:=0;
  t_code_ctn.open;
  t_code_ctn_de.open;
  t_ch_balance.Open;
  busicard.First;
  while not busicard.Eof do begin
    t_ch_fee_do.close;
    t_ch_fee.close;
    t_op_ctn.close;
    t_op_seae.close;
    str:=get_id;
    t_op_seae.SQL.Text:='select * from t_op_seae where 编号='''+str+'''';
    t_op_seae.Open;
    t_op_seae.Insert;
    t_op_seae['编号']:=str;
    t_op_seae['主编号']:=str;
    t_op_seae['会计期间']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('BSDATE').AsDateTime);
    t_op_seae['业务编号']:=get_no(t_op_seae.fieldbyname('会计期间').AsDateTime,'SE');
    t_op_seae['委托编号']:=busicard.fieldbyname('BSNO').AsString;
    t_op_seae['主提单号']:=busicard.fieldbyname('MBLNO').AsString;
    t_op_seae['分提单号']:=busicard.fieldbyname('HBLNO').AsString;
    if busicard.FieldByName('GOODSSTATUS').AsInteger=5 then
      t_op_seae['业务状态']:='业务封帐'
    else
      t_op_seae['业务状态']:='接受委托';
    if busicard.FieldByName('STATUS').AsInteger=1 then
      t_op_seae['费用状态']:='费用封帐'
    else
      t_op_seae['费用状态']:='录入状态';
    if busicard.FieldByName('POSTED').AsString='V' then
      t_op_seae['业务类型']:='更改单'
    else
      t_op_seae['业务类型']:='普通货';
    t_op_seae['周次']:=WeekofYear(t_op_seae.fieldbyname('会计期间').AsDateTime);
    if busicard.fieldbyname('BSTYPE').AsString='GENERAL' then
    t_op_seae['装运方式']:='整箱'
    else if busicard.fieldbyname('BSTYPE').AsString='MASTER' then
    t_op_seae['装运方式']:='拼箱主票'
    else if busicard.fieldbyname('BSTYPE').AsString='LCL' then
    t_op_seae['装运方式']:='拼箱分票';
    t_op_seae['委托单位']:=getcust('委托单位',busicard.fieldbyname('CONTROLLERID').AsString);
    t_op_seae['发货人']:=getcust('发货人',busicard.fieldbyname('SHIPPERID').AsString);
    t_op_seae['收货人']:=getcust('收货人',busicard.fieldbyname('CONSIGNEEID').AsString);
    t_op_seae['通知人']:=getcust('通知人',busicard.fieldbyname('NOTIFYPARTYID').AsString);
    t_op_seae['发货人代码']:=busicard.fieldbyname('SHIPPER').AsString;
    t_op_seae['收货人代码']:=busicard.fieldbyname('CONSIGNEE').AsString;
    t_op_seae['通知人代码']:=busicard.fieldbyname('NOTIFYPARTY').AsString;
    t_op_seae['代理']:=getcust('代理',busicard.fieldbyname('DAGENTID').AsString);
    t_op_seae['代理内容']:=busicard.fieldbyname('DAGENT').AsString;
    t_op_seae['场站']:=getcust('场站',busicard.fieldbyname('YARDID').AsString);

    t_op_seae['船名']:=getvessl(busicard.fieldbyname('VSLID').AsString);
    t_op_seae['航次']:=busicard.fieldbyname('VOYNO').AsString;
    if busicard.fieldbyname('etd').AsDateTime>strtodatetime('2002-01-01') then
    t_op_seae['开船日期']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('ETD').AsDateTime);
    if busicard.fieldbyname('eta').AsDateTime>strtodatetime('2002-01-01') then
    t_op_seae['预抵日期']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('ETA').AsDateTime);

    t_op_seae['起运港']:=busicard.fieldbyname('PLACERECEIPT').AsString;
    t_op_seae['装货港']:=busicard.fieldbyname('PORTLOAD').AsString;
    t_op_seae['装港代码']:=busicard.fieldbyname('PORTLOADID').AsString;
    t_op_seae['卸货港']:=busicard.fieldbyname('PORTDISCHARGE').AsString;
    t_op_seae['卸货代码']:=busicard.fieldbyname('PORTDISCHARGEID').AsString;
    t_op_seae['目的地']:=busicard.fieldbyname('DESTINATION').AsString;
    t_op_seae['交货地点']:=busicard.fieldbyname('PLACEDELIVERY').AsString;
    t_op_seae['交货代码']:=busicard.fieldbyname('PLACEDELIVERYID').AsString;
    t_op_seae['提单份数']:=busicard.fieldbyname('NOOBL').AsString;
    t_op_seae['签单方式']:='正本';
    t_op_seae['签单地点']:=busicard.fieldbyname('PLACEISSUE').AsString;
    if busicard.fieldbyname('dateissue').AsDateTime>strtodatetime('2002-01-01') then
    t_op_seae['签单日期']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('DATEISSUE').AsDateTime);
    if busicard.FieldByName('MBLFRT').AsString='PP' then
     t_op_seae['付费方式']:='FREIGHT PREPAID'
    else if busicard.FieldByName('MBLFRT').AsString='CC' then
     t_op_seae['付费方式']:='FREIGHT COLLECT';

    t_op_seae['预付地点']:=busicard.fieldbyname('PREPAIDAT').AsString;
    t_op_seae['到付地点']:=busicard.fieldbyname('PAYABLEAT').AsString;
    t_op_seae['运输条款']:=busicard.fieldbyname('SERVICE').AsString;
    t_op_seae['唛头']:=busicard.fieldbyname('MARKS').AsString;
    t_op_seae['件数包装']:=inttostr(busicard.fieldbyname('PKGS').Asinteger)+busicard.fieldbyname('KINDPKGS').AsString;
    t_op_seae['货物描述']:=busicard.fieldbyname('GOODSDESCRIP').AsString;
    t_op_seae['货物名称']:=busicard.fieldbyname('GOODSNAME').AsString;
    t_op_seae['货物重量']:=floattostr(busicard.fieldbyname('GROSSWEIGHT').Asfloat)+'KGS';
    t_op_seae['货物尺码']:=floattostr(busicard.fieldbyname('MEASUREMENT').Asfloat)+'KGS';
    t_op_seae['件数']:=busicard.fieldbyname('PKGS').Asinteger;
    t_op_seae['包装']:=busicard.fieldbyname('KINDPKGS').AsString;
    t_op_seae['重量']:=busicard.fieldbyname('GROSSWEIGHT').Asfloat;
    t_op_seae['尺码']:=busicard.fieldbyname('MEASUREMENT').Asfloat;
    t_op_seae['件数大写']:=busicard.fieldbyname('TOTALNO').Asstring;
    t_op_seae['箱型1']:=0;
    t_op_seae['箱型2']:=0;
    t_op_seae['箱型3']:=0;
    t_op_seae['箱型4']:=0;
    t_op_seae['箱型5']:=0;
    t_op_seae['箱型6']:=0;
    t_op_seae['箱型7']:=0;
    t_op_seae['箱型8']:=0;
    t_op_seae['箱型9']:=0;
    t_op_seae['箱型10']:=0;
    t_op_seae['其他箱型']:=0;
    t_op_seae['箱TEU']:=0;
    t_op_seae['录入人']:=getemp('录入人',busicard.fieldbyname('OPID').Asstring);
    t_op_seae['录入日期']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('BSDATE').AsDateTime);
    t_op_seae['操作员']:=getemp('操作员',busicard.fieldbyname('OPID').Asstring);
    t_op_seae['揽货人']:=busicard.fieldbyname('SALEID').Asstring;
    t_op_seae['客服员']:=getemp('客服员',busicard.fieldbyname('OPID').Asstring);
    t_op_seae['航线']:=busicard.fieldbyname('LANEID').Asstring;
    t_op_seae['船公司']:=getcust('船公司',busicard.fieldbyname('CARRIERID').AsString);
    t_op_seae['货代公司']:=getcust('货代公司',busicard.fieldbyname('FCARRIERID').AsString);
    t_op_seae['报关行']:=getcust('报关行',busicard.fieldbyname('CUSTBROKERID').AsString);
    t_op_seae['备注']:=busicard.fieldbyname('REMARKS').Asstring;
    t_op_seae['承运车队']:=getcust('承运车队',busicard.fieldbyname('MOTORID').AsString);
    if busicard.fieldbyname('CUSTBROKERTIME').AsDateTime>strtodatetime('2002-01-01') then
    t_op_seae['报关日期']:=FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('CUSTBROKERTIME').AsDateTime);
    t_op_seae['操作部门']:=getdept('操作部门',busicard.fieldbyname('OPID').Asstring);
    t_op_seae['销售部门']:=getdeptname('销售部门',busicard.fieldbyname('SALEID').Asstring);
    t_op_seae['业务来源']:=busicard.fieldbyname('MARKET').Asstring;
    t_op_seae['来源明细']:=busicard.fieldbyname('MARKETNAME').Asstring;
    t_op_seae['财务']:=0;
    t_op_seae['主提单标准']:=str;
    t_op_seae['分提单标准']:=str;
    t_op_seae['委托标准']:=str;
    t_op_seae['核销标准']:=str;
    t_op_seae['报关标准']:=str;
    t_op_seae['报检标准']:=str;
    t_op_seae['分部名称']:=getsub('分部名称',busicard.fieldbyname('OPID').Asstring);;

    t_op_seae.post;

    bscntrqty.Open;
    if bscntrqty.RecordCount<>0 then begin
      t_op_ctn.SQL.Text:='select * from t_op_ctn where 编号='''+str+'''';
      t_op_ctn.Open;
      bscntrqty.first;
      while not bscntrqty.eof do begin
        cntr:=bscntrqty.fieldbyname('CNTR').AsString;
        if length(cntr)=4 then begin
        cntr:=copy(cntr,0,2)+''''+copy(cntr,3,2);
        if t_code_ctn.Locate('表现形式',cntr,[]) then begin
          t_op_ctn.Insert;
          t_op_ctn['编号']:=str;
          t_op_ctn['表现形式']:=t_code_ctn['表现形式'];
          t_op_ctn['箱型']:=t_code_ctn['箱型'];
          t_op_ctn['尺寸']:=t_code_ctn['尺寸'];
          t_op_ctn['代码']:=t_code_ctn['代码'];
          t_op_ctn['数量']:=bscntrqty.fieldbyname('CNTRQTY').AsInteger;
          if t_code_ctn.FieldByName('尺寸').asstring='20'then
            t_op_ctn['TEU']:=1*t_op_ctn.FieldByName('数量').asinteger;
          if (t_code_ctn.FieldByName('尺寸').asstring='40')OR
           (t_code_ctn.FieldByName('尺寸').asstring='45')then
          t_op_ctn['TEU']:=2*t_op_ctn.FieldByName('数量').asinteger;
          t_op_ctn.post;
        end else begin
          Memo1.Lines.Add('箱型'+cntr+'不存在！');
        end;
        end;
        bscntrqty.Next;
      end;
      update_ctn(t_op_ctn);
    end;
    bscntrqty.Close;
    carriage.Open;
    if carriage.RecordCount<>0 then begin
      t_ch_fee.SQL.Text:='select * from t_ch_fee where 编号='''+str+'''';
      t_ch_fee.Open;
      carriage.First;
      while not carriage.Eof do begin
        t_ch_fee.Insert;
        t_ch_fee.fieldbyname('编号').AsString:=str;
        if carriage.FieldByName('DRCR').AsString='D' then
          t_ch_fee.fieldbyname('类型').AsString:='收'
        else
          t_ch_fee.fieldbyname('类型').AsString:='付';
        case carriage.FieldByName('STATUS').AsInteger of
          0:t_ch_fee.fieldbyname('费用状态').AsString:='录入状态';
          1:t_ch_fee.fieldbyname('费用状态').AsString:='审核通过';
          2:t_ch_fee.fieldbyname('费用状态').AsString:='部分结算';
          3:t_ch_fee.fieldbyname('费用状态').AsString:='结算完毕';
        end;
        t_ch_fee.fieldbyname('费用名称').AsString:=getFeename(carriage.fieldbyname('ACCID').AsString);
        if carriage.FieldByName('DRCR').AsString='D' then
          t_ch_fee.fieldbyname('结算单位').AsString:='委托单位'
        else
          t_ch_fee.fieldbyname('结算单位').AsString:='船公司';
        cust:=getcust('费用客户名称',carriage.fieldbyname('OBJID').AsString);
        t_ch_fee['客户名称']:=cust;
        t_ch_fee['标准']:=carriage.fieldbyname('RTUNIT').AsString;
        t_ch_fee['单价']:=carriage.fieldbyname('RTPRICE').Asfloat;
        t_ch_fee['数量']:=carriage.fieldbyname('RT').Asfloat;
        t_ch_fee['金额']:=carriage.fieldbyname('ACCAMT_L').Asfloat;
        if (carriage.fieldbyname('FCURRID').AsString='') or (carriage.fieldbyname('FCURRID').AsString='RMB') then begin
          t_ch_fee['币别']:='RMB';
          t_ch_fee['汇率']:=1;
        end else begin
          t_ch_fee['币别']:=carriage.fieldbyname('FCURRID').AsString;
          t_ch_fee['汇率']:=carriage.fieldbyname('USDEXRATE').Asfloat;
        end;
        t_ch_fee['备注']:=carriage.fieldbyname('COMMENT').AsString;
        t_ch_fee['结算金额']:=carriage.fieldbyname('STLAMT_L').Asfloat;
        t_ch_fee['开票金额']:=0;
        t_ch_fee['录入人']:=getemp('录入人',carriage.fieldbyname('INPUTBY').Asstring);
        t_ch_fee['录入日期']:=FormatDateTime('YYYY-MM-DD',carriage.fieldbyname('INPUTDATE').AsDatetime);
        t_ch_fee['是否对帐']:=0;
        t_ch_fee['是否复核']:=0;
        t_ch_fee['是否垫付']:=0;
        t_ch_fee['机密']:=0;
        t_ch_fee['冲抵金额']:=0;
        t_ch_fee['申请金额']:=0;
        t_ch_fee['不开发票']:=0;
        t_ch_fee.post;

        if carriage.FieldByName('STLAMT_L').AsFloat<>0 then begin
          stlitems.open;
          stlitems.First;
          t_ch_fee_do.Open;
          while not stlitems.Eof do begin
            t_ch_fee_do.insert;
            t_ch_fee_do['类型']:=3;
            bal:=stlitems.fieldbyname('STLRMBDEBIT').AsFloat+stlitems.fieldbyname('STLRMBCREDIT').AsFloat+stlitems.fieldbyname('STLUSDDEBIT').AsFloat+stlitems.fieldbyname('STLUSDCREDIT').AsFloat;
            t_ch_fee_do['金额']:=bal;
            t_ch_fee_do['原始金额']:=bal;
            if (carriage.fieldbyname('FCURRID').AsString='') or (carriage.fieldbyname('FCURRID').AsString='RMB') then
              t_ch_fee_do['币别']:='RMB'
            else
              t_ch_fee_do['币别']:=carriage.fieldbyname('FCURRID').AsString;
            t_ch_fee_do['CH_ID']:=t_ch_fee['CH_ID'];
            t_ch_fee_do['业务编号']:=str;
            if t_ch_balance.Locate('客户名称;结算日期',VarArrayOf([cust,FormatDateTime('YYYY-MM-DD',stlitems.fieldbyname('stldate').AsDatetime)]),[]) then begin
              balno:=t_ch_balance['帐单编号'];
              t_ch_balance.edit;
              if (carriage.fieldbyname('FCURRID').AsString='') or (carriage.fieldbyname('FCURRID').AsString='RMB') then begin
                t_ch_balance['人民币金额']:=t_ch_balance['人民币金额']+bal;
              end else begin
                t_ch_balance['美元金额']:=t_ch_balance['美元金额']+bal;
              end;
              t_ch_balance.post;
            end else begin
              t_ch_balance.insert;
              t_ch_balance['结算类别']:='自由';
              t_ch_balance['帐单状态']:='录入状态';
              t_ch_balance['结算方式']:=stlitems.fieldbyname('STLMODE').AsString;
              t_ch_balance['客户名称']:=cust;
              t_ch_balance['结算人']:=getemp('结算人',stlitems.fieldbyname('OPNAME').Asstring);
              t_ch_balance['结算日期']:=FormatDateTime('YYYY-MM-DD',stlitems.fieldbyname('stldate').AsDatetime);
              t_ch_balance['自动']:=1;
              t_ch_balance['财务']:=0;
              if carriage.FieldByName('DRCR').AsString='D' then begin
                balno:=get_no(t_ch_balance['结算日期'],'BA');
                t_ch_balance['类型']:='收';
              end else begin
                balno:=get_no(t_ch_balance['结算日期'],'BP');
                t_ch_balance['类型']:='付';
              end;
              if (carriage.fieldbyname('FCURRID').AsString='') or (carriage.fieldbyname('FCURRID').AsString='RMB') then begin
                t_ch_balance['人民币金额']:=bal;
                t_ch_balance['美元金额']:=0;
              end else begin
                t_ch_balance['人民币金额']:=0;
                t_ch_balance['美元金额']:=bal;
              end;
              t_ch_balance['帐单编号']:=balno;
              t_ch_balance.post;
            end;
            t_ch_fee_do['工作编号']:=balno;
            t_ch_fee_do.Post;
            stlitems.Next;
          end;
        end;


        carriage.Next;
      end;
     p_op_gain.Parameters.ParamByName('@con_no').value:=str;
     p_op_gain.ExecProc;
     Memo1.Lines.Add(busicard.fieldbyname('BSNO').AsString+'费用数据导入完成！')
    end;
    ProgressBar1.StepIt;
    busicard.Next;
  end;

  Memo1.Lines.Add('全部业务数据导入完成,'+datetimetostr(now));
  showmessage('导入完成！');
end;


procedure TForm1.N1Click(Sender: TObject);
begin
  if SaveDialog1.Execute then
    Memo1.Lines.SaveToFile(SaveDialog1.FileName);
end;

procedure TForm1.t_crm_clientAfterInsert(DataSet: TDataSet);
begin
  t_crm_client['客户性质']:='其他';
  t_crm_client['结费类型']:='约定天数';
//  t_crm_client['录入人']:=employee;
  
end;

procedure TForm1.Button6Click(Sender: TObject);
begin
  with temp do begin
    close;sql.Clear;
    sql.Add('delete from t_code_fee');
    ExecSQL;
  end;

  t_code_fee.open;
  with qrytemp do begin
    Close;sql.Clear;
    sql.Add('select * from accounts');
    open;
    first;
    while not eof do begin
      if not t_code_fee.Locate('中文名',fieldbyname('CNAME').AsString,[]) then begin
        t_code_fee.Insert;
        t_code_fee['代码']:=fieldbyname('AccID').AsString;
        t_code_fee['中文名']:=fieldbyname('CNAME').AsString;
        t_code_fee['英文名']:=fieldbyname('ENAME').AsString;
        t_code_fee['默认币别']:=fieldbyname('CURRID').AsString;
        t_code_fee['海运']:=1;
        t_code_fee['空运']:=1;
        t_code_fee['标准']:='';
        t_code_fee.post;
      end;
      next;
    end;
  end;
  t_code_fee.close;
  showmessage('导入完成！');
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  t_sys_employee.First;
  with temp do begin
    close;sql.Clear;
    sql.Add('delete from t_crm_client_link');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_crm_client_linkman');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_crm_client_contract');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_crm_client_limited');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_crm_client_trade');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_crm_client');
    ExecSQL;
  end;
  t_crm_client.open;
  with qrytemp do begin
    Close;sql.Clear;
    sql.Add('select * from customer');
    open;
    first;
    while not eof do begin
      if (not t_crm_client.Locate('客户简称',fieldbyname('SNAME').AsString,[])) and (not t_crm_client.Locate('代码',fieldbyname('CUSTID').AsString,[])) then begin
        t_crm_client.Insert;
        t_crm_client['代码']:=fieldbyname('CUSTID').AsString;
        t_crm_client['客户简称']:=fieldbyname('SNAME').AsString;
        t_crm_client['客户全称']:=fieldbyname('CNAME').AsString;
        t_crm_client['通讯地址']:=fieldbyname('ADDR').AsString;
        t_crm_client['邮箱']:=fieldbyname('EMAIL').AsString;
        t_crm_client['电话']:=fieldbyname('TEL').AsString;
        t_crm_client['传真']:=fieldbyname('FAX').AsString;
        t_crm_client['录入人']:=t_sys_employee['姓名'];
        t_crm_client['快捷代码']:=fieldbyname('SUMMARY').AsString;
        qrytemp2.close;qrytemp2.sql.Clear;
        qrytemp2.SQL.Add('select * from custtype where custtype='''+fieldbyname('CUSTTYPE').AsString+'''');
        qrytemp2.Open;
        if qrytemp2.FieldByName('SHIPPERID').AsString='T' then
          t_crm_client['发货人']:=1;
        if qrytemp2.FieldByName('CONSIGNEEID').AsString='T' then
          t_crm_client['收货人']:=1;
        if qrytemp2.FieldByName('NOTIFYPARTYID').AsString='T' then
          t_crm_client['通知人']:=1;
        if qrytemp2.FieldByName('CARRIERID').AsString='T' then
          t_crm_client['船公司']:=1;
        if qrytemp2.FieldByName('YARDID').AsString='T' then
          t_crm_client['场站']:=1;
        if qrytemp2.FieldByName('MOTORID').AsString='T' then
          t_crm_client['车队']:=1;
        if qrytemp2.FieldByName('CUSTBROKERID').AsString='T' then
          t_crm_client['报关行']:=1;
        if qrytemp2.FieldByName('CONTROLLERID').AsString='T' then
          t_crm_client['委托单位']:=1;
        if qrytemp2.FieldByName('AGENTID').AsString='T' then
          t_crm_client['代理']:=1;
        if qrytemp2.FieldByName('LOCALAGENTID').AsString='T' then
          t_crm_client['国内代理']:=1;
        t_crm_client.post;
      end;
      next;
    end;
  end;
  t_crm_client.close;
  showmessage('导入完成！');

end;

procedure TForm1.Button5Click(Sender: TObject);
var
  i:integer;
begin
 with temp do begin
    close;sql.Clear;
    sql.Add('delete from t_code_trade_map');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_code_trade');
    ExecSQL;
  end;

  t_code_trade.open;
  with qrytemp do begin
    Close;sql.Clear;
    sql.Add('select * from LANE');
    open;
    first;
    i:=1;
    while not eof do begin
      if not t_code_trade.Locate('航线',fieldbyname('CNAME').AsString,[]) then begin
        t_code_trade.Insert;
        t_code_trade['航线代码']:=inttostr(i);
        t_code_trade['航线']:=fieldbyname('CNAME').AsString;
        t_code_trade.post;
        inc(i);
      end;
      next;
    end;
  end;
  t_code_trade.close;

  showmessage('导入完成！');
end;

procedure TForm1.Button7Click(Sender: TObject);
var
  i:integer;
begin
 with temp do begin
    close;sql.Clear;
    sql.Add('delete from t_code_source_dl');
    ExecSQL;
    close;sql.Clear;
    sql.Add('delete from t_code_source');
    ExecSQL;
  end;

  t_code_source.open;
  with qrytemp do begin
    Close;sql.Clear;
    sql.Add('select * from MARKET');
    open;
    first;
    i:=1;
    while not eof do begin
      if not t_code_source.Locate('业务来源',fieldbyname('MARKET').AsString,[]) then begin
        t_code_source.Insert;
        t_code_source['代码']:=inttostr(i);
        t_code_source['业务来源']:=fieldbyname('MARKET').AsString;
        t_code_source.post;
        inc(i);
      end;
      next;
    end;
  end;
  t_code_source.close;

  t_code_source_dl.open;
  with qrytemp do begin
    Close;sql.Clear;
    sql.Add('select * from MARKETDETAIL');
    open;
    first;
    while not eof do begin
      t_code_source_dl.Insert;
      t_code_source_dl['业务来源']:=fieldbyname('MARKET').AsString;
      t_code_source_dl['来源明细']:=fieldbyname('MARKETNAME').AsString;
      t_code_source_dl.post;
      next;
    end;
  end;
  t_code_source_dl.close;


  showmessage('导入完成！');

end;

procedure TForm1.Button4Click(Sender: TObject);
var
  i:integer;
begin
  t_sys_code.Open;
  t_sys_code_data_all.Open;
  memtblDetail.LoadFromDataSet(t_sys_code_data_all,[]);
  conAdoServer.BeginTrans;
  try
    with temp do begin
      close;sql.Clear;
      sql.Add('delete from t_sys_code_data');
      ExecSQL;
      close;sql.Clear;
      sql.Add('delete from t_sys_code_emp');
      ExecSQL;
      close;sql.Clear;
      sql.Add('delete from t_sys_code_sql');
      ExecSQL;
      close;sql.Clear;
      sql.Add('delete from t_sys_report_employee');
      ExecSQL;

      close;sql.Clear;
      sql.Add('delete from t_sys_department');
      ExecSQL;
      close;sql.Clear;
      sql.Add('delete from t_sys_employee');
      ExecSQL;

    end;
    t_sys_department.Open;
    t_sys_code_emp.open;
    t_sys_code_data.open;
    t_sys_code_sql.open;
    with qrytemp do begin
      close;sql.Clear;
      sql.Add('select * from department');
      open;
      first;
      while not eof do begin
        t_sys_department.Insert;
        t_sys_department['部门代码']:=fieldbyname('DEPTID').AsString;
        t_sys_department['部门名称']:=fieldbyname('SNAME').AsString;
        t_sys_department['分部名称']:='总公司';
        t_sys_department.post;
        Next;
      end;
        t_sys_department.Insert;
        t_sys_department['部门代码']:='WSZBM';
        t_sys_department['部门名称']:='未设置部门';
        t_sys_department['分部名称']:='总公司';
        t_sys_department.post;

    end;

    t_sys_employee.Requery();
    qrytemp2.Close;qrytemp2.sql.clear;
    qrytemp2.sql.Add('select DISTINCT OPNAME from pur_op');
    qrytemp2.open;
    with qrytemp do begin
      close;sql.Clear;
      sql.Add('select * from PUR_OP');
      open;
      ProgressBar1.Max:=RecordCount;
      ProgressBar1.Position:=0;
//      ProgressBar1.Step:=100/RecordCount;
      first;
      while not eof do begin
        if not t_sys_employee.Locate('姓名',fieldbyname('OPNAME').AsString,[]) then begin
        t_sys_employee.Insert;
        if fieldbyname('DEPTID').AsString<>'-1' then begin
          if t_sys_department.Locate('部门代码',fieldbyname('DEPTID').AsString,[]) then
            t_sys_employee['部门名称']:=t_sys_department['部门名称'];
        end else begin
          t_sys_employee['部门名称']:='未设置部门';
        end;
        t_sys_employee['代码']:=fieldbyname('OPNO').AsString;
        t_sys_employee['姓名']:=fieldbyname('OPNAME').AsString;
        t_sys_employee['分部名称']:='总公司';
        t_sys_employee.post;
        
        if fieldbyname('STATUS').AsInteger=0 then begin
          t_sys_code.First;
          while not t_sys_code.eof do begin
            t_sys_code_emp.Insert;
            t_sys_code_emp['权限代码']:=t_sys_code['权限代码'];
            t_sys_code_emp['一级模块']:=t_sys_code['一级模块'];
            t_sys_code_emp['二级模块']:=t_sys_code['二级模块'];
            t_sys_code_emp['权限名称']:=t_sys_code['权限名称'];
            t_sys_code_emp['人员']:=Fieldbyname('OPNAME').AsString;
            t_sys_code_emp['是否可操作']:=1;
            t_sys_code_emp.post;
            t_sys_code.next;
          end;
          memtblDetail.First;
          while not memtblDetail.Eof do begin
            qrytemp2.First;
            while not qrytemp2.Eof do begin
              t_sys_code_data.Insert;
              t_sys_code_data['人员']:=fieldbyname('OPNAME').AsString;
              t_sys_code_data['属性']:=memtblDetail.fieldbyname('属性').AsString;
              t_sys_code_data['是否可视']:=1;
              t_sys_code_data['是否操作']:=1;
              t_sys_code_data['职员']:=qrytemp2.fieldbyname('OPNAME').AsString;
              t_sys_code_data.post;
              qrytemp2.Next;
            end;
            t_sys_code_sql.Insert;
            t_sys_code_sql['人员']:=fieldbyname('OPNAME').AsString;
            t_sys_code_sql['属性']:=memtblDetail.fieldbyname('属性').AsString;
            t_sys_code_sql['可视范围']:='全部';
            t_sys_code_sql.post;
            memtbldetail.next;
          end;

        end else begin
{
          t_sys_code.First;
          while not t_sys_code.eof do begin
            t_sys_code_emp.Insert;
            t_sys_code_emp['权限代码']:=t_sys_code['权限代码'];
            t_sys_code_emp['一级模块']:=t_sys_code['一级模块'];
            t_sys_code_emp['二级模块']:=t_sys_code['二级模块'];
            t_sys_code_emp['权限名称']:=t_sys_code['权限名称'];
            t_sys_code_emp['人员']:=Fieldbyname('OPNAME').AsString;
            t_sys_code_emp['是否可操作']:=0;
            t_sys_code_emp.post;
            t_sys_code.next;
          end;
          memtblDetail.First;
          while not memtblDetail.Eof do begin
            qrytemp2.First;
            while not qrytemp2.Eof do begin
              t_sys_code_data.Insert;
              t_sys_code_data['人员']:=fieldbyname('OPNAME').AsString;
              t_sys_code_data['属性']:=memtblDetail.fieldbyname('属性').AsString;
              t_sys_code_data['是否可视']:=0;
              t_sys_code_data['是否操作']:=0;
              t_sys_code_data['职员']:=qrytemp2.fieldbyname('OPNAME').AsString;
              t_sys_code_data.post;
              qrytemp2.Next;
            end;
            memtbldetail.next;
          end;
}          
        end;
        end;
        ProgressBar1.StepIt;
        Next;
      end;
    end;


    conAdoServer.CommitTrans;
  except
    conAdoServer.RollbackTrans;
  end;


  showmessage('导入完成！');

end;

procedure TForm1.Button8Click(Sender: TObject);
begin
  t_ch_invoice.Open;
  busicard.First;
  while not busicard.Eof do begin
    if busicard.FieldByName('status').AsInteger<>2 then begin
    t_ch_invoice.Insert;
    t_ch_invoice['发票号码']:=busicard.fieldbyname('INVNO').AsString;
    t_ch_invoice['发票抬头']:=busicard.fieldbyname('OBJNAME').AsString;
    t_ch_invoice['开票日期']:=strtodate(FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('INVDATE').AsDatetime));
    t_ch_invoice['开户银行']:=busicard.fieldbyname('BANK').AsString;
    t_ch_invoice['银行账号']:=busicard.fieldbyname('ACCOUNT').AsString;
    t_ch_invoice['船名航次']:=busicard.fieldbyname('VSLVOY').AsString;
    t_ch_invoice['提单号']:=busicard.fieldbyname('MBLNO').AsString;
    if not busicard.fieldbyname('ETD').IsNull then
    t_ch_invoice['开船日期']:=strtodate(FormatDateTime('YYYY-MM-DD',busicard.fieldbyname('ETD').AsDatetime));
    t_ch_invoice['启运港']:=busicard.fieldbyname('PORTLOAD').AsString;
    t_ch_invoice['卸货港']:=busicard.fieldbyname('PORTDIS').AsString;
    t_ch_invoice['目的港']:=busicard.fieldbyname('DESTINATION').AsString;
    t_ch_invoice['备注']:=busicard.fieldbyname('REMARKS').AsString;
    t_ch_invoice['金额大写']:=busicard.fieldbyname('TOTALCAPITAL').AsString;
    t_ch_invoice['币别']:=busicard.fieldbyname('CURRENCY').AsString;
    t_ch_invoice['制单人']:=getemp('结算人',busicard.fieldbyname('OPID').Asstring);
    t_ch_invoice['发票类别']:='自由发票';
    t_ch_invoice['委托编号']:=busicard.fieldbyname('BSNO').Asstring;
    t_ch_invoice['合计金额']:=busicard.fieldbyname('DEBIT').AsFloat;
    t_ch_invoice['虚开金额']:=busicard.fieldbyname('DEBIT').AsFloat;
    invitem.Open;
    invitem.First;
    t_ch_invoice['费用列表']:='';
    t_ch_invoice['金额列表']:='';
    while not invitem.Eof do begin
      t_ch_invoice['费用列表']:=t_ch_invoice['费用列表']+invitem.fieldbyname('DESCRIPTION').AsString+#10#13;
      t_ch_invoice['金额列表']:=t_ch_invoice['金额列表']+floattostr(invitem.fieldbyname('DEBIT').Asfloat)+#10#13;
      invitem.Next;
    end;
    if busicard.FieldByName('status').AsInteger=1 then
      t_ch_invoice['是否废除']:=1
    else
      t_ch_invoice['是否废除']:=0;
    t_ch_invoice['废除日期']:=null;
    t_ch_invoice['申请日期']:=null;
    t_ch_invoice['复核日期']:=null;
    t_ch_invoice['是否打印']:=1;
    t_ch_invoice['自动']:=1;
    t_ch_invoice['分部名称']:='总公司';

    t_ch_invoice.post;
    end;
    busicard.Next;
  end;
  showmessage('导入完成！');
end;

end.