makeSql.c

#include  <stdio.h>
#include  <stdlib.h>
#include  <string.h>
#include  <strings.h>
#include  <unistd.h>
#include  <ctype.h>
#include <sybfront.h>
#include <sybdb.h>
#include "sybdbex.h"

#define  rOK          0
#define  rNG         -1
#define  BUFFER_SIZE  1024

void makeOutfileName(char*, char*);
int getConnection_id(char* , char* , char* );
/* Forward declarations of the error handler and message handler. */
int CS_PUBLIC   err_handler();
int CS_PUBLIC   msg_handler();
DBPROCESS   *dbproc;       /* Our connection with SQL Server. */
LOGINREC    *login;        /* Our login information. */
 
/*****************************************************************************************************/
/* ユーザから送られてくる代表口座番号、担当トレーダのシートを読み込み、SQL文を生成するツール         */
/* 準備:                                                                                             */
/*    1.シートをcsv形式に変換し、このサーバにFTPする                                                 */
/*      *) FTPする際は、asciiモード SJIS->EUC変換する。FFFTPを使うと楽                               */
/*    2.本番環境の fix_connection_tbl, fix_trader_tbl のデータを noah_db2 に入れておく               */
/*                                                                                                   */
/* 実行方法:                                                                                         */
/*     - 既存顧社のトレーダ変更の場合                                                                */
/*         makeSql [口座番号]  <  [csvファイル]                                                      */
/*     - 新規顧客の場合                                                                              */
/*         makeSql [口座番号] [新規コネクションID] <  [csvファイル]                                  */
/*     - シート上のすべてのものが対象                                                                */
/*         makeSql < [csvファイル]                                                                   */
/*                                                                                                   */
/*     標準出力にSQL文が出力される                                                                   */
/*****************************************************************************************************/
 
int main(int argc, char* argv[])
{
  FILE*  fp;
  FILE*  fp2;
  char   buffer[BUFFER_SIZE];
  char   workbuf[BUFFER_SIZE];
  char*  office_code;
  char*  account_no;
  char   connection_id[BUFFER_SIZE];
  char*  wk_str;
  char   workFile[64];
  char   ac[64];
  char   co[64];
  char   filename[64];
  int    cnt=0;
 
 
  if(argc > 3 ) {
    printf("Usage : %s [account_no] [connection_id] < csvfile \n", argv[0]);
    return(rNG);
  }
 
  if(argc == 2 && !strcmp(argv[1], "-h") ){
    printf("Usage : %s [account_no] [connection_id] < csvfile \n", argv[0]);
    return(rNG);
  }
 
  memset(ac, '\0', sizeof(ac));
  memset(co, '\0', sizeof(co));
 
  fp = stdin;
  fp2 = stdout;
 
  if ( argc == 3  ) {             /* 新規の場合  */
    strcpy(ac, argv[1]);
    strcpy(co, argv[2]);
  }
 
  if ( argc == 2 ) {             /* 既存修正の場合  */
    strcpy(ac, argv[1]);
  }
 
 
  if (dbinit() == FAIL)         /* Initialize DB-Library. */
    exit(ERREXIT);
 
/* Install the user-supplied error-handling and message-handling
 * routines. They are defined at the bottom of this source file.
 */
  dberrhandle((EHANDLEFUNC)err_handler);
  dbmsghandle((MHANDLEFUNC)msg_handler);
 
  login = dblogin();
  DBSETLUSER(login, (char*)USER);
  DBSETLPWD(login, (char*)PASSWORD);
  DBSETLAPP(login, (char*)"example1");
  DBSETLCHARSET(login, (char*)LANGUAGE);
 
  dbproc = dbopen(login, NULL);
  dbuse(dbproc, (char*)"noah_db2");
 
  fprintf(fp2, "use noahdb\n");
  fprintf(fp2, "go\n");
  while(fgets(buffer, BUFFER_SIZE, fp)){
    if ( wk_str=strchr(buffer, '\n') ) {
       *wk_str=NULL;
    }
    strtok(buffer, ",");           /* 1カラム目(通番)を飛ばす */
    office_code=strtok(NULL, ",");
    account_no=strtok(NULL, ",");
 
    if ( office_code == NULL || account_no == NULL) {
       continue;
    }
    if ( !isdigit(office_code[0]) )  /* 不正行を飛ばす */
        continue;
 
    /* 新規、または　既存修正の場合、引数に指定された口座番号ではなかったら飛ばす */
    if ( ( argc == 2 || argc == 3 ) && strcmp(account_no, ac) )
        continue;
 
    /* 新規の場合、引数に指定された connection_id　を使用する        */
    if ( strcmp(account_no, ac) == 0 && argc == 3 ) {
       strcpy(connection_id, co);
    } else {
       if ( getConnection_id(office_code, account_no, connection_id) == 0  ) {
         fprintf(fp2, "/*------------------------------------------------------------------*/\n");
         fprintf(fp2, "/* office_code=[%s] account_no=[%s] ", office_code, account_no);
         fprintf(fp2, "not found                */\n");
         continue;
       }
    }
 
    fprintf(fp2, "/*------------------------------------------------------------------*/\n");
    fprintf(fp2, "/* office_code=[%s] account_no=[%s] ", office_code, account_no);
    fprintf(fp2, "connection_id=[%s]    */\n", connection_id );
 
    strtok(NULL, ",");           /* 4カラム目(1部or2部)を飛ばす */
    strtok(NULL, ",");           /* 5カラム目(顧客名)を飛ばす   */
    for ( cnt=0 ; wk_str=strtok(NULL, ",") ; cnt++ ) {
       if ( strlen(wk_str) != 0 && isdigit(wk_str[0]) && strchr(wk_str, '/') == NULL ) {
         if ( cnt == 0 ) {
           if ( argc == 3 ) {   /* 新規   */
              fprintf(fp2, "%s%s%s%s%s%s%s%s%s\n", "insert into fix_connection_tbl values (<CompID>, <LocationID>, <SubID>, \'",
                       office_code, "\', \'", account_no, "\', \'", wk_str, "\', \'", connection_id,
                       "\', <gw_process_id>, <vendorName>)");
           } else {            /* 既存顧客の修正    */
              fprintf(fp2, "%s%s%s%s%s%s\n", "update fix_connection_tbl set trader_code = \'", wk_str, "\'",
                        " where connection_id = \'" , connection_id, "\'");
              fprintf(fp2, "%s%s%s\n", "delete from fix_trader_tbl where connection_id=\'", connection_id, "\'");
           }
         }
         fprintf(fp2, "%s%s%s%s%s\n", "insert into fix_trader_tbl values (\'", connection_id, "\', \'", wk_str, "\')");
       }
    }
  }
  fprintf(fp2, "go\n");
 
  dbexit();
  fclose(fp);
  fclose(fp2);
}
 
void makeOutfileName(char* argv, char* workFile)
{
  char* ptr;
  if ( (ptr=strrchr(argv, '/')) != NULL ) {
     strcpy(workFile, ++ptr);
  } else {
     strcpy(workFile, argv);
  }
  strcat(workFile, ".log");
  return ;
}
 
 
int getConnection_id(char* office_code, char* account_no, char* connection_id)
{
        RETCODE     result_code;
        char        sql_buf[128];
        int         existflg=0;
 
        strcpy(sql_buf, "select connection_id from fix_connection_tbl where office_code=\'");
        strcat(sql_buf, office_code);
        strcat(sql_buf, "\' and account_no=\'");
        strcat(sql_buf, account_no);
        strcat(sql_buf, "\'");
 
#ifdef DEBUG
fprintf(stderr, "%s\n\n", sql_buf);
#endif

        dbcmd(dbproc, sql_buf);
        dbsqlexec(dbproc);
 
        while ((result_code = dbresults(dbproc)) != NO_MORE_RESULTS) {
                if (result_code == SUCCEED) {
                        /* Bind program variables. */
                        dbbind(dbproc, 1, NTBSTRINGBIND, (DBINT)0, (BYTE DBFAR *)connection_id);
                        while (dbnextrow(dbproc) != NO_MORE_ROWS) {
                                existflg=1;
                                if ((DBCURCMD(dbproc) == 2) && (DBCURROW(dbproc) > 10))
                                        continue;
#ifdef DEBUG
                                fprintf (stderr, "%s\n", connection_id);
#endif
                        }
                }
        }
        if ( existflg ) {
                return 1;
        } else {
                return 0;
        }
}
 
int CS_PUBLIC err_handler(DBPROCESS *dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
        if ((dbproc == NULL) || (DBDEAD(dbproc)))
                return(INT_EXIT);
        else
        {
                fprintf (ERR_CH, "DB-Library error:\n\t%s\n", dberrstr);
 
                if (oserr != DBNOERR)
                        fprintf (ERR_CH, "Operating-system error:\n\t%s\n", oserrstr);
 
                return(INT_CANCEL);
        }
}
 
int CS_PUBLIC msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext,
                char *srvname, char *procname, int line)
{
/*
        fprintf (ERR_CH, "Msg %d, Level %d, State %d\n",
                msgno, severity, msgstate);
 
        if (strlen(srvname) > 0)
                fprintf (ERR_CH, "Server '%s', ", srvname);
        if (strlen(procname) > 0)
                fprintf (ERR_CH, "Procedure '%s', ", procname);
        if (line > 0)
                fprintf (ERR_CH, "Line %d", line);
 
        fprintf (ERR_CH, "\n\t%s\n", msgtext);
*/
        return(0);
}