private void LoadKPMFromDatabase(DataGridView dgv, string kpitype)
{
DataRowView dr = (DataRowView)cboManagerFrom.SelectedItem;
if (string.IsNullOrEmpty(dr["director_id"].ToString()) || string.IsNullOrEmpty(dr["manager_id"].ToString()))
{
return;
}
string strsql = KeyPerformanceIndex.GetQueryKPIwithProgress(OPGlobals.currentYear, OPGlobals.currentMonth, kpitype, dr["director_id"].ToString(), dr["manager_id"].ToString());
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
DataTable tb = db.GetDataTable(conn, strsql);
dgv.Rows.Clear();
dgv.Refresh();
try
{
foreach (DataRow row in tb.Rows)
{
dgv.Rows.Add(
row["kpi_id"].ToString(),
row["efficiency_description"].ToString(),
false
);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public static void TestSubReport(string opYear, int opMonth, string directorID, string managerID)
{
frmPrint frmprint = new frmPrint();
List <DataTable> tbList = new List <DataTable>();
Database db = MyDLLs.MyDBFactory.GetDatabase(OPGlobals.dbProvider);
string strsql = "";
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
//strsql = ReportQueries.QMonthlyProgress(opYear, opMonth, false, directorID, managerID);
strsql = MonthlyProgress.GetQueryMonthlyProgress(opYear, opMonth, directorID, managerID);
DataTable tb = db.GetDataTable(conn, strsql);
frmprint.dataTable = tb;
tb = CapitalWork.GetTableCapitalWorksMonthlyProgress(opYear, opMonth, directorID, managerID);
tbList.Add(tb);
strsql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(opYear, opMonth, directorID, managerID);
tb = db.GetDataTable(conn, strsql);
tbList.Add(tb);
frmprint.subDataTable = tbList;
frmprint.dataTable = tb;
frmprint.reportName = @"rpt1.rdlc";
frmprint.Show();
}
private void SaveKPMMonthlyProgress()
{
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
KeyPerformanceIndex kpi;
for (int x = 0; x < dgv.Count; x++)
{
dgv[x].EndEdit();
for (int i = 0; i < dgv[x].RowCount; i++)
{
if (!IsRowDataChanged(dgv[x].Rows[i]))
{
continue;
}
int id = int.Parse(dgv[x].Rows[i].Cells["kpi_id"].Value.ToString());
kpi = new KeyPerformanceIndex();
kpi.KPIID = id;
kpi.CurrentYear = OPGlobals.currentYear;
kpi.KPIMonth = OPGlobals.currentMonth;
try
{ kpi.CurrentValue = Double.Parse(dgv[x].Rows[i].Cells["Progress"].Value.ToString()); }
catch (Exception eex) { kpi.CurrentValue = 0; }
try
{ kpi.Remark = dgv[x].Rows[i].Cells["Remarks"].Value.ToString(); }
catch (NullReferenceException ex) { kpi.Remark = ""; }
kpi.InsertUpdateMonthlyProgress(db, conn, trans);
}
}
trans.Commit();
RefreshGridAfterSaveData();
MessageBox.Show("KPM Progress has been saved/updated successfully", "OP MESSAGE", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show("Data NOT Saved ..." + Environment.NewLine + ex.Message.ToString(), "OP ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
conn.Close();
}
private void LoadTableFromDatabase(DataGridView dgv, string kpitype)
{
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
dgv.Rows.Clear();
dgv.Refresh();
try
{
DataTable tb = KeyPerformanceIndex.GetKPITable(db, conn, cboManager.SelectedValue.ToString(), OPGlobals.currentYear, kpitype);
foreach (DataRow row in tb.Rows)
{
dgv.Rows.Add((dgv.RowCount + 1).ToString(),
row["kpi_id"].ToString(),
row["efficiency_description"].ToString(),
row["kpi_prefix_id"].ToString(),
row["kpi_prefix"].ToString(),
row["kpi_estimate"].ToString(),
row["unit_id"].ToString(),
row["kpi_unit_short"].ToString(),
row["manager_id"].ToString(),
row["manager_description"].ToString(),
row["kpi_estimate_year"].ToString(),
row["service_plan_id"].ToString(),
row["service_plan"].ToString(),
row["kpi_status"],
row["efficiency_description"].ToString(),
row["kpi_prefix_id"].ToString(),
row["kpi_estimate"].ToString(),
row["unit_id"].ToString(),
row["manager_id"].ToString(),
row["service_plan_id"].ToString(),
row["kpi_status"]
);
}
dgv.CurrentCell = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void PrintSubReportyMonth()
{
Months m = (Months)Enum.Parse(typeof(Months), cboOPMonth.SelectedValue.ToString());
//string month = OPGlobals.currentMonth.ToString();
string year = cboOPYear.Text;
string servicePlanSql = GetServicePlanForSubReporting(year, (int)m, cboServicePlan.SelectedValue.ToString());
//"Select A.id As service_plan_id, A.service_plan, B.actions As action, C.kpm As kpm, D.cwp As cwp From service_plan A"
//+ " Left Join(Select action.service_plan_id, COUNT(*) As actions From action Group By action.service_plan_id) B"
//+ " On B.service_plan_id = A.id Left Join (Select kpi.service_plan_id, COUNT(*) As kpm From kpi Group By kpi.service_plan_id) C"
//+ " On C.service_plan_id = A.id Left Join (Select capital_works.capital_works_service_plann_id As service_plan_id, COUNT(*) As cwp From capital_works Group By capital_works.capital_works_service_plann_id) D"
//+ " On D.service_plan_id = A.id where service_plan != '-NONE-';";
string actionSql = "", kpmSql = "", cwpSql = "";
actionSql = MonthlyProgress.GetQueryMonthlyProgress(cboServicePlan.SelectedValue.ToString(), cboOPYear.Text, (int)m);
cwpSql = CapitalWork.GetSQLCapitalWorksMonthlyProgress(cboServicePlan.SelectedValue.ToString(), cboOPYear.Text, (int)m);
kpmSql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(cboServicePlan.SelectedValue.ToString(), cboOPYear.Text, (int)m);
clsReports.PrintSubReport(servicePlanSql, actionSql, kpmSql, cwpSql);
}
private void tsbDelete_Click(object sender, EventArgs e)
{
string msg;
try
{
if (MessageBox.Show("THIS WILL DLETE KPI ID =" + txtKPIID.Text + Environment.NewLine + " DO YOU WANT TO CONTINUE", "OPERATION PLAN", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
KeyPerformanceIndex kpm = new KeyPerformanceIndex();
kpm.DeleteKPM(txtKPIID.Text);
msg = "KPM has been DELETED successfully";
btnEffiAdd_Click(this, e);
}
else
{
msg = "Operation has beed cancelled by the user";
}
}
catch (Exception ex)
{
msg = "Data NOT DELETED ..." + Environment.NewLine + ex.Message.ToString();
}
MessageBox.Show(msg, "OP MESSAGE", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private bool InsertWorkload(KeyPerformanceIndex kpi, DbConnection con, DbTransaction trans)
{
bool retval = true;
try
{
for (int i = 0; i < dgv03.RowCount; i++)
{
if (String.IsNullOrEmpty(dgv03.Rows[i].Cells[1].Value.ToString()))
{
kpi.KPIID = LAST_KPI_ID;
kpi.KPMID = "003";
kpi.ManagerID = dgv03.Rows[i].Cells[8].Value.ToString();
kpi.Description = dgv03.Rows[i].Cells[2].Value.ToString();
kpi.Prefix = dgv03.Rows[i].Cells[3].Value.ToString();
kpi.EstimateYear = dgv03.Rows[i].Cells[10].Value.ToString();
//double x = Convert.ToDouble(dgv01.Rows[i].Cells[5].Value);
kpi.EstimateValue = Convert.ToDouble(dgv03.Rows[i].Cells[5].Value);
kpi.Unit = dgv03.Rows[i].Cells[6].Value.ToString();
kpi.ServicePlanID = dgv01.Rows[i].Cells[11].Value.ToString();
kpi.InsertKPI(db, con, trans);
kpi.InsertKPIEstimate(db, con, trans);
LAST_KPI_ID++;
}
}
}
catch (Exception ex)
{
retval = false;
}
return(retval);
}
public static void PrintKPIProgressFull(string opYear, int opMonth, string directorID, string managerId)
{
string strsql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(opYear, opMonth, directorID, managerId);
PrintKPIProgressReportFull(strsql);
}
public static void PrintKPIProgressFull(string opYear, int opMonth)
{
string strsql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(opYear, opMonth);
PrintKPIProgressReportFull(strsql);
}
public static void PrintKPIProgressCouncil(string serviceID, string opYear, int opMonth)
{
string strsql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(serviceID, opYear, opMonth);
PrintKPIProgressReportCouncil(strsql);
}
public static void PrintKPIProgressCouncil(string opYear, int opMonth, string directorID)
{
string strsql = KeyPerformanceIndex.GetMonthlyKPIProgressQuery(opYear, opMonth, directorID);
PrintKPIProgressReportCouncil(strsql);
}
private void ChangeDelegation()
{
DataRowView row1 = (DataRowView)cboManagerFrom.SelectedItem;
DataRowView row2 = (DataRowView)cboManagerTo.SelectedItem;
string msg = "";
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
#region ------------- ACTIONS -----------
dgv01.ClearSelection();
for (int i = 0; i < dgv01.RowCount; i++)
{
bool chk = Convert.ToBoolean(dgv01.Rows[i].Cells["dgv01X"].Value);
if (chk == true)
{
clsAction action = new clsAction();
action.ActionID = dgv01.Rows[i].Cells["ID"].Value.ToString();
action.ManagerID = row1["manager_id"].ToString();
action.ChangeManagerAction(db, conn, trans, row2["manager_id"].ToString());
}
}
#endregion
#region ------------ KPM ---------------
for (int i = 0; i < dgvList.Count; i++)
{
dgvList[i].ClearSelection();
string dgvname = dgvList[i].Name.ToString() + "X";
for (int j = 0; j < dgvList[i].RowCount; j++)
{
bool chk = Convert.ToBoolean(dgvList[i].Rows[j].Cells[dgvname].Value);
if (chk == true)
{
KeyPerformanceIndex kpi = new KeyPerformanceIndex();
kpi.KPIID = int.Parse(dgvList[i].Rows[j].Cells["ID"].Value.ToString());
kpi.ManagerID = row1["manager_id"].ToString();
kpi.ChangeManagerKPI(db, conn, trans, row2["manager_id"].ToString());
}
}
}
#endregion
#region ------------- Capital Works -----------
dgv03.ClearSelection();
for (int i = 0; i < dgv03.RowCount; i++)
{
bool chk = Convert.ToBoolean(dgv03.Rows[i].Cells["dgv03X"].Value);
if (chk == true)
{
CapitalWork cwp = new CapitalWork();
cwp.CapitalWorkID = int.Parse(dgv03.Rows[i].Cells["ID"].Value.ToString());
cwp.ManagerID = row1["manager_id"].ToString();
cwp.ChangeManagerCWP(db, conn, trans, row2["manager_id"].ToString());
}
}
#endregion
trans.Commit();
msg = "Responsible Manager has been changed successfully";
}
catch (Exception ex)
{
trans.Rollback();
msg = "E R R O R !!! ..." + Environment.NewLine + ex.Message;
}
}
conn.Close();
MessageBox.Show(msg, "OPERATION PLAN", MessageBoxButtons.OK, MessageBoxIcon.Information);
FillActionGrid();
FillKPMGrid();
FillCWPGrid();
}
private bool SaveDataToDatabase(string kpm_id, DataGridView dgv, DbConnection con, DbTransaction trans)
{
bool retval = true;
KeyPerformanceIndex kpi;
LAST_KPI_ID = KeyPerformanceIndex.getNextKPIIndex();
int mKPIId;
bool mNewFlag = false;
try
{
for (int i = 0; i < dgv.RowCount; i++)
{
if (!MyGridUtils.IsColumnDataChanged(dgv.Rows[i],
new List <String>()
{
"Description", "PrefixID", "Value", "UnitID", "ManagerID", "SP_ID", "STATUS"
},
new List <String>()
{
"BDescription", "BPrefixID", "BValue", "BUnitID", "BManagerID", "BSP_ID", "BSTATUS"
}))
{
continue;
}
//---- Check for KPI Index in the Grid--
//--------- If Empty or Null New KPI otherwise Edit
if (String.IsNullOrEmpty(dgv.Rows[i].Cells["ID"].Value.ToString()))
{
mKPIId = LAST_KPI_ID;
LAST_KPI_ID++;
mNewFlag = true;
}
else
{
mKPIId = int.Parse(dgv.Rows[i].Cells["ID"].Value.ToString());
mNewFlag = false;
}
kpi = new KeyPerformanceIndex();
kpi.KPIID = mKPIId;
kpi.KPMID = kpm_id;
kpi.ManagerID = dgv.Rows[i].Cells["ManagerID"].Value.ToString();
kpi.Description = dgv.Rows[i].Cells["Description"].Value.ToString();
kpi.Prefix = dgv.Rows[i].Cells["PrefixID"].Value.ToString();
kpi.EstimateYear = dgv.Rows[i].Cells["Est.Year"].Value.ToString();
kpi.EstimateValue = Convert.ToDouble(dgv.Rows[i].Cells["Value"].Value);
kpi.Unit = dgv.Rows[i].Cells["UnitID"].Value.ToString();
kpi.ServicePlanID = dgv.Rows[i].Cells["SP_ID"].Value == null ? "000" : dgv.Rows[i].Cells["SP_ID"].Value.ToString();
kpi.Status = ((dgv.Rows[i].Cells["STATUS"].Value.ToString().ToUpper() == "TRUE") ? true : false);
if (mNewFlag)
{
kpi.InsertKPI(db, con, trans);
kpi.InsertKPIEstimate(db, con, trans);
}
else
{
kpi.UpdateKPI(db, con, trans);
kpi.UpdateKPIEstimate(db, con, trans);
}
}
} catch (Exception ex)
{
retval = false;
}
return(retval);
}
//-----------cboDirector_SelectedIndexChanged
//LoadManagers();
//-----------Manager SelectedIndexChanged---
//FillGrid();
#endregion
private void LoadFromDatabase(DataGridView dgv, string kpitype)
{
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
//DataTable tb;
string strsql;
DataRowView dr1 = (DataRowView)cboDirector.SelectedItem;
DataRowView dr2 = (DataRowView)cboManager.SelectedItem;
if (dr2 != null)
{
strsql = KeyPerformanceIndex.GetQueryKPIwithProgress(OPGlobals.currentYear, OPGlobals.currentMonth, kpitype, dr1["director_id"].ToString(), dr2["manager_id"].ToString());
}
else
{
if (opt2.Checked)
{
strsql = KeyPerformanceIndex.GetQueryKPIwithProgress(OPGlobals.currentYear, OPGlobals.currentMonth, kpitype);
//tb = KeyPerformanceIndex.GetKPIwithProgressTable(db, conn, OPGlobals.currentYear, OPGlobals.currentMonth, kpitype);
}
else if (opt1.Checked)
{
strsql = KeyPerformanceIndex.GetQueryKPIwithProgress(OPGlobals.currentYear, OPGlobals.currentMonth, kpitype, OPGlobals.CurrentUser.DirectorID);
//tb = KeyPerformanceIndex.GetKPIwithProgressTable(db, conn, OPGlobals.CurrentUser.DirectorID, OPGlobals.currentYear, OPGlobals.currentMonth, kpitype);
}
else
{
strsql = KeyPerformanceIndex.GetQueryKPIwithProgress(OPGlobals.currentYear, OPGlobals.currentMonth, kpitype, OPGlobals.CurrentUser.DirectorID, OPGlobals.CurrentUser.ManagerID);
//tb = KeyPerformanceIndex.GetKPIwithProgressTable(db, conn, OPGlobals.CurrentUser.ManagerID, OPGlobals.currentYear, OPGlobals.currentMonth, kpitype);
}
}
//strsql += " AND A.kpm_id = '" + kpitype + "'";
DataTable tb = db.GetDataTable(conn, strsql);
dgv.Rows.Clear();
dgv.Refresh();
try
{
foreach (DataRow row in tb.Rows)
{
dgv.Rows.Add(new String[] { (dgv.RowCount + 1).ToString(),
row["kpi_id"].ToString(),
row["efficiency_description"].ToString(),
row["kpi_prefix_short"].ToString(),
row["kpi_estimate"].ToString(),
row["kpi_unit_short"].ToString(),
row["manager_id"].ToString(),
row["kpi_progress"].ToString(),
row["kpi_remark"].ToString(),
row["kpm_id"].ToString(),
row["kpi_progress"].ToString(),
row["kpi_remark"].ToString() });
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
dgv.CurrentCell = null;
}