saletotalinfo.~pas

是一款针对啤酒行业的销售进销存管理系统,包括基本的进货,销售,库存

unit SaleTotalInfo;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, ComCtrls, Grids, PicButton, ExtCtrls,Quickrpt,
  QRExtra,COMOBJ,excel97,excel2000;

type
  TSaleTotal = class(TForm)
    Image1: TImage;
    Label1: TLabel;
    Image2: TImage;
    Bevel1: TBevel;
    Bevel2: TBevel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    DBgrid: TStringGrid;
    BeginDate: TDateTimePicker;
    EndDate: TDateTimePicker;
    ComboBox1: TComboBox;
    Query1: TADOQuery;
    ADOTable1: TADOTable;
    PicButton4: TPicButton;
    PicButton5: TPicButton;
    PicButton1: TPicButton;
    //procedure CreateList(Sender:TObject);
    procedure Image2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure PicButton4Click(Sender: TObject);
    procedure DBgridDrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure PicButton5Click(Sender: TObject);
    procedure PicButton1Click(Sender: TObject);
  private
    { Private declarations }
     FReport:TCustomQuickRep;
    CreateListReport:TQuickRep;
    procedure SetReport(Value:TCustomQuickRep);
  public
    { Public declarations }
    property Report:TcustomQuickRep read FReport write SetReport;
  end;

var
  SaleTotal: TSaleTotal;

implementation

{$R *.dfm}
uses common,saleTotalrptInfo,PrinterRpt;
/////设置报表////
procedure TsaleTotal.SetReport(Value:TCustomQuickRep);
begin
     FReport:=Value;
end;
{procedure TsaleTotal.CreateList(Sender: TObject);
var
  aReport : TCustomQuickRep;
  SomeFields: TStringList;
  MyTable: TAdoTable;
  nIdx: integer;
begin
  MyTable := TAdoTable.Create(self);
  SomeFields := TStringList.Create;
  with MyTable do
  begin
    ConnectionString:=ConnectToDatabase;
    TableName := 'tmpOutTable';
    ReadOnly := True;
    Active := True;
    for nIdx := 0 to FieldCount - 1 do
      SomeFields.Add(Fields[nIdx].FieldName);
  end;
  areport := nil;
  with MyTable.Fields[1] do
    DisplayWidth := DisplayWidth div 2;
  // create the report
  QRCreateList(aReport, nil, MyTable, 'Country Listing', SomeFields);
  // Make the column header's font use bold attribute
  aReport.Bands.ColumnHeaderBand.Font.Style := [fsBold];
  // Now adjust the spacing of the fields.  There isn't any reason to
  // do this, this is just to show how to access the controls on the
  // report.
  for nIdx := 0 to aReport.Bands.ColumnHeaderBand.ControlCount -1 do
    if aReport.Bands.ColumnHeaderBand.Controls[nIdx] is TQRPrintable then
      with TQRPrintable(aReport.Bands.ColumnHeaderBand.Controls[nIdx]) do
        Left := Left - (5 * nIdx);

  for nIdx := 0 to aReport.Bands.DetailBand.ControlCount -1 do
    if aReport.Bands.DetailBand.Controls[nIdx] is TQRPrintable then
      with TQRPrintable(aReport.Bands.DetailBand.Controls[nIdx]) do
        Left := Left - (5 * nIdx);
  if sender = Picbutton5 then
  begin
     areport.preview;
  end;

  aReport.Free;
  MyTable.Free;
  SomeFields.Free;
end;
}
procedure TSaleTotal.Image2Click(Sender: TObject);
begin
  close;
end;

procedure TSaleTotal.FormCreate(Sender: TObject);
begin
    ShowPloyForm(image1.Picture.Bitmap,handle);
     combobox1.Items.Add('辽阳');
       combobox1.Items.Add('沈阳');
       combobox1.Items.Add('本溪');
       combobox1.Items.Add('大连');
       combobox1.Items.Add('鞍山');
       combobox1.Items.Add('抚顺');
       combobox1.Items.Add('丹东');
       combobox1.Items.Add('北京');
       combobox1.text:='辽阳';
       ////设置Dbgrid列标题////
       Dbgrid.Cells[0,0]:='编号';
       Dbgrid.ColWidths[0]:=50;
       dbgrid.Cells[1,0]:='产品名称';
       Dbgrid.ColWidths[1]:=80;
       dbgrid.Cells[2,0]:='原库存';
       Dbgrid.Cells[3,0]:='进货数量';
       dbgrid.ColWidths[2]:=70;
       dbgrid.Cells[4,0]:='应付厂金额';
       Dbgrid.Cells[5,0]:='销售量';
       dbgrid.Cells[6,0]:='实际销量';
       Dbgrid.Cells[7,0]:='搭赠数量';
       DBgrid.ColWidths[6]:=70;
       Dbgrid.Cells[8,0]:='酒店应回款';
       dbgrid.Cells[9,0]:='返盖金额';
       Dbgrid.Cells[10,0]:='现库存';
       dbgrid.Cells[11,0]:='实际回款';
       dbgrid.Cells[12,0]:='所属市';
       CreateListReport:=TQuickRep.create(self);
end;

procedure TSaleTotal.PicButton4Click(Sender: TObject);
var
  query2,Query3:TAdoquery;
  strSearch,strSearch1:string;
  Product_count:integer;
  i:integer;
  FieldList,FieldList1:TStringList;
  strWhere:string;
  Save_cursor:Tcursor;
  strSql,strSql1:string;
  tmpTotal:Array[0..11] of Double;
  tmpPrice:Array of integer; ////存储产品单价
  tmpPresentSum:Array of double; ///存储产品搭赠金额
  strInsert:string;
begin
      Save_cursor:=Screen.Cursor;
      Screen.Cursor:=CrHourGlass;
      ////显示所有产品名称////

      strSearch:='SELECT ProductName as c2 FROM  (SELECT  DISTINCT ProductName from  ProductTable)';
      query2:=RunSqlOfAdo(query1,strSearch);
      if isRunSql(query1,strSearch) then
      begin
        DBgrid.RowCount:=query2.RecordCount+2;
        Product_count:=query2.RecordCount;
        i:=1; 
         while not Query2.Eof do
         begin
             DBgrid.Cells[1,i]:=query2.fieldByName('c2').AsString;
             query2.Next;
             inc(i);
         end;
      end;
       /////得到指定时间范围内的所有进货单号,并插入到临时表中////
         strSearch1:='SELECT * FROM  billTable WHERE  billDate>="'+DateToStr(BeginDate.Date)+'" and  billDate<="'+ DateToStr(EndDate.Date)+'" and  city="'+trim(combobox1.Text)+'"';
         //showmessage(strSearch1);
         FieldList:=TStringList.Create;
         //showmessage(strSearch);
         strWhere:='DELETE * FROM  TmpIntoTable';
         RunNonSql(query1,strWhere);
         query2:=RunSqlOfAdo(query1,strSearch1);
         if isRunSql(query1,strSearch1) then
         begin
            //Showmessage('bbb'+'aaaa');
            while not query2.Eof do
            begin
               FieldList.Add(query2.FieldValues['billCode']);
               query2.next;
            end;
            FieldList.Sort;
            strWhere:='INSERT INTO TmpintoTable SELECT * FROM  IntoTable WHERE billCode  BETWEEN  "'+FieldList[0]+'" and  "'+FieldList[FieldList.count-1]+'"';
            //showmessage(strWhere);
            RunNonSql(query1,strWhere);
         end
         else
            begin
                ShowMessage('无记录存在...');
                Screen.Cursor:=Save_cursor;
                exit;
            end;
         ///统计产品的进货数量//////
         for i:=1 to DBgrid.RowCount-2 do
         begin
             strSql:=' ProductName="'+DBgrid.Cells[1,i]+'"';
             DBgrid.cells[3,i]:=FloatTostr(GetTotalInfo(query1,'tmpIntoTable','ProductNum',strSql));
             DBgrid.cells[4,i]:=FloatTostr(GetTotalInfo(query1,'tmpIntoTable','ProductSum',strSql));
         end;
          /////得到指定时间范围内的所有销售单号,并插入到临时表中////
         strSearch1:='SELECT * FROM  saleTable WHERE  saleDate>="'+DateToStr(BeginDate.Date)+'" and  saleDate<="'+ DateToStr(EndDate.Date)+'" and  city="'+trim(combobox1.Text)+'"';
         //showmessage(strSearch1);
         FieldList1:=TStringList.Create;
         //showmessage(strSearch);
         strWhere:='DELETE * FROM  TmpOutTable';
         RunNonSql(query1,strWhere);
         query3:=RunSqlOfAdo(query1,strSearch1);
         if isRunSql(query1,strSearch1) then
         begin
            //Showmessage('bbb'+'aaaa');
            while not query3.Eof do
            begin
               FieldList1.Add(query3.FieldValues['saleCode']);
               query3.next;
            end;
            FieldList1.Sort;
            strWhere:='INSERT INTO TmpOutTable SELECT * FROM  OutTable WHERE saleCode  BETWEEN  "'+FieldList1[0]+'" and  "'+FieldList1[FieldList1.count-1]+'"';
            //showmessage(strWhere);
            RunNonSql(query1,strWhere);
         end
         else
            begin
                ShowMessage('无记录存在...');
                Screen.Cursor:=Save_cursor;
                exit;
            end;
        ////统计销售量以及销售金额///////////
        for i:=1 to DBgrid.RowCount-2 do
         begin
             strSql:=' ProductName="'+DBgrid.Cells[1,i]+'"';
             DBgrid.cells[6,i]:=FloatTostr(GetTotalInfo(query1,'tmpOutTable','Amount',strSql));
             //DBgrid.cells[11,i]:=FloatTostr(GetTotalInfo(query1,'tmpOutTable','saleSum',strSql));
             DBgrid.cells[7,i]:=FloatTostr(GetTotalInfo(query1,'tmpOutTable','PresentNum',strSql));
         end;
         ///计算现库存数量////
         for i:=1 to DBgrid.RowCount-2 do
         begin
             strSql:='  ProductName="'+DBgrid.Cells[1,i]+'"  and   city="'+trim(combobox1.Text)+'"';
             DBgrid.cells[10,i]:=FloatTostr(GetTotalInfo(query1,'storeTable','storeNum',strSql));
         end;
          ////计算应该的销售量==实际销售量+搭赠的数量////
         for i:=1 to DBgrid.RowCount-2 do
         begin
             DBgrid.Cells[5,i]:=IntTostr(strToInt(DBgrid.Cells[6,i])+strToInt(DBgrid.Cells[7,i]));
         end;
         /////计算酒店应回款==单价*实际销售量+搭赠金额///////////
          setLength(tmpPrice,DBgrid.RowCount-2);////动态设置数组的长度///
          setLength(tmpPresentSum,DBgrid.RowCount-2);
         for i:=1 to DBgrid.RowCount-2 do