public SearchResult SearchContractsFromCustomer(int userId, string term, int pageSize, int page)
{
SearchResult result = new SearchResult();
result.TotalRecords = 0;
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_usuario", value: userId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_term", value: term, dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
parameters.Add("p_page_size", value: pageSize, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_page", value: page, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
parameters.Add("p_result_total", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var multi = connection.QueryMultiple("vnd.gx_customer_portal.px_search_contracts", parameters, commandType: CommandType.StoredProcedure);
// Read first cursor.
result.Payload = multi.Read <NormalContract, Customer, NormalContract>(
(contract, customer) =>
{
contract.Customer = customer;
return(contract);
}
).AsList();
// Read second cursor.
var row = (IDictionary <string, object>)multi.ReadFirst();
if (row["TOTAL"] != null)
{
int total;
int.TryParse(row["TOTAL"].ToString(), out total);
result.TotalRecords = total;
}
}
return(result);
}
public IEnumerable <SalesOrder> SalesOrdersFromContractItem(string contractNumber, int contractItemId)
{
IEnumerable <SalesOrder> salesOrders = new List <SalesOrder>();
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_nu_contrato_sap", value: contractNumber, dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_item_contrato", value: contractItemId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var lookup = new Dictionary <string, SalesOrder>();
connection.Query <SalesOrder, SalesOrderItem, Material, SalesOrder>(
"vnd.gx_customer_portal.px_contract_item_sales_orders",
(so, it, ma) =>
{
SalesOrder salesOrder;
if (!lookup.TryGetValue(so.Id, out salesOrder))
{
lookup.Add(so.Id, salesOrder = so);
}
if (salesOrder.SalesOrderItems == null)
{
salesOrder.SalesOrderItems = new List <SalesOrderItem>();
}
it.Material = ma;
salesOrder.SalesOrderItems.Add(it);
return(salesOrder);
},
param: parameters,
commandType: CommandType.StoredProcedure
).AsQueryable();
salesOrders = lookup.Values;
}
return(salesOrders);
}
public MasterContractItem UpdateContractMasterItem(MasterContractItem contractMasterItem)
{
MasterContractItem result;
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato_master_item", value: contractMasterItem.Id, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_cd_contrato_master", value: contractMasterItem.ContractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_cd_incoterms", value: (contractMasterItem.Incoterms == null ? "" : contractMasterItem.Incoterms.Id), dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_ds_region", value: (contractMasterItem.Region == null ? "" : contractMasterItem.Region.Name), dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
parameters.Add("p_cd_produto_sap", value: (contractMasterItem.Material == null ? "" : contractMasterItem.Material.Id), dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
parameters.Add("p_ic_price_list", value: (contractMasterItem.PriceListOption ? "S" : "N"), dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_usuario_alteracao", value: (contractMasterItem.ModifyUser == null ? 0 : contractMasterItem.ModifyUser.Id), dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
result = connection.Query <MasterContractItem, Incoterms, Material, Region, User, User, MasterContractItem>(
"vnd.gx_contract_master.pu_contrato_master_item",
(item, incoterms, material, region, creationUser, modifyUser) =>
{
item.Incoterms = incoterms;
item.Material = material;
item.Region = region;
item.CreationUser = creationUser;
item.ModifyUser = modifyUser;
return(item);
},
param: parameters,
commandType: CommandType.StoredProcedure
).AsList <MasterContractItem>().First();
}
return(result);
}
public Result SetQuantityOfContractsInMasterContract(int masterContractId, int quantityOfContracts)
{
Result result = new Result(success: true);
try
{
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato_master", value: masterContractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_qt_contratos", value: quantityOfContracts, dbType: OracleDbType.Decimal, direction: ParameterDirection.Input);
connection.Execute("vnd.gx_contract_master.pu_master_qt_contratos", param: parameters, commandType: CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
result.Success = false;
result.Messages.Add(ex.Message);
}
return(result);
}
public Result SetMasterContractSentToSAP(MasterContract masterContract)
{
Result result = new Result(success: true);
try
{
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato_master", value: masterContract.Id, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_cd_usuario", value: (masterContract.GeneratorUser == null ? 0 : masterContract.GeneratorUser.Id), dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
connection.Execute("vnd.gx_contract_master.pu_set_master_sent_to_sap", param: parameters, commandType: CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
result.Success = false;
result.Messages.Add(ex.Message);
}
return(result);
}
public Result SetNormalContractSentToSAP(NormalContract normalContract, string SAPNumber)
{
Result result = new Result(success: true);
try
{
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato", value: normalContract.Id, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_nu_contrato_sap", value: SAPNumber, dbType: OracleDbType.Char, direction: ParameterDirection.Input);
connection.Execute("vnd.gx_contract_master.pu_set_normal_sent_to_sap", param: parameters, commandType: CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
result.Success = false;
result.Messages.Add(ex.Message);
}
return(result);
}
public IEnumerable <Period> ListByContract(int contractId)
{
IEnumerable <Period> periods;
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato_master", value: contractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
periods = connection.Query <Period, User, User, Period>(
"vnd.gx_contract_master.px_cadencias",
(per, creationUser, modifyUser) =>
{
per.CreationUser = creationUser;
per.ModifyUser = modifyUser;
return(per);
},
param: parameters,
commandType: CommandType.StoredProcedure
);
}
return(periods);
}
public IEnumerable <NormalContractItem> DocumentFlow(int contractId)
{
IEnumerable <NormalContractItem> contractItems = new List <NormalContractItem>();
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato", value: contractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var lookup = new Dictionary <int, NormalContractItem>();
connection.Query <NormalContractItem, Material, SalesOrder, SalesOrderItem, NotaFiscal, NotaFiscalItem, NormalContractItem>(
"vnd.gx_customer_portal.px_document_flow",
(ci, ma, so, soi, nf, nfi) =>
{
NormalContractItem normalContractItem;
if (!lookup.TryGetValue(ci.Id, out normalContractItem))
{
lookup.Add(ci.Id, normalContractItem = ci);
}
if (normalContractItem.SalesOrders == null)
{
normalContractItem.SalesOrders = new List <SalesOrder>();
}
normalContractItem.Material = ma;
SalesOrder salesOrder;
if (so != null)
{
var salesOrders = from _so in normalContractItem.SalesOrders
where _so.Id == so.Id
select _so;
if (salesOrders.Count() == 0)
{
salesOrder = so;
normalContractItem.SalesOrders.Add(salesOrder);
}
else
{
salesOrder = salesOrders.First();
}
if (salesOrder.SalesOrderItems == null)
{
salesOrder.SalesOrderItems = new List <SalesOrderItem>();
}
SalesOrderItem salesOrderItem;
if (soi != null)
{
var salesOrderItems = from _soi in salesOrder.SalesOrderItems
where _soi.Id == soi.Id
select _soi;
if (salesOrderItems.Count() == 0)
{
salesOrderItem = soi;
salesOrder.SalesOrderItems.Add(salesOrderItem);
}
else
{
salesOrderItem = salesOrderItems.First();
}
NotaFiscal notaFiscal;
if (nf != null)
{
var notaFiscals = from _nf in salesOrderItem.NotaFiscals
where _nf.Id == nf.Id
select _nf;
if (notaFiscals.Count() == 0)
{
notaFiscal = nf;
salesOrderItem.NotaFiscals.Add(notaFiscal);
}
else
{
notaFiscal = notaFiscals.First();
}
NotaFiscalItem notaFiscalItem;
if (nfi != null)
{
var notaFiscalItems = from _nfi in notaFiscal.NotaFiscalItems
where _nfi.Id == nfi.Id
select _nfi;
if (notaFiscalItems.Count() == 0)
{
notaFiscalItem = nfi;
notaFiscal.NotaFiscalItems.Add(notaFiscalItem);
}
else
{
// Nota Fiscal Item is the leaf node.
// We could suppress this, unless we add a child node to it.
notaFiscalItem = notaFiscalItems.First();
}
}
}
}
}
return(normalContractItem);
},
param: parameters,
commandType: CommandType.StoredProcedure
).AsQueryable();
contractItems = lookup.Values;
}
return(contractItems);
}
public NormalContract GetNormalContractWithMaster(int normalContractId)
{
NormalContract result = null;
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato", value: normalContractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var multi = connection.QueryMultiple("vnd.gx_contract_master.px_normal_contract_with_master", param: parameters, commandType: CommandType.StoredProcedure);
IEnumerable <NormalContract> list = multi.Read(
new[]
{
typeof(NormalContract),
typeof(DocumentType),
typeof(SalesOrganization),
typeof(DistributionChannel),
typeof(SalesDivision),
typeof(User),
typeof(User),
typeof(Customer),
typeof(User),
typeof(NormalContractItem),
typeof(Material),
typeof(Incoterms),
typeof(Region)
},
(objects) =>
{
var lookup = new Dictionary <int, NormalContract>();
NormalContract ct;
var contract = (NormalContract)objects[0];
if (!lookup.TryGetValue(contract.Id, out ct))
{
lookup.Add(contract.Id, ct = contract);
ct.DocumentType = (DocumentType)objects[1];
ct.SalesOrganization = (SalesOrganization)objects[2];
ct.DistributionChannel = (DistributionChannel)objects[3];
ct.SalesDivision = (SalesDivision)objects[4];
ct.SalesSupervisor = (User)objects[5];
ct.SalesSupervisor.Superior = (User)objects[6];
ct.Customer = (Customer)objects[7];
ct.CreationUser = (User)objects[8];
}
if (ct.NormalContractItems == null)
{
ct.NormalContractItems = new List <NormalContractItem>();
}
NormalContractItem normalContractItem;
var it = (NormalContractItem)objects[9];
if (it != null)
{
var items = from mi in ct.NormalContractItems
where mi.Id == it.Id
select mi;
if (items.Count() == 0)
{
normalContractItem = it;
normalContractItem.Material = (Material)objects[10];
normalContractItem.Incoterms = (Incoterms)objects[11];
normalContractItem.Region = (Region)objects[12];
ct.NormalContractItems.Add(normalContractItem);
}
else
{
normalContractItem = items.First();
}
}
return(ct);
}).AsList();
if (list.Count() > 0)
{
result = list.First();
}
}
return(result);
}
public NormalContract GenerateNormalContract(NormalContract normalContract)
{
NormalContract result = null;
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_contrato_master", value: normalContract.MasterContractId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_cd_tipo_contrato", value: (normalContract.DocumentType == null ? "" : normalContract.DocumentType.Id), dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_sales_org", value: (normalContract.SalesOrganization == null ? "" : normalContract.SalesOrganization.Id), dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_distribution_channel", value: (normalContract.DistributionChannel == null ? "" : normalContract.DistributionChannel.Id), dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_sales_division", value: normalContract.SalesDivision.Id, dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_cd_usuario_venda", value: (normalContract.SalesSupervisor == null ? null : normalContract.SalesSupervisor.Id), dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_cd_cliente", value: (normalContract.Customer == null ? "" : normalContract.Customer.Id), dbType: OracleDbType.Varchar2, direction: ParameterDirection.Input);
parameters.Add("p_dt_inicio", value: normalContract.StartDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input);
parameters.Add("p_dt_fim", value: normalContract.EndDate, dbType: OracleDbType.Date, direction: ParameterDirection.Input);
parameters.Add("p_cd_usuario_inclusao", value: (normalContract.CreationUser == null ? null : normalContract.CreationUser.Id), dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
var multi = connection.QueryMultiple("vnd.gx_contract_master.pu_generate_contract", param: parameters, commandType: CommandType.StoredProcedure);
IEnumerable <NormalContract> list = multi.Read(
new[]
{
typeof(NormalContract),
typeof(DocumentType),
typeof(SalesOrganization),
typeof(DistributionChannel),
typeof(SalesDivision),
typeof(User),
typeof(User),
typeof(Customer),
typeof(User),
typeof(NormalContractItem),
//typeof(Incoterms),
typeof(Material)
},
(objects) =>
{
var lookup = new Dictionary <int, NormalContract>();
NormalContract ct;
var contract = (NormalContract)objects[0];
if (!lookup.TryGetValue(contract.Id, out ct))
{
lookup.Add(contract.Id, ct = contract);
ct.DocumentType = (DocumentType)objects[1];
ct.SalesOrganization = (SalesOrganization)objects[2];
ct.DistributionChannel = (DistributionChannel)objects[3];
ct.SalesDivision = (SalesDivision)objects[4];
ct.SalesSupervisor = (User)objects[5];
ct.SalesSupervisor.Superior = (User)objects[6];
ct.Customer = (Customer)objects[7];
ct.CreationUser = (User)objects[8];
}
if (ct.NormalContractItems == null)
{
ct.NormalContractItems = new List <NormalContractItem>();
}
NormalContractItem normalContractItem;
var it = (NormalContractItem)objects[9];
if (it != null)
{
var items = from mi in ct.NormalContractItems
where mi.Id == it.Id
select mi;
if (items.Count() == 0)
{
normalContractItem = it;
normalContractItem.Material = (Material)objects[10];
//normalContractItem.Incoterms = (Incoterms)objects[11];
ct.NormalContractItems.Add(normalContractItem);
}
else
{
normalContractItem = items.First();
}
}
return(ct);
}).AsList();
if (list.Count() > 0)
{
result = list.First();
}
}
return(result);
}
public List <MenuItem> GetMenu(string appSymbol, int userId, string rootUrl)
{
List <MenuItem> res = new List <MenuItem>();
using (IDbConnection connection = new OracleConnection(this.connString))
{
connection.Open();
var parameters = new OracleDynamicParameters();
parameters.Add("p_cd_usuario", value: userId, dbType: OracleDbType.Int32, direction: ParameterDirection.Input);
parameters.Add("p_sigla_sis", value: appSymbol, dbType: OracleDbType.Char, direction: ParameterDirection.Input);
parameters.Add("p_result", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);
List <MenuItem> list = new List <MenuItem>();
MenuItem deepSearcheableMenuItem = new MenuItem
{
items = list
};
connection.Query <MenuItem, MenuItem, MenuItem>(
"vnd.gx_contract_master.px_menu",
(item, childItem) =>
{
if (childItem.Id == 0)
{
//list.Add(item);
return(item);
}
if (list.Count() == 0)
{
list.Add(item);
return(item);
}
//var father = from i in list
// where i.Id == childItem.Id
// select i;
var matches = MenuItem.DepthFirstSearch(deepSearcheableMenuItem, t => t.Id == childItem.Id).ToList();
//var father = from i in list
// from j in i.Descendants()
// where j.Id == childItem.Id
// select j;
if (matches.Count() > 0)
{
// Father exists.
matches.First().items.Add(item);
}
else
{
list.Add(item);
}
return(item);
},
param: parameters,
commandType: CommandType.StoredProcedure
);
res = list;
}
return(res);
//try
// {
// int count = 1;
// int acaoBase = 0;
// int acaoSupAtual = 0;
// string url = String.Empty;
// conn.Open("Mosaic.ELO.Persistence.SecurityDAO.GetMenu");
// ProcedureParameters p = new ProcedureParameters();
// p.Add("P_CD_USUARIO", userId);
// p.Add("P_SIGLA_SIS", appSymbol);
// using (IDataReader rdr = conn.DbManager.ExecuteReaderProcedure("VND.GX_ELO_SECURITY.PX_MENU", p.getParametersArray(), "P_RETORNO"))
// {
// while (rdr.Read())
// {
// if (count == 1)
// {
// acaoBase = Converter.ToInt32(rdr["CD_ACAO"]);
// count++;
// continue;
// }
// url = String.Empty;
// if (Converter.ToInt32(rdr["CD_ACAO_SUPERIOR"]) > acaoBase)
// {
// acaoSupAtual = Converter.ToInt32(rdr["CD_ACAO_SUPERIOR"]);
// // É filho de alguém. Busca esse alguém na lista:
// Domain.MenuItem item = Busca(res, acaoSupAtual);
// if (item != null)
// {
// if (item.items == null)
// {
// item.items = new List<Domain.MenuItem>();
// }
// url = Converter.ToString(rdr["DS_ACAO"]);
// if (!String.IsNullOrEmpty(url))
// {
// url = rootUrl + Converter.ToString(rdr["DS_ACAO"]);
// }
// item.items.Add(new Domain.MenuItem()
// {
// id = Converter.ToInt32(rdr["CD_ACAO"]),
// text = Converter.ToString(rdr["DS_MENU"]),
// url = url
// });
// }
// else
// {
// // Não achou a ação pai.
// Exception exNotFound = new Exception(string.Concat("Não foi encontrada a ação pai: ", Converter.ToString(rdr["CD_ACAO_SUPERIOR"]), " para montar o menu."));
// throw exNotFound;
// }
// }
// else
// {
// // É órfão.
// if (Converter.ToInt32(rdr["CD_ACAO"]) > acaoBase)
// {
// url = Converter.ToString(rdr["DS_ACAO"]);
// if (!String.IsNullOrEmpty(url))
// {
// url = rootUrl + Converter.ToString(rdr["DS_ACAO"]);
// }
// res.Add(new Domain.MenuItem()
// {
// id = Converter.ToInt32(rdr["CD_ACAO"]),
// text = Converter.ToString(rdr["DS_MENU"]),
// url = url
// });
// }
// }
// }
// }
//}
//catch (Exception ex)
//{
// throw ex;
//}
//finally
//{
// conn.Close();
//}
}