public IEnumerable <CRMShipper> GetCRMShippersByConditionNoPaging(CRMShipperSearchCondition SearchCondition) { string sqlWhere = this.GenGetCRMShipperWhere(SearchCondition); int tempRowCount = 0; DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input) }; return(this.ExecuteDataTable("Proc_GetCRMShipperByConditionNoPaging", dbParams).ConvertToEntityCollection <CRMShipper>()); }
public IEnumerable <CRMShipper> GetCRMShippersByCondition(CRMShipperSearchCondition SearchCondition, int pageIndex, int pageSize, out int rowCount) { string sqlWhere = this.GenGetCRMShipperWhere(SearchCondition); int tempRowCount = 0; DbParam[] dbParams = new DbParam[] { new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input), new DbParam("@PageIndex", DbType.Int32, pageIndex, ParameterDirection.Input), new DbParam("@PageSize", DbType.Int32, pageSize, ParameterDirection.Input), new DbParam("@RowCount", DbType.Int32, tempRowCount, ParameterDirection.Output) }; DataTable dt = this.ExecuteDataTable("Proc_GetCRMShipperByCondition", dbParams); rowCount = (int)dbParams[3].Value; return(dt.ConvertToEntityCollection <CRMShipper>()); }
private string GenGetCRMShipperWhere(CRMShipperSearchCondition SearchCondition) { StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(SearchCondition.Name)) { sb.Append(" AND a.Name like '%").Append(SearchCondition.Name).Append("%' "); } if (!string.IsNullOrEmpty(SearchCondition.Attribution)) { sb.Append(" AND a.Attribution='").Append(SearchCondition.Attribution).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.RegisteredCapitalRange)) { sb.Append(" AND a.RegisteredCapitalRange='").Append(SearchCondition.RegisteredCapitalRange).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.AnnualTurnoverRange)) { sb.Append(" AND a.AnnualTurnoverRange='").Append(SearchCondition.AnnualTurnoverRange).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.TrunkOfVehicleType)) { sb.Append(" AND a.TrunkOfVehicleType like '%").Append(SearchCondition.TrunkOfVehicleType).Append("%' "); } if (!string.IsNullOrEmpty(SearchCondition.FrequencyOfDeparture)) { sb.Append(" AND a.FrequencyOfDeparture='").Append(SearchCondition.FrequencyOfDeparture).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.TrunkOfVehicleRange)) { sb.Append(" AND a.TrunkOfVehicleRange='").Append(SearchCondition.TrunkOfVehicleRange).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.DeliveryOfVehicleRange)) { sb.Append(" AND a.DeliveryOfVehicleRange='").Append(SearchCondition.DeliveryOfVehicleRange).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.WarehouseAreaRange)) { sb.Append(" AND a.WarehouseAreaRange='").Append(SearchCondition.WarehouseAreaRange).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.Recommended)) { sb.Append(" AND a.Recommended='").Append(SearchCondition.Recommended).Append("' "); } if (!string.IsNullOrEmpty(SearchCondition.StartPlaceIDs)) { StringBuilder startPlaceSB = new StringBuilder(); using (SqlConnection conn = new SqlConnection(BaseAccessor._dataBase.ConnectionString)) { DataTable dtable = new DataTable(); SqlCommand cmd = new SqlCommand("Proc_GetReginAndSunRegionsByRegionIDs", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EndCityIDs", SearchCondition.StartPlaceIDs.Split(',').Select(c => new IdsForInt64(c.ObjectToInt64()))); cmd.Parameters[0].SqlDbType = SqlDbType.Structured; SqlDataAdapter Adp = new SqlDataAdapter(cmd); Adp.Fill(dtable); for (int i = 0; i < dtable.Rows.Count; i++) { startPlaceSB.Append(dtable.Rows[i][0].ToString()).Append(","); } startPlaceSB.Remove(startPlaceSB.Length - 1, 1); sb.Append(" AND b.StartCityID IN (").Append(startPlaceSB.ToString()).Append(") "); } } if (!string.IsNullOrEmpty(SearchCondition.EndPlaceIDs)) { StringBuilder endPlaceSB = new StringBuilder(); using (SqlConnection conn = new SqlConnection(BaseAccessor._dataBase.ConnectionString)) { DataTable dtable = new DataTable(); SqlCommand cmd = new SqlCommand("Proc_GetReginAndSunRegionsByRegionIDs", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EndCityIDs", SearchCondition.EndPlaceIDs.Split(',').Select(c => new IdsForInt64(c.ObjectToInt64()))); cmd.Parameters[0].SqlDbType = SqlDbType.Structured; SqlDataAdapter Adp = new SqlDataAdapter(cmd); Adp.Fill(dtable); for (int i = 0; i < dtable.Rows.Count; i++) { endPlaceSB.Append(dtable.Rows[i][0].ToString()).Append(","); } endPlaceSB.Remove(endPlaceSB.Length - 1, 1); sb.Append(" AND b.EndCityID IN (").Append(endPlaceSB.ToString()).Append(") "); } } if (!string.IsNullOrEmpty(SearchCondition.CoverRegionIDs)) { var CoverRegionIDs = SearchCondition.CoverRegionIDs.Split(','); sb.Append(" AND b.CoverRegionID IN ("); CoverRegionIDs.Each((i, s) => { sb.Append(s).Append(","); }); sb.Remove(sb.Length - 1, 1); sb.Append(") "); } if (!string.IsNullOrEmpty(SearchCondition.TransportMode)) { var transportModes = SearchCondition.TransportMode.Split('|'); sb.Append(" AND ("); transportModes.Each((i, t) => { sb.Append("a.TransportMode like '%").Append(t).Append("%' ").Append(" OR "); }); sb.Remove(sb.Length - 3, 3); sb.Append(") "); } if (!string.IsNullOrEmpty(SearchCondition.ProductType)) { var productTypes = SearchCondition.ProductType.Split('|'); sb.Append(" AND ("); productTypes.Each((i, t) => { sb.Append("c.Str7 like '%").Append(t).Append("%' ").Append(" OR "); }); sb.Remove(sb.Length - 3, 3); sb.Append(") "); } if (!string.IsNullOrEmpty(SearchCondition.KeyWord)) { sb.Append(" AND (").Append("c.Name like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR a.Name like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Remark like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str1 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str2 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str3 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str4 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str5 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str6 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR c.Str7 like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR a.AnnualTurnover like '%").Append(SearchCondition.KeyWord.Trim()) .Append("%' OR a.Remark like '%").Append(SearchCondition.KeyWord.Trim()).Append("%')"); } if (!string.IsNullOrEmpty(SearchCondition.PartnerShipType)) { sb.Append(" AND a.PartnershipTypes = '").Append(SearchCondition.PartnerShipType).Append("' "); } return(sb.ToString()); }