社区团购提货管理

应用场景：

1、多个社区团购平台。2、快速录入提货信息。3、到货通过微信给顾客发提醒消息，4、提货时快速找到是哪个平台，哪一天。

 

 

 

from PyQt5.Qt import *
import sys
import datetime
from UI.pickup import Ui_Pickup
from NtTools.ODBC_MySQL import ODBC_MySQL


class BtnPane(object):
    # 按钮 添加  如是新手机号，先向客户表添加，在向提货表添加，否则直接向提货表添加
    @pyqtSlot()
    def on_btnAdd_clicked(self):
        if self.isCustomerExist():
            self.clearCustomer()
            self.leShortPhone.setText("")
            self.leShortPhone.setFocus(True)

    # 按钮 删除 如相同尾号，有多个客户，则删除客户
    @pyqtSlot()
    def on_btnDel_clicked(self):
        result = QMessageBox.question(None, "询问", "将从客户表中\n删除这个电话话码【%s】？？？" % self.cbPhone.currentText())
        if result == QMessageBox.Yes:
            rows = self.db.exec("delete from customer where phone = %s", self.cbPhone.currentText())
            if rows == 1:
                self.clearCustomer()
                self.showCustomer()
                self.cbPhone.setFocus()

    # 按钮 修改
    @pyqtSlot()
    def on_btnEdit_clicked(self):
        print(sys._getframe().f_lineno, "-on_btnEdit_clicked", self.customerId)
        result = QMessageBox.question(None, "询问", "将从客户表中\n修改这个电话话码的信息【%s】？？？" % self.cbPhone.currentText())
        if result == QMessageBox.Yes:
            # id = self.cbPhone.currentData()
            phone = self.lePhone.text()
            pickupName = self.lePickupName.text()
            wxNickname = self.leWxNickName.text()
            wxKey = self.leWxKey.text()
            notifyNum = self.spNotifyNum.value()
            if phone == "":
                phone = self.cbPhone.currentText()
            sql = """
                   update customer set  
                   phone = %s, pickupName = %s, wxNickname = %s, wxKey = %s,notifyNum = %s  
                   where id = %s
                 """
            rows = self.db.exec(sql, phone, pickupName, wxNickname, wxKey, notifyNum, self.customerId)
            print("是否修改成功", rows)
            self.showCustomer()
            self.show_tbInputPickup()

    @pyqtSlot()
    def on_btnAdd_2_clicked(self):
        if self.isCustomerExist():  # 客户存在，则添加到提货表
            self.addPickup(self.customerId)
            self.clearCustomer()
            self.leShortPhone.setText("")
            self.leShortPhone.setFocus(True)

    @pyqtSlot()
    def on_btnDel_2_clicked(self):
        print(sys._getframe().f_lineno, "-btnDel_2", self.pickupId)
        phone = self.tbInputPickup.item(self.tbInputPickup.currentRow(), 0).text()
        pickupName = self.tbInputPickup.item(self.tbInputPickup.currentRow(), 1).text()
        print(pickupName)
        r = QMessageBox.question(None, "询问", "是否从提货表中删除\n电话：%s\n取货人：%s" % (phone, pickupName))
        if r == QMessageBox.Yes:
            self.db.exec("delete from pickup where id = %s", self.pickupId)
            self.show_tbInputPickup()

    @pyqtSlot()
    def on_btnEdit_2_clicked(self):
        print(sys._getframe().f_lineno, "-on_btnEdit_2", self.pickupId)
        phone = self.tbInputPickup.item(self.tbInputPickup.currentRow(), 0).text()
        pickupName = self.tbInputPickup.item(self.tbInputPickup.currentRow(), 1).text()
        platFormId = self.cbbPlatform2.currentData()

        r = QMessageBox.question(None, "询问", "是否从提货表中修改\n电话：%s\n取货人：%s" % (phone, pickupName))
        if r == QMessageBox.Yes:
            self.db.exec("update pickup set customerId = %s,platFormId = %s where id = %s", self.customerId,platFormId, self.pickupId)
            self.show_tbInputPickup()
        pass
class ShowClearAdd(object):
    # 显示　右侧提货表
    def showPickup(self):
        platFormId = self.cbbPlatform.currentData()
        pickupDate = self.dePickup.dateTime().date().toString("yyyy-MM-dd")
        # print(platFormId, pickupDate)
        if platFormId == "":
            sql = "select * from v_pickup_customer where  pickupDate = %s"
            result = self.db.query(sql, pickupDate)
        else:
            sql = "select * from v_pickup_customer where platFormId = %s and pickupDate = %s"
            result = self.db.query(sql,platFormId,pickupDate)
        return result
    def show_tbInputPickup(self):
        result = self.showPickup()
        rows = len(result)
        self.tbInputPickup.setRowCount(rows)
        print(sys._getframe().f_lineno, "-show_tbInputPickup", result)
        for row in range(rows):
            pickupId = str(result[row][0])
            customerId = str(result[row][3])
            phone = result[row][4]
            pickupName = result[row][5]
            platformId = str(result[row][1])
            self.tbInputPickup.setItem(row, 0, QTableWidgetItem(phone))
            self.tbInputPickup.setItem(row, 1, QTableWidgetItem(pickupName))
            self.tbInputPickup.setItem(row, 2, QTableWidgetItem(customerId))
            self.tbInputPickup.setItem(row, 3, QTableWidgetItem(pickupId))
            self.tbInputPickup.setItem(row, 4, QTableWidgetItem(platformId))

            self.leShortPhone.setFocus(True)
    def show_tbNotifyPickup(self):
        result = self.showPickup()
        print(sys._getframe().f_lineno, "show_tbNotifyPickup", result)
        rows = len(result)
        self.tbNotifyPickup.setRowCount(rows)
        for row in range(rows):
            print(result[row])
            PlatformId = str(result[row][1])
            CustomerId = str(result[row][3])
            phone = result[row][4]
            pickupName = result[row][5]
            wxNickName = result[row][6]
            wxKey = result[row][7]
            NotifyNum = str(result[row][8])
            self.tbNotifyPickup.setItem(row, 0, QTableWidgetItem(phone))
            self.tbNotifyPickup.setItem(row, 1, QTableWidgetItem(pickupName))
            self.tbNotifyPickup.setItem(row, 2, QTableWidgetItem(wxNickName))
            self.tbNotifyPickup.setItem(row, 3, QTableWidgetItem(wxKey))
            self.tbNotifyPickup.setItem(row, 4, QTableWidgetItem(NotifyNum))
            self.tbNotifyPickup.setItem(row, 5, QTableWidgetItem(CustomerId))
            self.tbNotifyPickup.setItem(row, 6, QTableWidgetItem(PlatformId))

    def showCustomerById(self, id):
        result = self.db.query("select * from customer where id=%s ", id)
        print(sys._getframe().f_lineno, "-showCustomerById",result)
        if len(result) == 1:
            shortPhone = result[0][1]
            pickupName = result[0][2]
            wxNickname = result[0][3]
            phone = result[0][4]
            wxKey = result[0][5]
            notifyNum = result[0][6]

            self.leShortPhone.setText(shortPhone)
            self.lePickupName.setText(pickupName)
            self.leWxNickName.setText(wxNickname)
            self.cbPhone.blockSignals(True)
            self.cbPhone.setCurrentText(phone)
            self.cbPhone.blockSignals(False)
            self.leWxKey.setText(wxKey)
            self.spNotifyNum.setValue(notifyNum)
            self.leEnabled(True)
    # 通过手机短号，显示客户资料
    def showCustomer(self, row = 0):
        self.cbPhone.blockSignals(True)   #临时阻断信号发射
        self.cbPhone.clear()

        result = self.db.query("select * from customer where shortPhone=%s ", self.leShortPhone.text())
        print(sys._getframe().f_lineno, "-showCustomer",result)
        rows = len(result)
        # 没有找到手机号码
        if rows == 0:
            self.clearCustomer()
            self.customerId = ""
            self.cbPhone.setCurrentText("*"+self.leShortPhone.text())
            self.cbPhone.setEnabled(True)
            self.cbPhone.setFocus(True)
            self.cbPhone.lineEdit().setCursorPosition(0)
            self.cbPhone.blockSignals(False)
            return
        #　找到一个或多个号码
        if rows > 1:
            for r in result:
                self.cbPhone.addItem(r[4],r[0])
            self.leShortPhone.focusNextChild()
            QMessageBox.information(None,"提醒","手机尾号重复！",QMessageBox.Ok)
            self.leWxNickName.setFocus(True)
        self.cbPhone.blockSignals(False)
        # 显示左侧　客户信息
        self.customerId = result[row][0]
        self.showCustomerById(self.customerId)

    # 清除客户显示
    def clearCustomer(self, isEnable = False):
        self.lePickupName.setText("")
        self.leWxNickName.setText("")
        self.cbPhone.setCurrentText("")
        self.leWxKey.setText("")
        self.spNotifyNum.setValue(1)
        self.lePhone.setText("")
        self.leEnabled(isEnable)
    def clearCustomerNotPhone(self, isEnable = False):
        print("clearcustomerNotPhone")
        self.lePickupName.setText("")
        self.leWxNickName.setText("")
        self.leWxKey.setText("*"+self.leShortPhone.text())
        self.spNotifyNum.setValue(1)
        self.lePhone.setText("")       # 下方　需要更改电话号码的输入框
        self.leEnabled(isEnable)

    #　判断客户是否存在
    def isCustomerExist(self):
        # 1. 赋值
        shortPhone = self.leShortPhone.text()
        phone = self.cbPhone.currentText()
        # 2. 手机号为空，则设置焦点，返回
        if len(phone) == 0:
            self.cbPhone.setFocus()
            return False
        # 3. 通过手机号查询
        result = self.db.query("select id from customer where phone = %s", phone)
        print(sys._getframe().f_lineno, "-addCustomer:", result, len(result))
        # 3.1 没找到，说明是新的手机号,添加新客户
        if len(result) == 0:
            if self.addCustomer():  # 添加成功，则查找客户Id
                result = self.db.query("select id from customer where phone = %s", phone)
            else:
                return False

        # 3.2 找到,提货表增加一条记录
        self.customerId = result[0][0]
        return True
    # 添加到客户表
    def addCustomer(self):
        shortPhone = self.leShortPhone.text()
        phone = self.cbPhone.currentText()
        pickupName = self.lePickupName.text()
        wxNickname = self.leWxNickName.text()
        wxKey = self.leWxKey.text()
        notifyNum = self.spNotifyNum.value()

        sql = "insert into customer (phone, shortPhone, pickupName, wxNickname, wxKey,notifyNum) values(%s,%s,%s,%s,%s,%s)"
        rows = self.db.exec(sql, phone, shortPhone, pickupName, wxNickname, wxKey, notifyNum)
        if rows == 1:
            return True
        else:
            QMessageBox.warning(None, "警告", "客户添加失败")
            return False
        # 添加到提货表
    # 添加到提货表
    def addPickup(self, customerId):
        platFormId = self.cbbPlatform.currentData()
        pickupDate = self.dePickup.text()
        sql = "insert into pickup(platformId, pickupDate, customerId) values (%s, %s, %s)"
        self.db.exec(sql, platFormId, pickupDate, customerId)

        self.show_tbInputPickup()
        # 先通过手机号判断客户是否存在？


class MinWindow(QWidget, Ui_Pickup, BtnPane, ShowClearAdd):

    def __init__(self, parent = None, *args, **kwargs):
        super().__init__(parent, *args, **kwargs)
        self.db = ODBC_MySQL()
        self.setupUi(self)
        self.setupData()
        self.setupStyle()
        self.leShortPhone.added.connect(self.btnAdd_2.click)
        self.lePickupName.nextFocuse.connect(lambda :self.leWxNickName.setFocus(True))
        self.leWxNickName.nextFocuse.connect(lambda :self.leWxKey.setFocus(True))
        self.leWxKey.nextFocuse.connect(self.btnAdd_2.click)
        self.dePickup.setDate(datetime.date.today())

    def setupData(self):
        # 平台选择组合框
        result = self.db.queryPlatform()
        self.cbbPlatform.blockSignals(True)
        self.cbbPlatform.addItem("所有的平台","")
        for platformId,platformName,joinDate in result:
            self.cbbPlatform.addItem(platformName,platformId)
            self.cbbPlatform2.addItem(platformName,platformId)

        self.cbbPlatform.setCurrentIndex(1)
        self.cbbPlatform.blockSignals(False)

        self.oRow = -1
    def setupStyle(self):
        print(sys._getframe().f_lineno, "-setupStyle")
        self.tbInputPickup.setColumnWidth(0,138)
        self.tbInputPickup.setColumnWidth(1,118)

        self.tbQueryCustomer.setColumnWidth(0,138)
        self.tbQueryCustomer.setColumnWidth(1,128)
        self.tbQueryCustomer.setColumnWidth(2,138)

        self.tbQueryPickup.setColumnWidth(0,56)
        self.tbQueryPickup.setColumnWidth(1,108)
        self.tbQueryPickup.setColumnWidth(2,128)
        self.tbQueryPickup.setColumnWidth(3,128)
        self.tbQueryPickup.setColumnWidth(4,128)
        self.tbQueryPickup.setColumnWidth(5,188)

        self.tbNotifyPickup.setColumnWidth(0,108)
        self.tbNotifyPickup.setColumnWidth(1,108)
        self.tbNotifyPickup.setColumnWidth(2,168)
        self.tbNotifyPickup.setColumnWidth(3,108)
        self.tbNotifyPickup.setColumnWidth(4,108)
        pass
    def leEnabled(self,bool):
        self.lePickupName.setEnabled(bool)
        self.leWxNickName.setEnabled(bool)
        self.cbPhone.setEnabled(bool)
        self.leWxKey.setEnabled(bool)
        self.spNotifyNum.setEnabled(bool)

        self.btnAdd.setEnabled(bool)
        self.btnEdit.setEnabled(bool)
        self.btnDel.setEnabled(bool)

        self.btnAdd_2.setEnabled(bool)
        self.btnEdit_2.setEnabled(bool)
        self.btnDel_2.setEnabled(bool)

    #平台选择 改变
    @pyqtSlot(int)
    def on_cbbPlatform_currentIndexChanged(self, idx):
        print(sys._getframe().f_lineno, "-cbbPlatform_currentIndexChanged", idx,self.cbbPlatform.itemData(idx))
        if self.tabWidget.currentIndex() == 0:
            self.show_tbInputPickup()
            self.cbbPlatform2.setCurrentIndex(idx - 1)
        elif self.tabWidget.currentIndex() == 2:
            self.show_tbNotifyPickup()
    #提货日期 改变
    @pyqtSlot(QDate)
    def on_dePickup_dateChanged(self,date):
        print(sys._getframe().f_lineno, "-on_dePickup_dateChanged")
        if self.tabWidget.currentIndex() == 0:
            self.show_tbInputPickup()
        elif self.tabWidget.currentIndex() == 2:
            self.show_tbNotifyPickup()
    # 按钮 今天
    @pyqtSlot()
    def on_btnToday_clicked(self):
        print(datetime.date.today())
        self.dePickup.setDate(datetime.date.today())

    # TAB 切换 （输入，通知，查询）
    pyqtSlot(int)
    def on_tabWidget_tabBarClicked(self, idx):
        # print(sys._getframe().f_lineno, idx)
        if idx == 0:
            self.show_tbInputPickup()
        elif idx == 1:
            self.leQueryKey.setFocus(True)
            self.leQueryKey.selectAll()
        elif idx == 2:
            self.show_tbNotifyPickup()

    #################TAB 输入#######################
    # 手机短号文本框值改变且值为四位数，则显示客户资料。
    @pyqtSlot(str)
    def on_leShortPhone_textChanged(self, str):
        if len(str) == 4:
            self.showCustomer()
        else:
            self.clearCustomer()

    # 手机号 编辑后
    @pyqtSlot(str)
    def on_cbPhone_editTextChanged(self, str):
        print(sys._getframe().f_lineno, "-cbPhone_editTextChanged", str, self.cbPhone.findText(str))
        if self.cbPhone.findText(str) == -1:
            self.clearCustomerNotPhone(True)
        else:
            self.showCustomer(self.cbPhone.currentIndex())

    @pyqtSlot(int,int)
    def on_tbInputPickup_cellClicked(self, row, col):

        if row == 0 or  row != self.oRow:
            self.customerId = int(self.tbInputPickup.item(row, 2).text())
            self.pickupId = int(self.tbInputPickup.item(row, 3).text())
            self.platformId = int(self.tbInputPickup.item(row, 4).text())
            self.leEnabled(True)
            self.showCustomerById(self.customerId)
            idx = self.cbbPlatform2.findData(self.platformId)
            self.cbbPlatform2.setCurrentIndex(idx)
            self.oRow = row
    ##################TAB 查询#######################
    @pyqtSlot()      #查询按钮
    def on_btnQuery_clicked(self):
        print("310-on_btnQuery_clicked")
        queryKey = self.leQueryKey.text()
        queryKey = '%'+queryKey+'%'
        idSet = set()
        if queryKey != "":
            result = self.db.query("select id from customer where phone LIKE %s",queryKey)
            for r in result:
                idSet.add(r[0])
            result = self.db.query("select id from customer where pickupName LIKE %s", queryKey)
            for r in result:
                idSet.add(r[0])
            result = self.db.query("select id from customer where wxNickname LIKE %s", queryKey)
            for r in result:
                idSet.add(r[0])

            idList = list(idSet)
            idList.sort()

            if len(idList) == 0:
                QMessageBox.information(None,"提醒", "没有找到！！",QMessageBox.Ok)
                self.leQueryKey.selectAll()
                self.leQueryKey.setFocus(True)
                return

            self.tbQueryCustomer.setRowCount(len(idList))
            for r, id in enumerate(idList):
                result = self.db.query("select * from customer where id = %s", id)
                print(result)
                self.tbQueryCustomer.setItem(r,0,QTableWidgetItem(result[0][4]))
                self.tbQueryCustomer.setItem(r,1,QTableWidgetItem(result[0][2]))
                self.tbQueryCustomer.setItem(r,2,QTableWidgetItem(result[0][3]))
                self.tbQueryCustomer.setItem(r,3,QTableWidgetItem(str(id)))
            self.tbQueryCustomer.setFocus(True)
            self.tbQueryCustomer.setCurrentCell(0,3)
            self.tbQueryCustomer.setCurrentCell(0,0)
            self.leQueryKey.selectAll()
            self.leQueryKey.setFocus(True)

    @pyqtSlot(int,int,int,int)  #改变客户
    def on_tbQueryCustomer_currentCellChanged(self,row,col,oRow,oCol):
        if row != -1 and row != oRow or row == 0:
            customerId = int(self.tbQueryCustomer.item(row,3).text())
            # result = self.db.query("select * from v_pickup_customer where customerId = %s order by pickupDate desc",customerId)
            result = self.db.query("select * from v_platform_pickup where customerId = %s order by pickupDate desc",customerId)
            print(result)

            rows = len(result)
            self.tbQueryPickup.setRowCount(rows)
            for r in range(rows):
                platformId = str(result[r][0])
                platformName = result[r][1]
                pickupDate = str(result[r][2])
                phone = result[r][3]
                pickupName = result[r][4]
                wxNickname = result[r][5]
                print(platformId,platformName,pickupDate,phone,pickupName,wxNickname)
                self.tbQueryPickup.setItem(r, 0, QTableWidgetItem(platformId))
                self.tbQueryPickup.setItem(r, 1, QTableWidgetItem(platformName))
                self.tbQueryPickup.setItem(r, 2, QTableWidgetItem(pickupDate))
                self.tbQueryPickup.setItem(r, 3, QTableWidgetItem(phone))
                self.tbQueryPickup.setItem(r, 4, QTableWidgetItem(pickupName))
                self.tbQueryPickup.setItem(r, 5, QTableWidgetItem(wxNickname))

    #################TAB 通知########################
    def updateCustomer(self):   #更新客户表，微信标识，通知人数两个字段
        rows = self.tbNotifyPickup.rowCount()
        for row in range(rows):
            wxKey = self.tbNotifyPickup.item(row, 3).text()
            notifyNum = int(self.tbNotifyPickup.item(row, 4).text())
            customerId = int(self.tbNotifyPickup.item(row, 5).text())
            self.db.exec("update customer set wxKey = %s, notifyNum = %s where id = %s",wxKey, notifyNum, customerId)
        self.show_tbNotifyPickup()

    def buildNotify(self):
        # 1. 平台
        context = ""
        # 2. 内容
        if self.teNotifyContent.toPlainText() != "":
            context = context + self.teNotifyContent.toPlainText() + ">>>"
        else:
            context = context + "货已经到了" + ">>>"
        return context
    def buildNotify0(self):
        context = self.buildNotify()
        # 3. 顾客
        pickupName = self.lePickupName.text()
        wxKey = self.leWxKey.text()
        notifyNum = self.spNotifyNum.value()
        context = context + pickupName + "," + wxKey + "," + str(notifyNum) +"," + self.cbbPlatform.currentText()
        return context
    def buildNotify1(self):
        context = self.buildNotify()
        # 3. 顾客
        rows = self.tbNotifyPickup.rowCount()
        if rows == 0:
            return
        notifyCustomerNum = 0
        for row in range(rows):
            notifyNum = int(self.tbNotifyPickup.item(row, 4).text())
            if notifyNum > 0:
                pickupName = self.tbNotifyPickup.item(row, 1).text()
                wxKey = self.tbNotifyPickup.item(row, 3).text()
                platformId = self.tbNotifyPickup.item(row, 6).text()
                platformName = self.db.query("select platformName from platform where Id = %s" ,int(platformId))
                print(platformName)
                context = context + pickupName + "," + wxKey + "," + str(notifyNum) + "," + platformName[0][0] +","
                notifyCustomerNum += 1
        context = context.rstrip(",")
        QMessageBox.information(None,"说明", "总共要提醒的人数为：%s" % notifyCustomerNum, QMessageBox.Ok)
        return context

    @pyqtSlot()   # 按钮 通知
    def on_btnNotify_clicked(self):
        if self.tabWidget.currentIndex() == 0:
            context = self.buildNotify0()
            clipboard = QApplication.clipboard()
            clipboard.setText(context)

        elif self.tabWidget.currentIndex() == 2:
            print(sys._getframe().f_lineno, "on_btnNOtify_clicked")
            r = QMessageBox.question(None,"询问", "是否更新微信标识与通知人数\n这两个字段？")
            if r == QMessageBox.Yes:
                self.updateCustomer()
            context = self.buildNotify1()
            print(context)
            clipboard = QApplication.clipboard()
            clipboard.setText(context)



if __name__ == '__main__':
    app = QApplication(sys.argv)
    win = MinWindow()

    win.show()
    sys.exit(app.exec_())