public IEnumerable <SettledPod> GetSettledPodsByCondition(SettledPodSearchCondition SearchCondition)
{
string endCities = string.Empty;
if (SearchCondition.EndCities != null && SearchCondition.EndCities.IndexOf(',') > 0)
{
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.EndCities.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++)
{
endCities += dtable.Rows[i][0].ToString() + ",";
}
endCities = endCities.Substring(0, endCities.Length - 1);
}
}
string sqlWhere = this.GenGetSettledPodsWhere(SearchCondition, endCities);
DbParam[] dbParams = new DbParam[] {
new DbParam("@Where", DbType.String, sqlWhere, ParameterDirection.Input)
};
return(this.ExecuteDataTable("Proc_GetSettledPodsByCondition", dbParams).ConvertToEntityCollection <SettledPod>());
}
private string GenGetSettledPodsWhere(SettledPodSearchCondition SearchCondition, string endCities)
{
StringBuilder sb = new StringBuilder();
sb.Append(" where ProjectID=").Append(SearchCondition.ProjectID).Append(" ");
sb.Append(" and SettledType=").Append(SearchCondition.SettledType).Append(" ");
if (!string.IsNullOrEmpty(SearchCondition.SystemNumber))
{
IEnumerable <string> systemNumbers = Enumerable.Empty <string>();
if (SearchCondition.SystemNumber.IndexOf("\n") > 0)
{
systemNumbers = SearchCondition.SystemNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.SystemNumber.IndexOf(',') > 0)
{
systemNumbers = SearchCondition.SystemNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (systemNumbers != null && systemNumbers.Any())
{
sb.Append(" and SystemNumber in ( ");
foreach (string s in systemNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and SystemNumber like '%").Append(SearchCondition.SystemNumber.Trim()).Append("%' ");
}
}
if (!string.IsNullOrEmpty(SearchCondition.CustomerOrderNumber))
{
IEnumerable <string> customerOrderNumbers = Enumerable.Empty <string>();
if (SearchCondition.CustomerOrderNumber.IndexOf("\n") > 0)
{
customerOrderNumbers = SearchCondition.CustomerOrderNumber.Split('\n').Select(s => { return(s.Trim()); });
}
if (SearchCondition.CustomerOrderNumber.IndexOf(',') > 0)
{
customerOrderNumbers = SearchCondition.CustomerOrderNumber.Split(',').Select(s => { return(s.Trim()); });
}
if (customerOrderNumbers != null && customerOrderNumbers.Any())
{
sb.Append(" and CustomerOrderNumber in ( ");
foreach (string s in customerOrderNumbers)
{
sb.Append("'").Append(s).Append("',");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" ) ");
}
else
{
sb.Append(" and CustomerOrderNumber like '%" + SearchCondition.CustomerOrderNumber.Trim() + "%' ");
}
}
if (SearchCondition.CustomerOrShipperID != null && SearchCondition.CustomerOrShipperID != 0)
{
//sb.Append(" and CustomerOrShipperID=").Append(SearchCondition.CustomerOrShipperID).Append(" ");
sb.Append(" and RelatedCustomerID=").Append(SearchCondition.CustomerOrShipperID).Append(" ");
}
if (SearchCondition.CustomerIDs != null && SearchCondition.CustomerIDs.Any())
{
sb.Append(" and RelatedCustomerID in (");
foreach (long i in SearchCondition.CustomerIDs)
{
sb.Append(i.ToString());
sb.Append(",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") ");
}
else
{
sb.Append(" and RelatedCustomerID=0 ");
}
if (SearchCondition.PODTypeID != null && SearchCondition.PODTypeID != 0)
{
sb.Append(" and PodTypeID=").Append(SearchCondition.PODTypeID).Append(" ");
}
if (SearchCondition.ShipperTypeID != null && SearchCondition.ShipperTypeID != 0)
{
sb.Append(" and ShipperTypeID=").Append(SearchCondition.ShipperTypeID).Append(" ");
}
if (SearchCondition.TtlOrTplID != null && SearchCondition.TtlOrTplID != 0)
{
sb.Append(" and TtlOrTplID=").Append(SearchCondition.TtlOrTplID).Append(" ");
}
if (SearchCondition.StartCityID != null && SearchCondition.StartCityID != 0)
{
sb.Append(" and StartCityID=").Append(SearchCondition.StartCityID).Append(" ");
}
if (SearchCondition.EndCityID != null && SearchCondition.EndCityID != 0)
{
sb.Append(" and EndCityID in (SELECT ID FROM Func_GetReginAndSunRegions(" + SearchCondition.EndCityID + ")) ");
}
else
{
if (!string.IsNullOrEmpty(endCities))
{
sb.Append(" and EndCityID in (").Append(endCities).Append(") ");
}
}
if (SearchCondition.ActualDeliveryDate.HasValue)
{
sb.Append(" and ActualDeliveryDate >= '").Append(SearchCondition.ActualDeliveryDate.Value.DateTimeToString()).Append("' ");
}
if (SearchCondition.EndActualDeliveryDate.HasValue)
{
sb.Append(" and ActualDeliveryDate <= '").Append(SearchCondition.EndActualDeliveryDate.Value.DateTimeToString()).Append(" 23:59' ");
}
if (SearchCondition.InvoiceID > 0)
{
sb.Append(" and InvoiceID > 0 ");
}
else
{
sb.Append(" and (InvoiceID=0 or InvoiceID is NULL) ");
}
if (SearchCondition.IsAudit.HasValue)
{
sb.Append(" and IsAudit=" + (SearchCondition.IsAudit.Value ? "1" : "0") + " ");
}
if (!string.IsNullOrEmpty(SearchCondition.SystemNumberSufixx))
{
sb.Append(" and SystemNumber like '%" + SearchCondition.SystemNumberSufixx + "'");
}
if (SearchCondition.IsManualSettled && SearchCondition.IsForAudit)
{
sb.Append(" and Str3='1' ");
}
else if (SearchCondition.IsForAudit && !SearchCondition.IsManualSettled)
{
sb.Append(" and (Str3='0' OR Str3 IS NULL) ");
}
return(sb.ToString());
}