#include <WSCstring.h>
#include <WSCdb.h>

#include "newwin000.h"
#define BUF_SQL_MAX_MESSAGE_LENGTH 256

WSCstring dsn;
WSCstring uid;
WSCstring psw;
WSCstring dbn;
WSCstring prt;
WSCbool connected = False;
extern WSCvdb* newvdb__000;

void setChar(char* str, long len, char ch);

void app_init(){
}
long db_connect(){
  WSCstring cout;
  long ret = newdial_001->popup();
  if(ret == WS_DIALOG_OK){
    dsn = newvifi_003->getProperty(WSNlabelString);
    uid = newvifi_006->getProperty(WSNlabelString);
    psw = newvifi_000->getProperty(WSNlabelString);
    dbn = newvifi_001->getProperty(WSNlabelString);
    prt = newvifi_004->getProperty(WSNlabelString);
    if(newvdb__000->isOpen()) {
      newvdb__000->close();
    }
    if(newvdb__000->open(dsn, uid, psw,dbn,prt) == WS_ERR) {
      connected = False;
      char szErrorMsg[BUF_SQL_MAX_MESSAGE_LENGTH + 1];
      newvdb__000->getErrorMsg(szErrorMsg,BUF_SQL_MAX_MESSAGE_LENGTH);
      cout << szErrorMsg << "\n";
    } else {
      connected = True;
      cout << "connect ok\n";
    }
  }
  newtext_004->setProperty(WSNlabelString, cout);
  return ret;
}

void db_init()
{
	char buf1[512];
	if(connected == False){
		if(db_connect() != WS_DIALOG_OK) {
			return;
		}
	}

	strcpy(buf1, "drop table shinamono");
	long ret = newvdb__000->sqlExecute(buf1);
  
	strcpy(buf1, "create table shinamono(code int, hinmei char(20), nedan float)");
	newvdb__000->sqlExecute(buf1);

	newvdb__000->beginTran();
	strcpy(buf1, "insert into shinamono values(1, 'みかん', 100)");
	newvdb__000->sqlExecute(buf1);
	strcpy(buf1, "insert into shinamono values(2, 'りんご', 200)");
	newvdb__000->sqlExecute(buf1);
	strcpy(buf1, "insert into shinamono values(3, 'バナナ', 300)");
	newvdb__000->sqlExecute(buf1);
	strcpy(buf1, "insert into shinamono values(4, 'メロン', 0)");
	newvdb__000->sqlExecute(buf1);

	newvdb__000->commitTran();

    newtext_004->setProperty(WSNlabelString, "create ok\n");
}

void sqltest()
{
	char buf1[512];

	if(connected == False){
		if(db_connect() != WS_DIALOG_OK) {
			return;
		}
	}

	WSCdbRecord rs(newvdb__000);

	WSCvariant var;
	WSCstring cout;
	WSCstring cout1;

	newvdb__000->beginTran();

	cout1 << "select * from shinamono order by code" << "\n";
	
	if(rs.open("select * from shinamono order by code") == WS_NO_ERR) {
		while (!rs.isEOF()) {
			rs.getColValue("code", &var);
			int code = (int)var;
			cout << "code:" << (int)var << " ";
			rs.getColValue("hinmei", &var);
			cout << "hinmei:" << (char*)var << " ";
			rs.getColValue("nedan", &var);
			char buf[80];
			double nedan = (float)var + 10;

			sprintf(buf, "%f", (float)var);
			cout << "nedan:" << buf << "\n";

			if(nedan != 0) {
				sprintf(buf1, "update shinamono set nedan = %f where code = %d", nedan, code);
			} else {
				sprintf(buf1, "delete from shinamono where code = %d", code);
			}

			cout1 << buf1 << "\n";
			
			newvdb__000->sqlExecute(buf1);

			rs.moveNext();
		}
	}
	rs.close();
	
	newvdb__000->commitTran();


	if(cout[0] == '\0') {
      char szErrorMsg[BUF_SQL_MAX_MESSAGE_LENGTH + 1];
      newvdb__000->getErrorMsg(szErrorMsg,BUF_SQL_MAX_MESSAGE_LENGTH);
      cout << szErrorMsg << "\n";
	}
	newtext_003->setProperty(WSNlabelString, cout1);
	newtext_004->setProperty(WSNlabelString, cout);
}
void sql_execute()
{
	char buf1[512];
	char buf2[256];

	strcpy(buf1, (char*)newtext_003->getProperty(WSNlabelString));
	if(buf1[0] == '\0') {
		return;
	}
	if(connected == False){
		if(db_connect() != WS_DIALOG_OK) {
			return;
		}
	}

	WSCdbRecord rs(newvdb__000);

	char var[256];
	WSCstring cout;

	newvdb__000->beginTran();

	if(rs.open(buf1) == WS_NO_ERR) {
		buf1[6] = '\0';
		if(WSGFstricmp(buf1, "select") != 0) {
			rs.open("select * from shinamono order by code");
		}
		int i;
		memset(buf1, '\0', sizeof(buf1));
		memset(buf2, '\0', sizeof(buf2));
		for(i = 0; i < rs.getColCount(); i++) {
			rs.getColName(i, buf2);
			setChar(buf2, rs.getColLength(i), ' ');
			strcat(buf1, buf2);
			strcat(buf1, " ");
		}
		cout << buf1 << "\n";
		buf1[0] = '\0';
		for(i = 0; i < rs.getColCount(); i++) {
			buf2[0] = '\0';
			setChar(buf2, rs.getColLength(i), '-');
			strcat(buf1, buf2);
			strcat(buf1, "+");
		}
		cout << buf1 << "\n";
		while (!rs.isEOF()) {
			buf1[0] = '\0';
			for(int i = 0; i < rs.getColCount(); i++) {
				rs.getColStringValue((long)i, var);
				setChar(var, rs.getColLength(i), ' ');
				cout << var << " ";
			}
			cout << "\n";

			rs.moveNext();
		}
	}
	rs.close();

	newvdb__000->commitTran();


	if(cout[0] == '\0') {
      char szStat[6];
      char szErrorMsg[BUF_SQL_MAX_MESSAGE_LENGTH + 1];
      newvdb__000->getErrorMsg(szErrorMsg,BUF_SQL_MAX_MESSAGE_LENGTH);
      cout << szErrorMsg << "\n";
	}

	newtext_004->setProperty(WSNlabelString, cout);
}
void dbclose()
{
	newvdb__000->close();
}
void setChar(char* str, long len, char ch)
{
	len = len > 40 ? 40 : len;
	for(int i = strlen(str); i < len; i++) {
		str[i] = ch;
	}
	str[len] = '\0';
}
