private DateTime getStartDate(string path )
{
string fileLocation = Path.Combine(path, "156.DB");
string localParkNumber = "156"; //156 for Yvonne data, 303 for Sandy
if (File.Exists(Path.Combine(path, "156.DB"))) { localParkNumber = "156"; }
if (File.Exists(Path.Combine(path, "303.DB"))) { localParkNumber = "303"; }
// The utilstartDate
DateTime localStartDate = new DateTime(1965, 05, 23);
var table = new ParadoxTable(path, localParkNumber); //156 for Yvonne data, 303 for Sandy
int count = 0;
foreach (var rec in table.Enumerate())
{
count = count +1;
if (count > 1) { break; }
else
{
return (DateTime)rec.DataValues[18]; // 18 is the New Date in the Park table which is the new meter read date
} // this is the start date we use to calculate the utility bill
}
return localStartDate;
}
private int ImportTenantandBill(string ParkNumber, string dbPath)
{
int localParkID = GetParkID(ParkNumber);
if (localParkID == -1)
{
MessageBox.Show("Park record missing in Database - " + ParkNumber.ToString());
return -1;
}
var table = new ParadoxTable(dbPath, ParkNumber);
// find out whicch column index is used for electricity multiple medical allowances
messageColumn = -1;
eleMedAlloanceColumn = -1;
for (int i = 0; i < table.FieldCount; i= i+1 )
{
if (table.FieldNames[i] == "Top mess1")
{
messageColumn = i; // it is supposed to be 118
break;
}
if (table.FieldNames[i] == "ELE MULT ALLOW")
{
eleMedAlloanceColumn = i;
break;
}
};
// now we process each tenant space record in the park
int count = 0;
foreach (var rec in table.Enumerate())
{
int parkSpaceID = 0;
// check if the parkSpacetenant already exist in database
try
{
if (count == 0)
{
count = count + 1;
insertMessage(rec,localParkID, messageColumn);
insertPeriodRecord(localParkID, rec);
insertTaxRate(localParkID);
}
string spaceName = " ";
if (rec.DataValues[1] is DBNull)
{
spaceName = rec.DataValues[0].ToString();
}
else
{
spaceName = rec.DataValues[1].ToString();
}
parkSpaceID = DatabaseControl.getParkSpaceID(localParkID, spaceName);
}
catch
{
parkSpaceID = -1;
}
if (parkSpaceID < 1)
{
// parkspace does not exist, need to insert tenant record and meter location record
parkSpaceID = insertTenantRecord(localParkID, rec);
insertMeterLocationRecord(localParkID, rec);
}
else
{
int a1, a2;
// parkspace exist already, need to check if it is still the same tenant
object[] result = DatabaseControl.getSingleRecord(new string[] { "Tenant" }, "ParkSpaceTenant", "ParkSpaceID=@value0", new object[] { parkSpaceID });
if (result[0].ToString() == rec.DataValues[5].ToString())
{
// tenant name is the same as before
a1 = updateTenantRecord(localParkID, parkSpaceID, rec);
}
else
{
// new tenant --> need to update the old tenant move-out date and create tenant record for the new tenant
a2 = updateTenantRecordWithMoveOutDate(localParkID, parkSpaceID, rec);
parkSpaceID = insertTenantRecord(localParkID, rec);
}
}
int b = insertBillRecord(parkSpaceID, rec);
int c = insertMeterReadRecord(parkSpaceID, rec);
int d = insertParkChargeRecord(parkSpaceID, rec, localParkID);
}
return 0;
}
private void ImportEleRate(string path)
{
var table = new ParadoxTable(path, isEleDB);
int utilRateID, utilCompanyID = 1;
// find out whicch column index is used for DWR BC
DWRBCColumn = -1;
FERADiscountColumn = -1;
for (int i = 0; i < table.FieldCount; i = i + 1)
{
if (table.FieldNames[i] == "Ele li sur sr citizen")
{
DWRBCColumn = i;
}
else
if (table.FieldNames[i] == "'98 Discount")
{
FERADiscountColumn = i;
}
};
//string x = table.Enumerate().ElementAt(0).DataValues[0].ToString();
foreach (var rec in table.Enumerate())
{
// We will only work for SCE for now
//utilCompanyID = insertUtilCompany(rec, isEleDB);
//if (rec.DataValues[1].ToString() == "PG&E")
//{
utilCompanyID = 1102; //PG&E
utilRateID = insertUtilRate(rec, isEleDB, utilCompanyID, path);
if ((utilRateID > 0) & (DWRBCColumn > 0) | (FERADiscountColumn > 0))
{
insertSurcharge(rec, utilRateID);
}
insertBaselineAllowance(rec, utilRateID);
const int SCE = 1;
const int PGE = 1102;
switch (utilCompanyID)
{
case SCE:
insertTierRateforSCE(rec, utilRateID);
insertBasicRateforSCE(rec, utilRateID);
break;
case PGE:
insertTierRateforPGE(rec, utilRateID);
insertBasicRateforPGE(rec, utilRateID);
break;
default:
break;
}
break;
//}
}
}