static public Court GetByECFCourtString(string ECFCourtString)
{
Court objCourt = new Court();
string query;
if (ECFCourtString.Contains("-"))
{
query = _selectViewSQL + " where CourtName like '" + ECFCourtString.Substring(0, ECFCourtString.IndexOf("-") - 4) + "%'";
}
else
{
query = _selectViewSQL + " where CourtName = '" + ECFCourtString + "'";
}
Database db = DatabaseFactory.CreateDatabase("BRGCaseMail");
using (IDataReader reader = db.ExecuteReader(CommandType.Text, query))
{
if (reader.Read())
{
LoadCourt(objCourt, reader);
// always call Close when done reading.
reader.Close();
objCourt.IsModified = false;
return(objCourt);
}
else
{
return(null);
}
}
}
static public void Save(Court objCourt, Database db, DbTransaction trans, bool CommitTrans)
{
try
{
if (objCourt.IsModified == true)
{
if (objCourt.ID == 0)
{
Insert(objCourt, db, trans);
}
else
{
Update(objCourt, db, trans);
}
}
if (CommitTrans == true)
{
trans.Commit();
}
}
catch (Exception e)
{
if (CommitTrans == true)
{
//we were hoping to commit the transaction which means we're at the end so
//Roll back the transaction. Otherwise just rethrow the error and let the next
//higher level catch it and rollback
trans.Rollback();
}
//and then rethrow the error
throw (e);
}
}
static public void Delete(Court objCourt)
{
Database db = DatabaseFactory.CreateDatabase("BRGCaseMail");
System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("p_CourtDelete");
db.AddInParameter(dbCommand, "ID", DbType.Int32, objCourt.ID);
db.ExecuteNonQuery(dbCommand);
}
static public void Save(Court objCourt)
{
if (objCourt.IsModified == true)
{
if (objCourt.ID == 0)
{
Insert(objCourt);
}
else
{
Update(objCourt);
}
}
}
static private void LoadCourt(Court objCourt, IDataReader _reader)
{
if (_reader.IsDBNull(0) != true)
{
objCourt.ID = _reader.GetInt32(0);
}
if (_reader.IsDBNull(1) != true)
{
objCourt.PacerFileFormatID = _reader.GetInt32(1);
}
if (_reader.IsDBNull(2) != true)
{
objCourt.StateCode = _reader.GetString(2);
}
if (_reader.IsDBNull(3) != true)
{
objCourt.CourtName = _reader.GetString(3);
}
if (_reader.IsDBNull(4) != true)
{
objCourt.URLAbbrv = _reader.GetString(4);
}
if (_reader.IsDBNull(5) != true)
{
objCourt.URLFull = _reader.GetString(5);
}
if (_reader.IsDBNull(6) != true)
{
objCourt.FilePrefix = _reader.GetString(6);
}
if (_reader.IsDBNull(7) != true)
{
objCourt.LastPacerLoadDischargeDate = _reader.GetDateTime(7);
}
if (_reader.IsDBNull(8) != true)
{
objCourt.LastPacerLoadFileDate = _reader.GetDateTime(8);
}
if (_reader.IsDBNull(9) != true)
{
objCourt.PacerFileVersion = _reader.GetString(9);
}
if (_reader.IsDBNull(10) != true)
{
objCourt.PacerFileVersionNumColumns = _reader.GetInt32(10);
}
}
public Court Copy()
{
Court _court = new Court();
_court.ID = _iD;
_court.PacerFileFormatID = _pacerFileFormatID;
_court.StateCode = _stateCode;
_court.CourtName = _courtName;
_court.URLAbbrv = _uRLAbbrv;
_court.URLFull = _uRLFull;
_court.FilePrefix = _filePrefix;
_court.LastPacerLoadDischargeDate = _lastPacerLoadDischargeDate;
_court.LastPacerLoadFileDate = _lastPacerLoadFileDate;
_court.PacerFileVersion = _pacerFileVersion;
_court.PacerFileVersionNumColumns = _pacerFileVersionNumColumns;
return(_court);
}
static public string CheckECFVersions()
{
ECFVersionByCourt _version = new ECFVersionByCourt();
List <ECFVersion> _versions = _version.GetBankruptcyCourtVersions();
int iUnknownFormat = 0;
int iUnknownCourt = 0;
int iUpdated = 0;
int iMatch = 0;
foreach (ECFVersion _ecf in _versions)
{
PacerFileFormat _format = PacerFileFormatService.GetByVersionString(_ecf.versionString);
if (_format == null)
{
iUnknownFormat++;
}
else
{
Court _icourt = CourtService.GetByECFCourtString(_ecf.courtName);
//if ( _icourt.CourtName == "Illinois Central")
// Debug.WriteLine(_icourt.CourtAndFileVersion);
if (_icourt != null && _icourt.PacerFileFormatID == _format.ID)
{
iMatch++;
}
else if (_icourt != null)
{
_icourt.PacerFileFormatID = _format.ID;
_icourt.PacerFileVersion = _format.PacerFileVersion;
CourtService.Save(_icourt);
iUpdated++;
}
else
{
iUnknownCourt++;
}
}
}
return(iUpdated + " courts updated, " + iMatch + " court versions matched, " + iUnknownFormat + " pacer file formats unknown, " + iUnknownCourt + " Courts unknown ");
}
static public List <Court> GetAll()
{
List <Court> objCourts = new List <Court>();
Database db = DatabaseFactory.CreateDatabase("BRGCaseMail");
using (IDataReader reader = db.ExecuteReader(CommandType.Text, _selectViewSQL))
{
while (reader.Read())
{
Court objCourt = new Court();
LoadCourt(objCourt, reader);
objCourt.IsModified = false;
objCourts.Add(objCourt);
}
// always call Close when done reading.
reader.Close();
return(objCourts);
}
}
static public Court Refresh(Court objCourt)
{
string query = _selectViewSQL + " where ID = " + objCourt.ID;
Database db = DatabaseFactory.CreateDatabase("BRGCaseMail");
using (IDataReader reader = db.ExecuteReader(CommandType.Text, query))
{
if (reader.Read())
{
LoadCourt(objCourt, reader);
// always call Close when done reading.
reader.Close();
objCourt.IsModified = false;
return(objCourt);
}
else
{
return(null);
}
}
}
static private void Insert(Court objCourt)
{
Database db = DatabaseFactory.CreateDatabase("BRGCaseMail");
System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("p_CourtInsert");
db.AddOutParameter(dbCommand, "ID", DbType.Int32, 4);
if (objCourt.PacerFileFormatID > 0)
{
db.AddInParameter(dbCommand, "PacerFileFormatID", DbType.Int32, objCourt.PacerFileFormatID);
}
else
{
db.AddInParameter(dbCommand, "PacerFileFormatID", DbType.Int32, Convert.DBNull);
}
db.AddInParameter(dbCommand, "StateCode", DbType.String, objCourt.StateCode);
db.AddInParameter(dbCommand, "CourtName", DbType.String, objCourt.CourtName);
db.AddInParameter(dbCommand, "URLAbbrv", DbType.String, objCourt.URLAbbrv);
db.AddInParameter(dbCommand, "URLFull", DbType.String, objCourt.URLFull);
db.AddInParameter(dbCommand, "FilePrefix", DbType.String, objCourt.FilePrefix);
if (objCourt.LastPacerLoadDischargeDate > DateTime.MinValue)
{
db.AddInParameter(dbCommand, "LastPacerLoadDischargeDate", DbType.DateTime, objCourt.LastPacerLoadDischargeDate);
}
else
{
db.AddInParameter(dbCommand, "LastPacerLoadDischargeDate", DbType.DateTime, Convert.DBNull);
}
if (objCourt.LastPacerLoadFileDate > DateTime.MinValue)
{
db.AddInParameter(dbCommand, "LastPacerLoadFileDate", DbType.DateTime, objCourt.LastPacerLoadFileDate);
}
else
{
db.AddInParameter(dbCommand, "LastPacerLoadFileDate", DbType.DateTime, Convert.DBNull);
}
db.ExecuteNonQuery(dbCommand);
objCourt.ID = Int32.Parse(db.GetParameterValue(dbCommand, "ID").ToString());
}
public bool DownloadNewCases(bool GeoCodeAddresses)
{
//ceck if we've established conection and queried for the data
bool _status = false;
//we may be calling Downloadnewcases without the intermediate prompt that QueryCases allows.\
//if so call it first.
if (_result == null)
{
_status = QueryNewCases();
}
if (_status == true || _result != null)
{
try
{
string _rawData = _bankruptcyParser.PurchaseCases(_result);
if (_rawData == null || _rawData.Length == 0)
{
_importStatus = "NO DATA";
return(false);
}
//_transaction.RawData = "text";
//string _rawData = "text";
//create the filepath
string newPath = System.IO.Path.Combine(ConfigurationManager.AppSettings.Get("PACERFilePath"), _court.FilePrefix);
System.IO.Directory.CreateDirectory(newPath);
_filePath = newPath + @"\" + _court.FilePrefix + _startDate.ToString("MMddyyyy") + "_" + _endDate.ToString("MMddyyyy") + ".txt";
System.IO.File.WriteAllText(_filePath, _rawData);
PacerImportTransactionService.Save(this);
//if (_court == null)
//{
// _court = CourtService.GetByID(this.CourtID);
//}
// ZipGeoCodeService.UpdateStatsForState(_court.StateCode);
//execute the dts package
//Load DTSX
//Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//Package package = app.LoadPackage(System.Configuration.ConfigurationManager.AppSettings.Get("PACERImportDTSXFile"), null);
////Specify Excel Connection From DTSX Connection Manager
//Debug.WriteLine(package.Connections["SourceConnectionFlatFile"].ConnectionString);
//package.Connections["SourceConnectionFlatFile"].ConnectionString = _filePath;
//Debug.WriteLine(package.Connections["DestinationConnectionOLEDB"].ConnectionString);
//package.Connections["DestinationConnectionOLEDB"].ConnectionString = ConfigurationManager.AppSettings["DTSConnectionString"];
//// //Execute DTSX.
//Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
//if (results == DTSExecResult.Success)
//{
// //yay!
// BRGCaseMailServer.PacerImportTransactionService.ProcessImportTransaction(this);
//}
//else
//{
// string _message = string.Empty;
// foreach (DtsError _err in package.Errors)
// {
// _message += _err.Description + ". ";
// }
// _message += "Please see you system adminstrator. ";
// //dts did not work so load the file and parse manually
// _importMessage = "SSIS Import Failed with the error:" + _message + ". Trying Manaul import...";
if (BRGCaseMailServer.PacerImportTransactionService.ProcessManualImportTransaction(this) == true)
{
//process imported lines
int rows = PacerImportTransactionService.ProcessImportedLineItems(this, GeoCodeAddresses);
if (_court == null)
{
_court = CourtService.GetByID(this.CourtID);
}
if (this._dischargedCases == true)
{
_court.LastPacerLoadDischargeDate = this.EndDate;
}
else
{
_court.LastPacerLoadFileDate = this.EndDate;
}
CourtService.Save((Court)_court);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
_importMessage = ex.Message;
_importStatus = "FAILURE";
PacerImportTransactionService.Save(this);
return(false);
}
}
else
{
PacerImportTransactionService.Save(this);
return(false); //end if (_status == true || _result != null)
}
}
public bool Reprocess(bool GeoCodeAddresses)
{
//ceck if we've established conection and queried for the data
if (this.FilePath.Length > 0)
{
try
{
// Read the file as one string.
System.IO.StreamReader myFile = new System.IO.StreamReader(this.FilePath);
string _rawData = myFile.ReadToEnd();
myFile.Close();
if (_rawData == null || _rawData.Length == 0)
{
_importStatus = "NO DATA";
return(false);
}
//delete all imported data but don;t bother deleting bankruptcy case data as it will get written over
//if already exists
PacerImportDataService.DeleteForTransaction(this.ID);
//get the court
//Court _court = CourtService.GetByID(this.CourtID);
//this.PacerFileFormatID = _format.ID;
PacerFileFormat _format = PacerFileFormatService.GetByID(this.PacerFileFormatID);
//on a reprocess we use the current format for the court
if (BRGCaseMailServer.PacerImportTransactionService.ProcessManualImportTransaction(this, _format) == true)
{
//process imported lines
int rows = PacerImportTransactionService.ProcessImportedLineItems(this, GeoCodeAddresses);
if (_court == null)
{
_court = CourtService.GetByID(this.CourtID);
}
if (this._dischargedCases == true)
{
_court.LastPacerLoadDischargeDate = this.EndDate;
}
else
{
_court.LastPacerLoadFileDate = this.EndDate;
}
CourtService.Save((Court)_court);
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
this.ImportMessage = ex.Message;
this.ImportStatus = "FAILURE";
PacerImportTransactionService.Save(this);
return(false);
}
}
else
{
PacerImportTransactionService.Save(this);
return(false); //end if (_status == true || _result != null)
}
}
public bool QueryNewCases()
{
o = new CaseSearchOptions();
string user = System.Configuration.ConfigurationManager.AppSettings.Get("PACERuser");
string pass = System.Configuration.ConfigurationManager.AppSettings.Get("PACERPassword");
_court = CourtService.GetByID(_courtID);
_bankruptcyParser = new BankruptcyParser(_court.URLAbbrv, user, pass);
// Login to the system
if (!_bankruptcyParser.Login())
{
_importMessage = "Could Not Login: "******"FAILURE";
_court = CourtService.GetByID(_courtID);
_bankruptcyParser = new BankruptcyParser(_court.URLAbbrv, user, pass);
if (!_bankruptcyParser.NextGenLogin())
{
return(false);
}
else
{
return(true);
}
}
// setup the search paramters
o.open_cases = true;
o.closed_cases = true;
o.party_information = true;
//set the chapters and discharged/filed
o.chapter = new List <CaseSearchOptions.chapters>();
if (_dischargedCases == true)
{
o.date_type = CaseSearchOptions.date_types.discharged;
o.chapter.Add(CaseSearchOptions.chapters.seven);
o.chapter.Add(CaseSearchOptions.chapters.thirteen);
}
else
{
o.date_type = CaseSearchOptions.date_types.filed;
o.chapter.Add(CaseSearchOptions.chapters.seven);
}
//set the case type
o.case_type = new List <CaseSearchOptions.case_types>();
o.case_type.Add(CaseSearchOptions.case_types.bk);
//Set the start and end dates.
o.start = DateTime.Parse(_startDate.ToString());
o.end = DateTime.Parse(_endDate.ToString());
_result = _bankruptcyParser.FindCases(o);
if (_result == null)
{
_importMessage = "Could Not Find Cases:" + _bankruptcyParser.lastError;
_importStatus = "FAILURE";
PacerImportTransactionService.Save(this);
return(false);
}
else
{
this.BillablePages = _result.billable_pages;
this.Cost = Decimal.Parse(_result.cost.ToString());
SearchCriteria = _result.criteria;
DownloadTimeStamp = DateTime.Now;
PacerImportTransactionService.Save(this);
//don't set the status and message yet as we still have to import...
return(true);
}
}
static public int ProcessImportedLineItems(PacerImportTransaction objPacerImportTransaction, bool GeocodeLocations)
{
List <PacerImportData> _data = PacerImportDataService.GetForImportTransaction(objPacerImportTransaction.ID);
PacerImportData _previousRow = null;
int insertedRowCount = 0;
int updatedRowCount = 0;
int formatErrors = 0;
string StateCode = null;
foreach (PacerImportData _row in _data)
{
if (_previousRow == null)
{
//first row - is this row a debtor - if this row fails we just exit with failure because the
//file format is most likely incorrect.
if (((_row.PartyCaseRole.ToUpper() == "DEBTOR") || (_row.PartyCaseRole.ToUpper() == "CO-DEBTOR") || (_row.PartyCaseRole.ToUpper() == "JOINT DEBTOR")) && _row.CareOfOK)
{
_row.Imported = true;
PacerImportDataService.Save(_row);
try
{
BankruptcyCase _case = new BankruptcyCase(_row);
Debug.WriteLine(_case.CaseNumber4Digit);
if (GeocodeLocations)
{
YahooGeoCoder.GeocodeCase(_case);
}
_case.IsModified = true;
int returnvalue = BankruptcyCaseService.Save(_case, true);
if (returnvalue == 1)
{
insertedRowCount++;
}
else if (returnvalue == 2)
{
updatedRowCount++;
}
}
catch (Exception ex)
{
formatErrors++;
}
}
_previousRow = _row;
}
else //previousrow != null if this row fails we do not exit with failure because the
// it's just a bad row.
{
if (_row.PartyAddressLine1 != _previousRow.PartyAddressLine1 && ((_row.PartyCaseRole.ToUpper() == "DEBTOR") || (_row.PartyCaseRole.ToUpper() == "CO-DEBTOR") || (_row.PartyCaseRole.ToUpper() == "JOINT DEBTOR")) && _row.CareOfOK)
{
try
{
//debtor at different address
_row.Imported = true;
PacerImportDataService.Save(_row);
BankruptcyCase _case = new BankruptcyCase(_row);
if (GeocodeLocations)
{
YahooGeoCoder.GeocodeCase(_case);
}
if (insertedRowCount == 318)
{
Debug.WriteLine(_case.CaseNumber4Digit);
}
if (_case.LastName == "Babakatis")
{
Debug.WriteLine(_case.CaseNumber4Digit);
}
_case.IsModified = true;
int returnvalue = BankruptcyCaseService.Save(_case, true);
if (returnvalue == 1)
{
insertedRowCount++;
if (insertedRowCount == 1654)
{
Debug.WriteLine(insertedRowCount);
}
}
else if (returnvalue == 2)
{
updatedRowCount++;
}
}
catch (Exception e)
{
formatErrors++;
}
}
_previousRow = _row;
}
}
// update counts for
objPacerImportTransaction.InsertedCases = insertedRowCount;
objPacerImportTransaction.LineCount = _data.Count;
objPacerImportTransaction.TotalCases = insertedRowCount + updatedRowCount;
if (objPacerImportTransaction.ImportMessage.Length > 0)
{
//we already have an import string - parse the result to add the previous total
try
{
if (objPacerImportTransaction.ImportMessage.IndexOf("Unique Cases Added") > 0)
{
int previoustotal = Int32.Parse(objPacerImportTransaction.ImportMessage.Substring(0, objPacerImportTransaction.ImportMessage.IndexOf("Unique Cases Added") - 1));
objPacerImportTransaction.ImportMessage = previoustotal + insertedRowCount + " Unique Cases Added! " + updatedRowCount + " Cases Updated! " + formatErrors + " format Errors.";
}
else
{
objPacerImportTransaction.ImportMessage = insertedRowCount + " Unique Cases Added! " + updatedRowCount + " Cases Updated! " + formatErrors + " format Errors.";
}
}
catch
{
}
}
else
{
objPacerImportTransaction.ImportMessage = insertedRowCount + " Unique Cases Added! " + updatedRowCount + " Cases Updated! " + formatErrors + " format Errors.";
}
objPacerImportTransaction.ImportStatus = "SUCCESS";
Save(objPacerImportTransaction);
Court _court = CourtService.GetByID(objPacerImportTransaction.CourtID);
ZipGeoCodeService.UpdateStatsForState(_court.StateCode);
return(insertedRowCount);
}