using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Quiz.Models;
using Quiz.Utility;

namespace Quiz.DAL
{
    public class User
    {
        /// <summary>
        /// 判断该手机号码有没有答过题
        /// </summary>
        /// <param name="iphone"></param>
        /// <returns></returns>
        public static int IsAnswer(string iphone)
        {
            string sql = string.Format(@"select count(*) from UserAnswer where PhoneName='{0}'", iphone);
            var nc = DBF.dbCore.GetDataRow(System.Data.CommandType.Text, sql);
            if (nc != null) return Convert.ToInt32(nc[0]);
            else return 0;
        }

        /// <summary>
        /// 获取所有的用户信息
        /// </summary>
        /// <returns></returns>
        public static List<Models.User> GetUsers()
        {
            string sql = "select * from [User] where State=1 Order By OrderId";
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql);
            if (ds == null) return null;
            return ModelConstructor<Models.User>.CreateModelList(ds.Tables[0]);
        }

        /// <summary>
        /// 插入该用户答题
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool AddUserAnswer(Models.UserAnswer model)
        {
            int id;
            return TableMappings.UserAnswer.Insert(model, out id, "id");
        }

        public static List<Models.Topic> GetTopics()
        {
            string sql = "select * from Topic order by ID";
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql);
            if (ds == null) return null;
            return ModelConstructor<Models.Topic>.CreateModelList(ds.Tables[0]);
        }

        public static List<Models.School> GetSchools()
        {
            string sql = "select * from [School]";
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql);
            if (ds == null) return null;
            return ModelConstructor<Models.School>.CreateModelList(ds.Tables[0]);
        }

        public static UserAnswer GetUserAnswerOne(string phone)
        {
            string sql = string.Format("select * from userAnswer where phoneName='{0}'", phone);
            var nc = DBF.dbCore.GetDataRow(CommandType.Text, sql);
            if (nc == null)
                return null;
            return nc.CreateModel(typeof(UserAnswer)) as UserAnswer;
        }


        public static bool ExamineSendMsg(string code, string mobile) {
            StringBuilder str = new StringBuilder();
            str.AppendFormat(@" insert into ems_db.dbo.ems_mt_task(msg_content,to_mobile,channel_id,ext,Create_time,send_time, priority,reserve_01, reserve_02)
			values ('【满意度调查】'+'{1}', '{0}', 'xyc_yd_01','',getdate(),getdate(),1,'','xst')",mobile,code);
            var nc = DBF.dbDore.NonQuery(System.Data.CommandType.Text, str.ToString());
            return nc > 0;
        }

        //数据解析
        public static List<UserAnswer> GetAnswers()
        {
            StringBuilder strSql=new StringBuilder();
            strSql.Append("select * from userAnswer   order by className");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, strSql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.UserAnswer>.CreateModelList(ds.Tables[0]);
        }


      

        public static List<UserAnswer> GetAnswerskq()
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from userAnswer ");
            //  var ds = DBF.dbCoreKq.GetDataSet(CommandType.Text, strSql.ToString());
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, strSql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.UserAnswer>.CreateModelList(ds.Tables[0]);
        }

        public static List<Models.Topic> GetTopicskq()
        {
            string sql = "select * from Topic order by ID";
            //  var ds = DBF.dbCoreKq.GetDataSet(CommandType.Text, sql);
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.Topic>.CreateModelList(ds.Tables[0]);
        }
        public static List<Models.User> GetUserskq()
        {
            string sql = "select * from [User] where State=1 Order By OrderId";
            //var ds = DBF.dbCoreKq.GetDataSet(CommandType.Text, sql);

            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.User>.CreateModelList(ds.Tables[0]);
        }
        public static List<Models.Topic> GetTopicsyc()
        {
            string sql = "select * from Topic order by ID";
            //  var ds = DBF.dbCoreYc.GetDataSet(CommandType.Text, sql);
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.Topic>.CreateModelList(ds.Tables[0]);
        }
        public static List<UserAnswer> GetAnswersyc()
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from userAnswer  ");
            //var ds = DBF.dbCoreYc.GetDataSet(CommandType.Text, strSql.ToString());
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, strSql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.UserAnswer>.CreateModelList(ds.Tables[0]);
        }

        public static List<UserAnswer> getschoolMB()
        {
            string sql = "  select distinct  schoolType ,schoolName  from [yuechengDB].[dbo].[yuechengXZsj]";
            //var ds = DBF.dbCoreYc.GetDataSet(CommandType.Text, sql);
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.UserAnswer>.CreateModelList(ds.Tables[0]);

        }


        public static List<Models.User> GetUsersyc()
        {
            string sql = "select * from [User] where State=1 Order By OrderId";
            //var ds = DBF.dbCoreYc.GetDataSet(CommandType.Text, sql);
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.User>.CreateModelList(ds.Tables[0]);
        }
        public static List<Models.User> GetUsersycTwo()
        {
            string sql = " select distinct  region ,schoolName  from [yuechengDB].[dbo].[User]";
            //var ds = DBF.dbCoreYc.GetDataSet(CommandType.Text, sql);
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, sql.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.User>.CreateModelList(ds.Tables[0]);
        }
        public static List<schoolTypeM> GetSchoolList()
        {
            //CAST(COUNT(schoolType) as decimal) 
            StringBuilder str = new StringBuilder();
            str.AppendFormat(@" select CAST(COUNT(schoolType) as decimal)  schoolCount,schoolType from [dbo].[User]  group by schoolType");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, str.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.schoolTypeM>.CreateModelList(ds.Tables[0]);

        }

        public static List<schoolTypeM> getschoolcountT()
        {
            StringBuilder str = new StringBuilder();

            str.AppendFormat(@" with  cr  as( select  distinct  SchoolName,schoolType  from  [dbo].[User]) select  schoolType,count(schoolType) as schoolcount from cr  group by schoolType");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, str.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.schoolTypeM>.CreateModelList(ds.Tables[0]);


        }

        public static List<schoolRecovery> GetSchoolNameList()
        {
            //CAST(COUNT(schoolType) as decimal) 
            StringBuilder str = new StringBuilder();
            str.AppendFormat(@" select SchoolName, CAST(COUNT(schoolType) as decimal)  schoolCount,schoolType from [dbo].[User]  group by SchoolName,schoolType");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, str.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.schoolRecovery>.CreateModelList(ds.Tables[0]);

        }



        public static List<schoolRecovery> getschoolRecoveryList()
        {
            //CAST(COUNT(SchoolName) as decimal) 
            StringBuilder str = new StringBuilder();
            str.AppendFormat(@" select SchoolName, CONVERT(varchar(100), Intime, 23) as Intime,CAST(COUNT(SchoolName) as decimal)  as RecoveryCount,schoolType from userAnswer  group by SchoolName,CONVERT(varchar(100), Intime, 23),schoolType");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, str.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.schoolRecovery>.CreateModelList(ds.Tables[0]);

        }


        public static List<schoolTypeM> getschooltypeRecoveryList()
        {
            StringBuilder str = new StringBuilder();
            str.AppendFormat(@"select CAST(COUNT(SchoolName) as decimal)  as schoolCount,schoolType from userAnswer  group by schoolType");
            var ds = DBF.dbCore.GetDataSet(CommandType.Text, str.ToString());
            if (ds == null) return null;
            return ModelConstructor<Models.schoolTypeM>.CreateModelList(ds.Tables[0]);

        }

        public static UserAnswer getIsKanuser(string phone)
        {

            StringBuilder str = new StringBuilder();
            str.AppendFormat(@"  select phone as PhoneName, name as StudentName, schoolname as SchoolName  from[luyou].[dbo].[SjKanban] where phone='{0}' and state=1", phone);
            var nc = DBF.dbCore.GetDataRow(CommandType.Text, str.ToString());
            if (nc == null) return null;
            return nc.CreateModel(typeof(UserAnswer)) as UserAnswer;
        }
    }
}