/// <summary>
/// 删除数据
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public int Delete(AgentInvoicePayment entity)
{
string sql = "DELETE FROM agent_invoice_payment where agentNo=@agentNo and processTime=@processTime ";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@processTime", entity.processTime);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
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(AgentInvoicePayment entity)
{
string sql = "INSERT INTO agent_invoice_payment (agentNo,agentName,processTime,invoiceFee,payFee,summary,payStatus) VALUE (@agentNo,@agentName,@processTime,@invoiceFee,@payFee,@summary,@payStatus)";
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("@processTime", entity.processTime);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@payFee", entity.payFee);
command.Parameters.AddWithValue("@summary", entity.summary);
command.Parameters.AddWithValue("@payStatus", entity.payStatus);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(AgentInvoicePayment entity)
{
string sql = "UPDATE agent_invoice_payment SET agentNo=@agentNo,agentName=@agentName,processTime=@processTime,";
sql = sql + "invoiceFee=@invoiceFee,payFee=@payFee,summary=@summary,payStatus=@payStatus";
sql = sql + " where agentNo=@agentNo and processTime=@processTime ";
//string sql = "UPDATE cimuser SET userNickName=@userNickName WHERE userid=@userid";
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("@processTime", entity.processTime);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@payFee", entity.payFee);
command.Parameters.AddWithValue("@summary", entity.summary);
command.Parameters.AddWithValue("@payStatus", entity.payStatus);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public AgentInvoicePayment GetByKey(AgentInvoicePayment entity)
{
string sql = "SELECT agentNo,agentName,processTime,invoiceFee,payFee,summary,payStatus FROM agent_invoice_payment where agentNo=@agentNo and processTime=@processTime ";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@agentNo", entity.agentNo);
command.Parameters.AddWithValue("@processTime", entity.processTime);
MySqlDataReader reader = command.ExecuteReader();
AgentInvoicePayment agentInvoicePayment = null;
if (reader.Read())
{
agentInvoicePayment = new AgentInvoicePayment();
agentInvoicePayment.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentInvoicePayment.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
agentInvoicePayment.processTime = reader["processTime"] == DBNull.Value ? null : reader["processTime"].ToString();
agentInvoicePayment.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString();
agentInvoicePayment.payFee = reader["payFee"] == DBNull.Value ? null : reader["payFee"].ToString();
agentInvoicePayment.summary = reader["summary"] == DBNull.Value ? null : reader["summary"].ToString();
agentInvoicePayment.payStatus = reader["payStatus"] == DBNull.Value ? null : reader["payStatus"].ToString();
}
mycn.Close();
return agentInvoicePayment;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentInvoicePayment> GetList(String agentNo,String agentName,String processMonth)
{
string sql = "SELECT agentNo,agentName,processTime,invoiceFee,payFee,summary,payStatus FROM agent_invoice_payment where 1=1 ";
if (!String.IsNullOrEmpty(agentNo))
{
sql = sql + " and (agentNo like \"%" + agentNo + "%\")";
}
if (!String.IsNullOrEmpty(agentName))
{
sql = sql + " and (agentName like \"%" + agentName + "%\")";
}
if (!String.IsNullOrEmpty(processMonth))
{
sql = sql + " and ( date_format(str_to_date(processtime, '%Y-%m-%d'),'%Y%m') = '" + processMonth + "')";
}
sql = sql + " order by processtime asc";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
MySqlDataReader reader = command.ExecuteReader();
IList<AgentInvoicePayment> list = new List<AgentInvoicePayment>();
AgentInvoicePayment agentInvoicePayment = null;
while (reader.Read())
{
agentInvoicePayment = new AgentInvoicePayment();
agentInvoicePayment.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentInvoicePayment.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
agentInvoicePayment.processTime = reader["processTime"] == DBNull.Value ? null : reader["processTime"].ToString();
agentInvoicePayment.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString();
agentInvoicePayment.payFee = reader["payFee"] == DBNull.Value ? null : reader["payFee"].ToString();
agentInvoicePayment.summary = reader["summary"] == DBNull.Value ? null : reader["summary"].ToString();
agentInvoicePayment.payStatus = reader["payStatus"] == DBNull.Value ? null : reader["payStatus"].ToString();
list.Add(agentInvoicePayment);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(3, "开始导入支付记录...\r\n");
//导入代理商
AgentInvoicePaymentDao agentInvoicePaymentDao = new AgentInvoicePaymentDao();
AgentDao agentDao = new AgentDao();
WechatAction wechatAction = new WechatAction();
for (int i = 0; i < dgInvoicePayment.RowCount; i++)
{
AgentInvoicePayment agentInvoicePayment = new AgentInvoicePayment();
agentInvoicePayment.agentNo = dgInvoicePayment[0, i].Value.ToString();
agentInvoicePayment.agentName = dgInvoicePayment[1, i].Value.ToString();
agentInvoicePayment.processTime = dgInvoicePayment[2, i].Value.ToString();
agentInvoicePayment.invoiceFee = dgInvoicePayment[3, i].Value.ToString();
agentInvoicePayment.payFee = dgInvoicePayment[4, i].Value.ToString();
agentInvoicePayment.summary = dgInvoicePayment[5, i].Value.ToString();
agentInvoicePayment.payStatus = dgInvoicePayment[6, i].Value.ToString();
Agent agent = agentDao.Get(agentInvoicePayment.agentNo);
if (agent != null && !String.IsNullOrEmpty(agent.agentName))
{
agentInvoicePaymentDao.Delete(agentInvoicePayment);
agentInvoicePaymentDao.Add(agentInvoicePayment);
dgInvoicePayment["result", i].Value = "导入成功";
String message = String.Format(Settings.Default.InvoicePayment_Wechat_Message, agentInvoicePayment.processTime, agentInvoicePayment.invoiceFee, agentInvoicePayment.payFee, agentInvoicePayment.summary, agentInvoicePayment.payStatus);
wechatAction.sendTextMessageToWechat(agentInvoicePayment.agentNo, message, Settings.Default.Wechat_Secret, Settings.Default.Wechar_Invoice_AppId);
}
else
{
dgInvoicePayment["result", i].Value = "导入失败，代理商编号：" + agentInvoicePayment.agentNo + "不存在,请先导入代理商.";
}
}
worker.ReportProgress(4, "导入支付记录完成...\r\n");
//MessageBox.Show("数据上传完毕");
}