Category: 小小草

IT 技术领域学海无涯。其实任何领域都学海无涯,无非 IT 发展太快了,让我有更多嘘唏。希望我掌握的技术有如小小草,虽然渺小,却有旺盛的生命力。

  • Mysql varchar could be transformed to memo in Ms Access

    Mysql varchar could be transformed to memo in Ms Access when Mysql table is used as linked table inside Access, but I want to prevent this from happening. Mainly because I can not join tables with memo field.

    As I observed, long Mysql varchar field is transformed to memo field, but short one stays as text field. The maximum length of varchar is 85 which allows varchar stays as text field in Access.

    This limitation is quite touch, isn’t it?

  • Understand how tax is calculated in Magento

    彻底理解 Magento tax 是如何计算的不是一件容易的事。我做了无数次组合测试,算是了解 Magento tax 是如何计算的,但我未及去读代码,不敢说彻底摸透它的来龙去脉,写下这段文字权作日后参考。

    首先,零税率不用设置,因为如果什么设置都没有的话税率就是零,所以大多数时候只要针对非零税率进行设置。稍后会讲到什么时候需要进行零税率设置。

    其次,要分清以下名词:

    • tax rate 税率
    • tax zone 税区域
    • tax rule 税规则
    • customer tax class 顾客税种
    • product tax class 产品税种
    • shipping tax class 运费税种
    • customer group 顾客属组
    • customer address 顾客地址

    它们的关系是:

    1. 税计算的起点是税规则。换言之,如果后台设置了若干税率、税区域、顾客税种、产品税种、运费税种,却没有设置税规则,那么没有任何税生效。
    2. 只有税规则可以设置优先级,税区域无法设置优先级(除非去改数据库,id 越小的税区域优先)。
    3. 税规则是产品税种(或运费税种)、顾客税种、税区域在三维空间决定的。
    4. 后台界面设置税率和税区域在一个表单里,似乎税率由税区域直接决定,但并不一定能生效,原因见第3点。
    5. 税区域与顾客税种无关。这点曾是我困惑的地方,因为地址是顾客的一个属性,所以我很自然地认为顾客地址就对应税区域。如果尝试着放弃“顾客地址就对应税区域”思维,一切都容易理解了。
    6. 税规则的三维空间较难描述,可以这么来简化:先拿产品税种(或运费税种)和顾客税种交叉排列组合出多个税规则,再拿税区域去套,套中的税规则就生效,套不中就不生效。
    7. 顾客属组和顾客税种是多对一的关系。
    8. 在前台 checkout 时,Magento 会根据顾客地址启用一条或多条税规则,但后台 create order 时,Magento 的税规则却无视顾客地址。这或许是 Magento 的一个 bug。

    我以一个实例说明如何在 Magento 中设置 UK VAT。以我目前所知,within the UK, postcode JE 和 GY9 开头的区域可以免 VAT,其他区域需加收 VAT,英国以外免 VAT。因为我没有免 VAT 区域的完整列表,必须照顾到今后能随时添加更多的 postcode 进免 VAT 区域。我是这么设置的:

    • 因为产品比较单一,运费税率与产品税率相同,目前都是17.5%,所以只设一个产品税种,不搞运费税种,运费就沿用产品税种,便于理解。
    • 顾客税种分两种,普通和VAT FREE。顾客属组也分两种,普通和VAT FREE,一一对应便于理解。请注意,普通类不是指一定要收 VAT,而是按税规则的第三维——税区域智能决定税率;但免 VAT 类下的顾客一定是零税率,这么做是为了照顾后台 create order 时的 bug(姑且当是)。
    • 建若干个税区域, UK postcode JE* 税率为0、UK postcode GY9* 税率为0、UK postcode * 税率为17.%。其他国家的税率不需要一个建,因为没有税规则,那就是零税率。

    现在开始创建税规则,按优先级别排序:

    1. 产品税种全选(反正只有一个),顾客税种选普通,税区域选 UK postcode JE*和UK postcode GY9*
    2. 产品税种全选(反正只有一个),顾客税种选普通,税区域选 UK postcode *
    3. 产品税种全选(反正只有一个),顾客税种选VAT FREE,税区域选 UK postcode JE*

    前两条规则是为了前台 checkout,第三条税规则是为了照顾后台 create order 时的 bug。Magento 目前版本 1.4.0.1,后台 create order 若把顾客归类普通,bug 无视税区域的存在,也不管税规则的优先级,似乎只会挑一个最高税率。普通顾客税种有0和17.5%两种可能,后台却总是用17.5%去收税。为避免激发 bug,干脆,顾客税种选VAT FREE,VAT FREE税种只有一个可能,永远是0,只在后台使用。

    如果今后得知 UK postcode XYZ* 也免 VAT,那就再建一个税区域 UK postcode XYZ* 税率为0;然后进入已有的税规则1,税区域增选 UK postcode XYZ* 即可。虽然税区域 UK postcode * 创建在先,UK postcode XYZ* 创建在后,但税规则的优先级别保证了 UK postcode XYZ* 先于 UK postcode *。

    再说一个极端的例子,若 UK 免 VAT 的区域只有 JE* 和 GY9*,今后也不会增加了,那么先创建税区域 UK postcode JE*和UK postcode GY9*,后创建 UK postcode *,然后只需要第1条税规则里同时选中UK postcode JE*、UK postcode GY9* 和 UK postcode *,删除税规则2也可以保证 UK postcode JE*和UK postcode GY9* 的优先性。但这么做有悖 Magento tax 的设计思路,不可取。

  • Nginx try_files syntax

    今天在一台很久不用的服务器上测试 Magento search result page,URL /catalogsearch/result/?q=searchword,发现它不工作,但其他页面正常。这个症状让我联想到以前碰到的类似问题,Magento 无法获得 query_string,所以含问号的 URL 都不能处理,页面重定向到 referring URL。应该是 server rewrite 规则没有写正确,我想。打开 nginx 的配置文件一看,果然,当中一条规则用的是很久以前的写法,后来在不同的服务器上几经改进,production server 都已经用上了新规则。

    新规则的写法:


    location @magento {
    root $php_script_root;
    index index.php;
    if ($uri ~ ^/(media|js|skin)/) {
    break;
    }
    if (!-e $request_filename) {
    rewrite .* /index.php last;
    }
    }

    而老规则的写法:


    location @magento {
    root $php_script_root;
    index index.php;
    if ($uri ~ ^/(media|js|skin)/) {
    break;
    }
    try_files $uri $uri/ /index.php;
    }

    效果略有区别,我在 Difference of try_files to rewrite in Nginx 文章里有提及。不过,今天我还有一个新发现。

    我倾向于使用简介语法,try_files 就比 rewrite 简洁得多,难道 try_files 就没有办法应付带问号的 URL 吗?非也,是我不知道 Nginx 原本可以这么奥妙——用 $args 变量!

    因此,最新一条完美规则出炉了:


    location @magento {
    root $php_script_root;
    index index.php;
    if ($uri ~ ^/(media|js|skin)/) {
    break;
    }
    try_files $uri $uri/ /index.php?$args;
    }

  • Style Magento checkout form fields without touching the core code

    Although I can change css complete restyle the checkout forms, this is not what I want to discuss today. What I want to achieve are:

    1. fix City required asterisk not showing bug
    2. change Address lines from full width to half width as City

    The City required asterisk is hidden by a javascript wrongly. I found City and State/County/Province are put into one li, i.e. <li><div></div></li>. It might be something should happen on State/County/Province but happens on City. If I close li after City, and reopen it before State/County/Province, bug is fixed. By the way, I hide State/County/Province.

    To show a field in full length, Magento uses
    <li><label><span></span></label><input/></li>
    To show a field in half length, Magento uses
    <li><div><label><span></span></label><input/></div></li>

    So just find the right place to insert <div> and close it for Address lines, I easily style them to the same length of City.

  • Magento idea: business intelligence

    It is very time consuming to set Related Products, Cross Sells, Up Sells on individual product basis, and not accurate if you work them out just using your brain. It is good to see Magento has these marketing terms built in, but I hate to do the job by hand (or brain).

    So I am thinking of developing a business intelligence module for Magento to let computer work Related Products, Cross Sells, Up Sells for us.

    Here is the overview of this module:

    First, we need record more data than what Magento provides. we specify a period of data to data-mine.

    Then, for Related Products, mine product pageviews by each visit. Put a visitor’s all visited products into an array, and count all visitors’ arrays for every product-product combination.

    As for Cross Sells, mine orders received or fulfilled. The algorithm is similar to Related Products.

    As for Up Sells, mine the products were added to cart but dropped from final order. Compare the product dropped with the products in final order, and if the former is of higher value, and some sort related to the latter, we can call the former Up Sells. This is my definition, and the algorithm is some sort complicated.

  • Extend Magento inventory management

    Magento inventory management is very basic – only an Inventory Qty figure. Strictly speaking, it is not an “in-stock” figure, but an “orderable”. Because orders keep coming in but you only do despatch once a day, Magento inventory quantity changes upon order is placed online, so you can not tell how many pieces of a product sitting in the warehouse.

    Use only one figure to manage inventory is not enough. Imagine this scenario:
    You are doing stocktake someday. At the time of last despatch, Magento inventory quantity of product X is 100. Stocktaking takes many hours, and when it finishes, Magento inventory quantity has changed to 80. If you physically count product X as 95, you can not simply update Magento inventory figure to be 95. Instead you should change it to 75 (worked out as 80 – 100 + 95). It is very easy to make such mistakes in stocktaking, but I have only Magento to blame – if stocktaking is based on a figure which is changing from time to time, it is not rock solid.

    However, I am not thinking of introducing a 3rd party inventory management system to work with Magento, largely because I do not know which one can fit.

    I am thinking of a simple extension to Magento – use 3 figures to show stock level from different concerts. Let’s clarify the terminology first.
    Figure 1: inventory orderable, i.e. the existing Magento Inventory Qty;
    Figure 2: inventory in-stock, i.e. how many pieces in the warehouse;
    Figure 3: inventory coming, i.e. expected purchase.

    • When an order is placed, decrease inventory orderable
    • When an order is despatched, decrease inventory in-stock
    • When an order is cancelled before despatch, increase inventory orderable
    • When an order is cancelled after despatch, do nothing (some companies raise RMA at this point but we do not need it.)
    • When an order is returned, if goods in good condition, increase inventory orderable, and increase inventory in-stock
    • When a purchase order is placed (by us to our supplier), increase inventory coming
    • When a purchase order arrives, decrease inventory coming, increase inventory orderable, and increase inventory in-stock
    • When stocktake begins, snapshot inventory in-stock as inventory1
    • When stocktake ends, update inventory in-stock to inventory2, and add (inventory2 – inventory1) to inventory orderable

    If all above events are logged, we have a kind of traceability. The log gives some clue to analyse where “inventory2 – inventory1” is from.

    In case a customer asks “how many you have? I take them all”, we tell him/her inventory orderable.

    In case we need find out “how many on hold (for late despatch)”, we use the balance between inventory orderable and inventory in-stock.

    Inventory orderable (figure 1) is built-in with Magento. Inventory coming (figure 3) is not essential to stock control. We can introduce it after we have implemented inventory in-stock (figure 2).

  • Forget Magento bundle product, for now

    Magento bundle product type 是我想往已久的:如果 A bundle 和 B bundle 都捆绑了某些数量的 simple product,它就可以实现 MRP 中 material consumed 功能。一开始我把所有产品都设为 bundle product,结果发现在后台 create order 时,一个可选产品也没有,原来这里只能把 simple product 加入订单。

    总之,bundle product 的适用性大大有待提高,否则如同鸡肋。目前我全盘放弃了 bundle product,而且在新建产品选择 product type 时,我尽量使用 simple product,毫无疑问 simple product 的适用性是最高的。

  • 5 approaches to customise Magento product

    1. Configurable product. It is a built-in feature of Magento, and very easy to implement. No coding skills required. However, every sub product of the configurable must pre-exist as a simple product in Magento products. It is literally impossible to use it as made-to-order functionality, which aims at taking lots of parameters from customer’s input, calculating prices under millions scenarios.

    2. Product custom options. Again it is a built-in feature of Magento, and easy to implement. But it is labour intensive to set it up if you have thousands of products of same set of custom options. Alternatively, you can buy an extension called Custom Options Template for less than $100. But I do not like data redundancy. Also, custom options on its own can not build the prices on the fly.

    3. Made-to-order extension (MTO) developed by metrofindings.com. It does not require coding, either. I must admit it is a promising extension to some business. However, I think the majority of business pricing models are much more complicated than this MTO module can dealt with. It is not possible to describe pricing just based on xml or cvs, as far as my business is concerned.

    4. My favourite approach version 1.0. It is inspired by MTO module with two major differences.
    a) All raw data are stored in database rather than external files so a backend administrator can manage data changes without uploading xml or cvs files. I think it is a bad idea to mix daily maintenance with programming.
    b) All business logics (cost / profit calculation, supplier screening, etc) fully rely on programming. Because I am a programmer so it is easier for me to control the program to follow my business logics. But if you are not, you’d better find a programmer to do it rather than fiddling with xml. Your business logics are usually more complex than an xml can solve.

    5. My favourite approach version 2.0. When I developed version 1.0, I did not think carefully about how to store custom options. I added fields to quote_item and order_item, and created a session to store what customer’s inputs before they were written to database. Because it was not Magento native way to store options, when it came to reorder, I had to take extra care of those options. So, in version 2.0, I am going to (I have not implemented it) create ONE simple product that will be used for customisation across ALL made-to-order products. This product will be assigned a full range of custom options. If some custom options do not apply to certain made-to-order products, just leave them blank. And most importantly, this product will have a custom option to store sku telling us from which product the customisation is based on. I assume this approach does not break Magento reorder process. I only need to change some view templates to show this special product’s sku, image, description properly.

  • How Magento cache Blocks HTML output

    我曾错误地认为 Magento 安装后,只要启用 cache,Magento 就会缓存页面的大部分内容,比如,cms, product, category 的 content block。

    直到今天第 N 遍看 Magento wiki,才意识到 Magento 初始只启用了很小一部分 block cache。细想之下,Magento 默认不缓存 content block 有一定的道理:各用户对内容缓存的要求不一而足,所以 Magento 把这个问题留给用户自己去思考。

    Block cache 有三个参数,cache_lifetime 顾名思义,cache_tags 关系到 cache 何时更新,cache_key 关系到 cache 有多少个版本。

    在 Magento 目录下所有文件里搜索 cache_tags 这个词,我只发现它只出现在跟 Navigation (产品菜单),Footer (脚注),Adminhtml Menu (后台菜单) 相关的少数文件里。由此说明 Magento 根本没有缓存页面的最主要部分:content block。我联想到很久以前我在 Magento forum 上提问的一个问题:为什么 cache 只用掉了 0.5 MB 内存?当时我用的是memcached,结果热心人来问我 memcached 有没有安装正确啊,php-perl-memcache 有没有安装啊。就是没有人告诉我——一切正常,因为 Magento 尚未缓存页面主要内容

    了解了 Magento cache 机制,再根据自己的实际情况对 product page 缓存 content block 就简单了:只要在 extends Mage_Catalog_Block_Product_View 的基础上加入

        protected function _construct()
        {
            $this->addData(array(
                'cache_lifetime'    => 86400,  //seconds
                'cache_tags'        => array(Mage_Catalog_Model_Product::CACHE_TAG . "_" . $this->getProduct()->getId()),
                'cache_key'         => $this->getProduct()->getId(),
            ));
        }
    
    

    以此类推 cms page content block cache。Category page content block cache 稍微复杂一些,具体去看 Magento wiki。

    设置 content block cache 对速度优化效果显著,我的 product page requests per second 指标提高了约 70%。

    但我还是想让 Magento 跑得再快些,常说的那些 Magento 速度优化结果让我感觉不够畅快淋漓。我有个 page cache 想法,就是把整个页面缓存下来。Nginx 或其他的 web server 都有很好的机制去调度 html cache。据我测试,同一个静态内容的页面,保存为 html (Nginx 直接读取) 比保存为 php (经 php backend on socket or port 读取) 就快好几倍,这个结果让我对 page cache 充满了憧憬。

    如使用 page cache,必须对页面中的 dynamic block (如 sidebar cart,recent viewed/compared products, etc)进行改写,简言之就是 load pages in two stages by ajax。Magento Enterprise Edition 就有 page cache feature,但我不清楚它是不是跟我同个思路。

    与其买个 Magento Enterprise Edition,不过自己动手或请人实现 page cache。如果你恰好跟我有同样想法,请留言。

  • Solution to php fastcgi crashes

    One of my servers is running Fedora + Nginx + php-cgi (spawned). I noticed sometimes php-cgi crashes without a reason or warning (Nginx gives out 500 error), and I have to spawn it again.

    After some digging, I find exporting PHP_FCGI_MAX_REQUESTS to ENV cures the problem. As advised, I export PHP_FCGI_CHILDREN as well.

    To achieve that, in detail, just add the following to the top of spawn bash script.

    # Set these two env variables to reduce php-cgi crash
    PHP_FCGI_MAX_REQUESTS=1000
    export PHP_FCGI_MAX_REQUESTS
    PHP_FCGI_CHILDREN=5
    export PHP_FCGI_CHILDREN