Exemplo n.º 1
0
        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"));
        }
Exemplo n.º 2
0
        public static DataTable GetHolidaysList()
        {
            MudarDBHelper mdbh = MudarDBHelper.Instance;

            return(mdbh.ExecuteDataTable("SELECT HolidayID,HolidayDate,HolidayYear FROM tblHolidayList WHERE [Delete]= 0  ORDER BY HolidayDate ASC"));
        }
Exemplo n.º 3
0
        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"));
        }
Exemplo n.º 4
0
        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"));
        }
Exemplo n.º 5
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 "));
        }
Exemplo n.º 6
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"));
        }
Exemplo n.º 7
0
        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 + "'"));
        }
Exemplo n.º 8
0
        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 + "'"));
        }
Exemplo n.º 9
0
        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"));
        }
Exemplo n.º 10
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"));
        }
Exemplo n.º 11
0
        public static DataTable GetAllProducDetails()
        {
            MudarDBHelper mdbh = MudarDBHelper.Instance;

            return(mdbh.ExecuteDataTable("select pd.* from tblProductDetails pd where pd.[delete]=0"));
        }
Exemplo n.º 12
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);
        }
Exemplo n.º 13
0
        // 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"));
        }
Exemplo n.º 14
0
        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"));
        }
Exemplo n.º 15
0
        public static DataTable GetBranches()
        {
            MudarDBHelper mbdh = MudarDBHelper.Instance;

            return(mbdh.ExecuteDataTable("select BranchId,Bname from tblbranchdetails where other=1"));
        }
Exemplo n.º 16
0
        // 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 "));
        }
Exemplo n.º 17
0
        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"));
        }
Exemplo n.º 18
0
        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"));
        }
Exemplo n.º 19
0
        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 "));
        }
Exemplo n.º 20
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 + "'"));
        }
Exemplo n.º 21
0
        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 "));
        }
Exemplo n.º 22
0
        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"));
        }
Exemplo n.º 23
0
        //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"));
        }
Exemplo n.º 24
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"));
        }
Exemplo n.º 25
0
        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"));
        }