///////////////////////////////////////////////////////////////////////////
/// METHODS FOR JLinersReadyToLine
///
//
// GetJLinersReadyToLineByCompanyIdCompaniesID
//
public TDSJLinersReadyToLine GetJLinersReadyToLineByCompanyIDCompaniesID(int companyId, bool all_clients, int companiesId)
{
if ((all_clients == true) && (companiesId == 0))
{
//--- Get data for JLinersReadyToLine
return GetJLinersReadyToLineByCompanyID(companyId);
}
else
{
SqlConnection connection = new SqlConnection(ConnectionString);
TDSJLinersReadyToLine dataSet = new TDSJLinersReadyToLine();
//--- Select master data
string commandText = "SELECT m.ID, m.COMPANY_ID, m.COMPANIES_ID, c.Name, m.SubArea, m.Street, m.USMH, m.DSMH, m.ConfirmedSize, m.ActualLength, m.P1Date, m.RecordID , m.TrafficControlDetails, m.Comments FROM LFS_MASTER_AREA m INNER JOIN COMPANIES c on m.Company_ID = c.Company_ID and m.Companies_ID = c.Companies_ID WHERE (ID in ( SELECT DISTINCT ma.ID FROM LFS_MASTER_AREA ma INNER JOIN LFS_JUNCTION_LINER j ON ma.ID = j.ID and ma.COMPANY_ID = j.COMPANY_ID WHERE (ma.COMPANY_ID = @companyId) AND (ma.COMPANIES_ID = @companiesId) AND (ma.Deleted = 0) AND (ma.Archived = 0) AND (j.Deleted = 0) AND (j.Archived = 0))) ORDER BY m.RecordID";
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add(new SqlParameter("@companyId", companyId));
command.Parameters.Add(new SqlParameter("@companiesId", companiesId));
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet, "JLinersReadyToLine");
//--- Select detail data
commandText = "SELECT m.ID, j.RefID, m.COMPANY_ID, j.DetailID, j.MN, j.DistanceFromUSMH, j.CleanoutInstalled, j.PitInstalled, j.PrepCompleted, j.Transition, j.ConfirmedLatSize, j.MeasuredLatLength, j.LinerInstalled, j.LinerInStock, j.Comments FROM LFS_MASTER_AREA m INNER JOIN LFS_JUNCTION_LINER j ON m.ID = j.ID AND m.COMPANY_ID = j.COMPANY_ID WHERE (m.COMPANY_ID = @companyId) AND (m.COMPANIES_ID = @companiesId) AND (m.Deleted = 0) AND (m.Archived = 0) AND (j.Deleted = 0) AND (j.Archived = 0) ORDER BY m.RecordID, j.DetailID";
command = new SqlCommand(commandText, connection);
command.Parameters.Add(new SqlParameter("@companyId", companyId));
command.Parameters.Add(new SqlParameter("@companiesId", companiesId));
SqlDataAdapter dataAdapter1 = new SqlDataAdapter(command);
dataAdapter1.Fill(dataSet, "LFS_JUNCTION_LINER");
//--- Filter data with report conditions
FilterJLinersReadyToLine(dataSet);
//--- Return results
return dataSet;
}
}
//
// FilterJLinersReadyToLine
//
private void FilterJLinersReadyToLine(TDSJLinersReadyToLine dataSet)
{
//--- Initialize
int indexMA = 0;
int recordsMA = dataSet.JLinersReadyToLine.Count;
//--- Move in MASTER_AREA records
while (recordsMA > 0)
{
//--- Initialize
int numJL = 0; //rows in LFS_JUNCTION_LINER
int numC1 = 0; //rows in LFS_JUNCTION_LINER with MeauredLatLength and LinerInStock condition
int numC2 = 0; //rows in LFS_JUNCTION_LINER with LinerInstalled condition
TDSJLinersReadyToLine.JLinersReadyToLineRow rowMA = dataSet.JLinersReadyToLine[indexMA];
//--- Move in LFS_JUNCTION_LINER records
foreach (TDSJLinersReadyToLine.LFS_JUNCTION_LINERRow lfsJunctionLinerRow in dataSet.LFS_JUNCTION_LINER)
{
//--- Verify condition
if ((lfsJunctionLinerRow.ID == rowMA["ID"].ToString()) && (lfsJunctionLinerRow.COMPANY_ID.ToString() == rowMA["COMPANY_ID"].ToString()))
{
numJL++;
if ((!lfsJunctionLinerRow.IsNull("MeasuredLatLength")) && (lfsJunctionLinerRow.LinerInStock == true))
{
numC1++;
if (lfsJunctionLinerRow.IsNull("LinerInstalled"))
{
numC2++;
}
}
}
}
//--- Move forward or delete LFS_MASTER_AREA record
if ((numJL == numC1) && (numC2>0))
{
indexMA++;
}
else
{
rowMA.Delete();
dataSet.AcceptChanges();
}
recordsMA--;
}
}