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()); } } } }