/// <summary>
/// Save Excel on computer and info on database
/// </summary>
/// <param name="fileImported">file need to be saved</param>
/// <param name="voyCode">Voyage Code</param>
/// <param name="path">path need to be saved to</param>
/// <param name="customerId">Customer Id</param>
/// <param name="userId">User Id</param>
/// <returns></returns>
private FileExcel SaveExcel(HttpPostedFileBase fileImported, string voyCode, string path, int customerId, string userName)
{
Logger.Info("SaveExcel voyCode=" + voyCode
+ " path=" + path
+ " customerId=" + customerId
+ " userName="******"SaveExcel Error: ", e);
throw e;
}
}
//protected override void ProcesarOrden(int idOrden)
//{
// this._parametroOrden = new AuthHeaderOrder()
// {
// CompanyID = this._conexion.CompanyID,
// MasterKey = this._conexion.MasterKey,
// OrderID = idOrden,
// ProducerID = this._conexion.ProducerID,
// Username = this._conexion.Username
// };
// //this._parametroOrden.OrderID = idOrden;
// AuthReturnOrder result = this.client.GetOrder(this._parametroOrden);
// Order orden = result.Order;
// Job[] jobs = result.Jobs;
// VentaDAO venta_dao = new VentaDAO();
// bool existeVenta = venta_dao.Buscar(orden.OrderID.ToString());
// if (existeVenta)
// {
// Console.WriteLine("La orden {0} ya existe", orden.OrderID.ToString());
// return;
// }
// ClienteDAO cliente_dao = new ClienteDAO();
// CtoCampoExtra cte = cliente_dao.BuscarClienteCampoExtra(orden.WebUserID.ToString());
// DateTime time = DateTime.Now;
// DateTime timeVencimiento = time.AddDays(7);
// #region Llenado de Venta
// Venta venta = new Venta()
// {
// Empresa = "LITO",
// Mov = "Factura Electronica",
// FechaEmision = new DateTime(time.Year, time.Month, time.Day),
// Concepto = $"SAAM {this._nombreTienda.ToUpper()}",
// Moneda = "Pesos",
// TipoCambio = 1.0,
// Usuario = "MTOVAR",
// Referencia = orden.OrderID.ToString(),
// OrdenCompra = "",
// Estatus = "SINAFECTAR",
// Cliente = cte != null ? cte.Clave : "16776",
// Almacen = "AL PT",
// Observaciones = $"SAAM {this._nombreTienda.ToUpper()}",
// Condicion = this.condiciones,
// Vencimiento = new DateTime(timeVencimiento.Year, timeVencimiento.Month, timeVencimiento.Day),
// Agente = this.agente,
// Sucursal = 0,
// SucursalOrigen = 0,
// Atencion = "",
// AtencionTelefono = "",
// Clase = "",
// Directo = true,
// OrdenID = orden.OrderID.ToString()
// };
// #endregion
// int id_venta = venta_dao.Insertar(venta);
// int x = 1;
// #region Llenado de VentaD
// foreach (Job job in jobs)
// {
// int _cantidad = job.Quantity;
// if (job.Records > 1)
// {
// _cantidad = job.Records;
// }
// VentaD ventad = new VentaD()
// {
// ID = id_venta,
// Renglon = x * 2048,
// RenglonID = x++,
// ABC = "2",
// Cantidad = _cantidad,
// Almacen = "AL PT",
// Articulo = job.SKU,
// Unidad = "pza",
// Precio = (job.TotalPrice/_cantidad),
// Impuesto1 = 16,
// DescripcionExtra = job.JobName,
// RenglonTipo = 'N'
// };
// venta_dao.InsertarDetalle(ventad);
// }
// #endregion
// #region Envio
// if (orden.ShippingPrice > 0)
// {
// VentaD detalleEnvio = new VentaD()
// {
// ID = id_venta,
// DescripcionExtra = "Gastos de Envio",
// ABC = "5",
// RenglonID = x,
// RenglonTipo = 'N',
// Renglon = x * 2048,
// Articulo = "EN",
// Precio = Math.Round(orden.ShippingPrice,2),
// Cantidad = 1,
// Impuesto1 = 16,
// Unidad = "Servicio",
// Almacen = "AL PT"
// };
// venta_dao.InsertarDetalle(detalleEnvio);
// }
// #endregion
//}
public void DescargarArchivos()
{
List <OrdenDTO> _ordenes = this.GetOrdenesConArchivos();
if (_ordenes.Count == 0)
{
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(String.Format("No hay ordenes en la tienda {0} para descargar PDFs", this._nombreTienda.ToUpper()));
Console.WriteLine("---------------------------------------------------------------------");
}
_ordenes.ForEach(orden =>
{
int x = Int32.Parse(orden.SKU.Substring(orden.SKU.Length - 2, 2));
string _ordenFolder = $"{this._workspace}\\{orden.OrdenId}";
orden.Imprimir();
FileExcel cfile = new FileExcel();
orden.CustomData.ForEach(custom => cfile.AgregarRow(custom.Name, custom.Value.Replace("<nextline>", " ")));
this.DownloadFile(orden.FilePdfURL, _ordenFolder, $"{orden.NombreArchivo}.pdf");
Row cabecera = new Row();
cabecera.Append(
cfile.ConstructCell("Valor", CellValues.String),
cfile.ConstructCell("Campo", CellValues.String));
cfile.SalvarExcel($"{_ordenFolder}\\{orden.NombreArchivo}.xlsx", this._nombreTienda, cabecera);
}
);
}
//public new void CambiarEstatusOrdenes(string ordenes)
//{
// base.CambiarEstatusOrdenes(ordenes);
//}
private void DescargaNormal(OrdenDTO orden)
{
string _ordenFolder = $"{ this._workspace}\\produccion\\{ this.fullMonthName}\\{ orden.OrdenId}";
Console.WriteLine(_ordenFolder);
orden.Imprimir();
if (orden.TipoProducto == "Blocks")
{
this.DownloadFile(orden.FilePdfURL, _ordenFolder, orden.NombreArchivo + ".pdf");
// Se descarga el Documento Excel
this.DownloadFile(orden.FileExcelURL, _ordenFolder, orden.NombreArchivo + ".xlsx");
FileExcel excel = new FileExcel();
// Se crea un nuevo Excel unicamente con la informacion de la primera columna (el archivo original es borrado)
excel.CrearArchivoPrimerColumna(_ordenFolder + "\\" + orden.NombreArchivo + ".xlsx");
}
else
{
XElement xelement = XElement.Load(orden.FileXMLURL);
string _urlFileTemplate = xelement.Element("Job").Element("Product").Element("ProductDetails").Element("ProductPreviewFile").Value;
if (_urlFileTemplate.Length != 0)
{
this.DownloadFile(_urlFileTemplate, _ordenFolder, orden.NombreArchivo + ".pdf");
Console.WriteLine($"URL ARCHIVO NO PERSONALIZADO {_urlFileTemplate}");
}
}
}
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.
}
DataSet result = FileExcel.AsDataSet();
FileExcel.IsFirstRowAsColumnNames = true;
listtemp = new List <string>();
foreach (System.Data.DataTable table in result.Tables)
{
for (int i = 2; i < table.Rows.Count; i++)
{
for (int j = 0; j < 5; j++)
{
listtemp.Add(table.Rows[i].ItemArray[j].ToString());
}
}
}
FileExcel.Close();
}
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.
}
DataSet result = FileExcel.AsDataSet();
FileExcel.IsFirstRowAsColumnNames = true;
//listtemp = new List<string>();
foreach (System.Data.DataTable table in result.Tables)
{
for (int i = START_POSITION_AT_ROW_READ_FROM_EXCEL; i < table.Rows.Count; i++)
{
GIAOVIEN temp = new GIAOVIEN();
temp.HoTen = table.Rows[i].ItemArray[0].ToString();
temp.NgaySinh = (DateTime)table.Rows[i].ItemArray[1];
temp.GioiTinh = table.Rows[i].ItemArray[2].ToString();
temp.DiaChi = table.Rows[i].ItemArray[3].ToString();
temp.Email = table.Rows[i].ItemArray[4].ToString();
for (int j = 0; j < _listMonHoc.Count; j++)
{
if (_listMonHoc[j].TENMONHOC.Equals(table.Rows[i].ItemArray[5].ToString()))
{
temp.MaMonHoc = _listMonHoc[j].MAMONHOC;
break;
}
}
temp.MaGiaoVien = "GV" + Convert.ToInt64(_giaoVienBus.LaySTTMaGiaoVienCuoiCung() + 1);
if (_giaoVienBus.Them(temp.MaGiaoVien, temp.HoTen, temp.DiaChi, temp.NgaySinh, temp.Email, temp.GioiTinh, temp.MaMonHoc) == 0)
{
MessageBox.Show("Giáo viên: " + temp.HoTen + " dạy môn không nằm trong danh sách quy định!", "Lỗi");
}
else
{
_listGiaoVien.Add(temp);
}
}
}
FileExcel.Close();
hienThiDanhSachGiaoVienTrenGridView();
MessageBox.Show("Mở tập tin excel thành công!", "Thông báo");
}
public void AgregarFila(List <string> row, bool color)
{
FileExcel fileExcel = new FileExcel();
fileExcel.celdas = row;
if (color)
{
fileExcel.color = Color.DarkGray;
fileExcel.colorFont = Color.White;
}
contenido.Add(fileExcel);
}
public void DescargarArchivos()
{
List <OrdenDTO> _ordenes = this.GetOrdenesConArchivos();
if (_ordenes.Count == 0)
{
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(String.Format("No hay ordenes en la tienda {0} para descargar PDFs", this._nombreTienda.ToUpper()));
Console.WriteLine("---------------------------------------------------------------------");
}
_ordenes.ForEach(orden =>
{
string _ordenFolder = $"{ this._workspace}\\{ orden.OrdenId}";
Console.WriteLine(_ordenFolder);
orden.Imprimir();
FileExcel cfile = new FileExcel();
try
{
orden.CustomData.ForEach(x => cfile.AgregarRow(x.Name, x.Value));
string urlLogoDistribuidor = orden.CustomData
.Where(x => x.Name.Contains("Logo"))
.Select(x => x.Value).FirstOrDefault();
string extension = Path.GetExtension(urlLogoDistribuidor);
this.DownloadFile(urlLogoDistribuidor, _ordenFolder, $"{orden.NombreArchivo}{extension}");
Row cabecera = new Row();
cabecera.Append(
cfile.ConstructCell("Valor", CellValues.String),
cfile.ConstructCell("Campo", CellValues.String)
);
cfile.SalvarExcel($"{_ordenFolder}\\{orden.NombreArchivo}.xlsx", "Michelin", cabecera);
}
catch (Exception e)
{
Console.WriteLine("No se pudo crear el archivo excel");
}
this.DownloadFile(orden.FilePdfURL, _ordenFolder, $"{orden.NombreArchivo}.pdf");
});
}
public void DescargaEspecial(OrdenDTO orden)
{
string _ordenFolder = $"{ this._workspace}\\produccion\\{ this.fullMonthName}\\{ orden.OrdenId}";
if (Directory.Exists(_ordenFolder))
{
return;
}
Console.WriteLine(_ordenFolder);
orden.Imprimir();
if (orden.TipoProducto == "Blocks")
{
this.DownloadFile(orden.FilePdfURL, _ordenFolder, orden.NombreArchivo + ".pdf");
FileExcel excel = new FileExcel();
orden.CustomData.ForEach(data =>
{
excel.AgregarRow(data.Name, data.Value.Replace("<nextline>", " "));
Console.WriteLine($"{data.Name} {data.Value}");
});
excel.SalvarExcel(_ordenFolder + "\\" + orden.NombreArchivo + ".xlsx", "Kumon");
}
}
///
/// <summary>
/// Update File Excel's status stored in database
/// </summary>
/// <param name="file">File need to update its status</param>
/// <param name="status">status which file need to change into</param>
/// <returns></returns>
///
public bool UpdateStatusExcelFile(FileExcel file, bool status = false)
{
Logger.Info("UpdateStatusExcelFile status=" + status);
try
{
file.IsDone = status;
var entity = _fileExcelRepository.Update(file);
if (entity == null)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception e)
{
Logger.Error("UpdateStatusExcelFile Error: ", e);
throw e;
}
}
/// <summary>
/// Save and read file Excel
/// </summary>
/// <param name="path"></param>
/// <param name="fileImported"></param>
/// <param name="voyCode"></param>
/// <param name="partnerId"></param>
/// <param name="userName"></param>
/// <param name="Continue"></param>
/// <returns></returns>
private int[] SaveAndReadFile(string path, HttpPostedFileBase fileImported, string voyCode, int partnerId, string userName, int Continue)
{
int[] result = new int[2];
//Check if file name already existed
if (fw.Exist == true || fw.Exists(path))
{
result[0] = (int)ImportedFileState.Existed;//Existed
return(result);
}
//Save file to computer and database
FileExcel fileExcel = SaveExcel(fileImported, voyCode, path, partnerId, userName);
//Read Excel File's content
List <ExcelDetail> lstExcelDetail = ReadFile(fileImported, fileExcel.Id, Continue);
if (lstExcelDetail == null)
{
result[0] = (int)ImportedFileState.ContentSizeWrong;//Content size wrong
return(result);
}
fileExcel.RowTotal = lstExcelDetail.Count;
_fileExcelRepository.Update(fileExcel);
//Check and execute saving file's content
if (!SaveExcelDetail(lstExcelDetail, voyCode, partnerId))
{
result[0] = (int)ImportedFileState.NotFinished;//Not finished
return(result);
}
else
{
fileExcel.IsDone = true; //Already saved file's content
_fileExcelRepository.Update(fileExcel);
result[0] = (int)ImportedFileState.Done; //Not finished
result[1] = fileExcel.Id;
return(result);
}
}
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;
}
}
}
}
