utilities.pas

企业进销存管理系统

procedure QryRefresh(var xQry: TQuery; asFdNm: Array of string);
var
  i, iLen : Integer;
  avVal : Array of Variant;
  sLocFdNm, sVal : string;
begin
  iLen := High(asFdNm);
  if (iLen=0) then
  begin
    sVal := xQry.FieldByName(asFdNm[0]).Value;
    xQry.DisableControls;
    Try
      xQry.Close;
      xQry.Open;
      xQry.Locate(asFdNm[0], sVal, []);
    Finally
      xQry.EnableControls;
    end;
  end
  else
  begin
    SetLength(avVal, iLen+1);
    for i := 0 to iLen do
    begin
      sLocFdNm := sLocFdNm +';'+ asFdNm[i];
      avVal[i] := xQry.FieldByName(asFdNm[i]).Value;
    end;
    Delete(sLocFdNm, 1, 1);
    xQry.DisableControls;
    Try
      xQry.Close;
      xQry.Open;
      xQry.Locate(sLocFdNm, VarArrayOf(avVal), []);
    Finally
      xQry.EnableControls;
    end;
  end;
end;


//========================================================================
//资料重整
//若程式没有dm单元 , 请在project | options | directories/conditionals |
//conditionals defines 里加上 nodatabase
//重整比对资料库放在 DataBasePath\clone
//========================================================================
procedure ReBuildTable(DatabaseName: string);
//var rDataBaseName:string;
begin
  with TReBuild_Index.Create(Application) do
    try
      rDataBaseName := DatabaseName;
      showmodal;
    finally
      Free;
    end;
end;

//                                                      <5,6>中叁数
//                                                      要在<2,4>中
//  Search_MasterDetailex(table1,[0,1,2,3,5],table2,[3,8],[0,3],[3],['T1020','T1050'],['F01','F01'],['F02','F02,F03'],['厂商名称','品名,规格'],['M.F03','D.F02'],'');
  //=====================主档===主档寻找栏位=明细档=明细==主档==明细=厂商或原料资料表=厂商或原料资== 厂商或原料资料===对应显示的名称 ==========与主档或明细档关==过滤语句
  //                                                寻找  显示  显示                  料表要被寻找   表要被显示栏位                            联的栏位
  //                                                栏位  栏位  栏位                  栏位如编号
  //                                                    (空,全部显示)
  //                       <1>      <2>        <3>   <4>   <5>  <6>        <7>              <8>             <9>                  <10>                <11>         <12>
procedure Search_MasterDetailEx(const MDataSet: TDBDataSet;sMField: array of Integer;const DDataSet: TDBDataSet;sDField,iMDisField_List,iDDisField_List: array of Integer;LookUpData,LookUpPurposeFields,LookUpVisibleFields,LookUpVisibleName,LookUpSourceFields :array of string;sOtherWhere: string='');
begin
  if MDataSet=nil then
  begin
    R_OkMessage(['请输入要查询的资料表名称'],'',MB_ICONWARNING);
    Exit;
  end;
  if High(sMField)=0 then begin
    R_OkMessage(['请输入要查询的栏位编号'],'',MB_ICONWARNING);
    Exit;
  end;

  MasterDetailSearch := TMasterDetailSearch.Create(Application);
  try
    MasterDetailSearch.Exec(MDataSet,sMField,DDataSet,sDField,iMDisField_List,iDDisField_List,sOtherWhere,LookUpData,LookUpPurposeFields,LookUpVisibleFields,LookUpVisibleName,LookUpSourceFields);
  finally
    MasterDetailSearch.Free;
  end;

end;

procedure Search_MasterDetailNoLook(const MDataSet: TDBDataSet;sMField: array of Integer;const DDataSet: TDBDataSet;sDField,iMDisField_List,iDDisField_List: array of Integer;sOtherWhere: string='');
begin
  if MDataSet=nil then
  begin
    R_OkMessage(['请输入要查询的资料表名称'],'',MB_ICONWARNING);
    Exit;
  end;
  if High(sMField)=0 then begin
    R_OkMessage(['请输入要查询的栏位编号'],'',MB_ICONWARNING);
    Exit;
  end;

  MasterDetailSearchNoLook := TMasterDetailSearchNoLook.Create(Application);
  try
    MasterDetailSearchNoLook.Exec(MDataSet,sMField,DDataSet,sDField,iMDisField_List,iDDisField_List,sOtherWhere);
  finally
    MasterDetailSearchNoLook.Free;
  end;

end;


{KeyField必须包在搜寻的栏位内,也就是所搜寻的栏位必须要有KEY 的栏位名称,iKeyField表示搜寻的栏位前几位是KEY栏位}
{procedure Search_MasterDetail(const MDataSet: TDBDataSet;sMField: array of Integer;const DDataSet: TDBDataSet;sDField: array of Integer;sOtherWhere: string='');
begin
  if MDataSet=nil then begin
    R_OkMessage(['请输入要查询的资料表名称']);
    Exit;
  end;
  if High(sMField)=0 then begin
    R_OkMessage(['请输入要查询的栏位编号']);
    Exit;
  end;

  MasterDetailSearch := TMasterDetailSearch.Create(Application);
  try
    MasterDetailSearch.Exec(MDataSet,sMField,DDataSet,sDField,[],[],sOtherWhere);
  finally
    MasterDetailSearch.Free;
  end;
end;    }

//取得磁碟机的序号
function Get_disk_serial_number(lw: Char): LongInt;
var
  pdw : pDWord;
  mc, fl : dword;
begin
  New(pdw);
  GetVolumeInformation(nil,nil,0,pdw,mc,fl,nil,0);
  Result := abs(pdw^);
  dispose(pdw);
end;




//将数字转为一定长度的字串,如不足指定的位数则填入0,如1变为001
function IntToString(scr, count: Integer; cFill:Char='0'): string;
begin
  Result := IntToStr(scr);
  if Length(Result) > count then Exit;
  while Length(Result) < count do Result := cFill + Result;
end;


function CreateQueryFromTable(T: TTable): TQuery;//从一个table建立一个query
begin
  Result := TQuery.Create(nil);
  try
    Result.DatabaseName := T.DatabaseName;
    Result.SessionName := T.SessionName;
  except
    Result.Free;
    Raise;
  end;
end;

function DoSQLAggFunc(T: TTable; FieldName: string; func: TSQLAggFunc;xwhere: string): Extended;
begin
  with CreateQueryFromTable(T) do begin
    try
      SQL.Add(Format(SQL_str_For_Agg[func], [FieldName,T.TableName,xwhere]));
      Open;
      Result := fields[0].Asfloat;
    finally
      Free;
    end;
  end;
end;

//求栏位值的总和
function SumField(T: TTable; Field: string;xWhere: string=''): Extended;
begin
  Result := DoSQLAggFunc(T,Field,xSum,xwhere);
end;

//求栏位值的平均值
function AgvField(T: TTable; Field: string;xWhere: string=''): Extended;
begin
  Result := DoSQLAggFunc(T,Field,xAvg,xwhere);
end;

//求栏位的最大值
function MaxField(T: TTable; Field: string;xWhere: string=''): Extended;
begin
  Result := DoSQLAggFunc(T,Field,xMax,xwhere);
end;

//求栏位的最小值
function MinField(T: TTable; Field: string;xWhere: string=''): Extended;
begin
  Result := DoSQLAggFunc(T,Field,xMin,xWhere);
end;

{KeyField必须包在搜寻的栏位内,也就是所搜寻的栏位必须要有KEY 的栏位名称,iKeyField表示搜寻的栏位前几位是KEY栏位}
procedure xSearch_Data(const DataSet: TDBDataSet;iKeyField:Integer;sField: array of Integer);
begin
  if DataSet=nil then begin
    R_OkMessage(['请输入要查询的资料表名称']);
    Exit;
  end;
  if High(sField)=0 then begin
    R_OkMessage(['请输入要查询的栏位编号']);
    Exit;
  end;

  if iKeyField=0 then begin
    R_OkMessage(['请输入主键的栏位编号']);
    Exit;
  end;

  Form_SearchData := TForm_SearchData.Create(Application);
  try
    Form_SearchData.Exec(DataSet,ikeyField,sField);
  finally
    Form_SearchData.Free;
  end;
end;



function SearchData(DataSet: TTable;  SearchFieldNO:array of Integer):TstringList;
var
  iCount: Integer;
begin
  if DataSet.State <> dsBrowse then begin
    R_OkMessage(['您必须在浏漤状态才能做查询']);
    Result := nil;
    Exit;
  end;
  Result := TStringList.Create;
  try
    with TForm_Search.Create(Application) do begin
      SourceTable := DataSet;
      giSearchCount := High(SearchFieldNo)+1;
      setLength(giSearchField,giSearchCount);
      for iCount := 0 to giSearchCount-1 do
        giSearchField[iCount] := SearchFieldNO[iCount];
      startSearch;
      if GetData then
        Result.Assign(gsReturnString)
      else begin
        Result.Free;
        Result := nil;
      end;
    end;
    sSearchField :=nil;
    giSearchField:=nil;
    sDisplayName:=nil;
  except
    Result.Free;
    raise;
  end;
end;



function AutoSingleIntNo(var xQry: TQuery; sTbNm, sKeyFdNm: string): Integer;
begin
  Result := 1;
  QryExec(xQry,'Select Max('+sKeyFdNm+') as MaxNo From '+sTbNm);
  if (xQry.RecordCount <> 0) then Result := xQry.Fields[0].AsInteger + 1;
end;


procedure Auto_Item_Number(DataSet: TDBDataSet;Item_Field: string; //自动编序号
              Item_Format: Auto_Item_Formate;FirstWord: Char='X';IsInsert: Boolean=True;ODay: TDate=0);
var
  xQuery: TQuery;
  iCount: Integer;
  function GetMainString:string;
  var
    yy,mm,dd: Word;
  begin
    //针对不同的编号格式,取得其主要的编排格式
    DecodeDate(ODay,yy,mm,dd);
    Result := FirstWord;
    case Item_Format of
      cYYYMMxxxx: Result := Result + Copy(IntToStr(yy-911),2,3) + Copy(IntToStr(mm+100),2,2);
      cYYMMDDxxx: Result := Result + Copy(IntToStr(yy-1911+100),2,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      cYYYMM_xxx: Result := Result + Copy(IntToStr(yy-911),2,3) + Copy(IntToStr(mm+100),2,2) + '-';
      YYYMMDDxxx: Result := Copy(IntToStr(yy-911),2,3) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      YYMMDDxxxx: Result := Copy(IntToStr(yy-911),3,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      cYYMMDD_xxxS: Result := Result + Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2) + '_';
      cYYMM_xxxxS : Result := Result + Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2) + '_';
      YYMM_xxxxS  : Result := Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2) + '_';
      YYMMDDxxxxS : Result := Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      YYMMDDxxxS : Result := Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      YYMMxxxxxS : Result := Copy(IntToStr(yy),3,2) + Copy(IntToStr(mm+100),2,2);
      YYMMDDxxx: Result := Copy(IntToStr(yy-911),3,2) + Copy(IntToStr(mm+100),2,2) + Copy(IntToStr(dd+100),2,2);
      YYMMxxxxx: Result := Copy(IntToStr(yy-911),3,2) + Copy(IntToStr(mm+100),2,2);
    end;
  end;

  function GetItemNumber: Integer;
  begin
    Result := 0;
    if (xQuery.RecordCount <> 0) then
      case Item_Format of
        cYYYMMxxxx: Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,4));
        cYYMMDDxxx: Result := StrToInt(Copy(xQuery.Fields[0].AsString,8,3));
        cYYYMM_xxx: Result := StrToInt(Copy(xQuery.Fields[0].AsString,8,3));
        YYYMMDDxxx: Result := StrToInt(Copy(xQuery.Fields[0].AsString,8,3));
        YYMMDDxxxx: Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,4));
        cYYMMDD_xxxS: Result := StrToInt(Copy(xQuery.Fields[0].AsString,9,3));
        cYYMM_xxxxS : Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,4));
        YYMM_xxxxS  : Result := StrToInt(Copy(xQuery.Fields[0].AsString,6,4));
        YYMMDDxxxxS : Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,4));
        YYMMDDxxxS : Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,3));
        YYMMxxxxxS : Result := StrToInt(Copy(xQuery.Fields[0].AsString,5,5));
        YYMMDDxxx : Result := StrToInt(Copy(xQuery.Fields[0].AsString,7,3));
        YYMMxxxxx : Result := StrToInt(Copy(xQuery.Fields[0].AsString,5,5));
      end;
  end;

function AddUserLog(s: string): string;
var
FF : TextFile;
logdir:string;
begin
    logdir:='用户日志文件.LOG';
    AssignFile(FF,logdir);
    Append(FF);
    Writeln(FF,s);
    CloseFile(FF);
end;
    
  function GetNewItemNumber(i: Integer): string;
  begin
    case Item_Format of
      cYYYMMxxxx: Result := Copy(IntToStr(i+10000),2,4);
      cYYMMDDxxx: Result := Copy(IntToStr(i+1000),2,3);
      cYYYMM_xxx: Result := Copy(IntToStr(i+1000),2,3);
      YYYMMDDxxx: Result := Copy(IntToStr(i+1000),2,3);
      YYMMDDxxxx: Result := Copy(IntToStr(i+10000),2,4);
      cYYMMDD_xxxS: Result := Copy(IntToStr(i+1000),2,3);
      cYYMM_xxxxS : Result := Copy(IntToStr(i+10000),2,4);
      YYMM_xxxxS  : Result := Copy(IntToStr(i+10000),2,4);
      YYMMDDxxxxS : Result := Copy(IntToStr(i+10000),2,4);
      YYMMDDxxxS : Result := Copy(IntToStr(i+1000),2,3);
      YYMMxxxxxS : Result := Copy(IntToStr(i+100000),2,5);
      YYMMDDxxx : Result := Copy(IntToStr(i+1000),2,3);
      YYMMxxxxx : Result := Copy(IntToStr(i+100000),2,5);
    end;
  end;

  function GetMaxNumber:Integer;
  begin
    Result := 0;
    case Item_Format of
      cYYYMMxxxx: Result := 9999;
      cYYMMDDxxx: Result := 999;
      cYYYMM_xxx: Result := 999;
      YYYMMDDxxx: Result := 999;
      YYMMDDxxxx: Result := 9999;
      cYYMMDD_xxxS: Result := 999;
      cYYMM_xxxxS : Result := 9999;
      YYMM_xxxxS  : Result := 9999;
      YYMMDDxxxxS : Result := 9999;
      YYMMDDxxxS : Result := 999;
      YYMMxxxxxS : Result := 99999;
      YYMMDDxxx : Result := 999;
      YYMMxxxxx : Result := 99999;
    end;
  end;

  function GetQuery_TableName: string; //取得Query元件的资料表名称
  var
    iPos: Integer;
    iWherePos: Integer;
  begin