/// <summary>
/// Insert customer detail.
/// </summary>
/// <param name="xmlData">Data that converted into xml format.</param>
/// <returns>Returns 1 and 0; (1 indicates successful operation).</returns>
public int InsertCustomerRelation(CustomerBDto customer)
{
Database db = null;
DbCommand dbCmd = null;
int Result = 0;
try
{
db = DatabaseFactory.CreateDatabase(DALHelper.CRM_CONNECTION_STRING);
dbCmd = db.GetStoredProcCommand(DALHelper.USP_CUST_CUSTOMER_RELATION_DETAILS_INSERT);
db.AddInParameter(dbCmd, "@CUST_ID", DbType.Int32, customer.CustomerId);
db.AddInParameter(dbCmd, "@CUST_REL_ID", DbType.Int32, customer.RelationId);
db.AddInParameter(dbCmd, "@CUST_REL_TITLE", DbType.Int32, customer.TitleId);
db.AddInParameter(dbCmd, "@CUST_REL_SURNAME", DbType.String, customer.SurName);
db.AddInParameter(dbCmd, "@CUST_REL_NAME", DbType.String, customer.Name);
db.AddInParameter(dbCmd, "@CUST_REL_GENDER", DbType.String, customer.GeneralInfo.Gender);
db.AddInParameter(dbCmd, "@CUST_REL_MARITAL_STATUS_ID", DbType.Int32, customer.GeneralInfo.MarriageStatusId);
if (customer.GeneralInfo.MarriageDate == DateTime.MinValue)
{
db.AddInParameter(dbCmd, "@CUST_REL_MARRIAGE_DATE", DbType.DateTime, DBNull.Value);
}
else
{
db.AddInParameter(dbCmd, "@CUST_REL_MARRIAGE_DATE", DbType.DateTime, customer.GeneralInfo.MarriageDate);
}
db.AddInParameter(dbCmd, "@CUST_REL_RELIGION_ID", DbType.Int32, customer.GeneralInfo.ReligionId);
db.AddInParameter(dbCmd, "@CUST_REL_EMAIL", DbType.String, customer.ContactInfo.EmailId);
db.AddInParameter(dbCmd, "@CUST_REL_MOBILE", DbType.String, customer.ContactInfo.MobileNo);
db.AddInParameter(dbCmd, "@CUST_REL_PHONE", DbType.String, customer.ContactInfo.PhoneNo);
db.AddInParameter(dbCmd, "@CUST_REL_PROFESSION_ID", DbType.Int32, customer.ProfessionId);
db.AddInParameter(dbCmd, "@CUST_REL_ANNUAL_INCOME", DbType.Decimal, customer.AnnualIncome);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_NO", DbType.String, customer.PassPortInfo.PassportNo);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_DATE", DbType.DateTime, customer.PassPortInfo.IssueDate);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_PLACE", DbType.String, customer.PassPortInfo.IssuePlace);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_EXPIRY_DATE", DbType.DateTime, customer.PassPortInfo.EntryDate);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_PRINTED_NAME", DbType.String, customer.PassPortInfo.PrintName);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_COUNTRY", DbType.String, customer.PassPortInfo.IssueCountry);
db.AddInParameter(dbCmd, "@CUST_REL_STATE", DbType.String, customer.RelationState);
db.AddInParameter(dbCmd, "@USER_ID", DbType.Int32, customer.UserId);
db.AddOutParameter(dbCmd, "@IS_INSERT", DbType.Int32, 1);
db.ExecuteNonQuery(dbCmd);
Result = Convert.ToInt32(db.GetParameterValue(dbCmd, "@IS_INSERT"));
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, DALHelper.DAL_EXP_POLICYNAME);
if (rethrow)
{
throw ex;
}
}
finally
{
DALHelper.Destroy(ref dbCmd);
}
return Result;
}
/// <summary>
/// Update customer detail.
/// </summary>
/// <param name="xmlData">Data that converted into xml format.</param>
/// <returns>Returns 1 and 0; (1 indicates successful operation).</returns>
public int UpdateCustomer(CustomerBDto customer)
{
string str1;
Database db = null;
DbCommand dbCmd = null;
int Result = 0;
try
{
db = DatabaseFactory.CreateDatabase(DALHelper.CRM_CONNECTION_STRING);
dbCmd = db.GetStoredProcCommand(DALHelper.USP_CUST_CUSTOMER_UPDATE);
db.AddInParameter(dbCmd, "@CUST_ID", DbType.Int32, customer.CustomerId);
db.AddInParameter(dbCmd, "@CUST_TYPE_ID", DbType.Int32, customer.TypeId);
db.AddInParameter(dbCmd, "@CUST_TITLE", DbType.Int32, customer.TitleId);
db.AddInParameter(dbCmd, "@CUST_SURNAME", DbType.String, customer.SurName);
db.AddInParameter(dbCmd, "@CUST_NAME", DbType.String, customer.Name);
db.AddInParameter(dbCmd, "@CUST_PROFILE", DbType.String, customer.Profile);
if (customer.ProfessionId != 0)
db.AddInParameter(dbCmd, "@CUST_PROFESSION_ID", DbType.Int32, customer.ProfessionId);
else
db.AddInParameter(dbCmd, "@CUST_PROFESSION_ID", DbType.Int32, DBNull.Value);
db.AddInParameter(dbCmd, "@CUST_CODE_ID", DbType.Int32, customer.CodeId);
if (customer.OwnerCompanyId != 0)
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, customer.OwnerCompanyId);
else
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.CompanyName))
db.AddInParameter(dbCmd, "@CUST_COMPANY_NAME", DbType.String, customer.CompanyName);
else
db.AddInParameter(dbCmd, "@CUST_COMPANY_NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.Remarks))
db.AddInParameter(dbCmd, "@REMARKS", DbType.String, customer.Remarks);
else
db.AddInParameter(dbCmd, "@REMARKS", DbType.String, DBNull.Value);
db.AddInParameter(dbCmd, "@IsPhotoChange", DbType.Boolean, customer.IsPhotoChanged);
if (customer.CustomerPhoto != null)
db.AddInParameter(dbCmd, "@PHOTO", DbType.Binary, customer.CustomerPhoto);
else
db.AddInParameter(dbCmd, "@PHOTO", DbType.Binary, DBNull.Value);
if (!String.IsNullOrEmpty(customer.CustomerPhotoType))
db.AddInParameter(dbCmd, "@PHOTO_CONTENT_TYPE", DbType.String, customer.CustomerPhotoType);
else
db.AddInParameter(dbCmd, "@PHOTO_CONTENT_TYPE", DbType.String, DBNull.Value);
if (customer.GeneralInfo.Gender != '0')
db.AddInParameter(dbCmd, "@CUST_REL_GENDER", DbType.String, customer.GeneralInfo.Gender);
else
db.AddInParameter(dbCmd, "@CUST_REL_GENDER", DbType.String, DBNull.Value);
if (customer.GeneralInfo.BirthDate == DateTime.MinValue)
db.AddInParameter(dbCmd, "@CUST_BIRTH_DATE", DbType.DateTime, DBNull.Value);
else
db.AddInParameter(dbCmd, "@CUST_BIRTH_DATE", DbType.DateTime, customer.GeneralInfo.BirthDate);
if (customer.GeneralInfo.MarriageStatusId != 0)
db.AddInParameter(dbCmd, "@CUST_REL_MARITAL_STATUS_ID", DbType.Int32, customer.GeneralInfo.MarriageStatusId);
else
db.AddInParameter(dbCmd, "@CUST_REL_MARITAL_STATUS_ID", DbType.Int32, DBNull.Value);
if (customer.GeneralInfo.MarriageDate == DateTime.MinValue)
db.AddInParameter(dbCmd, "@CUST_REL_MARRIAGE_DATE", DbType.DateTime, DBNull.Value);
else
db.AddInParameter(dbCmd, "@CUST_REL_MARRIAGE_DATE", DbType.DateTime, customer.GeneralInfo.MarriageDate);
if (customer.GeneralInfo.ReligionId != 0)
db.AddInParameter(dbCmd, "@CUST_REL_RELIGION_ID", DbType.Int32, customer.GeneralInfo.ReligionId);
else
db.AddInParameter(dbCmd, "@CUST_REL_RELIGION_ID", DbType.Int32, DBNull.Value);
db.AddInParameter(dbCmd, "@CUST_REL_EMAIL", DbType.String, customer.ContactInfo.EmailId);
db.AddInParameter(dbCmd, "@CUST_REL_MOBILE", DbType.String, customer.ContactInfo.MobileNo);
db.AddInParameter(dbCmd, "@CUST_REL_PHONE", DbType.String, customer.ContactInfo.PhoneNo);
if (customer.AnnualIncome != 0)
db.AddInParameter(dbCmd, "@CUST_REL_ANNUAL_INCOME", DbType.Decimal, customer.AnnualIncome);
else
db.AddInParameter(dbCmd, "@CUST_REL_ANNUAL_INCOME", DbType.Decimal, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.PassportNo))
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_NO", DbType.String, customer.PassPortInfo.PassportNo);
else
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_NO", DbType.String, DBNull.Value);
if (customer.PassPortInfo.IssueDate == DateTime.MinValue)
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_DATE", DbType.DateTime, DBNull.Value);
else
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_DATE", DbType.DateTime, customer.PassPortInfo.IssueDate);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_PLACE", DbType.String, customer.PassPortInfo.IssuePlace);
if (customer.PassPortInfo.EntryDate == DateTime.MinValue)
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_EXPIRY_DATE", DbType.DateTime, DBNull.Value);
else
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_EXPIRY_DATE", DbType.DateTime, customer.PassPortInfo.EntryDate);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_PRINTED_NAME", DbType.String, customer.PassPortInfo.PrintName);
db.AddInParameter(dbCmd, "@CUST_REL_PASSPORT_ISSUE_COUNTRY", DbType.String, customer.PassPortInfo.IssueCountry);
db.AddInParameter(dbCmd, "@CUST_REL_STATE", DbType.String, customer.RelationState);
if (!string.IsNullOrEmpty(customer.XmlData))
{
str1 = customer.XmlData;
customer.XmlData = str1.Replace("&", "&");
db.AddInParameter(dbCmd, "@XML_DATA", DbType.Xml, customer.XmlData);
}
else
db.AddInParameter(dbCmd, "@XML_DATA", DbType.Xml, DBNull.Value);
if (!string.IsNullOrEmpty(customer.PrefAirlineXmlData))
db.AddInParameter(dbCmd, "@PREF_AIRLINE_XML_DATA", DbType.Xml, customer.PrefAirlineXmlData);
else
db.AddInParameter(dbCmd, "@PREF_AIRLINE_XML_DATA", DbType.Xml, DBNull.Value);
if (!string.IsNullOrEmpty(customer.VisaXmlData))
db.AddInParameter(dbCmd, "@VISA_XML_DATA", DbType.Xml, customer.VisaXmlData);
else
db.AddInParameter(dbCmd, "@VISA_XML_DATA", DbType.Xml, DBNull.Value);
db.AddInParameter(dbCmd, "@USER_ID", DbType.Int32, customer.UserId);
db.AddOutParameter(dbCmd, "@IS_INSERT", DbType.Int32, 1);
db.ExecuteNonQuery(dbCmd);
Result = Convert.ToInt32(db.GetParameterValue(dbCmd, "@IS_INSERT"));
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, DALHelper.DAL_EXP_POLICYNAME);
if (rethrow)
{
throw ex;
}
}
finally
{
DALHelper.Destroy(ref dbCmd);
}
return Result;
}
/// <summary>
/// Gets customer list.
/// </summary>
/// <param name="customer"></param>
/// <param name="fromCreatedDate"></param>
/// <param name="toCreatedDate"></param>
/// <param name="fromModifiedDate"></param>
/// <param name="toModifiedDate"></param>
/// <returns>Returns dataset contains custromer data.</returns>
public DataSet GetCustomerExport(CustomerBDto customer, int ownerCompanyId, DateTime fromCreatedDate, DateTime toCreatedDate, DateTime fromModifiedDate, DateTime toModifiedDate)
{
Database db = null;
DbCommand dbCmd = null;
DataSet ds = null;
try
{
//UserProfileBDto UserName,
db = DatabaseFactory.CreateDatabase(DALHelper.CRM_CONNECTION_STRING);
dbCmd = db.GetStoredProcCommand(DALHelper.USP_CUST_CUSTOMER_SELECT_EXPORT);
// customer.EmployeeName =@EMP_NAME_ALL
if (!String.IsNullOrEmpty(customer.EmployeeName) && customer.EmployeeName == "All" )
// db.AddInParameter(dbCmd, "@EMP_NAME_ALL", DbType.String, customer.EmployeeId);
//else
db.AddInParameter(dbCmd, "@EMP_NAME_ALL", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.EmployeeName) && customer.EmployeeName != "All")
db.AddInParameter(dbCmd, "@EMP_NAME", DbType.String, customer.EmployeeName);
else
db.AddInParameter(dbCmd, "@EMP_NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.UniqueId))
db.AddInParameter(dbCmd, "@UNIQUE_ID", DbType.String, customer.UniqueId);
else
db.AddInParameter(dbCmd, "@UNIQUE_ID", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.SurName))
db.AddInParameter(dbCmd, "@SURNAME", DbType.String, customer.SurName);
else
db.AddInParameter(dbCmd, "@SURNAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.Name))
db.AddInParameter(dbCmd, "@NAME", DbType.String, customer.Name);
else
db.AddInParameter(dbCmd, "@NAME", DbType.String, DBNull.Value);
if (customer.GeneralInfo.Gender.ToString() != "0")
db.AddInParameter(dbCmd, "@GENDER", DbType.String, customer.GeneralInfo.Gender);
else
db.AddInParameter(dbCmd, "@GENDER", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.EmailId))
db.AddInParameter(dbCmd, "@EMAIL", DbType.String, customer.ContactInfo.EmailId);
else
db.AddInParameter(dbCmd, "@EMAIL", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.MobileNo))
db.AddInParameter(dbCmd, "@MOBILE", DbType.String, customer.ContactInfo.MobileNo);
else
db.AddInParameter(dbCmd, "@MOBILE", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.PhoneNo))
db.AddInParameter(dbCmd, "@PHONE", DbType.String, customer.ContactInfo.PhoneNo);
else
db.AddInParameter(dbCmd, "@PHONE", DbType.String, DBNull.Value);
if (customer.GeneralInfo.MarriageStatusId != 0)
db.AddInParameter(dbCmd, "@MARITAL_STATUS_ID", DbType.Int32, customer.GeneralInfo.MarriageStatusId);
else
db.AddInParameter(dbCmd, "@MARITAL_STATUS_ID", DbType.Int32, DBNull.Value);
if (customer.GeneralInfo.ReligionId != 0)
db.AddInParameter(dbCmd, "@RELIGION_ID", DbType.Int32, customer.GeneralInfo.ReligionId);
else
db.AddInParameter(dbCmd, "@RELIGION_ID", DbType.Int32, DBNull.Value);
if (customer.AddressInfo.AddressTypeId != 0)
db.AddInParameter(dbCmd, "@ADDRESS_TYPE_ID", DbType.Int32, customer.AddressInfo.AddressTypeId);
else
db.AddInParameter(dbCmd, "@ADDRESS_TYPE_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.AddressInfo.AddressLine1))
db.AddInParameter(dbCmd, "@ADDRESS_LINE1", DbType.String, customer.AddressInfo.AddressLine1);
else
db.AddInParameter(dbCmd, "@ADDRESS_LINE1", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.AddressInfo.AddressLine2))
db.AddInParameter(dbCmd, "@ADDRESS_LINE2", DbType.String, customer.AddressInfo.AddressLine2);
else
db.AddInParameter(dbCmd, "@ADDRESS_LINE2", DbType.String, DBNull.Value);
if (customer.AddressInfo.CityId != 0)
db.AddInParameter(dbCmd, "@CITY_ID", DbType.Int32, customer.AddressInfo.CityId);
else
db.AddInParameter(dbCmd, "@CITY_ID", DbType.Int32, DBNull.Value);
if (customer.AddressInfo.StateId != 0)
db.AddInParameter(dbCmd, "@STATE_ID", DbType.Int32, customer.AddressInfo.StateId);
else
db.AddInParameter(dbCmd, "@STATE_ID", DbType.Int32, DBNull.Value);
if (customer.AddressInfo.CountryId != 0)
db.AddInParameter(dbCmd, "@COUNTRY_ID", DbType.Int32, customer.AddressInfo.CountryId);
else
db.AddInParameter(dbCmd, "@COUNTRY_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.AddressInfo.PinCodeNo))
db.AddInParameter(dbCmd, "@PINCODE", DbType.String, customer.AddressInfo.PinCodeNo);
else
db.AddInParameter(dbCmd, "@PINCODE", DbType.String, DBNull.Value);
if (customer.TypeId != 0)
db.AddInParameter(dbCmd, "@TYPE_ID", DbType.Int32, customer.TypeId);
else
db.AddInParameter(dbCmd, "@TYPE_ID", DbType.Int32, DBNull.Value);
if (customer.CodeId != 0)
db.AddInParameter(dbCmd, "@CODE_ID", DbType.Int32, customer.CodeId);
else
db.AddInParameter(dbCmd, "@CODE_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.CompanyName))
db.AddInParameter(dbCmd, "@COMPANY_NAME", DbType.String, customer.CompanyName);
else
db.AddInParameter(dbCmd, "@COMPANY_NAME", DbType.String, DBNull.Value);
if (customer.ProfessionId != 0)
db.AddInParameter(dbCmd, "@PROFESSION_ID", DbType.Int32, customer.ProfessionId);
else
db.AddInParameter(dbCmd, "@PROFESSION_ID", DbType.Int32, DBNull.Value);
if (customer.AnnualIncome != 0)
db.AddInParameter(dbCmd, "@ANNUAL_INCOME", DbType.Decimal, customer.AnnualIncome);
else
db.AddInParameter(dbCmd, "@ANNUAL_INCOME", DbType.Decimal, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.PassportNo))
db.AddInParameter(dbCmd, "@PASSPORT_NO", DbType.String, customer.PassPortInfo.PassportNo);
else
db.AddInParameter(dbCmd, "@PASSPORT_NO", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.PrintName))
db.AddInParameter(dbCmd, "@PASSPORT_PRINTED_NAME", DbType.String, customer.PassPortInfo.PrintName);
else
db.AddInParameter(dbCmd, "@PASSPORT_PRINTED_NAME", DbType.String, DBNull.Value);
if (customer.PassPortInfo.IssueDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_DATE", DbType.DateTime, customer.PassPortInfo.IssueDate);
else
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_DATE", DbType.DateTime, DBNull.Value);
if (customer.PassPortInfo.EntryDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@PASSPORT_EXPIRY_DATE", DbType.DateTime, customer.PassPortInfo.EntryDate);
else
db.AddInParameter(dbCmd, "@PASSPORT_EXPIRY_DATE", DbType.DateTime, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.IssuePlace))
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_PLACE", DbType.String, customer.PassPortInfo.IssuePlace);
else
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_PLACE", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.IssueCountry))
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_COUNTRY", DbType.String, customer.PassPortInfo.IssueCountry);
else
db.AddInParameter(dbCmd, "@PASSPORT_ISSUE_COUNTRY", DbType.String, DBNull.Value);
if (ownerCompanyId != 0)
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, ownerCompanyId);
else
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, DBNull.Value);
if (fromCreatedDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@CREATED_DATE_FROM", DbType.DateTime, fromCreatedDate);
else
db.AddInParameter(dbCmd, "@CREATED_DATE_FROM", DbType.DateTime, DBNull.Value);
if (toCreatedDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@CREATED_DATE_TO", DbType.DateTime, toCreatedDate);
else
db.AddInParameter(dbCmd, "@CREATED_DATE_TO", DbType.DateTime, DBNull.Value);
if (fromModifiedDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@MODIFIED_DATE_FROM", DbType.DateTime, fromModifiedDate);
else
db.AddInParameter(dbCmd, "@MODIFIED_DATE_FROM", DbType.DateTime, DBNull.Value);
if (toModifiedDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@MODIFIED_DATE_TO", DbType.DateTime, toModifiedDate);
else
db.AddInParameter(dbCmd, "@MODIFIED_DATE_TO", DbType.DateTime, DBNull.Value);
ds = db.ExecuteDataSet(dbCmd);
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, DALHelper.DAL_EXP_POLICYNAME);
if (rethrow)
{ throw ex; }
}
finally
{
DALHelper.Destroy(ref dbCmd);
}
return ds;
}
public DataTable GetTourBookingDetails(TourBookingHrdBDto tourBookingHrd, CustomerBDto customer)
{
Database db = null;
DbCommand dbCmd = null;
DataTable dt = null;
DataSet ds = null;
try
{
db = DatabaseFactory.CreateDatabase(DALHelper.CRM_CONNECTION_STRING);
dbCmd = db.GetStoredProcCommand(DALHelper.USP_BOOKING_TOUR_BOOKING_HDR_SELECT);
if (tourBookingHrd.TourTypeId != 0)
db.AddInParameter(dbCmd, "@TOUR_TYPE_ID", DbType.Int32, tourBookingHrd.TourTypeId);
else
db.AddInParameter(dbCmd, "@TOUR_TYPE_ID", DbType.Int32, DBNull.Value);
if (tourBookingHrd.TourId != 0)
db.AddInParameter(dbCmd, "@TOUR_ID", DbType.Int32, tourBookingHrd.TourId);
else
db.AddInParameter(dbCmd, "@TOUR_ID", DbType.Int32, DBNull.Value);
if (tourBookingHrd.BookingDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@BOOKING_DATE", DbType.DateTime, tourBookingHrd.BookingDate);
else
db.AddInParameter(dbCmd, "@BOOKING_DATE", DbType.DateTime, DBNull.Value);
if (tourBookingHrd.SalesExecutiveId != 0)
db.AddInParameter(dbCmd, "@SALES_EXECUTIVE_ID", DbType.Int32, tourBookingHrd.SalesExecutiveId);
else
db.AddInParameter(dbCmd, "@SALES_EXECUTIVE_ID", DbType.Int32, DBNull.Value);
if (tourBookingHrd.BranchId != 0)
db.AddInParameter(dbCmd, "@BRANCH_ID", DbType.Int32, tourBookingHrd.BranchId);
else
db.AddInParameter(dbCmd, "@BRANCH_ID", DbType.Int32, DBNull.Value);
if (tourBookingHrd.AgentId != 0)
db.AddInParameter(dbCmd, "@AGENT_ID", DbType.Int32, tourBookingHrd.AgentId);
else
db.AddInParameter(dbCmd, "@AGENT_ID", DbType.Int32, DBNull.Value);
if (tourBookingHrd.BoardingFrom != 0)
db.AddInParameter(dbCmd, "@BOARDING_FROM", DbType.Int32, tourBookingHrd.BoardingFrom);
else
db.AddInParameter(dbCmd, "@BOARDING_FROM", DbType.Int32, DBNull.Value);
if (tourBookingHrd.ArrivalTo != 0)
db.AddInParameter(dbCmd, "@ARRIVAL_TO", DbType.Int32, tourBookingHrd.ArrivalTo);
else
db.AddInParameter(dbCmd, "@ARRIVAL_TO", DbType.Int32, DBNull.Value);
if (tourBookingHrd.DepartureDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@DEPARTURE_DATE", DbType.DateTime, tourBookingHrd.DepartureDate);
else
db.AddInParameter(dbCmd, "@DEPARTURE_DATE", DbType.DateTime, DBNull.Value);
if (!String.IsNullOrEmpty(customer.UniqueId))
db.AddInParameter(dbCmd, "@CUSTOMER_ID", DbType.String, customer.UniqueId);
else
db.AddInParameter(dbCmd, "@CUSTOMER_ID", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.Name))
db.AddInParameter(dbCmd, "@CUSTOMER_FIRST_NAME", DbType.String, customer.Name);
else
db.AddInParameter(dbCmd, "@CUSTOMER_FIRST_NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.SurName))
db.AddInParameter(dbCmd, "@CUSTOMER_LAST_NAME", DbType.String, customer.SurName);
else
db.AddInParameter(dbCmd, "@CUSTOMER_LAST_NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(tourBookingHrd.EmergencyPersonName))
db.AddInParameter(dbCmd, "@EMERGENCY_CONTACT_PERSON_NAME", DbType.String, tourBookingHrd.EmergencyPersonName);
else
db.AddInParameter(dbCmd, "@EMERGENCY_CONTACT_PERSON_NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(tourBookingHrd.EmergencyMobileNo))
db.AddInParameter(dbCmd, "@EMERGENCY_MOBILE_NO", DbType.String, tourBookingHrd.EmergencyMobileNo);
else
db.AddInParameter(dbCmd, "@EMERGENCY_MOBILE_NO", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(tourBookingHrd.EmergencyEmail))
db.AddInParameter(dbCmd, "@EMERGENCY_EMAIL", DbType.String, tourBookingHrd.EmergencyEmail);
else
db.AddInParameter(dbCmd, "@EMERGENCY_EMAIL", DbType.String, DBNull.Value);
ds = db.ExecuteDataSet(dbCmd);
using (ds)
{
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, DALHelper.DAL_EXP_POLICYNAME);
if (rethrow)
{
throw ex;
}
}
finally
{
DALHelper.Destroy(ref dbCmd);
}
return dt;
}
public DataSet GetCustomerInquries(CustomerBDto customer, InquiryBDto inquiry)
{
Database db = null;
DbCommand dbCmd = null;
DataSet ds = null;
try
{
db = DatabaseFactory.CreateDatabase(DALHelper.CRM_CONNECTION_STRING);
dbCmd = db.GetStoredProcCommand(DALHelper.USP_CUST_CUSTOMER_INQURIES_SELECT);
if (!String.IsNullOrEmpty(customer.SurName))
db.AddInParameter(dbCmd, "@SURNAME", DbType.String, customer.SurName);
else
db.AddInParameter(dbCmd, "@SURNAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.UniqueId))
db.AddInParameter(dbCmd, "@UNIQUE_ID", DbType.String, customer.UniqueId);
else
db.AddInParameter(dbCmd, "@UNIQUE_ID", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.Name))
db.AddInParameter(dbCmd, "@NAME", DbType.String, customer.Name);
else
db.AddInParameter(dbCmd, "@NAME", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.EmailId))
db.AddInParameter(dbCmd, "@EMAIL", DbType.String, customer.ContactInfo.EmailId);
else
db.AddInParameter(dbCmd, "@EMAIL", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.MobileNo))
db.AddInParameter(dbCmd, "@MOBILE", DbType.String, customer.ContactInfo.MobileNo);
else
db.AddInParameter(dbCmd, "@MOBILE", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.ContactInfo.PhoneNo))
db.AddInParameter(dbCmd, "@PHONE", DbType.String, customer.ContactInfo.PhoneNo);
else
db.AddInParameter(dbCmd, "@PHONE", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.CompanyName))
db.AddInParameter(dbCmd, "@COMPANY", DbType.String, customer.CompanyName);
else
db.AddInParameter(dbCmd, "@COMPANY", DbType.String, DBNull.Value);
if (customer.ProfessionId != 0)
db.AddInParameter(dbCmd, "@PROFESSION_ID", DbType.Int32, customer.ProfessionId);
else
db.AddInParameter(dbCmd, "@PROFESSION_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.PassPortInfo.PassportNo))
db.AddInParameter(dbCmd, "@PASSPORT_NO", DbType.String, customer.PassPortInfo.PassportNo);
else
db.AddInParameter(dbCmd, "@PASSPORT_NO", DbType.String, DBNull.Value);
if (customer.AddressInfo.AddressTypeId != 0)
db.AddInParameter(dbCmd, "@ADDRESS_TYPE_ID", DbType.Int32, customer.AddressInfo.AddressTypeId);
else
db.AddInParameter(dbCmd, "@ADDRESS_TYPE_ID", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(customer.AddressInfo.AddressLine1))
db.AddInParameter(dbCmd, "@ADDRESS_LINE1", DbType.String, customer.AddressInfo.AddressLine1);
else
db.AddInParameter(dbCmd, "@ADDRESS_LINE1", DbType.String, DBNull.Value);
if (!String.IsNullOrEmpty(customer.AddressInfo.AddressLine2))
db.AddInParameter(dbCmd, "@ADDRESS_LINE2", DbType.String, customer.AddressInfo.AddressLine2);
else
db.AddInParameter(dbCmd, "@ADDRESS_LINE2", DbType.String, DBNull.Value);
if (customer.AddressInfo.CityId != 0)
db.AddInParameter(dbCmd, "@CITY_ID", DbType.Int32, customer.AddressInfo.CityId);
else
db.AddInParameter(dbCmd, "@CITY_ID", DbType.Int32, DBNull.Value);
if (customer.AddressInfo.StateId != 0)
db.AddInParameter(dbCmd, "@STATE_ID", DbType.Int32, customer.AddressInfo.StateId);
else
db.AddInParameter(dbCmd, "@STATE_ID", DbType.Int32, DBNull.Value);
if (customer.AddressInfo.CountryId != 0)
db.AddInParameter(dbCmd, "@COUNTRY_ID", DbType.Int32, customer.AddressInfo.CountryId);
else
db.AddInParameter(dbCmd, "@COUNTRY_ID", DbType.Int32, DBNull.Value);
if (inquiry.InquiryNo != 0)
db.AddInParameter(dbCmd, "@INQUIRY_NO", DbType.Int32, inquiry.InquiryNo);
else
db.AddInParameter(dbCmd, "@INQUIRY_NO", DbType.Int32, DBNull.Value);
if (inquiry.InquiryForId != 0)
db.AddInParameter(dbCmd, "@INQUIRY_FOR", DbType.Int32, inquiry.InquiryForId);
else
db.AddInParameter(dbCmd, "@INQUIRY_FOR", DbType.Int32, DBNull.Value);
if (!String.IsNullOrEmpty(inquiry.InquiryDescription))
db.AddInParameter(dbCmd, "@INQUIRY_DESC", DbType.String, inquiry.InquiryDescription);
else
db.AddInParameter(dbCmd, "@INQUIRY_DESC", DbType.String, DBNull.Value);
if (inquiry.InquiryStatusId != 0)
db.AddInParameter(dbCmd, "@INQUIRY_STATUS", DbType.Int32, inquiry.InquiryStatusId);
else
db.AddInParameter(dbCmd, "@INQUIRY_STATUS", DbType.Int32, DBNull.Value);
if (inquiry.InquiryDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@INQUIRY_DATE_FROM", DbType.DateTime, inquiry.InquiryDate);
else
db.AddInParameter(dbCmd, "@INQUIRY_DATE_FROM", DbType.DateTime, DBNull.Value);
if (inquiry.NextFollowupDate != DateTime.MinValue)
db.AddInParameter(dbCmd, "@INQUIRY_DATE_TO", DbType.DateTime, inquiry.NextFollowupDate);
else
db.AddInParameter(dbCmd, "@INQUIRY_DATE_TO", DbType.DateTime, DBNull.Value);
if (inquiry.SalesPersonId != 0)
db.AddInParameter(dbCmd, "@SALES_PERSON_ID", DbType.Int32, inquiry.SalesPersonId);
else
db.AddInParameter(dbCmd, "@SALES_PERSON_ID", DbType.Int32, DBNull.Value);
if (inquiry.OwnerCompanyId != 0)
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, inquiry.OwnerCompanyId);
else
db.AddInParameter(dbCmd, "@OWNER_COMPANY_ID", DbType.Int32, DBNull.Value);
ds = db.ExecuteDataSet(dbCmd);
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, DALHelper.DAL_EXP_POLICYNAME);
if (rethrow)
{
throw ex;
}
}
finally
{
DALHelper.Destroy(ref dbCmd);
}
return ds;
}