/// <summary>
/// 删除数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Delete(AgentBonus entity)
{
string sql = "DELETE FROM agent_bonus WHERE agentNo=@agentNo and month =@month";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@month", entity.month);
int i = command.ExecuteNonQuery();
mycn.Close();
return i;
}
}
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(AgentBonus entity)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO agent_bonus (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 (@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("@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();
return i;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public AgentBonus GetByKey(String month, string agentNo)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT 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_bonus where month=@month");
sb.Append(" and agentNo= @agentNo ");
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("@agentNo", agentNo);
command.Parameters.AddWithValue("@month", month);
MySqlDataReader reader = command.ExecuteReader();
AgentBonus agentBonus = null;
if (reader.Read())
{
agentBonus = new AgentBonus();
agentBonus.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentBonus.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();
agentBonus.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString();
for (int i = 1; i <= 100; i++)
{
FieldInfo feeNameField = agentBonus.GetType().GetField("feeName" + i);
FieldInfo feeField = agentBonus.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(agentBonus, feeNameFieldValue);
feeField.SetValue(agentBonus, feeFieldValue);
}
}
mycn.Close();
return agentBonus;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentBonus> GetAllListMonth(String agentNo)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT distinct agentNo,agentName,month");
sb.Append(" FROM agent_bonus 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);
command.Parameters.AddWithValue("@agentNo", agentNo);
// command.Parameters.AddWithValue("@month", month);
MySqlDataReader reader = command.ExecuteReader();
IList<AgentBonus> list = new List<AgentBonus>();
AgentBonus agentMonthPerformance = null;
while (reader.Read())
{
agentMonthPerformance = new AgentBonus();
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<AgentBonus> GetListByKeyword(String keyword,String month)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT 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_bonus where 1=1");
if (!String.IsNullOrEmpty(month))
{
sb.Append(" and month = \"" + month + "\"");
}
if (!String.IsNullOrEmpty(keyword))
{
sb.Append(" and agentNo like \"%" + keyword + "%\"");
}
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("@agentNo", agentNo);
//command.Parameters.AddWithValue("@month", month);
MySqlDataReader reader = command.ExecuteReader();
IList<AgentBonus> list = new List<AgentBonus>();
AgentBonus agentMonthPerformance = null;
while (reader.Read())
{
agentMonthPerformance = new AgentBonus();
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;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(1, "开始导入红包...\r\n");
AgentBonusDao agentBonusDao = new AgentBonusDao();
for (int i = 0; i < dgAgentBonus.RowCount; i++)
{
AgentBonus agentBonus = new AgentBonus();
agentBonus.month = this.dtFeeMonth.Value.ToString("yyyyMM");
agentBonus.agentNo = dgAgentBonus[0, i].Value.ToString();
agentBonus.agentName = dgAgentBonus[1, i].Value.ToString();
int index = 2;
for (int j = index; j <= 101 && j < dgAgentBonus.Columns.Count; j++)
{
FieldInfo feeNameField = agentBonus.GetType().GetField("feeName" + (j - index + 1));
FieldInfo feeField = agentBonus.GetType().GetField("fee" + (j - index + 1));
String feeNameFieldValue = dgAgentBonus.Columns[j].HeaderCell.Value.ToString();
String feeFieldValue = dgAgentBonus[j, i].Value.ToString();
if (feeFieldValue.Trim().Equals("0") || String.IsNullOrWhiteSpace(feeFieldValue))
{
feeFieldValue = null;
}
feeNameField.SetValue(agentBonus, feeNameFieldValue);
feeField.SetValue(agentBonus, feeFieldValue);
}
agentBonusDao.Delete(agentBonus);
agentBonusDao.Add(agentBonus);
}
worker.ReportProgress(4, "导入红包完成...\r\n");
WechatAction wechatAction = new WechatAction();
wechatAction.sendTextMessageToWechat("@all", this.dtFeeMonth.Value.ToString("yyyy年MM月") + "红包已发布，请通过底部菜单查询红包详情", Settings.Default.Wechat_Secret, MyConstant.APP_Payment);
//MessageBox.Show("数据上传完毕");
}
private StringBuilder createAgentBonusNewsMessages(String feeMonth, AgentBonus agentBonus, 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.Substring(0,4)+"年"+feeMonth.Substring(4,2)+"月").Append("</Title>");
StringBuilder sbDesc = new StringBuilder();
//sbDesc.AppendFormat("本月佣金告知单({0})", feeMonth);
sbDesc.AppendFormat("代理商编号：{0}\n", agentBonus.agentNo);
sbDesc.AppendFormat("代理商名字：{0}\n\n", agentBonus.agentName);
// sb1.AppendFormat("佣金\n\n");
sbDesc.AppendFormat("红包明细：\n");
int i = 1;
for (int j = 1; j <= 100; j++)
{
FieldInfo feeNameField = agentBonus.GetType().GetField("feeName" + j);
FieldInfo feeField = agentBonus.GetType().GetField("fee" + j);
if (feeNameField != null && feeField != null)
{
String feeNameFieldValue = feeNameField.GetValue(agentBonus) == null ? null : feeNameField.GetValue(agentBonus).ToString();
String feeFieldValue = feeField.GetValue(agentBonus) == null ? null : feeField.GetValue(agentBonus).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", agentBonus.feeTotal);
//sbDesc.Append(" ").Append(i++).AppendFormat(".{0}", "过网开票金额").Append(" ").AppendFormat("{0}\n", agentBonus.preInvoiceFee);
sb.Append("<Description>").AppendFormat("<![CDATA[{0}]]>", sbDesc.ToString()).Append("</Description>");
String url1 = String.Format("http://{0}/Wechat/AgentBonusDetailQuery.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;
}
private StringBuilder createAllNewsMessages(String feeMonth, String agentNo, AgentFee agentFee, AgentBonus agentBonus, IList<InvoicePayment> agentInvoicePaymentList)
{
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();
if (agentFee != null)
{
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);
if (agentTypeCommentList.Length > 0)
{
for (int count = 0; count < agentTypeCommentList.Length; count++)
{
sbDesc.Append(" ").Append(count + 1).AppendFormat(".{0}\n", agentTypeCommentList[count]);
}
}
else
{
sbDesc.Append(" 无");
}
}
}
else
{
sbDesc.AppendFormat("本月无佣金或者佣金尚未发布.\n");
}
sbDesc.AppendLine();
///支付结算
if (agentInvoicePaymentList != null && agentInvoicePaymentList.Count > 0)
{
//sbDesc.AppendFormat("本月佣金告知单({0})", feeMonth);
sbDesc.AppendFormat("总共处理了：{0}次支付结算信息\n", agentInvoicePaymentList.Count);
foreach (InvoicePayment agentInvoicePayment in agentInvoicePaymentList)
{
sbDesc.AppendFormat("\n处理时间：" + agentInvoicePayment.processTime + "\n发票金额：" + agentInvoicePayment.invoiceFee + "\n内容：" + agentInvoicePayment.content + "\n发票类型：" + agentInvoicePayment.invoiceType + "\n付款状态：" + agentInvoicePayment.payStatus).AppendLine();
}
}
else
{
sbDesc.AppendFormat("没有发布支付结算信息.\n");
}
sbDesc.AppendLine();
///红包
if (agentBonus != null)
{
sbDesc.AppendFormat("红包明细：\n");
int i = 1;
for (int j = 1; j <= 100; j++)
{
FieldInfo feeNameField = agentBonus.GetType().GetField("feeName" + j);
FieldInfo feeField = agentBonus.GetType().GetField("fee" + j);
if (feeNameField != null && feeField != null)
{
String feeNameFieldValue = feeNameField.GetValue(agentBonus) == null ? null : feeNameField.GetValue(agentBonus).ToString();
String feeFieldValue = feeField.GetValue(agentBonus) == null ? null : feeField.GetValue(agentBonus).ToString();
if (!String.IsNullOrEmpty(feeFieldValue) && !String.IsNullOrWhiteSpace(feeFieldValue))
{
sbDesc.Append(" ").Append(i++).AppendFormat(".{0}", feeNameFieldValue).Append(" ").AppendFormat("{0}\n", feeFieldValue);
}
}
}
}
else
{
sbDesc.AppendFormat("没有发布红包信息.\n");
}
// sbDesc.AppendLine();
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;
}
protected void Page_Load(object sender, EventArgs e)
{
string feeMonth = Request.QueryString["feeMonth"];
string agentNo = Request.QueryString["agentNo"];
logger.Info("month=" + Request.QueryString["month"]);
logger.Info("agentNo=" + Request.QueryString["agentNo"]);
try
{
Request.ContentEncoding = Encoding.UTF8;
feeMonth = QueryStringEncryption.Decode(feeMonth, QueryStringEncryption.key);
agentNo = QueryStringEncryption.Decode(agentNo, QueryStringEncryption.key);
logger.Info("feeMonth=" + feeMonth);
logger.Info("agentNo=" + agentNo);
}
catch (Exception)
{
// return;
}
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
{
}
DataTable dt = new DataTable();
DataRow row = null;
dt.Columns.Add("name");
dt.Columns.Add("value");
AgentBonusDao agentBonusDao = new ChinaUnion_DataAccess.AgentBonusDao();
AgentBonus agentBonus = new AgentBonus();
agentBonus = agentBonusDao.GetByKey(feeMonth,agentNo );
if (agentBonus != null)
{
row = dt.NewRow();
row["name"] = "渠道编码";
row["value"] = agentBonus.agentNo;
dt.Rows.Add(row);
row = dt.NewRow();
row["name"] = "渠道名称";
row["value"] = agentBonus.agentName;
dt.Rows.Add(row);
for (int j = 1; j <= 100; j++)
{
FieldInfo feeNameField = agentBonus.GetType().GetField("feeName" + j);
FieldInfo feeField = agentBonus.GetType().GetField("fee" + j);
if (feeNameField != null && feeField != null)
{
String feeNameFieldValue = feeNameField.GetValue(agentBonus) == null ? null : feeNameField.GetValue(agentBonus).ToString();
String feeFieldValue = feeField.GetValue(agentBonus) == null ? null : feeField.GetValue(agentBonus).ToString();
if (!String.IsNullOrEmpty(feeFieldValue) && !String.IsNullOrWhiteSpace(feeFieldValue))
{
row = dt.NewRow();
row["name"] = feeNameFieldValue;
row["value"] = feeFieldValue;
dt.Rows.Add(row);
}
}
}
}
this.lblFeeMonth.Text = feeMonth.Substring(0, 4) + "年" + feeMonth.Substring(4, 2) + "月" + "红包详情";
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
}