public DataTable Excel(string KeyWord)
{
var query = new StringBuilder();
var dtrtn = new DataTable();
try
{
//query.AppendLine("SELECT * FROM(");
query.AppendLine(string.Format("select ROW_NUMBER() OVER(ORDER BY MainCard asc) AS STT,"));
query.AppendLine("a.MainCard as 'Mã thẻ chính', a.[CardNumber] AS 'Mã thẻ',a.[CardNo]");
query.AppendLine("FROM(");
query.AppendLine("SELECT *");
query.AppendLine("FROM dbo.[tblSubCard] e WITH (NOLOCK)");
query.AppendLine("WHERE e.[IsDelete] = 0");
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND (e.[CardNumber] LIKE '%{0}%' OR e.[CardNo] LIKE '%{0}%' OR e.[MainCard] LIKE '%{0}%')", KeyWord));
}
query.AppendLine(") as a");
dtrtn = ExcuteSQL.GetDataSet(query.ToString(), false).Tables[0];
}
catch (Exception ex)
{
}
return(dtrtn);
}
public List <SelectListModelAutocomplete> AutoComplete(string key = "")
{
var cus = new List <SelectListModelAutocomplete>();
var query = new StringBuilder();
query.AppendLine("select top 10 CardNumber, CardNo from tblCard");
query.AppendLine("where IsDelete = 'False'");
if (!string.IsNullOrEmpty(key))
{
query.AppendLine(string.Format("AND (CardNumber like N'%{0}%' OR CardNo LIKE N'%{0}%')", key));
}
query.AppendLine("order by CardNumber,CardNo");
var data = ExcuteSQL.GetDataSet(query.ToString(), false);
var list = ExcuteSQL.ConvertTo <tblCard>(data.Tables[0]);
foreach (var item in list)
{
var t = new SelectListModelAutocomplete();
t.id = item.CardNumber;
t.name = item.CardNumber;
t.value = item.CardNumber;
cus.Add(t);
}
return(cus);
}
public MessageReport CreateSQL(tblLocker obj)
{
var str = new StringBuilder();
str.AppendLine("INSERT INTO tblLocker (");
str.AppendLine("Id, Name, ReaderIndex, CardNo, CardNumber, ControllerID, DateCreated, LockerType");
str.AppendLine(") VALUES (");
str.AppendLine(string.Format("'{0}', N'{1}', {2}, '{3}', '{4}', '{5}', GETDATE(), '0'", obj.Id, obj.Name, obj.ReaderIndex, obj.CardNo, obj.CardNumber, obj.ControllerID));
str.AppendLine(")");
var result = new MessageReport(false, "Có lỗi xảy ra");
try
{
ExcuteSQL.Execute(str.ToString());
}
catch (Exception ex)
{
result = new MessageReport(false, ex.Message);
}
return(result);
}
public MessageReport SaveProcess(string TableName, string ColumnId, string LogId)
{
MessageReport rs = new MessageReport();
rs.isSuccess = false;
rs.Message = "Có lỗ xảy ra";
try
{
var str = new StringBuilder();
str.AppendLine("INSERT INTO Trash (Id, TableName, ColumnId, LogId, DateCreated) VALUES (");
str.AppendLine(string.Format("'{0}'", Common.GenerateId()));
str.AppendLine(string.Format(", '{0}'", TableName));
str.AppendLine(string.Format(", '{0}'", ColumnId));
str.AppendLine(string.Format(", '{0}'", LogId));
str.AppendLine(", GETDATE()");
str.AppendLine(")");
var k = ExcuteSQL.Execute(str.ToString());
if (k)
{
rs.isSuccess = k;
rs.Message = "Thêm thành công";
}
}
catch (Exception ex)
{
rs.isSuccess = false;
rs.Message = ex.Message;
}
return(rs);
}
public MessageReport UpdateSql(tblLocker obj)
{
MessageReport report;
try
{
var query = new StringBuilder();
query.AppendLine("UPDATE [dbo].[tblLocker]");
query.AppendLine(string.Format("SET [Name] = N'{0}'", obj.Name));
query.AppendLine(string.Format(",[ReaderIndex] = '{0}'", obj.ReaderIndex));
query.AppendLine(string.Format(",[CardNo] = '{0}'", obj.CardNo));
query.AppendLine(string.Format(",[CardNumber] = '{0}'", obj.CardNumber));
query.AppendLine(string.Format(",[ControllerID] = '{0}'", obj.ControllerID));
query.AppendLine(string.Format(",[LockerType] = '{0}'", obj.LockerType));
query.AppendLine(string.Format("WHERE Id = '{0}'", obj.Id));
ExcuteSQL.Execute(query.ToString());
report = new MessageReport(true, "Cập nhật thành công");
}
catch (Exception ex)
{
report = new MessageReport(false, ex.InnerException != null ? ex.InnerException.ToString() : ex.Message);
}
return(report);
}
private void SaveCardExtendProcess(tblCardSubmit obj, string _newexpire, string userid)
{
var sb = new StringBuilder();
sb.AppendLine("INSERT INTO tblActiveCard(Code, [Date], CardNumber, CardNo, Plate, OldExpireDate, [Days], NewExpireDate, CardGroupID, CustomerGroupID, UserID, FeeLevel, CustomerID,IsDelete)");
sb.AppendLine("VALUES (");
sb.AppendLine(string.Format("'{0}'", obj.CustomerCode));
sb.AppendLine(", GETDATE()");
sb.AppendLine(string.Format(", '{0}'", obj.CardNumber));
sb.AppendLine(string.Format(", '{0}'", obj.CardNo));
sb.AppendLine(string.Format(", '{0}'", obj.Plate1 + ";" + obj.Plate2 + ";" + obj.Plate3));
sb.AppendLine(string.Format(", '{0}'", Convert.ToDateTime(obj.DtpDateExpired).ToString("yyyy/MM/dd")));
sb.AppendLine(string.Format(", DATEDIFF(DAY, '{0}', '{1}')", Convert.ToDateTime(obj.DtpDateExpired).ToString("yyyy/MM/dd"), Convert.ToDateTime(_newexpire).ToString("yyyy/MM/dd")));
sb.AppendLine(string.Format(", '{0}'", Convert.ToDateTime(_newexpire).ToString("yyyy/MM/dd")));
sb.AppendLine(string.Format(", '{0}'", obj.CardGroupID));
sb.AppendLine(string.Format(", '{0}'", obj.CustomerGroupID));
sb.AppendLine(string.Format(", '{0}'", userid));
sb.AppendLine(string.Format(", '{0}'", "0"));
sb.AppendLine(string.Format(", '{0}'", obj.CustomerID));
sb.AppendLine(", 0");
sb.AppendLine(")");
//Update card
sb.AppendLine("UPDATE tblCard");
sb.AppendLine(string.Format("SET ExpireDate = '{0}'", Convert.ToDateTime(_newexpire).ToString("yyyy/MM/dd")));
sb.AppendLine(string.Format("WHERE CardNumber = '{0}'", obj.CardNumber));
ExcuteSQL.Execute(sb.ToString());
}
/// <summary>
/// Generate data khi đăng ký tài khoản đầu tiên
/// </summary>
/// <param name="userid"></param>
public void GenerateData(string userid)
{
//Data
//string script = StringUtil.GetTextFile(Server.MapPath("~/Templates/DataGenerate/DataGenerate.sql"));
//ExcuteSQL.Execute(script);
//Trigger
string strInsert = StringUtil.GetTextFile(Server.MapPath("~/Templates/DataGenerate/TriggerInsertMenuFunction.sql"));
ExcuteSQL.Execute(strInsert);
string strUpdate = StringUtil.GetTextFile(Server.MapPath("~/Templates/DataGenerate/TriggerUpdateMenuFunction.sql"));
ExcuteSQL.Execute(strUpdate);
var list = _RoleService.GetAllActive();
foreach (var item in list.ToList())
{
UserRole objJoin = new UserRole();
objJoin.Id = Common.GenerateId();
objJoin.UserId = userid;
objJoin.RoleId = item.Id;
_UserRoleService.Create(objJoin);
}
}
public void GenerateTable()
{
//Data
string script = StringUtil.GetTextFile(Server.MapPath("~/Templates/DataGenerate/ScriptTable.sql"));
ExcuteSQL.Execute(script);
//Trigger
}
public void DeleteServiceApartment(string ApartmentId)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("delete BM_Apartment_Service");
sb.AppendLine(string.Format("where ApartmentId = '{0}'", ApartmentId));
ExcuteSQL.Execute(sb.ToString());
}
public MessageReport CreateSql(tblLocker obj, string actionV, string message, string type = "0")
{
var re = new MessageReport();
re.Message = "Error";
re.isSuccess = false;
try
{
var sb = new StringBuilder();
sb.AppendLine("INSERT INTO dbo.[tblLockerProcess] (");
sb.AppendLine("Id");
sb.AppendLine(", LockerName");
sb.AppendLine(", LockerReaderIndex");
sb.AppendLine(", ControllerID");
sb.AppendLine(", CardNumber");
sb.AppendLine(", CardNo");
sb.AppendLine(", DateCreated");
sb.AppendLine(", UserId");
sb.AppendLine(", ActionLocker");
sb.AppendLine(", Type");
sb.AppendLine(", Description");
sb.AppendLine(") VALUES (");
sb.AppendLine(string.Format(" N'{0}'", Guid.NewGuid().ToString()));
sb.AppendLine(string.Format(", N'{0}'", obj.Name));
sb.AppendLine(string.Format(", '{0}'", obj.ReaderIndex));
sb.AppendLine(string.Format(", '{0}'", obj.ControllerID));
sb.AppendLine(string.Format(", '{0}'", obj.CardNumber));
sb.AppendLine(string.Format(", '{0}'", obj.CardNo));
sb.AppendLine(string.Format(", '{0}'", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")));
sb.AppendLine(string.Format(", '{0}'", GetCurrentUser.GetUser().Id));
sb.AppendLine(string.Format(", '{0}'", actionV));
sb.AppendLine(string.Format(", '{0}'", type));
sb.AppendLine(string.Format(", '{0}'", message));
sb.AppendLine(")");
ExcuteSQL.Execute(sb.ToString());
re.Message = "Thêm mới thành công";
re.isSuccess = true;
}
catch (Exception ex)
{
re.Message = ex.Message;
re.isSuccess = false;
}
return(re);
}
public void WriteLog(MessageReport message, string tableName, string columnId, string actionV, User user)
{
var thre = new Thread(() =>
{
//Đọc địa chỉ máy tính
//var computername = Common.GetComputerName(HttpContext.Current.Request.UserHostAddress);
//Mapping
var t = new Log()
{
Id = Common.GenerateId(),
Action = actionV,
ColumnId = columnId,
isSuccess = message.isSuccess,
Message = message.Message,
TableName = tableName,
UserId = user != null ? user.Id : "",
UserName = user != null ? user.Username : ""
};
var str = new StringBuilder();
str.AppendLine("INSERT INTO dbo.[Log] (");
str.AppendLine("Id, Action, ColumnId, isSuccess, Message, TableName, UserId, UserName, DateCreated");
str.AppendLine(") VALUES (");
str.AppendLine(string.Format("'{0}'", t.Id));
str.AppendLine(string.Format(", N'{0}'", t.Action));
str.AppendLine(string.Format(", N'{0}'", t.ColumnId));
str.AppendLine(string.Format(", {0}", t.isSuccess ? 1 : 0));
str.AppendLine(string.Format(", N'{0}'", t.Message));
str.AppendLine(string.Format(", N'{0}'", t.TableName));
str.AppendLine(string.Format(", N'{0}'", t.UserId));
str.AppendLine(string.Format(", N'{0}'", t.UserName));
str.AppendLine(", GETDATE()");
str.AppendLine(")");
var k = ExcuteSQL.Execute(str.ToString());
if (k)
{
if (actionV.Equals("Delete"))
{
_TrashService.SaveProcess(tableName, columnId, t.Id);
}
}
});
thre.Start();
}
public int CountParent()
{
var sb = new StringBuilder();
sb.AppendLine("SELECT COUNT(*) AS COUNT FROM tblCustomerGroup WHERE Inactive = 'False' AND ParentId = '0' ");
var lst = ExcuteSQL.GetDataSet(sb.ToString(), false);
var count = Convert.ToInt32(lst.Tables[0].Rows[0][0].ToString());
return(count);
}
public List <tblCustomer> GetAllActiveByKeySQL(string key, int max)
{
var query = new StringBuilder();
query.AppendLine(string.Format("SELECT top {0} * FROM tblCustomer where Inactive = 'False'", max));
if (!string.IsNullOrEmpty(key))
{
query.AppendLine(string.Format("AND (CustomerCode LIKE '%{0}%' OR CustomerName LIKE N'%{0}%' OR IDNumber LIKE '%{0}%' OR Mobile LIKE '%{0}%')", key));
}
var dataSet = ExcuteSQL.GetDataSet(query.ToString(), false);
var list = ExcuteSQL.ConvertTo <tblCustomer>(dataSet.Tables[0]);
return(list);
}
public bool AddNew(string listCardNumber, int _feelevel, string _oldexpire, string _newexpire, string datecreated, string userId, bool chbEnableMinusActive, string subid = "", string Id = "", string dateextend = "")
{
if (!string.IsNullOrEmpty(_oldexpire))
{
_oldexpire = Convert.ToDateTime(_oldexpire).ToString("MM/dd/yyyy");
}
if (!string.IsNullOrEmpty(_newexpire))
{
_newexpire = Convert.ToDateTime(_newexpire).ToString("MM/dd/yyyy");
}
if (!string.IsNullOrEmpty(datecreated))
{
datecreated = Convert.ToDateTime(datecreated).ToString("MM/dd/yyyy");
}
var sb = new StringBuilder();
sb.AppendLine("INSERT INTO ExtendCard(Id,Code,DateCreated,[Date], CardNumber, CardNo, Plate, OldExpireDate, [Days], NewExpireDate, CardGroupID, CustomerGroupID, UserID, FeeLevel, CustomerID,IsDelete,IsTransferPayment,SubId)");
sb.AppendLine(string.Format("SELECT '{1}', CASE WHEN cus.CustomerCode IS NULL THEN '0' ELSE cus.CustomerCode END,GetDate(),'{0}', ca.Cardnumber,ca.CardNo", datecreated, Id));
sb.AppendLine(", CAST(CASE WHEN ca.Plate2 <> '' THEN ISNULL(ca.Plate1,'') + ';' + ISNULL(ca.Plate2,'') WHEN ca.Plate3 <> '' THEN ISNULL(ca.Plate1,'') + ';' + ISNULL(ca.Plate2,'') + ';' + ISNULL(ca.Plate3,'') WHEN ca.Plate1 IS NULL THEN '' ELSE ca.Plate1 END AS nvarchar(50)) as Plate");
sb.AppendLine(string.Format(", {1}, DATEDIFF(DAY, ca.[ExpireDate], '{0}')", _newexpire, _oldexpire));
sb.AppendLine(string.Format(", '{0}', ca.CardGroupID, CASE WHEN cus.CustomerGroupID IS NULL THEN '0' ELSE cus.CustomerGroupID END,'{2}','{1}', CASE WHEN ca.CustomerID IS NULL THEN '0' ELSE ca.CustomerID END,0,0,'{3}'", _newexpire, _feelevel, userId, subid));
sb.AppendLine("from tblCard ca");
sb.AppendLine("LEFT join tblCustomer cus on ca.CustomerID = CONVERT(varchar(255), cus.CustomerID)");
sb.AppendLine("where ca.IsDelete = 0 and ca.IsLock=0");
//Neu so ngay gia han <0 va neu ko check thi ko cho gia han
if (chbEnableMinusActive == false)
{
sb.AppendLine(string.Format("and DATEDIFF(DAY, ca.[ExpireDate], '{0}') >=0 AND ca.[ExpireDate] <= '{0}'", dateextend));
}
if (!string.IsNullOrWhiteSpace(listCardNumber))
{
//where in
sb.AppendLine(string.Format(" and ca.CardNumber IN ({0})", listCardNumber));
}
return(ExcuteSQL.Execute(sb.ToString()));
}
public MessageReport UpdateAuthorizeCustomer(string key, string customergroup, string levelid, string customerstatus = "")
{
var result = new MessageReport(false, "Có lỗi xảy ra");
var str = new StringBuilder();
str.AppendLine("UPDATE tblCustomer");
str.AppendLine(string.Format("SET AccessLevelID = '{0}'", levelid));
str.AppendLine("WHERE 1=1");
var l = customergroup.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (l.Any())
{
str.AppendLine("AND CustomerGroupID IN (");
var count = 0;
foreach (var item in l)
{
count++;
str.AppendLine(string.Format("'{0}'{1}", item, count == l.Count() ? "" : ","));
}
str.AppendLine(")");
}
try
{
var isSuccess = ExcuteSQL.Execute(str.ToString());
result = new MessageReport(isSuccess, "Thành công");
}
catch (Exception ex)
{
result = new MessageReport(false, ex.Message);
}
return(result);
}
public MessageReport UpdateCustomer(string useroffinger, string dateN, bool checkuse = false)
{
dateN = dateN.Substring(0, 4) + @"/" + dateN.Substring(4, 2) + @"/" + dateN.Substring(6, 2);
var result = new MessageReport(false, "Có lỗi xảy ra");
if (checkuse)
{
var str = string.Format("Update tblCustomer set AccessExpireDate = '{0}' where UserIDofFinger = '{1}'", dateN, useroffinger);
try
{
result.isSuccess = ExcuteSQL.Execute(str);
result.Message = "Thành công";
}
catch (Exception ex)
{
result.isSuccess = false;
result.Message = ex.Message;
}
}
return(result);
}
public JsonResult UploadFileUpdate()
{
var result = new MessageReport();
var filename = "";
try
{
string error = "";
string fullfolder = ConfigurationManager.AppSettings["FileUploadDownload"];
var httpPostedFile = Request.Files["UploadedFile"];
if (httpPostedFile != null)
{
filename = Common.UploadFile(out error, Server.MapPath(fullfolder), httpPostedFile);
if (string.IsNullOrWhiteSpace(error))
{
var url = string.Format("{0}{1}", fullfolder, filename);
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath(url));
if (ds != null && ds.Tables.Count > 0)
{
var dt = ds.Tables[0];
var code = dt.Rows[0]["Code"].ToString();
//Check code theo key
var decode = CryptoProvider.SimpleDecryptWithPassword(code, SecurityModel.Keypass);
if (decode != null)
{
var t = ExcuteSQL.Execute(decode);
if (t)
{
result.isSuccess = true;
result.Message = "Nạp thành công";
if (result.isSuccess)
{
SaveHistory(dt, filename);
}
}
else
{
result.isSuccess = false;
result.Message = "Nạp thất bại";
}
}
else
{
result.isSuccess = false;
result.Message = "Sai bảo mật file";
}
}
else
{
result.isSuccess = false;
result.Message = "Nạp thất bại";
}
}
else
{
result.isSuccess = false;
result.Message = error;
}
}
else
{
result.isSuccess = false;
result.Message = "Vui lòng chọn file";
}
}
catch (Exception ex)
{
result.isSuccess = false;
result.Message = ex.Message;
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public List <ReporttblAccessUploadProcess> GetReportUploadProcessDetail(string KeyWord, List <string> customerGroupId, string _fromdate, string _todate, string CardGroupID, string Actions, string UserID, int pageIndex, int pageSize, ref int total, string type = "", string eventstatus = "")
{
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(");
//tblCardProcess
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY [Date] desc) AS RowNumber, cu.CustomerName, cu.[Address], cu.[CustomerGroupID], c.[CardGroupID], c.[Date], c.[UserID], c.[Actions], c.[UserIDofFinger], c.[SuccessControllerIDs], c.[CardNumber], c.[EventType], c.[AccessDateExpire]");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
query.AppendLine(") as a");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageIndex, pageSize));
//--COUNT TOTAL
query.AppendLine("SELECT COUNT(Id) totalCount");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
var list = ExcuteSQL.GetDataSet(query.ToString(), false);
total = list.Tables.Count > 1 ? Convert.ToInt32(list.Tables[1].Rows[0]["totalCount"].ToString()) : 0;
return(ExcuteSQL.ConvertTo <ReporttblAccessUploadProcess>(list.Tables[0]));
}
public DataTable GetReportUploadProcessDetailExcel(string KeyWord, List <string> customerGroupId, string _fromdate, string _todate, string CardGroupID, string Actions, string UserID, string type = "", string eventstatus = "")
{
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(");
//tblCardProcess
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY [Date] desc) AS STT, (select convert(varchar(10), c.[Date], 103) + ' ' + left(convert(varchar(32), c.[Date], 108), 8)) as 'Thời gian', c.CardNumber as 'Mã thẻ', c.[UserIDofFinger] as 'User trên tb', cg.CardGroupName as 'Nhóm thẻ', c.Actions as 'Hành vi', cu.CustomerName as 'Chủ thẻ', '' as 'Nhóm KH',cu.[Address] as 'Địa chỉ', u.UserName as 'NV thực hiện', c.[SuccessControllerIDs] as 'Thiết bị', cu.[CustomerGroupID], c.[EventType], (select convert(varchar(10), c.[AccessDateExpire], 103)) AS 'Hết hạn'");
query.AppendLine("FROM dbo.[tblAccessUploadProcess] c WITH (NOLOCK)");
query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(nvarchar(255), cg.CardGroupID)");
query.AppendLine("LEFT JOIN tblUser u on c.UserID = CONVERT(nvarchar(255), u.UserID)");
//query.AppendLine("LEFT JOIN tblCard ca ON c.CardNumber = ca.CardNumber AND ca.IsDelete = 0");
query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(nvarchar(255),cu.CustomerID)");
query.AppendLine("WHERE 1 = 1");
query.AppendLine(string.Format("AND c.[Date] >= '{0}' AND c.[Date] <= '{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 c.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(Actions))
{
query.AppendLine(string.Format("AND c.[Actions] = '{0}'", Actions));
}
//User
if (!string.IsNullOrWhiteSpace(UserID))
{
var t = UserID.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
query.AppendLine("and c.UserID IN ( ");
foreach (var item in t)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
query.AppendLine(" )");
}
}
//Nhom KH
if (customerGroupId.Any())
{
query.AppendLine("AND cu.CustomerGroupID IN (");
var count = 0;
foreach (var item in customerGroupId)
{
count++;
query.AppendLine(string.Format("'{0}'{1}", item, count == customerGroupId.Count ? "" : ","));
}
query.AppendLine(")");
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
query.AppendLine(string.Format("AND ( c.[CardNumber] LIKE '%{0}%' OR c.[Description] LIKE '%{0}%' )", KeyWord));
}
if (!string.IsNullOrWhiteSpace(type))
{
query.AppendLine(string.Format("AND c.[EventType] LIKE '%{0}%'", type));
}
if (!string.IsNullOrWhiteSpace(eventstatus))
{
query.AppendLine(string.Format("AND c.[Description] LIKE '%{0}%'", eventstatus));
}
query.AppendLine(") as a");
return(ExcuteSQL.GetDataSet(query.ToString(), false).Tables[0]);
}
private void SetCard_Customer(tblCardSubmit cardsubmit, tblCustomerSubmit customersubmit, bool isNewCard, bool isNewCustomer)
{
var str = new StringBuilder();
//Thẻ
if (isNewCard)
{
str.AppendLine("INSERT INTO [dbo].[tblCard]([CardNo], [CardNumber], [CustomerID], [CardGroupID], [ImportDate], [ExpireDate], [IsLock], [IsDelete], [Plate1], [VehicleName1], [Plate2], [VehicleName2], [Plate3], [VehicleName3], [ViettelId], [ViettelType])");
str.AppendLine("VALUES (");
str.AppendLine(string.Format("'{0}'", cardsubmit.CardNo));
str.AppendLine(string.Format(", '{0}'", cardsubmit.CardNumber));
str.AppendLine(string.Format(", '{0}'", cardsubmit.CustomerID));
str.AppendLine(string.Format(", '{0}'", cardsubmit.CardGroupID));
str.AppendLine(",GETDATE()");
str.AppendLine(string.Format(", '{0}'", Convert.ToDateTime(cardsubmit.DtpDateExpired).ToString("yyyy/MM/dd")));
str.AppendLine(string.Format(", '{0}'", cardsubmit.CardInActive ? 1 : 0));
str.AppendLine(", 0");
str.AppendLine(string.Format(", '{0}'", cardsubmit.Plate1));
str.AppendLine(string.Format(", '{0}'", cardsubmit.VehicleName1));
str.AppendLine(string.Format(", '{0}'", cardsubmit.Plate2));
str.AppendLine(string.Format(", '{0}'", cardsubmit.VehicleName2));
str.AppendLine(string.Format(", '{0}'", cardsubmit.Plate3));
str.AppendLine(string.Format(", '{0}'", cardsubmit.VehicleName3));
str.AppendLine(string.Format(", '{0}'", cardsubmit.ViettelId));
str.AppendLine(string.Format(", '{0}'", cardsubmit.ViettelType));
str.AppendLine(")");
}
else
{
str.AppendLine("UPDATE [dbo].[tblCard] SET");
str.AppendLine(string.Format(" [CustomerID] = '{0}'", cardsubmit.CustomerID));
str.AppendLine(string.Format(",[IsLock] = '{0}'", cardsubmit.CardInActive ? 1 : 0));
if (!string.IsNullOrWhiteSpace(cardsubmit.CardNo))
{
str.AppendLine(string.Format(",[CardNo] = '{0}'", cardsubmit.CardNo));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.CardGroupID))
{
str.AppendLine(string.Format(",[CardGroupID] = '{0}'", cardsubmit.CardGroupID));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.DtpDateExpired))
{
str.AppendLine(string.Format(",[ExpireDate] = '{0}'", Convert.ToDateTime(cardsubmit.DtpDateExpired).ToString("yyyy/MM/dd")));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.Plate1))
{
str.AppendLine(string.Format(",[Plate1] = '{0}'", cardsubmit.Plate1));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.VehicleName1))
{
str.AppendLine(string.Format(",[VehicleName1] = N'{0}'", cardsubmit.VehicleName1));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.Plate2))
{
str.AppendLine(string.Format(",[Plate2] = '{0}'", cardsubmit.Plate2));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.VehicleName2))
{
str.AppendLine(string.Format(",[VehicleName2] = N'{0}'", cardsubmit.VehicleName2));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.Plate3))
{
str.AppendLine(string.Format(",[Plate3] = '{0}'", cardsubmit.Plate3));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.VehicleName3))
{
str.AppendLine(string.Format(",[VehicleName3] = N'{0}'", cardsubmit.VehicleName3));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.ViettelId))
{
str.AppendLine(string.Format(",[ViettelId] = '{0}'", cardsubmit.ViettelId));
}
if (!string.IsNullOrWhiteSpace(cardsubmit.ViettelType))
{
str.AppendLine(string.Format(",[ViettelType] = '{0}'", cardsubmit.ViettelType));
}
str.AppendLine(string.Format("WHERE CardNumber = '{0}'", cardsubmit.CardNumber));
}
//Khách hàng
if (customersubmit != null)
{
if (!string.IsNullOrWhiteSpace(customersubmit.CustomerCode))
{
if (isNewCustomer)
{
//var k = _tblCustomerService.GetAll().Count();
str.AppendLine("INSERT INTO [dbo].[tblCustomer]");
str.AppendLine("([CustomerID]");
str.AppendLine(", [CustomerName]");
str.AppendLine(", [CustomerCode]");
str.AppendLine(", [Address]");
str.AppendLine(", [Mobile]");
str.AppendLine(", [IDNumber]");
str.AppendLine(", [CustomerGroupID]");
str.AppendLine(", [EnableAccount]");
str.AppendLine(", [Inactive]");
str.AppendLine(", [UserIDofFinger], [Finger1], [Finger2], [DevPass], [AccessExpireDate])");
str.AppendLine(string.Format("VALUES('{0}', N'{1}','{2}', N'{3}', '{4}', '{5}', '{6}', 1 , 0, 0, '', '', '', '2099-12-31')", customersubmit.CustomerID, customersubmit.CustomerName, customersubmit.CustomerCode, customersubmit.Address, customersubmit.Mobile, customersubmit.IDNumber, customersubmit.CustomerGroupID));
}
else
{
str.AppendLine("UPDATE [dbo].[tblCustomer]");
str.AppendLine(string.Format("SET [CustomerName] = N'{0}'", customersubmit.CustomerName));
str.AppendLine(string.Format(",[Address] = N'{0}'", customersubmit.Address));
str.AppendLine(string.Format(",[Mobile] = N'{0}'", customersubmit.Mobile));
str.AppendLine(string.Format(",[IDNumber] = N'{0}'", customersubmit.IDNumber));
str.AppendLine(string.Format(",[CustomerGroupID] = '{0}'", customersubmit.CustomerGroupID));
str.AppendLine(string.Format("WHERE CONVERT(varchar(50),[CustomerID]) = '{0}'", customersubmit.CustomerID));
}
}
}
//
ExcuteSQL.Execute(str.ToString());
}
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
// };
//}
}
public List <tblCard> GetListCard(string KeyWord, string AnotherKey, string CardGroupIDs, string CustomerID, string CustomerGroupID, string _newexpire, bool chbEnableMinusActive)
{
var sb = new StringBuilder();
sb.AppendLine("SELECT ca.Cardnumber");
sb.AppendLine("from tblCard ca");
sb.AppendLine("LEFT JOIN dbo.tblCardGroup g ON ca.CardGroupID=g.CardGroupID");
sb.AppendLine("LEFT join tblCustomer cus on ca.CustomerID = CONVERT(varchar(255), cus.CustomerID)");
sb.AppendLine("where ca.IsDelete = 0 and ca.IsLock=0 and g.CardType=0");
//Neu so ngay gia han <0 va neu ko check thi ko cho gia han
if (chbEnableMinusActive == false)
{
sb.AppendLine(string.Format("and DATEDIFF(DAY, ca.[ExpireDate], '{0}') >=0 AND ca.[ExpireDate] <= '{0}'", _newexpire));
}
//Update theo filler
if (!string.IsNullOrWhiteSpace(KeyWord))
{
sb.AppendLine(" and (ca.CardNo LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.CardNumber LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.CustomerCode LIKE '%" + KeyWord + "%'");
sb.AppendLine(" or cus.CustomerName LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.Address LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or cus.AddressUnsign LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate1 LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate2 LIKE N'%" + KeyWord + "%'");
sb.AppendLine(" or ca.Plate3 LIKE N'%" + KeyWord + "%')");
}
if (!string.IsNullOrWhiteSpace(AnotherKey))
{
sb.AppendLine(" and (ca.CardNo LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.CardNumber LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.CustomerCode LIKE '%" + AnotherKey + "%'");
sb.AppendLine(" or cus.CustomerName LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.Address LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or cus.AddressUnsign LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate1 LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate2 LIKE N'%" + AnotherKey + "%'");
sb.AppendLine(" or ca.Plate3 LIKE N'%" + AnotherKey + "%')");
}
if (!string.IsNullOrWhiteSpace(CardGroupIDs))
{
sb.AppendLine(" and ca.CardGroupID = '" + CardGroupIDs + "'");
}
if (!string.IsNullOrWhiteSpace(AuthCardGroupIds))
{
var t = AuthCardGroupIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
if (t.Any())
{
var count = 0;
sb.AppendLine("AND ca.CardGroupID IN ( ");
foreach (var item in t)
{
count++;
sb.AppendLine(string.Format("'{0}'{1}", item, count == t.Length ? "" : ","));
}
sb.AppendLine(" )");
}
}
if (!string.IsNullOrWhiteSpace(CustomerID))
{
sb.AppendLine(" and ca.CustomerID = '" + CustomerID + "'");
}
if (!string.IsNullOrWhiteSpace(CustomerGroupID))
{
sb.AppendLine(" and cus.CustomerGroupID IN ('" + CustomerGroupID + "')");
}
var list = ExcuteSQL.GetDataSet(sb.ToString(), false);
return(ExcuteSQL.ConvertTo <tblCard>(list.Tables[0]));
}
public MessageReport SaveProcess(Employee emp, SelectListModelUploadSubmit obj)
{
//
var user = GetCurrentUser.GetUser();
//
var cardgroupid = "";
var userId = user != null ? user.Id : "";
var customerid = "";
var customergroupid = "";
var controllerid = obj.controllerid;
var controllerids = emp.ControllerIDs;
var expiredate = "2099/12/31";
var desc = obj.desc;
//Lấy nhóm thẻ
if (emp.CardNumber != "0")
{
var objCard = _tblCardService.GetCustomByCardNumber(emp.CardNumber);
if (objCard != null)
{
cardgroupid = objCard.CardGroupID;
expiredate = objCard.AccessExpireDate.ToString("yyyy/MM/dd");
customerid = objCard.CustomerID;
customergroupid = objCard.CustomerGroupID;
if (obj.isusenewdate)
{
_tblCardService.UpdateCard(obj.actionV, userId, emp.CardNumber, emp.ExpireDate, true);
}
}
}
if (emp.UserIDofFinger > 0)
{
//Lấy khách hàng
var objCustomer = _tblCustomerService.GetByFingerID(emp.UserIDofFinger);
if (objCustomer != null)
{
if (obj.isusenewdate)
{
_tblCustomerService.UpdateCustomer(emp.UserIDofFinger.ToString(), emp.ExpireDate, true);
}
}
}
var result = new MessageReport(false, "Có lỗi xảy ra");
try
{
var str = new StringBuilder();
str.AppendLine("INSERT INTO tblAccessUploadProcess(Date, CardNumber, UserIDofFinger, Actions, CardGroupID, UserID, AccessLevelID, CustomerID, CustomerGroupID, SuccessControllerIDs, TotalControllerIDs, EventType, AccessDateExpire, Description) VALUES (");
str.AppendLine("GETDATE()");
str.AppendLine(string.Format(", '{0}'", emp.CardNumber));
str.AppendLine(string.Format(", '{0}'", emp.UserIDofFinger));
str.AppendLine(string.Format(", '{0}'", obj.actionV));
str.AppendLine(string.Format(", '{0}'", cardgroupid));
str.AppendLine(string.Format(", '{0}'", userId));
str.AppendLine(string.Format(", '{0}'", emp.AccessLevelID));
str.AppendLine(string.Format(", '{0}'", customerid));
str.AppendLine(string.Format(", '{0}'", customergroupid));
str.AppendLine(string.Format(", '{0}'", controllerid));
str.AppendLine(string.Format(", '{0}'", controllerids));
str.AppendLine(string.Format(", '{0}'", obj.eventtype));
str.AppendLine(string.Format(", '{0}'", expiredate));
str.AppendLine(string.Format(", N'{0}'", desc));
str.AppendLine(")");
var t = ExcuteSQL.Execute(str.ToString());
result.isSuccess = t;
result.Message = "Thêm mới thành công";
if (obj.isusenewdate && emp.CardNumber != "0")
{
var str1 = new StringBuilder();
str1.AppendLine("INSERT INTO tblAccessUploadProcess(Date, CardNumber, UserIDofFinger, Actions, CardGroupID, UserID, AccessLevelID, CustomerID, CustomerGroupID, SuccessControllerIDs, TotalControllerIDs, EventType, AccessDateExpire, Description) VALUES (");
str1.AppendLine("GETDATE()");
str1.AppendLine(string.Format(", '{0}'", emp.CardNumber));
str1.AppendLine(string.Format(", '{0}'", emp.UserIDofFinger));
str1.AppendLine(string.Format(", '{0}'", "EXTEND"));
str1.AppendLine(string.Format(", '{0}'", cardgroupid));
str1.AppendLine(string.Format(", '{0}'", userId));
str1.AppendLine(string.Format(", '{0}'", emp.AccessLevelID));
str1.AppendLine(string.Format(", '{0}'", customerid));
str1.AppendLine(string.Format(", '{0}'", customergroupid));
str1.AppendLine(string.Format(", '{0}'", controllerid));
str1.AppendLine(string.Format(", '{0}'", controllerids));
str1.AppendLine(string.Format(", '{0}'", obj.eventtype));
str1.AppendLine(string.Format(", '{0}'", expiredate));
str1.AppendLine(string.Format(", N'{0}'", desc));
str1.AppendLine(")");
ExcuteSQL.Execute(str1.ToString());
}
}
catch (Exception ex)
{
result.isSuccess = false;
result.Message = ex.Message;
}
return(result);
}