1217 lines
57 KiB
C#
1217 lines
57 KiB
C#
|
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 = '合格' th
|
|||
|
|
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 });
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|