private void btnNN_Click(object sender, EventArgs e)
{
DataTable dtNN = TextUtils.GetDistinctDatatable(dt, "F141");
dtNN.Rows.RemoveAt(0);
dtNN.Rows.RemoveAt(0);
foreach (DataRow r in dtNN.Rows)
{
ModuleErrorTypeModel model = new ModuleErrorTypeModel();
model.Name = r["F141"].ToString();
model.Type = 1;
ModuleErrorTypeBO.Instance.Insert(model);
}
}
private void btnFCM_Click(object sender, EventArgs e)
{
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\FCM - " + thisQuotation.Code + ".xlsx";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo giá..."))
{
DataTable dtQuotation = LibQLSX.Select("select * from vC_Quotation where ID = " + thisQuotation.ID);
DataTable dtAllCost = LibQLSX.Select("exec spReportAllCostWithQuotation " + thisQuotation.ID);
DataTable dtGroupP = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCodeP");
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[3, 4] = TextUtils.ToString(dtQuotation.Rows[0]["Name"]);
workSheet.Cells[4, 4] = "";
//workSheet.Cells[5, 4] = TextUtils.ToString(thisQuotation.Code);
workSheet.Cells[5, 4] = TextUtils.ToString(thisQuotation.ProjectCode);
workSheet.Cells[6, 4] = TextUtils.ToString(thisQuotation.ProjectName);
workSheet.Cells[7, 4] = TextUtils.ToString(thisQuotation.CustomerName);
workSheet.Cells[8, 4] = TextUtils.ToString(thisQuotation.CustomerName);
workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["Name"]);
workSheet.Cells[4, 6] = thisQuotation.DeliveryTime;
workSheet.Cells[8, 7] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]);
workSheet.Cells[9, 7] = TextUtils.ToString(thisQuotation.POnumber);
workSheet.Cells[13, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue);
workSheet.Cells[14, 7] = TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[15, 7] = TextUtils.ToDecimal(txtTax.EditValue);
workSheet.Cells[16, 7] = TextUtils.ToDecimal(txtTotalReal.EditValue);
workSheet.Cells[17, 7] = TextUtils.ToDecimal(txtTotalTrienKhai.EditValue);
workSheet.Cells[18, 7] = TextUtils.ToDecimal(txtTotalNC.EditValue);
workSheet.Cells[19, 7] = TextUtils.ToDecimal(txtTotalPB.EditValue);
workSheet.Cells[20, 7] = TextUtils.ToDecimal(txtTotalBX.EditValue);
workSheet.Cells[21, 7] = TextUtils.ToDecimal(txtCustomerValue.EditValue);
workSheet.Cells[22, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue);
workSheet.Cells[23, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue);
workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalProfitHD.EditValue);
workSheet.Cells[26, 7] = TextUtils.ToDecimal(txtTotalVT.EditValue);
for (int i = dtGroupP.Rows.Count - 1; i >= 0; i--)
{
string groupCodeP = TextUtils.ToString(dtGroupP.Rows[i]["GroupCodeP"]);
DataRow[] listGroupC = dtAllCost.AsEnumerable()
.Where(y => y.Field <string>("GroupCodeP") == groupCodeP)
.GroupBy(r => r.Field <string>("GroupCode"))
.Select(g => g.First())
.Distinct()
.OrderBy(r => r.Field <string>("GroupCode"))
.ToArray();
for (int j = listGroupC.Length - 1; j >= 0; j--)
{
string groupCode = TextUtils.ToString(listGroupC[j]["GroupCode"]);
DataRow[] listCost = dtAllCost.AsEnumerable()
.Where(y => y.Field <string>("GroupCode") == groupCode)
.ToArray();
for (int k = listCost.Length - 1; k >= 0; k--)
{
//workSheet.Cells[31, 2] = TextUtils.ToString(listCost[k]["Code"]);
workSheet.Cells[31, 3] = TextUtils.ToString(listCost[k]["Code"]);
workSheet.Cells[31, 4] = TextUtils.ToString(listCost[k]["Name"]);
workSheet.Cells[31, 5] = TextUtils.ToString(listCost[k]["Unit"]);
workSheet.Cells[31, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]);
workSheet.Cells[31, 7] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]);
workSheet.Cells[31, 8] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]) / TextUtils.ToDecimal(txtTotalHD.EditValue);
((Excel.Range)workSheet.Rows[31]).Insert();
}
workSheet.Cells[31, 2] = TextUtils.ToString(listGroupC[j]["GroupCode"]);
workSheet.Cells[31, 3] = TextUtils.ToString(listGroupC[j]["GroupName"]);
//workSheet.Cells[31, 4] = TextUtils.ToString(listGroupC[j]["Name"]);
workSheet.Cells[31, 5] = "Đồng"; //TextUtils.ToString(listGroupC[j]["Unit"]);
workSheet.Cells[31, 6] = 1; // TextUtils.ToDecimal(listGroupC[j]["Qty"]);
decimal costC = TextUtils.ToDecimal(dtAllCost.Select("GroupCode = '" + groupCode + "'")
.Sum(x => x.Field <decimal>("TotalPrice")));
workSheet.Cells[31, 7] = costC;
workSheet.Cells[31, 8] = costC / TextUtils.ToDecimal(txtTotalHD.EditValue);
((Excel.Range)workSheet.Rows[31]).Font.Bold = true;
((Excel.Range)workSheet.Rows[31]).Insert();
}
workSheet.Cells[31, 2] = TextUtils.ToString(dtGroupP.Rows[i]["GroupCodeP"]);
workSheet.Cells[31, 3] = TextUtils.ToString(dtGroupP.Rows[i]["GroupNameP"]);
//workSheet.Cells[31, 4] = TextUtils.ToString(dtGroupP.Rows[i]["Name"]);
workSheet.Cells[31, 5] = "Đồng"; //TextUtils.ToString(dtGroupP.Rows[i]["Unit"]);
workSheet.Cells[31, 6] = 1; // TextUtils.ToDecimal(dtGroupP.Rows[i]["Qty"]);
decimal costP = TextUtils.ToDecimal(dtAllCost.Select("GroupCodeP = '" + groupCodeP + "'")
.Sum(x => x.Field <decimal>("TotalPrice")));;
workSheet.Cells[31, 7] = costP;
workSheet.Cells[31, 8] = costP / TextUtils.ToDecimal(txtTotalHD.EditValue);
((Excel.Range)workSheet.Rows[31]).Font.Bold = true;
((Excel.Range)workSheet.Rows[31]).Insert();
}
((Excel.Range)workSheet.Rows[30]).Delete();
((Excel.Range)workSheet.Rows[30]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(localPath);
}
}
private void btnFCM_Click(object sender, EventArgs e)
{
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\FCM-KD-" + Quotation.Code + ".xlsx";
}
else
{
return;
}
string fileName = "";
if (Quotation.CreatedDepartmentID == 10)//KD1
{
fileName = "FCM-KD1.xlsx";
}
if (Quotation.CreatedDepartmentID == 16)//KD2
{
fileName = "FCM-KD2.xlsx";
}
string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\" + fileName;
//string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM-KD-TEMPLATE.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo FCM..."))
{
DataTable dtQuotation = LibQLSX.Select("exec [spGetQuotation] @QuotationID = " + Quotation.ID);
DataTable dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationKD] " + Quotation.ID);
DataTable dtGroup = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCode");
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, 4] = "KD";
workSheet.Cells[4, 6] = Quotation.DeliveryTime;
workSheet.Cells[5, 4] = TextUtils.ToString(Quotation.ProjectCode);
workSheet.Cells[6, 4] = TextUtils.ToString(Quotation.ProjectName);
workSheet.Cells[7, 4] = TextUtils.ToString(Quotation.CustomerName);
workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["DName"]);
workSheet.Cells[8, 7] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]);
workSheet.Cells[9, 7] = TextUtils.ToString(Quotation.POnumber);
//Lợi nhuận
workSheet.Cells[13, 7] = TextUtils.ToDecimal(txtTotalProfitTT.EditValue);
workSheet.Cells[14, 7] = TextUtils.ToDecimal(txtTotalProfitQD.EditValue);
//Giá bán
workSheet.Cells[16, 7] = TextUtils.ToDecimal(txtTotalReal.EditValue); //Giá thực thu
workSheet.Cells[17, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue); //Giá có VAT thực tế bán trên HĐ
workSheet.Cells[18, 7] = TextUtils.ToDecimal(txtTotalHD.EditValue); //Giá chưa VAT thực tế bán trên HĐ
workSheet.Cells[19, 7] = TextUtils.ToDecimal(txtTotalTPA.EditValue); //Giá có VAT theo quy định phải bán
workSheet.Cells[20, 7] = TextUtils.ToDecimal(txtTotalTPA_PreVAT.EditValue); //Giá chưa VAT theo quy định phải bán
//Chi phí
workSheet.Cells[22, 7] = TextUtils.ToDecimal(txtTotalVAT.EditValue); //Thuế GTGT phải nộp
//Khách hàng gửi
if (Quotation.CreatedDepartmentID == 10) //KD1
{
workSheet.Cells[23, 7] = Quotation.CustomerCash; //Chênh lệch chi- thu xúc tiền bán hàng
workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalXL.EditValue); //Xử lí phần gửi
workSheet.Cells[25, 7] = Quotation.CustomerCash + TextUtils.ToDecimal(txtTotalXL.EditValue); //Khách hàng gửi
}
else
{
workSheet.Cells[23, 7] = Quotation.CustomerCash - TextUtils.ToDecimal(txtTotalXL.EditValue); //Chênh lệch chi- thu xúc tiền bán hàng
workSheet.Cells[24, 7] = TextUtils.ToDecimal(txtTotalXL.EditValue); //Xử lí phần gửi
workSheet.Cells[25, 7] = Quotation.CustomerCash; //Khách hàng gửi
}
workSheet.Cells[26, 7] = Quotation.TotalVC_KD; //Chi phí vận chuyển
workSheet.Cells[27, 7] = Quotation.TotalBX_KD; //Chi phí bốc xếp
workSheet.Cells[29, 7] = TextUtils.ToDecimal(txtTotalVT_SX.EditValue); //Mua tại TPA
workSheet.Cells[30, 7] = TextUtils.ToDecimal(txtTotalVT_MN.EditValue); //Mua ngoài TPA
workSheet.Cells[31, 7] = TextUtils.ToDecimal(txtTotalNC.EditValue); //Chi phí cố định - Nhân công gián tiếp
workSheet.Cells[32, 7] = TextUtils.ToDecimal(txtTotalNC_KD.EditValue); //Chi phí cố định - Nhân công KD
workSheet.Cells[33, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N08'")); //Chi phí quản lí N08
workSheet.Cells[34, 7] = TextUtils.ToDecimal(txtTotalDP.EditValue); //Chi phí dự phòng
workSheet.Cells[38, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N11'")); //Chi phí bảo hành
workSheet.Cells[39, 7] = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N09'")); //Chi phí lãi vay
for (int i = dtGroup.Rows.Count - 1; i >= 0; i--)
{
string groupCode = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
DataRow[] listCost = dtAllCost.AsEnumerable()
.Where(y => y.Field <string>("GroupCode") == groupCode)
.ToArray();
for (int k = listCost.Length - 1; k >= 0; k--)
{
workSheet.Cells[42, 2] = TextUtils.ToString(listCost[k]["Code"]);
workSheet.Cells[42, 3] = TextUtils.ToString(listCost[k]["Name"]);
//workSheet.Cells[42, 4] = TextUtils.ToString(listCost[k]["Name"]);
workSheet.Cells[42, 5] = "Đồng";
//workSheet.Cells[42, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]);
workSheet.Cells[42, 7] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]);
((Excel.Range)workSheet.Rows[42]).Insert();
Excel.Range range = workSheet.get_Range(workSheet.Cells[42, 3], workSheet.Cells[42, 4]);
range.Merge(true);
}
workSheet.Cells[42, 2] = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
workSheet.Cells[42, 3] = TextUtils.ToString(dtGroup.Rows[i]["GroupName"]);
((Excel.Range)workSheet.Rows[42]).Font.Bold = true;
((Excel.Range)workSheet.Rows[42]).Insert();
Excel.Range range1 = workSheet.get_Range(workSheet.Cells[42, 3], workSheet.Cells[42, 4]);
range1.Merge(true);
}
((Excel.Range)workSheet.Rows[41]).Delete();
((Excel.Range)workSheet.Rows[41]).Delete();
Excel.Worksheet workSheet2 = (Excel.Worksheet)workBoook.Worksheets[2];
DataTable dtItem = LibQLSX.Select("select * from vC_QuotationDetail_KD with(nolock) where ParentID = 0 and C_QuotationID = " + Quotation.ID);
for (int i = dtItem.Rows.Count - 1; i >= 0; i--)
{
int id = TextUtils.ToInt(dtItem.Rows[i]["ID"]);
DataTable dtC = LibQLSX.Select("select * from vC_QuotationDetail_KD with(nolock) where ParentID = " + id);
for (int j = dtC.Rows.Count - 1; j >= 0; j--)
{
workSheet2.Cells[5, 1] = (i + 1) + "." + (j + 1);
workSheet2.Cells[5, 2] = TextUtils.ToString(dtC.Rows[j]["ModuleName"]);
workSheet2.Cells[5, 3] = TextUtils.ToString(dtC.Rows[j]["ModuleCode"]);
workSheet2.Cells[5, 4] = TextUtils.ToString(dtC.Rows[j]["Manufacture"]);
workSheet2.Cells[5, 5] = TextUtils.ToString(dtC.Rows[j]["Origin"]);
workSheet2.Cells[5, 6] = TextUtils.ToString(dtC.Rows[j]["DCode"]);
workSheet2.Cells[5, 7] = TextUtils.ToString(dtC.Rows[j]["C_ProductGroupCode"]);
workSheet2.Cells[5, 8] = TextUtils.ToString(dtC.Rows[j]["Qty"]);
workSheet2.Cells[5, 17] = TextUtils.ToString(dtC.Rows[j]["PriceVT"]);
workSheet2.Cells[5, 18] = TextUtils.ToDecimal(dtC.Rows[j]["VAT"]) / 100;
workSheet2.Cells[5, 22] = TextUtils.ToString(dtC.Rows[j]["PriceTPA_PreVAT"]);
workSheet2.Cells[5, 23] = TextUtils.ToString(dtC.Rows[j]["PriceTPA"]);
workSheet2.Cells[5, 24] = TextUtils.ToString(dtC.Rows[j]["PriceVAT_HD"]);
workSheet2.Cells[5, 25] = TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]) * (1 - TextUtils.ToDecimal(dtC.Rows[j]["VAT"]) / 100);
workSheet2.Cells[5, 26] = TextUtils.ToDecimal(dtC.Rows[j]["Qty"]) * TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]);
workSheet2.Cells[5, 27] = TextUtils.ToDecimal(dtC.Rows[j]["Qty"]) * TextUtils.ToDecimal(dtC.Rows[j]["PriceVT"]);
((Excel.Range)workSheet2.Rows[5]).Insert();
}
workSheet2.Cells[5, 1] = i + 1;
workSheet2.Cells[5, 2] = TextUtils.ToString(dtItem.Rows[i]["ModuleName"]);
workSheet2.Cells[5, 3] = TextUtils.ToString(dtItem.Rows[i]["ModuleCode"]);
workSheet2.Cells[5, 4] = TextUtils.ToString(dtItem.Rows[i]["Manufacture"]);
workSheet2.Cells[5, 5] = TextUtils.ToString(dtItem.Rows[i]["Origin"]);
workSheet2.Cells[5, 6] = TextUtils.ToString(dtItem.Rows[i]["DCode"]);
workSheet2.Cells[5, 8] = TextUtils.ToString(dtItem.Rows[i]["Qty"]);
if (dtC.Rows.Count == 0)
{
workSheet2.Cells[5, 7] = TextUtils.ToString(dtItem.Rows[i]["C_ProductGroupCode"]);
workSheet2.Cells[5, 17] = TextUtils.ToString(dtItem.Rows[i]["PriceVT"]);
workSheet2.Cells[5, 18] = TextUtils.ToDecimal(dtItem.Rows[i]["VAT"]) / 100;
workSheet2.Cells[5, 22] = TextUtils.ToString(dtItem.Rows[i]["PriceTPA_PreVAT"]);
workSheet2.Cells[5, 23] = TextUtils.ToString(dtItem.Rows[i]["PriceTPA"]);
workSheet2.Cells[5, 24] = TextUtils.ToString(dtItem.Rows[i]["PriceVAT_HD"]);
workSheet2.Cells[5, 25] = TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]) * (1 - TextUtils.ToDecimal(dtItem.Rows[i]["VAT"]) / 100);
workSheet2.Cells[5, 26] = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]) * TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]);
workSheet2.Cells[5, 27] = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]) * TextUtils.ToDecimal(dtItem.Rows[i]["PriceVT"]);
}
((Excel.Range)workSheet2.Rows[5]).Font.Bold = true;
((Excel.Range)workSheet2.Rows[5]).Insert();
}
((Excel.Range)workSheet2.Rows[4]).Delete();
((Excel.Range)workSheet2.Rows[4]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(localPath);
}
}
private void btnFCM_Click(object sender, EventArgs e)
{
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\FCM-SX- " + Quotation.Code + ".xlsx";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\FCM-SX-TEMPLATE.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Lỗi: File excel đang được mở." + Environment.NewLine + ex.Message);
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo FCM..."))
{
DataTable dtQuotation = LibQLSX.Select("exec spGetQuotation @QuotationID = " + Quotation.ID);
DataTable dtAllCost = new DataTable();
if (Quotation.StatusNC == 1)
{
dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_DN] " + Quotation.ID);
}
else
{
dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationSX_CN] " + Quotation.ID);
}
decimal totalNC_ThietKe1 = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'"));
DataTable dtGroup = TextUtils.GetDistinctDatatable(dtAllCost, "GroupCode");
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, 4] = "KD";
workSheet.Cells[4, 6] = Quotation.DeliveryTime;
workSheet.Cells[5, 4] = TextUtils.ToString(Quotation.ProjectCode);
workSheet.Cells[6, 4] = TextUtils.ToString(Quotation.ProjectName);
workSheet.Cells[7, 4] = TextUtils.ToString(Quotation.CustomerName);
workSheet.Cells[8, 8] = TextUtils.ToString(dtQuotation.Rows[0]["StatusText"]);
workSheet.Cells[9, 4] = TextUtils.ToString(dtQuotation.Rows[0]["DName"]);
workSheet.Cells[9, 7] = TextUtils.ToString(Quotation.POnumber);
//Lợi nhuận
workSheet.Cells[14, 8] = TextUtils.ToDecimal(txtTotalProfit.EditValue);
//Giá bán
workSheet.Cells[16, 8] = TextUtils.ToDecimal(txtTotalTPA.EditValue);//Giá bán chưa VAT
workSheet.Cells[16, 9] = TextUtils.ToDecimal(txtTotalHD.EditValue);
//Chi phí vật tư
workSheet.Cells[19, 8] = TextUtils.ToDecimal(txtTotalVT.EditValue);//Giá vật tư
workSheet.Cells[19, 9] = TextUtils.ToDecimal(txtTotalVT.EditValue);
decimal totalNC_GianTiep = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N01'"));
workSheet.Cells[23, 6] = totalNC_GianTiep / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[23, 8] = totalNC_GianTiep;
workSheet.Cells[23, 9] = totalNC_GianTiep;//Chi phí cố định - đầu nhân viên gián tiếp
//Chi phí phân bổ khác
decimal totalPB_QuanLy = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N08'"));
workSheet.Cells[35, 6] = totalPB_QuanLy / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[35, 9] = totalPB_QuanLy;//Chi phí quản lí
decimal totalPB_TaiChinh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N09'"));
workSheet.Cells[36, 6] = totalPB_TaiChinh / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[36, 9] = totalPB_TaiChinh; //Chi phí tài chính và lãi vay
workSheet.Cells[37, 9] = TextUtils.ToDecimal(txtTotalDP.EditValue); //Chi phí dự phòng
decimal totalPB_BaoHanh = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N11'"));
workSheet.Cells[43, 6] = totalPB_BaoHanh / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[43, 9] = totalPB_BaoHanh;//Chi phí bảo hành
if (Quotation.StatusNC == 1)
{
//Chi phí nhân công
decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N02'"));
workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế
decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N03'"));
workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR
//Chi phí kỹ thuật
workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán
workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại
//workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán
workSheet.Cells[30, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán
decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N04'"));
workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow)
decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.02'"));
workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow)
decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "GroupCode = 'N07.03'"));
workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow)
}
else
{
//Chi phí nhân công
decimal totalNC_ThietKe = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P11' and Code = 'C09P24'"));
workSheet.Cells[24, 6] = totalNC_ThietKe / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[24, 9] = totalNC_ThietKe;//Chi phí cố định - đầu nhân viên Tkế
decimal totalNC_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P07'"));
workSheet.Cells[25, 6] = totalNC_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[25, 9] = totalNC_SXLR;//Chi phí cố định - đầu nhân viên SXLR
//Chi phí kỹ thuật
workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue); //Chi phí vận chuyển hàng bán
workSheet.Cells[29, 9] = TextUtils.ToDecimal(txtTotalDiLai.EditValue); // Chi phí đi lại
//workSheet.Cells[27, 9] = TextUtils.ToDecimal(txtTotalVC.EditValue);//Chi phí vận chuyển hàng bán
//workSheet.Cells[28, 9] = TextUtils.ToDecimal(txtTotalBX.EditValue);//Chi phí bốc xếp hàng bán
decimal totalKT_Service = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'C09P12'"));
workSheet.Cells[31, 6] = totalKT_Service / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[31, 9] = totalKT_Service;//Chi phí cố bộ phận Service (Lắp đặt, chuyển giao, follow)
decimal totalKT_SXLR = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PLD'"));
workSheet.Cells[32, 6] = totalKT_SXLR / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[32, 9] = totalKT_SXLR;//Chi phí bộ phận SXLR (Lắp đặt, chuyển giao, follow)
decimal totalKT_TK = TextUtils.ToDecimal(dtAllCost.Compute("Sum(TotalPrice)", "Code = 'PCG'"));
workSheet.Cells[33, 6] = totalKT_TK / TextUtils.ToDecimal(txtTotalTPA.EditValue);
workSheet.Cells[33, 9] = totalKT_TK;//Chi phí bộ phận thiết kế (Lắp đặt, chuyển giao, follow)
}
for (int i = dtGroup.Rows.Count - 1; i >= 0; i--)
{
string groupCode = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
DataRow[] listCost = dtAllCost.AsEnumerable()
.Where(y => y.Field <string>("GroupCode") == groupCode)
.ToArray();
for (int k = listCost.Length - 1; k >= 0; k--)
{
workSheet.Cells[46, 2] = TextUtils.ToString(listCost[k]["Code"]);
workSheet.Cells[46, 3] = TextUtils.ToString(listCost[k]["Name"]);
//workSheet.Cells[46, 4] = TextUtils.ToString(listCost[k]["Name"]);
workSheet.Cells[46, 5] = "Đồng";
//workSheet.Cells[46, 6] = TextUtils.ToDecimal(listCost[k]["Qty"]);
workSheet.Cells[46, 8] = TextUtils.ToDecimal(listCost[k]["TotalPrice"]);
((Excel.Range)workSheet.Rows[46]).Insert();
Excel.Range range = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]);
range.Merge(true);
}
workSheet.Cells[46, 2] = TextUtils.ToString(dtGroup.Rows[i]["GroupCode"]);
workSheet.Cells[46, 3] = TextUtils.ToString(dtGroup.Rows[i]["GroupName"]);
((Excel.Range)workSheet.Rows[46]).Font.Bold = true;
((Excel.Range)workSheet.Rows[46]).Insert();
Excel.Range range1 = workSheet.get_Range(workSheet.Cells[46, 3], workSheet.Cells[46, 4]);
range1.Merge(true);
}
((Excel.Range)workSheet.Rows[45]).Delete();
((Excel.Range)workSheet.Rows[45]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(localPath);
}
}
private void btnExportFormBank_Click(object sender, EventArgs e)
{
bool isTrongNuoc = true;
DialogResult result = MessageBox.Show("Bạn muốn xuất các biểu mẫu loại trong nước?", TextUtils.Caption, MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
isTrongNuoc = true;
}
else if (result == DialogResult.No)
{
isTrongNuoc = false;
}
else
{
return;
}
string filePath = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
filePath = ofd.FileName;
}
else
{
return;
}
DataTable dtItem = TextUtils.ExcelToDatatableNoHeader(filePath, "Sheet1");
for (int i = 0; i < 3; i++)
{
dtItem.Rows.RemoveAt(0);
}
DataTable dtDistinctItem = TextUtils.GetDistinctDatatable(dtItem, "F4");
string path = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
path = fbd.SelectedPath;
}
else
{
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo mẫu lệnh chi..."))
{
#region Mẫu lệnh chi
for (int i = 0; i < dtDistinctItem.Rows.Count; i++)
//for (int i = 0; i < 1; i++)
{
string stt = TextUtils.ToString(dtDistinctItem.Rows[i]["F1"]);
if (stt == "")
{
continue;
}
string filePathS = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\Mau lenh chi.xls";
string currentPath = path + "\\Mau lenh chi-" + TextUtils.ToString(dtDistinctItem.Rows[i]["F2"]) + ".xls";
try
{
File.Copy(filePathS, currentPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi tạo biểu mẫu lệnh chi!" + Environment.NewLine + ex.Message,
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
continue;
}
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];
string loaiTienTe = TextUtils.ToString(dtDistinctItem.Rows[i]["F13"]);
string percentThanhToan = TextUtils.ToString(dtDistinctItem.Rows[i]["F14"]);
string soTaiKhoan = TextUtils.ToString(dtDistinctItem.Rows[i]["F4"]);
string soHoaDonHopDong = "";
double tienNhanNo = 0;
DataRow[] drs = dtItem.Select("F4 = '" + soTaiKhoan + "'");
foreach (DataRow item in drs)
{
tienNhanNo += TextUtils.ToDouble(item["F9"]);
soHoaDonHopDong += TextUtils.ToString(item["F5"]) + ",";
}
workSheet.Cells[7, 1] = " Số No………Ngày Date ……/"
+ string.Format("{0:00}", DateTime.Now.Month) + "/" + DateTime.Now.Year;
workSheet.Cells[12, 3] = TextUtils.ToString(dtDistinctItem.Rows[i]["F2"]); //Bên thụ hưởng
workSheet.Cells[15, 4] = TextUtils.ToString(dtDistinctItem.Rows[i]["F12"]); //địa chỉ bên thụ hưởng
if (isTrongNuoc)
{
workSheet.Cells[13, 3] = "'" + soTaiKhoan;
workSheet.Cells[16, 4] = tienNhanNo.ToString("n2") + " " + loaiTienTe; //Số tiền nhận nợ
workSheet.Cells[17, 4] = TextUtils.NumericToString(tienNhanNo, loaiTienTe); //tiền nhận nợ bẳng chữ
}
workSheet.Cells[21, 3] = "Công ty cp tự động hóa Tân phát thanh toán " + tienNhanNo + " " + (loaiTienTe == "" ? "VNĐ" : loaiTienTe)
+ " tiền hợp đồng " + soHoaDonHopDong.Substring(0, soHoaDonHopDong.Length - 1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
}
#endregion Mẫu lệnh chi
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Giấy nhận nợ..."))
{
#region Giấy nhận nợ
Word.Application word = new Word.Application();
Word.Document doc = new Word.Document();
try
{
string localFilePath = "";
if (isTrongNuoc)
{
localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\GiayNhanNo_VND.docx";
}
else
{
localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\GiayNhanNo_USD.docx";
}
string currentFilePath = path + "\\GiayNhanNo-" + DateTime.Now.ToString("ddMMyyyy") + ".docx";
try
{
File.Copy(localFilePath, currentFilePath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi tạo biểu Giấy nhận nợ!" + Environment.NewLine + ex.Message,
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
doc = word.Documents.Open(currentFilePath);
doc.Activate();
decimal tong1 = 0;
decimal tong2 = 0;
for (int i = 0; i < dtItem.Rows.Count; i++)
{
int stt = TextUtils.ToInt(dtItem.Rows[i]["F1"]);
decimal tienHoaDon = TextUtils.ToDecimal(dtItem.Rows[i]["F8"]);
decimal tienNhanNo = TextUtils.ToDecimal(dtItem.Rows[i]["F9"]);
string loaiTienTe = TextUtils.ToString(dtItem.Rows[i]["F13"]);
tong1 += tienHoaDon;
tong2 += tienNhanNo;
TextUtils.RepalaceText(doc, "<thuHuong" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F2"]));
TextUtils.RepalaceText(doc, "<nganHang" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F3"]));
TextUtils.RepalaceText(doc, "<soTaiKhoan" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F4"]));
TextUtils.RepalaceText(doc, "<soHoaDon" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F5"]));
//TextUtils.RepalaceText(doc, "<ngay" + stt + ">", TextUtils.ToDate3(dtItem.Rows[i]["F6"]).ToString("dd/MM/yyyy"));
TextUtils.RepalaceText(doc, "<ngay" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F6"]));
TextUtils.RepalaceText(doc, "<matHang" + stt + ">", TextUtils.ToString(dtItem.Rows[i]["F7"]));
TextUtils.RepalaceText(doc, "<tienHoaDon" + stt + ">", tienHoaDon.ToString("n2"));
TextUtils.RepalaceText(doc, "<tienNhanNo" + stt + ">", tienNhanNo.ToString("n2"));
}
TextUtils.RepalaceText(doc, "<tong1>", tong1.ToString("n2"));
TextUtils.RepalaceText(doc, "<tong2>", tong2.ToString("n2"));
TextUtils.RepalaceText(doc, "<bangchu>", TextUtils.NumericToString((double)tong2, isTrongNuoc ? "" : "USD"));
TextUtils.RepalaceText(doc, "<month>", string.Format("{0:00}", DateTime.Now.Month));
TextUtils.RepalaceText(doc, "<year>", DateTime.Now.Year.ToString());
doc.Save();
doc.Close();
word.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Tân Phát", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
TextUtils.ReleaseComObject(doc);
TextUtils.ReleaseComObject(word);
}
#endregion Giấy nhận nợ
}
if (isTrongNuoc)
{
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Hợp đồng ngoại tệ..."))
{
#region Hợp đồng ngoại tệ
Word.Application word = new Word.Application();
Word.Document doc = new Word.Document();
try
{
string localFilePath = Application.StartupPath + "\\Templates\\PhongKeToan\\NganHang\\HopDongMuaBanNgoaiTe.docx";
string currentFilePath = path + "\\HopDongMuaBanNgoaiTe-" + DateTime.Now.ToString("ddMMyyyy") + ".docx";
try
{
File.Copy(localFilePath, currentFilePath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi tạo biểu Hợp đồng mua bán ngoại tệ!" + Environment.NewLine + ex.Message,
TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
doc = word.Documents.Open(currentFilePath);
doc.Activate();
for (int i = 0; i < dtDistinctItem.Rows.Count; i++)
{
int f1 = TextUtils.ToInt(dtDistinctItem.Rows[i]["F1"]);
if (f1 == 0)
{
continue;
}
int stt = f1; //i + 1;
string loaiTienTe = TextUtils.ToString(dtDistinctItem.Rows[i]["F13"]);
string soTaiKhoan = TextUtils.ToString(dtDistinctItem.Rows[i]["F4"]);
double tienNhanNo = 0;
string noiDung = "";
DataRow[] drs = dtItem.Select("F4 = '" + soTaiKhoan + "'");
if (drs.Length > 1)
{
foreach (DataRow item in drs)
{
tienNhanNo += TextUtils.ToDouble(item["F9"]);
string soHoaDonHopDong = TextUtils.ToString(item["F5"]);
string ngay = TextUtils.ToDate3(item["F6"]).ToString("dd/MM/yyyy");
string percentThanhToan = TextUtils.ToString(item["F14"]);
if (percentThanhToan != "")
{
noiDung += "Thanh toán trả trước nốt " + percentThanhToan + "% hợp đồng số " + soHoaDonHopDong + " ngày " + ngay + Environment.NewLine;
}
else
{
noiDung += "Thanh toán tiền hàng hợp đồng số " + soHoaDonHopDong + " ngày " + ngay + Environment.NewLine;
}
}
}
else
{
tienNhanNo = TextUtils.ToDouble(drs[0]["F9"]);
string soHoaDonHopDong = TextUtils.ToString(drs[0]["F5"]);
string ngay = TextUtils.ToDate3(drs[0]["F6"]).ToString("dd/MM/yyyy");
string percentThanhToan = TextUtils.ToString(drs[0]["F14"]);
if (percentThanhToan != "")
{
noiDung = "Thanh toán trả trước nốt " + percentThanhToan + "% hợp đồng số " + soHoaDonHopDong + " ngày " + ngay;
}
else
{
noiDung = "Thanh toán tiền hàng hợp đồng số " + soHoaDonHopDong + " ngày " + ngay;
}
}
int length = noiDung.Length;
if (length <= 200)
{
TextUtils.RepalaceText(doc, "<noiDung" + stt + ">", noiDung);
}
TextUtils.RepalaceText(doc, "<benThuHuong" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F2"]));
TextUtils.RepalaceText(doc, "<NganHang" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F3"]));
TextUtils.RepalaceText(doc, "<soTaiKhoan" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F4"]));
TextUtils.RepalaceText(doc, "<tienNhanNo" + stt + ">", tienNhanNo.ToString("n2"));
TextUtils.RepalaceText(doc, "<bangChu" + stt + ">", TextUtils.NumericToString(tienNhanNo, "USD"));
TextUtils.RepalaceText(doc, "<swiftCode" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F10"]));
TextUtils.RepalaceText(doc, "<diaChiNganHang" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F11"]));
TextUtils.RepalaceText(doc, "<diaChiBenThuHuong" + stt + ">", TextUtils.ToString(dtDistinctItem.Rows[i]["F12"]));
}
TextUtils.RepalaceText(doc, "<month>", string.Format("{0:00}", DateTime.Now.Month));
TextUtils.RepalaceText(doc, "<year>", DateTime.Now.Year.ToString());
doc.Save();
doc.Close();
word.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Tân Phát", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
TextUtils.ReleaseComObject(doc);
TextUtils.ReleaseComObject(word);
}
#endregion Hợp đồng ngoại tệ
}
}
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();
}
}
}
}