khdh.~pas

已经给出使用的每个SQL Server数据库

        Column1.Title.Font.Color := clRed;
        Case ColNum of
          0: begin
               Column1.FieldName := 'spdm';
               Column1.Title.Caption := '商品代码';
             end;
          1: begin
               Column1.FieldName := 'spmc';
               Column1.Title.Caption := '商品名称';
             end;
          2: begin
               Column1.FieldName := 'jbdw';
               Column1.Title.Caption := '采购单位';
             end;
          3: begin
                 Column1.FieldName := 'bzsj';
                 Column1.Title.Caption := '标准售价';
             end;
          4: begin
               Column1.FieldName := 'sl';
               Column1.Title.Caption := '税率';
             end;
          5: begin
               Column1.FieldName := 'se';
               Column1.Title.Caption := '税额';
             end;
        end;
      end;
      DataSource1.DataSet := Data.Query1;
      if Grid1.Visible = False then
      begin
        CellRect := bg.CellRect(Col1,Row1);
        CellRect.Left := CellRect.Left+bg.Left;
        CellRect.Right := CellRect.Right+ bg.Left;
        CellRect.Top := bg.Top+ CellRect.Top;
        Grid1.Left := CellRect.Right+1;
        Grid1.Top := CellRect.Top;
        Grid1.Visible := True;
      end;
    end;
    Grid1.Tag := 2;
  end
  { DONE : 按商品名称查找商品信息 }
  else if (Col1 = spmc)and(kc= false)and(Trim(bg.Cells[spdm,Row1])='') then
  begin
    With Data.Query1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select aa.*,bb.bzsj from t_spjcxx aa inner join t_spsj bb on aa.spdm = bb.spdm and spmc like :a  and spzl = :b inner join t_sprkjl cc on aa.spdm = cc.spdm');
      ParamByName('a').AsString := Trim(bg.Cells[spmc,Row1])+ '%';
      ParamByName('b').AsString := Trim(bg.Cells[cklb,Row1]);
      Open;
    end;
    if Data.Query1.RecordCount>0 then
    begin
      Grid1.Columns.Clear;
      //在数据表格中需要显示6个字段
      For ColNum := 0 to 5 do
      begin
        Column1 := Grid1.Columns.Add;
        Column1.Title.Alignment := taCenter;
        Column1.Title.Font.Color := clRed;
        Case ColNum of
          0: begin
               Column1.FieldName := 'spdm';
               Column1.Title.Caption := '商品代码';
             end;
          1: begin
               Column1.FieldName := 'spmc';
               Column1.Title.Caption := '商品名称';
             end;
          2: begin
               Column1.FieldName := 'cgdw';
               Column1.Title.Caption := '采购单位';
             end;
          3: begin
               Column1.FieldName := 'bzsj';
               Column1.Title.Caption := '标准售价';
             end;
          4: begin
               Column1.FieldName := 'sl';
               Column1.Title.Caption := '税率';
             end;
          5: begin
               Column1.FieldName := 'se';
               Column1.Title.Caption := '税额';
             end;
        end;
      end;
      DataSource1.DataSet := Data.Query1;
      if Grid1.Visible = False then
      begin
        CellRect := bg.CellRect(Col1,Row1);
        CellRect.Left := CellRect.Left+bg.Left;
        CellRect.Right := CellRect.Right+ bg.Left;
        CellRect.Top := bg.Top+ CellRect.Top;
        Grid1.Left := CellRect.Right+1;
        Grid1.Top := CellRect.Top;
        Grid1.Visible := True;
      end;
    end;
    Grid1.Tag := 2;
  end;
end;

function Tf_khdh.GroupPrice: real;
var
  r: Integer;
  Sum: Real;
begin
  Sum := 0.0;
  For r:=1 to bg.RowCount-1 do
    if Trim(bg.Cells[wzrje,r])<>'' then
      Sum := Sum + StrToFloat(bg.Cells[wzrje,r]);
  Result := Sum;
end;

function Tf_khdh.GroupTax: real;
var
  r: Integer;
  Sum: Real;
begin
  Sum := 0.0;
  For r:=1 to bg.RowCount-1 do
    if Trim(bg.Cells[se,r])<>'' then
      Sum := Sum + StrToFloat(bg.Cells[se,r]);
  Result := Sum;
end;

procedure Tf_khdh.xsxjChange(Sender: TObject);
var
  r: Integer;
begin
  if Trim(xszr.Text)<>'' then
  begin
    if (StrToFloat(xszr.Text)<>0)and(StrToFloat(xszr.Text)>=StrToFloat(xsxj.Text)) then
    begin
      Application.MessageBox('订货折让不能大于等于订货小计.','提示',64);
      xszr.Text := '0.0';
      Exit;
    end;
    if StrToFLoat(xsxj.Text)<>0 then
      zrb := StrToFloat(xszr.Text)/StrToFloat(xsxj.Text);
    if Trim(ksb.Text)='应税内含' then
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)])
    else
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)+StrToFloat(xsse.Text)]);
    if FirstRowIsNull= False then
    For r :=1 to bg.RowCount-1 do
      begin
        row1 := r;
        bg.OnSetEditText(sender,0,0,'')
      end;
  end
  else
    xszr.Text := '0.0';
end;

procedure Tf_khdh.FindNext;
begin
  if FindNextControl(ActiveControl,True,False,False)is TEdit then
    TEdit(FindNextControl(ActiveControl,True,False,False)).SetFocus
  else if FindNextControl(ActiveControl,True,False,False)is TComboBox then
    TComboBox(FindNextControl(ActiveControl,True,False,False)).SetFocus
  else if FindNextControl(ActiveControl,True,False,False)is TDateTimePicker then
    TDateTimePicker(FindNextControl(ActiveControl,True,False,False)).SetFocus
  else if FindNextControl(ActiveControl,True,False,False)is TStringGrid then
  begin
    TStringGrid(FindNextControl(ActiveControl,True,False,False)).SetFocus;
    bg.Col := 0;
    bg.Cells[zk,1]:= '1.0';
  end;
end;

function Tf_khdh.FirstRowIsNull: Boolean;
var
  c: Integer;
begin
  Result := False;
  For c:= 0 to bg.ColCount-2 do
    if Trim(bg.Cells[c,1])='' then
    begin
      Result := True;
      Break;
    end;
end;

procedure Tf_khdh.SelectTable(TableName: String);
begin
  With Data.Query1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from '+ TableName);
    Open;
  end;
  lb.Clear;
  if Data.Query1.RecordCount>0 then
    while Not Data.Query1.Eof do
    begin
      lb.Items.Add(Data.Query1.Fields[1].AsString);
      Data.Query1.Next;
    end;
end;

procedure Tf_khdh.SelectTable(TableName, FieldName: String;
  FieldValue: Variant);
begin
  With Data.Query1 do
  begin
    Close;
    SQL.Clear;
    SQL.Add('select * from '+ TableName + ' where '+ FieldName + ' =:a');
    Params[0].Value :=  FieldValue;
    Open;
  end;
  lb.Clear;
  if Data.Query1.RecordCount>0 then
    while Not Data.Query1.Eof do
    begin
      lb.Items.Add(Data.Query1.Fields[1].AsString);
      Data.Query1.Next;
    end;
end;

procedure Tf_khdh.xszrChange(Sender: TObject);
var
  r: Integer;
begin
  if Trim(xszr.Text)<>'' then
  begin
    if (StrToFloat(xszr.Text)<>0)and(StrToFloat(xszr.Text)>=StrToFloat(xsxj.Text)) then
    begin
      Application.MessageBox('进货折让不能大于等于进货小计.','提示',64);
      xszr.Text := '0.0';
      Exit;
    end;
    if StrToFLoat(xsxj.Text)<>0 then
      zrb := StrToFloat(xszr.Text)/StrToFloat(xsxj.Text);
    if Trim(ksb.Text)='应税内含' then
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)])
    else
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)+StrToFloat(xsse.Text)]);
    if FirstRowIsNull= False then
    For r :=1 to bg.RowCount-1 do
      begin
        row1 := r;
        bg.OnSetEditText(sender,0,0,'')
      end;
  end
  else
    xszr.Text := '0.0';
end;

procedure Tf_khdh.xszrKeyPress(Sender: TObject; var Key: Char);
var
  i: Boolean;
begin
  i := (Key<#8)or(Key>#8)and(Key<#46)or(Key>#46)and(Key<#48)or(Key>#57);
  if i then
    Key := #0;
end;

procedure Tf_khdh.xsseChange(Sender: TObject);
begin
  if Trim(xszr.Text)<>'' then
  begin
    if Trim(ksb.Text)='应税内含'then
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)])
    else
      zj.Text := Format('%8.4f',[StrToFloat(xsxj.Text)-StrToFloat(xszr.Text)+StrToFloat(xsse.Text)]);
  end
  else
    zj.Text := xsxj.Text;
end;

procedure Tf_khdh.bgKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
var
  Rect1: TRect;
  col,row: integer;
begin
  kc:= False;
  if (Key = vk_shift)and(Grid1.Visible = True) then //显示网格
  begin
    Grid1.SetFocus;
    Exit;
  end;
  if Key = vk_Delete then //删除当前记录
  begin
    if Application.MessageBox('确实要删除当前记录吗?','提示',mb_YesNo)=ID_Yes then
    begin
      ClearCurrentRow;
      if bg.RowCount>2 then
      begin
        if Row1<>bg.RowCount-1 then
        begin
          For row := Row1+1to (bg.RowCount-1) do
            For col:=0 to bg.ColCount-1 do
              bg.Cells[col,row-1]:= bg.Cells[col,row];
        end;
        ClearEndRow;
        bg.RowCount := bg.RowCount-1;
      end
      else
        bg.Cells[zk,Row1]:= '1.0';
      xsxj.Text := Format('%8.4f',[GroupPrice]);
      xsse.Text := Format('%8.4f',[GroupTax]);
      bg.SetFocus;
      bg.Col := ckmc;
    end;
  end;
  if (Key= vk_insert)and(CurrentIsNull = False)and(Row1 = bg.RowCount-1) then  //添加新行
  begin
    bg.RowCount := bg.RowCount+1;
    bg.Row:= Bg.Row+1;
    bg.Cells[zk,Row1]:='1.0';
    bg.Col:=ckmc;
    exit;
  end;
  if Key = vk_Return then    //查询定位计算数据
  begin
    if Grid1.Visible = True then
      Grid1.Visible := False;
    if Trim(bg.Cells[ckmc,Row1])='' then
      Exit;
    if (Col1 = bzq)and(Trim(bg.Cells[jbdw,Row1])<>'') then
    begin
      bzqlb.Clear;
      with Data.Query2 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select bzq from t_sprkjl where spdm = :a and kcmc = :b');
        ParamByName('a').AsString := Trim(bg.Cells[spdm,Row1]);
        ParamByName('b').AsString := Trim(bg.Cells[ckmc,Row1]);
        Open;
      end;
      while Not Data.Query2.Eof do
      begin
        bzqlb.Items.Add(DateTimeToStr(Data.Query2.FieldByName('bzq').AsDateTime));
        Data.Query2.Next;
      end;
      Rect1 := bg.CellRect(bzq,Row1);
      Rect1.Left := Rect1.Left+bg.Left;
      Rect1.Right :=Rect1.Right+bg.Left;
      Rect1.Top := bg.Top+ Rect1.Top;
      bzqlb.Left := Rect1.Left;
      bzqlb.Top := Rect1.Top+1;
      bzqlb.Visible := True;
      bzqlb.SetFocus;
      bzqlb.ItemIndex := 0;
      Exit;
    end;
    if (Col1 = ckmc)and(Trim(bg.Cells[ckmc,Row1])<>'') then //不在列表中选择,直接输入库存信息时,判断库存信息是否存在
    begin
      With Data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select * from t_kcjcxx where kcdm = :a or kcmc = :a');
        ParamByName('a').AsString := Trim(bg.Cells[ckmc,Row1]);
        Open;
      end;
      if Data.Query1.RecordCount>0 then
      begin
        bg.Cells[ckmc,Row1]:= Trim(Data.Query1.Fields[1].AsString);
        bg.Cells[cklb,Row1]:= Trim(Data.Query1.Fields[2].AsString);
        if Row1 = 1 then
          bg.Cells[zk,row1]:='1.0';
        bg.SetFocus;
        bg.Col := bg.Col+1;
      end
      else
      begin
        kc := True;
        bg.Cells[ckmc,Row1]:='';
      end;
    end
    else if (Col1 = spdm)and(Trim(bg.Cells[spdm,Row1])<>'')and(Trim(bg.Cells[ckmc,Row1])<>'') then
    begin
      With Data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select aa.*,bb.bzsj from t_spjcxx aa inner join t_spsj bb on aa.spdm = bb.spdm and aa.spdm = :a  and spzl = :b inner join t_sprkjl cc on  aa.spdm = cc.spdm');
        ParamByName('a').AsString := Trim(bg.Cells[spdm,Row1]);
        ParamByName('b').AsString := Trim(bg.Cells[cklb,Row1]);
        Open;
      end;
      if Data.Query1.RecordCount>0 then
      begin
        bg.Cells[spdm,Row1]:= Trim(Data.Query1.Fields[0].AsString);
        bg.Cells[spmc,Row1]:= Trim(Data.Query1.Fields[1].AsString);
        bg.Cells[dj,Row1]:= Trim(Data.Query1.FieldByName('bzsj').AsString);
        if (Trim(ksb.Text)='应税内含')or(Trim(ksb.Text)='应税外加')then
          bg.Cells[sl,Row1]:= Trim(Data.Query1.FieldByName('sl').AsString)
        else
          bg.Cells[sl,Row1]:= '0.0';
        bg.Cells[jbdw,Row1]:= Trim(Data.Query1.FieldByName('jbdw').AsString);
        bg.SetFocus;
        bg.Col := bg.Col+1;
      end
      else
      begin
        kc := True;
        bg.Cells[spdm,Row1]:='';
        bg.Cells[spmc,Row1]:='';
      end;
    end
    else if (Col1 = spmc)and(Trim(bg.Cells[spmc,Row1])<>'')and(Trim(bg.Cells[spdm,Row1])='')and(Trim(bg.Cells[ckmc,Row1])<>'') then
    begin
      With Data.Query1 do
      begin
        Close;
        SQL.Clear;
        SQL.Add('select aa.*,bb.bzsj from t_spjcxx aa inner join t_spsj bb on aa.spdm = bb.spdm and spmc = :a  and spzl = :b inner join t_sprkjl cc on aa.spdm = cc.spdm');
        ParamByName('a').AsString := Trim(bg.Cells[spmc,Row1]);
        ParamByName('b').AsString := Trim(bg.Cells[cklb,Row1]);
        Open;
      end;
      if Data.Query1.RecordCount>0 then
      begin
        bg.Cells[spdm,Row1]:= Trim(Data.Query1.Fields[0].AsString);
        bg.Cells[spmc,Row1]:= Trim(Data.Query1.Fields[1].AsString);
        bg.Cells[dj,Row1]:= Trim(Data.Query1.FieldByName('bzsj').AsString);
        if (Trim(ksb.Text)='应税内含')or(Trim(ksb.Text)='应税外加')then
          bg.Cells[sl,Row1]:= Trim(Data.Query1.FieldByName('sl').AsString)
        else
          bg.Cells[sl,Row1]:= '0.0';
        bg.Cells[jbdw,Row1]:= Trim(Data.Query1.FieldByName('jbdw').AsString);
        bg.SetFocus;
        bg.Col := bg.Col+1;
      end
      else
      begin
        kc := True;
        bg.Cells[spdm,Row1]:='';  
        bg.Cells[spmc,Row1]:='';
        bg.Cells[jbdw,Row1]:='';
      end;
    end
    else
    begin
      bg.SetFocus;
      if bg.Col<bg.ColCount-1 then
        bg.Col := bg.Col+1;
    end;
  end; 
end;

procedure Tf_khdh.ClearCurrentRow;
var
  i: Integer;
begin
  For i:=0 to bg.ColCount-1 do
    bg.Cells[i,Row1]:= '';
end;

procedure Tf_khdh.ClearEndRow;
var
  i: Integer;
begin
  For i:=0 to bg.ColCount-1 do
    bg.Cells[i,bg.RowCount-1]:= '';
end;

function Tf_khdh.CurrentIsNull: Boolean;
var
  a: Integer;
begin
  Result := False;
  For a:= 0 to bg.ColCount -1 do
  begin
    if Trim(bg.Cells[a,Row1])='' then
    begin
      Result := True;
      Break;
    end;
  end;
end;

procedure Tf_khdh.xsphEnter(Sender: TObject);
begin
  Grid1.Visible := False;
  bzqlb.Visible := False;
end;

procedure Tf_khdh.ywyExit(Sender: TObject);
var
  key: Word;
begin
  if Trim(ywy.Text)<>'' then
  begin
    Key := vk_ReTurn;
    ywy.OnKeyDown(Sender,Key,[ssctrl]);
  end;
end;