public IHttpActionResult GetDataToExport()
{
try
{
using (BDRAEntities db = new BDRAEntities()) {
List <DataStructureForFilePN> lsDataPN = new List <DataStructureForFilePN>();
var lsPrReqeust = db.STRPRC_GET_DATA_TO_EXPORT_FILE_PN();
foreach (var dtPrRequest in lsPrReqeust)
{
DataStructureForFilePN dataPN = new DataStructureForFilePN();
dataPN.consecutivo = dtPrRequest.Consecutivo;
dataPN.fechaRegistro = dtPrRequest.Fecha_de_registro;
dataPN.cliente = dtPrRequest.Cliente;
dataPN.email = dtPrRequest.Email;
dataPN.celular = dtPrRequest.Celular;
dataPN.ciudad = dtPrRequest.Ciudad;
dataPN.estado = dtPrRequest.Estado;
dataPN.lineaDeVehiculo = dtPrRequest.Linea_del_vehiculo;
dataPN.canalPrimario = dtPrRequest.Canal_primario;
dataPN.canalSecundario = dtPrRequest.Canal_secundario;
dataPN.observaciones = dtPrRequest.Observaciones;
dataPN.gerenteDeCuenta = dtPrRequest.Gerente_de_cuenta;
lsDataPN.Add(dataPN);
}
return(Ok(lsDataPN));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult getAccountManager()
{
try
{
using (BDRAEntities db = new BDRAEntities()) {
var AV_VILLAS_NIT = "860035827";
var branch = db.branch.Where(br => br.ally_document == AV_VILLAS_NIT && br.bra_isMain == true)
.Select(br => new BranchViewModel {
id = br.bra_id, name = br.bra_name
}).FirstOrDefault();
var lsAccountManager = db.Contact.Where(cnt => cnt.bra_id == branch.id)
.Select(
cnt => new ContactViewModel {
id = cnt.cnt_id,
name = cnt.cnt_name,
lastName = (cnt.cnt_lastName != null)?cnt.cnt_lastName:"",
state = cnt.cnt_state
})
.ToList().OrderBy(cnt => cnt.name);
return(Ok(lsAccountManager));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
throw;
}
}
public void createClients(List <ClientViewModel> lsClients)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
foreach (var oClient in lsClients)
{
Client cli = new Client();
cli.cli_document = oClient.id;
cli.kod_id = oClient.kindOfDocument.id;
cli.cli_name = oClient.name;
cli.cnl_id = oClient.canal.id;
cli.ea_id = oClient.economicActivity.id;
cli.cty_id = oClient.city.id;
cli.cli_registrationDate = DateTime.Now;
db.Client.Add(cli);
db.SaveChanges();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
throw ex;
}
}
public IHttpActionResult Get()
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lsPreClients = db.PreClient
.Where(pre => pre.preCli_state == true)
.Select(
pre => new PreClientViewModel {
idPreClient = pre.preCli_id,
id = pre.preCli_document,
kindOfDocument = new KindOfDocumentViewModel {
id = pre.kod_id, description = pre.kindOfDocument.kod_description
},
name = pre.preCli_name,
lastName = pre.preCli_lastName,
cellPhone = pre.preCli_cellPhone,
phone = pre.preCli_phone,
email = pre.preCli_email,
registrationDate = pre.preCli_registrationDate,
user = new UserViewModel {
id = pre.usu_document, name = pre.users.usu_name, lastName = pre.users.usu_lastName
}
}
).ToList();
return(Ok(lsPreClients));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IHttpActionResult addContactsByClient(List <ContactViewModel> lsContacts)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
if (lsContacts.Count > 0)
{
foreach (var oContact in lsContacts)
{
Contact bdContact = new Contact();
bdContact.cnt_name = oContact.name;
bdContact.cnt_lastName = oContact.lastName;
bdContact.cnt_email = oContact.email;
bdContact.cnt_phone = oContact.phone;
bdContact.cnt_cellPhone = oContact.cellPhone;
bdContact.cnt_adress = oContact.adress;
bdContact.jtcl_id = oContact.jobTitle.id;
bdContact.bra_id = oContact.branch.id;
db.Contact.Add(bdContact);
db.SaveChanges();
}
}
}
return(Ok(lsContacts));
}
catch (Exception e)
{
return(BadRequest(e.StackTrace));
}
}
public IHttpActionResult UpdatePreClient(PreClientViewModel pPreClient)
{
try
{
ResponseViewModel rta = new ResponseViewModel();
using (BDRAEntities db = new BDRAEntities())
{
var preClientBd = db.PreClient.Where(p => p.preCli_id == pPreClient.idPreClient).FirstOrDefault();
preClientBd.preCli_document = pPreClient.id;
preClientBd.kod_id = pPreClient.kindOfDocument.id;
preClientBd.preCli_name = pPreClient.name;
preClientBd.preCli_lastName = pPreClient.lastName;
preClientBd.preCli_cellPhone = pPreClient.cellPhone;
preClientBd.preCli_phone = pPreClient.phone;
preClientBd.preCli_email = pPreClient.email;
preClientBd.cty_id = pPreClient.city.id;
db.SaveChanges();
rta.response = true;
rta.message = "Se ha actualizado el pre-cliente " + pPreClient.idPreClient;
return(Ok(rta));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public List <UserViewModel> Get(string area_name)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var area = db.areas.Where(a => a.area_description.ToUpper() == area_name.ToUpper()).FirstOrDefault();
var jobTitles = db.jobTitle.Where(jt => jt.area_id == area.area_id).ToList();
List <UserViewModel> lsUserByArea = new List <UserViewModel>();
foreach (var jobTitle in jobTitles)
{
var lsUsers = db.users.Where(u => u.jt_id == jobTitle.jt_id)
.Select(u => new UserViewModel {
id = u.usu_document, name = u.usu_name, lastName = u.usu_lastName
})
.ToList();
lsUserByArea.AddRange(lsUsers);
}
return(lsUserByArea.OrderBy(u => u.name).ToList());
}
}
catch (Exception ex)
{
throw;
}
}
private List <PreRequestObservationViewModel> GetObservationsByPreRequest(int pPreRequest_id)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lsObservationDB = db.PreRequest_Observation
.Where(obs => obs.preReq_id == pPreRequest_id)
.Select(obs => new PreRequestObservationViewModel
{
id = obs.prObs_id,
observation = obs.prObs_observation,
user = new UserViewModel
{
id = obs.usu_document,
name = obs.users.usu_name,
lastName = obs.users.usu_lastName
},
registrationDate = obs.prObs_registrationDate
}).ToList();
return(lsObservationDB);
}
}
catch (Exception ex)
{
return(null);
}
}
public IHttpActionResult getParentStates(string pDescription)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
List <StateViewModel> lsParentStates = new List <StateViewModel>();
if (pDescription.ToUpper() == "TODOS")
{
lsParentStates = db.states.Where(st => st.sta_parentState == null && st.stateGroup.stGrp_id != 2 && st.stateGroup.stGrp_id != 4)
.Select(st => new StateViewModel {
id = st.sta_id, description = st.sta_description
})
.ToList();
}
else
{
var grpState = db.stateGroup.Where(sg => sg.stGrp_description.ToUpper() == pDescription.ToUpper()).FirstOrDefault();
lsParentStates = db.states.Where(st => st.sta_parentState == null && st.stGrp_id == grpState.stGrp_id)
.Select(st => new StateViewModel {
id = st.sta_id, description = st.sta_description
})
.ToList();
}
return(Ok(lsParentStates));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
public IHttpActionResult getStatesByParent(int parentState_id)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
List <StateViewModel> lsChildStates = new List <StateViewModel>();
if (parentState_id == 0)
{
lsChildStates = db.states.Where(st => st.sta_parentState != null && st.stateGroup.stGrp_id != 2)
.Select(st => new StateViewModel {
id = st.sta_id, description = st.sta_description, parentState = st.sta_parentState
})
.ToList();
}
else
{
lsChildStates = db.states.Where(st => st.sta_parentState == parentState_id)
.Select(st => new StateViewModel {
id = st.sta_id, description = st.sta_description, parentState = st.sta_parentState
})
.ToList();
}
return(Ok(lsChildStates));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
public IHttpActionResult getMainBranchByClient(string pClient_id)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var branch = db.branch.Where(b => b.cli_document == pClient_id && b.bra_isMain == true)
.Select(b => new BranchViewModel
{
id = b.bra_id,
name = b.bra_name,
adress = b.bra_adress,
phone = b.bra_phone,
client = new ClientViewModel {
id = b.Client.cli_document, name = b.Client.cli_name, lastName = b.Client.cli_lastName
}
}).FirstOrDefault();
return(Ok(branch));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
throw;
}
}
public bool existsClient(string client_id)
{
bool rta = false;
try
{
using (BDRAEntities db = new BDRAEntities())
{
var cli = db.Client.Where(cl => cl.cli_document == client_id).FirstOrDefault();
if (cli != null)
{
rta = true;
}
else
{
rta = false;
}
}
}
catch (Exception)
{
rta = false;
throw;
}
return(rta);
}
public List <KindOfDocumentViewModel> Get()
{
using (BDRAEntities db = new BDRAEntities()) {
var lsKindOfDocuments = db.kindOfDocument
.Select(k => new KindOfDocumentViewModel {
id = k.kod_id, description = k.kod_description
})
.ToList();
return(lsKindOfDocuments);
}
}
public List <DepartmentViewModel> Get()
{
using (BDRAEntities dbra = new BDRAEntities()) {
var lsDepartments = dbra.Departments
.Select(dpt => new DepartmentViewModel {
id = dpt.dpt_id, name = dpt.dpt_name, countyId = dpt.ctry_id
})
.ToList();
return(lsDepartments);
}
}
public List <CityViewModel> Get()
{
using (BDRAEntities db = new BDRAEntities()) {
var lsCities = db.Cities
.Select(ct => new CityViewModel {
id = ct.cty_id, name = ct.cty_name, departmentId = ct.dpt_id
})
.ToList();
return(lsCities);
}
}
public PreClient GetPreClientBD(PreClientViewModel pPreClient)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var PreClientBD = new PreClient();
if ((pPreClient.email != null && pPreClient.email != "") && (pPreClient.cellPhone != null && pPreClient.cellPhone != ""))
{
PreClientBD = db.PreClient.Where(p => p.preCli_email == pPreClient.email && p.preCli_cellPhone == pPreClient.cellPhone).FirstOrDefault();
if (PreClientBD == null)
{
if (pPreClient.cellPhone != null && pPreClient.cellPhone != "")
{
PreClientBD = db.PreClient.Where(p => p.preCli_cellPhone == pPreClient.cellPhone).FirstOrDefault();
}
else if (pPreClient.email != null && pPreClient.email != "")
{
PreClientBD = db.PreClient.Where(p => p.preCli_email == pPreClient.email).FirstOrDefault();
}
}
}
//Se deben validar los casos excluyentes si viene el celular y no el coreo y viceversa
else if (pPreClient.cellPhone != null && pPreClient.cellPhone != "")
{
PreClientBD = db.PreClient.Where(p => p.preCli_cellPhone == pPreClient.cellPhone).FirstOrDefault();
}
else if (pPreClient.email != null && pPreClient.email != "")
{
PreClientBD = db.PreClient.Where(p => p.preCli_email == pPreClient.email).FirstOrDefault();
}
if (PreClientBD != null)
{
return(PreClientBD);
}
else
{
return(null);
}
}
}
catch (Exception ex)
{
return(null);
}
}
private bool InsertObservationInDB(PreRequest_Observation pPbservation)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
db.PreRequest_Observation.Add(pPbservation);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
public List <ContactViewModel> getContactsByClient(string pClient_id)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var countBranch = db.branch.Where(b => b.cli_document == pClient_id).Count();
var lsContacts = new List <ContactViewModel>();
if (countBranch > 0)
{
var branch = db.branch.Where(b => b.cli_document == pClient_id).Select(b => new BranchViewModel {
id = b.bra_id, name = b.bra_name
}).FirstOrDefault();
var lsTmp = db.Contact.Where(ct => ct.bra_id == branch.id).Count();
if (lsTmp > 0)
{
lsContacts = db.Contact.Where(ct => ct.bra_id == branch.id)
.Select(ct =>
new ContactViewModel
{
id = ct.cnt_id,
name = ct.cnt_name,
lastName = ct.cnt_lastName,
phone = ct.cnt_phone,
cellPhone = ct.cnt_cellPhone,
email = ct.cnt_email,
jobTitle = new JobTitleViewModel {
id = ct.JobTitlesClient.jtcl_id, description = ct.JobTitlesClient.jtcl_description
},
adress = ct.cnt_adress,
branch = new BranchViewModel {
id = ct.branch.bra_id, name = ct.branch.bra_name
}
}
).ToList();
}
}
return(lsContacts);
}
}
catch (Exception e)
{
throw;
}
}
public IHttpActionResult UpdatePreRequest(PreRequestViewModel pPreRequest)
{
try
{
ResponseViewModel rta = new ResponseViewModel();
using (BDRAEntities db = new BDRAEntities())
{
//TODO: Update client
var preRequestBD = db.PreRequest.Where(p => p.preReq_id == pPreRequest.id).FirstOrDefault();
preRequestBD.firstCanal_id = pPreRequest.firstCanal.id;
preRequestBD.secondCanal_id = pPreRequest.secondCanal.id;
preRequestBD.sta_id = pPreRequest.stateRequest.id;
if (pPreRequest.vehicleModel.id != null && pPreRequest.vehicleModel.id != 0)
{
preRequestBD.vehMdl_id = pPreRequest.vehicleModel.id;
}
else
{
var vechicleModel = VehicleModelController.SetDataToVehicleModel(pPreRequest.vehicleModel);
var vehicleModelDB = VehicleModelController.CreateVehicleModelInDB(vechicleModel);
preRequestBD.vehMdl_id = vehicleModelDB.id;
}
preRequestBD.preCli_id = pPreRequest.preClient.idPreClient;
db.SaveChanges();
if (pPreRequest.lsObservation[0].observation != "")
{
int idPreRequest = int.Parse(pPreRequest.id.ToString());
PreRequest_Observation observation = this.SetDataToObservation(idPreRequest, pPreRequest.lsObservation[0].observation, pPreRequest.user.id);
this.InsertObservationInDB(observation);
}
rta.response = true;
rta.message = "Se ha actualizado la solicitud: " + pPreRequest.id;
return(Ok(rta));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public bool CreatePreClientInBD(PreClient pPreClient)
{
try
{
PreClient oPreClientBd = new PreClient();
using (BDRAEntities db = new BDRAEntities())
{
db.PreClient.Add(pPreClient);
db.SaveChanges();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
public PreRequest GetTheLastPreRequestByPreClient(PreClient pPreClient)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lastRequest = db.PreRequest.Where(p => p.preCli_id == pPreClient.preCli_id)
.OrderByDescending(p => p.preReq_registrationDate)
.FirstOrDefault();
return(lastRequest);
}
}
catch (Exception)
{
return(null);
}
}
public IHttpActionResult deleteContact(ContactViewModel pContact)
{
try {
ResponseViewModel rta = new ResponseViewModel();
using (BDRAEntities db = new BDRAEntities())
{
var contact = db.Contact.Where(cnt => cnt.cnt_id == pContact.id).FirstOrDefault();
db.Contact.Remove(contact);
db.SaveChanges();
rta.response = true;
rta.message = "Se ha eliminado el contacto correctamente";
return(Ok(rta));
}
}
catch (Exception e) {
return(BadRequest(e.Message));
}
}
public IHttpActionResult updateRiskInformationPerRequest(RequestViewModel pRequest)
{
try
{
ResponseViewModel response = new ResponseViewModel();
using (BDRAEntities db = new BDRAEntities())
{
var oRiskInformation = db.riskInformationByRequest.Where(ri => ri.rqt_id == pRequest.id && ri.ribr_state == true)
.FirstOrDefault();
oRiskInformation.sta_id = pRequest.riskInformation.riskState.id;
if (pRequest.riskInformation.datefiling != null)
{
oRiskInformation.ribr_dateFiling = pRequest.riskInformation.datefiling;
}
if (pRequest.riskInformation.ammountApproved != 0)
{
oRiskInformation.ribr_ammountApproved = pRequest.riskInformation.ammountApproved;
}
if (pRequest.riskInformation.dateApproved != null)
{
oRiskInformation.ribr_dateApproved = pRequest.riskInformation.dateApproved;
}
oRiskInformation.ribr_dateUpdateRow = DateTime.Now;
oRiskInformation.usu_document = pRequest.riskInformation.user.id;
db.SaveChanges();
response.response = true;
response.message = "se ha actualizado la solicitud: " + pRequest.id;
}
return(Ok(response));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
throw;
}
}
public IHttpActionResult getProbabilities()
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lsProbabilities = db.probability.Where(p => p.prb_state == true)
.Select(p => new ProbabilityViewModel {
id = p.prb_id, description = p.prb_description
})
.ToList();
return(Ok(lsProbabilities));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
public List <JobTitleViewModel> Get(string description)
{
try
{
using (BDRAEntities db = new BDRAEntities()) {
var lsJobTitles = db.JobTitlesClient.Where(jtc => jtc.jtcl_description.ToUpper().Contains(description.ToUpper()))
.Select(jtc => new JobTitleViewModel {
id = jtc.jtcl_id, description = jtc.jtcl_description, state = jtc.jtcl_state
})
.ToList();
return(lsJobTitles);
}
}
catch (Exception e)
{
throw;
}
}
public List <ClientViewModel> Get()
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lsClients = db.Client
.Select(cl => new ClientViewModel {
id = cl.cli_document, name = cl.cli_name, lastName = cl.cli_lastName
})
.ToList();
return(lsClients);
}
}
catch (Exception e)
{
throw;
}
}
public IHttpActionResult Get(int canalGroup_id)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var lsCanal = db.Canal
.Where(cnl => cnl.cnlGrp_id == canalGroup_id)
.Select(cnl => new CanalViewModel {
id = cnl.cnl_id, description = cnl.cnl_description, state = cnl.cnl_state
})
.ToList();
return(Ok(lsCanal));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
}
}
public IHttpActionResult updateOperationalInformationPerRequest(RequestViewModel pRequest)
{
try
{
ResponseViewModel response = new ResponseViewModel();
using (BDRAEntities db = new BDRAEntities())
{
if (pRequest.parentState != null && pRequest.childState != null)
{
var RequestBD = db.Request.Where(r => r.rqt_id == pRequest.id).FirstOrDefault();
RequestBD.rqt_primaryState = pRequest.parentState.id;
RequestBD.rqt_secondState = pRequest.childState.id;
db.SaveChanges();
}
var operationalInformation = db.operationalInformationByRequest.Where(oi => oi.rqt_id == pRequest.id && oi.oibr_state == true).FirstOrDefault();
operationalInformation.oibr_deliveredVehicles = short.Parse(pRequest.operationalInformation.deliveredVehicles.ToString());
operationalInformation.oibr_deliveredAmmount = long.Parse(pRequest.operationalInformation.deliveredAmmount.ToString());
operationalInformation.oibr_legalizationDate = pRequest.operationalInformation.legalizationDate;
operationalInformation.oibr_deliveredDate = pRequest.operationalInformation.deliveredDate;
operationalInformation.usu_document = pRequest.operationalInformation.user.id;
operationalInformation.oibr_dateUpdateRow = DateTime.Now;
db.SaveChanges();
response.response = true;
response.message = "Se actualiza la solicitud: " + pRequest.id;
return(Ok(response));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
throw;
}
}
public IHttpActionResult deleteRequest(RequestViewModel pRq)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
var requestRa = db.Request.Where(rq => rq.rqt_id == pRq.id).FirstOrDefault();
requestRa.rqt_state = false;
db.SaveChanges();
return(Ok(true));
}
}
catch (Exception e)
{
return(BadRequest(e.Message));
throw;
}
}
public IHttpActionResult DeleteRequestByID(string idPreRequest)
{
try
{
using (BDRAEntities db = new BDRAEntities())
{
int idPreRqt = int.Parse(idPreRequest);
var rta = new ResponseViewModel();
var objPreRequest = db.PreRequest.Where(p => p.preReq_id == idPreRqt).FirstOrDefault();
objPreRequest.preReq_state = false;
db.SaveChanges();
rta.response = true;
rta.message = "Se ha eliminado correctamente la pre solicitud: " + objPreRequest.preReq_id;
return(Ok(rta));
}
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}