/// <summary>
/// Builds the SQL query string.
/// </summary>
/// <returns>The joined Sql</returns>
private string BuildSqlQueryString()
{
StringBuilder sql = new StringBuilder();
sql.Append(@"
SELECT ReceiveHeaderId,
(
CASE
WHEN Status = 0 THEN 'HOLD'
WHEN Status = 1 THEN 'POST'
END
) AS Status, TxNumber, TxDate, StaffNumber, ");
sql.Append(" Location, SupplierCode, ");
sql.Append(" CreatedOn, CreatedBy, ModifiedOn, ModifiedBy ");
sql.Append(" FROM vwReceivingList ");
sql.Append(" WHERE 1 = 1 AND ");
switch (SelectedViewIndex)
{
case 0: //// Last 7 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 1: //// Last 14 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 2: //// Last 30 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 3: //// Last 60 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 4: //// Last 90 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 5: //// All
default:
sql.Append(" 1 = 1 ");
break;
}
if (!string.IsNullOrEmpty(SearchForText))
{
string objTxNumber = PurchasingUtils.GenSafeChars(SearchForText);
sql.Append(" AND TxNumber LIKE '%").Append(objTxNumber).Append("%'");
}
if (SelectedStaff != System.Guid.Empty)
{
sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'");
}
sql.Append("ORDER BY TxNumber");
return(sql.ToString());
}
/// <summary>
/// Builds the SQL query string.
/// </summary>
/// <returns>The joined Sql</returns>
private string BuildSqlQueryString()
{
StringBuilder sql = new StringBuilder();
//// 2008.03.24 paulus: TxType is not that useful, changed to show the Header.Status
//// sql.Append("SELECT HeaderId, TxType, TxNumber, TxDate, StaffNumber, ");
sql.Append(@"
SELECT OrderHeaderId,
(
CASE
WHEN Status = 0 THEN 'HOLD'
WHEN Status = 1 THEN 'POST'
END
) AS Status, OrderNumber, OrderOn, StaffNumber, ");
sql.Append(" Location, SupplierCode, Remarks1, Remarks2, Remarks3, ");
sql.Append(" CreatedOn, CreatedBy, ModifiedOn, ModifiedBy ");
sql.Append(" FROM vwPurchaseOrderList ");
sql.Append(" WHERE PostedBy = '00000000-0000-0000-0000-000000000000' AND ");
switch (SelectedViewIndex)
{
case 0: //// Last 7 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-7).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 1: //// Last 14 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-14).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 2: //// Last 30 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-30).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 3: //// Last 60 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-60).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 4: //// Last 90 days
sql.Append(" CreatedOn BETWEEN CAST('").Append(DateTime.Today.AddDays(-90).ToString("yyyy-MM-dd 00:00:00")).Append("' AS DATETIME)");
sql.Append(" AND CAST('").Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")).Append("' AS DATETIME)");
break;
case 5: //// All
default:
sql.Append(" 1 = 1 ");
break;
}
if (!string.IsNullOrEmpty(SearchForText))
{
string objOrderNumber = PurchasingUtils.GenSafeChars(SearchForText);
sql.Append(" AND OrderNumber LIKE '%").Append(objOrderNumber).Append("%'");
}
if (SelectedStaff != System.Guid.Empty)
{
sql.Append(" AND CreatedBy = '").Append(SelectedStaff.ToString()).Append("'");
}
sql.Append("ORDER BY OrderNumber");
return(sql.ToString());
}