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 tsbDelete_Click(object sender, EventArgs e)
{
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
CapitalWork cpw = new CapitalWork();
cpw.DeleteCWP(db, conn, trans, txtcpwid.Text);
trans.Commit();
MessageBox.Show("Capital Work Project has been DELETED successfully", "OP MESSAGE", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show("Data NOT DELETED ..." + Environment.NewLine + ex.Message.ToString(), "OP ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
conn.Close();
}
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());
}
}
public static void PrintCapitalWorksDepartmentSummary(string cpw_year, int cpw_month, string department)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksDepartmentSummary(cpw_year, cpw_month, department);
frmprint.reportName = @"gphBudgetSummaryDepartment.rdlc";
frmprint.Show();
}
private void tsbSave_Click(object sender, EventArgs e)
{
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
CapitalWork cpw;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
string msg = "";
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
for (int i = 0; i < dgv01.Rows.Count; i++)
{
double number1, number2;
try
{ number1 = Double.Parse(dgv01.Rows[i].Cells[6].Value.ToString(), NumberStyles.Currency); }
catch (Exception ex) { number1 = 0; }
try
{ number2 = Double.Parse(dgv01.Rows[i].Cells[7].Value.ToString(), NumberStyles.Currency); }
catch (Exception ex) { number2 = 0; }
if (number1 != number2)
{
cpw = new CapitalWork(int.Parse(dgv01.Rows[i].Cells[1].Value.ToString()));
cpw.CapitalWorkYear = OPGlobals.currentYear;
cpw.CapitalWorkMonth = OPGlobals.currentMonth;
cpw.YearToDate = number1;
if (dgv01.Rows[i].Cells[8].Value.ToString() == "0")
{
cpw.InsertCWPYTD(db, conn, trans);
}
else
{
cpw.UpdateCWPYTD(db, conn, trans);
}
}
}
trans.Commit();
msg = "Capital Work YTD has been saved/updated successfully";
}
catch (Exception ex)
{
trans.Rollback();
msg = "Data NOT Saved ..." + Environment.NewLine + ex.Message;
}
}
conn.Close();
//msg += Environment.NewLine + "<<<<<<< OPERATION SUMMARY >>>>>>>";
MessageBox.Show(msg, "OPERATION PLAN", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void PrintCapitalWorksQBR(string cpw_year, int cpw_month)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksMonthlyProgress(cpw_year, cpw_month);
frmprint.reportName = @"rptcpwqbr.rdlc";
frmprint.Show();
}
public static void PrintCapitalWorksCounil(string cpw_year, int cpw_month, string serviceID)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksMonthlyProgress(cpw_year, cpw_month, serviceID);
frmprint.reportName = @"rptcpw_progressCouncil.rdlc";
frmprint.Show();
}
public static void PrintCapitalWorksServiceDetails(string cpw_year, int cpw_month, string servicePlanID)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksServiceDetails(cpw_year, cpw_month, servicePlanID);
frmprint.reportName = @"rptCPWServiceWise.rdlc";
frmprint.Show();
}
public static void PrintCapitalWorksServiceSummary(string cpw_year, int cpw_month)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksServiceSummary(cpw_year, cpw_month);
frmprint.reportName = @"rptCPWServiceSummary.rdlc";
frmprint.Show();
}
public static void PrintCapitalWorksMonthlyProgressByServicePlan(string cpw_year, int cpw_month)
{
frmPrint frmprint = new frmPrint();
frmprint.dataTable = CapitalWork.GetTableCapitalWorksMonthlyProgress(cpw_year, cpw_month);
frmprint.reportName = @"rptcpw_progress_by_service_plan.rdlc";
frmprint.Show();
}
private void tsbSave_Click(object sender, EventArgs e)
{
int startIndex = CapitalWork.getNextCPWIndexStatic();
for (int dr = 0; dr < dg1.RowCount; dr++)
{
CapitalWork cpw = new CapitalWork(dr + startIndex);
cpw.CapitalWorkJobCostNumber = dg1.Rows[dr].Cells["JCNo"].Value.ToString();
cpw.Description = dg1.Rows[dr].Cells["Description"].Value.ToString();
cpw.OriginalBudget = Double.Parse(string.IsNullOrEmpty(dg1.Rows[dr].Cells["Org.Budget"].Value.ToString()) ? "0" : dg1.Rows[dr].Cells["Org.Budget"].Value.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
cpw.RevisedBudget = Double.Parse(string.IsNullOrEmpty(dg1.Rows[dr].Cells["Rev.Budget"].Value.ToString()) ? "0" : dg1.Rows[dr].Cells["Rev.Budget"].Value.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
cpw.ServicePlanID = dg1.Rows[dr].Cells["ServiceID"].Value.ToString();
cpw.ManagerID = dg1.Rows[dr].Cells["ManagerID"].Value.ToString();
cpw.CapitalWorkYear = OPGlobals.currentYear;
cpw.CapitalWorkMonth = OPGlobals.currentMonth;
cpw.YearToDate = Double.Parse(string.IsNullOrEmpty(dg1.Rows[dr].Cells["YTD"].Value.ToString()) ? "0" : dg1.Rows[dr].Cells["YTD"].Value.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
bool saveresult = cpw.InsertCWP(db, conn, trans);
if (saveresult)
{
cpw.InsertCWPQBR(db, conn, trans);
cpw.InsertCWPYTD(db, conn, trans);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show("Data NOT Saved ..." + cpw.Description.ToString() + Environment.NewLine + ex.Message.ToString(), "OP ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
MessageBox.Show("Capital Work Project has been imported successfully", "OP MESSAGE", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
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();
dgv01.Refresh();
try
{
//string strsql = CapitalWork.GetSQLCapitalWorksYTD(OPGlobals.currentYear, OPGlobals.currentMonth)
// + " ORDER BY A.director_id, A.cpw_manager_id, A.cpw_id;";
DataTable tb = CapitalWork.GetTableCapitalWorksYTDDataEntry(OPGlobals.currentYear, OPGlobals.currentMonth);
//DataTable tb = db.GetDataTable(conn, strsql);
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:$0,0.00}", row["cpw_revised_budget"]),
string.Format("{0:$0,0.00}", row["cpw_ytd"]),
row["cpw_ytd"].ToString(),
(string.IsNullOrEmpty(row["cpw_ytd"].ToString()) ? "0" : "1"),
row["cpw_jobno"].ToString() });
//string.Format("{0:$0,0.00}", row["cpw_revised_budget"]),
}
dgv01.CurrentCell = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
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 void tsbSave_Click(object sender, EventArgs e)
{
bool saveresult = false;
//======== NEW CPW===========
CapitalWork cpw = new CapitalWork((string.IsNullOrEmpty(txtcpwid.Text) ? 0 : int.Parse(txtcpwid.Text)));
cpw.CapitalWorkJobCostNumber = txtJobCostNumber.Text;
DataRowView row;
row = (DataRowView)cboManager.SelectedItem;
if (row != null)
{
cpw.ManagerID = row["manager_id"].ToString();
}
cpw.CapitalWorkYear = txtFinYear.Text;
cpw.CapitalWorkMonth = OPGlobals.currentMonth;
row = (DataRowView)cboServicePlan.SelectedItem;
if (row != null)
{
cpw.ServicePlanID = row["id"].ToString();
}
row = (DataRowView)cboStrategyObj.SelectedItem;
if (row != null)
{
cpw.StrategyObjectiveID = row["id"].ToString();
}
cpw.Description = txtCPWDescription.Text;
double number;
if (Double.TryParse(txtCPWBudget.Text, out number))
{
cpw.OriginalBudget = number;
}
if (Double.TryParse(txtRevisedBudget.Text, out number))
{
cpw.RevisedBudget = number;
}
if (Double.TryParse(txtCarryOver.Text, out number))
{
cpw.CarryOverBudget = number;
}
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
try
{
if (cpw.CapitalWorkID == 0)
{
saveresult = cpw.InsertCWP(db, conn, trans);
if (saveresult)
{
cpw.InsertCWPQBR(db, conn, trans);
}
}
else
{
saveresult = cpw.UpdateCWP(db, conn, trans);
if (saveresult)
{
cpw.UpdateCWPQBR(db, conn, trans);
}
}
trans.Commit();
MessageBox.Show("Capital Work Project 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();
if (saveresult == true)
{
UpdateDataGrid(cpw.CapitalWorkID);
}
}
private bool SaveCapitalWorkMonthlyProgress()
{
bool mRetVal = false;
CapitalWork c;
double projected, percentage_completed;
string comments, msg;
DataTable tb;
dgv01.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 < dgv01.Rows.Count; i++)
{
if (!IsRowDataChanged(dgv01.Rows[i]))
{
continue;
}
try
{ projected = Double.Parse(dgv01.Rows[i].Cells["Projected"].Value.ToString(), NumberStyles.Currency); }
catch (Exception eex) { projected = 0; }
try
{ percentage_completed = Double.Parse(dgv01.Rows[i].Cells["% Comp."].Value.ToString(), NumberStyles.Currency); }
catch (Exception eex) { percentage_completed = 0; }
if (percentage_completed > 100 || percentage_completed < 0)
{
percentage_completed = 0;
}
try { comments = dgv01.Rows[i].Cells["Comment"].Value.ToString(); } catch (Exception eex) { comments = ""; }
c = new CapitalWork(int.Parse(dgv01.Rows[i].Cells["cpwID"].Value.ToString()));
c.CapitalWorkMonth = OPGlobals.currentMonth;
c.CapitalWorkYear = OPGlobals.currentYear;
c.ProjectedBudget = projected;
c.PercentageCompled = percentage_completed;
c.MonthlyComment = comments;
c.InsertUpdateMonthlyProgress(db, conn, trans);
}
trans.Commit();
mRetVal = true;
RefreshGridAfterSaveData();
msg = "Capital Work 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);
}
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());
}
}
private void tsbSave_Click(object sender, EventArgs e)
{
var exists = System.Diagnostics.Process.GetProcessesByName(System.IO.Path.GetFileNameWithoutExtension(System.Reflection.Assembly.GetEntryAssembly().Location)).Count() > 1;
bool opstatus = true;
string msg = "<<<<<<< OPERATION SUMMARY >>>>>>>" + Environment.NewLine;
pb1.Value = 0;
pb1.Maximum = dgv01.RowCount + 1;
pb1.Visible = true;
double projected;
double revised;
CapitalWork cw;
DbConnection conn = db.CreateDbConnection(Database.ConnectionType.ConnectionString, OPGlobals.connString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (DbTransaction trans = conn.BeginTransaction())
{
for (int i = 0; i < dgv01.RowCount; i++)
{
dgv01.CurrentCell = dgv01.Rows[i].Cells[0];
pb1.Value++;
try
{
projected = Double.Parse(string.IsNullOrEmpty(dgv01.Rows[i].Cells["Projected"].Value.ToString()) ? "0" : dgv01.Rows[i].Cells["Projected"].Value.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands | NumberStyles.AllowDecimalPoint);
revised = Double.Parse(string.IsNullOrEmpty(dgv01.Rows[i].Cells["Rev.Budget"].Value.ToString()) ? "0" : dgv01.Rows[i].Cells["Rev.Budget"].Value.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
if (projected <= 0)
{
continue;
}
cw = new CapitalWork(int.Parse(dgv01.Rows[i].Cells["cpwID"].Value.ToString()));
cw.CapitalWorkYear = OPGlobals.currentYear;
cw.CapitalWorkMonth = OPGlobals.currentMonth;
cw.RevisedBudget = projected;
if (!cw.InsertCWPQBR(db, conn, trans))
{
cw.UpdateCWPQBR(db, conn, trans);
}
}
catch (NullReferenceException ex1)
{
continue;
}
catch (Exception ex)
{
trans.Rollback();
msg = ex.Message;
break;
}
}
trans.Commit();
msg += "Data saved successfully";
LoadTableFromDatabase();
}
conn.Close();
MessageBox.Show(msg, "OPERATION PLAN", MessageBoxButtons.OK, MessageBoxIcon.Information);
pb1.Visible = false;
}
private void ReadFromExcel(string filePath)
{
ErrorCount = 0;
ErrorMessage = "";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange; // get the entire used range
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
double totYTD = 0.0;
double totRB = 0.0;
double temp = 0.0;
txtRows.Text = rowCount.ToString();
//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
pb1.Value = 0;
pb1.Maximum = rowCount;
pb1.Visible = true;
dg1.Rows.Clear();
string val;
try
{
for (int i = 2; i <= rowCount; i++)
{
if (xlRange.Cells[i, 2].Value2 != null)
{
dg1.Rows.Add(new String[] {
xlRange.Cells[i, 1].Value2.ToString(),
xlRange.Cells[i, 2].Value2.ToString(),
xlRange.Cells[i, 3].Value2.ToString()
});
temp = Double.Parse(string.IsNullOrEmpty(xlRange.Cells[i, 2].Value2.ToString()) ? "0" : xlRange.Cells[i, 2].Value2.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
totYTD += temp;
temp = Double.Parse(string.IsNullOrEmpty(xlRange.Cells[i, 3].Value2.ToString()) ? "0" : xlRange.Cells[i, 3].Value2.ToString(),
NumberStyles.AllowCurrencySymbol | NumberStyles.AllowDecimalPoint | NumberStyles.AllowThousands);
totRB += temp;
if (CapitalWork.IsExist(xlRange.Cells[i, 1].Value2.ToString()) == false)
{
ErrorCount++;
ErrorMessage += "Missing Capital Work (" + xlRange.Cells[i, 1].Value2.ToString() + ")" + Environment.NewLine;
}
}
}
txt1.Text = string.Format("{0:0.#0}", totYTD.ToString());
txt2.Text = string.Format("{0:0.#0}", totRB.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
pb1.Visible = false;
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
