示例#1
1
        /// <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;
            }
        }
示例#2
0
        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;
            }
        }
示例#3
0
        /// <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;
            }
        }
示例#4
0
        /// <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;
            }
               
        }
示例#5
0
        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;
        }
示例#6
0
        /// <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;
            }
        }