sellmaindaoimpl.cs

一个超市管理系统,没有错误,非常好,里面什么都有!很使用,很有用

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DaFanRongMIS.Model.Common;

namespace DaFanRongMIS.Model.Sell
{
    class SellMainDAOImpl:SellMainDAO
    {
        public static DataTable dt = new DataTable();

        #region 生成销售主表的编号(流水号)
        public string CreateMainSellID()
        {
            string date = System.DateTime.Now.ToString("yyyyMMddhhmmss");//20071204083923
            date = "ZZ" + date;
            return date;
        }
        #endregion

        #region 根据拼音码填充listbox
        public void FillListBoxByPin(string str, System.Windows.Forms.ListBox lb)
        {
            DataRow[] dr = dt.Select("PinName like '" + str + "%'");
            lb.Items.Clear();
            for (int i = 0; i < dr.Length; i++)
            {
                lb.Items.Add("【" + Convert.ToString(i + 1) + "】(" + dr[i]["ID"] + ")" + dr[i]["Name"].ToString());
            }
        }
        #endregion

        #region 根据编号查询产品信息
        public DataRow GetDataByID(string str)
        {
            DataRow DrProduct = null;
            try
            {
                DataRow[] dr = dt.Select("ID like '"+'P'+  str + "%'");
                for (int i = 0; i < dr.Length; i++)
                {
                    DrProduct = dr[i];
                }
            }
            catch (Exception ee)
            {
                DrProduct = null;
            }
            return DrProduct;
        }
        #endregion

        #region 填充商品信息
        public void FillData()
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            DataSet ds = new DataSet();
            sda.SelectCommand = new SqlCommand("select ID,Name,PinName,Price,UnitID,CateGoryID,Memo from Product", DaFanRongMIS.Model.Common.ConnectionDataBase.getConOpen());
            sda.Fill(dt);
            dt.PrimaryKey = new DataColumn[] { dt.Columns["ID"] };
            sda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        }
        #endregion

        #region  查询商品价格
        public decimal GetProductPrice(string ProductID)
        {

            try
            {
                DataRow dr = dt.Rows.Find(ProductID);
                return Convert.ToDecimal(dr["Price"]);
            }
            catch (Exception ee)
            {
                return 0;
            }
        }
        #endregion

        #region 增加销售主表信息
        //public string MainSellAdd(SellMainEntity main)
        //{
        //    string result = "";
        //    try
        //    {
        //        string sql = "insert into SellMain values(@id,@shopid,@cashid,@casher,@selltime,@memo)";
        //        SqlCommand cmd = new SqlCommand(sql, DaFanRongMIS.Model.Common.ConnectDatabase.GetSqlConnection());
        //        cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar));
        //        cmd.Parameters["@id"].Value = main.ID;
        //        cmd.Parameters.Add(new SqlParameter("@shopid", SqlDbType.VarChar));
        //        cmd.Parameters["@shopid"].Value = main.ShopID;
        //        cmd.Parameters.Add(new SqlParameter("@cashid", SqlDbType.VarChar));
        //        cmd.Parameters["@cashid"].Value = main.CashID;
        //        cmd.Parameters.Add(new SqlParameter("@casher", SqlDbType.VarChar));
        //        cmd.Parameters["@casher"].Value = main.Casher;
        //        cmd.Parameters.Add(new SqlParameter("@selltime", SqlDbType.VarChar));
        //        cmd.Parameters["@selltime"].Value = main.SellTime;
        //        cmd.Parameters.Add(new SqlParameter("@memo", SqlDbType.VarChar));
        //        cmd.Parameters["@memo"].Value = main.Memo;
        //        cmd.ExecuteNonQuery();
        //        result = "ok";
        //    }
        //    catch (Exception ee)
        //    {
        //        result = ee.Message;
        //    }
        //    finally
        //    { 
        //        DaFanRongMIS.Model.Common.ConnectDatabase.CloseConnection();
        //    }
        //    return result;
        //}
        #endregion

        #region 增加销售明细表信息
        //public string DetailSellAdd(SellDetailEntity detail)
        //{
        //    string result = "";
        //    try
        //    {
        //        string sql = "insert into SellDetail values(@mainid,@proid,@count,@discount,@memberprice)";
        //        SqlCommand cmd = new SqlCommand(sql, DaFanRongMIS.Model.Common.ConnectDatabase.GetSqlConnection());
        //        cmd.Parameters.Add(new SqlParameter("@mainid", SqlDbType.VarChar));
        //        cmd.Parameters["@mainid"].Value = detail.MainID;
        //        cmd.Parameters.Add(new SqlParameter("@proid", SqlDbType.VarChar));
        //        cmd.Parameters["@proid"].Value = detail.ProductID;
        //        cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int));
        //        cmd.Parameters["@count"].Value = detail.SoldCount;
        //        cmd.Parameters.Add(new SqlParameter("@discount", SqlDbType.Float));
        //        cmd.Parameters["@discount"].Value = detail.Diccount;
        //        cmd.Parameters.Add(new SqlParameter("@memberprice", SqlDbType.Decimal));
        //        cmd.Parameters["@memberprice"].Value = detail.MemberPrice;
        //        cmd.ExecuteNonQuery();
        //        result = "ok";
        //    }
        //    catch (Exception ee)
        //    {
        //        result = ee.Message;
        //    }
        //    finally
        //    {
        //        DaFanRongMIS.Model.Common.ConnectDatabase.CloseConnection();
        //    }
        //    return result;
        //}
        #endregion

        #region 增加销售信息
        public string SellProduct(SellMainEntity main, SellDetailEntity[] details)
        {
            //定义返回值
            string result = "";
            //定义命令对象
            SqlCommand cmd = new SqlCommand();
            //定义事务
            SqlTransaction tran = null;
            #region 无事务处理，优点可以减低力度和代码的可读性，缺点处理事务会滚之后的代码比较繁琐
            //int j = 0;
            //if (MainSellAdd(main) == "ok")
            //{
            //    for (int i = 0; i < detail.Length; i++)
            //    {
            //        if (DetailSellAdd(detail[i]) == "ok")
            //        {
            //            j++;
            //        }
            //    }
            //}
            //else
            //{
            //    result = "no";
            //}
            //if (j == detail.Length)
            //{
            //    result = "ok";
            //}
            #endregion
            try
            {
                //构建主表SQL语句
                string sqlmain = "insert into SellMain values(@id,@shopid,@cashid,@casher,@selltime,@memo)";
                //定义链接
                cmd.Connection = ConnectionDataBase.getConOpen();
                cmd.CommandText = sqlmain;
                //开启事务
                tran = cmd.Connection.BeginTransaction("Transaction Of SellProduct");
                cmd.Transaction = tran;
                cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar));
                cmd.Parameters["@id"].Value = main.Id;
                cmd.Parameters.Add(new SqlParameter("@shopid", SqlDbType.VarChar));
                cmd.Parameters["@shopid"].Value = main.Shopid;
                cmd.Parameters.Add(new SqlParameter("@cashid", SqlDbType.VarChar));
                cmd.Parameters["@cashid"].Value = main.Cashid;
                cmd.Parameters.Add(new SqlParameter("@casher", SqlDbType.VarChar));
                cmd.Parameters["@casher"].Value = main.Casher;
                cmd.Parameters.Add(new SqlParameter("@selltime", SqlDbType.VarChar));
                cmd.Parameters["@selltime"].Value = main.Selltime;
                cmd.Parameters.Add(new SqlParameter("@memo", SqlDbType.VarChar));
                cmd.Parameters["@memo"].Value = main.Memo;
                cmd.ExecuteNonQuery();
                foreach (Model.Sell.SellDetailEntity detail in details)
                {
                    string sqlitem = "insert into SellDetail values(@mainid,@productid,@soldcount)";
                    cmd.CommandText = sqlitem;
                    cmd.Parameters.Add(new SqlParameter("@mainid", SqlDbType.VarChar));
                    cmd.Parameters["@mainid"].Value = detail.MainID;
                    cmd.Parameters.Add(new SqlParameter("@productid", SqlDbType.VarChar));
                    cmd.Parameters["@productid"].Value = detail.ProductID;
                    cmd.Parameters.Add(new SqlParameter("@soldcount", SqlDbType.Int));
                    cmd.Parameters["@soldcount"].Value = detail.SoldCount;
                    
                    cmd.ExecuteNonQuery();
                    //清空参数
                    cmd.Parameters.Clear();
                }
                result = "ok";
                //提交事务
                tran.Commit();
            }
            catch (Exception ee)
            {
                result = ee.Message;
                //会滚事务
                tran.Rollback();
            }

            return result;
        }//end mothed AddSellProduct
        #endregion
        #region  根据用户ID获得店铺编号
        public string GetShopID(string userid)
        {
           
            SqlCommand cmd = new SqlCommand("select Shop.ID ,Shop.Name from Shop where id in (select id from CashRegister  where shopid in  (select CashRegisterid from login where userid= userid))",ConnectionDataBase.getConOpen());
            string shopid=cmd.ExecuteScalar().ToString();
            return shopid;
        }
        #endregion

        #region  根据用户ID获得收银机号
        public string CashRegisterid(string userid)
        {

            SqlCommand cmd = new SqlCommand("select id from CashRegister  where id in(select CashRegisterid from login where userid=userid)", ConnectionDataBase.getConOpen());
            string CashRegisterid = cmd.ExecuteScalar().ToString();
            return CashRegisterid;
        }
        #endregion

        #region 增加
        public string AddSellMain(SellMainEntity sellMail)
        {

            string str = "";

            //声明并设置SqlCommand命令

            SqlCommand cmdadd = new SqlCommand();
            cmdadd.Connection = ConnectionDataBase.getConOpen();
            cmdadd.CommandType = CommandType.StoredProcedure;
            cmdadd.CommandText = "Add_SellMain";

            //结束声明和设置SqlCommand命令


            //参数赋值

            SqlParameter IDAdd = new SqlParameter("@ID", SqlDbType.VarChar, 20);
            IDAdd.Value = sellMail.Id;
            cmdadd.Parameters.Add(IDAdd);

            SqlParameter ShopIDAdd = new SqlParameter("@ShopID", SqlDbType.VarChar, 2);
            ShopIDAdd.Value = sellMail.Shopid;
            cmdadd.Parameters.Add(ShopIDAdd);

            SqlParameter CashIDAdd = new SqlParameter("@CashID", SqlDbType.VarChar, 2);
            CashIDAdd.Value = sellMail.Cashid;
            cmdadd.Parameters.Add(CashIDAdd);

            SqlParameter CasherAdd = new SqlParameter("@Casher", SqlDbType.VarChar, 20);
            CasherAdd.Value = sellMail.Casher;
            cmdadd.Parameters.Add(CasherAdd);

            SqlParameter SellTimeAdd = new SqlParameter("@SellTime", SqlDbType.VarChar, 60);
            SellTimeAdd.Value = sellMail.Selltime;
            cmdadd.Parameters.Add(SellTimeAdd);

            SqlParameter MemoAdd = new SqlParameter("@Memo", SqlDbType.VarChar, 2000);
            MemoAdd.Value = sellMail.Id;
            cmdadd.Parameters.Add(MemoAdd);


            //结束参数赋值

            //打开库,执行,关闭库
            try
            {

                cmdadd.Connection = Common.ConnectionDataBase.getConOpen();

                cmdadd.ExecuteNonQuery();
                str = "OK";

            }
            catch (Exception ee)
            {
                str = ee.Message;
            }
            finally
            {
                Common.ConnectionDataBase.getConClose();
            }
            //结束打开库,执行,关闭库
            return str;
        }//end mothed AddSellDetail
        #endregion

        #region 查找
        public DataTable SelectSellMain()
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "Proc_SelMaxID_ShellMain";
            cmd.Connection = Common.ConnectionDataBase.getConOpen();
            da.SelectCommand = cmd;
            da.Fill(ds);
            dt = ds.Tables[0];
            return dt;
        }
        #endregion
        
    }
}