public static string GetTransactionDbConnectionString(this CommerceResourceCollection resources) { string result = resources["transactions"]["connstr_db_Transactions"].ToString(); return(result); }
public static string GetCatalogDbConnectionString(this CommerceResourceCollection resources) { string result = resources["Product Catalog"]["connstr_db_Catalog"].ToString(); return(result); }
public static CommerceResourceCollection GetCsConfig() { if (siteConfig == null) { siteConfig = new CommerceResourceCollection(SiteHelper.GetSiteName()); } return(siteConfig); }
// by default, CS Profile Sequence Components only load profiles by ID; so we have to override that functionality to get a list back public override void ExecuteQuery(CommerceQueryOperation queryOperation, OperationCacheDictionary operationCache, CommerceQueryOperationResponse response) { CommerceModelSearch search = ((CommerceModelSearch)(queryOperation.SearchCriteria)); if (!String.IsNullOrEmpty(search.WhereClause)) // no search criteria, so override CS behavior to load all orgs { CommerceResourceCollection csResources = SiteHelper.GetCsConfig(); String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString(); //ProfileContext pContext = CommerceSiteContexts.Profile[GetSiteName()]; string fields = string.Join(", ", Array.ConvertAll(this.ProfileEntityMappings.PropertyMappings.ToArray(), i => i.Value)); ProfileContext ctxt = CommerceSiteContexts.Profile[SiteHelper.GetSiteName()]; var sqlFormat = " SELECT * FROM [BEK_Commerce_profiles].[dbo].[OrganizationObject] oo {0} Order By oo.u_name OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY;"; var pageSize = queryOperation.SearchCriteria.NumberOfItemsToReturn.HasValue ? queryOperation.SearchCriteria.NumberOfItemsToReturn.Value : int.MaxValue; var from = queryOperation.SearchCriteria.FirstItemIndex.HasValue ? queryOperation.SearchCriteria.FirstItemIndex.Value : 0; string dataSQLText = string.Format(sqlFormat, search.WhereClause.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) >= 0 ? search.WhereClause : "WHERE " + search.WhereClause, from, pageSize); string countSQLText = string.Format("SELECT count(*) FROM [BEK_Commerce_profiles].[dbo].[OrganizationObject] oo {0}", search.WhereClause.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) >= 0 ? search.WhereClause : "WHERE " + search.WhereClause); var entities = new List <CommerceEntity>(); using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); //Get total count using (OleDbCommand cmdTotal = new OleDbCommand(countSQLText, conn)) { response.TotalItemCount = (int)cmdTotal.ExecuteScalar(); } //Read paged results using (OleDbCommand cmdRead = new OleDbCommand(dataSQLText, conn)) { using (OleDbDataReader dataReader = cmdRead.ExecuteReader()) { while (dataReader.Read()) { CommerceEntity org = new CommerceEntity("Organization"); org.Id = dataReader.GetString("u_org_id"); org.SetPropertyValue("Name", dataReader.GetString("u_name")); org.SetPropertyValue("CustomerNumber", dataReader.GetString("u_customer_number")); org.SetPropertyValue("BranchNumber", dataReader.GetString("u_branch_number")); org.SetPropertyValue("DsrNumber", dataReader.GetString("u_dsr_number")); org.SetPropertyValue("ContractNumber", dataReader.GetString("u_contract_number")); org.SetPropertyValue("IsPoRequired", dataReader.GetNullableBool("u_is_po_required")); org.SetPropertyValue("IsPowerMenu", dataReader.GetNullableBool("u_is_power_menu")); org.SetPropertyValue("OrganizationType", dataReader.GetString("u_organization_type")); org.SetPropertyValue("NationalOrRegionalAccountNumber", dataReader.GetString("u_national_or_regional_account_number")); org.SetPropertyValue("ParentOrganizationId", dataReader.GetString("u_parent_organization")); org.SetPropertyValue("TermCode", dataReader.GetString("u_term_code")); org.SetPropertyValue("CurrentBalance", dataReader.GetNullableDecimal("u_current_balance")); org.SetPropertyValue("BalanceAge1", dataReader.GetNullableDecimal("u_balance_age_1")); org.SetPropertyValue("BalanceAge2", dataReader.GetNullableDecimal("u_balance_age_2")); org.SetPropertyValue("BalanceAge3", dataReader.GetNullableDecimal("u_balance_age_3")); org.SetPropertyValue("BalanceAge4", dataReader.GetNullableDecimal("u_balance_age_4")); org.SetPropertyValue("AmountDue", dataReader.GetNullableDecimal("u_amount_due")); org.SetPropertyValue("AchType", dataReader.GetString("u_customer_ach_type")); org.SetPropertyValue("DsmNumber", dataReader.GetString("u_dsm_number")); org.SetPropertyValue("NationalId", dataReader.GetString("u_national_id")); org.SetPropertyValue("NationalNumber", dataReader.GetString("u_national_number")); org.SetPropertyValue("NationalSubNumber", dataReader.GetString("u_national_sub_number")); org.SetPropertyValue("RegionalId", dataReader.GetString("u_regional_id")); org.SetPropertyValue("RegionalNumber", dataReader.GetString("u_regional_number")); org.SetPropertyValue("IsKeithnetCustomer", dataReader.GetString("u_is_keithnet_customer")); org.SetPropertyValue("NationalIdDesc", dataReader.GetString("u_national_id_desc")); org.SetPropertyValue("NationalNumberSubDesc", dataReader.GetString("u_national_numbersub_desc")); org.SetPropertyValue("RegionalIdDesc", dataReader.GetString("u_regional_id_desc")); org.SetPropertyValue("RegionalNumberDesc", dataReader.GetString("u_regional_number_desc")); org.SetPropertyValue("GeneralInfo.preferred_address", dataReader.GetString("u_preferred_address")); org.SetPropertyValue("CanViewPricing", dataReader.GetNullableBool("u_can_view_pricing")); response.CommerceEntities.Add(org); } } } if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } } } else { base.ExecuteQuery(queryOperation, operationCache, response); } }
public override void ExecuteQuery(CommerceQueryOperation queryOperation, OperationCacheDictionary operationCache, CommerceQueryOperationResponse response) { CommerceModelSearch searchCriteria = queryOperation.GetSearchCriteria <CommerceModelSearch>(); ParameterChecker.CheckForNull(searchCriteria, "searchCriteria"); if (searchCriteria.Model.Properties.Count == 1) { string sproc = string.Empty; if (searchCriteria.Model.Properties[0].Key == "OrganizationId") // looking for users associated to org { sproc = "[dbo].[sp_BEK_ReadUsersForOrg]"; } else if (searchCriteria.Model.Properties[0].Key == "UserId") // looking for orgs associated to user { sproc = "[dbo].[sp_BEK_ReadOrgsForUser]"; } CommerceResourceCollection csResources = SiteHelper.GetCsConfig(); String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString(); //ProfileContext pContext = CommerceSiteContexts.Profile[GetSiteName()]; using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); CommercePropertyItem item = searchCriteria.Model.Properties[0]; OleDbCommand cmd = new OleDbCommand(sproc, conn); cmd.Parameters.Add(new OleDbParameter("@id", OleDbType.VarChar, 50)); cmd.Parameters[0].Value = item.Value; cmd.Parameters[0].Direction = ParameterDirection.Input; cmd.CommandType = CommandType.StoredProcedure; using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd)) { DataTable dt = new DataTable(); adapter.Fill(dt); foreach (DataRow r in dt.Rows) { if (searchCriteria.Model.Properties[0].Key == "UserId") // todo: use profile entity mapping to fill this in //this.Metadata.ProfileMapping; { CommerceEntity org = new CommerceEntity("Organization"); org.Id = r.GetString("u_org_id"); org.SetPropertyValue("Name", r.GetString("u_name")); org.SetPropertyValue("CustomerNumber", r.GetString("u_customer_number")); org.SetPropertyValue("BranchNumber", r.GetString("u_branch_number")); org.SetPropertyValue("DsrNumber", r.GetString("u_dsr_number")); org.SetPropertyValue("ContractNumber", r.GetString("u_contract_number")); org.SetPropertyValue("IsPoRequired", r.GetNullableBool("u_is_po_required")); org.SetPropertyValue("IsPowerMenu", r.GetNullableBool("u_is_power_menu")); org.SetPropertyValue("OrganizationType", r.GetString("u_organization_type")); org.SetPropertyValue("NationalOrRegionalAccountNumber", r.GetString("u_national_or_regional_account_number")); org.SetPropertyValue("ParentOrganizationId", r.GetString("u_parent_organization")); org.SetPropertyValue("TermCode", r.GetString("u_term_code")); org.SetPropertyValue("CurrentBalance", r.GetNullableDecimal("u_current_balance")); org.SetPropertyValue("BalanceAge1", r.GetNullableDecimal("u_balance_age_1")); org.SetPropertyValue("BalanceAge2", r.GetNullableDecimal("u_balance_age_2")); org.SetPropertyValue("BalanceAge3", r.GetNullableDecimal("u_balance_age_3")); org.SetPropertyValue("BalanceAge4", r.GetNullableDecimal("u_balance_age_4")); org.SetPropertyValue("AmountDue", r.GetNullableDecimal("u_amount_due")); org.SetPropertyValue("AchType", r.GetString("u_customer_ach_type")); org.SetPropertyValue("GeneralInfo.preferred_address", r.GetString("u_preferred_address")); response.CommerceEntities.Add(org); } else if (searchCriteria.Model.Properties[0].Key == "OrganizationId") { CommerceEntity org = new CommerceEntity("UserProfile"); org.Id = r.GetString("u_user_id"); org.SetPropertyValue("FirstName", r.GetString("u_first_name")); org.SetPropertyValue("LastName", r.GetString("u_last_name")); org.SetPropertyValue("Email", r.GetString("u_email_address")); response.CommerceEntities.Add(org); } } } if (conn.State == ConnectionState.Open) { conn.Close(); } } } else { base.ExecuteQuery(queryOperation, operationCache, response); } }
public PipelineHelper(String siteName) { resources = Extensions.SiteHelper.GetCsConfig(); webSiteName = siteName; orderCtx = Extensions.SiteHelper.GetOrderContext(); }
public override void ExecuteQuery(CommerceQueryOperation queryOperation, OperationCacheDictionary operationCache, CommerceQueryOperationResponse response) { int numToTake = 2000; lock (_addressLoaderLock) { for (int i = 0; i < response.CommerceEntities.Count && response.CommerceEntities.Count < numToTake; i += numToTake) // only load addresses if we have less than 2000; any more than that will require a incoming filter { List <string> preferredAddressIds = new List <string>(); Dictionary <string, string> addressToParentIdMap = new Dictionary <string, string>(); List <CommerceEntity> currentBatch = response.CommerceEntities.Skip(i).Take(numToTake).ToList(); foreach (var entity in currentBatch) { string preferredAddressId = entity.GetPropertyValue("GeneralInfo.preferred_address") as string; if (!String.IsNullOrEmpty(preferredAddressId) && !addressToParentIdMap.ContainsKey(preferredAddressId)) { preferredAddressIds.Add("'" + preferredAddressId + "'"); addressToParentIdMap.Add(preferredAddressId, entity.Id); } } // query them out if (preferredAddressIds.Count == 0) { continue; } CommerceResourceCollection csResources = SiteHelper.GetCsConfig(); String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString(); //ProfileContext pContext = CommerceSiteContexts.Profile[GetSiteName()]; string fields = string.Join(", ", Array.ConvertAll(this.ProfileEntityMappings.PropertyMappings.ToArray(), p => p.Value)); string keys = string.Join(", ", Array.ConvertAll(this.ProfileEntityMappings.PropertyMappings.ToArray(), p => p.Key)); ProfileContext ctxt = CommerceSiteContexts.Profile[SiteHelper.GetSiteName()]; string dataSQLText = "SELECT * FROM [BEK_Commerce_profiles].[dbo].[Addresses] WHERE u_address_id in (" + String.Join(",", preferredAddressIds.ToArray()) + ")"; try { using (OleDbConnection conn = new OleDbConnection(connStr)) { conn.Open(); using (OleDbCommand cmdRead = new OleDbCommand(dataSQLText, conn)) { using (OleDbDataReader dataReader = cmdRead.ExecuteReader()) { while (dataReader.Read()) { CommerceEntity entity = new CommerceEntity("Address"); entity.Id = dataReader.GetString("u_address_id"); entity.SetPropertyValue("LastName", dataReader.GetString("u_last_name")); entity.SetPropertyValue("FirstName", dataReader.GetString("u_first_name")); entity.SetPropertyValue("AddressName", dataReader.GetString("u_address_name")); entity.SetPropertyValue("AddressType", dataReader.GetNullableInt("i_address_type")); entity.SetPropertyValue("Description", dataReader.GetString("u_description")); entity.SetPropertyValue("Line1", dataReader.GetString("u_address_line1")); entity.SetPropertyValue("Line2", dataReader.GetString("u_address_line2")); entity.SetPropertyValue("City", dataReader.GetString("u_city")); entity.SetPropertyValue("StateProvinceCode", dataReader.GetString("u_region_code")); entity.SetPropertyValue("StateProvinceName", dataReader.GetString("u_region_name")); entity.SetPropertyValue("ZipPostalCode", dataReader.GetString("u_postal_code")); entity.SetPropertyValue("CountryRegionCode", dataReader.GetString("u_country_code")); entity.SetPropertyValue("CountryRegionName", dataReader.GetString("u_country_name")); entity.SetPropertyValue("Telephone", dataReader.GetString("u_tel_number")); entity.SetPropertyValue("TelephoneExtension", dataReader.GetString("u_tel_extension")); entity.SetPropertyValue("LocaleId", dataReader.GetString("i_locale")); entity.SetPropertyValue("DateModified", dataReader.GetNullableDateTime("dt_date_last_changed")); entity.SetPropertyValue("DateCreated", dataReader.GetNullableDateTime("dt_date_created")); currentBatch.Where(x => x.Id == (addressToParentIdMap[entity.Id])).FirstOrDefault() .Properties.Add("PreferredAddress", new CommerceRelationship(entity)); } dataReader.Close(); } } } } catch (Exception ex) { throw new ApplicationException("Error loading organization addresses", ex); } } } }