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(AgentDailyPerformance entity) { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO agent_daily_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("date) 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("@date)"); //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("@date", entity.date); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public AgentDailyPerformance GetByKey(String date, 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("date"); sb.Append(" FROM agent_daily_performance where date=@date"); 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("date"); sb.Append(" FROM agent_daily_performance where date=@date"); 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("@date", date); MySqlDataReader reader = command.ExecuteReader(); AgentDailyPerformance agentDailyPerformance = null; if (reader.Read()) { agentDailyPerformance = new AgentDailyPerformance(); agentDailyPerformance.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); agentDailyPerformance.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentDailyPerformance.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agentDailyPerformance.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString(); agentDailyPerformance.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString(); agentDailyPerformance.date = reader["date"] == DBNull.Value ? null : reader["date"].ToString(); for (int i = 1; i <= 100; i++) { FieldInfo feeNameField = agentDailyPerformance.GetType().GetField("feeName" + i); FieldInfo feeField = agentDailyPerformance.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(agentDailyPerformance, feeNameFieldValue); feeField.SetValue(agentDailyPerformance, feeFieldValue); } } mycn.Close(); return agentDailyPerformance; } }
private StringBuilder createNewsMessages(String feeDate, String agentNo, AgentDailyPerformance agentDailyPerformance, 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}日业绩详情", feeDate).Append("</Title>"); StringBuilder sbDesc = new StringBuilder(); //sbDesc.AppendFormat("本月佣金告知单({0})", feeMonth); // sbDesc.AppendFormat("总共处理了:{0}次发票信息\n", agentMonthPerformanceList.Count); if (!String.IsNullOrEmpty(agentDailyPerformance.agentNo)) { sbDesc.AppendFormat("代理商编号:" + agentDailyPerformance.agentNo + "\n代理商名称:" + agentDailyPerformance.agentName).Append("\n"); } if (!String.IsNullOrEmpty(agentDailyPerformance.branchNo)) { sbDesc.AppendFormat("渠道编号:" + agentDailyPerformance.branchNo + "\n渠道名称:" + agentDailyPerformance.branchName).Append("\n"); } sbDesc.AppendLine().AppendFormat("\n业绩汇总明细:\n"); int i = 1; for (int j = 1; j <= 100; j++) { FieldInfo feeNameField = agentDailyPerformance.GetType().GetField("feeName" + j); FieldInfo feeField = agentDailyPerformance.GetType().GetField("fee" + j); if (feeNameField != null && feeField != null) { String feeNameFieldValue = feeNameField.GetValue(agentDailyPerformance) == null ? null : feeNameField.GetValue(agentDailyPerformance).ToString(); String feeFieldValue = feeField.GetValue(agentDailyPerformance) == null ? null : feeField.GetValue(agentDailyPerformance).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/PerformanceDailySummaryQuery.aspx?agentNo={1}&feeDate={2}&type={3}", Properties.Settings.Default.Host, QueryStringEncryption.Encode(agentNo, QueryStringEncryption.key), QueryStringEncryption.Encode(feeDate, 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> /// <returns></returns> public IList<AgentDailyPerformance> GetAllList(String date, 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("date"); sb.Append(" FROM agent_daily_performance where 1=1"); if (!String.IsNullOrEmpty(date)) { sb.Append(" and date = \"" + date + "\""); } 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("@date", date); // command.Parameters.AddWithValue("@type", type); MySqlDataReader reader = command.ExecuteReader(); IList<AgentDailyPerformance> list = new List<AgentDailyPerformance>(); AgentDailyPerformance agentDailyPerformance = null; while (reader.Read()) { agentDailyPerformance = new AgentDailyPerformance(); agentDailyPerformance.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); agentDailyPerformance.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString(); agentDailyPerformance.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString(); agentDailyPerformance.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString(); agentDailyPerformance.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString(); agentDailyPerformance.date = reader["date"] == DBNull.Value ? null : reader["date"].ToString(); for (int i = 1; i <= 100; i++) { FieldInfo feeNameField = agentDailyPerformance.GetType().GetField("feeName" + i); FieldInfo feeField = agentDailyPerformance.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(agentDailyPerformance, feeNameFieldValue); feeField.SetValue(agentDailyPerformance, feeFieldValue); } list.Add(agentDailyPerformance); } mycn.Close(); return list; } }
protected void Page_Load(object sender, EventArgs e) { string feeDate = Request.QueryString["date"]; string branchNo = Request.QueryString["branchNo"]; string branchName = Request.QueryString["branchName"]; string type = Request.QueryString["type"]; logger.Info("feeDate=" + Request.QueryString["date"]); logger.Info("branchNo=" + Request.QueryString["branchNo"]); logger.Info("branchName=" + Request.QueryString["branchName"]); logger.Info("type=" + Request.QueryString["type"]); try { Request.ContentEncoding = Encoding.UTF8; // feeMonth = QueryStringEncryption.Decode(feeMonth, QueryStringEncryption.key); // agentNo = QueryStringEncryption.Decode(agentNo, QueryStringEncryption.key); logger.Info("feeMonth=" + feeDate); logger.Info("branchNo=" + branchNo); } catch (Exception) { // return; } DataTable dt = new DataTable(); DataRow row = null; dt.Columns.Add("name"); dt.Columns.Add("value"); AgentDailyPerformanceDao agentPerformanceDao = new ChinaUnion_DataAccess.AgentDailyPerformanceDao(); AgentDailyPerformance agentPerformance = new AgentDailyPerformance(); if (!branchName.Equals("总计")) { agentPerformance = agentPerformanceDao.GetByKey(feeDate, branchNo, type); } else { agentPerformance = agentPerformanceDao.GetSummary(branchNo, feeDate, type); } if (agentPerformance != null) { if (!String.IsNullOrEmpty(agentPerformance.branchNo)) { row = dt.NewRow(); row["name"] = "门店编号"; row["value"] = agentPerformance.branchNo; dt.Rows.Add(row); row = dt.NewRow(); row["name"] = "门店名称"; row["value"] = agentPerformance.branchName; dt.Rows.Add(row); } else { row = dt.NewRow(); row["name"] = "代理商编号"; row["value"] = agentPerformance.agentNo; dt.Rows.Add(row); row = dt.NewRow(); row["name"] = "代理商名称"; row["value"] = agentPerformance.agentName; dt.Rows.Add(row); } for (int j = 1; j <= 100; j++) { FieldInfo feeNameField = agentPerformance.GetType().GetField("feeName" + j); FieldInfo feeField = agentPerformance.GetType().GetField("fee" + j); if (feeNameField != null && feeField != null) { String feeNameFieldValue = feeNameField.GetValue(agentPerformance) == null ? null : feeNameField.GetValue(agentPerformance).ToString(); String feeFieldValue = feeField.GetValue(agentPerformance) == null ? null : feeField.GetValue(agentPerformance).ToString(); if (!String.IsNullOrEmpty(feeFieldValue) && !String.IsNullOrWhiteSpace(feeFieldValue)) { row = dt.NewRow(); row["name"] = feeNameFieldValue; row["value"] = feeFieldValue; dt.Rows.Add(row); } } } } this.lblFeeMonth.Text = feeDate + "绩效详情"; GridView1.DataSource = dt.DefaultView; GridView1.DataBind(); }