Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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;
            }
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        /// <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);
        }
Ejemplo n.º 6
0
        /// <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);
        }
Ejemplo n.º 7
0
        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());
        }
Ejemplo n.º 8
0
        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;
            }
        }
Ejemplo n.º 9
0
        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();
        }
Ejemplo n.º 10
0
        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;
        }
Ejemplo n.º 11
0
        /// <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);
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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;
        }
Ejemplo n.º 14
0
        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;
                }
            }
        }
Ejemplo n.º 15
0
        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();
                }
            }
        }