saleorderbl.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
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;

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

    public static void GetOrderbyID(int orderid, out DataTable dt, out string OrderNumber, out string customername, out decimal sum, out DateTime orderdate, out string salername, out string comment)
    {
        DataTable tbl = new DataTable();

        using (SqlConnection connect = new SqlConnection(PublicDefine.SQLConnectString))
        {
            connect.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connect;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select OrderNumber,CustomerName,Sum,OrderDate,SalerName,Comment from tbl_Orders where OrderID=" + orderid.ToString();
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            adapter.Fill(tbl);
            if (0 == tbl.Rows.Count)
            {
                throw new Exception("销售订单" + orderid.ToString() + "不存在！");
            }
            OrderNumber = (string)tbl.Rows[0]["OrderNumber"];
            customername = (string)tbl.Rows[0]["CustomerName"];
            sum = (decimal)tbl.Rows[0]["Sum"];
            orderdate = (DateTime) tbl.Rows[0]["OrderDate"];
            salername = tbl.Rows[0]["SalerName"].ToString();
            comment = tbl.Rows[0]["Comment"].ToString();

            tbl = new DataTable();
            cmd.CommandText = "Select ProductID,SalePrice,SaleQuantity from tbl_OrderDetail where OrderID =" + orderid.ToString();
            adapter.Fill(tbl);
            if (0 == tbl.Rows.Count)
            {
                throw new Exception("销售订单" + orderid.ToString() + "没有明细！");
            }
            // get the details 
            dt = GetEmptyList();
            string code, name, module, color;
            int id;
            decimal quantity,price;
            for (int i = 0; i < tbl.Rows.Count; i++)
            {
                id = (int)tbl.Rows[i]["ProductID"];
                price = (decimal)tbl.Rows[i]["SalePrice"];
                quantity = (decimal)tbl.Rows[i]["SaleQuantity"];
                SystemFunction.GetProductInfo(id, out code, out name, out module, out color);
                DataRow dr = AddNewRow(dt, id, code, name, module, color, price, quantity);
                dt.Rows.Add(dr);
            }
            connect.Close();
        }
        
    }

    public static DataTable GetEmptyList()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("ProductID", typeof(System.Int32)));
        dt.Columns.Add(new DataColumn("ProductCode", typeof(System.String)));
        dt.Columns.Add(new DataColumn("ProductName", typeof(System.String)));
        dt.Columns.Add(new DataColumn("ProductModule", typeof(System.String)));
        dt.Columns.Add(new DataColumn("ProductColor", typeof(System.String)));
        dt.Columns.Add(new DataColumn("Price", typeof(System.Decimal)));
        dt.Columns.Add(new DataColumn("Quantity", typeof(System.Decimal)));
        return dt;
    }

    public static DataRow AddNewRow(DataTable dt, int id, string code, string name, string module, string color, decimal price, decimal quantity)
    {
        DataRow dr = dt.NewRow();
        dr["ProductID"] = id;
        dr["ProductCode"] = code;
        dr["ProductName"] = name;
        dr["ProductModule"] = module;
        dr["ProductColor"] = color;
        dr["Price"] = price;
        dr["Quantity"] = quantity;
        return dr;
    }

    public static void SaveOrder(int orderid, DataTable dt, string OrderNumber, string customername, decimal sum,DateTime orderdate, string salername, string comment, out int neworderid)
    {
        using (SqlConnection connect = new SqlConnection(PublicDefine.SQLConnectString))
        {
            SqlParameter parameter;
            connect.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connect;
            SqlTransaction trans = connect.BeginTransaction();
            cmd.Transaction = trans;
            
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                if (0 < orderid)
                {
                    /****** delete old order   ******/
                    cmd.CommandText = "sp_Order_Delete";
                    cmd.Parameters.Clear();
                    //add parameters: @OrderNumber,@CustomerName,@Sum,@OrderDate,@SalerName,@Comment,@OrderID
                    parameter = new SqlParameter("@OrderID", orderid);
                    cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                }                 

                /****** add new order   ******/
                
                //first add order                 
                cmd.CommandText = "sp_Order_Add";
                cmd.Parameters.Clear();
                //add parameters: @OrderNumber,@CustomerName,@Sum,@OrderDate,@SalerName,@Comment,@OrderID
                parameter = new SqlParameter("@OrderNumber", OrderNumber);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@CustomerName", customername);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@Sum", sum);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@OrderDate", orderdate);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@SalerName", salername);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@Comment", comment);
                cmd.Parameters.Add(parameter);
                parameter = new SqlParameter("@OrderID", orderid);
                parameter.Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add(parameter);
                cmd.ExecuteNonQuery();
                neworderid = (int)cmd.Parameters["@OrderID"].Value;
                //second add details
                foreach (DataRow dr in dt.Rows)
                {
                    int ProductID = (int)dr["ProductID"];
                    decimal Price = (decimal)dr["Price"];
                    decimal Quantity = (decimal)dr["Quantity"];
                    cmd.CommandText = "sp_OrderDetail_Add";
                    cmd.Parameters.Clear();
                    //add parameters: @OrderID,@ProductID,@ProductPrice,@SalePrice,@SaleQuantity
                    parameter = new SqlParameter("@OrderID", neworderid);
                    cmd.Parameters.Add(parameter);
                    parameter = new SqlParameter("@ProductID", ProductID);
                    cmd.Parameters.Add(parameter);
                    parameter = new SqlParameter("@ProductPrice", Price);
                    cmd.Parameters.Add(parameter);
                    parameter = new SqlParameter("@SalePrice", Price);
                    cmd.Parameters.Add(parameter);
                    parameter = new SqlParameter("@SaleQuantity", Quantity);
                    cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }   
        }
    }


}