SYCW_API/codegit/CallCenterApi/CallCenterAp.../archives.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using CallCenterApi.DB;
using System.Reflection;

namespace CallCenterApi.DAL
{
    public class archives
    {
        /// <summary>
        /// 查询物资档案表信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" * ");
            strSql.Append(" FROM archives ");

            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool IfHave(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from archivestate");
            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>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(CallCenterApi.Model.archives model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into archives(");
            strSql.Append("A_archiveCode,A_archiveName,A_archiveState,A_archivePeople,fileid)");
            strSql.Append(" values (");
            strSql.Append("@A_archiveCode,@A_archiveName,@A_archiveState,@A_archivePeople,@fileid)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@A_archiveCode", SqlDbType.NVarChar,100),
                    new SqlParameter("@A_archiveName", SqlDbType.NVarChar,100),
                    new SqlParameter("@A_archiveState", SqlDbType.Int,4),
                    new SqlParameter("@A_archivePeople", SqlDbType.NVarChar,50),
                    new SqlParameter("@fileid", SqlDbType.Int,4)};
            parameters[0].Value = model.A_archiveCode;
            parameters[1].Value = model.A_archiveName;
            parameters[2].Value = model.A_archiveState;
            //parameters[3].Value = model.A_archiveRecord;
            parameters[3].Value = model.A_archivePeople;
            parameters[4].Value = model.fileid;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 得到一个档案物资对象实体
        /// </summary>
        public CallCenterApi.Model.archives GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 a.*,s.stateName as stateName,o.OperationContent,o.OperationDate from archives as a left join archivestate as s on a.A_archiveState = s.id left join Operationlog as o on a.id = o.archivesid  ");
            strSql.Append(" where a.id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.BigInt)
            };
            parameters[0].Value = id;

            CallCenterApi.Model.T_Wo_WorkOrder model = new CallCenterApi.Model.T_Wo_WorkOrder();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelarchives(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个档案物资对象实体
        /// </summary>
        public CallCenterApi.Model.archives DataRowToModelarchives(DataRow row)
        {
            #region 之前的 
            //CallCenterApi.Model.archives model = new CallCenterApi.Model.archives();
            //if (row != null)
            //{
            //    if (row["id"] != null && row["id"].ToString() != "")
            //    {
            //        model.id = int.Parse(row["id"].ToString());
            //    }
            //    if (row["A_archiveCode"] != null)
            //    {
            //        model.A_archiveCode = row["A_archiveCode"].ToString();
            //    }
            //    if (row["A_archiveName"] != null && row["A_archiveName"].ToString() != "")
            //    {
            //        model.A_archiveName = row["A_archiveName"].ToString();
            //    }

            //    if (row["A_archiveState"] != null)
            //    {
            //        model.A_archiveState = int.Parse(row["A_archiveState"].ToString());
            //    }
            //    if (row["A_archivePeople"] != null)
            //    {
            //        model.A_archivePeople = row["A_archivePeople"].ToString();
            //    }
            //    if (row["stateName"] != null && row["stateName"].ToString() != "")
            //    {
            //        model.stateName = row["stateName"].ToString(); 
            //    }

            //}
            //return model; 
            #endregion
            Model.archives model = new Model.archives();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }

        /// <summary>
        /// 更新一条档案物资数据
        /// </summary>
        public bool Update(CallCenterApi.Model.archives model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update archives set ");
            strSql.Append("A_archiveCode=@A_archiveCode,");
            strSql.Append("A_archiveName=@A_archiveName,");
            strSql.Append("A_archiveState=@A_archiveState,");
            strSql.Append("A_archivePeople=@A_archivePeople");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@A_archiveCode", SqlDbType.NVarChar,100),
                    new SqlParameter("@A_archiveName", SqlDbType.NVarChar,100),
                    new SqlParameter("@A_archiveState", SqlDbType.Int,4),
                    new SqlParameter("@A_archivePeople", SqlDbType.NVarChar,50),
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.A_archiveCode;
            parameters[1].Value = model.A_archiveName;
            parameters[2].Value = model.A_archiveState;
           // parameters[3].Value = model.A_archiveRecord;
            parameters[3].Value = model.A_archivePeople;
            parameters[4].Value = model.id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 根据id 修改档案物资的状态
        /// </summary>
        /// <returns></returns>
        public bool updateState(int id,int stateid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update archives set ");
            strSql.Append("A_archiveState=@A_archiveState,");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@A_archiveState", SqlDbType.Int,4) };
            parameters[0].Value = stateid;
            parameters[1].Value = id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条档案物资数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from archives ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取物资状态信息 用作可选的下拉框选项
        /// </summary>
        /// <returns></returns>
        public DataSet GetArchivestate()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" * ");
            strSql.Append(" FROM archivestate ");

            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 得到一个物资状态对象实体
        /// </summary>
        public CallCenterApi.Model.archivestate DataRowToModelarchives1(DataRow row)
        {
            CallCenterApi.Model.archivestate model = new CallCenterApi.Model.archivestate();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["stateName"] != null)
                {
                    model.stateName = row["stateName"].ToString();
                }
               
            }
            return model;
        }
        /// <summary>
        /// 增加物资状态数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Addarchivestate(CallCenterApi.Model.archivestate model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into archivestate(");
            strSql.Append("stateName)");
            strSql.Append(" values (");
            strSql.Append("@stateName)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@stateName", SqlDbType.NVarChar,20)};
            parameters[0].Value = model.stateName;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 删除一条物资状态信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delarchivestate(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from archivestate ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 根据档案名称获取档案状态id
        /// </summary>
        /// <param name="statename"></param>
        /// <returns></returns>
        public int GetStateid(string statename)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from archivestate");
            strSql.Append(" where stateName=@stateName");
            SqlParameter[] parameters = {
                    new SqlParameter("@stateName", SqlDbType.NVarChar ,20)
            };
            parameters[0].Value = statename;
            var stateid = 0;

            var ds = DbHelperSQL.Query(strSql.ToString());
            foreach (Model.archivestate ar in ds.Tables[0].Columns)
            {
                stateid=ar.id;
            }

            return stateid;
        }
        /// <summary>
        /// 获取外部客户操作表信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetCustomerBorrow()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" * ");
            strSql.Append(" FROM customerBorrow ");

            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 外部客户操作信息添加
        /// </summary>
        /// <returns></returns>
        public int  AddCustomerBorrow(CallCenterApi.Model.customerBorrow model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into customerBorrow(");
            strSql.Append("archivesid,BorrowOreturn,BorrowDate,BorrowPeople)");
            strSql.Append(" values (");
            strSql.Append("@archivesid,@BorrowOreturn,@BorrowDate,@BorrowPeople)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@archivesid", SqlDbType.Int ,4),
                    new SqlParameter("@BorrowOreturn", SqlDbType.NVarChar,20),
                    new SqlParameter("@BorrowDate", SqlDbType.DateTime),
                    new SqlParameter("@BorrowPeople", SqlDbType.NVarChar,50) };
            parameters[0].Value = model.archivesid;
            parameters[1].Value = model.BorrowOreturn;
            parameters[2].Value = model.BorrowDate;
            parameters[3].Value = model.BorrowPeople;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 修改客户操作信息
        /// </summary>
        /// <returns></returns>
        public bool UpCustomerBorrow(CallCenterApi.Model.customerBorrow model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update customerBorrow set ");
            strSql.Append("archivesid=@archivesid,");
            strSql.Append("BorrowOreturn=@BorrowOreturn,");
            strSql.Append("BorrowDate=@BorrowDate,");
            strSql.Append("BorrowPeople=@BorrowPeople");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@archivesid", SqlDbType.Int ,4),
                    new SqlParameter("@BorrowOreturn", SqlDbType.NVarChar,20),
                    new SqlParameter("@BorrowDate", SqlDbType.DateTime),
                    new SqlParameter("@BorrowPeople", SqlDbType.NVarChar,50),
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.archivesid;
            parameters[1].Value = model.BorrowOreturn;
            parameters[2].Value = model.BorrowDate;
            parameters[3].Value = model.BorrowPeople;
            parameters[4].Value = model.id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取客户操作表实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CallCenterApi.Model.customerBorrow GetCustomerModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,archivesid,BorrowOreturn,BorrowDate,BorrowPeople from customerBorrow ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelcustomerBorrow(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取客户操作表实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public  Model.customerBorrow DataRowToModelcustomerBorrow(DataRow row)
        {
            Model.customerBorrow model = new Model.customerBorrow();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }
        /// <summary>
        /// 删除一条客户操作信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delCustomerBorrow(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from customerBorrow ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取内部操作表信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetinsideOperation()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" * ");
            strSql.Append(" FROM insideOperation ");

            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 内部员工操作信息添加
        /// </summary>
        /// <returns></returns>
        public int AddinsideOperation(CallCenterApi.Model.insideOperation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into insideOperation(");
            strSql.Append("archivesid,Warehousing,WarehousingDate,Operator)");
            strSql.Append(" values (");
            strSql.Append("@archivesid,@Warehousing,@WarehousingDate,@Operator)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@archivesid", SqlDbType.Int ,4),
                    new SqlParameter("@Warehousing", SqlDbType.NVarChar,20),
                    new SqlParameter("@WarehousingDate", SqlDbType.DateTime),
                    new SqlParameter("@Operator", SqlDbType.NVarChar,50) };
            parameters[0].Value = model.archivesid;
            parameters[1].Value = model.Warehousing;
            parameters[2].Value = model.WarehousingDate;
            parameters[3].Value = model.Operator;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 修改内部员工操作信息
        /// </summary>
        /// <returns></returns>
        public bool UpinsideOperation(CallCenterApi.Model.insideOperation model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update insideOperation set ");
            strSql.Append("archivesid=@archivesid,");
            strSql.Append("Warehousing=@Warehousing,");
            strSql.Append("WarehousingDate=@WarehousingDate,");
            strSql.Append("Operator=@Operator,");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@archivesid", SqlDbType.Int ,4),
                    new SqlParameter("@Warehousing", SqlDbType.NVarChar,20),
                    new SqlParameter("@WarehousingDate", SqlDbType.DateTime),
                    new SqlParameter("@Operator", SqlDbType.NVarChar,50),
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.archivesid;
            parameters[1].Value = model.Warehousing;
            parameters[2].Value = model.WarehousingDate;
            parameters[3].Value = model.Operator;
            parameters[4].Value = model.id;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 获取内部员工操作表实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CallCenterApi.Model.insideOperation GetinsideOperationModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,archivesid,Warehousing,WarehousingDate,Operator from insideOperation ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModelinsideOperation(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获取内部员工操作表实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.insideOperation DataRowToModelinsideOperation(DataRow row)
        {
            Model.insideOperation model = new Model.insideOperation();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }
        /// <summary>
        /// 删除一条内部员工操作信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delinsideOperation(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from insideOperation ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)
            };
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取操作日志表信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetOperationlog()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" * ");
            strSql.Append(" FROM Operationlog ");

            var ds = DbHelperSQL.Query(strSql.ToString());

            return ds;
        }
        /// <summary>
        /// 操作日志信息添加
        /// </summary>
        /// <returns></returns>
        public int AddOperationlog(CallCenterApi.Model.Operationlog model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Operationlog(");
            strSql.Append("archivesid,Operator,OperationContent,OperationDate)");
            strSql.Append(" values (");
            strSql.Append("@archivesid,@Operator,@OperationContent,@OperationDate)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@archivesid", SqlDbType.Int,50),
                    new SqlParameter("@Operator", SqlDbType.NVarChar,50),
                    new SqlParameter("@OperationContent", SqlDbType.NVarChar,100),
                    new SqlParameter("@OperationDate", SqlDbType.DateTime)};
            parameters[0].Value = model.archivesid;
            parameters[1].Value = model.Operator;//操作人
            parameters[2].Value = model.OperationContent;//操作内容
            parameters[3].Value = model.OperationDate;//操作时间

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 获取操作日志信息的实体
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public Model.Operationlog DataRowToModelOperationlog(DataRow row)
        {
            Model.Operationlog model = new Model.Operationlog();//主表字段
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        //用索引值设置属性值
                        proInfo.SetValue(model, row[i], null);
                    }
                }
                #endregion
            }
            return model;
        }
    }
}