systembl.cs

這是一個由CSharp寫成的小型ERP系統

using System;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Linq.Dynamic;

/// <summary>
/// Summary description for SystemBL
/// </summary>
public class SystemBL
{
	public SystemBL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static int CompareProductType(DAO.ProductTypeInfo x, DAO.ProductTypeInfo y)
    {
        return int.Parse(x.ProductTypeCode) - int.Parse(y.ProductTypeCode);
    }
    
    static List<DAO.ProductTypeInfo> ProductTypList=null; // all product type list in product select dropdownlist

    static List<DAO.ProductTypeInfo> ProductCategories=null; // the category of product type

    public static List<DAO.ProductTypeInfo> SelectProductType(string category)
    {
        if (string.Empty != category)
        {
            int len = category.Length;
            foreach (DAO.ProductTypeInfo producttype in ProductTypList)
            {
                if (category != producttype.ProductTypeCode.Substring(0, len))
                {
                    ProductTypList.Remove(producttype);
                }
            }
        }        
        return ProductTypList;
    }

    public static List<DAO.ProductViewInfo> GetProductsView(int type,string orderby)
    {
        string orderexpress;
        if (null == orderby || string.Empty == orderby)
        {
            orderexpress = "ProductCode";
        }
        else
        {
            orderexpress = orderby;
        }

        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            List<DAO.ProductViewInfo> products;
            if (type > 0)
            {
                var result = from p in db.ProductViewInfos
                           where p.ProductTypeID == type
                             orderby (orderexpress)
                           select p;
                products = result.ToList<DAO.ProductViewInfo>();
            }
            else
            {
                var query = db.ProductViewInfos
                            .OrderBy(orderexpress);

                products = query.ToList<DAO.ProductViewInfo>();
            }
            return products;
        }
    }

    public static List<DAO.ProductViewInfo> GetProductsView(string productcode, string orderby)
    {
        string orderexpress;
        if (null == orderby || string.Empty == orderby)
        {
            orderexpress = "ProductCode";
        }
        else
        {
            orderexpress = orderby;
        }

        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            List<DAO.ProductViewInfo> products;
            if (productcode.Trim().Length > 0)
            {
                var result = from p in db.ProductViewInfos
                             where p.ProductCode.Contains(productcode.Trim())
                             orderby (orderexpress)
                             select p;
                products = result.ToList<DAO.ProductViewInfo>();
            }
            else
            {
                var query = db.ProductViewInfos
                            .OrderBy(orderexpress);

                products = query.ToList<DAO.ProductViewInfo>();
            }
            return products;
        }
    }

    public static List<DAO.ProductTypeInfo> GetProductCategories()
    {
        if (null == ProductCategories)
        {
            ProductCategories = new List<DAO.ProductTypeInfo>();
            using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
            {
                foreach (DAO.ProductTypeInfo type in db.ProductTypeInfos)
                {
                    if (2 == type.ProductTypeCode.Trim().Length)
                    {
                        ProductCategories.Add(type);
                    }
                }
                ProductCategories.Sort(CompareProductType);    
            }
        }
        return ProductCategories;
    }

    public static List<DAO.ProductTypeInfo> GetAllProductTypes()
    {
        List<DAO.ProductTypeInfo> list = GetProductTypes(string.Empty).ToList<DAO.ProductTypeInfo>();
        DAO.ProductTypeInfo alltype = new DAO.ProductTypeInfo();
        alltype.ProductTypeID = -1;
        alltype.ProductTypeCode = "00";
        alltype.ProductTypeName = "全部产品";
        list.Add(alltype);
        list.Sort(CompareProductType);
        return list;
    }

    public static List<DAO.ProductTypeInfo> GetProductTypes(string category)
    {
        if (null == ProductTypList)
        {
            ProductTypList = new List<DAO.ProductTypeInfo>();
            using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
            {
                foreach (DAO.ProductTypeInfo type in db.ProductTypeInfos)
                {
                    if (4 == type.ProductTypeCode.Trim().Length)
                    {
                        ProductTypList.Add(type);
                    }
                }
            }            
        }
        //filter
        List<DAO.ProductTypeInfo> list;
        if (string.Empty == category)
        {
            list = ProductTypList.ToList<DAO.ProductTypeInfo>();
        }
        else
        {
            list = new List<DAO.ProductTypeInfo>();
            int len=category.Trim().Length;
            foreach (DAO.ProductTypeInfo type in ProductTypList)
            {
                if (category.Trim() == type.ProductTypeCode.Substring(0, len))
                {
                    list.Add(type);
                }
            }
        }
        //sort
        list.Sort(CompareProductType);
        return list;
    }

    public static void GetProductInfo(string productcode, out int id, out string name, out string module, out string color)
    {
        id = -1;
        name = "";
        module = "";
        color = "";

        //refactory to LINQ
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            DAO.ProductInfo product = db.ProductInfos.Single(p => p.ProductCode == productcode);
            // if find the product, return the result
            if (null != product)
            {
                id = product.ProductID;
                name = product.ProductName;
                module = product.ProductModule;
                color = product.ProductColor;
            }
        }

        //using (SqlConnection connect = new SqlConnection(PublicDefine.SQLConnectString))
        //{
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.Connection = connect;
        //    connect.Open();
        //    cmd.CommandText = "select ProductID,ProductName,ProductModule,ProductColor from tbl_Products where ProductCode = '" + productcode + "'";
        //    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //    DataTable tbl = new DataTable();
        //    adapter.Fill(tbl);
        //    if (tbl.Rows.Count > 0)
        //    {
        //        id = (int)tbl.Rows[0][0];
        //        name = (string)tbl.Rows[0][1];
        //        module = (string)tbl.Rows[0][2];
        //        color = (string)tbl.Rows[0][3];
        //    }
        //}
    }

    public static void GetProductInfo(int id, out string productcode, out string name, out string module, out string color)
    {
        productcode = "";
        name = "";
        module = "";
        color = "";

        //refactory to LINQ
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            DAO.ProductInfo product = db.ProductInfos.Single(p => p.ProductID == id);
            // if find the product, return the result
            if (null != product)
            {
                productcode = product.ProductCode;
                name = product.ProductName;
                module = product.ProductModule;
                color = product.ProductColor;
            }
        }

        //using (SqlConnection connect = new SqlConnection(PublicDefine.SQLConnectString))
        //{
        //    SqlCommand cmd = new SqlCommand();
        //    cmd.Connection = connect;
        //    connect.Open();
        //    cmd.CommandText = "select ProductCode,ProductName,ProductModule,ProductColor from tbl_Products where ProductID = " + id.ToString() + "";
        //    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        //    DataTable tbl = new DataTable();
        //    adapter.Fill(tbl);
        //    if (tbl.Rows.Count > 0)
        //    {
        //        productcode = (string)tbl.Rows[0][0];
        //        name = (string)tbl.Rows[0][1];
        //        module = (string)tbl.Rows[0][2];
        //        color = (string)tbl.Rows[0][3];
        //    }
        //}
    }

    public static DataTable ReplaceRow(DataTable dt, DataRow dr, int rowidx)
    {
        if (rowidx > dt.Rows.Count - 1)
        {
            throw new Exception("rowidx > dt.Rows.Count -1");
        }
        dt.Rows[rowidx].ItemArray = dr.ItemArray;
        return dt;
    }

    public static void AddNewProduct(DAO.ProductInfo newproduct)
    {
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            db.ProductInfos.InsertOnSubmit(newproduct);
            db.SubmitChanges();
        }
    }

    public static void UpdateProduct(DAO.ProductInfo product)
    {
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            DAO.ProductInfo changedproduct = (from p in db.ProductInfos
                                             where p.ProductID == product.ProductID
                                              select p).First<DAO.ProductInfo>();
            changedproduct.ProductCode = product.ProductCode;
            changedproduct.ProductColor = product.ProductColor;
            changedproduct.ProductModule = product.ProductModule;
            changedproduct.ProductName = product.ProductName;
            changedproduct.ProductPrice = product.ProductPrice;
            changedproduct.ProductTypeID = product.ProductTypeID;
            db.SubmitChanges();
        }
    }

    public static void DeleteProduct(int productid)
    {
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            DAO.ProductInfo deleteproduct = (from p in db.ProductInfos
                                             where p.ProductID == productid
                                              select p).First<DAO.ProductInfo>();
            db.ProductInfos.DeleteOnSubmit(deleteproduct);
            db.SubmitChanges();
        }
    }

    public static Hashtable CheckForAddNew(DAO.ProductInfo newproduct)
    {
        Hashtable errors = new Hashtable();
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            var products = from p in db.ProductInfos
                           where p.ProductName == newproduct.ProductName && p.ProductColor == newproduct.ProductColor
                           select p;

            if (products.ToList<DAO.ProductInfo>().Count > 0)
            {
                errors.Add(SystemError_DuplicatedProductNameOrColor, SystemError_DuplicatedProductNameOrColor);
            }

            var samecodeproducts = from p in db.ProductInfos
                                   where p.ProductCode == newproduct.ProductCode
                                   select p;
            if (samecodeproducts.ToList<DAO.ProductInfo>().Count > 0)
            {
                errors.Add(SystemError_DuplicatedProductCode, SystemError_DuplicatedProductCode);
            }
        }
        return errors;
    }
    
    public static Hashtable CheckForUpdate(DAO.ProductInfo product)
    {
        Hashtable errors = new Hashtable();
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            var products = from p in db.ProductInfos
                           where (p.ProductName == product.ProductName && p.ProductColor == product.ProductColor && (p.ProductID != product.ProductID))
                           select p;

            if (products.ToList<DAO.ProductInfo>().Count > 0)
            {
                errors.Add(SystemError_DuplicatedProductNameOrColor, SystemError_DuplicatedProductNameOrColor);
            }

            var samecodeproducts = from p in db.ProductInfos
                                   where p.ProductCode == product.ProductCode && p.ProductID != product.ProductID
                                   select p;
            if (samecodeproducts.ToList<DAO.ProductInfo>().Count > 0)
            {
                errors.Add(SystemError_DuplicatedProductCode, SystemError_DuplicatedProductCode);
            }
        }
        return errors;
    }

    public static string GetProductTypeCode(int producttypeid)
    {
        using (DAO.SystemDataDataContext db = new DAO.SystemDataDataContext(PublicDefine.SQLConnectString))
        {
            var producttypes = from p in db.ProductTypeInfos
                           where p.ProductTypeID == producttypeid
                           select p;
            return producttypes.First<DAO.ProductTypeInfo>().ProductTypeCode;
        }
    }

    public const string SystemError_DuplicatedProductNameOrColor = "SystemError_DuplicatedProductNameOrColor";
    public const string SystemError_DuplicatedProductCode = "SystemError_DuplicatedProductCode"; 
}