public JsonResult SaveInformationToDatabase(int franchiseId, long questionnaireId, long businessInformationId, string services, string cellPhone, string officePhone)
{
List <ZipCode> zipCodes = db.ZipCode.Where(z => z.BusinessInformationId == businessInformationId).ToList();
List <Dba> dbas = db.Dba.Where(z => z.BusinessInformationId == businessInformationId).ToList();
List <double?> itaxRates = new List <double?>();
foreach (var z in zipCodes)
{
if (z.TaxRate != null)
{
itaxRates.Add(z.TaxRate);
}
}
IEnumerable <double?> taxRates = itaxRates.Distinct();
int insertedZipRows, skipedZipRows, insertedDBARows,
skipedDBARows, insertedTaxRows, skipedTaxRows,
insertedServiceRows, skipedServiceRows, insertedContactRows,
skipedContactRows;
int totalZipRows, totalDBARows, totalTaxRows, totalServiceRows, totalContactRows;
string contactName = "";
// Initialize static data
insertedZipRows = 0;
skipedZipRows = 0;
insertedDBARows = 0;
skipedDBARows = 0;
insertedTaxRows = 0;
skipedTaxRows = 0;
insertedServiceRows = 0;
skipedServiceRows = 0;
insertedContactRows = 0;
skipedContactRows = 0;
totalZipRows = 0;
totalDBARows = 0;
totalTaxRows = 0;
totalServiceRows = 0;
totalContactRows = 0;
string errorMessage = "";
bool success = false;
var franchise = edb.tbl_Franchise.Single(f => f.FranchiseID == franchiseId);
// Contact information
if (((cellPhone != null) && (cellPhone != "")) || ((officePhone != null) && (officePhone != "")))
{
var ownerInformation = db.OwnerInformation.Where(oi => oi.QuestionnaireId == questionnaireId).ToList();
if ((ownerInformation != null) && (ownerInformation.Count > 0))
{
contactName = ownerInformation[0].FirstName + " " + ownerInformation[0].LastName;
}
}
using (TransactionScope transation = new TransactionScope())
{
try
{
// Cell Phone
if ((cellPhone != null) && (cellPhone != ""))
{
string[] phoneElements = cellPhone.Split(new char[] { '-' });
cellPhone = string.Format("({0}) {1}-{2}", phoneElements[0], phoneElements[1], phoneElements[2]);
// Check if phone exists in tbl_Franchise_Contacts
totalContactRows = (from fphone in edb.tbl_Franchise_Contacts
where (fphone.FranchiseID == franchiseId && fphone.PhoneNumber == cellPhone && fphone.PhoneTypeID == 4)
select fphone.FranchiseContactID).Count();
if (totalContactRows == 0)
{
// Insert a new row
tbl_Franchise_Contacts contactToInsert = new tbl_Franchise_Contacts()
{
FranchiseID = franchise.FranchiseID,
ContactName = contactName,
PhoneTypeID = 4,
PhoneNumber = cellPhone
};
edb.tbl_Franchise_Contacts.AddObject(contactToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedContactRows++;
}
else
{
skipedContactRows++;
}
}
// Office Phone
if ((officePhone != null) && (officePhone != ""))
{
string[] phoneElements = officePhone.Split(new char[] { '-' });
officePhone = string.Format("({0}) {1}-{2}", phoneElements[0], phoneElements[1], phoneElements[2]);
// Check if phone exists in tbl_Franchise_Contacts
totalContactRows = (from fphone in edb.tbl_Franchise_Contacts
where (fphone.FranchiseID == franchiseId && fphone.PhoneNumber == cellPhone && fphone.PhoneTypeID == 5)
select fphone.FranchiseContactID).Count();
if (totalContactRows == 0)
{
// Insert a new row
tbl_Franchise_Contacts contactToInsert = new tbl_Franchise_Contacts()
{
FranchiseID = franchise.FranchiseID,
ContactName = contactName,
PhoneTypeID = 5,
PhoneNumber = officePhone
};
edb.tbl_Franchise_Contacts.AddObject(contactToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedContactRows++;
}
else
{
skipedContactRows++;
}
}
// Services
if ((services != null) && (services != ""))
{
string[] serviceElements = services.Split(new char[] { ',' });
for (int i = 0; i < serviceElements.Length; i++)
{
// Insert a new row
int serviceId = int.Parse(serviceElements[i]);
totalServiceRows = (from fservice in edb.tbl_Franchise_Services
where (fservice.FranchiseID == franchiseId && fservice.ServiceID == serviceId)
select fservice.FranchiseeServiceID).Count();
if (totalServiceRows == 0)
{
tbl_Franchise_Services serviceToInsert = new tbl_Franchise_Services()
{
FranchiseID = franchise.FranchiseID,
ServiceID = int.Parse(serviceElements[i].ToString())
};
edb.tbl_Franchise_Services.AddObject(serviceToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedServiceRows++;
}
else
{
skipedServiceRows++;
}
}
}
// ZIP
foreach (var zip in zipCodes)
{
// Check if ZipCode exists in tbl_Franchise_ZipList
totalZipRows = (from fzip in edb.tbl_Franchise_ZipList
where (fzip.FranchiseID == franchiseId && fzip.FranchiseZipID == zip.ZipCodeNumber)
select fzip.ZipID).Count();
if (totalZipRows == 0)
{
// Insert a new row
tbl_Franchise_ZipList zipToInsert = new tbl_Franchise_ZipList()
{
FranchiseZipID = zip.ZipCodeNumber,
FranchiseID = franchise.FranchiseID,
ActiveYN = true,
DateAdded = DateTime.Now,
DateRemoved = null,
OwnedYN = false,
ServicesYN = true,
City = zip.Comment.Length >= 50 ? zip.Comment.Substring(0, 47) + "..." : zip.Comment,
State = franchise.LegalState,
Country = "USA",
CallTakerMessage = null
};
edb.tbl_Franchise_ZipList.AddObject(zipToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedZipRows++;
}
else
{
skipedZipRows++;
}
}
// DBA
foreach (var dba in dbas)
{
// Check if DBA Name exists in tbl_Dispatch_DBA
totalDBARows = (from fdba in edb.tbl_Dispatch_DBA
where (fdba.FranchiseID == franchiseId && fdba.DBAName == dba.DbaName)
select fdba.DBAID).Count();
if (totalDBARows == 0)
{
// Insert a new row
tbl_Dispatch_DBA dbaToInsert = new tbl_Dispatch_DBA()
{
FranchiseID = franchise.FranchiseID,
DBAName = dba.DbaName,
DBAIMage = null
};
edb.tbl_Dispatch_DBA.AddObject(dbaToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedDBARows++;
}
else
{
skipedDBARows++;
}
}
// Tax Rate
foreach (var tax in taxRates)
{
// Check if Tax Rate exists in tbl_TaxRates
totalTaxRows = (from ftax in edb.tbl_TaxRates
where (ftax.FranchiseId == franchiseId && ftax.LaborAmount == tax.Value)
select ftax.TaxRateID).Count();
if (totalTaxRows == 0)
{
// Insert a new row
tbl_TaxRates taxToInsert = new tbl_TaxRates()
{
FranchiseId = franchise.FranchiseID,
TaxDescription = tax.Value.ToString(),
LaborAmount = (float)tax.Value,
PartsAmount = (float)tax.Value,
AccountCode = "20601", // TODO Anis: find what to insert into account code
ActiveYN = true
};
edb.tbl_TaxRates.AddObject(taxToInsert);
edb.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
insertedTaxRows++;
}
else
{
skipedTaxRows++;
}
}
// Mark the transaction as complete
transation.Complete();
success = true;
}
catch (Exception ex)
{
errorMessage = ex.Message;
}
}
if (success)
{
edb.AcceptAllChanges();
return(Json(new
{
Message = "All data updated with success.",
ContactRowInserted = insertedContactRows,
ContactRowSkiped = skipedContactRows,
ZIPRowInserted = insertedZipRows,
ZIPRowSkiped = skipedZipRows,
DBARowInserted = insertedDBARows,
DBARowSkiped = skipedDBARows,
TaxRowInserted = insertedTaxRows,
TaxRowSkiped = skipedTaxRows,
ServiceRowInserted = insertedServiceRows,
ServiceRowSkiped = skipedServiceRows,
Success = true
}));
}
return(Json(new
{
Message = errorMessage,
Success = false
}));
}