commodityleavesdao.cs

小型ERP系统源码,修改版本

using System;
using System.Data;
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 DLOA.DALFactory;
using System.Text;
using System.Data.SqlClient;
using DLOA.Model;
using System.Collections;


namespace DLOA.DAO
{
    /// <summary>
    /// CommodityLeavesDAO 的摘要说明
    /// </summary>
    public class CommodityLeavesDAO
    {
        public CommodityLeavesDAO()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }


        #region  成员方法

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "CommodityLeaves");
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CommodityLeaves");
            strSql.Append(" where id= @id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
				};
            parameters[0].Value = id;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条物资出库数据并且修改物资入库信息
        /// <param name="model">物资出库实体对象</param>
        /// </summary>
        public void Add(DLOA.Model.CommodityLeaves model)
        {
            System.Collections.Hashtable ht = new System.Collections.Hashtable();
            StringBuilder strSql = new StringBuilder();

            //减少库存数量
            strSql.Append(" update CommodityGoes set GoesQuantity = GoesQuantity - @GoesQuantity where id = @id");
            SqlParameter[] goesParameters = {
					new SqlParameter("@GoesQuantity", SqlDbType.VarChar,50),
                    new SqlParameter("@id",SqlDbType.Int)
            };
            goesParameters[0].Value = model.leavesQuantity;
            goesParameters[1].Value = model.GoesId;
            ht.Add(strSql, goesParameters);

            //将数据保存到出库表中
            strSql = new StringBuilder();
            strSql.Append("insert into CommodityLeaves(");
            strSql.Append("goesId,leavesname,leavesModel,leavesQuantity,leavesParameter,leavesUnit,departmentid,signature,datex,opinion)");
            strSql.Append(" values (");
            strSql.Append("@goesId,@leavesname,@leavesModel,@leavesQuantity,@leavesParameter,@leavesUnit,@departmentid,@signature,@datex,@opinion)");
            SqlParameter[] leavesParameters = {
					new SqlParameter("@goesId", SqlDbType.Int,4),
					new SqlParameter("@leavesname", SqlDbType.VarChar,50),
					new SqlParameter("@leavesModel", SqlDbType.VarChar,50),
					new SqlParameter("@leavesQuantity", SqlDbType.Int,4),
					new SqlParameter("@leavesParameter", SqlDbType.VarChar,50),
					new SqlParameter("@leavesUnit", SqlDbType.Money,8),
					new SqlParameter("@departmentid", SqlDbType.Int,4),
					new SqlParameter("@signature", SqlDbType.VarChar,50),
					new SqlParameter("@datex", SqlDbType.DateTime),
					new SqlParameter("@opinion", SqlDbType.VarChar,500)};
            leavesParameters[0].Value = model.GoesId;
            leavesParameters[1].Value = model.leavesname;
            leavesParameters[2].Value = model.leavesModel;
            leavesParameters[3].Value = model.leavesQuantity;
            leavesParameters[4].Value = model.leavesParameter;
            leavesParameters[5].Value = model.leavesUnit;
            leavesParameters[6].Value = model.departmentid;
            leavesParameters[7].Value = model.signature;
            leavesParameters[8].Value = model.datex;
            leavesParameters[9].Value = model.opinion;
            ht.Add(strSql, leavesParameters);
            DbHelperSQL.ExecuteSqlTran(ht);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(DLOA.Model.CommodityLeaves model)
        {
            Hashtable ht = new Hashtable();
            StringBuilder strSql = new StringBuilder();
            //修改仓库库存
            //将现有的库存数量加上原有出库数量减去今次出货数量
            strSql.Append(" update CommodityGoes set GoesQuantity = GoesquanTity + ");
            strSql.Append("(select leavesQuantity from CommodityLeaves where id = @leaveId ) - ");
            strSql.Append(" @leavesQuantity where id = (select goesId from CommodityLeaves where id = @leaveId ) ");
            SqlParameter[] goesParameters = {
					new SqlParameter("@leaveId", SqlDbType.Int),
                    new SqlParameter("@leavesQuantity",SqlDbType.Int)
            };
            goesParameters[0].Value = model.id;
            goesParameters[1].Value = model.leavesQuantity;
            ht.Add(strSql, goesParameters);
            
            //修改出库库存信息
            strSql = new StringBuilder();
            strSql.Append("update CommodityLeaves set ");
            strSql.Append("leavesname=@leavesname,");
            strSql.Append("leavesModel=@leavesModel,");
            strSql.Append("leavesQuantity=@leavesQuantity,");
            strSql.Append("leavesParameter=@leavesParameter,");
            strSql.Append("leavesUnit=@leavesUnit,");
            strSql.Append("departmentid=@departmentid,");
            strSql.Append("signature=@signature,");
            strSql.Append("datex=@datex,");
            strSql.Append("opinion=@opinion");
            strSql.Append(" where id=@id");
            SqlParameter[] leavesParameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@leavesname", SqlDbType.VarChar,50),
					new SqlParameter("@leavesModel", SqlDbType.VarChar,50),
					new SqlParameter("@leavesQuantity", SqlDbType.Int,4),
					new SqlParameter("@leavesParameter", SqlDbType.VarChar,50),
					new SqlParameter("@leavesUnit", SqlDbType.Money,8),
					new SqlParameter("@departmentid", SqlDbType.Int,4),
					new SqlParameter("@signature", SqlDbType.VarChar,50),
					new SqlParameter("@datex", SqlDbType.DateTime),
					new SqlParameter("@opinion", SqlDbType.VarChar,500)};
            leavesParameters[0].Value = model.id;
            leavesParameters[1].Value = model.leavesname;
            leavesParameters[2].Value = model.leavesModel;
            leavesParameters[3].Value = model.leavesQuantity;
            leavesParameters[4].Value = model.leavesParameter;
            leavesParameters[5].Value = model.leavesUnit;
            leavesParameters[6].Value = model.departmentid;
            leavesParameters[7].Value = model.signature;
            leavesParameters[8].Value = model.datex;
            leavesParameters[9].Value = model.opinion;
            ht.Add(strSql, leavesParameters);

            DbHelperSQL.ExecuteSqlTran(ht);
        }

        /// <summary>
        /// 删除一条数据
        /// <param name="leavesId">出库编号</param>
        /// <param name="goesId">库存编号</param>
        /// </summary>
        public void Delete(int leavesId, int goesId)
        {
            Hashtable ht = new Hashtable();
            StringBuilder strSql = new StringBuilder();
            //修改库存信息
            strSql.Append(" update CommodityGoes set GoesQuantity = GoesquanTity + ");
            strSql.Append(" (select leavesQuantity from CommodityLeaves where id = @leavesId)");
            strSql.Append(" where id = @goesId  ");
            SqlParameter[] goesParameters = { 
                new SqlParameter("@leavesId",SqlDbType.Int),
                new SqlParameter("@goesId",SqlDbType.Int)
            };
            goesParameters[0].Value = leavesId;
            goesParameters[1].Value = goesId;
            ht.Add(strSql, goesParameters);

            //删除出库记录
            strSql = new StringBuilder();
            strSql.Append("delete CommodityLeaves ");
            strSql.Append(" where id=@id ");
            SqlParameter[] leavesParameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
				};
            leavesParameters[0].Value = leavesId;
            ht.Add(strSql, leavesParameters);
            //执行
            DbHelperSQL.ExecuteSqlTran(ht);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DLOA.Model.CommodityLeaves GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,goesId,leavesname,leavesModel,leavesQuantity,leavesParameter,leavesUnit,departmentid,signature,datex,opinion from CommodityLeaves ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            DLOA.Model.CommodityLeaves model = new DLOA.Model.CommodityLeaves();
            DataTable dt = DbHelperSQL.QueryByParameter(strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(dt.Rows[0]["id"].ToString());
                }
                if (dt.Rows[0]["goesId"].ToString() != "")
                {
                    model.GoesId = Convert.ToInt32(dt.Rows[0]["goesId"].ToString());
                }
                model.leavesname = dt.Rows[0]["leavesname"].ToString();
                model.leavesModel = dt.Rows[0]["leavesModel"].ToString();
                if (dt.Rows[0]["leavesQuantity"].ToString() != "")
                {
                    model.leavesQuantity = int.Parse(dt.Rows[0]["leavesQuantity"].ToString());
                }
                model.leavesParameter = dt.Rows[0]["leavesParameter"].ToString();
                if (dt.Rows[0]["leavesUnit"].ToString() != "")
                {
                    model.leavesUnit = decimal.Parse(dt.Rows[0]["leavesUnit"].ToString());
                }
                if (dt.Rows[0]["departmentid"].ToString() != "")
                {
                    model.departmentid = int.Parse(dt.Rows[0]["departmentid"].ToString());
                }
                model.signature = dt.Rows[0]["signature"].ToString();
                if (dt.Rows[0]["datex"].ToString() != "")
                {
                    model.datex = DateTime.Parse(dt.Rows[0]["datex"].ToString());
                }
                model.opinion = dt.Rows[0]["opinion"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT CommodityLeaves.id,");
            strSql.Append(" CommodityLeaves.leavesname,  CommodityLeaves.leavesModel,");
            strSql.Append(" CommodityLeaves.leavesQuantity,  CommodityLeaves.leavesParameter, ");
            strSql.Append(" CommodityLeaves.leavesUnit,  CommodityLeaves.departmentid, Department.name AS DepartmentName,");
            strSql.Append(" CommodityLeaves.signature,  CommodityLeaves.datex,CommodityLeaves.GoesId");
            strSql.Append(" FROM  Department INNER JOIN");
            strSql.Append(" CommodityLeaves ON  Department.id =  CommodityLeaves.departmentid ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.QueryBySql(strSql.ToString());
        }

       
        #endregion  成员方法

    }
}