Пример #1
0
        public static DataTable GetTableCapitalWorksDepartmentSummary(string cpw_year, int cpw_month, string directorate)
        {
            Database db = MyDLLs.MyDBFactory.GetDatabase(OPGlobals.dbProvider);

            string strsql = "Select A.manager_dept, A.manager_subdept, A.cpw_year," + cpw_month + " As cpw_month,"
                            + " Count(A.manager_subdept)As noofrecs, Sum(A.cpw_original_budget) As sum_cpw_org, Sum(C.capital_works_revised_budget) As sum_cpw_revised,"
                            + " Sum(B.capital_works_ytd) As cpw_ytod, Sum(D.capital_works_percentage) As sum_cpw_percentage From view_cpw A Left Join"
                            + " (Select capital_works_ytd.* From capital_works_ytd Where"
                            + " capital_works_month = " + cpw_month + " And capital_works_year = '" + cpw_year + "') B"
                            + " On A.cpw_id = B.capital_works_id And A.cpw_year = B.capital_works_year Left Join"
                            + " (Select capital_works_id, capital_works_year, capital_works_quarter, capital_works_revised_budget From capital_works_qbr"
                            + " Where capital_works_qbr.capital_works_quarter = " + OPGlobals.GetQuarter(cpw_month) + ") C"
                            + " On A.cpw_id = C.capital_works_id And A.cpw_year = C.capital_works_year Left Join"
                            + " (Select capital_works_monthly_progress.* From capital_works_monthly_progress Where"
                            + " capital_works_month = " + cpw_month + " AND capital_works_year='" + cpw_year + "') D"
                            + " On A.cpw_id = D.capital_works_id And A.cpw_year = D.capital_works_year"
                            + " WHERE A.director_id ='" + directorate + "'"
                            + " Group By A.manager_dept, A.manager_subdept, A.cpw_year";


            DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);

            DataTable tb = db.GetDataTable(conn, strsql);

            return(tb);
        }
Пример #2
0
        public bool UpdateCWPQBR(Database db, DbConnection con, DbTransaction trans)
        {
            bool result = false;
            Dictionary <string, dynamic> strdct = new Dictionary <string, dynamic>();

            strdct.Add("CPW_ID", this._cpwID);
            strdct.Add("CPW_Year", this._cpwYear);
            strdct.Add("CPW_Quarter", OPGlobals.GetQuarter(this.CapitalWorkMonth));
            strdct.Add("CPW_Revised", this._cpwReviseBudget);

            string query = @"UPDATE capital_works_qbr SET capital_works_revised_budget = @CPW_Revised"
                           + " WHERE capital_works_id = @CPW_ID AND capital_works_year = @CPW_Year AND capital_works_quarter = @CPW_Quarter;";

            try
            {
                db.InsertUpdateDeleteRecord(con, trans, query, strdct);
                result = true;
            }
            catch (Exception ex)
            {
                return(false);
            }

            return(result);
        }
Пример #3
0
        public bool InsertCWPQBR(Database db, DbConnection con, DbTransaction trans)
        {
            bool result = false;
            Dictionary <string, dynamic> strdct = new Dictionary <string, dynamic>();

            strdct.Add("CPW_ID", this._cpwID);
            strdct.Add("CPW_Year", this._cpwYear);
            strdct.Add("CPW_Quarter", OPGlobals.GetQuarter(this._cpwMonth));
            strdct.Add("CPW_Revised", this._cpwReviseBudget);

            string query = @"INSERT INTO capital_works_qbr (capital_works_id, capital_works_year,"
                           + " capital_works_quarter, capital_works_revised_budget) VALUES"
                           + " (@CPW_ID, @CPW_Year, @CPW_Quarter, @CPW_Revised);";

            try
            {
                db.InsertUpdateDeleteRecord(con, trans, query, strdct);
                result = true;
            }
            catch (Exception ex)
            {
                return(false);
            }

            return(result);
        }
Пример #4
0
        public static string GetSQLCapitalWorksYTDDataEntry(string cpw_year, int cpw_month)
        {
            int qtr = OPGlobals.GetQuarter(cpw_month);

            if (qtr > 1)
            {
                qtr -= 1;
            }

            string strsql = "SELECT A.capital_works_id AS cpw_id,"
                            + " A.capital_works_jobno AS cpw_jobno,"
                            + " A.capital_works_manager_id AS cpw_manager_id,"
                            + " A.capital_works_service_plann_id AS cpw_service_plann_id,"
                            + " A.capital_works_description AS cpw_description,"
                            + " A.capital_works_original_budget AS cpw_original_budget,"
                            + " C.service_plan AS service_plan,"
                            + " D.capital_works_revised_budget AS cpw_revised_budget,"
                            + " B.capital_works_ytd as cpw_ytd FROM capital_works A"
                            + " LEFT JOIN service_plan C ON C.id = A.capital_works_service_plann_id"
                            + " LEFT JOIN (SELECT capital_works_qbr.capital_works_id,"
                            + " capital_works_qbr.capital_works_year,"
                            + " capital_works_qbr.capital_works_quarter,"
                            + " capital_works_qbr.capital_works_revised_budget FROM capital_works_qbr"
                            + " WHERE capital_works_qbr.capital_works_year = '" + cpw_year + "' AND"
                            + " capital_works_qbr.capital_works_quarter = " + qtr + ") D ON"
                            + " D.capital_works_id = A.capital_works_id AND D.capital_works_year = A.capital_works_year"
                            + " LEFT JOIN (SELECT capital_works_ytd.capital_works_id,"
                            + " capital_works_ytd.capital_works_month,"
                            + " capital_works_ytd.capital_works_ytd,"
                            + " capital_works_ytd.capital_works_year FROM capital_works_ytd"
                            + " WHERE capital_works_ytd.capital_works_month = " + cpw_month + " AND capital_works_ytd.capital_works_year = '" + cpw_year + "'"
                            + " ORDER BY capital_works_ytd.capital_works_id) B ON"
                            + " B.capital_works_id = A.capital_works_id WHERE A.capital_works_year = '" + cpw_year + "'";

            //string strsql1 = "SELECT A.capital_works_id as cpw_id,"
            //    + " A.capital_works_jobno as cpw_jobno,"
            //    + " A.capital_works_manager_id as cpw_manager_id,"
            //    + " A.capital_works_service_plann_id as cpw_service_plann_id,"
            //    + " A.capital_works_description as cpw_description,"
            //    + " A.capital_works_original_budget as cpw_original_budget,"
            //    + " B.capital_works_year as cpw_year,"
            //    + " B.capital_works_month as cpw_month,"
            //    + " B.capital_works_ytd as cpw_ytd,"
            //    + " C.service_plan as service_plan,"
            //    + " D.capital_works_revised_budget as cpw_revised_budget"
            //    + " FROM capital_works A"
            //    + " LEFT JOIN capital_works_ytd B ON B.capital_works_id = A.capital_works_id AND B.capital_works_year = A.capital_works_year"
            //    + " LEFT JOIN service_plan C ON C.id = A.capital_works_service_plann_id"
            //    + " LEFT JOIN (SELECT capital_works_qbr.capital_works_id,"
            //    + " capital_works_qbr.capital_works_year,"
            //    + " capital_works_qbr.capital_works_quarter,"
            //    + " capital_works_qbr.capital_works_revised_budget"
            //    + " FROM capital_works_qbr WHERE capital_works_qbr.capital_works_year = '" + cpw_year
            //    + "' AND capital_works_qbr.capital_works_quarter = " + qtr + ") D"
            //    + " ON D.capital_works_id = A.capital_works_id AND D.capital_works_year = A.capital_works_year"
            //    + " WHERE B.capital_works_month = " + cpw_month + " AND B.capital_works_year = '" + cpw_year + "'";

            return(strsql);
        }
        private void tsbPreviousMonth_Click(object sender, EventArgs e)
        {
            string msg = "This will fill all empty cells with previous values, Do you want to Continue?";

            if (MessageBox.Show(msg, "OPERATION PLAN", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
            {
                int pMonth = OPGlobals.GetStrategyMeasurePrevouosMonth(mStartegyMEasureMonth);
                AddDatatoGridFromPrevious(OPGlobals.GetStrategyMeasurePrevouosYear(OPGlobals.currentYear, pMonth), pMonth);
            }
        }
Пример #6
0
        public static string GetSQLCapitalWorksMonthlyProgress(string cpw_year, int cpw_month)
        {
            string strsql = "";
            int    qtr    = OPGlobals.GetQuarter(cpw_month);

            strsql = "Select A.*, " + cpw_month + " As cpw_month, C.capital_works_ytd As cpw_ytod, D.capital_works_projected As cpw_projected,"
                     + " if(D.capital_works_percentage>0, D.capital_works_percentage,0) As cpw_percentage, D.capital_works_remark As cpw_remark"
                     + " From view_cpw_qbr A Left Join (Select capital_works_ytd.* From capital_works_ytd"
                     + " Where capital_works_ytd.capital_works_month = " + cpw_month
                     + " And capital_works_ytd.capital_works_year = '" + cpw_year + " ') C On A.cpw_id = C.capital_works_id And A.cpw_year = C.capital_works_year Left Join"
                     + " (Select capital_works_monthly_progress.* From capital_works_monthly_progress"
                     + " Where capital_works_monthly_progress.capital_works_month = " + cpw_month
                     + " And capital_works_monthly_progress.capital_works_year = '" + cpw_year + "') D"
                     + " On A.cpw_id = D.capital_works_id And A.cpw_year = D.capital_works_year";

            return(strsql);
        }
        public static string GetQueryStrategyMeasuresProgress(string cYear, int cMonth)
        {
            int mnth = OPGlobals.GetStrategyMeasureMonth(cMonth);
            //string strsql = "SELECT A.*, B.month, B.current_result, B.comment FROM view_strategy_measure A"
            //    + " LEFT JOIN (SELECT * FROM strategy_measure_monthly WHERE"
            //        + " strategy_measure_monthly.year = '" + cYear + "' AND"
            //        + " strategy_measure_monthly.month = " + mnth + ") B"
            //    + " ON B.strategy_measure_code = A.strategy_measure_code AND B.year = A.year AND B.strategy_id = A.strategy_id"
            //    + " WHERE A.year = '" + cYear + "'";

            string strsql = "SELECT A.*, B.progress_year, B.month, B.current_result, B.comment FROM view_strategy_measure A"
                            + " LEFT JOIN (SELECT strategy_measure_code, strategy_id, month, current_result, comment, year as progress_year"
                            + " FROM strategy_measure_monthly WHERE"
                            + " strategy_measure_monthly.year = '" + cYear + "' AND"
                            + " strategy_measure_monthly.month = " + mnth + ") B"
                            + " ON B.strategy_measure_code = A.strategy_measure_code AND B.strategy_id = A.strategy_id"
                            + " WHERE A.year = '17/18'";

            return(strsql);
        }
Пример #8
0
        public static DataTable GetTableCapitalWorksDepartmentSummary(string cpw_year, int cpw_month)
        {
            Database db = MyDLLs.MyDBFactory.GetDatabase(OPGlobals.dbProvider);

            string strsql = "Select A.manager_dept, A.manager_subdept As manager_description, A.cpw_year,"
                            + cpw_month + " As cpw_month, Sum(A.cpw_original_budget)As sum_cpw_org,"
                            + " Sum(B.capital_works_ytd) As sum_cpw_ytod, Sum(C.capital_works_revised_budget) as sum_cpw_revised From"
                            + " (Select capital_works_ytd.* From capital_works_ytd Where"
                            + " capital_works_month = " + cpw_month + " And capital_works_year = '" + cpw_year + " ') B Right Join"
                            + " view_cpw A On A.cpw_id = B.capital_works_id And A.cpw_year = B.capital_works_year Left Join"
                            + " (Select capital_works_id, capital_works_year, capital_works_quarter, capital_works_revised_budget From capital_works_qbr"
                            + " Where capital_works_qbr.capital_works_quarter = " + OPGlobals.GetQuarter(cpw_month) + ") C"
                            + " On A.cpw_id = C.capital_works_id And A.cpw_year = C.capital_works_year Group By"
                            + " A.manager_dept, A.manager_subdept, A.cpw_year";

            DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);

            DataTable tb = db.GetDataTable(conn, strsql);

            return(tb);
        }
Пример #9
0
 public static int GetStrategyMeasurePrevouosMonth()
 {
     return(GetStrategyMeasurePrevouosMonth(OPGlobals.GetStrategyMeasureMonth()));
 }
 private void GetPeriodRank()
 {
     this.PeriodRank = OPGlobals.GetStrategyMeasureRank(this.Year, this.Month);
 }
Пример #11
0
        static void Main()
        {
            //Application.EnableVisualStyles();
            //Application.SetCompatibleTextRenderingDefault(false);

            try
            {
                //----------Check if another instance of this application is already running---
                //if (Process.GetProcessesByName("NSCOperationalPlan").Length > 0)
                //{
                //    throw new Exception("Another Instance of this Program is already running");
                //}

                System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.AppStarting;

                OPGlobals.dbProvider = "MySql.Data.MySqlClient";
                OPGlobals.db         = MyDLLs.MyDBFactory.GetDatabase(OPGlobals.dbProvider);
                //OPGlobals.connString = "SERVER = nscutil; DATABASE = nsc_operation_plan_17_to_21; UID = sudinthap; PASSWORD = NSCv90cisc0;";
                OPGlobals.connString = "SERVER = nscutil; DATABASE = nsc_operation_plan_17_to_21; UID = opuser; PASSWORD = NSC@2390;";
                string exeFolder = Path.GetDirectoryName(Application.ExecutablePath);
                OPGlobals.reportParth = Path.Combine(exeFolder, @"Reports\");

                //=========================================
                if (AD_INTERGRATE)
                {
                    //NSCUtils.ADUser u = new NSCUtils.ADUser("masonli");
                    //NSCUtils.ADUser u = new NSCUtils.ADUser("saundzo");
                    //mccleti
                    NSCUtils.ADUser u = new ADUser();
                    if (string.IsNullOrEmpty(u.UserName))
                    {
                        throw new Exception("Sorry, You don't have enough permission to run this Program");
                    }
                    if (!LoadUser(u, "C"))
                    {
                        throw new Exception("Sorry, You don't have enough permission to run this Program");
                    }
                }
                else
                {
                }

                //Application.Run(new frmCPWMonthly());
                //Application.Run(new frmCPWQBR());

                Database     db     = null;
                string       strsql = "";
                DbConnection conn   = null;
                DataTable    tb     = null;

                db = MyDLLs.MyDBFactory.GetDatabase(OPGlobals.dbProvider);
                //DBConnect db = new DBConnect();
                strsql = "SELECT * FROM tbl_params ORDER BY ID DESC;";
                conn   = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
                tb     = db.GetDataTable(conn, strsql);

                OPGlobals.currentYear  = tb.Rows[0][1].ToString();
                OPGlobals.currentMonth = int.Parse(tb.Rows[0][2].ToString());

                //--- TODO ---
                //---  Remove this in production---
                //OPGlobals.currentMonth = OPGlobals.GetPreviousMonth(OPGlobals.currentMonth, OPGlobals.currentYear);
                //=================================



                OPGlobals.FinancialYearStarts = new DateTime(int.Parse(OPGlobals.currentYear.Substring(0, 2)), 7, 1);
                OPGlobals.CurrentQuarter      = OPGlobals.GetQuarter(OPGlobals.currentMonth);

                if (tb.Rows.Count > 1)
                {
                    OPGlobals.prevoiusYear  = tb.Rows[1][1].ToString();
                    OPGlobals.previousMonth = int.Parse(tb.Rows[1][2].ToString());
                }
                else
                {
                    OPGlobals.previousMonth = OPGlobals.GetPreviousMonth(OPGlobals.currentMonth, OPGlobals.currentYear);
                    OPGlobals.prevoiusYear  = OPGlobals.GetPreviousYear(OPGlobals.currentMonth, OPGlobals.currentYear);
                }

                //string exeFolder = Path.GetDirectoryName(Application.ExecutablePath);

                OPGlobals.reportParth = Path.Combine(exeFolder, @"Reports\");
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                //frmOperationPlan frmMDI = new frmOperationPlan();
                frmOperationPlan frmMDI = frmOperationPlan.getInstance();
                Application.Run(frmMDI);
            }
            catch (Exception ex)
            {
                CloseApplication(ex.Message, false);
            }
        }
        private bool SaveStrategyMeasureMonthlyProgress()
        {
            bool mRetVal = false;

            DataTable tb;
            string    msg;

            dgv.EndEdit();

            DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (DbTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    for (int i = 0; i < dgv.Rows.Count; i++)
                    {
                        if (string.IsNullOrEmpty(dgv.Rows[i].Cells["Progress"].Value.ToString()) && string.IsNullOrEmpty(dgv.Rows[i].Cells["Comments"].Value.ToString()))
                        {
                            continue;
                        }

                        StrategyMeasures sm = new StrategyMeasures();
                        sm.MeasureCode = dgv.Rows[i].Cells["Code"].Value.ToString();
                        sm.StrategyID  = dgv.Rows[i].Cells["strategy_id"].Value.ToString();
                        sm.Year        = OPGlobals.currentYear;
                        sm.Month       = OPGlobals.GetStrategyMeasureMonth();

                        double mProgress;
                        Double.TryParse(dgv.Rows[i].Cells["Progress"].Value.ToString(), out mProgress);
                        sm.CurrentProgress = mProgress;

                        sm.Comment = dgv.Rows[i].Cells["Comments"].Value.ToString();
                        if (!sm.IsExist())
                        {
                            sm.InsertMonthlyStrategyMeasures(db, conn, trans);
                        }
                        else
                        {
                            sm.UpdateMonthlyStrategyMeasures(db, conn, trans);
                        }

                        //DbConnection conn1 = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
                        //string t = "SELECT count(strategy_measure_code) as noofrecs FROM  strategy_measure_monthly"
                        //    + " WHERE strategy_measure_code = '" + sm.MeasureCode + "'"
                        //    + " AND strategy_id = '" + sm.StrategyID + "'"
                        //    + " AND year = '" + sm.Year + "' AND month = " + sm.Month;
                        //DataTable tb_temp = db.GetDataTable(conn, t);
                        //if (int.Parse(tb_temp.Rows[0]["noofrecs"].ToString()) == 0)
                        //{
                        //    sm.InsertMonthlyStrategyMeasures(db, conn, trans);
                        //} else
                        //{
                        //    sm.UpdateMonthlyStrategyMeasures(db, conn, trans);
                        //}
                    }
                    trans.Commit();
                    mRetVal = true;
                    //RefreshGridAfterSaveData();
                    msg = "Strategy Measures Monthly Progress has been saved/updated successfully";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    msg = "Data NOT Saved ..." + Environment.NewLine + ex.Message;
                }
            }

            conn.Close();
            MessageBox.Show(msg, "OPERATION PLAN", MessageBoxButtons.OK, MessageBoxIcon.Information);

            return(mRetVal);
        }