Exemplo n.º 1
0
    def __call__(self):
        request = self.request
        id = request.get('id')
        type = request.get('type')
        category = request.get('category')
        date = request.get('date')
        department = request.get('department')
        title = request.get('title')
        detail_1 = request.get('detail_1')
        detail_2 = request.get('detail_2')
        detail_3 = request.get('detail_3')
        detail_4 = request.get('detail_4')
        sqlInstance = SqlObj()
        if category and title:
            sqlStr = """UPDATE signature SET title='{}',category='{}',date='{}',type='{}',department='{}',title='{}',detail_1='{}',
                        detail_2='{}',detail_3='{}',detail_4='{}' WHERE id = {}""".format(
                title, category, date, type, department, title, detail_1,
                detail_2, detail_3, detail_4, id)
            sqlInstance.execSql(sqlStr)
            url = api.portal.get().absolute_url() + '/signature_view'
            api.portal.show_message(message='更新簽呈成功!'.decode(),
                                    request=request)
            request.response.redirect(url)
        else:
            sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
            self.result = sqlInstance.execSql(sqlStr)
            self.sample = self.GetSignatureSample()

            return self.template()
Exemplo n.º 2
0
    def __call__(self):
        request = self.request
        type = request.get('type')
        category = request.get('category')
        date = request.get('date')
        department = request.get('department')
        title = request.get('title')
        detail_1 = request.get('detail_1')
        detail_2 = request.get('detail_2')
        detail_3 = request.get('detail_3')
        detail_4 = request.get('detail_4')
        user = api.user.get_current().getUserName()
        if type and category and date:
            sqlInstance = SqlObj()
            year = datetime.now().year - 1911
            sqlStr = """SELECT MAX(code) FROM signature WHERE year = {}""".format(
                year)
            code = sqlInstance.execSql(sqlStr)[0][0]
            code = int(code) + 1 if code else 1
            sqlStr = """INSERT INTO `signature`(`user`, `category`, `type`, `date`, `department`, `title`, `detail_1`, `detail_2`,
                       `detail_3`, `detail_4`, year, code) VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}', {}, {})
                     """.format(user, category, type, date, department, title,
                                detail_1, detail_2, detail_3, detail_4, year,
                                code)
            sqlInstance.execSql(sqlStr)
            api.portal.show_message(message='新增簽呈成功!'.decode(),
                                    request=request)
            abs_url = api.portal.get().absolute_url()
            request.response.redirect(abs_url + '/signature_view')

        self.sample = self.GetSignatureSample()

        return self.template()
Exemplo n.º 3
0
 def __call__(self):
     request = self.request
     id = request.get('id')
     execSql = SqlObj()
     sqlStr = """SELECT embedUrl FROM youtube WHERE id = '%s'""" % id
     self.result = execSql.execSql(sqlStr)
     return self.template()
Exemplo n.º 4
0
 def __call__(self):
     request = self.request
     action = request.get('action')
     self.action = action
     key_word = request.get('key_word')
     if key_word:
         sqlInstance = SqlObj()
         if action == 'between_date':
             start_date = key_word.split(',')[0]
             end_date = key_word.split(',')[1]
             sqlStr = """SELECT * FROM receipt WHERE receipt_date between '{}' AND '{}'""".format(
                 start_date, end_date)
         elif action == 'code':
             keyList = key_word.split(',')
             trainingCenter = keyList[0]
             year = keyList[1]
             sn = keyList[2]
             sqlStr = """SELECT * FROM receipt WHERE 1"""
             if year:
                 sqlStr += ' AND year = %s' % year
             if trainingCenter:
                 sqlStr += " AND training_center LIKE '%s%%%%'" % trainingCenter
             if sn:
                 sqlStr += " AND serial_number = %s" % sn
         else:
             sqlStr = """SELECT * FROM receipt WHERE {} LIKE '{}%%'""".format(
                 action, key_word)
         self.result = sqlInstance.execSql(sqlStr)
         return self.search_result()
     else:
         return self.template()
Exemplo n.º 5
0
    def __call__(self):
        request = self.request
        id = request.get('id')
        type = request.get('type')
        sqlInstance = SqlObj()
        sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
        result = sqlInstance.execSql(sqlStr)[0]
        obj = dict(result)
        parameter = {}
        sn = obj['year']
        for i in range(4 - len(str(obj['code']))):
            sn = '0' + str(sn)
        parameter['sn'] = safe_unicode(sn)
        parameter['year'] = safe_unicode(obj['date'].year)
        parameter['month'] = safe_unicode(obj['date'].month)
        parameter['day'] = safe_unicode(obj['date'].day)
        parameter['title'] = safe_unicode(obj['title'])
        parameter['detail_1'] = safe_unicode(obj['detail_1'])
        parameter['detail_2'] = safe_unicode(obj['detail_2'])
        parameter['detail_3'] = safe_unicode(obj['detail_3'])
        parameter['detail_4'] = safe_unicode(obj['detail_4'])
        parameter['department'] = safe_unicode(obj['department'])
        parameter['type'] = safe_unicode(obj['type'])
        parameter['category'] = safe_unicode(obj['category'])
        if type == '1':
            parameter['condition'] = safe_unicode('會辦部門行政服務處')

        filePath = '/home/andy/cshm/zeocluster/src/cshm.content/src/cshm/content/browser/static/signature.docx'

        doc = DocxTemplate(filePath)
        doc.render(parameter)
        doc.save("/tmp/temp.docx")

        return self.downloadFile('%s_簽呈' % sn)
Exemplo n.º 6
0
 def getAllStudent(self):
     uid = self.context.UID()
     sqlInstance = SqlObj()
     sqlStr = """SELECT *, training_status_code.status FROM `reg_course`, `training_status_code`, education_code WHERE uid = '{}' 
              and isAlt = 0 and seatNo IS NOT null and reg_course.training_status = training_status_code.id AND
              reg_course.education_id = education_code.id ORDER BY reg_course.seatNo""".format(
         uid)
     return sqlInstance.execSql(sqlStr)
Exemplo n.º 7
0
 def __call__(self):
     request = self.request
     sqlInstance = SqlObj()
     uid = self.context.UID()
     sqlStr = """SELECT * FROM receipt WHERE uid = '{}' ORDER BY receipt_date""".format(
         uid)
     self.result = sqlInstance.execSql(sqlStr)
     return self.template()
Exemplo n.º 8
0
 def __call__(self):
     request = self.request
     user_id = request.get('user_id')
     sqlInstance = SqlObj()
     sqlStr = """SELECT reg_course.*,training_status_code.status FROM reg_course,training_status_code WHERE reg_course.id = '{}' AND
                 reg_course.training_status = training_status_code.id""".format(
         user_id)
     self.result = sqlInstance.execSql(sqlStr)
     return self.template()
Exemplo n.º 9
0
    def __call__(self):
        request = self.request
        receipt_id = request.get('receipt_id')
        sqlInstance = SqlObj()

        sqlStr = """SELECT * FROM receipt WHERE id = {}""".format(receipt_id)
        self.result = sqlInstance.execSql(sqlStr)
        self.trainingCenter = self.context.trainingCenter.to_object.title

        return self.template()
Exemplo n.º 10
0
 def __call__(self):
     request = self.request
     receipt_id = request.get('receipt_id')
     if receipt_id:
         sqlInstance = SqlObj()
         sqlStr = """SELECT * FROM receipt WHERE id = {}""".format(
             receipt_id)
         self.result = sqlInstance.execSql(sqlStr)
         self.receipt_id = receipt_id
     return self.template()
Exemplo n.º 11
0
 def __call__(self):
     request = self.request
     receipt_id = request.get('receipt_id')
     if receipt_id:
         sqlInstance = SqlObj()
         sqlStr = """UPDATE receipt SET is_cancel = 1 WHERE id = {}""".format(
             int(receipt_id))
         sqlInstance.execSql(sqlStr)
         request.response.redirect('%s/@@admin_receipt_list' %
                                   self.context.absolute_url())
Exemplo n.º 12
0
    def __call__(self):
        context = self.context
        portal = api.portal.get()
        uid = context.UID()
        request = self.request
        execSql = SqlObj()

        execStr = """SELECT * FROM material WHERE uid = '%s'""" % uid
        order = execSql.execSql(execStr)
        orderList = []
        totalMaterialList = {}
        if order:
            for item in order:
                tmp = dict(item)
                status = tmp['status']
                apply_time = tmp['apply_time']
                order_id = tmp['id']
                logistics_code = tmp['logistics_code']
                send_date = tmp['send_date']
                action = tmp['action']
                orderList.append([
                    status, apply_time, order_id, logistics_code, send_date,
                    action
                ])
                # 切割detail,判斷action
                for detail in tmp['detail'].split('/'):
                    if detail:
                        name = detail.split('*')[0]
                        amount = int(detail.split('*')[1])
                        if action == 'return':
                            amount = amount * -1
                        if totalMaterialList.has_key(name):
                            totalMaterialList[name] += amount
                        else:
                            totalMaterialList[name] = amount

        self.orderList = orderList
        self.totalMaterialList = totalMaterialList

        materialList = []
        availableMaterial = context.availableMaterial
        # 抓此期別底下可以被新增的教材
        for materialUID in availableMaterial:
            content = api.content.get(UID=materialUID)
            title = content.title
            price = content.price
            discountPrice = content.discountPrice
            unit = content.unit
            finalPrice = discountPrice if discountPrice else price

            materialList.append([title, finalPrice, unit])
        self.materialList = materialList
        return self.template()
Exemplo n.º 13
0
 def __call__(self):
     request = self.request
     id = request.get('id')
     action = request.get('action')
     sqlInstance = SqlObj()
     if action == 'search':
         sqlStr = """SELECT * FROM signature_sample WHERE id = {}""".format(
             id)
         result = dict(sqlInstance.execSql(sqlStr)[0])
         return json.dumps(result)
     elif action == 'delect':
         sqlStr = """DELETE FROM signature_sample WHERE id = {}""".format(
             id)
         sqlInstance.execSql(sqlStr)
Exemplo n.º 14
0
 def __call__(self):
     request = self.request
     id = request.get('id')
     status = request.get('status')
     sqlInstance = SqlObj()
     if status:
         sqlStr = """UPDATE signature SET status = '{}' WHERE id = {}""".format(
             status, id)
         sqlInstance.execSql(sqlStr)
         url = api.portal.get().absolute_url() + '/signature_view'
         api.portal.show_message(message='更新狀態成功!'.decode(),
                                 request=request)
         request.response.redirect(url)
     else:
         sqlStr = """SELECT * FROM signature WHERE id = {}""".format(id)
         self.result = sqlInstance.execSql(sqlStr)
         return self.template()
Exemplo n.º 15
0
    def __call__(self):
        request = self.request
        receipt_id = request.get('receipt_id')
        if receipt_id:
            sqlInstance = SqlObj()
            now = datetime.datetime.now().strftime('%Y-%m-%d')
            user_name = api.user.get_current().getUserName()
            time = request.get('time')
            if time == 'one':
                check = 'one_check'
                date = 'one_check_date'
            elif time == 'two':
                check = 'two_check'
                date = 'two_check_date'

            sqlStr = """UPDATE receipt SET {} = 1,{} = '{}',inspector = '{}'  WHERE id = {}
                     """.format(check, date, now, user_name, receipt_id)
            self.result = sqlInstance.execSql(sqlStr)
            request.response.redirect('%s/@@admin_receipt_list' %
                                      self.context.absolute_url())
Exemplo n.º 16
0
    def __call__(self):
        request = self.request
        userList = request.get("userList", "")
        if userList:
            userList = json.loads(userList)
            if type(userList) == int:
                userList = [userList, 'aa']
            sqlInstance = SqlObj()
            sqlStr = """SELECT name,tax_no,company_name,receipt_money.money FROM reg_course LEFT JOIN receipt_money ON receipt_money.user_id = 
                        reg_course.id WHERE reg_course.id in {}""".format(
                tuple(userList))
            result = sqlInstance.execSql(sqlStr)
            title = ''
            totalMoney = 0
            for item in result:
                name = item[0]
                self.tax_no = item[1] if item[1] != 'None' and item[1] else ''
                money = item[3]
                company_name = item[2]

                if money:
                    totalMoney += money

                if company_name:
                    title = company_name
                elif title:
                    title += ',%s' % name
                else:
                    title = name

            self.title = title
            self.totalMoney = totalMoney
            try:
                userList.remove('aa')
            except:
                pass
            self.userList = userList

            return self.template()
        else:
            return self.empty_template()
Exemplo n.º 17
0
    def __call__(self):
        self.portal = api.portal.get()
        context = self.context

        uid = context.UID()
        sqlInstance = SqlObj()
        # 正取名單
        sqlStr = """SELECT reg_course.*,receipt_money.money FROM reg_course LEFT JOIN receipt_money ON receipt_money.user_id = reg_course.id 
                    WHERE reg_course.uid = '{}' and isAlt = 0 and on_training = 1 ORDER BY reg_course.seatNo""".format(
            uid)
        self.admit = sqlInstance.execSql(sqlStr)
        sqlStr = """SELECT id,user_id FROM receipt WHERE is_cancel = 0"""
        receipt = sqlInstance.execSql(sqlStr)
        idDict = {}
        for item in receipt:
            idList = item[1].split(',')
            for id in idList:
                if id:
                    idDict[id] = item[0]
        self.idDict = idDict
        return self.template()
Exemplo n.º 18
0
    def __call__(self):
        context = self.context
        execSql = SqlObj()
        data = []
        abs_url = api.portal.get().absolute_url()
        context_uid = context.UID()

        execStr = "SELECT COUNT(id) FROM `reg_course` WHERE uid = '%s'" % context_uid
        totalNumber = execSql.execSql(execStr)[0][0]

        for child in context.getChildNodes():
            child_uid = child.UID()
            execStr = "SELECT seat FROM satisfaction WHERE uid = '%s' ORDER BY seat" % child_uid
            seatList = execSql.execSql(execStr)
            seatStr = ''
            for seat in seatList:
                seatStr += str(seat[0]) + ','
            content = api.content.get(UID=child_uid)
            subjectName = content.title
            teacher = content.teacher.to_object.title
            startDateTime = content.startDateTime.strftime('%Y-%m-%d %H:%M:%S')
            if totalNumber:
                rate = round(float(len(seatList)) / float(totalNumber),
                             1) * 100
            else:
                rate = '無人報名'

            data.append([subjectName, seatStr, startDateTime, teacher, rate])
        url = "%s/check_survey" % (context.absolute_url())
        # 製作qrcode
        qr = qrcode.QRCode()
        qr.add_data(url)
        qr.make_image().save('url.png')
        img = open('url.png', 'rb')
        b64_img = base64.b64encode(img.read())
        self.url = url
        self.b64_img = b64_img
        self.data = data
        return self.template()
Exemplo n.º 19
0
    def __call__(self):
        request = self.request
        order_id = request.get('order_id')
        execSql = SqlObj()
        context = self.context

        execStr = """SELECT * FROM material WHERE id = %s""" % order_id
        self.result = execSql.execSql(execStr)
        availableMaterial = context.availableMaterial

        materialList = {}
        for materialUID in availableMaterial:
            content = api.content.get(UID=materialUID)
            title = content.title
            price = content.price
            discountPrice = content.discountPrice
            unit = content.unit
            finalPrice = discountPrice if discountPrice else price

            materialList[title] = [finalPrice, unit]
        self.materialList = materialList
        return self.template()
Exemplo n.º 20
0
    def __call__(self):
        portal = api.portal.get()
        echelonBrain = api.content.find(context=portal['mana_course'],
                                        portal_type='Echelon')
        nowDate = datetime.datetime.now().date()
        data = {}
        execSql = SqlObj()
        for item in echelonBrain:
            obj = item.getObject()
            courseStart = obj.courseStart
            if courseStart and courseStart >= nowDate:
                trainingCenter = str(obj.trainingCenter.to_object.title)
                period = obj.id
                name = str(obj.getParentNode().title)
                uid = obj.UID()
                execStr = """SELECT COUNT(id), organizer FROM `material` WHERE uid = '%s' GROUP BY organizer""" % uid
                result = execSql.execSql(execStr)
                # 若result為空代表尚未請領
                times = result[0][0] if result else '尚未請領'
                organizer = result[0][1] if result else ''
                contentUrl = obj.absolute_url()
                courseStart = courseStart.strftime('%Y-%m-%d')
                if data.has_key(trainingCenter):
                    data[trainingCenter].append([
                        name, period, courseStart, organizer, times, contentUrl
                    ])
                else:
                    data[trainingCenter] = [[
                        name, period, courseStart, organizer, times, contentUrl
                    ]]

        for k, v in data.items():
            data[k] = sorted(v, key=lambda x: x[2])
        self.data = data
        self.trainingCenterBrains = api.content.find(
            portal['resource']['training_center'],
            portal_type='TrainingCenter')
        return self.template()
Exemplo n.º 21
0
 def __call__(self):
     request = self.request
     sample_name = request.get('sample_name')
     category = request.get('category')
     title = request.get('title')
     detail_1 = request.get('detail_1')
     detail_2 = request.get('detail_2')
     detail_3 = request.get('detail_3')
     detail_4 = request.get('detail_4')
     user = api.user.get_current().getUserName()
     if sample_name:
         sqlInstance = SqlObj()
         sqlStr = """INSERT INTO signature_sample(`sample_name`, `user`, `category`, `title`, `detail_1`, `detail_2`, `detail_3`, 
                     `detail_4`) VALUES('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')""".format(
             sample_name, user, category, title, detail_1, detail_2,
             detail_3, detail_4)
         sqlInstance.execSql(sqlStr)
         api.portal.show_message(message='新增範本成功!'.decode(),
                                 request=request)
         abs_url = api.portal.get().absolute_url()
         request.response.redirect(abs_url + '/signature_view')
     else:
         return self.template()
Exemplo n.º 22
0
    def __call__(self):
        request = self.request
        year = request.get('year')
        category = request.get('category')
        item = request.get('item')
        kw = request.get('kw')

        sqlInstance = SqlObj()
        if year and category:
            sqlStr = """SELECT * FROM signature WHERE year = {} and category = '{}' ORDER BY code DESC""".format(
                year, category)
            self.result = sqlInstance.execSql(sqlStr)
            return self.template2()
        elif item and kw:
            sqlStr = """SELECT * FROM signature WHERE {} = '{}' ORDER BY code DESC""".format(
                item, kw)
            self.result = sqlInstance.execSql(sqlStr)
            return self.template2()
        else:
            sqlStr = """SELECT DISTINCT(year) FROM `signature`"""
            self.yearList = sqlInstance.execSql(sqlStr)

            return self.template()
Exemplo n.º 23
0
    def __call__(self):
        request = self.request
        keyword = request.get('keyword')
        language = request.get('language')

        if keyword or language:
            execSql = SqlObj()
            if keyword and language:
                sqlStr = """SELECT * FROM youtube WHERE keyword LIKE '%%{}%%' AND language LIKE '%%{}%%'""".format(
                    keyword, language)
            elif keyword:
                sqlStr = """SELECT * FROM youtube WHERE keyword LIKE '%%{}%%'""".format(
                    keyword)
            elif language:
                sqlStr = """SELECT * FROM youtube WHERE language LIKE '%%{}%%'""".format(
                    language)
            else:
                self.result = []
                return self.template2()

            self.result = execSql.execSql(sqlStr)
            return self.template2()
        else:
            return self.template()
Exemplo n.º 24
0
    def __call__(self):
        request = self.request
        material_detail = request.get('detail')
        send_date = request.get('send_date')
        remark = request.get('remark')
        address = request.get('address')
        action = request.get('action')
        context = self.context

        uid = context.UID()
        course = context.getParentNode().title
        period = context.id
        context_url = context.absolute_url()
        organizer = api.user.get_current().getUserName()
        # 做新增及退還
        execSql = SqlObj()
        execStr = """INSERT INTO `material`( `course`, `period`, `uid`, `status`, `send_date`, `logistics_code`, `detail`, `remark`,
                 `organizer`, `address`, `action`) VALUES('{}','{}','{}','{}','{}','{}','{}','{}', '{}', '{}', '{}')
                  """.format(course, period, uid, '處理中(寫死)', send_date, ' ',
                             material_detail, remark, organizer, address,
                             action)
        execSql.execSql(execStr)

        request.response.redirect(context_url + '/@@material_view')
Exemplo n.º 25
0
    def __call__(self):
        request = self.request
        portal = api.portal.get()
        execSql = SqlObj()

        now = datetime.datetime.now()

        prepareList = api.content.find(
            context=portal['language_study'],
            portal_type='Prepare',
            start_date={
                'query': (datetime.date(now.year, now.month, now.day),
                          datetime.date(now.year, now.month, now.day)),
                'range':
                'min: max'
            })
        #        prepareList = [api.content.get(UID='e9a28fd812014e849e5f4868168e3f4a')]

        for brain in prepareList:
            obj = brain.getObject()
            #        for obj in prepareList:

            uid = obj.UID()
            sqlStr = "SELECT name FROM attend WHERE uid = '{}'".format(uid)
            attend = execSql.execSql(sqlStr)
            attend = [i[0] for i in attend]

            parentUid = obj.getParentNode().UID()
            sqlStr = "SELECT * FROM student WHERE uid = '{}' AND verify = 1".format(
                parentUid)
            student = execSql.execSql(sqlStr)

            date = now.strftime('%Y-%m-%d')
            for item in student:
                temp = dict(item)
                name = temp['name']
                county = temp['county']
                school = temp['school']
                course = '%s%s' % (temp['course'], temp['language'])

                if name not in attend:
                    sqlStr = """INSERT INTO attend(name, date, status, course, county, school, uid) VALUES('{}', '{}', '{}', '{}', '{}',
                            '{}', '{}')""".format(name, date, 'notOnCall',
                                                  course, county, school, uid)
                    execSql.execSql(sqlStr)
Exemplo n.º 26
0
 def __call__(self):
     try:
         request = self.request
         money = int(request.get('money'))
         user_id = int(request.get('user_id'))
         uid = self.context.UID()
         path = self.context.absolute_url_path()
         sqlInstance = SqlObj()
         sqlStr = """SELECT id FROM receipt_money WHERE user_id = {}""".format(
             user_id)
         check = sqlInstance.execSql(sqlStr)
         if check:
             sqlStr = """UPDATE receipt_money SET money = {} WHERE user_id = {}""".format(
                 money, user_id)
             sqlInstance.execSql(sqlStr)
         else:
             sqlStr = """INSERT INTO receipt_money(user_id, money, uid, path) VALUES({}, {}, '{}', '{}')
                      """.format(user_id, money, uid, path)
             sqlInstance.execSql(sqlStr)
         return 'success'
     except Exception as e:
         print e
         return 'error'
Exemplo n.º 27
0
    def __call__(self):
        sqlInstance = SqlObj()
        sqlStr = """SELECT * FROM signature  ORDER BY code desc LIMIT 50"""
        self.result = sqlInstance.execSql(sqlStr)

        return self.template()
Exemplo n.º 28
0
 def GetSignatureSample(self):
     sqlInstance = SqlObj()
     user = api.user.get_current().getUserName()
     sqlStr = """SELECT * FROM `signature_sample` WHERE user = '******' or user is NULL """.format(
         user)
     return sqlInstance.execSql(sqlStr)
Exemplo n.º 29
0
    def __call__(self):
        request = self.request
        mode = request.get('mode')
        period = request.get('period')
        search = request.get('search')
        selected_course = request.get('selected_course')
        portal = api.portal.get()
        execSql = SqlObj()
        lang_cata = {
            '阿美語': '南勢阿美語 秀姑巒阿美語 海岸阿美語 馬蘭阿美語 恆春阿美語',
            '泰雅語': '賽考利克泰雅語 澤敖利泰雅語 汶水泰雅語 萬大泰雅語 四季泰雅語 澤敖利泰雅語',
            '賽夏語': '賽夏語',
            '邵語': '邵語',
            '賽德克語': '都達語 德固達雅語 德魯固語',
            '布農語': '卓群布農語 卡群布農語 丹群布農語 巒群布農語 郡群布農語',
            '排灣語': '東排灣語 北排灣語 中排灣語 南排灣語',
            '魯凱語': '東魯凱語 霧臺魯凱語 大武魯凱語 多納魯凱語 茂林魯凱語 萬山魯凱語',
            '太魯閣語': '太魯閣語',
            '噶瑪蘭語': '噶瑪蘭語',
            '鄒語': '鄒語',
            '卡那卡那富語': '卡那卡那富語',
            '拉阿魯哇語': '拉阿魯哇語',
            '卑南語': '南王卑南語 知本卑南語 初鹿卑南語 建和卑南語',
            '雅美語': '雅美語',
            '撒奇萊雅語': '撒奇萊雅語'
        }

        if search:
            course = api.content.find(
                context=portal['language_study'][period]['class_intro'],
                depth=1,
                portal_type='Course')
            courseList = []
            for i in course:
                courseList.append({'title': i.Title, 'id': i.id})
            return json.dumps(courseList)

        if mode == 'lang':
            course = api.content.find(
                context=portal['language_study'][period]['class_intro'],
                depth=1,
                portal_type='Course')
            count = {
                '阿美語': 0,
                '泰雅語': 0,
                '賽夏語': 0,
                '邵語': 0,
                '賽德克語': 0,
                '布農語': 0,
                '排灣語': 0,
                '魯凱語': 0,
                '太魯閣語': 0,
                '噶瑪蘭語': 0,
                '鄒語': 0,
                '卡那卡那富語': 0,
                '拉阿魯哇語': 0,
                '卑南語': 0,
                '雅美語': 0,
                '撒奇萊雅語': 0
            }
            total = 0
            for i in course:
                title = i.Title[6:]
                flag = True
                for k, v in lang_cata.items():
                    if title in v.split(' '):
                        count[k] += 1
                        total += 1
                        flag = False
                        continue
            self.count = json.dumps(count)
            self.total = total
            return self.template2()
        elif mode == 'student_rate':
            count = {'到課': 0, '缺課': 0}

            start = request.get('start')
            end = request.get('end')
            month = request.get('month')

            sqlStr = """SELECT COUNT(status) FROM attend WHERE date """

            if start and end:
                onCallStr = """BETWEEN '{}' AND '{}' AND status = 'onCall'""".format(
                    start, end)
                notOnCallStr = """BETWEEN '{}' AND '{}' AND status = 'notOnCall'""".format(
                    start, end)
            elif month:
                onCallStr = """like '{}' AND status = 'onCall'""".format(
                    month + '%%')
                notOnCallStr = """like '{}' AND status = 'notOnCall'""".format(
                    month + '%%')

            onCall = execSql.execSql('%s %s' % (sqlStr, onCallStr))[0][0]
            notOnCall = execSql.execSql('%s %s' % (sqlStr, notOnCallStr))[0][0]

            self.start = start
            self.end = end
            self.month = month
            self.result = json.dumps({
                'onCall': onCall,
                'notOnCall': notOnCall
            })
            return self.template3()

        elif mode == 'notOnCallDetail':
            start = request.get('start')
            end = request.get('end')
            month = request.get('month')

            if start and end:
                sqlStr = """SELECT * FROM attend WHERE date BETWEEN '{}' AND '{}'""".format(
                    start, end)

            elif month:
                sqlStr = """SELECT * FROM attend WHERE date LIKE '{}'""".format(
                    month + '%%')

            result = execSql.execSql(sqlStr)

            count = {}
            for item in result:
                obj = dict(item)
                course = obj['course'][6:].encode('utf-8')
                status = obj['status']
                for k, v in lang_cata.items():
                    if course in v.split(' '):
                        if count.has_key(k):
                            count[k]['出席' if status == 'onCall' else '缺席'] += 1
                            if status == 'notOnCall':
                                count[k]['缺席資料'].append(obj)
                        else:
                            count[k] = {
                                '出席': 1 if status == 'onCall' else 0,
                                '缺席': 1 if status == 'notOnCall' else 0,
                                '缺席資料': [obj] if status == 'notOnCall' else []
                            }
                        continue
            self.count = count

            return self.template4()
        elif mode == 'page_count':
            period = request.get('period')
            url = 'http://apc2.17study.com.tw/language_study/%s/class_intro/' % period

            sqlStr = """SELECT url,count,title FROM page_count WHERE url LIKE '{}%%'""".format(
                url)

            self.count = execSql.execSql(sqlStr)
            return self.template5()
        elif mode == 'number':
            number_period = request.get('number_period')

            sqlStr = """SELECT language FROM student WHERE course like '{}%%' AND verify = 1 AND cancel = 0""".format(
                number_period)
            result = execSql.execSql(sqlStr)

            count = {
                '阿美語': 0,
                '泰雅語': 0,
                '賽夏語': 0,
                '邵語': 0,
                '賽德克語': 0,
                '布農語': 0,
                '排灣語': 0,
                '魯凱語': 0,
                '太魯閣語': 0,
                '噶瑪蘭語': 0,
                '鄒語': 0,
                '卡那卡那富語': 0,
                '拉阿魯哇語': 0,
                '卑南語': 0,
                '雅美語': 0,
                '撒奇萊雅語': 0
            }
            for lang in result:
                for k, v in lang_cata.items():
                    if lang[0].encode('utf-8') in v.split(' '):
                        count[k] += 1
                        continue
            self.count = count
            self.total = len(result)
            return self.template6()
        elif mode == 'satisfaction':
            sqlStr = """SELECT anw1, anw2, anw3, anw4, anw5 FROM satisfaction"""
            result = execSql.execSql(sqlStr)
            count = {
                'anw1': {
                    '非常滿意': 0,
                    '滿意': 0,
                    '普通': 0,
                    '不滿意': 0,
                    '非常不滿意': 0
                },
                'anw2': {
                    '非常滿意': 0,
                    '滿意': 0,
                    '普通': 0,
                    '不滿意': 0,
                    '非常不滿意': 0
                },
                'anw3': {
                    '非常滿意': 0,
                    '滿意': 0,
                    '普通': 0,
                    '不滿意': 0,
                    '非常不滿意': 0
                },
                'anw4': {
                    '非常滿意': 0,
                    '滿意': 0,
                    '普通': 0,
                    '不滿意': 0,
                    '非常不滿意': 0
                },
                'anw5': {
                    '非常滿意': 0,
                    '滿意': 0,
                    '普通': 0,
                    '不滿意': 0,
                    '非常不滿意': 0
                },
            }
            for item in result:
                obj = dict(item)
                anw1 = obj['anw1'].encode('utf-8')
                anw2 = obj['anw2'].encode('utf-8')
                anw3 = obj['anw3'].encode('utf-8')
                anw4 = obj['anw4'].encode('utf-8')
                anw5 = obj['anw5'].encode('utf-8')
                count['anw1'][anw1] += 1
                count['anw2'][anw2] += 1
                count['anw3'][anw3] += 1
                count['anw4'][anw4] += 1
                count['anw5'][anw5] += 1
            self.count = json.dumps(count)
            return self.template7()
        else:
            self.brain = api.content.find(portal_type='LiveClass')
            return self.template()

        if api.is_anonymous():
            self.call_powerbi()
Exemplo n.º 30
0
    def __call__(self):
        request = self.request
        order_id = request.get('order_id', '')
        uid = self.context.UID()
        type = request.get('type')
        execSql = SqlObj()
        if type == 'shipment':
            execStr = """SELECT * FROM material WHERE id = '%s'""" % order_id
            self.result = execSql.execSql(execStr)

            return self.template_shipment()

        elif type == 'return':
            execStr = """SELECT * FROM material WHERE id = '%s'""" % order_id
            self.result = execSql.execSql(execStr)

            return self.template_return()

        elif type == 'material':
            execStr = """SELECT * FROM material WHERE uid = '%s'""" % uid
            result = execSql.execSql(execStr)
            organizerList = []
            applyTimeList = []
            returnList = {}
            materialList = {}
            availableList = {}
            availableMaterial = self.context.availableMaterial
            # 抓此期別底下可以被新增的教材
            for available in availableMaterial:
                content = api.content.get(UID=available)
                title = content.title
                price = content.price
                discountPrice = content.discountPrice
                availableList[
                    title] = discountPrice if discountPrice else price
            # 切割detail,判斷action
            for item in result:
                for material in item[8].split('/'):
                    name = material.split('*')[0]
                    if name:
                        amount = int(material.split('*')[1])
                        if item[12] == 'return':
                            if returnList.has_key(name):
                                returnList[name] += amount
                            else:
                                returnList[name] = amount
                            amount = amount * -1

                        if materialList.has_key(name):
                            materialList[name][0] += amount
                        else:
                            price = availableList[name]
                            materialList[name] = [amount, price]
                if item[10] not in organizerList:
                    organizerList.append(item[10])
                if item[5] not in applyTimeList:
                    applyTimeList.append(item[5])

            self.returnList = returnList
            self.materialList = materialList
            self.organizerList = organizerList
            self.applyTimeList = applyTimeList
            return self.template_material()