private void AddValuesToTablesData()
{
try
{
_objTwoColumnsTableData = new TwoColumnsTableData();
_objTwoColumnsTableData.CompanyID = Convert.ToInt32(Session["lastPositionNo"].ToString());
_objTwoColumnsTableData.BranchID = Convert.ToInt32(Session["branchID"].ToString());
_objTwoColumnsTableData.TableID = Convert.ToInt32(Session["tableID"].ToString());
_objTwoColumnsTableData.FieldOfName = txtFieldOfName.Text == string.Empty ? null : txtFieldOfName.Text;
_objTwoColumnsTableData.FieldDescription = txtFieldDescription.Text == string.Empty ? null : txtFieldDescription.Text;
_objTwoColumnsTableData.EntryUserName = Session["entryUserCode"].ToString();
_objTwoColumnsTableData.TableName = lblSelectedTableName.Text == string.Empty ? null : lblSelectedTableName.Text;
_objTwoColumnsTableDataController = new TwoColumnsTableDataController();
if (btnSave.Text == "Save")
{
_objTwoColumnsTableDataController.Save(_connectionString, _objTwoColumnsTableData);
}
else
{
_objTwoColumnsTableData.FieldOfID = Session["selectedIndex"].ToString();
_objTwoColumnsTableDataController.Update(_connectionString, _objTwoColumnsTableData);
}
}
catch (Exception msgException)
{
throw msgException;
}
}
private void LoadRecordDynamicDDL(DropDownList givenDDLID, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
string sqlString = @"SELECT [FieldOfID]
,[FieldOfName]
FROM [TwoColumnsTable]
WHERE DataUsed = 'A' AND [TableID] = " + objTwoColumnsTableData.TableID + "";
if (objTwoColumnsTableData.CompanyID != 0)
{
sqlString += " AND [CompanyID] = " + objTwoColumnsTableData.CompanyID + "";
}
if (objTwoColumnsTableData.BranchID != 0)
{
sqlString += " AND[BranchID] = " + objTwoColumnsTableData.BranchID + "";
}
sqlString += " ORDER BY [FieldOfName]";
ClsDropDownListController.LoadDropDownList(this.ConnectionString, sqlString, givenDDLID, "FieldOfName", "FieldOfID");
}
catch (Exception msgException)
{
throw msgException;
}
}
private void AddValuesOrganizationalChart(Button btnAction)
{
try
{
_objOrganizationalChartSetup = new OrganizationalChartSetup();
_objOrganizationalChartSetup.CompanyID = Convert.ToInt32(ddlCompanyChart.SelectedValue);
_objOrganizationalChartSetup.EntityTypeID = Convert.ToInt32(ddlElement.SelectedValue);
_objOrganizationalChartSetup.TableName = ddlElement.SelectedValue == "-1" ? null : ddlElement.SelectedItem.Text;
_objOrganizationalChartSetup.EntityName = txtTitle.Text == string.Empty ? null : txtTitle.Text;
_objOrganizationalChartSetup.ParentEntityID = Convert.ToInt32(lblParentElementValue.Text);
_objOrganizationalChartSetup.ShortName = txtShortName.Text == string.Empty ? null : txtShortName.Text;
_objOrganizationalChartSetup.DisplayName = txtDisplayName.Text == string.Empty ? null : txtDisplayName.Text;
_objOrganizationalChartSetup.GroupEmailAddress = txtEmail.Text == string.Empty ? null : txtEmail.Text;
EmployeeSetup objEmployeeSetup = new EmployeeSetup();
objEmployeeSetup.EmployeeID = txtHeadID.Text == string.Empty ? null : txtHeadID.Text;
TwoColumnsTableData objTwoColumnsTableData = new TwoColumnsTableData();
objTwoColumnsTableData.FieldOfID = ddlCategory.SelectedValue == "-1" ? null : ddlCategory.SelectedValue;
if (txtOpeningDate.Text != string.Empty)
{
_objOrganizationalChartSetup.EntityOpeningDate = Convert.ToDateTime(txtOpeningDate.Text);
}
_objOrganizationalChartSetup.AddressTag = CheckBoxAddress.Checked == true ? "Y" : "N";
if (_objOrganizationalChartSetup.AddressTag == "Y")
{
var contactNumberValue = txtContactNumber.Text == string.Empty ? null : txtContactNumber.Text;
if (contactNumberValue != null)
{
_objOrganizationalChartSetup.ContactAdreessNumber = Convert.ToInt32(contactNumberValue);
}
_objOrganizationalChartSetup.DisplayAddress = txtDisplayAddress.Text == string.Empty ? null : txtDisplayAddress.Text;
_objOrganizationalChartSetup.DivisionID = ddlDivision.SelectedValue == "-1" ? null : ddlDivision.SelectedValue;
_objOrganizationalChartSetup.DistrictID = ddlDistrict.SelectedValue == "-1" ? null : ddlDistrict.SelectedValue;
_objOrganizationalChartSetup.PostalCode = txtPostalCode.Text == string.Empty ? null : txtPostalCode.Text;
_objOrganizationalChartSetup.PhoneNo = txtPhoneNo.Text == string.Empty ? null : txtPhoneNo.Text;
_objOrganizationalChartSetup.Fax = txtFax.Text == string.Empty ? null : txtFax.Text;
}
_objOrganizationalChartSetupController = new OrganizationalChartSetupController();
if (btnAction == btnSaveChart)
{
_objOrganizationalChartSetupController.SaveChart(_objOrganizationalChartSetup, objEmployeeSetup, objTwoColumnsTableData);
}
else
{
_objOrganizationalChartSetupController.UpdateChart(_objOrganizationalChartSetup, objEmployeeSetup, objTwoColumnsTableData);
}
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void LoadEmployeeCategoryDDL(DropDownList ddlEmployeeCategory, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
ClsDropDownListController.LoadDropDownList(this.ConnectionString, SqlGetEmployeeCategory(objTwoColumnsTableData), ddlEmployeeCategory, "FieldOfName", "FieldOfID");
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void LoadCategory(TwoColumnsTableData objTwoColumnsTableData, DropDownList ddlCategory)
{
try
{
this.LoadRecordDynamicDDL(ddlCategory, objTwoColumnsTableData);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void LoadDesignationDDL(DropDownList givenDDL, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
ClsDropDownListController.LoadDropDownList(this.ConnectionString, SqlGetDesignation(objTwoColumnsTableData), givenDDL, "FieldOfName", "FieldOfID");
}
catch (Exception msgException)
{
throw msgException;
}
}
private string SqlGetEmployeeCategory(TwoColumnsTableData objTwoColumnsTableData)
{
try
{
string sqlString = null;
sqlString = "SELECT [FieldOfID],[FieldOfName] FROM [EmployeeCategory] WHERE [DataUsed] = 'A' AND [CompanyID] = " + objTwoColumnsTableData.CompanyID + " ORDER BY [FieldOfName]";
return(sqlString);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void LoadRoleType(DropDownList ddlRoleType)
{
try
{
TwoColumnsTableData objTwoColumnsTableData = new TwoColumnsTableData();
objTwoColumnsTableData.TableID = 25;
this.LoadRecordDynamicDDL(ddlRoleType, objTwoColumnsTableData);
}
catch (Exception msgException)
{
throw msgException;
}
}
public void Delete(string connectionString, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
string sqlString = "UPDATE TwoColumnsTable SET DataUsed = 'I' WHERE FieldOfID = '" + objTwoColumnsTableData.FieldOfID + "'";
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, sqlString);
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, this.SqlCreateView(objTwoColumnsTableData));
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void LoadCategory(OrganizationalChartSetup objOrganizationalChartSetup, DropDownList ddlCategory)
{
try
{
TwoColumnsTableDataController objTwoColumnsTableDataController = new TwoColumnsTableDataController();
TwoColumnsTableData objTwoColumnsTableData = new TwoColumnsTableData();
objTwoColumnsTableData.TableID = objOrganizationalChartSetup.EntityTypeID;
objTwoColumnsTableDataController.LoadCategory(objTwoColumnsTableData, ddlCategory);
}
catch (Exception msgException)
{
throw msgException;
}
}
private void LoadorgDesignation()
{
try
{
TwoColumnsTableDataController objTwoColumnsTableDataController = new TwoColumnsTableDataController();
TwoColumnsTableData objTwoColumnsTableData = new TwoColumnsTableData();
objTwoColumnsTableData.CompanyID = LoginUserInformation.CompanyID;
objTwoColumnsTableDataController.LoadDesignationDDL(ddlDesignationEmployee, objTwoColumnsTableData);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void GetEmployeeCategory(DropDownList ddlEmployeeCategory, EmployeeDetailsSetup objEmployeeDetailsSetup)
{
try
{
_objTwoColumnsTableData = new TwoColumnsTableData();
_objTwoColumnsTableData.CompanyID = objEmployeeDetailsSetup.CompanyID;
_objTwoColumnsTableDataController = new TwoColumnsTableDataController();
_objTwoColumnsTableDataController.LoadEmployeeCategoryDDL(ddlEmployeeCategory, _objTwoColumnsTableData);
}
catch (Exception msgException)
{
throw msgException;
}
}
private void LoadDesignation(DropDownList givenDDL)
{
try
{
_objTwoColumnsTableDataController = new TwoColumnsTableDataController();
_objTwoColumnsTableData = new TwoColumnsTableData();
_objTwoColumnsTableData.CompanyID = Convert.ToInt32(Session["lastPositionNo"].ToString());
//objTwoColumnsTableData.BranchID = Convert.ToInt32(Session["branchID"].ToString());
_objTwoColumnsTableDataController.LoadDesignationDDL(givenDDL, _objTwoColumnsTableData);
}
catch (Exception msgException)
{
throw msgException;
}
}
public DataTable GetRecord(string connectionString, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
DataTable dtRecord = null;
string sqlString = @"SELECT [FieldOfID]
,[FieldOfName]
,[FieldDescription]
FROM [TwoColumnsTable]
WHERE DataUsed = 'A' AND [TableID] = " + objTwoColumnsTableData.TableID + " AND [CompanyID] = " + objTwoColumnsTableData.CompanyID + " AND [BranchID] = " + objTwoColumnsTableData.BranchID + " ORDER BY [FieldOfName]";
dtRecord = clsDataManipulation.GetData(connectionString, sqlString);
return(dtRecord);
}
catch (Exception msgException)
{
throw msgException;
}
}
public void Update(string connectionString, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
var storedProcedureComandTest = "exec [ACT_TwoColumnsTable] " +
objTwoColumnsTableData.CompanyID + "," +
objTwoColumnsTableData.BranchID + "," +
objTwoColumnsTableData.TableID + ",'" +
objTwoColumnsTableData.FieldOfID + "','" +
objTwoColumnsTableData.FieldOfName + "','" +
objTwoColumnsTableData.FieldDescription + "','" +
objTwoColumnsTableData.EntryUserName + "'";
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, storedProcedureComandTest);
clsDataManipulation.StoredProcedureExecuteNonQuery(connectionString, this.SqlCreateView(objTwoColumnsTableData));
}
catch (Exception msgException)
{
throw msgException;
}
}
private static string SqlGetDesignation(TwoColumnsTableData objTwoColumnsTableData)
{
try
{
string sqlString = @"SELECT [FieldOfID]
,[FieldOfName]
FROM [orgDesignation]
WHERE DataUsed = 'A' AND [CompanyID] = " + objTwoColumnsTableData.CompanyID + "";
if (objTwoColumnsTableData.BranchID != 0)
{
sqlString += " AND [BranchID] = " + objTwoColumnsTableData.BranchID + "";
}
sqlString += " ORDER BY [FieldOfName]";
return(sqlString);
}
catch (Exception msgException)
{
throw msgException;
}
}
protected void grdTableData_RowCommand(object sender, GridViewCommandEventArgs e)
{
int selectedIndex = Convert.ToInt32(e.CommandArgument.ToString());
string lblFieldOfID = ((Label)grdTableData.Rows[selectedIndex].FindControl("lblFieldOfID")).Text;
if (e.CommandName.Equals("Select"))
{
try
{
string lblFieldOfName = ((Label)grdTableData.Rows[selectedIndex].FindControl("lblFieldOfName")).Text;
string lblFieldDescription = ((Label)grdTableData.Rows[selectedIndex].FindControl("lblFieldDescription")).Text;
txtFieldOfName.Text = lblFieldOfName;
txtFieldDescription.Text = lblFieldDescription;
btnSave.Text = "Update";
Session["selectedIndex"] = lblFieldOfID;
}
catch (Exception msgException)
{
clsTopMostMessageBox.Show(msgException.Message);
}
}
else if (e.CommandName.Equals("Delete"))
{
try
{
_objTwoColumnsTableData = new TwoColumnsTableData();
_objTwoColumnsTableData.FieldOfID = lblFieldOfID;
_objTwoColumnsTableData.CompanyID = LoginUserInformation.CompanyID;
_objTwoColumnsTableData.TableID = Convert.ToInt32(Session["tableID"].ToString());
_objTwoColumnsTableData.TableName = lblSelectedTableName.Text == string.Empty ? null : lblSelectedTableName.Text;
_objTwoColumnsTableDataController = new TwoColumnsTableDataController();
_objTwoColumnsTableDataController.Delete(_connectionString, _objTwoColumnsTableData);
ShowTwoColumnsTableData();
}
catch (Exception msgException)
{
clsTopMostMessageBox.Show(msgException.Message);
}
}
}
private void ShowTwoColumnsTableData()
{
try
{
_objTwoColumnsTableData = new TwoColumnsTableData();
_objTwoColumnsTableData.CompanyID = Convert.ToInt32(Session["lastPositionNo"].ToString());
_objTwoColumnsTableData.BranchID = Convert.ToInt32(Session["branchID"].ToString());
_objTwoColumnsTableData.TableID = Convert.ToInt32(Session["tableID"].ToString());
_objTwoColumnsTableDataController = new TwoColumnsTableDataController();
DataTable dtTableData = _objTwoColumnsTableDataController.GetRecord(_connectionString, _objTwoColumnsTableData);
grdTableData.DataSource = null;
grdTableData.DataBind();
if (dtTableData.Rows.Count > 0)
{
grdTableData.DataSource = dtTableData;
grdTableData.DataBind();
}
}
catch (Exception msgException)
{
throw msgException;
}
}
private string SqlCreateView(TwoColumnsTableData objTwoColumnsTableData)
{
try
{
string sqlForView = null;
sqlForView = @" if exists(Select * from sysobjects where name = '" + objTwoColumnsTableData.TableName.Replace(" ", String.Empty) + "' and type = 'V' ) begin drop view " + objTwoColumnsTableData.TableName.Replace(" ", String.Empty) + " end;";
sqlForView += " exec('create view " + objTwoColumnsTableData.TableName.Replace(" ", string.Empty) +
@" as SELECT CompanyID
,[FieldOfID]
,[FieldOfName]
,FieldDescription
,[DataUsed]
,[EntryUserID]
,[EntryDate]
,[LastUpdateDate]
,[LastUpdateUserID]
FROM [TwoColumnsTable] WHERE CompanyID = " + objTwoColumnsTableData.CompanyID + " AND [TableID] = " + objTwoColumnsTableData.TableID + "');";
return(sqlForView);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void SaveChart(OrganizationalChartSetup objOrganizationalChartSetup, EmployeeSetup objEmployeeSetup, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
if (objOrganizationalChartSetup.CompanyID == -1)
{
throw new Exception("company is required ");
}
objOrganizationalChartSetup.EntityID = Convert.ToInt32(objOrganizationalChartSetup.EntityTypeID.ToString() + this.GetEntityID(objOrganizationalChartSetup).ToString());
var storedProcedureComandText = @"INSERT INTO [orgOrganizationalChartSetup] ([CompanyID],[ParentEntityID],[EntityID],[EntityTypeID]
,[AddressTag],[AddressID],[EntityName],[ShortName],[DisplayName],[GroupEmailAddress],[HeadID]
,[EntityCategoryID],[EntityOpeningDate],[DataUsed],[EntryUserID],[EntryDate]) VALUES ( " +
objOrganizationalChartSetup.CompanyID + "," +
objOrganizationalChartSetup.ParentEntityID + "," +
objOrganizationalChartSetup.EntityID + "," +
objOrganizationalChartSetup.EntityTypeID + ",'" +
objOrganizationalChartSetup.AddressTag + "'," +
objOrganizationalChartSetup.EntityID + ",'" +
objOrganizationalChartSetup.EntityName + "','" +
objOrganizationalChartSetup.ShortName + "','" +
objOrganizationalChartSetup.DisplayName + "','" +
objOrganizationalChartSetup.GroupEmailAddress + "','" +
objEmployeeSetup.EmployeeID + "','" +
objTwoColumnsTableData.FieldOfID + "',CONVERT(DATETIME,'" +
objOrganizationalChartSetup.EntityOpeningDate + "', 103),'" +
"A" + "', '" +
"160ea939-7633-46a8-ae49-f661d12abfd5" + "'," +
"CAST(GETDATE() AS DateTime));";
if (objOrganizationalChartSetup.AddressTag == "Y")
{
storedProcedureComandText += SqlSaveAddress(objOrganizationalChartSetup);
}
storedProcedureComandText += this.SqlCreateView(objOrganizationalChartSetup);
clsDataManipulation.StoredProcedureExecuteNonQuery(this.ConnectionString, storedProcedureComandText);
}
catch (Exception msgException)
{
throw msgException;
}
}
internal void UpdateChart(OrganizationalChartSetup objOrganizationalChartSetup, EmployeeSetup objEmployeeSetup, TwoColumnsTableData objTwoColumnsTableData)
{
try
{
var storedProcedureComandText = @"UPDATE [orgOrganizationalChartSetup]
SET
[AddressTag] = '" + objOrganizationalChartSetup.AddressTag + "'," +
"[EntityName] = '" + objOrganizationalChartSetup.EntityName + "'," +
"[ShortName] = '" + objOrganizationalChartSetup.ShortName + "'," +
"[DisplayName] = '" + objOrganizationalChartSetup.DisplayName + "'," +
"[GroupEmailAddress] = '" + objOrganizationalChartSetup.GroupEmailAddress + "'," +
"[HeadID] = '" + objEmployeeSetup.EmployeeID + "'," +
"[EntityCategoryID] = '" + objTwoColumnsTableData.FieldOfID + "'," +
"[EntityOpeningDate] = CONVERT(DATETIME,'" + objOrganizationalChartSetup.EntityOpeningDate + "',103)" + "," +
"[LastUpdateDate] = CAST(GETDATE() AS DateTime) " +
",[LastUpdateUserID] = '160ea939-7633-46a8-ae49-f661d12abfd5'" +
" WHERE [EntityID] = " + objOrganizationalChartSetup.ParentEntityID + ";";
if (objOrganizationalChartSetup.AddressTag == "N")
{
storedProcedureComandText += SqlInactiveAddressData(objOrganizationalChartSetup);
}
else if (objOrganizationalChartSetup.AddressTag == "Y")
{
storedProcedureComandText += SqlUpdateAddress(objOrganizationalChartSetup);
}
storedProcedureComandText += this.SqlCreateView(objOrganizationalChartSetup);
clsDataManipulation.StoredProcedureExecuteNonQuery(this.ConnectionString, storedProcedureComandText);
}
catch (Exception msgException)
{
throw msgException;
}
}
