public DataTable ViewDailyCop(DateTime FromDate, DateTime Todate) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Day")); dt.Columns.Add(new DataColumn("JobnNme")); dt.Columns.Add(new DataColumn("JobGroup")); dt.Columns.Add(new DataColumn("Cost")); dt.Columns.Add(new DataColumn("MadeTea")); DataRow dtrow = dt.NewRow(); SqlDataReader reader; SqlDataReader readerNew; reader = SQLHelper.ExecuteReader("SELECT DAY(DailyGroundTransactions.DateEntered) AS Day,JobMaster.JobName, JobGroup.GroupName, DailyGroundTransactions.Expenditure + DailyGroundTransactions.PluckingExpenditure AS Cost,DailyGroundTransactions.DateEntered FROM JobMaster INNER JOIN JobGroup ON JobMaster.JobGroup = JobGroup.GroupID INNER JOIN DailyGroundTransactions ON JobMaster.JobShortName = DailyGroundTransactions.WorkCodeID GROUP BY JobMaster.JobShortName, JobGroup.GroupName, DailyGroundTransactions.Expenditure, DailyGroundTransactions.PluckingExpenditure,JobMaster.JobName, DAY(DailyGroundTransactions.DateEntered), DailyGroundTransactions.DateEntered HAVING(DailyGroundTransactions.DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + Todate + "',102))", CommandType.Text); while (reader.Read()) { dtrow = dt.NewRow(); if (!reader.IsDBNull(0)) { dtrow[0] = reader.GetInt32(0); } if (!reader.IsDBNull(1)) { dtrow[1] = reader.GetString(1).Trim(); } if (!reader.IsDBNull(2)) { dtrow[2] = reader.GetString(2).Trim(); } if (!reader.IsDBNull(3)) { dtrow[3] = reader.GetDecimal(3); } if (!reader.IsDBNull(4)) { dtrow[4] = "0"; } readerNew = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(NettMadeTea) AS MadeTea FROM DailyYieldBookDetails GROUP BY DateEntered HAVING(DateEntered = CONVERT(DATETIME, '" + reader.GetDateTime(4) + "', 102))", CommandType.Text); while (readerNew.Read()) { if (!readerNew.IsDBNull(0)) { dtrow[4] = readerNew.GetDecimal(0); } } readerNew.Close(); dt.Rows.Add(dtrow); } reader.Close(); return(dt); }
public Decimal GetMadeTea(DateTime FromDate, DateTime Todate) { Decimal Madetea = 0; SqlDataReader reader; reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(NettMadeTea) AS MadeTea FROM DailyYieldBookDetails WHERE (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + Todate + "', 102))", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { Madetea = reader.GetDecimal(0); } } reader.Close(); return(Madetea); }
//public DataSet ViewIntakePerPlucker(DateTime FromDate, DateTime ToDate) //{ // DataSet ds = new DataSet(); // ds = SQLHelper.FillDataSet("", CommandType.Text); // return ds; //} public DataTable ViewIntakePerPlucker(DateTime FromDate, DateTime ToDate) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Date")); dt.Columns.Add(new DataColumn("GreenLeaf")); dt.Columns.Add(new DataColumn("CashKillos")); dt.Columns.Add(new DataColumn("PluckingManDays")); SqlDataReader reader; SqlDataReader readerNew; DataRow dtrow = dt.NewRow(); reader = EstateProductionSQLHelper.ExecuteReader("SELECT DateEntered,SUM(GreenLeaf) AS GreenLeaf FROM DailyYieldBookDetails GROUP BY DateEntered HAVING (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))ORDER BY DateEntered", CommandType.Text); while (reader.Read()) { dtrow = dt.NewRow(); if (!reader.IsDBNull(0)) { dtrow[0] = reader.GetDateTime(0); } if (!reader.IsDBNull(1)) { dtrow[1] = reader.GetDecimal(1); } dtrow[2] = "0"; dtrow[3] = "0"; readerNew = SQLHelper.ExecuteReader("SELECT SUM(CashKgs) AS CashKillos, SUM(ManDays - HolidayHalfNames) AS Mandays, WorkCodeID FROM DailyGroundTransactions GROUP BY DateEntered, CropType, WorkCodeID HAVING (DateEntered = CONVERT(DATETIME, '" + reader.GetDateTime(0) + "', 102)) AND (CropType = 1) AND (WorkCodeID = 'PLK')", CommandType.Text); while (readerNew.Read()) { if (!readerNew.IsDBNull(0)) { dtrow[2] = readerNew.GetDecimal(0); } if (!readerNew.IsDBNull(1)) { dtrow[3] = readerNew.GetDecimal(1); } } readerNew.Close(); dt.Rows.Add(dtrow); } reader.Close(); return(dt); }
public DataSet ViewYphToDate(Int32 Year, Int32 Month) { DataSet ds = new DataSet(); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Month")); dt.Columns.Add(new DataColumn("MonthID")); dt.Columns.Add(new DataColumn("Division")); dt.Columns.Add(new DataColumn("Field")); dt.Columns.Add(new DataColumn("FieldType")); dt.Columns.Add(new DataColumn("Extenet")); dt.Columns.Add(new DataColumn("GreenLeafMonth")); dt.Columns.Add(new DataColumn("MadeTeaMonth")); dt.Columns.Add(new DataColumn("YPHMonth")); dt.Columns.Add(new DataColumn("GreenLeafPrevious")); dt.Columns.Add(new DataColumn("MadeTeaPrevious")); dt.Columns.Add(new DataColumn("YPHPrevious")); DataRow dtrow = dt.NewRow(); SqlDataReader reader; SqlDataReader newReader; Decimal MadeTea = 0; Decimal Extent = 0; Decimal PreviousMadeTea = 0; Decimal PreviousGreenLeaf = 0; String Division = ""; String Field = ""; Int32 monthId = 0; String MonthName = ""; reader = SQLHelper.ExecuteReader("SELECT MonthName, MonthCode FROM Month WHERE (MonthCode = '" + Month + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { MonthName = reader.GetString(0).Trim(); } if (!reader.IsDBNull(1)) { monthId = reader.GetInt32(1); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT EstateDivision.DivisionName, EstateField.FieldName, EstateField.FieldType, EstateField.Extent,EstateDivision.DivisionID FROM EstateField INNER JOIN EstateDivision ON EstateField.DivisionID = EstateDivision.DivisionID", CommandType.Text); while (reader.Read()) { dtrow = dt.NewRow(); dtrow[0] = MonthName; dtrow[1] = monthId; if (!reader.IsDBNull(0)) { dtrow[2] = reader.GetString(0).Trim(); } if (!reader.IsDBNull(1)) { dtrow[3] = reader.GetString(1).Trim(); Field = reader.GetString(1).Trim(); } if (!reader.IsDBNull(2)) { dtrow[4] = reader.GetString(2).Trim(); } if (!reader.IsDBNull(3)) { dtrow[5] = reader.GetDecimal(3); Extent = reader.GetDecimal(3); } if (!reader.IsDBNull(4)) { Division = reader.GetString(4).Trim(); } dtrow[6] = "0"; dtrow[7] = "0"; newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea FROM DailyYieldBookDetails WHERE (MONTH(DateEntered) = '" + monthId + "') AND (YEAR(DateEntered) = '" + Year + "')GROUP BY DivisionID, FieldID HAVING(SUM(GreenLeaf) > 0) AND (DivisionID = '" + reader.GetString(4) + "') AND (FieldID = '" + reader.GetString(1).Trim() + "')", CommandType.Text); { while (newReader.Read()) { if (!newReader.IsDBNull(0)) { dtrow[6] = newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { dtrow[7] = newReader.GetDecimal(1); MadeTea = newReader.GetDecimal(1); } } } dtrow[8] = MadeTea / Extent; for (int i = 1; i < monthId; i++) { newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea FROM DailyYieldBookDetails WHERE (MONTH(DateEntered) = '" + i + "') AND (YEAR(DateEntered) = '" + Year + "')GROUP BY DivisionID, FieldID HAVING(SUM(GreenLeaf) > 0) AND (DivisionID = '" + Division + "') AND (FieldID = '" + Field + "')", CommandType.Text); { while (newReader.Read()) { if (!newReader.IsDBNull(0)) { PreviousGreenLeaf += newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { PreviousMadeTea += newReader.GetDecimal(1); } } } newReader.Dispose(); } dtrow[9] = PreviousGreenLeaf; dtrow[10] = PreviousMadeTea; dtrow[11] = PreviousMadeTea / Extent; PreviousGreenLeaf = 0; PreviousMadeTea = 0; Extent = 0; MadeTea = 0; Division = ""; Field = ""; dt.Rows.Add(dtrow); newReader.Dispose(); } reader.Dispose(); // return dt; ///////////////////////////Division///////////////////////// DataTable dtNew = new DataTable(); dtNew.Columns.Add(new DataColumn("Division")); dtNew.Columns.Add(new DataColumn("Extenet")); dtNew.Columns.Add(new DataColumn("GreenLeafMonth")); dtNew.Columns.Add(new DataColumn("MadeTeaMonth")); dtNew.Columns.Add(new DataColumn("YPHMonth")); dtNew.Columns.Add(new DataColumn("GreenLeafPrevious")); dtNew.Columns.Add(new DataColumn("MadeTeaPrevious")); dtNew.Columns.Add(new DataColumn("YPHPrevious")); DataRow dtrownew = dtNew.NewRow(); //SqlDataReader reader1; //SqlDataReader newReader; Decimal DivisionMadeTea = 0; Decimal DivisionExtenet = 0; reader = SQLHelper.ExecuteReader("SELECT EstateDivision.DivisionID, EstateDivision.DivisionName, SUM(EstateField.Extent) AS Extent FROM EstateDivision INNER JOIN EstateField ON EstateDivision.DivisionID = EstateField.DivisionID GROUP BY EstateDivision.DivisionID, EstateDivision.DivisionName", CommandType.Text); while (reader.Read()) { dtrownew = dtNew.NewRow(); if (!reader.IsDBNull(1)) { dtrownew[0] = reader.GetString(1).Trim(); } if (!reader.IsDBNull(2)) { dtrownew[1] = reader.GetDecimal(2); DivisionExtenet = reader.GetDecimal(2); } dtrownew[2] = "0"; dtrownew[3] = "0"; dtrownew[4] = "0"; dtrownew[5] = "0"; dtrownew[6] = "0"; dtrownew[7] = "0"; newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea FROM DailyYieldBookDetails WHERE (YEAR(DateEntered) = '" + Year + "') AND (MONTH(DateEntered) = '" + Month + "')GROUP BY DivisionID HAVING(DivisionID = '" + reader.GetString(0) + "')", CommandType.Text); while (newReader.Read()) { if (!newReader.IsDBNull(0)) { dtrownew[2] = newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { dtrownew[3] = newReader.GetDecimal(1); DivisionMadeTea = newReader.GetDecimal(1); } } dtrownew[4] = DivisionMadeTea / DivisionExtenet; Decimal DivisionPreviousGreenLeaf = 0; Decimal DivisionPreviousMadeTea = 0; for (int i = 1; i < Month; i++) { newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea FROM DailyYieldBookDetails WHERE (YEAR(DateEntered) = '" + Year + "') AND (MONTH(DateEntered) = '" + i + "')GROUP BY DivisionID HAVING(DivisionID = '" + reader.GetString(0) + "')", CommandType.Text); { while (newReader.Read()) { if (!newReader.IsDBNull(0)) { DivisionPreviousGreenLeaf += newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { DivisionPreviousMadeTea += newReader.GetDecimal(1); } } } newReader.Dispose(); } dtrownew[5] = DivisionPreviousGreenLeaf; dtrownew[6] = DivisionPreviousMadeTea; dtrownew[7] = DivisionPreviousMadeTea / DivisionExtenet; DivisionExtenet = 0; DivisionMadeTea = 0; dtNew.Rows.Add(dtrownew); newReader.Dispose(); } reader.Close(); //////////////////////////////////////////////////vp and sd DataTable dtNew1 = new DataTable(); dtNew1.Columns.Add(new DataColumn("Type")); dtNew1.Columns.Add(new DataColumn("Extenet")); dtNew1.Columns.Add(new DataColumn("GreenLeafMonth")); dtNew1.Columns.Add(new DataColumn("MadeTeaMonth")); dtNew1.Columns.Add(new DataColumn("YPHMonth")); dtNew1.Columns.Add(new DataColumn("GreenLeafPrevious")); dtNew1.Columns.Add(new DataColumn("MadeTeaPrevious")); dtNew1.Columns.Add(new DataColumn("YPHPrevious")); dtNew1.Columns.Add(new DataColumn("TotalYPHMonth")); dtNew1.Columns.Add(new DataColumn("TotalYPHPrevious")); DataRow dtrownew1 = dtNew1.NewRow(); Decimal Madetea = 0; Decimal TypeExtent = 0; //Decimal TotalMadetea = 0; Decimal TotalExtent = 0; reader = SQLHelper.ExecuteReader("SELECT DISTINCT FieldType, SUM(Extent) AS Extent FROM EstateField GROUP BY FieldType", CommandType.Text); while (reader.Read()) { dtrownew1 = dtNew1.NewRow(); if (!reader.IsDBNull(1)) { dtrownew1[0] = reader.GetString(0).Trim(); } if (!reader.IsDBNull(1)) { dtrownew1[1] = reader.GetDecimal(1); TypeExtent = reader.GetDecimal(1); } dtrownew1[2] = "0"; dtrownew1[3] = "0"; dtrownew1[4] = "0"; dtrownew1[5] = "0"; dtrownew1[6] = "0"; dtrownew1[7] = "0"; dtrownew1[8] = "0"; dtrownew1[9] = "0"; newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea, FieldType FROM DailyYieldBookDetails WHERE (YEAR(DateEntered) = '" + Year + "') AND (MONTH(DateEntered) = '" + Month + "')GROUP BY FieldType HAVING (FieldType = '" + reader.GetString(0).Trim() + "')", CommandType.Text); while (newReader.Read()) { if (!newReader.IsDBNull(0)) { dtrownew1[2] = newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { dtrownew1[3] = newReader.GetDecimal(1); Madetea = newReader.GetDecimal(1); } } dtrownew1[4] = Madetea / TypeExtent; Decimal PreviousGreenLeafType = 0; Decimal PreviousMadeTeaType = 0; for (int i = 1; i < Month; i++) { newReader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS GreenLeaf, SUM(MadeTea) AS MadeTea, FieldType FROM DailyYieldBookDetails WHERE (YEAR(DateEntered) = '" + Year + "') AND (MONTH(DateEntered) = '" + i + "')GROUP BY FieldType HAVING (FieldType = '" + reader.GetString(0).Trim() + "')", CommandType.Text); { while (newReader.Read()) { if (!newReader.IsDBNull(0)) { PreviousGreenLeafType += newReader.GetDecimal(0); } if (!newReader.IsDBNull(1)) { PreviousMadeTeaType += newReader.GetDecimal(1); } } } newReader.Dispose(); } dtrownew1[5] = PreviousGreenLeafType; dtrownew1[6] = PreviousMadeTeaType; dtrownew1[7] = PreviousMadeTeaType / TypeExtent; TotalExtent = TypeExtent = 0; Madetea = 0; dtNew1.Rows.Add(dtrownew1); newReader.Dispose(); } reader.Close(); ds.Tables.Add(dt); ds.Tables.Add(dtNew); ds.Tables.Add(dtNew1); return(ds); }
public DataTable Harvesting(String DivisionID, Int32 Week, Int32 Year, DateTime FromDate, DateTime ToDate) { DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("Crop")); dt.Columns.Add(new DataColumn("LPH")); dt.Columns.Add(new DataColumn("BelowNorm")); dt.Columns.Add(new DataColumn("ManDays")); dt.Columns.Add(new DataColumn("Extent")); dt.Columns.Add(new DataColumn("TodateCrop")); dt.Columns.Add(new DataColumn("TodateLPH")); dt.Columns.Add(new DataColumn("TodateBelowNorm")); dt.Columns.Add(new DataColumn("TodateManDays")); dt.Columns.Add(new DataColumn("TodateExtent")); dt.Columns.Add(new DataColumn("BelowNormPercentage")); dt.Columns.Add(new DataColumn("TodateBelowNormPercentage")); dt.Columns.Add(new DataColumn("FactoryWeightToFieldWeight")); dt.Columns.Add(new DataColumn("FactoryWeightToCRollWeight")); dt.Columns.Add(new DataColumn("FactoryWeightToFieldWeightPercentage")); dt.Columns.Add(new DataColumn("FactoryWeightToCRollWeightPercentage")); dt.Columns.Add(new DataColumn("TodateFactoryWeightToFieldWeight")); dt.Columns.Add(new DataColumn("TodateFactoryWeightToCRollWeight")); dt.Columns.Add(new DataColumn("TodateFactoryWeightToFieldWeightPercentage")); dt.Columns.Add(new DataColumn("TodateFactoryWeightToCRollWeightPercentage")); dt.Columns.Add(new DataColumn("Average")); dt.Columns.Add(new DataColumn("TodateAverage")); //dt.Columns.Add(new DataColumn("CropBudjet")); //dt.Columns.Add(new DataColumn("ExtentBudjet")); SqlDataReader reader; DataRow dtrow = dt.NewRow(); Decimal Extent = 0; Int32 LabourCount = 0; Decimal TodateExtent = 0; Int32 TodateLabourCount = 0; Decimal BelownormPercentage = 0; Decimal TodateBelownormPercentage = 0; Int32 Belownorm = 0; Int32 TodateBelownorm = 0; Int32 empCount = 0; Int32 TodateempCount = 0; Decimal FieldWeight = 0; Decimal FactoryWeight = 0; Decimal CheckRollWeight = 0; Decimal TodateFieldWeight = 0; Decimal TodateFactoryWeight = 0; Decimal TodateCheckRollWeight = 0; Decimal FactoryWeightToFieldWeight = 0; Decimal FactoryWeightToFieldWeightPercentage = 0; Decimal FactoryWeightToCRollWeight = 0; Decimal FactoryWeightToCRollWeightPercentage = 0; Decimal TodateFactoryWeightToFieldWeight = 0; Decimal TodateFactoryWeightToFieldWeightPercentage = 0; Decimal TodateFactoryWeightToCRollWeight = 0; Decimal TodateFactoryWeightToCRollWeightPercentage = 0; reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(FieldWeight) AS Total, YEAR(EnteredDate) AS Year FROM DailyPluckingRounds GROUP BY { fn WEEK(EnteredDate) }, DivisionID, YEAR(EnteredDate)HAVING (YEAR(EnteredDate) = '" + Year + "') AND ({ fn WEEK(EnteredDate) } = '" + Week + "') AND (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[0] = reader.GetDecimal(0); FieldWeight = reader.GetDecimal(0); } } reader.Dispose(); reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(HectaresCompleted) AS Total FROM DailyPluckingRounds GROUP BY { fn WEEK(EnteredDate) }, DivisionID, YEAR(EnteredDate) HAVING (YEAR(EnteredDate) = '" + Year + "') AND ({ fn WEEK(EnteredDate) } = '" + Week + "') AND (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { Extent = reader.GetDecimal(0); } } reader.Dispose(); reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(AreaCompletedExtent) AS total FROM DailyFieldWorkDetails GROUP BY DivisionID, { fn WEEK(EnteredDate) }, YEAR(EnteredDate) HAVING (YEAR(EnteredDate) = '" + Year + "') AND ({ fn WEEK(EnteredDate) } = '" + Week + "') AND (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { Extent += reader.GetDecimal(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT COUNT(DailyGroundTransactions.EmpNo) AS Count FROM DailyGroundTransactions INNER JOIN JobMaster ON DailyGroundTransactions.WorkCodeID = JobMaster.JobShortName WHERE (JobMaster.ExpenditureType <> 'Capital') AND (DailyGroundTransactions.DivisionID = '" + DivisionID + "') AND ({ fn WEEK(DailyGroundTransactions.DateEntered) } = '" + Week + "') AND (YEAR(DailyGroundTransactions.DateEntered) = '" + Year + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { LabourCount = reader.GetInt32(0);; } } reader.Dispose(); try { Decimal LPH = Extent / LabourCount; dtrow[1] = Math.Round(LPH, 2); } catch { } reader = SQLHelper.ExecuteReader("SELECT COUNT(EmpNo) AS count FROM DailyGroundTransactions WHERE (WorkQty - NormKilos < 0) AND (DivisionID = '" + DivisionID + "') AND ({ fn WEEK(DateEntered) } = '" + Week + "') AND (WorkCodeID = 'PLK') AND (YEAR(DateEntered) = '" + Year + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[2] = reader.GetInt32(0); Belownorm = reader.GetInt32(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT SUM(DailyGroundTransactions.ManDays) AS Count FROM DailyGroundTransactions INNER JOIN JobMaster ON DailyGroundTransactions.WorkCodeID = JobMaster.JobShortName WHERE (JobMaster.ExpenditureType <> 'Capital') AND (DailyGroundTransactions.WorkCodeID = 'PLK') AND (YEAR(DailyGroundTransactions.DateEntered) = '" + Year + "') AND ({ fn WEEK(DailyGroundTransactions.DateEntered) } = '" + Week + "') AND (DailyGroundTransactions.DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[3] = reader.GetDecimal(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT SUM(Extent) AS extent FROM EstateField GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[4] = reader.GetDecimal(0); } } reader.Dispose(); //////////////////////////////// Todate/////////////////////////////////////////////////////////////////////// reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(FieldWeight) AS Total FROM DailyPluckingRounds WHERE (EnteredDate BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[5] = reader.GetDecimal(0); TodateFieldWeight = reader.GetDecimal(0); } } reader.Dispose(); reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(HectaresCompleted) AS Total FROM DailyPluckingRounds WHERE(EnteredDate BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateExtent = reader.GetDecimal(0); } } reader.Dispose(); reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(AreaCompletedExtent) AS total FROM DailyFieldWorkDetails WHERE(EnteredDate BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateExtent += reader.GetDecimal(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT COUNT(DailyGroundTransactions.EmpNo) AS Count, DailyGroundTransactions.DivisionID FROM DailyGroundTransactions INNER JOIN JobMaster ON DailyGroundTransactions.WorkCodeID = JobMaster.JobShortName WHERE (JobMaster.ExpenditureType <> 'Capital') AND (DailyGroundTransactions.DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY DailyGroundTransactions.DivisionID HAVING (DailyGroundTransactions.DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateLabourCount = reader.GetInt32(0);; } } reader.Dispose(); Decimal LPHTodate = TodateExtent / TodateLabourCount; dtrow[6] = Math.Round(LPHTodate, 2); reader = SQLHelper.ExecuteReader("SELECT COUNT(EmpNo) AS count FROM DailyGroundTransactions WHERE (WorkQty - NormKilos < 0) AND (DivisionID = '" + DivisionID + "') AND (WorkCodeID = 'PLK') AND (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[7] = reader.GetInt32(0); TodateBelownorm = reader.GetInt32(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT SUM(DailyGroundTransactions.ManDays) AS Count FROM DailyGroundTransactions INNER JOIN JobMaster ON DailyGroundTransactions.WorkCodeID = JobMaster.JobShortName WHERE (DailyGroundTransactions.DivisionID = '" + DivisionID + "') AND (DailyGroundTransactions.WorkCodeID = 'PLK') AND (JobMaster.ExpenditureType <> 'Capital') AND (DailyGroundTransactions.DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { dtrow[8] = reader.GetDecimal(0); } } dtrow[9] = dtrow[4]; ///////////////////////////////////belownorm Percentage///////////////// reader = SQLHelper.ExecuteReader("SELECT COUNT(EmpNo) AS count FROM DailyGroundTransactions WHERE (DivisionID = '" + DivisionID + "') AND (WorkCodeID = 'PLK') AND ({ fn WEEK(DateEntered) } = '" + Week + "') AND (YEAR(DateEntered) = '" + Year + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { empCount = reader.GetInt32(0); } } reader.Dispose(); try { BelownormPercentage = (Convert.ToDecimal(Belownorm) / Convert.ToDecimal(empCount)) * 100; } catch { } dtrow[10] = BelownormPercentage; reader = SQLHelper.ExecuteReader("SELECT COUNT(EmpNo) AS count FROM DailyGroundTransactions WHERE (DivisionID = '" + DivisionID + "') AND (WorkCodeID = 'PLK') AND (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME,'" + ToDate + "', 102))", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateempCount = reader.GetInt32(0); } } reader.Dispose(); try { TodateBelownormPercentage = (Convert.ToDecimal(TodateBelownorm) / Convert.ToDecimal(TodateempCount)) * 100; } catch { } dtrow[11] = TodateBelownormPercentage; ///////////////////////////////////Factory and Checkroll weight///////////////// reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS Qty FROM DailyYieldBookDetails WHERE (YEAR(DateEntered) = '" + Year + "') AND ({ fn WEEK(DateEntered) } = '" + Week + "')GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { FactoryWeight = reader.GetDecimal(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT SUM(WorkQty) AS Qty FROM dbo.DailyGroundTransactions WHERE ({ fn WEEK(DateEntered) } = '" + Week + "') AND (YEAR(DateEntered) = '" + Year + "')GROUP BY WorkCodeID, DivisionID HAVING (WorkCodeID = 'PLK') AND (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { CheckRollWeight = reader.GetDecimal(0); } } reader.Dispose(); FactoryWeightToFieldWeight = FieldWeight - FactoryWeight; dtrow[12] = FactoryWeightToFieldWeight; FactoryWeightToCRollWeight = CheckRollWeight - FactoryWeight; dtrow[13] = FactoryWeightToCRollWeight; try { dtrow[14] = (FactoryWeightToFieldWeight / FieldWeight) * 100; } catch { } try { dtrow[15] = (FactoryWeightToCRollWeight / CheckRollWeight) * 100; } catch { } ////////////////////////Todate Factory and Checkroll weight///////////////// reader = EstateProductionSQLHelper.ExecuteReader("SELECT SUM(GreenLeaf) AS Qty FROM DailyYieldBookDetails WHERE (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY DivisionID HAVING (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateFactoryWeight = reader.GetDecimal(0); } } reader.Dispose(); reader = SQLHelper.ExecuteReader("SELECT SUM(WorkQty) AS Qty FROM dbo.DailyGroundTransactions WHERE (DateEntered BETWEEN CONVERT(DATETIME, '" + FromDate + "', 102) AND CONVERT(DATETIME, '" + ToDate + "', 102))GROUP BY WorkCodeID, DivisionID HAVING (WorkCodeID = 'PLK') AND (DivisionID = '" + DivisionID + "')", CommandType.Text); while (reader.Read()) { if (!reader.IsDBNull(0)) { TodateCheckRollWeight = reader.GetDecimal(0); } } reader.Dispose(); TodateFactoryWeightToFieldWeight = TodateFieldWeight - TodateFactoryWeight; dtrow[16] = TodateFactoryWeightToFieldWeight; TodateFactoryWeightToCRollWeight = TodateCheckRollWeight - TodateFactoryWeight; dtrow[17] = TodateFactoryWeightToCRollWeight; try { dtrow[18] = (TodateFactoryWeightToFieldWeight / TodateFieldWeight) * 100; } catch { } try { dtrow[19] = (TodateFactoryWeightToCRollWeight / TodateCheckRollWeight) * 100; } catch { } //////////////////////////////////////average///////////////////////////////////////////// Decimal Average = (FactoryWeight / Convert.ToDecimal(empCount)); dtrow[20] = Average; dtrow[21] = TodateFactoryWeight / Convert.ToDecimal(TodateempCount); ////////////////////////////////budjet//////////////////////////////////////////////////////// dt.Rows.Add(dtrow); return(dt); }