querystat.pas

计量功能 事件记录 购电提醒 欠费报警 负荷控制 自检功能 显示功能 用户插卡错误信息显示

unit QueryStat;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, DBTables, ComCtrls, StdCtrls, ExtCtrls, DBCtrls,
  CheckLst, ConstDef, FieldDef, Mask, MskEdit;

type
  TFrmQueryStat = class(TForm)
    DataSourceCust: TDataSource;
    Session1: TSession;
    QueryCust: TQuery;
    PageControlData: TPageControl;
    TabSheetCust: TTabSheet;
    DBGridCust: TDBGrid;
    DBNavCust: TDBNavigator;
    TabSheetEMeter: TTabSheet;
    TabSheetBuy: TTabSheet;
    DataSourceEMeter: TDataSource;
    QueryEMeter: TQuery;
    QueryBuy: TQuery;
    DataSourceBuy: TDataSource;
    QueryCustICustNo: TIntegerField;
    QueryCustSIDNo: TStringField;
    QueryCustSCustName: TStringField;
    QueryCustSMark: TStringField;
    DBGridEMeter: TDBGrid;
    DBNavEMeter: TDBNavigator;
    DBGridBuy: TDBGrid;
    DBNavBuy: TDBNavigator;
    QueryEMeterICustNo: TIntegerField;
    QueryEMeterSENo: TStringField;
    QueryEMeterDTEDate: TDateField;
    QueryEMeterIEDegree: TIntegerField;
    QueryEMeterIESum: TIntegerField;
    QueryEMeterIEConstKind: TIntegerField;
    QueryEMeterIEConst: TIntegerField;
    QueryBuyICustNo: TIntegerField;
    QueryBuyIBuyNum: TIntegerField;
    QueryBuyIECurBuy: TIntegerField;
    QueryBuyIEPriceKind: TIntegerField;
    QueryBuyFEPrice: TFloatField;
    QueryBuyFMoney: TFloatField;
    QueryBuyDTBuyDate: TDateField;
    QueryBuySOpName: TStringField;
    QueryBuySHostName: TStringField;
    QueryEMeterITotalNum: TIntegerField;
    GroupBoxQCust: TGroupBox;
    Label2: TLabel;
    Label1: TLabel;
    Label3: TLabel;
    MskEdit2: TMskEdit;
    ComboBoxCustNo: TComboBox;
    MskEditCustNo: TMskEdit;
    ComboBoxCustName: TComboBox;
    MskEditCustName: TMskEdit;
    ComboBoxIDNo: TComboBox;
    MskEditIDNo: TMskEdit;
    GroupBoxQEMeter: TGroupBox;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    MskEditNewFromDate: TMskEdit;
    MskEditNewToDate: TMskEdit;
    ComboBoxEMeterNo: TComboBox;
    MskEditEMeterNo: TMskEdit;
    Bevel1: TBevel;
    Label8: TLabel;
    BtnOK: TButton;
    BtnCancel: TButton;
    GroupBoxQBuy: TGroupBox;
    Label9: TLabel;
    Label10: TLabel;
    Label12: TLabel;
    MskEditBuyFromDate: TMskEdit;
    MskEditBuyToDate: TMskEdit;
    GroupBoxSCust: TGroupBox;
    LabCustNum: TLabel;
    GroupBoxSEMeter: TGroupBox;
    LabEMeterNum: TLabel;
    GroupBoxSBuy: TGroupBox;
    LabBuyMoney: TLabel;
    DataSourceStat: TDataSource;
    QueryStat: TQuery;
    ComboBoxNewDate: TComboBox;
    ComboBoxBuyDate: TComboBox;
    procedure BtnCancelClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure PrdFrmQueryStatIni(Sender: TObject);
    procedure BtnOKClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure PrdQueryStatCust(Sender: TObject);
    procedure PrdQueryStatEMeter(Sender: TObject);
    procedure PrdQueryStatBuy(Sender: TObject);
    procedure DataSourceCustDataChange(Sender: TObject; Field: TField);
    procedure DataSourceEMeterDataChange(Sender: TObject; Field: TField);
    procedure DataSourceBuyDataChange(Sender: TObject; Field: TField);
    procedure ComboBoxNewDateChange(Sender: TObject);
    procedure ComboBoxBuyDateChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    EnterMode: EnterModeType;
  end;

var
  FrmQueryStat: TFrmQueryStat;
  sQSCust, sQSEMeter, sQSBuy: string;
  dtCurDate,dtBuyFromDate,dtBuyToDate: TDateTime;

Function  sFunGetStrByDate(dtDate: TDateTime): WideString; export;
   export;far;external 'NIMStrans.dll';
Function  dtFunGetDateByStr(sStrDate: string): TDateTime;
   export;far;external 'NIMStrans.dll';
Function  sFunCheckStr(sStrIn: String): widestring;
   export;far;external 'NIMStrans.dll';

implementation

{$R *.DFM}

procedure TFrmQueryStat.BtnCancelClick(Sender: TObject);
begin
  ModalResult:=mrCancel;
end;

procedure TFrmQueryStat.FormCreate(Sender: TObject);
begin
  Session.AddPassword(Psw_Cust);
  Session.AddPassword(Psw_EMeterInfo);
  Session.AddPassword(Psw_Buy);
end;

procedure TFrmQueryStat.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Session.RemovePassword(Psw_Cust);
  Session.RemovePassword(Psw_EMeterInfo);
  Session.RemovePassword(Psw_Buy);
end;

procedure TFrmQueryStat.FormShow(Sender: TObject);
begin
  PrdFrmQueryStatIni(Sender);
end;

procedure TFrmQueryStat.PrdFrmQueryStatIni(Sender: TObject);
var
  iCount: integer;
  Component: TComponent;
begin
  QueryCust.DatabaseName:=DefSrvAlias;
  QueryEmeter.DatabaseName:=DefSrvAlias;
  QueryBuy.DatabaseName:=DefSrvAlias;
  QueryStat.DatabaseName:=DefSrvAlias;

  QueryCust.DataSource:=nil;
  if EnterMode=QSAllInfoMode then begin
     QueryEmeter.DataSource:=nil;
     QueryBuy.DataSource:=nil;
    end
  else begin
     QueryEmeter.DataSource:=DataSourceCust;
     QueryBuy.DataSource:=DataSourceCust;
     QueryStat.DataSource:=DataSourceCust;
    end;

  PageControlData.ActivePage:=TabSheetCust;
  for iCount:=0 to ComponentCount-1 do
   begin
     Component:=Components[iCount];
     if Component is TComboBox then
        (Component as TComboBox).ItemIndex:=0;
   end;

  dtCurDate:=Now;
  ComboBoxNewDateChange(Sender);
  ComboBoxBuyDateChange(Sender);
end;

procedure TFrmQueryStat.PrdQueryStatCust(Sender: TObject);
var
  sQueryStr: string;
  iTotalCustNum: integer;
begin
//---------------- 设置查询条件开始 ----------------//
  sQSCust:='(';
  //------------- 查询条件：用户编号 -------------//
  sQueryStr:=sFunCheckStr(MskEditCustNo.Text);
  case ComboBoxCustNo.ItemIndex of
    1:{等于} sQSCust:=sQSCust+{CMP}'ICustNo = '+sQueryStr;
    2:{大于} sQSCust:=sQSCust+{CMP}'ICustNo > '+sQueryStr;
    3:{小于} sQSCust:=sQSCust+{CMP}'ICustNo < '+sQueryStr;
  else
     {全部} sQSCust:=sQSCust+{CMP}'ICustNo IS NOT NULL';
   end;
  //----------------------------------------------//
  sQSCust:=sQSCust+') and (';
  //------------ 查询条件：用户姓名 ------------//
  sQueryStr:=MskEditCustName.Text;
  case ComboBoxCustName.ItemIndex of
    1:{等于} sQSCust:=sQSCust+
            {CMP}'SCustName LIKE "'+sQueryStr+'"';
    2:{包含} sQSCust:=sQSCust+
            {CMP}'SCustName LIKE '+'"%'+sQueryStr+'%"';
  else
     {全部} sQSCust:=sQSCust+{CMP}'SCustName IS NOT NULL';
   end;
  //----------------------------------------------//
  sQSCust:=sQSCust+') and (';
  //------------ 查询条件：身份证号 ------------//
  sQueryStr:=sFunCheckStr(MskEditIDNo.Text);
  case ComboBoxIDNo.ItemIndex of
    1:{等于} sQSCust:=sQSCust+
            {CMP}'SIDNo LIKE "'+sQueryStr+'"';
    2:{包含} sQSCust:=sQSCust+
            {CMP}'SIDNo LIKE '+'"%'+sQueryStr+'%"';
  else
     {全部} sQSCust:=sQSCust+{CMP}'SIDNo IS NOT NULL';
   end;
  //----------------------------------------------//
  sQSCust:=sQSCust+')';
//---------------- 设置查询条件完成 ----------------//
  with QueryCust do begin
     close; SQL.Clear;
     SQL.Add('SELECT ICustNo, SCustName, SIDNo, SMark');
     SQL.Add('FROM "Cust.DB" Cust');
     SQL.Add('WHERE '+sQSCust);
     Prepare; Open;
   end;
end;

procedure TFrmQueryStat.PrdQueryStatEMeter(Sender: TObject);
var
  sQueryStr: string;
  dtNewFromDate,dtNewToDate: TDateTime;
  iTotalEMeterNum: integer;
begin
//---------------- 设置查询条件开始 ----------------//
  sQSEMeter:='(';
  //------------- 查询条件：电表编号 -------------//
  sQueryStr:=sFunCheckStr(MskEditEMeterNo.Text);
  case ComboBoxEMeterNo.ItemIndex of
    1:{等于} sQSEMeter:=sQSEMeter+
            {CMP}'SENo LIKE "'+sQueryStr+'"';
    2:{包含} sQSEMeter:=sQSEMeter+
            {CMP}'SENo LIKE '+'"%'+sQueryStr+'%"';
  else
     {全部} sQSEMeter:=sQSEMeter+{CMP}'SENo IS NOT NULL';
   end;
  //----------------------------------------------//
  sQSEMeter:=sQSEMeter+') and (';
  //------------- 查询条件：开户日期 -------------//
  dtNewFromDate:=dtFunGetDateByStr(MskEditNewFromDate.Text);
  dtNewToDate:=dtFunGetDateByStr(MskEditNewToDate.Text);
  //----------------------------------------------//
  sQSEMeter:=sQSEMeter+
      {CMP}'DTEDate BETWEEN :FromDate AND :ToDate';
  //------------------ 连接查询 -----------------//
  if EnterMode=QSDefCustMode then
     sQSEMeter:=sQSEMeter+') and ('
         +'Emeterinfo.ICustNo = :ICustNo';
  //----------------------------------------------//
  sQSEMeter:=sQSEMeter+')';
//---------------- 设置查询条件完成 ----------------//
  with QueryEMeter do begin
     close; SQL.Clear;
     SQL.Add('SELECT ICustNo, SENo, DTEDate, IEDegree,');
     SQL.Add('IESum, ITotalNum, IEConstKind, IEConst');
     SQL.Add('FROM "EMeterInfo.DB" Emeterinfo');
     SQL.Add('WHERE '+sQSEMeter);
     ParamByName('FromDate').AsDate:=dtNewFromDate;
     ParamByName('ToDate').AsDate:=dtNewToDate;
     Prepare; Open;
   end;
end;

procedure TFrmQueryStat.PrdQueryStatBuy(Sender: TObject);
begin
//---------------- 设置查询条件开始 ----------------//
  sQSBuy:='(';
  //------------- 查询条件：申购日期 -------------//
  dtBuyFromDate:=dtFunGetDateByStr(MskEditBuyFromDate.Text);
  dtBuyToDate:=dtFunGetDateByStr(MskEditBuyToDate.Text);
  //----------------------------------------------//
  sQSBuy:=sQSBuy+
      {CMP}'DTBuyDate BETWEEN :FromDate AND :ToDate';
  //------------------ 连接查询 -----------------//
  if EnterMode=QSDefCustMode then
     sQSBuy:=sQSBuy+') and ('
         +'Buy.ICustNo = :ICustNo';
  //----------------------------------------------//
  sQSBuy:=sQSBuy+')';
//---------------- 设置查询条件完成 ----------------//
  with QueryBuy do begin //查询
     close; SQL.Clear;
     SQL.Add('SELECT ICustNo, IBuyNum, IECurBuy,');
     SQL.Add('IEPriceKind, FEPrice,');
     SQL.Add('(IECurBuy * FEPrice) AS FMoney,');
     SQL.Add('DTBuyDate, SOpName, SHostName');
     SQL.Add('FROM "Buy.DB" Buy');
     SQL.Add('WHERE '+sQSBuy);
     ParamByName('FromDate').AsDate:=dtBuyFromDate;
     ParamByName('ToDate').AsDate:=dtBuyToDate;
     Prepare; Open;
   end;
end;

procedure TFrmQueryStat.BtnOKClick(Sender: TObject);
begin
  PrdQueryStatCust(Sender);
  PrdQueryStatEMeter(Sender);
  PrdQueryStatBuy(Sender);
end;

procedure TFrmQueryStat.DataSourceCustDataChange(Sender: TObject;
  Field: TField);
begin
  LabCustNum.Caption:='用户数量'#13+inttostr(QueryCust.RecordCount);
end;

procedure TFrmQueryStat.DataSourceEMeterDataChange(Sender: TObject;
  Field: TField);
begin
  LabEMeterNum.Caption:='电表数量'#13+inttostr(QueryEMeter.RecordCount);
end;

procedure TFrmQueryStat.DataSourceBuyDataChange(Sender: TObject;
  Field: TField);
var
  fTotalMoney: Double;
begin
  with QueryStat do begin //统计
     SQL.Clear;
     SQL.Add('SELECT SUM(IECurBuy * FEPrice) AS FToTal');
     SQL.Add('FROM "Buy.DB" Buy');
     SQL.Add('WHERE '+sQSBuy);
     ParamByName('FromDate').AsDate:=dtBuyFromDate;
     ParamByName('ToDate').AsDate:=dtBuyToDate;
     Prepare; Open;
     fTotalMoney:=FieldByName('FToTal').AsFloat;
     close;
   end;
  LabBuyMoney.Caption:='总金额'#13'￥'+FloatToStr(fTotalMoney);
end;

procedure TFrmQueryStat.ComboBoxNewDateChange(Sender: TObject);
var
  iInterval: integer;
begin
  case ComboBoxNewDate.ItemIndex of
     1: iInterval:=30;//一个月(30天)
     2: iInterval:=90;//三个月(90天)
     3: iInterval:=180;//六个月(180天)
  else
     iInterval:=-1;//系统默认
   end;
  dtCurDate:=Now;
  MskEditNewToDate.Text:=sFunGetStrByDate(dtCurDate);
  if iInterval>0 then
     MskEditNewFromDate.Text:=sFunGetStrByDate(dtCurDate-iInterval);
end;

procedure TFrmQueryStat.ComboBoxBuyDateChange(Sender: TObject);
var
  iInterval: integer;
begin
  case ComboBoxBuyDate.ItemIndex of
     1: iInterval:=30;//一个月(30天)
     2: iInterval:=90;//三个月(90天)
     3: iInterval:=180;//六个月(180天)
  else
     iInterval:=-1;//系统默认
   end;
  MskEditBuyToDate.Text:=sFunGetStrByDate(dtCurDate);
  if iInterval>0 then
     MskEditBuyFromDate.Text:=sFunGetStrByDate(dtCurDate-iInterval);
end;

end.