Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Datetime Import #576

Open
JamesHabben opened this issue Oct 18, 2023 · 50 comments
Open

Datetime Import #576

JamesHabben opened this issue Oct 18, 2023 · 50 comments

Comments

@JamesHabben
Copy link
Collaborator

Saw a commit to change the use of datetime import in I. i had updated that as part of syncing cross versions. A, R, V use datetime.datetime.now() and I is the only one importing at higher level and using datetime.now(). do you want to move the others to this instead?

I:

iLEAPP/scripts/ilapfuncs.py

Line 40 in eb2bfba

now = datetime.now()

A:
https://github.com/abrignoni/ALEAPP/blob/f7243e99c6ccc5ab5148106558461140d7f5dab8/scripts/ilapfuncs.py#L33

R:
https://github.com/abrignoni/RLEAPP/blob/3a62eb369d7203353db4872b6077a6361c07f095/scripts/ilapfuncs.py#L30

V:
https://github.com/abrignoni/VLEAPP/blob/20a066a144c931ccf383a45fb0ce878c9d62ffc6/scripts/ilapfuncs.py#L30
@abrignoni
Copy link
Owner

We have to make sure that the timezone offset functions right beneath the line 40 code block work after any changes to the way the datetime functon is imported. The need is to have access to the datetime and timezone functions within the datetime library.

@JamesHabben
Copy link
Collaborator Author

ah understood. we can sync these up then with the timezone changes you are working on then?

@abrignoni
Copy link
Owner

abrignoni commented Oct 19, 2023 via email •
edited
Loading

@JamesHabben
Copy link
Collaborator Author

end goal, i am thinking, is to make a report with an ability to adjust timezone?

@abrignoni
Copy link
Owner

abrignoni commented Oct 19, 2023 via email

@JamesHabben
Copy link
Collaborator Author

wiat! then whats your goal with the timezone stuff?

@abrignoni
Copy link
Owner

abrignoni commented Oct 19, 2023 via email

@JamesHabben
Copy link
Collaborator Author

oh cool. if every date is timezone aware, then making the report timezone aware would be a great next step. i havent checked out the javascript libs in use just yet, but it should be possible. best case, change a drop down somewhere. worst case, have to click a button after changing it.

@JamesHabben
Copy link
Collaborator Author

I see a handful of modules using this type of thing in the sqlite query. is there a specific purpose to having this in the query, or could we move this into python code to make converting dates much easier for module devs?

DATETIME(CREATIONDATE+978307200,'UNIXEPOCH'),

something like this would allow for the module to take the timestamp right out of sqlite untouched and have a simpler query. plus this would return the date as a date object, which we should keep the date in until its ready to be displayed or printed.

def convert_sqlite_epoch(epoch_date):
    try:
        epoch_start = datetime.datetime(2001, 1, 1)
        date_obj = epoch_start + datetime.timedelta(seconds=epoch_date)
        return date_obj
    except ValueError:
        return "Invalid Epoch Date"

@abrignoni
Copy link
Owner

abrignoni commented Oct 21, 2023 via email

@abrignoni
Copy link
Owner

abrignoni commented Oct 21, 2023 via email

@JamesHabben
Copy link
Collaborator Author

not fully tested, but it should be good to go. not sending PR yet because i want to check it out more. but i think its in good shape. on the case information page, the user can select the timezone and date format to apply to dates that are provided to the report in the correct way. i have done my testing with the apple address book contacts module. it stores the value to local storage, so it will survive between reports and show the same settings for the user. check it out and let me know what you think.

image

image

https://github.com/JamesHabben/iLEAPP/tree/dynamicreporttimestamps

@JamesHabben
Copy link
Collaborator Author

they key is passing in a python datetime object to the report data_list, and the code will dynamically detect that and format it in the table accordingly, including providing an ISO format date string for table sorting since a format like m/d/y would completely break sorting. btw, screenshots show m/d/y, but i am a full fledged yyyy/mm/dd card carrying member.

@abrignoni
Copy link
Owner

abrignoni commented Oct 22, 2023 via email

@JamesHabben
Copy link
Collaborator Author

ran short on time yesterday so my comment was short. heres a better run down of what i changed. happy to discuss and open to changes or adjustments as you see fit.

Sqlite Row Factory & Cursor

this code opens the connection, creates a row factory and returns the cursor to the module. result is more compact code in the module (easier for module dev) and now the columns can be referenced by column name/alias. using the column name will make modules much easier to dev and read over using position reference.

# ilapfuncs.py
def open_sqlite_file_readonly(file_path):  # <-- name might need adjustment
    conn = open_sqlite_db_readonly(file_path)
    conn.row_factory = sqlite3.Row
    return conn.cursor()

one liner open, and reference by name.

# in module
    cursor = open_sqlite_file_readonly(file_found) # <-- one line open
    cursor.execute(
    SELECT 
    ABPerson.ROWID,
    c16Phone,
    FIRST,
    # (...)
        for row in all_rows:
            if row['c16Phone'] is not None: # <-- use column name
                try:
                    numbers = row['c16Phone'].split(" +")

Normalize Sqlite Date

many of the queries have things like datetime(ZADDEDDATE + 978307200, 'unixepoch') in them. created a function to convert that into a datetime object, with option for timezone offset if the module is aware of a time being stored local vs utc.

# ilapfuncs.py
def convert_sqlite_epoch(epoch_date, timezone_offset=0):
    try:
        epoch_start = datetime(2001, 1, 1, tzinfo=timezone.utc)
        date_obj = epoch_start + timedelta(seconds=epoch_date)
        if timezone_offset:
            date_obj = date_obj + timedelta(hours=timezone_offset)
        return date_obj
    except ValueError:
        logfunc(f"Date conversion error")
        return False

so in module, we can simply call the colum without mutating, and convert it outside the query.

# in module
    SELECT 
    # (...)
    DATETIME(CREATIONDATE+978307200,'UNIXEPOCH'), # <-- dont need
    CREATIONDATE, # <-- use this instead
    DATETIME(MODIFICATIONDATE+978307200,'UNIXEPOCH'),
    MODIFICATIONDATE,
   # (...)
            data_list.append((
                convert_sqlite_epoch(row['CREATIONDATE']), # <-- returns date object for later
                row['ROWID'],
                phone_number,
                row['FIRST'],
                row['MIDDLE'],
                row['LAST'],
                row['c17Email'],
                convert_sqlite_epoch(row['CREATIONDATE']),
                row['NAME']
            ))

Value Format Function

added a function to format the table cells for the values. currently has special handling for date objects only, but this can be expanded to other data types. for example, i've used a python lib (i'm sure there is javascript also) in other projects that will pretty print a phone number into the format a region typically uses based on its country code, so we can put special handing to designate fields as phone numbers for that if you want. had to bring the <td> tag into this function to better handle sorting. the jquery grid tables support a data-sort attribute to sort on regardless of how the cell is displaying data. as mentioned above sorting on m/d/y would break chronologically, so this function inserts the ISO format for sorting.

# artifact_report.py
        def format_value(value):
            if isinstance(value, datetime.datetime):
                # Format datetime objects as HTML divs with data-timestamp attributes
                return f'<td data-sort="{value.isoformat()}"><div data-timestamp="{value.isoformat()}"></div></td>'
            elif value in [None, 'N/A']:
                return '<td></td>'
            else:
                return '<td>' + html.escape(str(value)) + '</td>'

used lower in the same file

# artifact_report.py
        if html_escape:
            for row in data_list:
                if html_no_escape:
                    self.report_file.write('<tr>' + ''.join(
                    f'{format_value(x) if h not in html_no_escape else x}' for x, h in zip(row, data_headers)  # <-- here
                ) + '</tr>')
                else:
                    self.report_file.write('<tr>' + ''.join(
                        f'{format_value(x)}' for x in row # <-- here
                    ) + '</tr>'),
        else:
            for row in data_list:
                self.report_file.write('<tr>' + ''.join(
                    f'{format_value(x)}' for x in row # <-- here
                ) + '</tr>')

function expansion

the function currently detects the special handling based on the value type as its passed in. this wont work for phone numbers since they will be strings like any other string, and i dont think testing every string for a starting "+" is either performant or reliable. so i think this may expand into detect a tuple vs non-tuple. we can keep the date object detect as part of the test in case a module provides that outside of a tuple. this could work to allow format control over date or time only values as well.

my thought would be to allow for something like this:

            data_list.append((
                convert_sqlite_epoch(row['CREATIONDATE']), # <-- no tuple, but detect on datetime object
                row['ROWID'],
                (phone_number, 'phonenumber'), # <-- tuple with special handling
                row['FIRST'],
                row['MIDDLE'],
                row['LAST'],
                row['c17Email'],
                (convert_sqlite_epoch(row['CREATIONDATE']), 'datetime'), # <-- tuple with explicit datetime handling
                row['NAME']
            ))

HTML Tagging

with these special handling cases on format, they wrap the provided value with <div> tags to identify their type. it uses <div data-timestamp="{value.isoformat()}"></div> in the case of the current datetime object. then there is a javascript function added to the HTML output file to resolve the value of the attribute to a displayed data inside the data grid cell. for time formatting, i added a library called moment which has a timezone awareness piece as well.

    function updateDates() {
        // Get the currently selected timezone and format
        var savedTimezone = localStorage.getItem('timezone') || 'UTC';
        var savedFormat = localStorage.getItem('format') || 'MMMM Do YYYY, h:mm:ss a';
    
        // Select all divs with a data-timestamp attribute
        const dateDivs = document.querySelectorAll('div[data-timestamp]');
    
        dateDivs.forEach(div => {
            // Get the timestamp from the data attribute
            const timestamp = div.getAttribute('data-timestamp');
    
            // Convert and format the timestamp using Moment.js
            const formattedDate = moment.tz(timestamp, savedTimezone).format(savedFormat);
    
            // Update the content of the div to display the formatted date
            div.textContent = formattedDate;
        });

this same concept can be added for any data type, such as phone numbers above. we can provide an option in the report to display phone numbers in regional formatting or not, and this function can be adjusted to update all <div> with a data-phonenumber attribute.

Report DateTime Settings

i added a couple modals to pop up the selections for timezone selection and date format. placed them in the html_parts.py file to be added to the report. they pop based on some other javascript added to detect the button click. here is the timezone modal as an example.

# modal window for timezone selection
modal_timezone = \
"""
            <!-- TimeZone Modal -->
<div class="modal fade" id="timezoneModal" tabindex="-1" role="dialog" aria-labelledby="timezoneModalLabel" aria-hidden="true">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <h5 class="modal-title" id="timezoneModalLabel">Select Timezone</h5>
        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body">
        <input type="text" id="timezoneSearch" placeholder="Search for a timezone" class="form-control">
        <div id="timezoneList">
          <!-- List of timezones will be populated here -->
        </div>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        <button type="button" class="btn btn-primary" id="saveTimezone">Save changes</button>
      </div>
    </div>
  </div>
</div>
"""

it presents a full list of standardized naming timezones with a search at the top to quickly find the appropriate setting.
image

on save of each modal, the value is stored in local storage of the browser so that should persist even clearing cookies and cache, and will be pulled in anytime a xLEAPP report is opened (i think).

        // Event handler to select a timezone when clicked
        $('#timezoneList').on('click', '.timezone-item', function () {
            var selectedTimezone = $(this).data('timezone');
            $('#timezoneSearch').val(selectedTimezone);
            $('#timezoneModal').modal('hide');
            $('#currentTimezone').text(selectedTimezone);
            localStorage.setItem('timezone', selectedTimezone);
            updateCurrentTimeInfo();
            updateDates();
        });

it also updates the settings card to show the selected values and how it will be displayed
image

@abrignoni
Copy link
Owner

I agree fully with the approach you propose. Here is some historical context on why things look as they do now:

One of the reasons the sqlite rows are called by position is that the column names in the report are almost always different from the column names in the table. The data_headers variable keeps track of the report's column names. I have no issue with folks using the table column names or the query return list positions when appending to data_list.

Regarding timestamps in sqlite they will not always be epoch ones as you know. Since most contributors' queries return a string timestamp in ISO format for time fields my approach has been to turn that string into a UTC datetime object which I offset with a user provided timezone at the start of processing. I agree fully with your timezone offset approach at the report level. If I understand correctly the main point will be making contributors aware that their timestamps (be it of sqlite origin or not) need to be a datetime objects at UTC timezone when appending to data_list. Btw we should not give the users the option to see the times in anything other than ISO format.

Regarding function expansion I am all for it. All these are great improvements, no doubt about it. I don't see the changes having negative impacts anywhere else.

If it goes as planed we should do a full version change from 1.18.7 to 2.0. Same with all the other LEAPPs as it is ported. :-D

@JamesHabben
Copy link
Collaborator Author

dates will be in all kinds of formats, and even sqlite dates could have different epoch points too. that function just helps to clean up the query from those that use the 2001 epoch. if a date has a different epoch, the module dev can make their own function to handle or we can create another function if its common enough.

using this row factory doesnt prevent the module dev from using the positional column reference. it just makes working with the column values easier as reading through the code uses "creationtime" instead of row[1]. it helps to prevent coding mistakes by using 2 instead of 3 when its "first" vs "last" type of thing.

If I understand correctly the main point will be making contributors aware that their timestamps (be it of sqlite origin or not) need to be a datetime objects at UTC timezone when appending to data_list.

yes. this code wont break any of the existing modules that are supplying their date values as strings, but it wont recognize those for the dynamic date. to get the date recognized, the module dev should supply the value in a datetime object at utc. then the javascript will handle the timezone offset in the browser.

Btw we should not give the users the option to see the times in anything other than ISO format.

lol, be nice to your users. let them have cake and eat it.

@JamesHabben
Copy link
Collaborator Author

also got the phone number formatting in

image

@JamesHabben
Copy link
Collaborator Author

dropped an info icon next to the number with more information that can be viewed.

image

@abrignoni
Copy link
Owner

abrignoni commented Oct 24, 2023 via email

@JamesHabben
Copy link
Collaborator Author

let me know what you think in terms of this direction. i dont think it works well with the timezone parameter input, so not sure if you still have some thought on what that might create a result of.

@abrignoni
Copy link
Owner

abrignoni commented Oct 24, 2023 via email

@JamesHabben
Copy link
Collaborator Author

if you are game for merging this dynamic report stuff in, we can just remove the input parameter for now. leaving the other parts of the code wont break anything and they can be adjusted over time as we update artifact modules. i have only added the dynamic fields to the addressbook module so far. the phonenumber pop up is in my branch already. i think i will add another pop up for dates to include a few things like UTC and tz applied value, week number, maybe even a "this was 2 years and 3 months ago" type of thing. anything else you think would be helpful to see in a datetime pop up?

@JamesHabben
Copy link
Collaborator Author

put some more updates into my fork getting ready for the pull request.

  • latest is moving all the reporting html, css, js type stuff into a report_elements folder to separate them from the python code. then refactoring the report construction to copy all files from that folder into the destination _elements folder instead of naming each of the objects in code, it will autodiscover anything in that folder.

  • also put the filename of the module generating each sub-report at the bottom of the <main> section so its easier to track down which python module is responsible for the code. future feature is that we can print a bit more information about that module by reading the data from the artifact structure. it can either go direct, or can be a small bit of text with an icon or something and a click that pops something up with more information about the author, module, description, whatever. was a bit of a selfish thing since trying to figure out which module put in which report section and item was not easy. we can remove it if you dont like it.

current edits are pushed to my github fork if you want to check it out.

@abrignoni
Copy link
Owner

abrignoni commented Oct 26, 2023 via email

@abrignoni
Copy link
Owner

abrignoni commented Oct 26, 2023 via email

@JamesHabben
Copy link
Collaborator Author

i think we can adjust the spec file to do a similar thing. not so familiar with those yet, so doing some research

@JamesHabben
Copy link
Collaborator Author

found a bug in the current spec file while looking, it didnt build as it sits. edited and its now working great. also moved the MDB-Free_4.13.0 folder inside the report_elements folder.

old:

             datas=[('.\\scripts\\logo.jpg', '.\\scripts'),
                    ('.\\scripts\\chats.css', '.\\scripts'),
                    ('.\\scripts\\dashboard.css', '.\\scripts'),
                    ('.\\scripts\\dark-mode.css', '.\\scripts'),
                    ('.\\scripts\\dark-mode-switch.js', '.\\scripts'),
                    ('.\\scripts\\feather.min.js', '.\\scripts'),
                    ('.\\scripts\\MDB-Free_4.13.0', '.\\scripts\\MDB-Free_4.13.0'),
                    ('.\\scripts\\artifacts', '\\scripts\\artifacts')],

-------------------------------------------------^ missing period
new:

             datas=[('.\\scripts\\report_elements', '.\\scripts\\report_elements'),
                    ('.\\scripts\\artifacts', '.\\scripts\\artifacts')],

both spec files adjusted and pushed to my fork

@abrignoni
Copy link
Owner

Some comments:L
Love the display setting options. I wonder if they could be placed in a tab or another location.
The report's home page looks really cluttered.
Dark mode makes the timezones hard to read.
All the reports that had images (like chat ones) are broken. They are tags inside the table. Not sure how the changes are impacting these tags.

Screenshot 2023-10-26 at 7 18 40 PM Screenshot 2023-10-26 at 7 16 58 PM

@JamesHabben
Copy link
Collaborator Author

oh weird. not sure what would be impacting images like that. i dont have an image with viber in it.

i hadnt played with the dark mode yet, so i will have to explore that to see what css needs to get applied to these boxes. we can definitely move the display settings cards to somewhere else. any ideas on where?

@abrignoni
Copy link
Owner

abrignoni commented Oct 26, 2023 via email

@JamesHabben
Copy link
Collaborator Author

fixed dark mode, fixed those image display issues, moved settings to a settings page and put a button at the top for the settings page.

image

@abrignoni
Copy link
Owner

abrignoni commented Oct 27, 2023 via email

@abrignoni
Copy link
Owner

abrignoni commented Oct 28, 2023 •
edited
Loading

The Display Settings page is fantastic. Way better than what I had in mind. Only thing it needs is a way to go back to the report after the selections are made. I like it a lot.

The images are back where they should be. Woot!!!
I can't even with how awesome this is turning out.
Hopefully it is not super hard to port it over to the other LEAPPs.

@JamesHabben
Copy link
Collaborator Author

the back button works to take the user back to the page they were on. otherwise, what do you have in mind to take the user back? the settings are all saved live, so there is no 'save' or 'close' type button.

porting to xLEAPP was on my mind the whole time, and it really shouldnt be very hard at all. i have a few things in mind that will make the sync process between xLEAPP version even easier, but tackle one thing at a time.

@abrignoni
Copy link
Owner

abrignoni commented Oct 29, 2023 via email

@JamesHabben
Copy link
Collaborator Author

JamesHabben commented Oct 30, 2023 •
edited
Loading

i got the side bar back onto the page. was getting an error in the mark_active_item function line i copied from create_index_html because the settings.html file didnt exist, and i excluded too much code.

this now brings the nav items back in without marking it active. pushed to my fork.
active_nav_list_data = nav_list_data + nav_bar_script

and image
image

@abrignoni
Copy link
Owner

I think this looks fantastic. Love the consistency.

@JamesHabben
Copy link
Collaborator Author

got the 'datetime' popover working

image

@abrignoni
Copy link
Owner

abrignoni commented Nov 1, 2023 via email

@JamesHabben
Copy link
Collaborator Author

added a few more but have an empty box. what else you want in there?

image

@abrignoni
Copy link
Owner

abrignoni commented Nov 2, 2023 via email

@JamesHabben
Copy link
Collaborator Author

got some color handler code in too. found a lib that provides color blind simulations.

image

@JamesHabben
Copy link
Collaborator Author

ive updated all the modules that hit in my backup, except the sms.py. that one is a bit scary lol. def more modules to update, but i dont have data to validate. all the updates are pushed to my fork. let me know what you think and how you wanna go about PR.

@abrignoni
Copy link
Owner

abrignoni commented Nov 3, 2023 via email

@abrignoni
Copy link
Owner

Been thinking how we address the TSV generated files regarding timezone. If we move timezone offsets to the html report then the TSVs will have to be UTC since they are generated at the artifact level.

I'm not against it. Just thinking about that as something that will be part of this design approach.

@JamesHabben
Copy link
Collaborator Author

Ya, and the timeline function. I already adjusted both of those to take in UTC, but there could be a parameter to adjust the TSV out out by timezone and make the adjustment on the TSV only.

@JamesHabben
Copy link
Collaborator Author

All the modules updated and pushed to my fork

@JamesHabben
Copy link
Collaborator Author

couple edits pushed. only module left is the SMS stuff. not sure yet how to put this stuff into the threaded representation.

@abrignoni
Copy link
Owner

abrignoni commented Nov 10, 2023 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@JamesHabben @abrignoni