private void btnKdas_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable table = ImportExcel2007.TranslateToTable(ofd.FileName); foreach (DataRow row in table.Rows) { string id = row.ItemArray[0].ToString().Trim(); string name = AdUtil.getUsernameByUserId(id, "kmas.local"); string dept = row.ItemArray[2].ToString().Trim(); string sect = row.ItemArray[3].ToString().Trim(); string div = row.ItemArray[4].ToString().Trim(); string query = string.Format("insert into TB_USER_AS (u_staffid, u_name, u_dept, u_sect, u_div) values ('{0}', N'{1}', '{2}', N'{3}', N'{4}')", id, name, dept, sect, div); DataServiceM.GetInstance().ExecuteNonQuery(query); } } }
private void tsbtnUpload_Click(object sender, EventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataTable tmptable = new DataTable(); tmptable = ImportExcel2007.TranslateToTable(ofd.FileName); foreach (DataRow row in tmptable.Rows) { string staffid = row.ItemArray[0].ToString(); staffid = staffid.ToLower(); string name = staffid.StartsWith("hk") ? AdUtil.getUsernameByUserId(staffid, "kmhk.local") : AdUtil.getUsernameByUserId(staffid, "kmas.local"); string company = staffid.StartsWith("hk") ? "KDTHK" : staffid.StartsWith("as") ? "KDAS" : "KDHK"; string query = string.Format("if exists (select * from TB_STAFF where st_staffid = '{0}')" + " update TB_STAFF set st_name = N'{1}', st_company = '{2}' where st_staffid = '{0}' else" + " insert into TB_STAFF (st_staffid, st_name, st_company) values ('{0}', N'{1}', '{2}')", staffid, name, company); DataServiceEducation.GetInstance().ExecuteNonQuery(query); } } MessageBox.Show("Record has been saved."); }
private string GetUsername(string staffid) { string id = staffid.ToLower(); string domain = id.StartsWith("hk") ? "kmhk.local" : id.StartsWith("cn") ? "kmcn.local" : "kmas.local"; return(AdUtil.getUsernameByUserId(staffid, domain)); }
private string StaffName(string staffId) { try { return(AdUtil.getUsernameByUserId(staffId.Trim(), "kmhk.local")); } catch { return(""); } }
private void GetIdleData(int months) { List <string> tableList = new List <string>(); string query = "select table_name from information_schema.tables where table_name like 'TB_hk%'"; using (IDataReader reader = DataService.GetInstance().ExecuteReader(query)) { while (reader.Read()) { tableList.Add(reader.GetString(0).Trim()); } } tableList = tableList.Distinct().ToList(); DataTable table = new DataTable(); string[] headers = { "lastaccess", "filename", "owner", "path", "vpath" }; foreach (string header in headers) { table.Columns.Add(header); } int range = 6 + months; int total = tableList.Count; int count = 0; foreach (string t in tableList) { string staffid = t.Substring(3); string staff = AdUtil.getUsernameByUserId(staffid, "kmhk.local"); string q1 = string.Format("select r_lastaccess, r_filename, r_owner, r_path, r_vpath from " + t + " where datediff(month, r_lastaccess, getdate()) >= {0} and r_owner = N'{1}'", range, staff); using (IDataReader reader = DataService.GetInstance().ExecuteReader(q1)) { while (reader.Read()) { table.Rows.Add(new object[] { reader.GetString(0).Trim(), reader.GetString(1).Trim(), reader.GetString(2).Trim(), reader.GetString(3).Trim(), reader.GetString(4).Trim() }); } } SetCountText(count++ + " / " + total); } foreach (DataTable dt in CloneTableList(table, 500000)) { Invoke((Action)(() => { ExportCsvUtil.ExportCsv(dt, "", ""); })); } }
private void LoadData() { List <string> tableList = new List <string>(); string query = "select table_name from information_schema.tables where table_name like 'TB_hk%'"; using (IDataReader reader = DataService.GetInstance().ExecuteReader(query)) { while (reader.Read()) { tableList.Add(reader.GetString(0).Trim()); } } tableList = tableList.Distinct().ToList(); DataTable table = new DataTable(); string[] headers = { "lastaccess", "filename", "owner", "path", "st", "disc" }; foreach (string header in headers) { table.Columns.Add(header); } table.Columns.Add("check", typeof(string)); table.Columns["check"].DefaultValue = "-"; int range = 9; List <string> idleFileList = new List <string>(); string q2 = "select di_path from TB_DISC_IDLE"; using (IDataReader reader = DataService.GetInstance().ExecuteReader(q2)) { while (reader.Read()) { idleFileList.Add(reader.GetString(0).Trim()); } } foreach (string t in tableList) { string staffid = t.Substring(3); string staff = AdUtil.getUsernameByUserId(staffid, "kmhk.local"); string q1 = string.Format("select r_lastaccess, r_filename, r_owner, r_path from " + t + " where datediff(month, r_lastaccess, getdate()) >= {0} and r_owner = N'{1}'", range, staff); using (IDataReader reader = DataService.GetInstance().ExecuteReader(q1)) { while (reader.Read()) { if (!idleFileList.Exists(x => x == reader.GetString(3).Trim())) { table.Rows.Add(new object[] { reader.GetString(0).Trim(), reader.GetString(1).Trim(), reader.GetString(2).Trim(), reader.GetString(3).Trim(), "-", "-" }); } } } } _table = table; dgvTotal.DataSource = table; }