/* ==================================================================== * * (c) Rafael Hernandez rhm@stanford.edu 2001 * * ==================================================================== */ /* Include files */ #include #include #include #include #include /* SQL Communications Area */ #include "cgi.h" #include "orautils.h" #define PAGE15 15 #define MaX(a,b) ((a) > (b) ? (a) : (b)) /* Function prototypes */ int ShowCustStatistics(int pg); /* * NAME * main * DESCRIPTION * Gather statistics from customers ordered by how much * have they spent. How many times have they purchased * photos, what type of photos do they purchase * We present the data in pages of 15 rows */ int main() { char * uid = "/"; int result; BindingSet *bs; char *page; int pg; SetEnvs(); Initialize(); PrintHTMLHeader(); bs = GetValuesFor("page"); if (bs) { page = GetNextValue(bs); if (!page) page = "1"; } else page = "1"; EXEC SQL WHENEVER SQLERROR DO PrintErr("Error: "); EXEC SQL CONNECT :uid; pg = atoi(page); result = ShowCustStatistics(pg); if (result) { printf("
", homepagecgi); printf("", pg + 1); printf(""); printf(""); printf("
"); } else { printf("
"); printf("

*** NO MORE DATA ***"); printf("

"); printf("

"); } PrintHTMLEnd(); EXEC SQL ROLLBACK RELEASE; return(0); } /* * NAME * ShowCustStatistics * DESCRIPTION * Gather statistics from customers ordered by how much * have they spent. How many times have they purchased * photos, what type of photos do they purchase */ int ShowCustStatistics(int pg) { int done = 0, i, j = 0, numinpage, page, oldpage; char choice[30]; char name[PAGE15][256], cc_type[PAGE15][256]; long int num_trans[PAGE15], total[PAGE15], nlandscapes[PAGE15], nportraits[PAGE15], nabstracts[PAGE15]; short iname[PAGE15], icc_type[PAGE15], inum_trans[PAGE15], itotal[PAGE15], inlandscapes[PAGE15], inportraits[PAGE15], inabstracts[PAGE15]; page = pg * PAGE15; oldpage = page - PAGE15; /* Update transaction amounts */ EXEC SQL UPDATE Transaction T SET amount = ( SELECT SUM(P.price) FROM Photo P, Includes I WHERE I.idn = T.idn AND I.catalogn = P.catalogn); EXEC SQL COMMIT; EXEC SQL VAR name IS STRING(256); EXEC SQL VAR cc_type IS STRING(256); EXEC SQL DECLARE c3 CURSOR FOR /* For some strange reason the ProC compiler on Linux */ /* dies here */ SELECT name, cc_type, num_trans, total, nlandscapes, nportraits, nabstracts FROM ( SELECT ROWNUM AS rnum, name, cc_type, num_trans, total, nlandscapes, nportraits, nabstracts FROM ( SELECT C.name, T.cc_type, COUNT(T.idn) AS num_trans, SUM(T.amount) AS total, SUM(nlandscapes) AS nlandscapes, SUM(nportraits) AS nportraits, SUM(nabstracts) AS nabstracts FROM Customer C, Transaction T, Buys B, ( (SELECT DISTINCT I3.idn AS idn, nportraits, nlandscapes, nabstracts FROM Includes I3, ( (SELECT DISTINCT I2.idn AS idn, nportraits, nlandscapes FROM Includes I2, (SELECT idn, COUNT(P.catalogn) AS nportraits FROM Includes I, Portrait P WHERE P.catalogn = I.catalogn GROUP BY idn) PT, (SELECT idn, COUNT(L.catalogn) AS nlandscapes FROM Includes I, Landscape L WHERE L.catalogn = I.catalogn GROUP BY idn) LN WHERE LN.idn = PT.idn (+) AND ((I2.idn = LN.idn) OR (I2.idn = PT.idn)) ) UNION (SELECT DISTINCT I2.idn AS idn, nportraits, nlandscapes FROM Includes I2, (SELECT idn, COUNT(P.catalogn) AS nportraits FROM Includes I, Portrait P WHERE P.catalogn = I.catalogn GROUP BY idn) PT, (SELECT idn, COUNT(L.catalogn) AS nlandscapes FROM Includes I, Landscape L WHERE L.catalogn = I.catalogn GROUP BY idn) LN WHERE LN.idn (+) = PT.idn AND ((I2.idn = LN.idn) OR (I2.idn = PT.idn)) ) ) PL, (SELECT idn, COUNT(A.catalogn) AS nabstracts FROM Includes I, Abstract A WHERE A.catalogn = I.catalogn GROUP BY idn) AB WHERE PL.idn = AB.idn (+) AND ((I3.idn = PL.idn) OR (I3.idn = AB.idn)) ) UNION (SELECT DISTINCT I3.idn AS idn, nportraits, nlandscapes, nabstracts FROM Includes I3, ( (SELECT DISTINCT I2.idn AS idn, nportraits, nlandscapes FROM Includes I2, (SELECT idn, COUNT(P.catalogn) AS nportraits FROM Includes I, Portrait P WHERE P.catalogn = I.catalogn GROUP BY idn) PT, (SELECT idn, COUNT(L.catalogn) AS nlandscapes FROM Includes I, Landscape L WHERE L.catalogn = I.catalogn GROUP BY idn) LN WHERE LN.idn = PT.idn (+) AND ((I2.idn = LN.idn) OR (I2.idn = PT.idn)) ) UNION (SELECT DISTINCT I2.idn AS idn, nportraits, nlandscapes FROM Includes I2, (SELECT idn, COUNT(P.catalogn) AS nportraits FROM Includes I, Portrait P WHERE P.catalogn = I.catalogn GROUP BY idn) PT, (SELECT idn, COUNT(L.catalogn) AS nlandscapes FROM Includes I, Landscape L WHERE L.catalogn = I.catalogn GROUP BY idn) LN WHERE LN.idn (+) = PT.idn AND ((I2.idn = LN.idn) OR (I2.idn = PT.idn)) ) ) PL, (SELECT idn, COUNT(A.catalogn) AS nabstracts FROM Includes I, Abstract A WHERE A.catalogn = I.catalogn GROUP BY idn) AB WHERE PL.idn (+) = AB.idn AND ((I3.idn = PL.idn) OR (I3.idn = AB.idn)) ) ) PLA WHERE C.idname = B.idname AND B.idn = T.idn AND PLA.idn = T.idn GROUP BY C.name, T.cc_type ORDER BY name, total DESC, cc_type, num_trans DESC ) WHERE ROWNUM <= :page) WHERE rnum > :oldpage; EXEC SQL OPEN c3; EXEC SQL WHENEVER SQLERROR DO PrintErr("Error: "); EXEC SQL FETCH c3 INTO :name:iname, :cc_type:icc_type, :num_trans:inum_trans, :total:itotal, :nlandscapes:inlandscapes, :nportraits:inportraits, :nabstracts:inabstracts; while (!done) { printf("\n"); printf("" "" "\n"); numinpage = sqlca.sqlerrd[2] - j * PAGE15; for (i = 0; i < numinpage; ++i) { printf("\n", i % 2 == 0 ? "Aqua" : "Gray", S(name[i], iname[i]), S(cc_type[i], icc_type[i]), I(num_trans[i], inum_trans[i]), I(total[i], itotal[i]), I(nlandscapes[i], inlandscapes[i]), I(nportraits[i], inportraits[i]), I(nabstracts[i], inabstracts[i])); } printf("
NAMECARD TYPENUM TRANSACTIONSTOTAL AMOUNTLANDSCAPESPORTRAITSABSTRACTS
%s%s%ld%ld%ld%ld%ld
\n"); if (numinpage == PAGE15) { done = 1; } else break; } EXEC SQL CLOSE c3; return done; }