import boto3 from botocore.exceptions import ClientError import dataset db = dataset.connect( 'mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57.cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4') def GetList(): #本週/粉絲/編輯推薦 records = db.query("""SELECT hs.start_time,hs.end_time, ifnull(ca.hdesigner_id,co.hdesigner_ids) hid, ca.hdesigner_id,co.hdesigner_ids, oss.title sec ,dd.title, dd.name, dd.mail, ifnull(ca.cover,co.clogo) img FROM homepage_set hs LEFT JOIN outer_site_set oss ON oss.oss_id = hs.outer_set left join _hcase ca ON ca.hcase_id = hs.mapping_id AND hs.theme_type='case' LEFT JOIN _hcolumn co ON hs.mapping_id = co.hcolumn_id AND hs.theme_type='column' LEFT JOIN _hdesigner dd ON dd.hdesigner_id = ifnull(ca.hdesigner_id,co.hdesigner_ids) WHERE hs.theme_type IN ('case','column') and start_time BETWEEN DATE_ADD(CURDATE(), INTERVAL-1 DAY) AND DATE_ADD(NOW(), INTERVAL-1 DAY) ORDER BY hs.ps_id """) mList = [] for row in records: if str(row['hid']) != "": sMail = row['mail'] sName = row['name'] sCom = row['title'] sSec = row['sec'] sImg = row['img'] sDuration = row['start_time'].strftime("%Y/%m/%d") + '-' + row['end_time'].strftime("%Y/%m/%d") mList.append([sMail,sCom,sName,sDuration,sSec,sImg]) #首八大 records = db.query("""SELECT hh.start_time,hh.end_time, hh.hdesigner_id hid, '首頁大' sec ,dd.title, dd.name, dd.mail, adlogo img FROM _had hh LEFT JOIN _hdesigner dd ON dd.hdesigner_id = hh.hdesigner_id WHERE adtype LIKE '首八大%' AND hh.onoff='1' AND dd.onoff = '1' and start_time BETWEEN DATE_ADD(CURDATE(), INTERVAL-1 DAY) AND DATE_ADD(NOW(), INTERVAL-1 DAY) -- AND(NOW() BETWEEN start_time AND end_time OR(start_time='0000-00-00 00:00:00' and end_time='0000-00-00 00:00:00') or (start_time is null and end_time is NULL)) ORDER BY cast(SUBSTR(adtype,4) AS DECIMAL) """) for row in records: if str(row['hid']) != "": sMail = row['mail'] sName = row['name'] sCom = row['title'] sSec = row['sec'] sImg = row['img'] sDuration = row['start_time'].strftime("%Y/%m/%d") + '-' + row['end_time'].strftime("%Y/%m/%d") mList.append([sMail,sCom,sName,sDuration,sSec,sImg]) return mList #print(sMail,sCom,sName,sDuration,sSec) def SendMail(iRecipient:str, iCompany:str, iName:str, iDuration:str, iSection:str, iImg:str): # Replace sender@example.com with your "From" address. # This address must be verified with Amazon SES. SENDER = "Gorgeous Space - Mailer " # Replace recipient@example.com with a "To" address. If your account # is still in the sandbox, this address must be verified. #RECIPIENT = iRecipient RECIPIENTS = iRecipient.split(',') CCS = ["yukyo@choozmo.com","mollie@hhh.com.tw","hhh_backup@hhh.com.tw"] BCCS = [] # Specify a configuration set. If you do not want to use a configuration # set, comment the following variable, and the # ConfigurationSetName=CONFIGURATION_SET argument below. #CONFIGURATION_SET = "ConfigSet" # If necessary, replace us-west-2 with the AWS Region you're using for Amazon SES. AWS_REGION = "us-east-1" # The subject line for the email. SUBJECT = "【幸福空間】BANNER露出：" + iCompany # The email body for recipients with non-HTML email clients. BODY_TEXT = ("\r\n" + iName + " 設計師 , 您好:\r\n\r\n您的幸福空間BANNER已露出！\r\n露出期間："+ iDuration + "\r\n露出位置："+ iSection+"Banner\r\n\r\n"+ iImg+"\r\n\r\n幸福空間 www.hhh.com.tw") # The HTML body of the email. BODY_HTML = """

""" + iName + " 設計師 , 您好:

您的幸福空間BANNER已露出！
露出期間："+ iDuration + "
露出位置："+ iSection+" Banner

幸福空間 www.hhh.com.tw

""" # The character encoding for the email. CHARSET = "UTF-8" # Create a new SES resource and specify a region. client = boto3.client('ses' ,aws_access_key_id='AKIAQG3PJZCHXPCSTAJG' ,aws_secret_access_key='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' ,region_name=AWS_REGION) # Try to send the email. try: #Provide the contents of the email. response = client.send_email( Destination={ 'ToAddresses': RECIPIENTS, 'CcAddresses': CCS, 'BccAddresses': BCCS }, Message={ 'Body': { 'Html': { 'Charset': CHARSET, 'Data': BODY_HTML, }, 'Text': { 'Charset': CHARSET, 'Data': BODY_TEXT, }, }, 'Subject': { 'Charset': CHARSET, 'Data': SUBJECT, }, }, Source=SENDER, # If you are not using a configuration set, comment or delete the # following line #ConfigurationSetName=CONFIGURATION_SET, ) # Display an error if something goes wrong. except ClientError as e: print(e.response['Error']['Message']) else: print("Email sent! Message ID:"), print(response['MessageId']) for item in GetList(): #print(item) SendMail(item[0],item[1],item[2],item[3],item[4],item[5])