void exportIn(string selectFolder)
{
string fileSourcePath = Application.StartupPath + "\\Templates\\PhongSXLR\\ChiThi.xls";
string localPath = selectFolder + "/" + _projectDirection.ProjectCode + ".CTSX." + _projectDirection.ID + "/" + _projectDirection.ProjectCode + "-CTSX.IN";
Directory.CreateDirectory(localPath);
//string filePath = localPath + "/Chi_Thi_In.xls";
string filePath = localPath + "/" + _projectDirection.ProjectCode + "-CTSX.IN.xls";
try
{
File.Copy(fileSourcePath, filePath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
DocUtils.InitFTPQLSX();
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo chỉ thị IN..."))
{
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(filePath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
workSheet.Cells[5, 3] = _projectDirection.ID;
workSheet.Cells[2, 7] = _projectDirection.ProjectCode;
workSheet.Cells[4, 2] = "PHÒNG/ TỔ: CNC";
DataRow[] drsIn = _dtData.Select("ProjectDirectionTypeID = 2 and IsDeleted = 0", "PartsCode ASC");
for (int i = 0; i < drsIn.Length; i++)
{
string moduleCode = TextUtils.ToString(drsIn[i]["ModuleCode"]);
string moduleGroup = moduleCode.Substring(0, 6);
//string serverPathMat = string.Format(@"/Thietke.Ck/{0}/{1}.Ck/MAT.{1}", moduleGroup, moduleCode);
//string serverPathCad = string.Format(@"/Thietke.Ck/{0}/{1}.Ck/CAD.{1}", moduleGroup, moduleCode);
string stt = TextUtils.ToString(drsIn[i]["STT"]);
string partsCode = TextUtils.ToString(drsIn[i]["PartsCode"]);
string partsName = TextUtils.ToString(drsIn[i]["PartsName"]);
decimal qty = TextUtils.ToDecimal(drsIn[i]["Qty"]);
decimal makeTime = TextUtils.ToDecimal(drsIn[i]["MakeTime"]);
string userName = TextUtils.ToString(drsIn[i]["UserName"]);
DateTime?startDateDK = TextUtils.ToDate2(drsIn[i]["StartDateDK"]);
DateTime?endDateDK = TextUtils.ToDate2(drsIn[i]["EndDateDK"]);
string vatLieu = TextUtils.ToString(drsIn[i]["Material"]);
//string fileName = TextUtils.ToString(drsIn[i]["FileName"]);
string filePath1 = TextUtils.ToString(drsIn[i]["FilePath"]);
string note = TextUtils.ToString(drsIn[i]["Note"]);
DocUtils.DownloadFile(localPath, Path.GetFileName(filePath1), filePath1);
workSheet.Cells[10, 1] = stt;
workSheet.Cells[10, 2] = partsName;
workSheet.Cells[10, 3] = partsCode;
workSheet.Cells[10, 4] = moduleCode;
workSheet.Cells[10, 5] = qty;
workSheet.Cells[10, 6] = makeTime;
workSheet.Cells[10, 8] = userName;
workSheet.Cells[10, 9] = startDateDK != null?startDateDK.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[10, 10] = endDateDK != null?endDateDK.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[10, 18] = vatLieu;
workSheet.Cells[10, 24] = note;
if (note != "")
{
((Excel.Range)workSheet.Rows[10]).Interior.Color = Excel.XlRgbColor.rgbLightBlue;
}
((Excel.Range)workSheet.Rows[10]).Insert();
}
((Excel.Range)workSheet.Rows[9]).Delete();
((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();
}
}
}
}
void exportLRCN(string selectFolder)
{
string fileSourcePath = Application.StartupPath + "\\Templates\\PhongSXLR\\ChiThi.xls";
string localPath = selectFolder + "/" + _projectDirection.ProjectCode + ".CTSX." + _projectDirection.ID + "/" + _projectDirection.ProjectCode + "-CTSX.LR";
Directory.CreateDirectory(localPath);
string filePath = localPath + "/" + _projectDirection.ProjectCode + "-CTSX.LR.xls";
try
{
File.Copy(fileSourcePath, filePath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
DocUtils.InitFTPQLSX();
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo chỉ thị LR..."))
{
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(filePath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
workSheet.Cells[5, 3] = _projectDirection.ID;
workSheet.Cells[2, 7] = _projectDirection.ProjectCode;
workSheet.Cells[4, 2] = "PHÒNG/ TỔ: Lắp ráp";
#region Download file
DataTable dtModule = TextUtils.GetDistinctDatatable(_dtData, "ModuleCode");
foreach (DataRow item in dtModule.Rows)
{
string moduleCode = TextUtils.ToString(item["ModuleCode"]);
string moduleGroup = moduleCode.Substring(0, 6);
DataTable dtStructureFile = TextUtils.Select("select * from vPBDL_Structure_File where [PBDLStructureID] in (13,15)");
DataTable dtDMVT = LibQLSX.Select("select * from MaterialModuleLink where ModuleCode = '" + moduleCode + "'");
foreach (DataRow row in dtStructureFile.Rows)
{
string pathServer = TextUtils.ToString(row["FolderContain"]).Replace("group", moduleGroup).Replace("code", moduleCode);
string folderName = localPath + '/' + moduleCode + "/" + TextUtils.ToString(row["Name"]);
string filterDonVi = TextUtils.ToString(row["FilterDonVi"]);
string extension = TextUtils.ToString(row["Extension"]);
int getType = TextUtils.ToInt(row["GetType"]);
Directory.CreateDirectory(folderName);
if (getType == 1)
{
if (DocUtils.CheckExits(pathServer))
{
DocUtils.DownloadFile(folderName, Path.GetFileName(pathServer), pathServer);
}
}
else
{
DataRow[] listDonVi = dtDMVT.Select("Unit = '" + filterDonVi + "'");
foreach (DataRow rowMVL in listDonVi)
{
string fileName = TextUtils.ToString(rowMVL["Code"]);
try
{
if (DocUtils.CheckExits(pathServer + "/" + fileName + extension))
{
DocUtils.DownloadFile(folderName, fileName + extension, pathServer + "/" + fileName + extension);
}
}
catch
{
}
}
}
}
}
#endregion
#region Export Excel
DataRow[] drsIn = _dtData.Select("ProjectDirectionTypeID > 4 and IsDeleted = 0", "PartsCode ASC");
for (int i = 0; i < drsIn.Length; i++)
{
string moduleCode1 = TextUtils.ToString(drsIn[i]["ModuleCode"]);
string moduleGroup1 = moduleCode1.Substring(0, 6);
//string cadPathServer = string.Format("Thietke.Ck/{0}/{1}.Ck/CAD.{1}", moduleGroup, moduleCode);
//string cadPathServerBC = string.Format("Thietke.Ck/{0}/{1}.Ck/BCCk.{1}/BC-CAD.{1}", moduleGroup, moduleCode);
string stt = TextUtils.ToString(drsIn[i]["STT"]);
string partsCode = TextUtils.ToString(drsIn[i]["PartsCode"]);
string partsName = TextUtils.ToString(drsIn[i]["PartsName"]);
decimal qty = TextUtils.ToDecimal(drsIn[i]["Qty"]);
decimal makeTime = TextUtils.ToDecimal(drsIn[i]["MakeTime"]);
string userName = TextUtils.ToString(drsIn[i]["UserName"]);
DateTime?startDateDK = TextUtils.ToDate2(drsIn[i]["StartDateDK"]);
DateTime?endDateDK = TextUtils.ToDate2(drsIn[i]["EndDateDK"]);
string vatLieu = TextUtils.ToString(drsIn[i]["Material"]);
string note = TextUtils.ToString(drsIn[i]["Note"]);
workSheet.Cells[10, 1] = stt;
workSheet.Cells[10, 2] = partsName;
workSheet.Cells[10, 3] = partsCode;
workSheet.Cells[10, 4] = moduleCode1;
workSheet.Cells[10, 5] = qty;
workSheet.Cells[10, 6] = makeTime;
//workSheet.Cells[10, 6] = stt;
workSheet.Cells[10, 8] = userName;
workSheet.Cells[10, 9] = startDateDK != null?startDateDK.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[10, 10] = endDateDK != null?endDateDK.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[10, 18] = vatLieu;
workSheet.Cells[10, 24] = note;
//workSheet.Cells[10, 18] = moduleCode;
if (note != "")
{
((Excel.Range)workSheet.Rows[10]).Interior.Color = Excel.XlRgbColor.rgbLightBlue;
}
((Excel.Range)workSheet.Rows[10]).Insert();
}
((Excel.Range)workSheet.Rows[9]).Delete();
((Excel.Range)workSheet.Rows[9]).Delete();
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
}
}
private void btnExecl_Click(object sender, EventArgs e)
{
if (cboProject.EditValue == null)
{
MessageBox.Show("Bạn phải chọn một dự án.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
string projectCode = TextUtils.ToString(grvProject.GetFocusedRowCellValue(colProjectCode));
string path = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
path = fbd.SelectedPath;
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKeToan\\KIEM TRA HOA DON.xls";
string currentPath = path + "\\KIEM TRA HOA DON - " + projectCode + ".xls";
try
{
File.Copy(filePath, currentPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi xuất excel!" + Environment.NewLine + ex.Message,
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
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(currentPath);
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
workSheet.Cells[2, 3] = projectCode;
//DataTable dtItem = LibQLSX.Select("select * from vPaymentTableItem with(nolock) where PaymentTableID = " + PaymentTable.ID);
//DataTable dtSupplier = LibQLSX.Select("select Distinct SupplierCode from vPaymentTableItem with(nolock) where PaymentTableID = " + PaymentTable.ID);
for (int i = grvData.RowCount - 1; i >= 0; i--)
{
string orderCode = TextUtils.ToString(grvData.GetRowCellValue(i, colOrderCode));
string sql = "SELECT T_XNTC.C_SOPHIEU,[C_DIENGIAI] ,T_XNTC.C_NGAYLAP, T_XNTC.C_NGAYCT, T_XNTC.C_SOCT, T_XNTC.C_PSNO, T_XNTC.C_PSCO, T_DM_VUVIEC.C_MA" +
",InvoiceNumber = (CASE WHEN [C_DIENGIAI2] IS NULL OR [C_DIENGIAI2] = '' THEN (case when [C_SOHDGTGT] IS NULL OR [C_SOHDGTGT] = '' THEN [C_DIENGIAI] ELSE [C_SOHDGTGT] END) ELSE [C_DIENGIAI2] END)" +
" FROM T_XNTC INNER JOIN T_DM_VUVIEC ON T_XNTC.FK_VUVIEC = T_DM_VUVIEC.PK_ID" +
" WHERE (T_DM_VUVIEC.C_MA = '" + orderCode + "') AND (T_XNTC.FK_TKNO LIKE '133%')";
DataTable dt = LibIE.Select(sql);
decimal total = 0;
for (int j = dt.Rows.Count - 1; j >= 0; j--)
{
DateTime?date = TextUtils.ToDate2(dt.Rows[j]["C_NGAYCT"]);
workSheet.Cells[7, 9] = TextUtils.ToString(dt.Rows[j]["InvoiceNumber"]);
workSheet.Cells[7, 10] = date != null?date.Value.ToString("dd/MM/yyyy") : "";
workSheet.Cells[7, 11] = TextUtils.ToString(dt.Rows[j]["C_PSNO"]);
total += TextUtils.ToDecimal(dt.Rows[j]["C_PSNO"]);
((Excel.Range)workSheet.Rows[7]).Insert();
}
decimal chenhLech = TextUtils.ToDecimal(grvData.GetRowCellValue(i, colChenhLech));
workSheet.Cells[7, 1] = i + 1;
workSheet.Cells[7, 2] = TextUtils.ToString(grvData.GetRowCellValue(i, colOrderCode));
workSheet.Cells[7, 3] = TextUtils.ToString(grvData.GetRowCellValue(i, colNCC));
workSheet.Cells[7, 4] = TextUtils.ToString(grvData.GetRowCellValue(i, colNguoiPhuTrach));
workSheet.Cells[7, 5] = TextUtils.ToString(grvData.GetRowCellValue(i, colTienHang));
workSheet.Cells[7, 6] = TextUtils.ToString(grvData.GetRowCellValue(i, colVAT));
workSheet.Cells[7, 7] = total;
workSheet.Cells[7, 8] = TextUtils.ToString(grvData.GetRowCellValue(i, colChenhLech));
((Excel.Range)workSheet.Rows[7]).Font.Bold = true;
//((Excel.Range)workSheet.Rows[7]).Font.Size = 10;
if (chenhLech > 0)
{
((Excel.Range)workSheet.Cells[7, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);// Color.Yellow;
}
if (chenhLech < 0)
{
((Excel.Range)workSheet.Cells[7, 8]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); //Color.Red;
}
//workSheet.Cells[7, 8].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightYellow);
((Excel.Range)workSheet.Rows[7]).Insert();
}
((Excel.Range)workSheet.Rows[6]).Delete();
((Excel.Range)workSheet.Rows[6]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(currentPath);
}
}
void save(bool close)
{
ProcessTransaction pt = new ProcessTransaction();
pt.OpenConnection();
pt.BeginTransaction();
try
{
if (!ValidateForm())
{
return;
}
grvDebt.FocusedRowHandle = -1;
#region Vụ việc
Case.Name = txtName.Text.Trim().ToUpper();
Case.Code = txtCode.Text.Trim().ToUpper();
Case.Content = txtContent.Text.Trim();
Case.UserId = TextUtils.ToString(cboNguoiPhuTrach.EditValue);
Case.Target = txtTarget.Text.Trim();
Case.IsLocked = false;
Case.Note = txtNote.Text.Trim();
if (Case.ID == 0)
{
Case.CreatedBy = Global.AppUserName;
Case.ID = (int)pt.Insert(Case);
}
else
{
Case.UpdatedBy = Global.AppUserName;
pt.Update(Case);
}
#endregion
#region Chứng từ vụ việc
for (int i = 0; i < grvDebt.RowCount; i++)
{
int id = TextUtils.ToInt(grvDebt.GetRowCellValue(i, colDebtID));
CaseVoucherDebtModel link = new CaseVoucherDebtModel();
if (id > 0)
{
link = (CaseVoucherDebtModel)CaseVoucherDebtBO.Instance.FindByPK(id);
}
link.PayVoucherID = TextUtils.ToInt(grvDebt.GetRowCellValue(i, colDebtVoucherID));
link.CaseID = Case.ID;
link.CompletedDateDK = TextUtils.ToDate2(grvDebt.GetRowCellValue(i, colDebtCompletedDateDK));
link.CompletedDate = null;
if (id > 0)
{
pt.Update(link);
}
else
{
pt.Insert(link);
}
}
#endregion
pt.CommitTransaction();
loadDebt();
_isSaved = true;
if (close)
{
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("Lưu trữ thành công!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
pt.CloseConnection();
}
if (_isSaved)
{
if (this.LoadDataChange != null)
{
this.LoadDataChange(null, null);
}
}
}
void loadData()
{
removeColumn();
DateTime from = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, dtpFromDate.Value.Day, 0, 0, 0);
DateTime to = new DateTime(dtpEndDate.Value.Year, dtpEndDate.Value.Month, dtpEndDate.Value.Day, 23, 59, 59);
if (txtFromOrder.Text.Trim() != "" && txtToOrder.Text.Trim() != "")
{
string sql = @"SELECT TOP 1 DateLR
FROM dbo.ProductCheckHistoryDetail
WHERE OrderCode LIKE '%{0}%' ORDER BY DateLR";
DateTime?date1 = TextUtils.ToDate2(LibIE.ExcuteScalar(string.Format(sql, txtFromOrder.Text.Trim())));
DateTime?date2 = TextUtils.ToDate2(LibIE.ExcuteScalar(string.Format(sql, txtToOrder.Text.Trim())));
if (date1.HasValue && date2.HasValue)
{
if (date2.Value >= date1.Value)
{
from = new DateTime(date1.Value.Year, date1.Value.Month, date1.Value.Day, 0, 0, 0);
to = new DateTime(date2.Value.Year, date2.Value.Month, date2.Value.Day, 0, 0, 0);
}
else
{
to = new DateTime(date1.Value.Year, date1.Value.Month, date1.Value.Day, 0, 0, 0);
from = new DateTime(date2.Value.Year, date2.Value.Month, date2.Value.Day, 0, 0, 0);
}
}
}
List <string> listColumnText = new List <string>();
if (txtFindText.Text.Trim() == "")
{
return;
}
string[] arr = txtFindText.Text.Trim().Split(';');
for (int i = 0; i < arr.Length; i++)
{
listColumnText.Add(string.Format("[{0}]", arr[i]));
grvData.Columns[i + 1].VisibleIndex = i + 1;
grvData.Columns[i + 1].Caption = arr[i];
grvData.Columns[i + 1].FieldName = arr[i];
grvData.Columns[i + 1].Width = 200;
}
DataTable dt = LibIE.LoadDataFromSP("spThongKePhanTichData", "A"
, new string[] { "@From", "@To", "@FistColumnText" }
, new object[] { from, to, string.Join(",", listColumnText) });
//DataColumn c = new DataColumn();
//c.Caption = "STT";
//c.ColumnName = "STT";
//c.DataType = typeof(int);
//c.AutoIncrement = true;
//c.AutoIncrementSeed = 1;
//c.AutoIncrementStep = 1;
//c.AllowDBNull = false;
////c.Unique = true;
//dt.Columns.Add(c);
//dt.Columns["STT"].SetOrdinal(0);
grdData.DataSource = dt;
}