private void IncomeReport()
{
int netValue = 0;
DataTable dtExpense = new DataTable();
DataTable dtRevenue = new DataTable();
DB_Operations db = new DB_Operations();
String queryExpense = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '50'";
String queryRevenue = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '40'";
dtExpense = db.SelectQry(queryExpense);
dtRevenue = db.SelectQry(queryRevenue);
GridView4.DataSource = dtRevenue;
GridView4.DataBind();
GridView5.DataSource = dtExpense;
GridView5.DataBind();
netValue = revenue - expense;
lblNetValue.Font.Bold = true;
lblNetIncome.Text = "$" + netValue.ToString();
lblNetIncome.Font.Bold = true;
}
private void BalanceSheet()
{
DataTable dtAsset = new DataTable();
DataTable dtLiability = new DataTable();
DataTable dtOwnership = new DataTable();
DB_Operations db = new DB_Operations();
String queryAsset = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '10'";
String queryLiability = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '20'";
String queryOwnership = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '30'";
dtAsset = db.SelectQry(queryAsset);
dtLiability = db.SelectQry(queryLiability);
dtOwnership = db.SelectQry(queryOwnership);
GridView1.DataSource = dtAsset;
GridView1.DataBind();
GridView2.DataSource = dtLiability;
GridView2.DataBind();
GridView3.DataSource = dtOwnership;
GridView3.DataBind();
lblTotal.Text += (liability + ownership).ToString();
lblAsset.Text += asset;
lblTotal.Font.Bold = true;
lblAsset.Font.Bold = true;
lbllo.Font.Bold = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
DB_Operations db = new DB_Operations();
DataTable dt = db.SelectQry("Select * from Applications");
string[] x = new string[dt.Rows.Count];
int[] y = new int[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
x[i] = dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][9]);
}
Chart1.Series[0].Points.DataBindXY(x, y);
//Chart1.Series[0].ChartType = SeriesChartType.Pie;
Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;
//OleDbCommand com = new OleDbCommand();
//com.Connection = c;
//com.CommandText = "Select * from Applications";
//OleDbDataReader r = com.ExecuteReader();
// while (r.Read())
// {
// Chart1.Series["Experience"].Points.AddXY(r["DocNum"].ToString(), r["Experience"].ToString());
// }
// c.Close();
//}
//catch (Exception ex)
//{
// MessageBox.Show("" + ex);
//}
}
public void RetrieveDetails(int EMP_ID)
{
string res = "select * from Timesheets where Emp_ID=" + EMP_ID + " and week_id=(select max(week_id) from timesheets where Emp_ID=" + EMP_ID + ")";
DataTable s = db.SelectQry(res);
Label7.Text = s.Rows[0].ItemArray[0].ToString();
Label8.Text = s.Rows[0].ItemArray[2].ToString();
Label9.Text = DateTime.Parse(s.Rows[0].ItemArray[6].ToString()).AddDays(7).ToString();
Label10.Text = DateTime.Parse(s.Rows[0].ItemArray[7].ToString()).AddDays(7).ToString();
Label11.Text = (Convert.ToInt32(s.Rows[0].ItemArray[4].ToString()) + 1).ToString();
Label12.Text = s.Rows[0].ItemArray[3].ToString();
}
//public static String _connectionString = @"Provider=SQLOLEDB;Data Source=mis-sql.uhcl.edu; Initial Catalog=Isam5635; User ID=Isam5635; Password=isam-5635#";
//public static String _connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\PACS.accdb";
//public OleDbConnection con = new OleDbConnection(_connectionString);
//public OleDbCommand cmd = new OleDbCommand();
//public OleDbDataReader dr;
//public DataTable dt = new DataTable();
//public DataSet ds = new DataSet();
/********************************** Access Connection Code Begins **************************************************************************************************************************************
\***************/
//public static OleDbConnection GetConnectionObject()
//{
// //String _connectionString = @"Provider=SQLOLEDB;Data Source=mis-sql.uhcl.edu; Initial Catalog=Isam5635; User ID=Isam5635; Password=isam-5635#";
// String _connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\PACS.accdb";
// // String _connectionString = @"Provider=SQLOLEDB;Data Source=ISAM5635.db.10408957.hostedresource.com; Initial Catalog=ISAM5635; User ID= ISAM5635; Password=UhCL123#";
// OleDbConnection conn = new OleDbConnection(_connectionString); //ConfigurationManager.AppSettings["ConnectionString"]);
// // conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AppData\Frontend.accdb";
// try
// {
// conn.Open();
// conn.Close();
// return conn;
// }
// catch (Exception ex)
// {
// //ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "ClientScript", "alert(ex.errorMessage)", true);
// }
// finally
// {
// if (conn.State == ConnectionState.Open) conn.Close();
// }
// return conn;
//}
public static DataTable GetTable(string _query)
{
//DataSet dset1 = new DataSet();
//DataTable dtable; // = new DataTable();
//OleDbConnection conn = GetConnectionObject();
//OleDbDataAdapter adapter1 = new OleDbDataAdapter(_query, conn);
DB_Operations db = new DB_Operations();
try
{
return(db.SelectQry(_query));
}
catch
{
//ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "ClientScript", "alert(ex.errorMessage)", true);
return(null);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
String sql = "Select TableName from BasicQueries";
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(sql);
if (ds.Rows.Count > 0)
{
dropDownTableName.DataSource = ds;
dropDownTableName.DataTextField = "TableName";
dropDownTableName.DataValueField = "TableName";
dropDownTableName.DataBind();
dropDownTableName.Items.Insert(0, new ListItem("EmployeeBenefits", "EmployeeBenefits"));
}
}
catch (Exception ex) { }
try
{
String qry = "Select DocType from DocumentTypes";
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
if (ds.Rows.Count > 0)
{
dropDownDocType.DataSource = ds;
dropDownDocType.DataTextField = "DocType";
dropDownDocType.DataValueField = "DocType";
dropDownDocType.DataBind();
dropDownDocType.Items.Insert(0, new ListItem("65", "65"));
}
}
catch (Exception ex)
{
}
}
}
public static List <string> GetList(string query, char delim)
{
List <string> list = new List <string>();
string strrow = string.Empty;
string strcolumn = string.Empty;
// OleDbConnection conn = GetConnectionObject();
DataTable dtable = new DataTable();
DB_Operations db = new DB_Operations();
try
{
//OleDbDataAdapter dr = new OleDbDataAdapter(query, conn);
//dr.Fill(dtable);
dtable = db.SelectQry(query);
foreach (DataColumn col in dtable.Columns)
{
strcolumn = strcolumn + col.ColumnName.ToString() + " " + delim;
}
list.Add(strcolumn + Environment.NewLine);
foreach (DataRow row in dtable.Rows)
{
strrow = row[0].ToString();
for (int i = 1; i < dtable.Columns.Count; i++)
{
strrow = strrow + " " + delim + row[i].ToString();
}
list.Add(strrow);
}
return(list);
}
catch (Exception e)
{
// MessageBox.Show(e.Message);
}
return(list);
}
protected void GetDocument_Click(object sender, EventArgs e)
{
Error.Visible = false;
GridView1.DataSource = null;
GridView1.DataBind();
try
{
txtQuery.Text = "select * from " + txtTabName.Text + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text);
List <string> _selDocStr = DataBaseUtility.GetList("select * from " + txtTabName.Text + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text));
string[] selDocStr = _selDocStr[1].Split(',');
txtExtRef.Text = selDocStr[2];
txtIntRef.Text = selDocStr[3];
txtProcessNum.Text = selDocStr[4];
txtStatus.Text = selDocStr[5];
txtDocStartDate.Text = selDocStr[6];
txtDocStatusDate.Text = selDocStr[7];
txtQuotedAmt.Text = selDocStr[8];
txtActualAmt.Text = selDocStr[9];
txtComment.Text = selDocStr[10];
/*code starts here*/
try
{
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry("select * from " + txtTabName.Text + "_Details" + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text));
//con.Open();
//cmd = new OleDbCommand("select * from " + txtTabName.Text + "_Details" + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text), con);
//dr = cmd.ExecuteReader();
GridView1.DataSource = ds;
GridView1.DataBind();
//Attribute to show the Plus Minus Button.
GridView1.HeaderRow.Cells[0].Attributes["data-class"] = "expand";
int column_Num = GridView1.Rows[0].Cells.Count;
if (column_Num >= 3)
{
for (int i = 2; i <= column_Num - 1; i++)
{
//Attribute to hide column in Phone.
GridView1.HeaderRow.Cells[i].Attributes["data-hide"] = "phone,tablet";
}
}
//Adds THEAD and TBODY to GridView.
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
// con.Close();
}
catch (Exception ex)
{
//Debug.WriteLine("********************************************************************");
//Debug.WriteLine("Exception Caught: {0}", ex.Message);
//Debug.WriteLine("Exception Caught: {0}", ex.StackTrace);
//Debug.WriteLine("********************************************************************");
}
}
catch (Exception ex)
{
Error.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
List <String> account = new List <String>();
List <String> amount = new List <String>();
List <String> debit = new List <String>();
DB_Operations db = new DB_Operations();
DataTable amountAll = new DataTable();
DataTable debits = new DataTable();
DataTable accounts = new DataTable();
DataTable dt = new DataTable();
dt.Columns.Add("Account");
dt.Columns.Add("Credits");
dt.Columns.Add("Debits");
string queryAccounts = "Select DocNum from AccountBalances";
string queryAmount = "Select ActualValue from AccountBalances";
string queryDebit = "Select Debit from AccountBalances";
db.SelectQry(queryAccounts);
amountAll = db.SelectQry(queryAmount);
debits = db.SelectQry(queryDebit);
accounts = db.SelectQry(queryAccounts);
foreach (DataRow r in accounts.Rows)
{
account.Add(r["DocNum"].ToString());
}
foreach (DataRow r in amountAll.Rows)
{
amount.Add(r["ActualValue"].ToString());
}
foreach (DataRow r in debits.Rows)
{
debit.Add(r["Debit"].ToString());
}
for (int i = 0; i < amount.Count; i++)
{
amount[i] = (Convert.ToInt32(amount[i]) * Convert.ToInt32(debit[i])).ToString();
}
for (int i = 0; i < amount.Count; i++)
{
if (Convert.ToInt32(amount[i]) < 0)
{
int money = -1 * Convert.ToInt32((amount[i]));
dt.Rows.Add(account[i], "", money.ToString());
}
else if (Convert.ToInt32(amount[i]) >= 0)
{
dt.Rows.Add(account[i], amount[i], "");
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
private void InternalTrialBalanceRevenue()
{
List <String> account = new List <String>();
List <String> amount = new List <String>();
List <String> debit = new List <String>();
DB_Operations db = new DB_Operations();
DataTable amountAll = new DataTable();
DataTable debits = new DataTable();
DataTable accounts = new DataTable();
DataTable dt = new DataTable();
dt.Columns.Add("Account");
dt.Columns.Add("Debits");
dt.Columns.Add("Credits");
string queryAccounts = "Select DocNum from AccountBalances where Type > 30";
string queryAmount = "Select ActualValue from AccountBalances where Type > 30";
string queryDebit = "Select Debit from AccountBalances where Type > 30";
db.SelectQry(queryAccounts);
amountAll = db.SelectQry(queryAmount);
debits = db.SelectQry(queryDebit);
accounts = db.SelectQry(queryAccounts);
foreach (DataRow r in accounts.Rows)
{
account.Add(r["DocNum"].ToString());
}
foreach (DataRow r in amountAll.Rows)
{
amount.Add(r["ActualValue"].ToString());
}
foreach (DataRow r in debits.Rows)
{
debit.Add(r["Debit"].ToString());
}
for (int i = 0; i < amount.Count; i++)
{
amount[i] = (Convert.ToInt32(amount[i]) * Convert.ToInt32(debit[i])).ToString();
}
for (int i = 0; i < amount.Count; i++)
{
if (Convert.ToInt32(amount[i]) < 0)
{
int money = -1 * Convert.ToInt32((amount[i]));
dt.Rows.Add(account[i], "", money.ToString());
revenueDebit += money;
}
else if (Convert.ToInt32(amount[i]) >= 0)
{
dt.Rows.Add(account[i], amount[i], "");
revenueCredit = Convert.ToInt32(amount[i]);
}
}
GridView8.DataSource = dt;
GridView8.DataBind();
}
protected void Button_Get_Click(object sender, EventArgs e)
{
string currentID = empId[empId.Count - 1];
int nextEmpID = Convert.ToInt32(currentID) + 1;
if (DropDownList1.SelectedIndex == 0)
{
Response.Write("Please select applicant id first");
}
else
{
string chosenApp = DropDownList1.SelectedItem.Text;
DB_Operations db = new DB_Operations();
_applicantsValues = db.SelectQry("select * from Applicant where DocNum = " + chosenApp);
string fname = _applicantsValues.Rows[0][2].ToString();;
string lname = _applicantsValues.Rows[0][3].ToString();
string add = _applicantsValues.Rows[0][4].ToString();
string add2 = _applicantsValues.Rows[0][5].ToString();
string city = _applicantsValues.Rows[0][6].ToString();
string state = _applicantsValues.Rows[0][7].ToString();
string zip = _applicantsValues.Rows[0][8].ToString();
string country = _applicantsValues.Rows[0][9].ToString();
string phone = _applicantsValues.Rows[0][10].ToString();
string phone2 = _applicantsValues.Rows[0][11].ToString();
string email = _applicantsValues.Rows[0][12].ToString();
string dep_id = txtDep.Text;
string role = txtRole.Text;
string ad_level = txtAdmin.Text;
string cmt = _applicantsValues.Rows[0][16].ToString();
////
txtDocNum.Text = nextEmpID.ToString();
txtDocType.Text = "3";
txtExtRef.Text = fname;
txtIntRef.Text = lname;
txtProcNum.Text = add;
txtStatus.Text = add2;
txtValue.Text = city;
txtValue2.Text = state;
txtNumValue1.Text = zip;
txtNumValue2.Text = country;
txtPhone1.Text = phone;
txtPhone2.Text = phone2;
txtEmail.Text = email;
txtDep.Text = dep_id;
txtRole.Text = role;
txtAdmin.Text = ad_level;
txtComments.Text = cmt;
StringBuilder dataString1 = new StringBuilder();
dataString1.Append(txtDocNum.Text + " , "); // entity number
dataString1.Append(txtDocType.Text + " , '");
dataString1.Append(txtExtRef.Text + "' , '"); // first name
dataString1.Append(txtIntRef.Text + "' , '");
dataString1.Append(txtProcNum.Text + "' , '");
dataString1.Append(txtStatus.Text + "' , '");
dataString1.Append(txtValue.Text + "' , '");
dataString1.Append(txtValue2.Text + "' , '");
dataString1.Append(txtNumValue1.Text + "' , '");
dataString1.Append(txtNumValue2.Text + "' , '");
dataString1.Append(txtPhone1.Text + "' , '");
dataString1.Append(txtPhone2.Text + "' , '");
dataString1.Append(txtEmail.Text + "' , '");
dataString1.Append(txtDep.Text + "' , '");
dataString1.Append(txtRole.Text + "' , '");
dataString1.Append(txtAdmin.Text + "' , '");
dataString1.Append(txtComments.Text + "'");
dataStringsList.Add(dataString1.ToString());
//TextBox_Details.Text = dataStringsList[0];
TextBox_Details.Text = dataString1.ToString();
}
}
protected void DisplayResults_Click(object sender, EventArgs e)
{
Error.Visible = false;
docGrid.DataSource = null;
docGrid.DataBind();
try
{
txtTableName.Text = dropDownTableName.SelectedItem.Text;
txtQuery.Text = " select * from " + dropDownTableName.SelectedItem.Text;
txtDocType.Text = dropDownDocType.SelectedItem.Text;
if (dropDownDocNumber.SelectedItem.Text == "0 - Any Num")
{
txtDocNum.Text = "0 - Any Num";
}
else
{
txtDocNum.Text = dropDownDocNumber.SelectedItem.Text;
}
// con.Open();
String qry = "";
if (dropDownDocType.SelectedItem.Text == "0" && dropDownDocNumber.SelectedItem.Text == "0 - Any Num")
{
qry = "select * from " + dropDownTableName.SelectedItem.Text;
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
docGrid.DataSource = ds;
docGrid.DataBind();
txtQuery.Text = " select * from " + dropDownTableName.SelectedItem.Text;
}
else if (dropDownDocType.SelectedItem.Text == "0" && dropDownDocNumber.SelectedItem.Text != "0 - Any Num")
{
qry = "select * from " + dropDownTableName.SelectedItem.Text + " where DocNum = " + Convert.ToInt32(txtDocNum.Text);
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
docGrid.DataSource = ds;
docGrid.DataBind();
txtQuery.Text = "select * from " + dropDownTableName.SelectedItem.Text + " where DocNum = " + txtDocType.Text;
}
else if (dropDownDocType.SelectedItem.Text != "0" && dropDownDocNumber.SelectedItem.Text != "0 - Any Num")
{
qry = "select * from " + dropDownTableName.SelectedItem.Text + " where DocType = " + dropDownDocType.SelectedItem.Text + " And DocNum = " + Convert.ToInt32(txtDocNum.Text);
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
docGrid.DataSource = ds;
docGrid.DataBind();
txtQuery.Text = "select * from " + dropDownTableName.SelectedItem.Text + " where DocType = " + dropDownDocType.SelectedItem.Text + " And DocNum = " + txtDocType.Text;
}
else if (dropDownDocType.SelectedItem.Text != "0" && dropDownDocNumber.SelectedItem.Text == "0 - Any Num")
{
qry = "select * from " + dropDownTableName.SelectedItem.Text + " where DocType = " + dropDownDocType.SelectedItem.Text;
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
docGrid.DataSource = ds;
docGrid.DataBind();
txtQuery.Text = "select * from " + dropDownTableName.SelectedItem.Text + " where DocType = " + dropDownDocType.SelectedItem.Text;
}
//Attribute to show the Plus Minus Button.
docGrid.HeaderRow.Cells[0].Attributes["data-class"] = "expand";
int column_Num = docGrid.Rows[0].Cells.Count;
if (column_Num >= 3)
{
for (int i = 2; i <= column_Num - 1; i++)
{
//Attribute to hide column in Phone.
docGrid.HeaderRow.Cells[i].Attributes["data-hide"] = "phone,tablet";
}
}
//Adds THEAD and TBODY to GridView.
docGrid.HeaderRow.TableSection = TableRowSection.TableHeader;
}
catch (Exception ex)
{
Error.Visible = true;
}
}