private void grvLink_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
{
if (e.Column == colNumberDay || e.Column == colPersonNumber || e.Column == colNCType)
{
decimal numberDay = TextUtils.ToDecimal(grvLink.GetFocusedRowCellValue(colNumberDay));
decimal personNumber = TextUtils.ToDecimal(grvLink.GetFocusedRowCellValue(colPersonNumber));
decimal qty = TextUtils.ToDecimal(grvLink.GetFocusedRowCellValue(colQty));
decimal pricePerDay = 0;// TextUtils.ToDecimal(grvLink.GetFocusedRowCellValue(colPricePerDay));
if (e.Column == colNCType)
{
string ncType = TextUtils.ToString(grvLink.GetFocusedRowCellValue(colNCType));
int costID = TextUtils.ToInt(grvLink.GetFocusedRowCellValue(colC_CostID));
string sql = "select Price from C_CostNCType where C_CostID = " + costID + " and CostNCType = '" + ncType + "'";
pricePerDay = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sql));
}
else
{
pricePerDay = TextUtils.ToDecimal(grvLink.GetFocusedRowCellValue(colPricePerDay));
}
grvLink.SetFocusedRowCellValue(colPricePerDay, pricePerDay);
decimal price = numberDay * personNumber * pricePerDay;
grvLink.SetFocusedRowCellValue(colPrice, price);
grvLink.SetFocusedRowCellValue(colTongTien, qty * price);
}
}
private void cboProject_EditValueChanged(object sender, EventArgs e)
{
if (cboProject.EditValue == null)
{
return;
}
string sql = "select sum(TotalReal*Price) from vGetProductOfProject1 where ProjectId = '" + TextUtils.ToString(cboProject.EditValue) + "'";
txtPrice.EditValue = LibQLSX.ExcuteScalar(sql);
}
void saveDN(ProcessTransaction pt, int count, DataRow row)
{
int stt = TextUtils.ToInt(row["F1"]);
decimal qty = TextUtils.ToDecimal(row["F7"]);
C_QuotationDetail_SXModel item = new C_QuotationDetail_SXModel();
item.C_QuotationID = Quotation.ID;
item.ParentID = 0;
item.Qty = item.QtyT = qty;
item.PriceVT = TextUtils.ToDecimal(row["F41"]);
item.PriceVTTN = TextUtils.ToDecimal(row["F42"]);
item.PriceVTPS = TextUtils.ToDecimal(row["F43"]);
item.Manufacture = TextUtils.ToString(row["F4"]);
item.ModuleCode = TextUtils.ToString(row["F3"]);
item.ModuleName = TextUtils.ToString(row["F2"]);
item.Origin = TextUtils.ToString(row["F5"]);
item.C_ProductGroupID = TextUtils.ToInt(LibQLSX.ExcuteScalar("select ID from C_ProductGroup where Code = '" + TextUtils.ToString(row["F6"]) + "'"));
item.ID = (int)pt.Insert(item);
count++;
row["ID"] = item.ID;
DataRow[] drs = _dtData.Select("F1 like '" + stt + ".%'");
foreach (DataRow rowC in drs)
{
decimal qtyC = TextUtils.ToDecimal(rowC["F7"]);
string groupCode = TextUtils.ToString(rowC["F6"]);
int groupID = TextUtils.ToInt(LibQLSX.ExcuteScalar("select ID from C_ProductGroup where Code = '" + groupCode + "'"));
C_QuotationDetail_SXModel itemC = new C_QuotationDetail_SXModel();
itemC.C_QuotationID = Quotation.ID;
itemC.ParentID = item.ID;
itemC.C_ProductGroupID = groupID;
itemC.Qty = qtyC;
itemC.QtyT = qtyC / item.Qty;
itemC.PriceVT = TextUtils.ToDecimal(rowC["F41"]);
itemC.PriceVTTN = TextUtils.ToDecimal(row["F42"]);
itemC.PriceVTPS = TextUtils.ToDecimal(row["F43"]);
itemC.ModuleName = TextUtils.ToString(rowC["F2"]);
itemC.ModuleCode = TextUtils.ToString(rowC["F3"]);
itemC.Manufacture = TextUtils.ToString(rowC["F4"]);
itemC.Origin = TextUtils.ToString(rowC["F5"]);
itemC.ID = (int)pt.Insert(itemC);
rowC["ID"] = itemC.ID;
count++;
}
}
private void frmShowStaff_Load(object sender, EventArgs e)
{
loadStaffGroupLink();
loadStaffGroup();
loadCombo();
loadGroupQLSX();
if (Model.ID != 0)
{
txtBankAccount.Text = Model.BankAccount;
txtBHXH.Text = Model.BHXH;
txtBHYT.Text = Model.BHYT;
dtpBirthOfDate.EditValue = Model.BirthOfDate;
txtCMTND.Text = Model.CMTND;
txtCode.Text = Model.Code;
cboDepartment.SelectedValue = Model.DepartmentID;
txtEmail.Text = Model.Email;
txtEmailCom.Text = Model.EmailCom;
txtFullName.Text = Model.FullName;
txtTelephone.Text = Model.HandPhone;
txtHomeAddress.Text = Model.HomeAddress;
txtJobDescription.Text = Model.JobDescription;
txtLoginName.Text = Model.LoginName;
txtMST.Text = Model.MST;
txtPasswordHash.Text = MD5.DecryptPassword(Model.PasswordHash);
txtPosition.Text = Model.Position;
txtQualifications.Text = Model.Qualifications;
cboSex.SelectedIndex = Model.Sex;
dtpStartWorking.EditValue = Model.StartWorking;
pictureBox1.ImageLocation = Model.ImagePath;
cboStatus.SelectedIndex = Model.Status;
lookUpEdit1.EditValue = Model.UserGroupID;
if (Model.UserGroupSXID > 0)
{
cboGroup.EditValue = Model.UserGroupSXID;
}
else
{
cboGroup.EditValue = LibQLSX.ExcuteScalar("select top 1 UserGroup1ID from Users with(nolock) where Account = '" + Model.LoginName + "'");
}
if (txtLoginName.Text != "")
{
chkHasUser.Checked = true;
}
}
else
{
txtPasswordHash.Text = "123456";
}
}
private void cboOrder_EditValueChanged(object sender, EventArgs e)
{
string orderId = TextUtils.ToString(grvCboOrder.GetFocusedRowCellValue(colOrderID));
if (orderId == "")
{
return;
}
_type = 0;
_countProject = 0;
cboUser.EditValue = null;
cboSupplier.EditValue = null;
cboNguoiPhuTrach.EditValue = null;
txtCode.Text = TextUtils.ToString(grvCboOrder.GetFocusedRowCellValue(colOrderCode));
txtName.Text = TextUtils.ToString(grvCboOrder.GetFocusedRowCellValue(colOrderSupplierName));
DataTable dtPrice = loadPartOfOrder(txtCode.Text);
decimal totalTransfer = TextUtils.ToDecimal(LibIE.ExcuteScalar("SELECT * FROM V_XNTC_VUVIEC WHERE (C_MA = '"
+ txtCode.Text.Trim() + "') AND (FK_TKCO LIKE '111%' OR FK_TKCO LIKE '112%') AND (FK_TKNO LIKE '331%') and C_KHACHHANG_MA = '"
+ TextUtils.ToString(grvCboOrder.GetFocusedRowCellValue(colOrderSupplierCode)) + "'"));
decimal totalPrice = TextUtils.ToDecimal(dtPrice.Compute("Sum(TotalPrice)", ""));
txtTotalTH.EditValue = totalPrice;
txtDiffCost.EditValue = TextUtils.ToDecimal(grvCboOrder.GetFocusedRowCellValue(colOrderDiffCost));
//Tính lại công thức lấy tổng tiền PO = tiền hàng + vat + chi phí vận chuyển 10/03/2018
decimal deliveryNew = TextUtils.ToDecimal(
LibQLSX.ExcuteScalar("select isnull(sum(isnull(Amount,0) ),0) FROM [dbo].vTransport where [OrderId]='" + orderId + "'")
);
txtDeliveryCost.EditValue = deliveryNew;
txtVAT.EditValue = TextUtils.ToDecimal(grvCboOrder.GetFocusedRowCellValue(colOrderVAT));
decimal total = (TextUtils.ToDecimal(txtTotalTH.EditValue) + TextUtils.ToDecimal(txtDiffCost.EditValue) + TextUtils.ToDecimal(txtDeliveryCost.EditValue));
total = total + (TextUtils.ToDecimal(txtVAT.EditValue) * total / 100);
txtTotal.EditValue = total;
txtTotalTransfer.EditValue = totalTransfer;
txtTotalRest.EditValue = TextUtils.ToDecimal(txtTotal.EditValue) - TextUtils.ToDecimal(txtTotalTransfer.EditValue);
txtPercentPay.Text = "100";
chkTM.Checked = true;
txtTM.EditValue = TextUtils.ToDecimal(txtTotal.EditValue) * TextUtils.ToDecimal(txtPercentPay.EditValue) / 100;
txtCK.EditValue = 0;
DataTable dtProject = LibQLSX.Select("select * from vGetProjectWithOrder with(nolock) where OrderId = '" + orderId + "'");
List <string> listProject = new List <string>();
foreach (DataRow row in dtProject.Rows)
{
string projectCode = TextUtils.ToString(row["ProjectCode"]) == "" ? "Mua vật tư sản xuất chung" : TextUtils.ToString(row["ProjectCode"]);
if (!listProject.Contains(projectCode))
{
listProject.Add(projectCode);
_countProject++;
}
}
cboNguoiPhuTrach.EditValue = TextUtils.ToString(grvCboOrder.GetFocusedRowCellValue(colOrderUserId));
txtTarget.Text = TextUtils.ArrayToString(" ,", listProject.ToArray());
}
private void btnSave_Click(object sender, EventArgs e)
{
if (grvData.RowCount == 0)
{
return;
}
if (_dtData.Rows.Count == 0)
{
return;
}
int count = 0;
bool isSaved = false;
ProcessTransaction pt = new ProcessTransaction();
pt.OpenConnection();
pt.BeginTransaction();
try
{
DataRow[] drsIsSaved = _dtData.Select("ID > 0");
if (drsIsSaved.Length > 0)
{
MessageBox.Show("Danh sách thiết bị đã được lưu!");
}
DataRow[] drsParent = _dtData.Select("F2 = '0'");
foreach (DataRow row in drsParent)
{
int stt = TextUtils.ToInt(row["F1"]);
//int parentID = TextUtils.ToInt(row["F2"]);
decimal qty = TextUtils.ToDecimal(row["F7"]);
C_QuotationDetail_KDModel item = new C_QuotationDetail_KDModel();
item.C_QuotationID = Quotation.ID;
item.ParentID = 0;
item.Qty = item.QtyT = qty;
item.PriceVT = TextUtils.ToDecimal(row["F8"]);
//item.PriceVT = item.PriceVT;
item.Manufacture = TextUtils.ToString(row["F9"]);
item.ModuleCode = TextUtils.ToString(row["F4"]);
item.ModuleName = TextUtils.ToString(row["F3"]);
item.Origin = TextUtils.ToString(row["F10"]);
item.VAT = TextUtils.ToDecimal(row["F6"]) * 100;
item.PriceHD = TextUtils.ToDecimal(row["F12"]);
//DataTable dtGroup = LibQLSX.Select();
string sqlGroup = "select ID from C_ProductGroup where Code = '" + TextUtils.ToString(row["F5"]) + "'";
item.C_ProductGroupID = TextUtils.ToInt(LibQLSX.ExcuteScalar(sqlGroup)); //dtGroup.Rows.Count > 0 ? TextUtils.ToInt(dtGroup.Rows[0][0]) : 0;
string sqlPercentXLKH = "select top 1 " + (Quotation.DepartmentId == "D018" ? "CustomerPercentKD1" : "CustomerPercentKD2")
+ " from C_ProductGroup where ID = " + item.C_ProductGroupID;
item.PercentXLKH = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlPercentXLKH));
item.ID = (int)pt.Insert(item);
count++;
row["ID"] = item.ID;
DataRow[] drs = _dtData.Select("F2 = '" + stt + "'");
foreach (DataRow rowC in drs)
{
int sttC = TextUtils.ToInt(rowC["F1"]);
int parentIDC = TextUtils.ToInt(rowC["F2"]);
decimal qtyC = TextUtils.ToDecimal(rowC["F7"]);
string groupCode = TextUtils.ToString(rowC["F5"]);
int groupID = 0;
DataTable dtGroupC = LibQLSX.Select("select ID from C_ProductGroup where Code = '" + groupCode + "'");
groupID = dtGroupC.Rows.Count > 0 ? TextUtils.ToInt(dtGroupC.Rows[0][0]) : 0;
C_QuotationDetail_KDModel itemC = new C_QuotationDetail_KDModel();
itemC.C_QuotationID = Quotation.ID;
itemC.ParentID = item.ID;
itemC.C_ProductGroupID = groupID;
string sqlPercentXLKH1 = "select top 1 " + (Quotation.DepartmentId == "D018" ? "CustomerPercentKD1" : "CustomerPercentKD2")
+ " from C_ProductGroup where ID = " + itemC.C_ProductGroupID;
item.PercentXLKH = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlPercentXLKH1));
itemC.VAT = TextUtils.ToDecimal(rowC["F6"]) * 100;
itemC.Qty = qtyC * item.Qty;
itemC.QtyT = qtyC;
itemC.PriceVT = TextUtils.ToDecimal(rowC["F8"]);
//itemC.PriceVT = itemC.PriceVT;
itemC.ModuleName = TextUtils.ToString(rowC["F3"]);
itemC.ModuleCode = TextUtils.ToString(rowC["F4"]);
itemC.Manufacture = TextUtils.ToString(rowC["F9"]);
itemC.Origin = TextUtils.ToString(rowC["F10"]);
string pTK = TextUtils.ToString(rowC["F11"]);
if (pTK.ToUpper() == "TK1")
{
itemC.DepartmentId = "D009";
}
else if (pTK.ToUpper() == "TK2")
{
itemC.DepartmentId = "D028";
}
else
{
itemC.DepartmentId = "";
}
itemC.ID = (int)pt.Insert(itemC);
rowC["ID"] = itemC.ID;
count++;
}
}
pt.CommitTransaction();
isSaved = true;
MessageBox.Show("Đã lưu trữ thành công " + count + "/" + _dtData.Rows.Count + " thiết bị!");
}
catch (Exception ex)
{
MessageBox.Show("Lưu trữ không thành công!" + Environment.NewLine + ex.Message, TextUtils.Caption,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
pt.CloseConnection();
}
if (count > 0 && isSaved)
{
if (this.LoadDataChange != null)
{
this.LoadDataChange(null, null);
}
}
}
void loadDataOld()
{
string fcmCode = TextUtils.ToString(cboFCM.EditValue);
txtTotalVT.EditValue = LibQLSX.ExcuteScalar("select sum(isnull(TotalBuy,0) * isnull(Price,0)) FROM [vRequirePartFull_Order] where ProjectCode = '" + fcmCode + "' and isnull([OrderCode],'')<>''");
int year = TextUtils.ToInt(cboYear.SelectedItem);
//int month = cboMonth.SelectedIndex;
DataTable dtLaiVay = loadLaiVay(fcmCode);
string sqlStart = "SELECT a.PK_ID, a.C_MA, a.C_MOTA, a.[GroupCode] +'-'+ a.[GroupName] as [GroupCode]";
string sqlEnd = " FROM V_DM_KMP a where isnull([FK_GROUP],0) > 0";
string sql = ",[C_Value] = isnull((select sum([C_PRICE]) from [V_DM_FCM_DETAIL] where [C_CODE] like '" + fcmCode + "%' and [C_KMP_MA] = a.C_MA),0)";
DataTable dtMonth = LibIE.Select("select distinct [C_Month] from [V_XNTC_REPORT_ALL] where [C_DTCP_MA] like '" + fcmCode + "%' and [C_YEAR] = " + year);
sql += " ,[Total_NamTruoc] = isnull((SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] = '" + fcmCode + "' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = a.C_MA "
+ " and [C_Year] < " + year + "),0)";
string exp = "Total_NamTruoc";
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
sql += " ,[T_" + p + "] = isnull((SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '" + fcmCode + "%'"
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = a.C_MA "
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p + "),0) ";
exp += "+ T_" + p;
}
string str = sqlStart + sql + sqlEnd;
DataTable dtData = LibIE.Select(str);
#region Nhập chi phí vật tư thực tế của dự án
DataRow[] drsVT = dtData.Select("C_MA = 'C27' or C_MA = 'C20' or C_MA = 'C24' or C_MA = 'C22'");
foreach (DataRow r in drsVT)
{
string kmpCode = TextUtils.ToString(r["C_MA"]);
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] = '" + kmpCode + "'"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sql2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA_LUU] like '" + fcmCode + "%' "
+ " and (FK_TKNO = '154' AND FK_TKNO = '154')"
//+ " and [C_KMP_MA] = '" + kmpCode + "'"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1)) + TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2));
}
}
#endregion
#region Nhập chi phí lãi vay thực tế của dự án
DataRow[] drsLaiVay = dtData.Select("C_MA = 'C11'");
foreach (DataRow r in drsLaiVay)
{
string kmpCode = TextUtils.ToString(r["C_MA"]);
foreach (DataRow row in dtMonth.Rows)
{
int p = TextUtils.ToInt(row["C_Month"]);
decimal cLai = TextUtils.ToDecimal(dtLaiVay.Compute("SUM(LaiGop)", "C_Month = " + p + " and C_Year = " + year));
r["T_" + p] = cLai;
}
}
#endregion
DataColumnCollection columns = dtData.Columns;
DataTable dtFCM = LibIE.Select("select top 1 * from T_DM_FCM where C_CODE = '" + fcmCode + "'");
if (dtFCM.Rows.Count > 0)
{
#region Add column
DataRow dr1 = dtData.NewRow();
dr1["PK_ID"] = 0;
dr1["C_MA"] = "T1";
dr1["C_MOTA"] = "DT.Giá bán trên HĐ";
dr1["GroupCode"] = "";
dr1["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalHD"]);
if (columns.Contains("Total_NamTruoc"))
{
dr1["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr1);
DataRow dr2 = dtData.NewRow();
dr2["PK_ID"] = 0;
dr2["C_MA"] = "T2";
dr2["C_MOTA"] = "DT.Giá bán theo quy định TPA";
dr2["GroupCode"] = "";
dr2["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalTPA"]);
if (columns.Contains("Total_NamTruoc"))
{
dr2["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr2);
DataRow dr3 = dtData.NewRow();
dr3["PK_ID"] = 0;
dr3["C_MA"] = "T3";
dr3["C_MOTA"] = "DT.Thuế GTGT";
dr3["GroupCode"] = "";
dr3["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalVAT"]);
if (columns.Contains("Total_NamTruoc"))
{
dr3["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr3);
DataRow dr4 = dtData.NewRow();
dr4["PK_ID"] = 0;
dr4["C_MA"] = "T4";
dr4["C_MOTA"] = "DT.Giá thực thu";
dr4["GroupCode"] = "";
dr4["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalReal"]);
if (columns.Contains("Total_NamTruoc"))
{
dr4["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr4);
DataRow dr5 = dtData.NewRow();
dr5["PK_ID"] = 0;
dr5["C_MA"] = "T5";
dr5["C_MOTA"] = "DT.Tổng chi phí triển khai DA tại Khách Hàng";
dr5["GroupCode"] = "";
dr5["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalTrienKhai"]);
if (columns.Contains("Total_NamTruoc"))
{
dr5["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr5);
DataRow dr6 = dtData.NewRow();
dr6["PK_ID"] = 0;
dr6["C_MA"] = "T6";
dr6["C_MOTA"] = "DT.Tổng chi phí nhân công trực tiếp";
dr6["GroupCode"] = "";
dr6["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalNC"]);
if (columns.Contains("Total_NamTruoc"))
{
dr6["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr6);
DataRow dr7 = dtData.NewRow();
dr7["PK_ID"] = 0;
dr7["C_MA"] = "T7";
dr7["C_MOTA"] = "DT.Tổng chi phí quản lí phân bổ";
dr7["GroupCode"] = "";
dr7["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalPB"]);
if (columns.Contains("Total_NamTruoc"))
{
dr7["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr7);
DataRow dr8 = dtData.NewRow();
dr8["PK_ID"] = 0;
dr8["C_MA"] = "T8";
dr8["C_MOTA"] = "DT.Chi phí bổ xung";
dr8["GroupCode"] = "";
dr8["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalBX"]);
if (columns.Contains("Total_NamTruoc"))
{
dr8["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr8);
DataRow dr9 = dtData.NewRow();
dr9["PK_ID"] = 0;
dr9["C_MA"] = "T9";
dr9["C_MOTA"] = "DT.Loi Nhuan";
dr9["GroupCode"] = "";
dr9["C_Value"] = TextUtils.ToDecimal(dtFCM.Rows[0]["TotalProfit"]);
if (columns.Contains("Total_NamTruoc"))
{
dr9["Total_NamTruoc"] = 0;
}
dtData.Rows.Add(dr9);
#endregion
}
DataRow[] drsTong = dtData.Select("C_MA like 'T%'", "C_MA ASC");
#region Update Tổng
foreach (DataRow r in drsTong)
{
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
decimal totalTrienKhai = 0;
decimal totalPB = 0;
decimal totalReal = 0;
if (TextUtils.ToString(r["C_MA"]) == "T4")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKCO like '511%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sql2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '521%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalReal = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2));
}
else if (TextUtils.ToString(r["C_MA"]) == "T5")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalTrienKhai = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1));
}
else if (TextUtils.ToString(r["C_MA"]) == "T7")
{
string sql1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09P01','C09P03','C09P05','C09P06','C09P08','C09P09','C09P13','C09P14','C09P16','C09P20','C09P23'"
+ ",'C07','C12','C01','C06','C02','C17','C16','C32','C51','C47','C48',"
+ "'C50','C54','C55','C57','C10','C23','C25','C26','C11','C19','C31','C30','C53','C29','C33')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
r["T_" + p] = totalPB = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1));
}
else if (TextUtils.ToString(r["C_MA"]) == "T9")
{
string sqlReal1 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKCO like '511%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlReal2 = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '521%')"
//+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlTrienKhai = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C13','C52','C03','C38','C39','C40','C43','C04','C41','C42','C46','C05','C44','C45')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlPB = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09P01','C09P03','C09P05','C09P06','C09P08','C09P09','C09P13','C09P14','C09P16','C09P20','C09P23'"
+ ",'C07','C12','C01','C06','C02','C17','C16','C32','C51','C47','C48',"
+ "'C50','C54','C55','C57','C10','C23','C25','C26','C11','C19','C31','C30','C53','C29','C33')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlNhanCong = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C09','C09P01','C09P07','C09P10','C09P11','C09P12','C09P17','C15','C34','C35','C36')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
string sqlVT = "SELECT sum([C_PSNO]) FROM [V_XNTC_REPORT_ALL]"
+ " where [C_DTCP_MA] like '%" + fcmCode + "%' "
+ " and (FK_TKNO like '154%' or FK_TKNO like '6321%' or FK_TKNO like '642%' or FK_TKNO like '521%' or FK_TKNO like '635%')"
+ " and [C_KMP_MA] in ('C27','C20','C24','C22')"
+ " and [C_Year] = " + year
+ " and [C_Month] = " + p;
decimal totalLoiNhuan = TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlReal1)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlReal2)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlNhanCong))
- TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlPB)) - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlTrienKhai)) - 0 - TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlVT));
r["T_" + p] = totalLoiNhuan;
}
else
{
r["T_" + p] = 0;
}
}
}
#endregion
dtData.Columns.Add("LuyKe", typeof(decimal), exp);
dtData.Columns.Add("ChenhLech", typeof(decimal), "C_Value - (" + exp + ")");
dtData.Columns["PK_ID"].Caption = "PK_ID";
dtData.Columns["C_MA"].Caption = "Mã";
dtData.Columns["C_MOTA"].Caption = "Tên";
dtData.Columns["GroupCode"].Caption = "Nhóm";
dtData.Columns["C_Value"].Caption = "Tổng tiền";
dtData.Columns["Total_NamTruoc"].Caption = "Năm trước";
dtData.Columns["LuyKe"].Caption = "Lũy kế";
dtData.Columns["ChenhLech"].Caption = "Chênh lệch (DK-TT)";
grvData.PopulateColumns(dtData);
grdData.DataSource = dtData;
grvData.Columns[0].Visible = false;
//grvData.Columns[3].GroupIndex = 0;
grvData.Columns[3].Visible = false;
grvData.Columns[4].Visible = false;
for (int i = 5; i < dtData.Columns.Count; i++)
{
grvData.Columns[i].DisplayFormat.FormatType = FormatType.Numeric;
grvData.Columns[i].DisplayFormat.FormatString = "n0";
}
grvData.BestFitColumns();
grvData.ExpandAllGroups();
grvData.Columns[1].Fixed = FixedStyle.Left;
grvData.Columns[2].Fixed = FixedStyle.Left;
grvData.Columns[3].Fixed = FixedStyle.Left;
grvData.Columns[4].Fixed = FixedStyle.Left;
grvData.Columns["LuyKe"].AppearanceCell.BackColor = Color.Yellow;
grvData.Columns["ChenhLech"].AppearanceCell.BackColor = Color.YellowGreen;
}
void loadData()
{
string fcmCode = TextUtils.ToString(cboFCM.EditValue);
int year = TextUtils.ToInt(cboYear.SelectedItem);
txtTotalVT.EditValue = LibQLSX.ExcuteScalar("select sum(isnull(TotalBuy,0) * isnull(Price,0)) FROM [vRequirePartFull_Order] where ProjectCode = '" + fcmCode + "' and isnull([OrderCode],'') <> ''");
DataTable dtLaiVay = loadLaiVay(fcmCode);
DataTable dtMonth = LibIE.Select("select distinct [C_Month] from [V_XNTC_REPORT_ALL] where [C_DTCP_MA] like '" + fcmCode + "%' and [C_YEAR] = " + year);
initTable();
string exp = "NamTruoc";
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
_dtData.Columns.Add("T_" + p, typeof(decimal));
exp += "+ T_" + p;
}
foreach (DataRow r in _dtData.Rows)
{
string sql1 = TextUtils.ToString(r["SqlText1"]);
string sql2 = TextUtils.ToString(r["SqlText2"]);
if (sql1 == "")
{
continue;
}
foreach (DataColumn c in _dtData.Columns)
{
if (c.ColumnName == "NamTruoc")
{
if (sql2 == "")
{
r["NamTruoc"] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] < " + year));
}
else
{
r["NamTruoc"] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] < " + year))
- TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] < " + year));
}
}
if (c.ColumnName.StartsWith("T_"))
{
if (sql2 == "")
{
r[c.ColumnName] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] = " + year + " and [C_Month] = " + c.ColumnName.Replace("T_", "")));
}
else
{
r[c.ColumnName] = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] = " + year + " and [C_Month] = " + c.ColumnName.Replace("T_", "")))
- TextUtils.ToDecimal(LibIE.ExcuteScalar(sql2 + " and C_DTCP_MA like '" + fcmCode + "%' and [C_Year] = " + year + " and [C_Month] = " + c.ColumnName.Replace("T_", "")));
}
}
}
}
#region Nhập chi phí lãi vay thực tế của dự án
DataRow[] drsLaiVay = _dtData.Select("STT = 28");
foreach (DataRow r in drsLaiVay)
{
r["NamTruoc"] = TextUtils.ToDecimal(dtLaiVay.Compute("SUM(LaiGop)", "C_Month <= 12 and C_Year = " + (year - 1)));;
foreach (DataRow row in dtMonth.Rows)
{
int p = TextUtils.ToInt(row["C_Month"]);
decimal cLai = TextUtils.ToDecimal(dtLaiVay.Compute("SUM(LaiGop)", "C_Month = " + p + " and C_Year = " + year));
r["T_" + p] = cLai;
}
}
#endregion
_dtData.Columns.Add("LuyKe", typeof(decimal), exp);
_dtData.Columns.Add("ChenhLech", typeof(decimal), "C_Value - (" + exp + ")");
//_dtData.Columns["PK_ID"].Caption = "PK_ID";
_dtData.Columns["Code"].Caption = "STT";
_dtData.Columns["Name"].Caption = "Tên";
//_dtData.Columns["GroupCode"].Caption = "Nhóm";
_dtData.Columns["C_Value"].Caption = "Tổng tiền";
_dtData.Columns["NamTruoc"].Caption = "Năm trước";
_dtData.Columns["LuyKe"].Caption = "Lũy kế";
_dtData.Columns["ChenhLech"].Caption = "Chênh lệch (DK-TT)";
grvData.PopulateColumns(_dtData);
grdData.DataSource = _dtData;
grvData.Columns["STT"].Visible = false;
grvData.Columns["SqlText1"].Visible = false;
grvData.Columns["SqlText2"].Visible = false;
for (int i = 4; i < _dtData.Columns.Count; i++)
{
grvData.Columns[i].DisplayFormat.FormatType = FormatType.Numeric;
grvData.Columns[i].DisplayFormat.FormatString = "n0";
}
grvData.BestFitColumns();
grvData.Columns["Code"].Fixed = FixedStyle.Left;
grvData.Columns["Name"].Fixed = FixedStyle.Left;
//grvData.Columns["SqlText1"].Fixed = FixedStyle.Left;
//grvData.Columns["SqlText2"].Fixed = FixedStyle.Left;
//grvData.Columns["Group"].Fixed = FixedStyle.Left;
grvData.Columns["C_Value"].Fixed = FixedStyle.Left;
grvData.Columns["NamTruoc"].Fixed = FixedStyle.Left;
grvData.Columns["Group"].GroupIndex = 0;
grvData.Columns["Group"].Visible = false;
grvData.Columns["LuyKe"].AppearanceCell.BackColor = Color.Yellow;
grvData.Columns["ChenhLech"].AppearanceCell.BackColor = Color.YellowGreen;
grvData.Columns["LuyKe"].SummaryItem.FieldName = "LuyKe";
grvData.Columns["LuyKe"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
grvData.Columns["LuyKe"].SummaryItem.DisplayFormat = "{0:n0}";
grvData.GroupSummary.Add(DevExpress.Data.SummaryItemType.Sum, "LuyKe", grvData.Columns["LuyKe"], "{0:n0}");
grvData.GroupSummary.Add(DevExpress.Data.SummaryItemType.Sum, "NamTruoc", grvData.Columns["NamTruoc"], "{0:n0}");
foreach (DataRow row in dtMonth.Rows)
{
string p = TextUtils.ToString(row["C_Month"]);
grvData.GroupSummary.Add(DevExpress.Data.SummaryItemType.Sum, "T_" + p, grvData.Columns["T_" + p], "{0:n0}");
grvData.Columns["T_" + p].Width = 90;
}
grvData.Columns["NamTruoc"].Width = 100;
grvData.Columns["Code"].Width = 80;
grvData.Columns["LuyKe"].Width = 200;
grvData.ExpandAllGroups();
}
private void btnExportDetail_Click(object sender, EventArgs e)
{
string projectCode = TextUtils.ToString(grvData.GetFocusedRowCellValue(colProjectCode));
if (projectCode == "")
{
return;
}
string sql = "exec spProjectProblemAll -1,'" + projectCode + "', '', 0";
DataTable dt = LibQLSX.Select(sql);
if (dt.Rows.Count == 0)
{
return;
}
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\VanDeChiTiet - " + projectCode + ".xlsx";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongDuAn\\VanDeChiTiet.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch
{
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];
string nguoiPhuTrach = TextUtils.ToString(LibQLSX.ExcuteScalar("select UserName from vProject where ProjectCode = '" + projectCode + "'"));
for (int i = 0; i < dt.Rows.Count; i++)
{
workSheet.Cells[4, 1] = TextUtils.ToString(dt.Rows[i]["ProjectCode"]);
workSheet.Cells[4, 2] = TextUtils.ToString(dt.Rows[i]["ProjectName"]);
workSheet.Cells[4, 3] = nguoiPhuTrach;// TextUtils.ToString(dt.Rows[i]["UserName"]);
workSheet.Cells[4, 4] = TextUtils.ToString(dt.Rows[i]["DatePhatSinh"]);
workSheet.Cells[4, 5] = TextUtils.ToString(dt.Rows[i]["ModuleCode"]);
workSheet.Cells[4, 6] = TextUtils.ToString(dt.Rows[i]["Content"]);
workSheet.Cells[4, 7] = TextUtils.ToString(dt.Rows[i]["DName"]);
workSheet.Cells[4, 8] = TextUtils.ToString(dt.Rows[i]["Reason"]);
workSheet.Cells[4, 9] = TextUtils.ToString(dt.Rows[i]["DName"]);
workSheet.Cells[4, 10] = TextUtils.ToString(dt.Rows[i]["UserName"]);
//workSheet.Cells[4, 11] = TextUtils.ToString(dt.Rows[i][""]);
workSheet.Cells[4, 12] = TextUtils.ToString(dt.Rows[i]["StatusText"]) == "" ? "Chưa xử lý" : TextUtils.ToString(dt.Rows[i]["StatusText"]);
//workSheet.Cells[4, 13] = TextUtils.ToString(dt.Rows[i][""]);
//workSheet.Cells[4, 14] = TextUtils.ToString(dt.Rows[i][""]);
((Excel.Range)workSheet.Rows[4]).Insert();
}
((Excel.Range)workSheet.Rows[3]).Delete();
((Excel.Range)workSheet.Rows[3]).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 btnAddModule_Click(object sender, EventArgs e)
{
#region Check
if (txtModuleCode.Text.Trim() == "")
{
MessageBox.Show("Bạn phải thêm mã module!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtModuleName.Text.Trim() == "")
{
MessageBox.Show("Bạn phải thêm tên module!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtManufacture.Text.Trim() == "")
{
MessageBox.Show("Bạn phải thêm hãng!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (txtOrigin.Text.Trim() == "")
{
MessageBox.Show("Bạn phải thêm xuất xứ!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (TextUtils.ToDecimal(txtPriceVTSX.EditValue) == 0 && cboProduct.EditValue != null)
{
MessageBox.Show("Bạn phải điền giá vật tư sản xuất!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (TextUtils.ToDecimal(txtQtyT.EditValue) == 0)
{
MessageBox.Show("Bạn phải điền số lượng!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (cboProductGroup.EditValue == null && cboProduct.EditValue != null)
{
MessageBox.Show("Bạn phải chọn một nhóm sản phẩm!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#endregion
C_QuotationDetail_KDModel item = new C_QuotationDetail_KDModel();
item.C_QuotationID = Quotation.ID;
item.ParentID = TextUtils.ToInt(cboProduct.EditValue);
item.C_ProductGroupID = TextUtils.ToInt(cboProductGroup.EditValue);
//if (item.C_ProductGroupID > 0)
//{
item.PriceVT = TextUtils.ToDecimal(txtPriceVTSX.EditValue);
//}
string sql = "select top 1 " + (Quotation.DepartmentId == "D018" ? "CustomerPercentKD1" : "CustomerPercentKD2")
+ " from C_ProductGroup where ID = " + item.C_ProductGroupID;
item.PercentXLKH = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sql));
if (item.ParentID > 0)
{
item.QtyT = TextUtils.ToDecimal(txtQtyT.EditValue);
item.Qty = TextUtils.ToDecimal(txtQtyT.EditValue) * TextUtils.ToDecimal(grvCboProduct.GetFocusedRowCellValue(colPQty));
C_QuotationDetail_KDModel itemP = (C_QuotationDetail_KDModel)C_QuotationDetail_KDBO.Instance.FindByPK(item.ParentID);
itemP.C_ProductGroupID = 0;
C_QuotationDetail_KDBO.Instance.Update(itemP);
}
else
{
item.Qty = item.QtyT = TextUtils.ToDecimal(txtQtyT.EditValue);
}
item.Manufacture = txtManufacture.Text.Trim();
item.ModuleCode = item.ModuleCodeHD = txtModuleCode.Text.Trim().ToUpper();
item.ModuleName = item.ModuleNameHD = txtModuleName.Text.Trim();
item.Origin = txtOrigin.Text.Trim();
item.VAT = TextUtils.ToDecimal(txtVAT.EditValue);
item.ID = (int)C_QuotationDetail_KDBO.Instance.Insert(item);
//if (item.ParentID > 0)
//{
// C_QuotationDetail_KDModel itemP = (C_QuotationDetail_KDModel)C_QuotationDetail_KDBO.Instance.FindByPK(item.ParentID);
// //itemP.PriceVTSX += item.PriceVTSX;
// //itemP.PriceVTLD += item.PriceVTLD;
// //itemP.PriceVTPS += item.PriceVTPS;
// //itemP.PriceVTTN += item.PriceVTTN;
// itemP.PriceVT += item.PriceVT;
// C_QuotationDetail_KDBO.Instance.Update(itemP);
// //calculateCost(item);
//}
MessageBox.Show("Lưu trữ thành công.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
loadProduct();
loadQuotationItem();
txtPriceVTSX.EditValue = 0;
txtQtyT.EditValue = 1;
txtModuleName.Text = txtModuleCode.Text = "";
}
private void btnCheckHoaDon_Click(object sender, EventArgs e)
{
grvData.ExpandAllGroups();
for (int i = 0; i < grvData.RowCount; i++)
{
string completeDate = TextUtils.ToString(grvData.GetRowCellValue(i, colCompletedDate));
int payVoucherID = TextUtils.ToInt(grvData.GetRowCellValue(i, colPayVoucherID));
string poCode = TextUtils.ToString(grvData.GetRowCellValue(i, colItemCode));
int caseVoucherDebtID = TextUtils.ToInt(grvData.GetRowCellValue(i, colID));
if (caseVoucherDebtID == 0)
{
continue;
}
if (completeDate != "")
{
continue;
}
if (payVoucherID == 1)//Nợ hóa đơn
{
string sqlTotalVat = "SELECT TotalVAT = case when dbo.vOrderNew.IsTranferAfferVAT = 1 then isnull(dbo.vOrderNew.VAT,10)*dbo.vOrderNew.TotalPrice /100" +
" else isnull(dbo.vOrderNew.VAT,10)*(dbo.vOrderNew.TotalPrice+dbo.vOrderNew.DeliveryCost)/100 end " +
" from vOrderNew with(nolock) where vOrderNew.OrderCode = '" + poCode + "'";
string sqlInvoice = "select sum(T_XNTC.C_PSNO) FROM T_XNTC INNER JOIN T_DM_VUVIEC ON T_XNTC.FK_VUVIEC = T_DM_VUVIEC.PK_ID" +
" WHERE (T_DM_VUVIEC.C_MA = '" + poCode + "') AND (T_XNTC.FK_TKNO LIKE '133%')";
decimal totalVAT = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlTotalVat));
decimal totalInvoice = TextUtils.ToDecimal(LibIE.ExcuteScalar(sqlInvoice));
decimal chenhLech = totalVAT - totalInvoice;
if (chenhLech <= 10 && chenhLech >= -10)
{
string sqlDateTra = "select top 1 C_NGAYLAP FROM T_XNTC INNER JOIN T_DM_VUVIEC ON T_XNTC.FK_VUVIEC = T_DM_VUVIEC.PK_ID" +
" WHERE (T_DM_VUVIEC.C_MA = '" + poCode + "') AND (T_XNTC.FK_TKNO LIKE '133%')" +
" order by C_NGAYLAP desc";
DateTime?dateNgayTra = TextUtils.ToDate2(LibIE.ExcuteScalar(sqlDateTra));
if (dateNgayTra.HasValue)
{
string sqlUpdate = "update CaseVoucherDebt set CompletedDate = '" + dateNgayTra.Value.ToString("yyyy-MM-dd") + "' where ID = " + caseVoucherDebtID;
LibQLSX.ExcuteSQL(sqlUpdate);
}
}
if (chenhLech < -10)
{
string sqlUpdateChenhLech = "update CaseVoucherDebt set TotalDiff = " + chenhLech + " where ID = " + caseVoucherDebtID;
LibQLSX.ExcuteSQL(sqlUpdateChenhLech);
}
}
if (payVoucherID == 2)//Nợ báo giá
{
string sql = "SELECT TOP 1 [C_NGAYLAP]"
+ " FROM [tanphat].[dbo].[V_XNTC_VUVIEC] with (nolock)"
+ " where [C_MA] = '" + poCode + "' and FK_TKNO LIKE '133%' and [FK_PHANXUONG] = 115";
DateTime?dateNgayLap = TextUtils.ToDate2(LibIE.ExcuteScalar(sql));
if (dateNgayLap.HasValue)
{
string sqlUpdate = "update CaseVoucherDebt set CompletedDate = '" + dateNgayLap.Value.ToString("yyyy-MM-dd") + "' where ID = " + caseVoucherDebtID;
LibQLSX.ExcuteSQL(sqlUpdate);
}
}
}
loadData();
MessageBox.Show("Hoàn thành Check báo giá, hóa đơn", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnSave_Click(object sender, EventArgs e)
{
bool _isSaved = false;
if (treeData.AllNodesCount == 0)
{
return;
}
treeData.ExpandAll();
treeData.FocusedNode = null;
DataTable dtSource = (DataTable)treeData.DataSource;
DataRow[] drsVatTu = dtSource.Select("(PriceVT is null or PriceVT = 0) and (C_ProductGroupID > 0 and ParentID > 0)");
if (drsVatTu.Length > 0)
{
MessageBox.Show("Bạn chưa nhập đủ VẬT TƯ SẢN XUẤT cho các hạng mục.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
int costID = (Quotation.DepartmentId == "D018" ? 73 : 72);//Phòng KD1->lấy ra chi phí nhân công KD2 không thì lấy chi phí nhân công KD1
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang xử lý..."))
{
#region Save item
for (int i = 0; i < treeData.AllNodesCount; i++)
{
int id = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colDetailID));
C_QuotationDetail_SXModel item = (C_QuotationDetail_SXModel)C_QuotationDetail_SXBO.Instance.FindByPK(id);
item.ModuleCode = TextUtils.ToString(treeData.GetNodeByVisibleIndex(i).GetValue(colModuleCode));
item.ModuleName = TextUtils.ToString(treeData.GetNodeByVisibleIndex(i).GetValue(colModuleName));
item.PriceVTLD = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceVTLD));
item.PriceVTPS = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceVTPS));
item.PriceVT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceVTSX));
item.PriceVTTN = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceVTTN));
item.PriceHD = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceHD));
item.C_ProductGroupID = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colProductGroupID));
item.VAT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colVAT));
item.QtyT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colQtyT));
string sqlPB = "select sum(ValuePercentSX) from vC_CostProductGroupLinkNew where C_CostGroupNew_Code in ('N01','N08','N09','N11') and C_ProductGroupID = " + item.C_ProductGroupID + " and [C_VersionID] = " + Quotation.C_VersionID;
string sqlNC = "select sum(ValuePercentSX) from vC_CostProductGroupLinkNew where C_CostGroupNew_Code in ('N02','N03','N04','N07.02','N07.03') and C_ProductGroupID = " + item.C_ProductGroupID + " and [C_VersionID] = " + Quotation.C_VersionID;
decimal totalPercentCP_PB = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlPB)) / 100;
decimal totalPercentCP_NC = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlNC)) / 100;
item.TotalPercentCP_NC = totalPercentCP_NC;
item.TotalPercentCP_PB = totalPercentCP_PB;
int parentID = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colParentID));
if (parentID == 0)
{
item.Qty = item.QtyT;
}
else
{
C_QuotationDetail_SXModel parent = (C_QuotationDetail_SXModel)C_QuotationDetail_SXBO.Instance.FindByPK(parentID);
item.Qty = item.QtyT * parent.Qty;
}
item.UpdatedDate = DateTime.Now;
item.UpdatedBy = Global.AppUserName;
C_QuotationDetail_SXBO.Instance.Update(item);
}
#endregion
DataTable dtCostVT = LibQLSX.Select("select sum(isnull(Qty,0)*(isnull(PriceVT,0)+isnull(PriceVTTN,0)+isnull(PriceVTPS,0)+isnull(PriceVTLD,0)))"
+ ", sum(isnull(Qty,0)*isnull(TotalPercentCP_NC,0))"
//+ ", sum(isnull(Qty,0)*isnull(TotalPercentCP_PB,0))"
+ " from C_QuotationDetail_SX where (ParentID > 0 or (ParentID = 0 and C_ProductGroupID > 0)) and [C_QuotationID] = " + Quotation.ID);
decimal totalAllVT = dtCostVT.Rows.Count > 0 ? TextUtils.ToDecimal(dtCostVT.Rows[0][0]) : 0;
decimal totalAllPercentCP_NC = dtCostVT.Rows.Count > 0 ? TextUtils.ToDecimal(dtCostVT.Rows[0][1]) : 0;
//decimal totalAllPercentCP_PB = dtCostVT.Rows.Count > 0 ? TextUtils.ToDecimal(dtCostVT.Rows[0][2]) : 0;
if (Quotation.StatusNC == 1)
{
#region Tính nhân công theo % phân bổ
for (int i = 0; i < treeData.AllNodesCount; i++)
{
int id = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colDetailID));
C_QuotationDetail_SXModel item = (C_QuotationDetail_SXModel)C_QuotationDetail_SXBO.Instance.FindByPK(id);
if (item.ParentID == 0 && item.C_ProductGroupID == 0)
{
continue;
}
decimal profitPercent = TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select top 1 ProfitPercentSX from C_ProductGroup where ID = " + item.C_ProductGroupID)) / 100;
decimal percentPB = item.TotalPercentCP_NC + item.TotalPercentCP_PB;
decimal totalVT = item.PriceVT + item.PriceVTLD + item.PriceVTPS + item.PriceVTTN;
decimal tyle = (item.TotalPercentCP_NC + totalVT) / (totalAllPercentCP_NC + totalAllVT);
item.TotalDP = Quotation.TotalDP_SX * tyle; //dự phòng
item.TotalVC = Quotation.TotalCPVCHB_C13 * tyle; //vận chuyển
item.TotalBX = Quotation.TotalBXHB_C52 * tyle; //bốc xếp
item.PriceDiLai = Quotation.TotalDiLai * tyle; //Đi lại
item.TotalPB_DA = item.TotalDP + item.TotalVC + item.TotalBX + item.PriceDiLai;
item.PriceCP = item.TotalPB_DA + totalVT;
item.PriceTPA = item.PriceCP * (1 + profitPercent) / (1 - percentPB * (1 + profitPercent));
item.TotalPB = item.TotalPercentCP_PB / 100 * item.PriceTPA;
item.TotalNC = item.TotalPercentCP_NC / 100 * item.PriceTPA;
item.TotalProfit = profitPercent * (item.PriceCP + (percentPB * item.PriceTPA));
C_QuotationDetail_SXBO.Instance.Update(item);
calculateCost_PB(item);
}
#endregion
}
else
{
#region Tính nhân công theo công nhật
decimal totalAllNC = TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select sum(isnull(Qty,0) * isnull(Price,0)) from vC_CostQuotationItemLinkSX"
+ " where IsDirect = 1 and [C_QuotationID] = " + Quotation.ID));
for (int i = 0; i < treeData.AllNodesCount; i++)
{
int id = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colDetailID));
C_QuotationDetail_SXModel item = (C_QuotationDetail_SXModel)C_QuotationDetail_SXBO.Instance.FindByPK(id);
if (item.ParentID == 0 && item.C_ProductGroupID == 0)
{
continue;
}
decimal profitPercent = TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select top 1 ProfitPercentSX from C_ProductGroup where ID = " + item.C_ProductGroupID)) / 100;
decimal percentPB = item.TotalPercentCP_PB;
decimal totalVT = item.PriceVT + item.PriceVTLD + item.PriceVTPS + item.PriceVTTN;
decimal totalNC = TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select sum(Price) from C_CostQuotationItemLinkNew where IsDirect = 1 and C_QuotationDetail_SXID = " + item.ID));
item.TotalNC = totalNC;
item.TotalDP = Quotation.TotalDP_SX * (item.TotalNC + totalVT) / (totalAllNC + totalAllVT); //dự phòng
item.TotalVC = Quotation.TotalCPVCHB_C13 * (item.TotalNC + totalVT) / (totalAllNC + totalAllVT); //vận chuyển
item.TotalBX = Quotation.TotalBXHB_C52 * (item.TotalNC + totalVT) / (totalAllNC + totalAllVT); //bốc xếp
item.PriceDiLai = Quotation.TotalDiLai * (item.TotalNC + totalVT) / (totalAllNC + totalAllVT); //Đi lại
item.TotalPB_DA = item.TotalDP + item.TotalVC + item.TotalBX;
item.PriceCP = item.TotalNC + item.TotalPB_DA + totalVT;
item.PriceTPA = item.PriceCP * (1 + profitPercent) / (1 - percentPB * (1 + profitPercent));
item.TotalPB = percentPB * item.PriceTPA;
//item.TotalNC = item.TotalPercentCP_NC * item.PriceTPA;
item.TotalProfit = profitPercent * (item.PriceCP + (percentPB * item.PriceTPA));
C_QuotationDetail_SXBO.Instance.Update(item);
calculateCost_PB(item);
}
#endregion
}
#region Save parent
ArrayList listParent = C_QuotationDetail_SXBO.Instance.FindByExpression(new Expression("ParentID", 0)
.And(new Expression("C_ProductGroupID", 0))
.And(new Expression("C_QuotationID", Quotation.ID)));
if (listParent != null)
{
foreach (var item in listParent)
{
C_QuotationDetail_SXModel itemP = (C_QuotationDetail_SXModel)item;
DataTable dtChild = LibQLSX.Select("select PriceVTLD=Sum(QtyT*PriceVTLD),PriceVTPS=Sum(QtyT*PriceVTPS),PriceVT=Sum(QtyT*PriceVT),PriceVTTN=Sum(QtyT*PriceVTTN)"
+ ",TotalNC=Sum(QtyT*TotalNC),TotalPB_DA=Sum(QtyT*TotalPB_DA),TotalPB=Sum(QtyT*TotalPB),PriceCP=Sum(QtyT*PriceCP),PriceTPA=Sum(QtyT*PriceTPA),PriceHD=Sum(QtyT*PriceHD)"
+ ",TotalProfit=Sum(QtyT*TotalProfit)"
+ ",TotalDP=Sum(QtyT*TotalDP),TotalVC=Sum(QtyT*TotalVC),TotalBX=Sum(QtyT*TotalBX) from C_QuotationDetail_SX where ParentID = " + itemP.ID);
itemP.PriceVTLD = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceVTLD"]) : itemP.PriceVTLD;
itemP.PriceVTPS = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceVTPS"]) : itemP.PriceVTPS;
itemP.PriceVT = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceVT"]) : itemP.PriceVT;
itemP.PriceVTTN = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceVTTN"]) : itemP.PriceVTTN;
itemP.TotalNC = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalNC"]) : itemP.TotalNC;
itemP.TotalPB_DA = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalPB_DA"]) : itemP.TotalPB_DA;
itemP.TotalPB = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalPB"]) : itemP.TotalPB;
itemP.PriceCP = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceCP"]) : itemP.PriceCP;
itemP.PriceTPA = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceTPA"]) : itemP.PriceTPA;
itemP.PriceHD = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["PriceHD"]) : itemP.PriceHD;
itemP.TotalProfit = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalProfit"]) : itemP.TotalProfit;
itemP.TotalDP = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalDP"]) : itemP.TotalDP;
itemP.TotalVC = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalVC"]) : itemP.TotalVC;
itemP.TotalBX = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0]["TotalBX"]) : itemP.TotalBX;
C_QuotationDetail_SXBO.Instance.Update(itemP);
}
}
#endregion
loadQuotationItem();
_isSaved = true;
}
MessageBox.Show("Lưu trữ thành công.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
if (_isSaved)
{
if (this.LoadDataChange != null)
{
this.LoadDataChange(null, null);
}
}
}
private void btnCreateDirection_Click(object sender, EventArgs e)
{
DocUtils.InitFTPQLSX();
grvData.FocusedRowHandle = -1;
DataTable dtDirectionType = LibQLSX.Select("SELECT * FROM [ProjectDirectionType]");
DataTable dtMaterialModel = LibQLSX.Select("SELECT * FROM [MaterialsModel]");
string projectCode = TextUtils.ToString(grvProject.GetFocusedRowCellValue(colProjectCode));
DataRow[] drs = _dtData.Select("Check = 1 and ProjectDirectionID is null");
if (drs.Length == 0)
{
return;
}
//ProcessTransaction pt = new ProcessTransaction();
//pt.OpenConnection();
//pt.BeginTransaction();
//try
//{
ProjectDirectionModel direction = new ProjectDirectionModel();
direction.Code = "";
direction.Name = "Chỉ thị ngày: " + DateTime.Now.ToString("dd/MM/yyyy");
direction.ProjectCode = projectCode;
direction.ProjectId = TextUtils.ToString(cboProject.EditValue);
direction.CreatedBy = Global.AppUserName;
direction.CreatedDate = DateTime.Now;
direction.ID = (int)ProjectDirectionBO.Instance.Insert(direction);
for (int i = 0; i < drs.Length; i++)
{
//DateTime maxDate = new DateTime(1990, 1, 1);
//string statusText = TextUtils.ToString(drs[i]["StatusText"]);
//if (statusText != "") continue;
string moduleCode = TextUtils.ToString(drs[i]["ProjectModuleCode"]);
decimal parentQty = TextUtils.ToDecimal(drs[i]["TotalReal"]);
DateTime?dateAboutE = TextUtils.ToDate2(drs[i]["DateAboutE"]);
string projectModuleId = TextUtils.ToString(drs[i]["ProjectModuleId"]);
string note = TextUtils.ToString(drs[i]["Note"]);
DataTable dtLink = new DataTable();
dtLink = TextUtils.GetDMVT(moduleCode, true);
DataRow[] drsL = dtLink.Select("F3 = 'TPA'");
if (drsL.Length > 0)
{
dtLink = drsL.CopyToDataTable();
}
else
{
continue;
}
//dtLink = LibQLSX.Select("select * from vMaterialModuleLink with(nolock) where ThongSo = 'TPA' and ModuleCode = '" + 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[] array = DocUtils.GetContentList(serverPathMat);
#region Update ProjectModule
ArrayList arr = ProjectModuleBO.Instance.FindByAttribute("ProjectModuleId", projectModuleId);
if (arr.Count > 0)
{
ProjectModuleModel model = (ProjectModuleModel)arr[0];
model.ProjectDirectionID = direction.ID;
ProjectModuleBO.Instance.UpdateQLSX(model);
}
#endregion
#region In phim
if (array != null)
{
for (int i1 = 0; i1 < array.Length; i1++)
{
//try
//{
string partsCode = array[i1].Split('-')[0];
DataRow[] drsLink = dtLink.Select("F4 = '" + partsCode + "' or F4 like '" + partsCode + "-%'");
decimal qty = parentQty * (drsLink.Length > 0 ? TextUtils.ToDecimal(drsLink[0]["QtyReal"]) : 1);
//DataTable dtDetail = LibQLSX.Select("select top 1 * from ProjectDirectionDetail where ProjectDirectionTypeID = 2 and PartsCode = '"
// + partsCode + "' and ProjectDirectionID = " + direction.ID);
//if (dtDetail.Rows.Count > 0)
//{
// ProjectDirectionDetailModel detail = (ProjectDirectionDetailModel)ProjectDirectionDetailBO.Instance.FindByPK(TextUtils.ToInt(dtDetail.Rows[0]["ID"]));
// detail.Qty += qty;
// ProjectDirectionDetailBO.Instance.Update(detail);
//}
//else
//{
ProjectDirectionDetailModel detail = new ProjectDirectionDetailModel();
detail.ProjectModuleId = projectModuleId;
detail.ProjectDirectionID = direction.ID;
//projectDirection.Material = TextUtils.ToString(dtLink.Rows[j]["VatLieu"]);
detail.ModuleCode = moduleCode;
detail.PartsCode = partsCode;
//projectDirection.PartsName = TextUtils.ToString(dtLink.Rows[j]["PartsName"]);
detail.ProjectCode = projectCode;
detail.Qty = qty;
//projectDirection.STT = TextUtils.ToString(dtLink.Rows[j]["STT"]);
detail.ThongSo = "TPA";
//projectDirection.Unit = TextUtils.ToString(dtLink.Rows[j]["Unit"]);
detail.UserId = "";
detail.ProjectDirectionTypeID = 2;//In
DataRow[] drsDirectionType = dtDirectionType.Select("ID = " + detail.ProjectDirectionTypeID);
detail.MakeTime = TextUtils.ToDecimal(drsDirectionType[0]["TimeDK"]);
detail.FilePath = serverPathMat + "/" + array[i1];
detail.FileName = array[i1];
detail.StartDateDK = null; //dateAboutE;
detail.EndDateDK = null; //dateAboutE != null ? dateAboutE.Value.AddHours((double)(parentQty * detail.MakeTime)) : dateAboutE;
detail.StartDate = null;
detail.EndDate = null;
detail.IsNew = 0;
detail.IsDeleted = 0;
detail.CreatedBy = Global.AppUserName;
detail.CreatedDate = DateTime.Now;
detail.Note = note;
ProjectDirectionBO.Instance.Insert(detail);
//}
//}
//catch (Exception)
//{
// throw;
//}
}
}
#endregion
#region Other
for (int j = 0; j < dtLink.Rows.Count; j++)
{
//try
//{
string partsCode = TextUtils.ToString(dtLink.Rows[j]["F4"]);
decimal qty = parentQty * TextUtils.ToDecimal(dtLink.Rows[j]["QtyReal"]);
string material = TextUtils.ToString(dtLink.Rows[j]["F8"]);
int projectDirectionTypeID = TextUtils.ToInt(dtLink.Rows[j]["ProjectDirectionTypeID"]);
if (partsCode.StartsWith("PCB"))
{
projectDirectionTypeID = 4;
}
//else
//{
// DataRow[] drsMaterialModel = dtMaterialModel.Select("MaterialsId = '" + material + "'");
// projectDirectionTypeID = drsMaterialModel.Length > 0 ? 3 : TextUtils.ToInt(drsMaterialModel[0]["ProjectDirectionTypeID"]);
//}
//DataTable dtDetail = LibQLSX.Select("select top 1 * from ProjectDirectionDetail where ProjectDirectionTypeID = " + projectDirectionTypeID + " and PartsCode = '"
// + partsCode + "' and ProjectDirectionID = " + direction.ID);
//if (dtDetail.Rows.Count > 0)
//{
// ProjectDirectionDetailModel detail = (ProjectDirectionDetailModel)ProjectDirectionDetailBO.Instance.FindByPK(TextUtils.ToInt(dtDetail.Rows[0]["ID"]));
// detail.Qty += qty;
// ProjectDirectionDetailBO.Instance.Update(detail);
//}
//else
//{
ProjectDirectionDetailModel detail = new ProjectDirectionDetailModel();
detail.ProjectModuleId = projectModuleId;
detail.ProjectDirectionID = direction.ID;
detail.Material = material;
detail.ModuleCode = moduleCode;
detail.PartsCode = partsCode;
detail.PartsName = TextUtils.ToString(dtLink.Rows[j]["F2"]);
detail.ProjectCode = projectCode;
detail.Qty = qty;
detail.STT = TextUtils.ToString(dtLink.Rows[j]["F1"]);
detail.ThongSo = "TPA";
//detail.Unit = TextUtils.ToString(dtLink.Rows[j]["F6"]);
//detail.MaVatLieu = TextUtils.ToString(dtLink.Rows[j]["F5"]);
detail.MaVatLieu = TextUtils.ToString(dtLink.Rows[j]["F5"]);
string unitPart = TextUtils.ToString(LibQLSX.ExcuteScalar("select top 1 Unit from Parts where PartsCode = N'" + detail.MaVatLieu + "'"));
detail.Unit = unitPart;
detail.UserId = "";
detail.ProjectDirectionTypeID = projectDirectionTypeID;
if (detail.PartsCode.StartsWith("PCB"))
{
string serverPathPCB = string.Format("/Thietke.Dt/PCB/{0}/PRD.{0}/", detail.PartsCode);
detail.FilePath = serverPathPCB + "TPAT." + detail.PartsCode.Substring(4, 7) + ".doc";//PCB.B060101
detail.FileName = "TPAT." + detail.PartsCode.Substring(4, 7) + ".doc";
detail.StartDateDK = dateAboutE;
detail.EndDateDK = dateAboutE != null?dateAboutE.Value.AddHours((double)(parentQty *detail.MakeTime)) : dateAboutE;
}
else
{
detail.FilePath = serverPathCad + "/" + detail.PartsCode + ".dwg";
detail.FileName = detail.PartsCode + ".dwg";
detail.StartDateDK = null;
detail.EndDateDK = null;
}
DataRow[] drsDirectionType = dtDirectionType.Select("ID = " + detail.ProjectDirectionTypeID);
detail.MakeTime = drsDirectionType.Length > 0 ? TextUtils.ToDecimal(drsDirectionType[0]["TimeDK"]) : 3;
detail.StartDate = null;
detail.EndDate = null;
detail.IsNew = 0;
detail.IsDeleted = 0;
detail.CreatedBy = Global.AppUserName;
detail.CreatedDate = DateTime.Now;
detail.Note = note;
ProjectDirectionBO.Instance.Insert(detail);
//if (maxDate < detail.EndDateDK)
// maxDate = (DateTime)detail.EndDateDK;
//}
//}
//catch (Exception)
//{
// throw;
//}
}
#endregion
#region Lắp ráp
//try
//{
//DataTable dtDetail1 = LibQLSX.Select("select top 1 * from ProjectDirectionDetail where ProjectDirectionTypeID > 4 and PartsCode = '"
// + moduleCode + "' and ProjectDirectionID = " + direction.ID);
//if (dtDetail1.Rows.Count > 0)
//{
// ProjectDirectionDetailModel detail = (ProjectDirectionDetailModel)ProjectDirectionDetailBO.Instance.FindByPK(TextUtils.ToInt(dtDetail1.Rows[0]["ID"]));
// detail.Qty += parentQty;
// ProjectDirectionDetailBO.Instance.Update(detail);
//}
//else
//{
ProjectDirectionDetailModel directionDetail = new ProjectDirectionDetailModel();
directionDetail.ProjectModuleId = projectModuleId;
directionDetail.ProjectDirectionID = direction.ID;
//directionDetail.Material = TextUtils.ToString(dtLink.Rows[j]["VatLieu"]);
directionDetail.ModuleCode = moduleCode;
directionDetail.PartsCode = moduleCode;
directionDetail.PartsName = TextUtils.ToString(drs[i]["ProjectModuleName"]);
directionDetail.ProjectCode = projectCode;
directionDetail.Qty = parentQty;
//directionDetail.STT = TextUtils.ToString(dtLink.Rows[j]["STT"]);
directionDetail.ThongSo = "TPA";
directionDetail.Unit = "BỘ";
directionDetail.UserId = "";
directionDetail.FilePath = "";
DataRow[] drsDirectionType1 = dtDirectionType.Select("Code = '" + moduleCode.Substring(0, 6) + "'");
if (drsDirectionType1.Length > 0)
{
directionDetail.ProjectDirectionTypeID = TextUtils.ToInt(drsDirectionType1[0]["ID"]);
directionDetail.MakeTime = TextUtils.ToDecimal(drsDirectionType1[0]["TimeDK"]);
}
else
{
directionDetail.MakeTime = 0;
}
directionDetail.StartDateDK = null; //maxDate;
directionDetail.EndDateDK = null; //directionDetail.StartDateDK != null ? directionDetail.StartDateDK.Value.AddHours((double)(directionDetail.Qty * directionDetail.MakeTime)) : directionDetail.StartDateDK;
directionDetail.StartDate = null;
directionDetail.EndDate = null;
directionDetail.IsNew = 0;
directionDetail.IsDeleted = 0;
directionDetail.CreatedBy = Global.AppUserName;
directionDetail.CreatedDate = DateTime.Now;
directionDetail.Note = note;
ProjectDirectionBO.Instance.Insert(directionDetail);
//}
//}
//catch (Exception)
//{
// throw;
//}
#endregion
}
//pt.CommitTransaction();
MessageBox.Show("Tạo chỉ thị thành công", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
frmProjectDirection frm = new frmProjectDirection();
frm.ProjectDirectionID = direction.ID;
frm.Show();
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
//}
//finally
//{
// pt.CloseConnection();
//}
}
void saveCN(ProcessTransaction pt, int count, DataRow row)
{
int stt = TextUtils.ToInt(row["F1"]);
decimal qty = TextUtils.ToDecimal(row["F7"]);
C_QuotationDetail_SXModel item = new C_QuotationDetail_SXModel();
item.C_QuotationID = Quotation.ID;
item.ParentID = 0;
item.Qty = item.QtyT = qty;
item.PriceVT = TextUtils.ToDecimal(row["F47"]);
item.PriceVTTN = TextUtils.ToDecimal(row["F48"]);
item.PriceVTPS = TextUtils.ToDecimal(row["F49"]);
item.Manufacture = TextUtils.ToString(row["F4"]);
item.ModuleCode = TextUtils.ToString(row["F3"]);
item.ModuleName = TextUtils.ToString(row["F2"]);
item.Origin = TextUtils.ToString(row["F5"]);
item.C_ProductGroupID = TextUtils.ToInt(LibQLSX.ExcuteScalar("select ID from C_ProductGroup where Code = '" + TextUtils.ToString(row["F6"]) + "'"));
item.ID = (int)pt.Insert(item);
count++;
row["ID"] = item.ID;
DataRow[] drs = _dtData.Select("F1 like '" + stt + ".%'");
foreach (DataRow rowC in drs)
{
decimal qtyC = TextUtils.ToDecimal(rowC["F7"]);
string groupCode = TextUtils.ToString(rowC["F6"]);
int groupID = TextUtils.ToInt(LibQLSX.ExcuteScalar("select ID from C_ProductGroup where Code = '" + groupCode + "'"));
C_QuotationDetail_SXModel itemC = new C_QuotationDetail_SXModel();
itemC.C_QuotationID = Quotation.ID;
itemC.ParentID = item.ID;
itemC.C_ProductGroupID = groupID;
itemC.Qty = qtyC;
itemC.QtyT = qtyC / item.Qty;
itemC.PriceVT = TextUtils.ToDecimal(rowC["F47"]);
itemC.PriceVTTN = TextUtils.ToDecimal(row["F48"]);
itemC.PriceVTPS = TextUtils.ToDecimal(row["F49"]);
itemC.ModuleName = TextUtils.ToString(rowC["F2"]);
itemC.ModuleCode = TextUtils.ToString(rowC["F3"]);
itemC.Manufacture = TextUtils.ToString(rowC["F4"]);
itemC.Origin = TextUtils.ToString(rowC["F5"]);
itemC.ID = (int)pt.Insert(itemC);
rowC["ID"] = itemC.ID;
count++;
#region Chi phí nhân công
//Phòng thiết kê
C_CostQuotationItemLinkNewModel linkP24 = new C_CostQuotationItemLinkNewModel();
linkP24.C_CostID = Quotation.CreatedDepartmentID == 18 ? 101 : 61;
linkP24.C_QuotationDetail_SXID = itemC.ID;
linkP24.NumberDay = TextUtils.ToDecimal(rowC["F10"]);
linkP24.PersonNumber = TextUtils.ToDecimal(rowC["F9"]);
linkP24.CostNCType = TextUtils.ToString(rowC["F12"]);
linkP24.TotalR = linkP24.NumberDay * linkP24.PersonNumber;
linkP24.Price = linkP24.TotalR * TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Price from C_CostNCType where C_CostID = "
+ linkP24.C_CostID + " and CostNCType = '" + linkP24.CostNCType + "'"));//pricePerDay;
linkP24.IsDirect = 1;
pt.Insert(linkP24);
//Phòng SXLR
C_CostQuotationItemLinkNewModel linkP07 = new C_CostQuotationItemLinkNewModel();
linkP07.C_CostID = 64;
linkP07.C_QuotationDetail_SXID = itemC.ID;
linkP07.NumberDay = TextUtils.ToDecimal(rowC["F16"]);
linkP07.PersonNumber = TextUtils.ToDecimal(rowC["F15"]);
linkP07.CostNCType = TextUtils.ToString(rowC["F17"]);
linkP07.TotalR = linkP07.NumberDay * linkP07.PersonNumber;
linkP07.Price = linkP07.TotalR * TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Price from C_CostNCType where C_CostID = "
+ linkP07.C_CostID + " and CostNCType = '" + linkP07.CostNCType + "'")); //pricePerDay;
linkP07.IsDirect = 1;
pt.Insert(linkP07);
//Phòng Service
C_CostQuotationItemLinkNewModel linkP12 = new C_CostQuotationItemLinkNewModel();
linkP12.C_CostID = 63;
linkP12.C_QuotationDetail_SXID = itemC.ID;
linkP12.NumberDay = TextUtils.ToDecimal(rowC["F21"]);
linkP12.PersonNumber = TextUtils.ToDecimal(rowC["F20"]);
linkP12.CostNCType = TextUtils.ToString(rowC["F22"]);
linkP12.TotalR = linkP12.NumberDay * linkP12.PersonNumber;
linkP12.Price = linkP12.TotalR * TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Price from C_CostNCType where C_CostID = "
+ linkP12.C_CostID + " and CostNCType = '" + linkP12.CostNCType + "'"));//pricePerDay;
linkP12.IsDirect = 1;
pt.Insert(linkP12);
//Phòng PLD
C_CostQuotationItemLinkNewModel linkPLD = new C_CostQuotationItemLinkNewModel();
linkPLD.C_CostID = 103;
linkPLD.C_QuotationDetail_SXID = itemC.ID;
linkPLD.NumberDay = TextUtils.ToDecimal(rowC["F30"]);
linkPLD.PersonNumber = TextUtils.ToDecimal(rowC["F29"]);
linkPLD.CostNCType = TextUtils.ToString(rowC["F31"]);
linkPLD.TotalR = linkPLD.NumberDay * linkPLD.PersonNumber;
linkPLD.Price = linkPLD.TotalR * TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Price from C_CostNCType where C_CostID = "
+ linkPLD.C_CostID + " and CostNCType = '" + linkPLD.CostNCType + "'"));//pricePerDay;
linkPLD.IsDirect = 1;
pt.Insert(linkPLD);
//Phòng PCG
C_CostQuotationItemLinkNewModel linkPCG = new C_CostQuotationItemLinkNewModel();
linkPCG.C_CostID = 104;
linkPCG.C_QuotationDetail_SXID = itemC.ID;
linkPCG.NumberDay = TextUtils.ToDecimal(rowC["F35"]);
linkPCG.PersonNumber = TextUtils.ToDecimal(rowC["F34"]);
linkPCG.CostNCType = TextUtils.ToString(rowC["F36"]);
linkPCG.TotalR = linkPCG.NumberDay * linkPCG.PersonNumber;
linkPCG.Price = linkPCG.TotalR * TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Price from C_CostNCType where C_CostID = "
+ linkPCG.C_CostID + " and CostNCType = '" + linkPCG.CostNCType + "'"));//pricePerDay;
linkPCG.IsDirect = 1;
pt.Insert(linkPCG);
#endregion
}
}
void sendKcsMail(int index)
{
string subject = "";
string content = "";
string to = "";
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang load dữ liệu..."))
{
int id = TextUtils.ToInt(grvData.GetRowCellValue(index, colID));
string departmentCodeGL = grvData.GetRowCellValue(index, colDepartmentCodeGL).ToString();
string departmentMailGL = "";
try
{
departmentMailGL = ((DepartmentModel)DepartmentBO.Instance.FindByAttribute("Code", departmentCodeGL)[0]).Email;
}
catch
{
//departmentMailGL = "*****@*****.**";
departmentMailGL = "[email protected];[email protected];[email protected];[email protected];[email protected]";
}
ModulesModel product = (ModulesModel)ModulesBO.Instance.FindByAttribute("Code", grvData.GetRowCellValue(index, colModuleCode).ToString())[0];
string productCode = product.Code;
string projectCode = TextUtils.ToString(grvData.GetRowCellValue(index, colProjectCode));
List <string> listEmail = getListMail(productCode);
#region Mở outlook
int count = Process.GetProcesses().Where(o => o.ProcessName.Contains("OUTLOOK")).Count();
if (count == 0)
{
try
{
Process.Start("outlook.exe");
}
catch (Exception)
{
}
}
#endregion Mở outlook
subject = string.Format("ERROR REPORT - {0} - {1}", TextUtils.ToString(grvData.GetRowCellValue(index, colCode)), productCode);
DataTable dtProjectUser = LibQLSX.Select("SELECT p.ProjectCode, u.UserName FROM Project p INNER JOIN Users u ON p.UserId = u.UserId where p.ProjectCode='" + projectCode + "'");
string projectUser = dtProjectUser.Rows.Count > 0 ? TextUtils.ToString(dtProjectUser.Rows[0][1]) : "";
DataTable dtConfig = TextUtils.Select("select KeyValue from [ConfigSystem] where [KeyName]='KcsToTK_Email'");
content = dtConfig.Rows[0][0].ToString();
content = content.Replace("<ErrorCode>", "<b>" + grvData.GetRowCellValue(index, colCode).ToString() + "</b>")
.Replace("<ProjectCode>", "<b>" + projectCode + "</b>")
.Replace("<ProjectUser>", "<b>" + projectUser == "" ? "Không tìm thấy" : projectUser + "</b>")
.Replace("<ProductCode>", "<b>" + productCode + "</b>")
.Replace("<ProductName>", product.Name)
.Replace("<UserFind>", grvData.GetRowCellValue(index, colUserFind) == null ? "Không tìm thấy" : grvData.GetRowCellValue(index, colUserFind).ToString())
.Replace("<TQ>", grvData.GetRowCellValue(index, colTQ) == null ? "Không tìm thấy" : grvData.GetRowCellValue(index, colTQ).ToString())
.Replace("<Description>", grvData.GetRowCellValue(index, colDes).ToString())
.Replace("<Ck>", NVCoKhi == "" ? "Không tìm thấy" : NVCoKhi)
.Replace("<Dn>", NVDien == "" ? "Không tìm thấy" : NVDien)
.Replace("<Dt>", NVDT == "" ? "Không tìm thấy" : NVDT);
if (departmentCodeGL == "TK")//Phong TK
{
foreach (string item in listEmail)
{
to += item.ToLower() + ";";
}
}
else
{
to = departmentMailGL + ";";
}
string supplierId = TextUtils.ToString(grvData.GetRowCellValue(index, colSupplierId));
if (!string.IsNullOrEmpty(supplierId))
{
to += TextUtils.ToString(LibQLSX.ExcuteScalar("SELECT Email FROM dbo.Suppliers WHERE SupplierId = '" + supplierId + "'"));
}
}
frmSendEmailAttach frm = new frmSendEmailAttach();
frm.Subject = subject;
//frm.CC = "*****@*****.**";
frm.To = to;
frm.Content = content;
if (frm.ShowDialog() == DialogResult.OK)
{
ModuleErrorModel errorModel = (ModuleErrorModel)ModuleErrorBO.Instance.FindByPK(TextUtils.ToInt(grvData.GetRowCellValue(index, colID)));
errorModel.CreateMailContent = Global.AppFullName + " - " + Environment.MachineName + " đã gửi mail vào ngày: " + DateTime.Now;
ModuleErrorBO.Instance.Update(errorModel);
loadGrid();
}
}
private void btnExcelDetail_Click(object sender, EventArgs e)
{
if (PaymentTable.ID == 0)
{
MessageBox.Show("Bạn phải lưu lại trước khi xuất file", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
string path = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
path = fbd.SelectedPath;
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongKeToan\\MauChungTu.xlsx";
string currentPath = path + "\\ChungTu-" + PaymentTable.Number + ".xlsx";
try
{
File.Copy(filePath, currentPath, true);
}
catch (Exception ex)
{
MessageBox.Show("Có lỗi khi tạo bảng kê thanh toán chi tiết!" + 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];
DataTable dtItem = LibQLSX.Select("select * from vPaymentTableItem with(nolock) where PaymentTableID = " + PaymentTable.ID);
for (int i = 0; i < dtItem.Rows.Count; i++)
{
string orderCode = TextUtils.ToString(dtItem.Rows[i]["Code"]);
//if (orderCode == "NCC1016.201702.007")
//{
// MessageBox.Show("a");
//}
string listProjects = TextUtils.ToString(dtItem.Rows[i]["Target"]);
decimal vat = TextUtils.ToDecimal(dtItem.Rows[i]["VAT"]);
decimal delivery = TextUtils.ToDecimal(dtItem.Rows[i]["DeliveryCost"]);
decimal percentPay = TextUtils.ToDecimal(dtItem.Rows[i]["PercentPay"]);
decimal totalTH = TextUtils.ToDecimal(dtItem.Rows[i]["TotalTH"]);
string[] arrProject = listProjects.Split(',');
if (arrProject.Length == 1)
{
//workSheet.Cells[8, 1] = i + 1;
workSheet.Cells[8, 5] = orderCode.Length > 7 ? orderCode.Substring(0, 7) : orderCode;
workSheet.Cells[8, 19] =
(TextUtils.ToDecimal(dtItem.Rows[i]["TotalCash"]) +
TextUtils.ToDecimal(dtItem.Rows[i]["TotalCK"])).ToString();
workSheet.Cells[8, 7] = TextUtils.ToString(dtItem.Rows[i]["Name"]);
workSheet.Cells[8, 20] = TextUtils.ToString(dtItem.Rows[i]["DCode"]);
workSheet.Cells[8, 21] = TextUtils.ToString(dtItem.Rows[i]["CostCode"]);
workSheet.Cells[8, 22] = TextUtils.ToString(dtItem.Rows[i]["Target"]);
workSheet.Cells[8, 23] = orderCode;
((Excel.Range)workSheet.Rows[8]).Insert();
}
else if (arrProject.Length > 1)
{
//if (orderCode == "NCC1281.201803.017")
//{
// MessageBox.Show(orderCode);
//}
//arrProject = arrProject.OrderByDescending(c => c).ToArray();
foreach (string projectCode in arrProject)
{
decimal total = 0;
if (orderCode.Length > 7)
{
string sql = "";
//bool isSXC = false;
if (!projectCode.Trim().StartsWith("Mua"))
{
sql = "select sum(TotalPrice) as total from vGetPartWithOrder_New060318 where OrderCode = '" + orderCode +
"' and ProjectCode = '" + projectCode.Trim() + "'";
//isSXC = true;
}
else
{
sql = "select sum(TotalPrice) as total from vGetPartWithOrder_New060318 where OrderCode = '" + orderCode +
"' and (ProjectCode is null or ProjectCode = '')";
}
//DataTable dtSum = LibQLSX.Select(sql);
decimal totalTH_Project = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sql));
decimal total_TH_Delivery = (delivery / (totalTH == 0 ? 1 : totalTH) * totalTH_Project) + totalTH_Project;
total = Math.Round((total_TH_Delivery + total_TH_Delivery * vat / 100) * (percentPay / 100));
}
else
{
total = TextUtils.ToDecimal(dtItem.Rows[i]["TotalCash"]) + TextUtils.ToDecimal(dtItem.Rows[i]["TotalCK"]);
}
workSheet.Cells[8, 5] = orderCode.Length > 7 ? orderCode.Substring(0, 7) : orderCode;
workSheet.Cells[8, 19] = total;
workSheet.Cells[8, 7] = TextUtils.ToString(dtItem.Rows[i]["Name"]);
workSheet.Cells[8, 20] = TextUtils.ToString(dtItem.Rows[i]["DCode"]);
workSheet.Cells[8, 21] = TextUtils.ToString(dtItem.Rows[i]["CostCode"]);
workSheet.Cells[8, 22] = projectCode.Trim();
workSheet.Cells[8, 23] = orderCode;
((Excel.Range)workSheet.Rows[8]).Insert();
}
}
}
((Excel.Range)workSheet.Rows[7]).Delete();
((Excel.Range)workSheet.Rows[7]).Delete();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (app != null)
{
app.ActiveWorkbook.Save();
app.Workbooks.Close();
app.Quit();
}
}
Process.Start(currentPath);
}
}
private void btnTongHopVanDe_Click(object sender, EventArgs e)
{
grvProject.FocusedRowHandle = -1;
DataTable dtProject = (DataTable)grdProject.DataSource;
DataRow[] drs = dtProject.Select("check = 1");
if (drs.Length == 0)
{
return;
}
string listProject = "'";
for (int i = 0; i < drs.Length; i++)
{
if (i == (drs.Length - 1))
{
listProject += drs[i]["ProjectCode"] + "'";
}
else
{
listProject += drs[i]["ProjectCode"] + ",";
}
}
//string projectCode = TextUtils.ToString(grvProject.GetFocusedRowCellValue(colPProjectCode));
string sql = "exec spProjectProblemSummary ''," + listProject;
DataTable dt = LibQLSX.Select(sql);
if (dt.Rows.Count == 0)
{
return;
}
string localPath = "";
FolderBrowserDialog fbd = new FolderBrowserDialog();
if (fbd.ShowDialog() == DialogResult.OK)
{
localPath = fbd.SelectedPath + "\\TongHopVanDe - " + DateTime.Now.ToString("dd-MM-yyyy") + ".xlsx";
}
else
{
return;
}
string filePath = Application.StartupPath + "\\Templates\\PhongDuAn\\TongHopVanDe.xlsx";
try
{
File.Copy(filePath, localPath, true);
}
catch
{
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];
for (int i = 0; i < dt.Rows.Count; i++)
{
string projectCode = TextUtils.ToString(dt.Rows[i]["ProjectCode"]);
string nguoiPhuTrach = TextUtils.ToString(LibQLSX.ExcuteScalar("select top 1 UserName from vProject where ProjectCode = '" + projectCode + "'"));
workSheet.Cells[5, 1] = i + 1;
workSheet.Cells[5, 2] = projectCode;
workSheet.Cells[5, 3] = nguoiPhuTrach;
workSheet.Cells[5, 4] = TextUtils.ToInt(dt.Rows[i]["Total"]);
workSheet.Cells[5, 5] = TextUtils.ToInt(dt.Rows[i]["Total_CXL"]);
//workSheet.Cells[5, 6] = TextUtils.ToString(dt.Rows[i][""]);
//workSheet.Cells[5, 7] = TextUtils.ToString(dt.Rows[i][""]);
workSheet.Cells[5, 8] = TextUtils.ToInt(dt.Rows[i]["TotalTK"]);
workSheet.Cells[5, 9] = TextUtils.ToInt(dt.Rows[i]["TotalTK_CXL"]);
workSheet.Cells[5, 10] = TextUtils.ToInt(dt.Rows[i]["TotalVT"]);
workSheet.Cells[5, 11] = TextUtils.ToInt(dt.Rows[i]["TotalVT_CXL"]);
workSheet.Cells[5, 12] = TextUtils.ToInt(dt.Rows[i]["TotalSXLR"]);
workSheet.Cells[5, 13] = TextUtils.ToInt(dt.Rows[i]["TotalSXLR_CXL"]);
workSheet.Cells[5, 14] = TextUtils.ToInt(dt.Rows[i]["TotalSV"]);
workSheet.Cells[5, 15] = TextUtils.ToInt(dt.Rows[i]["TotalSV_CXL"]);
workSheet.Cells[5, 16] = TextUtils.ToInt(dt.Rows[i]["TotalCG"]);
workSheet.Cells[5, 17] = TextUtils.ToInt(dt.Rows[i]["TotalCG_CXL"]);
workSheet.Cells[5, 18] = TextUtils.ToString(dt.Rows[i]["TotalKhac"]);
workSheet.Cells[5, 19] = TextUtils.ToString(dt.Rows[i]["TotalKhac_CXL"]);
//workSheet.Cells[5, 20] = TextUtils.ToString(dt.Rows[i][""]);
((Excel.Range)workSheet.Rows[5]).Insert();
}
((Excel.Range)workSheet.Rows[4]).Delete();
((Excel.Range)workSheet.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 btnSave_Click(object sender, EventArgs e)
{
bool _isSaved = false;
if (treeData.AllNodesCount == 0)
{
return;
}
treeData.ExpandAll();
treeData.FocusedNode = null;
DataTable dtSource = (DataTable)treeData.DataSource;
DataRow[] drsQty = dtSource.Select("(QtyT is null or QtyT = 0) and C_ProductGroupID > 0");
//DataRow[] drsVatTu = dtSource.Select("(PriceVT is null or PriceVT = 0) and C_ProductGroupID > 0");
int checkVT = dtSource.AsEnumerable().Count(o => TextUtils.ToDecimal(o.Field <decimal>("PriceVT")) == 0 && TextUtils.ToInt(o.Field <object>("C_ProductGroupID")) > 0);
int checkQty = dtSource.AsEnumerable().Count(o => TextUtils.ToDecimal(o.Field <decimal>("QtyT")) == 0 && TextUtils.ToInt(o.Field <object>("C_ProductGroupID")) > 0);
if (checkQty > 0)
{
MessageBox.Show("Bạn chưa nhập số lượng cho các hạng mục.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
if (checkVT > 0)
{
MessageBox.Show("Bạn chưa nhập đủ VẬT TƯ SẢN XUẤT cho các hạng mục.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
int costID = (Quotation.DepartmentId == "D018" ? 73 : 72);//Phòng KD1->lấy ra chi phí nhân công KD2 không thì lấy chi phí nhân công KD1
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang xử lý..."))
{
#region Tính vật tư
for (int i = 0; i < treeData.AllNodesCount; i++)
{
int id = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colDetailID));
C_QuotationDetail_KDModel item = (C_QuotationDetail_KDModel)C_QuotationDetail_KDBO.Instance.FindByPK(id);
item.ModuleCode = TextUtils.ToString(treeData.GetNodeByVisibleIndex(i).GetValue(colModuleCode));
item.ModuleName = TextUtils.ToString(treeData.GetNodeByVisibleIndex(i).GetValue(colModuleName));
item.PriceVT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceVT));
item.PriceHD = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPriceHD));
item.C_ProductGroupID = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colProductGroupID));
item.VAT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colVAT));
item.QtyT = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colQtyT));
item.DepartmentId = TextUtils.ToString(treeData.GetNodeByVisibleIndex(i).GetValue(colDepartmentId));
//item.CustomerType = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colCustomerType));
item.PercentXLKH = TextUtils.ToDecimal(treeData.GetNodeByVisibleIndex(i).GetValue(colPercentXLKH));
int parentID = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colParentID));
if (parentID == 0)
{
item.Qty = item.QtyT;
}
else
{
C_QuotationDetail_KDModel parent = (C_QuotationDetail_KDModel)C_QuotationDetail_KDBO.Instance.FindByPK(parentID);
item.Qty = item.QtyT * parent.Qty;
}
C_QuotationDetail_KDBO.Instance.Update(item);
}
#endregion
#region Tính giá, chi phí
//DataTable dtCostVT = LibQLSX.Select("select sum(Qty*PriceVT) from C_QuotationDetail_KD where (ParentID > 0 or (ParentID = 0 and C_ProductGroupID > 0)) and [C_QuotationID] = " + Quotation.ID);
//decimal totalCostVT = dtCostVT.Rows.Count > 0 ? TextUtils.ToDecimal(dtCostVT.Rows[0][0]) : 0;
string sqlSumCostVT = "select sum(isnull(Qty,0)*isnull(PriceVT,0)) from C_QuotationDetail_KD where (ParentID > 0 or (ParentID = 0 and C_ProductGroupID > 0)) and [C_QuotationID] = " + Quotation.ID;
decimal totalCostVT = TextUtils.ToDecimal(LibQLSX.ExcuteScalar(sqlSumCostVT));
for (int i = 0; i < treeData.AllNodesCount; i++)
{
int id = TextUtils.ToInt(treeData.GetNodeByVisibleIndex(i).GetValue(colDetailID));
C_QuotationDetail_KDModel item = (C_QuotationDetail_KDModel)C_QuotationDetail_KDBO.Instance.FindByPK(id);
if (item.ParentID == 0 && item.C_ProductGroupID == 0)
{
continue;
}
DataTable dtProductGroup = LibQLSX.Select("select top 1 ProfitPercentKD, CustomerPercentKD1, CustomerPercentKD2, ProfitPercentKD_OEM, XLPercent from C_ProductGroup where ID = " + item.C_ProductGroupID);
decimal profitPercent = 0;
if (Quotation.DepartmentId == "D018")
{
profitPercent = dtProductGroup.Rows.Count > 0 ? TextUtils.ToDecimal(dtProductGroup.Rows[0]["XLPercent"]) / 100 : 0;
}
else
{
if (Quotation.CustomerType == 1)//EUS
{
profitPercent = dtProductGroup.Rows.Count > 0 ? TextUtils.ToDecimal(dtProductGroup.Rows[0]["ProfitPercentKD"]) / 100 : 0;
}
if (Quotation.CustomerType == 2)//OEM
{
profitPercent = dtProductGroup.Rows.Count > 0 ? TextUtils.ToDecimal(dtProductGroup.Rows[0]["ProfitPercentKD_OEM"]) / 100 : 0;
}
}
decimal xuLyPhanGuiPercent = item.PercentXLKH;
decimal chiXucTienBanHang = (Quotation.CustomerCash * item.PriceVT) / totalCostVT;
if (Quotation.DepartmentId == "D018")
{
chiXucTienBanHang = chiXucTienBanHang / (1 - xuLyPhanGuiPercent / 100);
}
decimal priceDuPhong = (Quotation.TotalDP_KD * item.PriceVT) / totalCostVT;
decimal totalPercentCP = TextUtils.ToDecimal(LibQLSX.ExcuteScalar("select Sum(" + (Quotation.DepartmentId == "D018" ? "ValuePercentKD1" : "ValuePercentKD2")
+ ") from vC_CostProductGroupLinkNew where IsDeleted = 0 and C_CostGroupNew_Code in ('N01','N05','N08','N09','N11') "
+ " and C_ProductGroupID = " + item.C_ProductGroupID + " and [C_VersionID] = " + Quotation.C_VersionID)) / 100;
item.TotalBX = Quotation.TotalBX_KD * item.PriceVT / totalCostVT;
item.TotalVC = Quotation.TotalVC_KD * item.PriceVT / totalCostVT;
item.TotalDP = priceDuPhong;
item.TotalCustomer = chiXucTienBanHang;
item.TotalXL = Quotation.IsVAT == 1 ? xuLyPhanGuiPercent / 100 * chiXucTienBanHang : 0;
item.PriceCP = (item.PriceVT + item.TotalBX + item.TotalVC + priceDuPhong) * (1 + profitPercent) / (1 - totalPercentCP * (1 + profitPercent));
item.PriceTPA = chiXucTienBanHang + item.PriceCP * (1 + item.VAT / 100);
item.PriceTPA_PreVAT = item.PriceTPA / (1 + item.VAT / 100);
item.PriceVAT = (item.PriceTPA_PreVAT - chiXucTienBanHang) * item.VAT / 100;
item.PriceVAT_HD = ((item.PriceHD / (1 + item.VAT / 100)) - chiXucTienBanHang) * item.VAT / 100;
item.PriceReal = item.PriceHD - item.PriceVAT_HD - chiXucTienBanHang;
C_QuotationDetail_KDBO.Instance.Update(item);
calculateCost(item);
decimal totalAllCP = (item.TotalVC + item.TotalBX + item.PriceVT + item.TotalNC + item.TotalNC_KD + item.TotalPB + item.TotalDP);
item.TotalProfitQD = item.PriceCP - totalAllCP;
item.TotalProfitTT = item.PriceHD - totalAllCP - item.PriceVAT_HD - chiXucTienBanHang;
C_QuotationDetail_KDBO.Instance.Update(item);
}
#endregion
#region Tính thiết bị cha
ArrayList listParent = C_QuotationDetail_KDBO.Instance.FindByExpression(new Expression("ParentID", 0)
.And(new Expression("C_ProductGroupID", 0))
.And(new Expression("C_QuotationID", Quotation.ID)));
if (listParent != null)
{
foreach (var item in listParent)
{
C_QuotationDetail_KDModel itemP = (C_QuotationDetail_KDModel)item;
DataTable dtChild = LibQLSX.Select("select Sum(QtyT*PriceVT),Sum(QtyT*PriceTPA),Sum(QtyT*PriceCP),Sum(QtyT*PriceTPA_PreVAT)"
+ ",Sum(QtyT*PriceVAT),Sum(QtyT*PriceHD),Sum(QtyT*TotalXL)"
+ ",Sum(QtyT*PriceReal),Sum(QtyT*TotalDP),Sum(QtyT*TotalCustomer),Sum(QtyT*TotalProfitQD),Sum(QtyT*TotalProfitTT)"
+ ",Sum(QtyT*TotalNC_KD),Sum(QtyT*TotalNC),Sum(QtyT*TotalPB)"
+ ",Sum(QtyT*TotalBX),Sum(QtyT*TotalVC)"
+ " from C_QuotationDetail_KD where ParentID = " + itemP.ID);
itemP.PriceVT = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][0]) : itemP.PriceVT;
itemP.PriceTPA = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][1]) : itemP.PriceTPA;
itemP.PriceCP = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][2]) : itemP.PriceCP;
itemP.PriceTPA_PreVAT = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][3]) : itemP.PriceTPA_PreVAT;
itemP.PriceVAT = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][4]) : itemP.PriceVAT;
itemP.PriceHD = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][5]) : itemP.PriceHD;
itemP.TotalXL = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][6]) : itemP.TotalXL;
itemP.PriceReal = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][7]) : itemP.PriceReal;
itemP.TotalDP = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][8]) : itemP.TotalDP;
itemP.TotalCustomer = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][9]) : itemP.TotalCustomer;
itemP.TotalProfitQD = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][10]) : itemP.TotalProfitQD;
itemP.TotalProfitTT = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][11]) : itemP.TotalProfitTT;
itemP.TotalNC_KD = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][12]) : itemP.TotalNC_KD;
itemP.TotalNC = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][13]) : itemP.TotalNC;
itemP.TotalPB = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][14]) : itemP.TotalPB;
itemP.TotalBX = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][15]) : itemP.TotalBX;
itemP.TotalVC = dtChild.Rows.Count > 0 ? TextUtils.ToDecimal(dtChild.Rows[0][16]) : itemP.TotalVC;
C_QuotationDetail_KDBO.Instance.Update(itemP);
}
}
#endregion
loadQuotationItem();
_isSaved = true;
}
MessageBox.Show("Lưu trữ thành công.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
if (_isSaved)
{
if (this.LoadDataChange != null)
{
this.LoadDataChange(null, null);
}
}
}
