int integrity(struct db_context_t *dbc, struct integrity_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
struct sql_result_t result;
char query[256];
int W_ID=0;
dbt2_init_values(vals, nvals);
sprintf(query, INTEGRITY_1);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("INTEGRITY_1 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "INTEGRITY_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[W_ID] = dbt2_sql_getvalue(dbc, &result, 0); //W_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[W_ID])
{
LOG_ERROR_MESSAGE("ERROR: W_ID is NULL for query INTEGRITY_1:\n%s\n", query);
return -1;
}
if (atoi(vals[W_ID]) != w_id)
{
LOG_ERROR_MESSAGE("ERROR: Expect W_ID = %d Got W_ID = %d", w_id, atoi(vals[W_ID]));
return -1;
}
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
return 1;
}
int execute_integrity(struct db_context_t *dbc, struct integrity_t *data)
{
int rc;
char * vals[1];
int nvals=1;
rc=integrity(dbc, data, vals, nvals);
if (rc == -1 )
{
LOG_ERROR_MESSAGE("TEST FINISHED WITH ERRORS \n");
//should free memory that was allocated for nvals vars
dbt2_free_values(vals, nvals);
return ERROR;
}
return OK;
}
int execute_stock_level(struct db_context_t *dbc, struct stock_level_t *data)
{
int rc;
char * vals[2];
int nvals=2;
rc=stock_level(dbc, data, vals, nvals);
if (rc == -1 )
{
LOG_ERROR_MESSAGE("STOCK LEVEL FINISHED WITH ERRORS \n");
//should free memory that was allocated for nvals vars
dbt2_free_values(vals, nvals);
return evaluate_error_severity(dbc);
}
return OK;
}
int execute_order_status(struct db_context_t *dbc, struct order_status_t *data)
{
int rc;
int nvals=9;
char * vals[9];
rc= order_status(dbc, data, vals, nvals);
if (rc == -1 )
{
LOG_ERROR_MESSAGE("ORDER_STATUS FINISHED WITH ERRORS %d\n", rc);
//should free memory that was allocated for nvals vars
dbt2_free_values(vals, nvals);
return ERROR;
}
return OK;
}
int execute_delivery(struct db_context_t *dbc, struct delivery_t *data)
{
int rc;
int nvals=3;
char * vals[3];
dbt2_init_values(vals, nvals);
rc=delivery(dbc, data, vals, nvals);
if (rc == -1 )
{
LOG_ERROR_MESSAGE("DELIVERY FINISHED WITH ERRORS \n");
//should free memory that was allocated for nvals vars
dbt2_free_values(vals, nvals);
return ERROR;
}
return OK;
}
int execute_new_order(struct db_context_t *dbc, struct new_order_t *data)
{
int rc;
char * vals[6];
int nvals=6;
rc= new_order(dbc, data, vals, nvals);
if (rc)
{
LOG_ERROR_MESSAGE("NEW_ORDER FINISHED WITH RC %d\n", rc);
//should free memory that was allocated for nvals vars
dbt2_free_values(vals, nvals);
return ERROR;
}
return OK;
}
int order_status(struct db_context_t *dbc, struct order_status_t *data, char ** vals, int nvals)
{
/* Input variables. */
int c_id = data->c_id;
int c_w_id = data->c_w_id;
int c_d_id = data->c_d_id;
char c_last[C_LAST_LEN+1];
char query[512];
struct sql_result_t result;
int i;
int my_c_id = 0;
int TMP_C_ID=0;
int C_FIRST = 1;
int C_MIDDLE = 2;
int MY_C_BALANCE = 3;
int C_BALANCE = 4;
int O_ID = 5;
int O_CARRIER_ID = 6;
int O_ENTRY_D = 7;
int O_OL_CNT = 8;
char * ol_i_id[15];
char * ol_supply_w_id[15];
char * ol_quantity[15];
char * ol_amount[15];
char * ol_delivery_d[15];
unsigned long skip_rows;
dbt2_init_values(vals, nvals);
dbt2_init_values(ol_i_id, 15);
dbt2_init_values(ol_supply_w_id, 15);
dbt2_init_values(ol_quantity, 15);
dbt2_init_values(ol_amount, 15);
dbt2_init_values(ol_delivery_d, 15);
snprintf(c_last, C_LAST_LEN+1, "%s", data->c_last);
if (c_id == 0)
{
sprintf(query, ORDER_STATUS_1, c_w_id, c_d_id, c_last);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_1 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_1") && result.result_set)
{
//We have to get data from middle of result set
if (result.num_rows > 1 )
{
skip_rows=result.num_rows/2;
while (skip_rows && dbt2_sql_fetchrow(dbc, &result))
{
skip_rows--;
}
}
else
{
dbt2_sql_fetchrow(dbc, &result);
}
vals[TMP_C_ID]= dbt2_sql_getvalue(dbc, &result, 0); //TMP_C_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[TMP_C_ID])
{
LOG_ERROR_MESSAGE("ERROR: TMP_C_ID=NULL for query ORDER_STATUS_1:\n%s\n", query);
return -1;
}
my_c_id = atoi(vals[TMP_C_ID]);
}
else //error
{
return -1;
}
}
else
{
my_c_id = c_id;
vals[TMP_C_ID]=NULL;
}
sprintf(query, ORDER_STATUS_2, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_2 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_2") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[C_FIRST]= dbt2_sql_getvalue(dbc, &result, 0); //C_FIRST C_MIDDLE MY_C_BALANCE C_BALANCE
vals[C_MIDDLE]= dbt2_sql_getvalue(dbc, &result, 1);
vals[MY_C_BALANCE]= dbt2_sql_getvalue(dbc, &result, 2);
vals[C_BALANCE]= dbt2_sql_getvalue(dbc, &result, 3);
//FIXME: To add checks that vars above are not null
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
sprintf(query, ORDER_STATUS_3, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_3 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_3") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[O_ID]= dbt2_sql_getvalue(dbc, &result, 0); //O_ID O_CARRIER_ID O_ENTRY_D O_OL_CNT
vals[O_CARRIER_ID]= dbt2_sql_getvalue(dbc, &result, 1);
vals[O_ENTRY_D]= dbt2_sql_getvalue(dbc, &result, 2);
vals[O_OL_CNT]= dbt2_sql_getvalue(dbc, &result, 3);
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
sprintf(query, ORDER_STATUS_4, c_w_id, c_d_id, vals[O_ID]);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_4 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_4") && result.result_set)
{
i= 0;
while (dbt2_sql_fetchrow(dbc, &result))
{
ol_i_id[i]= dbt2_sql_getvalue(dbc, &result, 0);
ol_supply_w_id[i]= dbt2_sql_getvalue(dbc, &result, 1);
ol_quantity[i]= dbt2_sql_getvalue(dbc, &result, 2);
ol_amount[i]= dbt2_sql_getvalue(dbc, &result, 3);
ol_delivery_d[i]= dbt2_sql_getvalue(dbc, &result, 4);
i++;
}
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
dbt2_free_values(ol_i_id, 15);
dbt2_free_values(ol_supply_w_id, 15);
dbt2_free_values(ol_quantity, 15);
dbt2_free_values(ol_amount, 15);
dbt2_free_values(ol_delivery_d, 15);
return 1;
}
int pgsql_sp_new_order(struct db_context_t *_dbc, struct new_order_t *data)
{
char stmt[1024];
struct sql_result_t result;
char * tran_status[1];
/*
* Create the query and execute it.
*/
sprintf(stmt,
"DECLARE mycursor CURSOR FOR SELECT new_order("
"%d, %d, %d, %d, %d, "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d), "
"make_new_order_info(%d, %d, %d) )",
data->w_id, data->d_id, data->c_id, data->o_all_local,
data->o_ol_cnt,
data->order_line[0].ol_i_id,
data->order_line[0].ol_supply_w_id,
data->order_line[0].ol_quantity,
data->order_line[1].ol_i_id,
data->order_line[1].ol_supply_w_id,
data->order_line[1].ol_quantity,
data->order_line[2].ol_i_id,
data->order_line[2].ol_supply_w_id,
data->order_line[2].ol_quantity,
data->order_line[3].ol_i_id,
data->order_line[3].ol_supply_w_id,
data->order_line[3].ol_quantity,
data->order_line[4].ol_i_id,
data->order_line[4].ol_supply_w_id,
data->order_line[4].ol_quantity,
data->order_line[5].ol_i_id,
data->order_line[5].ol_supply_w_id,
data->order_line[5].ol_quantity,
data->order_line[6].ol_i_id,
data->order_line[6].ol_supply_w_id,
data->order_line[6].ol_quantity,
data->order_line[7].ol_i_id,
data->order_line[7].ol_supply_w_id,
data->order_line[7].ol_quantity,
data->order_line[8].ol_i_id,
data->order_line[8].ol_supply_w_id,
data->order_line[8].ol_quantity,
data->order_line[9].ol_i_id,
data->order_line[9].ol_supply_w_id,
data->order_line[9].ol_quantity,
data->order_line[10].ol_i_id,
data->order_line[10].ol_supply_w_id,
data->order_line[10].ol_quantity,
data->order_line[11].ol_i_id,
data->order_line[11].ol_supply_w_id,
data->order_line[11].ol_quantity,
data->order_line[12].ol_i_id,
data->order_line[12].ol_supply_w_id,
data->order_line[12].ol_quantity,
data->order_line[13].ol_i_id,
data->order_line[13].ol_supply_w_id,
data->order_line[13].ol_quantity,
data->order_line[14].ol_i_id,
data->order_line[14].ol_supply_w_id,
data->order_line[14].ol_quantity);
if (dbc_sql_execute(_dbc, stmt, NULL, NULL) == ERROR)
return ERROR;
if (dbc_sql_execute(_dbc, "FETCH ALL IN mycursor", &result, NULL) == ERROR)
return ERROR;
dbc_sql_fetchrow(_dbc, &result);
dbt2_init_values(tran_status, 1);
tran_status[0] = dbc_sql_getvalue(_dbc, &result, 0);
data->rollback = atoi(tran_status[0]);
dbt2_free_values(tran_status, 1);
dbc_sql_close_cursor(_dbc, &result);
if (dbc_sql_execute(_dbc, "CLOSE mycursor", NULL, NULL) == ERROR)
return ERROR;
return OK;
}
int delivery(struct db_context_t *dbc, struct delivery_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
int o_carrier_id = data->o_carrier_id;
struct sql_result_t result;
char query[256];
int d_id;
int NO_O_ID=0;
int O_C_ID=1;
int OL_AMOUNT=2;
for (d_id = 1; d_id <= 10; d_id++)
{
sprintf(query, DELIVERY_1, w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_1: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[NO_O_ID]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //NO_O_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[NO_O_ID])
{
LOG_ERROR_MESSAGE("ERROR: NO_O_ID=NULL for query DELIVERY_1:\n%s\n", query);
//return -1;
}
}
else
{
/* Nothing to delivery for this district, try next. */
continue;
}
if (vals[NO_O_ID] && atoi(vals[NO_O_ID])>0)
{
sprintf(query, DELIVERY_2, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_2: %s\n",query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_2"))
{
return -1;
}
sprintf(query, DELIVERY_3, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_3: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_3") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[O_C_ID]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //O_C_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[O_C_ID])
{
LOG_ERROR_MESSAGE("DELIVERY_3:query %s\nO_C_ID= NULL", query);
//return -1;
}
}
else //error
{
return -1;
}
sprintf(query, DELIVERY_4, o_carrier_id, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_4: query %s\n", query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_4"))
{
return -1;
}
sprintf(query, DELIVERY_5, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_5: query %s\n", query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_5"))
{
return -1;
}
sprintf(query, DELIVERY_6, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_6: query %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_6") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[OL_AMOUNT]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //OL_AMOUNT
dbt2_sql_close_cursor(dbc, &result);
if (!vals[OL_AMOUNT])
{
return -1;
}
}
else //error
{
return -1;
}
snprintf(query, 250, DELIVERY_7, vals[OL_AMOUNT], vals[O_C_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_7: query %s LEN %d\n", query, strlen(query));
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_7"))
{
LOG_ERROR_MESSAGE("DELIVERY_7: OL_AMOUNT: |%s| O_C_ID: |%s| query %s", vals[OL_AMOUNT],
vals[O_C_ID], query);
return -1;
}
}
dbt2_free_values(vals, nvals);
}
return 1;
}
int stock_level(struct db_context_t *dbc, struct stock_level_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
int d_id = data->d_id;
int threshold = data->threshold;
struct sql_result_t result;
int d_next_o_id = 0;
int low_stock = 0;
char query[256];
int D_NEXT_O_ID=0;
int LOW_STOCK=1;
dbt2_init_values(vals, nvals);
sprintf(query, STOCK_LEVEL_1, w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("STOCK_LEVEL_1 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "STOCK_LEVEL_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[D_NEXT_O_ID] = dbt2_sql_getvalue(dbc, &result, 0); //D_NEXT_O_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[D_NEXT_O_ID])
{
LOG_ERROR_MESSAGE("ERROR: D_NEXT_O_ID=NULL for query STOCK_LEVEL_1:\n%s\n", query);
return -1;
}
d_next_o_id = atoi(vals[D_NEXT_O_ID]);
}
else //error
{
return -1;
}
sprintf(query, STOCK_LEVEL_2, d_id, w_id, threshold, d_next_o_id - 20,
d_next_o_id - 1);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("STOCK_LEVEL_2 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "STOCK_LEVEL_2") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[LOW_STOCK]= dbt2_sql_getvalue(dbc, &result, 0); //LOW_STOCK
dbt2_sql_close_cursor(dbc, &result);
if (!vals[LOW_STOCK])
{
LOG_ERROR_MESSAGE("ERROR: LOW_STOCK=NULL for query STOCK_LEVEL_2:\n%s\n", query);
return -1;
}
low_stock = atoi(vals[LOW_STOCK]);
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
return 1;
}
