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(AgentWechatAccount entity)
{
string sql = "INSERT INTO agent_wechat_account (agentNo,agentName,branchNo,branchName,regionName,contactId,contactName,contactEmail,contactTel,contactWechat,feeRight,policyRight,performanceRight,studyRight,complainRight,monitorRight,errorRight,contactRight,type)";
sql = sql + " VALUE (@agentNo,@agentName,@branchNo,@branchName,@regionName,@contactId,@contactName,@contactEmail,@contactTel,@contactWechat,@feeRight,@policyRight,@performanceRight,@studyRight,@complainRight,@monitorRight,@errorRight,@contactRight,@type)";
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("@regionName", entity.regionName);
command.Parameters.AddWithValue("@contactId", entity.contactId);
command.Parameters.AddWithValue("@contactName", entity.contactName);
command.Parameters.AddWithValue("@contactEmail", entity.contactEmail);
command.Parameters.AddWithValue("@contactTel", entity.contactTel);
command.Parameters.AddWithValue("@contactWechat", entity.contactWechat);
command.Parameters.AddWithValue("@feeRight", entity.feeRight);
command.Parameters.AddWithValue("@policyRight", entity.policyRight);
command.Parameters.AddWithValue("@performanceRight", entity.performanceRight);
command.Parameters.AddWithValue("@studyRight", entity.studyRight);
command.Parameters.AddWithValue("@complainRight", entity.complainRight);
command.Parameters.AddWithValue("@monitorRight", entity.monitorRight);
command.Parameters.AddWithValue("@errorRight", entity.errorRight);
command.Parameters.AddWithValue("@contactRight", entity.contactRight);
command.Parameters.AddWithValue("@type", entity.type);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public int UpdateWechatImportStatus(AgentWechatAccount entity)
{
string sql = "update agent_wechat_account set wechatImportStatus=@wechatImportStatus";
sql = sql + " where contactId=@contactId";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
command.Parameters.AddWithValue("@wechatImportStatus", entity.wechatImportStatus);
command.Parameters.AddWithValue("@contactId", entity.contactId);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public int Update(AgentWechatAccount entity)
{
string sql = "update agent_wechat_account set agentNo=@agentNo,agentName=@agentName,branchNo=@branchNo,";
sql = sql + " branchName=@branchName,regionName=@regionName,contactId=@contactId,contactName=@contactName,";
sql = sql + " contactEmail=@contactEmail,contactTel=@contactTel,contactWechat=@contactWechat,";
sql = sql + " feeRight=@feeRight,policyRight=@policyRight,performanceRight=@performanceRight,";
sql = sql + " studyRight=@studyRight,complainRight=@complainRight,monitorRight=@monitorRight,errorRight=@errorRight,contactRight=@contactRight,type=@type ";
sql = sql + " where contactId=@contactId";
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("@regionName", entity.regionName);
command.Parameters.AddWithValue("@contactId", entity.contactId);
command.Parameters.AddWithValue("@contactName", entity.contactName);
command.Parameters.AddWithValue("@contactEmail", entity.contactEmail);
command.Parameters.AddWithValue("@contactTel", entity.contactTel);
command.Parameters.AddWithValue("@contactWechat", entity.contactWechat);
command.Parameters.AddWithValue("@feeRight", entity.feeRight);
command.Parameters.AddWithValue("@policyRight", entity.policyRight);
command.Parameters.AddWithValue("@performanceRight", entity.performanceRight);
command.Parameters.AddWithValue("@studyRight", entity.studyRight);
command.Parameters.AddWithValue("@complainRight", entity.complainRight);
command.Parameters.AddWithValue("@monitorRight", entity.monitorRight);
command.Parameters.AddWithValue("@errorRight", entity.errorRight);
command.Parameters.AddWithValue("@contactRight", entity.contactRight);
command.Parameters.AddWithValue("@type", entity.type);
int i = command.ExecuteNonQuery();
mycn.Close();
mycn.Dispose();
return i;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(this.txtAgentNo.Text.Trim()))
{
MessageBox.Show("编号不能为空");
this.txtAgentNo.Focus();
return;
}
if (String.IsNullOrEmpty(this.txtUserId.Text.Trim()))
{
MessageBox.Show("账号不能为空");
this.txtAgentName.Focus();
return;
}
if (String.IsNullOrEmpty(this.txtWeixinId.Text.Trim()) && String.IsNullOrEmpty(this.txtMobile.Text.Trim()) && String.IsNullOrEmpty(this.txtEmail.Text.Trim()))
{
MessageBox.Show("微信号，手机和邮箱不能同时为空");
return;
}
String EmailReg = @"^\w+([\.\-]\w+)*\@\w+([\.\-]\w+)*\.\w+$";
if (!String.IsNullOrEmpty(txtEmail.Text.Trim()) && !Regex.IsMatch(txtEmail.Text.Trim(), EmailReg))
{
MessageBox.Show("请输入有效的邮箱地址");
this.txtEmail.Focus();
return;
}
String MobileReg = @"^[1]+[3,5,8]+\d{9}";
if (!String.IsNullOrEmpty(txtMobile.Text.Trim()) && !Regex.IsMatch(this.txtMobile.Text.Trim(), MobileReg))
{
MessageBox.Show("请输入有效的手机号码");
this.txtMobile.Focus();
return;
}
this.Cursor = Cursors.WaitCursor;
AgentWechatAccount agentWechatAccount = new AgentWechatAccount();
AgentWechatAccountDao agentWechatAccountDao = new AgentWechatAccountDao();
agentWechatAccount = agentWechatAccountDao.Get(this.txtUserId.Text);
agentWechatAccount.contactEmail = this.txtEmail.Text;
agentWechatAccount.contactId = this.txtUserId.Text;
agentWechatAccount.contactName = this.txtUserName.Text;
agentWechatAccount.contactTel = this.txtMobile.Text;
agentWechatAccount.contactWechat = this.txtWeixinId.Text;
agentWechatAccount.feeRight = "N";
agentWechatAccount.policyRight = "N";
agentWechatAccount.performanceRight = "N";
agentWechatAccount.studyRight = "N";
agentWechatAccount.complainRight = "N";
agentWechatAccount.monitorRight = "N"; ;
agentWechatAccount.errorRight = "N";
agentWechatAccount.contactRight = "N";
if (this.chkFee.Checked)
{
agentWechatAccount.feeRight = "Y";
}
if (this.chkPolicy.Checked)
{
agentWechatAccount.policyRight = "Y";
}
if (this.chkPerformance.Checked)
{
agentWechatAccount.performanceRight = "Y";
}
if (this.chkStudy.Checked)
{
agentWechatAccount.studyRight = "Y";
}
if (this.chkComplain.Checked)
{
agentWechatAccount.complainRight = "Y";
}
if (this.chkService.Checked)
{
agentWechatAccount.monitorRight = "Y";
}
if (this.chkError.Checked)
{
agentWechatAccount.errorRight = "Y";
}
if (this.chkContactUs.Checked)
{
agentWechatAccount.contactRight = "Y";
}
this.retAgentWechatAccount = agentWechatAccount;
agentWechatAccountDao.Update(agentWechatAccount);
this.Cursor = Cursors.Default;
DialogResult = System.Windows.Forms.DialogResult.OK;
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentWechatAccount> GetAllAgentOrBranch()
{
string sql = "SELECT distinct type,agentNo,agentName,branchNo,branchName,regionName FROM agent_wechat_account";
sql = sql+" where 1=1";
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<AgentWechatAccount> list = new List<AgentWechatAccount>();
AgentWechatAccount agentContact = null;
while (reader.Read())
{
agentContact = new AgentWechatAccount();
agentContact.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString();
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.regionName = reader["regionName"] == DBNull.Value ? null : reader["regionName"].ToString();
list.Add(agentContact);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<AgentWechatAccount> GetListByKeyword(String keyword, String type)
{
string sql = "SELECT type,agentNo,agentName,branchNo,branchName,regionName,contactId,contactName,contactEmail,contactTel,contactWechat,feeRight,policyRight,performanceRight,studyRight,complainRight,monitorRight,errorRight,contactRight FROM agent_wechat_account";
sql = sql + " where 1=1";
if (!String.IsNullOrEmpty(keyword))
{
sql = sql + " and ((agentNo like \"%" + keyword + "%\")";
sql = sql + " or (agentName like \"%" + keyword + "%\")";
sql = sql + " or (contactId like \"%" + keyword + "%\")";
sql = sql + " or (contactName like \"%" + keyword + "%\")";
sql = sql + " or (contactWechat like \"%" + keyword + "%\")";
sql = sql + " or (branchNo like \"%" + keyword + "%\")";
sql = sql + " or (branchName like \"%" + keyword + "%\"))";
}
if (!String.IsNullOrEmpty(type))
{
sql = sql + " and type= \"" + type + "\"";
}
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<AgentWechatAccount> list = new List<AgentWechatAccount>();
AgentWechatAccount agentContact = null;
while (reader.Read())
{
agentContact = new AgentWechatAccount();
agentContact.type = reader["type"] == DBNull.Value ? "" : reader["type"].ToString();
agentContact.agentNo = reader["agentNo"] == DBNull.Value ? "" : reader["agentNo"].ToString();
agentContact.agentName = reader["agentName"] == DBNull.Value ? "" : reader["agentName"].ToString();
agentContact.branchNo = reader["branchNo"] == DBNull.Value ? "" : reader["branchNo"].ToString();
agentContact.branchName = reader["branchName"] == DBNull.Value ? "" : reader["branchName"].ToString();
agentContact.regionName = reader["regionName"] == DBNull.Value ? "" : reader["regionName"].ToString();
agentContact.contactId = reader["contactId"] == DBNull.Value ? "" : reader["contactId"].ToString();
agentContact.contactEmail = reader["contactEmail"] == DBNull.Value ? "" : reader["contactEmail"].ToString();
agentContact.contactTel = reader["contactTel"] == DBNull.Value ? "" : reader["contactTel"].ToString();
agentContact.contactName = reader["contactName"] == DBNull.Value ? "" : reader["contactName"].ToString();
agentContact.contactWechat = reader["contactWechat"] == DBNull.Value ? "" : reader["contactWechat"].ToString();
agentContact.feeRight = reader["feeRight"] == DBNull.Value ? "" : reader["feeRight"].ToString();
agentContact.policyRight = reader["policyRight"] == DBNull.Value ? "" : reader["policyRight"].ToString();
agentContact.performanceRight = reader["performanceRight"] == DBNull.Value ? "" : reader["performanceRight"].ToString();
agentContact.studyRight = reader["studyRight"] == DBNull.Value ? "" : reader["studyRight"].ToString();
agentContact.complainRight = reader["complainRight"] == DBNull.Value ? "" : reader["complainRight"].ToString();
agentContact.monitorRight = reader["monitorRight"] == DBNull.Value ? "" : reader["monitorRight"].ToString();
agentContact.errorRight = reader["errorRight"] == DBNull.Value ? "" : reader["errorRight"].ToString();
agentContact.contactRight = reader["contactRight"] == DBNull.Value ? "" : reader["contactRight"].ToString();
list.Add(agentContact);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public AgentWechatAccount GetByAgentNo(String agentNo)
{
string sql = "SELECT agentNo,agentName,branchNo,branchName,regionName,contactId,contactName,contactEmail,contactTel,contactWechat,feeRight,policyRight,performanceRight,studyRight,complainRight,monitorRight,errorRight,contactRight,type, status from agent_wechat_account where 1=1";
sql = sql + " and ((agentNo=\"" + agentNo + "\") or (branchNo=\"" + agentNo + "\")) and feeRight='Y' limit 1";
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
// command.Parameters.AddWithValue("@contactId", primaryKey);
MySqlDataReader reader = command.ExecuteReader();
AgentWechatAccount agentContact = null;
if (reader.Read())
{
agentContact = new AgentWechatAccount();
agentContact.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString();
agentContact.status = reader["status"] == DBNull.Value ? null : reader["status"].ToString();
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.regionName = reader["regionName"] == DBNull.Value ? null : reader["regionName"].ToString();
agentContact.contactId = reader["contactId"] == DBNull.Value ? null : reader["contactId"].ToString();
agentContact.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString();
agentContact.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString();
agentContact.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString();
agentContact.contactWechat = reader["contactWechat"] == DBNull.Value ? null : reader["contactWechat"].ToString();
agentContact.feeRight = reader["feeRight"] == DBNull.Value ? null : reader["feeRight"].ToString();
agentContact.policyRight = reader["policyRight"] == DBNull.Value ? null : reader["policyRight"].ToString();
agentContact.performanceRight = reader["performanceRight"] == DBNull.Value ? null : reader["performanceRight"].ToString();
agentContact.studyRight = reader["studyRight"] == DBNull.Value ? null : reader["studyRight"].ToString();
agentContact.complainRight = reader["complainRight"] == DBNull.Value ? null : reader["complainRight"].ToString();
agentContact.monitorRight = reader["monitorRight"] == DBNull.Value ? null : reader["monitorRight"].ToString();
agentContact.errorRight = reader["errorRight"] == DBNull.Value ? null : reader["errorRight"].ToString();
agentContact.contactRight = reader["contactRight"] == DBNull.Value ? null : reader["contactRight"].ToString();
}
mycn.Close();
return agentContact;
}
}
/// <summary>
/// 异步 开始事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
//需要执行的代码
worker.ReportProgress(1, "开始同步微信账号...\r\n");
WechatAction wechatAction = new WechatAction();
List<String> feeRightUserIdsAll = new List<string>();
List<String> policyRightUserIdsAll = new List<string>();
List<String> performanceRightUserIdsAll = new List<string>();
List<String> studyRightUserIdsAll = new List<string>();
List<String> complainRightUserIdsAll = new List<string>();
List<String> monitorRightUserIdsAll = new List<string>();
List<String> errorRightUserIdsAll = new List<string>();
List<String> contactRightUserIdsAll = new List<string>();
List<String> feeRightUserIds = new List<string>();
List<String> policyRightUserIds = new List<string>();
List<String> performanceRightUserIds = new List<string>();
List<String> studyRightUserIds = new List<string>();
List<String> complainRightUserIds = new List<string>();
List<String> monitorRightUserIds = new List<string>();
List<String> errorRightUserIds = new List<string>();
List<String> contactRightUserIds = new List<string>();
for (int i = 0; i < dgAgentWechatAccount.RowCount ; i++)
{
String userid = this.dgAgentWechatAccount[6, i].Value.ToString();
feeRightUserIdsAll.Add(userid);
policyRightUserIdsAll.Add(userid);
performanceRightUserIdsAll.Add(userid);
studyRightUserIdsAll.Add(userid);
complainRightUserIdsAll.Add(userid);
monitorRightUserIdsAll.Add(userid);
errorRightUserIdsAll.Add(userid);
contactRightUserIdsAll.Add(userid);
if (this.dgAgentWechatAccount[12, i].Value.ToString().ToUpper().Equals("Y"))
{
feeRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[13, i].Value.ToString().ToUpper().Equals("Y"))
{
policyRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[14, i].Value.ToString().ToUpper().Equals("Y"))
{
performanceRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[15, i].Value.ToString().ToUpper().Equals("Y"))
{
studyRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[16, i].Value.ToString().ToUpper().Equals("Y"))
{
complainRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[17, i].Value.ToString().ToUpper().Equals("Y"))
{
monitorRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[18, i].Value.ToString().ToUpper().Equals("Y"))
{
errorRightUserIds.Add(userid);
}
if (this.dgAgentWechatAccount[19, i].Value.ToString().ToUpper().Equals("Y"))
{
contactRightUserIds.Add(userid);
}
WechatJsonUser wechatJsonUser = new WechatJsonUser();
wechatJsonUser.userid = this.dgAgentWechatAccount[6, i].Value.ToString();
wechatJsonUser.name = this.dgAgentWechatAccount[7, i].Value.ToString();
if (string.IsNullOrEmpty(wechatJsonUser.name))
{
wechatJsonUser.name = wechatJsonUser.userid;
}
wechatJsonUser.email = this.dgAgentWechatAccount[8, i].Value.ToString();
wechatJsonUser.mobile = this.dgAgentWechatAccount[9, i].Value.ToString();
wechatJsonUser.weixinid = this.dgAgentWechatAccount[10, i].Value.ToString();
wechatJsonUser.position = this.dgAgentWechatAccount[4, i].Value.ToString();
if (String.IsNullOrEmpty(wechatJsonUser.position))
{
wechatJsonUser.position = this.dgAgentWechatAccount[1, i].Value.ToString();
}
wechatJsonUser.department = new List<int>();
wechatJsonUser.department.Add(1);
worker.ReportProgress(2, "总共" + dgAgentWechatAccount.RowCount + "条记录，正在处理第" + (i + 1) + "条，同步微信账号" + wechatJsonUser.userid + "\r\n");
//Check the Wechat rule
#region
String mobile = wechatJsonUser.mobile;
String weixinid = wechatJsonUser.weixinid;
// ^[1]+[3,5,8]+\d{9}
if (Regex.IsMatch(weixinid, @"^[1]+[3,5,8]+\d{9}"))
{
mobile = weixinid;
weixinid = "";
}
else
{
if (Regex.IsMatch(weixinid, @"^\d+$"))
{
weixinid = "QQ" + weixinid;
mobile = "";
}
}
#endregion
var userData = new
{
userid = wechatJsonUser.userid,
name = wechatJsonUser.name,
department = wechatJsonUser.department,
mobile = mobile,//wechatJsonUser.mobile,
email ="",// wechatJsonUser.email,
position = wechatJsonUser.position,
weixinid =weixinid// wechatJsonUser.weixinid
};
string InsertUserJson = JsonConvert.SerializeObject(userData, Formatting.Indented);
HttpResult result = wechatAction.getUserFromWechat(wechatJsonUser.userid, Settings.Default.Wechat_Secret);
if (result.StatusCode == System.Net.HttpStatusCode.OK)
{
//表示访问成功，具体的大家就参考HttpStatusCode类
WechatJsonUser wechatJsonUserFromWechat = JsonConvert.DeserializeObject<WechatJsonUser>(result.Html);
if (!String.IsNullOrEmpty(wechatJsonUserFromWechat.userid))
{
string updateUserJson = JsonConvert.SerializeObject(userData, Formatting.Indented);
// if (wechatJsonUser.department.Count == 0)
// {
// result = wechatAction.deleteUserFromWechat(wechatJsonUser.userid, Settings.Default.Wechat_Secret);
// }
// else
// {
result = wechatAction.updateUserToWechat(Settings.Default.Wechat_Secret, updateUserJson);
if (!String.IsNullOrEmpty(wechatJsonUser.email))
{
//this.sendEmail(this.dgAgent[2, i].Value.ToString());
}
// }
}
else
{
result = wechatAction.addUserToWechat(Settings.Default.Wechat_Secret, InsertUserJson);
ReturnMessage returnMessage1 = (ReturnMessage)JsonConvert.DeserializeObject(result.Html, typeof(ReturnMessage));
if (returnMessage1 != null && returnMessage1.errcode.Equals("0"))
{
if (!String.IsNullOrEmpty(wechatJsonUser.email))
{
this.sendEmail(wechatJsonUser.email);
}
}
else
{
AgentWechatAccount agentWechatAccount = new ChinaUnion_BO.AgentWechatAccount();
agentWechatAccount.contactId = wechatJsonUser.userid;
agentWechatAccount.wechatImportStatus = returnMessage1.getErrorDescrition();
agentWechatAccountDao.UpdateWechatImportStatus(agentWechatAccount);
}
}
ReturnMessage returnMessage = (ReturnMessage)JsonConvert.DeserializeObject(result.Html, typeof(ReturnMessage));
if (returnMessage != null && returnMessage.errcode != "0")
{
this.dgAgentWechatAccount[11, i].Value = returnMessage.getErrorDescrition();
}
else
{
var userInviteData = new
{
userid = wechatJsonUser.userid
};
string inviteUserJson = JsonConvert.SerializeObject(userInviteData, Formatting.Indented);
wechatAction.inviteUserToWechat(Settings.Default.Wechat_Secret, inviteUserJson);
this.dgAgentWechatAccount[11, i].Value = "同步成功";
}
}
}
deleteTagUser(feeRightUserIdsAll, 2);
addTagUser(feeRightUserIds, 2);
deleteTagUser(policyRightUserIdsAll, 3);
addTagUser(policyRightUserIds, 3);
deleteTagUser(performanceRightUserIdsAll, 4);
addTagUser(performanceRightUserIds, 4);
deleteTagUser(studyRightUserIdsAll, 5);
addTagUser(studyRightUserIds, 5);
deleteTagUser(complainRightUserIdsAll, 6);
addTagUser(complainRightUserIds, 6);
deleteTagUser(monitorRightUserIdsAll, 7);
addTagUser(monitorRightUserIds, 7);
deleteTagUser(errorRightUserIdsAll, 8);
addTagUser(errorRightUserIds, 8);
deleteTagUser(contactRightUserIdsAll, 9);
addTagUser(contactRightUserIds, 9);
worker.ReportProgress(2, "同步微信账号完毕...\r\n");
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<StudyReceiverLog> GetList(String subject, String userId, String readTime)
{
string sql = "SELECT t1.study_sequence,t1.userId,t1.readtime,t2.subject,t2.content,t3.type,t3.agentNo,t3.agentName,t3.branchNo,t3.branchName,t3.regionName,t3.contactId,t3.contactName,t3.contactEmail,t3.contactTel,t3.contactWechat FROM tb_study_read_log t1,tb_study t2,agent_wechat_account t3 ";
sql = sql + " where t1.study_sequence = t2.sequence and t1.userId = t3.contactId ";
if (!String.IsNullOrEmpty(subject))
{
sql = sql + " and ((t2.subject like \"%" + subject + "%\")";
sql = sql + " or (t2.content like \"%" + subject + "%\"))";
}
if (!String.IsNullOrEmpty(userId))
{
sql = sql + " and ((t3.agentNo like \"%" + userId + "%\")";
sql = sql + " or (t3.agentName like \"%" + userId + "%\")";
sql = sql + " or (t3.contactId like \"%" + userId + "%\")";
sql = sql + " or (t3.contactName like \"%" + userId + "%\")";
sql = sql + " or (t3.contactWechat like \"%" + userId + "%\")";
sql = sql + " or (t3.branchNo like \"%" + userId + "%\")";
sql = sql + " or (t3.branchName like \"%" + userId + "%\"))";
}
if (!String.IsNullOrEmpty(readTime))
{
sql = sql + " and left(readtime,10)<=\""+ readTime + "\"";
}
using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
{
mycn.Open();
MySqlCommand command = new MySqlCommand(sql, mycn);
MySqlDataReader reader = command.ExecuteReader();
IList<StudyReceiverLog> list = new List<StudyReceiverLog>();
StudyReceiverLog studyReceiver = null;
while (reader.Read())
{
studyReceiver = new StudyReceiverLog();
studyReceiver.studySequence = reader["study_sequence"] == DBNull.Value ? null : reader["study_sequence"].ToString();
studyReceiver.userId = reader["userId"] == DBNull.Value ? null : reader["userId"].ToString();
studyReceiver.readtime = reader["readtime"] == DBNull.Value ? null : reader["readtime"].ToString();
Study study = new Study();
study.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString();
study.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString();
studyReceiver.study = study;
AgentWechatAccount agentContact = new AgentWechatAccount();
agentContact.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString();
// agentContact.status = reader["status"] == DBNull.Value ? null : reader["status"].ToString();
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.regionName = reader["regionName"] == DBNull.Value ? null : reader["regionName"].ToString();
agentContact.contactId = reader["contactId"] == DBNull.Value ? null : reader["contactId"].ToString();
agentContact.contactEmail = reader["contactEmail"] == DBNull.Value ? null : reader["contactEmail"].ToString();
agentContact.contactTel = reader["contactTel"] == DBNull.Value ? null : reader["contactTel"].ToString();
agentContact.contactName = reader["contactName"] == DBNull.Value ? null : reader["contactName"].ToString();
agentContact.contactWechat = reader["contactWechat"] == DBNull.Value ? null : reader["contactWechat"].ToString();
studyReceiver.agentContact = agentContact;
list.Add(studyReceiver);
}
mycn.Close();
return list;
}
}
private void btnQuery_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
//代理商信息
// Queryworker.ReportProgress(1, "代理商信息...\r\n");
// Queryworker.ReportProgress(2, "代理商渠道类型...\r\n");
//代理商渠道类型
AgentTypeDao agentTypeDao = new AgentTypeDao();
IList<AgentType> agentTypeList = agentTypeDao.GetList(dtFeeMonth.Value.ToString("yyyy-MM"));
dgAgentType.Rows.Clear();
dgAgentType.Columns.Clear();
if (agentTypeList != null && agentTypeList.Count > 0)
{
dgAgentType.Columns.Add("代理商编号", "代理商编号");
dgAgentType.Columns.Add("渠道类型", "渠道类型");
for (int i = 0; i < agentTypeList.Count; i++)
{
dgAgentType.Rows.Add();
DataGridViewRow row = dgAgentType.Rows[i];
row.Cells[0].Value = agentTypeList[i].agentNo;
row.Cells[1].Value = agentTypeList[i].agentType;
}
}
// Queryworker.ReportProgress(3, "代理商渠道类型说明...\r\n");
//代理商渠道类型说明
AgentTypeCommentDao agentTypeCommentDao = new AgentTypeCommentDao();
IList<AgentTypeComment> agentTypeCommentList = agentTypeCommentDao.GetList(dtFeeMonth.Value.ToString("yyyy-MM"));
dgAgentTypeComment.Rows.Clear();
dgAgentTypeComment.Columns.Clear();
if (agentTypeCommentList != null && agentTypeCommentList.Count > 0)
{
dgAgentTypeComment.Columns.Add("渠道类型", "渠道类型");
dgAgentTypeComment.Columns.Add("佣金说明", "佣金说明");
for (int i = 0; i < agentTypeCommentList.Count; i++)
{
dgAgentTypeComment.Rows.Add();
DataGridViewRow row = dgAgentTypeComment.Rows[i];
row.Cells[0].Value = agentTypeCommentList[i].agentType;
row.Cells[1].Value = agentTypeCommentList[i].agentTypeComment;
}
}
// Queryworker.ReportProgress(4, "代理商佣金...\r\n");
//代理商佣金
AgentFeeDao agentFeeDao = new AgentFeeDao();
IList<AgentFee> agentFeeList = agentFeeDao.GetList(dtFeeMonth.Value.ToString("yyyy-MM"));
AgentWechatAccount agentWechatAccount = new ChinaUnion_BO.AgentWechatAccount();
AgentWechatAccountDao agentWechatAccountDao = new ChinaUnion_DataAccess.AgentWechatAccountDao();
dgAgentFee.Rows.Clear();
dgAgentFee.Columns.Clear();
if (agentFeeList != null && agentFeeList.Count > 0)
{
this.grpAgentFee.Text = "月度佣金明细信息(" + agentFeeList.Count + ")";
dgAgentFee.Columns.Add("代理商编号", "代理商编号");
dgAgentFee.Columns.Add("代理商名称", "代理商名称");
dgAgentFee.Columns.Add("代理商类型", "代理商类型");
dgAgentFee.Columns.Add("代理商类型说明", "代理商类型说明");
dgAgentFee.Columns.Add("联系人邮件", "联系人邮件");
dgAgentFee.Columns.Add("联系人名称", "联系人名称");
dgAgentFee.Columns.Add("告知单编号", "告知单编号");
// dgAgentFee.Columns["联系人邮件"].Visible = false;
// dgAgentFee.Columns["联系人名称"].Visible = false;
for (int i = 0; i < agentFeeList.Count; i++)
{
if (i == 0)
{
for (int j = 1; j <= 100; j++)
{
FieldInfo feeNameField = agentFeeList[i].GetType().GetField("feeName" + j);
// FieldInfo feeField = agentFeeList[i].GetType().GetField("fee" + j);
String feeNameFieldValue = feeNameField.GetValue(agentFeeList[i]) == null ? null : feeNameField.GetValue(agentFeeList[i]).ToString();
// String feeFieldValue = feeField.GetValue(agentFeeList[i]) == null ? null : feeField.GetValue(agentFeeList[i]).ToString(); ;
if (!String.IsNullOrEmpty(feeNameFieldValue) && !String.IsNullOrWhiteSpace(feeNameFieldValue))
{
dgAgentFee.Columns.Add(feeNameFieldValue, feeNameFieldValue);
}
}
this.dgAgentFee.Columns.Add("总计", "总计");
this.dgAgentFee.Columns.Add("开票金额", "开票金额");
this.dgAgentFee.Columns.Add("过网开票金额", "过网开票金额");
}
dgAgentFee.Rows.Add();
DataGridViewRow row = dgAgentFee.Rows[i];
row.Cells[0].Value = agentFeeList[i].agentNo;
row.Cells[1].Value = agentFeeList[i].agent.agentName;
row.Cells[2].Value = agentFeeList[i].agent.agentType;
row.Cells[3].Value = agentFeeList[i].agent.agentTypeComment;
agentWechatAccount = agentWechatAccountDao.GetByAgentNo(agentFeeList[i].agentNo);
if (agentWechatAccount != null)
{
agentFeeList[i].agent.contactEmail = agentWechatAccount.contactEmail;
agentFeeList[i].agent.contactName = agentWechatAccount.contactName;
}
row.Cells[4].Value = agentFeeList[i].agent.contactEmail;
row.Cells[5].Value = agentFeeList[i].agent.contactName;
row.Cells[6].Value = agentFeeList[i].agentFeeSeq;
int feeColIndex = 6;
int fixColCount = 7;
for (int j = 1; j <= 100 ; j++)
{
// FieldInfo feeNameField = agentFeeList[i].GetType().GetField("feeName" + j);
FieldInfo feeField = agentFeeList[i].GetType().GetField("fee" + j);
// String feeNameFieldValue = feeNameField.GetValue(agentFeeList[i]) == null ? null : feeNameField.GetValue(agentFeeList[i]).ToString();
String feeFieldValue = feeField.GetValue(agentFeeList[i]) == null ? null : feeField.GetValue(agentFeeList[i]).ToString(); ;
if (dgAgentFee.Columns.Count >= fixColCount + j)
{
row.Cells[feeColIndex + j].Value = feeFieldValue;
}
}
row.Cells[dgAgentFee.Columns.Count - 3].Value = agentFeeList[i].feeTotal;
row.Cells[dgAgentFee.Columns.Count - 2].Value = agentFeeList[i].invoiceFee;
row.Cells[dgAgentFee.Columns.Count - 1].Value = agentFeeList[i].preInvoiceFee;
}
}
this.dgAgentType.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dgAgentType.AutoResizeColumns();
this.dgAgentTypeComment.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dgAgentTypeComment.AutoResizeColumns();
//this.dgAgent.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
//dgAgent.AutoResizeColumns();
if (dgAgentFee.RowCount > 0)
{
this.btnMail.Visible = true;
}
else
{
this.btnMail.Visible = false;
}
this.Cursor = Cursors.Default;
}
private void saveGridData(DataGridView dg, String type)
{
ArrayList AgentNoList = new ArrayList();
ArrayList WechatNoList = new ArrayList();
for (int i = 0; i < dgAgentWechatAccount.RowCount; i++)
{
AgentNoList.Add(dgAgentWechatAccount[4, i].Value.ToString());
if (!String.IsNullOrEmpty(dgAgentWechatAccount[7, i].Value.ToString()))
{
WechatNoList.Add(dgAgentWechatAccount[7, i].Value.ToString());
}
}
AgentWechatAccountDao agentWechatAccountDao = new AgentWechatAccountDao();
for (int i = 0; i < dg.RowCount; i++)
{
AgentWechatAccount agentWechatAccount = new AgentWechatAccount();
int index = 0;
agentWechatAccount.type = type;
if (dg.Columns[0].HeaderText.Equals("区县"))
{
agentWechatAccount.regionName = dg[index++, i].Value.ToString();
agentWechatAccount.branchNo = dg[index++, i].Value.ToString();
agentWechatAccount.branchName = dg[index++, i].Value.ToString();
//agentWechatAccount.type = "直供渠道联系人";
}
else if (dg.Columns[0].HeaderText.Equals("代理商编号"))
{
agentWechatAccount.agentNo = dg[index++, i].Value.ToString();
agentWechatAccount.agentName = dg[index++, i].Value.ToString();
if (dg.Columns[2].HeaderText.Equals("渠道编码"))
{
agentWechatAccount.branchNo = dg[index++, i].Value.ToString();
agentWechatAccount.branchName = dg[index++, i].Value.ToString();
}
}
agentWechatAccount.contactEmail = dg[index++, i].Value.ToString();
agentWechatAccount.contactId = dg[index++, i].Value.ToString();
agentWechatAccount.contactName = dg[index++, i].Value.ToString();
agentWechatAccount.contactTel = dg[index++, i].Value.ToString();
agentWechatAccount.contactWechat = dg[index++, i].Value.ToString();
agentWechatAccount.feeRight = dg[index++, i].Value.ToString();
agentWechatAccount.policyRight = dg[index++, i].Value.ToString();
agentWechatAccount.performanceRight = dg[index++, i].Value.ToString();
agentWechatAccount.studyRight = dg[index++, i].Value.ToString();
agentWechatAccount.complainRight = dg[index++, i].Value.ToString();
agentWechatAccount.monitorRight = dg[index++, i].Value.ToString();
agentWechatAccount.errorRight = dg[index++, i].Value.ToString();
agentWechatAccount.contactRight = dg[index++, i].Value.ToString();
if (String.IsNullOrEmpty(agentWechatAccount.branchNo))
agentWechatAccount.branchNo = "";
if (AgentNoList.Contains(agentWechatAccount.contactId) && !type.Equals("代理商联系人"))
{
continue;
}
if (WechatNoList.Contains(agentWechatAccount.contactWechat) && !type.Equals("代理商联系人"))
{
continue;
}
agentWechatAccountDao.Delete(agentWechatAccount.contactId);
agentWechatAccountDao.Add(agentWechatAccount);
}
}
