public static void DFT_MoveRoomTypeDataForSHGroup()
{
var sourceCon = PMSDBManager.GetPMSConnectionString();
var destinationCon = CRMDBManager.GetCRMConnectionString();
var tableName = "dbo.L_Data_Dictionary";
var sql = PMSDBManager.SQL_GetRoomTypeDataForSHGroup;
var lSql = CRMDBManager.SQL_GetRoomTypesForSHGroup;
var lKeys = new Dictionary <string, string>();
lKeys.Add("CendynPropertyID", "CendynPropertyID");
lKeys.Add("FieldValue", "FieldValue");
lKeys.Add("FieldName", "FieldName");
var lMapping = new Dictionary <string, string>();
lMapping.Add("PropertyCode", "PropertyCode");
var primaryKeys = new List <string>()
{
"PropertyCode", "FieldValue", "FieldName"
};
var updateFields = new List <string>()
{
"Description", "Notes"
};
new DataFlowTask <L_Data_Dictionary, L_Data_Dictionary, L_Data_Dictionary>().runTask(sourceCon, destinationCon, destinationCon, tableName, sql, lSql, lKeys, lMapping, null, true, false, primaryKeys, updateFields);
}
public void Start()
{
var logResult = SSISLOGDBManager.LogPackageStart("eConciergeETL_Driver");
var BatchLogID = logResult["BatchLogID"];
var PackageLogID = logResult["PackageLogID"];
var EndBatchAudit = logResult["EndBatchAudit"];
CRMDBManager.UpdateRateTypeDictionary();
CRMDBManager.UpdateRoomTypeDictionary();
CRMDBManager.UpdateChannelCodeDictionary();
CRMDBManager.UpdateRoomCodeDictionary();
if ("1".Equals(CompanySettings.BringRateTypeDescFromCenRes) && CompanySettings.CompanyID == "7375")
{
DataDictionaryDataFlowTaskManager.DFT_MoveRateTypeDataForBiltmore();
DataDictionaryDataFlowTaskManager.DFT_UpdateShowAlphaCodeData();
CRMDBManager.UpdateNotesForRateType();
}
if (CompanySettings.CompanyID == "8012")
{
CRMDBManager.UpdateTargetTableName1AndTargetFieldName1ForRateType();
}
if (CompanySettings.CompanyID == "12960")
{
DataDictionaryDataFlowTaskManager.DFT_MoveRateTypeDataForSHGroup();
DataDictionaryDataFlowTaskManager.DFT_MoveRoomTypeDataForSHGroup();
}
if (CompanySettings.CompanyID == "12951")
{
DataDictionaryDataFlowTaskManager.DFT_MoveRateTypeDataFor12951();
}
if (CompanySettings.CompanyID == "8525")
{
DataDictionaryDataFlowTaskManager.DFT_MoveRateTypeDataForAquaAston();
}
SSISLOGDBManager.LogPackageEnd(PackageLogID, BatchLogID, EndBatchAudit);
}
public static void DFT_UpdateShowAlphaCodeData()
{
var sourceCon = CRMDBManager.GetCRMConnectionString();
var tableName = "dbo.L_Data_Dictionary";
var sql = CRMDBManager.SQL_GetShowAlphaCodeAndShowName;
var primaryKeys = new List <string>()
{
"PropertyCode", "FieldValue", "FieldName"
};
var updateFields = new List <string>()
{
"Description"
};
new DataFlowTask <L_Data_Dictionary>().runTask(sourceCon, sourceCon, tableName, sql, true, true, primaryKeys, updateFields);
}
public void Start()
{
ControlFlow.STAGE = "0";
var logger = new LogTask()
{
Message = "D_Customer Task",
ActionType = "Start"
};
logger.Info();
//Get Last CheckTime
ControlFlow.STAGE = (int.Parse(ControlFlow.STAGE) + 1) + "";
string lastCheckTime = CRMDBManager.GetLastCheckTime("D_CUSTOMER");
//Truncate Table ETL_TEMP_Profiles
//PMSDBManager.TruncateTable("ETL_TEMP_Profiles");
//TRUNCATE TABLE ETL_TEMP_PROFILES_D_CUSTOMER;
//TRUNCATE TABLE ETL_TEMP_PROFILES_D_CUSTOMER_INSERT;
//TRUNCATE TABLE ETL_TEMP_PROFILES_D_CUSTOMER_UPDATE;
//TRUNCATE TABLE ETL_TEMP_D_CUSTOMER_For_Email;
//CRMDBManager.TruncateTable("ETL_TEMP_PROFILES_D_CUSTOMER", "ETL_TEMP_PROFILES_D_CUSTOMER_INSERT", "ETL_TEMP_PROFILES_D_CUSTOMER_UPDATE", "ETL_TEMP_D_CUSTOMER_For_Email");
var companyId = CompanySettings.CompanyID;
if ("7375".Equals(companyId))
{
// Create Source Code For Open Table
string openTableSourceCode = CRMDBManager.CreateSourceCode(companyId, "CLIENT", "Open Table", 1, 99, 99);//SQLHelper.GetDbValues(strConnectionString, "sqlCreateSourceCodeForOpenTable", sqlCreateSourceCodeForOpenTable, null);
// Create Source Code For Counter Point
string counterPointSourceCode = CRMDBManager.CreateSourceCode(companyId, "CLIENT", "Counter Point", 1, 99, 99);//SQLHelper.GetDbValues(strConnectionString, "sqlCreateSourceCodeForCounterPoint", sqlCreateSourceCodeForCounterPoint, null);
}
//create source code
string pmsSourceCode = CRMDBManager.CreateSourceCode(companyId, "PMS", "PMS", 0, 1, 1);//SQLHelper.GetDbValues(strConnectionString, "sqlCreateSourceCode", sqlCreateSourceCode, null);
// Move ProfileDocument
CustomerDataFlowTaskManager.DFT_MoveProfileDocument();
// Move ProfilePolicies
CustomerDataFlowTaskManager.DFT_MoveProfilePolicies();
//Move ContactMethodPolicies
CustomerDataFlowTaskManager.DFT_MoveContactMethodPolicies();
//Move ContactMethod
CustomerDataFlowTaskManager.DFT_MoveContactMethod();
//Update SourceName under ContactMethod
CustomerDataFlowTaskManager.DFT_UpdateSourceNameUnderContactMethod();
//Move Action comments
CustomerDataFlowTaskManager.DFT_MoveActionComments();
//Move Address
CustomerDataFlowTaskManager.DFT_MoveAddress();
//Update AddressTypeCode under table PMS_Address
CRMDBManager.UpdateAddressTypeCodeUnderPMS_Address();
//Move special Requests - Omni
if ("12123".Equals(companyId) || "13358".Equals(companyId) || "13298".Equals(companyId))
{
CustomerDataFlowTaskManager.DFT_MoveSpecialRequests_Omni();
}
else
{
//Truncate CENRES_Specialrequests
CRMDBManager.TruncateTable("CENRES_SpecialRequests", "ETL_TEMP_Remove_SpecialRequests");
//Move Updated Profiles into Temp table
CustomerDataFlowTaskManager.DFT_MoveUpdatedProfilesIntoTempTable();
//Delete Global Special Requests for Updated Profiles
CRMDBManager.DeleteGlobalSpecialRequestsForUpdatedProfiles();
//Move the existing Special Requests
//Move Existing Special Requests
CRMDBManager.MoveExistingSpecialRequests();
//Move Special Requests
CustomerDataFlowTaskManager.DFT_MoveSpecialRequests_Omni();
//Remove ResortField for Global Request
CRMDBManager.RemoveResortFieldForGlobalRequest();
//Truncate CENRES_Specialrequests
CRMDBManager.TruncateTable("CENRES_SpecialRequests");
//Move Special Requests into temp table
//Delete Orphan records that don't match CenRes from PMS_SpecialRequests table
CRMDBManager.DeleteOrphanRecords();
}
if ("7375".Equals(companyId))
{
// Update PMS Profile Mapping for Biltmore
CRMDBManager.UpdatePMSProfileMappingForBiltmore();
}
else
{
//Update PMS Profile Mapping
CRMDBManager.UpdatePMSProfileMapping();
}
// Move the New or Updates in tehe profiles in the Temp Table
// Separate the Insert and Update into 2 temp tables
//Insert profiles in D_Customer without membership
CustomerDataFlowTaskManager.DFT_InsertOrUpdateD_CustomerWithoutMembership();
if ("1338".Equals(companyId) || "1475".Equals(companyId) || "6245".Equals(companyId) || "7902".Equals(companyId) || "8009".Equals(companyId) || "7692".Equals(companyId) || "11733".Equals(companyId) || "11761".Equals(companyId))
{
//Update profiles into D_Customer with membership
CustomerDataFlowTaskManager.DFT_InsertOrUpdateD_CustomerWithMembershipColumnUpdate();
}
else
{
//Update profiles into D_Customer without membership Column Update
CustomerDataFlowTaskManager.DFT_InsertOrUpdateD_CustomerWithoutMembershipColumnUpdate();
}
//Update property code
CustomerDataFlowTaskManager.DFT_UpdatePropertyCode();
//issue Move Profiles to PMS_Profiles and PMS_PROFILE_MAPPING
CustomerDataFlowTaskManager.DFT_MoveProfilesToPMS_ProfilesAndPMS_PROFILE_MAPPING();
//Move Profiles_Ext to PMS_Profiles_Ext
CustomerDataFlowTaskManager.DFT_MoveProfiles_ExtToPMS_Profiles_Ext();
//Move Birthday into D_Customer_Profile
CustomerDataFlowTaskManager.DFT_MoveBirthday();
//Move Anniversary into D_Customer_Profile
CustomerDataFlowTaskManager.DFT_MoveAnniversaryIntoD_Customer_Profile();
//Move SpouseBirthDate into D_Customer_Profile
CustomerDataFlowTaskManager.DFT_MoveSpouseBirthDateIntoD_Customer_Profile();
if ("11312".Equals(companyId))
{
//Update Preferred Language in PMS_Profiles table - Minor
CustomerDataFlowTaskManager.DFT_UpdatePreferredLanguageUnderPMS_Profiles();
//Update preferred language in D_Customer table - Minor
CRMDBManager.UpdatePreferredlanguageUnderD_Customer();
}
else
{
//Truncate temp tables
CRMDBManager.TruncateTable("ETL_TEMP_D_CUSTOMER_PHONE");
}
if ("12123".Equals(companyId) || "13298".Equals(companyId) || "13358".Equals(companyId))
{
//Update all address fields to blank - Omni
CRMDBManager.UpdateAllAddressFieldsToBlank();
}
//Update D_Customer table for Address
CustomerDataFlowTaskManager.DFT_UpdateD_CustomerForAddress();
if ("7375".Equals(companyId))
{
// Update Email in D_Customer for Biltmore
CustomerDataFlowTaskManager.DFT_UpdateEmailUnderD_Customer();
// Update Phone Numbers in D_Customer for Biltmore
CustomerDataFlowTaskManager.DFT_UpdatePhoneNumbersUnderD_Customer();
// Update Phone Ext in D_Customer for Biltmore
CustomerDataFlowTaskManager.DFT_UpdatePhoneExtUnderD_Customer();
}
else
{
//Remove inactive email records
CustomerDataFlowTaskManager.DFT_RemoveInactiveEmailRecords();
if ("10738".Equals(companyId))
{
//Update AddressType as W if it is U for Rydges under D_Customer table
CRMDBManager.UpdateAddressTypeUnderD_Customer();
}
//Update Email And Phone In D_Customer
CustomerDataFlowTaskManager.DFT_UpdateEmailAndPhoneUnderD_Customer();
if ("12123".Equals(companyId) || "13298".Equals(companyId) || "13358".Equals(companyId))
{
//Update all phone fields to blank - Omni
CRMDBManager.UpdateAllPhoneFieldsToBlank();
}
//Update Phones in D_Customer
CRMDBManager.UpdatePhonesUnderD_Customer();
}
// D_Customer Email maintenance
CRMDBManager.D_CustomerEmailMaintenance();
if ("6543".Equals(companyId))
{
//Update D_Customer_Email Rosewood
CustomerDataFlowTaskManager.DFT_UpdateD_Customer_Email();
}
//Fill Email table
//Move data from ETL_D_Customer_Email_Staging table to D_Customer_Email table
CustomerDataFlowTaskManager.DFT_UpSertDataIntoD_Customer_Email();
//Truncate table ETL_D_CUSTOMER_EMAIL_STAGING
CRMDBManager.TruncateTable("ETL_D_CUSTOMER_EMAIL_STAGING");
if ("10960".Equals(companyId))
{
//Update OptIn OptOut based on ProfilePolicies Data
CRMDBManager.UpdateOptInOptOut();
}
//Move EmailList type of data
CustomerDataFlowTaskManager.DFT_MoveEmailListTypeOfData();
//Move UDF31 Type Of Data
CustomerDataFlowTaskManager.DFT_MoveUDF31TypeOfData();
//Update VIPLevel based on UDFC31 type of data
CRMDBManager.UpdateVIPLevel();
if ("11757".Equals(companyId) || "12988".Equals(companyId) || "13006".Equals(companyId))
{
//Move KanaLastName
CustomerDataFlowTaskManager.DFT_MoveKanaLastName();
//Move KanaFirstName
CustomerDataFlowTaskManager.DFT_MoveKanaFirstName();
//Move NKanaLastName
CustomerDataFlowTaskManager.DFT_MoveNKanaLastName();
//Move NKanaFirstName
CustomerDataFlowTaskManager.DFT_MoveNKanaFirstName();
//Move NKanaName
CustomerDataFlowTaskManager.DFT_MoveNKanaName();
//Move UDFC37 Type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC37TypeOfData();
}
else if ("12123".Equals(companyId) || "13298".Equals(companyId) || "13358".Equals(companyId))
{
//Move Always_Email_Folio data
CustomerDataFlowTaskManager.DFT_MoveAlways_Email_DolioData();
//Move GHA_Email Data
CustomerDataFlowTaskManager.DFT_MoveGHA_EmailData();
//Move TOTEL_OFFER_EMAIL Data
CustomerDataFlowTaskManager.DFT_MoveHotel_Offer_EmailData();
//Move Marketing_Email Data
CustomerDataFlowTaskManager.DFT_MoveMarketing_EmailData();
//Move Marketing_Print Data
CustomerDataFlowTaskManager.DFT_MoveMarketing_PrintData();
//Move Mokara_Email Data
CustomerDataFlowTaskManager.DFT_MoveMokara_EmailData();
//Move New_Card_Request Data
CustomerDataFlowTaskManager.DFT_MoveNew_Card_RequestData();
//Move NewsLetter_Email Data
CustomerDataFlowTaskManager.DFT_MoveNewsLetter_EmailData();
//Move SG_ACCOUNT_SUMMARY Data
CustomerDataFlowTaskManager.DFT_MoveSG_Account_SummaryData();
//Move Transaction_Email Data
CustomerDataFlowTaskManager.DFT_MoveTransaction_EmailData();
//Move SG_Email Data
CustomerDataFlowTaskManager.DFT_MoveSG_EmailData();
//Move UDFData
CustomerDataFlowTaskManager.DFT_MoveUDFData();
}
else
{
//Move UDFC37 Type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC37TypeOfData();
}
if ("7375".Equals(companyId))
{
//Move Sales Rep UDF Data
CustomerDataFlowTaskManager.DFT_MoveSalesRepUDFData();
//Move Est Rate UDF Data
CustomerDataFlowTaskManager.DFT_MoveEstrateUDFData();
//Move Depart UDF Data
CustomerDataFlowTaskManager.DFT_MoveDepartUDFData();
//Move Arrival UDF Data
CustomerDataFlowTaskManager.DFT_MoveArrivalUDFData();
//Move Block Ref UDF Data
CustomerDataFlowTaskManager.DFT_MoveBlockRefUDFData();
//Move Market Segment UDF Data
CustomerDataFlowTaskManager.DFT_MoveMarketSegmentUDFData();
//Move Market SubSegment UDF Data
CustomerDataFlowTaskManager.DFT_MoveMarketSubSegmentUDFData();
//Move Source UDF Data
CustomerDataFlowTaskManager.DFT_MoveSourceUDFData();
//Move Notes UDF Data
CustomerDataFlowTaskManager.DFT_MoveNotesUDFData();
//Move Banned UDF Data
CustomerDataFlowTaskManager.DFT_MoveBannedUDFData();
//Move Address3 UDF Data
CustomerDataFlowTaskManager.DFT_MoveAddress3UDFData();
//Move Alternate UDF Data
CustomerDataFlowTaskManager.DFT_MoveAlternateUDFData();
//Update CompanyName from ContactMethod table
CustomerDataFlowTaskManager.DFT_UpdateCompanyNameFromContactMethod();
//Move cnotes UDF Data for OpenTable
CustomerDataFlowTaskManager.DFT_MoveCNotesUDFData();
//Move count_cancel UDF Data for OpenTable
CustomerDataFlowTaskManager.DFT_MoveCount_CancelUDFData();
//Move count_noshow UDF Data for OpenTable
CustomerDataFlowTaskManager.DFT_MoveCount_NoshowUDFData();
//Move count_resos UDF Data for OpenTable
CustomerDataFlowTaskManager.DFT_MoveCount_ResosUDFData();
//Move custcodes UDF Data for OpenTable
CustomerDataFlowTaskManager.DFT_MoveCustcodesUDFData();
//Move BAPExpiration UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveBAPExpirationUDFData();
//Move ChildMembership UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveChildMembershipUDFData();
//Move Comment UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveCommentUDFData();
//Move CreatedOnline UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveCreatedOnlineUDFData();
//Move CustomerCode UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveCustomerCodeUDFData();
//Move HasOnlineProfile UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveHasOnlineProfileUDFData();
//Move LastOnlineOrderDate UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveLastOnlineOrderDateUDFData();
//Move LastOnlineOrderNumber UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveLastOnlineOrderNumberUDFData();
//Move OmniPassBarcode UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveOmniPassBarcodeUDFData();
//Move OmniPassID UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveOmniPassIDUDFData();
//Move OnlineCustomerNumber UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveOnlineCustomerNumberUDFData();
//Move ProductDelivery UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MoveProductDeliveryUDFData();
//Move PROF_COD_5 UDF Data for counterpoint
CustomerDataFlowTaskManager.DFT_MovePROF_COD_5UDFData();
}
if ("11964".Equals(companyId))
{
//Move UDFC20 type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC20TypeOfData();
//Move UDFC22 type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC22TypeOfData();
//Move UDFC23 type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC23TypeOfData();
//Move UDFC33 type of data
CustomerDataFlowTaskManager.DFT_MoveUDFC33TypeOfData();
}
//DTF Connector
//EmailVerify With Bounce Rules
CRMDBManager.EmailVerifyWithBounceRules();
// EmailVerify on D_Customer
CRMDBManager.EmailVerifyOnD_Customer();
if ("6543".Equals(companyId))
{
//EmailVerify on D_CUSTOMER_EMAIL - Rosewood
CRMDBManager.EmailVerifyOnD_CUSTOMER_EMAIL_Rosewood();
}
else
{
//Move Emails From D_Customer To D_Customer_Email _NonRosewood
CustomerDataFlowTaskManager.DFT_MoveEmailsFromD_CustomerToD_Customer_Email_NonRosewood();
//Fill up missing data under D_Customer_Email table
CRMDBManager.FillUpMissingDataUnderD_Customer_Email();
//Fix emails which are not matching under D_Customer_Email table
CRMDBManager.FixEmailsDataUnderD_Customer_Email();
//Fix email_id under D_Customer_Email table
CRMDBManager.FixEmail_IDUnderD_Customer_Email();
}
if ("12123".Equals(companyId) || "13298".Equals(companyId) || "13358".Equals(companyId))
{
//Update Inactive profiles record status - omni
CustomerDataFlowTaskManager.DFT_UpdateRecordStatusUnderPMS_Profiles();
CustomerDataFlowTaskManager.DFT_UpdateRecordStatusUnderD_Customer();
}
if ("1".Equals(CompanySettings.ETL_HASUNIFOCUSDATA))//@HasUNIFOCUSData == 1
{
//Update Unifocus score per customer
CRMDBManager.UpdateUnifocusScorePerCustomer();
}
// Insert EndTime into Log Table
//CRMDBManager.InsertEndTimeIntoLogTable();
logger.ActionType = "END";
logger.Info();
ControlFlow.STAGE = (int.Parse(ControlFlow.STAGE) - 1) + "";
}
