warehouse_system/CommodityAction.py at master · fa-fa97/warehouse_system · GitHub

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
# -*- coding: utf-8 -*-
"""
Created on Thu Jun 07 16:14:08 2018

@author: Fatemeh
"""
import sys
sys.path.append(r"C:\Users\asus\Desktop\warehouseSystem\Model")
from pyodbc import *
from Warehouse import Warehouse
from Commodity import Commodity
import pandas as pd
from Settings import Setting
sting = Setting()
num=0
class CommodityAction(object):
    def addCommodity(self,Commodity, Warehouse):
        SQLCommand =  ("INSERT INTO Commodity"
                      "(CommodityName ,Type ,number ,price , WarehouseName)"
                      " VALUES ('%s','%s','%d','%s','%s')"%(Commodity.name ,Commodity.Type ,Commodity.number ,Commodity.price,Warehouse.name))
        sting.cursor.execute(SQLCommand)
        sting.connection.commit()


    def reduceCommodity(self,Commodity,numOfLost,Warehouse):
        number = self.getNumber(Commodity) - numOfLost
        SQLCommand = ("UPDATE Commodity SET number=%d WHERE CommodityName='%s' and Type='%s' and number=%d and price='%s' and WarehouseName='%s'"
                      %(number,self.getName(Commodity) ,self.getType(Commodity),self.getNumber(Commodity) ,self.getPrice(Commodity) ,Warehouse.name))

        sting.cursor.execute(SQLCommand)
        sting.connection.commit()

    def delCommodity(self,Commodity, Warehouse):
        SQLCommand = ("DELETE FROM Commodity WHERE CommodityName='%s' and Type='%s' and number=%d and price='%s' and  WarehouseName='%s' "%(Commodity.name ,Commodity.Type ,Commodity.number ,Commodity.price,Warehouse.name))

        sting.cursor.execute(SQLCommand)
        sting.connection.commit()

    def editCommodity(self, Commodity, newName, newType, newNumber, newPrice, Warehouse):
         SQLCommand = ("UPDATE Commodity SET CommodityName='%s' , Type='%s' , number=%d , price='%s' WHERE CommodityName='%s' and Type='%s' and number=%d and price='%s' and WarehouseName='%s'"
                       %(newName, newType, newNumber, newPrice,self.getName(Commodity) ,self.getType(Commodity),self.getNumber(Commodity) ,self.getPrice(Commodity) ,Warehouse.name))

         sting.cursor.execute(SQLCommand)
         sting.connection.commit()

    def getTotalNumOfCommodity(self,Warehouse):

        query = ("SELECT SUM(number) AS Total  FROM Commodity WHERE WarehouseName='%s'"%(Warehouse.name))
        sting.cursor.execute(query)
        results = sting.cursor.fetchone()
        r=str(results)
        removed = r.replace("(", "")
        removed2 = removed.replace(", )", "")
        print str(removed2)


        sting.connection.commit()

    def showAllCommodity(self,Warehouse):
        query=("SELECT CommodityName FROM Commodity WHERE WarehouseName='%s' "
                                      %(Warehouse.name))
        sting.cursor.execute(query)
        results = sting.cursor.fetchall()
        for row in results:
            r=str(row)
            removed = r.replace("u'", "")
            removed2 = removed.replace("'", "")
            print str(removed2)

    def checkNumOfCommodity(self,Commodity):
        query=("SELECT number FROM Commodity WHERE CommodityName='%s' and Type='%s' and price='%s'"%(Commodity.name,Commodity.Type, Commodity.price))
        sting.cursor.execute(query)
        results = sting.cursor.fetchone()
        r=str(results)
        removed = r.replace("(", "")
        removed2 = removed.replace(", )", "")
        print str(removed2)

    def showSpecificationCommodity(self, Commodity,Warehouse):
        query=("SELECT * FROM Commodity WHERE WarehouseName='%s' "
                                        %(Warehouse.name))
        sting.cursor.execute(query)
        results = sting.cursor.fetchall()
        for row in results:
            r=str(row)
            removed = r.replace("u'", "")
            removed2 = removed.replace("'", "")
            print str(removed2)


    def getName(self,Commodity):
        return Commodity.name

    def getType(self,Commodity):
        return Commodity.Type

    def getNumber(self,Commodity):
        return Commodity.number

    def getPrice(self,Commodity):
        return Commodity.price