/*************************************************************************/ /** **/ /** MODULE: Create Report AUTHOR: Tim Sabin **/ /** DESCRIPTION: In Part II, we fetch data from the database and create **/ /** a "flat file". **/ /** DATE CREATED: 04/08/1997 LAST UPDATED: 07/18/1997 **/ /** **/ /*************************************************************************/ #include "sybfront.h" #include "sybdb.h" #include "syberror.h" #includechar months [][4]={"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}; char *printfComma (int size, int intToConvert) { static char buffer [20]; int tempi, i, j; memset (buffer, ' ', 19); buffer [size] = '\0'; for (tempi = intToConvert, i = size - 1, j = 0; tempi; tempi = tempi / 10, i--, j++) { if (i < 0) break; /* Field not wide enough */ if (j == 3) { if (i < 0) break; buffer [i] = ','; i--; j = 0; } buffer [i] = (tempi % 10) + '0'; } return buffer; } int main (int argc, char **argv) { int i, status, outstand_shares, line_seq, runTraded, runHeld, inDex; int change_in_shares, shares_held, oddness; char *sybServ, *userID; float closingPrice; FILE *PASSFILE; char password [20], SQLcmd [500], filer_name [61], issue_desc [21], ticker [7]; char issuer_cusip [7], issue_cusip [3], report_month [3], report_year [3]; char qtr_date_yyyy [5], mgr_name [61], day [3]; char lastCusip [7], lastIssue [3], osc; LOGINREC *login, *login2; DBPROCESS *dbh, *dbh2; /** Fetch the Sybase server and user ID from the command line **/ for (i = 1; i < argc; i++) { if (!strcmp (argv [i], "-s")) sybServ = argv [++i]; else if (!strcmp (argv [i], "-u")) userID = argv [++i]; } if (!sybServ || !userID) { fprintf (stderr, "Need sybase server and userID, exiting\n"); exit (-1); } /** Login to the Sybase server for Section 00 **/ PASSFILE = fopen ("maint_file", "r"); if (!PASSFILE) { fprintf (stderr, "Cannot access maint_file, exiting\n"); exit (-2); } fscanf (PASSFILE, "%s", password); fclose (PASSFILE); if (dbinit () == FAIL) { fprintf (stderr, "Could not initialize Sybase interface library\n"); exit (-4); } login = dblogin (); DBSETLUSER (login, userID); DBSETLPWD (login, password); dbh = dbopen (login, sybServ); if (!dbh) { fprintf (stderr, "Could not login to Sybase server %s, user ID %s\n", sybServ, userID); exit (-4); } dbuse (dbh, "sec13f"); /** Login to the Sybase server for Section 03 **/ login2 = dblogin (); DBSETLUSER (login2, userID); DBSETLPWD (login2, password); dbh2 = dbopen (login2, sybServ); if (!dbh2) { fprintf (stderr, "Could not 2nd login to Sybase server %s, user ID %s\n", sybServ, userID); exit (-4); } dbuse (dbh2, "sec13f"); /*************************************************************************/ /** **/ /** FUNCTION: Fetch QCed Data (Part II) AUTHOR: Tim Sabin **/ /** DESCRIPTION: This "function" creates a "data list" for the rest **/ /** of the program to access. **/ /** DATE CREATED: 04/08/1997 LAST UPDATED: 07/11/1997 **/ /** **/ /*************************************************************************/ /** For the Company Header (section "00"): **/ /** Perform an SQL SELECT, getting header information into a temp table. **/ strcpy (SQLcmd, "SELECT issuer_name, issue_desc, ticker, " "issuer_company_cusip, issuer_cusip_suffix, qtr_date_mm, qtr_date_dd, " "qtr_date_yyyy INTO #SECT00TMP1 FROM inst_inv_manager_holding " "GROUP BY qtr_date_yyyy, qtr_date_mm, issuer_company_cusip, " "issuer_cusip_suffix " "HAVING max (convert (integer, qtr_date_yyyy + qtr_date_mm)) = " "convert (integer, qtr_date_yyyy + qtr_date_mm)"); dbcmd (dbh, SQLcmd); status = dbsqlexec (dbh); if (status == FAIL) { fprintf (stderr, "Error: cannot get Section 00 (header) data from " "Sybase\n"); exit (-5); } /** For the Institutional Holdings (section "03"): **/ /** Perform an SQL SELECT, getting detail info into a temp table. **/ sprintf (SQLcmd, "SELECT issuer_company_cusip, issuer_cusip_suffix, " "qtr_date_yyyy, qtr_date_mm, qtr_date_dd, inst_inv_mgr_name, " "change_in_shares, aggregate_shares_held " "INTO #SECT03TMP1 " "FROM inst_inv_manager_holding " "HAVING max (convert (integer, qtr_date_yyyy + qtr_date_mm)) = " "convert (integer, qtr_date_yyyy + qtr_date_mm)"); dbcmd (dbh2, SQLcmd); status = dbsqlexec (dbh2); if (status == FAIL) { fprintf (stderr, "Error: cannot get Section 03 (inst.) data from " "Sybase\n"); exit (-5); } /*************************************************************************/ /** **/ /** FUNCTION: Create Flat File AUTHOR: Tim Sabin **/ /** DESCRIPTION: This "function" takes the temp files already created **/ /** and creates a "flat" file a la the CDA input tape. **/ /** DATE CREATED: 04/14/1997 LAST UPDATED: 07/18/1997 **/ /** **/ /*************************************************************************/ /** DO FOR each company in the database **/ strcpy (SQLcmd, "SELECT issuer_name, issue_desc, ticker, " "issuer_company_cusip, issuer_cusip_suffix, " "qtr_date_mm, qtr_date_dd, qtr_date_yyyy FROM #SECT00TMP1 " "GROUP BY issuer_company_cusip, issuer_cusip_suffix, qtr_date_yyyy, " "qtr_date_mm " "ORDER BY issuer_company_cusip, issuer_cusip_suffix "); dbcmd (dbh, SQLcmd); dbsqlexec (dbh); if (dbresults (dbh) != SUCCEED) exit (-5); dbbind (dbh, 1, STRINGBIND, sizeof (filer_name), filer_name); dbbind (dbh, 2, STRINGBIND, sizeof (issue_desc), issue_desc); dbbind (dbh, 3, STRINGBIND, sizeof (ticker), ticker); dbbind (dbh, 4, STRINGBIND, sizeof (issuer_cusip), issuer_cusip); dbbind (dbh, 5, STRINGBIND, sizeof (issue_cusip), issue_cusip); dbbind (dbh, 6, STRINGBIND, sizeof (report_month), report_month); dbbind (dbh, 7, STRINGBIND, sizeof (day), day); dbbind (dbh, 8, STRINGBIND, sizeof (qtr_date_yyyy), qtr_date_yyyy); for (lastCusip [0] = lastIssue [0] = '\0';dbnextrow(dbh) != NO_MORE_ROWS;) { if (!strcmp(lastCusip, issuer_cusip) && !strcmp(lastIssue, issue_cusip)) continue; /* So report doesn't get multiple records */ strcpy (lastCusip, issuer_cusip); strcpy (lastIssue, issue_cusip); report_year [0] = qtr_date_yyyy [2]; /* Faster than strcpy */ report_year [1] = qtr_date_yyyy [3]; report_year [2] = '\0'; /** Merge data and Output "Flat" File: **/ /** o Reset line sequence to 1 **/ line_seq = 1; /** o Output lines for: Company Name, Stock Class, Ticker, CUSIP, **/ /** Number Outstanding Shares, Quarter End Date, Quarter Closing Price **/ /** incrementing line sequence for each field **/ /* Company Name line */ printf ("00CO %s " " %s%s%04d\n", filer_name, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* Stock Class line */ printf ("00CL %s " " " " %s%s%04d\n", issue_desc, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* Ticker line */ printf ("00TS %s " " " " %s%s%04d\n", ticker, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* CUSIP line */ printf ("00CU %s%s " " " " %s%s%04d\n", issuer_cusip, issue_cusip, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* Number Outstanding Shares line */ sprintf (SQLcmd, "SELECT closing_price, outstanding_shares " "FROM iverson..security_pricing a, iverson..security b " "WHERE a.security_id = b.security_id " "AND period_type_code = \"M\" " "AND period_end_date = \"%s %s, %s\" " "AND company_cusip = \"%s\" " "AND cusip_suffix = \"%s\"", months [atoi(report_month)-1], day, qtr_date_yyyy, issuer_cusip, issue_cusip); dbcmd (dbh2, SQLcmd); dbsqlexec (dbh2); if (dbresults (dbh2) != SUCCEED) { break; } dbbind (dbh2, 1, REALBIND, sizeof (closingPrice), (DBCHAR *)&closingPrice); dbbind (dbh2, 2, INTBIND, sizeof (outstand_shares), (DBCHAR *)&outstand_shares); if (dbnextrow (dbh2) == NO_MORE_ROWS) { closingPrice = 0.00; outstand_shares = 0; } else { dbcancel (dbh2); } if (outstand_shares / 1000000) osc = (outstand_shares / 1000000) + '0'; else osc = ' '; printf ("00OS%c%s " " " " %s%s%04d\n", osc, printfComma(7, outstand_shares), issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* Quarter End Date line */ printf ("00OD %s/%s/%s " " " " %s%s%04d\n", report_month, day, report_year, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /* Quarter Closing Price line */ printf ("00PR %7.2f " " " " %s%s%04d\n", closingPrice, issuer_cusip, issue_cusip, line_seq); line_seq += 1; /** DO FOR each institutional holder in the company **/ runTraded = 0; runHeld = 0; inDex = 0; sprintf (SQLcmd, "SELECT inst_inv_mgr_name, change_in_shares, " "aggregate_shares_held FROM #SECT03TMP1 " "WHERE issuer_company_cusip = \"%s\" " "AND issuer_cusip_suffix = \"%s\"", issuer_cusip, issue_cusip); dbcmd (dbh2, SQLcmd); dbsqlexec (dbh2); if (dbresults (dbh2) != SUCCEED) { break; } dbbind (dbh2, 1, STRINGBIND, sizeof (mgr_name), mgr_name); dbbind (dbh2, 2, INTBIND, sizeof (change_in_shares), (BYTE *)&change_in_shares); dbbind (dbh2, 3, INTBIND, sizeof (shares_held), (BYTE *)&shares_held); while (dbnextrow(dbh2) != NO_MORE_ROWS) { /** Merge data and output: make sure latest line is complete, and **/ /** output a totals line **/ /* Make initializations */ oddness = inDex % 2; /** o IF the index is 0 THEN **/ if (inDex == 0) { /** o Output line for "As Of Date-3" **/ printf ("03OF %s/%s/%s " " " " " "%s%s%04d\n", report_month, day, report_year, issuer_cusip, issue_cusip, line_seq); /** o Increment the sequence **/ line_seq += 1; /** o ENDIF **/ } /** o IF the index is even THEN **/ if (oddness == 0) { /** o Output "03" **/ printf ("03"); /** o ENDIF **/ } /** o Output incomplete line including: Insider Name, Relation Code, **/ /** o #Shares Traded, #Shares Held, Effective Date **/ mgr_name [28] = '\0'; printf ("%28s %s %s %s/%s/%s", mgr_name, printfComma (11, change_in_shares), printfComma (11, shares_held), report_month, day, report_year); /** o Add #Shares Traded and #Shares Held to running totals **/ runTraded += change_in_shares; runHeld += shares_held; /** o IF the index is odd THEN **/ if (oddness == 1) { /** o Output complete line including Sequence **/ printf (" %s%s%04d\n", issuer_cusip, issue_cusip, line_seq); /** o Increment Line Sequence **/ line_seq += 1; /** o ELSE **/ } else { /** o Add spacing between the 2 entries per line **/ printf (" "); /** o ENDIF **/ } /** o Increment index **/ inDex += 1; /** ENDDO **/ } /** o IF we are in the middle of a line THEN **/ oddness = inDex % 2; if (oddness == 1) { /** o Output line including sequence **/ printf (" " " %s%s%04d\n", issuer_cusip, issue_cusip, line_seq); /** o Increment Line Sequence **/ line_seq += 1; /** o ENDIF **/ } /** o Output totals line using running totals **/ if (inDex > 999) { inDex = 999; } printf ("03 " " TOTAL OF %3d OWNERS %s %s " " %s%s%04d\n", inDex, printfComma (11, runTraded), printfComma (11, runHeld), issuer_cusip, issue_cusip, line_seq); /** o Increment line sequence **/ line_seq += 1; /** ENDDO **/ } fprintf (stderr, "Finished creating mainframe file\n"); /** **/ }