index.xql

DB Query file
File Contents

<?xml version="1.0"?>
<queryset>

  <fullquery name="locale_stats">
    <querytext>

        select
            locales_summary.*,
            (select count(*) from ad_locales al where al.language = locales_summary.language) as num_locales_for_language
        from (
            select
                al.locale as locale,
                al.label as locale_label,
                language,
                default_p,
                enabled_p,
	        case when num_messages is null          then 0 else num_messages end as num_messages,
	        case when num_translated is null        then 0 else num_translated end as num_translated,
	        case when num_untranslated is null      then 0 else num_untranslated end as num_untranslated,
	        case when num_deleted is null           then 0 else num_deleted end as num_deleted
            from (
                select
                    locale,
                    count(*)                                                                    as num_messages,
                    count(message_is_not_null + messages_not_deleted)                           as num_translated,
                    count(message_is_null + default_message_not_deleted + message_not_deleted)  as num_untranslated,
                    count(any_message_deleted)                                                  as num_deleted
                from (
                    select
                        case when lm2.locale is null                            then :current_locale else lm2.locale end locale,
                        case when lm2.message is null                           then 1 end message_is_null,
                        case when lm2.message is not null                       then 1 end message_is_not_null,
                        case when lm1.deleted_p = 't' or lm2.deleted_p = 't'    then 1 end any_message_deleted,
                        case when lm1.deleted_p = 'f' and lm2.deleted_p = 'f'   then 1 end messages_not_deleted,
                        case when lm1.deleted_p = 'f'                           then 1 end default_message_not_deleted,
                        case when lm2.deleted_p = 'f' or lm2.deleted_p is null  then 1 end message_not_deleted
                    from
                        lang_messages lm1 left outer join
                        lang_messages lm2 on (
                                lm2.locale = :current_locale
                            and lm2.message_key = lm1.message_key
                            and lm2.package_key = lm1.package_key
                        )
                    where
                        lm1.locale = :default_locale
                ) locale_messages
                group by locale
            ) locale_summary join
              ad_locales al on
                 al.locale = locale_summary.locale
            group by
                al.locale,
		al.label,
	        language,
	        default_p,
	        enabled_p,
                num_messages,
                num_translated,
                num_untranslated,
                num_deleted
            order by
                locale_label
        ) locales_summary

    </querytext>
  </fullquery>

</queryset>
index.xql

Related Files

File Contents
