private void GetCheckCom()
{
baocom.Clear();
try
{
string pathfile = Application.StartupPath + @"\DLNS\DuLieuNhanSu.xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
oada.Fill(table);
MyConnection.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
CheckBaoCom ck = new CheckBaoCom()
{
manhansu = drow["maid"].ToString(),
hoten = drow["hoten"].ToString(),
phong = drow["phong"].ToString(),
ban = drow["ban"].ToString(),
congdoan = drow["congdoan"].ToString()
};
baocom.Add(ck);
}
}
}
catch (Exception)
{
}
}
private void button2_Click(object sender, EventArgs e)
{
//Insert
CheckBaoCom ck = new CheckBaoCom()
{
empid = "1434",
manhansu = "0001434",
hoten = "Đinh Thị OO",
phongid = null,
phong = "QC5",
banid = null,
ban = "CSP",
congdoanid = null,
congdoan = "---",
khach = "false",
ngay = DateTime.Now.ToString("yyyy-MM-dd"),
thang = 4,
nam = 2019,
//userid = "3C5AC74AC16E4C3DB8FE69CB2210A828",
thoigiandat = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
sudung = "false",
dangky = "false",
thoigiansudung = null,
soxuatandadung = 0,
sotiendadung = 0,
chot = "true",
ghichu = "test2",
buaanid = "275AD44F777F4C679B8F72B8F299A931",
nhaanid = "D982F0F304104EB3A9A903CCC23958C3",
dangkybosung = "true"
};
InsertCheckBaoCom(ck);
}
private async void UpdateCheckBaoCom(CheckBaoCom ck)
{
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(ck);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PutAsync(String.Format("{0}/{1}", APICheckBaoCom, ck.id), content);
}
}
private async void UpdateCheckBaoCom(CheckBaoCom ck)
{
string APIbaocom = "http://192.84.100.207/MealOrdersAPI/api/DulieuBaoComs";
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(ck);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PutAsync(String.Format("{0}/{1}", APIbaocom, ck.id), content);
}
}
private async Task <bool> InsertCheckBaoCom(CheckBaoCom ck)
{
bool check = false;
string APIbaocom = "http://192.84.100.207/MealOrdersAPI/api/DulieuBaoComs";
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(ck);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PostAsync(APIbaocom, content);
if (result.IsSuccessStatusCode)
{
check = true;
}
}
return(await Task.FromResult(check));
}
private void button1_Click(object sender, EventArgs e)
{
//Update
CheckBaoCom ck = new CheckBaoCom()
{
id = "00058EF5E55743078B9D66C6864A5DB6",
empid = null,
manhansu = "008809",
hoten = "Nguyễn Thị Minh Tuyết",
phongid = null,
phong = "MA1A",
banid = null,
ban = "Drilling",
congdoanid = null,
congdoan = null,
khach = "false",
ngay = DateTime.Now.ToString("2019-05-11"),
thang = 5,
nam = 2019,
userid = null,
thoigiandat = DateTime.Now.ToString("2019-05-11"),
sudung = "true",
dangky = "true",
thoigiansudung = DateTime.Now.ToString("2019-05-11 HH:mm:ss"),
soxuatandadung = 0,
sotiendadung = 0,
chot = "true",
ghichu = "update123",
// thucdontheobuaid = null,
// thucdontheobua = null,
// kieudoan = 0,
buaanid = "275AD44F777F4C679B8F72B8F299A931",
// buaan = "Trưa",
// ca = 0,
nhaanid = "D982F0F304104EB3A9A903CCC23958C3",
// nhaan = "Việt",
// loaidouong = 0,
// thanhtoan = 0,
// phongrieng = 0,
dangkybosung = "false"
};
UpdateCheckBaoCom(ck);
}
private async void UpdateCheckBaoCom(CheckBaoCom ck)
{
string APIbaocom = "http://192.84.100.207/MealOrdersAPI/api/DulieuBaoComs";
// string APIbaocom = "";
try
{
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(ck);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PutAsync(String.Format("{0}/{1}", APIbaocom, ck.id), content);
if (result.IsSuccessStatusCode)
{
try
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnectionup);
oada.Fill(table);
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung=" + ck.sudung + ",thoigiansudung='" + ck.thoigiansudung + "',soxuatandadung=" + ck.soxuatandadung + " where manhansu='" + ck.manhansu + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
catch (Exception)
{
MessageBox.Show("Update dữ liệu lỗi!");
}
}
else
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
DataTable table1 = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada1 = new OleDbDataAdapter("select * from [Sheet1$] where trangthai2='NG' and manhansu='" + ck.manhansu + "'", MyConnection);
oada1.Fill(table1);
MyConnection.Close();
if (table1.Rows.Count == 1)
{
try
{
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnectionup);
oada.Fill(table);
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung=" + ck.sudung + ",thoigiansudung='" + ck.thoigiansudung + "',soxuatandadung=" + ck.soxuatandadung + " where manhansu='" + ck.manhansu + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
catch (Exception)
{
MessageBox.Show("Update dữ liệu client lỗi!");
}
}
else
{
try
{
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnectionup);
oada.Fill(table);
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung=" + ck.sudung + ",thoigiansudung='" + ck.thoigiansudung + "',soxuatandadung=" + ck.soxuatandadung + ",trangthai1='NG' where manhansu='" + ck.manhansu + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
catch (Exception)
{
MessageBox.Show("Update dữ liệu client lỗi!");
}
}
}
}
}
catch (Exception)
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
DataTable table1 = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada1 = new OleDbDataAdapter("select * from [Sheet1$] where trangthai2='NG' and manhansu='" + ck.manhansu + "'", MyConnection);
oada1.Fill(table1);
MyConnection.Close();
if (table1.Rows.Count == 1)
{
try
{
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnectionup);
oada.Fill(table);
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung=" + ck.sudung + ",thoigiansudung='" + ck.thoigiansudung + "',soxuatandadung=" + ck.soxuatandadung + " where manhansu='" + ck.manhansu + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
catch (Exception)
{
MessageBox.Show("Update dữ liệu client lỗi!");
}
}
else
{
try
{
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnectionup);
oada.Fill(table);
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung=" + ck.sudung + ",thoigiansudung='" + ck.thoigiansudung + "',soxuatandadung=" + ck.soxuatandadung + ",trangthai1='NG' where manhansu='" + ck.manhansu + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
catch (Exception)
{
MessageBox.Show("Update dữ liệu client lỗi!");
}
}
}
}
private async void txtID_TextChanged(object sender, EventArgs e)
{
await Task.Delay(70);
if (CheckData() == true)
{
if (!string.IsNullOrEmpty(txtID.Text))
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$] where manhansu='" + txtID.Text + "'", MyConnection);
oada.Fill(table);
MyConnection.Close();
if (table.Rows.Count == 1)
{
CheckBaoCom ck = new CheckBaoCom()
{
id = table.Rows[0]["id"].ToString(),
empid = string.IsNullOrEmpty(table.Rows[0]["empid"].ToString()) ? null : table.Rows[0]["empid"].ToString(),
manhansu = table.Rows[0]["manhansu"].ToString(),
hoten = table.Rows[0]["hoten"].ToString(),
phongid = string.IsNullOrEmpty(table.Rows[0]["phongid"].ToString()) ? null : table.Rows[0]["phongid"].ToString(),
phong = string.IsNullOrEmpty(table.Rows[0]["phong"].ToString()) ? null : table.Rows[0]["phong"].ToString(),
banid = string.IsNullOrEmpty(table.Rows[0]["banid"].ToString()) ? null : table.Rows[0]["banid"].ToString(),
ban = string.IsNullOrEmpty(table.Rows[0]["ban"].ToString()) ? null : table.Rows[0]["ban"].ToString(),
congdoanid = string.IsNullOrEmpty(table.Rows[0]["congdoanid"].ToString()) ? null : table.Rows[0]["congdoanid"].ToString(),
congdoan = string.IsNullOrEmpty(table.Rows[0]["congdoan"].ToString()) ? null : table.Rows[0]["congdoanid"].ToString(),
khach = table.Rows[0]["khach"].ToString(),
ngay = Convert.ToDateTime(table.Rows[0]["ngay"].ToString()).ToString("yyyy-MM-dd"),
thang = int.Parse(table.Rows[0]["thang"].ToString()),
nam = int.Parse(table.Rows[0]["nam"].ToString()),
userid = string.IsNullOrEmpty(table.Rows[0]["userid"].ToString()) ? null : table.Rows[0]["userid"].ToString(),
thoigiandat = Convert.ToDateTime(table.Rows[0]["thoigiandat"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
sudung = table.Rows[0]["sudung"].ToString(),
dangky = table.Rows[0]["dangky"].ToString(),
sotiendadung = int.Parse(table.Rows[0]["sotiendadung"].ToString()),
chot = table.Rows[0]["chot"].ToString(),
buaanid = table.Rows[0]["buaanid"].ToString(),
nhaanid = table.Rows[0]["nhaanid"].ToString(),
dangkybosung = table.Rows[0]["dangkybosung"].ToString()
};
if (table.Rows[0]["sudung"].ToString() == "False")
{
lbthongtinnv.Text = table.Rows[0]["manhansu"].ToString() + "-" + table.Rows[0]["hoten"].ToString() + "-" + table.Rows[0]["phong"].ToString() + "-" + table.Rows[0]["ban"].ToString();
lbthongbao.Text = "OK";
checkok.Play();
checkok.Dispose();
lbthongbao.BackColor = Color.Green;
ck.sudung = "True";
ck.thoigiansudung = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
ck.soxuatandadung = Convert.ToInt32(table.Rows[0]["soxuatandadung"].ToString()) + 1;
UpdateCheckBaoCom(ck);
txtID.Text = null;
lbthoigiansudung.Text = "Thành công: " + DateTime.Now.ToString("dd/MM/yy-HH:mm:ss");
}
else
{
lbthongtinnv.Text = table.Rows[0]["manhansu"].ToString() + "-" + table.Rows[0]["hoten"].ToString() + "-" + table.Rows[0]["phong"].ToString() + "-" + table.Rows[0]["ban"].ToString();
lbthongbao.Text = "NG";
checkng.Play();
checkng.Dispose();
lbthongbao.BackColor = Color.Yellow;
lbthoigiansudung.Text = "Bạn đã lấy cơm lúc: " + table.Rows[0]["thoigiansudung"];
txtID.Text = null;
}
}
else
{
checkng.Play();
checkng.Dispose();
lbthongbao.Text = "NG";
lbthongbao.BackColor = Color.Red;
txtID.Text = null;
lbthongtinnv.Text = null;
lbthoigiansudung.Text = "Bạn chưa báo cơm. Vui lòng qua bàn đăng ký bổ sung!";
}
}
}
else
{
txtID.Text = null;
}
#region Check Failed
//=============================================================================================================
//List<CheckBaoCom> check = baocom.Where(x => x.manhansu == txtID.Text).ToList();
//if (!string.IsNullOrEmpty(txtID.Text))
//{
// if (check.Count == 1)
// {
// CheckBaoCom ck = new CheckBaoCom()
// {
// id = check.First().id,
// empid = string.IsNullOrEmpty(check.First().empid) ? null : check.First().empid,
// manhansu = check.First().manhansu,
// hoten = check.First().hoten,
// phongid = string.IsNullOrEmpty(check.First().phongid) ? null : check.First().phongid,
// phong = string.IsNullOrEmpty(check.First().phong)?null: check.First().phong,
// banid = string.IsNullOrEmpty(check.First().banid) ? null : check.First().banid,
// ban = string.IsNullOrEmpty(check.First().ban)?null: check.First().ban,
// congdoanid = string.IsNullOrEmpty(check.First().congdoanid) ? null : check.First().congdoanid,
// congdoan = string.IsNullOrEmpty(check.First().congdoan)?null: check.First().congdoan,
// khach = check.First().khach,
// ngay = Convert.ToDateTime(check.First().ngay).ToString("yyyy-MM-dd"),
// thang = check.First().thang,
// nam = check.First().nam,
// userid=string.IsNullOrEmpty(check.First().userid)?null: check.First().userid,
// thoigiandat = Convert.ToDateTime(check.First().thoigiandat).ToString("yyyy-MM-dd HH:mm:ss"),
// sudung = check.First().sudung,
// dangky = check.First().dangky,
// sotiendadung = check.First().sotiendadung,
// chot = check.First().chot,
// // ghichu = check.First().ghichu,
// // thucdontheobuaid = check.First().thucdontheobuaid,
// // thucdontheobua = check.First().thucdontheobua,
// buaanid = check.First().buaanid,
// // buaan = check.First().buaan,
// // kieudoan = check.First().kieudoan,
// nhaanid = check.First().nhaanid,
// dangkybosung = check.First().dangkybosung,
// };
// if (check.First().sudung.ToLower() == "false")
// {
// lbthongtinnv.Text = check.First().manhansu + "-" + check.First().hoten + "-" + check.First().phong + "-" + check.First().ban;
// lbthongbao.Text = "OK";
// checkok.Play();
// checkok.Dispose();
// lbthongbao.BackColor = Color.Green;
// ck.sudung = "true";
// ck.thoigiansudung = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// ck.soxuatandadung = check.First().soxuatandadung + 1;
// UpdateCheckBaoCom(ck);
// txtID.Text = null;
// lbthoigiansudung.Text = "Thành công: " + DateTime.Now.ToString("dd/MM/yy-HH:mm:ss");
// GetBaoCom();
// }
// else
// {
// lbthongtinnv.Text = check.First().manhansu + "-" + check.First().hoten + "-" + check.First().phong + "-" + check.First().ban;
// lbthongbao.Text = "NG";
// checkng.Play();
// checkng.Dispose();
// lbthongbao.BackColor = Color.Yellow;
// lbthoigiansudung.Text = "Bạn đã lấy cơm lúc: " + check.First().thoigiansudung;
// txtID.Text = null;
// }
// }
// else
// {
// checkng.Play();
// checkng.Dispose();
// lbthongbao.Text = "NG";
// lbthongbao.BackColor = Color.Red;
// txtID.Text = null;
// lbthongtinnv.Text = null;
// lbthoigiansudung.Text = "Bạn chưa báo cơm. Vui lòng qua bàn đăng ký bổ sung!";
// }
//}
#endregion
}
private void btnDongBo_Click(object sender, EventArgs e)
{
if (baocom.Count >= 1)
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$] where trangthai1='NG'", MyConnection);
oada.Fill(table);
myCommandup.Connection = MyConnection;
foreach (CheckBaoCom checkcom in baocom)
{
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
bool check1 = false;
if (drow["trangthai1"].ToString() == "NG")
{
if (checkcom.manhansu == drow["manhansu"].ToString())
{
CheckBaoCom ck = new CheckBaoCom()
{
id = drow["id"].ToString(),
empid = string.IsNullOrEmpty(drow["empid"].ToString())?null: drow["empid"].ToString(),
manhansu = drow["manhansu"].ToString(),
hoten = drow["hoten"].ToString(),
phongid = string.IsNullOrEmpty(drow["phongid"].ToString())?null: drow["phongid"].ToString(),
phong = string.IsNullOrEmpty(drow["phong"].ToString())?null: drow["phong"].ToString(),
banid = string.IsNullOrEmpty(drow["banid"].ToString())?null: drow["banid"].ToString(),
ban = string.IsNullOrEmpty(drow["ban"].ToString())?null: drow["ban"].ToString(),
congdoanid = string.IsNullOrEmpty(drow["congdoanid"].ToString())?null : drow["banid"].ToString(),
congdoan = string.IsNullOrEmpty(drow["congdoan"].ToString())?null: drow["congdoan"].ToString(),
khach = drow["khach"].ToString(),
ngay = string.IsNullOrEmpty(drow["ngay"].ToString()) ? null : Convert.ToDateTime(drow["ngay"].ToString()).ToString("yyyy-MM-dd"),
thang = int.Parse(drow["thang"].ToString()),
nam = int.Parse(drow["nam"].ToString()),
userid = string.IsNullOrEmpty(drow["userid"].ToString())?null: drow["userid"].ToString(),
thoigiandat = string.IsNullOrEmpty(drow["thoigiandat"].ToString()) ? null : Convert.ToDateTime(drow["thoigiandat"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
sudung = drow["sudung"].ToString(),
dangky = drow["dangky"].ToString(),
thoigiansudung = string.IsNullOrEmpty(drow["thoigiansudung"].ToString())?null: Convert.ToDateTime(drow["thoigiansudung"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
soxuatandadung = int.Parse(drow["soxuatandadung"].ToString()),
sotiendadung = 0,
chot = drow["chot"].ToString(),
ghichu = string.IsNullOrEmpty(drow["ghichu"].ToString())?null: drow["ghichu"].ToString(),
//thucdontheobuaid = "",
buaanid = drow["buaanid"].ToString(),
// kieudoan = 0,
nhaanid = idnhaan,
dangkybosung = drow["dangkybosung"].ToString()
};
check1 = Task.Run(() => UpdateCheckBaoCom(ck)).Result;
}
}
// Update data NG->""
if (check1 == true)
{
sqlup = "update [Sheet1$] set trangthai1='' where manhansu='" + drow["manhansu"].ToString() + "'";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
}
}
}
}
MyConnection.Close();
GetBaoCom();
GetDataClientChuaUpdateServer();
}
}
private void btnCapNhap_Click(object sender, EventArgs e)
{
string pathfile = Application.StartupPath + @"\CheckCom\" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + caan + ".xls";
if (lvDongbo.Items.Count == 0)
{
if (baocom.Count > 0)
{
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
//System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
//string sqlup = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$] where trangthai2='NG'", MyConnection);
oada.Fill(table);
//myCommandup.Connection = MyConnection;
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
bool check1 = false;
if (drow.RowState != DataRowState.Deleted)
{
CheckBaoCom ck = new CheckBaoCom()
{
empid = drow["empid"].ToString(),
manhansu = drow["manhansu"].ToString(),
hoten = drow["hoten"].ToString(),
phongid = string.IsNullOrEmpty(drow["phongid"].ToString()) ? null : drow["phongid"].ToString(),
phong = string.IsNullOrEmpty(drow["phong"].ToString()) ? null : drow["phong"].ToString(),
banid = string.IsNullOrEmpty(drow["banid"].ToString()) ? null : drow["banid"].ToString(),
ban = string.IsNullOrEmpty(drow["ban"].ToString()) ? null : drow["ban"].ToString(),
congdoanid = string.IsNullOrEmpty(drow["congdoanid"].ToString()) ? null : drow["congdoanid"].ToString(),
congdoan = string.IsNullOrEmpty(drow["congdoan"].ToString()) ? null : drow["congdoan"].ToString(),
khach = drow["khach"].ToString(),
ngay = string.IsNullOrEmpty(drow["ngay"].ToString()) ? null : Convert.ToDateTime(drow["ngay"].ToString()).ToString("yyyy-MM-dd"),
thang = int.Parse(drow["thang"].ToString()),
nam = int.Parse(drow["nam"].ToString()),
thoigiandat = string.IsNullOrEmpty(drow["thoigiandat"].ToString()) ? null : Convert.ToDateTime(drow["thoigiandat"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
sudung = drow["sudung"].ToString(),
dangky = drow["dangky"].ToString(),
thoigiansudung = string.IsNullOrEmpty(drow["thoigiansudung"].ToString()) ? null : Convert.ToDateTime(drow["thoigiansudung"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"),
soxuatandadung = 0,
sotiendadung = 0,
chot = drow["chot"].ToString(),
ghichu = drow["ghichu"].ToString(),
buaanid = drow["buaanid"].ToString(),
nhaanid = idnhaan,
dangkybosung = drow["dangkybosung"].ToString()
};
check1 = Task.Run(() => InsertCheckBaoCom(ck)).Result;
}
//if (check1 == true)
//{
// sqlup = "update [Sheet1$] set trangthai1='', trangthai2='' where manhansu='" + drow["manhansu"].ToString() + "'";
// myCommandup.CommandText = sqlup;
// myCommandup.ExecuteNonQuery();
//}
}
MyConnection.Close();
//GetBaoCom();
Task <string> callTask = Task.Run(() => GetAllCheckBaoCom());
callTask.Wait();
string astr = callTask.Result;
DataTable dt = (DataTable)JsonConvert.DeserializeObject(astr, typeof(DataTable));
Excel._Application docExcel = new Microsoft.Office.Interop.Excel.Application {
Visible = false
};
dynamic workbooksExcel = docExcel.Workbooks.Open(pathfile);
var worksheetExcel = (Excel._Worksheet)workbooksExcel.ActiveSheet;
var data = new object[dt.Rows.Count, dt.Columns.Count];
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
data[row, column] = dt.Rows[row][column].ToString();
}
}
var startCell = (Excel.Range)worksheetExcel.Cells[2, 1];
var endCell = (Excel.Range)worksheetExcel.Cells[dt.Rows.Count + 1, dt.Columns.Count];
//var endCell1 = (Excel.Range)worksheetExcel.Cells[dt.Rows.Count + 50, dt.Columns.Count + 1];
//worksheetExcel.Range[startCell, endCell1].Clear();
var writeRange = worksheetExcel.Range[startCell, endCell];
worksheetExcel.Columns[3].NumberFormat = "@";
worksheetExcel.Columns[19].NumberFormat = "@";
writeRange.Value2 = data;
workbooksExcel.Save();
workbooksExcel.Close();
docExcel.Application.Quit();
GetBaoCom();
kiemtratrangthai();
}
}
else
{
MessageBox.Show("Chưa đồng bộ dữ liệu!");
}
}
private void btnDongBo_Click_1(object sender, EventArgs e)
{
APICheckBaoCom = "http://192.84.100.207/MealOrdersAPI/api/BaoComBuaAn";
try
{
// get name data local
string filePath = Application.StartupPath + @"\FileName\checkcom.txt";
StreamReader objReader = new StreamReader(filePath);
string nameBaoCom = objReader.ReadLine();
objReader.Close();
string newstring = nameBaoCom.Substring(0, 6);
string pathfile = Application.StartupPath + @"\CheckCom\" + nameBaoCom + "";
if (DateTime.Now.ToString("yyMMdd") == newstring)
{
//get data server================================================================
List <CheckBaoCom> ck = new List <CheckBaoCom>();
Task <string> callTask = Task.Run(() => GetAllCheckBaoCom());
callTask.Wait();
string astr = callTask.Result;
List <CheckBaoCom> check = JsonConvert.DeserializeObject <List <CheckBaoCom> >(astr);
ck = check.Where(x => x.sudung == "false").ToList();
//get data local=================================================================
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
oada.Fill(table);
MyConnection.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
if (drow["sudung"].ToString() == "True")
{
foreach (CheckBaoCom result in ck)
{
if (int.Parse(drow["id"].ToString()) == result.id)
{
//thực hiện update đồng bộ data server
CheckBaoCom baocom = new CheckBaoCom()
{
id = int.Parse(drow["id"].ToString()),
empid = int.Parse(drow["empid"].ToString()),
manhansu = drow["manhansu"].ToString(),
hoten = drow["hoten"].ToString(),
phongid = int.Parse(drow["phongid"].ToString()),
phong = drow["phong"].ToString(),
banid = drow["banid"].ToString(),
ban = drow["ban"].ToString(),
congdoanid = drow["congdoanid"].ToString(),
congdoan = drow["congdoan"].ToString(),
khach = drow["khach"].ToString(),
ngay = DateTime.Parse(drow["ngay"].ToString()),
thang = int.Parse(drow["thang"].ToString()),
nam = int.Parse(drow["nam"].ToString()),
taikhoandat = int.Parse(drow["taikhoandat"].ToString()),
thoigiandat = DateTime.Parse(drow["thoigiandat"].ToString()),
sudung = drow["sudung"].ToString(),
dangky = drow["dangky"].ToString(),
thoigiansudung = drow["thoigiansudung"].ToString(),
soxuatandadung = int.Parse(drow["soxuatandadung"].ToString()),
sotiendadung = int.Parse(drow["sotiendadung"].ToString()),
chot = drow["chot"].ToString(),
ghichu = drow["ghichu"].ToString(),
thucdontheobuaid = drow["thucdontheobuaid"].ToString(),
thucdontheobua = drow["thucdontheobua"].ToString(),
buaanid = int.Parse(drow["buaanid"].ToString()),
buaan = drow["buaan"].ToString(),
};
UpdateCheckBaoCom(baocom);
}
}
}
}
}
MessageBox.Show("Đồng bộ thành công !");
}
else
{
MessageBox.Show("Hãy đồng bộ dữ liệu data local mới nhất !");
}
}
catch (AggregateException c)
{
MessageBox.Show("Lỗi đường truyền!");
}
}
private void GetBaoCom()
{
if (comboBox1.Text == "Online")
{
try
{
Task <string> callTask = Task.Run(() => GetAllCheckBaoCom());
callTask.Wait();
string astr = callTask.Result;
baocom = JsonConvert.DeserializeObject <List <CheckBaoCom> >(astr);
}
catch (AggregateException e)
{
MessageBox.Show("Lỗi đường truyền!");
}
}
else
{
//get check com==================================================
baocom.Clear();
string filePath = Application.StartupPath + @"\FileName\checkcom.txt";
StreamReader objReader = new StreamReader(filePath);
string nameBaoCom = objReader.ReadLine();
objReader.Close();
//==================================================================
string pathfile = Application.StartupPath + @"\CheckCom\" + nameBaoCom + "";
DataTable table = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnection.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
oada.Fill(table);
MyConnection.Close();
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow drow = table.Rows[i];
if (drow.RowState != DataRowState.Deleted)
{
CheckBaoCom ck = new CheckBaoCom()
{
id = int.Parse(drow["id"].ToString()),
empid = int.Parse(drow["empid"].ToString()),
manhansu = drow["manhansu"].ToString(),
hoten = drow["hoten"].ToString(),
phongid = int.Parse(drow["phongid"].ToString()),
phong = drow["phong"].ToString(),
banid = drow["banid"].ToString(),
ban = drow["ban"].ToString(),
congdoanid = drow["congdoanid"].ToString(),
congdoan = drow["congdoan"].ToString(),
khach = drow["khach"].ToString(),
ngay = DateTime.Parse(drow["ngay"].ToString()),
thang = int.Parse(drow["thang"].ToString()),
nam = int.Parse(drow["nam"].ToString()),
taikhoandat = int.Parse(drow["taikhoandat"].ToString()),
thoigiandat = DateTime.Parse(drow["thoigiandat"].ToString()),
sudung = drow["sudung"].ToString(),
dangky = drow["dangky"].ToString(),
thoigiansudung = drow["thoigiansudung"].ToString(),
soxuatandadung = int.Parse(drow["soxuatandadung"].ToString()),
sotiendadung = int.Parse(drow["sotiendadung"].ToString()),
chot = drow["chot"].ToString(),
ghichu = drow["ghichu"].ToString(),
thucdontheobuaid = drow["thucdontheobuaid"].ToString(),
thucdontheobua = drow["thucdontheobua"].ToString(),
buaanid = int.Parse(drow["buaanid"].ToString()),
buaan = drow["buaan"].ToString(),
};
baocom.Add(ck);
}
}
}
}
private async void UpdateCheckBaoCom(CheckBaoCom ck)
{
string APIbaocom = "http://192.84.100.207/MealOrdersAPI/api/BaoComBuaAn";
if (comboBox1.Text == "Online")
{
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(ck);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PutAsync(String.Format("{0}/{1}", APIbaocom, ck.id), content);
}
// Update local ==============================================
string filePath = Application.StartupPath + @"\FileName\checkcom.txt";
StreamReader objReader = new StreamReader(filePath);
string nameBaoCom = objReader.ReadLine();
objReader.Close();
string pathfile = Application.StartupPath + @"\CheckCom\" + nameBaoCom + "";
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung="
+ ck.sudung + ",thoigiansudung='"
+ ck.thoigiansudung + "',soxuatandadung="
+ ck.soxuatandadung + " where id="
+ ck.id + "";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
else
{
// Update local ==============================================
string filePath = Application.StartupPath + @"\FileName\checkcom.txt";
StreamReader objReader = new StreamReader(filePath);
string nameBaoCom = objReader.ReadLine();
objReader.Close();
string pathfile = Application.StartupPath + @"\CheckCom\" + nameBaoCom + "";
System.Data.OleDb.OleDbConnection MyConnectionup;
System.Data.OleDb.OleDbCommand myCommandup = new System.Data.OleDb.OleDbCommand();
string sqlup = null;
MyConnectionup = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pathfile + "';Extended Properties=Excel 8.0;");
MyConnectionup.Open();
myCommandup.Connection = MyConnectionup;
sqlup = "update [Sheet1$] set sudung="
+ ck.sudung + ",thoigiansudung='"
+ ck.thoigiansudung + "',soxuatandadung="
+ ck.soxuatandadung + " where id="
+ ck.id + "";
myCommandup.CommandText = sqlup;
myCommandup.ExecuteNonQuery();
MyConnectionup.Close();
}
}
private async void txtID_TextChanged(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtID.Text))
{
if (!string.IsNullOrEmpty(cbBuaAn.Text))
{
if (comboBox1.Text == "Online")
{
// get name data local
await Task.Delay(70);
string filePath = Application.StartupPath + @"\FileName\checkcom.txt";
StreamReader objReader = new StreamReader(filePath);
string nameBaoCom = objReader.ReadLine();
objReader.Close();
string newstring = nameBaoCom.Substring(0, 6);
if (!string.IsNullOrEmpty(txtID.Text))
{
List <CheckBaoCom> check = baocom;
check = check.Where(x => x.manhansu == txtID.Text).ToList();
if (check.Count == 1)
{
CheckBaoCom ck = new CheckBaoCom()
{
id = check.First().id,
empid = check.First().empid,
manhansu = check.First().manhansu,
hoten = check.First().hoten,
phongid = check.First().phongid,
phong = check.First().phong,
banid = check.First().banid,
ban = check.First().ban,
congdoanid = check.First().congdoanid,
congdoan = check.First().congdoan,
khach = check.First().khach,
ngay = check.First().ngay,
thang = check.First().thang,
nam = check.First().nam,
taikhoandat = check.First().taikhoandat,
thoigiandat = check.First().thoigiandat,
sudung = check.First().sudung,
dangky = check.First().dangky,
thoigiansudung = check.First().thoigiansudung,
soxuatandadung = check.First().soxuatandadung,
sotiendadung = check.First().sotiendadung,
chot = check.First().chot,
ghichu = check.First().ghichu,
thucdontheobuaid = check.First().thucdontheobuaid,
thucdontheobua = check.First().thucdontheobua,
buaanid = check.First().buaanid,
buaan = check.First().buaan
};
if (check.First().dangky == "False")
{
SoundPlayer checkng = new SoundPlayer(Application.StartupPath + @"\sound\buzzer_x.wav");
lbThongTinNV.Text = check.First().manhansu.ToString() + "-" + check.First().hoten.ToString() + "-" + check.First().phong.ToString() + "-" + check.First().ban.ToString();
checkng.Play();
lbThongBao.Text = "NG";
lbThongBao.BackColor = Color.Red;
checkng.Dispose();
txtID.Text = null;
lbthoigiancheck.Text = null;
}
else
{
if (DateTime.Now.ToString("yyMMdd") == newstring)
{
if (check.First().sudung == "false")
{
SoundPlayer checkok = new SoundPlayer(Application.StartupPath + @"\sound\Beep_Once.wav");
lbThongTinNV.Text = check.First().manhansu.ToString() + "-" + check.First().hoten.ToString() + "-" + check.First().phong.ToString() + "-" + check.First().ban.ToString();
lbThongBao.Text = "OK";
checkok.Play();
lbThongBao.BackColor = Color.Green;
ck.sudung = "true";
ck.thoigiansudung = DateTime.Now.ToString();
ck.soxuatandadung = check.First().soxuatandadung + 1;
UpdateCheckBaoCom(ck);
checkok.Dispose();
txtID.Text = null;
lbthoigiancheck.Text = null;
}
else
{
SoundPlayer checkok = new SoundPlayer(Application.StartupPath + @"\sound\Beep_Once.wav");
lbThongTinNV.Text = check.First().manhansu.ToString() + "-" + check.First().hoten.ToString() + "-" + check.First().phong.ToString() + "-" + check.First().ban.ToString();
lbThongBao.Text = "OK";
checkok.Play();
lbThongBao.BackColor = Color.Green;
checkok.Dispose();
lbthoigiancheck.BackColor = Color.Red;
lbthoigiancheck.Text = "Thời gian sử dụng:" + check.First().thoigiansudung;
txtID.Text = null;
}
}
else
{
MessageBox.Show("Hãy tạo dữ liệu data local mới nhất !");
}
}
}
else
{
SoundPlayer checkng = new SoundPlayer(Application.StartupPath + @"\sound\buzzer_x.wav");
checkng.Play();
lbThongBao.Text = "NG";
lbThongBao.BackColor = Color.Red;
checkng.Dispose();
txtID.Text = null;
lbThongTinNV.Text = null;
lbthoigiancheck.Text = null;
lbthoigiancheck.BackColor = DefaultBackColor;
}
}
}
}
else
{
txtID.Text = null;
MessageBox.Show("Hãy Chọn Bữa Ăn!");
}
}
}
private async void UpdateCheckBaoCom()
{
// get ID thực đơn ===========================
List <ThucDon> td = new List <ThucDon>();
Task <string> callTaskthucdon = Task.Run(() => GetAllThucDon());
callTaskthucdon.Wait();
string astrthucdon = callTaskthucdon.Result;
List <ThucDon> thucdon = JsonConvert.DeserializeObject <List <ThucDon> >(astrthucdon);
td = thucdon.Where(t => t.tenthucdon == comboBox3.Text).ToList();
// get ID bữa ăn ===========================
List <BuaAn> ba = new List <BuaAn>();
Task <string> callTaskbuaan = Task.Run(() => GetAllbuaan());
callTaskbuaan.Wait();
string astrbuaan = callTaskbuaan.Result;
List <BuaAn> buaan = JsonConvert.DeserializeObject <List <BuaAn> >(astrbuaan);
ba = buaan.Where(t => t.tenbuaan == comboBox2.Text).ToList();
// get all checkbaocom ========================
List <CheckBaoCom> check = new List <CheckBaoCom>();
Task <string> callTaskbaocom = Task.Run(() => GetAllCheckBaoCom());
callTaskbaocom.Wait();
string astrbaocom = callTaskbaocom.Result;
List <CheckBaoCom> baocom = JsonConvert.DeserializeObject <List <CheckBaoCom> >(astrbaocom);
check = baocom.Where(c => c.manhansu == textBox1.Text).ToList();
if (check.Count > 0 && td.Count > 0 && ba.Count > 0)
{
CheckBaoCom baocomnew = new CheckBaoCom();
baocomnew.ID = check.First().ID;
baocomnew.empid = check.First().empid;
baocomnew.manhansu = check.First().manhansu;
baocomnew.hoten = check.First().hoten;
baocomnew.phongid = check.First().phongid;
baocomnew.banid = check.First().banid;
baocomnew.congdoanid = check.First().congdoanid;
baocomnew.khach = check.First().khach;
baocomnew.ngay = check.First().ngay;
baocomnew.thang = check.First().thang;
baocomnew.nam = check.First().nam;
baocomnew.taikhoandat = check.First().taikhoandat;
baocomnew.thoigiandat = check.First().thoigiandat;
baocomnew.sudung = 1;
baocomnew.thoigiansudung = DateTime.Now;
baocomnew.sosuatsudung = check.First().sosuatsudung + 1;
baocomnew.sotiendadung = check.First().sotiendadung;
baocomnew.chot = check.First().chot;
baocomnew.ghichu = check.First().ghichu;
baocomnew.thucdontheobuaanid = td.First().ID;
baocomnew.buaanid = ba.First().ID;
baocomnew.phong = check.First().phong;
baocomnew.ban = check.First().ban;
baocomnew.congdoan = check.First().congdoan;
baocomnew.dangky = check.First().dangky;
using (var client = new HttpClient())
{
var serializedProduct = JsonConvert.SerializeObject(baocomnew);
var content = new StringContent(serializedProduct, Encoding.UTF8, "application/json");
var result = await client.PutAsync(String.Format("{0}/{1}", APICheckBaoCom, baocomnew.ID), content);
}
}
}
