Ejemplo n.º 1
0
        public bool CheckCardInEvent(string cardnumber)
        {
            var query = new StringBuilder();

            query.AppendLine("SELECT TOP 1(e.Id) FROM dbo.[tblCardEvent] e WITH(NOLOCK)");
            query.AppendLine(string.Format("Where e.[CardNumber] = '{0}'", cardnumber));
            var obj = ExcuteSQLEvent.GetDataSet(query.ToString(), false);

            return(ExcuteSQLEvent.ConvertTo <ReportInOut_API_3rd_data>(obj.Tables[0]).Count > 0 ? true : false);
        }
Ejemplo n.º 2
0
        public MessageReport DeleteMulti(List <string> lstid)
        {
            var re = new MessageReport();

            re.Message   = "Error";
            re.isSuccess = false;

            try
            {
                if (lstid != null && lstid.Count > 0)
                {
                    var str = new StringBuilder();
                    str.AppendLine("delete from PublicEvent where");

                    var count = 0;

                    str.AppendLine(" EventID IN ( ");

                    foreach (var item in lstid)
                    {
                        count++;

                        str.AppendLine(string.Format("'{0}'{1}", item, count == lstid.Count ? "" : ","));
                    }

                    str.AppendLine(" )");

                    ExcuteSQLEvent.GetDataSet(str.ToString(), false);

                    re.Message   = "Xóa thành công";
                    re.isSuccess = true;
                }
                else
                {
                    re.Message   = "Có lỗi xảy ra";
                    re.isSuccess = false;
                }
            }
            catch (Exception ex)
            {
                re.Message   = ex.Message;
                re.isSuccess = false;
            }

            return(re);
        }
Ejemplo n.º 3
0
        /// <summary>
        /// get event in out
        /// </summary>
        /// <param name="KeyWord"></param>
        /// <param name="IsHaveTimeIn"></param>
        /// <param name="fromdate"></param>
        /// <param name="todate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List <ReportInOut_API_3rd_data> GetReportInOut(string key, string cardGroupId, bool isHaveTimeIn, string vehicleGroupId, string fromdate, string todate, int pageIndex, int pageSize, ref int totalItem, ref int totalPage)
        {
            if (!string.IsNullOrEmpty(fromdate))
            {
                fromdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd HH:mm:ss");
            }
            if (!string.IsNullOrEmpty(todate))
            {
                todate = Convert.ToDateTime(todate).ToString("yyyy/MM/dd HH:mm:ss");
            }
            var query = new StringBuilder();

            query.AppendLine("SELECT * FROM(");
            query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[DatetimeOut] desc) as RowNumber,a.*");
            query.AppendLine("FROM(");
            query.AppendLine("SELECT  e.Id, e.[CardNumber], CAST(CASE WHEN e.[PlateOut] <> '' THEN e.[PlateOut] ELSE e.[PlateIn] END AS nvarchar(50)) as Plate, CONVERT(VARCHAR, e.[DatetimeIn], 126) AS DatetimeIn ,CONVERT(VARCHAR, e.[DatetimeOut], 126) AS DatetimeOut,  e.[Moneys], e.[CardGroupID],e.[VehicleGroupID]");
            query.AppendLine("FROM dbo.[tblCardEvent] e WITH(NOLOCK)");

            if (isHaveTimeIn)
            {
                query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '1'");
            }
            else
            {
                query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '2'");
            }

            query.AppendLine(string.Format("{0}", isHaveTimeIn ? string.Format("AND e.[DatetimeIn] >= '{0}' AND e.[DatetimeIn] <= '{1}' ", fromdate, todate) : string.Format("AND e.[DatetimeOut] >= '{0}' AND e.[DatetimeOut] <= '{1}' ", fromdate, todate)));

            if (!string.IsNullOrWhiteSpace(key))
            {
                query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateIn], '-', ''), '.', '') LIKE '%{0}%' OR  REPLACE(REPLACE(e.[PlateOut], '-', ''), '.', '') LIKE '%{0}%')", key));
            }
            if (!string.IsNullOrWhiteSpace(cardGroupId))
            {
                query.AppendLine(string.Format("AND e.[CardGroupID] = '{0}'", cardGroupId));
            }
            if (!string.IsNullOrWhiteSpace(vehicleGroupId))
            {
                query.AppendLine(string.Format("AND e.[VehicleGroupID] = '{0}'", vehicleGroupId));
            }

            query.AppendLine(") as a");
            query.AppendLine(") as TEMP");
            query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageIndex, pageSize));

            //--Count Total
            query.AppendLine("SELECT COUNT(Id) as totalCount");
            query.AppendLine("FROM ( SELECT Id FROM dbo.[tblCardEvent]");
            query.AppendLine("e WITH(NOLOCK)");

            if (isHaveTimeIn)
            {
                query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '1'");
            }
            else
            {
                query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '2'");
            }

            query.AppendLine(string.Format("{0}", isHaveTimeIn ? string.Format("AND e.[DatetimeIn] >= '{0}' AND e.[DatetimeIn] <= '{1}' ", fromdate, todate) : string.Format("AND e.[DatetimeOut] >= '{0}' AND e.[DatetimeOut] <= '{1}' ", fromdate, todate)));

            ////Nhom the
            //if (!string.IsNullOrWhiteSpace(CardGroupID))
            //{
            //    var t = CardGroupID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            //    if (t.Any())
            //    {
            //        var count = 0;

            //        query.AppendLine("and e.CardGroupID IN ( ");

            //        foreach (var item in t)
            //        {
            //            count++;

            //            query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
            //        }

            //        query.AppendLine(" )");
            //    }
            //}

            if (!string.IsNullOrWhiteSpace(key))
            {
                query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateIn], '-', ''), '.', '') LIKE '%{0}%' OR REPLACE(REPLACE(e.[PlateOut], '-', ''), '.', '') LIKE '%{0}%')", key));
            }
            if (!string.IsNullOrWhiteSpace(cardGroupId))
            {
                query.AppendLine(string.Format("AND e.[CardGroupID] = '{0}'", cardGroupId));
            }
            if (!string.IsNullOrWhiteSpace(vehicleGroupId))
            {
                query.AppendLine(string.Format("AND e.[VehicleGroupID] = '{0}'", vehicleGroupId));
            }

            query.AppendLine(") as e");

            var list = ExcuteSQLEvent.GetDataSet(query.ToString(), false);

            totalItem = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
            totalPage = totalItem % pageSize > 0 ? totalItem / pageSize + 1 : totalItem / pageSize;
            return(ExcuteSQLEvent.ConvertTo <ReportInOut_API_3rd_data>(list.Tables[0]));
        }
Ejemplo n.º 4
0
        public List <ReportAlarm> GetAllPagingByFirst(string key, string user, string lane, string alarmCode, string fromdate, string todate, int pageNumber, int pageSize, ref int total)
        {
            string database = "MPARKING";

            if (!string.IsNullOrEmpty(fromdate))
            {
                fromdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd HH:mm:ss");
            }
            else
            {
                fromdate = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss");
            }
            if (!string.IsNullOrEmpty(todate))
            {
                todate = Convert.ToDateTime(todate).ToString("yyyy/MM/dd HH:mm:ss");
            }
            else
            {
                todate = Convert.ToDateTime(DateTime.Now).ToString("yyyy/MM/dd HH:mm:ss");
            }

            var query = new StringBuilder();

            query.AppendLine("select DISTINCT * FROM(");
            query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[Date] desc) AS RowNumber, a.*");
            query.AppendLine(string.Format(",ISNULL((select CardNo from [{0}].dbo.[tblCard] c where c.IsLock = '0' AND c.IsDelete = '0' and c.CardNumber = a.CardNumber),'') as CardNo", database));
            query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");

            query.AppendLine("WHERE 1 = 1");
            query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));

            if (!string.IsNullOrWhiteSpace(alarmCode))
            {
                query.AppendLine(string.Format("AND a.[AlarmCode] = '{0}'", alarmCode));
            }

            if (!string.IsNullOrWhiteSpace(lane))
            {
                var t = lane.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and LaneID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }

            if (!string.IsNullOrWhiteSpace(user))
            {
                var t = user.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and UserID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }

            if (!string.IsNullOrWhiteSpace(key))
            {
                query.AppendLine(string.Format("AND (a.[CardNumber] LIKE '%{0}%' OR a.[Plate] LIKE '%{0}%')", key));
            }

            query.AppendLine(") as a");
            query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageNumber, pageSize));

            //COUNT TOTAL
            query.AppendLine("SELECT COUNT(Id) totalCount");
            query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");

            query.AppendLine("WHERE 1 = 1");
            query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));

            if (!string.IsNullOrWhiteSpace(alarmCode))
            {
                query.AppendLine(string.Format("AND a.[AlarmCode] = '{0}'", alarmCode));
            }
            if (!string.IsNullOrWhiteSpace(lane))
            {
                var t = lane.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and LaneID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }

            if (!string.IsNullOrWhiteSpace(user))
            {
                var t = user.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and UserID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }
            if (!string.IsNullOrWhiteSpace(key))
            {
                query.AppendLine(string.Format("AND (a.[CardNumber] LIKE '%{0}%' OR a.[Plate] LIKE '%{0}%' )", key));
            }

            var list = ExcuteSQLEvent.GetDataSet(query.ToString(), false);

            total = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
            return(ExcuteSQLEvent.ConvertTo <ReportAlarm>(list.Tables[0]));
        }
Ejemplo n.º 5
0
        public DataTable ReportGetAllPagingByFirst_TRANSERCO(string key, string user, string lane, string alarmCode, string dateFromPicker)
        {
            var query = new StringBuilder();

            //tblAlarm
            //query.AppendLine("select * FROM(");
            query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.[Date] desc) AS NumberRow, (select convert(varchar(10), a.[Date], 103) + ' ' + left(convert(varchar(32), a.[Date], 108), 8)) as 'Date',a.[CardNumber] as 'CardNumber', a.[Plate] as 'Plate', a.[AlarmCode] as 'AlarmName', a.[LaneID] as 'LaneName', a.Description, a.[UserID] as 'Username'");

            query.AppendLine("FROM dbo.[tblAlarm] a WITH (NOLOCK)");
            query.AppendLine("WHERE 1 = 1 AND a.[AlarmCode] != '001'");

            if (!string.IsNullOrWhiteSpace(dateFromPicker))
            {
                //ISODate("2017-10-01T00:00:00.000+07:00")
                var fromdate = Convert.ToDateTime(dateFromPicker.Split(new[] { '-' })[0]).ToString("yyyy-MM-dd HH:mm:00");
                var todate   = Convert.ToDateTime(dateFromPicker.Split(new[] { '-' })[1]).ToString("yyyy-MM-dd HH:mm:59");

                query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));
            }
            else
            {
                //ISODate("2017-10-01T00:00:00.000+07:00")
                var fromdate = DateTime.Now.ToString("yyyy-MM-dd 00:00:00");
                var todate   = DateTime.Now.ToString("yyyy-MM-dd 23:59:59");

                query.AppendLine(string.Format("AND a.[Date] >= '{0}' AND a.[Date] <= '{1}'", fromdate, todate));
            }


            if (!string.IsNullOrWhiteSpace(alarmCode))
            {
                query.AppendLine(string.Format("AND a.[AlarmCode] = '{0}'", alarmCode));
            }

            if (!string.IsNullOrWhiteSpace(lane))
            {
                var t = lane.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and LaneID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }

            if (!string.IsNullOrWhiteSpace(user))
            {
                var t = user.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                if (t.Any())
                {
                    var count = 0;

                    query.AppendLine("and UserID IN ( ");

                    foreach (var item in t)
                    {
                        count++;

                        query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
                    }

                    query.AppendLine(" )");
                }
            }

            if (!string.IsNullOrWhiteSpace(key))
            {
                query.AppendLine(string.Format("AND (a.[CardNumber] LIKE '%{0}%' OR a.[Plate] LIKE '%{0}%')", key));
            }

            var list = ExcuteSQLEvent.GetDataSet(query.ToString(), false);

            return(list.Tables[0]);
        }