GetPartFilter+SumStocklist

【 tulaoshi.com - 编程语言 】

　　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
　　
　　}