示例#1
0
        public JsonResult ExportData(string fromDate, string toDate, string isDuBao)
        {
            // fix data request
            DateTime?fromdate = fromDate.ConvertStringToDate();
            DateTime?todate   = toDate.ConvertStringToDate();

            // lấy dữ liệu xử lý
            using (var db = new Models.DbEntities())
            {
                string fileName;
                Dictionary <DateTime, string[, ]> list = GetDataSearch(db, fromDate, toDate, isDuBao);
                string excelFile  = Excel.getCopyExcelTemplateFile(out fileName);
                var    excelBytes = list.WriteExcel(excelFile);
                if (excelBytes != null)
                {
                    string fullPath = Path.Combine(Server.MapPath("~/TemplateFile/temp"), fileName);
                    using (var exportData = new MemoryStream())
                    {
                        exportData.Write(excelBytes, 0, excelBytes.Length);
                        exportData.Position = 0;

                        FileStream file = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
                        exportData.WriteTo(file);
                        file.Close();
                    }
                    return(Json(new { messenger = "success", fileName, status = true }));
                }
                else
                {
                    return(Json(new { messenger = list.Count() > 0 ? "success" : "not_data", status = false }));
                }
            }
        }
示例#2
0
 public JsonResult GetData(string fromDate, string toDate, string bc)
 {
     try
     {
         Dictionary <DateTime, string[, ]> list = new Dictionary <DateTime, string[, ]>();
         using (var db = new Models.DbEntities())
         {
             list = GetDataSearch(db, fromDate, toDate, bc);
         }
         return(Json(new { date = list.Keys, arrDatas = list.Values, status = true }));
     }
     catch (Exception ex)
     {
         return(Json(new { messenger = ex.Message, status = false }));
     }
 }
示例#3
0
文件: Excel.cs 项目: hieult5/MTN
        public static bool ReadAndWriteDataToExcel(this string filePath, bool isBC_QuanTrac)
        {
            bool response = true;

            using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, true))
            {
                WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                Workbook     workbook     = workBookPart.Workbook;
                using (var db = new Models.DbEntities())
                {
                    using (DbContextTransaction transaction = db.Database.BeginTransaction())
                    {
                        try
                        {
                            workBookPart.Workbook.Descendants <Sheet>().ForEach(sheet =>
                            {
                                if (response)
                                {
                                    WorksheetPart wsPart = (WorksheetPart)workBookPart.GetPartById(sheet.Id);
                                    SheetData sheetData  = wsPart.Worksheet.GetFirstChild <SheetData>();

                                    bool res = sheetData.WriteDatabase(sheet.Name, workBookPart, db, isBC_QuanTrac);
                                    if (!res)
                                    {
                                        response = false;
                                    }
                                }
                            });
                            if (response)
                            {
                                transaction.Commit();
                            }
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            response = false;
                        }
                    }
                }
            }
            return(response);
        }
示例#4
0
        private Dictionary <DateTime, string[, ]> GetDataSearch(Models.DbEntities db, string fromDate, string toDate, string isDuBao)
        {
            // fix data request
            DateTime?fromdate = fromDate.ConvertStringToDate();
            DateTime?todate   = toDate.ConvertStringToDate();

            Dictionary <DateTime, string[, ]> list = new Dictionary <DateTime, string[, ]>();

            string[,] arr;
            var lstTT = db.NV_MaubaocaoThuoctinh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Thuoctinh_ID);
            var lstDD = db.NV_MaubaocaoDiadanh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Diadanh_ID);

            if (!"true".Equals(isDuBao))
            {
                var dataNgayQuanTrac = db.NV_DulieuQuantrac.Select(x => x.NgayQuantrac).Distinct()
                                       .WhereIf(fromdate.HasValue, row => DateTime.Compare(fromdate.Value, row) <= 0)
                                       .WhereIf(todate.HasValue, row => DateTime.Compare(row, todate.Value) <= 0);

                // loaddata to string[,]
                int?rowIndex, colIndex;
                dataNgayQuanTrac.ForEach(date =>
                {
                    var lstTTs = from thuoctinh in db.TD_Thuoctinh
                                 select new DanhSachDiaDanh()
                    {
                        thuocTinhId = thuoctinh.Thuoctinh_ID,
                        diadanh     = (from dd in db.TD_Diadanh
                                       join dlQT in db.NV_DulieuQuantrac on dd.Diadanh_ID equals dlQT.BaocaoDiadanh_ID
                                       where dlQT.BaocaoThuoctinh_ID == thuoctinh.Thuoctinh_ID && dlQT.NgayQuantrac == date
                                       select new DanhSachThuocTinh()
                        {
                            DiaDanhId = dd.Diadanh_ID, GiaTri = dlQT.Giatri.ToString()
                        })
                    };
                    arr      = null;
                    rowIndex = null;
                    lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
                    {
                        colIndex = null;
                        arr      = arr ?? new string[lstDD.Count(), lstTT.Count()];
                        lstTT.AsEnumerable().ForEach(ref colIndex, col =>
                        {
                            var danhSachDiaDanhs = lstTTs.Where(r => r.thuocTinhId.Equals(col)).FirstOrDefault();
                            if (danhSachDiaDanhs == null)
                            {
                                arr[rowIndex.Value, colIndex.Value] = "0";
                            }
                            else
                            {
                                var tt = danhSachDiaDanhs.diadanh.Where(d => d.DiaDanhId.Equals(row)).FirstOrDefault();
                                arr[rowIndex.Value, colIndex.Value] = tt == null ? "0" : tt.GiaTri;
                            }
                        });
                    });
                    list.Add(date, arr);
                }
                                         );
            }
            else
            {
                var dataNgayDuBao = db.NV_Dulieudubao.Select(x => x.Ngaydubao).Distinct()
                                    .WhereIf(fromdate.HasValue, row => DateTime.Compare(fromdate.Value, row) <= 0)
                                    .WhereIf(todate.HasValue, row => DateTime.Compare(row, todate.Value) <= 0);

                // loaddata to string[,]
                int?rowIndex, colIndex;
                dataNgayDuBao.ForEach(date =>
                {
                    var lstTTs = from thuoctinh in db.TD_Thuoctinh
                                 select new DanhSachDiaDanh()
                    {
                        thuocTinhId = thuoctinh.Thuoctinh_ID,
                        diadanh     = (from dd in db.TD_Diadanh
                                       join dlDB in db.NV_Dulieudubao on dd.Diadanh_ID equals dlDB.BaocaoDiadanh_ID
                                       where dlDB.BaocaoThuoctinh_ID == thuoctinh.Thuoctinh_ID && dlDB.Ngaydubao == date
                                       select new DanhSachThuocTinh()
                        {
                            DiaDanhId = dd.Diadanh_ID, GiaTri = dlDB.Giatri.ToString()
                        })
                    };
                    arr      = null;
                    rowIndex = null;
                    lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
                    {
                        colIndex = null;
                        arr      = arr ?? new string[lstDD.Count(), lstTT.Count()];
                        lstTT.AsEnumerable().ForEach(ref colIndex, col =>
                        {
                            var danhSachDiaDanhs = lstTTs.Where(r => r.thuocTinhId.Equals(col)).FirstOrDefault();
                            if (danhSachDiaDanhs == null)
                            {
                                arr[rowIndex.Value, colIndex.Value] = "0";
                            }
                            else
                            {
                                var tt = danhSachDiaDanhs.diadanh.Where(d => d.DiaDanhId.Equals(row)).FirstOrDefault();
                                arr[rowIndex.Value, colIndex.Value] = tt == null ? "0" : tt.GiaTri;
                            }
                        });
                    });
                    list.Add(date, arr);
                }
                                      );
            }

            return(list);
        }
示例#5
0
文件: Excel.cs 项目: hieult5/MTN
        private static bool WriteDatabase(this SheetData sheetData, string sheetName, WorkbookPart workBookPart, Models.DbEntities db, bool isBC_QuanTrac = true)
        {
            DateTime?datetime = sheetData.GetDateImportDb(workBookPart, sheetName);

            if (datetime == null)
            {
                return(false);
            }
            bool response = false;

            db.Database.Log = Console.Write;
            bool isUpdate = isBC_QuanTrac
                ? db.NV_DulieuQuantrac.Select(x => x.NgayQuantrac).Distinct()
                            .Any(row => DateTime.Compare(datetime.Value, row) == 0)
                : db.NV_Dulieudubao.Select(x => x.Ngaydubao).Distinct()
                            .Any(row => DateTime.Compare(datetime.Value, row) == 0);

            var lstTT = db.NV_MaubaocaoThuoctinh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Thuoctinh_ID);
            var lstDD = db.NV_MaubaocaoDiadanh.Where(x => x.MauBC_ID.Equals("BC_CLN")).OrderBy(x => x.STT).Select(row => row.Diadanh_ID);

            int?rowIndex = null;
            int?colIndex = null;

            try
            {
                int?soLieuIndex = null;
                if (isBC_QuanTrac)
                {
                    var lastEntity = db.NV_DulieuQuantrac.AsEnumerable()
                                     .Select(x => new { id = Convert.ToInt32(x.SolieuQuantrac_ID) })
                                     .OrderByDescending(x => x.id).FirstOrDefault();
                    soLieuIndex = lastEntity == null ? 0 : lastEntity.id;
                }
                else
                {
                    var lastEntity = db.NV_Dulieudubao
                                     .Select(x => new { id = Convert.ToInt32(x.SolieuDB_ID) })
                                     .OrderByDescending(x => x.id).FirstOrDefault();
                    soLieuIndex = lastEntity == null ? 0 : lastEntity.id;
                }
                lstDD.AsEnumerable().ForEach(ref rowIndex, row =>
                {
                    colIndex = null;
                    lstTT.AsEnumerable().ForEach(ref colIndex, col =>
                    {
                        soLieuIndex = soLieuIndex ?? 0;
                        double value;
                        string val = sheetData.GetValueCell(workBookPart, rowIndex.Value + 6, colIndex.Value + 3);
                        if (string.IsNullOrEmpty(val) || !double.TryParse(val, out value))
                        {
                            value = 0;
                        }

                        if (isBC_QuanTrac)
                        {
                            if (isUpdate)
                            {
                                var isExits = db.NV_DulieuQuantrac.Any(res =>
                                                                       res.BaocaoThuoctinh_ID == col &&
                                                                       res.BaocaoDiadanh_ID == row &&
                                                                       DateTime.Compare(datetime.Value, res.NgayQuantrac) == 0);
                                if (isExits)
                                {
                                    #region update du lieu quan trac
                                    var temp = db.NV_DulieuQuantrac.FirstOrDefault(res =>
                                                                                   res.BaocaoThuoctinh_ID == col &&
                                                                                   res.BaocaoDiadanh_ID == row &&
                                                                                   DateTime.Compare(datetime.Value, res.NgayQuantrac) == 0);
                                    temp.Giatri = value;
                                    #endregion
                                }
                                else
                                {
                                    #region add du lieu quan trac
                                    soLieuIndex++;
                                    Models.NV_DulieuQuantrac entity = new Models.NV_DulieuQuantrac()
                                    {
                                        SolieuQuantrac_ID  = soLieuIndex.ToString(),
                                        NgayQuantrac       = datetime.Value,
                                        BaocaoDiadanh_ID   = row,
                                        BaocaoThuoctinh_ID = col,
                                        Giatri             = value
                                    };
                                    db.NV_DulieuQuantrac.Add(entity);
                                    #endregion
                                }
                            }
                            else
                            {
                                #region add du lieu quan trac
                                soLieuIndex++;
                                Models.NV_DulieuQuantrac entity = new Models.NV_DulieuQuantrac()
                                {
                                    SolieuQuantrac_ID  = soLieuIndex.ToString(),
                                    NgayQuantrac       = datetime.Value,
                                    BaocaoDiadanh_ID   = row,
                                    BaocaoThuoctinh_ID = col,
                                    Giatri             = value
                                };
                                db.NV_DulieuQuantrac.Add(entity);
                                #endregion
                            }
                        }
                        else
                        {
                            if (isUpdate)
                            {
                                var isExits = db.NV_Dulieudubao.Any(res =>
                                                                    res.BaocaoThuoctinh_ID == col &&
                                                                    res.BaocaoDiadanh_ID == row &&
                                                                    DateTime.Compare(datetime.Value, res.Ngaydubao) == 0);
                                if (isExits)
                                {
                                    #region update du lieu du bao
                                    var temp = db.NV_Dulieudubao.FirstOrDefault(res =>
                                                                                res.BaocaoThuoctinh_ID == col &&
                                                                                res.BaocaoDiadanh_ID == row &&
                                                                                DateTime.Compare(datetime.Value, res.Ngaydubao) == 0
                                                                                );
                                    temp.Giatri = value;
                                    #endregion
                                }
                                else
                                {
                                    #region add du lieu du bao
                                    soLieuIndex++;
                                    Models.NV_Dulieudubao entity = new Models.NV_Dulieudubao()
                                    {
                                        SolieuDB_ID        = soLieuIndex.ToString(),
                                        Ngaydubao          = datetime.Value,
                                        BaocaoDiadanh_ID   = row,
                                        BaocaoThuoctinh_ID = col,
                                        Giatri             = value
                                    };
                                    db.NV_Dulieudubao.Add(entity);
                                    #endregion
                                }
                            }
                            else
                            {
                                #region add du lieu du bao
                                soLieuIndex++;
                                Models.NV_Dulieudubao entity = new Models.NV_Dulieudubao()
                                {
                                    SolieuDB_ID        = soLieuIndex.ToString(),
                                    Ngaydubao          = datetime.Value,
                                    BaocaoDiadanh_ID   = row,
                                    BaocaoThuoctinh_ID = col,
                                    Giatri             = value
                                };
                                db.NV_Dulieudubao.Add(entity);
                                #endregion
                            }
                        }

                        db.SaveChanges();
                    });
                });

                response = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error occurred: " + ex.StackTrace);
                response = false;
            }
            return(response);
        }