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