/// <summary>
/// 修改数据
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public int Update(Study entity)
{
string sql = "UPDATE tb_study 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(Study entity)
{
string sql = "INSERT INTO tb_study (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<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;
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <returns></returns>
public IList<Study> 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_study";
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<Study> list = new List<Study>();
Study study = null;
while (reader.Read())
{
study = new Study();
study.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString();
study.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString();
study.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString();
study.sender = reader["sender"] == DBNull.Value ? null : reader["sender"].ToString();
study.attachmentName = reader["attachmentName"] == DBNull.Value ? null : reader["attachmentName"].ToString();
study.attachment = reader["attachment"] == DBNull.Value ? null : (byte[])reader["attachment"];
study.creatTime = reader["creatTime"] == DBNull.Value ? null : reader["creatTime"].ToString();
study.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString();
study.validateStartTime = reader["validateStartTime"] == DBNull.Value ? null : reader["validateStartTime"].ToString();
study.validateEndTime = reader["validateEndTime"] == DBNull.Value ? null : reader["validateEndTime"].ToString();
study.isValidate = reader["isValidate"] == DBNull.Value ? null : reader["isValidate"].ToString();
study.isDelete = reader["isDelete"] == DBNull.Value ? null : reader["isDelete"].ToString();
study.deleteTime = reader["deleteTime"] == DBNull.Value ? null : reader["deleteTime"].ToString();
study.sequence = reader["sequence"] == DBNull.Value ? null : reader["sequence"].ToString();
study.toAll = reader["toAll"] == DBNull.Value ? null : reader["toAll"].ToString();
list.Add(study);
}
mycn.Close();
return list;
}
}
/// <summary>
/// 根据主键查询
/// </summary>
/// <param name="primaryKey"></param>
/// <returns></returns>
public Study Get(int primaryKey)
{
string sql = "SELECT agentType,sequence,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime, validateEndTime,isValidate, isDelete, deleteTime,toAll from tb_study 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();
Study study = null;
if (reader.Read())
{
study = new Study();
study.agentType = reader["agentType"] == DBNull.Value ? null : reader["agentType"].ToString();
study.subject = reader["subject"] == DBNull.Value ? null : reader["subject"].ToString();
study.content = reader["content"] == DBNull.Value ? null : reader["content"].ToString();
study.sender = reader["sender"] == DBNull.Value ? null : reader["sender"].ToString();
study.attachmentName = reader["attachmentName"] == DBNull.Value ? null : reader["attachmentName"].ToString();
study.attachment = reader["attachment"] == DBNull.Value ? null : (byte[])reader["attachment"];
study.creatTime = reader["creatTime"] == DBNull.Value ? null : reader["creatTime"].ToString();
study.type = reader["type"] == DBNull.Value ? null : reader["type"].ToString();
study.validateStartTime = reader["validateStartTime"] == DBNull.Value ? null : reader["validateStartTime"].ToString();
study.validateEndTime = reader["validateEndTime"] == DBNull.Value ? null : reader["validateEndTime"].ToString();
study.isValidate = reader["isValidate"] == DBNull.Value ? null : reader["isValidate"].ToString();
study.isDelete = reader["isDelete"] == DBNull.Value ? null : reader["isDelete"].ToString();
study.deleteTime = reader["deleteTime"] == DBNull.Value ? null : reader["deleteTime"].ToString();
study.sequence = reader["sequence"] == DBNull.Value ? null : reader["sequence"].ToString();
study.toAll = reader["toAll"] == DBNull.Value ? null : reader["toAll"].ToString();
}
mycn.Close();
return study;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
//MessageBox.Show(this.lstAgentType.CheckedItems.Count.ToString());
//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;
Study study = new Study();
study.sequence = this.txtSequence.Text.Trim();
study.type = "在线学习";
study.subject = this.txtSubject.Text.Trim();
study.content = this.txtContent.Text.Trim();
study.sender = this.loginUser.name;
study.validateStartTime = this.dtStartDate.Value.ToString("yyyy-MM-dd");
study.validateEndTime = this.dtEndDate.Value.ToString("yyyy-MM-dd");
study.creatTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
study.isDelete = "N";
study.isValidate = "Y";
study.agentType = "";
foreach (object item in this.chkAgentType.CheckedItems)
{
study.agentType = study.agentType + item.ToString()+";";
}
// study.agentType = this.cboAgentType.Text;
if (this.lstAgentType.CheckedItems.Contains("所有渠道"))
{
study.toAll = "Y";
}
else
{
study.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)
{
study.attachmentName = this.txtAttachmentName.Text;
study.attachment = attachmentBytes;
}
}
else
{
if (!String.IsNullOrEmpty(study.sequence))
{
Study tempStudy = this.studyDao.Get(Int32.Parse(study.sequence));
study.attachment = tempStudy.attachment;
study.attachmentName = tempStudy.attachmentName;
}
}
if (!String.IsNullOrEmpty(study.sequence))
{
this.studyDao.Update(study);
}
else
{
studyDao.Add(study);
study = studyDao.GetBySubject(study.subject);
}
studyReceiverDao.Delete(study.sequence);
for (int i = 0; i < lstAgentType.Items.Count; i++)
{
if (lstAgentType.GetItemChecked(i))
{
StudyReceiver studyReceiver = new StudyReceiver();
studyReceiver.studySequence = study.sequence;
studyReceiver.receiver = lstAgentType.Items[i].ToString();
studyReceiver.type = "渠道类型";
studyReceiverDao.Add(studyReceiver);
}
}
for (int i = 0; i < lstGroup.Items.Count; i++)
{
if (lstGroup.GetItemChecked(i))
{
StudyReceiver studyReceiver = new StudyReceiver();
studyReceiver.studySequence = study.sequence;
studyReceiver.receiver = lstGroup.Items[i].ToString();
studyReceiver.type = "自定义组";
studyReceiverDao.Add(studyReceiver);
}
}
this.prepareGrid(this.txtSearchCondition.Text);
MessageBox.Show("操作完成");
this.Cursor = Cursors.Default;
}