Пример #1
0
        public override void WriteFile()
        {
            if (TemplateHandlerPrams.Structure.Id == 0)
            {
                throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
            }
            if (TemplateHandlerPrams.Factor.Id == 0)
            {
                throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
            }

            if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
            {
                logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
                throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
            }
            FileStream ms;

            if (!File.Exists(TemplateHandlerPrams.FileFullName))
            {
                _existOrNot = false;
                ms          = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
            }
            else
            {
                _existOrNot = true;
                ms          = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
            }
            // 读取模版
            Workbook ww;

            using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
            {
                ww = new Workbook(fs);
                fs.Close();
            }
            if (Cdbh == null)
            {
                ms.Close();
                throw new ArgumentException();
            }
            if (Cdbh.Count() > 0)
            {
                try
                {
                    string sb = TemplateHandlerPrams.Structure.Id.ToString() + '_' + TemplateHandlerPrams.Factor.Id.ToString();
                    Thread.Sleep(500);
                    int cnt = ReportCounter.Get(sb);
                    cnt += 1;
                    DateTime dtTime = TemplateHandlerPrams.Date;
                    if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
                    {
                        FillBasicInformation(ww, 3, 0, "[projName]", TemplateHandlerPrams.Organization.SystemName);
                    }

                    FillBasicInformation(ww, 2, 0, "[rptNo]", this.rptNo);
                    FillBasicInformation(ww, 4, 0, "[date]",
                                         TemplateHandlerPrams.Date.AddDays((-1)).ToString("yyyy年MM月dd日"));

                    FillBasicInformation(ww, 4, 10, "[count]", cnt.ToString());
                    // 查询水位数据
                    var todayData  = new Dictionary <string, string>();
                    var yesterData = new Dictionary <string, string>();

                    using (var db = new DW_iSecureCloud_EmptyEntities())
                    {
                        int      stcid         = TemplateHandlerPrams.Structure.Id;
                        DateTime dateToday     = TemplateHandlerPrams.Date;
                        DateTime dateYesterday = TemplateHandlerPrams.Date.AddDays(-1);
                        var      query         = from sw in db.T_THEMES_ENVI_WATER_LEVEL
                                                 from s in db.T_DIM_SENSOR
                                                 where s.STRUCT_ID == stcid && s.SENSOR_ID == sw.SENSOR_ID
                                                 select new
                        {
                            sw.ID,
                            sw.SENSOR_ID,
                            s.SENSOR_LOCATION_DESCRIPTION,
                            sw.WATER_LEVEL_CUMULATIVEVALUE,
                            sw.ACQUISITION_DATETIME
                        };

                        var td = query.Where(d => d.ACQUISITION_DATETIME < dateToday)
                                 .GroupBy(d => d.SENSOR_ID).Select(d => d.Select(s => s.ID).Max());
                        var ys =
                            query.Where(d => d.ACQUISITION_DATETIME < dateYesterday)
                            .GroupBy(d => d.SENSOR_ID)
                            .Select(d => d.Select(s => s.ID).Max());

                        todayData =
                            query.Where(d => td.Contains(d.ID))
                            .Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
                            .ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
                                          d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
                        yesterData =
                            query.Where(d => ys.Contains(d.ID))
                            .Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
                            .ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
                                          d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
                    }
                    var sheet          = ww.Worksheets[0];
                    int StartRowIndex  = 6;
                    int InsertRowIndex = 8;
                    Array.Sort(Cdbh);
                    if (Cdbh.Count() > 3)
                    {
                        if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
                        {
                            if (Cdbh.Count() > 4)
                            {
                                sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 4);
                            }
                        }
                        else
                        {
                            sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 3);
                        }
                    }
                    int structId           = TemplateHandlerPrams.Structure.Id;
                    int factorId           = TemplateHandlerPrams.Factor.Id;
                    List <SensorList> list = DataAccess.FindSensorsByStructAndFactor(structId, factorId);
                    foreach (SensorList sensorList in list)
                    {
                        foreach (Sensor sensor in sensorList.Sensors)
                        {
                            bool todayFlag    = false;
                            bool yestodayFlag = false;
                            //测点位置
                            sheet.Cells[StartRowIndex, 0].PutValue(sensor.Location);
                            //初始高程
                            decimal?waterLevelInit = DataAccess.GetWaterLevelInit(sensor.SensorId);
                            if (waterLevelInit == null)
                            {
                                sheet.Cells[StartRowIndex, 1].PutValue("/");
                            }
                            else
                            {
                                sheet.Cells[StartRowIndex, 1].PutValue(Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000")));
                            }
                            //本次高程
                            if (todayData.ContainsKey(sensor.Location) && todayData[sensor.Location] != null)
                            {
                                sheet.Cells[StartRowIndex, 2].PutValue(Convert.ToDecimal(todayData[sensor.Location]));
                            }
                            else
                            {
                                sheet.Cells[StartRowIndex, 2].PutValue("/");
                                todayFlag = true;
                            }

                            //上次高程
                            if (yesterData.ContainsKey(sensor.Location) && yesterData[sensor.Location] != null)
                            {
                                sheet.Cells[StartRowIndex, 3].PutValue(Convert.ToDecimal(yesterData[sensor.Location]));
                            }
                            else
                            {
                                sheet.Cells[StartRowIndex, 3].PutValue("/");
                                yestodayFlag = true;
                            }
                            //本次变化量
                            //变化速率
                            if (todayFlag || yestodayFlag)
                            {
                                sheet.Cells[StartRowIndex, 4].PutValue("/");
                                sheet.Cells[StartRowIndex, 6].PutValue("/");
                            }
                            else
                            {
                                var temp = (Convert.ToDecimal(todayData[sensor.Location]) - Convert.ToDecimal(yesterData[sensor.Location])) * 1000;
                                sheet.Cells[StartRowIndex, 4].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
                                sheet.Cells[StartRowIndex, 6].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
                            }
                            //累计变化量
                            if (waterLevelInit == null || todayFlag)
                            {
                                sheet.Cells[StartRowIndex, 5].PutValue("/");
                            }
                            else
                            {
                                var init  = Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000"));
                                var value = (Convert.ToDecimal(todayData[sensor.Location]) - init) * 1000;
                                sheet.Cells[StartRowIndex, 5].PutValue(value);
                            }
                            // 预警值
                            sheet.Cells[StartRowIndex, 7].PutValue(limit);
                            StartRowIndex++;
                        }
                    }
                    Thread.Sleep(500);
                    ReportCounter.Inc(sb);
                    ww.Save(ms, SaveFormat.Excel97To2003);
                    ms.Close();
                }
                catch (Exception ex)
                {
                    logger.Warn(ex);
                    ms.Close();
                    throw ex;
                }
            }
            else
            {
                logger.WarnFormat("{0}没有找到对应的信息,结构物ID:{1},监测因素ID:{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
                ms.Close();
                throw new ArgumentException();
            }
        }
Пример #2
0
        public override void WriteFile()
        {
            if (TemplateHandlerPrams.Structure.Id == 0)
            {
                throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
            }
            if (TemplateHandlerPrams.Factor.Id == 0)
            {
                throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
            }
            if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
            {
                logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
                throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
            }
            FileStream ms;

            if (!File.Exists(TemplateHandlerPrams.FileFullName))
            {
                _existOrNot = false;
                ms          = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
            }
            else
            {
                _existOrNot = true;
                ms          = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
            }
            // 读取模版
            Workbook ww;

            using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
            {
                ww = new Workbook(fs);
                fs.Close();
            }
            DateTime dtTime = TemplateHandlerPrams.Date;
            var      groups = new List <CxGroup>();

            // 根据传感器列表创建sheet页
            try
            {
                using (var db = new DW_iSecureCloud_EmptyEntities())
                {
                    var stcId = TemplateHandlerPrams.Structure.Id;
                    var fctId = TemplateHandlerPrams.Factor.Id;
                    groups = (from s in db.T_DIM_SENSOR
                              from sg in db.T_DIM_SENSOR_GROUP_CEXIE
                              from g in db.T_DIM_GROUP
                              where
                              s.SENSOR_ID == sg.SENSOR_ID && sg.GROUP_ID == g.GROUP_ID && s.STRUCT_ID == stcId &&
                              s.SAFETY_FACTOR_TYPE_ID == fctId
                              select new { g.GROUP_ID, g.GROUP_NAME, sg.DEPTH }).ToList()
                             .GroupBy(g => new { g.GROUP_ID, g.GROUP_NAME })
                             .Select(
                        s =>
                        new CxGroup
                    {
                        Id    = s.Key.GROUP_ID,
                        Name  = s.Key.GROUP_NAME,
                        Depth = s.Select(d => d.DEPTH * -1).OrderBy(d => d).ToArray()
                    })
                             .ToList();
                }
            }
            catch (Exception ex)
            {
                logger.Warn(ex.Message);
                ms.Close();
                throw ex;
            }
            if (groups.Count > 0)
            {
                try
                {
                    string sb = TemplateHandlerPrams.Structure.Id.ToString() + '_' + TemplateHandlerPrams.Factor.Id.ToString();
                    Thread.Sleep(300);
                    int cs = ReportCounter.Get(sb);
                    cs += 1;
                    // 复制sheet
                    ww.Worksheets[0].Name = groups[0].Name ?? "测斜管" + (0 + 1);
                    for (int i = 1; i < groups.Count; i++)
                    {
                        var index = ww.Worksheets.AddCopy(0);
                        ww.Worksheets[index].Name = groups[i].Name ?? "测斜管" + (i + 1);
                    }

                    // 填写数据
                    for (int i = 0; i < ww.Worksheets.Count; i++)
                    {
                        var sheet = ww.Worksheets[i];
                        // 设置项目编号
                        var rptNo = sheet.Cells[2, 0].StringValue;
                        sheet.Cells[2, 0].PutValue(rptNo.Replace("[rptNo]", this.rptNo));
                        // 设置项目名称
                        if (!TemplateHandlerPrams.TemplateFileName.Contains("CxDailyReportNcdwy"))
                        {
                            var projName = sheet.Cells[3, 0].StringValue;
                            sheet.Cells[3, 0].PutValue(projName.Replace("[projName]",
                                                                        TemplateHandlerPrams.Organization.SystemName));
                        }
                        // 设置监测点信息
                        var info = sheet.Cells[4, 0].StringValue;
                        sheet.Cells[4, 0].PutValue(
                            info.Replace("[date]", TemplateHandlerPrams.Date.AddDays(-1).ToString("yyyy年MM月dd日"))
                            .Replace("[sensor]", sheet.Name));
                        // 设置生成次数
                        var cout = sheet.Cells[4, 8].StringValue;
                        sheet.Cells[4, 8].PutValue(cout.Replace("[count]", cs.ToString()));
                        // 获取本次累积位移
                        var dataToday = DataAccess.GetByGroupDirectAndDateGroupByTime(
                            groups[i].Id,
                            TemplateHandlerPrams.Date.AddDays(-1).Date, /*当前时间0时0分0秒*/
                            TemplateHandlerPrams.Date.Date.AddSeconds(-1) /*当前时间23:59:59*/);
                        // 获取上次累积位移
                        var dataYesterday = DataAccess.GetByGroupDirectAndDateGroupByTime(
                            groups[i].Id,
                            TemplateHandlerPrams.Date.AddDays(-2).Date, /*前一天0时0分0秒*/
                            TemplateHandlerPrams.Date.AddDays((-1)).Date.AddSeconds(-1) /*前一天23:59:59*/);
                        int startRowIndex = 6;
                        #region 测斜管组下的深度个数如果超出模板设定范围,动态增加行
                        int InsertRowIndex = 8;
                        if (groups[i].Depth.Length > 3)
                        {
                            sheet.Cells.InsertRows(InsertRowIndex, groups[i].Depth.Length - 3);
                        }
                        #endregion
                        // 设置数据
                        foreach (var depth in groups[i].Depth)
                        {
                            // 设置深度
                            sheet.Cells[startRowIndex, 0].PutValue(depth);

                            // 上次累积位移
                            decimal?yesterdayValue = 0;
                            if (dataYesterday.Count != 0)
                            {
                                var depthValue = dataYesterday[0].Data.FirstOrDefault(d => d.Depth * -1 == depth);
                                if (depthValue != null)
                                {
                                    yesterdayValue = depthValue.YValue;
                                }
                            }
                            // 本次累积位移
                            decimal?todayValue = 0;
                            if (dataToday.Count != 0)
                            {
                                var depthValue = dataToday[0].Data.FirstOrDefault(d => d.Depth * -1 == depth);
                                if (depthValue != null)
                                {
                                    todayValue = depthValue.YValue;
                                }
                            }
                            // 变化量
                            decimal?dlta = todayValue - yesterdayValue;

                            // 设置数据
                            sheet.Cells[startRowIndex, 1].PutValue(dlta);
                            sheet.Cells[startRowIndex, 2].PutValue(yesterdayValue);
                            sheet.Cells[startRowIndex, 3].PutValue(todayValue);

                            sheet.Cells[startRowIndex, 5].PutValue(warnValue);
                            sheet.Cells[startRowIndex, 6].PutValue(limitValue);

                            startRowIndex++;
                        }
                    }
                    Thread.Sleep(300);
                    ReportCounter.Inc(sb);
                    ww.Save(ms, SaveFormat.Excel97To2003);
                    ms.Close();
                }
                catch (Exception ex)
                {
                    logger.Warn(ex);
                    ms.Close();
                    throw ex;
                }
            }
            else
            {
                logger.WarnFormat("{0}没有找到对应的信息,结构物ID:{1},监测因素ID:{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
                ms.Close();
                throw new ArgumentException();
            }
        }