API tools faq
paste
Guest User

PLEASE SHORT THIS QUERY

a guest
Jun 13th, 2013
299
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 99.95 KB | None | 0 0
raw download clone embed print report
  1.  
  2.                     SELECT
  3.                         B.*,
  4.                         A.order_id,
  5.                         A.sidemark,
  6.                         A.order_design,
  7.                         A.order_colorway,
  8.                         A.order_quality,
  9.                         A.ktm_contact_id,
  10.                         A.order_silk_percentage,
  11.                         IF(A.ship_by != '0000-00-00', DATE_FORMAT(A.ship_by, '%d-%b-%y'), 'TBD') AS ship_by,
  12.                         (
  13.             IF(A.priority = '1', 'Normal',
  14.                 IF(A.priority = '2', 'High',
  15.                     IF(A.priority = '3', 'Critical', 'Normal')
  16.                 )
  17.             )
  18.         ) AS priority_text,
  19.                         A.factory_ref_number,
  20.                         DATE_FORMAT( A.order_date , '%d-%b-%y') AS order_date,
  21.                         IF(A.lapchi_purchase_order != '', A.lapchi_purchase_order, 'None') AS LPO,
  22.                         (IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm)) AS crpt_length_cm,
  23.                         (IF(E.current_position IS NULL OR E.current_position = '', 0, E.current_position)) AS current_position,
  24.                         E.last_reading_date,
  25.                         E.on_loom,
  26.                         D.loc_name,
  27.                         (IF(((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm)) - E.current_position) < 0 OR ((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm)) - E.current_position) IS NULL, 0, ((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm)) - E.current_position))) AS balance_on_loom,
  28.                         D.vendor_id,
  29.                         E.assign_date AS assign_date_db,
  30.  
  31.                         IF(E.assign_date IS NOT NULL AND E.assign_date != '0000-00-00',  DATE_FORMAT(E.assign_date , '%d-%b-%y'),
  32.  
  33.                         (IF(DATE_FORMAT((IF(IF((
  34.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  35.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  36.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  37.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  38.                                     )
  39.                                 )
  40.                             )
  41.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  42.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  43.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  44.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  45.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  46.                                     )
  47.                                 )
  48.                             )
  49.                         )), -1) != -1,
  50.                                     DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL ((IF((
  51.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  52.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  53.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  54.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  55.                                     )
  56.                                 )
  57.                             )
  58.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  59.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  60.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  61.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  62.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  63.                                     )
  64.                                 )
  65.                             )
  66.                         )), -1))+(SELECT loomNumWeekends((DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL (IF((
  67.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  68.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  69.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  70.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  71.                                     )
  72.                                 )
  73.                             )
  74.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  75.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  76.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  77.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  78.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  79.                                     )
  80.                                 )
  81.                             )
  82.                         )), -1)) DAY)), (DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), (IF((SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) IS NULL OR (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) = '', '6', (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id)))))+(SELECT loomNumHolidays((DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL (IF((
  83.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  84.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  85.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  86.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  87.                                     )
  88.                                 )
  89.                             )
  90.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  91.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  92.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  93.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  94.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  95.                                     )
  96.                                 )
  97.                             )
  98.                         )), -1)) DAY)), (DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), (IF(A.vendor_id > 0, (
  99.                         SELECT
  100.                             GROUP_CONCAT(DISTINCT h1.holiday_date)
  101.                         FROM
  102.                             holidays h1
  103.                         INNER JOIN
  104.                             holidays_ref hr1
  105.                         ON
  106.                             h1.ref_id = hr1.ref_id
  107.                         WHERE
  108.                             hr1.vendor_id REGEXP CONCAT('[[:<:]]', A.vendor_id, '[[:>:]]') OR hr1.vendor_id = ''
  109.                     ), (
  110.                         SELECT
  111.                             GROUP_CONCAT(DISTINCT h1.holiday_date)
  112.                         FROM
  113.                             holidays h1
  114.                     ))), (IF((SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) IS NULL OR (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) = '', '6', (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id)))))) DAY),
  115.                                     '0000-00-00')), '%d-%b-%y') IS NOT NULL, DATE_FORMAT((IF(IF((
  116.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  117.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  118.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  119.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  120.                                     )
  121.                                 )
  122.                             )
  123.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  124.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  125.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  126.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  127.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  128.                                     )
  129.                                 )
  130.                             )
  131.                         )), -1) != -1,
  132.                                     DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL ((IF((
  133.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  134.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  135.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  136.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  137.                                     )
  138.                                 )
  139.                             )
  140.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  141.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  142.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  143.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  144.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  145.                                     )
  146.                                 )
  147.                             )
  148.                         )), -1))+(SELECT loomNumWeekends((DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL (IF((
  149.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  150.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  151.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  152.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  153.                                     )
  154.                                 )
  155.                             )
  156.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  157.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  158.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  159.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  160.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  161.                                     )
  162.                                 )
  163.                             )
  164.                         )), -1)) DAY)), (DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), (IF((SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) IS NULL OR (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) = '', '6', (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id)))))+(SELECT loomNumHolidays((DATE_SUB((DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), INTERVAL (IF((
  165.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  166.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  167.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  168.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  169.                                     )
  170.                                 )
  171.                             )
  172.                         ) > 0, ROUND((IF(E.length_on_loom IS NOT NULL AND E.length_on_loom > 0, E.length_on_loom, B.ideal_length_cm))/(
  173.                             IF(E.weave_rate IS NOT NULL AND E.weave_rate > 0, E.weave_rate,
  174.                                 IF((IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) IS NULL OR (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))) = 0, 0,
  175.                                     IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id AND pc.prog_id = A.colorway_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))),
  176.                                         IF((SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) IS NOT NULL AND (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) > 0, (SELECT pc.weave_rate FROM crpt_design cd, crpt_designnumbers dn, crpt_programmcarpet pc, crpt_programmcarpet_partnumbers pcpn, partnumbers pn WHERE cd.design_id = dn.design_id AND dn.dsgnnmber_id = pc.dsgnnmber_id AND pc.prog_id = pcpn.prog_id AND pcpn.prtnmbr_id = pn.prtnmbr_id AND pcpn.colorway = '1' AND cd.design_id = A.design_id AND dn.dsgnnmber_id = A.dsgnnmber_id LIMIT 1) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / (IF(IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom) <= 75, 1, IF(FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75)) <= 90, FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75), FLOOR((IF(E.width_on_loom IS NULL OR E.width_on_loom = 0, B.ideal_width_cm, E.width_on_loom)) / 75) + 1)))))), 0)
  177.                                     )
  178.                                 )
  179.                             )
  180.                         )), -1)) DAY)), (DATE_SUB(A.est_delivery_date, INTERVAL ((IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14))+(IF(A.priority = '2', 7, 12))) DAY)), (IF(A.vendor_id > 0, (
  181.                         SELECT
  182.                             GROUP_CONCAT(DISTINCT h1.holiday_date)
  183.                         FROM
  184.                             holidays h1
  185.                         INNER JOIN
  186.                             holidays_ref hr1
  187.                         ON
  188.                             h1.ref_id = hr1.ref_id
  189.                         WHERE
  190.                             hr1.vendor_id REGEXP CONCAT('[[:<:]]', A.vendor_id, '[[:>:]]') OR hr1.vendor_id = ''
  191.                     ), (
  192.                         SELECT
  193.                             GROUP_CONCAT(DISTINCT h1.holiday_date)
  194.                         FROM
  195.                             holidays h1
  196.                     ))), (IF((SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) IS NULL OR (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) = '', '6', (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id)))))) DAY),
  197.                                     '0000-00-00')), '%d-%b-%y'), 'TBD'))
  198.  
  199.  
  200.                         ) AS assign_date,
  201.  
  202.  
  203.                         (IF((IF((LOWER(TRIM(A.shipping_mode)) LIKE 'expedited') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%fedex%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%ups%') OR (LOWER(TRIM(A.shipping_mode)) LIKE '%dhl%'), 'E', 'R')) = 'E', 7, 14)) AS shipBuffer,
  204.                         (IF(A.priority = '2', 7, 12)) AS washingFinishing,
  205.                         A.projected_delivery_date AS projected_delivery_date_db,
  206.                         A.est_delivery_date AS est_delivery_date_db,
  207.                         E.weaving_start_date,
  208.                         IF(E.current_speed IS NULL, 0, E.current_speed) AS current_speed,
  209.                         L.loom_reference,
  210.                         QC.qc_id,
  211.                         QC.new_ship_date,
  212.                         QC.graph_issued_date,
  213.                         QC.nemcha_issued_date,
  214.                         QC.carpet_received_date,
  215.                         QC.remarks,
  216.                         QC.qc_notes
  217.                     FROM
  218.                        
  219.             (crpt_admin_orders A, crpt_admin_orders_size B)
  220.         LEFT JOIN
  221.             crpt_loom E ON (E.order_id = A.order_id AND E.id = (SELECT MAX(E1.id) FROM crpt_loom E1 WHERE E1.order_id = A.order_id LIMIT 1))
  222.         LEFT JOIN
  223.             weaving_unit D ON (D.weave_unit_id = E.weaving_id)
  224.         LEFT JOIN
  225.             looms L on (E.loom_id = L.loom_id)
  226.         LEFT JOIN
  227.             crpt_admin_orders_qc AS QC ON A.order_id = QC.order_id
  228.  
  229.        
  230.                     WHERE
  231.                        
  232.                     A.order_id = B.order_id
  233.                 AND
  234.                     A.order_status = '3' AND
  235.                     A.production_status != 'SPD'
  236.  
  237.  
  238.                
  239.                        
  240.                     GROUP BY
  241.                         A.order_id
  242.                     ORDER BY
  243.                         A.order_date ASC
  244.                    
  245. limit 23
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!