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;
}