traderfindinfo.~pas

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

                   dbgrid.Cells[5,i]:='';
                   dbgrid.Cells[6,i]:='';
                   dbgrid.Cells[7,i]:='';
                   dbgrid.Cells[8,i]:='';
                   dbgrid.Cells[9,i]:='';
                   dbgrid.Cells[10,i]:='';
                   dbgrid.Cells[11,i]:='';
                   dbgrid.Cells[12,i]:='';
                   dbgrid.Cells[13,i]:='';
                end;
                dbgrid.RowCount:=2;
                exit;
            end;


      end;
      //ShowMessage('w1:'+strWhere1);
      //Showmessage(Inttostr(strList1.count));
      if (strList.Count>0) then
      begin
          strWhere2:=DeleteLastSubstring(' and ',strSql);
      end;
      //ShowMessage('w2:'+strWhere2);
      //Showmessage(Inttostr(strList.count));
      if (strList1.count=0) and (strList.Count>0) then
      begin
         strSearch1:='SELECT * FROM outTable  where  '+strWhere2;
      end;
      if (strList1.count>0) and (strList.Count>0) then
      begin
         strSearch1:='SELECT * FROM  outTable  where  '+strWhere1+'  and  '+strWhere2;
      end;
      if (strList1.count>0) and (strList.Count=0) then
      begin
         strSearch1:='SELECT * FROM outTable  where  '+strWhere1;
      end;
      //Showmessage(strSearch1);
      //////////////
         for i:=1 to dbgrid.rowcount-2 do
         begin
           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]:='';
           dbgrid.Cells[11,i]:='';
           dbgrid.Cells[12,i]:='';
           dbgrid.Cells[13,i]:='';
        end;
        dbgrid.RowCount:=2;
      ///////////////
      ////#3333FillValueToListview(query1,strSearch1,0,dbgrid);
      ///////////////
       //////显示记录/////
      Query3:=RunSqlOfAdo(query1,strSearch1);
      if Query3.RecordCount<>0 then
      begin
         i:=1;
         //DBgrid.RowCount:=Query3.RecordCount+2;
         while not Query3.Eof do
         begin
            Dbgrid.Cells[0,i]:=query3.fieldByname('Salecode').AsString;
            Dbgrid.Cells[1,i]:=query3.fieldByname('TraderName').AsString;
            Dbgrid.Cells[2,i]:=query3.fieldByname('AreaName').AsString;
            Dbgrid.Cells[3,i]:=query3.fieldByname('ProductCode').AsString;
            Dbgrid.Cells[4,i]:=query3.fieldByname('ProductName').AsString;
            Dbgrid.Cells[5,i]:=query3.fieldByname('Rule').AsString;
            Dbgrid.Cells[6,i]:=query3.fieldByname('Capacity').AsString;
            Dbgrid.Cells[7,i]:=query3.fieldByname('Unit').AsString;
            Dbgrid.Cells[8,i]:=query3.fieldByname('Price').AsString;
            Dbgrid.Cells[9,i]:=query3.fieldByname('Amount').AsString;
            Dbgrid.Cells[10,i]:=query3.fieldByname('SaleSum').AsString;
            Dbgrid.Cells[11,i]:=query3.fieldByname('PresentWay').AsString;
            Dbgrid.Cells[12,i]:=query3.fieldByname('presentNum').AsString;
            Dbgrid.Cells[13,i]:=query3.fieldByname('PresentSum').AsString;
            Query3.Next;
            inc(i);
            DBgrid.RowCount:= DBgrid.RowCount+1;
         end;
      end;
       ////////显示日期//2005-6-2////
      for i:=0 to DBgrid.RowCount-2 do
      begin
          strSql:='SELECT *  FROM  SaleTable  WHERE  SaleCode="'+DBgrid.Cells[0,i+1]+'"';
          Query3:=RunSqlOfAdo(query1,strSql);
          if Query3.RecordCount<>0 then
          begin
             while not Query3.Eof do
             begin
                 DBgrid.Cells[14,i+1]:=query3.fieldByname('Saledate').AsString;
                 query3.Next;
             end;
          end;
      end;
      //////////
      ////////////////////
      /////添加总计///////
      tmpTotal[0]:=0;
      tmpTotal[1]:=0;
      tmpTotal[2]:=0;
      tmpTotal[3]:=0;
      for i:=1 to DBgrid.rowcount-2 do
      begin
          tmpTotal[0]:=tmpTotal[0]+strToFloat(DBgrid.cells[9,i]);
          tmpTotal[1]:=tmpTotal[1]+strToFloat(DBgrid.cells[10,i]);
          tmpTotal[2]:=tmpTotal[2]+strToFloat(DBgrid.cells[12,i]);
          tmpTotal[3]:=tmpTotal[3]+strToFloat(DBgrid.cells[13,i]);
      end;
      DBgrid.Cells[0,DBgrid.RowCount-1]:='合计:';
      DBgrid.Cells[9,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[0]);
      DBgrid.Cells[10,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[1]);
      DBgrid.Cells[12,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[2]);
      DBgrid.Cells[13,DBgrid.RowCount-1]:=FloatTostr(tmpTotal[3]);
      ////添加报表数据////////
      strdelete:='DELETE * FROM  ReportOutTable';
      RunNonSql(query1,strDelete);
      for i:=1 to Dbgrid.RowCount-1 do
      begin
          strInsert:='INSERT INTO ReportoutTable(salecode,traderName,areaName,productCode,productName,rule,capacity,unit,price,Amount,saleSum,presentWay,PresentNum,PresentSum) 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]+'","';
          strInsert:=strInsert+DBgrid.cells[8,i]+'","'+DBgrid.Cells[9,i]+'","'+DBgrid.cells[10,i]+'","'+DBgrid.Cells[11,i]+'","'+DBgrid.cells[12,i]+'","'+DBgrid.Cells[13,i]+'")';
          RunNonSql(query1,strInsert);
      end;
      ///统计各值//////
      price_arg:=0;
      Amount_Sum:=0;
      Money_Sum:=0;
      //Showmessage(intToStr(dbgrid.rowcount));
      ////计算平均单价///
      if dbgrid.rowcount>2 then
      begin
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             price_arg:=price_arg+strToFloat(dbgrid.cells[8,i]);
         end;
         edit2.Text:=formatfloat('0.00',(price_arg)/(dbgrid.rowcount-2));

        ////计算总数量///
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             Amount_Sum:=Amount_Sum+strToFloat(dbgrid.cells[9,i]);
         end;
         edit3.Text:=FloatToStr((Amount_Sum));
          ////计算总金额///
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             Money_Sum:=Money_Sum+strToFloat(dbgrid.cells[10,i]);
         end;
         edit4.Text:=FloatToStr((Money_Sum));
          ////计算计算搭赠数量///
         for i:=1 to Dbgrid.RowCount-2 do
         begin
            present_Sum:=Present_Sum+strToFloat(dbgrid.cells[12,i]);
         end;
         edit5.Text:=FloatToStr((Present_Sum));
          ////计算计算搭赠数量///
         for i:=1 to Dbgrid.RowCount-2 do
         begin
             if  dbgrid.cells[13,i]<>'' then
             begin
                 present_Money:=Present_Money+strToFloat(dbgrid.cells[13,i]);
             end
             else
                 present_Money:=Present_Money+0;

         end;
         edit6.Text:=FloatToStr((Present_Money));
      end;
      
end;

procedure TTraderFind.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 TTraderFind.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('搭赠金额');
      //TitleName.Add('搭赠金额');
      SetReportTitle('二级商销售情况报表');
      SetTableName('ReportOutTable'); ////设置报表的表名
      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]:=90;
      ColumnWidth[2]:=80;
      ColumnWidth[3]:=70;
      ColumnWidth[4]:=70;
      ColumnWidth[5]:=80;
      ColumnWidth[6]:=60;
      ColumnWidth[7]:=60;
      ColumnWidth[8]:=60;
      ColumnWidth[9]:=70;
      ColumnWidth[10]:=70;
      ColumnWidth[11]:=70;
      ColumnWidth[12]:=70;
      ColumnWidth[13]:=70;
      ColumnWidth[14]:=70;

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

procedure TTraderFind.PicButton2Click(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:='saleFind';
     sheet:=v.workbooks[1].sheets['saleFind'];
     range:=Sheet.range[sheet.cells[1,1],sheet.cells[1,14]];
     //sheet.caption:='sdfgsdfsd';
     range.select;
     range.merge;
     range.HorizontalAlignment:=3;
     range.font.size:=19;
     range.font.bold:=2;
     TitleName:='进货查询统计情况表';
     Sheet.range[sheet.cells[1,1],sheet.cells[1,14]]:=TitleName;

        for i:=1 to DBgrid.RowCount do
        begin
            for j:=1 to 15 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,7]:='审核人:';
     v.workbooks[1].sheets[1].cells[DBgrid.RowCount+2,9]:='制表日期:';
    range:=Sheet.range[sheet.cells[2,1],sheet.cells[DBgrid.RowCount+1,14]];
    range.select;
    Sheet.range['A2','A7'].columnwidth:=16;
    //sheet.columns.width:=12;
    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.