using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using AjaxControlToolkit;
using System.Text;
using System.Data.SqlClient;
using System.Threading;
using System.Net.Mail;

public partial class Comment : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // ihXǤJѼƬOComponent Name
        //bnXתWٵ lblComponentName
        string componentID = Request.QueryString["componentID"];   //γoӴե\઺i      

        lblComponentName.Text = queryComponentNameByID(componentID);  //test data      
        lblComponentVersion.Text = queryComponentVersionByID(componentID);
        lblComponentID.Text = componentID;
        lblComponentID.Visible = true;
        string strContributorEmail = queryContributorEmailByID(lblComponentID.Text.Trim());
        lblEmailTo.Text = strContributorEmail;

        if (!isThisPersionHadDownloadTheComponent(componentID, Context.User.Identity.Name))
        {
            
           // showMsg("zQ\צ");
           // Thread.Sleep(3000);
            Server.Transfer("~/Default.aspx");
        }
    }

    protected void btnComment_Click(object sender, EventArgs e)
    {
        insertCommentList();  //sWצC
        updateTotalScoreOfComponent(lblComponentID.Text.Trim());    //򱵵~s줸`
        //He׵}o
      
        sendEMail("", lblEmailTo.Text.Trim());
        Server.Transfer("~/Default.aspx");
    }


    private string queryComponentVersionByID(string strComponentID)
    {
        string strComponentVersion = String.Empty;
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;


        SqlConnection conn = new SqlConnection(CCSConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT VERSION FROM COMPONENT WHERE COMPONENT_ID ='" + strComponentID + "' ORDER BY VERSION DESC",
                                   conn);


        conn.Open();
        SqlDataReader  reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            strComponentVersion = reader["VERSION"].ToString().Trim();
                                
        }
        reader.Close();
        conn.Close();
        return strComponentVersion;
    }

   #region Hcomponent_IDdߤW
    private string queryComponentNameByID(string strComponentID) 
    {
        string strComponentName = String.Empty;
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;


        SqlConnection conn = new SqlConnection(CCSConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT NAME FROM COMPONENT WHERE COMPONENT_ID ='" + strComponentID + "' ORDER BY NAME DESC",
                                   conn);


        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            strComponentName = reader["NAME"].ToString().Trim();

        }
        reader.Close();
        conn.Close();

        return strComponentName;
    }
   #endregion

    #region Hcomponent_IDdߤ}o̪Email
    private string queryContributorEmailByID(string strComponentID)
    {
        string strContributorEmail = String.Empty;
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;


        SqlConnection conn = new SqlConnection(CCSConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT EMAIL FROM COMPONENT WHERE COMPONENT_ID ='" + strComponentID+"' ",
                                   conn);


        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            strContributorEmail = reader["EMAIL"].ToString().Trim();

        }
        reader.Close();
        conn.Close();

        return strContributorEmail;
    }
    #endregion




    #region sWצC
    private void insertCommentList()
    {

      StringBuilder strCommentTime = new StringBuilder("");
        strCommentTime.AppendFormat("{0:yyyy/MM/dd-hh:mm:ss}", System.DateTime.Now);      

        //nOO֩ɡA@FסAMFAoӤHeNULoӤAϥΫo
        string strInsertCommand = "INSERT INTO COMMENT_LIST (COMPONENT_ID, VERSION, COMMENT, USER_ID, SHOW_USER_OR_NOT, MYDATETIME)"
                                + " VALUES('" + lblComponentID.Text + "','" + lblComponentVersion.Text + "' ,'" + tbxComment.Text + "' ,'"
                                + Context.User.Identity.Name + "','" + (cbxShowUserOrNot.Checked ? "1":"0") + "','" + strCommentTime + "')";                           
        
        showMsg(strInsertCommand);
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;
        SqlConnection Conn = new SqlConnection(CCSConnectionString);

        try
        {
            Conn.Open();
        }
        catch (SqlException ex)
        {
            // Connection failed
            showMsg(ex.Message);
        }

        try
        {
            SqlCommand command = new SqlCommand(strInsertCommand, Conn);
            command.CommandType = System.Data.CommandType.Text;
            command.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            showMsg(ex.Message);
        }
        finally
        {
            Conn.Close();    //remember to close the db connection
        }

    }
    #endregion
     
    #region s`
    private void updateTotalScoreOfComponent(string strComponentID) 
    {

        int iTotalScoreTillNow = queryTotalScoreOfComponent(strComponentID);

        int iNewTotalScore = iTotalScoreTillNow + Convert.ToInt32(ddlComponentScore.SelectedValue);

        string strUpdateCommand = "UPDATE COMPONENT SET TOTAL_SCORE = '" + iNewTotalScore.ToString() +"'"
                                + " WHERE COMPONENT_ID = '" + strComponentID + "'";

        showMsg(strUpdateCommand);

        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;
       
        SqlConnection Conn = new SqlConnection(CCSConnectionString);

        try
        {
            Conn.Open();
        }
        catch (SqlException ex)
        {
            // Connection failed
            showMsg(ex.Message);
        }

        try
        {
            SqlCommand command = new SqlCommand(strUpdateCommand, Conn);
            command.CommandType = System.Data.CommandType.Text;
            command.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            showMsg(ex.Message);
        }
        finally
        {
            Conn.Close();    //remember to close the db connection
        }
    
    }

    #endregion

    #region dߥثe`
    private int queryTotalScoreOfComponent(string strComponentID)
    {
        //dߥثe`AM[`As`
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;
        
        int iTotalScoreNow = 0; //lȵs
        string SelectCommand = "SELECT TOTAL_SCORE FROM COMPONENT WHERE COMPONENT_ID = '"+ strComponentID+"'";       
        SqlConnection Conn = new SqlConnection(CCSConnectionString);     

        try
        {
            Conn.Open();
        }
        catch (SqlException ex)
        {
            // Connection failed
            showMsg(ex.Message);
        }

        try
        {
            SqlCommand command = new SqlCommand(SelectCommand, Conn);
            command.CommandType = System.Data.CommandType.Text;
            iTotalScoreNow = Convert.ToInt32(command.ExecuteScalar());
        }
        catch (SqlException ex)
        {
            showMsg(ex.Message);
        }
        finally
        {
            Conn.Close();    //remember to close the db connection         
        }
        return iTotalScoreNow;
    }
    #endregion

    protected void showMsg(string AlertMessage)
    {
        Literal txtMsg = new Literal();
        txtMsg.Text = "<script>alert('" + AlertMessage + "')</script>" + "<br/>";
        Page.Controls.Add(txtMsg);
    }

    #region

    private bool isThisPersionHadDownloadTheComponent(string strComponentID, string strUserID)
    {
        bool hasBeenDownloaded = false;
        
        ConnectionStringSettings connSettings = ConfigurationManager.ConnectionStrings["CCSConnectionString"];
        string CCSConnectionString = connSettings.ConnectionString;
        SqlDataReader reader = null;
        int iQualifyCounter = 0; //lȵs
        
        SqlConnection conn = new SqlConnection(CCSConnectionString);
        SqlCommand cmd = new SqlCommand("SELECT * FROM DOWNLOAD_LIST WHERE COMPONENT_ID = '"+ strComponentID+"' AND USER_ID ='"+strUserID +"'" ,
                                  conn);

        try{
            conn.Open();
             reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                iQualifyCounter++;

            }
        }catch(SqlException ex){
            showMsg(ex.Message);
        }finally{
             reader.Close();
            conn.Close();
        }        
   
    if (iQualifyCounter > 0) 
    {
        hasBeenDownloaded =true;
    }

        return hasBeenDownloaded;
    }

    #endregion

    protected void cbxShowUserOrNot_CheckedChanged(object sender, EventArgs e)
    {

    }

    #region HeEmailiT
    private void sendEMail(string strMailBody, string strMailToAddress)
    {

        //PɱHe޲z̻PϥΪ  
        String strTo = "<jiing.deng@cathaybk.com.tw>;" + strMailToAddress;
        //ӷO@Τ޲ztΩM޲z
        String strFrom = "<CCOM@cathaybk.com.tw>";
        String strMailSubject = "[@Τ]@Τ޲ztΪAqHI";

        if (String.IsNullOrEmpty(strMailBody))
        {
            strMailBody = "oOѦ@Τ޲ztγqT</b>A<b> "+ Context.User.Identity.Name +" /khzҰ^m@FצpUG<br>" + tbxComment.Text;

        }
        System.Net.Mail.SmtpClient client = new SmtpClient();
        client.Host = "PIEX2K05.cathaybk.intra.uwccb";
        client.Port = 25;
        client.UseDefaultCredentials = false;
        client.Credentials = new System.Net.NetworkCredential("uid", "pwd");  //uid, pwd please replace as yours
        client.DeliveryMethod = SmtpDeliveryMethod.Network;

        System.Net.Mail.MailMessage message = new MailMessage(strFrom, strTo, strMailSubject, strMailBody);
        message.BodyEncoding = System.Text.Encoding.UTF8;
        message.IsBodyHtml = true;

        try
        {
            client.Send(message);
            Response.Write("<font color=\"blue\">Email successfully sent.</font>");
        }
        catch (Exception ex)
        {
            Response.Write("Send Email Failed." + ex.ToString()); ;
        }
    }
    #endregion
}