unit U_StuffHelp_db;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxCustomData, cxStyles, cxTL, cxMaskEdit, cxFilter,
  cxData, cxDataStorage, cxEdit, DB, cxDBData, StdCtrls, cxGridLevel,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxClasses,
  cxControls, cxGridCustomView, cxGrid, cxInplaceContainer, cxDBTL,
  cxTLData, ComCtrls, ToolWin, ADODB, ImgList,StrUtils, ExtCtrls, DBClient,
  Menus, cxGridCustomPopupMenu, cxGridPopupMenu;

type
  TfrmStuffHelp_db = class(TForm)
    ToolBar2: TToolBar;
    TOk: TToolButton;
    TBClose: TToolButton;
    cxDBTreeList1: TcxDBTreeList;
    cxDBTreeList1cxDBTreeListColumn2: TcxDBTreeListColumn;
    cxGrid1: TcxGrid;
    tv1: TcxGridDBTableView;
    tv1Code: TcxGridDBColumn;
    tv1Name: TcxGridDBColumn;
    DBGrid1Level1: TcxGridLevel;
    GroupBox1: TGroupBox;
    ADOQueryDept: TADOQuery;
    DataSource1: TDataSource;
    ADOConnection1: TADOConnection;
    cxStyleRepository1: TcxStyleRepository;
    cxStyle1: TcxStyle;
    cxStyle2: TcxStyle;
    cxStyle_gridRow: TcxStyle;
    cxStyle_gridFoot: TcxStyle;
    cxStyle_gridHead: TcxStyle;
    cxStyle_gridGroupBox: TcxStyle;
    cxStyle_yellow: TcxStyle;
    cxStyle_Red: TcxStyle;
    ADOQueryHelp: TADOQuery;
    DataSource2: TDataSource;
    Label1: TLabel;
    Edt_name: TEdit;
    ImageList1: TImageList;
    cxStyleTree: TcxStyle;
    Label2: TLabel;
    Edt_Code: TEdit;
    Splitter1: TSplitter;
    DS_sel: TDataSource;
    CDS_sel: TClientDataSet;
    cxGrid2: TcxGrid;
    tv2: TcxGridDBTableView;
    cxGridDBColumn1: TcxGridDBColumn;
    cxGridDBColumn2: TcxGridDBColumn;
    cxGridDBColumn3: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    CDS_selcode: TStringField;
    CDS_selName: TStringField;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    tv1gwName: TcxGridDBColumn;
    tv1duties: TcxGridDBColumn;
    tv1kqDeptName: TcxGridDBColumn;
    cxGridPopupMenu1: TcxGridPopupMenu;
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure TBCloseClick(Sender: TObject);
    procedure TOkClick(Sender: TObject);
    procedure tv1DblClick(Sender: TObject);
    procedure Edt_nameChange(Sender: TObject);
    procedure cxDBTreeList1Click(Sender: TObject);
    procedure tv2DblClick(Sender: TObject);
    procedure N1Click(Sender: TObject);
  private
    procedure InitTree();
    procedure DoQuery();
    procedure FilterData(strdepotno,strcode,strname:string);
    procedure InitTree20();
  public
    fFrameNo:string;
    fFrameName:string;
    fIsYwy:boolean;
    fWhereStr:string;
  end;

var
  frmStuffHelp_db: TfrmStuffHelp_db;

implementation
uses
  U_global;
{$R *.dfm}
///////////////////////////////////////////////////////////
//////功能：过滤数据
///////////////////////////////////////////////////////////
procedure TfrmStuffHelp_db.FilterData(strdepotno,strcode,strname:string);
var
  filterStr:string;
begin
  filterStr:='';
  if trim(strdepotno)<>'' then
  filterStr:=filterStr+' and dept like '+quotedstr(trim(strdepotno)+'%');
  if trim(strcode)<>'' then
  filterStr:=filterStr+' and code like '+quotedstr('%'+trim(strcode)+'%');
  if trim(strname)<>'' then
  filterStr:=filterStr+' and name like '+quotedstr('%'+trim(strname)+'%');

  try
    ADOQueryHelp.DisableControls ;
    if trim(filterStr)='' then
    begin
      ADOQueryHelp.Filtered:=false;
      ADOQueryHelp.EnableControls;
      exit;
   end;
    filterStr:=trim(RightBStr(filterStr,length(filterStr)-5));
   // showmessage(filterStr);
    with ADOQueryHelp do
    begin
      filtered:=false;
      filter:=filterStr;
      filtered:=true;
    end;
   finally
    ADOQueryHelp.EnableControls;
  end;
end;
//////////////////////////////////////////////////////
procedure TfrmStuffHelp_db.InitTree();
var
  strsql,strwhere:string;
begin
  strsql:=' SELECT distinct frameNo,frameTop,frameName,Framelevel   '+
          ' from  YC_frame                                          '+
          ' where Framelevel=''0'' and  Valid=''Y''                 '+
          ' union                                                   '+
          ' SELECT distinct B.frameNo,B.frameTop,B.frameName,Framelevel '+
          ' FROM YC_stuff A                                             '+
          ' INNER JOIN YC_frame B on A.dept=B.frameNo and B.Valid=''Y'' ';

  strwhere:=' where A.Valid=''Y''  ';
  if fIsYwy then
  strwhere:=strwhere+' and A.YWYFlag=''Y''       ';
  if trim(fFrameNo)<>'' then
  strwhere:=strwhere+' and A.dept='+quotedstr(trim(fFrameNo));
  if trim(fFrameName)<>'' then
  strwhere:=strwhere+' and B.frameName like '+quotedstr('%'+trim(fFrameName)+'%');
  strsql:=strsql+ strwhere;

  with ADOQueryDept do
  begin
    close;
    sql.Clear ;
    sql.Add( strsql);
    Open;
  end;
  cxDBTreeList1.FullExpand;

  DoQuery();
end;
procedure TfrmStuffHelp_db.InitTree20();
var
  strsql,strwhere:string;
begin

  strsql:=' select * from Yc_Frame order by framelevel  ';
  if trim(fFrameNo)<>'' then
  strwhere:=strwhere+' and frameno='+quotedstr(trim(fFrameNo));
  if trim(fFrameName)<>'' then
  strwhere:=strwhere+' and frameName like '+quotedstr('%'+trim(fFrameName)+'%');
  strsql:=strsql+ strwhere;

  with ADOQueryDept do
  begin
    close;
    sql.Clear ;
    sql.Add( strsql);
    Open;
  end;
  cxDBTreeList1.Items[0].Expand(false);

  DoQuery();
end;

procedure TfrmStuffHelp_db.FormShow(Sender: TObject);
begin
  //InitTree();
  InitTree20();
end;

procedure TfrmStuffHelp_db.FormCreate(Sender: TObject);
begin
  cxGrid2.Align :=alclient;
  with ADOConnection1 do
  begin
    Connected:=false;
    ConnectionString:=gConString;
    Connected:=true;
  end;
  cds_sel.Close;
  cds_sel.CreateDataSet ;
end;
//////////////////////////////////////////////////
  //
//////////////////////////////////////////////////
procedure TfrmStuffHelp_db.DoQuery();
var
  strwhere:string;
begin
  if fIsYwy then
    strwhere:=strwhere+' and A.YWYFlag=''Y''';
  if trim(fFrameNo)<>'' then
    strwhere:=strwhere+' and A.dept='+quotedstr(trim(fFrameNo));
  try
    ADOQueryHelp.DisableControls ;
    with ADOQueryHelp do
    begin
      close;
      sql.Clear ;
      sql.Add('select A.code,A.Name,A.dept,A.ywyflag,A.duties,'); //A.workerPostion,
      sql.Add('kqDeptName=(select Name from XC_Custcode where code=B.kqdept),');
      sql.Add('gwName=(select name from xc_custcode where code=A.gangwei and flag=''gangwei'')');
      sql.Add('from YC_stuff A');
      sql.Add('left join YC_kqDeptStuff B on A.code=B.userId');
      sql.Add('where A.Valid=''Y''');
      sql.Add(strwhere);
      sql.Add(fWhereStr);
      sql.Add('and A.Name<>'''''); 
      sql.Add('order by A.Code');
      Open;
    end;
  finally
    ADOQueryHelp.EnableControls ;
  end;
end;

procedure TfrmStuffHelp_db.TBCloseClick(Sender: TObject);
begin
  close;
end;

procedure TfrmStuffHelp_db.TOkClick(Sender: TObject);
begin
  if ADOQueryHelp.IsEmpty then
  begin
    ModalResult:=-1
  end
  else
    ModalResult:=1;
end;

procedure TfrmStuffHelp_db.tv1DblClick(Sender: TObject);
begin
  with CDS_sel do
  begin
    if locate('code', trim(ADOQueryHelp.fieldByName('code').AsString),[]) then exit;
    Append;
    fieldByName('code').Value :=trim(ADOQueryHelp.fieldByName('code').AsString);
    fieldByName('Name').Value :=trim(ADOQueryHelp.fieldByName('Name').AsString);
    Post;
  end;
end;

procedure TfrmStuffHelp_db.Edt_nameChange(Sender: TObject);
begin
  FilterData('',edt_code.Text,Edt_name.Text);
end;

procedure TfrmStuffHelp_db.cxDBTreeList1Click(Sender: TObject);
begin
  if ADOQueryDept.IsEmpty then exit;
  if trim(ADOQueryDept.FieldByName('Framelevel').AsString)='0' then
    FilterData('','','')
  else
    FilterData(ADOQueryDept.FieldByName('Frameno').AsString,'','');
end;

procedure TfrmStuffHelp_db.tv2DblClick(Sender: TObject);
begin
  CDS_sel.Delete ;
end;

procedure TfrmStuffHelp_db.N1Click(Sender: TObject);
begin
  try
    ADOQueryHelp.DisableControls ;
    CDS_sel.DisableControls ;
    ADOQueryHelp.First ;
    while not ADOQueryHelp.Eof do
    begin
      with CDS_sel do
      begin
        if locate('code', trim(ADOQueryHelp.fieldByName('code').AsString),[]) then
        begin
          ADOQueryHelp.Next ;
          continue;
        end;
        Append;
        fieldByName('code').Value :=trim(ADOQueryHelp.fieldByName('code').AsString);
        fieldByName('Name').Value :=trim(ADOQueryHelp.fieldByName('Name').AsString);
        Post;
      end;
      ADOQueryHelp.Next ;
    end;
  finally
    ADOQueryHelp.EnableControls;
    CDS_sel.EnableControls ;
  end;
end;

end.