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); }
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); }
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); }
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); } }
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); }
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); }
public static int GetStrategyMeasurePrevouosMonth() { return(GetStrategyMeasurePrevouosMonth(OPGlobals.GetStrategyMeasureMonth())); }
private void GetPeriodRank() { this.PeriodRank = OPGlobals.GetStrategyMeasureRank(this.Year, this.Month); }
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); }