tonysa
-
Posts
4 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by tonysa
-
-
Quote
CREATE TABLE `order` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`delivery_address` varchar(256) NOT NULL,
`complex_unit_no` varchar(200) DEFAULT NULL,
`delivery_latitude` varchar(20) NOT NULL,
`delivery_longitude` varchar(20) NOT NULL,
`store_uid` int(10) unsigned NOT NULL,
`app_version` varchar(10) DEFAULT NULL,
`delivery_fee` varchar(10) NOT NULL,
`amended_delivery_fee` varchar(10) DEFAULT NULL,
`bottles_fee` varchar(10) NOT NULL,
`amended_bottles_fee` varchar(10) DEFAULT NULL,
`promo_code` varchar(50) NOT NULL,
`promo_discount` varchar(10) NOT NULL,
`promo_discount_amount` varchar(10) NOT NULL,
`promo_discount_type` varchar(10) NOT NULL,
`user_agent_string` varchar(512) DEFAULT NULL,
`amended_promo_code` varchar(50) DEFAULT NULL,
`amended_promo_discount` varchar(10) DEFAULT NULL,
`amended_promo_discount_amount` varchar(10) DEFAULT NULL,
`amended_promo_discount_type` varchar(10) DEFAULT NULL,
`delivery_notes` longtext NOT NULL,
`items_total` varchar(30) NOT NULL,
`amended_items_total` varchar(10) DEFAULT NULL,
`order_total` varchar(10) NOT NULL,
`this_order_total` varchar(10) DEFAULT NULL,
`amended_order_total` varchar(30) DEFAULT NULL,
`adjusted_items_total` varchar(30) DEFAULT NULL,
`adjusted_datetime` datetime DEFAULT NULL,
`adjusted_order_total` varchar(10) DEFAULT NULL,
`outstanding_amount` varchar(10) DEFAULT NULL,
`crc` varchar(10) NOT NULL,
`created_datetime` datetime NOT NULL,
`placed_datetime` datetime DEFAULT NULL,
`transaction_sequence` varchar(20) NOT NULL,
`user_uid` int(11) NOT NULL,
`status` varchar(3) DEFAULT NULL,
`accepted_datetime` datetime DEFAULT NULL,
`picking_datetime` datetime DEFAULT NULL,
`edi_order_no` varchar(50) DEFAULT NULL,
`edi_invoice_status` enum('NONE','QUEUED','PENDING','ERROR','SUCCESS','SENT') DEFAULT 'NONE',
`edi_invoice_no` varchar(50) DEFAULT NULL,
`edi_message_response` varchar(500) DEFAULT NULL,
`last_status_changed_datetime` datetime DEFAULT NULL,
`edi_credit_status` enum('NONE','QUEUED','PENDING','ERROR','SUCCESS','SENT') NOT NULL DEFAULT 'NONE',
`amended_datetime` datetime DEFAULT NULL,
`vendor_amended_datetime` datetime DEFAULT NULL,
`vendor_viewed_datetime` datetime DEFAULT NULL,
`user_amending_datetime` datetime DEFAULT NULL,
`user_substituted` enum('Y','N') DEFAULT 'N',
`rating` varchar(1) DEFAULT NULL,
`rating_comment` varchar(2000) DEFAULT NULL,
`confirmed_datetime` datetime DEFAULT NULL,
`delivered_datetime` datetime DEFAULT NULL,
`delivery_integration_enabled` enum('Y','N') DEFAULT 'N',
`paid_in_full` enum('Y','N') DEFAULT 'N',
`driver_name` varchar(80) DEFAULT NULL,
`driver_contact_no` varchar(40) DEFAULT NULL,
`invoice_no` varchar(20) DEFAULT NULL,
`ppay_requested` enum('Y','N') NOT NULL DEFAULT 'N',
`ppay_payment_id` varchar(50) DEFAULT NULL,
`driver_cancelled` enum('Y','N') NOT NULL DEFAULT 'N',
`bottles_notes` varchar(150) DEFAULT NULL,
`bottles_agent_name` varchar(50) DEFAULT NULL,
`driver_started_delivery` enum('Y','N') NOT NULL DEFAULT 'N',
`driver_tracking_link` varchar(300) DEFAULT NULL,
`vendor_need_help` enum('Y','N') DEFAULT 'N',
`has_vendor_viewed_order` enum('Y','N') DEFAULT 'N',
`picking_slip_pn_sent` enum('NONE','PICKING_SLIP','AMENDED_PICKING_SLIP') DEFAULT 'NONE',
`vendor_ready_for_driver` enum('Y','N') DEFAULT 'N',
`driver_ready_datetime` datetime DEFAULT NULL,
`driver_status` enum('CANCELLED','DECLINED','NONE','FAILED TO DELIVER') DEFAULT 'NONE',
`vendor_driver_collected` enum('Y','N') DEFAULT 'N',
`delivery_integration_service_name` enum('NONE','JARVIS','TOOKAN','PICUP') DEFAULT 'NONE',
`show_driver_tracking` enum('Y','N') DEFAULT 'N',
`payment_method` enum('3DS','DB','NONE') DEFAULT 'NONE',
`possible_fraud` enum('Y','N') DEFAULT 'N',
`invoice_status` enum('SENT','NOT_SENT') DEFAULT 'NOT_SENT',
`driver_tip` decimal(10,2) DEFAULT '0.00',
`order_type` enum('GV','OD','MO') DEFAULT 'OD',
`picker_name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`uid`),
UNIQUE KEY `transaction_sequence_user_uid` (`transaction_sequence`,`user_uid`),
KEY `store_uid` (`store_uid`),
KEY `user_uid` (`user_uid`),
KEY `idx_created_datetime` (`created_datetime`),
KEY `store_and_status` (`store_uid`,`status`),
KEY `status` (`status`),
KEY `store_user_status` (`store_uid`,`user_uid`,`status`),
KEY `promocode_status` (`promo_code`,`status`),
KEY `user_promcode_status` (`user_uid`,`promo_code`,`status`),
KEY `edi_order_no` (`edi_order_no`),
KEY `voucher_payment` (`order_type`,`status`,`edi_invoice_status`)
) ENGINE=InnoDB AUTO_INCREMENT=846063 DEFAULT CHARSET=utf8;
-
I have the following code that i need to alter to add additional time metrics such as:
Document Number, Status,
Time Elapsed Minutes, Created Date, Created Time, Accepted By Store,
Minutes To Accepted By Store, Invoiced At, Mins Elapsed, Invoiced,
Driver Accepted, Mins Driver Accepted, Driver At Store Mins Driving To Store,
,Mins Driver In Store, Driver Starts Delivery,
,Driver Arrival At Client,Mins Driving To Client,
Driver Completes Delivery,
Total Mins Elapsed To Delivery.Please assist?
SELECT DATE(o.created_datetime) as Date, o.store_uid as 'Store UID'
, o.uid as 'Order'
, o.rating as 'Rating'
, IF(o.accepted_datetime < o.driver_ready_datetime, TRUE, FALSE) as 'Switch'
, timestampdiff(minute, o.created_datetime, o.accepted_datetime) as 'Accepted Order'
, if(accepted_datetime < driver_ready_datetime, timestampdiff(minute, o.accepted_datetime, o.driver_ready_datetime), timestampdiff(minute, o.created_datetime, o.driver_ready_datetime)) as'Invoiced Order'
, timestampdiff(minute, o.created_datetime, o.delivered_datetime) as 'Delivery Time'
, ROUND(if(o.amended_items_total IS NOT NULL, o.amended_items_total, o.items_total)
+ if(o.amended_bottles_fee IS NOT NULL, o.amended_bottles_fee, o.bottles_fee)
+ if(o.amended_delivery_fee IS NOT NULL, o.amended_delivery_fee, o.delivery_fee),2) as 'Turnover'
FROM `order` o
WHERE o.status = 'D' AND NOT store_uid IN (6,56, 202)
AND date(o.created_datetime) >= '2020-11-01' -
I would like to connect to a mysql database that is a localhost connection and not on a internet service provider.
How do i connect? Do you use "localhost or your ip"?I tried it and it does not work. Also, how do i open a port for access? Is the port connections blocking me from succeeding regarding this?
Time Metrics/Order Detail -MSSQL
in SQL
Posted