/* Function void makeComparison(IADOTierPtr dbConn, int modelID, std::string machineName)
 * This function takes a pointer to the database, a model ID, a machine name, and a
 * string with an email address.  It opens the database and looks to the model.  It takes 
 * the last 9 points put into the database and compares it to the model.  If there is an 
 * error, an email is sent to the address.*/
bool makeComparison(IADOTierPtr dbConn, int modelID, std::string machineName, std::string emailIP, int emailPort, std::string emailFrom, std::string emailAddress)
{
	/*the number of fields used in this model*/
	int numberOfFields = 0;
	/*variables used in the statistical test*/
	int twoOfThree = 0;
	int fourOfFive = 0;
	int sixOfSeven = 0;
	int nineOnOneSide = 0;
	/*counter used to go through the 9 records*/
	int topNineCtr;
	/*generic counter*/
	int counter = 0;
	/*stores the 9 records.  it is a pointer to 9 arrays of integers*/
	int *pulledData[9];
	/*stores data about the model*/
	double difference[9];
	double deviation[9];
	double average[9];
	int pointCount[9];
	/*tracks if a record was found in the model for this state*/
	int recordFound[9];
	/*buffer to convert int to string*/
	char intBuffer[4];
	/*pointer to an array that contains the list of fields*/
	std::string *fieldList;
	/*string with SQL command*/
	std::string SQLStr = "";
	/*holds the model ID as a string*/
	std::string modelIDStr = "";
	/*used for ModelID conversion*/
	std::stringstream intBuff;

	try{
		/*convert modelID to string*/
		intBuff << modelID;
		intBuff >> modelIDStr;

		/*pull out all the fields. the last one is the dependent*/
		SQLStr = "Select * from Model_Fields where ModelID = " + modelIDStr + " order by type";
		/*get the data*/
		dbConn->OpenRecordset(SQLStr.c_str());
		if(!dbConn->GetEmpty()){
			dbConn->First();

			/*count the records returned*/
			while (!dbConn->ADOEOF)
			{
				numberOfFields++;
				dbConn->Next();
			}
			/*create the array for the fields*/
			fieldList = new std::string[numberOfFields];
			dbConn->First();
			/*get all the field names*/
			while (!dbConn->ADOEOF)
			{
				fieldList[counter] = (std::string)(_bstr_t)dbConn->GetField("FieldName");
				dbConn->Next();
				counter++;
			}
			dbConn->CloseRecordset();

			/*create the actual arrays*/
			for(topNineCtr = 0; topNineCtr < 9; topNineCtr++){
				pulledData[topNineCtr] = new int[numberOfFields];
			}

			/*construct SQL string to figure out the table name for this machine*/
			SQLStr = "SELECT TargetFile FROM DSJobsConfig WHERE JobName = '" + machineName + "' AND JobType = 1";
			/*run the SQL*/
			dbConn->ExecuteConnection(SQLStr.c_str(), VARIANT_FALSE);	
			dbConn->OpenRecordset(SQLStr.c_str());
			if(!dbConn->GetEmpty()){
				dbConn->First();
				/*get the table name from another table and use it in the next SQL string*/
				SQLStr = "select top 9 * from ";
				SQLStr = SQLStr + (std::string)(_bstr_t)dbConn->GetField("TargetFile");
				SQLStr = SQLStr + " order by CurrentDateTime desc";
			}
			dbConn->CloseRecordset();
			/*grab data from the table that stores it all*/
			dbConn->OpenRecordset(SQLStr.c_str());
			topNineCtr = 0;
			if(!dbConn->GetEmpty()){
				dbConn->First();
				/*go through the 9 returned records*/
				while (!dbConn->ADOEOF)
				{
					/*grab each field individually and put it into pulledData for storage*/
					for(counter = 0; counter<numberOfFields;counter++){
						pulledData[topNineCtr][counter] = dbConn->GetField(fieldList[counter].c_str()).intVal;
					}
					topNineCtr++;
					dbConn->Next();
				}		
			}
			dbConn->CloseRecordset();

			/*go through each stored record*/
			for(topNineCtr = 0; topNineCtr < 9; topNineCtr++){
				/*create the SQL string using independent fields*/
				SQLStr = "Select dataAverage, dataDeviation, pointCount from " + machineName + "_Model_" + modelIDStr + " WHERE ";
				/*tack on the where clause*/
				for(counter = 0; counter < numberOfFields - 1; counter++){
					sprintf(intBuffer,"%d", pulledData[topNineCtr][counter]);
					SQLStr = SQLStr + fieldList[counter] + " = " + intBuffer;
					if(counter < numberOfFields - 2){
						SQLStr = SQLStr + " AND ";
					}
				}

				/*call the SQL string that gets the appropriate entry in the model*/
				dbConn->OpenRecordset(SQLStr.c_str());
				if(!dbConn->GetEmpty()){
					/*there should be only one if there are any*/
					dbConn->First();
					/*grab the model data*/
					average[topNineCtr] = dbConn->GetField("dataAverage").dblVal;
					deviation[topNineCtr] = dbConn->GetField("dataDeviation").dblVal;
					pointCount[topNineCtr] = dbConn->GetField("pointCount").intVal;
					difference[topNineCtr] = average[topNineCtr] - pulledData[topNineCtr][numberOfFields - 1];

					/*record that the record was found*/
					recordFound[topNineCtr] = FOUND;
				}
				else{
					/*record the lack of a record*/
					recordFound[topNineCtr] = NOT_FOUND;		
				}
				dbConn->CloseRecordset();
			}
			//do statistical tests
			for(topNineCtr = 0; topNineCtr < 9; topNineCtr++){
				/*only work with the found records*/
				/*if it wasn't listed in the model, it gets ignored*/
				if(recordFound[topNineCtr] == FOUND){
					/*if the difference is negative*/
					if(difference[topNineCtr] < 0){
						nineOnOneSide--;
					}
					/*if the difference is positive*/
					if(difference[topNineCtr] > 0){
						nineOnOneSide++;
					}
					/*perform two of three test*/
					if(topNineCtr <= 2){
						/*if the point is 3 sigma from the average*/
						if(abs(difference[topNineCtr]) >= (3 * deviation[topNineCtr])){
							twoOfThree++;
						}
					}
					/*perform four of five test*/
					if(topNineCtr <= 4){
						/*if the point is 2 sigma from the average*/
						if(abs(difference[topNineCtr]) >= (2 * deviation[topNineCtr])){
							fourOfFive++;
						}
					}
					/*perform six of seven test*/
					if(topNineCtr <= 6){
						/*if the point is 1 sigma from the average*/
						if(abs(difference[topNineCtr]) >= deviation[topNineCtr]){
							sixOfSeven++;
						}
					}

					if(twoOfThree >= 2){
						/*send email about 2 of 3 error*/
						emailSend(emailIP, emailPort, emailFrom, emailAddress, "Error Found!", "2 of 3 error");
						return true;
					}
					if(fourOfFive >= 4){
						/*send email about 4 of 5 error*/
						emailSend(emailIP, emailPort, emailFrom, emailAddress, "Error Found!", "4 of 5 error");
						return true;
					}
					if(sixOfSeven >= 6){
						/*send email about 6 of 7 error*/
						emailSend(emailIP, emailPort, emailFrom, emailAddress, "Error Found!", "6 of 7 error");
						return true;
					}

					if(nineOnOneSide == 9 || nineOnOneSide == -9){
						/*send email about 9 on one side error*/
						emailSend(emailIP, emailPort, emailFrom, emailAddress, "Error Found!", "9 on one side error");
						return true;
					}
				}
			}
		}
	}
	catch (_com_error& e)
	{
		return false;
	}	
	return true;
} 




/*this function sends an email to the specified IP and port.
 *I do not like this function.  The arrays are bounded, so the data reading could eventually
 * run off the end of the array and cause very bad things to happen.  Unfortunately, I do not
 * have the time right now to fix it.  It's on my to do list*/
void emailSend(std::string emailIP, int emailPort, std::string emailFrom, std::string emailAddress, std::string subject, std::string message)
{
	WSADATA *WsaData = new WSADATA;
	SOCKET s;
	struct sockaddr_in sa;
	struct hostent *hp;
	WORD VersionRequested;
	char * buff;
	int bytes_read = 0;
	int Error;

    // Start WinSock 2.  If it fails, we don't need to call
    // WSACleanup().
    VersionRequested = MAKEWORD(2, 0);
    Error = WSAStartup(VersionRequested, WsaData);
    if (Error) {
        return;
    }
	else {

        // Now confirm that the WinSock 2 DLL supports the exact version
        // we want. If not, make sure to call WSACleanup().
        if (LOBYTE(WsaData->wVersion) != 2) {
            WSACleanup();
            return;
        }
    }

	hp = gethostbyname(emailIP.c_str());
	/*cant find the host.  not sure what to do now so we return*/
	if (hp == NULL){
		return;
	}
 	
	memset(&sa,0,sizeof(sa));
 	memcpy((char *)&sa.sin_addr, hp->h_addr, hp->h_length); /* set address */
 	sa.sin_family = hp->h_addrtype;
 	sa.sin_port = htons(emailPort);

 	s = socket(hp->h_addrtype, SOCK_STREAM, 0);
	/*if socket not created, return*/
	if (s == INVALID_SOCKET){
		return;
	}
  	
	/* try to connect to the specified socket */
  	if (connect(s, (struct sockaddr *)&sa, sizeof sa) == SOCKET_ERROR) {
		/*return if it fails*/
		closesocket(s);
		return;
	}

	if (WsaData == NULL){
		return;
	}
	else{
			/*create the buffer*/
			buff = (char *)malloc(1024 * sizeof(char));
			bytes_read = 0;
			/*get the first two bytes*/
			bytes_read += recv(s, buff, 1, 0);
			bytes_read += recv(s, buff + 1, 1, 0);
			/*check for the end sequence*/
			while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
				bytes_read += recv(s, buff + bytes_read, 1, 0);
			}

			/*220 is the connection message*/
			if(strncmp(buff, "220", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*send the HELO*/
				send(s, "HELO www.aol.com\r\n", 18, 0);
				bytes_read = 0;
				bytes_read += recv(s, buff, 1, 0);
				bytes_read += recv(s, buff + 1, 1, 0);
				while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
					bytes_read += recv(s, buff + bytes_read, 1, 0);
				}
			}
			else{
				free(buff);
				return;
			}

			/*if HELO is accepted, you get a 250*/
			if(strncmp(buff, "250", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*specify sender*/
				send(s, "MAIL FROM: <", 12, 0);
				send(s, emailFrom.c_str(), emailFrom.length(), 0);
				send(s, ">\r\n", 3, 0);
				bytes_read = 0;
				bytes_read += recv(s, buff, 1, 0);
				bytes_read += recv(s, buff + 1, 1, 0);
				while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
					bytes_read += recv(s, buff + bytes_read, 1, 0);
				}

			}
			else{
				free(buff);
				return;
			}

			/*if sender is accepted you get 250*/
			if(strncmp(buff, "250", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*specify recipient*/
				send(s, "RCPT TO: <", 10, 0);
				send(s, emailAddress.c_str(), emailAddress.length(), 0);
				send(s, ">\r\n", 3, 0);
				bytes_read = 0;
				bytes_read += recv(s, buff, 1, 0);
				bytes_read += recv(s, buff + 1, 1, 0);
				while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
					bytes_read += recv(s, buff + bytes_read, 1, 0);
				}
			}
			else{
				free(buff);
				return;
			}

			/*if recipient is accepted you get a 250*/
			if(strncmp(buff, "250", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*tell it we're sending the data*/
				send(s, "DATA\r\n", 6, 0);
				bytes_read = 0;
				bytes_read += recv(s, buff, 1, 0);
				bytes_read += recv(s, buff + 1, 1, 0);
				while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
					bytes_read += recv(s, buff + bytes_read, 1, 0);
				}
			}
			else{
				free(buff);
				return;
			}

			/*if data was accepted, we get the go ahead 354*/
			if(strncmp(buff, "354", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*send the subject line*/
				send(s, "SUBJECT: ", 9, 0);
				send(s, subject.c_str(), subject.length(), 0);
				send(s, "\r\n", 2, 0);
				/*send the message*/
				if(message.length() > 0){
					send(s, message.c_str(), message.length(), 0);
				}
				/*finish off the message with a period*/
				send(s, "\r\n.\r\n\r\n", 7, 0);
				bytes_read = 0;
				bytes_read += recv(s, buff, 1, 0);
				bytes_read += recv(s, buff + 1, 1, 0);
				while((buff[bytes_read - 1] != '\n') && (buff[bytes_read - 2] != '\r')){
					bytes_read += recv(s, buff + bytes_read, 1, 0);
				}
			}
			else{
				free(buff);
				return;
			}

			/*if all went well, we get a 250*/
			if(strncmp(buff, "250", 3) == 0){
				free(buff);
				buff = (char *)malloc(1024 * sizeof(char));
				/*close the connection*/
				send(s, "QUIT\r\n", 6, 0);
			}
			else{
				free(buff);
				return;
			}
		}
	free(buff);
	WSACleanup();
	return;
}