public List <Estimate> GetAllEstimate(EstimateAdvanceSearch estimateAdvanceSearch) { return(_estimateRepository.GetAllEstimate(estimateAdvanceSearch)); }
public List <Estimate> GetAllEstimate(EstimateAdvanceSearch estimateAdvanceSearch) { List <Estimate> estimateList = null; try { using (SqlConnection con = _databaseFactory.GetDBConnection()) { using (SqlCommand cmd = new SqlCommand()) { if (con.State == ConnectionState.Closed) { con.Open(); } cmd.Connection = con; cmd.CommandText = "[PSA].[GetAllEstimate]"; if (string.IsNullOrEmpty(estimateAdvanceSearch.SearchTerm)) { cmd.Parameters.AddWithValue("@SearchTerm", DBNull.Value); } else { cmd.Parameters.Add("@SearchTerm", SqlDbType.NVarChar, -1).Value = estimateAdvanceSearch.SearchTerm.Trim(); } cmd.Parameters.Add("@RowStart", SqlDbType.Int).Value = estimateAdvanceSearch.DataTablePaging.Start; if (estimateAdvanceSearch.DataTablePaging.Length == -1) { cmd.Parameters.AddWithValue("@Length", DBNull.Value); } else { cmd.Parameters.Add("@Length", SqlDbType.Int).Value = estimateAdvanceSearch.DataTablePaging.Length; } cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = estimateAdvanceSearch.AdvFromDate; cmd.Parameters.Add("@Todate", SqlDbType.DateTime).Value = estimateAdvanceSearch.AdvToDate; if (estimateAdvanceSearch.AdvCustomerID == Guid.Empty) { cmd.Parameters.AddWithValue("@CustomerID", DBNull.Value); } else { cmd.Parameters.Add("@CustomerID", SqlDbType.UniqueIdentifier).Value = estimateAdvanceSearch.AdvCustomerID; } cmd.Parameters.Add("@AreaCode", SqlDbType.Int).Value = estimateAdvanceSearch.AdvAreaCode; cmd.Parameters.Add("@ReferencePersonCode", SqlDbType.Int).Value = estimateAdvanceSearch.AdvReferencePersonCode; cmd.Parameters.Add("@BranchCode", SqlDbType.Int).Value = estimateAdvanceSearch.AdvBranchCode; cmd.Parameters.Add("@DocumentStatusCode", SqlDbType.Int).Value = estimateAdvanceSearch.AdvDocumentStatusCode; if (estimateAdvanceSearch.AdvDocumentOwnerID == Guid.Empty) { cmd.Parameters.AddWithValue("@DocumentOwnerID", DBNull.Value); } else { cmd.Parameters.Add("@DocumentOwnerID", SqlDbType.UniqueIdentifier).Value = estimateAdvanceSearch.AdvDocumentOwnerID; } cmd.CommandType = CommandType.StoredProcedure; using (SqlDataReader sdr = cmd.ExecuteReader()) { if ((sdr != null) && (sdr.HasRows)) { estimateList = new List <Estimate>(); while (sdr.Read()) { Estimate estimate = new Estimate(); { estimate.ID = (sdr["ID"].ToString() != "" ? Guid.Parse(sdr["ID"].ToString()) : estimate.ID); estimate.EnquiryID = (sdr["EnquiryID"].ToString() != "" ? Guid.Parse(sdr["EnquiryID"].ToString()) : estimate.EnquiryID); estimate.EstimateNo = (sdr["EstimateNo"].ToString() != "" ? sdr["EstimateNo"].ToString() : estimate.EstimateNo); estimate.EstimateRefNo = (sdr["EstimateRefNo"].ToString() != "" ? sdr["EstimateRefNo"].ToString() : estimate.EstimateRefNo); estimate.EstimateDate = (sdr["EstimateDate"].ToString() != "" ? DateTime.Parse(sdr["EstimateDate"].ToString()) : estimate.EstimateDate); estimate.EstimateDateFormatted = (sdr["EstimateDate"].ToString() != "" ? DateTime.Parse(sdr["EstimateDate"].ToString()).ToString(_settings.DateFormat) : estimate.EstimateDateFormatted); estimate.Enquiry = new Enquiry(); estimate.Enquiry.EnquiryNo = (sdr["EnquiryNo"].ToString() != "" ? sdr["EnquiryNo"].ToString() : estimate.Enquiry.EnquiryNo); estimate.Customer = new Customer(); estimate.Customer.CompanyName = (sdr["CompanyName"].ToString() != "" ? sdr["CompanyName"].ToString() : estimate.Customer.CompanyName); estimate.Customer.ContactPerson = (sdr["ContactPerson"].ToString() != "" ? sdr["ContactPerson"].ToString() : estimate.Customer.ContactPerson); estimate.DocumentStatus = new DocumentStatus(); estimate.DocumentStatus.Description = (sdr["DocumentStatusDescription"].ToString() != "" ? sdr["DocumentStatusDescription"].ToString() : estimate.DocumentStatus.Description); estimate.Branch = new Branch(); estimate.Branch.Description = (sdr["BranchCode"].ToString() != "" ? sdr["BranchCode"].ToString() : estimate.Branch.Description); estimate.Area = new Area(); estimate.Area.Description = (sdr["Area"].ToString() != "" ? (sdr["Area"].ToString()) : estimate.Area.Description); estimate.ReferencePerson = new ReferencePerson(); estimate.ReferencePerson.Name = (sdr["ReferencePerson"].ToString() != "" ? (sdr["ReferencePerson"].ToString()) : estimate.ReferencePerson.Name); estimate.UserName = (sdr["DocumentOwner"].ToString() != "" ? sdr["DocumentOwner"].ToString() : estimate.UserName); estimate.TotalCount = (sdr["TotalCount"].ToString() != "" ? int.Parse(sdr["TotalCount"].ToString()) : estimate.TotalCount); estimate.FilteredCount = (sdr["FilteredCount"].ToString() != "" ? int.Parse(sdr["FilteredCount"].ToString()) : estimate.FilteredCount); } estimateList.Add(estimate); } } } } } } catch (Exception ex) { throw ex; } return(estimateList); }