#!/bin/env python

import pymssql
from datetime import datetime, timedelta

conn = pymssql.connect(host = '10.1.1.14',
                       user = 'test',
                       password = 'password',
                       database = 'PDPStdStorage')

cursor = conn.cursor()

#today = datetime.now()
#today = today.replace(hour = 0, minute = 0, second = 0, microsecond = 0)
#print(today.strftime("%d.%m.%Y"))

day = datetime.strptime("20150501", "%Y%m%d")
#print(day.strftime("%d.%m.%Y"))

cursor.execute("""
SELECT
    [pat].[fam_18565] AS [Фамилия],
    [pat].[im_53316] AS [Имя],
    [pat].[ot_48206] AS [Отчество],
    [pat].[birthday_38523] AS [Родился],
    (select name_38263 from PDPStdStorage.dbo.t_sextype_38430 where code_51254 = [pat].[sex_40994]) AS [Пол],
    (select name_48562 from PDPStdStorage.dbo.t_socialstatus_09192 where code_25747 = [pat].[socialstatus_59270]) AS [Соц статус],
    (select name_51559 from PDPStdStorage.dbo.t_invgroup_33610 where code_50623 = [pat].[invgroup_59187]) AS [Инвалидность],
    [pat].[snils_34985] AS [СНИЛС],
    [pat].[homephone_02050] AS [дом. Телефон],
    [pat].[workphone_39150] AS [раб. Телефон],
    [pat].[employmentplace_12520] AS [Место работы],
    [pat].[profession_56032] AS [Профессия],
    [pat].[position_61591] AS [Должность],
    [pat].[created_by] AS [created_by],
    [pat].[updatedate_30831] AS [UpdateDate],
    [pat].[registrationdate_10831] AS [RegistrationDate]
FROM
    PDPStdStorage.dbo.t_patient_10905 AS [pat] LEFT OUTER JOIN PDPStdStorage.dbo.t_patient_59944 AS [op] ON (([pat].[uid] = [op].[patientuid_48245])) AND ([op].version_end = 9223372036854775807) LEFT OUTER JOIN PDPStdStorage.dbo.t_erzvector_05863 AS [v] ON (([v].[code_11880] = [op].[erzvector_07895]))
WHERE
    [pat].version_end = 9223372036854775807 AND [pat].[registrationdate_10831] >= """ + "'" + day.strftime("%Y%m%d") + "'" + """
ORDER BY
    RegistrationDate;
""")

class cell:
    day = datetime.now()
    counter = 0
    def chDay(self, day):
        self.day = day
    def chCounter(self, counter):
        self.counter = counter

my_list = []
prevDay = day - timedelta(days = 1)

row = cursor.fetchone()
while row:
    day = datetime.strptime(str(row[15]), "%Y-%m-%d %H:%M:%S")
    day = day.replace(hour = 0, minute = 0, second = 0, microsecond = 0)
    deltaDays = (day-prevDay).days
    if day != prevDay:
        if deltaDays > 1:
            while deltaDays > 1:
                deltaDays -= 1
                new_cell = cell()
                new_cell.chDay(day - timedelta(days=deltaDays))
                my_list.append(new_cell)
        new_cell = cell()
        new_cell.chDay(day)
        new_cell.chCounter(1)
        my_list.append(new_cell)
    else:
        my_list[-1].chCounter(my_list[-1].counter + 1)
    prevDay = day
    row = cursor.fetchone()

conn.close()

i = iter(my_list)
for c in i:
    if c.day.weekday() > 4:
        continue
    print('{0:2} {1:20s}'.format(c.counter, c.day.strftime("%d.%m.%Y")))