مینی پروژه استخراج اسامی از دیتابیس با پایتون

موضوع تسک: با داشتن یک فایل خام استخراج شده از دستگاه تردد uFace202 میخواهیم لیست نفرات ورود شده را در ابتدای روز کاری داشته باشیم (بدانیم از بین ۳۰۰ نفر پرسنل چه تعداد امروز صبح وارد شرکت شده اند)

در محل کار از یک دستگاه ثبت تردد uFace202 شرکت دنیای پردازش استفاده میکنیم که در حال حاضر با توجه به محدودیت های فعلی به صورت تحت شبکه نیست و نیاز است روزانه از طریق USB در ابتدای هر روز ورود های ثبت شده را استخراج کنیم.

محدودیت ها و موانع:

• نداشتن سرور مرکزی جهت راه اندازی نرم افزار تحت وب و ارتباط با دستگاه تردد
• اشکال در ارتباط با دستگاه تردد از طریق شبکه به صورت مستقیم
• محدودیت قفل سخت افزاری جهت استفاده از نرم افزار های شرکت دنیای پردازش در رایانه های متفاوت
• تعداد بالای نفرات روزانه که صرفا باید از طریق دستگاه کنترل گردد

خواسته ها:

• ساخت یک اسکریپت جهت پیدا کردن نام پرسنل ورود شده از طریق اطلاعات خام استخراج شده از دستگاه تردد به صورت portable و قابلیت اجرا روی هر سیستم رایانه ای بدون هیچگونه پیش نیاز نرم افزاری (به دلیل محدودیت سیستم ها که از سمت واحد IT سازمان اعمال شده)
  • اجرای یک فایل اجرایی با پسوند exe
  • باز شدن پنجره ای برای انتخاب فایل خام
  • پردازش و در نهایت ایجاد فایل حاوی اسامی

اطلاعات تکمیلی:

مشخصات مربوط به پرسنل شامل نام و نام خانوادگی و مدرک تحصیلی و … از طریق نرم افزار تحت ویندوز (PwKara) مخصوص ارتباط با دستگاه ثبت و در دیتابیس MS SQL server ذخیره میگردد.

توضیحات در خصوص نرم افزار های مرتبط با دستگاه:

نرم افزار PwNet دنیای پردازش: جهت اتصال به دستگاه تردد از طریق شبکه و یا USB و استخراج اطلاعات ثبت تردد و ذخیره در دیتابیس ایجاد شده در MS SQL server (به صورت local روی ویندوز)

نرم افزار PwKara دنیای پردازش: از طریق این نرم افزار کلیه ارتباطات با بانک اطلاعاتی مورد نظر برقرار میگردد و در واقع یک Interface جهت عملیات CRUD میباشد. عملیاتی مانند ثبت اطلاعات پرسنل، تخصیص کد تعریف شده در دستگاه برای پرسنل، مشاهده یا ویرایش رکورد ها و …

سوالات متداول:

• نرم افزار PwKara قادر به ارائه نفرات وارد شده در لحظه نیست؟
  • بله. اما دلیل عدم استفاده از این قابلیت آن است که سایر افراد صرفا با داشتن فایل خام تردد اسامی را مشاهده کنند و به لحاظ امنیتی و به دلیل حساس بودن موضوع به سیستم admin و نرم افزار های مربوطه دسترسی نداشته باشند.
• آیا در آینده شرایط تغییر میکند؟
  • بله. با راه اندازی سرور مرکزی در آینده احتمالا با ایجاد دسترسی های محدود بتوان خدمات مورد نظر را ارائه داد.

خروجی دستگاه یک فایل با پسوند Dat میباشد که حاوی کدهای زیر است (هر خط مربوط به ثبت تردد یک شخص میباشد):

 168 1403-03-13 02:28:21 70 0 15 0
971096 1403-03-13 06:00:25 70 0 1 0
166 1403-03-13 06:19:10 70 0 15 0
993135 1403-03-13 06:24:37 70 0 15 0
14020286 1403-03-13 06:25:00 70 0 15 0

این اطلاعات خام شامل کد پرسنلی و ساعت و تاریخ و … میباشد و فاقد اسامی پرسنل بوده چرا که دستگاه قابلیت اضافه کردن اسامی به صورت فارسی در حافظه را ندارد.

خلاصه اطلاعاتی که تا کنون داریم:

• فایل دیتای ثبت شده در دستگاه تردد uFace202 و استخراج آن به USB
• بانک اطلاعاتی MSSQL Server شامل اطلاعات پرسنل
• یک فایل csv دیتابیس شامل کد و نام کلیه پرسنل

اکنون با استخراج کلیه نام و کد پرسنلی از دیتابیس و ذخیره در یک فایل csv با پایتون تسک مورد نظر را انجام میدهیم.

انجام تسک

توضیح حل مسئله ذکر شده با پایتون

1. باز شدن پنجره انتخاب فایل و باز کردن فایل خروجی دستگاه تردد با پسوند Dat
2. تبدیل فایل انتخاب شده به فایل متنی با پسوند txt
3. خواندن فایل متنی
4. حذف فاصله های white space در ابتدا و انتهای خطوط فایل متنی از طریق تابع strip()
5. حذف تمامی کاراکتر های بعد از کد پرسنلی با تابع re.sub()
6. اضافه کردن خطوط پاکسازی شده به یک لیست جدید
7. باز کردن دیتابیس csv مشخصات پرسنل
8. match کردن هر کد پرسنلی با دیتابیس و پیدا کردن نام مرتبط
9. ذخیره اسامی پیدا شده در یک فایل متنی و اکسل
10. تغییر نام فایل های ساخته شده به تاریخ روز به صورت شمسی (تبدیل تاریخ میلادی به شمسی)
11. تبدیل فایل پایتون به فایل اجرایی ویندوز با پسوند exe جهت اجرا روی سایر رایانه ها از طریق کتابخانه auto-py-to-exe

کد پایتون
(برای هر قدم کامنت واضح نوشته شده که هر قطعه از کد چه کاری انجام میدهد)

import re
from pathlib import Path
import pathlib
import pandas as pd
import os
import shutil
from tkinter.filedialog import askopenfilename
import xlsxwriter
from datetime import datetime

#Function: remove temporary txt and Dat files to cleanup folder
def file_cleaner():
dir_name = pathlib.Path().resolve()
#dir_name = "/Users/John/downloads/"
get_file_in_directory = os.listdir(dir_name)
for item in get_file_in_directory:
if item.endswith(".txt"):
if(item != "final_result.txt"):
os.remove(os.path.join(dir_name, item))
elif item.endswith(".dat"):
os.remove(os.path.join(dir_name, item))

#Function: gonvert Gregorian date to Jalali (Shamsi)
def gregorian_to_jalali(gy, gm, gd):
g_d_m = [0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334]
if (gm > 2):
gy2 = gy + 1
else:
gy2 = gy
days =355666+(365*gy)+((gy2+3)//4)-((gy2+99)//100)+((gy2+399)//400)+gd+g_d_m[gm-1]
jy = -1595 + (33 * (days // 12053))
days %= 12053
jy += 4 * (days // 1461)
days %= 1461
if (days > 365):
jy += (days - 1) // 365
days = (days - 1) % 365
if (days < 186):
jm = 1 + (days // 31)
jd = 1 + (days % 31)
else:
jm = 7 + ((days - 186) // 30)
jd = 1 + ((days - 186) % 30)
return [jy, jm, jd]


#Convert Today Gregorian date to Jalali
year = datetime.today().strftime('%Y')
month = datetime.today().strftime('%m')
day = datetime.today().strftime('%d')
jalali_date = gregorian_to_jalali(int(year), int(month), int(day))
jalali_date = str(jalali_date[0]) + "-" + str(jalali_date[1]) + "-" + str(jalali_date[2])
print(f"Today: {jalali_date}")

#Clean folder from mass and unwanted files such as files created before
file_cleaner()

# Open a dialog window and select a file
filename = askopenfilename()

# Extract the base name of the file (i.e., the file name without the path)
file_name = os.path.basename(filename)

# Define the destination path (root of the Python project)
destination_path = os.path.join(os.getcwd(), file_name)

# Copy the file to the destination path
shutil.copy(filename, destination_path)

 

p = Path(file_name)
#it means: p = Path('3574203200056_attlog.dat')

# Convert Dat file to Txt file
# .Dat file is the file we Exported from attendance device
p = p.rename(p.with_suffix('.txt'))

# get all string lines from file
with open(p, 'r') as f:
lines = f.readlines()

new_lines = []

# remove unrelated characters and clean string to get the employee number
for line in lines:
line = re.sub(r'1403-.*', '', line)
line = line.strip()
new_lines.append(line + "\n")

 

cleaned_numbers = [line.strip() for line in new_lines]

# Load the Database CSV file that include all employee names and codes
file_path = 'database.csv'
df = pd.read_csv(file_path, header=None, names=['Number', 'Name'])

 

# Search for the name with the each number we have in cleaned lines to confirm registered codes.
'''
This piece of code is performing a lookup operation to match employee numbers
 withtheir corresponding names using the database you loaded earlier.
 Here's a breakdown of what it does:

1. It initializes an empty list called finalResult to store the results.
2. It iterates through each line in the cleaned_numbers list, which presumably
 contains employee numbers extracted from the attendance log.
3. For each number:
- It strips any leading or trailing whitespace from the line.
- It searches the df DataFrame (which was loaded from 'database.csv')
 for a row where the 'Number' column matches the current line (converted to an integer).
- The matching row (or an empty DataFrame if no match is found)
 is stored in the temp variable.
- This temp result is then appended to the finalResult list.

The purpose of this code is to take the list of employee numbers from the attendance log
 and find the corresponding employee names from your database.
It's essentially translating the attendance log numbers into actual employee names.
'''

finalResult = []
for line in cleaned_numbers:
line = line.strip()
# Try to find the matching row
temp = df[df['Number'] == int(line)]
try:
# print names found
# print(temp['Name'].values[0])
# append name found in db to our temporary list > finalResult[]
finalResult.append(temp['Name'].values[0])
except:
print(f"Error | Code not exist in database: {line}")

# Get the count of results
count = len(finalResult)

# Prepare the content for the output file
output_content = f"{count}\n" + "\n".join(finalResult)

# Write the output content to a text file
output_path = 'final_result.txt'
with open(output_path, 'w',encoding='utf-8') as f:
f.write(output_content)


# write into an excel file
xbook = xlsxwriter.Workbook(jalali_date + ".xlsx")
xsheet = xbook.add_worksheet('stats')

for idx, month in enumerate(finalResult):
xsheet.write(idx,0,finalResult[idx])

xbook.close()

print(f"{count} Final results have been written to {output_path}")
file_cleaner()

os.rename('final_result.txt', jalali_date + ".txt")