public IEnumerable <Shipper> GetCRMShippersByConditionNoPaging(ShipperSearchCondition 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_GetShipperByConditionNoPaging", dbParams).ConvertToEntityCollection <Shipper>());
}
public IEnumerable <Shipper> GetCRMShippersByCondition(ShipperSearchCondition 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_GetShipperByCondition", dbParams);
rowCount = (int)dbParams[3].Value;
return(dt.ConvertToEntityCollection <Shipper>());
}
public ActionResult Index( )
{
ShipperIndexViewModel vm = new ShipperIndexViewModel();
ShipperSearchCondition ss = new ShipperSearchCondition();
vm.ShipperSearchCondition = ss;
//vm.PageIndex = 0;
//vm.PageCount = 0;
vm.ShowEditButton = true;
//if (useSession.HasValue && useSession.Value)
//{
//if (Session["ShipperCRM_SearchCondition"] != null)
//{
// vm.ShipperSearchCondition = (ShipperSearchCondition)Session["ShipperCRM_SearchCondition"];
// vm.PageIndex = Session["ShipperCRM_PageIndex"] != null ? (int)Session["ShipperCRM_PageIndex"] : 0;
//bukan
if (!string.IsNullOrEmpty(vm.ShipperSearchCondition.TransportMode))
{
IList <SelectListItem> selectedtransportModeTypes = new List <SelectListItem>();
vm.ShipperSearchCondition.TransportMode.Split('|').Each((i, s) =>
{
selectedtransportModeTypes.Add(new SelectListItem()
{
Text = s, Value = s
});
});
vm.SelectedTransportModes = selectedtransportModeTypes;
}
if (!string.IsNullOrEmpty(vm.ShipperSearchCondition.ProductType))
{
IList <SelectListItem> selectedProductTypes = new List <SelectListItem>();
vm.ShipperSearchCondition.ProductType.Split('|').Each((i, s) =>
{
selectedProductTypes.Add(new SelectListItem()
{
Text = s, Value = s
});
});
vm.SelectedProductTypes = selectedProductTypes;
}
//else
//{
// vm.ShipperSearchCondition = new ShipperSearchCondition();
// vm.PageIndex = 0;
//}
var getCRMShippersByConditionResponse = new ShipperManagementService().GetShippersByCondition(new GetShippersByConditionRequest()
{
SearchCondition = vm.ShipperSearchCondition,
PageSize = UtilConstants.PAGESIZE,
PageIndex = vm.PageIndex,
});
if (getCRMShippersByConditionResponse.IsSuccess)
{
vm.ShipperCollection = getCRMShippersByConditionResponse.Result.ShipperCollection;
vm.PageIndex = getCRMShippersByConditionResponse.Result.PageIndex;
vm.PageCount = getCRMShippersByConditionResponse.Result.PageCount;
}
//else
//{
// vm.ShipperSearchCondition = new ShipperSearchCondition();
//}
return(View(vm));
}
private string GenGetCRMShipperWhere(ShipperSearchCondition SearchCondition)
{
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(SearchCondition.Name))
{
sb.Append(" AND a.Name like '%").Append(SearchCondition.Name.Trim()).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());
}
