datamrp.pas

飞恒进销存,从盒子上下来的,不知这里有没有.

      end
      else //单位不同
      begin
        nOutQty:=Querysum['qty1'];
        if nQty>0 then //进零，出件
        begin
          if (nQty1+nLastQty1)*nPerQty <= nOutQty then  //一次减完
            nOutQty:=(nQty1+nLastQty1)*nPerQty;

          //累计每次利润
          nProfit:=nProfit+nOutQty*(nSalePrice/nPerQty-QuerySum['inprice']);

          //每次减数
          nQty1:=nQty1-int(nOutQty*100/nPerqty+0.5)/100;
        end
        else    //进件,出零
        begin
          if (nQty1+nLastQty1)/nPerQty <= nOutQty then  //一次减完
            nOutQty:=int((nQty1+nLastQty1)*1000/nPerQty+0.5)/1000;

          //累计每次利润
          nProfit:=nProfit+nOutQty*nPerQty*(nSalePrice-QuerySum['inprice']/nPerqty);

          //每次减数
          nLastqty1:=nLastQty1-nOutQty*nPerqty;
        end;

        //更新instore'qty1
        UpdateOneFIFO(querySum['invono'],querySum['seq'],-nOutQty);

        //已减完，返回
        if nLastqty1+nQty1<=0.001 then break;
      end; //else end

      QuerySum.next;
    end; //end of QuerySum.eof

    QuerySum.close;
  end; //end of datamodule2

  //未有进仓记录,用件数价计利润
  if nLastQty1+nQty1>0.001 then
    nProfit:=nProfit+(nQty1+nLastQty1/nPerQty)*(nSalePrice-nInprice);
  //保留三位小数
  result:=int(nProfit*1000)/1000;
end;

procedure TdataPub.UpExpDate(nQty1:real;nseq:integer;sInvo:string);
begin
  with dataModule2 do
  begin
    if query1.Active then query1.close;
    query1.sql.clear;
    query1.sql.add('update instore set qty1= :qty1 where invono= :invono and seq= :seq');
    query1.params[0].asfloat:=nQty1;
    query1.params[1].asstring:=sInvo;
    query1.params[2].asinteger:=nSeq;

    query1.execsql;
  end;
end;

Function TDataPub.GetSql(sFunc:string):string;
begin
  with dataModule2 do
  begin
  if query1.active then Query1.close;
  query1.sql.clear;
  //in sql2000, function is reserved word,so must add []
  if (nDatabaseType=DataSQL7) then
    query1.sql.add('select * from datasql where dataid= :dataid and [function]= :function and number= :number')
  else
    query1.sql.add('select * from datasql where dataid= :dataid and function= :function and number= :number');

  //2003/03/12 datasql.dataid set is 2
  query1.params[0].asinteger:=2;//nDatabaseType;
  query1.params[1].asstring:=sFunc;
  if sUser='gsg' then
    query1.params[2].asstring:='gsg'
  else
    query1.params[2].asstring:='ALL';

  query1.open;

  if not query1.eof then
    result:=trim(query1.fieldbyname('sql1').asstring)+' '+trim(query1.fieldbyname('sql2').asstring)
  else
    result:='';
  end ;//end of datamodule2
end;

procedure  TDataPub.UpdateTarStore(bClearSql:boolean;sTarStore,Barcode:string;nQty,nLastQty:real);
var
 sSql:string;
begin
  with dataModule2 do
  begin
    if bClearSql then
    begin
      sSql:='Update '+sTarStore;
      sSql:=sSql+' set Qty= Qty+ :Qty,LastQty=LastQty+ :LastQty';
      sSql:=sSql+' where barcode= :Barcode';

      if query1.Active then query1.close;
      query1.sql.clear;
      query1.sql.add(sSql);
    end;

    query1.params[0].asfloat:=nQty;
    query1.params[1].asfloat:=nLastQty;
    query1.params[2].asstring:=Barcode;
    query1.execsql;
  end;
end;

//根据出仓，计算库存，分大units小unit2单位,pos出仓为小单位unit2
//1:出仓单位为大单位（大=小单位），qty-outqty
//2:小单位，Lastqty >= outqty,     last-outqty
//3:                < ,减qty,lastqty
procedure  TDataPub.UpdatePOSStore(Barcode,sUnits,sUnit2:string;nQty,nLastQty,nPerQty,nOutQty:real);
var
  sSql:string;
  nTmp,nTmp1:real;
begin
  with dataModule2 do
  begin
    nTmp:=nOutQty;
    if sUnits=sUnit2 then
      sSql:=     ' update store set Qty= Qty- :Qty where barcode= :Barcode'
    else if nLastqty >= nOutqty then
      sSql:=     ' update store set LastQty=Lastqty- :LastQty where barcode= :Barcode'
    else  begin
      sSql:=     ' update store set Qty=Qty - :Qty,lastqty= LastQty- :lastqty where barcode= :Barcode';
      nTmp:=nOutqty - nLastQty;
      nTmp:=ceil(nTmp / nPerqty);
      nTmp1:=nOutQty - nTmp * nPerQty;
    end;

    query1.sql.clear;
    query1.sql.add(sSql);
    query1.params[0].asfloat:=nTmp;
    if ( sUnits<>sUnit2 ) and ( nLastQty < nOutQty  )then
    begin
      query1.params[1].asfloat:=nTmp1;
      query1.params[2].asstring:=Barcode;end
    else query1.params[1].asstring:=Barcode;
    query1.execsql;
  end;
end;

procedure TDataPub.UpdateOutProfit(sInvoNo,sbarcode:string;nProfit,nCost:real);
var
 sSql:string;
begin
  with dataModule2 do
  begin
    sSql:='update outstore set profit= :profit ,cost= :cost';
    sSql:=sSql+' where invono= :invono and barcode= :Barcode';

    if query1.Active then query1.close;
    query1.sql.clear;
    query1.sql.add(sSql);

    query1.params[0].asCurrency:=nProfit;
    query1.params[1].asCurrency:=nCost;
    query1.params[2].asstring:=sInvono;
    query1.params[3].asstring:=sBarcode;
    query1.execsql;
  end;
end;

procedure TDataPub.DeleteOldData(dDelDate:Tdatetime);
begin
  with datamodule2 do
  begin
    if query1.active then query1.close;
    query1.sql.clear;
    query1.sql.add('delete from ledger where Iodate< :iodate');
    query1.params[0].asdatetime:=dDelDate;
    query1.execsql;

    if query1.active then query1.close;
    query1.sql.clear;
    query1.sql.add('delete from Instore where Indate< :iodate');
    query1.params[0].asdatetime:=dDelDate;
    query1.execsql;

    if query1.active then query1.close;
    query1.sql.clear;
    query1.sql.add('delete from outstore where outdate< :iodate');
    query1.params[0].asdatetime:=dDelDate;
    query1.execsql;

    if query1.active then query1.close;
    query1.sql.clear;
    query1.sql.add('update cfg set SaveDate= :Savedate');
    query1.params[0].asdatetime:=dDelDate;
    query1.execsql;
  end;
end;

Function  TDataPub.ToMyValue(oldValue:real):real;
var
  nNum,nAbs:integer;
  nTmp:real;
begin
  nNum:=100;
  case nSumbit of
  0: nNum:=1;
  1: nNum:=10;
  2: nNum:=100;
  3: nNum:=1000;
  4: nNum:=10000;
  end;

  nAbs:=1;
  if OldValue <0 then nAbs:=-1;

  nTmp:=OldValue*nNum;

  nTmp:=nTmp+0.5*nAbs; //四舍五入

  nTmp:=int(nTmp)/nNum;

  result:=nTmp;
end;

Function TDataPub.GetPinYin(sName:string):string;
var
  i,nLen,nAsc:integer;
  sTmp:string;
  c:char;
begin
  result:='';
  nLen:=length(sName) div 2;
  for i:=1 to nLen do
  begin
    sTmp:= copy(sName,2 * (I - 1) + 1,1);
    nAsc:=ord(sTmp[1]) * 256 ;
    sTmp:=copy(sName,2 * I,1);
    nAsc:=nAsc+ ord(sTmp[1]);
    c := getLetter(nAsc);
    if c <> '0' then
       result := result + c
  end;
end;

Function TDataPub.GetLetter(nAsc:integer):char;
begin
  case nAsc of
    45217.. 45252 :result:= 'A';
    45253.. 45760 :result:= 'B';
    45761.. 46317 :Result:= 'C';
    46318.. 46825 :Result:= 'D';
    46826.. 47009 :Result:= 'E';
    47010.. 47296 :Result:= 'F';
    47297.. 47613 :Result:= 'G' ;
    47614.. 48118 :Result:= 'H' ;
    48119.. 49061 :Result:= 'J';
    49062.. 49323 :Result:= 'K';
    49324.. 49895 :Result:= 'L';
    49896.. 50370 :Result:= 'M' ;
    50371.. 50613 :Result:= 'N' ;
    50614.. 50621 :Result:= 'O' ;
    50622.. 50905 :Result:= 'P';
    50906.. 51386 :Result:= 'Q';
    51387.. 51445 :Result:= 'R';
    51446.. 52217 :Result:= 'S';
    52218.. 52697 :Result:= 'T';
    52698.. 52979 :Result:= 'W';
    52980.. 53640 :Result:= 'X';
    53689.. 54480 :Result:= 'Y';
    54481.. 55289 :Result:= 'Z';
    else Result:='0'
  end;
end;


Function TDataPub.ExcelColStr(nCol:integer):string;
var
  c:string;
  i:integer;
begin
  c:='';
  i:=nCol;
  if i >26 then
    c:=chr(ord('A')+i div 26 -1);
  i:=i mod 26;
  if nCol mod 26=0 then i:=26;
  result:=c+chr(ord('A')+i-1);
end;

procedure TDataPub.InsEvent(appopr,description:string);
var
  s:string;
begin
 with datamodule2 do
 begin
  s:='insert into eventlog ';
  s:=s+' values( :Indate, :number, :appopr, :logtime, :description)';
  if query1.active then query1.close;
  query1.sql.clear;
  query1.sql.add(s);

  query1.params[0].asdatetime:=date;
  query1.params[1].asstring:=sUser;
  query1.params[2].asstring:=appopr;
  query1.params[3].asdatetime:=now;
  query1.params[4].asstring:=description;

  query1.execsql;
 end;
end;

procedure TDataPub.InsFinanceUpBank(sBankId,sCusName,sother:string;nDebits,nCredits:real;dIndate:tDatetime);
var
  sSql1:string;
begin
  sSql1:='insert into finance (BankId, CusName, Debits, Credits, xdate, indate,other) ';
  sSql1:=sSql1+' values ( :BankId, :CusName, :Debits, :Credits, :xdate, :indate, :other)';
  with datamodule2 do
  begin
      query1.close;
      query1.sql.clear;
      query1.sql.add(sSql1);

      query1.params[0].asstring:=sBankid;
      query1.params[1].asstring:=sCusName;
      query1.params[2].asfloat:=nDebits;    //收入
      query1.params[3].asfloat:=nCredits;   //支出
      query1.params[4].asdatetime:=date;
      query1.params[5].asdatetime:=dIndate;
      query1.params[6].asstring:=sOther;

      query1.execsql;

      //对于Access,sql anywhere 未有存储过程，需要手工更新bank表
      query1.sql.clear;
      query1.sql.add('update bank set amount=amount+ :amount where bankid= :bankid');
      query1.params[0].asfloat:=nDebits+nCredits*-1;
      query1.params[1].asstring:=sBankid;
      query1.execsql;
  end;
end;

//一张单，付一部分，赊一部分
//处理时，先现付入帐，再转为未付赊销
procedure TDataPub.PayToLend(dIodate1,dIoDate2:TDatetime;sCusName:string);
var
  sSql1:string;
begin
  sSql1:='update ledger set IsPay=false where ';
  sSql1:=sSql1+' ioDate>= :IODate and IODate<= :IODate1 and name like :name';
  if datapub.nDatabaseType = DataAccess then
  begin
    sSql1:='update ledger set IsPay=false where ';
    sSql1:=sSql1+' ioDate>= :IODate and IODate<= :IODate1 and name like :name';
    sSql1:=sSql1+' and InOut='''+'O'+''' and IsPay=True and others1 = :others1' end
  else begin
    sSql1:='update ledger set IsPay=0 where ';
    sSql1:=sSql1+' ioDate>= :IODate and IODate<= :IODate1 and name like :name';
    sSql1:=sSql1+' and InOut='''+'O'+''' and IsPay=1 and others1 = :others1';
  end;
  with datamodule2 do
  begin
      query1.close;
      query1.sql.clear;
      query1.sql.add(sSql1);

      query1.params[0].asdatetime:=dIoDate1;
      query1.params[1].asdatetime:=dIoDate2;
      query1.params[2].asstring:=sCusName+'%';
      query1.params[3].asstring:='赊销';

      query1.execsql;
  end;
end;

procedure TDataPub.UpDeposit(sCardno:string;nAmount:real);
var
  sSql1:string;
begin
  sSql1:='update deposit set balance=balance- :nAmount where ';
  sSql1:=sSql1+' cardno= :cardno ';
  with datamodule2 do
  begin
      query1.close;
      query1.sql.clear;
      query1.sql.add(sSql1);

      query1.params[0].asfloat:=nAmount;
      query1.params[1].asstring:=scardno;

      query1.execsql;
  end;
end;

function TDataPub.GetWindowsPlatFormId:integer;
var
  OS: TOSVersionInfo;
  PlatformId: Dword;
{  function Plat(Pl: DWORD): string;
  begin
    case Pl of
      VER_PLATFORM_WIN32s: result := 'Win32s on Windows 3.1x';
      VER_PLATFORM_WIN32_WINDOWS: result := 'Windows 95/98/ME';
      VER_PLATFORM_WIN32_NT:   result := 'Windows NT/2000/XP';
    else
      result := 'Windows';
    end;
  end;}
begin
  PlatformId := 0;
  try
  with OS do
  begin
      dwOSVersionInfoSize := SizeOf(TOSVersionInfo);
      if GetVersionEx(OS) then
      begin
        PlatformId := 0;//dwPlatformId;
        if dwPlatFormid =  VER_PLATFORM_WIN32_NT then
         if  (dwMajorVersion = 5 )and (dwMinorVersion = 1 ) then
          platformId:=1; //xp
      end;
  end; //Os
  except
  end;
  result:=PlatFormId;
end;

end.