public function action_index()
{
$staff_id = Session::get(self::LOGIN);
$staff = Model_Staff::find($staff_id);
$data['deliverylist'] = $staff->getDeliveryList();
$this->template->title = '配達一覧';
$this->template->content = View::forge('deliverysupport/client/deliverylist', $data);
}
public static function isExist($staffNo)
{
$staff = Model_Staff::query()->where('staffNo', $staffNo)->get_one();
if (!empty($staff)) {
return $staff;
}
return null;
}
public function action_delete($id = null)
{
if ($staff = Model_Staff::find($id)) {
$staff->delete();
Session::set_flash('success', 'Deleted staff #' . $id);
} else {
Session::set_flash('error', 'Could not delete staff #' . $id);
}
Response::redirect('staff');
}
/**
* Действие для просмотра игрока
*
* @param int $id
*/
public function action_view($id)
{
is_null($id) and \Response::redirect('');
// Получаем новость
$data['player'] = \Model_Staff::query()->related('position')->where('id', $id)->where('is_core_player', 0)->get_one();
// Если такой статьи нет, то отображаем страницу 404
if (is_null($data['player'])) {
throw new \HttpNotFoundException();
}
// Передаем данные в вид
$this->template->page_title = 'Команда :: Все игроки :: ' . $data['player']->player_name;
$this->template->content = \View::forge('players/past/view', $data, FALSE);
}
/**
* Действие для просмотра игрока
*
* @param int $id
*/
public function action_view($id)
{
is_null($id) and \Response::redirect('');
// Получаем запись
$data['staff'] = \Model_Staff::query()->where('id', $id)->get_one();
// Если такой статьи нет, то отображаем страницу 404
if (is_null($data['staff'])) {
throw new \HttpNotFoundException();
}
// Передаем данные в вид
$this->template->page_title = 'Команда :: Персонал :: ' . $data['staff']->staff_name;
$this->template->content = \View::forge('staff/view', $data, FALSE);
}
public function post_testposition()
{
/*if(empty(Session::get(self::LOGIN))){
return array(
'status' => 'NG',
'id' => $_POST['lat']
);
}*/
$staff_id = $_POST['staff_id'];
$staff = Model_Staff::find($staff_id);
$staff->lat = $_POST['lat'];
$staff->long = $_POST['lng'];
$staff->save();
return array('status' => 'OK');
}
public function action_index()
{
if (isset($_POST['input_staffNo'])) {
$staffNo = $_POST['input_staffNo'];
if (!empty($staff = Model_Staff::isExist($staffNo))) {
Session::set(self::LOGIN, $staff->id);
return Response::redirect('client');
} else {
$this->template->title = 'ログイン';
$this->template->content = View::forge('client/login_error');
}
} else {
$this->template->title = 'ログイン';
$this->template->content = View::forge('client/login');
}
}
public function get_trading_places($center = null)
{
$userList = array();
$allstaff = Model_Staff::query()->where('active', 1)->where('center_id', 1)->or_where('center_id', 2)->get();
//print_r($allstaff);
foreach ($allstaff as $oneStaff) {
$userList[$oneStaff->debtsolv_id]['points'] = 0;
}
// Get a list of debtsolv_id names for active users
$salesstaff = Model_Staff::query()->where('active', 1)->where('department_id', 1);
if (!is_null($center)) {
$salesstaff->where('center_id', $call_center->id);
}
$totalStaff = $salesstaff->count();
$salesstaff = $salesstaff->get();
// Convert the active users into a list ready for the "IN" query
$salesinList = "";
$salesinListCount = 0;
foreach ($salesstaff as $salesmember) {
$salesinListCount++;
$salesinList .= "'" . $salesmember->debtsolv_id . "'";
if ($salesinListCount < $totalStaff) {
$salesinList .= ",";
}
}
$seniorstaff = Model_Staff::query()->where('active', 1)->where('department_id', 2);
if (!is_null($center)) {
$seniorstaff->where('center_id', $call_center->id);
}
$totalStaff = $seniorstaff->count();
$seniorstaff = $seniorstaff->get();
// Convert the active users into a list ready for the "IN" query
$seniorinList = "";
$seniorinListCount = 0;
foreach ($seniorstaff as $seniormember) {
$seniorinListCount++;
$seniorinList .= "'" . $seniormember->debtsolv_id . "'";
if ($seniorinListCount < $totalStaff) {
$seniorinList .= ",";
}
}
$salesQuery = "SELECT \n D_CLD.Client_ID\n \t, D_URS.Login\n \t, D_CLD.DatePackSent\n FROM\n \tDebtsolv_GABFS.dbo.Client_LeadData AS D_CLD\n LEFT JOIN\n \tDebtsolv_GABFS.dbo.Users AS D_URS ON D_CLD.TelesalesAgent = D_URS.ID\n WHERE\n \tD_CLD.DatePackSent >= '2013-03-01'\n \tAND D_CLD.DatePackSent < '2013-06-01'\n \tAND D_URS.Login IN (" . $salesinList . ")";
$seniorQuery = "SELECT \n D_CLD.Client_ID\n \t, D_URS.Login\n \t, D_CLD.DatePackReceived\n FROM\n \tDebtsolv_GABFS.dbo.Client_LeadData AS D_CLD\n LEFT JOIN\n \tDebtsolv_GABFS.dbo.Users AS D_URS ON D_CLD.Counsellor = D_URS.ID\n WHERE\n \tD_CLD.DatePackReceived >= '2013-03-01'\n \tAND D_CLD.DatePackReceived < '2013-06-01'\n \tAND D_URS.Login IN (" . $seniorinList . ")";
$reportResultsSeniors = DB::query($seniorQuery)->cached(60)->execute('debtsolv');
$reportResultsSales = DB::query($salesQuery)->cached(60)->execute('debtsolv');
$point = array('packOut' => 50, 'packIn' => 25);
foreach ($reportResultsSeniors as $oneSenior) {
$userList[$oneSenior['Login']]['points'] = !isset($userList[$oneSenior['Login']]['points']) ? $point['packIn'] : $userList[$oneSenior['Login']]['points'] + $point['packIn'];
}
foreach ($reportResultsSales as $oneSenior) {
$userList[$oneSenior['Login']]['points'] = !isset($userList[$oneSenior['Login']]['points']) ? $point['packOut'] : $userList[$oneSenior['Login']]['points'] + $point['packOut'];
}
arsort($userList);
$pccResults = array();
$hqResults = array();
foreach ($userList as $username => $points) {
$salesstaff = Model_Staff::query()->where('debtsolv_id', $username);
if ($salesstaff->count() > 0) {
$salesstaff = $salesstaff->get_one();
if ($salesstaff->center_id == 1) {
$hqResults[] = array('name' => $salesstaff->first_name . " " . $salesstaff->last_name, 'points' => (int) $points['points']);
} else {
if ($salesstaff->center_id == 2) {
$pccResults[] = array('name' => $salesstaff->first_name . " " . $salesstaff->last_name, 'points' => (int) $points['points']);
}
}
}
}
return $this->response(array('pcc' => $pccResults, 'hq' => $hqResults));
}
public static function generate_telesales_report($center = null, $_startDate = null, $_endDate = null, $valueScheme = null)
{
// Pull in the values required for all centers
$_allValues = Model_Telesales_Report_Value::find('all');
$centerValues = array();
foreach ($_allValues as $value) {
$centerValues[$value->center_id] = array('referral' => $value->referral_points, 'pack_out' => $value->pack_out_points, 'di_point' => $value->di_pound_point, 'pack_out_commission' => $value->pack_out_commission, 'pack_out_bonus' => $value->pack_out_bonus, 'payment_percentage' => $value->payment_percentage);
}
// Set the start and end dates
$startDate = is_null($_startDate) ? date('Y-m-d', mktime(0, 0, 0, (int) date('m'), 1, (int) date('Y'))) : $_startDate;
$endDate = is_null($_endDate) ? date('Y-m-d', strtotime("Today")) : $_endDate;
$call_center = Model_Call_Center::query()->where('shortcode', $center)->get_one();
if (is_null($valueScheme)) {
$centerValue = is_null($center) ? $centerValues[0] : $centerValues[$call_center->id];
} else {
$centerValue = $centerValues[$valueScheme];
}
// Get a list of debtsolv_id names for active users
$staff = Model_Staff::query()->where('active', 1)->where('department_id', 1);
if (!is_null($center)) {
if ($center == 'INTERNAL') {
$staff->where('center_id', 'IN', array(1, 2));
} else {
$staff->where('center_id', $call_center->id);
}
}
$totalStaff = $staff->count();
$staff = $staff->get();
// Convert the active users into a list ready for the "IN" query
$inList = "";
$inListCount = 0;
foreach ($staff as $member) {
$inListCount++;
$inList .= "'" . $member->dialler_id . "'";
if ($inListCount < $totalStaff) {
$inList .= ",";
}
}
// Select all the required details from Debtsolv_GABFS.
$reportQuery = "SELECT DR.leadpool_id\n , DR.short_code\n , DR.user_login\n , TCR.[Description]\n , D_CPD.NormalExpectedPayment/100 AS DI\n , DR.referral_date\n , CONVERT(varchar, CC.LastContactAttempt, 120) AS 'Last Contact Date'\n , CASE\n \t\t\t WHEN CC.ContactResult = 700\n \t\t\t THEN CONVERT(varchar, CC.Appointment, 120)\n \t\t\t ELSE\n \t\t\t ''\n \t\t\t END AS 'Call Back Date'\n \t\t\t , (\n \t\t\t \tSELECT Top (1)\n \t\t\t \t\tResponseVal\n \t\t\t \tFROM\n \t\t\t \t\tDebtsolv_GABFS.dbo.Client_CustomQuestionResponses\n \t\t\t \tWHERE\n \t\t\t \t\tQuestionID = 10007\n \t\t\t \t\tAND ClientID = D_CLD.Client_ID\n \t\t\t ) AS 'ProductType'\n , (CD.Forename + ' ' + CD.Surname) AS Name\n FROM Dialler.dbo.referrals AS DR\n LEFT JOIN Leadpool_GABFS.dbo.Client_LeadDetails AS CLD ON DR.leadpool_id=CLD.ClientID\n LEFT JOIN Leadpool_GABFS.dbo.Campaign_Contacts AS CC ON CLD.ClientID = CC.ClientID\n LEFT JOIN Leadpool_GABFS.dbo.Type_ContactResult AS TCR ON CC.ContactResult = TCR.ID\n LEFT JOIN Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON CLD.ClientID = D_CLD.LeadPoolReference\n LEFT JOIN Debtsolv_GABFS.dbo.Client_PaymentData AS D_CPD ON D_CLD.Client_ID = D_CPD.ClientID\n LEFT JOIN Leadpool_GABFS.dbo.Client_Details AS CD ON D_CLD.LeadPoolReference = CD.ClientID\n WHERE DR.user_login IN (" . $inList . ")\n AND DR.short_code IN ('RESOLVE', 'GAB','GBS', 'MMS', 'MMS-GBS', 'EMS', 'EMS-GBS')\n AND TCR.[Description] <> 'Referred'\n AND CONVERT(date, DR.referral_date, 105) >= '" . $startDate . "'\n AND CONVERT(date, DR.referral_date, 105) < '" . $endDate . "'";
$reportQueryResolve = "SELECT DR.leadpool_id\n , DR.short_code\n , DR.user_login\n , TCR.[Description]\n , D_CPD.NormalExpectedPayment/100 AS DI\n , DR.referral_date\n , CONVERT(varchar, CC.LastContactAttempt, 120) AS 'Last Contact Date'\n , CASE\n \t\t\t WHEN CC.ContactResult = 700\n \t\t\t THEN CONVERT(varchar, CC.Appointment, 120)\n \t\t\t ELSE\n \t\t\t ''\n \t\t\t END AS 'Call Back Date'\n \t\t\t , (\n \t\t\t \tSELECT Top (1)\n \t\t\t \t\tResponseVal\n \t\t\t \tFROM\n \t\t\t \t\tBS_Debtsolv_DM.dbo.Client_CustomQuestionResponses\n \t\t\t \tWHERE\n \t\t\t \t\tQuestionID = 10007\n \t\t\t \t\tAND ClientID = D_CLD.Client_ID\n \t\t\t ) AS 'ProductType'\n , (CD.Forename + ' ' + CD.Surname) AS Name\n FROM Dialler.dbo.referrals AS DR\n LEFT JOIN BS_Leadpool_GABFS.dbo.Client_LeadDetails AS CLD ON DR.leadpool_id=CLD.ClientID\n LEFT JOIN BS_Leadpool_GABFS.dbo.Campaign_Contacts AS CC ON CLD.ClientID = CC.ClientID\n LEFT JOIN BS_Leadpool_GABFS.dbo.Type_ContactResult AS TCR ON CC.ContactResult = TCR.ID\n LEFT JOIN BS_Debtsolv_DM.dbo.Client_LeadData AS D_CLD ON CLD.ClientID = D_CLD.LeadPoolReference\n LEFT JOIN BS_Debtsolv_DM.dbo.Client_PaymentData AS D_CPD ON D_CLD.Client_ID = D_CPD.ClientID\n LEFT JOIN BS_Leadpool_GABFS.dbo.Client_Details AS CD ON D_CLD.LeadPoolReference = CD.ClientID\n WHERE DR.user_login IN (" . $inList . ")\n AND DR.short_code IN ('RESOLVE', 'GAB','GBS', 'MMS', 'MMS-GBS', 'EMS', 'EMS-GBS')\n AND TCR.[Description] <> 'Referred'\n AND CONVERT(date, DR.referral_date, 105) >= '" . $startDate . "'\n AND CONVERT(date, DR.referral_date, 105) < '" . $endDate . "'";
// Find all the paid clients for this date range
$paymentsQuery = "SELECT D_CD.ClientID\n , D_CD.FirstPaymentDate\n , D_CPD.NormalExpectedPayment AS DI\n , D_CLD.LeadPoolReference AS LeadpoolID\n , D_R.user_login\n FROM [Dialler].[dbo].[client_dates] AS D_CD\n LEFT JOIN Debtsolv_GABFS.dbo.Client_PaymentData AS D_CPD ON D_CD.ClientID = D_CPD.ClientID\n LEFT JOIN Debtsolv_GABFS.dbo.Client_LeadData AS D_CLD ON D_CD.ClientID = D_CLD.Client_ID\n LEFT JOIN Dialler.dbo.referrals AS D_R ON D_CLD.LeadPoolReference = D_R.leadpool_id\n WHERE D_R.user_login IN (" . $inList . ")\n AND D_R.short_code IN ('RESOLVE', 'GAB','GBS', 'MMS', 'MMS-GBS', 'EMS', 'EMS-GBS')\n AND CONVERT(date, D_CD.FirstPaymentDate, 105) >= '" . $startDate . "'\n AND CONVERT(date, D_CD.FirstPaymentDate, 105) < '" . $endDate . "'";
// Find all the paid clients for this date range
$paymentsQueryResolve = "SELECT D_CD.ClientID\n , D_CD.FirstPaymentDate\n , D_CPD.NormalExpectedPayment AS DI\n , D_CLD.LeadPoolReference AS LeadpoolID\n , D_R.user_login\n FROM [Dialler].[dbo].[client_dates] AS D_CD\n LEFT JOIN BS_Debtsolv_DM.dbo.Client_PaymentData AS D_CPD ON D_CD.ClientID = D_CPD.ClientID\n LEFT JOIN BS_Debtsolv_DM.dbo.Client_LeadData AS D_CLD ON D_CD.ClientID = D_CLD.Client_ID\n LEFT JOIN Dialler.dbo.referrals AS D_R ON D_CLD.LeadPoolReference = D_R.leadpool_id\n WHERE D_R.user_login IN (" . $inList . ")\n AND D_R.short_code IN ('RESOLVE', 'GAB','GBS', 'MMS', 'MMS-GBS', 'EMS', 'EMS-GBS')\n AND CONVERT(date, D_CD.FirstPaymentDate, 105) >= '" . $startDate . "'\n AND CONVERT(date, D_CD.FirstPaymentDate, 105) < '" . $endDate . "'";
// Loop through the results and create the report
$reportResultsGAB = DB::query($reportQuery)->cached(60)->execute('debtsolv');
#$reportResultsResolve = DB::query($reportQueryResolve)->cached(60)->execute('debtsolv');
$paymentsResults = DB::query($paymentsQuery)->cached(60)->execute('debtsolv');
#$paymentsResultsResolve = DB::query($paymentsQueryResolve)->cached(60)->execute('debtsolv');
$reportResults = array();
foreach ($reportResultsGAB as $result) {
$reportResults[] = $result;
}
/*
foreach ($reportResultsResolve AS $result)
{
$reportResults[] = $result;
}
*/
$reportArray = array();
foreach ($reportResults as $result) {
if ($result['Description'] == "Lead Completed" and $result['DI'] < 10 or (string) $result['ProductType'] == '2') {
// It's a PPI
} else {
if (isset($reportArray[$result['user_login']])) {
$reportArray[$result['user_login']]['referrals']++;
$reportArray[$result['user_login']]['totalDI'] = $result['Description'] == "Lead Completed" ? $reportArray[$result['user_login']]['totalDI'] + $result['DI'] : 0;
$reportArray[$result['user_login']]['packOuts'] = $result['Description'] == "Lead Completed" ? $reportArray[$result['user_login']]['packOuts'] + 1 : $reportArray[$result['user_login']]['packOuts'];
} else {
$singleResult = array('referrals' => 1, 'totalDI' => $result['Description'] == "Lead Completed" ? $result['DI'] : 0, 'packOuts' => $result['Description'] == "Lead Completed" ? 1 : 0);
$reportArray[$result['user_login']] = $singleResult;
}
$pdtype = "";
switch ((string) $result['ProductType']) {
case '0':
$pdtype = "DR";
break;
case '1':
$pdtype = "DMPLUS";
break;
case '2':
$pdtype = "PPI";
break;
case '3':
$pdtype = "DRPLUS";
break;
case '':
$pdtype = "";
break;
}
$reportArray[$result['user_login']]['allReferrals'][] = array('Name' => $result['Name'], 'leadID' => $result['leadpool_id'], 'LeadName' => 'Leadpool Name', 'Result' => $result['Description'], 'DI' => (int) $result['DI'] < 10 ? "" : "£" . number_format((double) $result['DI'], 2), 'Product' => $pdtype, 'referred' => date("d/m/Y", strtotime($result['referral_date'])), 'lastContact' => strlen($result['Last Contact Date']) < 4 ? '' : date("d/m/Y", strtotime($result['Last Contact Date'])), 'callBack' => strlen($result['Call Back Date']) < 4 ? '' : date("d/m/Y", strtotime($result['Call Back Date'])));
}
}
// Work out points, conversion rate and P/O bonus
foreach ($reportArray as $key => $items) {
$reportArray[$key]['conversionRate'] = $items['packOuts'] / $items['referrals'] * 100;
$reportArray[$key]['points'] = $items['packOuts'] * 2 + $items['referrals'];
$reportArray[$key]['commission'] = $items['packOuts'] * $centerValue['pack_out_commission'];
}
// Finally look through the first payments and create the comissions
foreach ($paymentsResults as $payment) {
$reportArray[$payment['user_login']]['commission'] = isset($reportArray[$payment['user_login']]['commission']) ? $reportArray[$payment['user_login']]['commission'] + $payment['DI'] / 100 / 100 * $centerValue['payment_percentage'] : $payment['DI'] / 100 / 100 * $centerValue['payment_percentage'];
}
// Finally look through the first payments and create the comissions
/*
foreach ($paymentsResultsResolve AS $payment)
{
$reportArray[$payment['user_login']]['commission'] = (isset($reportArray[$payment['user_login']]['commission'])) ? $reportArray[$payment['user_login']]['commission'] + ((($payment['DI']/100)/100)*$centerValue['payment_percentage']) : ((($payment['DI']/100)/100)*$centerValue['payment_percentage']);
}
*/
// Last but not least. Create a nice array to return
$sendArray = array();
foreach ($staff as $member) {
$sendArray[] = array('name' => $member->first_name . " " . $member->last_name, 'referrals' => isset($reportArray[$member->dialler_id]['referrals']) ? $reportArray[$member->dialler_id]['referrals'] : 0, 'packouts' => isset($reportArray[$member->dialler_id]['packOuts']) ? $reportArray[$member->dialler_id]['packOuts'] : 0, 'conversionrate' => isset($reportArray[$member->dialler_id]['conversionRate']) ? number_format($reportArray[$member->dialler_id]['conversionRate'], 2) : 0, 'points' => isset($reportArray[$member->dialler_id]['referrals']) ? $reportArray[$member->dialler_id]['referrals'] * $centerValue['referral'] + $reportArray[$member->dialler_id]['packOuts'] * $centerValue['pack_out'] + $reportArray[$member->dialler_id]['totalDI'] * $centerValue['di_point'] : 0.0, 'commission' => isset($reportArray[$member->dialler_id]['commission']) ? number_format($reportArray[$member->dialler_id]['commission'], 2) : 0.0, 'allReferrals' => isset($reportArray[$member->dialler_id]['allReferrals']) ? $reportArray[$member->dialler_id]['allReferrals'] : array(), 'totalDI' => isset($reportArray[$member->dialler_id]['totalDI']) ? $reportArray[$member->dialler_id]['totalDI'] : 0, 'staff_id' => $member->id, 'dialler_id' => $member->dialler_id);
}
// Sory the array by points
$sort = array();
foreach ($sendArray as $key => $row) {
$sort[$key] = $row['points'];
}
array_multisort($sort, SORT_DESC, $sendArray);
foreach ($sendArray as $ksend => $send) {
$sendArray[$ksend]['points'] = number_format($send['points'], 2);
}
return array('report' => $sendArray, 'centerVals' => $centerValue);
}
public static function getPostion($staff_id)
{
$staff = Model_Staff::find($staff_id);
return array('lat' => $staff->lat, 'long' => $staff->long);
}
/**
* Удаление игрока
*
* @param int $id
*/
public function action_delete($id = null)
{
is_null($id) and \Response::redirect_back('admin/staff');
if ($staff = \Model_Staff::find($id)) {
if ($staff->image_uri) {
unlink(DOCROOT . 'assets/img/staff/' . $staff->image_uri);
}
$staff->delete();
\Session::set_flash('success', 'Персонал удалён.');
} else {
\Session::set_flash('error', 'Could not delete Ыефаа #' . $id);
}
\Response::redirect_back('admin/staff');
}
public function post_getPostion()
{
$staff_id = $_POST['staff_id'];
$staff = Model_Staff::find($staff_id);
return array('lat' => $staff->lat, 'long' => $staff->long);
}
