# -*- coding: utf-8 -*- from django.shortcuts import render,redirect, get_object_or_404 from django.http import JsonResponse from django.db.models import Sum, F from django.db import transaction from datetime import datetime import pandas as pd import json from io import StringIO from .models import Project, ProjectItem from management.models import Good, Vendor, StorageFacility from stock.models import Stock, Reservation, InvoiceWritingOffMaterial, WritingOffMaterialsItem, Receipt, ReceiptItem from custom_user.models import User from .filters import ProjectFilter from stock.views import get_next_number_invoice_write_off from action_history.views import user_action_audit, changes_history def convert_date_format(input_date): input_date = input_date.strip() format_str = '%d.%m.%Y' try: date_object = datetime.strptime(input_date, format_str) formatted_date = date_object.strftime('%Y-%m-%d') return formatted_date except ValueError as e: return None # def process_project_items(project_id, project_item): # try: # project_items_delete = ProjectItem.objects.filter(project=project_id) # project_items_delete.delete() # itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index() # for _, row in itm.iterrows(): # good_name = row['Товар'] # unit_of_measurement = row['Одиниці'] # quantity = float(row['Кількість']) # project_item = ProjectItem.objects.create( # project=Project.objects.get(id=project_id), # good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement), # quantity = quantity, # is_write_off = False, # ) # except Exception as error: # raise error # def process_project_items(project_id, project_item): # try: # project_items = ProjectItem.objects.filter(project=project_id).values('good__name', 'quantity') # old_value = serializers.serialize('json', project_items) # itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index() # for _, row in itm.iterrows(): # good_name = row['Товар'] # unit_of_measurement = row['Одиниці'] # quantity = float(row['Кількість']) # project_item = ProjectItem.objects.create( # project=Project.objects.get(id=project_id), # good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement), # quantity = quantity, # is_write_off = False, # ) # new_value = serializers.serialize('json', ProjectItem.objects.filter(project=project_id)) # audit_changes(old_value, new_value) # except Exception as error: # raise error # def process_project_items(project_id, project_item): # from django.core.serializers.json import DjangoJSONEncoder # try: # with transaction.atomic(): # old_project_items = ProjectItem.objects.filter(project=project_id).values('good__name', 'quantity') # # old_value = serializers.serialize('json', Good.objects.filter(id=id)) # # old_value = serializers.serialize('json', old_project_items) # old_value = json.dumps(list(old_project_items), cls=DjangoJSONEncoder) # print(f"old_value ==> {old_value}") # # Додавання нових project_item # itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index() # for _, row in itm.iterrows(): # good_name = row['Товар'] # unit_of_measurement = row['Одиниці'] # quantity = float(row['Кількість']) # ProjectItem.objects.create( # project=Project.objects.get(id=project_id), # good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement), # quantity=quantity, # is_write_off=False, # ) # new_project_items = ProjectItem.objects.filter(project=project_id).values('good__name', 'quantity') # new_value = json.dumps(list(new_project_items), cls=DjangoJSONEncoder) # print(f"new_value ==> {new_value}") # # Видалення project_item # # deleted_items = set(old_value) - set(new_value) # # print(f"deleted_items ==> {deleted_items}") # # if deleted_items: # # for deleted_item_id in deleted_items: # # deleted_item = ProjectItem.objects.get(id=deleted_item_id) # # deleted_item.delete() # # Оновлення project_item (якщо потрібно) # # updated_items = set(old_project_items) & set(new_project_items) # # for updated_item_id in updated_items: # # old_item = ProjectItem.objects.get(id=updated_item_id) # # new_item = ProjectItem.objects.get(id=updated_item_id) # # new_project_items_after_changes = ProjectItem.objects.filter(project=project_id).values('good__name', 'quantity') # # new_project_items_after_changes = serializers.serialize('json', new_project_items_after_changes) # # print(f"new_project_items_after_changes ==> {new_project_items_after_changes}") # audit_changes(old_value, new_value) # except Exception as error: # print(f"process_project_items ERROR ==> {error}") # raise error # def process_project_items(project_id, project_item): # from django.core.serializers.json import DjangoJSONEncoder # try: # old_project_items = list(ProjectItem.objects.filter(project=project_id).values_list('id','good__name', 'quantity')) # project_items_for_delete = ProjectItem.objects.filter(project=project_id) # project_items_for_delete.delete() # itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index() # if itm.empty: # items = ProjectItem.objects.filter(project=project_id) # items.delete() # for _, row in itm.iterrows(): # good_name = row['Товар'] # unit_of_measurement = row['Одиниці'] # quantity = float(row['Кількість']) # ProjectItem.objects.create( # project=Project.objects.get(id=project_id), # good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement), # quantity=quantity, # is_write_off=False, # ) # new_project_items = list(ProjectItem.objects.filter(project=project_id).values_list('id','good__name', 'quantity')) # old_record = json.dumps([list(item) for item in old_project_items], cls=DjangoJSONEncoder) # new_record = json.dumps([list(item) for item in new_project_items], cls=DjangoJSONEncoder) # audit_changes(old_record, new_record) # except Exception as error: # raise error def process_project_items(project_id, project_item, user): try: # Додавання нових project_item itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index() all_ids_before_update = set(ProjectItem.objects.filter(project=project_id).values_list('id', flat=True)) all_ids_after_update = set() print(f"all_ids_before_update ==> {all_ids_before_update}") for _, row in itm.iterrows(): good_name = row['Товар'] unit_of_measurement = row['Одиниці'] quantity = float(row['Кількість']) print(f"good_name ==> {good_name}, unit_of_measurement==> {unit_of_measurement}, quantity ==> {quantity}") if ProjectItem.objects.filter(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id,is_write_off=False).exists(): item_exist = ProjectItem.objects.get(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id) all_ids_after_update.add(item_exist.id) if ProjectItem.objects.get(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id).quantity != quantity: print(f"ITEM UPDATE") project_item = ProjectItem.objects.get(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id) old_record = json.dumps({'name_item': project_item.good.name, 'quantity': project_item.quantity}) item_updated = ProjectItem.objects.get(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id) item_updated.quantity = quantity item_updated.save() all_ids_after_update.add(item_updated.id) new_record = ProjectItem.objects.get(good__name=good_name, good_id__unit_of_measurement=unit_of_measurement, project = project_id) new_record = json.dumps({'name_item': new_record.good.name, 'quantity': new_record.quantity}) changes_history("Оновлено", project_id, good_name, user, old_record, new_record) else: print(f"ITEM CREATE") item_created = ProjectItem.objects.create( project=Project.objects.get(id=project_id), good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement), quantity=quantity, is_write_off=False, ) all_ids_after_update.add(item_created.id) created_item = ProjectItem.objects.get(id=item_created.id) new_record = json.dumps({'name_item': created_item.good.name, 'quantity': created_item.quantity}) changes_history("Створено", project_id, good_name, user, json.dumps({'name_item':'', 'quantity':'0'}), new_record) items_for_delete = all_ids_before_update - all_ids_after_update print(f"items_for_delete ==> {items_for_delete}") if items_for_delete: for item in items_for_delete: item_delete = ProjectItem.objects.get(id = item) item_delete.delete() changes_history("Видалено", project_id, item_delete.good.name, user, json.dumps([{"name_item":item_delete.good.name,"quantity":quantity}]), json.dumps([{"name_item":"", "quantity":0}])) # audit_changes(old_project_items, new_project_items_after_changes) except Exception as error: print(f"process_project_items ERROR ==> {error}") raise error def project(request): if request.method == "GET": projects = Project.objects.all().order_by("-created_at")[:5] for item in projects: user = User.objects.get(email = item.created_by) or 'System' item.user = user count_all_projects = Project.objects.all().count() return render(request, 'project/project.html', context={'projects': projects, "count_all_projects":count_all_projects}) elif request.method == "POST": data = json.loads(request.body) project_name = data['data'].get("project_name") project = Project.objects.filter(name = project_name).values('id','name', 'customer', 'date_start', 'created_by') if project: project_item = project.first() user = User.objects.get(email = project_item.get('created_by')) project_item = { 'project_id': project_item.get('id'), 'name': project_item.get('name'), 'customer': project_item.get('customer'), 'date_start': project_item.get('date_start'), 'created_by': project_item.get('created_by'), 'user_first_name': user.first_name, 'user_last_name': user.last_name, } return JsonResponse({ 'message': 'Success', 'project_item':project_item }, safe=False) def add_new_project(request): try: if request.method == "GET": return render(request, 'project/new_project.html') elif request.method == "POST": data = json.loads(request.body) name = data['data'].get("name") customer = data['data'].get("customer") date_start = data['data'].get("date_start") formatted_date_start = datetime.strptime(date_start, '%d-%m-%Y') date_end = data['data'].get("date_end") formatted_date_end = datetime.strptime(date_end, '%d-%m-%Y') description = data['data'].get("description") with transaction.atomic(): old_record = json.dumps({'name': '', 'customer': '', 'description':'', 'date_start':'', 'date_end':''}) project = Project.objects.create( name = name, customer = customer, date_start = formatted_date_start, date_end = formatted_date_end, description = description, created_by = request.user ) new_value = Project.objects.get(id=project.id) new_record = json.dumps({'name': new_value.name, 'customer': new_value.customer, 'description':new_value.description, 'date_start':str(new_value.date_start), 'date_end':str(new_value.date_end)}) changes_history("Створено", project.id, project.name, request.user, old_record, new_record) table = data['data'].get("table") project_item = pd.read_html(table)[0] process_project_items(project.id, project_item, request.user) return JsonResponse({'message': 'Success'}) except Exception as error: print(f"add_new_project() ERROR ==> {error}") # changes_history(request.user, f"Додано обьєкт {name}", {error}) return JsonResponse({'message': 'Error', 'error': str(error)}) def project_detail(request, id): goods = Good.objects.all() project = Project.objects.get(id=id) project_items = ProjectItem.objects.filter(project=project.id, is_write_off = False) project_price = 0 write_of_items = None total_price_write_off = 0 for item in project_items: stock_entries = Stock.objects.filter(good_id=item.good).order_by('date') remaining_quantity = item.quantity total_price = 0.0 for stock_entry in stock_entries: if remaining_quantity <= 0: break if stock_entry.quantity >= remaining_quantity: total_price += remaining_quantity * stock_entry.price remaining_quantity = 0 else: remaining_quantity -= stock_entry.quantity item.reservation = Reservation.objects.filter(good=item.good, project=id ).aggregate(Sum('quantity'))['quantity__sum'] or 0 item.is_reservation = "False" item.is_write_of = "False" good = Good.objects.filter(name=item).first().id item.availability = Stock.objects.filter(good_id=good).aggregate(Sum('actual_quantity'))['actual_quantity__sum'] or 0 nestacha = item.quantity - item.availability if nestacha > 0: item.nestacha = nestacha reserv = Reservation.objects.filter(good=good, quantity = item.quantity, project = id) if reserv: item.is_reservation = "True" invoice_write_off = InvoiceWritingOffMaterial.objects.filter(project = id).first() if invoice_write_off: write_of_items = WritingOffMaterialsItem.objects.filter(invoice = invoice_write_off) total_price_write_off = 0.0 price_per_unit = WritingOffMaterialsItem.objects.filter(invoice = invoice_write_off) if price_per_unit: for item in price_per_unit: price = item.price * item.quantity total_price_write_off += price return render(request, 'project/project_detail.html', {'project': project, 'project_items': project_items, 'goods': goods, 'project_price':project_price, 'write_of_items':write_of_items, 'total_price_write_off':total_price_write_off}) def project_update(request): try: if request.method == "POST": data = json.loads(request.body) name = data['data'].get("name") customer = data['data'].get("customer") date_start = data['data'].get("date_start") formatted_date_start = datetime.strptime(date_start, '%d-%m-%Y') date_end = data['data'].get("date_end") formatted_date_end = datetime.strptime(date_end, '%d-%m-%Y') description = data['data'].get("description") project_id = data['data'].get("project_id") project = get_object_or_404(Project, id=project_id) old_value = Project.objects.get(id=project_id) old_record = json.dumps({'name': old_value.name, 'customer': old_value.customer, 'description':old_value.description, 'date_start':str(old_value.date_start), 'date_end':str(old_value.date_end)}) with transaction.atomic(): # Оновлюємо значення проекту project.name = name project.customer = customer project.date_start = formatted_date_start project.date_end = formatted_date_end project.description = description project.save() new_value = Project.objects.get(id=project_id) new_record = json.dumps({'name': new_value.name, 'customer': new_value.customer, 'description':new_value.description, 'date_start':str(new_value.date_start), 'date_end':str(new_value.date_end)}) changes_history("Оновлено", project_id, project.name, request.user, old_record, new_record) table = data['data'].get("table") project_item = pd.read_html(table)[0] process_project_items(project.id, project_item, request.user) return JsonResponse({'message': 'Success'}) except Exception as error: print(f'project_update ERROR ==> {error}') # action_history(request.user, f"Оновлено обьєкт {project.name}", {error}) return JsonResponse({'message': 'Error', 'error': str(error)}) def project_delete(request): try: data = json.loads(request.body) project_id = data.get("project_id") with transaction.atomic(): is_write_off = InvoiceWritingOffMaterial.objects.filter(project = project_id).first() if is_write_off: return JsonResponse({'message': 'Error', 'data': str('Ви не можете видалити обьєкт, на нього вже є списання')}) reservation_items = Reservation.objects.filter(project = project_id) if reservation_items: for item in reservation_items: item.delete() project = get_object_or_404(Project, id=project_id) project.delete() changes_history("Видалено", project_id, project.name, request.user, json.dumps([{"name":project.name}]), json.dumps([{"name":""}])) return JsonResponse({'message': 'Success'}) except Exception as error: changes_history(request.user, f"Видалення обьєкт {project.name}", {error}) return JsonResponse({'message': 'Error', 'data': str(error)}) def add_tmc_to_project(request): data = {} try: if request.method == "POST": table = request.POST.get("table") project_id = request.POST.get("project_id") project_item = pd.read_html(table)[0] process_project_items(project_id, project_item, request.user) data["message"] = 'Success' return JsonResponse(data) except Exception as error: data["error"] = 'Помилка додавання ТМЦ' return JsonResponse(data) def checkActualQuantityAndShortage(request): try: if request.method == "POST": data = json.loads(request.body) good_name = data.get("good_name") unit_of_measurement = data.get("unit_of_measurement") good_quantity = data.get("good_quantity").replace(',','.') good = Good.objects.get(name=good_name,unit_of_measurement=unit_of_measurement) reservation = Reservation.objects.filter(good=good).aggregate(Sum('quantity'))['quantity__sum'] or 0 actual_quantity = Stock.objects.filter(good_id=good).aggregate(Sum('quantity'))['quantity__sum'] or 0 actual_quantity = actual_quantity - reservation shortage_quantity = float(actual_quantity) - float(good_quantity) result = {'status': 'success', 'actual_quantity': actual_quantity, 'shortage_quantity':shortage_quantity} return JsonResponse(result) except json.JSONDecodeError as e: return JsonResponse({'status': 'error', 'message': 'Invalid JSON format'}, status=400) except Exception as e: return JsonResponse({'status': 'error', 'message': str(e)}, status=500) def get_price_for_good(id, quantity): stock_entries = Stock.objects.filter(good_id=id).order_by('date') remaining_quantity = quantity total_price = 0.0 for stock_entry in stock_entries: if remaining_quantity <= 0: break if stock_entry.quantity >= remaining_quantity: # Якщо наявна достатня кількість total_price += remaining_quantity * stock_entry.price remaining_quantity = 0 else: # Якщо наявна кількість менша, ніж потрібно total_price += stock_entry.quantity * stock_entry.price remaining_quantity -= stock_entry.quantity return total_price def export_project_items_to_excel(reguest, id): from io import BytesIO from django.http import HttpResponse project = get_object_or_404(Project, id=id) project_items = ProjectItem.objects.filter(project=project.id) data = { "Товар": [item.good.name for item in project_items], "Кількість": [item.quantity for item in project_items], "Одиниці": [item.good.unit_of_measurement for item in project_items], "Вартість": [get_price_for_good(item.good.id, item.quantity) for item in project_items], } # total_cost = sum(data["Вартість"]) # data["Вартість проекту"] = [total_cost] * len(project_items) df = pd.DataFrame(data) # df.loc['Загалом']= df.sum({index (0), columns (1)}) with BytesIO() as b: with pd.ExcelWriter(b) as writer: writer.book.formats[0].set_text_wrap() df.to_excel(writer, sheet_name="Data", index=False) filename = f"project.xlsx" res = HttpResponse( b.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) res['Content-Disposition'] = f'attachment; filename={filename}' return res def add_additional_purchases(request, id): if request.method == 'GET': project = Project.objects.get(id = id) goods = Good.objects.all().values("name", "unit_of_measurement") vendors = Vendor.objects.all().values("name") return render(request, "project/additional_purchases.html", context={"project":project, "goods":goods, "vendors":vendors}) def save_additional_purchases(request): try: if request.method == 'POST': data = json.loads(request.body) table = data.get("table") project_id = data.get("project_id") vendor = data.get("vendor") invoice = data.get("invoice") html_buffer = StringIO(table) items = pd.read_html(html_buffer)[0] with transaction.atomic(): storage_facility = StorageFacility.objects.get(name = 'Склад_1') project = Project.objects.get(id=project_id) # Створюємо закупку receipt = Receipt.objects.create( vendor = vendor, created_by = request.user, invoice=invoice, storage_facility = storage_facility, ) for _, row in items.iterrows(): good_name = row['Товар'] unit_of_measurement = row['Одиниці'] quantity = float(row['Кількість']) good_price = float(row['Вартість']) utilized = float(row['Використанно']) price = good_price / quantity good = Good.objects.get(name=good_name, unit_of_measurement = unit_of_measurement) receipt_item = ReceiptItem.objects.create( receipt=Receipt.objects.get(id = receipt.id), good = good, quantity=quantity, price=float(price), total=good_price, created_by = request.user ) # Додаємо на склад identifier_write_off = Stock.objects.create( storage_facility_id=storage_facility, good_id=good, quantity=quantity, actual_quantity=quantity, price=price, receipt = receipt ) # Додаємо до списаних в обьєкт is_invoice_present = InvoiceWritingOffMaterial.objects.filter(project=project_id).exists() if is_invoice_present: write_off_invoice = InvoiceWritingOffMaterial.objects.get(project=project) if quantity > utilized: price = price * utilized item_for_write_off = WritingOffMaterialsItem.objects.create( quantity = utilized, good = good, invoice = write_off_invoice, price = price, identifier_write_off = identifier_write_off.id ) identifier_write_off.actual_quantity = F('actual_quantity') - utilized identifier_write_off.save() write_off_invoice.total_sum = F('total_sum') + price write_off_invoice.save() else: price = price * utilized item_for_write_off = WritingOffMaterialsItem.objects.create( quantity = quantity, good = good, invoice = write_off_invoice, price = price, identifier_write_off = identifier_write_off.id ) identifier_write_off.actual_quantity = F('actual_quantity') - quantity identifier_write_off.save() write_off_invoice.total_sum = F('total_sum') + price write_off_invoice.save() else: invoice = InvoiceWritingOffMaterial.objects.create( invoice = get_next_number_invoice_write_off(), reason = project.name, project = project, total_sum = 0, created_by = request.user.email ) if quantity > utilized: price = price * utilized item_for_write_off = WritingOffMaterialsItem.objects.create( quantity = utilized, good = good, invoice = invoice, price = price, identifier_write_off = identifier_write_off.id ) identifier_write_off.actual_quantity = F('actual_quantity') - utilized identifier_write_off.save() invoice.total_sum = F('total_sum') + price invoice.save() else: price = price * utilized item_for_write_off = WritingOffMaterialsItem.objects.create( quantity = quantity, good = good, invoice = invoice, price = price, identifier_write_off = identifier_write_off.id ) identifier_write_off.actual_quantity = F('actual_quantity') - quantity identifier_write_off.save() invoice.total_sum = F('total_sum') + price invoice.save() # ############################################ # action_history(request.user, f"Дозакупка на обьєкт {project.name} чек {invoice}", "OK") return JsonResponse({'message': 'Success'}) except Exception as error: # action_history(request.user, f"Дозакупка на обьєкт {project.name} чек {invoice}", {error}) return JsonResponse({'status': 'error', 'message': str(error)}, status=500) def return_from_project(request, id): try: if request.method == 'GET': project = Project.objects.get(id = id) is_write_off_invoice = InvoiceWritingOffMaterial.objects.get(project = project ) goods = WritingOffMaterialsItem.objects.filter(invoice=is_write_off_invoice).values('good__name') return render(request, "project/return_from_project.html", context={"project":project, "goods":goods}) except Exception as error: raise error def return_to_stock_from_project(request): try: if request.method == 'POST': data = json.loads(request.body) identifier_write_off = data.get("identifier_write_off") quantity = data.get("quantity") project_id = data.get("project_id") project = Project.objects.get(id = project_id ) with transaction.atomic(): remove_from_write_off = WritingOffMaterialsItem.objects.filter(identifier_write_off = identifier_write_off).first() if remove_from_write_off.quantity < float(quantity): return JsonResponse({'message': 'Error', 'data': str('Кількість повернення не може бути більше ніж наявна кількість')}) remove_from_write_off.quantity = F('quantity') - quantity remove_from_write_off.save() add_to_stock = Stock.objects.get(id = identifier_write_off) add_to_stock.actual_quantity = F('actual_quantity') + quantity add_to_stock.save() items_for_delete = WritingOffMaterialsItem.objects.filter(quantity = 0) for item in items_for_delete: item.delete() is_present_other_items = WritingOffMaterialsItem.objects.filter(invoice = remove_from_write_off.invoice ).exists() if not is_present_other_items: invoice_for_delete = InvoiceWritingOffMaterial.objects.get(project = project) invoice_for_delete.delete() return JsonResponse({'message': 'Success'}) except Exception as error: raise error