text:
"""
This script reads from ENA FAANG data portal, parses and validates the data and stores into Elastic Search
It is highly recommended to check out the corresponding rule set
https://github.com/FAANG/faang-metadata/blob/master/rulesets/faang_experiments.metadata_rules.json
to help understanding the code
"""
import click
from constants import TECHNOLOGIES, STANDARDS, CATEGORIES, SPECIES_DICT, EXPERIMENT_TARGETS
from elasticsearch import Elasticsearch
import constants
from typing import Set, Dict, List
from utils import determine_file_and_source, check_existsence, remove_underscore_from_end_prefix, \
    write_system_log, get_line_number, insert_into_es, get_record_ids, generate_ena_api_endpoint
import re
import validate_experiment_record
import sys
import json
import requests
from misc import convert_readable, parse_date

SCRIPT_NAME = 'import_ena_legacy'

to_es_flag = True
es = None

# in FAANG ruleset each technology has mandatory fields in the corresponding section, which is not expected in the
# general ENA datasets, so only validate against Legacy standard
RULESETS = ["FAANG Legacy Experiments"]

SPECIES_TAXONOMY_LIST = list(SPECIES_DICT.keys())

# holds all sample records from ES
BIOSAMPLES_RECORDS = dict()
# holds the material information for all records encountered (retrieve_biosample_record function)
# keys are biosamples accessions and values are dicts which have three sub keys
# confirmed (boolean), material (dict), accession (str)
CACHED_MATERIAL = dict()

# control which assay types to be imported
ASSAY_TYPES_TO_BE_IMPORTED = {
    "ATAC-seq": "ATAC-seq",
    "BS-Seq": "methylation profiling by high throughput sequencing",
    "Hi-C": "Hi-C",
    "DNase": "DNase-Hypersensitivity seq",
    "RNA-Seq": "transcription profiling by high throughput sequencing",
    "miRNA": "microRNA profiling by high throughput sequencing",
    "WGS": "whole genome sequencing assay",
    "ChIP-Seq": "ChIP-seq"
}
# value of all is not allowed in the general ena data portal, not like FAANG, so need to list fields
# which we want to retrieve
FIELD_LIST = [
    'study_accession', 'secondary_study_accession', 'sample_accession', 'experiment_accession', 'run_accession',
    'submission_accession', 'tax_id', 'instrument_platform', 'instrument_model', 'library_strategy',
    'library_selection', 'read_count', 'base_count', 'first_public', 'last_updated',
    'study_title', 'study_alias', 'run_alias', 'fastq_bytes', 'fastq_md5',
    'fastq_ftp', 'fastq_aspera', 'fastq_galaxy', 'submitted_format', 'submitted_ftp', 'submitted_md5',
    'submitted_bytes', 'sra_bytes',
    'sra_md5', 'sra_ftp', 'sra_aspera', 'sra_galaxy', 'cram_index_ftp',
    'cram_index_aspera', 'cram_index_galaxy', 'project_name'
]

def get_biosamples_records_from_es(host, es_index_prefix, es_type):
    """
    Get existing biosample records from elastic search
    :param host: elasticsearch python instance
    :param es_index_prefix: the name of the index set
    :param es_type: type of record, either organism or specimen
    """
    global BIOSAMPLES_RECORDS
    url = f'http://{host}/{es_index_prefix}_{es_type}/_search?size=100000'
    response = requests.get(url).json()
    if 'hits' not in response:
        write_system_log(es, SCRIPT_NAME, 'error', get_line_number(),
                         f'No data retrieved from {url}, please double check whether the URL is correct', to_es_flag)
        return
    for item in response['hits']['hits']:
        BIOSAMPLES_RECORDS[item['_id']] = item['_source']

def retrieve_biosamples_record(es_index_prefix, biosample_id):
    """
    retrieve biosample record on the fly which referenced in the non-FAANG ENA study and store into ES
    status values:
    0: already existing in ES, nothing done in this function
    200: successfully retrieved record
    all other values: error to retrieve the record
    :param es_index_prefix: Elastic search index prefix
    :param biosample_id: BioSanples id
    :return: status code
    """

    # BIOSAMPLE_RECORDS stores the full details of records in ES
    # CACHED_MATERIAL only stores the material type of records and the confirmation status
    # which also indicates whether the record has been processed or not
    global CACHED_MATERIAL
    # check whether the biosamples id has been dealt with by this script
    if biosample_id in CACHED_MATERIAL:
        return 0
    # already in ES, no need to go further, but still need to register with CACHED_MATERIAL
    if biosample_id in BIOSAMPLES_RECORDS:
        CACHED_MATERIAL.setdefault(biosample_id, {})
        CACHED_MATERIAL[biosample_id]['accession'] = biosample_id
        CACHED_MATERIAL[biosample_id]['material'] = BIOSAMPLES_RECORDS[biosample_id]['material']
        CACHED_MATERIAL[biosample_id]['confirmed'] = True
        CACHED_MATERIAL[biosample_id]['source'] = 'ES records'
        return 0
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                     f'Try to get data for {biosample_id} from BioSamples', to_es_flag)
    url = f"https://www.ebi.ac.uk/biosamples/samples/{biosample_id}"
    response = requests.get(url)
    status = response.status_code
    # if not successful, return the status code and add to cache
    if status != 200:  # success
        tmp = {
            'accession': biosample_id,
            'confirmed': False,
            'source': 'error status'
        }
        CACHED_MATERIAL[biosample_id] = tmp
        return status

    default_material_value = {
        'text': 'specimen from organism',
        'ontologyTerms': 'http://purl.obolibrary.org/obo/OBI_0001479'
    }
    # successfully get the sample record
    data = response.json()

    material_key = get_field_name(data, 'Material', 'material')
    if material_key:
        CACHED_MATERIAL.setdefault(biosample_id, dict())
        CACHED_MATERIAL[biosample_id]['accession'] = biosample_id
        CACHED_MATERIAL[biosample_id].setdefault('material', dict())
        CACHED_MATERIAL[biosample_id]['material']['text'] = data['characteristics'][material_key][0]['text']
        CACHED_MATERIAL[biosample_id]['material']['ontologyTerms'] = \
            data['characteristics'][material_key][0]['ontologyTerms'][0]
        CACHED_MATERIAL[biosample_id]['confirmed'] = True
        CACHED_MATERIAL[biosample_id]['source'] = 'biosample server material key'

    parent_animal_list = list()
    if 'relationships' in data:
        relationships = data['relationships']
        for relationship in relationships:
            # child of relationship only exists in animals
            if relationship['type'] == 'child of':
                material_value = {
                    'text': 'organism',
                    'ontologyTerms': 'http://purl.obolibrary.org/obo/OBI_0100026'
                }
                tmp = {
                    'accession': biosample_id,
                    'confirmed': True,
                    'material': material_value,
                    'source': 'child of relationship'
                }
                CACHED_MATERIAL[biosample_id] = tmp
                if relationship['target'] != biosample_id:
                    parent_animal_list.append(relationship['target'])
            elif relationship['type'] == 'derived from':
                if relationship['target'] != biosample_id:
                    # try to get all records from the relationships
                    if relationship['target'] not in CACHED_MATERIAL:
                        retrieve_biosamples_record(es_index_prefix, relationship['target'])
                    related_material = CACHED_MATERIAL[relationship['target']]
                    if 'material' in related_material and related_material['confirmed'] \
                            and related_material['material']['text'] == 'organism':
                        if biosample_id in CACHED_MATERIAL and not CACHED_MATERIAL[biosample_id]['confirmed']:
                            material_value = {
                                'text': 'specimen from organism',
                                'ontologyTerms': 'http://purl.obolibrary.org/obo/OBI_0001479'
                            }
                            tmp = {
                                'accession': biosample_id,
                                'confirmed': True,
                                'material': material_value,
                                'source': 'derived from organism relationship'
                            }
                            CACHED_MATERIAL[biosample_id] = tmp
                        animal = relationship['target']

    if biosample_id not in CACHED_MATERIAL:
        CACHED_MATERIAL.setdefault(biosample_id, {})
        CACHED_MATERIAL[biosample_id]['accession'] = biosample_id
        CACHED_MATERIAL[biosample_id]['confirmed'] = False
        CACHED_MATERIAL[biosample_id]['material'] = default_material_value

    es_doc = dict()
    # sample record labelled as FAANG does not guarantee to be in ES as it may fail the validation,
    # but should not be dealt with here

    # this is a tag used in the ruleset, not a standard, just happening to share the same value
    # therefore not replacing with constant
    if 'project' in data['characteristics'] and data['characteristics']['project'][0]['text'] == 'FAANG':
        return -1
    es_doc['biosampleId'] = biosample_id
    es_doc['name'] = data['name']
    es_doc['material'] = CACHED_MATERIAL[biosample_id]['material']

    is_ebi_record = False
    # id_number is an artificial value which make it possible to sort BioSamples records using numeric order
    if biosample_id[0:5] == 'SAMEA':
        es_doc['id_number'] = biosample_id[5:]
        is_ebi_record = True
    else:  # non EBI BioSamples record, indicating by using - sign
        match = re.search(r'\d+', biosample_id)
        if match:
            es_doc['id_number'] = -int(match.group(0))
    # In the BioSample's code converting NCBI BioSample records into EBI ones,
    # XML <Description><Title>text</Title></Description> is saved in field "description title"
    desc_key = get_field_name(data, 'description', 'description title')
    if desc_key:
        es_doc['description'] = data['characteristics'][desc_key][0]['text']

    species_key = get_field_name(data, 'organism', 'Organism')

    """
    https://www.ncbi.nlm.nih.gov/biosample/docs/attributes/?format=xml
    NCBI uses attributes, like EBI uses characteristics, <Name>field name</Name>
    FAANG mandatory field  => NCBI attribute(s)      EBI
    organism part => tissue                organism part
    cell type => cell type                 cell type
    breed => breed                         breed
    sex => sex                             sex
    developmental stage => development stage     developmental stage
    """

    found_fields = set()

    if CACHED_MATERIAL[biosample_id]['material']['text'] == 'organism':
        es_type = 'organism'
        es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'sex', 'sex')
        es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'breed', 'breed')
        if species_key:
            es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'organism', species_key)
    else:  # specimen
        es_type = 'specimen'
        if is_ebi_record:
            if 'organism part' in data['characteristics']:
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'organismPart',
                                                          'organism part', 'specimenFromOrganism')
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'cellType', 'organism part')
            elif 'cell type' in data['characteristics']:
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'cellType', 'cell type')
            es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'developmentalStage',
                                                      'developmental stage', 'specimenFromOrganism')
        else:  # from NCBI or DDBJ
            if 'tissue' in data['characteristics']:
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'cellType', 'tissue')
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'organismPart',
                                                          'tissue', 'specimenFromOrganism')
            elif 'cell type' in data['characteristics']:
                es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'cellType', 'cell type')
            es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'developmentalStage',
                                                      'development stage', 'specimenFromOrganism')

        if species_key:
            es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'organism', species_key, 'organism')
        es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'sex', 'sex', 'organism')
        es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'breed', 'breed', 'organism')
        es_doc, found_fields = extract_field_info(data, es_doc, found_fields, 'breed', 'strain', 'organism')

    if 'release' in data:
        es_doc['releaseDate'] = parse_date(data['release'])
    else:
        es_doc['releaseDate'] = None

    if 'update' in data:
        es_doc['updateDate'] = parse_date(data['update'])
    else:
        es_doc['updateDate'] = None
    # as no project = FAANG (otherwise already dealt with by import biosample), the standard is always basic
    es_doc['standardMet'] = constants.STANDARD_BASIC
    # animal only gets assigned during dealing with the relationship derived from
    # https://stackoverflow.com/questions/1592565/determine-if-variable-is-defined-in-python
    try:
        # noinspection PyUnboundLocalVariable
        animal
    except NameError:
        pass
    else:
        es_doc['derivedFrom'] = animal

    if parent_animal_list:
        es_doc.setdefault('childOf', list(parent_animal_list))

    custom_fields = list()
    for field_name in data['characteristics'].keys():
        # these fields have already been dealt with above
        if field_name == desc_key or field_name == species_key \
                or field_name == material_key or field_name in found_fields:
            continue
        if type(data['characteristics'][field_name]) is list:
            to_parse = data['characteristics'][field_name][0]
        else:
            to_parse = data['characteristics'][field_name]
        one_custom_field = dict()
        one_custom_field['name'] = field_name
        if type(to_parse) is dict:
            if 'text' in to_parse:
                one_custom_field['value'] = str(to_parse['text'])
            elif 'value' in to_parse:
                one_custom_field['value'] = str(to_parse['value'])
            if 'unit' in to_parse:
                one_custom_field['unit'] = to_parse['unit']
            if 'ontologyTerms' in to_parse:
                one_custom_field['ontologyTerms'] = to_parse['ontologyTerms'][0]
        else:
            one_custom_field['value'] = to_parse
        custom_fields.append(one_custom_field)
    es_doc['customField'] = custom_fields

    # expected to fail validation (Legacy basic), so no need to carry out
    body = json.dumps(es_doc)
    insert_into_es(es, es_index_prefix, es_type, biosample_id, body)

    BIOSAMPLES_RECORDS[biosample_id] = es_doc
    return status

def extract_field_info(data, es_doc, found_fields, result_field_name, target_field_name, es_section=None):
    """
    extract data for particular field :param target_field_name from the BioSamples API record :param data
    and save into the document :param es_doc as field :param result_field_name while adding into found_fields
    :param data: BioSamples API records
    :param es_doc: Elastic search document
    :param found_fields: list of fields which have been found in the BioSamples record
    :param result_field_name: the field name to store the extract information in the es_doc
    :param target_field_name: the field name to be search in the BioSamples record
    :param es_section: indicate which section the field belongs to in the es_doc, if in the root, set to None
    :return:
    """
    if target_field_name in data['characteristics']:
        found_fields.add(target_field_name)
        if es_section:
            es_doc.setdefault(es_section, dict())
            es_doc[es_section].setdefault(result_field_name, dict())
            es_doc[es_section][result_field_name]['text'] = data['characteristics'][target_field_name][0]['text']
            if 'ontologyTerms' in data['characteristics'][target_field_name][0]:
                es_doc[es_section][result_field_name]['ontologyTerms'] = \
                    data['characteristics'][target_field_name][0]['ontologyTerms'][0]
        else:
            es_doc.setdefault(result_field_name, dict())
            es_doc[result_field_name]['text'] = data['characteristics'][target_field_name][0]['text']
            if 'ontologyTerms' in data['characteristics'][target_field_name][0]:
                es_doc[result_field_name]['ontologyTerms'] = \
                    data['characteristics'][target_field_name][0]['ontologyTerms'][0]
    return es_doc, found_fields

def get_field_name(data, original_key, alternative_value):
    """
    check which column name used in the BioSamples record, if both provided column names not found, return ''
    :param data: one BioSamples record in JSON format retrieved from API
    :param original_key: field name to be checked
    :param alternative_value: alternative field name to be checked
    :return: either original key or alternative value if found in the data, if not found return ''
    """
    # biosamples record always has characteristics section
    if original_key not in data['characteristics']:
        if alternative_value in data['characteristics']:
            return alternative_value
        else:
            return ''
    return original_key

@click.command()
@click.option(
    '--es_hosts',
    default=constants.STAGING_NODE1,
    help='Specify the Elastic Search server(s) (port could be included), e.g. wp-np3-e2:9200. '
         'If multiple servers are provided, please use ";" to separate them, e.g. "wp-np3-e2;wp-np3-e3"'
)
@click.option(
    '--es_index_prefix',
    default="",
    help='Specify the Elastic Search index prefix, e.g. '
         'faang_build_1_ then the indices will be faang_build_1_experiment etc.'
         'If not provided, then work on the aliases, e.g. experiment'
)
@click.option(
    '--to_es',
    default="true",
    help='Specify how to deal with the system log either writing to es or printing out. '
         'It only allows two values: true (to es) or false (print to the terminal)'
)
def main(es_hosts, es_index_prefix, to_es: str):
    """
    Main function that will import legacy data (not FAANG labelled) from ena
    :param es_hosts: elasticsearch hosts where the data import into
    :param es_index_prefix: the index prefix points to a particular version of data
    :param to_es: determine whether to output log to Elasticsearch (True) or terminal (False, printing)
    """
    global to_es_flag
    if to_es.lower() == 'false':
        to_es_flag = False
    elif to_es.lower() == 'true':
        pass
    else:
        print('to_es parameter can only accept value of true or false')
        exit(1)

    global es
    hosts = es_hosts.split(";")
    es = Elasticsearch(hosts)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'Start importing ena legacy', to_es_flag)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'Command line parameters', to_es_flag)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), f'Hosts: {str(hosts)}', to_es_flag)
    es_index_prefix = remove_underscore_from_end_prefix(es_index_prefix)
    if es_index_prefix:
        write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), f'Index_prefix: {es_index_prefix}', to_es_flag)

    get_biosamples_records_from_es(hosts[0], es_index_prefix, 'organism')
    get_biosamples_records_from_es(hosts[0], es_index_prefix, 'specimen')
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                     f'There are {len(BIOSAMPLES_RECORDS)} sample records in the ES', to_es_flag)
    if not BIOSAMPLES_RECORDS:
        write_system_log(es, SCRIPT_NAME, 'error', get_line_number(),
                         'No biosamples data found in the given index, please run import_from_biosamles.py first',
                         to_es_flag)
        sys.exit(1)

    existing_faang_datasets: Set[str] = get_record_ids(hosts[0], es_index_prefix, 'dataset', only_faang=True)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                     f'There are {len(existing_faang_datasets)} FAANG datasets in the ES', to_es_flag)

    # strings used to build ENA API query
    field_str = ",".join(FIELD_LIST)
    species_str = ",".join(SPECIES_TAXONOMY_LIST)

    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'Retrieving data from ENA', to_es_flag)
    # collect all data from ENA API and saved into local dict which has keys as study accession
    # and values as array of data related to the study
    todo: Dict[str, List[Dict]] = dict()
    for term in CATEGORIES.keys():
        category = CATEGORIES[term]
        if category not in ASSAY_TYPES_TO_BE_IMPORTED:
            continue
        write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                         f'term {term} in category {category}', to_es_flag)
        # f"https://www.ebi.ac.uk/ena/portal/api/search/?result=read_run&format=JSON&limit=0&" \
        #    f"query=library_strategy%3D%22{term}%22%20AND%20tax_eq({species_str})&fields={field_str}"
        # extra constraint based on species and library strategy
        # debug notes: category in records descending order RNA-Seq (32k), WGS,
        # miRNA-Seq (4k), and others (around 1k or less)
        optional_str = f"query=library_strategy%3D%22{term}%22%20AND%20tax_eq({species_str})"
        url = generate_ena_api_endpoint('read_run', 'ena', field_str, optional_str)
        response = requests.get(url)
        if response.status_code == 204:  # 204 is the status code for no content => the current term does not have match
            continue
        data = response.json()
        for hit in data:
            study_accession = hit['study_accession']
            if study_accession in existing_faang_datasets:  # already in the data portal
                continue
            # not replaced with constants.STANDARD_FAANG because they are separate concepts,
            # here is a tag used in the ruleset, not a standard
            if hit['project_name'] == 'FAANG':  # labelled as FAANG which is supposed to be deal with import_from _ena
                continue
            todo.setdefault(term, list())
            todo[term].append(hit)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                     'Finishing retrieving data from ENA', to_es_flag)

    indexed_files = dict()
    datasets = dict()
    experiments = dict()
    files_dict = dict()
    technology = dict()

    for category in todo.keys():
        write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                         f'{category} has {len(todo[category])} records', to_es_flag)
        assay_type = ASSAY_TYPES_TO_BE_IMPORTED[CATEGORIES[category]]
        experiment_target = EXPERIMENT_TARGETS[CATEGORIES[category]]
        technology[assay_type] = category

        count = dict()
        with_files = dict()
        for record in todo[category]:
            # if fail to get the related sample record, skip the record
            specimen_biosample_id = record['sample_accession']
            if len(specimen_biosample_id) < 5: # invalid sample accession, which should be SAM[M|D|E]
                continue
            status = retrieve_biosamples_record(es_index_prefix, specimen_biosample_id)
            if status != 0 and status != 200:
                continue

            study_accession = record['study_accession']
            count.setdefault(study_accession, 0)
            count[study_accession] += 1

            file_type, source_type = determine_file_and_source(record)

            if file_type == '':
                continue

            with_files.setdefault(study_accession, 0)
            with_files[study_accession] += 1
            if file_type == 'fastq':
                archive = 'ENA'
            elif file_type == 'cram_index':
                archive = 'ENA'
                file_type = 'submitted'
                source_type = 'ftp'
            else:
                archive = 'SRA'

            try:
                files = record[f"{file_type}_{source_type}"].split(";")
                types = record['submitted_format'].split(";")
                sizes = record[f"{file_type}_bytes"].split(";")
            except KeyError:
                print(f"category {category} record {record}")
                exit()

            if len(files) != len(sizes):
                continue
            # for ENA, it is fixed to MD5 as the checksum method
            checksums = record[f"{file_type}_md5"].split(";")
            # logger.info(f"study {study_accession} with sample {specimen_biosample_id} having files {','.join(files)}")

            for index, file in enumerate(files):
                fullname = file.split("/")[-1]
                filename = fullname.split(".")[0]
                type_found = True
                try:
                    file_type = types[index]
                    if len(file_type) == 0:
                        type_found = False
                except IndexError:
                    type_found = False
                if not type_found:
                    start_index = len(filename)+1
                    file_type = fullname[start_index:]

                es_file_doc = {
                    'specimen': specimen_biosample_id,
                    'species': {
                        'text': SPECIES_DICT[record['tax_id']],
                        'ontologyTerms': f"http://purl.obolibrary.org/obo/NCBITaxon_{record['tax_id']}"
                    },
                    'url': file,
                    'name': fullname,
                    'type': file_type,
                    'size': sizes[index],
                    'readableSize': convert_readable(sizes[index]),
                    'checksumMethod': 'md5',
                    'checksum': checksums[index],
                    'archive': archive,
                    'baseCount': record['base_count'],
                    'readCount': record['read_count'],
                    'releaseDate': record['first_public'],
                    'updateDate': record['last_updated'],
                    'submission': record['submission_accession'],
                    'experiment': {
                        'accession': record['experiment_accession'],
                        'assayType': assay_type,
                        'target': experiment_target
                    },
                    'run': {
                        'accession': record['run_accession'],
                        'alias': record['run_alias'],
                        'platform': record['instrument_platform'],
                        'instrument': record['instrument_model']
                    },
                    'study': {
                        'accession': record['study_accession'],
                        'alias': record['study_alias'],
                        'title': record['study_title'],
                        'type': category,
                        'secondaryAccession': record['secondary_study_accession']
                    }
                }
                files_dict[filename] = es_file_doc

                exp_id = record['experiment_accession']
                # one experiment could have multiple runs/files, therefore experiment info needs to be collected once
                if exp_id not in experiments:
                    exp_es = {
                        'accession': exp_id,
                        'assayType': assay_type,
                        'experimentTarget': experiment_target
                    }
                    experiments[exp_id] = exp_es

                # dataset (study) has mutliple experiments/runs/files/specimens_list
                # so collection information into datasets
                # and process it after iteration of all files
                dataset_id = record['study_accession']
                es_doc_dataset = dict()
                if dataset_id in datasets:
                    es_doc_dataset = datasets[dataset_id]
                else:
                    es_doc_dataset['accession'] = dataset_id
                    es_doc_dataset['alias'] = record['study_alias']
                    es_doc_dataset['title'] = record['study_title']
                    es_doc_dataset['secondaryAccession'] = record['secondary_study_accession']
                datasets.setdefault('tmp', dict())
                datasets['tmp'].setdefault(dataset_id, dict())
                datasets['tmp'][dataset_id].setdefault('specimen', set())
                datasets['tmp'][dataset_id]['specimen'].add(specimen_biosample_id)

                datasets['tmp'][dataset_id].setdefault('instrument', set())
                datasets['tmp'][dataset_id]['instrument'].add(record['instrument_model'])

                datasets['tmp'][dataset_id].setdefault('archive', set())
                datasets['tmp'][dataset_id]['archive'].add(archive)

                tmp_file = {
                    'url': file,
                    'name': fullname,
                    'fileId': filename,
                    'experiment': record['experiment_accession'],
                    'type': file_type,
                    'size': sizes[index],
                    'readableSize': convert_readable(sizes[index]),
                    'archive': archive,
                    'baseCount': record['base_count'],
                    'readCount': record['read_count'],
                    'checksumMethod': 'md5',
                    'checksum': checksums[index]
                }
                datasets['tmp'][dataset_id].setdefault('file', dict())
                datasets['tmp'][dataset_id]['file'][fullname] = tmp_file

                tmp_exp = {
                    'accession': record['experiment_accession'],
                    'assayType': assay_type,
                    'target': experiment_target
                }
                datasets['tmp'][dataset_id].setdefault('experiment', dict())
                datasets['tmp'][dataset_id]['experiment'][record['experiment_accession']] = tmp_exp
                datasets[dataset_id] = es_doc_dataset

    if not datasets:
        write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'No datasets have been found', to_es_flag)
        exit()

    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'The dataset list:', to_es_flag)
    dataset_ids = sorted(list(datasets.keys()))
    for index, dataset_id in enumerate(dataset_ids):
        num_exps = 0
        if dataset_id == 'tmp':
            continue
        if dataset_id in datasets['tmp'] and 'experiment' in datasets['tmp'][dataset_id]:
            num_exps = len(datasets['tmp'][dataset_id]["experiment"])
        printed_index = index + 1
        write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                         f'{printed_index} {dataset_id} has {num_exps} experiments to be processed', to_es_flag)
    # datasets contains one artificial value set with the key as 'tmp', so need to -1
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                     f'There are {len(datasets) -  1} datasets to be processed', to_es_flag)

    validator = validate_experiment_record.ValidateExperimentRecord(experiments, RULESETS)
    validation_results = validator.validate()
    exp_validation = dict()
    for exp_id in sorted(list(experiments.keys())):
        exp_es = experiments[exp_id]
        for ruleset in RULESETS:
            if validation_results[ruleset]['detail'][exp_id]['status'] == 'error':
                write_system_log(es, SCRIPT_NAME, 'info', get_line_number(),
                                 f"{exp_id}\tExperiment\terror\t"
                                 f"{validation_results[ruleset]['detail'][exp_id]['message']}", to_es_flag)
            else:
                # only indexing when meeting standard
                exp_validation[exp_id] = STANDARDS[ruleset]
                exp_es['standardMet'] = STANDARDS[ruleset]
                body = json.dumps(exp_es)
                insert_into_es(es, es_index_prefix, 'experiment', exp_id, body)
                # index into ES so break the loop
                break
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'finishing indexing experiments', to_es_flag)

    for file_id in files_dict.keys():
        es_file_doc = files_dict[file_id]
        # noinspection PyTypeChecker
        exp_id = es_file_doc['experiment']['accession']
        # only files linked to valid experiments are allowed into the data portal
        if exp_id not in exp_validation:
            continue
        es_file_doc['experiment']['standardMet'] = exp_validation[exp_id]
        body = json.dumps(es_file_doc)
        insert_into_es(es, es_index_prefix, 'file', file_id, body)
        indexed_files[file_id] = 1
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'finishing indexing files', to_es_flag)

    for dataset_id in datasets:
        if dataset_id == 'tmp':
            continue
        es_doc_dataset = datasets[dataset_id]
        exps = datasets['tmp'][dataset_id]["experiment"]
        only_valid_exps = dict()
        dataset_standard = constants.STANDARD_FAANG
        experiment_type = dict()
        tech_type = dict()
        for exp_id in exps:
            if exp_id in exp_validation:
                if exp_validation[exp_id] == constants.STANDARD_LEGACY:
                    dataset_standard = constants.STANDARD_LEGACY
                only_valid_exps[exp_id] = exps[exp_id]
                assay_type = exps[exp_id]['assayType']
                tech_type.setdefault(TECHNOLOGIES[assay_type], 0)
                tech_type[TECHNOLOGIES[assay_type]] += 1
                experiment_type.setdefault(assay_type, 0)
                experiment_type[assay_type] += 1
            else:
                pass
        num_valid_exps = len(only_valid_exps.keys())
        if num_valid_exps == 0:
            write_system_log(es, SCRIPT_NAME, 'warning', get_line_number(),
                             f'dataset {dataset_id} has no valid experiments, skipped.', to_es_flag)
            continue
        es_doc_dataset['standardMet'] = dataset_standard
        specimen_set = datasets['tmp'][dataset_id]['specimen']
        species = dict()
        specimens_list = list()
        for specimen in specimen_set:
            if specimen not in BIOSAMPLES_RECORDS:
                write_system_log(es, SCRIPT_NAME, 'warning', get_line_number(),
                                 f'BioSamples record {specimen} required by dataset {dataset_id} could not be found',
                                 to_es_flag)
                continue
            specimen_detail = BIOSAMPLES_RECORDS[specimen]
            es_doc_specimen = dict()
            es_doc_specimen['biosampleId'] = check_existsence(specimen_detail, 'biosampleId')
            es_doc_specimen['material'] = check_existsence(specimen_detail, 'material')
            es_doc_specimen['cellType'] = check_existsence(specimen_detail, 'cellType')
            es_doc_specimen['organism'] = check_existsence(specimen_detail['organism'], 'organism')
            es_doc_specimen['sex'] = check_existsence(specimen_detail['organism'], 'sex')
            es_doc_specimen['breed'] = check_existsence(specimen_detail['organism'], 'breed')
            specimens_list.append(es_doc_specimen)
            species[specimen_detail['organism']['organism']['text']] = specimen_detail['organism']['organism']
        if not specimens_list:  # no sepcimen found for the dataset, skip
            write_system_log(es, SCRIPT_NAME, 'warning', get_line_number(),
                             f'Dataset {dataset_id} could not find any related specimen, skipped', to_es_flag)
            continue
        es_doc_dataset['specimen'] = sorted(specimens_list, key=lambda k: k['biosampleId'])
        es_doc_dataset['species'] = list(species.values())
        file_arr = datasets['tmp'][dataset_id]['file'].values()
        valid_files = list()
        for file_entry in sorted(file_arr, key=lambda k: k['name']):
            file_id = file_entry['fileId']
            if file_id in indexed_files:
                valid_files.append(file_entry)
        es_doc_dataset['file'] = valid_files
        es_doc_dataset['experiment'] = list(only_valid_exps.values())
        es_doc_dataset['assayType'] = list(experiment_type.keys())
        es_doc_dataset['tech'] = list(tech_type.keys())
        es_doc_dataset['instrument'] = list(datasets['tmp'][dataset_id]['instrument'])
        es_doc_dataset['archive'] = sorted(list(datasets['tmp'][dataset_id]['archive']))
        body = json.dumps(es_doc_dataset)
        insert_into_es(es, es_index_prefix, 'dataset', dataset_id, body)
    write_system_log(es, SCRIPT_NAME, 'warning', get_line_number(),
                     f'finishing indexing datasets', to_es_flag)
    write_system_log(es, SCRIPT_NAME, 'info', get_line_number(), 'Finish importing ena legacy', to_es_flag)

if __name__ == "__main__":
    main()

redpajama_set_name: RedPajamaGithub


Finally things are starting to go in the right direction! The job transition has been a slow one, but now we're slated for a move in the pretty-much-immediate future. I'm happy to say that our little family is returning to a town we've called home before. We had high hopes for a new start near the ocean, but the job opportunities were lacking, and the cost of living was not going to make the move a smart decision. I still think we'll get to the shores someday! Now comes the fun part - a frenzy of sorting, packing and cleaning ! We'll be searching for a new place to live, and hopefully our new home will include more space to dedicate to my business efforts. Right now I'm having to juggle TorchFire and all that comes with moving, and my to-do list plagues me as soon as I climb into bed at night. I just keep reminding myself that in a few weeks, it'll all be worth it!
But for now? Bring on the to-do list.


text:
Форсирование Сиваша может относиться к следующим военным операциям:

 Крымская операция 1918 г. армии Украинской Народной Республики с целью свержения Советской власти в Крыму и захвата Черноморского флота.
 Перекопско-Чонгарская операция 1920 г. Южного фронта РККА против Русской армии генерала Врангеля.
 Нижнеднепровская стратегическая операция 26 сентября — 20 декабря 1943 года — создание советскими войсками важного плацдарма для дальнейшего освобождения Крыма.

redpajama_set_name: RedPajamaWikipedia


text:
This prototype of The Pagemaster shows the game at an alpha level.
This alpha version of The Pagemaster was leaked onto the BBS scene in August 1994 by the group "The Accumulators". The alpha has several levels, and the main menu is basically a stage select. The two ghost house levels definitely have different layouts than their equivalents in the final version, and there are noticeable spots where there are hidden platforms. The fonts are different than the final version, as are the level opening messages. Most sound effects are missing, and the ball throwing powerup does not yet work. Most of the graphics look finalized, though. I found a bunch of debugging text messages located at 0x1D8000 in the binary. It appears that the internal header was hacked by the BBS release group, though I see no evidence of any other tampering.
Font used in the alpha Font used in the final version.
Intro stage select in the alpha Sometimes, the platforms just don't exist.
Download the alpha version here

redpajama_set_name: RedPajamaCommonCrawl


text:
Israeli election season starts early
Israeli Prime Minister Benjamin Netanyahu's coalition is submerged in a general pre-election atmosphere, where each party tries to score points with its electorate and the media.
Yair Lapid, leader of the Israeli Yesh Atid Party (L), and Naftali Bennett, head of Israel's Jewish Home Party, attend the swearing-in ceremony of the 19th Knesset in Jerusalem, Feb. 5, 2013. - GETTY IMAGES/Uriel Sinai
Mazal Mualem
@mazalm3
On the afternoon of May 12, just a few hours after the Knesset returned from its spring break, Science Minister Yaakov Peri (Yesh Atid Party) appealed a decision by the Ministerial Committee on Legislation approving the "Murderers Law" initiated by Knesset member Ayelet Shaked (HaBayit HaYehudi Party) and David Tsur (Hatnua Party). The proposed law would have limited the ability to grant clemency to terrorists who committed murder.
In explaining the reasons for the appeal, Peri, former head of the Shin Bet, argued that the proposed law, which enables the court to prevent the president from granting a pardon to terrorists, would harm Israel's ability to conduct negotiations. He stated that these negotiations were vital to the diplomatic and security interests of the state.
As would only be expected, moments after the news of the appeal was publicized by Yesh Atid's talented media team, a public quarrel erupted with Knesset members from the two parties. This quarrel culminated in a dire warning from the chairman of HaBayit HaYehudi Party, Trade and Economy Minister Naftali Bennett, that any delay in approving the law would put people's lives at risk.
"I have not and will not have any tolerance or patience for political games being played at the expense of laws that are vital to the security of the State of Israel," Bennett wrote brashly on his Facebook page. He then awarded a "mark of shame to Yesh Atid, which has now stopped the advance of a law intended to prevent the release of terrorists." Other Knesset members from his faction responded by promising to sabotage any future bills proposed by Yesh Atid.
Given the composition of the coalition, the chances of the appeal being accepted are slim. Nevertheless, it was important for Yesh Atid to distinguish itself as a coalition partner that is pushing for a diplomatic process to take place, and is therefore taking a bold stand against legislation to hinder such a process, even if it comes from a party that was considered to be its political ally until just a few months ago. It has been well-established by now that the alliance between party leaders Yair Lapid and Bennett, which was the hottest, most up-to-date thing in politics after the last election, is already a thing of the past. But moreover, this quarrel was also part of the election mood that has been hovering over the coalition for the past few weeks.
Although the alliance between Lapid and Bennett was unexpected, it had the appeal of change and benefited from the aura of the social protests of summer 2011. Lapid and Bennett were able to force upon Prime Minister Benjamin Netanyahu a certain coalition structure, and present a strong — and similar — socioeconomic agenda, at least at the beginning. Inevitably, they were also forced to bear criticism from some of their voters for forging an alliance with an ideological rival.
But ever since this alliance disintegrated, Lapid and Bennett were doing everything they could to shake off the reminders of those days. In other words, there is even more squabbling and tension within the coalition now.
It is hard to believe that only this Passover (April 2014), the government marked a year since its founding, given the large number of conflicting interests, the dissonance, the squabbling, the feuds and the attempts by all the players to mark their unique territories. It is much more reminiscent of a coalition that is counting down until it finally unravels.
The falling apart of the diplomatic process should, ostensibly, have brought some stability and even tranquility to the coalition. After all, the seed of calamity that confronted Lapid and Justice Minister Tzipi Livni with the right-wing forces in the government have vanished, and everyone agrees that the Palestinians were at fault.
The chairman of the governing coalition, Knesset member Yariv Levin, arrived at the same conclusion in closed talks on Monday. He said that the Knesset's summer session should be a relatively easy one, from the perspective of the coalition, mainly because the diplomatic process has fallen apart, and there is nothing to set the coalition aflame.
But the dynamic that is actually developing on the ground is completely different. That the diplomatic process took place at low intensity, without any breakthroughs or expectations, actually allowed all of the participants to run their affairs in a relatively calm atmosphere, from within the same coalition. Livni, the chairwoman of the Hatnua Party, was up to her neck in an ineffectual diplomatic process, which justified her being part of the government. On the opposite side of the spectrum, Bennett and his right-wing faction could tolerate diplomatic negotiations that weren't going anywhere. All of the people involved, headed by Netanyahu himself, would gladly sign on for a few more years of negotiations like that.
That is why the absence of diplomatic negotiations, with the specific way in which they were conducted, upsets the delicate balance that Netanyahu's bizarre coalition depended on since day one. Without one large guiding process, which enables all the sides to sharpen their positions (the diplomatic negotiations were such a case), the parties are left to seek out new ways of bringing to light their agenda and achievements. This immediately makes them jittery.
With the beginning of the Knesset's summer session this week, the government has switched gears and entered a path that will certainly lead it to its demise, or in other words, to new elections. That is, unless an alternative coalition is formed, but that option does not seem now to be on the horizon.
This election mood, which as we have noted, is only beginning, is being expressed by an increasing number of initiatives and struggles all geared for "the day after." Though none of the coalition party leaders want early elections, they understand that the dynamics that will lead to elections could occur quickly and unexpectedly.
Take, for example, Livni, who is facing the most troubling political predicament. Since she has no more diplomatic negotiations to oversee, a question mark hovers over her remaining in the government. She returned to center stage just a year ago, serving as Netanyahu's right-hand person in the diplomatic negotiations. This, in a government that has a clear right-wing orientation, even though she was elected by votes from the left. Very soon, she will be asked what she is actually doing in a government that promotes the Jewish State Basic Law and the Murderers Law.
Meanwhile, Livni is trying to create a constructive presence for herself as the minister of justice. In other words, she is looking for an agenda that will enable her to justify clinging to her chair. On May 11, the Israeli daily Yedioth Ahronoth published a big headline, "The Divorce Revolution," along with a photo of Livni. According to the plan attributed to the justice minister, Livni wants to introduce dramatic changes to the divorce process in Israel by transferring responsibility for parts of it to family court. In this way, she would reduce the power of the rabbinical courts, which are so loathed by her voters.
It is highly doubtful whether Livni will be able to bring this over-ambitious plan to fruition, which obviously did not remain hanging in an empty space. Her plan has already elicited a sharp response from HaBayit HaYehudi, which has promised to torpedo it.
At the same time, battles over the defense budget are being waged in the press between Finance Minister Lapid and Defense Minister Moshe Ya'alon. Lapid wants to show leadership by refusing to give in to the endless budgetary demands of the Defense Ministry, thus demonstrating loyalty to the country's civil agenda. He therefore made clear to Ya'alon that if he does succumb to his demands, it would come at the expense of health and education.
Lapid is also holding on as tightly as he can to his housing reform, which is scheduled to go into effect in the coming months. His plan to eliminate VAT on new apartments for young couples who have completed army service will be at the center of his next election campaign. As such, the chairman of Yesh Atid remains indifferent to criticism that the reform discriminates against Arabs and ultra-Orthodox Jews, who did not serve in the army. In fact, he even uses this claim surreptitiously to win the support of his electorate, which considers sharing the burden to be an important banner. "A young person who devoted three years to the country and in many cases put his life at risk is a better citizen," declared Lapid on Monday in a speech to a forum of the Calcalist newspaper.
Unlike Livni, Lapid is performing much better in the polls. Though he suffered electorally since the last elections, he has nevertheless managed to maintain a significant support base. If and when the cost of apartments drops next year, he will receive credit for it. That will help to rehabilitate him politically.
And where is Netanyahu in all this? Is he ready? Netanyahu, as any of the other players, is not interested in elections, but he also realizes that such a dynamic could emerge in the coming year. According to one assessment making the rounds in the political establishment, Lapid cannot afford to sign another budget based on economic decrees, so that by December he will initiate a process to lead his party out of the government. Elections will then take place in the following May.
In this climate, Netanyahu has gone back to courting the right, his political base. And he does so inter alia by the Jewish State Basic Law, which he intends to promote as soon as he gets back from Japan.
According to senior members of the Likud, Netanyahu believes that with the Likud running separately from Yisrael Beitenu in the next elections, there could well be a situation in which five or six medium-sized parties gain seats. The Likud would still be the biggest party, but not by a large margin. That is why he is interested in changing the precedent of the State Basic Law. He wants to see the task of forming a government granted automatically to the head of the largest party. This, contrary to the current situation where the party head most likely to form a coalition receives this mandate.
That is also how political sources explain his obsession with eliminating the presidency. He is worried that if Knesset member Reuven Rivlin is elected president, he will give someone else the task of putting together a coalition.
Read in Hebrew


text:
Lafayette ist eine Kleinstadt im Yamhill County in Oregon, USA. Das U.S. Census Bureau hat bei der Volkszählung 2020 eine Einwohnerzahl von 4.423 ermittelt. Lafayette war seit der Gründung im Jahre 1843 bis 1886 Sitz des Countys, bevor nach einer Entscheidung der Einwohner des County McMinnville Verwaltungssitz wurde.

Geographie
Das Gemeindegebiet umfasst 2,3 km².

Einwohnerentwicklung
 2000: 2.586
 2006: 3.440 (Schätzung)

Demographie
In Lafayette existieren 841 Haushalte und 656 Familien.

Weblinks

Einzelnachweise

Marie-Joseph Motier, Marquis de La Fayette als Namensgeber
Gegründet 1846


text:
Q: Remove Duplicates in Power Query WITHOUT Removing Entire Row enter image description hereI need to remove duplicates in multiple columns without removing the entire row. I have a Query with multiple columns and there are 6 columns I want to remove duplicates from. The column headers are:
"Unforcasted Orders"
"Forecasted Orders"
"Forecasted and Unforcasted"
"Open Orders"
"Capability"
"Actuals"
My previous applied step is "Reordered Columns2".
What is the M code for this? Thanks in advance.
I cannot group by because each "Forecast Date" is for a given month and with that month it corresponds to multiple months as "Month in Forecast". If I cannot remove these duplicates without removing the entire row, is there a way to replace the duplicates with null or make them blank? Thanks.

A: If you want to remove all duplicates in specified columns no matter what the values are in the remaining [Product, Forecast Date, Month in Forecast, Attribute] columns then use
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns2", "Index", 0, 1),
#"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Product", "Forecast Date", "Month in Forecast", "Attribute", "Index"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Index"}, "Attribute.1", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Product", "Forecast Date", "Month in Forecast", "Attribute"}),
#"Unpivoted Other Columns2" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute.1", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns2", {"Attribute.1", "Value"}),
Combined = #"Unpivoted Other Columns" & #"Removed Duplicates",
#"Pivoted Column" = Table.Pivot(Combined, List.Distinct(Combined[Attribute.1]), "Attribute.1", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Forecast Date", type date}, {"Month in Forecast", type date}})
in #"Changed Type1"

If you want to remove all duplicates in specified columns based on first grouping together by [Product, Forecast Date, Month in Forecast, Attribute], then use
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns2", "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Product", "Forecast Date", "Month in Forecast", "Attribute","Index"}, "Attribute.1", "Value"),
#"Removed Duplicates1" = Table.Distinct(#"Unpivoted Other Columns", {"Product", "Forecast Date", "Month in Forecast", "Attribute", "Attribute.1", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Duplicates1", List.Distinct(#"Removed Duplicates1"[Attribute.1]), "Attribute.1", "Value", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in #"Removed Columns"

redpajama_set_name: RedPajamaStackExchange


May, June, and July have been so busy in the wedding and engagement departments.
Three acquaintances and a family member got married. My brother-in-law got engaged which means we will have a wedding in Chicago or Omaha in the future.
These events made me think about the wedding I had with FullEclipse and how things could have been.
I make no secret that I was married before. The wedding was gorgeous, but the marriage did not last.
When I met FullEclipse, I had no intentions of ever falling in love again or even walking down the aisle again. Everything changed overnight. We got engaged very quickly. Our intention was to have a big wedding in Chicago in a few years. We did not want to rush anything. Even though this would have been my second marriage, FullEclipse wanted me to have the wedding of my dreams. But, things worked out very differently. FullEclipse and I just couldn't wait to be together forever. We were also trying to have a baby and decided that the best course of action was to get married. I had a suspicion that I was pregnant by our wedding day anyway.
So, one day we woke up and said to each other, "let's get married!" That was it. No big party, no big celebration, no professional pictures, nothing that screamed, "Big wedding!". I bought a blue dress and off to the court-house we went on a Friday afternoon. My parents were the only people who were invited as witnesses. The guy who married us was about 18 years old and wearing torn jeans. He looked like such a kid. It was just the perfect way for us to say, "I love you. Let's spend the rest of our lives together!" By the way, my hunch was right, I was pregnant with Paczki.
To this day, our love and devotion to each other and our family remains just as strong as it did when we got married. Our bond is stronger. I love being married to FullEclipse.
But, these celebrations have me thinking. As much as our wedding was special, I feel like we should have had a bigger celebration. My in-laws missed everything. My plan is to renew our vows in a few years. I am paying off all of our credit card debt first. I will have my big celebration with my husband and my two children.
Time flies especially when you are unprepared!
K2 will be here in six weeks. I spoke to my new doctor yesterday and he told me that the tentative schedule for the c-section is 8/24/11.
How will our family be affected by the new arrival?
Will the dogs be okay with the new baby?
What about the limited space we have?
Where is my driver's license?
Why didn't I learn to drive before I got pregnant?
How will FullEclipse and I make time to give each kid the name they need?
Oh My God, who will take care of me while I get over the c-section?
What if I go into panic mode while I am on the operating table?
FullEclipse will be at work this time around. He will not be able to stay over night at the hospital like he did with Paczki. We had such a nice time bonding. It was the K family against the world.
Taking care of K2 will be my sole responsibility. I feel bad for this kid. I really do! FullEclipse was the best stay at home parent. Paczki thrived with him. I don't know if I can even come close to being as good as my husband. He did everything!
But, I gotta sit down and relax.
Things will work out. I am blessed to have this child. I am lucky to have a daughter who is growing strong everyday and a husband who loves me.
I am lucky to have gotten pregnant when a lot of women struggle with fertility.
I do, however, need to get my act together and buy K2 a crib!


text:
2023 Millennium Leadership Initiative (MLI)
The American Association of State Colleges and Universities (AASCU)'s Millennium Leadership Initiative (MLI) was established to assist individuals derived from groups seriously underrepresented at these exclusive leadership levels. Due to rapidly shifting demographics in higher education, there was a clear need to bring diversity to this critical leadership level to reflect the changing diversity of the nation's student population. As a result of more than a decade of success in meeting this challenge, the MLI continues to maintain its historical preferences while accepting any qualified candidates.
MLI attracts a prestigious faculty of presidents and chancellors and experts in advancement and fundraising, governance, leadership building, financial management, and the executive search process. Participants experience both theoretical learning and practical, hands-on training through case studies, workshops, and close interactions with presidents and chancellors.
Apply Now to Participate in the 2023-2024 Cohort
All submissions require an endorsement form (completed by president/chancellor) and application form (completed by candidate) to be submitted by Feb. 6, 2023.
APPLICATION ENDORSEMENT
The 2023 MLI program is designed for senior academic and administrative professionals traditionally underrepresented in the highest ranks of higher education seeking to advance to the presidency.
Participants apply to the MLI program and are admitted through a rigorous selection process administered by a selection committee composed exclusively of presidents and chancellors. Successful applicants will demonstrate the leadership experience necessary to achieve a presidency or chancellorship in higher education or to advance to the role of vice president or provost. In addition to the above experience, candidates must possess a terminal degree or have significant experience as a senior executive. Equivalent experience outside the academy in government or the private sector is acceptable. All candidates must be nominated by a president or chancellor, or by a chief operating officer from a large complex university or organization.
Timeline & Format
The 2023 MLI program will take place from June 2023 to spring 2024.
Programming will be delivered in a blended format, with in-person and virtual programming. In-person programming will not be livestreamed or otherwise recorded. One-hundred percent attendance and participation is expected. Virtual programming will take place on select Tuesdays between 3 and 5 p.m. ET (exact dates TBD).
The program will feature:
Up to six interactive 60- to 120-minute virtual sessions (August–February)
An opening in-person seminar at AASCU's Washington, D.C., office (June 7–10, 2023)
A closing in-person seminar at AASCU's Washington, D.C., office (March 13 – 15, 2024)
Mentoring with current AASCU presidents and chancellors (October 2023–May 2024)
Coaching with retired/former AASCU presidents and chancellors (November 2023–March 2024)
The cohort is limited to 25 participants.
(to be paid upon acceptance to the program)
For applicants from AASCU plus MLI or AASCU plus APLU member institutions: $5,500
For applicants from AASCU or APLU member institutions: $6,500
For applicants from non-member institutions: $7,500
Upon acceptance to MLI, participants must register no later than Apr. 28, 2023.
The registration fee covers program administration, meals during the in-person seminars, and all materials. Participants are responsible for their own travel and accommodation in Washington, D.C.
For a list of AASCU member institutions, visit www.aascu.org/members.
Participants from AASCU member institutions: $500
Participants from non-member institutions: $750
Cancellations received in writing by Apr. 3 will receive a full refund. Cancellations received by Apr. 17 will incur the above administrative fees. Refunds are not available for cancellations after Apr. 17. Email Sara Gassman and include "MLI Cancellation" in the subject line.
Please note that limited scholarships will be made available to accepted applicants.
(June 7-10, 2023 )
2022 MLI Testimonials
"Profoundly transformational! Eye-opening and edifying! Thank you. I deeply appreciate being exposed to such an outstanding group of presenters and colleagues. I am changed for the better."
"Epiphany after epiphany … excellent! Great use of my time."
"I felt like I had the privilege of sitting at the various presidents' kitchen tables and hearing the real and authentic conversations."
Apply for the 2023 Cohort
Applications are due on
Feb. 6, 2023.
Applicants must ask their president/cabinet level administrator to complete
this endorsement form by
Contact MLI
Karusha Naidoo
Director of Leadership Development and Millennium Leadership Initiative


X3 system: Made in the USA. 500+LBS Capacity.
The most complex resistance bands ever made. Four different levels of resistance made from layered latex of exceptional thickness.
12 week X3 workout program including a video for every workout, nutrition advice and more, for maximum gains and fat loss.
"I'm totally blown away by this thing...three times the muscle per minute of exercise."
Get £50+ off the X3.
Talk is cheap, so we made a video. The X3 is a serious piece of equipment. Note that getting forces this high may require you to buy an extra heavy band. We recommend the Iron Woody Fitness #6 or #7 if you're an elite level lifter.
Copyright © 2019, Titan Biomedical.


text:
Female Office Rivalries Are A Myth, According To New Book
Best Work-From-Home Jobs to Apply to in 2020
How to Manage a Dual Career Family
Tips and tricks for managing one household with two incomes
Almost half of marriages in the U.S. are made up of dual-career couples where both partners work, says the Bureau of Labor Statistics; a McKinsey study suggests that number is even higher, in the 70+% range. In those families, household responsibilities—whether they are chores or cooking or children's carpools—don't automatically go to the one parent who stays at home, since that parent doesn't exist.
And while the benefits of dual-career families include improved financial security and the opportunity for both parents to find professional fulfillment, the costs aren't negligible. Managing logistics becomes a huge responsibility, burnout risk rises, and childcare gets more complicated.
Whether you are currently in a dual-career relationship or envision having one in the future, don't fear: it is possible to support your careers and your family at the same time.
The Harvard Business Review highlights the most important one in their to-the-point article titled "If You Can't Find a Spouse Who Supports Your Career, Stay Single": make sure you first choose the right partner!
This is especially important for women in heterosexual relationships, where longstanding gender norms about who stays home to clean and who goes out to bring home the bacon may put pressure on the woman's career path. "Professionally ambitious women really only have two options when it comes to their personal partners — a super-supportive partner or no partner at all. Anything in between ends up being a morale- and career-sapping morass," writes Avivah Wittenberg-Cox. She goes on to cite a troubling study of HBS grads, where the majority of male graduates, despite being exposed to research like hers, expect their career to take precedence over their wives' careers. Yikes.
So first things first: shack up only after confirming (I'd suggest in writing) that your partner is as committed to your career as they are to theirs. If it's too late for you…ask for a divorce lawyer from your friends this holiday season?
From then on: follow these tips to manage your dual-career family.
Agree on Ambition
Figuring out the best way to support your career along with your partner's will require first defining what success in that career looks like for each of you. Is your goal to be a partner at your firm? Is theirs to start their own company? Or are your goals financial — like reaching a salary of $80,000 a year to support other goals around retirement and children? Whatever your goals, be clear whether one person's path takes precedence over the other's, or whether you'll support both equally. You might also establish timeframes in which one person's career will take precedence (e.g. focusing on your partner's startup for the next 5 years, after which the focus will be on you so you can go back to school part-time for your masters) — just because one person is the priority one year doesn't mean it has to stay that way. You both just need to be aligned about what's being prioritized and what's being sacrificed.
For serial entrepreneur Christine Perkett, CEO of Mindfull Marketing, and her husband Phil, who works in software sales, success meant following their passions and having enough financial success to support their blended family. "Be sure you're passionate about working because juggling two careers and a family can be tough," says Perkett. "It's busy and hectic and requires, as any relationship dynamic, compromise. You can both feel burned out and you still have kiddos to be there for and take care of. You have to take turns and compromise. And never think that your career is more important than the other."
Plan for Childcare
If you're part of a dual-career couple that has or plans to have children, get in sync with your partner about how you'll deal with childcare. Perkett notes that someone staying home to save money isn't the best solution for all couples: "Often couples decide one shouldn't work because childcare can essentially negate the salary. But remember that working isn't just about money. It can (and should) be about passion and getting out of the house and having a sense of purpose."
Alexandra Fung, lawyer and CEO of recommendation-sharing site Upparent, and her psychologist husband were committed to being the primary caretakers for their first child when she was born. Both were finishing their higher-education degrees in New York City when their daughter was born, which meant childcare occasionally included exchanging their infant daughter while one parent was en route to class and the other was on their way home. But that's what worked for them! (And I imagine their daughter is now entirely at home in the New York subway system as a result.)
Get creative with how you'll deal with childcare. There are plenty of viable set-ups that don't include one person putting their career ambition on hold to stay at home full-time.
Chore Charts (And Calendars) Coming Your Way
Assuming that your partner will come home from work and magically know that you already did the laundry, so therefore they should handle dinner is a recipe for disaster. And if you have multiple children, balancing household chores along with soccer practice snack duty, school drop-off and pick-up, and PTA meetings on top of two full-time jobs, is a nightmare waiting to happen. (Dual-career married couple Elizabeth DeFinnis, who is an occupational therapist, and her credit union manager husband call their life an "organized tornado," which seems about perfect.) Making a system to deal with responsibilities is the only way to get through it.
For Melanie Musson, a writer for ExpertInsuranceReviews.com, and her police officer husband, divvying up tasks keeps their system afloat: she makes dinner, he does dishes. "We're both tired at the end of the day, but we have four children and if you let things go for just one day, the aftermath can seem insurmountable," she says.
Amanda Holdsworth, director of PR and brand strategy at Reink Media Group, and her logistics firm co-owner husband espouse a similar approach—he does grocery shopping and cooking, she does cleaning and laundry—but have taken it a step further with a color-coded family calendar that hangs in their mudroom. They even have shared Google Sheets for planning Christmas gifts, monthly budgets, and annual membership fees. Get on their level.
Be Each Other's Biggest Cheerleader
At the end of the day, it's you and your partner in it together, dealing with the joys and terrors of running a household, raising children, managing finances, and pursuing your career goals. That partnership will go a lot more smoothly if you can remember that you love this person you've chosen to spend your life with, even when they forget to pick up your shirts from the dry cleaner or serve microwave pizza four dinners in a row.
Avoid keeping a running tally of who's done what; it can only end in frustration. "It's so natural and easy to think only about one's own self and pending tasks. And when you do that, you fall into the 'woe is me' trap where you see all the things you do to contribute, but you fail to see what your partner does. Be attentive to what your partner is facing at work and the struggles [they are] overcoming. Notice the contributions they make to your family and home life. Offer your spouse encouragement, praise, and thanks. A few words of support go a long way in lifting each other up," says Musson.
Holdsworth reminds dual-career couples to have perspective. "You can easily get hammered at work; I've worked at organizations (thankfully, not my current one) where every day felt like a battle zone. So to come home and have my husband tell me he's proud of me has meant the world. And I do the same," she says. "There are no egos."
Finding Balance as a Dual-Career Couple ›


text:
Home/Political News/President Biden Announces 4th Slate of Judicial Nominations
President Biden Announces 4th Slate of Judicial Nominations
J. Williams June 17, 2021 Political News Leave a comment
As he continues to move at a historic pace with respect to his judicial nominations, President Biden is announcing five new candidates for the federal bench and two new candidates for District of Columbia courts.
These choices also continue to fulfill the President's promise to ensure that the nation's courts reflect the diversity that is one of our greatest assets as a country — both in terms of personal and professional backgrounds.
For example, this slate includes:
A nationally recognized leader on voting rights who would be the only Latina serving on the U.S. Court of Appeals for the Second Circuit
A former public defender and current civil rights plaintiff's attorney who would be the second African American woman judge actively serving on the District Court for the District of Columbia
A federal prosecutor who would be the first judge of South Asian descent to serve on the District Court for the District of Connecticut
A Department of Justice lawyer with two decades of civil rights experience, including as Deputy Chief of the Appellate Section in the Civil Rights Division
A magistrate judge on the D.C. Superior Court with deep experience in domestic violence and family law issues
This is President Biden's fourth round of names for federal judicial positions, bringing his number of announced federal judicial nominations to 24.
Nominated to the United States Court of Appeals for the Second Circuit
Myrna Pérez is the director of the Brennan Center's Voting Rights and Elections Program at the New York University (NYU) School of Law. Ms. Pérez joined the Brennan Center in 2006. She has also been a lecturer-in-law at Columbia Law School since 2016. She previously served as an Adjunct Professor of Clinical Law at NYU School of Law from 2013 to 2015. Prior to joining the Brennan Center, Ms. Pérez was a Civil Rights Fellow at Relman, Dane & Colfax, a civil rights law firm in Washington, D.C., from 2005 to 2006. Ms. Pérez served as a law clerk for Judge Anita B. Brody on the U.S. District Court for the Eastern District of Pennsylvania from 2003 to 2004 and for Judge Julio M. Fuentes on the U.S. Court of Appeals for the Third Circuit from 2004 to 2005.
Nominated to the United States District Court for the District of Connecticut
Judge Sarah A. L. Merriam has served as a U.S. Magistrate Judge for the District of Connecticut since 2015. Previously, she served as an Assistant Federal Public Defender for the District of Connecticut from 2007 to 2015. Judge Merriam worked on political campaigns in Connecticut from 2006 to 2007. She was an associate at the Connecticut-based law firm Cowdery, Ecker & Murphy from 2003 to 2006. Judge Merriam served as a law clerk for Judge Thomas Meskill on the U.S. Court of Appeals for the Second Circuit from 2002 to 2003 and for Judge Alvin Thompson on the U.S. District Court for the District of Connecticut from 2000 to 2002.
Sarala Vidya Nagala is the Deputy Chief of the Major Crimes Unit in the U.S. Attorney's Office in the District of Connecticut, a role she has held since 2017. Ms. Nagala joined the U.S. Attorney's Office in 2012, and has served in a number of leadership roles in the office, including as Hate Crimes Coordinator. Previously, Ms. Nagala was an associate at Munger, Tolles, & Olson in San Francisco, California from 2009 to 2012. She began her legal career as a law clerk for Judge Susan Graber on the U.S. Court of Appeals for the Ninth Circuit from 2008 to 2009.
Judge Omar A. Williams has served as a Superior Court Judge in Hartford, Connecticut since 2016. In that role, Judge Williams has served on the New England Regional Judicial Opioid Initiative, the Sentence Review Division, and the Wiretap Panel. He was previously a Superior Court Judge in New London from 2014 to 2016. Prior to his appointment as a state court judge, Judge Williams was an Assistant Public Defender for the State of Connecticut Division of Public Defender Services from 2003 to 2014.
Nominated to the United States District Court for the District of Columbia
Jia M. Cobb is a partner at Relman Colfax, where she has practiced since 2012. Her plaintiff-side litigation practice focuses on fair housing, disability discrimination, employment discrimination, and cases at the intersection of civil rights and criminal justice. Ms. Cobb taught trial advocacy as an adjunct professor at American University Washington College of Law in 2011 and was a faculty member at Harvard Law School's Trial Advocacy Workshop in 2010 and 2011. Prior to her time in private practice, Ms. Cobb served as a trial attorney at the Public Defender Service for the District of Columbia from 2006 to 2012. She began her legal career as a law clerk for Judge Diane Wood on the U.S. Court of Appeals for the Seventh Circuit from 2005 to 2006.
On the local level, President Biden nominated Tovah R. Calderon to the District of Columbia Court of Appeals and Judge Kenia Seoane Lopez to the Superior Court of the District of Columbia.
President Biden's first judicial nominations announcement was made faster than that of any new President in modern American history, and today's announcement continues that trend.
Tags Connecticut District Court Federal judges Jia M. Cobb Judge Kenia Seoane Lopez Judge Omar A. Williams Judge Sarah A. L. Merriam Judicial nominees Myrna Pérez President Joe Biden Sarala Vidya Nagala Second Circuit Tovah R. Calderon Washington D.C.
Previous McConnell: GOP-Controlled Senate Would Block Supreme Court Nominee
Next Education Department says LGBTQ Students Are Protected By Title IX


Twin Waters Designs, exclusive to G.J. Gardner Homes, has been created to impress and to deliver comfort - a winning combination. The light and airy feel to the house lifts the spirits, supported by the resort-like alfresco and huge retreat areas which are sure to become your family's favourite spaces.
The Twin Waters Designs offer individually crafted facades that will inspire you by providing a choice of homes that reflect your personal lifestyle and preferences.


text:
Hartsville Free and Accepted Masons
Degree Work
Yearly Installations
50 Year Award of Gold
25 Year Award of Silver
2014 Annual Picnic
2014 Cook Out
Order of the Rusty Nail
Annual Past Master Degree Group Photo
On March 31st, 1877 the Dispensation was given by Frank S. Devol, Grand Master, Grand Lodge of Indiana, to form Hartsville Lodge No.547. The Dispensation was attested by John M. Bramwell, Grand Secretary, Grand Lodge of Indiana. The Dispensation was read and accepted by Hartsville Lodge No. 547 on the 6th day of April, 1877. On May 22nd, 1877 the Grand Lodge of Indiana, during the annual communication in Indianapolis, issued Hartsville Lodge No.547 it's Charter.
Hartsville Lodge has met in the following buildings and/or locations throughout the years:
The 1st building the Lodge met in was owned by Mr. E.D. Hamilton (The building was known as Hamilton Hall)
In 1884 the Lodge moved locations and met in an upstairs room above a grocery store on the North side of the square until 1891.
September 12th, 1891 - 1897
In September of 1891 the lodge moved locations and rented an upstairs room in a building on the square from a Mrs. Kinsley
July 31st, 1897 - 1902
In July of 1897 the Lodge rents from William C. Clapp the upstairs of his business block on the West side of the Square
In September of 1902 the Lodge bought the building 35' by 44' on the Southeast corner of Lot 36 from William C. Clapp and the Lodge was at that location until 1919.
In 1919 the Lodge purchased a building on the West side of the square.
The upstairs was used for the Lodge Hall and the bottom was used for White's Grocery
(The grocery was later owned by Tracy Mahaffey)
October 4th, 1939 - 2001
On October 4th, 1939 the Lodge bought the brick building on the Southwest corner of the square, known as the Cottle Block, from the Morrison Brothers. (The building was originally the Hartsville Opera House)
In July of 2001 Hartsville Lodge moved it's Charter to the Hope Lodge No. 150's building.
Hartsville Lodge shares the building and resources with Hope Lodge No 150,
Camon Lodge No. 343, Hartsville Eastern Star and Clifford Eastern Star.