public void CalculateStartEndDateTimeVin()
{
if (ExcelDataTable.Rows.Count <= 0)
{
return;
}
var temp = (from row in ExcelDataTable.AsEnumerable()
where DateTime.Compare(ParseDateTimeValue(row.Field <string>("DateTime")), ParseDateTimeValue(DateTimePicker)) <= 0
select new
{
vin = row.Field <string>("Vin"),
datetime = ParseDateTimeValue(row.Field <string>("DateTime"))
}).Take(int.Parse(Sampling));
//TEST
foreach (var item in temp)
{
Console.WriteLine(item.vin + " " + item.datetime);
}
var start = temp.OrderByDescending(x => x.datetime).Last();
var end = temp.OrderByDescending(x => x.datetime).First();
StartDateTime = start.datetime.ToString();
StartVin = start.vin;
EndDateTime = end.datetime.ToString();
EndVin = end.vin;
}
public string ProcessMessage(string batchName)
{
if (Operation.FailerReason.Contains('='))
{
string dyData = string.Empty;
WriteAndReadData.DataFilePath = ConfigurationManager.AppSettings["ExcelDataFile"] + "\\DynamicData" + "\\" + batchName + ".xls";
ExcelDataTable.PopulateRecordData(WriteAndReadData.DataFilePath);
dyData = pop.getDataFromDynamicExcel(Operation.FailerReason.Split('+')[1].Trim());
return(Operation.FailerReason.Split('+')[0].Replace("[X]", dyData));
}
else
{
return(Operation.FailerReason);
}
}
public static ExcelDataSet ReadExcel(string excelPath)
{
ExcelDataSet set = new ExcelDataSet();
if (!File.Exists(excelPath))
{
throw new Exception("path not exists");
}
try
{
FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
do
{
ExcelDataTable table = new ExcelDataTable();
// sheet name
while (excelReader.Read())
{
ExcelDataRow row = new ExcelDataRow();
for (int i = 0; i < excelReader.FieldCount; i++)
{
string value = excelReader.IsDBNull(i) ? "" : excelReader.GetString(i);
row.Cells.Add(value);
}
table.Rows.Add(row);
}
if (table.Rows.Count > 0)
{
set.Tables.Add(table);
}
} while (excelReader.NextResult());
excelReader.Dispose();
stream.Dispose();
}
catch (Exception e)
{
DebugLogWrapper.LogError(e);
}
return(set);
}
public void AutomationTestReportMail()
{
ExcelDataTable.PopulateInCollection(Init + "\\GlobalSettings.xlsx");
SendingReportConfig = ExcelDataTable.ReadData(1, "ReportSending");
int Recipients = Convert.ToInt32(ExcelDataTable.ReadData(1, "NumberOfRecipients"));
string result = string.Empty;
string to = string.Empty;
string Emailbody = ExcelDataTable.ReadData(1, "Body").ToString();
if (SendingReportConfig == "Yes")
{
try
{
var fromAddress = new MailAddress(ExcelDataTable.ReadData(1, "MailFrom").ToString(), ExcelDataTable.ReadData(1, "DispayName").ToString());
var fromPassword = ExcelDataTable.ReadData(1, "EmailPass").ToString();
var toAddress = new MailAddress("*****@*****.**");
string subject = ExcelDataTable.ReadData(1, "Subject").ToString();
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient
{
Host = "mail.exclusivehosting.net",
Port = 2525,
EnableSsl = true,
DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network,
UseDefaultCredentials = false,
Credentials = new NetworkCredential(fromAddress.Address, fromPassword)
};
ServicePointManager.ServerCertificateValidationCallback = delegate(object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{
return(true);
};
using (MailMessage mailMessage = new MailMessage())
{
mailMessage.From = fromAddress;
mailMessage.Subject = subject;
mailMessage.Body = Emailbody;
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(ReportFiles);
string[] filepaths = Directory.GetFiles(ReportFiles);
foreach (var file in filepaths)
{
var attachment = new Attachment(file);
mailMessage.Attachments.Add(attachment);
}
Attachment ReportFile;
RegistryKey key = Registry.CurrentUser.OpenSubKey(@"SOFTWARE\Testing");
string f = key.GetValue("AutomationReport").ToString();
ReportFile = new Attachment(f);
mailMessage.Attachments.Add(ReportFile);
mailMessage.IsBodyHtml = true;
for (int i = 1; i <= Recipients; i++)
{
try
{
to = ExcelDataTable.ReadData(i, "MailTo").ToString();
mailMessage.To.Add(new MailAddress(to));
}
catch (Exception e) { }
}
using (var message = new MailMessage(fromAddress, toAddress)
{
Subject = subject,
Body = Emailbody,
})
smtp.Send(mailMessage);
}
}
catch (Exception e) { }
}
else
{
Console.WriteLine("Sending Batch Report is disabled");
}
}
public byte[] CreateExcel(System.Data.DataTable data, string templateFullPath, ExcelDataTable excelColumns)
{
HttpContext context = HttpContext.Current;
string GlobalTempPath = ConfigurationManager.AppSettings["GlobalTempFolder"].ToString();
var filePath = GlobalTempPath + "tmp" + Guid.NewGuid() + "_upload.xls";
var fileStream = new FileStream(filePath, FileMode.Create);
var b = CommonFunctions.ReadFile(templateFullPath);
fileStream.Write(b, 0, b.Length);
fileStream.Dispose();
var sConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No;\";", filePath);
var objConn = new OleDbConnection(sConnectionString);
try
{
var dt = data;
objConn.Open();
var i = 2;
foreach (DataRow row in dt.Rows)
{
//var cmd = new OleDbCommand(string.Format("INSERT INTO [Sheet1$A{0}:A{0}] values ('aaa')", i), objConn);
////cmd.Parameters.AddWithValue("@barcode", row["barcode"]);
////cmd.Parameters.AddWithValue("@goods_name", row["goods_name"]);
//cmd.ExecuteNonQuery();
//cmd.Dispose();
//var cmd1 = new OleDbCommand(string.Format("INSERT INTO [Sheet1$E{0}:F{0}] values (@unit_txt,@other_unit_txt)", i), objConn);
//cmd1.Parameters.AddWithValue("@unit_txt", row["unit_txt"]);
//cmd1.Parameters.AddWithValue("@other_unit_txt", row["other_unit_txt"]);
//cmd1.ExecuteNonQuery();
//cmd1.Dispose();
if (string.IsNullOrEmpty(excelColumns.Sheet))
{
throw new Exception();
}
foreach (var a in excelColumns.ColumnsWithFieldNames)
{
var cmd = new OleDbCommand(string.Format("INSERT INTO [" + excelColumns.Sheet.ToString() + "$" + a.Key.ToString() + "{0}:" + a.Key.ToString() + "{0}] values (@" + a.Value + ")", i), objConn);
cmd.Parameters.AddWithValue("@" + a.Value, row[a.Value]);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
i++;
}
}
catch (Exception ex)
{
throw new UserExceptions("ფაილის გადმოწერა ვერ მოხერხდა");
}
finally
{
objConn.Close();
objConn.Dispose();
}
var FileBytes = CommonFunctions.ReadFile(filePath);
try
{
File.Delete(filePath);
}
catch
{
}
return(FileBytes);
}
public static void ResourceMessages(string datareferenceKeyword)
{
string[] KeyMessages;
if (datareferenceKeyword.Contains('|'))
{
KeyMessages = datareferenceKeyword.Split('|');
}
else
{
KeyMessages = datareferenceKeyword.Split(',');
}
resMsg_GetTemplateMessage = string.Empty;
string resMsg_GetTemplateMessageStore = string.Empty;
for (int i = 0; i < KeyMessages.Count(); i++)
{
string[] getResource = KeyMessages[i].Split('+');
if (getResource.Count() > 1)
{
string resMsgTemplateMessage = ExcelLanguageTemplateKeyword.ReadKeywordMessage(getResource[0].Split(':')[1]);
for (int j = 1; j <= getResource.Count() - 1; j++)
{
if (j == 1)
{
if (getResource[j].StartsWith("Rec_"))
{
resMsg_GetTemplateMessageStore = resMsgTemplateMessage.Replace("[X" + j + "]", Operation.recordedData[getResource[j].Replace("Rec_", string.Empty)]).ToString();
}
else if (getResource[j].StartsWith("Order"))
{
resMsg_GetTemplateMessageStore = resMsgTemplateMessage.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else if (getResource[j].StartsWith("Material"))
{
resMsg_GetTemplateMessageStore = resMsgTemplateMessage.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else if (getResource[j].StartsWith("Reference"))
{
resMsg_GetTemplateMessageStore = resMsgTemplateMessage.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else
{
resMsg_GetTemplateMessageStore = resMsgTemplateMessage.Replace("[X" + j + "]", ExcelDataTable.ReadData(1, getResource[j])).ToString();
}
}
else
{
if (getResource[j].StartsWith("Rec_"))
{
resMsg_GetTemplateMessageStore = resMsg_GetTemplateMessageStore.Replace("[X" + j + "]", Operation.recordedData[getResource[j].Replace("Rec_", string.Empty)]).ToString();
}
else if (getResource[j].StartsWith("Order"))
{
resMsg_GetTemplateMessageStore = resMsg_GetTemplateMessageStore.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else if (getResource[j].StartsWith("Material"))
{
resMsg_GetTemplateMessageStore = resMsg_GetTemplateMessageStore.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else if (getResource[j].StartsWith("Reference"))
{
resMsg_GetTemplateMessageStore = resMsg_GetTemplateMessageStore.Replace("[X" + j + "]", pops.getDataFromDynamicExcel(getResource[j]));
}
else
{
resMsg_GetTemplateMessageStore = resMsg_GetTemplateMessageStore.Replace("[X" + j + "]", ExcelDataTable.ReadData(1, getResource[j])).ToString();
}
}
}
if (datareferenceKeyword.Contains('|'))
{
resMsg_GetTemplateMessage = resMsg_GetTemplateMessage + resMsg_GetTemplateMessageStore + '|';
}
else
{
resMsg_GetTemplateMessage = resMsg_GetTemplateMessage + resMsg_GetTemplateMessageStore;
}
}
else
{
string[] data = KeyMessages[i].Split(':');
if (data.Count() > 1)
{
resMsg_GetTemplateMessage = resMsg_GetTemplateMessage + ExcelLanguageTemplateKeyword.ReadKeywordMessage(data[1]);
}
else
{
resMsg_GetTemplateMessage = ExcelDataTable.ReadData(1, data[i].ToString());
}
}
}
}
public void AddDataForReference(string _Filepath, string _columhead, string _writeData, string referencedata)
{
try
{
DataTable dtReport = new DataTable();
dtReport.Clear();
dtReport = ExcelKeywordTable.ExcelData(_Filepath, "sheet1");
int columeindex = dtReport.Columns.Count;
int rowindex = dtReport.Rows.Count;
int referenceDataCount = 0;
string material = string.Empty;
var Wbook = Workbook.Load(DataFilePath);
var Wsheet = Wbook.Worksheets[0];
#region RR
if (referencedata.Contains(','))
{
string[] MatchData = referencedata.Split(',');
if (MatchData.Count() > 1)
{
if (MatchData[0].StartsWith("Rec_"))
{
material = Operation.recordedData[MatchData[0].Replace("Rec_", string.Empty)].ToString();
}
else if (MatchData[0].StartsWith("Order"))
{
material = ExcelDataTable.ReadRefferenceInfoByOrder(MatchData[0].Split('=')[1], "Material");
}
else
{
material = ExcelDataTable.ReadData(1, MatchData[0]);
}
if (MatchData[1].Contains('+'))
{
string Store = ExcelDataTable.ReadData(1, MatchData[1].Split('+')[0]);
referencedata = material + ':' + ExcelDataTable.ReadRefferenceInfoByMaterialWithStore(Store, material, MatchData[1].Split('=')[1]);
}
else if (MatchData[1].StartsWith("Store"))
{
referencedata = ExcelDataTable.ReadData(1, MatchData[1].Split('=')[0]) + ':' + material;
}
getRefferenceIndexData(rowindex, columeindex, referencedata);
}
else
{
referencedata = ExcelDataTable.ReadData(1, MatchData[0]);
}
}
#endregion
getColumeIndexData(columeindex, _columhead);
if (writecolumeIndex == 0)
{
Wsheet.Cells[0, columeindex] = new Cell(_columhead);
Wbook.Save(_Filepath);
getColumeIndexData(columeindex, _columhead);
getRefferenceIndexData(rowindex, columeindex, referencedata);
if (writeRowIndex.Contains(','))
{
string[] rowcount = writeRowIndex.Split(',');
foreach (var row in rowcount)
{
if (row != string.Empty)
{
Wsheet.Cells[Convert.ToInt16(row), columeindex] = new Cell(_writeData);
}
}
}
else
{
Wsheet.Cells[Convert.ToInt16(writeRowIndex), columeindex] = new Cell(_writeData);
}
}
else
{
getRefferenceIndexData(rowindex, columeindex, referencedata);
if (referencedata.Contains(':'))
{
if (writeRowIndex.Contains(','))
{
string[] rowcount = writeRowIndex.Split(',');
foreach (var row in rowcount)
{
if (row != string.Empty)
{
Wsheet.Cells[Convert.ToInt16(row), writecolumeIndex] = new Cell(_writeData);
}
}
}
}
else
{
for (int a = 1; a <= rowindex; a++)
{
if (Wsheet.Cells[a, 0].StringValue == referencedata)
{
referenceDataCount++;
}
}
if (referenceDataCount > 1)
{
Wsheet.Cells[Convert.ToInt16(writeRowIndex) + 1, writecolumeIndex] = new Cell(_writeData);
}
else
{
Wsheet.Cells[Convert.ToInt16(writeRowIndex), writecolumeIndex] = new Cell(_writeData);
}
}
}
Wsheet.Cells.ColumnWidth[0, (ushort)columeindex] = 5000;
Wbook.Save(_Filepath);
}
catch (Exception e)
{
throw new Exception("Error while writing data for the reference data.");
}
}
