def get_items_from_file(doc_name, warehouse, posting_date, posting_time):
res_items = []
try:
rows = read_csv_content_from_attached_file(frappe.get_doc("Stock Reconciliation", doc_name))
except:
frappe.throw(_("Please select a valid csv file with data"))
# detect item_code, quantity, description
is_header_detected = False
item_code_idx = 0
quantity_idx = 0
cost_idx = 0
description_idx = 0
for row in rows:
if not is_header_detected:
try:
robust_row = ['' if field is None else field.lower().strip() for field in row]
item_code_idx = map(lambda a: a.startswith('item no') or a.startswith('item code'), robust_row).index(True)
quantity_idx = map(lambda a: a.startswith('quantity'), robust_row).index(True)
cost_idx = map(lambda a: a.startswith('cost'), robust_row).index(True)
description_idx = map(lambda a: a.startswith('description'), robust_row).index(True)
except ValueError:
continue
else:
is_header_detected = True
continue
item_code = row[item_code_idx]
# quantity
quantity = row[quantity_idx]
if isinstance(quantity, basestring):
quantity = quantity.strip().replace(',', '')
quantity = float(quantity) if quantity else None
# cost
cost = row[cost_idx]
if isinstance(cost, basestring):
cost = cost.strip().replace(',', '')
cost = float(cost) if cost else None
description = row[description_idx]
if item_code is None or quantity is None or description is None or cost is None:
continue
item_code = item_code.upper().strip()
list_item = frappe.get_list('Item', fields=['name', 'item_name'], filters={'is_stock_item': 'Yes', 'name': item_code})
if not list_item:
continue
item = list_item[0]
item.item_code = item.name
item.oc_item_name = item.item_name
item.warehouse = warehouse
item.qty = quantity
item.valuation_rate = cost
item.current_qty = quantity
item.current_valuation_rate = cost
del item["name"]
res_items.append(item)
return res_items
def get_prepared_report_result(report, filters, dn=""):
latest_report_data = {}
doc_list = frappe.get_all("Prepared Report",
filters={
"status": "Completed",
"report_name": report.name
})
doc = None
if len(doc_list):
if dn:
# Get specified dn
doc = frappe.get_doc("Prepared Report", dn)
else:
# Get latest
doc = frappe.get_doc("Prepared Report", doc_list[0])
data = read_csv_content_from_attached_file(doc)
if data:
latest_report_data = {
"columns": json.loads(doc.columns) if doc.columns else data[0],
"result": data[1:]
}
latest_report_data.update({"prepared_report": True, "doc": doc})
return latest_report_data
def upload(select_doctype=None, rows=None):
from frappe.utils.csvutils import read_csv_content_from_attached_file
from frappe.model.rename_doc import rename_doc
if not select_doctype:
select_doctype = frappe.form_dict.select_doctype
if not frappe.has_permission(select_doctype, "write"):
raise frappe.PermissionError
if not rows:
rows = read_csv_content_from_attached_file(frappe.get_doc("Rename Tool", "Rename Tool"))
if not rows:
frappe.throw(_("Please select a valid csv file with data"))
max_rows = 500
if len(rows) > max_rows:
frappe.throw(_("Maximum {0} rows allowed").format(max_rows))
rename_log = []
for row in rows:
# if row has some content
if len(row) > 1 and row[0] and row[1]:
try:
if rename_doc(select_doctype, row[0], row[1]):
rename_log.append(_("Successful: ") + row[0] + " -> " + row[1])
frappe.db.commit()
else:
rename_log.append(_("Ignored: ") + row[0] + " -> " + row[1])
except Exception, e:
rename_log.append("<span style='color: RED'>" + \
_("Failed: ") + row[0] + " -> " + row[1] + "</span>")
rename_log.append("<span style='margin-left: 20px;'>" + repr(e) + "</span>")
def upload(select_doctype=None, rows=None):
from frappe.utils.csvutils import read_csv_content_from_attached_file
if not select_doctype:
select_doctype = frappe.form_dict.select_doctype
if not frappe.has_permission(select_doctype, "write"):
raise frappe.PermissionError
rows = read_csv_content_from_attached_file(frappe.get_doc("Rename Tool", "Rename Tool"))
return bulk_rename(select_doctype, rows=rows)
def upload(select_doctype=None, rows=None):
from frappe.utils.csvutils import read_csv_content_from_attached_file
if not select_doctype:
select_doctype = frappe.form_dict.select_doctype
if not frappe.has_permission(select_doctype, "write"):
raise frappe.PermissionError
rows = read_csv_content_from_attached_file(
frappe.get_doc("Rename Tool", "Rename Tool"))
return bulk_rename(select_doctype, rows=rows)
def upload(select_doctype=None, rows=None):
from frappe.utils.csvutils import read_csv_content_from_attached_file
from frappe.model.rename_doc import rename_doc
if not select_doctype:
select_doctype = frappe.form_dict.select_doctype
if not frappe.has_permission(select_doctype, "write"):
raise frappe.PermissionError
if not rows:
rows = read_csv_content_from_attached_file(
frappe.get_doc("Rename Tool", "Rename Tool"))
if not rows:
frappe.throw(_("Please select a valid csv file with data"))
max_rows = 500
if len(rows) > max_rows:
frappe.throw(_("Maximum {0} rows allowed").format(max_rows))
rename_log = []
for row in rows:
# if row has some content
if len(row) > 1 and row[0] and row[1]:
try:
if rename_doc(select_doctype, row[0], row[1]):
rename_log.append(
_("Successful: ") + row[0] + " -> " + row[1])
frappe.db.commit()
else:
rename_log.append(
_("Ignored: ") + row[0] + " -> " + row[1])
except Exception, e:
rename_log.append("<span style='color: RED'>" + \
_("Failed: ") + row[0] + " -> " + row[1] + "</span>")
rename_log.append("<span style='margin-left: 20px;'>" +
repr(e) + "</span>")
def update_inventory(warehouse, item_code_from, update_bin_location,
bin_location_from, update_barcode, barcode_from):
results = {}
res_items = []
try:
rows = read_csv_content_from_attached_file(
frappe.get_doc('Warehouse', warehouse))
except:
frappe.throw(_('Please select a valid csv file with data'))
is_header_detected = False
item_code_idx = 0
bin_location_idx = 0
barcode_idx = 0
for row in rows:
if not is_header_detected:
try:
robust_row = [
'' if field is None else field.lower().strip()
for field in row
]
item_code_idx = map(
lambda a: a.startswith(item_code_from.lower().strip()),
robust_row).index(True)
if update_bin_location:
bin_location_idx = map(
lambda a: a.startswith(bin_location_from.lower().strip(
)), robust_row).index(True)
if update_barcode:
barcode_idx = map(
lambda a: a.startswith(barcode_from.lower().strip()),
robust_row).index(True)
except ValueError:
continue
else:
is_header_detected = True
continue
item_code = row[item_code_idx] or ''
item_code = item_code.upper().strip()
bin_location = row[bin_location_idx] or ''
bin_location = bin_location.strip()
barcode = row[barcode_idx] or ''
barcode = barcode.strip()
status_message = ''
if not item_code or (bin_location in ['-', '', None]
and barcode in ['-', '', None]):
continue
# update Bin with new bin location
if update_bin_location:
db_bin = frappe.db.get('Bin', {
'item_code': item_code,
'warehouse': warehouse
})
if db_bin:
if bin_location in ['-', '', None]:
status_message += '\Bin Location is not invalid.'
else:
frappe.db.set_value('Bin', db_bin.get('name'),
'bin_location', bin_location)
status_message += 'Bin Location is updated.'
else:
status_message += 'Bin was not updated: Bin with Item Code "%s" and Warehouse "%s" not found.' % (
item_code, warehouse)
# update Item with new barcode
if update_barcode:
db_item = frappe.db.get('Item', {'name': item_code})
if db_item:
if barcode in ['-', '', None]:
status_message += '\nBarcode is not invalid.'
else:
frappe.db.set_value('Item', db_item.get('name'), 'barcode',
barcode)
status_message += '\nBarcode is updated.'
else:
status_message += '\nItem was not updated: Item with Item Code "%s" not found.' % (
item_code, )
item = {
'item_code': item_code,
'bin_location': bin_location,
'barcode': barcode,
'status_message': status_message
}
res_items.append(item)
results['items'] = res_items
return results
def pull_from_inventory_spreadsheet(site_name, silent=False):
results = {}
results_list = []
check_count = 0
update_count = 0
add_count = 0
skip_count = 0
success = True
try:
rows = read_csv_content_from_attached_file(frappe.get_doc("Opencart Site", site_name))
except:
frappe.throw(_("Please select a valid csv file with data"))
# detect item_code, quantity, description
is_header_detected = False
item_code_idx = 0
quantity_idx = 0
description_idx = 0
for row in rows:
if not is_header_detected:
try:
robust_row = ['' if field is None else field.lower().strip() for field in row]
item_code_idx = map(lambda a: a.startswith('item no') or a.startswith('item code'), robust_row).index(True)
quantity_idx = map(lambda a: a.startswith('quantity'), robust_row).index(True)
description_idx = map(lambda a: a.startswith('description'), robust_row).index(True)
except ValueError:
continue
else:
is_header_detected = True
continue
item_code = row[item_code_idx]
quantity = row[quantity_idx]
description = row[description_idx]
if item_code is None or quantity is None or description is None:
continue
item_code = item_code.strip().upper()
check_count += 1
site_doc = frappe.get_doc('Opencart Site', site_name)
items_default_warehouse = site_doc.get('items_default_warehouse')
root_item_group = site_doc.get('root_item_group')
if not items_default_warehouse:
sync_info([], 'Please specify a Default Warehouse and proceed.', stop=True, silent=silent)
doc_item = get_item_by_item_code(item_code)
if doc_item:
# update_item(doc_item, oc_product)
update_count += 1
extras = (1, 'updated', 'Updated')
results_list.append((doc_item.get('name'),
doc_item.get('item_group'),
doc_item.get('oc_product_id'),
doc_item.get_formatted('oc_last_sync_from'),
doc_item.get('modified')) + extras)
else:
# creating new Item
params = {
'doctype': 'Item',
'item_group': root_item_group,
'item_code': item_code,
'is_group': 'No',
'default_warehouse': items_default_warehouse,
'item_name': description,
'description': '',
'show_in_website': 0,
'oc_is_updating': 1,
'oc_site': site_name,
'oc_product_id': '',
'oc_manufacturer_id': '',
'oc_tax_class_id': '',
'oc_stock_status': '',
'oc_model': item_code,
'oc_sku': item_code,
# 'oc_quantity': quantity,
'oc_status': 0,
'oc_meta_title': '',
'oc_meta_keyword': '',
'oc_meta_description': '',
'price': '',
'oc_sync_from': False,
'oc_last_sync_from': datetime.now(),
'oc_sync_to': False,
'oc_last_sync_to': datetime.now(),
}
doc_item = frappe.get_doc(params)
doc_item.insert(ignore_permissions=True)
add_count += 1
extras = (1, 'added', 'Added')
results_list.append((doc_item.get('name'),
root_item_group,
doc_item.get('oc_product_id'),
doc_item.get_formatted('oc_last_sync_from'),
doc_item.get('modified')) + extras)
results = {
'check_count': check_count,
'add_count': add_count,
'update_count': update_count,
'skip_count': skip_count,
'results': results_list,
'success': success,
}
return results
def update_inventory(warehouse, item_code_from, update_bin_location, bin_location_from, update_barcode, barcode_from):
results = {}
res_items = []
try:
rows = read_csv_content_from_attached_file(frappe.get_doc('Warehouse', warehouse))
except:
frappe.throw(_('Please select a valid csv file with data'))
is_header_detected = False
item_code_idx = 0
bin_location_idx = 0
barcode_idx = 0
for row in rows:
if not is_header_detected:
try:
robust_row = ['' if field is None else field.lower().strip() for field in row]
item_code_idx = map(lambda a: a.startswith(item_code_from.lower().strip()), robust_row).index(True)
if update_bin_location:
bin_location_idx = map(lambda a: a.startswith(bin_location_from.lower().strip()), robust_row).index(True)
if update_barcode:
barcode_idx = map(lambda a: a.startswith(barcode_from.lower().strip()), robust_row).index(True)
except ValueError:
continue
else:
is_header_detected = True
continue
item_code = row[item_code_idx] or ''
item_code = item_code.upper().strip()
bin_location = row[bin_location_idx] or ''
bin_location = bin_location.strip()
barcode = row[barcode_idx] or ''
barcode = barcode.strip()
status_message = ''
if not item_code or (bin_location in ['-', '', None] and barcode in ['-', '', None]):
continue
# update Bin with new bin location
if update_bin_location:
db_bin = frappe.db.get('Bin', {'item_code': item_code, 'warehouse': warehouse})
if db_bin:
if bin_location in ['-', '', None]:
status_message += '\Bin Location is not invalid.'
else:
frappe.db.set_value('Bin', db_bin.get('name'), 'bin_location', bin_location)
status_message += 'Bin Location is updated.'
else:
status_message += 'Bin was not updated: Bin with Item Code "%s" and Warehouse "%s" not found.' % (item_code, warehouse)
# update Item with new barcode
if update_barcode:
db_item = frappe.db.get('Item', {'name': item_code})
if db_item:
if barcode in ['-', '', None]:
status_message += '\nBarcode is not invalid.'
else:
frappe.db.set_value('Item', db_item.get('name'), 'barcode', barcode)
status_message += '\nBarcode is updated.'
else:
status_message += '\nItem was not updated: Item with Item Code "%s" not found.' % (item_code,)
item = {'item_code': item_code, 'bin_location': bin_location, 'barcode': barcode, 'status_message': status_message}
res_items.append(item)
results['items'] = res_items
return results
def get_report_attachment_data(dn):
doc = frappe.get_doc("Prepared Report", dn)
data = read_csv_content_from_attached_file(doc)
return {'columns': data[0], 'result': data[1:]}
def get_items_from_file(doc_name, warehouse, posting_date, posting_time):
res_items = []
try:
rows = read_csv_content_from_attached_file(
frappe.get_doc("Stock Reconciliation", doc_name))
except:
frappe.throw(_("Please select a valid csv file with data"))
# detect item_code, quantity, description
is_header_detected = False
item_code_idx = 0
quantity_idx = 0
cost_idx = 0
description_idx = 0
for row in rows:
if not is_header_detected:
try:
robust_row = [
'' if field is None else field.lower().strip()
for field in row
]
item_code_idx = map(
lambda a: a.startswith('item no') or a.startswith(
'item code'), robust_row).index(True)
quantity_idx = map(lambda a: a.startswith('quantity'),
robust_row).index(True)
cost_idx = map(lambda a: a.startswith('cost'),
robust_row).index(True)
description_idx = map(lambda a: a.startswith('description'),
robust_row).index(True)
except ValueError:
continue
else:
is_header_detected = True
continue
item_code = row[item_code_idx]
# quantity
quantity = row[quantity_idx]
if isinstance(quantity, basestring):
quantity = quantity.strip().replace(',', '')
quantity = float(quantity) if quantity else None
# cost
cost = row[cost_idx]
if isinstance(cost, basestring):
cost = cost.strip().replace(',', '')
cost = float(cost) if cost else None
description = row[description_idx]
if item_code is None or quantity is None or description is None or cost is None:
continue
item_code = item_code.upper().strip()
list_item = frappe.get_list('Item',
fields=['name', 'item_name'],
filters={
'is_stock_item': 'Yes',
'name': item_code
})
if not list_item:
continue
item = list_item[0]
item.item_code = item.name
item.oc_item_name = item.item_name
item.warehouse = warehouse
item.qty = quantity
item.valuation_rate = cost
item.current_qty = quantity
item.current_valuation_rate = cost
del item["name"]
res_items.append(item)
return res_items
