sql.txt

这是一个啤酒行业的软件

 {/*
       查询前一天的交班数据
   */}
   for i:=3 to  grid1.RowCount-2 do
   begin
      param.clear;
      param.Add(_whichStore);
      param.add(_prevDate);
      param.Add(trim(grid1.cells[2,i].foretext));
      param.Add(trim(grid1.cells[3,i].foretext));
      param.Add(trim(grid1.cells[4,i].foretext));
      strsql:='select  sum(convert(real,SF_boxNum)) as a,sum(convert(real,SF_bottleNum)) as b  from  sale_handRealStore  ';
      strsql:=strsql+'where  SF_whichStore=:s1 and  substring(SF_CheckDate,1,10)=:s2  and  SF_produceName=:s3  ';
      strsql:=strsql+' and  SF_specification=:s4  and SF_UnitName=:s5';
      grid1.Cells[7,i].ForeText:=_AdoNewProduce.getFieldValue(strsql,param,'a');
      grid1.Cells[8,i].ForeText:=_AdoNewProduce.getFieldValue(strsql,param,'b');

   end;
{/*
       计算库存差额
   */}
   for i:=3 to grid1.RowCount-2 do
   begin
       {/*
           计算整箱数差
       */}
       if  grid1.cells[5,i].foretext<>'' then
       begin
           _boxNum:=strToInt(grid1.cells[5,i].foretext);
       end
       else
           _boxNum:=0;

       if  grid1.cells[6,i].foretext<>'' then
       begin
           _bottleNum:=strToInt(grid1.cells[6,i].foretext);
       end
       else
           _bottleNum:=0;


       //grid1.cells[11,i].ForeText:=formatFloat('##0',strToInt(grid1.cells[5,i].foretext)-strToInt(grid1.cells[8,i].foretext));
       {/*
           计算整瓶数差
       */}
       if  grid1.cells[7,i].foretext<>'' then
       begin
           _boxNum1:=strToInt(grid1.cells[7,i].foretext);
       end
       else
           _boxNum1:=0;

       if  grid1.cells[8,i].foretext<>'' then
       begin
           _bottleNum1:=strToInt(grid1.cells[8,i].foretext);
       end
       else
           _bottleNum1:=0;


       grid1.cells[9,i].ForeText:=formatFloat('##0',_boxNum-_boxNum1);
       grid1.cells[10,i].ForeText:=formatFloat('##0',_bottleNum-_bottleNum1);
       //grid1.cells[13,i].ForeText:=formatFloat('##0',_emptyBoxNum-_emptyBoxNum1);
   end;
   {/*
       设置差额颜色
   */}
   for i:=3 to grid1.RowCount-2 do
   begin
       grid1.cells[9,i].Color:=rgb(241,222,157);
       grid1.cells[10,i].Color:=rgb(241,222,157);

       if grid1.cells[9,i].ForeText<>'0' then
       begin
          grid1.cells[9,i].FontColor:=rgb(255,0,0);
       end;
       if grid1.cells[10,i].ForeText<>'0' then
       begin
          grid1.cells[10,i].FontColor:=rgb(255,0,0);
       end;

   end;
   {/*
       计算差额合计
   */}
   for i:=3 to grid1.RowCount-2 do
   begin
       _boxNumSum:=_boxNumSum+strToFloat(grid1.cells[9,i].foretext);
       _bottleNumSum:=_bottleNumSum+strToFloat(grid1.cells[10,i].foretext);
      //_emptyBoxNumSum:=_emptyboxNumSum+strToFloat(grid1.cells[13,i].foretext);
   end;
   grid1.Cells[9,grid1.RowCount-1].ForeText:=formatFloat('##0',_boxNumSum);
   grid1.Cells[10,grid1.RowCount-1].ForeText:=formatFloat('##0',_bottleNumSum);
   //grid1.Cells[13,grid1.RowCount-1].ForeText:=formatFloat('##0',_emptyBoxNumSum);
   {/*
       设置最后一行颜色
   */}
   for i:=1 to grid1.ColCount-1 do
   begin
       grid1.Cells[i,grid1.rowcount-1].Color:=rgb(0,143,247);
       grid1.Cells[i,grid1.rowcount-1].FontColor:=rgb(255,255,255);
   end;