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

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

        /// <summary>
        /// 
        /// </summary>
        public void Add(int signature)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select name ,max(model) as SpecificationModel,'件' as unit ,max(onQuantity) as OpeningQuantity,");
            strSql.Append(" max(onUnit) as OpeningUnit,max(onAmount) as OpeningAmount,");
            strSql.Append(" max(saveQuantity) as GoesQuantity,max(saveUnit) as GoesUnit,max(saveAmount) GoesAmount,");
            strSql.Append(" max(leavesQuantity) as leavesQuantity,max(leavesUnit) as leavesUnit,max(leavesAmount) as leavesAmount,");
            strSql.Append(" max(thisQuantity) as ConclusionQuantity,max(thisUnit) as ConclusionUnit,max(thisAmount) as ConclusionAmount ");
            strSql.Append(" 	from ( ");
            //	--初期数
            strSql.Append(" 	select goesName as Name,GoesModel as Model,goesQuantity as onQuantity,");
            strSql.Append(" 	goesUnit as onUnit,goesQuantity * goesUnit as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	from CommodityGoes");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 1 ");
            strSql.Append(" 	union all");
            //	--本期入库
            strSql.Append(" 	select leavesName as Name,max(leavesModel) as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount, ");
            strSql.Append(" 	sum(leavesQuantity) as saveQuantity,max(leavesUnit) as saveUnit,");
            strSql.Append(" 	sum(leavesAmount) as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	 from (");
            strSql.Append(" 	select leavesName,leavesModel,leavesQuantity,leavesUnit,leavesQuantity*leavesUnit as leavesAmount");
            strSql.Append(" 	from commodityLeaves as a");
            strSql.Append(" 	union all");
            strSql.Append(" 	select goesName,goesModel,goesQuantity,goesUnit,goesQuantity * goesUnit as goesAmount");
            strSql.Append(" 	from CommodityGoes as b");
            strSql.Append(" 	) tem");
            strSql.Append(" 	group by leavesName ");
            strSql.Append(" 	union all");
            //	--本月出库
            strSql.Append(" 	select leavesName as Name,leavesModel as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	leavesQuantity,leavesUnit,leavesQuantity*leavesUnit as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	from commodityLeaves");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 0 ");
            strSql.Append(" 	union all");
            //	--期末结存
            strSql.Append(" 	select goesName as Name,goesModel as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	goesQuantity as thisQuantity,goesUnit as thisUnit,");
            strSql.Append(" 	goesQuantity * goesUnit as  thisAmount ");
            strSql.Append(" 	from CommodityGoes ");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 0 ");
            strSql.Append(" ) as tem ");
            strSql.Append(" group by Name ");
            DataTable dt = DbHelperSQL.QueryBySql(strSql.ToString());
            Hashtable ht = new Hashtable();
            foreach (DataRow dr in dt.Rows)
            {
                strSql = new StringBuilder();
                strSql.Append("insert into MaterialMonth(");
                strSql.Append(" name,specificationModel,unit,openingQuantity,OpeningUnit,openingAmount,goesQuantity,goesUnit,goesAmount,leavesQuantity,leavesUnit,leavesAmount,conclusionQuantity,conclusionUnit,ConclusionAmount,signature,datex)");
                strSql.Append(" values (");
                strSql.Append("@name,@specificationModel,@unit,@openingQuantity,@OpeningUnit,@openingAmount,@goesQuantity,@goesUnit,@goesAmount,@leavesQuantity,@leavesUnit,@leavesAmount,@conclusionQuantity,@conclusionUnit,@ConclusionAmount,@signature,@datex)");
                SqlParameter[] parameters = {
					new SqlParameter("@name", SqlDbType.VarChar,50),
					new SqlParameter("@specificationModel", SqlDbType.VarChar,50),
					new SqlParameter("@unit", SqlDbType.VarChar,50),
					new SqlParameter("@openingQuantity", SqlDbType.Int,4),
					new SqlParameter("@OpeningUnit", SqlDbType.Money,8),
					new SqlParameter("@openingAmount", SqlDbType.Money,8),
					new SqlParameter("@goesQuantity", SqlDbType.Int,4),
					new SqlParameter("@goesUnit", SqlDbType.Money,8),
					new SqlParameter("@goesAmount", SqlDbType.Money,8),
					new SqlParameter("@leavesQuantity", SqlDbType.Int,4),
					new SqlParameter("@leavesUnit", SqlDbType.Money,8),
					new SqlParameter("@leavesAmount", SqlDbType.Money,8),
					new SqlParameter("@conclusionQuantity", SqlDbType.Int,4),
					new SqlParameter("@conclusionUnit", SqlDbType.Money,8),
					new SqlParameter("@ConclusionAmount", SqlDbType.Money,8),
					new SqlParameter("@signature", SqlDbType.VarChar,50),
					new SqlParameter("@datex", SqlDbType.DateTime)};
                parameters[0].Value = dr["name"];
                parameters[1].Value = dr["specificationModel"];
                parameters[2].Value = dr["unit"];
                parameters[3].Value = dr["openingQuantity"];
                parameters[4].Value = dr["OpeningUnit"];
                parameters[5].Value = dr["openingAmount"];
                parameters[6].Value = dr["goesQuantity"];
                parameters[7].Value = dr["goesUnit"];
                parameters[8].Value = dr["goesAmount"];
                parameters[9].Value = dr["leavesQuantity"];
                parameters[10].Value = dr["leavesUnit"];
                parameters[11].Value = dr["leavesAmount"];
                parameters[12].Value = dr["conclusionQuantity"];
                parameters[13].Value = dr["conclusionUnit"];
                parameters[14].Value = dr["ConclusionAmount"];
                parameters[15].Value = signature;
                parameters[16].Value = DateTime.Now;
                ht.Add(strSql, parameters);
            }
            DbHelperSQL.ExecuteSqlTran(ht);
        }

        public DataTable GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select 0 as Id, name ,max(model) as SpecificationModel,'件' as unit ,max(onQuantity) as OpeningQuantity,");
            strSql.Append(" max(onUnit) as OpeningUnit,max(onAmount) as OpeningAmount,");
            strSql.Append(" max(saveQuantity) as GoesQuantity,max(saveUnit) as GoesUnit,max(saveAmount) GoesAmount,");
            strSql.Append(" max(leavesQuantity) as leavesQuantity,max(leavesUnit) as leavesUnit,max(leavesAmount) as leavesAmount,");
            strSql.Append(" max(thisQuantity) as ConclusionQuantity,max(thisUnit) as ConclusionUnit,max(thisAmount) as ConclusionAmount ");
            strSql.Append(" 	from ( ");
            //	--初期数
            strSql.Append(" 	select goesName as Name,GoesModel as Model,goesQuantity as onQuantity,");
            strSql.Append(" 	goesUnit as onUnit,goesQuantity * goesUnit as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	from CommodityGoes");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 1 ");
            strSql.Append(" 	union all");
            //	--本期入库
            strSql.Append(" 	select leavesName as Name,max(leavesModel) as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount, ");
            strSql.Append(" 	sum(leavesQuantity) as saveQuantity,max(leavesUnit) as saveUnit,");
            strSql.Append(" 	sum(leavesAmount) as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	 from (");
            strSql.Append(" 	select leavesName,leavesModel,leavesQuantity,leavesUnit,leavesQuantity*leavesUnit as leavesAmount");
            strSql.Append(" 	from commodityLeaves as a");
            strSql.Append(" 	union all");
            strSql.Append(" 	select goesName,goesModel,goesQuantity,goesUnit,goesQuantity * goesUnit as goesAmount");
            strSql.Append(" 	from CommodityGoes as b");
            strSql.Append(" 	) tem");
            strSql.Append(" 	group by leavesName ");
            strSql.Append(" 	union all");
            //	--本月出库
            strSql.Append(" 	select leavesName as Name,leavesModel as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	leavesQuantity,leavesUnit,leavesQuantity*leavesUnit as leavesAmount,");
            strSql.Append(" 	0 as thisQuantity,0 as thisUnit, 0 as thisAmount");
            strSql.Append(" 	from commodityLeaves");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 0 ");
            strSql.Append(" 	union all");
            //	--期末结存
            strSql.Append(" 	select goesName as Name,goesModel as Model,");
            strSql.Append(" 	0 as onQuantity,0 as onUnit,0 as onAmount,");
            strSql.Append(" 	0 as saveQuantity,0 as saveUnit,0 as saveAmount,");
            strSql.Append(" 	0 as leavesQuantity,0 as leavesUnit, 0 as leavesAmount,");
            strSql.Append(" 	goesQuantity as thisQuantity,goesUnit as thisUnit,");
            strSql.Append(" 	goesQuantity * goesUnit as  thisAmount ");
            strSql.Append(" 	from CommodityGoes ");
            strSql.Append(" 	where datediff(mm,datex,getdate()) = 0 ");
            strSql.Append(" ) as tem ");
            strSql.Append(" group by Name ");
            DataTable dt =  DbHelperSQL.QueryBySql(strSql.ToString());
            int i = 1;
            //显示行号
            foreach (DataRow dr in dt.Rows)
            {
                dr["id"] = i;
                i++;
            }
            return dt;
        }
    }
}