#include <stdio.h>
#include <stdlib.h>
#include <oci.h>
#include <ocidem.h>
#include <assert.h>
Lda_Def lda;
Cda_Def cda;
ub1 hda[256];
text *sql_statement = "SELECT acyc_id,integrate_item_code,balance,late_balance, /
canpay_tag FROM ts_a_bill WHERE balance <> 0 and rownum<=10000";
text *insert_statement = "INSERT INTO zhuyf1 (STAT_MONTH, DATE_ID, EPARCHY_CODE, ACYC_ID, /
INTEGRATE_ITEM_CODE, BAL_SUM, LATE_BAL_SUM, CANPAY_TAG) /
VALUES (200702, 2, '0022', :1, :2, :3, :4, :5)";
typedef struct billnode{
int acyc_id;
int integrate_item_code;
float balance;
float late_balance;
char canpay_tag;
struct billnode *next;
}bill;
typedef bill* BillList;
typedef struct debtnode{
int stat_month;
int data_id;
int acyc_id;
char eparchy_code[5];
int integrate_item_code;
float bal_sum;
float late_bal_sum;
char canpay_tag;
struct debtnode *next;
}debt;
typedef debt* DebtList;
void oci_error(void)
{
text msg[600];
sword rv;
rv = oerhms(&lda, cda.rc, msg, 600);
printf("/n/n%.*s", rv, msg);
printf("processing OCI function %s/n", oci_func_tab[cda.fc]);
if(oclose(&cda))
exit(1);
if(ologof(&lda))
exit(1);
exit(0);
}
main()
{
sword find;
bill *h, *s, *p;
debt *d_h, *d_s, *d_p, *d_tail;
int acyc_id, integrate_item_code;
float balance, late_balance;
ub1 canpay_tag;
int i, j, k;
h = s = p = NULL;
d_h = d_s = d_p = d_tail = NULL;
i = j = k = 0;
if(olon(&lda, "ucr_bafs/ucrbafsabc@SYSMGR_TJ1", -1, 0, -1, 0)){
printf("conn ucr_bafs error./n");
oci_error();
}
if(oopen(&cda, &lda, 0, -1, -1, 0, -1)){
printf("open cursor error./n");
oci_error();
}
if(oparse(&cda, sql_statement, -1, 1, 2))
oci_error();
// if(odefin(&cda, 1, &acyc_id, (int)sizeof(int), 2, -1, (sb2 *) 0, 0, -1, -1))
// oci_error();
if(odefin(&cda, 1, &acyc_id, (int)sizeof(int), 3, -1, (sb2 *) 0, (text *) 0, -1, -1, (ub2 *) 0, (ub2 *) 0))
oci_error();
if(odefin(&cda, 2, &integrate_item_code, (int)sizeof(int), 3, -1, (sb2 *) 0, (text *) 0, -1, -1, (ub2 *) 0, (ub2 *) 0))
oci_error();
if(odefin(&cda, 3, &balance, (int)sizeof(float), 4, -1, (sb2 *) 0, (text *) 0, -1, -1, (ub2 *) 0, (ub2 *) 0))
oci_error();
if(odefin(&cda, 4, &late_balance, (int)sizeof(float), 4, -1, (sb2 *) 0, (text *) 0, -1, -1, (ub2 *) 0, (ub2 *) 0))
oci_error();
if(odefin(&cda, 5, &canpay_tag, (int)sizeof(char), 1, -1, (sb2 *) 0, (text *) 0, -1, -1, (ub2 *) 0, (ub2 *) 0))
oci_error();
// 出库
if(oexec(&cda))
oci_error();
for (; ;) {
if(ofetch(&cda))
if (cda.rc != 1403) {
oci_error();
exit(1);
}
if (cda.rc == 1403)
break;
s = (bill *)malloc(sizeof(bill));
assert(s);
s->next = NULL;
s->acyc_id = acyc_id;
s->integrate_item_code = integrate_item_code;
s->balance = balance;
s->late_balance = late_balance;
s->canpay_tag = canpay_tag;
s->next = h;
h = s;
i++;
printf("bill list %d/n", i);
}
// 汇总
p = h->next;
while (p) {
find = 0;
// printf("acyc_id=%d integrate_item_code=%d balance=%f late_balance=%f canpay_tag=%c /n",
// p->acyc_id, p->integrate_item_code, p->balance, p->late_balance, p->canpay_tag);
if (d_h == NULL) {
d_s = (debt *)malloc(sizeof(debt));
assert(d_s);
d_s->next = NULL;
d_s->acyc_id = p->acyc_id;
d_s->integrate_item_code = p->integrate_item_code;
d_s->canpay_tag = p->canpay_tag;
d_s->bal_sum = p->balance;
d_s->late_bal_sum = p->late_balance;
d_h = d_s;
d_tail = d_s;
j++;
printf("debt list %d/n", j);
}else{
d_p = d_h;
while (d_p) {
if ((d_p->acyc_id == p->acyc_id) && (d_p->integrate_item_code == p->integrate_item_code) ) {
d_p->bal_sum += p->balance;
d_p->late_bal_sum += p->late_balance;
find = 1;
break;
}
d_p = d_p->next;
}
if (find == 0) {
d_s = (debt *)malloc(sizeof(debt));
assert(d_s);
d_s->next = NULL;
d_s->acyc_id = p->acyc_id;
d_s->integrate_item_code = p->integrate_item_code;
d_s->canpay_tag = p->canpay_tag;
d_s->bal_sum = p->balance;
d_s->late_bal_sum = p->late_balance;
d_tail->next = d_s;
d_tail = d_s;
j++;
printf("debt list %d/n", j);
}
}
p = p->next;
}
if(oclose(&cda))
exit(1);
if(oopen(&cda, &lda, 0, -1, -1, 0, -1))
oci_error();
if(oparse(&cda, insert_statement, -1, 1, 2))
oci_error();
d_p = d_h;
printf("-------------------------------------------------------------------------------");
while (d_p) {
k++;
// printf("acyc_id=%d integrate_item_code=%d balance=%f late_balance=%f canpay_tag=%c /n",
// d_p->acyc_id, d_p->integrate_item_code, d_p->bal_sum, d_p->late_bal_sum, d_p->canpay_tag);
// 入库
if(obndrn(&cda, 1, &(d_p->acyc_id), (int)sizeof(int), 3, -1, 0, 0, -1, -1))
oci_error();
if(obndrn(&cda, 2, &(d_p->integrate_item_code), (int)sizeof(int), 3, -1, 0, 0, -1, -1))
oci_error();
if(obndrn(&cda, 3, &(d_p->bal_sum), (int)sizeof(float), 4, -1, 0, 0, -1, -1))
oci_error();
if(obndrn(&cda, 4, &(d_p->late_bal_sum), (int)sizeof(float), 4, -1, 0, 0, -1, -1))
oci_error();
if(obndrn(&cda, 5, &(d_p->canpay_tag), (int)sizeof(char), 1, -1, 0, 0, -1, -1))
oci_error();
if(oexec(&cda))
oci_error();
d_p = d_p->next;
printf("group list %d/n", k);
}
if(oclose(&cda))
exit(1);
if(ologof(&lda))
exit(1);
exit(0);
}