public static DataTable GetInspectionByFarmerID(string FarmerID, string Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT far.FarmerId,far.FirstName as 'FarmerName',far.FarmerCode,far.City_Village AS 'FarmerVillage',emp.EmployeeId AS 'InspectorID',emp.EmployeeFristName AS 'InspectorName', convert(char(11),ins.PlanDate,106) AS PlanDate,convert(char(11),ins.VisitDate,106) AS 'VisitedDate',ins.InspectionID,SUM( ffd.PlotArea ) AS 'Total_Area',ins.Result FROM dbo.tblInspection ins INNER JOIN dbo.tblInspectionHistory inh ON ins.InspectionHistoryID = inh.InspectioHistoryID and inh.[Year] ='" + Year + "' INNER JOIN dbo.tblEmployeeDetails emp ON ins.EmployeeID = emp.EmployeeId INNER JOIN dbo.tblFarmerDetails far ON ins.FarmerID='" + FarmerID + "' and far.FarmerId='" + FarmerID + "' LEFT JOIN dbo.tblFarmerFarmDetails ffd ON ins.FarmerID='" + FarmerID + "' and ffd.FarmerID='" + FarmerID + "' WHERE ffd.ParentFarmID = 0 AND ffd.[Delete] = 0 GROUP BY far.FarmerId,far.FirstName ,far.FarmerCode,far.City_Village ,emp.EmployeeId ,emp.EmployeeFristName, ins.PlanDate,ins.VisitDate ,ins.InspectionID,ins.Result"));
}
public static DataTable GetHolidaysList()
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT HolidayID,HolidayDate,HolidayYear FROM tblHolidayList WHERE [Delete]= 0 ORDER BY HolidayDate ASC"));
}
public static DataTable GetplotandDiesinfo(string farmerID, int Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select ffd.FarmID,ffd.FarmerID,ffd.AreaCode,ffd.PlotArea,pd.ProductName as Maincrop,ffd.IsInterCrop as IIC,pd.ProductId,pld.PlantationDate,FirstHarvestDate,SecondHarvestDate,di.DiseaseName,DMIExpected,DMIPreventionMaterial,DMISource,DMIBillNo,dt.DMIT_HC,DMIT_Days,DMIT_Period,DMIT_Planting from tblFarmerFarmDetails ffd, tblProductDetails pd,tblPlantationDetails pld,tblDiseaseManagementInfo di,tblDiseaseManagementInfoTransaction dt where ffd.ParentFarmID > 0 and ffd.FarmerID='" + farmerID + "' and FYear='" + Year + "' and ffd.productID=pd.ProductId and pld.ProductId=pd.ProductId and ffd.FarmID=pld.FarmID and di.DiseaseMID =dt.DiseaseMID AND di.ProductID=ffd.productID and pd.ProductId= di.ProductID and dt.[delete]=0 and di.[delete]=0 order by areacode"));
}
public static DataTable GetProductDetailsbySeason(int seasonid)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT p.*, s.SeasonName, s.StartDate, s.EndDate, c.CategoryName From dbo.tblProductDetails p,dbo.tblSeason s, dbo.tblCategory c WHERE s.SeasonID =" + seasonid + " and s.SeasonID = p.SeasonID AND c.CategoryId = p.CategoryId and p.[Delete] =0"));
}
public static DataTable Holidays(int year, DateTime start, DateTime end)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select HolidayID, CONVERT(varchar(10), HolidayDate,101) AS 'HolidayDate', HolidayYear from tblHolidayList where HolidayYear=" + year + " AND HolidayDate BETWEEN '" + start.ToShortDateString() + "' AND '" + end.ToShortDateString() + " 23:59:59:970' and [delete]=0 "));
}
// new code
public static DataTable GetPlotandPlantinfo(string farmerID, int Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select ffd.FarmID,ffd.FarmerID,ffd.AreaCode,ffd.PlotArea,pd.ProductName as Maincrop,ffd.IsInterCrop as IIC,pd.ProductId,pld.PlantationDate,FirstHarvestDate,SecondHarvestDate,p.PlantingSource,PlantingBill_Date,PlantingQuantity from tblFarmerFarmDetails ffd, tblProductDetails pd ,tblPlantationDetails pld,tblPlantingInformation p where ffd.ParentFarmID > 0 and ffd.FarmerID='" + farmerID + "' and FYear='" + Year + "' and ffd.productID=pd.ProductId and pld.ProductId=pd.ProductId and ffd.FarmID=pld.FarmID and pld.ProductId= p.ProductID order by areacode"));
}
public static DataTable GetSupplierDetails(string SupplierId)
{
MudarDBHelper mbdh = MudarDBHelper.Instance;
return(mbdh.ExecuteDataTable("SELECT sd.*,sppt.* FROM dbo.tblSupplierDetails sd LEFT JOIN dbo.tblSupplierPriceandPaymentTermsDetails sppt ON sd.SupplierId = sppt.SupplierId WHERE sd.[Delete] = 0 AND sd.SupplierId ='" + SupplierId + "'"));
}
public static DataTable GetPlantationDate(string farmerID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select pd.PlantationDate,productID,FirstHarvestDate,SecondHarvestDate from tblPlantationDetails pd where pd.FarmerId='" + farmerID + "'"));
}
public static DataTable GetProductDetails(int seasonID, int CategoryID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT p.*, s.SeasonName, c.CategoryName From dbo.tblProductDetails p,dbo.tblSeason s, dbo.tblCategory c WHERE s.SeasonID = " + seasonID + " AND c.CategoryId = " + CategoryID + " and p.[Delete] =0"));
}
public static DataTable GetSupplierDetails()
{
MudarDBHelper mbdh = MudarDBHelper.Instance;
return(mbdh.ExecuteDataTable("SELECT sd.*,sppt.* FROM dbo.tblSupplierDetails sd LEFT JOIN dbo.tblSupplierPriceandPaymentTermsDetails sppt ON sd.SupplierId = sppt.SupplierId WHERE sd.[Delete] = 0 ORDER BY sd.CreatedDate DESC"));
}
public static DataTable GetAllProducDetails()
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select pd.* from tblProductDetails pd where pd.[delete]=0"));
}
public static bool tblProductPrice_INSandUPD(DataTable dt, DateTime dtDate, double USD, double Transport, double Others, string CreatedBy,
string ModifiedBy, int TypeOfOperation, decimal MandyTax, decimal BMar, decimal insurance, decimal Localtax, decimal AddUpPrice)
{
bool Result = false;
int ReturnHistoryId = 0;
MudarSPName sp = new MudarSPName();
MudarDBHelper mdbh = MudarDBHelper.Instance;
List <SqlParameter> Params = new List <SqlParameter>();
Params.Add(mdbh.AddParameter("CreateDate", SqlDbType.DateTime, dtDate));
Params.Add(mdbh.AddParameter("CreatedBy", SqlDbType.NVarChar, CreatedBy));
Params.Add(mdbh.AddParameter("return", SqlDbType.Bit, Result, Param_Directions.Param_Out));
Params.Add(mdbh.AddParameter("HistoryId", SqlDbType.Int, ReturnHistoryId, Param_Directions.Param_Out));
try
{
List <string> output = new List <string>();
mdbh.ExecuteNonQuery(sp.sp_tblPriceHistory_INS, Params, ref output);
if (output.Count >= 2)
{
Result = Convert.ToBoolean(output[0]);
ReturnHistoryId = Convert.ToInt32(output[1]);
}
if (Result == true)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Result = false;
Params.Clear();
Params.Add(mdbh.AddParameter("PriceHistoryId", SqlDbType.Int, ReturnHistoryId));
Params.Add(mdbh.AddParameter("ProductId", SqlDbType.Int, dt.Rows[i]["ProductId"].ToString()));
Params.Add(mdbh.AddParameter("PriceMB", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["PriceMB"].ToString()), 2)));
Params.Add(mdbh.AddParameter("POPriceMB", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["POPriceMB"].ToString()), 2)));
Params.Add(mdbh.AddParameter("FOBPrice", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["FOBPrice"].ToString()), 2, MidpointRounding.AwayFromZero)));
Params.Add(mdbh.AddParameter("USA_Sea", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["USA_Sea"].ToString()), 2, MidpointRounding.AwayFromZero)));
Params.Add(mdbh.AddParameter("USA_Air", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["USA_Air"].ToString()), 2)));
Params.Add(mdbh.AddParameter("USA_Air_West", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["USA_Air_West"].ToString()), 2)));
Params.Add(mdbh.AddParameter("Europe_Sea", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["Europe_Sea"].ToString()), 2)));
Params.Add(mdbh.AddParameter("Europe_Air", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["Europe_Air"].ToString()), 2)));
Params.Add(mdbh.AddParameter("Europe_Air_West", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["Europe_Air_West"].ToString()), 2)));
Params.Add(mdbh.AddParameter("India_Price", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["India_Price"].ToString()), 2)));
Params.Add(mdbh.AddParameter("Non_organic_India", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["Non_organic_India"].ToString()), 2)));
Params.Add(mdbh.AddParameter("Non_organic_USA", SqlDbType.Money, Math.Round(Convert.ToDecimal(dt.Rows[i]["Non_organic_USA"].ToString()), 2)));
Params.Add(mdbh.AddParameter("USDollar", SqlDbType.Money, USD));
Params.Add(mdbh.AddParameter("OtherPrice", SqlDbType.Money, Others));
Params.Add(mdbh.AddParameter("Transport", SqlDbType.Money, Transport));
Params.Add(mdbh.AddParameter("MandyTax", SqlDbType.Decimal, MandyTax));
Params.Add(mdbh.AddParameter("BMar", SqlDbType.Decimal, BMar));
Params.Add(mdbh.AddParameter("insurance", SqlDbType.Decimal, insurance));
Params.Add(mdbh.AddParameter("Localtax", SqlDbType.Decimal, Localtax));
Params.Add(mdbh.AddParameter("createdBy", SqlDbType.VarChar, CreatedBy));
Params.Add(mdbh.AddParameter("modifiedBy", SqlDbType.VarChar, ModifiedBy));
Params.Add(mdbh.AddParameter("typeOfOperation", SqlDbType.Int, TypeOfOperation));
Params.Add(mdbh.AddParameter("AddUPPrice", SqlDbType.Money, AddUpPrice));
Params.Add(mdbh.AddParameter("retunValue", SqlDbType.Bit, Result, Param_Directions.Param_Out));
try
{
Result = (bool)mdbh.ExecuteNonQuery(sp.sp_tblProductPrice_INSandUPD, Params);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(Result);
}
// test run
public static DataTable GetProductDetailsbySeason(int seasonid, int year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select p.*, s.SeasonName, s.StartDate, s.EndDate, c.CategoryName from tblProductDetails p, tblSeason s,tblCategory c where p.SeasonID=" + seasonid + " and s.SeasonID=" + seasonid + " and s.SeasonYear=" + year + " and p.Pyear=" + year + " and c.CategoryId = p.CategoryId"));
}
public static DataTable GetHolidaysListBasedonHolidayID(int HolidayID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT HolidayID,HolidayDate,HolidayYear FROM tblHolidayList WHERE HolidayID='" + HolidayID + "' AND [Delete]= 0"));
}
public static DataTable GetBranches()
{
MudarDBHelper mbdh = MudarDBHelper.Instance;
return(mbdh.ExecuteDataTable("select BranchId,Bname from tblbranchdetails where other=1"));
}
// Internal Inspection Report
public static DataTable GetPlantingInfo(string farmerID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select pd.PlantationDate,p.PlantingSource,PlantingBill_Date,PlantingQuantity from tblPlantationDetails pd,tblPlantingInformation p where pd.FarmerId='" + farmerID + "' and pd.ProductId= p.ProductID "));
}
public static DataTable GetInspectionPlan(int HistoryID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT far.FarmerId,far.FirstName as 'FarmerName',far.FarmerCode,far.City_Village AS 'FarmerArea',emp.EmployeeId AS 'InspectorID',emp.EmployeeFristName AS 'InspectorName', ins.PlanDate,ins.VisitDate AS 'VisitedDate',ins.InspectionID FROM dbo.tblInspection ins INNER JOIN dbo.tblEmployeeDetails emp ON ins.EmployeeID = emp.EmployeeId INNER JOIN dbo.tblFarmerDetails far ON ins.FarmerID = far.FarmerId WHERE ins.InspectionHistoryID = " + HistoryID + " and ins.[delete]=0 order by ins.PlanDate asc"));
}
public static DataTable GetInspectiononFarmerID(string FarmerID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select PlanDate from tblInspection where FarmerID='" + FarmerID + "' and [Delete] = 0 [Delete] = 0 order by PlanDate asc"));
}
public static DataTable GetInspectionPlanHistory(int Year, int SeasonID, string ICStype)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT * FROM [tblInspectionHistory] WHERE [Year] =" + Year + " AND [SeasonID] =" + SeasonID + " AND planname='" + ICStype + "' AND [Delete]=0 "));
}
public static DataTable GetPlantaionDetails(string FarmerID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select FirstHarvestDate,(EstimationFHerbaga+EstimationSHerbaga) as Estimation,FirstHerbaga,FirstDistillationDate,FirstProductQuantity,FarmerLotnumber,ISNULL(SoldTotalQty,0) as soldMIE,ISNULL(SoldTotalQty,0) as SoldOut from tblPlantationDetails where FarmerId='" + FarmerID + "'"));
}
public static DataTable GetInspectionBasedonFarmerName(string Farmername)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT far.FarmerId,far.FirstName as 'FarmerName',far.FarmerCode,far.City_Village AS 'FarmerVillage',emp.EmployeeId AS 'InspectorID',emp.EmployeeFristName AS 'InspectorName', ins.PlanDate,ins.VisitDate AS 'VisitedDate',ins.InspectionID,SUM( ffd.PlotArea ) AS 'Total_Area' FROM dbo.tblInspection ins INNER JOIN dbo.tblEmployeeDetails emp ON ins.EmployeeID = emp.EmployeeId INNER JOIN dbo.tblFarmerDetails far ON ins.FarmerID = far.FarmerId LEFT JOIN dbo.tblFarmerFarmDetails ffd ON ins.FarmerID = ffd.FarmerID WHERE far.FirstName='" + Farmername + "'GROUP BY far.FarmerId,far.FirstName ,far.FarmerCode,far.City_Village ,emp.EmployeeId ,emp.EmployeeFristName, ins.PlanDate,ins.VisitDate ,ins.InspectionID "));
}
public static DataTable GetPlotandInputinfo(string farmerID, int Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select ffd.FarmID,ffd.FarmerID,ffd.AreaCode,ffd.PlotArea,pd.ProductName as Maincrop,ffd.IsInterCrop as IIC,pd.ProductId,pld.PlantationDate,FirstHarvestDate,SecondHarvestDate,ii.IMMaterial,ii.IMSource,it.IM_MT_HC,it.IMDays,it.IMPeriod,it.IMPlanting from tblFarmerFarmDetails ffd, tblProductDetails pd,tblPlantationDetails pld,tblInputTransaction it,tblInputInformation ii where ffd.ParentFarmID > 0 and ffd.FarmerID='" + farmerID + "' and FYear='" + Year + "' and ffd.productID=pd.ProductId and pld.ProductId=pd.ProductId and ffd.FarmID=pld.FarmID and ii.InputMID =it.InputMID and ii.ProductID=ffd.productID and pd.ProductId= ii.ProductID and ii.[delete]=0 and it.[delete]=0 order by areacode"));
}
//public static DataTable HolidaysCount(int year)
//{
// MudarDBHelper mdbh = MudarDBHelper.Instance;
// return mdbh.ExecuteDataTable("select count(*) from tblHolidayList where HolidayYear=" + year);
//}
public static DataTable Holidays(int year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select HolidayID, CONVERT(varchar(10), HolidayDate,101) AS 'HolidayDate', HolidayYear from tblHolidayList where HolidayYear=year and [delete]=0"));
}
public static DataTable GetplotandInsectinfo(string farmerID, int Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select ffd.FarmID,ffd.FarmerID,ffd.AreaCode,ffd.PlotArea,pd.ProductName as Maincrop,ffd.IsInterCrop as IIC,pd.ProductId,pld.PlantationDate,FirstHarvestDate,SecondHarvestDate,imi.InsectName,IMIExpected,IMIPreventionMaterial,IMISource,IMIBillNo,imt.InsectM_MT_HC,InsectMDays,InsectMPeriod,InsectMPlanting from tblFarmerFarmDetails ffd, tblProductDetails pd,tblPlantationDetails pld,tblInsectsManagementInfo imi,tblInsectsManagementInfoTransaction imt where ffd.ParentFarmID > 0 and ffd.FarmerID='" + farmerID + "' and FYear='" + Year + "' and ffd.productID=pd.ProductId and pld.ProductId=pd.ProductId and ffd.FarmID=pld.FarmID and imi.InsectMIID= imt.InsectMIID AND imi.ProductID=ffd.productID and pd.ProductId= imi.ProductID and imt.[delete]=0 and imi.[delete]=0 order by areacode"));
}
public static DataTable GetProductDetails(string Productvalue)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT p.*, s.SeasonName, c.CategoryName From dbo.tblProductDetails p,dbo.tblSeason s, dbo.tblCategory c WHERE (p.ProductName LIKE '%" + Productvalue + "%' OR p.ProductId LIKE '%" + Productvalue + "%') AND s.SeasonID = p.SeasonID AND c.CategoryId = p.CategoryId AND p.[Delete] =0"));
}
