- I have set-up DB connection and fetching several data from my DB.
- All datas are well displayed in my twig.
- I have set-up connection to github.
- I am fetching issues from Github.
- I have set-up comparison helpers + conditions to fetch some data from Github and attach value to my objects from DB.
- Comparison and direct issue-title fetch doesn’t work as expected and I can’t find a way to do so, I am about to ask how to successfully retrieve wanted data.
Code:
<?php
// Function to fetch emails based on search criteria
function fetchEmails($currentPage, $itemsPerPage, $sortColumn, $sortOrder, $searchField, $searchType, $con_arr) {
try {
$con_arr = connectDatabase($con_arr);
// Calculate pagination offset
$offset = ($currentPage - 1) * $itemsPerPage;
// Validate sort column
$validSortColumns = ['Datum', 'Souborů', 'Issue'];
$sortColumn = in_array($sortColumn, $validSortColumns) ? $sortColumn : 'Datum';
// Validate sort order
$validSortOrders = ['ASC', 'DESC'];
$sortOrder = in_array($sortOrder, $validSortOrders) ? $sortOrder : 'DESC';
// Map sort column to database column
$sortColumnMapping = [
'Datum' => 'e.sentDateTime',
'Souborů' => 'content_files_number',
'Issue' => 'e.issue_number'
];
$dbSortColumn = $sortColumnMapping[$sortColumn];
// Initialize search conditions
$searchCondition = '';
$parameters = [];
// Handle search field and type
if ($searchField !== '') {
// Escape and prepare search field
$searchFieldEscaped = '%' . addcslashes(mb_strtolower($searchField, 'UTF-8'), "%_") . '%';
$parameters[] = $searchFieldEscaped;
// Build search condition based on search type
if ($searchType === 'fromEmail') {
$searchCondition = "AND LOWER(e.fromEmail) LIKE ?";
} else {
$searchCondition = "AND LOWER(e.subject) LIKE ?";
}
}
// Fetch total count of emails
$totalEmailsCount = getTotalEmailsCount($searchCondition, $parameters, $con_arr);
// Calculate total pages for pagination
$totalPages = ($itemsPerPage > 0) ? ceil($totalEmailsCount / $itemsPerPage) : 1;
// Fetch emails and associated files from the database
$query = "
SELECT e.*,
GROUP_CONCAT(f.name SEPARATOR ', ') AS content_name,
COUNT(f.id) AS content_files_number
FROM sd_email e
LEFT JOIN sd_email_file f ON e.id = f.email_id
WHERE 1=1 $searchCondition
GROUP BY e.id
ORDER BY $dbSortColumn $sortOrder
LIMIT ?, ?
";
$stmt = mysqli_prepare($con_arr['link'], $query);
// Check for errors in preparing the statement
if (!$stmt) {
throw new Exception('Failed to prepare statement: ' . mysqli_error($con_arr['link']));
}
// Bind parameters
if (!empty($searchCondition)) {
mysqli_stmt_bind_param($stmt, 'sii', $parameters[0], $offset, $itemsPerPage);
} else {
mysqli_stmt_bind_param($stmt, 'ii', $offset, $itemsPerPage);
}
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$emailsQuery = mysqli_fetch_all($result, MYSQLI_ASSOC);
mysqli_stmt_close($stmt);
$github = new GitHubClient();
if (is_array($emailsQuery) && !empty($emailsQuery)) {
foreach ($emailsQuery as &$email) {
if (empty($email['issue_number'])) {
// Search GitHub issues with specific format using subject as keyword
$github_issues = $github->search_github_issues('email: ' . $email['subject'], $email['id']);
if (!empty($github_issues['items'])) {
foreach ($github_issues['items'] as $issue) {
// Extract email ID and issue ID from the issue title
if (preg_match('/^(d+) - email: (.+) #(d+)$/', $issue['title'], $matches)) {
$issue_email_id = $matches[1];
$issue_subject = $matches[2];
$issue_id = $matches[3];
// Check if the email ID matches the current email ID
if ($issue_email_id == $email['id'] && stripos($issue_subject, $email['subject']) !== false) {
$email['issue_number'] = $issue_id;
break;
}
}
}
} else {
$email['issue_number'] = null;
}
}
if (!empty($email['issue_number'])) {
$comments = $github->get_comments_github($email['issue_number']);
foreach ($comments as $comment) {
$similarity = calculate_cosine_similarity($email['body'], $comment);
if ($similarity >= 0.4) {
$email['github_issue_link'] = generateGitHubLink($email);
break;
}
}
}
if (empty($email['github_issue_link'])) {
$email['github_issue_link'] = generateGitHubLink($email);
}
}
} else {
logErrorAndRespond('No emails found or error fetching emails.');
}
return [
'emails' => $emailsQuery,
'total_emails_count' => $totalEmailsCount,
'total_pages' => $totalPages
];
} catch (Exception $ex) {
logErrorAndRespond($ex->getMessage());
}
}
// Function to calculate cosine similarity
function calculate_cosine_similarity($text1, $text2) {
// Check if $text1 is an array and extract the relevant text
if (is_array($text1)) {
$text1 = isset($text1['body']) ? $text1['body'] : '';
}
// Check if $text2 is an array and extract the relevant text
if (is_array($text2)) {
$text2 = isset($text2['body']) ? $text2['body'] : '';
}
// Ensure $text1 and $text2 are strings before calling text_to_vector()
if (!is_string($text1) || !is_string($text2)) {
throw new InvalidArgumentException('Both arguments must be strings.');
}
$vector1 = text_to_vector($text1);
$vector2 = text_to_vector($text2);
$dot_product = array_sum(array_map(function($x, $y) { return $x * $y; }, $vector1, $vector2));
$magnitude1 = sqrt(array_sum(array_map(function($x) { return $x * $x; }, $vector1)));
$magnitude2 = sqrt(array_sum(array_map(function($x) { return $x * $x; }, $vector2)));
if ($magnitude1 * $magnitude2 == 0) {
return 0;
} else {
return $dot_product / ($magnitude1 * $magnitude2);
}
}
function text_to_vector($text) {
$words = explode(' ', strtolower($text));
$vector = array_count_values($words);
return $vector;
}
// Function to get total count of emails
function getTotalEmailsCount($searchCondition, $parameters, $con_arr)
{
$query = "
SELECT COUNT(*) AS total_count
FROM sd_email e
WHERE 1=1 $searchCondition
";
$stmt = mysqli_prepare($con_arr['link'], $query);
// Check for errors in preparing the statement
if (!$stmt) {
throw new Exception('Failed to prepare statement: ' . mysqli_error($con_arr['link']));
}
// Bind parameters if there is a search condition
if (!empty($parameters)) {
mysqli_stmt_bind_param($stmt, 's', $parameters[0]);
}
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $totalEmailsCount);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
return $totalEmailsCount;
}
// Function to generate GitHub link based on issue_number from the email
function generateGitHubLink($email)
{
$GITHUB_URL = 'https://github.com';
if (isset($email['issue_number']) && $email['issue_number']) {
$issue_number = $email['issue_number'];
$url = "$GITHUB_URL/issues/{$issue_number}";
return "<a href="$url">github/{$issue_number}</a>";
} else {
return ''; // Handle case where no issue_number is available
}
}
// Function to log error and respond
function logErrorAndRespond($errorMessage)
{
error_log('Error: ' . $errorMessage);
echo json_encode(['error' => $errorMessage]);
exit;
}
// Main execution logic
try {
// Pagination, sorting, and search parameters
$defaultItemsPerPage = 20;
$itemsPerPage = isset($_GET['itemsPerPage']) ? intval($_GET['itemsPerPage']) : $defaultItemsPerPage;
$currentPage = isset($_GET['page']) ? intval($_GET['page']) : 1;
$sortColumn = isset($_GET['sortColumn']) ? $_GET['sortColumn'] : 'Datum';
$sortOrder = isset($_GET['sortOrder']) ? $_GET['sortOrder'] : 'DESC';
$searchField = isset($_GET['searchField']) ? $_GET['searchField'] : '';
$searchType = isset($_GET['searchType']) ? $_GET['searchType'] : 'fromEmail';
// Fetch emails with pagination and search
$result = fetchEmails($currentPage, $itemsPerPage, $sortColumn, $sortOrder, $searchField, $searchType, $con_arr);
// Set up Twig for templating
$templatesDir = __DIR__ . '/templates';
$loader = new FilesystemLoader($templatesDir);
$twig = new Environment($loader);
// Prepare data for Twig rendering
$data = [
'emails' => isset($result['emails']) ? $result['emails'] : [],
'current_page' => $currentPage,
'items_per_page' => $itemsPerPage,
'total_pages' => isset($result['total_pages']) ? $result['total_pages'] : 1,
'total_emails_count' => isset($result['total_emails_count']) ? $result['total_emails_count'] : 0,
'sort_column' => $sortColumn,
'sort_order' => $sortOrder,
'searchField' => $searchField,
'searchType' => $searchType
];
// Render Twig template
echo $twig->render('emails.twig', $data);
} catch (Exception $ex) {
logErrorAndRespond('An error occurred');
}
?>
Github email based issue titles usually looks like this: 42 - email: Re: chyba mobilu #857 where 42 is email id from DB, Re: chyba mobilu is subject from DB, and #857 is issue_number.
So to get issue_numbers to be able create a link to it, I tried to fetch comment body from issues and even tried to fetch issue titles.
But somehow it loops over all issues within GitHub, but don’t give any issue_number related to email even when I find it manually there….
PS: github address is correct in my code, was changed just there….
New contributor
PBT is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.