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="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>
/// <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> 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;
}
}
