protected void FillEntityConfigurationHead(IDataReader dr, Entity.ConfigurationHead ObjConfigurationHead, int Type)
{
ObjConfigurationHead.ConfigurationHeadName = GetField(dr, "ConfigurationHeadName");
ObjConfigurationHead.ConfigurationHeadId = GetFieldInt64(dr, "ConfigurationHeadId");
ObjConfigurationHead.HeadNote = GetField(dr, "HeadNote");
ObjConfigurationHead.InputTypeId = GetFieldInt(dr, "InputTypeId");
ObjConfigurationHead.InputType = GetField(dr, "InputType");
ObjConfigurationHead.MinInputValue = GetField(dr, "MinInputValue");
ObjConfigurationHead.MaxInputValue = GetField(dr, "MaxInputValue");
ObjConfigurationHead.IsCompulsaryEntry = GetFieldBool(dr, "IsCompulsaryEntry");
ObjConfigurationHead.TemplateDropdownMasterId = GetFieldInt64(dr, "TemplateDropdownMasterId");
ObjConfigurationHead.TemplateDropdownName = GetField(dr, "TemplateDropdownName");
ObjConfigurationHead.Remark = GetField(dr, "Remark");
ObjConfigurationHead.DisplayPreferenceNo = GetFieldDecimal(dr, "DisplayPreferenceNo");
ObjConfigurationHead.FormId = GetFieldInt(dr, "FormId");
ObjConfigurationHead.FormTitle = GetField(dr, "FormTitle");
ObjConfigurationHead.MaxLength = GetFieldInt(dr, "MaxLength");
ObjConfigurationHead.ChildDBId = GetFieldInt64(dr, "ChildDBId");
ObjConfigurationHead.FormComponentMasterId = GetFieldInt64(dr, "FormComponentMasterId");
ObjConfigurationHead.RegexMasterId = GetFieldInt(dr, "RegexMasterId");
ObjConfigurationHead.FormName = GetField(dr, "FormName");
ObjConfigurationHead.IsUniqueKey = GetFieldBool(dr, "IsUniqueKey");
ObjConfigurationHead.IsCompositeUniqueKey = GetFieldBool(dr, "IsCompositeUniqueKey");
ObjConfigurationHead.IsSearchableDropdown = GetFieldBool(dr, "IsSearchableDropDown");
}
public void FillParamsDetails(SqlParameterCollection parameters, Entity.ConfigurationHead Entity)
{
parameters.AddWithValue("@ConfigurationHeadId", Entity.ConfigurationHeadId);
parameters.AddWithValue("@ConfigurationHeadName", Entity.ConfigurationHeadName);
parameters.AddWithValue("@HeadNote", Entity.HeadNote);
parameters.AddWithValue("@DisplayPreferenceNo", Entity.DisplayPreferenceNo);
parameters.AddWithValue("@InputTypeId", Entity.InputTypeId);
parameters.AddWithValue("@MinInputValue", Entity.MinInputValue);
parameters.AddWithValue("@MaxInputValue", Entity.MaxInputValue);
parameters.AddWithValue("@IsCompulsaryEntry", Entity.IsCompulsaryEntry);
parameters.AddWithValue("@TemplateDropdownMasterId", Entity.TemplateDropdownMasterId);
parameters.AddWithValue("@Remark", Entity.Remark);
parameters.AddWithValue("@UserId", Entity.UserId);
parameters.AddWithValue("@HostName", Entity.HostName);
parameters.AddWithValue("@IpAddr", Entity.IpAddr);
parameters.AddWithValue("@EventId", Entity.EventId);
parameters.AddWithValue("@TransactionId", Entity.TransactionId);
parameters.AddWithValue("@ChildDBId", Entity.ChildDBId);
parameters.AddWithValue("@MaxLength", Entity.MaxLength);
parameters.AddWithValue("@FormId", Entity.FormId);
parameters.AddWithValue("@IsUniqueKey", Entity.IsUniqueKey);
parameters.AddWithValue("@IsCompositeUniqueKey", Entity.IsCompositeUniqueKey);
parameters.AddWithValue("@FormComponentMasterId", Entity.FormComponentMasterId);
parameters.AddWithValue("@RegexMasterId", Entity.RegexMasterId);
parameters.AddWithValue("@IsSearchableDropDown", Entity.IsSearchableDropdown);
}
public void Summary_QueryDetails(SqlParameterCollection parameters, Entity.ConfigurationHead Entity)
{
parameters.AddWithValue("@TableName", Entity.TableName);
parameters.AddWithValue("@Summary_Query_Data", Entity.dtQuerydata);
parameters.AddWithValue("@Query", Entity.QueryName);
parameters.AddWithValue("@QueryDisplayText", Entity.QueryDisplayText);
parameters.AddWithValue("@QueryMasterid", Entity.QueryMasterId);
}
public void HeadParamsDetails(SqlParameterCollection parameters, Entity.ConfigurationHead Entity)
{
parameters.AddWithValue("@QueryName", Entity.QueryName);
parameters.AddWithValue("@FormID", Entity.FormName);
parameters.AddWithValue("@StrColumnsid", Entity.ColumnsId);
parameters.AddWithValue("@StrColumnsName", Entity.ColumnsName);
parameters.AddWithValue("@HostName", Entity.HostName);
parameters.AddWithValue("@IpAddr", Entity.IpAddr);
//parameters.AddWithValue("@EventId", Entity.EventId );
parameters.AddWithValue("@ConditionString", Entity.ConditionString);
parameters.AddWithValue("@UserId", Entity.UserId);
}
public virtual int InsertSummary_Query_Data(Entity.ConfigurationHead Entity)
{
SqlCommand cmdInsert = new SqlCommand();
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.CommandTimeout = 0;
cmdInsert.CommandText = "dbo.Inert_Ms_Summary_Query_Builder";
this.Summary_QueryDetails(cmdInsert.Parameters, Entity);
int res = this.ExecuteNonQuery(cmdInsert);
base.ForceCloseConnection(cmdInsert);
return(res);
}
public virtual int AddUpdateHeadName(Entity.ConfigurationHead Entity)
{
SqlCommand cmdInsert = new SqlCommand();
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.CommandTimeout = 0;
cmdInsert.CommandText = "dbo.Insert_ms_NewQueryGenerationForm";
this.HeadParamsDetails(cmdInsert.Parameters, Entity);
int res = this.ExecuteNonQuery(cmdInsert);
base.ForceCloseConnection(cmdInsert);
return(res);
}
public virtual List <Entity.ConfigurationHead> Get_ColumnNameFromQueryMaster(Int32 QueryMasterId)
{
List <Entity.ConfigurationHead> lstConfigurationHead = new List <Entity.ConfigurationHead>();
SqlCommand cmdSelect = new SqlCommand();
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.CommandTimeout = 0;
cmdSelect.CommandText = "dbo.Get_ColumnNameFromQueryMaster";
cmdSelect.Parameters.AddWithValue("@QueryMasterId", QueryMasterId);
SqlDataReader dr = this.ExecuteDataReader(cmdSelect);
while (dr.Read())
{
Entity.ConfigurationHead objConfigurationHead = new Entity.ConfigurationHead();
lstConfigurationHead.Add(objConfigurationHead);
this.FillEntityHeadName_ID(dr, objConfigurationHead, 0);
}
base.ForceCloseConnection(cmdSelect);
return(lstConfigurationHead);
}
public virtual List <Entity.ConfigurationHead> GetDescriptionData(string Id)
{
List <Entity.ConfigurationHead> lstDescription = new List <Entity.ConfigurationHead>();
SqlCommand cmdSelect = new SqlCommand();
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.CommandTimeout = 0;
cmdSelect.CommandText = "dbo.GetDescriptionData";
cmdSelect.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = this.ExecuteDataReader(cmdSelect);
while (dr.Read())
{
Entity.ConfigurationHead objConfigurationHead = new Entity.ConfigurationHead();
lstDescription.Add(objConfigurationHead);
this.FillDescriptionData(dr, objConfigurationHead, 0);
}
base.ForceCloseConnection(cmdSelect);
return(lstDescription);
}
protected void btnSave_Click(object sender, EventArgs e)
{
string ConditionString = "";
if (Page.IsValid)
{
Entity.ConfigurationHead objConfigurationHead = new Entity.ConfigurationHead();
objConfigurationHead.MinInputValue = "0";
objConfigurationHead.MaxInputValue = "0";
string IDs = "";
string Name = "";
//DataTable dtconditiondata = new DataTable();
//dtconditiondata.Columns.Add("ColumnName");
//dtconditiondata.Columns.Add("Condition");
//dtconditiondata.Columns.Add("ConditionValue");
foreach (ListViewItem item in lstvQualifyingHeadMaster.Items)
{
CheckBox cb = (CheckBox)item.FindControl("Chk_select");
DropDownList ddlcondition = (DropDownList)item.FindControl("ddlConditon");
Label lblcname = (Label)item.FindControl("lblColumnName");
if (cb.Checked)
{
HiddenField hf = (HiddenField)item.FindControl("hfDKId");
IDs += hf.Value + ",";
HiddenField hf2 = (HiddenField)item.FindControl("hfDKName");
Name += hf2.Value.Replace(" ", "") + ",";
//DataRow dr = dtconditiondata.NewRow();
//dr["ColumnName"] = hf2.Value;
//dr["Condition"] = ddlcondition.SelectedValue.ToString();
TextBox txtCondiValue = (TextBox)item.FindControl("txtConditionValue");
DropDownList ddlOperator = (DropDownList)item.FindControl("ddlOperator");
//dr["ConditionValue"] = txtCondiValue.Text;
//dtconditiondata.Rows.Add(dr);
if (ddlcondition.SelectedIndex > 0 && txtCondiValue.Text == "")
{
msg.GetMsg(this, "Please select Condition Value for column " + lblcname.Text, "");
return;
}
if (txtCondiValue.Text != string.Empty)
{
if (ddlcondition.SelectedValue != "0")
{
if (ddlcondition.SelectedValue == "Like")
{
ConditionString = ConditionString + "[" + hf2.Value.Replace(" ", "") + "] " + ddlcondition.SelectedValue.ToString() + "'%" + txtCondiValue.Text + "%' ";
}
else
{
ConditionString = ConditionString + "[" + hf2.Value.Replace(" ", "") + "] " + ddlcondition.SelectedValue.ToString() + " '" + txtCondiValue.Text + "' ";
}
ConditionString = ConditionString + ddlOperator.SelectedValue;
}
}
}
else
{
if (ddlcondition.SelectedIndex > 0)
{
msg.GetMsg(this, "Please select Checkbox for column " + lblcname.Text, "");
return;
}
}
}
if (ConditionString == string.Empty)
{
ConditionString = string.Empty;
}
else
{
ConditionString = " Where " + ConditionString;
ConditionString = ConditionString.Remove(ConditionString.Length - 4);
}
if (IDs == "")
{
msg.GetMsg(this.Page, "AND", "LE");
return;
}
//if (hfPKEdit.Value != "0")
//{
// if (!(IsUpdate && Event.Find(x => x.EventName.Contains("Update")) != null))
// {
// msg.GetMsg(this.Page, "QualifyingHeadMaster", "UR");
// return;
// }
// objConfigurationHead.ConfigurationHeadId = hfPKEdit.Value.ToInt32();
// objConfigurationHead.EventId = Event.Find(x => x.EventName.Contains("Update")).EventId;
//}
//else
//{
if (!(IsInsert && Event.Find(x => x.EventName.Contains("Insert")) != null))
{
msg.GetMsg(this.Page, "NewQueryGeneratedForm", "SR");
return;
}
//objConfigurationHead.ConfigurationHeadId = 0;
//objConfigurationHead.EventId = Event.Find(x => x.EventName.Contains("Insert")).EventId;
objConfigurationHead.QueryName = txtQueryName.Text;
objConfigurationHead.FormName = ddlFormname.SelectedValue;
objConfigurationHead.ColumnsId = IDs;
objConfigurationHead.ColumnsName = Name;
objConfigurationHead.HostName = "";
objConfigurationHead.IpAddr = "";
objConfigurationHead.ConditionString = ConditionString;
//objConfigurationHead.UserId = 0;
int check = BAL.ConfigurationHeadMastermgmt.AddUpdateHeadName(objConfigurationHead);
if (check > 0)
{
msg.GetMsg(this.Page, "NewQueryGeneratedForm", "I");
Clear();
}
else if (check == -2)
{
msg.GetMsg(this.Page, "NewQueryGeneratedForm", "EE");
}
else if (check == -1)
{
msg.GetMsg(this.Page, "Table Does Not Exits.", "");
}
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string Querystr = string.Empty;
string Groupby = string.Empty;
string FinalQuery = string.Empty;
string SelectedColumn = string.Empty;
string SelectedAggrigate = string.Empty;
string SelectedIsGroupby = string.Empty;
string strLeftJoin = string.Empty;
if (Page.IsValid)
{
if (dt.Rows.Count == 0)
{
dt.Columns.Add("ColumnName");
dt.Columns.Add("Aggrigate");
dt.Columns.Add("Alias");
dt.Columns.Add("Isgroupby");
}
int i = 0;
foreach (ListViewItem item in lstSummaryQueryBuilder.Items)
{
i = i + 1;
CheckBox cb = (CheckBox)item.FindControl("Chk_select");
Label lblcol = (Label)item.FindControl("lblColumnName");
Label lblinputtypid = (Label)item.FindControl("lblinputtypid");
DropDownList ddlaggri = (DropDownList)item.FindControl("ddlAggrigate");
TextBox txtAliyas = (TextBox)item.FindControl("txtAliyas");
CheckBox chkgroup = (CheckBox)item.FindControl("chkIsgroupby");
if (lblinputtypid.Text == "5" || lblinputtypid.Text == "14" || lblinputtypid.Text == "15")
{
strLeftJoin = strLeftJoin + " LEFT JOIN dbo.ParameterMaster pm" + i + " ON t.[" + lblcol.Text + "] = pm" + i + ".ParameterId";
}
if (cb.Checked)
{
//AddColumn in Table
DataRow dr = dt.NewRow();
dr["ColumnName"] = lblcol.Text;
if (ddlaggri.SelectedIndex > 0)
{
dr["Aggrigate"] = ddlaggri.SelectedItem.ToString();
}
else
{
dr["Aggrigate"] = "";
}
dr["Alias"] = txtAliyas.Text;
dr["Isgroupby"] = chkgroup.Checked;
dt.Rows.Add(dr);
SelectedColumn = "pm" + i + ".ParameterName ";
SelectedAggrigate = SelectedAggrigate + ',' + ddlaggri.SelectedItem.ToString();
SelectedIsGroupby = SelectedIsGroupby + ',' + chkgroup.Checked;
string CoumnName = lblcol.Text;
string AggrigateFunName = ddlaggri.SelectedItem.ToString();
string aliyas = txtAliyas.Text;
Boolean IsGroupBy = chkgroup.Checked;
//adding column with aggrigate function
if (ddlaggri.SelectedIndex.ToString() != "0")
{
if (Querystr == string.Empty)
{
if (AggrigateFunName == "Count")
{
Querystr = AggrigateFunName + "(seqid) as " + aliyas;
}
else if (AggrigateFunName == "SUM")
{
Querystr = AggrigateFunName + "( cast([" + CoumnName + "] as decimal(18,3))) as " + aliyas;
}
else
{
Querystr = AggrigateFunName + "([" + CoumnName + "]) as " + aliyas;
}
}
else
{
if (AggrigateFunName == "Count")
{
Querystr = Querystr + " , " + AggrigateFunName + "([seqid]) as " + aliyas;
}
else if (AggrigateFunName == "SUM")
{
Querystr = Querystr + " , " + AggrigateFunName + "( cast([" + CoumnName + "] as decimal(18,3))) as " + aliyas;
}
else
{
Querystr = Querystr + " , " + AggrigateFunName + "([" + CoumnName + "]) as " + aliyas;
}
}
}
//adding column only
else
{
if (lblinputtypid.Text == "5" || lblinputtypid.Text == "14" || lblinputtypid.Text == "15")
{
if (Querystr == string.Empty)
{
Querystr = Querystr + SelectedColumn + "as " + CoumnName.Replace(" ", "") + " ";
}
else
{
Querystr = Querystr + " , " + SelectedColumn + "as " + CoumnName.Replace(" ", "") + " ";
}
}
else
{
if (Querystr == string.Empty)
{
Querystr = Querystr + "[" + CoumnName + "]";
}
else
{
Querystr = Querystr + " , " + "[" + CoumnName + "]";
}
}
//for ggroupby to add coloumn in var
if (chkgroup.Checked == true)
{
if (lblinputtypid.Text == "5" || lblinputtypid.Text == "14" || lblinputtypid.Text == "15")
{
if (Groupby == string.Empty)
{
Groupby = SelectedColumn;
}
else
{
Groupby = Groupby + " ," + SelectedColumn;
}
}
else
{
if (Groupby == string.Empty)
{
Groupby = "[" + CoumnName + "]";;
}
else
{
Groupby = Groupby + " ," + "[" + CoumnName + "]";
}
}
}
}
}
}
if (Groupby != string.Empty)
{
Groupby = " Group by " + Groupby;
}
FinalQuery = "Select " + Querystr + " from " + ddlFormname.SelectedItem.ToString() + " t " + strLeftJoin + " Where IsDeleted=0 " + Groupby;
}
Entity.ConfigurationHead objConfigurationHead = new Entity.ConfigurationHead();
objConfigurationHead.TableName = ddlColumn.SelectedValue.ToString();
objConfigurationHead.QueryMasterId = ddlColumn.SelectedValue.ToString();
objConfigurationHead.dtQuerydata = dt;
objConfigurationHead.QueryName = FinalQuery;
objConfigurationHead.QueryDisplayText = txtQueryName.Text.Trim();
int check = BAL.ConfigurationHeadMastermgmt.Insert_Summary_Query(objConfigurationHead);
if (check > 0)
{
msg.GetMsg(this.Page, "Record Inserted Successfully.", "");
clear();
}
}
protected void FillEntityHeadName_ID(IDataReader dr, Entity.ConfigurationHead ObjConfigurationHead, int Type)
{
ObjConfigurationHead.ConfigurationHeadName = GetField(dr, "ConfigurationHeadName");
ObjConfigurationHead.ConfigurationHeadId = GetFieldInt64(dr, "ConfigurationHeadId");
ObjConfigurationHead.InputTypeId = GetFieldInt(dr, "InputTypeId");
}
protected void FillDescriptionData(IDataReader dr, Entity.ConfigurationHead ObjConfigurationHead, int Type)
{
ObjConfigurationHead.Description = GetField(dr, "Description");
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
Entity.ConfigurationHead objConfigurationHead = new Entity.ConfigurationHead();
objConfigurationHead.MinInputValue = "0";
objConfigurationHead.MaxInputValue = "0";
if (hfPKEdit.Value != "0")
{
if (!(IsUpdate && Event.Find(x => x.EventName.Contains("Update")) != null))
{
msg.GetMsg(this.Page, "QualifyingHeadMaster", "UR");
return;
}
objConfigurationHead.ConfigurationHeadId = hfPKEdit.Value.ToInt32();
objConfigurationHead.EventId = Event.Find(x => x.EventName.Contains("Update")).EventId;
}
else
{
if (!(IsInsert && Event.Find(x => x.EventName.Contains("Insert")) != null))
{
msg.GetMsg(this.Page, "QualifyingHeadMaster", "SR");
return;
}
objConfigurationHead.ConfigurationHeadId = 0;
objConfigurationHead.EventId = Event.Find(x => x.EventName.Contains("Insert")).EventId;
}
objConfigurationHead.ConfigurationHeadName = txtQualifyingHeadName.Text;
objConfigurationHead.HeadNote = txtHeadNoteforStudent.Text;
objConfigurationHead.InputTypeId = ddlInputType.SelectedValue.ToInt16();
objConfigurationHead.FormId = ddlFormname.SelectedValue.ToString().ToInt32();
objConfigurationHead.FormComponentMasterId = ddlFormComponent.SelectedValue.ToString().ToInt64();
objConfigurationHead.IsUniqueKey = chkUniqueKey.Checked;
objConfigurationHead.IsCompositeUniqueKey = chkCompositeUniqueKey.Checked;
if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 6)
{
if (txtMinInputValue.Text == string.Empty)
{
msg.GetMsg(this.Page, "Please Enter Minimum Input Value", "");
return;
}
else if (txtMaxInputValue.Text == string.Empty)
{
msg.GetMsg(this.Page, "Please Enter Maximum Input Value", "");
return;
}
}
if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 3 || Convert.ToInt64(ddlInputType.SelectedItem.Value) == 12)
{
objConfigurationHead.MinInputValue = txtMinInputValue.Text;
objConfigurationHead.MaxInputValue = txtMaxInputValue.Text;
}
else if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 15 || Convert.ToInt64(ddlInputType.SelectedItem.Value) == 5 || Convert.ToInt64(ddlInputType.SelectedItem.Value) == 14)
{
objConfigurationHead.TemplateDropdownMasterId = ddlTemplateDropdownMaster.SelectedValue.ToInt16();
}
else if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 2)
{
objConfigurationHead.MinInputValue = txtMinDate.Text;
objConfigurationHead.MaxInputValue = txtMaxDate.Text;
}
else if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 10)
{
objConfigurationHead.MaxLength = txtMaxLength.Text.ToInt32();
objConfigurationHead.RegexMasterId = ddlValidationType.SelectedValue.ToString().ToInt64();
}
if (Convert.ToInt64(ddlInputType.SelectedItem.Value) == 15)
{
objConfigurationHead.ChildDBId = ddlChildDropDownHead.Text.ToInt64();
}
objConfigurationHead.IsCompulsaryEntry = chkIsCompulsaryEntry.Checked;
objConfigurationHead.IsSearchableDropdown = chkSarchableDrp.Checked;
objConfigurationHead.Remark = txtRemark.Text;
objConfigurationHead.HostName = "";
objConfigurationHead.IpAddr = "";
objConfigurationHead.UserId = 0;
objConfigurationHead.DisplayPreferenceNo = txtQualifyingHeadDisplayPreferenceNo.Text.ToDecimal();
int check = BAL.ConfigurationHeadMastermgmt.AddUpdateConfigurationHead(objConfigurationHead);
if (check > 0)
{
if (hfPKEdit.Value != "0")
{
msg.GetMsg(this.Page, "ConfigurationForm", "U");
clrscr();
GetListData();
GetSetClass(false, true);
}
else
{
msg.GetMsg(this.Page, "ConfigurationForm", "I");
clrscr();
GetListData();
}
}
else if (check == -1)
{
msg.GetMsg(this.Page, "ConfigurationForm", "AE");
}
if (check == -3)
{
msg.GetMsg(this.Page, "This Form control's is freezed you can't Insert/Update Configuration ,In order to update configuration please unfreez form and then try again.", "");
}
else if (check == -2)
{
msg.GetMsg(this.Page, "Head Display Preference already exist. Make sure it should be unique in every component.", "");
}
else
{
msg.GetMsg(this.Page, "ConfigurationForm", "E");
}
}
}
public static int Insert_Summary_Query(Entity.ConfigurationHead Entity)
{
return(new DAL.ConfigurationHeadSQL().InsertSummary_Query_Data(Entity));
}
public static int AddUpdateHeadName(Entity.ConfigurationHead Entity)
{
return(new DAL.ConfigurationHeadSQL().AddUpdateHeadName(Entity));
}
