public override void Begin() { using (var context = new SiteResourceEntities()) { context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quad.PhoneBook")); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quad.PhoneBook", 0)); System.Console.WriteLine("Reading Excel..."); CopyPhoneBook(context); System.Console.WriteLine("Saving to DB..."); context.SaveChanges(); } }
public override void Begin() { using (var context = new SiteResourceEntities()) { context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.BuildingQualifyCriteria")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.BuildingType")); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.BuildingQualifyCriteria", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.BuildingType", 0)); System.Console.WriteLine("Reading qualification..."); CopyCriteria(context); System.Console.WriteLine("Saving qualification..."); context.SaveChanges(); } }
public override void Begin() { using (var context = new SiteResourceEntities()) { context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.leadpersonal")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.leadgroup")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.leadpriority")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.leadshiftinfo")); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.leadpersonal", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.leadpriority", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.leadshiftinfo", 0)); System.Console.WriteLine("Reading workbook..."); CopyLeadGroup(context); context.SaveChanges(); CopyLeadPersonal(context); CopyLeadPriority(context); CopyLeadShift(context); System.Console.WriteLine("Saving workbook..."); context.SaveChanges(); } }
public override void Begin() { using (var context = new SiteResourceEntities()) { context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.Equipment")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.Machine")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.ToiletRequisite")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.LabourRate")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.AllowanceRate")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.OnCostRate")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.PublicLiability")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.QuoteSource")); context.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.quote.StandardRegion")); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.LabourRate", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.AllowanceRate", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.OnCostRate", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.PublicLiability", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.QuoteSource", 0)); context.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.quote.StandardRegion",0)); System.Console.WriteLine("Reading Machine..."); CopyMachine(context); System.Console.WriteLine("Saving Machie..."); System.Console.WriteLine("Reading Equipment..."); CopyEquipment(context); System.Console.WriteLine("Saving Equipment..."); System.Console.WriteLine("Reading Supply..."); CopySupply(context); System.Console.WriteLine("Saving Supply..."); System.Console.WriteLine("Reading labour rate..."); CopyLabourRate(context); System.Console.WriteLine("Saving labour rate..."); System.Console.WriteLine("Reading allowance rate..."); CopyAllowanceRate(context); System.Console.WriteLine("Saving allowance rate..."); System.Console.WriteLine("Reading oncost rate..."); CopyOnCostRate(context); System.Console.WriteLine("Saving oncost rate..."); System.Console.WriteLine("Reading publicLiability rate..."); CopyPublicLiability(context); System.Console.WriteLine("Saving publicLiability rate..."); System.Console.WriteLine("Reading QuoteSource..."); CopyQuoteSource(context); System.Console.WriteLine("Saving QuoteSource..."); System.Console.WriteLine("Reading StandardRegion..."); CopyStandardRegion(context); System.Console.WriteLine("Saving StandardRegion..."); try { context.SaveChanges(); } catch (DbEntityValidationException ex) { throw; } } }
private void CopyLeadPriority(SiteResourceEntities context) { Copy<LeadPriority> copy = delegate(ref LeadPriority entity, int i, int j, string value, string sheet) { switch (j) { case 1: int number; var isNumber = int.TryParse(value, out number); if (!isNumber) return false; entity.Distance = number; break; case 2: if (value.StartsWith("G")) entity.Role = (byte)(LeadGroups.GGM | LeadGroups.GM | LeadGroups.ROP); else entity.Role = (byte) LeadGroups.BD; break; case 3: entity.Priority = value; break; } return true; }; Save<LeadPriority> save = delegate(LeadPriority entity) { if ( context.LeadPriorities.Any( x => x.Distance == entity.Distance && x.Priority == entity.Priority && x.Role == entity.Role)) return; context.LeadPriorities.Add(entity); context.SaveChanges(); }; Read("Lead Priorities", "B3", "D66", copy, save); }
public static void Begin() { try { using (var destination = new SiteResourceEntities()) using (var origin = new CompanyContext()) { destination.Configuration.AutoDetectChangesEnabled = false; destination.Configuration.ValidateOnSaveEnabled = false; origin.Configuration.AutoDetectChangesEnabled = false; origin.Configuration.ValidateOnSaveEnabled = false; destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.QuestionResult")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.Issue")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.QuoteHistory")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.WPRequiredInfo")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.Cost")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.Quote.Quote")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.LeadHistory")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.Lead")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.CallLine")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.ContactPersonHistory")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.CleaningContract")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.SecurityContract")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.Contact")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.ContactPerson")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.SiteToGroupMapping")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.ExternalManager")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.SiteGroup")); destination.Database.ExecuteSqlCommand(SqlCmd.EmptyTable("IMS_Test.bd.site")); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.contact", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.contactperson", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.site", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("ims_test.bd.SiteGroup", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.bd.LeadHistory", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.bd.Lead", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.bd.CallLine", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.bd.ContactPersonHistory", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quote.Issue", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quote.QuoteHistory", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quote.Quote", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quote.Cost", 0)); destination.Database.ExecuteSqlCommand(SqlCmd.Reseed("IMS_Test.Quote.QuestionResult", 0)); //first to create business types if (!destination.BusinessTypes.Any()) { foreach (var businessType in GetBusinessTypes()) { destination.BusinessTypes.Add(businessType); } destination.SaveChanges(); } foreach (var row in origin.COMPTEMP.ToList()) { var site = CopySite(row); var siteGroup = CopySiteGroup(row); if (siteGroup != null) { //var group = destination.SiteGroups.Local.SingleOrDefault(x => x.Name.ToUpper() == siteGroup.Name.ToUpper()); var group = destination.SiteGroups.Local.SingleOrDefault( x => Util.StringSimilarity.Compare(x.GroupName, siteGroup.GroupName) >= Migration.NameSimilarityFactor); if (group != null) { if (group.Sites == null) group.Sites = new Collection<Site>(); group.Sites.Add(site); if (site.Groups == null) site.Groups = new Collection<SiteGroup>(); site.Groups.Add(group); } else { if (siteGroup.Sites == null) siteGroup.Sites = new Collection<Site>(); siteGroup.Sites.Add(site); if(site.Groups == null) site.Groups = new Collection<SiteGroup>(); site.Groups.Add(siteGroup); destination.SiteGroups.Add(siteGroup); } } if (row.BUILD_TYPE != null) { var buildingType = destination.BuildingTypes.SingleOrDefault(x => x.Code == row.BUILD_TYPE); if (buildingType != null) site.BuildTypeId = buildingType.Id; } site.Contacts = CopyContacts(row); site.CleaningContract = CopyCleaningContract(row); site.SecurityContract = CopySecurityContract(row); if (site.Contacts != null) site.ContactPersons = site.Contacts.Select(x => x.ContactPerson).ToList(); if (row.SALES_REP == "PMS") { var tenants = CopyTenant(row); if (tenants.Count > 0) { if (site.Groups == null || site.Groups.Count <= 0) site.Groups = new Collection<SiteGroup>(); site.Groups.Add(new SiteGroup { GroupName = string.Format("{0} {1}, {2} {3}, {4}", site.Number, site.Street, site.Suburb, site.State, site.Postcode), Type = "Building", Sites = new Collection<Site>() }); foreach (var group in site.Groups) { foreach (var tenant in tenants) { group.Sites.Add(tenant); if (tenant.Groups == null) tenant.Groups = new Collection<SiteGroup>(); tenant.Groups.Add(group); destination.Sites.Add(tenant); } } } } destination.Sites.Add(site); System.Console.WriteLine("Site: " + row.KEY + " " + row.COMPANY); } System.Console.WriteLine("Saving into new database, please wait"); destination.SaveChanges(); System.Console.WriteLine("extracting finished, press any keys to continue"); System.Console.ReadKey(); } } catch (DbEntityValidationException ex) { foreach (var item in ex.EntityValidationErrors) { } } catch (DbUpdateException ex) { foreach (var item in ex.Entries) { } } catch (Exception ex) { var i = ex; } }
public static void Menu() { var menu = new StringBuilder("1. Copy database \n2. Copy excel\n3. Copy xml script\n4. Report\n5. PhoneBook"); System.Console.WriteLine(menu); var input = System.Console.ReadLine(); int option; if (!int.TryParse(input, out option)) { System.Console.WriteLine("Invalid option"); } else { switch (option) { case (int)Options.DB: Copier.Begin(); break; case (int)Options.EXCEL: var excels = new List<ExcelCopier> { //new WorkbookCopier(@"K:\SQL_DATA\BD\Estimation Pipeline 2014 06 25.xlsm"), //new QualificationCopier(@"C:\Users\jing\Desktop\Doc\BD database verticals 2015 02 24.rock.xlsx"), new SmallQuoteCopier(@"C:\dev\BDWorkbook\Small-Medium quote costing workbook_v8.37.xlsm"), //new SpecCopier(@"K:\SQL_DATA\BD\estimation\Small-Medium quote costing workbook_v8.30.test.xlsm") }; foreach (var excel in excels) { excel.Begin(); } break; case (int)Options.SCRIPT: var file = AppDomain.CurrentDomain.BaseDirectory + "script.xml"; var template = new ScriptXmlTemplate(); var factory = new ScriptFactory(); var qualifications = new QualificationCreator(); var cleaningQuestions = new CleaningQuestionCreator(); foreach (var script in factory) { template.Scripts.Add(script.Create()); } foreach (var creator in qualifications.Creators) { template.Scripts.Add(creator.Create()); } foreach (var creator in cleaningQuestions.Creators) { template.Scripts.Add(creator.Create()); } SerializeHelper.Create(file, template); System.Console.WriteLine("File is saved to " + file); System.Console.ReadLine(); break; case (int)Options.Report: using (var context = new SiteResourceEntities()) { var helper = new ReportHelper(new UnitOfWork(context)); foreach (var report in context.WeeklyReports) { context.Entry(report).State = EntityState.Deleted; } helper.GenerateWeeklyHistory(DateTime.Today); helper.GenerateWeeklyHistory(DateTime.Today.AddDays(-7)); helper.GenerateWeeklyHistory(DateTime.Today.AddDays(-14)); foreach (var report in context.FullReports) { context.Entry(report).State = EntityState.Deleted; } helper.GenerateFullHistory(DateTime.Today); helper.GenerateFullHistory(DateTime.Today.AddDays(-7)); helper.GenerateFullHistory(DateTime.Today.AddDays(-14)); context.SaveChanges(); } break; case (int)Options.PhoneBook: System.Console.WriteLine("Please enter the Excel path"); var filePath = System.Console.ReadLine(); if (!System.IO.File.Exists(filePath)) { System.Console.WriteLine("Error: File does not exists at {0}", filePath); System.Console.ReadLine(); } else { new PhoneBookCopier(filePath).Begin(); } break; case (int)Options.Test: new SpecCopier(@"K:\SQL_DATA\BD\estimation\Small-Medium quote costing workbook_v8.30.test.xlsm").Begin(); break; default: System.Console.WriteLine("Invalid option"); System.Console.ReadLine(); break; } } }