jm_xsqktj.pas

省级集邮品管理ERP

{*******************************************************}
{                                                       }
{                       销售情况统计                    }
{                                                       }
{            中软金马公司版权所有。2002.12前            }
{                                                       }
{            编制：中软金马邮资票品项目开发组           }
{                                                       }
{                                                       }
{*******************************************************}
(*
本模块在地市集邮票管理，地市集邮品管理，地市市场购入票里面调用。

*)
unit JM_XSQKTJ;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, ExtCtrls, ComCtrls, RXSpin, Mask, ToolEdit,
  Spin, RXCtrls, Db, DBTables, JM_data, FieldComboBox,
  DBCtrls, YzppSelect, RxQuery, Menus, DBGridExport;
type
  PYGXX = ^TYGXX;
  TYGXX = record
    YGDM: string;
    YGMC: string;
  end;

type
  Tfrm_XSQKTJ = class(TForm)
    Panel4: TPanel;
    XttxTitle: TRxLabel;
    Lbl_user: TLabel;
    Panel1: TPanel;
    Label6: TLabel;
    DataSource1: TDataSource;
    rxQry_find: TRxQuery;
    Qry_YYY: TQuery;
    DataSource2: TDataSource;
    Panel2: TPanel;
    DBGrid1: TDBGrid;
    Panel3: TPanel;
    GroupBox2: TGroupBox;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    CBo_PP: TCheckBox;
    YzppSelect: TYzppSelect;
    DEd_from: TDateEdit;
    DEd_to: TDateEdit;
    btnFind: TButton;
    btnPrint: TButton;
    btnEdit: TButton;
    rxQry_findPPDM: TStringField;
    rxQry_findLB: TStringField;
    rxQry_findSJ: TFloatField;
    rxQry_findSUMLSSL: TFloatField;
    rxQry_findSUMLSJE: TFloatField;
    rxQry_findSUMCKLSSL: TFloatField;
    rxQry_findSUMCKLSJE: TFloatField;
    rxQry_findSUMCKYSSL: TFloatField;
    rxQry_findSUMCKYSJE: TFloatField;
    rxQry_findSUMJSJE: TFloatField;
    rxQry_findSUMTBJE: TFloatField;
    RB_YYY: TRadioButton;
    cmbYYY: TComboBox;
    RB_BDW: TRadioButton;
    rxQry_findPPMC: TStringField;
    rxQry_findZH: TStringField;
    procedure btnFindClick(Sender: TObject);
    procedure Qry_find0DJGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure Qry_find0DJSetText(Sender: TField; const Text: string);
    procedure btnPrintClick(Sender: TObject);
    procedure GetYGXX;
    procedure rxQry_findSJGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSJSetText(Sender: TField; const Text: string);
    procedure rxQry_findSUMLSJEGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSUMLSJESetText(Sender: TField; const Text: string);
    procedure rxQry_findSUMCKLSJEGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSUMCKLSJESetText(Sender: TField;
      const Text: string);
    procedure rxQry_findSUMCKYSJEGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSUMCKYSJESetText(Sender: TField;
      const Text: string);
    procedure rxQry_findSUMJSJEGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSUMJSJESetText(Sender: TField; const Text: string);
    procedure rxQry_findSUMTBJEGetText(Sender: TField; var Text: string;
      DisplayText: Boolean);
    procedure rxQry_findSUMTBJESetText(Sender: TField; const Text: string);
    procedure CBo_PPClick(Sender: TObject);
  private
    YGDM: string;
    SqlStr: string;
    PartSql, YzppSelect_PartSql: string; {PartSql 查询的条件；YzppSelect_Partsql在
                                         小阕控件中选择票品的条件}
    function CheckedCondi: boolean; {生成查询条件}
    procedure GenQryMacros; {设置查询语句}
  public
    function Execute(p_ISQH: TQH_TYPE): Boolean;
  end;

var
  frm_XSQKTJ: Tfrm_XSQKTJ;

implementation
uses pub;
{$R *.DFM}

function Tfrm_XSQKTJ.Execute(p_ISQH: TQH_TYPE): Boolean;
begin
  Result := false;
  try
    YzppSelect.Open;
    Lbl_user.Caption := ' 操作员:' + VG_UserName;
    SQLstr := '';
    Result := true;
    DEd_from.Date := EncodeDate(VG_Year, 1, 1);
    DEd_to.Date := StrToDate(VarToStr(VG_Year) + '-' + VarToStr(VG_Month) + '-' + VarToStr(VG_Day));
    GetYGXX;
    Caption := Application.Title;
    CBo_PPClick(nil);
  except
    CHQMsgBox('不能打开 <集邮门市信息> 表 ！');
    exit;
  end;
end;

procedure Tfrm_XSQKTJ.btnFindClick(Sender: TObject);
begin

  if RB_BDW.Checked then
  begin
    ygdm := '';

  end
  else begin
    ygdm := '';
    if Trim(CmbYYY.Text) = '' then
    begin
      CHQMSGBOX('请选择营业员！');
      exit;
    end;
    ygdm := ' and ygdm=''' + PYGXX(cmbyyy.Items.Objects[cmbyyy.ItemIndex])^.YGDM + '''';
  end;
  rxQry_find.Close;
  rxQry_find.SQL.Clear;
  GenQryMacros;
  rxQry_find.Open;
end;

function Tfrm_XSQKTJ.CheckedCondi: boolean;
begin
  Result := false;
  PartSql := '';
  YzppSelect_partSql := '';

  //增加类别查询条件
  if CheckBox1.Checked and CheckBox2.Checked and CheckBox3.Checked then
  begin
    PartSql := PartSql + ' and  a.lb in(''0'',''1'',''2'')';
    YzppSelect_PartSql := ' ';
  end;
  if CheckBox1.Checked and (not CheckBox2.Checked) and CheckBox3.Checked then
  begin
    PartSql := PartSql + '  and a.lb in(''0'',''2'')';
    YzppSelect_partSql := ' where type <>''yp''';
  end;
  if CheckBox1.Checked and CheckBox2.Checked and (not CheckBox3.Checked) then
  begin
    PartSql := PartSql + ' and  a.lb in(''0'',''1'')';
    YzppSelect_partsql := ' where type  in (''jttp'',''yp'')';
  end;
  if CheckBox1.Checked and (not CheckBox2.Checked) and (not CheckBox3.Checked) then
  begin
    PartSql := PartSql + '  and a.lb in(''0'')';
    YzppSelect_partSql := ' where type =''jttp'' ';
  end;
  if (not CheckBox1.Checked) and CheckBox2.Checked and CheckBox3.Checked then
  begin
    PartSql := PartSql + ' and a.lb in(''1'',''2'')';
    YzppSelect_partsql := ' where type <>''jttp''';
  end;
  if (not CheckBox1.Checked) and CheckBox2.Checked and (not CheckBox3.Checked) then
  begin
    PartSql := PartSql + ' and a.lb in(''1'')';
    YzppSelect_partsql := ' where type =''yp''';
  end;
  if (not CheckBox1.Checked) and (not CheckBox2.Checked) and CheckBox3.Checked then
  begin
    PartSql := PartSql + ' and a.lb in(''2'')';
    yzppselect_partsql := ' where type <>''jttp'' and type <>''yp''';
  end;
  if (not CheckBox1.Checked) and (not CheckBox2.Checked) and (not CheckBox3.Checked) then
  begin
    PartSql := PartSql + '';
    yzppselect_partsql := '';
  end;
  if CBo_pp.Checked then
    PartSql := partsql + ' and a.ppdm=''' + yzppselect.GetPPDM + ''''
  else
    PartSql := PartSql + '';
  Result := true;
end;

procedure Tfrm_XSQKTJ.Qry_find0DJGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.Qry_find0DJSetText(Sender: TField; const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.btnPrintClick(Sender: TObject);
begin
  if RB_YYY.Checked then
    PrintDBGrid(DBGrid1, '营业员销售情况');
  if RB_BDW.Checked then
    PrintDBGrid(DBGrid1, '本单位销售情况');
end;

procedure Tfrm_XSQKTJ.GenQryMacros;
var
  temp: string;
begin
  CheckedCondi; {生成查询条件}
  if Trim(ygdm) <> '' then
    temp := 'select a.*,b.tmc ppmc,b.zh from (select ppdm,lb,sj,ygdm,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ls''' + ygdm + ')' + ygdm + ' and to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj,ygdm ' +
      ' union ' +
      ' select ppdm,lb,sj,ygdm,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''tb'' ' + ygdm + ')' + ygdm + ' and to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj ,ygdm' +
      ' union ' +
      ' select ppdm,lb,sj,ygdm,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''js''' + ygdm + ')' + ygdm + '  and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj,ygdm ' +
      ' union ' +
      ' select ppdm,lb,sj,ygdm,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ys''' + ygdm + ')' + ygdm + ' and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj,ygdm ' +
      ' union ' +
      ' select ppdm,lb,sj,ygdm,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ckls''' + ygdm + ')' + ygdm + ' and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj ,ygdm ' +
      ' ) a,tb_yzppxxb b where a.ppdm=b.tdm ' + partsql + ' order by a.ppdm,a.lb,a.sj';
  if Trim(ygdm) = '' then
    temp := 'select a.*,b.tmc ppmc,b.zh from (select ppdm,lb,sj,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ls'') and to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj' +
      ' union ' +
      ' select ppdm,lb,sj,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''tb'' ) and to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj ' +
      ' union ' +
      ' select ppdm,lb,sj,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''js'') and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj ' +
      ' union ' +
      ' select ppdm,lb,sj,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ys'') and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj ' +
      ' union ' +
      ' select ppdm,lb,sj,sum(lssl),sum(lsje),sum(CKLSSL), sum(CKLSJE), sum(CKYSSL), sum(CKYSJE), sum(JSJE), sum(TBJE) from tm_xsqkmx where (ppdm,lb) in (select ppdm,lb' +
      ' from tm_jymxb where  jylx=''ckls'') and  to_char(jyrq,''yyyy-mm-dd'')>=:rq1 and to_char(jyrq,''yyyy-mm-dd'')<=:rq2 group by ppdm,lb,sj  ' +
      ' ) a,tb_yzppxxb b where a.ppdm=b.tdm ' + partsql + ' order by a.ppdm,a.lb,a.sj';

  rxQry_find.SQL.Text := temp;
  rxQry_find.Prepare;
  with rxQry_find do
  begin
    Params.ParamByName('rq1').Value := FormatDateTime('yyyy-mm-dd', DEd_from.DateTime);
    Params.ParamByName('rq2').Value := FormatDateTime('yyyy-mm-dd', DEd_to.DateTime);
  end;
end;

procedure Tfrm_XSQKTJ.GetYGXX;
var
  FYGXX: PYGXX;
begin
  cmbYYY.Items.Clear;
  with Qry_yyy do
  begin
    Close;
    Sql.Clear;
    Sql.Add('select ygdm,ygmc from tgs_ryb ');
    Open;
    First;
    while not eof do
    begin
      New(FYGXX);
      FYGXX^.YGDM := FieldByName('YGDM').AsString;
      FYGXX^.YGMC := FieldByName('YGMC').AsString;
      cmbYYY.Items.AddObject(FYGXX^.YGMC, TObject(FYGXX));
      Next;
    end;
  end;
  if Cmbyyy.Items.Count > 0 then Cmbyyy.ItemIndex := 0;
end;

procedure Tfrm_XSQKTJ.rxQry_findSJGetText(Sender: TField; var Text: string;
  DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSJSetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMLSJEGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMLSJESetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMCKLSJEGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMCKLSJESetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMCKYSJEGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMCKYSJESetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMJSJEGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMJSJESetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMTBJEGetText(Sender: TField;
  var Text: string; DisplayText: Boolean);
begin
  CurrGetText(Sender, Text, DisplayText);
end;

procedure Tfrm_XSQKTJ.rxQry_findSUMTBJESetText(Sender: TField;
  const Text: string);
begin
  CurrSetText(Sender, Text);
end;

procedure Tfrm_XSQKTJ.CBo_PPClick(Sender: TObject);
begin
  if Cbo_pp.Checked then
  begin
    YzppSelect.Enabled := True;
    CheckedCondi;
    yzppselect.SQL.Text := 'select tdm,tmc tjc,zh,tpsj/100 mc from tb_yzppxxb  ' + YzppSelect_Partsql + '  order by zh';
    yzppselect.open;
  end
  else YzppSelect.Enabled := False;
end;

end.