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); }