WEB开发网
开发学院软件开发Delphi GetPartFilter+SumStocklist 阅读

GetPartFilter+SumStocklist

 2006-02-04 14:01:23 来源:WEB开发网   
核心提示: sql.text:= 'insert into Rep仓库收付存月结报表(MC,ycjcje,bqsrje,bqllje,qmjcje) '+ ' (select sorttype,sum(qc*inPRice),sum((rk-rkt)*inprice),sum((ck-ckt)*inp
 

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

}

Tags:GetPartFilter SumStocklist

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接