u_form_datacheck_3.pas

这是一个啤酒行业的软件

   param.Clear;
   {/*
       进行数据验证
   */}
   if not _Check.Do_Control(combobox2,1,'请选择酒库名称...') then exit;
   {/*
       清空数据
   */}
   for i:=3 to grid1.RowCount-1 do
   begin
       for j:=1 to grid1.ColCount-1 do
       begin
           grid1.cells[j,i].ForeText:='';
       end;
   end;
   grid1.RowCount:=4;
   grid1.Refresh;
   {/*

       查询产品品种
   */}

   //param.Clear;
   //param.Add(trim(_whichStore));
   //strSql:='select  distinct SCW_ProductName,SCW_specification,SCW_UnitName  from  sale_changeBill_BW  Where  SCW_whichStore=:s1';
   //_AdonewProduce.BindToGrid_JK(grid1,strsql,param,2,4,2);
   with AdoQuery1 do
   begin
        connectionString:='Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=newproduce;Data Source=NTSERVER';
        close;
        sql.clear;
        strSql:='select  distinct ProductName,specification,UnitName  from  sale_changeBill  Where  whichStore=:s1  and  outStoreDate>=:s2 and  outStoredate<=:s3';
        sql.Add(strSql);
        parameters.ParamByName('s1').Value:=trim(_whichStore);
        parameters.ParamByName('s2').Value:=trim(begindate.text)+' 00:00:00';
        parameters.ParamByName('s3').Value:=trim(endDate.text)+' 23:59:59';
        prepared;
        open;
        if recordcount<>0 then
        begin
           i:=3;
           while not eof do
           begin
                grid1.RowCount:=i+2;
                grid1.Cells[1,i].ForeText:=intToStr(i-2);
                grid1.Cells[2,i].ForeText:=copy(trim(FieldByName('productname').AsString),1,pos('∕',trim(FieldByName('productname').AsString))-1);
                grid1.Cells[3,i].ForeText:=copy(trim(FieldByName('specification').AsString),4,length(trim(FieldByName('specification').AsString))-3);
                grid1.Cells[4,i].ForeText:=trim(FieldByName('unitName').AsString);
                next;
                inc(i);
           end;
        end;
   end;

   {/*
       查询保卫输入的数据
    */}
   ///showmessage(_whichstore);
   for i:=3 to grid1.RowCount-2 do
   begin
       param.Clear;
       param.Add(trim(beginDate.text));
       param.Add(trim(endDate.text));
       param.Add(trim(_whichStore));
       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,SCW_saleNum)) as a  from  sale_changeBill_BW  where  SCW_OutStoreDate>=:s1  and   ';
       strsql:=strsql+'SCW_outStoreDate<=:s2  and  SCW_whichStore=:s3 and  SCW_productName=:s4  and  SCW_specification=:s5  and  ';
       strsql:=strsql+'SCW_unitName=:s6';
       grid1.Cells[5,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
   end;

   {/*
       查询酒库调票数据
   */}
   for i:=3 to grid1.RowCount-2 do
   begin
       param.Clear;
       param.Add(trim(beginDate.text)+' 00:00:00');
       param.Add(trim(endDate.text)+' 23:59:59');
       param.Add(trim(_whichStore));
       param.Add(trim(grid1.cells[2,i].foretext)+'∕'+'1'+'×'+trim(grid1.cells[3,i].foretext)+'--'+trim(grid1.cells[4,i].foretext));
       param.Add('1'+'×'+trim(grid1.cells[3,i].foretext));
       param.Add(trim(grid1.cells[4,i].foretext));
       strsql:='select  sum(saleNum) as a  from  sale_changeBill  where  OutStoreDate>=:s1  and   ';
       strsql:=strsql+'outStoreDate<=:s2  and  whichStore=:s3 and  productName=:s4  and  specification=:s5  and  ';
       strsql:=strsql+'unitName=:s6';
       grid1.Cells[6,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
   end;

   {/*
       计算差额
    */}
   for i:=3 to grid1.RowCount-2 do
   begin
       if  grid1.cells[5,i].ForeText='' then
       begin
           _col5:='0';
       end
       else
           _col5:=trim(grid1.cells[5,i].ForeText);

       if  grid1.cells[6,i].ForeText='' then
       begin
           _col6:='0';
       end
       else
           _col6:=trim(grid1.cells[6,i].ForeText);



       grid1.cells[7,i].ForeText:=formatFloat('##0',strToFloat(grid1.cells[5,i].ForeText)-strToFloat(grid1.cells[6,i].ForeText));
    end;//for

    {/*
       设置第七列颜色

    for i:=3 to grid1.RowCount-2 do
    begin
        grid1.Cells[7,i].Color:=rgb(241,222,157);
        if grid1.cells[7,i].ForeText<>'0' then
        begin
           grid1.cells[7,i].FontColor:=rgb(255,0,0);
        end;
    end;
     */}
    {/*
       计算合计

    _Total:=0;
    for i:=3 to grid1.RowCount-2 do
    begin
        _Total:=_Total+strToFloat(grid1.cells[7,i].foretext);
    end;
    grid1.Cells[7,grid1.RowCount-1].ForeText:=formatFloat('##0',_Total);

    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;
    grid1.cells[1,grid1.RowCount-1].ForeText:='合计:';
    */}
    grid1.Refresh; 
end;

procedure TForm_DataCheck_3.FormShow(Sender: TObject);
var
   i,j:integer;
begin
   for i:=3 to grid1.RowCount-1 do
   begin
       for j:=1 to grid1.ColCount-1 do
       begin
           grid1.Cells[j,i].ForeText:='';
       end;
   end;
   grid1.RowCount:=4;
   
   grid1.Refresh;  
end;

procedure TForm_DataCheck_3.BtnFind1Click(Sender: TObject);
var
   param:TStringList;
   strsql:string;
   i,j:integer;

   _Col3,_Col4,_Col5,_Col6:double;
   _Col7,_Col8,_Col9,_Col10:double;
begin
   param:=TStringList.Create;
   param.Clear;
   {/*
       进行数据验证
   */}
   if not _Check.Do_Control(combobox1,1,'请选择酒库名称...') then exit;
    {/*
       清空数据
   */}
   for i:=3 to grid2.RowCount-1 do
   begin
       for j:=1 to grid2.ColCount-1 do
       begin
           grid2.cells[j,i].ForeText:='';
       end;
   end;
   grid2.RowCount:=4;
   grid2.Refresh;
   {/*
       到表RongPin2004--PGBaozhuangwu查询包装物名称
   */}
   param.clear;
   param.Add(trim(_whichStore));
   param.Add(trim(beginDate1.text)+' 00:00:00');
   param.Add(trim(endDate1.text)+' 23:59:59');
   strsql:='select distinct PG_wrapName   from  PG_ChangeBill  where  whichStore=:s1  and  PG_outStoreDate>=:s2  and  PG_OutStoredate<=:s3';
   _AdoNewproduce.BindToGrid_JK(grid2,strsql,param,2,2,2);
   for i:=3 to grid2.RowCount-2 do
   begin
       grid2.Cells[1,i].ForeText:=intToStr(i-2);
   end;
   {/*
       查询保卫输入的包装数量
   */}
   for i:=3 to grid2.RowCount-2  do
   begin
       param.clear;
       param.Add(trim(begindate1.text));
       param.Add(trim(enddate1.text));
       param.Add(_whichStore);
       param.Add(trim(grid2.cells[2,i].foretext)); 
       strsql:='select sum(convert(real,PG_JTNum)) as a,sum(convert(real,PG_CTNum)) as b,';
       strsql:=strsql+'sum(convert(real,PG_KXNum))as  c,sum(convert(real,PG_KPNum)) as d ';
       strsql:=strsql+'   from  PG_ChangeBill_BW  where  PG_OutDate>=:s1  and   ';
       strsql:=strsql+'PG_OutDate<=:s2  and  PG_whichStore=:s3 and  PG_WrapName=:s4';
       grid2.Cells[3,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
       grid2.Cells[4,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
       grid2.Cells[5,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'c');
       grid2.Cells[6,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'d');
   end;
   {/*
       查询酒库的包装数量
   */}
   for i:=3 to grid2.RowCount-2  do
   begin
       param.clear;
       param.Add(trim(begindate1.text)+' 00:00:00');
       param.Add(trim(enddate1.text)+' 23:59:59');
       param.Add(_whichStore);
       param.Add(trim(grid2.cells[2,i].foretext)); 
       strsql:='select sum(convert(real,PG_JTNum)) as a,sum(convert(real,PG_CTNum)) as b,';
       strsql:=strsql+'sum(convert(real,PG_KXNum))as  c,sum(convert(real,PG_KPNum)) as d ';
       strsql:=strsql+'   from  PG_ChangeBill  where  PG_OutStoreDate>=:s1  and   ';
       strsql:=strsql+'PG_OutStoreDate<=:s2  and  whichStore=:s3 and  PG_WrapName=:s4';
       grid2.Cells[7,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'a');
       grid2.Cells[8,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'b');
       grid2.Cells[9,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'c');
       grid2.Cells[10,i].ForeText:=_AdoNewproduce.getFieldValue(strsql,param,'d');
   end;
   {/*
       计算包装差枝
   */}
   for i:=3 to grid2.RowCount-2 do
   begin
       grid2.Cells[11,i].ForeText:=formatFloat('##0',strToFloat(grid2.Cells[3,i].ForeText)-strToFloat(grid2.Cells[7,i].ForeText));
       grid2.Cells[12,i].ForeText:=formatFloat('##0',strToFloat(grid2.Cells[4,i].ForeText)-strToFloat(grid2.Cells[8,i].ForeText));
       grid2.Cells[13,i].ForeText:=formatFloat('##0',strToFloat(grid2.Cells[5,i].ForeText)-strToFloat(grid2.Cells[9,i].ForeText));
       grid2.Cells[14,i].ForeText:=formatFloat('##0',strToFloat(grid2.Cells[6,i].ForeText)-strToFloat(grid2.Cells[10,i].ForeText));
   end;
   {/*
       设置差值列颜色
   */}
   for i:=3 to grid2.rowcount-2 do
   begin
       grid2.Cells[11,i].Color:=rgb(241,222,157);
       grid2.Cells[11,i].FontColor:=rgb(255,0,0);

       grid2.Cells[12,i].Color:=rgb(241,222,157);
       grid2.Cells[12,i].FontColor:=rgb(255,0,0);

       grid2.Cells[13,i].Color:=rgb(241,222,157);
       grid2.Cells[13,i].FontColor:=rgb(255,0,0);

       grid2.Cells[14,i].Color:=rgb(241,222,157);
       grid2.Cells[14,i].FontColor:=rgb(255,0,0);
   end;
   {/*
       计算差值合计
   */}
   _Col3:=0;
   _Col4:=0;
   _Col5:=0;
   _Col6:=0;
   for i:=3 to grid2.RowCount-2 do
   begin
       _Col3:=_Col3+strToFloat(grid2.cells[11,i].foretext);
       _Col4:=_Col3+strToFloat(grid2.cells[12,i].foretext);
       _Col5:=_Col3+strToFloat(grid2.cells[13,i].foretext);
       _Col6:=_Col3+strToFloat(grid2.cells[14,i].foretext);
   end;
   grid2.Cells[11,grid2.RowCount-1].ForeText:=formatFloat('##0',_Col3);
   grid2.Cells[12,grid2.RowCount-1].ForeText:=formatFloat('##0',_Col4);
   grid2.Cells[13,grid2.RowCount-1].ForeText:=formatFloat('##0',_Col5);
   grid2.Cells[14,grid2.RowCount-1].ForeText:=formatFloat('##0',_Col6);
   {/*
       设置最后一行的颜色
   */}
   for i:=1 to grid2.colcount-1 do
   begin
       grid2.Cells[i,grid2.RowCount-1].Color:=rgb(0,143,247);
       grid2.Cells[i,grid2.RowCount-1].FontColor:=rgb(255,255,255);
   end;
end;

procedure TForm_DataCheck_3.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
var
   param:TStringList;
   strsql:string;
   _CheckDate:string;
   _Total:Array[0..6] of string;
begin
   param:=TStringList.create;
   param.clear;
   EnableWindow(Form_DataCheck.handle,true);
   CanClose:=true;
   {_CheckDate:=formatDateTime('yyyy-mm-dd',now);
   _Total[0]:=trim(grid1.cells[7,grid1.rowcount-1].foretext);
   ///showmessage(_Total[0]);
   _Total[1]:=trim(grid2.cells[11,grid2.rowcount-1].foretext);
   _Total[2]:=trim(grid2.cells[12,grid2.rowcount-1].foretext);
   _Total[3]:=trim(grid2.cells[13,grid2.rowcount-1].foretext);
   _Total[4]:=trim(grid2.cells[14,grid2.rowcount-1].foretext);

   if (_Total[0]<>'0') or (_Total[1]<>'0') or (_Total[2]<>'0') or (_Total[3]<>'0') or (_Total[4]<>'0')  then
   begin
      ///showmessage('对不起，数据核对工作没进行或核对错误，不能交班');
      param.clear;
      param.add('CHK_BW_Beer');
      param.add(_CheckDate);
      param.add(_whichStore);
      param.add('1');
      strsql:='insert into  PG_ChangeBill_BW_Flag(CHK_Type,CHK_Checkdate,CHK_WhichStore,CHK_successFlag) ';
      strsql:=strsql+'Values(:s1,:s2,:s3,:s4)';
      _AdoNewProduce.actionRecord(strsql,param);

      param.clear;
      param.add('CHK_BW_Wrap');
      param.add(_CheckDate);
      param.add(_whichStore);
      param.add('1');
      strsql:='insert into  PG_ChangeBill_BW_Flag(CHK_Type,CHK_Checkdate,CHK_WhichStore,CHK_successFlag) ';
      strsql:=strsql+'Values(:s1,:s2,:s3,:s4)';
      _AdoNewProduce.actionRecord(strsql,param);

      EnableWindow(Form_MainMenu.Handle,true);
      CanClose:=true;
   end
   else