papa nyomkövető

select
    account.account_id,
    `character`.name,
    item1.name_english,
    case when item1.id is not null then item1.id else item2.id end as item,
    case plog.`type`
        when 'M' then '(M)onsters Drop'
        when 'P' then '(P)layers Drop/Take'
        when 'L' then 'Mobs Drop (L)oot Drop/Take'
        when 'T' then 'Players (T)rade Give/Take'
        when 'V' then 'Players (V)ending Sell/Take'
        when 'S' then '(S)hop Sell/Take'
        when 'N' then '(N)PC Give/Take'
        when 'C' then '(C)onsumable Items'
        when 'R' then 'Sto(R)age'
        when 'G' then '(G)uild Storage'
        when 'E' then '(E)mail attachment'
        when 'B' then '(B)uying Store'
        when 'O' then 'Pr(O)duced Items/Ingredients'
        when 'I' then 'Auct(I)oned Items'
        when 'D' then '(D) Stolen from mobs'
        when 'A' then '(A)dministrators Create/Delete'
        when 'X' then '(X) Other'
        when 'U' then '(U) MVP Prizes'
        when 'F' then '(F) Guild/Party Bound retrieval'
        when 'Y' then 'Lotter(Y)'
        when 'Z' then '(Z) Merged Items'
        when 'Q' then '(Q)uest'
        when 'H' then 'Private Airs(H)ip'
        else 'UNKNOWN'
        end as drop_type,
    plog.amount,
    plog.`map`,
    plog.unique_id,
    plog.time,
    concat(case when inv.nameid is not null then 'Inventory' else '' end,
            case when gst.nameid is not null then 'Guild Storage' else '' end,
            case when cart.nameid is not null then 'Cart' else '' end,
            case when st.nameid is not null then 'Storage' else '' end,
            case when mail.nameid is not null then 'Mail' else '' end)
        as current_location
from picklog plog
join `char` as `character` on `character`.char_id = plog.char_id
LEFT join item_db as item1 on item1.id = plog.nameid
LEFT join item_db2 as item2 on item2.id = plog.nameid
join login account on `character`.account_id = account.account_id
left join inventory inv on inv.unique_id = plog.unique_id
left join storage st on st.unique_id = plog.unique_id
left join guild_storage gst on gst.unique_id = plog.unique_id
left join cart_inventory cart on cart.unique_id = plog.unique_id
left join mail_attachments mail on mail.unique_id = plog.unique_id
where plog.unique_id > 0 and `character`.name like '%Zasura%'
order by plog.unique_id desc, plog.`time` DESC