C# SIOTSDB_HHZXDataContext.ExecuteQuery方法代码示例

        public List<BlacklistChangeRecord_blc_Info> AllRecords()
        {
            List<BlacklistChangeRecord_blc_Info> listRecord = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine(" SELECT blc_cRecordID,blc_iCardNo,blc_cOperation,blc_cOptReason,blc_lIsFinished,blc_cAdd ");
                sbSQL.AppendLine(" ,blc_dAddDate,blc_dFinishDate,cus_cIdentityNum as IdentityNum ");
                sbSQL.AppendLine(" FROM BlacklistChangeRecord_blc WITH(NOLOCK) ");
                sbSQL.AppendLine(" JOIN UserCardPair_ucp WITH(NOLOCK) ON ucp_iCardNo=blc_iCardNo ");
                sbSQL.AppendLine(" JOIN CardUserMaster_cus WITH(NOLOCK) ON cus_cRecordID=ucp_cCUSID ");
                sbSQL.AppendLine(" WHERE blc_lIsFinished=0 ");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<BlacklistChangeRecord_blc_Info> query = db.ExecuteQuery<BlacklistChangeRecord_blc_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listRecord = query.ToList<BlacklistChangeRecord_blc_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listRecord;
        }

        public List<MealBookingHistory_mbh_Info> AllRecords()
        {
            List<MealBookingHistory_mbh_Info> listPairInfo = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT ");
                sbSQL.AppendLine("* FROM MealBookingHistory_mbh WHERE 1 = 1");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<MealBookingHistory_mbh_Info> query = db.ExecuteQuery<MealBookingHistory_mbh_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listPairInfo = query.ToList<MealBookingHistory_mbh_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listPairInfo;
        }

        public List<Model.HHZX.ConsumerDevice.ConsumeMachineMaster_cmm_Info> AllRecords()
        {
            List<ConsumeMachineMaster_cmm_Info> listRecordInfo = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT TOP");
                sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
                sbSQL.AppendLine("* FROM ConsumeMachineMaster_cmm WHERE 1 = 1");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<ConsumeMachineMaster_cmm_Info> query = db.ExecuteQuery<ConsumeMachineMaster_cmm_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listRecordInfo = query.ToList<ConsumeMachineMaster_cmm_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listRecordInfo;
        }

        public List<Sys_UserMaster_usm_Info> SearchRecords(Sys_UserMaster_usm_Info infoObj)
        {
            List<Sys_UserMaster_usm_Info> listReturn = null;

            try
            {
                string strSql = "select * from dbo.Sys_UserMaster_usm where 1=1 ";

                if(infoObj != null)
                {
                    if (!String.IsNullOrEmpty(infoObj.usm_cUserLoginID))
                    {
                        strSql += " and usm_cUserLoginID like %'" + infoObj.usm_cUserLoginID + "'% ";
                    }
                }

                IEnumerable<Sys_UserMaster_usm_Info> infos = null;

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    infos = db.ExecuteQuery<Sys_UserMaster_usm_Info>(strSql, new object[] { });
                    if (infos != null)
                    {
                        listReturn = infos.ToList();
                    }
                }
            }
            catch
            {
                throw;
            }

            return listReturn;
        }

        public List<UserCardPair_ucp_Info> AllRecords()
        {
            List<UserCardPair_ucp_Info> listPairInfo = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT ");
                sbSQL.AppendLine("* FROM UserCardPair_ucp WHERE 1 = 1");
                sbSQL.AppendLine("AND ucp_lIsActive = 1");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<UserCardPair_ucp_Info> query = db.ExecuteQuery<UserCardPair_ucp_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listPairInfo = query.ToList<UserCardPair_ucp_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listPairInfo;
        }

        public List<CardUserAccountDetail_cuad_Info> AllRecords()
        {
            List<CardUserAccountDetail_cuad_Info> listRecord = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT ");
                sbSQL.AppendLine("* FROM CardUserAccountDetail_cuad WHERE 1 = 1");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<CardUserAccountDetail_cuad_Info> query = db.ExecuteQuery<CardUserAccountDetail_cuad_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listRecord = query.ToList<CardUserAccountDetail_cuad_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listRecord;
        }

        public List<DeductionBalance_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
        {
            List<DeductionBalance_Info> returnList = new List<DeductionBalance_Info>();

            try
            {
                DeductionBalance_Info searchInfo = searchCondition as DeductionBalance_Info;

                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("select *,cua_fCurrentBalance as Balance ");
                sbSQL.AppendLine("from dbo.PreConsumeRecord_pcs");
                sbSQL.AppendLine("join dbo.CardUserAccount_cua on cua_cRecordID = pcs_cAccountID");
                sbSQL.AppendLine("join dbo.CardUserMaster_cus on cus_cRecordID = cua_cCUSID");
                sbSQL.AppendLine("join dbo.ClassMaster_csm on csm_cRecordID = cus_cClassID");
                sbSQL.AppendLine("join dbo.UserCardPair_ucp on ucp_cCUSID = cus_cRecordID and ucp_dReturnTime is null");
                sbSQL.AppendLine(" where pcs_cConsumeType = 'SetMealCost' and pcs_lIsSettled = 1");

                if (searchInfo != null)
                {
                    if (searchInfo.ClassID != Guid.Empty)
                    {
                        sbSQL.AppendLine("AND csm_cRecordID ='" + searchInfo.ClassID.ToString() + "'");
                    }
                    if (searchInfo.CardNo > 0)
                    {
                        sbSQL.AppendLine("AND ucp_iCardNo ='" + searchInfo.CardNo.ToString() + "'");
                    }
                    if (!String.IsNullOrEmpty(searchInfo.ChaName))
                    {
                        sbSQL.AppendLine("AND cus_cChaName like N'%" + searchInfo.ChaName.ToString() + "%'");
                    }
                    if (!String.IsNullOrEmpty(searchInfo.StudentID))
                    {
                        sbSQL.AppendLine("AND cus_cStudentID ='" + searchInfo.StudentID.ToString() + "'");
                    }

                    sbSQL.AppendLine("AND pcs_dConsumeDate between '" + searchInfo.FromTime.ToString("yyyy/MM/dd") + "' and '" + searchInfo.ToTime.ToString("yyyy/MM/dd") + "'");
                    sbSQL.AppendLine("order by pcs_dConsumeDate desc");

                }

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<DeductionBalance_Info> query = db.ExecuteQuery<DeductionBalance_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        returnList = query.ToList<DeductionBalance_Info>();
                    }
                }

            }
            catch
            {

            }
            return returnList;
        }

        /// <summary>
        /// 取得日志
        /// </summary>
        /// <param name="p_strIP">IP</param>
        /// <param name="p_strSystemName">系统名</param>
        /// <param name="p_strModel">模组</param>
        /// <param name="p_strType">类型</param>
        /// <param name="p_strOperator">操作人</param>
        /// <returns></returns>
        public List<LogDetail_lgd_Info> GetLogs(string p_strIP, string p_strSystemName, string p_strModel, string p_strType, string p_strOperator)
        {
            StringBuilder l_strSQL = new StringBuilder();

            l_strSQL.AppendLine("Select Top 200 lgd_iID,");
            l_strSQL.AppendLine("lgd_cIpAddr,");
            l_strSQL.AppendLine("lgd_cSysName,");
            l_strSQL.AppendLine("lgd_cClassMethodName,");
            l_strSQL.AppendLine("lgd_cLogType,");
            l_strSQL.AppendLine("lgd_cLogMessage,");
            l_strSQL.AppendLine("lgd_cRemark,");
            l_strSQL.AppendLine("lgd_cOperator,");
            l_strSQL.AppendLine("lgd_dOperateDateTime");
            l_strSQL.AppendLine("From LogDetail_lgd");

            l_strSQL.AppendLine("Where 1=1");

            if (p_strIP != "")
            {
                l_strSQL.AppendLine("And lgd_cIpAddr = '" + p_strIP + "'");
            }

            if (p_strSystemName != "")
            {
                l_strSQL.AppendLine("And lgd_cSysName = '" + p_strSystemName + "'");
            }

            if (p_strModel != "")
            {
                l_strSQL.AppendLine("And lgd_cClassMethodName = '" + p_strModel + "'");
            }

            if (p_strType != "")
            {
                l_strSQL.AppendLine("And lgd_cLogType = '" + p_strType + "'");
            }

            if (p_strOperator != "")
            {
                l_strSQL.AppendLine("And lgd_cOperator = '" + p_strOperator + "'");
            }

            l_strSQL.AppendLine("Order By lgd_dOperateDateTime Desc");

            using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
            {
                return db.ExecuteQuery<LogDetail_lgd_Info>(l_strSQL.ToString(), new object[] { }).ToList();
            }
        }

        public List<DepartmentMaster_dpm_Info> AllRecords()
        {
            List<DepartmentMaster_dpm_Info> listReturn = null;

            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT * FROM DepartmentMaster_dpm");

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    listReturn = db.ExecuteQuery<DepartmentMaster_dpm_Info>(sbSQL.ToString(), new object[] { }).ToList();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            return listReturn;
        }

        public decimal GetOrtherIncome(DateTime dtQuery)
        {
            decimal fReturn = decimal.Zero;
            try
            {
                StringBuilder sbSQL = new StringBuilder();

                sbSQL.AppendLine("SELECT rid_fOrtherIncome FROM ReportIncomeDetail_rid");
                sbSQL.AppendLine("WHERE rid_cDateNo = '" + dtQuery.ToString("yyyy-MM-dd") + "'");
                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    decimal fSource = db.ExecuteQuery<decimal>(sbSQL.ToString(), new object[] { }).FirstOrDefault();
                    fReturn = fSource;
                }
            }
            catch (Exception ex)
            {
                return decimal.Zero;
            }
            return fReturn;
        }

        public List<PreConsumeRecord_pcs_Info> SearchRecords(IModelObject searchCondition)
        {
            List<PreConsumeRecord_pcs_Info> listRecordInfo = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT  pcs_cRecordID,pcs_cUserID,pcs_cAccountID,pcs_cSourceID,pcs_fCost,pcs_cConsumeType");
                sbSQL.AppendLine(",pcs_dConsumeDate,pcs_lIsSettled,pcs_dSettleTime,pcs_cAdd,pcs_dAddDate, mbh_cMealType as MealType");
                sbSQL.AppendLine("from PreConsumeRecord_pcs  with(nolock)");
                sbSQL.AppendLine("LEFT JOIN MealBookingHistory_mbh  with(nolock) on mbh_cRecordID = pcs_cSourceID ");

                PreConsumeRecord_pcs_Info searchInfo = searchCondition as PreConsumeRecord_pcs_Info;
                if (searchInfo != null)
                {
                    sbSQL.AppendLine("where 1= 1");
                    if (searchInfo.pcs_cUserID != Guid.Empty)
                        sbSQL.AppendLine("AND pcs_cUserID ='" + searchInfo.pcs_cUserID + "'");
                    if (!string.IsNullOrEmpty(searchInfo.pcs_cConsumeType))
                        sbSQL.AppendLine("AND pcs_cConsumeType ='" + searchInfo.pcs_cConsumeType + "'");
                    if (searchInfo.pcs_cAccountID != Guid.Empty)
                        sbSQL.AppendLine("AND pcs_cAccountID ='" + searchInfo.pcs_cAccountID.ToString() + "'");
                    if (searchInfo.pcs_dConsumeDate != DateTime.MinValue)
                        sbSQL.AppendLine("AND pcs_dConsumeDate ='" + searchInfo.pcs_dConsumeDate.ToString("yyyy-MM-dd HH:mm:ss") + "'");
                    if (searchInfo.pcs_fCost != 0)
                        sbSQL.AppendLine("AND pcs_fCost =" + searchInfo.pcs_fCost);
                }

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    db.CommandTimeout = 10 * 60000;
                    IEnumerable<PreConsumeRecord_pcs_Info> query = db.ExecuteQuery<PreConsumeRecord_pcs_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listRecordInfo = query.ToList<PreConsumeRecord_pcs_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listRecordInfo;
        }

        public List<Model.HHZX.ComsumeAccount.CardUserAccount_cua_Info> SearchRecords(Model.IModel.IModelObject searchCondition)
        {
            List<CardUserAccount_cua_Info> cuaList = new List<CardUserAccount_cua_Info>();

            try
            {
                CardUserMaster_cus_Info searchInfo = searchCondition as CardUserMaster_cus_Info;

                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine(@"select cus_cStudentID,cus_cChaName as cua_cUserName
            ,ISNULL(csm_cClassName,dpm_cName) as cua_cClassName
            ,ISNULL(cua_fCurrentBalance,0.00) as cua_fCurrentBalance
            ,cua_dLastSyncTime
            ,ISNULL(csr_fCardBalance,0.00) as cua_dCardBlance
            from CardUserMaster_cus with(nolock)
            left join CardUserAccount_cua with(nolock) on cus_cRecordID = cua_cCUSID
            left join ClassMaster_csm with(nolock) on csm_cRecordID = cus_cClassID
            left join DepartmentMaster_dpm with(nolock) on dpm_RecordID = cus_cClassID
            left join
            (
            select * from ConsumeRecord_csr with(nolock) inner join
            (select MAX(csr_dConsumeDate) as ConsumeDate,csr_cCardUserID as UserID from ConsumeRecord_csr with(nolock)
            where csr_lIsSettled = 1
            group by csr_cCardUserID) as MaxTB
            on csr_cCardUserID = UserID and csr_dConsumeDate = ConsumeDate
            ) as CosumeTB on cus_cRecordID = UserID");
                sbSQL.AppendLine("where 1=1 and cua_fCurrentBalance is not null");

                if (searchInfo != null)
                {
                    if (searchInfo.cus_cClassID != Guid.Empty)
                    {
                        sbSQL.AppendLine("AND cus_cClassID ='" + searchInfo.cus_cClassID.ToString() + "'");
                    }
                    if (!String.IsNullOrEmpty(searchInfo.cus_cStudentID))
                    {
                        sbSQL.AppendLine("AND cus_cStudentID like '%" + searchInfo.cus_cStudentID + "%'");
                    }
                    if (!String.IsNullOrEmpty(searchInfo.cus_cChaName))
                    {
                        sbSQL.AppendLine("AND cus_cChaName like N'%" + searchInfo.cus_cChaName + "%'");
                    }
                }

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    db.CommandTimeout = 10 * 60000;
                    IEnumerable<CardUserAccount_cua_Info> query = db.ExecuteQuery<CardUserAccount_cua_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        cuaList = query.ToList<CardUserAccount_cua_Info>();
                    }
                }

            }
            catch
            {

            }
            return cuaList;
        }

        public bool UpdateRecord(Sys_RoleMaster_rlm_Info infoObject)
        {
            string sqlString = string.Empty;
            using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
            {
                Sys_RoleMaster_rlm rlm = new Sys_RoleMaster_rlm();
                Sys_RoleMaster_rlm_Info info = new Sys_RoleMaster_rlm_Info();
                try
                {

                    rlm = db.Sys_RoleMaster_rlm.SingleOrDefault(t => t.rlm_iRecordID == infoObject.rlm_iRecordID);

                    for (int i = 0; i < rlm.Sys_UserRoles_usr.Count; i++)
                    {
                        Sys_UserRoles_usr sta = rlm.Sys_UserRoles_usr[i];
                        db.Sys_UserRoles_usr.DeleteOnSubmit(sta);
                    }

                    info = Common.General.CopyObjectValue<Sys_RoleMaster_rlm, Sys_RoleMaster_rlm_Info>(rlm);

                    IEnumerable<Sys_RoleMaster_rlm_Info> IErlm = null;
                    IEnumerable<Sys_UserRoles_usr_Info> IEusr = null;

                    sqlString += "UPDATE Sys_RoleMaster_rlm" + Environment.NewLine;
                    sqlString += " SET rlm_cRoleID='" + infoObject.rlm_cRoleID + "'," + Environment.NewLine;
                    sqlString += " rlm_cRoleDesc='" + infoObject.rlm_cRoleDesc + "'," + Environment.NewLine;
                    sqlString += " rlm_cLast='" + infoObject.rlm_cLast + "'," + Environment.NewLine;
                    sqlString += " rlm_dLastDate='" + infoObject.rlm_dLastDate.Value.ToString(DefineConstantValue.gc_DateTimeFormat) + "'" + Environment.NewLine;
                    sqlString += " WHERE rlm_iRecordID='" + infoObject.rlm_iRecordID + "'";

                    IErlm = db.ExecuteQuery<Sys_RoleMaster_rlm_Info>(sqlString, new object[] { });

                    sqlString = string.Empty;
                    sqlString += "DELETE FROM Sys_UserRoles_usr WHERE usr_cRoleID='" + infoObject.rlm_cRoleID + "'";
                    IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });

                    if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0)
                    {
                        for (int i = 0; i < infoObject.userMasterList.Count; i++)
                        {
                            sqlString = string.Empty;
                            Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i];

                            sqlString += "INSERT INTO Sys_UserRoles_usr" + Environment.NewLine;
                            sqlString += "(usr_cUserLoginID, usr_cRoleID)" + Environment.NewLine;
                            sqlString += " VALUES " + Environment.NewLine;
                            sqlString += "('" + usmInfo.usm_cUserLoginID + "','" + infoObject.rlm_cRoleID + "')";

                            IEusr = db.ExecuteQuery<Sys_UserRoles_usr_Info>(sqlString, new object[] { });
                        }
                    }

                    //if (rlm != null)
                    //{
                    //    //usm = Common.General.SetDataToLingQEntity(usm, infoObject);//时间溢出
                    //    rlm.rlm_cRoleDesc = infoObject.rlm_cRoleDesc;
                    //    rlm.rlm_cLast = infoObject.rlm_cLast;
                    //    rlm.rlm_dLastDate = infoObject.rlm_dLastDate.Value;

                    //    if (infoObject.userMasterList != null && infoObject.userMasterList.Count > 0)
                    //    {
                    //        for (int i = 0; i < infoObject.userMasterList.Count; i++)
                    //        {
                    //            Sys_UserMaster_usm_Info usmInfo = infoObject.userMasterList[i];

                    //            Sys_UserRoles_usr item = new Sys_UserRoles_usr();

                    //            item.usr_cUserLoginID = usmInfo.usm_cUserLoginID;
                    //            item.usr_cRoleID = infoObject.rlm_cRoleID;
                    //            //courseitem.cum_cNumber;
                    //            rlm.Sys_UserRoles_usrs.Add(item);
                    //            //db.Sys_UserRoles_usrs.InsertOnSubmit(item);
                    //        }
                    //    }
                    //    db.SubmitChanges();
                    //}
                    return true;
                }
                catch (Exception Ex)
                {
                    throw Ex;
                }
            }
        }

        public List<CardUserAccountDetail_cuad_Info> SearchRecords(IModelObject searchCondition)
        {
            List<CardUserAccountDetail_cuad_Info> listRecordInfo = null;
            try
            {
                StringBuilder sbSQL = new StringBuilder();
                sbSQL.AppendLine("SELECT TOP");
                sbSQL.AppendLine(Common.DefineConstantValue.ListRecordMaxCount.ToString());
                sbSQL.AppendLine(@"[cuad_cRecordID]
              ,[cuad_cCUAID]
              ,[cuad_fFlowMoney]
              ,[cuad_cFlowType]
              ,[cuad_cConsumeID]
              ,[cuad_cOpt]
              ,[cuad_dOptTime]
              ,csr_dConsumeDate
              ,csr_cMachineID
              ,csr_cCardID
              ,cmm_iMacNo as MacNo");
                sbSQL.AppendLine("FROM CardUserAccountDetail_cuad ");
                sbSQL.AppendLine("LEFT JOIN ConsumeRecord_csr ON  cuad_cConsumeID = csr_cRecordID");
                sbSQL.AppendLine("LEFT JOIN ConsumeMachineMaster_cmm ON csr_cMachineID = cmm_cRecordID");
                sbSQL.AppendLine("WHERE 1 = 1");

                CardUserAccountDetail_cuad_Info searchInfo = searchCondition as CardUserAccountDetail_cuad_Info;
                if (searchInfo != null)
                {
                    if (searchInfo.cuad_cCUAID != Guid.Empty)
                        sbSQL.AppendLine("AND cuad_cCUAID ='" + searchInfo.cuad_cCUAID + "'");
                    if (searchInfo.cuad_fFlowMoney != 0)
                        sbSQL.AppendLine("AND cuad_cFlowMoney =" + searchInfo.cuad_fFlowMoney.ToString());
                    if (!string.IsNullOrEmpty(searchInfo.cuad_cFlowType))
                        sbSQL.AppendLine("AND cuad_cFlowType ='" + searchInfo.cuad_cFlowType + "'");
                    if (searchInfo.cuad_dOptTime != DateTime.MinValue)
                        sbSQL.AppendLine("AND cuad_dOptTime ='" + searchInfo.cuad_dOptTime.ToString("yyyy-MM-dd HH:mm") + "'");
                    if (searchInfo.cuad_cConsumeID != null)
                        sbSQL.AppendLine("AND cuad_cConsumeID ='" + searchInfo.cuad_cConsumeID + "'");

                    if (searchInfo.OptTime_From != null && searchInfo.OptTime_To != null)
                    {
                        sbSQL.AppendLine("AND cuad_dOptTime between '" + ((DateTime)(searchInfo.OptTime_From)).ToString("yyyy-MM-dd HH:mm") + "' and  '" + ((DateTime)(searchInfo.OptTime_To)).ToString("yyyy-MM-dd HH:mm") + "'");
                    }

                }

                using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
                {
                    IEnumerable<CardUserAccountDetail_cuad_Info> query = db.ExecuteQuery<CardUserAccountDetail_cuad_Info>(sbSQL.ToString(), new object[] { });
                    if (query != null)
                    {
                        listRecordInfo = query.ToList<CardUserAccountDetail_cuad_Info>();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return listRecordInfo;
        }

 public ReturnValueInfo SetOrtherIncome(DateTime dtQuery, decimal fIncome)
 {
     ReturnValueInfo rvInfo = new ReturnValueInfo();
     try
     {
         StringBuilder sbSQL = new StringBuilder();
         sbSQL.AppendLine("if exists(select top 1 1 from dbo.ReportIncomeDetail_rid where rid_cDateNo='" + dtQuery.ToString("yyyy-MM-dd") + "')");
         sbSQL.AppendLine("begin");
         sbSQL.AppendLine("UPDATE ReportIncomeDetail_rid SET rid_fOrtherIncome=" + Math.Round(fIncome, 2).ToString());
         sbSQL.AppendLine("WHERE rid_cDateNo = '" + dtQuery.ToString("yyyy-MM-dd") + "'");
         sbSQL.AppendLine("end");
         sbSQL.AppendLine("else");
         sbSQL.AppendLine("begin");
         sbSQL.AppendLine("insert into ReportIncomeDetail_rid(rid_cDateNo,rid_fHotIncome,rid_fOrtherIncome,rid_dRecDate)values(");
         sbSQL.AppendLine("'" + dtQuery.ToString("yyyy-MM-dd") + "',0," + fIncome.ToString() + ",'" + dtQuery.ToString("yyyy-MM-dd HH:mm:ss") + "')");
         sbSQL.AppendLine("end");
         using (SIOTSDB_HHZXDataContext db = new SIOTSDB_HHZXDataContext())
         {
             var res = db.ExecuteQuery(typeof(string), sbSQL.ToString(), new object[] { });
             rvInfo.boolValue = true;
         }
     }
     catch (Exception ex)
     {
         rvInfo.isError = true;
         rvInfo.messageText = ex.Message;
         return rvInfo;
     }
     return rvInfo;
 }