KN_CAMP_SOFTWARE_TEST/Code/ClassLib/ZHYQ/OracleDAL/DeviceInfoDAL.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

using MySql.Data.MySqlClient;

namespace FangYar.OracleDAL
{
    /// <summary>
    /// 设备（Oracle数据库实现）
    /// </summary>
    public class DeviceInfoDAL : FangYar.IDAL.DeviceInfoIDAL
    {
        #region 私有方法

        /// <summary>
        /// 把DataRow行转成实体类对象
        /// </summary>
        private void DataRowToModel(FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model, DataRow dr)
        {
            if (!DBNull.Value.Equals(dr["ID"]))
                model.ID = dr["ID"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_CODE"]))
                model.DEV_CODE = dr["DEV_CODE"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_NAME"]))
                model.DEV_NAME = dr["DEV_NAME"].ToString();
            if (!DBNull.Value.Equals(dr["GROUP_ID"]))
                model.GROUP_ID = int.Parse(dr["GROUP_ID"].ToString());
            if (!DBNull.Value.Equals(dr["DEV_CLASS"]))
                model.DEV_CLASS = dr["DEV_CLASS"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LOC"]))
                model.DEV_LOC = dr["DEV_LOC"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_FACTORY"]))
                model.DEV_FACTORY = dr["DEV_FACTORY"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_MODEL"]))
                model.DEV_MODEL = dr["DEV_MODEL"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_ADDR"]))
                model.DEV_ADDR = dr["DEV_ADDR"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_TIME"]))
                model.DEV_TIME = new DateTime?(DateTime.Parse(dr["DEV_TIME"].ToString()));
            if (!DBNull.Value.Equals(dr["CPY_ID"]))
                model.CPY_ID = dr["CPY_ID"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LXR"]))
                model.DEV_LXR = dr["DEV_LXR"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LXRTEL"]))
                model.DEV_LXRTEL = dr["DEV_LXRTEL"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_HY"]))
                model.DEV_HY = dr["DEV_HY"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_STATE"]))
                model.DEV_STATE = dr["DEV_STATE"].ToString();
            if (!DBNull.Value.Equals(dr["ORG_CODE"]))
                model.ORG_CODE = dr["ORG_CODE"].ToString();
            if (!DBNull.Value.Equals(dr["FOLDER_ID"]))
                model.FOLDER_ID = dr["FOLDER_ID"].ToString();
        }
        /// <summary>
        /// 把DataRow行转成实体类对象
        /// </summary>
        private FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model DataRowToModel(DataRow dr)
        {
            FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model = new Model.ZHSQ.ZHSQ_DEVICE_INFO_Model();
            if (!DBNull.Value.Equals(dr["ID"]))
                model.ID = dr["ID"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_CODE"]))
                model.DEV_CODE = dr["DEV_CODE"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_NAME"]))
                model.DEV_NAME = dr["DEV_NAME"].ToString();
            if (!DBNull.Value.Equals(dr["GROUP_ID"]))
                model.GROUP_ID = int.Parse(dr["GROUP_ID"].ToString());
            if (!DBNull.Value.Equals(dr["DEV_CLASS"]))
                model.DEV_CLASS = dr["DEV_CLASS"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LOC"]))
                model.DEV_LOC = dr["DEV_LOC"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_FACTORY"]))
                model.DEV_FACTORY = dr["DEV_FACTORY"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_MODEL"]))
                model.DEV_MODEL = dr["DEV_MODEL"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_ADDR"]))
                model.DEV_ADDR = dr["DEV_ADDR"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_TIME"]))
                model.DEV_TIME = new DateTime?(DateTime.Parse(dr["DEV_TIME"].ToString()));
            if (!DBNull.Value.Equals(dr["CPY_ID"]))
                model.CPY_ID = dr["CPY_ID"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LXR"]))
                model.DEV_LXR = dr["DEV_LXR"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_LXRTEL"]))
                model.DEV_LXRTEL = dr["DEV_LXRTEL"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_HY"]))
                model.DEV_HY = dr["DEV_HY"].ToString();
            if (!DBNull.Value.Equals(dr["DEV_STATE"]))
                model.DEV_STATE = dr["DEV_STATE"].ToString();
            if (!DBNull.Value.Equals(dr["ORG_CODE"]))
                model.ORG_CODE = dr["ORG_CODE"].ToString();
            if (!DBNull.Value.Equals(dr["FOLDER_ID"]))
                model.FOLDER_ID = dr["FOLDER_ID"].ToString();
            return model;


        }
        /// <summary>
        /// 把DataTable行转成实体类List集合
        /// </summary>
        private List<FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model> DataTableToList(DataTable dt)
        {
            List<FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model> modellist = new List<Model.ZHSQ.ZHSQ_DEVICE_INFO_Model>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow myRow in dt.Rows)
                {
                    modellist.Add(DataRowToModel(myRow));
                }
            }
            return modellist;

        }
        #endregion

        #region 基本方法

        /// <summary>
        /// 添加设备
        /// </summary>
        public bool Add(Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ZHSQ_DEVICE_INFO(DEV_CODE,DEV_NAME,GROUP_ID,DEV_CLASS,DEV_LOC,DEV_FACTORY,DEV_MODEL,DEV_ADDR,DEV_TIME,CPY_ID,DEV_LXR,DEV_LXRTEL,DEV_HY,DEV_STATE,FOLDER_ID,ORG_CODE)");
            strSql.Append(" values ");
            strSql.Append("(@DEV_CODE,@DEV_NAME,@GROUP_ID,@DEV_CLASS,@DEV_LOC,@DEV_FACTORY,@DEV_MODEL,@DEV_ADDR,@DEV_TIME,@CPY_ID,@DEV_LXR,@DEV_LXRTEL,@DEV_HY,@DEV_STATE,@FOLDER_ID,@ORG_CODE)");
            MySqlParameter[] paras = {
                    new MySqlParameter("@DEV_CODE",model.DEV_CODE),
                    new MySqlParameter("@DEV_NAME",model.DEV_NAME),
                    new MySqlParameter("@GROUP_ID",model.GROUP_ID),
                    new MySqlParameter("@DEV_CLASS",model.DEV_CLASS),
                    new MySqlParameter("@DEV_LOC",model.DEV_LOC),
                    new MySqlParameter("@DEV_FACTORY",model.DEV_FACTORY),
                    new MySqlParameter("@DEV_MODEL",model.DEV_MODEL),
                    new MySqlParameter("@DEV_ADDR",model.DEV_ADDR),
                    new MySqlParameter("@DEV_TIME",model.DEV_TIME),
                    new MySqlParameter("@CPY_ID",model.CPY_ID),
                    new MySqlParameter("@DEV_LXR",model.DEV_LXR),
                    new MySqlParameter("@DEV_LXRTEL",model.DEV_LXRTEL),
                    new MySqlParameter("@DEV_HY",model.DEV_HY),
                    new MySqlParameter("@DEV_STATE",model.DEV_STATE),
                    new MySqlParameter("@FOLDER_ID",model.FOLDER_ID),
                    new MySqlParameter("@ORG_CODE",model.ORG_CODE)
                };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改设备信息
        /// </summary>
        public bool Edit(Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ZHSQ_DEVICE_INFO set ");
            strSql.Append("DEV_CODE=@DEV_CODE,");
            strSql.Append("DEV_NAME=@DEV_NAME,");
            strSql.Append("GROUP_ID=@GROUP_ID,");
            strSql.Append("DEV_CLASS=@DEV_CLASS,");
            strSql.Append("DEV_LOC=@DEV_LOC,");
            strSql.Append("DEV_FACTORY=@DEV_FACTORY, ");
            strSql.Append("DEV_MODEL=@DEV_MODEL, ");
            strSql.Append("DEV_ADDR=@DEV_ADDR, ");
            strSql.Append("DEV_TIME=@DEV_TIME, ");
            strSql.Append("CPY_ID=@CPY_ID, ");
            strSql.Append("DEV_LXR=@DEV_LXR, ");
            strSql.Append("DEV_LXRTEL=@DEV_LXRTEL, ");
            strSql.Append("DEV_HY=@DEV_HY, ");
            strSql.Append("DEV_STATE=@DEV_STATE, ");
            strSql.Append("FOLDER_ID=@FOLDER_ID, ");
            strSql.Append("ORG_CODE=@ORG_CODE ");
            strSql.Append(" where ID=@ID");

            MySqlParameter[] paras = {
                new MySqlParameter("@DEV_CODE",model.DEV_CODE),
                new MySqlParameter("@DEV_NAME",model.DEV_NAME),
                new MySqlParameter("@GROUP_ID",model.GROUP_ID),
                new MySqlParameter("@DEV_CLASS",model.DEV_CLASS),
                new MySqlParameter("@DEV_LOC",model.DEV_LOC),
                new MySqlParameter("@DEV_FACTORY",model.DEV_FACTORY),
                new MySqlParameter("@DEV_MODEL",model.DEV_MODEL),
                new MySqlParameter("@DEV_ADDR",model.DEV_ADDR),
                new MySqlParameter("@DEV_TIME",model.DEV_TIME),
                new MySqlParameter("@CPY_ID",model.CPY_ID),
                new MySqlParameter("@DEV_LXR",model.DEV_LXR),
                new MySqlParameter("@DEV_LXRTEL",model.DEV_LXRTEL),
                new MySqlParameter("@DEV_HY",model.DEV_HY),
                new MySqlParameter("@DEV_STATE",model.DEV_STATE),
                new MySqlParameter("@FOLDER_ID",model.FOLDER_ID),
                new MySqlParameter("@ORG_CODE",model.ORG_CODE),
                new MySqlParameter("@ID",model.ID)
            };
            int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras); ;
            if (i > 0)
                return true;
            else
                return false;
        }



        /// <summary>
        /// 删除设备 
        /// </summary>
        public bool Delete(string IDList)
        {
            string sql = "delete from ZHSQ_DEVICE_INFO where ID in (" + IDList + ")";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 更改设备所属单位
        /// </summary>
        public bool Edit_ORG(string org_code, string IDList)
        {
            string sql = "update ZHSQ_DEVICE_INFO set ORG_CODE='" + org_code + "' where ID in (" + IDList + ")";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 更改设备所属目录
        /// </summary>
        public bool Edit_Folder(string folder_code, string IDList)
        {
            string sql = "update ZHSQ_DEVICE_INFO set FOLDER_ID='" + folder_code + "' where ID in (" + IDList + ")";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 更改设备所属行业
        /// </summary>
        public bool Edit_HY(string hy_code, string IDList)
        {
            string sql = "update ZHSQ_DEVICE_INFO set DEV_HY='" + hy_code + "' where ID in (" + IDList + ")";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 更改设备 
        /// </summary>
        public bool Edit_DEV(string field, string value, string IDList)
        {
            string sql = "update ZHSQ_DEVICE_INFO set " + field + "='" + value + "' where ID in (" + IDList + ")";
            try
            {
                FangYar.Common.MySqlHelper.ExecuteSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 根据设备编号获取设备
        /// </summary>
        public Model.ZHSQ.ZHSQ_DEVICE_INFO_Model GetModelBydeviceID(string DEV_CODE)
        {
            string sql = "select * from ZHSQ_DEVICE_INFO where DEV_CODE = @DEV_CODE";
            FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model = null;
            MySqlParameter[] paras = {
                    new MySqlParameter("@DEV_CODE",MySqlDbType.VarChar,50)//
                };
            paras[0].Value = DEV_CODE;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);

            if (dt.Rows.Count > 0)
            {
                model = new FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model();
                DataRowToModel(model, dt.Rows[0]);
                return model;
            }
            else
                return null;
        }

        /// <summary>
        /// 根据ID获取设备
        /// </summary>
        public Model.ZHSQ.ZHSQ_DEVICE_INFO_Model GetModelByID(string ID)
        {
            string sql = "select * from ZHSQ_DEVICE_INFO where ID = @ID";
            FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model model = null;
            MySqlParameter[] paras = {
                    new MySqlParameter("@ID",MySqlDbType.VarChar,50)//
                };
            paras[0].Value = ID;
            DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);

            if (dt.Rows.Count > 0)
            {
                model = new FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model();
                DataRowToModel(model, dt.Rows[0]);
                return model;
            }
            else
                return null;
        }

        /// <summary>
        /// 获取记录数
        /// </summary>
        public int Count(string strwhere)
        {
            string sql = "select count(1) from ZHSQ_DEVICE_INFO ";
            if (strwhere != null && strwhere != "")
            {
                sql += " where " + strwhere;
            }
            try
            {

                return FangYar.Common.MySqlHelper.GetCount(sql);

            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region 分页

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string strwhere)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public string QueryJsonList(int PageIndex, int PageSize, string strwhere, string order)
        {

            return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere, order));
        }

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public List<FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model> QueryList(int PageIndex, int PageSize, string strwhere)
        {

            return DataTableToList(QueryPage(PageIndex, PageSize, strwhere));
        }
        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public List<FangYar.Model.ZHSQ.ZHSQ_DEVICE_INFO_Model> QueryList(int PageIndex, int PageSize, string strwhere, string order)
        {

            return DataTableToList(QueryPage(PageIndex, PageSize, strwhere, order));
        }

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string strwhere)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "ZHSQ_DEVICE_INFO", strwhere);
        }

        /// <summary>
        /// 分页无排序
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="strwhere">排序</param>
        /// <returns></returns>
        public DataTable QueryPage(int PageIndex, int PageSize, string strwhere, string order)
        {

            return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "ZHSQ_DEVICE_INFO", strwhere, order);
        }
        /// <summary>
        /// 存储过程分页
        /// </summary>
        /// <param name="PageIndex">页面索引</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="strwhere">排序</param>
        /// <returns></returns>
        public void QueryProc(int PageIndex, int PageSize, string strwhere, string order)
        {
            int totalcount = 0;
            FangYar.Common.SqlPagerHelperOra.Select(PageSize, PageIndex, out totalcount, "ZHSQ_DEVICE_INFO", "*", null, null, null);


        }
        #endregion

        #region 扩展业务方法

        #endregion


    }
}