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;
}
}
}
