{
"cells": [
{
"cell_type": "code",
<<<<<<< Updated upstream
"execution_count": 12,
=======
"execution_count": 1,
>>>>>>> Stashed changes
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Connected to company.db\n"
]
}
],
"source": [
"import sqlite3\n",
"\n",
"# Connect to the database\n",
"conn = sqlite3.connect('company.db')\n",
"\n",
"# Create a cursor object\n",
"cursor = conn.cursor()\n",
"\n",
<<<<<<< Updated upstream
"print(\"Connected to company.db\")\n"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"from datetime import datetime, timedelta\n",
"\n",
"\n",
"\n",
"chrismas_day = datetime(2024, 12, 25)\n",
"cursor.execute(\"SELECT NUMERO_EMPLOYE, NOM, PRENOM, CS_PRODUCTION,CS_JOURNEE, DATE_DEBUT FROM EMPLOYE\")\n",
"employees = cursor.fetchall()\n",
"# Create a list to store the data\n",
"prime_totale = []\n",
"prime_partielle=[]\n",
"\n",
"\n",
"def generate_mondays(start_date):\n",
" \"\"\"\n",
" Génère une liste de lundis à partir d'une date donnée jusqu'à un an après.\n",
" \n",
" :param start_date: Date de début au format 'YYYY-MM-DD'\n",
" :return: Liste de lundis au format 'YYYY-MM-DD'\n",
" \"\"\"\n",
" # Convertir la date de départ en objet datetime\n",
" start = datetime.strptime(start_date, '%Y-%m-%d')\n",
" end_date = start + timedelta(days=365)\n",
" \n",
" # Trouver le premier lundi (si la date donnée n'est pas un lundi)\n",
" if start.weekday() != 0: # Si ce n'est pas un lundi\n",
" start += timedelta(days=(7 - start.weekday())) # Aller au prochain lundi\n",
" \n",
" # Générer les lundis\n",
" mondays = []\n",
" current_date = start\n",
" while current_date <= end_date:\n",
" mondays.append(current_date.strftime('%Y-%m-%d'))\n",
" current_date += timedelta(weeks=1) # Passer au lundi suivant\n",
" \n",
" return mondays\n",
"\n",
"mondays=generate_mondays('2024-12-04')\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Connected to company.db\n"
]
}
],
"source": [
"import sqlite3\n",
"import pandas as pd\n",
"from datetime import datetime, timedelta\n",
"\n",
"# Fonction pour générer les lundis\n",
"def generate_mondays(start_date):\n",
" \"\"\"\n",
" Génère une liste de lundis à partir d'une date donnée jusqu'à un an après.\n",
" \n",
" :param start_date: Date de début au format 'YYYY-MM-DD'\n",
" :return: Liste de lundis au format 'YYYY-MM-DD'\n",
" \"\"\"\n",
" start = datetime.strptime(start_date, '%Y-%m-%d')\n",
" end_date = start + timedelta(days=365)\n",
" \n",
" # Ajuster au premier lundi\n",
" if start.weekday() != 0:\n",
" start += timedelta(days=(7 - start.weekday()))\n",
" \n",
" mondays = []\n",
" current_date = start\n",
" while current_date <= end_date:\n",
" mondays.append(current_date.strftime('%Y-%m-%d'))\n",
" current_date += timedelta(weeks=1)\n",
" \n",
" return mondays\n",
"\n",
"# Utilisation de SQLite\n",
"try:\n",
" # Connexion à la base de données\n",
" conn = sqlite3.connect('company.db')\n",
" cursor = conn.cursor()\n",
" print(\"Connected to company.db\")\n",
" \n",
" # Génération des lundis\n",
" mondays = generate_mondays(datetime.now().strftime('%Y-%m-%d'))\n",
" \n",
" # Exemple d'opérations avec les employés\n",
" chrismas_day = datetime(2024, 12, 25)\n",
" cursor.execute(\"SELECT NUMERO_EMPLOYE, NOM, PRENOM, CS_PRODUCTION, CS_JOURNEE, DATE_DEBUT FROM EMPLOYE\")\n",
" employees = cursor.fetchall()\n",
" \n",
" prime_totale = []\n",
" prime_partielle = []\n",
" \n",
" Bonus=[]\n",
" \n",
" \n",
" for employee in employees:\n",
" NUMERO_EMPLOYE, NOM, PRENOM, CS_PRODUCTION, CS_JOURNEE, DATE_DEBUT = employee\n",
" start_date = datetime.strptime(DATE_DEBUT, '%Y-%m-%d')\n",
" \n",
" \n",
" for LUNDI in mondays:\n",
" lundi = datetime.strptime(LUNDI, '%Y-%m-%d')\n",
" delta = lundi - start_date\n",
" delta_days = delta.days\n",
" delta_noel =lundi - chrismas_day\n",
" delta_noel_days=delta_noel.days\n",
" prime_noel=0\n",
" prime_anniversaire=0\n",
"\n",
" if 0 <= delta_noel_days%365<= 6: \n",
" if delta.days >= 365:\n",
" if CS_JOURNEE is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_HORAIRE WHERE CS_HORAIRE = {CS_JOURNEE}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_noel= salaire_base*4.5\n",
" if CS_PRODUCTION is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_PROD WHERE CS_PRODUCTION = {CS_PRODUCTION}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_noel= salaire_base*4.5\n",
" else:\n",
" if CS_JOURNEE is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_HORAIRE WHERE CS_HORAIRE = {CS_JOURNEE}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_noel= max(0, salaire_base * delta.days / 365 )\n",
" if CS_PRODUCTION is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_PROD WHERE CS_PRODUCTION = {CS_PRODUCTION}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_noel=max(0, salaire_base*100 * delta.days / 365 )\n",
" \n",
" if 0 <= abs(delta_days%365)<= 6: \n",
" if CS_JOURNEE is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_HORAIRE WHERE CS_HORAIRE = {CS_JOURNEE}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_anniversaire= salaire_base\n",
" if CS_PRODUCTION is not None:\n",
" cursor.execute(f\"SELECT SALAIRE_SEMAINE_BASE FROM GRILLE_SALAIRE_PROD WHERE CS_PRODUCTION = {CS_PRODUCTION}\")\n",
" salaire_base=cursor.fetchone()[0]\n",
" prime_anniversaire= salaire_base\n",
" Bonus.append((NUMERO_EMPLOYE, NOM, PRENOM, DATE_DEBUT, (datetime.strptime(LUNDI, \"%Y-%m-%d\") - timedelta(days=7)), prime_noel,prime_anniversaire))\n",
"\n",
"\n",
"\n",
" # Sauvegarde des lundis dans une table de la base\n",
" # cursor.executemany(\"INSERT OR IGNORE INTO LUNDIS (date_lundi) VALUES (?)\", [(monday,) for monday in mondays])\n",
" # conn.commit()\n",
" # pri<nt(\"Mondays added to the database.\")\n",
" \n",
"except sqlite3.Error as e:\n",
" print(f\"SQLite error: {e}\")\n",
"\n"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>NUMERO_EMPLOYE</th>\n",
" <th>NOM</th>\n",
" <th>PRENOM</th>\n",
" <th>DATE_DEBUT</th>\n",
" <th>LUNDI</th>\n",
" <th>PRIMES</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>1</td>\n",
" <td>Dubois</td>\n",
" <td>Michel</td>\n",
" <td>2020-11-13</td>\n",
" <td>2024-12-23</td>\n",
" <td>2160.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>49</th>\n",
" <td>1</td>\n",
" <td>Dubois</td>\n",
" <td>Michel</td>\n",
" <td>2020-11-13</td>\n",
" <td>2025-11-10</td>\n",
" <td>480.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>55</th>\n",
" <td>2</td>\n",
" <td>Bouchaira</td>\n",
" <td>Neirouz</td>\n",
" <td>2021-11-13</td>\n",
" <td>2024-12-23</td>\n",
" <td>2160.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>101</th>\n",
" <td>2</td>\n",
" <td>Bouchaira</td>\n",
" <td>Neirouz</td>\n",
" <td>2021-11-13</td>\n",
" <td>2025-11-10</td>\n",
" <td>480.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>107</th>\n",
" <td>3</td>\n",
" <td>Caty</td>\n",
" <td>Jeanne</td>\n",
" <td>2019-11-03</td>\n",
" <td>2024-12-23</td>\n",
" <td>3712.50000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>151</th>\n",
" <td>3</td>\n",
" <td>Caty</td>\n",
" <td>Jeanne</td>\n",
" <td>2019-11-03</td>\n",
" <td>2025-10-27</td>\n",
" <td>825.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>159</th>\n",
" <td>5</td>\n",
" <td>Mourin</td>\n",
" <td>Julie</td>\n",
" <td>2023-11-13</td>\n",
" <td>2024-12-23</td>\n",
" <td>2126.25000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>205</th>\n",
" <td>5</td>\n",
" <td>Mourin</td>\n",
" <td>Julie</td>\n",
" <td>2023-11-13</td>\n",
" <td>2025-11-10</td>\n",
" <td>472.50000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>211</th>\n",
" <td>6</td>\n",
" <td>Bowers</td>\n",
" <td>Matthew</td>\n",
" <td>2024-11-20</td>\n",
" <td>2024-12-23</td>\n",
" <td>52.60274</td>\n",
" </tr>\n",
" <tr>\n",
" <th>258</th>\n",
" <td>6</td>\n",
" <td>Bowers</td>\n",
" <td>Matthew</td>\n",
" <td>2024-11-20</td>\n",
" <td>2025-11-17</td>\n",
" <td>480.00000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>263</th>\n",
" <td>7</td>\n",
" <td>Jura</td>\n",
" <td>Charlie</td>\n",
" <td>2022-11-28</td>\n",
" <td>2024-12-23</td>\n",
" <td>3712.50000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>311</th>\n",
" <td>7</td>\n",
" <td>Jura</td>\n",
" <td>Charlie</td>\n",
" <td>2022-11-28</td>\n",
" <td>2025-11-24</td>\n",
" <td>825.00000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" NUMERO_EMPLOYE NOM PRENOM DATE_DEBUT LUNDI PRIMES\n",
"3 1 Dubois Michel 2020-11-13 2024-12-23 2160.00000\n",
"49 1 Dubois Michel 2020-11-13 2025-11-10 480.00000\n",
"55 2 Bouchaira Neirouz 2021-11-13 2024-12-23 2160.00000\n",
"101 2 Bouchaira Neirouz 2021-11-13 2025-11-10 480.00000\n",
"107 3 Caty Jeanne 2019-11-03 2024-12-23 3712.50000\n",
"151 3 Caty Jeanne 2019-11-03 2025-10-27 825.00000\n",
"159 5 Mourin Julie 2023-11-13 2024-12-23 2126.25000\n",
"205 5 Mourin Julie 2023-11-13 2025-11-10 472.50000\n",
"211 6 Bowers Matthew 2024-11-20 2024-12-23 52.60274\n",
"258 6 Bowers Matthew 2024-11-20 2025-11-17 480.00000\n",
"263 7 Jura Charlie 2022-11-28 2024-12-23 3712.50000\n",
"311 7 Jura Charlie 2022-11-28 2025-11-24 825.00000"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"Bonus = pd.DataFrame(Bonus, columns=['NUMERO_EMPLOYE','NOM', 'PRENOM' ,'DATE_DEBUT', 'LUNDI','PRIMES'])\n",
"Bonus[Bonus['PRIMES']>0].head(50)"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'2024-12-07'"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"datetime.now().strftime('%Y-%m-%d')\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
=======
"print(\"Connected to company.db\")"
]
>>>>>>> Stashed changes
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.13.0"
}
},
"nbformat": 4,
<<<<<<< Updated upstream
"nbformat_minor": 4
=======
"nbformat_minor": 2
>>>>>>> Stashed changes
}