SqlDataset

import csv
import datetime

def main():
    file = open("sample.csv")
    csvreader = csv.reader(file)
	header = next(csvreader)
    rows = []

    shops = {}

    for row in csvreader:
        rows.append(row)
        shop_id = int(row[0])
        if(shop_id not in shops):
            shops[shop_id] = [datetime.datetime(int(row[1][0:4]), int(row[1][5:7]),int(row[1][8:]))]
        else:
            shops[shop_id].append(datetime.datetime(int(row[1][0:4]), int(row[1][5:7]),int(row[1][8:])))


    closed = {}
    opened = {}

    for shop in shops:
        shops[shop].sort()
        i = 0
        opendate = datetime.datetime(2021,1,1)
        while i < len(shops[shop]) - 1:
            if((shops[shop][i+1] - shops[shop][i]).days > 30):
                if(shop not in closed):
                    opened[shop] = [[opendate, shops[shop][i]]]
                    closed[shop] = [[shops[shop][i] + datetime.timedelta(days=1), shops[shop][i+1] - datetime.timedelta(days=1)]]
                    opendate = shops[shop][i+1]
                else:
                    opened[shop].append([opendate, shops[shop][i]])
                    closed[shop].append([shops[shop][i] + datetime.timedelta(days=1), shops[shop][i+1] - datetime.timedelta(days=1)])
                    opendate = shops[shop][i+1]
            i += 1

        if((datetime.datetime(2022,12,31) - shops[shop][i]).days > 30):
            if(shop not in closed):
                opened[shop] = [[opendate, shops[shop][i]]]
                closed[shop] = [[shops[shop][i] + datetime.timedelta(days=1), "NULL"]]
            else:
                opened[shop].append([opendate, shops[shop][i]])
                closed[shop].append([shops[shop][i] + datetime.timedelta(days=1), "NULL"])

    for shop in shops:
        if(shop not in closed):
            opened[shop] =[[datetime.datetime(2021,1,1),"NULL"]]

    for shop in closed:
        if(closed[shop][-1][1]!="NULL"):
            opened[shop].append([closed[shop][-1][1] + datetime.timedelta(days=1),"NULL"])

    with open('SqlDataset.csv','w',newline='') as f:
        writer = csv.writer(f)
        writer.writerow(['shop_id','status','lower_range','upper_range'])
        outputdata = []
        for shop in opened:
            for interval in opened[shop]:
                if(interval[1]!="NULL"):
                    outputdata.append([shop, 'open', datetime.datetime.strftime(interval[0],"%Y-%m-%d"), datetime.datetime.strftime(interval[1],"%Y-%m-%d")])
                else:
                    outputdata.append([shop, 'open', datetime.datetime.strftime(interval[0],"%Y-%m-%d"), "NULL"])

        for shop in closed:
            for interval in closed[shop]:
                if(interval[1]!="NULL"):
                    outputdata.append([shop, 'clsd', datetime.datetime.strftime(interval[0],"%Y-%m-%d"), datetime.datetime.strftime(interval[1],"%Y-%m-%d")])
                else:
                    outputdata.append([shop, 'clsd', datetime.datetime.strftime(interval[0],"%Y-%m-%d"), "NULL"])
        outputdata.sort(key=lambda x:(x[0],x[2]))
        writer.writerows(outputdata)

main()