📄 invcomm.pas.svn-base
字号:
SYSDM.qryQuery.SQL.Add('select I500A_001,I500B_002,I550_001,I550_004,I500A_004,');
SYSDM.qryQuery.SQL.Add(' I500A_003,I500A_010,I500B_008,I500B_003,I150_011,');
SYSDM.qryQuery.SQL.Add(' I500B_005*I500B_014,I500B_006,I500B_007,0,0,');
SYSDM.qryQuery.SQL.Add(' 0,'+IntToStr(ASeqNo));
SYSDM.qryQuery.SQL.Add('from INV500A,INV500B,INV550,INV150');
SYSDM.qryQuery.SQL.Add('where I500A_001=I500B_001 and I500A_002=I550_001 and I500B_003=I150_001 and I500A_001='+''''+ACode+'''');
SYSDM.qryQuery.ExecSQL;
end else
if AType='INV510' then //转仓单
begin
//单据编号,单据行号,操作类型,出入类型,操作日期,
//摘要说明,仓库编号,库位编号,料品编号,计量单位,
//进出数量,单位价格,进出金额,库存数量,库存单价,
//库存金额,顺序编号
//转出仓库 操作类型=2 出库
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)');
SYSDM.qryQuery.SQL.Add('select I510A_001,I510B_002,I510A_002,2,I510A_004,');
SYSDM.qryQuery.SQL.Add(' I510A_003,I510A_005,I510B_010,I510B_003,I150_011,');
SYSDM.qryQuery.SQL.Add(' I510B_005,I510B_006,I510B_007,0,0,');
SYSDM.qryQuery.SQL.Add(' 0,'+IntToStr(ASeqNo));
SYSDM.qryQuery.SQL.Add('from INV510A,INV510B,INV550,INV150');
SYSDM.qryQuery.SQL.Add('where I510A_001=I510B_001 and I510A_002=I550_001 and I510B_003=I150_001 and I510A_001='+''''+ACode+'''');
SYSDM.qryQuery.ExecSQL;
//转入仓库 操作类型=1 入库
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)');
SYSDM.qryQuery.SQL.Add('select I510A_001,I510B_002,I510A_002,1,I510A_004,');
SYSDM.qryQuery.SQL.Add(' I510A_003,I510A_007,I510B_011,I510B_003,I150_011,');
SYSDM.qryQuery.SQL.Add(' I510B_005,I510B_006,I510B_007,0,0,');
SYSDM.qryQuery.SQL.Add(' 0,'+IntToStr(ASeqNo+1));
SYSDM.qryQuery.SQL.Add('from INV510A,INV510B,INV550,INV150');
SYSDM.qryQuery.SQL.Add('where I510A_001=I510B_001 and I510A_002=I550_001 and I510B_003=I150_001 and I510A_001='+''''+ACode+'''');
SYSDM.qryQuery.ExecSQL;
end;
//更新料品流水帐表(INV500)的库存数量(I500_014)、库存单价(I500_015)、库存金额(I500_016)字段
UpdateInvBookBanlance(ACode,AType,AMode);
end;
//更新相关的数据，当进出仓单更新时
//ACode：单据编号
//AType：单据类型，'INV500'=出入仓单，'INV510'=转仓单
//AMode: 更新状态，ADD=新增，DEL=删除
procedure UpdateInv500B(ACode,AType,AMode:String;ASeqNo:Integer);
begin
//更新料品流水帐表(INV500)的库存数量(I500_014)、库存单价(I500_015)、库存金额(I500_016)字段
if AMode='DEL' then UpdateInvBookBanlance(ACode,AType,AMode);
//更新与进出仓单关联的来源单据(采购定单、销售定单、车间定单、进出仓单)的已收发量及单据状态
if AType='INV500' then UpdateOrderData(ACode,AMode);
//更新流水帐表
UpdateInv500(ACode,AType,AMode,ASeqNo);
//更新库存余额表(INV501)
UpdateBanlance(ACode,AType);
end;
//更新进出仓单的应收发数量 I500B_013
//在新增、修改、删除进出仓单时，必须更新其后面的进出仓单的应收发数量
//ACode：单据编号
procedure UpdateInv500BOrdQty(ACode:string);
var
AQuery,BQuery:TADOQuery;
AOrder:String;
AType,ALine:Integer;
AOrdQty:Double;
begin
AQuery:=TADOQuery.Create(nil);
AQuery.Connection:=SYSDM.ADOC;
BQuery:=TADOQuery.Create(nil);
BQuery.Connection:=SYSDM.ADOC;
AQuery.Close;
AQuery.SQL.Clear;
AQuery.SQL.Add('select I500B_011,I500B_012,I550_005 from INV500A,INV500B,INV550 where I500A_001=I500B_001 and I500A_002=I550_001 and I500A_001='+''''+ACode+'''');
AQuery.Open;
while not AQuery.Eof do
begin
AOrder:=AQuery.FieldByName('I500B_011').AsString;
ALine:=AQuery.FieldByName('I500B_012').AsInteger;
//相关单据(0=手工输入，1=采购定单，2=销售定单，3=车间定单，4=出入仓单)
AType:=AQuery.FieldByName('I550_005').AsInteger;
//取得来源单据数量
AOrdQty:=0;
case AType of
0:Exit;
1:AOrdQty:=GetValue('select P510B_005 from PUR510B where P510B_001='+''''+AOrder+''''+' and P510B_002='+IntToStr(ALine));
2:AOrdQty:=GetValue('select O510B_005 from ORD510B where O510B_001='+''''+AOrder+''''+' and O510B_002='+IntToStr(ALine));
3:Exit;
4:AOrdQty:=GetValue('select I500B_005 from INV500B where I500B_001='+''''+AOrder+''''+' and I500B_002='+IntToStr(ALine));
end;
BQuery.Close;
BQuery.SQL.Clear;
BQuery.SQL.Add('select * from INV500A,INV500B where I500A_001=I500B_001 and I500B_011='+''''+AOrder+''''+' and I500B_012='+IntToStr(ALine)+' order by I500A_004,I500A_001');
BQuery.Open;
while not BQuery.Eof do
begin
BQuery.Edit;
BQuery.FieldByName('I500B_013').Value:=AOrdQty;
BQuery.Post;
AOrdQty:=AOrdQty-BQuery.FieldByName('I500B_005').Value;
BQuery.Next;
end;
AQuery.Next;
end;
AQuery.Close;
AQuery.Free;
BQuery.Close;
BQuery.Free;
end;
//更新盘点计划的“盘点完成”标识
//ACode: 盘点汇报单号
procedure UpdateCountFlag(ACode:String;ABoolean:Boolean);
var
APlanID:String;
begin
APlanID:=GetValue('select I610A_002 from INV610A where I610A_001='+''''+ACode+'''');
SYSDM.qryQuery.Close;
SYSDM.qryQuery.SQL.Clear;
SYSDM.qryQuery.SQL.Add('update INV600B set I600B_007='+GetBoolean(ABoolean));
SYSDM.qryQuery.SQL.Add('where I600B_001='+''''+APlanID+''''+' and ');
SYSDM.qryQuery.SQL.Add(' exists(select 1 from INV610B where I610B_002=I600B_002 and I610B_001='+''''+ACode+''''+')');
SYSDM.qryQuery.ExecSQL;
end;
//取得料品的期初库存、单价、金额
procedure GetItemOpenValue(AWhsNo,ALocNo,AItemNo:Integer;ADate:TDateTime;var ABanlance,APrice,AAmount:Double);
begin
if ((AWhsNo=0) and (ALocNo=0)) or (AItemNo=0) then
begin
ShowMsg('UMS10000622'); //查询的仓库或料品编号不能为空
Abort;
end;
if (ALocNo=0) and (IsExists('select 1 from INV110 where I110_002='+IntToStr(AWhsNo))) then
begin
ShowMsg('UMS10000623'); //查询的仓库中存在相关的库位，库位编号不能为空
Abort;
end;
if ADate=0 then
begin
ShowMsg('UMS10000624'); //查询的日期不能为空
Abort;
end;
SYSDM.qryQuery.Close;
SYSDM.qryQuery.SQL.Clear;
SYSDM.qryQuery.SQL.Add('select top 1 I500_014,I500_015,I500_016');
SYSDM.qryQuery.SQL.Add('from INV500');
SYSDM.qryQuery.SQL.Add('where I500_005<='+GetDateString(ADate));
if AWhsNo<>0 then SYSDM.qryQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo)); //仓库
if ALocNo<>0 then SYSDM.qryQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo)); //库位
SYSDM.qryQuery.SQL.Add(' and I500_009='+IntToStr(AItemNo)); //料品
SYSDM.qryQuery.SQL.Add('order by I500_005 desc,I500_017 desc,I500_002 desc');
SYSDM.qryQuery.Open;
if not SYSDM.qryQuery.IsEmpty then
begin
ABanlance:=SYSDM.qryQuery.FieldByName('I500_014').AsFloat;
APrice:=SYSDM.qryQuery.FieldByName('I500_015').AsFloat;
AAmount:=SYSDM.qryQuery.FieldByName('I500_016').AsFloat;
end else
begin
//取得期初建帐的余额
SYSDM.qryQuery.Close;
SYSDM.qryQuery.SQL.Clear;
SYSDM.qryQuery.SQL.Add('select top 1 I500_011,I500_012,I500_013');
SYSDM.qryQuery.SQL.Add('from INV500');
SYSDM.qryQuery.SQL.Add('where I500_004=0');
if AWhsNo<>0 then SYSDM.qryQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo)); //仓库
if ALocNo<>0 then SYSDM.qryQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo)); //库位
SYSDM.qryQuery.SQL.Add(' and I500_009='+IntToStr(AItemNo)); //料品
SYSDM.qryQuery.SQL.Add('order by I500_005 desc,I500_017 desc,I500_002 desc');
SYSDM.qryQuery.Open;
ABanlance:=SYSDM.qryQuery.FieldByName('I500_011').AsFloat;
APrice:=SYSDM.qryQuery.FieldByName('I500_012').AsFloat;
AAmount:=SYSDM.qryQuery.FieldByName('I500_013').AsFloat;
end;
end;
//取得所有物料的出入数量及库存数量
procedure GetAllItemInvBalance(ADOQuery:TADOQuery;AWhsNo,ALocNo:Integer;AFromDate,AToDate:TDateTime);
begin
//期末余额
//先按日期、单据序号、行号分组，找出最接近查询日期的最后一条记录的库存余额
//期初余额=期末余额+出库数量-入库数量
try
Screen.Cursor:=crHourGlass;
ADOQuery.DisableControls;
ADOQuery.Close;
ADOQuery.SQL.Clear;
case ADatabase of //0=Access, 1=SQL Server
0:ADOQuery.SQL.Add('select I150_001,I150_002,I150_003,I150_004,I140_003,FBalance,iif(FInQty,FInQty,0) as FIn,iif(FOutQty,FOutQty,0) as FOut,FBalance+iif(FOutQty,FOutQty,0)-iif(FOutQty,FInQty,0) as FOpen from ');
1:ADOQuery.SQL.Add('select I150_001,I150_002,I150_003,I150_004,I140_003,FBalance,isnull(FInQty,0) as FIn,isnull(FOutQty,0) as FOut,FBalance+isnull(FOutQty,0)-isnull(FInQty,0) as FOpen from ');
end;
ADOQuery.SQL.Add('((');
ADOQuery.SQL.Add(' select E.I150_001,E.I150_002,E.I150_003,E.I150_004,F.I140_003,A.I500_014 as FBalance ');
ADOQuery.SQL.Add(' from INV500 A,');
ADOQuery.SQL.Add(' (');
ADOQuery.SQL.Add(' select max(B.I500_002) as FLine,B.I500_007,B.I500_008,B.I500_009,B.I500_010,B.I500_001,B.I500_004 from INV500 B,');
ADOQuery.SQL.Add(' (');
ADOQuery.SQL.Add(' select max(I500_005) as FDate,max(I500_017) as FSeqNo,I500_007,I500_008,I500_009,I500_010');
ADOQuery.SQL.Add(' from INV500');
ADOQuery.SQL.Add(' where I500_005<='+GetDateString(AToDate));
if AWhsNo<>0 then ADOQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo));
if ALocNo<>0 then ADOQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo));
ADOQuery.SQL.Add(' group by I500_007,I500_008,I500_009,I500_010');
ADOQuery.SQL.Add(' ) C');
ADOQuery.SQL.Add(' where B.I500_005=C.FDate and B.I500_017=C.FSeqNo and');
ADOQuery.SQL.Add(' B.I500_007=C.I500_007 and B.I500_008=C.I500_008 and B.I500_009=C.I500_009 and B.I500_010=C.I500_010');
ADOQuery.SQL.Add(' group by B.I500_007,B.I500_008,B.I500_009,B.I500_010,B.I500_001,B.I500_004');
ADOQuery.SQL.Add(' ) D,');
ADOQuery.SQL.Add(' INV150 E,INV140 F');
ADOQuery.SQL.Add(' where A.I500_001=D.I500_001 and A.I500_002=D.FLine and A.I500_004=D.I500_004 and');
ADOQuery.SQL.Add(' A.I500_009=E.I150_001 and A.I500_010=F.I140_001 ');
ADOQuery.SQL.Add(') AA');
ADOQuery.SQL.Add('left join');
ADOQuery.SQL.Add(' (select I500_009,sum(I500_011) as FInQty from INV500 where I500_004 in (1,3) and I500_005>='+GetDateString(AFromDate)+' and I500_005<='+GetDateString(AToDate));
if AWhsNo<>0 then ADOQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo));
if ALocNo<>0 then ADOQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo));
ADOQuery.SQL.Add(' group by I500_009) BB on AA.I150_001=BB.I500_009)');
ADOQuery.SQL.Add('left join');
ADOQuery.SQL.Add(' (select I500_009,sum(I500_011) as FOutQty from INV500 where I500_004 in (2) and I500_005>='+GetDateString(AFromDate)+' and I500_005<='+GetDateString(AToDate));
if AWhsNo<>0 then ADOQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo));
if ALocNo<>0 then ADOQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo));
ADOQuery.SQL.Add(' group by I500_009) CC on AA.I150_001=CC.I500_009');
ADOQuery.SQL.Add('order by I150_002');
ADOQuery.Open;
finally
ADOQuery.EnableControls;
Screen.Cursor:=crDefault;
end;
end;
//取得仓库的料品的期初数量
procedure GetOpenData(ADataSet:TADODataSet;AWhsNo,ALocNo:Integer;AFromDate:TDateTime);
var
AItemNo:Integer;
ADOQuery:TADOQuery;
ABanlance,APrice,AAmount:Double;
begin
ADOQuery:=TADOQuery.Create(nil);
ADOQuery.Connection:=SYSDM.ADOC;
ADOQuery.CacheSize:=1000;
ADOQuery.Close;
ADOQuery.SQL.Clear;
ADOQuery.SQL.Add('select distinct I500_007,I500_008,I500_009,I150_002,I150_003,I150_004,I140_003');
ADOQuery.SQL.Add('from INV500,INV150,INV140');
ADOQuery.SQL.Add('where I500_009=I150_001 and I500_010=I140_001');
if AWhsNo<>0 then ADOQuery.SQL.Add(' and I500_007='+IntToStr(AWhsNo)); //仓库
if ALocNo<>0 then ADOQuery.SQL.Add(' and I500_008='+IntToStr(ALocNo)); //库位
ADOQuery.Open;
while not ADOQuery.Eof do
begin
AWhsNo:=ADOQuery.FieldByName('I500_007').AsInteger;
ALocNo:=ADOQuery.FieldByName('I500_008').AsInteger;
AItemNo:=ADOQuery.FieldByName('I500_009').AsInteger;
if ADataSet.Locate('FItemNo',AItemNo,[]) then ADataSet.Edit else ADataSet.Append;
ADataSet.FieldByName('FItemNo').Value:=AItemNo;
ADataSet.FieldByName('FItemID').Value:=ADOQuery.FieldByName('I150_002').AsString;
ADataSet.FieldByName('FItemName').Value:=ADOQuery.FieldByName('I150_003').AsString;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -