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(AgentContact entity)
{
string sql = "INSERT INTO agent_Contact (agentNo,agentName,branchNo,branchName,area,zone,contactTel,contactName,contactEmail) VALUE (@agentNo,@agentName,@branchNo,@branchName,@area,@zone,@contactTel,@contactName,@contactEmail)";
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("@branchNo", entity.branchNo);
command.Parameters.AddWithValue("@branchName", entity.branchName);
command.Parameters.AddWithValue("@area", entity.area);
command.Parameters.AddWithValue("@zone", entity.zone);
command.Parameters.AddWithValue("@contactTel", entity.contactTel);
command.Parameters.AddWithValue("@contactName", entity.contactName);
command.Parameters.AddWithValue("@contactEmail", entity.contactEmail);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentContact> GetListByKeyword(String keyword)
{
string sql = "SELECT agentNo,agentName,branchNo,branchName,area,zone,contactTel,contactName,contactEmail FROM agent_contact";
sql = sql+" where 1=1";
if(!String.IsNullOrEmpty(keyword)){
sql = sql + " and ((agentNo like \"%" + keyword +"%\")";
sql = sql + " or (agentName like \"%" + keyword + "%\")";
sql = sql + " or (branchNo like \"%" + keyword + "%\")";
sql = sql + " or (branchName like \"%" + keyword + "%\"))";
}
sql = sql + " order by agentNo asc,branchNo asc";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
MySqlDataReader reader = command.ExecuteReader();
IList<AgentContact> list = new List<AgentContact>();
AgentContact agentContact = null;
while (reader.Read())
{
agentContact = new AgentContact();
agentContact.agentNo = reader["agentNo"] == DBNull.Value ? null : reader["agentNo"].ToString();
agentContact.agentName = reader["agentName"] == DBNull.Value ? null : reader["agentName"].ToString();
agentContact.branchNo = reader["branchNo"] == DBNull.Value ? null : reader["branchNo"].ToString();
agentContact.branchName = reader["branchName"] == DBNull.Value ? null : reader["branchName"].ToString();
agentContact.area = reader["area"] == DBNull.Value ? null : reader["area"].ToString();
agentContact.zone = reader["zone"] == DBNull.Value ? null : reader["zone"].ToString();
agentContact.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString();
agentContact.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString();
agentContact.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString();
list.Add(agentContact);
}
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");
//导入代理商
AgentContactDao agentContactDao = new AgentContactDao();
for (int i = 0; i < dgAgentContact.RowCount; i++)
{
AgentContact agentContact = new AgentContact();
agentContact.agentNo = dgAgentContact[0, i].Value.ToString();
agentContact.agentName = dgAgentContact[1, i].Value.ToString();
agentContact.branchNo = dgAgentContact[2, i].Value.ToString();
agentContact.branchName = dgAgentContact[3, i].Value.ToString();
agentContact.area = dgAgentContact[4, i].Value.ToString();
agentContact.zone = dgAgentContact[5, i].Value.ToString();
agentContact.contactName = dgAgentContact[6, i].Value.ToString().Replace("；",";");
agentContact.contactTel = dgAgentContact[7, i].Value.ToString().Replace("；", ";");
agentContact.contactEmail = dgAgentContact[8, i].Value.ToString().Replace("；", ";");
String[] contactNames = agentContact.contactName.Split(';');
String[] contactTels = agentContact.contactTel.Split(';');
String[] contactEmails = agentContact.contactEmail.Split(';');
for (int j = 0; j < contactNames.Length; j++)
{
String contactName = contactNames[j];
agentContactDao.Delete(agentContact.agentNo.Trim(), agentContact.branchNo.Trim(), contactName);
agentContact.contactName = contactName;
if (contactTels.Length >= j + 1)
{
agentContact.contactTel = contactTels[j];
}
else
{
agentContact.contactTel = "";
}
if (contactEmails.Length >= j + 1)
{
agentContact.contactEmail = contactEmails[j];
}
else
{
agentContact.contactEmail = "";
}
agentContactDao.Add(agentContact);
}
}
worker.ReportProgress(4, "导入代理商联系人完成...\r\n");
WechatAction wechatAction = new WechatAction();
wechatAction.sendTextMessageToWechat("@all", "最新渠道经理信息已发布，请通过底部菜单联系最新渠道经理", Settings.Default.Wechat_Secret, MyConstant.APP_Contact);
//MessageBox.Show("数据上传完毕");
}
