using BatchService.Framework.Utility;

using GemBox.Spreadsheet;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

using PalGain.Core;

using sbcLabSystem.Data.Domain.Account;
using sbcLabSystem.Data.Domain.Backstage;
using sbcLabSystem.Service.Account;

using System;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;

//using NPOI.XWPF.UserModel;

namespace sbcLabSystem.Service.QC
{
    public class QCService
    {
        private AccountService _accountService;
        private IRespository<QCDistribution> _qcDistributions;
        private IRespository<QCDistributionRegisterInfo> _qcDistributionRegisters;
        private IRespository<StandAnswerInfo> _standAnswerInfo;
        private IRespository<ApprovalInfo> _approvalInfos;
        private IRespository<Resultspercentage> _Resultspercentage;
        public QCService(IRespository<QCDistribution> i_qcDistributions,
            IRespository<QCDistributionRegisterInfo> i_qcDistributionRegisters,
            IRespository<StandAnswerInfo> i_standAnswerInfo,
            IRespository<ApprovalInfo> i_approvalInfos,
            IRespository<Resultspercentage> i_Resultspercentage,
            AccountService i_accountService)
        {
            _Resultspercentage = i_Resultspercentage;
            _approvalInfos = i_approvalInfos;
            _qcDistributions = i_qcDistributions;
            _qcDistributionRegisters = i_qcDistributionRegisters;
            _accountService = i_accountService;
            _standAnswerInfo = i_standAnswerInfo;
        }
        public Resultspercentage GetResultSpercentage(int QCid)
        {
            return _Resultspercentage.GetById(QCid);
        }
        public void AddResultspercentage(Resultspercentage info)
        {
            _Resultspercentage.Insert(info);
        }
        public IQueryable<Resultspercentage> GetResultSpercentages()
        {
            return _Resultspercentage.Table;
        }
        public void UpdateResultspercentage(Resultspercentage info)
        {
            _Resultspercentage.Update(info);
        }

        public ExcelFile ExportPDF(string excelPath, QCDistributionRegisterInfo regInfo, string scores)
        {
            // If using Professional version, put your serial key below.
            SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
            FileInfo excelFileInfo = new FileInfo(excelPath);
            string pdfFilePath = Path.ChangeExtension(excelPath, "pdf");
            ExcelFile ef = new ExcelFile();
            using (FileStream fs = new FileStream(excelPath, FileMode.Open))
            {
                ef = ExcelFile.Load(fs, LoadOptions.XlsxDefault);
            }
            ExcelWorksheet ws = ef.Worksheets["报表1"];
            var scoresObject = JsonConvert.DeserializeObject(scores) as JArray;
            var approvals = this.GetApprovals().Where(p => p.QCDistributionID == regInfo.QCDistributionInfo.Id);
            var allRegisters = this.GetQcDistributionRegisters().
                Where(p => p.QCDistributionInfo.Id == regInfo.QCDistributionInfo.Id).ToList();
            var DBStatistics = this.GetResultSpercentages().Where(p => p.QCDistributionId == regInfo.QCDistributionInfo.Id).ToList();

            #region Page1
            ws.Cells["C6"].Value = regInfo.LabInfo.LabCode;
            ws.Cells["C8"].Value = regInfo.LabInfo.ManagerName;
            ws.Cells["C10"].Value = regInfo.LabInfo.CompanyName;
            ws.Cells["C14"].Value = regInfo.LabInfo.Address;
            ws.Cells["C18"].Value = regInfo.LabInfo.PostCode;
            ws.Cells["C21"].Value = regInfo.ProjectId;

            var ABO1 = scoresObject[0];
            var ABO2 = scoresObject[2];
            var ABO3 = scoresObject[4];

            var RH1 = scoresObject[1];
            var RH2 = scoresObject[3];
            var RH3 = scoresObject[5];

            var Identity1 = scoresObject[9];
            var Identity2 = scoresObject[10];
            var Identity3 = scoresObject[11];

            var Filter1 = scoresObject[6];
            var Filter2 = scoresObject[7];
            var Filter3 = scoresObject[8];

            var Patient1X = scoresObject[12];
            var Patient1Y = scoresObject[13];
            var Patient1Z = scoresObject[14];

            var Patient2X = scoresObject[15];
            var Patient2Y = scoresObject[16];
            var Patient2Z = scoresObject[17];

            var Patient3X = scoresObject[18];
            var Patient3Y = scoresObject[19];
            var Patient3Z = scoresObject[20];

            var ZhiKang1 = scoresObject[21];
            var ZhiKang2 = scoresObject[22];
            var ZhiKang3 = scoresObject[23];

            var Part6_W_ABO = scoresObject[24];
            var Part6_W_KangYuanFenXing = scoresObject[25];
            var Part6_W_RhD = scoresObject[26];
            var Part6_Y_ABO = scoresObject[27];
            var Part6_Y_KangYuanFenXing = scoresObject[28];
            var Part6_Y_RhD = scoresObject[29];
            var Part6_Z_ABO = scoresObject[30];
            var Part6_Z_KangYuanFenXing = scoresObject[31];
            var Part6_Z_RhD = scoresObject[32];

            ws.Cells["L11"].Value = Part6_W_ABO["StandValue"].ToString();
            ws.Cells["M11"].Value = Part6_W_RhD["StandValue"].ToString();
            ws.Cells["N11"].Value = Part6_W_KangYuanFenXing["StandValue"].ToString();

            ws.Cells["L12"].Value = Part6_Y_ABO["StandValue"].ToString();
            ws.Cells["M12"].Value = Part6_Y_RhD["StandValue"].ToString();

            ws.Cells["N12"].Value = Part6_Y_KangYuanFenXing["StandValue"].ToString();

            ws.Cells["L13"].Value = Part6_Z_ABO["StandValue"].ToString();
            ws.Cells["M13"].Value = Part6_Z_RhD["StandValue"].ToString();
            ws.Cells["N13"].Value = Part6_Z_KangYuanFenXing["StandValue"].ToString();

            #endregion
            #region Page2
            //ABO
            ws.Cells["L6"].Value = ABO1["StandValue"].ToString();
            ws.Cells["L7"].Value = ABO2["StandValue"].ToString();
            ws.Cells["L8"].Value = ABO3["StandValue"].ToString();
            //RH
            ws.Cells["M6"].Value = RH1["StandValue"].ToString();
            ws.Cells["M7"].Value = RH2["StandValue"].ToString();
            ws.Cells["M8"].Value = RH3["StandValue"].ToString();
            //Identity
            ws.Cells["N6"].Value = Identity1["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity1["StandValue"].ToString();
            ws.Cells["N7"].Value = Identity2["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity2["StandValue"].ToString();
            ws.Cells["N8"].Value = Identity3["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity3["StandValue"].ToString();
            //标准答案 
            ws.Cells["L27"].Value = Filter1["Score"].ToString();
            ws.Cells["L28"].Value = Filter2["Score"].ToString();
            ws.Cells["L29"].Value = Filter3["Score"].ToString();
            ws.Cells["M27"].Value = Identity1["Score"].ToString();
            ws.Cells["M28"].Value = Identity2["Score"].ToString();
            ws.Cells["M29"].Value = Identity3["Score"].ToString();
            ws.Cells["N27"].Value = Patient1X["Score"].ToString();
            ws.Cells["N28"].Value = Patient1Y["Score"].ToString();
            ws.Cells["N29"].Value = Patient1Z["Score"].ToString();
            ws.Cells["O27"].Value = Patient2X["Score"].ToString();
            ws.Cells["O28"].Value = Patient2Y["Score"].ToString();
            ws.Cells["O29"].Value = Patient2Z["Score"].ToString();
            ws.Cells["P27"].Value = Patient3X["Score"].ToString();
            ws.Cells["P28"].Value = Patient3Y["Score"].ToString();
            ws.Cells["P29"].Value = Patient3Z["Score"].ToString();
            ws.Cells["Q27"].Value = ZhiKang1["Score"].ToString();
            ws.Cells["Q28"].Value = ZhiKang2["Score"].ToString();
            ws.Cells["Q29"].Value = ZhiKang3["Score"].ToString();
            #endregion

            #region Page3
            ws.Cells["V5"].Value = ws.Cells["L6"].Value;
            ws.Cells["W11"].Value = ws.Cells["L6"].Value;
            ws.Cells["V6"].Value = ws.Cells["L7"].Value;
            ws.Cells["W15"].Value = ws.Cells["L7"].Value;
            ws.Cells["V7"].Value = ws.Cells["L8"].Value;
            ws.Cells["W19"].Value = ws.Cells["L8"].Value;

            ws.Cells["W5"].Value = ws.Cells["M6"].Value;
            ws.Cells["X11"].Value = ws.Cells["M6"].Value;
            ws.Cells["W6"].Value = ws.Cells["M7"].Value;
            ws.Cells["X15"].Value = ws.Cells["M7"].Value;
            ws.Cells["W7"].Value = ws.Cells["M8"].Value;
            ws.Cells["X19"].Value = ws.Cells["M8"].Value;

            ws.Cells["X5"].Value = ws.Cells["Q27"].Value;
            ws.Cells["X6"].Value = ws.Cells["Q28"].Value;
            ws.Cells["X7"].Value = ws.Cells["Q29"].Value;

            ws.Cells["W10"].Value = ABO1["RealValue"].ToString();
            ws.Cells["W14"].Value = ABO2["RealValue"].ToString();
            ws.Cells["W18"].Value = ABO3["RealValue"].ToString();

            ws.Cells["X10"].Value = RH1["RealValue"].ToString();
            ws.Cells["X14"].Value = RH2["RealValue"].ToString();
            ws.Cells["X18"].Value = RH3["RealValue"].ToString();

            double Page3_1 = 0;
            double Page3_2 = 0;
            double Page3_3 = 0;
            double temp1 = 0;
            double temp2 = 0;
            int errors_ABO = 0;
            int errors_RH = 0;

            errors_ABO += ABO1["RealScore"].ToString() == "0" ? 0 : 1 +
                ABO2["RealScore"].ToString() == "0" ? 0 : 1 +
                ABO3["RealScore"].ToString() == "0" ? 0 : 1;
            errors_RH += RH1["RealScore"].ToString() == "0" ? 0 : 1 +
                RH1["RealScore"].ToString() == "0" ? 0 : 1 +
                RH1["RealScore"].ToString() == "0" ? 0 : 1;
            ws.Cells["M18"].Value = errors_ABO;
            ws.Cells["M19"].Value = errors_RH;

            ws.Cells["N18"].Value = ABO1["Score"].ToString();
            ws.Cells["N19"].Value = RH1["Score"].ToString();

            ws.Cells["O18"].Value = int.Parse(ABO1["Score"].ToString()) * errors_ABO;
            ws.Cells["O19"].Value = int.Parse(RH1["Score"].ToString()) * errors_RH;

            double.TryParse(ABO1["RealScore"].ToString(), out temp1);
            double.TryParse(RH1["RealScore"].ToString(), out temp2);
            Page3_1 = temp1 + temp2;
            ws.Cells["AA10"].Value = Page3_1;

            double.TryParse(ABO2["RealScore"].ToString(), out temp1);
            double.TryParse(RH2["RealScore"].ToString(), out temp2);
            Page3_2 = temp1 + temp2;
            ws.Cells["AA14"].Value = Page3_2;

            double.TryParse(ABO3["RealScore"].ToString(), out temp1);
            double.TryParse(RH3["RealScore"].ToString(), out temp2);
            Page3_3 = temp1 + temp2;
            ws.Cells["AA18"].Value = Page3_3;

            ws.Cells["Y21"].Value = double.Parse(ABO1["RealScore"].ToString()) +
                double.Parse(ABO2["RealScore"].ToString()) +
                double.Parse(ABO3["RealScore"].ToString());
            ws.Cells["Y22"].Value = double.Parse(RH1["RealScore"].ToString()) +
                double.Parse(RH2["RealScore"].ToString()) +
                double.Parse(RH3["RealScore"].ToString());

            //统计ABO/RH
            var allABORH = allRegisters.ToList();
            ws.Cells["X24"].Value = allABORH.Count();
            #endregion

            #region page4
            ws.Cells["AF5"].Value = ws.Cells["N6"].Value;
            ws.Cells["AF6"].Value = ws.Cells["N7"].Value;
            ws.Cells["AF7"].Value = ws.Cells["N8"].Value;

            //抗体筛选
            ws.Cells["AF11"].Value = Filter1["RealValue"].ToString();
            ws.Cells["AF12"].Value = Filter1["StandValue"].ToString();
            ws.Cells["AH11"].Value = Filter1["RealScore"].ToString();

            ws.Cells["AF15"].Value = Filter2["RealValue"].ToString();
            ws.Cells["AF16"].Value = Filter2["StandValue"].ToString();
            ws.Cells["AH15"].Value = Filter2["RealScore"].ToString();

            ws.Cells["AF19"].Value = Filter3["RealValue"].ToString();
            ws.Cells["AF20"].Value = Filter3["StandValue"].ToString();
            ws.Cells["AH19"].Value = Filter3["RealScore"].ToString();
            double score_Filter = double.Parse(ws.Cells["AH11"].Value.ToString())
                + double.Parse(ws.Cells["AH15"].Value.ToString())
                + double.Parse(ws.Cells["AH19"].Value.ToString());

            int errors_Filter = 0;

            errors_Filter += Filter1["RealScore"].ToString() == "0" ? 0 : 1 +
                Filter2["RealScore"].ToString() == "0" ? 0 : 1 +
                Filter3["RealScore"].ToString() == "0" ? 0 : 1;
            ws.Cells["M20"].Value = errors_Filter;
            ws.Cells["O20"].Value = score_Filter;

            //抗体鉴定 
            ws.Cells["AI11"].Value = Identity1["RealValue"].ToString() == "" ? "未检出" : "检出抗" + Identity1["RealValue"].ToString();
            ws.Cells["AI12"].Value = Identity1["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity1["StandValue"].ToString();
            ws.Cells["AK11"].Value = Identity1["RealScore"].ToString();

            ws.Cells["AI15"].Value = Identity2["RealValue"].ToString() == "" ? "未检出" : "检出抗" + Identity2["RealValue"].ToString();
            ws.Cells["AI16"].Value = Identity2["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity2["StandValue"].ToString();
            ws.Cells["AK15"].Value = Identity2["RealScore"].ToString();

            ws.Cells["AI19"].Value = Identity3["RealValue"].ToString() == "" ? "未检出" : "检出抗" + Identity3["RealValue"].ToString();
            ws.Cells["AI20"].Value = Identity3["StandValue"].ToString() == "" ? "未检出" : "检出抗" + Identity3["StandValue"].ToString();
            ws.Cells["AK19"].Value = Identity3["RealScore"].ToString();

            if (regInfo.ProjectId == 2 || regInfo.ProjectId == 3 || regInfo.ProjectId == 4)
            {
                ws.Cells["AI11"].Value = "";
                ws.Cells["AI12"].Value = "";
                ws.Cells["AI15"].Value = "";
                ws.Cells["AI16"].Value = "";
                ws.Cells["AI19"].Value = "";
                ws.Cells["AI20"].Value = "";
            }

            double score_Identity = double.Parse(ws.Cells["AK11"].Value.ToString())
                + double.Parse(ws.Cells["AK15"].Value.ToString())
                + double.Parse(ws.Cells["AK19"].Value.ToString());
            ws.Cells["AJ22"].Value = score_Filter;
            ws.Cells["AJ23"].Value = score_Identity;

            int errors_Identity = 0;

            errors_Identity += Identity1["RealScore"].ToString() == "0" ? 0 : 1 +
                Identity2["RealScore"].ToString() == "0" ? 0 : 1 +
                Identity3["RealScore"].ToString() == "0" ? 0 : 1;
            ws.Cells["M21"].Value = errors_Identity;
            ws.Cells["O21"].Value = score_Identity;
            #endregion

            #region Page5
            ws.Cells["AP5"].Value = Patient1X["StandValue"].ToString();
            ws.Cells["AP11"].Value = Patient1X["RealValue"].ToString();
            ws.Cells["AP12"].Value = Patient1X["StandValue"].ToString();

            ws.Cells["AQ5"].Value = Patient1Y["StandValue"].ToString();
            ws.Cells["AQ11"].Value = Patient1Y["RealValue"].ToString();
            ws.Cells["AQ12"].Value = Patient1Y["StandValue"].ToString();

            ws.Cells["AR5"].Value = Patient1Z["StandValue"].ToString();
            ws.Cells["AR11"].Value = Patient1Z["RealValue"].ToString();
            ws.Cells["AR12"].Value = Patient1Z["StandValue"].ToString();

            ws.Cells["AP6"].Value = Patient2X["StandValue"].ToString();
            ws.Cells["AP15"].Value = Patient2X["RealValue"].ToString();
            ws.Cells["AP16"].Value = Patient2X["StandValue"].ToString();

            ws.Cells["AQ6"].Value = Patient2Y["StandValue"].ToString();
            ws.Cells["AQ15"].Value = Patient2Y["RealValue"].ToString();
            ws.Cells["AQ16"].Value = Patient2Y["StandValue"].ToString();

            ws.Cells["AR6"].Value = Patient2Z["StandValue"].ToString();
            ws.Cells["AR15"].Value = Patient2Z["RealValue"].ToString();
            ws.Cells["AR16"].Value = Patient2Z["StandValue"].ToString();

            ws.Cells["AP7"].Value = Patient3X["StandValue"].ToString();
            ws.Cells["AP19"].Value = Patient3X["RealValue"].ToString();
            ws.Cells["AP20"].Value = Patient3X["StandValue"].ToString();

            ws.Cells["AQ7"].Value = Patient3Y["StandValue"].ToString();
            ws.Cells["AQ19"].Value = Patient3Y["RealValue"].ToString();
            ws.Cells["AQ20"].Value = Patient3Y["StandValue"].ToString();

            ws.Cells["AR7"].Value = Patient3Z["StandValue"].ToString();
            ws.Cells["AR19"].Value = Patient3Z["RealValue"].ToString();
            ws.Cells["AR20"].Value = Patient3Z["StandValue"].ToString();

            ws.Cells["AU11"].Value = double.Parse(Patient1X["RealScore"].ToString()) +
                double.Parse(Patient1Y["RealScore"].ToString()) +
                double.Parse(Patient1Z["RealScore"].ToString());

            ws.Cells["AU15"].Value = double.Parse(Patient2X["RealScore"].ToString()) +
                double.Parse(Patient2Y["RealScore"].ToString()) +
                double.Parse(Patient2Z["RealScore"].ToString());

            ws.Cells["AU19"].Value = double.Parse(Patient3X["RealScore"].ToString()) +
                double.Parse(Patient3Y["RealScore"].ToString()) +
                double.Parse(Patient3Z["RealScore"].ToString());

            ws.Cells["AS22"].Value = double.Parse(ws.Cells["AU11"].Value.ToString()) +
                double.Parse(ws.Cells["AU15"].Value.ToString()) +
                double.Parse(ws.Cells["AU19"].Value.ToString());

            int errors_JiaoCha = 0;

            errors_JiaoCha += Patient1X["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient1Y["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient1Z["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient2X["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient2Y["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient2Z["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient3X["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient3Y["RealScore"].ToString() == "0" ? 0 : 1 +
                Patient3Z["RealScore"].ToString() == "0" ? 0 : 1;
            ws.Cells["M22"].Value = errors_JiaoCha;
            ws.Cells["O22"].Value = ws.Cells["AS22"].Value;

            ws.Cells["M24"].Value = errors_ABO + errors_Filter + errors_Identity + errors_JiaoCha + errors_RH;
            ws.Cells["O24"].Value = double.Parse(ws.Cells["O18"].Value.ToString()) +
                double.Parse(ws.Cells["O19"].Value.ToString()) +
                double.Parse(ws.Cells["O20"].Value.ToString()) +
                double.Parse(ws.Cells["O21"].Value.ToString()) +
                double.Parse(ws.Cells["O22"].Value.ToString()) +
                double.Parse(ws.Cells["O23"].Value.ToString());
            #endregion

            #region 统计

            var stats_ABO1 = DBStatistics.Select(p => p.Abo1).Distinct().ToList();

            string cellNumber = "U";
            int cellRowNumber = 27;
            stats_ABO1.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                LogHelper.Info("stat:" + stat);
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.Abo1 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "V", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            var stats_ABO2 = DBStatistics.Select(p => p.Abo2).Distinct().ToList();
            cellNumber = "W";
            cellRowNumber = 27;
            stats_ABO2.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.Abo2 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "X", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            var stats_ABO3 = DBStatistics.Select(p => p.Abo3).Distinct().ToList();
            cellNumber = "Y";
            cellRowNumber = 27;
            stats_ABO3.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.Abo3 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "Z", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            var stats_RH1 = DBStatistics.Select(p => p.RH1).Distinct().ToList();
            cellNumber = "U";
            cellRowNumber = 32;
            stats_RH1.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.RH1 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "V", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            var stats_RH2 = DBStatistics.Select(p => p.RH2).Distinct().ToList();
            cellNumber = "W";
            cellRowNumber = 32;
            stats_RH2.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.RH2 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "X", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            var stats_RH3 = DBStatistics.Select(p => p.RH3).Distinct().ToList();
            cellNumber = "Y";
            cellRowNumber = 32;
            stats_RH3.ForEach(stat =>
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = stat;
                if (stat == "")
                {
                    ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = "未填写";
                }
                var rowcount = DBStatistics.Count(p => p.RH3 == stat);
                var percent = (double)rowcount / (double)allRegisters.Count();
                ws.Cells[string.Format("{0}{1}", "Z", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });
            //抗体筛选
            string displayValue = "";
            string standValue = "";
            string othersValue = "";

            var labs = allRegisters.Where(p => p.ProjectId == 1 || p.ProjectId == 2 || p.ProjectId == 4)
                .Select(p => p.Id).ToList();
            var stats_temp = DBStatistics.Where(p => labs.Contains(p.QcDistributionRegisterId)).ToList();
            ws.Cells["AH24"].Value = labs.Count;
            cellNumber = "AD";
            cellRowNumber = 26;
            stats_temp.Select(p => p.KangTiFilter1).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiFilter1 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            displayValue = (standValue + displayValue);
            if (!string.IsNullOrEmpty(displayValue))
            {
                displayValue = displayValue.Substring(0, displayValue.Length - 2);
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            standValue = "";
            displayValue = "";

            cellNumber = "AG";
            cellRowNumber = 26;
            stats_temp.Select(p => p.KangTiFilter2).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiFilter2 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            displayValue = (standValue + displayValue);

            if (!string.IsNullOrEmpty(displayValue))
            {
                displayValue = displayValue.Substring(0, displayValue.Length - 2);
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            standValue = "";
            displayValue = "";

            cellNumber = "AJ";
            cellRowNumber = 26;
            stats_temp.Select(p => p.KangTiFilter3).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiFilter3 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            displayValue = (standValue + displayValue);
            if (!string.IsNullOrEmpty(displayValue))
            {
                displayValue = displayValue.Substring(0, displayValue.Length - 2);
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            standValue = "";
            displayValue = "";

            //抗体鉴定

            labs = allRegisters.Where(p => p.ProjectId == 1).Select(p => p.Id).ToList();
            stats_temp = DBStatistics.Where(p => labs.Contains(p.QcDistributionRegisterId)).ToList();

            ws.Cells["AK24"].Value = labs.Count;
            cellNumber = "AD";
            cellRowNumber = 29;
            stats_temp.Select(p => p.KangTiIdentity1).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiIdentity1 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (Identity1["StandValue"].ToString() == stat)
                {
                    standValue = string.Format("{0} {1}; ", statText, percentText);
                }
                else if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            //displayValue = (standValue + displayValue);
            if (!string.IsNullOrEmpty(displayValue) || !string.IsNullOrEmpty(standValue))
            {
                if (!string.IsNullOrEmpty(displayValue))
                {
                    displayValue = displayValue.Substring(0, displayValue.Length - 2);
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            standValue = "";
            displayValue = "";

            cellNumber = "AG";
            cellRowNumber = 29;
            stats_temp.Select(p => p.KangTiIdentity2).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiIdentity2 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (Identity2["StandValue"].ToString() == stat)
                {
                    standValue = string.Format("{0} {1}; ", statText, percentText);
                }
                else if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            //displayValue = (standValue + displayValue);
            if (!string.IsNullOrEmpty(displayValue))
            {
                displayValue = displayValue.Substring(0, displayValue.Length - 2);
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            else
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue;
            }
            standValue = "";
            displayValue = "";

            cellNumber = "AJ";
            cellRowNumber = 29;
            stats_temp.Select(p => p.KangTiIdentity3).Distinct().ToList().ForEach(stat =>
            {
                var rowcount = stats_temp.Count(p => p.KangTiIdentity3 == stat);
                var percent = (double)rowcount / (double)labs.Count;
                var percentText = string.Format("{0}%", Math.Round(percent * 100, 2));
                var statText = stat == "" ? "未填写" : stat;
                if (Identity3["StandValue"].ToString() == stat)
                {
                    standValue = string.Format("{0} {1}; ", statText, percentText);
                }
                else if (statText == "未填写")
                {
                    displayValue += string.Format("{0} {1}; ", percentText, statText);
                }
                else
                {
                    displayValue += string.Format("{0} {1}; ", statText, percentText);
                }
            });
            //displayValue = (standValue + displayValue);
            if (!string.IsNullOrEmpty(displayValue))
            {
                displayValue = displayValue.Substring(0, displayValue.Length - 2);
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue + displayValue;
            }
            else
            {
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = standValue;
            }
            standValue = "";
            displayValue = "";

            //交叉配血
            labs = allRegisters.Where(p => p.ProjectId == 1 || p.ProjectId == 2).Select(p => p.Id).ToList();
            stats_temp = DBStatistics.Where(p => labs.Contains(p.QcDistributionRegisterId)).ToList();
            ws.Cells["AQ24"].Value = labs.Count;
            cellNumber = "AO";
            cellRowNumber = 26;
            stats_temp.Select(p => p.JiaoChaPeiXing1W).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing1W == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AP", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AQ";
            cellRowNumber = 26;
            stats_temp.Select(p => p.JiaoChaPeiXing2W).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写"; 
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing2W == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AR", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AS"; 
            cellRowNumber = 26;
            stats_temp.Select(p => p.JiaoChaPeiXing3W).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing3W == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AT", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AO";
            cellRowNumber = 29;
            stats_temp.Select(p => p.JiaoChaPeiXing1Y).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing1Y == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AP", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AQ";
            cellRowNumber = 29;
            stats_temp.Select(p => p.JiaoChaPeiXing2Y).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing2Y == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AR", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AS";
            cellRowNumber = 29;
            stats_temp.Select(p => p.JiaoChaPeiXing3Y).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing3Y == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AT", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AO";
            cellRowNumber = 32;
            stats_temp.Select(p => p.JiaoChaPeiXing1Z).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing1Z == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AP", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AQ";
            cellRowNumber = 32;
            stats_temp.Select(p => p.JiaoChaPeiXing2Z).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing2Z == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AR", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            });

            cellNumber = "AS";
            cellRowNumber = 32;
            stats_temp.Select(p => p.JiaoChaPeiXing3Z).Distinct().ToList().ForEach(stat =>
            {
                displayValue = stat;
                if (stat.IsNullOrEmpty())
                {
                    displayValue = "未填写";
                }
                ws.Cells[string.Format("{0}{1}", cellNumber, cellRowNumber)].Value = displayValue;
                var rowcount = stats_temp.Count(p => p.JiaoChaPeiXing3Z == stat);
                var percent = (double)rowcount / (double)labs.Count;
                ws.Cells[string.Format("{0}{1}", "AT", cellRowNumber)].Value = Math.Round(percent, 4);
                cellRowNumber++;
            }); 
            #endregion

            #region 计算Z值

            ZService zService = new ZService(regInfo.ProjectId, this);

            zService.CalcZ_Value(allRegisters, DBStatistics);

            var percentTemp = DBStatistics.FirstOrDefault(p => p.QcDistributionRegisterId == regInfo.Id);

            var zValue = Math.Round(percentTemp.ZValueScore, 2);
            ws.Cells["L32"].Value = zValue;
            if (zValue <= 2)
            {
                ws.Cells["B27"].Value = "您实验室本次血型室间质控结果 满意";
            }
            else if (zValue < 3)
            {
                ws.Cells["B27"].Value = "您实验室本次血型室间质控结果 有待改进";
            }
            else
            {
                ws.Cells["B27"].Value = "您实验室本次血型室间质控结果 不满意";
            }

            labs = allRegisters.Where(p => p.ProjectId == regInfo.ProjectId).Select(p => p.Id).ToList();
            stats_temp = DBStatistics.Where(p => labs.Contains(p.QcDistributionRegisterId)).ToList();

            var Project_SatisfiedScore1 = stats_temp.Count(p => p.SatisfiedScore == 0);
            var Project_SatisfiedScore2 = stats_temp.Count(p => p.SatisfiedScore == 1);
            var Project_SatisfiedScore3 = stats_temp.Count(p => p.SatisfiedScore == 2);

            ws.Cells["K34"].Value = Math.Round((double)Project_SatisfiedScore1 / (double)labs.Count, 2);
            ws.Cells["K35"].Value = Math.Round((double)Project_SatisfiedScore2 / (double)labs.Count, 2);
            ws.Cells["K36"].Value = Math.Round((double)Project_SatisfiedScore3 / (double)labs.Count, 2);

            #endregion



            // In order to achieve the conversion of a loaded Excel file to PDF,
            // or to some other Excel format,
            // we just need to save an ExcelFile object to desired output file format.

            if (regInfo.ProjectId == 3 || regInfo.ProjectId == 4)
            {
                // Set print area
                ws.Columns.Remove(37, 15);
                ws.PrintOptions.FitWorksheetWidthToPages = 4;
                ws.PrintOptions.FitToPage = true;
            }
            else
            {
                ws.PrintOptions.FitWorksheetWidthToPages = 5;
            }
            //ef.Save(pdfFilePath); 

            return ef;

            //return new FileInfo(pdfFilePath).FullName;
        }

        public void CalcScore(QCDistributionRegisterInfo regInfo)
        {

        }
        public void SendMail(Menus ent, string cc, string body)
        {
            StringOperation service = new StringOperation();
            EmileInfo model = new EmileInfo();
            model.Boody = body;
            model.EmileName = cc;
            model.Title = ent.Name;
            model.Type = ent.Id;
            model.Explain = ent.Title;
            model.SendOutDatetime = DateTime.Now;
            _accountService.InserEmilinfo(model);
        }
        public void SendMail(UserRequestInfo labInfo, QCDistributionRegisterInfo regInfo, int mailTemplateId)
        {
            Menus ent = _accountService.MenusId(mailTemplateId);
            StringOperation service = new StringOperation();
            string body = service.StringContents(labInfo, regInfo, ent.Contents);
            string cc = "";
            if (service.Contains(ent.Url, "管理员"))
            {
                cc = "eqas@sbc.org.cn";
            }
            if (service.Contains(ent.Url, "操作人"))
            {
                cc = labInfo.ManagerEmail;
            }
            if (service.Contains(ent.Url, "负责人"))
            {
                cc = labInfo.OperatorEmail;
            }
            SendMail(ent, cc, body);
        }
        public void SendMail(int LabId, int mailTemplateId)
        {

            SendMail(_accountService.GetAllUserRequestInfoQueryable().FirstOrDefault(p => p.Id == LabId)
                , null
                , mailTemplateId);
        }
        public IQueryable<ApprovalInfo> GetApprovals()
        {
            return _approvalInfos.Table;
        }
        public void SaveApproval(ApprovalInfo entity)
        {
            if (entity.Id == 0)
            {
                _approvalInfos.Insert(entity);
            }
            else
            {
                _approvalInfos.Update(entity);
            }
        }
        public void DeleteApproval(ApprovalInfo entity)
        {
            if (entity.Id > 0)
            {
                _approvalInfos.Delete(entity);
            }
        }
        public IQueryable<StandAnswerInfo> GetStandAnswers()
        {
            return _standAnswerInfo.Table;
        }
        public IQueryable<QCDistribution> GetQcDistributions()
        {
            return _qcDistributions.Table;
        }
        public void SaveQcDistribution(QCDistribution qcDistribution)
        {
            if (qcDistribution.Id == 0)
            {
                _qcDistributions.Insert(qcDistribution);
            }
            else
            {
                _qcDistributions.Update(qcDistribution);
            }
        }
        public QCDistribution GetId(int id)
        {
            return _qcDistributions.GetById(id);
        }
        public void DeleteQcDistribution(QCDistribution qcDistribution)
        {
            if (qcDistribution.Id > 0)
            {
                if (qcDistribution.QCDistributionRegisters != null && qcDistribution.QCDistributionRegisters.Count > 0)
                {
                    while (qcDistribution.QCDistributionRegisters.Count > 0)
                    {
                        qcDistribution.QCDistributionRegisters.ForEach(x =>
                        {
                            DeleteQcDistributionRegister(x);
                        });
                    }
                }
                qcDistribution.QCDistributionRegisters.Clear();
                _qcDistributions.Delete(qcDistribution);
            }
        }
        public IQueryable<QCDistributionRegisterInfo> GetQcDistributionRegisters()
        {
            return _qcDistributionRegisters.Table;
        }
        public QCDistributionRegisterInfo GetQcDistributionRegister(int Id)
        {
            return _qcDistributionRegisters.GetById(Id);
        }
        public void SaveQcDistributionRegister(QCDistributionRegisterInfo qcDistributionRegister)
        {
            LogHelper.Debug("准备保存对象:" + qcDistributionRegister.AnswerJSON);
            if (qcDistributionRegister.Id == 0)
            {
                _qcDistributionRegisters.Insert(qcDistributionRegister);
            }
            else
            {
                _qcDistributionRegisters.Update(qcDistributionRegister);
            }
        }
        public QCDistributionRegisterInfo GetNextOneQCDistRegInfo(QCDistributionRegisterInfo prevRegInfo)
        {
            var results = this.GetQcDistributionRegisters()
                .Where(p => p.QCDistributionId == prevRegInfo.QCDistributionId).OrderBy(p => p.LabInfo.LabCode).ToList();
            int index = results.FindIndex(p => p.Id == prevRegInfo.Id);
            if (index >= results.Count - 1)
            {
                return results[index];
            }
            return results[index + 1];
        }
        public void DeleteQcDistributionRegister(QCDistributionRegisterInfo qcDistributionRegister)
        {
            if (qcDistributionRegister.Id > 0)
            {
                _qcDistributionRegisters.Delete(qcDistributionRegister);
            }
        }

        public string GetPDFFromEMS(string rdlcFile, int labInfoId, int qcDistInfoId)
        {
            UserRequestInfo labInfo = _accountService.GetUserRequestInfoQueryable().FirstOrDefault(p => p.Id == labInfoId);
            QCDistribution qcDistInfo = this.GetQcDistributions().FirstOrDefault(p => p.Id == qcDistInfoId);
            QCDistributionRegisterInfo qcDistRegisterInfo = this.GetQcDistributionRegisters().FirstOrDefault(p => p.QCDistributionId == qcDistInfoId && p.LabId == labInfoId);
            //string filePath = Path.Combine(Directory.GetParent(AssemblyHelper.GetBaseDirectory()).FullName,
            //    "Reports", "EmsInfo.rdlc");
            EMFPrinter printer = new EMFPrinter(rdlcFile,
                Consts.Instance.EMFWidth(),
                Consts.Instance.EMFHeight(),
                Consts.Instance.EMFTop(),
                Consts.Instance.EMFLeft(),
                Consts.Instance.EMFBottom(),
                Consts.Instance.EMFRight());

            DataTable dt = new DataTable();
            dt.Columns.Add("ManagerName");
            dt.Columns.Add("ManagerTelephone");
            dt.Columns.Add("CompanyName");
            dt.Columns.Add("Address");
            dt.Columns.Add("PostCode");
            dt.Columns.Add("LabCode");
            dt.Columns.Add("ProjectName");
            DataRow row = dt.NewRow();
            row["ManagerName"] = labInfo.ManagerName;
            row["ManagerTelephone"] = labInfo.ManagerPhone;
            row["CompanyName"] = labInfo.CompanyName;
            row["Address"] = labInfo.Address;
            row["PostCode"] = labInfo.PostCode;
            row["LabCode"] = labInfo.LabCode;
            row["ProjectName"] = qcDistRegisterInfo.ProjectId;
            dt.Rows.Add(row);

            printer.AddDataSource("DataSet1", dt);
            Stream stream = new MemoryStream(printer.Print(EMFPrinter.PrintType.PDF));
            if (stream == null)
            {
                return "";
            }
            FileInfo fileInfo = new FileInfo(rdlcFile);
            string savefilePath = Path.Combine(Directory.GetParent(AssemblyHelper.GetBaseDirectory()).FullName, "files",
                string.Format("{2}_{0}_{1}.jpeg", labInfoId, qcDistInfoId, fileInfo.Name));

            int width = Consts.Instance.PrintWidth();
            int height = Consts.Instance.PrintHeight();
            Metafile emf = new Metafile(stream);
            System.Drawing.Graphics G;
            System.Drawing.Bitmap b = new System.Drawing.Bitmap((Int32)width, (Int32)height);
            G = System.Drawing.Graphics.FromImage(b);
            G.DrawLines(Pens.Black, new Point[] { new Point(2, 2), new Point(2, height-2),
            new Point(width-2,height-2),new Point(width-2,2)});
            G.DrawImage(emf, 0, 0, (float)width, (float)height);
            b.Save(savefilePath);
            printer.Dispose();
            return savefilePath;
        }
    }
}