sql.text:= 'insert into Rep倉庫收付存月結報表(MC,ycjcje,bqsrje,bqllje,qmjcje)  '+
      '   (select sorttype,sum(qc*in                '   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

}