stocksql.c

用GTK+做用户界面 股票每日分时行情用数据库管理——MYSQL 软件应用方式客户端/服务器模式 基于TCP/IP的C/S通信模式 实现STOCK

  stock=(STOCK *)g_malloc(sizeof(STOCK)*(recsum+10));
//  num_fields = mysql_num_fields(result);
  i=1;
  while ((row = mysql_fetch_row(result)))
  {
//    lengths = mysql_fetch_lengths(result); 
     stock[i].jydate=atoi(row[0]);
     stock[i].kpj=atoi(row[1]);
     stock[i].spj=atoi(row[2]);
     stock[i].zgj=atoi(row[3]);
     stock[i].zdj=atoi(row[4]);
     stock[i].cjsl=(long)atoi(row[5]);
     stock[i].cjje=(long)atoi(row[6]);
     i++;   
  }
   mysql_free_result(result);
   getklineflag = 1;
   frechflag=1;
   curstocksum = recsum;
   stocksum=recsum;
   strcpy(stockcode,code);
   klinewidth=9;
   areastart=((stocksum-50)>=1)?(stocksum-50):1;
   mouselinex=stocksum;
   createJszbData();
   return recsum;  
}


int stockSql::createGpmxTable(int bz)
{
  char querystr[4096];
  char opt[]="( 
                code VARCHAR(6) NOT NULL,
                cjj  FLOAT(10,3) DEFAULT 0 NOT NULL,
                jj   FLOAT(10,3) DEFAULT 0 NOT NULL,
                zd   FLOAT(10,3) DEFAULT 0 NOT NULL,
                zdf  FLOAT(10,3) DEFAULT 0 NOT NULL,
                zsp  FLOAT(10,3) DEFAULT 0 NOT NULL,
                kpj  FLOAT(10,3) DEFAULT 0 NOT NULL,
                zgj  FLOAT(10,3) DEFAULT 0 NOT NULL,
                zdj  FLOAT(10,3) DEFAULT 0 NOT NULL,
                zss  FLOAT(15,3) DEFAULT 0 NOT NULL,
                xss  FLOAT(10,3) DEFAULT 0 NOT NULL,
                lb   FLOAT(10,3) DEFAULT 0 NOT NULL,
                wb   FLOAT(10,3) DEFAULT 0 NOT NULL,
                cjje FLOAT(15,3) DEFAULT 0 NOT NULL,
                zf   FLOAT(10,3) DEFAULT 0 NOT NULL,
                wp   FLOAT(10,3) DEFAULT 0 NOT NULL,
                np   FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrj1 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrj2 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrj3 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrl1 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrl2 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mrl3 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcj1 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcj2 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcj3 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcl1 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcl2 FLOAT(10,3) DEFAULT 0 NOT NULL,
                mcl3 FLOAT(10,3) DEFAULT 0 NOT NULL,
                PRIMARY KEY (code),
                KEY(zdf),
                KEY(zd),
                KEY(zf),
                KEY(zss),
                KEY(cjje),
                KEY(lb),
                KEY(wb));";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(isExistGpmxTable(bz)) return SUCCESS;
  if(bz == SHBZ)
    sprintf(querystr,"CREATE TABLE %s%s",SHGPMXTBL,opt);
  else
    sprintf(querystr,"CREATE TABLE %s%s",SZGPMXTBL,opt);
  if(!mysql_query(&stsql,querystr)){
    printf("create nametbl %d Table SUCCESS!\n",bz);  
    return SUCCESS;
  }
  else{
    printf("create nametbl:Error:%s\n",mysql_error(&stsql));  
    return FAIL;
  }
   
}

int stockSql::dropGpmxTable(int bz)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    sprintf(querystr,"DROP TABLE IF EXISTS %s;",SHGPMXTBL);
  else
    sprintf(querystr,"DROP TABLE IF EXISTS %s;",SZGPMXTBL);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::isExistGpmxTable(int bz)
{
  char tmp[256];
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    strcpy(tmp,SHGPMXTBL);
  else
    strcpy(tmp,SZGPMXTBL);
  result=mysql_list_tables(&stsql,tmp);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("Nametbl table exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("Nametbl table not exist!\n");  
      return FAIL;
  }  
}

int stockSql::selectGpmxTable(int bz)
{
  if(!sqlflag) return FAIL;
//  printf("sqlflag: %d\n",sqlflag);
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  return SUCCESS;
}

int stockSql::insertGpmxData(int bz,STOCKGPMX data)
{
  char querystr[4096];
  char tmp[20];
    
//  if(!sqlflag) return FAIL;
//  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  if(!isExistGpmxTable(bz)) return FAIL;
  if(bz == SHBZ)
     strcpy(tmp,SHGPMXTBL);
  else
     strcpy(tmp,SZGPMXTBL);
  sprintf(querystr,"INSERT INTO %s  VALUES('%s',%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%15.3f,%10.3f,%10.3f,%10.3f,%15.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f,%10.3f);",tmp,data.code,data.cjj,data.jj,data.zd,
          data.zdf,data.zsp,data.kpj,data.zgj,data.zdj,
          data.zss,data.xss,data.lb,data.wb,
          data.cjje,data.zf,data.wp,data.np,data.mrj1,data.mrj2,
          data.mrj3,data.mrl1,data.mrl2,data.mrl3,
          data.mcj1,data.mcj2,data.mcj3,data.mcl1,
          data.mcl2,data.mcl3);
   mysql_query(&stsql,querystr);
  return SUCCESS;
}

int stockSql::updateGpmxData(int bz,STOCKGPMX data)
{
  char querystr[4096];
  char tmp[20];
    
//  if(!sqlflag) return FAIL;
//  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;
  if(!isExistGpmxTable(bz)) return FAIL;
  if(bz == SHBZ)
     strcpy(tmp,SHGPMXTBL);
  else
     strcpy(tmp,SZGPMXTBL);
  sprintf(querystr,"UPDATE %s set  cjj=%10.3f,jj=%10.3f,zd=%10.3f,zdf=%10.3f,zsp=%10.3f,kpj=%10.3f,zgj=%10.3f,zdj=%10.3f,zss=%15.3f,xss=%10.3f,lb=%10.3f,wb=%10.3f,cjje=%15.3f,zf=%10.3f,wp=%10.3f,np=%10.3f,mrj1=%10.3f,mrj2=%10.3f,mrj3=%10.3f,mrl1=%10.3f,mrl2=%10.3f,mrl3=%10.3f,mcj1=%10.3f,mcj2=%10.3f,mcj3=%10.3f,mcl1=%10.3f,mcl2=%10.3f,mcl3=%10.3f WHERE code='%s';",tmp,
          data.cjj,data.jj,data.zd,
          data.zdf,data.zsp,data.kpj,data.zgj,data.zdj,
          data.zss,data.xss,data.lb,data.wb,
          data.cjje,data.zf,data.wp,data.np,data.mrj1,data.mrj2,
          data.mrj3,data.mrl1,data.mrl2,data.mrl3,
          data.mcj1,data.mcj2,data.mcj3,data.mcl1,
          data.mcl2,data.mcl3,data.code);
//  printf("%s",querystr);
  if(!mysql_query(&stsql,querystr)){
//    printf("update gpmx success.\n");  
    return SUCCESS;
  }
  else{
//     printf("update gpmx:Error:%s\n",mysql_error(&stsql));  
     return FAIL;
  }
}

int stockSql::insertGpmxDataFromNameTbl(int bz)
{
  char querystr[4096];
  
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    sprintf(querystr,"INSERT INTO %s (code) SELECT  code  FROM %s;",SHGPMXTBL,SHNAMETBL);
  else
    sprintf(querystr,"INSERT INTO %s (code) SELECT  code  FROM %s;",SZGPMXTBL,SZNAMETBL);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::insertGpmxDataFromNameTblWithZsp()
{
  int i;
  char querystr[4096];
  
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

   for(i=1;i<shstocksum;i++){
    sprintf(querystr,"INSERT INTO %s (code,zsp)  VALUES('%s',%10.3f);",SHGPMXTBL,shtbl[i].code,shtbl[i].zsp);
    mysql_query(&stsql,querystr);
   }
   for(i=1;i<szstocksum;i++){
    sprintf(querystr,"INSERT INTO %s (code,zsp)  VALUES('%s',%10.3f);",SZGPMXTBL,sztbl[i].code,sztbl[i].zsp);
    mysql_query(&stsql,querystr);
   }
   return SUCCESS;
}


int stockSql::deleteGpmxData(int bz)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)) return FAIL;

  if(bz == SHBZ)
    sprintf(querystr,"DELETE FROM %s;",SHGPMXTBL);
  else
    sprintf(querystr,"DELETE FROM %s;",SZGPMXTBL);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::getSqlGpmxData(int bz,char *code)
{
  char querystr[4096];
  char tmp[20];
  MYSQL_RES *result;
  MYSQL_ROW row;
  int recsum;

//  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKDATABASE)){
//     printf("select database fail.\n");
     return FAIL;
  }
  if(bz == SHBZ)
     strcpy(tmp,SHGPMXTBL);
  else
     strcpy(tmp,SZGPMXTBL);
  sprintf(querystr,"SELECT * FROM %s WHERE code='%s';",
           tmp,code);
  if(mysql_query(&stsql,querystr)){
//     printf("get gpmx:Error: %s\n",mysql_error(&stsql));  
     return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  recsum = mysql_num_rows(result);
  if(recsum <= 0) return FAIL;
  row = mysql_fetch_row(result);

  strcpy(gpmx.code,row[0]);
  gpmx.cjj=atof(row[1]);
  gpmx.jj=atof(row[2]);
  gpmx.zd=atof(row[3]);
  gpmx.zdf=atof(row[4]);
  gpmx.zsp=atof(row[5]);
  gpmx.kpj=atof(row[6]);
  gpmx.zgj=atof(row[7]);
  gpmx.zdj=atof(row[8]);
  gpmx.zss=atof(row[9]);
  gpmx.xss=atof(row[10]);
  gpmx.lb=atof(row[11]);
  gpmx.wb=atof(row[12]);
  gpmx.cjje=atof(row[13]);
  gpmx.zf=atof(row[14]);
  gpmx.wp=atof(row[15]);
  gpmx.np=atof(row[16]);
  gpmx.mrj1=atof(row[17]); 
  gpmx.mrj2=atof(row[18]); 
  gpmx.mrj3=atof(row[19]); 
  gpmx.mrl1=atof(row[20]); 
  gpmx.mrl2=atof(row[21]); 
  gpmx.mrl3=atof(row[22]); 
  gpmx.mcj1=atof(row[23]); 
  gpmx.mcj2=atof(row[24]); 
  gpmx.mcj3=atof(row[25]); 
  gpmx.mcl1=atof(row[26]); 
  gpmx.mcl2=atof(row[27]); 
  gpmx.mcl3=atof(row[28]); 

  mysql_free_result(result);
  return SUCCESS;  
}

int stockSql::getSqlGpmxListData(int bz,int order,int start)
{

  char querystr[4096];
  char tmp[20],tmp1[20];
  MYSQL_RES *result;
  MYSQL_ROW row;
  int recsum;
  int i;
  char c;

  if(mysql_select_db(&stsql,STOCKDATABASE)){
//     printf("select database fail.\n");
     return FAIL;
  }
  if(bz == SHBZ){
     strcpy(tmp,SHGPMXTBL);
     strcpy(tmp1,SHNAMETBL);
     c='6';
  }
  else{
     strcpy(tmp,SZGPMXTBL);
     strcpy(tmp1,SZNAMETBL);
     c='0';
  }
  if(order == 1){
    sprintf(querystr,"SELECT a.code, b.name,a.zsp,a.kpj,a.cjj,a.zd,a.zss,a.xss,a.zgj,a.zdj,a.mrj1,a.mcj1,a.zdf FROM %s as a,%s as b WHERE a.code=b.code AND LEFT(a.code,1)=%c ORDER BY a.code;"
    ,tmp,tmp1,c);
  }
  else{
    sprintf(querystr,"SELECT b.name,a.zsp,a.kpj,a.cjj,a.zd,a.zss,a.xss,a.zgj,a.zdj,a.mrj1,a.mcj1,a.zdf,a.code FROM %s as a,%s as b WHERE a.code=b.code ORDER BY a.zdf DESC;"
    ,tmp,tmp1);
  }  
  if(mysql_query(&stsql,querystr)){
     printf("get gpmxlist:Error: %s\n",mysql_error(&stsql));  
     return FAIL;
  }
  result=mysql_store_result(&stsql);
  
  recsum = mysql_num_rows(result);
  if(recsum <= 0) return FAIL;
  if(start > recsum)  return FAIL;
  gpmxlistsum=
    (recsum-start)>MAXGPMXLISTNUM?MAXGPMXLISTNUM:(recsum-start);
  if(gpmxlist)
      free(gpmxlist);
  gpmxlist=(STOCKGPMXLIST *)
       malloc(sizeof(STOCKGPMXLIST)*MAXGPMXLISTNUM);
  mysql_data_seek(result,start);
  i=0; 
  while((row = mysql_fetch_row(result)) && i<MAXGPMXLISTNUM){

    strcpy(gpmxlist[i].code,row[0]);
    strcpy(gpmxlist[i].name,row[1]);
    gpmxlist[i].zsp=atof(row[2]);
    gpmxlist[i].kpj=atof(row[3]);
    gpmxlist[i].cjj=atof(row[4]);
    gpmxlist[i].zd=atof(row[5]);
    gpmxlist[i].zss=atof(row[6]);
    gpmxlist[i].xss=atof(row[7]);
    gpmxlist[i].zgj=atof(row[8]);
    gpmxlist[i].zdj=atof(row[9]);
    gpmxlist[i].mrj=atof(row[10]); 
    gpmxlist[i].mcj=atof(row[11]); 
    gpmxlist[i].zdf=atof(row[12]);
    i++;
  }

  mysql_free_result(result);
  frechgpmxlistflag = SUCCESS;
  return SUCCESS;  

}



int stockSql::createFsDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
  if(isExistFsDatabase()) return SUCCESS;
  sprintf(querystr,"CREATE DATABASE %s;",STOCKFSDATABASE);
  if(!mysql_query(&stsql,querystr))
      return SUCCESS;
  else
      return FAIL;

}

int stockSql::dropFsDatabase()
{
  char querystr[4096];

  if(!sqlflag) return FAIL;
 sprintf(querystr,"DROP DATABASE IF EXISTS %s;",STOCKFSDATABASE);
  if(!mysql_query(&stsql,querystr))
       return SUCCESS;
  else
       return FAIL;
}

int stockSql::isExistFsDatabase()
{
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;

  result=mysql_list_dbs(&stsql,STOCKFSDATABASE);
  num= mysql_num_rows(result);
  mysql_free_result(result);
  if(num){
//     printf("stock fs database exist success!\n");
     return SUCCESS;
  }
  else{
//      printf("stock fs database not exist!\n");  
      return FAIL;
  }  

}

int stockSql::quickCreateFsTable(char *code)
{
  char querystr[4096];
  char opt[]="( 
                time     INT(8) NOT NULL,
                price    INT(8) DEFAULT '0' NOT NULL,
                avgprice INT(8) DEFAULT '0' NOT NULL,
                colume   INT(8) DEFAULT '0' NOT NULL,
                PRIMARY KEY (time));";

  sprintf(querystr,"CREATE TABLE %s_fs%s",code,opt);
  mysql_query(&stsql,querystr);
  return SUCCESS;
   
}

int stockSql::createFsTable(char *code)
{
  char querystr[4096];
  char opt[]="( 
                time     INT(8) NOT NULL,
                price    INT(8) DEFAULT '0' NOT NULL,
                avgprice INT(8) DEFAULT '0' NOT NULL,
                colume   INT(8) DEFAULT '0' NOT NULL,
                PRIMARY KEY (time));";
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKFSDATABASE)) return FAIL;

//  if(isExistFsTable(code)) return SUCCESS;
  sprintf(querystr,"CREATE TABLE %s_fs%s",code,opt);
  if(!mysql_query(&stsql,querystr)){
//    printf("create fs Table %s SUCCESS!\n",code);  
      return SUCCESS;
  }
  else{
//    printf("create fs Table: Error: %s\n",mysql_error(&stsql));  
      return FAIL;
  }
   
}

int stockSql::dropFsTable(char *code)
{
  char querystr[4096];
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKFSDATABASE)) return FAIL;

  sprintf(querystr,"DROP TABLE IF EXISTS %s_fs;",code);
  if(!mysql_query(&stsql,querystr))
        return SUCCESS;
  else
        return FAIL;
}

int stockSql::selectFsTable(char *code)
{
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKFSDATABASE)) return FAIL;
//  if(!isExistFsTable(code)) return FAIL;
  strcpy(curstockcode,code);
  return SUCCESS;
}

int stockSql::selectFsDatabase()
{
  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKFSDATABASE)) return FAIL;
//  if(!isExistFsTable(code)) return FAIL;
  return SUCCESS;
}

int stockSql::insertFsData(char *code,int num,STOCKFS *data)
{
  char querystr[4096];
  int i;
    
//  if(!selectDayKlineTable(code))  return FAIL;
  if(!isExistFsTable(code)) return FAIL;
  for(i=0;i<num;i++){
  sprintf(querystr,"INSERT INTO %s_fs 
      VALUES(%d,%d,%d,%d);",code,
      data[i].time,data[i].price,data[i].avgprice,data[i].colume);
   mysql_query(&stsql,querystr);
  }
  return SUCCESS;
}

int stockSql::isExistFsTable(char *code)
{
  char tmp[256];
  MYSQL_RES *result;
  int num;

  if(!sqlflag) return FAIL;
  if(mysql_select_db(&stsql,STOCKFSDATABASE)) return FAIL;