import datetime
import time

from common.utils.models import BaseConnection


class StatisticService(object):
    def __init__(self):
        self.conn = BaseConnection()

    def get_customer_clue_number(self, **kwargs):
        """
        数据统计--销售漏斗--获取客户线索数量
        :param kwargs: user_id：销售id
                    type：日期搜索类型，value枚举值：year--今年，month--当月，week--本周，day--今天
                    start_time：时间区间搜索值--区间开始
                    end_time：时间区间搜索值--区间结束
        :return:
        """
        sql = """
        select count(distinct a.id) count from crm_customer_clue a left join crm_customer b on a.crm_customer_id=b.id where 1=1
        """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(a.created_time)= {} ".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(a.created_time)={} and month(a.created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and a.created_time>'{}'".format(week)
            else:
                sql += " and date_format(a.created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and a.created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and a.created_time<='{}'".format(kwargs.get("end_time"))
        if kwargs.get("user_id"):
            sql += " and b.salesman_id={}".format(kwargs.get("user_id"))
        data = self.conn.query(sql)
        return data[0].get("count") if data else 0

    def get_visit_customer_number(self, **kwargs):
        """
                数据统计--销售漏斗--获取拜访客户数量
                :param kwargs: user_id：销售id
                            type：日期搜索类型，value枚举值：year--今年，month--当月，week--本周，day--今天
                            start_time：时间区间搜索值--区间开始
                            end_time：时间区间搜索值--区间结束
                :return:
                """
        sql = """
                select count(distinct a.id) count from  crm_customer a left join crm_plan b on a.id=b.customer_id where 
                1=1
                """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(b.start_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(b.start_time)={} and month(b.start_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and b.start_time>'{}'".format(week)
            else:
                sql += " and date_format(b.start_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and b.start_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and b.start_time<='{}'".format(kwargs.get("end_time"))
        if kwargs.get("user_id"):
            sql += " and a.salesman_id={}".format(kwargs.get("user_id"))
        data = self.conn.query(sql)
        return data[0].get("count") if data else 0

    def get_scheme_number(self, **kwargs):
        """
                数据统计--销售漏斗--获取提交方案客户数量
                :param kwargs: user_id：销售id
                            type：日期搜索类型，value枚举值：year--今年，month--当月，week--本周，day--今天
                            start_time：时间区间搜索值--区间开始
                            end_time：时间区间搜索值--区间结束
                :return:
                """
        sql = """
                select count(distinct customer_id) count from  crm_task  where code_id=2
                """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(created_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(created_time)={} and month(created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and created_time>'{}'".format(week)
            else:
                sql += " and date_format(created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and created_time<='{}'".format(kwargs.get("end_time"))
        if kwargs.get("user_id"):
            sql += " and submitter_id={}".format(kwargs.get("user_id"))
        data = self.conn.query(sql)
        return data[0].get("count") if data else 0

    def get_contract_number(self, **kwargs):
        """
                数据统计--销售漏斗--获取签订合同客户数量
                :param kwargs: user_id：销售id
                            type：日期搜索类型，value枚举值：year--今年，month--当月，week--本周，day--今天
                            start_time：时间区间搜索值--区间开始
                            end_time：时间区间搜索值--区间结束
                :return:
                """
        sql = """
                select count(distinct customer_id) count from  crm_task  where code_id=3
                """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(created_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(created_time)={} and month(created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and created_time>'{}'".format(week)
            else:
                sql += " and date_format(created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and created_time<='{}'".format(kwargs.get("end_time"))
        if kwargs.get("user_id"):
            sql += " and submitter_id={}".format(kwargs.get("user_id"))
        data = self.conn.query(sql)
        return data[0].get("count") if data else 0

    def person_task_statictis(self, user_id):
        sql = """
        select count(id) count from crm_customer where salesman_id={}
        """.format(user_id)
        total_count = self.conn.query(sql)

        sql = """
        select count(distinct  a.id) count from crm_customer a left join crm_plan b on a.id=b.customer_id where a.salesman_id={}
        and b.status="completed"
        """.format(user_id)

        finish_count = self.conn.query(sql)
        sql = """
        select count(id) count from crm_customer where intention=1 and salesman_id={}
        """.format(user_id)
        intention_count = self.conn.query(sql)
        return {
            "total_count": total_count[0].get("count") if total_count else 0,
            "finished_count": finish_count[0].get("count") if finish_count else 0,
            "customer_count": total_count[0].get("count") if total_count else 0,
            "intention_count": intention_count[0].get("count") if intention_count else 0
        }

    def person_achievement(self, user_id, year_month):
        sql = """
            select sum(ifnull(c.amount,0)) amount from crm_plan a left join  crm_customer b on a.customer_id=b.id 
            left join contract c on a.contract_id=c.id where b.salesman_id={} and date_format(a.end_time,"%Y-%m")="{}"
        """.format(user_id, year_month)
        amount = self.conn.query(sql)

        sql = """
        select count(distinct a.id) count from crm_plan a left join crm_customer b on a.customer_id=b.id
         where a.status="completed" and b.salesman_id={} and date_format(a.end_time,"%Y-%m")="{}"
        """.format(user_id, year_month)

        plan_count = self.conn.query(sql)

        sql = """
                select count(distinct b.id) count from crm_plan a left join crm_customer b on a.customer_id=b.id
                 where a.status="completed" and b.salesman_id={} and date_format(a.end_time,"%Y-%m")="{}"
                """.format(user_id, year_month)
        customer_count = self.conn.query(sql)

        return {
            "amount": amount[0].get("amount") if amount else 0,
            "plan_count": plan_count[0].get("count") if plan_count else 0,
            "customer": customer_count[0].get("count") if customer_count else 0
        }

    def get_sign_contract_status(self, **kwargs):
        """
        签约客户转化趋势
        :return:
        """

        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql = """
                        SELECT ifnull(count(DISTINCT customer_id),0) count, DATE_FORMAT(created_time,"%Y-%m") `date`
                         from crm_task where code_id=3  and year(created_time)={} 
                        """.format(year)
            elif kwargs.get("type") == "month":
                sql = """
                        SELECT ifnull(count(DISTINCT customer_id),0) count, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                         from crm_task where code_id=3  and date_format(created_time,"%Y-%m")="{}"
                        """.format(year, str(month).zfill(2))
            elif kwargs.get("type") == "week":
                sql = """
                        SELECT ifnull(count(DISTINCT customer_id),0) count, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3  and created_time>="{}"
                        """.format(week)
            else:
                sql = """
                        SELECT ifnull(count(DISTINCT customer_id),0) count, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3  and date_format(created_time,"%Y-%m-%d")="{}"
                        """.format(now.date())
        else:
            sql = """
                        SELECT ifnull(count(DISTINCT customer_id),0) count, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3 """
            if kwargs.get("start_time"):
                sql += """
                          and date_format(created_time,"%Y-%m-%d")>="{}"
                        """.format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += """
                         and date_format(created_time,"%Y-%m-%d")<="{}"
                                        """.format(kwargs.get("end_time"))

        data = self.conn.query(sql + " GROUP BY `date`")
        return {
            "date": [item.get("date") for item in data],
            "data": [item.get("count") for item in data]
        } if data else {
            "date": [],
            "data": []
        }

    def get_person_ranking(self, **kwargs):
        sql = """
        select IFNULL(count(distinct a.customer_id),0) count,b.nickname from crm_task a 
        left join users b on a.submitter_id=b.id 
        where code_id=3 
        """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(a.created_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(a.created_time)={} and month(a.created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and a.created_time>'{}'".format(week)
            else:
                sql += " and date_format(a.created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and a.created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and a.created_time<='{}'".format(kwargs.get("end_time"))
        data = self.conn.query(sql + " group by a.submitter_id order by count desc")
        return data

    def get_rank(self, user_id):
        now = datetime.datetime.now()
        year = now.year
        month = now.month
        sql = """
        select IFNULL(count(distinct a.customer_id),0) count,b.id from crm_task a 
        left join users b on a.submitter_id=b.id 
        where code_id=3 and date_format(a.created_time,"%Y-%m")="{}" group by a.submitter_id
        order by count desc
        """.format("{}-{}".format(year, str(month).zfill(2)))
        data = self.conn.query(sql)
        i = 0
        for item in data:
            i += 1
            if item.get("id") == user_id:
                break
        else:
            i = None
        return i

    def get_contract_amount(self):
        """
        获取合同金额以及月增长量
        :return:
        """
        now = datetime.datetime.now()
        year = now.year
        month = now.month
        last_month = month - 1
        last_year = year
        if last_month <= 0:
            last_year -= 1
            last_month = 12
        sql = """
        select ifnull(sum(ifnull(amount,0)),0) amount from crm_task where code_id=3
        """
        amount = self.conn.query(sql)

        sql = """
        select ifnull(sum(ifnull(amount,0)),0) amount from crm_task where code_id=3 and date_format(created_time,"%Y-%m")="{}"
        """.format("[]-{}".format(year, str(month).zfill(2)))
        this_month_amount = self.conn.query(sql)
        this_month_amount = this_month_amount[0].get("amount") if this_month_amount else 0

        sql = """
                select ifnull(sum(ifnull(amount,0)),0) amount from crm_task where code_id=3 and date_format(created_time,"%Y-%m")="{}"
                """.format("[]-{}".format(last_year, str(last_month).zfill(2)))
        last_month_amount = self.conn.query(sql)
        last_month_amount = last_month_amount[0].get("amount") if last_month_amount else 0

        return {
            "amount": amount[0].get("amount") if amount else 0,
            "month_increase_amount": this_month_amount - last_month_amount,
            "month_compare": (this_month_amount - last_month_amount) / last_month_amount if last_month_amount else 0
        }

    def get_contract_status(self):
        """
        获取合同数量，以及月增长量
        :return:
        """
        now = datetime.datetime.now()
        year = now.year
        month = now.month
        last_month = month - 1
        last_year = year
        if last_month <= 0:
            last_year -= 1
            last_month = 12
        sql = """
        select ifnull(count(id),0) amount from crm_task where code_id=3
        """
        amount = self.conn.query(sql)

        sql = """
        select ifnull(count(id),0) amount from crm_task where code_id=3 and date_format(created_time,"%Y-%m")="{}"
        """.format("[]-{}".format(year, str(month).zfill(2)))
        this_month_amount = self.conn.query(sql)
        this_month_amount = this_month_amount[0].get("amount") if this_month_amount else 0

        sql = """
                select ifnull(count(id),0) amount from crm_task where code_id=3 and date_format(created_time,"%Y-%m")="{}"
                """.format("[]-{}".format(last_year, str(last_month).zfill(2)))
        last_month_amount = self.conn.query(sql)
        last_month_amount = last_month_amount[0].get("amount") if last_month_amount else 0

        return {
            "number": amount[0].get("amount") if amount else 0,
            "month_increase_number": this_month_amount - last_month_amount,
            "month_compare": (this_month_amount - last_month_amount) / last_month_amount if last_month_amount else 0
        }

    def amount_statistic(self, **kwargs):
        """
        销售额趋势统计
        :param kwargs:
        :return:
        """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql = """
                        SELECT sum(ifnull(amount,0)) amount, DATE_FORMAT(created_time,"%Y-%m") `date`
                         from crm_task where code_id=3  and year(created_time)={} 
                        """.format(year)
            elif kwargs.get("type") == "month":
                sql = """
                        SELECT sum(ifnull(amount,0)) amount, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                         from crm_task where code_id=3  and date_format(created_time,"%Y-%m")="{}"
                        """.format(year, str(month).zfill(2))
            elif kwargs.get("type") == "week":
                sql = """
                        SELECT sum(ifnull(amount,0)) amount, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3  and created_time>="{}"
                        """.format(week)
            else:
                sql = """
                        SELECT sum(ifnull(amount,0)) amount, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3  and date_format(created_time,"%Y-%m-%d")="{}"
                        """.format(now.date())
        else:
            sql = """
                        SELECT sum(ifnull(amount,0)) amount, DATE_FORMAT(created_time,"%Y-%m-%d") `date`
                        from crm_task where code_id=3 """
            if kwargs.get("start_time"):
                sql += """
                          and date_format(created_time,"%Y-%m-%d")>="{}"
                        """.format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += """
                         and date_format(created_time,"%Y-%m-%d")<="{}"
                                        """.format(kwargs.get("end_time"))

        data = self.conn.query(sql + " GROUP BY `date`")
        return {
            "date": [item.get("date") for item in data],
            "data": [item.get("count") for item in data]
        } if data else {
            "date": [],
            "data": []
        }

    def person_amount_rank(self, **kwargs):
        """
        个人销售额排名
        :param kwargs:
        :return:
        """

        sql = """
        select sum(ifnull(a.amount,0)) amount,b.nickname from crm_task a left join users b on a.submitter_id=b.id
        where a.code_id=3
        """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(a.created_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(a.created_time)={} and month(a.created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and a.created_time>'{}'".format(week)
            else:
                sql += " and date_format(a.created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and a.created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and a.created_time<='{}'".format(kwargs.get("end_time"))
        data = self.conn.query(sql + " group by b.id order by amount desc")
        return data

    def area_amount(self):
        """
        地区销售额统计
        :return:
        """

        sql = """
        select sum(ifnull(a.amount,0)) amount,b.adcode from crm_task a left join crm_customer_clue b
         on a.customer_id=b.crm_customer_id where a.code_id=3 group by b.adcode
        """
        data = self.conn.query(sql)
        return data

    def industry_amount(self):
        """
        行业销售额统计
        :return:
        """

        sql = """
                select sum(ifnull(a.amount,0)) amount,c.industry from crm_task a left join crm_customer_clue b
                 on a.customer_id=b.crm_customer_id 
                 left join enterprise c on b.enterprise_id=c.id
                 where a.code_id=3 group by c.industry
                """
        data = self.conn.query(sql)
        return data

    def clue_number_statistic(self):
        """
        销售线索数量统计
        :return:
        """
        sql = """
        select count(id) count from crm_customer_clue
        """
        clue_count = self.conn.query(sql)
        clue_count = clue_count[0].get("count") if clue_count else 0
        sql = """
        select count(distinct customer_id) count from crm_task where code_id=3
        """
        contract_count = self.conn.query(sql)
        contract_count = contract_count[0].get("count") if contract_count else 0
        return {
            "count": clue_count,
            "rate": contract_count / clue_count if clue_count else 0
        }

    def clue_statistic(self, **kwargs):
        """
        客户线索统计
        :param kwargs:
        :return:
        """

        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql = """
                select count(id) count, date_format(created_time,"%Y-%m") date from crm_customer_clue
                where year(created_time)={} group by date
                """.format(year)
            elif kwargs.get("type") == "month":
                sql = """
                select count(id) count, date_format(created_time,"%Y-%m-%d") date from crm_customer_clue
                where date_format(created_time,"%Y-%m")="{}" group by date
                """.format("{}-{}".format(year, str(month).zfill(2)))
            elif kwargs.get("type") == "week":
                sql = """
                        select count(id) count, date_format(created_time,"%Y-%m-%d") date from crm_customer_clue
                        where created_time>="{}" group by date
                      """.format(week)
            else:
                sql = """
                        select count(id) count, date_format(created_time,"%Y-%m-%d") date from crm_customer_clue
                        where date_format(created_time,"%Y-%m-%d")="{}" group by date
                        """.format(week)
        else:
            sql = """
            select count(id) count, date_format(created_time,"%Y-%m-%d") date from crm_customer_clue
                        where 1=1
            """
            if kwargs.get("start_time"):
                sql += " and created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and created_time<='{}'".format(kwargs.get("end_time"))
            sql += " group by date"
        data = self.conn.query(sql)
        return data

    def person_clue_rank(self, **kwargs):
        """
        个人新增线索排名
        :param kwargs:
        :return:
        """
        sql = """
        select count(a.id) count,b.nickname from crm_customer_clue a left join users b on a.salesman_id=b.id
        where 1=1 
        """
        if kwargs.get("type"):
            now = datetime.datetime.now()
            year = now.year
            month = now.month
            week = (now - datetime.timedelta(now.weekday())).date()
            if kwargs.get("type") == "year":
                sql += " and year(a.created_time)={}".format(year)
            elif kwargs.get("type") == "month":
                sql += " and year(a.created_time)={} and month(a.created_time)={}".format(year, month)
            elif kwargs.get("type") == "week":
                sql += " and a.created_time>'{}'".format(week)
            else:
                sql += " and date_format(a.created_time,'%Y-%m-%d')='{}'".format(now.date())
        else:
            if kwargs.get("start_time"):
                sql += " and a.created_time>='{}'".format(kwargs.get("start_time"))
            if kwargs.get("end_time"):
                sql += " and a.created_time<='{}'".format(kwargs.get("end_time"))
        data = self.conn.query(sql + " group by a.salesman_id order by count desc")
        return data
