UPDATE `hb_configuration` SET `value` = '4.0.0' WHERE `setting`= 'Version';
##########
ALTER TABLE `hb_transactions` ADD COLUMN `refund_of` INT NULL AFTER `rate`;
##########
ALTER TABLE `hb_cancel_requests` CHANGE COLUMN `type` `type` ENUM('Immediate','End of billing period', 'Other') NOT NULL AFTER `reason`;
##########
UPDATE `hb_language_locales` SET `value`='Statistics & Reports' WHERE `section`='global' AND `keyword`='systemstatistics';
##########
INSERT INTO `hb_reports` (`id`, `type`, `name`, `query`, `options`) VALUES
(1, 'Orders', 'Orders list in selected period', 'SELECT\r\n	o.id as `Order ID`,\r\n	o.number as `Order Number`,\r\n	COALESCE(m.modname,''None'') AS `Payment Gateway`,\r\n	o.client_id AS `Client ID`,\r\n	ca.email AS `Client Email`,\r\n	CONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\n	hb_currency(o.total) AS `Order Total`,\r\n	o.order_ip AS `Order IP`,\r\n	hb_date(o.date_created) AS `Order Date`,\r\n	o.status AS `Current Status`,\r\n	COALESCE(cp.code,''None'') AS `Promotional Coupon`,\r\n	COALESCE(clog.discount,''0.00'') AS `Discount`\r\n\r\n\r\nFROM\r\n	hb_orders o JOIN\r\n	hb_modules_configuration m ON (m.id=o.payment_module) JOIN\r\n	hb_client_details cd ON (cd.id = o.client_id) JOIN\r\n	hb_client_access ca ON (ca.id=cd.id) LEFT JOIN\r\n	hb_coupons_log clog ON (clog.order_id=o.id) LEFT JOIN\r\n	hb_coupons cp ON (cp.id=clog.coupon_id)\r\nWHERE\r\n	o.date_created > :date_bottom	  AND\r\n	o.date_created < :date_top\r\n\r\n\r\nORDER BY\r\n	o.date_created ASC', 1),
(2, 'Invoices', 'List of all invoices in given period', 'SELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total,i.currency_id,i.rate) AS `Invoice Total`,\r\ni.status AS `Invoice Status`,\r\nhb_date(i.date) AS `Invoice Date`,\r\nhb_currency(i.credit,i.currency_id,i.rate) AS `Invoice Credit`,\r\nm.modname AS `Gateway Name`,\r\ntaxrate as `Tax1 rate`,\r\ntaxrate2 as `Tax2 rate`,\r\ntax as `Tax1 Amount`,\r\ntax2 as `Tax2 Amount`,\r\ni.rate AS `Exchange Rate`,\r\nhb_date(i.duedate) AS `Invoice Due Date`,\r\nhb_date(i.datepaid) AS `Invoice Paid Date`,\r\nt.trans_id AS `Related transaction`,\r\nca.email AS `Client Email`,\r\nd.phonenumber AS `Client Phone`,\r\nd.companyname  AS `Client Company`,\r\nd.city AS `Client City`,\r\nd.country AS `Client Country`,\r\nd.address1 AS `Client Address`,\r\nd.postcode AS `Client ZIP`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_currencies c ON (c.id=i.currency_id)\r\n    LEFT JOIN hb_transactions t ON (t.invoice_id=i.id)\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    ) \r\n    AND i.status NOT IN (''Draft'',''Recurring'')\r\n    AND i.datepaid>= :date_bottom\r\n    AND i.datepaid<= :date_top\r\n\r\nORDER BY\r\n	i.id ASC', 1),
(3, 'Clients', 'Top 15 customers by income', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\nhb_currency(SUM(t.`in`),cb.currency_id) AS `Total Income`\r\nFROM hb_client_details cd\r\nJOIN hb_client_billing cb ON (cb.client_id=cd.id)\r\nJOIN hb_transactions t ON (t.client_id=cd.id)\r\nGROUP BY cd.id\r\nORDER BY SUM(t.`in`) DESC\r\nLIMIT 15', 1),
(4, 'Support', 'Average number of ticket replies per department', 'SELECT\r\nd.name AS `Department`,\r\n(a.tot/COUNT(t.id)) AS `Average replies`\r\nFROM hb_ticket_departments d JOIN\r\n(\r\n SELECT COUNT(r.id) as tot, t.dept_id FROM hb_ticket_replies r JOIN hb_tickets t ON (t.id=r.ticket_id)\r\n GROUP BY t.dept_id\r\n) a ON (a.dept_id=d.id)\r\nJOIN hb_tickets t ON (t.dept_id=d.id)\r\nGROUP BY t.dept_id\r\n', 1),
(5, 'Support', 'Unresolved tickets count by staff member', 'SELECT aa.id AS `Staff ID` , CONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member` , COUNT( t.id ) AS `Unresolved Tickets Count`\r\nFROM hb_admin_access aa\r\nJOIN hb_admin_details ad ON ( aa.id = ad.id )\r\nJOIN hb_tickets t ON ( t.owner_id = aa.id )\r\nWHERE t.status!=''Closed''\r\nGROUP BY t.owner_id', 1),
(6, 'Support', 'Unresolved tickets count by department member', 'SELECT d.id AS `Department ID` , d.name AS `Department Name` , COUNT( t.id ) AS `Unresolved Tickets Count`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nGROUP BY t.dept_id', 1),
(7, 'Clients', 'List of clients that are Active, but dont have any active service', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`\r\nFROM hb_client_details cd\r\nWHERE cd.id NOT IN (SELECT client_id FROM hb_accounts WHERE status=''Active'')\r\nAND cd.id NOT IN (SELECT client_id FROM hb_domains WHERE status=''Active'')\r\nAND cd.id NOT IN (SELECT a.client_id FROM hb_accounts a JOIN hb_accounts_addons ad ON (ad.account_id=a.id) WHERE ad.status=''Active'')\r\nORDER BY cd.id ASC', 1),
(8, 'Clients', 'List of clients with active services, with inactive profile (disabled login)', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`\r\nFROM hb_client_details cd\r\nJOIN hb_client_access ca ON (ca.id=cd.id)\r\nWHERE (cd.id IN (SELECT client_id FROM hb_accounts WHERE status=''Active'')\r\nOR cd.id IN (SELECT client_id FROM hb_domains WHERE status=''Active'')\r\nOR cd.id IN  (SELECT a.client_id FROM hb_accounts a JOIN hb_accounts_addons ad ON (ad.account_id=a.id) WHERE ad.status=''Active''))\r\nAND ca.status!=''Active''\r\nORDER BY cd.id ASC', 1),
(9, 'Clients', 'List of possible duplicates in client profiles', 'SELECT\r\ncd.id AS `Client ID`,\r\nCONCAT(cd.firstname,'' '',cd.lastname) AS `Client`,\r\nca.email AS `Client Email`\r\nFROM hb_client_details cd\r\nJOIN hb_client_access ca ON (ca.id=cd.id)\r\nINNER JOIN (\r\n SELECT firstname, lastname FROM hb_client_details GROUP BY firstname,lastname HAVING COUNT(id)>1\r\n) dup ON (dup.firstname=cd.firstname AND dup.lastname=cd.lastname)\r\nORDER BY cd.firstname,cd.lastname ASC', 1),
(10, 'Orders', 'Total transaction fees by gateway', 'SELECT\r\nm.modname AS `Gateway Name`,\r\nhb_currency(SUM(t.fee),t.currency_id,t.rate) AS `Total Fee`\r\nFROM hb_modules_configuration m\r\nJOIN hb_transactions t ON (t.module=m.id)\r\nGROUP BY t.module', 1),
(11, 'Orders', 'Total transactions count by gateway', 'SELECT\r\nm.modname AS `Gateway Name`,\r\nCOUNT(t.id) AS `Transactions count`\r\nFROM hb_modules_configuration m\r\nJOIN hb_transactions t ON (t.module=m.id)\r\nGROUP BY t.module', 1),
(12, 'Support', 'List of tickets due today (based on SLA)', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`,\r\nt.resolve_date AS `Ticket Due Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nAND\r\nt.resolve_date!=''0000-00-00 00:00:00''\r\nAND\r\nDATE(t.resolve_date)=DATE(NOW())', 1),
(13, 'Support', 'List of overdue tickets (based on SLA)', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`,\r\nt.resolve_date AS `Ticket Due Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.status != ''Closed''\r\nAND\r\nt.resolve_date!=''0000-00-00 00:00:00''\r\nAND\r\nt.resolve_date < NOW()', 1),
(14, 'Support', 'List of tickets rated less than 3 points this month', 'SELECT\r\nt.subject AS `Ticket Subject`,\r\nt.ticket_number AS `Ticket ID`,\r\nd.name AS `Department`,\r\nt.status AS `Ticket Status`,\r\nt.name AS `Submitter Name`,\r\nt.email AS `Submitter Email`,\r\nt.`date` AS `Ticket Date`\r\nFROM hb_tickets t\r\nJOIN hb_ticket_departments d ON ( t.dept_id = d.id )\r\nWHERE t.id IN (\r\n    SELECT r.ticket_id FROM hb_ticket_replies r JOIN  `hb_ticket_replies_rating` rate ON (rate.reply_id=r.id)\r\n    WHERE rate.rating<3\r\n)\r\nORDER BY t.date ASC', 1),
(15, 'Invoices', 'List of overdue invoices', '\r\nSELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total,i.currency_id,i.rate) AS `Invoice Total`,\r\ni.status AS `Invoice Status`,\r\ni.duedate AS `Invoice Due Date`,\r\nm.modname AS `Gateway Name`,\r\ntaxrate as `Tax1 rate`,\r\ntaxrate2 as `Tax2 rate`,\r\ntax as `Tax1 Amount`,\r\ntax2 as `Tax2 Amount`,\r\ni.rate AS `Exchange Rate`,\r\ni.date AS `Invoice Date`,\r\nca.email AS `Client Email`,\r\nd.phonenumber AS `Client Phone`,\r\nd.companyname  AS `Client Company`,\r\nd.city AS `Client City`,\r\nd.country AS `Client Country`,\r\nd.address1 AS `Client Address`,\r\nd.postcode AS `Client ZIP`,\r\nd.id AS `Client ID`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_currencies c ON (c.id=i.currency_id)\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    )\r\n    AND i.status IN (''Unpaid'')\r\n    AND i.duedate >= NOW()\r\n\r\nORDER BY\r\n	i.id ASC', 1),
(16, 'Staff', 'Hours spent online by staff this week', '\r\nSELECT\r\nl.admin_id AS `Staff ID`,\r\nCONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member`,\r\nSUM(TIME_TO_SEC(TIMEDIFF(l.logout,l.login)))/3600 as `Hours Online`\r\nFROM hb_admin_log l \r\nJOIN hb_admin_details ad ON (ad.id=l.admin_id)\r\nWHERE\r\n YEARWEEK(l.login) = YEARWEEK(NOW())\r\n GROUP BY admin_id', 1),
(17, 'Staff', 'Hours spent online by staff this month', '\r\nSELECT\r\nl.admin_id AS `Staff ID`,\r\nCONCAT( ad.firstname, '' '', ad.lastname ) AS `Staff Member`,\r\nSUM(TIME_TO_SEC(TIMEDIFF(l.logout,l.login)))/3600 as `Hours Online`\r\nFROM hb_admin_log l\r\nJOIN hb_admin_details ad ON (ad.id=l.admin_id)\r\nWHERE\r\n MONTH(l.login) = MONTH(NOW())\r\nAND\r\n YEAR(l.login) = YEAR(NOW())\r\n GROUP BY admin_id', 1),
(18, 'Support', 'Number of support ticket opened per service in given period', 'SELECT\r\nc.name AS `Product Category`,\r\np.name AS `Product Name`,\r\nCOUNT(t.id) AS `Tickets count`\r\nFROM hb_products p\r\nJOIN hb_categories c ON (c.id=p.category_id)\r\nJOIN hb_accounts a ON (a.product_id=p.id)\r\nJOIN hb_tickets t ON (t.client_id=a.client_id)\r\nWHERE\r\n    t.date >= :date_bottom\r\n    AND t.date <= :date_top\r\nGROUP BY p.id\r\n', 1),
(19, 'Invoices', 'Income from manually created invoices in given period', 'SELECT\r\n hb_currency(SUM(t.`in`),t.currency_id,t.rate) AS `Total Income`\r\nFROM\r\n hb_transactions t\r\n JOIN hb_invoices i ON (t.invoice_id=i.id)\r\n WHERE\r\n i.id IN (SELECT invoice_id FROM hb_invoice_items WHERE  `type`=''Other'')\r\n AND\r\n    t.date >= :date_bottom\r\n    AND t.date <= :date_top\r\n', 1),
(20, 'Invoices', 'List of overdue but non-terminated recurring accounts', 'SELECT\r\n a.id AS `Account ID`,\r\n c.name AS `Category Name`,\r\n p.name AS `Product Name`,\r\n a.status AS `Account Status`,\r\n a.next_due AS `Account next due date`\r\n\r\nFROM hb_accounts a\r\n JOIN hb_products p ON (p.id=a.product_id)\r\n JOIN hb_categories c ON (c.id=p.category_id)\r\nWHERE\r\n a.status NOT IN (''Terminated'',''Cancelled'',''Fraud'',''Pending'')\r\nAND\r\n a.billingcycle NOT IN (''Free'',''One Time'')\r\nAND\r\n a.id IN (\r\n SELECT it.item_id FROM hb_invoice_items it JOIN hb_invoices i ON (i.id=it.invoice_id)\r\n WHERE it.type=''Hosting'' AND i.status=''Unpaid''\r\n)', 1),
(21, 'Invoices', 'Total sales tax liability in selected period', 'SELECT\r\n hb_currency(SUM(i.tax)) AS `Total Tax L1`,\r\n hb_currency(SUM(i.tax2)) AS `Total Tax L2`,\r\n hb_currency(SUM(i.total)) AS `Invoice totals`,\r\n hb_currency(SUM(i.subtotal)) AS `Invoice subtotals`,\r\n hb_currency(SUM(i.credit)) AS `Invoice credits`\r\nFROM hb_invoices i\r\n WHERE i.status=''Paid''\r\n AND  i.datepaid>= :date_bottom\r\n    AND i.datepaid<= :date_top', 1),
(22, 'Invoices', 'Sales tax liability in selected period, converted to main currency', '\r\nSELECT\r\ni.id AS `Invoice ID`,\r\nCONCAT(d.firstname,'' '',d.lastname) AS `Client`,\r\nhb_currency(i.total) AS `Invoice Subtotal`,\r\nhb_currency(i.credit) AS `Invoice Credit`,\r\nhb_currency(i.tax) AS `Invoice Tax L1`,\r\nhb_currency(i.tax2) AS `Invoice Tax L2`,\r\nhb_currency(i.total) AS `Invoice Total`,\r\nhb_date(i.datepaid) AS `Invoice Paid Date`,\r\nhb_date(i.date) AS `Invoice Date`,\r\nm.modname AS `Gateway Name`,\r\nt.trans_id AS `Related transaction`\r\n\r\nFROM hb_invoices i\r\n    JOIN hb_client_details d ON (i.client_id=d.id)\r\n    JOIN hb_client_access ca ON (i.client_id=ca.id)\r\n    LEFT JOIN hb_modules_configuration m ON (m.id=i.payment_module)\r\n    LEFT JOIN hb_transactions t ON (t.invoice_id=i.id)\r\nWHERE\r\n    i.id NOT IN (\r\n        SELECT invoice_id\r\n        FROM hb_invoice_items\r\n        WHERE `type` = ''Invoice''\r\n    )\r\n    AND i.status IN (''Paid'')\r\n    AND i.datepaid>= :date_bottom\r\n    AND i.datepaid<= :date_top\r\n\r\nORDER BY\r\n	i.id ASC', 1);