@transform(loadAlleles, 
           suffix("_alleles.load"),
           "_alleles_transcripts.load" )
def summarizeAllelesPerTranscript( infile, outfile ):
    
    tablename = outfile[:-len(".load")]
    track = infile[:-len("_alleles.load")]

    dbhandle = sqlite3.connect( PARAMS["database"] )

    statement = '''
    CREATE TABLE %(tablename)s AS
    SELECT DISTINCT 
           transcript_id,
           COUNT(DISTINCT allele_id) AS nalleles,
           CASE WHEN SUM( is_nmd_knockout) = 2 THEN 1 ELSE 0 END AS is_nmd_knockout,
           CASE WHEN SUM( is_nmd_knockout) >= 1 THEN 1 ELSE 0 END AS is_nmd_affected,
           CASE WHEN SUM( is_splice_truncated) = 2 THEN 1 ELSE 0 END AS is_splice_truncated,
           CASE WHEN SUM( is_splice_truncated) >= 1 THEN 1 ELSE 0 END AS is_splice_affected,
           CASE WHEN SUM( is_stop_truncated) = 2 THEN 1 ELSE 0 END AS is_stop_truncated,
           CASE WHEN SUM( is_stop_truncated) >= 1 THEN 1 ELSE 0 END AS is_stop_affected,
           CASE WHEN SUM( is_wildtype ) = 2 THEN 1 ELSE 0 END AS is_wildtype, 
           CASE WHEN SUM( is_wildtype ) >= 1 THEN 1 ELSE 0 END AS has_wildtype, 
           contig AS contig, 
           strand AS strand, 
           GROUP_CONCAT( reference_first_stop_start ) AS stop_codons_start,
           GROUP_CONCAT( reference_first_stop_end ) AS stop_codons_end,
           0 AS is_knockout,
           0 AS is_truncated,
           0 AS is_affected
    FROM %(track)s_alleles AS a
    GROUP BY transcript_id
    ''' % locals()
    
    Database.executewait( dbhandle, "DROP TABLE IF EXISTS %(tablename)s" % locals() )
    Database.executewait( dbhandle, statement )
    Database.executewait( dbhandle, "CREATE INDEX %(tablename)s_transcript_id ON %(tablename)s (transcript_id)" % locals())
    Database.executewait( dbhandle, "UPDATE %(tablename)s SET is_knockout = is_nmd_knockout" % locals())
    Database.executewait( dbhandle, '''UPDATE %(tablename)s SET is_truncated = 
                                       is_splice_truncated OR is_stop_truncated OR 
                                       (is_splice_affected AND is_stop_affected) OR 
                                       (is_splice_affected AND is_nmd_affected) OR 
                                       (is_stop_affected AND is_nmd_affected)
                                       ''' % locals())
    Database.executewait( dbhandle, 'UPDATE %(tablename)s SET is_affected ='
                          '(is_nmd_affected OR is_splice_affected OR is_stop_affected) AND NOT'
                          '(is_knockout or is_truncated)'% locals())
    dbhandle.commit()

    P.touch(outfile)

###################################################################
###################################################################
###################################################################
Setup the flows for passing LLDP/VDP frames in OVS.
dataset/ETHPy150Open CGATOxford/cgat/obsolete/pipeline_snps.py/summarizeAllelesPerTranscript--crossover with--dataset/ETHPy150Open openstack/networking-cisco/networking_cisco/apps/saf/agent/vdp/ovs_vdp.py/OVSNeutronVdp.setup_lldpad_ports