contact us enquiry form validation

Posted on 2017-04-24

原版本中若不填写mobile可以提交但会提交报错如下

问题应该是在向数据库提交表格的过程中设定了mobile为其中必须填写的字段但是实际前端页面没有进行验证

此修改方法为

在路径Theme\customerMicro\Views\commom\contactus.cshtml
修改mobile model->phone

可能原因是在进行customerise的过程中调用了

@model ContactUsModel

@using Nop.Web.Models.Common;

然而这个两个model中可能没有预留关于mobile的validation 判定，所以使用phone的model进行判定

nopcommerce 3.3 source code

Posted on 2017-04-24

nopcommerce 3.3 source code

目前公司使用的nopcommerce 3.3 是无source code的版本，导致很多功能无法实现。
恢复步骤

download from nopcommerce.com 3.3 source code
open NopCommerce.sln
先按照正常步骤setup
在app data里更改数据库设置
把customerise的theme拷贝到路径

\nopCommerce_3.30_Source\Presentation\Nop.Web\Themes
在vs2008中刷新，在浏览器中查看，点击更新theme，可以进行更改theme进行还原。

然后怎么发布呢？由于是vs2012，不是2013，如果在vs里选择发布就一直会报错。所以目前的措施是直接从nop.web中把程序copy出来放到c盘iss发布环境，也就是WWW下面，在浏览器输入localhost，就可以在本地看到网站了

Excel bugs

Posted on 2017-04-24

Excel tips

从系统中导出的excel cell format 的格式调整

日期调整。有的单元格看上去是日期但其实是text，此时采用的方法使用data-数据分列

Excel的一些小坑巧用数据分列 - zeng-yanzi - zeng-yanzi 的博客
csv文件中的分隔符可能会导致本地修改后再导入数据库出错，采取的措施就是在本地进行修改后统一添加分隔符，方法也是通过数据分列
选中这列数字—点击数据—-点击分列—-下一步—-下一步–

–在右上角列数据格式下选择“文本”—-点击完成

nopcommerce monitor duplicated attributes

Posted on 2017-04-24

nopcommerce monitor duplicated attributes

import requests
import re
import csv
root = "websites"
a = open("./alertau.txt","wb")
columns = {"SKU":0,"Type":1,"Colour":2,"Media":3,"Pore size":4,"Length":5,"i.d.":6,"Volume":7,"Diameter":8,"Pack Size":9}
skus = []
skus.append(["SKU","Type","Colour","Media","Pore size","Length","i.d.","Volume","Diameter","Pack Size"])
def process():
    url = "websites"
    html = requests.get(url).content
    cs = extract(html)
    for c in cs:
        visit(c)
    write2file()
def write2file():
    csvfile = file('./au.csv', 'wb')
    writer = csv.writer(csvfile)
    writer.writerows(skus)
def visit(c):
    url = root + c
    print url
    html = requests.get(url).content
    maxnum = findmax(html)
    print maxnum
    for i in range(1,maxnum+1):
        visitv(c,i)
def visitv(c,i):
    url = root + c + "?pagenumber=%d" % i
    print url
    html = requests.get(url).content
    if iscat(html):
        links = extractlink(html)
        print links
        for link in links:
            visit(link)
    else:
        extractproduct(html)
def findmax(html):
    res = re.findall("pagenumber=(\d+)\">Last</a>", html, re.I|re.M|re.DOTALL)
    print res
    if res:
        return int(res[0])
    res = re.findall("pagenumber=(\d+)\">\d+", html, re.I|re.M|re.DOTALL)
    if res:
        return int(res[-1])
    return 1
def iscat(html):
    if html.find("<span class=\"label\">SKU: </span>") != -1:
        return False
    return True
def extractproduct(html):
    sku = extractsku(html)
    attr = extractattr(html)
    print sku
    print attr
    ats = set([])
    r = False
    for k,v in attr:
        if k in ats:
            r = True
        else:
            ats.add(k)
    if r:
        a.write("%s\n" % sku)
        a.flush()
    export(sku,attr)
def export(sku,attr):
    p = ["","","","","","","","","",""]
    p[0] = sku
    for k,v in attr:
        p[columns[k]] = v
    skus.append(p)
def extractsku(html):
    return re.findall("<span class=\"value\" itemprop=\"sku\" id=\"sku\-\d+\">(.*?)</span>", html, re.I|re.M|re.DOTALL)[0]
def extractattr(html):
    attr = []
    res = re.findall("<td class=\"a-left spec\-name\">(.*?)</td>\s+<td class=\"a-left spec-value\">(.*?)</td>",html,re.I|re.M|re.DOTALL)
    for a in res:
        attr.append([clean(a[0]),clean(a[1])])
    return attr
def clean(a):    
    return a.replace("\r\n","").strip()
def extractlink(html):
    res = []
    res1 = re.findall("<h2 class=\"title\">\s+<a href=\"(/.*?)\"", html, re.I|re.M|re.DOTALL)
    res2 = re.findall("<h2 class=\"product\-title\">\s+<a href=\"(/.*?)\"", html, re.I|re.M|re.DOTALL)
    for i in res1:
        res.append(i)
    for i in res2:
        res.append(i)
    return res
def extract(html):
    res = re.findall("<li class=\"inactive\">\s+<a href=\"(/.*?)\">.*?</a>\s+</li>",html,re.I|re.M|re.DOTALL)
    return [i for i in res]

if __name__ == "__main__":
    process()

sql depulicated

Posted on 2017-04-24

SQL Duplicatede

situation :
数据库里有很多sku是重复的，在进行处理的时候需要注意，所以特意写了一个查重sql语句来显示哪些是已经deleted
下面的语句中， group by 是以sku进行分组的意思，having可以理解成where，但是区别在于，在group by后面的就要使用having

SELECT  Sku,Deleted
  FROM [MicroAnalytix].[dbo].[Product] where 
      (Sku in (select Sku from [MicroAnalytix].[dbo].[Product] group by  Sku having count(*)>=2)) ;

GROUP BY 语句
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用

sql not in

Posted on 2017-04-24

SQL not in

select Id from [MicroAnalytix].[dbo].[Product] where Deleted=0 and Id NOT IN ( SELECT ProductId FROM [MicroAnalytix].[dbo].[TierPrice] );

sql exist

Posted on 2017-04-24

SQL

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT Email from [MicroAnalytix].[dbo].[Customer] where Id  
  in( select Customer_Id From [MicroAnalytix].[dbo].[Customer_CustomerRole_Mapping] where CustomerRole_Id=6);

remove application css

Posted on 2017-04-24

applications

<!--<a href="@Url.RouteUrl("BlogRSS", new { languageId = Model.WorkingLanguageId })" class="link-rss" title="@T("Blog.RSS.Hint")">@T("Blog.RSS")</a>
   <h1>
       @if (String.IsNullOrEmpty(Model.PagingFilteringContext.Tag))
       {
           if (Model.PagingFilteringContext.GetParsedMonth().HasValue)
           {
           @String.Format(T("Blog.FilteredByMonth").Text, Model.PagingFilteringContext.GetParsedMonth().Value.Year, Model.PagingFilteringContext.GetParsedMonth().Value.ToString("MMMM"))
           }
           else
           {
           @T("APPLICATIONS")
           }
       }
       else
       {
           @String.Format(T("Blog.TaggedWith").Text, Model.PagingFilteringContext.Tag)
       }
   </h1>-->

then go to style.css
```
rss
block:none
```

bulk add attribute

Posted on 2017-04-24

bulk add attribute

# ~*~ coding:utf-8 ~*~
#csv是一个读取csv文件的lib
import csv
#itertools生成组合和排列的lib
import itertools
#pyodbc是连接ms sql server的lib
import pyodbc
import MySQLdb
#cnxn是数据库连接  更改数据库连接方式，账号密码

#cursor是数据库浮标
cursor = cnxn.cursor()



#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("c:/Users/Jenny/Desktop/ski/attribute/attribute.csv")))
    header = reader[0]
    #遍历数据，并处理， tips：跳过第一行
    for i in reader[1:]:
        sku = i[0]
        product_id = getIdFromSku(sku)
        if product_id == 0:
            print "error sku %s" % sku
            continue
        options = i[1]
        option_id=int(options)
        if exist(product_id,option_id):
            pass
        else:
            insert(product_id,option_id)


def exist(product_id,option_id):
    sql = "select * from dbo.Product_SpecificationAttribute_Mapping where ProductId = %d and SpecificationAttributeOptionId = %d" % (product_id,option_id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    if len(rows)==0:
        return False
    else:
        print rows
        return True


def insert(product_id,option_id):
    sql = "insert into dbo.Product_SpecificationAttribute_Mapping (ProductId,SpecificationAttributeOptionId,DisplayOrder,AllowFiltering,ShowOnProductPage) values(%d,%d,%d,%d,%d)" % (product_id,option_id,0,0,1)
    print sql
    cursor.execute(sql)
    cnxn.commit()

def getIdFromSku(a):
    sql = "select Id from dbo.Product where Sku='%s' and deleted=0" % a
    cursor.execute(sql)
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0

def test(skuset):
    err = False
    for sku in skuset:
        product_id = getIdFromSku(sku)
        if not product_id:
            print "can't find product id where sku='%s'" % sku
            err = True    

if __name__ == "__main__":
    process()

tireprice

Posted on 2017-04-24

tireprice

# ~*~ coding:utf-8 ~*~
#csv是一个读取csv文件的lib
import csv
#itertools生成组合和排列的lib
import itertools
#pyodbc是连接ms sql server的lib
import pyodbc
import pymysql
#cnxn是数据库连接  更改数据库连接方式，账号密码

#cursor是数据库浮标
cursor = cnxn.cursor()


def test(skuset):
    err = False
    for sku in skuset:
        product_id = getIdFromSku(sku)
        if not product_id:
            print ("can't find product id where sku='%s'" % sku)
            err = True
#主方法
def process():
    #读取csv，转换成list 更改文件存储位置
    reader = list(csv.reader(open("c:/Users/Jenny/Desktop/ski/tierprice for customer/VIP-AU-1.csv")))
    header = reader[0]
    #遍历数据，并处理， tips：跳过第一行
    skuset = set([])
    for i in reader[1:]:
        sku = i[0]
        product_id = getIdFromSku(sku)
        if product_id == 0:
            print ("error sku %s" % sku)
            continue
        prices = i[1:]
        for j in range(len(prices)):
            price = float(prices[j])
            crid = int(header[j+1]) 
            updatetire(product_id)
            if find(product_id,crid):
                update(product_id,crid,price)
            else:
                insert(product_id,price,crid)
def update(product_id,crid,price):
    sql = "update dbo.TierPrice set Price=%f where ProductId=%d and CustomerRoleId=%d" %(price,product_id,crid)
    cursor.execute(sql)
    cnxn.commit()

def find(product_id,crid):
    sql ="select * from dbo.TierPrice where ProductId=%d and CustomerRoleId=%d" % (product_id,crid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    return rows

def updatetire(productid):
    sql = "update dbo.Product set HasTierPrices=1 where Id=%d" % productid
    print (sql)
    cursor.execute(sql)
    cnxn.commit()
def insert(product_id,price,crid):
    sql = "insert into dbo.TierPrice (ProductId,StoreId,CustomerRoleId,Quantity,Price) values(%d,0,%d,1,%f)" % (product_id,crid,price)
    print (sql)
    cursor.execute(sql)
    cnxn.commit()

def getIdFromSku(a):
    sql = "select Id from dbo.Product where Sku='%s' and deleted=0" % a
    cursor.execute(sql)
    row = cursor.fetchall()
    if row:
        return row[0][0]
    else:
        return 0


if __name__ == "__main__":
process()

Jenny Zeng

Loving code ,especially javascript, React Native

GitHub Linkedin