private void SetDataSource(ReportDocument Report)
{
ContactGroupCategory enumContactGroupCategory = (ContactGroupCategory)Enum.Parse(typeof(ContactGroupCategory), cboContactGroupCategory.SelectedValue);
ReportDataset rptds = new ReportDataset();
Contacts clsContact = new Contacts();
System.Data.DataTable dt = clsContact.AdvanceSearchDataTable(enumContactGroupCategory, cboContactCode.SelectedItem.Value, cboContactName.SelectedItem.Value, Convert.ToInt16(cboDeleted.SelectedItem.Value), Convert.ToInt32(cboGroup.SelectedItem.Value), false, "ContactID", SortOption.Ascending);
clsContact.CommitAndDispose();
foreach (DataRow dr in dt.Rows)
{
DataRow drNew = rptds.Contacts.NewRow();
foreach (DataColumn dc in rptds.Contacts.Columns)
{
drNew[dc] = dr[dc.ColumnName];
}
rptds.Contacts.Rows.Add(drNew);
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
public System.Data.DataTable ListAsDataTable(ContactGroupCategory Category = ContactGroupCategory.BOTH, string SearchKey = null, string SortField = "ContactGroupName", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE 1=1 ";
if (SearchKey != "")
{
SQL += "AND (ContactGroupCode LIKE @SearchKey OR ContactGroupName LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%");
}
if (Category != ContactGroupCategory.BOTH)
{
SQL += "AND (ContactGroupCategory = @Category OR ContactGroupCategory = 3) ";
cmd.Parameters.AddWithValue("@Category", Category.ToString("d"));
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ContactGroupCode") + " ";
SQL += SortOrder == SortOption.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return(dt);
}
private void LoadOptions()
{
cboContactGroupCategory.Items.Clear();
foreach (string strEnum in Enum.GetNames(typeof(Data.ContactGroupCategory)))
{
ContactGroupCategory itemContactGroupCategory = (ContactGroupCategory)Enum.Parse(typeof(ContactGroupCategory), strEnum);
cboContactGroupCategory.Items.Add(new ListItem(strEnum, itemContactGroupCategory.ToString("d")));
}
}
protected void lstItem_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Header)
{
LoadSortFieldOptions(e);
}
else if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
DataRowView dr = (DataRowView)e.Item.DataItem;
ImageButton imgItemDelete = (ImageButton)e.Item.FindControl("imgItemDelete");
ImageButton imgItemEdit = (ImageButton)e.Item.FindControl("imgItemEdit");
HtmlInputCheckBox chkList = (HtmlInputCheckBox)e.Item.FindControl("chkList");
chkList.Value = dr["ContactGroupID"].ToString();
if (chkList.Value == "1" || chkList.Value == "2")
{
chkList.Attributes.Add("disabled", "false");
imgItemDelete.Enabled = false; imgItemDelete.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
imgItemEdit.Enabled = false; imgItemEdit.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
}
else
{
imgItemDelete.Enabled = cmdDelete.Visible; if (!imgItemDelete.Enabled)
{
imgItemDelete.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
}
imgItemEdit.Enabled = cmdEdit.Visible; if (!imgItemEdit.Enabled)
{
imgItemEdit.ImageUrl = Constants.ROOT_DIRECTORY + "/_layouts/images/blank.gif";
}
if (imgItemDelete.Enabled)
{
imgItemDelete.Attributes.Add("onClick", "return confirm_item_delete();");
}
}
HyperLink lnkContactGroupCode = (HyperLink)e.Item.FindControl("lnkContactGroupCode");
lnkContactGroupCode.Text = dr["ContactGroupCode"].ToString();
lnkContactGroupCode.NavigateUrl = "Default.aspx?task=" + Common.Encrypt("details", Session.SessionID) + "&id=" + Common.Encrypt(chkList.Value, Session.SessionID);
HyperLink lnkContactGroupName = (HyperLink)e.Item.FindControl("lnkContactGroupName");
lnkContactGroupName.Text = dr["ContactGroupName"].ToString();
lnkContactGroupName.NavigateUrl = "Default.aspx?task=" + Common.Encrypt("details", Session.SessionID) + "&id=" + Common.Encrypt(chkList.Value, Session.SessionID);
Label lblContactGroupCategory = (Label)e.Item.FindControl("lblContactGroupCategory");
ContactGroupCategory category = (ContactGroupCategory)Enum.Parse(typeof(ContactGroupCategory), dr["ContactGroupCategory"].ToString());
lblContactGroupCategory.Text = category.ToString("G");
}
}
public MySqlDataReader List(ContactGroupCategory Category, string SortField, SortOption SortOrder)
{
try
{
string SQL = SQLSelect() + "WHERE (ContactGroupCategory = @Category OR ContactGroupCategory = 3) " +
"ORDER BY " + SortField;
if (SortOrder == SortOption.Ascending)
{
SQL += " ASC";
}
else
{
SQL += " DESC";
}
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@Category", Category.ToString("d"));
return(base.ExecuteReader(cmd));
}
catch (Exception ex)
{
{
}
throw base.ThrowException(ex);
}
}
public DataTable ListAsDataTable(ContactGroupCategory groupCategory = ContactGroupCategory.BOTH, long ContactID = 0, string ContactCode = "", string ContactName = "", string ContactGroupCode = "", string RewardCardNo = "", string Name = "", bool hasCreditOnly = false, int intDeleted = -1, int Limit = 0, string SortField = "", SortOption SortOrder = SortOption.Ascending, string BirthDateFrom = Constants.C_DATE_MIN_VALUE_STRING, string BirthDateTo = Constants.C_DATE_MIN_VALUE_STRING, string AnniversaryDateFrom = Constants.C_DATE_MIN_VALUE_STRING, string AnniversaryDateTo = Constants.C_DATE_MIN_VALUE_STRING, int BirthMonth = 0, int AnniversaryMonth = 0, int ModeOfTerms = -1)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = "CALL procContactSelect(@ContactGroupCategory, @ContactID, @ContactCode, @ContactName, @ContactGroupCode, @RewardCardNo, @Name, @BirthMonth, @AnniversaryMonth, @BirthDateFrom, @BirthDateTo, @AnniversaryDateFrom, @AnniversaryDateTo, @hasCreditOnly, @intDeleted, @intModeOfTerms, @lngLimit, @SortField, @SortOrder)";
cmd.Parameters.AddWithValue("@ContactGroupCategory", groupCategory.ToString("d"));
cmd.Parameters.AddWithValue("@ContactID", ContactID);
cmd.Parameters.AddWithValue("@ContactCode", ContactCode);
cmd.Parameters.AddWithValue("@ContactName", ContactName);
cmd.Parameters.AddWithValue("@ContactGroupCode", ContactGroupCode);
cmd.Parameters.AddWithValue("@RewardCardNo", RewardCardNo);
cmd.Parameters.AddWithValue("@Name", Name);
cmd.Parameters.AddWithValue("@BirthMonth", BirthMonth);
cmd.Parameters.AddWithValue("@AnniversaryMonth", AnniversaryMonth);
cmd.Parameters.AddWithValue("@BirthDateFrom", BirthDateFrom);
cmd.Parameters.AddWithValue("@BirthDateTo", BirthDateTo);
cmd.Parameters.AddWithValue("@AnniversaryDateFrom", AnniversaryDateFrom);
cmd.Parameters.AddWithValue("@AnniversaryDateTo", AnniversaryDateTo);
cmd.Parameters.AddWithValue("@hasCreditOnly", hasCreditOnly);
cmd.Parameters.AddWithValue("@intDeleted", intDeleted);
cmd.Parameters.AddWithValue("@intModeOfTerms", ModeOfTerms);
cmd.Parameters.AddWithValue("@lngLimit", Limit);
cmd.Parameters.AddWithValue("@SortField", SortField);
cmd.Parameters.AddWithValue("@SortOrder", SortOrder == SortOption.Ascending ? "ASC" : "DESC");
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
//public MySqlDataReader AdvanceSearch(string ContactCode, string ContactName, Int16 Deleted, Int32 ContactGroupID, bool HasCreditOnly, string SortField, SortOption SortOrder)
//{
// try
// {
// MySqlCommand cmd = new MySqlCommand();
// cmd.CommandType = System.Data.CommandType.Text;
// string SQL = SQLSelect() + "WHERE 1=1 ";
// if (ContactCode != null && ContactCode != string.Empty && ContactCode != "" && ContactCode != "0" && ContactName != Constants.ALL)
// {
// SQL += " AND a.ContactCode = @ContactCode ";
// cmd.Parameters.AddWithValue("@ContactCode", ContactCode);
// }
// if (ContactName != null && ContactName != string.Empty && ContactName != "" && ContactName != "0" && ContactName != Constants.ALL)
// {
// SQL += " AND a.ContactName = @ContactName ";
// cmd.Parameters.AddWithValue("@ContactName", ContactName);
// }
// if (ContactGroupID != 0)
// {
// SQL += "AND a.ContactGroupID = @ContactGroupID ";
// cmd.Parameters.AddWithValue("@ContactGroupID", ContactGroupID);
// }
// if (HasCreditOnly == true)
// SQL += "AND Credit > 0 ";
// if (Deleted != 2)
// {
// SQL += "AND a.Deleted = @Deleted ";
// MySqlParameter prmDeleted = new MySqlParameter("@Deleted",MySqlDbType.Byte);
// prmDeleted.Value = Deleted;
// cmd.Parameters.Add(prmDeleted);
// }
// SQL += "ORDER BY " + SortField;
// if (SortOrder == SortOption.Ascending)
// SQL += " ASC";
// else
// SQL += " DESC";
// cmd.CommandText = SQL;
// return base.ExecuteReader(cmd);
// }
// catch (Exception ex)
// {
// throw base.ThrowException(ex);
// }
//}
public System.Data.DataTable AdvanceSearchDataTable(ContactGroupCategory ContactGroupCategory, string ContactCode, string ContactName, Int16 Deleted, Int32 ContactGroupID, bool HasCreditOnly, string SortField, SortOption SortOrder)
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE 1=1 ";
if (ContactGroupCategory == ContactGroupCategory.CUSTOMER)
{
SQL += "AND (b.ContactGroupCategory = @CustomerCategory OR b.ContactGroupCategory = @BothCategory) ";
//SQL += "OR (ContactID IN (SELECT CustomerID FROM tblContactRewards WHERE RewardCardNo LIKE @SearchKey)) ";
cmd.Parameters.AddWithValue("@CustomerCategory", ContactGroupCategory.CUSTOMER.ToString("d"));
cmd.Parameters.AddWithValue("@BothCategory", ContactGroupCategory.BOTH.ToString("d"));
}
if (ContactGroupCategory == ContactGroupCategory.SUPPLIER)
{
SQL += "AND (b.ContactGroupCategory = @SupplierCategory OR b.ContactGroupCategory = @BothCategory) ";
cmd.Parameters.AddWithValue("@SupplierCategory", ContactGroupCategory.SUPPLIER.ToString("d"));
cmd.Parameters.AddWithValue("@BothCategory", ContactGroupCategory.BOTH.ToString("d"));
}
if (ContactCode != null && ContactCode != string.Empty && ContactCode != "" && ContactCode != "0")
{
SQL += " AND a.ContactCode = @ContactCode ";
cmd.Parameters.AddWithValue("@ContactCode", ContactCode);
}
if (ContactName != null && ContactName != string.Empty && ContactName != "" && ContactName != "0")
{
SQL += " AND a.ContactName = @ContactName ";
cmd.Parameters.AddWithValue("@ContactName", ContactName);
}
if (ContactGroupID != 0)
{
SQL += "AND a.ContactGroupID = @ContactGroupID ";
cmd.Parameters.AddWithValue("@ContactGroupID", ContactGroupID);
}
if (HasCreditOnly == true)
SQL += "AND Credit > 0 ";
if (Deleted != 2)
{
SQL += "AND a.Deleted = @Deleted ";
cmd.Parameters.AddWithValue("@Deleted", Deleted);
}
SQL += "ORDER BY " + SortField;
if (SortOrder == SortOption.Ascending)
SQL += " ASC";
else
SQL += " DESC";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListAsDataTable(ContactGroupCategory Category = ContactGroupCategory.BOTH, string SearchKey = null, string SortField = "ContactGroupName", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "WHERE 1=1 ";
if (SearchKey != "")
{
SQL += "AND (ContactGroupCode LIKE @SearchKey OR ContactGroupName LIKE @SearchKey) ";
cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%");
}
if (Category != ContactGroupCategory.BOTH)
{
SQL += "AND (ContactGroupCategory = @Category OR ContactGroupCategory = 3) ";
cmd.Parameters.AddWithValue("@Category", Category.ToString("d"));
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "ContactGroupCode") + " ";
SQL += SortOrder == SortOption.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
public MySqlDataReader List(ContactGroupCategory Category, string SortField, SortOption SortOrder)
{
try
{
string SQL = SQLSelect() + "WHERE (ContactGroupCategory = @Category OR ContactGroupCategory = 3) " +
"ORDER BY " + SortField;
if (SortOrder == SortOption.Ascending)
SQL += " ASC";
else
SQL += " DESC";
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@Category", Category.ToString("d"));
return base.ExecuteReader(cmd);
}
catch (Exception ex)
{
{
}
throw base.ThrowException(ex);
}
}
