API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 19th, 2019
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 12.28 KB | None | 0 0
raw download clone embed print report
  1. select round((case
  2.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) >=0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) > (case
  3.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) >=  and ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  4. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0) > 0
  5.             then ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  6. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0)
  7.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) >=  and ifnull(max(if(c.storage_id=2,c.cost,0)),0)>0
  8.             then ifnull(max(if(c.storage_id=2,c.cost,0)),0)
  9.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) >  and ifnull(max(if(c.storage_id=4,c.cost,0)),0)>0
  10.             then ifnull(max(if(c.storage_id=4,c.cost,0)),0)
  11.             else 99999999 end)*1.05
  12.             then if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1)
  13.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) >=0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) <= (case
  14.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) >=  and ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  15. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0) > 0
  16.             then ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  17. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0)
  18.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) >=  and ifnull(max(if(c.storage_id=2,c.cost,0)),0)>0
  19.             then ifnull(max(if(c.storage_id=2,c.cost,0)),0)
  20.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) >  and ifnull(max(if(c.storage_id=4,c.cost,0)),0)>0
  21.             then ifnull(max(if(c.storage_id=4,c.cost,0)),0)
  22.             else 99999999 end)*1.05
  23.             then round(((case
  24.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) >=  and ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  25. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0) > 0
  26.             then ifnull(sum(if(c.storage_id in (select id from storages where group_id=32),c.cost*c.amount,0))/
  27. sum(if(c.storage_id in (select id from storages where group_id=32),c.amount,0)) ,0)
  28.             when if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) >=  and ifnull(max(if(c.storage_id=2,c.cost,0)),0)>0
  29.             then ifnull(max(if(c.storage_id=2,c.cost,0)),0)
  30.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) <  and (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) >  and ifnull(max(if(c.storage_id=4,c.cost,0)),0)>0
  31.             then ifnull(max(if(c.storage_id=4,c.cost,0)),0)
  32.             else 99999999 end)*1.05),-1)
  33.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) <0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) - ) >= 0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) > ifnull(max(if(c.storage_id=2,c.cost,0)),0)*1.01
  34.             then if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1)
  35.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) <0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) - ) >= 0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) <= ifnull(max(if(c.storage_id=2,c.cost,0)),0)*1.01
  36.             then round((ifnull(max(if(c.storage_id=2,c.cost,0)),0)*1.01),-1)
  37.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) <0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) - ) < 0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) - ) >= 0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) > ifnull(max(if(c.storage_id=4,c.cost,0)),0)*1.02
  38.             then if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1)
  39.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) <0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) - ) < 0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) - ) >= 0 and if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) <= ifnull(max(if(c.storage_id=4,c.cost,0)),0)*1.02
  40.             then round((ifnull(max(if(c.storage_id=4,c.cost,0)),0)*1.05),-1)
  41.             when (if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) - ) <0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0)))) - ) < 0 and ((if((sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )<=0,0,(sum(if(c.storage_id=1,c.amount,0)) + sum(if(c.storage_id=3,c.amount,0)) - (max(ifnull(r.amount,0)) - 0) )) + if(sum(if(c.storage_id=2,c.amount,0))>10,500,sum(if(c.storage_id=2,c.amount,0))) + sum(if(c.storage_id=4,c.amount,0))) - ) < 0
  42.             then 99999999
  43.             else if(1 =0 or 1 is null,if(p.switch=1,p.retail1,p.retail2),1) end)) price
  44.     from goods g
  45.     left join contents c on c.good_id=g.id
  46.     left join prices p on p.good_id = g.id and p.shop_id=1
  47.     left join
  48.         (select tc.good_id, sum(tc.amount)amount
  49.         from transactions_content tc
  50.         join
  51.             (select r.transaction_id, substring_index(group_concat(r.reserve order by r.id desc),',',1)r
  52.             from reserves r
  53.             join transactions_content tc on tc.status=0 and tc.transaction_id=r.transaction_id and tc.good_id='185'
  54.             join transactions t on r.transaction_id=t.id and t.direction=-1
  55.             where r.status = 0
  56.             group by r.transaction_id
  57.             having r>0)r
  58.         on r.transaction_id = tc.transaction_id
  59.         where tc.good_id='185'
  60.         group by tc.good_id) r
  61.     on r.good_id=g.id
  62.     where g.id = 185
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!