spxs.~pas

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

      bg.Cells[je,Row1]:= Format('%8.4f',[StrToInt(bg.Cells[num,row1])*StrToFloat(bg.Cells[dj,row1])*StrToFloat(bg.Cells[zk,Row1])*(1-zrb)]);
    end
    else
    begin
      kc := True;
      bg.Cells[je,Row1]:='';
      bg.Cells[wzrje,Row1]:='' ;
      if (Trim(bg.Cells[Num,Row1])='')or(Trim(bg.Cells[dj,Row1])='') then
        bg.Cells[se,Row1]:= '';
    end;
    xsxj.Text := Format('%8.4f',[GroupPrice]);
    xsse.Text := Format('%8.4f',[GroupTax]);
  end
  else if (Col1 = ckmc)and(kc= False) then
  begin
    With Data.Query1 do
    begin
      Close;
      SQL.Clear;
      SQL.Add('select * from t_kcjcxx where kcdm like :a or kcmc like :a');
      ParamByName('a').AsString := Trim(bg.Cells[ckmc,Row1])+ '%';
      Open;
    end;
    if Data.Query1.RecordCount>0 then
    begin
      Grid1.Columns.Clear;
      Column1 := Grid1.Columns.Add;
      Column1.FieldName := 'kcdm';
      Column1.Title.Caption := '库存代码';
      Column1.Title.Alignment := taCenter;
      Column1.Title.Font.Color := clRed;
      Column1 := Grid1.Columns.Add;
      Column1.FieldName := 'kcmc';
      Column1.Title.Alignment := taCenter;
      Column1.Title.Font.Color := clRed;
      Column1.Title.Caption := '库存名称';
      Column1 := Grid1.Columns.Add;
      Column1.FieldName := 'kclb';
      Column1.Title.Alignment := taCenter;
      Column1.Title.Font.Color := clRed;
      Column1.Title.Caption := '库存类别';
      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;
      Grid1.Tag := 1;
    end
    else
    begin
      DataSource1.DataSet := Nil;
      Grid1.Visible := False;
      Application.MessageBox('该库存信息不存在.','提示',64);
      kc := True;
      bg.Cells[ckmc,Row1]:='';
      bg.Col := ckmc;
    end;
  end
  {todo 按商品代码查询商品信息}
  else if (Col1 = spdm)and(kc= false)and(Trim(bg.Cells[spmc,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 like :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
      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 := '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_spxs.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_spxs.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_spxs.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_spxs.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_spxs.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_spxs.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_spxs.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
  begin
    while Not Data.Query1.Eof do
    begin
      if Trim(TableName)='t_dhph' then
        lb.Items.Add(Data.Query1.Fields[0].AsString)
      else
        lb.Items.Add(Data.Query1.Fields[1].AsString);
      Data.Query1.Next;
    end;
  end;
end;

procedure Tf_spxs.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_spxs.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_spxs.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_spxs.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 Trim(dhph.Text) = '' then
  begin
    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;
  end;
  if Key = vk_Return then    //查询定位计算数据
  begin
    if Grid1.Visible = True then
      Grid1.Visible := False;
    if Trim(bg.Cells[ckmc,Row1])='' then
      Exit;
    if Trim(dhph.Text)='' then
      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 order by bzq ');
          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;