Ali's Photography Space…

In this part we all do the following:

• Define the tables and the fields.(initially)
• Write the code to create the Data Base.
• Write the code to insert the data in selected tables.
• write the functions names.(initially)
• Create the main menu.
• Create the menu for the lookups tables.

Define the Tables and the fields
After spending some time thinking about the project, we can figure out some out-line about most important tables and fields that we may need in this project. So here are what I will use and we may need to add or alter during the project.

Tables are not in any order ..

staff : st_id, name, mobile, email, position(manager, )
ticket: t_id, dep_id, date(today), asignto(st_id), ndescription, priority_id(High, Medium, Low), Status_id(Open, In Progress, Resolved, Closed)
updated_date, category
category: cat_id, cat_name
t_notes: tn_id, note
priority: pr_id, pri_level(High, Medium, Low)
Status: stat_id, stat(Open, In Progress, Resolved, Closed)

staff_job: sj_id, jobid, recived, finishd, status, note

departments: dep_id, dep_name

Create the Data Base
We will use the SQlite3 to create the database and the tables.
We will write the codes to create the table, and insert a Zero record, and in Lookups tables we will insert some more data such as in priority table we will insert [High, Medium, Low] in the priority level field.

#Code to create the status table.
sql_Ticket_status_t = “CREATE TABLE if not exists status_t (st_id INTEGER PRIMARY KEY AUTOINCREMENT, st_name text )”
c.execute(sql_Ticket_status_t)
db_conn.commit()

# Inserting ZERO records.
c.execute (“INSERT INTO status_t (st_name) VALUES(:st_name )”,{“st_name”:’0′})
db_conn.commit()

# Inserting Basic Information.
Ticket_status_list = [‘Open’, ‘In Progress’, ‘Resolved’, ‘Closed’]
for each in Ticket_status_list:
c.execute (“INSERT INTO status_t (st_name) VALUES(:st_name )”,{“st_name”:each})
db_conn.commit()

#Code to create the department table.
sql_Ticket_department_t = “CREATE TABLE if not exists department_t (dep_id INTEGER PRIMARY KEY AUTOINCREMENT, dep_name text )”
c.execute(sql_Ticket_department_t)
db_conn.commit()

# Inserting ZERO records.
c.execute (“INSERT INTO department_t (dep_name) VALUES(:dep_name )”,{“dep_name”:’0′})
db_conn.commit()

# Inserting Basic Information.
Ticket_department_list = [‘Human Resources (HR)’,’Marketing’,’Finance’,’Information Technology (IT)’,’Customer Servic’]
for each in Ticket_department_list:
    c.execute (“INSERT INTO department_t (dep_name) VALUES(:dep_name)”,{“dep_name”:each})
db_conn.commit()

# Code to create the category table.
sql_Ticket_category_t = “CREATE TABLE if not exists category_t (cat_id INTEGER PRIMARY KEY AUTOINCREMENT, cat_name text)”
c.execute(sql_Ticket_category_t)
db_conn.commit()

# Inserting ZERO records.
c.execute (“INSERT INTO category_t (cat_name) VALUES(:cat_name)”,{‘cat_name’:’0′})
db_conn.commit()

# Inserting Basic Information.
Ticket_category_list = [‘Technical Support’,’Billing Inquiry’,’Account Management’,’Product Feedback’,’Bug Report’,’Feature Request’,’General Inquiry’,’Training Request’,’Hardware Issue’,’Software Issue’]
for each in Ticket_category_list:
    c.execute (“INSERT INTO category_t (cat_name) VALUES(:cat_name)”,{“cat_name”:each})
db_conn.commit()

Code to create the status table.

Thats the code for three tables, the other will be the same with other table names and fields, I will not write them all save the time.

Main Menu
The main menu will be called to start/run the application, through the main menu we can jump to all other functions in the application. let’s see the cobe.

# The Main Menu.
def main_menu():
  while True :
    os.system(‘clear’)
     line2 =”Main Menu”
    header(line2,7,7)
    print(‘ ‘*5,’ 1. Add New Ticket.’)
    print(‘ ‘*5,’ 2. Edit a Ticket.’)
    print(‘ ‘*5,’ 3. Delete a Ticket.’)
    print(‘ ‘*5,’ 4. Show Tickets.\n’)
    print(‘ ‘*5,’ 5. [ SETTING ]’)
    print(‘ ‘*5,’ 99. Exit.’)
    user_select = input(“\n\t Select from the Menu: > “)

    if user_select == ‘1’ :
      print(‘   you select 1’)
    if user_select == ‘2’ :
      print(‘   you select 2’)
    if user_select == ‘3’ :
      print(‘   you select 3’)
    if user_select == ‘4’ :
       print(‘   you select 4’)
    if user_select == ‘5’ :
      setting_menu()
    if user_select == ’99’ :
      input(‘\n\n\t Thank you for using this Appliation. ‘)
       break

Helping Functions
In the Ticket application, when the user insert a record in any lookup tables [category, priority, status, departmen], say category, then he may inter something already there, so we need to check for that.
Also, when the user want to select a ticket priority, or a category, he want to see the list of categories to select from, so we create a function called show_list so he can select from.

Two functions that will help and works within other functions.

def show_list(dt,d_id):
“””
   Function to Display the Data we have in the Lookup Tables based on the user selection function.

   Return: None
“””
   pass

def check_availabilty(dt, d_id, check_id):
“””
   Function to check if the passed ID available in the data-set or not.

  Arguments:
     dt : Data-Table
     d_id : Name of id column.
   check_id : The id we want to search for.

   Return : True if ID is available, False if not.
“””
  pass

Functions Names
Here we list all [almost all] functions we may need in this application.

# ========== Category Function ==============

def ticket_category_menu () :
     pass

def add_category():
     pass

def edit_category():
     pass

def delet_category():
     pass

def show_category():
     pass

# ========== Priority Function ==============
def add_priority ():
     pass

def edit_priority ():
     pass

def delete_priority ():
     pass

def show_priority ():
     pass

# ========== Status Function ==============
def add_status():
     pass

def edit_status():
     pass

def delet_status():
     pass

def show_status():
     pass

# ========== Department Function ==============

def add_department():
     pass

def edit_department():
     pass

def delet_department():
     pass

def show_department():
     pass

# ========== Staff Function ==============

def add_staff():
     pass

def edit_staff():
    pass

def delet_staff():
     pass

def show_staff():
     pass

We almost done with part one, all base are ready to start coding, in next part [Part2], we will start writing the codes for two or three functions to manage the lookups table such as Add,Edit and Delete data from Category, Department, Priority .. tables.

:: Ticket System ::

..:: Have Fun with Coding ::.. 🙂

To Download my Python code (.py) files Click-Here

Follow me on Twitter..

By: Ali Radwani