LYGJJ_4.0/DAL/DockingSyste...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using XYFDRQ.DBUtility;

namespace XYFDRQ.DAL
{
    /// <summary>
    /// 数据访问类:DockingSystem
    /// </summary>
    public partial class DockingSystem
    {
        public DockingSystem()
        { }

        #region 基本代码段

        #region 客户信息

        /// <summary>
        /// 获取客户信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserInfo(string strWhere)
        {
            try
            {
                string sql = "select * FROM V_CallCenter_CusInfos where 1=1 " + strWhere;
                return DbHelperSQLIGMS.Query(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        #region 小区信息

        /// <summary>
        /// 根据小区id获取小区信息
        /// </summary>
        /// <param name="lvId"></param>
        /// <returns></returns>
        public DataTable GetLvInfo(string lvId)
        {
            try
            {
                string sql = "select * from Lv where lvId=" + lvId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 根据小区编号或名称获取小区信息
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public DataTable GetLvInfos(string code)
        {
            try
            {
                string sql = "select top 10 * from Lv where Code like '" + code + "%' or Text like '%"+code+"%'";
                return DbHelperSQLIGMS.Query(sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 获取小区信息列表
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public DataTable GetLvInfos()
        {
            try
            {
                string sql = "select  * from Lv";
                return DbHelperSQLIGMS.Query(sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }

        #endregion

        #region 区域信息
        /// <summary>
        /// 获取区域id获取区域信息
        /// </summary>
        /// <param name="regionId"></param>
        /// <returns></returns>
        public DataTable GetRegionInfo(string regionId)
        {
            try
            {
                string sql = "select * from Region where regionId=" + regionId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }


        #endregion

        #region 用气类型

        /// <summary>
        /// 获取用气类型信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetPrice(string GasPriceId)
        {

            try
            {
                string sql = "select * from GasPrice where GasPriceId=" + GasPriceId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }

        #endregion

        #region 表具信息


        /// <summary>
        /// 获取表具信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDevice(string AddressId)
        {

            try
            {
                string sql = "select * from Device where AddressId=" + AddressId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }
        #endregion

        #region IC卡信息



        /// <summary>
        /// 获取IC卡信息
        /// </summary>
        /// <param name="DeviceId"></param>
        /// <returns></returns>
        public DataTable GetICCard(string DeviceId)
        {
            try
            {
                string sql = " select * from ICCard where DeviceId=" + DeviceId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }
        #endregion

        #region 抄表记录表

        /// <summary>
        /// 获取最后一次读表信息
        /// </summary>
        /// <param name="AddressId"></param>
        /// <param name="GasUserId"></param>
        /// <returns></returns>
        public DataTable GetLastReadMeterRecord(string AddressId, string GasUserId)
        {
            try
            {
                string sql = "select top 1 * from ReadMeterRecord where GasUserId=" + GasUserId + " and AddressId=" + AddressId + " order by ReadMeterRecordId desc";
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取读表信息
        /// </summary>
        /// <param name="AddressId"></param>
        /// <param name="GasUserId"></param>
        /// <returns></returns>
        public DataTable GetReadMeterRecord(string AddressId, string GasUserId)
        {
            try
            {
                string sql = "select  * from ReadMeterRecord where GasUserId=" + GasUserId + " and AddressId=" + AddressId + " order by ReadMeterRecordId desc";
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }
        }

       
        #endregion

        #region 包装记录
         /// <summary>
        /// 获取报装记录信息
        /// </summary>
        /// <param name="AddressId"></param>
        /// <returns></returns>
        public DataTable GetAddressProjectInfo(string AddressId)
        {
            try
            {
                string sql = "select  * from AddressProjectInfo where  AddressId=" + AddressId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion

        #region 产品类型
        /// <summary>
        /// 获取产品类型信息
       /// </summary>
       /// <param name="ProductId"></param>
       /// <returns></returns>
        public DataTable GetProduct(string ProductId)
        {

            try
            {
                string sql = "select * from Product where ProductId=" + ProductId;
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }

        #endregion

        #region 根据客户编号获取客户的欠费金额
       /// <summary>
        /// 根据客户编号获取客户的欠费金额
       /// </summary>
       /// <param name="cusCode"></param>
       /// <returns></returns>
        public DataTable GetQFMoneyByCusCode(string cusCode)
        {

            try
            {
                string sql = "select top 1 [Month],TotalCount,ActualReceivableMoney from V_CallCenter_Income where  AddressId=(select top 1 AddressId from V_CallCenter_CusInfos where AddressNumber='" + cusCode + "') and State=-1 order by [date] desc";
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }

        #endregion
        #region 根据客户编号获取客户所有的欠费金额
        /// <summary>
        /// 根据客户编号获取客户所有的欠费金额
        /// </summary>
        /// <param name="cusCode"></param>
        /// <returns></returns>
        public DataTable GetALLQFMoneyByCusCode(string cusCode)
        {

            try
            {
                string sql = "select isnull(SUM(TotalCount),0) as TotalCount,isnull(SUM(ActualReceivableMoney),0) as ActualReceivableMoney from V_CallCenter_Income where  AddressId=(select top 1 AddressId from V_CallCenter_CusInfos where AddressNumber='" + cusCode + "') and State=-1";
                return DbHelperSQLIGMS.Query(sql).Tables[0];

            }
            catch (Exception ex)
            {
                throw;
            }

        }

        #endregion
        #endregion

    }
}