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