MESProductionWeb/Central.Control.WebPage/Areas/SysManage/Controllers/SportsEventController.cs

using Ant.Data;
using Ant.ORM;
using Ant.SuperSocket.Common;
using Ant.Service.Common;
using Ant.Service.Common.Enums;
using Central.Control.Domain;
using MES.Production.Entity;
using MES.Production.Entity.Entity;
using MES.Production.Entity.Enum;
using Ant.Service.Mongodb;
using MES.Production.Service.IService;
using MES.Production.Service.ServiceImp;
using Ant.Service.Utility;
using ChangFa.Machinery.WebPage.App_Start;
using ChangFa.Machinery.WebPage.Areas.SysManage.Models;
using ChangFa.Machinery.WebPage.Controllers;
using MongoDB.Driver.Builders;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using static ChangFa.Machinery.WebPage.App_Start.ExportExcelHelper;
using JsonHelper = Ant.Service.Common.JsonHelper;

namespace ChangFa.Machinery.WebPage.Areas.SysManage.Controllers
{
    public class SportsEventController : BaseController
    {

        [UserAuthorizeAttribute(ModuleAlias = "SportsEventList", OperaAction = "View")]
        public ActionResult SportsEventList()
        {
            try
            {

                PageInfo pageInfo = new PageInfo(base.page, base.pagesize, 0, JsonConverter.JsonClass(new List<YW_SportsEvent>()));


                #region 处理查询参数
                var nameOrProject = Request.QueryString["nameOrProject"];
                #endregion

                #region 加载列表

                using (AntORM orm = new AntORM())
                {
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    RequestModel res = new RequestModel();
                    res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取参赛列表" };

                    var judgeList = orm.Queryable<YW_SportsEvent>();

                    int pageno = (base.page - 1) * base.pagesize;


                    if (!nameOrProject.IsEmpty())
                    {
                        judgeList = judgeList.Where(p => (!string.IsNullOrEmpty(p.Name) && p.Name.Contains(nameOrProject)));
                    }

                    var reslut = judgeList.OrderByDesc(p => p.CreateDate).Skip(pageno).Top(pagesize).ToList(res);

                    if (reslut.IsSuccess)
                    {
                        reslut.RecordNum = judgeList.Count();

                        pageInfo = new PageInfo(base.page, base.pagesize, reslut.RecordNum, JsonConverter.JsonClass(reslut.ResultModel));

                    }
                }

                ViewBag.Search = base.keywords;
                ViewData["nameOrProject"] = nameOrProject;

                return View(pageInfo);
                #endregion

            }
            catch (Exception e)
            {
                WriteLog(enumOperator.Select, "获取参赛列表：", e);
                throw e.InnerException;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [UserAuthorizeAttribute(ModuleAlias = "SportsEventList", OperaAction = "View")]
        public ActionResult EventScheduling()
        {
            try
            {

                PageInfo pageInfo = new PageInfo(base.page, base.pagesize, 0, JsonConverter.JsonClass(new List<YW_SportsEvent>()));


                #region 处理查询参数
                var nameOrProject = Request.QueryString["nameOrProject"];
                #endregion

                #region 加载列表

                using (AntORM orm = new AntORM())
                {
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    RequestModel res = new RequestModel();
                    res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取参赛列表" };

                    var judgeList = orm.Queryable<YW_SportsEvent>();

                    int pageno = (base.page - 1) * base.pagesize;


                    if (!nameOrProject.IsEmpty())
                    {
                        judgeList = judgeList.Where(p => (!string.IsNullOrEmpty(p.Name) && p.Name.Contains(nameOrProject)));
                    }
                    int state = SportsEventController.Status.报名.GetHashCode();
                    judgeList = judgeList.Where(p => p.State >= state);//展示可以报名数据
                    //judgeList = judgeList.Where(p => p.State > state);//展示可以报名数据
                    var reslut = judgeList.OrderByDesc(p => p.CreateDate).Skip(pageno).Top(pagesize).ToList(res);
                    if (reslut.IsSuccess)
                    {
                        reslut.RecordNum = judgeList.Count();

                        pageInfo = new PageInfo(base.page, base.pagesize, reslut.RecordNum, JsonConverter.JsonClass(reslut.ResultModel));

                    }
                }

                ViewBag.Search = base.keywords;
                ViewData["nameOrProject"] = nameOrProject;

                return View(pageInfo);
                #endregion

            }
            catch (Exception e)
            {
                WriteLog(enumOperator.Select, "获取参赛列表：", e);
                throw e.InnerException;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        [UserAuthorize(ModuleAlias = "SportsEventList", OperaAction = "View")]
        public ActionResult SelectSport(string sportsName, string sportsId, string id)
        {
            try
            {

                PageInfo pageInfo = new PageInfo(base.page, base.pagesize, 0, JsonConverter.JsonClass(new List<YM_RaceProject>()));
                List<SelectListItem> items = new List<SelectListItem>();
                items.Add(new SelectListItem { Text = "请选择", Value = "" });
                var sysCodes = CommonEnumHelper.LoadDic(CommonCodeType.运动分类);
                //foreach (var item in sysCodes)
                //{
                //    items.Add(new SelectListItem { Text = item.NAMETEXT, Value = item.NAMETEXT, Selected = Request.QueryString["SportType"] == item.NAMETEXT });
                //}
                ViewBag.SportTypes = sysCodes;


                #region 处理查询参数
                var nameOrProject = sportsName;
                #endregion

                #region 加载列表
                using (AntORM orm = new AntORM())
                {
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    RequestModel res = new RequestModel();
                    res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取比赛项目列表" };

                    var coachList = orm.Queryable<YM_RaceProject>();

                    int pageno = (base.page - 1) * base.pagesize;

                    if (!nameOrProject.IsEmpty())
                    {
                        coachList = coachList.Where(p => (!string.IsNullOrEmpty(p.SportType) && p.SportType.Contains(nameOrProject)));
                    }

                    var reslut = coachList.OrderByDesc(p => p.UpdateTime).ToList(res);//.Skip(pageno).Top(pagesize)

                    if (reslut.IsSuccess)
                    {
                        reslut.RecordNum = 0;

                        pageInfo = new PageInfo(base.page, base.pagesize, reslut.RecordNum, JsonConverter.JsonClass(reslut.ResultModel));

                    }
                }

                ViewData["sportsId"] = sportsId;
                ViewData["sportsName"] = sportsName;
                ViewData["SEPID"] = id;
                ViewBag.nameOrProject = nameOrProject;
                ViewBag.Search = base.keywords;

                return View(pageInfo);
                #endregion

            }
            catch (Exception e)
            {
                WriteLog(enumOperator.Select, "获取比赛项目列表：", e);
                throw e.InnerException;
            }
        }

        public ActionResult Delete(string idList)
        {
            JsonHelper json = new JsonHelper() { Status = "n", Msg = "删除赛事成功" };
            try
            {
                //是否为空
                if (string.IsNullOrEmpty(idList)) { json.Msg = "未找到要删除的赛事"; return Json(json); }
                string[] id = idList.Trim(',').Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    var userId = (id[i]);
                    using (AntORM orm = new AntORM())
                    {

                        orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                        RequestModel request = new RequestModel
                        {
                            newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "删除赛事" },
                            Oid = userId.ToString()
                        };
                        var reslut = orm.DeleteById<YW_SportsEvent>(request);
                        if (reslut.IsSuccess)
                        {
                            json.Status = "y";
                            WriteLog(enumOperator.Remove, json.Msg, enumLog4net.WARN);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                json.Msg = "删除赛事发生内部错误！";
                WriteLog(enumOperator.Remove, "删除赛事：", e);
            }
            return Json(json);
        }

        [UserAuthorize(ModuleAlias = "SportsEventList", OperaAction = "Add,Edit")]
        [ValidateInput(false)]
        [HttpPost]
        public ActionResult Save(YW_SportsEvent entity)
        {
            JsonHelper json = new JsonHelper() { Msg = "保存赛事成功", Status = "n", ReUrl = "/Sys/SportsEvent/SportsEventList" };
            try
            {
                using (AntORM orm = new AntORM())
                {
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    RequestModel request = new RequestModel();
                    request.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "保存赛事" };

                    var RaceProjectName = Request["RaceProjectName"];
                    entity.RaceProject = RaceProjectName;
                    if (entity.ID > 0)
                    {
                        request.Oid = entity.ID.ToString();
                        var reslut = orm.UpdateById(entity, request);
                        if (reslut.IsSuccess)
                        {
                            json.Status = "y";
                        }
                        else
                        {
                            json.Msg = "保存赛事失败";
                        }
                    }
                    else
                    {
                        entity.Createper = this.CurrentUser.Name;
                        entity.CreateDate = DateTime.Now;

                        var reslut = orm.Save(entity, request);
                        if (reslut.IsSuccess)
                        {
                            json.Status = "y";
                        }
                        else
                        {
                            json.Msg = "保存赛事失败";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                json.Msg = "保存赛事发生内部错误！";
                WriteLog(enumOperator.None, "保存赛事：", ex);
            }

            return Json(json);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [UserAuthorize(ModuleAlias = "SportsEventList", OperaAction = "View")]
        public ActionResult SetEntryConditions(int? id)
        {
            PageInfo pageInfo = new PageInfo(base.page, base.pagesize, 0, JsonConverter.JsonClass(new List<YW_EntryConditions>()));

            using (AntORM orm = new AntORM())
            {
                orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                RequestModel res = new RequestModel();
                res.newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "获取比赛级别条件列表" };

                var coachList = orm.Queryable<YW_EntryConditions>();

                int pageno = (base.page - 1) * base.pagesize;

                //if (!nameOrProject.IsEmpty())
                //{
                //    coachList = coachList.Where(p => (!string.IsNullOrEmpty(p.SportType) && p.SportType.Contains(nameOrProject)));
                //}
                if (id > 0)
                {
                    coachList = coachList.Where(p => p.SEPID == id);
                }

                var reslut = coachList.OrderByDesc(p => p.CreateDate).Skip(pageno).Top(pagesize).ToList(res);

                if (reslut.IsSuccess)
                {
                    reslut.RecordNum = coachList.Count();

                    pageInfo = new PageInfo(base.page, base.pagesize, reslut.RecordNum, JsonConverter.JsonClass(reslut.ResultModel));

                }
            }
            return View(pageInfo);
        }


        public FileResult GetQualificationsExecl(int id)
        {
            RaceInfo raceInfo = new RaceInfo(); //比赛信息
            List<RaceContactInfo> contactInfos = new List<RaceContactInfo>();//教练信息
            List<RaceSporterInfo> sporterInfos = new List<RaceSporterInfo>();//报名信息
            List<RaceStatisticsInfo> statisticsInfos = new List<RaceStatisticsInfo>();//报名统计
            //领队
            using (AntORM orm = new AntORM())
            {
                try
                {


                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    RequestModel request = new RequestModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "查询赛事的单个实体方法" },
                        Oid = id.ToString()
                    };
                    var result = orm.GetEntity<YW_SportsEvent>(request);
                    if (result.IsSuccess)
                    {
                        YW_SportsEvent mod = result.ResultModel;
                        raceInfo.RaceName = mod.Name;
                        raceInfo.RaceTime = mod.EventStartTime.ToDateTime();
                        raceInfo.Address = mod.EventAddress;
                    }
                    orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                    request = new RequestModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "查询赛事的单个实体方法" },

                    };

                    RequestItemSqlModel requeststrsql = new RequestItemSqlModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "导出报名信息" },
                    };
                    requeststrsql.StrSql = string.Format(@"SELECT a.*,b.Name as SchoolName,b.ID as SchoolID,b.SchoolSmallType from YW_ChiefOfficial as a LEFT JOIN YM_School as b on a.Schoolid=b.ID where a.SEPID={0}", id);
                    result = orm.GetDataTableList(requeststrsql);
                    if (result.IsSuccess)
                    {
                        foreach (DataRow dr in result.DataTable.Rows)
                        {
                            RaceContactInfo contactInfo = new RaceContactInfo();
                            contactInfo.Leader = dr["Leader"].ToString().Trim();
                            contactInfo.Coach1 = dr["Coach1"].ToString().Trim();
                            contactInfo.Coach2 = dr["Coach2"].ToString().Trim();
                            contactInfo.Coach3 = dr["Coach3"].ToString().Trim();
                            contactInfo.Coach4 = dr["Coach4"].ToString().Trim();
                            contactInfo.Contact = dr["Watchman"].ToString().Trim();


                            contactInfo.ContactPhone = dr["Contact"].ToString().Trim();
                            contactInfo.SchoolName = dr["SchoolName"].ToString().Trim();
                            contactInfo.SchoolID = dr["SchoolID"].ToInt32();
                            contactInfos.Add(contactInfo);
                        }
                    }
                    requeststrsql = new RequestItemSqlModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "导出报名信息" },
                    };
                    requeststrsql.StrSql = string.Format(@"SELECT a.SporterID,b.Name,b.Sex,b.StudentNo,b.RegNo,b.IDCard,b.Grade,d.Constituencies,c.SmallEventName,b.SchoolName,b.SchoolID,e.SchoolSmallType
                                                                FROM YW_MatchReport as a 
                                                                LEFT JOIN YM_Sporter as b on a.SporterID=b.ID 
                                                                LEFT JOIN  YW_ProjectEntry as c on a.PEID=c.ID 
                                                                LEFT JOIN YW_EntryConditions as d on a.ECID=d.ID
                                                                LEFT JOIN YM_School as e on a.Schoolid=e.ID
                                                                where a.SEPID={0} ", id);
                    result = orm.GetDataTableList(requeststrsql);

                    List<int> temp = new List<int>();
                    if (result.IsSuccess)
                    {
                        foreach (DataRow item in result.DataTable.Rows)
                        {
                            if (!temp.Contains(Convert.ToInt32(item["SporterID"].ToString())))
                            {
                                result.DataTable.DefaultView.RowFilter = string.Format("SporterID={0}", item["SporterID"].ToString());
                                var dt = result.DataTable.DefaultView.ToTable();
                                List<string> projects = new List<string>();
                                foreach (DataRow item2 in dt.Rows)
                                {
                                    projects.Add(item2["SmallEventName"].ToString().Trim());
                                }
                                while (projects.Count < 4)
                                {
                                    projects.Add(string.Empty);
                                }
                                RaceSporterInfo sporterInfo = new RaceSporterInfo();
                                sporterInfo.RegNo = item["RegNo"].ToString().Trim();
                                sporterInfo.SporterName = item["Name"].ToString().Trim();
                                sporterInfo.StudentNo = item["StudentNo"].ToString().Trim();
                                sporterInfo.Grade = item["Grade"].ToString().Trim();
                                sporterInfo.GroupName = item["Constituencies"].ToString().Trim();
                                sporterInfo.SchoolName = item["SchoolName"].ToString().Trim();
                                string strs = item["SchoolSmallType"].ToString(); int schooltype = 0;
                                if (strs == "小学")
                                {
                                    schooltype = 1;
                                }
                                if (strs == "初中")
                                {
                                    schooltype = 2;
                                }
                                if (strs == "高中")
                                {
                                    schooltype = 3;
                                }
                                sporterInfo.SchoolType = schooltype;
                                sporterInfo.SchoolID = item["SchoolID"].ToInt32();
                                sporterInfo.Sex = item["Sex"].ToString().Trim();
                                sporterInfo.IDCard = item["IDCard"].ToString().Trim();
                                sporterInfo.Projects = projects;
                                sporterInfos.Add(sporterInfo);
                                temp.Add(Convert.ToInt32(item["SporterID"].ToString()));
                            }
                        }
                    }

                    //string strsql = @"SELECT b.SchoolName,d.Constituencies,b.Sex,count(1) as num
                    //                FROM YW_MatchReport as a 
                    //                LEFT JOIN YM_Sporter as b on a.SporterID=b.ID 
                    //                LEFT JOIN  YW_ProjectEntry as c on a.PEID=c.ID 
                    //                LEFT JOIN YW_EntryConditions as d on a.ECID=d.ID
                    //                WHERE a.SEPID=" + id + @"
                    //                GROUP BY b.Sex,b.SchoolName,d.Constituencies
                    //                ORDER BY b.SchoolName,d.Constituencies";
                    string strsql = string.Format(@"SELECT a.LeaderNum,a.CoachNum,b.Name as SchoolName,a.Schoolid as SchoolID from YW_ChiefOfficial as a LEFT JOIN  YM_School as b on a.Schoolid=b.ID  WHERE a.SEPID={0}", id);
                    requeststrsql = new RequestItemSqlModel
                    {
                        newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "导出报名信息" },
                    };
                    requeststrsql.StrSql = strsql;
                    result = orm.GetDataTableList(requeststrsql);
                    if (result.IsSuccess)
                    {
                        foreach (DataRow dr in result.DataTable.Rows)
                        {
                            RaceStatisticsInfo raceStatisticsInfo = new RaceStatisticsInfo();
                            raceStatisticsInfo.LeaderCount = dr["LeaderNum"].ToInt32();
                            raceStatisticsInfo.SchoolName = dr["SchoolName"].ToString().Trim();
                            raceStatisticsInfo.SchoolID = dr["SchoolID"].ToInt32();
                            raceStatisticsInfo.CoachCount = dr["CoachNum"].ToInt32();
                            raceStatisticsInfo.SporterCount = 0;
                            raceStatisticsInfo.TotalCount = 0;
                            statisticsInfos.Add(raceStatisticsInfo);
                        }
                    }
                }
                catch (Exception e)
                {

                }


                var ms = ExportAllSchoolExcel(raceInfo, contactInfos, sporterInfos, statisticsInfos);

                return File(ms, "application/vnd.ms-excel", string.Format("{0}_资格审查报名表.xls", this.CurrentUser.Name));
            }
        }

        [UserAuthorize(ModuleAlias = "SportsEventList", OperaAction = "Detail")]
        public ActionResult Detail(int? id)
        {
            YW_SportsEvent mod = new YW_SportsEvent();

            if (id != null)
            {
                using (AntORM orm = new AntORM())
                {
                    try
                    {
                        orm.db = DataAccessFactory.CreateDataConnection("CyclingItem");
                        RequestModel request = new RequestModel
                        {
                            newSt = new SqlNote() { Author = "季健国", NewSt = new System.Diagnostics.StackTrace(true), SqlDesc = "查询赛事的单个实体方法" },
                            Oid = id.ToString()
                        };
                        var reslut = orm.GetEntity<YW_SportsEvent>(request);
                        if (reslut.IsSuccess)
                        {
                            mod = reslut.ResultModel;
                        }
                    }
                    catch (Exception e)
                    {

                    }
                }
            }



            List<SelectListItem> items = new List<SelectListItem>();
            items.Add(new SelectListItem { Text = "请选择", Value = "" });
            var sysCodes = CommonEnumHelper.LoadDic(CommonCodeType.比赛编码);
            foreach (var item in sysCodes)
            {
                items.Add(new SelectListItem { Text = item.NAMETEXT, Value = item.NAMETEXT });
            }
            ViewBag.MatchCodes = items;

            items = new List<SelectListItem>();
            items.Add(new SelectListItem { Text = "请选择", Value = "" });
            sysCodes = CommonEnumHelper.LoadDic(CommonCodeType.组队参赛);
            foreach (var item in sysCodes)
            {
                items.Add(new SelectListItem { Text = item.NAMETEXT, Value = item.NAMETEXT });
            }
            ViewBag.TeamUpCodes = items;


            items = new List<SelectListItem>();
            items.Add(new SelectListItem { Text = "请选择", Value = "" });
            sysCodes = CommonEnumHelper.LoadDic(CommonCodeType.运动分类);
            foreach (var item in sysCodes)
            {
                items.Add(new SelectListItem { Text = item.NAMETEXT, Value = item.ID.ToString() });
            }
            ViewBag.Projects = items;

            items = new List<SelectListItem>();
            items.Add(new SelectListItem { Text = "请选择", Value = "" });
            var enums = Tools.BindEnumsList(typeof(Status));
            foreach (var item in enums)
            {
                items.Add(new SelectListItem { Text = item.Name, Value = item.Value.ToString() });
            }
            ViewBag.States = items;

            return View(mod);
        }

        public enum Status
        {
            起草,
            公告,
            报名,
            审核,
            比赛,
            关闭
        }
    }
}