public IHttpActionResult TotalProfitByMovie(StatisticsParameters statisticsParameters)
{
var fltTechTypes = new List <int>();
var CinemaID = statisticsParameters.CinemaID;
var TechTypeID = statisticsParameters.TechTypeID;
DateTime DateTimeFrom = statisticsParameters.DateTimeFrom;
DateTime DateTimeTo = statisticsParameters.DateTimeTo;
fltTechTypes.AddRange((TechTypeID == 0) ? principal.TechnologyTypes.Select(x => x.TechnologyTypeID).ToList() : principal.TechnologyTypes.Where(x => x.TechnologyTypeID == TechTypeID).Select(x => x.TechnologyTypeID).ToList());
var profitByMovie = (from T in principal.Tickets
join P in principal.Projections on T.ProjectionID equals P.ProjectionID
join M in principal.Movies on P.MovieID equals M.MovieID
join TT in principal.TechnologyTypes on P.TechnologyTypeID equals TT.TechnologyTypeID
join CHS in principal.CinemaHalls on P.CinemaHallID equals CHS.CinemaHallID
join CS in principal.Cinemas on CHS.CinemaID equals CS.CinemaID
where P.DateTimeStart >= DateTimeFrom &&
P.DateTimeStart <= DateTimeTo &&
fltTechTypes.Contains(TT.TechnologyTypeID) &&
CS.CinemaID == CinemaID
group T by T.Projections.Movies into Tgroup
orderby Tgroup.Sum(x => x.TicketPrice) descending
select new
{
MovieName = Tgroup.Key.Name,
SumOfTicketPrice = Tgroup.Sum(x => x.TicketPrice)
}).ToDictionary(t => t.MovieName, t => t.SumOfTicketPrice);
if (profitByMovie.Count == 0)
{
return(NotFound());
}
return(Ok(profitByMovie));
}
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);
}
public IHttpActionResult TotalTicketsSold(StatisticsParameters statisticsParameters)
{
var CinemaID = statisticsParameters.CinemaID;
var TechTypeID = statisticsParameters.TechTypeID;
var MovieID = statisticsParameters.MovieID;
var fltMovies = new List <int>();
var fltTechTypes = new List <int>();
fltMovies.AddRange((MovieID == 0) ? principal.Movies.Select(x => x.MovieID).ToList() : principal.Movies.Where(x => x.MovieID == MovieID).Select(x => x.MovieID).ToList());
fltTechTypes.AddRange((TechTypeID == 0) ? principal.TechnologyTypes.Select(x => x.TechnologyTypeID).ToList() : principal.TechnologyTypes.Where(x => x.TechnologyTypeID == TechTypeID).Select(x => x.TechnologyTypeID).ToList());
DateTime DateTimeFrom = statisticsParameters.DateTimeFrom;
DateTime DateTimeTo = statisticsParameters.DateTimeTo;
var countTickets = (from T in principal.Tickets
join P in principal.Projections on T.ProjectionID equals P.ProjectionID
join M in principal.Movies on P.MovieID equals M.MovieID
join TT in principal.TechnologyTypes on P.TechnologyTypeID equals TT.TechnologyTypeID
join CHS in principal.CinemaHalls on P.CinemaHallID equals CHS.CinemaHallID
join CS in principal.Cinemas on CHS.CinemaID equals CS.CinemaID
where P.DateTimeStart >= DateTimeFrom &&
P.DateTimeStart <= DateTimeTo &&
fltMovies.Contains(M.MovieID) &&
fltTechTypes.Contains(TT.TechnologyTypeID) &&
CS.CinemaID == CinemaID
group T by new
{
T.Projections.DateTimeStart.Day,
T.Projections.DateTimeStart.Month,
T.Projections.DateTimeStart.Year
} into Tgroup
orderby Tgroup.Key.Day
select new
{
DayMonth = Tgroup.Key.Day.ToString() + "." + Tgroup.Key.Month.ToString(),
SumOfTicketPrice = Tgroup.Count()
}).ToDictionary(t => t.DayMonth, t => t.SumOfTicketPrice);
if (countTickets.Count == 0)
{
return(NotFound());
}
return(Ok(countTickets));
}
public IHttpActionResult Top10SeatReservationFrequency(StatisticsParameters statisticsParameters)
{
var CinemaID = statisticsParameters.CinemaID;
var TechTypeID = statisticsParameters.TechTypeID;
var MovieID = statisticsParameters.MovieID;
var fltMovies = new List <int>();
var fltTechTypes = new List <int>();
fltMovies.AddRange((MovieID == 0) ? principal.Movies.Select(x => x.MovieID).ToList() : principal.Movies.Where(x => x.MovieID == MovieID).Select(x => x.MovieID).ToList());
fltTechTypes.AddRange((TechTypeID == 0) ? principal.TechnologyTypes.Select(x => x.TechnologyTypeID).ToList() : principal.TechnologyTypes.Where(x => x.TechnologyTypeID == TechTypeID).Select(x => x.TechnologyTypeID).ToList());
DateTime DateTimeFrom = statisticsParameters.DateTimeFrom;
DateTime DateTimeTo = statisticsParameters.DateTimeTo;
var Frequency = (from T in principal.Tickets
join P in principal.Projections on T.ProjectionID equals P.ProjectionID
join M in principal.Movies on P.MovieID equals M.MovieID
join G in principal.Genres on M.GenreID equals G.GenreID
join TT in principal.TechnologyTypes on P.TechnologyTypeID equals TT.TechnologyTypeID
join CHS in principal.CinemaHalls on P.CinemaHallID equals CHS.CinemaHallID
join CS in principal.Cinemas on CHS.CinemaID equals CS.CinemaID
where P.DateTimeStart >= DateTimeFrom &&
P.DateTimeStart <= DateTimeTo &&
fltMovies.Contains(M.MovieID) &&
fltTechTypes.Contains(TT.TechnologyTypeID) &&
CS.CinemaID == CinemaID
group T by T.Seats into SeatGroup
orderby SeatGroup.Count() descending
select new
{
SeatLbl = SeatGroup.Key.CinemaHalls.Name + " - seat " + SeatGroup.Key.SeatRows.SeatRowLbl + SeatGroup.Key.SeatColumns.SeatColumnLbl.ToString(),
SeatFreq = SeatGroup.Count()
}).Take(10).ToDictionary(t => t.SeatLbl, t => t.SeatFreq);
if (Frequency.Count == 0)
{
return(NotFound());
}
return(Ok(Frequency));
}
//统计
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 IHttpActionResult TotalProfitByMovieGenre(StatisticsParameters statisticsParameters)
{
var fltTechTypes = new List <int>();
var CinemaID = statisticsParameters.CinemaID;
var TechTypeID = statisticsParameters.TechTypeID;
var GenreID = statisticsParameters.GenreID;
DateTime DateTimeFrom = statisticsParameters.DateTimeFrom;
DateTime DateTimeTo = statisticsParameters.DateTimeTo;
fltTechTypes.AddRange((TechTypeID == 0) ? principal.TechnologyTypes.Select(x => x.TechnologyTypeID).ToList() : principal.TechnologyTypes.Where(x => x.TechnologyTypeID == TechTypeID).Select(x => x.TechnologyTypeID).ToList());
var profitGenres = (from T in principal.Tickets
join P in principal.Projections on T.ProjectionID equals P.ProjectionID
join M in principal.Movies on P.MovieID equals M.MovieID
join G in principal.Genres on M.GenreID equals G.GenreID
join TT in principal.TechnologyTypes on P.TechnologyTypeID equals TT.TechnologyTypeID
join CHS in principal.CinemaHalls on P.CinemaHallID equals CHS.CinemaHallID
join CS in principal.Cinemas on CHS.CinemaID equals CS.CinemaID
where P.DateTimeStart >= DateTimeFrom &&
P.DateTimeStart <= DateTimeTo &&
fltTechTypes.Contains(TT.TechnologyTypeID) &&
CS.CinemaID == CinemaID &&
G.GenreID == GenreID
group T by new
{
T.Projections.DateTimeStart.Day,
T.Projections.DateTimeStart.Month,
T.Projections.DateTimeStart.Year
} into Tgroup
orderby Tgroup.Key.Day
select new
{
DayMonth = Tgroup.Key.Day.ToString() + "." + Tgroup.Key.Month.ToString() + "." + Tgroup.Key.Year.ToString(),
SumOfTicketPrice = Tgroup.Sum(x => x.TicketPrice)
}).ToDictionary(t => t.DayMonth, t => t.SumOfTicketPrice);
Dictionary <DateTime, decimal> profitFinalConvert = new Dictionary <DateTime, decimal>();
foreach (var item in profitGenres)
{
string[] dayMonthYear = item.Key.Split('.');
string temp = String.Empty;
foreach (var s in dayMonthYear)
{
if (s.Length == 1)
{
temp += "0" + s + ".";
}
else
{
temp += s + ".";
}
}
string Final = temp.Substring(0, 10);
profitFinalConvert.Add(DateTime.ParseExact(Final, "dd.MM.yyyy", CultureInfo.InvariantCulture), item.Value);
}
if (profitFinalConvert.Count == 0)
{
return(NotFound());
}
return(Ok(profitFinalConvert));
}
