private void btnDelete_Click(object sender, EventArgs e) { int index = 0; string commandText = string.Empty; object obj; ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); string setupVariableID = string.Empty; string variableDatasetID = string.Empty; try { WaitShow("Deleting data..."); string selectedItem = lstDataSetVariable.SelectedItem.ToString(); int itemCount = lstDataSetVariable.Items.Count; int selectedIndex = lstDataSetVariable.SelectedIndex; lstDataSetVariable.Items.RemoveAt(selectedIndex); if (itemCount - 1 == selectedIndex) { selectedIndex--; } index = _dsSelectedData.Tables.IndexOf(selectedItem); _dsSelectedData.Tables.RemoveAt(index); if (_dsSelectedDataTemp.Tables.Contains(selectedItem)) { _dsSelectedDataTemp.Tables.Remove(selectedItem); } if (_datasetName != string.Empty) { commandText = string.Format("select SETUPVARIABLEDATASETID from SETUPVARIABLEDATASETS where setupid={0} and SETUPVARIABLEDATASETNAME='{1}'", CommonClass.ManageSetup.SetupID, _datasetName); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); int ID = int.Parse(obj.ToString()); commandText = string.Format("select SetUpVariableID from SetUpVariables where SetUpVariableName='{0}' and SETUPVARIABLEDATASETID={1}", selectedItem, ID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); setupVariableID = obj.ToString(); commandText = string.Format("delete from SetUpGeographicVariables where SetUpVariableID={0}", setupVariableID); int rtn = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); commandText = string.Format("select SetUpVariableDataSetID from SetUpVariableDataSets where SetUpVariableDataSetName='{0}'", txtDataSetName.Text); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); variableDatasetID = obj.ToString(); commandText = string.Format("delete from SETUPVARIABLES where SetUpVariableID={0}", setupVariableID); rtn = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); if (lstDataSetVariable.Items.Count == 0) { DataTable dt = new DataTable(); olvData.DataSource = dt; } } lstDataSetVariable.SelectedIndex = selectedIndex; System.Threading.Thread.Sleep(300); WaitClose(); } catch (Exception ex) { WaitClose(); Logger.LogError(ex); } }
private void loadGrid() { try { ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); string commondtext = string.Format("select GridDefinitionName,GridDefinitionID,Ttype from GridDefinitions where setupid={0} order by GridDefinitionName asc", CommonClass.ManageSetup.SetupID); System.Data.DataSet ds = fb.ExecuteDataset(CommonClass.Connection, new CommandType(), commondtext); Dictionary <int, string> dicGridType = new Dictionary <int, string>(); dicShapeOrRegular = new Dictionary <int, int>(); foreach (DataRow dr in ds.Tables[0].Rows) { dicGridType.Add(Convert.ToInt32(dr["GridDefinitionID"]), dr["GridDefinitionName"].ToString()); dicShapeOrRegular.Add(Convert.ToInt16(dr["GridDefinitionID"]), Convert.ToInt16(dr["Ttype"])); } for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { lstAvailableGrid.Items.Add(new ListItem(ds.Tables[0].Rows[i]["GridDefinitionID"].ToString(), ds.Tables[0].Rows[i]["GridDefinitionName"].ToString())); cboDefaultGridType.Items.Add(ds.Tables[0].Rows[i]["GridDefinitionName"].ToString()); } commondtext = string.Format("select GridDefinitionName from GridDefinitions where setupid={0} and defaultType=1", CommonClass.ManageSetup.SetupID); object defaultGrid = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commondtext); if (defaultGrid != null) { cboDefaultGridType.Text = defaultGrid.ToString(); } if (lstAvailableGrid.Items.Count != 0) { lstAvailableGrid.SelectedIndex = 0; } } catch (Exception ex) { Logger.LogError(ex.Message); } }
private void btnOK_Click(object sender, EventArgs e) { try { ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); DataSet dsFunctions = new DataSet(); string commandText = "select * from FUNCTIONALFORMS"; dsFunctions = fb.ExecuteDataset(CommonClass.Connection, new CommandType(), commandText); List <string> listFunctions = new List <string>(); int rowFunctionsCount = dsFunctions.Tables[0].Rows.Count; for (int i = 0; i < rowFunctionsCount; i++) { listFunctions.Add(dsFunctions.Tables[0].Rows[i][1].ToString()); } commandText = "select * from BASELINEFUNCTIONALFORMS"; DataSet dsBaselineFunctions = fb.ExecuteDataset(CommonClass.Connection, new CommandType(), commandText); List <string> listBaselineFunctions = new List <string>(); int rowBaselineFunctionsCount = dsBaselineFunctions.Tables[0].Rows.Count; for (int j = 0; j < rowBaselineFunctionsCount; j++) { listBaselineFunctions.Add(dsBaselineFunctions.Tables[0].Rows[j][1].ToString()); } List <string> lstSystemVariableName = Configuration.ConfigurationCommonClass.getAllSystemVariableNameList(); Dictionary <string, double> dicVariable = new Dictionary <string, double>(); foreach (string s in lstSystemVariableName) { dicVariable.Add(s, 1); } string functionText = Configuration.ConfigurationCommonClass.getFunctionStringFromDatabaseFunction(txtFunction.Text); double functionResult = Configuration.ConfigurationCommonClass.getValueFromPointEstimateFunctionString("0", functionText, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, dicVariable); if (txtFunction.Text == string.Empty || functionResult == -999999999.0) { MessageBox.Show("Please input a valid value for 'Function'."); return; } else { if (!listFunctions.Contains(txtFunction.Text)) { commandText = "select max(FUNCTIONALFORMID) from FUNCTIONALFORMS"; object objFunction = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); int functionsID = int.Parse(objFunction.ToString()) + 1; commandText = string.Format("insert into FunctionalForms values ({0},'{1}')", functionsID, txtFunction.Text); int rth = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); } } if (Tools.CalculateFunctionString.dicPointEstimateMethodInfo != null) { Tools.CalculateFunctionString.dicPointEstimateMethodInfo.Clear(); } string baselineFunctionText = Configuration.ConfigurationCommonClass.getFunctionStringFromDatabaseFunction(txtBaselineIncidenceFunction.Text); double baselineFunctionResult = Configuration.ConfigurationCommonClass.getValueFromBaseFunctionString("1", baselineFunctionText, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, dicVariable); if (baselineFunctionResult == -999999999.0) { MessageBox.Show("Please input a valid value for 'Baseline Function'."); return; } else { if (!listBaselineFunctions.Contains(txtBaselineIncidenceFunction.Text)) { commandText = "select max(FUNCTIONALFORMID) from BASELINEFUNCTIONALFORMS"; object objBaselineFunction = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); int BaselienFunctionID = int.Parse(objBaselineFunction.ToString()) + 1; commandText = string.Format("insert into BaselineFunctionalForms values ({0},'{1}')", BaselienFunctionID, txtBaselineIncidenceFunction.Text); int rtn = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); } } bool ok = false; ok = IsValidDate(txtYear.Text); if (ok == false) { MessageBox.Show("Please input a valid year."); return; } if (txtBetaParameter1.Text == string.Empty) { MessageBox.Show("'Beta Parameter 1' can not be null. Please input a valid value."); return; } if (txtBetaParameter2.Text == string.Empty) { MessageBox.Show("'Beta Parameter 2' can not be null. Please input a valid value."); return; } if (txtBeta.Text == string.Empty) { MessageBox.Show("'Beta' can not be null. Please input a valid value."); return; } if (txtCconstantValue.Text == string.Empty) { MessageBox.Show("'C' can not be null. Please input a valid value."); return; } if (txtBconstantValue.Text == string.Empty) { MessageBox.Show("'B' can not be null. Please input a valid value."); return; } if (nudownStartAge.Value > nudownEndAge.Value) { MessageBox.Show("The end age must be higher than start age."); return; } if (txtAconstantValue.Text == string.Empty) { MessageBox.Show("'A' can not be null. Please input a valid value."); return; } _healthImpacts.EndpointGroup = cboEndpointGroup.Text; _healthImpacts.Endpoint = cboEndpoint.Text; _healthImpacts.Pollutant = cboPollutant.Text; _healthImpacts.Metric = cboMetric.Text; _healthImpacts.MetricStatistis = cboMetricStatistic.Text; _healthImpacts.SeasonalMetric = cboSeasonalMetric.Text; _healthImpacts.Race = cboRace.Text; _healthImpacts.Ethnicity = cboEthnicity.Text; _healthImpacts.Gender = cboGender.Text; _healthImpacts.StartAge = nudownStartAge.Value.ToString(); _healthImpacts.EndAge = nudownEndAge.Value.ToString(); _healthImpacts.Author = txtAnthor.Text; _healthImpacts.Year = txtYear.Text; _healthImpacts.Location = txtLocation.Text; _healthImpacts.LocationName = cboLocationName.Text; _healthImpacts.Qualifier = txtQualifier.Text; _healthImpacts.OtherPollutant = txtOtherPollutant.Text; _healthImpacts.Reference = txtReference.Text; _healthImpacts.Function = txtFunction.Text; _healthImpacts.BaselineIncidenceFunction = txtBaselineIncidenceFunction.Text; _healthImpacts.BetaDistribution = cboBetaDistribution.Text; _healthImpacts.Beta = txtBeta.Text; _healthImpacts.BetaParameter1 = txtBetaParameter1.Text; _healthImpacts.BetaParameter2 = txtBetaParameter2.Text; _healthImpacts.AConstantDescription = txtAconstantDescription.Text; _healthImpacts.AConstantValue = txtAconstantValue.Text; _healthImpacts.BConstantDescription = txtBconstantDescription.Text; _healthImpacts.BConstantValue = txtBconstantValue.Text; _healthImpacts.CConstantDescription = txtCconstantDescription.Text; _healthImpacts.CConstantValue = txtCconstantValue.Text; _healthImpacts.Incidence = cboIncidenceDataSet.Text; _healthImpacts.Prevalence = cboPrevalenceDataSet.Text; _healthImpacts.Variable = cboVariableDataSet.Text; _listCustom = list; this.DialogResult = DialogResult.OK; } catch (Exception ex) { Logger.LogError(ex); } }
private void btnOK_Click(object sender, EventArgs e) { try { if (txtDatabase.Text == string.Empty) { MessageBox.Show("Please select a datafile."); return; } ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); string commandText = string.Format("select inflationdatasetid from inflationdatasets where setupid={0} and inflationdatasetname='{1}'", CommonClass.ManageSetup.SetupID, txtInflationDataSetName.Text); object obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { MessageBox.Show("This inflation dataset name is already in use. Please enter a different name."); return; } DataTable dt = new DataTable(); string strfilename = string.Empty; string strtablename = string.Empty; commandText = string.Empty; dt = CommonClass.ExcelToDataTable(txtDatabase.Text); int iYear = -1; int iAllGoodsIndex = -1; int iMedicalCostIndex = -1; int iWageIndex = -1; for (int i = 0; i < dt.Columns.Count; i++) { switch (dt.Columns[i].ColumnName.ToLower().Replace(" ", "")) { case "year": iYear = i; break; case "allgoodsindex": iAllGoodsIndex = i; break; case "medicalcostindex": iMedicalCostIndex = i; break; case "wageindex": iWageIndex = i; break; } } string warningtip = ""; if (iYear < 0) { warningtip = "'Year', "; } if (iAllGoodsIndex < 0) { warningtip += "'AllGoodsIndex', "; } if (iMedicalCostIndex < 0) { warningtip += "'MedicalCostIndex', "; } if (iWageIndex < 0) { warningtip += "'WageIndex', "; } if (warningtip != "") { warningtip = warningtip.Substring(0, warningtip.Length - 2); warningtip = "Please check the column header of " + warningtip + ". It is incorrect or does not exist."; MessageBox.Show(warningtip, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } commandText = "SELECT max(INFLATIONDATASETID) from INFLATIONDATASETS"; int inflationdatasetid = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText)) + 1; commandText = string.Format("insert into INFLATIONDATASETS VALUES({0},{1},'{2}' )", inflationdatasetid, CommonClass.ManageSetup.SetupID, txtInflationDataSetName.Text); int rth = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); int currentDataSetID = inflationdatasetid; if (dt == null) { return; } int rtn = 0; foreach (DataRow row in dt.Rows) { if (row == null) { continue; } commandText = string.Format("insert into INFLATIONENTRIES values({0},{1},{2},{3},{4})", currentDataSetID, int.Parse(row[iYear].ToString()), row[iAllGoodsIndex], row[iMedicalCostIndex], row[iWageIndex]); rtn = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); } if (rtn != 0) { InflationDataSetName = txtInflationDataSetName.Text; } } catch (Exception ex) { Logger.LogError(ex); } this.DialogResult = DialogResult.OK; this.Close(); }
public Dictionary <string, List <GridRelationshipAttributePercentage> > getRelationshipFromBenMAPGridPercentage(int big, int small) { try { IFeatureSet fsBig = new FeatureSet(); IFeatureSet fsSmall = new FeatureSet(); BenMAPGrid bigBenMAPGrid = Grid.GridCommon.getBenMAPGridFromID(big == 20 ? 18 : big); BenMAPGrid smallBenMAPGrid = Grid.GridCommon.getBenMAPGridFromID(small == 20 ? 18 : small); if (bigBenMAPGrid == null) { bigBenMAPGrid = new ShapefileGrid() { ShapefileName = "County_epa2", } } ; if (smallBenMAPGrid == null) { smallBenMAPGrid = new ShapefileGrid() { ShapefileName = "County_epa2", } } ; string bigShapefileName = ""; string smallShapefileName = ""; if (bigBenMAPGrid as ShapefileGrid != null) { bigShapefileName = (bigBenMAPGrid as ShapefileGrid).ShapefileName; } else { bigShapefileName = (bigBenMAPGrid as RegularGrid).ShapefileName; } if (smallBenMAPGrid as ShapefileGrid != null) { smallShapefileName = (smallBenMAPGrid as ShapefileGrid).ShapefileName; } else { smallShapefileName = (smallBenMAPGrid as RegularGrid).ShapefileName; } ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); string finsSetupname = string.Format("select setupname from setups where setupid in (select setupid from griddefinitions where griddefinitionid={0})", big); string setupname = Convert.ToString(fb.ExecuteScalar(CommonClass.Connection, CommandType.Text, finsSetupname)); if (File.Exists(CommonClass.DataFilePath + @"\Data\Shapefiles\" + setupname + "\\" + bigShapefileName + ".shp")) { string shapeFileName = CommonClass.DataFilePath + @"\Data\Shapefiles\" + setupname + "\\" + bigShapefileName + ".shp"; fsBig = DotSpatial.Data.FeatureSet.Open(shapeFileName); string shapeFileNameSmall = CommonClass.DataFilePath + @"\Data\Shapefiles\" + setupname + "\\" + smallShapefileName + ".shp"; fsSmall = DotSpatial.Data.FeatureSet.Open(shapeFileNameSmall); List <GridRelationshipAttributePercentage> lstGR = null; if (big == 20 || small == 20) { lstGR = CommonClass.IntersectionPercentageNation(fsBig, fsSmall, FieldJoinType.All, big, small); } else { lstGR = CommonClass.IntersectionPercentage(fsBig, fsSmall, FieldJoinType.All); } Dictionary <string, List <GridRelationshipAttributePercentage> > dic = new Dictionary <string, List <GridRelationshipAttributePercentage> >(); dic.Add(small + "," + big, lstGR); return(dic); string commandText = "select max(PercentageID) from GridDefinitionPercentages"; int iMax = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, CommandType.Text, commandText)) + 1; commandText = string.Format("insert into GridDefinitionPercentages values({0},{1},{2})", iMax, small, big); fb.ExecuteNonQuery(CommonClass.Connection, CommandType.Text, commandText); foreach (GridRelationshipAttributePercentage grp in lstGR) { commandText = string.Format("insert into GridDefinitionPercentageEntries values({0},{1},{2},{3},{4},{5},{6})", iMax, grp.sourceCol, grp.sourceRow, grp.targetCol, grp.targetRow, grp.percentage, 0); fb.ExecuteNonQuery(CommonClass.Connection, CommandType.Text, commandText); } } else { return(null); } } catch (Exception ex) { return(null); } }
private void btnOK_Click(object sender, EventArgs e) { try { List <ModelResultAttribute> lstResult = new List <ModelResultAttribute>(); BenMAPLine inputBenMAPLine = new BenMAPLine(); List <ModelResultAttribute> outModelResultAttributes = new List <ModelResultAttribute>(); List <Dictionary <string, float> > lstBig = new List <Dictionary <string, float> >(); List <ModelResultAttribute> lstSmall = new List <ModelResultAttribute>(); string err = ""; inputBenMAPLine = DataSourceCommonClass.LoadAQGFile(txtAirQualitySurface.Text, ref err); if (inputBenMAPLine == null) { MessageBox.Show(err); return; } DataRowView drOutput = (cboAggregationSurface.SelectedItem) as DataRowView; int outputGridDefinitionID = Convert.ToInt32(drOutput["GridDefinitionID"]); if (inputBenMAPLine.GridType.GridDefinitionID == outputGridDefinitionID) { MessageBox.Show("The AQG file you want to aggregate is already at the spatial resolution of the selected aggregation surface."); return; } SaveFileDialog saveOutAQG = new SaveFileDialog(); saveOutAQG.Filter = "aqgx file|*.aqgx"; saveOutAQG.InitialDirectory = CommonClass.ResultFilePath + @"\Result\AQG"; saveOutAQG.RestoreDirectory = true; if (saveOutAQG.ShowDialog() == DialogResult.Cancel) { return; } WaitShow("Aggregating air quality surface..."); string filePath = saveOutAQG.FileName.Substring(0, saveOutAQG.FileName.LastIndexOf(@"\") + 1); string fileName = saveOutAQG.FileName.Substring(saveOutAQG.FileName.LastIndexOf(@"\") + 1).Replace("aqgx", "shp"); int GridFrom = inputBenMAPLine.GridType.GridDefinitionID; if (GridFrom == 28) { GridFrom = 27; } int GridTo = outputGridDefinitionID; if (GridTo == 28) { GridTo = 27; } if (GridFrom == GridTo) { outModelResultAttributes = inputBenMAPLine.ModelResultAttributes; } else { string str = string.Format("select sourcecolumn, sourcerow, targetcolumn, targetrow,Percentage from GridDefinitionPercentages a,GridDefinitionPercentageEntries b where a.PercentageID=b.PercentageID and a.SourceGridDefinitionID={0} and a.TargetGridDefinitionID={1} and normalizationstate in (0,1)", GridFrom, GridTo); ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); DataSet ds; int iCount = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, CommandType.Text, "select count(*) from (" + str + " ) a")); if (iCount == 0) { Configuration.ConfigurationCommonClass.creatPercentageToDatabase(GridTo, GridFrom); iCount = 1; } if (iCount != 0) { Dictionary <string, Dictionary <string, double> > dicRelationShip = new Dictionary <string, Dictionary <string, double> >(); ds = fb.ExecuteDataset(CommonClass.Connection, CommandType.Text, str); foreach (DataRow dr in ds.Tables[0].Rows) { if (dicRelationShip.ContainsKey(dr["targetcolumn"].ToString() + "," + dr["targetrow"].ToString())) { if (!dicRelationShip[dr["targetcolumn"].ToString() + "," + dr["targetrow"].ToString()].ContainsKey(dr["sourcecolumn"].ToString() + "," + dr["sourcerow"].ToString())) { dicRelationShip[dr["targetcolumn"].ToString() + "," + dr["targetrow"].ToString()].Add(dr["sourcecolumn"].ToString() + "," + dr["sourcerow"].ToString(), Convert.ToDouble(dr["Percentage"])); } } else { dicRelationShip.Add(dr["targetcolumn"].ToString() + "," + dr["targetrow"].ToString(), new Dictionary <string, double>()); dicRelationShip[dr["targetcolumn"].ToString() + "," + dr["targetrow"].ToString()].Add(dr["sourcecolumn"].ToString() + "," + dr["sourcerow"].ToString(), Convert.ToDouble(dr["Percentage"])); } } ds.Dispose(); Dictionary <string, ModelResultAttribute> dicModelFrom = new Dictionary <string, ModelResultAttribute>(); foreach (ModelResultAttribute mo in inputBenMAPLine.ModelResultAttributes) { dicModelFrom.Add(mo.Col + "," + mo.Row, mo); } foreach (KeyValuePair <string, Dictionary <string, double> > gra in dicRelationShip) { if (gra.Value == null || gra.Value.Count == 0) { continue; } ModelResultAttribute anew = new ModelResultAttribute(); anew.Col = Convert.ToInt32(gra.Key.Split(new char[] { ',' }).ToArray()[0]); anew.Row = Convert.ToInt32(gra.Key.Split(new char[] { ',' }).ToArray()[1]); anew.Values = new Dictionary <string, float>(); Dictionary <string, float> dicValue = new Dictionary <string, float>(); for (int i = 0; i < gra.Value.Count; i++) { if (dicModelFrom.ContainsKey(gra.Value.ToList()[i].Key)) { foreach (KeyValuePair <string, float> k in dicModelFrom[gra.Value.ToList()[i].Key].Values) { if (!anew.Values.ContainsKey(k.Key)) { anew.Values.Add(k.Key, Convert.ToSingle(k.Value * gra.Value.ToList()[i].Value)); } else { anew.Values[k.Key] += Convert.ToSingle(k.Value * gra.Value.ToList()[i].Value); } } } } if (anew.Values != null && anew.Values.Count > 0) { List <string> lstKey = anew.Values.Keys.ToList(); foreach (string k in lstKey) { anew.Values[k] = Convert.ToSingle(anew.Values[k] / gra.Value.Sum(p => p.Value)); } } outModelResultAttributes.Add(anew); } } } inputBenMAPLine.ModelResultAttributes = outModelResultAttributes; inputBenMAPLine.ModelAttributes = null; inputBenMAPLine.GridType = Grid.GridCommon.getBenMAPGridFromID(outputGridDefinitionID); DataSourceCommonClass.CreateAQGFromBenMAPLine(inputBenMAPLine, saveOutAQG.FileName); WaitClose(); MessageBox.Show("File saved.", "File saved"); this.DialogResult = DialogResult.OK; } catch (Exception ex) { WaitClose(); Logger.LogError(ex.Message); } }
private void btnOK_Click(object sender, EventArgs e) { Object obj = null; string commandText = string.Empty; if (txtDataSetName.Text == string.Empty) { MessageBox.Show("Please input a valid dataset name."); return; } if (lstDataSetVariable.Items.Count == 0) { MessageBox.Show("Please load a variable datafile."); return; } ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); if (CommonClass.Connection.State != ConnectionState.Open) { CommonClass.Connection.Open(); } FirebirdSql.Data.FirebirdClient.FbConnection fbconnection = CommonClass.getNewConnection(); fbconnection.Open(); FirebirdSql.Data.FirebirdClient.FbTransaction fbtra = fbconnection.BeginTransaction(); FirebirdSql.Data.FirebirdClient.FbCommand fbCommand = new FirebirdSql.Data.FirebirdClient.FbCommand(); fbCommand.Connection = fbconnection; fbCommand.CommandType = CommandType.Text; fbCommand.Transaction = fbtra; DataSet ds = new DataSet(); DataTable dt; int rowCount; string variableDatasetID = string.Empty; int variableID = 0; try { lblProgressBar.Visible = true; progBarVariable.Visible = true; progBarVariable.Value = 0; progBarVariable.Minimum = 0; progBarVariable.Maximum = 0; progBarVariable.Refresh(); commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (_datasetName == string.Empty) { if (obj != null) { MessageBox.Show("The dataset name has already been defined. Please enter a different name."); return; } commandText = "select max(SETUPVARIABLEDATASETID) from SETUPVARIABLEDATASETS"; obj = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText)) + 1; variableDatasetID = obj.ToString(); commandText = string.Format("insert into SetUpVariableDataSets values({0},{1},'{2}')", variableDatasetID, CommonClass.ManageSetup.SetupID, txtDataSetName.Text); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); string gridDefinationID = obj.ToString(); int count = _dsSelectedData.Tables.Count; foreach (DataTable dtcount in _dsSelectedData.Tables) { progBarVariable.Maximum += dtcount.Rows.Count; } commandText = "select max(SETUPVARIABLEID) from SetUpVariables"; obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { variableID = Convert.ToInt32(obj); } for (int i = 0; i < count; i++) { dt = _dsSelectedData.Tables[i].Clone(); dt = _dsSelectedData.Tables[i].Copy(); string variableName = dt.TableName; commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj == null) { variableID++; commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); rowCount = dt.Rows.Count; for (int j = 0; j < (rowCount / 125) + 1; j++) { commandText = "execute block as declare SetupVariableID int;" + " BEGIN "; for (int k = 0; k < 125; k++) { if (j * 125 + k < dt.Rows.Count) { commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID); progBarVariable.Value++; progBarVariable.Refresh(); lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%"; lblProgressBar.Refresh(); } else { continue; } commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]); } commandText = commandText + "END"; fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); } } } } else { commandText = string.Format("select SETUPVARIABLEDATASETID from SetUpVariableDataSets where SETUPVARIABLEDATASETNAME='{0}' and SetupID={1}", _datasetName, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); variableDatasetID = obj.ToString(); commandText = string.Format("update SETUPVARIABLEDATASETS set SETUPVARIABLEDATASETNAME='{0}' where setupid={1} and SETUPVARIABLEDATASETID={2}", txtDataSetName.Text, CommonClass.ManageSetup.SetupID, variableDatasetID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); commandText = string.Format("select GridDefinitionID from GridDefinitions where GridDefinitionName='{0}' and SetupID={1}", txtGridDefinition.Text, CommonClass.ManageSetup.SetupID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); string gridDefinationID = obj.ToString(); int count = _dsSelectedDataTemp.Tables.Count; foreach (DataTable dtcount in _dsSelectedDataTemp.Tables) { progBarVariable.Maximum += dtcount.Rows.Count; } commandText = "select max(SETUPVARIABLEID) from SetUpVariables"; obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { variableID = Convert.ToInt32(obj); } for (int i = 0; i < count; i++) { dt = _dsSelectedDataTemp.Tables[i].Clone(); dt = _dsSelectedDataTemp.Tables[i].Copy(); string variableName = dt.TableName; commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", variableName, variableDatasetID); obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); variableID++; commandText = string.Format("insert into SetUpVariables values({0},{1},'{2}','{3}')", variableID, variableDatasetID, variableName, gridDefinationID); fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); rowCount = dt.Rows.Count; for (int j = 0; j < (rowCount / 125) + 1; j++) { commandText = "execute block as declare SetupVariableID int;" + " BEGIN "; for (int k = 0; k < 125; k++) { if (j * 125 + k < dt.Rows.Count) { commandText = commandText + string.Format(" select SetupVariableID from SetupVariables where SetupVariableDataSetID={0} and SetupVariableName='{1}' and GridDefinitionID={2} into :SetupVariableID;", variableDatasetID, variableName, gridDefinationID); progBarVariable.Value++; progBarVariable.Refresh(); lblProgressBar.Text = Convert.ToString((int)((double)progBarVariable.Value * 100 / progBarVariable.Maximum)) + "%"; lblProgressBar.Refresh(); } else { continue; } commandText = commandText + string.Format(" insert into SETUPGEOGRAPHICVARIABLES values (:SetupVariableID,{0},{1},{2});", dt.Rows[j * 125 + k][0], dt.Rows[j * 125 + k][1], dt.Rows[j * 125 + k][2]); } commandText = commandText + "END"; fbCommand.CommandText = commandText; fbCommand.ExecuteNonQuery(); } } } fbtra.Commit(); fbCommand.Connection.Close(); progBarVariable.Visible = false; lblProgressBar.Visible = false; this.DialogResult = DialogResult.OK; this.Close(); } catch (Exception ex) { MessageBox.Show("Failed to load variable dataset."); fbtra.Rollback(); progBarVariable.Value = 0; progBarVariable.Visible = false; lblProgressBar.Text = ""; lblProgressBar.Visible = false; Logger.LogError(ex.Message); } }
private void btnLoadData_Click(object sender, EventArgs e) { try { string tip = "Loading the datafile."; LoadVariableDatabase frm = new LoadVariableDatabase(); frm.DefinitionID = txtGridDefinition.Text; DialogResult rtn = frm.ShowDialog(); if (rtn != DialogResult.OK) { return; } txtGridDefinition.Text = frm.DefinitionID; txtGridDefinition.ReadOnly = false; string strPath = frm.DataPath; WaitShow(tip); _dtOrigin = CommonClass.ExcelToDataTable(strPath); int colCount = _dtOrigin.Columns.Count; if (_dtOrigin == null || colCount <= 2) { WaitClose(); MessageBox.Show("Failed to load variable dataset."); return; } int icol = -1; int irow = -1; for (int i = 0; i < _dtOrigin.Columns.Count; i++) { if (_dtOrigin.Columns[i].ColumnName.ToLower().Replace(" ", "") == "row") { irow = i; } if (_dtOrigin.Columns[i].ColumnName.ToLower().Replace(" ", "") == "col" || _dtOrigin.Columns[i].ColumnName.ToLower().Replace(" ", "") == "column") { icol = i; } } string warningtip = ""; if (irow < 0) { warningtip = "'Row', "; } if (icol < 0) { warningtip += "'Column', "; } if (warningtip != "") { warningtip = warningtip.Substring(0, warningtip.Length - 2); WaitClose(); warningtip = "Please check the column header of " + warningtip + ". It is incorrect or does not exist."; MessageBox.Show(warningtip, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } string header = string.Empty; DataColumn[] cols = new DataColumn[colCount]; _dtOrigin.Columns.CopyTo(cols, 0); for (int i = 0; i < colCount; i++) { if (i == icol || i == irow) { continue; } header = _dtOrigin.Columns[i].ColumnName; dt = new DataTable(header); dt = _dtOrigin.DefaultView.ToTable(false, _dtOrigin.Columns[icol].ColumnName, _dtOrigin.Columns[irow].ColumnName, header); dt.TableName = header; dt.Columns[0].ColumnName = "ccolumn"; dt.Columns[1].ColumnName = "row"; dt.Columns[2].ColumnName = "vvalue"; if (_dsSelectedData.Tables.Contains(dt.TableName)) { string commandText = string.Format("select SETUPVARIABLEID from SETUPVARIABLES where SETUPVARIABLENAME='{0}' and SETUPVARIABLEDATASETID='{1}'", dt.TableName, variabledatasetID); ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); object obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); WaitClose(); DialogResult dr = MessageBox.Show("The variable name '" + dt.TableName + "' is already in use. Please enter a different name. Click cancel to skip this variable.", "Error", MessageBoxButtons.OKCancel); if (dr == DialogResult.OK) { Renamefile frmRename = new Renamefile(); frmRename.lblRename.Text = "Please rename the variable:"; frmRename.newfileName = dt.TableName; frmRename.manage = "Variable"; frmRename.datasetID = Convert.ToInt16(variabledatasetID); if (frmRename.ShowDialog() == DialogResult.OK) { string newvariableName = frmRename.newfileName; dt.TableName = newvariableName; } else { continue; } } else { continue; } } WaitShow(tip); lstDataSetVariable.Items.Add(dt.TableName); _dsSelectedData.Tables.Add(dt); DataTable _dt = dt.Copy(); _dsSelectedDataTemp.Tables.Add(_dt); } lstDataSetVariable.SelectedIndex = 0; txtGridDefinition.Enabled = false; WaitClose(); } catch (Exception ex) { WaitClose(); MessageBox.Show("Failed to load variable dataset."); Logger.LogError(ex); } }
private void btnOK_Click(object sender, EventArgs e) { try { if (txtDatabase.Text == string.Empty) { MessageBox.Show("Please select a datafile."); return; } ESIL.DBUtility.FireBirdHelperBase fb = new ESIL.DBUtility.ESILFireBirdHelper(); string commandText = string.Format("select IncomeGrowthadjdatasetid from IncomeGrowthadjdatasets where setupid={0} and IncomeGrowthadjdatasetname='{1}'", CommonClass.ManageSetup.SetupID, txtDataSetName.Text); object obj = fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText); if (obj != null) { MessageBox.Show("This income growth dataset name is already in use. Please enter a different name."); return; } DataTable dt = new DataTable(); string strfilename = string.Empty; string strtablename = string.Empty; commandText = string.Empty; dt = CommonClass.ExcelToDataTable(txtDatabase.Text); int iYear = -1; int iMean = -1; int iEndpointGroup = -1; for (int i = 0; i < dt.Columns.Count; i++) { switch (dt.Columns[i].ColumnName.ToLower().Replace(" ", "")) { case "year": iYear = i; break; case "mean": iMean = i; break; case "endpointgroup": iEndpointGroup = i; break; } } string warningtip = ""; if (iYear < 0) { warningtip = "'Year', "; } if (iMean < 0) { warningtip += "'Mean', "; } if (iEndpointGroup < 0) { warningtip += "'Endpoint Group', "; } if (warningtip != "") { warningtip = warningtip.Substring(0, warningtip.Length - 2); warningtip = "Please check the column header of " + warningtip + ". It is incorrect or does not exist."; MessageBox.Show(warningtip, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } commandText = "SELECT max(INCOMEGROWTHADJDATASETID) from INCOMEGROWTHADJDATASETS"; int incomegrowthadjdatasetID = Convert.ToInt32(fb.ExecuteScalar(CommonClass.Connection, new CommandType(), commandText)) + 1; commandText = string.Format("insert into INCOMEGROWTHADJDATASETS VALUES({0},{1},'{2}' )", incomegrowthadjdatasetID, CommonClass.ManageSetup.SetupID, txtDataSetName.Text); fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); int currentDataSetID = incomegrowthadjdatasetID; if (dt == null) { return; } int rtn = 0; foreach (DataRow row in dt.Rows) { if (row == null) { continue; } commandText = string.Format("insert into INCOMEGROWTHADJFACTORS(INCOMEGROWTHADJDATASETID,YYEAR,MEAN,ENDPOINTGROUPS) values({0},{1},{2},'{3}')", currentDataSetID, int.Parse(row[iYear].ToString()), row[iMean], row[iEndpointGroup]); rtn = fb.ExecuteNonQuery(CommonClass.Connection, new CommandType(), commandText); } if (rtn != 0) { IncomeGrowthDataSetName = txtDataSetName.Text; } } catch (Exception ex) { Logger.LogError(ex); } this.DialogResult = DialogResult.OK; this.Close(); }