11 |
11 |
|
12 |
12 |
use Rose::Object::MakeMethods::Generic (
|
13 |
13 |
scalar => [ qw(db_args flat_filter) ],
|
14 |
|
'scalar --get_set_init' => [ qw(models) ],
|
|
14 |
'scalar --get_set_init' => [ qw(models all_edit_right) ],
|
15 |
15 |
);
|
16 |
16 |
|
17 |
|
__PACKAGE__->run_before(sub { $::auth->assert('sales_order_edit'); });
|
|
17 |
__PACKAGE__->run_before(sub { $::auth->assert('delivery_plan'); });
|
18 |
18 |
|
19 |
19 |
my %sort_columns = (
|
20 |
20 |
reqdate => t8('Reqdate'),
|
... | ... | |
27 |
27 |
customer => t8('Customer'),
|
28 |
28 |
);
|
29 |
29 |
|
30 |
|
my $delivery_plan_query = [
|
31 |
|
'order.customer_id' => { gt => 0 },
|
32 |
|
'order.closed' => 0,
|
33 |
|
or => [ 'order.quotation' => 0, 'order.quotation' => undef ],
|
34 |
|
|
35 |
|
# filter by shipped_qty < qty, read from innermost to outermost
|
36 |
|
'id' => [ \"
|
37 |
|
-- 3. resolve the desired information about those
|
38 |
|
SELECT oi.id FROM (
|
39 |
|
-- 2. slice only part, orderitem and both quantities from it
|
40 |
|
SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM (
|
41 |
|
-- 1. join orderitems and deliverorder items via record_links.
|
42 |
|
-- also add customer data to filter for sales_orders
|
43 |
|
SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty
|
44 |
|
FROM orderitems oi, oe, record_links rl, delivery_order_items doi
|
45 |
|
WHERE
|
46 |
|
oe.id = oi.trans_id AND
|
47 |
|
oe.customer_id IS NOT NULL AND
|
48 |
|
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
49 |
|
NOT oe.closed AND
|
50 |
|
rl.from_id = oe.id AND
|
51 |
|
rl.from_id = oi.trans_id AND
|
52 |
|
oe.id = oi.trans_id AND
|
53 |
|
rl.from_table = 'oe' AND
|
54 |
|
rl.to_table = 'delivery_orders' AND
|
55 |
|
rl.to_id = doi.delivery_order_id AND
|
56 |
|
oi.parts_id = doi.parts_id
|
57 |
|
) tuples GROUP BY parts_id, trans_id, qty
|
58 |
|
) partials
|
59 |
|
LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id
|
60 |
|
WHERE oi.qty > doi_qty
|
61 |
|
|
62 |
|
UNION ALL
|
63 |
|
|
64 |
|
-- 4. since the join over record_links fails for sales_orders wihtout any delivery order
|
65 |
|
-- retrieve those without record_links at all
|
66 |
|
SELECT oi.id FROM orderitems oi, oe
|
67 |
|
WHERE
|
68 |
|
oe.id = oi.trans_id AND
|
69 |
|
oe.customer_id IS NOT NULL AND
|
70 |
|
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
71 |
|
NOT oe.closed AND
|
72 |
|
oi.trans_id NOT IN (
|
73 |
|
SELECT from_id
|
74 |
|
FROM record_links rl
|
75 |
|
WHERE
|
76 |
|
rl.from_table ='oe' AND
|
77 |
|
rl.to_table = 'delivery_orders'
|
78 |
|
)
|
79 |
|
|
80 |
|
UNION ALL
|
81 |
|
|
82 |
|
-- 5. In case someone deleted a line of the delivery_order there will be a record_link (4 fails)
|
83 |
|
-- but there won't be a delivery_order_items to find (3 fails too). Search for orphaned orderitems this way
|
84 |
|
SELECT oi.id FROM orderitems AS oi, oe, record_links AS rl
|
85 |
|
WHERE
|
86 |
|
rl.from_table = 'oe' AND
|
87 |
|
rl.to_table = 'delivery_orders' AND
|
88 |
|
|
89 |
|
oi.trans_id = rl.from_id AND
|
90 |
|
oi.parts_id NOT IN (
|
91 |
|
SELECT doi.parts_id FROM delivery_order_items AS doi WHERE doi.delivery_order_id = rl.to_id
|
92 |
|
) AND
|
93 |
|
|
94 |
|
oe.id = oi.trans_id AND
|
95 |
|
|
96 |
|
oe.customer_id IS NOT NULL AND
|
97 |
|
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
98 |
|
NOT oe.closed
|
99 |
|
" ],
|
100 |
|
];
|
101 |
|
|
102 |
30 |
sub action_list {
|
103 |
31 |
my ($self) = @_;
|
104 |
32 |
|
... | ... | |
194 |
122 |
$self->{filter_summary} = join ', ', @filter_strings;
|
195 |
123 |
}
|
196 |
124 |
|
|
125 |
sub delivery_plan_query {
|
|
126 |
my $employee_id = SL::DB::Manager::Employee->current->id;
|
|
127 |
my $oe_owner = $_[0]->all_edit_right ? '' : " oe.eployee_id = $employee_id AND";
|
|
128 |
[
|
|
129 |
'order.customer_id' => { gt => 0 },
|
|
130 |
'order.closed' => 0,
|
|
131 |
or => [ 'order.quotation' => 0, 'order.quotation' => undef ],
|
|
132 |
|
|
133 |
# filter by shipped_qty < qty, read from innermost to outermost
|
|
134 |
'id' => [ \"
|
|
135 |
-- 3. resolve the desired information about those
|
|
136 |
SELECT oi.id FROM (
|
|
137 |
-- 2. slice only part, orderitem and both quantities from it
|
|
138 |
SELECT parts_id, trans_id, qty, SUM(doi_qty) AS doi_qty FROM (
|
|
139 |
-- 1. join orderitems and deliverorder items via record_links.
|
|
140 |
-- also add customer data to filter for sales_orders
|
|
141 |
SELECT oi.parts_id, oi.trans_id, oi.id, oi.qty, doi.qty AS doi_qty
|
|
142 |
FROM orderitems oi, oe, record_links rl, delivery_order_items doi
|
|
143 |
WHERE
|
|
144 |
oe.id = oi.trans_id AND
|
|
145 |
oe.customer_id IS NOT NULL AND
|
|
146 |
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
|
147 |
NOT oe.closed AND
|
|
148 |
$oe_owner
|
|
149 |
rl.from_id = oe.id AND
|
|
150 |
rl.from_id = oi.trans_id AND
|
|
151 |
oe.id = oi.trans_id AND
|
|
152 |
rl.from_table = 'oe' AND
|
|
153 |
rl.to_table = 'delivery_orders' AND
|
|
154 |
rl.to_id = doi.delivery_order_id AND
|
|
155 |
oi.parts_id = doi.parts_id
|
|
156 |
) tuples GROUP BY parts_id, trans_id, qty
|
|
157 |
) partials
|
|
158 |
LEFT JOIN orderitems oi ON partials.parts_id = oi.parts_id AND partials.trans_id = oi.trans_id
|
|
159 |
WHERE oi.qty > doi_qty
|
|
160 |
|
|
161 |
UNION ALL
|
|
162 |
|
|
163 |
-- 4. since the join over record_links fails for sales_orders wihtout any delivery order
|
|
164 |
-- retrieve those without record_links at all
|
|
165 |
SELECT oi.id FROM orderitems oi, oe
|
|
166 |
WHERE
|
|
167 |
oe.id = oi.trans_id AND
|
|
168 |
oe.customer_id IS NOT NULL AND
|
|
169 |
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
|
170 |
NOT oe.closed AND
|
|
171 |
$oe_owner
|
|
172 |
oi.trans_id NOT IN (
|
|
173 |
SELECT from_id
|
|
174 |
FROM record_links rl
|
|
175 |
WHERE
|
|
176 |
rl.from_table ='oe' AND
|
|
177 |
rl.to_table = 'delivery_orders'
|
|
178 |
)
|
|
179 |
|
|
180 |
UNION ALL
|
|
181 |
|
|
182 |
-- 5. In case someone deleted a line of the delivery_order there will be a record_link (4 fails)
|
|
183 |
-- but there won't be a delivery_order_items to find (3 fails too). Search for orphaned orderitems this way
|
|
184 |
SELECT oi.id FROM orderitems AS oi, oe, record_links AS rl
|
|
185 |
WHERE
|
|
186 |
rl.from_table = 'oe' AND
|
|
187 |
rl.to_table = 'delivery_orders' AND
|
|
188 |
|
|
189 |
oi.trans_id = rl.from_id AND
|
|
190 |
oi.parts_id NOT IN (
|
|
191 |
SELECT doi.parts_id FROM delivery_order_items AS doi WHERE doi.delivery_order_id = rl.to_id
|
|
192 |
) AND
|
|
193 |
|
|
194 |
oe.id = oi.trans_id AND
|
|
195 |
|
|
196 |
oe.customer_id IS NOT NULL AND
|
|
197 |
(oe.quotation = 'f' OR oe.quotation IS NULL) AND
|
|
198 |
$oe_owner
|
|
199 |
NOT oe.closed
|
|
200 |
" ],
|
|
201 |
]
|
|
202 |
}
|
|
203 |
|
197 |
204 |
sub init_models {
|
198 |
205 |
my ($self) = @_;
|
199 |
206 |
|
... | ... | |
207 |
214 |
},
|
208 |
215 |
%sort_columns,
|
209 |
216 |
},
|
210 |
|
query => $delivery_plan_query,
|
|
217 |
query => $self->delivery_plan_query,
|
211 |
218 |
with_objects => [ 'order', 'order.customer', 'part' ],
|
212 |
219 |
);
|
213 |
220 |
}
|
214 |
221 |
|
|
222 |
sub init_all_edit_right {
|
|
223 |
$::auth->assert('sales_all_edit', 1)
|
|
224 |
}
|
|
225 |
|
215 |
226 |
sub link_to {
|
216 |
227 |
my ($self, $object, %params) = @_;
|
217 |
228 |
|
Eigenes Recht für Lieferplan und "sales_all_edit" berücksichtigen