Exemple #1
0
        /// <summary>
        /// Obtener registros de asistencia
        /// </summary>
        /// <returns></returns>
        public DataTable GetAll()
        {
            string sql = "select * from AttLog order by AttTime desc";

            return(SqliteHelper.GetDataTable(sql));
        }
Exemple #2
0
        /// <summary>
        /// Registros claros de asistencia
        /// </summary>
        /// <returns></returns>
        public int ClearAll()
        {
            string sql = "delete from AttLog";

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #3
0
        public List <TableInfo> GetList(TableInfo ti)
        {
            //构造sql语句
            //链接查询,需要查到MTypeId的会员名称
            string sql = "select ti.*,hi.hTitle from TableInfo ti" +
                         " inner join hallinfo hi" +
                         " on ti.thallid=hi.hid" +
                         " where ti.tIsDelete=0";
            //拼接查询,得到结果
            List <SQLiteParameter> listP = new List <SQLiteParameter>();

            if (ti.THallId > 0)
            {
                sql += " and ti.THallId=@hid";
                listP.Add(new SQLiteParameter("@hid", ti.THallId));
            }
            if (ti.IsFreeSearch > -1)
            {
                sql += " and ti.TIsFree=@isFree";
                listP.Add(new SQLiteParameter("@isFree", ti.IsFreeSearch));
            }
            if (ti.TId != 0)//这里用来拼接查询,根据餐桌ID查询餐桌名称和所属包厅,在点菜时使用。
            {
                sql += " and ti.TId=@id";
                listP.Add(new SQLiteParameter("@id", ti.TId));
            }
            //if (!string.IsNullOrEmpty(mei.MName))
            //{
            //    //模糊查询
            //    sql += " and mei.mname like @name";
            //    listP.Add(new SQLiteParameter("@name", "%" + mei.MName + "%"));

            //}
            //if (!string.IsNullOrEmpty(mei.MPhone))
            //{
            //    sql += " and mei.mphone like @phone";
            //    listP.Add(new SQLiteParameter("@phone", "%" + mei.MPhone + "%"));
            //}

            sql += " order by ti.tid desc";
            //执行查询,获取数据
            DataTable table = SqliteHelper.GetList(sql, listP.ToArray());

            //构造集合对象
            //定义一个集合用于转存数据
            List <TableInfo> list = new List <TableInfo>();

            //逐行遍历数据,将表中的数据存到集合中
            foreach (DataRow row in table.Rows)
            {
                //1.list.Add()
                //2.new Managerinfo()
                //3.对象的初始化
                list.Add(new TableInfo()
                {
                    TId      = Convert.ToInt32(row["tid"]),
                    THallId  = Convert.ToInt32(row["thallid"]),
                    TTitle   = row["ttitle"].ToString(),
                    TIsFree  = Convert.ToBoolean(row["tisfree"]),
                    HallType = row["htitle"].ToString(),
                });
            }
            //返回数据
            return(list);
        }
        public List <MemberInfo> GetList(MemberInfo mi)
        {
            string sql = "select mi.*,mti.mctype,mti.mcbz from memberinfo mi" +
                         " inner join memberctypeinfo mti" +
                         " on mi.mtypeid=mti.mcid" +
                         " where mi.misdelete=0";

            //条件查询
            List <SQLiteParameter> listP = new List <SQLiteParameter>();

            if (!string.IsNullOrEmpty(mi.MName))
            {
                sql += " and mi.mname like @name";
                listP.Add(new SQLiteParameter("@name", "%" + mi.MName + "%"));//"%%" 表示模糊查询(不精确)
            }
            if (!string.IsNullOrEmpty(mi.MPhone))
            {
                sql += " and mi.mphone like @phone";
                listP.Add(new SQLiteParameter("@phone", "%" + mi.MPhone + "%"));
            }
            if (!string.IsNullOrEmpty(mi.MCId))
            {
                sql += " and mi.mcid = @mcid";
                listP.Add(new SQLiteParameter("@mcid", mi.MCId));
            }
            if (mi.MId > 0)
            {
                sql += " and mi.mid=@id";
                listP.Add(new SQLiteParameter("@id", mi.MId));
            }
            if (mi.MTag > 0)
            {
                switch (mi.MTag)
                {
                case 1:
                    sql += " and mi.menddate < datetime('now','7 day')";
                    break;

                case 2:
                    sql += " and mi.menddate < datetime('now','30 day')";
                    break;

                case 3:
                    sql += " and mi.mstartdate > datetime('now','-7 day')";
                    break;

                case 4:
                    sql += " and mi.mstartdate > datetime('now','-30 day')";
                    break;

                case 5:
                    sql += " and mi.menddate < datetime('now')";
                    break;
                }
            }

            sql += " order by mi.mid desc";//倒叙

            DataTable dt = SqliteHelper.GetList(sql, listP.ToArray());

            List <MemberInfo> list = new List <MemberInfo>();

            foreach (DataRow row in dt.Rows)
            {
                list.Add(new MemberInfo()
                {
                    MId        = Convert.ToInt32(row["mid"]),
                    MName      = row["mname"].ToString(),
                    MPhone     = row["mphone"].ToString(),
                    MAdress    = row["madress"].ToString(),
                    MCId       = row["mcid"].ToString(),
                    MSex       = row["msex"].ToString(),
                    MTypeId    = Convert.ToInt32(row["mtypeid"]),
                    MCount     = Convert.ToInt32(row["mcount"]),
                    MStartDate = Convert.ToDateTime(row["mstartdate"]),
                    MEndDate   = Convert.ToDateTime(row["menddate"]),
                    MDate      = Convert.ToDateTime(row["mdate"]),
                    MBZ        = row["mbz"].ToString(),
                    MPhoto     = row["mphoto"].ToString(),
                    TypeTitle  = row["mctype"].ToString(),
                    TypeBz     = row["mcbz"].ToString(),
                });
            }
            return(list);
        }
Exemple #5
0
        public static int DeleteAll()
        {
            string sql = "delete from tb_EquipmentData";

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #6
0
        /// <summary>
        /// 按ID删除设备
        /// </summary>
        /// <param name="equipmentID"></param>
        /// <returns></returns>
        public static int DeleteByEquipmentID(long equipmentID)
        {
            string sql = string.Format("delete from tb_EquipmentData where EquipmentID={0}", equipmentID);

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #7
0
        public static int DeleteByTime(long equipmentID, DateTime dt1, DateTime dt2)
        {
            string sql = string.Format("delete from tb_EquipmentData where EquipmentID={0} and AddTime >='{1}' and AddTime <='{2}'", equipmentID, dt1.ToString(), dt2.ToString());

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #8
0
        /// <summary>Update
        /// </summary>
        /// <param name="device"></param>
        /// <returns></returns>
        public int Update(DeviceModel device)
        {
            string sql = string.Format(@"
update Device set 
    TransInterval = @TransInterval , 
    TransTimes = @TransTimes , 
    Encrypt = @Encrypt , 
    LastRequestTime = @LastRequestTime , 
    DevIP = @DevIP , 
    DevMac = @DevMac , 
    DevFirmwareVersion = @DevFirmwareVersion , 
    UserCount = @UserCount , 
    AttCount = @AttCount , 
    DevName = @DevName , 
    TimeZone = @TimeZone , 
    Timeout = @Timeout , 
    SyncTime = @SyncTime , 
    ATTLOGStamp = @ATTLOGStamp , 
    OPERLOGStamp = @OPERLOGStamp , 
    ATTPHOTOStamp = @ATTPHOTOStamp , 
    ErrorDelay = @ErrorDelay , 
    Delay = @Delay , 
    TransFlag = @TransFlag , 
    Realtime = @Realtime,  
    VendorName = @VendorName,  
    IRTempDetectionFunOn = @IRTempDetectionFunOn,  
    MaskDetectionFunOn = @MaskDetectionFunOn,  
    MultiBioDataSupport = @MultiBioDataSupport,  
    MultiBioPhotoSupport = @MultiBioPhotoSupport,  
    MultiBioVersion = @MultiBioVersion,  
    MultiBioCount = @MultiBioCount,  
    MaxMultiBioDataCount = @MaxMultiBioDataCount,  
    MaxMultiBioPhotoCount = @MaxMultiBioPhotoCount  
where DevSN=@DevSN 
");

            SQLiteParameter[] parameters =
            {
                new SQLiteParameter("@DevSN",                 device.DevSN),
                new SQLiteParameter("@TransInterval",         device.TransInterval),
                new SQLiteParameter("@TransTimes",            device.TransTimes),
                new SQLiteParameter("@Encrypt",               device.Encrypt),
                new SQLiteParameter("@LastRequestTime",       device.LastRequestTime),
                new SQLiteParameter("@DevIP",                 device.DevIP),
                new SQLiteParameter("@DevMac",                device.DevMac),
                new SQLiteParameter("@DevFirmwareVersion",    device.DevFirmwareVersion),
                new SQLiteParameter("@UserCount",             device.UserCount),
                new SQLiteParameter("@AttCount",              device.AttCount),
                new SQLiteParameter("@DevName",               device.DevName),
                new SQLiteParameter("@TimeZone",              device.TimeZone),
                new SQLiteParameter("@Timeout",               device.Timeout),
                new SQLiteParameter("@SyncTime",              device.SyncTime),
                new SQLiteParameter("@ATTLOGStamp",           device.AttLogStamp),
                new SQLiteParameter("@OPERLOGStamp",          device.OperLogStamp),
                new SQLiteParameter("@ATTPHOTOStamp",         device.AttPhotoStamp),
                new SQLiteParameter("@ErrorDelay",            device.ErrorDelay),
                new SQLiteParameter("@Delay",                 device.Delay),
                new SQLiteParameter("@TransFlag",             device.TransFlag),
                new SQLiteParameter("@Realtime",              device.Realtime),
                new SQLiteParameter("@VendorName",            device.VendorName),
                new SQLiteParameter("@IRTempDetectionFunOn",  device.IRTempDetectionFunOn),
                new SQLiteParameter("@MaskDetectionFunOn",    device.MaskDetectionFunOn),
                new SQLiteParameter("@MultiBioDataSupport",   device.MultiBioDataSupport),
                new SQLiteParameter("@MultiBioPhotoSupport",  device.MultiBioPhotoSupport),
                new SQLiteParameter("@MultiBioVersion",       device.MultiBioVersion),
                new SQLiteParameter("@MultiBioCount",         device.MultiBioCount),
                new SQLiteParameter("@MaxMultiBioDataCount",  device.MaxMultiBioDataCount),
                new SQLiteParameter("@MaxMultiBioPhotoCount", device.MaxMultiBioPhotoCount)
            };
            return(SqliteHelper.ExecuteNonQuery(sql, parameters));
        }
        /// <summary>
        /// 清空
        /// </summary>
        /// <returns></returns>
        public int ClearAll()
        {
            string sql = "delete from TmpBioPhoto ";

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #10
0
        public static int DeleteByEqID(long eqid)
        {
            string sql = string.Format("delete from [tb_Chroma] where EquipmentID={0}", eqid);

            return(SqliteHelper.ExecuteNonQuery(sql));
        }
Exemple #11
0
        public static int DeleteAll()
        {
            string sql = "delete from [tb_Chroma]";

            return(SqliteHelper.ExecuteNonQuery(sql));
        }