Пример #1
0
        public static void UploadStations(string username, UploadFile data, ApplicationDbContext db)
        {
            using (var transaction = db.Database.BeginTransaction())
            {
                var fileName = data.Filename;
                fileName = StringGenerators.GenerateRandomString(16) + fileName.Replace(" ", "").ToLower();
                var filePath = Path.Combine(Directory.GetCurrentDirectory(), "Documents", "TempFiles", fileName);

                var base64Data = data.File.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
                var binData    = Convert.FromBase64String(base64Data);
                using (var stream = new MemoryStream(binData))
                {
                    File.WriteAllBytes(filePath, binData);
                }
                //gembox things
                var workbook  = ExcelFile.Load(filePath);
                var worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == "Main");
                if (worksheet == null)
                {
                    throw new Exception("Invalid Upload File");
                }
                var uploadData = new List <UploadModel>();
                var cnt        = 0;
                foreach (var row in worksheet.Rows)
                {
                    cnt++;
                    if (cnt == 1)
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(row.Cells[0].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a valid code for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[1].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a name for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[2].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter the electoral area code for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[3].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a constituency code for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[4].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a district code for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[5].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a region code for the record on line {cnt}");
                    }

                    var ud = new UploadModel
                    {
                        Code = row.Cells[0].GetFormattedValue(),
                        Name = row.Cells[1].GetFormattedValue(),
                        ElectoralAreaCode = row.Cells[2].GetFormattedValue(),
                        ConstituencyCode  = row.Cells[3].GetFormattedValue(),
                        DistrictCode      = row.Cells[4].GetFormattedValue(),
                        RegionCode        = row.Cells[5].GetFormattedValue()
                    };
                    uploadData.Add(ud);
                }

                foreach (var uData in uploadData)
                {
                    var existing = db.Stations.Where(x => (x.Code == uData.Code || uData.Name == x.Name) && x.ElectoralArea.Code == uData.ElectoralAreaCode && x.ElectoralArea.Constituency.Code == uData.ConstituencyCode && x.ElectoralArea.Constituency.District.Code == uData.DistrictCode && x.ElectoralArea.Constituency.District.Region.Code == uData.RegionCode).Include(x => x.ElectoralArea.Constituency.District.Region).FirstOrDefault();
                    if (existing != null)
                    {
                        if (existing.Code == uData.Code)
                        {
                            throw new Exception($"There is already an existing record with this Code - {existing.Code}, \nElectoral Area: {existing.ElectoralArea?.Name}, \nConstituency: {existing.ElectoralArea?.Constituency?.Name}, \nDistrict: {existing.ElectoralArea?.Constituency?.District?.Name} and \nRegion: {existing.ElectoralArea?.Constituency?.District?.Region?.Name}");
                        }
                        if (existing.Name == uData.Name)
                        {
                            throw new Exception($"There is already an existing record with this Name - {existing.Name}, \nElectoral Area: {existing.ElectoralArea?.Name}, \nConstituency: {existing.ElectoralArea?.Constituency?.Name}, \nDistrict: {existing.ElectoralArea?.Constituency?.District?.Name} and \nRegion: {existing.ElectoralArea?.Constituency?.District?.Region?.Name}");
                        }
                    }
                    var region = db.Regions.FirstOrDefault(x => x.Code.ToLower() == uData.RegionCode.ToLower());
                    if (region == null)
                    {
                        throw new Exception($"There is no region with this region code ({uData.RegionCode}). Please check and verify.");
                    }
                    var district = db.Districts.FirstOrDefault(x => x.Code.ToLower() == uData.DistrictCode.ToLower() && x.RegionId == region.Id);
                    if (district == null)
                    {
                        throw new Exception($"There is no district with this district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
                    }
                    var consti = db.Constituencies.FirstOrDefault(x => x.Code.ToLower() == uData.ConstituencyCode.ToLower() && x.DistrictId == district.Id);
                    if (consti == null)
                    {
                        throw new Exception($"There is no constituency with this constituency code - ({uData.ConstituencyCode}), district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
                    }
                    var ea = db.ElectoralAreas.FirstOrDefault(x => x.Code.ToLower() == uData.ElectoralAreaCode.ToLower() && x.ConstituencyId == consti.Id);
                    if (ea == null)
                    {
                        throw new Exception($"There is no electoral area with this electoral area code - ({uData.ElectoralAreaCode}), constituency code - ({uData.ConstituencyCode}), district code - ({uData.DistrictCode}) and region code - ({uData.RegionCode}). Please check and verify.");
                    }

                    // save the data
                    var newRec = new Station
                    {
                        Code            = uData.Code,
                        Name            = uData.Name,
                        ElectoralAreaId = ea.Id,
                        CreatedAt       = DateTime.Now.ToUniversalTime(),
                        CreatedBy       = username,
                        ModifiedAt      = DateTime.Now.ToUniversalTime(),
                        ModifiedBy      = username
                    };
                    db.Stations.Add(newRec);
                    db.SaveChanges();
                    //db.Entry<Station>(newRec).State = EntityState.Detached;
                }

                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                transaction.Commit();
            }
        }
Пример #2
0
        public static void UploadRegions(string username, UploadFile data, ApplicationDbContext db)
        {
            using (var transaction = db.Database.BeginTransaction())
            {
                var fileName = data.Filename;
                fileName = StringGenerators.GenerateRandomString(16) + fileName.Replace(" ", "").ToLower();
                var filePath = Path.Combine(Directory.GetCurrentDirectory(), "Documents", "TempFiles", fileName);

                var base64Data = data.File.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
                var binData    = Convert.FromBase64String(base64Data);
                using (var stream = new MemoryStream(binData))
                {
                    File.WriteAllBytes(filePath, binData);
                }
                //gembox things
                var workbook  = ExcelFile.Load(filePath);
                var worksheet = workbook.Worksheets.FirstOrDefault(x => x.Name == "Main");
                if (worksheet == null)
                {
                    throw new Exception("Invalid Upload File");
                }
                var uploadData = new List <UploadModel>();
                var cnt        = 0;
                foreach (var row in worksheet.Rows)
                {
                    cnt++;
                    if (cnt == 1)
                    {
                        continue;
                    }
                    if (string.IsNullOrEmpty(row.Cells[0].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a valid code for the record on line {cnt}");
                    }

                    if (string.IsNullOrEmpty(row.Cells[1].GetFormattedValue()))
                    {
                        throw new Exception($"Please enter a name for the record on line {cnt}");
                    }

                    var ud = new UploadModel
                    {
                        Code = row.Cells[0].GetFormattedValue(),
                        Name = row.Cells[1].GetFormattedValue()
                    };
                    uploadData.Add(ud);
                }


                foreach (var uData in uploadData)
                {
                    var existing = db.Regions.Where(x => x.Code == uData.Code || uData.Name == x.Name).FirstOrDefault();
                    if (existing != null)
                    {
                        if (existing.Code == uData.Code)
                        {
                            throw new Exception($"There is already an existing record with this Code - {existing.Code}");
                        }
                        if (existing.Name == uData.Name)
                        {
                            throw new Exception($"There is already an existing record with this Name - {existing.Name}");
                        }
                    }

                    // save the data
                    var newRec = new Models.Region
                    {
                        Code       = uData.Code,
                        Name       = uData.Name,
                        CreatedAt  = DateTime.Now.ToUniversalTime(),
                        CreatedBy  = username,
                        ModifiedAt = DateTime.Now.ToUniversalTime(),
                        ModifiedBy = username
                    };
                    db.Regions.Add(newRec);
                    db.SaveChanges();
                }

                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                transaction.Commit();
            }
        }