· 7 years ago · Nov 25, 2018, 07:58 AM
1SELECT so.*
2FROM
3 (SELECT email.value email, so.entity_id
4 FROM sales_order so
5 LEFT JOIN sales_order_varchar email
6 ON (email.entity_id = so.entity_id AND email.attribute_id = 197)
7 WHERE subtotal > total_refunded
8 AND subtotal_canceled IS NULL
9 AND created_at > '2011-08-01'
10 AND email.value != ''
11 ) AS sq
12LEFT JOIN sales_order so
13 ON so.entity_id = sq.entity_id
14WHERE so.entity_id IS NULL
15
16--
17-- Table structure for table `sales_order`
18--
19
20CREATE TABLE IF NOT EXISTS `sales_order` (
21 `entity_id` int(10) unsigned NOT NULL auto_increment,
22 `entity_type_id` smallint(5) unsigned NOT NULL default '0',
23 `attribute_set_id` smallint(5) unsigned NOT NULL default '0',
24 `increment_id` varchar(50) NOT NULL default '',
25 `parent_id` int(10) unsigned NOT NULL default '0',
26 `store_id` smallint(5) unsigned default NULL,
27 `created_at` datetime NOT NULL default '0000-00-00 00:00:00',
28 `updated_at` datetime NOT NULL default '0000-00-00 00:00:00',
29 `is_active` tinyint(1) unsigned NOT NULL default '1',
30 `customer_id` int(10) unsigned default NULL,
31 `tax_amount` decimal(12,4) NOT NULL default '0.0000',
32 `shipping_amount` decimal(12,4) NOT NULL default '0.0000',
33 `discount_amount` decimal(12,4) NOT NULL default '0.0000',
34 `subtotal` decimal(12,4) NOT NULL default '0.0000',
35 `grand_total` decimal(12,4) NOT NULL default '0.0000',
36 `total_paid` decimal(12,4) NOT NULL default '0.0000',
37 `total_refunded` decimal(12,4) NOT NULL default '0.0000',
38 `total_qty_ordered` decimal(12,4) NOT NULL default '0.0000',
39 `total_canceled` decimal(12,4) NOT NULL default '0.0000',
40 `total_invoiced` decimal(12,4) NOT NULL default '0.0000',
41 `total_online_refunded` decimal(12,4) NOT NULL default '0.0000',
42 `total_offline_refunded` decimal(12,4) NOT NULL default '0.0000',
43 `base_tax_amount` decimal(12,4) NOT NULL default '0.0000',
44 `base_shipping_amount` decimal(12,4) NOT NULL default '0.0000',
45 `base_discount_amount` decimal(12,4) NOT NULL default '0.0000',
46 `base_subtotal` decimal(12,4) NOT NULL default '0.0000',
47 `base_grand_total` decimal(12,4) NOT NULL default '0.0000',
48 `base_total_paid` decimal(12,4) NOT NULL default '0.0000',
49 `base_total_refunded` decimal(12,4) NOT NULL default '0.0000',
50 `base_total_qty_ordered` decimal(12,4) NOT NULL default '0.0000',
51 `base_total_canceled` decimal(12,4) NOT NULL default '0.0000',
52 `base_total_invoiced` decimal(12,4) NOT NULL default '0.0000',
53 `base_total_online_refunded` decimal(12,4) NOT NULL default '0.0000',
54 `base_total_offline_refunded` decimal(12,4) NOT NULL default '0.0000',
55 `subtotal_refunded` decimal(12,4) default NULL,
56 `subtotal_canceled` decimal(12,4) default NULL,
57 `discount_refunded` decimal(12,4) default NULL,
58 `discount_canceled` decimal(12,4) default NULL,
59 `discount_invoiced` decimal(12,4) default NULL,
60 `tax_refunded` decimal(12,4) default NULL,
61 `tax_canceled` decimal(12,4) default NULL,
62 `shipping_refunded` decimal(12,4) default NULL,
63 `shipping_canceled` decimal(12,4) default NULL,
64 `base_subtotal_refunded` decimal(12,4) default NULL,
65 `base_subtotal_canceled` decimal(12,4) default NULL,
66 `base_discount_refunded` decimal(12,4) default NULL,
67 `base_discount_canceled` decimal(12,4) default NULL,
68 `base_discount_invoiced` decimal(12,4) default NULL,
69 `base_tax_refunded` decimal(12,4) default NULL,
70 `base_tax_canceled` decimal(12,4) default NULL,
71 `base_shipping_refunded` decimal(12,4) default NULL,
72 `base_shipping_canceled` decimal(12,4) default NULL,
73 `subtotal_invoiced` decimal(12,4) default NULL,
74 `tax_invoiced` decimal(12,4) default NULL,
75 `shipping_invoiced` decimal(12,4) default NULL,
76 `base_subtotal_invoiced` decimal(12,4) default NULL,
77 `base_tax_invoiced` decimal(12,4) default NULL,
78 `base_shipping_invoiced` decimal(12,4) default NULL,
79 `shipping_tax_amount` decimal(12,4) default NULL,
80 `base_shipping_tax_amount` decimal(12,4) default NULL,
81 `shipping_tax_refunded` decimal(12,4) default NULL,
82 `base_shipping_tax_refunded` decimal(12,4) default NULL,
83 `udropship_status` tinyint(4) NOT NULL,
84 PRIMARY KEY (`entity_id`),
85 KEY `FK_SALES_ORDER_TYPE` (`entity_type_id`),
86 KEY `FK_SALES_ORDER_STORE` (`store_id`),
87 KEY `IDX_CUSTOMER` (`customer_id`),
88 KEY `IDX_INCREMENT_ID` (`increment_id`),
89 KEY `udropship_status` (`udropship_status`)
90) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=13519 ;
91
92--
93-- Dumping data for table `sales_order`
94--
95
96INSERT INTO `sales_order` (`entity_id`, `entity_type_id`, `attribute_set_id`, `increment_id`, `parent_id`, `store_id`, `created_at`, `updated_at`, `is_active`, `customer_id`, `tax_amount`, `shipping_amount`, `discount_amount`, `subtotal`, `grand_total`, `total_paid`, `total_refunded`, `total_qty_ordered`, `total_canceled`, `total_invoiced`, `total_online_refunded`, `total_offline_refunded`, `base_tax_amount`, `base_shipping_amount`, `base_discount_amount`, `base_subtotal`, `base_grand_total`, `base_total_paid`, `base_total_refunded`, `base_total_qty_ordered`, `base_total_canceled`, `base_total_invoiced`, `base_total_online_refunded`, `base_total_offline_refunded`, `subtotal_refunded`, `subtotal_canceled`, `discount_refunded`, `discount_canceled`, `discount_invoiced`, `tax_refunded`, `tax_canceled`, `shipping_refunded`, `shipping_canceled`, `base_subtotal_refunded`, `base_subtotal_canceled`, `base_discount_refunded`, `base_discount_canceled`, `base_discount_invoiced`, `base_tax_refunded`, `base_tax_canceled`, `base_shipping_refunded`, `base_shipping_canceled`, `subtotal_invoiced`, `tax_invoiced`, `shipping_invoiced`, `base_subtotal_invoiced`, `base_tax_invoiced`, `base_shipping_invoiced`, `shipping_tax_amount`, `base_shipping_tax_amount`, `shipping_tax_refunded`, `base_shipping_tax_refunded`, `udropship_status`) VALUES
97(1, 11, 0, '400000001', 0, 4, '2010-04-07 17:42:03', '2010-04-08 14:25:55', 1, NULL, '0.0000', '0.0000', '0.0000', '0.0100', '0.0100', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0100', '0.0100', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', NULL, '0.0100', NULL, '0.0000', NULL, NULL, '0.0000', NULL, '0.0000', NULL, '0.0100', NULL, '0.0000', NULL, NULL, '0.0000', NULL, '0.0000', NULL, NULL, NULL, NULL, NULL, NULL, '0.0000', '0.0000', NULL, NULL, 0),
98(2, 11, 0, '400000002', 0, 4, '2010-04-07 17:43:14', '2010-05-05 14:43:52', 1, NULL, '0.0000', '9.9400', '0.0000', '0.0100', '9.9500', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '9.9400', '0.0000', '0.0100', '9.9500', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', NULL, '0.0100', NULL, '0.0000', NULL, NULL, '0.0000', NULL, '9.9400', NULL, '0.0100', NULL, '0.0000', NULL, NULL, '0.0000', NULL, '9.9400', NULL, NULL, NULL, NULL, NULL, NULL, '0.0000', '0.0000', NULL, NULL, 0)
99
100--
101-- Table structure for table `sales_order_varchar`
102--
103
104CREATE TABLE IF NOT EXISTS `sales_order_varchar` (
105 `value_id` int(11) NOT NULL auto_increment,
106 `entity_type_id` smallint(5) unsigned NOT NULL default '0',
107 `attribute_id` smallint(5) unsigned NOT NULL default '0',
108 `entity_id` int(10) unsigned NOT NULL default '0',
109 `value` varchar(255) NOT NULL default '',
110 PRIMARY KEY (`value_id`),
111 UNIQUE KEY `UNQ_ENTITY_ATTRIBUTE_TYPE` (`entity_id`,`attribute_id`,`entity_type_id`),
112 KEY `FK_SALES_ORDER_VARCHAR_ENTITY_TYPE` (`entity_type_id`),
113 KEY `FK_SALES_ORDER_VARCHAR_ATTRIBUTE` (`attribute_id`),
114 KEY `FK_SALES_ORDER_VARCHAR` (`entity_id`)
115) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=181535 ;
116
117--
118-- Dumping data for table `sales_order_varchar`
119--
120
121INSERT INTO `sales_order_varchar` (`value_id`, `entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
122(1, 11, 105, 1, '208.157.175.242'),
123(2, 11, 197, 1, ''),
124(3, 11, 125, 1, 'USD'),
125(4, 11, 126, 1, 'USD'),
126(5, 11, 127, 1, 'USD'),
127(6, 11, 128, 1, 'USD'),
128(7, 11, 122, 1, '123321'),
129(8, 11, 123, 1, '1,6'),
130(9, 11, 134, 1, 'usps_Priority Mail'),
131(10, 11, 135, 1, 'United States Postal Service - Priority Mail'),
132(11, 11, 107, 1, 'canceled'),
133(12, 11, 106, 1, 'canceled'),
134(13, 11, 104, 1, 'Main Website - Probiotic Smart\nPSC Pets Store\nPSCPets.com'),
135(14, 11, 105, 2, '208.157.175.242'),
136(15, 11, 197, 2, ''),
137(16, 11, 125, 2, 'USD'),
138(17, 11, 126, 2, 'USD'),
139(18, 11, 127, 2, 'USD'),
140(19, 11, 128, 2, 'USD'),
141(20, 11, 123, 2, '6'),
142(21, 11, 134, 2, 'fedex_GROUNDHOMEDELIVERY'),
143(22, 11, 135, 2, 'Federal Express - Home Delivery'),
144(23, 11, 107, 2, 'canceled'),
145(24, 11, 106, 2, 'canceled'),
146(25, 11, 104, 2, 'Main Website - Probiotic Smart\nPSC Pets Store\nPSCPets.com'),
147(26, 11, 105, 3, '208.157.175.242'),
148(27, 11, 197, 3, 'robz@vets-plus.com'),
149(28, 11, 199, 3, 'Rob'),
150(29, 11, 201, 3, 'Zimmerman'),
151(30, 11, 125, 3, 'USD'),
152(31, 11, 126, 3, 'USD'),
153(32, 11, 127, 3, 'USD'),
154(33, 11, 128, 3, 'USD'),
155(34, 11, 122, 3, '123321'),
156(35, 11, 123, 3, '1,6'),
157(36, 11, 134, 3, 'usps_Priority Mail'),
158(37, 11, 135, 3, 'United States Postal Service - Priority Mail'),
159(38, 11, 107, 3, 'complete'),
160(39, 11, 106, 3, 'complete'),
161(40, 11, 104, 3, 'Main Website - Probiotic Smart\nPSC Pets Store\nPSCPets.com'),