SQL.TEXT: = 'INSERT INTO REP Warehouse Collection Monthly Reportable (MC, YCJCJE, BQSRJE, BQLLJE, QMJCJE)' '(Select SortType, Sum (QC * Inprice), SUM ((RK-RKT) * INPRICE ), SUM ((CK-CKT) * Inprice, SUM (JC * Inprice) ' ' from TMPOK2 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 =' quotedstr (AID) 'and ID <> quotedstr (aid) ' and imageindex = 0 '; lquery.open; while not lquery.eof do beg, limited (LQuery.fieldByname ('ID'). asstring; getownerids ('ID'). asstring; lquery.next; end; finally lquery.free; end; end; var i: integer; begin Result: = ''; Querytemp: = toceanQuery.create (nil); lownerids: = tstringlist.creat e; 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 dam: = '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' [TMPK2] ') and' ObjectProperty (ID, N''Susertable ') = 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 RK,' 'SUM (RKT) AS RKT, SUM (CKT) AS CKT, SUM (JC ) AS JC ' ' from TMPK2 WHERE INDEPOTCODE = ' quotedstr (adepotcode) stmp; open;
END;
Llist.add (INTTOSTR (Myoceanqry.fieldbyName); // strings [0] // Take the library ticket llist.add (INTTOSTR (Myoceanqry.fieldbyName); / / strings [1] // Introduction actual llist.add (INTTOSTR (Myoceanqry.fieldByname); // strings [2] // Accommoding bill llist.add (INTTOSTR (Myoceanqry.fieldbyName) 'ck'). Asinteger); // strings [3] // Acquisition actual
Llist.add (INTTOSTR (Myoceanqry.fieldbyName); // Strings [4] // Introduction Return Detailed ticket llist.add (INTTOSTR (Myoceanqry.fieldByname); // Strings [5] // Introduction Return Real
Llist.add (INTTOSTR (Myoceanqry.fieldbyName); // Strings [6] // Acquired return ticket llist.add (INTTOSTR (MyoceanQry.fieldbyName); // strings [7] // Acquired return actual
Llist.add (INTTOSTR (Myoceanqry.fieldByname); // strings [8] // Save the ticket llist.add (INTTOSTR (Myoceanqry.fieldByname); // Strings [9] // Survival
Llist.add (INTTOSTR (Myoceanqry.fieldbyName); // strings [10] // Atttostr (Myoceanqry.fieldByname); // Strings [11] // At first
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.outDepotcodefrom
Select Isnull (Tmp1.Sumquantity, 0) - Isnull (Tmp2.Sumquantity, 0) - Isnull (Tmp3.Sumquantity, 0) ISNULL (Tmp4.Sumquantity, 0) AS EXPR1, ISNULL (TMP1.SUMQUANTINTIN, 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, AP ARTNAME, 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
