-
Notifications
You must be signed in to change notification settings - Fork 0
/
stckmgmt.py
356 lines (320 loc) · 13.1 KB
/
stckmgmt.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
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
# STOCK MANAGEMENT
import os
import mysql.connector as sqli
import datetime
now = datetime.datetime.now()
def mysql_conn():
global mydb,mycursor
try:
mydb=sqli.connect(user="root",host="localhost",passwd="adijio123")
mycursor=mydb.cursor()
mycursor.execute("create database stock;")
mydb.commit()
mycursor.execute("use stock;")
except sqli.Error :
mydb=sqli.connect(user="root",host="localhost",passwd="adijio123",database="stock")
mycursor=mydb.cursor()
def product_mgmt( ):
while True :
print("\t\t\t 1. Add New Product")
print("\t\t\t 2. List Product")
print("\t\t\t 3. Update Product")
print("\t\t\t 4. Delete Product")
print("\t\t\t 5. Back (Main Menu)")
p=int (input("\t\tEnter Your Choice :"))
if p==1:
add_product()
if p==2:
search_product()
if p==3:
update_product()
if p==4:
delete_product()
if p== 5 :
break
def purchase_mgmt( ):
while True :
print("\t\t\t 1. Add Order")
print("\t\t\t 2. List Order")
print("\t\t\t 3. Back (Main Menu)")
o=int (input("\t\tEnter Your Choice :"))
if o==1 :
add_order()
if o==2 :
list_order()
if o== 3 :
break
def sales_mgmt( ):
while True :
print("\t\t\t 1. Sale Items")
print("\t\t\t 2. List Sales")
print("\t\t\t 3. Back (Main Menu)")
s=int (input("\t\tEnter Your Choice :"))
if s== 1 :
sale_product()
if s== 2 :
list_sale()
if s== 3 :
break
def user_mgmt( ):
while True :
print("\t\t\t 1. Add user")
print("\t\t\t 2. List user")
print("\t\t\t 3. Back (Main Menu)")
u=int (input("\t\tEnter Your Choice :"))
if u==1:
add_user()
if u==2:
list_user()
if u==3:
break
def create_database():
print(" Creating PRODUCT table")
sql = "CREATE TABLE if not exists product (\
pcode int(4) PRIMARY KEY,\
pname char(30) NOT NULL,\
pprice float(8,2) ,\
pqty int(4) ,\
pcat char(30));"
mycursor.execute(sql)
print(" Creating ORDER table")
sql = "CREATE TABLE if not exists orders (\
orderid int(4)PRIMARY KEY ,\
orderdate DATE ,\
pcode char(30) NOT NULL , \
pprice float(8,2) ,\
pqty int(4) ,\
supplier char(50),\
pcat char(30));"
mycursor.execute(sql)
print(" ORDER table created")
print(" Creating SALES table")
sql = "CREATE TABLE if not exists sales (\
salesid int(4) PRIMARY KEY ,\
salesdate DATE ,\
pcode char(30) references product(pcode), \
pprice float(8,2) ,\
pqty int(4) ,\
Total double(8,2)\
);"
mycursor.execute(sql)
print(" SALES table created")
sql = "CREATE TABLE if not exists user (\
uid char(6) PRIMARY KEY,\
uname char(30) NOT NULL,\
upwd char(30));"
mycursor.execute(sql)
print(" USER table created")
mydb.commit()
def list_database():
sql="show tables;"
mycursor.execute(sql)
for i in mycursor:
print(i)
def add_order():
now = datetime.datetime.now()
sql="INSERT INTO orders(orderid,orderdate,pcode,pprice,pqty,supplier,pcat) values (%s,%s,%s,%s,%s,%s,%s)"
code=int(input("Enter product code :"))
oid=now.year+now.month+now.day+now.hour+now.minute+now.second
qty=int(input("Enter product quantity : "))
price=float(input("Enter Product unit price: "))
cat=input("Enter product category: ")
supplier=input("Enter Supplier details: ")
val=(oid,now,code,price,qty,supplier,cat)
mycursor.execute(sql,val)
mydb.commit()
def list_order():
sql="SELECT * from orders"
mycursor.execute(sql)
clrscr()
print("\t\t\t\t\t\t\t ORDER DETAILS")
print("-"*85)
print("orderid Date Product code price quantity Supplier Category")
print("-"*85)
for i in mycursor:
print(i[0],"\t",i[1],"\t",i[2],"\t ",i[3],"\t",i[4],"\t ",i[5],"\t",i[6])
print("-"*85)
def db_mgmt( ):
while True :
print("\t\t\t 1. Database creation")
print("\t\t\t 2. List Database")
print("\t\t\t 3. Back (Main Menu)")
p=int (input("\t\tEnter Your Choice :"))
if p==1 :
create_database()
if p==2 :
list_database()
if p== 3 :
break
def add_product():
sql="INSERT INTO product(pcode,pname,pprice,pqty,pcat) values (%s,%s,%s,%s,%s)"
code=int(input("\t\tEnter product code :"))
search="SELECT count(*) FROM product WHERE pcode=%s;"
val=(code,)
mycursor.execute(search,val)
for x in mycursor:
cnt=x[0]
if cnt==0:
name=input("\t\tEnter product name :")
qty=int(input("\t\tEnter product quantity :"))
price=float(input("\t\tEnter product unit price :"))
cat=input("\t\tEnter Product category :")
val=(code,name,price,qty,cat)
mycursor.execute(sql,val)
mydb.commit()
else:
print("\t\t Product already exist")
def update_product():
code=int(input("Enter the product code :"))
qty=int(input("Enter the quantity :"))
sql="UPDATE product SET pqty=pqty+%s WHERE pcode=%s;"
val=(qty,code)
mycursor.execute(sql,val)
mydb.commit()
print("\t\t Product details updated")
def delete_product():
code=int(input("Enter the product code :"))
sql="DELETE FROM product WHERE pcode = %s;"
val=(code,)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount," record(s) deleted");
def search_product():
while True :
print("\t\t\t 1. List all product")
print("\t\t\t 2. List product code wise")
print("\t\t\t 3. List product categoty wise")
print("\t\t\t 4. Back (Main Menu)")
s=int (input("\t\tEnter Your Choice :"))
if s==1 :
list_product()
if s==2 :
code=int(input(" Enter product code :"))
list_prcode(code)
if s==3 :
cat=input("Enter category :")
list_prcat(cat)
if s== 4 :
break
def list_product():
sql="SELECT * from product"
mycursor.execute(sql)
clrscr()
print("\t\t\t\t PRODUCT DETAILS")
print("\t\t","-"*47)
print("\t\t code name price quantity category")
print("\t\t","-"*47)
for i in mycursor:
print("\t\t",i[0],"\t",i[1],"\t",i[2],"\t ",i[3],"\t\t",i[4])
print("\t\t","-"*47)
def list_prcode(code):
sql="SELECT * from product WHERE pcode=%s"
val=(code,)
mycursor.execute(sql,val)
clrscr()
print("\t\t\t\t PRODUCT DETAILS")
print("\t\t","-"*47)
print("\t\t code name price quantity category")
print("\t\t","-"*47)
for i in mycursor:
print("\t\t",i[0],"\t",i[1],"\t",i[2],"\t ",i[3],"\t\t",i[4])
print("\t\t","-"*47)
def sale_product():
pcode=input("Enter product code: ")
sql="SELECT count(*) from product WHERE pcode=%s;"
val=(pcode,)
mycursor.execute(sql,val)
for x in mycursor:
cnt=x[0]
if cnt !=0 :
sql="SELECT * from product WHERE pcode=%s;"
val=(pcode,)
mycursor.execute(sql,val)
for x in mycursor:
print(x)
price=int(x[2])
pqty=int(x[3])
qty=int(input("Enter no of quantity :"))
if qty <= pqty:
total=qty*price;
print ("Collect Rs. ", total)
sql="INSERT into sales values(%s,%s,%s,%s,%s,%s)"
val=(int(cnt)+1,datetime.datetime.now(),pcode,price,qty,total)
mycursor.execute(sql,val)
sql="UPDATE product SET pqty=pqty-%s WHERE pcode=%s"
val=(qty,pcode)
mycursor.execute(sql,val)
mydb.commit()
else:
print(" Quantity not Available")
else:
print(" Product is not avalaible")
def list_sale():
sql="SELECT * FROM sales"
mycursor.execute(sql)
print(" \t\t\t\tSALES DETAILS")
print("-"*80)
print("Sales id Date Product Code Price Quantity Total")
print("-"*80)
for x in mycursor:
print(x[0],"\t",x[1],"\t",x[2],"\t ",x[3],"\t\t",x[4],"\t\t",x[5])
print("-"*80)
def list_prcat(cat):
print (cat)
sql="SELECT * from product WHERE pcat =%s"
val=(cat,)
mycursor.execute(sql,val)
clrscr()
print("\t\t\t\t PRODUCT DETAILS")
print("\t\t","-"*47)
print("\t\t code name price quantity category")
print("\t\t","-"*47)
for i in mycursor:
print("\t\t",i[0],"\t",i[1],"\t",i[2],"\t ",i[3],"\t\t",i[4])
print("\t\t","-"*47)
def add_user():
uid=input("Enter emaid id :")
name=input(" Enter Name :")
paswd=input("Enter Password :")
sql="INSERT INTO user values (%s,%s,%s);"
val=(uid,name,paswd)
mycursor.execute(sql,val)
mydb.commit()
print(mycursor.rowcount, " user created")
def list_user():
sql="SELECT uid,uname from user"
mycursor.execute(sql)
clrscr()
print("\t\t\t\t USER DETAILS")
print("\t\t","-"*27)
print("\t\t UID name ")
print("\t\t","-"*27)
for i in mycursor:
print("\t\t",i[0],"\t",i[1])
print("\t\t","-"*27)
def clrscr():
print("\n"*5)
while True:
clrscr()
print("\t\t\t STOCK MANAGEMENT")
print("\t\t\t ****************\n")
print("\t\t 1. PRODUCT MANAGEMENT")
print("\t\t 2. PURCHASE MANAGEMENT")
print("\t\t 3. SALES MANAGEMENT")
print("\t\t 4. USER MANAGEMENT")
print("\t\t 5. DATABASE SETUP")
print("\t\t 6. EXIT\n")
mysql_conn()
n=int(input("Enter your choice :"))
if n== 1:
product_mgmt()
if n== 2:
purchase_mgmt()
if n== 3:
sales_mgmt()
if n== 4:
user_mgmt()
if n==5 :
db_mgmt()
if n== 6:
break