itemaccessor.cs.svn-base

MIED是用于个人帐务管理的小型软件

using System.Collections;
using System.Data.Common;
using DatabaseUtil;
using DatabaseUtil.Schemas;
using Mied.BusinessObject;
using Mied.BusinessObject.Bills;
using Mied.BusinessObject.Items;
using Mied.DAL.Cache;
using Mied.DAL.Exceptions;
using Mied.DAL.TableSchema;
using CommonUtil;

namespace Mied.DAL.Accesses
{
    public class ItemAccessor : AccessorCache
    {
        public ItemAccessor(MiedDatabase database)
            : base(database, ProductSchema.TableName)
        {
        }

        public Product SelectProduct(int id)
        {
            return (Product)base.Select(id);
        }

        public ProductList SelectProductList()
        {
            DbCommand command = this.CommandSelectEntity();
            return (ProductList)SelectList(command);
        }

        public void Verify(BillLineItem lineItem)
        {
            if (string.IsNullOrEmpty(lineItem.Description))
                return;

            int? id = base.SelectID(ProductSchema.FieldDescription, lineItem.Description);
            if (id != null)
            {
                lineItem.ProductID = id.Value;
                return;
            }

            Product product = new Product();
            lineItem.CopyTo(product);
            this.Save(product);
            lineItem.ProductID = product.ID;
        }

        public bool ItemGroupReferenced(int groupID, ItemGroupType type)
        {
            string groupIDField = ProductSchema.FieldCategoryID;
            if (type == ItemGroupType.Class)
                groupIDField = ProductSchema.FieldClassID;
            else if (type == ItemGroupType.Type)
                groupIDField = ProductSchema.FieldTypeID;

            int count = CommandHelper.SelectCount(this.Database.Connection, this.TableName, groupIDField, groupID);
            return count > 0;
        }

        public void UpdateInventories(ItemInventoryList updatedList, DbTransaction transaction)
        {
            ItemInventoryList currentList = this.SelectItemInventoryList(updatedList);
            ItemInventoryList newList = currentList + updatedList;
            foreach (ItemInventory item in newList)
                UpdateInventory(item, transaction);
        }

        protected override Entity ReadEntity(RecordReader reader)
        {
            Product product = new Product();

            product.ID = reader.GetInt32(ProductSchema.FieldID);
            product.Description = reader.GetString(ProductSchema.FieldDescription);
            product.Col1 = reader.GetString(ProductSchema.FieldDate);
            product.Col2 = reader.GetString(ProductSchema.FieldItemNumber);
            product.Col3 = reader.GetString(ProductSchema.FieldDepartment);
            product.VendorPartNumber = reader.GetString(ProductSchema.FieldVendorPartNumber);
            product.UPCNumber = reader.GetString(ProductSchema.FieldUPCNumber);
            product.Weight = reader.GetString(ProductSchema.FieldWeight);
            product.Measurements = reader.GetString(ProductSchema.FieldMeasurements);
            product.Status = reader.GetBoolean(ProductSchema.FieldStatus);
            product.SalePrice = reader.GetDecimal(ProductSchema.FieldSalePrice);
            product.SaleDiscount = reader.GetDecimal(ProductSchema.FieldSaleDiscount);
            product.PurchasePrice = reader.GetDecimal(ProductSchema.FieldPurchasePrice);
            product.Tax1 = reader.GetBoolean(ProductSchema.FieldTax1);
            product.Tax2 = reader.GetBoolean(ProductSchema.FieldTax2);
            product.Inventory = reader.GetDecimal(ProductSchema.FieldInventory);
            product.InventoryWarning = reader.GetDecimal(ProductSchema.FieldInventoryWarning);
            product.Notes = reader.GetString(ProductSchema.FieldNotes);
            product.LastActivity = reader.GetString(ProductSchema.FieldLastActivity);
            int? categoryID = reader.GetInt32Null(ProductSchema.FieldCategoryID);
            int? classID = reader.GetInt32Null(ProductSchema.FieldClassID);
            int? typeID = reader.GetInt32Null(ProductSchema.FieldTypeID);
            product.CategoryUIName = this.SelectItemGroupUIName(categoryID);
            product.ClassUIName = this.SelectItemGroupUIName(classID);
            product.TypeUIName = this.SelectItemGroupUIName(typeID);

            return product;
        }

        protected override CommandFieldValueList BuildPairList(Entity entity)
        {
            Product product = (Product)entity;
            CommandFieldValueList pairList = new CommandFieldValueList(product.ID);
            pairList.Add(ProductSchema.FieldDescription, product.Description);
            pairList.Add(ProductSchema.FieldDate, product.Col1);
            pairList.Add(ProductSchema.FieldItemNumber, product.Col2);
            pairList.Add(ProductSchema.FieldDepartment, product.Col3);
            pairList.Add(ProductSchema.FieldVendorPartNumber, product.VendorPartNumber);
            pairList.Add(ProductSchema.FieldUPCNumber, product.UPCNumber);
            pairList.Add(ProductSchema.FieldWeight, product.Weight);
            pairList.Add(ProductSchema.FieldMeasurements, product.Measurements);
            pairList.Add(ProductSchema.FieldStatus, product.Status);
            pairList.Add(ProductSchema.FieldSalePrice, product.SalePrice);
            pairList.Add(ProductSchema.FieldSaleDiscount, product.SaleDiscount);
            pairList.Add(ProductSchema.FieldPurchasePrice, product.PurchasePrice);
            pairList.Add(ProductSchema.FieldTax1, product.Tax1);
            pairList.Add(ProductSchema.FieldTax2, product.Tax2);
            pairList.Add(ProductSchema.FieldInventory, product.Inventory);
            pairList.Add(ProductSchema.FieldInventoryWarning, product.InventoryWarning);
            pairList.Add(ProductSchema.FieldNotes, product.Notes);
            pairList.Add(ProductSchema.FieldLastActivity, product.LastActivity);

            int? categoryID = this.VerifyItemGroup(product.CategoryUIName, ItemGroupType.Category);
            int? classID = this.VerifyItemGroup(product.ClassUIName, ItemGroupType.Class);
            int? typeID = this.VerifyItemGroup(product.TypeUIName, ItemGroupType.Type);

            pairList.Add(ProductSchema.FieldCategoryID, categoryID);
            pairList.Add(ProductSchema.FieldClassID, classID);
            pairList.Add(ProductSchema.FieldTypeID, typeID);
            return pairList;
        }

        protected override IList CreateCacheListObject()
        {
            return new ItemCacheList(this, this.Database.ItemGroupsAccessor);
        }

        protected override IList CreateEntityList()
        {
            return new ProductList();
        }

        protected override void SaveBefore(Entity entity, DbTransaction transaction)
        {
            Product product = (Product)entity;
            int? id = base.SelectID(ProductSchema.FieldDescription, product.Description);
            if (id != null)
                throw new ItemDescriptionDuplicateException(product);
        }

        protected override void SaveAfter(Entity entity, Entity entityOld, DbTransaction transaction)
        {
            base.SaveAfter(entity, entityOld, transaction);

            if (null == entityOld) //insert action
            {
                Product product = (Product)entity;

                string strLastActivity = CommonHelper.LastActivity_ProductCreated;
                string strNote = product.Notes;

                CommonHelper.ConstructLastActivityAndNote(ref strLastActivity, ref strNote);
                this.UpdateLastActivity(product.ID.Value, strLastActivity, strNote, transaction);
            }
        }

        private void UpdateLastActivity(int productID, string lastActivity, string note, DbTransaction transaction)
        {
            DbCommand command = transaction.Connection.CreateCommand();
            command.CommandText = string.Format("UPDATE {0} SET [{1}] = @{1}, [{2}] = @{2}",
                this.TableName, ProductSchema.FieldLastActivity, ProductSchema.FieldNotes);
            command.Transaction = transaction;

            CommandFactory.WhereAnd(command, ProductSchema.FieldID, productID);
            CommandFactory.AddCommandParameter(command, "@" + ProductSchema.FieldLastActivity, lastActivity);
            CommandFactory.AddCommandParameter(command, "@" + ProductSchema.FieldNotes, note);

            CommandHelper.ExecuteCommand(command);
        }

        private string SelectItemGroupUIName(int? id)
        {
            if (id == null)
                return string.Empty;

            return this.Database.ItemGroupsAccessor.SelectUIName(id.Value);
        }

        private int? VerifyItemGroup(string uiName, ItemGroupType type)
        {
            if (string.IsNullOrEmpty(uiName))
                return null;

            return this.Database.ItemGroupsAccessor.VerifyGroup(uiName, type);
        }

        private ItemInventoryList SelectItemInventoryList(ItemInventoryList list)
        {
            CommandFieldList fields = new CommandFieldList(this.TableName);
            fields.UsingTableName = false;
            fields.Add(ProductSchema.FieldID);
            fields.Add(ProductSchema.FieldDescription);
            fields.Add(ProductSchema.FieldInventory);
            DbCommand command = CommandFactory.SelectFields(this.Database.Connection, fields);
            foreach (ItemInventory item in list)
                CommandFactory.WhereOr(command, ProductSchema.FieldID, item.ID);

            ItemInventoryList resultList = new ItemInventoryList();
            CommandHelper.ExecuteReader(command, this.ReadtemQuantity, resultList.DataList);
            return resultList;
        }

        private object ReadtemQuantity(RecordReader reader)
        {
            int id = reader.GetInt32(ProductSchema.FieldID);
            string description = reader.GetString(ProductSchema.FieldDescription);
            ItemInventory item = new ItemInventory(description, id);
            item.Inventory = reader.GetDecimal(ProductSchema.FieldInventory);
            return item;
        }

        private void UpdateInventory(ItemInventory item, DbTransaction transaction)
        {
            DbCommand command = CommandFactory.Update(transaction.Connection, this.TableName, ProductSchema.FieldInventory, item.Inventory);
            CommandFactory.WhereAnd(command, ProductSchema.FieldID, item.ID);
            command.Transaction = transaction;
            CommandHelper.ExecuteCommand(command);
        }
    }
}