public static void SetQueues(DropDownList ddl)
{
try
{
String query = String.Format("SELECT PKQueueU, QueueUName FROM PaymentsQueueU");
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
ddl.Items.Insert(0, new ListItem("--Seleccionar cola--", "0"));
List <ListItem> data = aux.AsEnumerable().Select(m => new ListItem()
{
Value = Convert.ToString(m.Field <int>("PKQueueU")),
Text = m.Field <String>("QueueUName"),
}).ToList();
int i = 1;
foreach (ListItem item in data)
{
ddl.Items.Insert(i, item);
i++;
}
ddl.DataBind();
}
catch (Exception ex)
{
throw ex;
}
}
public static List <string> GetCustomers(string username)
{
List <string> customers = new List <string>();
try
{
if (username != String.Empty)
{
//Obtiene clientes que tengan deuda
String query = String.Format(@"SELECT DISTINCT XS.RazonSocial
FROM Customer C
INNER JOIN xSOAddress XS ON C.CustId = XS.CustId
INNER JOIN ARDoc AD ON C.CustId = AD.CustId
WHERE AD.DocBal > 0 AND GETDATE() > AD.DueDate AND XS.RazonSocial LIKE '%{0}%'", username);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
foreach (DataRow row in aux.Rows)
{
customers.Add(row["RazonSocial"].ToString().Trim());
}
}
}
catch (Exception ex)
{
throw ex;
}
return(customers);
}
public String GetID()
{
String query = String.Format("SELECT IDUser FROM PaymentsUsers WHERE UserName = '******'", userName);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
IDUser = aux.Rows.Count > 0 ? aux.Rows[0]["IDUser"].ToString() : "IDUser was not found";
return(IDUser);
}
public void Register()
{
try
{
String query = String.Format("INSERT INTO PaymentsUsers (UserName, Password, Name) VALUES ('{0}', '{1}', '{2}')", user.userName, user.password, user.name);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public void SetPaymentFree()
{
try
{
String query = String.Format("UPDATE Customer SET User3 = 0 WHERE CustId = '{0}'", this.customerID);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public void SavePhone(Phone phone)
{
try
{
String query = String.Format(@"INSERT INTO PaymentsCustomerPhone (FKCustomerSL, PhoneType, Phone) VALUES ('{0}', '{1}', '{2}')", this.fkHolder, phone.phoneType, phone.phone);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public Balance GetBalances(DateTime date)
{
try
{
//date = date.AddHours(23);
//date = date.AddMinutes(59);
String query = String.Format(@"SELECT CASE WHEN W.Interes IS NULL THEN (SELECT SUM(PA.Intereses + PA.Iva) AS 'Interes'
FROM PaymentsOportunity PO
INNER JOIN PaymentsCustomer PC ON PO.FKCliente = PC.PKCustomer
INNER JOIN PaymentsAmortization PA ON PO.PKOportunity = PA.FKOportunity
WHERE PC.PKCustomerSL = '{1}')
ELSE W.Interes END 'Interes',
Z.DiasVencimiento,
Z.DocBal,
Z.SaldoVigente,
Z.DueDate,
Z.CustId
FROM (SELECT /*CASE WHEN XAM.Interes IS NULL THEN 0 ELSE XAM.Interes END 'Interes', */
'DiasVencimiento' =
CASE
WHEN DATEDIFF(DAY, AD.DueDate, '{0}') > 0 AND DATEDIFF(DAY, AD.DueDate, '{0}') <=30 THEN '30'
WHEN DATEDIFF(DAY, AD.DueDate, '{0}') > 30 AND DATEDIFF(DAY, AD.DueDate, '{0}') <=60 THEN '60'
WHEN DATEDIFF(DAY, AD.DueDate, '{0}') > 60 AND DATEDIFF(DAY, AD.DueDate, '{0}') <=90 THEN '90'
WHEN DATEDIFF(DAY, AD.DueDate, '{0}') > 90 THEN '+90'
ELSE '0'
END
, CASE WHEN AD.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * AD.DocBal AS DocBal,
CASE WHEN SV.SaldoVigente IS NULL THEN 0 ELSE SV.SaldoVigente END 'SaldoVigente',
AD.DueDate,
AD.CustId
FROM xAMContAutDet XAM
RIGHT JOIN ARDOC AD ON XAM.Custid = AD.CustId AND XAM.Contrato = AD.USER5 AND XAM.Num = AD.InstallNbr
LEFT JOIN (SELECT SUM(AD.DocBal) 'SaldoVigente', AD.CustId, AD.User5 --Documentos no vencidos
FROM ARDoc AD
WHERE AD.CustId = '{1}' AND '{0}' <= AD.DueDate AND AD.DocBal != 0 AND AD.DocType IN ('IN','DM','FI','NC','AD')
GROUP BY AD.CustId, AD.User5) SV ON SV.CustId = AD.CustId AND SV.User5 = AD.User5
WHERE AD.DocBal != 0 AND /* '{0}' > AD.DueDate AND */ AD.CustId = '{1}') Z
LEFT JOIN (SELECT SUM(xa.Interes+ xa.IvaInteres) as 'Interes', xa.Custid
FROM ARDoc AD
INNER JOIN xAMContAutDet XA ON AD.CustId = XA.Custid AND AD.User5 = XA.Contrato and ad.user6 = xa.anexo AND AD.InstallNbr = XA.Num
WHERE AD.CustId = '{1}' AND '{0}' <= AD.DueDate AND AD.DocBal > 0 --and Anexo = 'b'
GROUP BY xa.Custid)
W ON Z.CustId = W.CustId --ON
GROUP BY W.Interes, Z.DiasVencimiento, Z.DocBal, Z.SaldoVigente, Z.DueDate, Z.CustId", date.ToString("yyyy -MM-dd HH:mm:ss"), this.CustomerID);
DataBaseSettings db = new DataBaseSettings();
Balance bl = new Balance(db.GetDataTable(query), date);
return(bl);
} catch (Exception ex)
{
throw ex;
}
}
public static String GetQueueConditionsByUsers(String pkUser)
{
String result = String.Empty;
try
{
String orderBy = String.Format("ORDER BY EnCola");
//bool flag = false;
String query = String.Format(@"SELECT PQC.Query, PQM.Value
FROM PaymentsUsers PU
INNER JOIN PaymentsQueueUsers PQU ON PU.IDUser = PQU.FKUser
INNER JOIN PaymentsQueueU PQUU ON PQU.FKQueueU = PQUU.PKQueueU
INNER JOIN PaymentsQueueMix PQM ON PQM.FKQueueU = PQUU.PKQueueU
INNER JOIN PaymentsQueueC PQC ON PQM.FKQueueC = PQC.PKQueueC
WHERE PQU.FKUser = '******'", pkUser);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
if (aux.Rows.Count > 0) //the user does not have queue assigned
{
foreach (DataRow row in aux.Rows)
{
if (row["Query"].ToString().Contains("BETWEEN"))
{
result += String.Format(" AND {0} {1} ", row["Query"].ToString(), row["Value"].ToString());
}
else if (row["Query"].ToString().Contains("ORDER BY"))
{
//if (flag)
//{
orderBy += String.Format(",{0} ", row["Query"].ToString().Replace("ORDER BY", String.Empty));
//}
//else //first order by
//{
//orderBy += String.Format("{0} ", row["Query"].ToString());
// flag = true;
//}
}
else
{
result += String.Format(" AND {0} '{1} '", row["Query"].ToString(), row["Value"].ToString());
}
}
result += orderBy;
}
} catch (Exception ex)
{
throw ex;
}
return(result);
}
private void InsertQueueByUser()
{
try
{
String query = String.Format(@"INSERT INTO PaymentsQueueUsers (FKQueueU, FKUser) VALUES ({0}, {1})", this.pkQueueU, this.pkUser);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetFirstPayment(String CustomerID)
{
try
{
String query = queryGet + String.Format(@"AND PKCliente = '{0}'", CustomerID);
DataBaseSettings db = new DataBaseSettings();
return(db.GetDataTable(query));
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetFirstPaymentByName(String customerName)
{
try
{
String query = queryGet + String.Format(@"AND NombreTitular = '{0}'", customerName);
DataBaseSettings db = new DataBaseSettings();
return(db.GetDataTable(query));
}
catch (Exception ex)
{
throw ex;
}
}
private void UpdateQueueByUser()
{
try
{
String query = String.Format(@"UPDATE PaymentsQueueUsers SET FKQueueU = {0} WHERE FKUser = {1}", this.pkQueueU, this.pkUser);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
private void SetPKQueueU(String queueName)
{
try
{
String query = String.Format(@"SELECT PKQueueU FROM PaymentsQueueU Where QueueUName = '{0}'", queueName);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
this.pkQueueU = aux.Rows.Count > 0 ? Convert.ToInt32(aux.Rows[0]["PKQueueU"].ToString()) : 0;
}
catch (Exception ex)
{
throw ex;
}
}
public void Save()
{
try
{
String query = String.Format(@"INSERT INTO PaymentsRecord (FKUser, FKCusId, FKCallType, PhoneCalled, FkStatusCall, Coments, StartDate, FinalDate)
VALUES ({0}, '{1}', {2}, '{3}', {4}, '{5}', '{6}', '{7}')", fkUser, PKCustomer, fkCallType, phoneCalled, fkStatusCall, coments, startTime.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
} catch (Exception ex)
{
throw ex;
}
}
public void SaveQueueU(String queueName, String moraValue, String historialMoraValue, String status, String productoFinanciadoValue, String creditoSimpleValue, String arrendamientoValue)
{
try
{
String query = String.Format(@"INSERT INTO PaymentsQueueU (QueueUName) VALUES ('{0}')", queueName);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
SetPKQueueU(queueName);
SaveQueueMix(moraValue, historialMoraValue, status, productoFinanciadoValue, creditoSimpleValue, arrendamientoValue);
} catch (Exception ex)
{
throw ex;
}
}
public DataTable GetReport2()
{
try
{
String query = String.Format(@"SELECT PR.User1, PR.User2, PR.User3, PR.User4, PR.User5
FROM PaymentsRecord PR
WHERE PR.FKCusId = '{0}'
ORDER BY PR.FinalDate DESC", this.PKCustomer);
DataBaseSettings db = new DataBaseSettings();
return(db.GetDataTable(query));
} catch (Exception ex)
{
throw ex;
}
}
public void SaveAddrees(Address information)
{
try
{
String query = String.Format(@"INSERT INTO PaymentsGuaranteeAddress (FKGuarantee, AddressType, Address, BetweenStreets, Colony, City, AreaCode, Reference)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", this.pkGuarantee, information.addressType, information.myAddress, information.betweenStreets, information.colony, information.city, information.CP, information.reference);
DataBaseSettings db = new DataBaseSettings();
db.ExecuteQuery(query);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetContracts(String custId)
{
try
{
String query = String.Format(@"SELECT DISTINCT User5 as 'Contrato'
FROM ARDoc
WHERE CustId = '{0}' AND User5 != ' '", custId);
DataBaseSettings db = new DataBaseSettings();
return(db.GetDataTable(query));
} catch (Exception ex)
{
throw ex;
}
}
public DataTable GetReportByDay()
{
try
{
//initial date
this.fechaInicial = this.fechaInicial.AddMinutes(-1);
String finalQuery = String.Format("{0}'{1}' AND '{2}'", query, this.fechaInicial.ToString("yyyy-MM-dd HH:mm:ss"), this.fechaFinal.ToString("yyyy-MM-dd HH:mm:ss"));
DataBaseSettings db = new DataBaseSettings();
return(db.GetDataTable(finalQuery));
} catch (Exception ex)
{
throw ex;
}
}
private void SaveQueueMix(String moraValue, String historialMoraValue, String status, String productoFinanciadoValue, String creditoSimpleValue, String arrendamientoValue)
{
try
{
SetPKQueueC();
String query = String.Empty;
DataBaseSettings db = new DataBaseSettings();
foreach (int pkQueueC in pkQueueC)
{
switch (pkQueueC)
{
case 1:
query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, moraValue);
break;
case 3:
query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, historialMoraValue);
break;
case 6:
query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, status);
break;
case 8:
query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, productoFinanciadoValue);
break;
/*
* case 9:
* query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, creditoSimpleValue);
* break;
*
* case 10:
* query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU, Value) VALUES ({0}, {1}, '{2}')", pkQueueC, this.pkQueueU, arrendamientoValue);
* break;
*/
default:
query = String.Format(@"INSERT INTO PaymentsQueueMix (FKQueueC, FKQueueU) VALUES ({0}, {1})", pkQueueC, this.pkQueueU); //without value
break;
}
db.ExecuteQuery(query);
}
} catch (Exception ex)
{
throw ex;
}
}
public DataTable GetAddress()
{
DataTable data = new DataTable();
try
{
String query = String.Format(@"SELECT ga.AddressType as 'Tipo', ga.Address as 'Domicilio', ga.BetweenStreets as 'Entre calles', ga.Colony as 'Colonia', ga.City, ga.AreaCode as 'CP', ga.Reference as 'Pto referencia'
FROM PaymentsCustomerAddress ga WHERE ga.FKCustomerSL = '{0}'", this.fkHolder);
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
} catch (Exception ex)
{
throw ex;
}
return(data);
}
public DataTable GetPhone()
{
DataTable data = new DataTable();
try
{
String query = String.Format("SELECT hp.PhoneType as 'Tipo de teléfono', hp.Phone as 'Número' FROM PaymentsCustomerPhone hp WHERE hp.FKCustomerSL = '{0}'", this.fkHolder);
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
} catch (Exception ex)
{
throw ex;
}
return(data);
}
private String GetPKGuarantee()
{
String pkGuarantee = String.Empty;
try
{
String query = String.Format(@"SELECT PKGuarantee FROM PaymentsGuarantee WHERE FKCustomerSL = '{0}'", this.fkCustomer);
DataBaseSettings db = new DataBaseSettings();
DataTable aux = db.GetDataTable(query);
pkGuarantee = aux.Rows.Count > 0 ? aux.Rows[0]["PKGuarantee"].ToString().Trim() : String.Empty;
} catch (Exception ex)
{
throw ex;
}
return(pkGuarantee);
}
public bool UserNameExist()
{
DataTable data = new DataTable();
try
{
String query = String.Format("Select * from PaymentsUsers where UserName = '******'", user.userName);
DataBaseSettings dbObject = new DataBaseSettings();
data = dbObject.GetDataTable(query);
}
catch (Exception ex)
{
throw ex;
}
return(data.Rows.Count > 0 ? true : false);
}
public DataTable GetAddress()
{
DataTable data = new DataTable();
try
{
String query = String.Format(@"SELECT ga.AddressType as 'Tipo', ga.Address as 'Domicilio', ga.BetweenStreets as 'Entre calles', ga.Colony as 'Colonia', ga.City, ga.AreaCode as 'CP', ga.Reference as 'Pto referencia'
FROM PaymentsGuaranteeAddress ga INNER JOIN PaymentsGuarantee PG ON GA.FKGuarantee = PG.PKGuarantee
INNER JOIN Customer C ON C.CustId = PG.FKCustomerSL
WHERE C.CustId = '{0}'", this.fkCustomer);
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
}
catch (Exception ex)
{
throw ex;
}
return(data);
}
public static DataTable GetQueueByUsers()
{
DataTable data = new DataTable();
try
{
String query = String.Format(@"SELECT PU.Name, PU.UserName 'Username', PS.QueueUName 'Queue'
FROM PaymentsUsers PU
LEFT JOIN PaymentsQueueUsers PQ on PU.IDUser = PQ.FKUser
LEFT JOIN PaymentsQueueU PS on PQ.FKQueueU = PS.PKQueueU");
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
} catch (Exception ex)
{
throw ex;
}
return(data);
}
private bool CheckUserInQueue()
{
DataTable aux = new DataTable();
try
{
String query = String.Format(@"SELECT *
FROM PaymentsUsers PU
INNER JOIN PaymentsQueueUsers PQU ON PU.IDUser = PQU.FKUser
INNER JOIN PaymentsQueueU PQQ ON PQQ.PKQueueU = PQU.FKQueueU
WHERE PQU.FKUser = {0}", this.pkUser);
DataBaseSettings db = new DataBaseSettings();
aux = db.GetDataTable(query);
} catch (Exception ex)
{
throw ex;
}
return(aux.Rows.Count > 0 ? true : false);
}
public DataTable GetPhone()
{
DataTable data = new DataTable();
try
{
String query = String.Format(@"SELECT hp.PhoneType as 'Tipo de teléfono', hp.Phone as 'Número'
FROM PaymentsGuaranteePhone hp INNER JOIN PaymentsGuarantee PG ON HP.FKGuarantee = PG.PKGuarantee
INNER JOIN Customer C ON C.CustId = PG.FKCustomerSL
WHERE C.CustId = '{0}'", this.fkCustomer);
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
}
catch (Exception ex)
{
throw ex;
}
return(data);
}
public DataTable GetFirstPaymentByUser(String userID)
{
try
{
DataBaseSettings db = new DataBaseSettings();
String finalQuery = queryGet + QueueOperations.GetQueueConditionsByUsers(userID);
//if (finalQuery.Contains("ORDER BY"))
//{
// finalQuery += String.Format(", EnCola");
//}
//else
//{
// finalQuery += String.Format("ORDER BY EnCola");
//}
return(db.GetDataTable(finalQuery));
} catch (Exception ex)
{
throw ex;
}
}
public DataTable Get()
{
DataTable data = new DataTable();
try
{
String query = String.Format(@"SELECT USR.Name AS 'Gestor', PR.FinalDate AS 'Fecha y hora de gestión', CT.CallTypeName AS 'Tipo de llamada', PR.PhoneCalleD AS 'Teléfono marcado', ST.StatusCallAbbreviation AS 'Estatus', ST.StatusCallName AS 'Descripción estatus', PR.Coments AS 'Comentario'
, PR.PaymentPromise AS 'Fecha promesa de pago', PR.MoneyPromise AS 'Cantidad promesa de pago'
FROM PaymentsRecord PR
INNER JOIN PaymentsUsers USR ON PR.FKUser = USR.IDUser
INNER JOIN PaymentsCallTypes CT ON PR.FKCallType = CT.PKCallType
INNER JOIN PaymentsStatusCall ST ON PR.FKStatusCall = ST.PKStatusCall
WHERE PR.FKCusId = '{0}'
ORDER BY PR.FinalDate DESC", this.PKCustomer);
DataBaseSettings db = new DataBaseSettings();
data = db.GetDataTable(query);
} catch (Exception ex)
{
throw ex;
}
return(data);
}