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.receiptorEmail, 
      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_order.remark, 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
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
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
GO