public static string SELECT_BUILDER(SelectCaller local_caller, Common.QueryConditions conditions)
{
string query = string.Empty;
string union = " UNION ";
string copy = string.Empty;
foreach (KeyValuePair <ETipoEntidad, TEntidadRegistroBase> entity in ModuleController.Instance.ActiveEntidades)
{
if (!entity.Value.Active)
{
continue;
}
copy = query;
conditions.TipoEntidad = entity.Key;
query += union + local_caller(conditions);
if (query == (copy + union))
{
query = copy;
}
}
query = query.Substring(union.Length);
return(query);
}
public static Invoice.QueryConditions ConvertTo(Common.QueryConditions conditions)
{
Invoice.QueryConditions conds = new Invoice.QueryConditions
{
FechaIni = conditions.FechaIni,
FechaFin = conditions.FechaFin,
FechaAuxIni = conditions.FechaAuxIni,
FechaAuxFin = conditions.FechaAuxFin,
Estado = conditions.Estado
};
return(conds);
}
public string GetConditions(Common.QueryConditions conditions)
{
string filtro = string.Empty;
if (conditions.FechaIni != DateTime.MinValue)
{
filtro += "Fecha Inicial: " + conditions.FechaIni.ToShortDateString() + "; ";
}
if (conditions.FechaFin != DateTime.MinValue)
{
filtro += "Fecha Final: " + conditions.FechaFin.ToShortDateString() + "; ";
}
filtro += "Estado: " + Common.EnumText <EEstado> .GetLabel(conditions.Estado) + "; ";
return(filtro);
}
public static Common.QueryConditions ConvertToCommonQuery(Store.QueryConditions conditions)
{
Common.QueryConditions conds = new Common.QueryConditions
{
Oid = conditions.Oid,
EntityType = conditions.EntityType,
Status = conditions.Status,
FechaIni = conditions.FechaIni,
FechaFin = conditions.FechaFin,
FechaAuxIni = conditions.FechaAuxIni,
FechaAuxFin = conditions.FechaAuxFin,
Estado = conditions.Estado
};
return(conds);
}
public static Common.QueryConditions ConvertTo(Invoice.QueryConditions conditions)
{
Common.QueryConditions conds = new Common.QueryConditions
{
Oid = conditions.Oid,
EntityType = conditions.EntityType,
Status = conditions.Status,
Year = conditions.Year,
FechaIni = conditions.FechaIni,
FechaFin = conditions.FechaFin,
FechaAuxIni = conditions.FechaAuxIni,
FechaAuxFin = conditions.FechaAuxFin,
Estado = conditions.Estado,
TipoEntidad = conditions.TipoEntidad,
Modelo = conditions.Modelo,
};
return(conds);
}
public static string SELECT_BUILDER(SelectCaller local_caller, ETipoEntidad[] list, Common.QueryConditions conditions)
{
string query = string.Empty;
string union = @"
UNION " ;
string copy = string.Empty;
/*foreach (KeyValuePair<ETipoEntidad, TEntidadRegistroBase> entity in ModuleController.Instance.ActiveEntidades)
* if (entity.Value.Active)
* {
* foreach (ETipoEntidad item in list)
* if (item == entity.Value.ETipoEntidad)
* {
* copy = query;
* conditions.TipoEntidad = item;
* query += union + local_caller(conditions);
*
* if (query == (copy + union)) query = copy;
* }
* }*/
TEntidadRegistroBase entity;
foreach (ETipoEntidad item in list)
{
if (!ModuleController.Instance.ActiveEntidades.ContainsKey(item))
{
continue;
}
entity = ModuleController.Instance.ActiveEntidades[item];
if (!entity.Active)
{
continue;
}
copy = query;
conditions.TipoEntidad = item;
query += union + local_caller(conditions);
if (query == (copy + union))
{
query = copy;
}
}
query = query.Substring(union.Length);
return(query);
}
public static string JOIN_PAYMENTS(Common.QueryConditions conditions, long[] paymentTypes, string tableAlias)
{
string pa = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.PaymentRecord));
string tp = nHManager.Instance.GetSQLTable(typeof(TransactionPaymentRecord));
string tipos = "(" + String.Join(",", paymentTypes) + ")";
long oid_pago = conditions.OidEntity;
// IMPORTE DE PAGOS ASOCIADOS
string query = @"
LEFT JOIN (SELECT PF.""OID_OPERACION""
,SUM(PF.""CANTIDAD"") AS ""TOTAL_PAGADO""
,PF.""TIPO_PAGO""
,MAX(PF.""OID_PAGO"") AS ""OID_PAGO""
FROM " + tp + @" AS PF
INNER JOIN " + pa + @" AS PA ON PA.""OID"" = PF.""OID_PAGO"" AND PF.""TIPO_PAGO"" IN " + tipos + @"
WHERE PA.""ESTADO"" != " + (long)EEstado.Anulado + @"
GROUP BY PF.""OID_OPERACION"", PF.""TIPO_PAGO"")
AS PF1 ON PF1.""OID_OPERACION"" = " + tableAlias + @".""OID""";
// IMPORTE PARCIAL DEL PAGO ASIGNADO A ESTE GASTO
query += @"
LEFT JOIN (SELECT PF.""OID_OPERACION""
,SUM(PF.""CANTIDAD"") AS ""ASIGNADO_PAGO""
,MAX(PF.""OID_PAGO"") AS ""OID_PAGO""
FROM " + tp + @" AS PF
INNER JOIN " + pa + @" AS PA ON PA.""OID"" = PF.""OID_PAGO"" AND PF.""TIPO_PAGO"" IN " + tipos + @"
WHERE PA.""ESTADO"" != " + (long)EEstado.Anulado;
if (oid_pago != 0)
{
query += @"
AND PA.""OID"" = " + oid_pago;
}
query += @"
GROUP BY PF.""OID_OPERACION"", PF.""TIPO_PAGO"")
AS PF2 ON PF2.""OID_OPERACION"" = " + tableAlias + @".""OID""";
// IMPORTE TOTAL ASIGNADO A ESTE GASTO POR TODOS LOS PAGOS
query += @"
LEFT JOIN (SELECT PF.""OID_OPERACION""
,SUM(PF.""CANTIDAD"") AS ""TOTAL_ASIGNADO""
,MAX(PF.""OID_PAGO"") AS ""OID_PAGO""
FROM " + tp + @" AS PF
INNER JOIN " + pa + @" AS PA ON PA.""OID"" = PF.""OID_PAGO"" AND PF.""TIPO_PAGO"" IN " + tipos + @"
WHERE PA.""ESTADO"" != " + (long)EEstado.Anulado + @"
AND (PA.""ESTADO_PAGO"" != " + (long)EEstado.Pagado + @" OR PA.""VENCIMIENTO"" > '" + DateTime.Today.ToString("MM/dd/yyyy") + @"')
GROUP BY PF.""OID_OPERACION"", PF.""TIPO_PAGO"")
AS PF3 ON PF3.""OID_OPERACION"" = " + tableAlias + @".""OID""";
// IMPORTE LIQUIDADO
query += @"
LEFT JOIN (SELECT PF.""OID_OPERACION""
,SUM(PF.""CANTIDAD"") AS ""TOTAL_LIQUIDADO""
,PF.""TIPO_PAGO""
,MAX(PF.""OID_PAGO"") AS ""OID_PAGO""
FROM " + tp + @" AS PF
INNER JOIN " + pa + @" AS PA ON PA.""OID"" = PF.""OID_PAGO"" AND PF.""TIPO_PAGO"" IN " + tipos + @"
WHERE PA.""ESTADO"" != " + (long)EEstado.Anulado + @"
AND PA.""ESTADO_PAGO"" = " + (long)EEstado.Pagado + @"
GROUP BY PF.""OID_OPERACION"", PF.""TIPO_PAGO"")
AS PF4 ON PF4.""OID_OPERACION"" = " + tableAlias + @".""OID""";
// PAGO ASOCIADO (EL ULTIMO)
query += @"
LEFT JOIN " + pa + @" AS PA ON PA.""OID"" = PF1.""OID_PAGO""";
return(query);
}
public static string SELECT(Common.QueryConditions conditions)
{
return(ModeloInfo.SELECT(conditions));
}
public static ModeloList GetList(Common.QueryConditions conditions, bool childs)
{
return(GetList(SELECT(conditions), childs));
}
