public static void subFillGiftCatCbo(ref ComboBox _cboCat)
{
//fill the passed combo box with gift categories
OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn);
OleDbCommand cmdDB;
OleDbDataReader drCat;
string strSQL;
conDB.Open();
strSQL = "SELECT tblGiftCategory.lngGiftCategoryID, " +
"[tlkpGiftCategoryChoice].[strGiftCategoryChoice]+'--'+IIf(IsNull([tlkpGiftAllocation].[strGiftAllocation]),\"\",[tlkpGiftAllocation].[strGiftAllocation])+'--'+IIf(IsNull([tlkpGiftSubAllocation].[strGiftSubAllocation]),\"\",[tlkpGiftSubAllocation].[strGiftSubAllocation])+\" (\"+IIf(IsNull([tblGiftCategory].[strOLDesc]),\"\",[tblGiftCategory].[strOLDesc])+\")\" AS strGiftCategory " +
"FROM ((tblGiftCategory " +
"INNER JOIN tlkpGiftCategoryChoice ON tblGiftCategory.lngGiftCategoryChoiceID = tlkpGiftCategoryChoice.lngGiftCategoryChoiceID) " +
"LEFT JOIN tlkpGiftAllocation ON tblGiftCategory.lngGiftAllocation = tlkpGiftAllocation.lngGiftAllocation) " +
"LEFT JOIN tlkpGiftSubAllocation ON tblGiftCategory.lngGiftSubAllocation = tlkpGiftSubAllocation.lngGiftSubAllocation " +
"WHERE tblGiftCategory.blnUseOnline=True " +
"ORDER BY [tlkpGiftCategoryChoice].[strGiftCategoryChoice]+'--'+IIf(IsNull([tlkpGiftAllocation].[strGiftAllocation]),\"\",[tlkpGiftAllocation].[strGiftAllocation])+'--'+IIf(IsNull([tlkpGiftSubAllocation].[strGiftSubAllocation]),\"\",[tlkpGiftSubAllocation].[strGiftSubAllocation])+\" (\"+IIf(IsNull([tblGiftCategory].[strOLDesc]),\"\",[tblGiftCategory].[strOLDesc])+\")\";";
cmdDB = new OleDbCommand(strSQL, conDB);
drCat = cmdDB.ExecuteReader();
_cboCat.Items.Clear();
while (drCat.Read())
{
clsCboItem cboNew = new clsCboItem(long.Parse(drCat["lngGiftCategoryID"].ToString()), drCat["strGiftCategory"].ToString());
_cboCat.Items.Add(cboNew);
}
drCat.Close();
conDB.Close();
drCat.Dispose();
cmdDB.Dispose();
conDB.Dispose();
}
private void frmUploadEvents_Load(object sender, EventArgs e)
{
OleDbConnection objConn;
OleDbCommand objCommand;
OleDbDataReader dr;
clsCboItem cboItem;
string strSQL;
objConn = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn);
objConn.Open();
objCommand = new OleDbCommand();
strSQL = "SELECT tlkpCampName.lngCampID, " +
"tlkpCampName.strCampName " +
"FROM tlkpCampName " +
"WHERE tlkpCampName.blnUpload = True AND " +
"tlkpCampName.lngProgramTypeID = " + ((int)clsGlobalEnum.conPROGRAMTYPE.GroupEvent).ToString() + " " +
"ORDER BY tlkpCampName.strCampName;";
objCommand.CommandText = strSQL;
objCommand.Connection = objConn;
dr = objCommand.ExecuteReader();
while (dr.Read())
{
long lngNewCampID;
long.TryParse(dr["lngCampID"].ToString(), out lngNewCampID);
cboItem = new clsCboItem(lngNewCampID, dr["strCampName"].ToString());
cboProgram.Items.Add(cboItem);
}
dr.Close();
strSQL = "SELECT tblGGCC.lngGGCCID, " +
"[tblGGCC].[strGGCCName] & \" (\" & [tblGGCC].[dteStartDate] & \")\" AS strEventName " +
"FROM tblGGCC " +
"INNER JOIN tlkpCampName ON tblGGCC.lngProgramTypeID = tlkpCampName.lngCampID " +
"WHERE tlkpCampName.blnUpload = True AND " +
"tlkpCampName.lngProgramTypeID = " + ((int)clsGlobalEnum.conPROGRAMTYPE.GroupEvent).ToString() + " " +
"ORDER BY tblGGCC.dteStartDate;";
objCommand.CommandText = strSQL;
dr = objCommand.ExecuteReader();
while (dr.Read())
{
long lngNewGGCCID;
long.TryParse(dr["lngGGCCID"].ToString(), out lngNewGGCCID);
cboItem = new clsCboItem(lngNewGGCCID, dr["strEventName"].ToString());
cboSpecificEvent.Items.Add(cboItem);
}
dr.Close();
objConn.Close();
objCommand.Dispose();
objConn.Dispose();
radAllEvents.Checked = true;
}
public static void subFillCampaignCbo(ref ComboBox _cboCampaign)
{
//fill the passed combo box with campaign codes
OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn);
OleDbCommand cmdDB;
OleDbDataReader drCampaign;
string strSQL;
conDB.Open();
strSQL = "SELECT tlkpCampaignCodes.lngCampaignID, " +
"tlkpCampaignCodes.strCampaignCode " +
"FROM tlkpCampaignCodes " +
"ORDER BY tlkpCampaignCodes.strCampaignCode;";
cmdDB = new OleDbCommand(strSQL, conDB);
drCampaign = cmdDB.ExecuteReader();
_cboCampaign.Items.Clear();
while (drCampaign.Read())
{
clsCboItem cboNew = new clsCboItem(long.Parse(drCampaign["lngCampaignID"].ToString()), drCampaign["strCampaignCode"].ToString());
_cboCampaign.Items.Add(cboNew);
}
drCampaign.Close();
conDB.Close();
drCampaign.Dispose();
cmdDB.Dispose();
conDB.Dispose();
}
private void subFillCbos()
{
cboPaymentType.Items.Add(new clsCboItem(2, "Credit Card"));
cboPaymentType.Items.Add(new clsCboItem(11, "EFT"));
string strSQL = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT lngStateID, " +
"strState " +
"FROM tlkpStates " +
"ORDER BY strState";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drStates = cmdDB.ExecuteReader())
{
while (drStates.Read())
{
try
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drStates["lngStateID"]), Convert.ToString(drStates["strState"]));
cboBillState.Items.Add(cboNew);
}
catch { }
}
drStates.Close();
}
}
conDB.Close();
}
}
private void frmNCOASummaryReport_Load(object sender, EventArgs e)
{
//load processing dates
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
string strSQL = "";
strSQL = "SELECT First(Format([tblRecordCert].[dteProcessed],\"m/d/yyyy h:mm ampm\")) AS dteProcessed, tblRecordCert.strTicketID " +
"FROM tblRecordCert " +
"GROUP BY tblRecordCert.strTicketID, Format([tblRecordCert].[dteProcessed],\"yyyymmdd\") " +
"ORDER BY Format([tblRecordCert].[dteProcessed],\"yyyymmdd\")";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drCert = cmdDB.ExecuteReader())
{
while (drCert.Read())
{
string strCert = "";
int intTicketID=0;
try { strCert = Convert.ToString(drCert["dteProcessed"]); }
catch { strCert = "Err"; }
try { intTicketID = Convert.ToInt32(drCert["strTicketID"]); }
catch { intTicketID = 0; }
clsCboItem cboNew=new clsCboItem((long)intTicketID,strCert);
cboCert.Items.Add(cboNew);
}
drCert.Close();
}
}
conDB.Close();
}
}
private void subLoadPrimaryContact()
{
cboPrimaryContact.Items.Clear();
//load primary contact cbo from members list
for (int intI = 0; intI < lstMembers.Items.Count; intI++)
{
clsCboItem cboNew = new clsCboItem(((clsCboItem)lstMembers.Items[intI]).ID, ((clsCboItem)lstMembers.Items[intI]).Item);
cboPrimaryContact.Items.Add(cboNew);
}
}
private void subPopRegHoldCbo()
{
string strSQL="";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT tblRegHold.lngRegHoldID, " +
"[strLastCoName] & \", \" & [strFirstName] & \"--\" & [strCompanyName] AS Name, tblBlock.strBlockCode, tblRegHold.intHoldQty, tblRegHold.curCostShare, tblRegHold.dteDeadline AS Deadline " +
"FROM (tblBlock " +
"INNER JOIN tblRegHold ON tblBlock.lngBlockID = tblRegHold.lngBlockID) " +
"INNER JOIN tblRecords ON tblRecords.lngRecordID = tblRegHold.lngRecordID " +
"WHERE tblRegHold.intHoldQty > DCount(\"lngRegistrationID\", \"tblRegistrations\", \"lngRegHoldID=\" & [tblRegHold].[lngRegHoldID]) AND " +
"tblRegHold.dteDeadline >= Now() AND " +
"tblRegHold.lngBlockID = " + lngBlockID + " " +
"ORDER BY [strLastCoName] & \", \" & [strFirstName] & \"--\" & [strCompanyName];";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drHolds = cmdDB.ExecuteReader())
{
while (drHolds.Read())
{
string strCbo = "";
long lngCbo = 0;
try { lngCbo = Convert.ToInt32(drHolds["lngRegHoldID"]); }
catch { lngCbo = 0; }
try { strCbo = Convert.ToString(drHolds["Name"]) + ", " + Convert.ToString(drHolds["intHoldQty"]) + ", " + Convert.ToDecimal(drHolds["curCostShare"]).ToString("C"); }
catch { strCbo = ""; }
clsCboItem cboNew = new clsCboItem(lngCbo, strCbo);
cboRegHoldID.Items.Add(cboNew);
}
drHolds.Close();
}
}
conDB.Close();
}
}
private void subAddIRToMemberList(long _lngRecordID)
{
string strSQL = "";
strSQL = "SELECT strLastCoName & \", \" & strFirstName AS strName " +
"FROM tblRecords " +
"WHERE lngRecordID=" + _lngRecordID.ToString();
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
try
{
clsCboItem cboNew = new clsCboItem(_lngRecordID, cmdDB.ExecuteScalar().ToString());
lstMembers.Items.Add(cboNew);
}
catch { }
}
conDB.Close();
}
}
private void subLoadCbos()
{
string strSQL = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT tlkpStates.lngStateID, " +
"tlkpStates.strState " +
"FROM tlkpStates " +
"ORDER BY tlkpStates.strState";
using (OleDbCommand cmdCbo = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drStates = cmdCbo.ExecuteReader())
{
while (drStates.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drStates["lngStateID"]), drStates["strState"].ToString());
cboMORState.Items.Add(cboNew);
}
drStates.Close();
}
strSQL = "SELECT tlkpCountry.lngCountryID, " +
"tlkpCountry.strCountry " +
"FROM tlkpCountry " +
"ORDER BY tlkpCountry.lngCountryID";
cmdCbo.CommandText = strSQL;
using (OleDbDataReader drCountries = cmdCbo.ExecuteReader())
{
while (drCountries.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drCountries["lngCountryID"]), drCountries["strCountry"].ToString());
cboMORCountry.Items.Add(cboNew);
}
drCountries.Close();
}
strSQL = "SELECT tlkpMORTypes.lngMORTypeID, " +
"tlkpMORTypes.strMORType " +
"FROM tlkpMORTypes " +
"ORDER BY tlkpMORTypes.strMORType";
cmdCbo.CommandText = strSQL;
using (OleDbDataReader drMORTypes = cmdCbo.ExecuteReader())
{
while (drMORTypes.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drMORTypes["lngMORTypeID"]), drMORTypes["strMORType"].ToString());
cboMORType.Items.Add(cboNew);
}
drMORTypes.Close();
}
}
conDB.Close();
}
subLoadPrimaryContact();
}
private void subFillSpecificCboSource(string _strType)
{
string strSQL = "";
cboSpecific.Items.Clear();
if (_strType == "Ind")
strSQL = "SELECT tblBlock.lngBlockID AS lngID, " +
"tblBlock.strBlockCode AS strName " +
"FROM tblBlock " +
"ORDER BY tblBlock.strBlockCode;";
else if (_strType == "CC")
strSQL = "SELECT tblGGCC.lngGGCCID AS lngID, " +
"tblGGCC.strGGCCName AS strName " +
"FROM tblGGCC " +
"WHERE tblGGCC.lngGroupTypeID=0 AND " +
"tblGGCC.lngGroupStatusID<>1 " +
"ORDER BY tblGGCC.strGGCCName;";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drCbo["lngID"]), Convert.ToString(drCbo["strName"]));
cboSpecific.Items.Add(cboNew);
}
drCbo.Close();
}
}
conDB.Close();
}
}
private void subLoadCbo(ComboBox _cboToLoad, string _strDefault)
{
string strSQL = "";
switch (_cboToLoad.Name)
{
case "cboReferredBy":
strSQL = "SELECT tblReferredBy.strReferredBy AS strValue " +
"FROM tblReferredBy " +
"ORDER BY tblReferredBy.strReferredBy";
break;
}
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToString(drCbo["strValue"]), Convert.ToString(drCbo["strValue"]));
_cboToLoad.Items.Add(cboNew);
if (Convert.ToString(drCbo["strValue"]) == _strDefault) _cboToLoad.SelectedItem = cboNew;
}
drCbo.Close();
}
}
conDB.Close();
}
_cboToLoad.Items.Insert(0, new clsCboItem("", ""));
}
private void subLoadCbo(ComboBox _cboToLoad, long _lngDefaultID)
{
string strSQL = "";
switch (_cboToLoad.Name)
{
case "cboCamperState":
case "cboBillState":
strSQL = "SELECT tlkpStates.lngStateID AS lngID, " +
"tlkpStates.strState AS strValue " +
"FROM tlkpStates " +
"ORDER BY tlkpStates.strState;";
break;
case "cboPmtMethod":
strSQL = "SELECT tlkpPaymentType.lngPaymentTypeID AS lngID, " +
"tlkpPaymentType.strPaymentType AS strValue " +
"FROM tlkpPaymentType " +
"WHERE tlkpPaymentType.lngPaymentTypeID=2 OR " +
"tlkpPaymentType.lngPaymentTypeID=11 " +
"ORDER BY tlkpPaymentType.strPaymentType;";
break;
case "cboBlockChoice1":
case "cboBlockChoice2":
case "cboBlockChoice3":
case "cboBlockChoice4":
case "cboBlockChoice5":
case "cboBlockChoice6":
case "cboBlockChoice7":
case "cboBlockChoice8":
case "cboBlockChoice9":
case "cboBlockChoice10":
case "cboBlockChoice11":
strSQL = "SELECT tblBlock.lngBlockID AS lngID, " +
"tblBlock.strBlockCode AS strValue " +
"FROM tblBlock " +
"ORDER BY tblBlock.strBlockCode;";
break;
}
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNew = new clsCboItem(Convert.ToInt32(drCbo["lngID"]), Convert.ToString(drCbo["strValue"]));
_cboToLoad.Items.Add(cboNew);
if (Convert.ToInt32(drCbo["lngID"]) == _lngDefaultID) _cboToLoad.SelectedItem = cboNew;
}
drCbo.Close();
}
}
conDB.Close();
}
}
private void subLoadCbos()
{
string strSQL = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT tlkpWeekDesc.lngWeekID, " +
"tlkpWeekDesc.strWeekDesc " +
"FROM tlkpWeekDesc " +
"ORDER BY tlkpWeekDesc.intSortOrder, " +
"tlkpWeekDesc.dteStartDate";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNewWeek;
long lngWeekID = 0;
string strWeek = "";
try { lngWeekID = Convert.ToInt32(drCbo["lngWeekID"]); }
catch { lngWeekID = 0; }
try { strWeek = Convert.ToString(drCbo["strWeekDesc"]); }
catch { strWeek = ""; }
cboNewWeek = new clsCboItem(lngWeekID, strWeek);
cboWeek.Items.Add(cboNewWeek);
}
drCbo.Close();
}
//program cbo
strSQL = "SELECT tlkpCampName.lngCampID, " +
"tlkpCampName.strCampName " +
"FROM tlkpCampName " +
"WHERE tlkpCampName.lngProgramTypeID=1 " +
"ORDER BY tlkpCampName.strCampName";
cmdDB.CommandText = strSQL;
cmdDB.Parameters.Clear();
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNewCamp;
long lngCampID = 0;
string strCampName = "";
try { lngCampID = Convert.ToInt32(drCbo["lngCampID"]); }
catch { lngCampID = 0; }
try { strCampName = Convert.ToString(drCbo["strCampName"]); }
catch { strCampName = ""; }
cboNewCamp = new clsCboItem(lngCampID, strCampName);
cboProgram.Items.Add(cboNewCamp);
}
drCbo.Close();
}
//housing cbo
strSQL = "SELECT tlkpHousingName.lngHousingID, " +
"tlkpHousingName.strHousingName " +
"FROM tlkpHousingName " +
"ORDER BY tlkpHousingName.strHousingName";
cmdDB.CommandText = strSQL;
cmdDB.Parameters.Clear();
using (OleDbDataReader drCbo = cmdDB.ExecuteReader())
{
while (drCbo.Read())
{
clsCboItem cboNewHousing;
long lngHousingID = 0;
string strHousingName = "";
try { lngHousingID = Convert.ToInt32(drCbo["lngHousingID"]); }
catch { lngHousingID = 0; }
try { strHousingName = Convert.ToString(drCbo["strHousingName"]); }
catch { strHousingName = ""; }
cboNewHousing = new clsCboItem(lngHousingID, strHousingName);
cboHousing.Items.Add(cboNewHousing);
}
drCbo.Close();
}
}
conDB.Close();
}
}
private void subPopLsts()
{
//load program type, status options; set default vals for dates
txtStart.Text = DateTime.Now.ToString("MM/dd/yyyy");
txtEnd.Text = txtStart.Text;
string strSQL = "";
using (OleDbConnection conDB = new OleDbConnection(clsAppSettings.GetAppSettings().strCTConn))
{
conDB.Open();
strSQL = "SELECT lngCampID, " +
"strCampName " +
"FROM tlkpCampName " +
"WHERE lngProgramTypeID=" + lngProgramType + " " +
"ORDER BY strCampName";
using (OleDbCommand cmdDB = new OleDbCommand(strSQL, conDB))
{
using (OleDbDataReader drPrograms = cmdDB.ExecuteReader())
{
while (drPrograms.Read())
{
clsCboItem lstItem;
long lngID = 0;
string strVal = "";
try { lngID = Convert.ToInt32(drPrograms["lngCampID"]); }
catch { lngID = 0; }
try { strVal = Convert.ToString(drPrograms["strCampName"]); }
catch { strVal = ""; }
lstItem = new clsCboItem(lngID, strVal);
lstProgramTypes.Items.Add(lstItem);
}
drPrograms.Close();
}
//only load/display status for gg or cc
if (lngProgramType > 1)
{
strSQL = "SELECT lngGroupStatusID, " +
"strGroupStatus " +
"FROM tlkpGroupStatus " +
"ORDER BY strGroupStatus";
cmdDB.Parameters.Clear();
cmdDB.CommandText = strSQL;
using (OleDbDataReader drStatus = cmdDB.ExecuteReader())
{
while (drStatus.Read())
{
clsCboItem lstItem;
long lngID = 0;
string strVal = "";
try { lngID = Convert.ToInt32(drStatus["lngGroupStatusID"]); }
catch { lngID = 0; }
try { strVal = Convert.ToString(drStatus["strGroupStatus"]); }
catch { strVal = ""; }
lstItem = new clsCboItem(lngID, strVal);
lstStatus.Items.Add(lstItem);
}
drStatus.Close();
}
}
else
fraStatus.Visible = false;
}
conDB.Close();
}
}