<?php   
        include "db.php"; 
		$time = date("Y-m-d H:i:s");
		$chron_status = 'incomplete';
		$no_email_parsed = 0;
		$inv_rows_parsed = 0;
		$inv_rows_parsed_success = 0;
		$inv_rows_parsed_fail = 0;

		$set_message_query = "INSERT INTO client_pay_mail_cron_status SET status = '$chron_status',init_time = '$time'";
		$set_message_run = mysqli_query($conn, $set_message_query);
		$settle_email_chron_id = mysqli_insert_id($conn);

		$admin_id = 4;
		
		function clean_input_data($cellVal){
			$cellVal = trim($cellVal);
			$cellVal = strtolower($cellVal);
			$cellVal = str_replace(' ', '', $cellVal);
			return $cellVal;
		}  
		function clean_io_data($cellVal){
			$cellVal = trim($cellVal);
			$cellVal = str_replace('&nbsp;', '', $cellVal);
			$cellVal = str_replace(',', '', $cellVal);
			$cellVal = str_replace('$', '', $cellVal);
			return $cellVal;
		}
		$uniqueId = time() . '-' . mt_rand();
		
		require 'vendor1/autoload.php';
		require 'vendor2/autoload.php';
		use PHPHtmlParser\Dom;
	   
	    $mailbox = new PhpImap\Mailbox(
			'{imap.gmail.com:993/imap/ssl}INBOX',
			'payments@propertycpr.com', 
			'Paswerd112##',
			__DIR__,
			'UTF-8'
		);
		$email_array = array();
		
		$day_current = date("d-M-Y",time());
		$day_prev = date("d-M-Y",strtotime("-1 days"));
		
		$fetch_client_email_query = "SELECT * FROM client_pay_settle_email WHERE status IS NULL";
		$fetch_client_email_run = mysqli_query($conn, $fetch_client_email_query);
		
		if(mysqli_num_rows($fetch_client_email_run) > 0){
			while($fetch_client_email_row = mysqli_fetch_array($fetch_client_email_run)){
				$client_email = $fetch_client_email_row['email'];
				$client_email_id = $fetch_client_email_row['id'];
				array_push($email_array, array("email"=>"$client_email", "date"=>"$day_current"), array("email"=>"$client_email", "date"=>"$day_prev"));
			}
		}
		
		foreach($email_array as $email_arr){
			$search_email = $email_arr['email'];
			$search_date = $email_arr['date'];
			
		 try {
			
			$mails_ids = $mailbox->searchMailbox("FROM $search_email ON $search_date");
		
			} catch(PhpImap\Exceptions\ConnectionException $ex) {
				die();
			}
		
			$mailbox->setPathDelimiter('/');
		
			$mailbox->setServerEncoding('UTF-8');
		
			$mailbox->setAttachmentsIgnore(true);
			
			foreach($mails_ids as $mail_id) {
				$is_email_settle = 'unknown';
				$email = $mailbox->getMail(
					$mail_id, 
					false 
				);
		
				if($email->textHtml) {
					$email_text = $email->textHtml;
				
				}
				if(!isset($email_text)){
				    break;
				}
		
		$check_number = '';
		$check_date = '';
		$check_currency = '';
		$check_amount = '';	
		
		$dom = new Dom;
		
		$dom->load($email_text);
        
		for($i = 0; $i < 1000; $i++){
			$check_td= $dom->find('td')[$i];
			if(!isset($check_td)){
				continue;
		    }
			$check_p = $check_td->find('p')[0];
			if(!isset($check_p)){
			    continue;
		    }
			$check_s = $check_p->find('span')[0];
			if(!isset($check_s)){
				continue;
		    }
			$check_st = $check_s->find('b')[0];
			if(isset($check_st)){
				$check_st_text = $check_st->text;  
				$check_st_text = clean_input_data($check_st_text);
				if($check_st_text == "checknumber"){
					$check_no_td = $i;
					$check_no_td++;
					break;
				} 
			}			
		}

		if(isset($check_no_td)){
			$check_no_value_td= $dom->find('td')[$check_no_td];
			if(!isset($check_no_value_td)){
			    break;
		    }
			$check_no_value_p = $check_no_value_td->find('p')[0];
			if(!isset($check_no_value_p)){
			    break;
		    }
			$check_no_value_s = $check_no_value_p->find('span')[0];
			if(!isset($check_no_value_s)){
			    break;
		    }
			$check_no_value_s_text = trim($check_no_value_s->text); 
			if(!isset($check_no_value_s_text)){
			    break;
		    }
			if(strlen($check_no_value_s_text) > 0){
				$check_number = $check_no_value_s_text;

			}
		}

		for($i = 0; $i < 1000; $i++){
			$check_date_td= $dom->find('td')[$i];
			if(!isset($check_date_td)){
			    continue;
		    }
			$check_date_p = $check_date_td->find('p')[0];
			if(!isset($check_date_p)){
				continue;
		    }
			$check_date_s = $check_date_p->find('span')[0];
			if(!isset($check_date_s)){
				continue;
		    }
			$check_date_st = $check_date_s->find('b')[0];
			if(isset($check_date_st)){
				$check_date_st_text = $check_date_st->text;  
				if(!isset($check_date_st_text)){
					continue;
		        }
				$check_date_st_text = clean_input_data($check_date_st_text);
				if($check_date_st_text == "checkdate"){
					$check_date_td = $i;
					$check_date_td++;
					break;
				} 
			}			
		}

		if(isset($check_date_td)){
			$check_date_value_td= $dom->find('td')[$check_date_td];
			if(!isset($check_date_value_td)){
			         break;
		        }
			$check_date_value_p = $check_date_value_td->find('p')[0];
			if(!isset($check_date_value_p)){
			         break;
		        }
			$check_date_value_s = $check_date_value_p->find('span')[0];
			if(!isset($check_date_value_s)){
			         break;
		        }
			$check_date_value_s_text = trim($check_date_value_s->text);
			if(!isset($check_date_value_s_text)){
			         break;
		        }
			if(strlen($check_date_value_s_text) > 0){
				$check_date = $check_date_value_s_text;
			}
		}

		for($i = 0; $i < 1000; $i++){
			$check_currency_td= $dom->find('td')[$i];
				if(!isset($check_currency_td)){
			         continue;
		        }
			$check_currency_p = $check_currency_td->find('p')[0];
			if(!isset($check_currency_p)){
				continue;
		        }
			$check_currency_s = $check_currency_p->find('span')[0];	
			if(!isset($check_currency_s)){
				continue;
		        }
			$check_currency_st = $check_currency_s->find('b')[0];
			if(!isset($check_currency_st)){
				continue;
		        }
			if(isset($check_currency_st)){
				$check_currency_st_text = $check_currency_st->text;  
				if(!isset($check_currency_st_text)){
					continue;
		        }
				$check_currency_st_text = clean_input_data($check_currency_st_text);
				if($check_currency_st_text == "currency"){
					$check_currency_td = $i;
					$check_currency_td++;
					break;
				} 
			}			
		}

		if(isset($check_currency_td)){
			$check_currency_value_td= $dom->find('td')[$check_currency_td];
			if(!isset($check_currency_value_td)){
			         break;
		        }
			$check_currency_value_p = $check_currency_value_td->find('p')[0];
			if(!isset($check_currency_value_p)){
			         break;
		        }
			$check_currency_value_s = $check_currency_value_p->find('span')[0];
			if(!isset($check_currency_value_s)){
			         break;
		        }
			$check_currency_value_s_text = trim($check_currency_value_s->text); 
			if(!isset($check_currency_value_s_text)){
			         break;
		        }
			if(strlen($check_currency_value_s_text) > 0){
				$check_currency = $check_currency_value_s_text;
			}
		}

		for($i = 0; $i < 1000; $i++){
			$check_amount_td= $dom->find('td')[$i];
			if(!isset($check_amount_td)){
				continue;
		        }
			$check_amount_p = $check_amount_td->find('p')[0];
			if(!isset($check_amount_p)){
				continue;
		        }
			$check_amount_s = $check_amount_p->find('span')[0];			
			if(!isset($check_amount_s)){
				continue;
		        }
			$check_amount_st = $check_amount_s->find('b')[0];
			if(!isset($check_amount_st)){
				continue;
		        }
			if(isset($check_amount_st)){
				$check_amount_st_text = $check_amount_st->text;  
				if(!isset($check_amount_st_text)){
					continue;
		        }
				$check_amount_st_text = clean_input_data($check_amount_st_text);
				if($check_amount_st_text == "checkamount"){
					$check_amount_td = $i;
					$check_amount_td++;
					break;
				} 
			}			
		}

		if(isset($check_amount_td)){
			$check_amount_value_td= $dom->find('td')[$check_amount_td];
			if(!isset($check_amount_value_td)){
			         break;
		        }
			$check_amount_value_p = $check_amount_value_td->find('p')[0];
			if(!isset($check_amount_value_p)){
			         break;
		        }
			$check_amount_value_s = $check_amount_value_p->find('span')[0];
			if(!isset($check_amount_value_s)){
			         break;
		        }
			$check_amount_value_s_text = trim($check_amount_value_s->text); 
			if(!isset($check_amount_value_s_text)){
			         break;
		        }
			if(strlen($check_amount_value_s_text) > 0){
				$check_amount = $check_amount_value_s_text;
			}
		}

		for($i = 0; $i < 1000; $i++){
			$net_amount_td= $dom->find('td')[$i];
			if(!isset($net_amount_td)){
				continue;
		        }
			$net_amount_p = $net_amount_td->find('p')[0];
			if(!isset($net_amount_p)){
				continue;
		        }
			$net_amount_s = $net_amount_p->find('span')[0];	
			if(!isset($net_amount_s)){
				continue;
		        }
			$net_amount_st = $net_amount_s->find('b')[0];
			if(!isset($net_amount_st)){
				continue;
		        }
			if(isset($net_amount_st)){
				$net_amount_st_text = $net_amount_st->text; 
					if(!isset($net_amount_st_text)){
						continue;
		        }
				$net_amount_st_text = clean_input_data($net_amount_st_text);
				if($net_amount_st_text == "netamount"){
					$net_amount_td = $i;
					$net_amount_td++;
					break;
				} 
			}			
		}

		$row_init = $net_amount_td;

		if(isset($row_init)){
			for($row_init; $row_init < 100000; $row_init++){
				$inv_no = '';
				$description = '';
				$date = '';
				$loan_number = '';
				$settlement_amt = '';

				$inv_no_td = $dom->find('td')[$row_init];
				if(isset($inv_no_td)){
					$inv_no_p = $inv_no_td->find('p')[0];
					$inv_no_s = $inv_no_p->find('span')[0];
					$inv_no_s_text = clean_io_data($inv_no_s->text); 
					if(strlen($inv_no_s_text) > 0){
						$inv_no = $inv_no_s_text;						
					}else{
					break;
					}
				}else{
					break;
				}
				$row_init++;
				
				$description_td = $dom->find('td')[$row_init];
				$description_p = $description_td->find('p')[0];
				if(isset($description_p)){
					$description_s = $description_p->find('span')[0];
					$description_s_text = clean_io_data($description_s->text); 
					if(strlen($description_s_text) > 0){
						$description = $description_s_text;
					}
				}
				$row_init++;
				$date_td = $dom->find('td')[$row_init];
				$date_p = $date_td->find('p')[0];
				if(isset($date_p)){
					$date_s = $date_p->find('span')[0];
					$date_s_text = clean_io_data($date_s->text); 
					if(strlen($date_s_text) > 0){
						$date = $date_s_text;
					}
				}
				$row_init++;
				$loan_number_td = $dom->find('td')[$row_init];
				$loan_number_p = $loan_number_td->find('p')[0];
				if(isset($loan_number_p)){
					$loan_number_s = $loan_number_p->find('span')[0];
					$loan_number_s_text = clean_io_data($loan_number_s->text); 
					if(strlen($loan_number_s_text) > 0){
						$loan_number = $loan_number_s_text;
					}
				}
				
				$row_init = $row_init + 2;
				
				$settlement_charge_td = $dom->find('td')[$row_init];
				$settlement_charge_p = $settlement_charge_td->find('p')[0];
				if(isset($settlement_charge_p)){
					$settlement_charge_s = $settlement_charge_p->find('span')[0];
					$settlement_charge_s_text = clean_io_data($settlement_charge_s->text); 
					if(strlen($settlement_charge_s_text) > 0){
						$settlement_charge = $settlement_charge_s_text;
					}
				}

				$charge_query = '';
				if(isset($settlement_charge)){
					if(strlen($settlement_charge) > 0){
						$charge_query = ", charge = $settlement_charge";
					}
				}

				$row_init++;

				$settlement_amt_td = $dom->find('td')[$row_init];
				$settlement_amt_p = $settlement_amt_td->find('p')[0];
				if(isset($settlement_amt_p)){
					$settlement_amt_s = $settlement_amt_p->find('span')[0];
					$settlement_amt_s_text = clean_io_data($settlement_amt_s->text); 
					if(strlen($settlement_amt_s_text) > 0){
						$settlement_amt = $settlement_amt_s_text;
					}
				}
				$inv_no = str_replace('_DED', '', $inv_no);
                $inv_no = str_replace('_ded', '', $inv_no);

				if (strlen($inv_no) > 0 && strlen($check_number) > 0 && strlen($settlement_amt) > 0) {
					$inv_rows_parsed++;
					$pay_ref = $check_number;
					$invoice_amount = $settlement_amt;
					$pay_date = $check_date;	
					$pay_date = strtotime($pay_date);
					$pay_date = date("Y-m-d H:i:s", $pay_date);	

					if(strlen($loan_number) > 0){
						$prop_param = $loan_number;
						$prop_param = strtolower($prop_param);
						$prop_type = 'loan';
					}elseif(strlen($description) > 0){
						$prop_param = $description;
						$prop_param = strtolower($prop_param);
						$prop_type = 'prop_full_add';
					}else{
						$prop_type = 'none';
						$prop_set = 1;
					}
					$fetch_client_detail_query = "SELECT * FROM client_pay_settle_email WHERE email = '$search_email' AND status IS NULL";
					$fetch_client_detail_run = mysqli_query($conn,$fetch_client_detail_query);
					if(mysqli_num_rows($fetch_client_detail_run) == 1){
						$fetch_client_detail_row = mysqli_fetch_array($fetch_client_detail_run);
						$fetched_client_id = $fetch_client_detail_row['client_id'];							
					}
					$check_invoice_query = "SELECT * FROM invoice WHERE invoice_no = '$inv_no' AND is_delete IS NULL AND is_approve = 1";
					$check_invoice_run = mysqli_query($conn, $check_invoice_query);

					if(mysqli_num_rows($check_invoice_run) == 1){
						$check_invoice_row = mysqli_fetch_array($check_invoice_run);
						$int_property_id = $check_invoice_row['prop_id'];
						$invoice_id = $check_invoice_row['invoice_id'];
						$prev_vendor_pay_amount = $check_invoice_row['client_pay'];
						$int_vendor_pay_amount = $check_invoice_row['vendor_pay'];
						if($prop_type != 'none'){
							$check_property_query = "SELECT * FROM property WHERE prop_id = $int_property_id";
							$check_property_run = mysqli_query($conn, $check_property_query);

							if (mysqli_num_rows($check_property_run) == 1) {
								$check_property_row = mysqli_fetch_array($check_property_run);
								if($prop_type == 'loan'){
									$int_loan_no = $check_property_row['prop_case_num'];
									$int_loan_no = strtolower($int_loan_no);
									if ($int_loan_no == $prop_param) {
										$prop_set = 1;
									}
								}elseif($prop_type == 'prop_full_add'){
									$int_prop_full_add = $check_property_row['prop_full_add'];
									$int_prop_full_add = strtolower($int_prop_full_add);
									if ($int_prop_full_add == $prop_param) {
										$prop_set = 1;
									}
								}
							}
						}
						
						$fetch_client_detail_query = "SELECT * FROM client_pay_settle_email WHERE email = '$search_email' AND status IS NULL";
						$fetch_client_detail_run = mysqli_query($conn,$fetch_client_detail_query);
						if(mysqli_num_rows($fetch_client_detail_run) == 1){
							$fetch_client_detail_row = mysqli_fetch_array($fetch_client_detail_run);
							$fetched_client_id = $fetch_client_detail_row['client_id'];							
						}
						$client_set = 0;
						
						$int_client_id = $check_invoice_row['client_id'];
						if($int_client_id == $fetched_client_id) {
							$client_set = 1;
						}
						
						if ($prop_set == 1 && $client_set == 1 && strlen($pay_ref) > 0) {
							$check_vendor_pay_query = "SELECT * FROM client_pay_settle WHERE invoice_id = $invoice_id AND ref_no = '$pay_ref'";
							$check_vendor_pay_run = mysqli_query($conn, $check_vendor_pay_query);
							if (mysqli_num_rows($check_vendor_pay_run) > 0) {
								$check_vendor_pay_row = mysqli_fetch_array($check_vendor_pay_run);
								$vendor_pay_id = $check_vendor_pay_row['id'];
								$vendor_amount_prev = $check_vendor_pay_row['amount'];
								$vendor_status = $check_vendor_pay_row['status'];
								$vendor_transact_id = $check_vendor_pay_row['transact_id'];
								if ($vendor_transact_id == $uniqueId && $vendor_status == 0) {
									$invoice_amount_new = $prev_vendor_pay_amount + $invoice_amount;
									$email_text_escaped = mysqli_real_escape_string($conn, $email_text);
									$check_vendor_pay_query = "INSERT INTO client_pay_settle SET amount = $invoice_amount, invoice_id = $invoice_id, ref_no = '$pay_ref', pay_date = '$pay_date',create_time = '$time', transact_id = '$uniqueId', status = 0, type = 'email', client_email_id = '$search_email', email_data = '$email_text_escaped' $charge_query";
									$check_vendor_pay_run_change = mysqli_query($conn, $check_vendor_pay_query);
								} 
								
							} elseif (mysqli_num_rows($check_vendor_pay_run) == 0) {
								$invoice_amount_new = $prev_vendor_pay_amount + $invoice_amount;
                                $email_text_escaped = mysqli_real_escape_string($conn, $email_text);
								$check_vendor_pay_query = "INSERT INTO client_pay_settle SET amount = $invoice_amount, invoice_id = $invoice_id, ref_no = '$pay_ref', pay_date = '$pay_date',create_time = '$time', transact_id = '$uniqueId', status = 0, type = 'email', client_email_id = '$search_email', email_data = '$email_text_escaped' $charge_query";
								$check_vendor_pay_run_change = mysqli_query($conn, $check_vendor_pay_query);
							}
							if ($int_vendor_pay_amount  > $invoice_amount_new) {
								$time = date("Y-m-d H:i:s");
								$alert_string = "Client Pay less than Vendor Pay in Invoice# $inv_no.";
								$query_cat_verify_alert = "INSERT INTO admin_alert SET admin_alert_id = NULL,admin_alert_time = '$time', admin_alert_link = 'invoice_finalize.php?in_no=$invoice_id',admin_id= $admin_id, admin_alert_activity = '$alert_string'";
								$run_cat_verify_alert = mysqli_query($conn, $query_cat_verify_alert);
							}else{
								$temp_diff = $invoice_amount_new - $int_vendor_pay_amount;
								$temp_diff_frac = $temp_diff / $invoice_amount_new;
								$temp_diff_frac = $temp_diff_frac * 100;
								if ($temp_diff_frac < 40) {
									$alert_string = "Profit Less than 40 in Invoice# $inv_no.";
									$query_cat_verify_alert = "INSERT INTO admin_alert SET admin_alert_id = NULL,admin_alert_time = '$time', admin_alert_link = 'invoice_finalize.php?in_no=$invoice_id',admin_id= $admin_id, admin_alert_activity = '$alert_string'";
									$run_cat_verify_alert = mysqli_query($conn, $query_cat_verify_alert);
								}
							}

							if($check_vendor_pay_run_change){
								$insert_detail_query = "UPDATE invoice SET client_pay = $invoice_amount_new, is_settle = 1 WHERE invoice_no = '$inv_no'";
								$insert_detail_run = mysqli_query($conn, $insert_detail_query);
								if (mysqli_affected_rows($conn) == 1) {
									$inv_rows_parsed_success++;
								}
							}
						} else {
							$inv_rows_parsed_fail++;
							$invoice_client_name = '';
							$fetch_invoice_client_name_query = "SELECT * FROM client WHERE client_id = $int_client_id AND client_is_delete IS NULL";
							$fetch_invoice_client_name_run = mysqli_query($conn, $fetch_invoice_client_name_query);
							if(mysqli_num_rows($fetch_invoice_client_name_run)==1){
								$fetch_invoice_client_name_row = mysqli_fetch_array($fetch_invoice_client_name_run);
								$invoice_client_name = $fetch_invoice_client_name_row['client_name'];
							}

							$message = "Incorrect details filled.<br>Invoice System Client:$invoice_client_name<br>Property Detail in Email:$prop_param";
							$check_alert_exist_query = "SELECT * FROM client_email_pay_settle_fail WHERE invoice_no = '$inv_no'";
							$check_alert_exist_run = mysqli_query($conn, $check_alert_exist_query);
							if(mysqli_num_rows($check_alert_exist_run) == 0){
								
								$insert_alert_exist_query = "INSERT INTO client_email_pay_settle_fail SET invoice_no = '$inv_no', message = '$message', time = '$time', client_id = $fetched_client_id, client_email_id = '$search_email'";
								$insert_alert_exist_run = mysqli_query($conn, $insert_alert_exist_query);
								$email_text_escaped = mysqli_real_escape_string($conn, $email_text);
								$insert_alert_exist_insert_id = mysqli_insert_id($conn);
								$update_alert_exist_query = "UPDATE client_email_pay_settle_fail SET email_data = '$email_text_escaped' WHERE id = $insert_alert_exist_insert_id";
								$update_alert_exist_run = mysqli_query($conn, $update_alert_exist_query);


								}
						}
					}else{
						$inv_rows_parsed_fail++;						 
						$message = "Invoice does not exist.";
						$check_alert_exist_query = "SELECT * FROM client_email_pay_settle_fail WHERE invoice_no = '$inv_no'";
						$check_alert_exist_run = mysqli_query($conn, $check_alert_exist_query);
						if(mysqli_num_rows($check_alert_exist_run) == 0){
							$insert_alert_exist_query = "INSERT INTO client_email_pay_settle_fail SET invoice_no = '$inv_no', message = '$message', time = '$time', client_id = $fetched_client_id, client_email_id = '$search_email'";
							$insert_alert_exist_run = mysqli_query($conn, $insert_alert_exist_query);
							$email_text_escaped = mysqli_real_escape_string($conn, $email_text);
							$insert_alert_exist_insert_id = mysqli_insert_id($conn);
							$update_alert_exist_query = "UPDATE client_email_pay_settle_fail SET email_data = '$email_text_escaped' WHERE id = $insert_alert_exist_insert_id";
							$update_alert_exist_run = mysqli_query($conn, $update_alert_exist_query);
						}
					}
				}
			}
	}
	                $set_status_query = "UPDATE client_pay_settle SET status = 1 WHERE transact_id = '$uniqueId'";
                    $set_status_run = mysqli_query($conn, $set_status_query);

					$no_email_parsed++;
}
		}
		
		
		
$mailbox->disconnect();	
$close_time = date("Y-m-d H:i:s");
$chron_status = 'success';

$set_message_query = "UPDATE client_pay_mail_cron_status SET end_time = '$close_time', status = '$chron_status', email_no_parsed = $no_email_parsed,inv_parsed = $inv_rows_parsed, inv_parsed_success = $inv_rows_parsed_success, inv_parsed_fail = $inv_rows_parsed_fail WHERE id = $settle_email_chron_id";
$set_message_run = mysqli_query($conn, $set_message_query);

exit;


