unitdeptab1.pas

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

unit Unitdeptab1;

interface

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

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

var
  Frmdeptab1: TFrmdeptab1;
  selectedMonth:string;
implementation
 uses Unitdeptab2, Unit1;
{$R *.DFM}

procedure TFrmdeptab1.DBGridEh1CellClick(Column: TColumnEh);
begin
if column.FieldName='月份' then
 begin
  selectedMonth:=column.Field.Text;
  
  Application.CreateForm(Tfrmdeptab2,frmdeptab2);
  frmdeptab2.showmodal;
 end;
end;

procedure TFrmdeptab1.FormShow(Sender: TObject);
var
 depcode,tmpsql,curyear,curmonth,curday:string;
 year,month,day:word;
 i:integer;
begin
 DecodeDate(now,year,month,day);
 curyear:=Inttostr(year);
//制表
 { for i:=1 to 12 do
   begin
    if length(inttostr(i))=1 then
     curmonth:='0'+inttostr(i)
    else
     curmonth:=inttostr(i);
    with queryinsert do
    begin
     tmpsql:='Insert Into ShopSaleReportBase(年份,月份,部门编码) Values(';
     tmpsql:=tmpsql+''''+curyear+''','''+curmonth+''',''商场'')';
     close;
     sql.clear;
     sql.Add(tmpsql);
     prepare;
     try
      execsql;
     except
       messageBox(form1.handle,'数据库连接失败','错误',MB_OK+MB_ICONERROR);
       exit;
     end;
    end;
  end;
 }
 with querydep do
 begin
  tmpsql:='Select 部门名称 from DeptNameBase where 部门名称 NOT IN(select 部门编码 from ShopSaleReportBase)';
  close;
  sql.Clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
   messageBox(form1.handle,'数据库连接失败','错误',MB_OK+MB_ICONERROR);
   exit;
  end;
 end;
 while not querydep.Eof do
 begin
  for i:=1 to 12 do
   begin
    if length(inttostr(i))=1 then
     curmonth:='0'+inttostr(i)
    else
     curmonth:=inttostr(i);
    with queryinsert do
    begin
     tmpsql:='Insert Into ShopSaleReportBase(年份,月份,部门编码) Values(';
     tmpsql:=tmpsql+''''+curyear+''','''+curmonth+''','''+querydep['部门名称']+''')';
     close;
     sql.clear;
     sql.Add(tmpsql);
     prepare;
     try
      execsql;
     except
       messageBox(form1.handle,'数据库连接失败','错误',MB_OK+MB_ICONERROR);
       exit;
     end;
    end;
   end;
    querydep.Next;
 end;

 DecodeDate(now,year,month,day);
 curyear:=Inttostr(year);
 label2.Caption:='制表日期：'+inttostr(year)+'年'+inttostr(month)+'月'+inttostr(day)+'日';
 depcode:='商场';
 with queryselect do
 begin
  tmpsql:='select * from shopSaleReportBase where 年份='''+curyear+''' and 部门编码='''+depcode+''' order by 月份';
  close;
  sql.clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
   messageBox(form1.Handle,'数据库连接失败','错误',MB_OK+MB_ICONERROR);
   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:='前年同期销售额';
 dbgrideh1.Columns[9].FieldName:='前年同期毛利润';
end;

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

 year,month,day:word;

begin
 curday:=formatdatetime('yyyy-mm-dd',now);
 Decodedate(now,year,month,day);
 curyear:=inttostr(year);
 curmonth:=inttostr(month);
 tmpname1:='零售';
 tmpname2:='批发';
 tmpname3:='商场';
 //计算商场的本年度成本金额和销售金额
 with query1 do
  begin

  tmpsql:='select sum(成本金额) as 成本金额,sum(销售金额) as 销售金额 from GoodsFlowBase where 操作日期='''+curday+''' and ';
  tmpsql:=tmpsql+' 物流目标='''+tmpname1+''' or 物流目标='''+tmpname2+'''';
  close;
  sql.clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
   messagebox(frmdeptab1.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
   exit;
  end;
  end;
 //把数据写入shopsaleReportbase表
  while not query1.Eof do
   begin
    //计算进项税与毛利润
    if query1['销售金额']=null then
     tmpxsje:=0
    else
     tmpxsje:=query1['销售金额'];
    if query1['成本金额']=null then
     tmpcbje:=0
    else
     tmpcbje:=query1['成本金额'];
    tmpfax:=tmpxsje*0.1453;            //进项税
    tmpmlr:=tmpxsje-tmpcbje-tmpfax;   //毛利润
    //插库
    with queryinsert do
    begin

    tmpsql:='Update ShopSaleReportBase set 当月销售额='''+floattostr(tmpxsje)+''',当月销项税='''+floattostr(tmpfax)+''',';
    tmpsql:=tmpsql+'当月商品成本='''+floattostr(tmpcbje)+''',当月毛利润='''+floattostr(tmpmlr)+''',更新日期='''+curday+''' where 年份='''+curyear+''' and 月份='''+curmonth+''' and 部门编码='''+tmpname3+'''';
    close;
    sql.clear;
    sql.add(tmpsql);
    prepare;
     try
      execsql;
     except
      messagebox(frmdeptab1.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
     end;
    end;
    query1.Next;
   end;
 //取不同的部门编码
 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(frmdeptab1.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
   exit;
  end;
 end;
 //计算各部门的本年度成本金额和销售金额
 while not querymain.Eof do
 begin
  with query1 do
  begin

  tmpsql:='select sum(成本金额) as 成本金额,sum(销售金额) as 销售金额 from GoodsFlowBase where 操作日期='''+curday+''' and 部门编码='''+querymain['部门编码']+'''';
  tmpsql:=tmpsql+' 物流目标='''+tmpname1+''' or 物流目标='''+tmpname2+'''';
  close;
  sql.clear;
  sql.add(tmpsql);
  prepare;
  try
   open;
  except
   messagebox(form1.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
   exit;
  end;
  end;
 //把数据写入shopsaleReportbase表
  while not query1.Eof do
   begin
    //计算进项税与毛利润
    if query1['销售金额']=null then
     tmpxsje:=0
    else
     tmpxsje:=query1['销售金额'];
    if query1['成本金额']=null then
     tmpcbje:=0
    else
     tmpcbje:=query1['成本金额'];
    tmpfax:=tmpxsje*0.1453;            //销项税
    tmpmlr:=tmpxsje-tmpcbje-tmpfax;   //毛利润
    //插库
    with queryinsert do
    begin
    tmpsql:='Update ShopSaleReportBase set 当月销售额='''+floattostr(tmpxsje)+''',当月销项税='''+floattostr(tmpfax)+''',';
    tmpsql:=tmpsql+'当月商品成本='''+floattostr(tmpcbje)+''',当月毛利润='''+floattostr(tmpmlr)+''',更新日期='''+curday+''' where 年份='''+curyear+''' and 月份='''+curmonth+''' and 部门编码='''+querymain['部门编码']+'''';
    close;
    sql.clear;
    sql.add(tmpsql);
    prepare;
    try
     execsql;
    except
     messagebox(frmdeptab1.handle,'数据库读写错误！','错误',MB_OK+MB_ICONINFORMATION);
     exit;
    end;
    end;
    query1.Next;
   end;
   querymain.Next;
 end;
 //显示数据
 queryselect.Active:=false;
 queryselect.Active:=true;
end;

procedure TFrmdeptab1.SpeedButton3Click(Sender: TObject);
begin
 frmdeptab1.close;
 frmdeptab1.Release;
end;

procedure TFrmdeptab1.SpeedButton2Click(Sender: TObject);
var
 tmpdate:string;
begin
 tmpdate:=label2.caption;
 printdbgrideh1.Title.Clear;
 printdbgrideh1.Title.Append(tmpdate);
 printdbgrideh1.Preview;
end;

end.