private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
txtDMVTPath.Text = ofd.FileName;
DataTable dtModule = TextUtils.ExcelToDatatableNoHeader(ofd.FileName, "3");
if (dtModule.Rows[5]["F12"].ToString().Contains(":"))
{
_muc = dtModule.Rows[5]["F12"].ToString().Split(':')[1].Trim();
}
_productName = dtModule.Rows[5]["F1"].ToString().Split(':')[1].Trim();
_projectCode = dtModule.Rows[2]["F8"].ToString().Trim();
dtProject.Rows.Clear();
dtProject.Rows.Add(_projectCode);
dtModule = dtModule.Select("F5 like 'TPAD%'").CopyToDataTable();
dtModule.Columns.Add("check", typeof(bool));
grvModule.DataSource = null;
grvModule.AutoGenerateColumns = false;
grvModule.DataSource = dtModule;
}
}
private void btnImportPrice_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
DataTable dtPrice = TextUtils.ExcelToDatatableNoHeader(ofd.FileName, "Sheet1");
for (int i = 0; i < grvData.RowCount; i++)
{
string partsCode = TextUtils.ToString(grvData.GetRowCellValue(i, colCode));
decimal giaGanNhat = TextUtils.ToDecimal(grvData.GetRowCellValue(i, colPriceGN));
decimal qty = TextUtils.ToDecimal(grvData.GetRowCellValue(i, colTotalBuy));
DataRow[] drs = dtPrice.Select("F3 = '" + partsCode + "'");
if (drs.Length > 0)
{
decimal currentPrice = TextUtils.ToDecimal(drs[0]["F7"]);
grvData.SetRowCellValue(i, colPrice, currentPrice);
grvData.SetRowCellValue(i, colChenhLech, currentPrice - giaGanNhat);
//grvData.SetRowCellValue(i, colChenhLech, currentPrice - giaGanNhat);
grvData.SetRowCellValue(i, colTotalChenhLech, qty * (currentPrice - giaGanNhat));
}
}
}
}
void loadAllDmvt()
{
string[] listFile = Directory.GetFiles("D:\\ListDMVT", "*.*", SearchOption.TopDirectoryOnly);
DataTable dtAll = new DataTable();
foreach (string filePath in listFile)
{
try
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "DMVT");
dt = dt.AsEnumerable()
.Where(row => TextUtils.ToInt(row.Field <string>("F1") == "" ||
row.Field <string>("F1") == null ? "0" : row.Field <string>("F1").Substring(0, 1)) > 0)
.CopyToDataTable();
if (dtAll.Rows.Count == 0)
{
dtAll = dt.Copy();
}
else
{
dtAll.Merge(dt);
}
}
catch
{
}
}
gridControl1.DataSource = dtAll;
gridView1.BestFitColumns();
}
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang load danh sách thiết bị..."))
{
_count = 0;
_dtProduct = TextUtils.ExcelToDatatableNoHeader(ofd.FileName, "Sheet1");
if (_dtProduct.Rows.Count == 0)
{
return;
}
_dtProduct.Rows.RemoveAt(0);
DataRow[] drs = _dtProduct.Select("F1 > 0");
//_dtProduct = _dtProduct.AsEnumerable()
// .Where(row => TextUtils.ToInt(row.Field<string>("F1") == "" ||
// row.Field<string>("F1") == null ? "0" : row.Field<string>("F1").Substring(0, 1)) > 0)
// .CopyToDataTable();
grvModule.DataSource = null;
grvModule.AutoGenerateColumns = false;
grvModule.DataSource = _dtProduct;
}
}
}
void updatePart()
{
string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
}
else
{
return;
}
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet2");
dt = dt.Select("F2 is not null and F2 <> ''").CopyToDataTable();
if (dt.Rows.Count == 0)
{
MessageBox.Show("Bạn đã chọn sai biểu mẫu dữ liệu.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
DataTable dtPart = LibQLSX.Select("select * from Parts with(nolock)");
int count = 0;
foreach (DataRow row in dt.Rows)
{
string partsCode = TextUtils.ToString(row["F2"]);
if (partsCode == "")
{
continue;
}
string des = TextUtils.ToString(row["F3"]);
string hsCode = TextUtils.ToString(row["F4"]);
decimal importTax = TextUtils.ToDecimal(row["F5"]);
DataRow[] drs = dtPart.Select("PartsCode = '" + partsCode.Trim() + "'");
if (drs.Length > 0)
{
string partsId = TextUtils.ToString(drs[0]["PartsId"]);
PartsModel part = (PartsModel)PartsBO.Instance.FindByAttribute("PartsId", partsId)[0];
part.Description = des;
part.HsCode = hsCode;
part.ImportTax = importTax;
PartsBO.Instance.UpdateQLSX(part);
count++;
}
}
MessageBox.Show(count.ToString());
loadData(1);
}
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel|*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
txtPath.Text = ofd.FileName;
_dtMaterial = TextUtils.ExcelToDatatableNoHeader(ofd.FileName, "Sheet1");
_dtMaterial.Rows.RemoveAt(0);
grdData.DataSource = _dtMaterial;
}
}
private void txtCode_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
string filePath = @"\\SERVER\Company\Share\Chuyen giao\HDTH.xlsx";
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "EX");
DataRow[] drs = dt.Select("F3 = '" + txtCode.Text.Trim() + "'");
if (drs.Length > 0)
{
txtName.Text = TextUtils.ToString(drs[0]["F2"]).ToUpper();
}
}
}
void updateUser()
{
string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
}
else
{
return;
}
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet2");
dt = dt.Select("F2 is not null and F2 <> ''").CopyToDataTable();
DataTable dtUser = LibQLSX.Select("select * from Users with(nolock)");
int count = 0;
foreach (DataRow row in dt.Rows)
{
try
{
string code = TextUtils.ToString(row["F2"]);
if (code == "")
{
continue;
}
string userName = TextUtils.ToString(row["F3"]);
DataRow[] drs = dtUser.Select("UserName = '******'");
if (drs.Length > 0)
{
string userId = TextUtils.ToString(drs[0]["UserId"]);
TPA.Model.UsersModel user = (TPA.Model.UsersModel)TPA.Business.UsersBO.Instance.FindByAttribute("UserId", userId)[0];
user.Code = code;
PartsBO.Instance.UpdateQLSX(user);
count++;
}
}
catch
{
}
}
MessageBox.Show(count.ToString());
}
void updatePart()
{
string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
}
else
{
return;
}
//filePath = @"E:\PROJECTS\TanPhat\KAIZEN\Phòng vật tư\HScode-Xuất nhập khẩu\MISUMI-HSCODE.xlsx";
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet2");
dt = dt.Select("F2 is not null and F2 <> ''").CopyToDataTable();
DataTable dtPart = LibQLSX.Select("select * from Parts with(nolock)");
int count = 0;
foreach (DataRow row in dt.Rows)
{
string partsCode = TextUtils.ToString(row["F2"]);
if (partsCode == "")
{
continue;
}
string des = TextUtils.ToString(row["F3"]);
string hsCode = TextUtils.ToString(row["F4"]);
decimal importTax = TextUtils.ToDecimal(row["F5"]);
DataRow[] drs = dtPart.Select("PartsCode = '" + partsCode.Trim() + "'");
if (drs.Length > 0)
{
string partsId = TextUtils.ToString(drs[0]["PartsId"]);
PartsModel part = (PartsModel)PartsBO.Instance.FindByAttribute("PartsId", partsId)[0];
part.Description = des;
part.HsCode = hsCode;
part.ImportTax = importTax;
PartsBO.Instance.UpdateQLSX(part);
count++;
}
}
MessageBox.Show(count.ToString());
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
try
{
_dtData = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
_dtData.Columns.Add("ID", typeof(int));
_dtData.Rows.RemoveAt(0);
grdData.DataSource = _dtData;
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
grvData.Columns.Clear();
if (chkAutoCheck.Checked)
{
try
{
DataTable dtt = ds.Tables[cboSheet.SelectedIndex];
for (int i = 0; i < dtt.Columns.Count; i++)
{
dtt.Columns[i].ColumnName = "F" + i.ToString();
}
grdData.DataSource = dtt;
grvData.PopulateColumns(); // Tao cot cho cac truong trong datatable
//grvData.BestFitColumns(); // Tu dong dieu chinh kich thuoc cot -- Ton nhieu tg
grvData.Focus();
for (int i = 0; i < dtt.Columns.Count; i++)
{
grvData.Columns[i].Caption = "F" + i.ToString();
//dtt.Columns[i].ColumnName = "F" + i.ToString();
}
var test = grvData.Columns;
}
catch (Exception er)
{
TextUtils.ShowError(er);
grdData.DataSource = null;
}
}
else
{
try
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
grdData.DataSource = dt;
grvData.PopulateColumns(); // Tao cot cho cac truong trong datatable
//grvData.BestFitColumns(); // Tu dong dieu chinh kich thuoc cot -- Ton nhieu tg
grvData.Focus();
}
catch (Exception er)
{
TextUtils.ShowError(er);
grdData.DataSource = null;
}
}
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
try
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
grdData.DataSource = dt;
grvData.PopulateColumns();
grvData.BestFitColumns();
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
public DataTable GetFullDMVT(string filePath)
{
try
{
DataTable dt1 = TextUtils.ExcelToDatatableNoHeader(filePath.Trim(), "DMVT");
DataTable dt = dt1.Copy();
dt = dt.AsEnumerable()
.Where(row => TextUtils.ToInt(row.Field <string>("F1") == "" ||
row.Field <string>("F1") == null ? "0" : row.Field <string>("F1").Substring(0, 1)) > 0)
.CopyToDataTable();
return(dt);
}
catch (Exception)
{
return(null);
}
}
void addMaterialModuleLink()
{
string localPath = "D:\\ListDMVT";
string[] listFiles = Directory.GetFiles(localPath);
foreach (string filePath in listFiles)
{
string fileName = Path.GetFileName(filePath);
string moduleCode = fileName.Substring(3, 10);
try
{
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(filePath, "DMVT");
string designer = TextUtils.ToString(dtDMVT.Rows[3]["F3"]);
DataRow[] drs = dtDMVT.AsEnumerable()
.Where(row => TextUtils.ToInt(row.Field <string>("F1") == "" ||
row.Field <string>("F1") == null ? "0" : row.Field <string>("F1").Substring(0, 1)) > 0)
.ToArray();
foreach (DataRow row in drs)
{
MaterialModuleLinkModel link = new MaterialModuleLinkModel();
link.ModuleCode = moduleCode;
link.STT = TextUtils.ToString(row["F1"]);
link.Name = TextUtils.ToString(row["F2"]);
link.ThongSo = TextUtils.ToString(row["F3"]);
link.Code = TextUtils.ToString(row["F4"]);
link.MaVatLieu = TextUtils.ToString(row["F5"]);
link.Unit = TextUtils.ToString(row["F6"]);
link.Qty = TextUtils.ToDecimal(row["F7"]);
link.VatLieu = TextUtils.ToString(row["F8"]);
link.Weight = TextUtils.ToDecimal(row["F9"]);
link.Hang = TextUtils.ToString(row["F10"]);
link.Note = TextUtils.ToString(row["F11"]);
link.Designer = designer;
link.DateCreated = DateTime.Now;
MaterialModuleLinkBO.Instance.Insert(link);
}
}
catch
{
}
}
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
try
{
_dtData = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
_dtData.Columns.Add("ID", typeof(int));
_dtData = _dtData.AsEnumerable()
.Where(row => TextUtils.ToInt(row.Field <string>("F1") == "" ||
row.Field <string>("F1") == null ? "0" : row.Field <string>("F1").Substring(0, 1)) > 0)
.CopyToDataTable();
grdData.DataSource = _dtData;
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
void ChangeModuleName()
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang lấy mã mới"))
{
string tableCodeFilePath = @"\\server\data2\Thietke\ISO\ISO.Thietke\TAI LIEU DAO TAO\TAI LIEU HO TRO PHONG THIET KE\TK09- Huong dan doi ma san pham TK\TK09-BM01 - Bang thay doi ma TK.xlsx";
List <string> listSheet = TextUtils.ListSheetInExcel(tableCodeFilePath);
foreach (string sheetName in listSheet)
{
if (!sheetName.ToUpper().StartsWith("TPAD."))
{
continue;
}
DataTable dt = TextUtils.ExcelToDatatableNoHeader(tableCodeFilePath, sheetName);
foreach (DataRow item in dt.Rows)
{
string newCode = item["F3"] == null ? "" : item["F3"].ToString();
if (newCode == "")
{
continue;
}
ModulesModel model = null;
try
{
model = (ModulesModel)ModulesBO.Instance.FindByCode(newCode);
}
catch (Exception)
{
}
if (model != null)
{
string oldName = item["F5"] == null ? "" : item["F5"].ToString();
string newName = item["F6"] == null ? "" : item["F6"].ToString();
string name = (newName == "" ? oldName : newName);
model.Name = name;
ModulesBO.Instance.Update(model);
}
}
}
}
}
void upateDatePrice()
{
string filePath = @"E:\PROJECTS\TanPhat\KAIZEN\VAT TU DA THEM GIA.xlsx";
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet");
dt = dt.Select("F4 is not null and F4 <> ''").CopyToDataTable();
DataTable dtPart = LibQLSX.Select("select * from Parts with(nolock)");
int count = 0;
foreach (DataRow row in dt.Rows)
{
string partsCode = TextUtils.ToString(row["F4"]);
string MaVatLieu = TextUtils.ToString(row["F5"]);
if (partsCode == "")
{
continue;
}
if (MaVatLieu != "" && MaVatLieu != "GCCX")
{
partsCode = MaVatLieu;
}
DataRow[] drs = dtPart.Select("PartsCode = '" + partsCode.Trim() + "'");
if (drs.Length > 0)
{
string partsId = TextUtils.ToString(drs[0]["PartsId"]);
decimal price = TextUtils.ToDecimal(drs[0]["Price"]);
if (price <= 1)
{
continue;
}
PartsModel part = (PartsModel)PartsBO.Instance.FindByAttribute("PartsId", partsId)[0];
part.UpdatedPriceDate = DateTime.Now;
PartsBO.Instance.UpdateQLSX(part);
count++;
}
}
MessageBox.Show(count.ToString());
}
public DataTable GetDMVT(string filePath)
{
try
{
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet");
dtDMVT = dtDMVT.Select("F1 <> ''").CopyToDataTable();
DataTable dtDMVT1 = dtDMVT.Copy();
dtDMVT1.Rows.Clear();
for (int i = 0; i < dtDMVT.Rows.Count; i++)
{
try
{
if (dtDMVT.Rows[i][0] == null)
{
continue;
}
if (dtDMVT.Rows[i][0].ToString().Trim() == "")
{
continue;
}
string STT = dtDMVT.Rows[i][0].ToString().Trim();
if (TextUtils.ToDecimal(STT.Substring(0, 1)) > 0)
{
dtDMVT1.ImportRow(dtDMVT.Rows[i]);
}
}
catch (Exception)
{
}
}
return(dtDMVT1);
}
catch (Exception)
{
return(null);
}
}
private void btnUpdateDMVT_Click(object sender, EventArgs e)
{
try
{
int id = TextUtils.ToInt(grvData.GetFocusedRowCellValue(colID));
if (id == 0)
{
return;
}
ModulesModel model = (ModulesModel)ModulesBO.Instance.FindByPK(id);
if (model.Status != 2)
{
MessageBox.Show("Module chưa có trên nguồn chuẩn!");
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo danh mục vật tư..."))
{
string _serverPathCK = string.Format("Thietke.Ck/{0}/{1}.Ck/VT.{1}.xlsm",
model.Code.Substring(0, 6), model.Code);
DocUtils.InitFTPQLSX();
if (DocUtils.CheckExits(_serverPathCK))
{
DocUtils.DownloadFile("D:/", "VT." + model.Code + ".xlsm", _serverPathCK);
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader("D:/VT." + model.Code + ".xlsm", "DMVT");
TextUtils.AddDMVTfromModule("D:/VT." + model.Code + ".xlsm");
File.Delete("D:/VT." + model.Code + ".xlsm");
}
}
MessageBox.Show("Cập nhật danh mục vật tư thành công!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Cập nhật danh mục vật tư không thành công!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
grvData.Columns.Clear();
if (chkAutoCheck.Checked)
{
try
{
var tablename = cboSheet.SelectedItem.ToString();
grdData.DataSource = ds; // dataset
grdData.DataMember = tablename;
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
else
{
try
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
grdData.DataSource = dt;
grvData.PopulateColumns();
grvData.BestFitColumns();
grdData.Focus();
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
}
private void btnBrowser_Click(object sender, EventArgs e)
{
FolderBrowserDialog fbd = new FolderBrowserDialog();
fbd.SelectedPath = @"D:\Thietke.Ck\TPAD.D";
if (fbd.ShowDialog() == DialogResult.OK)
{
_modulePath = fbd.SelectedPath;
txtOldModule.Text = Path.GetFileNameWithoutExtension(_modulePath);
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang lấy mã mới"))
{
string tableCodeFilePath = @"\\server\data2\Thietke\ISO\ISO.Thietke\TAI LIEU DAO TAO\TAI LIEU HO TRO PHONG THIET KE\TK09- Huong dan doi ma san pham TK\TK09-BM01 - Bang thay doi ma TK.xlsx";
List <string> listSheet = TextUtils.ListSheetInExcel(tableCodeFilePath);
foreach (string sheetName in listSheet)
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(tableCodeFilePath, sheetName);
try
{
DataRow[] drs = dt.Select("F4 = '" + txtOldModule.Text + "'");
if (drs.Length > 0)
{
string oldName = drs[0]["F5"] == null ? "" : drs[0]["F5"].ToString();
string newName = drs[0]["F6"] == null ? "" : drs[0]["F6"].ToString();
_newModuleName = newName == "" ? oldName : newName;
txtNewModule.Text = drs[0]["F3"].ToString();
break;
}
}
catch (Exception)
{
}
}
}
}
}
private void cboProject_EditValueChanged(object sender, EventArgs e)
{
try
{
string projectCode = TextUtils.ToString(grvProject.GetFocusedRowCellValue(colProjectCode));
txtDateP.Text = TextUtils.ToString(grvProject.GetFocusedRowCellDisplayText(colDateFinishE));
string filePath = @"\\SERVER\data2\Thietke\Luutru\Hoancong\TONG HOP GIA DAU VAO\" + projectCode + ".xls";
if (!File.Exists(filePath))
{
MessageBox.Show("Không tồn tại file tổng hợp giá đầu vào cho dự án [" + projectCode + "].",
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
DataTable dtData = TextUtils.ExcelToDatatableNoHeader(filePath, "Tổng hợp v1");
dtData = dtData.Select("F3 <> ''").CopyToDataTable();
TextUtils.PopulateCombo(cboModuleP, dtData, "F3", "F3", "");
}
catch (Exception)
{
}
}
private void downloadCụmToolStripMenuItem_Click(object sender, EventArgs e)
{
DocUtils.InitFTPQLSX();
string code = TextUtils.ToString(grvData.GetFocusedRowCellValue(colCode));
string oldCode = "";
if (code.Contains("-"))
{
oldCode = code.Split('-')[0];
}
string moduleCode = code.Substring(0, 10);
string pathCkFTP = "Thietke.Ck/" + moduleCode.Substring(0, 6) + "/" + moduleCode + ".Ck/";
string comFtpPath = pathCkFTP + "3D." + moduleCode + "/COM." + moduleCode + "/";
string dmvtFtpPath = pathCkFTP + "VT." + moduleCode + ".xlsm";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang download cụm"))
{
string ftpPathCum = TextUtils.GetPathOfCum(code);
TextUtils.DownloadFtpFolder(ftpPathCum, fbd.SelectedPath);
string parentPath = Path.GetDirectoryName(ftpPathCum);
List <string> listPath = TextUtils.GetListFolderInFtpPath(parentPath);
foreach (string path in listPath)
{
string pathName = Path.GetFileName(path);
if (oldCode == "")
{
if (pathName.StartsWith(code + "-"))
{
TextUtils.DownloadFtpFolder(path, fbd.SelectedPath);
}
}
else
{
if (pathName.StartsWith(oldCode))
{
TextUtils.DownloadFtpFolder(path, fbd.SelectedPath);
}
}
}
DocUtils.DownloadFile(fbd.SelectedPath, Path.GetFileName(dmvtFtpPath), dmvtFtpPath);
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(fbd.SelectedPath + "/" + Path.GetFileName(dmvtFtpPath), "DMVT");
File.Delete(fbd.SelectedPath + "/" + Path.GetFileName(dmvtFtpPath));
DataRow[] drs = dtDMVT.Select("F4 = '" + code + "'");
if (drs.Length > 0)
{
string stt = TextUtils.ToString(drs[0]["F1"]);
DataRow[] drsCum = dtDMVT.Select("F1 like '" + stt + "%' and F4 not like '" + moduleCode + "%'");
foreach (DataRow row in drsCum)
{
string hang = TextUtils.ToString(row["F10"]);
string codeVT = TextUtils.ToString(row["F4"]);
string unit = TextUtils.ToString(row["F6"]);
string localPath = fbd.SelectedPath + "/COM." + moduleCode + "/" + hang;
if (unit == "BỘ" && codeVT.StartsWith("TPAD.") && hang == "TPA")
{
TextUtils.DownloadFtpFolder(comFtpPath + hang + "/" + codeVT, localPath);
}
else
{
List <string> listFilePath = new List <string>();
listFilePath = TextUtils.GetListFileInFtpPath(comFtpPath + hang);
foreach (string item in listFilePath)
{
string itemCode = Path.GetFileNameWithoutExtension(item).Replace(" ", "");
if (itemCode.ToLower() == codeVT.ToLower())
{
DocUtils.DownloadFile(localPath, Path.GetFileName(item), item);
}
}
}
}
}
}
}
}
private void btnImport_Click(object sender, EventArgs e)
{
string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
}
else
{
return;
}
DataTable dt = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet1");
dt.Rows.RemoveAt(0);
dt.Rows.RemoveAt(0);
int count = 0;
DataTable dtError = new DataTable();
dtError.Columns.Add("STT");
dtError.Columns.Add("PartsCode");
dtError.Columns.Add("PartsName");
dtError.Columns.Add("IsPart", typeof(int));
dtError.Columns.Add("IsPartGereral", typeof(int));
DataTable dtPartsGeneral = LibQLSX.Select("select PartsCode from [vPartsGeneral]");
DataTable dtParts = LibQLSX.Select("select PartsCode from [Parts] where PartsCode is not null and PartsCode <> ''");
foreach (DataRow row in dt.Rows)
{
string partsCode = TextUtils.ToString(row["F2"]);
if (partsCode == "")
{
continue;
}
string name = TextUtils.ToString(row["F1"]);
string unit = TextUtils.ToString(row["F4"]);
string hang = TextUtils.ToString(row["F5"]);
decimal qtyMin = TextUtils.ToDecimal(row["F6"]);
decimal price = TextUtils.ToDecimal(row["F7"]);
decimal qty = TextUtils.ToDecimal(row["F9"]);
decimal total = TextUtils.ToDecimal(row["F9"]);
decimal projectPercent = TextUtils.ToDecimal(row["F10"]);
int type = TextUtils.ToInt(row["F11"]);
int isProject = TextUtils.ToInt(row["F12"]);
PartAccessoriesModel model = new PartAccessoriesModel();
model.Code = partsCode;
model.Name = name;
model.Hang = hang;
model.IsProject = isProject;
model.Price = price;
model.ProjectPercent = projectPercent;
model.Qty = qty;
model.QtyDM = 0;
model.QtyMin = qtyMin;
model.Total = total;
model.Type = type;
model.Unit = unit;
PartAccessoriesBO.Instance.Insert(model);
count++;
}
//foreach (DataRow row in dt.Rows)
//{
// string partsCode = TextUtils.ToString(row["F3"]);
// if (partsCode == "") continue;
// DataRow[] drsG = dtPartsGeneral.Select("PartsCode = '" + partsCode + "'");
// DataRow[] drsP = dtParts.Select("PartsCode = '" + partsCode + "'");
// DataRow dr = dtError.NewRow();
// dr["STT"] = TextUtils.ToString(row["F1"]);
// dr["PartsCode"] = partsCode;
// dr["PartsName"] = TextUtils.ToString(row["F2"]);
// dr["IsPart"] = drsP.Length > 0 ? 1 : 0;
// dr["IsPartGereral"] = drsG.Length > 0 ? 1 : 0;
// dtError.Rows.Add(dr);
// count++;
//}
//TextUtils.DatatableToCSV(dtError, "D:\\1.csv");
MessageBox.Show(count.ToString());
LoadInfoSearch();
}
private void thêmNhómVậtTưVàoDMVTToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
string filePath = ofd.FileName;
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...",
"Đang thêm nhóm vật tư vào DMVT..."))
{
Excel.Application objXLApp = default(Excel.Application);
Excel.Workbook objXLWb = default(Excel.Workbook);
Excel.Worksheet objXLWs = default(Excel.Worksheet);
try
{
objXLApp = new Excel.Application();
objXLApp.Workbooks.Open(filePath);
objXLWb = objXLApp.Workbooks[1];
objXLWs = (Excel.Worksheet)objXLWb.Worksheets[1];
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(filePath, "DMVT");
dtDMVT = dtDMVT.AsEnumerable()
.Where(row => TextUtils.ToInt(row.Field <string>("F1") == "" ||
row.Field <string>("F1") == null ? "0" : row.Field <string>("F1").Substring(0, 1)) > 0)
.CopyToDataTable();
//L-12;M-13
for (int i = 0; i < dtDMVT.Rows.Count; i++)
{
string partCode = TextUtils.ToString(dtDMVT.Rows[i][3]);
if (partCode == "" || partCode.StartsWith("TPAD."))
{
continue;
}
DataTable dtGroup =
TextUtils.Select("select * from [vMaterial] with(nolock) where Code = '" + partCode + "'");
string groupCode = "";
string groupName = "";
if (dtGroup.Rows.Count > 0)
{
groupCode = TextUtils.ToString(dtGroup.Rows[0]["MaterialGroupCode"]);
groupName = TextUtils.ToString(dtGroup.Rows[0]["MaterialGroupName"]);
objXLWs.Cells[7 + i, 12] = groupCode;
objXLWs.Cells[7 + i, 13] = groupName;
}
}
}
catch
{
}
finally
{
objXLApp.ActiveWorkbook.Save();
objXLApp.Workbooks.Close();
objXLApp.Quit();
}
}
}
}
void KiemTraVatTu_VatLieu()
{
DataTable dtError = new DataTable();
dtError.Columns.Add("ModuleCode");
dtError.Columns.Add("STT");
dtError.Columns.Add("MaterialCode");
dtError.Columns.Add("MaterialName");
dtError.Columns.Add("MaVatLieu");
dtError.Columns.Add("VatLieu");
dtError.Columns.Add("Error");
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang kiem tra..."))
{
string[] listFilePath = Directory.GetFiles("D:/ListDMVT");
foreach (string filePath in listFilePath)
{
try
{
string moduleCode = Path.GetFileName(filePath).Substring(3, 10);
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(filePath, "DMVT");
var results = from myRow in dtDMVT.AsEnumerable()
where TextUtils.ToDecimal(myRow.Field <string>("F1") != "" && myRow.Field <string>("F1") != null
? myRow.Field <string>("F1").Substring(0, 1) : "") > 0
select myRow;
if (results == null)
{
continue;
}
if (results.Count() == 0)
{
continue;
}
if (results.Count() > 0)
{
dtDMVT = results.CopyToDataTable();
}
for (int i = 0; i <= dtDMVT.Rows.Count - 1; i++)
{
string nameDMVT = dtDMVT.Rows[i]["F4"].ToString();
if (nameDMVT == "")
{
continue;
}
//string hang = dtDMVT.Rows[i][9].ToString();
string MaVatLieu = TextUtils.ToString(dtDMVT.Rows[i]["F5"]).Trim();
string VatLieu = TextUtils.ToString(dtDMVT.Rows[i]["F8"]).Trim();
string stt = TextUtils.ToString(dtDMVT.Rows[i]["F1"]).Trim();
List <string> errorString = new List <string>();
if (MaVatLieu != "")
{
DataTable dtMaVatLieu = LibQLSX.Select("SELECT top 1 PartsCode from Parts with(nolock)"
+ " where PartsCode = N'" + MaVatLieu.Replace(" ", "").Replace("(", "/") + "'");
if (dtMaVatLieu.Rows.Count == 0)
{
errorString.Add("Mã vật liệu không tồn tại trên QLSX");
}
}
if (VatLieu != "" && VatLieu != "-")
{
DataTable dtVatLieu = LibQLSX.Select("SELECT top 1 MaterialsId from MaterialsModel with(nolock)"
+ " where MaterialsId = N'" + VatLieu + "'");
if (dtVatLieu.Rows.Count == 0)
{
errorString.Add("Vật tư không tồn tại trên QLSX");
}
}
if (MaVatLieu != "" && (VatLieu == "" || VatLieu == "-"))
{
errorString.Add("Vật tư không có vật liệu");
}
if (errorString.Count > 0)
{
DataRow dr = dtError.NewRow();
dr["ModuleCode"] = moduleCode;
dr["STT"] = stt;
dr["MaterialCode"] = nameDMVT;
dr["MaterialName"] = TextUtils.ToString(dtDMVT.Rows[i]["F2"]);
dr["MaVatLieu"] = MaVatLieu;
dr["VatLieu"] = VatLieu;
dr["Error"] = string.Join(", ", errorString.ToArray());
dtError.Rows.Add(dr);
}
}
}
catch (Exception)
{
}
}
}
gridControl1.DataSource = dtError;
MessageBox.Show("ok");
}
private void cboSheet_SelectionChangeCommitted(object sender, EventArgs e)
{
grvData.Columns.Clear();
if (chkAutoCheck.Checked)
{
try
{
DataTable dtt = ds.Tables[cboSheet.SelectedIndex];
dtt.Columns.Add("DATEF2", typeof(DateTime));
for (int i = 0; i < dtt.Rows.Count; i++)
{
if (i < 1
)
{
continue;
}
if (string.IsNullOrEmpty(Lib.ToString(dtt.Rows[i]["F1"])))
{
continue;
}
if (Lib.ToString(dtt.Rows[i]["F2"]).Trim() != "")
{
dtt.Rows[i]["DATEF2"] = DateTime.FromOADate(TextUtils.ToDouble(dtt.Rows[i]["F2"]));
}
}
dtt.Columns.Add("DATEF3", typeof(DateTime));
for (int i = 0; i < dtt.Rows.Count; i++)
{
if (i < 1
)
{
continue;
}
if (string.IsNullOrEmpty(Lib.ToString(dtt.Rows[i]["F1"])))
{
continue;
}
if (Lib.ToString(dtt.Rows[i]["F3"]).Trim() != "")
{
dtt.Rows[i]["DATEF3"] = DateTime.FromOADate(TextUtils.ToDouble(dtt.Rows[i]["F3"]));
}
}
grdData.DataSource = dtt;
grvData.PopulateColumns();
grvData.BestFitColumns();
grdData.Focus();
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
else
{
try
{
DataTable dt = TextUtils.ExcelToDatatableNoHeader(btnBrowse.Text, cboSheet.SelectedValue.ToString());
// thêm 1 cột date mới
dt.Columns.Add("DATEF2", typeof(DateTime));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < 1
)
{
continue;
}
if (string.IsNullOrEmpty(Lib.ToString(dt.Rows[i]["F1"])))
{
continue;
}
if (Lib.ToString(dt.Rows[i]["F2"]).Trim() != "")
{
dt.Rows[i]["DATEF2"] = DateTime.FromOADate(TextUtils.ToDouble(dt.Rows[i]["F2"]));
}
}
dt.Columns.Add("DATEF3", typeof(DateTime));
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < 1
)
{
continue;
}
if (string.IsNullOrEmpty(Lib.ToString(dt.Rows[i]["F1"])))
{
continue;
}
if (Lib.ToString(dt.Rows[i]["F3"]).Trim() != "")
{
dt.Rows[i]["DATEF3"] = DateTime.FromOADate(TextUtils.ToDouble(dt.Rows[i]["F3"]));
}
}
grdData.DataSource = dt;
grvData.PopulateColumns();
grvData.BestFitColumns();
grdData.Focus();
//grvData.Columns[3].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
//grvData.Columns[3].DisplayFormat.FormatString = "dd/MM/yyyy";
}
catch (Exception ex)
{
TextUtils.ShowError(ex);
grdData.DataSource = null;
}
}
}
void KiemTraVatTu()
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang kiem tra..."))
{
string[] listFilePath = Directory.GetFiles("D:/ListDMVT");
foreach (string filePath in listFilePath)
{
try
{
string moduleCode = Path.GetFileName(filePath).Substring(3, 10);
DataTable dtDMVT = TextUtils.ExcelToDatatableNoHeader(filePath, "DMVT");
var results = from myRow in dtDMVT.AsEnumerable()
where TextUtils.ToDecimal(myRow.Field <string>("F1")) > 0 &&
!(myRow.Field <string>("F4")).StartsWith("TPAD") &&
!(myRow.Field <string>("F4")).StartsWith("PCB")
select myRow;
if (results.Count() == 0)
{
continue;
}
dtDMVT = results.CopyToDataTable();
for (int i = 0; i <= dtDMVT.Rows.Count - 1; i++)
{
string nameDMVT = dtDMVT.Rows[i][3].ToString();
if (nameDMVT == "")
{
continue;
}
string hang = dtDMVT.Rows[i][9].ToString();
List <string> errorString = new List <string>();
#region Kiem tra hang co hop le
DataTable dtGroup = TextUtils.Select("select CustomerCode from vMaterialCustomer a where replace(a.Code,' ','') ='" + nameDMVT.Replace(" ", "").Replace("(", "/") + "'");
if (dtGroup.Rows.Count > 0)
{
DataRow[] drsCustomer = dtGroup.Select("CustomerCode = '" + hang + "'");
if (drsCustomer.Count() == 0)
{
errorString.Add("Hãng không được sử dụng");
}
}
#endregion
#region Vật tư ngừng sử dụng
DataTable dtViewMaterial = TextUtils.Select("SELECT MaterialGroupCode FROM [vMaterial] with (nolock) where replace(Code,' ','') = '" + nameDMVT.Replace(" ", "").Replace(")", "/") + "'");
if (dtViewMaterial.Rows.Count > 0)
{
string materialGroupCode = dtViewMaterial.Rows[0][0].ToString();
if (materialGroupCode == "TPAVT.Z01")
{
errorString.Add("Vật tư ngừng sử dụng");
}
}
#endregion
#region Vật tư tạm dừng sử dụng
DataTable dtMaterialCSDL = TextUtils.Select("SELECT * FROM [Material] with (nolock) where [StopStatus] = 1 and replace(Code,' ','') = '" + nameDMVT.Replace(" ", "").Replace(")", "/") + "'");
if (dtMaterialCSDL.Rows.Count > 0)
{
errorString.Add("Vật tư tạm dừng sử dụng");
}
#endregion
#region Kiểm tra trên quản lý sản xuất
//Kiem tra xem vat tu co trong kho chua
DataTable dtMaterialQLSX = LibQLSX.Select("SELECT top 1 p.PartsCode,m.ManufacturerCode"
+ " FROM Manufacturer m RIGHT OUTER JOIN"
+ " PartsManufacturer pm ON m.ManufacturerId = pm.ManufacturerId RIGHT OUTER JOIN"
+ " Parts p ON pm.PartsId = p.PartsId"
+ " where p.PartsCode = '" + nameDMVT.Replace(" ", "").Replace("(", "/") + "'");
if (dtMaterialQLSX.Rows.Count == 0)
{
errorString.Add("Vật tư không tồn tại");
}
else
{
if (dtMaterialQLSX.Rows[0]["ManufacturerCode"].ToString().ToUpper() != hang.ToUpper())
{
errorString.Add("Hãng khác với hãng trên QLSX (" + hang + " - " + dtMaterialQLSX.Rows[0]["ManufacturerCode"].ToString() + ")");
}
}
#endregion
if (errorString.Count > 0)
{
TestModel model = new TestModel();
model.ModuleCode = moduleCode;
model.MaterialCode = nameDMVT;
model.MaterialName = dtDMVT.Rows[i]["F2"].ToString();
model.Hang = hang;
model.Error = string.Join(", ", errorString.ToArray());
TestBO.Instance.Insert(model);
}
}
}
catch (Exception)
{
}
}
}
MessageBox.Show("ok");
}
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang load danh sách module..."))
{
_count = 0;
_dtModule = TextUtils.ExcelToDatatableNoHeader(ofd.FileName, "3");
if (_dtModule.Rows.Count == 0)
{
return;
}
DataRow[] drs = _dtModule.Select("F5 like 'TPAD%' and len(F5) = 10 and F12 <> 'VTP'");
DataRow[] drsVTP = _dtModule.Select("F4 like 'TB.%'");
if (drs.Length > 0)
{
_dtModule = drs.CopyToDataTable();
_dtModule.Columns.Add("ID", typeof(int));
_dtModule.Columns.Add("ParentID", typeof(int));
_dtModule.Columns.Add("TypeId");
int rowCount = _dtModule.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
string moduleCode = TextUtils.ToString(_dtModule.Rows[i]["F5"]);
if (i == 0)
{
_count = 1;
}
else
{
_count++;
}
_dtModule.Rows[i]["ID"] = _count;
_dtModule.Rows[i]["ParentID"] = 0;
loadChildModule(moduleCode, _count);
}
if (drsVTP.Length > 0)
{
DataRow r = _dtModule.NewRow();
r["F3"] = TextUtils.ToString(drsVTP[0]["F3"]);
r["F4"] = TextUtils.ToString(drsVTP[0]["F4"]);
r["F5"] = TextUtils.ToString(drsVTP[0]["F4"]);
r["F7"] = 1.ToString();
_count++;
r["ID"] = _count;
if (rowCount == 1)
{
r["ParentID"] = TextUtils.ToString(_dtModule.Rows[0]["ID"]);
}
else
{
r["ParentID"] = 0;
}
_dtModule.Rows.Add(r);
}
grvModule.DataSource = null;
grvModule.AutoGenerateColumns = false;
grvModule.DataSource = _dtModule;
}
}
}
}
private void btnCreateTHTL_Click(object sender, EventArgs e)
{
if (txtFilePathTHTK.Text.Trim() == "")
{
return;
}
DataTable dt = TextUtils.ExcelToDatatableNoHeader(txtFilePathTHTK.Text.Trim(), "3");
string khCuoi = TextUtils.ToString(dt.Rows[2]["F1"]);
string duAn = TextUtils.ToString(dt.Rows[2]["F8"]);
string HangMuc = TextUtils.ToString(dt.Rows[5]["F12"]);
string spHD = TextUtils.ToString(dt.Rows[5]["F1"]);
string maSPHD = TextUtils.ToString(dt.Rows[5]["F8"]);
//DataRow[] drs = dt.Select("F5 like 'TPAD.%' and F13 not like 'VTP%'");
DataRow[] drs = dt.Select("F5 like 'TPAD.%' and (F13 <> 'VTP' or F13 is null)");
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\THTL - Muc " + HangMuc.Replace(":", "") + " - " + duAn + ".xlsm";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKyThuat\\THTL.xlsm";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở.");
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo biểu mẫu..."))
{
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application app = default(Excel.Application);
Excel.Workbook workBoook = default(Excel.Workbook);
Excel.Worksheet workSheet = default(Excel.Worksheet);
try
{
app = new Excel.Application();
app.Workbooks.Open(localPath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
workSheet.Cells[4, 1] = khCuoi;
workSheet.Cells[4, 5] = "Dự án: " + duAn;
workSheet.Cells[4, 6] = HangMuc;
workSheet.Cells[5, 1] = spHD;
workSheet.Cells[5, 5] = maSPHD;
workSheet.Cells[19, 6] = "Hà Nội, Ngày " + string.Format("{0:00}", DateTime.Now.Day)
+ " tháng " + string.Format("{0:00}", DateTime.Now.Month)
+ " năm " + DateTime.Now.Year;
for (int i = drs.Length - 1; i >= 0; i--)
{
((Excel.Range)workSheet.Rows[10]).Insert();
string code = TextUtils.ToString(drs[i]["F5"]);
workSheet.Cells[10, 1] = i + 1;
workSheet.Cells[10, 2] = TextUtils.ToString(drs[i]["F2"]).ToUpper();
workSheet.Cells[10, 3] = TextUtils.ToString(drs[i]["F3"]).ToUpper();
workSheet.Cells[10, 4] = TextUtils.ToString(drs[i]["F4"]).ToUpper();
workSheet.Cells[10, 5] = code.ToUpper();
workSheet.Cells[10, 6] = "D0.00." + code.Substring(5, 5).ToUpper();
workSheet.Cells[10, 7] = TextUtils.ToString(drs[i]["F13"]);
//workSheet.Cells[10, 7] = TextUtils.ToString(drs[i][""]);
}
((Excel.Range)workSheet.Rows[9]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
}
Process.Start(localPath);
}