public void BindChuongThuoc()
{
if (ddlLoaiCa.SelectedValue == "0")
{
grvDanhSach.Visible = false;
}
else
{
grvDanhSach.Visible = true;
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT = 0;
DateTime NgayAn = DateTime.MinValue;
DataTable tblChuong;
if (ddlRowStatus.SelectedValue == "1")
{
tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(int.Parse(hdCaSauAn.Value), int.Parse(ddlThuoc.SelectedValue), int.Parse(ddlLoaiCa.SelectedValue), out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
}
else
{
tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa_Delete(int.Parse(hdCaSauAn.Value), int.Parse(ddlThuoc.SelectedValue), int.Parse(ddlLoaiCa.SelectedValue), out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
}
int maxTest = 3;
string s = "";
int temp = 1;
int countTest = 0;
for (int i = 0; i < tblChuong.Rows.Count; i++)
{
if (tblChuong.Rows[i]["KhoiLuong"] != DBNull.Value)
{
s = Convert.ToDecimal(tblChuong.Rows[i]["KhoiLuong"]).ToString("0.#####");
int vitriphancach = s.LastIndexOf(thapphan);
if (vitriphancach >= 0)
{
temp = s.Length - vitriphancach - 1;
}
if (temp > ChuongScale)
{
ChuongScale = temp;
}
countTest++;
if (countTest == maxTest)
{
break;
}
}
}
grvDanhSach.DataSource = tblChuong;
grvDanhSach.DataBind();
txtKhoiLuongPhanBo.Text = txtKhoiLuong.Text = KhoiLuong.ToString("0.#####");
txtSoCaAn.Text = Config.ToXVal2(SoLuongCa, 0);
hdNgayAn.Value = NgayAn.ToString("dd/MM/yyyy");
}
}
private void importExcelThuoc(string dir, string file)
{
DateTime NgayAn = DateTime.MinValue;
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT = 0;
string columnName = "";
decimal value = 0;
int idVatTu = 0;
int SoCaAn = 0;
string StrChuong = "";
string StrKL = "";
decimal kl = 0;
int LoaiCa = 0;
string[] aChuongOnly = null;
string[] aChuongExcept = null;
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
int numSheet = int.Parse(txtNumSheet.Text);
for (int num = 1; num <= numSheet; num++)
{
string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 2;
ArrayList colNames = new ArrayList();
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = dTable.Columns[i].ColumnName;
colNames.Add(columnName);
if (columnName.StartsWith("Column"))
{
int cot = i + 1;
lblMessage.Text += "<br/>Cột thứ " + cot.ToString() + " sheet " + num.ToString() + " có tên không hợp lệ (" + columnName + ")";
}
}
foreach (DataRow r in dTable.Rows)
{
DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture);
DateTime NgayChoAnOutput = DateTime.MinValue;
int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput);
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = colNames[i - 4].ToString();
if (columnName.StartsWith("LoaiCa"))
{
LoaiCa = Convert.ToInt32(r[i]);
aChuongOnly = null;
aChuongExcept = null;
}
else if (columnName.StartsWith("Chuong-Only"))
{
string sChuongOnly = r[i].ToString();
sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2);
aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Chuong-Except"))
{
string sChuongExcept = r[i].ToString();
sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2);
aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Column"))
{
}
else
{
value = 0;
if (r[i] != DBNull.Value) value = Convert.ToDecimal(r[i]);
if (value != 0)
{
idVatTu = int.Parse(columnName.Substring(columnName.IndexOf('(') + 1, columnName.IndexOf(')') - columnName.IndexOf('(') - 1));
if (IDCaSauAn == 0) IDCaSauAn = csCont.CaSauAn_ThemMoi(NgayChoAn, UserId, "");
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
if (aChuongOnly != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
int k = 0;
for (k = 0; k < aChuongOnly.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k]))
{
break;
}
}
if (k == aChuongOnly.Length) tblChuong.Rows.RemoveAt(j);
}
}
if (aChuongExcept != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
for (int k = 0; k < aChuongExcept.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k]))
{
tblChuong.Rows.RemoveAt(j);
break;
}
}
}
}
SoCaAn = 0;
StrChuong = "";
StrKL = "";
foreach (DataRow rC in tblChuong.Rows)
{
SoCaAn += Convert.ToInt32(rC["SoLuong"]);
}
foreach (DataRow rC in tblChuong.Rows)
{
StrChuong += "@" + rC["IDChuong"].ToString() + "@";
kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn);
StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@";
}
csCont.CaSauAn_InsertUpdateThuoc_UpdateChuong(IDCaSauAn, idVatTu, LoaiCa, SoCaAn, StrChuong, StrKL);
}
}
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
}
protected void btnUpdateThuoc_Click(object sender, EventArgs e)
{
decimal KhoiLuong = 0;
DateTime NgayAn = DateTime.MinValue;
string StrPhanCachKhuChuong = "";
string khuchuong = "";
string currkhuchuong = "";
string StrSoLuongChuong = "";
string StrSoLuongChuongTT = "";
string StrChuong = "";
string StrKL = "";
int SoLuongCa = 0;
int SoLuongTT = 0;
int index = 0;
if (txtThoiDiemFrom.Text.Trim() == "")
{
txtThoiDiemFrom.Text = DateTime.Now.ToString("dd/MM/yyyy");
}
DateTime TuNgay = DateTime.Parse(txtThoiDiemFrom.Text.Trim(), ci);
DateTime DenNgay;
if (txtThoiDiemTo.Text.Trim() == "")
{
DenNgay = DateTime.Now;
}
else
{
DenNgay = DateTime.Parse(txtThoiDiemTo.Text.Trim(), ci);
}
string strLoaiCa = Config.GetSelectedValues_At(ddlLoaiCa);
DataTable tblCaAn = csCont.LoadCaSauAnTuNgayDenNgay(TuNgay, DenNgay);
int current = 0;
if (strLoaiCa == "")
{
foreach (DataRow r in tblCaAn.Rows)
{
current++;
DataTable tblThuoc = csCont.CaSauAn_GetThuoc(Convert.ToInt32(r["ID"]), 1);
foreach (DataRow rThuoc in tblThuoc.Rows)
{
SoLuongCa = 0;
SoLuongTT = 0;
StrSoLuongChuong = "";
StrSoLuongChuongTT = "";
StrKL = "";
StrChuong = "";
StrPhanCachKhuChuong = "";
currkhuchuong = "";
khuchuong = "";
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(Convert.ToInt32(r["ID"]), Convert.ToInt32(rThuoc["Thuoc"]),
Convert.ToInt32(rThuoc["LoaiCa"]), out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
SoLuongCa = 0;
SoLuongTT = 0;
index = 0;
foreach (DataRow rChuong in tblChuong.Rows)
{
if (rChuong["KhoiLuong"] != DBNull.Value && Convert.ToDecimal(rChuong["KhoiLuong"]) != 0)
{
StrSoLuongChuong += "@" + rChuong["SoLuong"].ToString() + "@";
StrSoLuongChuongTT += "@" + rChuong["SoLuongTT"].ToString() + "@";
StrChuong += "@" + rChuong["IDChuong"].ToString() + "@";
StrKL += "@" + Convert.ToDecimal(rChuong["KhoiLuong"]).ToString("0.#####").Replace(",", ".") + "@";
SoLuongCa += Convert.ToInt32(rChuong["SoLuong"]);
SoLuongTT += Convert.ToInt32(rChuong["SoLuongTT"]);
currkhuchuong = rChuong["Chuong"].ToString().Substring(0, 2);
if (currkhuchuong != khuchuong)
{
StrPhanCachKhuChuong += "@" + index + "@";
khuchuong = currkhuchuong;
}
index++;
}
}
if (StrPhanCachKhuChuong != "")
{
StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(3) + "@" + index.ToString() + "@";
}
csCont.CaSauAn_InsertUpdateThuoc_NoCheck_ForUpdateHangLoat(Convert.ToInt32(r["ID"]), Convert.ToInt32(rThuoc["Thuoc"]),
KhoiLuong, Convert.ToInt32(rThuoc["LoaiCa"]), SoLuongCa, SoLuongTT, StrSoLuongChuong,
StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, UserId);
}
}
}
else
{
foreach (DataRow r in tblCaAn.Rows)
{
current++;
DataTable tblThuoc = csCont.CaSauAn_GetThuoc(Convert.ToInt32(r["ID"]), 1);
foreach (DataRow rThuoc in tblThuoc.Rows)
{
if (strLoaiCa.Contains("@" + rThuoc["LoaiCa"].ToString() + "@"))
{
SoLuongCa = 0;
SoLuongTT = 0;
StrSoLuongChuong = "";
StrSoLuongChuongTT = "";
StrKL = "";
StrChuong = "";
StrPhanCachKhuChuong = "";
currkhuchuong = "";
khuchuong = "";
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(Convert.ToInt32(r["ID"]), Convert.ToInt32(rThuoc["Thuoc"]),
Convert.ToInt32(rThuoc["LoaiCa"]), out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
SoLuongCa = 0;
SoLuongTT = 0;
index = 0;
foreach (DataRow rChuong in tblChuong.Rows)
{
if (rChuong["KhoiLuong"] != DBNull.Value && Convert.ToDecimal(rChuong["KhoiLuong"]) != 0)
{
StrSoLuongChuong += "@" + rChuong["SoLuong"].ToString() + "@";
StrSoLuongChuongTT += "@" + rChuong["SoLuongTT"].ToString() + "@";
StrChuong += "@" + rChuong["IDChuong"].ToString() + "@";
StrKL += "@" + Convert.ToDecimal(rChuong["KhoiLuong"]).ToString("0.#####").Replace(",", ".") + "@";
SoLuongCa += Convert.ToInt32(rChuong["SoLuong"]);
SoLuongTT += Convert.ToInt32(rChuong["SoLuongTT"]);
currkhuchuong = rChuong["Chuong"].ToString().Substring(0, 2);
if (currkhuchuong != khuchuong)
{
StrPhanCachKhuChuong += "@" + index + "@";
khuchuong = currkhuchuong;
}
index++;
}
}
if (StrPhanCachKhuChuong != "")
{
StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(3) + "@" + index.ToString() + "@";
}
csCont.CaSauAn_InsertUpdateThuoc_NoCheck_ForUpdateHangLoat(Convert.ToInt32(r["ID"]), Convert.ToInt32(rThuoc["Thuoc"]),
KhoiLuong, Convert.ToInt32(rThuoc["LoaiCa"]), SoLuongCa, SoLuongTT, StrSoLuongChuong,
StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, UserId);
}
}
}
}
Page.ClientScript.RegisterStartupScript(typeof(string), "daluuxong", "alert('Số ngày ăn đã cập nhật: " + tblCaAn.Rows.Count.ToString() + ", thời điểm kết thúc: " + DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss") + "');", true);
}
private void importExcelThuoc(string dir, string file)
{
string columnName = "";
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT;
DateTime NgayAn = DateTime.MinValue;
decimal value = 0;
int idVatTu = 0;
int SoCaAn = 0;
string StrChuong = "";
string StrSoLuongChuong = "";
string StrPhanCachKhuChuong = "";
string khuchuong = "";
string currkhuchuong = "";
int index = 0;
string StrKL = "";
decimal kl = 0;
int LoaiCa = 0;
string[] aChuongOnly = null;
string[] aChuongExcept = null;
decimal TongDoiChieu = 0;
string f = "{0:0.";
for (int i = 0; i < int.Parse(txtThapPhanT.Text); i++)
{
f += "#";
}
f += "}";
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
int numSheet = int.Parse(txtNumSheet.Text);
for (int num = 1; num <= numSheet; num++)
{
string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 2;
foreach (DataRow r in dTable.Rows)
{
ArrayList lstParam = new ArrayList();
DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture);
DateTime NgayChoAnOutput = DateTime.MinValue;
int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput);
if (IDCaSauAn < 1)
{
//Hiện thông báo ngày này chưa có cho ăn
lblMessage.Text += "<br/>Sheet " + num.ToString() + ",dòng " + idx.ToString() + " chưa được cho ăn ngày này.";
continue;
}
if (NgayChoAn != NgayChoAnOutput)
{
csCont.CaSauAn_Update(IDCaSauAn, NgayChoAn, UserId);
}
for (int i = 4; i < dTable.Columns.Count; i++)
{
columnName = dTable.Columns[i].ColumnName;
if (columnName.StartsWith("LoaiCa"))
{
LoaiCa = Convert.ToInt32(r[i]);
aChuongOnly = null;
aChuongExcept = null;
}
else if (columnName.StartsWith("Chuong-Only"))
{
string sChuongOnly = r[i].ToString();
sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2);
aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else if (columnName.StartsWith("Chuong-Except"))
{
string sChuongExcept = r[i].ToString();
sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2);
aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
else
{
value = 0;
if (r[i] != DBNull.Value)
{
value = Convert.ToDecimal(r[i]);
}
if (value != 0)
{
idVatTu = int.Parse(columnName.Substring(columnName.IndexOf('(') + 1, columnName.IndexOf(')') - columnName.IndexOf('(') - 1));
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
if (aChuongOnly != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
int k = 0;
for (k = 0; k < aChuongOnly.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k]))
{
break;
}
}
if (k == aChuongOnly.Length)
{
tblChuong.Rows.RemoveAt(j);
}
}
}
if (aChuongExcept != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
for (int k = 0; k < aChuongExcept.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k]))
{
tblChuong.Rows.RemoveAt(j);
break;
}
}
}
}
CaSauAnParam pF = Find(lstParam, LoaiCa, idVatTu);
if (pF == null)
{
pF = new CaSauAnParam(LoaiCa, idVatTu);
lstParam.Add(pF);
}
SoCaAn = 0;
StrChuong = "";
StrSoLuongChuong = "";
StrKL = "";
StrPhanCachKhuChuong = "";
foreach (DataRow rC in tblChuong.Rows)
{
SoCaAn += Convert.ToInt32(rC["SoLuong"]);
}
pF.KhoiLuong += value;
pF.SoLuongCa += SoCaAn;
TongDoiChieu = 0;
int h = 0;
int countChuong = 0;
khuchuong = "";
index = 0;
if (pF.StrChuong != "")
{
countChuong = pF.StrChuong.Substring(1, pF.StrChuong.Length - 2).Split(new string[] { "@@" }, StringSplitOptions.RemoveEmptyEntries).Length;
}
foreach (DataRow rC in tblChuong.Rows)
{
StrChuong += "@" + rC["IDChuong"].ToString() + "@";
kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn);
TongDoiChieu += Convert.ToDecimal(String.Format(f, kl));
if (h == tblChuong.Rows.Count - 1)
{
if (TongDoiChieu != value)
{
decimal temp = Convert.ToDecimal(String.Format(f, kl)) + value - TongDoiChieu;
StrKL += "@" + String.Format(f, temp).Replace(',', '.') + "@";
}
else
{
StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@";
}
}
else
{
StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@";
}
h++;
StrSoLuongChuong += "@" + rC["SoLuong"].ToString() + "@";
currkhuchuong = rC["Chuong"].ToString().Substring(0, 2);
if (currkhuchuong != khuchuong)
{
int temp = index + countChuong;
StrPhanCachKhuChuong += "@" + temp.ToString() + "@";
khuchuong = currkhuchuong;
}
index++;
}
int temp1 = index + countChuong;
StrPhanCachKhuChuong += "@" + temp1.ToString() + "@";
StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(StrPhanCachKhuChuong.IndexOf("@@") + 1);
pF.StrChuong += StrChuong;
pF.StrKL += StrKL;
pF.StrPhanCachKhuChuong += StrPhanCachKhuChuong;
pF.StrSoLuongChuong += StrSoLuongChuong;
//int res = csCont.CaSauAn_InsertUpdateThucAn(IDCaSauAn, idVatTu, value, LoaiCa, SoCaAn, SoCaTT, StrSoLuongChuong, StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, NV, UserId, (!chkValueAdd.Checked));
//if (res != 1) lblMessage.Text += "<br/>Sheet " + num.ToString() + ",dòng " + idx.ToString() + ", thức ăn " + r["VT"].ToString() + " không import được do số lượng hoặc thời điểm biến động không hợp lệ.";
}
}
}
foreach (CaSauAnParam p in lstParam)
{
//Xử lý từng cụm ở đây
int res = csCont.CaSauAn_UpdateThuoc_CoBan(IDCaSauAn, p.VatTu, p.KhoiLuong, p.LoaiCa, p.SoLuongCa, p.StrSoLuongChuong, p.StrChuong, p.StrKL, p.StrPhanCachKhuChuong);
if (res != 1)
{
lblMessage.Text += "<br/>Sheet " + num.ToString() + ",dòng " + idx.ToString() + ", thuốc " + p.VatTu.ToString() + ", loại cá " + p.LoaiCa.ToString() + " không import được do số lượng hoặc thời điểm biến động không hợp lệ.";
}
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
}
protected void UpdateThuoc(int CSAID)
{
decimal KhoiLuong = 0;
DateTime NgayAn = DateTime.MinValue;
string StrPhanCachKhuChuong = "";
string khuchuong = "";
string currkhuchuong = "";
string StrSoLuongChuong = "";
string StrSoLuongChuongTT = "";
string StrChuong = "";
string StrKL = "";
int SoLuongCa = 0;
int SoLuongTT = 0;
int index = 0;
DataTable tblThuoc = csCont.CaSauAn_GetThuoc(CSAID, 1);
foreach (DataRow rThuoc in tblThuoc.Rows)
{
SoLuongCa = 0;
SoLuongTT = 0;
StrSoLuongChuong = "";
StrSoLuongChuongTT = "";
StrKL = "";
StrChuong = "";
StrPhanCachKhuChuong = "";
currkhuchuong = "";
khuchuong = "";
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(CSAID, Convert.ToInt32(rThuoc["Thuoc"]),
Convert.ToInt32(rThuoc["LoaiCa"]), out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
SoLuongCa = 0;
SoLuongTT = 0;
index = 0;
foreach (DataRow rChuong in tblChuong.Rows)
{
if (rChuong["KhoiLuong"] != DBNull.Value && Convert.ToDecimal(rChuong["KhoiLuong"]) != 0)
{
StrSoLuongChuong += "@" + rChuong["SoLuong"].ToString() + "@";
StrSoLuongChuongTT += "@" + rChuong["SoLuongTT"].ToString() + "@";
StrChuong += "@" + rChuong["IDChuong"].ToString() + "@";
StrKL += "@" + Convert.ToDecimal(rChuong["KhoiLuong"]).ToString("0.#####").Replace(",", ".") + "@";
SoLuongCa += Convert.ToInt32(rChuong["SoLuong"]);
SoLuongTT += Convert.ToInt32(rChuong["SoLuongTT"]);
currkhuchuong = rChuong["Chuong"].ToString().Substring(0, 2);
if (currkhuchuong != khuchuong)
{
StrPhanCachKhuChuong += "@" + index + "@";
khuchuong = currkhuchuong;
}
index++;
}
}
if (StrPhanCachKhuChuong != "")
{
StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(3) + "@" + index.ToString() + "@";
}
csCont.CaSauAn_InsertUpdateThuoc_NoCheck_ForUpdateHangLoat(CSAID, Convert.ToInt32(rThuoc["Thuoc"]),
KhoiLuong, Convert.ToInt32(rThuoc["LoaiCa"]), SoLuongCa, SoLuongTT, StrSoLuongChuong,
StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, UserId);
}
}
private void importExcelThuoc(string dir, string file)
{
DateTime NgayAn = DateTime.MinValue;
decimal KhoiLuong = 0;
int SoLuongCa = 0;
int SoLuongTT = 0;
decimal value = 0;
int idVatTu = 0;
int SoCaAn = 0;
int SoCaTT = 0;
string StrChuong = "";
string StrSoLuongChuong = "";
string StrSoLuongChuongTT = "";
string StrPhanCachKhuChuong = "";
string khuchuong = "";
string currkhuchuong = "";
int index = 0;
string StrKL = "";
decimal kl = 0;
int LoaiCa = 0;
string[] aChuongOnly = null;
string[] aChuongExcept = null;
decimal TongDoiChieu = 0;
string f = "{0:0.";
for (int i = 0; i < int.Parse(txtThapPhanT.Text); i++)
{
f += "#";
}
f += "}";
IFormatProvider culture = new System.Globalization.CultureInfo("vi-VN", true);
string Excelstrcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + file + @";Extended Properties=""Excel 8.0;HDR=YES;""";
int numSheet = int.Parse(txtNumSheet.Text);
for (int num = 1; num <= numSheet; num++)
{
string SQLstr = "SELECT * FROM [Sheet" + num.ToString() + "$]";
OleDbConnection ExcelCon = new OleDbConnection(Excelstrcon);
ExcelCon.Open();
OleDbCommand dbCommand = new OleDbCommand(SQLstr, ExcelCon);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
DataTable dTable = new DataTable();
try
{
dataAdapter.Fill(dTable);
int idx = 2;
foreach (DataRow r in dTable.Rows)
{
DateTime NgayChoAn = DateTime.Parse(r["Ngay"].ToString() + "/" + r["Thang"].ToString() + "/" + r["Nam"].ToString() + " 23:00:00", culture);
DateTime NgayChoAnOutput = DateTime.MinValue;
int IDCaSauAn = csCont.CaSauAn_GetCaSauAnByNgay(NgayChoAn, out NgayChoAnOutput);
if (IDCaSauAn != 0 && NgayChoAn != NgayChoAnOutput)
{
csCont.CaSauAn_Update(IDCaSauAn, NgayChoAn, UserId);
}
if (idx == 2)
{
LoaiCa = Convert.ToInt32(r["LoaiCa"]);
aChuongOnly = null;
aChuongExcept = null;
string sChuongOnly = r["Chuong-Only"].ToString();
if (sChuongOnly != "")
{
sChuongOnly = sChuongOnly.Substring(1, sChuongOnly.Length - 2);
aChuongOnly = sChuongOnly.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
string sChuongExcept = r["Chuong-Except"].ToString();
if (sChuongExcept != "")
{
sChuongExcept = sChuongExcept.Substring(1, sChuongExcept.Length - 2);
aChuongExcept = sChuongExcept.Split(new string[] { "!!" }, StringSplitOptions.RemoveEmptyEntries);
}
}
if (r["SL"] != DBNull.Value && r["SL"].ToString() != "" && Convert.ToDecimal(r["SL"]) != 0)
{
value = Convert.ToDecimal(r["SL"]);
string VT = r["VT"].ToString();
idVatTu = int.Parse(VT.Substring(VT.IndexOf('(') + 1, VT.IndexOf(')') - VT.IndexOf('(') - 1));
if (IDCaSauAn == 0)
{
IDCaSauAn = csCont.CaSauAn_ThemMoi(NgayChoAn, UserId, "");
}
DataTable tblChuong = csCont.CaSauAn_GetChuongByThuocByLoaiCa(IDCaSauAn, idVatTu, LoaiCa, out KhoiLuong, out SoLuongCa, out SoLuongTT, out NgayAn);
if (aChuongOnly != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
int k = 0;
for (k = 0; k < aChuongOnly.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongOnly[k]))
{
break;
}
}
if (k == aChuongOnly.Length)
{
tblChuong.Rows.RemoveAt(j);
}
}
}
if (aChuongExcept != null)
{
for (int j = tblChuong.Rows.Count - 1; j > -1; j--)
{
for (int k = 0; k < aChuongExcept.Length; k++)
{
if (tblChuong.Rows[j]["Chuong"].ToString().Contains(aChuongExcept[k]))
{
tblChuong.Rows.RemoveAt(j);
break;
}
}
}
}
SoCaAn = 0;
SoCaTT = 0;
StrChuong = "";
StrSoLuongChuong = "";
StrSoLuongChuongTT = "";
StrKL = "";
foreach (DataRow rC in tblChuong.Rows)
{
SoCaAn += Convert.ToInt32(rC["SoLuong"]);
SoCaTT += Convert.ToInt32(rC["SoLuongTT"]);
}
TongDoiChieu = 0;
int h = 0;
foreach (DataRow rC in tblChuong.Rows)
{
StrChuong += "@" + rC["IDChuong"].ToString() + "@";
kl = value * Convert.ToDecimal(rC["SoLuong"]) / Convert.ToDecimal(SoCaAn);
//StrKL += "@" + String.Format("{0:0.#####}", kl).Replace(',', '.') + "@";
TongDoiChieu += Convert.ToDecimal(String.Format(f, kl));
if (h == tblChuong.Rows.Count - 1)
{
if (TongDoiChieu != value)
{
decimal temp = Convert.ToDecimal(String.Format(f, kl)) + value - TongDoiChieu;
StrKL += "@" + String.Format(f, temp).Replace(',', '.') + "@";
}
else
{
StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@";
}
}
else
{
StrKL += "@" + String.Format(f, kl).Replace(',', '.') + "@";
}
h++;
StrSoLuongChuong += "@" + rC["SoLuong"].ToString() + "@";
StrSoLuongChuongTT += "@" + rC["SoLuongTT"].ToString() + "@";
currkhuchuong = rC["Chuong"].ToString().Substring(0, 2);
if (currkhuchuong != khuchuong)
{
StrPhanCachKhuChuong += "@" + index + "@";
khuchuong = currkhuchuong;
}
index++;
}
StrPhanCachKhuChuong = StrPhanCachKhuChuong.Substring(3) + "@" + index.ToString() + "@";
int res = csCont.CaSauAn_InsertUpdateThuoc(IDCaSauAn, idVatTu, value, LoaiCa, SoCaAn, SoLuongTT, StrSoLuongChuong, StrSoLuongChuongTT, StrChuong, StrKL, StrPhanCachKhuChuong, UserId, (!chkValueAdd.Checked));
if (res != 1)
{
lblMessage.Text += "<br/>Sheet " + num.ToString() + ",dòng " + idx.ToString() + ", thuốc " + r["VT"].ToString() + " không import được do số lượng hoặc thời điểm biến động không hợp lệ.";
}
}
idx++;
}
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
lblMessage.Text += "<br/>Đã import xong Sheet " + num.ToString() + "!";
}
catch (Exception ex)
{
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
ExcelCon.Close();
ExcelCon.Dispose();
Response.Write(ex.ToString());
}
}
}
