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