Esempio n. 1
0
 /**
 * Yield total percentage between periods
 *
 * @param array $yearsminmax [2010,2013]
 * @param string $groupby [year, month, quarter]
 * @return array
 *
 * This is a sample of the array result of this method:
 *
 * [0] => array(21) {
     ["invoice_id"] => string(3) "532"
     ["year"] => string(4) "2012"
     ["gross_grandtotal"] => string(8) "10802.53"
     ["gross_total"] => string(7) "9102.29"
     ["gross_vat"] => string(7) "1700.23"
     ["creditmemo_grandtotal"] => NULL
     ["creditmemo_total"] => NULL
     ["creditmemo_vat"] => NULL
     ["net_grandtotal"] => float(8470.35)
     ["net_total"] => float(7143.5)
     ["net_vat"] => float(1326.84)
     ["month"] => string(1) "4"
     ["monthname"] => string(5) "April"
     ["yieldrate"] => string(6) "602,60"
     ["incdec"] => float(6126.77)
     ["old"] => array(21) {
     ["invoice_id"] => string(2) "61"
     ["year"] => string(4) "2011"
     ["gross_grandtotal"] => string(7) "2465.52"
     ["gross_total"] => string(7) "2054.60"
     ["gross_vat"] => string(6) "410.92"
     ["creditmemo_grandtotal"] => NULL
     ["creditmemo_total"] => NULL
     ["creditmemo_vat"] => NULL
     ["net_grandtotal"] => float(1242.45)
     ["net_total"] => float(1016.73)
     ["net_vat"] => float(225.72)
     ["month"] => string(1) "4"
     ["monthname"] => string(5) "April"
     ["yieldrate"] => int(0)
     ["incdec"] => int(0)
     ["old"] => array(0) {
     }
     ["costs_total"] => string(7) "1037.87"
     ["costs_vat"] => string(6) "185.20"
     ["costs_grandtotal"] => string(7) "1223.07"
     ["output_vat"] => string(6) "410.92"
     ["input_vat"] => string(6) "185.20"
     }
     ["costs_total"] => string(7) "1958.79"
     ["costs_vat"] => string(6) "373.39"
     ["costs_grandtotal"] => string(7) "2332.18"
     ["output_vat"] => string(7) "1700.23"
     ["input_vat"] => string(6) "373.39"
     }
 * @return ArrayObject
 */
 public static function incomes(array $years = array(), $groupby = "year", $recursive = true)
 {
     $diff = 0;
     // Get the year incomes total and subtract the credit memo
     $dq = Doctrine_Query::create()->select("invoice_id, YEAR(i.invoice_date) as year, SUM(o.grandtotal) as gross_grandtotal, SUM(o.total) as gross_total, SUM(o.vat) as gross_vat,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   SUM(cm.total) as creditmemo_grandtotal, SUM(cm.total_net) as creditmemo_total, SUM(cm.total_vat) as creditmemo_vat,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   (SUM(o.grandtotal) - ifnull(SUM(cm.total), 0)) as net_grandtotal,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   (SUM(o.total) - ifnull(SUM(cm.total_net), 0)) as net_total,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t   (SUM(o.vat) - ifnull(SUM(cm.total_vat), 0)) as net_vat")->from('Invoices i')->leftJoin('i.Orders o')->leftJoin('o.Customers c')->leftJoin('i.CreditNotes cm')->where('o.status_id = ? OR o.status_id = ?', array(Statuses::id('paid', 'orders'), Statuses::id('complete', 'orders')))->andWhere('c.isp_id = ?', Isp::getCurrentId());
     // Group by the prefered fields
     if ($groupby == "month") {
         $dq->addSelect('MONTH(i.invoice_date) as month');
         $dq->addSelect('MONTHNAME(i.invoice_date) as monthname');
         $dq->groupBy("month, year")->orderBy('year, month');
     } elseif ($groupby == "quarter") {
         $dq->addSelect('QUARTER(i.invoice_date) as quarter');
         $dq->groupBy("quarter, year")->orderBy('year, quarter');
     } else {
         $dq->groupBy("year")->orderBy('year');
     }
     if (!empty($years[0]) && !empty($years[1])) {
         $dq->addWhere('YEAR(i.invoice_date) >= ?', $years[0]);
         $dq->addWhere('YEAR(i.invoice_date) <= ?', $years[1]);
     } elseif (!empty($years[0]) && is_numeric($years[0])) {
         $dq->addWhere('YEAR(i.invoice_date) = ?', $years);
     }
     $income = $dq->execute(null, Doctrine::HYDRATE_ARRAY);
     for ($i = 0; $i < count($income); $i++) {
         // Yield Percentage
         $income[$i]['yieldrate'] = 0;
         // Increment / Decrement
         $income[$i]['incdec'] = 0;
         // Before the last year
         $income[$i]['old'] = array();
         $income[$i]['costs_total'] = 0;
         $income[$i]['costs_vat'] = 0;
         $income[$i]['costs_grandtotal'] = 0;
         if ($groupby == "month") {
             // Load all the purchase invoices per month
             $costs = PurchaseInvoices::getSummary($income[$i]['year'], false, false, false, true);
             foreach ($costs as $out) {
                 if ($out['month'] == $income[$i]['month']) {
                     $income[$i]['costs_total'] = $out['total'];
                     $income[$i]['costs_vat'] = $out['vat'];
                     $income[$i]['costs_grandtotal'] = $out['grandtotal'];
                     $income[$i]['output_vat'] = $income[$i]['net_vat'];
                     $income[$i]['input_vat'] = $income[$i]['costs_vat'];
                     $income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
                     $income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
                     $income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
                     break;
                 }
             }
         } elseif ($groupby == "quarter") {
             // Load all the purchase invoices per quarter
             $costs = PurchaseInvoices::getSummary($income[$i]['year'], false, false, true);
             foreach ($costs as $out) {
                 if ($out['quarter'] == $income[$i]['quarter']) {
                     $income[$i]['costs_total'] = $out['total'];
                     $income[$i]['costs_vat'] = $out['vat'];
                     $income[$i]['costs_grandtotal'] = $out['grandtotal'];
                     $income[$i]['output_vat'] = $income[$i]['net_vat'];
                     $income[$i]['input_vat'] = $income[$i]['costs_vat'];
                     $income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
                     $income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
                     $income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
                     break;
                 }
             }
         } else {
             $costs = PurchaseInvoices::getSummary($income[$i]['year'], false, true);
             // Load all the purchase invoices per year
             foreach ($costs as $out) {
                 if ($out['year'] == $income[$i]['year']) {
                     $income[$i]['costs_total'] = $out['total'];
                     $income[$i]['costs_vat'] = $out['vat'];
                     $income[$i]['costs_grandtotal'] = $out['grandtotal'];
                     $income[$i]['output_vat'] = $income[$i]['net_vat'];
                     $income[$i]['input_vat'] = $income[$i]['costs_vat'];
                     $income[$i]['net_total'] = $income[$i]['net_total'] - $income[$i]['costs_total'];
                     $income[$i]['net_vat'] = $income[$i]['net_vat'] - $income[$i]['costs_vat'];
                     $income[$i]['net_grandtotal'] = $income[$i]['net_grandtotal'] - $income[$i]['costs_grandtotal'];
                     break;
                 }
             }
         }
         // For each Income read the old years values
         foreach ($income as $item) {
             // If the selected year is before last year
             if ($item['year'] == $income[$i]['year'] - 1) {
                 if ($groupby == "month") {
                     if ($income[$i]['month'] == $item['month']) {
                         if ($income[$i]['net_total'] > 0) {
                             $diff = $income[$i]['net_total'] - $item['net_total'];
                             $percent = $diff / $item['net_total'] * 100;
                         } else {
                             $percent = 0;
                         }
                         $income[$i]['old'] = $item;
                         // Assign the yield percentage value
                         $income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
                         $income[$i]['incdec'] = $diff;
                         // Increase / Decrease
                     }
                 } elseif ($groupby == "quarter") {
                     if ($income[$i]['quarter'] == $item['quarter']) {
                         if ($income[$i]['net_total'] > 0) {
                             $diff = $income[$i]['net_total'] - $item['net_total'];
                             $percent = $diff / $item['net_total'] * 100;
                         } else {
                             $percent = 0;
                         }
                         $income[$i]['old'] = $item;
                         // Assign the yield percentage value
                         $income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
                         $income[$i]['incdec'] = $diff;
                         // Increase / Decrease
                     }
                 } else {
                     // Calculate the Yield percentage on diff
                     if ($income[$i]['net_total'] > 0) {
                         $diff = $income[$i]['net_total'] - $item['net_total'];
                         $percent = $diff / $item['net_total'] * 100;
                     } else {
                         $percent = 0;
                     }
                     $income[$i]['old'] = $item;
                     // Assign the yield percentage value
                     $income[$i]['yieldrate'] = number_format($percent, 2, ',', '');
                     $income[$i]['incdec'] = $diff;
                     // Increase / Decrease
                 }
             }
         }
     }
     return !empty($income[0]) ? $income : array();
 }
Esempio n. 2
0
 /**
  * showAction
  * Create the reports page
  */
 public function showAction()
 {
     $request = $this->getRequest();
     $datatype = $request->getParam('type');
     $param = $request->getParam('q');
     $autorenew = 1;
     $links = "";
     switch ($datatype) {
         case 'profitofyear':
             if (is_numeric($param)) {
                 $links = $this->translator->_("Click one of these links to show the economic reports");
                 $years = Invoices::getYears();
                 foreach ($years as $year) {
                     $links .= " <a href='/admin/reports/show/type/profitofyear/q/{$year}'>{$year}</a>";
                 }
                 $this->view->title = $this->translator->_("Estimated Revenue for %s", $param);
                 if (!empty($years)) {
                     $this->view->description = $this->translator->_("Below is the economic summary of the %s.", $param) . " " . $links;
                 } else {
                     $this->view->description = $this->translator->_("Below is the economic summary of the %s.", $param);
                 }
                 $this->view->year = $param;
                 $graph = new Shineisp_Commons_Morris();
                 // Get the total of the revenues per year
                 $graphdata = $graph->setType('Area')->setData(Orders::prepareGraphData(array($param), 'month', false))->setElement('graph')->setXkey('xdata')->setLabels(array($this->translator->translate('Net Revenue (Taxable Income less Costs)')))->setOptions(array('lineColors' => array('#428BCA'), 'preUnits' => Settings::findbyParam('currency') . " "))->plot();
                 $this->view->placeholder("admin_endbody")->append($graphdata);
                 Invoices::getSummaryGrid($this->_helper, $param);
                 PurchaseInvoices::getSummaryGrid($this->_helper, $param);
             } else {
                 $this->_helper->redirector('show', 'reports', 'admin', array('type' => 'profitofyear', 'q' => date('Y')));
             }
             break;
         case 'productsummary':
             $years = Invoices::getYears();
             foreach ($years as $year) {
                 $links .= " <a href='/admin/reports/show/type/productsummary/q/{$year}'>{$year}</a>";
             }
             if (!empty($years)) {
                 $this->view->description = $this->translator->_("In this list you can see the summary of the products sold. %s", $param) . " <br/> " . $links . " <a href='/admin/reports/show/type/productsummary/'>" . $this->translator->translate('Show All') . "</a> ";
             } else {
                 $this->view->description = $this->translator->_("In this list you can see the summary of the products sold. %s", $param);
             }
             $this->view->title = $this->translator->translate("Products summary");
             $this->view->data = array('records' => Products::getBestseller($param));
             break;
         case 'tldsummarypermonth':
             $this->view->title = $this->translator->translate("Domain TLD monthly summary");
             $this->view->description = $this->translator->translate("In this list you can see the summary of the TLD per month.");
             $graph = new Shineisp_Commons_Morris();
             $data = Domains::prepareGraphDataperMonth();
             if (!empty($data)) {
                 // Get the total of the revenues per year
                 $graphdata = $graph->setType('Bar')->setData($data)->setElement('graph')->setXkey('xdata')->setLabels(array_keys($data['tld']))->plot();
                 $this->view->placeholder("admin_endbody")->append($graphdata);
             }
             $this->view->data = array('records' => Domains::getSummaryPerMonth());
             break;
         case 'domainstats':
             $this->view->title = $this->translator->translate("Domain stats");
             $this->view->description = $this->translator->translate("This list shows all the costs and earnings of the domains sold grouped by tld.");
             $graph = new Shineisp_Commons_Morris();
             // Get the tlds domains per type
             $graphdata = $graph->setType('Donut')->setData(Domains::prepareGraphData())->setElement('graph')->plot();
             $this->view->placeholder("admin_endbody")->append($graphdata);
             $this->view->data = array('records' => Domains::getSummary());
             break;
         case 'tldsummaryowner':
             $this->view->title = $this->translator->translate("Summary per client");
             $this->view->description = $this->translator->translate("By this list you can see the summary of the domains bought per client.");
             $this->view->data = array('records' => Domains::domains_per_customers(), 'pager' => true);
             break;
         case 'domainstasks':
             $this->view->title = $this->translator->translate("List of all domain tasks (last 100 records)");
             $this->view->description = $this->translator->translate("By this list you can know all the tasks for each created domain.");
             $this->view->graph = "";
             $this->view->data = array('records' => DomainsTasks::GetTask(100), 'delete' => array('controller' => 'reports', 'action' => 'deletetask'), 'pager' => true);
             break;
         case 'servicesummary':
             // get all the recurring products and services as default
             $groups = ProductsAttributesGroups::getList(null, true);
             if (!empty($groups)) {
                 $groups = array_keys($groups);
             }
             $groups = array('3', '9');
             $fields = "detail_id,\n\t\t\t\t\t\t\to.order_id as orderid,\n\t\t\t\t\t\t\tc.customer_id as customer_id,  \n\t\t\t\t\t\t\toid.relationship_id as relationship_id,\n\t\t\t\t\t\t\tDATE_FORMAT(oi.date_end, '%d/%m/%Y') as expiringdate,\n\t\t\t\t\t\t\td.autorenew as autorenew,\n\t\t\t\t\t\t\tCONCAT(c.firstname, ' ', c.lastname, ' ', c.company) as customer,\n\t\t\t\t\t\t\toi.description as description,\n\t\t\t\t\t\t\tCONCAT(d.domain, '.', d.tld) as domain,  \n\t\t\t\t\t\t\toi.cost as cost, \n\t\t\t\t\t\t\toi.price as price";
             $this->view->title = $this->translator->translate("List of the recurring services");
             $this->view->description = $this->translator->translate("By this list you can see the summary of the services bought per client.");
             $this->view->graph = "";
             $this->view->data = array('records' => OrdersItems::getAllRecurringServices($fields, $groups), 'pager' => true);
             break;
         case 'ticketsummary':
             $this->view->title = $this->translator->translate("Ticket summary");
             $this->view->description = $this->translator->translate("List of the last help requests.");
             $this->view->graph = "";
             $this->view->data = array('records' => Tickets::Last(), 'actions' => array('/admin/tickets/edit/id/' => 'show'), 'pager' => true);
             break;
         case 'domainsexpiration':
             $this->view->title = $this->translator->translate("Expiration list of domains");
             $this->view->description = $this->translator->translate("This view helps you to check which are the domains next to expiration.");
             $this->view->graph = "";
             $this->view->data = array('records' => Domains::getExpiringDomains(), 'actions' => array('/admin/domains/edit/id/' => 'show'), 'pager' => true);
             break;
         case 'servicesexpiration':
             $this->view->title = $this->translator->translate("Expiration list of services");
             $this->view->description = $this->translator->translate("This view helps you to check which are the services next to expiration.");
             $this->view->graph = "";
             $this->view->data = array('records' => Products::getExpiringProducts(), 'actions' => array('/admin/services/edit/id/' => 'show'), 'pager' => true);
             break;
         default:
             $this->_helper->redirector('show', 'reports', 'admin', array('type' => 'profitofyear', 'q' => date('Y')));
             break;
     }
 }
Esempio n. 3
0
 /**
  * Delete the file attached
  */
 public function deletefileAction()
 {
     $id = $this->getRequest()->getParam('id');
     if (PurchaseInvoices::DeleteAttachment($id)) {
         $this->_helper->redirector('edit', 'purchases', 'admin', array('id' => $id, 'mex' => $this->translator->translate('The task requested has been executed successfully.'), 'status' => 'success'));
     } else {
         $this->_helper->redirector('edit', 'purchases', 'admin', array('id' => $id, 'mex' => $this->translator->translate('The file cannot be found.'), 'status' => 'danger'));
     }
 }
Esempio n. 4
0
 /**
  * Save all the data
  * @param array $data
  */
 public static function saveAll($id, $data)
 {
     // Set the new values
     if (is_numeric($id)) {
         $invoice = self::find($id);
     } else {
         $invoice = new PurchaseInvoices();
     }
     $invoice['creationdate'] = Shineisp_Commons_Utilities::formatDateIn($data['creationdate']);
     $invoice['expiringdate'] = Shineisp_Commons_Utilities::formatDateIn($data['expiringdate']);
     $invoice['paymentdate'] = !empty($data['paymentdate']) ? Shineisp_Commons_Utilities::formatDateIn($data['paymentdate']) : "";
     $invoice['number'] = $data['number'];
     $invoice['company'] = $data['company'];
     $invoice['category_id'] = $data['category_id'];
     $invoice['method_id'] = $data['method_id'];
     $invoice['total_net'] = $data['total_net'];
     $invoice['total_vat'] = $data['total_vat'];
     $invoice['total'] = $data['total'];
     $invoice['note'] = $data['note'];
     $invoice['status_id'] = $data['status_id'];
     $invoice->save();
     self::UploadDocument($invoice['purchase_id']);
     return $invoice['purchase_id'];
 }