protected override void Awake() {
base.Awake();
EnemyTargets = new List<IMortalTarget>();
ID = Guid.NewGuid();
RangeSpan = new ValueRange<float>(Constants.ZeroF, Constants.ZeroF);
Collider.radius = Constants.ZeroF; // initialize to same value as Range
}
static void Main(string[] args)
{
UserCredential credential;
using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "1xeRwJHbs4T10akqx5LNRQHobx7CQe1_C4PTv_mb3I_E";
String range = "Sheet1!A2:G";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
List <DriverModel> drivers = DriverModel.Convert(values);
using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=playground;Integrated Security=True"))
{
foreach (var driver in drivers)
{
SqlCommand insert = new SqlCommand("insert into Drivers(DriverID, PayPeriod, Qualify, PaperWork, Expiration, Complaint, UpdateTime) " +
"values(@driverId, @payPeriod, @qualify, @paperWork, @expiration, @complaint, @updateTime)", conn);
insert.Parameters.AddWithValue("@driverId", driver.DriverID);
insert.Parameters.AddWithValue("@payPeriod", driver.PayPeriod);
insert.Parameters.AddWithValue("@qualify", driver.Qualify);
insert.Parameters.AddWithValue("@paperWork", driver.PaperWork);
insert.Parameters.AddWithValue("@expiration", driver.Expiration);
insert.Parameters.AddWithValue("@complaint", driver.Complaint);
insert.Parameters.AddWithValue("@updateTime", driver.UpdateTime);
try
{
conn.Open();
insert.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"Error inserting record for {driver.DriverID} - {ex.Message}");
}
finally
{
conn.Close();
}
}
}
}
else
{
Console.WriteLine("No data found.");
}
Console.Read();
}
public Builder SetRange(SymbolId key, ValueRange value)
{
ranges[key] = value;
return(this);
}
public static void UpdateSheetAtParticularIndex(string newSheetName, string sheetName, int index, IUoW uow, int clientId, string sheetId, int apiKeySecretId = 20, int accountTypeId = 27)
{
try
{
var service = GetGoogleSpreadSheetService(uow, clientId, sheetId, apiKeySecretId, accountTypeId);
Spreadsheet spreadsheet = service.Spreadsheets.Get(sheetId).Execute();
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int curretSheetId = (int)sheet.Properties.SheetId;
var dd = sheet.Data;
//getting all data from sheet
var valTest = service.Spreadsheets.Values.Get(sheetId, $"{sheetName}!A2:D2").Execute();
var valvalues = valTest.Values;
//adding to specific row
InsertRangeRequest insRow = new InsertRangeRequest();
insRow.Range = new GridRange()
{
SheetId = curretSheetId,
StartRowIndex = 3,
EndRowIndex = 4,
};
insRow.ShiftDimension = "ROWS";
//sorting
SortRangeRequest sorting = new SortRangeRequest();
sorting.Range = new GridRange()
{
SheetId = curretSheetId,
StartColumnIndex = 0, // sorted by firstcolumn for all data after 1st row
StartRowIndex = 1
};
sorting.SortSpecs = new List <SortSpec>
{
new SortSpec
{
SortOrder = "DESCENDING"
}
};
//
InsertDimensionRequest insertRow = new InsertDimensionRequest();
insertRow.Range = new DimensionRange()
{
SheetId = curretSheetId,
Dimension = "ROWS",
StartIndex = 1, // 0 based
EndIndex = 2
};
var oblistt = new List <object>()
{
"Helloins", "This4ins", "was4ins", "insertd4ins"
};
PasteDataRequest data = new PasteDataRequest
{
Data = string.Join(",", oblistt),
Delimiter = ",",
// data gets inserted form this cordinate point( i.e column and row) index and to the right
Coordinate = new GridCoordinate
{
ColumnIndex = 0, // 0 based Col A is 0, col B is 1 and so on
RowIndex = 2, //
SheetId = curretSheetId
},
};
BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
//new Request{ InsertDimension = insertRow },
//new Request{ PasteData = data },
//new Request { InsertRange = insRow},
new Request {
SortRange = sorting
}
}
};
BatchUpdateSpreadsheetResponse response1 = service.Spreadsheets.BatchUpdate(r, sheetId).Execute();
//adding data to sheet
var valueRange = new ValueRange();
var oblist = new List <object>()
{
"Hello466", "This466", "was466", "insertd466"
};
valueRange.Values = new List <IList <object> > {
oblist
};
var appendRequest = service.Spreadsheets.Values.Append(valueRange, sheetId, $"{sheetName}!A:D");
//appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
//updating data to sheet
var valueRangeUpdate = new ValueRange();
var oblistUpdate = new List <object>()
{
"Hello4uqq", "This4uqq", "was4uqq", "insertd4uqq"
};
valueRangeUpdate.Values = new List <IList <object> > {
oblistUpdate
};
var updateRequest = service.Spreadsheets.Values.Update(valueRangeUpdate, sheetId, $"{sheetName}!A10:D510");
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var updateReponse = updateRequest.Execute();
//deleting row from sheet
var range = $"{sheetName}!A7:G7";
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, sheetId, range);
var deleteReponse = deleteRequest.Execute();
// update new Sheet
var updateSheetRequest = new UpdateSheetPropertiesRequest();
updateSheetRequest.Properties = new SheetProperties();
updateSheetRequest.Properties.Title = sheetName; //Sheet1
updateSheetRequest.Properties.Index = index;
updateSheetRequest.Properties.SheetId = curretSheetId;
updateSheetRequest.Fields = "Index,Title";//fields that needs to be updated.* means all fields from that sheet
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateSheetProperties = updateSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, sheetId);
var response = batchUpdateRequest.Execute();
}
catch (Exception ex)
{
throw;
}
}
private List <Treasure> GetTable(List <Treasure> treasure)
{
// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
string ApplicationName = "Google Sheets API .NET Quickstart";
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)
)
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = System.IO.Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "1wkCt0YfEe7nPtCixij5zR66vZubmw7K21q7r07b1EBI";
String range = "Items!A2:K";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1wkCt0YfEe7nPtCixij5zR66vZubmw7K21q7r07b1EBI/edit
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
treasure = new List <Treasure>();
if (values != null && values.Count > 0)
{
Console.WriteLine("Importing data");
foreach (var row in values)
{
if ((string)row[4] == "")
{
row[4] = 0;
}
switch (Convert.ToString(row[2]))
{
case "Weapon":
treasure.Add(new Weapon(row));
break;
case "Armor":
treasure.Add(new Armor(row));
break;
case "Trinket":
treasure.Add(new Trinket(row));
break;
default:
break;
}
}
}
else
{
Console.WriteLine("No data found.");
}
return(treasure);
}
private static void AddMarkedOrSelectedDistance(TrailsItemTrackSelectionInfo tmpSel, bool singleSelection, double d1, double d2)
{
//Use SelectedTime with only one selection and 0 span - valueRanges will not handle it
//Add 1s if not possible
IValueRange<double> vd = new ValueRange<double>(d1, d2);
if (singleSelection && (tmpSel.MarkedDistances == null || tmpSel.MarkedDistances.Count == 0))
{
//Could add to selected, single point marked
tmpSel.SelectedDistance = vd;
tmpSel.MarkedDistances = null;
}
else
{
if (tmpSel.MarkedDistances == null)
{
tmpSel.MarkedDistances = new ValueRangeSeries<double>();
}
if (vd.Lower == vd.Upper)
{
//fake, add a meter
vd = new ValueRange<double>(vd.Lower, vd.Upper + 1);
}
tmpSel.MarkedDistances.Add(vd);
}
}
public void SetUp()
{
valueRange = new ValueRange(0.2f, 0.3f);
}
protected void LoadData()
{
string credPath = @"d:\\project-id-9301225348112901974-d05770b23edc.json";
DateTime t1 = new DateTime(); t1 = DateTime.Now;
string json = File.ReadAllText(credPath);
Newtonsoft.Json.Linq.JObject cr = (Newtonsoft.Json.Linq.JObject)JsonConvert.DeserializeObject(json);
string private_key = (string)cr.GetValue("private_key");
string email = (string)cr.GetValue("Client_email");
ServiceAccountCredential credential = new ServiceAccountCredential(new ServiceAccountCredential.Initializer("104602038154026657417")
{
Scopes = new[] { SheetsService.Scope.Spreadsheets }
}.FromPrivateKey(private_key));
// Create the service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "CC_Sheets_Service",
});
var fred = service.Spreadsheets.Get("10usxblP6yGQTB2VkYeA5xuLUMepf1TuIcyhig4H1ygk");
fred.IncludeGridData = true;
String spreadsheetId = "10usxblP6yGQTB2VkYeA5xuLUMepf1TuIcyhig4H1ygk";
String range = "Sheet1!A1:AB210";
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
// now who are we??
Guid PersonID = new Guid();
Utility u1 = new Utility();
PersonID = u1.GetPersonIdfromRequest(Request);
if (PersonID == new Guid("20744211-d0f0-4e69-af84-020c1023dfda")) //cc
{
PersonID = u1.GetPersonIDX(@"CHALLONERS\william.kitchener"); //development
//PersonID = u1.GetPersonIDX(@"CHALLONERS\George.pickford");//development
}
int adno = u1.GetAdmissionNumber(PersonID);
SimplePupil pupil1 = new SimplePupil(); pupil1.Load(adno);
string s1 = "<p ><h3 align=\"center\">";
s1 += "Predicted Results for ";
s1 += pupil1.m_GivenName + " " + pupil1.m_Surname + "</h3></p>";
s1 += "<br /><TABLE BORDER class=\"ResultsTbl\" align=\"center\" >";
s1 += "<TR><TD>Course Code</TD><TD> Course Name </TD><TD> Grade </TD></TR>";
int max_cols = values[0].Count;
int max_rows = values.Count;
//need to get names for course codes
CourseList cl1 = new CourseList(5);
for (var i = 1; i < max_rows; i++)
{
string s = (string)values[i][0];
int ad2 = System.Convert.ToInt32(s);
string cse = ""; string cseN = "";
if (ad2 == adno)
{
max_cols = values[i].Count;
for (var j = 5; j < max_cols; j++)
{
s = (string)values[i][j];
cse = (string)values[0][j];
if (s != "")
{
foreach (Course c in cl1._courses)
{
if (c.CourseCode == cse)
{
cseN = c.CourseName; break;
}
}
//for 2018 only!!!
if (cse == "MFA")
{
cseN = "Further Maths";
}
; if (cse == "MF")
{
cseN = "Mathematics";
}
s1 += "<TR><TD>" + cse + "</TD><TD> " + cseN + "</TD><TD> " + s + " </TD></TR>";
}
cseN = "";
}
break;
}
}
s1 += "</TABLE></br>";
TimeSpan ts1 = new TimeSpan(); ts1 = DateTime.Now - t1;
s1 += "Time Taken : " + ts1.TotalSeconds.ToString() + "<br/>";
content.InnerHtml = s1;
}
static void Main(string[] args)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
UserCredential credential;
using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
string spreadsheetId = "1EFRAzE64e8bZlDs1WWZiG5e1hJgOWlQ3uIezJ_6ZZCQ";//"1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
string range = "Runtime Spells!A3:T";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
// https://docs.google.com/spreadsheets/d/1zBzUJ8TE6jpQRF-RubmZ-p8PmVXqm_o-cjUb4kwnh1U/edit?usp=sharing
// https://docs.google.com/spreadsheets/d/1EFRAzE64e8bZlDs1WWZiG5e1hJgOWlQ3uIezJ_6ZZCQ/edit?usp=sharing
ValueRange response = request.Execute();
IList <IList <object> > values = response.Values;
int loaded = 0;
if (values != null && values.Count > 0)
{
/*Console.WriteLine("Spell Name, Spell Rarity/How to Obtain, Type, Class Exclusive, Spell Damage Min, Spell Damage Max, Crit Chance, " +
* "Modifier ID (See Spell Index), Spell Heal Min (% max health), Spell Heal Max (% max health), Mana Cost, Stamina Cost Cast Chance (%), " +
* "Spell Type, Element, Spell Turns, Spell Description, Spell Icon, Allowed in PvP, Allowed on World Bosses");*/
foreach (var row in values)
{
//try
//{
// Print columns A and E, which correspond to indices 0 and 4.
Console.WriteLine("Adding new spell to the database: {0}", row[0] as string);
loaded++;
float damage_min = 0;
float damage_max = 0;
float crit_chance = 0;
float modID = 0;
float spell_heal_min = 0;
float spell_heal_max = 0;
float mana_cost = 0;
float stamina_cost = 0;
float cast_chance = 0;
float spell_turns = 0;
bool allowed_in_pvp = false;
bool allowed_on_world_bosses = false;
try { damage_min = Convert.ToSingle((row[4] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set damage_min of " + row[0] as string); }
try { damage_max = Convert.ToSingle((row[5] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set damage_max of " + row[0] as string); }
try { crit_chance = Convert.ToSingle((row[6] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set crit_chance of " + row[0] as string); }
try { modID = Convert.ToSingle((row[7] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set modID of " + row[0] as string); }
try { spell_heal_min = Convert.ToSingle((row[8] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set spell_heal_min of " + row[0] as string); }
try { spell_heal_max = Convert.ToSingle((row[9] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set spell_heal_max of " + row[0] as string); }
try { mana_cost = Convert.ToSingle((row[10] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set mana_cost of " + row[0] as string); }
try { stamina_cost = Convert.ToSingle((row[11] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set stamina_cost of " + row[0] as string); }
try { cast_chance = Convert.ToSingle((row[12] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set cast_chance of " + row[0] as string); }
try { spell_turns = Convert.ToSingle((row[15] as string).Replace("%", "").Replace("x", "")); }
catch { Console.WriteLine("Failed to set spell_turns of " + row[0] as string); }
try { allowed_in_pvp = (row[17] as string) == "Yes" ? true : false; }
catch { Console.WriteLine("Failed to set allowed_in_pvp of " + row[0] as string); }
try { allowed_on_world_bosses = (row[18] as string) == "Yes" ? true : false; }
catch { Console.WriteLine("Failed to set allowed_on_world_bosses of " + row[0] as string); }
Resources.Spell spell = new Resources.Spell
(
row[0] as string,
row[1] as string,
row[2] as string,
row[3] as string,
damage_min,
damage_max,
crit_chance,
modID,
spell_heal_min,
spell_heal_max,
mana_cost,
stamina_cost,
cast_chance,
row[13] as string,
row[14] as string,
spell_turns,
row[16] as string,
row[17] as string,
allowed_in_pvp,
allowed_on_world_bosses
);
Gameplay.spellDatabase.Add(row[0] as string, spell);
//}
//catch (Exception ex)
//{
// Console.WriteLine("Failed to parse {0}, because {1}", row[0] as string, ex.ToString());
//}
}
}
else
{
Console.WriteLine("No data found.");
}
stopWatch.Stop();
TimeSpan ts = stopWatch.Elapsed;
Console.WriteLine("Loaded {0} spells into the spell database in {1}ms", loaded, ts.Milliseconds);
//Console.Read();
Console.WriteLine("Loading Dependencies...");
//Open the file that contains the bot login token, this txt file is set up with the launcher.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
System.IO.StreamReader file = new System.IO.StreamReader(@"Resources\BotKey.txt");
//Read the opened file and set its contents to the token string.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
TOKEN = file.ReadToEnd();
if (File.Exists(@"Resources\MonsterCollection.AsteriaMonsters"))
{
using (StreamReader sr = File.OpenText(@"Resources\MonsterCollection.AsteriaMonsters"))
{
string s = "";
while ((s = sr.ReadLine()) != null)
{
string[] temp = s.Split('Æ');
if (temp[0] == "Monster")
{
//0[Type], 1[ID], 2[x], 3[y] 4[z], 5[Name], [6]minLv, [7]maxLv, [8]difficulty, [9]tankiness, [10]flavor, [11]link
Gameplay.designedEnemiesIndex.Add(new Resources.DesignedEnemy(temp[5], temp[10], temp[11], temp[6], temp[7], temp[8], temp[9]));
}
}
}
}
else
{
File.Create(@"Resources\MonsterCollection.AsteriaMonsters");
Console.WriteLine("Failed to find a valid monster collection...");
}
//Begin the async task for the bot.
Main2(args);
}
public static async Task <Tuple <bool, string> > ProcessPurchase(string item, string name, int qty, ValueRange itemResult = null, ValueRange dkpResult = null, ValueRange reqResult = null)
{
// find the actual item from the store
string _item = "";
int _cost = 0;
int _stock = 0;
int _lotSize = 1;
int _itemIdx = -1;
if (itemResult == null)
{
itemResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPStoreTab);
}
if (itemResult.Values != null && itemResult.Values.Count > 0)
{
_itemIdx = GoogleSheetsHelper.Instance.IndexInRange(itemResult, item);
if (_itemIdx >= 0)
{
_item = itemResult.Values[_itemIdx][0].ToString();
int.TryParse(itemResult.Values[_itemIdx][1].ToString(), out _cost);
int.TryParse(itemResult.Values[_itemIdx][2].ToString(), out _stock);
int.TryParse(itemResult.Values[_itemIdx][3].ToString(), out _lotSize);
}
}
if (string.IsNullOrEmpty(_item))
{
return(new Tuple <bool, string>(false, $"Unable to find {item} in the DKP store! Use !store to list available items."));
}
// check available clan stock of item
if (qty * _lotSize > _stock)
{
return(new Tuple <bool, string>(false, $"Unable to purchase {item} for {name}: Out of stock"));
}
// check available balance of player DKP
int _dkp = 0;
int _bonusPriority = 0;
int _limit = 0;
int _playerIdx = -1;
if (dkpResult == null)
{
dkpResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPMainTab);
}
if (dkpResult.Values != null && dkpResult.Values.Count > 0)
{
_playerIdx = GoogleSheetsHelper.Instance.IndexInRange(dkpResult, name);
if (_playerIdx >= 0)
{
int.TryParse(dkpResult.Values[_playerIdx][2].ToString(), out _dkp);
int.TryParse(dkpResult.Values[_playerIdx][6].ToString(), out _bonusPriority);
int.TryParse(dkpResult.Values[_playerIdx][7].ToString(), out _limit);
}
}
if (_dkp < _cost * qty)
{
return(new Tuple <bool, string>(false, $"Insufficient DKP [{_dkp}] for {name} to cover the cost [{_cost * qty}] of {qty} {_item}"));
}
if (_limit + qty * _lotSize > Config.Global.Commands.Buy.WeeklyLimit)
{
return(new Tuple <bool, string>(false, $"{name} is unable to purchase {_item} due to weekly limit of {_limit}/{Config.Global.Commands.Buy.WeeklyLimit}"));
}
IList <Request> transaction = new List <Request>();
// find any existing requests from this user for this item
if (reqResult == null)
{
reqResult = await GoogleSheetsHelper.Instance.GetAsync(Config.Global.DKPRequestsTab);
}
if (reqResult.Values != null && reqResult.Values.Count > 0)
{
int reqIdx = GoogleSheetsHelper.Instance.SubIndexInRange(reqResult, name, 1, _item);
if (reqIdx >= 0)
{
int.TryParse(reqResult.Values[reqIdx][2].ToString(), out int currentQty);
currentQty -= qty;
GridRange staticReqRange = GetTabRange(EDKPTabs.Requests);
GridRange reqRange = new GridRange
{
SheetId = staticReqRange.SheetId,
StartRowIndex = reqIdx,
EndRowIndex = reqIdx + 1,
StartColumnIndex = staticReqRange.EndColumnIndex - 1,
EndColumnIndex = staticReqRange.EndColumnIndex
};
if (currentQty > 0)
{
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = reqRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = currentQty
}
}
}
}
}
}
});
}
else
{
transaction.Add(new Request
{
DeleteDimension = new DeleteDimensionRequest
{
Range = new DimensionRange
{
Dimension = "ROWS",
SheetId = reqRange.SheetId,
StartIndex = reqRange.StartRowIndex,
EndIndex = reqRange.EndRowIndex
}
}
});
}
}
}
// log the purchase
GridRange logRange = GetTabRange(EDKPTabs.Log);
transaction.Add(new Request
{
AppendCells = new AppendCellsRequest
{
SheetId = logRange.SheetId,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = name
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _cost * qty * -1
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = DateTime.Now.ToOADate()
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = Config.Global.Commands.Buy.PurchaseReason
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = _item
}
}
}
}
}
}
});
// log the priority (if necessary)
if (_bonusPriority > 0)
{
GridRange priorityRange = GetTabRange(EDKPTabs.PriorityLog);
transaction.Add(new Request
{
AppendCells = new AppendCellsRequest
{
SheetId = priorityRange.SheetId,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = name
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = Math.Min(_bonusPriority, _cost * qty) * -1
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = DateTime.Now.ToOADate()
}
},
new CellData
{
UserEnteredValue = new ExtendedValue
{
StringValue = _item
}
}
}
}
}
}
});
}
// update the stock
GridRange staticStoreRange = GetTabRange(EDKPTabs.Store);
GridRange storeRange = new GridRange
{
SheetId = staticStoreRange.SheetId,
StartRowIndex = staticStoreRange.StartRowIndex + _itemIdx,
EndRowIndex = staticStoreRange.StartRowIndex + _itemIdx + 1,
StartColumnIndex = staticStoreRange.StartColumnIndex + 2,
EndColumnIndex = staticStoreRange.StartColumnIndex + 3
};
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = storeRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _stock - (qty * _lotSize)
}
}
}
}
}
}
});
// update the weekly limit
GridRange staticMainRange = GetTabRange(EDKPTabs.Main);
GridRange dkpRange = new GridRange
{
SheetId = staticMainRange.SheetId,
StartRowIndex = staticMainRange.StartRowIndex + _playerIdx,
EndRowIndex = staticMainRange.StartRowIndex + _playerIdx + 1,
StartColumnIndex = staticMainRange.StartColumnIndex + 7,
EndColumnIndex = staticMainRange.EndColumnIndex
};
transaction.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = dkpRange,
Fields = "*",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue = new ExtendedValue
{
NumberValue = _limit + qty * _lotSize
}
}
}
}
}
}
});
BatchUpdateSpreadsheetResponse txResponse = await GoogleSheetsHelper.Instance.TransactionAsync(transaction);
return(new Tuple <bool, string>(true, $"{name} purchased {qty} {_item} for {_cost * qty} DKP"));
}
/// <summary>
/// Called when the Plugin is called by VoiceAttack.
/// </summary>
/// <param name="vaProxy">A connection to VoiceAttack, used for passing back values.</param>
public static void VA_Invoke1(dynamic vaProxy)
{
// Set in progress Variable (for Async Calls)
vaProxy.SetBoolean("ReadingGoogleSheet", true);
// Clear all previous variables.
foreach (var variable in voiceAttackVariables)
{
vaProxy.SetText(variable, null);
}
voiceAttackVariables.Clear();
// Get the Google Sheet Id, which should be in the Context, Exit if it is Empty or Null
string sheetId = vaProxy.Context;
if (string.IsNullOrEmpty(sheetId) || sheetId == "ClearData")
{
vaProxy.SetBoolean("ReadingGoogleSheet", false);
return;
}
// They have made an authorization request, so call the authorization and exit
if (sheetId == "Authorize")
{
VA_Init1(vaProxy);
vaProxy.SetBoolean("ReadingGoogleSheet", false);
return;
}
// A variable named "RequestSheetRange" specifies what data to get.
string range = vaProxy.GetText("RequestSheetRange");
if (range != null)
{
if (service == null)
{
vaProxy.SetText("SheetRange", "Google API Connection is not Initialized!");
voiceAttackVariables.Add("SheetRange"); // Include this variable into the list for clearing
}
// Get data from Spreadsheet
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(sheetId, range);
cts = new CancellationTokenSource();
var requestTask = request.ExecuteAsync(cts.Token);
requestTask.Wait();
cts = null;
if (requestTask.IsCanceled)
{
vaProxy.SetBoolean("ReadingGoogleSheet", false);
return;
}
ValueRange response = requestTask.Result;
var values = response.Values;
if (values != null && values.Count > 0)
{
// We got some data. Data will be passed back to Voice Attack in two ways.
// 1) A variable named "SheetRange" will contain all values in the specified range Comma Delimted, with each row on its own line
// 2) Each individual value will be in a variable named like "SheetRange[<R>][<C>]" where "<R>" is the Row Number (0 based) and "<C>" is the Column Number (0 based).
var sb = new StringBuilder();
for (int r = 0; r < values.Count; r++)
{
var newLine = true;
for (int c = 0; c < values[r].Count; c++)
{
if (!newLine)
{
sb.Append(", ");
}
newLine = false;
sb.Append(values[r][c]);
// Alright, store the data in a variable named "SheetRange[<R>][<C>]" where "<R>" is the Row Number (0 based) and "<C>" is the Column Number (0 based).
var cellName = string.Format("SheetRangeRow{0}Col{1}", r, c);
vaProxy.SetText(cellName, values[r][c].ToString());
voiceAttackVariables.Add(cellName); // Keep a list of variables so that we can clear them on next call.
}
sb.AppendLine();
}
// Put all data into "SheetRange"
vaProxy.SetText("SheetRange", sb.ToString());
voiceAttackVariables.Add("SheetRange"); // Include this variable into the list for clearing
}
}
vaProxy.SetBoolean("ReadingGoogleSheet", false);
}
private int mouseMoveCount = 0; //See sysSensibilityPAN
/// <summary>
/// Get Y-Viewport(min,max) of all curves in the current X-Vieport defined by [myViewportX].
/// The function used Y-Axis data stored in [mySeriesY]
/// </summary>
/// <returns></returns>
private ValueRange GetViewportY()
{
ValueRange range = new ValueRange();
for (int idx = 0; idx < this.myGraphPane.CurveList.Count; idx++)
{
Libs.GetRangeY(this.myGraphPane.CurveList[idx], this.myViewportX.Min, this.myViewportX.Max, ref range);
}
range.Max += Settings.sysViewSpaceAtTOP;
range.Min -= Settings.sysViewSpaceAtBOT;
return range;
}
private static RangeGraph<ValueRange> ReadRangeOfRanges(BinaryReader reader)
{
reader.ReadBoolean();
var typeName = reader.ReadString();
var start = ReadValueRange(reader);
var end = ReadValueRange(reader);
reader.ReadByte();
var count = ReadNumberMostlyBelow255(reader);
var arrayType = reader.ReadByte();
var arrayTypeName = reader.ReadString();
var values = new ValueRange[count];
for (int i = 0; i < count; i++)
values[i] = ReadValueRange(reader);
return new RangeGraph<ValueRange>(values);
}
public void Union(IItemTrackSelectionInfo t)
{
if (m_MarkedTimes == null)
{
m_MarkedTimes = t.MarkedTimes;
}
else if (t.MarkedTimes != null)
{
foreach (IValueRange<DateTime> i in t.MarkedTimes)
{
m_MarkedTimes.Add(i);
}
}
if (m_MarkedDistances == null)
{
m_MarkedDistances = t.MarkedDistances;
}
else if (t.MarkedDistances != null)
{
foreach (IValueRange<double> i in t.MarkedDistances)
{
m_MarkedDistances.Add(i);
}
}
if (m_SelectedTime == null)
{
m_SelectedTime = t.SelectedTime;
}
else if (t.SelectedTime != null)
{
DateTime Lower = m_SelectedTime.Lower.CompareTo(t.SelectedTime.Lower) < 0 ?
m_SelectedTime.Lower : t.SelectedTime.Lower;
DateTime Upper = m_SelectedTime.Upper.CompareTo(t.SelectedTime.Upper) > 0 ?
m_SelectedTime.Upper : t.SelectedTime.Upper;
m_SelectedTime = new ValueRange<DateTime>(Lower, Upper);
}
if (m_SelectedDistance == null)
{
m_SelectedDistance = t.SelectedDistance;
}
else if (t.SelectedDistance != null)
{
double Lower = Math.Min(m_SelectedDistance.Lower, t.SelectedDistance.Lower);
double Upper = Math.Max(m_SelectedDistance.Upper, t.SelectedDistance.Upper);
m_SelectedDistance = new ValueRange<double>(Lower, Upper);
}
}
public SamplingEvent(CompoundIdentity id, string name, CompoundIdentity fieldTripId, CompoundIdentity principalOrgId, ValueRange <DateTime> range, string description)
{
MethodContract.NotNullOrEmpty(id, nameof(id));
MethodContract.NotNullOrEmpty(name, nameof(name));
MethodContract.NotNullOrEmpty(fieldTripId, nameof(fieldTripId));
MethodContract.NotNullOrEmpty(principalOrgId, nameof(principalOrgId));
this.Identity = id;
this.name = name;
this.tripId = fieldTripId;
this.orgId = principalOrgId;
this.dates = range;
this.Description = description;
}
public async Task <string> AddThumbnails()
{
Response.Headers.Add("Cache-Control", "no-cache");
string result = string.Empty;
string newFolderId = null;
string imagesFolderId = null;
string accountName = this._httpContextAccessor.HttpContext.Request.Headers[DriveImportConstants.VTEX_ACCOUNT_HEADER_NAME];
FolderIds folderIds = await _driveImportRepository.LoadFolderIds(accountName);
if (folderIds != null)
{
newFolderId = folderIds.NewFolderId;
imagesFolderId = folderIds.ImagesFolderId;
ListFilesResponse imageFiles = new ListFilesResponse();
imageFiles.Files = new List <GoogleFile>();
string nextPageToken = string.Empty;
do
{
ListFilesResponse listFilesResponse = await _googleDriveService.ListImagesInFolder(newFolderId, nextPageToken);
imageFiles.Files.AddRange(listFilesResponse.Files);
nextPageToken = listFilesResponse.NextPageToken;
} while (!string.IsNullOrEmpty(nextPageToken));
ListFilesResponse spreadsheets = await _googleDriveService.ListSheetsInFolder(imagesFolderId);
if (imageFiles != null && spreadsheets != null)
{
var sheetIds = spreadsheets.Files.Select(s => s.Id);
if (sheetIds != null)
{
foreach (var sheetId in sheetIds)
{
Dictionary <string, int> headerIndexDictionary = new Dictionary <string, int>();
string sheetContent = await _googleDriveService.GetSheet(sheetId, string.Empty);
GoogleSheet googleSheet = JsonConvert.DeserializeObject <GoogleSheet>(sheetContent);
string valueRange = googleSheet.ValueRanges[0].Range;
string sheetName = valueRange.Split("!")[0];
string[] sheetHeader = googleSheet.ValueRanges[0].Values[0];
int headerIndex = 0;
int rowCount = googleSheet.ValueRanges[0].Values.Count();
int writeBlockSize = rowCount;
foreach (string header in sheetHeader)
{
headerIndexDictionary.Add(header.ToLower(), headerIndex);
headerIndex++;
}
string[][] arrValuesToWrite = new string[writeBlockSize][];
for (int index = 1; index < rowCount; index++)
{
string imageFileName = string.Empty;
string[] dataValues = googleSheet.ValueRanges[0].Values[index];
if (headerIndexDictionary.ContainsKey("image") && headerIndexDictionary["image"] < dataValues.Count())
{
imageFileName = dataValues[headerIndexDictionary["image"]];
}
GoogleFile file = imageFiles.Files.FirstOrDefault(f => f.Name.Equals(imageFileName));
if (file != null)
{
string[] row = new string[headerIndexDictionary.Count];
row[headerIndexDictionary["thumbnail"]] = $"=IMAGE(\"{ file.ThumbnailLink}\")";
arrValuesToWrite[index - 1] = row;
}
}
string lastColumn = ((char)(headerIndexDictionary.Count + 65)).ToString();
ValueRange valueRangeToWrite = new ValueRange
{
Range = $"{sheetName}!A2:{lastColumn}{rowCount + 1}",
Values = arrValuesToWrite
};
await _googleDriveService.WriteSpreadsheetValues(sheetId, valueRangeToWrite);
}
}
}
}
return(result);
}
void XulyVeThuong()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
for (int i = 0; i < NhanVienName.Count; i++)
{
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 1 and LoaiGiaoDich in (4,13,14) and LoaiKhachHang <> 3 and convert(date,NgayGD) > convert(date,getdate()-10) and IDKhachHang <> 87451 and NVGiaoDich in (0,{NhanVienID[i]})", false);
String range = $"{NhanVienName[i]}!A3:E";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
#region Cập nhật khách
foreach (var row in values)
{
#region Điều kiện
if (row.Count < 2)//có ít nhất 2 cột có dữ liệu
{
continue;
}
else if (row.Count > 1)
{
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
if (row.Count == 5)
{
if (row[4].ToString() != string.Empty)//Xóa Giao dịch quá hạn
{
if (DateTime.ParseExact(row[4].ToString().Split(':')[0].Replace("-", "/"), "dd/MM/yyyy", null).Subtract(DateTime.Now.AddDays(-7)).Days < 0)
{
continue;
}
}
}
if (row.Count == 2)
{
if (row[1].ToString() == string.Empty)// Đại lý và CTV trống
{
lstAPI.Add(aPI("Thiếu thông tin khách", row[0].ToString(), row[1].ToString(), string.Empty, string.Empty, string.Empty));
continue;
}
}
else
{
if (row[1].ToString() == string.Empty && row[2].ToString() == string.Empty)// Đại lý và CTV trống
{
lstAPI.Add(aPI("Thiếu thông tin khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), (row.Count == 4) ? row[3].ToString() : string.Empty, string.Empty));
continue;
}
}
}
#endregion
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => w.MaCho.Replace(" ", string.Empty).Equals(row[0].ToString().ToUpper().Replace(" ", string.Empty)) || (w.SoVeVN ?? string.Empty).Replace(" ", string.Empty).Equals(row[0].ToString().Replace(" ", string.Empty))).ToList();
if (GiaoDinhTamLuu.Count > 0)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.NVGiaoDich = NhanVienID[i];
ac.GhiChu = row.Count > 3 ? row[3].ToString() : string.Empty;
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
}
else// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
}
if (ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich && ac.IDKhachHang == GiaoDinhTamLuu[0].IDKhachHang)
{
continue;
}
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang > 0)//Thay đổi dữ liệu trên hệ thống
{
string GhiChuTam = string.Format("GExcel : [{1}] {0} - ", row[0].ToString(), NhanVienName[i]);
if (ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
GhiChuTam += "[";
if (GhiChuTam == string.Format("GExcel : [{1}] {0} - [", row[0].ToString(), NhanVienName[i]) && ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich)
{
continue;
}
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].GhiChu = ac.GhiChu;
GiaoDinhTamLuu[u].NVGiaoDich = ac.NVGiaoDich;
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 3 ? row[3].ToString() : string.Empty, row.Count > 4 ? row[4].ToString() : string.Empty));
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy vé", row[0].ToString(), row[1].ToString(), row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 3 ? row[3].ToString() : string.Empty, row.Count > 4 ? row[4].ToString() : string.Empty));
}
}
#endregion
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
}
public MultilineString([CanBeNull] string value, ValueRange<int> lineIndexRange)
{
Lines = SplitIntoLines(value).AsReadOnly();
LineIndexRange = lineIndexRange;
}
void XuLyVeHoanBk()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
for (int i = 0; i < NhanVienName.Count; i++)
{
String range = $"{NhanVienName[i]}!F3:L";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
List <string> str = new List <string>();
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
str.Add(row[0].ToString());
}
if (str.Count < 0)
{
return;
}
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and SoVeVN in ('{String.Join("' ,'", str.ToArray())}')", false);
//List<GiaoDichO> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 0 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and NVGiaoDich in (0,{NhanVienID[i]})", false);
//List<GiaoDichO> DaNhan2 = giaoDichD.DuLieu($"TinhCongNo = 1 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3 and NVGiaoDich in (0,{NhanVienID[i]})", false);
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => w.SoVeVN.Replace(" ", "").Equals(row[0].ToString().Replace(" ", "")) && !w.TinhCongNo).ToList();
List <O_GIAODICH> DaNhan = lstGiaoDich.Where(w => w.SoVeVN.Replace(" ", "").Equals(row[0].ToString().Replace(" ", "")) && w.TinhCongNo).ToList();
if (GiaoDinhTamLuu.Count == 1)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.NVGiaoDich = NhanVienID[i];
ac.LoaiKhachHang = 0;
ac.GhiChu = row.Count > 5 ? row[5].ToString() : string.Empty;
ac.GiaHeThong = row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : -1;
ac.GiaHoan = row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : -1;
if (GiaoDinhTamLuu[0].NVGiaoDich != NhanVienID[i] && GiaoDinhTamLuu[0].NVGiaoDich != 0)
{
lstAPI.Add(aPI($"Vé của {lstNV.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].NVGiaoDich)).ToList()[0].TenDangNhapCty}", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
continue;
}// Vé đã có người nhận
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
else if (row.Count > 3)
{
if (row[2].ToString() != string.Empty)// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
}
string GhiChuTam = string.Format("GExcel Hoàn : [{1}] {0} - ", row[0].ToString(), NhanVienName[i]);
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
if (ac.GiaHeThong != -1 && ac.GiaHeThong != GiaoDinhTamLuu[0].GiaHeThong)
{
GhiChuTam += string.Format("[Phí hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHeThong.ToString("#,##0"), ac.GiaHeThong.ToString("#,##0"));
}
if (ac.GiaHoan != -1 && ac.GiaHoan != GiaoDinhTamLuu[0].GiaHoan)
{
GhiChuTam += string.Format("[Giá hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHoan.ToString("#,##0"), ac.GiaHoan.ToString("#,##0"));
}
GhiChuTam += "[";
if (GhiChuTam == string.Format("GExcel Hoàn : [{1}] {0} - [", row[0].ToString(), NhanVienName[i]) && ac.NVGiaoDich == GiaoDinhTamLuu[0].NVGiaoDich)
{
continue;
}
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].NVGiaoDich = ac.NVGiaoDich;
GiaoDinhTamLuu[u].GhiChu = ac.GhiChu;
if (ac.LoaiKhachHang > 0)
{
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
}
if (ac.GiaHeThong > 10000)
{
GiaoDinhTamLuu[u].GiaHeThong = ac.GiaHeThong;
}
if (ac.GiaHoan > 10000)
{
GiaoDinhTamLuu[u].GiaHoan = ac.GiaHoan;
}
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else if (GiaoDinhTamLuu.Count == 0)
{
if (DaNhan.Count > 0)
{
if (DaNhan[0].NVGiaoDich != NhanVienID[i] && DaNhan[0].NVGiaoDich != 0)
{
lstAPI.Add(aPI($"Vé đã nhận cho {lstNV.Where(w => w.ID.Equals(DaNhan[0].NVGiaoDich)).ToList()[0].TenDangNhapCty}", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
else
{
lstAPI.Add(aPI("Không tìm thấy hoàn", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
else
{
lstAPI.Add(aPI("Trên 2 giao dịch", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 5 ? row[5].ToString() : string.Empty, row.Count > 6 ? row[6].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0));
}
}
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.PhiHoanDaiLy, aPI.GiaHoanDaiLy, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
}
private void OnRangeChanged() {
Collider.radius = Range;
RangeSpan = new ValueRange<float>(0.9F * Range, 1.10F * Range);
if (enabled) {
D.Log("{0}.{1}.Range changed to {2:0.00}.", ParentFullName, _transform.name, Range);
Collider.enabled = false;
AllTargets.ForAll(t => Remove(t)); // clears both AllTargets and EnemyTargets
Collider.enabled = true; // TODO unconfirmed - this should repopulate the Targets when re-enabled with new radius
}
}
void XuLyVeHoanKt()
{
D_GIAODICH giaoDichD = new D_GIAODICH();
List <O_GIAODICH> lstGiaoDich = giaoDichD.DuLieu($"TinhCongNo = 0 and LoaiGiaoDich = 9 and LoaiKhachHang <> 3", false);
String range = $"Kế toán CN!A2:I";
var request = SService.Spreadsheets.Values.Get(SpreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
List <APISheet> lstAPI = new List <APISheet>();
if (values != null)
{
foreach (var row in values)
{
if (row.Count < 1)//có ít nhất 2 cột có dữ liệu
{
continue;
}
if (row[0].ToString() == string.Empty)//không mã chỗ số vé
{
continue;
}
List <O_GIAODICH> GiaoDinhTamLuu = lstGiaoDich.Where(w => (w.SoVeVN ?? string.Empty).Replace(" ", string.Empty).Equals(row[0].ToString().Replace(" ", string.Empty))).ToList();
if (GiaoDinhTamLuu.Count == 1)// Mã chỗ có tồn tại theo điều kiện
{
O_GIAODICH ac = new O_GIAODICH();
ac.LoaiKhachHang = 0;
ac.GhiChu = row.Count > 7 ? row[7].ToString() : string.Empty;
ac.GiaHeThong = row.Count > 3 ? XuLyDuLieu.ConvertStringToLong(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : -1;
ac.GiaHoan = row.Count > 4 ? XuLyDuLieu.ConvertStringToLong(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : -1;
ac.GiaNet = row.Count > 5 ? XuLyDuLieu.ConvertStringToLong(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : -1;
ac.HangHoan = row.Count > 6 ? XuLyDuLieu.ConvertStringToLong(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : -1;
if (row.Count > 1)
{
if (row[1].ToString() != string.Empty)// tìm DL theo sheet
{
if (lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstDaiLy.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[1].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 1;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
else if (row.Count > 3)
{
if (row[2].ToString() != string.Empty)// tìm CTV theo sheet
{
if (lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).Count() > 0)
{
ac.IDKhachHang = lstCTV.Where(w => w.Ten.TrimEnd().ToLower().Normalize(NormalizationForm.FormKD).Equals(row[2].ToString().TrimEnd().ToLower().Normalize(NormalizationForm.FormKD))).First().ID;
ac.LoaiKhachHang = 2;
}
if (ac.LoaiKhachHang == 0)
{
lstAPI.Add(aPI("Không tìm thấy khách", row[0].ToString(), row[1].ToString(), row[2].ToString(), row[5].ToString(), row[6].ToString(), ac.GiaHeThong, ac.GiaHoan));
continue;
}
}
}
}
string GhiChuTam = string.Format("GExcel Hoàn : Nhận hoàn [{0}] - ", row[0].ToString());
if (ac.LoaiKhachHang > 0 && ac.IDKhachHang != GiaoDinhTamLuu[0].IDKhachHang)
{
GhiChuTam += string.Format("[{0} => {1}] ", lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).Count() > 0 ? lstALL.Where(w => w.ID.Equals(GiaoDinhTamLuu[0].IDKhachHang)).ToList()[0].Ten : string.Empty, lstALL.Where(w => w.ID.Equals(ac.IDKhachHang)).ToList()[0].Ten);
}
if (ac.GiaHeThong > 10 && ac.GiaHeThong != GiaoDinhTamLuu[0].GiaHeThong)
{
GhiChuTam += string.Format("[Phí hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHeThong.ToString("#,##0"), ac.GiaHeThong.ToString("#,##0"));
}
if (ac.GiaHoan > 10 && ac.GiaHoan != GiaoDinhTamLuu[0].GiaHoan)
{
GhiChuTam += string.Format("[Giá hoàn {0} => {1}] ", GiaoDinhTamLuu[0].GiaHoan.ToString("#,##0"), ac.GiaHoan.ToString("#,##0"));
}
if (ac.GiaNet > 10 && ac.GiaNet != GiaoDinhTamLuu[0].GiaNet)
{
GhiChuTam += string.Format("[Phí hãng {0} => {1}] ", GiaoDinhTamLuu[0].GiaNet.ToString("#,##0"), ac.GiaNet.ToString("#,##0"));
}
if (ac.HangHoan > 10 && ac.HangHoan != GiaoDinhTamLuu[0].HangHoan)
{
GhiChuTam += string.Format("[Hãng hoàn {0} => {1}] ", GiaoDinhTamLuu[0].HangHoan.ToString("#,##0"), ac.HangHoan.ToString("#,##0"));
}
GhiChuTam += "[";
for (int u = 0; u < GiaoDinhTamLuu.Count; u++)
{
GiaoDinhTamLuu[u].TinhCongNo = true;
if (ac.LoaiKhachHang > 0)
{
GiaoDinhTamLuu[u].LoaiKhachHang = ac.LoaiKhachHang;
GiaoDinhTamLuu[u].IDKhachHang = ac.IDKhachHang;
}
if (ac.GiaHeThong > 10)
{
GiaoDinhTamLuu[u].GiaHeThong = ac.GiaHeThong;
}
if (ac.GiaHoan > 10)
{
GiaoDinhTamLuu[u].GiaHoan = ac.GiaHoan;
}
if (ac.GiaNet > 10)
{
GiaoDinhTamLuu[u].GiaNet = ac.GiaNet;
}
if (ac.HangHoan > 100)
{
GiaoDinhTamLuu[u].HangHoan = ac.HangHoan;
}
GhiChuTam += GiaoDinhTamLuu[u].ID;
GhiChuTam += (u != GiaoDinhTamLuu.Count - 1) ? ", " : "]";
}
if (giaoDichD.ThucThiSua(GiaoDinhTamLuu) > 0)
{
Dictionary <string, object> dic1 = new Dictionary <string, object>();
dic1.Add("FormName", "Hệ thống");
dic1.Add("MaCho", row[0].ToString());
dic1.Add("NoiDung", GhiChuTam);
dic1.Add("LoaiKhachHang", 0);
dic1.Add("Ma", 0);
new D_LS_GIAODICH().ThemMoi(dic1);
}
}
else if (GiaoDinhTamLuu.Count == 0)
{
lstAPI.Add(aPI("Không tìm thấy hoàn", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0, row.Count > 5 ? long.Parse(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : 0, row.Count > 6 ? long.Parse(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : 0));
}
else
{
lstAPI.Add(aPI("Trên 2 giao dịch", row[0].ToString(), row.Count > 1 ? row[1].ToString() : string.Empty, row.Count > 2 ? row[2].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 7 ? row[7].ToString() : string.Empty, row.Count > 3 ? long.Parse(row[3].ToString() == string.Empty ? "0" : row[3].ToString()) : 0, row.Count > 4 ? long.Parse(row[4].ToString() == string.Empty ? "0" : row[4].ToString()) : 0, row.Count > 5 ? long.Parse(row[5].ToString() == string.Empty ? "0" : row[5].ToString()) : 0, row.Count > 6 ? long.Parse(row[6].ToString() == string.Empty ? "0" : row[6].ToString()) : 0));
}
}
#region Xóa
var requestbody = new ClearValuesRequest();
var request1 = SService.Spreadsheets.Values.Clear(requestbody, SpreadsheetId, range);
var Drequest1 = request1.Execute();
#endregion
#region Thêm lại các dòng lỗi
if (lstAPI.Count > 0)
{
var valueRange = new ValueRange();
List <IList <object> > lstList = new List <IList <object> >();
foreach (APISheet aPI in lstAPI)
{
var objectList = new List <object>()
{
aPI.MaCho, aPI.DaiLy, aPI.CTV, aPI.PhiHoanDaiLy, aPI.GiaHoanDaiLy, aPI.PhiHoan, aPI.GiaHoan, aPI.GhiChu, aPI.GhiChuHeThong
};
lstList.Add(objectList);
}
valueRange.Values = lstList;
var appendRequest = SService.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendResponse = appendRequest.Execute();
lstAPI.Clear();
}
#endregion
}
}
static void Main(string[] args)
{
UserCredential credential;
using (var stream =
new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
//credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "14g46VAUsxqAkevmNDKdrc0c5R3L1cOfN6PsBYz_2zf0";
String range = "A2:D";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/14g46VAUsxqAkevmNDKdrc0c5R3L1cOfN6PsBYz_2zf0/edit
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
foreach (var row in values)
{
// Print columns A and D, which correspond to indices 0 and 3.
//Console.WriteLine("{0},{1},{2},{3}", row[0], row [1], row[2], row[3]);
//Send data to sqlServer Database "InventoryTest"
//change Data Source to name of server
//use on desktop SqlConnection inv = new SqlConnection("Data Source=ALEX-PC; Initial Catalog=InventoryTest;Integrated Security=True;Pooling=False");
SqlConnection inv = new SqlConnection("Data Source=DESKTOP-VB076EU;Initial Catalog=InventoryTest;Integrated Security=True");
{
//Attempt to get the EmployeeID
//String that sets the employee name for current row
string employeeName = row[3].ToString();
//opens connection to server
inv.Open();
//sql command that selects the employeeID
SqlCommand getEmployeeID = new SqlCommand("SELECT EmployeeID FROM Employees WHERE EmployeeName = '" + employeeName + "';", inv);
//sets the employeeID to the ID that was grabbed from getEmployeeID command
int employeeID = Convert.ToInt32(getEmployeeID.ExecuteScalar());
//closes connection
inv.Close();
//Attempt to insert part from the Google Sheet
SqlCommand insertPart =
new SqlCommand("insert into PartsAdded(EntryTime, PartNumber, Quantity, EmployeeID, EmployeeName)"
+ "values(CURRENT_TIMESTAMP," +
"@PartNumber," +
"@Quantity," +
"@EmployeeID," +
"@EmployeeName)", inv);
//inserts values within sql command
insertPart.Parameters.AddWithValue("@PartNumber", row[1]);
insertPart.Parameters.AddWithValue("@Quantity", row[2]);
insertPart.Parameters.AddWithValue("@EmployeeID", employeeID);
insertPart.Parameters.AddWithValue("@EmployeeName", row[3]);
//verifies user the parameter is working
Console.WriteLine("InsParams Working");
//opens the inv then executes nonquery then closes connection
inv.Open();
Console.WriteLine("inv opened");
insertPart.ExecuteNonQuery();
Console.WriteLine("NonQuery Executed");
inv.Close();
Console.WriteLine("inv closed");
}
}
}
else
{
Console.WriteLine("No data found.");
}
Console.Read();
}
public CompoundIdentity CreateSampleEvent(CompoundIdentity fieldTripId, CompoundIdentity princOrgId, string name, string desc, ValueRange <DateTime> range)
{
if (fieldTripId != null && !fieldTripId.IsEmpty && princOrgId != null && !princOrgId.IsEmpty && !string.IsNullOrEmpty(name))
{
ISampleEventProvider prov = FieldActivityManager.Instance.GetSampleEventProvider(this.ctx);
if (prov != null)
{
SamplingEvent evt = prov.Create(name, fieldTripId, princOrgId, range, desc);
if (evt != null)
{
return(evt.Identity);
}
}
}
return(null);
}
/// <summary>
/// Serialize to a JSON object
/// </summary>
public new void SerializeJson(Utf8JsonWriter writer, JsonSerializerOptions options, bool includeStartObject = true)
{
if (includeStartObject)
{
writer.WriteStartObject();
}
((Fhir.R4.Models.Element) this).SerializeJson(writer, options, false);
if (!string.IsNullOrEmpty(Url))
{
writer.WriteString("url", (string)Url !);
}
if (_Url != null)
{
writer.WritePropertyName("_url");
_Url.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueBase64Binary))
{
writer.WriteString("valueBase64Binary", (string)ValueBase64Binary !);
}
if (_ValueBase64Binary != null)
{
writer.WritePropertyName("_valueBase64Binary");
_ValueBase64Binary.SerializeJson(writer, options);
}
if (ValueBoolean != null)
{
writer.WriteBoolean("valueBoolean", (bool)ValueBoolean !);
}
if (!string.IsNullOrEmpty(ValueCanonical))
{
writer.WriteString("valueCanonical", (string)ValueCanonical !);
}
if (_ValueCanonical != null)
{
writer.WritePropertyName("_valueCanonical");
_ValueCanonical.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueCode))
{
writer.WriteString("valueCode", (string)ValueCode !);
}
if (_ValueCode != null)
{
writer.WritePropertyName("_valueCode");
_ValueCode.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueDate))
{
writer.WriteString("valueDate", (string)ValueDate !);
}
if (_ValueDate != null)
{
writer.WritePropertyName("_valueDate");
_ValueDate.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueDateTime))
{
writer.WriteString("valueDateTime", (string)ValueDateTime !);
}
if (_ValueDateTime != null)
{
writer.WritePropertyName("_valueDateTime");
_ValueDateTime.SerializeJson(writer, options);
}
if (ValueDecimal != null)
{
writer.WriteNumber("valueDecimal", (decimal)ValueDecimal !);
}
if (_ValueDecimal != null)
{
writer.WritePropertyName("_valueDecimal");
_ValueDecimal.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueId))
{
writer.WriteString("valueId", (string)ValueId !);
}
if (_ValueId != null)
{
writer.WritePropertyName("_valueId");
_ValueId.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueInstant))
{
writer.WriteString("valueInstant", (string)ValueInstant !);
}
if (_ValueInstant != null)
{
writer.WritePropertyName("_valueInstant");
_ValueInstant.SerializeJson(writer, options);
}
if (ValueInteger != null)
{
writer.WriteNumber("valueInteger", (int)ValueInteger !);
}
if (!string.IsNullOrEmpty(ValueMarkdown))
{
writer.WriteString("valueMarkdown", (string)ValueMarkdown !);
}
if (_ValueMarkdown != null)
{
writer.WritePropertyName("_valueMarkdown");
_ValueMarkdown.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueOid))
{
writer.WriteString("valueOid", (string)ValueOid !);
}
if (_ValueOid != null)
{
writer.WritePropertyName("_valueOid");
_ValueOid.SerializeJson(writer, options);
}
if (ValuePositiveInt != null)
{
writer.WriteNumber("valuePositiveInt", (uint)ValuePositiveInt !);
}
if (!string.IsNullOrEmpty(ValueString))
{
writer.WriteString("valueString", (string)ValueString !);
}
if (_ValueString != null)
{
writer.WritePropertyName("_valueString");
_ValueString.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueTime))
{
writer.WriteString("valueTime", (string)ValueTime !);
}
if (_ValueTime != null)
{
writer.WritePropertyName("_valueTime");
_ValueTime.SerializeJson(writer, options);
}
if (ValueUnsignedInt != null)
{
writer.WriteNumber("valueUnsignedInt", (uint)ValueUnsignedInt !);
}
if (!string.IsNullOrEmpty(ValueUri))
{
writer.WriteString("valueUri", (string)ValueUri !);
}
if (_ValueUri != null)
{
writer.WritePropertyName("_valueUri");
_ValueUri.SerializeJson(writer, options);
}
if (!string.IsNullOrEmpty(ValueUrl))
{
writer.WriteString("valueUrl", (string)ValueUrl !);
}
if (_ValueUrl != null)
{
writer.WritePropertyName("_valueUrl");
_ValueUrl.SerializeJson(writer, options);
}
if (ValueUuid != null)
{
writer.WriteString("valueUuid", (Guid)ValueUuid !);
}
if (ValueAddress != null)
{
writer.WritePropertyName("valueAddress");
ValueAddress.SerializeJson(writer, options);
}
if (ValueAge != null)
{
writer.WritePropertyName("valueAge");
ValueAge.SerializeJson(writer, options);
}
if (ValueAnnotation != null)
{
writer.WritePropertyName("valueAnnotation");
ValueAnnotation.SerializeJson(writer, options);
}
if (ValueAttachment != null)
{
writer.WritePropertyName("valueAttachment");
ValueAttachment.SerializeJson(writer, options);
}
if (ValueCodeableConcept != null)
{
writer.WritePropertyName("valueCodeableConcept");
ValueCodeableConcept.SerializeJson(writer, options);
}
if (ValueCoding != null)
{
writer.WritePropertyName("valueCoding");
ValueCoding.SerializeJson(writer, options);
}
if (ValueContactPoint != null)
{
writer.WritePropertyName("valueContactPoint");
ValueContactPoint.SerializeJson(writer, options);
}
if (ValueCount != null)
{
writer.WritePropertyName("valueCount");
ValueCount.SerializeJson(writer, options);
}
if (ValueDistance != null)
{
writer.WritePropertyName("valueDistance");
ValueDistance.SerializeJson(writer, options);
}
if (ValueDuration != null)
{
writer.WritePropertyName("valueDuration");
ValueDuration.SerializeJson(writer, options);
}
if (ValueHumanName != null)
{
writer.WritePropertyName("valueHumanName");
ValueHumanName.SerializeJson(writer, options);
}
if (ValueIdentifier != null)
{
writer.WritePropertyName("valueIdentifier");
ValueIdentifier.SerializeJson(writer, options);
}
if (ValueMoney != null)
{
writer.WritePropertyName("valueMoney");
ValueMoney.SerializeJson(writer, options);
}
if (ValuePeriod != null)
{
writer.WritePropertyName("valuePeriod");
ValuePeriod.SerializeJson(writer, options);
}
if (ValueQuantity != null)
{
writer.WritePropertyName("valueQuantity");
ValueQuantity.SerializeJson(writer, options);
}
if (ValueRange != null)
{
writer.WritePropertyName("valueRange");
ValueRange.SerializeJson(writer, options);
}
if (ValueRatio != null)
{
writer.WritePropertyName("valueRatio");
ValueRatio.SerializeJson(writer, options);
}
if (ValueReference != null)
{
writer.WritePropertyName("valueReference");
ValueReference.SerializeJson(writer, options);
}
if (ValueSampledData != null)
{
writer.WritePropertyName("valueSampledData");
ValueSampledData.SerializeJson(writer, options);
}
if (ValueSignature != null)
{
writer.WritePropertyName("valueSignature");
ValueSignature.SerializeJson(writer, options);
}
if (ValueTiming != null)
{
writer.WritePropertyName("valueTiming");
ValueTiming.SerializeJson(writer, options);
}
if (ValueContactDetail != null)
{
writer.WritePropertyName("valueContactDetail");
ValueContactDetail.SerializeJson(writer, options);
}
if (ValueContributor != null)
{
writer.WritePropertyName("valueContributor");
ValueContributor.SerializeJson(writer, options);
}
if (ValueDataRequirement != null)
{
writer.WritePropertyName("valueDataRequirement");
ValueDataRequirement.SerializeJson(writer, options);
}
if (ValueExpression != null)
{
writer.WritePropertyName("valueExpression");
ValueExpression.SerializeJson(writer, options);
}
if (ValueParameterDefinition != null)
{
writer.WritePropertyName("valueParameterDefinition");
ValueParameterDefinition.SerializeJson(writer, options);
}
if (ValueRelatedArtifact != null)
{
writer.WritePropertyName("valueRelatedArtifact");
ValueRelatedArtifact.SerializeJson(writer, options);
}
if (ValueTriggerDefinition != null)
{
writer.WritePropertyName("valueTriggerDefinition");
ValueTriggerDefinition.SerializeJson(writer, options);
}
if (ValueUsageContext != null)
{
writer.WritePropertyName("valueUsageContext");
ValueUsageContext.SerializeJson(writer, options);
}
if (ValueDosage != null)
{
writer.WritePropertyName("valueDosage");
ValueDosage.SerializeJson(writer, options);
}
if (ValueMeta != null)
{
writer.WritePropertyName("valueMeta");
ValueMeta.SerializeJson(writer, options);
}
if (includeStartObject)
{
writer.WriteEndObject();
}
}
public ValueRange ReadDataFromGoogleXML(string firstPoint, string secondPont)
{
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId = Properties.Settings.Default["ContainerTable"].ToString();
String range = "ПРИБЫТИЕ/ЭКСПОРТ!D" + firstPoint + ":" + "W" + secondPont;
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = request.Execute();
string[] contArr = new string[response.Values.Count];
for (int i = 0; i < response.Values.Count; i++)
{
if (response.Values[i][2].ToString() == "")
{
if (i - 1 >= 0)
{
response.Values[i][2] = response.Values[i - 1][2];
}
}
}
for (int i = 0; i < response.Values.Count; i++)
{
contArr[i] = response.Values[i][0].ToString();
}
Array.Sort(contArr, StringComparer.InvariantCulture);
ValueRange result = request.Execute();
for (int i = 0; i < contArr.Length; i++)
{
for (int j = 0; j < contArr.Length; j++)
{
if (response.Values[j][0].ToString() == contArr[i])
{
result.Values[i] = response.Values[j];
}
}
}
return(result);
}
static void Main(string[] args)
{
System.Globalization.CultureInfo.DefaultThreadCurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("cs-CZ");;
System.Globalization.CultureInfo.DefaultThreadCurrentUICulture = System.Globalization.CultureInfo.GetCultureInfo("cs-CZ");
UserCredential credential;
//credentials.json created as described in https://developers.google.com/sheets/api/quickstart/dotnet?authuser=2
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "Podpora-COVID-Update",
});
var ds = HlidacStatu.Api.V2.Dataset.Typed.Dataset <pomoc> .OpenDataset(System.Configuration.ConfigurationManager.AppSettings["apikey"], DatasetNameId);
//ds.SetDeveleperUrl("http://local.hlidacstatu.cz/api/v1");
//
// Define request parameters.
String spreadsheetId = "1MtsFm3W8PXkNQ1y0JWYQfb7cl_usCK87nz57uOeI3RM";
String range = "Data!A2:J100";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(spreadsheetId, range);
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
if (values != null && values.Count > 0)
{
foreach (var row in values
.Where(r => r.Count > 6)
)
{
var p = new pomoc();
if (row[0].ToString().Length > 0)
{
p.Id = row[0].ToString();
p.ministerstvo = row[1].ToString();
p.typ_pomoci = row[2].ToString();
p.program = row[3].ToString();
p.odhadovana_celkova_vyse_v_mld_kc = decimal.TryParse(row[4].ToString(), out var dd) ? decimal.Parse(row[4].ToString()) : 0;
p.vyplacena = decimal.TryParse(row[5].ToString(), out var dd1) ? decimal.Parse(row[5].ToString()) : 0;
p.pocet_subjektu = int.Parse(row[6].ToString().Replace(" ", "").Replace(((char)160).ToString(), string.Empty));
p.udaj_ke_dni = DateTime.ParseExact(row[7].ToString(), "d.M.yyyy", System.Globalization.CultureInfo.GetCultureInfo("cs-CZ"));
if (row.Count > 8)
{
p.poznamka = row[8].ToString();
}
if (row.Count > 9)
{
p.url = row[9].ToString();
}
if (p.typ_pomoci.Length > 5)
{
Console.WriteLine(p.Id);
var id = ds.AddOrUpdateItem(p, HlidacStatu.Api.V2.Dataset.Typed.ItemInsertMode.rewrite);
}
}
}
}
else
{
Console.WriteLine("No data found.");
}
//Console.Read();
}
public static void Execute(IList <IList <object> > list)
{
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
// The file token.json stores the user's access and refresh tokens, and is created
// automatically when the authorization flow completes for the first time.
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"users",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
// Define request parameters.
String spreadsheetId = "1-NNRsKhonKzNmTrl2H3IfwIZvGTy0HMs6AvXhsw-nUc";
String range = "Test Data!A2:E";
//SpreadsheetsResource.ValuesResource.GetRequest request =
// service.Spreadsheets.Values.Get(spreadsheetId, range);
//var list1 = new List<object> { "Hi" };
//var list2 = new List<object> { "How are you?" };
//IList<IList<object>> list = new List<IList<object>> { list1, list2 };
var rng = string.Format("{0}!A1:A{1}", "Test Data", list.Count);
var vRange = new ValueRange
{
Range = rng,
Values = list,
MajorDimension = "ROWS"
};
var rqst = service.Spreadsheets.Values.Append(vRange, spreadsheetId, rng);
rqst.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
rqst.Execute();
// Prints the names and majors of students in a sample spreadsheet:
// https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
//ValueRange response = request.Execute();
//IList<IList<Object>> values = response.Values;
//if (values != null && values.Count > 0)
//{
// Console.WriteLine("Name, Major");
// foreach (var row in values)
// {
// // Print columns A and E, which correspond to indices 0 and 4.
// Console.WriteLine("{0}, {1}", row[0], row[4]);
// }
//}
//else
//{
// Console.WriteLine("No data found.");
//}
//Console.Read();
}
public Builder IntersectRange(SymbolId key, ValueRange intersectedRange)
{
ranges[key] = this[key].Intersect(intersectedRange);
return(this);
}
public void Initialize()
{
InnerLexer = Alternation.Create(
ValueRange.Create('\x00', '\x1F'),
Terminal.Create("\x7F", StringComparer.Ordinal));
}
private void SendData_CreateData(DTOAPIData item)
{
if (!string.IsNullOrEmpty(item.FileUpload) && !string.IsNullOrEmpty(item.SpreadsheetID) && !string.IsNullOrEmpty(item.SpreadsheetName))
{
string fileupload = HttpContext.Current.Server.MapPath("/Uploads/" + item.FileUpload);
if (System.IO.File.Exists(fileupload))
{
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
valueRange.Values = new List <IList <object> >();
if (System.IO.Path.GetExtension(fileupload).ToLower() == ".csv")
{
#region csv
using (var textReader = new System.IO.StreamReader(fileupload))
{
int row, col;
var csv = new CsvHelper.CsvReader(textReader);
row = 0;
while (csv.Read())
{
if (row <= RowMax)
{
var objrow = new List <object>();
for (col = 0; col < ColMax; col++)
{
try
{
objrow.Add(csv[col]);
}
catch
{
break;
}
}
valueRange.Values.Add(objrow);
}
else
{
break;
}
row++;
}
}
#endregion
}
else
{
#region excel
using (var package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(fileupload)))
{
var worksheet = HelperExcel.GetWorksheetFirst(package);
#region current
if (worksheet != null && worksheet.Dimension != null)
{
int row, col;
for (row = 1; row <= worksheet.Dimension.End.Row && row < RowMax; row++)
{
var objrow = new List <object>();
for (col = 1; col <= worksheet.Dimension.End.Column && col < ColMax; col++)
{
var str = HelperExcel.GetValue(worksheet, row, col);
objrow.Add(str);
}
valueRange.Values.Add(objrow);
}
}
#endregion
}
#endregion
}
string[] Scopes = { SheetsService.Scope.Spreadsheets }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new System.IO.FileStream(HttpContext.Current.Server.MapPath(FileClientId), System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
string credPath = HttpContext.Current.Server.MapPath(FileToken);
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
System.Threading.CancellationToken.None,
new FileDataStore(credPath, true)).Result;
//Console.WriteLine("Credential file saved to: " + credPath);
}
if (credential != null)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "Google Sheets API",
});
string range = item.SpreadsheetName + "!A1";
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, item.SpreadsheetID, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var result = update.Execute();
}
else
{
throw new Exception("Credential file fail");
}
}
}
}
private static void ImportDayLog(SheetsService service)
{
string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["DailySpreadsheetId"];
string inputPath = System.Configuration.ConfigurationManager.AppSettings["DailyFile"];
Console.WriteLine("Processing day file {0}", inputPath);
string sheetName = "dayfile";
string range = sheetName + "!A:AT";
var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute();
var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
sheet = CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet);
}
if (sheet == null)
{
Console.WriteLine("Unable to get sheet {0}", sheetName);
return;
}
string inputData;
using (var stream = new FileStream(inputPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = new StreamReader(stream))
{
inputData = reader.ReadToEnd();
reader.Close();
}
if (string.IsNullOrEmpty(inputData))
{
Console.WriteLine("File empty!");
return;
}
var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
Console.WriteLine("Loaded lines {0}", inputLines.Length);
var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute();
var rangeLines = 0;
if (rangeValues.Values != null)
{
rangeLines = rangeValues.Values.Count;
}
Console.WriteLine("Range lines {0}", rangeLines);
var newData = new List <IList <object> >();
for (int i = rangeLines; i < inputLines.Length; i++)
{
var splitedInputLine = inputLines[i].Split(";".ToCharArray());
var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture);
// var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture);
// var timeOnly = time - time.Date;
//var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast<object>().ToList();
var values = splitedInputLine.Skip(1).Select(x => x).Cast <object>().ToList();
var data = new List <object>();
data.Add(splitedInputLine[0]);
data.Add(decimal.Parse(splitedInputLine[1]));
data.Add(decimal.Parse(splitedInputLine[2]));
data.Add(splitedInputLine[3]);
data.Add(decimal.Parse(splitedInputLine[4]));
data.Add(splitedInputLine[5]);
data.Add(decimal.Parse(splitedInputLine[6]));
data.Add(splitedInputLine[7]);
data.Add(decimal.Parse(splitedInputLine[8]));
data.Add(splitedInputLine[9]);
data.Add(decimal.Parse(splitedInputLine[10]));
data.Add(splitedInputLine[11]);
data.Add(decimal.Parse(splitedInputLine[12]));
data.Add(splitedInputLine[13]);
data.Add(decimal.Parse(splitedInputLine[14]));
data.Add(decimal.Parse(splitedInputLine[15]));
data.Add(decimal.Parse(splitedInputLine[16]));
data.Add(decimal.Parse(splitedInputLine[17]));
data.Add(splitedInputLine[18]);
data.Add(decimal.Parse(splitedInputLine[19]));
data.Add(splitedInputLine[20]);
data.Add(decimal.Parse(splitedInputLine[21]));
data.Add(splitedInputLine[22]);
data.Add(decimal.Parse(splitedInputLine[23]));
data.Add(decimal.Parse(splitedInputLine[24]));
data.Add(decimal.Parse(splitedInputLine[25]));
data.Add(splitedInputLine[26]);
data.Add(decimal.Parse(splitedInputLine[27]));
data.Add(splitedInputLine[28]);
data.Add(decimal.Parse(splitedInputLine[29]));
data.Add(splitedInputLine[30]);
data.Add(decimal.Parse(splitedInputLine[31]));
data.Add(splitedInputLine[32]);
data.Add(decimal.Parse(splitedInputLine[33]));
data.Add(splitedInputLine[34]);
data.Add(decimal.Parse(splitedInputLine[35]));
data.Add(splitedInputLine[36]);
data.Add(decimal.Parse(splitedInputLine[37]));
data.Add(splitedInputLine[38]);
data.Add(decimal.Parse(splitedInputLine[39]));
data.Add(decimal.Parse(splitedInputLine[40]));
data.Add(decimal.Parse(splitedInputLine[41]));
data.Add(decimal.Parse(splitedInputLine[42]));
data.Add(splitedInputLine[43]);
data.Add(decimal.Parse(splitedInputLine[44]));
data.Add(splitedInputLine[45]);
//data.Add(splitedInputLine[1]);
//data.AddRange(values);
newData.Add(data);
}
if (newData.Count > 0)
{
Console.Write("Sending...");
ValueRange vr = new ValueRange();
vr.Values = newData;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
Console.WriteLine("DONE");
}
else
{
Console.WriteLine("No new data.");
}
}
private void FillFailedItems(IReadOnlyCollection <string> parameters)
{
var rangesToUpdate = new List <ValueRange>();
foreach (var parameter in parameters.Where(p => !string.IsNullOrWhiteSpace(p)))
{
var parameterParts = parameter.Split('.');
if (parameterParts.Length != 2 ||
!int.TryParse(parameterParts[0].Trim(), out var sheetIndex) ||
!int.TryParse(parameterParts[1].Trim(), out var ruleIndex))
{
throw new ReviewException($"Unknown format of parameter \"{parameterParts}\". Should be \"N.N\"");
}
var sheet = _sheetsService.Spreadsheets
.Get(_qbFile.Id)
.Execute()
.Sheets
.FirstOrDefault(s => s.Properties.Title.StartsWith($"{sheetIndex}."));
if (sheet == null)
{
throw new ReviewException($"Sheet with index \"{sheetIndex}\" not found");
}
var ruleValues = _sheetsService.Spreadsheets.Values.Get(_qbFile.Id, $"{sheet.Properties.Title}!G:G")
.Execute();
var fullRuleIndex = $"{sheetIndex}.{ruleIndex}";
var qbRuleIndex = -1;
for (var i = 0; i < ruleValues.Values.Count; i++)
{
var rule = ruleValues.Values[i][0].ToString();
if (rule == fullRuleIndex)
{
qbRuleIndex = i + 1;
break;
}
}
if (qbRuleIndex == -1)
{
throw new ReviewException($"Rule with index {fullRuleIndex} not found");
}
var valueRange = new ValueRange
{
Range = $"{sheet.Properties.Title}!A{qbRuleIndex}:A{qbRuleIndex}",
Values = new List <IList <object> > {
new List <object> {
"FAIL"
}
},
};
rangesToUpdate.Add(valueRange);
}
var batchUpdate = new BatchUpdateValuesRequest
{
Data = rangesToUpdate,
ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW.ToString()
};
_sheetsService.Spreadsheets.Values
.BatchUpdate(batchUpdate, _qbFile.Id)
.Execute();
}
private static void ImportMainLog(SheetsService service)
{
string spreadsheetId = System.Configuration.ConfigurationManager.AppSettings["spreadsheetId"];
string inputPath = System.Configuration.ConfigurationManager.AppSettings["InputPath"];
string filePrefix = System.Configuration.ConfigurationManager.AppSettings["FilePrefix"];
int sheetIdFrom = int.Parse(System.Configuration.ConfigurationManager.AppSettings["From"]);
int sheetIdTo = int.Parse(System.Configuration.ConfigurationManager.AppSettings["To"]);
Console.WriteLine("From {0} to {1}", sheetIdFrom, sheetIdTo);
var files = System.IO.Directory.EnumerateFiles(inputPath, filePrefix + "*.txt");
Console.WriteLine("Found {0} in {1}", files.Count(), inputPath + filePrefix);
foreach (var file in files)
{
Console.WriteLine("Processing {0}", file);
System.IO.FileInfo fi = new FileInfo(file);
string sheetName = fi.Name.Replace(filePrefix, "").Replace(".txt", "");
int sheetId = int.Parse(sheetName);
if (sheetId < sheetIdFrom || sheetId > sheetIdTo)
{
continue;
}
string range = sheetName + "!A:AR";
var spreadsheet = service.Spreadsheets.Get(spreadsheetId).Execute();
var sheet = spreadsheet.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
sheet = CreateSheet(service, spreadsheetId, sheetName, ref spreadsheet);
}
if (sheet == null)
{
Console.WriteLine("Unable to get sheet {0}", sheetName);
continue;
}
Console.WriteLine("Using sheet {0}", sheetName);
string inputData;
using (var stream = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = new StreamReader(stream))
{
inputData = reader.ReadToEnd();
reader.Close();
}
if (string.IsNullOrEmpty(inputData))
{
Console.WriteLine("File empty!");
continue;
}
var inputLines = inputData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
Console.WriteLine("Loaded lines {0}", inputLines.Length);
var rangeValues = service.Spreadsheets.Values.Get(spreadsheetId, range).Execute();
var rangeLines = 0;
if (rangeValues.Values != null)
{
rangeLines = rangeValues.Values.Count;
}
Console.WriteLine("Range lines {0}", rangeLines);
var newData = new List <IList <object> >();
for (int i = rangeLines; i < inputLines.Length; i++)
{
var splitedInputLine = inputLines[i].Split(";".ToCharArray());
var date = DateTime.ParseExact(splitedInputLine[0], "dd.MM.yy", CultureInfo.InvariantCulture);
var time = DateTime.ParseExact(splitedInputLine[1], "HH:mm", CultureInfo.InvariantCulture);
var timeOnly = time - time.Date;
var values = splitedInputLine.Skip(2).Select(x => decimal.Parse(x)).Cast <object>().ToList();
var data = new List <object>();
data.Add(splitedInputLine[0]);
data.Add(splitedInputLine[1]);
data.AddRange(values);
newData.Add(data);
}
if (newData.Count > 0)
{
Console.Write("Sending...");
ValueRange vr = new ValueRange();
vr.Values = newData;
SpreadsheetsResource.ValuesResource.AppendRequest request = service.Spreadsheets.Values.Append(vr, spreadsheetId, range);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
var response = request.Execute();
Console.WriteLine("DONE");
}
else
{
Console.WriteLine("No new data.");
}
}
}
public void UpdateLogSheet(string message, string SheetName)
{
try
{
Monitor.Enter(gsLock);
IList <Object> obj = new List <Object>()
{
DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")
};
obj.Add(message);
List <IList <Object> > data = new List <IList <Object> >();
data.Add(obj);
UserCredential credential;
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
//Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
String spreadsheetId2 = "16HWTcJg5mFDbFch2HIYl_OI5xpe9FxzFeNrFQv-k7cU";
String range2 = SheetName + "!A1"; // update cell F5
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS"; //"ROWS";//COLUMNS
//var oblist = new List<object>() { "1" };
//oblist.Add("2");
valueRange.Values = data;//new List<IList<object>> { oblist };
SpreadsheetsResource.ValuesResource.AppendRequest request =
service.Spreadsheets.Values.Append(new ValueRange()
{
Values = data
}, spreadsheetId2, range2);
request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var response = request.Execute();
}
catch
{
}
finally
{
Monitor.Exit(gsLock);
}
}
private int mouseMoveCount = 0; //See sysSensibilityPAN
/// <summary>
/// Get Y-Viewport(min,max) of all curves in the current X-Vieport defined by [myViewportX].
/// The function used Y-Axis data stored in [mySeriesY]
/// </summary>
/// <returns></returns>
private ValueRange GetViewportY()
{
ValueRange range = new ValueRange();
for (int idx = 0; idx < this.myGraphPane.CurveList.Count; idx++)
{
Libs.GetRangeY(this.myGraphPane.CurveList[idx], this.myViewportX.Min, this.myViewportX.Max, ref range);
}
if (this.myGraphPane.CurveList.Count > 0)
{
decimal diff = (decimal)(range.Max - range.Min);
range.Max += (double)(diff * Settings.sysViewSpaceAtTOP / 100);
range.Min -= (double)(diff * Settings.sysViewSpaceAtBOT / 100);
}
return range;
}
private GameTimeDuration __GenerateObstacleCheckPeriod() {
float relativeObstacleFreq; // IMPROVE OK for now as obstacleDensity is related but not same as Topography.GetRelativeDensity()
float defaultHours;
ValueRange<float> hoursRange;
switch (_ship.Topography) {
case Topography.OpenSpace:
relativeObstacleFreq = 40F;
defaultHours = 20F;
hoursRange = new ValueRange<float>(5F, 100F);
break;
case Topography.System:
relativeObstacleFreq = 4F;
defaultHours = 3F;
hoursRange = new ValueRange<float>(1F, 10F);
break;
case Topography.DeepNebula:
case Topography.Nebula:
case Topography.None:
default:
throw new NotImplementedException(ErrorMessages.UnanticipatedSwitchValue.Inject(_ship.Topography));
}
float speedValue = _engineRoom.IntendedCurrentSpeedValue;
float hoursBetweenChecks = speedValue > Constants.ZeroF ? relativeObstacleFreq / speedValue : defaultHours;
hoursBetweenChecks = hoursRange.Clamp(hoursBetweenChecks);
hoursBetweenChecks = VaryCheckPeriod(hoursBetweenChecks);
float checksPerHour = 1F / hoursBetweenChecks;
if (checksPerHour * GameTime.Instance.GameSpeedAdjustedHoursPerSecond > FpsReadout.FramesPerSecond) {
// check frequency is higher than the game engine can run
D.Warn("{0} obstacleChecksPerSec {1:0.#} > FPS {2:0.#}.",
DebugName, checksPerHour * GameTime.Instance.GameSpeedAdjustedHoursPerSecond, FpsReadout.FramesPerSecond);
}
return new GameTimeDuration(hoursBetweenChecks);
}
public static LibActionResult InsertExpenseToGoogleSheet(string userInput, Chat chat, TelegramBotClient botClient)
{
var connector = ((DomainDataContext)chat.DataContext).GoogleSheetsConnector;
var expense = ((DomainDataContext)chat.DataContext).CurrentExpense;
bool sheetnameHasChanged = false;
//int googleSheetsDate = Convert.ToInt32( (expense.ExpenseDate.Date - new DateTime(1899, 12, 30) ).TotalDays);
IList <IList <object> > newCellsValues = new List <IList <object> >()
{
new List <object>()
{
expense.ExpenseValue, expense.Category.Name, expense.ExpenseDate.Date.ToString("yyy-MM-dd")
}
};
var r = GoogleAuthActions.AuthorizeAndGetSheetsService(userInput, chat, botClient);
if (r.Status == false)
{
return(new LibActionResult()
{
Status = false,
ErrorMessage = "Не удалось авторизовать твой аккаунт у Гугла:("
});
}
ValueRange vr = new ValueRange();
vr.Values = newCellsValues;
var updateRequest = connector.SheetsService.Spreadsheets.Values.Append(vr, connector.SpreadsheetId, connector.Sheetname);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
try
{
var result = updateRequest.Execute();
}
catch (Google.GoogleApiException e)
{
if (e.Error.Code == 400)
{
sheetnameHasChanged = true;
}
else
{
return(new LibActionResult()
{
Status = false,
ErrorMessage = "Unknow error from Google, try again"
});
}
}
if (sheetnameHasChanged)
{
try
{
connector.Sheetname = connector.SheetsService.Spreadsheets.Get(connector.SpreadsheetId)
.Execute().Sheets.Where(x => x.Properties.SheetId == connector.SheetId).First().Properties.Title;
updateRequest = connector.SheetsService.Spreadsheets.Values.Append(vr, connector.SpreadsheetId, connector.Sheetname);
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
}
catch (Google.GoogleApiException anotherE)
{
return(new LibActionResult()
{
Status = false,
ErrorMessage = "Изменен лист в GoogleSheets"
});
}
}
return(new LibActionResult()
{
Status = true
});
}
public void CreateValueRangeFromString()
{
var range = new ValueRange("0.1, 0.9");
Assert.AreEqual(0.1f, range.Start);
Assert.AreEqual(0.9f, range.End);
}
//record new chracters
static public void nw(CommandService commands, UserCredential c, string appname)
{
commands.CreateCommand("new").Parameter("name", ParameterType.Required).Do(async(e) =>
{
var user = e.User;
string name = e.GetArg("name");
DateTime localDate = DateTime.Now;
bool dumbass = false;
int lin = 0;
int linenum = 0;
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = c,
ApplicationName = appname,
});
//records user data to spreadsheet
string range = "Characters!A1:C";
SpreadsheetsResource.ValuesResource.GetRequest request =
service.Spreadsheets.Values.Get(creds.ssid(), range);
//tests for previous chracters
ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
foreach (var row in values)
{
if (row[1].ToString() == user.Id.ToString() && row[2].ToString() == name)
{
dumbass = true;
}
if (row[1].ToString() == "0")
{
lin = linenum + 1;
}
linenum++;
}
if (lin == 0)
{
lin = linenum + 1;
}
if (dumbass == false)
{
String range2 = "Characters!A" + lin.ToString() + ':' + 'I' + lin.ToString();
var oblist = new List <object>()
{
localDate.ToString(), user.Id.ToString(), name, 500, 0, 0, "=VLOOKUP((H" + lin.ToString() + "+1),XPScaling!A:B,2,1)-F" + lin.ToString(), "= VLOOKUP(F" + lin.ToString() + ",{ XPScaling!B:B,XPScaling!A:A},2,1)", "= VLOOKUP(B" + lin.ToString() + ", Users!A:B,2,0)"
};
account.write(range2, oblist, c, appname);
await e.Channel.SendMessage(user.Name + " has created a new character, " + name);
Console.WriteLine(user.Name + " has created a new character, " + name);
}
else
{
await e.Channel.SendMessage("Sorry, no clones allowed.");
Console.WriteLine(user.Name + " tried to create a clone ");
}
});
}
public void SingleValueConstructor()
{
valueRange = new ValueRange(0.1f);
Assert.AreEqual(0.1f, valueRange.Start);
Assert.AreEqual(0.1f, valueRange.End);
}
private void CreateJsonFile(string fileName, string outputDirectory, ValueRange valueRange)
{
//Get properties's name, data type and sheet data
IDictionary <int, string> propertyNames = new Dictionary <int, string>(); //Dictionary of (column index, property name of that column)
IDictionary <int, string> dataTypes = new Dictionary <int, string>(); //Dictionary of (column index, data type of that column)
IDictionary <int, Dictionary <int, string> > values = new Dictionary <int, Dictionary <int, string> >(); //Dictionary of (row index, dictionary of (column index, value in cell))
int rowIndex = 0;
foreach (IList <object> row in valueRange.Values)
{
int columnIndex = 0;
foreach (string cellValue in row)
{
string value = cellValue;
if (rowIndex == 0)
{ //This row is properties's name row
propertyNames.Add(columnIndex, value);
}
else if (rowIndex == 1)
{ //This row is properties's data type row
dataTypes.Add(columnIndex, value);
}
else
{ //Data rows
//Because first row is name row and second row is data type row, so we will minus 2 from rowIndex to make data index start from 0
if (!values.ContainsKey(rowIndex - 2))
{
values.Add(rowIndex - 2, new Dictionary <int, string>());
}
values[rowIndex - 2].Add(columnIndex, value);
}
columnIndex++;
}
rowIndex++;
}
//Create list of Dictionaries (property name, value). Each dictionary represent for a object in a row of sheet.
List <object> datas = new List <object>();
foreach (int rowId in values.Keys)
{
bool thisRowHasError = false;
Dictionary <string, object> data = new Dictionary <string, object>();
foreach (int columnId in propertyNames.Keys)
{ //Read through all columns in sheet, with each column, create a pair of property(string) and value(type depend on dataType[columnId])
if (thisRowHasError)
{
break;
}
if ((!dataTypes.ContainsKey(columnId)) || (!allowedDataTypes.Contains(dataTypes[columnId])))
{
continue; //There is not any data type or this data type is strange. May be this column is used for comments. Skip this column.
}
if (!values[rowId].ContainsKey(columnId))
{
values[rowId].Add(columnId, "");
}
string strVal = values[rowId][columnId];
switch (dataTypes[columnId])
{
case "string_":
case "string":
{
//if( string.IsNullOrEmpty( strVal ) )
//{
// Debug.Log( "---------" );
// data.Add( propertyNames[columnId], "asifhjsaihdf" );
//}
//else
{
data.Add(propertyNames[columnId], strVal);
}
break;
}
case "int":
{
int val = 0;
if (!string.IsNullOrEmpty(strVal))
{
try
{
val = int.Parse(strVal);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
thisRowHasError = true;
continue;
}
}
data.Add(propertyNames[columnId], val);
break;
}
case "bool":
{
bool val = false;
if (!string.IsNullOrEmpty(strVal))
{
try
{
val = bool.Parse(strVal);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
continue;
}
}
data.Add(propertyNames[columnId], val);
break;
}
case "float":
{
float val = 0f;
if (!string.IsNullOrEmpty(strVal))
{
try
{
val = float.Parse(strVal);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
continue;
}
}
data.Add(propertyNames[columnId], val);
break;
}
case "string[]":
{
string[] valArr = strVal.Split(new char[] { ',' });
data.Add(propertyNames[columnId], valArr);
break;
}
case "int[]":
{
string[] strValArr = strVal.Split(new char[] { ',' });
int[] valArr = new int[strValArr.Length];
if (string.IsNullOrEmpty(strVal.Trim()))
{
valArr = new int[0];
}
bool error = false;
for (int i = 0; i < valArr.Length; i++)
{
int val = 0;
if (!string.IsNullOrEmpty(strValArr[i]))
{
try
{
val = int.Parse(strValArr[i]);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
error = true;
break;
}
}
valArr[i] = val;
}
if (error)
{
continue;
}
data.Add(propertyNames[columnId], valArr);
break;
}
case "bool[]":
{
string[] strValArr = strVal.Split(new char[] { ',' });
bool[] valArr = new bool[strValArr.Length];
if (string.IsNullOrEmpty(strVal.Trim()))
{
valArr = new bool[0];
}
bool error = false;
for (int i = 0; i < valArr.Length; i++)
{
bool val = false;
if (!string.IsNullOrEmpty(strValArr[i]))
{
try
{
val = bool.Parse(strValArr[i]);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
error = true;
break;
}
}
valArr[i] = val;
}
if (error)
{
continue;
}
data.Add(propertyNames[columnId], valArr);
break;
}
case "float[]":
{
string[] strValArr = strVal.Split(new char[] { ',' });
float[] valArr = new float[strValArr.Length];
if (string.IsNullOrEmpty(strVal.Trim()))
{
valArr = new float[0];
}
bool error = false;
for (int i = 0; i < valArr.Length; i++)
{
float val = 0f;
if (!string.IsNullOrEmpty(strValArr[i]))
{
try
{
val = float.Parse(strValArr[i]);
}
catch (System.Exception e)
{
Debug.LogError(string.Format("There is exception when parse value of property {0} of {1} class.\nDetail: {2}", propertyNames[columnId], fileName, e.ToString()));
error = true;
break;
}
}
valArr[i] = val;
}
if (error)
{
continue;
}
data.Add(propertyNames[columnId], valArr);
break;
}
default:
break; //This data type is strange, may be this column is used for comments, not for store data, so do nothing and read next column.
}
}
if (!thisRowHasError)
{
datas.Add(data);
}
else
{
Debug.LogError("There's error!");
}
}
//Create json text
string jsonText = JsonConvert.SerializeObject((object)datas);
// PABLO201901: Small but ESSENTIAL fix
// We have to add {"Items": at the beginning of the file, and a } at the end
jsonText = "{\"Items\":" + jsonText + "}";
//Create directory to store the json file
if (!outputDirectory.EndsWith("/"))
{
outputDirectory += "/";
}
Directory.CreateDirectory(outputDirectory);
StreamWriter strmWriter = new StreamWriter(outputDirectory + fileName + ".json", false, System.Text.Encoding.UTF8);
strmWriter.Write(jsonText);
strmWriter.Close();
Debug.Log("Created: " + fileName + ".json");
}
public void Update(ValueRange body, string sheetId, string range)
{
}
private static void AddMarkedOrSelectedTime(TrailsItemTrackSelectionInfo tmpSel, bool singleSelection, DateTime d1, DateTime d2)
{
//Use SelectedTime with only one selection and 0 span - valueRanges will not handle it
IValueRange<DateTime> vd = new ValueRange<DateTime>(d1, d2);
if (tmpSel.MarkedTimes == null)
{
tmpSel.MarkedTimes = new ValueRangeSeries<DateTime>();
}
int currAdd = tmpSel.MarkedTimes.Count;
tmpSel.MarkedTimes.Add(vd);
if(tmpSel.MarkedTimes.Count == currAdd)
{
//Could not add to ranges, add Selected
if (singleSelection && tmpSel.MarkedTimes.Count == 0)
{
//Could add to selected
tmpSel.SelectedTime = vd;
tmpSel.MarkedTimes = null;
}
else
{
//fake, add second
vd = new ValueRange<DateTime>(vd.Lower, vd.Upper.AddSeconds(1));
tmpSel.MarkedTimes.Add(vd);
}
}
}
