protected vDMVTCFacade(vDMVTCModel model) : base(model)
{
}
void CreateExcel()
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo biểu mẫu danh mục vật tư chính..."))
{
ModulesModel thisModule = (ModulesModel)(ModulesBO.Instance.FindByAttribute("Code", cboModule.EditValue.ToString()))[0];
int moduleID = thisModule.ID;
if (moduleID == 0)
{
return;
}
ArrayList listModuleHistory = vDMVTCBO.Instance.FindByAttribute("ModuleID", moduleID);
if (listModuleHistory.Count <= 0)
{
return;
}
DataTable dt = TextUtils.Select("SELECT top 1 (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],'@')) as countTS "
+ " FROM [QLKHCV].[dbo].[MaterialHistory] with(nolock) where ModuleID = " + moduleID
+ " order by (select COUNT(*) from [dbo].[fnStringToTable]([NameTS],'@')) desc");
int numberTT = TextUtils.ToInt(dt.Rows[0][0]);
if (numberTT == 0)
{
return;
}
vDMVTCModel mModel = (vDMVTCModel)listModuleHistory[0];
string productName = mModel.ProductName;
string productCode = mModel.ProductCode;
string projectName = "";
string projectCode = "";
string projectDateEnd = "";
string projectID = cboProject.Properties.View.GetFocusedRowCellValue(colProjectID) != null
? cboProject.Properties.View.GetFocusedRowCellValue(colProjectID).ToString() : "";
if (projectID != "")
{
projectName = cboProject.Properties.View.GetFocusedRowCellValue(colProjectName).ToString();
projectCode = cboProject.Properties.View.GetFocusedRowCellValue(colProjectCode).ToString();
projectDateEnd = cboProject.Properties.View.GetFocusedRowCellDisplayText(colDateEnd).ToString();
}
string localPath = @"D:/Thietke.Ck/" + productCode.Substring(0, 6) + "/" + productCode + ".Ck/DOC." + productCode + "/";
string filePath = Application.StartupPath + "/Templates/DMVTC.xlsm";
if (!Directory.Exists(localPath))
{
Directory.CreateDirectory(localPath);
}
File.Copy(filePath, localPath + "DMVTC." + productCode + ".xlsm", true);
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 + "DMVTC." + productCode + ".xlsm");
workBoook = app.Workbooks[1];
workSheet = (Excel.Worksheet)workBoook.Worksheets[1];
//chi tiết dự án
workSheet.Cells[4, 4] = productCode;
workSheet.Cells[5, 4] = productName;
workSheet.Cells[6, 4] = projectCode;
workSheet.Cells[7, 4] = projectName.ToUpper();
DataTable dtHistory = TextUtils.Select("vDMVTC", new Expression("ModuleID", moduleID));
workSheet.Cells[4, 19] = dtHistory.Compute("Sum(Total)", "");
workSheet.Cells[5, 19] = dtHistory.Compute("Max(Delivery)", "");
workSheet.Cells[6, 19] = projectDateEnd;
workSheet.Cells[15, 20] = "Tân Phát, ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;
workSheet.PageSetup.RightHeader = "Người sửa đổi: " + Global.AppFullName;
//nếu như vượt quá 14 thông số thì thêm cột
if (numberTT > 14)
{
for (int i = 0; i < numberTT - 14; i++)
{
Excel.Range oRng = workSheet.Range["Y1"];
oRng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
}
}
for (int i = 0; i < listModuleHistory.Count; i++)
{
vDMVTCModel hModel = (vDMVTCModel)listModuleHistory[i];
for (int j = 0; j <= 3; j++)
{
((Excel.Range)workSheet.Rows[13]).Insert();
}
//Copy and Past file
Excel.Range rCopy = workSheet.Range["A10", "AZ12"];
rCopy.Copy();
Excel.Range rPaste = workSheet.Range["A14", "AZ16"];
rPaste.PasteSpecial(Excel.XlPasteType.xlPasteAll);
workSheet.Cells[14, 1] = listModuleHistory.Count - i; //stt
workSheet.Cells[14, 2] = hModel.Code; //Mã vật tư
workSheet.Cells[14, 3] = hModel.Name; //Tên vật tư
workSheet.Cells[14, 4] = hModel.Hang; //Hãng
workSheet.Cells[14, 5] = hModel.ThoiGianGHCuoi.ToString("dd/MM/yyyy"); //Thời gian GH cuối
workSheet.Cells[14, 6] = hModel.Delivery; //Thời gian giao hàng(ngày)
workSheet.Cells[14, 7] = hModel.Unit; //Đơn vị tính
workSheet.Cells[14, 8] = hModel.Qty; //Số lượng
workSheet.Cells[14, 9] = hModel.Price; //Đơn giá
workSheet.Cells[14, 10] = hModel.Qty * hModel.Price; //Thành tiền
string[] arrTT = hModel.NameTS.Split('@');
string[] arrValueTT = hModel.ValueTT.Split('@');
string[] arrValueYC = hModel.ValueYC.Split('@');
for (int t = 0; t < arrTT.Count(); t++)
{
workSheet.Cells[14, 12 + t] = arrTT[t];
workSheet.Cells[15, 12 + t] = arrValueTT[t];
workSheet.Cells[16, 12 + t] = arrValueYC[t];
}
}
for (int i = 0; i < 4; i++)
{
((Excel.Range)workSheet.Rows[10]).Delete();
}
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
Process.Start(localPath + "DMVTC." + productCode + ".xlsm");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
TextUtils.ReleaseComObject(app);
TextUtils.ReleaseComObject(workBoook);
TextUtils.ReleaseComObject(workSheet);
}
}
}