public DataSet ViewYph(DateTime FromDate, DateTime ToDate)
{
DataSet ds = new DataSet();
ds = EstateProductionSQLHelper.FillDataSet("SELECT TOP (100) PERCENT dbo.DailyYieldBookDetails.DateEntered, dbo.EstateDivision.DivisionName, dbo.DailyYieldBookDetails.FieldID, dbo.DailyYieldBookDetails.MadeTea, dbo.EstateField.Extent FROM dbo.DailyYieldBookDetails INNER JOIN dbo.EstateDivision ON dbo.DailyYieldBookDetails.DivisionID = dbo.EstateDivision.DivisionID INNER JOIN dbo.EstateField ON dbo.DailyYieldBookDetails.FieldID = dbo.EstateField.FieldID AND dbo.DailyYieldBookDetails.DivisionID = dbo.EstateField.DivisionID WHERE (dbo.DailyYieldBookDetails.DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))ORDER BY dbo.DailyYieldBookDetails.DateEntered", CommandType.Text);
return(ds);
}
public DataTable MonthlyPluckingRounds(String DivisionID, Int32 Year, Int32 Month)
{
Int32 roundNo = 0;
Decimal FieldWeight = 0;
Decimal Mandays = 0;
Boolean chk = false;
int count = 0;
SqlDataReader reader;
DataSet ds = new DataSet();
ds = SQLHelper.FillDataSet("SELECT DivisionID,FieldID FROM EstateField WHERE(DivisionID = '" + DivisionID + "')", CommandType.Text);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Division"));
dt.Columns.Add(new DataColumn("Field"));
dt.Columns.Add(new DataColumn("RoundNo"));
dt.Columns.Add(new DataColumn("FieldWeight"));
dt.Columns.Add(new DataColumn("ManDays"));
dt.Columns.Add(new DataColumn("IntakePerPlucker"));
DataRow dtrow = dt.NewRow();
foreach (DataRow drRow in ds.Tables[0].Rows)
{
roundNo = 0;
String strDivisionID = drRow[0].ToString();
String strField = drRow[1].ToString();
DataSet dsnew = new DataSet();
dsnew = EstateProductionSQLHelper.FillDataSet("SELECT FieldWeight, RoundCompleted,FieldID,EnteredDate FROM DailyPluckingRounds WHERE (FieldID = '" + strField + "') AND (MONTH(EnteredDate) = '" + Month + "') AND (YEAR(EnteredDate) = '" + Year + "') AND (DivisionID = '" + strDivisionID + "') ORDER BY AutoKey", CommandType.Text);
foreach (DataRow drR in dsnew.Tables[0].Rows)
{
count++;
if (count == 1)
{
dtrow = dt.NewRow();
}
if (drR.ItemArray[1].ToString() == "True")
{
roundNo++;
FieldWeight += Convert.ToDecimal(drR.ItemArray[0].ToString());
reader = SQLHelper.ExecuteReader("SELECT SUM(dbo.DailyGroundTransactions.ManDays + dbo.DailyGroundTransactions.CashManDays) AS ManDays, dbo.EstateField.MapField,dbo.DailyGroundTransactions.DateEntered FROM dbo.DailyGroundTransactions INNER JOIN dbo.EstateField ON dbo.DailyGroundTransactions.DivisionID = dbo.EstateField.DivisionID AND dbo.DailyGroundTransactions.FieldID = dbo.EstateField.FieldID GROUP BY dbo.DailyGroundTransactions.DateEntered, dbo.DailyGroundTransactions.DivisionID, dbo.EstateField.MapField, dbo.DailyGroundTransactions.WorkCodeID,dbo.DailyGroundTransactions.WorkType, dbo.DailyGroundTransactions.CashManDays HAVING (dbo.DailyGroundTransactions.DateEntered = CONVERT(DATETIME, '" + drR.ItemArray[3].ToString() + "', 102)) AND (dbo.DailyGroundTransactions.DivisionID = '" + strDivisionID + "') AND (dbo.EstateField.MapField = '" + strField + "') AND (dbo.DailyGroundTransactions.WorkType = 1) AND (dbo.DailyGroundTransactions.WorkCodeID = 'PLK')", CommandType.Text);
while (reader.Read())
{
Mandays += reader.GetDecimal(0);
}
reader.Close();
dtrow[0] = strDivisionID;
dtrow[1] = strField;
dtrow[2] = roundNo;
dtrow[3] = FieldWeight;
dtrow[4] = Mandays;
dtrow[5] = "0";
dt.Rows.Add(dtrow);
count = 0;
FieldWeight = 0;
Mandays = 0;
}
else
{
FieldWeight += Convert.ToDecimal(drR.ItemArray[0].ToString());
DateTime date = Convert.ToDateTime(drR.ItemArray[3].ToString());
reader = SQLHelper.ExecuteReader("SELECT SUM(dbo.DailyGroundTransactions.ManDays + dbo.DailyGroundTransactions.CashManDays) AS ManDays, dbo.EstateField.MapField,dbo.DailyGroundTransactions.DateEntered FROM dbo.DailyGroundTransactions INNER JOIN dbo.EstateField ON dbo.DailyGroundTransactions.DivisionID = dbo.EstateField.DivisionID AND dbo.DailyGroundTransactions.FieldID = dbo.EstateField.FieldID GROUP BY dbo.DailyGroundTransactions.DateEntered, dbo.DailyGroundTransactions.DivisionID, dbo.EstateField.MapField, dbo.DailyGroundTransactions.WorkCodeID,dbo.DailyGroundTransactions.WorkType, dbo.DailyGroundTransactions.CashManDays HAVING (dbo.DailyGroundTransactions.DateEntered = CONVERT(DATETIME, '" + drR.ItemArray[3].ToString() + "', 102)) AND (dbo.DailyGroundTransactions.DivisionID = '" + strDivisionID + "') AND (dbo.EstateField.MapField = '" + strField + "') AND (dbo.DailyGroundTransactions.WorkType = 1) AND (dbo.DailyGroundTransactions.WorkCodeID = 'PLK')", CommandType.Text);
while (reader.Read())
{
Mandays += reader.GetDecimal(0);
}
reader.Close();
}
}
}
return(dt);
}
