DONGSHENG6/海运业务/u_op_crm_detail_modify.pas

unit u_op_crm_detail_modify;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dxExEdtr, dxCntner, dxTL, dxDBCtrl, dxDBGrid, DB, ADODB,u_crm_client_sales,
  dxDBTLCl, dxGrClms, StdCtrls;

type
  Tfrm_op_crm_detail_modify = class(TForm)
    dxDBGrid1: TdxDBGrid;
    t_crm_detail: TADOQuery;
    ds_crm_detail: TDataSource;
    dxDBGrid1Column1: TdxDBGridColumn;
    dxDBGrid1Column2: TdxDBGridCheckColumn;
    procedure FormShow(Sender: TObject);
    procedure dxDBGrid1Click(Sender: TObject);
  private
    { Private declarations }
    procedure InitData;
    procedure GetSaleDetail;
    procedure GetCdDetail;
    procedure GetKfDetail;
    procedure GetHxDetail;
    procedure CheckNull;
  public
    { Public declarations }
    //要修改的类型，根据frm_crm_client_sales里面memo 的不同确定哪种字段
    ModifyType : Integer;
  end;

var
  frm_op_crm_detail_modify: Tfrm_op_crm_detail_modify;
  

implementation
  uses u_main, my_sys_function, u_data_share, my_sys_chat;
{$R *.dfm}

procedure  Tfrm_op_crm_detail_modify.InitData;
var
    aQuery:TADOQuery;
    s,str,dm:string;
begin
   aQuery:=CreateAdoQuery;
   if ModifyType=1 then
   begin
     with aQuery do
     begin
       Close;
       SQL.Clear;
       SQL.Add('select 出货方向 from t_crm_client where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     s:=aQuery.fieldbyname('出货方向').AsString;
   end;
   if ModifyType=2 then
   begin
     with aQuery do
     begin
       Close;
       SQL.Clear;
       SQL.Add('select 合作船东 from t_crm_client where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     s:=aQuery.fieldbyname('合作船东').AsString;
   end;
   if ModifyType=3 then
   begin
     with aQuery do
     begin
       Close;
       SQL.Clear;
       SQL.Add('select 主营产品 from t_crm_client where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     s:=aQuery.fieldbyname('主营产品').AsString;
   end;
  { if ModifyType=4 then
   begin
     with aQuery do
     begin
       Close;
       SQL.Clear;
       SQL.Add('select 特色航线 from t_crm_client where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     s:=aQuery.fieldbyname('特色航线').AsString;
   end;   }
   FreeAndNil(aQuery);
   dm:='(';
   while Trim(s)<>'' do
   begin
     str:=Copy(s,0,Pos(',',s)-1);
     s:=Copy(s,Pos(',',s)+1,Length(s)-Pos(',',s));
     if Trim(s)<>'' then  dm:=dm+''''+str+''',' else dm:=dm+''''+str+'''' ;
   end;
   dm:=dm+')';
   t_crm_detail.Close;
   t_crm_detail.SQL.Clear;

   if ModifyType=1 then
   begin
     if dm<>'()' then
       t_crm_detail.SQL.Add('select *, cast(case when 航线 in (select 航线 from #Outport where 航线 in '+dm
                          +' ) then 1 else 0 end as bit) Checked  from #Outport')
     else
       t_crm_detail.SQL.Add('select *, cast(case when 航线 in (select 航线 from #Outport where 航线='''' '
                          +' ) then 1 else 0 end as bit) Checked  from #Outport');
   end;
   if ModifyType=2 then
   begin
     if dm<>'()' then
       t_crm_detail.SQL.Add('select *, cast(case when 客户简称 in (select 客户简称 from #HZCD where   客户简称 in '+dm
                          +' ) then 1 else 0 end as bit) Checked  from #HZCD')
     else
       t_crm_detail.SQL.Add('select *, cast(case when 客户简称 in (select 客户简称 from #HZCD where 客户简称='''' '
                          +' ) then 1 else 0 end as bit) Checked  from #HZCD');
   end;
   if ModifyType=3 then
   begin
     if dm<>'()' then
       t_crm_detail.SQL.Add('select *, cast(case when 货物名称 in (select 货物名称 from t_code_goods where 货物名称 in '+dm
                          +' ) then 1 else 0 end as bit) Checked  from t_code_goods')
     else
       t_crm_detail.SQL.Add('select *, cast(case when 货物名称 in (select 货物名称 from t_code_goods where 货物名称='''' '
                          +' ) then 1 else 0 end as bit) Checked  from t_code_goods');
   end;
   t_crm_detail.Open;
end;

procedure Tfrm_op_crm_detail_modify.FormShow(Sender: TObject);
begin
  if ModifyType=1 then
  begin
    dxDBGrid1.Columns[0].FieldName:='Checked';
    dxDBGrid1.Columns[0].Caption:='是否选中';
    dxDBGrid1.Columns[1].FieldName:='航线';
    dxDBGrid1.KeyField:='航线代码';
  end;
  if ModifyType=2 then
  begin
    dxDBGrid1.Columns[0].FieldName:='Checked';
    dxDBGrid1.Columns[0].Caption:='是否选中';
    dxDBGrid1.Columns[1].FieldName:='客户简称';
    dxDBGrid1.KeyField:='序号';
  end ;
  if ModifyType=3 then
  begin
    dxDBGrid1.Columns[0].FieldName:='Checked';
    dxDBGrid1.Columns[0].Caption:='是否选中';
    dxDBGrid1.Columns[1].FieldName:='货物名称';
    dxDBGrid1.KeyField:='代码';
  end;
  InitData;
end;

procedure Tfrm_op_crm_detail_modify.CheckNull;
var
  aQuery:TADOQuery;
begin
  aQuery:=CreateAdoQuery;
  with aQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('update t_crm_client set 出货方向='''' where 出货方向 is null and  代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    ExecSQL;
    Close;
    SQL.Clear;
    SQL.Add('update t_crm_client set 合作船东='''' where 合作船东 is null and  代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    ExecSQL;
    Close;
    SQL.Clear;
    SQL.Add('update t_crm_client set 主营产品='''' where 主营产品 is null and  代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    ExecSQL;
   { Close;
    SQL.Clear;
    SQL.Add('update t_crm_client set 特色航线='''' where 特色航线 is null and  代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    ExecSQL;  }
  end;
  FreeAndNil(aQuery);
end;

//出货方向
procedure Tfrm_op_crm_detail_modify.GetSaleDetail;
var
  s,str:string;
  Query,aQuery:TADOQuery;
begin
  frm_crm_client_sales.Memo1.Lines.Clear;
  Query:=CreateAdoQuery;
  aQuery:=CreateAdoQuery;
  with Query do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select 出货方向 from t_crm_client where 代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    Open;
  end;
  s:=Query.fieldbyname('出货方向').AsString;
  str:='';
  while  (Trim(s)<>'') do
  begin
    str:=Copy(s,0,Pos(',',s)-1);
    aQuery.Close;
    aQuery.SQL.Clear;                               // 航线代码
    aQuery.SQL.Add('select * from #Outport  where 航线='+QuotedStr(str) ) ;
    aQuery.Open;
    if not  Query.IsEmpty then frm_crm_client_sales.Memo1.Lines.Add(aQuery.fieldbyname('航线').AsString);
    s:=Copy(s,Pos(',',s)+1,Length(s)-Pos(',',s));
  end;
  FreeAndNil(Query);
  FreeAndNil(aQuery);
end;

//合作船东
procedure Tfrm_op_crm_detail_modify.GetCdDetail;
var
  s,str:string;
  Query,aQuery:TADOQuery;
begin
  frm_crm_client_sales.Memo2.Lines.Clear;
  Query:=CreateAdoQuery;
  aQuery:=CreateAdoQuery;
   with Query do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select 合作船东 from t_crm_client where 代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    Open;
  end;
  s:=Query.fieldbyname('合作船东').AsString;
  str:='';
  while  (Trim(s)<>'') do
  begin
    str:=Copy(s,0,Pos(',',s)-1);
    aQuery.Close;
    aQuery.SQL.Clear;                           // 代码
    aQuery.SQL.Add('select * from #HZCD where   客户简称='+QuotedStr(str) ) ;
    aQuery.Open;
    if not  aQuery.IsEmpty then frm_crm_client_sales.Memo2.Lines.Add(aQuery.fieldbyname('客户简称').AsString);
    s:=Copy(s,Pos(',',s)+1,Length(s)-Pos(',',s));
  end;
  FreeAndNil(Query);
  FreeAndNil(aQuery);
end;

//主营产品
procedure Tfrm_op_crm_detail_modify.GetKfDetail;
var
  s,str:string;
  Query,aQuery:TADOQuery;
begin
  frm_crm_client_sales.Memo3.Lines.Clear;
  Query:=CreateAdoQuery;
  aQuery:=CreateAdoQuery;
  with Query do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select 主营产品 from t_crm_client where 代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    Open;
  end;
  s:=Query.fieldbyname('主营产品').AsString;
  str:='';
  while  (Trim(s)<>'') do
  begin
    str:=Copy(s,0,Pos(',',s)-1);
    aQuery.Close;
    aQuery.SQL.Clear;                                 // 代码
    aQuery.SQL.Add('select * from t_code_goods where 货物名称='+QuotedStr(str) ) ;
    aQuery.Open;
    if not  aQuery.IsEmpty then frm_crm_client_sales.Memo3.Lines.Add(aQuery.fieldbyname('货物名称').AsString);
    s:=Copy(s,Pos(',',s)+1,Length(s)-Pos(',',s));
  end;
  FreeAndNil(Query);
  FreeAndNil(aQuery);
end;

//特色航线
procedure Tfrm_op_crm_detail_modify.GetHxDetail;
var
  s,str:string;
  Query,aQuery:TADOQuery;
begin
 { frm_crm_client_sales.Memo4.Lines.Clear;
  Query:=CreateAdoQuery;
  aQuery:=CreateAdoQuery;
  with Query do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select 特色航线 from t_crm_client where 代码='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('代码').AsString) );
    Open;
  end;
  s:=Query.fieldbyname('特色航线').AsString;
  str:='';
  while  (Trim(s)<>'') do
  begin
    str:=Copy(s,0,Pos(',',s)-1);
    aQuery.Close;
    aQuery.SQL.Clear;
    aQuery.SQL.Add('select * from t_code_trade where 航线代码='+QuotedStr(str) ) ;
    aQuery.Open;
    if not  aQuery.IsEmpty then frm_crm_client_sales.Memo4.Lines.Add(aQuery.fieldbyname('航线').AsString);
    s:=Copy(s,Pos(',',s)+1,Length(s)-Pos(',',s));
  end;
  FreeAndNil(Query);
  FreeAndNil(aQuery);   }
end;

procedure Tfrm_op_crm_detail_modify.dxDBGrid1Click(Sender: TObject);
  var
    aQuery:TADOQuery;
    s,str,dm:string;
    i:Integer;
begin
   CheckNull;
   aQuery:=CreateAdoQuery;
   if ModifyType=1 then
   begin
     s:= t_crm_detail.fieldbyname('航线').AsString+',' ;
     with aQuery do
     begin
       Close;
       SQL.Clear;
       if t_crm_detail.FieldByName('checked').AsBoolean  then
       begin
         SQL.Add('update t_crm_client set 出货方向=replace(出货方向,'''+s+''','''')  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString))
       end
       else
       begin
         SQL.Add('update t_crm_client set 出货方向=出货方向+'''+t_crm_detail.fieldbyname('航线').AsString+',''  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       end;
       ExecSQL;
       Close;
       SQL.Clear;
       SQL.Add('select 出货方向 from t_crm_client  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     GetSaleDetail;
   end;
   if ModifyType=2 then
   begin
     s:= t_crm_detail.fieldbyname('客户简称').AsString+',' ;
     with aQuery do
     begin
       Close;
       SQL.Clear;
       if t_crm_detail.FieldByName('checked').AsBoolean  then
       begin
         SQL.Add('update t_crm_client set 合作船东=replace(合作船东,'''+s+''','''')  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString))
       end
       else
       begin
         SQL.Add('update t_crm_client set 合作船东=合作船东+'''+t_crm_detail.fieldbyname('客户简称').AsString+',''  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       end;
       ExecSQL;
       Close;
       SQL.Clear;
       SQL.Add('select 合作船东 from t_crm_client  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     GetCdDetail;
   end;
   if ModifyType=3 then
   begin
     s:= t_crm_detail.fieldbyname('货物名称').AsString+',' ;
     with aQuery do
     begin
       Close;
       SQL.Clear;
       if t_crm_detail.FieldByName('checked').AsBoolean  then
       begin
         SQL.Add('update t_crm_client set 主营产品=replace(主营产品,'''+s+''','''')  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString))
       end
       else
       begin
         SQL.Add('update t_crm_client set 主营产品=主营产品+'''+t_crm_detail.fieldbyname('货物名称').AsString+',''  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       end;
       ExecSQL;
       Close;
       SQL.Clear;
       SQL.Add('select 主营产品 from t_crm_client  where 序号='+QuotedStr(frm_crm_client_sales.t_crm_client.fieldbyname('序号').AsString));
       Open;
     end;
     GetKfDetail;
   end;
    FreeAndNil(aQuery);
   InitData;
end;



end.