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(AgentMonthPerformance entity) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO agent_month_performance (type,branchNo, branchName,agentNo,agentName,"); for (int i = 1; i <= 100; i++) { sb.Append("feeName").Append(i.ToString()).Append(",").Append("fee").Append(i.ToString()).Append(","); } sb.Append("month) VALUE (@type,@branchNo, @branchName,@agentNo,@agentName,"); for (int i = 1; i <= 100; i++) { sb.Append("@feeName").Append(i.ToString()).Append(",").Append("@fee").Append(i.ToString()).Append(","); } sb.Append("@month)"); //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("@type", entity.type); command.Parameters.AddWithValue("@branchNo", entity.branchNo); command.Parameters.AddWithValue("@branchName", entity.branchName); command.Parameters.AddWithValue("@agentNo", entity.agentNo); command.Parameters.AddWithValue("@agentName", entity.agentName); 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("@month", entity.month); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 删除数据 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Delete(AgentMonthPerformance entity) { string sql = "DELETE FROM agent_month_performance WHERE branchNo=@branchNo and month =@month"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@branchNo", entity.branchNo); command.Parameters.AddWithValue("@month", entity.month); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 异步 开始事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void worker_DoWork(object sender, DoWorkEventArgs e) { //需要执行的代码 worker.ReportProgress(1, "开始导入月度绩效...\r\n"); //导入非直供 AgentMonthPerformanceDao agentMonthPerformanceDao = new AgentMonthPerformanceDao(); for (int i = 0; i < dgAgentPerformanceNoDirect.RowCount; i++) { AgentMonthPerformance agentMonthPerformance = new AgentMonthPerformance(); agentMonthPerformance.month = this.dtMonth.Value.ToString("yyyy-MM"); agentMonthPerformance.branchNo = dgAgentPerformanceNoDirect[0, i].Value.ToString(); agentMonthPerformance.branchName = dgAgentPerformanceNoDirect[1, i].Value.ToString(); int index = 4; agentMonthPerformance.agentNo = dgAgentPerformanceNoDirect[2, i].Value.ToString(); agentMonthPerformance.agentName = dgAgentPerformanceNoDirect[3, i].Value.ToString(); agentMonthPerformance.type = "非直供渠道"; for (int j = index; j <= 101 && j < dgAgentPerformanceNoDirect.Columns.Count; j++) { FieldInfo feeNameField = agentMonthPerformance.GetType().GetField("feeName" + (j - index + 1)); FieldInfo feeField = agentMonthPerformance.GetType().GetField("fee" + (j - index + 1)); String feeNameFieldValue = dgAgentPerformanceNoDirect.Columns[j].HeaderCell.Value.ToString(); String feeFieldValue = dgAgentPerformanceNoDirect[j, i].Value.ToString(); if (feeFieldValue.Trim().Equals("0") || String.IsNullOrWhiteSpace(feeFieldValue)) { feeFieldValue = null; } feeNameField.SetValue(agentMonthPerformance, feeNameFieldValue); feeField.SetValue(agentMonthPerformance, feeFieldValue); } agentMonthPerformanceDao.Delete(agentMonthPerformance); agentMonthPerformanceDao.Add(agentMonthPerformance); } //导入直供 //AgentMonthPerformanceDao agentMonthPerformanceDao = new AgentMonthPerformanceDao(); for (int i = 0; i < dgAgentPerformanceDirect.RowCount; i++) { AgentMonthPerformance agentMonthPerformance = new AgentMonthPerformance(); agentMonthPerformance.type = "直供渠道"; agentMonthPerformance.month = this.dtMonth.Value.ToString("yyyy-MM"); agentMonthPerformance.branchNo = dgAgentPerformanceDirect[0, i].Value.ToString(); agentMonthPerformance.branchName = dgAgentPerformanceDirect[1, i].Value.ToString(); int index = 2; // agentMonthPerformance.agentNo = dgAgentPerformanceDirect[0, i].Value.ToString(); //agentMonthPerformance.agentName = dgAgentPerformanceDirect[1, i].Value.ToString(); for (int j = index; j <= 101 && j < dgAgentPerformanceDirect.Columns.Count; j++) { FieldInfo feeNameField = agentMonthPerformance.GetType().GetField("feeName" + (j - index + 1)); FieldInfo feeField = agentMonthPerformance.GetType().GetField("fee" + (j - index + 1)); String feeNameFieldValue = dgAgentPerformanceDirect.Columns[j].HeaderCell.Value.ToString(); String feeFieldValue = dgAgentPerformanceDirect[j, i].Value.ToString(); if (feeFieldValue.Trim().Equals("0") || String.IsNullOrWhiteSpace(feeFieldValue)) { feeFieldValue = null; } feeNameField.SetValue(agentMonthPerformance, feeNameFieldValue); feeField.SetValue(agentMonthPerformance, feeFieldValue); } agentMonthPerformanceDao.Delete(agentMonthPerformance); agentMonthPerformanceDao.Add(agentMonthPerformance); } WechatAction wechatAction = new WechatAction(); wechatAction.sendTextMessageToWechat("@all", this.dtMonth.Value.ToString("yyyy年MM月") + "业绩已发布,请通过底部菜单查询业绩详情", Settings.Default.Wechat_Secret, MyConstant.APP_Performace); //MessageBox.Show("数据上传完毕"); }
private StringBuilder createNewsMessages(String feeMonth, String agentNo, AgentMonthPerformance agentMonthPerformance, String type) { 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", agentMonthPerformanceList.Count); if (!String.IsNullOrEmpty(agentMonthPerformance.agentNo)) { sbDesc.AppendFormat("代理商编号:" + agentMonthPerformance.agentNo + "\n代理商名称:" + agentMonthPerformance.agentName).Append("\n"); } if (!String.IsNullOrEmpty(agentMonthPerformance.branchNo)) { sbDesc.AppendFormat("渠道编号:" + agentMonthPerformance.branchNo + "\n渠道名称:" + agentMonthPerformance.branchName).Append("\n"); } sbDesc.AppendLine().AppendFormat("\n业绩汇总明细:\n"); for (int j = 1; j <= 100; j++) { FieldInfo feeNameField = agentMonthPerformance.GetType().GetField("feeName" + j); FieldInfo feeField = agentMonthPerformance.GetType().GetField("fee" + j); if (feeNameField != null && feeField != null) { String feeNameFieldValue = feeNameField.GetValue(agentMonthPerformance) == null ? null : feeNameField.GetValue(agentMonthPerformance).ToString(); String feeFieldValue = feeField.GetValue(agentMonthPerformance) == null ? null : feeField.GetValue(agentMonthPerformance).ToString(); if (!String.IsNullOrEmpty(feeFieldValue) && !String.IsNullOrWhiteSpace(feeFieldValue)) { // sbDesc.Append(" ").Append(i++).AppendFormat(".{0}", feeNameFieldValue).Append(" ").AppendFormat("{0}\n", feeFieldValue); if (feeNameFieldValue.Contains("后付费发展数") || feeNameFieldValue.Contains("预付费发展数") || feeNameFieldValue.Contains("总计")) { sbDesc.Append(" ").AppendFormat("{0}", feeNameFieldValue).Append(" ").AppendFormat("{0}\n", feeFieldValue); } else { sbDesc.Append(" ").AppendFormat(" {0}", feeNameFieldValue).Append(" ").AppendFormat("{0}\n", feeFieldValue); } } } } sbDesc.AppendFormat("\n查询时间:{0}\n", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sb.Append("<Description>").AppendFormat("<![CDATA[{0}]]>", sbDesc.ToString()).Append("</Description>"); String tempType = System.Web.HttpUtility.UrlEncode(type); String url1 = String.Format("http://{0}/Wechat/PerformanceMonthSummaryQuery.aspx?agentNo={1}&feeMonth={2}&type={3}", Properties.Settings.Default.Host, QueryStringEncryption.Encode(agentNo, QueryStringEncryption.key), QueryStringEncryption.Encode(feeMonth, QueryStringEncryption.key), QueryStringEncryption.Encode(tempType, QueryStringEncryption.key)); logger.Info(url1); sb.Append("<Url>").AppendFormat("<![CDATA[{0}]]>", url1).Append("</Url>"); sb.AppendFormat("</item>"); sb.AppendFormat("</Articles>"); return sb; }
/// <summary> /// 处理企业号的信息 /// </summary> /// <param name="context"></param> public void ProcessRequest(HttpContext context) { logger.Info(context.Request.Url.AbsoluteUri); string sToken = "PerformanceHandler"; string sCorpID = Properties.Settings.Default.Wechat_CorpId;// "wx4fe8b74e01fffcbb"; string sEncodingAESKey = "U7gOrkwP22ND4bIHSxU0WJqIestRcG2QroykyVKDUSG"; // string sToken = Properties.Settings.Default.Wechat_AgentFee_Token;//"AgentFee"; // string sCorpID = Properties.Settings.Default.Wechat_CorpId;// "wx31204de5a3ae758e"; // string sEncodingAESKey = Properties.Settings.Default.Wechat_AgentFee_EncodingAESKey;// "he8dYrZ5gLbDrDhfHVJkea1AfmHgRZQJq47kuKpQrSO"; System.Collections.Specialized.NameValueCollection queryStrings = context.Request.QueryString; Tencent.WXBizMsgCrypt wxcpt = new Tencent.WXBizMsgCrypt(sToken, sEncodingAESKey, sCorpID); context.Request.ContentEncoding = Encoding.UTF8; string sReqMsgSig = queryStrings["msg_signature"]; string sReqTimeStamp = queryStrings["timestamp"]; string sReqNonce = queryStrings["nonce"]; // 获取Post请求的密文数据 StreamReader reader = new StreamReader(context.Request.InputStream, Encoding.GetEncoding("UTF-8")); string sReqData = reader.ReadToEnd(); reader.Close(); string sMsg = ""; // 解析之后的明文 int ret = wxcpt.DecryptMsg(sReqMsgSig, sReqTimeStamp, sReqNonce, sReqData, ref sMsg); if (ret != 0) { logger.Info("ERR: Decrypt Fail, ret: " + ret); System.Console.WriteLine("ERR: Decrypt Fail, ret: " + ret); return; } // ret==0表示解密成功,sMsg表示解密之后的明文xml串 XmlDocument doc = new XmlDocument(); doc.LoadXml(sMsg); WechatMessage wechatMessage = new WechatMessage(doc.DocumentElement); // 需要发送的明文 String actionType = wechatMessage.EventKey; StringBuilder sb = new StringBuilder(); sb.AppendFormat("<xml>"); sb.AppendFormat("<ToUserName><![CDATA[{0}]]></ToUserName>", wechatMessage.FromUserName); sb.AppendFormat("<FromUserName><![CDATA[{0}]]></FromUserName>", wechatMessage.ToUserName); sb.AppendFormat("<CreateTime>{0}</CreateTime>", wechatMessage.CreateTime); // string sRespData = "<MsgId>1234567890123456</MsgId>"; logger.Info("EventKey: " + wechatMessage.EventKey); AgentWechatAccountDao agentWechatAccountDao = new AgentWechatAccountDao(); AgentWechatAccount agentWechatAccount = agentWechatAccountDao.Get(wechatMessage.FromUserName); if (agentWechatAccount != null && wechatMessage != null && !String.IsNullOrEmpty(wechatMessage.Event) && wechatMessage.Event.Equals("enter_agent")) { WechatQueryLog wechatQueryLog = new ChinaUnion_BO.WechatQueryLog(); wechatQueryLog.agentName = ""; wechatQueryLog.module = Util.MyConstant.module_Performance; wechatQueryLog.subSystem = "业绩查询"; wechatQueryLog.queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); wechatQueryLog.queryString = "成员进入应用"; wechatQueryLog.wechatId = agentWechatAccount.contactId; WechatQueryLogDao wechatQueryLogDao = new WechatQueryLogDao(); try { wechatQueryLogDao.Add(wechatQueryLog); } catch { } } if (agentWechatAccount != null && !String.IsNullOrEmpty(agentWechatAccount.status) && !agentWechatAccount.status.Equals("Y")) { sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "对不起,你的账号已被停用,请联系联通工作人员!\n\n"); } else if (agentWechatAccount == null) { sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "用户不存在,请联系联通工作人员!\n\n"); } else { String agentNo = agentWechatAccount.branchNo; if (String.IsNullOrEmpty(agentNo)) { agentNo = agentWechatAccount.agentNo; } String agentType = agentWechatAccount.type; AgentMonthPerformanceDao agentMonthPerformanceDao = new ChinaUnion_DataAccess.AgentMonthPerformanceDao(); AgentDailyPerformanceDao agentDailyPerformanceDao = new ChinaUnion_DataAccess.AgentDailyPerformanceDao(); AgentStarDao agentStarDao = new AgentStarDao(); IList<AgentStar> agentStarList = null; AgentScoreDao agentScoreDao = new AgentScoreDao(); IList<AgentScore> agentScoreList = null; String dateTime = ""; DateTime dt = DateTime.Now.AddMonths(-3); //当前时间 DateTime startQuarter = dt.AddMonths(0 - (dt.Month - 1) % 3).AddDays(1 - dt.Day); //本季度初 if (startQuarter.Month >= 1 && startQuarter.Month <= 3) { dateTime = startQuarter.Year + "年第一季度"; } if (startQuarter.Month >= 4 && startQuarter.Month <= 6) { dateTime = startQuarter.Year + "年第二季度"; } if (startQuarter.Month >= 7 && startQuarter.Month <= 9) { dateTime = startQuarter.Year + "年第三季度"; } if (startQuarter.Month >= 10 && startQuarter.Month <= 12) { dateTime = startQuarter.Year + "年第四季度"; } logger.Info("agentNo: " + agentNo); logger.Info("agentType: " + agentType); switch (actionType) { case "curQuaterStar": case "HistoryQuaterStar": if (actionType.Equals("curQuaterStar")) { agentStarList = agentStarDao.GetLatestByKeyword(agentNo, dateTime); } if (actionType.Equals("HistoryQuaterStar")) { agentStarList = agentStarDao.GetListByKeyword(agentNo); } if (agentStarList != null && agentStarList.Count > 0) { logger.Info("Exist Record: " + agentStarList.Count); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); StringBuilder sbContent = new StringBuilder(); sbContent.AppendFormat("星级查询详情").Append("\n"); for (int i = 0; i < agentStarList.Count;i++ ) { AgentStar agentStar = agentStarList[i]; sbContent.AppendFormat("\n时间:{0}", agentStar.dateTime).Append("\n"); // sbContent.AppendFormat("代理商编号:{0}", agentStar.agentNo).Append("\n"); //sbContent.AppendFormat("代理商名称:{0}", agentStar.agentName).Append("\n"); if (!String.IsNullOrEmpty(agentStar.branchNo)) { sbContent.AppendFormat("渠道编码:{0}", agentStar.branchNo).Append("\n"); sbContent.AppendFormat("渠道名称:{0}", agentStar.branchName).Append("\n"); } sbContent.AppendFormat("星级:{0}", agentStar.star).Append("\n"); } sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", sbContent.ToString()); // sb.Append(sbContent.ToString()); // sb.Append(this.createNewsMessages(feeDate, wechatMessage.FromUserName, agentDailyPerformance)); } else { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "本期无星级或尚未发布,请耐心等候\n\n"); } WechatQueryLog wechatQueryLog = new ChinaUnion_BO.WechatQueryLog(); wechatQueryLog.agentName = ""; wechatQueryLog.module = Util.MyConstant.module_Performance; wechatQueryLog.subSystem = "星级查询"; wechatQueryLog.queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); wechatQueryLog.queryString = dateTime; wechatQueryLog.wechatId = agentNo; WechatQueryLogDao wechatQueryLogDao = new WechatQueryLogDao(); try { wechatQueryLogDao.Add(wechatQueryLog); } catch { } break; case "curScore": case "HistoryScore": String month = DateTime.Now.AddMonths(-1).ToString("yyyyMM"); if (actionType.Equals("curScore")) { agentScoreList = agentScoreDao.GetLatestByKeyword(agentNo, month); } if (actionType.Equals("HistoryScore")) { agentScoreList = agentScoreDao.GetListByKeyword(agentNo); } if (agentScoreList != null && agentScoreList.Count > 0) { logger.Info("Exist Record: " + agentScoreList.Count); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); StringBuilder sbContent = new StringBuilder(); sbContent.AppendFormat("积分查询详情").Append("\n"); for (int i = 0; i < agentScoreList.Count; i++) { AgentScore agentScore = agentScoreList[i]; sbContent.AppendFormat("\n时间:{0}", agentScore.dateTime).Append("\n"); if (!String.IsNullOrEmpty(agentScore.agentNo)) { sbContent.AppendFormat("代理商编号:{0}", agentScore.agentNo).Append("\n"); sbContent.AppendFormat("代理商名称:{0}", agentScore.agentName).Append("\n"); } if (!String.IsNullOrEmpty(agentScore.branchNo)) { sbContent.AppendFormat("渠道编码:{0}", agentScore.branchNo).Append("\n"); sbContent.AppendFormat("渠道名称:{0}", agentScore.branchName).Append("\n"); } sbContent.AppendFormat("渠道积分:{0}", agentScore.score).Append("\n"); sbContent.AppendFormat("本月得分:{0}", agentScore.standardScore).Append("\n"); } sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", sbContent.ToString()); // sb.Append(sbContent.ToString()); // sb.Append(this.createNewsMessages(feeDate, wechatMessage.FromUserName, agentDailyPerformance)); } else { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "本期无积分或尚未发布,请耐心等候\n\n"); } wechatQueryLog = new ChinaUnion_BO.WechatQueryLog(); wechatQueryLog.agentName = ""; wechatQueryLog.module = Util.MyConstant.module_Performance; wechatQueryLog.subSystem = "积分查询"; wechatQueryLog.queryTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); wechatQueryLog.queryString = month; wechatQueryLog.wechatId = agentNo; wechatQueryLogDao = new WechatQueryLogDao(); try { wechatQueryLogDao.Add(wechatQueryLog); } catch { } break; case "YesterdayPerformance": String feeDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd"); AgentDailyPerformance agentDailyPerformance = new AgentDailyPerformance(); agentDailyPerformance = agentDailyPerformanceDao.GetSummary(agentNo, feeDate,agentType); if (agentDailyPerformance != null) { logger.Info("Exist Record: " + agentNo); sb.Append(this.createNewsMessages(feeDate, agentNo, agentDailyPerformance, agentType)); } else { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", DateTime.Now.AddDays(-1).ToString("yyyy年MM月dd日") + "无业绩或者业绩尚未发布\n\n"); } break; case "HistoryDayPerformance": String date = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("yyyy-MM-dd"); IList<AgentDailyPerformance> agentDailyPerformanceList = agentDailyPerformanceDao.GetAllListDate(agentNo, agentType,date); if (agentDailyPerformanceList == null || agentDailyPerformanceList.Count == 0) { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "近期无业绩或者业绩尚未发布!\n\n"); } else { sb.Append(this.createNewsMessages(agentNo, agentDailyPerformanceList, agentType)); } break; case "LastMonthPerformance": // case "YesterdayPerformance": String feeMonth = DateTime.Now.AddMonths(-1).ToString("yyyy-MM"); AgentMonthPerformance agentMonthPerformance = new AgentMonthPerformance(); agentMonthPerformance = agentMonthPerformanceDao.GetSummary(agentNo, feeMonth, agentType); if (agentMonthPerformance != null) { logger.Info("Exist Record: " + agentMonthPerformance.agentName); sb.Append(this.createNewsMessages(feeMonth, agentNo, agentMonthPerformance, agentType)); } else { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", feeMonth.Substring(0,4)+"年"+feeMonth.Substring(5,2)+ "月" + "业绩尚未发布,请耐心等待!\n\n"); } break; case "HistoryMonthPerformance": IList<AgentMonthPerformance> agentMonthPerformanceList = agentMonthPerformanceDao.GetAllListMonth(agentNo,agentType); if (agentMonthPerformanceList == null || agentMonthPerformanceList.Count == 0) { logger.Info("is not Existed Record: "); sb.AppendFormat("<MsgType><![CDATA[text]]></MsgType>"); sb.AppendFormat("<Content><![CDATA[{0}]]></Content>", "近期业绩尚未发布,请耐心等待!\n\n"); } else { sb.Append(this.createNewsMessages(agentNo, agentMonthPerformanceList, agentType)); } break; } } // sb.AppendFormat("<AgentID>{0}</AgentID>", textMessage.AgentID); sb.AppendFormat("</xml>"); string sRespData = sb.ToString(); string sEncryptMsg = ""; //xml格式的密文 ret = wxcpt.EncryptMsg(sRespData, sReqTimeStamp, sReqNonce, ref sEncryptMsg); logger.Info("sRespData=" + sRespData); logger.Info("ret=" + ret); if (ret != 0) { System.Console.WriteLine("ERR: EncryptMsg Fail, ret: " + ret); return; } context.Response.Write(sEncryptMsg); }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public AgentMonthPerformance GetByKey(String month, string branchNo,String type) { StringBuilder sb = new StringBuilder(); if (type.Contains("直供渠道") || type.Contains("非直供渠道")) { sb.Clear(); sb.Append("SELECT type,branchNo, branchName,agentNo,agentName,"); for (int i = 1; i <= 100; i++) { sb.Append("feeName").Append(i.ToString()).Append(",").Append("fee").Append(i.ToString()).Append(","); } sb.Append("month"); sb.Append(" FROM agent_month_performance where month=@month"); sb.Append(" and branchNo= @branchNo "); } else { sb.Clear(); sb.Append("SELECT type,branchNo, branchName,agentNo,agentName,"); for (int i = 1; i <= 100; i++) { sb.Append("feeName").Append(i.ToString()).Append(",").Append("fee").Append(i.ToString()).Append(","); } sb.Append("month"); sb.Append(" FROM agent_month_performance where month=@month"); sb.Append(" and agentNo= @branchNo "); } 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("@branchNo", branchNo); command.Parameters.AddWithValue("@month", month); MySqlDataReader reader = command.ExecuteReader(); AgentMonthPerformance agentMonthPerformance = null; if (reader.Read()) { agentMonthPerformance = new AgentMonthPerformance(); agentMonthPerformance.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); agentMonthPerformance.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentMonthPerformance.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agentMonthPerformance.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString(); agentMonthPerformance.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString(); agentMonthPerformance.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString(); for (int i = 1; i <= 100; i++) { FieldInfo feeNameField = agentMonthPerformance.GetType().GetField("feeName" + i); FieldInfo feeField = agentMonthPerformance.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(agentMonthPerformance, feeNameFieldValue); feeField.SetValue(agentMonthPerformance, feeFieldValue); } } mycn.Close(); return agentMonthPerformance; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<AgentMonthPerformance> GetAllListMonth(String agentNo,String type) { StringBuilder sb = new StringBuilder(); if (type.Contains("直供渠道") || type.Contains("非直供渠道")) { sb.Clear(); sb.Append("SELECT distinct branchNo,branchName,month"); sb.Append(" FROM agent_month_performance where branchNo = @branchNo order by month desc"); } else { sb.Clear(); sb.Append("SELECT distinct agentNo,agentName,month"); sb.Append(" FROM agent_month_performance where agentNo = @agentNo order by month desc"); } 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); if (type.Contains("直供渠道") || type.Contains("非直供渠道")) { command.Parameters.AddWithValue("@branchNo", agentNo); } else { command.Parameters.AddWithValue("@agentNo", agentNo); } // command.Parameters.AddWithValue("@month", month); MySqlDataReader reader = command.ExecuteReader(); IList<AgentMonthPerformance> list = new List<AgentMonthPerformance>(); AgentMonthPerformance agentMonthPerformance = null; while (reader.Read()) { agentMonthPerformance = new AgentMonthPerformance(); if (type.Contains("直供渠道") || type.Contains("非直供渠道")) { agentMonthPerformance.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString(); agentMonthPerformance.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString(); } else { agentMonthPerformance.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentMonthPerformance.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); } agentMonthPerformance.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString(); list.Add(agentMonthPerformance); } mycn.Close(); return list; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<AgentMonthPerformance> GetAllList(String month, String type) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT type,branchNo, branchName,agentNo,agentName,"); for (int i = 1; i <= 100; i++) { sb.Append("feeName").Append(i.ToString()).Append(",").Append("fee").Append(i.ToString()).Append(","); } sb.Append("month"); sb.Append(" FROM agent_month_performance where 1=1"); if (!String.IsNullOrEmpty(month)) { sb.Append(" and month = \"" + month + "\""); } if (!String.IsNullOrEmpty(type)) { sb.Append(" and type = \"" + type + "\""); } 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("@month", month); MySqlDataReader reader = command.ExecuteReader(); IList<AgentMonthPerformance> list = new List<AgentMonthPerformance>(); AgentMonthPerformance agentMonthPerformance = null; while (reader.Read()) { agentMonthPerformance = new AgentMonthPerformance(); agentMonthPerformance.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); agentMonthPerformance.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentMonthPerformance.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agentMonthPerformance.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString(); agentMonthPerformance.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString(); agentMonthPerformance.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString(); for (int i = 1; i <= 100; i++) { FieldInfo feeNameField = agentMonthPerformance.GetType().GetField("feeName" + i); FieldInfo feeField = agentMonthPerformance.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(agentMonthPerformance, feeNameFieldValue); feeField.SetValue(agentMonthPerformance, feeFieldValue); } list.Add(agentMonthPerformance); } mycn.Close(); return list; } }