providerprofitinfo.pas

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

                query3:=RunSqlOfAdo(query1,strSearch);
                if not query3.FieldByName('Amount').IsNull then
                begin
                   DBgrid.Cells[5,i+(Product_count+1)*(j-1)]:=query3.FieldValues['Amount'];
                end
                else
                   DBgrid.Cells[5,i+(Product_count+1)*(j-1)]:='0';
            end;
         end;
         ////得到销量对应的金额/////
          for j:=1 to  Trader_count do
         begin
            for i:=1 to Product_count do
            begin
                strSearch:='SELECT  SUM(saleSum) as saleSum  FROM  tmpOutTable  WHERE  productName="'+dbgrid.Cells[2,i+(Product_count+1)*(j-1)]+'"  and  TraderName="'+dbgrid.Cells[1,i+(Product_count+1)*(j-1)]+'"';
                query3:=RunSqlOfAdo(query1,strSearch);
                if not query3.FieldByName('saleSum').IsNull then
                begin
                   DBgrid.Cells[6,i+(Product_count+1)*(j-1)]:=query3.FieldValues['saleSum'];
                end
                else
                   DBgrid.Cells[6,i+(Product_count+1)*(j-1)]:='0';
            end;
         end;
         ////计算差价////////////////
         for j:=1 to  Trader_count do
         begin
            for i:=1 to Product_count do
            begin
                 tmpTotal[0]:=strToFloat(DBgrid.Cells[4,i+(Product_count+1)*(j-1)])*strToFloat(DBgrid.Cells[5,i+(Product_count+1)*(j-1)]);
                 tmpTotal[1]:=strToFloat(DBgrid.Cells[3,i+(Product_count+1)*(j-1)])*strToFloat(DBgrid.Cells[5,i+(Product_count+1)*(j-1)]);
                 DBgrid.Cells[7,i+(Product_count+1)*(j-1)]:=FloatTostr((tmpTotal[0]-tmpTotal[1]));
            end;
         end;
         ////添加所属市///
         for i:=1 to DBgrid.RowCount-1 do
         begin
             DBgrid.Cells[8,i]:=trim(combobox1.text);
         end;
         ////计算各自合计//////
         for j:=1 to  Trader_count do
         begin
            tmpTotal[2]:=0;
            tmpTotal[3]:=0;
            tmpTotal[4]:=0;
            for i:=1 to Product_count do
            begin
                tmpTotal[2]:=tmpTotal[2]+strToFloat(DBgrid.Cells[5,i+(Product_count+1)*(j-1)]);
                tmpTotal[3]:=tmpTotal[3]+strToFloat(DBgrid.Cells[6,i+(Product_count+1)*(j-1)]);
                tmpTotal[4]:=tmpTotal[4]+strToFloat(DBgrid.Cells[7,i+(Product_count+1)*(j-1)]);
            end;
            Dbgrid.cells[5,(Product_count+1)*j]:=FloatToStr(tmpTotal[2]);
            Dbgrid.cells[6,(Product_count+1)*j]:=FloatToStr(tmpTotal[3]);
            Dbgrid.cells[7,(Product_count+1)*j]:=FloatToStr(tmpTotal[4]);
         end;
         for j:=1 to trader_count do
         begin
             Dbgrid.cells[1,(Product_count+1)*j]:='小计';
             //Dbgrid.cells[1,(Product_count+1)*j]:=t
         end;
         ////添加编号////
         for i:=1 to DBgrid.RowCount-1 do
         begin
             Dbgrid.Cells[0,i]:=IntTostr(i);
         end;
         ////计算总合计/////
         for i:=1 to trader_count do
         begin
             tmpTotal[5]:=tmpTotal[5]+strToFloat(Dbgrid.cells[5,(Product_count+1)*i]);
             tmpTotal[6]:=tmpTotal[6]+strToFloat(Dbgrid.cells[6,(Product_count+1)*i]);
             tmpTotal[7]:=tmpTotal[7]+strToFloat(Dbgrid.cells[7,(Product_count+1)*i]);
         end;
         DBgrid.Cells[1,DBgrid.RowCount-1]:='总计:';
         DBgrid.Cells[5,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[5]);
         DBgrid.Cells[6,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[6]);
         DBgrid.Cells[7,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[7]);
         ////添加报表数据////
         strDelete:='DELETE *  FROM  ReportProfit';
         RunNonSql(query1,strDelete);
         for i:=1 to DBgrid.RowCount-1 do
         begin
             strInsert:='INSERT INTO  ReportProfit(code,traderName,productName,stockPrice,salePrice,saleNum,saleSum,subPrice,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]+'")';
             RunNonSql(query1,strInsert);
         end;
         Screen.Cursor:=Save_cursor;
end;

procedure TProviderProfit.DBgridDrawCell(Sender: TObject; ACol,
  ARow: Integer; Rect: TRect; State: TGridDrawState);
  var
    i:integer;
begin
     ////定制小计的颜色以及字体////
     for i:=1 to trader_count do
     begin
        if (Arow=(Product_count+1)*i) then
        begin
           DBgrid.Canvas.Brush.color:=rgb(255,255,206);
           dbgrid.Canvas.Brush.Style:=bssolid;
           dbgrid.Canvas.Font.Size:=10;
           dbgrid.canvas.Font.Style:=[fsbold];
           dbgrid.Canvas.FillRect(Rect);
           DBgrid.Canvas.TextOut(rect.left,rect.Top,dbgrid.Cells[Acol,Arow]);
       end;
     end;
     ////设置总计的颜色以及字体////
     if (Arow=Dbgrid.rowcount-1) then
     begin
         DBgrid.Canvas.Brush.color:=rgb(99,154,156);
         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 TProviderProfit.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('搭赠金额');
      SetReportTitle('二级商销售利润情况报表');
      SetTableName('ReportProfit'); ////设置报表的表名
      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]:=90;
      ColumnWidth[3]:=90;
      ColumnWidth[4]:=70;
      ColumnWidth[5]:=70;
      ColumnWidth[6]:=90;
      ColumnWidth[7]:=100;
      ColumnWidth[8]:=80;
      ColumnWidth[9]:=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 TProviderProfit.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:='saleProfit';
     sheet:=v.workbooks[1].sheets['saleProfit'];
     range:=Sheet.range[sheet.cells[1,1],sheet.cells[1,9]];
     //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,9]]:=TitleName;
        for i:=1 to DBgrid.RowCount do
        begin
            for j:=1 to 9 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,7]:='制表日期:';
    range:=Sheet.range[sheet.cells[2,1],sheet.cells[DBgrid.RowCount+1,9]];
    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.