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.adminID, shop_admin.roleID, shop_admin.adminPwd, 
      shop_admin.adminStatus, shop_admin.creator, shop_admin.createDate, 
      shop_admin.creatorIP, shop_admin.IPLocation, shop_admin.isSystem, 
      shop_admin.adminName, shop_admin.lastLoginDate, 
      shop_admin.recentLoginDate, shop_admin.remark, 
      shop_adminRole.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
FROM shop_products INNER JOIN
      shop_oosBook ON shop_products.productID = shop_oosBook.productID
 go 
CREATE VIEW productAndImgView
AS
SELECT p.productID, p.categoryID, p.brandID, p.manuID, p.productName, p.stockQty, 
      p.lowLimit, p.upperLimit, p.soldQty, p.quotePrice, p.salePrice, p.wholeSalePrice, 
      p.costPrice, p.integral, p.productSize, p.productColor, p.productWeight, p.productBrief, 
      p.productDetails, p.isOnSale, p.viewTimes, p.webTitle, p.webKeyWord, 
      p.webDescript, p.templateName, p.isTemplate, p.orderIndex, p.uploadDate, 
      p.miniWholesale, p.maxWholesale, p.isTrash, p.isGift, p.changeIntegral, p.htmlID, 
      ISNULL(img.imageSmall, '') AS imageSmall, ISNULL(img.imageMiddle, '') 
      AS imageMiddle, ISNULL(img.imageBig, '') AS imageBig, img.GUIDCode
FROM shop_products p LEFT OUTER JOIN
          (SELECT GUIDCode, productID, imageSmall, imageMiddle, imageBig, rawImg
         FROM shop_productsImg
         WHERE (GUIDCode IN
                   (SELECT MIN(GUIDCode)
                  FROM shop_productsImg
                  GROUP BY productID))) img ON p.productID = img.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, 
      productAndImgView.productName, productAndImgView.imageSmall, 
      productAndImgView.htmlID
FROM shop_orderProducts LEFT OUTER JOIN
      productAndImgView ON 
      shop_orderProducts.productID = productAndImgView.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
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_productsImg.imageSmall, shop_productsImg.imageMiddle, 
      shop_productsImg.imageBig, shop_productsImg.GUIDCode, 
      shop_products.htmlID
FROM shop_products LEFT OUTER JOIN
      shop_productsImg ON 
      shop_products.productID = shop_productsImg.productID LEFT OUTER JOIN
      shop_productInTag ON 
      shop_products.productID = shop_productInTag.productID
WHERE (shop_productsImg.GUIDCode IN
          (SELECT MIN(guidcode)
         FROM shop_productsImg
         GROUP BY 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 returnProductView
AS
SELECT shop_returnProduct.GUIDCode, shop_returnProduct.reshippingID, 
      shop_returnProduct.productID, shop_returnProduct.productColor, 
      shop_returnProduct.productSize, shop_returnProduct.returnQty, 
      productAndImgView.productName, productAndImgView.imageSmall, 
      productAndImgView.htmlID, shop_returnProduct.buyPrice
FROM shop_returnProduct INNER JOIN
      productAndImgView ON 
      shop_returnProduct.GUIDCode = productAndImgView.GUIDCode
 go 
CREATE VIEW shippingBillView
AS
SELECT shop_shippingBill.shipppBillID, 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 shippingProductView
AS
SELECT shop_shippingProduct.GUIDCode, shop_shippingProduct.shipppBillID, 
      shop_shippingProduct.productID, shop_shippingProduct.shippingQty, 
      shop_shippingProduct.productColor, shop_shippingProduct.productSize, 
      productAndImgView.productName, productAndImgView.imageSmall, 
      productAndImgView.htmlID, shop_shippingProduct.buyPrice
FROM shop_shippingProduct LEFT OUTER JOIN
      productAndImgView ON 
      shop_shippingProduct.GUIDCode = productAndImgView.GUIDCode
 go 
CREATE VIEW favoriteView
AS
SELECT shop_favorite.GUIDCode, shop_favorite.memberID, 
      shop_favorite.productID, productAndImgView.productName, 
      productAndImgView.imageSmall, productAndImgView.htmlID
FROM shop_favorite INNER JOIN
      productAndImgView ON 
      shop_favorite.productID = productAndImgView.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