CREATE VIEW OrderPayDelView
AS
SELECT shop_order.orderID, shop_order.memberID, shop_order.receiptor, 
      shop_order.country, shop_order.province, shop_order.city, 
      shop_order.receiptorAdd, shop_order.receiptorTell, 
      shop_order.receiptorMobi, shop_order.Email, shop_order.receiptorZip, 
      shop_order.deliveryID, shop_order.paymentID, shop_order.carriageSum, 
      shop_order.productsSum, shop_order.adjustSum, shop_order.taxSum, 
      shop_order.insuranceFee, shop_order.handlingCharge, 
      shop_order.couponSum, shop_order.orderDate, shop_order.appointDate, 
      shop_order.orderIP, shop_order.orderIPLocation, shop_order.isNew, 
      shop_order.invoiceTitle, shop_order.priBuilding, 
      shop_order.orderStatus, shop_order.payStatus, shop_order.isTrash, 
      shop_order.shippingStatus, shop_order.orderTags, 
      shop_deliveryManager.deliveryName, shop_payment.paymentName, 
      shop_order.carriageSum + shop_order.productsSum + shop_order.adjustSum
       + shop_order.taxSum + shop_order.handlingCharge + shop_order.insuranceFee
       - shop_order.couponSum AS grossAmount, shop_order.notePad, 
      shop_order.remark
FROM shop_order LEFT OUTER JOIN
      shop_deliveryManager ON 
      shop_order.deliveryID = shop_deliveryManager.deliveryID LEFT OUTER JOIN
      shop_payment ON shop_order.paymentID = shop_payment.paymentID
 go 
CREATE VIEW account_view
AS
SELECT memberID, SUM(recharge) AS recharge, SUM(consume) AS consume, 
      SUM(recharge) - SUM(consume) AS balance
FROM shop_account
GROUP BY memberID
 go 
CREATE VIEW admin_view
AS
SELECT shop_admin.*, shop_adminRole.roleName AS roleName
FROM shop_admin INNER JOIN
      shop_adminRole ON shop_admin.roleID = shop_adminRole.roleID
 go 
CREATE VIEW blogAndCateView
AS
SELECT shop_blogs.blogID, shop_blogs.cateID, shop_blogs.webTitle, 
      shop_blogs.webKeyWord, shop_blogs.webDescript, 
      shop_blogs.blogTitle, shop_blogs.blogBrief, shop_blogs.blogContent, 
      shop_blogs.postDate, shop_blogs.adminID, shop_blogs.orderIndex, 
      shop_blogs.isTrash, shop_blogCate.cateName
FROM shop_blogs INNER JOIN
      shop_blogCate ON shop_blogs.cateID = shop_blogCate.cateID
 go 
CREATE VIEW membLeveInfo_view
AS
SELECT shop_member.memberID, shop_member.memberPwd, 
      shop_member.memberEmail, shop_member.memberStatus, 
      shop_member.levelID, shop_memberLevel.levelName, 
      shop_member.registerIP, shop_member.registerIPLocation, 
      shop_member.registerDate, shop_member.activationCode, 
      shop_memberLevel.requiredIntegral, shop_memberLevel.discountRate, 
      shop_memberLevel.levelKind, shop_memberLevel.isDefault, 
      shop_member.isNew, ISNULL(shop_memberInfo.realName, '') AS realName, 
      ISNULL(shop_memberInfo.sex, '') AS sex, 
      ISNULL(shop_memberInfo.birthday, '') AS birthday, 
      ISNULL(shop_memberInfo.realAdd, '') AS realAdd, 
      ISNULL(shop_memberInfo.realZip, '') AS realZip, 
      ISNULL(shop_memberInfo.realTel, '') AS realTel, 
      ISNULL(shop_memberInfo.mobilCode, '') AS mobilCode, 
      ISNULL(shop_memberInfo.QQ, '') AS QQ, ISNULL(shop_memberInfo.ww, '') 
      AS ww, ISNULL(shop_memberInfo.skype, '') AS skype, 
      ISNULL(shop_memberInfo.answer, '') AS answer, 
      ISNULL(shop_memberInfo.answerKey, '') AS answerKey, 
      shop_member.isTrash, shop_member.isWholesalers, 
      shop_member.lastLoginDate, shop_member.memberPhoto, 
      shop_member.expireDate
FROM shop_member INNER JOIN
      shop_memberLevel ON 
      shop_member.levelID = shop_memberLevel.levelID LEFT OUTER JOIN
      shop_memberInfo ON 
      shop_member.memberID = shop_memberInfo.memberID
 go 
CREATE VIEW oosProduct_view
AS
SELECT shop_oosBook.GUIDCode, shop_oosBook.memberID, 
      shop_oosBook.productID, shop_oosBook.Email, 
      shop_oosBook.bookDate, shop_oosBook.isSent, shop_oosBook.isTrash, 
      shop_products.productName, shop_products.isOnSale, 
      shop_oosBook.noticeDate, shop_products.htmlID, 
      shop_products.imageSmall
FROM shop_products INNER JOIN
      shop_oosBook ON shop_products.productID = shop_oosBook.productID
 go 
CREATE VIEW orderProductsView
AS
SELECT shop_orderProducts.GUIDCode, shop_orderProducts.orderID, 
      shop_orderProducts.productID, shop_orderProducts.buyPrice, 
      shop_orderProducts.buyQty, 
      shop_orderProducts.buyPrice * shop_orderProducts.buyQty AS productSum, 
      shop_orderProducts.sentQty, shop_orderProducts.returnQty, 
      shop_orderProducts.productSize, shop_orderProducts.productColor, 
      shop_products.productName, shop_products.imageSmall, 
      shop_products.htmlID, shop_products.outProductID
FROM shop_orderProducts LEFT OUTER JOIN
      shop_products ON 
      shop_orderProducts.productID = shop_products.productID
 go       
CREATE VIEW proBraCatManView
AS
SELECT shop_products.productID, shop_products.categoryID, 
      shop_products.brandID, shop_products.manuID, 
      shop_products.productName, shop_products.stockQty, 
      shop_products.lowLimit, shop_products.upperLimit, 
      shop_products.soldQty, shop_products.quotePrice, 
      shop_products.salePrice, shop_products.wholeSalePrice, 
      shop_products.costPrice, shop_products.integral, 
      shop_products.productSize, shop_products.productColor, 
      shop_products.productWeight, shop_products.productBrief, 
      shop_products.productDetails, shop_products.isOnSale, 
      shop_products.templateName, shop_products.isTemplate, 
      shop_products.orderIndex, shop_products.uploadDate, 
      shop_products.miniWholesale, shop_products.maxWholesale, 
      shop_products.isTrash, shop_products.isGift, 
      shop_products.changeIntegral, ISNULL(shop_brand.brandName, '') 
      AS brandName, ISNULL(shop_manufacturer.manuName, '') AS manuName, 
      ISNULL(shop_productCate.categoryName, '') AS categoryName, 
      shop_products.htmlID, shop_products.outProductID, 
      shop_products.priceUnit
FROM shop_products LEFT OUTER JOIN
      shop_brand ON 
      shop_products.brandID = shop_brand.brandID LEFT OUTER JOIN
      shop_productCate ON 
      shop_products.categoryID = shop_productCate.categoryID LEFT OUTER JOIN
      shop_manufacturer ON 
      shop_products.manuID = shop_manufacturer.manuID
 go 
CREATE VIEW productAndTagView
AS
SELECT ISNULL(shop_productInTag.tagID, '') AS tagID, shop_products.productID, 
      shop_products.categoryID, shop_products.brandID, 
      shop_products.manuID, shop_products.productName, 
      shop_products.stockQty, shop_products.lowLimit, 
      shop_products.upperLimit, shop_products.soldQty, 
      shop_products.quotePrice, shop_products.salePrice, 
      shop_products.wholeSalePrice, shop_products.costPrice, 
      shop_products.integral, shop_products.productSize, 
      shop_products.productColor, shop_products.productWeight, 
      shop_products.productBrief, shop_products.productDetails, 
      shop_products.isOnSale, shop_products.viewTimes, 
      shop_products.webTitle, shop_products.webKeyWord, 
      shop_products.webDescript, shop_products.templateName, 
      shop_products.isTemplate, shop_products.orderIndex, 
      shop_products.uploadDate, shop_products.miniWholesale, 
      shop_products.maxWholesale, shop_products.isTrash, 
      shop_products.isGift, shop_products.changeIntegral, 
      shop_products.htmlID, shop_products.priceUnit, 
      shop_products.outProductID, shop_products.imageSmall, 
      shop_products.imageMiddle, shop_products.imageBig
FROM shop_products LEFT OUTER JOIN
      shop_productInTag ON 
      shop_products.productID = shop_productInTag.productID
 go 
CREATE VIEW returnBillView
AS
SELECT shop_returnBill.reshippingID, shop_returnBill.orderID, 
      shop_returnBill.deliveryID, shop_returnBill.deliveryBillID, 
      shop_returnBill.shippingFee, shop_returnBill.insuranceFee, 
      shop_returnBill.returner, shop_returnBill.returnAddress, 
      shop_returnBill.returnTel, shop_returnBill.returnMobi, 
      shop_returnBill.returnZip, shop_returnBill.returnReason, 
      shop_returnBill.returnDate, shop_returnBill.bookDate, 
      shop_returnBill.remark, shop_returnBill.isTrash, 
      shop_returnBill.isComplete, shop_order.memberID, 
      shop_deliveryManager.deliveryName, shop_deliveryManager.expressID, 
      shop_deliveryCompany.expressName, 
      shop_deliveryCompany.expressWebsite
FROM shop_returnBill INNER JOIN
      shop_order ON 
      shop_returnBill.orderID = shop_order.orderID INNER JOIN
      shop_deliveryManager ON 
      shop_returnBill.deliveryID = shop_deliveryManager.deliveryID INNER JOIN
      shop_deliveryCompany ON 
      shop_deliveryManager.expressID = shop_deliveryCompany.expressID
 go 
CREATE VIEW shippingBillView
AS
SELECT shop_shippingBill.shippBillID, shop_shippingBill.receiptor, 
      shop_shippingBill.receiptorAdd, shop_shippingBill.receiptorEmail, 
      shop_shippingBill.receiptorMobi, shop_shippingBill.receiptorTell, 
      shop_shippingBill.receiptorZip, shop_shippingBill.orderID, 
      shop_shippingBill.deliveryID, shop_shippingBill.shippingFee, 
      shop_shippingBill.insuranceFee, shop_shippingBill.deliveryBillID, 
      shop_shippingBill.shippingDate, shop_shippingBill.receiptDate, 
      shop_shippingBill.isReceipt, shop_shippingBill.bookDate, 
      shop_shippingBill.remark, shop_shippingBill.isTrash, 
      shop_shippingBill.isComplete, shop_deliveryManager.deliveryName, 
      shop_deliveryCompany.expressID, shop_deliveryCompany.expressName, 
      shop_deliveryCompany.expressWebsite, shop_order.memberID
FROM shop_shippingBill INNER JOIN
      shop_deliveryManager ON 
      shop_shippingBill.deliveryID = shop_deliveryManager.deliveryID INNER JOIN
      shop_deliveryCompany ON 
      shop_deliveryManager.expressID = shop_deliveryCompany.expressID INNER
       JOIN
      shop_order ON shop_shippingBill.orderID = shop_order.orderID
 go 
CREATE VIEW favoriteView
AS
SELECT shop_favorite.GUIDCode, shop_favorite.memberID, 
      shop_favorite.productID, shop_products.productName, 
      shop_products.imageSmall, shop_products.htmlID
FROM shop_favorite INNER JOIN
      shop_products ON shop_favorite.productID = shop_products.productID
 go 
CREATE VIEW roleAndPowerView
AS
SELECT shop_adminRole.roleID, shop_rolePower.powerID, 
      shop_power.powerName, shop_power.menuID
FROM shop_adminRole INNER JOIN
      shop_rolePower ON 
      shop_adminRole.roleID = shop_rolePower.roleID INNER JOIN
      shop_power ON shop_rolePower.powerID = shop_power.powerID