public void UpdatePayState(string payid)
{
var sql = new StringBuilder();
sql.AppendLine("Update PayIn ");
sql.AppendLine(string.Format("SET PayState = (CASE WHEN PayState = '1' THEN '0' ELSE'1' END )"));
sql.AppendLine(string.Format("WHERE ID = {0}", payid));
ExcuteSQLEvent.Execute(sql.ToString());
}
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);
}
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);
}
public long GetTotalMoney(string Key, string CardGroupId, string VehicleGroupId, string Fromdate, string Todate)
{
long money = 0;
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 ISNULL(SUM(e.Moneys), 0) AS Moneys");
query.AppendLine("FROM dbo.[tblCardEvent] e WITH(NOLOCK)");
query.AppendLine("WHERE e.[IsDelete] = 0 and e.[EventCode] = '2'");
query.AppendLine(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));
}
money = ExcuteSQLEvent.ExecuteReturnMoney(query.ToString());
return(money);
}
/// <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]));
}
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]));
}
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]);
}
private void updatetblUserToUser()
{
//Cấu trúc bảng mới MVC
string script01 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC.sql"));
var t01 = ExcuteSQL.Execute(script01);
//Cấu trúc bảng mới MVC
string script01_1 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC_ParkingEvent.sql"));
var t01_1 = Kztek.Data.Event.SqlHelper.ExcuteSQLEvent.Execute(script01_1);
//Cấu trúc bổ sung của tblUser
string script02 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Structure_tblUser.sql"));
var t02 = ExcuteSQL.Execute(script02);
//Cấu trúc để với hệ thống iAccessEvent
string script03 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC_AccessEvent.sql"));
var t03 = Kztek.Data.AccessEvent.SqlHelper.ExcuteSQLEvent.Execute(script03);
//Bảng mới cho Access
string script04 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC_Access.sql"));
var t04 = ExcuteSQL.Execute(script04);
//BẢNG MỚI CHO TRƯỜNG CHINH
string script05 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Structure_NoteFreeTRANSERCO.sql"));
var t05 = ExcuteSQLEvent.Execute(script05);
//Cấu trúc bảng phần Locker model - Phần nạp thẻ cố định
string script06 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC_Locker.sql"));
var t06 = ExcuteSQL.Execute(script06);
//Cấu trúc bảng phần Locker Event model
string script07 = System.IO.File.ReadAllText(Server.MapPath("~/uploads/file/Data_MVC_LockerEvent.sql"));
var t07 = Kztek.Data.LockerEvent.SqlHelper.ExcuteSQLEvent.Execute(script07);
//Chuyển dữ liệu tblUser -> User
var list = _tblUserService.GetAll();
if (list.Any())
{
foreach (var item in list)
{
var obj = _UserService.GetById(item.UserID.ToString());
if (obj == null)
{
var tNew = new User();
tNew.Id = item.UserID.ToString();
tNew.Username = item.UserName;
tNew.Active = !Convert.ToBoolean(item.IsLock);
tNew.Admin = item.IsSystem;
tNew.DateCreated = DateTime.Now;
tNew.Name = item.FullName;
tNew.PasswordSalat = Guid.NewGuid().ToString();
tNew.IsDeleted = false;
var pass = CryptorEngine.Decrypt(item.Password, true);
tNew.Password = pass.PasswordHashed(tNew.PasswordSalat);
_UserService.Create(tNew);
}
}
}
//Gán quyền BAOVE
//var listNewUser = _UserService.GetAllActive();
//foreach (var item in listNewUser)
//{
// var UserRole = new UserRole()
// {
// Id = Common.GenerateId(),
// RoleId = "",
// UserId = item.Id
// };
//}
}