internal static string SELECT(QueryConditions conditions, bool lockTable)
{
string em = nHManager.Instance.GetSQLTable(typeof(CompanyRecord));
string su = nHManager.Instance.GetSQLTable(typeof(SchemaUserRecord));
string us = nHManager.Instance.GetSQLTable(typeof(UserSettingRecord));
string query;
query =
SELECT_FIELDS(conditions) +
JOIN(conditions) +
WHERE(conditions);
if (conditions != null)
{
if (conditions.Orders == null)
{
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Code", ListSortDirection.Ascending, typeof(Company)));
}
query += ORDER(conditions.Orders, "EM", ForeignFields());
query += LIMIT(conditions.PagingInfo);
}
//if (lock_table) query += " FOR UPDATE OF I NOWAIT";
return(query);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "PO", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "PO");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "PO");
/*if (conditions.Municipio != null)
* query += @"
* AND PO.""OID"" = " + conditions.Municipio.Oid;*/
if (conditions.Acreedor != null)
{
query += @"
AND PO.""OID_TRANSPORTISTA"" = " + conditions.Acreedor.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "SU", ForeignFields());
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "AG");
if (conditions.AgentDocument != null)
{
query += @"
AND AG.""OID"" = " + conditions.AgentDocument.Oid;
}
if (conditions.Agent != null)
{
query += @"
AND AG.""OID_AGENTE"" = " + conditions.Agent.Oid;
}
if (conditions.Document != null)
{
query += @"
AND AG.""OID_DOCUMENTO"" = " + conditions.Document.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "MO", ForeignFields());
/*query += @"
* AND (MO.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";*/
query += EntityBase.STATUS_LIST_CONDITION(conditions.Status, "MO");
query += EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "MO");
if (conditions.Monitor != null)
{
if (conditions.Monitor.Oid != -1)
{
query += @"
AND MO.""OID"" = " + conditions.Monitor.Oid;
}
if (conditions.Monitor.ComponentSerial != string.Empty)
{
query += @"
AND MO.""COMPONENT_SERIAL"" = '" + conditions.Monitor.ComponentSerial + "'";
}
if (conditions.Monitor.ComponentType != 0)
{
query += @"
AND MO.""COMPONENT_TYPE"" = " + conditions.Monitor.ComponentType;
}
}
return(query + " " + conditions.ExtraWhere);
}
internal static string SELECT(QueryConditions conditions, bool lockTable)
{
string query =
FIELDS(conditions) +
JOIN(conditions) +
WHERE(conditions);
if (conditions != null)
{
if (conditions.Step != EStepGraph.None)
{
query += @"
GROUP BY ""STEP""
ORDER BY ""STEP""" ;
}
else
{
if (conditions.Orders == null)
{
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Till", ListSortDirection.Descending, typeof(CreditCardStatement)));
}
query += ORDER(conditions.Orders, "CCS", ForeignFields());
query += LIMIT(conditions.PagingInfo);
}
}
query += EntityBase.LOCK("CCS", lockTable);
return(query);
}
internal static string SELECT(QueryConditions conditions, bool lockTable)
{
string rn = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.PayrollBatchRecord));
string nm = nHManager.Instance.GetSQLTable(typeof(moleQule.Store.Data.PayrollRecord));
string query;
query =
SELECT_FIELDS() + @"
FROM " + rn + @" AS RN
LEFT JOIN (SELECT ""OID_REMESA""
,SUM(""NETO"") AS ""NETO""
,SUM(""BASE_IRPF"" * ""P_IRPF"" / 100) AS ""IRPF""
FROM " + nm + @"
WHERE ""ESTADO"" != " + (long)EEstado.Anulado + @"
GROUP BY ""OID_REMESA"")
AS NM ON NM.""OID_REMESA"" = RN.""OID""" +
WHERE(conditions);
if (conditions.Orders == null)
{
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Fecha", ListSortDirection.Descending, typeof(PayrollBatch)));
conditions.Orders.Add(FilterMng.BuildOrderItem("Codigo", ListSortDirection.Descending, typeof(PayrollBatch)));
}
query += ORDER(conditions.Orders, string.Empty, ForeignFields());
query += LIMIT(conditions.PagingInfo);
query += Common.EntityBase.LOCK("RN", lockTable);
return(query);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "KI", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "KI");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "KI");
if (conditions.Kit != null)
{
query += @"
AND KI.""OID_KIT"" = " + conditions.Kit.Oid;
}
if (conditions.Producto != null)
{
query += @"
AND KI.""OID_PRODUCTO"" = " + conditions.Producto.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "S", ForeignFields());
query += moleQule.Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "S");
if (conditions.Serie != null)
{
query += @"
AND S.""OID"" = " + conditions.Serie.Oid;
}
switch (conditions.SerieType)
{
case ETipoSerie.Todas: break;
case ETipoSerie.Compra:
case ETipoSerie.Venta:
query += @"
AND (S.""TIPO"" IN (" + (long)conditions.SerieType + @"," + (long)ETipoSerie.CompraVenta + "))";
break;
default:
query += @"
AND S.""TIPO"" = " + (long)conditions.SerieType;
break;
}
return(query += " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "RA", ForeignFields());
// query = @"
// AND (RA.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "RA");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "RA");
if (conditions.RazaAnimal != null)
{
query += @"
AND R.""OID"" = " + conditions.RazaAnimal.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "EM", ForeignFields());
if (conditions.Schema != null)
{
if (conditions.Schema.Oid != 0)
{
query += @"
AND EM.""OID"" = " + conditions.Schema.Oid;
}
if (conditions.Schema.Code != string.Empty)
{
query += @"
AND EM.""CODIGO"" = '" + conditions.Schema.Code + "'";
}
}
if (conditions.User != null && !conditions.User.IsAdmin)
{
query += " AND SU.\"OID_USER\" = " + conditions.User.Oid;
}
if (conditions.User != null && conditions.User.IsAdmin)
{
query += " AND SU.\"OID_USER\" = 1";
}
return(query + " " + conditions.ExtraWhere);
}
internal static string ORDER(QueryConditions conditions)
{
if (conditions.Orders == null)
{
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Fecha", ListSortDirection.Descending, typeof(PayrollBatch)));
conditions.Orders.Add(FilterMng.BuildOrderItem("Codigo", ListSortDirection.Descending, typeof(PayrollBatch)));
}
return(ORDER(conditions.Orders, string.Empty, ForeignFields()));
}
internal static string SELECT_BY_PAYMENT(QueryConditions conditions, bool lockTable)
{
conditions.ExtraWhere = @"
AND (PA.""OID"" = " + conditions.OidEntity + ")";
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Till", ListSortDirection.Ascending, typeof(CreditCardStatement)));
string query =
SELECT(conditions, lockTable);
return(query);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = " WHERE (TR.\"CREATED\" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "TR", ForeignFields());
query += @"
AND (TR.""CREATED"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += EntityBase.STATUS_LIST_CONDITION(conditions.Status, "TR");
query += EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "TR");
if (conditions.Transaction != null)
{
if (conditions.Transaction.Oid != 0)
{
query += @"
AND TR.""OID"" = " + conditions.Transaction.Oid;
}
if (conditions.Transaction.TransactionID != string.Empty)
{
query += @"
AND TR.""TRANSACTIONID"" = '" + conditions.Transaction.TransactionID + "'";
}
if (conditions.Transaction.ETransactionType != ETransactionType.All)
{
query += @"
AND TR.""TYPE"" = " + (long)conditions.Transaction.ETransactionType;
}
}
if (conditions.EntityType != ETipoEntidad.Todos)
{
query += @"
AND TR.""ENTITY_TYPE"" = " + (long)conditions.EntityType;
if (conditions.Oid != 0)
{
query += @"
AND TR.""OID_ENTITY"" = " + conditions.Oid;
}
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "C", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "C");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "C");
if (conditions.Caja != null)
{
query += @"
AND C.""OID"" = " + conditions.Caja.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "DT", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "DT");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "DT");
/*if (conditions.Municipio != null)
* query += @"
* AND P.""OID"" = " + conditions.Municipio.Oid;*/
return(query + " " + conditions.ExtraWhere);
}
internal static string SELECT_BY_EXPEDIENTS(QueryConditions conditions, bool lockTable)
{
conditions.ExtraWhere += @"
AND WR.""OID_EXPEDIENT"" IN " + Common.EntityBase.GET_IN_STRING(conditions.OidList);
conditions.OidList = null;
conditions.Orders = new OrderList();
conditions.Orders.Add(FilterMng.BuildOrderItem("Expedient", ListSortDirection.Ascending, typeof(Expedient)));
conditions.Orders.Add(FilterMng.BuildOrderItem("Date", ListSortDirection.Descending, typeof(WorkReport)));
string query =
SELECT(conditions, lockTable);
return(query);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "CU", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "CU");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "CU");
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "WO", ForeignFields());
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "WO");
if (conditions.WorkReportCategory != null)
{
query += @"
AND W.""OID"" = " + conditions.WorkReportCategory.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "WO", ForeignFields());
query += @"
AND (WO.""FROM"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "WO");
if (conditions.WorkReport != null)
{
query += @"
AND WO.""OID_WORK_REPORT"" = " + conditions.WorkReport.Oid;
}
if (conditions.EntityType != ETipoEntidad.Todos)
{
query += @"
AND WO.""ENTITY_TYPE"" = " + (long)conditions.EntityType;
}
if (conditions.Expedient != null)
{
query += @"
AND WR.""OID_EXPEDIENT"" = " + conditions.Expedient.Oid;
}
if (conditions.WorkReportCategory != null)
{
query += @"
AND WC.""OID"" = " + conditions.WorkReportCategory.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "SU", ForeignFields());
query = @"
AND (CL.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "CL");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "CL");
if (conditions.TipoCliente != null)
{
query += @"
AND C.""OID"" = " + conditions.TipoCliente.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "RE", ForeignFields());
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "RE");
if (conditions.Relation != null)
{
if (conditions.Relation.OidParent != 0)
{
query += @"
AND RE.""OID_PARENT"" = " + conditions.Relation.OidParent + @"
AND RE.""PARENT_TYPE"" = " + conditions.Relation.ParentType;
}
if (conditions.Relation.OidChild != 0)
{
query += @"
AND RE.""OID_CHILD"" = " + conditions.Relation.OidChild + @"
AND RE.""CHILD_TYPE"" = " + conditions.Relation.ChildType;
}
if ((conditions.Relation.OidParent == 0) && (conditions.Relation.OidChild == 0))
{
query += @"
AND RE.""OID"" = " + conditions.Relation.Oid;
}
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "WR", ForeignFields());
query += @"
AND (WR.""FROM"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.NO_NULL_RECORDS_CONDITION("WR");
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "WR");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "WR");
if (conditions.WorkReport != null)
{
query += @"
AND WR.""OID"" = " + conditions.WorkReport.Oid;
}
if (conditions.WorkReportCategory != null)
{
query += @"
AND WR.""CATEGORY"" = " + conditions.WorkReportCategory.Oid;
}
if (conditions.Expedient != null)
{
query += @"
AND WR.""OID_EXPEDIENT"" = " + conditions.Expedient.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "PU", ForeignFields());
// query += @"
// AND (PU.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "PU");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "PU");
if (conditions.PuertoDespachante != null)
{
query += @"
AND PU.""OID"" = " + conditions.PuertoDespachante.Oid;
}
if (conditions.Acreedor != null)
{
query += @"
AND PU.""OID_DESPACHANTE"" = " + conditions.Acreedor.Oid;
}
if (conditions.Puerto != null)
{
query += @"
AND PU.""OID_PUERTO"" = " + conditions.Puerto.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "ML", ForeignFields());
query += @"
AND (ML.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "ML");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "ML");
if (conditions.MonitorLine != null)
{
if (conditions.MonitorLine.Oid != -1)
{
query += @"
AND ML.""OID"" = " + conditions.MonitorLine.Oid;
}
}
if (conditions.Monitor != null)
{
if (conditions.Monitor.Oid != -1)
{
query += @"
AND ML.""OID_MONITOR"" = " + conditions.Monitor.Oid;
}
}
return(query + " " + conditions.ExtraWhere);
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "PO", ForeignFields());
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "PO");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "PO");
if (conditions.Naviera != null)
{
query += @"
AND PO.""OID_NAVIERA"" = " + conditions.Naviera.Oid;
}
return(query + " " + conditions.ExtraWhere);
}
internal static string SELECT_UNPAID(QueryConditions conditions, bool lockTable)
{
conditions.ExtraWhere = @"
AND (PF1.""TOTAL_PAGADO"" != CCS.""AMOUNT"" OR PF1.""TOTAL_PAGADO"" IS NULL)
AND (CCS.""DUE_DATE"" BETWEEN '" + conditions.FechaAuxIniLabel + "' AND '" + conditions.FechaAuxFinLabel + @"')
AND COALESCE(CCS.""AMOUNT"" - PF1.""TOTAL_PAGADO"", CCS.""AMOUNT"") != 0" ;
if (conditions.Orders == null)
{
conditions.Orders = new OrderList()
{
FilterMng.BuildOrderItem("DueDate", ListSortDirection.Ascending, typeof(CreditCardStatement))
}
}
;
string query =
SELECT(conditions, lockTable);
return(query);
}
#endregion
}
internal static string WHERE(QueryConditions conditions)
{
if (conditions == null)
{
return(string.Empty);
}
string query;
query = @"
WHERE " + FilterMng.GET_FILTERS_SQL(conditions.Filters, "TI", ForeignFields());
query = @"
AND (TI.""DATE"" BETWEEN '" + conditions.FechaIniLabel + "' AND '" + conditions.FechaFinLabel + "')";
query += Common.EntityBase.STATUS_LIST_CONDITION(conditions.Status, "TI");
query += Common.EntityBase.GET_IN_LIST_CONDITION(conditions.OidList, "TI");
/*if (conditions.TIPOANIMAL != null)
* query += @"
* AND T.""OID"" = " + conditions.TIPOANIMAL.Oid;*/
return(query + " " + conditions.ExtraWhere);
}
public static string GET_FILTERS(FilterList filters, string tableAlias)
{
return(FilterMng.GET_FILTERS_SQL(filters, tableAlias));
}
