partial void OnTaken(object sender, BusinessRulesEventArgs <SDFile> e)
{
var queryOr = e.ContextRequest.CustomParams.FirstOrDefault(p => p.Name == "queryOr");
if (queryOr != null && (e.Item != null || e.Items.Count > 0))
{
if (e.Item == null)
{
e.Item = e.Items[0];
}
CustomQuery customQuery = (CustomQuery)queryOr.Value;
if (customQuery.SelectedFields.FirstOrDefault(p => p.Name == "FileData") != null)
{
bool saveInBlob = false;
if (e.ContextRequest != null && e.ContextRequest.Company != null)
{
saveInBlob = (bool)SFSdotNet.Framework.Configuration.ModuleAppSettings.GetValue("save-files-blob", "SFSServiceDesk", "Boolean", false, false);
}
if (saveInBlob == true && !string.IsNullOrEmpty(e.Item.FileStorage))
{
SFSdotNet.Framework.Data.StaticStorage storage = new SFSdotNet.Framework.Data.StaticStorage("SFSServiceDesk".ToLower());
storage.GuidCompany = e.ContextRequest.Company.GuidCompany;
e.Item.FileData = storage.GetDataByteArray(e.Item.FileStorage);
}
}
}
}
public void DisposingTwoTimesShouldBeOk()
{
var query = new CustomQuery <SingleKeyParentTable>(this.Fixture.Connector, this.Fixture.SelectClause);
query.Dispose();
query.Dispose();
}
/// <summary>
/// Executes a query and return the results.
/// </summary>
/// <typeparam name="TResultType">The result type.</typeparam>
/// <param name="connector">The database connector.</param>
/// <param name="query">A custom commandText string. The select columns should be mapped to the <see cref="TResultType"/></param>
/// /// <param name="whereClause">A where filter clause. Do not add the "WHERE" keyword to it. If you need to pass parameters, pass using @1, @2, @3.</param>
/// <param name="parameters">A list of parameter values.</param>
/// <returns>A list of <see cref="TResultType"/></returns>
/// <seealso cref="Querying.CustomQuery{TResultType}"/>
public static async Task <List <TResultType> > CustomQueryAsync <TResultType>(this IDatabaseConnector connector, string query, string whereClause = null, params object[] parameters) where TResultType : class, new()
{
using (var customQuery = new CustomQuery <TResultType>(connector, query))
{
return(await customQuery.ExecuteAsync(whereClause, parameters));
}
}
public override bool Equals(Object obj)
{
if (this == obj)
{
return(true);
}
if (!base.Equals(obj))
{
return(false);
}
if (GetType() != obj.GetType())
{
return(false);
}
CustomQuery other = (CustomQuery)obj;
if (term == null)
{
if (other.term != null)
{
return(false);
}
}
else if (!term.equals(other.term))
{
return(false);
}
return(true);
}
public void Load()
{
string filter = string.Format("userId eq '{0}'", _client.User.user.userId);
CustomQuery query = new CustomQuery(filter);
Debug.Log("Load data for UserId: " + _client.User.user.userId + " query:" + query);
StartCoroutine(_table.Query <Inventory> (query, OnLoadCompleted));
}
public void DisposingTwoTimesShouldBeOk()
{
var query = new CustomQuery <SingleKeyParentTable>(Fixture.Connector, Fixture.SelectClause);
var result = query.Execute();
query.Dispose();
query.Dispose();
}
private static System.Data.DataTable QueryOnBase()
{
var extractCustomQueryName = System.Configuration.ConfigurationManager.AppSettings["extractCustomQueryName"];
if (!Int32.TryParse(System.Configuration.ConfigurationManager.AppSettings["numberOfDaysExtracted"], out Int32 numberOfDaysExtracted))
{
Log.Logger.Warning("numberOfDaysExtracted in Config is invalid! Defaulting to 30 days!");
numberOfDaysExtracted = 30;
}
if (!Int32.TryParse(System.Configuration.ConfigurationManager.AppSettings["maxQueryDocuments"], out Int32 maxQueryDocuments))
{
Log.Logger.Warning("maxQueryDocuments in Config is invalid! Defaulting to 10,000 documents!");
maxQueryDocuments = 10000;
}
System.Data.DataTable table = SetupTable();
using (Application obApp = OnBaseConnect())
{
DocumentQuery docQuery = obApp.Core.CreateDocumentQuery();
CustomQuery extractCQ = obApp.Core.CustomQueries.Find(extractCustomQueryName);
if (extractCQ != null)
{
Log.Logger.Debug(String.Format("Custom Query Found: {0}", extractCustomQueryName));
docQuery.AddCustomQuery(extractCQ);
DateTime endTime = DateTime.Now;
DateTime startTime = endTime.AddDays(-1 * numberOfDaysExtracted);
docQuery.AddDateRange(startTime, endTime);
DocumentList docList = docQuery.Execute(maxQueryDocuments);
foreach (Document doc in docList)
{
Log.Logger.Debug("Doc: {docId} - {docName}", doc.ID, doc.Name);
Form form = obApp.Core.GetDocumentByID(doc.ID).UnityForm;
if (form != null)
{
HomeEquityApplication heApplication = new HomeEquityApplication(doc, form);
table.Rows.Add(heApplication.RowDataArray());
}
}
}
else
{
Log.Logger.Error("Custom Query NOT Found: {extractCustomQueryName}", extractCustomQueryName);
}
}
return(table);
}
public void GetUsernameHighscore()
{
ResetList();
Highscore score = GetScore();
string filter = string.Format("username eq '{0}'", score.username);
string orderBy = "score desc";
CustomQuery query = new CustomQuery(filter, orderBy);
Query(query);
}
public void GetAzureUserData()
{
// ResetList ();
Userdata userdata = PrepareUserData();
string filter = string.Format("username eq '{0}'", GameManager.Instance.profileName);
//string orderBy = "score desc";
CustomQuery query = new CustomQuery(filter);
Query(query);
}
public void ShouldNotUseDisposedQueryObject()
{
var query = new CustomQuery <SingleKeyParentTable>(this.Fixture.Connector, this.Fixture.SelectClause);
query.Dispose();
Action executeQuery = () => query.Execute();
executeQuery.ShouldThrow <NullReferenceException>();
}
public void QueryObjectMustBeReutilizable()
{
var query = new CustomQuery <SingleKeyParentTable>(this.Fixture.Connector, this.Fixture.SelectClause);
var result = query.Execute();
var result2 = query.Execute();
result.Should().NotBeNull();
result2.Should().NotBeNull();
result2.Should().HaveSameCount(result);
}
public static Request <Models.MatchmakingBrowseResult> Browse(string pool, CustomQuery customQuery = null)
{
if (Core.IsInitialized())
{
return(new Request <Models.MatchmakingBrowseResult>(CAPI.ovr_Matchmaking_Browse(
pool,
customQuery != null ? customQuery.ToUnmanaged() : IntPtr.Zero
)));
}
return(null);
}
public static Request EnqueueRoom(UInt64 roomID, CustomQuery customQuery = null)
{
if (Core.IsInitialized())
{
return(new Request(CAPI.ovr_Matchmaking_EnqueueRoom(
roomID,
customQuery != null ? customQuery.ToUnmanaged() : IntPtr.Zero
)));
}
return(null);
}
public void GetTopHighscores()
{
ResetList();
DateTime today = DateTime.Today;
string day = today.ToString("s");
string filter = string.Format("createdAt gt '{0}Z'", day);
Debug.Log("filter:" + filter);
string orderBy = "score desc";
CustomQuery query = new CustomQuery(filter, orderBy, 10);
Query(query);
}
internal Tuple <DataTable, string> GetAllContactsCustomQuery()
{
CustomQuery custom = new CustomQuery(UserConnection, "Select Id, Name, Phone, Email from Contact");
DataTable dt;
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection(QueryKind.General))
{
using (IDataReader reader = custom.ExecuteReader(dbExecutor))
{
dt = reader.ReadToDataTable("Contact");
}
}
custom.BuildParametersAsValue = true;
return(new Tuple <DataTable, string>(dt, custom.GetSqlText()));
}
// Use this to run the queries for anomaly detection
public void AnomalyDetection()
{
ring.GetComponent <ProgressRing>().ResetFill();
ring.SetActive(true);
ProgressRing.flag3 = true;
drop.SetActive(true);
drop.GetComponent <Dropdown>().options.Clear();
anoms.Clear();
anomDevices.Clear();
anomSkip = 0;
filt = string.Format("eventprocessedutctime ge '{0}' and eventprocessedutctime le '{1}' and prediction eq 1",
start, end);
sel = "deviceid";
CustomQuery anomalyQuery = new CustomQuery(filt, "", 50, anomSkip, sel);
StartCoroutine(_anomalies.Query <Anomaly>(anomalyQuery, AnomalyQuery));
}
private void DeviceQuery(IRestResponse <NestedResults <Telemetry> > response)
{
if (!response.IsError)
{
Telemetry[] items = response.Data.results;
if (deviceSkip != 0)
{
device.AddRange(items.ToList());
}
else
{
device = items.ToList(); // set for first page of results
}
deviceSkip += 50;
if (deviceSkip < response.Data.count)
{
CustomQuery deviceQuery = new CustomQuery(filter, "eventprocessedutctime, deviceid", 50, deviceSkip, select);
StartCoroutine(_table.Query <Telemetry>(deviceQuery, DeviceQuery));
}
else
{
ProgressRing.flag2 = false;
if (device.Count() == 0)
{
flag = false;
}
else
{
foreach (string element in attributes)
{
graphVals[element] = dataPoints(device, element);
}
GetStats(4, graphVals);
TextToSpeechControl.Speech(getNumAnomalies(device).ToString() + " anomalous data points detected");
/*textToSpeech.SpeakText(getNumAnomalies(device).ToString() + " anomalous data points detected");*/
}
}
}
else
{
Debug.LogWarning("Read Nested Results Error Status:" + response.StatusCode.ToString() + " Url: " + response.Url);
}
}
public static Request <Models.MatchmakingEnqueueResultAndRoom> CreateAndEnqueueRoom(
string pool,
uint maxUsers,
bool subscribeToNotifications = false,
CustomQuery customQuery = null)
{
if (Core.IsInitialized())
{
return(new Request <Models.MatchmakingEnqueueResultAndRoom>(CAPI.ovr_Matchmaking_CreateAndEnqueueRoom(
pool,
maxUsers,
subscribeToNotifications,
customQuery != null ? customQuery.ToUnmanaged() : IntPtr.Zero
)));
}
return(null);
}
/// <summary>
/// Attach the model and view to the presenter
/// </summary>
/// <param name="model">The model to attach</param>
/// <param name="view">The view to attach</param>
/// <param name="explorerPresenter">The presenter to attach to</param>
public void Attach(object model, object view, ExplorerPresenter explorerPresenter)
{
this.query = model as CustomQuery;
this.view = view as CustomQueryView;
this.explorer = explorerPresenter;
this.view.OnRunQuery += RunQuery;
this.view.OnLoadFile += LoadFile;
this.view.OnWriteTable += WriteTable;
// If the model contains sql, update the view to display it
if (!string.IsNullOrEmpty(query.Sql))
{
this.view.Sql = query.Sql;
this.view.Filename = query.Filename;
this.view.Tablename = query.Tablename;
RunQuery(this, EventArgs.Empty);
}
}
public static string ExecuteSQL(string script, UserConnection userConnection)
{
script = script.Replace("|nl|", Environment.NewLine);
var query = new CustomQuery(userConnection, script);
var isChangeStateScript = GetIsChangeStateScript(script);
if (isChangeStateScript)
{
var count = query.Execute();
return(count.ToString());
}
var records = query.ExecuteReader(userConnection.EnsureDBConnection());
var dataTable = new DataTable {
TableName = "bpmcliTable"
};
dataTable.Load(records);
return(JsonConvert.SerializeObject(dataTable, Formatting.Indented));
}
// Runs the queries. Accepts a boolean which represents whether live mode is on.
public void RunQueries(bool live)
{
ResetData();
ring.GetComponent <ProgressRing>().ResetFill();
ring.SetActive(true);
ProgressRing.flag = true;
ProgressRing.flag2 = true;
// Check if up-to-date data is needed. Otherwise, include an end time for query results.
if (live)
{
filter = string.Format("eventprocessedutctime ge '{0}' and deviceid eq '{1}'",
start, ID);
filter2 = string.Format("eventprocessedutctime ge '{0}'",
start);
}
else
{
filter = string.Format("eventprocessedutctime ge '{0}' and eventprocessedutctime le '{1}' and deviceid eq '{2}'",
start, end, ID);
filter2 = string.Format("eventprocessedutctime ge '{0}' and eventprocessedutctime le '{1}'",
start, end);
}
foreach (string element in attributes)
{
select += element + ", ";
}
// This will change based on what the variable for the time element of the telemetry is called
select += "eventprocessedutctime, prediction";
// Send queries for both the device and globally. Order the results by the timestamp and the deviceid.
CustomQuery deviceQuery = new CustomQuery(filter, "eventprocessedutctime, deviceid", 50, deviceSkip, select);
StartCoroutine(_table.Query <Telemetry>(deviceQuery, DeviceQuery));
CustomQuery globalQuery = new CustomQuery(filter2, "eventprocessedutctime, deviceid", 50, globalSkip, select);
StartCoroutine(_table.Query <Telemetry>(globalQuery, GlobalQuery));
}
public void TestHighlightCustomQuery()
{
String s1 = "I call our world Flatland, not because we call it so,";
// Verify that a query against the default field results in text being
// highlighted
// regardless of the field name.
CustomQuery q = new CustomQuery(new Term(FIELD_NAME, "world"));
String expected = "I call our <B>world</B> Flatland, not because we call it so,";
String observed = highlightField(q, "SOME_FIELD_NAME", s1);
if (VERBOSE)
{
Console.WriteLine("Expected: \"" + expected + "\n" + "Observed: \""
+ observed);
}
assertEquals(
"Query in the default field results in text for *ANY* field being highlighted",
expected, observed);
// Verify that a query against a named field does not result in any
// highlighting
// when the query field name differs from the name of the field being
// highlighted,
// which in this example happens to be the default field name.
q = new CustomQuery(new Term("text", "world"));
expected = s1;
observed = highlightField(q, FIELD_NAME, s1);
if (VERBOSE)
{
Console.WriteLine("Expected: \"" + expected + "\n" + "Observed: \""
+ observed);
}
assertEquals(
"Query in a named field does not result in highlighting when that field isn't in the query",
s1, highlightField(q, FIELD_NAME, s1));
}
private void GlobalQuery(IRestResponse <NestedResults <Telemetry> > response)
{
if (!response.IsError)
{
Telemetry[] items = response.Data.results;
if (globalSkip != 0)
{
global.AddRange(items.ToList());
}
else
{
global = items.ToList(); // set for first page of results
}
globalSkip += 50;
if (globalSkip < response.Data.count)
{
CustomQuery globalQuery = new CustomQuery(filter2, "eventprocessedutctime, deviceid", 50, globalSkip, select);
StartCoroutine(_table.Query <Telemetry>(globalQuery, GlobalQuery));
}
else
{
ProgressRing.flag = false;
if (global.Count() != 0)
{
foreach (string element in attributes)
{
telGlobal[element] = dataPoints(global, element);
}
GetStats(0, telGlobal);
}
}
}
else
{
Debug.LogWarning("Read Nested Results Error Status:" + response.StatusCode.ToString() + " Url: " + response.Url);
}
}
private void AnomalyQuery(IRestResponse <NestedResults <Anomaly> > response)
{
if (!response.IsError)
{
Anomaly[] items = response.Data.results;
if (anomSkip != 0)
{
anoms.AddRange(items.ToList());
}
else
{
anoms = items.ToList(); // set for first page of results
}
anomSkip += 50;
if (anomSkip < response.Data.count)
{
CustomQuery anomalyQuery = new CustomQuery(filt, "", 50, anomSkip, sel);
StartCoroutine(_anomalies.Query <Anomaly>(anomalyQuery, AnomalyQuery));
}
else
{
ProgressRing.flag3 = false;
if (anoms.Count() != 0)
{
anomDevices = (from element in anoms select element.deviceid).Distinct().ToList <string>();
}
else
{
anomDevices.Add("0 Anomalies");
}
}
}
else
{
Debug.LogWarning("Read Nested Results Error Status:" + response.StatusCode.ToString() + " Url: " + response.Url);
}
}
public static TimeSpan GetRessult(DateTime startDate, DateTime endDate, Guid calendarId, string calendarTimeZone, string userTimeZone, UserConnection userConnection)
{
if (startDate > endDate)
{
return(new TimeSpan(0, 0, 0));
}
TimeZoneInfo timeZoneInfoKazahstan = TimeZoneInfo.FindSystemTimeZoneById(calendarTimeZone);
TimeZoneInfo timeZoneInfoLocal = TimeZoneInfo.FindSystemTimeZoneById(userTimeZone);
TimeSpan additionHours = timeZoneInfoKazahstan.BaseUtcOffset - timeZoneInfoLocal.BaseUtcOffset;
startDate += additionHours;
endDate += additionHours;
DayOfWeek startTimeDayOfWeek = startDate.DayOfWeek;
DayOfWeek endTimeDayOfWeek = endDate.DayOfWeek;
TimeSpan startTime = new TimeSpan(startDate.Hour, startDate.Minute, startDate.Second);
TimeSpan endTime = new TimeSpan(endDate.Hour, endDate.Minute, endDate.Second);
//startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day + 1);
//Вот именно в мессте + 1 и ошибка. Сегодня тот самый последний день в календаре для месяца.
//32 числа в июле нету. Внимательно посмотрите на 76 строку и только так добавляте в будущем дни, месяца и года, часы, минуты и т.д.
startDate = new DateTime(startDate.Year, startDate.Month, startDate.Day);
startDate = startDate.AddDays(1);
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day);
//var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
//var userConnection = new UserConnection(new AppConnection());
//место для запроса
var sqlQuery = string.Format(@"Select c.Id as CId,
c.Name as CName,
c.TimeZoneId as CTimeZoneId ,
c.Depth as CDepth ,
c.AroundClock as CAroundClock ,
c.WithoutDayOff as CWithoutDayOff,
dic.DayTypeId as DICDayTypeId ,
dic.DayOfWeekId as DICDayOfWeekId,
dic.Date as DICDay,
dow.Name as DOWNAme,
dow.Code as DOWCode,
dow.Number as DOWNumber,
dt.Name as DTName,
dt.IsWeekend as DTIsWeekend,
dt.NonWorking as DTNonWorking ,
wti.DayOffId as WTIDayOffId ,
wti.[From] as WTIfROM ,
wti.[To] AS WTITo
from [DayInCalendar] dic
join [Calendar] as c on c.Id = dic.CalendarId
join [DayOfWeek] as dow on dow.Id = dic.DayOfWeekId
join [DayType] as dt on dt.Id = dic.DayTypeId
join [WorkingTimeInterval] as wti on wti.DayInCalendarId= dic.Id
where dic.CalendarId = '{0}'
Order by dow.Number asc" , calendarId);
var sqlQueryDayOff = string.Format(@"Select top 20 do.Date as DODate,
dt.Name as DTName,
wti.[From] as WTIfROM ,
wti.[To] AS WTITo
from [DayOff] do
left join [DayType] as dt on dt.Id = do.DayTypeId
left join [WorkingTimeInterval] as wti on wti.DayOffId= do.Id
where do.CalendarId = '{0}'", calendarId);
List <UsrDayInCalendar> usrDays = new List <UsrDayInCalendar>();
List <UsrDayOff> usrDaysOff = new List <UsrDayOff>();
var resultQuery = new CustomQuery(userConnection, sqlQuery);
using (var dbExecutor = userConnection.EnsureDBConnection())
{
using (var reader = resultQuery.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
usrDays.Add(new UsrDayInCalendar()
{
CId = reader["CId"] != DBNull.Value ? reader["CId"].ToString() : string.Empty,
CName = reader["CName"] != DBNull.Value ? reader["CName"].ToString() : string.Empty,
CTimeZoneId = reader["CTimeZoneId"] != DBNull.Value ? reader["CTimeZoneId"].ToString() : string.Empty,
CDepth = reader["CDepth"] != DBNull.Value ? reader["CDepth"].ToString() : string.Empty,
CAroundClock = reader["CAroundClock"] != DBNull.Value ? reader["CAroundClock"].ToString() : string.Empty,
CWithoutDayOff = reader["CWithoutDayOff"] != DBNull.Value ? reader["CWithoutDayOff"].ToString() : string.Empty,
DICDayTypeId = reader["DICDayTypeId"] != DBNull.Value ? reader["DICDayTypeId"].ToString() : string.Empty,
DICDayOfWeekId = reader["DICDayOfWeekId"] != DBNull.Value ? reader["DICDayOfWeekId"].ToString() : string.Empty,
DICDay = reader["DICDay"] != DBNull.Value ? reader["DICDay"].ToString() : string.Empty,
DOWNAme = reader["DOWNAme"] != DBNull.Value ? reader["DOWNAme"].ToString() : string.Empty,
DOWCode = reader["DOWCode"] != DBNull.Value ? reader["DOWCode"].ToString() : string.Empty,
DOWNumber = reader["DOWNumber"] != DBNull.Value ? (int)reader["DOWNumber"] : 0,
DTName = reader["DTName"] != DBNull.Value ? reader["DTName"].ToString() : string.Empty,
DTIsWeekend = reader["DTIsWeekend"] != DBNull.Value ? reader["DTIsWeekend"].ToString() : string.Empty,
DTNonWorking = reader["DTNonWorking"] != DBNull.Value ? reader["DTNonWorking"].ToString() : string.Empty,
WTIDayOffId = reader["WTIDayOffId"] != DBNull.Value ? reader["WTIDayOffId"].ToString() : string.Empty,
WTIfROM = reader["WTIfROM"] != DBNull.Value ? reader["WTIfROM"].ToString() : string.Empty,
WTITo = reader["WTITo"] != DBNull.Value ? reader["WTITo"].ToString() : string.Empty
//Quantity = reader["Quantity"] != DBNull.Value ? (decimal)reader["Quantity"] : 0
});
}
}
}
var resultQueryDayOff = new CustomQuery(userConnection, sqlQueryDayOff);
using (var dbExecutor = userConnection.EnsureDBConnection())
{
using (var reader = resultQueryDayOff.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
usrDaysOff.Add(new UsrDayOff()
{
DODate = reader["DODate"] != DBNull.Value ? (DateTime)reader["DODate"] : new DateTime(2000, 1, 1),
DTName = reader["DTName"] != DBNull.Value ? reader["DTName"].ToString() : string.Empty,
WTIfROM = reader["WTIfROM"] != DBNull.Value ? reader["WTIfROM"].ToString() : string.Empty,
WTITo = reader["WTITo"] != DBNull.Value ? reader["WTITo"].ToString() : string.Empty
});
}
}
}
//Расчет часов для каждого дня недели
List <UsrDayOn> usrDaysOn = new List <UsrDayOn>();
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Monday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Tuesday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Wednesday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Thursday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Friday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Saturday, allDayHours = new TimeSpan(0, 0, 0)
});
usrDaysOn.Add(new UsrDayOn()
{
dayOfWeek = DayOfWeek.Sunday, allDayHours = new TimeSpan(0, 0, 0)
});
foreach (var currentDayOfWeek in usrDaysOn)
{
foreach (var item in usrDays.Where(t => t.DOWCode == currentDayOfWeek.dayOfWeek.ToString()))
{
currentDayOfWeek.allDayHours += TimeSpan.Parse(item.WTITo) - TimeSpan.Parse(item.WTIfROM);
}
}
TimeSpan TSRessult = new TimeSpan(0, 0, 0, 0);
//Расчет первой и последней даты
//Если первая и последняя даты тот же день
if (endDate == startDate.AddDays(-1))
{
//Сокращенный или праздничный день
if (usrDaysOff.Where(h => h.DODate == endDate).Count() > 0)
{
//Сокращенный день
foreach (var item in usrDaysOff.Where(t => t.DODate == endDate && (t.DTName == "Сокращенный" || t.DTName == "Рабочий день")))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (endTime > startTime)
{
if (endTime <= from || to <= startTime)
{
}
else if (startTime <= from && from < endTime && endTime <= to)
{
TSRessult += endTime - from;
}
else if (startTime <= from && to <= endTime)
{
TSRessult += to - from;
}
else if (from <= startTime && endTime <= to)
{
TSRessult += endTime - startTime;
}
else if (from <= startTime && startTime < to && to <= endTime)
{
TSRessult += to - startTime;
}
else
{
}
}
}
}
else // обычный день
{
foreach (var item in usrDays.Where(t => t.DOWCode == startTimeDayOfWeek.ToString()))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (endTime > startTime)
{
if (endTime <= from || to <= startTime)
{
}
else if (startTime <= from && from < endTime && endTime <= to)
{
TSRessult += endTime - from;
}
else if (startTime <= from && to <= endTime)
{
TSRessult += to - from;
}
else if (from <= startTime && endTime <= to)
{
TSRessult += endTime - startTime;
}
else if (from <= startTime && startTime < to && to <= endTime)
{
TSRessult += to - startTime;
}
else
{
}
}
}
}
}
else //Если последняя дата > первой
{
//Расчет часов начальной даты
//Сокращенный день или праздник
if (usrDaysOff.Where(h => h.DODate == startDate.AddDays(-1)).Count() > 0)
{
foreach (var item in usrDaysOff.Where(t => t.DODate == startDate.AddDays(-1) && (t.DTName == "Сокращенный" || t.DTName == "Рабочий день")))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (startTime <= from)
{
TSRessult += to - from;
}
else if (startTime > from && startTime < to)
{
TSRessult += to - startTime;
}
else
{
}
}
}
else // Обычный день
{
foreach (var item in usrDays.Where(t => t.DOWCode == startTimeDayOfWeek.ToString()))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (startTime <= from)
{
TSRessult += to - from;
}
else if (startTime > from && startTime < to)
{
TSRessult += to - startTime;
}
else
{
}
}
}
//Расчет часов конечной даты
//Сокращенный день или праздник
if (usrDaysOff.Where(h => h.DODate == endDate).Count() > 0)
{
foreach (var item in usrDaysOff.Where(t => t.DODate == endDate && (t.DTName == "Сокращенный" || t.DTName == "Рабочий день")))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (endTime <= from)
{
}
else if (endTime > from && endTime < to)
{
TSRessult += endTime - from;
}
else
{
TSRessult += to - from;
}
}
}
else //обычный день
{
foreach (var item in usrDays.Where(t => t.DOWCode == endTimeDayOfWeek.ToString()))
{
TimeSpan from = TimeSpan.Parse(item.WTIfROM);
TimeSpan to = TimeSpan.Parse(item.WTITo);
if (endTime <= from)
{
}
else if (endTime > from && endTime < to)
{
TSRessult += endTime - from;
}
else
{
TSRessult += to - from;
}
}
}
}
//Расчет срединного промежутка
if (startDate < endDate)
{
for (DateTime currentDate = startDate; currentDate < endDate; currentDate = currentDate.AddDays(1))
{
if (usrDaysOff.Where(h => h.DODate == currentDate).Count() > 0)
{
foreach (var item in usrDaysOff.Where(t => t.DODate == currentDate && (t.DTName == "Сокращенный" || t.DTName == "Рабочий день")))
{
TSRessult += TimeSpan.Parse(item.WTITo) - TimeSpan.Parse(item.WTIfROM);
}
}
else
{
TSRessult += usrDaysOn.Where(f => f.dayOfWeek == currentDate.DayOfWeek).FirstOrDefault().allDayHours;
}
}
}
return(TSRessult);
}
/// <summary>
/// Retrieves the filter for the current SearchValues.
/// </summary>
/// <returns>An SQL filter.</returns>
public string GetFilter()
{
string retval = "";
switch (Kind)
{
case Data.SIMPLE_TEXT:
case Data.YES_NO:
case Data.OPTIONS:
case Data.CHECKBOX:
case Data.MULTI_TEXT:
case Data.EMAIL:
if (!String.IsNullOrEmpty(SearchValue))
{
if (!String.IsNullOrEmpty(CustomQuery))
{
string searchItem = SearchValue;
if (FriendlyOptions.Count != 0)
{
try
{
searchItem = FriendlyOptions[SearchValue];
}
catch (KeyNotFoundException ex) { }
}
searchItem = Common.RemoveIntrusiveChars(searchItem);
if (!String.IsNullOrEmpty(searchItem))
{
retval = CustomQuery.Replace("#SEARCH_VALUE#", Common.SQSF(searchItem));
}
}
else
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND Value LIKE '%" + Common.SQSF(SearchValue) + "%')";
}
}
break;
case Data.TREE:
if (!String.IsNullOrEmpty(SearchValue))
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND Value LIKE '" + Common.SQSF(SearchValue) + "%')";
}
break;
case Data.INTEGER:
case Data.FLOAT:
case Data.SLIDER:
if (!String.IsNullOrEmpty(SearchValue) || !String.IsNullOrEmpty(SearchValueAux))
{
if (String.IsNullOrEmpty(SearchValueAux))
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CAST(Value AS Float) >= " + Common.SQSF(SearchValue) + ")";
}
else if (String.IsNullOrEmpty(SearchValue))
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CAST(Value AS Float) <= " + Common.SQSF(SearchValueAux) + ")";
}
else
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CAST(Value AS Float) >= " + Common.SQSF(SearchValue) + " AND CAST(Value AS Float) <= " + Common.SQSF(SearchValueAux) + ")";
}
if (!String.IsNullOrEmpty(CustomQuery))
{
if (String.IsNullOrEmpty(SearchValue))
{
SearchValue = "-999999";
}
if (String.IsNullOrEmpty(SearchValueAux))
{
SearchValueAux = "999999";
}
retval = CustomQuery.Replace("#SEARCH_VALUE#", Common.SQSF(SearchValue)).Replace("#SEARCH_VALUE2#", Common.SQSF(SearchValueAux));
}
}
break;
case Data.DATE:
if (!String.IsNullOrEmpty(SearchValue) || !String.IsNullOrEmpty(SearchValueAux))
{
if (String.IsNullOrEmpty(SearchValueAux))
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CONVERT(DATETIME, Value, 103) >= CONVERT(DATETIME, '" + Common.SQSF(SearchValue) + "', 103))";
}
else if (String.IsNullOrEmpty(SearchValue))
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CONVERT(DATETIME, Value, 103) <= CONVERT(DATETIME, '" + Common.SQSF(SearchValueAux) + "', 103))";
}
else
{
retval = "IdUser IN (SELECT IdUser FROM UserData WHERE IdData = " + IdData + " AND CONVERT(DATETIME, Value, 103) BETWEEN CONVERT(DATETIME, '" + Common.SQSF(SearchValue) + "', 103) AND CONVERT(DATETIME, '" + Common.SQSF(SearchValueAux) + "', 103))";
}
if (!String.IsNullOrEmpty(CustomQuery))
{
if (String.IsNullOrEmpty(SearchValue))
{
SearchValue = "01/01/1900";
}
if (String.IsNullOrEmpty(SearchValueAux))
{
SearchValueAux = "01/01/2500";
}
retval = CustomQuery.Replace("#SEARCH_VALUE#", Common.SQSF(SearchValue)).Replace("#SEARCH_VALUE2#", Common.SQSF(SearchValueAux));
}
}
break;
}
return(retval);
}
private void Query(CustomQuery query)
{
StartCoroutine(_table.Query <Highscore> (query, OnReadCompleted));
}
public Stream GenerateSalaryReport(string key)
{
if (System.Diagnostics.Debugger.IsAttached)
{
System.Diagnostics.Debugger.Break();
}
var UserConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
MemoryStream stream = new MemoryStream();
var filters = (Dictionary <string, string>)UserConnection.ApplicationCache[key];
UserConnection.ApplicationCache.Remove(key);
string StartDate = filters["StartDate"];
string DueDate = filters["DueDate"];
string location = filters["location"];
string ServicesScope = filters["services"];
////////////////////////Attributes for sql filter///////////////////////////////////////////
string subId2 = ";";
int mi1 = 0;
int mi2;
int k = 1;
string ServiceItemName;
string quot = "'";
string sqlfilter = "";
string teststr = ServicesScope;
////////////////////////Attributes for sql filter end///////////////////////////////////////////
////////////////////////SQL FILTER BY SERVICE ITEM///////////////////////////////////////////
if (teststr != String.Empty)
{
sqlfilter = " and ServiceItemId in (select Id from ServiceItem where Name = ";
//parse:
while (teststr != String.Empty)
{
mi2 = teststr.IndexOf(subId2);
ServiceItemName = teststr.Substring(mi1, mi2);
teststr = teststr.Remove(mi2, 1);
teststr = teststr.Remove(0, ServiceItemName.Length);
if (k == 1)
{
sqlfilter = sqlfilter + quot + ServiceItemName + quot;
k++;
}
else if (k > 1)
{
sqlfilter = sqlfilter + " or Name = " + quot + ServiceItemName + quot;
}
}
//if (teststr != String.Empty) {
// goto parse;
//}
sqlfilter = sqlfilter + ")";
}
////////////////////////SQL FILTER BY SERVICE ITEM END///////////////////////////////////////////
string StrStartDate = String.Format("{0:yyyy-MM-dd H:mm:ss}", StartDate);
string StrDueDate = String.Format("{0:yyyy-MM-dd H:mm:ss}", DueDate);
var dateFilter = "WHERE a.CreatedOn BETWEEN '" + StrStartDate + "' AND '" + StrDueDate + "'";
var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"];
var select = string.Empty;
int CountActivity = 0;
int flag = 1;
string errorText = "";
try
{
//------------------------Считать кол-во всех обращений за период----------------------//
var sqlQuery = string.Format(@"SELECT
Count(a.Id) AS [CountActivity]
FROM [Case] a WITH(NOLOCK)
{0} " , dateFilter);
select = sqlQuery; //проверка запроса
flag = 2;
var resultQuery = new CustomQuery(userConnection, sqlQuery);
flag = 3;
using (var dbExecutor = userConnection.EnsureDBConnection())
{
using (var reader = resultQuery.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
flag = 4;
CountActivity = reader["CountActivity"] != DBNull.Value ? (int)reader["CountActivity"] : 0;
flag = 5;
}
}
}
//------------------------Считать кол-во всех обращений c плохой оценкой за период----------------------//
var dateFilterb = "WHERE a.CreatedOn BETWEEN '" + StrStartDate + "' AND '" + StrDueDate + "'";
var sqlQueryb = string.Format(@"SELECT
Count(a.Id) AS [CountActivity]
FROM [Case] a WITH(NOLOCK)
{0} " , dateFilterb);
select = sqlQueryb; //проверка запроса
var resultQueryb = new CustomQuery(userConnection, sqlQueryb);
int CountActivityb = 0;
using (var dbExecutorb = userConnection.EnsureDBConnection())
{
using (var readerb = resultQueryb.ExecuteReader(dbExecutorb))
{
while (readerb.Read())
{
CountActivityb = readerb["CountActivity"] != DBNull.Value ? (int)readerb["CountActivity"] : 0;
}
}
}
var dateFilterb2a = "WHERE a.CreatedOn BETWEEN '" + StrStartDate + "' AND '" + StrDueDate + "'";
var sqlQueryb2a = string.Format(@"SELECT
Count(a.Id) AS [CountActivity]
FROM [Case] a WITH(NOLOCK)
{0} " , dateFilterb2a);
select = sqlQueryb2a; //проверка запроса
var resultQueryb2a = new CustomQuery(userConnection, sqlQueryb2a);
int CountActivityb2a = 0;
using (var dbExecutorb2a = userConnection.EnsureDBConnection())
{
using (var readerb2a = resultQueryb2a.ExecuteReader(dbExecutorb2a))
{
while (readerb2a.Read())
{
CountActivityb2a = readerb2a["CountActivity"] != DBNull.Value ? (int)readerb2a["CountActivity"] : 0;
}
}
}
using (var wb = new XLWorkbook())
{
flag = 6;
//------------------------Header----------------------//
//------------------------Первая страница----------------------//
var ws = wb.Worksheets.Add("Статистика");
var row1 = ws.Row(3);
ws.Range("A1:D1").Merge();
ws.Range("A3:D3").Merge();
//ws.Range("A5:B5").Merge();
//ws.Range("A6:B6").Merge();
//ws.Range("A7:B7").Merge();
ws.Row(1).Cell(1).Value = "Отчетная статистика c " + StrStartDate.ToString() + " по " + StrDueDate.ToString();
row1.Cell(1).Value = "Качественно и своевременно решенные обращения";
var row3 = ws.Row(5);
row3.Cell(1).Value = teststr;
row3.Cell(4).Value = sqlfilter;
var row4 = ws.Row(6);
var row5 = ws.Row(7);
var row6 = ws.Row(8);
row4.Cell(1).Value = "Имеющие плохую оценку: ";
row4.Cell(4).Value = CountActivityb.ToString();
row5.Cell(1).Value = "Имеющие просроченность по разрешению: ";
row5.Cell(4).Value = CountActivityb2a.ToString();
row6.Cell(1).Value = "Качественно решенные обращения за период: ";
row6.Cell(4).Value = Convert.ToString(CountActivity);
//************************************************************************************************
//************************************************************************************************
//************************************************************************************************
//************************************************************************************************
//************************************************************************************************
//-----------------------Настройка стилей-----------------------//
//-----------------------Первая страница-----------------------//
ws.Row(1).Cell(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(3).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Column(4).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Columns().AdjustToContents(1, 8);
ws.Column(1).Width = 26.00;
ws.Column(2).Width = 26.00;
ws.Column(3).Width = 26.00;
ws.Column(3).Width = 4.00;
ws.Row(5).CellsUsed().Style.Font.Bold = true;
ws.Row(6).CellsUsed().Style.Font.Bold = true;
ws.Row(7).CellsUsed().Style.Font.Bold = true;
ws.Row(8).CellsUsed().Style.Font.Bold = true;
ws.Row(1).CellsUsed().Style.Font.Bold = true;
ws.Row(3).CellsUsed().Style.Font.FontSize = 14;
ws.Row(3).CellsUsed().Style.Font.Bold = true;
ws.Row(10).Cell(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(12).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(14).CellsUsed().Style.Font.Bold = true;
ws.Row(15).CellsUsed().Style.Font.Bold = true;
ws.Row(16).CellsUsed().Style.Font.Bold = true;
ws.Row(12).CellsUsed().Style.Font.Bold = true;
ws.Row(12).CellsUsed().Style.Font.FontSize = 14;
ws.Row(19).Cell(1).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(21).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center).Alignment.Vertical = XLAlignmentVerticalValues.Center;
ws.Row(23).CellsUsed().Style.Font.Bold = true;
ws.Row(24).CellsUsed().Style.Font.Bold = true;
ws.Row(25).CellsUsed().Style.Font.Bold = true;
ws.Row(21).CellsUsed().Style.Font.Bold = true;
ws.Row(21).CellsUsed().Style.Font.FontSize = 14;
ws.Cell(3, 1).Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Accent1);
ws.Cell(12, 1).Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Accent1);
ws.Cell(21, 1).Style.Font.FontColor = XLColor.FromTheme(XLThemeColor.Accent1);
//-----------------------Save-----------------------//
var memStream = new MemoryStream();
wb.SaveAs(memStream);
memStream.Position = 0;
if (WebOperationContext.Current != null)
{
WebOperationContext.Current.OutgoingResponse.ContentType = "application/octet-stream";
WebOperationContext.Current.OutgoingResponse.ContentLength = memStream.Length;
WebOperationContext.Current.OutgoingResponse.Headers.Add("Content-Disposition",
"attachment; filename=\"" + "CaseReport" + ".xlsx\"");
}
return(memStream);
}
}
catch (Exception ex)
{
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("Exception");
var row1 = worksheet.Row(1);
row1.Cell("A").Value = " - " + flag + " - " + select;
var row2 = worksheet.Row(2);
row2.Cell("A").Value = ex.Message;
var row5 = worksheet.Row(5);
row5.Cell("A").Value = "errorText: " + errorText;
var memStream = new MemoryStream();
workbook.SaveAs(memStream);
memStream.Position = 0;
if (WebOperationContext.Current != null)
{
WebOperationContext.Current.OutgoingResponse.ContentType = "application/octet-stream";
WebOperationContext.Current.OutgoingResponse.ContentLength = memStream.Length;
WebOperationContext.Current.OutgoingResponse.Headers.Add("Content-Disposition",
"attachment; filename=\"" + "CaseReport" + ".xlsx\"");
}
return(memStream);
}
}
}
private void GetPageHighscores()
{
CustomQuery query = new CustomQuery("", "score desc", _noPageResults, _skip, "id,username,score");
StartCoroutine(_table.Query <Highscore> (query, OnReadNestedResultsCompleted));
}
private State <TId, TData> HandleCustomQuery(State <TId, TData> state, CustomQuery <TId, TData> request)
{
state.Index++;
return(state);
}
