Subversion Repositories SmartDukaan

Rev

Rev 35474 | Rev 35490 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 35474 Rev 35475
Line 274... Line 274...
274 
                        "       SUM(today_po_value) AS today_po_value, " +
 274 
                        "       SUM(today_po_value) AS today_po_value, " +
275 
                        "       SUM(today_billed_value) AS today_billed_value, " +
 275 
                        "       SUM(today_billed_value) AS today_billed_value, " +
276 
                        "       SUM(total_billed_value) AS total_billed_value, " +
 276 
                        "       SUM(total_billed_value) AS total_billed_value, " +
277 
                        "       COUNT(DISTINCT customer_id) AS partner_count " +
 277 
                        "       COUNT(DISTINCT customer_id) AS partner_count " +
278 
                        "FROM ( " +
 278 
                        "FROM ( " +
279 
                        "    SELECT COALESCE(region_partner_map.auth_user_id, -1) AS auth_user_id, " +
 - 
 
280 
                        "           li.brand, " +
 - 
 
281 
                        "           o.customer_id, " +
 279 
                        "    SELECT p.auth_user_id, li.brand, o.customer_id, " +
282 
                        "           CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +
 280 
                        "           CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END AS today_po_value, " +
283 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +
 281 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS today_billed_value, " +
284 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
 282 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
285 
                        "    FROM transaction.`order` o " +
 283 
                        "    FROM transaction.`order` o " +
286 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
 284 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
287 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
 285 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
288 
                        "    LEFT JOIN (SELECT p.auth_user_id, COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id " +
 - 
 
289 
                        "               FROM cs.partner_position ps " +
 - 
 
290 
                        "               LEFT JOIN cs.partner_region pr ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
 - 
 
291 
                        "               LEFT JOIN cs.position p ON p.id = ps.position_id " +
 286 
                        "    JOIN cs.partner_position ps ON ps.partner_id = fs.id " +
292 
                        "               WHERE p.category_id = :categoryId AND p.escalation_type = :escalationType) AS region_partner_map " +
 287 
                        "    JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +
293 
                        "        ON region_partner_map.final_retailer_id = fs.id " +
 - 
 
294 
                        "    WHERE o.refund_timestamp IS NULL " +
 - 
 
295 
                        "      AND o.created_timestamp BETWEEN :startDate AND :endDate " +
 288 
                        "    WHERE o.refund_timestamp IS NULL AND o.created_timestamp BETWEEN :startDate AND :endDate " +
296 
                        "      AND fs.internal = FALSE " +
 - 
 
297 
                        "      AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
 289 
                        "      AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
298 
                        "    UNION ALL " +
 290 
                        "    UNION ALL " +
299 
                        "    SELECT COALESCE(region_partner_map.auth_user_id, -1) AS auth_user_id, " +
 291 
                        "    SELECT p.auth_user_id, li.brand, o.customer_id, 0, 0, " +
- 
 
 292 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +
- 
 
 293 
                        "    FROM transaction.`order` o " +
- 
 
 294 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
- 
 
 295 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
- 
 
 296 
                        "    JOIN cs.partner_position ps ON ps.partner_id = fs.id " +
- 
 
 297 
                        "    JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +
- 
 
 298 
                        "    WHERE o.refund_timestamp IS NULL AND o.billing_timestamp BETWEEN :startDate AND :endDate " +
- 
 
 299 
                        "      AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +
- 
 
 300 
                        "      AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
300 
                        "           li.brand, " +
 301 
                        "    UNION ALL " +
301 
                        "           o.customer_id, " +
 302 
                        "    SELECT p.auth_user_id, li.brand, o.customer_id, " +
- 
 
 303 
                        "           CASE WHEN o.refund_timestamp IS NULL THEN o.total_amount ELSE 0 END, " +
- 
 
 304 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END, " +
- 
 
 305 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +
302 
                        "           0 AS today_po_value, " +
 306 
                        "    FROM transaction.`order` o " +
- 
 
 307 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
- 
 
 308 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
- 
 
 309 
                        "    JOIN cs.partner_region pr ON pr.fofo_id = fs.id " +
- 
 
 310 
                        "    JOIN cs.partner_position ps ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
- 
 
 311 
                        "    JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +
- 
 
 312 
                        "    WHERE o.refund_timestamp IS NULL AND o.created_timestamp BETWEEN :startDate AND :endDate " +
- 
 
 313 
                        "      AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
303 
                        "           0 AS today_billed_value, " +
 314 
                        "    UNION ALL " +
- 
 
 315 
                        "    SELECT p.auth_user_id, li.brand, o.customer_id, 0, 0, " +
304 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END AS total_billed_value " +
 316 
                        "           CASE WHEN o.billing_timestamp IS NOT NULL THEN o.total_amount ELSE 0 END " +
305 
                        "    FROM transaction.`order` o " +
 317 
                        "    FROM transaction.`order` o " +
306 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
 318 
                        "    JOIN transaction.lineitem li ON o.id = li.order_id " +
307 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
 319 
                        "    JOIN fofo.fofo_store fs ON fs.id = o.customer_id " +
308 
                        "    LEFT JOIN (SELECT p.auth_user_id, COALESCE(pr.fofo_id, ps.partner_id) AS final_retailer_id " +
 - 
 
309 
                        "               FROM cs.partner_position ps " +
 320 
                        "    JOIN cs.partner_region pr ON pr.fofo_id = fs.id " +
310 
                        "               LEFT JOIN cs.partner_region pr ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
 321 
                        "    JOIN cs.partner_position ps ON ps.partner_id = 0 AND ps.region_id = pr.region_id " +
311 
                        "               LEFT JOIN cs.position p ON p.id = ps.position_id " +
 - 
 
312 
                        "               WHERE p.category_id = :categoryId AND p.escalation_type = :escalationType) AS region_partner_map " +
 322 
                        "    JOIN cs.position p ON p.id = ps.position_id AND p.category_id = :categoryId AND p.escalation_type = :escalationType " +
313 
                        "        ON region_partner_map.final_retailer_id = fs.id " +
 - 
 
314 
                        "    WHERE o.refund_timestamp IS NULL " +
 - 
 
315 
                        "      AND o.billing_timestamp BETWEEN :startDate AND :endDate " +
 323 
                        "    WHERE o.refund_timestamp IS NULL AND o.billing_timestamp BETWEEN :startDate AND :endDate " +
316 
                        "      AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +
 324 
                        "      AND o.created_timestamp NOT BETWEEN :startDate AND :endDate " +
317 
                        "      AND fs.internal = FALSE " +
 - 
 
318 
                        "      AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
 325 
                        "      AND fs.internal = FALSE AND li.brand NOT IN ('Smartdukaan', 'Dummy', 'FOC', 'Live Demo') " +
319 
                        ") AS combined " +
 326 
                        ") AS combined " +
320 
                        "GROUP BY auth_user_id, brand " +
 327 
                        "GROUP BY auth_user_id, brand " +
321 
                        "ORDER BY auth_user_id, brand",
 328 
                        "ORDER BY auth_user_id, brand",
322 
                resultSetMapping = "TodayPoByAuthUsers"
 329 
                resultSetMapping = "TodayPoByAuthUsers"
323 
        ),
 330 
        ),