private bool ValidateForm()
{
if (cboProductGroupCode.EditValue == null)
{
MessageBox.Show("Xin hãy chọn Group.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (cboProductTypeCode.Text.Trim() == "")
{
MessageBox.Show("Xin hãy chọn loại sản phẩm.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
string sql1 = "";
if (formulaModel.ID > 0)
{
sql1 = $"SELECT top 1 ID from ConfigFormula WHERE ProductTypeCode = N'{cboProductTypeCode.Text.Trim()}' and ProductGroupID={cboProductGroupCode.EditValue} and ID <>'{formulaModel.ID}'";
}
else
{
sql1 = $"SELECT top 1 ID from ConfigFormula WHERE ProductTypeCode = '{cboProductTypeCode.Text.Trim()}' and ProductGroupID={cboProductGroupCode.EditValue}";
}
int id = TextUtils.ToInt(LibIE.ExcuteScalar(sql1));
if (id > 0)
{
MessageBox.Show("Đã tồn tại loại sản phẩm", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
return(true);
}
void loadData()
{
string projectId = TextUtils.ToString(cboProject.EditValue);
if (projectId == "")
{
return;
}
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang load dữ liệu..."))
{
string sql = "spGetOrderOfProject '" + projectId + "'";
DataTable dt = LibQLSX.Select(sql);
dt.Columns.Add("ChenhLech", typeof(decimal));
foreach (DataRow row in dt.Rows)
{
decimal totalVAT = TextUtils.ToDecimal(row["TotalVAT"]);
string orderCode = TextUtils.ToString(row["OrderCode"]);
decimal totalInvoice = 0;
string sql1 = "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 = '" + orderCode + "') AND (T_XNTC.FK_TKNO LIKE '133%')";
totalInvoice = TextUtils.ToDecimal(LibIE.ExcuteScalar(sql1));
row["ChenhLech"] = totalVAT - totalInvoice;
}
grdData.DataSource = dt;
}
}
private bool ValidateForm()
{
if (txtShipTo.Text.Trim() == "")
{
MessageBox.Show("Xin hãy nhập Đích", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
string sql1 = "";
if (_ShipToModel.ID > 0)
{
sql1 = $"SELECT top 1 ID from ConfigShipTo WHERE ShipTo = N'{txtShipTo.Text.Trim()}' and ID <>'{_ShipToModel.ID}'";
}
else
{
sql1 = $"SELECT top 1 ID from ConfigShipTo WHERE ShipTo = N'{txtShipTo.Text.Trim()}'";
}
int id = TextUtils.ToInt(LibIE.ExcuteScalar(sql1));
if (id > 0)
{
MessageBox.Show("Đã tồn tại Đích", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
return(true);
}
bool ValidateForm()
{
if (txtProjectCode.Text.Trim() == "")
{
MessageBox.Show("Không tìm thấy mã dự án.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
else
{
int id = TextUtils.ToInt(LibIE.ExcuteScalar("select top 1 ID from T_DM_FCM where [C_CODE] = '" + txtProjectCode.Text.Trim() + "'"));
if (id > 0)
{
MessageBox.Show("FCM này đã được tồn tại trong cơ sở dữ liệu.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
}
if (TextUtils.ToInt(cboPhongBan.SelectedValue) == 0)
{
MessageBox.Show("Bạn phải chọn một phòng ban phụ trách.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (TextUtils.ToInt(cboYear.SelectedItem) == 0)
{
MessageBox.Show("Bạn phải chọn một năm.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (cboMonth.SelectedIndex < 1)
{
MessageBox.Show("Bạn phải chọn một tháng trong năm.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (TextUtils.ToDecimal(txtTotalHD.EditValue) == 0 || TextUtils.ToDecimal(txtTotalProfit.EditValue) == 0)
{
MessageBox.Show("Đây không phải là dữ liệu FCM.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (TextUtils.ToDecimal(txtTotalVT.EditValue) == 0)
{
MessageBox.Show("Đây không phải là dữ liệu FCM.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
int dtcp = TextUtils.ToInt(LibIE.ExcuteScalar("SELECT TOP 1 [PK_ID] FROM [T_DM_DTCP] where C_MA = '" + txtProjectCode.Text.Trim() + "'"));
if (dtcp <= 0)
{
MessageBox.Show("Dự án này chưa có trên IEnter.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
return(true);
}
/// <summary>
/// Validate trước khi cất dữ liệu
/// </summary>
/// <returns></returns>
private bool ValidateForm()
{
if (cboGroup.EditValue == null)
{
MessageBox.Show("Xin hãy chọn nhóm sản phẩm.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (cboStep.EditValue == null)
{
MessageBox.Show("Xin hãy chọn công đoạn.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (cboValueType.SelectedIndex < 0)
{
MessageBox.Show("Xin hãy chọn kiểu kiểm tra.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
//if (cboCheckValueType.SelectedIndex < 1)
//{
// MessageBox.Show("Xin hãy chọn loại giá trị kiểm tra.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
// return false;
//}
if (txtName.Text.Trim() == "")
{
MessageBox.Show("Xin hãy điền Mô tả công đoạn.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
string sql1 = "";
if (WorkingModel.ID > 0)
{
sql1 = $"SELECT ID from Working WHERE SortOrder = '{txtSortOrder.Value}' and ID <>'{WorkingModel.ID}' and WorkingStepID={WorkingModel.WorkingStepID} ";
}
else
{
sql1 = $"SELECT ID from Working WHERE SortOrder = '{txtSortOrder.Value}' and WorkingStepID={StepID}";
}
int id = TextUtils.ToInt(LibIE.ExcuteScalar(sql1));
if (id > 0)
{
MessageBox.Show("Trùng STT", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
// return false;
}
return(true);
}
/// <summary>
/// Validate trước khi cất dữ liệu
/// </summary>
/// <returns></returns>
private bool ValidateForm()
{
if (cboStep.EditValue == null)
{
MessageBox.Show("Xin hãy chọn một công đoạn.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (txtName.Text.Trim() == "")
{
MessageBox.Show("Xin hãy điền Mô tả chi tiết kiểm tra.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
if (cboValueType.SelectedIndex < 0)
{
MessageBox.Show("Xin hãy chọn một kiểu kiểm tra.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
string sql1 = "";
if (ProductWorking.ID > 0)
{
sql1 = $"SELECT top 1 ID from ProductWorking WHERE SortOrder = '{txtSortOrder.Value}' and ID <>'{ProductWorking.ID}' and ProductID={ProductWorking.ProductID} and ProductStepID={ProductWorking.ProductStepID} ";
}
else
{
sql1 = $"SELECT top 1 ID from ProductWorking WHERE SortOrder = '{txtSortOrder.Value}' and ProductID={ProductID} and ProductStepID={StepID}";
}
int id = TextUtils.ToInt(LibIE.ExcuteScalar(sql1));
if (id > 0)
{
MessageBox.Show("Trùng STT", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
//return false;
}
if (cboCheckValueType.SelectedIndex <= 0)
{
MessageBox.Show("Xin hãy chọn một loại giá trị kiểm tra.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return(false);
}
return(true);
}
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 btnReloadData_Click(object sender, EventArgs e)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang load dữ liệu..."))
{
string dCode = TextUtils.ToString(cboPhongBan.EditValue);
int dID = TextUtils.ToInt(grvCboPhongBan.GetFocusedRowCellValue(colPhanXuongID));
int year = TextUtils.ToInt(cboYear.SelectedItem);
DataTable dtData = new DataTable();
if (dCode.ToUpper() == "P21")
{
string sql = "exec spReportChiPhi_BanGiamDoc " + year;
dtData = LibIE.Select(sql);
}
else
{
string sql = "exec spReportChiPhi " + year + ", '" + dCode + "'";
dtData = LibIE.Select(sql);
}
DataRow[] drs = dtData.Select("C_MA = 'C17'");
if (dCode.ToUpper() == "P21" || dCode.ToUpper() == "P13" || dCode.ToUpper() == "P14") // Ban giam doc, kinh doanh cn, kinh doanh day nghe
{
string sql = "SELECT Sum(ISNULL(C_PSNO,0)) XangXe,C_Month FROM [V_XNTC_REPORT] where (C_KMP_MA = 'C17') and C_Year = " + year + " Group by C_Month";
DataTable dtXangXe = LibIE.Select(sql);
decimal tyle = TextUtils.ToDecimal(LibIE.ExcuteScalar("select top 1 TYLE from T_DM_PHANXUONG_KMP where PK_KMP = 41 and PK_PHANXUONG = " + dID));
decimal total = 0;
for (int i = 1; i <= 12; i++)
{
try
{
decimal value = TextUtils.ToDecimal(dtXangXe.Rows[i - 1]["XangXe"]);
decimal giaTriPB = tyle * value / 100;
drs[0]["T" + i + "_PB"] = giaTriPB;
drs[0]["T" + i] = giaTriPB;
drs[0]["T" + i + "_TT"] = 0;
total += giaTriPB;
}
catch (Exception)
{
}
}
drs[0]["Total_PB"] = total;
drs[0]["Total_TT"] = 0;
drs[0]["Total"] = total;
}
else if (dCode != "")
{
for (int i = 1; i <= 12; i++)
{
drs[0]["T" + i + "_PB"] = 0;
drs[0]["T" + i] = 0;
drs[0]["T" + i + "_TT"] = 0;
}
drs[0]["Total_PB"] = 0;
drs[0]["Total_TT"] = 0;
drs[0]["Total"] = 0;
}
else
{
for (int i = 1; i <= 12; i++)
{
drs[0]["T" + i + "_PB"] = TextUtils.ToDecimal(drs[0]["T" + i + "_TT"]);
drs[0]["T" + i] = TextUtils.ToDecimal(drs[0]["T" + i + "_TT"]);
drs[0]["T" + i + "_TT"] = 0;
}
drs[0]["Total_PB"] = TextUtils.ToDecimal(drs[0]["Total_TT"]);
drs[0]["Total_TT"] = 0;
drs[0]["Total"] = TextUtils.ToDecimal(drs[0]["Total_PB"]);
}
grdData.DataSource = dtData;
}
}
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 dtQuotation = LibQLSX.Select("exec [spReportQuotationKD_Project] @ProjectCode = '" + fcmCode + "'");
DataTable dtAllCost = LibQLSX.Select("exec [spReportCostWithQuotationKD_Project] '" + fcmCode + "'");
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_", "")));
}
}
}
}
foreach (DataColumn c in _dtData.Columns)
{
if (c.ColumnName == "NamTruoc" || c.ColumnName.StartsWith("T_"))
{
_dtData.Rows[2][c.ColumnName] = TextUtils.ToDecimal(_dtData.Rows[0][c.ColumnName]) + TextUtils.ToDecimal(_dtData.Rows[1][c.ColumnName]);
}
}
#region Nhập chi phí lãi vay thực tế của dự án
DataRow[] drsLaiVay = _dtData.Select("STT = 8");
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();
}
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;
}
/// <summary>
/// Cất dữ liệu
/// </summary>
/// <returns></returns>
bool saveData()
{
if (!ValidateForm())
{
return(false);
}
string reason = "";
string sql = $"SELECT ReasonChange from ProductWorkingAudit " +
$"WHERE ProductWorkingID='{ProductWorking.ID}' ";
reason = TextUtils.ToString(LibIE.ExcuteScalar(sql));
frmReasonChange frm = new frmReasonChange();
frm.Reason = reason;
if (frm.ShowDialog() == DialogResult.OK)
{
reason = frm.Reason;
if (ProductWorking.ID > 0)
{
ProductWorking.ActionType = 2;
ProductWorking.IsApproved = 1;
//MessageBox.Show("Chờ duyệt", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
ProductWorkingBO.Instance.Update(ProductWorking);
}
if (Global.UserID != 0)
{
ProductWorking.CreatedBy = Global.AppFullName;
ProductWorking.UpdatedBy = Global.AppFullName;
}
ProductWorking.ProductID = ProductID;
ProductWorking.ProductStepID = TextUtils.ToInt(cboStep.EditValue);
ProductWorking.WorkingName = txtName.Text.Trim();
ProductWorking.SortOrder = (int)txtSortOrder.Value;
ProductWorking.IsHidden = chkIsShow.Checked;
ProductWorking.Unit = txtUnit.Text.Trim();
ProductWorking.ValueType = cboValueType.SelectedIndex;// kiểu kiểm tra
ProductWorking.ValueTypeName = cboValueType.SelectedIndex == 0 ? "Check mark" : "Giá trị\n数値";
ProductWorking.MaxValue = TextUtils.ToDecimal(txtMax.Value);
ProductWorking.MinValue = TextUtils.ToDecimal(txtMin.Value);
ProductWorking.CheckValueType = cboCheckValueType.SelectedIndex;
ProductWorking.ProductStepCode = cboStep.Text;
ProductWorking.IsApproved = 1;
if (ProductWorking.ValueType == 0)//checkmark
{
ProductWorking.PeriodValue = txtPeriodValue.Text.Trim();
}
else
{
if (ProductWorking.CheckValueType == 1)//giá trị dạng số
{
if (ProductWorking.MinValue == ProductWorking.MaxValue)
{
ProductWorking.PeriodValue = ProductWorking.MaxValue.ToString("n3");
}
else
{
ProductWorking.PeriodValue = ProductWorking.MinValue.ToString("n3") + "~" + ProductWorking.MaxValue.ToString("n3");
}
}
else
{
ProductWorking.PeriodValue = txtPeriodValue.Text.Trim();
}
}
if (ProductWorking.ID > 0)
{
if (_isApproved == 1 && _actionType == 1)
{
ProductWorking.ActionType = 1;
ProductWorkingBO.Instance.Update(ProductWorking);
return(true);
}
}
else
{
ProductWorking.ActionType = 1;
ProductWorking.ID = (int)ProductWorkingBO.Instance.Insert(ProductWorking);
}
processAudit(ProductWorking, reason);
return(true);
}
return(false);
}
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);
}
void loadData()
{
removeColumn();
DateTime from = new DateTime(dtpFromDate.Value.Year, dtpFromDate.Value.Month, dtpFromDate.Value.Day, 0, 0, 0);
DateTime to = new DateTime(dtpEndDate.Value.Year, dtpEndDate.Value.Month, dtpEndDate.Value.Day, 23, 59, 59);
if (txtFromOrder.Text.Trim() != "" && txtToOrder.Text.Trim() != "")
{
string sql = @"SELECT TOP 1 DateLR
FROM dbo.ProductCheckHistoryDetail
WHERE OrderCode LIKE '%{0}%' ORDER BY DateLR";
DateTime?date1 = TextUtils.ToDate2(LibIE.ExcuteScalar(string.Format(sql, txtFromOrder.Text.Trim())));
DateTime?date2 = TextUtils.ToDate2(LibIE.ExcuteScalar(string.Format(sql, txtToOrder.Text.Trim())));
if (date1.HasValue && date2.HasValue)
{
if (date2.Value >= date1.Value)
{
from = new DateTime(date1.Value.Year, date1.Value.Month, date1.Value.Day, 0, 0, 0);
to = new DateTime(date2.Value.Year, date2.Value.Month, date2.Value.Day, 0, 0, 0);
}
else
{
to = new DateTime(date1.Value.Year, date1.Value.Month, date1.Value.Day, 0, 0, 0);
from = new DateTime(date2.Value.Year, date2.Value.Month, date2.Value.Day, 0, 0, 0);
}
}
}
List <string> listColumnText = new List <string>();
if (txtFindText.Text.Trim() == "")
{
return;
}
string[] arr = txtFindText.Text.Trim().Split(';');
for (int i = 0; i < arr.Length; i++)
{
listColumnText.Add(string.Format("[{0}]", arr[i]));
grvData.Columns[i + 1].VisibleIndex = i + 1;
grvData.Columns[i + 1].Caption = arr[i];
grvData.Columns[i + 1].FieldName = arr[i];
grvData.Columns[i + 1].Width = 200;
}
DataTable dt = LibIE.LoadDataFromSP("spThongKePhanTichData", "A"
, new string[] { "@From", "@To", "@FistColumnText" }
, new object[] { from, to, string.Join(",", listColumnText) });
//DataColumn c = new DataColumn();
//c.Caption = "STT";
//c.ColumnName = "STT";
//c.DataType = typeof(int);
//c.AutoIncrement = true;
//c.AutoIncrementSeed = 1;
//c.AutoIncrementStep = 1;
//c.AllowDBNull = false;
////c.Unique = true;
//dt.Columns.Add(c);
//dt.Columns["STT"].SetOrdinal(0);
grdData.DataSource = dt;
}
private void btnBrowse_Click(object sender, EventArgs e)
{
if (TextUtils.ToInt(cboPhongBan.SelectedValue) == 0)
{
MessageBox.Show("Bạn phải chọn một phòng phụ trách.", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
OpenFileDialog ofd = new OpenFileDialog();
ofd.Multiselect = false;
if (ofd.ShowDialog() == DialogResult.OK)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo load dữ liệu..."))
{
fcm = new T_DM_FCMModel();
DataTable dt = new DataTable();
DataRow[] drs = null;
txtDMVTPath.Text = ofd.FileName;
if (TextUtils.ToInt(cboPhongBan.SelectedValue) == 40)
{
dt = TextUtils.ExcelToDatatableNoHeader(txtDMVTPath.Text, "Sheet1");
if (dt.Rows.Count == 0)
{
return;
}
txtProjectCode.Text = TextUtils.ToString(dt.Rows[3]["F4"]);
txtHopDong.Text = TextUtils.ToString(dt.Rows[7]["F7"]);
txtTotalBanHang.EditValue = TextUtils.ToDecimal(dt.Rows[19]["F7"]);
txtTotalBX.EditValue = TextUtils.ToDecimal(dt.Rows[18]["F7"]);
txtTotalHD.EditValue = TextUtils.ToDecimal(dt.Rows[11]["F7"]);
txtTotalNC.EditValue = TextUtils.ToDecimal(dt.Rows[16]["F7"]);
txtTotalPB.EditValue = TextUtils.ToDecimal(dt.Rows[17]["F7"]);
txtTotalProfit.EditValue = TextUtils.ToDecimal(dt.Rows[20]["F7"]);
txtTotalReal.EditValue = TextUtils.ToDecimal(dt.Rows[14]["F7"]);
txtTotalTPA.EditValue = TextUtils.ToDecimal(dt.Rows[12]["F7"]);
txtTotalTrienKhai.EditValue = TextUtils.ToDecimal(dt.Rows[15]["F7"]);
txtTotalVAT.EditValue = TextUtils.ToDecimal(dt.Rows[13]["F7"]);
txtTotalVT.EditValue = TextUtils.ToDecimal(dt.Rows[24]["F7"]);
drs = dt.Select("F2 is null and F3 is not null and F3 like 'C%'");
colCode.FieldName = "F3";
}
else
{
dt = TextUtils.ExcelToDatatableNoHeader(txtDMVTPath.Text, "INPUT");
if (dt.Rows.Count == 0)
{
return;
}
txtProjectCode.Text = TextUtils.ToString(dt.Rows[4]["F4"]);
txtHopDong.Text = TextUtils.ToString(dt.Rows[8]["F7"]);
txtTotalBanHang.EditValue = TextUtils.ToDecimal(dt.Rows[48]["F7"]);
txtTotalBX.EditValue = TextUtils.ToDecimal(dt.Rows[19]["F7"]);
txtTotalHD.EditValue = TextUtils.ToDecimal(dt.Rows[12]["F7"]);
txtTotalNC.EditValue = TextUtils.ToDecimal(dt.Rows[17]["F7"]);
txtTotalPB.EditValue = TextUtils.ToDecimal(dt.Rows[18]["F7"]);
txtTotalProfit.EditValue = TextUtils.ToDecimal(dt.Rows[20]["F7"]);
txtTotalReal.EditValue = TextUtils.ToDecimal(dt.Rows[15]["F7"]);
txtTotalTPA.EditValue = TextUtils.ToDecimal(dt.Rows[13]["F7"]);
txtTotalTrienKhai.EditValue = TextUtils.ToDecimal(dt.Rows[16]["F7"]);
txtTotalVAT.EditValue = TextUtils.ToDecimal(dt.Rows[14]["F7"]);
txtTotalVT.EditValue = TextUtils.ToDecimal(dt.Rows[22]["F7"]);
drs = dt.Select("F2 is not null and F2 like 'C%'");
colCode.FieldName = "F2";
}
if (drs.Length > 0)
{
dt = drs.CopyToDataTable();
}
dt.Columns.Add("DepartmentID", typeof(int));
foreach (DataRow row in dt.Rows)
{
string kmpCode = "";
if (TextUtils.ToInt(cboPhongBan.SelectedValue) == 40)
{
kmpCode = TextUtils.ToString(row["F3"]);
}
else
{
kmpCode = TextUtils.ToString(row["F2"]);
}
DataTable dtKMP = LibIE.Select("select top 1 [PK_ID],[C_ISKD] from T_DM_KMP where C_MA = '" + kmpCode + "'");
if (dtKMP.Rows.Count == 0)
{
continue;
}
int isKD = TextUtils.ToInt(dtKMP.Rows[0]["C_ISKD"]);
int pk_KMP = TextUtils.ToInt(dtKMP.Rows[0]["PK_ID"]);
if (isKD == 1)
{
row["DepartmentID"] = TextUtils.ToInt(cboPhongBan.SelectedValue);
}
else
{
int pk_PhanXuong = TextUtils.ToInt(LibIE.ExcuteScalar("select top 1 [PK_PHANXUONG] from T_DM_PHANXUONG_KMP_Link where [PK_KMP] = " + pk_KMP));
if (pk_PhanXuong == 0)
{
continue;
}
row["DepartmentID"] = pk_PhanXuong;
}
}
grdData.DataSource = dt;
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang lưu trữ dữ liệu..."))
{
ProcessTransaction pt = new ProcessTransaction();
pt.OpenConnection();
pt.BeginTransaction();
try
{
if (!ValidateForm())
{
return;
}
#region FCM
fcm.FK_DTCP = TextUtils.ToInt(LibIE.ExcuteScalar("SELECT TOP 1 [PK_ID] FROM [T_DM_DTCP] where C_MA = '" + txtProjectCode.Text.Trim() + "'"));
fcm.C_CODE = txtProjectCode.Text.Trim();
fcm.C_MONTH = cboMonth.SelectedIndex;
fcm.C_YEAR = TextUtils.ToInt(cboYear.SelectedItem);
fcm.C_SOHOADON = txtHopDong.Text.Trim();
//fcm.C_TYPE = chkIsGiaoDucFCM.Checked ? 1 : 2;
fcm.C_TYPE = TextUtils.ToInt(cboPhongBan.SelectedValue) == 40 ? 1 : 2;
fcm.TotalBX = TextUtils.ToDecimal(txtTotalBX.EditValue);
fcm.TotalTrienKhai = TextUtils.ToDecimal(txtTotalTrienKhai.EditValue);
fcm.TotalHD = TextUtils.ToDecimal(txtTotalHD.EditValue);
fcm.TotalNC = TextUtils.ToDecimal(txtTotalNC.EditValue);
fcm.TotalPB = TextUtils.ToDecimal(txtTotalPB.EditValue);
fcm.TotalProfit = TextUtils.ToDecimal(txtTotalProfit.EditValue);
fcm.TotalReal = TextUtils.ToDecimal(txtTotalReal.EditValue);
fcm.TotalTPA = TextUtils.ToDecimal(txtTotalTPA.EditValue);
fcm.TotalVAT = TextUtils.ToDecimal(txtTotalVAT.EditValue);
fcm.TotalBanHang = TextUtils.ToDecimal(txtTotalBanHang.EditValue);
fcm.TotalVT = TextUtils.ToDecimal(txtTotalVT.EditValue);
fcm.ID = (int)pt.Insert(fcm);
#endregion
#region Detail
grvData.FocusedRowHandle = -1;
for (int i = 0; i < grvData.RowCount; i++)
{
decimal c_Price = TextUtils.ToDecimal(grvData.GetRowCellValue(i, colValue));
//if (c_Price == 0) continue;
string kmpCode = TextUtils.ToString(grvData.GetRowCellValue(i, colCode));
int departmentID = TextUtils.ToInt(grvData.GetRowCellValue(i, colDepartmentID));
int pk_KMP = TextUtils.ToInt(LibIE.ExcuteScalar("select top 1 PK_ID from T_DM_KMP where C_MA = '" + kmpCode + "'"));
if (pk_KMP == 0)
{
continue;
}
if (departmentID == 81)
{
DataTable dtLink = LibIE.Select("SELECT [ID],[PK_PHANXUONG],[PK_KMP],[TYLE],[C_MA],[C_MOTA] FROM " +
"[V_DM_PHANXUONG_KMP] where PK_KMP = " + pk_KMP + " and TYLE > 0");
if (dtLink.Rows.Count > 0)
{
foreach (DataRow row in dtLink.Rows)
{
T_DM_FCM_DETAILModel detail = new T_DM_FCM_DETAILModel();
detail.FK_FCM = fcm.ID;
detail.FK_PHANXUONG = TextUtils.ToInt(row["PK_PHANXUONG"]);
detail.FK_KMP = pk_KMP;
detail.C_PRICE = TextUtils.ToDecimal(row["TYLE"]) * c_Price / 100;
pt.Insert(detail);
}
}
}
else
{
T_DM_FCM_DETAILModel detail = new T_DM_FCM_DETAILModel();
detail.FK_FCM = fcm.ID;
detail.FK_PHANXUONG = TextUtils.ToInt(grvData.GetRowCellValue(i, colDepartmentID));
detail.FK_KMP = pk_KMP;
detail.C_PRICE = c_Price;
pt.Insert(detail);
}
}
#endregion
pt.CommitTransaction();
if (this.LoadDataChange != null)
{
this.LoadDataChange(null, null);
}
MessageBox.Show("Lưu trữ thành công!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
pt.CloseConnection();
}
}
}