public int LoginClientes(ClientesDTO clientesDTO) { int rsta; DataSet dsLogin; try { strPLSQL = "SELECT CUSTID FROM CUSTOMER WHERE USUARIO = '" + clientesDTO.User.ToString() + "' AND PASSWORD = '******'"; OracleServerHelper OrclConection = new OracleServerHelper(); dsLogin = OrclConection.ExecuteSqlToDataSet(strPLSQL, new List <OracleParameter>()); if (dsLogin != null && dsLogin.Tables[0].Rows.Count > 0) { rsta = 1; } else { rsta = 0; } } catch (Exception ex) { rsta = 0; Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, "ERROR EN DAL Clientes: LOGIN"); Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, " :: " + ex.Message); } return(rsta); }
public List <ClientesDTO> GetClientesPaginadoxFechaFact(ClientesDTO clientesDTO) { string strPLSQL2; List <ClientesDTO> LstClientesDTOs = new List <ClientesDTO>(); DataSet dsClientes; DataSet dsCuenta; int tmpRegsTotales = 0; OracleServerHelper OrclConection = new OracleServerHelper(); try { strWhere = "WHERE O.ORDERSTATUS = 'PA' AND O.ORDERDATE BETWEEN TO_DATE ('" + clientesDTO.FechaIniFact.ToString("yyyy-MM-dd") + "', 'yyyy/mm/dd') AND TO_DATE ('" + clientesDTO.FechaFinFact.ToString("yyyy-MM-dd") + "', 'yyyy/mm/dd')"; strWherePag = ConfiguracionParametrosGetPaginado(clientesDTO); strPLSQL = "SELECT AX.* FROM( " + "SELECT ROWNUM registro, X.* FROM( " + "SELECT SUM(O.ORDERVALUE) ORDERVALUE, A.ID, E.TypeIdent, A.CUSTID, A.FNAME, A.LNAME, A.EMAIL, A.PHONENUMBER, A.ADDRESS, A.CITY, A.COUNTRY, D.STATUS " + "FROM ORDERS O " + "INNER JOIN CUSTOMER A ON O.IDCustomer = A.ID " + "INNER JOIN TypeIdent E ON A.IDTypeIdent = E.CodTypeIdent " + "LEFT JOIN Status D ON A.IDSTATUS = D.CodStatus " + strWhere + " GROUP BY A.ID, E.TypeIdent, A.CUSTID, A.FNAME, A.LNAME, A.EMAIL, A.PHONENUMBER, A.ADDRESS, A.CITY, A.COUNTRY, D.STATUS) X) AX " + strWherePag + " ORDER BY AX.ORDERVALUE DESC"; if (clientesDTO.RegsTotales == 0) { strPLSQL2 = "SELECT COUNT(SUM(O.ORDERVALUE)) RegsTotales " + "FROM ORDERS O " + "INNER JOIN CUSTOMER A ON O.IDCustomer = A.ID " + "INNER JOIN TypeIdent E ON A.IDTypeIdent = E.CodTypeIdent " + "LEFT JOIN Status D ON A.IDSTATUS = D.CodStatus " + strWhere + " GROUP BY A.ID, E.TypeIdent, A.CUSTID, A.FNAME, A.LNAME, A.EMAIL, A.PHONENUMBER, A.ADDRESS, A.CITY, A.COUNTRY, D.STATUS"; dsCuenta = OrclConection.ExecuteSqlToDataSet(strPLSQL2, Lstparameters); if (dsCuenta != null && dsCuenta.Tables[0].Rows.Count == 1) { foreach (DataRow ldr_temp in dsCuenta.Tables[0].Rows) { tmpRegsTotales = Convert.ToInt32(ldr_temp["RegsTotales"]); } } } dsClientes = OrclConection.ExecuteSqlToDataSet(strPLSQL, Lstparameters); if (dsClientes != null && dsClientes.Tables.Count > 0) { foreach (DataRow dataRowClientes in dsClientes.Tables[0].Rows) { ClientesDTO lclientesDTO; CreditCardDTO creditCardDTO = new CreditCardDTO(); creditCardDTO = null; lclientesDTO = new ClientesDTO { ID = Convert.ToInt32(dataRowClientes["ID"]), CustID = Convert.ToInt32(dataRowClientes["CUSTID"]), FName = Convert.ToString(dataRowClientes["FNAME"]), LName = Convert.ToString(dataRowClientes["LNAME"]), CodTypeIdent = Convert.ToString(dataRowClientes["TYPEIDENT"]), PhoneNumber = Convert.ToString(dataRowClientes["PHONENUMBER"]), Email = Convert.ToString(dataRowClientes["EMAIL"]), Address = Convert.ToString(dataRowClientes["ADDRESS"]), Country = Convert.ToString(dataRowClientes["COUNTRY"]), City = Convert.ToString(dataRowClientes["CITY"]), //User = Convert.ToString(dataRowClientes["USUARIO"]), Status = Convert.ToString(dataRowClientes["STATUS"]), RegsTotales = tmpRegsTotales == 0 ? clientesDTO.RegsTotales : tmpRegsTotales, TotalFacturado = Convert.ToDecimal(dataRowClientes["ORDERVALUE"]), LCreditCard = new List <CreditCardDTO>() }; LstClientesDTOs.Add(lclientesDTO); } } } catch (Exception ex) { LstClientesDTOs = null; Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, "ERROR EN DAL Clientes: GetClientesPaginadoxFechaFact"); Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, " :: " + ex.Message); } return(LstClientesDTOs); }
public List <ClientesDTO> GetClientesPaginadoxEvento(ClientesDTO clientesDTO) { string strPLSQL2; List <ClientesDTO> LstClientesDTOs = new List <ClientesDTO>(); DataSet dsClientes; DataSet dsCuenta; int tmpRegsTotales = 0; OracleServerHelper OrclConection = new OracleServerHelper(); try { //strWhere = "WHERE O.EVENTCODE LIKE '%" + clientesDTO.Evento.Trim() + "% ' OR O.EVENTNAME LIKE '%" + clientesDTO.Evento.Trim() + "%'"; strWhere = "WHERE O.EVENTCODE = '" + clientesDTO.Evento.Trim() + "'"; strWherePag = ConfiguracionParametrosGetPaginado(clientesDTO); strPLSQL = "SELECT AX.* FROM( " + "SELECT ROWNUM registro, X.* FROM( " + "SELECT DISTINCT A.ID, E.TypeIdent, A.CUSTID, A.FNAME, A.LNAME, A.EMAIL, A.PHONENUMBER, A.ADDRESS, A.CITY, A.COUNTRY, D.STATUS " + "FROM ORDERS O " + "INNER JOIN CUSTOMER A ON O.IDCustomer = A.ID " + "INNER JOIN TypeIdent E ON A.IDTypeIdent = E.CodTypeIdent " + "LEFT JOIN Status D ON A.IDSTATUS = D.CodStatus " + strWhere + " ORDER BY ID) X ) AX " + strWherePag; if (clientesDTO.RegsTotales == 0) { strPLSQL2 = "SELECT COUNT(DISTINCT A.ID) RegsTotales " + "FROM ORDERS O " + "INNER JOIN CUSTOMER A ON O.IDCustomer = A.ID " + "INNER JOIN TypeIdent E ON A.IDTypeIdent = E.CodTypeIdent " + "LEFT JOIN Status D ON A.IDSTATUS = D.CodStatus " + strWhere; dsCuenta = OrclConection.ExecuteSqlToDataSet(strPLSQL2, new List <OracleParameter>()); if (dsCuenta != null && dsCuenta.Tables[0].Rows.Count == 1) { foreach (DataRow ldr_temp in dsCuenta.Tables[0].Rows) { tmpRegsTotales = Convert.ToInt32(ldr_temp["RegsTotales"]); } } } dsClientes = OrclConection.ExecuteSqlToDataSet(strPLSQL, Lstparameters); if (dsClientes != null && dsClientes.Tables.Count > 0) { foreach (DataRow dataRowClientes in dsClientes.Tables[0].Rows) { ClientesDTO lclientesDTO; CreditCardDTO creditCardDTO = new CreditCardDTO(); creditCardDTO = null; lclientesDTO = new ClientesDTO { ID = Convert.ToInt32(dataRowClientes["ID"]), CustID = Convert.ToInt32(dataRowClientes["CUSTID"]), FName = Convert.ToString(dataRowClientes["FNAME"]), LName = Convert.ToString(dataRowClientes["LNAME"]), CodTypeIdent = Convert.ToString(dataRowClientes["TYPEIDENT"]), PhoneNumber = Convert.ToString(dataRowClientes["PHONENUMBER"]), Email = Convert.ToString(dataRowClientes["EMAIL"]), Address = Convert.ToString(dataRowClientes["ADDRESS"]), Country = Convert.ToString(dataRowClientes["COUNTRY"]), City = Convert.ToString(dataRowClientes["CITY"]), //User = Convert.ToString(dataRowClientes["USUARIO"]), Status = Convert.ToString(dataRowClientes["STATUS"]), RegsTotales = tmpRegsTotales == 0 ? clientesDTO.RegsTotales : tmpRegsTotales, LCreditCard = new List <CreditCardDTO>() }; LstClientesDTOs.Add(lclientesDTO); } } } catch (Exception ex) { LstClientesDTOs = null; Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, "ERROR EN DAL Clientes: GetClientesPaginadoxEvento"); Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, " :: " + ex.Message); } return(LstClientesDTOs); }
public List <ClientesDTO> GetClientes(ClientesDTO clientesDTO) { List <ClientesDTO> LstClientesDTOs = new List <ClientesDTO>(); DataSet dsClientes; try { strWhere = ConfiguracionParametrosGet(clientesDTO); strPLSQL = "SELECT A.ID, A.IDTypeIdent, A.CUSTID, A.FNAME, A.LNAME, A.EMAIL, A.PHONENUMBER, A.ADDRESS, A.CITY, A.COUNTRY, A.USUARIO, A.PASSWORD, D.STATUS, " + "E.CREDITCARDTYPE, C.CREDITCARDNUMBER, C.CARDNAME, C.FVENCE, C.CODESECURITY " + "FROM CUSTOMER A " + "LEFT JOIN customercreditcards C ON A.ID = C.IDCUSTOMER " + "LEFT JOIN Status D ON A.IDSTATUS = D.CodStatus " + "LEFT JOIN CreditCardType E ON C.IDCREDITCARDTYPE = E.CodCreditCardType " + strWhere; OracleServerHelper OrclConection = new OracleServerHelper(); dsClientes = OrclConection.ExecuteSqlToDataSet(strPLSQL, Lstparameters); if (dsClientes != null && dsClientes.Tables.Count > 0) { foreach (DataRow dataRowClientes in dsClientes.Tables[0].Rows) { ClientesDTO lclientesDTO; CreditCardDTO creditCardDTO = new CreditCardDTO(); lclientesDTO = LstClientesDTOs.Find(x => x.CustID == Convert.ToInt32(dataRowClientes["CUSTID"])); if (Convert.ToString(dataRowClientes["CREDITCARDNUMBER"]).Trim().Length > 0) { creditCardDTO = new CreditCardDTO { Type = Convert.ToString(dataRowClientes["CREDITCARDTYPE"]), Number = Convert.ToString(dataRowClientes["CREDITCARDNUMBER"]), CardName = Convert.ToString(dataRowClientes["CARDNAME"]), ExpirationDate = Convert.ToString(dataRowClientes["FVENCE"]), SecurityCode = Convert.ToString(dataRowClientes["CODESECURITY"]), StatusCard = "" }; } else { creditCardDTO = null; } if (lclientesDTO == null) { lclientesDTO = new ClientesDTO { ID = Convert.ToInt32(dataRowClientes["ID"]), CustID = Convert.ToInt64(dataRowClientes["CUSTID"]), FName = Convert.ToString(dataRowClientes["FNAME"]), LName = Convert.ToString(dataRowClientes["LNAME"]), CodTypeIdent = Convert.ToString(dataRowClientes["IDTYPEIDENT"]), PhoneNumber = Convert.ToString(dataRowClientes["PHONENUMBER"]), Email = Convert.ToString(dataRowClientes["EMAIL"]), Address = Convert.ToString(dataRowClientes["ADDRESS"]), Country = Convert.ToString(dataRowClientes["COUNTRY"]), City = Convert.ToString(dataRowClientes["CITY"]), User = Convert.ToString(dataRowClientes["USUARIO"]), Password = Convert.ToString(dataRowClientes["PASSWORD"]), Status = Convert.ToString(dataRowClientes["STATUS"]), LCreditCard = new List <CreditCardDTO>() }; if (creditCardDTO != null) { lclientesDTO.LCreditCard.Add(creditCardDTO); } LstClientesDTOs.Add(lclientesDTO); } else { if (!lclientesDTO.LCreditCard.Contains(creditCardDTO)) { lclientesDTO.LCreditCard.Add(creditCardDTO); } } } } } catch (Exception ex) { LstClientesDTOs = null; Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, "ERROR EN DAL Clientes:"); Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, " :: " + ex.Message); } return(LstClientesDTOs); }
public long InsertarCliente(ClientesDTO ac_cliente) { long lb_respuesta; lb_respuesta = 0; try { string ls_sql; OracleServerHelper losh_conection; DataSet lds_datos; ls_sql = "SELECT * FROM CUSTOMER WHERE USUARIO = '" + ac_cliente.User.ToString() + "'"; losh_conection = new OracleServerHelper(); lds_datos = losh_conection.ExecuteSqlToDataSet(ls_sql, new List <OracleParameter>()); if (lds_datos != null && lds_datos.Tables[0].Rows.Count > 0) { throw new Exception("Ya existe un usuario con el LOGIN ingresado"); } else { DataSet dsAffected; long tmpID = 0; int ll_affected; int tmprsta; ls_sql = "INSERT INTO CUSTOMER (CUSTID,FNAME,LNAME,PHONENUMBER,EMAIL,PASSWORD,IDSTATUS,"; ls_sql += "ADDRESS,CITY,COUNTRY,USUARIO,IDTYPEIDENT) VALUES(" + ac_cliente.CustID.ToString(); ls_sql += ",'" + ac_cliente.FName + "','" + ac_cliente.LName + "',"; ls_sql += ac_cliente.PhoneNumber == null ? "NULL," : "'" + ac_cliente.PhoneNumber + "',"; ls_sql += ac_cliente.Email == null ? "NULL," : "'" + ac_cliente.Email + "',"; ls_sql += "'" + ac_cliente.Password + "',1,'" + ac_cliente.Address + "',"; ls_sql += "'" + ac_cliente.City + "','" + ac_cliente.Country + "',"; ls_sql += "'" + ac_cliente.User + "'," + ac_cliente.CodTypeIdent + ")"; tmprsta = losh_conection.ExecuteSql(ls_sql, new List <OracleParameter>()); if (tmprsta > 0) { ls_sql = "SELECT MAX(ID) FROM Customer"; dsAffected = losh_conection.ExecuteSqlToDataSet(ls_sql, new List <OracleParameter>()); if (dsAffected != null && dsAffected.Tables[0].Rows.Count > 0) { foreach (DataRow dataRowClientes in dsAffected.Tables[0].Rows) { tmpID = Convert.ToInt32(dataRowClientes["MAX(ID)"]); } } else { tmpID = 0; } if (tmpID != 0) { if (ac_cliente.LCreditCard != null) { if (ac_cliente.LCreditCard.Count > 0) { foreach (CreditCardDTO lcc_creditCard in ac_cliente.LCreditCard) { ls_sql = "INSERT INTO CUSTOMERCREDITCARDS(CREDITCARDNUMBER,IDCUSTOMER,IDCREDITCARDTYPE,"; ls_sql += "FVENCE,CODESECURITY,CARDNAME) VALUES(" + lcc_creditCard.Number + ","; ls_sql += tmpID.ToString() + "," + lcc_creditCard.Type + ","; ls_sql += "'" + lcc_creditCard.ExpirationDate + "'," + lcc_creditCard.SecurityCode + ","; ls_sql += "'" + lcc_creditCard.CardName + "')"; ll_affected = losh_conection.ExecuteSql(ls_sql, new List <OracleParameter>()); if (ll_affected <= 0) { throw new Exception("Error ingresando tarjeta de credito"); } } lb_respuesta = tmpID; } else { lb_respuesta = tmpID; } } else { lb_respuesta = tmpID; } } else { lb_respuesta = tmpID; } } else { lb_respuesta = tmpID; } } } catch (Exception ae_e) { lb_respuesta = 0; Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, "ERROR EN DAL Clientes:InsertarCliente"); Common.CreateTrace.WriteLog(Common.CreateTrace.LogLevel.Error, " :: " + ae_e.Message); throw ae_e; } return(lb_respuesta); }