CREATE VIEW dbo.OrderPayDelView
AS
SELECT dbo.shop_order.orderID, dbo.shop_order.memberID, dbo.shop_order.receiptor, 
      dbo.shop_order.Country, dbo.shop_order.province, dbo.shop_order.city, 
      dbo.shop_order.receiptorAdd, dbo.shop_order.receiptorTell, 
      dbo.shop_order.receiptorMobi, dbo.shop_order.receiptorEmail, 
      dbo.shop_order.receiptorZip, dbo.shop_order.deliveryID, dbo.shop_order.paymentID, 
      dbo.shop_order.carriageSum, dbo.shop_order.productsSum, 
      dbo.shop_order.adjustSum, dbo.shop_order.taxSum, dbo.shop_order.insuranceFee, 
      dbo.shop_order.handlingCharge, dbo.shop_order.couponSum, 
      dbo.shop_order.orderDate, dbo.shop_order.appointDate, dbo.shop_order.orderIP, 
      dbo.shop_order.orderIPLocation, dbo.shop_order.isNew, dbo.shop_order.invoiceTitle, 
      dbo.shop_order.priBuilding, dbo.shop_order.orderStatus, dbo.shop_order.payStatus, 
      dbo.shop_order.isTrash, dbo.shop_order.shippingStatus, dbo.shop_order.orderTags, 
      dbo.shop_order.remark, dbo.shop_deliveryManager.deliveryName, 
      dbo.shop_payment.paymentName, 
      dbo.shop_order.carriageSum + dbo.shop_order.productsSum + dbo.shop_order.adjustSum
       + dbo.shop_order.taxSum + dbo.shop_order.handlingCharge + dbo.shop_order.insuranceFee
       - dbo.shop_order.couponSum AS grossAmount
FROM dbo.shop_order LEFT OUTER JOIN
      dbo.shop_deliveryManager ON 
      dbo.shop_order.deliveryID = dbo.shop_deliveryManager.deliveryID LEFT OUTER JOIN
      dbo.shop_payment ON dbo.shop_order.paymentID = dbo.shop_payment.paymentID
GO
CREATE VIEW dbo.account_view
AS
SELECT memberID, SUM(recharge) AS recharge, SUM(consume) AS consume, 
      SUM(recharge) - SUM(consume) AS balance
FROM dbo.shop_account
GROUP BY memberID
GO
CREATE VIEW dbo.admin_view
AS
SELECT dbo.shop_admin.*, dbo.shop_adminRole.roleName AS roleName
FROM dbo.shop_admin INNER JOIN
      dbo.shop_adminRole ON dbo.shop_admin.roleID = dbo.shop_adminRole.roleID
GO
CREATE VIEW dbo.blogAndCateView
AS
SELECT dbo.shop_blogs.blogID, dbo.shop_blogs.cateID, dbo.shop_blogs.webTitle, 
      dbo.shop_blogs.webKeyWord, dbo.shop_blogs.webDescript, 
      dbo.shop_blogs.blogTitle, dbo.shop_blogs.blogBrief, dbo.shop_blogs.blogContent, 
      dbo.shop_blogs.postDate, dbo.shop_blogs.adminID, dbo.shop_blogs.orderIndex, 
      dbo.shop_blogs.isTrash, dbo.shop_blogCate.cateName
FROM dbo.shop_blogs INNER JOIN
      dbo.shop_blogCate ON dbo.shop_blogs.cateID = dbo.shop_blogCate.cateID
GO
CREATE VIEW dbo.membLeveInfo_view
AS
SELECT dbo.shop_member.memberID, dbo.shop_member.memberPwd, 
      dbo.shop_member.memberEmail, dbo.shop_member.memberStatus, 
      dbo.shop_member.levelID, dbo.shop_memberLevel.levelName, 
      dbo.shop_member.registerIP, dbo.shop_member.registerIPLocation, 
      dbo.shop_member.registerDate, dbo.shop_member.activationCode, 
      dbo.shop_memberLevel.requiredIntegral, dbo.shop_memberLevel.discountRate, 
      dbo.shop_memberLevel.levelKind, dbo.shop_memberLevel.isDefault, 
      dbo.shop_member.isNew, ISNULL(dbo.shop_memberInfo.realName, '') AS realName, 
      ISNULL(dbo.shop_memberInfo.sex, '') AS sex, 
      ISNULL(dbo.shop_memberInfo.birthday, '') AS birthday, 
      ISNULL(dbo.shop_memberInfo.realAdd, '') AS realAdd, 
      ISNULL(dbo.shop_memberInfo.realZip, '') AS realZip, 
      ISNULL(dbo.shop_memberInfo.realTel, '') AS realTel, 
      ISNULL(dbo.shop_memberInfo.mobilCode, '') AS mobilCode, 
      ISNULL(dbo.shop_memberInfo.QQ, '') AS QQ, ISNULL(dbo.shop_memberInfo.ww, '') 
      AS ww, ISNULL(dbo.shop_memberInfo.skype, '') AS skype, 
      ISNULL(dbo.shop_memberInfo.answer, '') AS answer, 
      ISNULL(dbo.shop_memberInfo.answerKey, '') AS answerKey, 
      dbo.shop_member.isTrash, dbo.shop_member.isWholesalers, 
      dbo.shop_member.lastLoginDate
FROM dbo.shop_member INNER JOIN
      dbo.shop_memberLevel ON 
      dbo.shop_member.levelID = dbo.shop_memberLevel.levelID LEFT OUTER JOIN
      dbo.shop_memberInfo ON 
      dbo.shop_member.memberID = dbo.shop_memberInfo.memberID
GO
CREATE VIEW dbo.oosProduct_view
AS
SELECT dbo.shop_oosBook.GUIDCode, dbo.shop_oosBook.memberID, 
      dbo.shop_oosBook.productID, dbo.shop_oosBook.EMail, 
      dbo.shop_oosBook.bookDate, dbo.shop_oosBook.isSent, dbo.shop_oosBook.isTrash, 
      dbo.shop_products.productName, dbo.shop_products.isOnSale, 
      dbo.shop_oosBook.noticeDate, dbo.shop_products.htmlID
FROM dbo.shop_products INNER JOIN
      dbo.shop_oosBook ON dbo.shop_products.productID = dbo.shop_oosBook.productID
GO
CREATE VIEW dbo.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 dbo.shop_products p LEFT OUTER JOIN
          (SELECT GUIDCode, productID, imageSmall, imageMiddle, imageBig, rawImg
         FROM dbo.shop_productsImg
         WHERE (GUIDCode IN
                   (SELECT MIN(GUIDCode)
                  FROM shop_productsImg
                  GROUP BY productID))) img ON p.productID = img.productID

GO
CREATE VIEW dbo.orderProductsView
AS
SELECT dbo.shop_orderProducts.GUIDCode, dbo.shop_orderProducts.orderID, 
      dbo.shop_orderProducts.productID, dbo.shop_orderProducts.buyPrice, 
      dbo.shop_orderProducts.buyQty, 
      dbo.shop_orderProducts.buyPrice * dbo.shop_orderProducts.buyQty AS productSum, 
      dbo.shop_orderProducts.sentQty, dbo.shop_orderProducts.returnQty, 
      dbo.shop_orderProducts.productSize, dbo.shop_orderProducts.productColor, 
      dbo.productAndImgView.productName, dbo.productAndImgView.imageSmall, 
      dbo.productAndImgView.htmlID
FROM dbo.shop_orderProducts LEFT OUTER JOIN
      dbo.productAndImgView ON 
      dbo.shop_orderProducts.productID = dbo.productAndImgView.productID
GO
CREATE VIEW dbo.proBraCatManView
AS
SELECT dbo.shop_products.productID, dbo.shop_products.categoryID, 
      dbo.shop_products.brandID, dbo.shop_products.manuID, 
      dbo.shop_products.productName, dbo.shop_products.stockQty, 
      dbo.shop_products.lowLimit, dbo.shop_products.upperLimit, 
      dbo.shop_products.soldQty, dbo.shop_products.quotePrice, 
      dbo.shop_products.salePrice, dbo.shop_products.wholeSalePrice, 
      dbo.shop_products.costPrice, dbo.shop_products.integral, 
      dbo.shop_products.productSize, dbo.shop_products.productColor, 
      dbo.shop_products.productWeight, dbo.shop_products.productBrief, 
      dbo.shop_products.productDetails, dbo.shop_products.isOnSale, 
      dbo.shop_products.templateName, dbo.shop_products.isTemplate, 
      dbo.shop_products.orderIndex, dbo.shop_products.uploadDate, 
      dbo.shop_products.miniWholesale, dbo.shop_products.maxWholesale, 
      dbo.shop_products.isTrash, dbo.shop_products.isGift, 
      dbo.shop_products.changeIntegral, ISNULL(dbo.shop_brand.brandName, '') 
      AS brandName, ISNULL(dbo.shop_manufacturer.manuName, '') AS manuName, 
      ISNULL(dbo.shop_productCate.categoryName, '') AS categoryName, 
      dbo.shop_products.htmlID
FROM dbo.shop_products LEFT OUTER JOIN
      dbo.shop_brand ON 
      dbo.shop_products.brandID = dbo.shop_brand.brandID LEFT OUTER JOIN
      dbo.shop_productCate ON 
      dbo.shop_products.categoryID = dbo.shop_productCate.categoryID LEFT OUTER JOIN
      dbo.shop_manufacturer ON 
      dbo.shop_products.manuID = dbo.shop_manufacturer.manuID
GO
CREATE VIEW dbo.productAndTagView
AS
SELECT ISNULL(dbo.shop_productInTag.tagID, '') AS tagID, dbo.shop_products.productID, 
      dbo.shop_products.categoryID, dbo.shop_products.brandID, 
      dbo.shop_products.manuID, dbo.shop_products.productName, 
      dbo.shop_products.stockQty, dbo.shop_products.lowLimit, 
      dbo.shop_products.upperLimit, dbo.shop_products.soldQty, 
      dbo.shop_products.quotePrice, dbo.shop_products.salePrice, 
      dbo.shop_products.wholeSalePrice, dbo.shop_products.costPrice, 
      dbo.shop_products.integral, dbo.shop_products.productSize, 
      dbo.shop_products.productColor, dbo.shop_products.productWeight, 
      dbo.shop_products.productBrief, dbo.shop_products.productDetails, 
      dbo.shop_products.isOnSale, dbo.shop_products.viewTimes, 
      dbo.shop_products.webTitle, dbo.shop_products.webKeyWord, 
      dbo.shop_products.webDescript, dbo.shop_products.templateName, 
      dbo.shop_products.isTemplate, dbo.shop_products.orderIndex, 
      dbo.shop_products.uploadDate, dbo.shop_products.miniWholesale, 
      dbo.shop_products.maxWholesale, dbo.shop_products.isTrash, 
      dbo.shop_products.isGift, dbo.shop_products.changeIntegral, 
      dbo.shop_productsImg.imageSmall, dbo.shop_productsImg.imageMiddle, 
      dbo.shop_productsImg.imageBig, dbo.shop_productsImg.GUIDCode, 
      dbo.shop_products.htmlID
FROM dbo.shop_products LEFT OUTER JOIN
      dbo.shop_productsImg ON 
      dbo.shop_products.productID = dbo.shop_productsImg.productID LEFT OUTER JOIN
      dbo.shop_productInTag ON 
      dbo.shop_products.productID = dbo.shop_productInTag.productID
WHERE (dbo.shop_productsImg.GUIDCode IN
          (SELECT MIN(guidcode)
         FROM shop_productsImg
         GROUP BY productID))
GO
CREATE VIEW dbo.returnBillView
AS
SELECT dbo.shop_returnBill.reshippingID, dbo.shop_returnBill.orderID, 
      dbo.shop_returnBill.deliveryID, dbo.shop_returnBill.deliveryBillID, 
      dbo.shop_returnBill.shippingFee, dbo.shop_returnBill.insuranceFee, 
      dbo.shop_returnBill.returner, dbo.shop_returnBill.returnAddress, 
      dbo.shop_returnBill.returnTel, dbo.shop_returnBill.returnMobi, 
      dbo.shop_returnBill.returnZip, dbo.shop_returnBill.returnReason, 
      dbo.shop_returnBill.returnDate, dbo.shop_returnBill.bookDate, 
      dbo.shop_returnBill.remark, dbo.shop_returnBill.isTrash, 
      dbo.shop_returnBill.isComplete, dbo.shop_order.memberID, 
      dbo.shop_deliveryManager.deliveryName, dbo.shop_deliveryManager.expressID, 
      dbo.shop_deliveryCompany.expressName, 
      dbo.shop_deliveryCompany.expressWebsite
FROM dbo.shop_returnBill INNER JOIN
      dbo.shop_order ON 
      dbo.shop_returnBill.orderID = dbo.shop_order.orderID INNER JOIN
      dbo.shop_deliveryManager ON 
      dbo.shop_returnBill.deliveryID = dbo.shop_deliveryManager.deliveryID INNER JOIN
      dbo.shop_deliveryCompany ON 
      dbo.shop_deliveryManager.expressID = dbo.shop_deliveryCompany.expressID
GO
CREATE VIEW dbo.returnProductView
AS
SELECT dbo.shop_returnProduct.GUIDCode, dbo.shop_returnProduct.reshippingID, 
      dbo.shop_returnProduct.productID, dbo.shop_returnProduct.productColor, 
      dbo.shop_returnProduct.productSize, dbo.shop_returnProduct.returnQty, 
      dbo.productAndImgView.productName, dbo.productAndImgView.imageSmall, 
      dbo.productAndImgView.htmlID, dbo.shop_returnProduct.buyPrice
FROM dbo.shop_returnProduct INNER JOIN
      dbo.productAndImgView ON 
      dbo.shop_returnProduct.GUIDCode = dbo.productAndImgView.GUIDCode
GO
CREATE VIEW dbo.shippingBillView
AS
SELECT dbo.shop_shippingBill.shipppBillID, dbo.shop_shippingBill.receiptor, 
      dbo.shop_shippingBill.receiptorAdd, dbo.shop_shippingBill.receiptorEmail, 
      dbo.shop_shippingBill.receiptorMobi, dbo.shop_shippingBill.receiptorTell, 
      dbo.shop_shippingBill.receiptorZip, dbo.shop_shippingBill.orderID, 
      dbo.shop_shippingBill.deliveryID, dbo.shop_shippingBill.shippingFee, 
      dbo.shop_shippingBill.insuranceFee, dbo.shop_shippingBill.deliveryBillID, 
      dbo.shop_shippingBill.shippingDate, dbo.shop_shippingBill.receiptDate, 
      dbo.shop_shippingBill.isReceipt, dbo.shop_shippingBill.bookDate, 
      dbo.shop_shippingBill.remark, dbo.shop_shippingBill.isTrash, 
      dbo.shop_shippingBill.isComplete, dbo.shop_deliveryManager.deliveryName, 
      dbo.shop_deliveryCompany.expressID, dbo.shop_deliveryCompany.expressName, 
      dbo.shop_deliveryCompany.expressWebsite, dbo.shop_order.memberID
FROM dbo.shop_shippingBill INNER JOIN
      dbo.shop_deliveryManager ON 
      dbo.shop_shippingBill.deliveryID = dbo.shop_deliveryManager.deliveryID INNER JOIN
      dbo.shop_deliveryCompany ON 
      dbo.shop_deliveryManager.expressID = dbo.shop_deliveryCompany.expressID INNER
       JOIN
      dbo.shop_order ON dbo.shop_shippingBill.orderID = dbo.shop_order.orderID
GO
CREATE VIEW dbo.shippingProductView
AS
SELECT dbo.shop_shippingProduct.GUIDCode, dbo.shop_shippingProduct.shipppBillID, 
      dbo.shop_shippingProduct.productID, dbo.shop_shippingProduct.shippingQty, 
      dbo.shop_shippingProduct.productColor, dbo.shop_shippingProduct.productSize, 
      dbo.productAndImgView.productName, dbo.productAndImgView.imageSmall, 
      dbo.productAndImgView.htmlID, dbo.shop_shippingProduct.buyPrice
FROM dbo.shop_shippingProduct LEFT OUTER JOIN
      dbo.productAndImgView ON 
      dbo.shop_shippingProduct.GUIDCode = dbo.productAndImgView.GUIDCode
GO
CREATE VIEW dbo.favoriteView
AS
SELECT dbo.shop_favorite.GUIDCode, dbo.shop_favorite.memberID, 
      dbo.shop_favorite.productID, dbo.productAndImgView.productName, 
      dbo.productAndImgView.imageSmall, dbo.productAndImgView.htmlID
FROM dbo.shop_favorite INNER JOIN
      dbo.productAndImgView ON 
      dbo.shop_favorite.productID = dbo.productAndImgView.productID
GO
CREATE VIEW dbo.roleAndPowerView
AS
SELECT dbo.shop_adminRole.roleID, dbo.shop_rolePower.powerID, 
      dbo.shop_power.powerName, dbo.shop_power.menuID
FROM dbo.shop_adminRole INNER JOIN
      dbo.shop_rolePower ON 
      dbo.shop_adminRole.roleID = dbo.shop_rolePower.roleID INNER JOIN
      dbo.shop_power ON dbo.shop_rolePower.powerID = dbo.shop_power.powerID
GO