def handle_uploaded_file(request, permanences, file_to_import): error = False error_msg = None wb = load_workbook(file_to_import) # dict for performance optimisation purpose : read the DB only once customer_buyinggroup_id, customer_2_id_dict = get_customer_2_id_dict() producer_buyinggroup_id, producer_2_id_dict = get_producer_2_id_dict() if customer_buyinggroup_id is None: error = True error_msg = _("At least one customer must represent the buying group.") else: if producer_buyinggroup_id is None: error = True error_msg = _("At least one producer must represent the buying group.") if not error: permanence = permanences.first() if permanence is not None: if permanence.status == PERMANENCE_SEND: ws = wb.get_sheet_by_name(cap(slugify("%s" % (permanence)), 31)) error, error_msg = import_purchase_sheet( ws, permanence=permanence, customer_2_id_dict=customer_2_id_dict, producer_2_id_dict=producer_2_id_dict ) if error: error_msg = cap(slugify("%s" % (permanence)), 31) + " > " + error_msg else: error = True error_msg = _("The permanence has already been invoiced.") else: error = True error_msg = _("The permanence doesn't exists.") return error, error_msg
def worksheet_setup_a4(worksheet, title1, title2, add_print_title=True): title1 = "%s" % title1 worksheet.title = cap(slugify(title1), 31) worksheet.page_setup.paperSize = worksheet.PAPERSIZE_A4 worksheet.page_setup.fitToPage = True worksheet.page_setup.fitToHeight = 0 worksheet.page_setup.fitToWidth = 1 worksheet.print_gridlines = True if add_print_title: worksheet.add_print_title(1, rows_or_cols='rows') worksheet.freeze_panes = 'A2'.encode("utf8") worksheet.header_footer.left_header.text = Site.objects.get_current().name worksheet.header_footer.left_footer.text = "%s" % (title2) worksheet.header_footer.center_footer.text = title1 worksheet.header_footer.right_footer.text = 'Page &[Page]/&[Pages]' orders_responsible = Staff.objects.filter(is_reply_to_order_email=True, is_active=True).order_by('?').first() invoices_responsible = Staff.objects.filter(is_reply_to_invoice_email=True, is_active=True).order_by('?').first() s1 = EMPTY_STRING if orders_responsible: c = orders_responsible.customer_responsible if c is not None: s1 = "%s: %s, %s" % (_("Orders"), c.long_basket_name, c.phone1) s2 = EMPTY_STRING if invoices_responsible: c = invoices_responsible.customer_responsible if c is not None: s2 = "%s: %s, %s" % (_("Invoices"), c.long_basket_name, c.phone1) separator = chr(10) + " " worksheet.header_footer.right_header.text = separator.join((s1, s2)) return worksheet
def admin_duplicate(queryset, producer): user_message = _("The product is duplicated.") user_message_level = messages.INFO product_count = 0 long_name_postfix = "%s" % _(" (COPY)") max_length = Product_Translation._meta.get_field('long_name').max_length - len(long_name_postfix) for product in queryset: product_count += 1 new_long_name = "%s%s" % (cap(product.long_name, max_length), _(" (COPY)")) old_product_production_mode = product.production_mode.all() product.id = None product.reference = None product.producer = producer product.save() for production_mode in old_product_production_mode: product.production_mode.add(production_mode.id) Product_Translation.objects.create( master_id=product.id, long_name=new_long_name, offer_description=EMPTY_STRING, language_code=translation.get_language() ) if product_count > 1: user_message = _("The products are duplicated.") return user_message, user_message_level
def admin_duplicate(queryset, producer): user_message = _("The product is duplicated.") user_message_level = messages.INFO product_count = 0 long_name_postfix = "{}".format(_(" (COPY)")) max_length = Product_Translation._meta.get_field('long_name').max_length - len(long_name_postfix) for product in queryset: product_count += 1 new_long_name = "{}{}".format(cap(product.long_name, max_length), _(" (COPY)")) old_product_production_mode = product.production_mode.all() product.id = None product.reference = None product.producer = producer product.save() for production_mode in old_product_production_mode: product.production_mode.add(production_mode.id) Product_Translation.objects.create( master_id=product.id, long_name=new_long_name, offer_description=EMPTY_STRING, language_code=translation.get_language() ) if product_count > 1: user_message = _("The products are duplicated.") return user_message, user_message_level
def get_validation_formula(wb=None, valid_values=None): if valid_values: ws_dv_name = cap(slugify("%s" % (_("data validation"))), 31) ws_dv = wb.get_sheet_by_name(ws_dv_name) if ws_dv is None: ws_dv = wb.create_sheet(index=0) worksheet_setup_landscape_a4(ws_dv, ws_dv_name, EMPTY_STRING) col_dv = 0 c = ws_dv.cell(row=0, column=col_dv) while (c.value is not None) and (col_dv < 20): col_dv += 1 c = ws_dv.cell(row=0, column=col_dv) col_letter_dv = get_column_letter(col_dv + 1) row_num = 0 for v in valid_values: c = ws_dv.cell(row=row_num, column=col_dv) c.value = "%s" % (v) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT row_num += 1 return "'%s'!$%s$1:$%s$%s" % (ws_dv_name, col_letter_dv, col_letter_dv, row_num + 1) else: return EMPTY_STRING
def admin_duplicate(queryset): user_message = _("The box is duplicated.") user_message_level = messages.INFO box_count = 0 long_name_postfix = "{}".format(_(" (COPY)")) max_length = Product_Translation._meta.get_field('long_name').max_length - len(long_name_postfix) for box in queryset: box_count += 1 new_long_name = "{}{}".format(cap(box.long_name, max_length), _(" (COPY)")) old_product_production_mode = box.production_mode.all() previous_box_id = box.id box.id = None box.reference = None box.save() for production_mode in old_product_production_mode: box.production_mode.add(production_mode.id) Product_Translation.objects.create( master_id=box.id, long_name=new_long_name, offer_description=EMPTY_STRING, language_code=translation.get_language() ) for box_content in BoxContent.objects.filter( box_id=previous_box_id ): box_content.id = None box_content.box_id=box.id box_content.save() if box_count > 1: user_message = _("The boxes are duplicated.") return user_message, user_message_level
def handle_uploaded_purchase(request, permanences, file_to_import, *args): error = False error_msg = None wb = load_workbook(file_to_import) # dict for performance optimisation purpose : read the DB only once customer_buyinggroup_id, customer_2_id_dict = get_customer_2_id_dict() producer_buyinggroup_id, producer_2_id_dict = get_producer_2_id_dict() if customer_buyinggroup_id is None: error = True error_msg = _("At least one customer must represent the buying group.") else: if producer_buyinggroup_id is None: error = True error_msg = _( "At least one producer must represent the buying group.") if not error: permanence = permanences.first() if permanence is not None: if permanence.status == PERMANENCE_SEND: ws = wb.get_sheet_by_name(cap(slugify("%s" % (permanence)), 31)) error, error_msg = import_purchase_sheet( ws, permanence=permanence, customer_2_id_dict=customer_2_id_dict, producer_2_id_dict=producer_2_id_dict) if error: error_msg = cap(slugify("%s" % (permanence)), 31) + " > " + error_msg else: error = True error_msg = _("The permanence has already been invoiced.") else: error = True error_msg = _("The permanence doesn't exists.") return error, error_msg
def admin_duplicate(product, producer): user_message = _("The product is duplicated.") user_message_level = messages.INFO long_name_postfix = "{}".format(_(" (COPY)")) max_length = Product_Translation._meta.get_field( "long_name").max_length - len(long_name_postfix) new_long_name = "{}{}".format(cap(product.long_name, max_length), _(" (COPY)")) old_product_production_mode = product.production_mode.all() product.id = None product.reference = None product.producer = producer product.save() for production_mode in old_product_production_mode: product.production_mode.add(production_mode.id) Product_Translation.objects.create( master_id=product.id, long_name=new_long_name, offer_description=EMPTY_STRING, language_code=translation.get_language(), ) return user_message, user_message_level
def worksheet_setup_a4(worksheet, title1, title2, add_print_title=True): title1 = "%s" % title1 worksheet.title = cap(slugify(title1), 31) worksheet.page_setup.paperSize = worksheet.PAPERSIZE_A4 worksheet.page_setup.fitToPage = True worksheet.page_setup.fitToHeight = 0 worksheet.page_setup.fitToWidth = 1 worksheet.print_gridlines = True if add_print_title: worksheet.add_print_title(1, rows_or_cols='rows') try: # Python 2 worksheet.freeze_panes = 'A2'.encode("utf8") except: # Python 3 worksheet.freeze_panes = 'A2' worksheet.header_footer.left_header.text = Site.objects.get_current().name worksheet.header_footer.left_footer.text = "%s" % (title2) worksheet.header_footer.center_footer.text = title1 worksheet.header_footer.right_footer.text = 'Page &[Page]/&[Pages]' orders_responsible = Staff.objects.filter( is_reply_to_order_email=True, is_active=True).order_by('?').first() invoices_responsible = Staff.objects.filter( is_reply_to_invoice_email=True, is_active=True).order_by('?').first() s1 = EMPTY_STRING if orders_responsible: c = orders_responsible.customer_responsible if c is not None: s1 = "%s: %s, %s" % (_("Orders"), c.long_basket_name, c.phone1) s2 = EMPTY_STRING if invoices_responsible: c = invoices_responsible.customer_responsible if c is not None: s2 = "%s: %s, %s" % (_("Invoices"), c.long_basket_name, c.phone1) separator = chr(10) + " " worksheet.header_footer.right_header.text = separator.join((s1, s2)) return worksheet
def set_comment(self, comment): if comment: if self.comment: self.comment = cap("{}, {}".format(self.comment, comment), 100) else: self.comment = cap(comment, 100)
def format_worksheet_title(title): return cap(slugify("{}".format(title)), 31)
def set_comment(self, comment): if comment: if self.comment: self.comment = cap("%s, %s" % (self.comment, comment), 100) else: self.comment = cap(comment, 100)
def export_purchase(permanence=None, year=None, producer=None, customer=None, wb=None): yellowFill = Fill() yellowFill.start_color.index = 'FFEEEE11' yellowFill.end_color.index = 'FFEEEE11' yellowFill.fill_type = Fill.FILL_SOLID header = [ (_("Format"), 5), (_("Id"), 10), (_("Date"), 15), (_("producer"), 15), (_("product"), 60), (_("customer"), 15), (_("quantity invoiced"), 10), (_("producer unit price"), 10), (_("deposit"), 10), (_("purchase price"), 10), (_("tax"), 10), (_("rule of 3"), 10), (_("comment"), 30), (_("vat level"), 10), (_("CustId_01"), 10), ] if producer is None: if permanence is not None: if customer is not None: producers = Producer.objects.filter( producerinvoice__permanence_id=permanence.id).distinct( ).iterator() title1 = "%s-%s" % (customer.short_basket_name, permanence) else: producers = Producer.objects.filter( producerinvoice__permanence_id=permanence.id).distinct( ).iterator() title1 = "%s" % permanence else: producers = Producer.objects.filter( producerinvoice__permanence__permanence_date__year=year ).distinct().iterator() title1 = "%s-%d" % (customer.short_basket_name, year) else: producers = Producer.objects.filter(id=producer.id).iterator() title1 = "%s-%d" % (producer.short_profile_name, year) producer = next_row(producers) if producer is not None: wb, ws = new_landscape_a4_sheet(wb, title1, _('invoices'), header) row_num = 1 count_all_purchase = 0 purchase_price_all_purchase = [] tax_all_purchase = [] while producer is not None: producer_save = producer # count_producer_purchase = 0 producer_price = DECIMAL_ZERO if producer.invoice_by_basket: if year is None: if customer is None: purchases = Purchase.objects.filter( permanence_id=permanence.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "customer__short_basket_name", "offer_item__translations__preparation_sort_order" ).iterator() else: purchases = Purchase.objects.filter( permanence_id=permanence.id, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "customer__short_basket_name", "offer_item__translations__preparation_sort_order" ).iterator() else: if customer is None: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_INVOICED, permanence_date__year=year, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "permanence_date", "permanence_id", "customer__short_basket_name", "offer_item__translations__preparation_sort_order" ).iterator() else: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_INVOICED, permanence_date__year=year, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order" ).iterator() purchase = next_purchase(purchases) while purchase is not None: permanence_save = purchase.permanence count_permanence_purchase = 0 row_start_permanence = 0 row_num += 1 while purchase is not None and permanence_save.id == purchase.permanence_id: customer_save = purchase.customer count_purchase = 0 row_start_purchase = 0 purchases_price = DECIMAL_ZERO while purchase is not None and permanence_save.id == purchase.permanence_id \ and customer_save.id == purchase.customer_id: offer_item_save = purchase.offer_item department_for_customer_save = offer_item_save.department_for_customer department_for_customer_save__short_name = department_for_customer_save.short_name \ if department_for_customer_save is not None else EMPTY_STRING while purchase is not None and permanence_save.id == purchase.permanence_id \ and customer_save.id == purchase.customer_id \ and department_for_customer_save == purchase.offer_item.department_for_customer: c = ws.cell(row=row_num, column=1) c.value = purchase.id c = ws.cell(row=row_num, column=2) c.value = permanence_save.permanence_date c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY c = ws.cell(row=row_num, column=3) c.value = producer_save.short_profile_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_purchase == 0: row_start_purchase = row_num + 1 if count_permanence_purchase == 0: c.style.font.bold = True row_start_permanence = row_start_purchase c = ws.cell(row=row_num, column=0) c.value = "A" else: c = ws.cell(row=row_num, column=0) c.value = "B" count_purchase += 1 c = ws.cell(row=row_num, column=4) if department_for_customer_save__short_name is not None: c.value = "%s - %s" % ( purchase.get_long_name(), department_for_customer_save__short_name ) else: c.value = "%s" % purchase.get_long_name() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=5) c.value = customer_save.short_basket_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_purchase == 0 and customer is None: c.style.font.bold = True c = ws.cell(row=row_num, column=6) c.value = purchase.quantity_invoiced c.style.number_format.format_code = '#,##0.????' if year is None: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(7) + str(row_num + 1), 'notEqual', [str(purchase.quantity_invoiced)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num, column=7) c.value = purchase.get_producer_unit_price() c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX # if year is None: # c.style.font.color = Color(Color.BLUE) c = ws.cell(row=row_num, column=8) c.value = purchase.offer_item.unit_deposit.amount c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=9) c.value = '=ROUND(G%s*(H%s+I%s),2)' % ( row_num + 1, row_num + 1, row_num + 1) if year is None: purchase_price = ( purchase.quantity_invoiced * (purchase.get_producer_unit_price() + purchase.get_unit_deposit()) ).quantize(TWO_DECIMALS) purchases_price += purchase_price # Asked by GAC HAMOIS : sell broken products... # if offer_item_save.order_unit in [ # PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, # PRODUCT_ORDER_UNIT_LT # ]: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(purchase_price)], True, wb, None, None, yellowFill) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=10) c.value = '=G%s*%s' % ( row_num + 1, purchase.offer_item.customer_vat.amount) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=12) c.value = cap(purchase.comment, 100) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=13) c.value = purchase.offer_item.get_vat_level_display( ) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=14) c.value = purchase.customer.user.email c.style.number_format.format_code = NumberFormat.FORMAT_TEXT delta = 5 for col_num in range(5): c = ws.cell(row=row_num, column=delta + col_num) c.style.borders.top.border_style = Border.BORDER_THIN row_num += 1 purchase = next_purchase(purchases) count_permanence_purchase += count_purchase if year is None and count_purchase > 1: c = ws.cell(row=row_num - 1, column=11) c.value = '=SUM(J%s:J%s)' % (row_start_purchase, row_num) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(12) + str(row_num), 'notEqual', [str(purchases_price)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num - 1, column=0) c.value = "C" # row_num += 1 producer_price += purchases_price if count_permanence_purchase > 0: count_all_purchase += count_permanence_purchase purchase_price_producer_purchase = 'ROUND(SUM(J%s:J%s),2)' % ( row_start_permanence, row_num) purchase_price_all_purchase.append( purchase_price_producer_purchase) tax_producer_purchase = 'SUM(K%s:K%s)' % ( row_start_permanence, row_num) tax_all_purchase.append(tax_producer_purchase) row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s %s" % ( _("Total"), producer_save.short_profile_name, permanence_save) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % purchase_price_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True if year is None: ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(producer_price)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num, column=10) c.value = '=%s' % tax_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED row_num += 1 else: if year is None: # Using quantity_for_preparation_sort_order the order is by customer__short_basket_name if the product # is to be distributed by piece, otherwise by lower qty first. if customer is None: purchases = Purchase.objects.filter( permanence_id=permanence.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( # "product__placement", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order", "customer__short_basket_name").iterator() else: purchases = Purchase.objects.filter( permanence_id=permanence.id, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( # "product__placement", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order", "customer__short_basket_name").iterator() else: if customer is None: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_INVOICED, permanence_date__year=year, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order", "customer__short_basket_name").iterator() else: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_INVOICED, permanence_date__year=year, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation .get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order").iterator() purchase = next_purchase(purchases) while purchase is not None: permanence_save = purchase.permanence count_permanence_purchase = 0 row_start_permanence = 0 row_num += 1 while purchase is not None and permanence_save.id == purchase.permanence_id: producer_save = purchase.producer department_for_customer_save = purchase.offer_item.department_for_customer department_for_customer_save__short_name = department_for_customer_save.short_name \ if department_for_customer_save is not None else EMPTY_STRING while purchase is not None and permanence_save.id == purchase.permanence_id \ and producer_save == purchase.producer \ and department_for_customer_save == purchase.offer_item.department_for_customer: offer_item_save = purchase.offer_item count_offer_item = 0 row_first_offer_item = row_num offer_items_price = DECIMAL_ZERO for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.top.border_style = Border.BORDER_THIN row_start_offer_item = 0 while purchase is not None and offer_item_save == purchase.offer_item: c = ws.cell(row=row_num, column=1) c.value = purchase.id c = ws.cell(row=row_num, column=2) c.value = permanence_save.permanence_date c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY c = ws.cell(row=row_num, column=3) c.value = producer_save.short_profile_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_offer_item == 0: row_start_offer_item = row_num + 1 if count_permanence_purchase == 0: c.style.font.bold = True row_start_permanence = row_start_offer_item c = ws.cell(row=row_num, column=0) c.value = "A" else: c = ws.cell(row=row_num, column=0) c.value = "B" c = ws.cell(row=row_num, column=4) if department_for_customer_save__short_name is not None: c.value = "%s - %s" % ( purchase.get_long_name(), department_for_customer_save__short_name ) else: c.value = "%s" % purchase.get_long_name() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_offer_item != 0: c.style.font.color.index = 'FF939393' c = ws.cell(row=row_num, column=5) c.value = purchase.customer.short_basket_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=6) c.value = purchase.quantity_invoiced c.style.number_format.format_code = '#,##0.????' if year is None: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(7) + str(row_num + 1), 'notEqual', [str(purchase.quantity_invoiced)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num, column=7) if count_offer_item == 0: producer_unit_price = purchase.get_producer_unit_price( ) c.value = producer_unit_price c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(8) + str(row_num + 1), 'notEqual', [str(producer_unit_price)], True, wb, None, None, yellowFill) else: c.value = '=H%s' % (row_first_offer_item + 1) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=8) c.value = purchase.offer_item.unit_deposit.amount c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=9) c.value = '=ROUND(G%s*(H%s+I%s),2)' % ( row_num + 1, row_first_offer_item + 1, row_num + 1) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX if year is None: offer_item_price = ( purchase.quantity_invoiced * (purchase.get_producer_unit_price() + purchase.get_unit_deposit()) ).quantize(TWO_DECIMALS) offer_items_price += offer_item_price if offer_item_save.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(offer_item_price)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num, column=10) c.value = '=G%s*%s' % ( row_num + 1, purchase.offer_item.customer_vat.amount) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=12) c.value = cap(purchase.comment, 100) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=13) c.value = purchase.offer_item.get_vat_level_display( ) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=14) c.value = purchase.customer.user.email c.style.number_format.format_code = NumberFormat.FORMAT_TEXT delta = 5 for col_num in range(5): c = ws.cell(row=row_num, column=delta + col_num) c.style.borders.top.border_style = Border.BORDER_THIN purchase = next_purchase(purchases) row_num += 1 count_offer_item += 1 count_permanence_purchase += count_offer_item if year is None and count_offer_item > 1: if not offer_item_save.wrapped and offer_item_save.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: c = ws.cell(row=row_num - 1, column=11) c.value = '=SUM(J%s:J%s)' % ( row_start_offer_item, row_num) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(12) + str(row_num), 'notEqual', [str(offer_items_price)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num - 1, column=0) c.value = "D" # row_num += 1 producer_price += offer_items_price if count_permanence_purchase > 0: count_all_purchase += count_permanence_purchase purchase_price_producer_purchase = 'ROUND(SUM(J%s:J%s),2)' % ( row_start_permanence, row_num) purchase_price_all_purchase.append( purchase_price_producer_purchase) tax_producer_purchase = 'SUM(K%s:K%s)' % ( row_start_permanence, row_num) tax_all_purchase.append(tax_producer_purchase) row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s %s" % ( _("Total"), producer_save.short_profile_name, permanence_save) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % purchase_price_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True if year is None: ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(producer_price)], True, wb, None, None, yellowFill) c = ws.cell(row=row_num, column=10) c.value = '=%s' % tax_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED row_num += 1 producer = next_row(producers) if count_all_purchase > 0: row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s" % (_('Total'), title1) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % "+".join(purchase_price_all_purchase) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True c = ws.cell(row=row_num, column=10) c.value = '=%s' % "+".join(tax_all_purchase) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.top.border_style = Border.BORDER_MEDIUMDASHED if year is None: ws.column_dimensions[get_column_letter(3)].visible = False ws.column_dimensions[get_column_letter(11)].visible = False else: ws.column_dimensions[get_column_letter(12)].visible = False ws.column_dimensions[get_column_letter(1)].visible = False ws.column_dimensions[get_column_letter(15)].visible = False return wb
def __str__(self): return cap(escape(self.get_notification_display()), 50)
def __str__(self): return cap(escape(self.safe_translation_getter( 'notification', any_language=True)), 50)
def import_purchase_sheet(worksheet, permanence=None, customer_2_id_dict=None, producer_2_id_dict=None ): error = False error_msg = None import_counter = 0 header = get_header(worksheet) if header: row_num = 1 array_purchase = [] rule_of_3_source = DECIMAL_ZERO row = get_row(worksheet, header, row_num) while row and not error: try: row_format = row[_('Format')] if row_format in ["A", "B"]: import_counter += 1 if row[_('Id')] is None: error = True error_msg = _("Row %(row_num)d : No purchase id given.") % {'row_num': row_num + 1} break row_id = Decimal(row[_('Id')]) purchase = Purchase.objects.filter(id=row_id).order_by('?').first() if purchase is None: error = True error_msg = _("Row %(row_num)d : No purchase corresponding to the given purchase id.") % { 'row_num': row_num + 1} break if purchase.permanence_id != permanence.id: error = True error_msg = _("Row %(row_num)d : The given permanence doesn't own the given purchase id.") % { 'row_num': row_num + 1} break producer_id = None if row[_('producer')] in producer_2_id_dict: producer_id = producer_2_id_dict[row[_('producer')]] if producer_id != purchase.producer_id: error = True error_msg = _("Row %(row_num)d : No valid producer.") % {'row_num': row_num + 1} break customer_name = "%s" % row[_('customer')] if customer_name in customer_2_id_dict: customer_id = customer_2_id_dict[customer_name] if customer_id != purchase.customer_id: error = True error_msg = _("Row %(row_num)d : No valid customer") % {'row_num': row_num + 1} break comment = cap(row[_('comment')], 100) quantity_invoiced = DECIMAL_ZERO if row[_('quantity invoiced')] is None \ else Decimal(row[_('quantity invoiced')]).quantize(FOUR_DECIMALS) producer_row_price = row[_('purchase price')] if producer_row_price is not None: producer_row_price = Decimal(producer_row_price).quantize(TWO_DECIMALS) if purchase.purchase_price.amount != producer_row_price: purchase_price_modified = True if purchase.offer_item.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: producer_unit_price = (purchase.offer_item.producer_unit_price.amount + purchase.offer_item.unit_deposit.amount).quantize(TWO_DECIMALS) if producer_unit_price != DECIMAL_ZERO: purchase.quantity_invoiced = (producer_row_price / producer_unit_price).quantize(FOUR_DECIMALS) else: purchase.quantity_invoiced = DECIMAL_ZERO else: purchase_price_modified = False elif purchase.quantity_invoiced != quantity_invoiced: purchase.quantity_invoiced = quantity_invoiced purchase_price_modified = False else: purchase_price_modified = False if row_format == "A": array_purchase = [] rule_of_3_source = DECIMAL_ZERO if not purchase_price_modified: producer_unit_price = Decimal(row[_('producer unit price')]).quantize(TWO_DECIMALS) previous_producer_unit_price = purchase.get_producer_unit_price() if producer_unit_price != previous_producer_unit_price: offer_item = OfferItem.objects.filter(id=purchase.offer_item_id).order_by('?').first() offer_item.producer_unit_price = producer_unit_price recalculate_prices(offer_item, offer_item.producer_price_are_wo_vat, offer_item.is_resale_price_fixed, offer_item.price_list_multiplier) offer_item.save() recalculate_order_amount( permanence_id=offer_item.permanence_id, offer_item_queryset=OfferItem.objects.filter(id=offer_item.id).order_by('?'), send_to_producer=False ) purchase.comment = comment purchase.save() rule_of_3_source += purchase.purchase_price.amount array_purchase.append(purchase) elif row_format in ["C", "D"]: rule_of_3_target = row[_('rule of 3')] if rule_of_3_target is not None: rule_of_3_target = Decimal(rule_of_3_target).quantize(TWO_DECIMALS) if rule_of_3_target != rule_of_3_source: max_purchase_counter = len(array_purchase) if max_purchase_counter <= 1: error = True error_msg = _("Row %(row_num)d : Rule of 3 target in wrong context.") % { 'row_num': row_num + 1} return error, error_msg else: if rule_of_3_source != DECIMAL_ZERO: ratio = rule_of_3_target / rule_of_3_source else: if rule_of_3_target == DECIMAL_ZERO: ratio = DECIMAL_ZERO else: ratio = DECIMAL_ONE # Rule of 3 if ratio != DECIMAL_ONE: adjusted_invoice = DECIMAL_ZERO for i, purchase in enumerate(array_purchase, start=1): producer_unit_price = (purchase.offer_item.producer_unit_price.amount + purchase.offer_item.unit_deposit.amount ).quantize(TWO_DECIMALS) if i == max_purchase_counter: delta = rule_of_3_target - adjusted_invoice if producer_unit_price != DECIMAL_ZERO: purchase.quantity_invoiced = (delta / producer_unit_price).quantize( FOUR_DECIMALS) else: purchase.quantity_invoiced = DECIMAL_ZERO else: purchase.quantity_invoiced = (purchase.quantity_invoiced * ratio).quantize( FOUR_DECIMALS) adjusted_invoice += purchase.quantity_invoiced * producer_unit_price purchase.save() row_num += 1 row = get_row(worksheet, header, row_num) except KeyError, e: # Missing field error = True error_msg = _("Row %(row_num)d : A required column is missing %(error_msg)s.") % { 'row_num': row_num + 1, 'error_msg': str(e)} except Exception, e: error = True error_msg = _("Row %(row_num)d : %(error_msg)s.") % {'row_num': row_num + 1, 'error_msg': str(e)}
def export_purchase(permanence=None, year=None, producer=None, customer=None, wb=None): yellowFill = Fill() yellowFill.start_color.index = 'FFEEEE11' yellowFill.end_color.index = 'FFEEEE11' yellowFill.fill_type = Fill.FILL_SOLID header = [ (_("Format"), 5), (_("Id"), 10), (_("Date"), 15), (_("producer"), 15), (_("product"), 60), (_("customer"), 15), (_("quantity invoiced"), 10), (_("producer unit price"), 10), (_("deposit"), 10), (_("purchase price"), 10), (_("tax"), 10), (_("rule of 3"), 10), (_("comment"), 30), (_("vat level"), 10), ] if producer is None: if permanence is not None: producers = Producer.objects.filter( producerinvoice__permanence_id=permanence.id ).distinct().iterator() title1 = "%s" % permanence else: producers = Producer.objects.filter( producerinvoice__permanence__permanence_date__year=year ).distinct().iterator() title1 = "%s-%d" % (customer.short_basket_name, year) else: producers = Producer.objects.filter(id=producer.id).iterator() title1 = "%s-%d" % (producer.short_profile_name, year) producer = next_row(producers) if producer is not None: wb, ws = new_landscape_a4_sheet( wb, title1, _('invoices'), header ) row_num = 1 count_all_purchase = 0 purchase_price_all_purchase = [] tax_all_purchase = [] while producer is not None: producer_save = producer # count_producer_purchase = 0 producer_price = DECIMAL_ZERO if producer.invoice_by_basket: if year is None: purchases = Purchase.objects.filter( permanence_id=permanence.id, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( "customer__short_basket_name", "offer_item__translations__preparation_sort_order" ).iterator() else: if customer is None: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_DONE, permanence_date__year=year, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( "permanence_date", "permanence_id", "customer__short_basket_name", "offer_item__translations__preparation_sort_order" ).iterator() else: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_DONE, permanence_date__year=year, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order" ).iterator() purchase = next_purchase(purchases) while purchase is not None: permanence_save = purchase.permanence count_permanence_purchase = 0 row_start_permanence = 0 row_num += 1 while purchase is not None and permanence_save.id == purchase.permanence_id: customer_save = purchase.customer count_purchase = 0 row_start_purchase = 0 purchases_price = DECIMAL_ZERO while purchase is not None and permanence_save.id == purchase.permanence_id \ and customer_save.id == purchase.customer_id: offer_item_save = purchase.offer_item department_for_customer_save = offer_item_save.department_for_customer department_for_customer_save__short_name = department_for_customer_save.short_name \ if department_for_customer_save is not None else EMPTY_STRING while purchase is not None and permanence_save.id == purchase.permanence_id \ and customer_save.id == purchase.customer_id \ and department_for_customer_save == purchase.offer_item.department_for_customer: c = ws.cell(row=row_num, column=1) c.value = purchase.id c = ws.cell(row=row_num, column=2) c.value = permanence_save.permanence_date c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY c = ws.cell(row=row_num, column=3) c.value = producer_save.short_profile_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_purchase == 0: row_start_purchase = row_num + 1 if count_permanence_purchase == 0: c.style.font.bold = True row_start_permanence = row_start_purchase c = ws.cell(row=row_num, column=0) c.value = "A" else: c = ws.cell(row=row_num, column=0) c.value = "B" count_purchase += 1 c = ws.cell(row=row_num, column=4) if department_for_customer_save__short_name is not None: c.value = "%s - %s" % ( purchase.get_long_name(), department_for_customer_save__short_name) else: c.value = "%s" % purchase.get_long_name() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=5) c.value = customer_save.short_basket_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_purchase == 0 and customer is None: c.style.font.bold = True c = ws.cell(row=row_num, column=6) c.value = purchase.quantity_invoiced c.style.number_format.format_code = '#,##0.????' if year is None: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(7) + str(row_num + 1), 'notEqual', [str(purchase.quantity_invoiced)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=7) c.value = purchase.get_producer_unit_price() c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=8) c.value = purchase.offer_item.unit_deposit.amount c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=9) c.value = '=ROUND(G%s*(H%s+I%s),2)' % (row_num + 1, row_num + 1, row_num + 1) if year is None: purchase_price = (purchase.quantity_invoiced * (purchase.get_producer_unit_price() + purchase.get_unit_deposit())).quantize(TWO_DECIMALS) purchases_price += purchase_price if offer_item_save.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(purchase_price)], True, wb, None, None, yellowFill ) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=10) c.value = '=G%s*%s' % (row_num + 1, purchase.offer_item.customer_vat.amount) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=12) c.value = cap(purchase.comment, 100) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=13) c.value = purchase.offer_item.get_vat_level_display() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT delta = 5 for col_num in range(5): c = ws.cell(row=row_num, column=delta + col_num) c.style.borders.bottom.border_style = Border.BORDER_THIN row_num += 1 purchase = next_purchase(purchases) count_permanence_purchase += count_purchase if year is None and count_purchase > 1: c = ws.cell(row=row_num, column=11) c.value = '=SUM(J%s:J%s)' % (row_start_purchase, row_num) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(12) + str(row_num + 1), 'notEqual', [str(purchases_price)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=0) c.value = "C" row_num += 1 producer_price += purchases_price if count_permanence_purchase > 0: count_all_purchase += count_permanence_purchase purchase_price_producer_purchase = 'ROUND(SUM(J%s:J%s),2)' % (row_start_permanence, row_num) purchase_price_all_purchase.append(purchase_price_producer_purchase) tax_producer_purchase = 'SUM(K%s:K%s)' % (row_start_permanence, row_num) tax_all_purchase.append(tax_producer_purchase) row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s %s" % (_("Total"), producer_save.short_profile_name, permanence_save) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % purchase_price_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True if year is None: ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(producer_price)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=10) c.value = '=%s' % tax_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.bottom.border_style = Border.BORDER_MEDIUMDASHED row_num += 1 else: if year is None: # Using quantity_for_preparation_sort_order the order is by customer__short_basket_name if the product # is to be distributed by piece, otherwise by lower qty first. purchases = Purchase.objects.filter( permanence_id=permanence.id, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( # "product__placement", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order", "customer__short_basket_name" ).iterator() else: if customer is None: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_DONE, permanence_date__year=year, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order", "customer__short_basket_name" ).iterator() else: purchases = Purchase.objects.filter( permanence__status__gte=PERMANENCE_DONE, permanence_date__year=year, customer_id=customer.id, producer_id=producer.id, offer_item__translations__language_code=translation.get_language() ).order_by( "permanence_date", "permanence_id", "offer_item__translations__preparation_sort_order", "quantity_for_preparation_sort_order" ).iterator() purchase = next_purchase(purchases) while purchase is not None: permanence_save = purchase.permanence count_permanence_purchase = 0 row_start_permanence = 0 row_num += 1 while purchase is not None and permanence_save.id == purchase.permanence_id: producer_save = purchase.producer department_for_customer_save = purchase.offer_item.department_for_customer department_for_customer_save__short_name = department_for_customer_save.short_name \ if department_for_customer_save is not None else EMPTY_STRING while purchase is not None and permanence_save.id == purchase.permanence_id \ and producer_save == purchase.producer \ and department_for_customer_save == purchase.offer_item.department_for_customer: offer_item_save = purchase.offer_item count_offer_item = 0 row_first_offer_item = row_num offer_items_price = DECIMAL_ZERO for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.bottom.border_style = Border.BORDER_THIN row_start_offer_item = 0 while purchase is not None and offer_item_save == purchase.offer_item: c = ws.cell(row=row_num, column=1) c.value = purchase.id c = ws.cell(row=row_num, column=2) c.value = permanence_save.permanence_date c.style.number_format.format_code = NumberFormat.FORMAT_DATE_DDMMYYYY c = ws.cell(row=row_num, column=3) c.value = producer_save.short_profile_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_offer_item == 0: row_start_offer_item = row_num + 1 if count_permanence_purchase == 0: c.style.font.bold = True row_start_permanence = row_start_offer_item c = ws.cell(row=row_num, column=0) c.value = "A" else: c = ws.cell(row=row_num, column=0) c.value = "B" c = ws.cell(row=row_num, column=4) if department_for_customer_save__short_name is not None: c.value = "%s - %s" % ( purchase.get_long_name(), department_for_customer_save__short_name) else: c.value = "%s" % purchase.get_long_name() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT if count_offer_item != 0: c.style.font.color.index = 'FF939393' c = ws.cell(row=row_num, column=5) c.value = purchase.customer.short_basket_name c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=6) c.value = purchase.quantity_invoiced c.style.number_format.format_code = '#,##0.????' if year is None: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(7) + str(row_num + 1), 'notEqual', [str(purchase.quantity_invoiced)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=7) if count_offer_item == 0: producer_unit_price = purchase.get_producer_unit_price() c.value = producer_unit_price c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(8) + str(row_num + 1), 'notEqual', [str(producer_unit_price)], True, wb, None, None, yellowFill ) else: c.value = '=H%s' % (row_first_offer_item + 1) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=8) c.value = purchase.offer_item.unit_deposit.amount c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=9) c.value = '=ROUND(G%s*(H%s+I%s),2)' % (row_num + 1, row_first_offer_item + 1, row_num + 1) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX if year is None: offer_item_price = (purchase.quantity_invoiced * (purchase.get_producer_unit_price() + purchase.get_unit_deposit())).quantize( TWO_DECIMALS) offer_items_price += offer_item_price if offer_item_save.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(offer_item_price)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=10) c.value = '=G%s*%s' % (row_num + 1, purchase.offer_item.customer_vat.amount) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c = ws.cell(row=row_num, column=12) c.value = cap(purchase.comment, 100) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c = ws.cell(row=row_num, column=13) c.value = purchase.offer_item.get_vat_level_display() c.style.number_format.format_code = NumberFormat.FORMAT_TEXT delta = 5 for col_num in range(5): c = ws.cell(row=row_num, column=delta + col_num) c.style.borders.bottom.border_style = Border.BORDER_THIN purchase = next_purchase(purchases) row_num += 1 count_offer_item += 1 count_permanence_purchase += count_offer_item if year is None and count_offer_item > 1: if not offer_item_save.wrapped and offer_item_save.order_unit in [ PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, PRODUCT_ORDER_UNIT_LT ]: c = ws.cell(row=row_num, column=11) c.value = '=SUM(J%s:J%s)' % (row_start_offer_item, row_num) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.color = Color(Color.BLUE) ws.conditional_formatting.addCellIs( get_column_letter(12) + str(row_num + 1), 'notEqual', [str(offer_items_price)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=0) c.value = "D" producer_price += offer_items_price if count_permanence_purchase > 0: count_all_purchase += count_permanence_purchase purchase_price_producer_purchase = 'ROUND(SUM(J%s:J%s),2)' % (row_start_permanence, row_num) purchase_price_all_purchase.append(purchase_price_producer_purchase) tax_producer_purchase = 'SUM(K%s:K%s)' % (row_start_permanence, row_num) tax_all_purchase.append(tax_producer_purchase) row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s %s" % (_("Total"), producer_save.short_profile_name, permanence_save) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % purchase_price_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True if year is None: ws.conditional_formatting.addCellIs( get_column_letter(10) + str(row_num + 1), 'notEqual', [str(producer_price)], True, wb, None, None, yellowFill ) c = ws.cell(row=row_num, column=10) c.value = '=%s' % tax_producer_purchase c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.bottom.border_style = Border.BORDER_MEDIUMDASHED row_num += 1 producer = next_row(producers) if count_all_purchase > 0: row_num += 1 c = ws.cell(row=row_num, column=8) c.value = "%s : %s" % (_('Total'), title1) c.style.number_format.format_code = NumberFormat.FORMAT_TEXT c.style.font.bold = True c.style.alignment.horizontal = c.style.alignment.HORIZONTAL_RIGHT c = ws.cell(row=row_num, column=9) c.value = '=%s' % "+".join(purchase_price_all_purchase) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX c.style.font.bold = True c = ws.cell(row=row_num, column=10) c.value = '=%s' % "+".join(tax_all_purchase) c.style.number_format.format_code = REPANIER_SETTINGS_CURRENCY_XLSX row_num += 1 for col_num in range(14): c = ws.cell(row=row_num, column=col_num) c.style.borders.bottom.border_style = Border.BORDER_MEDIUMDASHED if year is None: ws.column_dimensions[get_column_letter(3)].visible = False ws.column_dimensions[get_column_letter(11)].visible = False else: ws.column_dimensions[get_column_letter(12)].visible = False ws.column_dimensions[get_column_letter(1)].visible = False return wb
def get_producers(self): if self.status == PERMANENCE_PLANNED: if len(self.producers.all()) > 0: changelist_url = urlresolvers.reverse( 'admin:repanier_product_changelist', ) link = [] for p in self.producers.all(): link.append( '<a href="%s?producer=%d"> %s</a>' % ( changelist_url, p.id, p.short_profile_name)) msg_html = '<div class="wrap-text">%s</div>' % ", ".join(link) else: msg_html = '<div class="wrap-text">%s</div>' % _("No offer") elif self.status == PERMANENCE_PRE_OPEN: msg_html = '<div class="wrap-text">%s</div>' % ", ".join([p.short_profile_name + " (" + p.phone1 + ")" for p in self.producers.all()]) elif self.status in [PERMANENCE_OPENED, PERMANENCE_CLOSED]: close_offeritem_changelist_url = urlresolvers.reverse( 'admin:repanier_offeritemclosed_changelist', ) link = [] for p in self.producers.all().only("id"): pi = ProducerInvoice.objects.filter( producer_id=p.id, permanence_id=self.id ).order_by('?').first() if pi is not None: if pi.status == PERMANENCE_OPENED: label = ('%s (%s) ' % (p.short_profile_name, pi.get_total_price_with_tax())).replace( ' ', ' ') offeritem_changelist_url = close_offeritem_changelist_url else: label = ('%s (%s) %s' % ( p.short_profile_name, pi.get_total_price_with_tax(), LOCK_UNICODE)).replace(' ', ' ') offeritem_changelist_url = close_offeritem_changelist_url else: label = ('%s ' % (p.short_profile_name,)).replace(' ', ' ') offeritem_changelist_url = close_offeritem_changelist_url link.append( '<a href="%s?permanence=%s&producer=%d">%s</a>' % ( offeritem_changelist_url, self.id, p.id, label)) msg_html = '<div class="wrap-text">%s</div>' % ", ".join(link) elif self.status in [PERMANENCE_SEND, PERMANENCE_INVOICED, PERMANENCE_ARCHIVED]: send_offeritem_changelist_url = urlresolvers.reverse( 'admin:repanier_offeritemsend_changelist', ) send_customer_changelist_url = urlresolvers.reverse( 'admin:repanier_customersend_changelist', ) link = [] at_least_one_permanence_send = False for pi in ProducerInvoice.objects.filter(permanence_id=self.id).select_related( "producer").order_by('producer'): if pi.status == PERMANENCE_SEND: at_least_one_permanence_send = True if pi.producer.invoice_by_basket: changelist_url = send_customer_changelist_url else: changelist_url = send_offeritem_changelist_url # Important : no target="_blank" label = '%s (%s) %s' % ( pi.producer.short_profile_name, pi.get_total_price_with_tax(), LOCK_UNICODE) link.append( '<a href="%s?permanence=%d&producer=%d"> %s</a>' % ( changelist_url, self.id, pi.producer_id, label.replace(' ', ' ') )) else: if pi.invoice_reference: if pi.to_be_invoiced_balance != DECIMAL_ZERO or pi.total_price_with_tax != DECIMAL_ZERO: label = "%s (%s - %s)" % ( pi.producer.short_profile_name, pi.to_be_invoiced_balance, cap(pi.invoice_reference, 15) ) else: label = "%s (%s)" % ( pi.producer.short_profile_name, cap(pi.invoice_reference, 15) ) else: if pi.to_be_invoiced_balance != DECIMAL_ZERO or pi.total_price_with_tax != DECIMAL_ZERO: label = "%s (%s)" % ( pi.producer.short_profile_name, pi.to_be_invoiced_balance ) else: # label = "%s" % ( # pi.producer.short_profile_name # ) continue # Important : target="_blank" because the invoices must be displayed without the cms_toolbar # Such that they can be accessed by the producer and by the staff link.append( '<a href="%s?producer=%d" target="_blank">%s</a>' % ( urlresolvers.reverse('producer_invoice_view', args=(pi.id,)), pi.producer_id, label.replace(' ', ' '))) producers = ", ".join(link) if at_least_one_permanence_send: msg_html = '<div class="wrap-text">%s</div>' % producers else: msg_html = """ <div class="wrap-text"><button onclick="django.jQuery('#id_get_producers_%d').toggle(); if(django.jQuery(this).html()=='%s'){ django.jQuery(this).html('%s') }else{ django.jQuery(this).html('%s') }; return false;" >%s</button> <div id="id_get_producers_%d" style="display:none;">%s</div></div> """ % ( self.id, _("Show"), _("Hide"), _("Show"), _("Show"), self.id, producers ) else: msg_html = '<div class="wrap-text">%s</div>' % ", ".join([p.short_profile_name for p in Producer.objects.filter( producerinvoice__permanence_id=self.id).only( 'short_profile_name')]) return mark_safe(msg_html)
def import_purchase_sheet(worksheet, permanence=None, customer_2_id_dict=None, producer_2_id_dict=None): error = False error_msg = None import_counter = 0 header = get_header(worksheet) if header: row_num = 1 array_purchase = [] rule_of_3_source = DECIMAL_ZERO row = get_row(worksheet, header, row_num) getcontext().rounding = ROUND_HALF_UP while row and not error: try: row_format = row[_('Format')] if row_format in ["A", "B", "C", "D"]: import_counter += 1 if row[_('Id')] is None: error = True error_msg = _( "Row %(row_num)d : No purchase id given.") % { 'row_num': row_num + 1 } break row_id = Decimal(row[_('Id')]) purchase = Purchase.objects.filter( id=row_id).order_by('?').first() if purchase is None: error = True error_msg = _( "Row %(row_num)d : No purchase corresponding to the given purchase id." ) % { 'row_num': row_num + 1 } break if purchase.permanence_id != permanence.id: error = True error_msg = _( "Row %(row_num)d : The given permanence doesn't own the given purchase id." ) % { 'row_num': row_num + 1 } break offer_item = OfferItem.objects.filter( id=purchase.offer_item_id).order_by('?').first() if offer_item is None: error = True error_msg = _( "Row %(row_num)d : No offer_item corresponding to the given purchase id." ) % { 'row_num': row_num + 1 } break producer_id = None if row[_('producer')] in producer_2_id_dict: producer_id = producer_2_id_dict[row[_('producer')]] if producer_id != purchase.producer_id: error = True error_msg = _( "Row %(row_num)d : No valid producer.") % { 'row_num': row_num + 1 } break customer_name = "%s" % row[_('customer')] if customer_name in customer_2_id_dict: customer_id = customer_2_id_dict[customer_name] if customer_id != purchase.customer_id: error = True error_msg = _( "Row %(row_num)d : No valid customer") % { 'row_num': row_num + 1 } break comment = cap(row[_('comment')], 100) quantity_has_been_modified = False producer_row_price = row[_('purchase price')] if producer_row_price is not None: producer_row_price = Decimal( producer_row_price).quantize(TWO_DECIMALS) if purchase.purchase_price.amount != producer_row_price: quantity_has_been_modified = True # Asked by GAC HAMOIS : sell broken products... # if purchase.offer_item.order_unit in [ # PRODUCT_ORDER_UNIT_KG, PRODUCT_ORDER_UNIT_PC_KG, # PRODUCT_ORDER_UNIT_LT # ]: producer_unit_price = ( purchase.offer_item.producer_unit_price.amount + purchase.offer_item.unit_deposit.amount ).quantize(TWO_DECIMALS) if producer_unit_price != DECIMAL_ZERO: purchase.quantity_invoiced = ( producer_row_price / producer_unit_price ).quantize(FOUR_DECIMALS) else: purchase.quantity_invoiced = DECIMAL_ZERO if not quantity_has_been_modified: quantity_invoiced = DECIMAL_ZERO if row[_('quantity invoiced')] is None \ else Decimal(row[_('quantity invoiced')]).quantize(FOUR_DECIMALS) if purchase.quantity_invoiced != quantity_invoiced: purchase.quantity_invoiced = quantity_invoiced if row_format == "A": array_purchase = [] rule_of_3_source = DECIMAL_ZERO producer_unit_price = row[_('producer unit price')] if producer_unit_price is not None and not purchase.producer.invoice_by_basket: previous_producer_unit_price = purchase.get_producer_unit_price( ) if producer_unit_price != previous_producer_unit_price: offer_item.producer_unit_price = producer_unit_price offer_item.save() purchase.comment = comment purchase.save() rule_of_3_source += purchase.purchase_price.amount array_purchase.append(purchase) if row_format in ["C", "D"]: rule_of_3_target = row[_('rule of 3')] if rule_of_3_target is not None: rule_of_3_target = Decimal(rule_of_3_target).quantize( TWO_DECIMALS) # print(rule_of_3_target) if rule_of_3_target != rule_of_3_source: max_purchase_counter = len(array_purchase) if max_purchase_counter <= 1: error = True error_msg = _( "Row %(row_num)d : Rule of 3 target in wrong context." ) % { 'row_num': row_num + 1 } return error, error_msg else: if rule_of_3_source != DECIMAL_ZERO: ratio = rule_of_3_target / rule_of_3_source else: if rule_of_3_target == DECIMAL_ZERO: ratio = DECIMAL_ZERO else: ratio = DECIMAL_ONE # Rule of 3 if ratio != DECIMAL_ONE: adjusted_invoice = DECIMAL_ZERO for i, purchase in enumerate( array_purchase, start=1): producer_unit_price = purchase.offer_item.producer_unit_price.amount if i == max_purchase_counter: delta = rule_of_3_target - adjusted_invoice if producer_unit_price != DECIMAL_ZERO: purchase.quantity_invoiced = ( delta / producer_unit_price ).quantize(FOUR_DECIMALS) else: purchase.quantity_invoiced = DECIMAL_ZERO else: purchase.quantity_invoiced = ( purchase.quantity_invoiced * ratio).quantize(FOUR_DECIMALS) adjusted_invoice += ( purchase.quantity_invoiced * producer_unit_price ).quantize(TWO_DECIMALS) purchase.save() row_num += 1 row = get_row(worksheet, header, row_num) except KeyError as e: # Missing field error = True error_msg = _( "Row %(row_num)d : A required column is missing %(error_msg)s." ) % { 'row_num': row_num + 1, 'error_msg': str(e) } except Exception as e: error = True error_msg = _("Row %(row_num)d : %(error_msg)s.") % { 'row_num': row_num + 1, 'error_msg': str(e) } if import_counter == 0: error = True error_msg = "%s" % _("Nothing to import.") # if not error: # recalculate_order_amount( # permanence_id=permanence.id, # re_init=True # ) return error, error_msg
def admin_generate_bank_account_movement( request, permanence=None, payment_date=None, customer_buyinggroup=None): if permanence is not None and payment_date is not None and customer_buyinggroup is not None: for producer in Producer.objects.filter(to_be_paid=True): producer_invoice = ProducerInvoice.objects.filter( producer_id=producer.id, permanence_id=permanence.id).first() # We have to pay something result_set = BankAccount.objects.filter( producer_id=producer.id, producer_invoice__isnull=True ).order_by('?').aggregate(Sum('bank_amount_in'), Sum('bank_amount_out')) if result_set["bank_amount_in__sum"] is not None: bank_in = RepanierMoney(result_set["bank_amount_in__sum"]) else: bank_in = REPANIER_MONEY_ZERO if result_set["bank_amount_out__sum"] is not None: bank_out = RepanierMoney(result_set["bank_amount_out__sum"]) else: bank_out = REPANIER_MONEY_ZERO bank_not_invoiced = bank_out - bank_in if producer.balance.amount != DECIMAL_ZERO or producer_invoice.to_be_invoiced_balance.amount != DECIMAL_ZERO or bank_not_invoiced != DECIMAL_ZERO: delta = (producer_invoice.to_be_invoiced_balance.amount - bank_not_invoiced.amount).quantize(TWO_DECIMALS) if delta > DECIMAL_ZERO: if producer_invoice.invoice_reference: operation_comment = producer_invoice.invoice_reference else: if producer.represent_this_buyinggroup: operation_comment = permanence.get_permanence_display(with_status=False) else: if producer_invoice is not None: if producer_invoice.total_price_with_tax.amount == delta: operation_comment = _("Delivery %(current_site)s - %(permanence)s. Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'permanence' : permanence.get_permanence_display( with_status=False) } else: operation_comment = _( "Deliveries %(current_site)s - up to the %(permanence)s (included). Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'permanence' : permanence.get_permanence_display( with_status=False) } else: operation_comment = _( "Deliveries %(current_site)s - up to %(payment_date)s (included). Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'payment_date': payment_date.strftime( settings.DJANGO_SETTINGS_DATE) } BankAccount.objects.create( permanence_id=None, producer_id=producer.id, customer=None, operation_date=payment_date, operation_status=BANK_CALCULATED_INVOICE, operation_comment=cap(operation_comment, 100), bank_amount_out=delta, customer_invoice=None, producer_invoice=None ) delta = (producer.balance.amount - producer_invoice.to_be_invoiced_balance.amount).quantize(TWO_DECIMALS) if delta != DECIMAL_ZERO: operation_comment = _("Correction %(producer)s") \ % { 'producer': producer.short_profile_name } if delta > DECIMAL_ZERO: # Profit for the group : the producer ask less than what is sold # --> This bank movement is not a real entry # operation_status=BANK_PROFIT # making this, it will not be removed from the new calculated bank account total BankAccount.objects.create( permanence_id=permanence.id, producer=None, customer_id=customer_buyinggroup.id, operation_date=payment_date, operation_status=BANK_PROFIT, operation_comment=cap(operation_comment, 100), bank_amount_in=delta, customer_invoice_id=None, producer_invoice=None ) elif delta < DECIMAL_ZERO: # Loss for the group : the producer ask more than what is sold # --> This bank movement is not a real entry # operation_status=BANK_PROFIT # making this, it will not be removed from the new calculated bank account total BankAccount.objects.create( permanence_id=permanence.id, producer=None, customer_id=customer_buyinggroup.id, operation_date=payment_date, operation_status=BANK_PROFIT, operation_comment=cap(operation_comment, 100), bank_amount_out=-delta, customer_invoice_id=None, producer_invoice=None ) producer_invoice.to_be_paid = False producer_invoice.balance.amount -= delta producer_invoice.save(update_fields=['to_be_paid', 'balance']) producer.balance.amount -= delta producer.save(update_fields=['balance']) # Mark previous not paid invoices as paid for previous_producer_invoice in ProducerInvoice.objects.filter( producer_id=producer_invoice.producer_id, invoice_sort_order__isnull=False, to_be_paid=True ).order_by('?'): previous_producer_invoice.to_be_paid=False previous_producer_invoice.save(update_fields=['to_be_paid']) for producer in Producer.objects.filter(to_be_paid=False): producer_invoice = ProducerInvoice.objects.filter( producer_id=producer.id, permanence_id=permanence ).order_by('?').first() if producer_invoice is not None: producer_invoice.to_be_paid = True producer_invoice.save(update_fields=['to_be_paid']) return
def admin_generate_bank_account_movement(permanence, payment_date, customer_buyinggroup): for producer_invoice in ProducerInvoice.objects.filter( permanence_id=permanence.id, invoice_sort_order__isnull=True, to_be_paid=True).select_related("producer"): # We have to pay something producer = producer_invoice.producer result_set = BankAccount.objects.filter( producer_id=producer.id, producer_invoice__isnull=True).order_by('?').aggregate( Sum('bank_amount_in'), Sum('bank_amount_out')) if result_set["bank_amount_in__sum"] is not None: bank_in = RepanierMoney(result_set["bank_amount_in__sum"]) else: bank_in = REPANIER_MONEY_ZERO if result_set["bank_amount_out__sum"] is not None: bank_out = RepanierMoney(result_set["bank_amount_out__sum"]) else: bank_out = REPANIER_MONEY_ZERO bank_not_invoiced = bank_out - bank_in if producer.balance.amount != DECIMAL_ZERO or producer_invoice.to_be_invoiced_balance.amount != DECIMAL_ZERO or bank_not_invoiced != DECIMAL_ZERO: delta = (producer_invoice.to_be_invoiced_balance.amount - bank_not_invoiced.amount).quantize(TWO_DECIMALS) if delta > DECIMAL_ZERO: if producer_invoice.invoice_reference: operation_comment = producer_invoice.invoice_reference else: if producer.represent_this_buyinggroup: operation_comment = permanence.get_permanence_display() else: if producer_invoice is not None: if producer_invoice.total_price_with_tax.amount == delta: operation_comment = _("Delivery %(current_site)s - %(permanence)s. Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'permanence' : permanence.get_permanence_display() } else: operation_comment = _( "Deliveries %(current_site)s - up to the %(permanence)s (included). Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'permanence' : permanence.get_permanence_display() } else: operation_comment = _( "Deliveries %(current_site)s - up to %(payment_date)s (included). Thanks!") \ % { 'current_site': REPANIER_SETTINGS_GROUP_NAME, 'payment_date': payment_date.strftime( settings.DJANGO_SETTINGS_DATE) } BankAccount.objects.create( permanence_id=None, producer_id=producer.id, customer=None, operation_date=payment_date, operation_status=BANK_CALCULATED_INVOICE, operation_comment=cap(operation_comment, 100), bank_amount_out=delta, customer_invoice=None, producer_invoice=None) delta = (producer.balance.amount - producer_invoice.to_be_invoiced_balance.amount ).quantize(TWO_DECIMALS) if delta != DECIMAL_ZERO: # Profit or loss for the group operation_comment = _("Correction %(producer)s") \ % { 'producer': producer.short_profile_name } BankAccount.objects.create( permanence_id=permanence.id, producer=None, customer_id=customer_buyinggroup.id, operation_date=payment_date, operation_status=BANK_PROFIT, operation_comment=cap(operation_comment, 100), bank_amount_in=delta if delta > DECIMAL_ZERO else DECIMAL_ZERO, bank_amount_out=-delta if delta < DECIMAL_ZERO else DECIMAL_ZERO, customer_invoice_id=None, producer_invoice=None) producer_invoice.balance.amount -= delta producer_invoice.save(update_fields=['balance']) producer.balance.amount -= delta producer.save(update_fields=['balance']) return