using dccdc.DAL;
using dccdc.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using ZWL.Common;
using dccdc.BLL;
using FastReport.Data;
using System.Data;
using dccdc.Models.DTO;
using static dccdc.Controllers.DictionariesController;
using AutoMapper;
using System.IO;

namespace dccdc.Controllers
{
    public class QueryStatisticsController : Controller
    {
        List<Droplist> kD = new List<Droplist>() { new Droplist { Value = "1", Title = "老库" }, new Droplist { Value = "2", Title = "应急仓库" } };
        public ActionResult Index()
        {
            return View();
        }
        #region 小组合格量统计
        public ActionResult XZHGLQuery()
        {
            //体检小组表
            ViewData["GroupList"] = new SelectList(new ExamGroupMaintainBll().GetAllList(""), "id", "team_name");
            return View();
        }

        public JsonResult getXZHGL(string dateStr, string dateEnd, string exam_group_maintain_id)
        {
            return Json(new { Rows = new BLL.QueryStatisticsBll().getXZHGL(dateStr, dateEnd, exam_group_maintain_id) });
        }
        #endregion

        #region 体检档案查询
        //登记类别对象
        public class DengjiList
        {
            public string Value { get; set; }
            public string Title { get; set; }
        }
        //体检方式对象
        public class TijianfsList
        {
            public string Value { get; set; }
            public string Title { get; set; }
        }
        public ActionResult TJDAQuery()
        {
            //体检小组表
            //ViewData["GroupList"] = new SelectList(new ExamGroupMaintainBll().GetAllList(""), "id", "team_name");

            //登记类别
            var djList = new List<DengjiList>();
            var djModel1 = new DengjiList
            {
                Value = "正常登记",
                Title = "正常登记"
            };
            var djModel2 = new DengjiList
            {
                Value = "复检登记",
                Title = "复检登记"
            };
            var djModel3 = new DengjiList
            {
                Value = "外来登记",
                Title = "外来登记"
            };
            var djModel4 = new DengjiList
            {
                Value = "外出登记",
                Title = "外出登记"
            };
            djList.Add(djModel1);
            djList.Add(djModel2);
            djList.Add(djModel3);
            djList.Add(djModel4);

            //体检方式
            var tjfsList = new List<TijianfsList>();
            var tjfsModel1 = new TijianfsList
            {
                Value = "个人体检",
                Title = "个人体检"
            };
            var tjfsModel2 = new TijianfsList
            {
                Value = "集体体检",
                Title = "集体体检"
            };
            tjfsList.Add(tjfsModel1);
            tjfsList.Add(tjfsModel2);

            ViewBag.djlb = new SelectList(djList, "Value", "Title");
            ViewBag.tjfs = new SelectList(tjfsList, "Value", "Title");
            ViewBag.hylb = new SelectList(new TradeTypeMaintainBll().GetAllTreeList(), "id", "trade_name");
            ViewBag.hjlb = new SelectList(new NativeInfoMaintainBll().GetAllList(), "id", "natives");
            ViewBag.ssqu = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");

            return View();
        }

        public JsonResult getHGList(Models.DTO.zydytj fc)
        {
            var list = new BLL.QueryStatisticsBll().getTjdaList(fc);
            return Json(new { Rows = list });
        }

        public JsonResult getQYFABySiteId(string id)
        {
            OccupationExamSchemeMaintainBll bll = new OccupationExamSchemeMaintainBll();
            return Json(bll.GetSiteList(id));
        }

        public JsonResult getHylb(string id)
        {
            return Json(new { Rows = new TradeTypeMaintainBll().GetAllList(id) });
        }
        #endregion

        #region 企业体检情况查询
        public class cols
        {
            public string name { get; set; }
            public string display { get; set; }
            public int width { get; set; }
            public Dictionary<string, string> totalSummary { get; set; }
            public List<cols> columns { get; set; }
            public string align { get; set; }
        }

        public ActionResult QYTJQKQuery()
        {  //登记类别
            var djList = new List<DengjiList>();
            var djModel1 = new DengjiList
            {
                Value = "正常登记",
                Title = "正常登记"
            };
            var djModel2 = new DengjiList
            {
                Value = "复检登记",
                Title = "复检登记"
            };
            var djModel3 = new DengjiList
            {
                Value = "外来登记",
                Title = "外来登记"
            };
            var djModel4 = new DengjiList
            {
                Value = "外出登记",
                Title = "外出登记"
            };
            djList.Add(djModel1);
            djList.Add(djModel2);
            djList.Add(djModel3);
            djList.Add(djModel4);
            ViewBag.djlb = new SelectList(djList, "Value", "Title");
            ViewBag.ssqu = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");
            ViewBag.zgzt = new SelectList(new JobsStateMaintainBll().GetAllList("", "是"), "id", "jobs_state");
            ViewBag.tjlx = new SelectList(new checktypemaintainBll().GetListBybelong("职业"), "id", "check_type");
            return View();
        }

        public string getQyList(Models.DTO.zydytj fc)
        {
            List<check_type_maintainModal> ctmList = new DAL.checktypemaintainDal().GetAllList("", "是");
            var qylist = new BLL.QueryStatisticsBll().getQYList(fc, ctmList);
            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "util_name", display = "企业名称", width = 300, align = "left" });
            int w = 0;
            foreach (check_type_maintainModal ctm in ctmList)
            {
                if (string.IsNullOrEmpty(ctm.check_type))
                {
                    ctm.check_type = "无类型";

                }
                w = ctm.check_type.Length * 15;
                cls.Add(new cols { name = ctm.check_type, display = ctm.check_type, width = w, align = "right" });
            }
            cls.Add(new cols { name = "total", display = "合计", width = 100, align = "right" });
            var data = new { Rows = qylist };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = data });
        }
        #endregion

        #region 主检工作量统计
        public ActionResult ZJGZLQuery()
        {
            ViewBag.tjlx = new SelectList(new checktypemaintainBll().GetListBybelong("职业"), "id", "check_type");
            ViewBag.zjys = new SelectList(new BLL.QueryStatisticsBll().getZjys(), "main_user_name", "main_user_name");
            return View();
        }

        public string getZJList(Models.DTO.zydytj fc)
        {
            var zjlist = new BLL.QueryStatisticsBll().getZjgzlList(fc);
            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "main_user_name", display = "主检医生", width = 150, align = "left" });
            cls.Add(new cols { name = "hege", display = "合格人数", width = 150, align = "right" });
            cls.Add(new cols { name = "buhege", display = "不合格人数", width = 150, align = "right" });
            cls.Add(new cols { name = "fujian", display = "复检人数", width = 150, align = "right" });
            cls.Add(new cols { name = "total", display = "合计", width = 150, align = "right" });
            var data = new { Rows = zjlist };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = data });
        }
        #endregion

        #region 收费情况统计
        public ActionResult ShouFeiQuery()
        {
            return View();
        }
        /// <summary>
        /// 收费时间段统计
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public string getShouFeiList(string start, string end, string bm)
        {
            var result = new BLL.QueryStatisticsBll().getShouFeiList(start, end, bm).ToList();
            var sffs = new BLL.QueryStatisticsBll().getShouFeiFSList(start, end, bm).ToList();
            string fzName = "";
            decimal xj = 0;
            decimal hj = 0;
            DataTable dt = new DataTable();
            dt.Columns.Add("sfxm", typeof(string));
            dt.Columns.Add("sl", typeof(string));
            dt.Columns.Add("dj", typeof(string));
            dt.Columns.Add("xj", typeof(string));
            int i = 0;
            foreach (var r in result)
            {
                DataRow dr = dt.NewRow();
                if (fzName == "")
                {
                    fzName = r.fzmc;
                    dr["sfxm"] = r.sfxm;
                    dr["sl"] = r.sl;
                    dr["dj"] = r.dj;
                    dr["xj"] = r.sl * r.dj;
                    xj += r.sl * r.dj;
                    dt.Rows.Add(dr.ItemArray);
                    hj += r.sl * r.dj;
                }
                else
                {
                    if (r.fzmc == fzName)
                    {
                        dr["sfxm"] = r.sfxm;
                        dr["sl"] = r.sl;
                        dr["dj"] = r.dj;
                        dr["xj"] = r.sl * r.dj;
                        xj += r.sl * r.dj;
                        dt.Rows.Add(dr.ItemArray);
                        hj += r.sl * r.dj;
                    }
                    else
                    {

                        dr["sfxm"] = "(" + fzName + ")小计：";
                        dr["sl"] = "";
                        dr["dj"] = "";
                        dr["xj"] = xj.ToString();
                        fzName = "";
                        xj = 0;
                        dt.Rows.Add(dr.ItemArray);

                        DataRow dr1 = dt.NewRow();
                        fzName = r.fzmc;
                        dr1["sfxm"] = r.sfxm;
                        dr1["sl"] = r.sl;
                        dr1["dj"] = r.dj;
                        dr1["xj"] = r.sl * r.dj;
                        xj += r.sl * r.dj;
                        dt.Rows.Add(dr1.ItemArray);
                        hj += r.sl * r.dj;
                    }
                }
                if (i == result.Count - 1)
                {
                    dr["sfxm"] = "(" + fzName + ")小计：";
                    dr["sl"] = "";
                    dr["dj"] = "";
                    dr["xj"] = xj.ToString();
                    fzName = "";
                    xj = 0;
                    dt.Rows.Add(dr.ItemArray);
                }
                i++;
            }
            string sffsstr = "合计：";
            foreach (var s in sffs)
            {
                sffsstr += s.sffs + "(" + s.je + ")";
            }
            DataRow dr2 = dt.NewRow();
            dr2["sfxm"] = "合计：";
            dr2["sl"] = "";
            dr2["dj"] = "";
            dr2["xj"] = hj.ToString();
            dt.Rows.Add(dr2.ItemArray);

            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "sfxm", display = "收费项目", width = 200 });
            cls.Add(new cols { name = "sl", display = "数量", width = 120, align = "right" });
            cls.Add(new cols { name = "dj", display = "单价", width = 120, align = "right" });
            cls.Add(new cols { name = "xj", display = "小计", width = 120, align = "right" });
            var data = new { Rows = dt };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = data, sffs = sffsstr });
        }

        [AllowAnonymous]
        public string getSftjList(string rq)
        {
            if (string.IsNullOrEmpty(rq))
                return null;
            string bm = "";
            if (rq.Split('|').Length == 3)
            {
                bm = rq.Split('|')[2]
;
            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.QueryStatisticsBll().getShouFeiList(rq.Split('|')[0].ToString(), rq.Split('|')[1].ToString(), bm).ToList());
        }

        [AllowAnonymous]
        public string getSffsList(string rq)
        {
            if (string.IsNullOrEmpty(rq))
                return null;
            string bm = "";
            if (rq.Split('|').Length == 3)
            {
                bm = rq.Split('|')[2]
;
            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.QueryStatisticsBll().getShouFeiFSList(rq.Split('|')[0].ToString(), rq.Split('|')[1].ToString(), bm).ToList());

        }
        #endregion

        #region 5）	行业类别统计
        public ActionResult HYLBQuery()
        {
            //ViewBag.ssqy = new SelectList(new AreaInfoMaintainBll().GetAllList(), "id", "area_name");
            //ViewBag.hylb = new SelectList(new MedicalSchemeMaintainBll().GetAllList(), "id", "medical_scheme");
            ViewBag.ssqy = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");
            ViewBag.hylb = new SelectList(new MedicalSchemeMaintainBll().GetAllList(), "medical_scheme", "medical_scheme");
            return View();
        }

        public string getHYLBList(string start, string end, string ssqy, string hylb)
        {
            var result = new BLL.QueryStatisticsBll().getHYLBList(start, end, ssqy, hylb).ToList();
            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "category", display = "类别名称", width = 150 });
            cls.Add(new cols { name = "all_count", display = "体检人数", width = 150 });
            cls.Add(new cols { name = "qualified", display = "合格人数", width = 150 });
            cls.Add(new cols { name = "unqualified", display = "不合格人数", width = 150 });
            cls.Add(new cols { name = "reexam_counts", display = "复检人数", width = 150 });

            dynamic dy = new { category = "合计", all_count = result.Sum(t => t.all_count), qualified = result.Sum(t => t.qualified), unqualified = result.Sum(t => t.unqualified), reexam_counts = result.Sum(t => t.reexam_counts) };
            result.Add(dy);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = data });
        }
        #endregion
        #region 6）	从业人员体检情况统计
        public ActionResult CYRYTJQuery()
        {
            ViewBag.ssqy = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");
            return View();
        }

        public string getCYRYTJList(string start, string end, string ssqy, string sort1, string sort2,string yyjg_val)
        {
            var result = new BLL.QueryStatisticsBll().getCYRYTJList(start, end, ssqy, sort1, sort2, yyjg_val).ToList();
            List<cols> cls = new List<cols>();
            if (sort2.ToLower() == "true")
            {
                List<cols> cls1 = new List<cols>();
                cls1.Add(new cols { name = "num", display = "30岁以下", width = 60, align = "right" });
                cls1.Add(new cols { name = "num1", display = "30-40岁", width = 60, align = "right" });
                cls1.Add(new cols { name = "num2", display = "40-50岁", width = 60, align = "right" });
                cls1.Add(new cols { name = "num3", display = "50岁以上", width = 60, align = "right" });
                cls1.Add(new cols { name = "num4", display = "无年龄", width = 60, align = "right" });
                cls1.Add(new cols { name = "num5", display = "总计", width = 60, align = "right" });
                List<cols> cls2 = new List<cols>();
                cls2.Add(new cols { name = "num6", display = "30岁以下", width = 60, align = "right" });
                cls2.Add(new cols { name = "num7", display = "30-40岁", width = 60, align = "right" });
                cls2.Add(new cols { name = "num8", display = "40-50岁", width = 60, align = "right" });
                cls2.Add(new cols { name = "num9", display = "50岁以上", width = 60, align = "right" });
                cls2.Add(new cols { name = "num10", display = "无年龄", width = 60, align = "right" });
                cls2.Add(new cols { name = "num11", display = "总计", width = 60, align = "right" });
                List<cols> cls3 = new List<cols>();
                cls3.Add(new cols { name = "num12", display = "30岁以下", width = 60, align = "right" });
                cls3.Add(new cols { name = "num13", display = "30-40岁", width = 60, align = "right" });
                cls3.Add(new cols { name = "num14", display = "40-50岁", width = 60, align = "right" });
                cls3.Add(new cols { name = "num15", display = "50岁以上", width = 60, align = "right" });
                cls3.Add(new cols { name = "num16", display = "无年龄", width = 60, align = "right" });
                cls3.Add(new cols { name = "num17", display = "总计", width = 60, align = "right" });
                List<cols> cls4 = new List<cols>();
                cls4.Add(new cols { name = "num18", display = "30岁以下", width = 60, align = "right" });
                cls4.Add(new cols { name = "num19", display = "30-40岁", width = 60, align = "right" });
                cls4.Add(new cols { name = "num20", display = "40-50岁", width = 60, align = "right" });
                cls4.Add(new cols { name = "num21", display = "50岁以上", width = 60, align = "right" });
                cls4.Add(new cols { name = "num22", display = "无年龄", width = 60, align = "right" });
                cls4.Add(new cols { name = "num23", display = "总计", width = 60, align = "right" });
                List<cols> cls5 = new List<cols>();
                cls5.Add(new cols { name = "num24", display = "30岁以下", width = 60, align = "right" });
                cls5.Add(new cols { name = "num25", display = "30-40岁", width = 60, align = "right" });
                cls5.Add(new cols { name = "num26", display = "40-50岁", width = 60, align = "right" });
                cls5.Add(new cols { name = "num27", display = "50岁以上", width = 60, align = "right" });
                cls5.Add(new cols { name = "num28", display = "无年龄", width = 60, align = "right" });
                cls5.Add(new cols { name = "num29", display = "总计", width = 60, align = "right" });
                List<cols> cls6 = new List<cols>();
                cls6.Add(new cols { name = "num30", display = "30岁以下", width = 60, align = "right" });
                cls6.Add(new cols { name = "num31", display = "30-40岁", width = 60, align = "right" });
                cls6.Add(new cols { name = "num32", display = "40-50岁", width = 60, align = "right" });
                cls6.Add(new cols { name = "num33", display = "50岁以上", width = 60, align = "right" });
                cls6.Add(new cols { name = "num34", display = "无年龄", width = 60, align = "right" });
                cls6.Add(new cols { name = "num35", display = "总计", width = 60, align = "right" });
                List<cols> cls7 = new List<cols>();
                cls7.Add(new cols { name = "num36", display = "30岁以下", width = 60, align = "right" });
                cls7.Add(new cols { name = "num37", display = "30-40岁", width = 60, align = "right" });
                cls7.Add(new cols { name = "num38", display = "40-50岁", width = 60, align = "right" });
                cls7.Add(new cols { name = "num39", display = "50岁以上", width = 60, align = "right" });
                cls7.Add(new cols { name = "num40", display = "无年龄", width = 60, align = "right" });
                cls7.Add(new cols { name = "num41", display = "总计", width = 60, align = "right" });
                List<cols> cls8 = new List<cols>();
                cls8.Add(new cols { name = "num42", display = "30岁以下", width = 60, align = "right" });
                cls8.Add(new cols { name = "num43", display = "30-40岁", width = 60, align = "right" });
                cls8.Add(new cols { name = "num44", display = "40-50岁", width = 60, align = "right" });
                cls8.Add(new cols { name = "num45", display = "50岁以上", width = 60, align = "right" });
                cls8.Add(new cols { name = "num46", display = "无年龄", width = 60, align = "right" });
                cls8.Add(new cols { name = "num47", display = "总计", width = 60, align = "right" });
                List<cols> cls12 = new List<cols>();
                cls12.Add(new cols { display = "男", width = 360, columns = cls1 });
                cls12.Add(new cols { display = "女", width = 360, columns = cls2 });
                List<cols> cls34 = new List<cols>();
                cls34.Add(new cols { display = "男", width = 360, columns = cls3 });
                cls34.Add(new cols { display = "女", width = 360, columns = cls4 });
                List<cols> cls56 = new List<cols>();
                cls56.Add(new cols { display = "男", width = 360, columns = cls5 });
                cls56.Add(new cols { display = "女", width = 360, columns = cls6 });
                List<cols> cls78 = new List<cols>();
                cls78.Add(new cols { display = "男", width = 360, columns = cls7 });
                cls78.Add(new cols { display = "女", width = 360, columns = cls8 });

                cls.Add(new cols { name = "categoryName", display = "类别名称", width = 100 });
                cls.Add(new cols { display = "体检人数(累计)", width = 720, columns = cls12, align = "right" });
                cls.Add(new cols { display = "完成体检人数(累计)", width = 720, columns = cls34, align = "right" });
                cls.Add(new cols { display = "合格人数(累计)", width = 720, columns = cls56, align = "right" });
                cls.Add(new cols { display = "不合格人数(累计)", width = 720, columns = cls78, align = "right" });

                dynamic dy = new
                {
                    categoryName = "合计",
                    num = result.Sum(t => t.num),
                    num1 = result.Sum(t => t.num1),
                    num2 = result.Sum(t => t.num2),
                    num3 = result.Sum(t => t.num3),
                    num4 = result.Sum(t => t.num4),
                    num5 = result.Sum(t => t.num5),
                    num6 = result.Sum(t => t.num6),
                    num7 = result.Sum(t => t.num7),
                    num8 = result.Sum(t => t.num8),
                    num9 = result.Sum(t => t.num9),
                    num10 = result.Sum(t => t.num10),
                    num11 = result.Sum(t => t.num11),
                    num12 = result.Sum(t => t.num12),
                    num13 = result.Sum(t => t.num13),
                    num14 = result.Sum(t => t.num14),
                    num15 = result.Sum(t => t.num15),
                    num16 = result.Sum(t => t.num16),
                    num17 = result.Sum(t => t.num17),
                    num18 = result.Sum(t => t.num18),
                    num19 = result.Sum(t => t.num19),
                    num20 = result.Sum(t => t.num20),
                    num21 = result.Sum(t => t.num21),
                    num22 = result.Sum(t => t.num22),
                    num23 = result.Sum(t => t.num23),
                    num24 = result.Sum(t => t.num24),
                    num25 = result.Sum(t => t.num25),
                    num26 = result.Sum(t => t.num26),
                    num27 = result.Sum(t => t.num27),
                    num28 = result.Sum(t => t.num28),
                    num29 = result.Sum(t => t.num29),
                    num30 = result.Sum(t => t.num30),
                    num31 = result.Sum(t => t.num31),
                    num32 = result.Sum(t => t.num32),
                    num33 = result.Sum(t => t.num33),
                    num34 = result.Sum(t => t.num34),
                    num35 = result.Sum(t => t.num35),
                    num36 = result.Sum(t => t.num36),
                    num37 = result.Sum(t => t.num37),
                    num38 = result.Sum(t => t.num38),
                    num39 = result.Sum(t => t.num39),
                    num40 = result.Sum(t => t.num40),
                    num41 = result.Sum(t => t.num41),
                    num42 = result.Sum(t => t.num42),
                    num43 = result.Sum(t => t.num43),
                    num44 = result.Sum(t => t.num44),
                    num45 = result.Sum(t => t.num45),
                    num46 = result.Sum(t => t.num46),
                    num47 = result.Sum(t => t.num47)
                };
                result.Add(dy);
            }
            else
            {
                foreach (var r in result)
                {
                    r.qualifiedrate = Math.Round(r.qualifiedrate, 2);
                }
                cls.Add(new cols { name = "categoryName", display = "类别名称", width = 150, align = "right" });
                cls.Add(new cols { name = "countNum", display = "体检人数", width = 150, align = "right" });
                cls.Add(new cols { name = "completeNum", display = "完成体检人数", width = 150, align = "right" });
                cls.Add(new cols { name = "qualifiedNum", display = "合格人数", width = 150, align = "right" });
                cls.Add(new cols { name = "qualifiedrate", display = "合格率", width = 150, align = "right" });
                dynamic dy = new { categoryName = "合计", countNum = result.Sum(t => t.countNum), completeNum = result.Sum(t => t.completeNum), qualifiedNum = result.Sum(t => t.qualifiedNum), qualifiedrate = result.Average(t => (decimal)t.qualifiedrate).ToString("#0.00"), align = "right" };
                result.Add(dy);
            }
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = data });
        }
        #endregion
        #region 7）	项目工作量统计
        public ActionResult XMGZLQuery()
        {
            ViewBag.ssqy = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");
            ViewBag.tjlx = new SelectList(new checktypemaintainBll().GetAllList("", "是"), "check_type", "check_type");
            ViewBag.rylb = new SelectList(new PersonTypeMaintainBll().GetAllList("", "是"), "type_name", "type_name");
            ViewBag.zgzt = new SelectList(new JobsStateMaintainBll().GetAllList("", "是"), "jobs_state", "jobs_state");
            ViewBag.lrys = new SelectList(new ExamGroupDoctorMatintainBll().getDoctor());
            return View();
        }

        public string getXMGZLList(string start, string end, string tjlx, string rylb, string xmlb, string ssqy, string nj, string bj, string dwmc, string zgzt, string lrys, string zjpd)
        {
            var result = new BLL.QueryStatisticsBll().getXMGZLList(start, end, tjlx, rylb, xmlb, ssqy, nj, bj, dwmc, zgzt, lrys, zjpd).ToList();
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public ActionResult getXM()
        {
            return View();
        }
        public ActionResult getXMList(string where)
        {
            var bll = new ExamProjectMaintainBll();
            var result = bll.GetAllList4(where);
            return Json(new { Rows = result });
        }
        #endregion
        #region 8）	复检流程查询
        public ActionResult FJLCQuery()
        {
            return View();
        }

        public JsonResult getFJLCList(string where, string where1)
        {
            var list = new BLL.QueryStatisticsBll().getFJLCList(where, where1);
            return Json(new { Rows = list });
        }

        public string getFJLCList2(string where, string where1)
        {
            return null;
        }
        #endregion
        #region 9）	登记信息查询
        public ActionResult DJXXQuery()
        {
            ViewBag.ssqy = new SelectList(new AreaInfoMaintainBll().GetAllList(), "area_name", "area_name");
            ViewBag.tjlx = new SelectList(new checktypemaintainBll().GetAllList("", "是"), "check_type", "check_type");
            ViewBag.fj = new SelectList(new List<Droplist> { new Droplist { Value = "是", Title = "是" }, new Droplist { Value = "否", Title = "否" } }, "Value", "Title");
            ViewBag.dj = new SelectList(new List<Droplist> { new Droplist { Value = "已冻结", Title = "已冻结" }, new Droplist { Value = "未冻结", Title = "未冻结" } }, "Value", "Title");
            return View();
        }

        public string getDJXXList(string start, string end, string tjlx, string ryxm, string tjbh_start, string tjbh_end, string ssqy, string dwmc, string fj, string dj)
        {
            var result = new BLL.QueryStatisticsBll().getDJXXList(start, end, tjlx, ryxm, tjbh_start, tjbh_end, ssqy, dwmc, fj, dj,Common.Global.jgid).ToList();
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public ActionResult DJXXUpdate(string id)
        {
            ViewData["hj"] = new SelectList(new NativeInfoMaintainBll().GetAllList(), "id", "natives");
            ViewData["work"] = new SelectList(new checkworktypemaintainBll().GetAllList(), "work_type_name", "work_type_name"); 
            if (!string.IsNullOrEmpty(id))
            {
                var bll = new BLL.ProfessionalExamRegisterBll();
                var model = bll.getModelByid(id);
                return View(model);
            }
            else
            {
                return View();
            }
        }


        public JsonResult saveDJXX(ProfessionalExamRegisterModel model)
        {
            var bll = new BLL.ProfessionalExamRegisterBll();
            return Json(bll.Update2(model));
        }

        public bool opDj(string id, string type)
        {
            if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(type))
                return false;
            var bll = new QueryStatisticsBll();
            var result = bll.opDj(id, type);
            return result;
        }

        #endregion

        #region 健康证发证信息查询
        public ActionResult JKZFZXXQuery()
        {
            return View();
        }
        public string getHYLBT()
        {
            List<MedicalSchemeMaintainModel> list = new MedicalSchemeMaintainBll().GetAllList();
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }
        public string getHYLBT2()
        {
            List<MedicalSchemeMaintainModel> list = new MedicalSchemeMaintainBll().GetAllList();
            List<MedicalSchemeMaintainModel> list2 = new List<MedicalSchemeMaintainModel>();
            foreach (var model in list)
            {
                if (model.medical_scheme == "公共场所" || model.medical_scheme == "食品从业" || model.medical_scheme == "药品从业")
                    list2.Add(model);
            }
            string modelslistjson = JsonConvert.SerializeObject(list2);
            return modelslistjson;
        }
        public string getSSQY()
        {
            List<AreaInfoMaintainModel> list = new AreaInfoMaintainBll().GetAllList();
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }
        public string getRYLB()
        {
            List<PersonTypeMaintainModel> list = new PersonTypeMaintainBll().GetAllList("", "是");
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }
        public string getWorkTypeMaintain()
        {
            List<WorkTypeMaintainModel> list = new WorkTypeMaintainBll().GetAllList("");
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }


        public string GetJKZFZXX(FormCollection data)
        {
            Criteria model = new Criteria();
            DateTime ru;
            DateTime.TryParse(data["startdate"].Substring(0, 15), out ru);
            model.startDate = ru.ToString("yyyy-MM-dd");
            DateTime.TryParse(data["enddate"].Substring(0, 15), out ru);
            model.endDate = ru.ToString("yyyy-MM-dd");

            if (data["checkstart"] != null && data["checkstart"] != "")
            {
                DateTime.TryParse(data["checkstart"].Substring(0, 15), out ru);
                model.checkstartDate = ru.ToString("yyyy-MM-dd");
            }
            if (data["checkend"] != null && data["checkend"] != "")
            {
                DateTime.TryParse(data["checkend"].Substring(0, 15), out ru);
                model.checkendDate = ru.ToString("yyyy-MM-dd");
            }
            model.physical_num = data["physical_num"];
            model.category = data["category"];
            model.personName = data["personName"];


            model.exam_type = data["exam_type_id"];
            model.work_category_id = data["work_category_id"];
            model.yyjg = data["yyjg"];


            var zjlist = new QueryStatisticsBll().queryJKZFZXX(model);

            string modelslistjson = JsonConvert.SerializeObject(zjlist);
            return "{\"Rows\":" + modelslistjson + "}";
        }

        public ActionResult JKZFZXXQuery2()
        {
            return View();
        }


        #endregion

        #region 健康证发证统计
        public ActionResult JKZFZTJQuery()
        {
            return View();
        }
        public string getTJFA()
        {
            List<MedicalSchemeMaintainModel> list = new MedicalSchemeMaintainBll().GetAllList();
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }

        public string GetJKZFTTJXX(FormCollection data)
        {
            Criteria model = new Criteria();

            DateTime ru;
            if (data["startdate"] != null && data["startdate"] != "")
            {
                DateTime.TryParse(data["startdate"].Substring(0, 15), out ru);
                model.startDate = ru.ToString("yyyy-MM-dd");
            }
            if (data["enddate"] != null && data["enddate"] != "")
            {
                DateTime.TryParse(data["enddate"].Substring(0, 15), out ru);
                model.endDate = ru.ToString("yyyy-MM-dd");
            }

            model.person_type = data["person_type"];
            model.area_name = data["area_name"];



            var zjlist = new QueryStatisticsBll().queryJKZFZTJ(model);

            string modelslistjson = JsonConvert.SerializeObject(zjlist);
            return "{\"Rows\":" + modelslistjson + "}";
        }
        #endregion

        #region  健康证发证累计统计
        public ActionResult JKZFZLJTJQuery()
        {
            return View();
        }

        public string GetJKZFZLJTJ(FormCollection data)
        {
            Criteria model = new Criteria();
            DateTime ru;
            if (data["startdate"] != null && data["checkstart"] != "")
            {
                DateTime.TryParse(data["startdate"].Substring(0, 15), out ru);
                model.startDate = ru.ToString("yyyy-MM-dd");
            }
            if (data["enddate"] != null && data["checkend"] != "")
            {
                DateTime.TryParse(data["enddate"].Substring(0, 15), out ru);
                model.endDate = ru.ToString("yyyy-MM-dd");
            }

            model.exam_type = data["code"];
            model.area_name = data["area_name"];
            model.yyjg = (Common.Global.jgid == 49 ? "" : Common.Global.jgid.ToString());

            var zjlist = new QueryStatisticsBll().queryJKZFZLJTJ(model);

            string modelslistjson = JsonConvert.SerializeObject(zjlist);
            return "{\"Rows\":" + modelslistjson + "}";
        }
        #endregion

        #region 体检人数统计
        public ActionResult TJRSTJQuery()
        {
            return View();
        }

        public string GetAlPersonList()
        {
            var list = new QueryStatisticsBll().GetAlPersonList();
            string modelslistjson = JsonConvert.SerializeObject(list);
            return modelslistjson;
        }
        public string GetTJRSTJ(FormCollection data)
        {
            Criteria model = new Criteria();
            DateTime ru;
            DateTime.TryParse(data["startdate"].Substring(0, 15), out ru);
            model.startDate = ru.ToString("yyyy-MM-dd");
            DateTime.TryParse(data["enddate"].Substring(0, 15), out ru);
            model.endDate = ru.ToString("yyyy-MM-dd");

            if (data["checkstart"] != null && data["checkstart"] != "")
            {
                DateTime.TryParse(data["checkstart"].Substring(0, 15), out ru);
                model.checkstartDate = ru.ToString("yyyy-MM-dd");
            }
            if (data["checkend"] != null && data["checkend"] != "")
            {
                DateTime.TryParse(data["checkend"].Substring(0, 15), out ru);
                model.checkendDate = ru.ToString("yyyy-MM-dd");
            }
            model.physical_num = data["physical_num"];
            model.category = data["category"];
            string exam_type = data["exam_type"];
            string persons = data["person_name"];
            List<string> exam_types = new List<string>();
            string[] register_persons = { };
            if (exam_type != null && exam_type != "")
            {
                string[] exam_typessss = exam_type.Split(';');
                foreach (var str in exam_typessss)
                {
                    exam_types.Add(str);
                }
            }
            if (persons != null && persons != "")
            {
                register_persons = persons.Split(';');
            }
            model.exam_type = data["exam_type_id"];
            model.work_category_id = data["work_category_id"];

            var zjlist = new QueryStatisticsBll().getTJRSTJList(model.startDate, model.endDate, model.checkstartDate, model.checkendDate, register_persons, model.area_name,Common.Global.jgid);
            if (exam_types != null && exam_types.Count > 0)
            {
                zjlist = zjlist.Where(t => exam_types.Contains(t.exam_type)).ToList();
            }

            string modelslistjson = JsonConvert.SerializeObject(zjlist);
            return "{\"Rows\":" + modelslistjson + "}";
        }

        #endregion

        #region 每日收费汇总
        public ActionResult mrsfhzQuery()
        {
            return View();
        }
        public string getmrsfhz(string b, string e)
        {

            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.chargeBll().getMRSFHZ(b, e));
        }
        #endregion

        #region 健康证打印人数
        public ActionResult JKZDYRSQuery()
        {
            return View();
        }
        public string getJKZDYRS(string b, string e)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.ProfessionalExamRegisterBll().getJKZDYRS(b, e));
        }
        #endregion

        #region  预约情况统计
        public ActionResult yyqktjQuery()
        {
            return View();
        }
        public string getYYQKTJ(string b, string e)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.MedicalAppointmentBll().getYYQKTJ(b, e));
        }
        #endregion

        #region 疫苗收费统计
        public ActionResult ymsftjQuery()
        {
            return View();
        }
        public string getYMSFTJ(string b, string e)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(new BLL.chargeBll().getYMSFTJ(b, e));
        }
        #endregion

        #region 4.6	物资库存情况查询
        public ActionResult wzkcQuery()
        {
            ViewBag.wzlbD = new SelectList(new wzlbBll().GetAllList("", "1"), "id", "lbmc");
            ViewBag.kD = new SelectList(kD, "value", "title");
            return View();
        }
        public JsonResult getWZKC(string key, string lb, string kcid)
        {
            var result = new BLL.QueryStatisticsBll().getWZKCList(key, lb, kcid);
            return Json(new { Rows = result });
        }
        #endregion
        #region 4.3.1	项目经费使用统计
        public ActionResult xmjfsytjQuery()
        {
            return View();
        }
        public string getXMJFSYTJ(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getXMJFSYTJ(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion
        #region 4.3.2	项目经费使用明细（物品采购）
        public ActionResult xmjfsymxQuery()
        {
            return View();
        }
        public string getXMJFSYMX(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getXMJFSYMX(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion
        #region 4.4.1	一定时期内各部门采购单申请的数量
        public ActionResult cgsqslQuery()
        {
            return View();
        }
        public string getCGSQSL(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getCGSQSL(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        public JsonResult getCGSQSL2(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getCGSQSL2(start, end, key);
            return Json(new { Rows = result });
        }
        #endregion
        #region  4.4.2 按照月份对疾控中心的物资申购进行纵向对比
        public ActionResult cgsqslyfQuery()
        {
            return View();
        }
        public string getCGSQSLYF(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getCGSQSLYF(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion
        #region 4.4.3	按照部门对一定日期内物资申购情况进行横向对比
        public ActionResult cgsqslbmQuery()
        {
            return View();
        }
        public string getCGSQSLBM(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getCGSQSLBM(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion
        #region 4.5.1	一定时期内各部门物资领用情况
        public ActionResult wzlyqkQuery()
        {
            return View();
        }
        public string getWZLYQK(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZLYQK(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        public JsonResult getWZLYQK2(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZLYQK2(start, end, key);
            return Json(new { Rows = result });
        }
        #endregion
        #region 4.5.2 按照月份对疾控中心的物资领用情况进行纵向对比
        public ActionResult wzlyqkyfQuery()
        {
            return View();
        }
        public string getWZLYQKYF(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZLYQKYF(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion
        #region 4.5.3 按照部门对疾控中心物资领用情况进行横向对比
        public ActionResult wzlyqkbmQuery()
        {
            return View();
        }
        public string getWZLYQKBM(string start, string end, string key)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZLYQKBM(start, end, key);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        #endregion

        #region 下载文件
        public FileResult down(string file)
        {
            string path = Server.MapPath("~/exp/");
            if (file.EndsWith("drimp"))
                return File(path + file, "application/drimp", DateTime.Now.ToString("yyyyMMdd") + ".drimp");
            if (file.EndsWith("xlsx"))
                return File(path + file, "application/Excel", DateTime.Now.ToString("yyyyMMdd") + ".xlsx");
            if (file.EndsWith("syzip"))
                return File(path + file, "application/Zip", DateTime.Now.ToString("yyyyMMdd") + ".syzip");
            return null;
        }
        #endregion

        #region 1 物资进出明细表
        public ActionResult jcmxbQuery()
        {
            return View();
        }
        public string getJCMXB(string start, string end, string key, string key1, string minkcsl)
        {
            if (minkcsl == "true")
                minkcsl = "0";
            else
                minkcsl = "-1";
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getJCMXB(start, end, key, key1, minkcsl);
            var result2 = new BLL.QueryStatisticsBll().getJCMXB2(start, end);
            foreach (var r2 in result2)
            {
                foreach (var r in result)
                {
                    if (r.wzid == r2.wzid)
                    {
                        switch ((string)r2.sort)
                        {
                            case "rk": r.rk = r2.sl; r.rkze = r.dj * r2.sl; break;
                            case "th": r.th = r2.sl; break;
                            case "ck": r.ck = r2.sl; break;
                            case "tk": r.tk = r2.sl; break;
                            case "pd": r.pd = r2.sl; break;
                            case "bs": r.bs = r2.sl; break;
                        }
                        break;
                    }
                }
            }
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getJCMXBExcel(string start, string end, string key, string key1, string sign)
        {
            if (sign == "true")
                sign = "0";
            else
                sign = "-1";
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getJCMXB(start, end, key, key1, sign);
            var result2 = new BLL.QueryStatisticsBll().getJCMXB2(start, end);
            foreach (var r2 in result2)
            {
                foreach (var r in result)
                {
                    if (r.wzid == r2.wzid)
                    {
                        switch ((string)r2.sort)
                        {
                            case "rk": r.rk = r2.sl; r.rkze = r.dj * r2.sl; break;
                            case "th": r.th = r2.sl; break;
                            case "ck": r.ck = r2.sl; break;
                            case "tk": r.tk = r2.sl; break;
                            case "pd": r.pd = r2.sl; break;
                            case "bs": r.bs = r2.sl; break;
                        }
                        break;
                    }
                }
            }

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("入库数");
            dt.Columns.Add("退货数");
            dt.Columns.Add("出库数");
            dt.Columns.Add("退库数");
            dt.Columns.Add("盘点数");
            dt.Columns.Add("报损数");
            dt.Columns.Add("库存数");
            dt.Columns.Add("单价");
            dt.Columns.Add("库存总额");
            dt.Columns.Add("入库总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["入库数"] = m.rk;
                mdr["退货数"] = m.th;
                mdr["出库数"] = m.ck;
                mdr["退库数"] = m.tk;
                mdr["盘点数"] = m.pd;
                mdr["报损数"] = m.bs;
                mdr["库存数"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["库存总额"] = m.kcze;
                mdr["入库总额"] = m.rkze;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 2.0 物资入库明细
        public ActionResult rkmxbQuery()
        {
            ViewData["gyslist"] = new SelectList(new gyszdBll().GetAllList(""), "id", "mc");
            return View();
        }
        public string getRKMXB(string start, string end, string key, string key1, string key2)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getRKMXB(start, end, key, key1, key2);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        public JsonResult getRKMXBExcel(string start, string end, string key, string key1, string key2)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getRKMXB(start, end, key, key1, key2);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("供应商");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            dt.Columns.Add("入库时间");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["供应商"] = m.gys;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.sl*m.dj;
                mdr["入库时间"] = m.rksj;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 2 物资入库历史记录
        public ActionResult wzrklsjlQuery()
        {
            return View();
        }
        public string getWZRKLSJL(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZRKLSJL(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }
        public JsonResult getWZRKLSJLExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZRKLSJL(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("入库数");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["入库数"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.je;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 3 物资发放明细
        public ActionResult wzffmxQuery()
        {
            ViewData["kslist"] = new SelectList(new ERPBuMenBll().GetAllList(""), "id", "bumenname");
            return View();
        }
        public string getWZFFMX(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZFFMX(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getWZFFMXExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZFFMX(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("科室名称");
            dt.Columns.Add("领用人");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["科室名称"] = m.ksmc;
                mdr["领用人"] = m.lyr;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.sl*m.dj;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 4 物资发放总表
        public ActionResult wzffzbQuery()
        {
            ViewData["kslist"] = new SelectList(new ERPBuMenBll().GetAllList(""), "id", "bumenname");
            return View();
        }
        public string getWZFFZB(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZFFZB(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getWZFFZBExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getWZFFZB(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("科室名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["科室名称"] = m.ksmc;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.ckje;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 5 退货明细表
        public ActionResult thmxbQuery()
        {
            ViewData["gyslist"] = new SelectList(new gyszdBll().GetAllList(""), "id", "mc");
            return View();
        }
        public string getTHMXB(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getTHMXB(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getTHMXBExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getTHMXB(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("供应商");
            dt.Columns.Add("退货日期");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["供应商"] = m.gys;
                mdr["退货日期"] = m.thsj;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.sl*m.dj;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 6 退库明细表
        public ActionResult tkmxbQuery()
        {
            ViewData["kslist"] = new SelectList(new ERPBuMenBll().GetAllList(""), "id", "bumenname");
            return View();
        }
        public string getTKMXB(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getTKMXB(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getTKMXBExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getTKMXB(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("科室名称");
            dt.Columns.Add("退库日期");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["科室名称"] = m.ksmc;
                mdr["退库日期"] = m.tksj;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.sl*m.dj;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 7 盘点明细表
        public ActionResult pdmxbQuery()
        {
            return View();
        }
        public string getPDMXB(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getPDMXB(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getPDMXBExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getPDMXB(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.je;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 8 报损明细表
        public ActionResult bsmxbQuery()
        {
            return View();
        }
        public string getBSMXB(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getBSMXB(start, end, key, key1);
            var data = new { Rows = result };
            return Newtonsoft.Json.JsonConvert.SerializeObject(new { data = data });
        }

        public JsonResult getBSMXBExcel(string start, string end, string key, string key1)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getBSMXB(start, end, key, key1);

            DataTable dt = new DataTable();
            dt.Columns.Add("物资编码");
            dt.Columns.Add("物资名称");
            dt.Columns.Add("规格型号");
            dt.Columns.Add("生产厂家");
            dt.Columns.Add("计量单位");
            dt.Columns.Add("数量");
            dt.Columns.Add("单价");
            dt.Columns.Add("总额");
            foreach (var m in result)
            {
                DataRow mdr = dt.NewRow();
                mdr["物资编码"] = m.bm;
                mdr["物资名称"] = m.wzmc;
                mdr["规格型号"] = m.ggxh;
                mdr["生产厂家"] = m.sccj;
                mdr["计量单位"] = m.jldw;
                mdr["数量"] = m.sl;
                mdr["单价"] = m.dj;
                mdr["总额"] = m.je;
                dt.Rows.Add(mdr);
            }

            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
        #region 9 核算明细表
        public ActionResult hsmxbQuery()
        {
            ViewBag.kD = new SelectList(kD, "value", "title");
            return View();
        }
        public string getHSMXB(string start, string end, string kcid)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getHSMXB(start, end, kcid);

            DataTable dt = new DataTable();
            dt.Columns.Add("ks", typeof(string));
            dt.Columns.Add("hj", typeof(decimal));

            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "ks", display = "科室", width = 150 });
            cls.Add(new cols { name = "hj", display = "合计", width = 100, align = "right" });

            List<string> columns = new List<string>();
            foreach (var r in result)
            {
                if (string.IsNullOrEmpty(r.lbmc))
                    continue;
                if (!columns.Contains(r.lbmc))
                {
                    columns.Add(r.lbmc);

                    dt.Columns.Add(r.lbmc, typeof(string));
                    cls.Add(new cols { name = r.lbmc, display = r.lbmc, width = 70, align = "right" });
                }

            }

            foreach (var r in result)
            {
                if (string.IsNullOrEmpty(r.lbmc))
                    continue;
                DataRow dr;
                if (dt.Select("ks='" + r.ksmc + "'").Length == 0)
                {
                    dr = dt.NewRow();
                    dr["ks"] = r.ksmc;
                    dr["hj"] = r.je;
                    dr[r.lbmc] = r.je;
                    dt.Rows.Add(dr);
                }
                else
                {
                    dr = dt.Select("ks='" + r.ksmc + "'")[0];
                    dr["hj"] = (decimal)dr["hj"] + r.je;
                    dr[r.lbmc] = r.je;
                }
            }
            foreach (DataRow dr in dt.Rows)
            {
                for(int i=0;i<dt.Columns.Count;i++)
                {
                    if(dr[i].ToString()=="")
                    {
                        dr[i] = 0;
                    }
                }

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(new { columns = cls, data = new { Rows = dt } });
        }

        public JsonResult getHSMXBExcel(string start, string end, string kcid)
        {
            start = start + " 00:00:00";
            end = end + " 23:59:59";
            var result = new BLL.QueryStatisticsBll().getHSMXB(start, end, kcid);
            var data = new { Rows = result };

            DataTable dt = new DataTable();
            dt.Columns.Add("ks", typeof(string));
            dt.Columns.Add("hj", typeof(decimal));

            List<cols> cls = new List<cols>();
            cls.Add(new cols { name = "ks", display = "科室", width = 150 });
            cls.Add(new cols { name = "hj", display = "合计", width = 150, totalSummary = new Dictionary<string, string>() { { "type", "sum" } } });

            List<string> columns = new List<string>();
            foreach (var r in result)
            {
                if (string.IsNullOrEmpty(r.lbmc))
                    continue;
                if (!columns.Contains(r.lbmc))
                {
                    columns.Add(r.lbmc);

                    dt.Columns.Add(r.lbmc, typeof(string));
                    cls.Add(new cols { name = r.lbmc, display = r.lbmc, width = 150, totalSummary = new Dictionary<string, string>() { { "type", "sum" } } });
                }

            }

            foreach (var r in result)
            {
                if (string.IsNullOrEmpty(r.lbmc))
                    continue;
                DataRow dr;
                if (dt.Select("ks='" + r.ksmc + "'").Length == 0)
                {
                    dr = dt.NewRow();
                    dr["ks"] = r.ksmc;
                    dr["hj"] = r.je;
                    dr[r.lbmc] = r.je;
                    dt.Rows.Add(dr);
                }
                else
                {
                    dr = dt.Select("ks='" + r.ksmc + "'")[0];
                    dr["hj"] = (decimal)dr["hj"] + r.je;
                    dr[r.lbmc] = r.je;
                }
            }
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dr[i].ToString() == "")
                    {
                        dr[i] = 0;
                    }
                }

            }
            string file = Guid.NewGuid().ToString("N") + ".xlsx";
            string path = Server.MapPath("~/exp/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            try
            {
                xiaoy.Excel.ExcelFile.SetData(dt, path + file, xiaoy.Excel.ExcelVersion.Excel12, xiaoy.Excel.HDRType.Yes);
                return Json(new { State = "1", Message = file });
            }
            catch (Exception ex)
            {
                return Json(new { State = "0", Message = ex.Message });
            }
        }
        #endregion
    }
}