public bool Finalize(PersonalCrmRecordDto dto)
{
bool retval = false;
DbCommand sp = null;
DbConnection connection = null;
IDataReader reader = null;
try
{
connection = _dbLayer.GetConnection();
sp = connection.CreateCommand();
sp.CommandText = "update_crm_records";
sp.CommandType = CommandType.StoredProcedure;
//SMALLINT = 0, --0 update flags, 1 update profile data
_dbLayer.AddInParameter(sp, "@mode", DbType.Int16, 1);
//required for export in RfgJob: "@col_flag", "@old_flag", "@new_flag", "@entry_id"
Write(sp, dto);
sp.ExecuteNonQuery();
retval = true;
}
catch (DbException e)
{
Trace.WriteLine("ResponseDao.Finalize(" + dto + "): " + e.Message);
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (sp != null)
{
sp.Dispose();
}
if (connection != null)
{
_dbLayer.ReturnConnection(connection);
}
else
{
_dbLayer.ReturnConnection(connection, true);
}
}
return retval;
}
private void Write(DbCommand sp, PersonalCrmRecordDto dto)
{
_dbLayer.AddInParameter(sp, "@response_id", DbType.Int64, dto.ResponseKey);
_dbLayer.AddInParameter(sp, "@company_id", DbType.String, dto.CompanyId);
_dbLayer.AddInParameter(sp, "@company_name", DbType.String, dto.CompanyName);
_dbLayer.AddInParameter(sp, "@company_website", DbType.String, dto.CompanyWebsite); //RFG 2.11 | Added by Raju
_dbLayer.AddInParameter(sp, "@company_revenue", DbType.String, dto.CompanyRevenue);
_dbLayer.AddInParameter(sp, "@country_FK", DbType.String, dto.CountryCode);
_dbLayer.AddInParameter(sp, "@address1", DbType.String, dto.Address1);
_dbLayer.AddInParameter(sp, "@address2", DbType.String, dto.Address2);
_dbLayer.AddInParameter(sp, "@address3", DbType.String, dto.Address3);
_dbLayer.AddInParameter(sp, "@town", DbType.String, dto.Town);
_dbLayer.AddInParameter(sp, "@postcode", DbType.String, dto.Postcode);
_dbLayer.AddInParameter(sp, "@county", DbType.String, dto.County);
_dbLayer.AddInParameter(sp, "@number_of_employees", DbType.String, dto.NumberOfEmployees);
_dbLayer.AddInParameter(sp, "@siebel_con_id", DbType.String, dto.SiebelConId);
_dbLayer.AddInParameter(sp, "@first_name", DbType.String, dto.FirstName);
_dbLayer.AddInParameter(sp, "@last_name", DbType.String, dto.LastName);
_dbLayer.AddInParameter(sp, "@jobtitle", DbType.String, dto.JobTitle);
_dbLayer.AddInParameter(sp, "@personal_title", DbType.String, dto.PersonalTitle);
_dbLayer.AddInParameter(sp, "@language_FK", DbType.String, dto.LanguageCode);
_dbLayer.AddInParameter(sp, "@language_name", DbType.String, dto.LanguageName);
//for cid _dbLayer.AddInParameter(sp, "@telephoneCountry", DbType.String, dto.TelephoneCountry);
//for cid _dbLayer.AddInParameter(sp, "@telephoneArea", DbType.String, dto.TelephoneArea);
//for cid _dbLayer.AddInParameter(sp, "@telephoneNumber", DbType.String, dto.TelephoneNumber);
_dbLayer.AddInParameter(sp, "@telephone", DbType.String, dto.TelephoneCountry + dto.TelephoneArea + dto.TelephoneNumber);
_dbLayer.AddInParameter(sp, "@telephone_extension", DbType.String, dto.TelephoneExt);
//for cid _dbLayer.AddInParameter(sp, "@faxCountry", DbType.String, dto.FaxCountry);
//for cid _dbLayer.AddInParameter(sp, "@faxArea", DbType.String, dto.FaxArea);
//for cid _dbLayer.AddInParameter(sp, "@faxNumber", DbType.String, dto.FaxNumber);
_dbLayer.AddInParameter(sp, "@fax", DbType.String, dto.FaxCountry + dto.FaxArea + dto.FaxNumber + dto.FaxExt);
//for cid _dbLayer.AddInParameter(sp, "@faxExt", DbType.String, dto.FaxExt);
//for cid _dbLayer.AddInParameter(sp, "@mobileCountry", DbType.String, dto.MobileCountry);
//for cid _dbLayer.AddInParameter(sp, "@mobileNumber", DbType.String, dto.MobileNumber);
_dbLayer.AddInParameter(sp, "@mobile", DbType.String, dto.MobileCountry + dto.MobileNumber);
_dbLayer.AddInParameter(sp, "@email_address", DbType.String, dto.EmailAddress);
_dbLayer.AddInParameter(sp, "@email_pref", DbType.String, dto.EmailPreferences);
_dbLayer.AddInParameter(sp, "@siebel_prosp_id", DbType.String, dto.SiebelProspId);
_dbLayer.AddInParameter(sp, "@wavecode", DbType.String, dto.WaveCode);
_dbLayer.AddInParameter(sp, "@flexfield_jumpid", DbType.String, dto.FlexFieldJumpId);
_dbLayer.AddInParameter(sp, "@flexfield_customquestions", DbType.String, dto.CustomQuestionsSummary);
// Added by kalyan for 2.2 Release
_dbLayer.AddInParameter(sp, "@flexfield10", DbType.String, dto.FlexField10);
_dbLayer.AddInParameter(sp, "@flexfield11", DbType.String, dto.FlexField11);
_dbLayer.AddInParameter(sp, "@flexfield12", DbType.String, dto.FlexField12);//††† 20120410 Parthasarathy | RFG 2.6 Support Sub_bu capture †††
//RFG 2.11 | FOR watch list
_dbLayer.AddInParameter(sp, "@flexfield14", DbType.String, dto.FlexField14);
_dbLayer.AddInParameter(sp, "@campaign_code", DbType.String, dto.CampaignCode);
_dbLayer.AddInParameter(sp, "@company", DbType.Int32, dto.Company_Code); //RFG 2.20
}
public void SubmitCrmData(string CountryCode, string LanguageCode,int Company_Code)
{
#region init variables and flex fields
string address1 = string.Empty;
string address2 = string.Empty;
string address3 = string.Empty;
string businessCode = string.Empty;
string businessSiebel63 = string.Empty;
string companyName = string.Empty;
//RFG 2.11 | Added by Raju
string companyWebsite = string.Empty;
string companyRevenue = string.Empty;
string companyRevenueCode = string.Empty;
string countryCode = string.Empty;
string county = string.Empty;
string customerId = CustomerID;
string emailAddress = string.Empty;
string firstName = string.Empty;
string flexField1 = string.Empty;
string flexField10 = string.Empty;
string flexField11 = string.Empty;
string flexField12 = string.Empty;
string flexField13 = string.Empty;
string flexField14 = string.Empty;
string flexField15 = string.Empty;
string flexfield2 = string.Empty;
string flexField3 = string.Empty;
string flexField4 = string.Empty;
string flexField5 = string.Empty;
string flexField6 = string.Empty;
string flexField7 = string.Empty;
string flexField8 = string.Empty;
string flexField9 = string.Empty;
string jobCode = string.Empty;
string jobSiebel63 = string.Empty;
string jobTitle = string.Empty;
string lastName = string.Empty;
string personalTitle = string.Empty;
string postcode = string.Empty;
string pwd = string.Empty;
string telephoneCountry = string.Empty;
string telephoneArea = string.Empty;
string telephoneNumber = string.Empty;
string telephoneExt = string.Empty;
string faxCountry = string.Empty;
string faxArea = string.Empty;
string faxNumber = string.Empty;
string faxExt = string.Empty;
string mobileCountry = string.Empty;
string mobileNumber = string.Empty;
string town = string.Empty;
string worldSize = string.Empty;
string worldSize63 = string.Empty;
string countryName = string.Empty;
EmailPreferences? emailPreferences = null;
string EmailPref = null;
#endregion
string CamaignCode = string.Empty;
string WaveCode = null; //QC 6166
string JumpId = null;
string languageFK = string.Empty;
string languageName = string.Empty;
string numberOfEmployees = string.Empty;
PersonalResponseDto ResponseDto = null;
FormSubmissionPersonalSectionLabels ObjPersonalLabels = new FormSubmissionPersonalSectionLabels();
FormSubmissionPrivacySectionLabels ObjPrivacyLabels = new FormSubmissionPrivacySectionLabels(); //QC:6159 & 6160
// EmailPreferences? emailPreferences = null;
if (HashPersonalFields != null)
{
if (HashPersonalFields.Count > 0)
{
foreach (DictionaryEntry Item in HashPersonalFields)
{
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Address1)
{
address1 = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Address2)
{
address2 = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Address3)
{
address3 = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.PreferredLanguage)
{
string[] ArrayPreferredLanguage = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayPreferredLanguage = Item.Value.ToString().Split(',');
if (ArrayPreferredLanguage[0] != null)
languageFK = ArrayPreferredLanguage[0];
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.CompanyName)
{
companyName = Item.Value.ToString();
}
//RFG 2.11 | Added by Raju
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.CompanyWebsite)
{
companyWebsite = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.CompanyRevenue)
{
string[] ArrayCompanyRevenue = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayCompanyRevenue = Item.Value.ToString().Split(',');
if (ArrayCompanyRevenue.Length > 0)
{
if (ArrayCompanyRevenue[0] != null)
companyRevenueCode = ArrayCompanyRevenue[0].ToString();
if (ArrayCompanyRevenue[1] != null)
companyRevenue = ArrayCompanyRevenue[1].ToString();
}
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Email_Address)
{
emailAddress = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Email_Pref)
{
string[] ArrayEmailPref = new string[1];
//string EmailPref = string.Empty;
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayEmailPref = Item.Value.ToString().Split(',');
if (ArrayEmailPref[0] != null)
EmailPref = ArrayEmailPref[0];
}
}
if (EmailPref.ToLower() == "html")
emailPreferences = EmailPreferences.Html;
if (EmailPref.ToLower() == "text")
emailPreferences = EmailPreferences.Text;
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.County)
{
county = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Country_code)
{
string[] ArrayCountry = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayCountry = Item.Value.ToString().Split(',');
if (ArrayCountry[0] != null)
countryCode = ArrayCountry[0];
}
}
if (countryCode == "")
countryCode = CountryCode;
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.JobCode)
{
string[] ArrayJob = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayJob = Item.Value.ToString().Split(',');
if (ArrayJob[0] != null)
jobCode = ArrayJob[0];
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Business_Code)
{
string[] ArrayBusinessCode = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayBusinessCode = Item.Value.ToString().Split(',');
if (ArrayBusinessCode[0] != null)
businessCode = ArrayBusinessCode[0];
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.FirstName)
{
firstName = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.JobTitle)
{
jobTitle = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.LastName)
{
lastName = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.WorlSize_Code)
{
string[] ArrayWorldSize_fk = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayWorldSize_fk = Item.Value.ToString().Split(',');
if (ArrayWorldSize_fk.Length > 0)
{
if (ArrayWorldSize_fk[0] != null)
worldSize = ArrayWorldSize_fk[0].ToString();
if (ArrayWorldSize_fk[1] != null)
numberOfEmployees = ArrayWorldSize_fk[1].ToString();
}
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Title)
{
string[] ArrayTitle = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty)
{
ArrayTitle = Item.Value.ToString().Split(',');
if (ArrayTitle.Length > 0)
{
if (ArrayTitle[1] != null)
personalTitle = ArrayTitle[1].ToString();
}
}
}
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.PostCode)
{
postcode = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Telephone_Country)
{
telephoneCountry = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Telephone_Area)
{
telephoneArea = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Telephone)
{
telephoneNumber = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Telephone_Extn)
{
telephoneExt = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Fax_Country)
{
faxCountry = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Fax_Area)
{
faxArea = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Fax)
{
faxNumber = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Fax_Extn)
{
faxExt = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Mobile_Country)
{
mobileCountry = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Mobile)
{
mobileNumber = Item.Value.ToString();
}
if (Item.Key.ToString() == "aid_p_" + ObjPersonalLabels.Town)
{
town = Item.Value.ToString();
}
#region flexfileds
if (Item.Key.ToString() == ObjPersonalLabels.flexfield1)
{
flexField1 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield2)
{
flexfield2 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield3)
{
flexField3 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield4)
{
flexField4 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield5)
{
flexField5 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield6)
{
flexField6 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield7)
{
flexField7 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield8)
{
flexField8 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield9)
{
flexField9 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield10)
{
//flexField10 = Item.Value.ToString(); //RFG 2.12 QC:6946
flexField10 = Item.Value.ToString().Equals("") ? null : Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield11)
{
flexField11 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield12)
{
flexField12 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield13)
{
flexField13 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield14)
{
flexField14 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.flexfield15)
{
flexField15 = Item.Value.ToString();
}
if (Item.Key.ToString() == ObjPersonalLabels.campaign_code)
{
CamaignCode = Item.Value.ToString();
if (string.IsNullOrEmpty(CamaignCode.Trim()))
CamaignCode = null;
}
if (Item.Key.ToString() == ObjPersonalLabels.wavecode)
{
WaveCode = Item.Value.ToString();
//QC6166 | Tirumala Raju
//if (string.IsNullOrEmpty(WaveCode.Trim()))
// WaveCode = null;
}
if (Item.Key.ToString() == ObjPersonalLabels.JumpId)
{
JumpId = Item.Value.ToString();
//if (string.IsNullOrEmpty(JumpId.Trim()))
// JumpId = null;
}
#endregion
}
}
}
//20120803 | Tirumala Raju | RFG 2.8 | QC:6159 & QC:6160 |Start
if (HashPrivacyFields != null)
{
foreach (DictionaryEntry Item in HashPrivacyFields)
{
if (Item.Key.ToString() == "aid_o_" + ObjPrivacyLabels.Privacy_Email_Pref)
{
string[] ArrayEmailPref = new string[1];
if (Item.Value != null)
{
if (Item.Value.ToString().Trim() != string.Empty.ToString())
{
ArrayEmailPref = Item.Value.ToString().Split(',');
if (ArrayEmailPref[0] != null)
EmailPref = ArrayEmailPref[0];
}
}
emailPreferences = EmailPref.Trim().ToLower() == "html" ? EmailPreferences.Html : EmailPreferences.Text;
}
}
}
//20120308 | Tirumala Raju | RFG 2.8 | QC:6159 & QC:6160 |End
if (countryCode == String.Empty)
countryCode = CountryCode;
if (CamaignCode != null)
{
if (string.IsNullOrEmpty(CamaignCode.Trim()))
CamaignCode = null;
}
//QC6166
//if (string.IsNullOrEmpty(WaveCode))
// WaveCode = null;
//if (string.IsNullOrEmpty(JumpId))
// JumpId = null;
if (worldSize != null)
{
if (worldSize.Trim() != string.Empty.ToString())
{
QuestionnaireDto QT = null;
QT = new QuestionnaireDto(
QuestionnaireType.Questionnaire, null, null, countryCode, LanguageCode,
null, false, DateTime.Now, null, false,
null, false, false, false, null, false,
false, null, null, null, null, 0, 0, DateTime.Now, null, null, false, null, null,
null, null, null, null, null, null, null, null, null, null, null, false, false, null, 0, null,false,0,0,false,0,0,null,0,0,0,2);
if (worldSize.Trim() != string.Empty.ToString())
numberOfEmployees = ResponseFacade.GetSelctedText(QT, "WorldSize", worldSize);
}
}
//RFG 2.11
if ((companyRevenue != null) && (companyRevenue.Trim() != string.Empty))
{
QuestionnaireDto QT = null;
QT = new QuestionnaireDto(
QuestionnaireType.Questionnaire, null, null, countryCode, LanguageCode,
null, false, DateTime.Now, null, false,
null, false, false, false, null, false,
false, null, null, null, null, 0, 0, DateTime.Now, null, null, false, null, null,
null, null, null, null, null, null, null, null, null, null, null, false, false, null, 0, null,false,0,0,false,0,0,null,0,0,0,2); //Balakumar Sprint2
companyRevenue = ResponseFacade.GetSelctedText(QT, "CompanyRevenue", companyRevenueCode);
}
//try to translate into CRM english value... for the namevalue passed and from state_locals where in_country_fk and in_language_Fk = questionnaire settings?
//if possible overwrite value from county otherwise leave it as it was
if (!String.IsNullOrEmpty(county))
{
StandardCountyDto sCounty = FormRegistry.ResponseDao.GetStandardCountyByLocalName(county, CountryCode, LanguageCode, countryCode);
if (sCounty != null)
{
county = sCounty.CountyName;
}
}
SiebelLanguageDto sLanguage = FormRegistry.ResponseDao.GetSiebelLanguage(languageFK);
if (sLanguage != null)
{
languageFK = sLanguage.LanguageFK;
languageName = sLanguage.LanguageName;
}
PersonalCrmRecordDto ResponsePersonalCrm = null;
//Comapany Value------RFG:2.20
ResponsePersonalCrm = new PersonalCrmRecordDto(Convert.ToInt64(ResponseID), address1, address2, address3,
string.Empty, companyName,companyWebsite,companyRevenue, county, countryCode, emailAddress, emailPreferences.ToString(),
firstName, JumpId, jobTitle, languageFK, lastName, numberOfEmployees,
personalTitle, postcode, string.Empty, string.Empty, telephoneCountry, telephoneArea,
telephoneNumber, telephoneExt, faxCountry, faxArea, faxNumber, faxExt, mobileCountry,
mobileNumber, town, WaveCode, customquestionssummary.ToString(), flexField10, flexField11, flexField12, flexField14, null, languageName,Company_Code);//††† 20120410 Parthasarathy | RFG 2.6 Support Sub_bu capture ††† RFG 2.9
FormRegistry.ResponseDao.Finalize(ResponsePersonalCrm);
responses.Add(ResponsePersonalCrm);
}