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