public void UserNameExist(string suario, string searchType, string accName)
{
DataTable accNameTable = new DataTable();
string nameInUse = "0";
Rep_Account accountSearch = new Rep_Account();
//DataTable User = (DataTable)Session["user"];
//userId = "4";
accName = Regex.Replace(accName, "[ ]{2,}", " ");
accNameTable = accountSearch.SelectSearch(int.Parse(suario), searchType, accName, "AccountSearch");
if (accNameTable.Rows.Count > 0)
{
nameInUse = accNameTable.Rows[0]["AccID"].ToString();
}
else
{
nameInUse = "0";
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(nameInUse));
}
protected void Export_OnClick(object sender, EventArgs e)
{
//CustomersGRD.ExportSettings.ExportOnlyData = true;
//CustomersGRD.ExportSettings.IgnorePaging = true;
//CustomersGRD.ExportSettings.OpenInNewWindow = false;
//CustomersGRD.ExportSettings.UseItemStyles = true;
////CustomersGRD.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
//CustomersGRD.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
////CustomersGRD.ExportSettings.Excel.Format = (GridExcelExportFormat)Enum.Parse(typeof(GridExcelExportFormat), "Xlsx");
////CustomersGRD.ExportSettings.FileName = "Customers (" + DateTime.Now + ")";
//CustomersGRD.ExportSettings.FileName = string.Format("Customers_{0}", DateTime.Now);
//CustomersGRD.MasterTableView.ExportToExcel();
//PostBackTrigger trigger = new PostBackTrigger();
//trigger.ControlID = "B_42";
//AccountUpdatePanel.Triggers.Add(trigger);
//TriggerId = ID.ToString();
DataTable expCustomersDt = new DataTable();
//expCustomersDt = SelectCustomers();
try
{
Rep_Account accountSearch = new Rep_Account();
DataTable User = (DataTable)Session["user"];
string userId = User.Rows[0]["UserID"].ToString();
expCustomersDt = accountSearch.SelectSearch(int.Parse(userId), "Excel", SearchTXT.Text, "AccountSearch");
}
catch (Exception ex)
{
string error = ex.Message;
string errText = error.Replace("\'", "");
string AlertMSG = "Error occurred please try again ";
AlertMSG += errText;
this.ClientScript.RegisterStartupScript(this.GetType(), this.GetType().Name,
string.Format("window.alert('{0}');", AlertMSG), true);
}
if (expCustomersDt.Rows.Count <= 0)
{
//ScriptManager.RegisterStartupScript(Page, Page.GetType(), "key", string.Format("window.alert('{0}');", "No records to export"), true);
}
else
{
expCustomersDt.Columns.Remove("AccID");
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(expCustomersDt, "Accounts");
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + string.Format("Accounts_{0}", DateTime.Now) + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
private DataTable SelectCustomers()
{
//string SelectCommand = "SELECT Account.AccID, Account.AccNo, Account.AccName, Account.Cancelled, AccountInfo.LineID, AccountInfo.InfoType, AccountInfo.InfoData, AccountInfo.Cancelled AS Expr1 "
// + " FROM Account INNER JOIN "
// + " AccountInfo ON Account.AccID = AccountInfo.AccID "
// + " and Account.Cancelled = 0 "
// + " and CONCAT(Account.AccNo ,' ' , Account.AccName ,' ', AccountInfo.InfoData) like @search ";
//SqlCommand SqlSelectCommand = new SqlCommand(SelectCommand, CustomersConnection);
// SqlSelectCommand.Parameters.Add("@SearchText", SqlDbType.NVarChar, 65);
// SqlSelectCommand.Parameters.Add("@SearchText", System.Data.SqlDbType.Text);
// SqlSelectCommand.Parameters["@SearchText"].Value = SearchTXT.Text;
// adapter.InsertCommand.Parameters.Add("@CompanyName",
//SqlDbType.VarChar, 40, "CompanyName");
//SqlDataAdapter CustomersDA = new SqlDataAdapter("SP_Account_Search", CustomersConnection);
//CustomersDA.SelectCommand.CommandType = CommandType.StoredProcedure;
//CustomersDA.SelectCommand.Parameters.AddWithValue("@UserID", userId);
//CustomersDA.SelectCommand.Parameters.AddWithValue("@SearchType", "Mobile");
//CustomersDA.SelectCommand.Parameters.AddWithValue("@ModuleName", "AccountSearch");
//CustomersDA.SelectCommand.Parameters.AddWithValue("@search", "%" + SearchTXT.Text + "%");
//CustomersDA.SelectCommand.Parameters.AddWithValue("@SearchText", SearchTXT.Text);
//Add(SearchTXT.Text);
//CustomersDA.Fill(CustomersDT);
DataTable CustomersDT = new DataTable();
DataTable boundTable = new DataTable();
try
{
Rep_Account accountSearch = new Rep_Account();
Rep_General general = new Rep_General();
DataTable User = (DataTable)Session["user"];
string userId = User.Rows[0]["UserID"].ToString();
CustomersDT = accountSearch.SelectSearch(int.Parse(userId), "Mobile", SearchTXT.Text, "AccountSearch");
//if query string
if (Request.QueryString[general.ObfuscateQueryString("accID")] != null)
{
DataTable contactsDt = new DataTable();
int accId = general.DecryptIds(int.Parse(Request.QueryString[general.ObfuscateQueryString("accID")].ToString()));
contactsDt = accountSearch.SelectContactSearch(int.Parse(userId), accId.ToString());
//var rows = from r in CustomersDT.AsEnumerable()
// //make sure there aren't any matching names in dt2
// where contactsDt.AsEnumerable().Any(r2 => r["AccID"].ToString() != r2["AccID"].ToString() && r["AccID"].ToString() != accId.ToString())
// select r;
//var rows = CustomersDT.AsEnumerable().Where(r => contactsDt.AsEnumerable()
// //make sure there aren't any matching names in dt2
// .Any( r2 => r["AccID"].ToString() != r2["AccID"].ToString() ));
//boundTable = rows.CopyToDataTable();
if (CustomersDT.Rows.Count > 0)
{
boundTable = CustomersDT.Rows.OfType <DataRow>()
.Where(a => CustomersDT.Rows.OfType <DataRow>().Select(k => Convert.ToInt32(k["AccID"])).Except(contactsDt.Rows.OfType <DataRow>()
.Select(k => Convert.ToInt32(k["AccID"])).ToList()).Contains(Convert.ToInt32(a["AccID"])) && Convert.ToInt32(a["AccID"]) != accId).CopyToDataTable();
}
}
else
{
if (CustomersDT.Rows.Count > 0)
{
boundTable = CustomersDT;
}
}
}
catch (Exception ex)
{
string error = ex.Message;
string errText = error.Replace("\'", "");
string AlertMSG = "Error occurred please try again ";
AlertMSG += errText;
this.ClientScript.RegisterStartupScript(this.GetType(), this.GetType().Name,
string.Format("window.alert('{0}');", AlertMSG), true);
}
return(boundTable);
}