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 getBlocks(DTOGetBlockFilter request)
{
using (var db = new CRMEntities())
{
db.Configuration.AutoDetectChangesEnabled = false;
db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = false;
var filter = request.getFilter();
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 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 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 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 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 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 getTaskstate(DTOGetTSPFilter 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.taskstatepool.SqlQuery(querySql).OrderBy(o => o.taskstateid).ToList();
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 getCustomer(DTOs.Adsl.DTORequestClasses.DTOGetCustomerRequest request)
{
using (var db = new KOCSAMADLSEntities(false))
{
var filter = request.getFilter();
var querySql = filter.getPagingSQL(request.pageNo, request.rowsPerPage);
var countsql = filter.getCountSQL();
var rowCount = db.Database.SqlQuery <int>(countsql).First();
var res = db.customer.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 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"));
}
}
