<jupyter_start><jupyter_text>Filings Daily Stats We need to load in these libraries into our notebook in order to query, load, manipulate and view the data<jupyter_code>import os
import csv
from datetime import datetime

%load_ext sql
%config SqlMagic.displaylimit = 5<jupyter_output><empty_output><jupyter_text>This will create the connection to the database and prep the jupyter magic for SQL<jupyter_code>connect_to_db = 'postgresql://' + \
                os.getenv('PG_USER', '') + ":" + os.getenv('PG_PASSWORD', '') +'@' + \
                os.getenv('PG_HOST', '') + ':' + os.getenv('PG_PORT', '5432') + '/' + os.getenv('PG_DB_NAME', '');
    
%sql $connect_to_db<jupyter_output><empty_output><jupyter_text>Simplest query to run to ensure our libraries are loaded and our DB connection is working<jupyter_code>%%sql 
select now() AT TIME ZONE 'PST' as current_date
%%sql 
insert into sent_to_gazette (filing_id, identifier, sent_to_gazette_date)
select f.id, b.identifier, null
from filings      f
    ,businesses   b 
    ,businesses_version old 
    ,businesses_version new 
where f.filing_type in ('alteration', 'changeOfName')   
    and f.business_id=b.id
    and f.transaction_id=old.end_transaction_id 
    and f.transaction_id=new.transaction_id
    and f.source='LEAR' 
    and old.legal_name != new.legal_name
    and f.id not in (select filing_id from sent_to_gazette);  
%%sql  coop_gazette_changeofname  <<
select
  to_char(f.effective_date,'MON dd, yyyy')
||';'
|| CASE WHEN b.legal_type='CP' THEN 'CP'||substr(b.identifier,6,4) ||'    '
        WHEN b.legal_type='XCP' THEN 'XCP'||substr(b.identifier,7,4)||'   '
        ELSE b.identifier||' '
   END  
||';'
||rpad(substr(old.legal_name,1,52),52)
||';'
||' to '
||';'
||rpad(substr(new.legal_name,1,58),58)
from filings      f
    ,businesses   b   
    ,sent_to_gazette stg
    ,businesses_version old 
    ,businesses_version new 
where f.filing_type in ('alteration', 'changeOfName')
    and f.business_id=b.id
    and f.id=stg.filing_id  
    and f.transaction_id=old.end_transaction_id 
    and f.transaction_id=new.transaction_id
    and old.legal_name != new.legal_name
    and stg.sent_to_gazette_date is null   
order by f.effective_date;
datestr = datetime.strftime(datetime.now(), '%Y%m%d')
coop_gazette_changeofname_filename = os.getenv('DATA_DIR', '')+'COOP_GAZETTE_CHANGEOFNAME_' + datestr +'.TXT'

with open(coop_gazette_changeofname_filename, 'w') as f:
    if not coop_gazette_changeofname.DataFrame().empty:
        dfAsString = coop_gazette_changeofname.DataFrame().to_string(header=False, index=False)
        f.write(dfAsString)
%%sql 
insert into sent_to_gazette (filing_id, identifier, sent_to_gazette_date)
select f.id, b.identifier, null
from filings      f
    ,businesses   b    
where f.filing_type in ('dissolved', 'dissolution') 
    and f.business_id=b.id 
    and f.source='LEAR' 
    and f.id not in (select filing_id from sent_to_gazette);
%%sql coop_gazette_dissolution  <<
select
  to_char(f.effective_date,'yyyymmdd')
||';'
|| CASE WHEN b.legal_type='CP' THEN 'CP '||substr(b.identifier,3,7)
        WHEN b.legal_type='XCP' THEN 'XCP'||substr(b.identifier,4,7) 
        ELSE 'BC '||substr(b.identifier,3,7)   
   END                     
||';' 
||rpad(b.legal_name,150)
||';'
|| CASE WHEN f.filing_type = 'dissolved' THEN '1'
        WHEN f.filing_type = 'dissolution' THEN '2'        
   end   
||rpad(' ',27)
from filings      f
    ,businesses   b   
    ,sent_to_gazette stg
where f.filing_type in ('dissolved', 'dissolution')  
      and f.business_id=b.id
      and f.id=stg.filing_id      
      and stg.sent_to_gazette_date is null   
order by f.effective_date;      
coop_gazette_dissolution_filename = os.getenv('DATA_DIR', '')+'COOP_GAZETTE_DISSOLUTION_' + datestr +'.TXT'

with open(coop_gazette_dissolution_filename, 'w') as f:  
    if not coop_gazette_dissolution.DataFrame().empty:
        dfAsString = coop_gazette_dissolution.DataFrame().to_string(header=False, index=False)
        f.write(dfAsString)
%%sql 
insert into sent_to_gazette (filing_id, identifier, sent_to_gazette_date)
select f.id, b.identifier, null
from filings      f
    ,businesses   b    
where f.filing_type in ('incorporationApplication','amalgamationApplication') 
    and f.business_id=b.id 
    and f.source='LEAR' 
    and f.id not in (select filing_id from sent_to_gazette);
%%sql coop_gazette_incorporation  <<
select
  to_char(b.founding_date,'MON dd, yyyy')
||';'
|| CASE WHEN b.legal_type='CP' THEN 'CP' ||substr(b.identifier,6,4)||'    '
       WHEN b.legal_type='XCP' THEN 'XCP'||substr(b.identifier,7,4)||'   '
        ELSE b.identifier ||' '
   END                 
||';'
||rpad(b.legal_name,150)
||rpad(' ',59)
from filings      f
    ,businesses   b    
    ,sent_to_gazette stg
where f.filing_type in ('incorporationApplication','amalgamationApplication') 
    and f.business_id=b.id 
    and f.id=stg.filing_id
    and stg.sent_to_gazette_date is null   
order by b.founding_date;
coop_gazette_incorporation_filename = os.getenv('DATA_DIR', '')+'COOP_GAZETTE_INCORPORATION_' + datestr +'.TXT'

with open(coop_gazette_incorporation_filename, 'w') as f:
    if not coop_gazette_incorporation.DataFrame().empty:
        dfAsString = coop_gazette_incorporation.DataFrame().to_string(header=False, index=False)
        f.write(dfAsString)
%%sql 
insert into sent_to_gazette (filing_id, identifier, sent_to_gazette_date)
select f.id, b.identifier, null
from filings      f
    ,businesses   b    
where f.filing_type = 'restoration' 
    and f.business_id=b.id 
    and f.source='LEAR' 
    and f.id not in (select filing_id from sent_to_gazette);
%%sql  coop_gazette_restoration  <<
select
  to_char(f.effective_date,'yyyymmdd')
||';'
|| CASE WHEN b.legal_type='CP' THEN 'CP    '||substr(b.identifier,6,4)
       WHEN b.legal_type='XCP' THEN 'XCP   '||substr(b.identifier,7,4)
       ELSE 'BC '||substr(b.identifier,3,7)
   END                     
||';'
||rpad(b.legal_name,112)
from filings      f
    ,businesses   b    
    ,sent_to_gazette stg
where f.filing_type = 'restoration'  
  and f.business_id=b.id 
  and f.id=stg.filing_id
  and stg.sent_to_gazette_date is null 
order by f.effective_date;
coop_gazette_restoration_filename = os.getenv('DATA_DIR', '')+'COOP_GAZETTE_RESTORATION_' + datestr +'.TXT'

with open(coop_gazette_restoration_filename, 'w') as f:
    if not coop_gazette_restoration.DataFrame().empty:
        dfAsString = coop_gazette_restoration.DataFrame().to_string(header=False, index=False)
        f.write(dfAsString)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12473444613050076, 'fraction_numerical': 0.007587253414264037, 'mean_word_length': 3.9370786516853933, 'pattern_counts': {'":': 1, '<': 21, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '66128', 'license': 'Apache-2.0', 'n_tokens_mistral': 2564, 'n_tokens_neox': 2473, 'n_words': 488, 'path': 'jobs/sftp-gazette/notebook/generate_files.ipynb', 'repo_name': 'lekshmimallika-aot/lear', 'size': '6590'}