-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathInventory_Management.py
310 lines (287 loc) · 11.4 KB
/
Inventory_Management.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
import mysql.connector
conn = mysql.connector.connect(host='localhost',
user='root',
password='Mysql0109@',
)
print("Connection Made Successfully!")
class Inventory:
def __init___(self):
pass
def createDatabase():
sql= f"create database Inventory_Management"
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Databse is created successfully!")
def useDatabase():
sql = "use Inventory_Management"
crsr = conn.cursor()
crsr.execute(sql)
crsr.close()
conn.commit()
print("Database selected")
def ManufacturerTable():
sql=f"""create table manufacture(manufacturer_id int primary key,
manufacturer_name varchar(30) not null,
email varchar(50),
phone bigint,
products_to_manufacture INT DEFAULT 0,
defective_items INT DEFAULT 0);"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Manufacture Table is created.")
def GoodsTable():
sql = f"""create table goods(goods_id int primary key,
good_name varchar(50) not null ,
manufacturer_id int not null,
category varchar(20),
unit_price float not null,
quantity int not null default 0,
manufactured_date date,
color varchar(20),
foreign key(manufacturer_id) references manufacture(manufacturer_id));
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Goods Table is created.")
def PurchaseTable():
sql = f"""create table purchase(purchase_id int primary key,
goods_id int not null,
purchase_date date,
purchase_qty int default 0,
purchase_price float default 0.0,
store_name varchar(50),
store_type varchar(50),
foreign key(goods_id) references goods(goods_id));
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Purchase table is created.")
def SalesTable():
sql = """create table sales(sale_id int primary key,
goods_id int not null,
sale_price float,
sale_date date ,
sale_qty int default 0,
store_name varchar(50),
foreign key(goods_id) references goods(goods_id));
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Sales table is created.")
def defectiveItems():
sql = """create table defectiveItems(def_id int primary key,
goods_id int not null,
manufacturer_id int not null,
defect_description varchar(256),
foreign key(goods_id) references goods(goods_id),
foreign key(manufacturer_id) references manufacture(manufacturer_id));
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("defectiveItems table is created")
def insertManufactureData():
# Inserting values into Manufacture table
msql = f"""INSERT INTO manufacture(manufacturer_id, manufacturer_name, email, phone)
VALUES (%s, %s, %s, %s)"""
values = [
(1, "SS Export", "[email protected]", 1234567890),
(2, "ABC Manufacturing", "[email protected]", 9876543210),
(3, "XYZ Industries", "[email protected]", 4567890123)
]
crsr = conn.cursor()
crsr.executemany(msql,values)
crsr.close()
conn.commit()
# Inserting values into good table
def insertGoodData():
gsql = f"""INSERT INTO goods (goods_id, good_name, manufacturer_id, category, unit_price, quantity, manufactured_date , color)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"""
values = [
(1, "Wooden Chair", 1, "Furniture", 50.0, 10, "2023-01-15", "red"),
(2, "Steel Table", 2, "Furniture", 100.0, 5, "2023-02-10", "blue"),
(3, "Plastic Shelf", 3, "Storage", 20.0, 8, "2023-03-20", "Green"),
(4, "shirt", 2, "Clothes", 250.99, 2, "2023-02-16", "black")
]
crsr = conn.cursor()
crsr.executemany(gsql,values)
crsr.close()
conn.commit()
# Inserting values into Purchase table
def insertPurchaseData():
psql = f"""INSERT INTO purchase (purchase_id, goods_id, purchase_date, purchase_qty, purchase_price, store_name, store_type)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
values = [
(1, 1, "2023-04-05", 5, 200.0, "Store A", "Online"),
(2, 2, "2023-04-07", 3, 500.0, "Store B", "Offline"),
(3, 3, "2023-04-10", 2, 150.0, "Store C", "Online")
]
crsr = conn.cursor()
crsr.executemany(psql,values)
crsr.close()
conn.commit()
# Inserting values into Sales table
def insertSalesData():
ssql = f"""INSERT INTO sales (sale_id, goods_id, sale_date, sale_qty, store_name)
VALUES (%s, %s, %s, %s, %s);
"""
values = [
(1, 1, "2023-04-15", 3, "Store A"),
(2, 2, "2023-04-18", 2, "Store B"),
(3, 3, "2023-04-20", 4, "Store C")
]
crsr = conn.cursor()
crsr.executemany(ssql,values)
crsr.close()
conn.commit()
# Inserting values into defectiveItmes table
def insertDefectiveData():
dsql = f"""INSERT INTO defectiveItems (def_id, goods_id, manufacturer_id, defect_description)
VALUES (%s, %s, %s, %s);
"""
values = [
(1, 1, 1, "Cracked leg"),
(2, 2, 2, "Faulty welding"),
(3, 3, 3, "Scratched surface")
]
crsr = conn.cursor()
crsr.executemany(dsql,values)
crsr.close()
conn.commit()
print("Data has been inserted into all tables successfully!")
def query3():
"""In the “manufacture” table, one should be able to see all the products that need to be manufactured,
and defective items during the manufacture with different entries like manufacture id, number of items required, etc."""
sql = f"""SELECT manufacturer_id, manufacturer_name, products_to_manufacture, defective_items
FROM manufacture;"""
crsr = conn.cursor()
crsr.execute(sql)
values = crsr.fetchall()
print("Manufacturer_id | manufacture_name | products_to_mnufacture | defective_itmes ")
for value in values:
print(*value)
crsr.close()
conn.commit()
def deleteDefectiveItems():
sql = f"""DELETE FROM purchase p WHERE p.goods_id in (
select d.goods_id from defectiveItems d)
AND p.store_name = 'ORay'
AND p.purchase_date = '2023-04-01'
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Defective items has been deleted successfully!")
def updateRedColorToys():
sql = """UPDATE manufacture m
JOIN goods g ON m.manufacturer_id = g.manufacturer_id
JOIN purchase p ON g.goods_id = p.goods_id
SET m.manufacturer_id = 10,
m.manufacturer_name = 'Javid',
m.email = '[email protected]',
m.phone = 1233211231,
m.products_to_manufacture = 4,
m.defective_items = 2
WHERE g.category = 'toy' AND g.color = 'red';
"""
crsr = conn.cursor()
crsr.execute(sql)
conn.commit()
crsr.close()
print("Manufacture table has been updated basd on the criteria")
def woodenChair():
sql = """select g.quantity from goods g
where g.manufacturer_id in (
select m.manufacturer_id from manufacture m where g.manufactured_date<'2023-05-01'
)"""
crsr = conn.cursor()
crsr.execute(sql)
values = crsr.fetchall()
for value in values:
print(*value)
crsr.close()
def profitOfWoodenTable():
sql = """select (s.sale_price - p.purchase_price) as profit from sales s
join goods on s.goods_id = goods.goods_id
join purchase p on goods.goods_id = p.goods_id
join manufacture m on goods.manufacturer_id = m.manufacturer_id
where goods.good_name = 'wooden table'
and s.store_name = 'Mycare'
and m.manufacturer_name = 'SS Export';
"""
crsr = conn.cursor()
crsr.execute(sql)
value = crsr.fetchone()
profit = value[0] if value else None
print("Profit margin is : ",profit)
crsr.close()
def databaseNotExists():
crsr = conn.cursor()
sql = "show databases"
crsr.execute(sql)
databses = crsr.fetchall()
for db in databases:
if db[0]== "Inventory_Management":
return False
return True
if __name__ == '__main__':
print("----\tMENU\t----\n")
print("""
3. Create Manufacture Table \t\t 4. Create Good Table\t
5. Create Purchase Table\t\t 6. Create Sales Table
7. Create defectiveItmes Table \t 8. Insert data into Goods Table
9. Insert data into Purchase Table \t 10. Insert data into Sales Table
11. Insert data in defective Table\t 12. query3()
13. Delete Defective Items\t\t 14. Update red colored toys
15. Query for wooden chair\t\t 16. Profit of Wooden Table Products
17. Exit\n""")
i = Inventory
if i.databaseNotExists():
i.createDatabase()
ch = 0
i.useDatabase()
while ch!=17:
ch = int(input("Enter your choice : "))
if ch==3:
i.ManufacturerTable()
elif ch==4:
i.GoodsTable()
elif ch==5:
i.PurchaseTable()
elif ch==6:
i.SalesTable()
elif ch==7:
i.defectiveItems()
elif ch==8:
i.insertGoodData()
elif ch==9:
i.insertPurchaseData()
elif ch==10:
i.insertSalesData()
elif ch==11:
i.insertDefectiveData()
elif ch==12:
i.query3()
elif ch==13:
i.deleteDefectiveItems()
elif ch==14:
i.updateRedColorToys()
elif ch==15:
i.woodenChair()
elif ch==16:
i.profitOfWoodenTable()
elif ch==17:
break