public DataTable BalanceAllItems(int storeId, int year, int month, string selectedType)
        {
            IssueDoc iss = new IssueDoc();
            ReceiveDoc rec = new ReceiveDoc();
            Disposal dis = new Disposal();
            YearEnd yEnd = new YearEnd();
            Items itm = new Items();
            DataTable dtResult = new DataTable();
            string[] col = { "FullItemName", "BBalance", "BBAmount", "ReceivedQty", "ReceivedAmount", "IssuedQty", "IssuedAmount", "LossesQty", "LossesAmount", "AdjustedQty", "AdjustedAmount", "SOH", "SOHAmount", "Received", "ID", "CategoryId", "SubCategoryID" };
            int i = 0;
            foreach (string s in col)
            {
                if (i > 0)
                {
                    dtResult.Columns.Add(s, typeof(double));
                }
                else
                {
                    dtResult.Columns.Add(s);
                }
                i++;
            }
            DataTable dtItem = ((selectedType == "Drug") ? itm.GetAllItems(1) : itm.GetAllSupply());
            foreach (DataRow dr in dtItem.Rows)
            {
                string itemName = dr["FullItemName"].ToString();// +" - " + dr["DosageForm"].ToString() + " - " + dr["Strength"].ToString();
                int itemId = Convert.ToInt32(dr["ID"]);
                Int64 bb = yEnd.GetBBalance(year, storeId, itemId, month);
                double bbAmount = yEnd.GetBBalanceAmount(year, storeId, itemId, month);
                Int64 recQuant = rec.GetReceivedQuantityTillMonth(itemId, storeId, month, year);
                double recPrice = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
                Int64 issuedQuant = iss.GetIssuedQuantityTillMonth(itemId, storeId, month, year);
                double issPrice = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
                Int64 lossQuant = dis.GetLossesQuantityTillMonth(itemId, storeId, month, year);
                double lossAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
                Int64 adjQuant = dis.GetAdjustedQuantityTillMonth(itemId, storeId, month, year);
                double adjAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
                Int64 SOH = bb + recQuant + adjQuant - issuedQuant - lossQuant;
                double SOHAmount = (bbAmount + recPrice + adjAmount - issPrice) - lossAmount;
                if (SOHAmount < 0)
                {
                    ;
                }

                int Isrec = ((bb == 0 && recQuant == 0) ? 0 : 1);
                object[] obj = { itemName, bb, bbAmount, recQuant, recPrice, issuedQuant, issPrice, lossQuant, lossAmount, adjQuant, adjAmount, SOH, SOHAmount, Isrec, itemId, Convert.ToInt32(dr["CategoryId"]), Convert.ToInt32(dr["SubCategoryID"]) };
                dtResult.Rows.Add(obj);
            }
            return dtResult;
        }
Exemple #2
0
        /* public Int64 GetBBalance(int year, int storeId, int itemId)
         * {
         *  this.FlushData();
         *  this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year));
         *
         *  Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0;
         *
         *  return bb;
         * }*/

        public double GetBBalanceAmount(int year, int storeId, int itemId, int month)
        {
            //CALENDAR:
            this.FlushData();
            double bb = 0;

            EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
            //int bYear = ((month > 10) ? year : year - 1);
            int    bYear = ethioDate.StartOfFiscalYear.Year;
            string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",
                                         storeId, itemId, bYear);

            this.LoadFromRawSql(query);
            if (this.DataTable.Rows.Count > 0)
            {
                bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
                          ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
                          : 0);
            }
            else
            {
                string queryString =
                    String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId,
                                  itemId, year - 1);
                this.LoadFromRawSql(queryString);
                if (this.DataTable.Rows.Count > 0)
                {
                    bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
                              ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
                              : 0);
                }
                else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
                // to check if it is different year from current
                {
                    double     cons = 0;
                    IssueDoc   iss  = new IssueDoc();
                    ReceiveDoc rec  = new ReceiveDoc();
                    Disposal   dis  = new Disposal();
                    if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase
                    {
                        year = year - 1;
                    }
                    month = 10;
                    //}

                    double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
                    double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
                    double IssuedAmount   = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
                    double LostAmount     = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
                    cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount);
                    bb   = cons;
                }
            }
            if (bb < 0)
            {
            }
            return(bb);


            //this.FlushData();
            //this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1));

            //double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0;

            //return bb;
        }
        /* public Int64 GetBBalance(int year, int storeId, int itemId)
        {
            this.FlushData();
            this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",storeId,itemId,year));

            Int64 bb = ( this.DataTable.Rows.Count > 0)? Convert.ToInt64(this.DataTable.Rows[0]["BBalance"]):0;

            return bb;
        }*/
        public double GetBBalanceAmount(int year, int storeId, int itemId, int month)
        {
            //CALENDAR:
            this.FlushData();
            double bb = 0;
            EthiopianDate.EthiopianDate ethioDate = new EthiopianDate.EthiopianDate(year, month, 30);
            //int bYear = ((month > 10) ? year : year - 1);
            int bYear = ethioDate.StartOfFiscalYear.Year;
            string query = String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}",
                                         storeId, itemId, bYear);
            this.LoadFromRawSql(query);
            if (this.DataTable.Rows.Count > 0)
            {
                bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
                          ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
                          : 0);
            }
            else
            {
                string queryString =
                    String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId,
                                  itemId, year - 1);
                this.LoadFromRawSql(queryString);
                if (this.DataTable.Rows.Count > 0)
                {
                    bb = ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "")
                              ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"])
                              : 0);
                }
                else if (((year + 8) > DateTime.Now.Year) || (month > 10 && (year + 8) == DateTime.Now.Year))
                    // to check if it is different year from current
                {
                    double cons = 0;
                    IssueDoc iss = new IssueDoc();
                    ReceiveDoc rec = new ReceiveDoc();
                    Disposal dis = new Disposal();
                    if ((year + 8) > DateTime.Now.Year) // to check if it is hamle and Nehase
                        year = year - 1;
                    month = 10;
                    //}

                    double ReceivedAmount = rec.GetReceivedAmountTillMonth(itemId, storeId, month, year);
                    double AdjustedAmount = dis.GetAdjustedAmountTillMonth(itemId, storeId, month, year);
                    double IssuedAmount = iss.GetIssuedAmountTillMonth(itemId, storeId, month, year);
                    double LostAmount = dis.GetLossesAmountTillMonth(itemId, storeId, month, year);
                    cons = ((ReceivedAmount + AdjustedAmount - IssuedAmount) - LostAmount);
                    bb = cons;
                }
            }
            if (bb < 0)
            {

            }
            return bb;

            //this.FlushData();
            //this.LoadFromRawSql(String.Format("Select * from YearEnd where StoreID = {0} AND ItemID = {1} AND Year = {2}", storeId, itemId, year-1));

            //double bb = (this.DataTable.Rows.Count > 0) ? ((this.DataTable.Rows[0]["PhysicalInventoryPrice"].ToString() != "") ? Convert.ToInt64(this.DataTable.Rows[0]["PhysicalInventoryPrice"]) : 0) : 0;

            //return bb;
        }