using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using dccdc.Models;
using Dapper;
using System.Reflection;
using System.Collections;

namespace dccdc.DAL
{
    public class QueryStatisticsDal
    {
        //体检小组合格率统计
        public List<Models.DTO.XZHGL> getXZHGL(string dateStr, string dateEnd, string exam_group_maintain_id)
        {
            string sql = "select isnull(uq.cou,0) as unqualified,c.cou as total,c.cou-isnull(uq.cou ,0)as qualified, " +
                         "g.team_name as team_name,g.id as id from(select count(*) cou, exam_group_maintain_id from " +
                         "(select exam_group_maintain_id, person_id  from professionalExam_project_result " +
                         " where commit_date is not null   and commit_date >= '" + dateStr + "' and " +
                         "commit_date <= '" + dateEnd + "'  and exam_group_maintain_id = " + exam_group_maintain_id + " " +
                         "group by exam_group_maintain_id, person_id) as a group by a.exam_group_maintain_id) as " +
                         "c left join exam_group_maintain g on g.id = c.exam_group_maintain_id " +
                         "left join (select count(*) cou, exam_group_maintain_id from " +
                         "(select person_id, exam_group_maintain_id From professionalExam_project_result " +
                         "where qualified = '不合格' and commit_date is not null " +
                         "and commit_date >= '" + dateStr + "' and  commit_date <= '" + dateEnd + "' " +
                         "and exam_group_maintain_id = " + exam_group_maintain_id + "  group by exam_group_maintain_id, person_id) " +
                         "a group by a.exam_group_maintain_id ) as uq on " +
                         "c.exam_group_maintain_id = uq.exam_group_maintain_id";

            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.DTO.XZHGL>(sql).ToList();
            }
        }

        //体检档案查询
        public List<ProfessionalExamRegisterModel> getTjdaList(Models.DTO.zydytj fc)
        {
            //throw new NotImplementedException();
            string sql = @"select 
* from professionalExam_register where 
   physical_num is not null  and 
    (freezing_and_thawing is null or freezing_and_thawing='') ";
            if (!string.IsNullOrEmpty(fc.xm))
            {
                sql += " and person_name like @xm";
                fc.xm = "%" + fc.xm + "%";
            }
            if (!string.IsNullOrEmpty(fc.tjbh))
            {
                sql += " and physical_num = @tjbh";
            }
            if (!string.IsNullOrEmpty(fc.djrq))
            {
                sql += " and register_date>=@djrq";
            }
            if (!string.IsNullOrEmpty(fc.djjsrq))
            {
                sql += " and register_date<=@djjsrq";
            }
            if (!string.IsNullOrEmpty(fc.sfzh))
            {
                sql += " and card_number=@sfzh";
            }
            if (!string.IsNullOrEmpty(fc.hylb))
            {
                sql += " and trade_type_maintain_id=@hylb";
            }
            if (!string.IsNullOrEmpty(fc.hjlb))
            {
                sql += " and native_info_maintain_id=@hjlb";
            }
            if (!string.IsNullOrEmpty(fc.ssqu))
            {
                sql += " and area=@ssqu";
            }
            if (!string.IsNullOrEmpty(fc.djlb))
            {
                sql += " and register_type=@djlb";
            }
            if (!string.IsNullOrEmpty(fc.tjfs))
            {
                sql += " and physical_way=@tjfs";
            }
            if (!string.IsNullOrEmpty(fc.qyid))
            {
                sql += " and enterprise_info_maintain_id=@qyid";
            }
            if (!string.IsNullOrEmpty(fc.tjfa))
            {
                sql += " and medical_scheme_maintain_id=@tjfa";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<ProfessionalExamRegisterModel>(sql, fc).ToList();
            }
        }

        //企业体检情况查询
        public IEnumerable<dynamic> getQYList(Models.DTO.zydytj fc, List<check_type_maintainModal> ctmList)
        {
            //throw new NotImplementedException();
            string strA = "";
            string sql = "";
            foreach (check_type_maintainModal ctmModel in ctmList)
            {
                if (string.IsNullOrEmpty(ctmModel.check_type))
                {
                    ctmModel.check_type = "无类型";
                }
                strA += "SUM(CASE WHEN exam_type = '" + ctmModel.check_type + "'  then 1 else 0 end) as '" + ctmModel.check_type + "',";
            }
            sql = "select  util_name," + strA + " count(id) as total " +
                  "FROM professionalExam_register " +
                  "where freezing_and_thawing is null ";

            if (!string.IsNullOrEmpty(fc.djrq))
            {
                sql += " and register_date>=@djrq";
            }
            if (!string.IsNullOrEmpty(fc.djjsrq))
            {
                sql += " and register_date<=@djjsrq";
            }
            if (!string.IsNullOrEmpty(fc.ssqu))
            {
                sql += " and area=@ssqu";
            }
            if (!string.IsNullOrEmpty(fc.tjlx))
            {
                sql += " and exam_type_id=@tjlx";
            }
            if (!string.IsNullOrEmpty(fc.zgzt))
            {
                sql += " and job_status_id=@zgzt";
            }
            if (!string.IsNullOrEmpty(fc.djlb))
            {
                sql += " and register_type=@djlb";
            }
            if (!string.IsNullOrEmpty(fc.qyid))
            {
                sql += " and enterprise_info_maintain_id=@qyid";
            }
            sql += " and physical_num is not null group by util_name";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, fc);
            }
        }

        //主检工作量统计
        public IEnumerable<dynamic> getZjgzlList(Models.DTO.zydytj fc)
        {
            string sql = "select main_user_name, " +
                         "sum(hege) as hege, " +
                         "sum(buhege) as buhege, " +
                         "sum(fujian) as fujian, " +
                         "sum(total) as total " +
                         "from(select main_user_name, case when result_status = '合格' " +
                         "then 1 else 0 end as hege, case when result_status = '不合格' " +
                         "then 1 else 0 end as buhege, case when result_status = '复检' " +
                         "then 1 else 0 end as fujian, 1 as total from " +
                         "professionalExam_register where procedure_status in('主检医生已审核', '已打印健康证') ";


            if (!string.IsNullOrEmpty(fc.djrq))
            {
                sql += " and register_date>=@djrq";
            }
            if (!string.IsNullOrEmpty(fc.djjsrq))
            {
                sql += " and register_date<=@djjsrq";
            }
            if (!string.IsNullOrEmpty(fc.tjrq))
            {
                sql += " and check_date>=@tjrq";
            }
            if (!string.IsNullOrEmpty(fc.tjjsrq))
            {
                sql += " and check_date<=@tjjsrq";
            }
            if (!string.IsNullOrEmpty(fc.zjrq))
            {
                sql += " and main_review_date>=@zjrq";
            }
            if (!string.IsNullOrEmpty(fc.zjjsrq))
            {
                sql += " and main_review_date<=@zjjsrq";
            }
            if (!string.IsNullOrEmpty(fc.tjlx))
            {
                sql += " and exam_type_id=@tjlx";
            }
            if (!string.IsNullOrEmpty(fc.zjys))
            {
                sql += " and main_user_name=@zjys";
            }

            sql += ") tb group by main_user_name";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, fc);
            }
        }

        //获取主检医生
        public List<ProfessionalExamRegisterModel> getZjys()
        {
            string sql = "select main_user_name from professionalExam_register where main_user_name!='' group by main_user_name";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<ProfessionalExamRegisterModel>(sql).ToList();
            }
        }

        //
        public IEnumerable<dynamic> queryJKZFZXX(Models.DTO.Criteria model)
        {
            //string sql = "select id,health_certificate_number as healthCertificateNumber,physical_num as physicalNum," +
            //            "person_name as personName, sex as Sex,person_age as personAge,card_number as cardCumber," +
            //            "util_name as utilName,phone as Phone,area as Area,category as categorys," +
            //            "work_category as workCategory,register_date as registerDate, check_date as checkDate," +
            //            "printtimes as printTimes,person_category as personCategory" +
            //            " from professionalExam_register" +
            //            " where procedure_status = '已打印健康证' ";

            string sql = @"select a.id,b.yzcod, health_certificate_number as healthCertificateNumber,physical_num as physicalNum,person_name as personName, sex as Sex,person_age as personAge,card_number as cardCumber,util_name as utilName,phone as Phone,area as Area,category as categorys,work_category as workCategory,register_date as registerDate, check_date as checkDate,printtimes as printTimes,category as personCategory 
                           from professionalExam_register a left join yanzheng b on a.id = b.pno where procedure_status = '已打印健康证' ";
            if (model.startDate != null && model.startDate != "")
            {
                sql += " and printtimes >= '" + model.startDate + "'";
            }
            if (model.endDate != null && model.endDate != "")
            {
                sql += " and printtimes < '" + DateTime.Parse( model.endDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
            }
            if (model.checkendDate != null && model.checkendDate != "")
            {
                sql += " and register_date < '" + DateTime.Parse(model.checkendDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
            }
            if (model.checkstartDate != null && model.checkstartDate != "")
            {
                sql += " and register_date >='" + model.checkstartDate + "'";
            }
            if (model.physical_num != null && model.physical_num != "")
            {
                sql += " and physical_num like '%" + model.physical_num + "%' ";
            }
            if (model.medical_scheme != null && model.medical_scheme != "")
            {
                sql += " and trade_type_maintain_id ='" + model.medical_scheme + "'";
            }
            if (model.person_type != null && model.person_type != "")
            {
                sql += " and person_type_maintain_id ='" + model.person_type + "'";
            }
            if (model.area_name != null && model.area_name != "")
            {
                sql += " and area ='" + model.area_name + "'";
            }
            if (model.exam_type != null && model.exam_type != "")
            {
                sql += " and  exam_type_id ='" + model.exam_type + "'";
            }

            if (model.work_category_id != null && model.work_category_id != "")
            {
                sql += " and  work_category_id ='" + model.work_category_id + "'";
            }
            if (model.category != null && model.category != "")
            {
                sql += " and  category ='" + model.category + "'";
            }
            if (model.yyjg != null && model.yyjg != "")
            {
                sql += " and  jgid ='" + model.yyjg + "'";
            }
            if (model.personName != null && model.personName != "")
            {
                sql += " and  person_name like '%" + model.personName + "%'";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql);
            }
        }

        //行业类别统计
        public IEnumerable<dynamic> getHYLBList(string start, string end, string ssqy, string hylb)
        {
            string sql = @"select category,sum(qualified)as qualified,sum(unqualified)as unqualified,sum(reexam_counts)as reexam_counts, sum(all_counts)as all_count from( select category, case when result_status='合格' then 1 else 0 end as qualified, case when result_status='不合格' then 1 else 0 end as unqualified, case when register_type='复检登记' then 1 else 0 end as reexam_counts,case when physical_num is not null then 1 else 0 end as all_counts 
                            from professionalExam_register
                            where freezing_and_thawing is null and exam_type like '从业%' and(procedure_status is not null or procedure_status not like'已登记')   and check_date >= @start and check_date <= @end";


            if (!string.IsNullOrEmpty(ssqy))
            {
                sql += " and area =@ssqy";
            }
            if (!string.IsNullOrEmpty(hylb))
            {
                sql += " and category =@hylb";
            }
            sql += ")tb group by category";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, ssqy = ssqy, hylb = hylb });
            }
        }

        //收费情况统计
        public IEnumerable<dynamic> getShouFeiList(string start, string end, string bm)
        {
            string where = "";
            if (string.IsNullOrEmpty(bm))
            {
                where += " and  isnull( charge.department,'')<>'" + new DAL.Common().getParm_Value("btjksmc", "尉署社区服务站", "默认不统计科室名称") + "' ";
            }
            else
            {
                where += " and isnull( charge.department,'')='" + bm + "' ";
            }
            string sql = "select charge.department as fzmc,charge_detail.projectname as sfxm,sum(charge_detail.count) as sl,charge_detail.money as dj " +
                         "from charge inner join charge_detail on charge.id = charge_detail.chargeid where charge.status='已缴费' " + where + " and charge.moneydate " +
                         "between @start and @end group by charge.department,charge_detail.projectname, " +
                         "charge_detail.money";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = DateTime.Parse(start), end = DateTime.Parse(end).AddDays(1) });
            }
        }

        //收费方式情况统计
        public IEnumerable<dynamic> getShouFeiFSList(string start, string end, string bm)
        {
            string where = "";
            if (string.IsNullOrEmpty(bm))
            {
                where += " and isnull( charge.department,'')<>'" + new DAL.Common().getParm_Value("btjksmc", "尉署社区服务站", "默认不统计科室名称") + "' ";
            }
            else
            {
                where += " and isnull( charge.department,'')='" + bm + "' ";
            }
            string sql = "select method as sffs,sum(money) as je from charge where status='已缴费' " + where + " and moneydate between @start and @end group by method ";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = DateTime.Parse(start), end = DateTime.Parse(end).AddDays(1) });
            }
        }

        //从业人员体检情况统计
        public IEnumerable<dynamic> getCYRYTJList(string start, string end, string ssqy, string sort1, string sort2,string yyjg_val)
        {
            if(string.IsNullOrEmpty(yyjg_val))
            {
                yyjg_val = "0";
            }
            string sql = @"select *, (case when completeNum=0 then 0 else qualifiedNum/(completeNum*1.0) end) as qualifiedrate from (select category as categoryName,count(id) as countNum,sum(case when procedure_status!='已登记' then 1 else 0 end)  as completeNum,sum(case when result_status='合格' then 1 else 0 end) as qualifiedNum 
                            from professionalExam_register 
                            where freezing_and_thawing is null and physical_num is not null and category is not null";

            if (sort2.ToLower() == "true")
            {
                sql = @"select category as categoryName,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '男' then 1 else 0 end) as num,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '男' then 1 else 0 end) as num1,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '男' then 1 else 0 end) as num2,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '男' then 1 else 0 end) as num3,sum(case when person_age is null or  person_age = 0 and sex = '男' then 1 else 0 end) as num4,sum(case when sex = '男' then 1 else 0 end) as num5,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '女' then 1 else 0 end) as num6,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '女' then 1 else 0 end) as num7,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '女' then 1 else 0 end) as num8,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '女' then 1 else 0 end) as num9,sum(case when person_age is null or  person_age = 0 and sex = '女' then 1 else 0 end) as num10,sum(case when sex = '女' then 1 else 0 end) as num11,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num12,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num13,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num14,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num15,sum(case when person_age is null or  person_age = 0 and sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num16,sum(case when sex = '男' and procedure_status = '已录入检查结果' then 1 else 0 end) as num17,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num18,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num19,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num20,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num21,sum(case when person_age is null or  person_age = 0 and sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num22,sum(case when sex = '女' and procedure_status = '已录入检查结果' then 1 else 0 end) as num23,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '男' and result_status = '合格' then 1 else 0 end) as num24,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '男' and result_status = '合格' then 1 else 0 end) as num25,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '男' and result_status = '合格' then 1 else 0 end) as num26,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '男' and result_status = '合格' then 1 else 0 end) as num27,sum(case when person_age is null or  person_age = 0 and sex = '男' and result_status = '合格' then 1 else 0 end) as num28,sum(case when sex = '男' and result_status = '合格' then 1 else 0 end) as num29,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '女' and result_status = '合格' then 1 else 0 end) as num30,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '女' and result_status = '合格' then 1 else 0 end) as num31,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '女' and result_status = '合格' then 1 else 0 end) as num32,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '女' and result_status = '合格' then 1 else 0 end) as num33,sum(case when person_age is null or  person_age = 0 and sex = '女' and result_status = '合格' then 1 else 0 end) as num34,sum(case when sex = '女' and result_status = '合格' then 1 else 0 end) as num35,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '男' and result_status = '不合格' then 1 else 0 end) as num36,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '男' and result_status = '不合格' then 1 else 0 end) as num37,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '男' and result_status = '不合格' then 1 else 0 end) as num38,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '男' and result_status = '不合格' then 1 else 0 end) as num39,sum(case when person_age is null or  person_age = 0 and sex = '男' and result_status = '不合格' then 1 else 0 end) as num40,sum(case when sex = '男' and result_status = '不合格' then 1 else 0 end) as num41,sum(case when(YEAR(GETDATE()) - YEAR(birth)) < 30  and sex = '女' and result_status = '不合格' then 1 else 0 end) as num42,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 30 and(YEAR(GETDATE()) - YEAR(birth)) < 40 and sex = '女' and result_status = '不合格' then 1 else 0 end) as num43,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 40 and(YEAR(GETDATE()) - YEAR(birth)) < 50 and sex = '女' and result_status = '不合格' then 1 else 0 end) as num44,sum(case when(YEAR(GETDATE()) - YEAR(birth)) >= 50 and sex = '女' and result_status = '不合格' then 1 else 0 end) as num45,sum(case when person_age is null or  person_age = 0 and sex = '女' and result_status = '不合格' then 1 else 0 end) as num46,sum(case when sex = '女' and result_status = '不合格' then 1 else 0 end) as num47
                        from professionalExam_register where freezing_and_thawing is null and category is not null ";
            }

            if (!string.IsNullOrEmpty(start))
            {
                sql += " and register_date >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and register_date <=@end";
            }
            if (!string.IsNullOrEmpty(ssqy))
            {
                sql += " and area =@ssqy";
            }
            if (yyjg_val != "0")
            {
                sql += " and isnull(jgid,49)=@jgid";
            }
            if (sort2.ToLower() == "true")
            {
                sql += " group by category";
            }
            else
            {
                sql += " group by category)as t";
            }
            
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, ssqy = ssqy, sort1 = sort1, sort2 = sort2,jgid=yyjg_val });
            }
        }

        //项目工作量统计
        public IEnumerable<dynamic> 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)
        {
            //where exam_group_maintain_id = 46
            string sql = @"select tt.project_id,unqualified,qualified,uncomplete,complete,allcount,project_name,exam_group from( 
                        select project_id,sum(unqualified)as unqualified,sum(qualified)as qualified,sum(uncomplete)as uncomplete,sum(complete)as complete,sum(1) as allcount from 
                        ( select project_id, case when qualified ='不合格' then 1 else 0 end as unqualified, case when qualified ='合格' then 1 else 0 end as qualified, case when presenter is null then 1 else 0 end as uncomplete, case when presenter is not null then 1 else 0 end as complete 
                        from (select*from professionalExam_project_result 
                        )as result left join  professionalExam_register as register on register.id=result.person_id 
                        where 1=1";

            if (!string.IsNullOrEmpty(start))
            {
                sql += " and register.check_date >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and register.check_date <=@end";
            }
            if (!string.IsNullOrEmpty(tjlx))
            {
                sql += "  and register.exam_type=@tjlx";
            }
            if (!string.IsNullOrEmpty(rylb))
            {

            }
            if (!string.IsNullOrEmpty(xmlb))
            {
                sql += " and result.project_type =@xmlb";
            }
            if (!string.IsNullOrEmpty(ssqy))
            {
                sql += " and register.area =@ssqy";
            }
            if (!string.IsNullOrEmpty(dwmc))
            {
                sql += " and register.util_name =@dwmc";
            }
            if (!string.IsNullOrEmpty(zgzt))
            {
                sql += " and register.status  =@zgzt";
            }
            if (!string.IsNullOrEmpty(lrys))
            {
                sql += "  and result.rummager =@lrys";
            }
            if (zjpd.ToLower() == "true")
            {
                sql += "  and register.procedure_status in('主检医生已审核','已打印健康证')";
            }
            sql += " ) as t group by t.project_id) as tt left join exam_project_maintain on tt.project_id=exam_project_maintain.project_id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, tjlx = tjlx, rylb = rylb, xmlb = xmlb, ssqy = ssqy, nj = nj, bj = bj, dwmc = dwmc, lrys = lrys, zgzt = zgzt, zjpd = zjpd });
            }
        }

        //登记信息查询
        public IEnumerable<dynamic> getDJXXList(string start, string end, string tjlx, string ryxm, string tjbh_start, string tjbh_end, string ssqy, string dwmc, string fj, string dj,int jgid)
        {
            string sql = @"select * from  professionalExam_register where 1=1 and isnull(special_health_certificate,'')<>'快速办证'";

            if (!string.IsNullOrEmpty(start))
            {
                sql += " and register_date >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and register_date <=@end";
            }
            if (!string.IsNullOrEmpty(tjlx))
            {
                sql += "  and exam_type=@tjlx";
            }
            if (!string.IsNullOrEmpty(ryxm))
            {
                sql += "  and person_name like @ryxm";
            }
            if (!string.IsNullOrEmpty(tjbh_start))
            {
                sql += " and physical_num >=@tjbh_start";
            }
            if (!string.IsNullOrEmpty(tjbh_end))
            {
                sql += " and physical_num <=@tjbh_end";
            }
            if (!string.IsNullOrEmpty(ssqy))
            {
                sql += " and area =@ssqy";
            }
            if (!string.IsNullOrEmpty(dwmc))
            {
                sql += " and util_name =@dwmc";
            }
            if (!string.IsNullOrEmpty(fj))
            {
                if (fj == "是")
                    sql += " and register_type  ='复检登记'";
                else
                    sql += " and register_type !='复检登记'";
            }
            if (!string.IsNullOrEmpty(dj))
            {
                if (dj == "已冻结")
                    sql += " and freezing_and_thawing = '冻结'";
                else
                    sql += " and freezing_and_thawing is null";
            }
            if(jgid!=49)
            {
                sql += " and jgid=" + jgid;
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, tjlx = tjlx, ryxm = "%" + ryxm + "%", tjbh_start = tjbh_start, tjbh_end = tjbh_end, ssqy = ssqy, dwmc = dwmc, fj = fj, dj = dj });
            }
        }
        //冻结解冻
        public bool opDj(string id, string type)
        {
            string sql = "";
            switch (type)
            {
                case "1": sql = "update professionalExam_register set freezing_and_thawing = '冻结' where id=@id"; break;
                case "2": sql = "update professionalExam_register set freezing_and_thawing = null where id=@id"; break;
            }
            using (IDbConnection conn = CommHelper.GetSqlConnection())
            {
                return (conn.Execute(sql, new { id = id }) != 0 ? true : false);
            }
        }

        public IEnumerable<dynamic> queryJKZFZTJ(Models.DTO.Criteria model)
        {
            string sql = "select category as categoryName," +
                        "sum(case when area = '监督一科' then 1 else 0 end) as jdyk," +
                        "sum(case when area = '监督二科' then 1 else 0 end) as jdek,sum(case when area = '职业卫生科' then 1 else 0 end) as zywsk," +
                        "sum(case when area = '新湖街道办' then 1 else 0 end) as xhjdb,sum(case when area = '新华街道办' then 1 else 0 end) as xhjdb2," +
                        "sum(case when area = '广川街道办' then 1 else 0 end) as gcjdb,sum(case when area = '天衢街道办' then 1 else 0 end) as thjdb," +
                        "sum(case when area = '黄河涯镇' then 1 else 0 end) as hhyz,sum(case when area = '二屯镇' then 1 else 0 end) as etz," +
                        "sum(case when isnull(area, '') = '' then 1 else 0 end) as wxz from professionalExam_register" +
                        " where procedure_status = '已打印健康证'";                                                                     //--- and physical_num like '1%' 
            if (model.startDate != null && model.startDate != "")
            {
                sql += " and register_date >= '" + model.startDate + "'";
            }
            if (model.endDate != null && model.endDate != "")
            {
                sql += " and register_date <= '" + model.endDate + "'";
            }
            if (model.area_name != null && model.area_name != "")
            {
                sql += " and area ='" + model.area_name + "'";
            }

            if (model.person_type != null && model.person_type != "")
            {
                sql += " and medical_scheme_maintain_id ='" + model.person_type + "'";
            }
            sql += "  group by category";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql);
            }
        }

        public IEnumerable<dynamic> queryJKZFZLJTJ(Models.DTO.Criteria model)
        {
            string sql = " select sum(case when category='公共场所' then 1 else 0 end) as 'category1'," +
                        " sum(case when category = '食品从业' then 1 else 0 end) as 'category2'," +
                        "sum(case when category = '药品从业' then 1 else 0 end) as 'category3'" +
                        "  from professionalExam_register where (procedure_status = '已打印健康证' or other = '是')";                                                                     //(procedure_status = '已打印健康证' or other = '是') 
            if (model.startDate != null && model.startDate != "")
            {
                sql += " and printtimes >= '" + model.startDate + "'";
            }
            if (model.endDate != null && model.endDate != "")
            {
                sql += " and printtimes <= '" + Convert.ToDateTime( model.endDate).AddDays(1).ToString("yyyy-MM-dd") + "'";
            }
            if (model.area_name != null && model.area_name != "")
            {
                sql += " and area ='" + model.area_name + "'";
            }
            if (model.yyjg != null && model.yyjg != "")
            {
                sql += " and jgid ='" + model.yyjg + "'";
            }
            //sql += "  group by area";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql);
            }
        }

        public IEnumerable<ProfessionalExamRegisterModel> getFJLCList(string where, string where1)
        {
            string sql = @"select id,physical_num,register_date,person_name,sex, (case when result_status='复检' then '是' else '否' end )as result_status,exam_type 
                           from professionalExam_register 
                           where 1=1 ";
            sql += " and physical_num is not null and physical_num_old is not null and freezing_and_thawing is null";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " and  physical_num like @where";
            }
            if (!string.IsNullOrEmpty(where1))
            {
                sql += " and  physical_num_old like @where1";
            }
            sql += " order by id desc";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<ProfessionalExamRegisterModel>(sql, new { where = "%" + where + "%", where1 = "%" + where1 + "%", });
            }
        }

        //体检人数统计
        public IEnumerable<dynamic> getTJRSTJList(string register_startdate, string register_enddate, string check_startdate, string check_enddate, string[] register_persons, string area,int jgid)
        {
            string sql = @"select exam_type, count(distinct tb.col1)as col1, isnull(sum(col2),0)as col2,isnull(sum(col3),0)as col3,
isnull(sum(col4),0)as col4,isnull(sum(col5),0)as col5,isnull(sum(col6),0)as col6,isnull(sum(col7),0)as col7,
isnull(sum(col8),0)as col8,isnull(sum(col9),0)as col9,isnull(sum(col10),0)as col10,count(distinct tb.col11)as col11,
isnull(sum(col12),0)as col12,isnull(sum(col13),0)as col13,isnull(sum(col14),0)as col14, isnull(sum(col15),0)as col15 
from ( select exam_type,case when register_type= '正常登记 '  then util_name else null end as col1, case when register_type= '正常登记 '  then 1 else 0 end as col2,
 case when payment= '未缴费 ' and register_type= '正常登记 ' then 1 else 0 end as col3, 
 case when payment= '已缴费 ' and (select count(1)from professionalExam_project_result where person_id=register.id and presenter is not null)=0 then 1 else 0 end as col4,
 case when payment= '已缴费 ' and procedure_status not in( '已录入检查结果 ', '主检医生已审核 ', '已打印健康证 ') then 1 else 0 end as col5,
 case when procedure_status= '已录入检查结果 ' then 1 else 0 end as col6,case when result_status is null then 1 else 0 end as col7,
 case when result_status is not null then 1 else 0 end as col8,case when is_printed is not null then 1 else 0 end as col9,
 case when result_status= '复检 ' then 1 else 0 end as col10,case when register_type= '复检登记 ' then util_name else null end as col11,
 case when register_type= '复检登记 ' then 1 else 0 end as col12,case when physical_num_old is not null and physical_num is null then 1 else 0 end as col13,
 case when register_type= '复检登记 ' and (select count(1)from professionalExam_project_result where person_id=register.id and presenter is null)=0 then 1 else 0 end as col14,
 case when register_type= '复检登记 ' and is_printed is not null then 1 else 0 end as col15 
 from professionalExam_register register where register_date>= @register_startdate and register_date<= @register_enddate and register_type not like '外出登记 '";
            if(jgid!=49)
            {
                sql += " and jgid=" + jgid;
            }
            if (check_startdate != null && check_startdate != "")
            {
                sql += "and check_date>= @check_startdate";
            }
            if (check_enddate != null && check_enddate != "")
            {
                sql += "and check_date<= @check_enddate";
            }
            if (area != null && area != "")
            {
                sql += " and area = @area ";
            }
            if (register_persons.Length > 0)
            {
                sql += "  and register_person in( ";
                int i = 0;
                foreach (var item in register_persons)
                {
                    if (i == 0)
                    {
                        sql += ("'" + item + "'");
                    }
                    else
                    {
                        sql += (",'" + item + "'");
                    }
                    i++;
                }
                sql += " )";
            }

            sql += "  and freezing_and_thawing is null )tb group by exam_type";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { register_startdate = register_startdate, register_enddate = register_enddate, check_startdate = check_startdate, check_enddate = check_enddate, area = area });
            }
        }


        public IEnumerable<ProfessionalExamRegisterModel> GetAlPersonList()
        {
            using (var conn = CommHelper.GetSqlConnection())
            {
                string sql = "select register_person from professionalExam_register where register_person is not null group by  register_person";
                return conn.Query<Models.ProfessionalExamRegisterModel>(sql).ToList();
            }

        }

        public IEnumerable<Models.wzModel> getWZKCList(string key,string lb, string kcid)
        {
            string sql = @"select a.*,b.sl,b.sign from wz a join kc b on a.id=b.wzid where 1=1";

            if (!string.IsNullOrEmpty(key))
            {
                sql += " and a.mc like @key";
            }
            if(!string.IsNullOrEmpty(lb))
            {
                sql += " and a.wzlb=" + lb;
            }
            if (!string.IsNullOrEmpty(kcid))
            {
                sql += " and b.sign=" + kcid;
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<Models.wzModel>(sql, new { key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getXMJFSYTJ(string start, string end, string key)
        {
            string sql = @"select xmmc,convert(varchar(7),tjsj,23) as month,zt,sum(srje) as srje, sum(hfje) as hfje, sum(srje-hfje) as kyje from XMJingFei where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and tjsj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and tjsj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and xmmc like @key";
            }
            sql += " group by xmmc,convert(varchar(7),tjsj,23),zt";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getXMJFSYMX(string start, string end, string key)
        {
            string sql = @"select a.*,b.wzid,b.wzmc,b.sl*b.dj as je from cgsqd a left join cgsqdmx b on a.id=b.sqdid where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and a.sqrq >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and a.sqrq <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and jfly like @key";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getCGSQSL(string start, string end, string key)
        {
            string sql = @"select sqksid,sqks,count(1) as count,sum(cgje) as cgje from cgsqd   where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and sqrq >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and sqrq <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and sqks like @key";
            }
            sql += " group by sqksid,sqks";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<cgsqdModel> getCGSQSL2(string start, string end, string key)
        {
            string sql = @"select * from cgsqd  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and sqrq >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and sqrq <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and sqks = @key";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<cgsqdModel>(sql, new { start = start, end = end, key = key });
            }
        }

        public IEnumerable<dynamic> getCGSQSLYF(string start, string end, string key)
        {
            string sql = @"select convert(varchar(7),sqrq,23) as sqrq,count(1) as count,sum(cgje) as cgje from cgsqd where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and sqrq >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and sqrq <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and sqks like @key";
            }
            sql += " group by convert(varchar(7),sqrq,23)";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getCGSQSLBM(string start, string end, string key)
        {
            string sql = @"select wzid,wzmc,sum(sl) as count,sum(sl*dj) as je from cgsqd a right join cgsqdmx b on a.id=b.sqdid where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and sqrq >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and sqrq <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and sqks like @key";
            }
            sql += " group by wzid,wzmc";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getWZLYQK(string start, string end, string key)
        {
            string sql = @"select ksid,ksmc,count(1) as count,sum(ckje) as ckje from ckd  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksmc like @key";
            }
            sql += " group by ksid,ksmc";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<ckdModel> getWZLYQK2(string start, string end, string key)
        {
            string sql = @"select a.*,b.wzmc,b.sl from ckd a left join ckdmx b on a.id=b.ckdid where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and a.cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and a.cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and a.ksmc = @key";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<ckdModel>(sql, new { start = start, end = end, key = key });
            }
        }

        public IEnumerable<dynamic> getWZLYQKYF(string start, string end, string key)
        {
            string sql = @"select convert(varchar(7), cksj, 23) as cksj,count(1) as count,sum(ckje) as ckje from ckd  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksmc like @key";
            }
            sql += " group by convert(varchar(7), cksj, 23)";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public IEnumerable<dynamic> getWZLYQKBM(string start, string end, string key)
        {
            string sql = @"select wzid, wzmc, sum(sl) as count, sum(sl * dj) as je from ckd a right  join ckdmx b on a.id = b.ckdid where 1 = 1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksmc like @key";
            }
            sql += " group by wzid,wzmc";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%" });
            }
        }

        public List<dynamic> getJCMXB(string start, string end, string key, string key1, string minkcsl)
        {
            string sql = @"select a.*,b.bm,b.ggxh,b.sccj,b.jldw,b.dj,0 as rk,0 as th,0 as ck,0 as tk,0 as pd,0 as bs,sl*dj as kcze,0 as rkze from kc a left join wz b on a.wzid=b.id where sl>" + minkcsl;
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and bm like @key1";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%", key1 = "%" + key1 + "%" }).ToList();
            }
        }
        public IEnumerable<dynamic> getJCMXB2(string start, string end)
        {
            string sql = @"select wzid, sum(sl) as sl, 'rk' as sort from rkdmx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid union
                            select wzid, sum(sl) as sl, 'th' as sort from thdmx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid union
                            select wzid, sum(sl) as sl, 'ck' as sort from ckdmx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid union
                            select wzid, sum(sl) as sl, 'tk' as sort from tkdmx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid union
                            select wzid, sum(sl) as sl, 'pd' as sort from pddmx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid union
                            select wzid, sum(sl) as sl, 'bs' as sort from pdd2mx where 1 = 1 and czsj >= @start and czsj <= @end group by wzid";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end});
            }
        }

        public IEnumerable<dynamic> getRKMXB(string start, string end, string key, string key1, string key2)
        {
            string sql = @"select a.*,b.*,c.bm,c.ggxh,c.sccj,c.jldw,a.dj*a.sl as mxzje from rkdmx a left join rkd b on a.rkdid=b.id left join wz c on a.wzid=c.id where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and rksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and rksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and gysid = @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and wzmc like @key1";
            }
            if (!string.IsNullOrEmpty(key2))
            {
                sql += " and bm like @key2";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = key, key1 = "%" + key1 + "%", key2 = "%" + key2 + "%" });
            }
        }

        public IEnumerable<dynamic> getWZRKLSJL(string start, string end, string key, string key1)
        {
            string sql = @"select a.*,b.bm,b.ggxh,b.sccj,b.jldw from (select rkd.rkdh,rkdmx.wzid,rkdmx.wzmc,rkdmx.dj,sum(rkdmx.sl) as sl,sum(rkdmx.sl*rkdmx.dj) as je  from rkdmx join rkd on rkd.id=rkdmx.rkdid where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and czsj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and czsj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            sql += " group by wzid,wzmc,dj,rkd.rkdh)a left join wz b on a.wzid = b.id where 1 = 1";
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and bm like @key1";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%", key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getWZFFMX(string start, string end, string key, string key1)
        {
            string sql = @"select a.*,b.*,c.bm,c.ggxh,c.sccj,c.jldw,a.dj*a.sl as mxzje from ckdmx a left join ckd b on a.ckdid=b.id left join wz c on a.wzid=c.id where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksid = @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and wzmc like @key1";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = key, key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getWZFFZB(string start, string end, string key, string key1)
        {
            string sql = @"select a.*,b.bm,b.ggxh,b.sccj,b.jldw from (select wzid,wzmc,ksid,ksmc,dj,sum(sl) sl,sum(sl*dj) as ckje from ckdmx a left join ckd b on a.ckdid=b.id where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and cksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksid = @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and wzmc like @key1";
            }
            sql += "  group by wzid,wzmc,ksid,ksmc,dj) a left join wz b on a.wzid = b.id";
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = key, key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getTHMXB(string start, string end, string key, string key1)
        {
            string sql = @" select a.*,b.*,c.bm,c.ggxh,c.sccj,c.jldw,a.dj*a.sl as mxzje from thdmx a left join thd b on a.thdid=b.id left join wz c on a.wzid=c.id  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and thsj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and thsj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and gysid = @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and wzmc like @key1";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = key, key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getTKMXB(string start, string end, string key, string key1)
        {
            string sql = @"  select a.*,b.*,c.bm,c.ggxh,c.sccj,c.jldw,a.dj*a.sl as mxzje from tkdmx a left join tkd b on a.tkdid=b.id left join wz c on a.wzid=c.id  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and tksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and tksj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and ksid = @key";
            }
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and wzmc like @key1";
            }
            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = key, key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getPDMXB(string start, string end, string key, string key1)
        {
            string sql = @"select a.*,b.bm,b.ggxh,b.sccj,b.jldw from (select wzid,wzmc,dj,sum(sl) as sl,sum(sl*dj) as je  from pddmx  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and czsj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and czsj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            sql += " group by wzid,wzmc,dj)a left join wz b on a.wzid = b.id where 1 = 1";
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and bm like @key1";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%", key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getBSMXB(string start, string end, string key, string key1)
        {
            string sql = @"select a.*,b.bm,b.ggxh,b.sccj,b.jldw from (select wzid,wzmc,dj,sum(sl) as sl,sum(sl*dj) as je  from pdd2mx  where 1=1";
            if (!string.IsNullOrEmpty(start))
            {
                sql += " and czsj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and czsj <=@end";
            }
            if (!string.IsNullOrEmpty(key))
            {
                sql += " and wzmc like @key";
            }
            sql += " group by wzid,wzmc,dj)a left join wz b on a.wzid = b.id where 1 = 1";
            if (!string.IsNullOrEmpty(key1))
            {
                sql += " and bm like @key1";
            }

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, key = "%" + key + "%", key1 = "%" + key1 + "%" });
            }
        }

        public IEnumerable<dynamic> getHSMXB(string start, string end, string kcid)
        {
            string sql = @"select ksmc, lbmc, sum(sl*dj*js) as je from (
                          select a.*,b.ksid,b.ksmc,1 as js,c.wzlb,d.lbmc  from ckdmx a left join ckd b on a.ckdid=b.id  left join wz c on a.wzid = c.id left join wzlb d on c.wzlb=d.id where 1=1";

            if (!string.IsNullOrEmpty(start))
            {
                sql += " and b.cksj >=@start";
            }
            if (!string.IsNullOrEmpty(end))
            {
                sql += " and b.cksj <=@end";
            }
            if (!string.IsNullOrEmpty(kcid))
            {
                sql += " and a.sign <=@kcid";
            }
            sql += " )t group by ksmc, lbmc";

            using (var conn = CommHelper.GetSqlConnection())
            {
                return conn.Query(sql, new { start = start, end = end, kcid = kcid });
            }
        }


    }
}