public static DataTable GetBlendDetails(string ProductID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string Code = string.Empty;
if (ProductID == "All")
{
DataTable dt = mdbh.ExecuteDataTable("select bt.CreatedDate,cl.OrderID,prd.ProductName,bt.Lotnumber,bt.BlendQty,bqty,Blending_BatchID,bqty,' ' as 'BlendedBy',' ' as 'Remarks',prd.ProductId from tblBlendingTransaction bt left join tblBlending bl on bt.BlendingID= bl.BlendingID left join tblProductDetails pd on pd.ProductId = bt.ProductID left join tblCollection cl on cl.CollectionID = bl.CollectionID left join tblProductDetails prd on prd.ProductId=bt.ProductID where bt.[Delete]=0 and bt.Lotnumber like '%x15%' or bt.Lotnumber like '%Y15%' or bt.Lotnumber like '%Z15%' order by bt.CreatedDate");
return(dt);
}
else
{
if (ProductID == "1")
{
Code = "X15";
}
if (ProductID == "2")
{
Code = "Y15";
}
if (ProductID == "3")
{
Code = "Z15";
}
DataTable dt = mdbh.ExecuteDataTable("select bt.CreatedDate,cl.OrderID,prd.ProductName,bt.Lotnumber,bt.BlendQty,bqty,Blending_BatchID,bqty as 'LotQty(KG)',' ' as 'BlendedBy',' ' as 'Remarks',prd.ProductId from tblBlendingTransaction bt left join tblBlending bl on bt.BlendingID= bl.BlendingID left join tblProductDetails pd on pd.ProductId='" + ProductID + "'AND bt.ProductID='" + ProductID + "' left join tblCollection cl on cl.CollectionID = bl.CollectionID left join tblProductDetails prd on prd.ProductId='" + ProductID + "' and bt.ProductID='" + ProductID + "' where bt.[Delete]=0 and bt.Lotnumber like '%" + Code + "%' order by bt.CreatedDate");
return(dt);
}
}
public static DataTable GetNewallcollectionList(string productID, string code)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (productID == "All")
{
DataTable dt = mdbh.ExecuteDataTable("select ct.*,c.OrderID, ProductName from tblCollectionTransaction ct left join tblCollection c on c.CollectionID = ct.CollectionID left join tblFarmerDetails fd on fd.FarmerId= ct.FarmerID left join tblProductDetails pd on pd.ProductId = ct.ProductID where ct.[Delete]=0 and Lotnumber like '%x15%' or Lotnumber like '%Y15%' or Lotnumber like '%Z15%'");
return(dt);
}
else
{
DataTable dtp = mdbh.ExecuteDataTable("select ProductCode from tblProductDetails where ProductID='" + productID + "'");
string cc = dtp.Rows[0]["ProductCode"].ToString() + code;
if (productID == "2")
{
DataTable dt = mdbh.ExecuteDataTable("select ct.*,c.OrderID,'Organic Cornmint Oil ' as ProductName from tblCollectionTransaction ct left join tblCollection c on c.CollectionID = ct.CollectionID left join tblFarmerDetails fd on fd.FarmerId= ct.FarmerID left join tblProductDetails pd on pd.ProductId = ct.ProductID where pd.ProductId in ('2','4') and ct.ProductID in ('2','4') and ct.[Delete]=0 and Lotnumber like '%" + cc + "%'");
return(dt);
}
else
{
DataTable dt = mdbh.ExecuteDataTable("select ct.*,c.OrderID, ProductName from tblCollectionTransaction ct left join tblCollection c on c.CollectionID = ct.CollectionID left join tblFarmerDetails fd on fd.FarmerId= ct.FarmerID left join tblProductDetails pd on pd.ProductId = ct.ProductID where pd.ProductId='" + productID + "' and ct.ProductID='" + productID + "' and ct.[Delete]=0 and Lotnumber like '%" + cc + "%'");
return(dt);
}
}
}
public static DataTable GePackingDetails(string ProductID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string Code = string.Empty;
if (ProductID == "All")
{
DataTable dt = mdbh.ExecuteDataTable("select opd.*,cl.OrderID,bt.BQty,pd.ProductName,'' as'Labelspasted?','' as 'PackedBy','' as 'Remarks' from tblOrderPackingDetails opd left join tblCollection cl on opd.CollectionID=cl.CollectionID left join tblProductDetails pd on opd.ProductID=pd.ProductId left join tblBlendingTransaction bt on bt.Blending_BatchID=opd.LotNumber where opd.LotNumber like '%X15%' or opd.LotNumber like '%Y15%' OR opd.LotNumber like '%Z15%' order by opd.CreatedDate");
return(dt);
}
else
{
if (ProductID == "1")
{
Code = "X15";
}
if (ProductID == "2")
{
Code = "Y15";
}
if (ProductID == "3")
{
Code = "Z15";
}
DataTable dt = mdbh.ExecuteDataTable("select opd.*,cl.OrderID,bt.BQty,pd.ProductName,'' as'Labelspasted?','' as 'PackedBy','' as 'Remarks' from tblOrderPackingDetails opd left join tblCollection cl on opd.CollectionID=cl.CollectionID left join tblProductDetails pd on opd.ProductID='" + ProductID + "' and pd.ProductId='" + ProductID + "' left join tblBlendingTransaction bt on bt.Blending_BatchID=opd.LotNumber where opd.LotNumber like '%" + Code + "%' order by opd.CreatedDate");
return(dt);
}
}
public static DataTable GeDispatchDetails(string ProductID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string Code = string.Empty;
if (ProductID == "All")
{
DataTable dt = mdbh.ExecuteDataTable("select bo.BOdispatchDate,opd.*,cl.OrderID,PD.ProductName,bt.BQty,'Mudar India Exports' as'Dispatchedto','' as 'Vehicleno','Good' as 'Vehiclecondition','' as 'Dispatchedby','' as 'Remarks' from tblBranchOrder bo left join tblCollection cl on bo.OrderID=cl.OrderID left join tblOrderPackingDetails opd on opd.CollectionID=cl.CollectionID left join tblProductDetails pd on opd.ProductID=pd.ProductId left join tblBlendingTransaction bt on bt.Blending_BatchID=opd.LotNumber where opd.LotNumber like '%X15%' or opd.LotNumber like '%Y15%' OR opd.LotNumber like '%Z15%' order by bo.BOdispatchDate");
return(dt);
}
else
{
if (ProductID == "1")
{
Code = "X15";
}
if (ProductID == "2")
{
Code = "Y15";
}
if (ProductID == "3")
{
Code = "Z15";
}
DataTable dt = mdbh.ExecuteDataTable("select bo.BOdispatchDate,opd.*,cl.OrderID,PD.ProductName,bt.BQty,'Mudar India Exports' as'Dispatchedto','' as 'Vehicleno','Good' as 'Vehiclecondition','' as 'Dispatchedby','' as 'Remarks' from tblBranchOrder bo left join tblCollection cl on bo.OrderID=cl.OrderID left join tblOrderPackingDetails opd on opd.CollectionID=cl.CollectionID left join tblProductDetails pd on opd.ProductID='" + ProductID + "' and pd.ProductId='" + ProductID + "' left join tblBlendingTransaction bt on bt.Blending_BatchID=opd.LotNumber where opd.LotNumber like '%" + Code + "%' order by bo.BOdispatchDate");
return(dt);
}
}
public static DataTable GetDataP3(string ICSType, string Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable DT = mdbh.ExecuteDataTable("Select * from tblSeason where SeasonYear='" + Year + "'");
DateTime Date = DateTime.Now.AddHours(12).AddMinutes(30).AddDays(-1);
return(mdbh.ExecuteDataTable("select sum(ISNULL(TotalProductQuantity,0.0)) as Total,sum(ISNULL(SoldTotalQty,0.0))AS collected,sum(ISNULL(TotalProductQuantity,0.0)) - sum(ISNULL(SoldTotalQty,0.0))as Avil from tblPlantationDetails pd,tblFarmerDetails fd where SeasonID='" + DT.Rows[0]["SeasonID"] + "' and ProductId='3' and pd.FarmerId=fd.FarmerId and fd.ICSType='" + ICSType + "' AND pd.FirstDistillationDate < '" + Date + "' UNION select sum(ISNULL(TotalProductQuantity,0.0)) as Total,sum(ISNULL(SoldTotalQty,0.0))AS collected,sum(ISNULL(TotalProductQuantity,0.0)) - sum(ISNULL(SoldTotalQty,0.0))as Avil from tblPlantationDetails pd,tblFarmerDetails fd where SeasonID='" + DT.Rows[0]["SeasonID"] + "' and ProductId='3' and pd.FarmerId=fd.FarmerId and fd.ICSType='" + ICSType + "' AND pd.SecondDistillationDate < '" + Date + "'"));
}
public static DataTable GetIcsandNonSuppliers(bool ics)
{
MudarDBHelper mbdh = MudarDBHelper.Instance;
if (ics)
{
return(mbdh.ExecuteDataTable("select SupplierId,SupplierCompanyName from tblSupplierDetails where suppliertype<>''"));
}
else
{
return(mbdh.ExecuteDataTable("select SupplierId,SupplierCompanyName from tblSupplierDetails where suppliertype=''"));
}
}
public static DataTable GetSelectedCollectionDate(string Date, int productID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (productID == 2)
{
return(mdbh.ExecuteDataTable("select ct.*,pd.ProductName from tblCollectionTransaction ct,tblProductDetails pd where ct.ProductId in (2,4) and pd.ProductID in (2,4) and ct.createddate='" + Date + "'"));
}
else
{
return(mdbh.ExecuteDataTable("select ct.*,pd.ProductName from tblCollectionTransaction ct,tblProductDetails pd where ct.ProductId='" + productID + "' and pd.ProductID='" + productID + "' and ct.createddate='" + Date + "'"));
}
}
//Report for farmer Details
public static DataTable GetFarmerDetails(string Village)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (Village != "All")
{
return(mdbh.ExecuteDataTable("(select farmer.FirstName,farmer.City_Village,farmer.TotalAreaInHectares,farmer.Organic,farmer.FarmerCode,farmer.[Presidnt],farmer.FarmerId,farmer.CreatedDate,farmer.NumberOfPlots from tblFarmerDetails farmer where farmer.[Delete]=0 and farmer.City_Village='" + Village + "') union (select '' as FirstName, '' as City_Village, farm.PlotArea as TotalAreaInHectares,farmer.Organic,farm.areacode,farmer.[Presidnt],farm.FarmerID,farmer.CreatedDate,farmer.NumberOfPlots from tblFarmerFarmDetails farm join tblFarmerDetails farmer on farm.FarmerID=farmer.FarmerId where farmer.City_Village='" + Village + "' and farm.[Delete]=0 and farmer.[Delete]=0 and farm.ParentFarmID=0) order by CreatedDate,FarmerCode asc"));
}
else
{
return(mdbh.ExecuteDataTable("(select farmer.FirstName,farmer.City_Village,farmer.TotalAreaInHectares,farmer.Organic,farmer.FarmerCode,farmer.[Presidnt],farmer.FarmerId,farmer.CreatedDate,farmer.NumberOfPlots from tblFarmerDetails farmer where farmer.[Delete]=0 ) union (select '' as FirstName,'' as City_Village, farm.PlotArea as TotalAreaInHectares,farmer.Organic,farm.areacode,farmer.[Presidnt],farm.FarmerID,farmer.CreatedDate,farmer.NumberOfPlots from tblFarmerFarmDetails farm join tblFarmerDetails farmer on farm.FarmerID=farmer.FarmerId where farm.[Delete]=0 and farmer.[Delete]=0 and farm.ParentFarmID=0) order by CreatedDate,FarmerCode asc"));
}
}
public static DataTable GetDisitlationUnits(string UnitID, string ICSType, string Year)
{
DateTime Date = DateTime.Now.AddHours(12).AddMinutes(30).AddDays(-1);
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (UnitID == "All")
{
return(mdbh.ExecuteDataTable("select pd.ProductId,ui.Ucode,FirstDistillationDate as [Date],FirstName,AreaCode,prd.ProductName,'1' as Cut,FirstHerbaga,FirstProductQuantity,FarmerLotnumber,'yes' as whetherdrumsealed,' ' as inchargeperson from tblFarmerFarmDetails ffd,tblPlantationDetails pd,tblFarmerDetails fd,tblProductDetails prd,tblUnitInformation ui where ffd.FarmID=pd.FarmID and fd.FarmerId=pd.FarmerId and fd.FarmerId=ffd.FarmerID and ffd.FYear='" + Year + "' and pd.ProductId=prd.ProductId and ffd.productID=prd.ProductId and pd.FirstUnitId=ui.UnitId and fd.ICSType='" + ICSType + "' and pd.FirstDistillationDate < '" + Date + "' union select pd.ProductId,ui.Ucode,SecondDistillationDate as [Date],FirstName,AreaCode,prd.ProductName,'2' as Cut,SecondHerbaga,SecondProductQuantity,FarmerLotnumber,'yes' as whetherdrumsealed,' ' as inchargeperson from tblFarmerFarmDetails ffd,tblPlantationDetails pd,tblFarmerDetails fd,tblProductDetails prd,tblUnitInformation ui where ffd.FarmID=pd.FarmID and fd.FarmerId=pd.FarmerId and fd.FarmerId=ffd.FarmerID and ffd.FYear='" + Year + "' and pd.ProductId=prd.ProductId and ffd.productID=prd.ProductId and pd.SecondUnitId = ui.UnitId and fd.ICSType='" + ICSType + "' and pd.SecondDistillationDate < '" + Date + "'"));
}
else
{
return(mdbh.ExecuteDataTable("select ui.Ucode,FirstDistillationDate as [Date],FirstName,AreaCode,prd.ProductName,'1' as Cut,FirstHerbaga,FirstProductQuantity,FarmerLotnumber,'yes' as whetherdrumsealed,' ' as inchargeperson from tblFarmerFarmDetails ffd,tblPlantationDetails pd,tblFarmerDetails fd,tblProductDetails prd,tblUnitInformation ui where ffd.FarmID=pd.FarmID and fd.FarmerId=pd.FarmerId and fd.FarmerId=ffd.FarmerID and ffd.FYear='" + Year + "' and pd.ProductId=prd.ProductId and ffd.productID=prd.ProductId and pd.FirstUnitId='" + UnitID + "' and ui.UnitId='" + UnitID + "' and fd.ICSType='" + ICSType + "' and pd.FirstDistillationDate < '" + Date + "' union select ui.Ucode,SecondDistillationDate as [Date],FirstName,AreaCode,prd.ProductName,'2' as Cut,SecondHerbaga,SecondProductQuantity,FarmerLotnumber,'yes' as whetherdrumsealed,' ' as inchargeperson from tblFarmerFarmDetails ffd,tblPlantationDetails pd,tblFarmerDetails fd,tblProductDetails prd,tblUnitInformation ui where ffd.FarmID=pd.FarmID and fd.FarmerId=pd.FarmerId and fd.FarmerId=ffd.FarmerID and ffd.FYear='" + Year + "' and pd.ProductId=prd.ProductId and ffd.productID=prd.ProductId and pd.SecondUnitId='" + UnitID + "' and ui.UnitId='" + UnitID + "' and fd.ICSType='" + ICSType + "' and pd.SecondDistillationDate < '" + Date + "'"));
}
}
public static DataTable GetSelectedBlendnDate(string Date, int productID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (productID == 2)
{
return(mdbh.ExecuteDataTable("select * from tblBlendingTransaction where ProductId in (2,4) and createddate='" + Date + "'"));
}
else
{
return(mdbh.ExecuteDataTable("select * from tblBlendingTransaction where ProductId='" + productID + "' and createddate='" + Date + "'"));
}
}
public static DataTable GetSelectedBlendQty(string Code, int productID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dtp = mdbh.ExecuteDataTable("select ProductCode from tblProductDetails where ProductID='" + productID + "'");
string cc = dtp.Rows[0]["ProductCode"].ToString() + Code;
if (productID == 2)
{
return(mdbh.ExecuteDataTable("select bt.*,'Organic Cornmint Oil 'as ProductName from tblBlendingTransaction bt where bt.ProductId in (2,4) and Lotnumber like '%" + cc + "%' "));
}
else
{
return(mdbh.ExecuteDataTable("select bt.*,pd.ProductName from tblBlendingTransaction bt,tblProductDetails pd where bt.ProductId='" + productID + "' and pd.ProductId='" + productID + "' and Lotnumber like '%" + cc + "%' "));
}
}
public static DataTable GetFarmerProdcts(int SeasonYear, int seasonID, int productID, string Village, string ICStype)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
if (Village != "All")
{
return(mdbh.ExecuteDataTable("SELECT fd.FarmerId,fd.FarmerCode,fd.FirstName,ISNULL(ffd.AreaCode,0) as PlotCode,ISNULL(ffd.PlotArea,0)as plotarea,ISNULL(ffdg.plotarea,0) as croparea,(ISNULL(ffd.plotarea,0)-((select ISNULL(SUM(PlotArea),0 )from tblFarmerFarmDetails ffdF,tblFarmerDetails fdF where productID > 0 AND ffdf.[Delete]=0 AND ffdF.FarmerID = fdF.FarmerId and ffdF.ParentFarmID = ffd.FarmID and ffdF.FYear ='" + SeasonYear + "'))) as Availablearea,fd.City_Village,fd.Organic,fd.InternalInspectorApproval,ffd.FarmID FROM tblFarmerDetails fd INNER JOIN tblSeasonDetails sd ON fd.FarmerId in (SELECT sd.FarmerID FROM tblSeasonDetails sd, tblProductDetails pd WHERE sd.SeasonYear='" + SeasonYear + "' AND pd.ProductId='" + productID + "' AND sd.ProductId='" + productID + "' AND sd.Result=1 AND sd.m_SeasonID ='" + seasonID + "') LEFT JOIN tblFarmerFarmDetails ffd ON fd.FarmerId = ffd.FarmerId and ffd.ParentFarmID = 0 LEFT JOIN tblFarmerFarmDetails FFDG ON fd.FarmerId = FFDG.FarmerId and ffdg.productID='" + productID + "' and ffdg.ParentFarmID = ffd.FarmID and ffdg.FYear='" + SeasonYear + "' WHERE fd.InternalInspectorApproval= 1 and fd.ICSType='" + ICStype + "' and fd.[Delete]=0 and fd.City_Village = '" + Village + "' GROUP BY ffd.AreaCode,ffd.farmid,fd.FarmerCode,fd.FirstName,fd.FarmerId,fd.TotalAreaInHectares,fd.City_Village,fd.Organic,fd.InternalInspectorApproval,ffd.PlotArea,ffdg.PlotArea order by ffd.FarmID asc"));
}
else
{
return(mdbh.ExecuteDataTable("SELECT fd.FarmerId,fd.FarmerCode,fd.FirstName,ISNULL(ffd.AreaCode,0) as PlotCode,ISNULL(ffd.PlotArea,0)as plotarea,ISNULL(ffdg.plotarea,0) as croparea,(ISNULL(ffd.plotarea,0)-((select ISNULL(SUM(PlotArea),0 )from tblFarmerFarmDetails ffdF,tblFarmerDetails fdF where productID > 0 AND ffdf.[Delete]=0 AND ffdF.FarmerID = fdF.FarmerId and ffdF.ParentFarmID = ffd.FarmID and ffdF.FYear ='" + SeasonYear + "'))) as Availablearea,fd.City_Village,fd.Organic,fd.InternalInspectorApproval,ffd.FarmID FROM tblFarmerDetails fd INNER JOIN tblSeasonDetails sd ON fd.FarmerId in (SELECT sd.FarmerID FROM tblSeasonDetails sd, tblProductDetails pd WHERE sd.SeasonYear='" + SeasonYear + "' AND pd.ProductId='" + productID + "' AND sd.ProductId='" + productID + "' AND sd.Result=1 AND sd.m_SeasonID ='" + seasonID + "') LEFT JOIN tblFarmerFarmDetails ffd ON fd.FarmerId = ffd.FarmerId and ffd.ParentFarmID = 0 LEFT JOIN tblFarmerFarmDetails FFDG ON fd.FarmerId = FFDG.FarmerId and ffdg.productID='" + productID + "' and ffdg.ParentFarmID = ffd.FarmID and ffdg.FYear='" + SeasonYear + "' WHERE fd.InternalInspectorApproval= 1 and fd.ICSType='" + ICStype + "' and fd.[Delete]=0 GROUP BY ffd.AreaCode,ffd.farmid,fd.FarmerCode,fd.FirstName,fd.FarmerId,fd.TotalAreaInHectares,fd.City_Village,fd.Organic,fd.InternalInspectorApproval,ffd.AreaCode,ffd.PlotArea,ffdg.PlotArea order by ffd.FarmID asc"));
}
//old ocde
//return mdbh.ExecuteDataTable("SELECT fd.FarmerId,fd.FarmerCode,fd.FirstName,ISNULL(ffd.AreaCode,0) as PlotCode,ISNULL(ffd.PlotArea,0)as cropareaISNULL((fd.TotalAreaInHectares - ffd.PlotArea),0) as Availablearea,fd.City_Village,fd.Organic,fd.InternalInspectorApproval FROM tblFarmerDetails fd INNER JOIN tblSeasonDetails sd ON fd.FarmerId in (SELECT sd.FarmerID FROM tblSeasonDetails sd, tblProductDetails pd WHERE sd.SeasonYear='" + SeasonYear + "' AND pd.ProductId='" + productID + "' AND sd.ProductId='" + productID + "' AND sd.Result=1 AND sd.m_SeasonID ='" + seasonID + "') LEFT JOIN tblFarmerFarmDetails ffd ON fd.FarmerId = ffd.FarmerId and ffd.ParentFarmID! = 0 WHERE fd.InternalInspectorApproval= 1 and fd.[Delete]=0 GROUP BY fd.FarmerId,fd.FarmerCode,fd.FirstName,fd.TotalAreaInHectares,fd.City_Village,fd.Organic,fd.InternalInspectorApproval,ffd.PlotArea");
}
public static DataTable GetSelectedCollectionDate(string Date, int productID, string Code)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dtp = mdbh.ExecuteDataTable("select ProductCode from tblProductDetails where ProductID='" + productID + "'");
string cc = dtp.Rows[0]["ProductCode"].ToString() + Code;
if (productID == 2)
{
return(mdbh.ExecuteDataTable("select ct.*,'Organic Cornmint Oil ' as ProductName from tblCollectionTransaction ct,tblProductDetails pd where ct.ProductId in (2,4) and pd.ProductID in (2,4) and Lotnumber like '%" + cc + "%' and ct.createddate='" + Date + "'"));
}
else
{
return(mdbh.ExecuteDataTable("select ct.*,pd.ProductName from tblCollectionTransaction ct,tblProductDetails pd where ct.ProductId='" + productID + "' and pd.ProductID='" + productID + "' and Lotnumber like '%" + cc + "%' and ct.createddate='" + Date + "'"));
}
}
public static DataTable GetCollectionDates(int productID, string code)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dtp = mdbh.ExecuteDataTable("select ProductCode from tblProductDetails where ProductID='" + productID + "'");
string cc = dtp.Rows[0]["ProductCode"].ToString() + code;
if (productID == 2)
{
return(mdbh.ExecuteDataTable("select distinct(createddate) from tblCollectionTransaction where ProductId in (2,4) and Lotnumber like '%" + cc + "%' order by createddate asc"));
}
else
{
return(mdbh.ExecuteDataTable("select distinct(createddate) from tblCollectionTransaction where ProductId='" + productID + "' and Lotnumber like '%" + cc + "%' order by createddate asc"));
}
}
public static DataTable FamerNameCodeArea(string Value, int TypeOfOperation)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = string.Empty;
sql = "SELECT ";
if (TypeOfOperation == 1)
{
sql += " [FirstName] ";
}
else if (TypeOfOperation == 2)
{
sql += " [FarmerCode] ";
}
else if (TypeOfOperation == 3)
{
sql += " [City_Village] ";
}
sql += " FROM tblFarmerDetails WHERE ";
if (TypeOfOperation == 1)
{
sql += " [FirstName] LIKE '%" + Value + "%'";
}
else if (TypeOfOperation == 2)
{
sql += " [FarmerCode] LIKE '%" + Value + "%'";
}
else if (TypeOfOperation == 3)
{
sql += " [City_Village] LIKE '%" + Value + "%'";
}
return(mdbh.ExecuteDataTable(sql));
}
public static DataTable ValidateUserGetData(string ULoginID, string UPassword)
{
//bool Result = false;
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("SELECT * FROM [vmUserLoginRoleDetails] WHERE UserLoginID COLLATE Latin1_General_CS_AS = '" + ULoginID + "' AND UserPassword COLLATE Latin1_General_CS_AS = '" + UPassword + "' "));
}
public static DataTable GetFreezeDetails()
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dt = mdbh.ExecuteDataTable("select StartDate,EndDate,OrderID,'Organic Cornmint Oil' as Productname,bt.Blending_BatchID,fz.FBatchID,Quntatiy as 'Qty',ft.FreezeQuantity as 'CrystalReceived',ft.freezeProductBatchID as 'CrystalLotnumber',ft1.FreezeQuantity,ft1.freezeProductBatchID,'' as 'Operator',''as 'Remarks' from tblFreeze fz LEFT JOIN tblFreezeTransaction ft ON fz.FreezeID=ft.FreezeID and ft.ProductID=4 LEFT JOIN tblFreezeTransaction ft1 ON fz.FreezeID=ft1.FreezeID and ft1.ProductID=6 LEFT JOIN tblBlendingTransaction bt on bt.BlendingTransactionID=fz.BlendingTransID where fz.[Delete] = 0 and ft.[Delete] =0 and ft.FreezeProductBatchID like '%C15%' OR ft.FreezeProductBatchID like '%D1815%' order by StartDate asc");
return(dt);
}
public static DataTable GetProductDetailsNew()
{
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 = p.SeasonID AND c.CategoryId = p.CategoryId and p.[Delete] =0");
return(mdbh.ExecuteDataTable("select pd.*,tc.CategoryName from tblProductDetails pd join tblCategory tc on pd.CategoryId=tc.CategoryId"));
}
public static DataTable GetFinicalYear()
{
MudarSPName sp = new MudarSPName();
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable(sp.sp_GetFinicalYear, "FYear"));
}
public static DataTable GetDiseInfoonProductID(string ProdutID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = "select di.DiseaseName,DMIExpected,DMIPreventionMaterial,DMISource,DMIBillNo,dt.DMIT_HC,DMIT_Days,DMIT_Period,DMIT_Planting from tblDiseaseManagementInfo di,tblDiseaseManagementInfoTransaction dt where di.DiseaseMID =dt.DiseaseMID AND di.ProductID ='" + ProdutID + "'";
return(mdbh.ExecuteDataTable(sql));
}
public static DataTable BuyerDetails(bool Approval)
{
MudarDBHelper mbdh = MudarDBHelper.Instance;
string value = Approval.ToString().ToLower() == "true" ? "1 ORDER BY BD.ModifiedDate DESC " : "0 ORDER BY BD.CREATEDDATE DESC ";
return(mbdh.ExecuteDataTable("SELECT bd.*, ul.UserLoginID, ul.UserPassword, ISNULL( bpt.FOB_India,0) AS 'FOB_India', ISNULL( bpt.CIF_Sea_By,0) AS 'CIF_Sea_By', ISNULL( bpt.CIF_Air_By_EuropeandEastUSA,0) AS 'CIF_Air_By_EuropeandEastUSA', ISNULL( bpt.CIF_AIR_By_WEST_USA, 0) AS 'CIF_AIR_By_WEST_USA',ISNULL( bpt.CIF_Seaport, 0) AS 'CIF_Seaport' FROM dbo.tblBuyerDetails bd LEFT JOIN dbo.tblUserLogin ul ON bd.BuyerId = ul.UserId LEFT JOIN dbo.tblBuyerPriceTermsDetails bpt ON bd.BuyerId = bpt.BuyerId WHERE bd.[Delete] = 0 AND Apporval = " + value));
}
public static DataTable FamerDetails(string farmerName, string farmerCode, string area)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = string.Empty;
sql = "SELECT TOP 1 * FROM tblFarmerDetails WHERE ";
if (!string.IsNullOrEmpty(farmerName))
{
sql += " [FirstName] LIKE '%" + farmerName + "%'";
}
if (!string.IsNullOrEmpty(farmerCode))
{
if (!string.IsNullOrEmpty(farmerName))
{
sql += " AND ";
}
sql += " [FarmerCode] LIKE '%" + farmerCode + "%'";
}
if (!string.IsNullOrEmpty(area))
{
if (!string.IsNullOrEmpty(farmerName) || !string.IsNullOrEmpty(farmerCode))
{
sql += " AND ";
}
sql += " [City_Village] LIKE '%" + area + "%'";
}
return(mdbh.ExecuteDataTable(sql));
}
public static List <int> GetSeasonProdIds(int seasonId)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dt = mdbh.ExecuteDataTable("select ProductId from tblSeasonProducts where SeasonId='" + seasonId + "'");
return(dt.AsEnumerable().Select(m => m.Field <int>("ProductId")).ToList());
}
public static DataTable GetAFLTotalProduction(string ICSCode, string Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = string.Empty;
sql = "select FirstName,City_Village,FarmerAPEDACode,TotalAreaInHectares,ffd.PlotArea as PA,";
sql += "isnull(FFDG.PlotArea,0) as CA,pd.PlantationDate,pd.FirstHarvestDate,pd.EstimationFHerbaga,pd.FirstHerbaga,pd.FirstDistillationDate,ui.Ucode as code1,pd.EstimationFProductQuantity,pd.FirstProductQuantity,";
sql += "pd.SecondHarvestDate,pd.EstimationSHerbaga,pd.SecondHerbaga,pd.SecondDistillationDate,ui.Ucode as code1,pd.EstimationSProductQuantity,pd.SecondProductQuantity,pd.TotalProductQuantity,";
sql += "isnull(FFDG1.PlotArea,0) as CA1,pd1.PlantationDate as PD,pd1.FirstHarvestDate as FHD,pd1.EstimationFHerbaga as EFH,pd1.FirstHerbaga as FH,pd1.FirstDistillationDate as FDD,ui1.Ucode as code1,pd1.EstimationFProductQuantity as EFPQ,pd1.FirstProductQuantity as FPQ,";
sql += "pd1.SecondHarvestDate as SHD,pd1.EstimationSHerbaga as ESH,pd1.SecondHerbaga as SH,pd1.SecondDistillationDate as SDD,ui1.Ucode as code1,pd1.EstimationSProductQuantity as ESPQ,pd1.SecondProductQuantity as SPQ,pd1.TotalProductQuantity as TPQ,";
sql += "isnull(FFDG2.PlotArea,0) as CA2,pd2.PlantationDate as PD2,pd2.FirstHarvestDate as FHD2,pd2.EstimationFHerbaga as EFH2,pd2.FirstHerbaga as FH2,pd2.FirstDistillationDate as FDD2,ui2.Ucode as code2,pd2.EstimationFProductQuantity as EFPQ2,pd2.FirstProductQuantity as FPQ2,";
sql += "pd2.SecondHarvestDate as SHD2,pd2.EstimationSHerbaga as ESH2,pd2.SecondHerbaga as SH2,pd2.SecondDistillationDate as SDD2,ui2.Ucode as code2,pd2.EstimationSProductQuantity as ESPQ2,pd2.SecondProductQuantity as SPQ2,pd2.TotalProductQuantity as TPQ2,";
//sql += "isnull(FFDG1.PlotArea,0) as CA2,pd2.PlantationDate as PD2,pd2.FirstHarvestDate,pd2.EstimationFHerbaga,pd2.FirstHerbaga,pd2.FirstDistillationDate,ui2.Ucode as code2,pd2.EstimationFProductQuantity,pd2.FirstProductQuantity,";
//sql += "pd2.SecondHarvestDate,pd2.EstimationSHerbaga,pd2.SecondHerbaga,pd2.SecondDistillationDate,ui2.Ucode as code2,pd2.EstimationSProductQuantity,pd2.SecondProductQuantity,pd2.TotalProductQuantity,";
sql += "0.000 as VC";
sql += " FROM tblFarmerDetails fd";
sql += " LEFT JOIN tblFarmerFarmDetails ffd ON fd.FarmerId = ffd.FarmerId and ffd.ParentFarmID = 0";
sql += " LEFT JOIN tblFarmerFarmDetails FFDG ON fd.FarmerId = FFDG.FarmerId and ffdg.productID='1' and ffdg.ParentFarmID = ffd.FarmID and ffdg.FYear='" + Year + "'";
sql += " LEFT JOIN tblFarmerFarmDetails FFDG1 ON fd.FarmerId = FFDG1.FarmerId and ffdg1.productID='2' and ffdg1.ParentFarmID = ffd.FarmID and ffdg1.FYear='" + Year + "'";
sql += " LEFT JOIN tblFarmerFarmDetails FFDG2 ON fd.FarmerId = FFDG2.FarmerId and ffdg2.productID='3' and ffdg2.ParentFarmID = ffd.FarmID and ffdg2.FYear='" + Year + "'";
sql += " LEFT JOIN tblPlantationDetails pd on fd.FarmerId = pd.FarmerId and ffdg.FarmID = pd.FarmID and ffdg.ParentFarmID >0 and pd.productID=1";
sql += " LEFT JOIN tblPlantationDetails pd1 on fd.FarmerId = pd1.FarmerId and ffdg1.FarmID = pd1.FarmID and ffdg1.ParentFarmID >0 and pd1.productID=2";
sql += " LEFT JOIN tblPlantationDetails pd2 on fd.FarmerId = pd2.FarmerId and ffdg2.FarmID = pd2.FarmID and ffdg2.ParentFarmID >0 and pd2.productID=3";
sql += " LEFT JOIN tblUnitInformation ui on pd.FirstUnitId=ui.UnitId";
sql += " LEFT JOIN tblUnitInformation ui1 on pd1.FirstUnitId=ui1.UnitId";
sql += " LEFT JOIN tblUnitInformation ui2 on pd2.FirstUnitId=ui2.UnitId";
sql += " WHERE fd.[Delete] = 0 and fd.ICSType ='" + ICSCode + "' order by fd.CreatedDate asc";
return(mdbh.ExecuteDataTable(sql));
}
public static DataTable GetProductionDataOnProductID(int ProductID, string Year, string ICStype)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
return(mdbh.ExecuteDataTable("select fd.farmercode,firstname,ffd.AreaCode,ffd.ParentFarmID,pd.PlantationId,pd.FarmID,PlantationDate,FirstDistillationDate as [DistillationDate],'I' AS Cut,PlantationArea,FirstHerbaga as Herbage,FirstProductQuantity as Qty,ISNULL(SoldTotalQty,0) AS SoldTotalQty,ISNULL((FirstProductQuantity-SoldTotalQty),0) as AvialQty from tblplantationdetails pd, tblFarmerFarmDetails ffd ,tblfarmerdetails fd where pd.ProductId='" + ProductID + "' and ffd.productID = '" + ProductID + "' and ffd.fyear='" + Year + "' and fd.ICSType in (" + ICStype + ") and pd.FarmID = ffd.FarmID and fd.farmerid= pd.farmerid union select fd.farmercode,firstname,ffd.AreaCode,ffd.ParentFarmID,pd.PlantationId,pd.FarmID,PlantationDate,SecondDistillationDate as [DistillationDate],'II' AS Cut,PlantationArea,SecondHerbaga as Herbage,(SecondProductQuantity),ISNULL(SoldTotalQty,0) AS SoldTotalQty,ISNULL((SecondProductQuantity-SoldTotalQty),0) as AvialQty from tblplantationdetails pd, tblFarmerFarmDetails ffd ,tblfarmerdetails fd where pd.ProductId='" + ProductID + "' and ffd.productID = '" + ProductID + "' and ffd.fyear='" + Year + "' and fd.ICSType in (" + ICStype + ") and pd.FarmID = ffd.FarmID and fd.farmerid= pd.farmerid"));
//return mdbh.ExecuteDataTable("select fd.farmercode,firstname,ffd.AreaCode,ffd.ParentFarmID,pd.PlantationId,pd.FarmID,PlantationDate,PlantationArea,TotalProductQuantity,ISNULL(SoldTotalQty,0) AS SoldTotalQty,ISNULL((TotalProductQuantity-SoldTotalQty),0) as AvialQty from tblplantationdetails pd, tblFarmerFarmDetails ffd ,tblfarmerdetails fd where pd.ProductId='" + ProductID + "' and ffd.productID = '" + ProductID + "' and ffd.fyear='"+Year+"' and pd.FarmID = ffd.FarmID and fd.farmerid= pd.farmerid order by ffd.FarmID asc");
}
//aslam by write by code 15-06-2013
public static DataTable GetProductDetailsByYear(string Year)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
// return mdbh.ExecuteDataTable("SELECT sd.*, pd.* FROM tblSeason sd, tblProductDetails pd WHERE SeasonYear = '" + Year + "' AND pd.SeasonID = sd.SeasonID ");
return(mdbh.ExecuteDataTable("select ts.*,tpd.* from tblSeason ts join tblSeasonProducts tsp on ts.SeasonID=tsp.SeasonId join tblProductDetails tpd on tsp.ProductId=tpd.ProductId where ts.SeasonYear='" + Year + "'"));
}
public static bool FieldRisk_Exist(string FarmerID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
DataTable dtFieldRisk = mdbh.ExecuteDataTable("SELECT * FROM tblFieldRisk_Farmer WHERE FarmerID = '" + FarmerID + "'");
return(dtFieldRisk.Rows.Count > 0 ? false : true);
}
public static DataTable GetInsectInfoonProduct(string ProdutID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = "select imi.InsectName,IMIExpected,IMIPreventionMaterial,IMISource,IMIBillNo,imt.InsectM_MT_HC,InsectMDays,InsectMPeriod,InsectMPlanting from tblInsectsManagementInfo imi,tblInsectsManagementInfoTransaction imt where imi.InsectMIID= imt.InsectMIID AND imi.ProductID='" + ProdutID + "'";
return(mdbh.ExecuteDataTable(sql));
}
public static DataTable GetMaxHistoryIDSupplierProducts(string ProductID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string Sql = "select ProductId,PriceMB,CreatedDate from tblProductPrice pp where ProductId='" + ProductID + "' and pricehistoryid =(select MAX(PriceHistoryId) from tblProductPrice where ProductId='" + ProductID + "')";
return(mdbh.ExecuteDataTable(Sql));
}
public static DataTable GetInputInfoonProductID(string ProdutID)
{
MudarDBHelper mdbh = MudarDBHelper.Instance;
string sql = "select ii.IMMaterial,ii.IMSource,it.IM_MT_HC,it.IMDays,it.IMPeriod,it.IMPlanting from tblInputTransaction it,tblInputInformation ii where ii.InputMID =it.InputMID and ii.ProductID='" + ProdutID + "' and ii.[delete]=0 and it.[delete]=0";
return(mdbh.ExecuteDataTable(sql));
}