public static InputDeliveryList GetList(bool childs,
ETipoAcreedor providerType,
long oidProvider,
long oidSerie,
ETipoAlbaranes deliveryType,
ETipoFactura invoiceType,
DateTime from,
DateTime till)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = (oidProvider != 0) ? ProviderBaseInfo.New(oidProvider, providerType) : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
Serie = (oidSerie != 0) ? Serie.SerieInfo.New(oidSerie) : null,
TipoAlbaranes = deliveryType,
TipoFactura = invoiceType,
FechaIni = from,
FechaFin = till,
};
if (oidSerie != 0)
{
conditions.Serie.Oid = oidSerie;
}
return(GetList(childs, SELECT(conditions)));
}
public static PaymentList GetList(ETipoAcreedor providerType,
long oidProvider,
DateTime from,
DateTime till,
EStepGraph step,
CriteriaEx criteria,
bool childs)
{
QueryConditions conditions = new QueryConditions()
{
PagingInfo = (criteria != null) ? criteria.PagingInfo : null,
Filters = (criteria != null) ? criteria.Filters : null,
Orders = (criteria != null) ? criteria.Orders : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
Acreedor = (oidProvider != 0) ? ProviderBaseInfo.New(oidProvider, providerType) : null,
PaymentType = providerType == ETipoAcreedor.Empleado ? ETipoPago.Nomina : ETipoPago.Factura,
FechaIni = from,
FechaFin = till,
Step = step,
};
string query = SELECT(conditions);
if (criteria != null)
{
criteria.PagingInfo = conditions.PagingInfo;
}
return(GetList(query, criteria, childs));
}
public static void IsPosibleDelete(long oid, ETipoAcreedor providerType)
{
QueryConditions conditions = new QueryConditions
{
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
Acreedor = ProviderBaseInfo.New(oid, providerType),
Estado = EEstado.NoAnulado
};
InputDeliveryList albaranes = InputDeliveryList.GetList(conditions, false);
if (albaranes.Count > 0)
{
throw new iQException(Resources.Messages.ALBARANES_ASOCIADOS);
}
InputInvoiceList facturas = InputInvoiceList.GetList(conditions, false);
if (facturas.Count > 0)
{
throw new iQException(Resources.Messages.FACTURAS_ASOCIADAS);
}
conditions.PaymentType = ETipoPago.Factura;
PaymentList pagos = PaymentList.GetList(conditions, false);
if (pagos.Count > 0)
{
throw new iQException(Resources.Messages.PAGOS_ASOCIADOS);
}
}
public static InputInvoiceList GetList(ETipoAcreedor providerType,
long oidProvider,
long oidSerie,
ETipoFacturas invoicesType,
ETipoFactura invoiceType,
DateTime from,
DateTime till,
bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = (oidProvider != 0) ? ProviderBaseInfo.New(oidProvider, providerType) : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
Serie = (oidSerie != 0) ? Serie.Serie.New().GetInfo() : null,
TipoFacturas = invoicesType,
TipoFactura = invoiceType,
FechaIni = from,
FechaFin = till,
};
if (oidSerie != 0)
{
conditions.Serie.Oid = oidSerie;
}
return(GetList(InputInvoiceSQL.SELECT(conditions), childs));
}
public static PayrollList GetPendientesList(long oid_empleado, bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = ProviderBaseInfo.New(oid_empleado, ETipoAcreedor.Empleado)
};
return(GetPendientesList(conditions, childs));
}
public static string SELECT_BY_PUERTO(long oid)
{
QueryConditions conditions = new QueryConditions()
{
Puerto = PuertoInfo.New()
};
conditions.Puerto.Oid = oid;
return(ProviderBaseInfo.SELECT(conditions, ETipoAcreedor.Despachante));
}
public static string SELECT_ESTIMADO(QueryConditions conditions)
{
string query;
if (conditions.TipoAcreedor[0] == ETipoAcreedor.Todos)
query = ProviderBaseInfo.SELECT_BUILDER(local_caller_ESTIMADO, conditions);
else
query = SELECT_BASE_ESTIMADO(conditions);
return query;
}
public static PaymentSummary Get(ETipoAcreedor tipo, long oid)
{
CriteriaEx criteria = Expedient.GetCriteria(Expedient.OpenSession());
if (nHManager.Instance.UseDirectSQL)
criteria.Query = SELECT(ProviderBaseInfo.New(oid, tipo));
Expedient.BeginTransaction(criteria.Session);
return DataPortal.Fetch<PaymentSummary>(criteria);
}
public static PedidoProveedorList GetPendientesList(long oid, ETipoAcreedor providerType, long oidSerie, bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = (oid != 0) ? ProviderBaseInfo.New(oid, providerType) : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
};
return(GetList(PedidoProveedor.SELECT_PENDIENTES(conditions, false), childs));
}
public static InputInvoiceList GetListByAcreedor(long oid, ETipoAcreedor providerType, bool childs)
{
QueryConditions conditions = new QueryConditions
{
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
Acreedor = ProviderBaseInfo.New(oid, providerType)
};
return(GetList(InputInvoiceSQL.SELECT(conditions), childs));
}
public static InputInvoiceList GetListNoAsignadasByAcreedor(long oid, ETipoAcreedor providerType, bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = ProviderBaseInfo.New(oid, providerType),
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
};
return(GetList(InputInvoiceSQL.SELECT_SIN_EXPEDIENTE(conditions), childs));
}
public static PedidoProveedorList GetByAcreedorList(long oid, ETipoAcreedor providerType, bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = (oid != 0) ? ProviderBaseInfo.New(oid, providerType) : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
};
return(GetList(conditions, childs));
}
public static InputInvoiceList GetPendientesList(long oidProvider, ETipoAcreedor providerType, bool childs)
{
QueryConditions conditions = new QueryConditions
{
TipoFacturas = ETipoFacturas.Pendientes,
Acreedor = ProviderBaseInfo.New(oidProvider, providerType),
TipoAcreedor = new ETipoAcreedor[1] {
providerType
}
};
return(GetList(InputInvoiceSQL.SELECT(conditions), childs));
}
public static string SELECT(QueryConditions conditions)
{
string query = string.Empty;
if ((conditions.TipoAcreedor[0] == ETipoAcreedor.Todos) ||
(conditions.TipoAcreedor.Length > 1))
query = ProviderBaseInfo.SELECT_BUILDER(local_caller, conditions);
else
query = SELECT_BASE(conditions);
return query;
}
internal static string INNER_ACREEDOR(ETipoAcreedor tipo)
{
string query;
if (tipo != ETipoAcreedor.Todos)
{
query = " INNER JOIN " + ProviderBaseInfo.TABLE(tipo) + " AS A ON A.\"OID\" = PP.\"OID_ACREEDOR\" AND PP.\"TIPO_ACREEDOR\" = " + Convert.ToInt32(tipo).ToString();
}
else
{
query = " LEFT JOIN " + ProviderBaseInfo.TABLE(ETipoAcreedor.Proveedor) + " AS A ON A.\"OID\" = PP.\"OID_ACREEDOR\" AND PP.\"TIPO_ACREEDOR\" = " + Convert.ToInt32(tipo).ToString();
}
return(query);
}
internal static string SELECT(ETipoTransportista tipo, bool lockTable)
{
string query = string.Empty;
if (tipo == ETipoTransportista.Todos)
{
query = ProviderBaseInfo.SELECT_BASE(0, ETipoAcreedor.TransportistaDestino, lockTable);
}
else
{
ETipoAcreedor tipo_acreedor = moleQule.Store.Structs.EnumConvert.ToETipoAcreedor(tipo);
query = ProviderBaseInfo.SELECT_BASE(0, tipo_acreedor, lockTable);
}
return(query);
}
public InputInvoice NewItem(InputDeliveryInfo albaran)
{
InputInvoice item = NewItem();
ProviderBaseInfo acreedor = ProviderBaseInfo.Get(albaran.OidAcreedor, albaran.ETipoAcreedor, true);
item.CopyFrom(acreedor);
item.CopyFrom(albaran);
item.Insert(albaran);
item.Fecha = albaran.Fecha;
item.NFactura = "SIN NUMERO";
SetNextCode(item);
return(item);
}
public static PedidoProveedorList GetList(ETipoAcreedor providerType,
long oidProvider,
DateTime from,
DateTime till,
bool childs)
{
QueryConditions conditions = new QueryConditions
{
Acreedor = (oidProvider != 0) ? ProviderBaseInfo.New(oidProvider, providerType) : null,
TipoAcreedor = new ETipoAcreedor[1] {
providerType
},
FechaIni = from,
FechaFin = till,
};
return(GetList(conditions, childs));
}
public static BatchList GetByProductStockList(long oidProduct, long oidProvider, ETipoAcreedor providerType, bool childs)
{
CriteriaEx criteria = Batch.GetCriteria(Batch.OpenSession());
criteria.Childs = childs;
QueryConditions conditions = new QueryConditions
{
Acreedor = (oidProvider != 0) ? ProviderBaseInfo.New(oidProvider, providerType) : null,
Producto = ProductInfo.New(oidProduct)
};
criteria.Query = Batch.SELECT_STOCK(conditions, false);
BatchList list = DataPortal.Fetch <BatchList>(criteria);
CloseSession(criteria.SessionCode);
return(list);
}
public static WorkReport GetByResource(long oidResource, ETipoEntidad entityType, bool childs = true)
{
QueryConditions conditions = new QueryConditions();
switch (entityType)
{
case moleQule.Common.Structs.ETipoEntidad.Empleado:
conditions.Acreedor = ProviderBaseInfo.New(oidResource, ETipoAcreedor.Empleado);
break;
case moleQule.Common.Structs.ETipoEntidad.OutputDelivery:
conditions.Add(oidResource, ETipoEntidad.OutputDelivery);
break;
case moleQule.Common.Structs.ETipoEntidad.Tool:
conditions.Tool = ToolInfo.New(oidResource);
break;
}
return(Get(SELECT(conditions, true), childs));
}
internal static string SELECT(QueryConditions conditions, bool lockTable)
{
string query;
if (conditions.TipoAcreedor[0] == ETipoAcreedor.Todos)
{
query = ProviderBaseInfo.SELECT_BUILDER(local_caller, conditions) +
" ORDER BY \"OID\"";//Para que respete el orden en el que se han añadido los productos
}
//y los muestre en ese mismo orden
else
{
query = SELECT_BASE(conditions, conditions.TipoAcreedor[0]) +
" ORDER BY \"OID\"";//Para que respete el orden en el que se han añadido los productos
//y los muestre en ese mismo orden
if (lockTable)
{
query += " FOR UPDATE OF PP NOWAIT";
}
}
return(query);
}
public static PaymentList GetListByAgente(long oid, ETipoAcreedor tipo, bool childs)
{
return(GetList(PaymentList.SELECT(ProviderBaseInfo.New(oid, tipo)), childs));
}
public static string SELECT(QueryConditions conditions)
{
return(ProviderBaseInfo.SELECT_BASE(conditions, ETipoAcreedor.Despachante));
}
public static string SELECT_BASE(QueryConditions conditions)
{
string e = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ExpedientRecord));
string pr = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.SupplierRecord));
string nv = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.ShippingCompanyRecord));
string tr = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.TransporterRecord));
string dp = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.CustomAgentRecord));
string query =
FIELDS(conditions.TipoAcreedor[0]) + @"
FROM " + ProviderBaseInfo.TABLE(conditions.TipoAcreedor[0]) + @" AS A";
query += (conditions.ExtraJoin != string.Empty)
? conditions.ExtraJoin
: JOIN_PAGOS(conditions.TipoAcreedor[0], conditions);
switch (conditions.TipoAcreedor[0])
{
case ETipoAcreedor.Proveedor:
query += @"
LEFT JOIN (SELECT SUM(""G_PROV_TOTAL"") AS ""TOTAL_ESTIMADO""
,""OID_PROVEEDOR""
FROM " + e + @"
WHERE ""OID_FACTURA_PRO"" = 0 GROUP BY ""OID_PROVEEDOR"")
AS E ON E.""OID_PROVEEDOR"" = A.""OID""";
break;
case ETipoAcreedor.Naviera:
query += @"
LEFT JOIN (SELECT SUM(""G_NAV_TOTAL"") AS ""TOTAL_ESTIMADO""
,""OID_NAVIERA""
FROM " + e + @"
WHERE ""OID_FACTURA_NAV"" = 0
GROUP BY ""OID_NAVIERA"")
AS E ON E.""OID_NAVIERA"" = A.""OID""";
break;
case ETipoAcreedor.TransportistaDestino:
query += @"
LEFT JOIN (SELECT SUM(""G_TRANS_DEST_TOTAL"") AS ""TOTAL_ESTIMADO""
,""OID_TRANS_DESTINO""
FROM " + e + @"
WHERE ""OID_FACTURA_TDE"" = 0
GROUP BY ""OID_TRANS_DESTINO"")
AS E ON E.""OID_TRANS_DESTINO"" = A.""OID""";
break;
case ETipoAcreedor.TransportistaOrigen:
query += @"
LEFT JOIN (SELECT SUM(""G_TRANS_TOTAL"") AS ""TOTAL_ESTIMADO""
,""OID_TRANS_ORIGEN""
FROM " + e + @"
WHERE ""OID_FACTURA_TOR"" = 0
GROUP BY ""OID_TRANS_ORIGEN"")
AS E ON E.""OID_TRANS_ORIGEN"" = A.""OID""";
break;
case ETipoAcreedor.Despachante:
query += @"
LEFT JOIN (SELECT SUM(""G_DESP_TOTAL"") AS ""TOTAL_ESTIMADO""
,""OID_DESPACHANTE""
FROM " + e + @"
WHERE ""OID_FACTURA_DES"" = 0
GROUP BY ""OID_DESPACHANTE"")
AS E ON E.""OID_DESPACHANTE"" = A.""OID""";
break;
default:
query += @"
LEFT JOIN (SELECT 0 AS ""TOTAL_ESTIMADO""
FROM " + e + @"
WHERE FALSE)
AS E ON E.""TOTAL_ESTIMADO"" != 0";
break;
}
query += WHERE(conditions);
return query;
}
internal static string SELECT(long oid, bool lockTable)
{
return(ProviderBaseInfo.SELECT_BASE(oid, ETipoAcreedor.Naviera, lockTable));
}
public static string SELECT(QueryConditions conditions)
{
return(ProviderBaseInfo.SELECT_BASE(conditions, ETipoAcreedor.TransportistaDestino));
}
public static string SELECT(QueryConditions conditions)
{
return(ProviderBaseInfo.SELECT(conditions, conditions.TipoAcreedor));
}
