public List <Smct2> Get_Smct2_List(Smct request) { List <Smct2> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (!string.IsNullOrEmpty(request.TrxNo)) { string strSelect = "SELECT * FROM Smct2 WHERE TrxNo=" + int.Parse(request.TrxNo); string strOrderBy = " ORDER BY LineItemNo ASC"; string strSQL = strSelect + strOrderBy; Result = db.Select <Smct2>(strSQL); } } } catch { throw; } return(Result); }
public List <Smct1> Get_Smct1_SpsList(Smct request) { List <Smct1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection()) { int count = int.Parse(request.RecordCount); string strWhere = ""; string strFilter = ""; if (!string.IsNullOrEmpty(request.TableType)) { if (string.Equals(request.TableType, "S")) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " ISNULL(TableType,'') = 'S'"; } else if (string.Equals(request.TableType, "C") || string.Equals(request.TableType, "SC")) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " (ISNULL(TableType,'') = 'C' Or ISNULL(TableType,'') = 'SC')"; } } if (!string.IsNullOrEmpty(request.PartyName)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " PartyCode in (Select BusinessPartyCode From Rcbp1 Where BusinessPartyName LIKE '" + request.PartyName + "%')"; } if (!string.IsNullOrEmpty(request.PortOfLoadingCode)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " PortOfLoadingCode in (Select PortCode From Rcsp1 Where PortName LIKE '" + request.PortOfLoadingCode + "%')"; } if (!string.IsNullOrEmpty(request.PortOfDischargeCode)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " PortOfDischargeCode in (Select PortCode From Rcsp1 Where PortName LIKE '" + request.PortOfDischargeCode + "%')"; } if (!string.IsNullOrEmpty(request.ModuleCode)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " ModuleCode LIKE '" + request.ModuleCode + "%'"; } if (!string.IsNullOrEmpty(request.JobType)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " JobType in (Select JobType From Jmjt1 Where JobDescription LIKE '" + request.JobType + "%'"; } if (!string.IsNullOrEmpty(request.EffectiveDate)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " convert(varchar(10),EffectiveDate,120) LIKE '" + request.EffectiveDate + "%'"; } if (!string.IsNullOrEmpty(request.ExpiryDate)) { if (strFilter.Length > 0) { strFilter = strFilter + " And "; } strFilter = strFilter + " convert(varchar(10),ExpiryDate,120) LIKE '" + request.ExpiryDate + "%'"; } if (strFilter.Length > 0) { strWhere = strWhere + " Where " + strFilter; } string strSelect = "SELECT " + "s1.*" + " FROM Smct1 s1," + "(SELECT TOP " + (count + 20) + " row_number() OVER (ORDER BY TrxNo ASC) n, TrxNo FROM Smct1 " + strWhere + ") s2" + " WHERE s1.TrxNo = s2.TrxNo AND s2.n > " + count; string strOrderBy = " ORDER BY s2.n ASC"; string strSQL = strSelect + strOrderBy; Result = db.Select <Smct1>(strSQL); } } catch { throw; } return(Result); }