Constructing a membership history

The organisation of the database makes re-creating a branch report trivial, but construction of personal histories for individual members of the union involves following pointers through the database. The following program was written to do this; it is written in c, but makes extensive use of embedded SQL statements to extract the data; the particular form of embedded SQL is that used by INGRES, but translation to other dialects should be straightforward.


Program Listing:

/*******************************************************************/
/* lifeline: This program takes as its only command line parameter */
/* the ID of a member. It begins by finding and printing */
/* the first membership record for the member, to which */
/* it appends all linked benefit payments from the */
/* relevant report. It then uses values of lsbid and */
/* nobid to find the next linked membership record and */
/* similarly outputs this and its dependents. This */
/* process continues until no further linked records */
/* can be found. */
/* */
/* For now, the program ignores travelling benefit. */
/* */
/* (c) H.R.Southall. May 1995. */
/*******************************************************************/

#include <string.h>
#include <stdio.h>
#include <ctype.h>

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;

#define SUCCESS 1
#define FAILURE 0
#define BUFLEN 1024
#define STRLEN 60

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'members' table: */
struct mem_ {
int report;
char br_name[STRLEN];
int number;
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
char code[2];
char place[STRLEN];
char comment[512];
int record_id;
int lsb_id;
int lob_id;
int nsb_id;
int nob_id;
} mem;
short mem_ind[14];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'payments' table: */
struct pay_ {
int report;
char br_name[STRLEN];
char type[2];
int year;
int month;
int day;
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
int weeks;
int days;
int pounds;
int shillings;
float pence;
char comment[512];
} pay;
short pay_ind[15];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'funerals' table: */
struct fun_ {
int report;
char br_name[STRLEN];
int year;
int month;
int day;
char f_status[2];
char wname[STRLEN];
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
int age;
char cause[STRLEN];
char comment[512];
int pounds;
int shillings;
float pence;
} fun;
short fun_ind[16];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'reg_book' table: */
struct rbk_ {
int book;
char br_name[STRLEN];
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
int age;
char trade[STRLEN];
char m_status[2];
int year;
int month;
int day;
char comment[512];
} rbk;
short rbk_ind[12];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'ase_reg' table: */
struct arb_ {
char br_name[STRLEN];
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
int age_y;
int age_m;
char u_name[STRLEN];
char trade[STRLEN];
char m_status[2];
int year;
int month;
int day;
char comment[512];
} arb;
short arb_ind[13];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
/* Parameters needed for processing 'd_cert' table: */
struct dcert_ {
char cname[STRLEN];
char sname[STRLEN];
char suffix[STRLEN];
int day;
int month;
int year;
char reg_dist[STRLEN];
int age;
char occup[STRLEN];
char place[STRLEN];
char cause[140];
} dcert;
short dcert_ind[11];

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;
int member; /* ID of member to be followed */
int target; /* ID of next Mem_Rec */
char sel_buf[BUFLEN]; /* Prepared SELECT */
int err; /* Error status */
int test_val; /* Test only */

EXEC SQL END DECLARE SECTION;

int open_flag, first_flag, char_buff;

main(argc, argv)
int argc;
char *argv[];
{
int i, len;

/* Check that membership ID has been supplied */
if (argc != 2) {
printf("\n%s%s%s\n\n",
"Usage: ", argv[0], " ID of member");
exit(1);
}

/* Place membership ID into parameter */
member = atoi(argv[1]);

printf("\nTracing lifeline for member %d:\n\n", member);

open_flag = first_flag = 0;

Init_Db();

/* Set initial value of target to membership ID */
target = member;

/* Start of main loop; continues to execute until no new */
/* value of target can be found. */
while(target != 0)
{
/* Find and print out membership record for target */
GetMem(target);
/* Find and output linked payments data */
GetPay(target);
/* Find and output linked funerals data */
GetFun(target);
if (mem_ind[12] == 0) target = mem.nsb_id;
else if (mem_ind[13] == 0) target = mem.nob_id;
else target = 0;
}

End_Db();
exit(0);
}

Init_Db()
{
if (open_flag == 0) {
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
EXEC SQL CONNECT sem;
open_flag=1;
}
}

End_Db()
{
if (open_flag != 0) {
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;

}
}

/* Find and print out membership record for target */

GetMem(target)
{
EXEC SQL SELECT m.report, b.br_name, m.number,
m.cname, m.sname, m.suffix, m.code, m.place,
m.comment, m.record_id, m.lsb_id, m.lob_id,
m.nsb_id, m.nob_id
into :mem:mem_ind
from members m, branches b
where m.branch=b.branch and
m.record_id = :target;

if (mem_ind[5] == 0)
printf("%d: Member of %s (Name: %s %s %s)\n",
mem.report, mem.br_name,
mem.cname, mem.sname, mem.suffix);
else printf("%d: Member of %s (Name: %s %s)\n",
mem.report, mem.br_name,
mem.cname, mem.sname);

/* Output footnote information, if present: */
if (mem_ind[6] == 0) { GetFoot(); }
/* Output Reg.Book information, if possibly present: */
if (mem_ind[10] != 0) { GetRBook(target); }
/* Output ASE Reg.Book information, if possibly present: */
if (mem_ind[10] != 0) {
if(mem.report < 1852) {GetAseRB(target); }}
}

/* Format and output footnote information: */
GetFoot()
{
int f_type;

printf(" Footnote information: ");

f_type = mem.code[0];
switch (f_type) {
case 'A': if (mem_ind[7] == 0) {
printf("Went abroad to %s.\n", mem.place);
}
else {
printf("Went abroad.\n");
}
break;
case 'B': if (mem_ind[7] == 0) {
printf("Joined %s branch.\n", mem.place);
}
else {
printf("Joined another branch.\n");
}
break;
case 'C': if (mem_ind[7] == 0) {
printf("Drew clearance to %s.\n", mem.place);
}
else {
printf("Drew clearance.\n");
}
break;
case 'D': printf("Died.\n");
break;
case 'E': printf("Was excluded.\n");
break;
case 'L': printf("Left.\n");
break;
case 'M': if (mem_ind[7] == 0) {
printf("Member of %s branch.\n", mem.place);
}
else {
printf("Member of another branch.\n");
}
break;
case 'P': printf("Proposition.\n");
break;
case 'S': printf("Superannuated.\n");
break;
case 'T': if (mem_ind[7] == 0) {
printf("Drew trav. cert. and moved"
" to %s.\n", mem.place);
}
else {
printf("Drew travelling certificate.\n");
}
break;
case 'X': printf("Miscellaneous.\n");
break;
default:
printf("Code = %s", mem.code);
if (mem_ind[7] == 0) printf(" (Place: %s)", mem.place);
printf(".\n");
break;
}
}

GetPay(target)
{
int p_type;

EXEC SQL DECLARE c_1 cursor for
select p.report, b.br_name, p.type,
p.year, p.month, p.day,
p.cname, p.sname, p.suffix,
p.weeks, p.days,
p.pounds, p.shillings, p.pence, p.comment
from payments p, branches b
where b.branch = p.branch and
p.mem_rec = :target
order by p.year, p.month, p.day;

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO closec_1;
EXEC SQL OPEN c_1;

/* Check value of sqlcode to see if there are still rows to process */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_1 INTO :pay:pay_ind;

p_type = pay.type[0];
switch (p_type) {
case 'M': printf(" Misc. payment at %s of Pnds%d/%d/%.1f"
" on %d/%d/%d:\n", pay.br_name,
pay.pounds, pay.shillings, pay.pence,
pay.day, pay.month, pay.year);
printf(" %s\n", pay.comment);
break;
case 'P': printf(" Pension: %s: %d/%d/%d: %d weeks %d days\n",
pay.br_name, pay.day, pay.month, pay.year,
pay.weeks, pay.days);
break;
case 'S': printf(" Sickness: %s: %d/%d/%d: %d weeks %d days\n",
pay.br_name, pay.day, pay.month, pay.year,
pay.weeks, pay.days);
break;
case 'U': printf(" Unempl: %s: %d/%d/%d: %d weeks %d days\n",
pay.br_name, pay.day, pay.month, pay.year,
pay.weeks, pay.days);
break;
default: printf(" Type %s: %s: %d/%d/%d: %d weeks %d days\n",
pay.type, pay.br_name, pay.day, pay.month, pay.year,
pay.weeks, pay.days);
break;
}
}

closec_1: EXEC SQL CLOSE c_1;
}

GetFun(target)
{
int f_type;

EXEC SQL DECLARE c_2 cursor for
select f.report, b.br_name,
f.year, f.month, f.day,
f.f_status, f.wname,
f.cname, f.sname, f.suffix,
f.age, f.cause, f.comment,
f.pounds, f.shillings, f.pence
from funerals f, branches b
where b.branch = f.branch and
f.mem_rec = :target
order by f.year, f.month, f.day;

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO closec_2;
EXEC SQL OPEN c_2;

/* Check value of sqlcode to see if there are still rows to process */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_2 INTO :fun:fun_ind;

f_type = fun.f_status[0];
switch (f_type) {
case 'D': if (fun_ind[6] == 0) {
printf(" Funeral benefit for his widow, %s,"
" was paid at %s on %d/%d/%d:\n",
fun.wname, fun.br_name,
fun.day, fun.month, fun.year);
}
else {
printf(" His widow's funeral benefit was"
" paid at %s on %d/%d/%d:\n",
fun.br_name, fun.day, fun.month, fun.year);
}
break;
case 'F': if (fun_ind[6] == 0) {
printf(" Funeral benefit for his wife, %s,"
" was paid at %s on %d/%d/%d:\n",
fun.wname, fun.br_name,
fun.day, fun.month, fun.year);
}
else {
printf(" His wife's funeral benefit was"
" paid at %s on %d/%d/%d:\n",
fun.br_name, fun.day, fun.month, fun.year);
}
break;
case 'M': printf(" His funeral benefit was paid at %s"
" on %d/%d/%d:\n",
fun.br_name, fun.day, fun.month, fun.year);
break;
default: printf(" Unknown funeral type: %s.\n",
fun.f_status);
break;
}

if (fun_ind[10] == 0) printf(" Age: %d.\n", fun.age);
if (fun_ind[11] == 0) printf(" Cause: %s.\n", fun.cause);
if (fun_ind[12] == 0) printf(" Comment: %s\n",
fun.comment);

/* Get death certificate information */
GetDCert(target);
}

closec_2: EXEC SQL CLOSE c_2;
}

GetRBook(target)
{
int r_type;

EXEC SQL DECLARE c_3 cursor for
select r.book, r.br_name, r.cname, r.sname, r.suffix,
r.age, r.trade, r.m_status,
r.year, r.month, r.day, r.comment
from reg_book r
where r.mem_rec = :target
order by r.year, r.month, r.day;

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO closec_3;
EXEC SQL OPEN c_3;

/* Check value of sqlcode to see if rows remain to process */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_3 INTO :rbk:rbk_ind;

printf(" Registration Book information:\n");
if(rbk_ind[8] == 0) printf(" Date of admission: "
"%d/%d/%d.\n",
rbk.day, rbk.month, rbk.year);
if(rbk_ind[1] == 0) printf(" Branch of admission: %s.\n",
rbk.br_name);
if(rbk_ind[6] == 0) printf(" Trade: %s.\n",
rbk.trade);
if(rbk_ind[5] == 0) printf(" Age: %d.\n",
rbk.age);
if(rbk_ind[7] == 0) {
r_type = rbk.m_status[0];
switch (r_type) {
case 'M': printf(" Marital status: Married.\n");
break;
case 'S': printf(" Marital status: Single.\n");
break;
case 'W': printf(" Marital status: Widowed.\n");
break;
default: printf(" [Invalid marital status code.]\n");
break;
}
}
}

closec_3: EXEC SQL CLOSE c_3;
}

GetAseRB(target)
{
int r_type;

EXEC SQL DECLARE c_4 cursor for
select r.br_name, r.cname, r.sname, r.suffix,
r.age_y, r.age_m, r.u_name, r.trade, r.m_status,
r.year, r.month, r.day, r.comment
from ase_reg r
where r.mem_rec = :target
order by r.year, r.month, r.day;

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO closec_4;
EXEC SQL OPEN c_4;

/* Check value of sqlcode to see if rows remain to process */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_4 INTO :arb:arb_ind;

printf(" ASE Registration Book information:\n");
if(arb_ind[11] == 0) printf(" Date of admission: %d/%d/%d.\n",
arb.day, arb.month, arb.year);
if(arb_ind[6] == 0) printf(" Union of admission: %s.\n",
arb.u_name);
if(arb_ind[0] == 0) printf(" Branch of admission: %s.\n",
arb.br_name);
if(arb_ind[7] == 0) printf(" Trade: %s.\n",
arb.trade);
if(arb_ind[4] == 0) {
printf(" Age: %d Years", arb.age_y);
if(arb_ind[5] == 0) {
if(arb.age_m != 0) printf(" %d Months", arb.age_m);
}
printf(".\n");
}
if(arb_ind[8] == 0) {
r_type = arb.m_status[0];
switch (r_type) {
case 'M': printf(" Marital status: Married.\n");
break;
case 'S': printf(" Marital status: Single.\n");
break;
case 'W': printf(" Marital status: Widowed.\n");
break;
default: printf(" [Invalid marital status code.]\n");
break;
}
}
if(arb_ind[12] == 0) printf(" Other information:\n %s\n",
arb.comment);
}

closec_4: EXEC SQL CLOSE c_4;
}

GetDCert(target)
{
EXEC SQL DECLARE c_5 cursor for
select d.cname, d.sname, d.suffix,
d.day, d.month, d.year, d.reg_dist,
d.age, d.occup, d.place, d.cause
from d_cert d
where d.mem_rec = :target
order by d.year, d.month, d.day;

EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO closec_5;
EXEC SQL OPEN c_5;

/* Check value of sqlcode to see if rows remain to process */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH c_5 INTO :dcert:dcert_ind;

printf(" Death Certificate information:\n");
if(dcert_ind[5] == 0) printf(" Date of death: %d/%d/%d.\n",
dcert.day, dcert.month, dcert.year);
if(dcert_ind[6] == 0) printf(" Registration District: %s.\n",
dcert.reg_dist);
if(dcert_ind[9] == 0) printf(" Place: %s.\n",
dcert.place);
if(dcert_ind[7] == 0) printf(" Age: %d.\n",
dcert.age);
if(dcert_ind[8] == 0) printf(" Occupation: %s.\n",
dcert.occup);
if(dcert_ind[10] == 0) printf(" Cause: %s.\n",
dcert.cause);
}

closec_5: EXEC SQL CLOSE c_5;
}


Specimen Output:

bash$ lifeline 51847025
Tracing lifeline for member 51847025:

1847: Member of HAWARDEN (Name: JOHN PRICE COEDTALON)
Registration Book information:
Date of admission: 20/6/1848.
Branch of admission: HAWARDEN.
Trade: FITTER.
Age: 20.
Marital status: Married.
1848: Member of HAWARDEN (Name: JOHN PRICE)
1849: Member of HAWARDEN (Name: JOHN PRICE)
1850: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 10/9/1850: 1 weeks 0 days
1852: Member of HAWARDEN (Name: JOHN PRICE)
1853: Member of HAWARDEN (Name: JOHN PRICE)
1854: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 6/10/1855: 1 weeks 0 days
Sickness: HAWARDEN: 3/11/1855: 1 weeks 2 days
1855: Member of HAWARDEN (Name: JOHN PRICE)
Unempl: HAWARDEN: 22/3/1856: 1 weeks 0 days
Unempl: HAWARDEN: 6/9/1856: 1 weeks 4 days
1856: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 18/4/1857: 2 weeks 1 days
Unempl: HAWARDEN: 21/7/1857: 2 weeks 3 days
Sickness: HAWARDEN: 28/11/1857: 4 weeks 0 days
1857: Member of HAWARDEN (Name: JOHN PRICE)
1858: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 30/10/1858: 13 weeks 0 days
1859: Member of HAWARDEN (Name: JOHN PRICE)
1860: Member of HAWARDEN (Name: JOHN PRICE)
1861: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 15/2/1862: 0 weeks 3 days
1862: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 27/9/1862: 0 weeks 4 days
Unempl: HAWARDEN: 11/4/1863: 1 weeks 0 days
1863: Member of HAWARDEN (Name: JOHN PRICE)
1864: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 30/7/1864: 0 weeks 5 days
1865: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 3/6/1865: 3 weeks 0 days
1866: Member of HAWARDEN (Name: JOHN PRICE)
1867: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 27/7/1867: 7 weeks 0 days
1868: Member of HAWARDEN (Name: JOHN PRICE)
1869: Member of HAWARDEN (Name: JOHN PRICE)
1870: Member of HAWARDEN (Name: JOHN PRICE)
1871: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 16/9/1871: 2 weeks 0 days
Sickness: HAWARDEN: 30/3/1872: 0 weeks 4 days
1872: Member of HAWARDEN (Name: JOHN PRICE)
Sickness: HAWARDEN: 8/2/1873: 4 weeks 3 days
Sickness: HAWARDEN: 18/3/1873: 1 weeks 1 days
1873: Member of HAWARDEN (Name: JOHN PRICE)
1874: Member of HAWARDEN (Name: JOHN PRICE)
1875: Member of HAWARDEN (Name: JOHN PRICE)
1876: Member of HAWARDEN (Name: JOHN PRICE)
bash$


(c) Humphrey Southall, 1997


Back to SEM Database Home Page