public int CountBapiret2ByCriteria(SapLog ret2, string DocNo, string PostingDate, string Type)
{
return(NHibernateQueryHelper.CountByCriteria(BapiQueryProvider.Bapiret2Query, "FindBapiret2ByCriteria", new object[] { ret2, true, string.Empty, DocNo, PostingDate, Type }));
}
public ISQLQuery FindBapiret2ByCriteria(SapLog ret2, bool isCount, string sortExpression, string DocNo, string PostingDate, string Type)
{
QueryParameterBuilder parameterBuilder = new QueryParameterBuilder();
StringBuilder sqlBuilder = new StringBuilder();
#region Old Code
// if (!isCount)
// {
// #region Select
// sqlBuilder.Append(@"
// SELECT
// BAPIRET2.Id1 AS Id,
// BAPIRET2.Doc_Id AS DocId,
// [Document].DocumentNo AS DocNo,
// BAPIRET2.Doc_Seq AS DocSeq,
// BAPIACHE09.Doc_Year AS DocYear,
// CONVERT(VARCHAR(10),CONVERT(DATETIME,BAPIACHE09.Pstng_Date),103) AS PostingDate,
// SuUser.EmployeeCode AS EmployeeCode,
// SuUser.EmployeeName AS EmployeeName,
// '[' + SuUser.EmployeeCode + '] ' + SuUser.EmployeeName AS Employee,
// DbCompany.CompanyCode AS CompanyCode,
// DbCompany.CompanyName AS CompanyName,
// '[' + DbCompany.CompanyCode + '] ' + DbCompany.CompanyName AS Company,
// CASE
// WHEN BAPIRET2.Type = 'S' THEN 'Success'
// WHEN BAPIRET2.Type = 'E' THEN 'Error'
// WHEN BAPIRET2.Type = 'W' THEN 'Warning'
// ELSE 'Error'
// END AS Type,
// BAPIRET2.Message AS Message
// FROM
// DbCompany AS DbCompany
// RIGHT OUTER JOIN
// BAPIACHE09 AS BAPIACHE09
// RIGHT OUTER JOIN
// BAPIRET2 AS BAPIRET2 ON
// BAPIACHE09.Doc_Id = BAPIRET2.Doc_Id AND
// BAPIACHE09.Doc_Seq = BAPIRET2.Doc_Seq
// LEFT OUTER JOIN
// [Document] AS Document ON
// BAPIRET2.Doc_Id = [Document].DocumentID
// LEFT OUTER JOIN
// SuUser AS SuUser ON
// [Document].RequesterID = SuUser.Userid ON
// DbCompany.CompanyId = [Document].CompanyId
// WHERE 1=1
// ");
// #endregion Select
// }
// else
// {
// #region Count
// sqlBuilder.Append(@"
// SELECT
// COUNT(BAPIRET2.Id1) AS SapLogCount
// FROM
// DbCompany AS DbCompany
// RIGHT OUTER JOIN
// BAPIACHE09 AS BAPIACHE09
// RIGHT OUTER JOIN
// BAPIRET2 AS BAPIRET2 ON
// BAPIACHE09.Doc_Id = BAPIRET2.Doc_Id AND
// BAPIACHE09.Doc_Seq = BAPIRET2.Doc_Seq
// LEFT OUTER JOIN
// [Document] AS Document ON
// BAPIRET2.Doc_Id = [Document].DocumentID
// LEFT OUTER JOIN
// SuUser AS SuUser ON
// [Document].RequesterID = SuUser.Userid ON
// DbCompany.CompanyId = [Document].CompanyId
// WHERE 1=1
// ");
// #endregion Count
// }
#endregion Old Code
if (!isCount)
{
#region Select
sqlBuilder.Append(@"
SELECT
VIEW_POST_LOG.Id AS Id,
VIEW_POST_LOG.DocId AS DocId,
VIEW_POST_LOG.PostingType AS PostingType,
VIEW_POST_LOG.DocNo AS DocNo,
VIEW_POST_LOG.DocSeq AS DocSeq,
VIEW_POST_LOG.DocYear AS DocYear,
VIEW_POST_LOG.PostingDate AS PostingDate,
VIEW_POST_LOG.FiDoc AS FiDoc,
VIEW_POST_LOG.EmployeeCode AS EmployeeCode,
VIEW_POST_LOG.EmployeeName AS EmployeeName,
VIEW_POST_LOG.Employee AS Employee,
VIEW_POST_LOG.CompanyCode AS CompanyCode,
VIEW_POST_LOG.CompanyName AS CompanyName,
VIEW_POST_LOG.Company AS Company,
VIEW_POST_LOG.Type AS Type,
VIEW_POST_LOG.MESSAGE AS Message
FROM
VIEW_POST_LOG AS VIEW_POST_LOG
WHERE 1=1
");
#endregion Select
}
else
{
#region Count
sqlBuilder.Append(@"
SELECT
COUNT(VIEW_POST_LOG.Id) AS SapLogCount
FROM
VIEW_POST_LOG AS VIEW_POST_LOG
WHERE 1=1
");
#endregion Count
}
#region เงื่อนไข
if (!string.IsNullOrEmpty(DocNo))
{
sqlBuilder.Append(" AND VIEW_POST_LOG.DocNo = :DocNo ");
parameterBuilder.AddParameterData("DocNo", typeof(string), DocNo);
}
if (!string.IsNullOrEmpty(PostingDate))
{
sqlBuilder.Append(" AND VIEW_POST_LOG.PostingDateString = :PostingDate ");
parameterBuilder.AddParameterData("PostingDate", typeof(string), PostingDate);
}
if (!string.IsNullOrEmpty(Type))
{
sqlBuilder.Append(" AND VIEW_POST_LOG.TypeString = :Type ");
parameterBuilder.AddParameterData("Type", typeof(string), Type);
}
#endregion เงื่อนไข
#region Order By
if (!isCount)
{
if (string.IsNullOrEmpty(sortExpression))
{
sqlBuilder.AppendLine(" ORDER BY PostingDate,EmployeeCode,DocNo,DocSeq,PostingType,DocYear,CompanyCode,FiDoc,Type,Message ");
}
else
{
sqlBuilder.AppendLine(string.Format(" ORDER BY {0}", sortExpression));
}
}
#endregion Order By
ISQLQuery query = GetCurrentSession().CreateSQLQuery(sqlBuilder.ToString());
parameterBuilder.FillParameters(query);
if (!isCount)
{
query.AddScalar("Id", NHibernateUtil.Int64);
query.AddScalar("DocId", NHibernateUtil.Int64);
query.AddScalar("PostingType", NHibernateUtil.String);
query.AddScalar("DocNo", NHibernateUtil.String);
query.AddScalar("DocSeq", NHibernateUtil.String);
query.AddScalar("DocYear", NHibernateUtil.String);
query.AddScalar("PostingDate", NHibernateUtil.DateTime);
query.AddScalar("FiDoc", NHibernateUtil.String);
query.AddScalar("Employee", NHibernateUtil.String);
query.AddScalar("EmployeeCode", NHibernateUtil.String);
query.AddScalar("EmployeeName", NHibernateUtil.String);
query.AddScalar("Company", NHibernateUtil.String);
query.AddScalar("CompanyCode", NHibernateUtil.String);
query.AddScalar("CompanyName", NHibernateUtil.String);
query.AddScalar("Type", NHibernateUtil.String);
query.AddScalar("Message", NHibernateUtil.String);
query.SetResultTransformer(Transformers.AliasToBean(typeof(SapLog)));
}
else
{
query.AddScalar("SapLogCount", NHibernateUtil.Int32);
query.UniqueResult();
}
return(query);
}
public IList <SapLog> GetBapiret2ByCriteria(SapLog ret2, int firstResult, int maxResult, string sortExpression, string DocNo, string PostingDate, string Type)
{
return(NHibernateQueryHelper.FindPagingByCriteria <SapLog>(BapiQueryProvider.Bapiret2Query, "FindBapiret2ByCriteria", new object[] { ret2, false, sortExpression, DocNo, PostingDate, Type }, firstResult, maxResult, sortExpression));
}
