def import_openwaters(filename):

    for openwater_definition in retrieve_definitions(filename):
        print openwater_definition
        openwater = OpenWater()

        openwater.name = openwater_definition['name']
        openwater.slug = "Aetseveldsepolder oost - %s" % openwater.name

        openwater.surface = int(float(openwater_definition['surface']))
        openwater.save()
def upload_settings_from_excelfile(xls_file_name, load_excel_reference_results=False):
    """load settings from the waternet excelfile format

    """

    print "start import file %s" % xls_file_name

    xls = xlrd.open_workbook(xls_file_name)
    sheet = xls.sheet_by_name("uitgangspunten")

    wb_area_name = sheet.cell(0, 0).value

    pars = {}

    pars["rainfall_day"], new = Parameter.objects.get_or_create(
        name="dag neerslag",
        unit="mm/dag",
        parameter=Parameter.PARAMETER_PRECIPITATION,
        sourcetype=Parameter.TYPE_MEASURED,
    )
    pars["evaporation_day"], new = Parameter.objects.get_or_create(
        name="dag verdamping",
        unit="mm/dag",
        parameter=Parameter.PARAMETER_EVAPORATION,
        sourcetype=Parameter.TYPE_MEASURED,
    )
    pars["min_level"], new = Parameter.objects.get_or_create(
        name="minimum waterpeil",
        unit="mNAP",
        parameter=Parameter.PARAMETER_WATERLEVEL,
        sourcetype=Parameter.TYPE_USERINPUT,
    )
    pars["max_level"], new = Parameter.objects.get_or_create(
        name="maximum waterpeil",
        unit="mNAP",
        parameter=Parameter.PARAMETER_WATERLEVEL,
        sourcetype=Parameter.TYPE_USERINPUT,
    )
    pars["reference_waterlevel_day"], new = Parameter.objects.get_or_create(
        name="gemeten waterpeil",
        unit="mNAP",
        parameter=Parameter.PARAMETER_WATERLEVEL,
        sourcetype=Parameter.TYPE_MEASURED,
    )
    pars["seepage_infiltration"], new = Parameter.objects.get_or_create(
        name="kwel(+) en wegzijging(-)",
        unit="mm/dag",
        parameter=Parameter.PARAMETER_SEEPAGE_INFILTRATION,
        sourcetype=Parameter.TYPE_USERINPUT,
    )
    pars["seepage"], new = Parameter.objects.get_or_create(
        name="kwel",
        unit="mm/dag",
        parameter=Parameter.PARAMETER_SEEPAGE_INFILTRATION,
        sourcetype=Parameter.TYPE_USERINPUT,
    )
    pars["infiltration"], new = Parameter.objects.get_or_create(
        name="wegzijging",
        unit="mm/dag",
        parameter=Parameter.PARAMETER_SEEPAGE_INFILTRATION,
        sourcetype=Parameter.TYPE_USERINPUT,
    )
    pars["structure_discharge"], new = Parameter.objects.get_or_create(
        name="kunstwerk_debiet",
        unit="m3/dag",
        parameter=Parameter.PARAMETER_DISCHARGE,
        sourcetype=Parameter.TYPE_MEASURED,
    )
    pars["chloride_measurement"], new = Parameter.objects.get_or_create(
        name="gemeten chloride", unit="mg/l", parameter=Parameter.PARAMETER_CHLORIDE, sourcetype=Parameter.TYPE_MEASURED
    )
    pars["sewer_day"], new = Parameter.objects.get_or_create(
        name="referentie gemengde riolering", unit="m3/dag/ha", sourcetype=Parameter.TYPE_USERINPUT
    )

    try:
        area = WaterbalanceArea.objects.get(name=wb_area_name)
        scenario, new = WaterbalanceScenario.objects.get_or_create(name="import")
    except (WaterbalanceArea.DoesNotExist):
        print "geometry van gebied %s niet gevonden" % wb_area_name
        area, new = WaterbalanceArea.objects.get_or_create(name="%s n.g." % wb_area_name)
        # create scenario with name and link areas later manually
        scenario, new = WaterbalanceScenario.objects.get_or_create(name="import")

    config, config_new = WaterbalanceConf.objects.get_or_create(
        waterbalance_scenario=scenario,
        waterbalance_area=area,
        defaults={
            "calculation_start_date": datetime(1996, 1, 1),
            "description": "%s - %s" % (str(area.name), str(scenario.name)),
        },
    )

    labels = {}
    labels[(0, "neerslag")], new = Label.objects.get_or_create(
        program_name="precipitation"
    )  # name='neerslag', flow_type=Label.TYPE_IN, defaucolor='#0000ff', color_increment='#ff0000')
    labels[(1, "kwel")], new = Label.objects.get_or_create(
        program_name="seepage"
    )  # name='kwel', flow_type=Label.TYPE_IN, color='#ffbb00', color_increment='#ff0000')
    labels[(2, "verhard")], new = Label.objects.get_or_create(
        program_name="hardened"
    )  # name='verhard', flow_type=Label.TYPE_IN, color='#bbbbbb', color_increment='#ff0000')
    labels[(3, "riolering")], new = Label.objects.get_or_create(
        program_name="sewer"
    )  # name='riolering', flow_type=Label.TYPE_IN, color='#006600', color_increment='#ff0000')
    labels[(4, "gedraineerd")], new = Label.objects.get_or_create(
        program_name="drained"
    )  # name='gedraineerd', flow_type=Label.TYPE_IN, color='#ff9900', color_increment='#ff0000')
    labels[(5, "uitspoeling")], new = Label.objects.get_or_create(
        program_name="undrained"
    )  # name='uitspoeling', flow_type=Label.TYPE_IN, color='#00ff00', color_increment='#ff0000')
    labels[(6, "afstroming")], new = Label.objects.get_or_create(
        program_name="flow_off"
    )  # name='afstroming', flow_type=Label.TYPE_IN, color='#008800', color_increment='#ff0000')

    labels[(7, "inlaat1")], new = Label.objects.get_or_create(
        program_name="inlet1"
    )  # name='inlaat 1', flow_type=Label.TYPE_IN, color='#ff00ff', color_increment='#ff0000')
    labels[(8, "inlaat2")], new = Label.objects.get_or_create(
        program_name="inlet2"
    )  # name='inlaat 2', flow_type=Label.TYPE_IN, color='#cc00cc', color_increment='#ff0000')
    labels[(9, "inlaat3")], new = Label.objects.get_or_create(
        program_name="inlet3"
    )  # name='inlaat 3',, flow_type=Label.TYPE_IN, color='#aa00aa', color_increment='#ff0000')
    labels[(10, "inlaat4")], new = Label.objects.get_or_create(
        program_name="inlet4"
    )  # name='inlaat 4',, flow_type=Label.TYPE_IN, color='#880088', color_increment='#ff0000')
    labels[(11, "inlaat peilhandhaving")], new = Label.objects.get_or_create(
        program_name="intake_wl_control"
    )  # name='inlaat peilhandhaving',, flow_type=Label.TYPE_IN, color='#440044', color_increment='#ff0000')

    labels[(-1, "initieel")], new = Label.objects.get_or_create(
        program_name="initial"
    )  # name='afstroming', flow_type=Label.TYPE_IN, color='#008800', color_increment='#ff0000')

    labels[(-1, "uitlaat1")], new = Label.objects.get_or_create(
        program_name="outtake1"
    )  # name='uitlaat 1', flow_type=Label.TYPE_OUT, color='#663399', color_increment='#ff0000')
    labels[(-1, "uitlaat2")], new = Label.objects.get_or_create(
        program_name="outtake2"
    )  # name='uitlaat 2', flow_type=Label.TYPE_OUT, color='#443399', color_increment='#ff0000')
    labels[(-1, "uitlaat3")], new = Label.objects.get_or_create(
        program_name="outtake3"
    )  # name='uitlaat 3', flow_type=Label.TYPE_OUT, color='#666699', color_increment='#ff0000')
    labels[(-1, "uitlaat4")], new = Label.objects.get_or_create(
        program_name="outtake4"
    )  # name='uitlaat 4', flow_type=Label.TYPE_OUT, color='#554499', color_increment='#ff0000')
    labels[(-1, "uitlaat5")], new = Label.objects.get_or_create(
        program_name="outtake5"
    )  # name='uitlaat 5', flow_type=Label.TYPE_OUT, color='#6633bb', color_increment='#ff0000')
    labels[(-1, "uitlaat6")], new = Label.objects.get_or_create(
        program_name="outtake6"
    )  # name='uitlaat 6', flow_type=Label.TYPE_OUT, color='#662288', color_increment='#ff0000')
    labels[(-1, "uitlaat peilhandhaving")], new = Label.objects.get_or_create(
        program_name="outtake_wl_control"
    )  # name='uitlaat peilhandhaving',  flow_type=Label.TYPE_OUT, color='#7744aa', color_increment='#ff0000')

    # lab, new = Label.objects.get_or_create(name='intrek', program_name='indraft')#, flow_type=Label.TYPE_OUT, color='#ff9911', color_increment='#ff0000')
    # lab, new = Label.objects.get_or_create(name='wegzijging', program_name='infiltration')#, flow_type=Label.TYPE_OUT, color='#444444', color_increment='#ff0000')
    # lab, new = Label.objects.get_or_create(name='verdamping', program_name='evaporation')#, flow_type=Label.TYPE_OUT, color='#0000ff', color_increment='#ff0000')
    # lab, new = Label.objects.get_or_create(name='sluitfout', program_name='sluice_error')#, flow_type=Label.TYPE_ERROR, color='#000000', color_increment='#000000')
    # lab, new = Label.objects.get_or_create(name='gemeten waterpeil', program_name='meas_waterlevel')# flow_type=Label.TYPE_OTHER, color='#0000ff', color_increment='#000000')
    # lab, new = Label.objects.get_or_create(program_name='calc_waterlevel')#name='berekend waterpeil', , flow_type=Label.TYPE_ERROR, color='#ff00ff', color_increment='#000000')
    # lab, new = Label.objects.get_or_create(program_name='initial')#name='initieel', , flow_type=Label.TYPE_ERROR, color='#222222', color_increment='#000000')
    # initieel nog invullen

    def get_float_or_default(sheet, rownr, colnr, default_value=None):
        if type(sheet.cell(rownr, colnr).value).__name__ in ["int", "float"]:
            return float(sheet.cell(rownr, colnr).value)
        else:
            return default_value

    for key, label in labels.items():
        rownr = key[0] + 16
        if key[0] >= 0 and (
            type(sheet.cell(key[0] + 16, 1).value).__name__ in ["str", "unicode"]
            or get_float_or_default(sheet, key[0] + 16, 1) is not None
        ):
            concentration, new = Concentration.objects.get_or_create(label=label, configuration=config)

            concentration.stof_lower_concentration = get_float_or_default(sheet, rownr, 3, 0)
            concentration.stof_increment = get_float_or_default(sheet, rownr, 4, 0)

            concentration.cl_concentration = get_float_or_default(sheet, rownr, 5, 0)

            concentration.p_lower_concentration = get_float_or_default(sheet, rownr, 7, 0)
            concentration.p_incremental = get_float_or_default(sheet, rownr, 8, 0)

            concentration.n_lower_concentration = get_float_or_default(sheet, rownr, 9)
            concentration.n_incremental = get_float_or_default(sheet, rownr, 10)

            concentration.so4_lower_concentration = get_float_or_default(sheet, rownr, 11)
            concentration.so4_incremental = get_float_or_default(sheet, rownr, 12)

            concentration.save()

    labels = dict([(key[1], label) for key, label in labels.items()])

    concentration, new = Concentration.objects.get_or_create(label=labels["initieel"], configuration=config)
    concentration.stof_lower_concentration = 0
    concentration.stof_increment = 0
    concentration.cl_concentration = get_float_or_default(sheet, 32, 3, 0)
    concentration.p_lower_concentration = 0
    concentration.p_incremental = 0
    concentration.save()

    if config_new:
        WaterbalanceTimeserie.objects.filter(configuration_references=config)
        # reference - waterlevel
        parameter = pars["reference_waterlevel_day"]
        if WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config).count() == 0:
            wb_timeserie = WaterbalanceTimeserie.objects.create(
                parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
            )
            config.references.add(wb_timeserie)
        else:
            wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config)[0]
        save_timeserie_into_database(wb_timeserie, sheet, 76, 0, 3, stick_to_last_value=True)

        # reference - chloride
        parameter = pars["chloride_measurement"]
        if WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config).count() == 0:
            wb_timeserie = WaterbalanceTimeserie.objects.create(
                parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
            )
            config.references.add(wb_timeserie)
        else:
            wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config)[0]
        save_timeserie_into_database(wb_timeserie, sheet, 76, 22, 23)

        # reference - chloride
        parameter = pars["chloride_measurement"]
        if WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config).count() == 0:
            wb_timeserie = WaterbalanceTimeserie.objects.create(
                parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
            )
            config.references.add(wb_timeserie)
        else:
            wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_references=config)[0]
        save_timeserie_into_database(wb_timeserie, sheet, 76, 24, 25)

    # results - todo
    if config.open_water:
        open_water = config.open_water
        new = False
    else:
        open_water = OpenWater()
        new = True

    ######################### OpenWater ##############################

    open_water.name = config.__unicode__()
    open_water.surface = sheet.cell(10, 3).value
    open_water.bottom_height = sheet.cell(11, 3).value  # is average depth
    open_water.init_water_level = sheet.cell(67, 3).value

    # precipitation
    parameter = pars["rainfall_day"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_precipitation=config).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
        )
        open_water.precipitation = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_precipitation=config)[0]
    save_timeserie_into_database(wb_timeserie, sheet, 76, 0, 1)

    # evaporation
    parameter = pars["evaporation_day"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_evaporation=config).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
        )
        open_water.evaporation = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, configuration_evaporation=config)[0]
    save_timeserie_into_database(wb_timeserie, sheet, 76, 0, 2)

    # sewer
    parameter = pars["sewer_day"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_sewer=open_water).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (config.__unicode__()[:30], parameter.name[:15])
        )
        open_water.sewer = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_sewer=open_water)[0]
    save_timeserie_into_database(wb_timeserie, sheet, 76, 0, 9)

    # minimum_level
    array_with_values = [
        [1, 1, float(sheet.cell(66, 2).value)],
        [3, 15, float(sheet.cell(63, 2).value)],
        [5, 1, float(sheet.cell(64, 2).value)],
        [8, 15, float(sheet.cell(65, 2).value)],
        [10, 1, float(sheet.cell(66, 2).value)],
    ]
    parameter = pars["min_level"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_min_level=open_water).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (open_water.__unicode__()[:30], parameter.name[:15])
        )
        open_water.minimum_level = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_min_level=open_water)[0]
    create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values, stick_to_last_value=True)

    # maximum_level
    array_with_values = [
        [1, 1, float(sheet.cell(66, 4).value)],
        [3, 15, float(sheet.cell(63, 4).value)],
        [5, 1, float(sheet.cell(64, 4).value)],
        [8, 15, float(sheet.cell(65, 4).value)],
        [10, 1, float(sheet.cell(66, 4).value)],
    ]
    parameter = pars["max_level"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_max_level=open_water).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (open_water.__unicode__()[:30], parameter.name[:15])
        )
        open_water.maximum_level = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_max_level=open_water)[0]
    create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values)

    # target_level, wordt niet gebruikt, weg?

    # seepage
    array_with_values = [[1, 1, float(sheet.cell(36, 1).value)]]
    parameter = pars["seepage"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_seepage=open_water).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (open_water.__unicode__()[:30], parameter.name[:15])
        )
        open_water.seepage = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_seepage=open_water)[0]
    create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values)

    array_with_values = [[1, 1, float(sheet.cell(36, 1).value)]]
    parameter = pars["infiltration"]
    if WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_infiltration=open_water).count() == 0:
        wb_timeserie = WaterbalanceTimeserie.objects.create(
            parameter=parameter, name="%s: %s" % (open_water.__unicode__()[:30], parameter.name[:15])
        )
        open_water.infiltration = wb_timeserie
    else:
        wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, open_water_infiltration=open_water)[0]
    create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values)

    open_water.save()

    config.open_water = open_water
    config.save()

    ######################### Buckets ##############################

    bucket_nr = 0
    for colnr in range(4, 12):
        if sheet.cell(34, colnr).value > 0 and (
            sheet.cell(34, colnr).value < sheet.cell(34, colnr + 1).value or sheet.cell(34, colnr + 1).value < 0
        ):
            bucket_nr = bucket_nr + 1

            if sheet.cell(34, colnr).value == sheet.cell(34, colnr - 1).value:
                # dubble bucket
                print "dubbel bucket: %s" % sheet.cell(35, colnr - 1).value
                col_upper = colnr - 1
                col_lower = colnr

                name = sheet.cell(35, col_upper).value

                if name.find("verhard") >= 0:
                    surface_type = Bucket.HARDENED_SURFACE
                elif name.find("gedraineerd") >= 0:
                    surface_type = Bucket.DRAINED_SURFACE
                else:
                    print 'WARNING: bucket type "%s"unknown, take drained' % name
                    surface_type = Bucket.DRAINED_SURFACE

            else:
                # single bucket
                print "single bucket: %s" % sheet.cell(35, colnr).value
                col_upper = colnr
                col_lower = colnr

                name = sheet.cell(35, col_upper).value

                if name.find("stedelijk") >= 0:
                    bucket.surface_type = Bucket.STEDELIJK_SURFACE
                elif name.find("ongedraineerd") >= 0:
                    bucket.surface_type = Bucket.UNDRAINED_SURFACE
                elif name.lower().find("verhard") >= 0:
                    bucket.surface_type = Bucket.HARDENED_SURFACE
                elif name.lower().find("landelijk") >= 0:
                    bucket.surface_type = Bucket.UNDRAINED_SURFACE
                else:
                    print 'WARNING: bucket type "%s" unknown, take undrained' % name
                    bucket.surface_type = Bucket.UNDRAINED_SURFACE

            if Bucket.objects.filter(open_water=open_water, name=name, surface_type=surface_type).count() > 0:
                bucket = Bucket.objects.filter(open_water=open_water, name=name, surface_type=surface_type)[0]
            else:
                bucket = Bucket()

            bucket.name = name
            bucket.surface_type = surface_type
            bucket.slug = sheet.cell(35, col_upper).value
            bucket.surface = 0
            if not sheet.cell(43, col_upper).value in ["", None]:
                bucket.surface = sheet.cell(43, col_upper).value
            else:
                bucket.surface = 0
                print "Warning: bucket surface veld is leeg"

            bucket.open_water = open_water

            # upper
            if bucket.surface_type == Bucket.HARDENED_SURFACE:
                bucket.crop_evaporation_factor = 1
                bucket.min_crop_evaporation_factor = 1
            else:
                bucket.crop_evaporation_factor = 1
                bucket.min_crop_evaporation_factor = 0.75

            bucket.upper_porosity = sheet.cell(38, col_upper).value
            bucket.upper_drainage_fraction = sheet.cell(36, col_upper).value
            bucket.upper_indraft_fraction = sheet.cell(37, col_upper).value
            bucket.upper_max_water_level = sheet.cell(39, col_upper).value
            bucket.upper_equi_water_level = 0
            if type(sheet.cell(41, col_upper).value) == type(1) or type(sheet.cell(41, col_upper).value) == type(1.0):
                bucket.upper_min_water_level = sheet.cell(41, col_upper).value
            else:
                bucket.upper_min_water_level = None

            bucket.upper_init_water_level = sheet.cell(42, col_upper).value

            # lower

            bucket.porosity = sheet.cell(38, col_lower).value
            bucket.drainage_fraction = sheet.cell(36, col_lower).value
            bucket.indraft_fraction = sheet.cell(37, col_lower).value
            bucket.max_water_level = sheet.cell(39, col_lower).value
            bucket.equi_water_level = 0

            if type(sheet.cell(41, col_lower).value) == type(1) or type(sheet.cell(41, col_lower).value) == type(1.0):
                bucket.min_water_level = sheet.cell(41, col_lower).value
            else:
                bucket.min_water_level = None

            bucket.init_water_level = sheet.cell(42, col_lower).value

            # bucket.seepage
            array_with_values = [[1, 1, float(sheet.cell(37 + bucket_nr, 1).value)]]
            parameter = pars["seepage_infiltration"]
            if WaterbalanceTimeserie.objects.filter(parameter=parameter, bucket_seepage=bucket).count() == 0:
                wb_timeserie = WaterbalanceTimeserie.objects.create(
                    parameter=parameter, name="%s: %s" % (bucket.__unicode__()[:30], parameter.name[:15])
                )
                bucket.seepage = wb_timeserie
            else:
                wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, bucket_seepage=bucket)[0]
            create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values)

            bucket.save()
            # results

    ################# PUMPINGSTATIONS ################

    retrieve_intake_for_level_control(open_water, labels, sheet, pars)

    # uitlaat peilhandhaving
    if PumpingStation.objects.filter(open_water=open_water, name="uitlaat peilhandhaving").count() > 0:
        pumping_station = PumpingStation.objects.filter(open_water=open_water, name="uitlaat peilhandhaving")[0]
    else:
        pumping_station = PumpingStation()
        pumping_station.open_water = open_water
        pumping_station.name = "uitlaat peilhandhaving"
    pumping_station.into = False
    pumping_station.percentage = 100
    pumping_station.computed_level_control = True
    pumping_station.label = labels["uitlaat peilhandhaving"]
    if type(sheet.cell(70, 1).value) == type(1) or type(sheet.cell(70, 1).value) == type(1.0):
        pumping_station.max_discharge = sheet.cell(70, 1).value
    pumping_station.save()
    for colnr in range(4, 8):
        if (not sheet.cell(72, colnr).value == None) and len(str(sheet.cell(72, colnr).value)) > 0:
            name = "%s_%i" % (str(sheet.cell(72, colnr).value), colnr - 3)
            print "pomplijn voor %s op %d, %d met naam %s" % (pumping_station.__unicode__(), 72, colnr, name)
            if PumpLine.objects.filter(pumping_station=pumping_station, name=name):
                pump_line = PumpLine.objects.filter(pumping_station=pumping_station, name=name)[0]
            else:
                pump_line = PumpLine()
                pump_line.pumping_station = pumping_station
                pump_line.name = name

            parameter = pars["structure_discharge"]
            if WaterbalanceTimeserie.objects.filter(parameter=parameter, pump_line_timeserie=pump_line).count() == 0:
                wb_timeserie = WaterbalanceTimeserie.objects.create(
                    parameter=parameter, name="%s: %s" % (pump_line.__unicode__()[:30], parameter.name[:15])
                )
                pump_line.timeserie = wb_timeserie
            else:
                wb_timeserie = WaterbalanceTimeserie.objects.filter(parameter=parameter, pump_line_timeserie=pump_line)[
                    0
                ]
            save_timeserie_into_database(wb_timeserie, sheet, 76, 0, colnr)

            pump_line.save()

    # in- en uitlaten niet voor peilhandhaving
    inlaat_nr = 0
    uitlaat_nr = 0

    for row in range(23, 31):
        if sheet.cell(row, 1).value in [0] and sheet.cell(row, 2).value in [0]:
            pass
        elif sheet.cell(row, 1).value == "rekenhart":
            pass
        else:
            name = sheet.cell(row, 0).value
            if PumpingStation.objects.filter(open_water=open_water, name=name).count() > 0:
                pumping_station = PumpingStation.objects.filter(open_water=open_water, name=name)[0]
            else:
                pumping_station = PumpingStation()
                pumping_station.open_water = open_water
                pumping_station.name = name
            if row > 27:
                pumping_station.into = False
                uitlaat_nr += 1
                pumping_station.label = labels["uitlaat%i" % uitlaat_nr]

            else:
                pumping_station.into = True
                inlaat_nr += 1
                pumping_station.label = labels["inlaat%i" % inlaat_nr]

            pumping_station.percentage = 100
            pumping_station.computed_level_control = False
            pumping_station.max_discharge = None
            pumping_station.save()

            if PumpLine.objects.filter(pumping_station=pumping_station, name=name):
                pump_line = PumpLine.objects.filter(pumping_station=pumping_station, name=name)[0]
            else:
                pump_line = PumpLine()
                pump_line.pumping_station = pumping_station
                pump_line.name = pumping_station.name

            colnr = row - 13
            # as row in range(23, 31), colnr in range(10, 18)
            if (not sheet.cell(72, colnr).value == None) and len(str(sheet.cell(72, colnr).value)) > 0:
                name = "%s_%i" % (str(sheet.cell(72, colnr).value), colnr - 3)
                print "opgedrukte pomplijn voor %s op rij %d, kolom %d met naam %s" % (
                    pumping_station.__unicode__(),
                    72,
                    colnr,
                    name,
                )
                if PumpLine.objects.filter(pumping_station=pumping_station, name=name):
                    pump_line = PumpLine.objects.filter(pumping_station=pumping_station, name=name)[0]
                else:
                    pump_line = PumpLine()
                    pump_line.pumping_station = pumping_station
                    pump_line.name = name

                parameter = pars["structure_discharge"]
                if (
                    WaterbalanceTimeserie.objects.filter(parameter=parameter, pump_line_timeserie=pump_line).count()
                    == 0
                ):
                    wb_timeserie = WaterbalanceTimeserie.objects.create(
                        parameter=parameter, name="%s: %s" % (pump_line.__unicode__()[:30], parameter.name[:15])
                    )
                    pump_line.timeserie = wb_timeserie
                else:
                    wb_timeserie = WaterbalanceTimeserie.objects.filter(
                        parameter=parameter, pump_line_timeserie=pump_line
                    )[0]
                save_timeserie_into_database(wb_timeserie, sheet, 76, 0, colnr)

                pump_line.save()
            else:
                print "opgedrukte jaarlijkse tijdreeks voor %s met naam %s" % (pumping_station.__unicode__(), name)

                # discharge
                winter_value = 0
                summer_value = 1
                if not sheet.cell(row, 1).value in [0, "", None]:
                    winter_value = float(sheet.cell(row, 1).value)
                if not sheet.cell(row, 2).value in [0, "", None]:
                    summer_value = float(sheet.cell(row, 2).value)
                print row, winter_value, summer_value
                array_with_values = [[1, 1, winter_value], [4, 1, summer_value], [10, 1, winter_value]]
                parameter = pars["structure_discharge"]
                if (
                    WaterbalanceTimeserie.objects.filter(parameter=parameter, pump_line_timeserie=pump_line).count()
                    == 0
                ):
                    wb_timeserie = WaterbalanceTimeserie.objects.create(
                        parameter=parameter, name="%s: %s" % (pump_line.__unicode__()[:30], parameter.name[:15])
                    )
                    pump_line.timeserie = wb_timeserie
                else:
                    wb_timeserie = WaterbalanceTimeserie.objects.filter(
                        parameter=parameter, pump_line_timeserie=pump_line
                    )[0]
                create_save_yearly_timeserie_into_database(wb_timeserie, array_with_values)
                pump_line.save()