/////////////////////////////////////////////////////////////////////////// /// 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--; } }