public function getMinutesLeftAttribute()
{
$company_id = $this->attributes['company_id'];
$worklog_hours = Werklog::selectRaw('SUM(minutes) AS minutes')->where('company_id', $company_id)->where('strippenkaarten_id', $this->id)->first();
$minutes_left = 60 * $this->hours - ($worklog_hours->minutes ?: 0);
//Debugbar::info($worklog_hours);
return $minutes_left;
}
/**
* [getData - datatables response]
* @return [json] [DT compatible object]
*/
public function getData($model = null)
{
$Model = $this->modelName;
$num_skip = 0;
$num_items = 10;
$recordsTotal = 0;
$recordsFiltered = 0;
// check get vars
if (isset($_GET['start'])) {
$num_skip = (int) $_GET['start'];
}
if (isset($_GET['length'])) {
$num_items = (int) $_GET['length'];
}
if (isset($_GET['search'])) {
$search_value = $_GET['search']['value'];
}
$all_werklogs = Werklog::orderBy('worklogs.id', 'DESC');
if ($model !== null) {
$all_werklogs->where('worklogs.company_id', '=', (int) $model->id);
if (!empty($search_value)) {
$all_werklogs->join('projects', 'worklogs.project_id', '=', 'projects.id')->whereRaw("(projects.name LIKE '%" . $search_value . "%')");
}
} else {
if (!empty($search_value)) {
$all_werklogs->join('companies', 'worklogs.company_id', '=', 'companies.id')->join('projects', 'worklogs.project_id', '=', 'projects.id')->whereRaw("(projects.name LIKE '%" . $search_value . "%' OR companies.bedrijfsnaam LIKE '%" . $search_value . "%')");
}
}
$recordsTotal = $all_werklogs->count();
$recordsFiltered = $recordsTotal;
if ($num_skip > 0) {
$all_werklogs->skip($num_skip);
}
$all_werklogs = $all_werklogs->take($num_items)->get();
$data = [];
$i = 0;
foreach ($all_werklogs as $werklog) {
// load relations
$load_curr_company = $werklog->company;
$load_curr_project = $werklog->project;
$load_curr_user = $werklog->user;
$load_curr_strippenkaart = $werklog->strippenkaart;
$curr_worklog = $werklog;
// Encoding example from the Zanussi App (remove weird strings and prepare the string for json encoding)
// htmlspecialchars(htmlspecialchars_decode($post['post_title'], ENT_NOQUOTES),ENT_NOQUOTES | ENT_HTML5 | ENT_SUBSTITUTE)
$curr_company = $werklog->company !== NULL ? (object) ['id' => $werklog->company_id, 'bedrijfsnaam' => utf8_encode($werklog->company->bedrijfsnaam)] : (object) null;
$curr_proj = $werklog->project !== NULL ? (object) ['id' => $werklog->project_id, 'name' => utf8_encode($werklog->project->name)] : (object) null;
$curr_strip = $werklog->strippenkaart !== NULL ? (object) ['id' => $werklog->strippenkaarten_id, 'hours' => $werklog->strippenkaart->hours] : (object) null;
$curr_user = $werklog->user !== NULL ? (object) ['id' => $werklog->user_id, 'username' => utf8_encode($werklog->user->username)] : (object) null;
$data[] = (object) ['DT_RowId' => 'row_' . $werklog->id, 'worklogs' => $curr_worklog, 'users' => $curr_user, 'companies' => $curr_company, 'projects' => $curr_proj, 'strippenkaarten' => $curr_strip];
}
$ret = ['recordsTotal' => $recordsTotal, 'recordsFiltered' => $recordsFiltered, 'data' => $data, 'companies' => $this->getAllCompanies(), 'projects' => $this->getAllProjects(), 'users' => $this->getAllUsers(), 'strippenkaarten' => $this->getAllStrippenkaarten()];
return Response::json($ret);
}
/**
* [store - jira weebhook handler]
* @return [void] []
*/
public function store()
{
$post_data = file_get_contents("php://input");
mail('*****@*****.**', 'jira_posted_data', $post_data);
$reply = json_decode($post_data);
$status_id = $reply->issue->fields->status->id;
//error_log('status_id: '.$status_id);
if ($status_id === Config::get('eenvoudcrm.jira_status_closed') || $status_id === Config::get('eenvoudcrm.jira_status_done')) {
mail('*****@*****.**', 'jira_new_worklog', $post_data);
$worklog = new Werklog();
$worklog->description = 'Issue ' . $reply->issue->id . ' - ' . $reply->issue->fields->summary;
$worklog->minutes = (int) $reply->issue->fields->timetracking->timeSpentSeconds / 60.0;
$worklog->date = date("Y-m-d");
$worklog->billable = 1;
$jira_username = $reply->user->name;
$user = User::whereRaw("username like '%" . $jira_username . "%'")->first();
if ($user) {
$worklog->user_id = $user->id;
} else {
$worklog->user_id = Config::get('eenvoudcrm.default_user_id');
}
$jira_id = $reply->issue->fields->project->id;
$project = Project::where('jira_id', '=', $jira_id)->first();
if ($project) {
$worklog->company_id = $project->company_id;
$worklog->project_id = $project->id;
} else {
$worklog->company_id = Config::get('eenvoudcrm.default_company_id');
$worklog->project_id = Config::get('eenvoudcrm.default_project_id');
}
try {
$worklog->save();
} catch (Exception $e) {
error_log(json_encode($e));
}
}
}
/**
* [processRoadmapWorklogs - process withstanding roadmap logs]
* @return [array] [the roadmap entries]
*/
public static function processRoadmapWorklogs()
{
// dates
$today = date("Ymd");
$yesterday = date("Ymd", strtotime("yesterday"));
// time entries
$roadmap_project_id = Config::get('eenvoudcrm.main_roadmap_project_id');
list($worklog_entries_status, $worklog_entries) = IntegrationRoadmapController::getProjectWorklogEntries($roadmap_project_id, $yesterday, $today);
if ($worklog_entries_status) {
// foreach of the entries create a new worklog and save it
foreach ($worklog_entries as $key => $value) {
// check if this ID is unique
$roadmap_id = $value->ID;
$roadmap_worklogs = Werklog::where('roadmap_id', '=', $roadmap_id)->first();
if (!$roadmap_worklogs) {
$worklog = new Werklog();
$date = explode("T", $value->Date, 2);
$worklog->date = $date[0];
$worklog->roadmap_id = (int) $value->ID;
// default project and company ID
$worklog->project_id = null;
$worklog->company_id = Config::get('eenvoudcrm.default_company_id');
//mail('*****@*****.**', 'resource_id', $value->Resource->ID);
$user = User::where('roadmap_resource_id', '=', $value->Resource->ID)->first();
if ($user) {
$worklog->user_id = $user->id;
} else {
$worklog->user_id = Config::get('eenvoudcrm.default_user_id');
}
$worklog->minutes = round((double) $value->Time * 60.0);
$worklog->description = $value->Description;
$worklog->billable = 1;
$worklog->processed = 0;
$worklog->save();
} else {
error_log('Roadmap worklogs: Skipping duplicated entry ' . $roadmap_id);
}
}
return $worklog_entries;
}
return false;
}
/**
* [invoiceClient - append selected entries to a MB invoice if one exists. Otherwise create a new one]
* @param [array] $all_company_values [segmented data company row]
* @param [int] $company_id [description]
* @param [string] $op_type [invoice/invoice_clear + strip/strip_clear]
* @return [mb invoice] [MB invoice that was used]
*/
public function invoiceClient($all_company_values, $company_id, $op_type)
{
error_log('Invoicing client');
// create invoice details obj
//$customer_id = Config::get('eenvoudcrm.moneybird_test_user_id');
$customer_id = $this->getContact($company_id);
if ($customer_id === -1) {
return false;
}
error_log('Local Contact Exists');
$contact = null;
try {
$contact = $this->getRequest('contacts/' . $customer_id . '.json');
} catch (Exception $e) {
error_log($e->getMessage());
die;
}
if (!$contact) {
error_log("Consistency check failure: contact not found");
return false;
}
error_log('Remote Contact Exists');
// check for open invoices
try {
$open_invoices = $this->getRequest('sales_invoices.json?filter=state:draft,contact_id:' . $customer_id);
} catch (Exception $e) {
error_log($e->getMessage());
die;
}
//$invoice = null;
$details = [];
$new_invoice = false;
if (count($open_invoices) > 0) {
$used_invoice_id = -1;
error_log('found an open invoice for ' . $customer_id . ' - ' . json_encode($open_invoices[0]));
//$used_invoice_id = 135871568029943606;
if (isset($open_invoices[0]->id)) {
$used_invoice_id = $open_invoices[0]->id;
}
if ($used_invoice_id === -1) {
$error_msg = 'Error: IntegrationMoneybirdController2 - Found open invoices but could not get invoice id';
throw new Exception($error_msg);
error_log($error_msg);
die;
}
// sanity check - try getting the first invoice draft
// try {
// $invoice = $this->getRequest('sales_invoices/'.$used_invoice_id.'.json');
// } catch(Exception $e) {
// error_log($e->getMessage());
// die();
// }
} else {
$new_invoice = true;
}
// get price and description
if ($op_type === 'subscription') {
// append details
$segmented_by_service = $this->segmentDataByService($all_company_values);
foreach ($segmented_by_service as $service_ndx => $service_values) {
$service = Service::find((int) $service_ndx);
// load relations
$load_cat_relation = $service->category;
$service_line = "\r\n*" . $service->category->name . " - " . $service->name . "*";
// invoice details line 1
foreach ($service_values as $key => $company_row_values) {
$curr_price = 0;
$curr_description = '';
$item_id = (int) $company_row_values['subscriptions.id'];
$subscription = Subscription::find($item_id);
$service = $subscription->service;
$service_category = $service->category;
$periodicity = $subscription->invoice_periods_id;
$interval_size = 1;
$periodicity_str = "";
switch ($periodicity) {
case 1:
$periodicity_str = "jaar";
$interval_size = 12;
break;
case 2:
$periodicity_str = "kwartjaar";
$interval_size = 4;
break;
case 3:
$periodicity_str = "kwartaal";
$interval_size = 3;
break;
case 4:
$periodicity_str = "mnd";
$interval_size = 1;
break;
default:
break;
}
// avoid MB errors dues to ammount too large
if ($subscription->subscription_start === '0000-00-00' || $subscription->subscription_end === '000-00-00') {
continue;
}
$start_date = new DateTime($subscription->subscription_start);
$end_date = new DateTime($subscription->subscription_end);
$end_date->add(new DateInterval('P1D'));
$num_months = $start_date->diff($end_date)->m + $start_date->diff($end_date)->y * 12;
$num_items = floatval($num_months) / floatval($interval_size);
if ($interval_size > $num_months) {
$num_items = $num_months;
$periodicity_str = "maand";
}
// calc price depending on whether item is singular or periodic
$curr_price = round((double) $subscription->price, 2);
$curr_description = ($subscription->description ? "{$subscription->description}" : "") . " _({$subscription->subscription_start} - {$subscription->subscription_end})_";
// invoice details line 1
$details[] = (object) ['amount' => "{$num_items} x {$periodicity_str}", 'description' => $curr_description, 'price' => $curr_price, 'tax_rate_id' => Config::get('eenvoudcrm.taxrate_high_id'), 'ledger_account_id' => Config::get('eenvoudcrm.ledger_account_id')];
}
}
} elseif ($op_type === 'worklog') {
$worklogs_line = "\r\n*Werklogs*";
// invoice details line 1
$details[] = (object) ['description' => $worklogs_line];
foreach ($all_company_values as $company_row_ndx => $company_row_values) {
$curr_price = 0;
$curr_description = '';
$item_id = (int) $company_row_values['worklogs.id'];
$worklog = Werklog::find($item_id);
// round minutes to upper quarter of the hour
$round_minutes = floor($worklog->minutes / 15.0) * 15 + ($worklog->minutes % 15 > 0 ? 15 : 0);
$dec_hours = (double) $round_minutes / 60.0;
// calc total price
$curr_price = (double) Config::get('eenvoudcrm.worklog_price_per_hour') * $dec_hours;
$hours = floor($dec_hours);
$minutes = round(60.0 * ($dec_hours - $hours));
$curr_description = "{$worklog->date} - (" . ($hours > 0 ? "{$hours} h " : "") . "{$minutes} m)" . ($worklog->description ? " - {$worklog->description}" : "");
if ($worklog->comment) {
$curr_description .= "\r\n[{$worklog->comment}]";
}
// invoice details line 1
$details[] = (object) ['amount' => '1 x', 'description' => $curr_description, 'price' => $curr_price, 'tax_rate_id' => Config::get('eenvoudcrm.taxrate_high_id'), 'ledger_account_id' => Config::get('eenvoudcrm.ledger_account_id')];
}
}
$details_line = '{"sales_invoice":{"details_attributes":' . json_encode((object) $details) . '}}';
$ret_invoice = null;
// save
try {
if (!$new_invoice) {
$ret_invoice = $this->patchRequest('sales_invoices/' . $used_invoice_id . '.json', $details_line);
} else {
$invoice = '{"sales_invoice":{"reference":"","contact_id":' . $customer_id . ',"details_attributes":' . json_encode((object) $details) . '}}';
$ret_invoice = $this->postRequest('sales_invoices.json', $invoice);
}
} catch (Exception $e) {
error_log($e->getMessage());
die;
}
return $ret_invoice;
}
public function worklogsReporting()
{
// project umbrella + 12 months umbrella
//
// by developer
//
// by hours
$worklogs_segmented_by_proj = [];
$company_projects = $this->getAllProjects();
foreach ($company_projects as $ndx => $company_project) {
$worklogs_by_user = Werklog::with('user')->selectRaw('user_id, COUNT(user_id) as user_occurrences, SUM(minutes) as user_project_total_time')->where('project_id', $company_project->id)->groupBy('user_id')->orderBy('user_project_total_time', 'DESC')->take(static::$_numrows)->get();
$worklogs_segmented_by_proj[$company_project->id] = $worklogs_by_user;
}
Debugbar::info($worklogs_segmented_by_proj);
return $worklogs_segmented_by_proj;
}
/**
* [postWorklogData - process posted worklog data]
* @return [json] [DT compatible object]
*/
public function postWorklogData()
{
$all_posted_values = $_POST['values'];
$op_type = $_POST['type'];
$segmented_data = $this->segmentDataByCompany($all_posted_values, 'worklogs');
if ($op_type === "strippenkaarten") {
foreach ($segmented_data as $company_ndx => $all_company_values) {
foreach ($all_company_values as $company_worklog_ndx => $company_worklog_values) {
$worklog = Werklog::find((int) $company_worklog_values['worklogs.id']);
if ($worklog) {
$this->associateWorklogToStrippenkaart($worklog);
}
}
}
} elseif ($op_type === "strippenkaarten_clear") {
foreach ($segmented_data as $company_ndx => $all_company_values) {
foreach ($all_company_values as $company_worklog_ndx => $company_worklog_values) {
$worklog = Werklog::find((int) $company_worklog_values['worklogs.id']);
if ($worklog) {
$this->dissociateWorklogFromStrippenkaarts($worklog);
}
}
}
} elseif ($op_type === "invoice") {
$mb = new IntegrationMoneybirdController2();
foreach ($segmented_data as $company_ndx => $all_company_values) {
$company_invoice = $mb->invoiceClient($all_company_values, $company_ndx, 'worklog');
// update worklog
if ($company_invoice) {
foreach ($all_company_values as $company_worklog_ndx => $company_worklog_values) {
$worklog = Werklog::find((int) $company_worklog_values['worklogs.id']);
if ($worklog) {
$worklog->invoice_id = $company_invoice->id;
$worklog->save();
}
}
}
}
} elseif ($op_type === "invoice_clear") {
foreach ($segmented_data as $company_ndx => $all_company_values) {
// clear worklog
foreach ($all_company_values as $company_worklog_ndx => $company_worklog_values) {
$worklog = Werklog::find((int) $company_worklog_values['worklogs.id']);
if ($worklog) {
$worklog->invoice_id = null;
$worklog->save();
}
}
}
} else {
error_log('AAC: No $op_type');
}
return Response::json((object) null);
}