saletotalinfo.~pas

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

         begin
             tmpPrice[i-1]:=0;
             tmpPresentSum[i-1]:=0;
         end;
         for i:=1 to DBgrid.rowcount-2 do
         begin
             strSql:='SELECT * FROM  tmpOutTable  WHERE  ProductName="'+DBgrid.Cells[1,i]+'"';
             //showmessage(strSql);
             tmpPrice[i-1]:=GetFieldValueOfInt(query1,strSql,'Price');
             tmpPresentSum[i-1]:=GetFieldValueOfInt(query1,strSql,'PresentSum');
             //showmessage(floattostr(tmppresentsum[i-1]));
             //showmessage(floattostr(tmpprice[i-1]));
             DBgrid.Cells[8,i]:=FloatToStr(tmpPrice[i-1]*StrToFloat(Dbgrid.Cells[6,i])+tmpPresentSum[i-1]);

         end;
         ////计算实际回款/////
         for i:=1 to DBgrid.RowCount-2 do
         begin
             strSql:='  productName="'+DBgrid.Cells[1,i]+'"  and  PayDate>="'+DateToStr(BeginDate.Date)+'" and  PayDate<="'+ DateToStr(EndDate.Date)+'"  and  city="'+trim(combobox1.text)+'"';
             DBgrid.Cells[11,i]:=FloatToStr(GetTotalInfo(query1,'returnPayTable','PaySum',strSql));
         end;

         /////计算返回的盖金额/////////
         strSql1:='  returnDate>="'+DateToStr(BeginDate.Date)+'" and  returnDate<="'+ DateToStr(EndDate.Date)+'" and  city="'+trim(combobox1.Text)+'"';
         DBgrid.cells[9,DBgrid.rowcount-2]:=FloatTostr(GetTotalInfo(query1,'returnLId','returnSum',strSql1));
         /////添加编号///////
         for i:=1 to DBgrid.RowCount-1 do
         begin
             DBgrid.Cells[0,i]:=IntToStr(i);
         end;
         ////计算总计/////
         tmpTotal[0]:=0;
         tmpTotal[1]:=0;
         tmpTotal[2]:=0;
         tmpTotal[3]:=0;
         tmpTotal[4]:=0;
         tmpTotal[5]:=0;
         tmpTotal[6]:=0;
         tmpTotal[7]:=0;
         tmpTotal[8]:=0;
         tmpTotal[9]:=0;
         tmpTotal[10]:=0;
         tmpTotal[11]:=0;
         for i:=1 to DBgrid.rowcount-2 do
         begin
             if DBgrid.cells[2,i]<>'' then
             begin
                tmpTotal[0]:=tmpTotal[0]+strToFloat(DBgrid.cells[2,i]);
             end;
              if DBgrid.cells[3,i]<>'' then
             begin
                tmpTotal[1]:=tmpTotal[1]+strToFloat(DBgrid.cells[3,i]);
             end;
              if DBgrid.cells[4,i]<>'' then
             begin
                tmpTotal[2]:=tmpTotal[2]+strToFloat(DBgrid.cells[4,i]);
             end;
              if DBgrid.cells[5,i]<>'' then
             begin
                tmpTotal[3]:=tmpTotal[3]+strToFloat(DBgrid.cells[5,i]);
             end;
              if DBgrid.cells[6,i]<>'' then
             begin
                tmpTotal[4]:=tmpTotal[4]+strToFloat(DBgrid.cells[6,i]);
             end;
              if DBgrid.cells[7,i]<>'' then
             begin
                tmpTotal[5]:=tmpTotal[5]+strToFloat(DBgrid.cells[7,i]);
             end;
              if DBgrid.cells[8,i]<>'' then
             begin
                tmpTotal[6]:=tmpTotal[6]+strToFloat(DBgrid.cells[8,i]);
             end;
              if DBgrid.cells[9,i]<>'' then
             begin
                tmpTotal[7]:=tmpTotal[7]+strToFloat(DBgrid.cells[9,i]);
             end;
              if DBgrid.cells[10,i]<>'' then
             begin
                tmpTotal[8]:=tmpTotal[8]+strToFloat(DBgrid.cells[10,i]);
             end;
              if DBgrid.cells[11,i]<>'' then
             begin
                tmpTotal[9]:=tmpTotal[9]+strToFloat(DBgrid.cells[11,i]);
             end;
         end;
         DBgrid.Cells[1,DBgrid.RowCount-1]:='合计:';
         DBgrid.Cells[2,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[0]);
         DBgrid.Cells[3,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[1]);
         DBgrid.Cells[4,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[2]);
         DBgrid.Cells[5,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[3]);
         DBgrid.Cells[6,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[4]);
         DBgrid.Cells[7,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[5]);
         DBgrid.Cells[8,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[6]);
         DBgrid.Cells[9,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[7]);
         DBgrid.Cells[10,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[8]);
         DBgrid.Cells[11,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[9]);
         /////添加所属市////
         for i:=1 to DBgrid.rowcount-2 do
         begin
             DBgrid.Cells[12,i]:=trim(combobox1.text);
         end;
         /////计算原来库存数量(现库存+销量-进货量)
         for i:=1 to DBgrid.RowCount-2 do
         begin
             DBgrid.Cells[2,i]:=FloatToStr(strToFloat(DBgrid.Cells[6,i])+strToFloat(DBgrid.Cells[10,i])-strToFloat(DBgrid.Cells[3,i]));
         end;
         ////计算酒店应回款/////
         strInsert:='DELETE * FROM  ReportSaleTotal';
         RunNonSql(query1,strInsert);
         /////将结果存储到报表中////
         for i:=1 to DBgrid.rowcount-1 do
         begin
             strInsert:='Insert Into  ReportSaleTotal(code,productName,beforeStore,stockNum,PayMoney,saleNum,realSaleNum,presentNum,returnPaySum,returnLidSum,NowStore,realReturnPaySum,city) 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]+'","';
             strInsert:=strInsert+Dbgrid.Cells[9,i]+'","'+Dbgrid.Cells[10,i]+'","'+Dbgrid.Cells[11,i]+'","'+Dbgrid.Cells[12,i]+'")';
             //Showmessage(strInsert);
             RunNonSql(query1,strInsert);
         end;

        Screen.Cursor:=Save_cursor;
end;////结束

procedure TSaleTotal.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 TSaleTotal.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('实际回款');
      TitleName.Add('所属市');
      //TitleName.Add('搭赠金额');
      SetReportTitle('2005年----2004年度销售情况报表');
      SetTableName('ReportSaleTotal'); ////设置报表的表名
      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]:=80;
      ColumnWidth[3]:=60;
      ColumnWidth[4]:=60;
      ColumnWidth[5]:=80;
      ColumnWidth[6]:=60;
      ColumnWidth[7]:=80;
      ColumnWidth[8]:=80;
      ColumnWidth[9]:=80;
      ColumnWidth[10]:=80;
      ColumnWidth[11]:=70;
      ColumnWidth[12]:=80;
      ColumnWidth[13]:=70;
      //ColumnWidth[14]:=80;
      ////计算表格水平线的宽度
      lineWidth:=0;
      for i:=1 to GetFieldCount(AdoTable1) do
      begin
          LineWidth:=LineWidth+ColumnWidth[i];
      end;
      SetIntegerCol(true);
      SetHorizontalLineWidth(LineWidth); ///设置表格水平线的宽度
      CreateList(sender,AdoTable1,picbutton5,colwidth,columnWidth);

end;

procedure TSaleTotal.PicButton1Click(Sender: TObject);
var
  v,sheet,range:variant;
  i,j:integer;
  TitleName:string;
  Query2:TAdoquery;
  strSql:string;
  FileName:string;
begin
     v:=createoleobject('excel.application');
     v.visible:=true;
     v.workbooks.add(-4167);
     v.workbooks[1].sheets[1].name:='test';
     sheet:=v.workbooks[1].sheets['test'];
     range:=Sheet.range[sheet.cells[1,1],sheet.cells[1,13]];
     //sheet.caption:='sdfgsdfsd';
     range.select;
     range.merge;
     range.HorizontalAlignment:=3;
     range.font.size:=24;
     range.font.bold:=2;
     TitleName:=DateTostr(beginDate.Date)+'---'+DateTostr(EndDate.Date)+'销售情况汇总表';
     Sheet.range[sheet.cells[1,1],sheet.cells[1,13]]:=TitleName;

        strSql:='SELECT * FROM  ReportSaleTotal';
        Query2:=RunSqlOfAdo(query1,strsql);
        for i:=1 to DBgrid.RowCount do
        begin
            for j:=1 to 13 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,4]:='审核人:';
     v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,9]:='制表日期:';
    range:=Sheet.range[sheet.cells[2,1],sheet.cells[DBgrid.RowCount+1,13]];
    range.select;

    range.borders.linestyle:=xlcontinuous;
    //range.borders.linewidth:=3;
    range.font.size:=14;
    //sheet.charttype:=
    Filename:=extractFilePath(paramStr(0))+'\saleTotal.xls';
    {if FileExists(FileName) then
    begin
       DeleteFile(FileName);
       v.workbooks[1].saveas(FileName);
    end
    else
       v.workbooks[1].saveas(FileName);
       }
end;

end.