main.py

from flask import Flask, request, jsonify, render_template
from flask_login import LoginManager
from app.routes import routes
from app.models import db, Apoteker
import pyodbc

def create_app():
    app = Flask(__name__, template_folder='app/templates', static_folder='app/static')
    app.config['SECRET_KEY'] = 'your-secret-key'
    
    # SQL Server connection string for SQLAlchemy
    app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pyodbc:///?odbc_connect=' + (
        'DRIVER={ODBC Driver 17 for SQL Server};'
        'SERVER=(local);'
        'DATABASE=Apotek;'
        'Trusted_Connection=yes;'
    )
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    
    # Initialize extensions
    db.init_app(app)
    login_manager = LoginManager()
    login_manager.init_app(app)
    login_manager.login_view = 'routes.login'

    @login_manager.user_loader
    def load_user(user_id):
        return Apoteker.query.get(user_id)

    # Register the routes blueprint
    app.register_blueprint(routes)

    # Add error handler for 404
    @app.errorhandler(404)
    def page_not_found(e):
        return render_template('404notfound.html'), 404

    @app.route('/add_pharmacist', methods=['POST'])
    def add_pharmacist():
        try:
            data = request.get_json()
            
            # Extract data from JSON
            id_apoteker = data['id_apoteker']
            nama_apoteker = data['nama_apoteker']
            lisensi_apoteker = data['lisensi_apoteker']
            no_telp_apoteker = data['no_telp_apoteker']
            shift_apoteker = data['shift_apoteker']
            jenis_kelamin_apoteker = data['jenis_kelamin_apoteker']
            tgl_lahir_apoteker = data['tgl_lahir_apoteker']
            alamat_apoteker = data['alamat_apoteker']
            tgl_diterima_kerja = data['tgl_diterima_kerja']
            password = data['password']
            medicines = data['medicines']

            # Connect to database using Windows Authentication
            conn_str = (
                'DRIVER={ODBC Driver 17 for SQL Server};'
                'SERVER=(local);'
                'DATABASE=Apotek;'
                'Trusted_Connection=yes;'
            )
            conn = pyodbc.connect(conn_str)
            cursor = conn.cursor()

            # Add to database
            cursor.execute("""
                INSERT INTO Apoteker (
                    id_apoteker, nama_apoteker, lisensi_apoteker, 
                    no_telp_apoteker, shift_apoteker, jenis_kelamin_apoteker,
                    tgl_lahir_apoteker, alamat_apoteker, tgl_diterima_kerja, password
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                id_apoteker, nama_apoteker, lisensi_apoteker,
                no_telp_apoteker, shift_apoteker, jenis_kelamin_apoteker,
                tgl_lahir_apoteker, alamat_apoteker, tgl_diterima_kerja, password
            ))

            # Add medicine handling permissions
            for medicine_id in medicines:
                cursor.execute("""
                    INSERT INTO Menangani (id_apoteker, id_obat)
                    VALUES (?, ?)
                """, (id_apoteker, medicine_id))

            conn.commit()
            cursor.close()
            conn.close()

            return jsonify({'success': True, 'message': 'Data apoteker berhasil ditambahkan'})
        except Exception as e:
            return jsonify({'success': False, 'message': str(e)})

    @app.route('/edit_pharmacist', methods=['POST'])
    def edit_pharmacist():
        try:
            data = request.get_json()
            
            # Extract data from JSON
            id_apoteker = data['id_apoteker']
            nama_apoteker = data['nama_apoteker']
            lisensi_apoteker = data['lisensi_apoteker']
            no_telp_apoteker = data['no_telp_apoteker']
            shift_apoteker = data['shift_apoteker']
            jenis_kelamin_apoteker = data['jenis_kelamin_apoteker']
            tgl_lahir_apoteker = data['tgl_lahir_apoteker']
            alamat_apoteker = data['alamat_apoteker']
            tgl_diterima_kerja = data['tgl_diterima_kerja']
            password = data['password']
            medicines = data['medicines']

            # Connect to database using Windows Authentication
            conn_str = (
                'DRIVER={ODBC Driver 17 for SQL Server};'
                'SERVER=(local);'
                'DATABASE=Apotek;'
                'Trusted_Connection=yes;'
            )
            conn = pyodbc.connect(conn_str)
            cursor = conn.cursor()
            
            # Update apoteker data
            cursor.execute("""
                UPDATE Apoteker SET
                    nama_apoteker = ?,
                    lisensi_apoteker = ?,
                    no_telp_apoteker = ?,
                    shift_apoteker = ?,
                    jenis_kelamin_apoteker = ?,
                    tgl_lahir_apoteker = ?,
                    alamat_apoteker = ?,
                    tgl_diterima_kerja = ?,
                    password = ?
                WHERE id_apoteker = ?
            """, (
                nama_apoteker, lisensi_apoteker, no_telp_apoteker,
                shift_apoteker, jenis_kelamin_apoteker, tgl_lahir_apoteker,
                alamat_apoteker, tgl_diterima_kerja, password, id_apoteker
            ))

            # Update medicine handling permissions
            cursor.execute("DELETE FROM Menangani WHERE id_apoteker = ?", (id_apoteker,))
            for medicine_id in medicines:
                cursor.execute("""
                    INSERT INTO Menangani (id_apoteker, id_obat)
                    VALUES (?, ?)
                """, (id_apoteker, medicine_id))

            conn.commit()
            cursor.close()
            conn.close()

            return jsonify({'success': True, 'message': 'Data apoteker berhasil diperbarui'})
        except Exception as e:
            return jsonify({'success': False, 'message': str(e)})

    @app.route('/add_obat', methods=['POST'])
    def add_obat():
        try:
            data = request.get_json()
            
            # Validate required fields
            required_fields = ['id_obat', 'nama_obat', 'kategori', 'stok', 'harga', 
                             'supplier', 'tgl_kadaluarsa', 'kemasan']
            
            for field in required_fields:
                if not data.get(field):
                    return jsonify({
                        'success': False,
                        'message': f'Field {field} harus diisi'
                    })

            # Validate numeric fields
            try:
                stok = int(data['stok'])
                harga = float(data['harga'])
                if stok < 0 or harga < 0:
                    raise ValueError
            except ValueError:
                return jsonify({
                    'success': False,
                    'message': 'Stok dan harga harus berupa angka positif'
                })

            # Connect to database
            conn_str = (
                'DRIVER={ODBC Driver 17 for SQL Server};'
                'SERVER=(local);'
                'DATABASE=Apotek;'
                'Trusted_Connection=yes;'
            )
            conn = pyodbc.connect(conn_str)
            cursor = conn.cursor()

            try:
                # Start transaction
                cursor.execute("BEGIN TRANSACTION")

                # Check if medicine ID already exists
                cursor.execute("SELECT id_obat FROM Obat WHERE id_obat = ?", (data['id_obat'],))
                if cursor.fetchone():
                    raise Exception("ID Obat sudah ada")

                # Insert medicine data
                cursor.execute("""
                    INSERT INTO Obat (
                        id_obat, nama_obat, kategori_obat, 
                        stok_obat, harga_obat, id_supplier,
                        tgl_kedaluarsa, kemasan
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    data['id_obat'], data['nama_obat'], data['kategori'],
                    data['stok'], data['harga'], data['supplier'],
                    data['tgl_kadaluarsa'], data['kemasan']
                ))

                # Commit transaction
                cursor.execute("COMMIT TRANSACTION")
                conn.commit()

                return jsonify({
                    'success': True,
                    'message': 'Data obat berhasil ditambahkan'
                })

            except Exception as e:
                # Rollback on error
                cursor.execute("ROLLBACK TRANSACTION")
                conn.rollback()
                raise e

            finally:
                cursor.close()
                conn.close()

        except Exception as e:
            print('Error:', str(e))
            return jsonify({
                'success': False,
                'message': str(e)
            })

    @app.route('/edit_transaction', methods=['POST'])
    def edit_transaction():
        try:
            data = request.get_json()
            
            # Extract data
            id_transaksi = data['id_transaksi']
            waktu_transaksi = data['waktu_transaksi']
            id_pelanggan = data['id_pelanggan']
            medicines = data['medicines']

            # Validate required fields
            if not all([id_transaksi, waktu_transaksi, id_pelanggan, medicines]):
                return jsonify({
                    'success': False,
                    'message': 'Semua field harus diisi'
                })

            # Connect to database
            conn_str = (
                'DRIVER={ODBC Driver 17 for SQL Server};'
                'SERVER=(local);'
                'DATABASE=Apotek;'
                'Trusted_Connection=yes;'
            )
            conn = pyodbc.connect(conn_str)
            cursor = conn.cursor()

            try:
                # Start transaction
                cursor.execute("BEGIN TRANSACTION")

                # First, get the current medicine quantities to restore stock
                cursor.execute("""
                    SELECT id_obat, kuantitas_obat 
                    FROM Detail_transaksi 
                    WHERE id_transaksi = ?
                """, (id_transaksi,))
                
                old_medicines = cursor.fetchall()
                
                # Restore old quantities back to stock
                for old_med in old_medicines:
                    cursor.execute("""
                        UPDATE Obat 
                        SET stok_obat = stok_obat + ?
                        WHERE id_obat = ?
                    """, (old_med[1], old_med[0]))

                # Update transaction header
                cursor.execute("""
                    UPDATE Transaksi 
                    SET waktu_transaksi = ?,
                        id_pelanggan = ?
                    WHERE id_transaksi = ?
                """, (waktu_transaksi, id_pelanggan, id_transaksi))

                # Calculate total price
                total_price = 0

                # Delete existing details
                cursor.execute("""
                    DELETE FROM Detail_transaksi 
                    WHERE id_transaksi = ?
                """, (id_transaksi,))

                # Insert new details and update stock
                for medicine in medicines:
                    # Validate medicine exists and has enough stock
                    cursor.execute("""
                        SELECT stok_obat, harga_obat 
                        FROM Obat 
                        WHERE id_obat = ?
                    """, (medicine['id_obat'],))
                    
                    result = cursor.fetchone()
                    if not result:
                        raise Exception(f"Obat dengan ID {medicine['id_obat']} tidak ditemukan")
                    
                    current_stock, harga_obat = result
                    requested_qty = int(medicine['kuantitas'])
                    
                    if requested_qty > current_stock:
                        raise Exception(f"Stok tidak mencukupi untuk obat {medicine['id_obat']}")

                    # Add to total price
                    total_price += float(harga_obat) * requested_qty

                    # Insert new detail
                    cursor.execute("""
                        INSERT INTO Detail_transaksi (
                            id_transaksi, id_obat, kuantitas_obat
                        ) VALUES (?, ?, ?)
                    """, (
                        id_transaksi, medicine['id_obat'], requested_qty
                    ))

                    # Update stock
                    cursor.execute("""
                        UPDATE Obat 
                        SET stok_obat = stok_obat - ?
                        WHERE id_obat = ?
                    """, (requested_qty, medicine['id_obat']))

                # Update total price in transaction
                cursor.execute("""
                    UPDATE Transaksi 
                    SET total_harga = ?
                    WHERE id_transaksi = ?
                """, (total_price, id_transaksi))

                # Commit transaction
                cursor.execute("COMMIT TRANSACTION")
                conn.commit()

                return jsonify({
                    'success': True,
                    'message': 'Transaksi berhasil diperbarui'
                })

            except Exception as e:
                # Rollback on error
                cursor.execute("ROLLBACK TRANSACTION")
                conn.rollback()
                raise e

            finally:
                cursor.close()
                conn.close()

        except Exception as e:
            print('Error:', str(e))
            return jsonify({
                'success': False,
                'message': str(e)
            })

    return app

if __name__ == '__main__':
    app = create_app()
    app.run(debug=True, port=5000)