public void ImPort_Excel()
{
DataTable dt = new DataTable();
try
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "*.xls|*.xls|*.xlsx|*.xlsx";
TOOLChuyenDuLieu.HelpFile.CTLImportExcel excel = new TOOLChuyenDuLieu.HelpFile.CTLImportExcel();
if (dialog.ShowDialog() == DialogResult.OK)
{
FileInfo file = new FileInfo(dialog.FileName);
if (file.Extension.ToString().Trim().ToUpper() == ".XLS")
{
dt = excel.getDataFromXLS(dialog.FileName);
}
else if (file.Extension.ToString().Trim().ToUpper() == ".XLSX")
{
dt = excel.getDataFromXLS2007(dialog.FileName);
}
int dem = 0;
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.ToUpper() == "CUSTID")
{
dem++;
}
}
if (dem == 0)
{
return;
}
DataTable table = KhoitaoDSPriceBook();
if (dt.Rows.Count >= 1)
{
foreach (DataRow row in dt.Rows)
{
DataRow dataRow = table.NewRow();
dataRow["CUSTID"] = row["CUSTID"].ToString();
dataRow["PRICEBOOK"] = row["PRICEBOOK"].ToString();
dataRow["DATE"] = row["DATE"].ToString();
TPSDataAccess access = new TPSDataAccess("H:\\Data\\CUST.pts");
DataTable source = access.GetTableDM(@"Select MCUSTNUM,Pricecategory from CUST
where 1=1 and MCUSTNUM=" + row["CUSTID"].ToString());
if (source == null)
{
dataRow["Check"] = true;
}
else if (source.Rows.Count == 0)
{
dataRow["Check"] = true;
}
else
{
if (source.Rows[0]["Pricecategory"].ToString().Trim() != row["PRICEBOOK"].ToString().Trim())
{
dataRow["Check"] = true;
}
else
{
dataRow["Check"] = false;
}
dataRow["Pricecategory"] = source.Rows[0]["Pricecategory"].ToString();
}
table.Rows.Add(dataRow);
}
}
GrDSPriceSKU.DataSource = table;
}
}
catch (Exception ex)
{
CTLError.WriteError("FrmImportfile ImportExcel", ex.Message);
return;
throw new Exception(ex.Message);
}
}
private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
//if (textBox1.Text.Length==13)
//{
// textBox1.Text = textBox1.Text.Substring(2, 10);
//}
if (textBox1.Text.Length != 10)
{
labthongbao.Visible = true;
labthongbao.Text = "Vui long nhap Ma 10 cua KH";
return;
}
//if (textBox1.Text.Length == 10 &&radKHTT.Checked)
//{
// string ma13 = "";
// SQLHelper helper = new SQLHelper();
// DataTable dtcustommer = helper.InfoCustommer(textBox1.Text.Substring(3,7));
// if (dtcustommer != null)
// {
// if (dtcustommer.Rows.Count > 0)
// {
// //_sogiay = 60;
// //if (Config._timemer != null)
// // _sogiay = Convert.ToInt32(Config._timemer) * 60;
// ConvertBarCode cv = new ConvertBarCode();
// //if (Convert.ToInt64(textBox1.Text) > 25000)
// ma13 = cv.CreaBarCode(textBox1.Text.Substring(3, 7));
// //else if (textBox1.Text.Length != 13)
// // ma13 = cv.CreaBarCodeTV(textBox1.Text);
// //if (textBox1.Text.Length == 13)
// // ma13 = textBox1.Text;
// //Frmkiot frmkiot = new Frmkiot(this, dtcustommer, ma13);
// //this.OpenDialog(frmkiot);
// if (dtcustommer.Rows.Count <= 0)
// return;
// labhoten.Text = dtcustommer.Rows[0]["HoLot"].ToString() + " " + dtcustommer.Rows[0]["Ten"].ToString();
// labsodt.Text = (dtcustommer.Rows[0]["DTDD"].ToString() == string.Empty) ? dtcustommer.Rows[0]["DTBan"].ToString() : dtcustommer.Rows[0]["DTDD"].ToString();
// labmathe.Text = dtcustommer.Rows[0]["MaThe13"].ToString();
// DataTable dt = helper.GetInfoCustommer(ma13);
// if (dt == null)
// {
// labdiemmuahangChinhthuc.Text = "_ _";
// labdiemthuongChinhthuc.Text = "_ _";
// labdiemdasudungChinhThuc.Text = "_ _";
// labdiemconlaiChinhthuc.Text = "_ _";
// labdiemmuahangGhinhan.Text = "_ _";
// labDiemThuongGhiNhan.Text = "_ _";
// labDiemDaSuDungGhiNhan.Text = "_ _";
// labDiemConLaiGiNhan.Text = "_ _";
// labSoPhieuQuaTang.Text = "_ _";
// labNgayIn.Text = DateTime.Now.ToString("dd/MM/yyyy");
// labDiemConLaiNamTruoc.Text = "_ _";
// labdiemmuahangchinhthucdenhet.Text = "_ _";
// }
// else
// {
// loadThongTin(dt);
// }
// textBox1.Clear();
// }
// else if (textBox1.Text.Length == 13)
// {
// if (rtbarcode != null)
// {
// rtbarcode(textBox1.Text);
// this.Close();
// }
// }
// }
// else if (textBox1.Text.Length == 13)
// {
// if (rtbarcode != null)
// {
// rtbarcode(textBox1.Text);
// this.Close();
// }
// }
//}
labthongbao.Visible = false;
if (textBox1.Text.Length == 10 && radWDSS.Checked)
{
//code get infomation cust Windss
TPSDataAccess access = new TPSDataAccess("H:\\Data\\CUST.tps");
DataTable source = access.GetTableDM(@"Select Mcreatedate,Xlastmoddate,Pricecategory,Pclienttype,Allowchrg,Mfname,Mlname,Mcustnum,Jcuststatus from CUST where Mcustnum=" + textBox1.Text);
if (source != null && source.Rows.Count > 0)
{
labWMa.Text = source.Rows[0]["Mcustnum"].ToString();
//labWten.Text = source.Rows[0]["Mlname"].ToString() + " " + source.Rows[0]["Mfname"].ToString();
labWngaytao.Text = source.Rows[0]["Mcreatedate"] != null ? ((DateTime)source.Rows[0]["Mcreatedate"]).ToString("dd/MM/yyyy") : "";
labWngaychinhsua.Text = source.Rows[0]["Jcuststatus"].ToString();
labWSogia.Text = source.Rows[0]["Pricecategory"].ToString();
labWLoaiKH.Text = source.Rows[0]["Pclienttype"].ToString();
labWmuano.Text = source.Rows[0]["Allowchrg"].ToString();
}
else
{
labthongbao.Visible = true;
labthongbao.Text = "Vui lòng kiểm tra mã KH khác";
labWMa.Text = "_ _";
//labWten.Text = "_ _";
labWngaytao.Text = "_ _";
labWngaychinhsua.Text = "_ _";
labWSogia.Text = "_ _";
labWLoaiKH.Text = "_ _";
labWmuano.Text = "_ _";
}
}
if (textBox1.Text.Length > 13)
{
textBox1.Clear();
}
}
}
private void txtsoluong_KeyDown(object sender, KeyEventArgs e)
{
string upc = "";
string sku = "";
if (e.KeyCode == Keys.F5)
{
btclear_Click(null, null);
return;
}
if (e.KeyCode == Keys.Enter)
{
if (gridView1.RowCount >= 30)
{
InfoMessage.HienThi("Số lượng SKU cho phép xuất bảng giá là 30 !", "Vui lòng kiểm tra lại",
"Thong bao", HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
if (txtSKU.Text == string.Empty || txtSKU.Text.Length > 18)
{
return;
}
string where = "";
if (txtSKU.Text.Length <= 9)
{
sku = _sokhong.Substring(0, 9 - txtSKU.Text.Length) + txtSKU.Text;
where = " and SKU='" + sku + "'";
}
else
{
upc = _sokhong.Substring(0, 18 - txtSKU.Text.Length) + txtSKU.Text;
where = " and UPC='" + upc + "'";
}
if (!validate((sku != string.Empty) ? sku : upc))
{
InfoMessage.HienThi("SKU đã có trong danh sách!", "Vui lòng thay đổi số lượng trong lưới", "Thong Bao",
HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
TPSDataAccess dataAccess = new TPSDataAccess(_PathFileINVMST);
TPSDataAccess dataAccessKM = new TPSDataAccess(_PathFileINVEVT);
DataTable dt = new DataTable();
dt = dataAccess.GetTableDM("Select SKU,UPC,Description,Price From INVMST where 1=1 " + where);
if (dt == null || dt.Rows.Count <= 0)
{
InfoMessage.HienThi("SKU không có trong danh mục!", "Vui lòng kiểm tra lại", "Thong Bao",
HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
DataTable dtKM = new DataTable();
dtKM =
dataAccessKM.GetTableDM("select SKU,QTY1,PRICE,DISCPRICE,Method,Prc_Key,Code From INVEVT where SKU='" +
dt.Rows[0]["SKU"].ToString() + "' and " + (Math.Round(dateNgayapDung.Value.ToOADate()) + _Const_datetime) + @" >= Start
and " + (Math.Round(dateNgayapDung.Value.ToOADate()) + _Const_datetime) + " <= stop and Method in (2,25)");
DataRow r = _dsSKU.NewRow();
r["SKU"] = dt.Rows[0]["SKU"].ToString();
r["UPC"] = dt.Rows[0]["UPC"].ToString();
r["Description"] = dt.Rows[0]["Description"].ToString();
r["SoLuong"] = txtsoluong.Value;
r["GiaGoc"] = dt.Rows[0]["Price"].ToString();
r["ThanhTien"] = r["GiaGoc"];
if (dtKM.Rows.Count > 0)
{
r["Method"] = dtKM.Rows[0]["Method"].ToString();
r["DISCPRICE"] = dtKM.Rows[0]["DISCPRICE"].ToString();
r["QTY1"] = dtKM.Rows[0]["QTY1"].ToString();
if (dtKM.Rows[0]["Method"].ToString() == "25")
{
DataRow row;
if (dtKM.Select("MeThod=25 and Prc_Key=Min(Prc_Key)").Length == 1)
{
row = dtKM.Select("MeThod=25 and Prc_Key=Min(Prc_Key)")[0];
}
else
{
Int32 prc_key = Convert.ToInt32(dtKM.Select("MeThod=25 and Prc_Key=Min(Prc_Key)")[0]["Prc_key"]);
row = dtKM.Select("MeThod=25 and Prc_Key=" + prc_key + " and Code=max(Code)")[0];
}
//panel1.Visible = true;
//labgiapos.Text = " " + Convert.ToDecimal(row["Price"]).ToString("N2");// +" Từ: " + Convert.ToDateTime(row["Start"]).ToString("dd/MM/yyyy") + " Đến:" + Convert.ToDateTime(row["Stop"]).ToString("dd/MM/yyyy");
//labslpos.Text = "1";
r["GiaKM"] = row["PRICE"].ToString();
r["ThanhTien"] = txtsoluong.Value * Convert.ToDecimal(r["GiaKM"]);
}
else if (dtKM.Rows[0]["Method"].ToString() == "2")
{
DataRow row;
if (dtKM.Select("MeThod=2 and Prc_Key=Min(Prc_Key)").Length == 1)
{
row = dtKM.Select("MeThod=2 and Prc_Key=Min(Prc_Key)")[0];
}
else
{
Int32 prc_key = Convert.ToInt32(dtKM.Select("MeThod=2 and Prc_Key=Min(Prc_Key)")[0]["Prc_key"]);
row = dtKM.Select("MeThod=2 and Prc_Key=" + prc_key + " and Code=max(Code)")[0];
}
//panel2.Visible = true;
//labGiaKMTL.Text = "Khi mua với SL " + Convert.ToInt32(row["Qty1"]).ToString("N0") + " giá tiền là " + Convert.ToDecimal(row["DiscPrice"]).ToString("N2"); //+ " Từ: " + Convert.ToDateTime(row["Start"]).ToString("dd/MM/yyyy") + " Đến:" + Convert.ToDateTime(row["Stop"]).ToString("dd/MM/yyyy");
//labslKMTL.Text = Convert.ToInt32(row["Qty1"]).ToString("N0");
r["ThanhTien"] = (int)(txtsoluong.Value / Int32.Parse(Convert.ToDecimal(row["QTY1"]).ToString("N0"))) *
Convert.ToDecimal(row["DISCPRICE"].ToString())
+
(int)(txtsoluong.Value % Int32.Parse(Convert.ToDecimal(row["QTY1"]).ToString("N0"))) *
Convert.ToDecimal(r["GiaGoc"]);
r["GhiChu"] = "Mua SL " + row["QTY1"].ToString() + " co gia " +
Convert.ToDecimal(row["DISCPRICE"]).ToString("N0");
}
}
else
{
r["GiaKM"] = r["GiaGoc"];
r["ThanhTien"] = Convert.ToDecimal(r["GiaGoc"]) * txtsoluong.Value;
}
_dsSKU.Rows.Add(r);
_dsSKUAdd.Add((sku != string.Empty) ? sku : upc, _indexadd++);
clearData();
txtSKU.Focus();
}
GrDSSKU.DataSource = _dsSKU;
//_dsSKUAdd.Add((sku != string.Empty) ? sku : upc, _indexadd++);
//clearData();
}
private void simpleButton1_Click(object sender, EventArgs e)
{
string upc = "";
string sku = "";
if (gridView1.RowCount >= 30)
{
InfoMessage.HienThi("Số lượng SKU cho phép xuất bảng giá là 30 !", "Vui lòng kiểm tra lại",
"Thong bao", HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
if (txtSKU.Text == string.Empty || txtSKU.Text.Length > 18)
{
return;
}
string where = "";
if (txtSKU.Text.Length <= 9)
{
sku = _sokhong.Substring(0, 9 - txtSKU.Text.Length) + txtSKU.Text;
where = " and SKU='" + sku + "'";
}
else
{
upc = _sokhong.Substring(0, 18 - txtSKU.Text.Length) + txtSKU.Text;
where = " and UPC='" + upc + "'";
}
if (!validate((sku != string.Empty) ? sku : upc))
{
InfoMessage.HienThi("SKU đã có trong danh sách!", "Vui lòng thay đổi số lượng trong lưới", "Thong Bao",
HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
TPSDataAccess dataAccess = new TPSDataAccess(_PathFileINVMST);
TPSDataAccess dataAccessKM = new TPSDataAccess(_PathFileINVEVT);
DataTable dt = new DataTable();
dt = dataAccess.GetTableDM("Select SKU,UPC,Description,Price From INVMST where 1=1 " + where);
if (dt == null || dt.Rows.Count <= 0)
{
InfoMessage.HienThi("SKU không có trong danh mục!", "Vui lòng kiểm tra lại", "Thong Bao",
HinhAnhThongBao.THONGTIN, NutThongBao.DONGY);
return;
}
DataTable dtKM = new DataTable();
dtKM =
dataAccessKM.GetTableDM("select SKU,QTY1,DISCPRICE,Method From INVEVT where SKU='" +
dt.Rows[0]["SKU"].ToString() + "' and " + (Math.Round(DateTime.Now.ToOADate()) + _Const_datetime) + @" >= Start
and " + (Math.Round(DateTime.Now.ToOADate()) + _Const_datetime) + " <= stop ");
DataRow r = _dsSKU.NewRow();
r["SKU"] = dt.Rows[0]["SKU"].ToString();
r["UPC"] = dt.Rows[0]["UPC"].ToString();
r["Description"] = dt.Rows[0]["Description"].ToString();
r["SoLuong"] = txtsoluong.Value;
r["GiaGoc"] = dt.Rows[0]["Price"].ToString();
r["ThanhTien"] = r["GiaGoc"];
if (dtKM.Rows.Count > 0)
{
r["Method"] = dtKM.Rows[0]["Method"].ToString();
r["DISCPRICE"] = dtKM.Rows[0]["DISCPRICE"].ToString();
r["QTY1"] = dtKM.Rows[0]["QTY1"].ToString();
if (dtKM.Rows[0]["Method"].ToString() == "25")
{
r["GiaKM"] = dtKM.Rows[0]["DISCPRICE"].ToString();
r["ThanhTien"] = txtsoluong.Value * Convert.ToDecimal(r["GiaKM"]);
}
else if (dtKM.Rows[0]["Method"].ToString() == "2")
{
r["ThanhTien"] = (int)(txtsoluong.Value / Int32.Parse(Convert.ToDecimal(dtKM.Rows[0]["QTY1"]).ToString("N0"))) *
Convert.ToDecimal(dtKM.Rows[0]["DISCPRICE"].ToString())
+
(int)(txtsoluong.Value % Int32.Parse(Convert.ToDecimal(dtKM.Rows[0]["QTY1"]).ToString("N0"))) *
Convert.ToDecimal(r["GiaGoc"]);
r["GhiChu"] = "Mua SL " + dtKM.Rows[0]["QTY1"].ToString() + " co gia " +
Convert.ToDecimal(dtKM.Rows[0]["DISCPRICE"]).ToString("N0");
}
}
else
{
r["GiaKM"] = r["GiaGoc"];
r["ThanhTien"] = Convert.ToDecimal(r["GiaGoc"]) * txtsoluong.Value;
}
_dsSKU.Rows.Add(r);
_dsSKUAdd.Add((sku != string.Empty) ? sku : upc, _indexadd++);
clearData();
txtSKU.Focus();
GrDSSKU.DataSource = _dsSKU;
}
public void ImPort_Excel()
{
DataTable dt = new DataTable();
try
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "*.xls|*.xls|*.xlsx|*.xlsx";
CTLImportExcel excel = new CTLImportExcel();
if (dialog.ShowDialog() == DialogResult.OK)
{
FileInfo file = new FileInfo(dialog.FileName);
if (file.Extension.ToString().Trim().ToUpper() == ".XLS")
{
dt = excel.getDataFromXLS(dialog.FileName);
}
else if (file.Extension.ToString().Trim().ToUpper() == ".XLSX")
{
dt = excel.getDataFromXLS2007(dialog.FileName);
}
int dem = 0;
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.ToUpper() == "SKU")
{
dem++;
}
}
if (dem == 0)
{
return;
}
string listma = LayDKMaSKUsql(dt);
if (listma.Trim() == "and SKU in )")
{
return;
}
if (dt.Rows.Count >= 1)
{//code cu
TPSDataAccess access = new TPSDataAccess(_PathFileINVMST);
// DataTable source = access.GetTableDM(@"Select SKU,UPC,Description,Price,CurrencyCode,Sell_Unit from INVMST
// where 1=1 "+listma);
// if (source.Rows.Count >= 1)
// GridDSMa.DataSource = source;
//sua lai
DataTable dttemp = new DataTable();
dttemp.Columns.Add("SKU");
dttemp.Columns.Add("UPC");
dttemp.Columns.Add("Description");
dttemp.Columns.Add("Price");
dttemp.Columns.Add("CurrencyCode");
dttemp.Columns.Add("Sell_Unit");
foreach (DataRow r in dt.Rows)
{
if (r["SKU"].ToString() != string.Empty)
{
DataTable source = access.GetTableDM(string.Format(@"Select SKU,UPC,Description,Price,CurrencyCode,Sell_Unit from INVMST
where 1=1 and sku='{0}'", r["SKU"].ToString()));
if (source.Rows.Count > 0)
{
DataRow nrow = dttemp.NewRow();
nrow["SKU"] = source.Rows[0]["SKU"];
nrow["UPC"] = source.Rows[0]["UPC"];
nrow["Description"] = source.Rows[0]["Description"];
nrow["Price"] = source.Rows[0]["Price"];
nrow["CurrencyCode"] = source.Rows[0]["CurrencyCode"];
nrow["Sell_Unit"] = source.Rows[0]["Sell_Unit"];
dttemp.Rows.Add(nrow);
}
}
}
if (dttemp.Rows.Count > 0)
{
GridDSMa.DataSource = dttemp;
}
}
}
}
catch (Exception ex)
{
CTLError.WriteError("FrmImportfile ImportExcel", ex.Message);
return;
throw new Exception(ex.Message);
}
}
