private void DisplaySections()
{
try
{
if (_Sections.AnyOn(BitField.Flag.f1)) //Archery
{
chkArchery.Checked = true;
}
else
{
chkArchery.Checked = false;
}
if (_Sections.AnyOn(BitField.Flag.f2)) //Handgun
{
chkHandgun.Checked = true;
}
else
{
chkHandgun.Checked = false;
}
if (_Sections.AnyOn(BitField.Flag.f3)) //Smallbore
{
chkSmallbore.Checked = true;
}
else
{
chkSmallbore.Checked = false;
}
if (_Sections.AnyOn(BitField.Flag.f4)) //SCA
{
chkSCA.Checked = true;
}
else
{
chkSCA.Checked = false;
}
if (_Sections.AnyOn(BitField.Flag.f5)) //Rifle
{
chkRifle.Checked = true;
}
else
{
chkRifle.Checked = false;
}
if (_Sections.AnyOn(BitField.Flag.f6)) //Action
{
chkAction.Checked = true;
}
else
{
chkAction.Checked = false;
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error setting Section Checkboxes", ex, true);
}
}
private void frmExtra_Load(object sender, EventArgs e)
{
ulong mask;
List <KeyValuePair <int, string> > years = new List <KeyValuePair <int, string> >();
try
{
mbr = new clsMembership(_ID, _Promote);
MembershipTableAdapters.MembershipTypeTableAdapter daMemType = new PFGA_Membership.MembershipTableAdapters.MembershipTypeTableAdapter();
Membership.MembershipTypeDataTable dtMemType = new Membership.MembershipTypeDataTable();
daMemType.Fill(dtMemType);
this.Text = String.Format("Card #: {0}", mbr.Card.ToString());
last_NameTextBox.Text = mbr.LastName.ToString();
first_NameTextBox.Text = mbr.FirstName.ToString();
birth_DateDateTimePicker.Text = (mbr.BirthDate.ToString() == "1900-01-01" ? string.Empty : mbr.BirthDate.ToString());
email_AddressTextBox.Text = mbr.Email.ToString();
website_UsernamesTextBox.Text = mbr.UserName.ToString();
palTextBox.Text = mbr.Pal.ToString();
pal_Exp_DateDateTimePicker.Text = (mbr.PalExpDate.ToString() == "1900-01-01" ? string.Empty : mbr.PalExpDate.ToString());
cboWalk.SelectedText = mbr.Walk.ToString();
sectionFlagTextBox.Text = mbr.SectionFlag.ToString();
txtNotes.Text = mbr.Notes;
txtCardNumber.Text = mbr.Card.ToString();
chkSwipe.Checked = mbr.SwipeCard;
txtCell.Text = (mbr.Cell == null ? string.Empty : mbr.Cell.ToString());
chkCardMade.Checked = mbr.CardMade;
if (mbr.BadgeImage != null)
{
if (mbr.BadgeImage.Length > 0)
{
Badge.Image = Image.FromStream(new MemoryStream(mbr.BadgeImage));
}
}
// New Extra Card, Hide Fields
if (IsExtra || (_ID < 0 && _Promote > 0))
{
this.Text = String.Format("Card #: {0}", mbr.Card.ToString());
last_NameTextBox.Text = mbr.LastName.ToString();
first_NameTextBox.Text = mbr.FirstName.ToString();
grpSection.Hide();
txtCardNumber.Show();
lblCardNumber.Show();
}
else if (_ID < 0 && _Promote < 0)
{
txtCardNumber.Show();
lblCardNumber.Show();
}
if (ulong.TryParse(sectionFlagTextBox.Text.ToString(), out mask))
{
_Sections.Mask = mask;
}
else
{
_Sections.ClearField();
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error Loading Member Form", ex, true);
}
DisplaySections();
}
private void frmMember_Load(object sender, EventArgs e)
{
ulong mask;
ulong pMask;
int curYear;
List <KeyValuePair <int, string> > years = new List <KeyValuePair <int, string> >();
try
{
mbr = new clsMembership(_ID, _Promote);
MembershipTableAdapters.MembershipTypeTableAdapter daMemType = new PFGA_Membership.MembershipTableAdapters.MembershipTypeTableAdapter();
Membership.MembershipTypeDataTable dtMemType = new Membership.MembershipTypeDataTable();
daMemType.Fill(dtMemType);
cboMemberType.DataSource = dtMemType;
cboMemberType.DisplayMember = "Membership Type";
cboMemberType.ValueMember = "MembershipTypeID";
if (DateTime.Today.Month >= 1 && DateTime.Today.Month < 8)
{
curYear = DateTime.Today.Year - 1;
}
else
{
curYear = DateTime.Today.Year;
}
for (int c = curYear; c > 2008; c--)
{
years.Add(new KeyValuePair <int, string>(c, string.Format("{0} - {1}", c, c + 1)));
}
cboPaid.DataSource = years;
cboPaid.ValueMember = "Key";
cboPaid.DisplayMember = "Value";
MembershipTableAdapters.PaymentTypeTableAdapter daPayType = new MembershipTableAdapters.PaymentTypeTableAdapter();
Membership.PaymentTypeDataTable dtPayType = new Membership.PaymentTypeDataTable();
daPayType.Fill(dtPayType);
RadioButton[] RadioButtons;
int index = 0;
RadioButtons = new RadioButton[dtPayType.Rows.Count];
foreach (DataRow row in dtPayType)
{
RadioButtons[index] = new RadioButton();
RadioButtons[index].Name = "chkPayment" + Convert.ToString(index);
RadioButtons[index].Text = row["PaymentType"].ToString();
RadioButtons[index].AutoSize = true;
RadioButtons[index].Tag = int.Parse(row["PaymentTypeId"].ToString());
RadioButtons[index].Location = new System.Drawing.Point((RadioButtons[index].Width * index) + 5, 15);
grpPayType.Controls.Add(RadioButtons[index]);
index++;
}
RadioButtons[0].Checked = true;
string Heading = string.Empty;;
if (mbr.MasterRecord < 0)
{
Heading = String.Format("Card #: {0}", mbr.Card.ToString());
}
else
{
Heading = string.Format("Card #: {0}, Account: {1}", mbr.Card.ToString(), mbr.MasterRecordName);
}
this.Text = Heading;
last_NameTextBox.Text = mbr.LastName.ToString();
first_NameTextBox.Text = mbr.FirstName.ToString();
birth_DateDateTimePicker.Text = (mbr.BirthDate.ToString() == "1900-01-01" ? string.Empty : mbr.BirthDate.ToString());
addressTextBox.Text = mbr.Address.ToString();
city__ProvTextBox.Text = mbr.CityProv.ToString();
postalTextBox.Text = mbr.Postal.ToString();
phoneTextBox.Text = mbr.Phone.ToString();
email_AddressTextBox.Text = mbr.Email.ToString();
website_UsernamesTextBox.Text = mbr.UserName.ToString();
palTextBox.Text = mbr.Pal.ToString();
pal_Exp_DateDateTimePicker.Text = (mbr.PalExpDate.ToString() == "1900-01-01" ? string.Empty : mbr.PalExpDate.ToString());
cboMemberType.SelectedValue = mbr.MemberTypeID;
date_JoinedDateTimePicker.Text = (mbr.JoinedDate.ToString() == "1900-01-01" ? string.Empty : mbr.JoinedDate.ToString());
cboWalk.SelectedText = mbr.Walk.ToString();
sectionFlagTextBox.Text = mbr.SectionFlag.ToString();
noBackTrackCheckBox.Checked = mbr.NoBackTrack;
noEmailingCheckBox.Checked = mbr.NoEmail;
chkActive.Checked = mbr.Active;
txtNotes.Text = mbr.Notes;
txtCardNumber.Text = mbr.Card.ToString();
txtSponsor.Text = mbr.Sponsor;
chkSwipe.Checked = mbr.SwipeCard;
chkExecutive.Checked = mbr.Executive;
txtCell.Text = (mbr.Cell == null ? string.Empty : mbr.Cell.ToString());
txtOther.Text = mbr.ParticipationOther;
txtParticipateFlag.Text = mbr.Participation.ToString();
chkCardMade.Checked = mbr.CardMade;
if (mbr.BadgeImage != null)
{
if (mbr.BadgeImage.Length > 0)
{
Badge.Image = Image.FromStream(new MemoryStream(mbr.BadgeImage));
}
}
// Existing Member
if (_ID > 0)
{
#region PaidHistory
dgPaidHistory.AutoGenerateColumns = false;
DataGridViewTextBoxColumn dgcYearPaid = new DataGridViewTextBoxColumn();
dgcYearPaid.Name = "YearPaid";
dgcYearPaid.Visible = false;
dgcYearPaid.SortMode = DataGridViewColumnSortMode.Programmatic;
dgcYearPaid.DataPropertyName = "YearPaid";
dgPaidHistory.Columns.Add(dgcYearPaid);
dgPaidHistory.Columns["YearPaid"].Visible = false;
DataGridViewTextBoxColumn dgcMembershipYear = new DataGridViewTextBoxColumn();
dgcMembershipYear.Name = "MembershipYear";
dgcMembershipYear.HeaderText = "Year Paid";
dgcMembershipYear.DataPropertyName = "MembershipYear";
dgcMembershipYear.SortMode = DataGridViewColumnSortMode.Programmatic;
dgPaidHistory.Columns.Add(dgcMembershipYear);
// Create DeleteButton
DataGridViewButtonColumn dgcDelButton = new DataGridViewButtonColumn();
dgcDelButton.Name = "colDelete";
dgcDelButton.HeaderText = "";
dgcDelButton.Text = "û";
dgcDelButton.ToolTipText = "Delete Row";
dgcDelButton.UseColumnTextForButtonValue = true;
dgcDelButton.DefaultCellStyle.Font = new Font("WingDings", 14);
dgcDelButton.DefaultCellStyle.ForeColor = Color.Red;
dgcDelButton.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgcDelButton.Width = 20;
dgPaidHistory.Columns.Add(dgcDelButton);
DataView dvPaidHistory = new DataView(mbr.PaidHistory);
dvPaidHistory.Sort = "YearPaid DESC";
dvPaidHistory.RowFilter = "Deleted = False";
dgPaidHistory.DataSource = dvPaidHistory;
#endregion
#region Extra Cards
dgExtraCards.AutoGenerateColumns = false;
DataGridViewTextBoxColumn dgcID = new DataGridViewTextBoxColumn();
dgcID.Name = "ID";
dgcID.Visible = false;
dgcID.DataPropertyName = "ID";
dgExtraCards.Columns.Add(dgcID);
DataGridViewTextBoxColumn dgcName = new DataGridViewTextBoxColumn();
dgcName.Name = "Name";
dgcName.HeaderText = "Name";
dgcName.DataPropertyName = "Name";
dgcName.Width = 500;
dgExtraCards.Columns.Add(dgcName);
DataGridViewImageButtonDeleteColumn dgRemove = new DataGridViewImageButtonDeleteColumn();
dgRemove.Name = "colDelete";
dgRemove.HeaderText = "";
dgRemove.Text = "Delete";
dgRemove.ToolTipText = "";
dgRemove.UseColumnTextForButtonValue = true;
dgRemove.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
dgExtraCards.Columns.Add(dgRemove);
DataGridViewImageButtonPromoteColumn dgPromote = new DataGridViewImageButtonPromoteColumn();
dgPromote.Name = "colPromote";
dgPromote.HeaderText = "";
dgPromote.Text = "Promote to Member";
dgPromote.ToolTipText = "Promote";
dgPromote.UseColumnTextForButtonValue = true;
dgExtraCards.Columns.Add(dgPromote);
DataGridViewImageButtonEditColumn dgEdit = new DataGridViewImageButtonEditColumn();
dgEdit.Name = "colEdit";
dgEdit.HeaderText = "";
dgEdit.Text = "Edit";
dgEdit.ToolTipText = "Edit";
dgEdit.UseColumnTextForButtonValue = true;
dgExtraCards.Columns.Add(dgEdit);
DataView dvExtraCards = new DataView(mbr.ExtraCards);
dvExtraCards.RowFilter = "Deleted = False AND Promote = False";
dgExtraCards.DataSource = dvExtraCards;
#endregion
}
// New Extra Card, Hide Fields
if (IsExtra || (_ID < 0 && _Promote > 0))
{
this.Text = String.Format("Card #: {0}", mbr.Card.ToString());
last_NameTextBox.Text = mbr.LastName.ToString();
first_NameTextBox.Text = mbr.FirstName.ToString();
addressTextBox.Hide();
city__ProvTextBox.Hide();
postalTextBox.Hide();
phoneTextBox.Hide();
website_UsernamesTextBox.Hide();
date_JoinedDateTimePicker.Hide();
grpSection.Hide();
noBackTrackCheckBox.Hide();
noEmailingCheckBox.Hide();
chkActive.Hide();
tabControl1.TabPages.Remove(tabPaid);
tabControl1.TabPages.Remove(tabExtra);
grpParticipate.Hide();
txtCardNumber.Show();
lblCardNumber.Show();
}
else if (_ID < 0 && _Promote < 0)
{
tabPaid.Hide();
tabExtra.Hide();
txtCardNumber.Show();
lblCardNumber.Show();
}
if (ulong.TryParse(sectionFlagTextBox.Text.ToString(), out mask))
{
_Sections.Mask = mask;
}
else
{
_Sections.ClearField();
}
if (ulong.TryParse(txtParticipateFlag.Text, out pMask))
{
_Participation.Mask = pMask;
}
else
{
_Participation.ClearField();
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error Loading Member Form", ex, true);
}
DisplaySections();
DisplayParticipation();
}
public void Save()
{
try
{
MembershipTableAdapters.MembersTableAdapter daMember = new MembershipTableAdapters.MembersTableAdapter();
Membership.MembersDataTable dtMember = new Membership.MembersDataTable();
Membership.MembersRow row;
daMember.Fill(dtMember);
if (ID > 0)
{
row = dtMember.FindByID(ID);
}
else
{
row = dtMember.NewMembersRow();
}
row.Last_Name = _LastName;
row.First_Name = _FirstName;
row.Address = Address;
row.City__Prov = _CityProv;
row.Postal = _Postal;
row.Phone = _Phone;
row.Email_Address = _Email;
row.MemberTypeID = _MemberTypeID;
row.Date_Joined = _JoinedDate;
row.Birth_Date = _BirthDate;
row.Walk = _Walk;
row.Pal = _Pal;
row.Pal_Exp_Date = _PalExpDate;
row.Website_Usernames = _UserName;
row.NoBackTrack = _NoBackTrack;
row.NoEmailing = _NoEmail;
row.Active = _Active;
row.MasterRecord = _MasterRecord;
row.SectionFlag = byte.Parse(_SectionFlag.ToString());
row.Card = _Card;
row.Notes = _Notes;
row.Swipe = _SwipeCard;
row.Executive = _Executive;
row.Sponsor = _Sponsor;
row.Cell = _Cell;
row.Participation = byte.Parse(_Participation.ToString());
row.pOther = _pOther;
row.Image = BadgeImage;
row.CardMade = _CardMade;
// Old columns
row.ATT_Expiry = new DateTime(1900, 01, 01);
row.Membership_Type = string.Empty;
row.Section = string.Empty;
if (ID > 0)
{
daMember.Update(row);
}
else
{
// Include an event to fill in the Autonumber value.
dtMember.AddMembersRow(row);
daMember.Update(dtMember);
dtMember.AcceptChanges();
ID = row.ID;
}
if (ID > 0 && MasterRecord <= 0)
{
savePaidHistory();
saveExtraCard();
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error saving Member", ex, true);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
double tmpPal;
MemoryStream ms = new MemoryStream();
try
{
mbr.LastName = last_NameTextBox.Text;
mbr.FirstName = first_NameTextBox.Text;
mbr.BirthDate = birth_DateDateTimePicker.Value;
mbr.Address = addressTextBox.Text;
mbr.CityProv = city__ProvTextBox.Text;
mbr.Postal = postalTextBox.Text;
mbr.Phone = phoneTextBox.Text;
mbr.Email = email_AddressTextBox.Text;
mbr.UserName = website_UsernamesTextBox.Text;
if (!double.TryParse(palTextBox.Text, out tmpPal))
{
tmpPal = 0;
}
mbr.Pal = tmpPal;
mbr.PalExpDate = pal_Exp_DateDateTimePicker.Value;
mbr.MemberTypeID = int.Parse(cboMemberType.SelectedValue.ToString());
mbr.JoinedDate = date_JoinedDateTimePicker.Value;
mbr.Walk = cboWalk.Text;
mbr.SectionFlag = _Sections.Mask;
mbr.NoBackTrack = noBackTrackCheckBox.Checked;
mbr.NoEmail = noEmailingCheckBox.Checked;
mbr.Active = chkActive.Checked;
mbr.Card = int.Parse(txtCardNumber.Text);
mbr.Notes = txtNotes.Text;
mbr.Sponsor = txtSponsor.Text;
mbr.SwipeCard = chkSwipe.Checked;
mbr.Executive = chkExecutive.Checked;
mbr.Cell = txtCell.Text;
mbr.ParticipationOther = txtOther.Text;
mbr.Participation = _Participation.Mask;
mbr.CardMade = chkCardMade.Checked;
if (Badge.Image != null)
{
Badge.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
mbr.BadgeImage = ms.ToArray();
ms.Dispose();
/*
* string imagesFiled = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "images");
* if (!Directory.Exists(imagesFiled))
* {
* Directory.CreateDirectory(imagesFiled);
* }
* if (mbr.Card > 0)
* {
* Badge.Image.Save(string.Format(@"{0}\{1}.jpg", imagesFiled, mbr.Card.ToString()));
* }
*/
}
mbr.Save();
if ((!IsExtra) && ((Promote == -1 && ID > 0) || Application.OpenForms.Count == 2))
{
frmParent frm = (frmParent)this.ParentForm;
frm.showList();
}
this.Close();
this.Dispose();
}
catch (Exception ex)
{
ErrorLogger.Log("Error trying to Save the Member", ex, true);
}
}
private void DisplayParticipation()
{
try
{
if (_Participation.AnyOn(BitField.Flag.f1)) //Work Party
{
chkWorkParty.Checked = true;
}
else
{
chkWorkParty.Checked = false;
}
if (_Participation.AnyOn(BitField.Flag.f2)) // Events
{
chkEvents.Checked = true;
}
else
{
chkEvents.Checked = false;
}
if (_Participation.AnyOn(BitField.Flag.f3)) // Executive
{
chkpExecutive.Checked = true;
}
else
{
chkpExecutive.Checked = false;
}
if (_Participation.AnyOn(BitField.Flag.f4)) // Range Officer
{
chkRO.Checked = true;
}
else
{
chkRO.Checked = false;
}
if (_Participation.AnyOn(BitField.Flag.f5)) // TRaining Officer
{
chkTO.Checked = true;
}
else
{
chkTO.Checked = false;
}
if (_Participation.AnyOn(BitField.Flag.f6)) // Other
{
chkOther.Checked = true;
}
else
{
chkOther.Checked = false;
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error setting Participation Checkboxes", ex, true);
}
}
private void pendingMembersToolStripMenuItem_Click(object sender, EventArgs e)
{
Word.Application oWd = new Word.Application();
Word.Document oDoc;
Word.Range rng;
object replaceAll = Word.WdReplace.wdReplaceAll;
Missing missing = System.Reflection.Missing.Value;
string section;
string fileName;
string template = Path.Combine(System.IO.Path.GetDirectoryName(Application.ExecutablePath), "NewMemberWelcomeLetter.docx");
System.Data.SqlClient.SqlConnection cnn;
String config = ConfigurationManager.ConnectionStrings["PFGA_Membership.Properties.Settings.PFGAMembershipConnectionString"].ToString();
cnn = new SqlConnection(config);
cnn.Open();
string query = @"SELECT Card, [Email Address], SectionFlag
FROM qryExport
WHERE (YearPaid = " + thisYear() + ") AND (MemberTypeID = 13)";
SqlCommand cmd = new SqlCommand(query, cnn);
DataTable dtEmails = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dtEmails);
try
{
Cursor.Current = Cursors.WaitCursor;
if (MessageBox.Show(string.Format("This will create {0} documents on your desktop", dtEmails.Rows.Count), "Creating Document"
, MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
{
foreach (DataRow row in dtEmails.Rows)
{
// Copy and rename template
fileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),
string.Concat(row["Email Address"].ToString(), ".docx"));
File.Copy(template, fileName, true);
oDoc = oWd.Documents.Open(fileName);
rng = oDoc.Content;
section = getSectionLabels(row["SectionFlag"].ToString());
if (section.Length > 0)
{
Word.Find findSection = rng.Find;
findSection.ClearFormatting();
findSection.Text = "<<Section>>";
findSection.Replacement.ClearFormatting();
findSection.Replacement.Text = section;
findSection.Execute(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
ref replaceAll, missing, missing, missing, missing);
}
Word.Find findCard = rng.Find;
findCard.ClearFormatting();
findCard.Text = "<<CardNo>>";
findCard.Replacement.ClearFormatting();
findCard.Replacement.Text = row["Card"].ToString();
findCard.Execute(missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,
ref replaceAll, missing, missing, missing, missing);
oDoc.Close(true, missing, missing);
}
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error trying to generate Pending emails", ex, true);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void reFormatGrid(int Index)
{
int iYear = -1;
int curYear;
int colWalk;
int colYear;
DateTime dYear = DateTime.MaxValue;
DateTime dTest = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month));
StringBuilder sFilter = new StringBuilder();
if (DateTime.Today.Month >= 1 && DateTime.Today.Month < 9)
{
curYear = DateTime.Today.Year - 1;
}
else
{
curYear = DateTime.Today.Year;
}
try
{
if (chkGeneral.Checked == true)
{
if (sFilter.Length > 0)
{
sFilter.Append(" AND ");
}
sFilter.Append("Active = 0 or Active = 1");
}
else
{
if (sFilter.Length > 0)
{
sFilter.Append(" AND ");
}
sFilter.Append("Active = 1");
}
dvMemberList.RowFilter = sFilter.ToString();
}
catch (Exception ex)
{
ErrorLogger.Log("Error filtering list", ex, true);
}
try
{
colWalk = dgMemberList.Columns["colWalk"].Index;
colYear = dgMemberList.Columns["colPaid"].Index;
foreach (DataGridViewRow Row in dgMemberList.Rows)
{
if (Row.Cells[colWalk].Value.ToString() != null)
{
if (Row.Cells[colWalk].Value.ToString() != "Done" && chkWalk.Checked)
{
Row.DefaultCellStyle.BackColor = Color.Yellow;
}
else
{
Row.DefaultCellStyle.BackColor = Color.White;
}
}
else
{
if (chkWalk.Checked)
{
Row.DefaultCellStyle.BackColor = Color.Yellow;
}
}
int.TryParse(Row.Cells[colYear].Value.ToString(), out iYear);
if (iYear == curYear)
{
if (Row.DefaultCellStyle.BackColor != Color.Yellow)
{
Row.DefaultCellStyle.BackColor = Color.White;
}
}
else
{
if (chkPaid.Checked)
{
Row.DefaultCellStyle.BackColor = Color.Red;
}
}
}
}
catch (Exception ex)
{
ErrorLogger.Log("Error colouring the List", ex, true);
}
dgMemberList.Refresh();
}
private void exportMembersToolStripMenuItem_Click(object sender, EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
int nYear = 0;
int nCount = 0;
int newCount = 0;
int renewCount = 0;
BitField section = new BitField();
int[] SectionCounts = new int[6];
DateTime dateJoined;
DateTime startDate;
DateTime endDate;
System.Data.SqlClient.SqlConnection cnn;
SqlCommand cmd;
DataTable dtExport;
DataTable dtExtra;
DataTable dtNon;
SqlDataAdapter da;
String config = ConfigurationManager.ConnectionStrings["PFGA_Membership.Properties.Settings.PFGAMembershipConnectionString"].ToString();
//Start Excel and get Application object.
oXL = new Excel.Application();
try
{
Cursor.Current = Cursors.WaitCursor;
// oXL.Visible = false;
cnn = new SqlConnection(config);
cnn.Open();
String qryExport = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + thisYear() + ") OR (MemberTypeID = 6)";
cmd = new SqlCommand(qryExport, cnn);
dtExport = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExport);
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = string.Format("Members {0}", thisYear().ToString());
//Add table headers going cell by cell.
for (int col = 0; col < dtExport.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtExport.Columns[col].ColumnName;
for (int row = 0; row < dtExport.Rows.Count; row++)
{
if (dtExport.Columns[col].ColumnName == "Section")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtExport.Rows[row][col].ToString());
}
else if (dtExport.Columns[col].ColumnName == "Participation")
{
oSheet.Cells[row + 2, col + 1] = getParticipation(dtExport.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtExport.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
string lastCol = string.Format("{0}1", Number2String(dtExport.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("Q2", string.Format("Q{0}", dtExport.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
String qryExtra = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, Pal, [Pal Exp Date], [Master Record], Swipe, YearPaid, Phone,
[Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
CardMade, pOther
FROM qryExportExtra
WHERE (YearPaid = " + thisYear() + ")";
cmd = new SqlCommand(qryExtra, cnn);
dtExtra = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExtra);
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Extra Cards";
//Add table headers going cell by cell.
for (int col = 0; col < dtExtra.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtExtra.Columns[col].ColumnName;
for (int row = 0; row < dtExtra.Rows.Count; row++)
{
if (dtExtra.Columns[col].ColumnName == "Section")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtExtra.Rows[row][col].ToString());
}
else if (dtExtra.Columns[col].ColumnName == "Participation")
{
oSheet.Cells[row + 2, col + 1] = getParticipation(dtExtra.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtExtra.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
lastCol = string.Format("{0}1", Number2String(dtExtra.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtExtra.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtExtra.Rows.Count + 1)).NumberFormat = "yyyy-mm-dd";
String qryNonRenewals = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + (thisYear() - 1) + ")";
cmd = new SqlCommand(qryNonRenewals, cnn);
dtNon = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtNon);
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Not Renewed";
//Add table headers going cell by cell.
for (int col = 0; col < dtNon.Columns.Count; col++)
{
oSheet.Cells[1, col + 1] = dtNon.Columns[col].ColumnName;
for (int row = 0; row < dtNon.Rows.Count; row++)
{
if (dtNon.Columns[col].ColumnName == "SectionFlag")
{
oSheet.Cells[row + 2, col + 1] = getSectionLabels(dtNon.Rows[row][col].ToString());
}
else
{
oSheet.Cells[row + 2, col + 1] = dtNon.Rows[row][col].ToString();
}
}
}
//Format A1:D1 as bold, vertical alignment = center.
lastCol = string.Format("{0}1", Number2String(dtNon.Columns.Count));
oSheet.get_Range("A1", lastCol).Font.Bold = true;
oSheet.get_Range("A1", lastCol).VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", lastCol).EntireColumn.AutoFit();
oSheet.get_Range("E2", string.Format("E{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("H2", string.Format("H{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet.get_Range("Q2", string.Format("Q{0}", dtNon.Rows.Count)).NumberFormat = "yyyy-mm-dd";
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Year";
oSheet.Cells[1, 2] = "Total";
oSheet.Cells[1, 3] = "New Members";
oSheet.Cells[1, 4] = "Renewals";
oSheet.Cells[1, 5] = "Archery";
oSheet.Cells[1, 6] = "Handgun";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "SCA";
oSheet.Cells[1, 9] = "Rifle";
oSheet.Cells[1, 10] = "Action";
nCount = 2;
for (nYear = 2008; nYear <= thisYear(); nYear++)
{
newCount = 0;
renewCount = 0;
SectionCounts = new int[6];
qryExport = @"SELECT Card, [Last Name], [First Name], [Membership Type], Walk, [Pal Exp Date], Pal, [ATT Expiry], Swipe, YearPaid, Phone,
Cell, [Email Address], [Date Joined], [Website Usernames], Notes, Sponsor, SectionFlag, Participation,
NoBackTrack, NoEmailing, pOther, CardMade, DatePaid
FROM qryExport
WHERE (YearPaid = " + nYear + ") OR (MemberTypeID = 6)";
cmd = new SqlCommand(qryExport, cnn);
dtExport = new DataTable();
da = new SqlDataAdapter(cmd);
da.Fill(dtExport);
oSheet.Cells[nCount, 1] = nYear;
oSheet.Cells[nCount, 2] = dtExport.Rows.Count;
for (int row = 0; row < dtExport.Rows.Count; row++)
{
if (DateTime.TryParse(dtExport.Rows[row]["Date Joined"].ToString(), out dateJoined) == false)
{
dateJoined = new DateTime(1900, 01, 01);
}
startDate = new DateTime(nYear, 09, 01);
endDate = new DateTime(nYear + 1, 08, 31);
if (dateJoined >= startDate && dateJoined <= endDate)
{
newCount++;
}
if (dateJoined < startDate)
{
renewCount++;
}
section.Mask = ulong.Parse(dtExport.Rows[row]["SectionFlag"].ToString());
if (section.AnyOn(BitField.Flag.f1)) // Archery
{
SectionCounts[0] += 1;
}
if (section.AnyOn(BitField.Flag.f2)) //Handgun
{
SectionCounts[1] += 1;
}
if (section.AnyOn(BitField.Flag.f3)) //Smallbore
{
SectionCounts[2] += 1;
}
if (section.AnyOn(BitField.Flag.f4)) //SCA
{
SectionCounts[3] += 1;
}
if (section.AnyOn(BitField.Flag.f5)) //Rifle
{
SectionCounts[4] += 1;
}
if (section.AnyOn(BitField.Flag.f6)) //Action
{
SectionCounts[5] += 1;
}
}
oSheet.Cells[nCount, 3] = newCount;
oSheet.Cells[nCount, 4] = renewCount;
oSheet.Cells[nCount, 5] = SectionCounts[0];
oSheet.Cells[nCount, 6] = SectionCounts[1];
oSheet.Cells[nCount, 7] = SectionCounts[2];
oSheet.Cells[nCount, 8] = SectionCounts[3];
oSheet.Cells[nCount, 9] = SectionCounts[4];
oSheet.Cells[nCount, 10] = SectionCounts[5];
nCount++;
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report 2";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Card";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "First Name";
oSheet.Cells[1, 4] = "Handgun";
oSheet.Cells[1, 5] = "Action";
oSheet.Cells[1, 6] = "Rifle";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "Archery";
oSheet.Cells[1, 9] = "Safety Walk";
oSheet.Cells[1, 10] = "Extra Card";
for (int row = 0; row < dtExport.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtExport.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtExport.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtExport.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtExport.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtExport.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
}
for (int row = 0; row < dtExtra.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtExtra.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtExtra.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtExtra.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtExtra.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtExtra.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
oSheet.Cells[row + 2, 10] = "Yes";
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
oSheet = (Excel._Worksheet)oWB.Sheets.Add(System.Reflection.Missing.Value, oWB.Sheets[oWB.Sheets.Count], 1, Excel.XlSheetType.xlWorksheet);
oSheet.Name = "Report 3";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Card";
oSheet.Cells[1, 2] = "Last Name";
oSheet.Cells[1, 3] = "First Name";
oSheet.Cells[1, 4] = "Handgun";
oSheet.Cells[1, 5] = "Action";
oSheet.Cells[1, 6] = "Rifle";
oSheet.Cells[1, 7] = "Smallbore";
oSheet.Cells[1, 8] = "Archery";
oSheet.Cells[1, 9] = "Safety Walk";
oSheet.Cells[1, 10] = "Extra Card";
for (int row = 0; row < dtNon.Rows.Count; row++)
{
section.Mask = ulong.Parse(dtNon.Rows[row]["SectionFlag"].ToString());
oSheet.Cells[row + 2, 1] = dtNon.Rows[row]["card"].ToString();
oSheet.Cells[row + 2, 2] = dtNon.Rows[row]["last name"].ToString();
oSheet.Cells[row + 2, 3] = dtNon.Rows[row]["first name"].ToString();
oSheet.Cells[row + 2, 4] = section.AnyOn(BitField.Flag.f2) ? "Yes" : "";
oSheet.Cells[row + 2, 5] = section.AnyOn(BitField.Flag.f6) ? "Yes" : "";
oSheet.Cells[row + 2, 6] = section.AnyOn(BitField.Flag.f5) ? "Yes" : "";
oSheet.Cells[row + 2, 7] = section.AnyOn(BitField.Flag.f3) ? "Yes" : "";
oSheet.Cells[row + 2, 8] = section.AnyOn(BitField.Flag.f1) ? "Yes" : "";
oSheet.Cells[row + 2, 9] = dtNon.Rows[row]["Walk"].ToString() == "Done" ? "yes" : "NO";
}
//Format A1:D1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "J1").Font.Bold = true;
oSheet.get_Range("A1", "J1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "J1").EntireColumn.AutoFit();
cnn.Close();
da.Dispose();
}
catch (Exception ex)
{
ErrorLogger.Log("Error trying to generate summary report", ex, true);
}
finally
{
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
Cursor.Current = Cursors.Default;
}
}
