def stock_report(): """Ask user for report date range then create report on post""" setExits() g.title = "Inventory Stock Report" start_date = None end_date = None warehouses = Warehouse(g.db).select() if request.form: start_date = getDatetimeFromString(request.form.get( "start_date", None)) end_date = getDatetimeFromString(request.form.get("end_date", None)) warehouse_id = request.form.get('warehouse_id', '-1') if start_date and end_date and start_date < end_date: return stock_on_hand_report(start_date, end_date, warehouse_id) else: start_date = request.form['start_date'] end_date = request.form['end_date'] flash("Those don't look like valid dates... Try 'YYYY-MM-DD'") ## else send form page if not start_date: start_date = local_datetime_now().replace(month=1, day=1) if not end_date: end_date = local_datetime_now().replace(month=12, day=31) return render_template('reports/item_report_input.html', start_date=start_date, end_date=end_date, warehouses=warehouses)
def set_dates(self, start_date, end_date): """Set the start and end dates if none provided""" if start_date is None: start_date = local_datetime_now() - timedelta( days=365 * 18) # a long time ago if end_date is None: end_date = local_datetime_now() + timedelta(days=365) #Next year return start_date, end_date
def test_local_datetime_now(): site_config = get_site_config() #site_config['TIME_ZONE'] = 'US/Pacific' now = datetime.now() local_now = dates.local_datetime_now() assert now.day == local_now.day assert now.hour == local_now.hour assert now.month == local_now.month assert local_now.tzinfo is not None assert local_now.tzinfo.zone == site_config["TIME_ZONE"] # spcecify a time zone zone = "US/Eastern" east_now = dates.local_datetime_now(zone) assert east_now.tzinfo is not None assert east_now.tzinfo.zone == zone
def do_backups(source_file_path, **kwargs): """ Make a series of backups of the sqlite3 database file **params**: * **source_file_path**: The path to database file to backup **kwargs**: * **exit_after**: An integer to limit howmay times the loop will run. It does not mean that there will be that many backups made. Just that many tries. If exit_after is less than 0, run forever. * The remainting kwargs are passed to the SqliteBackup constructor """ from app import app from shotglass2.takeabeltof.mailer import email_admin exit_after = int(kwargs.pop('exit_after', -1)) bac = SqliteBackup(source_file_path, **kwargs) time.sleep(2) loop_counter = 0 while not bac.fatal_error and (exit_after < loop_counter): loop_counter += 1 bac.backup() if app.config['TESTING']: return bac if bac.fatal_error: mes = "[{}] -- Backup error : {}, code: {}".format( local_datetime_now(), bac.result, bac.result_code) # log the error app.logger.error(mes) # send for the calvery email_admin("Fatal Backup Error Occurred", mes) break else: app.logger.info("[{}] -- Backup Result: {}, code: {}".format( local_datetime_now(), bac.result, bac.result_code)) time.sleep(30 * 60) #sleep for half an hour
def save(self, rec, **kwargs): #populate the created field if null if rec.created == None: if rec.last_access is not None: rec.created = rec.last_access else: rec.created = local_datetime_now() return super().save(rec, **kwargs)
def set_defaults(self,rec): """When creating a new record, set the defaults for this table. If the column is type datetime or date and the default value is 'now', insert the current date or datetime """ if rec.id == None and len(self.defaults) > 0: # row_dict = rec._asdict() for key, value in self.defaults.items(): if key in rec and rec.get(key) == None: if value == 'now': col_type = self.get_column_type(key) if col_type.upper() == 'DATE': value = local_datetime_now().date() if col_type.upper() == 'DATETIME': value = local_datetime_now() setattr(rec, key, value)
def update_last_access(self, user_id, no_commit=False): """Update the 'last_access' field with the current datetime. Default is for record to be committed""" if type(user_id) is int: self.db.execute('update user set last_access = ? where id = ?', ( local_datetime_now(), user_id, )) if not no_commit: self.db.commit()
def validate_form(rec): valid_form = True datestring = request.form.get('created', '').strip() if datestring == '': valid_form = False flash('Date may not be empty') else: createdDate = getDatetimeFromString(datestring) if createdDate is None: flash('Date is not in a known format ("mm/dd/yy")') valid_form = False elif createdDate > local_datetime_now(): flash("The date may not be in the future") valid_form = False if valid_form: rec.created = createdDate # Value must be a number try: rec.value = float(request.form.get('value', 0)) except ValueError as e: flash('Could not convert Value {} to a number'.format( request.form.get('value', ""))) valid_form = False # Must be attached to an item itemID = cleanRecordID(request.form.get('item_id', 0)) if not itemID or itemID < 0: flash("You must select an item to use with this transaction") valid_form = False #Try to coerse qty to a number rec.qty = request.form.get('qty', '').strip() if rec.qty == '': flash('Quantity is required') valid_form = False if not Warehouse(g.db).get(request.form.get('warehouse_id', -1)): flash("You must select a warehouse") valid_form = False try: rec.qty = float(rec.qty) if rec.qty == 0: flash('Quantity may not be 0') valid_form = False #truncate qty if int if rec.qty - int(rec.qty) == 0: rec.qty = int(rec.qty) except ValueError as e: flash('Could not convert Qty {} to a number'.format(rec.qty)) valid_form = False return valid_form
def edit(id=None): setExits() #import pdb;pdb.set_trace() transaction = Transaction(g.db) if request.form: id = request.form.get('id', None) id = cleanRecordID(id) items = Item(g.db).select() current_item = None if id < 0: flash("Invalid Record ID") return redirect(g.listURL) if id >= 0 and not request.form: if id == 0: rec = transaction.new() rec.created = local_datetime_now() if 'last_trx' in session: transaction.update(rec, session['last_trx']) else: rec = transaction.get(id) if not rec: flash('Record not Found') return redirect(g.listURL) else: #Get the item if there is one if rec.item_id != 0: current_item = Item(g.db).get(rec.item_id) elif request.form: current_item = Item(g.db).get( cleanRecordID(request.form.get('item_id', "0"))) if id == 0: rec = transaction.new() else: rec = transaction.get(id) if not rec: flash('Record not found when trying to save') return redirect(g.listURL) transaction.update(rec, request.form) error_list = [] if save_record(rec, error_list): return redirect(g.listURL) else: for err in error_list: flash(err) return redirect(g.listURL) return render_template('trx_edit.html', rec=rec, current_item=current_item, items=items)
def test_make_tz_aware(): # make_tz_aware(the_datetime,time_zone=None) site_config = get_site_config() zone = site_config["TIME_ZONE"] aware = dates.make_tz_aware(datetime.now(), zone) now = dates.local_datetime_now() assert aware.tzinfo.zone == zone aware = dates.make_tz_aware(datetime.now()) assert aware.tzinfo.zone == site_config["TIME_ZONE"]
def edit_from_list(id=None, item_id=None): """Handle creation of transaction from the Item record form""" setExits() #import pdb;pdb.set_trace() item_id = cleanRecordID(item_id) item_rec = None rec = None warehouses = Warehouse(g.db).select() trx_types = get_site_config().get('trx_types', [ 'Add', 'Remove', ]) transaction = Transaction(g.db) trx_id = cleanRecordID(id) if trx_id > 0: rec = transaction.get(trx_id) if rec: item_id = rec.item_id else: rec = transaction.new() rec.created = local_datetime_now() if 'last_trx' in session: transaction.update(rec, session['last_trx']) # Handle Response? if request.form: #import pdb;pdb.set_trace() error_list = [] transaction.update(rec, request.form) if save_record(rec, error_list): return "success" # the success function looks for this... else: pass if item_id > 0: item_rec = Item(g.db).get(item_id) if not item_rec: flash("This is not a valid item id") return "failure: This is not a valid item id." else: rec.item_id = item_id return render_template('trx_edit_from_list.html', rec=rec, current_item=item_rec, warehouses=warehouses, trx_types=trx_types)
def start_backup_thread(source_file_path, **kwargs): """ Create a thread to run do_backups in """ from app import app # set daemon to True so the thread will terminate when the originating thread (app) teminates backup_thread = threading.Thread(target=do_backups, args=(source_file_path, ), kwargs=kwargs, name='backup_thread', daemon=True) backup_thread.start() app.logger.info("[{}] -- Backups started in a new thread".format( local_datetime_now()))
def stock_on_hand(self, id, end_date=None, **kwargs): """Return the quantity in inventory Optional date range may be porvided. if "warehouse_id" is in kwargs, limit search to that warehouse """ #import pdb;pdb.set_trace() warehouse_where = self._get_warehouse_where(**kwargs) id = cleanRecordID(id) if end_date is None: end_date = local_datetime_now() sql = """select COALESCE(sum(qty), 0) as qty from trx where item_id = {} and date(created) <= date("{}") {} """.format(id, end_date, warehouse_where) rec = self.db.execute(sql).fetchone() return self.handle_rec_value(rec, 'qty')
def edit_from_list(id=None, item_id=None): """Handle creation of transfer from the Item record form It's not practical to edit existing transfers, so delete the offending transfer record then recreate it instead. """ setExits() #import pdb;pdb.set_trace() rec = None item_rec = None tran_id = cleanRecordID(id) item_id = cleanRecordID(item_id) transfer = Transfer(g.db) if tran_id > 0: sql = get_transfer_select(where="transfer.id = {}".format(tran_id)) rec = transfer.query(sql) if rec: rec = rec[0] item_id = rec.item_id # warehouse_in_id = rec.warehouse_in_id # warehouse_out_id = rec.warehouse_out_id else: rec = transfer.new() rec.transfer_date = local_datetime_now() if 'last_transfer' in session: transfer.update(rec, session['last_transfer']) if item_id > 0: item_rec = Item(g.db).get(item_id) if item_rec: rec.item_id = item_id else: flash("This is not a valid item id") return "failure: This is not a valid item id." warehouses = get_warehouse_dropdown(item_id) warehouse_in_id = cleanRecordID( request.form.get('warehouse_in_id', rec._asdict().get('warehouse_in_id', 0))) warehouse_out_id = cleanRecordID( request.form.get('warehouse_out_id', rec._asdict().get('warehouse_out_id', 0))) # Handle Response? if request.form: #import pdb;pdb.set_trace() if save_record(rec): return "success" # the success function looks for this... else: pass return render_template( 'transfer_edit_from_list.html', rec=rec, warehouses=warehouses, item=item_rec, warehouse_in_id=warehouse_in_id, warehouse_out_id=warehouse_out_id, )
def _now(self): if has_local_date: return local_datetime_now() else: return datetime.datetime.now()
def backup(self): """Create timestamped database copy""" try: # clear any previous errors self._set_result(0) # the data file exists if not os.path.isfile(self.database_path): self._set_result(10) mes = "The source file '{}' was not found.".format( self.database_path) raise self.BackupFatalError(mes, self.result_code) if not os.path.isdir(self.backup_dir): #print('Creating Backup Directory') try: os.makedirs(self.backup_dir) except Exception as e: self._set_result(11) mes = "Unable to access the backup diretory at '{}'.\r\rSystem error: {}".format( self.backup_dir, str(e)) raise self.BackupFatalError(mes, self.result_code) # test to see if it's time for a backup # do this before checking for data change if not self._backup_time(): self._set_result(1) # too soon raise self.BackupSoftError(self.result, self.result_code) # get the hash from the last backup if not self.source_changed(): self._set_result(2) #not changed raise self.BackupSoftError(self.result, self.result_code) # The backup name will be <original file name>-<date and time>.sqlite backup_target = os.path.join( self.backup_dir, os.path.basename(self.database_path) + local_datetime_now().strftime("-%Y-%m-%d-%H-%M") + '.sqlite') # let's do it! try: connection = sqlite3.connect(self.database_path) cursor = connection.cursor() # Lock database before making a backup cursor.execute('begin immediate') # Make new backup file shutil.copyfile(self.database_path, backup_target) # print ("\nCreating {0}").format(os.path.basename(backup_file)) # Unlock database connection.rollback() # rollup # self._rollup() # purge old self._purge() except Exception as e: self._set_result(12) self.result += " at {} Err: {}".format(self.backup_dir, str(e)) raise e except self.BackupSoftError: # not reallly an error. there is nothing to backup # The function that raised this error should have set self.result_code if self.result_code == 0: self._set_result(3) pass except self.BackupFatalError as e: # Something auful happened if not self.fatal_error: self._set_result(13) self.result = printException(str(e), err=e) except Exception as e: # some unexpected exception... self._set_result(20) mes = "An unexpected error occurred during backup" self.result += '\r\r' + printException(mes, err=e) return
def test_date_to_string(): assert len(dates.date_to_string(dates.local_datetime_now(), "%Y-%m-%d")) == 10 assert len(dates.date_to_string("11/15/18", "%Y-%m-%d")) == 10
def validate_form(rec): valid_form = True #Try to coerse qty to a number if request.form['qty'].strip() == '': flash('Quantity is required') valid_form = False else: try: rec.qty = float(rec.qty) if rec.qty <= 0: flash('Quantity must be greater than 0') valid_form = False #truncate qty if int elif rec.qty - int(rec.qty) == 0: rec.qty = int(rec.qty) except ValueError as e: flash('Quantity must be a number') valid_form = False # Must be attached to an item itemID = cleanRecordID(request.form.get('item_id', 0)) if itemID <= 0: flash("You must select an item to use with this transfer") valid_form = False #Must not be more than stock on hand elif rec.qty and type(rec.qty) != str: QOH = Item(g.db).stock_on_hand( itemID, warehouse_id=request.form.get("warehouse_out_id")) if rec.qty > QOH: flash("You may not transfer more than the quantity on hand ({})". format(QOH)) valid_form = False if not Warehouse(g.db).get( cleanRecordID(request.form.get('warehouse_out_id'))): flash("You must select a transfer out warehouse") valid_form = False if not Warehouse(g.db).get( cleanRecordID(request.form.get('warehouse_in_id'))): flash("You must select a transfer in warehouse") valid_form = False # test for valid date test_date = rec.transfer_date if isinstance(test_date, str): test_date = getDatetimeFromString(rec.transfer_date) if not test_date: flash("There must be transfer date") valid_form = False else: rec.transfer_date = test_date if test_date > local_datetime_now(): flash("Transfer date may not be in the future") valid_form = False return valid_form
def test_prefs(): from shotglass2.users.models import Pref #db = get_test_db() assert Pref(db).get(0) == None assert Pref(db).get("this") == None rec = Pref(db).new() rec.name = "Testing" rec.value = "A test value" recID = Pref(db).save(rec) rec = Pref(db).get(recID) assert rec.id == recID assert rec.name == 'Testing' assert rec.value == "A test value" rec = Pref(db).get('Testing') assert rec.name == 'Testing' assert rec.value == "A test value" # get is now case in-sensitive rec = Pref(db).get('testing') assert rec.name == 'Testing' assert rec.value == "A test value" #Modify the record rec.name = "New Test" Pref(db).save(rec) rec = Pref(db).get(rec.id) assert rec.name == "New Test" db.rollback() # test the default setting pref_name = "A new pref" default_value = "A Default value" rec = Pref(db).get(pref_name) assert rec == None # create a new record with default values rec = Pref(db).get(pref_name, default=default_value) assert rec != None assert rec.name == pref_name assert rec.value == default_value assert rec.user_name == None # create another except has a user name rec = Pref(db).get(pref_name, user_name='test', default="new value") assert rec != None assert rec.name == pref_name assert rec.value == 'new value' assert rec.user_name == 'test' # get the generic record rec = Pref(db).get(pref_name) assert rec != None assert rec.name == pref_name assert rec.value == default_value # get the user specific record. Providing a default should not change the record rec = Pref(db).get(pref_name, user_name='test', default="someother value") assert rec != None assert rec.name == pref_name assert rec.value == 'new value' assert rec.user_name == 'test' # this should have no effect because get with default does a commit db.rollback() rec = Pref(db).get(pref_name) assert rec != None assert rec.name == pref_name assert rec.value == default_value #new pref was committed, so delete it assert Pref(db).delete(rec.id) == True db.commit() #Test that it's really gone rec = Pref(db).get(pref_name) assert rec == None # make with all values temp_date = local_datetime_now() pref = Pref(db) rec = pref.get("sample1", default="me", description="test me", user_name='willie', expires=temp_date) assert rec != None assert rec.name == "sample1" assert rec.value == "me" assert rec.description == "test me" assert rec.user_name == "willie" assert getDatetimeFromString(rec.expires).date() == temp_date.date()
def export(self, **kwargs): """Export the current record selection as .csv file""" # import pdb;pdb.set_trace() # provide for case where recs are set extenally if not self.recs: self.select_recs(**kwargs) if self.recs: if self.export_file_name: filename = self.export_file_name else: filename = "{table_name}_report_{datetime}.csv".format( table_name=self.table.display_name, datetime=date_to_string(local_datetime_now(), 'iso_datetime'), ).replace(' ', '_').lower() if not self.export_fields: # include all fields by default self.export_fields = self._set_default_list_fields( include_all=True).copy() self.set_list_fields(self.export_fields) if self.export_template: result = render_template(self.export_template, data=self) else: # add a descriptive title row if self.export_title: result = self.export_title.strip() + '\n' else: result = "Export of table {} as of {}\n".format( self.table.table_name, excel_date_and_time_string(local_datetime_now())) result += ','.join([x['label'] for x in self.export_fields]) + '\n' for rec in self.recs: rec_row = [] for field in self.export_fields: data = rec.__getattribute__(field['name']) if field['type'].upper() == "DATE": data = local_date_string(data) elif field['type'].upper() == "DATETIME": data = excel_date_and_time_string(data) else: # just text data = str(data).strip() # replace double quotes with double-double quotes data = data.replace( '"', '""') #double up on double quotes if "," in data: # if any commas, wrap in quotes data = '"' + data + '"' #replace returns data = data.replace('\r\n', ' -crnl- ') data = data.replace('\n', ' -nl- ') data = data.replace('\r', ' -rtn- ') rec_row.append(data) result += ','.join([str(x) for x in rec_row]) + '\n' return DataStreamer(result, filename, 'text/csv').send() self.result_text = "No records selected" self.success = False flash(self.result_text) return self.list(**kwargs)
def stock_on_hand_report(start_date=None, end_date=None, warehouse=-1): view = TableView(Item, g.db) view.export_fields = [ { 'name': 'name', }, { 'name': 'uom', }, { 'name': 'category', }, { 'name': 'warehouse', }, { 'name': 'lifo_cost', }, { 'name': 'prev_soh', }, { 'name': 'added', }, { 'name': 'xfer_out', }, { 'name': 'used', }, { 'name': 'soh', 'label': 'On Hand' }, ] # import pdb;pdb.set_trace() view.path = [ 'export', ] warehouse_name = 'All Warehouse' where = '1' warehouse_id = cleanRecordID(warehouse) if warehouse_id > 0: where = " trx.warehouse_id = {}".format(warehouse_id) rec = Warehouse(g.db).get(warehouse_id) warehouse_name = rec.name if rec else "Warehouse Unknown" # Default to reporting only for this year if start_date is None: start_date = local_datetime_now().replace(month=1, day=1) if end_date is None: end_date = local_datetime_now().replace(year=local_datetime_now().year, month=12, day=31) view.export_title = "{} Stock Report for period {} thru {} generated on {}".format( warehouse_name.title(), local_date_string(start_date), local_date_string(end_date), excel_date_and_time_string(local_datetime_now()), ) view.export_file_name = "{} stock report {}.csv".format( warehouse_name, str(local_datetime_now())[:19], ).replace(' ', '_').lower() view.sql = """SELECT item.*, cats.name as category, '{warehouse_name}' as warehouse, COALESCE ( (select trx.value from trx where trx.item_id = item.id and date(trx.created, 'localtime') <= date('{end_date}','localtime') and trx.value > 0 and {where} order by trx.created desc limit 1 ) ,0) as lifo_cost, COALESCE ( (select sum(trx.qty) from trx where trx.item_id = item.id and date(trx.created, 'localtime') < date('{start_date}','localtime') and {where} ) ,0) as prev_soh, COALESCE ( (select sum(trx.qty) from trx where trx.item_id = item.id and date(trx.created, 'localtime') >= date('{start_date}','localtime') and date(trx.created, 'localtime') <= date('{end_date}','localtime') and {where} and lower(trx.trx_type) = 'transfer out' ) ,0) as xfer_out, COALESCE ( (select sum(trx.qty) from trx where trx.item_id = item.id and date(trx.created, 'localtime') >= date('{start_date}','localtime') and date(trx.created, 'localtime') <= date('{end_date}','localtime') and {where} and trx.qty > 0 ) ,0) as added, COALESCE ( (select sum(trx.qty) from trx where trx.item_id = item.id and date(trx.created, 'localtime') >= date('{start_date}','localtime') and date(trx.created, 'localtime') <= date('{end_date}','localtime') and {where} and lower(trx.trx_type) = 'remove' ) ,0) as used, COALESCE ( (select sum(trx.qty) from trx where trx.item_id = item.id and date(trx.created, 'localtime') <= date('{end_date}','localtime') and {where} ) ,0) as soh from item left join trx on trx.item_id = item.id left join warehouse as wares on wares.id = trx.warehouse_id left join category as cats on cats.id = item.cat_id where {where} group by item.id order by lower(category), lower(item.name) """.format( where=where, start_date=iso_date_string(start_date), end_date=iso_date_string(end_date), warehouse_name=warehouse_name, ) return view.dispatch_request()
def time_lapse_map(): """ Display an automated map of bike sightings over time @@@ This is left over from the JumpStat maps but it might be a place to start in the future... """ setExits() days = 1 start_date = local_datetime_now() + timedelta( days=-1) # Always starts at midnight, yesterday start_date = start_date.replace(hour=0, minute=0, second=0, microsecond=0) end_date = start_date + timedelta(days=days, seconds=-1) frame_duration = 10 * 60 # this many seconds of real time elapse between each frame seconds_per_frame = 1 # display each frame for this many seconds sql = """select id, lng, lat, sighted, retrieved from sighting where retrieved >= '{start_date}' and sighted <= '{end_date}' order by sighted """.format(start_date=start_date.isoformat(sep=' '), end_date=end_date.isoformat(sep=' ')) recs = g.db.execute(sql).fetchall() marker_data = {"markers": []} marker_data["zoomToFit"] = False # can/t zoom if there are no markers. if recs: """ The Marker is a list of lists containing: lng, lat, display start seconds, display end seconds At play time in javascript, every frame_duration seconds loop through Markers: if display start seconds <= frame start time and display end seconds >= frame end time, set the marker opacity to 1 else set opacity to 0 """ total_seconds = int(round((end_date - start_date).total_seconds(), 0)) marker_data["zoomToFit"] = True marker_data['total_seconds'] = total_seconds marker_data['frame_duration'] = frame_duration marker_data['seconds_per_frame'] = seconds_per_frame #import pdb;pdb.set_trace() for rec in recs: sighted_dt = getDatetimeFromString(rec['sighted']) if sighted_dt.day == 17: #import pdb;pdb.set_trace() pass #print('sighted_dt: {}'.format(sighted_dt)) retrieved_dt = getDatetimeFromString(rec['retrieved']) #print('retrieved_dt: {}'.format(retrieved_dt)) marker_data["markers"].append([ round(rec['lng'], 5), round(rec['lat'], 5), int(round((sighted_dt - start_date).total_seconds(), 0)), int(round((retrieved_dt - start_date).total_seconds(), 0)), ]) return render_template('JSONmap.html', marker_data=marker_data, start_date=start_date)