frmqrynoticeimpl.pas

多媒体播放后台管理系统适用于各种字幕播放软件的后台管理,包括播放内容、时间、等级等的管理。

unit FrmQryNoticeImpl;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBClient, Provider, DBTables, ActnList, ImgList, StdCtrls,
  Buttons, ComCtrls, ToolWin, Grids, DBGridEh, DBCtrlsEh, Mask, ADODB, temp,
  ExtCtrls, SUIForm;

type
  TFrmQryNotice = class(TForm)
    sfrMain: TsuiForm;
    imgSt: TImage;
    clbMain: TCoolBar;
    tlbMain: TToolBar;
    btnExportToExcel: TToolButton;
    ToolButton1: TToolButton;
    btnQuery: TToolButton;
    btnClearCond: TToolButton;
    ToolButton8: TToolButton;
    btnClose: TToolButton;
    grpCondition: TGroupBox;
    lblUnitID: TLabel;
    lblUserID: TLabel;
    lblStartBCDate: TLabel;
    lblJssj: TLabel;
    lblStartOperDate: TLabel;
    Label2: TLabel;
    Label1: TLabel;
    chkPending: TCheckBox;
    chkBroadCasting: TCheckBox;
    chkEnd: TCheckBox;
    cmbUnitID: TDBComboBoxEh;
    cmbUserID: TDBComboBoxEh;
    dtpStartBCDate: TDateTimePicker;
    dtpEndBCDate: TDateTimePicker;
    dtpStartOperDate: TDateTimePicker;
    dtpEndOperDate: TDateTimePicker;
    chkAudited: TCheckBox;
    cmbGrade: TDBComboBoxEh;
    grdMain: TDBGridEh;
    imlAction: TImageList;
    pvdMain: TDataSetProvider;
    cdsMain: TClientDataSet;
    cdsMainCONTENT: TWideStringField;
    cdsMainBC_TIME: TWideStringField;
    cdsMainE_TIME: TWideStringField;
    cdsMainUNIT_ID: TWideStringField;
    cdsMainUSER_ID: TWideStringField;
    cdsMainOP_TIME: TWideStringField;
    cdsMainSTATUS: TWideStringField;
    cdsMainREMARK: TWideStringField;
    cdsMaingrade: TWideStringField;
    dtsMain: TDataSource;
    conMain: TADOConnection;
    qryMain: TADOQuery;
    qryPublic: TADOQuery;
    atlAtions: TActionList;
    actExportToExcel: TAction;
    actQuery: TAction;
    actClearCond: TAction;
    actClose: TAction;
    cdsMainAUDIT_ID: TWideStringField;
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure actCloseExecute(Sender: TObject);
    procedure actQueryExecute(Sender: TObject);
    procedure actClearCondExecute(Sender: TObject);
    procedure actExportToExcelExecute(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmQryNotice: TFrmQryNotice;

implementation

{$R *.dfm}

procedure TFrmQryNotice.FormShow(Sender: TObject);
begin
  dtpStartBCDate.Date := GetSysDateTime(qryPublic);
  dtpEndBCDate.Date := dtpStartBCDate.Date;
  dtpStartOperDate.Date := dtpStartBCDate.Date;
  dtpEndOperDate.Date := dtpStartOperDate.Date;

  grdMain.Columns[4].KeyList.Clear;
  grdMain.Columns[4].PickList.Clear;
  cmbUnitID.Items.Clear;
  cmbUnitID.KeyItems.Clear;
  cmbUnitID.Items.Add('');
  cmbUnitID.KeyItems.Add('');
  qryPublic.Close;
  qryPublic.SQL.Clear;
  qryPublic.SQL.Add('select distinct unit_id,name from t_sys_unit order by unit_id');
  qryPublic.Open;
  while not qryPublic.Eof do
  begin
    cmbUnitID.KeyItems.Add(qryPublic.FieldByName('unit_id').AsString);
    cmbUnitID.Items.Add(qryPublic.FieldByName('name').AsString);

    grdMain.Columns[4].KeyList.Add(qryPublic.FieldByName('unit_id').AsString);
    grdMain.Columns[4].PickList.Add(qryPublic.FieldByName('name').AsString);

    qryPublic.Next;
  end;

  grdMain.Columns[5].KeyList.Clear;
  grdMain.Columns[5].PickList.Clear;
  grdMain.Columns[9].KeyList.Clear;
  grdMain.Columns[9].PickList.Clear;
  cmbUserID.Items.Clear;
  cmbUserID.KeyItems.Clear;
  cmbUserID.Items.Add('');
  cmbUserID.KeyItems.Add('');
  qryPublic.Close;
  qryPublic.SQL.Clear;
  qryPublic.SQL.Add('select distinct user_id,user_name from t_sys_user order by user_id');
  qryPublic.Open;
  while not qryPublic.Eof do
  begin
    cmbUserID.KeyItems.Add(qryPublic.FieldByName('user_id').AsString);
    cmbUserID.Items.Add(qryPublic.FieldByName('user_name').AsString);

    grdMain.Columns[5].KeyList.Add(qryPublic.FieldByName('user_id').AsString);
    grdMain.Columns[5].PickList.Add(qryPublic.FieldByName('user_name').AsString);
    grdMain.Columns[9].KeyList.Add(qryPublic.FieldByName('user_id').AsString);
    grdMain.Columns[9].PickList.Add(qryPublic.FieldByName('user_name').AsString);

    qryPublic.Next;
  end;
  qryPublic.Close;

  dtpStartBCDate.SetFocus;
end;

procedure TFrmQryNotice.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  qryMain.Close;
  qryPublic.Close;

  Action := caFree;
end;

procedure TFrmQryNotice.actCloseExecute(Sender: TObject);
begin
  Close;
end;

procedure TFrmQryNotice.actQueryExecute(Sender: TObject);
var
  strTemp, strWhere: string;
begin
  cmbUnitID.Text := Trim(cmbUnitID.Text);
  cmbUserID.Text := Trim(cmbUserID.Text);
  cmbGrade.Text := Trim(cmbGrade.Text);

{  if (not chkPending.Checked) and (not chkAudited.Checked)
    and (not chkBroadCasting.Checked) and (not chkEnd.Checked) then
  begin
    ShowMessage('请先选择“通知状态”！');
    chkPending.SetFocus;
    Abort;
  end;}

  Screen.Cursor := crHourGlass;

  strTemp := '';
  if chkPending.Checked then
    strTemp := ' status=''P'' ';
  if chkAudited.Checked then
  begin
    if strTemp = '' then
      strTemp := ' status=''A'' '
    else
      strTemp := strTemp + ' or status=''A'' ';
  end;
  if chkBroadCasting.Checked then
  begin
    if strTemp = '' then
      strTemp := ' status=''B'' '
    else
      strTemp := strTemp + ' or status=''B'' ';
  end;
  if chkEnd.Checked then
  begin
    if strTemp = '' then
      strTemp := ' status=''E'' '
    else
      strTemp := strTemp + ' or status=''E'' ';
  end;

  strWhere := '';
  if strTemp <> '' then
    strWhere := '(' + strTemp + ') ';

  if (dtpStartBCDate.Checked) then
  begin
    if strWhere = '' then
      strWhere := ' format(BROADCAST_DATE,''YYMMDD'')>=' + QuotedStr(FormatDateTime('YYMMDD', dtpStartBCDate.Date)) + ' '
    else
      strWhere := strWhere + 'and format(BROADCAST_DATE,''YYMMDD'')>=' + QuotedStr(FormatDateTime('YYMMDD', dtpStartBCDate.Date)) + ' ';
  end;
  if (dtpEndBCDate.Checked) then
  begin
    if strWhere = '' then
      strWhere := ' format(BROADCAST_DATE,''YYMMDD'')<=' + QuotedStr(FormatDateTime('YYMMDD', dtpEndBCDate.Date)) + ' '
    else
      strWhere := strWhere + 'and format(BROADCAST_DATE,''YYMMDD'')<=' + QuotedStr(FormatDateTime('YYMMDD', dtpEndBCDate.Date)) + ' ';
  end;

  if (dtpStartOperDate.Checked) then
  begin
    if strWhere = '' then
      strWhere := ' format(OPER_TIME,''YYMMDD'')>=' + QuotedStr(FormatDateTime('YYMMDD', dtpStartOperDate.Date)) + ' '
    else
      strWhere := strWhere + 'and format(OPER_TIME,''YYMMDD'')>=' + QuotedStr(FormatDateTime('YYMMDD', dtpStartOperDate.Date)) + ' ';
  end;
  if (dtpEndOperDate.Checked) then
  begin
    if strWhere = '' then
      strWhere := ' format(OPER_TIME,''YYMMDD'')<=' + QuotedStr(FormatDateTime('YYMMDD', dtpEndOperDate.Date)) + ' '
    else
      strWhere := strWhere + 'and format(OPER_TIME,''YYMMDD'')<=' + QuotedStr(FormatDateTime('YYMMDD', dtpEndOperDate.Date)) + ' ';
  end;

  if cmbUnitID.Text <> '' then
  begin
    if strWhere = '' then
      strWhere := ' unit_id = ' + QuotedStr(cmbUnitID.KeyItems[cmbUnitID.ItemIndex]) + ' '
    else
      strWhere := strWhere + ' and unit_id = ' + QuotedStr(cmbUnitID.KeyItems[cmbUnitID.ItemIndex]) + ' ';
  end;
  if cmbUserID.Text <> '' then
  begin
    if strWhere = '' then
      strWhere := ' user_id = ' + QuotedStr(cmbUserID.KeyItems[cmbUserID.ItemIndex]) + ' '
    else
      strWhere := strWhere + ' and user_id = ' + QuotedStr(cmbUserID.KeyItems[cmbUserID.ItemIndex]) + ' ';
  end;
  if cmbGrade.Text <> '' then
  begin
    if strWhere = '' then
      strWhere := ' grade = ' + QuotedStr(cmbGrade.KeyItems[cmbGrade.ItemIndex]) + ' '
    else
      strWhere := strWhere + ' and grade = ' + QuotedStr(cmbGrade.KeyItems[cmbGrade.ItemIndex]) + ' ';
  end;

  if strWhere <> '' then
    strWhere := ' where ' + strWhere;

  qryMain.Close;
  qryMain.SQL.Clear;
  qryMain.SQL.Add('select grade,CONTENT,format(BROADCAST_DATE,''YY/MM/DD'')+'' ''+format(BROADCAST_TIME,''HH:NN'') AS BC_TIME,');
  qryMain.SQL.Add('format(END_DATE,''YY/MM/DD'')+'' ''+format(END_TIME,''HH:NN'') AS E_TIME,UNIT_ID,USER_ID,');
  qryMain.SQL.Add('format(OPER_TIME,''YY/MM/DD HH:NN'') AS OP_TIME,STATUS,REMARK from t_notice ');
  qryMain.SQL.Add(strWhere + ' order by oper_time desc');
  cdsMain.Close;
  cdsMain.Open;

  Screen.Cursor := crDefault;
end;

procedure TFrmQryNotice.actClearCondExecute(Sender: TObject);
begin
  cmbUnitID.ItemIndex := -1;
  cmbUserID.ItemIndex := -1;
  cmbGrade.ItemIndex := -1;
  dtpStartBCDate.Checked := False;
  dtpEndBCDate.Checked := False;
  dtpStartOperDate.Checked := False;
  dtpEndOperDate.Checked := False;
  chkPending.Checked := False;
  chkAudited.Checked := False;
  chkBroadCasting.Checked := False;
  chkEnd.Checked := False;
end;

procedure TFrmQryNotice.actExportToExcelExecute(Sender: TObject);
begin
  ExportToExcel(Self, cdsMain, True);
end;

end.