public void UpdateWorks(bool bMachines, bool bUsers, bool bNew, bool bConfirm, bool bInProduciton, bool bEnd,
bool bDate, int dateType, DateTime from, DateTime to)
{
var filter = "";
var filterStatus = "";
if (bMachines)
{
if (SelectedMachines.Count > 0)
{
filter = " WHERE MachineID IN (" + string.Join(",", SelectedMachines.OfType <Int32>().ToArray()) + ")";
}
else
{
filter = " WHERE PlannedWorkID =-1";
}
}
if (bUsers)
{
if (SelectedUsers.Count > 0)
{
if (filter.Length > 0)
{
filter +=
" AND PlannedWorkID IN (SELECT PlannedWorkID FROM PlannedWorkUsers WHERE UserID IN (" +
string.Join(",", SelectedUsers.OfType <Int32>().ToArray()) + "))";
}
else
{
filter =
" WHERE PlannedWorkID IN (SELECT PlannedWorkID FROM PlannedWorkUsers WHERE UserID IN (" +
string.Join(",", SelectedUsers.OfType <Int32>().ToArray()) + "))";
}
}
else
{
filter = " WHERE PlannedWorkID =-1";
}
}
if (!bNew && !bConfirm && !bInProduciton && !bEnd)
{
filterStatus = "PlannedWorkStatusID=-1";
}
else
{
if (bNew)
{
filterStatus = "PlannedWorkStatusID=1";
}
if (bConfirm)
{
if (filterStatus.Length > 0)
{
filterStatus += " OR PlannedWorkStatusID=2";
}
else
{
filterStatus = "PlannedWorkStatusID=2";
}
}
if (bInProduciton)
{
if (filterStatus.Length > 0)
{
filterStatus += " OR PlannedWorkStatusID=3";
}
else
{
filterStatus = "PlannedWorkStatusID=3";
}
}
if (bEnd)
{
if (filterStatus.Length > 0)
{
filterStatus += " OR PlannedWorkStatusID=4";
}
else
{
filterStatus = "PlannedWorkStatusID=4";
}
}
}
if (bDate)
{
if (dateType == 0)
{
if (filter.Length > 0)
{
filter += " AND CAST(StartDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(StartDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
else
{
filter = " WHERE CAST(StartDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(StartDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
}
if (dateType == 1)
{
if (filter.Length > 0)
{
filter += " AND CAST(CreateDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(CreateDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
else
{
filter = " WHERE CAST(CreateDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(CreateDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
}
if (dateType == 2)
{
if (filter.Length > 0)
{
filter += " AND CAST(EndDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(EndDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
else
{
filter = " WHERE CAST(EndDate AS DATE) >= '" + from.ToString("yyyy-MM-dd") +
"' AND CAST(EndDate AS DATE) <= '" + to.ToString("yyyy-MM-dd") + "' ";
}
}
}
if (filter.Length > 0)
{
filter += " AND (" + filterStatus + ")";
}
else
{
filter = " WHERE " + filterStatus;
}
var selectCommand = @"SELECT * FROM PlannedWorks " + filter;
using (var da = new SqlDataAdapter(selectCommand, ConnectionStrings.LightConnectionString))
{
_plannedWorksDt.Clear();
da.Fill(_plannedWorksDt);
}
}