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);
}
public ActionResult AllCells()
{
SpreadsheetsService service;
service = new SpreadsheetsService("DevFestEvent");
service.setUserCredentials(
ConfigurationManager.AppSettings["GoogleUser"],
ConfigurationManager.AppSettings["GoogleUserPassword"]);
SpreadsheetQuery q = new SpreadsheetQuery(App.SheetFeedData);
var feed = service.Query(q);
AtomLink l = feed.Entries.First().Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(l.HRef.ToString());
WorksheetFeed f = service.Query(query);
foreach (var item in f.Entries)
{
AtomLink cellFeedLink = item.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cellfeedlink = cellFeedLink.HRef.ToString();
CellQuery cquery = new CellQuery(cellfeedlink);
CellFeed cfeed = service.Query(cquery);
Console.WriteLine("Cells in this worksheet:");
uint rownum = 2;
foreach (CellEntry curCell in cfeed.Entries)
{
rownum = curCell.Cell.Row;
}
}
return View(f);
}
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();
}
}
public static AtomEntryCollection GetSheetNames(SpreadsheetsService zSpreadsheetService, AtomEntry zSheetEntry)
{
var link = zSheetEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString());
var wsfeed = zSpreadsheetService.Query(wsquery);
return wsfeed.Entries;
}
public WorksheetEntry GetWorksheet(SpreadsheetFeed feed, string worksheetName)
{
var link = feed.Entries[0].Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var worksheetQuery = new WorksheetQuery(link.HRef.ToString());
worksheetQuery.Title = worksheetName;
var worksheetFeed = _service.Query(worksheetQuery);
if (worksheetFeed.Entries.Count != 1) throw new Exception(String.Format("Did not find exactly 1 {0} worksheet.", worksheetName));
return (WorksheetEntry)worksheetFeed.Entries[0];
}
private IEnumerable<WorksheetEntry> GetWorksheetEntrees(SpreadsheetsService service = null)
{
if (service == null)
service = new SpreadsheetsService(applicationName);
service.setUserCredentials(userName, password);
var worksheetQuery = new WorksheetQuery(spreadsheetKey, "private", "full");
var wsFeed = service.Query(worksheetQuery);
return wsFeed.Entries.OfType<WorksheetEntry>();
}
/// <summary>
/// Prints a list of all worksheets in the specified spreadsheet.
/// </summary>
/// <param name="service">an authenticated SpreadsheetsService object</param>
/// <param name="entry">the spreadsheet whose worksheets are to be retrieved</param>
private static void PrintAllWorksheets(SpreadsheetsService service,
SpreadsheetEntry entry)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed = service.Query(query);
Console.WriteLine("Worksheets in " + entry.Title.Text + ":");
foreach (WorksheetEntry worksheet in feed.Entries)
{
allWorksheets.Add(worksheet);
Console.WriteLine(" " + worksheet.Title.Text);
}
Console.WriteLine();
}
private WorksheetEntry GetWorksheet(SpreadsheetEntry pSpreadsheetEntry)
{
AtomLink link = tDicingSpreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
string tLink = link.HRef.ToString();
WorksheetQuery tWorksheetQuery = new WorksheetQuery(tLink);
tWorksheetQuery.Title = tSheetName;
WorksheetFeed wfeed = tService.Query(tWorksheetQuery);
WorksheetEntry wentry = wfeed.Entries.FirstOrDefault() as WorksheetEntry;
return wentry;
}
private static WorksheetEntry CreateWorksheet(string worksheetTitle, uint rows, uint columns, SpreadsheetsService service, SpreadsheetFeed feed)
{
SpreadsheetEntry spreadsheet = (SpreadsheetEntry)feed.Entries[0];
WorksheetEntry worksheet = new WorksheetEntry();
worksheet.Title.Text = worksheetTitle;
worksheet.Rows = rows;
worksheet.Cols = columns;
WorksheetFeed wsFeed = spreadsheet.Worksheets;
service.Insert(wsFeed, worksheet);
// retrieve the newly created worksheet from google
AtomLink worksheetLink = spreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery worksheetQuery = new WorksheetQuery(worksheetLink.HRef.ToString())
{
Title = worksheetTitle,
};
wsFeed = service.Query(worksheetQuery);
return (WorksheetEntry)wsFeed.Entries[0];
}
private void FindValidAuctionTypes()
{
validTypes = new List<string>();
WorksheetQuery wsQuery = new WorksheetQuery(String.Format(@"http://spreadsheets.google.com/feeds/worksheets/{0}/private/full", NotificationAdministrationSheetKey));
AtomEntryCollection worksheetEntries = MySpreadsheetService.Query(wsQuery).Entries;
foreach (WorksheetEntry sheet in worksheetEntries)
{
if (!"Admin,Template".Contains(sheet.Title.Text))
validTypes.Add(sheet.Title.Text);
}
}
static void Main(string[] args)
{
Console.WriteLine("Input google username:"******"Input google password:"******"Input spreadsheet to enumerate");
string spreadsheetName = string.Empty;// Console.ReadLine();
Console.WriteLine("Sheet to use");
string sheetName = string.Empty;// Console.ReadLine();
string csvString = string.Empty;
if (string.IsNullOrEmpty(spreadsheetName))
{
spreadsheetName = "Gig-it Performance Test Data ";
}
if (string.IsNullOrWhiteSpace(sheetName))
{
sheetName = "Sheet1";
}
SpreadsheetsService service = new SpreadsheetsService("rightscale.com");
service.setUserCredentials(userName, password);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
Console.WriteLine("Spreadsheets:");
AtomLink spreadSheetLink = null;
foreach (SpreadsheetEntry se in feed.Entries)
{
Console.WriteLine(" " + se.Title.Text);
if (se.Title.Text == spreadsheetName)
{
spreadSheetLink = se.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
}
}
if (spreadSheetLink != null)
{
WorksheetQuery wsQuery = new WorksheetQuery(spreadSheetLink.HRef.ToString());
WorksheetFeed wsFeed = service.Query(wsQuery);
AtomLink cellFeedLink = null;
foreach (WorksheetEntry we in wsFeed.Entries)
{
Console.WriteLine(" --" + we.Title.Text);
if (we.Title.Text == sheetName)
{
cellFeedLink = we.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
}
}
if (cellFeedLink != null)
{
CellQuery cellQuery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
uint currRow = 2;
Guid rowID = Guid.NewGuid();
csvString += buildEntry(rowID.ToString() );
foreach (CellEntry curCell in cellFeed.Entries)
{
if (curCell.Row > 1)
{
if (curCell.Row != currRow)
{
rowID = Guid.NewGuid();
csvString += Environment.NewLine + buildEntry(rowID.ToString() );
currRow = curCell.Row;
}
if (curCell.Cell.Column != 6)
{
csvString += buildEntry(curCell.Value );
}
else
{
//do nothing
}
}
}
if (!string.IsNullOrWhiteSpace(csvString))
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"D:\Dropbox\Services Operations\Clients\Chasin 3D Gig-It\E2E_SOW#_1048\3-Development\TestData\_harHeaders.csv"))
{
sw.Write(csvString);
sw.Flush();
}
}
}
}
string[] files = System.IO.Directory.GetFiles(@"D:\Dropbox\Services Operations\Clients\Chasin 3D Gig-It\E2E_SOW#_1048\3-Development\TestData\TestRun1", "*.har");
if(files != null && files.Length > 0)
{
foreach(string f in files)
{
try
{
string testID = f.Replace(".har", "").Replace(@"D:\Dropbox\Services Operations\Clients\Chasin 3D Gig-It\E2E_SOW#_1048\3-Development\TestData\TestRun1\", "").Split('_').First<string>();
HarFile hf = null;
using (System.IO.StreamReader sr = new System.IO.StreamReader(f))
{
string contents = sr.ReadToEnd();
hf = JsonConvert.DeserializeObject<HarFile>(contents);
}
string dataFileContents = string.Empty;
foreach (var entries in hf.log.entries)
{
string dataline = string.Empty;
dataline += buildEntry(testID);
dataline += buildEntry(entries.request.url);
dataline += buildEntry(entries.request.method);
dataline += buildEntry(entries.request.headersSize.ToString());
dataline += buildEntry(entries.request.bodySize.ToString());
dataline += buildEntry(entries.time.ToString());
dataline += buildEntry(entries.timings.dns);
dataline += buildEntry(entries.timings.connect);
dataline += buildEntry(entries.timings.send);
dataline += buildEntry(entries.timings.blocked);
dataline += buildEntry(entries.timings.wait);
dataline += buildEntry(entries.timings.ssl);
dataline += buildEntry(entries.timings.receive);
dataline += buildEntry(entries.response.bodySize.ToString());
dataline += buildEntry(entries.response.status.ToString());
dataline += buildEntry(entries.response.statusText);
dataFileContents += dataline + Environment.NewLine;
}
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"D:\Dropbox\Services Operations\Clients\Chasin 3D Gig-It\E2E_SOW#_1048\3-Development\TestData\_hardetail.csv",true))
{
sw.Write(dataFileContents);
sw.Flush();
}
}
catch (JsonSerializationException)
{
}
catch (JsonReaderException)
{
}
}
}
}
private CellEntry[,] GetCellEntryArray(string workbookKey, string SheetName)
{
WorksheetQuery wsQuery = new WorksheetQuery(String.Format(@"http://spreadsheets.google.com/feeds/worksheets/{0}/private/full", workbookKey));
AtomEntryCollection worksheetEntries = MySpreadsheetService.Query(wsQuery).Entries;
CellEntry[,] ce = null;
AtomLink MySheetLink = worksheetEntries.First(x => x.Title.Text == SheetName).Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
CellQuery MySheetQuery = new CellQuery(MySheetLink.HRef.Content);
CellFeed MySheetFeed = MySpreadsheetService.Query(MySheetQuery);
AtomEntryCollection MySheetEntries = MySheetFeed.Entries;
ce = new CellEntry[MySheetFeed.RowCount.IntegerValue, MySheetFeed.ColCount.IntegerValue];
for (int i = 0; i < MySheetEntries.Count; i++)
{
CellEntry entry = MySheetEntries[i] as CellEntry;
//google doc is a 1 based array, convert to 0 based
ce[entry.Row - 1, entry.Column - 1] = entry;
}
return ce;
}
/// <summary>
/// Handler for when the Worksheets page becomes active. Depends on
/// the spreadsheet being selected on the Spreadsheets page.
/// </summary>
/// <param name="sender">Ignored</param>
/// <param name="e">Ignored</param>
void OnEnterSelectWorksheetTab(object sender, System.EventArgs e)
{
if (spreadsheetListView.SelectedItems.Count > 0)
{
// Get the worksheet feed from the selected entry
WorksheetQuery query = new WorksheetQuery(spreadsheetListView.SelectedItems[0].SubItems[1].Text);
SetWorksheetListView(service.Query(query));
}
}
public Dictionary<DateTime, IEnumerable<SaleParsed>> ImportFromFile(string fileName, GoodType type, string producer)
{
var result = new Dictionary<DateTime, IEnumerable<SaleParsed>>();
var feed = GetSpreadSheetsFeed();
var mySpreadsheet = (SpreadsheetEntry)feed.Entries.FirstOrDefault(e => e.Title.Text.Equals(fileName, StringComparison.InvariantCultureIgnoreCase));
if (mySpreadsheet == null)
throw new FileNotFoundException(fileName);
//retrieve the worksheets of a particular spreadsheet
var link = mySpreadsheet.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wQuery = new WorksheetQuery(link.HRef.ToString());
var wFeed = spreadsheetsService.Query(wQuery);
//retrieve the cells in a worksheet
//var worksheetEntry = (WorksheetEntry)wFeed.Entries[0];
foreach (var wSheetAtomEntry in wFeed.Entries)
{
var wSheet = (WorksheetEntry)wSheetAtomEntry;
var cLink = wSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cQuery = new CellQuery(cLink.HRef.ToString());
var cFeed = spreadsheetsService.Query(cQuery);
foreach (var cellAtomEntryEntry in cFeed.Entries)
{
var cCell = (CellEntry) cellAtomEntryEntry;
Console.WriteLine("Value on row {0} and column {1} is {2}", cCell.Cell.Row,
cCell.Cell.Column, cCell.Cell.Value);
}
}
//using (var fs = File.OpenRead(fileName))
//{
// var workbook = new SpreadsheetEntry();
// for (var sheetIdx = 0; sheetIdx < workbook.NumberOfSheets; sheetIdx++)
// {
// ISheet sheet = workbook.GetSheetAt(sheetIdx);
// var datesRow = sheet.GetRow(Settings.DatesRow);
// if (datesRow == null)
// continue;
// var datesCell = datesRow.GetCell(Settings.DatesColumn);
// if (datesCell == null)
// continue;
// DateTime startDate = GetStartDate(datesCell);
// var sales = ParseSales(sheet, datesCell, startDate, type, producer);
// foreach (var s in sales)
// result.Add(s.Key, s.Value);
// }
//}
return result;
}
public void ReadDrive(string database)
{
SpreadsheetsService myService = new SpreadsheetsService("auniquename");
myService.setUserCredentials(emailUsername, emailPassword);
// GET THE SPREADSHEET from all the docs
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);
var campaign = (from x in feed.Entries where x.Title.Text.Contains("BeastAuths") select x).First();
// GET THE first WORKSHEET from that sheet
AtomLink link = campaign.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query2 = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed2 = myService.Query(query2);
var campaignSheet = feed2.Entries.First();
AtomLink cellFeedLink = campaignSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query3 = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed3 = myService.Query(query3);
foreach (CellEntry curCell in feed3.Entries)
{
using (StringReader reader = new StringReader(macID))
{
string line;
while ((line = reader.ReadLine()) != null && access == false)
{
// MessageBox.Show(line);
if (curCell.Cell.Value.ToString().Replace(" ", "").Replace("\r\n", "").Replace(System.Environment.NewLine, "").Contains(line.Replace(" ", "").Replace("\r\n", "").Replace(System.Environment.NewLine, "")) && Regex.IsMatch(line, @"\d") == true)
{
access = true;
}
}
}
}
}
// TODO: all callers of this method should handle exceptions (InvalidCredentialsException etc.)
public static List<List<string>> GetSpreadsheet(SpreadsheetsService zSpreadsheetService, string sSpreadsheetName, string sSheetName)
{
var listLines = new List<List<string>>();
// get all spreadsheets
var query = new SpreadsheetQuery
{
// only ask for the spreadsheet by the given name
Title = sSpreadsheetName
};
var feed = zSpreadsheetService.Query(query);
var bFoundSpreadsheet = false;
foreach (var entry in feed.Entries)
{
if (entry.Title.Text == sSpreadsheetName)
{
bFoundSpreadsheet = true;
Logger.AddLogLine("Google: Found spreadsheet: " + sSpreadsheetName);
var link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
var wsquery = new WorksheetQuery(link.HRef.ToString())
{
Title = sSheetName
};
var wsfeed = zSpreadsheetService.Query(wsquery);
var bFoundSheet = false;
foreach (var worksheet in wsfeed.Entries)
{
//System.Diagnostics.Trace.WriteLine(worksheet.Title.Text);
if (worksheet.Title.Text == sSheetName)
{
bFoundSheet = true;
Logger.AddLogLine("Google: Found sheet: " + sSheetName);
var cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
var cquery = new CellQuery(cellFeedLink.HRef.ToString());
var cfeed = zSpreadsheetService.Query(cquery);
//System.Diagnostics.Trace.WriteLine("Cells in this worksheet:");
uint uRow = 1;
uint uCol = 1;
var listRow = new List<string>();
foreach (var curCell in cfeed.Entries.OfType<CellEntry>())
{
// NOTE: This completely ignores blank lines in the spreadsheet
if (uRow != curCell.Cell.Row)
{
// new row, flush the previous
listLines.Add(listRow);
listRow = new List<string>();
uRow = curCell.Cell.Row;
uCol = 1;
}
// fill in any missing columns with empty strings
if (uCol != curCell.Cell.Column)
{
while (uCol < curCell.Cell.Column)
{
listRow.Add(string.Empty);
uCol++;
}
}
listRow.Add(curCell.Cell.Value);
uCol++;
}
// always flush the last line
listLines.Add(listRow);
}
if (bFoundSheet)
break;
}
if (!bFoundSheet)
Logger.AddLogLine("Google: Failed to find sheet: " + sSheetName);
}
if (bFoundSpreadsheet)
break;
}
if (!bFoundSpreadsheet)
Logger.AddLogLine("Google: Failed to find spreadsheet: " + sSpreadsheetName);
return listLines;
}
private List<WorksheetEntry> GetAllWorksheets(SpreadsheetEntry entry)
{
AtomLink listLink = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, AtomLink.ATOM_TYPE);
WorksheetQuery query = new WorksheetQuery(listLink.HRef.ToString());
WorksheetFeed feed = spreadsheetService.Query(query);
var list = new List<WorksheetEntry>();
foreach (WorksheetEntry worksheet in feed.Entries)
{
list.Add(worksheet);
}
return list;
}
private static void AddManualWorkbookByUrl(string in_manualUrl, Google2uData in_instance)
{
WorkbookBase info;
if (string.IsNullOrEmpty(in_manualUrl))
{
Debug.LogError(LocalizationInfo.Localize(Localization.rowIds.ID_ERROR_EMPTY_URL));
return;
}
var refreshManualWorkbookCache = false;
try
{
var key =
in_manualUrl.Substring(in_manualUrl.IndexOf("key=", StringComparison.InvariantCultureIgnoreCase) + 4);
key = key.Split('&')[0];
var singleQuery = new WorksheetQuery(key, "public", "values");
if (in_instance.ManualService == null && !SetupManualService(in_instance))
return;
var feed = in_instance.ManualService.Query(singleQuery);
var finalUrl = in_manualUrl.Split('&')[0];
if (feed != null)
{
info =
in_instance.ManualWorkbooks.Find(in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) ??
in_instance.AccountWorkbooks.Find(in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl))
as WorkbookBase;
if (info == null)
{
var newWorkbook = new Google2uManualWorkbook(feed, finalUrl, feed.Title.Text,
in_instance.ManualService);
in_instance.ManualWorkbooks.Add(newWorkbook);
refreshManualWorkbookCache = true;
}
}
}
catch
{
try
{
var key =
in_manualUrl.Substring(
in_manualUrl.IndexOf("spreadsheets/d/", StringComparison.InvariantCultureIgnoreCase) + 15);
key = key.Split('/')[0];
if (in_instance.ManualService == null && !SetupManualService(in_instance))
return;
var singleQuery = new WorksheetQuery(key, "public", "values");
var feed = in_instance.ManualService.Query(singleQuery);
var urlParts = in_manualUrl.Split('/');
var finalUrl = "";
var urlBuild = 0;
string urlPart;
do
{
urlPart = urlParts[urlBuild];
finalUrl += urlPart + '/';
urlBuild++;
} while (urlPart != key);
if (feed != null)
{
info =
in_instance.ManualWorkbooks.Find(
in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) ??
in_instance.AccountWorkbooks.Find(
in_i => Google2uGUIUtil.GfuStrCmp(in_i.WorkbookUrl, finalUrl)) as WorkbookBase;
if (info == null)
{
var newWorkbook = new Google2uManualWorkbook(feed, finalUrl, feed.Title.Text,
in_instance.ManualService);
in_instance.ManualWorkbooks.Add(newWorkbook);
refreshManualWorkbookCache = true;
}
}
}
catch
{
Debug.LogError(LocalizationInfo.Localize(Localization.rowIds.ID_ERROR_INVALID_URL));
}
}
if (refreshManualWorkbookCache)
{
in_instance.ManualWorkbookCache = string.Empty;
foreach (var Google2uSpreadsheet in in_instance.ManualWorkbooks)
{
in_instance.ManualWorkbookCache += Google2uSpreadsheet.WorkbookUrl + "|";
}
}
}
private static void Insert(int feedNumber,List<String> data)
{
try
{
SpreadsheetsService service = new SpreadsheetsService("exampleCo-exampleApp-1");
service.setUserCredentials("mauriceprojet", "xxxxxxxx");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry entry = (SpreadsheetEntry)feed.Entries[feedNumber];
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query2 = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed2 = service.Query(query2);
WorksheetEntry worksheet = (WorksheetEntry )feed2.Entries[0];
InsertRow(service, worksheet, data);
}
catch (Exception e)
{
MessageBox.Show("Error " + e.Message);
}
}
public static void GDocSupply(string[] args)
{
MUs MUList = new MUs();
List<string> blackList = new List<string>();
if (args.Length != 16)
{
ConsoleLog.WriteLine("Usage: armysupply eLogin ePassword ePin gLogin gPassword document_name sheet_name do_tanks do_food food_q period max_tanks max_health validation_type(none/MU/citizenship) do_init_block");
ConsoleLog.WriteLine("Example: armysupply snab_org 123456 1111 [email protected] 54321 \"Army supply\" \"Sheet1\" true true 5 60 8 300 MU false");
return;
}
string eLogin = args[1];
string ePassword = args[2];
string ePin = args[3];
string gLogin = args[4];
string gPassword = args[5];
string sDocumentName = args[6];
string sSheetName = args[7];
bool bDoTanks = Convert.ToBoolean(args[8]);
bool bDoFood = Convert.ToBoolean(args[9]);
int iFoodQ = Convert.ToInt32(args[10]);
int iPeriod = Convert.ToInt32(args[11]);
int iMaxTanks = Convert.ToInt32(args[12]);
int iMaxHP = Convert.ToInt32(args[13]);
string sValidation = args[14];
bool bDoInitBlock = Convert.ToBoolean(args[15]);
string token = "";
ConsoleLog.WriteLine("Loading document...");
#region Open sheet
SpreadsheetsService sheetService = new SpreadsheetsService("EArmy supply");
sheetService.setUserCredentials(gLogin, gPassword);
SpreadsheetQuery sheetQuery = new SpreadsheetQuery();
SpreadsheetFeed sheetFeed = sheetService.Query(sheetQuery);
SpreadsheetEntry sheetSnab = null;
foreach (SpreadsheetEntry entry in sheetFeed.Entries)
{
if (entry.Title.Text == sDocumentName)
{
sheetSnab = entry;
break;
}
}
if (sheetSnab == null)
{
ConsoleLog.WriteLine("Document '" + sDocumentName + "' not found");
return;
}
AtomLink sheetLink = sheetSnab.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wsQuery = new WorksheetQuery(sheetLink.HRef.ToString());
WorksheetFeed wsFeed = sheetService.Query(wsQuery);
WorksheetEntry wsSnab = null;
WorksheetEntry wsSettings = null;
foreach (WorksheetEntry ws in wsFeed.Entries)
{
if (ws.Title.Text == sSheetName)
{
wsSnab = ws;
}
if (ws.Title.Text == "Settings")
{
wsSettings = ws;
}
}
if (wsSnab == null)
{
ConsoleLog.WriteLine("Page '" + sSheetName + "' not found");
return;
}
if (wsSettings != null)
{
LoadSettings(sheetService, wsSettings, MUList, blackList);
}
#endregion
#region Login to supplier
DonaterBot bt = new DonaterBot(
eLogin,
eLogin,
ePassword,
ePin,
"Mozilla//4.0 (compatible; MSIE 7.0; Windows NT 6.0)",
Globals.BotConfig.AntiGateKey,
Globals.BotConfig.precaptchaBufferSize);
bt.HttpClient.SetProxy(null, null);
bool loggedIn = false;
int iTryToConnect = 0;
System.Random rnd = new System.Random();
#endregion
while (true)
{
try
{
ConsoleLog.WriteLine("Loading cells...");
#region Query cells
AtomLink cellLink = wsSnab.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query = new CellQuery(cellLink.HRef.ToString());
query.ReturnEmpty = ReturnEmptyCells.yes;
query.MaximumColumn = 13;
CellFeed feed = sheetService.Query(query);
List<ItemStorage> lines = new List<ItemStorage>();
ItemStorage item = null;
#endregion
#region Load cells
lines.Clear();
foreach (CellEntry curCell in feed.Entries)
{
if (curCell.Cell.Row < 2) continue;
item = lines.Find(l => l.row == curCell.Cell.Row);
if (item == null)
{
item = new ItemStorage();
lines.Add(item);
item.row = curCell.Cell.Row;
}
if (curCell.Cell.Column == 1) // time
{
item.time = curCell.Cell.Value;
}
if (curCell.Cell.Column == 2) // login
{
item.login = curCell.Cell.Value;
}
if (curCell.Cell.Column == 3) // link to profile
{
item.personID = curCell.Cell.Value;
try
{
item.personID = item.personID.Substring(item.personID.LastIndexOf('/') + 1);
Convert.ToInt64(item.personID);
}
catch
{
item.personID = "";
}
// Cut ID from link
}
if (curCell.Cell.Column == 4) // wellness
{
try
{
item.wellness = Convert.ToInt32(curCell.Cell.Value);
}
catch
{
item.wellness = 0;
}
}
if (curCell.Cell.Column == 5) // needed tanks
{
try
{
item.tanks = Convert.ToInt32(curCell.Cell.Value);
}
catch
{
item.tanks = 0;
}
}
if (curCell.Cell.Column == 6) // military unit
{
item.militaryUnit = curCell.Cell.Value;
}
if (curCell.Cell.Column == 8) // done tanks
{
item.doneTanksCell = curCell;
try
{
item.doneTanks = Convert.ToInt32(curCell.Cell.Value);
}
catch
{
item.wellness = 0;
}
}
if (curCell.Cell.Column == 9) // done food q
{
try
{
item.doneFoodQ = Convert.ToInt32(curCell.Cell.Value);
}
catch
{
item.doneFoodQ = 0;
}
item.doneFoodQCell = curCell;
}
if (curCell.Cell.Column == 10) // done food
{
item.doneFoodCell = curCell;
try
{
item.doneFood = Convert.ToInt32(curCell.Cell.Value);
}
catch
{
item.doneFood = 0;
}
}
if (curCell.Cell.Column == 11) // autocomment cell
{
item.comment = curCell.Cell.Value;
item.commentCell = curCell;
}
if (curCell.Cell.Column == 12) // tanks limit cell
{
try
{
if (!String.IsNullOrEmpty(curCell.Cell.Value))
item.tanksLimit = Convert.ToInt32(curCell.Cell.Value);
else
item.tanksLimit = -1;
}
catch
{
item.tanksLimit = -1;
}
item.tanksLimitCell = curCell;
}
if (curCell.Cell.Column == 13) // HP limit cell
{
try
{
if (!String.IsNullOrEmpty(curCell.Cell.Value))
item.foodLimit = Convert.ToInt32(curCell.Cell.Value);
else
item.foodLimit = -1;
}
catch
{
item.foodLimit = -1;
}
item.foodLimitCell = curCell;
}
}
ConsoleLog.WriteLine("Items loaded: " + lines.Count);
#endregion
#region Parce cells
int row = 0;
List<ItemStorage> tmpList = new List<ItemStorage>(lines);
foreach (ItemStorage testItem in tmpList)
{
row++;
if (string.IsNullOrEmpty(testItem.time))
{
lines.Remove(testItem);
continue;
}
if (!string.IsNullOrEmpty(testItem.comment))
{
lines.Remove(testItem);
continue;
}
if (string.IsNullOrEmpty(testItem.personID))
{
lines.Remove(testItem);
testItem.commentCell.Cell.InputValue = "Bad profile";
testItem.commentCell.Update();
continue;
}
if (blackList.Contains(testItem.login))
{
lines.Remove(testItem);
testItem.commentCell.Cell.InputValue = "Blacklist";
testItem.commentCell.Update();
continue;
}
if ((sValidation.ToLower() == "mu") &&
(String.IsNullOrEmpty(MUList[testItem.militaryUnit])))
{
lines.Remove(testItem);
testItem.commentCell.Cell.InputValue = "MU not in list";
testItem.commentCell.Update();
continue;
}
if ((testItem.wellness == 0) && (testItem.tanks == 0))
{
lines.Remove(testItem);
testItem.commentCell.Cell.InputValue = "Bad wellness/tanks";
testItem.commentCell.Update();
continue;
}
//if (testItem.tanks > 30)
//{
// lines.Remove(testItem);
// testItem.commentCell.Cell.InputValue = "Bad tanks";
// testItem.commentCell.Update();
// continue;
//}
if (tmpList.Find(o => (o.personID == testItem.personID && o.viewedForDuplicate)) != null)
{
lines.Remove(testItem);
testItem.commentCell.Cell.InputValue = "Duplicate record";
testItem.commentCell.Update();
continue;
}
if (testItem.tanksLimit == -1)
{
if (bDoInitBlock)
{
testItem.tanksLimit = 0;
}
else
{
testItem.tanksLimit = iMaxTanks;
}
testItem.tanksLimitCell.Cell.InputValue = testItem.tanksLimit.ToString();
testItem.tanksLimitCell.Update();
}
if (testItem.foodLimit == -1)
{
if (bDoInitBlock)
{
testItem.foodLimit = 0;
}
else
{
testItem.foodLimit = iMaxHP;
}
testItem.foodLimitCell.Cell.InputValue = testItem.foodLimit.ToString();
testItem.foodLimitCell.Update();
}
testItem.viewedForDuplicate = true;
ConsoleLog.WriteLine(
row + ": " +
testItem.time + ";" +
testItem.login + ";" +
testItem.personID + ";" +
testItem.wellness + ";" +
testItem.tanks + ";" +
testItem.doneTanks + ";" +
testItem.doneFoodQ + ";" +
testItem.doneFood
);
}
#endregion
#region Log in
if (!loggedIn)
{
iTryToConnect++;
if (iTryToConnect > 10)
break;
ConsoleLog.WriteLine("Trying to login (" + (iTryToConnect).ToString() + ")...");
if (bt.Login())
{
ConsoleLog.WriteLine("Logged in!");
iTryToConnect = 0;
loggedIn = true;
}
else
{
ConsoleLog.WriteLine("Login failed!");
System.Threading.Thread.Sleep(1000);
continue;
}
}
#endregion
//Process donations
foreach (ItemStorage donateItem in lines)
{
ConsoleLog.WriteLine("Donating to: login="******", id=" + donateItem.personID);
#region Calculate quantities
int foodQty;
if ((bDoFood) && (donateItem.wellness > 0) && (donateItem.doneFoodQ == 0))
{
foodQty = Convert.ToInt32(Math.Round((double)((double)Math.Min(donateItem.wellness, donateItem.foodLimit) / (iFoodQ * 2))));
foodQty = foodQty - donateItem.doneFood;
}
else
{
foodQty = 0;
}
int tankQty;
if (bDoTanks)
{
tankQty = Math.Min(donateItem.tanks, donateItem.tanksLimit);
tankQty = tankQty - donateItem.doneTanks;
}
else
{
tankQty = 0;
}
if ((foodQty <= 0) && (tankQty <= 0))
{
ConsoleLog.WriteLine("Nothing to donate");
continue;
}
#endregion
#region Open pages and validate
string srcPage = "http://www.erepublik.com/en/citizen/profile/" + donateItem.personID;
bt.CustomRequest(srcPage);
if (!bt.GetLastResponse().Contains("career_tab_content"))
{
ConsoleLog.WriteLine("Profile page loading failed. Try to relogin...");
loggedIn = false;
break;
}
if (bt.GetLastResponse().IndexOf("alt=\"" + donateItem.login.Trim() + "\"", StringComparison.OrdinalIgnoreCase) == -1)
{
ConsoleLog.WriteLine("Donating login validation failed");
donateItem.commentCell.Cell.InputValue = "Profile != Login";
donateItem.commentCell.Update();
continue;
}
else
{
ConsoleLog.WriteLine("Login validated");
}
if ((sValidation.ToLower() == "mu") &&
(bt.GetLastResponse().IndexOf("alt=\"" + MUList[donateItem.militaryUnit.Trim()] + "\"", StringComparison.OrdinalIgnoreCase) == -1))
{
ConsoleLog.WriteLine("Donating MU validation failed (" + MUList[donateItem.militaryUnit.Trim()] + ")");
ConsoleLog.WriteLine(bt.GetLastResponse(), "Storage page");
donateItem.commentCell.Cell.InputValue = "Profile != MU";
donateItem.commentCell.Update();
continue;
}
else
{
ConsoleLog.WriteLine("MU validated");
}
srcPage = "http://economy.erepublik.com/en/citizen/donate/" + donateItem.personID;
bt.CustomRequest(srcPage);
if (bt.CheckPin(true))
{
bt.SubmitPin();
}
if (bt.CheckPin(true))
{
ConsoleLog.WriteLine("Pin validation failed");
break;
}
if (!bt.GetLastResponse().Contains("<th colspan=\"4\" valign=\"middle\">Your storage</th>"))
{
ConsoleLog.WriteLine("Donate page loading failed. Try to relogin...");
loggedIn = false;
break;
}
token = CommonUtils.GetStringBetween(
bt.GetLastResponse(),
"donate_form[_csrf_token]\" value=\"",
"\"");
#endregion
#region Donate food
if (foodQty > 0)
{
ConsoleLog.WriteLine("Donating food: " + foodQty + " (q" + iFoodQ + ")");
if (bt.DonateItem(foodQty.ToString(), Goods.Food.ToString(), iFoodQ.ToString(), token, srcPage))
//if (true)
{
ConsoleLog.WriteLine("Donating food success");
donateItem.doneFoodQCell.Cell.InputValue = iFoodQ.ToString();
donateItem.doneFoodQCell.Update();
donateItem.doneFoodCell.Cell.InputValue = (donateItem.doneFood + foodQty).ToString();
donateItem.doneFoodCell.Update();
}
else
{
ConsoleLog.WriteLine("Donating food failed");
//ConsoleLog.WriteLine(bt.GetLastResponse(), "DonateLog.txt");
}
ConsoleLog.WriteLine("Wait 5 sec...");
System.Threading.Thread.Sleep(5 * 1000);
}
else
{
ConsoleLog.WriteLine("No food needed");
}
#endregion
#region Donate tanks
if (tankQty > 0)
{
ConsoleLog.WriteLine("Donating tanks: " + tankQty);
if (bt.DonateItem(tankQty.ToString(), Goods.Weapon.ToString(), 5.ToString(), token, srcPage))
//if (true)
{
ConsoleLog.WriteLine("Donating tanks success");
donateItem.doneTanksCell.Cell.InputValue = (donateItem.doneTanks + tankQty).ToString();
donateItem.doneTanksCell.Update();
}
else
{
ConsoleLog.WriteLine("Donating tanks failed");
ConsoleLog.WriteLine(bt.GetLastResponse(), "DonateLog.txt");
}
ConsoleLog.WriteLine("Wait 5 sec...");
System.Threading.Thread.Sleep(5 * 1000);
}
else
{
ConsoleLog.WriteLine("No tanks needed");
}
#endregion
}
}
catch (System.Exception e)
{
ConsoleLog.WriteLine("Donater error: " + e.Message);
ConsoleLog.WriteLine(bt.GetLastResponse(), "Responses.txt");
}
ConsoleLog.WriteLine("Waiting for next check");
//break;
System.Threading.Thread.Sleep(iPeriod * 1000);
}
ConsoleLog.WriteLine("It's OK :)");
}
/// <summary>
/// overwritten Query method
/// </summary>
/// <param name="feedQuery">The FeedQuery to use</param>
/// <returns>the retrieved WorksheetFeed</returns>
public WorksheetFeed Query(WorksheetQuery feedQuery)
{
return(base.Query(feedQuery) as WorksheetFeed);
}
/// <summary>
/// overwritten Query method
/// </summary>
/// <param name="feedQuery">The FeedQuery to use</param>
/// <returns>the retrieved WorksheetFeed</returns>
public WorksheetFeed Query(WorksheetQuery feedQuery)
{
return base.Query(feedQuery) as WorksheetFeed;
}
public string isCanLoginIn()
{
try
{
SpreadsheetsService service = new SpreadsheetsService("whatsappshower");
service.setUserCredentials("*****@*****.**", "e4rst6rh");
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
SpreadsheetEntry whatsAppShowerCerEntry = null;
foreach (SpreadsheetEntry entry in feed.Entries)
{
//Console.WriteLine(entry.Title.Text);
if ("whatsAppShowerCer".Equals(entry.Title.Text))
{
whatsAppShowerCerEntry = entry;
}
}
if (whatsAppShowerCerEntry == null)
{
return null;
}
AtomLink link = whatsAppShowerCerEntry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery worksheetQueryQuery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed worksheetQueryFeed = service.Query(worksheetQueryQuery);
WorksheetEntry whatsAppShowerCerWorksheet = null;
foreach (WorksheetEntry worksheet in worksheetQueryFeed.Entries)
{
if ("1".Equals(worksheet.Title.Text))
{
whatsAppShowerCerWorksheet = worksheet;
}
}
if (whatsAppShowerCerWorksheet == null)
{
return null;
}
AtomLink cellFeedLink = whatsAppShowerCerWorksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery cellQueryQuery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cellQueryFeed = service.Query(cellQueryQuery);
bool foundToken = false;
foreach (CellEntry curCell in cellQueryFeed.Entries)
{
if (foundToken)
{
return curCell.Cell.Value;
}
if (curCell.Cell.Column == 1)
{
if (curCell.Cell.Value.Equals(WhatsappProperties.Instance.AppToken))
{
foundToken = true;
}
}
}
}
catch (Exception e)
{
systemLog.Error(e);
}
return null;
}
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)
{
}
}
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 ActionResult MySpreadsheets()
{
SpreadsheetsService service = new SpreadsheetsService("chavp-mybook-1");
service.setUserCredentials("iuityuj@rtrtey5e6yrty", "sdfdsfsdfsdf");
WorksheetQuery query = new WorksheetQuery("0AsqxIqqTdYVidExTN0R0THh6XzlBa01pOURWVGRSMGc", "private", "full");
WorksheetFeed feed = service.Query(query);
List<Row> listData = new List<Row>();
foreach (WorksheetEntry worksheet in feed.Entries)
{
AtomLink cellFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, AtomLink.ATOM_TYPE);
CellQuery cellQuery = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed cellFeed = service.Query(cellQuery);
foreach (CellEntry curCell in cellFeed.Entries)
{
Cell cell = new Cell { Row = curCell.Cell.Row, Index = curCell.Cell.Column, Data = curCell.Cell.Value };
var row = listData.Find(r => r.Index == curCell.Cell.Row);
if (row == null)
{
row = new Row { Index = curCell.Cell.Row };
listData.Add(row);
}
row.CellList.Add(cell);
}
}
return View();
}
//Fetch all worksheets for given Spreadsheet
public static ArrayList getWorksheetList(string userName, string passWord, string spreadSheetName)
{
ArrayList worksheetList = new ArrayList();
SpreadsheetsService service = new SpreadsheetsService(spreadSheetName + "Service");
//You could set it up from DB or config file also. This is not a reccomended way. Just an easy way to show fetching
service.setUserCredentials(userName, passWord);
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = service.Query(query);
foreach (SpreadsheetEntry entry in feed.Entries)
{
if (entry.Title.Text == spreadSheetName)
{
AtomLink link = entry.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery wquery = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed wfeed = service.Query(wquery);
foreach (WorksheetEntry worksheet in wfeed.Entries)
{
worksheetList.Add(worksheet.Title.Text);
}
}
}
return worksheetList;
}
