/**
* grid
* create the configuration of the grid
*/
public static function grid($rowNum = 10)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$config['datagrid']['columns'][] = array('label' => null, 'field' => 's.subscriber_id', 'alias' => 'subscriber_id', 'type' => 'selectall');
$config['datagrid']['columns'][] = array('label' => $translator->translate('ID'), 'field' => 's.subscriber_id', 'alias' => 'subscriber_id', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Email'), 'field' => 's.email', 'alias' => 'email', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Subscription Date'), 'field' => 's.subscriptiondate', 'alias' => 'subscriptiondate', 'sortable' => true, 'searchable' => true, 'type' => 'date');
$config['datagrid']['fields'] = "s.subscriber_id, s.email as email, DATE_FORMAT(s.subscriptiondate, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as subscriptiondate";
$config['datagrid']['rownum'] = $rowNum;
$config['datagrid']['dqrecordset'] = Doctrine_Query::create()->select($config['datagrid']['fields'])->from('NewslettersSubscribers s');
$config['datagrid']['basepath'] = "/admin/subscribers/";
$config['datagrid']['index'] = "subscriber_id";
$config['datagrid']['rowlist'] = array('10', '50', '100', '1000');
$config['datagrid']['buttons']['edit']['label'] = $translator->translate('Edit');
$config['datagrid']['buttons']['edit']['cssicon'] = "edit";
$config['datagrid']['buttons']['edit']['action'] = "/admin/subscribers/edit/id/%d";
$config['datagrid']['buttons']['delete']['label'] = $translator->translate('Delete');
$config['datagrid']['buttons']['delete']['cssicon'] = "delete";
$config['datagrid']['buttons']['delete']['action'] = "/admin/subscribers/delete/id/%d";
$config['datagrid']['massactions'] = array('massdelete' => 'Mass Delete', 'bulkexport' => 'Export');
$bulkmailinglist = array();
$mailchimplists = Newsletters::get_mailchimp_list();
if (!empty($mailchimplists)) {
foreach ($mailchimplists as $id => $name) {
$bulkmailinglist['bulk_mailchimp_optin&list=' . $id] = "Add to ({$name})";
$bulkmailinglist['bulk_mailchimp_optout&list=' . $id] = "Remove from ({$name})";
}
}
$config['datagrid']['massactions']['commons'] = array_merge($config['datagrid']['massactions'], $bulkmailinglist);
return $config;
}
/**
* List of the last messages attached within the orders, domains, customers detail page
*
*
* @param string $attachedto [orders, domains]
* @param integer $limit
* @return ArrayObject
*/
public static function Last($attachedto = "orders", $limit = 5, $delIspReplies = true)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$dq = Doctrine_Query::create()->from('Messages m');
// Adding first the main ID index field
if ($attachedto == "orders") {
$dq->select("order_id as id");
$dq->where("order_id IS NOT NULL");
} elseif ($attachedto == "domains") {
$dq->select("domain_id as id");
$dq->where("domain_id IS NOT NULL");
}
// now we can add more fields
$dq->addSelect("DATE_FORMAT(m.dateposted, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as date, m.message as message");
if ($delIspReplies) {
$dq->andWhere("customer_id IS NOT NULL");
}
// Sort the items
$dq->orderBy('m.dateposted desc')->limit($limit);
$records['data'] = $dq->execute(null, Doctrine::HYDRATE_ARRAY);
// Strip the html and trucate the message
for ($i = 0; $i < count($records['data']); $i++) {
$records['data'][$i]['message'] = Shineisp_Commons_Utilities::truncate(strip_tags($records['data'][$i]['message']), 50, "...", false, true);
}
// adding the index reference
$records['index'] = "id";
// Create the header table columns
$records['fields'] = array('date' => array('label' => $translator->translate('Date')), 'message' => array('label' => $translator->translate('Message')));
return $records;
}
/**
* getAllInfo
* Get all data starting from the wikiID
* @param $id
* @return Doctrine Record / Array
*/
public static function getAllInfo($id)
{
$dq = Doctrine_Query::create()->select("block_id, \r\n \t\t\t\t\t\t\t\t\t\t cpd.language_id,\r\n \t\t\t\t\t\t\t\t\t\t title, \r\n \t\t\t\t\t\t\t\t\t\t DATE_FORMAT(publishedat, '" . Settings::getMySQLDateFormat('dateformat') . "') as publishedat, \r\n \t\t\t\t\t\t\t\t\t\t var, \r\n \t\t\t\t\t\t\t\t\t\t title, \r\n \t\t\t\t\t\t\t\t\t\t body")->from('CmsBlocks cms')->leftJoin('cms.CmsBlocksData cpd')->leftJoin('cpd.Languages l')->where("block_id = ?", $id)->limit(1);
$records = $dq->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
// Get the languages
if (!empty($records[0]['CmsBlocksData'])) {
foreach ($records[0]['CmsBlocksData'] as $record) {
$records[0]['language_id'][] = $record['language_id'];
}
unset($records[0]['CmsBlocksData']);
}
return !empty($records[0]) ? $records[0] : array();
}
/**
* getList
* Get all the records for the select objects
* @param $customer_id, $empty
* @return Array
*/
public static function getList($customer_id = null, $empty = false)
{
$theitems = array();
$dq = Doctrine_Query::create()->select("cr.nic_id, cr.value, c.customer_id, c.company as company, c.lastname as lastname, c.firstname as firstname, DATE_FORMAT(cr.creationdate, '" . Settings::getMySQLDateFormat('dateformat') . "') as creationdate")->from('CustomersDomainsRegistrars cr')->leftJoin('cr.Customers c');
if (is_numeric($customer_id)) {
$dq->where('cr.customer_id = ?', $customer_id);
}
$items = $dq->execute(array(), Doctrine::HYDRATE_ARRAY);
if ($empty) {
$theitems[] = "";
}
foreach ($items as $c) {
$theitems[$c['nic_id']] = $c['value'] . " - " . $c['lastname'] . " " . $c['firstname'] . " - " . $c['company'];
}
return $theitems;
}
/**
* grid
* create the configuration of the grid
*/
public static function grid()
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$config['datagrid']['columns'][] = array('label' => null, 'field' => 'r.review_id', 'alias' => 'review_id', 'type' => 'selectall');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Id'), 'field' => 'r.review_id', 'alias' => 'review_id', 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Subject'), 'field' => 'r.subject', 'alias' => 'subject', 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('City'), 'field' => 'r.city', 'alias' => 'city', 'type' => 'string', 'attributes' => array('class' => 'visible-lg visible-md hidden-xs'));
$config['datagrid']['columns'][] = array('label' => $translator->translate('Stars'), 'field' => 'r.stars', 'alias' => 'stars', 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Published on'), 'field' => 'r.publishedat', 'alias' => 'publishedat', 'type' => 'date', 'attributes' => array('class' => 'visible-lg visible-md hidden-xs'));
$config['datagrid']['columns'][] = array('label' => $translator->translate('Active'), 'field' => 'r.active', 'alias' => 'active', 'type' => 'boolean');
$config['datagrid']['fields'] = "r.review_id, r.*, DATE_FORMAT(r.publishedat, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as publishedat";
$config['datagrid']['dqrecordset'] = Doctrine_Query::create()->from('Reviews r')->orderBy('review_id desc');
$config['datagrid']['id'] = "reviews";
$config['datagrid']['index'] = "review_id";
$config['datagrid']['massactions']['common'] = array('bulk_delete' => $translator->translate('Mass Delete'), 'bulk_export' => $translator->translate('Export List'));
$statuses['bulk_set_status&status=1'] = $translator->translate("Set as active");
$statuses['bulk_set_status&status=0'] = $translator->translate("Set as suspended");
$config['datagrid']['massactions']['statuses'] = $statuses;
return $config;
}
/**
* grid
* create the configuration of the grid
*/
public static function grid($rowNum = 10)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$config['datagrid']['columns'][] = array('label' => null, 'field' => 'n.news_id', 'alias' => 'news_id', 'type' => 'selectall');
$config['datagrid']['columns'][] = array('label' => $translator->translate('ID'), 'field' => 'n.news_id', 'alias' => 'news_id', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Subject'), 'field' => 'n.subject', 'alias' => 'subject', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Send at'), 'field' => 'n.sendat', 'alias' => 'sendat', 'sortable' => true, 'searchable' => true, 'type' => 'date');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Sent'), 'field' => 'n.sent', 'alias' => 'sent', 'sortable' => true, 'searchable' => true, 'type' => 'date');
$config['datagrid']['fields'] = "n.news_id, \n\t\t\t\t\t\t\t\t\t\t DATE_FORMAT(n.sendat, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as sendat, \n\t\t\t\t\t\t\t\t\t\t n.sent as sent,\n\t\t\t\t\t\t\t\t\t\t n.subject as subject";
$config['datagrid']['dqrecordset'] = Doctrine_Query::create()->select($config['datagrid']['fields'])->from('Newsletters n');
$config['datagrid']['rownum'] = $rowNum;
$config['datagrid']['basepath'] = "/admin/newsletter/";
$config['datagrid']['index'] = "news_id";
$config['datagrid']['rowlist'] = array('10', '50', '100', '1000');
$config['datagrid']['buttons']['edit']['label'] = $translator->translate('Edit');
$config['datagrid']['buttons']['edit']['cssicon'] = "edit";
$config['datagrid']['buttons']['edit']['action'] = "/admin/newsletter/edit/id/%d";
$config['datagrid']['buttons']['delete']['label'] = $translator->translate('Delete');
$config['datagrid']['buttons']['delete']['cssicon'] = "delete";
$config['datagrid']['buttons']['delete']['action'] = "/admin/newsletter/delete/id/%d";
$config['datagrid']['massactions']['common'] = array('massdelete' => 'Mass Delete');
return $config;
}
/**
* grid
* create the configuration of the grid
*/
public static function grid($rowNum = 10)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$config['datagrid']['columns'][] = array('label' => null, 'field' => 'w.wiki_id', 'alias' => 'wiki_id', 'type' => 'selectall');
$config['datagrid']['columns'][] = array('label' => $translator->translate('ID'), 'field' => 'w.wiki_id', 'alias' => 'wiki_id', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Subject'), 'field' => 'w.subject', 'alias' => 'subject', 'sortable' => true, 'searchable' => true, 'type' => 'string');
$config['datagrid']['columns'][] = array('label' => $translator->translate('Creation Date'), 'field' => 'w.creationdate', 'alias' => 'creation_date', 'sortable' => true, 'searchable' => true, 'type' => 'date', 'attributes' => array('class' => 'span2'));
$config['datagrid']['columns'][] = array('label' => $translator->translate('Category'), 'field' => 'wc.category_id', 'alias' => 'category', 'sortable' => true, 'type' => 'index', 'searchable' => true, 'filterdata' => WikiCategories::getList(), 'attributes' => array('class' => 'span2'));
$config['datagrid']['columns'][] = array('label' => $translator->translate('Visits'), 'field' => 'w.views', 'alias' => 'visits', 'sortable' => true, 'type' => 'index');
$config['datagrid']['fields'] = "w.wiki_id, w.subject as subject, DATE_FORMAT(w.creationdate, '" . Settings::getMySQLDateFormat('dateformat') . "') as creation_date, wc.category as category, w.views as visits";
$config['datagrid']['dqrecordset'] = Doctrine_Query::create()->select($config['datagrid']['fields'])->from('Wiki w')->leftJoin('w.WikiCategories wc');
$config['datagrid']['rownum'] = $rowNum;
$config['datagrid']['basepath'] = "/admin/wiki/";
$config['datagrid']['index'] = "wiki_id";
$config['datagrid']['rowlist'] = array('10', '50', '100', '1000');
$config['datagrid']['buttons']['edit']['label'] = $translator->translate('Edit');
$config['datagrid']['buttons']['edit']['cssicon'] = "edit";
$config['datagrid']['buttons']['edit']['action'] = "/admin/wiki/edit/id/%d";
$config['datagrid']['buttons']['delete']['label'] = $translator->translate('Delete');
$config['datagrid']['buttons']['delete']['cssicon'] = "delete";
$config['datagrid']['buttons']['delete']['action'] = "/admin/wiki/delete/id/%d";
$config['datagrid']['massactions'] = array('massdelete' => 'Mass Delete', 'bulkexport' => 'Export');
return $config;
}
/**
* Create a widget for the dashboard
* @return Array
*/
public static function Last($limit = 10)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$dq = Doctrine_Query::create()->select("DATE_FORMAT(startdate, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as startdate, \r\n\t\t\t\t\t\t\t\t\t\t DATE_FORMAT(enddate, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as enddate,\r\n\t\t\t\t\t\t\t\t\t\t CONCAT(d.domain, '.', w.tld) as domain, \r\n\t\t\t\t\t\t\t\t\t\t action,\r\n\t\t\t\t\t\t\t\t\t\t log,\r\n\t\t\t\t\t\t\t\t\t\t s.status as status")->from('DomainsTasks dt')->leftJoin('dt.Statuses s')->leftJoin('dt.Domains d')->leftJoin('d.DomainsTlds t')->leftJoin('t.WhoisServers w')->leftJoin('d.Customers c')->orderBy('dt.startdate desc')->limit($limit);
$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
$logged_user = $auth->getIdentity();
$dq->whereIn("c.isp_id", $logged_user['isp_id']);
}
$records['data'] = $dq->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
// adding the index reference
$records['index'] = "task_id";
// Create the header table columns
$records['fields'] = array('startdate' => array('label' => $translator->translate('Start Date')), 'enddate' => array('label' => $translator->translate('End Date')), 'domain' => array('label' => $translator->translate('Domain'), 'attributes' => array('class' => 'visible-lg visible-md hidden-xs')), 'action' => array('label' => $translator->translate('Action')), 'log' => array('label' => $translator->translate('Log'), 'attributes' => array('class' => 'visible-lg visible-md hidden-xs')), 'status' => array('label' => $translator->translate('Status')));
return $records;
}
/**
* export the content in a pdf file
* @param array $items
*/
public function bulk_export($items)
{
$isp = Isp::getActiveISP();
$pdf = new Shineisp_Commons_PdfList();
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
// Get the records from the domains table
$domains = self::get_domains($items, "d.domain_id, \r\n\t\t\t\t\t\t\t\t\t\t\t CONCAT(d.domain, '.', d.tld) as domain, \r\n\t\t\t\t\t\t\t\t\t\t\t DATE_FORMAT(d.creation_date, '" . Settings::getMySQLDateFormat('dateformat') . "') as creation_date, \r\n\t\t\t\t\t\t\t\t\t\t\t DATE_FORMAT(d.expiring_date, '" . Settings::getMySQLDateFormat('dateformat') . "') as expiring_date, , \r\n\t\t\t\t\t\t\t\t\t\t\t DATEDIFF(expiring_date, CURRENT_DATE) as days,\r\n \t\t\t\t\t\t IF(d.autorenew = 1, 'YES', 'NO') as renew,\r\n\t\t\t\t\t\t\t\t\t\t\t s.status as status", "d.expiring_date, d.domain");
// Create the PDF header
$grid['headers']['title'] = $translator->translate('Domain Listing');
$grid['headers']['subtitle'] = $translator->_("List of the domains since %s and sorted by the expiring date and domain name", date('d/m/Y H:i:s'));
$grid['footer']['text'] = $isp['company'] . " - " . $isp['website'];
if (!empty($domains[0])) {
// Create the columns of the grid
$grid['columns'][] = array("value" => $translator->translate('Domain'));
}
$grid['columns'][] = array("value" => $translator->translate('Creation Date'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Expiry Date'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Days'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Automatic Renewal'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Status'), 'size' => 80);
// Getting the records values and delete the first column the customer_id field.
foreach ($domains as $domain) {
$values = array_values($domain);
array_shift($values);
$grid['records'][] = $values;
}
// Create the PDF
die($pdf->create($grid));
return false;
}
/**
* export the content in a pdf file
* @param array $items
*/
public function bulk_export($items)
{
$isp = Isp::getActiveISP();
$pdf = new Shineisp_Commons_PdfList();
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
// Get the records from the order table
$creditnotes = self::get_items($items, "creditnote_id, i.number as invoicenum, DATE_FORMAT(o.order_date, '" . Settings::getMySQLDateFormat('dateformat') . "') as orderdate, c.company as company, CONCAT(c.firstname, ' ', c.lastname) as fullname, cn.total as total, cn.total_vat as vat, cn.total as grandtotal, s.status as status", 'cn.creationdate');
// Create the PDF header
$grid['headers']['title'] = $translator->translate('Credit Note List');
$grid['headers']['subtitle'] = $translator->translate('List Credit Notes');
$grid['footer']['text'] = $isp['company'] . " - " . $isp['website'];
if (!empty($creditnotes[0])) {
// Create the columns of the grid
$grid['columns'][] = array("value" => $translator->translate('Credit Note'), 'size' => 50);
}
$grid['columns'][] = array("value" => $translator->translate('Invoice'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Date'), 'size' => 100);
$grid['columns'][] = array("value" => $translator->translate('Company'));
$grid['columns'][] = array("value" => $translator->translate('Fullname'));
$grid['columns'][] = array("value" => $translator->translate('Total'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('VAT'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Grand Total'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Status'), 'size' => 100);
// Getting the records values and delete the first column the customer_id field.
foreach ($creditnotes as $item) {
$values = array_values($item);
$grid['records'][] = $values;
}
// Create the PDF
die($pdf->create($grid));
return false;
}
/**
* export the content in a pdf file
* @param array $items
*/
public function bulk_export($items)
{
$isp = Isp::getActiveISP();
$pdf = new Shineisp_Commons_PdfList();
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
// Get the records from the payment table
$orders = self::get_payments($items, "p.payment_id,\r\n\t\t\t\t\t\t\t\t\t\t\t o.order_id as orderid,\r\n\t\t\t\t\t\t\t\t\t\t\t o.order_date as orderdate,\r\n\t\t\t\t\t\t\t\t\t\t\t i.number as invoice,\r\n DATE_FORMAT(p.paymentdate, '" . Settings::getMySQLDateFormat('dateformat') . "') as paymentdate,\r\n CONCAT(c.firstname, ' ', c.lastname, ' ', c.company) as customer,\r\n CONCAT(r.firstname, ' ', r.lastname, ' ', r.company) as reseller,\r\n p.reference as reference,\r\n p.confirmed as confirmed,\r\n p.income as income,\r\n p.outcome as outcome,\r\n\t\t\t\t\t\t\t\t\t\t\t o.grandtotal as grandtotal\r\n ", 'o.order_date');
// Create the PDF header
$grid['headers']['title'] = $translator->translate('Payment Transaction Listing');
$grid['headers']['subtitle'] = $translator->translate('List of the payment transactions');
$grid['footer']['text'] = $isp['company'] . " - " . $isp['website'];
if (!empty($orders[0])) {
// Create the columns of the grid
$grid['columns'][] = array("value" => $translator->translate('Payment'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Order'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Order Date'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Invoice'), 'size' => 100);
$grid['columns'][] = array("value" => $translator->translate('Transaction Date'), 'size' => 100);
$grid['columns'][] = array("value" => $translator->translate('Company'));
$grid['columns'][] = array("value" => $translator->translate('Reseller'));
$grid['columns'][] = array("value" => $translator->translate('Reference'));
$grid['columns'][] = array("value" => $translator->translate('Confirmed'));
$grid['columns'][] = array("value" => $translator->translate('Income'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Expense'), 'size' => 50);
$grid['columns'][] = array("value" => $translator->translate('Grand Total'), 'size' => 50);
// Getting the records values and delete the first column the customer_id field.
foreach ($orders as $item) {
$values = array_values($item);
$grid['records'][] = $values;
}
// Create the PDF
die($pdf->create($grid));
}
return false;
}
/**
* export the content in a pdf file
* @param array $items
*/
public function bulkexport($items)
{
$isp = Isp::getActiveISP();
$pdf = new Shineisp_Commons_PdfList();
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$fields = " t.ticket_id,\n\t\t\t\t\tt.subject as subject, \n\t\t\t\t\ttc.category as category, \n\t\t\t\t\ts.status as status, \n\t\t\t\t\tDATE_FORMAT(t.date_open, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i') as creation_date, \n\t\t\t\t\tDATE_FORMAT(t.date_updated, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i') as updated_at, \n\t\t\t\t\tCONCAT(c.firstname, ' ', c.lastname) as fullname,\n\t\t\t\t\tc.company as company";
// Get the records from the customer table
$tickets = self::get_tickets($items, $fields);
// Create the PDF header
$grid['headers']['title'] = $translator->translate('Tickets List');
$grid['headers']['subtitle'] = $translator->translate('List of the selected tickets');
$grid['footer']['text'] = $isp['company'] . " - " . $isp['website'];
if (!empty($tickets[0])) {
// Create the columns of the grid
$grid['columns'][] = array("value" => $translator->translate('Subject'), 'size' => 100);
}
$grid['columns'][] = array("value" => $translator->translate('Category'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Status'));
$grid['columns'][] = array("value" => $translator->translate('Creation Date'), 'size' => 100);
$grid['columns'][] = array("value" => $translator->translate('Upload Date'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Fullname'), 'size' => 80);
$grid['columns'][] = array("value" => $translator->translate('Company'), 'size' => 80);
// Getting the records values and delete the first column the customer_id field.
foreach ($tickets as $ticket) {
$values = array_values($ticket);
array_shift($values);
$grid['records'][] = $values;
}
// Create the PDF
die($pdf->create($grid));
return false;
}
/**
* Create a widget for the dashboard
* @return Array
*/
public static function Last($limit = 10)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$dq = Doctrine_Query::create()->select("DATE_FORMAT(start, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as startdate,\r\n\t\t\t\t\t\t\t\t\t\t DATE_FORMAT(end, '" . Settings::getMySQLDateFormat('dateformat') . " %H:%i:%s') as enddate,\r\n\t\t\t\t\t\t\t\t\t\t CONCAT(c.lastname, ' ', c.firstname) as fullname,\r\n\t\t\t\t\t\t\t\t\t\t p.panel_id as panel_id,\r\n\t\t\t\t\t\t\t\t\t\t c.customer_id as customer_id,\r\n\t\t\t\t\t\t\t\t\t\t p.name as name,\r\n\t\t\t\t\t\t\t\t\t\t action,\r\n\t\t\t\t\t\t\t\t\t\t log,\r\n\t\t\t\t\t\t\t\t\t\t s.status as status")->from('PanelsActions pa')->leftJoin('pa.Panels p')->leftJoin('pa.Statuses s')->leftJoin('pa.Customers c')->orderBy('pa.start desc')->limit($limit);
$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
$logged_user = $auth->getIdentity();
$dq->whereIn("p.isp_id", $logged_user['isp_id']);
}
$records['data'] = $dq->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
// adding the index reference
$records['index'] = "action_id";
// Create the header table columns
$records['fields'] = array('action_id' => array('label' => $translator->translate('ID')), 'startdate' => array('label' => $translator->translate('Start Date')), 'enddate' => array('label' => $translator->translate('End Date'), 'attributes' => array('class' => 'visible-lg visible-md hidden-xs')), 'fullname' => array('label' => $translator->translate('Full Name'), 'type' => 'link', 'link' => array('idx' => 'customer_id', 'href' => '/admin/customers/edit/id/%s'), 'attributes' => array('class' => 'visible-lg visible-md hidden-xs')), 'name' => array('label' => $translator->translate('Panel'), 'type' => 'link', 'link' => array('idx' => 'panel_id', 'href' => '/admin/panels/edit/id/%s')), 'log' => array('label' => $translator->translate('Log'), 'attributes' => array('class' => 'visible-lg visible-md hidden-xs')), 'status' => array('label' => $translator->translate('Status')));
return $records;
}
/**
* export the content in a csv file
* @param array $items
*/
public function bulk_csv_export($items)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
// Get the records from the order table
$orders = self::get_invoices($items, "purchase_id, \r\n\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(creationdate, '" . Settings::getMySQLDateFormat('dateformat') . "') as date, \r\n\t\t\t\t\t\t\t\t\t\t\t\tnumber as number, \r\n\t\t\t\t\t\t\t\t\t\t\t\tcompany as company, \r\n\t\t\t\t\t\t\t\t\t\t\t\tpm.method as payment,\r\n\t\t\t\t\t\t\t\t\t\t\t\ttotal_net as net, \r\n\t\t\t\t\t\t\t\t\t\t\t\ttotal_vat as vat, \r\n\t\t\t\t\t\t\t\t\t\t\t\ttotal as total,\r\n\t\t\t\t\t\t\t\t\t\t\t\ts.status as status", 'number, creationdate');
if (!empty($orders[0])) {
$tmpname = Shineisp_Commons_Utilities::GenerateRandomString();
@mkdir(PUBLIC_PATH . "/tmp/");
// Create the file and open it
$fp = fopen(PUBLIC_PATH . "/tmp/" . $tmpname . '.csv', 'w+');
// Add the headers
$headers = array_keys($orders[0]);
if (!empty($headers)) {
array_shift($headers);
foreach ($headers as $item) {
$newHeaders[] = $translator->translate(ucfirst($item));
}
fputcsv($fp, $newHeaders);
}
// For each record in the recordset
foreach ($orders as $item) {
array_shift($item);
fputcsv($fp, $item);
}
// Close the file
fclose($fp);
// Return the link
die(json_encode(array('url' => "/tmp/" . $tmpname . ".csv")));
}
return false;
}
private function filesGrid()
{
$request = $this->getRequest();
if (isset($request->id) && is_numeric($request->id)) {
$rs = Files::findbyExternalId($request->id, "orders", "file, Date_Format(date, '%d/%m/%Y') as date, fc.name as name, DATE_FORMAT(lastdownload, '" . Settings::getMySQLDateFormat('dateformat') . "') as downloaded");
if (isset($rs[0])) {
return array('records' => $rs, 'actions' => array('/admin/orders/getfile/id/' => $this->translator->translate('Download')), 'delete' => array('controller' => 'orders', 'action' => 'deletefile'));
}
}
}
/**
* expiringProducts
* Get all the expiring products
* @param $customerid
* @return ARRAY Record
*/
public static function getExpiringProducts($customerid = "", $locale = 1)
{
$dq = Doctrine_Query::create()->select("oi.detail_id as detail_id, o.order_id as order_id, pd.name as Products, DATE_FORMAT(oi.date_end, '" . Settings::getMySQLDateFormat('dateformat') . "') as Termination")->from('Orders o')->leftJoin('o.OrdersItems oi')->leftJoin('oi.Products p')->leftJoin("p.ProductsData pd WITH pd.language_id = {$locale}")->where("p.type <> ?", 'domain')->addWhere('DATEDIFF(oi.date_end, CURRENT_DATE) <= 31')->addWhere('oi.status_id = ?', Statuses::id("complete", "orders"));
// Complete
if (is_numeric($customerid)) {
$dq->addWhere("o.customer_id = ?", $customerid);
}
return $dq->execute(array(), Doctrine::HYDRATE_ARRAY);
}
/**
* export the content in a csv file
* @param array $items
*/
public function bulk_csv_export($items)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
// Get the records from the order table
$orders = self::get_invoices($items, "invoice_id, number as number, order_id as order, DATE_FORMAT(invoice_date, '" . Settings::getMySQLDateFormat('dateformat') . "') as date, \n\t\tc.company as company, CONCAT(c.firstname, ' ', c.lastname) as fullname, o.total as total, o.vat as VAT, o.grandtotal as grandtotal,", 'number, invoice_date');
if (!empty($orders[0])) {
$tmpname = Shineisp_Commons_Utilities::GenerateRandomString();
@mkdir(PUBLIC_PATH . "/tmp/");
// Create the file and open it
$fp = fopen(PUBLIC_PATH . "/tmp/" . $tmpname . '.csv', 'w+');
// Add the headers
$headers = array_keys($orders[0]);
if (!empty($headers)) {
array_shift($headers);
foreach ($headers as $item) {
$newHeaders[] = $translator->translate(ucfirst($item));
}
fputcsv($fp, $newHeaders);
}
// For each record in the recordset
foreach ($orders as $item) {
array_shift($item);
fputcsv($fp, $item);
}
// Close the file
fclose($fp);
// Return the link
die(json_encode(array('url' => "/tmp/" . $tmpname . ".csv")));
}
return false;
}
/**
* ProductsInOrdersItems
* Get all the orders where the product has been selected.
* @return array
*/
public static function ProductsInOrdersItems($product_id)
{
if (is_numeric($product_id)) {
$records = Doctrine_Query::create()->select('DATE_FORMAT(oi.date_start, "' . Settings::getMySQLDateFormat('dateformat') . '") as date, CONCAT(c.firstname, " ", c.lastname, " - ", c.company) as customer, oi.quantity, oi.order_id as orderid, s.status as status')->from('OrdersItems oi')->leftJoin('oi.Orders o')->leftJoin('o.Statuses s')->leftJoin('o.Customers c')->where("product_id = ?", $product_id)->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
return $records;
}
}
/**
* Get a list
*
* @param $id
* @return Doctrine Record
*/
public static function summary($userId)
{
$translator = Shineisp_Registry::getInstance()->Zend_Translate;
$dq = Doctrine_Query::create()->select("note_id, name, DATE_FORMAT(expire, '" . Settings::getMySQLDateFormat('dateformat') . "') as creation_date")->from('Notes n')->where("n.user_id = ?", $userId);
$auth = Zend_Auth::getInstance();
if ($auth->hasIdentity()) {
$logged_user = $auth->getIdentity();
$dq->leftJoin('n.AdminUser au')->whereIn("au.isp_id", $logged_user['isp_id']);
}
$records['data'] = $dq->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
$records['index'] = "note_id";
$records['fields'] = array('name' => array('label' => $translator->translate('Name')), 'creation_date' => array('label' => $translator->translate('Expiry Date')));
return $records;
}
