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); }