ufoptions.pas

一个功能较全的图书进销存软件,功能还算完备

unit ufOptions;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls, Buttons,ADODB,DB;

type
  TfOptions = class(TForm)
    Panel1: TPanel;
    GroupBox1: TGroupBox;
    StaticText1: TStaticText;
    StaticText2: TStaticText;
    cbPro: TComboBox;
    StaticText3: TStaticText;
    StaticText4: TStaticText;
    dtpEnd: TDateTimePicker;
    dtpbgn: TDateTimePicker;
    ApplyBtn: TBitBtn;
    ExitBtn: TBitBtn;
    cbpid: TComboBox;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure ExitBtnClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cbProChange(Sender: TObject);
    procedure cbProExit(Sender: TObject);
    procedure dtpbgnKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure ApplyBtnClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fOptions: TfOptions;

implementation

uses udmpublish, Uglobal, ufrpt_jx,  ufrpt_sales_qty_qr;

{$R *.dfm}

procedure TfOptions.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  fOptions:=nil;
  action:=cafree;
end;

procedure TfOptions.ExitBtnClick(Sender: TObject);
begin
close;
end;

//创建窗体，各个控件赋初值
procedure TfOptions.FormShow(Sender: TObject);
begin
//取数据库时间
  with dmpublish.l_store do
  begin
    procedurename:='geddate;1';
    parameters.Clear;
    parameters.CreateParameter('@out',ftDatetime,pdoutput,0,Unassigned);
    Prepared;
    execproc;
    if parameters[0].Value<>null then
    begin
     dtpBgn.Date := Parameters[0].Value-6;  //默认为一周
     dtpEnd.Date := Parameters[0].Value;
    end
    else
      showmessage('取数据库时间出错!');
  end;
  dtpbgn.Time:=strTotime('0:00:00');
  dtpend.Time:=strTotime('23:59:59');

end;

procedure TfOptions.FormCreate(Sender: TObject);
begin
 if not c_getPid3(dmpublish.adsShare,'ID','NAME','PROVIDER','STATUS',cbPid,cbpro) then
    MSG('加入供应商名称列表出错，请检查数据库！','W');
end;

procedure TfOptions.cbProChange(Sender: TObject);
begin
  cbpid.ItemIndex:=cbpro.ItemIndex;
end;

procedure TfOptions.cbProExit(Sender: TObject);
begin
if trim(cbpro.Text)<>'' then
begin
  if cbpro.Items.IndexOf(trim(cbpro.Text))<>-1 then
  begin
    cbpid.ItemIndex:=cbpro.ItemIndex;
  end
  else
  begin
    MSG('输入的供应商名称不存在，请重新输入!','E');
    cbpro.SetFocus;
  end;
end;
end;

procedure TfOptions.dtpbgnKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if (Key = VK_RETURN)
  then SelectNext(ActiveControl, True, True);
end;

procedure TfOptions.ApplyBtnClick(Sender: TObject);
var
  sqlstr,sqlstr2,sqlstr3:string;
begin
  applybtn.Enabled:=false;
{ TODO : 设置好查询范围 }
  sqlstr:=' (operate_date between '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime)+''' and '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime)+''' )';
  sqlstr2:=' (operate_date between '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime)+''' and '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime)+''' )';
  sqlstr3:=' (a.operate_date between '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime)+''' and '''+FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime)+''' )';
  if cbpid.ItemIndex>0 then
  begin
    sqlstr:=sqlstr+' and '+' supplier_id='+trim(cbpid.Text);
    sqlstr3:=sqlstr3+' and '+' b.supplier_id='+trim(cbpid.Text);
  end;
  case  ReportIndex of
  0:  //进销统计清单
  begin
  TRy
      { TODO : 清空表 }
    with dmpublish do
    begin
      if q_share.Active then
        q_share.Close;
        q_share.SQL.Clear;
        q_share.SQL.Add(' delete from publish_rpt_jx ');
        q_share.ExecSQL;
      { TODO : 打开进销统计表 }
      if not adsrpt_jx.Active then
        adsrpt_jx.Active:=true;
      { TODO : 获得进货资料 }
      if q_share.Active then
        q_share.Close;
        q_share.SQL.Clear;
        q_share.SQL.Add(' select sum(quantity) as qty,sum(in_price*quantity) as jine from publish_storage_in ');
        q_share.SQL.Add(' where status=''1'' and '+sqlstr);
        q_share.Open;
      { TODO : 插入进货资料 }
      with adsrpt_jx do
      begin
        append;
        fieldbyname('type').AsString:='入库';
        fieldbyname('qty').AsInteger:=q_share.fieldbyname('qty').AsInteger;
        fieldbyname('jine').AsFloat:=q_share.fieldbyname('jine').AsFloat;
        post;
      end;
      { TODO : 获得退货资料 }
      if q_share.Active then
        q_share.Close;
        q_share.SQL.Clear;
        q_share.SQL.Add(' select sum(quantity) as qty,sum(price*quantity) as jine from publish_storage_out ');
        q_share.SQL.Add(' where status=''1'' and issale=''退货'' and '+sqlstr2);
        q_share.Open;
      { TODO : 插入退货资料 }
      with adsrpt_jx do
      begin
        append;
        fieldbyname('type').AsString:='退货';
        fieldbyname('qty').AsInteger:=q_share.fieldbyname('qty').AsInteger;
        fieldbyname('jine').AsFloat:=q_share.fieldbyname('jine').AsFloat;
        post;
      end;
      { TODO : 获得出售资料 }
      if q_share.Active then
        q_share.Close;
        q_share.SQL.Clear;
        q_share.SQL.Add(' select sum(quantity) as qty,sum(price*quantity) as jine from publish_storage_out ');
        q_share.SQL.Add(' where status=''1'' and issale=''出售'' and '+sqlstr2);
        q_share.Open;
      { TODO : 插入出售资料 }
      with adsrpt_jx do
      begin
        append;
        fieldbyname('type').AsString:='出售';
        fieldbyname('qty').AsInteger:=q_share.fieldbyname('qty').AsInteger;
        fieldbyname('jine').AsFloat:=q_share.fieldbyname('jine').AsFloat;
        post;
      end;
    end;
    dmpublish.adsrpt_jx.Requery();
    //创建报表
    if not assigned(frpt_jx) then
      frpt_jx:=tfrpt_jx.Create(application);
    //设置报表头
    frpt_jx.qldate1.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime);
    frpt_jx.qldate2.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime);
    //预览
    frpt_jx.Preview;
    applybtn.Enabled:=true;
  Except
    MSG('生成报表出错！','W');
    formshow(fOptions);
  end;
  end;
  1: // 销售排行清单（按数量）
  begin
    //创建报表
    if not assigned(frpt_sales_qty_qr) then
      frpt_sales_qty_qr:=tfrpt_sales_qty_qr.Create(application);
    //设置报表头
    frpt_sales_qty_qr.qldate1.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime);
    frpt_sales_qty_qr.qldate2.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime);
    frpt_sales_qty_qr.date1:=dtpbgn.Date;
    frpt_sales_qty_qr.date2:=dtpend.Date;
    frpt_sales_qty_qr.QRLabel1.Caption:='销售排行清单（按数量）';
    //执行查询
    with frpt_sales_qty_qr.q_sales_qty_qr do
    begin
      close;
      sql.Clear;
      sql.Add('select top 20 a.book_barcode,a.cn_name,b.en_name,b.store_quantity,sum(a.quantity) as qty,sum(a.money) as jine');
      sql.Add(' from publish_storage_out a left outer join publish_book b ');
      sql.Add(' on b.status=''1'' and a.book_barcode=b.book_barcode and a.cn_name=b.cn_name and a.show_price=b.show_price');
      sql.Add(' where a.status=''1'' and a.issale=''出售'' and '+sqlstr3+' group by a.book_barcode,a.cn_name,b.en_name,b.store_quantity');
      sql.Add(' order by qty desc,a.book_barcode,a.cn_name,b.en_name,b.store_quantity');
      open;
    end;
    //预览
    frpt_sales_qty_qr.Preview;
    applybtn.Enabled:=true;
  end;
  2: // 销售排行清单（按金额）
  begin
    //创建报表
    if not assigned(frpt_sales_qty_qr) then
      frpt_sales_qty_qr:=tfrpt_sales_qty_qr.Create(application);
    //设置报表头
    frpt_sales_qty_qr.qldate1.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpbgn.Datetime);
    frpt_sales_qty_qr.qldate2.Caption:=FormatDateTime('YYYY-MM-DD HH:MM:SS',dtpend.Datetime);
    frpt_sales_qty_qr.date1:=dtpbgn.Date;
    frpt_sales_qty_qr.date2:=dtpend.Date;
    frpt_sales_qty_qr.QRLabel1.Caption:='销售排行清单（按金额）';
    //执行查询
    with frpt_sales_qty_qr.q_sales_qty_qr do
    begin
      close;
      sql.Clear;
      sql.Add('select top 20 a.book_barcode,a.cn_name,b.en_name,b.store_quantity,sum(a.quantity) as qty,sum(a.money) as jine');
      sql.Add(' from publish_storage_out a left outer join publish_book b ');
      sql.Add(' on b.status=''1'' and a.book_barcode=b.book_barcode and a.cn_name=b.cn_name and a.show_price=b.show_price');
      sql.Add(' where a.status=''1'' and a.issale=''出售''  and '+sqlstr3+' group by a.book_barcode,a.cn_name,b.en_name,b.store_quantity');
      sql.Add(' order by jine desc,a.book_barcode,a.cn_name,b.en_name,b.store_quantity');
      open;
    end;
    //预览
    frpt_sales_qty_qr.Preview;
    applybtn.Enabled:=true;
  end;
  end;
end;

end.