<?php
require_once(ONU_ROOT . 'application/lib/data/Base.class.php');

class AdvertAdminAction extends BaseAction{

		//添加用户
		public function addUser($uname,$pwd){
			$sql    = "insert into ly_user(uname,pwd) values('{$uname}','{$pwd}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		//登录
		public function selectUser($uname,$pwd){
			$sql    = "select * from ly_user where uname='$uname' and pwd='$pwd' and active=1 limit 1";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}
		
		//查询用户，通过uname
		public function selectUserByName($uname){
			$sql    = "select * from ly_user where uname='$uname' limit 1";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}
		
		//查询所有用户
		public function selectAllUser(){
			$sql    = "select * from ly_user ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}
		
		//查询属于部门领导的用户
		public function selectHeaderUser(){
			$sql    = "select id,uname from ly_user where role=2";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}
		
		//修改用户密码
		public function updateUserPwd($uname,$pwd){
			$sql    = "update ly_user set pwd='{$pwd}' where uname='{$uname}'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
		
		//修改用户权限
		public function updateUserPersission($uname,$persission){
			$sql    = "update ly_user set permission='{$persission}' where uname='{$uname}'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
		
		//修改用户角色及直属领导
		public function updateUserRole($data){
			$sql    = "update ly_user set role='{$data['role']}',header_uid='{$data['header_uid']}' where uname='{$data['uname']}'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		//删除用户
		public function deleteUser($uname){
			$sql    = "delete from ly_user where uname='{$uname}'";	
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//查询所有的图片
		public function selectAllImages(){
			$sql    = "select * from ly_smt_images ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//导入客户，添加客户信息
		public function add_customer($data){
			$sql    = "insert into ly_customer(email,uname,country,city,tel,uid,add_time,group_name) values
						('{$data['email']}','{$data['uname']}','{$data['country']}','{$data['city']}','{$data['tel']}','{$data['uid']}','{$data['add_time']}','{$data['group_name']}')";

			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		public function add_sms_customer($data){
			$sql = "replace into ly_sms_customer(uname, country, tel, uid, group_name) values
				('{$data['uname']}','{$data['country']}','{$data['tel']}','{$data['uid']}','{$data['group_name']}')";

			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		public function import_sms_customer($values){
			$sql = "replace into ly_sms_customer(uname, country, tel, uid, group_name, is_block,is_send) values" . $values;
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}
  
  		//导入客户，批量添加客户信息
		public function addpl_customer($data){

			$sql    = "replace into ly_customer(email,uname,country,city,tel,uid,add_time,group_name) values ";
			foreach ($data as $k => $v) {
				$sql    .= "(\"{$v['email']}\",\"{$v['uname']}\",\"{$v['country']}\",\"{$v['city']}\",\"{$v['tel']}\",\"{$v['uid']}\",\"{$v['add_time']}\",\"{$v['group_name']}\"),";
			}
			$sql = substr($sql,0,-1);
			$sql .= ";";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		//导入黑名单，添加黑名单信息
		public function add_blacklist($data){
			$sql    = "insert into ly_blacklist(email,add_time) values
							('{$data['email']}','{$data['add_time']}')";

			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		public function importBlacklist($values){
			$sql    = "replace into ly_blacklist(email, add_time) values" . $values;

			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		public function selectCustomerCount($where){
			$sql    = "select count(*) as count  from ly_customer  $where  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0]['count'];
		}

		public function selectSmsCustomerCount($where){
			$sql    = "select count(*) as count  from ly_sms_customer  $where  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0]['count'];
		}

		public function selectBlacklistCount($where){
			$sql    = "select count(*) as count  from ly_blacklist  $where  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0]['count'];
		}


	//查询所有客户
		public function selesctCustomer($where,$limit){
			if(!empty($limit)){
				$sql    = "select *  from ly_customer  $where order by id desc limit $limit ";
			}else{
				$sql    = "select *  from ly_customer  $where order by id desc ";
			}
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function selesctSmsCustomer($where,$limit=0){
			if(!empty($limit)){
				$sql    = "select *  from ly_sms_customer  $where order by id desc limit $limit ";
			}else{
				$sql    = "select *  from ly_sms_customer  $where order by id desc ";
			}
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function selectSmsBlackList() {
			$sql = "select tel from ly_sms_customer where is_block=1 order by id desc ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}
  		//查询UID下所有客户Email
		public function selesctCustomerByUid($uid){
			$sql    = "select email  from ly_customer where 1=1 and uid = $uid  ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//查询所有黑名单
		public function selesctBlacklist($where = '', $limit = 100){
			if(!empty($limit)){
				$sql    = "select *  from ly_blacklist  $where order by id desc limit $limit ";
			}else{
				$sql    = "select *  from ly_blacklist  $where order by id desc ";
			}
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		public function selectAllBlack(){
			$sql = "select email from ly_blacklist";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//查询客户信息
		public function selectOneCustomer($id){
			$sql    = "select *  from ly_customer  where id='$id'  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}
        //根据手机号查询客户信息
        public function selectOneCustomerBymobile($id){
            $sql    = "select *  from ly_sms_customer  where tel='$id'  ";
            $result = $this->getDbEntity()->query($sql);
            return $result[0];
        }
		public function selectOneSmsCustomer($id){
			$sql    = "select *  from ly_sms_customer  where id='$id'  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		//查询黑名单信息
		public function selectOneBlack($id){
			$sql    = "select *  from ly_blacklist  where id='$id'  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}


	//根据邮箱查询客户信息
		public function selectCustomerByEmail($email,$uid){
			$sql    = "select *  from ly_customer  where email='$email' and uid='$uid'  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		public function selectCustomerByTel($tel, $uid){
			$sql    = "select *  from ly_sms_customer  where tel='$tel' and uid='$uid'  ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}


		//根据邮箱查询H黑名单信息
		public function selectBlackByEmail($email){
			$sql    = "select *  from ly_blacklist  where email='$email' ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		//编辑客户信息
		public function updateCustomer($data,$id){
			$sql    = "update ly_customer set email='{$data['email']}',uname='{$data['uname']}',group_name='{$data['group_name']}',
 						country = '{$data['country']}',city = '{$data['city']}',tel='{$data['tel']}'
 						where id='$id'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
        public function updateSmsCustomerBlcak($data){
            $sql    = "update ly_sms_customer set is_block=1
                            where id={$data['id']}";
            $result = $this->getDbEntity()->execute_sql($sql);
            return $result;
        }


		public function updateSmsCustomer($data,$id){
			$sql    = "update ly_sms_customer set uname='{$data['uname']}',group_name='{$data['group_name']}',
 						country = '{$data['country']}',tel='{$data['tel']}'
 						where id='$id'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		//编辑黑名单信息
		public function updateBlack($data,$id){
			$sql    = "update ly_blacklist set email='{$data['email']}'
							where id='$id'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		//编辑客户信息
		public function updateCustomerByEmail($data){
			$sql    = "update ly_customer set uname='{$data['uname']}',group_name='{$data['group_name']}',
							country = '{$data['country']}',city = '{$data['city']}',tel='{$data['tel']}'
							where uid='{$data['uid']}' and email='{$data['email']}'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
 		 //批量更新客户用户组信息
		public function updateCustomerGroupByEmail($data){

			$sql = "insert into ly_im_data_tmp (sql_content) values ";
			foreach ($data as $k => $v) {
				$sql_content['tabname']   = "ly_customer";
				$sql_content['exc']   = " update";
				$sql_content['data']   = $v;

				$sql .= "('".addslashes(serialize($sql_content))."'),";
			}

			$sql = substr($sql,0,-1);
			$sql .= ";";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		//删除客户信息
		public function deleteCustomer($id,$uid){
			$sql    = "delete  from ly_customer  where id='$id' and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteSmsCustomer($id, $uid){
			$sql    = "delete from ly_sms_customer  where id='$id' and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteSmsCustomerByExcel($telsStr, $uid){
			$sql    = "delete from ly_sms_customer  where tel in ($telsStr) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteSmsCustomerByTel($telsStr){
			$sql    = "delete from ly_sms_customer  where tel in ($telsStr) ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//删除黑名单
		public function deleteBlack($id){
			$sql    = "delete  from ly_blacklist  where id='$id' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//按客户分组删除客户信息
		public function deleteCustomerByGroup($group_name,$uid){
			$sql    = "delete  from ly_customer  where group_name='$group_name' and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteSmsCustomerByGroup($group_name, $uid){
			$sql    = "delete from ly_sms_customer  where group_name='$group_name' and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//删除多个分组的客户信息
		public function deleteBatchCus($group_name,$uid){
			$sql    = "delete  from ly_customer  where group_name in ($group_name) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//批量删除客户信息
		public function deleteBatchCustomer($id_array,$uid){
			$sql    = "delete  from ly_customer  where id in ($id_array) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteBatchSmsCustomer($id_array, $uid){
			$sql    = "delete from ly_sms_customer  where id in ($id_array) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//批量删除黑名单
		public function deleteBatchBlack($id_array){
			$sql    = "delete  from ly_blacklist  where id in ($id_array) ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//批量删除用户分组
		public function deleteBatchUserGroup($id_array,$uid){
			$sql    = "delete  from ly_user_group  where id in ($id_array) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteBatchSmsUserGroup($id_array,$uid){
			$sql    = "delete  from ly_sms_user_group  where id in ($id_array) and uid='$uid' ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//普通发送插入日志表  普通邮件发送日志表
		public function inserEmail1Log($data){
			$sql    = "insert into ly_send_email1_log(to_user,subject,content,replay_to,`time`,message,uid) values
						('{$data['to']}','{$data['subject']}','{$data['html']}','{$data['replyto']}','{$data['time']}','{$data['message']}','{$data['uid']}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}
		public function deleteBatchCustomerByEmail($id_array,$uid){
			$sql    = "delete  from ly_customer  where email in ($id_array) and uid='$uid' ";
		
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//邮件模板列表
		public function select_msg_list($where,$limit){
			if(!empty($limit)){
				$sql    = "select *  from ly_email_template  $where order by status asc,id desc limit $limit";
			}else{
				$sql    = "select *  from ly_email_template  $where order by status asc,id desc";
			}
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

        //短信模板列表
        public function select_smstm_list($where,$limit=0){

            if(!empty($limit)){
                $sql    = "select *  from ly_sms_template  $where order by status asc,id desc limit $limit";
            }else{
                $sql    = "select *  from ly_sms_template  $where order by status asc,id desc";
            }

            $result = $this->getDbEntity()->query($sql);
            return $result;
        }

		//查询邮件模板数量
		public function select_msg_count($where){
			$sql    = "select count(*) as count  from ly_email_template  $where ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0]['count'];
		}
        //查询短信模板数量
        public function select_smstm_count($where){
            $sql    = "select count(*) as count  from ly_sms_template  $where ";
            $result = $this->getDbEntity()->query($sql);
            return $result[0]['count'];
        }

		//添加邮件模板
		public function add_email_msg($data){
			$sql    = "insert into ly_email_template(msg_title,msg_content,status,add_time,`update_time`,uid,`type`) values
						('{$data['msg_title']}','{$data['msg_content']}','{$data['status']}','{$data['add_time']}','{$data['update_time']}','{$data['uid']}','{$data['type']}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}
        //添加短信模板
        public function add_smstm_msg($data){
            $sql    = "insert into ly_sms_template(msg_title,msg_content,status,add_time,`update_time`,uid,`type`) values
                            ('{$data['msg_title']}','{$data['msg_content']}','{$data['status']}','{$data['add_time']}','{$data['update_time']}','{$data['uid']}','{$data['type']}')";
            $result = $this->getDbEntity()->insert_sql($sql);
            return $result;
        }
		//查询某一个邮件模板
		public function select_one_template($id){
			$sql    = "select *  from ly_email_template  where id='$id' ";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}
        //查询某一个短信模板
        public function select_onesms_template($id){
            $sql    = "select *  from ly_sms_template  where id='$id' ";
            $result = $this->getDbEntity()->query($sql);
            return $result[0];
        }
		//修改邮件模板
		public function update_email_msg($data,$id,$uid){
			$sql    = "update ly_email_template set msg_title='{$data['msg_title']}',msg_content='{$data['msg_content']}',
 						status = '{$data['status']}',update_time = '{$data['update_time']}'
 						where id='$id' and uid='$uid'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
        //修改短信模板
        public function update_smstm_msg($data,$id,$uid){
            $sql    = "update ly_sms_template set msg_title='{$data['msg_title']}',msg_content='{$data['msg_content']}',
                            status = '{$data['status']}',update_time = '{$data['update_time']}'
                            where id='$id' and uid='$uid'";
            $result = $this->getDbEntity()->execute_sql($sql);
            return $result;
        }
		//禁用邮件模板
		public function deleteEmailTemplate($status,$id,$uid){
			$sql    = "update ly_email_template set status='{$status}'
 						where id='$id' and uid='$uid'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}
        //禁用短信模板
        public function deleteSmsTemplate($status,$id,$uid){
            $sql    = "update ly_sms_template set status='{$status}'
                            where id='$id' and uid='$uid'";
            $result = $this->getDbEntity()->execute_sql($sql);
            return $result;
        }

		//查询所有用户分组
		public function select_user_group($where){
			$sql    = "select * from ly_user_group  $where order by position desc, id desc";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//查询所有用户分组
		public function select_sms_user_group($where){
			$sql    = "select * from ly_sms_user_group  $where order by position desc, id desc";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//添加用户分组
		public function add_user_group($data){
			$sql    = "insert into ly_user_group(uid,`name`,add_time,update_time)
					values('{$data['uid']}','{$data['name']}','{$data['add_time']}','{$data['update_time']}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		public function add_sms_user_group($data){
			$sql    = "insert into ly_sms_user_group(uid,`name`)
					values('{$data['uid']}','{$data['name']}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}

		//查询某个用户分组
		public function select_one_user_group($id,$uid){
			$sql    = "select * from ly_user_group where id='$id' and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		//查询多个用户分组
		public function select_batch_user_group($in_array,$uid){
			$sql    = "select * from ly_user_group where id in ($in_array) and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function select_batch_sms_group($in_array, $uid){
			$sql    = "select * from ly_sms_user_group where id in ($in_array) and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//判断客户分组
		public function select_group_byName($group_name,$uid){
			$sql    = "select * from ly_user_group where name='$group_name' and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		public function select_usergroup_byName($group_name,$uid){
			$sql    = "select * from ly_sms_user_group where name='$group_name' and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		public function select_usergroup_byId($id, $uid){
			$sql    = "select * from ly_sms_user_group where id='$id' and uid='$uid'";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}


		//修改用户分组
		public function update_user_group($data, $id,$uid){
			$sql    = "update ly_user_group set
							`name` = '{$data['name']}',
							`position` = '{$data['position']}',
							update_time	   = '{$data['update_time']}'
							where id='{$id}' and uid='$uid'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		public function update_sms_user_group($data, $id, $uid){
			$sql    = "update ly_sms_user_group set
							`name` = '{$data['name']}',
							`position` = '{$data['position']}'
							where id='{$id}' and uid='$uid'";
			$result = $this->getDbEntity()->execute_sql($sql);
			return $result;
		}

		//查询分组用户的数量
		public function select_usergroup_count($where){
			$sql    = "select count(*) as count from ly_customer $where";
			$result = $this->getDbEntity()->query($sql);
			return $result[0];
		}

		//查询用户分组里的所有用户
		public function select_usergroup_info($where,$limit){
			if(!empty($limit)){
				$sql    = "select * from ly_customer $where  order by add_time desc  limit $limit";
			}else{
				$sql    = "select * from ly_customer $where order by add_time desc";
			}

			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//删除用户分组
		public function deleteUserGroupMsg($where){
			$sql  = " delete from ly_user_group where $where";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		public function deleteSmsUserGroupMsg($where){
			$sql  = " delete from ly_sms_user_group where $where";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//查询退信用户是否存在
		public function select_ndr_user($where,$email){
			$sql  = " select email from ly_ndr_user where $where and email='$email'";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//查询所有退信用户
		public function selectAllNdrUser($uid){
			$sql = "select email from ly_ndr_user where uid=$uid";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}


		//插入退信用户
		public function insert_ndr_user($data){
			$sql    = "insert into ly_ndr_user(uid,email)
					values('{$data['uid']}','{$data['email']}')";
			$result = $this->getDbEntity()->insert_sql($sql);
			return $result;
		}
  
  		//查询临时表数据
		public function select_tmp_data(){
			$sql  = " select id from ly_im_data_tmp ";
			$result = $this->getDbEntity()->query($sql);
			return $result;
		}

		//插入待发送邮件参数数据
		public function add_email_data_queue($data){
		    $sql = 'INSERT INTO `ly_email_data_tmp` (`params_json`) VALUES ';
		    $params_json = '';
		    foreach($data as $value){
				$params_json = serialize($value);
		        $sql .= "('{$params_json}'),";
		    }
		    
		    $sql = trim($sql, ',').';';

		    $result = $this->getDbEntity()->insert_sql($sql);
		    return $result;
		}
        //插入待发送短信参数数据
        public function add_sms_data_queue($data){
            $sql = 'INSERT INTO `ly_sms_data_tmp` (`params_json`) VALUES ';
            foreach($data as $value){
                $params_json = serialize($value);
                $sql .= "('{$params_json}'),";
            }
            $sql = trim($sql, ',').';';
            $result = $this->getDbEntity()->insert_sql($sql);
            return $result;
        }
        //插入待发送短信参数数据--牛信云
        public function add_sms_data_queue_new($data){
            $sql = 'INSERT INTO `ly_sms_data_tmp_new` (`params_json`) VALUES ';
            foreach($data as $value){
                $params_json = serialize($value);
                $sql .= "('{$params_json}'),";
            }
            $sql = trim($sql, ',').';';
            $result = $this->getDbEntity()->insert_sql($sql);
            return $result;
        }
		//查询代发邮件数量
		public function selectEmailTmpData(){
		    $sql = 'SELECT COUNT(`id`) AS `ct` FROM `ly_email_data_tmp`;';
		    $result = $this->getDbEntity()->query($sql);
		    return $result;
		}

        //查询待发短信数量
        public function selectSmsTmpData(){
            $sql = 'SELECT COUNT(`id`) AS `ct` FROM `ly_sms_data_tmp`;';
            $result = $this->getDbEntity()->query($sql);
            return $result;
        }

        //查询美加州区号
        public function selectCityCode(){
            $sql    = "select code from ly_country_code ";
            $result = $this->getDbEntity()->query($sql);
            return $result;
        }
        //更改美加州区号用户状态
        public function upCodeSmsCustomer($is_send,$id){
            $sql    = "update ly_sms_customer set is_send='{$is_send}' where id='{$id}'";
            $result = $this->getDbEntity()->execute_sql($sql);
            return $result;
        }
	//end
}