/// <summary>
/// 一次向四个统计字典中增加统计项目
/// </summary>
/// <param name="myDic"></param>
/// <param name="DiseaseICD"></param>
/// <param name="patientSex"></param>
/// <param name="patientAgeRange"></param>
public void AddDic(ref Dic myDic, string DiseaseICD, string patientSex, string patientAgeRange)
{
AddDic_AllDisease(DiseaseICD, ref myDic);
AddDic_Sex(DiseaseICD + "，" + patientSex, ref myDic);
AddDic_Age(DiseaseICD + "，" + patientAgeRange, ref myDic);
AddDic_SexAge(DiseaseICD + "，" + patientSex + "，" + patientAgeRange, ref myDic);
}
private void btn_beginProgress_Click(object sender, EventArgs e)
{
if (txt_WorkUnit.Text == "")
{
return;
}
//要统计的条件
StatisticsParameters mnStatisticsParameters = new StatisticsParameters();
mnStatisticsParameters.startDate = datePicker_startDate.Value;
lab_endDate.Text = datePicker_startDate.Value.AddYears(1).ToString();
mnStatisticsParameters.endDate = datePicker_startDate.Value.AddYears(1);
mnStatisticsParameters.workunit = txt_WorkUnit.Text.ToString();
//禁用界面上面所有按钮
btn_beginProgress.Enabled = false;
btn_selectSavePath.Enabled = false;
datePicker_startDate.Enabled = false;
//文件的存储路径
String FilePath = txtbox_FilePath.Text;
Excel.Application myExcel = new Excel.Application();
myExcel.Visible = false;
//存储统计结果
Excel.Workbook myWorkbook = myExcel.Workbooks.Add(true);
Excel.Worksheet myWorkSheet = myWorkbook.Worksheets[1];
//存储主要的疾病的ICD诊断号码的Excel
Excel.Workbook ICDWorkbook = myExcel.Workbooks.Add(true);
Excel.Worksheet ICDWorksheet = ICDWorkbook.Worksheets[1];
//全部疾病诊断列表名称
Dictionary <string, string> Dic_DiseaseList = new Dictionary <string, string>();
DiseaseList myDiseaseList = new DiseaseList();
//读取列表
myDiseaseList.Initialize(AppDomain.CurrentDomain.BaseDirectory + @"Resource\2014ICD2217.xlsx", out Dic_DiseaseList);
//DiseaseList myDiseaseList = new DiseaseList();
//myDiseaseList.Initialize("", out List_Disease);
//所有疾病的查询字典
Dic myDic = new Dic();
//进行统计
processStatistics.Text = "统计开始";
ServiceStatistics myStatistics = new ServiceStatistics();
processStatistics.Text = myStatistics.statistics(ref myDic, mnStatisticsParameters);
//进行输出结果
processOutputExcel.Text = "输出Excel开始";
ServiceOutputExcel myOutputExcel = new ServiceOutputExcel();
processOutputExcel.Text = myOutputExcel.OutputExcel(FilePath, myDic, Dic_DiseaseList);
}
/// <summary>
/// 分年龄和性别分组
/// </summary>
/// <param name="sep_DiseaseSexAge"></param>
/// <param name="myDic"></param>
public void AddDic_SexAge(string sep_DiseaseSexAge, ref Dic myDic)
{
//分年龄和性别的
if (myDic.DiseaseAgeSex.ContainsKey(sep_DiseaseSexAge))
{
//此年龄和性别分组
myDic.DiseaseAgeSex[sep_DiseaseSexAge]++;
}
else
{
myDic.DiseaseAgeSex.Add(sep_DiseaseSexAge, 1);
}
}
/// <summary>
/// 统计所有的疾病
/// </summary>
/// <param name="diagcode"></param>
/// <param name="myDic"></param>
public void AddDic_AllDisease(string diagcode, ref Dic myDic)
{
//统计全体疾病
if (myDic.Disease.ContainsKey(diagcode))
{
//已经遇到此种ICD疾病了
myDic.Disease[diagcode]++;
}
else
{
//第一次统计此种ICD值疾病的数量
myDic.Disease.Add(diagcode, 1);
}
}
/// <summary>
/// 分性别分组
/// </summary>
/// <param name="sep_DiseaseSex"></param>
/// <param name="myDic"></param>
public void AddDic_Sex(string sep_DiseaseSex, ref Dic myDic)
{
//分性别
if (myDic.DiseaseSex.ContainsKey(sep_DiseaseSex))
{
//已经有此性别分类病种
myDic.DiseaseSex[sep_DiseaseSex]++;
}
else
{
//第一次统计此性别疾病
myDic.DiseaseSex.Add(sep_DiseaseSex, 1);
}
}
/// <summary>
/// 分年龄统计疾病发病率
/// </summary>
/// <param name="sep_DiseaseAge"></param>
/// <param name="myDic"></param>
public void AddDic_Age(string sep_DiseaseAge, ref Dic myDic)
{
//分年龄统计的
if (myDic.DiseaseAge.ContainsKey(sep_DiseaseAge))
{
//已经遇到此种ICD疾病了
myDic.DiseaseAge[sep_DiseaseAge]++;
}
else
{
//第一次统计此种ICD值疾病的数量
myDic.DiseaseAge.Add(sep_DiseaseAge, 1);
}
}
public void NotICDAdd(string diagnosisName, ref Dic myDic, string patientSex, string patientAgeRange)
{
//心肌缺血
bool xinjiquexue = false;
string ICDxinjiquexue = "ZNK003";
if (diagnosisName.Contains("心肌缺血"))
{
xinjiquexue = true;
}
//心肌缺血
if (xinjiquexue == true)
{
AddDic(ref myDic, ICDxinjiquexue, patientSex, patientAgeRange);
}
}
//统计
public string statistics(ref Dic myDic, StatisticsParameters mnStatisticsParameters)
{
//全部疾病诊断列表名称
Dictionary <string, string> List_Disease = new Dictionary <string, string>();
DiseaseList myDiseaseList = new DiseaseList();
//读取默认的ICD列表
//myDiseaseList.Initialize(@"c:\C:\Users\win7x64_20150617\Desktop\20150721PatientAnalyse\ICD.xls", out List_Disease);
//实际统计的人数，把年龄为零的人排除在外
myDic.NumAll = 0;
//查询数据库
medbase201511Entities1 myMedBaseEntities = new medbase201511Entities1();
//查询所有的待查询时间段内检查的患者
//查询条件 a0704 任职级别 01 副市级 02 正局级 03 副局级 04 正高 05 副高 14 院士
//查询条件 a6405 在职情况
//全部包括 (s1.a0704 == "01" || s1.a0704 == "02" || s1.a0704 == "03" || s1.a0704 == "04" || s1.a0704 == "05" || s1.a0704 == "14" || s1.a6405 == "02")
//副市级 s1.a0704 == "01"
//正局级 s1.a0704 == "02"
//副局级 s1.a0704 == "03"
//高级知识分子 (s1.a0704 == "04" || s1.a0704 == "05" || s1.a0704 == "14")
//离休 s1.a6405 == "02"
//离休 解决与上面重复问题 (s1.a0704 != "01" && s1.a0704 != "02" && s1.a0704 != "03" && s1.a0704 != "04" && s1.a0704 != "05" && s1.a0704 != "14" && s1.a6405 == "02")
//var ExportResult = from s1 in myMedBaseEntities.hcheckmemb
// where s1.checkdate < mnStatisticsParameters.endDate && s1.checkdate > mnStatisticsParameters.startDate && (s1.a0704 == "04" || s1.a0704 == "05" || s1.a0704 == "14")
// select s1;
string[] lotsWorkUnit = { "0022", "0023", "0024", "0025", "0026", "0027", "0028", "0029", "0030", "0031", "0032", "0033", "0034", "0035", "0036", "0037", "0038", "0039", "0040", "0041", "0042", "0043", "0044", "0045", "0046", "0047", "0048", "0049", "0050", "0051", "0052", "0053", "0054", "0055", "0056", "0057", "0098", "0099", "0100", "0234", "0248", "0317", "0326", "0347", "0720" };
string workUnit = mnStatisticsParameters.workunit;
System.Linq.IQueryable <PatientDataExport.Data.hcheckmemb> ExportResult = from s2 in myMedBaseEntities.hcheckmemb
where s2.b0110 == workUnit && s2.checkdate <mnStatisticsParameters.endDate && s2.checkdate> mnStatisticsParameters.startDate
select s2;
if (ExportResult == null)
{
return("没有查询到相应的患者");
}
//所有的性别分布范围
//string eachPersonSex = "";
//所有的年龄性别分布范围
string eachPersonAgeSexRange = "";
//每位患者的所有疾病
List <string> tempPersonAllDisese = new List <string>();
//遍历所有的患者
foreach (var checkpatient in ExportResult)
{
if (checkpatient.age == null)
{
continue;
}
//此人的年龄范围
string patientAgeRange = AgeSeprate(checkpatient.age.ToString());
//把年龄为零和空白的都排除在外
if (patientAgeRange == "0" || patientAgeRange == "空白")
{
continue;
}
//真正进入统计的人数
myDic.NumAll++;
//区分每个人，使用Checkcode
string eachPerson = checkpatient.checkcode.ToString();
//统计不同年龄范围内的人群
if (myDic.NumAge.ContainsKey(patientAgeRange))
{
//将此年龄范围的人数加1
myDic.NumAge[patientAgeRange]++;
}
else
{
//向统计的词典中增加此年龄范围
myDic.NumAge.Add(patientAgeRange, 1);
}
//每个病人的性别
string patientSex = checkpatient.a0107.ToString();
//统计不同性别的人群
if (myDic.NumSex.ContainsKey(patientSex))
{
//将此性别的人数加1
myDic.NumSex[patientSex]++;
}
else
{
//第一次统计此性别
myDic.NumSex.Add(patientSex, 1);
}
//统计不同性别的年龄分布
eachPersonAgeSexRange = patientSex + "，" + patientAgeRange;
if (myDic.NumSexAge.ContainsKey(eachPersonAgeSexRange))
{
myDic.NumSexAge[eachPersonAgeSexRange]++;
}
else
{
myDic.NumSexAge.Add(eachPersonAgeSexRange, 1);
}
//所有的疾病
try
{
var diseaseResult = from s5 in myMedBaseEntities.hdatadiag where checkpatient.checkcode == s5.checkcode select s5;
//此病人检查无任何诊断
if (diseaseResult == null)
{
//跳过此人的循环
continue;
}
else
{
//此病人有诊断
foreach (var eachDisease in diseaseResult)
{
//存储此人的所有疾病
tempPersonAllDisese.Clear();
//不论有没有确定的ICD值，都要增加总的疾病数量
myDic.DiseaseNum++;
//诊断有确定ICD值，相应的疾病ICD值加1
if (eachDisease.diagcode != null)
{
//一次增加四个统计
AddDic(ref myDic, eachDisease.diagcode, patientSex, patientAgeRange);
//有确定的ICD值，那每个人的ICD确定诊断数目加1
myDic.ICDDiseaseNum++;
}
//诊断没有确定的ICD值
else
{
//没有确定的ICD值，那此人的ICD不确定诊断数目加1
myDic.NotICDDiseaseNum++;
NotICDAdd(eachDisease.diagname, ref myDic, patientSex, patientAgeRange);
}
} //循环每个人的所有疾病结束
} //确定此人有诊断else结束
} //try 查找此人的所有诊断结束
catch
{
System.Windows.Forms.MessageBox.Show("遍历查询到的患者时出现错误");
}
} //Foreach查询到的所有患者循环
return("成功执行统计");
} //结束public string statistics(DateTime startDate, DateTime endDate)
public string OutputExcel(string FilePath, Dic myDic, Dictionary <string, string> List_Disease)
{
Excel.Application mnExcel = new Excel.Application();
mnExcel.Visible = false;
//存储统计结果
Excel.Workbook statisticsBook = mnExcel.Workbooks.Add(System.Reflection.Missing.Value);
statisticsBook.Sheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, 1, System.Reflection.Missing.Value);
Excel.Worksheet SheetDieases = statisticsBook.Worksheets[1];
Excel.Worksheet SheetSexNum = statisticsBook.Worksheets[2];
//输出统计结果
//总疾病的数量
SheetSexNum.Cells[1, 1] = "总疾病的数量";
SheetSexNum.Cells[1, 2] = myDic.DiseaseNum;
//总ICD诊断数量
SheetSexNum.Cells[2, 1] = "总ICD诊断的数量";
SheetSexNum.Cells[2, 2] = myDic.ICDDiseaseNum;
//总非ICD诊断数量
SheetSexNum.Cells[3, 1] = "总非ICD诊断的数量";
SheetSexNum.Cells[3, 2] = myDic.NotICDDiseaseNum;
//输出处理的人数
//输出表头
SheetSexNum.Cells[5, 1] = "统计的人数";
SheetSexNum.Cells[4, 2] = "男";
SheetSexNum.Cells[4, 3] = "女";
SheetSexNum.Cells[4, 4] = "总计";
//男性人数人数
SheetSexNum.Cells[5, 2] = myDic.NumSex["男"];
//女性人数
SheetSexNum.Cells[5, 3] = myDic.NumSex["女"];
//总人数
SheetSexNum.Cells[5, 4] = myDic.NumAll;
SheetSexNum.Cells[6, 1] = "";
//不同年龄段男女数目
//分年龄、性别ICD诊断的数量的标题，简化
string[] GroupAge = { "<30", "30-40", "40-50", "50-60", "60-70", ">=70" };
int temp1 = 0;
foreach (string s1 in GroupAge)
{
SheetSexNum.Cells[6 + temp1, 1] = s1;
if (myDic.NumSexAge.ContainsKey("男，" + s1))
{
SheetSexNum.Cells[6 + temp1, 2] = myDic.NumSexAge["男，" + s1];
}
if (myDic.NumSexAge.ContainsKey("女，" + s1))
{
SheetSexNum.Cells[6 + temp1, 3] = myDic.NumSexAge["女，" + s1];
}
if (myDic.NumAge.ContainsKey(s1))
{
SheetSexNum.Cells[6 + temp1, 4] = myDic.NumAge[s1];
}
temp1++;
}
//各个ICD诊断的数量
//简化ICD表头
//性别字符串集合
string[] GroupSex = { "男", "女" };
//疾病诊断
string[] GroupTitle = { "ICD诊断名称", "ICD诊断编码", "此ICD诊断发病数量" };
int titleRangeAll = 1;
foreach (string preTitleSex in GroupSex)
{
foreach (string titleDisease in GroupTitle)
{
SheetDieases.Cells[6, titleRangeAll++] = preTitleSex + "，" + titleDisease;
}
}
//筛选此范围内前50位的诊断，分男女
//控制横向移动的变量
int countX_Top20all = 0;
foreach (string top20allSex in GroupSex)
{
//控制纵向移动的变量
int countY_Top20all = 0;
try
{
var top20male = (from temptop20all in myDic.DiseaseSex
where temptop20all.Key.Contains(top20allSex)
orderby temptop20all.Value
descending
select temptop20all).Take(100);
if (top20male == null)
{
SheetDieases.Cells[7, 2 + countX_Top20all] = "空白";
SheetDieases.Cells[7, 3 + countX_Top20all] = "空白";
}
else
{
foreach (var eachICDDiseaseNum in top20male)
{
//做出统计
SheetDieases.Cells[7 + countY_Top20all, 1 + countX_Top20all] = DiagnosisName(List_Disease, eachICDDiseaseNum.Key.ToString());
SheetDieases.Cells[7 + countY_Top20all, 2 + countX_Top20all] = eachICDDiseaseNum.Key.ToString();
SheetDieases.Cells[7 + countY_Top20all, 3 + countX_Top20all] = eachICDDiseaseNum.Value.ToString();
//纵向移动
countY_Top20all++;
}
}
}
catch
{
SheetDieases.Cells[7, 2] = "异常";
SheetDieases.Cells[7, 3] = "异常";
}
//横向移动
countX_Top20all = countX_Top20all + 3;
}
//控制表头横向移动的变量
int countX_titleSexAgeDisease = 0;
foreach (string eachAge in GroupAge)
{
foreach (string eachSex in GroupSex)
{
foreach (string title_Disease in GroupTitle)
{
SheetDieases.Cells[6, 7 + countX_titleSexAgeDisease++] = eachSex + "，" + eachAge + "，" + title_Disease;
}
}
}
//控制疾病列表的横向移动
int countX2_SexAgeDisease = 0;
//循环性别
foreach (string eachAge in GroupAge)
{
//循环年龄
foreach (string eachSex in GroupSex)
{
//控制疾病移动的纵向移动,每次循环之内的疾病都清零
int countY2_SexAgeDisease = 0;
try
{
var top20DiseaseOfEachAge = (from tempTop20Disease in myDic.DiseaseAgeSex
where tempTop20Disease.Key.Contains(eachSex + "，" + eachAge)
orderby tempTop20Disease.Value
descending
select tempTop20Disease).Take(50);
//此性别、年龄的患者的统计到疾病为空白
if (top20DiseaseOfEachAge == null)
{
//诊断名称
SheetDieases.Cells[7 + countY2_SexAgeDisease, 7 + countX2_SexAgeDisease] = "空白";
//诊断编码
SheetDieases.Cells[7 + countY2_SexAgeDisease, 8 + countX2_SexAgeDisease] = "空白";
//诊断出现的数量
SheetDieases.Cells[7 + countY2_SexAgeDisease, 9 + countX2_SexAgeDisease] = "空白";
}
else
{
foreach (var eachDiseaseOfTop20DiseaseOfEachAge in top20DiseaseOfEachAge)
{
//通过列表之中查找响应的疾病名称
SheetDieases.Cells[7 + countY2_SexAgeDisease, 7 + countX2_SexAgeDisease] = DiagnosisName(List_Disease, eachDiseaseOfTop20DiseaseOfEachAge.Key.ToString());
//疾病的编码
SheetDieases.Cells[7 + countY2_SexAgeDisease, 8 + countX2_SexAgeDisease] = eachDiseaseOfTop20DiseaseOfEachAge.Key.ToString();
//疾病的数量
SheetDieases.Cells[7 + countY2_SexAgeDisease, 9 + countX2_SexAgeDisease] = eachDiseaseOfTop20DiseaseOfEachAge.Value.ToString();
countY2_SexAgeDisease++;
}
}
}
catch
{
//诊断名称
SheetDieases.Cells[7 + countY2_SexAgeDisease, 7 + countX2_SexAgeDisease] = "查询过程出现异常";
//诊断编码
SheetDieases.Cells[7 + countY2_SexAgeDisease, 8 + countX2_SexAgeDisease] = "查询过程出现异常";
//诊断出现的数量
SheetDieases.Cells[7 + countY2_SexAgeDisease, 9 + countX2_SexAgeDisease] = "查询过程出现异常";
}
//内层循环，每个年龄性别循环结束后，横向移动3个单元格
countX2_SexAgeDisease = countX2_SexAgeDisease + 3;
}
}
//保存文件
statisticsBook.SaveAs(FilePath);
statisticsBook.Close();
mnExcel.Quit();
return("成功输出结果");
} //结束public string OutputExcel(string FilePath, Dic myDic)
