private void CopyLeadGroup(SiteResourceEntities context)
{
Copy<LeadGroup> copy = delegate(ref LeadGroup entity, int i, int j, string value, string sheet)
{
switch (j)
{
case 1:
var parse = (LeadGroups)Enum.Parse(typeof(LeadGroups), value);
entity.Id = (byte) parse;
entity.Group = value;
break;
case 2:
entity.LeadTarget = Convert.ToInt32(value);
break;
case 3:
entity.LeadStop = Convert.ToInt32(value);
break;
}
return true;
};
Save<LeadGroup> save = entity => context.LeadGroups.Add(entity);
Read("Lead Priorities", "F3", "H6", copy, save);
}
private static void StatusInit(SiteResourceEntities context)
{
context.LeadStatus.AddOrUpdate(
l => l.Name,
new LeadStatus
{
Name = "new",
Description = "Please contact to make an appointment",
Order = 1
}, new LeadStatus
{
Name = "toBeCalled",
Description = "Contact not successfully, please contact again",
Order = 2
},
new LeadStatus
{
Name = "callback",
Description = "Please make a callback on: ",
Order = 3
},
new LeadStatus
{
Name = "appointment",
Description = "Please make a site visit on: ",
Order = 4
},
new LeadStatus
{
Name = "visited",
Description = "Please click following buttons to do future actions",
Order = 5
},
new LeadStatus
{
Name = "estimation",
Description = null,
Order = 6,
},
new LeadStatus
{
Name = "quoted",
Description = null,
Order = 7,
Hidden = true
},
new LeadStatus
{
Name = "cancel",
Description = null,
Order = 8,
Hidden = true
});
}
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();
}
}
private void CopyCriteria(SiteResourceEntities context)
{
Copy<BuildingType> copy = delegate(ref BuildingType entity, int i, int j, string value, string sheet)
{
if (entity.Criterias == null)
entity.Criterias = new Collection<BuildingQualifyCriteria>();
switch (j)
{
case 1:
entity.Code = value;
break;
case 3:
if (string.IsNullOrEmpty(value) && string.IsNullOrEmpty(entity.Type))
return false;
if (string.Compare(value, "School", StringComparison.OrdinalIgnoreCase) == 0 ||
string.Compare(value, "CBD residential Tower", StringComparison.OrdinalIgnoreCase) == 0)
return false;
entity.Type = value;
break;
case 5:
entity.CriteriaDescription = value;
break;
case 6:
GetCriteria(value, Size.Size120.GetDescription(), ref entity);
break;
case 7:
GetCriteria(value, Size.Size050.GetDescription(), ref entity);
break;
case 8:
GetCriteria(value, Size.Size025.GetDescription(), ref entity);
break;
case 9:
GetCriteria(value, Size.Size008.GetDescription(), ref entity);
break;
}
return true;
};
Save<BuildingType> save = delegate(BuildingType entity)
{
context.BuildingTypes.Add(entity);
};
Read("New Verticals", "A6", "J34", copy, save);
}
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();
}
}
private void CopyLeadPersonal(SiteResourceEntities context)
{
Copy<LeadPersonal> copy = delegate(ref LeadPersonal entity, int i, int j, string value, string sheet)
{
switch (j)
{
case 2:
entity.Initial = value;
break;
case 3:
entity.Name = value;
break;
case 4:
entity.GroupId = (byte) ((LeadGroups)Enum.Parse(typeof (LeadGroups), value));
break;
}
return true;
};
Save<LeadPersonal> save = entity => context.LeadPersonals.Add(entity);
Read("Lead Priorities", "B76", "Q106", copy, save);
}
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 CopyMachine(SiteResourceEntities context)
{
Copy<Machine> copy = (ref Machine entity, int i, int j, string value, string sheet) =>
{
switch (j)
{
case 1:
entity.Type = value;
break;
case 2:
int years;
if (int.TryParse(value, out years))
entity.YearsAllocated = years;
break;
case 3:
double repairFactor;
if (double.TryParse(value, out repairFactor))
entity.RepairFactor = repairFactor;
break;
case 4:
int fuel;
if (int.TryParse(value, out fuel))
entity.Fuels = fuel;
break;
}
return true;
};
Save<Machine> save = entity => context.Machines.Add(entity);
Read("Equ", "B75", "E124", copy, save);
}
private void CopyLabourRate(SiteResourceEntities context)
{
Copy<LabourRate> copy = delegate(ref LabourRate entity, int i, int j, string value, string sheet)
{
switch (j)
{
case 1:
entity.Weekdays = Convert.ToDecimal(value);
break;
case 2:
entity.Saturday = Convert.ToDecimal(value);
break;
case 3:
entity.Sunday = Convert.ToDecimal(value);
break;
case 4:
entity.Holiday = Convert.ToDecimal(value);
break;
}
if (string.IsNullOrEmpty(entity.Title))
{
switch (i)
{
case 1:
entity.Title = LabourRateOptions.PartTimeDay.GetDescription();
break;
case 2:
entity.Title = LabourRateOptions.PartTimeNight.GetDescription();
break;
case 3:
entity.Title = LabourRateOptions.FullTimeDay.GetDescription();
break;
case 4:
entity.Title = LabourRateOptions.FullTimeNight.GetDescription();
break;
case 5:
entity.Title = LabourRateOptions.CasualNight.GetDescription();
break;
case 6:
entity.Title = LabourRateOptions.FullTimeDayCleanStart.GetDescription();
break;
case 7:
entity.Title = LabourRateOptions.PartTimeDayCleanStart.GetDescription();
break;
case 8:
entity.Title = LabourRateOptions.PartTimeNightCleanStart.GetDescription();
break;
case 9:
entity.Title = LabourRateOptions.Supervisor.GetDescription();
break;
}
}
return true;
};
Save<LabourRate> save = entity => context.LabourRates.Add(entity);
Read("Quad Labour", "C122", "F130", copy, save);
}
private void CopyEquipment(SiteResourceEntities context)
{
Copy<Equipment> copy = (ref Equipment entity, int i, int j, string text, string sheet) =>
{
var toSave = true;
switch (j)
{
case 1:
entity.EquipmentCode = text;
entity.UpdateDate = DateTime.Today;
break;
case 2:
if (string.IsNullOrEmpty(entity.EquipmentCode))
toSave = false;
entity.Description = text;
break;
case 3:
int cost;
if (int.TryParse(text, out cost))
entity.Cost = cost;
break;
case 11:
entity.MachineType = text;
break;
case 17:
entity.UserGuide = text;
break;
case 18:
entity.Comment = text;
break;
case 19:
int rate;
if (int.TryParse(text, out rate))
entity.ProductionRatePerHour = rate;
break;
case 21:
entity.Issues = text;
break;
case 22:
entity.IsLargeEquipment = !string.IsNullOrEmpty(text);
break;
}
return toSave;
};
Save<Equipment> save = entity => context.Equipments.Add(entity);
Read("Equ", "A12", "V68", copy, save);
}
private static void QuestionInit(SiteResourceEntities context)
{
//ToCurrent questions
var answerList1 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Face to face presentation",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name="Hand delivered",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name="Emailed quote",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Posted quote",
Type = QuoteAnswerType.None
}
};
var question1 = new QuoteQuestion
{
Name = "How do you present the quote to the client ?",
Type = QuoteQuestionType.ToCurrent,
QuoteAnswers = answerList1
};
var answerList2 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "High",
Type = QuoteAnswerType.None,
},
new QuoteAnswer
{
Name = "Low",
Type = QuoteAnswerType.None,
},
new QuoteAnswer
{
Name = "Same",
Type = QuoteAnswerType.None,
},
new QuoteAnswer
{
Name = "Do not know",
Type = QuoteAnswerType.None,
},
};
var question2 = new QuoteQuestion
{
Name = "How is our price in relation to what you are paying now ?",
Type = QuoteQuestionType.ToCurrent,
QuoteAnswers = answerList2
};
var answerList3 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Yes",
Type = QuoteAnswerType.None,
},
new QuoteAnswer
{
Name = "No",
Type = QuoteAnswerType.None,
}
};
var question3 = new QuoteQuestion
{
Name = "Do we resubmit a price ?",
Type = QuoteQuestionType.ToCurrent,
QuoteAnswers = answerList3
};
context.QuoteQuestions.AddOrUpdate(l => new { l.Name, l.Type }, question1, question2, question3);
//Not called
var answerList4 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Client does not want to be called",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "I have tried and cannot contact",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Client has asked to be called on a specific date",
Type = QuoteAnswerType.Date
}
};
var question4 = new QuoteQuestion
{
Name = "Reason why not called",
Type = QuoteQuestionType.NotCalled,
QuoteAnswers = answerList4
};
context.QuoteQuestions.AddOrUpdate(l => new { l.Name, l.Type }, question4);
//No Dead
var answerList5 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Official tender",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Client still considering",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "No feedback but not dead",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Other",
Type = QuoteAnswerType.Text
}
};
var question5 = new QuoteQuestion
{
Name = "Reason why not dead",
Type = QuoteQuestionType.NoDead,
QuoteAnswers = answerList5
};
context.QuoteQuestions.AddOrUpdate(l => new { l.Name, l.Type }, question5);
//Dead
var answerList6 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Yes",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "No",
Type = QuoteAnswerType.None
}
};
var question6 = new QuoteQuestion
{
Name = "Did the client stay with their current contractors ?",
Type = QuoteQuestionType.Dead,
QuoteAnswers = answerList6
};
var answerList7 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Yes",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "No",
Type = QuoteAnswerType.None
}
};
var question7 = new QuoteQuestion
{
Name = "Was our price higher than the client current paying ?",
Type = QuoteQuestionType.Dead,
QuoteAnswers = answerList7
};
var answerList8 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Yes",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "No",
Type = QuoteAnswerType.None
}
};
var question8 = new QuoteQuestion
{
Name = "Was it more than 10% ?",
Type = QuoteQuestionType.Dead,
QuoteAnswers = answerList8
};
var answerList9 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Yes",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "No",
Type = QuoteAnswerType.None
}
};
var question9 = new QuoteQuestion
{
Name = "Were the client satisfied with our proposal/response ?",
Type = QuoteQuestionType.Dead,
QuoteAnswers = answerList9
};
var question10 = new QuoteQuestion
{
Name = "Client Comment",
Type = QuoteQuestionType.Dead,
TextOnly = true
};
context.QuoteQuestions.AddOrUpdate(l => new { l.Name, l.Type }, question6, question7, question8, question9, question10);
//Not Adjustment
var answerList11 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Official tender",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Do not feel comfortable to ask",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Previous negative prices check response",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Other",
Type = QuoteAnswerType.Text
}
};
var question11 = new QuoteQuestion
{
Name = "Reason why not adjust the quote ?",
Type = QuoteQuestionType.NoAdjust,
QuoteAnswers = answerList11
};
context.QuoteQuestions.AddOrUpdate(l => new { l.Name, l.Type }, question11);
//Client Email
var answerList12 = new List<QuoteAnswer>
{
new QuoteAnswer
{
Name = "Currently negotiating",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Spoke recently",
Type = QuoteAnswerType.None
},
new QuoteAnswer
{
Name = "Inappropriate for tihs quote",
Type = QuoteAnswerType.Text
}
};
var question12 = new QuoteQuestion
{
Name = "Reason why not send Email ?",
Type = QuoteQuestionType.NoEmail,
QuoteAnswers = answerList12
};
context.QuoteQuestions.AddOrUpdate(l=>new {l.Name,l.Type},question12);
}
public static void Start(SiteResourceEntities context)
{
StatusInit(context);
}
private void CopyQuoteSource(SiteResourceEntities context)
{
Copy<QuoteSource> copy = (ref QuoteSource entity, int i, int j, string value, string sheet) =>
{
switch (j)
{
case 1:
entity.Description = value;
break;
case 2:
return false;
case 3:
entity.Name = value;
break;
}
return true;
};
Save<QuoteSource> save = entity => context.QuoteSources.Add(entity);
Read("Small Quote form", "H191", "J199", copy, save);
}
public static void Start(SiteResourceEntities context)
{
StatusInit(context);
// TestInit(context);
QuestionInit(context);
}
private void CopySupply(SiteResourceEntities context)
{
Copy<ToiletRequisite> copy = (ref ToiletRequisite entity, int i, int j, string text, string sheet) =>
{
switch (j)
{
case 1:
entity.ItemCode = text;
break;
case 2:
entity.Type = text;
break;
case 3:
entity.Description = text;
break;
case 4:
entity.RarelyUsed = !string.IsNullOrEmpty(text);
break;
case 5:
entity.Ply = text;
break;
case 6:
entity.Size = text;
break;
case 7:
entity.Quality = text;
break;
case 8:
entity.UnitsPerCarton = text;
break;
case 9:
decimal price;
decimal.TryParse(text, out price);
entity.Price = price;
break;
case 12:
entity.HayesCode = text;
break;
case 14:
decimal cost;
decimal.TryParse(text, out cost);
entity.Cost = cost;
break;
}
return true;
};
Save<ToiletRequisite> save = entity =>
{
if (string.IsNullOrEmpty(entity.ItemCode))
return;
context.ToiletRequisites.Add(entity);
};
Read("Toi", "B6", "O127", copy, save);
}
private void CopyLeadShift(SiteResourceEntities context)
{
Copy<LeadShiftInfo> copy = delegate(ref LeadShiftInfo entity, int i, int j, string value, string sheet)
{
switch (i)
{
case 1:
entity.RecordPerShift = int.Parse(value);
break;
case 2:
entity.LeadPerShift = int.Parse(value);
break;
case 3:
entity.ContactRatePer3HrShift = int.Parse(value);
break;
case 4:
entity.CallCycleWeeks = int.Parse(value);
break;
case 5:
entity.PaCalledWks = int.Parse(value);
break;
}
return true;
};
Save<LeadShiftInfo> save = entity => context.LeadShiftInfos.Add(entity);
ReadVertical("Lead Priorities", "AD67", "AD71", copy, save);
}
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;
}
}
private static void TestInit(SiteResourceEntities context)
{
var address = new Address
{
Suburb = "wellington",
Street = "Shoreline",
Number = "12",
Unit = "13"
};
var quote = new Quote
{
CreatedDate = DateTime.Today,
LastUpdateDate = DateTime.Today,
BusinessTypeId = 1,
Firstname = "chang",
Lastname = "liu",
Position = "manager",
Title = "Mr",
Company = "NZ test company",
LeadPersonalId = 1,
StatusId = 1,
Phone = "231414124",
Postcode = "0112",
State = "NZ",
Address = address
};
context.Quotes.AddOrUpdate(l=>l.BusinessTypeId,quote);
}
private void CopyOnCostRate(SiteResourceEntities context)
{
Copy<OnCostRate> copy = (ref OnCostRate entity, int i, int j, string value, string sheet) =>
{
switch (i)
{
case 1:
entity.State = "NSW";
break;
case 2:
entity.State = "QLD";
break;
case 3:
entity.State = "ACT";
break;
case 4:
entity.State = "VIC";
break;
case 5:
entity.State = "SA";
break;
case 6:
entity.State = "NT";
break;
case 7:
entity.State = "WA";
break;
case 8:
entity.State = "TAS";
break;
}
switch (j)
{
case 1:
entity.HolidayPay = Convert.ToDecimal(value);
break;
case 2:
entity.SickPay = Convert.ToDecimal(value);
break;
case 3:
entity.WorkerCompensation = Convert.ToDecimal(value);
break;
case 4:
entity.Superannuation = Convert.ToDecimal(value);
break;
case 5:
entity.PayrollTax = Convert.ToDecimal(value);
break;
case 6:
entity.LongService = Convert.ToDecimal(value);
break;
case 7:
entity.IncomeProtection = Convert.ToDecimal(value);
break;
case 9:
entity.Materials = Convert.ToDecimal(value);
break;
}
return true;
};
Save<OnCostRate> save = entity => context.OnCostRates.Add(entity);
ReadHorizontally("Quad Labour", "E142", "L150", copy, save);
}
private void CopyPublicLiability(SiteResourceEntities context)
{
Copy<PublicLiability> copy = (ref PublicLiability entity, int i, int j, string value, string sheet) =>
{
switch (j)
{
case 1:
entity.Description = value;
break;
case 2:
entity.Precentage = Convert.ToDecimal(value);
break;
}
return true;
};
Save<PublicLiability> save = entity => context.PublicLiabilities.Add(entity);
Read("Small Quote form","B189","C190",copy,save);
}
private static void StatusInit(SiteResourceEntities context)
{
context.QuoteStatus.AddOrUpdate(l => l.Name,
new QuoteStatus
{
Name = QuoteStatusTypes.New.ToString(),
Description = "Please create cost estimations",
Order = 1,
},
new QuoteStatus
{
Name = QuoteStatusTypes.Estimation.ToString(),
Description = "Please finalize all the cost estimations and send to WP",
Order = 2,
},
new QuoteStatus
{
Name = QuoteStatusTypes.WPReview.ToString(),
Description =
"Please review all the cost estimations. If there is no poblems, please merge data to quote and upload",
Order = 3,
},
new QuoteStatus
{
Name = QuoteStatusTypes.QPReview.ToString(),
Description = "Please review the quote document",
Order = 4,
},
new QuoteStatus
{
Name = QuoteStatusTypes.BDReview.ToString(),
Description =
"Please review the quote document",
Order = 5,
},
new QuoteStatus
{
Name = QuoteStatusTypes.PreFinalReview.ToString(),
Description = "Please review the quote document before final review",
Order = 6,
},
new QuoteStatus
{
Name = QuoteStatusTypes.FinalReview.ToString(),
Description = "Please final review the quote document",
Order = 7,
},
new QuoteStatus
{
Name = QuoteStatusTypes.Print.ToString(),
Description = "Please print the quote and send to QP",
Order = 8,
},
new QuoteStatus
{
Name = QuoteStatusTypes.PresentToClient.ToString(),
Description = "Please present the quote to the client and get feedback from the client",
Order = 9,
},
new QuoteStatus
{
Name = QuoteStatusTypes.WPIssues.ToString(),
Description = "Please resolve the outstanding issue",
Order = 10,
},
new QuoteStatus
{
Name = QuoteStatusTypes.QPIssues.ToString(),
Description = "Please resolve the outstanding issue",
Order = 11,
},
new QuoteStatus
{
Name = QuoteStatusTypes.Current.ToString(),
Description = null,
Order = 12,
Hidden = true
},
new QuoteStatus
{
Name = QuoteStatusTypes.Cancel.ToString(),
Description = "This quote has been cancelled",
Order = 13,
Hidden = true
},
new QuoteStatus
{
Name = QuoteStatusTypes.Dead.ToString(),
Description = "This quote is dead",
Order = 14,
Hidden = true
}
);
}
private void CopyStandardRegion(SiteResourceEntities context)
{
Copy<StandardRegion> copy = (ref StandardRegion entity, int i, int j, string value, string sheet) =>
{
entity.Name = value;
return true;
};
Save<StandardRegion> save = entity => context.StandardRegions.Add(entity);
Read("Small Quote form","L190","L197",copy,save);
}
private void CopyPhoneBook(SiteResourceEntities context)
{
Copy<QuadPhoneBook> copy = (ref QuadPhoneBook entity, int i, int j, string text, string sheet) =>
{
switch (j)
{
case 1:
entity.LoginName = text;
break;
case 2:
entity.Intial = text;
break;
case 3:
entity.Firstname = text;
break;
case 4:
entity.Lastname = text;
break;
case 5:
entity.ManagerInitial = text;
break;
case 6:
entity.DirectNumber = text;
break;
case 7:
entity.Ext = text;
break;
case 8:
entity.SpeedDail = text;
break;
case 9:
entity.Mobile = text;
break;
case 10:
entity.State = text;
break;
case 11:
entity.Group = text;
break;
case 12:
entity.Position = text;
break;
case 13:
entity.QuadArea = text;
break;
case 14:
entity.LeadArea = text;
break;
case 15:
entity.Fax = text;
break;
case 16:
entity.Email = text;
break;
}
return true;
};
Save<QuadPhoneBook> save = entity => context.QuadPhoneBook.Add(entity);
Read("Staff Phone Listing", "A2", "P109", copy, save);
}
private void CopyAllowanceRate(SiteResourceEntities context)
{
Copy<AllowanceRate> copy = (ref AllowanceRate entity, int i, int j, string value, string sheet) =>
{
switch (i)
{
case 1:
entity.State = "NSW";
break;
case 2:
entity.State = "QLD";
break;
case 3:
entity.State = "ACT";
break;
case 4:
entity.State = "VIC";
break;
case 5:
entity.State = "SA";
break;
case 6:
entity.State = "NT";
break;
case 7:
entity.State = "WA";
break;
case 8:
entity.State = "TAS";
break;
}
switch (j)
{
case 1:
entity.ToiletAllowPerShift = Convert.ToDecimal(value);
break;
case 2:
entity.LeadingHandSmallGroup = Convert.ToDecimal(value);
break;
case 3:
entity.LeadingHandLargeGroup = Convert.ToDecimal(value);
break;
case 5:
entity.NumberOfHolidays = Convert.ToInt32(value);
break;
}
return true;
};
Save<AllowanceRate> save = entity => context.AllowanceRates.Add(entity);
ReadHorizontally("Quad Labour", "C134", "J138", copy, save);
}
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;
}
}
}
