Beispiel #1
0
        // 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);
            }
        }
Beispiel #2
0
        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 override void ExecuteQuery(CommerceServer.Foundation.CommerceQueryOperation queryOperation, CommerceServer.Foundation.OperationCacheDictionary operationCache, CommerceServer.Foundation.CommerceQueryOperationResponse response)
        {
            CommerceModelSearch search = queryOperation.GetSearchCriteria <CommerceModelSearch>();

            ParameterChecker.CheckForNull(search, "searchCriteria");

            if (!String.IsNullOrEmpty(search.WhereClause))
            {
                var sqlFormat = " SELECT u_user_id, u_first_name, u_last_name, u_email_address FROM [BEK_Commerce_profiles].[dbo].[UserObject] {0}";
                var sql       = string.Format(sqlFormat, search.WhereClause.IndexOf("where", StringComparison.CurrentCultureIgnoreCase) >= 0 ? search.WhereClause : "WHERE " + search.WhereClause);

                CommerceServer.Core.Runtime.Configuration.CommerceResourceCollection csResources = SiteHelper.GetCsConfig();
                String connStr = csResources["Biz Data Service"]["s_BizDataStoreConnectionString"].ToString();

                using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connStr))
                {
                    conn.Open();
                    using (System.Data.OleDb.OleDbCommand cmdRead = new System.Data.OleDb.OleDbCommand(sql, conn))
                    {
                        using (System.Data.OleDb.OleDbDataReader dataReader = cmdRead.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                CommerceEntity org = new CommerceEntity("UserProfile");
                                org.Id = dataReader.GetString("u_user_id");
                                org.SetPropertyValue("FirstName", dataReader.GetString("u_first_name"));
                                org.SetPropertyValue("LastName", dataReader.GetString("u_last_name"));
                                org.SetPropertyValue("Email", dataReader.GetString("u_email_address"));
                                response.CommerceEntities.Add(org);
                            }
                        }
                    }
                }
            }
            else
            {
                base.ExecuteQuery(queryOperation, operationCache, response);
            }
        }
Beispiel #4
0
        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);
                    }
                }
            }
        }