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

namespace DLOA.DAO
{
    /// <summary>
    /// CommodityBackDAO 的摘要说明
    /// </summary>
    public class CommodityBackDAO
    {
        public CommodityBackDAO()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        
		#region  成员方法
		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from Commodityback");
			strSql.Append(" where id= @id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
				};
			parameters[0].Value = id;
			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public void Add(DLOA.Model.Commodityback model,int leavesId)
		{
            Hashtable ht = new 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.backQuantity;
            goesParameters[1].Value = model.GoesId;
            ht.Add(strSql, goesParameters);

            //减掉出货数量
            strSql = new StringBuilder();
            strSql.Append(" update CommodityLeaves set leavesQuantity = leavesQuantity - @leavesQuantity where id = @leavesId");
            SqlParameter[] leavesParameters = {
                new SqlParameter("@leavesQuantity",SqlDbType.Int),
                new SqlParameter("@leavesId",SqlDbType.Int)
            };
            leavesParameters[0].Value = model.backQuantity;
            leavesParameters[1].Value = leavesId;
            ht.Add(strSql, leavesParameters);
            
            //向物资退库表中保存记录
            strSql = new StringBuilder();
			strSql.Append("insert into Commodityback(");
			strSql.Append("goesId,backsname,backModel,backQuantity,backParameter,backUnit,departmentid,signature,datex,backOpinion)");
			strSql.Append(" values (");
			strSql.Append("@goesId,@backsname,@backModel,@backQuantity,@backParameter,@backUnit,@departmentid,@signature,@datex,@backOpinion)");
			SqlParameter[] backParameters = {
					new SqlParameter("@goesId", SqlDbType.Int,4),
					new SqlParameter("@backsname", SqlDbType.VarChar,50),
					new SqlParameter("@backModel", SqlDbType.VarChar,50),
					new SqlParameter("@backQuantity", SqlDbType.Int,4),
					new SqlParameter("@backParameter", SqlDbType.VarChar,50),
					new SqlParameter("@backUnit", SqlDbType.Money,8),
					new SqlParameter("@departmentid", SqlDbType.Int,4),
					new SqlParameter("@signature", SqlDbType.VarChar,50),
					new SqlParameter("@datex", SqlDbType.DateTime),
					new SqlParameter("@backOpinion", SqlDbType.VarChar,500)};
            backParameters[0].Value = model.GoesId;
            backParameters[1].Value = model.backsname;
            backParameters[2].Value = model.backModel;
            backParameters[3].Value = model.backQuantity;
            backParameters[4].Value = model.backParameter;
            backParameters[5].Value = model.backUnit;
            backParameters[6].Value = model.departmentid;
            backParameters[7].Value = model.signature;
            backParameters[8].Value = model.datex;
            backParameters[9].Value = model.backOpinion;
            ht.Add(strSql, backParameters);

            DbHelperSQL.ExecuteSqlTran(ht);
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public void Update(DLOA.Model.Commodityback model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update Commodityback set ");
			strSql.Append("goesId=@goesId,");
			strSql.Append("backsname=@backsname,");
			strSql.Append("backModel=@backModel,");
			strSql.Append("backQuantity=@backQuantity,");
			strSql.Append("backParameter=@backParameter,");
			strSql.Append("backUnit=@backUnit,");
			strSql.Append("departmentid=@departmentid,");
			strSql.Append("signature=@signature,");
			strSql.Append("datex=@datex,");
			strSql.Append("backOpinion=@backOpinion");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@goesId", SqlDbType.Int,4),
					new SqlParameter("@backsname", SqlDbType.VarChar,50),
					new SqlParameter("@backModel", SqlDbType.VarChar,50),
					new SqlParameter("@backQuantity", SqlDbType.Int,4),
					new SqlParameter("@backParameter", SqlDbType.VarChar,50),
					new SqlParameter("@backUnit", SqlDbType.Money,8),
					new SqlParameter("@departmentid", SqlDbType.Int,4),
					new SqlParameter("@signature", SqlDbType.VarChar,50),
					new SqlParameter("@datex", SqlDbType.DateTime),
					new SqlParameter("@backOpinion", SqlDbType.VarChar,500)};
			parameters[0].Value = model.id;
			parameters[1].Value = model.GoesId;
			parameters[2].Value = model.backsname;
			parameters[3].Value = model.backModel;
			parameters[4].Value = model.backQuantity;
			parameters[5].Value = model.backParameter;
			parameters[6].Value = model.backUnit;
			parameters[7].Value = model.departmentid;
			parameters[8].Value = model.signature;
			parameters[9].Value = model.datex;
			parameters[10].Value = model.backOpinion;

			DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public void Delete(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete Commodityback ");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
				};
			parameters[0].Value = id;
			DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public DLOA.Model.Commodityback GetModel(int id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select id,goesId,backsname,backModel,backQuantity,backParameter,backUnit,departmentid,signature,datex,backOpinion from Commodityback ");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
			parameters[0].Value = id;
			DLOA.Model.Commodityback model=new DLOA.Model.Commodityback();
			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=int.Parse(dt.Rows[0]["goesId"].ToString());
				}
				model.backsname=dt.Rows[0]["backsname"].ToString();
				model.backModel=dt.Rows[0]["backModel"].ToString();
				if(dt.Rows[0]["backQuantity"].ToString()!="")
				{
					model.backQuantity=int.Parse(dt.Rows[0]["backQuantity"].ToString());
				}
				model.backParameter=dt.Rows[0]["backParameter"].ToString();
				if(dt.Rows[0]["backUnit"].ToString()!="")
				{
					model.backUnit=decimal.Parse(dt.Rows[0]["backUnit"].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.backOpinion=dt.Rows[0]["backOpinion"].ToString();
				return model;
			}
			else
			{
			return null;
			}
		}
		/// <summary>
		/// 获得数据列表
		/// </summary>
        public DataTable GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT Commodityback.id, Commodityback.goesId,");
            strSql.Append(" Commodityback.backsname, Commodityback.backModel, ");
            strSql.Append(" Commodityback.backQuantity, Commodityback.backParameter, ");
            strSql.Append(" Commodityback.backUnit, Commodityback.departmentid, ");
            strSql.Append(" Commodityback.signature, Commodityback.backOpinion,");
            strSql.Append(" Commodityback.datex, Department.name as DepartmentName, ");
            strSql.Append(" Personnel.name  as SignatureName");
            strSql.Append(" FROM Commodityback INNER JOIN");
            strSql.Append(" Department ON Commodityback.departmentid = Department.id inner JOIN");
            strSql.Append(" Personnel ON Commodityback.signature = Personnel.id ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.QueryBySql(strSql.ToString());
        }
		#endregion  成员方法
	
    }
}