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); } } }