急急呀，一个关于库存核算的问题，请大家帮一下忙。

Question

我是个小菜菜来的，
我现在在做着一个库存方面的程序。
这个取价问题是先进先出的。
我没有做过这方面的东西。
高手们请给些指点。每一次入库我都做一条记录，有商品编号,单价,数量
然后出库是取最先入库的商品的价格。
应该怎么取呢。
我自己想到的问题有，
1、当一次出库数量大于一次入库数量时，就会取二条入库记录的商品的单价了，应该怎么处理
2、当出现退库情况时应该怎么处理呢。万分感谢。

Answer 1

第二个问题:可以让其值为负(冲数)
第一个问题:
1.按照日期将入库表和出库表排序分别存入临时表A,B
2.按日期,正负相抵,去掉A,B中的负数(这个叫做冲数)
3.先进先出,计算数据示例代码如下:
function TLfamountgl.calMatStoreTime(prodNo,types:string;wMonth,wYear:Word;var vSum_qty,v_total_out_qty:Real):double;
var
  sql,sql_1:string;
  beginTime,endTime:string;
  in_date,out_date:string;
  reduce_qty,sum_qty,out_qty,total_out_qty:double;
  difDay:real;
  oldsep:char;
  nType:Integer;
begin
   if types='一般贸易' then nType:=0
   else nType:=1;
   
   oldsep:=dateseparator;
   dateseparator:='-';
   reduce_qty:=0;
   sum_qty:=0;
   out_qty:=0;
   total_out_qty:=0;    beginTime:=DateTimeToStr(EncodeDate(wYear,wMonth,1));
    endTime:=DateTimeToStr(IncMonth(StrToDateTime(beginTime))-1);
    beginTime:='2003-1-1';    //初始化
    if AdoQuery7.Active=true then
       AdoQuery7.Active:=false;
    AdoQuery7.SQL.Clear;
    AdoQuery7.SQL.Add('exec init_InOutStore '+#39+prodno+#39+','
             +QuotedStr(userid)+','+IntToStr(nType)+','
             +#39+beginTime+#39+','+#39+endTime+#39);
    AdoQuery7.ExecSQL;
    AdoQuery7.Close;     //去掉负数
    RemoveNegativeInOut('select * from c_in_store '
             +' where userid='+QuotedStr(userid)+' order by indate');    RemoveNegativeInOut('select * from c_out_store '
            +' where userid='+QuotedStr(userid)+' order by outdate');   //出库
   if AdoQuery6.Active=true then
     AdoQuery6.Close;
   AdoQuery6.SQL.Clear;   sql_1:='select * from c_out_store where userid='+Quotedstr(userid)+' order by outdate';
   AdoQuery6.SQL.Add(sql_1);
   AdoQuery6.Open;
   AdoQuery6.First;   //入库
   if AdoQuery5.Active=true then
     AdoQuery5.Close;   AdoQuery5.SQL.Clear;
   sql:= 'select * from c_in_store where userid='+QuotedStr(userid)+' order by indate';
   AdoQuery5.SQL.Add(sql);
   AdoQuery5.Open;
   AdoQuery5.First;   //开始计算
   in_date:=AdoQuery5.FieldByName('indate').AsString;
   reduce_qty:=AdoQuery5.FieldByName('inqty').AsFloat;   while (not AdoQuery6.Eof) and (not AdoQuery5.Eof) do
   begin
      out_date:=AdoQuery6.FieldByName('outdate').AsString;
      out_qty:=AdoQuery6.FieldByName('outqty').AsFloat;
      difDay:=StrToDate(out_date)-StrToDate(in_date)+1;      if Reduce_qty>=out_qty then
      begin
         sum_qty:=sum_qty+out_qty*difDay;
         total_out_qty:=total_out_qty+out_qty;
         reduce_qty:=reduce_qty-out_qty;
         out_qty:=0;
      end
      else begin
         sum_qty:=sum_qty+reduce_qty*difDay;
         total_out_qty:=total_out_qty+reduce_qty;
         out_qty:=out_qty-reduce_qty;
         reduce_qty:=0;         while out_qty>0 do
         begin
            if AdoQuery5.Eof then
            begin
            //   Raise  Exception.Create('出现严重错误：出库多而入库少!'+#10#13+'请与信息技术部联系!');
              memo1.Lines.Add(prodNo+'('+Adoquery2.FieldByName('prodname').AsString+'):'
                   +'出库剩余数：'+FloatToStr(out_qty)+'   '
                   +'日期：' +out_date);
              break;
            end;            AdoQuery5.Next;            in_date:=AdoQuery5.FieldByName('indate').AsString;
            difDay:=StrToDate(out_date)-StrToDate(in_date)+1;
            reduce_qty:=AdoQuery5.FieldByName('inqty').AsFloat;            if  reduce_qty>=out_qty then
            begin
               sum_qty:=sum_qty+out_qty*difDay;
               total_out_qty:=total_out_qty+out_qty;
               reduce_qty:=reduce_qty-out_qty;
               out_qty:=0;
            end
            else begin
               sum_qty:=sum_qty+reduce_qty*difDay;
               total_out_qty:=total_out_qty+reduce_qty;
               out_qty:=out_qty-reduce_qty;
               reduce_qty:=0;
            end;
         end;
      end;
      AdoQuery6.Next;
   end;   AdoQuery5.Close;
   AdoQuery6.Close;
   dateseparator:=oldsep;   v_total_out_qty:=total_out_qty;
   vSum_qty:=sum_qty;
   if total_out_qty=0 then
   begin
      Result:=0;
      Exit;
   end;
   Result:=sum_qty/total_out_qty;
end;去掉负数procedure TLfamountgl.RemoveNegativeInOut(sqlStr:string);
var
  Reduce_Qty:Double;
  nRecord:Integer;
begin
    AdoQuery5.Close;
    AdoQuery5.SQL.Clear;
    AdoQuery5.SQL.Add(sqlStr);
    AdoQuery5.Open;
    nRecord:=AdoQuery5.RecordCount;    
    AdoQuery5.First;
    
    while not AdoQuery5.Eof do
    begin
       Reduce_qty:=AdoQuery5.Fields[0].AsFloat;
       while Reduce_qty<=0 do
       begin
           AdoQuery5.Delete;
           if Reduce_qty=0 then
              Break;           Dec(nRecord);
           if nRecord=0 then
             Raise Exception.Create('入库的负数比正数还大');           Reduce_qty:=Reduce_qty+AdoQuery5.Fields[0].AsFloat;
           if Reduce_qty>0 then
           begin
              AdoQuery5.Edit;
              AdoQuery5.Fields[0].AsFloat:=Reduce_Qty;
              AdoQuery5.Post;
           end;
       end;
       if AdoQuery5.Fields[0].AsFloat>0 then
          AdoQuery5.Next;
    end;
    AdoQuery5.Close;
end;以上代码,仅供参考.

Answer 2

drop table c_in_store
go
drop table c_out_store
go  create table c_in_Store
  (
    inqty       numeric(18,6),
    inDate      datetime,
    row_id      int identity(1,1) primary key 
  )
go
  create table c_out_Store
  (
    outqty       numeric(18,6),
    outDate      datetime,
    row_id      int identity(1,1) primary key 
  )
godrop procedure init_InOutStore
gocreate procedure init_InOutStore @prodno varchar(10),@initDate datetime,@endDate datetime
as
begin
  truncate table c_in_store
  truncate table c_out_store    --初始入库值
  insert into c_in_store
    Select qty,@initDate-nDay
      From initGuanStoreDay
     where prodno=@prodno    --入库
  insert into c_in_store
     select qty,period
       from lifeng..instore
      where period between @initDate and @endDated
        and prodno=@prodno
        and isok=0  --出库 
  insert into c_out_Store 
    select custqty,period
      from lifeng..outstore
     where period between @initDate and @endDate
       and prodno=@prodno
end  
go