sql.text:= 'insert into Rep倉庫收付存月結報表(MC,ycjcje,bqsrje,bqllje,qmjcje) '+
' (select sorttype,sum(qc*inprice),sum((rk-rkt)*inprice),sum((ck-ckt)*inprice),sum(jc*inprice) '+
' from tmpkk2 where 1=1 '+sdepot+stmp +' group by sorttype )' ;
ExecSQL;
function GetPartFilter(AID: string): string;
var
querytemp: TOceanQuery;
s: string;
lOwnerids: TStringList;
procedure GetOwnerids(AID: string);
var lQuery: TOceanQuery;
begin
lQuery := TOceanQuery.Create(nil);
try
lQuery.Close;
lQuery.SQL.Text := 'select ID from PartInfo where Ownerid=' + QuotedStr(AID) + ' and ID<>' + QuotedStr(AID) + ' and ImageIndex=0';
lQuery.Open;
while not lQuery.Eof do
begin
lOwnerids.Add(lQuery.FIEldByName('ID').AsString);
GetOwnerids(lQuery.FIEldByName('ID').AsString);
lQuery.Next;
end;
finally
lQuery.Free;
end;
end;
var I: Integer;
begin
Result := '';
querytemp := TOceanQuery.Create(nil);
lOwnerids := TStringList.Create;
try
querytemp.Close;
querytemp.SQL.Text := 'select ID,ImageIndex from partinfo where ID=''' + AID + '''';
querytemp.Open;
if querytemp.IsEmpty then Exit;
if querytemp.FIEldByName('ImageIndex').AsInteger <> 0 then
begin
Result := 'ID=' + QuotedStr(AID);
Exit;
end;
lOwnerids.Add(querytemp.FIEldByName('ID').AsString);
GetOwnerids(querytemp.FIEldByName('ID').AsString);
for I := 0 to lOwnerids.Count - 1 do
begin
if Result = '' then
Result := 'Ownerid in (' + QuotedStr(lOwnerids[I])
else
Result := Result + ',' + QuotedStr(lOwnerids[I]);
end;
if Result <> '' then
Result := Result + ')';
finally
querytemp.Free;
lOwnerids.Free;
end;
end;
function sumStocklist(Aid: string; Aimaindex: integer; Adepotcode: string; time1, time2: Tdate; llist: Tstringlist): Tstringlist;
var
s, sdate, sdepot: string;
myOceanQry: TOceanQuery;
stmp: string;
qichu1,qichu2:string;
sumJC, sumJCsj, sumQC, sumQCsj, sumRK, sumRKsj, sumLY, sumLYsj, sumLT, sUMLTsj, sumRT, sumRTsj: integer;
begin
myOceanQry := TOceanQuery.Create(nil);
try
if Aimaindex = 1 then
stmp := ' and partid=' + QuotedStr(AId)
else
stmp := ' and ' + GetPartFilter(Aid);
with myOceanQry do
begin
Close;
sql.text:= ' if exists (select * from sysobjects where id = object_id(N''[tmpkk2]'') and '+
' OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [tmpkk2] ' ;
ExecSQL;
Close;
sql.text:='exec sp_stockSFC_depot_price '+QuotedStr(datetostr(time1)) +','+QuotedStr(datetostr(time2));
ExecSQL;
Close;
sql.text:=' select sum(qc) as qc,sum(rk) as rk ,sum(ck) as ck ,'+
' sum(rkt) as rkt ,sum(ckt) as ckt ,sum(jc) as jc '+
' from tmpkk2 where indepotcode='+ QuotedStr(Adepotcode)+stmp ;
open;
end;
llist.Add(inttostr(myOceanQry.fIEldbyname('rk').Asinteger)); //strings[0] //入庫票據
llist.Add(inttostr(myOceanQry.fIEldbyname('rk').Asinteger)); //strings[1] //入庫實際
llist.Add(inttostr(myOceanQry.fIEldbyname('ck').Asinteger)); //strings[2] //領用票據
llist.Add(inttostr(myOceanQry.fIEldbyname('ck').Asinteger)); //strings[3] //領用實際
llist.Add(inttostr(myOceanQry.fIEldbyname('rkt').Asinteger)); //strings[4] //入庫退貨票據
llist.Add(inttostr(myOceanQry.fIEldbyname('rkt').Asinteger)); //strings[5] //入庫退貨實際
llist.Add(inttostr(myOceanQry.fIEldbyname('ckt').Asinteger)); //strings[6] //領用退貨票據
llist.Add(inttostr(myOceanQry.fIEldbyname('ckt').Asinteger)); //strings[7] //領用退貨實際
llist.Add(inttostr(myOceanQry.fIEldbyname('jc').Asinteger)); //strings[8] //結存票據
llist.Add(inttostr(myOceanQry.fIEldbyname('jc').Asinteger)); //strings[9] //結存實際
llist.Add(inttostr(myOceanQry.fIEldbyname('qc').Asinteger)); //strings[10] //期初票據
llist.Add(inttostr(myOceanQry.fIEldbyname('qc').Asinteger)); //strings[11] //期初實際
Result := llist;
finally
myOceanQry.Free;
end;
end;
{
CREATE PROCEDURE sp_StockSFC_depot_price
@date1 datetime,
@date2 datetime
AS
select * into tmpkk2 from
(
SELECT ISNULL(a.Expr1, 0) AS xh, ISNULL(b.Expr1, 0) AS jc, b.Expr1 - a.Expr1 AS qc,
b.PARTNAME,b.partid,ownerid=(select ownerid from partinfo where id=b.partid),
sorttype=(select sorttype from partinfo where id=b.partid),
a.rk,a.rkt,a.ck,a.ckt,b.inprice,b.outprice,b.indepotcode,b.outdepotcode
FROM (
SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
isnull(tmp1.sumquantity,0) as rk,isnull(tmp2.sumquantity,0) as ck,
isnull(tmp3.sumquantity,0) as rkt,isnull(tmp4.sumquantity,0) as ckt,
TMP.PARTNAME, tmp.partid,tmp.depotcode as indepotcode,tmp2.depotcode as outdepotcode
FROM (SELECT a.PARTID, a.PARTNAME,b.depotcode
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME,b.depotcode) tmp LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME,b.depotcode, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 AND @date2)
GROUP BY a.PARTID, a.PARTNAME,b.depotcode) tmp1 ON
tmp.partid = tmp1.partid and tmp.depotcode=tmp1.depotcode LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME,b.depotcode, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 AND @date2)
GROUP BY a.PARTID, a.PARTNAME,b.depotcode) tmp2 ON
tmp.PARTID = tmp2.PARTID and tmp.depotcode=tmp2.depotcode LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid,b.depotcode
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate BETWEEN @date1 AND @date2
GROUP BY a.partid,b.depotcode) tmp3 ON
tmp.PARTID = tmp3.PARTID and tmp.depotcode=tmp3.depotcode LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid,b.depotcode
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate BETWEEN @date1 AND @date2
GROUP BY a.partid,b.depotcode) tmp4 ON
tmp.PARTID = tmp4.PARTID and tmp.depotcode=tmp4.depotcode)
a RIGHT OUTER JOIN
(SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
TMP1.PARTNAME, tmp1.partid,tmp1.price as inprice,tmp2.price as outprice,
tmp1.depotcode as indepotcode,tmp2.depotcode as outdepotcode
FROM (
SELECT a.PARTID, a.PARTNAME,a.price,b.depotcode, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME,a.price,b.depotcode)
tmp1 LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME,a.price,b.depotcode, SUM(a.QUANTITY)
AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME,a.price,b.depotcode)
tmp2 ON
tmp1.PARTID = tmp2.PARTID and tmp1.depotcode=tmp2.depotcode LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid,b.depotcode
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate <= @date2
GROUP BY a.partid,b.depotcode) tmp3 ON
tmp1.PARTID = tmp3.PARTID and tmp1.depotcode=tmp3.depotcode LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid,b.depotcode
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate <= @date2
GROUP BY a.partid,b.depotcode) tmp4 ON tmp1.PARTID = tmp4.PARTID and tmp1.depotcode=tmp4.depotcode) b ON
a.partid = b.partid and a.indepotcode=b.indepotcode
) tqQQ
GO
}
