private void SendData_ClearData(DTOAPIData item)
{
if (!string.IsNullOrEmpty(item.FileName) && !string.IsNullOrEmpty(item.SpreadsheetID) && !string.IsNullOrEmpty(item.SpreadsheetName))
{
string[] Scopes = { SheetsService.Scope.Spreadsheets }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new System.IO.FileStream(HttpContext.Current.Server.MapPath(FileClientId), System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
string credPath = HttpContext.Current.Server.MapPath(FileToken);
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
System.Threading.CancellationToken.None,
new FileDataStore(credPath, true)).Result;
//Console.WriteLine("Credential file saved to: " + credPath);
}
if (credential != null)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "Google Sheets API",
});
//SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
//{
// HttpClientInitializer = credential,
// ApplicationName = "Google-SheetsSample/0.1",
//});
string range = item.SpreadsheetName + "!A1:BA" + RowMax; // update cell F5
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, item.SpreadsheetID, range);
var deleteReponse = deleteRequest.Execute();
}
else
{
throw new Exception("Credential file fail");
}
}
}
public void SendPushByData(dynamic dynParam)
{
try
{
string strParam = dynParam != null?dynParam.ToString() : string.Empty;
if (!string.IsNullOrEmpty(strParam))
{
var dto = Newtonsoft.Json.JsonConvert.DeserializeObject <DTOAPIPush>(strParam);
string filePushPath = HttpContext.Current.Server.MapPath(FilePushData);
string fileDataPath = HttpContext.Current.Server.MapPath(FileData);
if (!System.IO.File.Exists(filePushPath))
{
System.IO.File.Create(filePushPath);
}
if (!System.IO.File.Exists(fileDataPath))
{
System.IO.File.Create(fileDataPath);
}
if (System.IO.File.Exists(filePushPath) && System.IO.File.Exists(fileDataPath))
{
List <DTOAPIData> lstData = new List <DTOAPIData>();
List <DTOAPIPush> lstPush = new List <DTOAPIPush>();
string str = string.Empty;
str = System.IO.File.ReadAllText(fileDataPath);
if (!string.IsNullOrEmpty(str))
{
try
{
lstData = Newtonsoft.Json.JsonConvert.DeserializeObject <List <DTOAPIData> >(str);
}
catch
{
lstData = new List <DTOAPIData>();
}
}
DTOAPIData itemData = lstData.Where(c => c.FileName == dto.FileName && c.CreatedDate != null).OrderByDescending(c => c.CreatedDate.Value).FirstOrDefault();
if (itemData != null)
{
str = System.IO.File.ReadAllText(filePushPath);
if (!string.IsNullOrEmpty(str))
{
try
{
lstPush = Newtonsoft.Json.JsonConvert.DeserializeObject <List <DTOAPIPush> >(str);
}
catch
{
lstPush = new List <DTOAPIPush>();
}
}
List <int> lstRowID = itemData.ListCells.Select(c => c.Row).Distinct().ToList();
List <string> lstRowTitle = new List <string>();
Dictionary <int, List <string> > dicRowValue = new Dictionary <int, List <string> >();
foreach (var rowid in lstRowID)
{
lstRowTitle.Add(itemData.ListRowTitle[rowid]);
List <string> lstCell = new List <string>();
foreach (var cell in itemData.ListCells.Where(c => c.Row == rowid))
{
string title = itemData.ListColumnTitle[cell.Column];
lstCell.Add(string.Format("[{0}: {1}->{2}]", title, cell.ValueFrom, cell.ValueTo));
}
dicRowValue.Add(rowid, lstCell);
}
dto.Title = DateTime.Now.ToString("HH:mm") + " " + string.Join(",", lstRowTitle);
dto.Body = string.Empty;
foreach (var itemRow in dicRowValue)
{
string title = itemData.ListRowTitle[itemRow.Key];
dto.Body += "," + title + string.Join("", itemRow.Value);
}
if (!string.IsNullOrEmpty(dto.Body))
{
dto.Body = dto.Body.Substring(1);
}
lstPush.Add(dto);
str = Newtonsoft.Json.JsonConvert.SerializeObject(lstPush);
System.IO.File.WriteAllText(filePushPath, str, System.Text.Encoding.UTF8);
using (var client = new HttpClient())
{
Uri url = new Uri("https://fcm.googleapis.com/fcm/send");
client.BaseAddress = new Uri(url.Scheme + "://" + url.Authority);
client.DefaultRequestHeaders.Accept.Clear();
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
client.DefaultRequestHeaders.TryAddWithoutValidation("Authorization", "key=AAAA5NRCJyY:APA91bHBfzDh6iupDq7CvIw9iaNSstO20flnBWzqwWCbMq_EvHt_fG5gIp4pUfmePz1KvuHMBGFuuYNXxYjrCcnvPWVi_pQ39h5IZuQWN8ikZulEgDgB3WCHuXwW7aQXTUdtiY3ZKXNH");
client.Timeout = TimeSpan.FromHours(0.1);
dynamic dynItem = new { to = "/topics/" + dto.FileName, notification = new { title = dto.Title, body = dto.Body, click_action = "" } };
var content = new StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(dynItem), Encoding.UTF8, "application/json");
var response = client.PostAsync(url.AbsolutePath, content);
if (response != null && response.Result.IsSuccessStatusCode)
{
HttpResponseMessage res = response.Result;
}
}
}
else
{
throw new Exception("not found filename in data change");
}
}
else
{
throw new Exception("not found data and push path");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
private void SendData_CreateData(DTOAPIData item)
{
if (!string.IsNullOrEmpty(item.FileUpload) && !string.IsNullOrEmpty(item.SpreadsheetID) && !string.IsNullOrEmpty(item.SpreadsheetName))
{
string fileupload = HttpContext.Current.Server.MapPath("/Uploads/" + item.FileUpload);
if (System.IO.File.Exists(fileupload))
{
ValueRange valueRange = new ValueRange();
valueRange.MajorDimension = "ROWS";//"ROWS";//COLUMNS
valueRange.Values = new List <IList <object> >();
if (System.IO.Path.GetExtension(fileupload).ToLower() == ".csv")
{
#region csv
using (var textReader = new System.IO.StreamReader(fileupload))
{
int row, col;
var csv = new CsvHelper.CsvReader(textReader);
row = 0;
while (csv.Read())
{
if (row <= RowMax)
{
var objrow = new List <object>();
for (col = 0; col < ColMax; col++)
{
try
{
objrow.Add(csv[col]);
}
catch
{
break;
}
}
valueRange.Values.Add(objrow);
}
else
{
break;
}
row++;
}
}
#endregion
}
else
{
#region excel
using (var package = new OfficeOpenXml.ExcelPackage(new System.IO.FileInfo(fileupload)))
{
var worksheet = HelperExcel.GetWorksheetFirst(package);
#region current
if (worksheet != null && worksheet.Dimension != null)
{
int row, col;
for (row = 1; row <= worksheet.Dimension.End.Row && row < RowMax; row++)
{
var objrow = new List <object>();
for (col = 1; col <= worksheet.Dimension.End.Column && col < ColMax; col++)
{
var str = HelperExcel.GetValue(worksheet, row, col);
objrow.Add(str);
}
valueRange.Values.Add(objrow);
}
}
#endregion
}
#endregion
}
string[] Scopes = { SheetsService.Scope.Spreadsheets }; //delete token folder to refresh scope
UserCredential credential;
using (var stream =
new System.IO.FileStream(HttpContext.Current.Server.MapPath(FileClientId), System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
string credPath = HttpContext.Current.Server.MapPath(FileToken);
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
System.Threading.CancellationToken.None,
new FileDataStore(credPath, true)).Result;
//Console.WriteLine("Credential file saved to: " + credPath);
}
if (credential != null)
{
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "Google Sheets API",
});
string range = item.SpreadsheetName + "!A1";
SpreadsheetsResource.ValuesResource.UpdateRequest update = service.Spreadsheets.Values.Update(valueRange, item.SpreadsheetID, range);
update.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.RAW;
var result = update.Execute();
}
else
{
throw new Exception("Credential file fail");
}
}
}
}
