public void CreateTotalLaneMileYearsPerConditionReport()
{
Report.XL.Visible = false;
Report.XL.UserControl = false;
Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook();
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
string strReportName = "Lane Mile Years Per Condition";
object oEndCell = new object();
string strFilter = "";
int sheetRow;
int ndx;
DataSet dsPage = null, dsSimulations = null;
try
{
dsPage = DBOp.QueryPageHeader(strReportName);
dsSimulations = DBOp.QuerySimulations(m_strSimulationID);
}
catch (Exception e)
{
throw e;
// Circular reference, address later
//Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
}
if (m_useLaneMiles)
{
Report.SheetPageSetup(oSheet, "Total Lane-Mile Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
}
else
{
Report.SheetPageSetup(oSheet, "Total VML Years Report", 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
}
Range oR = oSheet.get_Range("A1", "A1");
Cursor c = Cursor.Current;
Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);
DataRow drPage = dsPage.Tables[0].Rows[0];
string strMajorTitle = drPage["phText"].ToString();
if (strMajorTitle.IndexOf("@1") > 0)
{
strMajorTitle = strMajorTitle.Replace("@1", m_strBudgetPer); // stuff the attribute into title
}
if (!m_useLaneMiles)
{
strMajorTitle = strMajorTitle.Replace("Lane-Miles", "Vehicle Lane-Miles");
}
#region default column widths
// set some column widths
oR = oSheet.get_Range("A1:A1", Missing.Value);
oR.ColumnWidth = 18;
#endregion
#region place agency graphic
//string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
#endregion
#region write Major Title
int aryCols = 2;
int aryRows = 3;
object[,] oData = new object[aryRows, aryCols];
Report.ClearDataArray(ref oData);
oData[0, 1] = strMajorTitle;
sheetRow = 4;
oEndCell = "A1";
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
#endregion
#region get Budget Years
strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + " ORDER BY Year_";
DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);
int numYears = dsBudgetYears.Tables[0].Rows.Count;
#endregion
int numConditions = 5, nTables = 2, rowSpacer = 2;
aryCols = numYears + 1;
aryRows = nTables * (numConditions + 3) + rowSpacer;
#region build Column Headers array
// Set up column header once, for multiple uses
object[] oColumnHeader = new object[aryCols];
oColumnHeader[0] = "Condition"; //m_strBudgetPer;
ndx = 1;
foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
{
oColumnHeader[ndx++] = dr["Year_"].ToString();
}
#endregion
#region build Condition List
// REVIST THIS REGION. BUILD LIST FROM THE DATABASE WHEN POSSIBLE xyzzy
List <string> listCondition = new List <string>();
listCondition.Add("Very Good");
listCondition.Add("Good");
listCondition.Add("Fair");
listCondition.Add("Mediocre");
listCondition.Add("Poor");
#endregion
#region build Budget and Percentage tables
Report.Resize2DArray(ref oData, aryRows, aryCols);
Report.ClearDataArray(ref oData);
int totalRow, nCol, budgetStartRowNdx, rowCounter = 0, acceptableRow;
Hashtable laneMilesPerCondition;
double nTmp, nTmp1;
ndx = 0;
oData[ndx, 0] = "Lane-miles per Condition Category"; // Group Header
sheetRow = 4;
totalRow = 2 + numConditions;
acceptableRow = oData.GetUpperBound(0) - 1;
oEndCell = "A" + sheetRow.ToString();
sheetRow++;
ndx++;
oData[totalRow, 0] = "Total";
for (int i = 0; i < aryCols; i++)
{
oData[ndx, i] = oColumnHeader[i]; // Column Header
}
sheetRow++;
ndx++;
budgetStartRowNdx = 2; // percentRowNdx is used to pull a value out of oData then use it to calculate the percent table
List <string> listAttributes;
string strCriteria;
string strLeftSide = "", strConnector = "", strRightSide = "";
double nBudget = 0d;
foreach (string s in listCondition)
{
oData[ndx, 0] = s;
nCol = 1;
foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
{
ConditionCriteria(s, ref strLeftSide, ref strConnector, ref strRightSide);
strCriteria = m_strBudgetPer + "_" + dr["Year_"].ToString() + strLeftSide;
if (strConnector != "")
{
strCriteria += strConnector + m_strBudgetPer + "_" + dr["Year_"].ToString() + strRightSide;
}
strFilter = " YEARS = " + dr["Year_"].ToString() + " AND " + strCriteria; //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString();
//strFilter = " YEARS = " + dr["Year"].ToString(); //"BUDGET IS NOT NULL AND YEARS = " + dr["Year"].ToString();
laneMilesPerCondition = DBOp.GetPercentagePerStringAttribute(m_strNetworkID, m_strSimulationID, m_strBudgetPer.ToUpper(), dr["Year_"].ToString(), "AREA", strFilter, "REMAINING_LIFE", m_useLaneMiles, out listAttributes);
nBudget = 0d;
foreach (DictionaryEntry de in laneMilesPerCondition)
{
string entry = (string)de.Value;
if (entry != "")
{
nBudget += Convert.ToDouble(entry);
}
}
//string strHash = (string)laneMilesPerCondition[s.ToUpper()];
//double nBudget = Convert.ToDouble(strHash);
if (oData[totalRow, nCol].ToString() == "")
{
oData[totalRow, nCol] = 0d; // initalize array element
}
nTmp = (double)oData[totalRow, nCol];
nTmp += nBudget;
oData[totalRow, nCol] = nTmp;
oData[ndx, nCol++] = nBudget;
}
ndx++;
sheetRow++;
}
sheetRow += 2;
ndx += 2;
if (m_useLaneMiles)
{
oData[ndx, 0] = "Lane-Mile Years Distribution (%) per Condition Category";
}
else
{
oData[ndx, 0] = "Vehicle Lane-Mile Years Distribution (%) per Condition Category";
}
ndx++;
sheetRow++;
for (int i = 0; i < aryCols; i++)
{
oData[ndx, i] = oColumnHeader[i]; // Column Header
}
sheetRow++;
ndx++;
rowCounter = 1;
foreach (string s in listCondition)
{
oData[ndx, 0] = s;
nCol = 1;
foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
{
nTmp = (double)oData[budgetStartRowNdx, nCol];
nTmp1 = (double)oData[totalRow, nCol];
double nPercent = nTmp / nTmp1;
oData[ndx, nCol] = nPercent;
if (rowCounter <= 3)
{ // sum the % Acceptable
if (oData[acceptableRow, nCol].ToString() == "")
{
oData[acceptableRow, nCol] = 0d; // initalize array element
}
nTmp = (double)oData[acceptableRow, nCol];
nTmp += nPercent;
oData[acceptableRow, nCol] = nTmp;
}
else
{ // sum the % Unacceptable
if (oData[acceptableRow + 1, nCol].ToString() == "")
{
oData[acceptableRow + 1, nCol] = 0d; // initalize array element
}
nTmp = (double)oData[acceptableRow + 1, nCol];
nTmp += nPercent;
oData[acceptableRow + 1, nCol] = nTmp;
}
nCol++;
}
ndx++;
sheetRow++;
budgetStartRowNdx++;
rowCounter++;
}
oData[acceptableRow, 0] = "%Acceptable";
sheetRow++;
oData[acceptableRow + 1, 0] = "%Unacceptable";
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
#endregion
#region format pageheader
// PAGEHEADER
string strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
DataRow drPgHdr = dsPage.Tables[0].Rows[0];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
#endregion
#region format groupheader
int startingRow = 4;
int offSet = startingRow + numConditions + 2 + rowSpacer;
strRange = "A4:" + Report.GetColumnLetter(aryCols) + "4";
Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
strRange = "A" + offSet.ToString() + ":" + Report.GetColumnLetter(aryCols) + offSet.ToString();
Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
#endregion
#region format columnHeader
strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5" + ", A" + (offSet + 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (offSet + 1).ToString();
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
#endregion
#region format totals row
strRange = "A" + (totalRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
strRange = "A" + (acceptableRow + 4).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 4).ToString();
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
strRange = "A" + (acceptableRow + 5).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + 5).ToString();
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
#endregion
#region format grid data
strRange = "A5:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
strRange += ",A" + (acceptableRow - numConditions + startingRow - 1).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oR.Borders.LineStyle = XlLineStyle.xlContinuous;
oR.Borders.Weight = XlBorderWeight.xlThin;
oR.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
oR.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;
oR.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous;
oR.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
oR.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
#endregion
#region format Lane Mile cells
strRange = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + 4).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.NumberFormat = "#,##0.0";
oR.ColumnWidth = 15;
#endregion
#region format Percent cells
strRange = "B" + (acceptableRow - numConditions + startingRow).ToString() + ":" + Report.GetColumnLetter(aryCols) + (acceptableRow + startingRow + 1).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.NumberFormat = "0.0%";
#endregion
#region create column charts
strRange = "B6:" + Report.GetColumnLetter(aryCols) + (totalRow + startingRow - 1).ToString();
Range oSourceData = oSheet.get_Range(strRange, Missing.Value);
int left = (int)Report.GetColumnWidthInPixels(oSourceData, oSheet);
if (m_useLaneMiles)
{
Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total Lane-Mile Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5",
"Annual Total Lane-Mile Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows);
}
else
{
Report.CreateColClusterBarGraph(left, 15, 425, 315, oSheet, oSourceData, "='Total VML Years Report'!$B$5:$" + Report.GetColumnLetter(aryCols) + "$5",
"Annual Total VLM Years Per Condition", 12, "Fiscal Year", 11, listCondition, "", 11, XlRowCol.xlRows);
}
#endregion
Report.XL.Visible = true;
Report.XL.UserControl = true;
}
public static void PasteInvestment(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyInvestmentSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
var deleteInvestments = "DELETE FROM INVESTMENTS WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteInvestments);
var deleteYearlyInvestment = "DELETE FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteYearlyInvestment);
var delete = "DELETE FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(delete);
var deleteSplitTreatment = "DELETE FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteSplitTreatment);
String strSelect = "SELECT * FROM INVESTMENTS WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
String strSimulationID;
String strFirstYear;
String strNumberYear;
String strInflation;
String strDiscount;
String strBudgetOrder;
String strDescription = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
strSimulationID = dr["SIMULATIONID"].ToString();
strFirstYear = dr["FIRSTYEAR"].ToString();
strNumberYear = dr["NUMBERYEARS"].ToString();
strInflation = dr["INFLATIONRATE"].ToString();
strDiscount = dr["DISCOUNTRATE"].ToString();
strBudgetOrder = dr["BUDGETORDER"].ToString();
if (dr["DESCRIPTION"] != DBNull.Value)
{
strDescription = dr["DESCRIPTION"].ToString();
}
String strInsert = "INSERT INTO INVESTMENTS (SIMULATIONID,FIRSTYEAR,NUMBERYEARS,INFLATIONRATE,DISCOUNTRATE,BUDGETORDER,DESCRIPTION) VALUES ('" + strNewSimulationID + "','" + strFirstYear + "','" + strNumberYear + "','" + strInflation + "','" + strDiscount + "','" + strBudgetOrder + "','" + strDescription + "')";
listInserts.Add(strInsert);
}
else
{
return;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing Investment Information." + exception.Message);
return;
}
strSelect = "SELECT * FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strYear = dr["YEAR_"].ToString();
String strBudget = dr["BUDGETNAME"].ToString();
String strAmount = dr["AMOUNT"].ToString();
String strInsert = "INSERT INTO YEARLYINVESTMENT (SIMULATIONID,YEAR_,BUDGETNAME,AMOUNT) VALUES (" + strNewSimulationID + "," + strYear + ",'" + strBudget + "'," + strAmount + ")";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
var selectBudgetCriteria = "SELECT * FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectBudgetCriteria);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var budgetName = dr["BUDGET_NAME"].ToString();
var criteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO BUDGET_CRITERIA (SIMULATIONID,BUDGET_NAME,CRITERIA) VALUES ('" + strNewSimulationID + "','" + budgetName + "','" + criteria + "')";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Investment and Budget Criteria successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Investments from one simulation to another" + exception.Message);
}
var selectSplitTreatment = "SELECT * FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectSplitTreatment);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var description = dr["DESCRIPTION"].ToString();
var criteria = dr["CRITERIA"].ToString();
var splitTreatmentIdToCopy = dr["SPLIT_TREATMENT_ID"].ToString();
String strInsert = "INSERT INTO SPLIT_TREATMENT(SIMULATIONID,DESCRIPTION,CRITERIA) VALUES ('" + strNewSimulationID + "','" + description + "','" + criteria + "')";
DBMgr.ExecuteNonQuery(strInsert);
//Get the identity
string selectIdentity = "SELECT IDENT_CURRENT ('SPLIT_TREATMENT') FROM SPLIT_TREATMENT";
var splitTreatmentIdToPaste = DBMgr.ExecuteScalar(selectIdentity).ToString();
var splitTreatmentLimit = "SELECT * FROM SPLIT_TREATMENT_LIMIT WHERE SPLIT_TREATMENT_ID=" + splitTreatmentIdToCopy;
DataSet dataSetLimit = DBMgr.ExecuteQuery(splitTreatmentLimit);
foreach (DataRow limit in dataSetLimit.Tables[0].Rows)
{
int rank = -1;
float amount = -1;
string percentage = "";
if (limit["RANK"] != DBNull.Value)
{
rank = Convert.ToInt32(limit["RANK"]);
}
if (limit["AMOUNT"] != DBNull.Value)
{
amount = Convert.ToSingle(limit["AMOUNT"]);
}
if (limit["PERCENTAGE"] != DBNull.Value)
{
percentage = limit["PERCENTAGE"].ToString();
}
String limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "')";
if (amount > 0)
{
limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE,AMOUNT) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "','" + amount + "')";
}
DBMgr.ExecuteNonQuery(limitInsert);
}
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing split treatment information." + exception.Message);
return;
}
}
public static void PasteAnalysis(String strNewSimulation)
{
if (String.IsNullOrEmpty(m_strCopyAnalysisSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
String strSelect = "SELECT * FROM SIMULATIONS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count != 1)
{
return;
}
DataRow dr = ds.Tables[0].Rows[0];
String strComments = dr["COMMENTS"].ToString();
String strDateCreated = dr["DATE_CREATED"].ToString();
String strDateLastRun = dr["DATE_LAST_RUN"].ToString();
String strCreatorID = dr["CREATOR_ID"].ToString();
String strUserName = dr["USERNAME"].ToString();
String strPermission = dr["PERMISSIONS"].ToString();
String strJurisdiction = dr["JURISDICTION"].ToString();
String strAnalysis = dr["ANALYSIS"].ToString();
String strBudgetConstraint = dr["BUDGET_CONSTRAINT"].ToString();
String strWeighting = dr["WEIGHTING"].ToString();
String strBenefitVariable = dr["BENEFIT_VARIABLE"].ToString();
String strBenefitLimit = dr["BENEFIT_LIMIT"].ToString();
String strCommittedStart = dr["COMMITTED_START"].ToString();
String strCommittedPeriod = dr["COMMITTED_PERIOD"].ToString();
String strSimulationVariable = dr["SIMULATION_VARIABLES"].ToString();
String strUpdate = "UPDATE SIMULATIONS SET ";
if (strComments != "")
{
strUpdate += "COMMENTS" + "='" + strComments + "',";
}
if (strDateCreated != "")
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strUpdate += "DATE_CREATED" + "='" + strDateCreated + "',";
break;
case "ORACLE":
strUpdate += "DATE_CREATED=to_date('" + DateTime.Parse(strDateCreated).ToString("MM/dd/yyyy") + "','MM/DD/YYYY'),";
break;
default:
throw new NotImplementedException("TODO: Create ANSI version of PasteAnalysis()");
//break;
}
}
if (strDateLastRun != "")
{
strUpdate += "DATE_LAST_RUN" + "='" + strDateLastRun + "',";
}
if (strCreatorID != "")
{
strUpdate += "CREATOR_ID" + "='" + strCreatorID + "',";
}
if (strUserName != "")
{
strUpdate += "USERNAME" + "='" + strUserName + "',";
}
if (strPermission != "")
{
strUpdate += "PERMISSIONS" + "='" + strPermission + "',";
}
if (strJurisdiction != "")
{
strUpdate += "JURISDICTION" + "='" + strJurisdiction + "',";
}
if (strAnalysis != "")
{
strUpdate += "ANALYSIS" + "='" + strAnalysis + "',";
}
if (strBudgetConstraint != "")
{
strUpdate += "BUDGET_CONSTRAINT" + "='" + strBudgetConstraint + "',";
}
if (strWeighting != "")
{
strUpdate += "WEIGHTING" + "='" + strWeighting + "',";
}
if (strBenefitVariable != "")
{
strUpdate += "BENEFIT_VARIABLE" + "='" + strBenefitVariable + "',";
}
if (strBenefitLimit != "")
{
strUpdate += "BENEFIT_LIMIT" + "='" + strBenefitLimit + "',";
}
if (strBenefitLimit != "")
{
strUpdate += "COMMITTED_START" + "='" + strCommittedStart + "',";
}
if (strCommittedPeriod != "")
{
strUpdate += "COMMITTED_PERIOD" + "='" + strCommittedPeriod + "',";
}
if (strSimulationVariable != "")
{
strUpdate += "SIMULATION_VARIABLES" + "='" + strSimulationVariable + "',";
}
if (strUpdate.Substring(strUpdate.Length - 1) == ",")
{
strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);
strUpdate += " WHERE SIMULATIONID=" + strNewSimulation;
listInserts.Add(strUpdate);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying ANALYSIS information." + exception.Message);
return;
}
//Get TARGETS
String strDelete = "DELETE FROM TARGETS WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
strSelect = "SELECT * FROM TARGETS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strYears = dr["YEARS"].ToString();
String strTargetMean = dr["TARGETMEAN"].ToString();
String strTargetName = dr["TARGETNAME"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO TARGETS (SIMULATIONID,ATTRIBUTE_,";
String strValue = "VALUES(" + strNewSimulation + ",'" + strAttribute + "',";
if (!String.IsNullOrEmpty(strYears))
{
strInsert += "YEARS,";
strValue += "'" + strYears + "',";
}
if (!String.IsNullOrEmpty(strTargetMean))
{
strInsert += "TARGETMEAN,";
strValue += "'" + strTargetMean + "',";
}
if (!String.IsNullOrEmpty(strTargetName))
{
strInsert += "TARGETNAME,";
strValue += "'" + strTargetName + "',";
}
if (!String.IsNullOrEmpty(strCriteria))
{
strInsert += "CRITERIA,";
strValue += "'" + strCriteria + "',";
}
strInsert = strInsert.Substring(0, strInsert.Length - 1) + ") ";
strValue = strValue.Substring(0, strValue.Length - 1) + ")";
strInsert += strValue;
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Pasting TARGETS. Operation aborted." + exception.Message);
return;
}
//Get DEFICIENT
strDelete = "DELETE FROM DEFICIENTS WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
strSelect = "SELECT * FROM DEFICIENTS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strDeficientName = dr["DEFICIENTNAME"].ToString();
String strDeficient = dr["DEFICIENT"].ToString();
String strPercentDeficient = dr["PERCENTDEFICIENT"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO DEFICIENTS (SIMULATIONID,ATTRIBUTE_,";
String strValue = "VALUES(" + strNewSimulation + ",'" + strAttribute + "',";
if (!String.IsNullOrEmpty(strDeficientName))
{
strInsert += "DEFICIENTNAME,";
strValue += "'" + strDeficientName + "',";
}
if (!String.IsNullOrEmpty(strDeficient))
{
strInsert += "DEFICIENT,";
strValue += "'" + strDeficient + "',";
}
if (!String.IsNullOrEmpty(strPercentDeficient))
{
strInsert += "PERCENTDEFICIENT,";
strValue += "'" + strPercentDeficient + "',";
}
if (!String.IsNullOrEmpty(strCriteria))
{
strInsert += "CRITERIA,";
strValue += "'" + strCriteria + "',";
}
strInsert = strInsert.Substring(0, strInsert.Length - 1) + ") ";
strValue = strValue.Substring(0, strValue.Length - 1) + ")";
strInsert += strValue;
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Pasting DEFICIENT. Operation aborted." + exception.Message);
return;
}
strDelete = "DELETE FROM PRIORITY WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
//Run as much of this that can be batch in one try.
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying ANALYSIS. Operation aborted." + exception.Message);
}
//Now get PRIORITY and PRIORITYFUND
strSelect = "SELECT * FROM PRIORITY WHERE SIMULATIONID=" + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strPriorityID = dr["PRIORITYID"].ToString();
String strPriorityLevel = dr["PRIORITYLEVEL"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
string years = null;
if (dr["YEARS"] != DBNull.Value)
{
years = dr["YEARS"].ToString();
}
String strInsert;
if (years == null)
{
if (String.IsNullOrEmpty(strCriteria))
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL) VALUES(" + strNewSimulation + "," + strPriorityLevel + ")";
}
else
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,CRITERIA) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + strCriteria + "')";
}
}
else
{
if (String.IsNullOrEmpty(strCriteria))
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,YEARS) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + years + "')";
}
else
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,CRITERIA,YEARS) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + strCriteria + "','" + years + "')";
}
}
DBMgr.ExecuteNonQuery(strInsert);
String strIdentity;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('PRIORITY') FROM PRIORITY";
break;
case "ORACLE":
//strIdentity = "SELECT PRIORITY_PRIORITYID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT SELECT LAST_NUMBER - CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'PRIORITY_PRIORITYID_SEQ'";
strIdentity = "SELECT MAX(PRIORITYID) FROM PRIORITY";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet dsIdentity = DBMgr.ExecuteQuery(strIdentity);
strIdentity = dsIdentity.Tables[0].Rows[0].ItemArray[0].ToString();
strSelect = "SELECT * FROM PRIORITYFUND WHERE PRIORITYID =" + strPriorityID;
DataSet dsPriorityFund = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow drPriorityFund in dsPriorityFund.Tables[0].Rows)
{
String strBudget = drPriorityFund["BUDGET"].ToString();
String strFunding = drPriorityFund["FUNDING"].ToString();
strInsert = "INSERT INTO PRIORITYFUND (PRIORITYID,BUDGET,FUNDING) VALUES(" + strIdentity + ",'" + strBudget + "'," + strFunding + ")";
DBMgr.ExecuteNonQuery(strInsert);
}
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Failure in copying Priority Levels and Funding. Rest of Analysis copied correctly." + exception.Message);
}
try
{
var delete = "DELETE FROM REMAINING_LIFE_LIMITS WHERE SIMULATION_ID='" + strNewSimulation + "'";
DBMgr.ExecuteNonQuery(delete);
}
catch (Exception e)
{
Global.WriteOutput("Error: Failure in copying Remaining Life Limits. " + e.Message);
}
try
{
DataSet datasetRemainingLife =
DBMgr.ExecuteQuery("SELECT * FROM REMAINING_LIFE_LIMITS WHERE SIMULATION_ID ='" +
m_strCopyAnalysisSimulationID + "'");
foreach (DataRow dr in datasetRemainingLife.Tables[0].Rows)
{
var remainingLifeLimit = dr["REMAINING_LIFE_LIMIT"].ToString();
var attribute = dr["ATTRIBUTE_"].ToString();
var criteria = "";
if (dr["CRITERIA"] != DBNull.Value)
{
criteria = dr["CRITERIA"].ToString();
}
var insert =
"INSERT INTO REMAINING_LIFE_LIMITS (SIMULATION_ID,ATTRIBUTE_,REMAINING_LIFE_LIMIT,CRITERIA) VALUES ('"
+ strNewSimulation + "','" + attribute + "','" + remainingLifeLimit + "','" +
criteria + "')";
DBMgr.ExecuteNonQuery(insert);
}
}
catch (Exception e)
{
Global.WriteOutput("Error: Failure in copying Remaining Life Limits. " + e.Message);
}
}
public void CreateDetailedResultsReport()
{
Report.XL.Visible = false;
Report.XL.UserControl = false;
Microsoft.Office.Interop.Excel._Workbook oWB = Report.CreateWorkBook();
Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
string strReportName = "Detailed Results Report";
Report.SheetPageSetup(oSheet, strReportName, 50d, 20d, 10d, m_strNetwork + " - " + m_strSimulation, DateTime.Now.ToLongDateString(), "Page &P", 1);
//oSheet.PageSetup.RightFooter = m_strNetwork + " - " + m_strSimulation;
//oSheet.PageSetup.LeftFooter = DateTime.Now.ToLongDateString();
//oSheet.PageSetup.CenterFooter = "Page &P";
//oSheet.PageSetup.FirstPageNumber = 1;
//oSheet.PageSetup.LeftMargin = 50d;
//oSheet.PageSetup.RightMargin = 20d;
//oSheet.Columns.Font.Size = 10;
//oSheet.Name = strReportName;
Range oR = oSheet.get_Range("A1", "A1");
object oEndCell = new object();
string strFilter = "";
int sheetRow;
int ndx;
DataSet dsPage = null, dsResults = null;
try
{
dsPage = DBOp.QueryPageHeader(strReportName);
dsResults = DBOp.QueryReport(m_strNetworkID, m_strSimulationID);
}
catch (Exception e)
{
throw e;
// Circular reference, address later
//Global.WriteOutput("Error: Could not fill dataset in CreateBudgetPEr. " + e.Message);
}
Cursor c = Cursor.Current;
Cursor.Current = new Cursor(Cursors.WaitCursor.Handle);
DataRow drPage = dsPage.Tables[0].Rows[0];
string strMajorTitle = drPage["phText"].ToString();
drPage = dsPage.Tables[0].Rows[2];
string strMinorTitle = drPage["phText"].ToString();
if (strMinorTitle.IndexOf("@1") > 0)
{
strMinorTitle = strMinorTitle.Replace("@1", m_strNetwork + " (" + m_strNetworkID + ")");
}
if (strMinorTitle.IndexOf("@2") > 0)
{
strMinorTitle = strMinorTitle.Replace("@2", m_strSimulation + " (" + m_strSimulationID + ")");
}
#region default column widths
// set some column widths
oR = oSheet.get_Range("A1:B1", Missing.Value);
oR.ColumnWidth = 15;
#endregion
#region place agency graphic
//string strPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\RoadCare Projects\\" + drPage["reportGraphicFile"].ToString();
string strPath = ".\\" + drPage["reportGraphicFile"].ToString();
Report.PlaceReportGraphic(strPath, oSheet.get_Range("A1", Missing.Value), oSheet);
#endregion
#region write Major Title
int aryCols = 2;
int aryRows = 3;
object[,] oData = new object[aryRows, aryCols];
Report.ClearDataArray(ref oData);
oData[0, 1] = strMajorTitle;
oData[2, 1] = strMinorTitle;
sheetRow = 4;
oEndCell = "A1";
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, false);
#endregion
#region get Budget Years
strFilter = "SELECT DISTINCT Year_ FROM YearlyInvestment WHERE simulationID = " + m_strSimulationID + "ORDER BY Year_";
DataSet dsBudgetYears = DBMgr.ExecuteQuery(strFilter);
int numYears = dsBudgetYears.Tables[0].Rows.Count;
#endregion
aryCols = numYears + 2;
//aryRows = dsResults.Tables[0].Rows.Count / 5 + 2;
aryRows = dsResults.Tables[0].Rows.Count / numYears + 2;
#region build Column Headers array
Report.Resize2DArray(ref oData, aryRows + 1, aryCols);
Report.ClearDataArray(ref oData);
object[] oColumnHeader = new object[aryCols];
oColumnHeader[0] = "Facility";
oColumnHeader[1] = "Section";
ndx = 2;
foreach (DataRow dr in dsBudgetYears.Tables[0].Rows)
{
oColumnHeader[ndx++] = dr["Year_"].ToString();
}
#endregion
#region build Results table
ndx = 0;
oData[ndx, 0] = "Treatment Distribution per Section"; // Group Header
sheetRow = 5;
oEndCell = "A" + sheetRow.ToString();
sheetRow++;
ndx++;
for (int i = 0; i < aryCols; i++)
{
oData[ndx, i] = oColumnHeader[i]; // Column Header
}
sheetRow++;
ndx++;
int rowsToColumns = 0, nCol = 2;
string strGreen = "", strBlue = "", strRed = "";
List <string> greenList = new List <string>();
List <string> blueList = new List <string>();
List <string> redList = new List <string>();
foreach (DataRow dr in dsResults.Tables[0].Rows)
{
string sYear = dr["Years"].ToString();
if (rowsToColumns < numYears)
{
if (rowsToColumns == 0)
{
nCol = 2;
if (ndx == 132)
{
string testString = dr["Facility"].ToString();
}
oData[ndx, 0] = dr["Facility"].ToString();
oData[ndx, 1] = dr["Section"].ToString();
}
}
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
// Test for Treatment selected per RoadCare logic (GREEN)
if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
// Test for Feasible treatment not funded/selected (BLUE)
else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString().ToUpper() != "TRUE" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
}
// Test for Treatment selected as Committed Project (RED)
else if (dr["Iscommitted"].ToString().ToUpper() == "TRUE")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
break;
case "ORACLE":
// Test for Treatment selected per RoadCare logic (GREEN)
if (dr["Treatment"].ToString() != "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strGreen, ref greenList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
// Test for Feasible treatment not funded/selected (BLUE)
else if (dr["Treatment"].ToString() == "No Treatment" && dr["Iscommitted"].ToString() != "1" && dr["Numbertreatment"].ToString() != "0")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strBlue, ref blueList, sheetRow, asciiChar, 0);
}
// Test for Treatment selected as Committed Project (RED)
else if (dr["Iscommitted"].ToString() == "1")
{
string s = Report.GetColumnLetter(nCol + 1);
char[] chr = s.ToCharArray(0, 1);
char asciiChar = chr[0];
Report.BuildRange(ref strRed, ref redList, sheetRow, asciiChar, 0);
oData[ndx, nCol] = dr["Treatment"].ToString();
}
break;
default:
throw new NotImplementedException("TODO: develop ANSI version of CreateDetailedResultsReport()");
//break;
}
nCol++;
if (rowsToColumns + 1 >= numYears)
{
rowsToColumns = 0;
ndx++;
sheetRow++;
}
else
{
rowsToColumns++;
}
}
oEndCell = Report.WriteObjectArrayToExcel(oData, oSheet, oEndCell, false, true);
#endregion
#region format pageheader
// PAGEHEADER
string strRange = "B1:" + Report.GetColumnLetter(aryCols) + "1";
DataRow drPgHdr = dsPage.Tables[0].Rows[0];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
strRange = "B2:" + Report.GetColumnLetter(aryCols) + "2";
drPgHdr = dsPage.Tables[0].Rows[1];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
strRange = "B3:" + Report.GetColumnLetter(aryCols) + "3";
drPgHdr = dsPage.Tables[0].Rows[2];
Report.FormatHeaders(oR, drPgHdr, oSheet, "ph", strRange);
// Place a color legend at top and bottom of report
PlaceColorLegend(oSheet, aryCols, 3);
PlaceColorLegend(oSheet, 1, sheetRow + 1);
#endregion
#region format groupheader
strRange = "A5:" + Report.GetColumnLetter(aryCols) + "5";
Report.FormatHeaders(oR, drPage, oSheet, "gh", strRange);
#endregion
#region format columnHeader
strRange = "A6:" + Report.GetColumnLetter(aryCols) + "6";
Report.FormatHeaders(oR, drPage, oSheet, "ch", strRange);
#endregion
#region apply color
Report.EndRangeList(ref strGreen, ref greenList);
Report.EndRangeList(ref strBlue, ref blueList);
Report.EndRangeList(ref strRed, ref redList);
foreach (string s in greenList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen);
}
foreach (string s in blueList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
}
foreach (string s in redList)
{
oR = oSheet.get_Range(s, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon);
}
#endregion
#region default column widths
// set some column widths
oR = oSheet.get_Range("A1:B1", Missing.Value);
oR.ColumnWidth = 15;
strRange = "C1:" + Report.GetColumnLetter(aryCols) + "1";
oR = oSheet.get_Range(strRange, Missing.Value);
oR.ColumnWidth = 12;
#endregion
#region make bold
strRange = "A7:B" + (sheetRow - 1).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.Font.Bold = true;
#endregion
Report.XL.Visible = true;
Report.XL.UserControl = true;
}
private static void ORACLECopyConsequences(string identity, DataSet conSet)
{
foreach (DataRow conRow in conSet.Tables[0].Rows)
{
string criteriaFormula = conRow["CRITERIA"].ToString();
string attribute = conRow["ATTRIBUTE_"].ToString();
string change = conRow["CHANGE_"].ToString();
string strEquation = conRow["EQUATION"].ToString();
string isFunction = "0";
Boolean bIsFunction = false;
if (conRow["ISFUNCTION"] != DBNull.Value)
{
bIsFunction = Convert.ToBoolean(conRow["ISFUNCTION"]);
}
if (bIsFunction)
{
isFunction = "1";
}
string valueClause = "VALUES(" + identity;
string fieldClause = "(TREATMENTID";
if (attribute != "")
{
valueClause += ",'" + attribute + "'";
fieldClause += ",ATTRIBUTE_";
}
if (change != "")
{
valueClause += ",'" + change + "'";
fieldClause += ",CHANGE_";
}
if (!string.IsNullOrWhiteSpace(strEquation))
{
valueClause += ",'" + strEquation + "'";
fieldClause += ",EQUATION";
}
valueClause += ",'" + isFunction + "'";
fieldClause += ",ISFUNCTION";
valueClause += ",'" + criteriaFormula + "'";
fieldClause += ",CRITERIA";
valueClause += ")";
fieldClause += ")";
string insertStatement = "INSERT INTO CONSEQUENCES " + fieldClause + valueClause;
try
{
DBMgr.ExecuteNonQuery(insertStatement);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// Updates summary when necessary
/// </summary>
private void UpdateSummaryValues()
{
if (!m_bChange)
{
return;
}
labelError.Visible = false;
String strTreatment = dgvSummary[1, 1].Value.ToString();
if (strTreatment == m_strCurrentTreatment)
{
dgvSummary[1, 2].Value = m_strCurrentBudget;
dgvSummary[1, 3].Value = m_strCurrentCost;
dgvSummary[1, 4].Value = m_strCurrentAny;
dgvSummary[1, 5].Value = m_strCurrentSame;
dgvAttribute.Rows.Clear();
foreach (String key in m_hashCurrentChange.Keys)
{
int nRow = dgvAttribute.Rows.Add(key, m_hashCurrentChange[key]);
}
}
else if (m_listFeasibleTreatment.Contains(strTreatment))
{
double dArea = 0;
String strSelect = "SELECT AREA FROM " + this.ReportTable + " WHERE SECTIONID ='" + this.SectionID + "' AND YEARS='" + this.Year + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
dArea = double.Parse(dr["AREA"].ToString());
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error retrieving AREA." + exception.Message;
}
strSelect = "SELECT BUDGET,YEARSANY,YEARSSAME,COST_,CHANGEHASH FROM " + this.BenefitCostTable + " WHERE SECTIONID ='" + this.SectionID + "' AND YEARS='" + this.Year + "' AND TREATMENT='" + strTreatment + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
double dCost = double.Parse(dr["COST_"].ToString()) * dArea * inflationRate;
string budget = dr["BUDGET"].ToString();
if (!budget.Contains("|"))
{
dgvSummary[1, 2].Value = budget;
}
dgvSummary[1, 3].Value = Math.Round(dCost, 2).ToString("c");
dgvSummary[1, 4].Value = dr["YEARSANY"].ToString();
dgvSummary[1, 5].Value = dr["YEARSSAME"].ToString();
String strChangeHash = dr["CHANGEHASH"].ToString();
dgvAttribute.Rows.Clear();
string[] pairs = strChangeHash.Split(new string[] { "\n" }, StringSplitOptions.None);
for (int i = 0; i < pairs.Length; i++)
{
if (pairs[i].Contains("\t"))
{
string[] attributechange = pairs[i].Split(new string[] { "\t" }, StringSplitOptions.None);
dgvAttribute.Rows.Add(attributechange[0], attributechange[1]);
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default information from the BENEFIT_COST table." + exception.Message;
}
}
else if (m_listTreatments.Contains(strTreatment))
{
//Fill with default from Treatment table
String strSelect = "SELECT BUDGET, BEFOREANY, BEFORESAME, TREATMENTID FROM TREATMENTS WHERE SIMULATIONID ='" + this.SimulationID + "' AND TREATMENT='" + strTreatment + "'";
String strTreatmentID = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
dgvSummary[1, 2].Value = dr["BUDGET"].ToString();
dgvSummary[1, 4].Value = dr["BEFOREANY"].ToString();
dgvSummary[1, 5].Value = dr["BEFORESAME"].ToString();
strTreatmentID = dr["TREATMENTID"].ToString();
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default information from the TREATMENTS table." + exception.Message;
}
//Get Cost information
String strCost = "0";
//strSelect = "SELECT COST_ FROM COSTS WHERE TREATMENTID='" + strTreatmentID + "' AND CRITERIA=''";
//inserting '' in oracle inserts a null
// TODO: Why not pull the criteria for each cost equation found, and calculate the cost for each and show them in a cost dropdown.
strSelect = "SELECT COST_,CRITERIA FROM COSTS WHERE TREATMENTID='" + strTreatmentID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
var cost = dr["COST_"];
var criteria = dr["CRITERIA"];
if (criteria == DBNull.Value)
{
strCost = dr["COST_"].ToString();
}
else
{
var criteriaString = criteria.ToString();
if (String.IsNullOrWhiteSpace(criteriaString))
{
strCost = dr["COST_"].ToString();
}
else
{
var criteriaToEvaluate = new Criterias();
criteriaToEvaluate.Criteria = criteriaString;
if (criteriaToEvaluate.IsCriteriaMet(m_hashAttributeValue))
{
strCost = dr["COST_"].ToString();
}
}
}
strCost = dr["COST_"].ToString();
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Retrieving default COST information from the COSTS table." + exception.Message;
}
List <String> listError;
List <String> listAttributesEquation = Global.TryParseAttribute(strCost, out listError); // See if listAttributeEquations is included in dgvDefault
CalculateEvaluate.CalculateEvaluate calculate = new CalculateEvaluate.CalculateEvaluate();
calculate.BuildTemporaryClass(strCost, true);
CompilerResults m_crEquation = calculate.CompileAssembly();
object[] input = new object[listAttributesEquation.Count];
int i = 0;
foreach (String attribute in listAttributesEquation)
{
input[i] = m_hashAttributeValue[attribute];
i++;
}
try
{
object result = calculate.RunMethod(input);
dgvSummary[1, 3].Value = result.ToString();
}
catch (Exception exc)
{
Global.WriteOutput("Error running single section. " + exc.Message);
}
//strSelect = "SELECT ATTRIBUTE_,CHANGE_ FROM CONSEQUENCES WHERE TREATMENTID='" + strTreatmentID + "' AND CRITERIA=''";
//inserting '' in oracle inserts a null
strSelect = "SELECT ATTRIBUTE_,CHANGE_, CRITERIA FROM CONSEQUENCES WHERE TREATMENTID='" + strTreatmentID + "'";
dgvAttribute.Rows.Clear();
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var attribute = dr["ATTRIBUTE_"];
var change = dr["CHANGE_"];
var criteria = dr["CRITERIA"];
if (criteria == DBNull.Value)
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
else
{
var criteriaString = criteria.ToString();
if (String.IsNullOrWhiteSpace(criteriaString))
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
else
{
var criteriaToEvaluate = new Criterias();
criteriaToEvaluate.Criteria = criteriaString;
if (criteriaToEvaluate.IsCriteriaMet(m_hashAttributeValue))
{
dgvAttribute.Rows.Add(dr["ATTRIBUTE_"].ToString(), dr["CHANGE_"].ToString());
}
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading Consequences for selected TREATMENT. " + exception.Message;
}
}
else
{
//User defined treatment. Leave existing as is.
}
dgvSummary.Update();
dgvAttribute.Update();
}
public FormSpecialReportConfig()
{
InitializeComponent();
Nets = DBMgr
.ExecuteQuery(
"SELECT networkid, network_name" +
" FROM networks ORDER BY network_name")
.Tables[0]
.AsEnumerable()
.Select(dr => new Net
{
Id = dr[0].ToString(),
Name = dr[1].ToString()
})
.ToArray();
Sims = DBMgr
.ExecuteQuery(
"SELECT simulationid, simulation, networkid" +
" FROM simulations ORDER BY simulation")
.Tables[0]
.AsEnumerable()
.Select(dr => new Sim
{
Id = dr[0].ToString(),
Name = dr[1].ToString(),
Net = dr[2].ToString()
})
.ToArray();
NetRpts = DBOp.GetNetworkReportNames().ToArray();
SimRpts =
DBOp.GetSimulationReportNames().Concat(new[]
{
Cis.Report.GenericTitle,
Fytsp.Report.GenericTitle,
Ldvc.Report.GenericTitle,
Blfy.Report.GenericTitle,
Blfc.Report.GenericTitle,
Icf.Report.GenericTitle,
AllSec.Report.GenericTitle,
BenefitCostRatioReport.GenericTitle,
})
.ToArray();
Array.Sort(NetRpts);
Array.Sort(SimRpts);
cbNets.DisplayMember = "Name";
cbSims.DisplayMember = "Name";
if (Nets.Length != 0)
{
cbNets.Items.AddRange(Nets);
cbNets.SelectedIndex = 0;
}
else
{
cbNets.SelectedIndex = -1;
}
fbdSimpleGen.Description =
"Select the folder in which you would like the report file" +
" to be generated.";
Timer = new Stopwatch();
}
//非国内订单订单编辑页在首次加载或者修改时封装的方法 by panhuaguo 20160729 国内订单要复杂很多，单独放在其自身控制器里面
public string loadorder()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string ordercode = Request["ordercode"];
string copyordercode = Request["copyordercode"];
DataTable dt;
string bgsb_unit = "";
string bjsb_unit = "";
string result = "{}";
if (string.IsNullOrEmpty(ordercode)) //如果订单号为空、即新增的时候
{
string sql = "select * from base_company where CODE='" + json_user.Value <string>("CUSTOMERHSCODE") + "' AND ENABLED=1 AND ROWNUM=1"; //根据海关的10位编码查询申报单位
dt = DBMgr.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
bgsb_unit = dt.Rows[0]["NAME"] + "";
}
sql = "select * from base_company where INSPCODE='" + json_user.Value <string>("CUSTOMERCIQCODE") + "' AND ENABLED=1 AND ROWNUM=1";//根据海关的10位编码查询申报单位
dt = DBMgr.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
bjsb_unit = dt.Rows[0]["NAME"] + "";
}
if (string.IsNullOrEmpty(copyordercode))//如果不是复制新增
{
string formdata = "{STATUS:1,REPUNITNAME:'" + bgsb_unit + "',REPUNITCODE:'" + json_user.Value <string>("CUSTOMERHSCODE") + "',INSPUNITNAME:'" + bjsb_unit + "',INSPUNITCODE:'" + json_user.Value <string>("CUSTOMERCIQCODE") + "'}";
result = "{formdata:" + formdata + ",filedata:[]}";
}
else//如果是复制新增
{
sql = @"select t.*,'' CONTAINERTRUCK from LIST_ORDER t where t.CODE = '" + copyordercode + "' and rownum=1";
dt = DBMgr.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
dt.Rows[0]["CODE"] = DBNull.Value; dt.Rows[0]["STATUS"] = "1";
dt.Rows[0]["CREATEUSERID"] = DBNull.Value; dt.Rows[0]["CREATEUSERNAME"] = DBNull.Value;
dt.Rows[0]["SUBMITTIME"] = DBNull.Value; dt.Rows[0]["CREATETIME"] = DBNull.Value;
dt.Rows[0]["SUBMITUSERNAME"] = DBNull.Value; dt.Rows[0]["SUBMITUSERID"] = DBNull.Value;
dt.Rows[0]["SUBMITUSERPHONE"] = DBNull.Value; dt.Rows[0]["CSNAME"] = DBNull.Value;//平台客户字段需要清空
dt.Rows[0]["CONTAINERNO"] = DBNull.Value; dt.Rows[0]["DECLCARNO"] = DBNull.Value;
//报关、报检申报单位
dt.Rows[0]["REPUNITNAME"] = bgsb_unit; dt.Rows[0]["REPUNITCODE"] = json_user.Value <string>("CUSTOMERHSCODE");
dt.Rows[0]["INSPUNITNAME"] = bjsb_unit; dt.Rows[0]["INSPUNITCODE"] = json_user.Value <string>("CUSTOMERCIQCODE");
//件数和重量也要清空
dt.Rows[0]["GOODSNUM"] = DBNull.Value; dt.Rows[0]["PACKKIND"] = DBNull.Value;
dt.Rows[0]["GOODSGW"] = DBNull.Value; dt.Rows[0]["GOODSNW"] = DBNull.Value;
string formdata = JsonConvert.SerializeObject(dt).TrimStart('[').TrimEnd(']');
result = "{formdata:" + formdata + ",filedata:[]}";
}
}
}
else //如果订单号不为空
{
//订单基本信息 CONTAINERTRUCK 这个字段本身不属于list_order表,虚拟出来存储集装箱和报关车号记录,是个数组形式的字符串
string sql = @"select t.*,'' CONTAINERTRUCK from LIST_ORDER t where t.CODE = '" + Request["ordercode"] + "' and rownum=1";
dt = DBMgr.GetDataTable(sql);
IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式
iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
sql = "select * from list_predeclcontainer t where t.ordercode='" + dt.Rows[0]["CODE"] + "' order by containerorder";
DataTable dt_container = DBMgr.GetDataTable(sql);
dt.Rows[0]["CONTAINERTRUCK"] = JsonConvert.SerializeObject(dt_container);
string formdata = JsonConvert.SerializeObject(dt, iso).TrimStart('[').TrimEnd(']');
//订单随附文件
sql = @"select * from LIST_ATTACHMENT where instr(ordercode,'{0}') >0
and ((filetype=44 or filetype=58) or ( filetype=57 AND confirmstatus = 1 )) and (abolishstatus is null or abolishstatus=0)";
sql = string.Format(sql, ordercode);
dt = DBMgr.GetDataTable(sql);
string filedata = JsonConvert.SerializeObject(dt, iso);
result = "{formdata:" + formdata + ",filedata:" + filedata + "}";
}
return(result);
}
/*报关单管理 列表页展示*/
public string LoadDeclarationList()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string where = "";
string role = Request["role"];
string busitypeid = Request["busitypeid"];
if (!string.IsNullOrEmpty(Request["VALUE1"]))//判断查询条件1是否有值
{
switch (Request["CONDITION1"])
{
case "BUSIUNITCODE": //经营单位
where += " and ort.BUSISHORTCODE='" + Request["VALUE1"] + "' ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE2"]))//判断查询条件2是否有值
{
switch (Request["CONDITION2"])
{
case "CUSNO": //客户编号
where += " and instr(ort.CUSNO,'" + Request["VALUE2"] + "')>0 ";
break;
case "BLNO": //提运单号
where += " and instr(prt.BLNO,'" + Request["VALUE2"] + "')>0 ";
break;
case "ORDERCODE": //订单编号
where += " and instr(det.ORDERCODE,'" + Request["VALUE2"] + "')>0 ";
break;
case "DECLCARNO": //报关车号
where += " and instr(ort.DECLCARNO,'" + Request["VALUE2"] + "')>0 ";
break;
case "TRANSNAME": //运输工具名称
where += " and instr(prt.TRANSNAME,'" + Request["VALUE2"] + "')>0 ";
break;
case "DECLNO": //报关单号
where += " and instr(det.DECLARATIONCODE,'" + Request["VALUE2"] + "')>0 ";
break;
case "CONTRACTNO": //合同协议号
where += " and instr(det.CONTRACTNO,'" + Request["VALUE2"] + "')>0 ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE3"]))//判断查询条件3是否有值
{
switch (Request["CONDITION3"])
{
case "DYBZ": //打印标志
where += " and det.ISPRINT='" + Request["VALUE3"] + "' ";
break;
}
}
switch (Request["CONDITION4"])
{
case "SUBMITTIME": //订单委托日期
if (!string.IsNullOrEmpty(Request["VALUE4_1"])) //如果开始时间有值
{
where += " and ort.SUBMITTIME>=to_date('" + Request["VALUE4_1"] + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(Request["VALUE4_2"])) //如果结束时间有值
{
where += " and ort.SUBMITTIME<=to_date('" + Request["VALUE4_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
case "REPTIME": //申报时间
if (!string.IsNullOrEmpty(Request["VALUE4_1"])) //如果开始时间有值
{
where += " and det.REPTIME>=to_date('" + Request["VALUE4_1"] + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(Request["VALUE4_2"])) //如果结束时间有值
{
where += " and det.REPTIME<=to_date('" + Request["VALUE4_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
}
if (role == "supplier") //如果是现场服务角色
{
where += @" and ort.SCENEDECLAREID ='" + json_user.Value <string>("CustomerId") + "' ";
}//如果是企业服务
if (role == "enterprise")
{
where += @" and ort.BUSIUNITCODE IN
(SELECT b.incode QUANCODE FROM USER_RENAME_COMPANY a left join BASE_COMPANY b on a.companyid = b.id
where b.incode is not null and a.companyenname is not null and a.customerid = " + json_user.Value <string>("CustomerId") + ") ";
}
if (role == "customer")//如果角色是客户
{
where += @" and ort.customercode ='" + json_user.Value <string>("CUSTOMERCODE") + "' ";
}
//2016-6-24 更新报关单列表显示逻辑 根据报关单对应的订单【DECLPDF】即报关单是否已关联好PDF文件，作为显示的条件 国内业务不需要去判断关联订单，因为打这两个标志的时候已经判断了
//DECL_TRANSNAME 预制报关单的运输工具名称
//运输工具名称的显示需要更改为一下逻辑：根据草单中的申报库别 如果是13或者17 运输工具名称取预制报关单里面的。否则取草单的运输工具名称
string sql = @"select det.ID,det.PREDECLCODE,det.DECLARATIONCODE,det.CODE,ort.CUSTOMERNAME ,det.REPFINISHTIME, det.CUSTOMSSTATUS ,
det.ISPRINT,det.CONTRACTNO,det.GOODSNUM,det.GOODSNW,det.SHEETNUM,det.ORDERCODE,det.STARTTIME CREATEDATE,
det.BUSITYPE BUSITYPE,det.TRANSNAME DECL_TRANSNAME,
prt.TRANSNAME,prt.BUSIUNITCODE, prt.PORTCODE, prt.BLNO, prt.DECLTYPE,
ort.REPWAYID ,ort.REPWAYID REPWAYNAME,ort.DECLWAY ,ort.DECLWAY DECLWAYNAME,ort.TRADEWAYCODES ,
ort.CUSNO ,ort.IETYPE,ort.ASSOCIATENO,ort.CORRESPONDNO,ort.BUSISHORTNAME
from list_declaration det
left join list_predeclaration prt on det.predeclcode = prt.predeclcode
left join list_order ort on prt.ordercode = ort.code
where (ort.DECLPDF =1 or ort.PREPDF=1) and det.isinvalid=1 and instr('" + busitypeid + "',det.busitype)>0 " + where;
DataTable dt = DBMgr.GetDataTable(GetPageSql(sql, "CREATEDATE", "desc"));
IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式
iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
var json = JsonConvert.SerializeObject(dt, iso);
return("{rows:" + json + ",total:" + totalProperty + "}");
}
private void CreateReportRows()
{
string wardFuncQuery = "SELECT CLASS.DATA_ AS 'FUNC. CLASS', WARD.DATA_ AS WARD, sum(LENGTH.DATA_) / 5280 AS LONGNESS FROM CLASS INNER JOIN LENGTH ON (CLASS.FACILITY = LENGTH.FACILITY AND CLASS.SECTION = LENGTH.SECTION) INNER JOIN WARD ON (CLASS.FACILITY = WARD.FACILITY AND CLASS.SECTION = WARD.SECTION) GROUP BY CLASS.DATA_, WARD.DATA_";
DataSet wardFuncData = DBMgr.ExecuteQuery(wardFuncQuery);
string wardsQuery = "SELECT DISTINCT WARD.DATA_ FROM WARD ORDER BY WARD.DATA_";
DataSet distinctWards = DBMgr.ExecuteQuery(wardsQuery);
string funcsQuery = "SELECT DISTINCT CLASS.DATA_ FROM CLASS ORDER BY CLASS.DATA_";
DataSet distinctFuncs = DBMgr.ExecuteQuery(funcsQuery);
string aggregateQuery = "SELECT WARD.DATA_ AS WARD, sum(LENGTH.DATA_) / 5280 AS LONGNESS FROM LENGTH INNER JOIN WARD ON (LENGTH.FACILITY = WARD.FACILITY AND LENGTH.SECTION = WARD.SECTION) GROUP BY WARD.DATA_";
DataSet aggregateData = DBMgr.ExecuteQuery(aggregateQuery);
List <string> wards = new List <string>();
List <string> funcs = new List <string>();
foreach (DataRow wardRow in distinctWards.Tables[0].Rows)
{
wards.Add(wardRow[0].ToString());
}
foreach (DataRow funcRow in distinctFuncs.Tables[0].Rows)
{
funcs.Add(funcRow[0].ToString());
}
if (wards[0] != "")
{
wards.Insert(0, "");
}
if (funcs[0] != "")
{
funcs.Insert(0, "");
}
//we need an extra column for the class names and two extra rows (1 for the column headers and one for the aggregate line)
int height = funcs.Count + 2;
int width = wards.Count + 1;
object[,] writeToExcel = new object[height, width];
for (int h = 0; h + 1 < height; ++h)
{
for (int w = 0; w + 1 < width; ++w)
{
writeToExcel[h + 1, w + 1] = 0.0;
}
}
writeToExcel[0, 0] = "Func. Class";
int columnIndex = 1;
foreach (string ward in wards)
{
writeToExcel[0, columnIndex] = "W - " + ward;
++columnIndex;
}
int rowIndex = 1;
foreach (string func in funcs)
{
writeToExcel[rowIndex, 0] = func;
++rowIndex;
}
writeToExcel[rowIndex, 0] = "Total";
foreach (DataRow mileageRow in wardFuncData.Tables[0].Rows)
{
int columnToIncrement = wards.IndexOf(mileageRow["WARD"].ToString());
int rowToIncrement = funcs.IndexOf(mileageRow["Func. Class"].ToString());
writeToExcel[rowToIncrement + 1, columnToIncrement + 1] = ((double)writeToExcel[rowToIncrement + 1, columnToIncrement + 1]) + double.Parse(mileageRow["LONGNESS"].ToString());
}
columnIndex = 1;
foreach (string ward in wards)
{
double sum = 0.0;
for (int i = 0; i < funcs.Count; ++i)
{
sum += ((double)writeToExcel[i + 1, columnIndex]);
}
writeToExcel[rowIndex, columnIndex] = sum;
++columnIndex;
}
char finalLetter = Convert.ToChar(Convert.ToInt32('A') + width - 1);
int finalRow = height + 6;
string rangeSelector = "A7:" + finalLetter + finalRow.ToString();
ReportGlobals.XLMgr.SetValues(rangeSelector, writeToExcel);
ReportGlobals.XLMgr.SetNumberFormat(rangeSelector, "0.00");
//ReportGlobals.XLMgr.AutoSizeColumns("A:" + finalLetter);
}
//基础资料 by heguiqin 2016-08-25
public string Ini_Base_Data()
{
IDatabase db = SeRedis.redis.GetDatabase();
string sql = "";
string json_sbfs = "[]";//申报方式
string busitype = Request["busitype"];
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
sql = "select CODE,NAME||'('||CODE||')' NAME from SYS_REPWAY where Enabled=1 and instr(busitype,'" + busitype + "')>0";
if (busitype == "空运进口")
{
//if ((Int32)redisClient.Exists("common_data_sbfs:kj") == 1)
//{
// json_sbfs = redisClient.Get<string>("common_data_sbfs:kj");
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:kj", json_sbfs);
//}
}
if (busitype == "空运出口")
{
//if (db.KeyExists("common_data_sbfs:kc"))
//{
// json_sbfs = db.StringGet("common_data_sbfs:kc");
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:kc", json_sbfs);
// }
}
if (busitype == "陆运进口")
{
//if (db.KeyExists("common_data_sbfs:lj"))
//{
// json_sbfs = db.StringGet("common_data_sbfs:lj");
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:lj", json_sbfs);
//}
}
if (busitype == "陆运出口")
{
//if (db.KeyExists("common_data_sbfs:lc"))
//{
// json_sbfs = db.StringGet("common_data_sbfs:lc");
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:lc", json_sbfs);
//}
}
if (busitype == "海运进口")
{
//if (db.KeyExists("common_data_sbfs:hj"))
//{
// json_sbfs = db.StringGet("common_data_sbfs:hj");
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:hj", json_sbfs);
// }
}
if (busitype == "海运出口")
{
if (db.KeyExists("common_data_sbfs:hc"))
{
json_sbfs = db.StringGet("common_data_sbfs:hc");
}
else
{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:hc", json_sbfs);
}
}
if (busitype == "特殊区域")
{
//if (db.KeyExists("common_data_sbfs:ts"))
//{
// json_sbfs = db.StringGet("common_data_sbfs:ts").ToString();
//}
//else
//{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:ts", json_sbfs);
//}
}
if (busitype == "国内")
{
if (db.KeyExists("common_data_sbfs:gn"))
{
json_sbfs = db.StringGet("common_data_sbfs:gn");
}
else
{
json_sbfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data_sbfs:gn", json_sbfs);
}
}
string json_sbgq = "[]";//申报关区 进口口岸
if (db.KeyExists("common_data:sbgq"))
{
json_sbgq = db.StringGet("common_data:sbgq");
}
else
{
sql = "select CODE,NAME||'('||CODE||')' NAME from BASE_CUSTOMDISTRICT where ENABLED=1 ORDER BY CODE";
json_sbgq = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:sbgq", json_sbgq);
}
string json_jydw = "";//经营单位
if (db.KeyExists("jydw:" + json_user.Value <string>("CUSTOMERID")))
{
json_jydw = db.StringGet("jydw:" + json_user.Value <string>("CUSTOMERID"));
}
else
{
//2016-6-2 梁总提出一个改进 如果某一个经营单位 客户先 添加到自己的库了，但后来总库里面禁用了，则客户自己的库中也要禁用掉
sql = @"SELECT T.* FROM (
SELECT a.CUSTOMERID, a.companychname||'('||a.companyenname||')' NAME ,a.companychname SHORTNAME, a.companyenname CODE,b.incode QUANCODE,b.name QUANNAME FROM USER_RENAME_COMPANY a
left join BASE_COMPANY b
on a.companyid = b.id
where b.incode is not null and a.companyenname is not null and b.enabled=1) T
WHERE T.CUSTOMERID = '" + json_user.Value <string>("CUSTOMERID") + "'";
json_jydw = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("jydw:" + json_user.Value <string>("CUSTOMERID"), json_jydw);
}
string json_bgfs = "[]";//报关方式
if (db.KeyExists("common_data:bgfs"))
{
json_bgfs = db.StringGet("common_data:bgfs");
}
else
{
sql = "select CODE,NAME||'('||CODE||')' NAME from SYS_DECLWAY where enabled=1 order by id asc";
json_bgfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:bgfs", json_bgfs);
}
string json_bzzl = "[]";//包装种类
if (db.KeyExists("common_data:bzzl"))
{
json_bzzl = db.StringGet("common_data:bzzl");
}
else
{
sql = "select CODE,NAME||'('||CODE||')' NAME from base_Packing";
json_bzzl = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:bzzl", json_bzzl);
}
string json_myfs = "[]";//贸易方式
if (db.KeyExists("common_data:myfs"))
{
json_myfs = db.StringGet("common_data:myfs");
}
else
{
sql = @"select ID,CODE,NAME||'('||CODE||')' NAME from BASE_DECLTRADEWAY WHERE enabled=1";
json_myfs = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:myfs", json_myfs);
}
string json_containertype = "[]";//集装箱类型
if (db.KeyExists("common_data:containertype"))
{
json_containertype = db.StringGet("common_data:containertype");
}
else
{
sql = "select CODE,NAME||'('||CONTAINERCODE||')' as MERGENAME,CONTAINERCODE from BASE_CONTAINERTYPE where enabled=1";
json_containertype = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:containertype", json_containertype);
}
string json_containersize = "[]";//集装箱尺寸
if (db.KeyExists("common_data:containersize"))
{
json_containersize = db.StringGet("common_data:containersize");
}
else
{
sql = "select CODE,NAME as CONTAINERSIZE,NAME||'('||DECLSIZE||')' as MERGENAME,DECLSIZE from BASE_CONTAINERSIZE where enabled=1";
json_containersize = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:containersize", json_containersize);
}
string json_truckno = "[]";//报关车号
if (db.KeyExists("common_data:truckno"))
{
json_truckno = db.StringGet("common_data:truckno");
}
else
{
sql = @"select t.license, t.license||'('||t.whitecard||')' as MERGENAME,t.whitecard,t1.NAME||'('|| t1.CODE||')' as UNITNO from sys_declarationcar t
left join base_motorcade t1 on t.motorcade=t1.code where t.enabled=1";
json_truckno = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:truckno", json_truckno);
}
string json_relacontainer = "[]";//关联集装箱信息 通过选择集装箱类型的CODE和集装箱尺寸的CODE,会自动匹配关联集装箱信息
if (db.KeyExists("common_data:relacontainer"))
{
json_relacontainer = db.StringGet("common_data:relacontainer");
}
else
{
sql = @"select CONTAINERSIZE,CONTAINERTYPE,FORMATNAME,CONTAINERHS from rela_container t where t.enabled=1";
json_relacontainer = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:relacontainer", json_relacontainer);
}
//木质包装
string json_mzbz = "[]";
if (db.KeyExists("common_data:mzbz"))
{
json_mzbz = db.StringGet("common_data:mzbz");
}
else
{
sql = @"select CODE,NAME||'('||CODE||')' NAME from SYS_WOODPACKING";
json_mzbz = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
db.StringSet("common_data:mzbz", json_mzbz);
}
// string json_cur_usr = "";//当前用户信息
// sql = @"SELECT a.*,b.INTERFACECODE FROM sys_user a left join sys_customer b on a.customerid=b.id
// WHERE a.ID = '" + json_user.Value<string>("ID") + "'";
// json_cur_usr = JsonConvert.SerializeObject(DBMgr.GetDataTable(sql));
return("{jydw:" + json_jydw + ",sbfs:" + json_sbfs + ",sbgq:" + json_sbgq + ",bgfs:" + json_bgfs + ",bzzl:" + json_bzzl + ",myfs:" + json_myfs + ",containertype:" + json_containertype + ",containersize:" + json_containersize + ",truckno:" + json_truckno + ",relacontainer:" + json_relacontainer + ",mzbz:" + json_mzbz + "}");
}
private void MainMapImage_MouseDoubleClick(object sender, MouseEventArgs e)
{
//if (this.ImageView)
if (true)
{
PointF ptF = new PointF((float)e.X, (float)e.Y);
SharpMap.Geometries.Point pt = MainMapImage.Map.ImageToWorld(ptF);
#if DDOT
TransformGeo(pt);
#endif
String strQuery;
if (pt.X < 0)
{
strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX >"
+ pt.X * 1.001 + " AND " + pt.X * 0.999 + " > Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * 1.001 + " AND " + pt.Y * 0.999 + " < Envelope_MaxY";
}
else
{
strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX <"
+ pt.X * 1.001 + " AND " + pt.X * 0.999 + " < Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * 1.001 + " AND " + pt.Y * 0.999 + " < Envelope_MaxY";
}
try
{
// Get the list of geometries whose bounding boxes contain the selected point.
DataSet ds = DBMgr.ExecuteQuery(strQuery);
Geometry geoNearest = null;
double dMinDistance = double.PositiveInfinity;
double dDistance;
String strFacility = "";
String strSection = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
VectorLayer myLayer = (VectorLayer)MainMapImage.Map.Layers[0];
Geometry geo = (Geometry)myLayer.GeoIDs[dr["SECTIONID"].ToString()];
// Find the smallest distance between the clicked point and the geometries that met the selection
// criteria from the above select statement.
dDistance = geo.Distance(pt);
if (dDistance < dMinDistance)
{
dMinDistance = dDistance;
geoNearest = geo;
strFacility = dr["FACILITY"].ToString();
strSection = dr["SECTION"].ToString();
}
}
if (!String.IsNullOrEmpty(strFacility) && !String.IsNullOrEmpty(strSection))
{
String strSelect = "SELECT SECTIONID,DIRECTION,BEGIN_STATION FROM SECTION_" + m_strNetworkID + " WHERE FACILITY='" + strFacility + "' AND SECTION='" + strSection + "'";
DataSet dsNavigation = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow row in dsNavigation.Tables[0].Rows)
{
String strID = row["SECTIONID"].ToString();
int nSectionID = int.Parse(strID);
if (nSectionID < 1000000)
{
String strDirection = row["DIRECTION"].ToString();
String strBeginStation = row["BEGIN_STATION"].ToString();
m_event.issueEvent(new NavigationEvent(strFacility, strDirection, double.Parse(strBeginStation)));
}
else
{
m_event.issueEvent(new NavigationEvent(strFacility, strSection));
}
}
}
}
catch (Exception except)
{
Global.WriteOutput("Error handling double click: " + except.Message);
}
}
}
private void AssetAdvancedSearch()
{
String strQuery = "";
if (MainMapImage.Map.Layers.Count > 0)
{
strQuery = tbAdvancedSearch.Text;
FormQueryRaw form = new FormQueryRaw(MainMapImage.Map.Layers[0].LayerName, strQuery);
if (form.ShowDialog() == DialogResult.OK)
{
tbAdvancedSearch.Text = form.m_strQuery;
// Find the geoms that match the where clause of the query. Then redraw the layer with only
// those geoms? Or highlight those geoms?
List <Geometry> listGeoms = new List <Geometry>();
strQuery = "SELECT GEO_ID FROM " + MainMapImage.Map.Layers[0].LayerName + " WHERE " + form.m_strQuery;
DataReader dr;
try
{
dr = new DataReader(strQuery);
while (dr.Read())
{
try
{
m_listGeomIDs.Add(dr["GEO_ID"].ToString());
}
catch (Exception excInner)
{
Global.WriteOutput("Warning: Point not valid." + excInner.Message + " " + dr[0].ToString());
}
}
dr.Close();
}
catch (Exception exc)
{
Global.WriteOutput("Error: Failed to load asset geometries." + exc.Message);
}
// Add the new selection to the combobox and dgv
String strSelect = "SELECT * FROM " + MainMapImage.Map.Layers[0].LayerName + " WHERE GEO_ID = '";
String strWhere = "";
for (int i = 0; i < m_listGeomIDs.Count; i++)
{
if (i < m_listGeomIDs.Count - 1)
{
strWhere += m_listGeomIDs[i] + "' OR GEO_ID = '";
}
else
{
strWhere += m_listGeomIDs[i] + "'";
}
}
strSelect += strWhere;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
FormGISLayerManager formGISLayerManager;
if (FormManager.IsFormGISLayerManagerOpen(out formGISLayerManager))
{
formGISLayerManager.SetAdvSearchColor(Color.Cyan, m_listGeomIDs);
m_listGeomIDs.Clear();
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: " + exc.Message);
}
}
}
}
private void AttributeMapQuery(MouseEventArgs e)
{
// Get the section of the road that the user selected, and any information regarding the segmented
// attribute.
ApplyOldColorsToGeoms(false);
PointF ptF = new PointF((float)e.X, (float)e.Y);
SharpMap.Geometries.Point pt = MainMapImage.Map.ImageToWorld(ptF);
String strQuery = "SELECT SECTIONID, FACILITY, SECTION, BEGIN_STATION, END_STATION,"
+ " DIRECTION, AREA, UNITS, GEOMETRY FROM SECTION_" + m_strNetworkID
+ " WHERE Envelope_MinX <"
+ pt.X * (pt.X > 0 ? 1.001 : 0.999) + " AND " + pt.X * (pt.X > 0 ? 0.999 : 1.001) + " < Envelope_MaxX AND Envelope_MinY <"
+ pt.Y * (pt.Y > 0 ? 1.001 : 0.999) + " AND " + pt.Y * (pt.Y > 0 ? 0.999 : 1.001) + " < Envelope_MaxY";
try
{
// Get the list of geometries whose bounding boxes contain the selected point.
DataSet ds = DBMgr.ExecuteQuery(strQuery);
Geometry geoNearest = null;
double dMinDistance = double.PositiveInfinity;
double dDistance;
int iSectionID = -1;
foreach (DataRow dr in ds.Tables[0].Rows)
{
VectorLayer myLayer = (VectorLayer)MainMapImage.Map.Layers[0];
Geometry geo = (Geometry)myLayer.GeoIDs[dr["SECTIONID"].ToString()];
// Find the smallest distance between the clicked point and the geometries that met the selection
// criteria from the above select statement.
if (geo != null)
{
dDistance = geo.Distance(pt);
if (dDistance < dMinDistance)
{
dMinDistance = dDistance;
geoNearest = geo;
// Also, grab the section id of the clicked point
iSectionID = Int32.Parse(dr["SECTIONID"].ToString());
}
}
}
if (geoNearest != null)
{
m_htGeometryOldColors.Add(geoNearest, geoNearest.Color);
geoNearest.Width_ = 5;
geoNearest.Color = Color.Cyan;
MainMapImage.Refresh();
try
{
AttributeTab attributeTab;
if (FormManager.IsAttributeTabOpen(out attributeTab))
{
AssetTab assetTab;
if (FormManager.IsAssetTabOpen(out assetTab))
{
assetTab.Hide();
}
if (attributeTab.IsHidden)
{
attributeTab.Show();
}
attributeTab.SetSectionID(iSectionID);
attributeTab.PopulateComboBoxYears();
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Problem querying SEGMENT_" + m_strNetworkID + "_NS0 table. " + exc.Message);
}
}
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not get ATTRIBUTE or NETWORK MAP data. " + exc.Message);
}
}
/// <summary>
/// Loads existing data for this SECTION and YEAR
/// </summary>
private void FillAttributeValueHash()
{
// Fill list of variables available in Simulation. May not add new for single section run.
m_listAttributeSimulation = new List <String>();
String strSelect = "SELECT SIMULATION_VARIABLES FROM SIMULATIONS WHERE SIMULATIONID='" + SimulationID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
string[] attributes = ds.Tables[0].Rows[0].ItemArray[0].ToString().Split(new string[] { "\t" }, StringSplitOptions.None);
for (int i = 0; i < attributes.Length; i++)
{
m_listAttributeSimulation.Add(attributes[i]);
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing SIMULATION variables." + exception.Message;
}
//Fill list of current values starting with LENGTH and AREA for COST calculations
m_hashAttributeValue = new Hashtable();
//Get Length and Area for COST calculations
String strArea = "0";
double dLength = 0;
strSelect = "SELECT END_STATION,BEGIN_STATION,AREA FROM SECTION_" + NetworkID + " WHERE SECTIONID='" + SectionID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
if (!String.IsNullOrEmpty(dr["END_STATION"].ToString()) || !String.IsNullOrEmpty(dr["BEGIN_STATION"].ToString()))
{
dLength = (double)dr["END_STATION"] - (double)dr["BEGIN_STATION"];
m_hashAttributeValue.Add("LENGTH", dLength);
}
strArea = dr["AREA"].ToString();
double dArea = Convert.ToDouble(strArea);
m_hashAttributeValue.Add("AREA", dArea);
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading AREA information for cost calculation. " + exception.Message;
}
//Get values for each simulation value
strSelect = "SELECT ";
foreach (String str in m_listAttributeSimulation)
{
if (strSelect.Length != 7)
{
strSelect += ",";//Does not add comma first pass
}
String strAttributeYear = str + "_" + this.Year;
strSelect += strAttributeYear;
}
String strWhere = " FROM " + this.SimulationTable + " WHERE SECTIONID ='" + this.SectionID + "'";
strSelect += strWhere;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
foreach (String attribute in m_listAttributeSimulation)
{
m_hashAttributeValue.Add(attribute, dr[attribute + "_" + this.Year]);
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing SIMULATION RESULTS table." + exception.Message;
}
//Load current treatment
strSelect = "SELECT TREATMENT,COST_,BUDGET,YEARSANY,YEARSSAME,CHANGEHASH,AREA FROM " + this.ReportTable + " WHERE YEARS='" + this.Year + "' AND SECTIONID='" + this.SectionID + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
m_strCurrentTreatment = dr["TREATMENT"].ToString();
double dCost = double.Parse(dr["COST_"].ToString()); //inflation already factored in in the report table
double dArea = double.Parse(dr["AREA"].ToString());
//dCost *= dArea;
m_strCurrentCost = dCost.ToString("c");
m_strCurrentBudget = dr["BUDGET"].ToString();
m_strCurrentAny = dr["YEARSANY"].ToString();
m_strCurrentSame = dr["YEARSSAME"].ToString();
String strHashChange = dr["CHANGEHASH"].ToString();
m_hashCurrentChange = new Hashtable();
string[] pairs = strHashChange.Split(new string[] { "\n" }, StringSplitOptions.None);
for (int i = 0; i < pairs.Length; i++)
{
if (pairs[i].Contains("\t"))
{
string[] attributechange = pairs[i].Split(new string[] { "\t" }, StringSplitOptions.None);
m_hashCurrentChange.Add(attributechange[0], attributechange[1]);
}
}
}
}
catch (Exception exception)
{
labelError.Visible = true;
labelError.Text = "Error: Loading and parsing REPORT table." + exception.Message;
}
}
//订单列表页加载方法 by panhuaguo 2016-08-25
public string LoadList()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string where = "";
if (!string.IsNullOrEmpty(Request["VALUE1"]))//判断查询条件1是否有值
{
switch (Request["CONDITION1"])
{
case "BUSIUNITCODE": //经营单位
where += " and BUSISHORTCODE='" + Request["VALUE1"] + "' ";
break;
case "CUSTOMDISTRICTCODE": //申报关区
where += " and CUSTOMDISTRICTCODE='" + Request["VALUE1"] + "' ";
break;
case "PORTCODE": //进口口岸
where += " and PORTCODE='" + Request["VALUE1"] + "' ";
break;
case "REPWAYID": //申报方式
where += " and REPWAYID='" + Request["VALUE1"] + "' ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE2"]))//判断查询条件1是否有值
{
switch (Request["CONDITION2"])
{
case "CODE": //订单编号
where += " and instr(CODE,'" + Request["VALUE2"].Trim() + "')>0 ";
break;
case "CUSNO": //客户编号
where += " and instr(CUSNO,'" + Request["VALUE2"].Trim() + "')>0 ";
break;
case "DIVIDENO": //分单号
where += " and instr(DIVIDENO,'" + Request["VALUE2"].Trim() + "')>0 ";
break;
case "CONTRACTNO": //合同发票号
where += " and instr(CONTRACTNO,'" + Request["VALUE2"].Trim() + "')>0 ";
break;
case "MANIFEST": //载货清单号
where += " and instr(MANIFEST,'" + Request["VALUE2"].Trim() + "')>0 ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE3"]))//判断查询条件1是否有值
{
switch (Request["CONDITION3"])
{
case "ddzt": //订单状态
if ((Request["VALUE3"] + "") == "草稿") //草稿=草稿
{
where += " and STATUS='1' ";
}
if ((Request["VALUE3"] + "") == "订单待委托") //订单待委托 < 已委托
{
where += " and STATUS < 15 ";
}
if ((Request["VALUE3"] + "") == "订单待受理") //已委托 <= 订单待受理 < 已受理
{
where += " and STATUS >= 15 and STATUS < 20 ";
}
if ((Request["VALUE3"] + "") == "订单受理中") //已受理 <= 订单受理中 < 已交付
{
where += " and STATUS >= 20 and STATUS < 110 ";
}
if ((Request["VALUE3"] + "") == "订单待交付") //已委托 <= 订单待交付 < 已交付
{
where += " and STATUS >= 15 and STATUS < 110 ";
}
if ((Request["VALUE3"] + "") == "订单已交付") //订单已交付 = 已交付
{
where += " and STATUS='110' ";
}
if ((Request["VALUE3"] + "") == "订单已作废")
{
where += " and ISINVALID='0' ";
}
break;
case "bgzt":
where += " and DECLSTATUS='" + Request["VALUE3"] + "' ";
break;
case "bjzt":
where += " and INSPSTATUS='" + Request["VALUE3"] + "' ";
break;
}
}
switch (Request["CONDITION4"])
{
case "SUBMITTIME": //委托日期
if (!string.IsNullOrEmpty(Request["VALUE4_1"])) //如果开始时间有值
{
where += " and SUBMITTIME>=to_date('" + Request["VALUE4_1"] + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(Request["VALUE4_2"])) //如果结束时间有值
{
where += " and SUBMITTIME<=to_date('" + Request["VALUE4_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
case "CSSTARTTIME": //订单开始时间
if (!string.IsNullOrEmpty(Request["VALUE4_1"])) //如果开始时间有值
{
where += " and CREATETIME>=to_date('" + Request["VALUE4_1"] + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(Request["VALUE4_2"])) //如果结束时间有值
{
where += " and CREATETIME<=to_date('" + Request["VALUE4_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
}
if (!string.IsNullOrEmpty(Request["VALUE5"]))//判断查询条件5是否有值
{
switch (Request["CONDITION5"])
{
case "BUSIUNITCODE": //经营单位
where += " and BUSISHORTCODE='" + Request["VALUE5"] + "' ";
break;
case "CUSTOMDISTRICTCODE": //申报关区
where += " and CUSTOMDISTRICTCODE='" + Request["VALUE5"] + "' ";
break;
case "PORTCODE": //进口口岸
where += " and PORTCODE='" + Request["VALUE5"] + "' ";
break;
case "REPWAYID": //申报方式
where += " and REPWAYID='" + Request["VALUE5"] + "' ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE6"]))//判断查询条件1是否有值
{
switch (Request["CONDITION6"])
{
case "CODE": //订单编号
where += " and instr(CODE,'" + Request["VALUE6"].Trim() + "')>0 ";
break;
case "CUSNO": //客户编号
where += " and instr(CUSNO,'" + Request["VALUE6"].Trim() + "')>0 ";
break;
case "DIVIDENO": //分单号
where += " and instr(DIVIDENO,'" + Request["VALUE6"].Trim() + "')>0 ";
break;
case "CONTRACTNO": //合同发票号
where += " and instr(CONTRACTNO,'" + Request["VALUE6"].Trim() + "')>0 ";
break;
case "MANIFEST": //载货清单号
where += " and instr(MANIFEST,'" + Request["VALUE6"].Trim() + "')>0 ";
break;
}
}
if (!string.IsNullOrEmpty(Request["VALUE7"]))//判断查询条件1是否有值
{
switch (Request["CONDITION7"])
{
case "ddzt": //订单状态
//草稿=草稿
if ((Request["VALUE7"] + "") == "草稿")
{
where += " and STATUS='1' ";
}
//订单待委托 < 已委托
if ((Request["VALUE7"] + "") == "订单待委托")
{
where += " and STATUS < 15 ";
}
//已委托 <= 订单待受理 < 已受理
if ((Request["VALUE7"] + "") == "订单待受理")
{
where += " and STATUS >= 15 and STATUS < 20 ";
}
//已受理 <= 订单受理中 < 已交付
if ((Request["VALUE7"] + "") == "订单受理中")
{
where += " and STATUS >= 20 and STATUS < 110 ";
}
//已委托 <= 订单待交付 < 已交付
if ((Request["VALUE7"] + "") == "订单待交付")
{
where += " and STATUS >= 15 and STATUS < 110 ";
}
//订单已交付 = 已交付
if ((Request["VALUE7"] + "") == "订单已交付")
{
where += " and STATUS='110' ";
}
if ((Request["VALUE7"] + "") == "订单已作废")
{
where += " and ISINVALID='0' ";
}
break;
case "bgzt":
where += " and DECLSTATUS='" + Request["VALUE7"] + "' ";
break;
case "bjzt":
where += " and INSPSTATUS='" + Request["VALUE7"] + "' ";
break;
}
}
switch (Request["CONDITION8"])
{
case "SUBMITTIME": //委托日期
if (!string.IsNullOrEmpty(Request["VALUE8_1"])) //如果开始时间有值
{
where += " and SUBMITTIME>=to_date('" + Request["VALUE8_1"] + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(Request["VALUE8_2"])) //如果结束时间有值
{
where += " and SUBMITTIME<=to_date('" + Request["VALUE8_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
case "CSSTARTTIME": //订单开始时间
if (!string.IsNullOrEmpty(Request["VALUE8_1"])) //如果开始时间有值
{
where += " and CREATETIME>=to_date('" + Request["VALUE8_1"] + "','yyyy-mm-dd hh24:mi:ss')' ";
}
if (!string.IsNullOrEmpty(Request["VALUE8_2"])) //如果结束时间有值
{
where += " and CREATETIME<=to_date('" + Request["VALUE8_2"].Replace("00:00:00", "23:59:59") + "','yyyy-mm-dd hh24:mi:ss') ";
}
break;
}
if ((Request["VALUE3"] + "") != "订单已作废" && (Request["VALUE7"] + "") != "订单已作废")//在不查询已作废的订单情形下，皆显示正常的订单
{
where += " and ISINVALID='1' ";
}
if ((Request["OnlySelf"] + "").Trim() == "fa fa-check-square-o")
{
where += " and CREATEUSERID = " + json_user.Value <string>("ID") + " ";
}
IsoDateTimeConverter iso = new IsoDateTimeConverter();//序列化JSON对象时,日期的处理格式 BUSISHORTNAME,
iso.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
string sql = @"select ID, CODE,ENTRUSTTYPEID,CUSNO,PORTCODE,PORTNAME,BUSIUNITNAME,FIRSTLADINGBILLNO,SECONDLADINGBILLNO,
BUSITYPE,CORRESPONDNO,LADINGBILLNO,ARRIVEDNO,CUSTOMERNAME,CONTRACTNO,TOTALNO,DIVIDENO,TURNPRENO,
GOODSNUM || '/'|| GOODSGW GOODSNUMGOODSNW,GOODSGW,REPWAYID, GOODSWEIGHT,CUSTOMDISTRICTCODE,
CUSTOMDISTRICTNAME,BUSISHORTNAME,ISINVALID,LAWCONDITION,STATUS,DECLSTATUS,INSPSTATUS,
ASSOCIATENO,createtime CREATEDATE,SUBMITTIME from LIST_ORDER where instr('" + Request["busitypeid"] + "',BUSITYPE)>0 and customercode='" + json_user.Value <string>("CUSTOMERCODE") + "' " + where;
DataTable dt = DBMgr.GetDataTable(GetPageSql(sql, "CREATEDATE", "desc"));
var json = JsonConvert.SerializeObject(dt, iso);
return("{rows:" + json + ",total:" + totalProperty + "}");
}
/// <summary>
/// Setup datagridviews for form.
/// </summary>
private void SetupDataGridViews()
{
m_bChange = false;
m_listFeasibleTreatment = new List <String>();
String strSelect = "SELECT TREATMENT FROM " + this.BenefitCostTable + " INNER JOIN " + this.SectionTable + " ON " + this.SectionTable + ".SECTIONID=" + this.BenefitCostTable + ".SECTIONID WHERE " + this.BenefitCostTable + ".SECTIONID=" + this.SectionID + " AND YEARS=" + this.Year;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
m_listFeasibleTreatment.Add(dr["TREATMENT"].ToString());
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving feasibility information for tooltip." + exception.Message);
}
if (dgvAttribute == null)
{
return;
}
dgvAttribute.Columns.Clear();
DataGridViewComboBoxColumn comboboxColumn;
comboboxColumn = CreateComboBoxColumn();
comboboxColumn.HeaderText = "Attribute";
Global.LoadAttributes();
foreach (String str in m_listAttributeSimulation)
{
comboboxColumn.Items.Add(str);
}
dgvAttribute.Columns.Insert(0, comboboxColumn);
dgvAttribute.RowHeadersVisible = true;
dgvAttribute.AllowUserToDeleteRows = true;
dgvAttribute.ColumnCount = 2;
dgvAttribute.Columns[1].Name = "Change";
dgvAttribute.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
dgvAttribute.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
foreach (String key in m_hashCurrentChange.Keys)
{
int nRow = dgvAttribute.Rows.Add(key, m_hashCurrentChange[key]);
}
DataGridViewRow dataGridRow = new DataGridViewRow();
DataGridViewCell[] cells = new DataGridViewCell[2];
DataGridViewTextBoxCell txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbShowAll = new DataGridViewComboBoxCell();
txtCell.Value = "Show Only Feasible";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbShowAll);
cbShowAll.Items.Add("True");
cbShowAll.Items.Add("False");
cbShowAll.Value = "True";
dgvSummary.Rows.Add(dataGridRow);
dataGridRow = new DataGridViewRow();
cells = new DataGridViewCell[2];
txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbTreatment = new DataGridViewComboBoxCell();
txtCell.Value = "Treatment";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbTreatment);
foreach (String str in m_listFeasibleTreatment)
{
cbTreatment.Items.Add(str);
}
dgvSummary.Rows.Add(dataGridRow);
if (!cbTreatment.Items.Contains("No Treatment"))
{
cbTreatment.Items.Add("No Treatment");
}
if (!cbTreatment.Items.Contains(m_strCurrentTreatment))
{
cbTreatment.Items.Add(m_strCurrentTreatment);
}
cbTreatment.Value = m_strCurrentTreatment;
// ------------------------------------------------
// Add a combobox cell and add the combobox items
dataGridRow = new DataGridViewRow();
cells = new DataGridViewCell[2];
txtCell = new DataGridViewTextBoxCell();
DataGridViewComboBoxCell cbBudget = new DataGridViewComboBoxCell();
cbBudget.Items.Add("");
foreach (String str in m_listBudgets)
{
cbBudget.Items.Add(str);
}
if (!m_listBudgets.Contains(m_strCurrentBudget))
{
cbBudget.Items.Add(m_strCurrentBudget);
}
cbBudget.Value = m_strCurrentBudget;
txtCell.Value = "Budget";
dataGridRow.Cells.Add(txtCell);
txtCell.ReadOnly = true;
dataGridRow.Cells.Add(cbBudget);
dgvSummary.Rows.Add(dataGridRow);
float fCost = 0;
int nCostRow = dgvSummary.Rows.Add("Cost", fCost);
dgvSummary.Rows[nCostRow].Cells[1].Style.Format = "c";
dgvSummary[1, nCostRow].Value = m_strCurrentCost;
dgvSummary.Rows.Add("Years Before Any", m_strCurrentAny);
dgvSummary.Rows.Add("Years Before Same", m_strCurrentSame);
m_bChange = true;
}
/// <summary>
/// Loads all feasibility criteria for this treatment in for this treatment ID.
/// </summary>
public bool LoadFeasibility()
{
String strSelect = "SELECT CRITERIA,BINARY_CRITERIA,FEASIBILITYID FROM " + cgOMS.Prefix + "FEASIBILITY WHERE TREATMENTID='" + this.TreatmentID + "'";
_table = cgOMS.Prefix + "FEASIBILITY";
_column = "BINARY_CRITERIA";
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(strSelect);
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening Treatment FEASIBILITY table. SQL Message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
_id = row["FEASIBILITYID"].ToString();
Criterias criteria = new Criterias(_table, _column, _id);
byte[] assemblyCriteria = null;
string currentCriteria = "";
if (row["CRITERIA"] != DBNull.Value)
{
currentCriteria = Simulation.ConvertOMSAttribute(row["CRITERIA"].ToString());
}
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(_table, _column, _id, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (criteria.Evaluate.OriginalInput != currentCriteria)
{
criteria.Evaluate = null;
}
}
if (criteria.Evaluate != null && criteria.Evaluate.m_cr != null)
{
if (!File.Exists(criteria.Evaluate.m_cr.PathToAssembly))
{
criteria.Evaluate = null;
}
}
criteria.Criteria = Simulation.ConvertOMSAttribute(row["CRITERIA"].ToString());
foreach (String str in criteria.Errors)
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: Treatment feasibility criteria for Treatment " + _treatment + ":" + str));
}
this.CriteriaList.Add(criteria);
foreach (String str in criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Feasibility criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
return(true);
}
private void btnOk_Click(object sender, EventArgs e)
{
if (myProperties[0].Value.ToString() != "")
{
String strProperty;
String strDataType;
this.DialogResult = DialogResult.OK;
// Now add several default rows to the asset being created in the database as table "ASSET NAME"
m_listTP.Add(new TableParameters("ID", DataType.Int, false, true, true));
m_listTP.Add(new TableParameters("ROUTE", DataType.VarChar(-1), true, false, false));
m_listTP.Add(new TableParameters("BEGIN_STATION", DataType.Float, true, false, false));
m_listTP.Add(new TableParameters("END_STATION", DataType.Float, true, false, false));
m_listTP.Add(new TableParameters("DIRECTION", DataType.VarChar(50), true, false, false));
m_listTP.Add(new TableParameters("FACILITY", DataType.VarChar(-1), true, false, false));
m_listTP.Add(new TableParameters("SECTION", DataType.VarChar(-1), true, false, false));
m_listTP.Add(new TableParameters("ENTRY_DATE", DataType.DateTime, true, false, false));
m_listTP.Add(new TableParameters("LATITUDE", DataType.Float, true, false, false));
m_listTP.Add(new TableParameters("LONGITUDE", DataType.Float, true, false, false));
m_listTP.Add(new TableParameters("GEOMETRY", DataType.VarChar(-1), true, false, false));
m_listTPHistory.Add(new TableParameters("ID", DataType.Int, false, true, true));
m_listTPHistory.Add(new TableParameters("ATTRIBUTE_ID", DataType.Int, false, false, false));
m_listTPHistory.Add(new TableParameters("FIELD", DataType.VarChar(50), false, false, false));
m_listTPHistory.Add(new TableParameters("VALUE", DataType.VarChar(50), true, false, false));
m_listTPHistory.Add(new TableParameters("USER_ID", DataType.VarChar(200), true, false, false));
m_listTPHistory.Add(new TableParameters("WORKACTIVITY", DataType.VarChar(-1), true, false, false));
m_listTPHistory.Add(new TableParameters("WORKACTIVITY_ID", DataType.VarChar(-1), true, false, false));
m_listTPHistory.Add(new TableParameters("DATE_MODIFIED", DataType.DateTime, false, false, false));
for (int i = 1; i < myProperties.Count; i++)
{
strProperty = myProperties[i].Name;
strDataType = myProperties[i].Value.ToString();
DataType dataTypeToInsert = Global.ConvertStringToDataType(strDataType);
// Create the table params list to pass into the DBMgr.CreateTable function.
m_listTP.Add(new TableParameters(strProperty, dataTypeToInsert, false, false));
}
String strInsert;
// Now create the row in the ASSETS tables.
strInsert = "Insert INTO ASSETS (ASSET, DATE_CREATED, CREATOR_ID, LAST_MODIFIED) " +
"Values ('" + myProperties[0].Value
+ "', '" + DateTime.Now
+ "', '" + DBMgr.NativeConnectionParameters.UserName
+ "', '" + DateTime.Now
+ "')";
try
{
DBMgr.ExecuteNonQuery(strInsert);
}
catch (Exception sqlE)
{
Global.WriteOutput("Error: Insert new asset failed. " + sqlE.Message);
return;
}
// Now create the table in the database <asset name>
try
{
DBMgr.CreateTable(myProperties[0].Value.ToString(), m_listTP);
}
catch (FailedOperationException sqlE)
{
Global.WriteOutput("Error: Create asset data table failed. " + sqlE.Message);
return;
}
// Now create the <asset name>_CHANGELOG table for this asset in the database.
try
{
DBMgr.CreateTable(myProperties[0].Value.ToString() + "_" + "CHANGELOG", m_listTPHistory);
}
catch (FailedOperationException sqlE)
{
Global.WriteOutput("Error: Create asset Changelog failed. " + sqlE.Message);
return;
}
}
}
/// <summary>
/// Load all cost information associated with this treatment.
/// </summary>
public bool LoadCost()
{
Costs cost;
String select = "SELECT COST_,UNIT,CRITERIA,BINARY_CRITERIA,ISFUNCTION, COSTID FROM " + cgOMS.Prefix + "COSTS WHERE TREATMENTID='" + this.TreatmentID + "'";
if (SimulationMessaging.IsOMS)
{
select = "SELECT COST_,UNIT,CRITERIA,BINARY_CRITERIA,NULL AS ISFUNCTION, COSTID FROM " + cgOMS.Prefix + "COSTS WHERE TREATMENTID='" + this.TreatmentID + "'";
}
DataSet ds;
try
{
//SimulationMessaging.AddMessage("DEBUGGING: Attempting cost select...");
ds = DBMgr.ExecuteQuery(select);
//SimulationMessaging.AddMessage("DEBUGGING: Cost select successful.");
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening COSTS table. SQL message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["COSTID"].ToString();
cost = new Costs(id);
if (row["CRITERIA"].ToString().Trim().Length == 0)
{
cost.Default = true;
}
else
{
cost.Default = false;
string criteria = row["CRITERIA"].ToString();
byte[] assemblyCriteria = null;
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "COSTS", "BINARY_CRITERIA", cost.CostID, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
cost.Criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (cost.Criteria.Evaluate.OriginalInput != criteria)
{
cost.Criteria.Evaluate = null;
}
}
cost.Criteria.Criteria = criteria;
foreach (String str in cost.Criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Cost criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
byte[] assemblyCost = null;
//objectValue = row["BINARY_COST"];
//if (objectValue != System.DBNull.Value)
//{
// assemblyCost = (byte[])row["BINARY_COST"];
//}
String strCost = row["COST_"].ToString();
assemblyCost = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "COSTS", "BINARY_EQUATION", cost.CostID, assemblyCost);
if (assemblyCost != null && assemblyCost.Length > 0)
{
cost.Calculate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCost);
if (cost.Calculate.OriginalInput != strCost)
{
cost.Calculate = null;
}
}
if (strCost.Trim() == "")
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Cost equation is blank for treatment - " + _treatment));
return(false);
}
bool isFunction = false;
object function = row["ISFUNCTION"];
if (row["ISFUNCTION"] != DBNull.Value)
{
isFunction = Convert.ToBoolean(row["ISFUNCTION"]);
}
if (isFunction)
{
cost.SetFunction(row["COST_"].ToString());
}
else
{
cost.Equation = row["COST_"].ToString();
}
foreach (String str in cost._attributesEquation)
{
if (str != "AREA" && str != "LENGTH")
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Cost equation is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
if (!cost.IsCompoundTreatment)
{
if (cost._calculate.m_listError.Count > 0)
{
foreach (String str in cost.Calculate.m_listError)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Cost equation:" + str));
return(false);
}
}
}
_costs.Add(cost);
}
return(true);
}
public void ProcessRequest(HttpContext context)
{
try
{
dbm = new DBMgr();
string operation = context.Request["op"];
switch (operation)
{
case "Leave_message":
Leave_message(context);
break;
case "Apply_job":
Apply_job(context);
break;
case "get_menu_details":
get_menu_details(context);
break;
case "get_newitems":
get_newitems(context);
break;
default:
var jsonString = String.Empty;
context.Request.InputStream.Position = 0;
using (var inputStream = new StreamReader(context.Request.InputStream))
{
jsonString = HttpUtility.UrlDecode(inputStream.ReadToEnd());
}
if (jsonString != "")
{
var js = new JavaScriptSerializer();
GetJsonData obj = js.Deserialize <GetJsonData>(jsonString);
switch (obj.op)
{
//case "student_attendance_save":
// student_attendance_save(jsonString, context);
// break;
}
}
else
{
var js = new JavaScriptSerializer();
var title1 = context.Request.Params[1];
GetJsonData obj = js.Deserialize <GetJsonData>(title1);
switch (obj.op)
{
}
}
break;
}
}
catch (Exception ex)
{
string response = GetJson(ex.Message);
context.Response.Write(response);
}
}
/// <summary>
/// Load all consequence information associated with treat.
/// </summary>
public bool LoadConsequences()
{
Consequences consequence;
String select = "SELECT ATTRIBUTE_,CHANGE_,CRITERIA,EQUATION,ISFUNCTION, CONSEQUENCEID FROM " + cgOMS.Prefix + "CONSEQUENCES WHERE TREATMENTID='" + _treatmentID + "'";
if (SimulationMessaging.IsOMS)
{
select = "SELECT ATTRIBUTE_,CHANGE_,CRITERIA,EQUATION,NULL AS ISFUNCTION, CONSEQUENCEID FROM " + cgOMS.Prefix + "CONSEQUENCES WHERE TREATMENTID='" + _treatmentID + "'";
}
DataSet ds;
try
{
ds = DBMgr.ExecuteQuery(select);
}
catch (Exception exception)
{
SimulationMessaging.AddMessage(new SimulationMessage("Fatal Error: Opening CONSEQUENCES table. SQL Message - " + exception.Message));
return(false);
}
foreach (DataRow row in ds.Tables[0].Rows)
{
string id = row["CONSEQUENCEID"].ToString();
consequence = new Consequences(id);
String strAttribute = row["ATTRIBUTE_"].ToString();
String strChange = row["CHANGE_"].ToString();
String strCriteria = row["CRITERIA"].ToString();
String strEquation = row["EQUATION"].ToString();
consequence.TreatmentID = this.TreatmentID;
consequence.Treatment = this.Treatment;
if (strCriteria.Trim().Length == 0)
{
consequence.Default = true;
}
else
{
consequence.Default = false;
byte[] assemblyCriteria = null;
assemblyCriteria = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "CONSEQUENCES", "BINARY_CRITERIA", id, assemblyCriteria);
if (assemblyCriteria != null && assemblyCriteria.Length > 0)
{
consequence.Criteria.Evaluate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assemblyCriteria);
if (consequence.Criteria.Evaluate.OriginalInput != strCriteria)
{
consequence.Criteria.Evaluate = null;
}
}
consequence.Criteria.Criteria = strCriteria;
//Get attributes from consequence criteria
foreach (String str in consequence.Criteria.CriteriaAttributes)
{
if (!SimulationMessaging.IsAttribute(str))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + str + " which is used by the Consequence criteria is not present in the database."));
}
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
if (string.IsNullOrWhiteSpace(strEquation.Trim()))
{
consequence.IsEquation = false;;
}
else
{
byte[] assembly = SimulationMessaging.GetSerializedCalculateEvaluate(cgOMS.Prefix + "CONSEQUENCES", "BINARY_EQUATION", id, null);
if (assembly != null && assembly.Length > 0)
{
consequence._calculate = (CalculateEvaluate.CalculateEvaluate)AssemblySerialize.DeSerializeObjectFromByteArray(assembly);
if (consequence._calculate.OriginalInput != strEquation)
{
consequence._calculate = null;
}
}
bool isFunction = false;
if (row["ISFUNCTION"] != DBNull.Value)
{
isFunction = Convert.ToBoolean(row["ISFUNCTION"]);
}
if (isFunction)
{
consequence.SetFunction(strEquation);
}
else
{
consequence.Equation = strEquation;
}
//Get attributes from consequence criteria
foreach (string attribute in consequence._attributesEquation)
{
if (!SimulationMessaging.IsAttribute(attribute))
{
SimulationMessaging.AddMessage(new SimulationMessage("Error: " + attribute + " which is used by the Consequence criteria is not present in the database."));
}
if (!_attributes.Contains(attribute))
{
_attributes.Add(attribute);
}
}
}
consequence.LoadAttributeChange(strAttribute, strChange);
ConsequenceList.Add(consequence);
// Get attributes from Attribute change
foreach (String str in consequence.Attributes)
{
if (!_attributes.Contains(str))
{
_attributes.Add(str);
}
}
}
return(true);
}
public IntroDataSource(DBMgr dbMgr)
{
}
public bool UpdateAttributeView(List <String> listNew, bool bReadCurrentColumns)
{
if (bReadCurrentColumns)
{
m_listColumns.Clear();
foreach (DataGridViewColumn col in dgvAttributeView.Columns)
{
if (col.HeaderText != "FACILITY" && col.HeaderText != "SECTION" && col.HeaderText != "BEGIN_STATION" && col.HeaderText != "END_STATION" && col.HeaderText != "DIRECTION")
{
m_listColumns.Add(col.HeaderText);
}
}
}
//dsmelser 2008.08.01
//Clear icons
SetAllAttributeIconsInactive();
treeNodeTags.Clear();
foreach (String str in listNew)
{
if (!m_listColumns.Contains(str))
{
m_listColumns.Add(str);
}
}
foreach (String str in m_listColumns)
{
//dsmelser 2008.08.01
//Store the appropriate Tags to change the correct icons
int divideIndex = str.LastIndexOf('_');
int throwaway;
if (divideIndex >= 0)
{
string yearName = str.Substring(divideIndex + 1);
if (Int32.TryParse(yearName, out throwaway))
{
string attributeName = str.Substring(0, str.Length - yearName.Length - 1);
treeNodeTags.Add(m_strNetworkID + " " + attributeName + " " + yearName);
}
else
{
treeNodeTags.Add(m_strNetworkID + " " + str + " " + str);
}
}
else
{
treeNodeTags.Add(m_strNetworkID + " " + str + " " + str);
}
}
SetAllAttributeIconsActive();
String strSelect;
if (checkMilepost.Checked)
{
strSelect = "SELECT FACILITY,SECTION,BEGIN_STATION,END_STATION,DIRECTION";
}
else
{
strSelect = "SELECT FACILITY,SECTION";
}
String strSimulation = toolStripComboBoxSimulation.Text;
String strSimulationID = "";
if (strSimulation != "")
{
strSimulationID = m_hashSimulationID[strSimulation].ToString();
}
List <string> listColumns = new List <string>();
string simulationTable = "SIMULATION_" + m_strNetworkID.ToString() + "_" + strSimulationID.ToString();
if (!string.IsNullOrWhiteSpace(strSimulationID))
{
listColumns = DBMgr.GetTableColumns(simulationTable);
}
////Then each of the input columns
foreach (String str in m_listColumns)
{
strSelect += ",";
if (listColumns.Contains(str))
{
strSelect += simulationTable + ".";
}
strSelect += str;
}
String strFrom = DBOp.BuildFromStatement(m_strNetworkID, strSimulationID, true);
strSelect += strFrom;
String strWhere = "";
String strSearch = textBoxAdvanceSearch.Text;
strSearch = strSearch.Trim();
if (comboBoxRouteFacilty.Text != "All")
{
strWhere = " WHERE FACILITY='" + comboBoxRouteFacilty.Text + "'";
strSelect += strWhere;
}
if (strSearch != "")
{
strSelect += " AND ";
strSelect += "(" + strSearch + ")";
}
if (checkBoxCustomFilter.Checked)
{
if (comboBoxFilterAttribute.Text.Trim() != "")
{
if (comboBoxAttributeValue.Text.Trim() != "All")
{
strSelect += " AND ";
strSelect += "(" + comboBoxFilterAttribute.Text.Trim() + " = '" + comboBoxAttributeValue.Text.Trim() + "')";
}
}
}
// SELECT field1, field2, field3
// FROM first_table
// INNER JOIN second_table
// ON first_table.keyfield = second_table.foreign_keyfield
if (dataAdapter != null)
{
dataAdapter.Dispose(); // Free up the resources
}
if (binding != null)
{
binding.Dispose();
}
if (table != null)
{
table.Dispose();
}
try
{
binding = new BindingSource();
dataAdapter = new DataAdapter(strSelect);
// Populate a new data table and bind it to the BindingSource.
table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
binding.DataSource = table;
dgvAttributeView.DataSource = binding;
bindingNavigatorAttributeView.BindingSource = binding;
dgvAttributeView.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
for (int i = 0; i < m_listColumns.Count; i++)
{
String strCol = m_listColumns[i].ToString();
if (checkMilepost.Checked)
{
dgvAttributeView.Columns[strCol].DisplayIndex = i + 5;
}
else
{
dgvAttributeView.Columns[strCol].DisplayIndex = i + 2;
}
dgvAttributeView.Columns[strCol].DefaultCellStyle.Format = GetAttributeFormat(strCol);
}
if (dgvAttributeView.Columns["BEGIN_STATION"] != null)
{
dgvAttributeView.Columns["BEGIN_STATION"].DefaultCellStyle.Format = "f2";
}
if (dgvAttributeView.Columns["END_STATION"] != null)
{
dgvAttributeView.Columns["END_STATION"].DefaultCellStyle.Format = "f2";
}
if (checkMilepost.Checked)
{
dgvAttributeView.Columns["FACILITY"].DisplayIndex = 0;
dgvAttributeView.Columns["SECTION"].DisplayIndex = 1;
dgvAttributeView.Columns["BEGIN_STATION"].DisplayIndex = 2;
dgvAttributeView.Columns["END_STATION"].DisplayIndex = 3;
dgvAttributeView.Columns["DIRECTION"].DisplayIndex = 4;
}
else
{
dgvAttributeView.Columns["FACILITY"].DisplayIndex = 0;
dgvAttributeView.Columns["SECTION"].DisplayIndex = 1;
}
//update with validation stuff
UpdateValidation();
}
catch (Exception exception)
{
Global.WriteOutput("Error: Building current attribute view, try re-rolling up the network. " + exception.Message);
return(false);
}
return(true);
}
public static void PasteCommitted(String simulationID)
{
if (String.IsNullOrEmpty(m_strCopyTreatmentSimulationID))
{
return;
}
//Delete existing SIMULATIONID from TREATMENT
String strDelete = "DELETE FROM COMMITTED_ WHERE SIMULATIONID=" + simulationID;
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Overwriting existing committed projects." + exception.Message);
return;
}
String strSelect = "SELECT * FROM COMMITTED_ WHERE SIMULATIONID=" + m_strCopyCommittedSimulationID;
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
int sectionID = Convert.ToInt32(dr["SECTIONID"]);
int years = Convert.ToInt32(dr["YEARS"]);
String treatmentName = dr["TREATMENTNAME"].ToString();
int yearSame = Convert.ToInt32(dr["YEARSAME"]);
int yearAny = Convert.ToInt32(dr["YEARANY"]);
String budget = dr["BUDGET"].ToString();
double cost = Convert.ToDouble(dr["COST_"]);
int commitID = Convert.ToInt32(dr["COMMITID"]);
String insert = "INSERT INTO COMMITTED_ (SIMULATIONID,SECTIONID,YEARS,TREATMENTNAME,YEARSAME,YEARANY, BUDGET,COST_) VALUES('" +
simulationID.ToString() + "','" +
sectionID.ToString() + "','" +
years.ToString() + "','" +
treatmentName + "','" +
yearSame.ToString() + "','" +
yearAny.ToString() + "','" +
budget + "','" +
cost.ToString() + "')";
try
{
DBMgr.ExecuteNonQuery(insert);
}
catch (Exception e)
{
Global.WriteOutput("Error: copying committed projects." + e.Message);
break;
}
String strIdentity;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT TOP 1 IDENT_CURRENT ('COMMITTED_') FROM COMMITTED_";
break;
case "ORACLE":
//strIdentity = "SELECT TREATMENTS_TREATMENTID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT LAST_NUMBER - CACHE_SIZE + 1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TREATMENTS_TREATMENTID_SEQ'";
strIdentity = "SELECT MAX(COMMITID) FROM COMMITTED_";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet dsIdentity = DBMgr.ExecuteQuery(strIdentity);
strIdentity = dsIdentity.Tables[0].Rows[0].ItemArray[0].ToString();
strSelect = "SELECT * FROM COMMIT_CONSEQUENCES WHERE COMMITID =" + commitID;
DataSet dsCommitConsequences = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow drCommitConsequences in dsCommitConsequences.Tables[0].Rows)
{
String attribute = drCommitConsequences["ATTRIBUTE_"].ToString();
String change = drCommitConsequences["CHANGE_"].ToString();
insert = "INSERT INTO COMMIT_CONSEQUENCES (COMMITID,ATTRIBUTE_,CHANGE_) VALUES('" + strIdentity + "','" + attribute + "','" + change + "')";
try
{
DBMgr.ExecuteNonQuery(insert);
}
catch (Exception e)
{
Global.WriteOutput("Error: copying committed projects." + e.Message);
break;
}
}
}
Global.WriteOutput("Cloning committed projects complete!");
}
public FormAttributeView(String strNetwork, Hashtable hashAttributeYear)
{
InitializeComponent();
treeNodeTags = new List <string>();
m_strNetwork = strNetwork;
m_hashAttributeYear = hashAttributeYear;
String strSelect = "SELECT NETWORKID FROM NETWORKS WHERE NETWORK_NAME='" + m_strNetwork + "'";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
m_strNetworkID = ds.Tables[0].Rows[0].ItemArray[0].ToString();
}
catch (Exception e)
{
String strError = "Error in getting NETWORKID for NETWORK_NAME = " + m_strNetwork + ". SQL error = " + e.Message;
Global.WriteOutput(strError);
MessageBox.Show(strError);
}
ToolTip tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonUpdate, "Update data displayed in Attribute View dependent upon selected filters.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonEditColumns, "Change the selected attributes and their display order.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(checkBoxCustomFilter, "Custom filter will limit output to sections identified by the filter");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(buttonAdvancedSearch, "Create custom search which is used in conjunction with other filters.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(textBoxAdvanceSearch, "Current advanced search criteria.");
tip = new ToolTip();
tip.InitialDelay = 1000;
tip.ReshowDelay = 500;
// Force the ToolTip text to be displayed whether or not the form is active.
tip.ShowAlways = true;
tip.SetToolTip(comboBoxRouteFacilty, "Limit Attribute View to Facility selected.");
}
public static void PastePerformance(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyPerformanceSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
String strDelete = "DELETE FROM PERFORMANCE WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(strDelete);
String strSelect = "SELECT * FROM PERFORMANCE WHERE SIMULATIONID=" + m_strCopyPerformanceSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strEquationName = dr["EQUATIONNAME"].ToString();
String strCritera = dr["CRITERIA"].ToString();
String strEquations = dr["EQUATION"].ToString();
String strShift = dr["SHIFT"].ToString();
string strPiecewise = dr["PIECEWISE"].ToString();
string isFunction = "0";
bool bIsFunction = false;
if (dr["ISFUNCTION"] != DBNull.Value)
{
bIsFunction = Convert.ToBoolean(dr["ISFUNCTION"]);
}
if (bIsFunction)
{
isFunction = "1";
}
String strInsert = "";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
case "ORACLE":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
default:
throw new NotImplementedException("TODO: Implement ANSI version of PastePerformance()");
//break;
}
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copy PERFORMANCE table." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Performance equations successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Performance equations from one simulation to another" + exception.Message);
}
}
public void Init()
{
dbMgr = DBMgr.Instance;
Common.Log("CacheSvc Init Done.");
}
public static void PasteTreatment(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyTreatmentSimulationID))
{
return;
}
//Delete existing SIMULATIONID from TREATMENT
String strDelete = "DELETE FROM TREATMENTS WHERE SIMULATIONID=" + strNewSimulationID;
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Overwriting existig treatment." + exception.Message);
return;
}
var copyFromTreatmentHash = new Hashtable();
String strSelect = "SELECT * FROM TREATMENTS WHERE SIMULATIONID=" + m_strCopyTreatmentSimulationID;
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strTreatmentID = dr["TREATMENTID"].ToString();
String strTreatment = dr["TREATMENT"].ToString();
String strBeforeAny = dr["BEFOREANY"].ToString();
String strBeforeSame = dr["BEFORESAME"].ToString();
String strBudget = dr["BUDGET"].ToString();
String strDescription = dr["DESCRIPTION"].ToString();
Global.WriteOutput("Pasting treatment [" + strTreatment + "]");
String strInsert;
if (strBudget.Trim() == "")
{
strInsert =
"INSERT INTO TREATMENTS (SIMULATIONID,TREATMENT,BEFOREANY,BEFORESAME,DESCRIPTION)VALUES(" +
strNewSimulationID + ",'" + strTreatment + "'," + strBeforeAny + "," + strBeforeSame + ",'" +
strDescription + "')";
}
else
{
strInsert =
"INSERT INTO TREATMENTS (SIMULATIONID,TREATMENT,BEFOREANY,BEFORESAME,BUDGET,DESCRIPTION)VALUES(" +
strNewSimulationID + ",'" + strTreatment + "'," + strBeforeAny + "," + strBeforeSame + ",'" +
strBudget + "','" + strDescription + "')";
}
DBMgr.ExecuteNonQuery(strInsert);
String strIdentity;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('TREATMENTS') FROM TREATMENTS";
break;
case "ORACLE":
//strIdentity = "SELECT TREATMENTS_TREATMENTID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT LAST_NUMBER - CACHE_SIZE + 1 FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TREATMENTS_TREATMENTID_SEQ'";
strIdentity = "SELECT MAX(TREATMENTID) FROM TREATMENTS";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet dsIdentity = DBMgr.ExecuteQuery(strIdentity);
strIdentity = dsIdentity.Tables[0].Rows[0].ItemArray[0].ToString();
copyFromTreatmentHash.Add(strTreatmentID, strIdentity);
Global.WriteOutput("Pasting Feasibility");
DataSet dsFeasibility =
DBMgr.ExecuteQuery("SELECT * FROM FEASIBILITY WHERE TREATMENTID=" + strTreatmentID);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
SQLCopyFeasibility(strIdentity, dsFeasibility);
break;
case "ORACLE":
ORACLECopyFeasibility(strIdentity, dsFeasibility);
break;
default:
throw new NotImplementedException("TODO: implement ANSICopyConsequences()");
//break;
}
Global.WriteOutput("Pasting Costs");
DataSet dsCosts = DBMgr.ExecuteQuery("SELECT * FROM COSTS WHERE TREATMENTID = " + strTreatmentID);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
SQLCopyCosts(strIdentity, dsCosts);
break;
case "ORACLE":
ORACLECopyCosts(strIdentity, dsCosts);
break;
default:
throw new NotImplementedException("TODO: implement ANSICopyConsequences()");
//break;
}
Global.WriteOutput("Pasting Consequences");
DataSet dsConsequences =
DBMgr.ExecuteQuery("SELECT * FROM CONSEQUENCES WHERE TREATMENTID = " + strTreatmentID);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
SQLCopyConsequences(strIdentity, dsConsequences);
break;
case "ORACLE":
ORACLECopyConsequences(strIdentity, dsConsequences);
break;
default:
throw new NotImplementedException("TODO: implement ANSICopyConsequences()");
//break;
}
}
Global.WriteOutput("Pasting Scheduled and Supersedes for all treatments");
foreach (string key in copyFromTreatmentHash.Keys)
{
DataSet dataSetScheduled = DBMgr.ExecuteQuery("SELECT * FROM SCHEDULED WHERE TREATMENTID = " + key);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
SQLCopyScheduled(key, dataSetScheduled, copyFromTreatmentHash);
break;
case "ORACLE":
ORACLECopyScheduled(key, dataSetScheduled);
break;
default:
throw new NotImplementedException("TODO: implement ANSICopyConsequences()");
//break;
}
DataSet dataSetSupersedes = DBMgr.ExecuteQuery("SELECT * FROM SUPERSEDES WHERE TREATMENT_ID = " + key);
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
SQLCopySupersedes(key, dataSetSupersedes, copyFromTreatmentHash);
break;
case "ORACLE":
ORACLECopySupersedes(key, dataSetSupersedes);
break;
default:
throw new NotImplementedException("TODO: implement ANSICopyConsequences()");
//break;
}
}
}
public string Save()
{
string filedata = Request["filedata"];
string action = Request["action"];
JObject json = (JObject)JsonConvert.DeserializeObject(Request["formdata"]);
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string sql = "";
string ordercode = string.Empty; bool IsSubmitAfterSave = false;
if (Request["action"] + "" == "submit")
{
json.Remove("STATUS"); json.Remove("SUBMITTIME"); json.Remove("SUBMITUSERNAME"); json.Remove("SUBMITUSERID");
json.Add("STATUS", 10);
json.Add("SUBMITTIME", "sysdate");
json.Add("SUBMITUSERNAME", json_user.Value <string>("REALNAME"));
json.Add("SUBMITUSERID", json_user.Value <string>("ID"));
}
else
{
if (string.IsNullOrEmpty(json.Value <string>("SUBMITTIME"))) //有可能提交以后再对部分字段进行修改后保存
{
json.Remove("SUBMITTIME"); //委托时间 因为该字段需要取ORACLE的时间，而非系统时间 所以需要特殊处理,格式化时并没有加引号
json.Add("SUBMITTIME", "null");
}
else
{
string submittime = json.Value <string>("SUBMITTIME");
json.Remove("SUBMITTIME");//委托时间 因为该字段需要取ORACLE的时间，而非系统时间 所以需要特殊处理
json.Add("SUBMITTIME", "to_date('" + submittime + "','yyyy-MM-dd HH24:mi:ss')");
IsSubmitAfterSave = true;
}
}
if (json.Value <string>("ENTRUSTTYPE") == "01")
{
json.Add("DECLSTATUS", json.Value <string>("STATUS")); json.Add("INSPSTATUS", null);
}
if (json.Value <string>("ENTRUSTTYPE") == "02")
{
json.Add("DECLSTATUS", null); json.Add("INSPSTATUS", json.Value <string>("STATUS"));
}
if (json.Value <string>("ENTRUSTTYPE") == "03")
{
json.Add("DECLSTATUS", json.Value <string>("STATUS")); json.Add("INSPSTATUS", json.Value <string>("STATUS"));
}
if (string.IsNullOrEmpty(json.Value <string>("CODE")))//新增
{
ordercode = Extension.getOrderCode();
sql = @"INSERT INTO LIST_ORDER (ID,
BUSITYPE,CODE,CUSNO,BUSIUNITCODE,BUSIUNITNAME,CONTRACTNO
,TOTALNO,DIVIDENO,TURNPRENO,GOODSNUM,WOODPACKINGID
,CLEARANCENO,LAWFLAG,ENTRUSTTYPE,REPWAYID,CUSTOMAREACODE
,REPUNITCODE,REPUNITNAME,DECLWAY,PORTCODE,INSPUNITCODE
,INSPUNITNAME,ORDERREQUEST,CREATEUSERID,CREATEUSERNAME,STATUS
,SUBMITUSERID,SUBMITUSERNAME,CUSTOMERCODE,CUSTOMERNAME,DECLCARNO
,TRADEWAYCODES,GOODSGW,GOODSNW,PACKKIND,BUSIKIND
,ORDERWAY,CLEARUNIT,CLEARUNITNAME,CREATETIME,SPECIALRELATIONSHIP,PRICEIMPACT
,PAYPOYALTIES,SUBMITTIME,DECLSTATUS,INSPSTATUS,DOCSERVICECODE
) VALUES (LIST_ORDER_id.Nextval
,'{0}','{1}','{2}','{3}','{4}','{5}'
,'{6}','{7}','{8}','{9}','{10}'
,'{11}','{12}','{13}','{14}','{15}'
,'{16}','{17}','{18}','{19}','{20}'
,'{21}','{22}','{23}','{24}','{25}'
,'{26}','{27}','{28}','{29}','{30}'
,'{31}','{32}','{33}','{34}','{35}'
,'{36}','{37}','{38}',sysdate,'{39}','{40}'
,'{41}',{42},'{43}','{44}','{45}'
)";
sql = string.Format(sql
, "11", ordercode, json.Value <string>("CUSNO"), json.Value <string>("BUSIUNITCODE"), json.Value <string>("BUSIUNITNAME"), json.Value <string>("CONTRACTNO")
, json.Value <string>("TOTALNO"), json.Value <string>("DIVIDENO"), json.Value <string>("TURNPRENO"), json.Value <string>("GOODSNUM"), json.Value <string>("WOODPACKINGID")
, json.Value <string>("CLEARANCENO"), GetChk(json.Value <string>("LAWFLAG")), json.Value <string>("ENTRUSTTYPE"), json.Value <string>("REPWAYID"), json.Value <string>("CUSTOMAREACODE")
, GetCode(json.Value <string>("REPUNITCODE")), GetName(json.Value <string>("REPUNITCODE")), json.Value <string>("DECLWAY"), json.Value <string>("PORTCODE"), GetCode(json.Value <string>("INSPUNITCODE"))
, GetName(json.Value <string>("INSPUNITCODE")), json.Value <string>("ORDERREQUEST"), json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"), json.Value <string>("STATUS")
, json.Value <string>("SUBMITUSERID"), json.Value <string>("SUBMITUSERNAME"), json_user.Value <string>("CUSTOMERCODE"), json_user.Value <string>("CUSTOMERNAME"), json.Value <string>("DECLCARNO")
, json.Value <string>("TRADEWAYCODES"), json.Value <string>("GOODSGW"), json.Value <string>("GOODSNW"), json.Value <string>("PACKKIND"), "001"
, "1", json_user.Value <string>("CUSTOMERCODE"), json_user.Value <string>("CUSTOMERNAME"), GetChk(json.Value <string>("SPECIALRELATIONSHIP")), GetChk(json.Value <string>("PRICEIMPACT"))
, GetChk(json.Value <string>("PAYPOYALTIES")), json.Value <string>("SUBMITTIME"), json.Value <string>("DECLSTATUS"), json.Value <string>("INSPSTATUS"), json.Value <string>("DOCSERVICECODE")
);
}
else//修改
{
ordercode = json.Value <string>("CODE");
/* sql = @"UPDATE LIST_ORDER
* SET BUSITYPE='{1}',CUSNO='{2}',BUSIUNITCODE='{3}',BUSIUNITNAME='{4}',CONTRACTNO='{5}'
* ,TOTALNO='{6}',DIVIDENO='{7}',TURNPRENO='{8}',GOODSNUM='{9}',WOODPACKINGID='{10}'
* ,CLEARANCENO='{11}',LAWFLAG='{12}',ENTRUSTTYPE='{13}',REPWAYID='{14}',CUSTOMAREACODE='{15}'
* ,REPUNITCODE='{16}',REPUNITNAME='{17}',DECLWAY='{18}',PORTCODE='{19}',INSPUNITCODE='{20}'
* ,INSPUNITNAME='{21}',ORDERREQUEST='{22}',STATUS='{23}',SUBMITUSERID='{24}',SUBMITUSERNAME='******'
* ,CUSTOMERCODE='{26}',CUSTOMERNAME='{27}',DECLCARNO='{28}',TRADEWAYCODES='{29}',GOODSGW='{30}'
* ,GOODSNW='{31}',PACKKIND='{32}',BUSIKIND='{33}',ORDERWAY='{34}',CLEARUNIT='{35}'
* ,CLEARUNITNAME='{36}',SPECIALRELATIONSHIP='{37}', PRICEIMPACT='{38}',PAYPOYALTIES='{39}',SUBMITTIME={40},DOCSERVICECODE='{41}'
* ";
*
* if (IsSubmitAfterSave == false)//提交之后保存，就不更新报关报检状态；
* {
* sql += @",DECLSTATUS='{42}',INSPSTATUS='{43}'";
* }
* sql += @" WHERE CODE = '{0}'";
*/
string allcol = @"CODE
,BUSITYPE,CUSNO,BUSIUNITCODE,BUSIUNITNAME,CONTRACTNO
,TOTALNO,DIVIDENO,TURNPRENO,GOODSNUM,WOODPACKINGID
,CLEARANCENO,LAWFLAG,ENTRUSTTYPE,REPWAYID,CUSTOMAREACODE
,REPUNITCODE,REPUNITNAME,DECLWAY,PORTCODE,INSPUNITCODE
,INSPUNITNAME,ORDERREQUEST,STATUS,SUBMITUSERID,SUBMITUSERNAME
,CUSTOMERCODE,CUSTOMERNAME,DECLCARNO,TRADEWAYCODES,GOODSGW
,GOODSNW,PACKKIND,BUSIKIND,ORDERWAY,CLEARUNIT
,CLEARUNITNAME,SPECIALRELATIONSHIP, PRICEIMPACT,PAYPOYALTIES,SUBMITTIME
,DOCSERVICECODE,DECLSTATUS,INSPSTATUS
";
sql = Extension.getUpdateSql(allcol, ordercode, IsSubmitAfterSave);
if (sql != "")
{
sql = string.Format(sql
, ordercode, "11", json.Value <string>("CUSNO"), json.Value <string>("BUSIUNITCODE"), json.Value <string>("BUSIUNITNAME"), json.Value <string>("CONTRACTNO")
, json.Value <string>("TOTALNO"), json.Value <string>("DIVIDENO"), json.Value <string>("TURNPRENO"), json.Value <string>("GOODSNUM"), json.Value <string>("WOODPACKINGID")
, json.Value <string>("CLEARANCENO"), GetChk(json.Value <string>("LAWFLAG")), json.Value <string>("ENTRUSTTYPE"), json.Value <string>("REPWAYID"), json.Value <string>("CUSTOMAREACODE")
, GetCode(json.Value <string>("REPUNITCODE")), GetName(json.Value <string>("REPUNITCODE")), json.Value <string>("DECLWAY"), json.Value <string>("PORTCODE"), GetCode(json.Value <string>("INSPUNITCODE"))
, GetName(json.Value <string>("INSPUNITCODE")), json.Value <string>("ORDERREQUEST"), json.Value <string>("STATUS"), json.Value <string>("SUBMITUSERID"), json.Value <string>("SUBMITUSERNAME")
, json_user.Value <string>("CUSTOMERCODE"), json_user.Value <string>("CUSTOMERNAME"), json.Value <string>("DECLCARNO"), json.Value <string>("TRADEWAYCODES"), json.Value <string>("GOODSGW")
, json.Value <string>("GOODSNW"), json.Value <string>("PACKKIND"), "001", "1", json_user.Value <string>("CUSTOMERCODE")
, json_user.Value <string>("CUSTOMERNAME"), GetChk(json.Value <string>("SPECIALRELATIONSHIP")), GetChk(json.Value <string>("PRICEIMPACT")), GetChk(json.Value <string>("PAYPOYALTIES")), json.Value <string>("SUBMITTIME")
, json.Value <string>("DOCSERVICECODE"), json.Value <string>("DECLSTATUS"), json.Value <string>("INSPSTATUS")
);
}
}
if (sql != "")
{
int result = DBMgr.ExecuteNonQuery(sql);
if (result == 1)
{
//集装箱及报关车号列表更新
Extension.predeclcontainer_update(ordercode, json.Value <string>("CONTAINERTRUCK"));
//更新随附文件
Extension.Update_Attachment(ordercode, filedata, json.Value <string>("ORIGINALFILEIDS"), json_user);
//插入订单状态变更日志
Extension.add_list_time(json.Value <Int32>("STATUS"), ordercode, json_user);
if (json.Value <Int32>("STATUS") > 10)
{
Extension.Insert_FieldUpdate_History(ordercode, json, json_user, "11");
}
return("{success:true,ordercode:'" + ordercode + "'}");
}
else
{
return("{success:false}");
}
}
else
{
return("{success:false}");
}
}
