unitsuptab2.pas

PosEasy收银系统源码,Pos机前后台管理代码

unit Unitsuptab2;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGridEh, ExtCtrls, StdCtrls, Db, DBTables, Buttons, PrintAtOnces,
  PrnDbgeh, EHGrids;

type
  TFrmsuptab2 = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    Panel4: TPanel;
    DBGridEh1: TDBGridEh;
    Label1: TLabel;
    Querymain: TQuery;
    DataSource1: TDataSource;
    Image1: TImage;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    PrintDBGridEh1: TPrintDBGridEh;
    SpeedButton4: TSpeedButton;
    Database1: TDatabase;
    Querydelete: TQuery;
    QueryInsert: TQuery;
    Queryselect: TQuery;
    Querysupplier: TQuery;
    Querygrid: TQuery;
    procedure FormShow(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton4Click(Sender: TObject);
    procedure DBGridEh1CellClick(Column: TColumnEh);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Frmsuptab2: TFrmsuptab2;
  supplier:string;
implementation

uses Unit5, Unit1, Unitsuptab1;

{$R *.DFM}

procedure TFrmsuptab2.FormShow(Sender: TObject);
var
 curdep,tmpsql,curyear:string;
 year,month,day:word;
begin
 DecodeDate(now,year,month,day);
 curyear:=Inttostr(year);
 curdep:='商场';

  with querygrid do
 begin
  tmpsql:='select * from SuppliersSaleReportBase';
  close;
  sql.clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
    messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
    exit;
  end;
 end;
 dbgrideh1.Columns[0].FieldName:='供货商';
 dbgrideh1.Columns[1].FieldName:='当月销售额';
 dbgrideh1.Columns[2].FieldName:='当月销项税';
 dbgrideh1.Columns[3].FieldName:='当月商品成本';
 dbgrideh1.Columns[4].FieldName:='当月毛利润';
 dbgrideh1.Columns[5].FieldName:='去年同期销售额';
 dbgrideh1.Columns[6].FieldName:='去年同期毛利润';
 dbgrideh1.Columns[7].FieldName:='前年同期销售额';
 dbgrideh1.Columns[8].FieldName:='前年同期毛利润';
end;

procedure TFrmsuptab2.SpeedButton3Click(Sender: TObject);
begin
 frmsuptab2.Close;
 frmsuptab2.Release;
end;

procedure TFrmsuptab2.SpeedButton2Click(Sender: TObject);
begin
 printdbgrideh1.Preview;
end;

procedure TFrmsuptab2.SpeedButton4Click(Sender: TObject);
var
 curyear,curmonth,curday,tmpsql,tmpname1,tmpname2,tmpname3:string;
 tmpmlr,tmpfax,tmpcbje,tmpxsje:real;

 year,month,day:word;
begin
 //显示进度条
  Application.CreateForm(TFrmProgress,FrmProgress);
  FrmProgress.Show;
  Application.ProcessMessages;
  
 Decodedate(now,year,month,day);
 curyear:='%'+inttostr(year)+'%';
 curday:=formatdatetime('yyyy-mm-dd',now);
 tmpname1:='零售';
 tmpname2:='批发';

 {
 //清除本年度表中的数据
  with querydelete do
  begin
   tmpsql:='delete from SuppliersSaleReportBase';
   close;
   sql.clear;
   sql.add(tmpsql);
   prepare;
   try
    execsql;
   except
    messagebox(frmdep.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
    exit;
   end;
  end;
  }
 //先把部门名称写入表中
 with queryinsert do
   begin
    tmpsql:='Insert Into SuppliersSaleReportBase(供货商) select distinct 供货商 from goodsbase where 供货商 NOT IN(select 供货商 from SuppliersSaleReportbase)';
    close;
    sql.clear;
    sql.add(tmpsql);
    prepare;
    try
     execsql;
    except
     messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
    end;
 end;
  //进度条
  FrmProgress.ProgressBar.StepIt;
  //取物流库中的不同商品编码
 with querymain do
  begin
   tmpsql:='select distinct 条码 from goodsflowbase where 操作日期='''+curday+''' and ';
   tmpsql:=tmpsql+' 物流目标='''+tmpname1+''' or 物流目标='''+tmpname2+'''';
   close;
   sql.clear;
   sql.add(tmpsql);
   prepare;
   try
     open;
    except
     messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
    end;
  end;
 FrmProgress.ProgressBar.StepIt;
 if FrmProgress.ProgressBar.Position=FrmProgress.ProgressBar.Max then FrmProgress.ProgressBar.Position:=FrmProgress.ProgressBar.Min;
 //计算各厂商的本年度成本金额和销售金额
 while not querymain.Eof do
 begin
  //取厂商名称
  with querysupplier do
  begin
   tmpsql:='select 供货商 from goodsbase where 条码='''+querymain['条码']+'''';
   close;
   //showmessage(sql.Text);
   sql.Clear;
   sql.add(tmpsql);
   prepare;
   try
     open;
    except
     messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
    end;
  end;
  if querysupplier['供货商']=NULL then
   begin
    querymain.Next;
    continue;
   end;
  //进度条
   FrmProgress.ProgressBar.StepIt;
  if FrmProgress.ProgressBar.Position=FrmProgress.ProgressBar.Max then FrmProgress.ProgressBar.Position:=FrmProgress.ProgressBar.Min;
  //取数据
  with queryselect do
  begin

  tmpsql:='select sum(成本金额) as 成本金额,sum(销售金额) as 销售金额 from GoodsFlowBase where 操作日期 like '''+curyear+''' and ';
  tmpsql:=tmpsql+' 条码='''+querymain['条码']+''' and 物流目标='''+tmpname1+''' or 物流目标='''+tmpname2+'''';
  close;
  sql.clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
   messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
   exit;
  end;
  end;
 //把数据写入SuppliersSaleReportbase表
  while not queryselect.Eof do
   begin
    //计算进项税与毛利润
    if queryselect['销售金额']=NULL then
      tmpxsje:=0
    else
      tmpxsje:=queryselect['销售金额'];
    if queryselect['成本金额']=NULL THEN
      tmpcbje:=0
    else
      tmpcbje:=queryselect['成本金额'];
    tmpfax:=tmpxsje*0.1453;            //销项税
    tmpmlr:=tmpxsje-tmpcbje-tmpfax;   //毛利润
    //进度条
   FrmProgress.ProgressBar.StepIt;
  if FrmProgress.ProgressBar.Position=FrmProgress.ProgressBar.Max then FrmProgress.ProgressBar.Position:=FrmProgress.ProgressBar.Min;
    //插库
    with queryinsert do
    begin
    tmpsql:='Update SuppliersSaleReportBase set 当月销售额='''+floattostr(tmpxsje)+''',当月销项税='''+floattostr(tmpfax)+''',';
    tmpsql:=tmpsql+'当月商品成本='''+floattostr(tmpcbje)+''',当月毛利润='''+floattostr(tmpmlr)+''',更新日期='''+curday+''' where  供货商='''+querysupplier['供货商']+'''';
    close;
    sql.clear;
    sql.add(tmpsql);
    prepare;
    try
     execsql;
    except
     messagebox(frmsuptab2.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
    end;
    end;
    queryselect.Next;
   end;
   //进度条
   FrmProgress.ProgressBar.StepIt;
   if FrmProgress.ProgressBar.Position=FrmProgress.ProgressBar.Max then FrmProgress.ProgressBar.Position:=FrmProgress.ProgressBar.Min;
   querymain.Next;
 end;
 //进度完成
  FrmProgress.ProgressBar.Position:=FrmProgress.ProgressBar.Max;
  FrmProgress.Close;
 //显示数据
 querygrid.Active:=false;
 querygrid.Active:=true;
end;

procedure TFrmsuptab2.DBGridEh1CellClick(Column: TColumnEh);

begin
 if column.FieldName='供货商' then
 begin
  supplier:=column.Field.Text;
  
  Application.CreateForm(Tfrmsuptab1,frmsuptab1);
  frmsuptab1.showmodal;
 end;
end;

end.