public override void Log(LoggingEntery LE)
{
IniFile Ini = new IniFile(Path.Combine(Environment.CurrentDirectory, "GoogleDocs.ini"));
// nir start
////////////////////////////////////////////////////////////////////////////
// STEP 1: Configure how to perform OAuth 2.0
////////////////////////////////////////////////////////////////////////////
// TODO: Update the following information with that obtained from
// https://code.google.com/apis/console. After registering
// your application, these will be provided for you.
//string CLIENT_ID = "339569043085-6k0io9kdubi7a3g3jes4m76t614fkccr.apps.googleusercontent.com";
// This is the OAuth 2.0 Client Secret retrieved
// above. Be sure to store this value securely. Leaking this
// value would enable others to act on behalf of your application!
//string CLIENT_SECRET = "wWC4Wcb12RbQg4YuGWJtkh4j";
// Space separated list of scopes for which to request access.
//string SCOPE = "https://spreadsheets.google.com/feeds https://docs.google.com/feeds";
// This is the Redirect URI for installed applications.
// If you are building a web application, you have to set your
// Redirect URI at https://code.google.com/apis/console.
//tring REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";
////////////////////////////////////////////////////////////////////////////
// STEP 2: Set up the OAuth 2.0 object
////////////////////////////////////////////////////////////////////////////
// OAuth2Parameters holds all the parameters related to OAuth 2.0.
OAuth2Parameters parameters = new OAuth2Parameters();
// Set your OAuth 2.0 Client Id (which you can register at
// https://code.google.com/apis/console).
parameters.ClientId = Ini.IniReadValue(ConnectSection,"ClientID");
// Set your OAuth 2.0 Client Secret, which can be obtained at
// https://code.google.com/apis/console.
parameters.ClientSecret = Ini.IniReadValue(ConnectSection,"ClientSecret");
// Set your Redirect URI, which can be registered at
// https://code.google.com/apis/console.
parameters.RedirectUri = Ini.IniReadValue(ConnectSection,"RedirectURI");
// Set your refresh token
parameters.RefreshToken = Ini.IniReadValue(ConnectSection,"RefreshToken");
parameters.AccessToken = Ini.IniReadValue(ConnectSection,"LastAccessToken");
// Set the scope for this particular service.
parameters.Scope = Ini.IniReadValue(ConnectSection,"Scope");
// Get the authorization url. The user of your application must visit
// this url in order to authorize with Google. If you are building a
// browser-based application, you can redirect the user to the authorization
// url.
//string authorizationUrl = OAuthUtil.CreateOAuth2AuthorizationUrl(parameters);
//Console.WriteLine(authorizationUrl);
//Console.WriteLine("Please visit the URL above to authorize your OAuth "
// + "request token. Once that is complete, type in your access code to "
// + "continue...");
////////////////////////////////////////////////////////////////////////////
// STEP 4: Get the Access Token
////////////////////////////////////////////////////////////////////////////
// Once the user authorizes with Google, the request token can be exchanged
// for a long-lived access token. If you are building a browser-based
// application, you should parse the incoming request token from the url and
// set it in OAuthParameters before calling GetAccessToken().
OAuthUtil.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection,"LastAccessToken",parameters.AccessToken);
// Console.WriteLine("OAuth Access Token: " + accessToken);
////////////////////////////////////////////////////////////////////////////
// STEP 5: Make an OAuth authorized request to Google
////////////////////////////////////////////////////////////////////////////
// Initialize the variables needed to make the request
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
SpreadsheetsService myService = new SpreadsheetsService("OctoTipPlus");
myService.RequestFactory = requestFactory;
// nir end
string User = Ini.IniReadValue("UserLogin","User");
string Password = Ini.IniReadValue("UserLogin","Password");
// SpreadsheetsService myService = new SpreadsheetsService("MySpreadsheetIntegration-v1");
//myService.setUserCredentials(User,Password);
SpreadsheetQuery Squery = new SpreadsheetQuery();
string Sender = LE.Sender;
Squery.Title = Sender;
Squery.Exact = true;
SpreadsheetFeed Sfeed;
try
{
Sfeed = myService.Query(Squery);
}
catch (Google.GData.Client.InvalidCredentialsException e)
{
throw(new Exception(string.Format("Credentials error in google acount for user:{0}",User),e));
}
if(Sfeed.Entries.Count == 0)
{
//DriveService service1 = new DriveService();
//service.SetAuthenticationToken(parameters.AccessToken);//.setUserCredentials(User,Password);
//Google.GData.Client.GOAuth2RequestFactory requestf = new Google.GData.Client.GOAuth2RequestFactory(null, "OctoTipPlus",parameters);
OAuthUtil.RefreshAccessToken(parameters);//parameters.AccessToken;
Ini.IniWriteValue(ConnectSection,"LastAccessToken",parameters.AccessToken);
Google.GData.Documents.DocumentsService service = new Google.GData.Documents.DocumentsService("OctoTipPlus");
GOAuth2RequestFactory requestFactory2 =
new GOAuth2RequestFactory(null, "OctoTipPlus", parameters);
service.RequestFactory = requestFactory2;
//service.RequestFactory=requestf;
// Instantiate a DocumentEntry object to be inserted.
Google.GData.Documents.DocumentEntry entry = new Google.GData.Documents.DocumentEntry();
// Set the document title
entry.Title.Text = LE.Sender;
// Add the document category
entry.Categories.Add(Google.GData.Documents.DocumentEntry.SPREADSHEET_CATEGORY);
// Make a request to the API and create the document.
Google.GData.Documents.DocumentEntry newEntry = service.Insert(
Google.GData.Documents.DocumentsListQuery.documentsBaseUri, entry);
Squery = new SpreadsheetQuery();
Squery.Title = Sender;
Squery.Exact = true;
Sfeed = myService.Query(Squery);
}
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)Sfeed.Entries[0];
WorksheetEntry ProtocolWorksheetEntry=null;
AtomLink link = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery Wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed Wfeed = myService.Query(Wquery);
foreach (WorksheetEntry worksheet in Wfeed.Entries)
{
if (worksheet.Title.Text==LE.SubSender)
{
ProtocolWorksheetEntry = worksheet;
}
}
if (ProtocolWorksheetEntry==null)
{
// cteate new worksheet
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = LE.SubSender;
worksheet.Cols = 3;
worksheet.Rows = 5;
// Send the local representation of the worksheet to the API for
// creation. The URL to use here is the worksheet feed URL of our
// spreadsheet.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
ProtocolWorksheetEntry = myService.Insert(wsFeed, worksheet);
CellFeed cellFeed= ProtocolWorksheetEntry.QueryCellFeed();
CellEntry cellEntry= new CellEntry (1, 1,DateHeader);
cellFeed.Insert(cellEntry);
cellEntry= new CellEntry (1, 2, MessageHeader);
cellFeed.Insert(cellEntry);
}
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = ProtocolWorksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = myService.Query(listQuery);
string Message = string.Format("{0}\n{1}",LE.Title,LE.Message);
// Create a local representation of the new row.
ListEntry row = new ListEntry();
row.Elements.Add(new ListEntry.Custom() { LocalName = DateHeader, Value = DateTime.Now.ToString() });
row.Elements.Add(new ListEntry.Custom() { LocalName = MessageHeader, Value = Message });
// Send the new row to the API for insertion.
myService.Insert(listFeed, row);
}
// grab your spreadsheet's ID / "key" from the URL to access your doc...
// e.g. everything after "key=" in the URL: https://docs.google.com/spreadsheet/ccc?key=0Ak-N8rbAmu7WdGRFdllybTBIaU1Ic0FxYklIbk1vYlE
// make sure stop as soon as you hit an ampersand, those are additional URL parameters we don't need
public static ListFeed GetSpreadsheet(string spreadsheetID)
{
// We need this fake certificate to trick Mono's security to use HTTPS... doesn't work in webplayer's security sandbox
InsecureSecurityCertificatePolicy.Instate();
SpreadsheetsService service = new SpreadsheetsService( "UnityConnect" );
ListQuery listQuery = new ListQuery( "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/default/public/values" );
const bool debugTest = false; // change to TRUE to enable debug output
if ( debugTest ) {
ListFeed listFeed = service.Query( listQuery );
Debug.Log( "loaded Google Doc Spreadsheet: " + listFeed.Title.Text );
// Iterate through each row, printing its cell values.
foreach ( ListEntry row in listFeed.Entries ) {
// Print the first column's cell value
Debug.Log( row.Title.Text );
// Iterate over the remaining columns, and print each cell value
foreach ( ListEntry.Custom element in row.Elements ) {
Debug.Log( element.Value );
}
}
}
return service.Query( listQuery );
}
/// <summary>
/// Retrieves and prints a list feed of the specified worksheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the worksheet to retrieve</param>
/// <param name="reverseRows">true if the rows in the worksheet should
/// be reversed when returned from the server</param>
private static void RetrieveListFeed(SpreadsheetsService service, WorksheetEntry entry,
bool reverseRows)
{
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
Console.WriteLine();
Console.WriteLine("This worksheet's list feed URL is:");
Console.WriteLine(listFeedLink.HRef);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
if (reverseRows)
{
query.OrderByPosition = true;
query.Reverse = true;
}
ListFeed feed = service.Query(query);
Console.WriteLine();
Console.WriteLine("Worksheet has {0} rows:", feed.Entries.Count);
foreach (ListEntry worksheetRow in feed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements) {
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
public SpreadSheet()
{
service = new SpreadsheetsService("stock-market");
service.setUserCredentials("shurai", "$gva99void!");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry ssentry = (SpreadsheetEntry)feed.Entries[0];
AtomLink sslink = ssentry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wkquery = new WorksheetQuery(sslink.HRef.ToString());
WorksheetFeed wkfeed = service.Query(wkquery);
WorksheetEntry wkentry = (WorksheetEntry)wkfeed.Entries[0];
listFeedLink = wkentry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = service.Query(listQuery);
Console.WriteLine("Worksheet has {0} rows: ", listFeed.Entries.Count);
foreach (ListEntry worksheetRow in listFeed.Entries)
{
ListEntry.CustomElementCollection elements = worksheetRow.Elements;
foreach (ListEntry.Custom element in elements)
{
Console.Write(element.Value + "\t");
}
Console.WriteLine();
}
}
private static void DoWork()
{
service = new SpreadsheetsService("Comwell");
service.RequestFactory = GApi.RequestFactory;
// Make the request to Google
// See other portions of this guide for code to put here...
Console.Write("Retrieving data... ");
ListQuery query = new ListQuery(GApi.SpreadsheetSubmissions, "1", "private", "values");
ListFeed feed = service.Query(query);
Console.WriteLine("complete.");
Console.Write("Processing data... ");
Submission.ProcessSubmissions(feed);
Console.WriteLine("complete.");
Console.ReadLine();
Console.WriteLine("There are " + Submission.Submissions.Count + " submissions data retrieved.");
foreach (Submission sub in Submission.Submissions)
{
Console.WriteLine(sub.ToString());
}
Console.ReadLine();
}
private static ListEntry InsertRow(SpreadsheetsService service, WorksheetEntry entry, NameValueCollection parameters)
{
logger.Debug("inserting row...");
AtomLink listFeedLink = entry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed feed = service.Query(query);
ListEntry newRow = new ListEntry();
foreach(string key in parameters)
{
ListEntry.Custom curElement = new ListEntry.Custom();
curElement.Value = parameters[key];
curElement.LocalName = key;
newRow.Elements.Add(curElement);
}
// add datetime
ListEntry.Custom el = new ListEntry.Custom();
el.Value = parameters["data"];
el.LocalName = DateTime.Now.ToString() ;
newRow.Elements.Add(el);
ListEntry insertedRow = feed.Insert(newRow);
return insertedRow;
}
public static SpreadSheetUpdateViewModel GetModel()
{
var spreadSheet = SpreadSheetHelper.GetSpreadSheet(WebConfigurationManager.AppSettings["SpreadsheetURI"]);
var worksheets = SpreadSheetHelper.GetWorkSheets(spreadSheet);
var tempWorkSheet = spreadSheet.Worksheets.Entries[0];
AtomLink listFeedLink = tempWorkSheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = SpreadSheetHelper.Query(listQuery);
var expenditureTypes = GetExpenditureTypes(listFeed);
var users = new List<SelectListItem>
{
new SelectListItem
{
Text = "Henri",
Value = "Henri"
},
new SelectListItem
{
Text = "Fredrik",
Value = "Fredrik"
}
};
var model = new SpreadSheetUpdateViewModel
{
Worksheets = worksheets,
SpreadsheetTitle = spreadSheet.Title.Text,
ExpenditureTypes = expenditureTypes,
Users = users
};
return model;
}
public ListFeed GetListFeed(WorksheetEntry worksheetEntry)
{
AtomLink listFeedLink = worksheetEntry.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = _service.Query(listQuery);
return listFeed;
}
public ListFeed GetListFeed(int sheetNum)
{
WorksheetFeed wsFeed = spreadSheetEntry.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[sheetNum];
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
return service.Query(listQuery);
}
public double getPriceByName(String name)
{
ListQuery query = new ListQuery(listFeedLink.HRef.ToString());
query.SpreadsheetQuery = "market=" + name;
listFeed = service.Query(query);
ListEntry e = (ListEntry)listFeed.Entries[0];
string num = e.Elements[1].Value.ToString();
double result = Convert.ToDouble(num);
Console.WriteLine(result);
return result;
}
public void WriteDictToRow(Dictionary<string,string> dict, bool bUpdateRow)
{
try
{
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
if (!bUpdateRow)
{
ListEntry row = new ListEntry();
foreach (KeyValuePair<string, string> pair in dict)
{
row.Elements.Add(new ListEntry.Custom() { LocalName = pair.Key, Value = pair.Value } );
}
service.Insert(listFeed, row);
}
else
{
ListEntry row = (ListEntry)listFeed.Entries[listFeed.Entries.Count-1];
foreach (ListEntry.Custom element in row.Elements)
{
foreach (KeyValuePair<string, string> pair in dict)
{
if (element.LocalName == pair.Key)
{
element.Value = pair.Value;
}
}
}
row.Update();
}
}
catch (WebException e)
{
Debug.LogWarning("WriteDictToRow WebException: " + e);
}
catch (Exception e)
{
Debug.LogWarning("WriteDictToRow Exception: " + e);
}
}
public Dictionary<string, Tuple<string, string, string, bool>> SheetDownload(string IntegrationName, OAuth2Parameters paramaters, string SpreadSheetURI)
{
GOAuth2RequestFactory requestFactory = new GOAuth2RequestFactory(null, IntegrationName, paramaters);
SpreadsheetsService service = new SpreadsheetsService(IntegrationName);
string accessToken = paramaters.AccessToken;
service.RequestFactory = requestFactory;
WorksheetEntry worksheet = this.GetWorksheet(paramaters, IntegrationName, SpreadSheetURI, service);
Dictionary<string, Tuple<string, string, string, bool>> OnlineMapList = new Dictionary<string, Tuple<string, string, string, bool>>();
string map = "MapNameError";
string URL = "URL ERROR";
string UserSteamID = "0";
string Note = "No Notes";
bool MapUploadStatus = false;
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
foreach (ListEntry row in listFeed.Entries)
{
map = row.Elements[1].Value;
URL = row.Elements[2].Value;
UserSteamID = row.Elements[3].Value;
Note = row.Elements[4].Value;
if (row.Elements[5].Value == "TRUE")
{
MapUploadStatus = true;
}
else
{
MapUploadStatus = false;
}
if (!OnlineMapList.ContainsKey(map))
{
OnlineMapList.Add(map, new Tuple<string, string, string, bool>(URL, UserSteamID, Note, MapUploadStatus));
}
}
return OnlineMapList;
}
public static ListFeed getListFeedQuery(SpreadsheetEntry spreadsheet, string worksheetName, string query)
{
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries.ToList().Find((AtomEntry e) => e.Title.Text.Equals(worksheetName, StringComparison.InvariantCultureIgnoreCase));
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
if (query != null && !"".Equals(query)) {
listQuery.SpreadsheetQuery = query;
}
return GDriveManager.getService().Query(listQuery);
}
public void PopulateNewWorksheet(OAuth2ClientCredentials clientCredentials, string refreshToken, string spreadsheetTitle, string worksheetTitle, ListEntry.Custom[,] listEntries)
{
SpreadsheetsService service = GetSpreadsheetService(clientCredentials, refreshToken);
SpreadsheetQuery query = new SpreadsheetQuery()
{
Title = spreadsheetTitle,
};
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
throw new SpreadsheetNotFoundException(string.Format("Spreadsheet with title {0} not found", spreadsheetTitle));
WorksheetEntry worksheet = CreateWorksheet(worksheetTitle, (uint)listEntries.GetLength(0), (uint)listEntries.GetLength(1), service, feed);
CellQuery cellQuery = new CellQuery(worksheet.CellFeedLink);
CellFeed cellFeed = service.Query(cellQuery);
for (int i = 0; i < listEntries.GetLength(1); i++)
{
CellEntry cellEntry = new CellEntry(1, (uint)i + 1, listEntries[0, i].LocalName);
service.Insert(cellFeed, cellEntry);
}
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
for (int i = 1; i < listEntries.GetLength(0); i++)
{
ListEntry row = new ListEntry();
for (int j = 0; j < listEntries.GetLength(1); j++)
{
if (listEntries[i, j] != null)
{
listEntries[i, j].LocalName = listEntries[i, j].LocalName.ToLower().Replace(" ","");
row.Elements.Add(listEntries[i, j]);
}
}
LoggerUtil.LogMessage(string.Format("Adding row for {0}", listEntries[i, 0].Value));
service.Insert(listFeed, row);
}
}
public Dictionary<string, List<string>> GetRowsFromWorksheet(WorksheetEntry worksheet)
{
var dictionary = new Dictionary<string, List<string>>();
// Define the URL to request the list feed of the worksheet.
var listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
var listQuery = new ListQuery(listFeedLink.HRef.ToString());
var listFeed = _service.Query(listQuery);
foreach (ListEntry row in listFeed.Entries)
{
foreach (ListEntry.Custom element in row.Elements)
{
if (String.IsNullOrEmpty(element.Value))
continue;
var actionStart = element.Value.IndexOf("(", StringComparison.Ordinal);
var actionEnd = element.Value.IndexOf(" ", actionStart, StringComparison.Ordinal);
var secondEnd = element.Value.IndexOf(")", actionEnd, StringComparison.Ordinal);
var action = element.Value.Substring(actionStart + 1, actionEnd - actionStart - 1);
var thing = element.Value.Substring(actionEnd + 1, secondEnd - actionEnd - 1);
if (dictionary.ContainsKey(action))
{
dictionary[action].Add(thing);
}
else
{
var list = new List<string> { thing };
dictionary.Add(action, list);
}
}
}
return dictionary;
}
private void btnRetrieveData_Click(object sender, EventArgs e)
{
var wks = App.CurrentWorksheet;
// Define the URL to request the list feed of the worksheet.
var listFeedLink = wks.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
var listQuery = new ListQuery(listFeedLink.HRef.ToString());
var listFeed = App.Service.Query(listQuery);
var dataTable = new DataTable();
foreach (var entry in listFeed.Entries[0].ExtensionElements)
{
var columnName = entry.XmlName;
dataTable.Columns.Add(columnName);
}
// Iterate through each row, printing its header and cell values.
foreach (ListEntry row in listFeed.Entries)
{
var dataRow = dataTable.NewRow();
// Iterate over the columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements) //elements = columns!
{
Debug.WriteLine(element.LocalName + "; " + element.Value);
dataRow[element.LocalName] = element.Value;
}
dataTable.Rows.Add(dataRow);
}
Globals.Sheet1.tblGoogleData.SetDataBinding(dataTable,"");
}
private void Import_Click(object sender, RoutedEventArgs e)
{
var toProcess = Sheets.GetToProcessSheet();
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = toProcess.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = Sheets.GetSpreadsheetService().Query(listQuery);
_sellables.Clear();
foreach (var entry in listFeed.Entries.OfType<ListEntry>())
{
if (entry.Elements[(int)ToProcessSheetColumns.BundleID].Value != "")
{
int bundleId = int.Parse(entry.Elements[(int)ToProcessSheetColumns.BundleID].Value);
var bundle = _sellables.OfType<BundleToSell>().FirstOrDefault(x => x.BundleId == bundleId);
if (bundle == null)
{
bundle = new BundleToSell { BundleId = bundleId };
_sellables.Add(bundle);
}
bundle.Games.Add(new GameToSell(entry));
}
else
{
_sellables.Add(new GameToSell(entry));
}
}
// Define the URL to request the list feed of the worksheet.
listFeedLink = Sheets.GetProcessedSheet().Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
listQuery = new ListQuery(listFeedLink.HRef.ToString());
listFeed = Sheets.GetSpreadsheetService().Query(listQuery);
var ebay = new EbayAccess();
foreach (var entry in listFeed.Entries.OfType<ListEntry>())
{
if (string.IsNullOrEmpty(entry.Elements[3].Value))
{
EbayAccess.ListingInfo info;
ebay.GetListingInfo(live, entry.Elements[2].Value, out info);
if (info.ViewUrl != null)
{
entry.Elements[3].Value = info.ViewUrl;
}
else
{
entry.Elements[3].Value = "Error getting URL!";
}
entry.Update();
}
}
}
public void loadLogFeed()
{
// Get the Loot Log List worksheet
WorksheetEntry worksheet = (WorksheetEntry)spreadsheet.Worksheets.Entries.ToList().Find(new Predicate<AtomEntry>(findLootWorkSheet));
;
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
logFeed = GDriveManager.getService().Query(listQuery);
}
private void PublishGame(object game)
{
var sheets = Sheets;
var waitFor = Task.Run(() =>
{
AtomLink listFeedLink = sheets.GetProcessedSheet().Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = sheets.GetSpreadsheetService().Query(listQuery);
IEnumerable<ListEntry> newEntries = null;
if (game is GameToSell)
{
newEntries = new[] { ((GameToSell)game).PublishToEbay(live).Result };
}
else if (game is BundleToSell)
{
newEntries = ((BundleToSell)game).PublishToEbay(live).Result;
}
if (newEntries != null)
{
foreach (var newEntry in newEntries)
{
try
{
sheets.GetSpreadsheetService().Insert(listFeed, newEntry);
}
catch (Exception ex)
{
}
}
}
});
}
public void loadArmorTypes()
{
armorTypes.Clear();
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
// Get the Event list worksheet
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries.ToList().Find(new Predicate<AtomEntry>(findArmorTypesWorkSheet));
;
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.OrderByColumn = CONSTANTS_ARMOR_TYPES_NAME_COL;
ListFeed listFeed = GDriveManager.getService().Query(listQuery);
// Iterate through each row, printing its cell values.
foreach (ListEntry row in listFeed.Entries) {
// Print the first column's cell value
//logger.Debug(row.Title.Text);
ArmorTypeEntry at = new ArmorTypeEntry();
// Iterate over the remaining columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements) {
//logger.Debug(element.Value);
if (element.Value.Length > 0) {
switch (element.LocalName.ToLower()) {
case CONSTANTS_ARMOR_TYPES_NAME_COL:
at.armorType = element.Value;
break;
case CONSTANTS_TIER_COL:
tiers.Add(element.Value);
break;
}
}
}
armorTypes.Add(at);
}
tiers.Reverse();
logger.Info("Events loaded successfully. " + events.Count + " entries.");
}
public void loadEvents()
{
events.Clear();
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
// Get the Event list worksheet
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries.ToList().Find(new Predicate<AtomEntry>(findEventsWorkSheet));
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.OrderByColumn = EVENT_SHORT_COL;
ListFeed listFeed = GDriveManager.getService().Query(listQuery);
List<BulkLoader> rows = new List<BulkLoader>();
// Iterate through each row, printing its cell values.
foreach (ListEntry row in listFeed.Entries) {
// Print the first column's cell value
//logger.Debug(row.Title.Text);
EventEntry evt = new EventEntry();
// Iterate over the remaining columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements) {
//logger.Debug(element.Value);
switch (element.LocalName.ToLower()) {
case EVENT_EVENT_COL:
evt.eventName = element.Value;
break;
case EVENT_SHORT_COL:
evt.shortName = element.Value;
break;
case EVENT_TIER_COL:
evt.tier = element.Value;
break;
}
}
events.Add(evt);
rows.Add(evt);
}
DBManager.getManager().bulkInsert(rows, "events");
logger.Info("Events loaded successfully. " + events.Count + " entries.");
}
public void Init()
{
query = new ListQuery("");
}
protected void ddlexcellst_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
if (ddlexcellst.SelectedIndex == 0) {
Label2.Visible = true;
}
else
{
Label2.Visible = false;
OAuth2Parameters parameters_lst = oauthcredentials();
GOAuth2RequestFactory requestFactory =
new GOAuth2RequestFactory(null, "Fusion-SpreadSheet", parameters_lst);
SpreadsheetsService service = new SpreadsheetsService("Fusion-SpreadSheet");
service.RequestFactory = requestFactory;
service.RequestFactory = requestFactory;
// TODO: Authorize the service object for a specific user (see other sections)
// Instantiate a SpreadsheetQuery object to retrieve spreadsheets.
SpreadsheetQuery query = new SpreadsheetQuery();
// Make a request to the API and get all spreadsheets.
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count == 0)
{
// TODO: There were no spreadsheets, act accordingly.
}
// TODO: Choose a spreadsheet more intelligently based on your
// app's needs.
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[Convert.ToInt32(ddlexcellst.SelectedIndex) - 1];
//Response.Write(spreadsheet.Title.Text + "\n");
// Get the first worksheet of the first spreadsheet.
// TODO: Choose a worksheet more intelligently based on your
// app's needs.
WorksheetFeed wsFeed = spreadsheet.Worksheets;
WorksheetEntry worksheet = (WorksheetEntry)wsFeed.Entries[0];
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
listQuery.StartIndex = 0;
ListFeed listFeed = service.Query(listQuery);
// Iterate through each row, printing its cell values.
List<category> chart_categories = new List<category>();
ChartModels result = new ChartModels();
categories final_categories = new categories();
List<categories> list_categories = new List<categories>();
List<dataset> collect_dataset = new List<dataset>();
foreach (ListEntry row in listFeed.Entries)
{
// Print the first column's cell value
TableRow tr = new TableRow();
// Response.Write(row.Title.Text + "\n");
// Iterate over the remaining columns, and print each cell value
dataset final_dataset = new dataset();
List<data> collect_data = new List<data>();
foreach (ListEntry.Custom element in row.Elements)
{
if (row.Title.Text == "Row: 2")
{
category chart_category = new category();
chart_category.label = element.Value.ToString();
chart_categories.Add(chart_category);
}
else
{
data data_value = new data();
final_dataset.seriesname = row.Title.Text;
int n;
bool isNumeric = int.TryParse(element.Value, out n);
if (isNumeric)
{
data_value.value = element.Value.ToString();
collect_data.Add(data_value);
}
}
// Response.Write(element.Value + "\n");
TableCell tc = new TableCell();
if (row.Title.Text == "Row: 2")
{
tc.Text = "";
}
else
tc.Text = element.Value;
tr.Cells.Add(tc);
}
Table1.Rows.Add(tr);
if (collect_data.Count != 0)
{
final_dataset.data = collect_data;
collect_dataset.Add(final_dataset);
}
}
final_categories.category = chart_categories;
result.dataset = collect_dataset;
list_categories.Add(final_categories);
result.categories = list_categories;
JavaScriptSerializer js = new JavaScriptSerializer();
string res = js.Serialize(result);
string chartjson = JsonConvert.SerializeObject(result.categories);
StringBuilder strJson = new StringBuilder();
strJson.Append("{" +
"'chart': {" +
"'caption': 'Quarterly revenue'," +
"'subCaption':'Last year'," +
"'xAxisName':'Quarter (Click to drill down)'," +
"'subcaptionFontColor':'#0075c2'," +
"'numberPrefix': '$'," +
"'formatNumberScale': '1'," +
"'placeValuesInside': '1'," +
"'decimals': '0'" +
"},");
strJson.Append("'categories':");
strJson.Append(chartjson);
strJson.Append(",");
strJson.Append("'dataset':");
string chartdatajson = JsonConvert.SerializeObject(result.dataset);
strJson.Append(chartdatajson);
strJson.Append("}");
// Initialize the chart.
Chart sales = new Chart("mscolumn3d", "myChart", "600", "350", "json", strJson.ToString());
// Render the chart.
Label1.Text = sales.Render();
}
}
catch (Exception)
{
Response.Redirect("Default.aspx");
}
}
public formatSheet(string Username, string Password, string Sheet, string Worksheet)
{
dt = new DataTable();
try
{
SpreadsheetsService service = new
SpreadsheetsService("Anachrophobe");
service.setUserCredentials(Username, Password);
SpreadsheetQuery query = new SpreadsheetQuery();
query.Title = Sheet;
SpreadsheetFeed feed = service.Query(query);
if (feed.Entries.Count != 1) return;
AtomLink link =
feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel,
null);
WorksheetQuery worksheetQuery = new
WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = Worksheet;
WorksheetFeed worksheetFeed =
service.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1) return;
WorksheetEntry worksheet =
(WorksheetEntry)worksheetFeed.Entries[0];
AtomLink listFeedLink =
worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new
ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
// define the table's schema
dt.Columns.Add(new DataColumn("A", typeof(string)));
dt.Columns.Add(new DataColumn("B", typeof(string)));
dt.Columns.Add(new DataColumn("C", typeof(string)));
dt.Columns.Add(new DataColumn("D", typeof(string)));
foreach (ListEntry worksheetRow in listFeed.Entries)
{
string station = string.Empty;
string scanData =
worksheetRow.Elements[0].Value.ToString();
string scanData1 =
worksheetRow.Elements[1].Value.ToString();
string[] arSD = new string[100];
DataRow dr = dt.NewRow();
dr["A"] = scanData;
dr["B"] = scanData1;
dt.Rows.Add(dr);
}
//dgwTabla.DataSource = dt;
}
catch (Exception ex)
{
}
}
/// <summary>
/// overwritten Query method
/// </summary>
/// <param name="feedQuery">The FeedQuery touse</param>
/// <returns>the retrieved ListFeed</returns>
public ListFeed Query(ListQuery feedQuery)
{
return base.Query(feedQuery) as ListFeed;
}
private ListFeed GetList()
{
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
return listFeed;
}
public string ParseGoogleDocsSpreadsheet(Hashtable State, string use_spreadsheet_name)
{
try
{
SpreadsheetsService service = new SpreadsheetsService(State["SelectedApp"].ToString());
GOAuthRequestFactory requestFactory = new GOAuthRequestFactory("wise", "MobiFlex");
requestFactory.ConsumerKey = ConfigurationManager.AppSettings["GoogleAppsKey"];
requestFactory.ConsumerSecret = ConfigurationManager.AppSettings["GoogleAppsSecret"];
service.RequestFactory = requestFactory;
//get all spreadsheets
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
query.OAuthRequestorId = State["CustomerEmail"].ToString();
query.Uri = new Uri("https://spreadsheets.google.com/feeds/spreadsheets/private/full?xoauth_requestor_id=" + State["CustomerEmail"].ToString());
SpreadsheetFeed feed = service.Query(query);
bool found_spreadsheet = false;
Hashtable tables = new Hashtable();
foreach (SpreadsheetEntry entry in feed.Entries)
{
string spreadsheet_name = entry.Title.Text;
if (spreadsheet_name.ToLower() == use_spreadsheet_name.ToLower())
{
//Use this spreadsheet
found_spreadsheet = true;
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
//get all worksheets
WorksheetQuery wk_query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wk_feed = service.Query(wk_query);
foreach (WorksheetEntry worksheet in wk_feed.Entries)
{
string table_name = worksheet.Title.Text;
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery list_query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed list_feed = service.Query(list_query);
//get field names
if (list_feed.Entries.Count == 0)
{
return "The worksheet " + table_name + " has no values.";
}
ListEntry fieldRow = (ListEntry)list_feed.Entries[0];
ArrayList field_list = new ArrayList();
foreach (ListEntry.Custom column in fieldRow.Elements)
{
Hashtable field = new Hashtable();
field["name"] = column.LocalName;
field_list.Add(field);
}
tables[table_name] = field_list;
}
break;
}
}
if (!found_spreadsheet)
{
return use_spreadsheet_name + " could not be found";
}
Util util = new Util();
string connection_string = "spreadsheet=" + use_spreadsheet_name +
";consumer_key=" + ConfigurationManager.AppSettings["GoogleAppsKey"] +
";consumer_secret=" + ConfigurationManager.AppSettings["GoogleAppsSecret"] +
";requestor_id=" + State["Username"].ToString();
State["DBConnectionString"] = connection_string;
util.SaveDatabaseSchema(State, "GoogleDocs", connection_string, tables);
return "OK";
}
catch (Exception ex)
{
Util util = new Util();
util.LogError(State, ex);
if (ex.Message.Contains("Execution of request failed"))
return "Credentials to your Google Docs Account or spreadsheet name are not valid.";
else
return "There was an internal error with access to your Google Docs account.";
}
}
public string ParseGoogleDocsSpreadsheet(Hashtable State,string use_spreadsheet_name, string username, string password)
{
try
{
SpreadsheetsService service = new SpreadsheetsService(State["SelectedApp"].ToString());
service.setUserCredentials(username, password);
//get all spreadsheets
Google.GData.Spreadsheets.SpreadsheetQuery query = new Google.GData.Spreadsheets.SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
bool found_spreadsheet = false;
Hashtable tables = new Hashtable();
foreach (SpreadsheetEntry entry in feed.Entries)
{
string spreadsheet_name = entry.Title.Text;
if (spreadsheet_name.ToLower() == use_spreadsheet_name.ToLower())
{
//Use this spreadsheet
found_spreadsheet = true;
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
//get all worksheets
WorksheetQuery wk_query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wk_feed = service.Query(wk_query);
foreach (WorksheetEntry worksheet in wk_feed.Entries)
{
string table_name = worksheet.Title.Text;
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
ListQuery list_query = new ListQuery(listFeedLink.HRef.ToString());
ListFeed list_feed = service.Query(list_query);
//get field names
if (list_feed.Entries.Count == 0)
{
return "The worksheet " + table_name + " has no values.";
}
ListEntry fieldRow = (ListEntry)list_feed.Entries[0];
ArrayList field_list = new ArrayList();
foreach (ListEntry.Custom column in fieldRow.Elements)
{
Hashtable field = new Hashtable();
field["name"] = column.LocalName;
field_list.Add(field);
}
tables[table_name] = field_list;
}
break;
}
}
if (!found_spreadsheet)
{
return use_spreadsheet_name + " could not be found";
}
Util util = new Util();
string connection_string = "spreadsheet=" + use_spreadsheet_name + ";username="******";password="******"DBConnectionString"] = connection_string;
util.SaveDatabaseSchema(State, "GoogleDocs", connection_string, tables);
return "OK";
}
catch (Exception ex)
{
Util util = new Util();
util.LogError(State, ex);
if (ex.Message.Contains("Execution of request failed"))
return "Credentials to your Google Docs Account or spreadsheet name are not valid.";
else
return "There was an internal error with access to your Google Docs account.";
}
}
public List<Course> FetchOldCourseSheet()
{
var service = InitiateService();
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry ss = null;
foreach (SpreadsheetEntry spreadsheetEntry in feed.Entries)
{
if (spreadsheetEntry.Title.Text == SpreadSheetTitle)
{
ss = spreadsheetEntry;
break;
}
}
if (ss == null) return null;
WorksheetFeed wsFeed = ss.Worksheets;
WorksheetEntry worksheet = null;
foreach (WorksheetEntry worksheetEntry in wsFeed.Entries)
{
if (worksheetEntry.Title.Text == WorkSheetTitle)
{
worksheet = worksheetEntry;
break;
}
}
if (worksheet == null) return null;
// Define the URL to request the list feed of the worksheet.
AtomLink listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
// Fetch the list feed of the worksheet.
ListQuery listQuery = new ListQuery(listFeedLink.HRef.ToString());
ListFeed listFeed = service.Query(listQuery);
var courses = new List<Course>();
var currentCategoryName = "";
// Iterate through each row, printing its cell values.
foreach (ListEntry row in listFeed.Entries)
{
var course = new Course();
// Iterate over the remaining columns, and print each cell value
foreach (ListEntry.Custom element in row.Elements)
{
if (element.LocalName == HeaderTitle)
{
course.Title = element.Value;
}
else if (element.LocalName == HeaderAuthor)
{
course.Author = element.Value;
}
else if (element.LocalName == HeaderLevel)
{
course.Level = element.Value;
}
else if (element.LocalName == HeaderDuration)
{
if (string.IsNullOrEmpty(element.Value) == false)
course.Duration = TimeSpan.Parse(element.Value);
}
else if (element.LocalName == HeaderRelease)
{
if (string.IsNullOrEmpty(element.Value) == false)
course.ReleaseDate = System.DateTime.Parse(element.Value);
}
else if (element.LocalName == HeaderStatus)
{
if (string.IsNullOrEmpty(element.Value) == false)
course.LearningStatus = (LearningStatus)Enum.Parse(typeof(LearningStatus), element.Value);
}
else if (element.LocalName == HeaderCommment)
{
course.Comment = element.Value;
}
}
if (!string.IsNullOrEmpty(course.Title.Trim()) && !string.IsNullOrEmpty(course.Author.Trim()) && string.IsNullOrEmpty(course.Level.Trim()))
{
currentCategoryName = course.Title;
}
if (string.IsNullOrEmpty(course.Level.Trim()) == false) //Determine whether this is a actual course
{
course.ParentCateogry = currentCategoryName;
courses.Add(course);
}
}
return courses;
}
public static ListFeed Query(ListQuery lq)
{
return _Service.Query(lq);
}