SELECT COUNT query with many left JOIN clause is taking too long...

Multiple tables join and applying case when...