areatotalinfo.~pas

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

unit AreaTotalInfo;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DB, ADODB, PicButton, ExtCtrls, StdCtrls, ComCtrls,COMObj,Excel97,Excel2000;

type
  TAreaTotal = class(TForm)
    Image1: TImage;
    Label1: TLabel;
    Image2: TImage;
    Bevel1: TBevel;
    Bevel2: TBevel;
    PicButton4: TPicButton;
    Query1: TADOQuery;
    DBgrid: TStringGrid;
    Label2: TLabel;
    BeginDate: TDateTimePicker;
    Label3: TLabel;
    EndDate: TDateTimePicker;
    Label4: TLabel;
    ComboBox1: TComboBox;
    PicButton5: TPicButton;
    ADOTable1: TADOTable;
    PicButton1: TPicButton;
    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 }
  public
    { Public declarations }
  end;

var
  AreaTotal: TAreaTotal;

implementation

{$R *.dfm}
uses common,printerRpt;
procedure TAreaTotal.Image2Click(Sender: TObject);
begin
  close;
end;

procedure TAreaTotal.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.Cells[1,0]:='所属市';
       Dbgrid.Cells[2,0]:='区域名称';
       dbgrid.Cells[3,0]:='销量';
       Dbgrid.Cells[4,0]:='总金额';
       dbgrid.Cells[5,0]:='搭赠数量';
       Dbgrid.Cells[6,0]:='搭赠金额';
       dbgrid.Cells[7,0]:='返盖数';
       Dbgrid.Cells[8,0]:='返盖金额';
       dbgrid.Cells[9,0]:='返瓶数';
       dbgrid.Cells[10,0]:='返瓶金额';
end;

procedure TAreaTotal.PicButton4Click(Sender: TObject);
var
  query2,Query3:TAdoquery;
  strSearch,strSearch1:string;
  FieldList:TStringList;
  i:integer;
  strWhere,strSql:string;
  tmpTotal:array[0..7] of double;
  strDelete,strInsert:string;
  Save_cursor:Tcursor;
begin
         ////得到所属市的所有区域名称
         Save_cursor:=Screen.Cursor;
         Screen.Cursor:=CrHourGlass;
         DBgrid.RowCount:=2; 
         strSearch:='SELECT *  FROM  sysTable  where  param1="'+trim(combobox1.Text)+'"';
         query2:=RunSqlOfAdo(query1,strSearch);
         if  isRunSql(query1,strSearch) then
         begin
             i:=1;
             while not query2.Eof do
             begin
                 //Dbgrid.cells[0,i]:=IntTostr(i);
                 Dbgrid.cells[1,i]:=trim(query2.FieldByName('param1').AsString);
                 Dbgrid.Cells[2,i]:=trim(query2.FieldByName('param0').AsString);
                 query2.Next;
                 inc(i);
                 dbgrid.RowCount:=dbgrid.rowcount+1; 
             end;
         end;
         /////得到指定时间范围内的所有销售单号////
         strSearch1:='SELECT * FROM  saleTable WHERE  saleDate>="'+DateToStr(BeginDate.Date)+'" and  saleDate<="'+ DateToStr(EndDate.Date)+'" and  city="'+trim(combobox1.Text)+'"';
         //showmessage(strSearch1);
         FieldList:=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
               FieldList.Add(query3.FieldValues['saleCode']);
               query3.next;
            end;
            FieldList.Sort;
            strWhere:='INSERT INTO TmpOutTable SELECT * FROM  OutTable WHERE saleCode  BETWEEN  "'+FieldList[0]+'" and  "'+FieldList[FieldList.count-1]+'"';
            //showmessage(strWhere);
            RunNonSql(query1,strWhere);
         end
         else
            begin
                ShowMessage('无记录存在...');
                Screen.Cursor:=Save_cursor;
                exit;
            end;
         /////计算销售表outTable中汇总 /////
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             strSql:=' areaName="'+Dbgrid.Cells[2,i]+'"';
             DBgrid.Cells[3,i]:=FloatToStr(GetTotalInfo(query1,'TmpOutTable','Amount',strSql));
             DBgrid.Cells[4,i]:=FloatToStr(GetTotalInfo(query1,'TmpOutTable','saleSum',strSql));
             DBgrid.Cells[5,i]:=FloatToStr(GetTotalInfo(query1,'TmpOutTable','PresentNum',strSql));
             DBgrid.Cells[6,i]:=FloatToStr(GetTotalInfo(query1,'TmpOutTable','PresentSum',strSql));
         end;
         ////计算返回盖的数量级金额/////
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             strSql:=' areaName="'+Dbgrid.Cells[2,i]+'" and  city="'+trim(combobox1.Text)+'" and  returnDate>="'+DateToStr(BeginDate.Date)+'" and  returnDate<="'+DateToStr(EndDate.Date)+'"';
             DBgrid.Cells[7,i]:=FloatToStr(GetTotalInfo(query1,'returnLid','returnNum',strSql));
             DBgrid.Cells[8,i]:=FloatToStr(GetTotalInfo(query1,'returnLid','returnSum',strSql));
             DBgrid.Cells[9,i]:=FloatToStr(GetTotalInfo(query1,'returnBottle','returnNum',strSql));
             DBgrid.Cells[10,i]:=FloatToStr(GetTotalInfo(query1,'returnBottle','returnSum',strSql));
         end;
         /////计算总计///
         for i:=1 to Dbgrid.rowcount-2 do
         begin
             tmpTotal[0]:=tmpTotal[0]+strToFloat(Dbgrid.Cells[3,i]);
             tmpTotal[1]:=tmpTotal[1]+strToFloat(Dbgrid.Cells[4,i]);
             tmpTotal[2]:=tmpTotal[2]+strToFloat(Dbgrid.Cells[5,i]);
             tmpTotal[3]:=tmpTotal[3]+strToFloat(Dbgrid.Cells[6,i]);
             tmpTotal[4]:=tmpTotal[4]+strToFloat(Dbgrid.Cells[7,i]);
             tmpTotal[5]:=tmpTotal[5]+strToFloat(Dbgrid.Cells[8,i]);
             tmpTotal[6]:=tmpTotal[6]+strToFloat(Dbgrid.Cells[9,i]);
             tmpTotal[7]:=tmpTotal[7]+strToFloat(Dbgrid.Cells[10,i]);
         end;
         dbgrid.Cells[1,dbgrid.RowCount-1]:='合计:';
         dbgrid.Cells[3,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[0]);
         dbgrid.Cells[4,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[1]);
         dbgrid.Cells[5,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[2]);
         dbgrid.Cells[6,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[3]);
         dbgrid.Cells[7,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[4]);
         dbgrid.Cells[8,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[5]);
         dbgrid.Cells[9,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[6]);
         dbgrid.Cells[10,dbgrid.RowCount-1]:=FloatTostr(tmpTotal[7]);
         ////添加序号//////
         for i:=1 to DBgrid.RowCount-1 do
         begin
             DBgrid.cells[0,i]:=IntToStr(i);
         end;
         /////添加报表数据/////////
         strDelete:='DELETE * FROM  ReportAreaTotal';
         RunNonSql(query1,strDelete);
         for i:=1 to DBgrid.RowCount-1 do
         begin
             strInsert:='INSERT INTO ReportAreaTotal(code,city,areaName,saleNum,saleSum,presentNum,presentSum,returnLidNum,returnLidSum,returnBottleNum,returnBottleSum) VALUES('+Dbgrid.Cells[0,i]+',"'+Dbgrid.Cells[1,i]+'","'+Dbgrid.Cells[2,i]+'","'+Dbgrid.Cells[3,i]+'","'+Dbgrid.Cells[4,i]+'","'+Dbgrid.Cells[5,i]+'","'+Dbgrid.Cells[6,i]+'","'+Dbgrid.Cells[7,i]+'","'+Dbgrid.Cells[8,i]+'","'+Dbgrid.Cells[9,i]+'","'+Dbgrid.Cells[10,i]+'")';
             RunNonSql(query1,strInsert);
         end;
        Screen.Cursor:=Save_cursor;  
end;

procedure TAreaTotal.DBgridDrawCell(Sender: TObject; ACol, ARow: Integer;
  Rect: TRect; State: TGridDrawState);
begin
      if (Arow=(Dbgrid.rowcount-1)) then
        begin
           DBgrid.Canvas.Brush.color:=rgb(255,255,206);
           dbgrid.Canvas.Brush.Style:=bssolid;
           dbgrid.Canvas.Font.Size:=11;
           dbgrid.canvas.Font.Style:=[fsbold];
         //dbgrid.Canvas.font.Color:=rgb(255,255,255);
           dbgrid.Canvas.FillRect(Rect);
           DBgrid.Canvas.TextOut(rect.left,rect.Top,dbgrid.Cells[Acol,Arow]);
        end;

end;

procedure TAreaTotal.PicButton5Click(Sender: TObject);
var
   TitleName:TStringList;
   ColWidth,ColumnWidth:Array of Integer;
   i:integer;
   LineWidth:integer;
begin
    TitleName:=TStringList.Create;
      //ColWidth:=TStringList.Create;
      TitleName.Add('编号');
      TitleName.Add('所属市');
      TitleName.Add('区域名称');
      TitleName.Add('销售量');
      TitleName.Add('销售金额');
      TitleName.Add('搭赠数量');
      TitleName.Add('搭赠金额');
      TitleName.Add('返盖数');
      TitleName.Add('返盖金额');
      TitleName.Add('返瓶数');
      TitleName.Add('返瓶金额');

      //TitleName.Add('搭赠金额');
      SetReportTitle('二级商区域汇总情况报表');
      SetTableName('ReportareaTotal'); ////设置报表的表名
      SetColumnTitle(TitleName);////设置报表列的标题
      /////设置动态数组的长度////
      setlength(colwidth,GetFieldCount(AdoTable1));
      setlength(columnwidth,GetFieldCount(AdoTable1)+1);
      for i:=0 to  GetFieldCount(AdoTable1)-1 do
      begin
          Colwidth[i]:=10;
          //ColumnWidth[i]:=60;
      end;
      ////设置输出内容的列宽度
      ColumnWidth[1]:=40;
      ColumnWidth[2]:=70;
      ColumnWidth[3]:=80;
      ColumnWidth[4]:=70;
      ColumnWidth[5]:=80;
      ColumnWidth[6]:=80;
      ColumnWidth[7]:=80;
      ColumnWidth[8]:=80;
      ColumnWidth[9]:=80;
      ColumnWidth[10]:=80;
      ColumnWidth[11]:=80;

      //ColumnWidth[14]:=80;
      ////计算表格水平线的宽度
      lineWidth:=0;
      for i:=1 to GetFieldCount(AdoTable1) do
      begin
          LineWidth:=LineWidth+ColumnWidth[i];
      end;
      SetHorizontalLineWidth(LineWidth); ///设置表格水平线的宽度
      SetIntegerCol(true);
      CreateList(sender,AdoTable1,picbutton5,colwidth,columnWidth);
end;

procedure TAreaTotal.PicButton1Click(Sender: TObject);
var
  v,sheet,range:variant;
  i,j:integer;
  TitleName:string;
  FileName:string;
begin
     v:=createoleobject('excel.application');
     v.visible:=true;
     v.workbooks.add(-4167);
     v.workbooks[1].sheets[1].name:='areaTotal';
     sheet:=v.workbooks[1].sheets['areaTotal'];
     range:=Sheet.range[sheet.cells[1,1],sheet.cells[1,11]];
     //sheet.caption:='sdfgsdfsd';
     range.select;
     range.merge;
     range.HorizontalAlignment:=3;
     range.font.size:=19;
     range.font.bold:=2;
     TitleName:=DateTostr(beginDate.Date)+'---'+DateTostr(EndDate.Date)+'销售区域汇总情况表';
     Sheet.range[sheet.cells[1,1],sheet.cells[1,11]]:=TitleName;
        for i:=1 to DBgrid.RowCount do
        begin
            for j:=1 to 11 do
            begin
                v.workbooks[1].sheets[1].cells[i+1,j]:=Dbgrid.Cells[j-1,i-1];
            end;
        end;
     

    //range.select;
    v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,1]:='制表人:';
    v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,5]:='审核人:';
     v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,9]:='制表日期:';
    range:=Sheet.range[sheet.cells[2,1],sheet.cells[DBgrid.RowCount+1,11]];
    range.select;

    range.borders.linestyle:=xlcontinuous;
    //range.borders.linewidth:=3;
    range.font.size:=14;
    //sheet.charttype:=
    Filename:=extractFilePath(paramStr(0))+'\saleTotal.xls';
end;

end.