materialmaindaoimpl.cs

来自「一个超市管理系统,没有错误,非常好,里面什么都有!很使用,很有用」· CS 代码 · 共 148 行

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DaFanRongMIS.Model.Common;
using DaFanRongMIS.Model.OutStorage;

namespace DaFanRongMIS.Model.OutStorage
{
    class MaterialMainDAOImpl:MaterialMainDAO
    {
        string shopid = "";
        #region 生成出库主表的编号(流水号)
        public string CreateMaterialMainID()
        {
            
            string date = System.DateTime.Now.ToString("yyyyMMddhhmmss");//20071204083923
            date = shopid + date;
            return date;
        }
        #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());
            shopid = cmd.ExecuteScalar().ToString();
            return shopid;
        }
        #endregion

        #region 增加出库信息
        public string OutStorage(MaterialMainEntity main, MaterialDetailEntity[] 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 MaterialMain values(@id,@shopid,@operator,@businessTime,@businessType,@destination,@memo)";
                //定义链接
                cmd.Connection = ConnectionDataBase.getConOpen();
                cmd.CommandText = sqlmain;
                //开启事务
                tran = cmd.Connection.BeginTransaction("Transaction Of OutStorage");
                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("@operator", SqlDbType.VarChar));
                cmd.Parameters["@operator"].Value = main.OpeRator;
                cmd.Parameters.Add(new SqlParameter("@businessTime", SqlDbType.VarChar));
                cmd.Parameters["@businessTime"].Value = main.BusinessTime;
                cmd.Parameters.Add(new SqlParameter("@businessType", SqlDbType.VarChar));
                cmd.Parameters["@businessType"].Value = main.BusinessType;
                cmd.Parameters.Add(new SqlParameter("@destination", SqlDbType.VarChar));
                cmd.Parameters["@destination"].Value = main.Destination;
                cmd.Parameters.Add(new SqlParameter("@memo", SqlDbType.VarChar));
                cmd.Parameters["@memo"].Value = main.Memo;
                cmd.ExecuteNonQuery();
                foreach (Model.OutStorage.MaterialDetailEntity detail in details)
                {
                    string sqlitem = "insert into MaterialDetail values(@mainid,@materialid,@businessCount,@memberPrice)";
                    cmd.CommandText = sqlitem;
                    cmd.Parameters.Add(new SqlParameter("@mainid", SqlDbType.VarChar));
                    cmd.Parameters["@mainid"].Value = detail.MainID;
                    cmd.Parameters.Add(new SqlParameter("@materialid", SqlDbType.VarChar));
                    cmd.Parameters["@materialid"].Value = detail.MaterialID;
                    cmd.Parameters.Add(new SqlParameter("@businessCount", SqlDbType.Int));
                    cmd.Parameters["@businessCount"].Value = detail.BusinessCount;
                    cmd.Parameters.Add(new SqlParameter("@memberPrice", SqlDbType.Int));
                    cmd.Parameters["@memberPrice"].Value = detail.MemberPrice;

                    cmd.ExecuteNonQuery();
                    //清空参数
                    cmd.Parameters.Clear();
                    string sqla = "update Storage set BusinessCount=BusinessCount-" + detail.BusinessCount + " where MaterialID='" + detail.MaterialID + "'";
                    cmd.CommandText = sqla;
                    cmd.ExecuteNonQuery();
                }


                result = "ok";
                //提交事务
                tran.Commit();
            }
            catch (Exception ee)
            {
                result = ee.Message;
                //会滚事务
                tran.Rollback();
            }

            return result;
        }//end mothed AddSellProduct
        #endregion

        #region　查询指定材料编号的数量

        public string count(string n)
        {
            SqlCommand cmd = new SqlCommand("select BusinessCount from Storage where MaterialID='"+n+"'",ConnectionDataBase.getConOpen());
            string num = cmd.ExecuteScalar().ToString();
            return num;
        }
        #endregion

        #region　查询库存表中是否有指定材料
        public int StorageYorN(string n)
        {
            int j = 0;
            SqlCommand cmd = new SqlCommand("select count(*) from Storage where MaterialID='" + n + "'", ConnectionDataBase.getConOpen());
            j=Convert.ToInt32(cmd.ExecuteScalar());
            return j;
        }        
        #endregion

    }
}