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(); //} }