/**
* Retrieve all messages by contributor.
*
* @param $contributor_id
* @return mixed
*/
public function retrieveMessages($contributor_id)
{
/*
* --------------------------------------------------------------------------
* Retrieve message list
* --------------------------------------------------------------------------
* Select conversation by contributor and find who is the first sender,
* just select data with marker column which has value 1, if sender is
* this contributor then is_available_sender must be 1, otherwise
* is_available_receiver must be 1, this marker as guard that the data
* is available for the contributor because if the value of marker depends
* on they are the first sender or not then the message or conversation has
* been deleted before, and in other situation another user who chat with
* must keep see the message, because we just update the marker 0 or 1,
* 0 mean deleted, 1 mean available.
*/
$sender = "\n SELECT\n conversations.message_id AS message_id,\n sender AS message_sender\n FROM conversations\n JOIN (SELECT message_id, MIN(created_at) AS timestamp FROM conversations GROUP BY conversations.message_id) dates_min\n ON conversations.message_id = dates_min.message_id\n AND created_at = dates_min.timestamp\n WHERE\n (sender = " . $contributor_id . " OR receiver = " . $contributor_id . ")\n ";
$conversation = Conversation::select(DB::raw('
id,
conversations.message_id AS message_id,
IF(sender = ' . $contributor_id . ', receiver, sender) AS interact_with,
message_sender,
message,
conversation_total,
conversations.created_at AS created_at'))->join(DB::raw('(SELECT message_id, COUNT(*) as conversation_total, MAX(created_at) AS timestamp FROM conversations GROUP BY message_id) dates'), function ($join) {
$join->on('conversations.message_id', '=', 'dates.message_id');
$join->on('created_at', '=', 'dates.timestamp');
})->join(DB::raw('(' . $sender . ') AS first_sender'), 'conversations.message_id', '=', 'first_sender.message_id')->whereRaw('(sender = ' . $contributor_id . ' OR receiver = ' . $contributor_id . ')')->whereRaw('IF(message_sender = ' . $contributor_id . ', is_available_sender, is_available_receiver) = 1 ')->orderBy('conversations.created_at', 'desc');
/*
* --------------------------------------------------------------------------
* Grouping at once
* --------------------------------------------------------------------------
* Group the conversation by message_id, it mean same result of group by
* contributor who talk with then select the partner of conversation,
* because we just show the opposite of us as contributor.
* message_sender is the one who sent email first.
*/
$contributor = Contributor::select(DB::raw("\n conversations.id, \n message_id, \n message_sender, \n contributors.id AS contributor_id,\n name, \n username,\n avatar,\n message,\n conversation_total,\n conversations.created_at"))->join(DB::raw("({$conversation->toSql()}) AS conversations"), 'conversations.interact_with', '=', 'contributors.id')->orderBy('conversations.created_at', 'desc')->paginate(10);
return $this->preMessageModifier($contributor);
}
/**
* Remove the specified message from database.
*
* @param Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy(Request $request, $id)
{
$message = Conversation::whereMessageId($id);
if (count($message->get()) == 0) {
abort(404);
}
$result = DB::transaction(function () use($request, $message, $id) {
try {
if ($request->input('sender') == $request->input('contributor_id')) {
$message->update(['is_available_sender' => 0]);
} else {
$message->update(['is_available_receiver' => 0]);
}
Conversation::whereIsAvailableSender(0)->whereIsAvailableReceiver(0)->delete();
$message = Message::find($id);
$conversations = $message->conversations()->count();
if (!$conversations) {
$message->delete();
}
return response()->json(['request_id' => uniqid(), 'status' => 'success', 'message' => Lang::get('alert.message.delete', ['receiver' => $request->input('contributor')]), 'timestamp' => Carbon::now()]);
} catch (\Exception $e) {
return response()->json(['request_id' => uniqid(), 'status' => 'failure', 'message' => Lang::get('alert.error.generic'), 'timestamp' => Carbon::now()], 500);
}
});
return $result;
}
/**
* Remove the specified message from database.
*
* @param Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy(Request $request, $id)
{
$message = Conversation::whereMessageId($id);
if (count($message->get()) == 0) {
abort(404);
}
$result = DB::transaction(function () use($request, $message, $id) {
try {
if ($request->input('sender') == Auth::user()->id) {
$message->update(['is_available_sender' => 0]);
} else {
$message->update(['is_available_receiver' => 0]);
}
Conversation::whereIsAvailableSender(0)->whereIsAvailableReceiver(0)->delete();
$message = Message::find($id);
$conversations = $message->conversations()->count();
if (!$conversations) {
$message->delete();
}
return redirect(route('account.message.list'))->with(['status' => 'danger', 'message' => Lang::get('alert.message.delete', ['receiver' => $request->input('contributor')])]);
} catch (\Exception $e) {
return redirect()->back()->withErrors(['error' => Lang::get('alert.error.transaction')])->withInput();
}
});
return $result;
}