/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(InvoicePayment entity)
{
string sql = "UPDATE tb_invoice_payment SET agentNo=@agentNo,agentName=@agentName,month=@month,";
sql = sql + "receivedTime=@receivedTime,processTime=@processTime,content=@content,invoiceFee=@invoiceFee";
sql = sql + "invoiceType=@invoiceType,invoiceNo=@invoiceNo,payStatus=@payStatus";
sql = sql + " where agentNo=@agentNo and month=@month and invoiceNo=@invoiceNo";
//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("@month", entity.month);
command.Parameters.AddWithValue("@receivedTime", entity.receivedTime);
command.Parameters.AddWithValue("@processTime", entity.processTime);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@invoiceType", entity.invoiceType);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
command.Parameters.AddWithValue("@payStatus", entity.payStatus);
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(InvoicePayment entity)
{
string sql = "INSERT INTO tb_invoice_payment (agentNo,agentName,month,receivedTime,processTime,content,invoiceFee,invoiceType,invoiceNo,payStatus) VALUE (@agentNo,@agentName,@month,@receivedTime,@processTime,@content,@invoiceFee,@invoiceType,@invoiceNo,@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("@month", entity.month);
command.Parameters.AddWithValue("@receivedTime", entity.receivedTime);
command.Parameters.AddWithValue("@processTime", entity.processTime);
command.Parameters.AddWithValue("@content", entity.content);
command.Parameters.AddWithValue("@invoiceFee", entity.invoiceFee);
command.Parameters.AddWithValue("@invoiceType", entity.invoiceType);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
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 int Delete(InvoicePayment entity)
{
string sql = "DELETE FROM tb_invoice_payment where agentNo=@agentNo and month=@month and invoiceNo=@invoiceNo ";
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);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<InvoicePayment> GetList(String agentNo, String agentName, String processMonth, String invoiceNo)
{
string sql = "SELECT agentNo,agentName,month,receivedTime,processTime,content,invoiceFee,invoiceType,invoiceNo,payStatus FROM tb_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 (month = '" + processMonth + "')";
}
if (!String.IsNullOrEmpty(invoiceNo))
{
sql = sql + " and (invoiceNo like \"%" + invoiceNo + "%\")";
}
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<InvoicePayment> list = new List<InvoicePayment>();
InvoicePayment invoicePayment = null;
while (reader.Read())
{
invoicePayment = new InvoicePayment();
invoicePayment.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString();
invoicePayment.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
invoicePayment.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
invoicePayment.receivedTime = reader["receivedTime"] == DBNull.Value ? null : reader["receivedTime"].ToString();
invoicePayment.processTime = reader["processTime"] == DBNull.Value ? null : reader["processTime"].ToString();
invoicePayment.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString();
invoicePayment.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString();
invoicePayment.invoiceType = reader["invoiceType"] == DBNull.Value ? null : reader["invoiceType"].ToString();
invoicePayment.invoiceNo = reader["invoiceNo"] == DBNull.Value ? null : reader["invoiceNo"].ToString();
invoicePayment.payStatus = reader["payStatus"] == DBNull.Value ? null : reader["payStatus"].ToString();
list.Add(invoicePayment);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public InvoicePayment GetByKey(InvoicePayment entity)
{
string sql = "SELECT agentNo,agentName,month,receivedTime,processTime,content,invoiceFee,invoiceType,invoiceNo,payStatus FROM tb_invoice_payment where agentNo=@agentNo and month=@month and invoiceNo=@invoiceNo ";
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);
command.Parameters.AddWithValue("@invoiceNo", entity.invoiceNo);
MySqlDataReader reader = command.ExecuteReader();
InvoicePayment invoicePayment = null;
if (reader.Read())
{
invoicePayment = new InvoicePayment();
invoicePayment.month = reader["month"] == DBNull.Value ? null : reader["month"].ToString();
invoicePayment.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
invoicePayment.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
invoicePayment.receivedTime = reader["receivedTime"] == DBNull.Value ? null : reader["receivedTime"].ToString();
invoicePayment.processTime = reader["processTime"] == DBNull.Value ? null : reader["processTime"].ToString();
invoicePayment.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString();
invoicePayment.invoiceFee = reader["invoiceFee"] == DBNull.Value ? null : reader["invoiceFee"].ToString();
invoicePayment.invoiceType = reader["invoiceType"] == DBNull.Value ? null : reader["invoiceType"].ToString();
invoicePayment.invoiceNo = reader["invoiceNo"] == DBNull.Value ? null : reader["invoiceNo"].ToString();
invoicePayment.payStatus = reader["payStatus"] == DBNull.Value ? null : reader["payStatus"].ToString();
}
mycn.Close();
return invoicePayment;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(3, "开始导入支付记录...\r\n");
//导入代理商
InvoicePaymentDao agentInvoicePaymentDao = new InvoicePaymentDao();
AgentDao agentDao = new AgentDao();
WechatAction wechatAction = new WechatAction();
for (int i = 0; i < dgInvoicePayment.RowCount; i++)
{
InvoicePayment agentInvoicePayment = new InvoicePayment();
agentInvoicePayment.month = dgInvoicePayment[0, i].Value.ToString();
agentInvoicePayment.receivedTime = dgInvoicePayment[1, i].Value.ToString();
agentInvoicePayment.processTime = dgInvoicePayment[2, i].Value.ToString();
agentInvoicePayment.agentNo = dgInvoicePayment[3, i].Value.ToString();
agentInvoicePayment.agentName = dgInvoicePayment[4, i].Value.ToString();
agentInvoicePayment.content = dgInvoicePayment[5, i].Value.ToString();
agentInvoicePayment.invoiceFee = dgInvoicePayment[6, i].Value.ToString();
agentInvoicePayment.invoiceType = dgInvoicePayment[7, i].Value.ToString();
agentInvoicePayment.invoiceNo = dgInvoicePayment[8, i].Value.ToString();
agentInvoicePayment.payStatus = dgInvoicePayment[9, i].Value.ToString();
agentInvoicePaymentDao.Delete(agentInvoicePayment);
agentInvoicePaymentDao.Add(agentInvoicePayment);
dgInvoicePayment["result", i].Value = "导入成功";
}
worker.ReportProgress(4, "导入支付记录完成...\r\n");
wechatAction.sendTextMessageToWechat("@all", "结算支付信息已发布，请通过底部菜单查询结算支付详情", Settings.Default.Wechat_Secret, MyConstant.APP_Payment);
//MessageBox.Show("数据上传完毕");
}