Пример #1
0
 /// <summary> 
 /// 删除数据 
 /// </summary> 
 /// <param name="entity"></param> 
 /// <returns></returns> 
 public int Delete(MailJob entity)
 {
     string sql = "DELETE FROM mail_job WHERE mailJobId=@mailJobId";
     using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
     {
         mycn.Open();
         MySqlCommand command = new MySqlCommand(sql, mycn);
         command.Parameters.AddWithValue("@mailJobId", entity.mailJobId);
         int i = command.ExecuteNonQuery();
         mycn.Close();
         mycn.Dispose();
         return i;
     }
 }
Пример #2
0
        public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8";
        /// <summary> 
        /// 添加数据 
        /// </summary> 
        /// <returns></returns> 
        public int Add(MailJob entity)
        {


            string sql = "INSERT INTO mail_job (mailJobId,feeMonth, subject, sendTime) VALUE (@mailJobId,@feeMonth,@subject, @sendTime)";
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
                command.Parameters.AddWithValue("@mailJobId", entity.mailJobId);
                command.Parameters.AddWithValue("@feeMonth", entity.feeMonth);
                command.Parameters.AddWithValue("@subject", entity.subject);
                command.Parameters.AddWithValue("@sendTime", entity.sendTime);

                int i = command.ExecuteNonQuery();
                mycn.Close();
                mycn.Dispose();
                return i;
            }
        }
Пример #3
0
               /// <summary>
        /// 异步 开始事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            //需要执行的代码
            Cursor.Current = Cursors.WaitCursor;
            if (dgAgentFee.RowCount <= 0)
            {
                return;
            }

            try{
             MailData mailData = new MailData();


            StringBuilder sbAgent = new StringBuilder();

            worker.ReportProgress(1, "准备数据...\r\n");
            WechatAction wechatAction = new WechatAction();
            for (int i = 0; i < dgAgentFee.RowCount; i++)
            {
                if (dgAgentFee[4, i].Value == null)
                {
                    continue;
                }
                if (String.IsNullOrEmpty(dgAgentFee[4, i].Value.ToString()))
                {
                    continue;
                }
                StringBuilder sb = new StringBuilder();
                String url = String.Format(Settings.Default.Wechat_Message, dgAgentFee[0, i].Value.ToString(), feeMonth);
                wechatAction.sendTextMessageToWechat(dgAgentFee[0, i].Value.ToString(), feeMonth + url, Settings.Default.Wechat_Secret, Settings.Default.Wechat_Agent_AppId);

                sb.Append("agent_no#").Append(dgAgentFee[0, i].Value == null ? "" : dgAgentFee[0, i].Value.ToString()).Append(",");
                sb.Append("agent_name#").Append(dgAgentFee[1, i].Value == null ? "" : dgAgentFee[1, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                sb.Append("agent_type#").Append(dgAgentFee[2, i].Value == null ? "" : dgAgentFee[2, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                sb.Append("agent_type_comment#").Append(dgAgentFee[3, i].Value == null ? "" : dgAgentFee[3, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                sb.Append("email#").Append(dgAgentFee[4, i].Value == null ? "" : dgAgentFee[4, i].Value.ToString());

                mailData.ContactJsonList.Add(sb.ToString());
            }

            String client = Settings.Default.TripolisClient;
            String userName = Settings.Default.TripoisUserName;
            String password = Settings.Default.TripolisPassword;


            mailData.fromAddress = Settings.Default.MailFromAddress;
            mailData.replyAddress = Settings.Default.MailReplyAddress;
            mailData.sender = Settings.Default.MailSender;
            mailData.subject = this.subject;
            ChinaUnionAdapter mailAdapter = new ChinaUnionAdapter(client, userName, password, null);
            String databaseId = Settings.Default.TripolisDBId;
            String workspaceId = Settings.Default.TripolisWorkspaceId;
            String emailId = Settings.Default.TripolisDirectEmailId;
            worker.ReportProgress(1, "发送邮件...\r\n");
            String message = mailAdapter.sendBatchMail(databaseId, workspaceId, emailId, mailData, this.dtFeeMonth.Value);
            if (message.Contains("OK:"))
            {
                String mailJobId = message.Substring(3);

                MailJob mailJob = new MailJob();
                mailJob.feeMonth = feeMonth;
                mailJob.mailJobId = mailJobId;
                mailJob.subject = mailData.subject;

                mailJob.sendTime = this.dtFeeMonth.Value.ToString("yyyy-MM-dd hh:mm:ss");


                MailJobDao mailJobDao = new MailJobDao();
                mailJobDao.Delete(mailJob);
                mailJobDao.Add(mailJob);

             //  WechatAction wechatAction = new WechatAction();
              //  wechatAction.sendMessageToWechat("@all",this.dtFeeMonth.Value.ToString("yyyy-MM") + Settings.Default.Wechat_Message);
                MessageBox.Show("邮件重新发送成功");

            }
            else
            {
                MessageBox.Show(message);
            }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            Cursor.Current = Cursors.Default;
        }
Пример #4
0
        /// <summary> 
        /// 根据主键查询 
        /// </summary> 
        /// <param name="primaryKey"></param> 
        /// <returns></returns> 
        //public Agent Get(int primaryKey)
        //{
        //    string sql = "SELECT userid,userNickName FROM cimuser where userid=@userid";
        //    using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
        //    {
        //        mycn.Open();
        //        MySqlCommand command = new MySqlCommand(sql, mycn);
        //        command.Parameters.AddWithValue("@userid", primaryKey);
        //        MySqlDataReader reader = command.ExecuteReader();
        //        Agent userBase = null;
        //        if (reader.Read())
        //        {
        //            userBase = new Agent();
        //            userBase.UserId = Convert.ToInt32(reader["userid"]);
        //            userBase.UserNickName = reader["userNickName"] == DBNull.Value ? null : reader["userNickName"].ToString();
        //        }
        //        return userBase;
        //    }
        //}
        /// <summary> 
        /// 查询集合 
        /// </summary> 
        /// <returns></returns> 
        public IList<MailJob> GetList(String feeMonth)
        {
            string sql = "SELECT mailJobId,feeMonth, subject, sendTime FROM mail_job ";
            if (!String.IsNullOrEmpty(feeMonth))
            {
                sql = sql + " where feeMonth=@feeMonth";
            }
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
                if (!String.IsNullOrEmpty(feeMonth))
                {
                    command.Parameters.AddWithValue("@feeMonth", feeMonth);
                }
                MySqlDataReader reader = command.ExecuteReader();
                IList<MailJob> list = new List<MailJob>();
                MailJob mailJob = null;
                while (reader.Read())
                {
                    mailJob = new MailJob();

                    mailJob.mailJobId = reader["mailJobId"] == DBNull.Value ? null : reader["mailJobId"].ToString();
                    mailJob.feeMonth = reader["feeMonth"] == DBNull.Value ? null : reader["feeMonth"].ToString();
                    mailJob.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString();
                    mailJob.sendTime = reader["sendTime"] == DBNull.Value ? null : reader["sendTime"].ToString();

                    list.Add(mailJob);
                }
                mycn.Close();
                return list;
            }
        }
Пример #5
0
        /// <summary>
        /// 异步 开始事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            //需要执行的代码
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                String client = Settings.Default.TripolisClient;
                String userName = Settings.Default.TripoisUserName;
                String password = Settings.Default.TripolisPassword;

                ChinaUnionAdapter mailAdapter = new ChinaUnionAdapter(client, userName, password, null);
                MailData mailData = new MailData();
                mailData.fromAddress = Settings.Default.MailFromAddress;
                mailData.replyAddress = Settings.Default.MailReplyAddress;
                mailData.sender = Settings.Default.MailSender;
                mailData.subject = this.subject;

                String databaseId = Settings.Default.TripolisDBId;
                String workspaceId = Settings.Default.TripolisWorkspaceId;
                String emailId = Settings.Default.TripolisDirectEmailId;
                worker.ReportProgress(1, "准备数据...\r\n");
                WechatAction wechatAction = new WechatAction();



               
               
                for (int i = 0; i < dgAgentFee.RowCount; i++)
                {
                    if (dgAgentFee[4, i].Value == null)
                    {
                        continue;
                    }
                    if (String.IsNullOrEmpty(dgAgentFee[4, i].Value.ToString()))
                    {
                        continue;
                    }

                    StringBuilder sb = new StringBuilder();

                    String url = String.Format(Settings.Default.Wechat_Message, dgAgentFee[0, i].Value.ToString(), this.dateTimePicker1.Value.ToString("yyyy-MM"));
                    wechatAction.sendTextMessageToWechat(dgAgentFee[0, i].Value.ToString(), this.feeMonth + url, Settings.Default.Wechat_Secret, Settings.Default.Wechat_Agent_AppId);

                    sb.Append("agent_no#").Append(dgAgentFee[0, i].Value == null ? "" : dgAgentFee[0, i].Value.ToString()).Append(",");
                    sb.Append("agent_name#").Append(dgAgentFee[1, i].Value == null ? "" : dgAgentFee[1, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                    sb.Append("agent_type#").Append(dgAgentFee[2, i].Value == null ? "" : dgAgentFee[2, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                    sb.Append("agent_type_comment#").Append(dgAgentFee[3, i].Value == null ? "" : dgAgentFee[3, i].Value.ToString().Replace(",","、").Replace("#","%")).Append(",");
                    sb.Append("email#").Append(dgAgentFee[4, i].Value == null ? "" : dgAgentFee[4, i].Value.ToString()).Append(",");
                    sb.Append("contact_name#").Append(dgAgentFee[5, i].Value == null ? "" : dgAgentFee[5, i].Value.ToString().Replace(",", "、").Replace("#", "%")).Append(",");
                    sb.Append("agent_fee_seq#").Append(dgAgentFee[6, i].Value == null ? "" : dgAgentFee[6, i].Value.ToString()).Append(",");
                    sb.Append("feemonth#").Append(this.feeMonth).Append(",");
                   
                    ///

                    Dictionary<String, Dictionary<String, String>> CategoryMap = new Dictionary<string, Dictionary<String, String>>();
                    //按结账科目分类
                    int rowIndex = i;
                    for (int j = 7; j < dgAgentFee.ColumnCount - 3; j++)
                    {

                        String headText = dgAgentFee.Columns[j].HeaderText;
                        int locationIndex = headText.IndexOf("-");
                        int endIndex = headText.IndexOf("(");
                        if (locationIndex == -1)
                        {
                            locationIndex = headText.IndexOf("-");
                        }
                        if (endIndex == -1)
                        {
                            endIndex = headText.IndexOf("(");
                        }
                        String key = dgAgentFee.Columns[j].HeaderText.Substring(locationIndex + 1);
                        if (endIndex != -1)
                        {
                            key = dgAgentFee.Columns[j].HeaderText.Substring(locationIndex + 1, endIndex - locationIndex - 1);
                        }
                        String value = dgAgentFee[j, rowIndex].Value == null ? "" : dgAgentFee[j, rowIndex].Value.ToString();
                        if (!CategoryMap.ContainsKey(key))
                        {
                            Dictionary<String, String> valueMap = new Dictionary<string, string>();
                            valueMap.Add(headText, value);
                            CategoryMap.Add(key, valueMap);
                        }
                        else
                        {
                            Dictionary<String, String> valueMap = CategoryMap[key];
                            valueMap.Add(headText, value);
                        }

                    }



                    //int index = 1;
                    int feeSeq = 1;
                    int seq = 1;
                    foreach (String itemKey in CategoryMap.Keys)
                    {
                      
                        Dictionary<String, String> valueMap = CategoryMap[itemKey];
                        float subTotal = 0;
                        foreach (String value in valueMap.Values)
                        {
                            if (!String.IsNullOrEmpty(value))
                            {
                                subTotal = subTotal + float.Parse(value);
                            }
                           
                        }
                        sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                        sb.Append("fee_name").Append(feeSeq.ToString()).Append("#<b>").Append(itemKey.Replace(",", "、").Replace("#", "%")).Append("</b>,");
                        if (subTotal<=0)
                        {
                            sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                        }
                        else
                        {
                            sb.Append("fee").Append(feeSeq.ToString()).Append("#<b>").Append(subTotal).Append("</b>,");
                           
                        }
                        feeSeq++;

                        foreach (String subKey in valueMap.Keys)
                        {

                            sb.Append("fee_name").Append(feeSeq.ToString()).Append("#").Append(subKey.Replace(",", "、").Replace("#", "%")).Append(",");
                            if (String.IsNullOrEmpty(valueMap[subKey]) ||(!String.IsNullOrEmpty(valueMap[subKey]) && valueMap[subKey].Equals("0")))
                            {
                                sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                                sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                            }
                            else
                            {
                                sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append(seq.ToString()).Append(",");
                                sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append(valueMap[subKey]).Append(",");
                                seq++;
                            }
                           
                            feeSeq++;
                        }
                       
                    }




                    for (int j = feeSeq; j <= 101; j++)
                    {
                        sb.Append("fee_seq").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                        sb.Append("fee_name").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                        sb.Append("fee").Append(feeSeq.ToString()).Append("#").Append("").Append(",");
                        feeSeq++;
                    }

                    sb.Append("fee_total_seq").Append("#").Append(seq.ToString()).Append(",");
                    sb.Append("fee_total#").Append(dgAgentFee[dgAgentFee.Columns.Count - 3, i].Value.ToString()).Append(",");

                    seq++;
                    sb.Append("invoice_fee_seq").Append("#").Append(seq.ToString()).Append(",");
                    sb.Append("invoice_fee#").Append(dgAgentFee[dgAgentFee.Columns.Count - 2, i].Value.ToString()).Append(",");

                    seq++;
                    sb.Append("pre_invoice_fee_seq").Append("#").Append(seq.ToString()).Append(",");
                    sb.Append("pre_invoice_fee#").Append(dgAgentFee[dgAgentFee.Columns.Count - 1, i].Value.ToString());

                    mailData.ContactJsonList.Add(sb.ToString());

                    Cursor.Current = Cursors.Default;
                }

                worker.ReportProgress(2, "发送邮件...\r\n");
                String message = mailAdapter.sendBatchMail(databaseId, workspaceId, emailId, mailData, dateTimePicker1.Value);
                if (message.Contains("OK:"))
                {
                    String mailJobId = message.Substring(3);

                    MailJob mailJob = new MailJob();
                    mailJob.feeMonth = this.feeMonth;
                    mailJob.mailJobId = mailJobId;
                    mailJob.subject = mailData.subject;
                    if (dateTimePicker1.Value != null)
                    {
                        mailJob.sendTime = dateTimePicker1.Value.ToString("yyyy-MM-dd hh:mm:ss");
                    }

                    MailJobDao mailJobDao = new MailJobDao();
                    mailJobDao.Delete(mailJob);
                    mailJobDao.Add(mailJob);

                    MessageBox.Show("邮件发送成功");
                }
                else
                {
                    MessageBox.Show(message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            //this.Close();
        }