/// <summary>
/// Imports excel data and saves to database
/// </summary>
/// <param name="UploadFile"></param>
/// <param name="model"></param>
public void ImportExcel(HttpPostedFileBase UploadFile, SubscribersViewModel model)
{
DataSet excelResult = new DataSet();
IExcelDataReader excelDataReader;
subscriber = new M_Subscriber();
if (UploadFile != null && UploadFile.ContentLength > 0)
{
using (dbcontext = new ApplicationDbContext())
{
Stream stream = UploadFile.InputStream;
string filename = System.IO.Path.GetFileNameWithoutExtension(UploadFile.FileName);
if (UploadFile.FileName.EndsWith(".xlsx"))
{
string thisFile = filename + "_" + model.ListID + ".xlsx";
// var path = System.IO.Path.Combine(Server.MapPath("~/ExcelFiles"), thisFile);
excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelDataReader.IsFirstRowAsColumnNames = true;
excelResult = excelDataReader.AsDataSet();
model.dataTable = excelResult.Tables[0];
//read column headers
// var columnHeaders = (from DataColumn dc in model.dataTable.Columns select dc.ColumnName).ToArray();
// RedirectToAction("SetColumnHeader", columnHeaders);
//save list to database
if (model.dataTable.Rows.Count > 0)
{
// UploadFile.SaveAs(path);
//ColumnList = ReadExcelHeader(path);
for (int i = 0; i < model.dataTable.Rows.Count; i++)
{
List <string> sub = new List <string>();
sub = dbcontext.M_Subscribers.Where(l => l.ListID == model.ListID).Select(m => m.EmailAddress).ToList();
bool ispresent = false;
try
{
ispresent = sub.Any(s => s == model.dataTable.Rows[i]["EmailAddress"].ToString());
}
catch (ArgumentException ex)
{
// ModelState.AddModelError("Fileerr", "Please see sample file format");
// return View();
}
catch (Exception ex)
{
obj = new M_CustomException((int)ErorrTypes.others, ex.Message, ex.StackTrace, ErorrTypes.others.ToString(), Utlities.GetURL());
obj.LogException();
throw obj;
}
if (ispresent == false)
{
ListSusbscriber lSub = new ListSusbscriber();
lSub.ListID = model.ListID;
using (var trans = dbcontext.Database.BeginTransaction())
{
//ObjectParameter objParam = new ObjectParameter("ID", typeof(int));
try
{
subscriber.ListID = Convert.ToInt32(model.ListID);
subscriber.FirstName = model.dataTable.Rows[i]["FirstName"].ToString();
subscriber.LastName = model.dataTable.Rows[i]["LastName"].ToString();
subscriber.EmailAddress = model.dataTable.Rows[i]["EmailAddress"].ToString();
subscriber.AlternateEmailAddress = model.dataTable.Rows[i]["AlternateEmailAddress"].ToString();
subscriber.Address = model.dataTable.Rows[i]["Address"].ToString();
subscriber.Country = model.dataTable.Rows[i]["Country"].ToString();
subscriber.City = model.dataTable.Rows[i]["City"].ToString();
subscriber.AddedDate = DateTime.Now;
//subscriber.StatusID = 3;
dbcontext.M_Subscribers.Add(subscriber);
dbcontext.SaveChanges();
//dbcontext.ImportSubscribers(Convert.ToInt32(model.ListID), model.dataTable.Rows[i]["FirstName"].ToString(),
// model.dataTable.Rows[i]["LastName"].ToString(), model.dataTable.Rows[i]["EmailAddress"].ToString(), model.dataTable.Rows[i]["AlternateEmailAddress"].ToString(),
// model.dataTable.Rows[i]["Address"].ToString(), model.dataTable.Rows[i]["Country"].ToString(), model.dataTable.Rows[i]["City"].ToString(),
// DateTime.Now.ToString(), objParam);
//lSub.SubscribersID = Convert.ToInt32(objParam.Value);
// lSub.SubscribersID = (int?)((SqlParameter)param[9]).Value;
lSub.SubscribersID = subscriber.SubscriberID;
dbcontext.ListSusbscribers.Add(lSub);
dbcontext.SaveChanges();
trans.Commit();
}
catch (ArgumentException ex)
{
// ModelState.AddModelError("Fileerr", "Please see sample file format");
// return View();
}
catch (Exception ex)
{
trans.Rollback();
obj = new M_CustomException((int)ErorrTypes.others, ex.Message, ex.StackTrace, ErorrTypes.others.ToString(), Utlities.GetURL());
obj.LogException();
throw obj;
}
}
}
else
{
// ModelState.AddModelError("present", "Some subscribers already present");
}
}
}
// return RedirectToAction("ViewSubscribers/" + model.ListID);
}
else if (UploadFile.FileName.EndsWith(".xls"))
{
string thisFile = filename + "_" + model.ListID + ".xls";
// var path = System.IO.Path.Combine(Server.MapPath("~/ExcelFiles"), thisFile);
excelDataReader = ExcelReaderFactory.CreateBinaryReader(stream);
excelDataReader.IsFirstRowAsColumnNames = true;
excelResult = excelDataReader.AsDataSet();
model.dataTable = excelResult.Tables[0];
//read column headers
var columnHeaders = (from DataColumn dc in model.dataTable.Columns select dc.ColumnName).ToArray();
if (model.dataTable.Rows.Count > 0)
{
//UploadFile.SaveAs(path);
for (int i = 0; i < model.dataTable.Rows.Count; i++)
{
List <string> sub = new List <string>();
sub = dbcontext.M_Subscribers.Where(l => l.ListID == model.ListID).Select(m => m.EmailAddress).ToList();
bool ispresent = false;
try
{
ispresent = sub.Any(s => s == model.dataTable.Rows[i]["EmailAddress"].ToString());
}
catch (ArgumentException ex)
{
//ModelState.AddModelError("Fileerr", "Please see sample file format");
// return View();
}
if (ispresent == false)
{
ListSusbscriber lSub = new ListSusbscriber();
lSub.ListID = model.ListID;
using (var trans = dbcontext.Database.BeginTransaction())
{
// ObjectParameter objParam = new ObjectParameter("ID", typeof(int));
try
{
subscriber.ListID = Convert.ToInt32(model.ListID);
subscriber.FirstName = model.dataTable.Rows[i]["FirstName"].ToString();
subscriber.LastName = model.dataTable.Rows[i]["LastName"].ToString();
subscriber.EmailAddress = model.dataTable.Rows[i]["EmailAddress"].ToString();
subscriber.AlternateEmailAddress = model.dataTable.Rows[i]["AlternateEmailAddress"].ToString();
subscriber.Address = model.dataTable.Rows[i]["Address"].ToString();
subscriber.Country = model.dataTable.Rows[i]["Country"].ToString();
subscriber.City = model.dataTable.Rows[i]["City"].ToString();
subscriber.AddedDate = DateTime.Now;
// subscriber.StatusID = 3;
dbcontext.M_Subscribers.Add(subscriber);
dbcontext.SaveChanges();
//dbcontext.ImportSubscribers(Convert.ToInt32(model.ListID), model.dataTable.Rows[i]["FirstName"].ToString(),
// model.dataTable.Rows[i]["LastName"].ToString(), model.dataTable.Rows[i]["EmailAddress"].ToString(), model.dataTable.Rows[i]["AlternateEmailAddress"].ToString(),
// model.dataTable.Rows[i]["Address"].ToString(), model.dataTable.Rows[i]["Country"].ToString(), model.dataTable.Rows[i]["City"].ToString(),
// DateTime.Now.ToString(), objParam);
//lSub.SubscribersID = Convert.ToInt32(objParam.Value);
// lSub.SubscribersID = (int?)((SqlParameter)param[9]).Value;
lSub.SubscribersID = subscriber.SubscriberID;
dbcontext.ListSusbscribers.Add(lSub);
dbcontext.SaveChanges();
trans.Commit();
}
catch (ArgumentException ex)
{
// ModelState.AddModelError("Fileerr", "Please see sample file format");
// return View();
}
catch (SqlException)
{
trans.Rollback();
// ModelState.AddModelError("Fileerr", "Please see sample file format");
}
catch (Exception ex)
{
obj = new M_CustomException((int)ErorrTypes.others, ex.Message, ex.StackTrace, ErorrTypes.others.ToString(), Utlities.GetURL());
obj.LogException();
throw obj;
}
}
}
else
{
// ModelState.AddModelError("present", "Some subscribers already present");
}
}
}
// return RedirectToAction("ViewSubscribers/" + model.ListID);
}
}
}
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
#region Variables
ct_anio_fiscal_List ListaAnioFiscal = new ct_anio_fiscal_List();
ct_plancta_List ListaPlancta = new ct_plancta_List();
List <ct_plancta_Info> ListaPlan = new List <ct_plancta_Info>();
List <ct_anio_fiscal_Info> ListaAnio = new List <ct_anio_fiscal_Info>();
int cont = 0;
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa);
#endregion
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
#region Plan de cuentas
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
//var IdCtaCble = Convert.ToString(reader.GetValue(0));
//var pc_clave_corta = reader.GetValue(1) == null || string.IsNullOrEmpty(reader.GetString(1)) ? "" : reader.GetString(1);
//var pc_Cuenta = reader.GetString(2);
//var IdCtaCIdCtaCblePadreble = reader.GetValue(3) == null || string.IsNullOrEmpty(Convert.ToString(reader.GetValue(3))) ? null : Convert.ToString(reader.GetValue(3));
//var pc_Naturaleza = Convert.ToString(reader.GetValue(4));
//var IdNivelCta = Convert.ToInt32(reader.GetValue(5));
//var pc_EsMovimiento_bool = Convert.ToString(reader.GetValue(6)) == "SI" ? true : false;
//var pc_EsMovimiento = Convert.ToString(reader.GetValue(6)) == "SI" ? "S" : "N";
//var IdGrupoCble = Convert.ToString(reader.GetValue(7));
ct_plancta_Info info = new ct_plancta_Info
{
IdEmpresa = IdEmpresa,
IdCtaCble = Convert.ToString(reader.GetValue(0)),
pc_clave_corta = reader.GetValue(1) == null || string.IsNullOrEmpty(reader.GetString(1)) ? "" : reader.GetString(1),
pc_Cuenta = reader.GetString(2),
IdCtaCblePadre = reader.GetValue(3) == null || string.IsNullOrEmpty(Convert.ToString(reader.GetValue(3))) ? null : Convert.ToString(reader.GetValue(3)),
pc_Naturaleza = Convert.ToString(reader.GetValue(4)),
IdNivelCta = Convert.ToInt32(reader.GetValue(5)),
pc_EsMovimiento_bool = Convert.ToString(reader.GetValue(6)) == "SI" ? true : false,
pc_EsMovimiento = Convert.ToString(reader.GetValue(6)) == "SI" ? "S" : "N",
IdGrupoCble = Convert.ToString(reader.GetValue(7))
};
ListaPlan.Add(info);
}
else
{
cont++;
}
}
#endregion
cont = 0;
//Para avanzar a la siguiente hoja de excel
reader.NextResult();
#region Cuentas contables por anio
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
int Anio = Convert.ToInt32(reader.GetValue(0));
string IdCtaCble = reader.GetValue(1).ToString();
ct_anio_fiscal_Info info = new ct_anio_fiscal_Info
{
IdanioFiscal = Anio,
af_fechaIni = new DateTime(Anio, 1, 1),
af_fechaFin = new DateTime(Anio, 12, 31),
info_anio_ctautil = new ct_anio_fiscal_x_cuenta_utilidad_Info
{
IdEmpresa = IdEmpresa,
IdCtaCble = IdCtaCble,
IdanioFiscal = Anio,
},
};
ListaAnio.Add(info);
}
else
{
cont++;
}
}
#endregion
ListaPlancta.set_list(ListaPlan, IdTransaccionSession);
ListaAnioFiscal.set_list(ListaAnio, IdTransaccionSession);
}
}
private static List <Product> GetProductWithDescription(string pathToFile)
{
List <Product> result = new List <Product>();
using (FileStream stream = File.OpenRead(pathToFile))
using (IExcelDataReader dr = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
int nameColumn = -1;
int descriptionColumn = -1;
int priceColumn = -1;
int spicyColumn = -1;
int vegetarianColumn = -1;
int addChargeColumn = -1;
DataSet data = dr.AsDataSet();
var table = data.Tables[0];
for (int rowCount = 0; rowCount < table.Rows.Count; rowCount++)
{
if (rowCount == 0)
{
for (int columnCount = 0; columnCount < table.Rows[rowCount].ItemArray.Length; columnCount++)
{
string columnHeader = table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower();
if (columnHeader.ToLower().Contains("productnaam"))
{
nameColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("productomschrijving"))
{
descriptionColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("prijs"))
{
priceColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("spicy"))
{
spicyColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("vegetarisch"))
{
vegetarianColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("bezorgtoeslag"))
{
addChargeColumn = columnCount;
}
}
}
else
{
Product foundProduct = new Product
{
Name = nameColumn == -1 ? "" : table.Rows[rowCount].ItemArray[nameColumn].ToString().ToLower(),
Description = descriptionColumn == -1 ? "" : table.Rows[rowCount].ItemArray[descriptionColumn].ToString().ToLower(),
Price = priceColumn == -1 ? "0" : table.Rows[rowCount].ItemArray[priceColumn].ToString().ToLower(),
Spicy = spicyColumn == -1 ? false : table.Rows[rowCount].ItemArray[spicyColumn].ToString().ToLower() == "nee" ? false : true,
Vegetarisch = vegetarianColumn == -1 ? false : table.Rows[rowCount].ItemArray[vegetarianColumn].ToString().ToLower() == "nee" ? false : true,
AdditionalCharge = addChargeColumn == -(1) ? "0" : table.Rows[rowCount].ItemArray[addChargeColumn].ToString().ToLower(),
};
result.Add(foundProduct);
}
}
}
return(result.DistinctBy(p => p.Name).ToList());
}
/// <summary>
/// 根据命令行参数，执行Excel数据导出工作
/// </summary>
/// <param name="options">命令行参数</param>
private static void Run(Options options)
{
string excelPath = options.ExcelPath;
int header = options.HeaderRows;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel file is empty: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet is empty: " + excelPath);
}
//-- 确定编码
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.EncodingName == options.Encoding)
{
cd = e;
break;
}
}
}
//-- 导出JSON文件
if (options.JsonPath != null && options.JsonPath.Length > 0)
{
JsonExporter exporter = new JsonExporter(sheet, header, options.Lowcase);
exporter.SaveToFile(options.JsonPath, cd, options.ExportArray);
}
//-- 导出SQL文件
if (options.SQLPath != null && options.SQLPath.Length > 0)
{
SQLExporter exporter = new SQLExporter(sheet, header);
exporter.SaveToFile(options.SQLPath, cd);
}
//-- 生成C#定义文件
if (options.CSharpPath != null && options.CSharpPath.Length > 0)
{
string excelName = Path.GetFileName(excelPath);
CSDefineGenerator exporter = new CSDefineGenerator(sheet);
exporter.ClassComment = string.Format("// Generate From {0}", excelName);
exporter.SaveToFile(options.CSharpPath, cd);
}
}
}
/// <summary>
/// Read excel
/// </summary>
/// <param name="FilePath"> file path</param>
/// <returns></returns>
public List <string> ReadExcel(string FilePath)
{
// Return list
List <string> Result = new List <string>();
DataSet ds;
if (File.Exists(FilePath))
{
var extension = Path.GetExtension(FilePath).ToLower();
Console.WriteLine("Reading file：" + FilePath);
using (var stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
// Determine file format
if (extension == ".xls")
{
Console.WriteLine(" => XLS Format");
reader = ExcelReaderFactory.CreateBinaryReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding("big5")
});
}
else if (extension == ".xlsx")
{
Console.WriteLine(" => XLSX Format");
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (extension == ".csv")
{
Console.WriteLine(" => CSV Format");
reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding("big5")
});
}
else if (extension == ".txt")
{
Console.WriteLine(" => Text(Tab Separated) Format");
reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.GetEncoding("big5"),
AutodetectSeparators = new char[] { '\t' }
});
}
// No match file format
if (reader == null)
{
Console.WriteLine("Unkown file format：" + extension);
return(null);
}
Console.WriteLine(" => File reading...");
using (reader)
{
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
// Setting ignore header ?
UseHeaderRow = false
}
});
// Show dataset
var table = ds.Tables[0];
for (int row = 0; row < table.Rows.Count; row++)
{
string data = "";
for (var col = 0; col < table.Columns.Count; col++)
{
data += table.Rows[row][col].ToString().Trim() + "_";
}
Result.Add(data + "_" + $"{row + 1}");
}
}
}
Console.WriteLine("End");
return(Result);
}
else
{
Console.WriteLine("File " + FilePath + " not exists!");
}
return(Result);
}
public JsonResult OgrenciListeYukle(HttpPostedFileBase excelFile)
{
JsonResultModel jmodel = new JsonResultModel();
//try
//{
if (excelFile == null || excelFile.ContentLength == 0)
{
jmodel.IsSuccess = false;
jmodel.UserMessage = "Lütfen dosya seçimi yapınız.";
}
else
{
//Dosyanın uzantısı xls ya da xlsx ise;
if (excelFile.FileName.EndsWith("xls") || excelFile.FileName.EndsWith("xlsx"))
{
string dosyaadi = DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "-" + Session["OkulID"].ToString() + "-" + excelFile.FileName;
//Seçilen dosyanın nereye yükleneceği seçiliyor.
string path = string.Concat(Server.MapPath("~/UploadExcel/" + dosyaadi));
//Dosya kontrol edilir, varsa silinir.
//if (System.IO.File.Exists(path))
//{
// System.IO.File.Delete(path);
//}
//Excel path altına kaydedilir.
excelFile.SaveAs(path);
FileStream stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
//Gönderdiğim dosya xls'mi xlsx formatında mı? kontrol ediliyor.
if (Path.GetExtension(path).ToUpper() == ".XLS")
{
//Binary üzerinden excel okuması yapılıyor (Excel 97-2003 *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//Openxml üzerinden excel okuması yapılıyor (Excel 2007 *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
Donemler donem = donemrepo.GetByFilter(a => a.AktifMi == true);
List <Ogrenciler> localList = new List <Ogrenciler>();
int counter = 0;
while (excelReader.Read())
{
counter++;
if (counter > 1)
{
Ogrenciler lm = new Ogrenciler();
lm.OgrenciNo = Convert.ToString(excelReader.GetDouble(0));
lm.Adi = excelReader.GetString(1).ToString();
lm.Soyadi = excelReader.GetString(2).ToString();
lm.Sinif = Convert.ToInt32(excelReader.GetDouble(3).ToString());
lm.Sube = excelReader.GetString(4).ToString();
lm.Sube = lm.Sube.ToUpper();
lm.OkulID = Convert.ToInt32(Session["OkulID"]);
lm.KayitTarihi = DateTime.Now;
lm.DonemID = donem.DonemID;
lm.AktifMi = true;
localList.Add(lm);
}
}
//Okuma bitiriliyor.
excelReader.Close();
foreach (var item in localList)
{
ogrencirepo.Add(item);
}
Okullar okul = okulrepo.Get(Convert.ToInt32(Session["OkulID"]));
okul.OgrenciListeYuklediMi = true;
okulrepo.Update(okul);
jmodel.IsSuccess = true;
jmodel.UserMessage = "Öğrenci listesi veritabınına başarılı bir şekilde aktarıldı.";
}
else
{
jmodel.IsSuccess = false;
jmodel.UserMessage = "Dosya tipiniz yanlış, lütfen '.xls' yada '.xlsx' uzantılı dosya yükleyiniz.";
}
}
//}
//catch (Exception e)
//{
// jmodel.IsSuccess = false;
// //jmodel.UserMessage = "Yükleme işlemi sırasında bir hata oluştu. Lütfen daha sonra tekrar deneyiniz.";
// jmodel.UserMessage = e.ToString();
//}
return(Json(jmodel, JsonRequestBehavior.AllowGet));
}
private void DocFileExcel(string ipath)
{
IExcelDataReader FileExcel;
FileStream stream = File.Open(ipath, FileMode.Open, FileAccess.Read); //Đọc file vào
try
{
//1. Câu lệnh sau dùng cho Excel 2007 trở lên
FileExcel = ExcelReaderFactory.CreateOpenXmlReader(stream); //1.
}
catch
{
//2. Nếu bạn dùng Excel 2003 trở xuống vui lòng dùng câu lệnh 2. thay cho 1.
FileExcel = ExcelReaderFactory.CreateBinaryReader(stream); //2.
}
List <BangDiemHocSinh> listDiem = new List <BangDiemHocSinh>();
DataSet result = FileExcel.AsDataSet();
FileExcel.IsFirstRowAsColumnNames = true;
foreach (System.Data.DataTable table in result.Tables)
{
for (int i = START_POSITION_READ_FROM_EXCEL; i < table.Rows.Count; i++)
{
BangDiemHocSinh temp = new BangDiemHocSinh();
temp._MaHocSinh = Convert.ToInt32(table.Rows[i].ItemArray[0].ToString());
temp._Hoten = table.Rows[i].ItemArray[1].ToString();
temp._Diem15 = Convert.ToInt32(table.Rows[i].ItemArray[2].ToString());
temp._Diem1Tiet = Convert.ToInt32(table.Rows[i].ItemArray[3].ToString());
temp._DiemHK = Convert.ToInt32(table.Rows[i].ItemArray[4].ToString());
listDiem.Add(temp);
}
}
FileExcel.Close();
// update data to gridview
for (int i = 0; i < dataGridView.RowCount; i++)
{
for (int j = 0; j < listDiem.Count; j++)
{
if (dataGridView.Rows[i].Cells["MaHocSinh"].Value.Equals(listDiem[j]._MaHocSinh))
{
DataGridViewCell cell = dataGridView.Rows[i].Cells["Diem15"];
dataGridView.CurrentCell = cell;
dataGridView.BeginEdit(true);
dataGridView.Rows[i].Cells["Diem15"].Value = listDiem[j]._Diem15;
dataGridView.EndEdit();
DataGridViewCell cell1 = dataGridView.Rows[i].Cells["Diem1Tiet"];
dataGridView.CurrentCell = cell1;
dataGridView.BeginEdit(true);
dataGridView.Rows[i].Cells["Diem1Tiet"].Value = listDiem[j]._Diem1Tiet;
dataGridView.EndEdit();
DataGridViewCell cell2 = dataGridView.Rows[i].Cells["DiemHK"];
dataGridView.CurrentCell = cell2;
dataGridView.BeginEdit(true);
dataGridView.Rows[i].Cells["DiemHK"].Value = listDiem[j]._DiemHK;
dataGridView.EndEdit();
listDiem.RemoveAt(j);
break;
}
}
}
}
public string UploadExcel(int organizationId, string batchno, string makerId, double amount)
{
string message = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
message = "This file format is not supported";
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
bool isUploaded = false;
int lastRow = finalRecords.Rows.Count - 1;
double disburseTotal = Convert.ToDouble(finalRecords.Rows[lastRow][2]);
if (disburseTotal > 0)
{
if (disburseTotal > amount)
{
isUploaded = false;
message = "Disbursed total amount is greater than company total.";
}
else
{
for (int i = 1; i < finalRecords.Rows.Count - 1; i++)
{
TblDisburseTmp objTblDisburseTmp = new TblDisburseTmp();
objTblDisburseTmp.AcNo = finalRecords.Rows[i][1].ToString().Trim();
objTblDisburseTmp.Amount = Convert.ToDouble(finalRecords.Rows[i][2]);
objTblDisburseTmp.MakerId = makerId.ToString();
objTblDisburseTmp.Batchno = batchno.ToString();
objTblDisburseTmp.Sl = Convert.ToInt16(finalRecords.Rows[i][0]);
objTblDisburseTmp.OrganizationId = organizationId;
_TblDisburseTmpService.Add(objTblDisburseTmp);
}
isUploaded = true;
if (isUploaded)
{
message = "Excel file has been successfully uploaded";
//Insert into audit trial audit and detail
CompanyDisbursementUpload objCompanyDisbursementUpload = new CompanyDisbursementUpload();
objCompanyDisbursementUpload.CompanyId = organizationId;
objCompanyDisbursementUpload.BatchNo = batchno;
objCompanyDisbursementUpload.MakerId = makerId;
_auditTrailService.InsertModelToAuditTrail(objCompanyDisbursementUpload, objCompanyDisbursementUpload.MakerId, 10, 3, "Disbursement Process", objCompanyDisbursementUpload.CompanyId.ToString(), "Uploaded Successfully!");
}
else
{
message = "Excel file uploaded has fiald";
}
}
}
else
{
message = "Disbursed total amount must be greater than 0.";
}
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
}
return(message);
}
private void Loaddata(string filename)
{
string extension = System.IO.Path.GetExtension(filename);
if (extension == ".txt")
{
//#########################################################
//Read from Text file
//#########################################################
StreamReader streamReader = new StreamReader(filename);
string line;
while ((line = streamReader.ReadLine()) != null)
{
string par = line.Replace("；", ";").Substring(0, line.IndexOf(":")).Trim();
string val = line.Replace("，", ",").Substring(line.IndexOf(':') + 1).Trim().TrimEnd(",".ToCharArray());
var v = new VariationDataSource.Variation()
{
Parameter = par, Value = val
};
Variations.Add(v);
}
}
if (extension == ".xls")
{
//#########################################################
//Read from Excel 2003
//#########################################################
FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
//Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataTable result = excelReader.AsDataSet().Tables[0];
int Rows_Count = result.Rows.Count;
for (int i = 0; i < Rows_Count; i++)
{
var v = new VariationDataSource.Variation()
{
Parameter = result.Rows[i][0].ToString().Replace("；", ";").Trim(), Value = result.Rows[i][1].ToString().Replace("，", ",").Trim().TrimEnd(",".ToCharArray())
};
Variations.Add(v);
}
//Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
if (extension == ".xlsx")
{
//#########################################################
//Read from Excel 2007
//#########################################################
FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataTable result = excelReader.AsDataSet().Tables[0];
int Rows_Count = result.Rows.Count;
for (int i = 0; i < Rows_Count; i++)
{
var v = new VariationDataSource.Variation()
{
Parameter = result.Rows[i][0].ToString().Replace("；", ";").Trim(), Value = result.Rows[i][1].ToString().Replace("，", ",").Trim().TrimEnd(",".ToCharArray())
};
Variations.Add(v);
}
//Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
//Update ComboBox
updateComboBox();
//Update Par_ListBox
Parameters Ps = new Parameters(Variations);
Par_ListBox.ItemsSource = Ps;
//Update constrain tab
this.listBox3.Items.Clear();
this.Iftextbox.Clear();
this.Thentextbox.Clear();
}
public void Logic()
{
stops = new List <Stop>();
Days = new List <Day>();
DataSet result = new DataSet();
DataTable table = new DataTable();
FileStream fs = File.Open(sourceFilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
result = reader.AsDataSet();
table = result.Tables[0];
name = "";
SPZ = "";
bool firstLine = true; //hlavička
foreach (DataRow dr in table.Rows)
{
if (firstLine)
{
firstLine = false;
continue;
}
DateTime startDate = DateTime.Parse(dr[6].ToString());
DateTime endDate = DateTime.Parse(dr[5].ToString());
TimeSpan startTime = DateTime.Parse(dr[4].ToString()).TimeOfDay;
TimeSpan endTime = DateTime.Parse(dr[3].ToString()).TimeOfDay;
String place = dr[17].ToString();
name = dr[26].ToString();
SPZ = dr[21].ToString();
if (endDate.Date == startDate.Date)
{
Stop s = new Stop(startDate, endDate, startTime, endTime, place);
stops.Add(s);
}
else
{
Stop s = new Stop(startDate, endDate, startTime, endTime, place);
AddStops(s);
}
}
fs.Close();
Days = GroupToDays(stops);
foreach (Day d in Days)
{
d.Calculate();
}
BindingSource src = new BindingSource();
foreach (Day d in Days)
{
foreach (Stop s in d.Stops)
{
src.Add(s);
}
}
dataGridView1.DataSource = src;
nameLabel.Text = name;
spzLabel.Text = SPZ;
//Exporter ex = new Exporter();
//ex.WriteDietSheet(Days,name,SPZ);
//Application.Exit();
}
public string BulkUploadExcel(string bulkUploadType, string distributorAC, string distributorCode, string distributorName)
{
string message = "";
string duplicateMsg = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
message = "This file format is not supported";
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
string agentCode = null;
if (bulkUploadType == "Agent")
{
string firstClusterCode = _agentService.GetClusterCodeByTerritoryCode(distributorCode.Substring(0, 6));
agentCode = Convert.ToString(_agentService.GenerateAgentCodeAsString(firstClusterCode));
}
for (int i = 1; i < finalRecords.Rows.Count; i++)
{
if (_distributorService.IsExistsByMpohne(finalRecords.Rows[i][0].ToString()) == false)
{
Reginfo objReginfo = new Reginfo();
bool isDuplicateFound = false;
objReginfo.Mphone = finalRecords.Rows[i][0].ToString();
objReginfo.BankAcNo = finalRecords.Rows[i][1].ToString();
if (bulkUploadType == "Distributor")
{
objReginfo.CatId = "D";
}
else if (bulkUploadType == "Agent")
{
objReginfo.CatId = "A";
objReginfo.DistCode = agentCode;
}
else
{
objReginfo.CatId = "C";
}
if (_distributorService.IsExistsByCatidPhotoId(objReginfo.CatId, finalRecords.Rows[i][13].ToString()) == true)
{
if (!string.IsNullOrEmpty(duplicateMsg))
{
duplicateMsg = duplicateMsg + " , Photo duplicate for " + finalRecords.Rows[i][0].ToString();
}
else
{
duplicateMsg = "Photo duplicate for " + finalRecords.Rows[i][0].ToString();
}
isDuplicateFound = true;
}
if (isDuplicateFound == false)
{
objReginfo.Pmphone = finalRecords.Rows[i][3].ToString();
objReginfo.BranchCode = finalRecords.Rows[i][4].ToString();
objReginfo.DateOfBirth = Convert.ToDateTime(finalRecords.Rows[i][5]);
objReginfo.Name = finalRecords.Rows[i][6].ToString();
objReginfo.FatherName = finalRecords.Rows[i][7].ToString();
objReginfo.MotherName = finalRecords.Rows[i][8].ToString();
objReginfo.SpouseName = finalRecords.Rows[i][9].ToString();
objReginfo.Gender = finalRecords.Rows[i][10].ToString();
objReginfo.PhotoIdTypeCode = Convert.ToInt32(finalRecords.Rows[i][12]);
objReginfo.PhotoId = finalRecords.Rows[i][13].ToString();
objReginfo.TinNo = finalRecords.Rows[i][14].ToString();
objReginfo.Religion = finalRecords.Rows[i][15].ToString();
objReginfo.Occupation = finalRecords.Rows[i][16].ToString();
objReginfo.OffMailAddr = finalRecords.Rows[i][17].ToString();
objReginfo.LocationCode = finalRecords.Rows[i][18].ToString();
//objReginfo.DistCode = agentCode;
objReginfo.PreAddr = finalRecords.Rows[i][22].ToString();
objReginfo.PerAddr = finalRecords.Rows[i][23].ToString();
_distributorService.Add(objReginfo);
}
}
else
{
if (!string.IsNullOrEmpty(duplicateMsg))
{
duplicateMsg = duplicateMsg + " , " + finalRecords.Rows[i][0].ToString();
}
else
{
duplicateMsg = finalRecords.Rows[i][0].ToString();
}
}
}
//isUploaded = true;
if (!string.IsNullOrEmpty(duplicateMsg))
{
//message = "Excel file has been successfully uploaded";
message = "Uploaded but following are duplicate : " + duplicateMsg;
}
else
{
//message = "Excel file uploaded has fiald";
message = "Excel file has been successfully uploaded";
}
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
}
return(message);
}
/// <summary>
/// Xlsxs to text.
/// </summary>
/// <param name="autoGenerateClass">If set to <c>true</c> auto generate class.</param>
static void XlsxToTxt(bool autoGenerateClass)
{
var objs = Selection.objects;
for (int i = 0; i < objs.Length; i++)
{
string path = AssetDatabase.GetAssetPath(objs[i]);
if (path.EndsWith(".xlsx"))
{
string fileName = objs[i].name;
string targetFile = path.Replace(".xlsx", ".txt");
int lastI = targetFile.LastIndexOf('/');
targetFile = targetFile.Insert(lastI + 1, "_txt/Resources/");
string direct = Path.GetDirectoryName(targetFile);
if (!Directory.Exists(direct))
{
Directory.CreateDirectory(direct);
}
FileStream targetFileStream = new FileStream(targetFile, FileMode.OpenOrCreate);
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
int columns = result.Tables[0].Columns.Count;
int rows = result.Tables[0].Rows.Count;
StringBuilder txtBuilder = new StringBuilder();
for (int r = 0; r < rows; r++)
{
for (int c = 0; c < columns; c++)
{
txtBuilder.Append(result.Tables[0].Rows[r][c].ToString()).Append("\t");
}
txtBuilder.Append("\n");
}
StreamWriter steamWriter = new StreamWriter(targetFileStream);
steamWriter.Write(txtBuilder.ToString());
steamWriter.Close();
stream.Close();
targetFileStream.Close();
if (autoGenerateClass)
{
GenerateAllClass(targetFile);
}
}
}
AssetDatabase.Refresh();
}
public bool ReadDataFromExcel()
{
foreach (FileInfo fi in WellDataFiles)
{
FileStream fs = null;
try
{
fs = File.Open(fi.FullName, FileMode.Open, FileAccess.Read);
}
catch (System.IO.IOException ex)
{
AccessException(ex);
return(false);
}
if (fs != null)
{
IExcelDataReader reader = null;
switch (fi.Extension)
{
case ".xls":
reader = ExcelReaderFactory.CreateBinaryReader(fs);
break;
case ".xlsx":
reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
break;
//TODO?: можно добавить еще CSV
}
if (reader != null)
{
//Определить все видимые листы
HashSet <string> visibleSheets = new HashSet <string>();
for (var i = 0; i < reader.ResultsCount; i++)
{
// checking visible state
if (reader.VisibleState == "visible")
{
visibleSheets.Add(reader.Name);
}
reader.NextResult();
}
DataSet result = reader.AsDataSet();
string str = reader.VisibleState;
reader.Close();
int key = Convert.ToInt32(Path.GetFileNameWithoutExtension(fi.FullName)
.Replace("-", "").Replace("_", "").Replace(" ", ""));
if (!WellsData.ContainsKey(key))
{
Dictionary <string, WellData> thisFileDict = new Dictionary <string, WellData>();
WellsData.Add(key, thisFileDict);
foreach (DataTable table in result.Tables)
{
//если таблица скрыта, то не трогать ее
if (!visibleSheets.Contains(table.TableName))
{
continue;
}
int colNum = table.Columns.Count;
//int skipped = 0;
bool headerSkipped = false;
WellData currentWellData = null;
foreach (DataRow row in table.Rows)
{
//Пропустить нужное количество строк с начала таблицы
//if (skipped < SKIP_ROWS_COUNT)
//{
// skipped++;
// continue;
//}
//считать, что последняя строка шапки таблицы содержит занчения 1, 2, 3, 4...12
if (!headerSkipped)
{
if (DataRowIsHeaderEnd(row, colNum))
{
headerSkipped = true;
}
continue;
}
string wellNum = row[WELL_NUM_COL].ToString();
if (!String.IsNullOrEmpty(wellNum))
{
if (!thisFileDict.ContainsKey(wellNum))
{
string sizeStr = row[SIZE1_COL].ToString();
double size1 = -1;
double size2 = -1;
double topLevel = double.NegativeInfinity;
double bottomLevel = double.NegativeInfinity;
double.TryParse(sizeStr, out size1);
double.TryParse(row[SIZE2_COL].ToString(), out size2);
string topLevelStr = row[WELL_TOP_LEVEL_COL].ToString();
if (CONTAINS_NUMBERS.IsMatch(topLevelStr))//текст должен содержать хотябы одну цифру (иногда там пишут прочерки или что-то такое)
{
double.TryParse(topLevelStr.Replace(',', '.'), out topLevel);
}
string bottomLevelStr = row[WELL_BOTTOM_LEVEL_COL].ToString();
if (CONTAINS_NUMBERS.IsMatch(bottomLevelStr))
{
double.TryParse(bottomLevelStr.Replace(',', '.'), out bottomLevel);
}
currentWellData = new WellData()
{
Num = wellNum,
NetworkType = row[NETWORK_TYPE_COL].ToString(),
SizeString = sizeStr,
Size1 = size1,
Size2 = size2,
Material = row[WELL_MATERIAL_COL].ToString(),
TopLevel = topLevel,
BottomLevel = bottomLevel,
};
thisFileDict.Add(wellNum, currentWellData);
ReadPipeJunctionData(row, currentWellData);
}
}
else if (currentWellData != null)
{
//добавление нового присоединения к данным колодца
ReadPipeJunctionData(row, currentWellData);
}
}
}
}
}
}
}
return(true);
}
public DataSet ToDataSet(string filePath, bool isFirstRowAsColumnNames)
{
try
{
Logger.Current.Verbose("Get Rows content for the uploaded excel file");
var fileInfo = new FileInfo(filePath);
var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
var excelReader = (string.Equals(".xls", fileInfo.Extension, StringComparison.OrdinalIgnoreCase)) ? ExcelReaderFactory.CreateBinaryReader(stream) : ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tablereader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
excelReader.Close();
return(result);
}
catch (Exception ex)
{
Logger.Current.Error("An error occured while converting excel to DataSet", ex);
throw;
}
}
private double MARKING(string fileName)
{
string resultName = "";
foreach (string name in resultFileName)
{
if (name.Contains(formatExamID(fileName)))
{
resultName = name;
}
}
int countWrong = 0;
using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
answerView.DataSource = ds.Tables[0];
}
using (var stream = File.Open(resultName, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
resultView.DataSource = ds.Tables[0];
}
int rows = resultView.Rows.Count;
int columns = resultView.Columns.Count;
for (int i = 0; i < rows - 1; i++)
{
for (int j = 0; j < columns; j++)
{
if (!answerView.Rows[i].Cells[j].Value.ToString().Equals(resultView.Rows[i].Cells[j].Value.ToString()))
{
countWrong++;
}
}
}
double score = (double)(rows - 1 - countWrong) * 10 / (double)(rows - 1);
return(score);
}
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
var line = 1;
var isBinary = _fileInfo.Extension == ".xls";
using (var fileStream = File.Open(_fileInfo.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
using (var reader = isBinary ? ExcelReaderFactory.CreateBinaryReader(fileStream) : ExcelReaderFactory.CreateOpenXmlReader(fileStream)) {
if (reader == null)
{
yield break;
}
var emptyBuilder = new StringBuilder();
while (reader.Read())
{
line++;
if (line > _start)
{
if (_end == 0 || line <= _end)
{
var row = new Row();
row["TflFileName"] = _fileInfo.FullName;
emptyBuilder.Clear();
foreach (var field in _fields)
{
var value = reader.GetValue(field.Index);
row[field.Alias] = value;
emptyBuilder.Append(value);
}
emptyBuilder.Trim(" ");
if (!emptyBuilder.ToString().Equals(string.Empty))
{
yield return(row);
}
}
}
}
}
}
}
public static bool LoadFile(string filePath, ref List <DataType> listItems)
{
if (!File.Exists(filePath))
{
return(false);
}
FileInfo fi = new FileInfo(filePath);
if (null != listItems)
{
listItems.Clear();
}
else
{
listItems = new List <DataType>();
}
using (FileStream fs = new FileStream(filePath, FileMode.Open))
{
IExcelDataReader reader = null;
if (".xls" == fi.Extension)
{
reader = ExcelReaderFactory.CreateBinaryReader(fs);
}
else if (".xlsx" == fi.Extension)
{
reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
}
// no valid reader created -> exit
if (reader == null)
{
return(false);
}
DataSet ds = reader.AsDataSet();
foreach (DataTable dtTable in ds.Tables)
{
int iRowStart = 1;
for (int iRow = iRowStart; iRow < dtTable.Rows.Count; ++iRow)
{
DataType dataType = null;
try
{
dataType = BuildDataType(dtTable.TableName, iRow, dtTable.Rows[iRow]);
}
catch (InvalidRowException /*ex*/)
{
break;
}
catch (Exception ex)
{
_log.Error(string.Format("Failed to read {0}({1}) with message : {2}", dtTable.TableName, iRow, ex.Message));
dataType = null;
}
if (null != dataType)
{
listItems.Add(dataType);
}
}
}
}
return(listItems.Count > 0);
}
static void ReadExcel()
{
Config Config = Resources.Load <Config>("Config");
Config.Range.Clear();
Config.ExtraBonusConfigs.Clear();
Config.SpecialPropsConfigs.Clear();
Config.JackpotConfigConfigs.Clear();
//在此添加新列表
xlsxPath = Application.dataPath + "/Config.xlsx";
if (!File.Exists(xlsxPath))
{
Debug.LogError("文件不存在");
return;
}
FileStream fs = new FileStream(xlsxPath, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
DataSet dataSet = reader.AsDataSet();
reader.Dispose();
if (dataSet == null)
{
Debug.LogError("文件为空!");
return;
}
DataTable firstTable = dataSet.Tables[0];
int rowsCount = firstTable.Rows.Count;
for (int i = 2; i < rowsCount; i++)
{
//范围
Config.Range.Add((int)(float.Parse(firstTable.Rows[i][1].ToString()) * 100));
int configIndex = i - 2;
ExtraBonusConfig extraBonusConfig = new ExtraBonusConfig
{
rangeIndex = configIndex,
needTargetStep = int.Parse(firstTable.Rows[i][2].ToString()),
cashBonusRate = float.Parse(firstTable.Rows[i][3].ToString()),
minCashBonus = int.Parse(firstTable.Rows[i][4].ToString()),
maxCashBonus = int.Parse(firstTable.Rows[i][5].ToString()),
goldBonusRate = float.Parse(firstTable.Rows[i][6].ToString()),
minGoldBonus = int.Parse(firstTable.Rows[i][7].ToString()),
maxGoldBonus = int.Parse(firstTable.Rows[i][8].ToString()),
};
Config.ExtraBonusConfigs.Add(extraBonusConfig);
SpecialPropsConfig specialPropsConfig = new SpecialPropsConfig
{
rangeIndex = configIndex,
minCashReward = int.Parse(firstTable.Rows[i][9].ToString()),
maxCashReward = int.Parse(firstTable.Rows[i][10].ToString()),
minGoldReward = int.Parse(firstTable.Rows[i][12].ToString()),
maxGoldReward = int.Parse(firstTable.Rows[i][13].ToString())
};
specialPropsConfig.cashMutiple = new List <float>();
string[] cashmutiples = firstTable.Rows[i][11].ToString().Split(';');
for (int j = 0; j < cashmutiples.Length; j++)
{
specialPropsConfig.cashMutiple.Add(float.Parse(cashmutiples[j]));
}
specialPropsConfig.goldMutiple = new List <float>();
string[] goldmutiples = firstTable.Rows[i][14].ToString().Split(';');
for (int j = 0; j < goldmutiples.Length; j++)
{
specialPropsConfig.goldMutiple.Add(float.Parse(goldmutiples[j]));
}
Config.SpecialPropsConfigs.Add(specialPropsConfig);
JackpotConfig jackpotConfig = new JackpotConfig()
{
noRewardRate = float.Parse(firstTable.Rows[i][15].ToString()),
cashRewardRate = float.Parse(firstTable.Rows[i][16].ToString()),
goldRewardRate = float.Parse(firstTable.Rows[i][18].ToString())
};
jackpotConfig.cashPool = new List <int>();
string[] cashNums = firstTable.Rows[i][17].ToString().Split(';');
for (int j = 0; j < cashNums.Length; j++)
{
jackpotConfig.cashPool.Add(int.Parse(cashNums[j]));
}
jackpotConfig.goldPool = new List <int>();
string[] goldNums = firstTable.Rows[i][19].ToString().Split(';');
for (int j = 0; j < goldNums.Length; j++)
{
jackpotConfig.goldPool.Add(int.Parse(goldNums[j]));
}
Config.JackpotConfigConfigs.Add(jackpotConfig);
}
Debug.Log("Excel文件读取完成！");
EditorUtility.SetDirty(Config);
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
}
/// <summary>
/// 读取所有Excel文件内部信息
/// </summary>
private bool readAllExcelFilesInfo()
{
ExcelsInfoMap.Clear();
bool issuccess = true;
foreach (var excelfile in AllExcelFilesList)
{
// 如果配置有问题，直接退出
if (issuccess == false)
{
Console.WriteLine("配置有问题，请先修正配置后再导出!");
break;
}
FileStream fs = File.Open(excelfile, FileMode.Open, FileAccess.Read);
IExcelDataReader excelreader = ExcelReaderFactory.CreateOpenXmlReader(fs);
if (!excelreader.IsValid)
{
Console.WriteLine(string.Format("Excel文件:{0}读取失败！", excelfile));
issuccess = false;
break;
}
else
{
#if DEBUG
Console.WriteLine(string.Format("Excel文件.Name:{0}", excelreader.Name));
#endif
var dataset = excelreader.AsDataSet();
for (int index = 0, length = dataset.Tables.Count; index < length; index++)
{
if (isValideSheet(excelreader.Name))
{
if (hasSheetNameExist(excelreader.Name))
{
Console.WriteLine(string.Format("Excel:{0}有重名Sheet:{1}!", excelfile, excelreader.Name));
issuccess = false;
break;
}
else
{
var excelinfo = new ExcelInfo();
excelinfo.ExcelName = excelreader.Name;
int currentlinenumber = 1;
while (excelreader.Read())
{
//读取每一行的数据
string[] datas = new string[excelreader.FieldCount];
for (int i = 0; i < excelreader.FieldCount; i++)
{
datas[i] = excelreader.GetString(i);
}
// 字段信息行
if (currentlinenumber == FieldNameLineNumber)
{
excelinfo.FieldNames = datas;
}
// 字段注释信息行
else if (currentlinenumber == FieldNotationLineNumber)
{
excelinfo.FieldNotations = datas;
}
// 字段类型信息行
else if (currentlinenumber == FieldTypeLineNumber)
{
excelinfo.FieldTypes = datas;
}
// 字段分隔符信息行
else if (currentlinenumber == FieldSpliterLineNumber)
{
excelinfo.FieldSpliters = datas;
}
// 字段占位符1信息行
else if (currentlinenumber == FieldPlaceHolder1LineNumber)
{
excelinfo.FieldPlaceholder1s = datas;
}
// 字段占位符2信息行
else if (currentlinenumber == FieldPlaceHolder2LineNumber)
{
excelinfo.FieldPlaceholder2s = datas;
}
else if (currentlinenumber >= DataLineNumber)
{
// 存储数据之前，检查一次各字段名字，字段信息等配置是否正确
if (currentlinenumber == DataLineNumber)
{
if (hasInvalideName(excelinfo.FieldNames, excelinfo.FieldTypes))
{
Console.WriteLine(string.Format("Excel Table:{0}", excelreader.Name));
issuccess = false;
break;
}
if (hasInvalideType(excelinfo.FieldTypes))
{
Console.WriteLine(string.Format("Excel Table:{0}", excelreader.Name));
issuccess = false;
break;
}
if (hasInvalideSpliter(excelinfo.FieldSpliters))
{
Console.WriteLine(string.Format("Excel Table:{0}", excelreader.Name));
issuccess = false;
break;
}
}
// 记录每一行所有数据的字段名，字段类型，字段数据
ExcelData[] exceldatas = new ExcelData[datas.Length];
for (int m = 0; m < datas.Length; m++)
{
ExcelData cd = new ExcelData();
cd.Type = excelinfo.FieldTypes[m];
cd.Name = excelinfo.FieldNames[m];
cd.Spliter = excelinfo.FieldSpliters[m];
cd.Data = datas[m];
exceldatas[m] = cd;
}
if (issuccess == false)
{
break;
}
else
{
excelinfo.addData(exceldatas);
}
}
else
{
Console.WriteLine(string.Format("无效的行号:{0}", currentlinenumber));
issuccess = false;
break;
}
currentlinenumber++;
}
// 检查是否有重复的id
if (!isIdValide(excelinfo))
{
Console.WriteLine($"Excel Sheet:{excelinfo.ExcelName}的配置有问题!");
issuccess = false;
break;
}
ExcelsInfoMap.Add(excelreader.Name, excelinfo);
}
}
excelreader.NextResult();
}
}
}
if (!issuccess)
{
clearExcelInfo();
return(false);
}
else
{
#if DEBUG
//打印表格数据信息
//foreach(var excelinfo in ExcelsInfoMap)
//{
// excelinfo.Value.printOutAllExcelInfo();
//}
#endif
return(true);
}
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
#region Variables
fa_notaCreDeb_List ListaFactura = new fa_notaCreDeb_List();
List <fa_notaCreDeb_Info> Lista_Factura = new List <fa_notaCreDeb_Info>();
fa_cliente_Bus bus_cliente = new fa_cliente_Bus();
fa_cliente_contactos_Bus bus_cliente_contatos = new fa_cliente_contactos_Bus();
tb_sucursal_Bus bus_sucursal = new tb_sucursal_Bus();
fa_parametro_Bus bus_fa_parametro = new fa_parametro_Bus();
fa_TipoNota_Bus bus_tipo_nota = new fa_TipoNota_Bus();
tb_bodega_Bus bus_bodega = new tb_bodega_Bus();
int cont = 0;
int IdNota = 1;
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa);
#endregion
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
#region Saldo Fact
var info_fa_parametro = bus_fa_parametro.get_info(IdEmpresa);
var IdTipoNota = 12; //default
var infoTipoNota = bus_tipo_nota.get_info(IdEmpresa, IdTipoNota);
var CodDocumentoTipo = "NTDB";
var IdPuntoVta = 7;
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
var Su_CodigoEstablecimiento = Convert.ToString(reader.GetValue(0)).Trim();
var lst_sucursal = bus_sucursal.get_list(IdEmpresa, false);
var IdSucursal = lst_sucursal.Where(q => q.Su_CodigoEstablecimiento == Su_CodigoEstablecimiento).FirstOrDefault().IdSucursal;
var InfoCliente = bus_cliente.get_info_x_num_cedula(IdEmpresa, Convert.ToString(reader.GetValue(1)));
var infoBodega = bus_bodega.get_info(IdEmpresa, IdSucursal, 1);
if (InfoCliente != null && InfoCliente.IdCliente != 0)
{
//var InfoContactosCliente = bus_cliente_contatos.get_list(IdEmpresa, InfoCliente.IdCliente);
var InfoContactosCliente = bus_cliente_contatos.get_info(IdEmpresa, InfoCliente.IdCliente, 1);
fa_notaCreDeb_Info info = new fa_notaCreDeb_Info
{
IdEmpresa = IdEmpresa,
IdSucursal = IdSucursal,
IdBodega = infoBodega.IdBodega,
IdNota = IdNota++,
dev_IdEmpresa = null,
dev_IdDev_Inven = null,
CodNota = Convert.ToString(reader.GetValue(2)),
CreDeb = "D",
CodDocumentoTipo = CodDocumentoTipo,
Serie1 = null,
Serie2 = null,
NumNota_Impresa = null,
NumAutorizacion = null,
Fecha_Autorizacion = null,
IdCliente = InfoCliente.IdCliente,
no_fecha = Convert.ToDateTime(reader.GetValue(5)),
no_fecha_venc = Convert.ToDateTime(reader.GetValue(6)),
IdTipoNota = infoTipoNota.IdTipoNota,
sc_observacion = Convert.ToString(reader.GetValue(7)) == "" ? ("DOCUMENTO #" + Convert.ToString(reader.GetValue(2)) + " CLIENTE: " + InfoCliente.info_persona.pe_nombreCompleto) : Convert.ToString(reader.GetValue(7)),
IdUsuario = SessionFixed.IdUsuario,
NaturalezaNota = null,
IdCtaCble_TipoNota = infoTipoNota.IdCtaCble,
IdPuntoVta = IdPuntoVta,
aprobada_enviar_sri = false
};
info.lst_det = new List <fa_notaCreDeb_det_Info>();
info.lst_cruce = new List <fa_notaCreDeb_x_fa_factura_NotaDeb_Info>();
fa_notaCreDeb_det_Info info_detalle = new fa_notaCreDeb_det_Info
{
IdEmpresa = IdEmpresa,
IdSucursal = IdSucursal,
IdBodega = info.IdBodega,
IdNota = info.IdNota,
IdProducto = 1,
sc_cantidad = 1,
sc_Precio = Convert.ToDouble(reader.GetValue(4)),
sc_descUni = 0,
sc_PordescUni = 0,
sc_precioFinal = Convert.ToDouble(reader.GetValue(4)),
sc_subtotal = Convert.ToDouble(reader.GetValue(4)),
sc_iva = 0,
sc_total = Convert.ToDouble(reader.GetValue(4)),
sc_costo = 0,
sc_observacion = Convert.ToString(reader.GetValue(7)),
vt_por_iva = 0,
IdPunto_Cargo = null,
IdPunto_cargo_grupo = null,
IdCod_Impuesto_Iva = "IVA0",
IdCentroCosto = null,
sc_cantidad_factura = null
};
info.lst_det.Add(info_detalle);
Lista_Factura.Add(info);
}
}
else
{
cont++;
}
}
ListaFactura.set_list(Lista_Factura, IdTransaccionSession);
#endregion
}
}
public async Task <ResponseStatus> Uploadexcel()
{
ResponseStatus status = new ResponseStatus();
string message = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
//string vendorId = Convert.ToString(httpRequest.Form["vendorid"]);
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
message = "This file format is not supported";
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
for (int i = 0; i < finalRecords.Rows.Count; i++)
{
var code = finalRecords.Rows[i][4].ToString();
var product = appDbContex.Products.Where(a => a.code == code && a.deleted == false).FirstOrDefault();
if (product != null)
{
product.price = Convert.ToDouble(finalRecords.Rows[i][3].ToString());
product.quantity = Convert.ToDouble(finalRecords.Rows[i][4].ToString());
await appDbContex.SaveChangesAsync();
}
}
status.status = true;
status.message = "Price updated successfully";
return(status);
//if (output > 0)
//{
// message = "Excel file has been successfully uploaded";
//}
//else
//{
// message = "Excel file uploaded has fiald";
//}
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
status.status = true;
status.message = result.ToString();
return(status);
}
}
protected void btUpload_Click(object sender, EventArgs e)
{
string extensao = System.IO.Path.GetExtension(fUpload.FileName);
if (fUpload.HasFile)
{
if (extensao.ToLower() == ".xls" || extensao.ToLower() == ".xlsx")
{
DataTable dtDadosUpload = new DataTable();
string FileName = Server.HtmlEncode(fUpload.FileName);
string caminhoArquivo = string.Empty;
//Caminho local
//string caminhoDestino = Server.MapPath("/ArquivosTemp/");
//Caminho para o servidor
string caminhoDestino = @"C:\FreezyTrack\ArquivosTemp\";
if (Directory.Exists(caminhoDestino))
{
string arquivoComCaminho = caminhoDestino + fUpload.FileName;
if (File.Exists(arquivoComCaminho))
{
File.Delete(arquivoComCaminho);
}
try
{
fUpload.SaveAs(arquivoComCaminho);
if (File.Exists(arquivoComCaminho))
{
using (FileStream stream = File.Open(arquivoComCaminho, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader excelReader;
if (extensao.ToLower() == ".xls")
{
//Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//Reading from a OpenXml Excel file (2007+ format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
//DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet dsResult = excelReader.AsDataSet();
dtDadosUpload = dsResult.Tables[0];
//Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
CarregaDadosConteudoImportacao(dtDadosUpload);
}
//Apaga o arquivo temporário
File.Delete(arquivoComCaminho);
}
else
{
MostraRetorno("Falha ao importar o arquivo.<br/> " +
"Por favor, contate o administrador do sistema. <br>", 2);
}
}
catch (Exception ex)
{
MostraRetorno("Ocorreu um erro durante o processo. <br/> " +
"Por favor, contate o administrador do sistema e informe a seguinte mensagem; <br> " + ex.ToString() + "", 2);
}
}
else
{
MostraRetorno("Falha ao encontrar o caminho para salvar o arquivo.<br/> " +
"Por favor, contate o administrador do sistema. <br>" +
"Pasta Destino; " + Server.MapPath("/ArquivosTemp/") + "", 2);
}
}
else
{
divProcessando.Visible = false;
MostraRetorno("Formato de arquivo não suportado! <br/> Selecione somente arquivos .xls e .xlsx.", 2);
}
}
else
{
MostraRetorno("Por favor, selecione um arquivo para importaçao.", 2);
}
}
private static void RunTwitterApplication()
{
// BLOCK 1 : Twitter credentials
// Twitter Credentials: Reading in a json configuration file with both credentials for the twitter integration
// and the dedicated twitter account name used for "weather sense"
JObject TwitterConfiguration = JObject.Parse(File.ReadAllText(RootDirectory + @"Package\Config_Interfaces\Twitter_context_profile.json"));
// Locally reading and storing all credential values
string consumer_key = (string)TwitterConfiguration["consumer_key"];
string consumer_secret = (string)TwitterConfiguration["consumer_secret"];
string access_token = (string)TwitterConfiguration["access_token"];
string access_token_secret = (string)TwitterConfiguration["access_token_secret"];
string sensor_account = (string)TwitterConfiguration["sensor_account"];
// Setting twitter credentials
TwitterCredentials.SetCredentials(access_token, access_token_secret, consumer_key, consumer_secret);
// BLOCK 2: Populating Landmarks
// Import excel worksheet for landmark initialization (Name, Categories, Hashtag_trackers, Thematic_Metatags)
// Reading excel worksheet using exceldatareader nuget package
FileStream stream = File.Open(RootDirectory + @"Package\Config_Interfaces\Landmarks_Categories.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
// This should initialize all the parameters in the Lanmarks object array using its contructor
int numberoflandmarks = result.Tables[0].Rows.Count - 1;
Landmark[] Landmarks = new Landmark[numberoflandmarks];
for (int i = 0; i < numberoflandmarks; i++)
{
Landmarks[i] = new Landmark(result.Tables[0].Rows[i]);
}
//BLOCK 3: Hook population
// Reading in datatable which contains hooks and their properties
int numberofhooks = result.Tables[1].Rows.Count - 1;
Hook[] Hooks = new Hook[numberofhooks];
for (int i = 0; i < numberofhooks; i++)
{
Hooks[i] = new Hook(result.Tables[1].Rows[i]);
}
////BLOCK 4: Twitter Stream trackers
//// Creating twitter stream for detecting tweets with landmark names
var stream_for_landmark = Stream.CreateFilteredStream();
// Adding trackers for each landmark
foreach (Landmark obj in Landmarks)
{
string[] landmark_hashtags_store = obj.Hashtag_trackers;
// Loop block to assign trackers to each relevant hashtag element in the twitter stream
foreach (string hashtag in landmark_hashtags_store)
{
stream_for_landmark.AddTrack(hashtag);
}
}
//var stream_for_landmark = Stream.CreateFilteredStream();
//stream_for_landmark.AddTrack("volkshotel");
// BLOCK 5: Landing zone and context aware parameters
LandingZone landingzone = new LandingZone();
char MorningOrAfternoon = landingzone.MorningOrAfternoon();
char Weather = Sensors.WeatherSense.SunnyOrCloudy(sensor_account);
//BLOCK 5: Event trigger for tracked tweets
// Event trigger block for each new tweet on the above location based landmark triggers
// String array to store all different weather conditions
// Hook module : Time of the day - categorize as morning, afternoon and evening
// Hook is overlayed on the video - in the center of the video
// Video module : Videos are named as landmark and weather
// Weather detection module : Must read through twitter stream of test account -> Assign a hastag weather -> could filter out the
List <Hook> HooksforExcel = Hooks.ToList <Hook>();
//ExcelFileWriter<Hook> myExcel = new ExcelWrite();
//myExcel.WriteDateToExcel(@"C:\TEMP\myExcel.xls", HooksforExcel, "A1", "D1");
// Landmark is detected by trackers
// Landmark is pushed to method for extracting content from DB
// This can happen either here or in openframeworks
// Once content is extracted it must be sequenced on the basis of :
// Time to play each item : Once one item is played for a certain preset amount of time extract the next from DB
// Time calculation in openframeworks:
// Update loop and draw loop
// The sensing modules exist on this side. So Hooks are selected here and published onto the interface between OF and Encounter#
// Hooks are read by OF and sequenced according to time parameters in OF
//Two kinds of time parameters in OF:
// Amount of time video must be played for
// Amount of time a hook should be shown for
// How does timing fit into the update and draw cycles?
// Use a timer, counting seconds until a value is reached
// Validation for time count can be executed at every loop update
// Update should contain a timing module where all the timing logic can be placed
// Objects required in OF
// OF has to handle video placement, playback and sequencing
// OF has to handle Hook placement, sequencing and timing
// OF has to handle visual blocks with animated elements
Console.WriteLine("Stream detection live now:");
stream_for_landmark.MatchingTweetReceived += (sender, arg) =>
{
// Storing the tweet
string tweet = arg.Tweet.ToString();
// Searching through tweet to find relevant landmark
MatchedLandmark detectedlandmark = Find_tweeted_landmark(Landmarks, tweet);
// We ignore the landmark-specific categories, hooks are categorized by landmark + wildcard humor category.
string[] relevantHookCategories = new string[] { detectedlandmark.Landmark.Name, "humor" }; // detectedlandmark.Landmark.Categories;
// Writing detected landmark to text file interface between here and OF
using (StreamWriter writer = new StreamWriter(RootDirectory + @"Package\Config_Interfaces\TriggeredLandmark.txt", false))
{
writer.Write("{0}\r\n{1}", detectedlandmark.Landmark.Name, detectedlandmark.Hashtag);
}
List <Hook> RelevantHooks = new List <Hook>();
// Selecting hooks that belong to the relevant categories corresponding to the landmark - Obsolete
foreach (string category in relevantHookCategories)
{
RelevantHooks.AddRange((from item in Hooks
where item.category == category
select item).ToArray());
}
// Storing current sensor values
MorningOrAfternoon = landingzone.MorningOrAfternoon();
Weather = Sensors.WeatherSense.SunnyOrCloudy(sensor_account);
// Further filtering hooks based on sensor values
RelevantHooks = (from hook in RelevantHooks
where
(hook.timeofday.Contains(MorningOrAfternoon) || String.IsNullOrEmpty(hook.timeofday)) &&
(hook.weather.Contains(Weather) || String.IsNullOrEmpty(hook.weather))
select hook).ToList();
// Writing the selected hooks to an XML to be read in by OF
using (XmlWriter writer = XmlWriter.Create(RootDirectory + @"of_v0.8.4_vs_release\apps\myApps\OpenEncounters\bin\data\Hooks.xml"))
{
writer.WriteStartDocument();
writer.WriteStartElement("Filtered_hooks");
foreach (var item in RelevantHooks)
{
writer.WriteStartElement("Hook");
writer.WriteElementString("text", item.text);
writer.WriteElementString("category", item.category);
writer.WriteElementString("weather", item.weather);
writer.WriteElementString("timeofday", item.timeofday);
writer.WriteElementString("font", item.font);
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndDocument();
}
Console.WriteLine(tweet);
Console.WriteLine();
Console.Write("Landmark Triggered:");
Console.Write(detectedlandmark.Landmark.Name);
Console.WriteLine();
//System.Threading.Thread.Sleep(
//(int)System.TimeSpan.FromSeconds(30).TotalMilliseconds);
};
// Starting twitter stream detection
stream_for_landmark.StartStreamMatchingAllConditions();
}
public async Task <DataTable> ReadExcelUnityLink(string FileDirectory, int ReconTypeId, string fileNamenAndType)
{
DataTable dTable = new DataTable();
try
{
var getReconTypeInfo = await repoReconTypeRepository.GetAsync(c => c.ReconTypeId == ReconTypeId);
if (!fileNamenAndType.Contains(getReconTypeInfo.FileNamingConvention))
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Unrecognized file name";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read File, the Naming Convention does not match for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " . The file Name and type is: " + fileNamenAndType + " and the conventional name must be: " + getReconTypeInfo.FileNamingConvention);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
else
{
var chkfile = await repoadmFileProcessedRepository.GetAsync(c => c.FileName == fileNamenAndType);
if (chkfile != null)
{
var failFileTBL = new FailledFile();
failFileTBL.ReconTypeId = ReconTypeId;
failFileTBL.FileDirectory = FileDirectory;
failFileTBL.FileName = fileNamenAndType;
failFileTBL.NameConvention = getReconTypeInfo.FileNamingConvention;
failFileTBL.DateProcessed = DateTime.Now;
failFileTBL.ErrCode = -1;
failFileTBL.ErrText = "Couldn't process File more than once";
failFileTBL.DateCreated = DateTime.Now;
failFileTBL.UserId = 1;
LogManager.SaveLog("Cannot read the File more than once, File already Read for Recon type :" + getReconTypeInfo.ReconName + " from FileDirectory: " + FileDirectory + " The file Name and it type is: " + fileNamenAndType);
repoadmFailledFileRepository.Add(failFileTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
string fnn = getReconTypeInfo.RejectedFileDirectory;
string fn = @fnn + "\\" + FileDirectory + fileNamenAndType;
File.Move((FileDirectory + "\\" + fileNamenAndType), fn);
return(dTable);
}
}
}
IExcelDataReader excelReader = null;
using (MemoryStream ms = new MemoryStream())
using (FileStream file = new FileStream(FileDirectory + "\\" + fileNamenAndType, FileMode.Open, FileAccess.Read))
{
byte[] bytes = new byte[file.Length];
file.Read(bytes, 0, (int)file.Length);
ms.Write(bytes, 0, (int)file.Length);
if (fileNamenAndType.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(new MemoryStream(bytes));
}
else if (fileNamenAndType.Contains("xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(bytes));
}
else if (fileNamenAndType.Contains("XLS"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(new MemoryStream(bytes));
}
}
var result = excelReader.AsDataSet();
excelReader.IsFirstRowAsColumnNames = true;
var dt = new DataTable();
dt = result.Tables[0];
return(dTable);
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in Line Library ReadExcel File Source:" + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
throw;
}
return(dTable);
}
static void Main(string[] args)
{
//İSİM İŞLEMLERİ
//Dosyanın okunacağı dizin
string filePath = @"C:\Users\oguz-\Desktop\İETT PROJE\isim.xlsx";
//Dosyayı okuyacağımı ve gerekli izinlerin ayarlanması.
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
List <double> liste = new List <double>();
List <İsim> isimListesi = new List <İsim>();
int counter = 0;
//Gönderdiğim dosya xls'mi xlsx formatında mı kontrol ediliyor.
if (Path.GetExtension(filePath).ToUpper() == ".XLS")
{
//Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
//Datasete atarken ilk satırın başlık olacağını belirtiyor.
DataSet result = excelReader.AsDataSet();
while (excelReader.Read())//excelden veriler liste atıldı.
{
counter++;
//ilk satır başlık olduğu için 2.satırdan okumaya başlıyorum.
if (counter > 1)
{
liste.Add(excelReader.GetDouble(0));
liste.Add(excelReader.GetDouble(1));
liste.Add(excelReader.GetDouble(2));
}
}
//0-380 list<isim> içerisine veriler yerleştirildi.
for (int i = 0; i <= 378; i = i + 3)
{
İsim isim = new İsim();
isim.ID = liste[i];
isim.isimXkoordinat = liste[i + 1];
isim.isimYkoordinat = liste[i + 2];
isimListesi.Add(isim);
}
//Okuma bitiriliyor.
excelReader.Close();
//İSİM İŞLEMLERİ BİTTİ
//List<isim> isimlistesi altında isimler , x ve y koordinatları tutuldu.
//DURAK İŞLEMLERİ
//Dosyanın okunacağı dizin
string filePath1 = @"C:\Users\oguz-\Desktop\İETT PROJE\durak.xlsx";
//Dosyayı okuyacağımı ve gerekli izinlerin ayarlanması.
FileStream stream1 = File.Open(filePath1, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader1;
List <double> liste1 = new List <double>();
List <Durak> durakliste = new List <Durak>();
int counter1 = 0;
//Gönderdiğim dosya xls'mi xlsx formatında mı kontrol ediliyor.
if (Path.GetExtension(filePath1).ToUpper() == ".XLS")
{
//Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader1 = ExcelReaderFactory.CreateBinaryReader(stream1);
}
else
{
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader1 = ExcelReaderFactory.CreateOpenXmlReader(stream1);
}
//Datasete atarken ilk satırın başlık olacağını belirtiyor.
DataSet result1 = excelReader1.AsDataSet();
//Veriler okunmaya başlıyor.
while (excelReader1.Read())
{
counter1++;
//ilk satır başlık olduğu için 2.satırdan okumaya başlıyorum.
if (counter1 > 1)
{
liste1.Add(excelReader1.GetDouble(0));
liste1.Add(excelReader1.GetDouble(1));
liste1.Add(excelReader1.GetDouble(2));
}
}
for (int y = 0; y < liste1.Count; y = y + 3)
{
Durak durak = new Durak();
durak.durakKodu = liste1[y];
durak.durakXkoordinat = liste1[y + 1];
durak.durakYkoordinat = liste1[y + 2];
durakliste.Add(durak);
}
//Okuma bitiriliyor.
excelReader1.Close();
//DURAK İŞLEMLERİ BİTTİ
//List<Durak> durakliste adı altında durakKodu , durakXkoordinat , durakYkoordinat alanlarında tutuldu.
/*
* BENDEN İSTENENLER
* 1.Her kişiye en yakın 15 durak
* -bir kişiye en yakın durak bulunacak.durak ataması yapılacak.durak listeden silinecek.
* -diğer kişiye en yakın durak bulunacak.durak ataması yapıcalacak.durak listeden silinecek.
* -126 kişiye atama yapıldıktan sonra aynı işlemi 15 kere tekrar edicem.
*
*
* 2.Her kişiye en yakın 15 durak
* -Herkez için en yakın ilk durak bulunacak atama işlemi yapılacak.
* -Atanan durak listeden silinecek.
* -Daha sonra kişinin x ve y si en son atanan durağın x ve y si olacak.
* -Daha sonra diğer kişilere 1. durakları atanacak.
* -Daha sonra 2.3.,....,15. durak atanacak.
*/
List <İsim> yedekisimliste = new List <İsim>();
yedekisimliste = isimListesi;
List <Durak> yedekdurakliste = new List <Durak>();
yedekdurakliste = durakliste;
List <double> enyakin1 = new List <double>();
List <double> enyakin2 = new List <double>();
List <double> enyakin3 = new List <double>();
List <double> enyakin4 = new List <double>();
List <double> enyakin5 = new List <double>();
List <double> enyakin6 = new List <double>();
List <double> enyakin7 = new List <double>();
List <double> enyakin8 = new List <double>();
List <double> enyakin9 = new List <double>();
List <double> enyakin10 = new List <double>();
List <double> enyakin11 = new List <double>();
List <double> enyakin12 = new List <double>();
List <double> enyakin13 = new List <double>();
List <double> enyakin14 = new List <double>();
List <double> enyakin15 = new List <double>();
Console.Clear();
double distance = 0.0;
double ydifference = 0.0;
double xdifference = 0.0;
double sonuc = 1000.0;
double[,] Array2D = new double[127, 16];
for (int z = 0; z < 127; z++)
{
Array2D[z, 0] = z + 1;
}
Durak temp = new Durak();
List <double> gerceklisteX = new List <double>();
List <double> gerceklisteY = new List <double>();
//enyakin 1. duraklar burada bulundu ve listeden silindi.
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin1.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 1] = enyakin1[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
//2.for
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin2.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 2] = enyakin2[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
//3. for
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin3.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 3] = enyakin3[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin4.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 4] = enyakin4[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin5.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 5] = enyakin5[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*//////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin6.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 6] = enyakin6[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*//////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin7.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 7] = enyakin7[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*//////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin8.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 8] = enyakin8[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*///////////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin9.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 9] = enyakin9[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*////////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin10.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 10] = enyakin10[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*///////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin11.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 11] = enyakin11[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*////////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin12.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 12] = enyakin12[i];
}
//k
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*/////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin13.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 13] = enyakin13[i];
}
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*/////////////////////////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin14.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 14] = enyakin14[i];
}
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
/*/////////////////////////////////////////////////////*/
for (int a = 0; a < yedekisimliste.Count; a++)
{
for (int b = 0; b < yedekdurakliste.Count; b++)
{
ydifference = (yedekdurakliste[b].durakYkoordinat - yedekisimliste[a].isimYkoordinat);
xdifference = (yedekdurakliste[b].durakXkoordinat - yedekisimliste[a].isimXkoordinat);
distance = Math.Sqrt(Math.Abs((ydifference * ydifference) + (xdifference * xdifference)));
if (distance < sonuc)
{
sonuc = distance;
temp.durakKodu = yedekdurakliste[b].durakKodu;
temp.durakYkoordinat = yedekdurakliste[b].durakYkoordinat;
temp.durakXkoordinat = yedekdurakliste[b].durakXkoordinat;
} //end if
} //end inner for
enyakin15.Add(temp.durakKodu);
gerceklisteX.Add(temp.durakXkoordinat);
gerceklisteY.Add(temp.durakYkoordinat);
Durak ahmet;
ahmet = yedekdurakliste.Find(I => I.durakKodu == temp.durakKodu);
yedekdurakliste.Remove(ahmet);
sonuc = 1000.0;
}//end medium for
//Enyakin 1. duraklar 2 boyutlu diziye atandı.
for (int i = 0; i < 127; i++)
{
Array2D[i, 15] = enyakin15[i];
}
for (int i = 0; i < 127; i++)
{
yedekisimliste[i].isimXkoordinat = gerceklisteX[i];
yedekisimliste[i].isimYkoordinat = gerceklisteY[i];
}
for (int v = 0; v < 127; v++)
{
for (int b = 0; b < 16; b++)
{
Console.Write(Array2D[v, b] + " ");
if (b == 15)
{
Console.WriteLine();
}
}
}
Console.ReadLine();
} //end Main
public List <Amostra_Paciente> CarregaDados()
{
DataSet result;
List <Amostra_Paciente> amostras = new List <Amostra_Paciente>();
using (OpenFileDialog ofd = new OpenFileDialog()
{
Title = "Selecionar planilha de dados das amostras",
Filter = "Planilha Excel (*.XLSX)|*.XLSX|" + "All files (*.*)|*.*",
InitialDirectory = @"C:\",
ValidateNames = true,
CheckFileExists = true,
CheckPathExists = true,
ShowReadOnly = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
textBox10.Text = ofd.FileName;
FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
DataTable dt = result.Tables[0];
List <DataRow> colunas = new List <DataRow>(dt.Select());
foreach (DataRow coluna in colunas)
{
Amostra_Paciente amostra = new Amostra_Paciente(coluna.ItemArray);
amostras.Add(amostra);
}
reader.Close();
amostras.Sort((x, y) => x.idade.CompareTo(y.idade));
idade_minmax[0] = amostras.First().idade;
idade_minmax[1] = amostras.Last().idade;
amostras.Sort((x, y) => x.IMC.CompareTo(y.IMC));
IMC_minmax[0] = amostras.First().IMC;
IMC_minmax[1] = amostras.Last().IMC;
amostras.Sort((x, y) => x.glicose.CompareTo(y.glicose));
glicose_minmax[0] = amostras.First().glicose;
glicose_minmax[1] = amostras.Last().glicose;
amostras.Sort((x, y) => x.insulina.CompareTo(y.insulina));
insulina_minmax[0] = amostras.First().insulina;
insulina_minmax[1] = amostras.Last().insulina;
amostras.Sort((x, y) => x.HOMA.CompareTo(y.HOMA));
HOMA_minmax[0] = amostras.First().HOMA;
HOMA_minmax[1] = amostras.Last().HOMA;
amostras.Sort((x, y) => x.leptina.CompareTo(y.leptina));
leptina_minmax[0] = amostras.First().leptina;
leptina_minmax[1] = amostras.Last().leptina;
amostras.Sort((x, y) => x.adiponectina.CompareTo(y.adiponectina));
adiponectina_minmax[0] = amostras.First().adiponectina;
adiponectina_minmax[1] = amostras.Last().adiponectina;
amostras.Sort((x, y) => x.resistina.CompareTo(y.resistina));
resistina_minmax[0] = amostras.First().resistina;
resistina_minmax[1] = amostras.Last().resistina;
amostras.Sort((x, y) => x.MCP.CompareTo(y.MCP));
MCP_minmax[0] = amostras.First().MCP;
MCP_minmax[1] = amostras.Last().MCP;
foreach (Amostra_Paciente amostra in amostras)
{
amostra.NormalizarDados();
amostra.AtualizarArray();
}
}
}
return(amostras);
}
private static List <string> GetCategories(string basePath, string productSpecific = "")
{
string[] files = { @"\pizza_ingredienten.xlsx", @"\Overige producten.xlsx" };
List <string> result = new List <string>();
foreach (string file in files)
{
using (FileStream stream = File.OpenRead(basePath + file))
using (IExcelDataReader dr = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
DataSet data = dr.AsDataSet();
var table = data.Tables[0];
int categoryColumn = -1;
int subCatColumn = -1;
int prodNameColumn = -1;
for (int rowCount = 0; rowCount < table.Rows.Count; rowCount++)
{
if (rowCount == 0)
{
for (int columnCount = 0; columnCount < table.Rows[rowCount].ItemArray.Length; columnCount++)
{
if (table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower() == "categorie")
{
categoryColumn = columnCount;
}
if (table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower() == "subcategorie")
{
subCatColumn = columnCount;
}
if (table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower() == "productnaam")
{
prodNameColumn = columnCount;
}
}
}
else
{
if (!string.IsNullOrEmpty(productSpecific) && table.Rows[rowCount].ItemArray[prodNameColumn].ToString().ToLower() == productSpecific)
{
var specificResult = new List <string>();
if (table.Rows[rowCount].ItemArray[categoryColumn].ToString().ToLower().IndexOfAny(new char[] { '&', ',' }) > 0)
{
specificResult.AddRange(table.Rows[rowCount].ItemArray[categoryColumn].ToString().Split(new char[] { '&', ',' }));
}
else
{
specificResult.Add(table.Rows[rowCount].ItemArray[categoryColumn].ToString());
}
if (table.Rows[rowCount].ItemArray[subCatColumn].ToString().ToLower().IndexOfAny(new char[] { '&', ',' }) > 0)
{
specificResult.AddRange(table.Rows[rowCount].ItemArray[subCatColumn].ToString().Split(new char[] { '&', ',' }));
}
else
{
specificResult.Add(table.Rows[rowCount].ItemArray[subCatColumn].ToString());
}
return(specificResult.Select(t => t.Trim()).Distinct().ToList());
}
else
{
if (table.Rows[rowCount].ItemArray[categoryColumn].ToString().ToLower().IndexOfAny(new char[] { '&', ',' }) > 0)
{
result.AddRange(table.Rows[rowCount].ItemArray[categoryColumn].ToString().Split(new char[] { '&', ',' }));
}
else
{
result.Add(table.Rows[rowCount].ItemArray[categoryColumn].ToString());
}
if (table.Rows[rowCount].ItemArray[subCatColumn].ToString().ToLower().IndexOfAny(new char[] { '&', ',' }) > 0)
{
result.AddRange(table.Rows[rowCount].ItemArray[subCatColumn].ToString().Split(new char[] { '&', ',' }));
}
else
{
result.Add(table.Rows[rowCount].ItemArray[subCatColumn].ToString());
}
}
}
}
}
}
result.AddRange(new List <string> {
"ingredient", "pizzabodem"
});
return(result.Select(t => t.Trim()).Distinct().ToList());
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
ro_FormulaHorasRecargo_Bus busformulas = new ro_FormulaHorasRecargo_Bus();
var formula_horas = busformulas.get_info(Convert.ToInt32(SessionFixed.IdEmpresa));
if (formula_horas == null)
{
return;
}
int cont = 0;
ro_empleado_info_list empleado_info_list = new ro_empleado_info_list();
ro_HorasProfesores_detLis_Info EmpleadoNovedadCargaMasiva_detLis_Info = new ro_HorasProfesores_detLis_Info();
List <ro_HorasProfesores_det_Info> lista_novedades = new List <ro_HorasProfesores_det_Info>();
ro_rubros_calculados_Bus bus_rubros_calculados = new ro_rubros_calculados_Bus();
var rubros_calculados = bus_rubros_calculados.get_info(Convert.ToInt32(SessionFixed.IdEmpresa));
ro_rubro_tipo_Info_list ro_rubro_tipo_Info_list = new ro_rubro_tipo_Info_list();
ro_jornada_Data odata_j = new ro_jornada_Data();
var lst_jornada = odata_j.get_list(Convert.ToInt32(SessionFixed.IdEmpresa), false);
double horas_mat = 0;
string jornada = "";
string IdRubro = "";
string cedula = "";
if (rubros_calculados == null)
{
return;
}
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont != 0)
{
cedula = reader.GetString(0);
IdRubro = Convert.ToString(reader.GetValue(3));
jornada = Convert.ToString(reader.GetValue(2));
ro_rubro_tipo_Info rubros = new ro_rubro_tipo_Info();
var empleado = empleado_info_list.get_list().Where(v => v.pe_cedulaRuc == cedula).FirstOrDefault();
if (empleado != null)
{
if (empleado.Valor_horas_matutino == null)
{
empleado.Valor_horas_matutino = 0;
}
if (empleado.Valor_horas_vespertina == null)
{
empleado.Valor_horas_vespertina = 0;
}
if (empleado.Valor_horas_brigada == null)
{
empleado.Valor_horas_brigada = 0;
}
if (empleado.Valor_hora_adicionales == null)
{
empleado.Valor_hora_adicionales = 0;
}
if (empleado.Valor_hora_control_salida == null)
{
empleado.Valor_hora_control_salida = 0;
}
rubros = ro_rubro_tipo_Info_list.get_list().FirstOrDefault(v => v.rub_codigo == IdRubro);
if (rubros != null)
{
ro_HorasProfesores_det_Info info = new ro_HorasProfesores_det_Info
{
NumHoras = Convert.ToDouble(reader.GetValue(4)),
pe_cedulaRuc = cedula,
pe_apellido = empleado.Empleado,
em_codigo = empleado.em_codigo,
IdSucursal = empleado.IdSucursal,
Secuencia = cont,
IdEmpleado = empleado.IdEmpleado,
IdRubro = rubros.IdRubro,
ru_descripcion = rubros.ru_descripcion,
};
if (info.IdRubro == "63")
{
}
if (info.IdRubro == rubros_calculados.IdRubro_horas_matutina)
{
info.ValorHora = Convert.ToDouble(empleado.Valor_horas_matutino);
}
if (info.IdRubro == rubros_calculados.IdRubro_horas_vespertina)
{
info.ValorHora = Convert.ToDouble(empleado.Valor_horas_vespertina);
}
if (info.IdRubro == rubros_calculados.IdRubro_horas_control_salida)
{
info.ValorHora = Convert.ToDouble(empleado.Valor_hora_control_salida);
}
if (info.IdRubro == rubros_calculados.IdRubro_horas_brigadas)
{
info.ValorHora = Convert.ToDouble(empleado.Valor_horas_brigada);
}
if (info.IdRubro == rubros_calculados.IdRubro_horas_adicionales)
{
info.ValorHora = Convert.ToDouble(empleado.Valor_hora_adicionales);
}
horas_mat = info.NumHoras;
info.Valor = Convert.ToDouble(info.ValorHora * info.NumHoras);
info.Secuencia = lista_novedades.Count() + 1;
if (jornada != null && jornada != "")
{
info.IdJornada = lst_jornada.Where(v => v.codigo == jornada).FirstOrDefault().IdJornada;
}
if (info.Valor > 0)
{
lista_novedades.Add(info);
}
}
}
}
cont++;
}
}
foreach (var item in empleado_info_list.get_list())
{
double mat_mas_ves = lista_novedades.Where(v => v.IdEmpleado == item.IdEmpleado && (v.IdRubro == rubros_calculados.IdRubro_horas_matutina || v.IdRubro == rubros_calculados.IdRubro_horas_vespertina)).Sum(v => v.NumHoras);
#region horas recargo
if ((mat_mas_ves) * (formula_horas.Dividendo / formula_horas.Divisor) > 0)
{
if (rubros_calculados.IdRubro_horas_recargo != null)
{
var rubros = ro_rubro_tipo_Info_list.get_list().FirstOrDefault(v => v.IdRubro == rubros_calculados.IdRubro_horas_recargo);
if (rubros != null)
{
ro_HorasProfesores_det_Info info = new ro_HorasProfesores_det_Info
{
pe_cedulaRuc = item.pe_cedulaRuc,
pe_apellido = item.Empleado,
IdSucursal = item.IdSucursal,
em_codigo = item.em_codigo,
Secuencia = cont,
IdEmpleado = item.IdEmpleado,
IdRubro = rubros_calculados.IdRubro_horas_recargo,
ru_descripcion = rubros.ru_descripcion
};
info.ValorHora = 1.32;
info.NumHoras = (Math.Round(Convert.ToDouble((mat_mas_ves) * (formula_horas.Dividendo / formula_horas.Divisor))) - 160);
info.Valor = 1.32 * info.NumHoras;
info.Secuencia = lista_novedades.Count() + 1;
if (info.Valor > 0)
{
lista_novedades.Add(info);
}
}
}
}
#endregion
EmpleadoNovedadCargaMasiva_detLis_Info.set_list(lista_novedades);
}
}
private static List <Product> GetIngredients(string pathToFile, bool lookForSauce, string productName = "")
{
List <Product> result = new List <Product>();
List <Product> ProductSpecificResult = new List <Product>();
using (FileStream stream = File.OpenRead(pathToFile))
using (var dr = pathToFile.Contains(".csv") ? ExcelReaderFactory.CreateCsvReader(stream) : ExcelReaderFactory.CreateOpenXmlReader(stream))
{
int productColumn = -1;
int nameColumn = -1;
int amountColumn = -1;
int sauceColumn = -1;
int priceColumn = -1;
DataSet data = dr.AsDataSet();
var table = data.Tables[0];
for (int rowCount = 0; rowCount < table.Rows.Count; rowCount++)
{
if (rowCount == 0 && !string.IsNullOrEmpty(productName))
{
for (int columnCount = 0; columnCount < table.Rows[rowCount].ItemArray.Length; columnCount++)
{
string columnHeader = table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower();
if (columnHeader.ToLower().Contains("ingredientnaam"))
{
nameColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("aantalkeer_ingredient"))
{
amountColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("productnaam"))
{
productColumn = columnCount;
}
else if (columnHeader.ToLower().Contains("pizzasaus_standaard"))
{
sauceColumn = columnCount;
}
if (columnHeader.ToLower().Contains("price"))
{
priceColumn = columnCount;
}
}
}
else if (rowCount == 0 && string.IsNullOrEmpty(productName))
{
for (int columnCount = 0; columnCount < table.Rows[rowCount].ItemArray.Length; columnCount++)
{
string columnHeader = table.Rows[rowCount].ItemArray[columnCount].ToString().ToLower();
if (columnHeader.ToLower().Contains("ingredient"))
{
nameColumn = columnCount;
}
if (columnHeader.ToLower().Contains("price"))
{
priceColumn = columnCount;
}
if (columnHeader.ToLower().Contains("pizzasaus_standaard"))
{
sauceColumn = columnCount;
}
}
}
else
{
if (!string.IsNullOrEmpty(productName) && table.Rows[rowCount].ItemArray[productColumn].ToString().ToLower() == productName)
{
ProductSpecificResult.Add(new Product
{
Name = table.Rows[rowCount].ItemArray[nameColumn].ToString().ToLower(),
amount = Convert.ToInt32(table.Rows[rowCount].ItemArray[amountColumn].ToString().ToLower()),
Categories = new List <string> {
"ingredient"
}
});
ProductSpecificResult.Add(new Product
{
Name = table.Rows[rowCount].ItemArray[sauceColumn].ToString().ToLower(),
amount = 1,
Categories = new List <string> {
"ingredient"
}
});
}
else
{
result.Add(new Product
{
Name = table.Rows[rowCount].ItemArray[nameColumn].ToString().ToLower(),
Categories = new List <string> {
"ingredient"
},
Price = priceColumn < 0 ? "" : table.Rows[rowCount].ItemArray[priceColumn].ToString().ToLower()
});
}
if (lookForSauce)
{
result.Add(new Product
{
Name = table.Rows[rowCount].ItemArray[sauceColumn].ToString().ToLower(),
Categories = new List <string> {
"ingredient"
}
});
}
}
}
}
if (!string.IsNullOrEmpty(productName))
{
return(ProductSpecificResult.DistinctBy(p => p.Name).ToList());
}
return(result.DistinctBy(p => p.Name).ToList());
}
protected override void Process()
{
int vendorID_NL;
int vendorID_BE;
int vendorID_USA;
var config = GetConfiguration();
if (config.AppSettings.Settings["SennheiserVendorID_NL"] == null)
{
throw new Exception("SennheiserVendorID_NL not set in config for Sennheiser Price Import");
}
if (config.AppSettings.Settings["SennheiserVendorID_BE"] == null)
{
throw new Exception("SennheiserVendorID_BE not set in config for Sennheiser Price Import");
}
if (config.AppSettings.Settings["SennheiserVendorID_USA"] == null)
{
throw new Exception("SennheiserVendorID_UAS not set in config for Sennheiser Price Import");
}
vendorID_NL = int.Parse(config.AppSettings.Settings["SennheiserVendorID_NL"].Value);
vendorID_BE = int.Parse(config.AppSettings.Settings["SennheiserVendorID_BE"].Value);
vendorID_USA = int.Parse(config.AppSettings.Settings["SennheiserVendorID_USA"].Value);
try
{
using (var unit = GetUnitOfWork())
{
var path = config.AppSettings.Settings["SennheiserBasePath"].Value;
if (!Directory.Exists(path))
{
throw new NullReferenceException("The directory doesn't exist for SennheiserBasePath");
}
#region NL
log.Debug("Check for NL pricefile");
var pricePath = Path.Combine(path, config.AppSettings.Settings["SennheiserPriceFilesPath"].Value);
//Handling NL Excel file
config.AppSettings.Settings["ExcelFileNL"].Value.Split(',').ForEach((fileNameNL, idx) =>
{
//string fileNameNL = config.AppSettings.Settings["ExcelFileNL"].Value;
var excelFilePath_NL = Path.Combine(path, fileNameNL);
if (File.Exists(excelFilePath_NL))
{
FileStream stream_NL = File.Open(excelFilePath_NL, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader_NL = null;
var extensionNL = Path.GetExtension(excelFilePath_NL);
if (extensionNL == ".xls")
{
excelReader_NL = ExcelReaderFactory.CreateBinaryReader(stream_NL);
_taxRate = 19.00m;
_taxCalculation = 1.19m;
}
else
{
excelReader_NL = ExcelReaderFactory.CreateOpenXmlReader(stream_NL);
_taxRate = 19.00m;
_taxCalculation = 1.19m;
}
DataSet result_NL = excelReader_NL.AsDataSet();
ParseDocuments(unit, vendorID_NL, result_NL, true);
log.Debug("Save NL prices");
unit.Save();
stream_NL.Close();
var destpath = Path.Combine(pricePath, DateTime.Now.ToString("yyyyMMddmmss"));
if (!Directory.Exists(destpath))
{
Directory.CreateDirectory(destpath);
}
File.Move(excelFilePath_NL, Path.Combine(destpath, fileNameNL));
}
});
#endregion
#region BE
log.Debug("Check for BE pricefile");
// Handling BE Excel file
config.AppSettings.Settings["ExcelFileBE"].Value.Split(',').ForEach((fileNameBE, idx) =>
{
//string fileNameBE = config.AppSettings.Settings["ExcelFileBE"].Value;
var excelFilePath_BE = Path.Combine(path, fileNameBE);
if (File.Exists(excelFilePath_BE))
{
FileStream stream_BE = File.Open(excelFilePath_BE, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader_BE = null;
var extensionBE = Path.GetExtension(excelFilePath_BE);
if (extensionBE == ".xls")
{
excelReader_BE = ExcelReaderFactory.CreateBinaryReader(stream_BE);
_taxRate = 21.00m;
_taxCalculation = 1.21m;
}
else
{
excelReader_BE = ExcelReaderFactory.CreateOpenXmlReader(stream_BE);
_taxRate = 21.00m;
_taxCalculation = 1.21m;
}
DataSet result_BE = excelReader_BE.AsDataSet();
ParseDocuments(unit, vendorID_BE, result_BE, false);
log.Debug("Save BE prices");
unit.Save();
stream_BE.Close();
var destpath = Path.Combine(pricePath, DateTime.Now.ToString("yyyyMMddmmss"));
if (!Directory.Exists(destpath))
{
Directory.CreateDirectory(destpath);
}
File.Move(excelFilePath_BE, Path.Combine(destpath, fileNameBE));
}
});
#endregion
#region USA
log.Debug("Check for USA pricefile");
// Handling BE Excel file
config.AppSettings.Settings["ExcelFileUSA"].Value.Split(',').ForEach((fileNameUSA, idx) =>
{
// string fileNameUSA = config.AppSettings.Settings["ExcelFileUSA"].Value;
var excelFilePath_USA = Path.Combine(path, fileNameUSA);
if (File.Exists(excelFilePath_USA))
{
FileStream stream_USA = File.Open(excelFilePath_USA, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader_USA = null;
var extensionUSA = Path.GetExtension(excelFilePath_USA);
if (extensionUSA == ".xls")
{
excelReader_USA = ExcelReaderFactory.CreateBinaryReader(stream_USA);
_taxRate = 0;
_taxCalculation = 0;
}
else
{
excelReader_USA = ExcelReaderFactory.CreateOpenXmlReader(stream_USA);
_taxRate = 0;
_taxCalculation = 0;
}
DataSet result_USA = excelReader_USA.AsDataSet();
ParseDocuments(unit, vendorID_USA, result_USA, false);
log.Debug("Save USA prices");
unit.Save();
stream_USA.Close();
var destpath = Path.Combine(pricePath, DateTime.Now.ToString("yyyyMMddmmss"));
if (!Directory.Exists(destpath))
{
Directory.CreateDirectory(destpath);
}
File.Move(excelFilePath_USA, Path.Combine(destpath, fileNameUSA));
}
});
#endregion
}
}
catch (Exception e)
{
log.AuditError("Something went wrong during the price import", e);
}
}