allvar.pas

来自「图书馆管理系统设计详细资料」· PAS 代码 · 共 1,386 行 · 第 1/3 页

    SQL.SetParams(ukmodify);
    with SQL.Query[ukmodify] do
    begin
      if newvalue = ''
      then if (quy.FieldByName(zdname).DataType = ftDate) or
              (quy.FieldByName(zdname).DataType = ftDatetime )
           then Parambyname(zdname).value := null
           else Parambyname(zdname).value := newvalue
      else Parambyname(zdname).value := newvalue;
      Parambyname('old_jlh').AsInteger := ik;
    end;
    SQL.ExecSQL(ukmodify);
    quy.CachedUpdates := false;
    quy.Next;
  end;
end;

{修改屏幕选出记录的单一字段值
需要字段名、新值并切不询问}
procedure modifypmjlnoak(quy:tquery;SQL:TUpdateSQL;dpltxt,zdname,newvalue:string);
var ik : integer;
begin
  while not quy.Eof do
  begin
    ik := quy.Fieldbyname('jlh').value;
    quy.CachedUpdates := true;
    SQL.SetParams(ukmodify);
    with SQL.Query[ukmodify] do
    begin
      if newvalue = ''
      then if (quy.FieldByName(zdname).DataType = ftDate) or
              (quy.FieldByName(zdname).DataType = ftDatetime )
           then Parambyname(zdname).value := null
           else Parambyname(zdname).value := newvalue
      else Parambyname(zdname).value := newvalue;
      Parambyname('old_jlh').AsInteger := ik;
    end;
    SQL.ExecSQL(ukmodify);
    quy.CachedUpdates := false;
    quy.Next;
  end;
end;

{修改一条记录中部分字段值函数，需要字段名串、新值串、修改个数和记录号
//字段名 s  = 'aaa,bbb,ccc,ddd,'
 //新值  st = 'aaa,bbb,ccc,ddd,'}
procedure modifyMultisZd(kquy:tquery;kSQL:TUpdateSQL;mzd,mval:string;ln,vjlh:integer);
var i       : integer;
    svn,svz : string;
begin
  svn := mzd;
  svz := mval;
  kquy.CachedUpdates := true;
  kSQL.SetParams(ukmodify);
  i := 1;
  repeat
    kSQL.Query[ukmodify].Parambyname(sltfu(svn)).value := sltfu(svz);
    inc(i);
  until i > ln;
  kSQL.Query[ukmodify].Parambyname('jlh').AsInteger := vjlh;
  kSQL.Query[ukmodify].Parambyname('old_jlh').AsInteger := vjlh;
  kSQL.ExecSQL(ukmodify);
  kquy.CachedUpdates := false;
end;

{修改一条记录全部字段值函数，需要新值串和记录号
 修改记录  st = 'aaa,bbb,ccc,ddd,' }
function modifyjlfu(quy:tquery;SQL:TUpdateSQL;st:string;vjlh:integer;mydbgrid:Tdbgrid):integer;
var i,k   : integer;
    s   : string;
begin
  result := 0;
  quy.CachedUpdates := true;
  SQL.SetParams(ukmodify);
  i := 0;
  while i < (quy.FieldCount-1) do
  begin
    s := sltfu(st);
    k := pos(',',s);
    if k > 0
    then begin
           showmessage('字符串中含有《逗号》出错！'
           + #10 + #13 + '字段名 : ' + mydbgrid.Columns[i].Title.Caption
           + #10 + #13 + '出错值 : ' + s );
           result := i;
         end;
    k := pos(' ',s);
    if k > 0
    then begin
           showmessage('字符串中含有《间隔符》出错！'
             + #10 + #13 + '字段名 : ' + mydbgrid.Columns[i].Title.Caption
             + #10 + #13 + '出错值 : ' + s );
           result := i;
         end;

    if (quy.Fields.Fields[i].DataType = ftstring) and ( result = 0 )
    then begin
           if length(s) > quy.Fields.Fields[i].Size
           then begin
                  showmessage('字符串长度出错！'
                     +#13+'字段号..' + inttostr(i+1)
                     +#13+'字段名..' + mydbgrid.Columns[i].Title.Caption
                     +#13+'字段值..' + s );
                  result := i;
                end
           else SQL.Query[ukmodify].Params[i].Asstring := s;
         end
    else if quy.Fields.Fields[i].DataType = ftinteger
    then begin
           if not jcintfu(s)
           then begin
                  showmessage('整数类型出错！'
                     +#13+'字段号..' + inttostr(i+1)
                     +#13+'字段名..' + mydbgrid.Columns[i].Title.Caption
                     +#13+'字段值..' + s );
                  result := i;
                end
           else SQL.Query[ukmodify].Params[i].Asinteger := strtoint(s);
         end
    else if quy.Fields.Fields[i].DataType = ftfloat
         then begin
                if not jcrealfu(s)
                then begin
                       showmessage('实数类型出错！'
                          +#13+'字段号..' + inttostr(i+1)
                          +#13+'字段名..' + mydbgrid.Columns[i].Title.Caption
                          +#13+'字段值..' + s );
                       result := i;
                     end
                else SQL.Query[ukmodify].Params[i].AsFloat := strtofloat(s);
              end
    else if (quy.Fields.Fields[i].DataType = ftDate) or
            (quy.Fields.Fields[i].DataType = ftDatetime )
         then begin
                if not jcdateSPfu(s)
                then begin
                       showmessage('日期类型出错！'
                       +#13+'字段号..' + inttostr(i+1)
                       +#13+'字段名..' + mydbgrid.Columns[i].Title.Caption
                       +#13+'字段值..' + s );
                       result := i;
                     end
                else if s = ''
                     then SQL.Query[ukmodify].Params[i].Value := null
                     else SQL.Query[ukmodify].Params[i].Value := strtodate(s);
              end
         else SQL.Query[ukmodify].Params[i].Value := s;
    inc(i);
  end;
  if result = 0
  then begin
         SQL.Query[ukmodify].Params[i].Value := vjlh;
         SQL.Query[ukmodify].Params[i+1].Value := vjlh;
         SQL.ExecSQL(ukmodify);
       end;
  quy.CachedUpdates := false;
end;

{清除查询选择 }
procedure sltvarclearpd;
var i : integer;
begin
  for i := 1 to sltvar.vln do
  begin
    sltvar.index[i] :=  0;
    sltvar.name [i] := '';
    sltvar.value[i] := '';
    sltvar.tedit[i].Text := '';
    sltvar.tedit[i].Color := clbtnface;
    sltvar.sltname  := '';
    sltvar.sltvalue := '';
    sltvar.sltik    :=  0;
  end;
end;

{增加查询选择 }
procedure addslteditpd(name,value,caption : string;index : integer);
var i   : integer;
    bol : boolean;
begin
  if sltvar.name[sltvar.vln] <> ''    //达到最大时
  then sltvarclearpd;
  i := 1; bol := false;
  repeat
    if sltvar.name[i] = ''
    then begin
           sltvar.name [i]       := name;
           sltvar.caption [i]    := caption;
           sltvar.value[i]       := value;
           sltvar.index[i]       := index;
           sltvar.tedit[i].Text  := value;
           sltvar.tedit[i].Color := cllime;
           sltvar.sltname  := sltvar.sltname  + sltvar.name [i]  + ',';
           sltvar.sltvalue := sltvar.sltvalue + sltvar.value[i]  + ',';
           sltvar.sltik    := i;
           bol := true;
         end;
    inc(i);
  until (i > sltvar.vln) or bol;
end;


function getselectnumfu:integer;
var i   : integer;
begin
 result:=0;
 for i:=1 to sltvar.vln+1 do
 begin
    if sltvar.name[i] = ''
    then begin
    result:=i-1;
    break ;
    end;
 end;
end;


function getselecttextfu:string;
var i   : integer;
begin
 result:='';
 for i:=1 to sltvar.vln do
 begin
    if sltvar.name[i] = ''
    then  break
    else  result:=result+' <'+sltvar.tedit[i].Text+'>';
 end;
end;


{选择多字段查询（包括单字段）需要给出 库表名 sn
字段名 Mzd  := 'aaa,bbb,ccc,'
字段值 Mval := 'aaa,bbb,ccc' 及查询的长度 ln 、排序串 order by 字段名1,字段名2,字段名3}
function selectMultisfu(Quy:TQuery;sn,Mzd,Mval:string;ln:integer;tj:string):integer;
var i : integer;
    svn,svz,sxz,sxx : string;
    gs : string;
begin
  result := 0;
  svn := mzd;
  svz := mval; sxz := '';
  if svn = ''
  then begin
         messagedlg('没有选则字段!',mtinformation,[mbok],0);
         Exit;
       end;
  i := 1;
  repeat
    gs := sltfu(svn);
    sxz := sxz + gs + ' =:' + gs + ' and ' ;
    inc(i);
  until i > ln;
  delete(sxz,length(sxz)-3,4);
  quy.Close;
  quy.SQL.Clear;
  quy.SQL.Add('select * from ' + sn + ' where ' + sxz + tj );
  i := 0;
  repeat
    sxx := sltfu(svz);
    if sxx = ''
    then quy.Params[i].Value  := sxx
    else if (quy.FieldByName(quy.Params[i].Name).DataType = ftDate) or
            (quy.FieldByName(quy.Params[i].Name).DataType = ftDateTime)
         then quy.Params[i].AsDate := strtodate(sxx)
         else quy.Params[i].Value  := sxx;
    inc(i);
  until i = ln;
  quy.Active := true;
  quy.First;
  result := quy.RecordCount;
end;

{选择单字段（或）查询需要给出 库表名 sn
字段名 Mzd  := 'aaa'
字段值 Mval := 'aaa,bbb,ccc' 及查询的长度 ln 、排序串 order by 字段名1,字段名2,字段名3}
function selectORfu(Quy:TQuery;sn,Mzd,Mval:string;ln:integer;tj:string):integer;
var i : integer;
    s,sxx : string;
begin
  result := 0;
  if mzd = ''
  then begin
         messagedlg('没有选则字段!',mtinformation,[mbok],0);
         Exit;
       end;

  s := '';
  i := 1;
  repeat
    s := s + mzd + ' = :' + mzd + inttostr(i) + ' or ' ;
    inc(i);
  until i > ln;
  delete(s,length(s)-3,3);

  quy.Close;
  quy.SQL.Clear;
  quy.SQL.Add('select * from ' + sn + ' where '+ s + tj );

  s := mval;
  i := 0;
  repeat
    sxx := sltfu(s);
    quy.Params[i].Value  := sxx;
    inc(i);
  until i >= ln;
  quy.Active := true;
  quy.First;
  result := quy.RecordCount;
end;

{选择单字段范围查询需要给出 库表名 sn
字段名 Mzd  := 'aaa'
字段小值 vmil := 'aaa'
字段小值 vmax := 'aaa'
及排序串 order by 字段名1,字段名2,字段名3}
function selectBEGtoENDfu(Quy:TQuery;sn,vZD,vmil,vmax : string;tj:string):integer;
begin
  result := 0;
  if (vmil = '' ) or ( vmax = '' )
  then begin
         messagedlg('没有选则字段及字段值!',mtinformation,[mbok],0);
         Exit;
       end;

  if not (messagedlg('查询从( ' + vmil + ' ) 到 ( ' + vmax+ ' ) 吗? '
           ,mtconfirmation,[mbyes,mbno],0) = mryes)
  then Exit;

  quy.Close;
  quy.SQL.Clear;
  quy.SQL.Add('select * from ' + sn + ' where ( '
     + vZD + ' >= :aa ) and ( ' + vZD + ' <= :bb ) ' + tj );
  if (quy.FieldByName(vZD).DataType = ftDate) or
     (quy.FieldByName(vZD).DataType = ftDatetime )
  then begin
         quy.Params[0].AsDate := strtodate(vmil);
         quy.Params[1].AsDate := strtodate(vmax);
       end
  else begin
         quy.Params[0].Value := vmil;
         quy.Params[1].Value := vmax;
       end;
  quy.Active := true;
  result := quy.RecordCount;
end;

{选择多字段范围查询（包括单字段）需要给出 库表名 sn
字段名 Mzd  := 'aaa,bbb,ccc,'
字段值 Mval := 'aaa,bbb,ccc' 及查询的长度 ln
字段名 Mzd  := 'aaa'
字段小值 vmil := 'aaa'
字段小值 vmax := 'aaa'
排序串 order by 字段名1,字段名2,字段名3}
function selectMultisfwfu(Quy:TQuery;sn,Mzd,Mval:string;ln:integer;vZD,vmil,vmax,tj:string):integer;
var i : integer;
    svn,svz,sxz,sxx : string;
    gs : string;
begin
  result := 0;
  svn := mzd;
  svz := mval; sxz := '';
  if svn = ''
  then begin
         messagedlg('没有选则字段!',mtinformation,[mbok],0);
         Exit;
       end;
  if (vmil = '' ) or ( vmax = '' ) or ( vZD = '' )
  then begin
         messagedlg('没有选则范围值!',mtinformation,[mbok],0);
         Exit;
       end;
  i := 1;
  repeat
    gs := sltfu(svn);
    sxz := sxz + gs + ' =:' + gs + ' and ' ;
    inc(i);
  until i > ln;
  sxz := sxz +  ' ( '+ vZD + ' >= :bb ) and ( ' + vZD + ' <= :cc ) ' ;
  quy.Close;
  quy.SQL.Clear;
  quy.SQL.Add('select * from ' + sn + ' where ' + sxz + tj );

  i := 0;
  repeat
    sxx := sltfu(svz);
    if sxx = ''
    then quy.Params[i].Value  := sxx
    else if (quy.FieldByName(quy.Params[i].Name).DataType = ftDate) or
            (quy.FieldByName(quy.Params[i].Name).DataType = ftDateTime)
         then quy.Params[i].AsDate := strtodate(sxx)
         else quy.Params[i].Value  := sxx;
    inc(i);
  until i = ln;

  if (quy.FieldByName(vZD).DataType = ftDate) or
     (quy.FieldByName(vZD).DataType = ftDatetime )
  then begin
         quy.Params[ln  ].AsDate := strtodate(vmil);
         quy.Params[ln+1].AsDate := strtodate(vmax);
       end
  else begin
         quy.Params[ln  ].Value := vmil;
         quy.Params[ln+1].Value := vmax;
       end;
  quy.Active := true;
  result := quy.RecordCount;
end;

{选择单字段部分值查询 需要给出 库表名 sn
字段名 Mzd  := 'aaa'
字段值 Mval := 'aa%' 及查询的长度 ln 、排序串 order by 字段名1,字段名2,字段名3}
function selectTOallfu(Quy:TQuery;sn,Mzd,Mval:string;ln:integer;tj:string):integer;
var i : integer;
    s,sxx : string;
begin
  result := 0;
  if mzd = ''
  then begin
         messagedlg('没有选则字段!',mtinformation,[mbok],0);
         Exit;
       end;

  s := '';
  i := 1;
  repeat
    s := s + mzd + ' = :' + mzd + inttostr(i) + ' or ' ;
    inc(i);
  until i > ln;
  delete(s,length(s)-3,3);

  quy.Close;
  quy.SQL.Clear;
  quy.SQL.Add('select * from ' + sn + ' where '+ s + tj );

  s := mval;
  i := 0;
  repeat
    sxx := sltfu(s);
    quy.Params[i].Value  := sxx;
    inc(i);
  until i >= ln;
  quy.Active := true;
  quy.First;
  result := quy.RecordCount;
end;

function  insertjlfu(quy:tquery;SQL:TUpdateSQL;st:string;var vjlh:integer;mydbgrid:Tdbgrid):integer;
var i,k   : integer;
    s   : string;
begin
  result := 0;
  quy.CachedUpdates := true;     //;
  SQL.SetParams(ukinsert);
  i := 0;
  while i < (quy.FieldCount-1) do
  begin
    s := sltfu(st);
    k := pos(',',s);
    if k > 0
    then begin
           showmessage('字符串中含有《逗号》出错！'
           + #10 + #13 + '字段名 : ' + mydbgrid.Columns[i].Title.Caption