/// <summary>
/// Gets Crop Dates given a year and group number
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public static CropDatesDataSet.CropDatesDataTable GetCropDataTable(int?year, string cbtt, int?group)
{
CropDatesDataSet.CropDatesDataTable table = new CropDatesDataSet.CropDatesDataTable();
var sql = "select * from cropdates where 1=1 ";
if (year.HasValue)
{
sql += " and year = " + year;
}
if (cbtt != "")
{
sql += " and cbtt = '" + cbtt + "' ";
}
if (group.HasValue)
{
sql += " and \"group\" = " + group;
}
sql += " ORDER BY year,cbtt,SortIndex";
DB.FillTable(table, sql);
SetupIndexColumn(table);
return(table);
}
private void buttonRead_Click(object sender, EventArgs e)
{
try
{
CropDatesDataSet.DB = HydrometTools.Database.GetServer("agrimet");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
bool hasYear = textBoxYear.Text.Trim() != "";
bool hasGroup = textBoxGroup.Text.Trim() != "";
int?yr = null;
if (hasYear)
{
yr = Convert.ToInt32(this.textBoxYear.Text);
}
int?group = null;
if (hasGroup)
{
group = Convert.ToInt32(textBoxGroup.Text);
}
tbl = CropDatesDataSet.GetCropDataTable(yr, textBoxCbtt.Text.ToUpper(), group);
this.spreadsheetControl1.SetDataTable(tbl);
}
/// <summary>
/// Gets Crop Dates given a station and a year - Used for Crop Chart generation
/// </summary>
/// <param name="cbtt"></param>
/// <param name="year"></param>
/// <returns></returns>
public static CropDatesDataSet.CropDatesDataTable GetCropDataTable(int year, string[] cbttList)
{
var table = new CropDatesDataSet.CropDatesDataTable();
if (cbttList.Length == 0)
return table;// nothing to do.
var sql = "select * from CropDates WHERE year = " + year
+ " and cbtt IN ('" + String.Join("','", cbttList) + "') order by CBTT,SortIndex ";
DB.FillTable(table,sql );
SetupIndexColumn(table);
return table;
}
/// <summary>
/// Gets Crop Dates given a year , used by user interface to edit crops
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public static CropDatesDataSet.CropDatesDataTable GetCropDataTable(int year, bool includeEmptyDates = true)
{
CropDatesDataSet.CropDatesDataTable table = new CropDatesDataSet.CropDatesDataTable();
var sql = "select * from CropDates where year = " + year;
if (!includeEmptyDates)
{
sql += " and StartDate is not null and FullCoverDate is not null and TerminateDate is not null";
}
sql += " ORDER BY cbtt,SortIndex";
DB.FillTable(table, sql);
//SQL search for Year and CBTT from the Access DB
SetupIndexColumn(table);
return(table);
}
/// <summary>
/// Copy a new year's worth of Crop Dates from the previous years data
/// Designed to run for the current year so run on 1/1/(CurrentYear) or later
/// </summary>
/// <param name="year"></param>
public static void InitializeYear(int year)
{
if (DateTime.Now.Year != year)
{
throw new ArgumentException("You can't initialize years other than the current one!");
}
else
{
var prevYearTable = new CropDatesDataSet.CropDatesDataTable();
DB.FillTable(prevYearTable, "SELECT * FROM CropDates WHERE year = " + (year - 1).ToString());
var newYearTable = new CropDatesDataSet.CropDatesDataTable();
DB.FillTable(newYearTable, "SELECT * FROM CropDates WHERE year = " + year.ToString());
if (newYearTable.Rows.Count != 0)
{
throw new Exception("Error: there is existing data for year " + year);
}
int nextIndex = CropDatesDataSet.GetNextIndex();
var rowCount = prevYearTable.Count;
for (int i = 0; i < rowCount; i++)
{
var row = prevYearTable[i];
var newRow = newYearTable.NewCropDatesRow();
newRow.index = nextIndex;
newRow.year = year;
newRow.sortindex = row.sortindex;
newRow.group = row.group;
newRow.cbtt = row.cbtt;
newRow.cropname = row.cropname;
//newRow.UIDX = row.UIDX;
newRow.cropcurvenumber = row.cropcurvenumber;
newYearTable.Rows.Add(newRow);
nextIndex++;
}
DB.SaveTable(newYearTable);
}
}
/// <summary>
/// Creates Daily and Summary Crop Reports
/// </summary>
/// <param name="cbttList">list of cbtt to create charts for</param>
/// <param name="year">year used to filter database of crop dates</param>
/// <param name="date">terminate date (or if null use etr terminate date)</param>
public static void CreateCropReports(int year, string outputDirectory)
{
CropDatesDataSet.CropDatesDataTable cropTable = new CropDatesDataSet.CropDatesDataTable();
cropTable = CropDatesDataSet.GetCropDataTable(year, false);
var cbttList = (from row in cropTable.AsEnumerable() // get list of cbtt
select row.cbtt).Distinct().ToArray();
if (cbttList.Length > 1)
{// for performance query hydromet only once.
var cache = new HydrometDataCache();
// boii, abei
// boii et, abei et
var s = String.Join(" ETRS,", cbttList) + " ETRS";
var cbttPcode = s.Split(',');
DateTime t1 = new DateTime(year, 1, 1);
DateTime t2 = new DateTime(year, 12, 31);
if (t2 > DateTime.Now.Date)
{
t2 = DateTime.Now.Date;
}
cache.Add(cbttPcode, t1, t2, HydrometHost.PN, TimeSeries.TimeInterval.Daily);
HydrometDailySeries.Cache = cache;;
}
DateTime t = DateTime.Now.Date;
for (int i = 0; i < cbttList.Length; i++)
{
var cropDates = cropTable.Where(x => x.cbtt == cbttList[i] &&
!x.IsterminatedateNull() &&
!x.IsstartdateNull() &&
!x.IsfullcoverdateNull()).ToArray();
if (cropDates.Length == 0)
{
continue;
}
ValidateCropDates(cbttList[i], cropDates);
var terminateDate = TerminateDate(cbttList[i], cropDates);
if (terminateDate < DateTime.Now)
{
t = terminateDate.AddDays(1);
}
else
{
t = DateTime.Now.Date;
}
// Generates Daily and Summary Crop Charts
var dailyChart = CreateDailyReport(cbttList[i], t, cropDates);
var sumChart = CreateSummaryReport(cbttList[i], t, cropDates);
//string header = "AgriMet is excited to announce a partnership with Washington State University to icorporate AgriMet data into WSU's Irrigation Scheduler. To customize crop consumptive water use specific to your field or fields, visit http://weather.wsu.edu/is/";
var fnDaily = Path.Combine(outputDirectory, cbttList[i].ToLower() + "ch.txt");
var fnSum = Path.Combine(outputDirectory, cbttList[i].ToLower() + t.Year.ToString().Substring(2) + "et.txt");
WriteCropFile(dailyChart, fnDaily);
WriteCropFile(sumChart, fnSum);
}
Console.WriteLine(" Daily and Summary Crop Charts Saved for " + cbttList.Length + " sites");
}
/// <summary>
/// Creates Daily Crop Water Use Charts
/// </summary>
/// <param name="cbtt"></param>
/// <param name="t"></param>
/// <returns></returns>
private static void CreateDailyHTMLWebReport(string cbtt, DateTime t,
CropDatesDataSet.CropDatesRow[] cropRow)
{
CropDatesDataSet.CropDatesDataTable cropTable = new CropDatesDataSet.CropDatesDataTable();
cropTable = CropDatesDataSet.GetCropDataTable(t.Year, false);
var agrimetDir = ConfigurationManager.AppSettings["AgriMetCropOutputDirectory"];
string html_file = Path.Combine(agrimetDir, "chart", "agricultureEmail-V2.html");
if (!File.Exists(html_file))
{
Logger.WriteLine("Error: missing file :" + html_file);
return;
}
//var fn = Path.Combine(agrimetDir, "chart", html_file);
var contents = File.ReadAllText(html_file);
var outputfn = Path.Combine(agrimetDir, "chart", t.Year.ToString(), (cbtt + ".html"));
var et = new HydrometDailySeries(cbtt, "ETRS", s_host);
// Below is the calculation to determine how many days to read back. Set to calculate based on ETr Start Date.
var etStartDate = cropRow[0].startdate.DayOfYear;
var etTodayDate = t.DayOfYear;
int numDaysRead = etTodayDate - etStartDate - 1;
et.Read(t.AddDays(-numDaysRead), t.AddDays(-1));
var pcode = new string[] { "MX", "MN", "PP", "TA", "UA", "WG" };
var htmlPcode = new string[] {
"{site_high_temp}",
"{site_low_temp}",
"{site_precip}",
"{site_humidity}",
"{site_wind}",
"{site_max_wind}"
};
DateTime yesterday = DateTime.Now.Date.AddDays(-1);
for (int i = 0; i < pcode.Length; i++)
{
var s = new HydrometDailySeries(cbtt, pcode[i], s_host);
s.Read(yesterday, yesterday);
var val = "";
if (s.Count == 1 && !s[0].IsMissing)
{
val = s[0].Value.ToString("F2");
}
contents = contents.Replace(htmlPcode[i], val);
}
contents = contents.Replace("{site_name}", cbtt);
contents = contents.Replace("{yesterday}", yesterday.ToString("yyyy-MM-dd"));
contents = contents.Replace("{site_m1}", t.AddDays(-1).Month.ToString() + "/" + t.AddDays(-1).Day.ToString());
contents = contents.Replace("{site_m2}", t.AddDays(-2).Month.ToString() + "/" + t.AddDays(-2).Day.ToString());
contents = contents.Replace("{site_m3}", t.AddDays(-3).Month.ToString() + "/" + t.AddDays(-3).Day.ToString());
contents = contents.Replace("{site_m4}", t.AddDays(-4).Month.ToString() + "/" + t.AddDays(-4).Day.ToString());
String[] cropCode = { "ETr", "ALFP", "ALFM", "HAYP", "HAYM", "POTA", "POTS", "WGRN", "SGRN" };
String[] cropNames = { "site_ref", "alfalfa_peak", "alfalfa_mean", "grass_peak", "grass_mean", "russet",
"shepody", "winter_grain", "spring_grain" };
for (int i = 0; i < cropNames.Length; i++)
{
var rows = cropTable.Select("cbtt= '" + cbtt + "' and cropname= '" + cropCode[i] + "'");
if (rows.Length == 0)
{
continue;
}
var cRow = cropTable.NewCropDatesRow();
cRow.ItemArray = rows[0].ItemArray;
var crop = cropNames[i];
contents = contents.Replace("{" + crop + "_et_m1}", CropCurves.ETCropDaily(numDaysRead, 1, et, cRow));
contents = contents.Replace("{" + crop + "_et_m2}", CropCurves.ETCropDaily(numDaysRead, 2, et, cRow));
contents = contents.Replace("{" + crop + "_et_m3}", CropCurves.ETCropDaily(numDaysRead, 3, et, cRow));
contents = contents.Replace("{" + crop + "_et_m4}", CropCurves.ETCropDaily(numDaysRead, 4, et, cRow));
contents = contents.Replace("{" + crop + "_et_avg}", (CropCurves.EtSummation(4, et, cRow, numDaysRead) / 4).ToString("F2"));
contents = contents.Replace("{" + crop + "_7_day}", CropCurves.EtSummation(7, et, cRow, numDaysRead).ToString("F1"));
contents = contents.Replace("{" + crop + "_14_day}", CropCurves.EtSummation(14, et, cRow, numDaysRead).ToString("F1"));
contents = contents.Replace("{" + crop + "_total}", CropCurves.EtSummation(numDaysRead, et, cRow, numDaysRead).ToString("F1"));
contents = contents.Replace("{" + crop + "_start_date}", cRow.startdate.ToString("MM/dd"));
contents = contents.Replace("{" + crop + "_cover_date}", cRow.fullcoverdate.ToString("MM/dd"));
contents = contents.Replace("{" + crop + "_term_date}", cRow.terminatedate.ToString("MM/dd"));
}
File.WriteAllText(outputfn, contents);
}
/// <summary>
/// Creates Daily and Summary Crop Reports
/// </summary>
/// <param name="cbttList">list of cbtt to create charts for</param>
/// <param name="year">year used to filter database of crop dates</param>
/// <param name="date">terminate date (or if null use etr terminate date)</param>
public static void CreateCropReports(int year, string outputDirectory, HydrometHost host = HydrometHost.PNLinux)
{
s_host = host;
CropDatesDataSet.CropDatesDataTable cropTable = new CropDatesDataSet.CropDatesDataTable();
cropTable = CropDatesDataSet.GetCropDataTable(year, false);
var cbttList = (from row in cropTable.AsEnumerable() // get list of cbtt
select row.cbtt).Distinct().ToArray();
if (cbttList.Length > 1)
{// for performance query hydromet only once.
DateTime t1 = new DateTime(year, 1, 1);
DateTime t2 = new DateTime(year, 12, 31);
if (t2 > DateTime.Now.Date)
{
t2 = DateTime.Now.Date;
}
AddToCache(host, cbttList, "ETRS", t1, t2);
var pcode = new string[] { "MX", "MN", "PP", "TA", "UA", "WG" };
for (int i = 0; i < pcode.Length; i++)
{ // without 192 seconds., with = 21 seconds
AddToCache(host, cbttList, pcode[i], t1, t2);
}
}
DateTime t = DateTime.Now.Date;
for (int i = 0; i < cbttList.Length; i++)
{
var cropDates = cropTable.Where(x => x.cbtt == cbttList[i] &&
!x.IsterminatedateNull() &&
!x.IsstartdateNull() &&
!x.IsfullcoverdateNull()).ToArray();
if (cropDates.Length == 0)
{
continue;
}
ValidateCropDates(cbttList[i], cropDates);
var terminateDate = TerminateDate(cbttList[i], cropDates);
if (terminateDate < DateTime.Now)
{
t = terminateDate.AddDays(1); // daily is read in one more day
}
else
{
t = DateTime.Now.Date;
}
// Generates Daily and Summary Crop Charts
var dailyTxtChart = CreateDailyUglyTextReport(cbttList[i], t, cropDates);
var dailyHtmlChart = CreateDailyHTMLReport(cbttList[i], t, cropDates);
var sumChart = CreateSummaryReport(cbttList[i], terminateDate, cropDates);
CreateDailyHTMLWebReport(cbttList[i], t, cropDates); // for emails
//string header = "AgriMet is excited to announce a partnership with Washington State University to icorporate AgriMet data into WSU's Irrigation Scheduler. To customize crop consumptive water use specific to your field or fields, visit http://weather.wsu.edu/is/";
var fnDaily = Path.Combine(outputDirectory, cbttList[i].ToLower() + "ch.txt");
var fnDailyHtml = Path.Combine(outputDirectory, cbttList[i].ToLower() + "_crop_summary.html");
var fnSum = Path.Combine(outputDirectory, cbttList[i].ToLower() + terminateDate.Year.ToString().Substring(2) + "et.txt");
File.WriteAllLines(fnDailyHtml, dailyHtmlChart.ToArray());
WriteCropFile(dailyTxtChart, fnDaily);
WriteCropFile(sumChart, fnSum);
}
Console.WriteLine(" Daily and Summary Crop Charts Saved for " + cbttList.Length + " sites");
}
private static void SetupIndexColumn(CropDatesDataSet.CropDatesDataTable table)
{
table.indexColumn.AutoIncrement = true;
table.indexColumn.AutoIncrementSeed = GetNextIndex();
}
