public DataReader(UIControls ui, DBConnect db, DataGridView gridDay, DataGridView gridPeriod) { _ui = ui; _db = db; _gridDay = gridDay; _gridPeriod = gridPeriod; }
//根据控件组合拼接sql public void ConcatSQLByUIControls(UIControls ui) { _sql = new StringBuilder(); foreach (Control c in ui.GroupBoxProductDataSource.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { _dataSource = rb.Text; switch (rb.Text) { case "ISCCP D2": { ConcatISCCPSQL(ui); return; } default: break; } } } ConcatSQLByProductPeriod(ui); //周期类型 ConcatSQLByProductDataSource(ui); //数据来源-传感器 ConcatSQLByProductType(ui); //产品类型 ConcatSQLByDataSet(ui); //数据集ID ConcatSQLByDayNight(ui); //日夜标识 AppendSQLByProductPeriod(ui); ConcatSQLByProductTime(ui); //连续或同期查询 }
//拼接数据集sql,并记录数据集 private void ConcatSQLByDataSet(UIControls ui) { DataSetTableMapping mapping = GetTableNameByDataSource(ui); foreach (Control c in ui.GroupBoxDataSet.Controls) { ucCheckBoxList uccbl = c as ucCheckBoxList; if (uccbl != null) { //_sql.Append("("); bool checkedPrevious = false; foreach (CheckBox cb in uccbl) { if (cb.Checked) { StringBuilder sql = new StringBuilder(); //sql.Append("DataSetID=(select ").Append(mapping.DataSetIDFieldName) // .Append(" from ").Append(mapping.DatabaseTableName) // .Append(" where ").Append(mapping.DataSetLabelFieldName).Append("='").Append(cb.Text).Append("')"); sql.Append("DataSetID='").Append(cb.Tag.ToString()).Append("'"); AppendOr(ref checkedPrevious, cb.Checked, sql.ToString()); } } _sql.Append(" and "); break; } } }
//拼接产品数据源sql,并记录产品数据源 private void ConcatSQLByProductDataSource(UIControls ui) { foreach (Control c in ui.GroupBoxProductDataSource.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { _dataSource = rb.Text; switch (rb.Text) { case "MOD06": { _sql.Append("SensorType='MODIS' and "); break; } case "AIRS": { _sql.Append("SensorType='AIRS' and "); break; } case "ISCCP D2": { _sql.Append("SensorType='ISCCP' and "); break; } } break; } } }
public static UIControls GetInstance() { if (_instance == null) { _instance = new UIControls(); } return(_instance); }
//记录连续查询、同期查询 private void RecordSearchType(UIControls ui) { foreach (Control c in ui.GroupBoxProductTime.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { _searchType = rb.Text; break; } } }
private void ConcatISCCPSQL(UIControls ui) { //_sql.Append("select KeyWords,Resolution, DataYear, DataMonth, Data10Day,ImageData, ValidValuePercent, statTypeID from cp_periodicsynthesis_tb where PeriodTypeID=4 and "); _sql.Append("select KeyWord as KeyWords,Resolution, YEAR(ReceiveTime) as DataYear, MONTH(ReceiveTime) as DataMonth,null as Data10Day, ImageData,3 as statTypeID from cp_isccpd2_tb where ReceiveUTCTime=9999 and ");//MONTH(ReceiveTime)=12;"); RecordSearchType(ui); RecordDateTime(ui); foreach (Control c in ui.GroupBoxProductPeriod.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { _productPeriod = rb.Text; } } switch (_searchType) { case "连续查询": { ConcatISCCPSQLByProductTimeContinue(); break; } case "同期查询": { ConcatISCCPSQLByProductTimeOverTheSamePeriod(ui); break; } } #region 波段号 foreach (Control c in ui.GroupBoxDataSet.Controls) { ucCheckBoxList uccbl = c as ucCheckBoxList; if (uccbl != null) { foreach (CheckBox cb in uccbl) { if (cb.Checked) { int isccpband = int.Parse(cb.Tag.ToString()); } } break; } } #endregion }
private DataSetTableMapping GetTableNameByDataSource(UIControls ui) { DataSetTableMapping mapping = new DataSetTableMapping(); foreach (Control c in ui.GroupBoxProductDataSource.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { switch (rb.Text) { case "MOD06": { mapping.DatabaseTableName = "cp_cldprds2mod06sets_tb"; mapping.DataSetIDFieldName = "Prds2MOD06ID"; mapping.DataSetNameFieldName = "MOD06DataSetName"; mapping.DataSetLabelFieldName = "MOD06SetsLabel"; break; } case "AIRS": { mapping.DatabaseTableName = "cp_cldprds2airssets_tb"; mapping.DataSetIDFieldName = "Prds2AIRSID"; mapping.DataSetNameFieldName = "AIRSDataSetName"; mapping.DataSetLabelFieldName = "AIRSSetsLabel"; break; } case "ISCCP D2": { mapping.DatabaseTableName = "cp_cldprds2isccpbands_tb"; mapping.DataSetIDFieldName = "Prds2CloudSATID"; mapping.DataSetNameFieldName = "CloudSATDataSetName"; mapping.DataSetLabelFieldName = "CloudSATDataSetLabel"; break; } } break; } } return(mapping); }
//拼接产品时间sql,并记录连续查询、同期查询和开始日期、结束日期 private void ConcatSQLByProductTime(UIControls ui) { RecordSearchType(ui); RecordDateTime(ui); switch (_searchType) { case "连续查询": { ConcatSQLByProductTimeContinue(); break; } case "同期查询": { ConcatSQLByProductTimeOverTheSamePeriod(ui); break; } } }
//拼接产品周期sql,并记录产品周期 private void ConcatSQLByProductPeriod(UIControls ui) { foreach (Control c in ui.GroupBoxProductPeriod.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { _productPeriod = rb.Text; switch (rb.Text) { case "日产品": { _sql.Append("select DATE(DataTime) as DataTime, KeyWords,Resolution,ValidValuePercent, GranuleCounts, GranuleTimes, ImageData from cp_daymergeproducts_tb where "); //_sql.Append("select * from cp_daymergeproducts_tb where "); break; } case "旬产品": { //_sql.Append("select KeyWords,Resolution, DataYear, DataMonth, Data10Day,ImageData, ValidValuePercent, statTypeID from cp_periodicsynthesis_tb where PeriodTypeID=3 and "); _sql.Append("select* from cp_periodicsynthesis_tb where PeriodTypeID=3 and "); break; } case "月产品": { //_sql.Append("select KeyWords,Resolution, DataYear, DataMonth, Data10Day,ImageData, ValidValuePercent, statTypeID from cp_periodicsynthesis_tb where PeriodTypeID=4 and "); _sql.Append("select * from cp_periodicsynthesis_tb where PeriodTypeID=4 and "); break; } case "年产品": { //_sql.Append("select KeyWords,Resolution, DataYear, DataMonth, Data10Day,ImageData, ValidValuePercent, statTypeID from cp_periodicsynthesis_tb where PeriodTypeID=5 and "); _sql.Append("select * from cp_periodicsynthesis_tb where PeriodTypeID=5 and "); break; } } break; } } }
//拼接产品类型sql,并记录产品类型 private void ConcatSQLByProductType(UIControls ui) { foreach (Control c in ui.GroupBoxProductType.Controls) { ucRadioBoxList ucrbl = c as ucRadioBoxList; if (ucrbl != null) { foreach (RadioButton rb in ucrbl) { if (rb.Checked) { _productType = rb.Text; //_sql.Append("ProductID=(select ProductsID from cp_cloudprds_tb where ProductsComments='").Append(rb.Text).Append("') and "); _sql.Append("ProductID='").Append(rb.Tag.ToString()).Append("' and "); break; } } break; } } }
//记录开始日期、结束日期 private void RecordDateTime(UIControls ui) { foreach (Control c in ui.GroupBoxProductTime.Controls) { DateTimePicker dtp = c as DateTimePicker; if (dtp != null) { switch (dtp.Name) { case "dateTimePickerBegin": { _dateTimeBegin = dtp.Text; break; } case "dateTimePickerEnd": { _dateTimeEnd = dtp.Text; break; } } } } }
//拼接昼夜sql,并记录白天、晚上 private void ConcatSQLByDayNight(UIControls ui) { foreach (Control c in ui.GroupBoxDayNight.Controls) { RadioButton rb = c as RadioButton; if (rb != null && rb.Checked) { switch (rb.Text) { case "白天": { _sql.Append("DataSource='D' and "); break; } case "晚上": { _sql.Append("DataSource='N' and "); break; } } } } }
/// <summary> /// 拼接同期查询sql /// </summary> /// <param name="ui"></param> private void ConcatISCCPSQLByProductTimeOverTheSamePeriod(UIControls ui) { _sql.Append("Year(ReceiveTime)>=").Append(_dateTimeBegin.Split('-')[0]); _sql.Append(" and "); _sql.Append("Year(ReceiveTime)<=").Append(_dateTimeEnd.Split('-')[0]).Append(" and ("); switch (_productPeriod) { case "月产品": { foreach (Control c in ui.GroupBoxProductTime.Controls) { Panel p = c as Panel; if (p != null && p.Name == "panelPrdsTimeMonth") { foreach (Control cc in p.Controls) { ucMonths ucM = cc as ucMonths; if (ucM != null) { bool checkedPrevious = false; int month = 0; foreach (CheckBox cb in ucM) { AppendOr(ref checkedPrevious, cb.Checked, "Month(ReceiveTime)", ++month); } _sql.Append(");"); break; } } break; } } break; } } }
public DataReader(UIControls ui, DBConnect db, DataGridView gridCloudsat) { _ui = ui; _db = db; _gridclousat = gridCloudsat; }
/// <summary> /// 拼接同期查询sql /// </summary> /// <param name="ui"></param> private void ConcatSQLByProductTimeOverTheSamePeriod(UIControls ui) { _sql.Append("DataYear>=").Append(_dateTimeBegin); _sql.Append(" and "); _sql.Append("DataYear<=").Append(_dateTimeEnd).Append(" and ("); switch (_productPeriod) { case "旬产品": { foreach (Control c in ui.GroupBoxProductTime.Controls) { Panel p = c as Panel; if (p != null && p.Name == "panelPrdsTimeMonth") { foreach (Control cc in p.Controls) { ucMonths ucM = cc as ucMonths; if (ucM != null) { bool checkedPrevious = false; int month = 0; foreach (CheckBox cb in ucM) { AppendOr(ref checkedPrevious, cb.Checked, "DataMonth", ++month); } _sql.Append(") and ("); break; } } } else if (p != null && p.Name == "panelPrdsTimeTen") { bool checkedPrevious = false; foreach (Control cc in p.Controls) { CheckBox cb = cc as CheckBox; if (cb != null && cb.Checked) { switch (cb.Text) { case "上旬": { AppendOr(ref checkedPrevious, cb.Checked, "Data10Day", 1); break; } case "中旬": { AppendOr(ref checkedPrevious, cb.Checked, "Data10Day", 2); break; } case "下旬": { AppendOr(ref checkedPrevious, cb.Checked, "Data10Day", 3); break; } } } } //_sql.Append(");"); _sql.Append(");"); } } break; } case "月产品": { foreach (Control c in ui.GroupBoxProductTime.Controls) { Panel p = c as Panel; if (p != null && p.Name == "panelPrdsTimeMonth") { foreach (Control cc in p.Controls) { ucMonths ucM = cc as ucMonths; if (ucM != null) { bool checkedPrevious = false; int month = 0; foreach (CheckBox cb in ucM) { AppendOr(ref checkedPrevious, cb.Checked, "DataMonth", ++month); } //_sql.Append(");"); _sql.Append(");"); break; } } break; } } break; } } }