invcomm.pas.svn-base

这是一个功能齐全的,代码完整的ERP企业信息管理系统,现在上传和大家分享

      AAmount:=AAmount*-1;
    end; 
    AToQuery.FieldByName('I501_005').Value:=AToQuery.FieldByName('I501_005').AsFloat+AQty;  //库存数量
    AToQuery.FieldByName('I501_007').Value:=AToQuery.FieldByName('I501_007').AsFloat+AAmount;  //库存金额
    if AToQuery.FieldByName('I501_005').AsFloat<>0 then
      AToQuery.FieldByName('I501_006').Value:=AToQuery.FieldByName('I501_007').Value/AToQuery.FieldByName('I501_005').Value  //单位价格
    else
      AToQuery.FieldByName('I501_006').Value:=AFromQuery.FieldByName('FPrice').Value;
    AToQuery.Post;
    AFromQuery.Next;
  end;
  AFromQuery.Close;
  AToQuery.Close;
  AFromQuery.Free;
  AToQuery.Free;
end;

procedure GetPriorValue(AWhsNo,ALocNo,AItemNo,ASeqNo:Integer;ADate:TDateTime;var AQty,APrice,AAmount:Double);
var
  BQuery:TADOQuery;
begin
  BQuery:=TADOQuery.Create(nil);
  BQuery.Connection:=SYSDM.ADOC;
  BQuery.Close;
  BQuery.SQL.Clear;
  BQuery.SQL.Add('select top 1 * from INV500');
  BQuery.SQL.Add('where I500_007='+IntToStr(AWhsNo));
  if ALocNo<>0 then BQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo));
  BQuery.SQL.Add('  and I500_009='+IntToStr(AItemNo));
  BQuery.SQL.Add('  and I500_005<='+GetDateString(ADate));
  BQuery.SQL.Add('  and I500_017<'+IntToStr(ASeqNo));
  BQuery.SQL.Add('order by I500_005 desc,I500_017 desc,I500_002 desc');
  BQuery.Open;
  AQty:=BQuery.FieldByName('I500_014').AsFloat;
  APrice:=BQuery.FieldByName('I500_015').AsFloat;
  AAmount:=BQuery.FieldByName('I500_016').AsFloat;
//showmessage(BQuery.SQL.Text+#13+BQuery.FieldByName('I500_014').asstring);
  BQuery.Close;
  BQuery.Free;
end;

//更新料品流水帐表(INV500)的库存数量(I500_014)、库存单价(I500_015)、库存金额(I500_016)字段
//(依据出入仓单表或转仓单表)
//进出单据和转仓单据新增、修改、删除时调用
//调用说明：
//  新增时，在单据保存后调用一次
//  修改时，在单据保存前调用一次，在单据保存后再调用一次
//  删除时，在单据删除前调用一次
//  注：该函数中已按“移动加权平均法”计算出库存单价
//ACode：单据编号
//AType：单据类型，'INV500'=出入仓单，'INV510'=转仓单
//AMode: 更新状态，ADD=新增，DEL=删除
procedure UpdateInvBookBanlance(ACode,AType,AMode:String);
var
  AQuery:TADOQuery;
  AItemNo,AWhsNo,ALocNo,ATypeNo,ASeqNo,ASeqNo1:Integer;
  AQty,AAmount,AInvQty,AInvAmount,AInvPrice:Double;
  ADate:TDateTime;
begin
  AQuery:=TADOQuery.Create(nil);
  AQuery.Connection:=SYSDM.ADOC;

  AQuery.Close;
  AQuery.SQL.Clear;
  AQuery.SQL.Add('select I500_005,I500_017 from INV500 where I500_001='+''''+ACode+'''');
  AQuery.Open;
  if AQuery.IsEmpty then
  begin
    AQuery.Close;
    AQuery.Free;
    Exit;
  end;
  ADate:=AQuery.Fields[0].AsDateTime;
  ASeqNo:=AQuery.Fields[1].AsInteger;
  //1.取得相关的流水帐记录，并逐条更新流水帐表余额字段(INV500)
  AQuery.Close;
  AQuery.SQL.Clear;
  AQuery.SQL.Add('select * from INV500 A');
  AQuery.SQL.Add('where I500_005>='+GetDateString(ADate)+' and I500_017>='+IntToStr(ASeqNo));
  AQuery.SQL.Add('  and exists(select 1 from INV500 B where A.I500_007=B.I500_007 and A.I500_008=B.I500_008 and A.I500_009=B.I500_009 and B.I500_001='+''''+ACode+''''+')');
  AQuery.SQL.Add('order by I500_005,I500_017,I500_002');
  AQuery.Open;
  while not AQuery.Eof do
  begin
    //取得上条记录的库存数量、库存金额
    ADate:=AQuery.FieldByName('I500_005').AsDateTime;
    AWhsNo:=AQuery.FieldByName('I500_007').AsInteger;
    ALocNo:=AQuery.FieldByName('I500_008').AsInteger;
    AItemNo:=AQuery.FieldByName('I500_009').AsInteger;
    ASeqNo1:=AQuery.FieldByName('I500_017').AsInteger;
    //出入类型(0=期初，1=入库，2=出库，3=调整)
    ATypeNo:=AQuery.FieldByName('I500_004').AsInteger;
    AQty:=AQuery.FieldByName('I500_011').AsFloat;
    AAmount:=AQuery.FieldByName('I500_013').AsFloat;
    GetPriorValue(AWhsNo,ALocNo,AItemNo,ASeqNo1,ADate,AInvQty,AInvPrice,AInvAmount);
    if AMode='DEL' then
    begin
      AQty:=AQty*-1;
      AAmount:=AAmount*-1;
    end;
    if ATypeNo=2 then
    begin
      AQty:=AQty*-1;
      AAmount:=AAmount*-1;
    end;

    AInvQty:=AInvQty+AQty;
    AInvAmount:=AInvAmount+AAmount;
    if AInvQty=0 then AInvPrice:=0 else AInvPrice:=AInvAmount/AInvQty;

    AQuery.Edit;
    AQuery.FieldByName('I500_014').AsFloat:=AInvQty;
    AQuery.FieldByName('I500_015').AsFloat:=AInvPrice;
    AQuery.FieldByName('I500_016').AsFloat:=AInvAmount;
    AQuery.Post;
    AQuery.Next;
  end;

  AQuery.Close;
  AQuery.Free;
end;

//更新与进出仓单关联的来源单据(采购定单、销售定单、车间定单、进出仓单)的已收发量
//ACode：单据编号
//AMode: 更新状态，ADD=新增，DEL=删除 修改=先删除后新增
procedure UpdateOrderData(ACode,AMode:String);
var
  AOrder:String;
  AType,AInout,ALine,AStatus:integer;
  AQuery:TADOQuery;
  AQty:Double;
begin
  SYSDM.qryQuery.Close;
  SYSDM.qryQuery.SQL.Clear;
  SYSDM.qryQuery.SQL.Add('select * from INV500A,INV550 where I500A_002=I550_001 and I500A_001='+''''+ACode+'''');
  SYSDM.qryQuery.Open;
  //影响类型(0=期初数量，1=入库数量，2=出库数量，3=调整数量，4=库存转仓)
  AInOut:=SYSDM.qryQuery.FieldByName('I550_004').AsInteger;
  //相关单据(0=手工输入，1=采购定单，2=销售定单，3=车间定单，4=出入仓单)
  AType:=SYSDM.qryQuery.FieldByName('I550_005').AsInteger;

  if AType=0 then Exit;

  AQuery:=TADOQuery.Create(nil);
  AQuery.Connection:=SYSDM.ADOC;
  AQuery.Close;
  AQuery.SQL.Clear;
  AQuery.SQL.Add('select * from INV500B where I500B_001='+''''+ACode+'''');
  AQuery.Open;
  while not AQuery.Eof do
  begin
    AOrder:=AQuery.FieldByName('I500B_011').AsString;
    ALine:=AQuery.FieldByName('I500B_012').AsInteger;
    //进出单的进出数量(转换成库位单位)  进出数量*单位转换率
    AQty:=AQuery.FieldByName('I500B_005').AsFloat*AQuery.FieldByName('I500B_014').AsFloat;
    case AInOut of
      1:AQty:=AQty;  //入库
      2:AQty:=AQty*-1;  //出库
    end;
    if AMode='DEL' then AQty:=AQty*-1;
    case AType of
      1:AQty:=AQty;  //采购定单
      2:AQty:=AQty*-1;  //销售定单
      3:AQty:=AQty;  //车间定单  产品入库
      4:AQty:=AQty;  //出入仓单  还出单、还入单
    end;
    AStatus:=0;

    if AType=1 then  //1=采购定单
    begin
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('update PUR510B set P510B_016=P510B_016+'+FloatToStr(AQty)+'/P510B_023 where P510B_001='+''''+ AOrder+''''+' and P510B_002='+IntToStr(ALine));
      SYSDM.qryQuery.ExecSQL;
      //定单状态(0=新建，1=核准，2=执行，3=收货，4=完成，5=取消)
      //如果 已收货量=0，则状态=执行，否则 采购数量>已收货量，则状态=收货，采购数量<=已收货量，状态=完成
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('select sum(P510B_005),sum(P510B_016) from PUR510B where P510B_001='+''''+ AOrder+''''+' group by P510B_001');
      SYSDM.qryQuery.Open;
      if SYSDM.qryQuery.Fields[1].AsFloat=0 then
        AStatus:=2
      else
      begin
        if SYSDM.qryQuery.Fields[0].AsFloat>SYSDM.qryQuery.Fields[1].AsFloat then
          AStatus:=3
        else if SYSDM.qryQuery.Fields[0].AsFloat<=SYSDM.qryQuery.Fields[1].AsFloat then
          AStatus:=4;
      end;
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('update PUR510A set P510A_004='+IntToStr(AStatus)+' where P510A_001='+''''+ AOrder+'''');
      SYSDM.qryQuery.ExecSQL;
    end else
    if AType=2 then  //2=销售定单
    begin
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('update ORD510B set O510B_016=O510B_016+'+FloatToStr(AQty)+'/O510B_023 where O510B_001='+''''+ AOrder+''''+' and O510B_002='+IntToStr(ALine));
      SYSDM.qryQuery.ExecSQL;
      //定单状态(0=新建，1=执行，2=发货，3=取消，4=锁定，5=完成)
      //如果 已发货量=0，则状态=执行，否则 销售数量>已发货量，则状态=发货，销售数量<=已发货量，状态=完成
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('select sum(O510B_005),sum(O510B_016) from ORD510B where O510B_001='+''''+ AOrder+''''+' group by O510B_001');
      SYSDM.qryQuery.Open;
      if SYSDM.qryQuery.Fields[1].AsFloat=0 then
        AStatus:=1
      else
      begin
        if SYSDM.qryQuery.Fields[0].AsFloat>SYSDM.qryQuery.Fields[1].AsFloat then
          AStatus:=2
        else if SYSDM.qryQuery.Fields[0].AsFloat<=SYSDM.qryQuery.Fields[1].AsFloat then
          AStatus:=5;
      end;
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('update ORD510A set O510A_004='+IntToStr(AStatus)+' where O510A_001='+''''+ AOrder+'''');
      SYSDM.qryQuery.ExecSQL;
    end else
    if AType=3 then  //3=车间定单
    begin

    end else
    if AType=4 then  //4=出入仓单
    begin
      SYSDM.qryQuery.Close;
      SYSDM.qryQuery.SQL.Clear;
      SYSDM.qryQuery.SQL.Add('update INV500B set I500B_015=I500B_015+'+FloatToStr(AQty)+'/I500B_014 where I500B_001='+''''+ AOrder+''''+' and I500B_002='+IntToStr(ALine));
      SYSDM.qryQuery.ExecSQL;
    end;
    AQuery.Next;
  end;
  AQuery.Close;
  AQuery.Free;
end;

//更新流水帐表
//ACode：单据编号
//AType：单据类型，'INV500'=出入仓单，'INV510'=转仓单
//AMode: 更新状态，ADD=新增，DEL=删除
procedure UpdateInv500(ACode,AType,AMode:String;ASeqNo:Integer);
begin
  if AMode='DEL' then
  begin
    //删除流水表的相关记录
    SYSDM.qryQuery.Close;
    SYSDM.qryQuery.SQL.Clear;
    SYSDM.qryQuery.SQL.Add('delete from INV500 where I500_001='+''''+ACode+'''');
    SYSDM.qryQuery.ExecSQL;
    Exit;
  end;

  if ASeqNo=0 then
  begin
    //取得流水编号、及出入类型
    SYSDM.qryQuery.Close;
    SYSDM.qryQuery.SQL.Clear;
    SYSDM.qryQuery.SQL.Add('select max(I500_017) from INV500');
    SYSDM.qryQuery.Open;
    ASeqNo:=SYSDM.qryQuery.Fields[0].AsInteger+1;
  end;
  //取得相关的单据记录，更新流水帐表(INV500)
  if AType='INV500' then  //出入仓单
  begin
    //单据编号,单据行号,操作类型,出入类型,操作日期,
    //摘要说明,仓库编号,库位编号,料品编号,计量单位,
    //进出数量,单位价格,进出金额,库存数量,库存单价,
    //库存金额,顺序编号
    SYSDM.qryQuery.Close;
    SYSDM.qryQuery.SQL.Clear;
    SYSDM.qryQuery.SQL.Add('insert into INV500(I500_001,I500_002,I500_003,I500_004,I500_005,');
    SYSDM.qryQuery.SQL.Add('                   I500_006,I500_007,I500_008,I500_009,I500_010,');
    SYSDM.qryQuery.SQL.Add('                   I500_011,I500_012,I500_013,I500_014,I500_015,');
    SYSDM.qryQuery.SQL.Add('                   I500_016,I500_017)');