Пример #1
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(AgentFee entity)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO agent_Fee (agentNo,agentName,agentFeeSeq,agentFeeMonth,");
            for(int i=1; i<=100;i++){
                sb.Append("feeName").Append(i.ToString()).Append(",").Append("fee").Append(i.ToString()).Append(",");
            }

            sb.Append("feeTotal,invoiceFee,preInvoiceFee) VALUE (@agentNo,@agentName, @agentFeeSeq,@agentFeeMonth,");
            for (int i = 1; i <= 100; i++)
            {
                sb.Append("@feeName").Append(i.ToString()).Append(",").Append("@fee").Append(i.ToString()).Append(",");
            }
            sb.Append("@feeTotal,@invoiceFee,@preInvoiceFee)");

            //string sql = "INSERT INTO agent_Fee (agentNo, agentFeeSeq,feeName1,fee1,feeName2,fee2,feeName3,fee3,feeName4,fee4,feeTotal) VALUE (@agentNo, @agentFeeSeq,@feeName1,@fee1,@feeName2,@fee2,@feeName3,@fee3,@feeName4,@fee4,@feeTotal)";
            string sql = sb.ToString();
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
              
                command.Parameters.AddWithValue("@agentNo", entity.agentNo);
                command.Parameters.AddWithValue("@agentName", entity.agentName);
                command.Parameters.AddWithValue("@agentFeeSeq", entity.agentFeeSeq);

                command.Parameters.AddWithValue("@agentFeeMonth", entity.agentFeeMonth);

                for (int j = 1; j <= 100; j++)
                {
                    FieldInfo feeNameField = entity.GetType().GetField("feeName" + j);
                    FieldInfo feeField = entity.GetType().GetField("fee" + j);
                    String feeNameFieldValue = feeNameField.GetValue(entity) == null ? null : feeNameField.GetValue(entity).ToString();

                    String feeFieldValue = feeField.GetValue(entity) == null ? null : feeField.GetValue(entity).ToString();
                   

                    command.Parameters.AddWithValue("@feeName" + j, feeNameFieldValue);
                    command.Parameters.AddWithValue("@fee" + j, feeFieldValue);
                }
                

                command.Parameters.AddWithValue("@feeTotal", entity.feeTotal);
                command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
                command.Parameters.AddWithValue("@preInvoiceFee", entity.preInvoiceFee);
                int i = command.ExecuteNonQuery();
                mycn.Close();
                mycn.Dispose();
                return i;
            }
        }
Пример #2
0
        /// <summary>
        /// 异步 开始事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            //需要执行的代码
           

            worker.ReportProgress(1, "开始导入代理商佣金...\r\n");

            //导入代理商佣金
            AgentFeeDao agentFeeDao = new AgentFeeDao();
            for (int i = 0; i < dgAgentFee.RowCount; i++)
            {
                AgentFee agentFee = new AgentFee();
                agentFee.agentFeeMonth = this.dtFeeMonth.Value.ToString("yyyy-MM");
                agentFee.agentNo = dgAgentFee[0, i].Value.ToString();
                agentFee.agentName= dgAgentFee[1, i].Value.ToString();
                agentFee.agentFeeSeq = agentFee.agentNo + this.dtFeeMonth.Value.ToString("yyyyMM") + String.Format("{0:D5}", i+1);
                agentFee.feeTotal = dgAgentFee[dgAgentFee.Columns.Count - 3, i].Value.ToString();
                agentFee.invoiceFee = dgAgentFee[dgAgentFee.Columns.Count - 2, i].Value.ToString();
                agentFee.preInvoiceFee = dgAgentFee[dgAgentFee.Columns.Count - 1, i].Value.ToString();

                for (int j = 2; j <= 101 && j < dgAgentFee.Columns.Count-3; j++)
                {

                    FieldInfo feeNameField = agentFee.GetType().GetField("feeName" + (j-1));
                    FieldInfo feeField = agentFee.GetType().GetField("fee" + (j-1));

                    String feeNameFieldValue = dgAgentFee.Columns[j].HeaderCell.Value.ToString();
                    String feeFieldValue = dgAgentFee[j, i].Value.ToString();
                    if (feeFieldValue.Trim().Equals("0") || String.IsNullOrWhiteSpace(feeFieldValue))
                    {
                        feeFieldValue = null;
                    }
                    feeNameField.SetValue(agentFee, feeNameFieldValue);
                    feeField.SetValue(agentFee, feeFieldValue);

                }

                agentFeeDao.Delete(agentFee);
                agentFeeDao.Add(agentFee);

            }
            WechatAction wechatAction = new WechatAction();
            wechatAction.sendTextMessageToWechat("@all", this.dtFeeMonth.Value.ToString("yyyy年MM月") + "佣金已发布,请通过底部菜单查询佣金详情", Settings.Default.Wechat_Secret, MyConstant.APP_Payment);

            worker.ReportProgress(2, "导入代理商佣金完成...\r\n");
           
           

            //MessageBox.Show("数据上传完毕");

        }
        private StringBuilder createAgentFeeNewsMessages(String feeMonth, AgentFee agentFee, String agentNo)
        {
            WechatQueryLog wechatQueryLog = new ChinaUnion_BO.WechatQueryLog();
            wechatQueryLog.agentName = "";
            wechatQueryLog.module = Util.MyConstant.module_Commission;
            wechatQueryLog.subSystem = "佣金查询";
            wechatQueryLog.queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            wechatQueryLog.queryString = feeMonth;
            wechatQueryLog.wechatId = agentNo;
            WechatQueryLogDao wechatQueryLogDao = new WechatQueryLogDao();
            try
            {
                wechatQueryLogDao.Add(wechatQueryLog);
            }
            catch
            {
            }

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("<MsgType><![CDATA[news]]></MsgType>");
            sb.AppendFormat("<ArticleCount>1</ArticleCount>");
            sb.AppendFormat("<Articles>");

            sb.AppendFormat("<item>");
            sb.Append("<Title>").AppendFormat("{0}佣金告知单", feeMonth).Append("</Title>");

            StringBuilder sbDesc = new StringBuilder();
            //sbDesc.AppendFormat("本月佣金告知单({0})", feeMonth);
            sbDesc.AppendFormat("告知单编号:{0}\n", agentFee.agentFeeSeq);
            sbDesc.AppendFormat("合作伙伴编号:{0}\n", agentFee.agentNo);
            sbDesc.AppendFormat("合作伙伴名字:{0}\n", agentFee.agentName);
            sbDesc.AppendFormat("渠道类型:{0}\n", agentFee.agent.agentType);

            // sb1.AppendFormat("佣金\n\n");

            sbDesc.AppendFormat("佣金明细:\n");
            int i = 1;
            for (int j = 1; j <= 100; j++)
            {
                FieldInfo feeNameField = agentFee.GetType().GetField("feeName" + j);
                FieldInfo feeField = agentFee.GetType().GetField("fee" + j);
                if (feeNameField != null && feeField != null)
                {
                    String feeNameFieldValue = feeNameField.GetValue(agentFee) == null ? null : feeNameField.GetValue(agentFee).ToString();

                    String feeFieldValue = feeField.GetValue(agentFee) == null ? null : feeField.GetValue(agentFee).ToString();

                    if (!String.IsNullOrEmpty(feeFieldValue) && !String.IsNullOrWhiteSpace(feeFieldValue))
                    {
                        sbDesc.Append("  ").Append(i++).AppendFormat(".{0}", feeNameFieldValue).Append(" ").AppendFormat("{0}\n", feeFieldValue);

                    }
                }


            }
            sbDesc.Append("  ").Append(i++).AppendFormat(".{0}", "佣金总计").Append(" ").AppendFormat("{0}\n", agentFee.feeTotal);
            sbDesc.Append("  ").Append(i++).AppendFormat(".{0}", "过网开票金额").Append(" ").AppendFormat("{0}\n", agentFee.preInvoiceFee);




            char[] separator = "<br>".ToCharArray();

            if (!String.IsNullOrEmpty(agentFee.agent.agentTypeComment))
            {
                sbDesc.AppendFormat("\n本月佣金说明:\n");
                string[] agentTypeCommentList = agentFee.agent.agentTypeComment.Split(separator, StringSplitOptions.RemoveEmptyEntries);
                for (int count = 0; count < agentTypeCommentList.Length; count++)
                {
                    sbDesc.Append("        ").Append(count + 1).AppendFormat(".{0}\n", agentTypeCommentList[count]);
                }
            }
            sb.Append("<Description>").AppendFormat("<![CDATA[{0}]]>", sbDesc.ToString()).Append("</Description>");



            String url1 = String.Format("http://{0}/Wechat/AgentFeeQuery.aspx?agentNo={1}&feeMonth={2}", Properties.Settings.Default.Host, QueryStringEncryption.Encode(agentNo, QueryStringEncryption.key), QueryStringEncryption.Encode(feeMonth, QueryStringEncryption.key));
            logger.Info(url1);
            sb.Append("<Url>").AppendFormat("<![CDATA[{0}]]>", url1).Append("</Url>");
            sb.AppendFormat("</item>");

            sb.AppendFormat("</Articles>");
            return sb;
        }
Пример #4
0
         /// <summary> 
        /// 查询集合 
        /// </summary> 
        /// <returns></returns> 
        public AgentFee GetByKey(String agentFeeMonth, string agentNo)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT t1.agentNo,t1.agentName, t1.agentFeeSeq,");
            for (int i = 1; i <= 100; i++)
            {
                sb.Append("t1.feeName").Append(i.ToString()).Append(",").Append("t1.fee").Append(i.ToString()).Append(",");
            }

            sb.Append("feeTotal,invoiceFee,preInvoiceFee, (select group_concat(distinct t3.agentType separator ';')  from agent_type t3 where t1.agentNo = t3.agentNo and t3.agentFeeMonth=@agentFeeMonth) agentType,");
            sb.Append("(select group_concat(distinct t4.agentTypeComment separator '<br>') from agent_type_comment t4 , agent_type t5 where t1.agentNo = t5.agentNo and  t4.agentType = t5.agentType and t4.agentFeeMonth=t5.agentFeeMonth and t4.agentFeeMonth=@agentFeeMonth) agentTypeComment");
           // sb.Append("t2.agentName,t2.contactEmail,t2.contactName,t2.contactTel");

            sb.Append(" FROM agent_Fee t1 where agentFeeMonth=@agentFeeMonth");

           // sb.Append("  and t1.agentNo= t2.agentNo ");
            sb.Append("  and t1.agentNo= @agentNo ");
           // sb.Append("  and t2.status!='Y'");
            string sql = sb.ToString();// "SELECT agentNo, agentFeeSeq,feeName1,fee1,feeName2,fee2,feeName3,fee3,feeName4,fee4,feeTotal FROM agent_Fee";
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
                command.Parameters.AddWithValue("@agentFeeMonth", agentFeeMonth);
                command.Parameters.AddWithValue("@agentNo", agentNo);
                MySqlDataReader reader = command.ExecuteReader();
               
                AgentFee agentFee = null;
                if (reader.Read())
                {
                    agentFee = new AgentFee();
                    agentFee.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
                    agentFee.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
                    agentFee.agentFeeSeq = reader["agentFeeSeq"] == DBNull.Value ? null : reader["agentFeeSeq"].ToString();
                    agentFee.agentFeeMonth = agentFeeMonth;
                    for (int i = 1; i <= 100; i++)
                    {
                        FieldInfo feeNameField = agentFee.GetType().GetField("feeName" + i);
                        FieldInfo feeField = agentFee.GetType().GetField("fee" + i);
                        String feeNameFieldValue = reader["feeName"+i] == DBNull.Value ? null : reader["feeName"+i].ToString();
                        String feeFieldValue = reader["fee"+i] == DBNull.Value ? null : reader["fee"+i].ToString();
                        feeNameField.SetValue(agentFee, feeNameFieldValue);
                        feeField.SetValue(agentFee, feeFieldValue);                   

                    }                  


                    agentFee.feeTotal = reader["feeTotal"] == DBNull.Value ? null : reader["feeTotal"].ToString();
                    agentFee.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString();
                    agentFee.preInvoiceFee = reader["preInvoiceFee"] == DBNull.Value ? null : reader["preInvoiceFee"].ToString();

                    Agent agent = new Agent();
                    agent.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
                  //  agent.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString();
                    //agent.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString();
                  //  agent.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString();
                    agent.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString();
                    agent.agentTypeComment = reader["agentTypeComment"] == DBNull.Value ? null : reader["agentTypeComment"].ToString();

                    agentFee.agent = agent;

                    
                }
                mycn.Close();
                return agentFee;
            }
        }