public List <UserBlockModel> APIGetBlockList(ForApiResponse query) { string blockSql = string.Format(@"SELECT sb.BlockId ,sb.BlockName as BlockName, sb.BlockName+' ('+c.CampName+')' as 'BlockFullName', UG.DistrictCode ,UG.UpazilaCode ,UG.UnionCode ,UG.VillageCode , SB.CenterId, UG.CampId FROM UserGeolocation UG INNER JOIN BlockInfo sb on (UG.DistrictCode = sb.DistrictCode OR UG.DistrictCode = 0) AND (UG.UpazilaCode = sb.UpazilaCode OR sb.UpazilaCode = 0) AND (UG.UnionCode = sb.UnionCode OR UG.UnionCode = 0) AND (UG.VillageCode = sb.VillageCode OR UG.VillageCode = 0) LEFT JOIN CampInfo C ON sb.CampId = C.CampId WHERE UG.UserId = {0}", query.UserId); return(_unitOfWork.GenericRepositories <UserBlockModel>().GetRecordSet(blockSql).ToList()); }
public int APIGetTotalRecord(ForApiResponse apr, SpinnerRequest req, DbContext dbContext = null) { string sql = ""; if (req.Where == "" || req.Where == null) { sql = $@"SELECT COUNT({req.ValueText}) as Total FROM {req.ModelName} "; } else { sql = $@"SELECT COUNT({req.ValueText}) as Total FROM {req.ModelName} WHERE {req.Where} "; } return(db.Get <TotalRecord>(sql, dbContext).FirstOrDefault().Total); }
public List <SpinnerValue> APIGetSpinnerData(ForApiResponse apr, SpinnerRequest req, DbContext dbContext = null) { string sql = ""; if (req.Where == "" || req.Where == null) { sql = $@"SELECT ROW_NUMBER() over ( order by {req.ValueText}) as RowNo,{req.DisplayText} as 'DisplayText', CAST({req.ValueText} as nvarchar) as 'ValueText' FROM {req.ModelName} Order by RowNo asc OFFSET ({apr.PageSize}*({apr.PageNo}-1)) ROWS FETCH NEXT {apr.PageSize} ROWS ONLY"; } else { sql = $@"SELECT ROW_NUMBER() over ( order by {req.ValueText}) as RowNo,{req.DisplayText} as 'DisplayText', CAST({req.ValueText} as nvarchar) as 'ValueText' FROM {req.ModelName} WHERE {req.Where} Order by RowNo asc OFFSET ({apr.PageSize}*({apr.PageNo}-1)) ROWS FETCH NEXT {apr.PageSize} ROWS ONLY"; } var list = db.Get <SpinnerValue>(sql, dbContext).ToList(); return(list); }
public int APIGetTotalRecord(ForApiResponse apr, SpinnerRequest req) { string sql = ""; if (req.where == "" || req.where == null) { sql = $@"SELECT COUNT({req.valueText}) as Total FROM {req.modelName} "; } else { sql = $@"SELECT COUNT({req.valueText}) as Total FROM {req.modelName} WHERE {req.where} "; } var total = unitOfWork.context.Database.SqlQuery <int>(sql).FirstOrDefault(); return(total); }
public List <SpinnerValue> APIGetSpinnerData(ForApiResponse apr, SpinnerRequest req) { string sql = ""; if (req.where == "" || req.where == null) { sql = $@"SELECT ROW_NUMBER() over ( order by {req.valueText}) as RowNo,{req.displayText} as 'displayText', {req.valueText} as 'valueText' FROM {req.modelName} Order by RowNo asc OFFSET ({apr.PageSize}*({apr.PageNo}-1)) ROWS FETCH NEXT {apr.PageSize} ROWS ONLY"; } else { sql = $@"SELECT ROW_NUMBER() over ( order by {req.valueText}) as RowNo,{req.displayText} as 'displayText', {req.valueText} as 'valueText' FROM {req.modelName} WHERE {req.where} Order by RowNo asc OFFSET ({apr.PageSize}*({apr.PageNo}-1)) ROWS FETCH NEXT {apr.PageSize} ROWS ONLY"; } var list = unitOfWork.GenericRepositories <SpinnerValue>().GetRecordSet(sql).ToList(); return(list); }
public ApiResponse <Object> GetPacketByUserId(ApiPacketRequest apr) { ApiResponse <Object> returnObject = new ApiResponse <Object>(); List <object> obj = new List <object>(); switch (apr.TableName) { case "Login": { UsersLogin query = JsonConvert.DeserializeObject <UsersLogin>(apr.ApiPacket.Packet.ToString()); var result = new AspNetUsers().Login(query); ApiPacket <LoginResponse> fp = new ApiPacket <LoginResponse>() { Packet = new LoginResponse() }; if (result != null) { returnObject.ApiPacket.Packet = new LoginResponse() { UserInfo = result.UserInfo /*, BlockInfo = result.BlockInfo ServicePoint = result.ServicePoint, GeoLocation = result.GeoLocation*/ }; } ApiResponse <LoginResponse> response = new ApiResponse <LoginResponse>() { Success = result != null ? true : false, ApiPacket = fp, PageNo = apr.PageNo, PageSize = apr.PageSize, TotalRecord = 0, Message = result != null ? "Login successfully." : "Invalid Username or Password", Status = result != null ? 200 : 400 }; Tools.CopyClass(returnObject, response); break; //return returnObject; } case "UserGeo": { var result = new PermittedGeoLocation().GetPermittedGeoLocation(new PermittedGeoLocationRM { UserId = apr.UserId });; ApiPacket <PermittedGeoLocationViewModels> fp = new ApiPacket <PermittedGeoLocationViewModels>() { Packet = new PermittedGeoLocationViewModels() }; if (result != null) { returnObject.ApiPacket.Packet = new PermittedGeoLocationViewModels() { District = result.District, Upazila = result.Upazila, Unions = result.Unions, Village = result.Village, CenterInfo = result.CenterInfo, CampInfo = result.CampInfo }; } ApiResponse <PermittedGeoLocationViewModels> response = new ApiResponse <PermittedGeoLocationViewModels>() { Success = result != null ? true : false, ApiPacket = fp, PageNo = apr.PageNo, PageSize = apr.PageSize, TotalRecord = 0, Message = result != null ? "Record Fetched successfully." : "Record Fetched fail.", Status = result != null ? 200 : 400 }; Tools.CopyClass(returnObject, response); break; } case "UniqueId": { var requestObject = JsonConvert.DeserializeObject <RequestForUniqueId>(apr.ApiPacket.Packet.ToString()); var result = new UniqueId().SaveAndGet(requestObject ?? new RequestForUniqueId(), apr.UserId); returnObject = GetApiResponse(apr, result.ToList <object>(), result.Count); break; } case "SpinnerData": { var spinnerRequest = JsonConvert.DeserializeObject <SpinnerRequest>(apr.ApiPacket.Packet.ToString()); SpinnerData spinner = new SpinnerData(); var result = spinner.APIGetSpinnerData(QueryFromAPIPacketRequest(apr), spinnerRequest); obj.AddRange(result); returnObject = GetApiResponse(apr, obj, apr.TotalRecord != 0 ? spinner.APIGetTotalRecord(QueryFromAPIPacketRequest(apr), spinnerRequest) : apr.TotalRecord); break; } case "BlockInfo": { var requestObject = new ForApiResponse() { UserId = apr.UserId }; BlockInfo blockinfo = new BlockInfo(); var result = blockinfo.APIGetBlockList(requestObject); obj.AddRange(result); returnObject = GetApiResponse(apr, result.ToList <object>(), result.Count); break; } default: { ApiResponse <object> response = new ApiResponse <object>() { Success = false, ApiPacket = null, PageNo = apr.PageNo, PageSize = apr.PageSize, TotalRecord = 0, Message = "Table is not registered to System", Status = 400 }; return(response); } } return(returnObject); }