private void LoadTableFromDatabase() { DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString); dgv01.Rows.Clear(); dgv01.Refresh(); string strsql; strsql = CapitalWork.GetSQLCapitalWorksMonthlyProgress(OPGlobals.currentYear, OPGlobals.currentMonth) + " Order by A.director_id, A.cpw_manager_id, A.cpw_id; "; DataTable tb = db.GetDataTable(conn, strsql); try { foreach (DataRow row in tb.Rows) { dgv01.Rows.Add(new String[] { (dgv01.RowCount + 1).ToString(), row["cpw_id"].ToString(), row["service_plan"].ToString(), row["cpw_description"].ToString(), //string.Format("{0:$0,0.00}", row["cpw_original_budget"]), string.Format("{0:C}", row["cpw_original_budget"]), string.Format("{0:C}", row["cpw_revised_budget"]), string.Format("{0:C}", row["cpw_projected"]) }); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } dgv01.CurrentCell = null; }
private void FillCWPGrid() { DataRowView dr = (DataRowView)cboManagerFrom.SelectedItem; if (string.IsNullOrEmpty(dr["director_id"].ToString()) || string.IsNullOrEmpty(dr["manager_id"].ToString())) { return; } string strsql = CapitalWork.GetSQLCapitalWorksMonthlyProgress(OPGlobals.currentYear, OPGlobals.currentMonth); strsql += " WHERE director_id ='" + dr["director_id"].ToString() + "' AND cpw_manager_id ='" + dr["manager_id"].ToString() + "'"; DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString); DataTable tb = db.GetDataTable(conn, strsql); dgv03.Rows.Clear(); dgv03.Refresh(); try { foreach (DataRow row in tb.Rows) { dgv03.Rows.Add(new String[] { row["cpw_id"].ToString(), row["cpw_description"].ToString() }); } dgv03.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 LoadTableFromDatabase() { DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString); dgv01.Rows.Clear(); cpwitems.Clear(); //Clear Capital Work Object Array dgv01.Refresh(); CapitalWork cp; bool flagEdit; //number1 = Double.Parse(dgv01.Rows[i].Cells[6].Value.ToString(), NumberStyles.Currency); try { string strsql; strsql = CapitalWork.GetSQLCapitalWorksMonthlyProgress(OPGlobals.currentYear, OPGlobals.currentMonth); //strsql += " WHERE A.cpw_quarter = " + OPGlobals.GetQuarter(OPGlobals.currentMonth); DataRowView dr1 = (DataRowView)cboDirector.SelectedItem; DataRowView dr2 = (DataRowView)cboManager.SelectedItem; strsql += " WHERE A.cpw_year ='" + OPGlobals.currentYear + "'"; if (dr2 != null) { strsql += " AND cpw_manager_id ='" + dr2["manager_id"].ToString() + "'"; } else { if (opt0.Checked) { strsql += " AND cpw_manager_id ='" + OPGlobals.CurrentUser.ManagerID + "'"; } else if (opt1.Checked) { strsql += " AND director_id ='" + OPGlobals.CurrentUser.DirectorID + "'"; } } strsql += " Order by A.director_id, A.cpw_manager_id, A.cpw_id;"; double projected = 0; double revised = 0; DataTable tb = db.GetDataTable(conn, strsql); foreach (DataRow row in tb.Rows) { //if (string.IsNullOrEmpty(row["cpw_revised_budget"].ToString())) //{ //throw new CustomException("001"); //} revised = string.IsNullOrEmpty(row["cpw_revised_budget"].ToString()) ? 0 : Double.Parse(row["cpw_revised_budget"].ToString(), NumberStyles.Currency); projected = string.IsNullOrEmpty(row["cpw_projected"].ToString()) ? 0 : Double.Parse(row["cpw_projected"].ToString(), NumberStyles.Currency); //projected = revised; if (revised == 0) { revised = Double.Parse(row["cpw_original_budget"].ToString(), NumberStyles.Currency); } dgv01.Rows.Add(new String[] { (dgv01.RowCount + 1).ToString(), row["cpw_id"].ToString(), row["service_plan"].ToString(), row["cpw_description"].ToString(), string.Format("{0:$0,0.00}", revised), //row["cpw_revised_budget"]), string.Format("{0:$0,0.00}", string.IsNullOrEmpty(row["cpw_ytod"].ToString()) ? 0 : Double.Parse(row["cpw_ytod"].ToString(), NumberStyles.Currency)), string.Format("{0:$0,0.00}", (projected == 0) ? revised : projected), string.IsNullOrEmpty(row["cpw_percentage"].ToString()) ? "" : row["cpw_percentage"].ToString(), row["cpw_remark"].ToString(), string.Format("{0:$0,0.00}", projected), string.IsNullOrEmpty(row["cpw_percentage"].ToString()) ? "" : row["cpw_percentage"].ToString(), row["cpw_remark"].ToString(), string.IsNullOrEmpty(row["cpw_projected"].ToString()) ? "0" : "1" }); } dgv01.CurrentCell = null; } catch (CustomException ex) { MessageBox.Show("ERROR IN CPW" + Environment.NewLine + "Finance has not updated YTD details", "Operation Plan", MessageBoxButtons.OK, MessageBoxIcon.Error); tsbClose_Click(this, new EventArgs()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }