public void AddNormalPolicySetting(DataTransferObject.Policy.NormalPolicySetting view)
{
int i = 0;
string sql = "";
string sql1 = "";
foreach (var item in view.Berths.Split(','))
{
i++;
sql += @"DELETE FROM dbo.T_NormalPolicySetting WHERE PolicyId = @delPolicyId" + i + " AND Berths=@delBerths" + i + " AND FlightsFilter=@delFlightsFilter" + i + ";";
sql1 += @"INSERT INTO [dbo].[T_NormalPolicySetting]
([Id],[PolicyId],[FlightsFilter],[Berths],[Commission],[StartTime],[EndTime],[Remark],[Type],[Enable],[Creator])
VALUES(@Id" + i + ",@PolicyId" + i + ",@FlightsFilter" + i + ",@Berths" + i + ",@Commission" + i + ", @StartTime" + i + ",@EndTime" + i + ",@Remark" + i + ",@Type" + i + ",@Enable" + i + ",@Creator" + i + ")";
AddParameter("@delPolicyId" + i, view.PolicyId);
AddParameter("@delBerths" + i, item);
AddParameter("@delFlightsFilter" + i, view.FlightsFilter);
AddParameter("@Id" + i, Guid.NewGuid());
AddParameter("@PolicyId" + i, view.PolicyId);
AddParameter("@FlightsFilter" + i, view.FlightsFilter);
AddParameter("@Berths" + i, item);
AddParameter("@Commission" + i, view.Commission);
AddParameter("@StartTime" + i, view.StartTime);
AddParameter("@EndTime" + i, view.EndTime);
AddParameter("@Remark" + i, view.Remark);
AddParameter("@Type" + i, view.Type);
AddParameter("@Enable" + i, true);
AddParameter("@Creator" + i, view.Creator);
}
if (!string.IsNullOrWhiteSpace(sql + sql1))
{
ExecuteNonQuery(sql + sql1);
}
}
public IEnumerable <DataTransferObject.Policy.NormalPolicySetting> QueryNormalPolicySetting(Guid?policyId, bool?Type, bool?Enable, string FlightsFilter, string Berths, DateTime?flightDate)
{
List <DataTransferObject.Policy.NormalPolicySetting> list = new List <DataTransferObject.Policy.NormalPolicySetting>();
string sql = "SELECT [Id],[PolicyId],[FlightsFilter],[Berths],[Commission],[StartTime],[EndTime],[Remark],[Type],[Enable],[Creator],[OperationTime] FROM dbo.T_NormalPolicySetting WHERE 1 = 1 ";
if (policyId != null)
{
sql += " AND PolicyId = @PolicyId ";
AddParameter("@PolicyId", policyId);
}
//AddParameter("@PolicyId", policyId);
if (Type != null)
{
sql += " AND Type=@Type";
AddParameter("@Type", Type);
}
if (Enable != null)
{
sql += " AND Enable=@Enable";
AddParameter("@Enable", Enable);
}
if (!string.IsNullOrEmpty(FlightsFilter))
{
sql += " AND (CHARINDEX(@FlightsFilter,FlightsFilter) > 0 OR CHARINDEX(@FlightsFilter1,FlightsFilter) > 0 OR CHARINDEX(@FlightsFilter2,FlightsFilter) > 0 OR CHARINDEX('**',FlightsFilter) > 0)";
AddParameter("@FlightsFilter", FlightsFilter);
AddParameter("@FlightsFilter1", FlightsFilter.Replace(FlightsFilter.Substring(0, 3), "*"));
AddParameter("@FlightsFilter2", FlightsFilter.Replace(FlightsFilter.Substring(3, 3), "*"));
}
if (!string.IsNullOrEmpty(Berths))
{
sql += " AND Berths=@Berths";
AddParameter("@Berths", Berths);
}
if (flightDate != null)
{
sql += " AND EndTime >= @EndTime AND StartTime<= @StartTime ";
AddParameter("@EndTime", flightDate.Value.Date);
AddParameter("@StartTime", flightDate.Value.Date);
}
sql += " ORDER BY OperationTime DESC";
using (var reader = ExecuteReader(sql))
{
while (reader.Read())
{
DataTransferObject.Policy.NormalPolicySetting setting = new DataTransferObject.Policy.NormalPolicySetting();
setting.Id = reader.GetGuid(0);
setting.PolicyId = reader.GetGuid(1);
setting.FlightsFilter = reader.GetString(2);
setting.Berths = reader.GetString(3);
setting.Commission = reader.GetDecimal(4);
setting.StartTime = reader.GetDateTime(5);
setting.EndTime = reader.GetDateTime(6);
setting.Remark = reader.GetString(7);
setting.Type = reader.GetBoolean(8);
setting.Enable = reader.GetBoolean(9);
setting.Creator = reader.GetString(10);
setting.OperationTime = reader.GetDateTime(11);
list.Add(setting);
}
}
return(list);
}