Das deutsche Python-Forum

Code: Alles auswählen

from __future__ import with_statement
with open("file") as f:
    header = f.readline()
    for line in f: # tue irgendwas

Code: Alles auswählen

#!/usr/bin/env python

from calendar import monthrange
from datetime import date
from dateutil.relativedelta import *
import locale
locale.setlocale(locale.LC_ALL, '')


#### Modules

# convert Dates in datetime.date
def ConvDat(d):
    sdat = d.split(".")
    day = int(sdat[0])
    month = int(sdat[1])
    year = int(sdat[2])
    if year < 1000: year += 2000
    conv_date =date(year, month, day)
    return conv_date

# calculate days in a month
def Daysmonth(date):
    d = (monthrange(date.year, date.month))[1]
    return d

# convert datetime.date as string
def Pdate(date):
    p = (str(date.year)	+ "."
        + str(date.month).zfill(2))
    return p

#### Main ####


# open files
try:
    input =  open('Smartlist.csv', 'r')
    output = open('Deferrals.csv', 'w')

except IOError:
    print 'file not found'


# Create headline
startdate_low = {}
enddate_high = {}
for line in input:
    row = line.rstrip('\n')                                         # read CSV-file
    if "Document Number" in row: continue                           # skip if Headline
    field =  row.split(";")                                         # split in fields
    if field[17].lower() != "y": continue                           # skip if not Deferrals
    if field[20].startswith("-"): continue 	                    # skip if alesprice empty
    salesprice = locale.atof(field[20])                             # convert salesprice in  local format
    if salesprice <= 0: continue                                    # skip if salesprice <0
    startdate_low[ConvDat(field[30])] = ''                          # add all existing startdates to a list
    enddate_high[ConvDat(field[31])] = ''                           # add all existing enddates to a list
    


ls = startdate_low.keys()                                           # calculate lowest startdate and
ls.sort()                                                           # highest enddate
le = enddate_high.keys()
le.sort()
start = ls[0]
end = le[-1]
head = []
while start <= end:                                                 # calculate each period between
    head.append(str(start.year) + '.' \
    + str(start.month).zfill(2)	+ ';')                              # the lowest startdate and
    start += relativedelta(months=+1)                               # the highest enddate

head.sort()
headline = (['Document Number;',
             'SoP;',
             'Salesprice;',
             'Startdate;', 
             'Enddate;'
             'Days_Total;'
             'Sales_Price_per_Day;']
              +	head)                                               # Additional fields for headline

headline += '\n'                                                    # linebreak for headline
for xa in headline:	                                            # print header for csv-file
    output.write(xa)                                                # one column for each period

period = head[:]                                                    # copy and save headline

# Create data for each column

input.seek(0)
for line in input:
    row = line.rstrip('\n')	                                    # read csv-file again
    if "Document Number" in row: continue                           # skip if Headline
    field =  row.split(";")                                         # split in fields
    if field[17].lower() != "y": continue                           # skip if not Deferrals
    if field[20].startswith("-"): continue                          # skip if salesprice empty
    salesprice = locale.atof(field[20])                             # convert in  local format
    if salesprice <= 0:	continue                                    # skip if salesprice <= 0

    document_n  = field[0]                                          # read Document Number
    startdate   = ConvDat(field[30])                                # read and convert Startdate
    enddate     = ConvDat(field[31])                                # read and convert Enddate

    defer_time = enddate - startdate                                # calculate contract days
    defer_time_p1 = int(defer_time.days) + 1                        # add one for the first day
    salesprice_day = (salesprice / defer_time_p1)                   # calculate contract amount per day

# clean
    defer_period = {}							
    deferrals = []                                                  
    for xb in period:                                               
        xc = xb[:-1]                                                
        defer_period[xc] = 0
    

# calculate Deferrals for each period
    
    first_period = startdate
    last_period  = enddate
    
    while first_period <= last_period:
        days_in_month = Daysmonth(first_period)                     # Days in a Period
        def_month = salesprice_day * days_in_month                  # Deferrals in Period
        act_per = (Pdate(first_period))                             # actual period	

    	if act_per in defer_period:	                            # enter Deferrals/Month in
            defer_period[act_per] = def_month                       # the right period

    	first_period += relativedelta(months=+1)                    # go to following month
    
# startperiod is probably not a full period	

    days_in_month = Daysmonth(startdate) - startdate.day  
    days_in_month_p1 = int(days_in_month) + 1                       # add one day for the first day
    def_month = salesprice_day * days_in_month_p1
    act_per = (str(startdate.year) + "."
              + str(startdate.month).zfill(2))
    if act_per in defer_period:	
        defer_period[act_per] = def_month

# for endperiod	is probably not a full period

    days_in_month = enddate.day
    def_month = salesprice_day * days_in_month
    act_per = (str(enddate.year) + "."
              + str(enddate.month).zfill(2))
    if act_per in defer_period:
	defer_period[act_per] = def_month
    
# if period is less than one month
    
    p1 = (enddate - startdate).days
    p2 = monthrange (startdate.year, startdate.month)[1]
    if p1 < p2 and startdate.month == enddate.month:
	days_in_month = enddate.day - startdate.day  
    	days_in_month_p1 = int(days_in_month) + 1                       # add one day for the first day
    	def_month = salesprice_day * days_in_month_p1
    	act_per = (str(startdate.year) + "."
              + str(startdate.month).zfill(2))
    	if act_per in defer_period:	
        	defer_period[act_per] = def_month

# sort Deferrals to Month 

    line = defer_period.keys()
    line.sort()
    for xe in line:
        deferrals.append(defer_period[xe])

# convert string for Excel-Import
    string = ([document_n,
                field[10],
                salesprice,
                str(startdate),
                str(enddate),
                defer_time_p1,
                salesprice_day]
                + deferrals)
    string = (str(string).replace(',',';')
                .replace('.',',')
                .rstrip(']')
                .lstrip('['))

# write string in txt-file  

    output.write(string + '\n')
    print document_n


print "ready"
input.close()
output.close()
raw_input()