erpnext_sql.py

import pyodbc
import requests
from datetime import datetime

# SQL Server Connection Details
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};'
    'SERVER=192.168.1.110\HRMSERVER;DATABASE=Hikvision;UID=sa;PWD=sa@12345'
)

# Create cursor
cursor = conn.cursor()

# ERPNext API details
ERPNEXT_API_KEY = '44453e3edfdf94f6ce'
ERPNEXT_API_SECRET = '98c1d44ddfdfeer88'
ERPNEXT_URL = 'http://192.168.1.222:8000/api/method/hrms.hr.doctype.employee_checkin.employee_checkin.add_log_based_on_employee_field'

headers = {
    "Authorization": f"token {ERPNEXT_API_KEY}:{ERPNEXT_API_SECRET}",
    "Content-Type": "application/json"
}

# Fixed latitude and longitude
latitude = 31.287604
longitude = 74.169660

# Fetch data from SQL Server
cursor.execute("""
    SELECT employeeID, authDateTime, direction, deviceName
    FROM [Hikvision].[dbo].[attlog]
    ORDER BY employeeID, authDateTime
""")

last_employee = None
log_type = 'IN'  # Initial log type

# Loop through check-in logs and send data to ERPNext Employee Checkin
for row in cursor.fetchall():
    employee_id = row[0]
    timestamp_str = row[1].strftime('%Y-%m-%d %H:%M:%S')

    # Switch between IN and OUT based on employee
    if employee_id != last_employee:
        log_type = 'IN'  # First log is IN
    else:
        log_type = 'OUT'  # Second log is OUT

    data = {
        "employee_field_value": employee_id,  # Employee's Biometric ID
        "timestamp": timestamp_str,  # Log time as string
        "device_id": row[3],  # Device IP or name (deviceName)
        "log_type": log_type,  # IN or OUT
        "latitude": latitude,  # Fixed latitude
        "longitude": longitude,  # Fixed longitude
        "fetch_geolocation": 0,  # Set to 0, since you are using fixed coordinates
        "skip_auto_attendance": 0,  # Skip auto attendance
        "employee": employee_id,  # Employee ID
        "employee_name": row[0]  # Assuming employee_name is stored in employeeID
    }

    # Send data to ERPNext Employee Checkin
    response = requests.post(ERPNEXT_URL, json=data, headers=headers)

    # Print response
    print(f"Employee {row[0]} log: {log_type}, {response.status_code}, {response.text}")

    # Alternate between IN and OUT for the same employee
    last_employee = employee_id

conn.close()