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); } }