// PenpalCPP.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"

#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>
#include <string>
#include <time.h>
#include <iomanip>
#include <math.h>
#include <algorithm>
#include <vector>
#include <thread>
#include <chrono>
#include <regex>
#include <iomanip>      // std::get_time
#include <ctime>        // struct std::tm

/* uncomment for applications that use vectors */
/*#include <vector>*/

#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>


#define EXAMPLE_HOST "localhost"
#define EXAMPLE_USER "smsEngine"
#define EXAMPLE_PASS "guEnaR28T"
#define EXAMPLE_DB "smartsmsrouter"

using namespace std;

string date()

{

	time_t rawtime;
	tm * timeinfo;
	char buffer[80];
	time(&rawtime);
	timeinfo = localtime(&rawtime);
	//2014 - 10 - 17 00:00 : 00
	strftime(buffer, 80, "%Y-%m-%d %H:%M:%S", timeinfo);
	std::string retVal = buffer;
	return(retVal);
}

string date(int n)

{


	time_t rawtime;
	tm * timeinfo;
	char buffer[80];
	time(&rawtime);
	rawtime = rawtime - (24 * 60 * 60 * n);
	timeinfo = localtime(&rawtime);
	//2014 - 10 - 17 00:00 : 00
	strftime(buffer, 80, "%Y-%m-%d", timeinfo);
	std::string retVal = buffer;
	return(retVal);



}


string mytime()

{

	time_t rawtime;
	tm * timeinfo;
	char buffer[80];
	time(&rawtime);
	timeinfo = localtime(&rawtime);
	//2014 - 10 - 17 00:00 : 00
	strftime(buffer, 80, "%H:%M", timeinfo);
	std::string retVal = buffer;
	return(retVal);
}

string getQuery(string myquery)
{
	string response = "#";

	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			response = res->getString(1);
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

	return response;
}//end of getQuery


string updateQuery(string myquery)
{
	string response = "#";

	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");
		cout << myquery << endl;
		stmt = con->createStatement();
		stmt->execute(myquery);
		//delete res;
		delete stmt;
		delete con;



	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return response;
}//end of getQuery



vector<string> split(string str, char delimiter) {
	vector<string> internal;
	stringstream ss(str); // Turn the string into a stream.
	string tok;

	while (getline(ss, tok, delimiter)) {
		internal.push_back(tok);
	}

	return internal;
}


string checkSpamFilter(string messageTocheck)
{
	vector<string> word;
	string response = "#";
	string strfield = "";
	string myquery = "select spamword from penpalspamfilter";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;
		word = split(messageTocheck,' ');
		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			strfield = res->getString(1);
			//if (messageTocheck.find(strfield) > 0)
			if (std::find(word.begin(), word.end(),strfield) != word.end())
				response = "0";
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return response;
}//end of getQuery


string getChatRooms()
{
	vector<string> word;
	string response = "";
	string response1 = "";
	string response2 = "";
	string strfield1 = "";
	string strfield2 = "";
	string myquery = "select chatroom, count(*) as tot  from penpalsubscriber  group by chatroom order by  tot desc;";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;
		
		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		int cnt7 = 1;
		while (res->next()) {
			strfield1 = res->getString(1);
			strfield2 = res->getString(2);
			response1 = response1 + to_string(cnt7) + " " + strfield1 + "(" + strfield2 + ")\n";
			response2 = response2 + "For  " + strfield1 + "\nSend Penpal R "+ to_string(cnt7) +"\n";
			cnt7++;
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}


	response = response1 + response2;
	return response;
}//end of getQuery




string getRowNumber(string query)
{
	int rowNum = 0;
	string pagenum = "0";


	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(query);
		rowNum = res->rowsCount();
		cout << "Number of rows : " << rowNum << endl;
		pagenum = to_string(rowNum / 2 + rowNum % 2);
		cout << "Number of pagenum : " << pagenum << endl;
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return pagenum;
}//end of getLocations



void PenpalsessionSearch(string msisdn, string method, string firstpage, string endpage, string recordno,string command1, string command2){

	string query = "select msisdn from penpalsessionsearch where msisdn ='" + msisdn + "'";
	string counter = getQuery(query);
	if (counter != "#"){

		
		query = "Update penpalsessionsearch set method='" + method + "',firstpage='" + firstpage + "',endpage='" + endpage + "',recordno='" + recordno + "',response1='" + command1 + "',response2='" + command2 + "' where msisdn ='" + msisdn + "'";
		updateQuery(query);

	}
	else{

		query = "insert into penpalsessionsearch (msisdn,method,firstpage,endpage,recordno,response1,response2) values ('" + msisdn + "','" + method + "','" + firstpage + "','" + endpage + "','" + recordno + "','" + command1 + "','" + command2 + "')";
		updateQuery(query);
	}


}




string getLocations(string msisdn,string recordindex,string method,string firstpage)
{
	string rowNum = "";
	string pagenum = "0";
	vector<string> word;
	string response = "";
	string response1 = "";
	string response2 = "";
	string strfield1 = "";
	string strfield2 = "";
	string command1 = "";
	string command2 = "";
	
	//get chatroom 

	string mquery = "select response2 from penpalsession where msisdn='" + msisdn + "' and command1 ='R' ";
	string chatroom = getQuery(mquery);
	
	//get rownumber
	mquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location";
	 pagenum = getRowNumber(mquery);

	 string myquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location order by  tot desc limit " + recordindex + ",2;";
	//string myquery = "select location, count(*) as tot  from penpalsubscriber  group by location";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		//pagenum =res->rowsCount();
		//cout << "Number of rows : " << pagenum << endl;
		//cout << pagenum / 2  + pagenum% 2;
		int cnt7 = 1;
		while (res->next()) {
			strfield1 = res->getString(1);
			strfield2 = res->getString(2);
			if (cnt7 == 1)
				command1 = strfield1;
			if (cnt7 == 2)
				command2 = strfield1;
			response1 = response1 + to_string(cnt7) + " " + strfield1 + "(" + strfield2 + ")\n";
			response2 = response2 + "For " + strfield1 + " Send\nPenpal L " + to_string(cnt7) + "\n";
			cnt7++;
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

	//update penpalsessionsearch
	PenpalsessionSearch(msisdn, method, firstpage, pagenum, recordindex,command1,command2);
	response = response1 + response2;
	return response;
}//end of getLocations





string getSubscribers(string msisdn, string recordindex, string method, string firstpage)
{
	string rowNum = "";
	string pagenum = "0";
	vector<string> word;
	string response = "";
	string response1 = "";
	string response2 = "";
	string response3 = "";
	string strfield1 = "";
	string strfield2 = "";
	string strfield3 = "";
	string command1 = "";
	string command2 = "";
	string msex = "";
	string location = "";
	string sex = "";
	string mstate = "";
	//get chatroom 

	string mquery = "select response2 from penpalsession where msisdn='" + msisdn + "'";
	string chatroom = getQuery(mquery);

	

	mquery = "select state from penpalsessionsearch where msisdn='" + msisdn + "'";
	mstate = getQuery(mquery);

	if (mstate == "1")
		mquery = "select response1 from penpalsessionsearch where msisdn='" + msisdn + "'";
	else
		mquery = "select response2 from penpalsessionsearch where msisdn='" + msisdn + "'";

	location= getQuery(mquery);

	mquery = "select sex from penpalsessionsearch where msisdn='" + msisdn + "'";
	sex = getQuery(mquery);

	

	//get rownumber
	//mquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location";
	mquery = "select nickname, age, palid from penpalsubscriber where chatroom = '" + chatroom + "' and location = '" + location + "' and sex = '" + sex + "' and msisdn != '" + msisdn + "'";
	cout << mquery << endl;
	pagenum = getRowNumber(mquery);


	//mquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location order by  tot desc limit " + recordindex + ",2;";
	mquery = "select nickname, age, palid from penpalsubscriber where chatroom = '" + chatroom + "' and location = '"+location+"' and sex = '"+sex+"' and msisdn != '"+ msisdn+"' order by sn desc limit " + recordindex + ",2";
	//string myquery = "select location, count(*) as tot  from penpalsubscriber  group by location";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(mquery);
		//pagenum =res->rowsCount();
		//cout << "Number of rows : " << pagenum << endl;
		//cout << pagenum / 2  + pagenum% 2;
		int cnt7 = 1;
		while (res->next()) {
			strfield1 = res->getString(1);
			strfield2 = res->getString(2);
			strfield3 = res->getString(3);
			///*if (cnt7 == 1)
			//	command1 = strfield1;
			//if (cnt7 == 2)
			//	command2 = strfield1;*/
			response1 = response1 +  strfield1 + " " + strfield2 + " " + strfield3 + " \n";
			response2 = response2 + "To chat with " + strfield1 + " Send\nPenpal C " + strfield3 + " <yourMessage>\n";
			cnt7++;
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

	//update penpalsessionsearch

	mquery = "Update penpalsessionsearch set method='" + method + "',firstpage='" + firstpage + "',endpage='" + pagenum + "',recordno='" + recordindex + "' where msisdn ='" + msisdn + "'";
	updateQuery(mquery);

	response = response1 + response2;
	return response;
}//end of getSubscribers






string getSubscribersSex(string msisdn, string recordindex, string method, string firstpage)
{
	string rowNum = "";
	string pagenum = "0";
	vector<string> word;
	string response = "";
	string response1 = "";
	string response2 = "";
	string response3 = "";
	string strfield1 = "";
	string strfield2 = "";
	string strfield3 = "";
	string command1 = "";
	string command2 = "";
	string msex = "";
	string location = "";
	string sex = "";
	string mstate = "";
	//get chatroom 

	string mquery = "select response2 from penpalsession where msisdn='" + msisdn + "'";
	string chatroom = getQuery(mquery);



	///*mquery = "select state from penpalsessionsearch where msisdn='" + msisdn + "'";
	//mstate = getQuery(mquery);

	//if (mstate == "1")
	//	mquery = "select response1 from penpalsessionsearch where msisdn='" + msisdn + "'";
	//else
	//	mquery = "select response2 from penpalsessionsearch where msisdn='" + msisdn + "'";

	//location = getQuery(mquery);*/

	mquery = "select sex from penpalsessionsearch where msisdn='" + msisdn + "'";
	sex = getQuery(mquery);



	//get rownumber
	//mquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location";
	mquery = "select nickname, age, palid from penpalsubscriber where chatroom = '" + chatroom + "' and sex = '" + sex + "' and msisdn != '" + msisdn + "'";
	cout << mquery << endl;
	pagenum = getRowNumber(mquery);


	//mquery = "select location, count(*) as tot  from penpalsubscriber where chatroom ='" + chatroom + "'  group by location order by  tot desc limit " + recordindex + ",2;";
	mquery = "select nickname, age, palid from penpalsubscriber where chatroom = '" + chatroom + "' and sex = '" + sex + "' and msisdn != '" + msisdn + "' order by sn desc limit " + recordindex + ",2";
	//string myquery = "select location, count(*) as tot  from penpalsubscriber  group by location";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(mquery);
		//pagenum =res->rowsCount();
		//cout << "Number of rows : " << pagenum << endl;
		//cout << pagenum / 2  + pagenum% 2;
		int cnt7 = 1;
		while (res->next()) {
			strfield1 = res->getString(1);
			strfield2 = res->getString(2);
			strfield3 = res->getString(3);
			///*if (cnt7 == 1)
			//	command1 = strfield1;
			//if (cnt7 == 2)
			//	command2 = strfield1;*/
			response1 = response1 + strfield1 + " " + strfield2 + " " + strfield3 + " \n";
			response2 = response2 + "To chat with " + strfield1 + " Send\nPenpal C " + strfield3 + " <yourMessage>\n";
			cnt7++;
		}
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

	//update penpalsessionsearch

	mquery = "Update penpalsessionsearch set method='" + method + "',firstpage='" + firstpage + "',endpage='" + pagenum + "',recordno='" + recordindex + "' where msisdn ='" + msisdn + "'";
	updateQuery(mquery);

	response = response1 + response2;
	return response;
}//end of getSubscribersSex




string getState(string msisdn, string keyword)
{
	string query = "";
	string response = "";
	
	//get chatroom 

	if (keyword == "1")
		query = "select response1 from penpalsessionsearch where msisdn='" + msisdn + "'";
	else
		query = "select response2 from penpalsessionsearch where msisdn='" + msisdn + "'";

	response = getQuery(query);

	//update penpalsessionsearch

	query = "Update penpalsessionsearch set state='" + keyword + "' where msisdn ='" + msisdn + "'";
	updateQuery(query);

	
	return response;
}//end of getstate




string getState2(string msisdn)
{
	string query = "";
	string response = "";
	string mstate = ""
		;
	query = "select state from penpalsessionsearch where msisdn='" + msisdn + "'";
	mstate = getQuery(query);

	if (mstate == "1")
		query = "select response1 from penpalsessionsearch where msisdn='" + msisdn + "'";
	else
		query = "select response2 from penpalsessionsearch where msisdn='" + msisdn + "'";

	
	response = getQuery(query);

	

	return response;
}//end of getstate2



string getGender(string msisdn)
{
	string query = "";
	string response = "";
	
		;
	query = "select sex from penpalsessionsearch where msisdn='" + msisdn + "' and method='S'";
	response = getQuery(query);

	/*if (mstate == "1")
		query = "select response1 from penpalsessionsearch where msisdn='" + msisdn + "'";
	else
		query = "select response2 from penpalsessionsearch where msisdn='" + msisdn + "'";*/


	response = getQuery(query);



	return response;
}//end of getstate2

void updatePenpalSession(string msisdn,string keyword2, string keyword3,string response2) {
	string query = "select command1 from penpalsession where msisdn ='" + msisdn + "' and command1='"+keyword2+"'";
	string command1 = getQuery(query);
	if (command1 != "#"){

		query = "Update penpalsession set command1='" + keyword2 + "',response1='" + keyword3 + "',response2='" + response2 + "' where msisdn ='" + msisdn + "' and command1='" + keyword2 + "'";
		updateQuery(query);

	}
	else{

		//int totalmessage = stoi(counter) + 1;
		query = "insert into penpalsession (msisdn,command1,response1,response2,status) values ('" + msisdn + "','" + keyword2 + "','" + keyword3 + "','" + response2 + "',1)";
		updateQuery(query);
	}


}



void updatePenpalSessionSex(string msisdn, string keyword1, string keyword2) {
	string query = "select command1 from penpalsessionSearch where msisdn ='" + msisdn + "' and command1='" + keyword1 + "'";
	string command1 = getQuery(query);
	if (command1 != "#"){

		query = "Update penpalsessionsearch set sex='" + keyword2 + "' where msisdn ='" + msisdn + "' and command1='" + keyword1 + "'";
		updateQuery(query);

	}
	else{

		//int totalmessage = stoi(counter) + 1;
		query = "insert into penpalsessionsearch (msisdn,command1,response1) values ('" + msisdn + "','" + keyword1 + "','" + keyword2 + "')";
		updateQuery(query);
	}


}


void messageCounter(string msisdn){
	string query = "select counter from penpalmessagecount where msisdn ='" + msisdn + "'";
	string counter = getQuery(query);
	if (counter != "#"){

		int totalmessage = stoi(counter) + 1;
		query = "Update penpalmessagecount set counter=" + to_string(totalmessage) + " where msisdn ='" + msisdn + "'";
		updateQuery(query);

	}
	else{

		//int totalmessage = stoi(counter) + 1;
		query = "insert into penpalmessagecount (msisdn,counter) values ('" + msisdn + "',1)";
		updateQuery(query);
	}


}

void messageCounter(string msisdn,string msisdnpal){
	string query = "select counter from penpaloneonone where msisdnsender ='" + msisdn + "' and msisdnreceiver ='"+msisdnpal+"' and counter >0";
	string counter = getQuery(query);
	if (counter != "#"){

		int totalmessage = stoi(counter) + 1;
		query = "Update penpaloneonone set counter=" + to_string(totalmessage) + " where msisdnsender ='" + msisdn + "' and msisdnreceiver ='" + msisdnpal + "'";
		updateQuery(query);

	}
	else{

		//int totalmessage = stoi(counter) + 1;
		query = "insert into penpaloneonone (msisdnsender,msisdnreceiver,counter) values ('" + msisdn + "','"+msisdnpal+"',1)";
		updateQuery(query);
	}


}





string  checkOneOnOne(string msisdn, string msisdnpal)
{

	string response = "#";

	//check if subscriber has sent 2 or more messages to pal
	string query = "select * from penpaloneonone where msisdnsender ='"+msisdn+"' and msisdnreceiver ='"+msisdnpal+"'  and counter >=2";
	string queryResponse = getQuery(query);
	if (queryResponse != "#"){

		query = "select * from penpaloneonone where msisdnsender ='" + msisdnpal + "' and msisdnreceiver ='" + msisdn + "'  and counter >=1";
		queryResponse = getQuery(query);
		if (queryResponse == "#")
		    response = "0";
	}
    return response;
}






void updateAccounts()
{

	string myquery = "select sn, DATEDIFF(expirydate, NOW()) as expired,msisdn from penpalsubscriber where billcode ='1'";
	string serial ="";
	int expirydate = 0;
	string query = "";
	string msisdn = "";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			serial = res->getString(1);
			expirydate = res->getInt(2);
			msisdn = res->getString(3);
			if (expirydate < 0){
				cout << "Update Billcode for " << msisdn << endl;
				query = "update penpalsubscriber set billcode='0' where sn="+serial;
				updateQuery(query);
			}
		}

		




		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	
}//end of updateAccounts


string getQuery2(string myquery )
{

	string response1 = "#";
	string response2 = "#";
	string myresponse = "";
	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			response1 = res->getString(1);
			response2 = res->getString(2);
		}

		myresponse = response1 + "," + response2;

	

	
		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return myresponse;
}//end of getQuery2







string getQuery4(string myquery)
{	string response1 = "#";
	string response2 = "#";
	string response3 = "#";
	string response4 = "#";
	string myresponse = "";

	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			response1 = res->getString(1);
			response2 = res->getString(2);
		    response3 = res->getString(3);
			response4 = res->getString(4);

		}

		myresponse = response1 + "," + response2 + "," + response3 + "," + response4;




		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return myresponse;
}//end of getQuery4




string getQuery3(string myquery)
{
	string response1 = "#";
	string response2 = "#";
	string response3 = "#";
	
	string myresponse = "";

	try {
		sql::Driver *driver;
		sql::Connection *con;
		sql::Statement *stmt;
		sql::ResultSet *res;

		/* Create a connection */
		driver = get_driver_instance();
		con = driver->connect("tcp://127.0.0.1:3306", "smsEngine", "guEnaR28T");
		/* Connect to the MySQL test database */
		con->setSchema("smartsmsrouter");

		stmt = con->createStatement();
		res = stmt->executeQuery(myquery);
		while (res->next()) {
			response1 = res->getString(1);
			response2 = res->getString(2);
			response3 = res->getString(3);
	
		}

		myresponse = response1 + "," + response2 + "," + response3 ;




		delete res;
		delete stmt;
		delete con;

	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}



	return myresponse;
}//end of getQuery3


// trim from start (in place)
static inline void ltrim(std::string &s) {
	s.erase(s.begin(), std::find_if(s.begin(), s.end(), [](int ch) {
		return !isspace(ch);
	}));
}

// trim from end (in place)
static inline void rtrim(std::string &s) {
	s.erase(std::find_if(s.rbegin(), s.rend(), [](int ch) {
		return !isspace(ch);
	}).base(), s.end());
}

// trim from both ends (in place)
static inline std::string trim(std::string &s) {
	ltrim(s);
	rtrim(s);
	return s;
}

// trim from start (copying)
static inline std::string ltrim_copy(std::string s) {
	ltrim(s);
	return s;
}

// trim from end (copying)
static inline std::string rtrim_copy(std::string s) {
	rtrim(s);
	return s;
}

// trim from both ends (copying)
static inline std::string trim_copy(std::string s) {
	trim(s);
	return s;
}




bool is_number(const std::string &s) {
	return !s.empty() && std::all_of(s.begin(), s.end(), ::isdigit);
}

static std::time_t to_time_t(const std::string& str, bool is_dst = false, const std::string& format = "%Y-%b-%d %H:%M:%S")
{
	std::tm t = { 0 };
	t.tm_isdst = is_dst ? 1 : 0;
	std::istringstream ss(str);
	ss >> std::get_time(&t, format.c_str());
	return mktime(&t);
}

int _tmain(int argc, _TCHAR* argv[])
{

	vector<string> sep;
	string msisdn = "";
	string nickname = "";
	string palid = "";
	double timediff = 0;
	string sn = "";
	string lastlogin = "";
	string query = "";
	
	while (1)
	{
		

		if ((mytime() == "00:01") || (mytime() == "00:02") || (mytime() == "00:03")){

			//update offline messages on a new day

			query = "update penpalmessageoffline set status='0'";
		    updateQuery(query);

			cout << "Please wait, table penpalmessageoffline being updated to status 0 " << endl;


			//update  message on a new day

			query = "delete from penpalmessagelimit";
			updateQuery(query);

			cout << "Please wait, table penpalmessagelimit will be deleted " << endl;

			//update billcode
			cout << "Update Billcode to zero" << endl;
			updateAccounts();


			this_thread::sleep_for(chrono::seconds(60));
			//cnt4 = 0;
		}
		//std::cout << "Total Messagesent today " << cnt4 << endl;

		//string test = "abc deF abc def";
		//test = regex_replace(test, regex("def", std::regex_constants::icase), "klm");
		//cout << "test" << test << endl;
		string url(argc >= 2 ? argv[1] : EXAMPLE_HOST);
		const string user(argc >= 3 ? argv[2] : EXAMPLE_USER);
		const string pass(argc >= 4 ? argv[3] : EXAMPLE_PASS);
		const string database(argc >= 5 ? argv[4] : EXAMPLE_DB);

		try {




			sql::Driver * driver = get_driver_instance();
			std::unique_ptr< sql::Connection > con(driver->connect(url, user, pass));
			con->setSchema(database);
			std::unique_ptr< sql::Statement > stmt(con->createStatement());
			std::unique_ptr< sql::PreparedStatement >  pstmt;
			std::unique_ptr< sql::ResultSet > res;
			std::unique_ptr< sql::PreparedStatement >  pstmt2;
			std::unique_ptr< sql::ResultSet > res2;
			//		pstmt.reset(con->prepareStatement("CALL get_pop_continent(?,@pop)"));
			//	res.reset(stmt->executeQuery("SELECT @pop AS _population"));
			pstmt.reset(con->prepareStatement("select sn, msisdn, nickname, palid, lastlogin, TIMESTAMPDIFF(SECOND,lastactive,now()) as timedifference from penpalsubscriber where online = '1' order by sn limit 1000"));
			res.reset(pstmt->executeQuery());
			while (res->next())
			{




				//cout << "Working..." << endl;

				sn = res->getString(1);
				msisdn = res->getString(2);
				nickname = res->getString(3);
				palid = res->getString(4);
				lastlogin = res->getString(5);
				timediff = res->getDouble(6);
				//cout << timediff << endl; 
				
				if (timediff > 900){
					cout << "msisdn " << msisdn << " " << nickname << " Lastlogin " << lastlogin << " difference " << timediff << endl;
					query = "update penpalsubscriber set online ='0' where sn = '" + sn + "'";
					pstmt2.reset(con->prepareStatement(query));
					pstmt2->execute();
					cout << "msisdn " << msisdn << " status changed to offline" << endl;
				}


			}//end of while loop


		}
		catch (sql::SQLException &e) {

			cout << "# ERR: SQLException in " << __FILE__;
			cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
			/* what() (derived from std::runtime_error) fetches error message */
			cout << "# ERR: " << e.what();
			cout << " (MySQL error code: " << e.getErrorCode();
			cout << ", SQLState: " << e.getSQLState() << " )" << endl;

			return EXIT_FAILURE;
		}

		cout << "Sleeping " << date() << endl;
		//Sleep(3000);
		this_thread::sleep_for(chrono::seconds(5));
	}//end of while
	return EXIT_SUCCESS;

}