yukyo0821
/
my_tests


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144
							
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 <noreply@hhh.com.tw>"

    # 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 = """<html>
    <head></head>
    <body>
    <p>""" + iName + " 設計師 , 您好:<br /><br />您的幸福空間BANNER已露出！<br />露出期間："+ iDuration + "<br />露出位置："+ iSection+" Banner<br /><br /><img style='width:40%' src='" + iImg + """' /><br /><br />幸福空間 <a href='http://www.hhh.com.tw'>www.hhh.com.tw</a></p>
    </body>
    </html>"""            

    # 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])