{*******************************************************}
{                                                       }
{                   庫存及明細帳查詢                    }
{                                                       }
{            中軟金馬公司版權所有。2002.12前            }
{                                                       }
{               編制：中軟金馬項目開發組                }
{                                                       }
{                                                       }
{*******************************************************}
(*
本模塊在省級庫房管理模塊 集郵票、零枚票、集郵品、市場購入票四部分的庫存及明細帳查詢菜單 調用
*)

unit SY_kcmxcx;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Buttons, ExtCtrls, StdCtrls, ActnList, Db, DBTables,
  ComCtrls, RXCtrls, sqlstrings, DBGridExport, Menus, Mask, ToolEdit;

type
  TFrmY_kcmxcx = class(TForm)
    GroupBox1: TGroupBox;
    DBGid_kc: TDBGrid;
    CBo_kfmc: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    CBo_ppmc: TComboBox;
    BitBtn1: TBitBtn;
    ActionList1: TActionList;
    Act_sel: TAction;
    Act_rtn: TAction;
    Qry_kcmx: TQuery;
    DS_kc: TDataSource;
    Query1: TQuery;
    CheckBox1: TCheckBox;
    Label3: TLabel;
    Label4: TLabel;
    BitBtn2: TBitBtn;
    Label5: TLabel;
    Edit1: TEdit;
    Panel2: TPanel;
    XttxTitle: TRxLabel;
    DWMC: TLabel;
    Panel3: TPanel;
    SqlStrings1: TSqlStrings;
    PM_Exp: TPopupMenu;
    MI_Exc: TMenuItem;
    N2: TMenuItem;
    MI_txt: TMenuItem;
    DBGE_rkd: TDBGridExport;
    d1: TDateEdit;
    d2: TDateEdit;
    procedure Act_selExecute(Sender: TObject);
    procedure Act_rtnExecute(Sender: TObject);
    procedure CBo_kfmcChange(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure Edit1Change(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure MI_ExcClick(Sender: TObject);
    procedure MI_txtClick(Sender: TObject);
    procedure FormKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Qry_kcmxAfterOpen(DataSet: TDataSet);
  private
    { Private declarations }
    vlp_sl_kfdm: TStringList;
    vlp_sl_ppdm: TStringList;
    Fio_Type: TIO_TYPE;
  public
    { Public declarations }
  end;

function ExecuteKCMX(io_typ: TIO_TYPE; const DB: TDatabase): boolean;

var
  FrmY_kcmxcx: TFrmY_kcmxcx;

implementation
uses  datas,Pub;

{$R *.DFM}

function ExecuteKCMX(io_typ: TIO_TYPE; const DB: TDatabase): boolean;
var
  year, month, day: Word;
begin
  Result := false;
  if not Assigned(DB) then
    exit;

  Application.CreateForm(TfrmY_kcmxcx, frmY_kcmxcx);
  with frmY_kcmxcx do
  try
    FIO_type := io_typ;
    Caption := '郵資票品管理信息系統——庫房管理';
    XttxTitle.Caption := pCaption[integer(FIO_type)] + '庫存及明細帳查詢';
    DWMC.Caption := '使用單位：' + VG_UnitName;
    if FIO_type = io_yp then
      DBGid_kc.Columns[1].FieldName := 'MC'
    else
      DBGid_kc.Columns[1].FieldName := 'TMC';

    CBo_kfmc.Items.Clear;
    CBo_kfmc.Items.Add('全部');
    if Assigned(vlp_sl_kfdm) then
      vlp_sl_kfdm.Destroy;
    vlp_sl_kfdm := TStringList.Create;
    vlp_sl_kfdm.Add('a.KFDM');
    CBo_kfmc.ItemIndex := 0;
    DecodeDate(GetSysDate, year, month, day);
    d1.Date := strToDate(Format('%u-%u-01', [year, month]));
    d2.DateTime := GetSysDate;
    try
      Qry_kcmx.Close;
      Qry_kcmx.DatabaseName := DB.DatabaseName;
      with Query1 do
      begin
        Close;
        DatabaseName := DB.DatabaseName;
        SQL.Clear;
        SQL.Add('select a.KFDM,b.KFMC from TGS_KFRYDZB a, TGS_KFB b ' +
          'where a.KFDM=b.KFDM and a.YGDM=''' + VG_USERID + ''' order by a.KFDM');
        Open;

        while not Eof do
        begin
          CBo_kfmc.Items.Add(FieldByName('KFMC').AsString);
          vlp_sl_kfdm.Add('''' + FieldByName('KFDM').AsString + '''');
          Next;
        end;
        CBo_kfmc.ItemIndex := 0;
        CBo_kfmcChange(nil);
        CheckBox1Click(nil);
      end;
      Result := ShowModal = MB_OK;
    except
      CHQMsgBox('不能打開 <' + pCaption[integer(FIO_type)] + '庫存明細分類帳信息表>');
      exit;
    end;
  finally
    free;
  end;
end;        

procedure TFrmY_kcmxcx.Act_selExecute(Sender: TObject);
const
  C_S_JYPSQL = 'select a.ppdm,a.kfdm,a.crkdh,a.zi,a.rq,a.zy,a.jssl,a.zjsl,a.jcsl,a.zbr,a.sdate,b.kfmc,c.tmc, c.zh ' +
    'from TKS_JYPMXFLZ a, TGS_KFB b, TB_YZPPXXB c ';
  C_S_TXPSQL = 'select a.ppdm,a.kfdm,a.crkdh,a.zi,a.rq,a.zy,a.jssl,a.zjsl,a.jcsl,a.zbr,a.sdate,b.kfmc,c.tmc, c.zh ' +
    'from tks_txpmxflz a, TGS_KFB b, TB_YZPPXXB c ';
  C_S_YPSQL = 'select a.ppdm,a.kfdm,a.crkdh,a.zi,a.rq,a.zy,a.jssl,a.zjsl,a.jcsl,a.zbr,a.sdate,b.kfmc,c.mc, c.zh ' +
    'from TKS_YPMXFLZ a, TGS_KFB b, TB_JYPTXXB c ';
  C_S_GRPSQL = 'select a.ppdm,a.kfdm,a.crkdh,a.zi,a.rq,a.zy,a.jssl,a.zjsl,a.jcsl,a.zbr,a.sdate,b.kfmc,c.tmc, c.zh ' +
    'from TKS_GRPMXFLZ a, TGS_KFB b, TB_YZPPXXB c ';
  C_S_Pub = 'where a.kfdm=b.kfdm and a.ppdm=c.tdm ' +
    ' and a.kfdm=%s and a.ppdm=%s ' +
    ' and a.KFDM in (select KFDM from TGS_KFRYDZB where YGDM=%s)';
var
  sqlstr: string;
begin
  with Qry_kcmx do
  begin
    Close;
    SQL.Clear;
    case FIO_type of
      io_jyp:
        begin
          sqlstr := C_S_JYPSQL + C_S_Pub;
        end;
      io_txp:
        begin
          sqlstr := C_S_TXPSQL + C_S_Pub;
        end;
      io_yp:
        begin
          sqlstr := C_S_YPSQL + C_S_Pub;
        end;
      io_grp:
        begin
          sqlstr := C_S_GRPSQL + C_S_Pub;
        end;
    end;
    sqlstr := format(sqlstr, [vlp_sl_kfdm[CBo_kfmc.ItemIndex], vlp_sl_ppdm[CBo_ppmc.ItemIndex], '''' + VG_USERID + '''']);

    if CheckBox1.Checked then
      sqlstr := sqlstr + ' and a.rq >= to_date(''' + FormatDateTime('yyyy-mm-dd', d1.DateTime) + ''',''yyyy-mm-dd'') ' +
        ' and a.rq <= to_date(''' + FormatDateTime('yyyy-mm-dd', d2.DateTime + 1) + ''',''yyyy-mm-dd'')';
    sqlstr := sqlstr + ' order by a.kfdm, a.RQ desc, a.ppdm';
    SQL.Add(sqlstr);
    Open;
  end;
  DBGid_kc.Columns[0].Visible := (CBo_kfmc.ItemIndex = 0);
  DBGid_kc.Columns[2].Visible := (CBo_ppmc.ItemIndex = 0);
end;

procedure TFrmY_kcmxcx.Act_rtnExecute(Sender: TObject);
begin
  Query1.Close;
  Qry_kcmx.Close;
  Close;
end;

procedure TFrmY_kcmxcx.CBo_kfmcChange(Sender: TObject);
const
  C_S_J = 'select distinct a.PPDM, b.TMC,c.mc ' +
    'from TKS_JYPMXFLZ a, TB_YZPPTXXB b, TG_YZPPTZDM c ' +
    'where b.TDM=a.PPDM and b.tz=c.tzdm and a.KFDM=%s';
  C_S_T = 'select distinct a.PPDM, b.TMC ' +
    'from TKS_TXPMXFLZ a, TB_YZPPXXB b ' +
    'where b.TDM=a.PPDM and a.KFDM=%s';
  C_S_Y = 'select distinct a.PPDM, b.MC TMC ' +
    'from TKS_YPMXFLZ a, TB_JYPTXXB b ' +
    'where b.TDM=a.PPDM and a.KFDM=%s';
  C_S_G = 'select distinct a.PPDM, b.TMC,'''' mc ' +
    'from TKS_GRPMXFLZ a, TB_YZPPXXB b ' +
    'where b.TDM=a.PPDM and a.KFDM=%s';
var
  v_s_sql: string;
begin
  CBo_ppmc.Items.Clear;
  CBo_ppmc.Items.Add('全部');
  if Assigned(vlp_sl_ppdm) then
    vlp_sl_ppdm.Destroy;
  vlp_sl_ppdm := TStringList.Create;
  vlp_sl_ppdm.Add('a.PPDM');

  with Query1 do
  begin
    Close;
    SQL.Clear;
    case FIO_type of
      io_jyp:
        v_s_sql := C_S_J;
      io_txp:
        v_s_sql := C_S_T;
      io_yp:
        v_s_sql := C_S_Y;
      io_grp:
        v_s_sql := C_S_G;
    end;
    v_s_sql := format(v_s_sql, [vlp_sl_kfdm[CBo_kfmc.ItemIndex]]);
    SQL.Add(v_s_sql);
    Open;
    while not Eof do
    begin
      CBo_ppmc.Items.Add(Fields[0].AsString + '(' + Fields[1].AsString + ')');
      vlp_sl_ppdm.Add('''' + FieldByName('PPDM').AsString + '''');
      Next;
    end;
  end;
  CBo_ppmc.ItemIndex := 0;
end;

procedure TFrmY_kcmxcx.CheckBox1Click(Sender: TObject);
begin
  D1.Enabled := CheckBox1.Checked;
  D2.Enabled := CheckBox1.Checked;
end;

procedure TFrmY_kcmxcx.Edit1Change(Sender: TObject);
begin
  if Edit1.Text <> '' then
    Qry_kcmx.Locate('ZH', Edit1.Text, [loCaseInsensitive, loPartialKey]);
end;

procedure TFrmY_kcmxcx.FormCreate(Sender: TObject);
begin
  GetAllColWidth(Self);
end;

procedure TFrmY_kcmxcx.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  SaveAllColWidth(Self);
end;

procedure TFrmY_kcmxcx.MI_ExcClick(Sender: TObject);
begin
  DBGE_rkd.Export_To_Excel(DBGid_kc);
end;

procedure TFrmY_kcmxcx.MI_txtClick(Sender: TObject);
begin
  DBGE_rkd.Export_To_Txt(DBGid_kc);
end;

procedure TFrmY_kcmxcx.FormKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  case key of
    VK_RETURN:
    begin
      if (self.activecontrol is TMEMO) or (self.activecontrol is TDBGrid) then
      else
      begin
        postmessage(self.handle,WM_KEYDOWN,9,0);
        postmessage(self.handle,WM_KEYUP,9,0);
      end;
    end;
    Vk_Escape: Self.Close;
  end;

end;

procedure TFrmY_kcmxcx.Qry_kcmxAfterOpen(DataSet: TDataSet);
begin
  TNumericField(Qry_kcmx.FieldByName('JSSL')).DisplayFormat := '#,##';
  TNumericField(Qry_kcmx.FieldByName('ZJSL')).DisplayFormat := '#,##';
  TNumericField(Qry_kcmx.FieldByName('JCSL')).DisplayFormat := '#,##';

end;

end.