/// <summary> /// 修改数据 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int Update(Policy entity) { string sql = "UPDATE tb_policy SET agentType=@agentType,subject=@subject,content=@content,sender=@sender,attachment=@attachment,attachmentName=@attachmentName,creatTime=@creatTime,"; sql = sql + " type=@type,validateStartTime=@validateStartTime,validateEndTime=@validateEndTime,isValidate=@isValidate,isDelete=@isDelete,deleteTime=@deleteTime,toAll=@toAll where sequence=@sequence "; //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("@agentType", entity.agentType); command.Parameters.AddWithValue("@sequence", entity.sequence); command.Parameters.AddWithValue("@subject", entity.subject); command.Parameters.AddWithValue("@content", entity.content); command.Parameters.AddWithValue("@sender", entity.sender); command.Parameters.AddWithValue("@attachment", entity.attachment); command.Parameters.AddWithValue("@attachmentName", entity.attachmentName); command.Parameters.AddWithValue("@creatTime", entity.creatTime); command.Parameters.AddWithValue("@type", entity.type); command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime); command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime); command.Parameters.AddWithValue("@isValidate", entity.isValidate); command.Parameters.AddWithValue("@isDelete", entity.isDelete); command.Parameters.AddWithValue("@deleteTime", entity.deleteTime); command.Parameters.AddWithValue("@toAll", entity.toAll); 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(Policy entity) { string sql = "INSERT INTO tb_policy (agentType,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime,validateEndTime, isValidate, isDelete, deleteTime,toAll) VALUE (@agentType,@subject,@content,@sender,@attachment,@attachmentName,@creatTime,@type, @validateStartTime,@validateEndTime, @isValidate, @isDelete, @deleteTime,@toAll)"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@agentType", entity.agentType); command.Parameters.AddWithValue("@subject", entity.subject); command.Parameters.AddWithValue("@content", entity.content); command.Parameters.AddWithValue("@sender", entity.sender); command.Parameters.AddWithValue("@attachment", entity.attachment); command.Parameters.AddWithValue("@attachmentName", entity.attachmentName); command.Parameters.AddWithValue("@creatTime", entity.creatTime); command.Parameters.AddWithValue("@type", entity.type); command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime); command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime); command.Parameters.AddWithValue("@isValidate", entity.isValidate); command.Parameters.AddWithValue("@isDelete", entity.isDelete); command.Parameters.AddWithValue("@deleteTime", entity.deleteTime); command.Parameters.AddWithValue("@toAll", entity.toAll); int i = command.ExecuteNonQuery(); mycn.Close(); mycn.Dispose(); return i; } }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<Policy> GetAllList(string keyWord,String type) { string sql = "SELECT agentType,sequence,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime, validateEndTime,isValidate, isDelete, deleteTime,toAll from tb_policy"; sql = sql + " where 1=1 "; if (!String.IsNullOrEmpty(keyWord)) { sql = sql + " and ((subject like \"%" + keyWord + "%\") or (content like \"%" + keyWord + "%\"))"; } if (!String.IsNullOrEmpty(type)) { sql = sql + " and type= \"" + type + "\""; } sql = sql + " order by creatTime desc "; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); MySqlDataReader reader = command.ExecuteReader(); IList<Policy> list = new List<Policy>(); Policy policy = null; while (reader.Read()) { policy = new Policy(); policy.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString(); policy.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString(); policy.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString(); policy.sender = reader["sender"] == DBNull.Value ? null : reader["sender"].ToString(); policy.attachmentName = reader["attachmentName"] == DBNull.Value ? null : reader["attachmentName"].ToString(); policy.attachment = reader["attachment"] == DBNull.Value ? null : (byte[])reader["attachment"]; policy.creatTime = reader["creatTime"] == DBNull.Value ? null : reader["creatTime"].ToString(); policy.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); policy.validateStartTime = reader["validateStartTime"] == DBNull.Value ? null : reader["validateStartTime"].ToString(); policy.validateEndTime = reader["validateEndTime"] == DBNull.Value ? null : reader["validateEndTime"].ToString(); policy.isValidate = reader["isValidate"] == DBNull.Value ? null : reader["isValidate"].ToString(); policy.isDelete = reader["isDelete"] == DBNull.Value ? null : reader["isDelete"].ToString(); policy.deleteTime = reader["deleteTime"] == DBNull.Value ? null : reader["deleteTime"].ToString(); policy.sequence = reader["sequence"] == DBNull.Value ? null : reader["sequence"].ToString(); policy.toAll = reader["toAll"] == DBNull.Value ? null : reader["toAll"].ToString(); list.Add(policy); } mycn.Close(); return list; } }
/// <summary> /// 根据主键查询 /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public Policy Get(int primaryKey) { string sql = "SELECT agentType,sequence,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime, validateEndTime,isValidate, isDelete, deleteTime,toAll from tb_policy where sequence=@sequence"; using (MySqlConnection mycn = new MySqlConnection(mysqlConnection)) { mycn.Open(); MySqlCommand command = new MySqlCommand(sql, mycn); command.Parameters.AddWithValue("@sequence", primaryKey); MySqlDataReader reader = command.ExecuteReader(); Policy policy = null; if (reader.Read()) { policy = new Policy(); policy.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString(); policy.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString(); policy.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString(); policy.sender = reader["sender"] == DBNull.Value ? null : reader["sender"].ToString(); policy.attachmentName = reader["attachmentName"] == DBNull.Value ? null : reader["attachmentName"].ToString(); policy.attachment = reader["attachment"] == DBNull.Value ? null : (byte[])reader["attachment"]; policy.creatTime = reader["creatTime"] == DBNull.Value ? null : reader["creatTime"].ToString(); policy.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString(); policy.validateStartTime = reader["validateStartTime"] == DBNull.Value ? null : reader["validateStartTime"].ToString(); policy.validateEndTime = reader["validateEndTime"] == DBNull.Value ? null : reader["validateEndTime"].ToString(); policy.isValidate = reader["isValidate"] == DBNull.Value ? null : reader["isValidate"].ToString(); policy.isDelete = reader["isDelete"] == DBNull.Value ? null : reader["isDelete"].ToString(); policy.deleteTime = reader["deleteTime"] == DBNull.Value ? null : reader["deleteTime"].ToString(); policy.sequence = reader["sequence"] == DBNull.Value ? null : reader["sequence"].ToString(); policy.toAll = reader["toAll"] == DBNull.Value ? null : reader["toAll"].ToString(); } mycn.Close(); return policy; } }
private void btnSave_Click(object sender, EventArgs e) { //MessageBox.Show(this.lstAgentType.CheckedItems.Count.ToString()); //return; if (String.IsNullOrEmpty(this.cbType.Text.Trim())) { MessageBox.Show("请选择类型!"); this.txtSubject.Focus(); return; } if (String.IsNullOrEmpty(this.txtSubject.Text.Trim())) { MessageBox.Show("请输入名称!"); this.txtSubject.Focus(); return; } if (String.IsNullOrEmpty(this.txtContent.Text.Trim())) { MessageBox.Show("请输入内容!"); this.txtContent.Focus(); return; } if (this.dtEndDate.Value.CompareTo(this.dtStartDate.Value) <= 0) { MessageBox.Show("有效期结束时间必须大于开始时间"); return; } this.Cursor = Cursors.WaitCursor; Policy policy = new Policy(); policy.sequence = this.txtSequence.Text.Trim(); policy.type = this.cbType.Text; policy.subject = this.txtSubject.Text.Trim(); policy.content = this.txtContent.Text.Trim(); policy.sender = this.loginUser.name; policy.validateStartTime = this.dtStartDate.Value.ToString("yyyy-MM-dd"); policy.validateEndTime = this.dtEndDate.Value.ToString("yyyy-MM-dd"); policy.creatTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); policy.isDelete = "N"; policy.isValidate = "Y"; policy.agentType = ""; foreach (object item in this.chkAgentType.CheckedItems) { policy.agentType = policy.agentType + item.ToString()+";"; } // policy.agentType = this.cboAgentType.Text; if (this.lstAgentType.CheckedItems.Contains("所有渠道")) { policy.toAll = "Y"; } else { policy.toAll = "N"; } byte[] b = new byte[0]; String fullpath = this.txtAttachmentLocation.Text; if (!String.IsNullOrEmpty(fullpath)) { FileStream fs = new FileStream(fullpath, FileMode.Open, FileAccess.Read); byte[] attachmentBytes = new byte[fs.Length]; fs.Read(attachmentBytes, 0, System.Convert.ToInt32(fs.Length)); // BinaryReader br = new BinaryReader(fs); // attachmentBytes = br.ReadBytes(Convert.ToInt32(fs.Length)); fs.Close(); // br.Close(); if (attachmentBytes.Length > 0) { policy.attachmentName = this.txtAttachmentName.Text; policy.attachment = attachmentBytes; } } else { if (!String.IsNullOrEmpty(policy.sequence)) { Policy tempPolicy = this.policyDao.Get(Int32.Parse(policy.sequence)); policy.attachment = tempPolicy.attachment; policy.attachmentName = tempPolicy.attachmentName; } } if (!String.IsNullOrEmpty(policy.sequence)) { this.policyDao.Update(policy); } else { policyDao.Add(policy); policy = policyDao.GetBySubject(policy.subject); } policyReceiverDao.Delete(policy.sequence); for (int i = 0; i < lstAgentType.Items.Count; i++) { if (lstAgentType.GetItemChecked(i)) { PolicyReceiver policyReceiver = new PolicyReceiver(); policyReceiver.policySequence = policy.sequence; policyReceiver.receiver = lstAgentType.Items[i].ToString(); policyReceiver.type = "渠道类型"; policyReceiverDao.Add(policyReceiver); } } for (int i = 0; i < lstGroup.Items.Count; i++) { if (lstGroup.GetItemChecked(i)) { PolicyReceiver policyReceiver = new PolicyReceiver(); policyReceiver.policySequence = policy.sequence; policyReceiver.receiver = lstGroup.Items[i].ToString(); policyReceiver.type = "自定义组"; policyReceiverDao.Add(policyReceiver); } } this.prepareGrid(this.txtSearchCondition.Text); MessageBox.Show("操作完成"); this.Cursor = Cursors.Default; }
/// <summary> /// 查询集合 /// </summary> /// <returns></returns> public IList<PolicyReceiverLog> GetList(String subject, String userId, String readTime) { string sql = "SELECT t1.policy_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_policy_read_log t1,tb_policy t2,agent_wechat_account t3 "; sql = sql + " where t1.policy_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<PolicyReceiverLog> list = new List<PolicyReceiverLog>(); PolicyReceiverLog policyReceiver = null; while (reader.Read()) { policyReceiver = new PolicyReceiverLog(); policyReceiver.policySequence = reader["policy_sequence"] == DBNull.Value ? null : reader["policy_sequence"].ToString(); policyReceiver.userId = reader["userId"] == DBNull.Value ? null : reader["userId"].ToString(); policyReceiver.readtime = reader["readtime"] == DBNull.Value ? null : reader["readtime"].ToString(); Policy policy = new Policy(); policy.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString(); policy.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString(); policyReceiver.policy = policy; 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(); policyReceiver.agentContact = agentContact; list.Add(policyReceiver); } mycn.Close(); return list; } }