package edu.neusoft.project.model.dao;

import edu.neusoft.project.model.vo.AckTime;
import edu.neusoft.project.model.vo.Person;
import edu.neusoft.project.model.vo.ProvinceAdd;
import edu.neusoft.project.util.DBconn;
import edu.neusoft.project.util.MyMD5Util;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class PersonDao {
    private Connection conn;
    private PreparedStatement  pst=null;
    private ResultSet rs=null;
    private List<Person> list=new ArrayList<>();

    private MyMD5Util myMD5Util = new MyMD5Util();


    public Person selectByNameAndToken(String usernsme,String token) {
        String sql="SELECT * FROM userlist where token = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,token);
            rs=pst.executeQuery();
            while (rs.next()) {
                Person p=new Person();
                System.out.println(rs);
                System.out.println(rs.getString("uname"));
                System.out.println(rs.getString("pwd"));
                if(usernsme.equals(rs.getString("uname"))){
                    System.out.println();
                    p.setUsername(rs.getString("uname"));
                    p.setPassword(rs.getString("pwd"));
                    p.setPersonCode(rs.getString("personCode"));
                    p.setPersonListened(rs.getInt("personListened"));
                    p.setToken(rs.getString("token"));
                    return p;
                }
                return null;
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;
    }
    public Person selectByName(String username) {
        String sql0="SELECT * FROM userlist where uname = ? limit 1 ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql0);
            pst.setString(1,username);
//            rs=pst.executeQuery();
            rs=pst.executeQuery();
            while (rs.next()) {
                Person p=new Person();
                System.out.println(rs);
//                System.out.println(rs.getString("uname"));
//                System.out.println(rs.getString("pwd"));

                p.setUsername(rs.getString("uname"));
                p.setPassword(rs.getString("pwd"));
                p.setPersonCode(rs.getString("personCode"));
                p.setPersonListened(rs.getInt("personListened"));
                p.setToken(rs.getString("token"));
                    return p;


            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;


    }
 public Person selectByAdminName(String username) {
        String sql0="SELECT * FROM adminlist where uname = ? limit 1 ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql0);
            pst.setString(1,username);
//            rs=pst.executeQuery();
            rs=pst.executeQuery();
            while (rs.next()) {
                Person p=new Person();
                System.out.println(rs);
//                System.out.println(rs.getString("uname"));
//                System.out.println(rs.getString("pwd"));

                p.setUsername(rs.getString("uname"));
                p.setPassword(rs.getString("pwd"));
                p.setPersonCode(rs.getString("personCode"));
                p.setPersonListened(rs.getInt("personListened"));
                p.setToken(rs.getString("token"));
                    return p;


            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;


    }
    public Person selectByNameAndTokenAdmin(String usernsme,String token) {
        String sql="SELECT * FROM adminlist where token = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,token);
            rs=pst.executeQuery();
            while (rs.next()) {
                Person p=new Person();
//                System.out.println(rs);
                System.out.println(rs.getString("uname"));
                System.out.println(rs.getString("pwd"));



                if(usernsme.equals(rs.getString("uname"))){
                    p.setUsername(rs.getString("uname"));
                    p.setPassword(rs.getString("pwd"));
                    p.setPersonCode(rs.getString("personCode"));
                    p.setPersonListened(rs.getInt("personListened"));
                    p.setToken(rs.getString("token"));
                    return p;
                }
                return null;
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return null;
    }

//    public List<Person> SelectAllDept() {
//        // TODO Auto-generated method stub
//        List<Person> list= new ArrayList<Person>();
//        String sql="SELECT username,password FROM userlist ";
//        conn= DBconn.getConn();
//        try {
//            pst=conn.prepareStatement(sql);
//
//            rs=pst.executeQuery();
//            while (rs.next()) {
//                MeetingDept dept=new MeetingDept();
//                dept.setDeptID(rs.getInt("dept_id"));
//                dept.setDeptName(rs.getString("dept_name"));
//                list.add(dept);
//            }
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }finally {
//            DBconn.closeDB(rs, pst, conn);
//        }
//
//        return list;
//    }

    public void insert(Person person) {
//        list.add(person);
//        System.out.println(person);
        // TODO Auto-generated method stub
        String sql = "INSERT INTO userlist VALUES (?,?,?,?,?,?,?) ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, person.getPersonCode());
            pst.setString(2,person.getUsername());
            pst.setString(3,person.getPassword());
            pst.setInt(4,1);
            pst.setInt(5,0);
            pst.setString(6,"");
            pst.setString(7,"");
            pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }


    }

    public int buySong(String id, String personCode) {
        int ret=0;
        Date date = new Date();
        String sdate=(new SimpleDateFormat("yyyyMMdd"/*你想要的格式*/)).format(date);
        int random= (int) (Math.random()*10000);
        String orderId= sdate+String.valueOf(date.getTime())+String.valueOf(random);
        System.out.println(orderId);

        String sql = "INSERT INTO `music`.`indentlist`(`personCode`, `ordersID`, `playListId`, `id`, `price`, `ServStatus`) VALUES (?, ?, NULL, ?, 2.56, 1);";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, personCode);
            pst.setString(2,orderId);
            pst.setString(3,id);

            ret =  pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }

        return ret;

    }

    public int delThisIndent(String adminListPersonCode, String id) {

        int ret=0;
        String sql = "delete from `music`.`indentlist` where `personCode` = ? and `id` = ?  ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, adminListPersonCode);
            pst.setString(2,id);
            ret =  pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }

        return ret;
    }

    public int insAckTime(int t) {
        int ret = 0;
        String sql = "update asktime set num =num + 1 where id=? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setInt(1, t);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        return ret;
    }

    public List<AckTime> getAckTime() {
        List<AckTime> ackList = new ArrayList<>();
        String sql="SELECT * FROM asktime ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            rs=pst.executeQuery();
            while (rs.next()) {
                AckTime ack = new AckTime();
                ack.setId(rs.getInt("id"));
                ack.setAckTime(rs.getString("AskTime"));
                ack.setNum(rs.getInt("num"));
                ackList.add(ack);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return ackList;
    }

    //without finish
    public void insIpAdd(String cip) {
        int ret = 0;
        String sql = "INSERT INTO `music`.`ipaddress`(`IPaddress`, `num`) VALUES (?, 1)";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cip);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("CIP-insert-YES");
        }
    }

    public String getIpAdd(String cip) {
        String sql="select IPaddress from ipaddress where IPaddress = ? limit 1";
        String ip="";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,cip);
            rs=pst.executeQuery();
            while (rs.next()) {
                ip= rs.getString("IPaddress");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return ip;

    }

    public void updIpAdd(String cip) {
        int ret = 0;
        String sql = "update ipaddress set num =num + 1 where IPaddress=? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cip);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("CIP-update-YES");
        }
    }

    public String getProvince(String cid, String cname) {
        String province = "";
        String sql="select ProvinceName from Province where ProvinceName = ? and ProvinceId = ? limit 1";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,cname);
            pst.setString(2,cid);
            rs=pst.executeQuery();
            while (rs.next()) {
                province= rs.getString("ProvinceName");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }


        return province;
    }

    public void insProvince(String cid, String cname) {
        int ret = 0;
        String sql = "INSERT INTO `music`.`Province`(`ProvinceId`, `ProvinceName`, `num`) VALUES (?, ?, 1);\n";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cid);
            pst.setString(2, cname);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("Cid,Province-insert-YES");
        }
    }

    public void updProvince(String cid, String cname) {
        int ret = 0;
        String sql = "UPDATE `music`.`Province` set num =num + 1 WHERE `ProvinceId` = ? AND `ProvinceName` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cid);
            pst.setString(2, cname);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("Cid,Province-update-YES");
        }
    }

    public void insLineCount() {
        int ret = 0;
        String sql = "UPDATE `music`.`linecount` set num =num + 1 WHERE `linecount` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, "linecount");;
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("linecount+1");
        }
    }

    public void remLineCount() {
        int ret = 0;
        String sql = "UPDATE `music`.`linecount` set num =num - 1 WHERE `linecount` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, "linecount");;
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("linecount-1");
        }


    }

    public void resetLineCount() {
        int ret = 0;
        String sql = "UPDATE `music`.`linecount` set num = ? WHERE `linecount` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setInt(1,0);
            pst.setString(2, "linecount");;
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("linecount-1");
        }
    }

    public int readLineCount() {
        int ret = 0;
        String sql="SELECT `num` from `music`.`linecount` where `linecount` = `linecount` limit 1 ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            rs=pst.executeQuery();
            while (rs.next()) {
                ret= rs.getInt("num");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }

        return ret;
    }

    public String getUserSession(String uid) {
        String str32="";
        String sql="SELECT `session` from `music`.`userlist` where `personcode` = ? limit 1 ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,uid);
            rs=pst.executeQuery();
            while (rs.next()) {
                str32= rs.getString("session");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }

        return  str32;
    }

    public void resetUserSession(String uid) {
        int ret = 0;
        String sql = "UPDATE `music`.`userlist` set `session` = ? WHERE `personcode` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,"");
            pst.setString(2,uid);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("personDao.resetUserSession");
        }
    }

    public void updUserSession(Person loginedPer) {
        int ret = 0;
        String str = myMD5Util.encryptUserSession(loginedPer.getUsername(),loginedPer.getPassword());
        String sql = "UPDATE `music`.`userlist` set `session` = ? WHERE `personcode` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,str);
            pst.setString(2,loginedPer.getPersonCode());
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("personDao.updUserSession");
        }
    }

    public void updUserToken(String personCode, String userToken) {
        int ret = 0;

        String sql = "UPDATE `music`.`userlist` set `token` = ? WHERE `personcode` = ? ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,userToken);
            pst.setString(2,personCode);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("personDao.updUserToken");
        }

    }

    public String getUserTooken(String personCode) {
        String str32="";
        String sql="SELECT `token` from `music`.`userlist` where `personcode` = ? limit 1 ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,personCode);
            rs=pst.executeQuery();
            while (rs.next()) {
                str32= rs.getString("token");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }

        return  str32;
    }


    public String checkRegistUser(String username) {
        String sql0="SELECT * FROM userlist where uname = ? limit 1 ";
        String str="  ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql0);
            pst.setString(1,username);
//            rs=pst.executeQuery();
            rs=pst.executeQuery();
            if (rs.next()==false){
                str="yes";
            }
            while (rs.next()) {
                str = rs.getString("uname");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return str;
    }

    public int getTradingCount() {
        String sql0="select COUNT(*) as sum from indentlist ";
//        String str="  ";
        int ret = 0;
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql0);
            rs=pst.executeQuery();
            while (rs.next()) {
                ret = rs.getInt("sum");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }
        return ret;
    }

    public String getLikeProvinceAdd(String cname) {
        String ret = "";
        cname = cname.substring(0,2);
        String sql="select PName from ProvinceAdd where PName LIKE concat('%',?,'%') limit 1";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1,cname);
            rs=pst.executeQuery();
            while (rs.next()) {
                ret= rs.getString("PName");
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
//                e.printStackTrace();
            System.out.println(0000);
        }finally {
            DBconn.closeDB(rs, pst, conn);
        }

        return ret;
    }

    public void insLikeProvinceAdd(String cname) {
        int ret = 0;
        String sql;

        if (cname.contains("市")){
            //PC aimed to City
            sql = "INSERT INTO `music`.`ProvinceAdd`(`PName`, `num`,`pc`,`phone`) VALUES (?, 1, 1, 0);\n";
        }else{
            //Phone aimed to Province
            sql = "INSERT INTO `music`.`ProvinceAdd`(`PName`, `num`,`pc`,`phone`) VALUES (?, 1, 0, 1);\n";
        }
        cname = cname.substring(0,2);
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cname);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            System.out.println();
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("Province PC/Phone-upd-YES");
        }
    }

    public void updLikeProvinceAdd(String cname) {
        int ret = 0;
        String sql ;
        if (cname.contains("市")) {
            sql = "UPDATE `music`.`ProvinceAdd` set num =num + 1,pc= pc+1 WHERE `PName` = ? ";
        }else {
            sql = "UPDATE `music`.`ProvinceAdd` set num =num + 1,phone= phone+1 WHERE `PName` = ? ";
        }
        cname = cname.substring(0,2);
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            pst.setString(1, cname);
            ret = pst.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }
        if (ret==1){
            System.out.println("ProvinceAdd-update-YES");
        }
    }

    public List<ProvinceAdd> getProvinceAddEcharts() {
        List<ProvinceAdd> plist = new ArrayList<>();
        String sql = "select * from `music`.`ProvinceAdd` ";
        conn= DBconn.getConn();
        try {
            pst=conn.prepareStatement(sql);
            rs=pst.executeQuery();
            while (rs.next()) {
                ProvinceAdd p = new ProvinceAdd();
                p.setPName(rs.getString("PName"));
                p.setNum(rs.getInt("num"));
                p.setPc(rs.getInt("pc"));
                p.setPhone(rs.getInt("phone"));
                plist.add(p);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println();
        }finally {
            DBconn.closeDB(null, pst, conn);
        }



        return  plist;
    }
}

