public async Task <IEnumerable <Import_Product> > List(Import_Product model)
{
using (var connection = new SqlConnection(ConnectionString))
{
var result = connection.Query <
Common.Import_Product>
("PA_CON_AC_MTR_HEADER_AGREEMENT_GET",
param: new
{
P_PK_AC_TRADE_AGREEMENT = model.Pk_Ac_Trade_Agreement,
P_PK_CAT_TYPE_AGREEMENT = model.Pk_Cat_Type_Agreement,
P_PK_AC_CAT_PROVIDER = model.Pk_Ac_Cat_Provider,
P_CREATION_DATE = model.Creation_Date,
P_CREATION_USER = model.Creation_User,
P_MODIFICATION_DATE = model.Modification_Date,
P_MODIFICATION_USER = model.Modification_User,
P_NAME_AGREEMENT = model.Name_Agreement,
P_DESCRIPTION_AGREEMENT = model.Description_Agreement,
P_DATE_START = model.Date_Start,
P_DATE_FINISH = model.Date_Finish,
P_DATE_PROCESS = model.Date_Process,
P_DATE_REPROCESS = model.Date_Reprocess,
P_ALL_PRODUCTS = model.All_Products,
P_PROVIDER_NAME = model.Provider_Name,
P_ACTIVE = model.Active,
P_FK_STATUS_AGREEMENT = model.Fk_Status_Agreement,
P_FK_GLB_MTR_ORGANIZATION = model.Fk_Glb_Mtr_Organization,
P_MAX_AMOUNT = model.Max_Amount,
P_EMAIL = model.Email,
P_STATUS_OPTION = model.Status_Option
},
commandType: CommandType.StoredProcedure);
return(await Task.FromResult <IEnumerable <Import_Product> >(result.ToList()));
}
}
public async Task <IEnumerable <Import_Product> > List(Import_Product model)
{
using (var connection = new SqlConnection(ConnectionString))
{
var result = connection.Query <
Common.Import_Product>
("PA_PRO_GBL_WRK_VALIDATE_ERRORS",
param: new
{
P_PRODUCT_ID_ALIAS = model.Product_Id_Alias,
P_PRODUCT_NAME = model.Product_Name,
P_ID_CURRENCY = model.Id_Currency,
P_PRODUCT_AMOUNT = model.Product_Amount,
P_CREATION_USER = model.Creation_User,
P_PK_AC_TRADE_AGREEMENT = model.Pk_Ac_Trade_Agreement,
P_PK_GBL_WRK_AGREEMENT = model.Pk_Gbl_Wrk_Agreement,
P_ERROR = model.Error,
P_MESSAGE_ERROR = model.Message_Error,
P_IT_PROCESSED = model.It_Processed,
P_INVALID_AMOUNT = model.Invalid_Amount,
P_NOT_EXIST_PRODUCT = model.Not_Exist_Product,
P_DUPLICATE_PRODUCT_ALIAS = model.Duplicate_Product_Alias,
P_NOT_EXIST_ID_CURRENCY = model.Not_Exist_Id_Currency,
P_ACTIVE = model.Active
},
commandType: CommandType.StoredProcedure);
return(await Task.FromResult <IEnumerable <Import_Product> >(result.ToList()));
}
}
public async Task <ICollection <Import_Product> > ListCollection(Import_Product model)
{
using (var connection = new SqlConnection(ConnectionString))
{
var result = connection.Query <
Common.Import_Product>
("",
param: new
{
},
commandType: CommandType.StoredProcedure);
return(await Task.FromResult <ICollection <Common.Import_Product> >(result.ToList()));
}
}
public async Task <IEnumerable <Import_Product> > ListWorkTable(Import_Product model)
{
using (var connection = new SqlConnection(ConnectionString))
{
var result = connection.Query <
Common.Import_Product>
("PA_PRO_GBL_WRK_PROCESS_VALIDATION",
param: new
{
P_PK_AC_TRADE_AGREEMENT = model.Pk_Ac_Trade_Agreement,
},
commandType: CommandType.StoredProcedure);
return(await Task.FromResult <IEnumerable <Import_Product> >(result.ToList()));
}
}
public async Task <IEnumerable <Import_Product> > DeleteScalar(Import_Product model)
{
using (var connection = new SqlConnection(ConnectionString))
{
var result = connection.Query <
Common.Import_Product>
("PA_MAN_AC_MTR_AGREEMENT_HEADER_DELETE",
param: new
{
P_PK_AC_TRADE_AGREEMENT = model.Pk_Ac_Trade_Agreement,
P_CREATION_USER = model.Creation_User,
P_MODIFICATION_DATE = model.Modification_Date,
P_MODIFICATION_USER = model.Modification_User
},
commandType: CommandType.StoredProcedure);
return(await Task.FromResult <IEnumerable <Import_Product> >(result.ToList()));
}
}
public int readAsExcelFile(Import_Product model)
{
try
{
DataTable dataTable = new DataTable();
CultureInfo newCulture = CultureInfo.CreateSpecificCulture("en-US");
Thread.CurrentThread.CurrentUICulture = newCulture;
// Make current UI culture consistent with current culture.
Thread.CurrentThread.CurrentCulture = newCulture;
// var model_Line_Work = Enumerable.Empty<Common.Excel_File_Quotation>();
// var model_Material = Enumerable.Empty<Common.Excel_File_Material>();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(model.File_Path, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
foreach (var item in sheets)
{
if (item.Name.ToString() != "CODE")
{
//DataTable dataTable = new DataTable();
string relationshipId = item.Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
foreach (var header in dataTable.Columns)
{
if (header.ToString() == "Código")
{
dataTable.Columns[header.ToString()].ColumnName = "PRODUCT_ID_ALIAS";
}
else if (header.ToString() == "Nombre")
{
dataTable.Columns[header.ToString()].ColumnName = "PRODUCT_NAME";
}
else if (header.ToString() == "Moneda(COLONES, DOLARES)")
{
dataTable.Columns[header.ToString()].ColumnName = "ID_CURRENCY";
}
else if (header.ToString() == "Monto")
{
dataTable.Columns[header.ToString()].ColumnName = "PRODUCT_AMOUNT";
}
dataTable.AcceptChanges();
}
dataTable.Columns.Add("CREATION_USER");
dataTable.Columns.Add("PK_AC_TRADE_AGREEMENT");
var x = 0;
foreach (Row row in rows)
{
if (x != 0)
{
if (row.Descendants <Cell>().Count() > 2)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants <Cell>().ElementAt(i));
}
dataRow["CREATION_USER"] = model.Creation_User;
dataRow["PK_AC_TRADE_AGREEMENT"] = model.Pk_Ac_Trade_Agreement;
dataTable.Rows.Add(dataRow);
}
}
x++;
}
// buscamos la tabla de parametros
var repositoryParameter = new GblParameterRepository(ConnectionString);
var parameter = new Sevensoft.Mexpress.Backend.Common.GblParameter();
parameter.SEARCH_KEY = "TABLE_IMPORT_PRODUCT";
var result = repositoryParameter.Get(parameter).Result;
if (dataTable.Rows.Count == 0)
{
throw new NullReferenceException();
}
else
{
SqlBulkCopy bulkcopy = new SqlBulkCopy(ConnectionString);
bulkcopy.DestinationTableName = result.VALUE;
bulkcopy.WriteToServer(dataTable);
}
}
}
}
return(dataTable.Rows.Count);
}
catch (Exception ex)
{
throw ex;
}
}
