unit pfm_querykcsp;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, Grids, DBGrids, Db, DBTables,pvar_lib, pfm_selgridcol;

type
  Tfm_querykcsp = class(TForm)
    GroupBox1: TGroupBox;
    CbBox1: TComboBox;
    BBtnset: TBitBtn;
    GroupBox2: TGroupBox;
    Query1: TQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    QryName: TQuery;
    BBtnclear: TBitBtn;
    BBtnSelV: TBitBtn;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure BBtnsetClick(Sender: TObject);
    procedure CbBox1Change(Sender: TObject);
    procedure BBtnclearClick(Sender: TObject);
    procedure DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect;
      Field: TField; State: TGridDrawState);
    procedure BBtnSelVClick(Sender: TObject);
  private
    { Private declarations }
    procedure OpenKcsp();
  public
    { Public declarations }
  end;

var
  fm_querykcsp: Tfm_querykcsp;

implementation

uses pfm_query;

{$R *.DFM}
var
gridcolname:Tstringlist;
mysql1:string;
qrysql:string;
visiblecol:Tstringlist;

procedure Tfm_querykcsp.FormCreate(Sender: TObject);
var
i:integer;
begin
//往中加入倉庫
    Qryname.close;
    Qryname.SQL.Clear;
    Qryname.SQL.Add('select * from t_ckxx');
    Qryname.Open;

    cbbox1.Items.add('全部倉庫');
    while not Qryname.Eof do
    begin
        cbBox1.items.add(Qryname.fieldbyname('ckmc').asstring);
        Qryname.Next;
    end;
    Qryname.close;
    Qryname.SQL.Clear;
    Qryname.SQL.Add('select * from t_fendianxx');
    Qryname.Open;

    cbbox1.items.add('全部分店');
    while not Qryname.Eof do
    begin
        cbbox1.items.add(Qryname.fieldbyname('fdmc').asstring);
        Qryname.Next;
    end;
    gridcolname:=tstringlist.create;
    visiblecol:=tstringlist.create;
    QryName.Active:=false;
    qryname.sql.clear;
    qryname.sql.add('select * from tab_chinesename where tablename=''t_spxx'' or tablename=''t_spstock''');
    qryname.active:=true;
    while not qryname.eof do
    begin
        gridcolname.add(qryname.fieldbyname('englishname').asstring+'='+
               qryname.fieldbyname('chinesename').asstring);
        qryname.next;
    end;
    qryname.active:=false;
    query1.sql.clear;
    query1.sql.add('select * from v_kcsp where 1=2');
    query1.active:=true;
    for i:=0 to dbgrid1.columns.count-1 do
    begin
        visiblecol.add(dbgrid1.columns[i].FieldName+'=1');
        dbgrid1.columns[i].Title.caption:=GridColName.values[dbgrid1.columns[i].Title.caption];
        if (dbgrid1.Columns[i].fieldname='spnum') or (dbgrid1.Columns[i].fieldname='spnummin') then
            dbgrid1.columns[i].font.Color:=clGreen;
    end;
    mysql1:='';
    qrysql:='2=2';

end;

procedure Tfm_querykcsp.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
    gridcolname.free;
    visiblecol.free;
    action:=cafree;
end;

procedure Tfm_querykcsp.BBtnsetClick(Sender: TObject);
var
i:integer;
begin
    for i:=0 to dbgrid1.Columns.Count-1 do
    begin
        mFieldCap[i]:=dbgrid1.Columns[i].Title.Caption;
        mFieldName[i]:=dbgrid1.Columns[i].fieldname;
        mFieldType[i]:=dbgrid1.Columns[i].Field.datatype;
        mFieldNum:=i;
    end;

    fm_query:=Tfm_query.Create(self);
    if fm_query.ShowModal=mrOK then
    begin
        qrysql:=sQuerySql;
        OpenKcsp();
    end;

end;

procedure Tfm_querykcsp.CbBox1Change(Sender: TObject);
var
i:integer;
begin
    if cbbox1.ItemIndex<0 then
    begin
        showmessage('請選擇倉庫或分店名稱');
        exit;
    end;
    if (cbbox1.text='全部倉庫') then
    begin
        mysql1:=' v_kcsp.ckmc in (select ckmc from t_ckxx) and ';
    end else if cbbox1.text='全部分店' then
    begin
        mysql1:=' v_kcsp.ckmc in (select fdmc from t_fendianxx) and ';
    end
    else
    begin
        mysql1:=' ckmc='''+cbbox1.text+''' and ';
    end;
    OpenKcsp();

end;

procedure Tfm_querykcsp.BBtnclearClick(Sender: TObject);

begin
    qrysql:='2=2';
    OpenKcsp();
end;
procedure Tfm_querykcsp.OpenKcsp();
var
i:integer;
begin
    query1.active:=false;
    query1.sql.clear;
    query1.sql.add('select * from v_kcsp where '+mysql1+qrysql);
    query1.active:=true;
    for i:=0 to dbgrid1.columns.count-1 do
    begin
        if visiblecol.values[dbgrid1.Columns[i].fieldname]='1' then
        begin
            dbgrid1.columns[i].visible:=true;
        end
        else
        begin
            dbgrid1.columns[i].visible:=false;
        end;
        dbgrid1.columns[i].Title.caption:=GridColName.values[dbgrid1.columns[i].Title.caption];
        if (dbgrid1.Columns[i].fieldname='spnum') or (dbgrid1.Columns[i].fieldname='spnummin') then
        begin
            dbgrid1.columns[i].font.Color:=clGreen;
            dbgrid1.columns[i].font.Style:=dbgrid1.columns[i].font.Style + [fsbold];
        end;

    end;
end;
procedure Tfm_querykcsp.DBGrid1DrawDataCell(Sender: TObject;
  const Rect: TRect; Field: TField; State: TGridDrawState);
begin
    if (query1.fieldbyname('spnum').asinteger<query1.fieldbyname('spnummin').asinteger) and (not(gdselected  in state)) then
    begin
        (sender as tdbgrid).Canvas.Brush.Color:=RGB(219,253,253);
        (sender as tdbgrid).Canvas.font.Color:=clred;
    end
    else if (query1.fieldbyname('spnum').asinteger>query1.fieldbyname('spnummax').asinteger) and (not(gdselected in state)) then
    begin
        (sender as tdbgrid).Canvas.Brush.Color:=RGB(229,253,253);
        (sender as tdbgrid).Canvas.font.Color:=clpurple;
    end;
     (sender as tdbgrid).DefaultDrawDataCell(Rect, Field, State);
end;

procedure Tfm_querykcsp.BBtnSelVClick(Sender: TObject);
var
i:integer;
begin
    Application.CreateForm(Tfm_selgridcol, fm_selgridcol);
    fm_selgridcol.GRID1:=dbgrid1;
    fm_selgridcol.showmodal;
    fm_selgridcol.Free;
    for i:=0 to dbgrid1.columns.count-1 do
    begin
        if dbgrid1.Columns[i].visible=true then
        begin
            visiblecol.values[dbgrid1.Columns[i].fieldname]:='1';
        end
        else
        begin
            visiblecol.values[dbgrid1.Columns[i].fieldname]:='0';
        end;
     end;
end;

end.