Python/Flask
[Day 8] WMS(Warehouse Management System) 구성 및 요구 사항 정의 3
깨준
2024. 7. 24. 17:06
출고 관리
- 출고 계획: 출고 예정인 상품의 종류, 수량, 출고 날짜 등을 계획합니다.
- 피킹 및 패킹: 출고 상품을 피킹(선별)하여 패킹(포장)합니다.
- 출고 처리: 출고된 상품을 스캔하여 데이터베이스에 기록하고, 운송 준비를 합니다.
출고 관리 실습예제 1
출고등록
다음은 출고승인된 정보에 대해 재고 기록(출고승인 후 자동으로 차감기록)을 하고 조회를 하기 위한 요구사항을 반영하여 화면설계 및 기능 설계를 한 예제입니다.
- 출고승인된 레코드는 재고에서 화주와 상품종류가 같을 경우 재고총량에서 차감되어 저장되고 출고조회화면에서는 출고완료로 출력된다.
- 추가적으로 출고를 위한 픽킹, 동선계획, 패킹등에 대한 정보를 더할 수 있다.
출고 테이블
CREATE TABLE outbound_registration (
id INT AUTO_INCREMENT PRIMARY KEY,
consignor VARCHAR(255) NOT NULL,
product_type VARCHAR(255) NOT NULL,
planned_quantity INT NOT NULL,
actual_quantity INT DEFAULT 0,
planned_date DATE NOT NULL,
actual_date DATE DEFAULT NULL,
picking_manager VARCHAR(255) DEFAULT NULL,
packing_manager VARCHAR(255) DEFAULT NULL,
shipping_manager VARCHAR(255) DEFAULT NULL,
storage_location VARCHAR(255) NOT NULL,
product_status VARCHAR(255) NOT NULL,
progress_status VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
디렉토리 구조
site1/
├── app.py
└── templates/
├── enterRegist.html
├── enterView.html
├── stockView.html
├── stockManage.html
├── stockView.html
└── outboundRegist.html
app.py
from flask import Flask, render_template, request, redirect, url_for
import mysql.connector
from mysql.connector import Error
from datetime import date, datetime
app = Flask(__name__)
# 데이터베이스 연결 설정
def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host='localhost',
user='joon',
password='1234',
database='backend'
)
except Error as e:
print(f"Error: '{e}'")
return connection
# 메인 페이지 라우트
@app.route('/')
def enterRegist():
return render_template('enterRegist.html')
# 데이터 저장 라우트
@app.route('/submit', methods=['POST'])
def submit():
if request.method == 'POST':
consignor = request.form['consignor']
product_type = request.form['product_type']
product_quantity = request.form['product_quantity']
arrival_date = request.form['arrival_date']
arrival_manager = request.form['arrival_manager']
storage_location = request.form['storage_location']
product_status = request.form['product_status']
progress_status = request.form['progress_status']
connection = create_connection()
cursor = connection.cursor()
cursor.execute("""
INSERT INTO incoming (consignor, product_type, product_quantity, arrival_date, arrival_manager, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, product_quantity, arrival_date, arrival_manager, storage_location, product_status, progress_status))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('enterRegist'))
# 출고 등록 라우트
@app.route('/outboundRegist', methods=['GET', 'POST'])
def outboundRegist():
if request.method == 'POST':
consignor = request.form['consignor']
product_type = request.form['product_type']
planned_quantity = request.form['planned_quantity']
planned_date = request.form['planned_date']
storage_location = request.form['storage_location']
product_status = request.form['product_status']
progress_status = request.form['progress_status']
connection = create_connection()
cursor = connection.cursor()
cursor.execute("""
INSERT INTO outbound_registration (consignor, product_type, planned_quantity, planned_date, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, planned_quantity, planned_date, storage_location, product_status, progress_status))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('outboundRegist'))
return render_template('outboundRegist.html')
def update_stock():
connection = create_connection()
cursor = connection.cursor(dictionary=True)
# 입고 테이블에서 입고완료인 경우만 가져오기
cursor.execute("""
SELECT * FROM incoming WHERE progress_status = '입고완료'
""")
incoming_data = cursor.fetchall()
for row in incoming_data:
consignor = row['consignor']
product_type = row['product_type']
product_quantity = row['product_quantity']
arrival_manager = row['arrival_manager']
storage_location = row['storage_location']
product_status = row['product_status']
progress_status = row['progress_status']
# 재고 테이블에서 동일한 화주와 상품종류가 있는지 확인
cursor.execute("""
SELECT * FROM stock WHERE consignor = %s AND product_type = %s AND storage_location = %s ORDER BY stock_update_date DESC LIMIT 1
""", (consignor, product_type, storage_location))
stock_entry = cursor.fetchone()
if stock_entry:
# 재고 수량을 계산
new_quantity = stock_entry['stock_quantity'] + product_quantity
# 재고 수량 업데이트
cursor.execute("""
UPDATE stock SET stock_quantity = %s, stock_update_date = %s, stock_manager = %s WHERE id = %s
""", (new_quantity, datetime.now(), arrival_manager, stock_entry['id']))
else:
# 재고 테이블에 새로운 레코드 추가
progress_status = "재고"
cursor.execute("""
INSERT INTO stock (consignor, product_type, stock_quantity, stock_update_date, stock_manager, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, product_quantity, datetime.now(), arrival_manager, storage_location, product_status, progress_status))
# incoming 테이블의 progress_status를 '재고반영완료'로 업데이트
cursor.execute("""
UPDATE incoming SET progress_status = '재고반영완료' WHERE id = %s
""", (row['id'],))
connection.commit()
cursor.close()
connection.close()
# 입고 조회 페이지 라우트
@app.route('/enterView', methods=['GET'])
def enterView():
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
arrival_date = request.args.get('arrival_date', '')
arrival_manager = request.args.get('arrival_manager', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM incoming
WHERE consignor LIKE %s AND product_type LIKE %s AND arrival_date LIKE %s AND arrival_manager LIKE %s AND progress_status LIKE %s
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + arrival_date + '%', '%' + arrival_manager + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM incoming WHERE consignor LIKE %s AND product_type LIKE %s AND arrival_date LIKE %s AND arrival_manager LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + arrival_date + '%', '%' + arrival_manager + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('enterView', page=page-1, consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status) if page > 1 else None
next_url = url_for('enterView', page=page+1, consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status) if page < total_pages else None
return render_template('enterView.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status)
# 재고 조회 페이지 라우트
@app.route('/stockView', methods=['GET'])
def stockView():
update_stock()
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
stock_manager = request.args.get('stock_manager', '')
storage_location = request.args.get('storage_location', '')
product_status = request.args.get('product_status', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM stock
WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s
ORDER BY stock_update_date DESC
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM stock WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('stockView', page=page-1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page > 1 else None
next_url = url_for('stockView', page=page+1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page < total_pages else None
return render_template('stockView.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status)
# 재고 관리 페이지 라우트
@app.route('/stockManage', methods=['GET', 'POST'])
def stockManage():
if request.method == 'POST':
update_ids = request.form.getlist('update_id')
storage_location = request.form.get('storage_location', '')
progress_status = request.form.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor()
for update_id in update_ids:
update_fields = []
update_values = []
if storage_location:
update_fields.append("storage_location = %s")
update_values.append(storage_location)
if progress_status:
update_fields.append("progress_status = %s")
update_values.append(progress_status)
update_values.append(update_id)
if update_fields:
cursor.execute(f"UPDATE stock SET {', '.join(update_fields)} WHERE id = %s", update_values)
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('stockManage'))
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
stock_manager = request.args.get('stock_manager', '')
storage_location = request.args.get('storage_location', '')
product_status = request.args.get('product_status', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM stock
WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s
ORDER BY stock_update_date DESC
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM stock WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('stockManage', page=page-1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page > 1 else None
next_url = url_for('stockManage', page=page+1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page < total_pages else None
return render_template('stockManage.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status)
# 데이터 수정 및 삭제 라우트
@app.route('/save_changes', methods=['POST'])
def save_changes():
update_ids = request.form.getlist('update_id')
delete_ids = request.form.getlist('delete_id')
consignor = request.form.get('consignor', '')
product_type = request.form.get('product_type', '')
product_quantity = request.form.get('product_quantity', '')
arrival_date = request.form.get('arrival_date', '')
arrival_manager = request.form.get('arrival_manager', '')
storage_location = request.form.get('storage_location', '')
product_status = request.form.get('product_status', '')
progress_status = request.form.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor()
for update_id in update_ids:
update_fields = []
update_values = []
if consignor:
update_fields.append("consignor = %s")
update_values.append(consignor)
if product_type:
update_fields.append("product_type = %s")
update_values.append(product_type)
if product_quantity:
update_fields.append("product_quantity = %s")
update_values.append(product_quantity)
if arrival_date:
update_fields.append("arrival_date = %s")
update_values.append(arrival_date)
if arrival_manager:
update_fields.append("arrival_manager = %s")
update_values.append(arrival_manager)
if storage_location:
update_fields.append("storage_location = %s")
update_values.append(storage_location)
if product_status:
update_fields.append("product_status = %s")
update_values.append(product_status)
if progress_status:
update_fields.append("progress_status = %s")
update_values.append(progress_status)
update_values.append(update_id)
if update_fields:
cursor.execute(f"UPDATE incoming SET {', '.join(update_fields)} WHERE id = %s", update_values)
for delete_id in delete_ids:
cursor.execute("DELETE FROM incoming WHERE id = %s", (delete_id,))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('enterView'))
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5001, debug=True)
출고 관리 실습예제 2
출고조회
- 출고 승인 후 재고에서 차감하고 최종 출고 처리되면 출고완료를 출력합니다.
- 재고 수량은 출고완료 후 출고수량만큼 자동으로 차감됩니다.
디렉토리 구조
site1/
├── app.py
└── templates/
├── enterRegist.html
├── enterView.html
├── stockView.html
├── stockManage.html
├── stockView.html
├── outboundRegist.html
└── outboundView.html
app.py
from flask import Flask, render_template, request, redirect, url_for
import mysql.connector
from mysql.connector import Error
from datetime import date, datetime
app = Flask(__name__)
# 데이터베이스 연결 설정
def create_connection():
connection = None
try:
connection = mysql.connector.connect(
host='localhost',
user='joon',
password='1234',
database='backend'
)
except Error as e:
print(f"Error: '{e}'")
return connection
# 메인 페이지 라우트
@app.route('/')
def enterRegist():
return render_template('enterRegist.html')
# 데이터 저장 라우트
@app.route('/submit', methods=['POST'])
def submit():
if request.method == 'POST':
consignor = request.form['consignor']
product_type = request.form['product_type']
product_quantity = request.form['product_quantity']
arrival_date = request.form['arrival_date']
arrival_manager = request.form['arrival_manager']
storage_location = request.form['storage_location']
product_status = request.form['product_status']
progress_status = request.form['progress_status']
connection = create_connection()
cursor = connection.cursor()
cursor.execute("""
INSERT INTO incoming (consignor, product_type, product_quantity, arrival_date, arrival_manager, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, product_quantity, arrival_date, arrival_manager, storage_location, product_status, progress_status))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('enterRegist'))
# 출고 등록 라우트
@app.route('/outboundRegist', methods=['GET', 'POST'])
def outboundRegist():
if request.method == 'POST':
consignor = request.form['consignor']
product_type = request.form['product_type']
planned_quantity = request.form['planned_quantity']
planned_date = request.form['planned_date']
storage_location = request.form['storage_location']
product_status = request.form['product_status']
progress_status = request.form['progress_status']
connection = create_connection()
cursor = connection.cursor()
cursor.execute("""
INSERT INTO outbound_registration (consignor, product_type, planned_quantity, planned_date, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, planned_quantity, planned_date, storage_location, product_status, progress_status))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('outboundRegist'))
return render_template('outboundRegist.html')
def update_stock():
connection = create_connection()
cursor = connection.cursor(dictionary=True)
# 입고 테이블에서 입고완료인 경우만 가져오기
cursor.execute("""
SELECT * FROM incoming WHERE progress_status = '입고완료'
""")
incoming_data = cursor.fetchall()
for row in incoming_data:
consignor = row['consignor']
product_type = row['product_type']
product_quantity = row['product_quantity']
arrival_manager = row['arrival_manager']
storage_location = row['storage_location']
product_status = row['product_status']
progress_status = row['progress_status']
# 재고 테이블에서 동일한 화주와 상품종류가 있는지 확인
cursor.execute("""
SELECT * FROM stock WHERE consignor = %s AND product_type = %s AND storage_location = %s ORDER BY stock_update_date DESC LIMIT 1
""", (consignor, product_type, storage_location))
stock_entry = cursor.fetchone()
if stock_entry:
# 재고 수량을 계산
new_quantity = stock_entry['stock_quantity'] + product_quantity
# 재고 수량 업데이트
cursor.execute("""
UPDATE stock SET stock_quantity = %s, stock_update_date = %s, stock_manager = %s WHERE id = %s
""", (new_quantity, datetime.now(), arrival_manager, stock_entry['id']))
else:
# 재고 테이블에 새로운 레코드 추가
progress_status = "재고"
cursor.execute("""
INSERT INTO stock (consignor, product_type, stock_quantity, stock_update_date, stock_manager, storage_location, product_status, progress_status)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""", (consignor, product_type, product_quantity, datetime.now(), arrival_manager, storage_location, product_status, progress_status))
# incoming 테이블의 progress_status를 '재고반영완료'로 업데이트
cursor.execute("""
UPDATE incoming SET progress_status = '재고반영완료' WHERE id = %s
""", (row['id'],))
connection.commit()
cursor.close()
connection.close()
def update_stock_outbound():
connection = create_connection()
cursor = connection.cursor(dictionary=True)
# 출고 테이블에서 출고승인인 경우만 가져오기
cursor.execute("""
SELECT * FROM outbound_registration WHERE progress_status = '출고승인'
""")
outbound_data = cursor.fetchall()
for row in outbound_data:
consignor = row['consignor']
product_type = row['product_type']
planned_quantity = row['planned_quantity']
storage_location = row['storage_location']
# 재고 테이블에서 동일한 화주와 상품종류가 있는지 확인
cursor.execute("""
SELECT * FROM stock WHERE consignor = %s AND product_type = %s AND storage_location = %s ORDER BY stock_update_date DESC LIMIT 1
""", (consignor, product_type, storage_location))
stock_entry = cursor.fetchone()
if stock_entry and stock_entry['stock_quantity'] >= planned_quantity:
# 재고 수량을 계산
new_quantity = stock_entry['stock_quantity'] - planned_quantity
# 재고 수량 업데이트
cursor.execute("""
UPDATE stock SET stock_quantity = %s, stock_update_date = %s WHERE id = %s
""", (new_quantity, datetime.now(), stock_entry['id']))
# outbound_registration 테이블의 progress_status를 '출고완료'로 업데이트
cursor.execute("""
UPDATE outbound_registration SET progress_status = '출고완료' WHERE id = %s
""", (row['id'],))
connection.commit()
cursor.close()
connection.close()
# 입고 조회 페이지 라우트
@app.route('/enterView', methods=['GET'])
def enterView():
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
arrival_date = request.args.get('arrival_date', '')
arrival_manager = request.args.get('arrival_manager', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM incoming
WHERE consignor LIKE %s AND product_type LIKE %s AND arrival_date LIKE %s AND arrival_manager LIKE %s AND progress_status LIKE %s
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + arrival_date + '%', '%' + arrival_manager + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM incoming WHERE consignor LIKE %s AND product_type LIKE %s AND arrival_date LIKE %s AND arrival_manager LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + arrival_date + '%', '%' + arrival_manager + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('enterView', page=page-1, consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status) if page > 1 else None
next_url = url_for('enterView', page=page+1, consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status) if page < total_pages else None
return render_template('enterView.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, arrival_date=arrival_date, arrival_manager=arrival_manager, progress_status=progress_status)
# 재고 조회 페이지 라우트
@app.route('/stockView', methods=['GET'])
def stockView():
update_stock()
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
stock_manager = request.args.get('stock_manager', '')
storage_location = request.args.get('storage_location', '')
product_status = request.args.get('product_status', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM stock
WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s
ORDER BY stock_update_date DESC
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM stock WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('stockView', page=page-1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page > 1 else None
next_url = url_for('stockView', page=page+1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page < total_pages else None
return render_template('stockView.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status)
# 재고 관리 페이지 라우트
@app.route('/stockManage', methods=['GET', 'POST'])
def stockManage():
if request.method == 'POST':
update_ids = request.form.getlist('update_id')
storage_location = request.form.get('storage_location', '')
progress_status = request.form.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor()
for update_id in update_ids:
update_fields = []
update_values = []
if storage_location:
update_fields.append("storage_location = %s")
update_values.append(storage_location)
if progress_status:
update_fields.append("progress_status = %s")
update_values.append(progress_status)
update_values.append(update_id)
if update_fields:
cursor.execute(f"UPDATE stock SET {', '.join(update_fields)} WHERE id = %s", update_values)
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('stockManage'))
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
stock_manager = request.args.get('stock_manager', '')
storage_location = request.args.get('storage_location', '')
product_status = request.args.get('product_status', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM stock
WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s
ORDER BY stock_update_date DESC
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM stock WHERE consignor LIKE %s AND product_type LIKE %s AND stock_manager LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + stock_manager + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('stockManage', page=page-1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page > 1 else None
next_url = url_for('stockManage', page=page+1, consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page < total_pages else None
return render_template('stockManage.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, stock_manager=stock_manager, storage_location=storage_location, product_status=product_status, progress_status=progress_status)
# 데이터 수정 및 삭제 라우트
@app.route('/save_changes', methods=['POST'])
def save_changes():
update_ids = request.form.getlist('update_id')
delete_ids = request.form.getlist('delete_id')
consignor = request.form.get('consignor', '')
product_type = request.form.get('product_type', '')
product_quantity = request.form.get('product_quantity', '')
arrival_date = request.form.get('arrival_date', '')
arrival_manager = request.form.get('arrival_manager', '')
storage_location = request.form.get('storage_location', '')
product_status = request.form.get('product_status', '')
progress_status = request.form.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor()
for update_id in update_ids:
update_fields = []
update_values = []
if consignor:
update_fields.append("consignor = %s")
update_values.append(consignor)
if product_type:
update_fields.append("product_type = %s")
update_values.append(product_type)
if product_quantity:
update_fields.append("product_quantity = %s")
update_values.append(product_quantity)
if arrival_date:
update_fields.append("arrival_date = %s")
update_values.append(arrival_date)
if arrival_manager:
update_fields.append("arrival_manager = %s")
update_values.append(arrival_manager)
if storage_location:
update_fields.append("storage_location = %s")
update_values.append(storage_location)
if product_status:
update_fields.append("product_status = %s")
update_values.append(product_status)
if progress_status:
update_fields.append("progress_status = %s")
update_values.append(progress_status)
update_values.append(update_id)
if update_fields:
cursor.execute(f"UPDATE incoming SET {', '.join(update_fields)} WHERE id = %s", update_values)
for delete_id in delete_ids:
cursor.execute("DELETE FROM incoming WHERE id = %s", (delete_id,))
connection.commit()
cursor.close()
connection.close()
return redirect(url_for('enterView'))
# 출고 조회 페이지 라우트
@app.route('/outboundView', methods=['GET'])
def outboundView():
update_stock_outbound()
page = request.args.get('page', 1, type=int)
per_page = 5
consignor = request.args.get('consignor', '')
product_type = request.args.get('product_type', '')
planned_date = request.args.get('planned_date', '')
storage_location = request.args.get('storage_location', '')
product_status = request.args.get('product_status', '')
progress_status = request.args.get('progress_status', '')
connection = create_connection()
cursor = connection.cursor(dictionary=True)
query = """
SELECT * FROM outbound_registration
WHERE consignor LIKE %s AND product_type LIKE %s AND planned_date LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s
LIMIT %s OFFSET %s
"""
cursor.execute(query, ('%' + consignor + '%', '%' + product_type + '%', '%' + planned_date + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%', per_page, (page-1)*per_page))
results = cursor.fetchall()
cursor.execute("SELECT COUNT(*) FROM outbound_registration WHERE consignor LIKE %s AND product_type LIKE %s AND planned_date LIKE %s AND storage_location LIKE %s AND product_status LIKE %s AND progress_status LIKE %s",
('%' + consignor + '%', '%' + product_type + '%', '%' + planned_date + '%', '%' + storage_location + '%', '%' + product_status + '%', '%' + progress_status + '%'))
total = cursor.fetchone()['COUNT(*)']
total_pages = (total + per_page - 1) // per_page
cursor.close()
connection.close()
prev_url = url_for('outboundView', page=page-1, consignor=consignor, product_type=product_type, planned_date=planned_date, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page > 1 else None
next_url = url_for('outboundView', page=page+1, consignor=consignor, product_type=product_type, planned_date=planned_date, storage_location=storage_location, product_status=product_status, progress_status=progress_status) if page < total_pages else None
return render_template('outboundView.html', results=results, total_pages=total_pages, current_page=page, prev_url=prev_url, next_url=next_url,
consignor=consignor, product_type=product_type, planned_date=planned_date, storage_location=storage_location, product_status=product_status, progress_status=progress_status)
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5001, debug=True)
연습문제 : 식자재 마트 관리 시스템을 만들기
- 식자재 마트에 필요한 데이터를 모델링 하세요(DB table, 입고, 재고, 출고, 재무관리)
- 위 내용에 맞는 가상 데이터를 pandas data frame으로 생성해 데이터를 유지하세요
- 위 내용에 맞는 웹서비스를 구축하세요.
- Main 화면, 입고화면, 재고화면, 출고화면, 재무관리화면
오늘은 WMS(Warehouse Management System) 구성 및 요구 사항 세 번째 수업으로 출고 조회 및 관리 시스템을 구현해 봤다. 입고 조회 및 관리랑 코드가 비슷하지만 재고 있는 수량을 수정하는 기능이 추가되었다. 수정하는 코드 하나 추가 되었을 뿐인데 오늘 혼자 코드를 작성을 시도했지만 작성하지 못했다. 조금만 코드가 달라도 너무 헷갈린다. 계속 코드를 작성하면서 연습해야 될 것 같다.