public Fee GetFeeData(Fee returnValue)
{
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
using (OleDbCommand sqlCommand = connection.CreateCommand())
{
string fields = returnValue.GetFieldNames();
sqlCommand.CommandText = "SELECT " + fields + ", Adjustment, AdjustmentExplanation, IsPriority, IsEmergency, IsRapidResponse FROM tblFees WHERE ID = " + returnValue.ID;
connection.Open();
OleDbDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
int index = 0;
foreach (ICharge charge in returnValue.Charges)
{
switch (charge.Type)
{
case "variable":
VariableCharge vCharge = (VariableCharge)charge;
vCharge.Count = reader.GetDecimalSafe(index++);
break;
case "categorical":
CategoricalCharge cCharge = (CategoricalCharge)charge;
cCharge.Count = reader.GetDecimalSafe(index++);
break;
case "boolean":
BooleanCharge bCharge = (BooleanCharge)charge;
bCharge.IsIncurred = reader.GetBooleanSafe(index++);
break;
}
}
returnValue.Adjustment = reader.GetDecimalSafe(index++);
returnValue.AdjustmentExplanation = reader.GetStringSafe(index++);
returnValue.IsPriority = reader.GetBooleanSafe(index++);
returnValue.IsEmergency = reader.GetBooleanSafe(index++);
returnValue.IsRapidResponse = reader.GetBooleanSafe(index++);
returnValue.CalculateProjectCost();
return(returnValue);
}
}
}
private void AddEntry(RecordSearch record)
{
//Date
Word.Paragraph dateHeader = document.Content.Paragraphs.Add(ref missing);
dateHeader.Range.Paragraphs.SpaceAfter = 0;
try
{
dateHeader.Range.Text = record.DateOfResponse.ToDateString() + "\n";
}
catch
{
dateHeader.Range.Text = "Missing date of response.";
dateHeader.Range.Font.Bold = 1;
dateHeader.Range.Font.Color = Word.WdColor.wdColorDarkRed;
_errorCount++;
}
dateHeader.Range.InsertParagraphAfter();
//Address, PEID, & Invoice #
Word.Table iTable = document.Tables.Add(dateHeader.Range, 1, 3, ref missing, ref missing);
iTable.AllowAutoFit = true;
iTable.AutoFitBehavior(Word.WdAutoFitBehavior.wdAutoFitWindow);
iTable.Columns[1].PreferredWidthType = Word.WdPreferredWidthType.wdPreferredWidthPercent;
iTable.Columns[1].PreferredWidth = 40;
iTable.Columns[2].PreferredWidthType = Word.WdPreferredWidthType.wdPreferredWidthPercent;
iTable.Columns[2].PreferredWidth = 30;
//--Address
if (record.BillingAddress.ValidateMinimalCompleteness())
{
if (string.IsNullOrWhiteSpace(record.BillingAddress.AddressLine2))
{
iTable.Rows[1].Cells[1].Range.Text = string.Format("{0}\r\n{1}\r\n{2}, {3} {4}",
record.BillingAddress.AddressName, record.BillingAddress.AddressLine1, record.BillingAddress.City, record.BillingAddress.State, record.BillingAddress.ZIP);
}
else
{
iTable.Rows[1].Cells[1].Range.Text = string.Format("{0}\r\n{1}\r\n{2}\r\n{3}, {4} {5}",
record.BillingAddress.AddressName, record.BillingAddress.AddressLine1, record.BillingAddress.AddressLine2, record.BillingAddress.City, record.BillingAddress.State, record.BillingAddress.ZIP);
}
}
else
{
iTable.Rows[1].Cells[1].Range.Text = "Missing billing address information.";
iTable.Rows[1].Cells[1].Range.Font.Bold = 1;
iTable.Rows[1].Cells[1].Range.Font.Color = Word.WdColor.wdColorDarkRed;
_errorCount++;
}
//--PEID
if (!string.IsNullOrWhiteSpace(record.ClientModel.NewPEID))
{
iTable.Rows[1].Cells[2].Range.Text = string.Format("PEID # " + record.ClientModel.NewPEID);
}
else if (!string.IsNullOrWhiteSpace(record.ClientModel.OldPEID))
{
iTable.Rows[1].Cells[2].Range.Text = string.Format("PEID # " + record.ClientModel.OldPEID);
}
else
{
iTable.Rows[1].Cells[2].Range.Text = "Missing PEID.";
iTable.Rows[1].Cells[2].Range.Font.Bold = 1;
iTable.Rows[1].Cells[2].Range.Font.Color = Word.WdColor.wdColorDarkRed;
_errorCount++;
}
iTable.Rows[1].Cells[2].Range.Bold = 1;
iTable.Rows[1].Cells[2].Range.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
//--Invoice #
iTable.Rows[1].Cells[3].Range.Text = "Invoice #";
iTable.Rows[1].Cells[3].Range.Bold = 1;
iTable.Range.InsertParagraphAfter();
//Person, Project Name, Requestor, IC File #
Word.Paragraph projectInfo = document.Content.Paragraphs.Add(ref missing);
string attentionTo;
if (string.IsNullOrWhiteSpace(record.BillingAddress.AttentionTo))
{
attentionTo = "";
}
else
{
attentionTo = "\r\nATTN: " + record.BillingAddress.AttentionTo;
}
string fileNumber = "IC File # " + record.GetFileNumberFormatted();
if (string.IsNullOrWhiteSpace(record.Requestor.FirstName))
{
projectInfo.Range.Text = string.Format("{0}\r\n>>\r\nRE: {1}; {2}\r\n>>",
attentionTo, record.ProjectName, fileNumber);
}
else
{
projectInfo.Range.Text = string.Format("{0}\r\n>>\r\nRE: {1} (Requested By: {2} {3}); {4}\r\n>>",
attentionTo, record.ProjectName, record.Requestor.FirstName, record.Requestor.LastName, fileNumber);
}
object startRange = projectInfo.Range.End - (fileNumber.Length + 4);
object endRange = projectInfo.Range.End - 3;
Word.Range toBold = document.Range(ref startRange, ref endRange);
toBold.Bold = 1;
projectInfo.Range.InsertParagraphAfter();
//Billing Info
Word.Table bTable = document.Content.Tables.Add(projectInfo.Range, 1, 2, ref missing);
bTable.AllowAutoFit = true;
bTable.AutoFitBehavior(Word.WdAutoFitBehavior.wdAutoFitWindow);
bTable.Columns[1].PreferredWidthType = Word.WdPreferredWidthType.wdPreferredWidthPercent;
bTable.Columns[1].PreferredWidth = 25;
//--Total
try
{
bTable.Rows[1].Cells[1].Range.Text = "Amount Due: $" + record.Fee.TotalProjectCost;
}
catch
{
bTable.Rows[1].Cells[1].Range.Text = "Missing PEID.";
bTable.Rows[1].Cells[1].Range.Font.Bold = 1;
bTable.Rows[1].Cells[1].Range.Font.Color = Word.WdColor.wdColorDarkRed;
_errorCount++;
}
//--Fees & Surcharge
string chargeInformation = "";
decimal runningTotal = 0;
foreach (ICharge charge in record.Fee.Charges)
{
if (charge.TotalCost <= 0)
{
continue;
}
switch (charge.Type)
{
case "variable":
VariableCharge vCharge = (VariableCharge)charge;
chargeInformation += string.Format(" {0} - {1} {2} @ ${3} per {4}\n",
vCharge.Name, vCharge.Count, vCharge.UnitNamePlural, vCharge.Cost, vCharge.UnitName);
runningTotal += vCharge.TotalCost;
break;
case "boolean":
BooleanCharge bCharge = (BooleanCharge)charge;
chargeInformation += string.Format(" {0} - ${1}\n", bCharge.Name, bCharge.TotalCost);
runningTotal += bCharge.TotalCost;
break;
case "categorical":
CategoricalCharge cCharge = (CategoricalCharge)charge;
chargeInformation += string.Format(" {0} - {1} {2} - ${3}\n", cCharge.Name, cCharge.Count, cCharge.UnitNamePlural, cCharge.TotalCost);
runningTotal += cCharge.TotalCost;
break;
default:
break;
}
}
string surcharge = "";
if (record.Fee.IsPriority)
{
surcharge += " Priority Surcharge Fee: $" + (record.Fee.TotalProjectCost - runningTotal) + "\n";
}
if (record.Fee.IsEmergency)
{
surcharge += " Emergency Surcharge Fee: $" + record.Fee.TotalProjectCost + "\n";
}
bTable.Rows[1].Cells[2].Range.Text = "Information\n" + chargeInformation + surcharge + "Please include the invoice number on your remittance";
bTable.Range.InsertParagraphAfter();
//Finish with line
InsertLine();
}
public int UpdateFee(Fee f)
{
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
using (OleDbCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = "SELECT ID FROM tblFees WHERE ID = ?";
sqlCommand.Parameters.AddWithValue("ID", f.ID);
connection.Open();
OleDbDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
using (OleDbConnection connection2 = new OleDbConnection(ConnectionString))
{
using (OleDbCommand updateCommand = connection2.CreateCommand())
{
updateCommand.CommandText = "UPDATE tblFees SET " + f.GetFieldNames("update") +
", Adjustment = @adj, AdjustmentExplanation = @adjexp, IsPriority=@priority, IsEmergency=@emergency, IsRapidResponse=@rapid " +
"WHERE ID = @id";
foreach (ICharge charge in f.Charges)
{
switch (charge.Type)
{
case "variable":
VariableCharge vCharge = (VariableCharge)charge;
updateCommand.Parameters.AddWithValue("@" + vCharge.DBField, vCharge.Count);
break;
case "categorical":
CategoricalCharge cCharge = (CategoricalCharge)charge;
updateCommand.Parameters.AddWithValue("@" + cCharge.DBField, cCharge.Count);
break;
case "boolean":
BooleanCharge bCharge = (BooleanCharge)charge;
updateCommand.Parameters.AddWithValue("@" + bCharge.DBField, bCharge.IsIncurred);
break;
}
}
updateCommand.Parameters.AddWithValue("@adj", f.Adjustment);
updateCommand.Parameters.AddWithValue("@adjexp", f.AdjustmentExplanation ?? Convert.DBNull);
updateCommand.Parameters.AddWithValue("@priority", f.IsPriority);
updateCommand.Parameters.AddWithValue("@emergency", f.IsEmergency);
updateCommand.Parameters.AddWithValue("@rapid", f.IsRapidResponse);
updateCommand.Parameters.AddWithValue("@id", f.ID);
connection2.Open();
updateCommand.ExecuteNonQuery();
return(f.ID);
}
}
}
else
{
return(AddNewFee());
}
}
}
}
