public MessageReport DeleteById(string id, ref string cardnumber)
{
var result = new MessageReport();
result.Message = "Có lỗi xảy ra";
result.isSuccess = false;
try
{
var obj = GetById(id);
if (obj != null)
{
cardnumber = obj.CardNumber;
}
var str = string.Format("UPDATE tblCardEvent SET EventCode='2', IsDelete=1 WHERE Id = '{0}'", id);
SqlExQuery <tblCardEvent> .ExcuteNone(str);
result.Message = "Xóa thành công";
result.isSuccess = true;
}
catch (Exception ex)
{
result.Message = ex.Message;
result.isSuccess = false;
}
return(result);
}
public bool DeleteByIds(string lstId)
{
bool isSuccess = false;
try
{
var lstParentId = (from m in _tblCustomerGroupRepository.Table
where lstId.Contains(m.CustomerGroupID.ToString()) && !m.Inactive
select m.ParentID).Distinct().ToList();
string[] ids = lstId.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var id in ids)
{
var str = "Delete from tblCustomerGroup Where CustomerGroupID = '" + id + "'";
SqlExQuery <tblCustomerGroup> .ExcuteNone(str);
}
isSuccess = true;
if (isSuccess)
{
#region cập nhật số TT
if (lstParentId.Any())
{
foreach (var item2 in lstParentId)
{
var lst = (from m in _tblCustomerGroupRepository.Table
where !m.Inactive && m.ParentID == item2
orderby m.Ordering ascending
select m).ToList();
if (lst.Any())
{
int count = 0;
foreach (var item1 in lst)
{
count++;
item1.Ordering = count;
_tblCustomerGroupRepository.Update(item1);
Save();
}
}
}
}
#endregion
}
}
catch (Exception ex)
{
var message = ex.Message;
isSuccess = false;
}
return(isSuccess);
}
public MN_Card_Custom GetInfoByCardNumber(string cardnumber)
{
var query = new StringBuilder();
query.AppendLine("select c.CardNumber,c.CardNo,cu.CustomerName,cu.Mobile as CustomerMobile,cu.Address as CustomerAddress,cu.Avatar,cu.CustomerGroupID,cg.CustomerGroupName from tblCard c");
query.AppendLine("left join tblCustomer cu on c.CustomerID = CONVERT(varchar(255), cu.CustomerID)");
query.AppendLine("left join tblCustomerGroup cg on cu.CustomerGroupID = CONVERT(varchar(255), cg.CustomerGroupID)");
query.AppendLine(string.Format("where c.CardNumber = '{0}'", cardnumber));
return(SqlExQuery <MN_Card_Custom> .ExcuteQuery(query.ToString()).FirstOrDefault());
}
public List <BM_ResidentCustom> GetResidentPaging(string key, string group, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, * from (");
sb.AppendLine("select r.*,g.Name as GroupName from BM_Resident r");
sb.AppendLine("left join BM_ResidentGroup g on g.Id = r.ResidentGroupId");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%' OR r.Mobile like '%{0}%' OR r.Email like N'%{0}%')", key));
}
if (!string.IsNullOrEmpty(group) && !group.Equals("0"))
{
sb.AppendLine(string.Format(" AND (g.ParentId = '{0}' or g.Id = '{0}') ", group));
}
sb.AppendLine(") as a");
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_ResidentCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(r.Id) as TotalCount from BM_Resident r");
sb.AppendLine("left join BM_ResidentGroup g on g.Id = r.ResidentGroupId");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%' OR r.Mobile like '%{0}%' OR r.Email like N'%{0}%')", key));
}
if (!string.IsNullOrEmpty(group) && !group.Equals("0"))
{
sb.AppendLine(string.Format(" AND (g.ParentId = '{0}' or g.Id = '{0}') ", group));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public List <BM_ResidentCustom> GetMemberApartment(string ApartmentId)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("select m.RoleId,r.Id,r.Name,r.Mobile,r.Email");
sb.AppendLine("from BM_Apartment_Member m");
sb.AppendLine("left join BM_Resident r on r.Id = m.ResidentId");
sb.AppendLine(string.Format("where m.IsDeleted = 'False' and m.ApartmentId = '{0}'", ApartmentId));
var listData = SqlExQuery <BM_ResidentCustom> .ExcuteQuery(sb.ToString());
return(listData);
}
public List <tblCustomer> GetListCustmer(string key, string customerGr, string selectedId, int page, int pageSize, ref int totalItem)
{
var query = new StringBuilder();
query.AppendLine("Select* FROM (");
query.AppendLine("SELECT ROW_NUMBER () OVER ( Order by CustomerName desc) AS RowNumber, a.*,");
query.AppendLine("CONvert (varchar(50) ,CustomerID) as cs");
query.AppendLine(" FROM ( ");
query.AppendLine(" SELECT a.[CustomerID]");
query.AppendLine(" ,a.[CustomerCode]");
query.AppendLine(" ,a.[CustomerName]");
query.AppendLine(" ,a.[Address]");
query.AppendLine(" ,a.[IDNumber]");
query.AppendLine(" ,a.[Mobile]");
query.AppendLine(" ,a.[CustomerGroupID]");
query.AppendLine(" ,a.[Description]");
query.AppendLine(" ,a.[EnableAccount]");
query.AppendLine(" ,a.[Account]");
query.AppendLine(" ,a.[Password]");
query.AppendLine(" ,a.[Avatar]");
query.AppendLine(" ,a.[Inactive]");
query.AppendLine(" ,a.[SortOrder]");
query.AppendLine(" ,a.[CompartmentId]");
query.AppendLine(" ,a.[AccessLevelID]");
query.AppendLine(" ,a.[Finger1]");
query.AppendLine(" ,a.[Finger2]");
query.AppendLine(" ,a.[UserIDofFinger]");
query.AppendLine(" ,a.[DevPass]");
query.AppendLine(" ,a.[AccessExpireDate]");
query.AppendLine(" ,a.[ContractStartDate]");
query.AppendLine(" ,a.[ContractEndDate]");
query.AppendLine(" ,a.[AddressUnsign] FROM tblCustomer a");
query.AppendLine(")as a");
query.AppendLine(")as c1");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", page, pageSize));
var list = SqlExQuery <tblCustomer> .ExcuteQuery(query.ToString());
//tính tổng
query.Clear();
query.AppendLine("SELECT COUNT(*) TotalCount");
query.AppendLine("FROM tblCustomer c WITH(NOLOCK)");
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(query.ToString());
totalItem = _total != null ? _total.TotalCount : 0;
return(list);
}
public List <BM_Building_ServiceCustom> GetServiceApartment(string ApartmentId)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("select r.Id, r.Name, m.SchedulePay, m.ScheduleType, m.Price, ");
sb.AppendLine("CONVERT(varchar(50),FORMAT(m.StartDate,'dd/MM/yyyy')) as DateStart,");
sb.AppendLine("CONVERT(varchar(50),FORMAT(m.EndDate,'dd/MM/yyyy')) as DateEnd");
sb.AppendLine("from BM_Apartment_Service m");
sb.AppendLine("left join BM_Building_Service r on r.Id = m.ServiceId");
sb.AppendLine(string.Format("where m.IsDeleted = 'False' and m.ApartmentId = '{0}'", ApartmentId));
var listData = SqlExQuery <BM_Building_ServiceCustom> .ExcuteQuery(sb.ToString());
return(listData);
}
public List <BM_ApartmentCustom> GetApartmentPaging(string key, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, a.*,");
sb.AppendLine("b.Name AS BuildingName,f.Name AS FloorName ");
sb.AppendLine("from BM_Apartment a");
sb.AppendLine("left join BM_Building b on b.Id = a.BuildingId");
sb.AppendLine("left join BM_Floor f on f.Id = a.FloorId");
sb.AppendLine("where a.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (a.Name like N'%{0}%')", key));
}
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_ApartmentCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(a.Id) as TotalCount");
sb.AppendLine("from BM_Apartment a");
sb.AppendLine("left join BM_Building b on b.Id = a.BuildingId");
sb.AppendLine("left join BM_Floor f on f.Id = a.FloorId");
sb.AppendLine("where a.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (a.Name like N'%{0}%')", key));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public bool DeleteByIds(string lstId)
{
bool isSuccess = false;
try
{
string[] ids = lstId.Split(new Char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var id in ids)
{
var objDelete = (from m in _MenuFunctionRepository.Table
where m.Breadcrumb.Contains(id)
select m).ToList();
if (objDelete.Any())
{
foreach (var item in objDelete)
{
//if (!item.Active)
//{
//}
//_MenuFunctionRepository.Delete(item);
//Save();
var str = "Delete from MenuFunction Where Id = " + item.Id;
SqlExQuery <MenuFunction> .ExcuteNone(str);
//Update BreadCrumb
UpdateBreadCrumb(item, "add");
}
}
}
isSuccess = true;
}
catch (Exception ex)
{
var message = ex.Message;
isSuccess = false;
}
return(isSuccess);
}
public List <BM_Building_ServiceCustom> GetResidentPaging(string key, int pageNumber, int pageSize, ref int total)
{
//Lấy danh sách
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM(");
sb.AppendLine("select ROW_NUMBER() OVER(ORDER BY a.Name asc) AS RowNumber, * from (");
sb.AppendLine("select r.* from BM_Building_Service r");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%')", key));
}
sb.AppendLine(") as a");
sb.AppendLine(") as b");
sb.AppendLine(string.Format("WHERE RowNumber BETWEEN(({0}-1) * {1}+1) AND({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_Building_ServiceCustom> .ExcuteQuery(sb.ToString());
//Tính tổng
sb.Clear();
sb.AppendLine("select count(r.Id) as TotalCount from BM_Building_Service r");
sb.AppendLine("where r.IsDeleted = 'False'");
if (!string.IsNullOrWhiteSpace(key))
{
sb.AppendLine(string.Format(" AND (r.Name like N'%{0}%')", key));
}
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(sb.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public static void Write(MessageReport report, User currentuser, string objId, string objname, string classname, string appcode, string actions)
{
var computername = Common.GetComputerName(HttpContext.Current.Request.UserHostAddress);
objname = objname.Replace("'", "");
var t = new tblLog();
t.LogID = Guid.NewGuid();
t.Actions = actions;
t.AppCode = appcode;
t.ComputerName = computername;
t.Date = DateTime.Now;
t.Description = string.Format("Action: {0} - Name: {1} - Id: {2}", report.Message, objname, objId);
t.IPAddress = computername;
t.ObjectName = objname;
t.SubSystemCode = classname;
t.UserName = currentuser.Username;
var str = new StringBuilder();
str.Append("INSERT INTO tblLog (LogID, Date, UserName, AppCode, SubSystemCode, ObjectName, Actions, Description, ComputerName)");
str.AppendLine("VALUES (");
str.AppendLine(string.Format("'{0}'", Guid.NewGuid()));
str.AppendLine(string.Format(", '{0}'", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")));
str.AppendLine(string.Format(", '{0}'", t.UserName));
str.AppendLine(string.Format(", '{0}'", t.AppCode));
str.AppendLine(string.Format(", '{0}'", t.SubSystemCode));
str.AppendLine(string.Format(", N'{0}'", t.ObjectName));
str.AppendLine(string.Format(", N'{0}'", t.Actions));
str.AppendLine(string.Format(", N'{0}'", t.Description));
str.AppendLine(string.Format(", '{0}'", t.ComputerName));
str.AppendLine(")");
SqlExQuery <tblLog> .ExcuteNone(str.ToString());
}
public MessageReport Create(MenuFunctionConfig obj)
{
var re = new MessageReport();
re.Message = "Error";
re.isSuccess = false;
try
{
var str = string.Format("INSERT INTO MenuFunctionConfig (Id, MenuFunctionId) VALUES ('{0}', '{1}')", obj.Id, obj.MenuFunctionId);
SqlExQuery <MenuFunctionConfig> .ExcuteNone(str);
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 DeleteAll()
{
var str = "Delete from MenuFunctionConfig";
var i = SqlExQuery <MenuFunctionConfig> .ExcuteNone(str);
}
private void SaveCardProcess(tblCardSubmit obj, string action, string userid)
{
var str = string.Format("insert into tblCardProcess(Date, CardNumber, Actions, CardGroupID, UserID, CustomerID) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), obj.CardNumber, action, obj.CardGroupID, userid, obj.CustomerID);
SqlExQuery <tblCardProcess> .ExcuteNone(str);
}
public List <BM_Apartment_ReceiptView> GetAllPagingByFirstTSQL(string key, string buildingIdSearch, string floorIdSearch, string apartmentIdSearch, string residentIdSearch, string typeSearch, string statusSearch, string userSearch, string userCreatedIdSearch, string fromDatePaid, string toDatePaid, int pageNumber, int pageSize, ref int total)
{
//if (!string.IsNullOrEmpty(fromDatePaid))
//{
// fromDatePaid = Convert.ToDateTime(fromDatePaid).ToString("yyyy/MM/dd HH:mm:ss");
//}
//if (!string.IsNullOrEmpty(toDatePaid))
//{
// toDatePaid = Convert.ToDateTime(toDatePaid).ToString("yyyy/MM/dd HH:mm:ss");
//}
//if (!string.IsNullOrEmpty(key))
// query.AppendLine(string.Format("AND (LP.CardNo LIKE '%{0}%' OR LP.CardNumber LIKE '%{0}%' OR U.Name LIKE '%{0}%' OR LC.ControllerName LIKE '%{0}%')", KeyWord));
////search toDate - fromdate
//if (!string.IsNullOrEmpty(fromDatePaid) && !string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated >= '{0}' AND LP.DateCreated <= '{1}'", fromDatePaid, toDatePaid));
//}
//else if (!string.IsNullOrEmpty(fromDatePaid) && string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated >= '{0}'", fromDatePaid));
//}
//else if (string.IsNullOrEmpty(fromDatePaid) && !string.IsNullOrEmpty(toDatePaid))
//{
// query.AppendLine(string.Format("AND LP.DateCreated <= '{0}'", toDatePaid));
//}
var query = new StringBuilder();
//danh sách
query.AppendLine("SELECT * FROM(");
query.AppendLine("SELECT ROW_NUMBER() OVER(ORDER BY a.DateCreated desc) as RowNumber,a.*");
query.AppendLine("FROM(");
query.AppendLine("SELECT AR.* from BM_Apartment_Receipt AR");
//query.AppendLine("inner join [User] U on U.Id = LP.UserId ");
//query.AppendLine("left join [tblLockerController] LC on LC.Id = LP.ControllerID ");
query.AppendLine("where 1=1");
query.AppendLine(") AS a");
query.AppendLine(") AS b");
query.AppendLine(string.Format("WHERE RowNumber BETWEEN (({0}-1) * {1} + 1) AND ({0} * {1})", pageNumber, pageSize));
var listData = SqlExQuery <BM_Apartment_ReceiptView> .ExcuteQuery(query.ToString());
//Tính tổng
query.Clear();
query.AppendLine("SELECT COUNT(*) TotalCount");
query.AppendLine("FROM BM_Apartment_Receipt AR WITH(NOLOCK)");
//query.AppendLine("LEFT JOIN tblCardGroup cg ON c.CardGroupID = CONVERT(varchar(50), cg.CardGroupID)");
//query.AppendLine("LEFT JOIN tblCustomer cu ON c.CustomerID = CONVERT(varchar(50), cu.CustomerID)");
//query.AppendLine("LEFT JOIN tblCustomerGroup cug ON cu.CustomerGroupID = CONVERT(varchar(50), cug.CustomerGroupID)");
query.AppendLine("WHERE 1=1");
var _total = SqlExQuery <TotalPaging> .ExcuteQueryFirst(query.ToString());
total = _total != null ? _total.TotalCount : 0;
return(listData);
}
public SelectListModel CountEventEveryDay(string fromdate, string todate)
{
//Model send back
var model = new SelectListModel();
//
var fdate = "";
var tdate = "";
int inNumber = 0;
int outNumber = 0;
//Check using Loop
var check = _tblLaneService.GetAllActive().FirstOrDefault(n => n.IsLoop == true);
//Date time
if (!string.IsNullOrWhiteSpace(fromdate) && !string.IsNullOrWhiteSpace(todate))
{
fdate = Convert.ToDateTime(fromdate).ToString("yyyy/MM/dd 00:00:00");
tdate = Convert.ToDateTime(todate).AddDays(1).ToString("yyyy/MM/dd 00:00:00");
}
else
{
fdate = DateTime.Now.ToString("yyyy/MM/dd 00:00:00");
tdate = DateTime.Now.AddDays(1).ToString("yyyy/MM/dd 00:00:00");
}
//IN
var sqlCardEventIN = new StringBuilder();
sqlCardEventIN.AppendLine("SELECT * from tblCardEvent WITH (NOLOCK)");
sqlCardEventIN.AppendLine("WHERE IsDelete = 0");
sqlCardEventIN.AppendLine(string.Format("AND ( DateTimeIn >= '{0}' AND DateTimeIn < '{1}' )", fdate, tdate));
var cardeIN = SqlExQuery <tblCardEvent> .ExcuteQuery(sqlCardEventIN.ToString()).Count;
inNumber += cardeIN;
if (check != null)
{
var sqlLoopEventIN = new StringBuilder();
sqlLoopEventIN.AppendLine("SELECT * from tblLoopEvent WITH (NOLOCK)");
sqlLoopEventIN.AppendLine("WHERE IsDelete = 0 AND EventCode = '2'");
sqlLoopEventIN.AppendLine(string.Format("AND ( DateTimeIn >= '{0}' AND DateTimeIn < '{1}' )", fdate, tdate));
var loopeIN = SqlExQuery <tblLoopEvent> .ExcuteQuery(sqlLoopEventIN.ToString()).Count;
inNumber += loopeIN;
}
//OUT
var sqlCardEventOUT = new StringBuilder();
sqlCardEventOUT.AppendLine("SELECT * from tblCardEvent WITH (NOLOCK)");
sqlCardEventOUT.AppendLine("WHERE IsDelete = 0 AND EventCode = '2'");
sqlCardEventOUT.AppendLine(string.Format("AND ( DateTimeOut >= '{0}' AND DateTimeOut < '{1}' )", fdate, tdate));
var cardeOUT = SqlExQuery <tblCardEvent> .ExcuteQuery(sqlCardEventOUT.ToString()).Count;
outNumber += cardeOUT;
if (check != null)
{
var sqlLoopEventOUT = new StringBuilder();
sqlLoopEventOUT.AppendLine("SELECT * from tblLoopEvent WITH (NOLOCK)");
sqlLoopEventOUT.AppendLine("where IsDelete = 0 AND EventCode = '2'");
sqlLoopEventOUT.AppendLine(string.Format("AND ( DateTimeOut >= '{0}' AND DateTimeOut < '{1}' )", fdate, tdate));
var loopeOUT = SqlExQuery <tblLoopEvent> .ExcuteQuery(sqlLoopEventOUT.ToString()).Count;
outNumber += loopeOUT;
}
model.ItemValue = inNumber.ToString();
model.ItemText = outNumber.ToString();
return(model);
}
