public HttpResponseMessage getStockCads(DTOFilterGetStockCardRequest request) { using (var db = new CRMEntities()) { var filter = request.getFilter(); filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var res = db.stockcard.SqlQuery(querySql).ToList(); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo, querySql) , "application/json")); } }
public HttpResponseMessage getTaskStateMatches(DTOGetTSMFilter request) { using (var db = new CRMEntities()) { var filter = request.getFilter(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; var res = db.taskstatematches.SqlQuery(querySql).ToList(); var taskids = res.Select(s => s.taskid).Distinct().ToList(); var tasks = db.task.Where(t => taskids.Contains(t.taskid)).ToList(); var stateids = res.Select(s => s.stateid).Distinct().ToList(); var states = db.taskstatepool.Where(tsp => stateids.Contains(tsp.taskstateid)).ToList(); res.ForEach(r => { r.task = tasks.Where(t => t.taskid == r.taskid).FirstOrDefault(); r.taskstatepool = states.Where(t => t.taskstateid == r.stateid).FirstOrDefault(); }); return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo) , "application/json")); } }
public HttpResponseMessage getSL(DTOFilterGetSLRequest request) { // SL hesaplama sayfasının doldurulması (Hüseyin KOZ) using (var db = new KOCSAMADLSEntities()) { var filter = request.getFilter(); filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var res = db.SL.SqlQuery(querySql).ToList(); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Select(r => r.toDTO()).ToList(), paginginfo, querySql) , "application/json")); } }
public HttpResponseMessage getCustomers(DTOFilterGetCustomerRequest request) { using (var db = new CRMEntities()) { var filter = request.getFilter(); //filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); var countSql = filter.getCountSQL(); var querySql = filter.getFilterSQL(); var res = db.customer.SqlQuery(querySql).ToList(); var blockIds = res.Select(b => b.blockid).Distinct().ToList(); var blocks = db.block.Where(b => blockIds.Contains(b.blockid)).ToList(); var issIds = res.Where(i => i.iss != null).Select(i => i.iss).Distinct().ToList(); var isss = db.issStatus.Where(i => issIds.Contains(i.id)).ToList(); var cststatusIds = res.Where(c => c.customerstatus != null).Select(c => c.customerstatus).Distinct().ToList(); var cststatus = db.customer_status.Where(c => cststatusIds.Contains(c.ID)).ToList(); var netstatusIds = res.Where(c => c.netstatu != null).Select(c => c.netstatu).Distinct().ToList(); var netstatus = db.netStatus.Where(c => netstatusIds.Contains(c.id)).ToList(); var tvStatusIds = res.Where(c => c.tvstatu != null).Select(c => c.tvstatu).Distinct().ToList(); var tvstatus = db.TvKullanımıStatus.Where(c => tvStatusIds.Contains(c.id)).ToList(); var telStatusIds = res.Where(c => c.telstatu != null).Select(c => c.telstatu).Distinct().ToList(); var telstatus = db.telStatus.Where(c => telStatusIds.Contains(c.id)).ToList(); var ttvStatusIds = res.Where(c => c.turkcellTv != null).Select(c => c.turkcellTv).Distinct().ToList(); var ttvstatus = db.TurkcellTVStatus.Where(c => ttvStatusIds.Contains(c.id)).ToList(); var gsmStatusIds = res.Where(c => c.gsmstatu != null).Select(c => c.gsmstatu).Distinct().ToList(); var gsmstatus = db.gsmKullanımıStatus.Where(c => gsmStatusIds.Contains(c.id)).ToList(); res.ForEach(r => { r.block = blocks.Where(b => b.blockid == r.blockid).FirstOrDefault(); r.issStatus = isss.Where(b => b.id == r.iss).FirstOrDefault(); r.customer_status = cststatus.Where(b => b.ID == r.customerstatus).FirstOrDefault(); r.netStatus = netstatus.Where(b => b.id == r.netstatu).FirstOrDefault(); r.telStatus = telstatus.Where(b => b.id == r.telstatu).FirstOrDefault(); r.TvKullanımıStatus = tvstatus.Where(b => b.id == r.tvstatu).FirstOrDefault(); r.TurkcellTVStatus = ttvstatus.Where(b => b.id == r.turkcellTv).FirstOrDefault(); r.gsmKullanımıStatus = gsmstatus.Where(b => b.id == r.gsmstatu).FirstOrDefault(); }); return(Request.CreateResponse(HttpStatusCode.OK, new DTOResponse(DTOResponseError.NoError(), res.Where(r => r.deleted != true).OrderByDescending(r => r.deleted).Select(r => r.toDTO()).ToList()) , "application/json")); } }
public HttpResponseMessage getTaskPersonelAtama(DTOFilterGetAtamaRequest request) { using (var db = new KOCSAMADLSEntities()) { var filter = request.getFilter(); //filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var res = db.atama.SqlQuery(querySql).ToList(); var offpersonelcall = res.Select(s => s.offpersonel).Distinct().ToList(); var offpersonels = db.personel.Where(s => offpersonelcall.Contains(s.personelid)).ToList(); var appointpersonelcall = res.Select(s => s.appointedpersonel).Distinct().ToList(); var appointpersonels = db.personel.Where(s => appointpersonelcall.Contains(s.personelid)).ToList(); var closedtaskcall = res.Select(s => s.closedtask).Distinct().ToList(); var closedtasks = db.task.Where(s => closedtaskcall.Contains(s.taskid)).ToList(); var formedtaskcall = res.Select(s => s.formedtask).Distinct().ToList(); var formedtasks = db.task.Where(s => formedtaskcall.Contains(s.taskid)).ToList(); var closedtasktypecall = res.Select(s => s.closedtasktype).Distinct().ToList(); var closedtasktypes = db.tasktypes.Where(s => closedtasktypecall.Contains(s.TaskTypeId)).ToList(); var formedtasktypecall = res.Select(s => s.formedtasktype).Distinct().ToList(); var formedtasktypes = db.tasktypes.Where(s => formedtasktypecall.Contains(s.TaskTypeId)).ToList(); res.ForEach(s => { s.adsl_personeloff = offpersonels.Where(i => i.personelid == s.offpersonel).FirstOrDefault(); s.adsl_personelappoint = appointpersonels.Where(i => i.personelid == s.appointedpersonel).FirstOrDefault(); s.adsl_taskclosed = closedtasks.Where(p => p.taskid == s.closedtask).FirstOrDefault(); s.adsl_taskformed = formedtasks.Where(p => p.taskid == s.formedtask).FirstOrDefault(); s.adsl_tasktypesclosed = closedtasktypes.Where(p => p.TaskTypeId == s.closedtasktype).FirstOrDefault(); s.adsl_tasktypesformed = formedtasktypes.Where(p => p.TaskTypeId == s.formedtasktype).FirstOrDefault(); }); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Select(r => r.toDTO()).ToList(), paginginfo, querySql) , "application/json")); //denenecek } }
public HttpResponseMessage saveDocument(DTOdocument request) { using (var db = new KOCSAMADLSEntities()) { var ddoc = db.document.Where(d => d.documentid == request.documentid).FirstOrDefault(); ddoc.documentname = request.documentname; ddoc.documentdescription = request.documentdescription; ddoc.lastupdated = DateTime.Now; ddoc.updatedby = KOCAuthorization.KOCAuthorizeAttribute.getCurrentUser().userId; db.SaveChanges(); return(Request.CreateResponse(HttpStatusCode.OK, DTOResponseError.NoError(), "application/json")); } }
public HttpResponseMessage getPersonels(DTOFilterGetPersonelRequest request) { using (var db = new KOCSAMADLSEntities()) { var filter = request.getFilter(); filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var res = db.personel.SqlQuery(querySql).ToList(); var ilIds = res.Select(s => s.ilKimlikNo).Distinct().ToList(); var ils = db.il.Where(s => ilIds.Contains(s.kimlikNo)).ToList(); var ilceIds = res.Select(s => s.ilceKimlikNo).Distinct().ToList(); var ilces = db.ilce.Where(s => ilceIds.Contains(s.kimlikNo)).ToList(); var relatedPersonelIds = res.Select(s => s.relatedpersonelid).Distinct().ToList(); var relatedPersonels = db.personel.Where(s => relatedPersonelIds.Contains(s.personelid)).ToList(); var kurulumPersonelIds = res.Select(s => s.kurulumpersonelid).Distinct().ToList(); var kurulumPersonels = db.personel.Where(s => kurulumPersonelIds.Contains(s.personelid)).ToList(); res.ForEach(s => { s.ilce = ilces.Where(i => i.kimlikNo == s.ilceKimlikNo).FirstOrDefault(); s.il = ils.Where(i => i.kimlikNo == s.ilKimlikNo).FirstOrDefault(); s.relatedpersonel = relatedPersonels.Where(p => p.personelid == s.relatedpersonelid).FirstOrDefault(); s.kurulumpersonel = kurulumPersonels.Where(p => p.personelid == s.kurulumpersonelid).FirstOrDefault(); }); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo, querySql) , "application/json")); } }
public HttpResponseMessage getDocuments(DTOGetDocumentFilter request) { using (var db = new KOCSAMADLSEntities()) { var filter = request.getFilter(); var countSql = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var res = db.document.SqlQuery(querySql).ToList(); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(r => r.toDTO()).ToList(), paginginfo) , "application/json")); } }
public HttpResponseMessage getSites(DTOGetSiteFilter request) { using (var db = new CRMEntities()) { db.Configuration.AutoDetectChangesEnabled = false; db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var filter = request.getFilter(); filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); string querySQL = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSQL = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSQL).First(); var res = db.site.SqlQuery(querySQL).ToList(); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Select(s => s.toDTO()).ToList(), paginginfo, querySQL), "application/json")); } }
public HttpResponseMessage getBlocks(DTOGetBlockFilter request) { using (var db = new CRMEntities()) { db.Configuration.AutoDetectChangesEnabled = false; db.Configuration.LazyLoadingEnabled = false; db.Configuration.ProxyCreationEnabled = false; var filter = request.getFilter(); filter.fieldFilters.Add(new DTOFieldFilter { fieldName = "deleted", value = 0, op = 2 }); string querySQL = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSQL = filter.getCountSQL(); var rowCount = db.Database.SqlQuery <int>(countSQL).First(); var res = db.block.SqlQuery(querySQL).ToList(); var siteids = res.Select(s => s.siteid).Distinct().ToList(); var sites = db.site.Where(s => siteids.Contains(s.siteid)).ToList(); var personelids = res.Select(s => s.salesrepresentative).Distinct().ToList(); var personels = db.personel.Where(p => personelids.Contains(p.personelid)).ToList(); res.ForEach(r => { r.site = sites.Where(s => s.siteid == r.siteid).FirstOrDefault(); r.salespersonel = personels.Where(p => p.personelid == r.salesrepresentative).FirstOrDefault(); }); DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Select(s => s.toDTO()).ToList(), paginginfo, querySQL), "application/json")); } }
public HttpResponseMessage getStock(DTOGetStockMovementRequest request) { // getStockMovements --> fromobject (id) ve toobject (id) olarak geldiğinde isim kontrolü ile çakışma olduğu için oluşturuldu var user = KOCAuthorizeAttribute.getCurrentUser(); using (var db = new KOCSAMADLSEntities()) { if (request.fromobject != null && request.fromobject.value == null && request.toobject.value == null) { request.fromobject.value = ""; } var filter = request.getFilter(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSql = filter.getCountSQL(); var performance = new DTOQueryPerformance(); var perf = Stopwatch.StartNew(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); performance.CountSQLDuration = perf.Elapsed; perf.Restart(); var res = db.stockmovement.SqlQuery(querySql).ToList(); performance.QuerSQLyDuration = perf.Elapsed; perf.Restart(); var fromObjectIds = res.Select(s => s.fromobject).Distinct().ToList(); var fromPersonels = db.personel.Where(p => fromObjectIds.Contains(p.personelid)).ToList(); var fromCustomers = db.customer.Where(c => fromObjectIds.Contains(c.customerid)).ToList(); var toObjectIds = res.Select(s => s.toobject).Distinct().ToList(); var toPersonels = db.personel.Where(p => toObjectIds.Contains(p.personelid)).ToList(); var toCustomers = db.customer.Where(c => toObjectIds.Contains(c.customerid)).ToList(); var stockcardids = res.Select(s => s.stockcardid).Distinct().ToList(); var stockcards = db.stockcard.Where(s => stockcardids.Contains(s.stockid)).ToList(); res.ForEach(r => { r.frompersonel = fromPersonels.Where(p => p.personelid == r.fromobject).FirstOrDefault(); if (r.frompersonel == null) { r.fromcustomer = fromCustomers.Where(c => c.customerid == r.fromobject).FirstOrDefault(); } r.topersonel = toPersonels.Where(p => p.personelid == r.toobject).FirstOrDefault(); if (r.topersonel == null) { r.tocustomer = toCustomers.Where(c => c.customerid == r.toobject).FirstOrDefault(); } r.stockcard = stockcards.Where(s => s.stockid == r.stockcardid).FirstOrDefault(); }); performance.LookupDuration = perf.Elapsed; DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(s => s.toDTO()).ToList(), paginginfo, querySql, performance) , "application/json")); } }
public HttpResponseMessage getStockMovements(DTOGetStockMovementRequest request) { var user = KOCAuthorizeAttribute.getCurrentUser(); using (var db = new KOCSAMADLSEntities()) { if (request.fromobject != null && request.fromobject.value == null && request.toobject.value == null) { request.fromobject.value = ""; } var filter = request.getFilter(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSql = filter.getCountSQL(); if (querySql.Contains("fromobject") || querySql.Contains("toobject")) { var sqlPartitions = querySql.Split(new string[] { "paging as (", ") SELECT *" }, StringSplitOptions.RemoveEmptyEntries); var pagingWhereClauses = sqlPartitions[1].Split(new string[] { "stockmovement WHERE", ") AND" }, StringSplitOptions.RemoveEmptyEntries).ToList(); var subTablesClauses = pagingWhereClauses.Where(sc => sc.Contains("stockmovement.fromobject") || sc.Contains("stockmovement.toobject")).ToList(); var newClauses = new Dictionary <int, List <string> >(); newClauses[0] = new List <string>(); newClauses[1] = new List <string>(); for (int i = 0; i < subTablesClauses.Count(); i++) { var clause = subTablesClauses[i]; if (clause.Contains("fromobject1")) { newClauses[0].Add(clause.Replace("))", ")").Replace("stockmovement.fromobject1", "stockmovement.fromobject") + ")"); } else if (clause.Contains("toobject1")) { newClauses[1].Add(clause.Replace("))", ")").Replace("stockmovement.toobject1", "stockmovement.toobject") + ")"); } else { newClauses[clause.Contains("fromobject") ? 0 : 1].Add(clause + ")"); } pagingWhereClauses.Remove(clause); } var whereClauses = new List <string>(); if (pagingWhereClauses.Skip(1).Any()) { whereClauses.Add(string.Join(") AND", pagingWhereClauses.Skip(1)) + ")"); } if (newClauses[0].Any()) { whereClauses.Add($"({string.Join(" OR ", newClauses[0])})"); } if (newClauses[1].Any()) { whereClauses.Add($"({string.Join(" OR ", newClauses[1])})"); } if (user.userRole == 2147483647 || user.hasRole(KOCUserTypes.StockRoomStuff)) { } else if (user.hasRole(KOCUserTypes.TeamLeader)) { var rolelist = Enum.GetValues(typeof(KOCUserTypes)).OfType <KOCUserTypes>().Where(r => user.hasRole(r)).Select(r => (int)r).ToList(); rolelist.Add(user.userRole); whereClauses.Add($"(fromobjecttype in ({string.Join(",", rolelist)}) or toobjecttype in ({string.Join(",", rolelist)}))"); } else { whereClauses.Add($"((fromobject = {user.userId} and fromobjecttype!= 16777217) or (toobject = {user.userId} and toobjecttype!= 16777217))"); // aynı id'ye sahip personel ve müşterilerde müşterinin hareketini personelinmiş gibi göstermesin diye type eklendi (16777217 -> müşteri type) } var whereClause = string.Join(" AND ", whereClauses); querySql = $"{sqlPartitions[0]}paging as ({pagingWhereClauses[0]}stockmovement WHERE{whereClause}) SELECT *{sqlPartitions[2]} "; countSql = $"{sqlPartitions[0]}paging as ({pagingWhereClauses[0]}stockmovement WHERE{whereClause}) SELECT COUNT(*) FROM _paging"; } var performance = new DTOQueryPerformance(); var perf = Stopwatch.StartNew(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); performance.CountSQLDuration = perf.Elapsed; perf.Restart(); var res = db.stockmovement.SqlQuery(querySql).ToList(); performance.QuerSQLyDuration = perf.Elapsed; perf.Restart(); var fromPerObjectIds = res.Where(t => t.fromobjecttype != (int)KOCUserTypes.ADSLCustomer).Select(s => s.fromobject).Distinct().ToList(); var fromCusObjectIds = res.Where(t => t.fromobjecttype == (int)KOCUserTypes.ADSLCustomer).Select(s => s.fromobject).Distinct().ToList(); var fromPersonels = db.personel.Where(p => fromPerObjectIds.Contains(p.personelid)).ToList(); var fromCustomers = db.customer.Where(c => fromCusObjectIds.Contains(c.customerid)).ToList(); var toPerObjectIds = res.Where(t => t.toobjecttype != (int)KOCUserTypes.ADSLCustomer).Select(s => s.toobject).Distinct().ToList(); var toCusObjectIds = res.Where(t => t.toobjecttype == (int)KOCUserTypes.ADSLCustomer).Select(s => s.toobject).Distinct().ToList(); var toPersonels = db.personel.Where(p => toPerObjectIds.Contains(p.personelid)).ToList(); var toCustomers = db.customer.Where(c => toCusObjectIds.Contains(c.customerid)).ToList(); var stockcardids = res.Select(s => s.stockcardid).Distinct().ToList(); var stockcards = db.stockcard.Where(s => stockcardids.Contains(s.stockid)).ToList(); res.ForEach(r => { r.frompersonel = fromPersonels.Where(p => p.personelid == r.fromobject).FirstOrDefault(); if (r.frompersonel == null) { r.fromcustomer = fromCustomers.Where(c => c.customerid == r.fromobject).FirstOrDefault(); } r.topersonel = toPersonels.Where(p => p.personelid == r.toobject).FirstOrDefault(); if (r.topersonel == null) { r.tocustomer = toCustomers.Where(c => c.customerid == r.toobject).FirstOrDefault(); } r.stockcard = stockcards.Where(s => s.stockid == r.stockcardid).FirstOrDefault(); }); performance.LookupDuration = perf.Elapsed; DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(s => s.toDTO()).ToList(), paginginfo, querySql, performance) , "application/json")); } }
public HttpResponseMessage getStockMovements(DTOGetStockMovementRequest request) { var userID = 12; using (var db = new CRMEntities()) { var filter = request.getFilter(); var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage); var countSql = filter.getCountSQL(); if (querySql.Contains("fromobject") || querySql.Contains("toobject")) { var sqlPartitions = querySql.Split(new string[] { "paging as (", ") SELECT *" }, StringSplitOptions.RemoveEmptyEntries); var pagingWhereClauses = sqlPartitions[1].Split(new string[] { "stockmovement WHERE", ") AND" }, StringSplitOptions.RemoveEmptyEntries).ToList(); var subTablesClauses = pagingWhereClauses.Where(sc => sc.Contains("stockmovement.fromobject") || sc.Contains("stockmovement.toobject")).ToList(); var newClauses = new Dictionary <int, List <string> >(); newClauses[0] = new List <string>(); newClauses[1] = new List <string>(); for (int i = 0; i < subTablesClauses.Count(); i++) { var clause = subTablesClauses[i]; if (clause.Contains("fromobject1")) { newClauses[0].Add(clause.Replace("))", ")").Replace("stockmovement.fromobject1", "stockmovement.fromobject") + " AND fromobjecttype = 3000)"); } else if (clause.Contains("fromobject2")) { newClauses[0].Add(clause.Replace("))", ")").Replace("stockmovement.fromobject2", "stockmovement.fromobject") + " AND fromobjecttype = 2000)"); } else if (clause.Contains("fromobject3")) { newClauses[0].Add(clause.Replace("))", ")").Replace("stockmovement.fromobject3", "stockmovement.fromobject") + " AND fromobjecttype = 1000)"); } else if (clause.Contains("toobject1")) { newClauses[1].Add(clause.Replace("))", ")").Replace("stockmovement.toobject1", "stockmovement.toobject") + " AND toobjecttype = 3000)"); } else if (clause.Contains("toobject2")) { newClauses[1].Add(clause.Replace("))", ")").Replace("stockmovement.toobject2", "stockmovement.toobject") + " AND toobjecttype = 2000)"); } else if (clause.Contains("toobject3")) { newClauses[1].Add(clause.Replace("))", ")").Replace("stockmovement.toobject3", "stockmovement.toobject") + " AND toobjecttype = 1000)"); } else { newClauses[clause.Contains("fromobject") ? 0 : 1].Add(clause + ")"); } pagingWhereClauses.Remove(clause); } var whereClauses = new List <string>(); if (pagingWhereClauses.Skip(1).Any()) { whereClauses.Add(string.Join(") AND", pagingWhereClauses.Skip(1)) + ")"); } if (newClauses[0].Any()) { whereClauses.Add($"({string.Join(" OR ", newClauses[0])})"); } if (newClauses[1].Any()) { whereClauses.Add($"({string.Join(" OR ", newClauses[1])})"); } whereClauses.Add($"(fromobject = {userID} or toobject = {userID})"); var whereClause = string.Join(" AND ", whereClauses); querySql = $"{sqlPartitions[0]}paging as ({pagingWhereClauses[0]}stockmovement WHERE{whereClause}) SELECT *{sqlPartitions[2]} "; countSql = $"{sqlPartitions[0]}paging as ({pagingWhereClauses[0]}stockmovement WHERE{whereClause}) SELECT COUNT(*) FROM _paging"; } var performance = new DTOQueryPerformance(); var perf = Stopwatch.StartNew(); var rowCount = db.Database.SqlQuery <int>(countSql).First(); performance.CountSQLDuration = perf.Elapsed; perf.Restart(); var res = db.stockmovement.SqlQuery(querySql).ToList(); performance.QuerSQLyDuration = perf.Elapsed; perf.Restart(); var fromObjectIds = res.Select(s => s.fromobject).Distinct().ToList(); var fromPersonels = db.personel.Where(p => fromObjectIds.Contains(p.personelid)).ToList(); var fromCustomers = db.customer.Where(c => fromObjectIds.Contains(c.customerid)).ToList(); var fromSites = db.site.Where(s => fromObjectIds.Contains(s.siteid)).ToList(); var fromBlocks = db.block.Where(b => fromObjectIds.Contains(b.blockid)).ToList(); var toObjectIds = res.Select(s => s.toobject).Distinct().ToList(); var toPersonels = db.personel.Where(p => toObjectIds.Contains(p.personelid)).ToList(); var toCustomers = db.customer.Where(c => toObjectIds.Contains(c.customerid)).ToList(); var toSites = db.site.Where(s => toObjectIds.Contains(s.siteid)).ToList(); var toBlocks = db.block.Where(b => toObjectIds.Contains(b.blockid)).ToList(); var stockcardids = res.Select(s => s.stockcardid).Distinct().ToList(); var stockcards = db.stockcard.Where(s => stockcardids.Contains(s.stockid)).ToList(); res.ForEach(r => { r.frompersonel = fromPersonels.Where(p => p.personelid == r.fromobject).FirstOrDefault(); if (r.frompersonel == null) { r.fromcustomer = fromCustomers.Where(c => c.customerid == r.fromobject).FirstOrDefault(); } r.topersonel = toPersonels.Where(p => p.personelid == r.toobject).FirstOrDefault(); if (r.topersonel == null) { r.tocustomer = toCustomers.Where(c => c.customerid == r.toobject).FirstOrDefault(); } r.stockcard = stockcards.Where(s => s.stockid == r.stockcardid).FirstOrDefault(); }); performance.LookupDuration = perf.Elapsed; DTOResponsePagingInfo paginginfo = new DTOResponsePagingInfo { pageCount = (int)Math.Ceiling(rowCount * 1.0 / request.rowsPerPage), pageNo = request.pageNo, rowsPerPage = request.rowsPerPage, totalRowCount = rowCount }; return(Request.CreateResponse(HttpStatusCode.OK, new DTOPagedResponse(DTOResponseError.NoError(), res.Where(r => r.deleted == false).Select(s => s.toDTO()).ToList(), paginginfo, querySql, performance) , "application/json")); } }