billaccessor.cs

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

using System;
using System.Collections;
using System.Data.Common;
using System.Diagnostics;
using System.Text;
using DatabaseUtil;
using Mied.BusinessObject;
using Mied.BusinessObject.Bills;
using Mied.BusinessObject.Emails;
using Mied.BusinessObject.Items;
using Mied.DAL.Exceptions;
using Mied.DAL.TableSchema;
using SqlcePlugin;
using Mied.BusinessObject.Preferences;

namespace Mied.DAL.Accesses
{
    public abstract class BillAccessor : AccessorSelect
    {
        public BillAccessor(MiedDatabase database, Type billObjectType, string firstNumber, string numberPrefix, InventoryMode inventoryMode)
            : base(database, BillSchema.TableName)
        {
            this.BillObjectType = billObjectType;
            this.FirstNumber = firstNumber;
            this.NumberPrefix = numberPrefix;
            this.InventoryMode = inventoryMode;
        }

        public Bill SelectBill(int id)
        {
            return (Bill)base.Select(id);
        }

        public IList SelectBillByRelationNumber(string relationNumber)
        {
            return base.SelectList(BillSchema.FieldRelationNumber, relationNumber);
        }

        public Bill SelectLastBill(int contactID)
        {
            IList list = this.SelectBillListByContact(contactID);
            if (list.Count == 0)
                return null;

            Bill last = null;
            foreach (Bill bill in list)
            {
                if (last == null)
                {
                    last = bill;
                    continue;
                }

                if (bill.Date > last.Date)
                {
                    last = bill;
                    continue;
                }

                if (bill.Date == last.Date)
                {
                    if (bill.ID > last.ID)
                    {
                        last = bill;
                        continue;
                    }
                }
            }

            return last;
        }

        public IList SelectBillListByContact(int contactID)
        {
            string contactIDFiled = BillSchema.TableName + "." + BillSchema.FieldContactID;
            string billTypeField = BillSchema.TableName + "." + BillSchema.FieldBillType;
            return this.SelectList(contactIDFiled, contactID, billTypeField, (int)this.BillType);
        }

        public EmailBillStuffList SelectEmailSummaryList()
        {
            EmailBillStuffList stuffList = new EmailBillStuffList();

            BillSummaryList summaryList = this.SelectSummaryList();
            foreach (BillSummary summary in summaryList)
            {
                string email = this.Database.CustomerAccessor.SelectContactEmail(summary.ContactKey);
                EmailBillStuff emailBillSummary = new EmailBillStuff(email, summary);
                stuffList.Add(emailBillSummary);
            }

            return stuffList;
        }

        public BillSummaryList SelectSummaryList()
        {
            BillSummaryList list = new BillSummaryList();
            DbCommand command = this.CommandSelectSummary();
            CommandFactory.WhereAnd(command, BillSchema.FieldBillType, (int)this.BillType);
            CommandHelper.ExecuteReader(command, ReadSummary, list);
            return list;
        }

        public BillSummaryList SelectSummaryListUnVoid()
        {
            BillSummaryList list = new BillSummaryList();
            DbCommand command = this.CommandSelectSummary();
            CommandFactory.WhereAnd(command, BillSchema.FieldBillType, (int)this.BillType);
            CommandFactory.WhereAnd(command, BillSchema.FieldVoid, false);
            CommandHelper.ExecuteReader(command, ReadSummary, list);
            return list;
        }

        public BillSummaryList SelectSummaryListByContact(int contactID)
        {
            BillSummaryList list = new BillSummaryList();
            DbCommand command = this.CommandSelectSummary();
            CommandFactory.WhereAnd(command, BillSchema.FieldBillType, (int)this.BillType);
            CommandFactory.WhereAnd(command, BillSchema.TableName + "." + BillSchema.FieldContactID, contactID);
            CommandHelper.ExecuteReader(command, ReadSummary, list);
            return list;
        }

        public BillSummaryList SelectSummaryListUnVoidByContact(int contactID)
        {
            BillSummaryList list = new BillSummaryList();
            DbCommand command = this.CommandSelectSummary();
            CommandFactory.WhereAnd(command, BillSchema.FieldBillType, (int)this.BillType);
            CommandFactory.WhereAnd(command, BillSchema.TableName + "." + BillSchema.FieldContactID, contactID);
            CommandFactory.WhereAnd(command, BillSchema.FieldVoid, false);
            CommandHelper.ExecuteReader(command, ReadSummary, list);
            return list;
        }

        public decimal SelectUnpaidBalanceByContact(int contactID)
        {
            DbCommand command = this.Database.Connection.CreateCommand();

            command.CommandText = string.Format("SELECT sum({0}-{1}) as UnpaidBalance FROM {2} INNER JOIN {3} ON {3}.{4} = {2}.{5}",
                BillSchema.FieldTotal, BillSchema.FieldAmountPaid,
                BillSchema.TableName, ContactSchema.TableName,
                ContactSchema.FieldID, BillSchema.FieldContactID);

            CommandFactory.WhereAnd(command, BillSchema.FieldBillType, (int)this.BillType);
            CommandFactory.WhereAnd(command, BillSchema.TableName + "." + BillSchema.FieldContactID, contactID);
            CommandFactory.WhereAnd(command, BillSchema.FieldVoid, false);

            object res = command.ExecuteScalar();
            Type type = res.GetType();

            return (null != res && type != typeof(DBNull)) ? (decimal)res : 0;
        }

        public void UpdatePaid(int id, decimal paid, DateTime paidDate, DbTransaction transaction)
        {
            DbCommand command = transaction.Connection.CreateCommand();
            command.CommandText = String.Format("UPDATE {0} SET [{1}] = @{1}, [{2}] = @{2}",
                this.TableName, BillSchema.FieldAmountPaid, BillSchema.FieldPaidDate);
            command.Transaction = transaction;

            CommandFactory.WhereAnd(command, BillSchema.FieldID, id);
            CommandFactory.AddCommandParameter(command, "@" + BillSchema.FieldAmountPaid, paid);
            CommandFactory.AddCommandParameter(command, "@" + BillSchema.FieldPaidDate, paidDate);

            CommandHelper.ExecuteCommand(command);
        }

        public void UpdateSalesTemplateID(int id)
        {
            this.UpdateTemplateID(this.Database.Connection, id, false);
        }

        public void UpdatePOTemplateID(int id)
        {
            this.UpdateTemplateID(this.Database.Connection, id, true);
        }

        public string GenerateNumber()
        {
            string lastNumber = this.SelectLastNumber();
            string baseNumber = String.IsNullOrEmpty(lastNumber) ? this.FirstNumber : lastNumber;

            if (!baseNumber.StartsWith(this.NumberPrefix))
                baseNumber = this.NumberPrefix + baseNumber;

            BillNumber newNumber = new BillNumber(baseNumber);
            while (this.NumberExisted(newNumber.String))
                newNumber.Increase();

            return newNumber.String;
        }

        public string GenerateNumber(string baseNumber)
        {
            string number = baseNumber;
            int i = 1;
            while (this.NumberExisted(number))
            {
                i++;
                number = baseNumber + i.ToString();
            }

            return number;
        }

        public Bill CreateBill()
        {
            Bill bill = this.NewBill();
            bill.Number = this.GenerateNumber();
            bill.FormTemplateID = this.GetDefaultTemplateID();
            bill.CompanyProfileID = this.Database.CompanyPreference.DefaultCompanyProfileID;
            this.TaxesPreference.Taxes.CopyTo(bill.Taxes);
            return bill;
        }

        public virtual ContactAccessor ContactAccessor
        {
            get { return this.Database.CustomerAccessor; }
        }

        public virtual TaxesPreference TaxesPreference
        {
            get { return this.Database.CompanyPreference.SaleTaxesPreference; }
            set
            {
                CompanyPreference company = this.Database.CompanyPreference;
                value.CopyTo(company.SaleTaxesPreference);
                this.Database.CompanyPreference = company;
            }
        }

        protected abstract int GetDefaultTemplateID();

        protected override void DeleteBefore(int id, DbTransaction transaction)
        {
            this.Database.LineItemAccessor.DeleteList(id, transaction);
        }

        protected override CommandFieldValueList BuildPairList(Entity entity)
        {
            Bill bill = (Bill)entity;
            int contactID = this.VerifyContact(bill);
            CommandFieldValueList list = new CommandFieldValueList(bill.ID);
            list.Add(BillSchema.FieldBillType, (int)bill.BillType);
            list.Add(BillSchema.FieldInvoiceType, (int)bill.InvoiceType);
            list.Add(BillSchema.FieldNumber, bill.Number);
            list.Add(BillSchema.FieldDate, bill.Date);
            list.Add(BillSchema.FieldContactID, contactID);
            list.Add(BillSchema.FieldBillToAddress1, bill.BillTo.Address1);
            list.Add(BillSchema.FieldBillToAddress2, bill.BillTo.Address2);
            list.Add(BillSchema.FieldBillToCity, bill.BillTo.City);
            list.Add(BillSchema.FieldBillToState, bill.BillTo.State);
            list.Add(BillSchema.FieldBillToZip, bill.BillTo.Zip);
            list.Add(BillSchema.FieldBillToCountry, bill.BillTo.Country);
            list.Add(BillSchema.FieldShipToFirstName, bill.ShipTo.FirstName);
            list.Add(BillSchema.FieldShipToLastName, bill.ShipTo.LastName);
            list.Add(BillSchema.FieldShipToCompany, bill.ShipTo.Company);
            list.Add(BillSchema.FieldShipToAddress1, bill.ShipTo.Address1);
            list.Add(BillSchema.FieldShipToAddress2, bill.ShipTo.Address2);
            list.Add(BillSchema.FieldShipToCity, bill.ShipTo.City);
            list.Add(BillSchema.FieldShipToState, bill.ShipTo.State);
            list.Add(BillSchema.FieldShipToZip, bill.ShipTo.Zip);
            list.Add(BillSchema.FieldShipToCountry, bill.ShipTo.Country);
            list.Add(BillSchema.FieldGeneral1, bill.General.Info1);
            list.Add(BillSchema.FieldGeneral2, bill.General.Info2);
            list.Add(BillSchema.FieldGeneral3, bill.General.Info3);
            list.Add(BillSchema.FieldGeneral4, bill.General.Info4);
            list.Add(BillSchema.FieldGeneral5, bill.General.Info5);
            list.Add(BillSchema.FieldGeneral6, bill.General.Info6);
            list.Add(BillSchema.FieldPaidDate, bill.PaidDate);
            list.Add(BillSchema.FieldVoid, bill.Void.Has);
            list.Add(BillSchema.FieldVoidDate, bill.Void.DateTime);
            list.Add(BillSchema.FieldLineItemDiscountType, (int)bill.LineItemDiscountType);
            list.Add(BillSchema.FieldDiscountPercent, bill.DiscountPercent);
            list.Add(BillSchema.FieldShippingCost, bill.ShippingCost);
            list.Add(BillSchema.FieldSubTotal, bill.SubTotal);
            list.Add(BillSchema.FieldTax1Enabled, bill.Tax1Enabled);
            list.Add(BillSchema.FieldTax1Name, bill.Tax1Name);
            list.Add(BillSchema.FieldTax1Percent, bill.Tax1Percent);
            list.Add(BillSchema.FieldTax1Amount, bill.Tax1Amount);
            list.Add(BillSchema.FieldTax1BaseTotal, bill.Tax1BaseTotal);
            list.Add(BillSchema.FieldTax2Enabled, bill.Tax2Enabled);
            list.Add(BillSchema.FieldTax2Name, bill.Tax2Name);
            list.Add(BillSchema.FieldTax2Percent, bill.Tax2Percent);
            list.Add(BillSchema.FieldTax2Amount, bill.Tax2Amount);
            list.Add(BillSchema.FieldTax2BaseTotal, bill.Tax2BaseTotal);
            list.Add(BillSchema.FieldAmountPaid, bill.AmountPaid);
            list.Add(BillSchema.FieldMessage, bill.Message);
            list.Add(BillSchema.FieldTotal, bill.Total);
            list.Add(BillSchema.FieldFormTemplateID, bill.FormTemplateID);
            list.Add(BillSchema.FieldRelationNumber, bill.RelationNumber);
            list.Add(BillSchema.FieldCompanyProfileID, bill.CompanyProfileID);
            return list;
        }