void CreateInventoryReport()
        {
            var ds = new StockMaster().GetDististinctItemsInStockMaster(); 
            int i = 0;
            var bl = new BlItemMaster();
            var stk = new StockMaster();
            var bls = new SalesManger();
            foreach (DataRow stockmasterRow in ds.Rows)
            {
                var dr = (Ledgers.InventoryVariationRow)  dt.NewRow();
                dr[0] = i++;
                var itm = stockmasterRow["ITEMNO"].ToString();
                dr.ITEMNO = itm;
                dr.ITEMNAME = bl.GetItemname(itm);
                dr.OPENING = bls.GetOpeningQty(itm);
                dr.RECEIVE = bls.GetReceivedQty(itm);
                dr.RETURN = bls.GetReturnQty(itm);
                dr.SALES = bls.GetSoldQty(itm);
                dr.TRANSFER = bls.GetTransferedQty(itm);
                dr.ADJQTY = bls.GetAdjQty(itm);
                dr.ACTUAL = (dr.OPENING + dr.ADJQTY  + dr.RECEIVE + (-1* dr.RETURN)) - (dr.SALES + dr.TRANSFER);
                dr.INSTOCK = stk.GetAvailableQty(itm);
              //  dr.STOCKID = int.Parse(stockmasterRow["STOCKID"].ToString());
                dt.Rows.Add(dr);
            }
            inventoryVariationBindingSource.DataSource = dt;
            fixstkdiff = false;
            

        }
        private void FrmNegativeStockReplace_Load(object sender, EventArgs e)
        {
            var n = new DsReports.NegativeStockDataTable();
            foreach(DataRow d  in GetDsInventory.Rows)
            {
                var newrow = n.NewRow();
                var qty = string.IsNullOrEmpty(d["QTY"].ToString()) ? 0 : int.Parse(d["QTY"].ToString());
                var stk = new StockMaster().GetAvailableQty(int.Parse(d[0].ToString()));
                newrow[0] = ClsGetData.GetItemname(d["ITEMNO"].ToString());
                newrow[1] = qty;
                newrow[2] = stk - qty;
                newrow[3] = stk ;
                n.Rows.Add(newrow);
            }
            gridControl1.DataSource = n;

        }
        private void simpleButton4_Click(object sender, EventArgs e)
        {
            string clnm = string.Empty;
            ClsGetData.DelConsolidateinventoryDataTable();

            dtcon = ClsGetData.GetConsolidateinventoryDataTable();
            cONSOLIDATEINVENTORYBindingSource.DataSource = dtcon;
            gridControl1.ResetBindings();

            var storecnt = checkedComboBoxEdit1.Properties.GetCheckedItems().ToString().Split(',');
            clnm = GetStorenames(storecnt);
            SetGridVisibleIndex(storecnt);
            //colPRGEN.VisibleIndex = 0;
            //colItemname.VisibleIndex = 1;
            //colItemno.VisibleIndex = 2;
            //colbarcode.VisibleIndex = 3;
            //colTOTAL.VisibleIndex = 4;
            //colPURQTY.VisibleIndex = 5;
             
            
            //foreach (var s in storecnt)
            //{
            //    clnm += s + ",";
            //    gridView1.Columns[i+4].VisibleIndex = i;
            //    gridView1.Columns[i+4].Visible = true;
            //    gridView1.Columns[i+4].Caption = s;
            //    i++;
            //}
            var i = 0;
            DsReports.ITEMLISTFORCONSOLIDATEINVDataTable dsitems = null;
            if (lookUpEdit2.EditValue == null && txtbarcode.EditValue == null && LookupSupplier.EditValue ==null)
            {
                dsitems = ClsGetData.ItemListForConsolidateInv(0,string.Empty);
            }
            if (comboBoxEdit1.SelectedIndex == 0 && lookUpEdit2.EditValue!=null)
            {
                dsitems = ClsGetData.ItemListForConsolidateInv(1,lookUpEdit2.EditValue.ToString());
            }
            if (comboBoxEdit1.SelectedIndex == 1 && txtbarcode.EditValue != null)
            {
                dsitems = ClsGetData.ItemListForConsolidateInv(2, txtbarcode.Text);
            }
            if (comboBoxEdit1.SelectedIndex == 2 && LookupSupplier.EditValue != null)
            {
                dsitems = ClsGetData.ItemListForConsolidateInv(3, txtbarcode.Text);
            }
            repositoryItemProgressBar1.Maximum = dsitems.Count;

            int k = 1;
            var connew = Utils.Connectionstring.Split(';');
            var bl = new StockMaster();
            
            if (dsitems != null)
                foreach (DsReports.ITEMLISTFORCONSOLIDATEINVRow  d in dsitems.Rows)
                {
                    progressBar1.EditValue = k;
                    Application.DoEvents();

                    k++;
                    i = 10;
                    var dr =(DsReports.CONSOLIDATEINVENTORYRow) dtcon.NewRow();
                    if(!d.IsSUPPLIERNAMENull())
                     dr.Suppliername = d.SUPPLIERNAME;
                    else
                    {
                        dr.Suppliername = "---";
                    }
                    dr.Itemname = d.ITEMNAME;
                    dr.Itemno = d.ITEMNO;

                    dr.ItemCategory = d.GROUPNAME;
                    dr.barcode = d.BARCODE;
                    dr.PURQTY = 0;
                    dr.RUNDATE = DateTime.Now.Date;
                    dr.PRGEN = false; 
                    dr.COLNAMES = clnm;
                    var toqty = 0;
                    foreach (string ss in storecnt)
                    {
                        var str = connew[0] + ";" + "Initial Catalog =" + ss + ";" + connew[2] + ";"   + connew[3] + ";" + connew[4] + ";";
                        var qty = bl.GetAvailableQty(d.ITEMNO, str, true);
                        toqty += qty;
                        dr[i] = qty;
                        dr.TOTAL = toqty;
                        i++;
                    }
                    dtcon.Rows.Add(dr);
                }
            cONSOLIDATEINVENTORYBindingSource.DataSource = dtcon;
           // COMMON.ClsGetData.SaveCosolidateInventoryRun(dtcon); 
        }
Beispiel #4
0
 void DoCreateOpeningStockMast(string sql, OleDbConnection con)
 {
     progressPanel1.Visible = true;
     try
     {
         var ds = new DataSet();
         var ta = new OleDbDataAdapter(sql, con);
         ta.Fill(ds);
         var dsM = new DsInventory();
         foreach (DataRow dr in ds.Tables[0].Rows)
         {
             Application.DoEvents();
             var row = (DsInventory.STOCKOPENINGRow)dsM.STOCKOPENING.NewRow();
             row.ITEMNO = dr["DCODE"].ToString();
             row.BARCODE = dr["BARCD"].ToString();
             row["TYEAR"] = DateTime.Now.Year;
             row["TDATE"] = DateTime.Now.ToString("yyyy-MM-dd");
             row.BEGQTY = int.Parse(dr["QTY"].ToString());
             if (!string.IsNullOrEmpty(dr["EXPIRY"].ToString()))
                 row.MINEXPDATE = DateTime.Parse(dr["EXPIRY"].ToString());
             if (!string.IsNullOrEmpty(dr["SPRICE"].ToString()))
                 row.MAXSPRICE = decimal.Parse(dr["SPRICE"].ToString());
             row["COMPANY"] = Utils.Company;
             row["TDATETIME"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
             dsM.STOCKOPENING.Rows.Add(row);
         }
         var ret = new StockMaster().UpdateOpeningStock(dsM.STOCKOPENING);
         if (ret == "1")
         {
             return;
         }
         if (ret != "1")
         {
             XtraMessageBox.Show(ret, "POS", MessageBoxButtons.OK, MessageBoxIcon.Error);
             return;
         }
     }
     catch (Exception ex)
     {
         XtraMessageBox.Show(ex.ToString(), "POS", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
 }
Beispiel #5
0
 private void simpleButton4_Click(object sender, EventArgs e)
 {
    // string str = "SELECT itemno,AVG(CPRICE) FROM  STOCKMASTER group by ITEMNO ";
     var ret = new StockMaster().UpdateAvgCostprice();
     if(ret=="1")
     {
         MessageBox.Show("Average Cost Updated");
     }
     else
     {
         MessageBox.Show(ret);
     }
 }
Beispiel #6
0
 bool DoCreateStockMast(string sql, OleDbConnection con)
 {
     progressPanel1.Visible = true;
     try
     {
         var ds = new DataSet();
         var ta = new OleDbDataAdapter(sql, con);
         ta.Fill(ds);
         var dsM = new DsInventory();
         var whid = new Functions().GetSalesStore();
         //dsMaster.Merge(new VIRETAILDAL.Masters.StockMaster().UpdateStockDataSet();
         int cnt = 0;
         foreach (DataRow dr in ds.Tables[0].Rows)
         {
             cnt++;
             Application.DoEvents();
             var row = (DsInventory.STOCKMASTERRow) dsM.STOCKMASTER.NewRow();
             row.ITEMNO= dr["DCODE"].ToString();
             row.BARCODE= dr["BARCD"].ToString();
             row.QTY = int.Parse(dr["QTY"].ToString());
             if (!string.IsNullOrEmpty(dr["EXPIRY"].ToString()))
                 row.EXPDATE = DateTime.Parse(dr["EXPIRY"].ToString());
             if (!string.IsNullOrEmpty(dr["SPRICE"].ToString()))
                 row.SPRICE = decimal.Parse(dr["SPRICE"].ToString());
             row.CPRICE= Math.Round(decimal.Parse(dr["CPRICE"].ToString()),2);
             row["STORETYPE"] = "S";
             row.SQTY = int.Parse(dr["QTY"].ToString());
             row["WHID"] = whid;
             row["COMPANY"] = Utils.Company;
             dsM.STOCKMASTER.Rows.Add(row);
             label1.Text = cnt.ToString();
         }
        label1.Text = cnt.ToString();
        var ret = new StockMaster().UpdateStockDataSet(dsM.STOCKMASTER);
        if (ret == "1")
        {
            return true;
        }
        if (ret != "1")
        {
            XtraMessageBox.Show(ret, "POS", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
        }
     }
     catch(Exception ex)
     {
         XtraMessageBox.Show(ex.ToString(),"POS",MessageBoxButtons.OK,MessageBoxIcon.Error);
     }
     return true;
 }
Beispiel #7
0
 public static int CheckStockAvailable(int stockid)
 {
     var cls = new StockMaster();
     return cls.GetAvailableQty(stockid);
 }
        public string DoUploadPromotionItems(Ledgers.PROMOITEMSDataTable dtp,bool isRemote)
        {
            string retval;
            SqlTransaction tran = null;
            try
            {
                //isRemote true mean data downloading in local machine , isremote false mean data uploading in sync
                if (isRemote)
                    _syncConstr = Utils.Connectionstring;

                var cn = new SqlConnection(_syncConstr);
                cn.Open();
                tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
                var ds = new Ledgers();

                //var dt = ds.COMMISIONMAST;
                var dtdetail = ds.PROMOITEMS;

                var taCommMain = new PROMOITEMSTableAdapter();
                taCommMain.AssignConnection(cn, tran);
                
                    var clstock = new StockMaster();
                    var clitem = new ItemMaster();
                
                foreach (Ledgers.PROMOITEMSRow ditemdetarow in dtp.Rows)
                {
                    var newdetrow = dtdetail.NewRow();


                    //dtdetail.TIDColumn.AutoIncrement = false;
                    //dtdetail.TIDColumn.ReadOnly = false;

                    if (isRemote)
                    {
                        clstock.UpdateSpriceByItemno(ditemdetarow.ITEMNO, ditemdetarow.NEWPRICE, ref cn, ref tran);
                        clitem.UpdateSpriceByItemno(ditemdetarow.ITEMNO, ditemdetarow.NEWPRICE, ref cn, ref tran);
                    }
                    foreach (DataColumn dc in dtp.Columns)
                    {
                        //if (!dc.ReadOnly)
                        //{
                        
                            newdetrow[dc.ColumnName] = ditemdetarow[dc.ColumnName];
                       // }
                    }
                    dtdetail.Rows.Add(newdetrow);
                }
                taCommMain.Update(dtdetail);
                 
                tran.Commit();
                retval = "1";
            }
            catch (Exception ex)
            {
                if (tran != null)
                    tran.Rollback();
                retval = ex.ToString();
            }
            return retval; 

            
        }
Beispiel #9
0
 public string DoUpdateOpeningStock(DateTime dt,int intyear)
 {
     string retval = string.Empty;
     try
     {
         var cn = new SqlConnection(_constr);
         cn.Open();
         string sql = "SELECT ITEMNO,BARCODE,SUM(PHYSICALSTOCK) AS QTY,MAX(SPRICE) as SPRICE,MIN(EXPDATE) as EXPIRY FROM YearEndInventory  WHERE TRANSFERED=1   GROUP BY ITEMNO,BARCODE,EXPdate  ";
         var ds = new DataSet();
         var ta = new SqlDataAdapter(sql, cn);
         ta.Fill(ds);
         var dsM = new DsInventory();
         foreach (DataRow dr in ds.Tables[0].Rows)
         {
             var row = (DsInventory.STOCKOPENINGRow) dsM.STOCKOPENING.NewRow();
             row.ITEMNO = dr["ITEMNO"].ToString();
             row.BARCODE = dr["BARCODE"].ToString();
             row["TYEAR"] = dt.Year;
             row["TDATE"] = dt.ToString("yyyy-MM-dd");
             row.BEGQTY = int.Parse(dr["QTY"].ToString());
             if (!string.IsNullOrEmpty(dr["EXPIRY"].ToString()))
                 row.MINEXPDATE = DateTime.Parse(dr["EXPIRY"].ToString());
             if (!string.IsNullOrEmpty(dr["SPRICE"].ToString()))
                 row.MAXSPRICE = decimal.Parse(dr["SPRICE"].ToString());
             row["COMPANY"] = Utils.Company;
             row["TDATETIME"] = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss");
             dsM.STOCKOPENING.Rows.Add(row);
         }
         var ret = new StockMaster().UpdateOpeningStock(dsM.STOCKOPENING);
         CLoseYearEndProcess(intyear);
         retval = "1";
     }
     catch (Exception ex)
     {
         retval = ex.ToString();
     }
     
     return retval;
 }
Beispiel #10
0
        public void ResetThePromotionPrice(DateTime dt1)
        {
            string retval = "0";
            SqlTransaction tran = null;
            var cn = new SqlConnection(_constr);
            try
            {
                cn.Open();

                var t = new Ledgers.PROMOITEMSDataTable();
                var tam = new LedgersTableAdapters.PROMOITEMSTableAdapter().FillByToDate(t, dt1);
                PromoitemsDataTable = t;

                tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
               // tam.AssignConnection(cn, tran);
                var clstock = new StockMaster();
                var clitem = new ItemMaster();
                foreach (Ledgers.PROMOITEMSRow dr in PromoitemsDataTable.Rows)
                {
                        //dr.POSTED = true;
                        clstock.UpdateSpriceByItemno(dr.ITEMNO, dr.OLDPRICE, ref cn, ref tran);
                        clitem.UpdateSpriceByItemno(dr.ITEMNO, dr.OLDPRICE, ref cn, ref tran);
                }
                tran.Commit();
              //  retval = "1";
            }
            catch (Exception ex)
            {
                retval = ex.ToString();
                if (tran != null && cn.State == ConnectionState.Open)
                {
                    tran.Rollback();
                }
            }
        }
Beispiel #11
0
        public string DoSave(bool isdownload=false)
        {
            string retval = "0";
            SqlTransaction tran = null;
            var cn = new SqlConnection(_constr);
            try
            {
                cn.Open();

                var tam = new LedgersTableAdapters.PROMOITEMSTableAdapter();
                
                tran = cn.BeginTransaction(IsolationLevel.ReadUncommitted);
                tam.AssignConnection(cn, tran);
                var clstock = new StockMaster();
                var clitem = new ItemMaster();
                foreach (Ledgers.PROMOITEMSRow dr in PromoitemsDataTable.Rows)
                {
                    if (!isdownload)
                    {
                        if (!dr.POSTED)
                        {
                            dr.POSTED = true;
                            clstock.UpdateSpriceByItemno(dr.ITEMNO, dr.NEWPRICE, ref cn, ref tran);
                            clitem.UpdateSpriceByItemno(dr.ITEMNO, dr.NEWPRICE, ref cn, ref tran);
                        }
                    }
                    else
                    {
                        dr.POSTED = true;
                        clstock.UpdateSpriceByItemno(dr.ITEMNO, dr.NEWPRICE, ref cn, ref tran);
                        clitem.UpdateSpriceByItemno(dr.ITEMNO, dr.NEWPRICE, ref cn, ref tran);

                    }
                }
                tam.Update(PromoitemsDataTable);
                tran.Commit();
                retval = "1";
            }
            catch (Exception ex)
            {
                retval = ex.ToString();
                if (tran != null && cn.State == ConnectionState.Open)
                {
                    tran.Rollback();
                }
            }
            return retval;
        }
Beispiel #12
0
        void AppendDataToGrid(string itemno,string barcode,string itemname, int grpid, int qty,string expdate,string machno,string inventoryby,int Yearclosed, bool  nostock=false)
        {
            try{
            var stk = new StockMaster().GetAvgMaxQtySpCpExpDtFromStock(itemno,Yearclosed);
            var dr = _dsyearend.NewRow();
            dr["ITEMNO"] = itemno;
            dr["BARCODE"] = barcode;
            dr["ITEMNAME"] = itemname;
            decimal cprice = 0;
            decimal sprice = 0;
            if (stk.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dss in stk.Tables[0].Rows)
                {
                    dr["INSTOCK"] = 0;
                    if (!string.IsNullOrEmpty(dss["QTY"].ToString()))
                        dr["INSTOCK"] = dss["QTY"];
                    dr["EXPDATE"] = dss["Expdate"];
                    if (!string.IsNullOrEmpty(dss["Sprice"].ToString()))
                        dr["SPRICE"]  = decimal.Parse(dss["Sprice"].ToString());
                    if (!string.IsNullOrEmpty(dss["Cprice"].ToString()))
                        dr["CPRICE"]   = decimal.Parse(dss["Cprice"].ToString());
                }
            }
            else
            {
                dr["INSTOCK"] = 0;
                dr["SPRICE"] = new ItemMaster().GetItemSalesPrice(itemno);
                dr["CPRICE"] = new ItemMaster().GetItemCostPrice(itemno);
                if(sprice > 0 && cprice==0)
                {
                    var disc = new ItemMaster().GetPurchaseDist(itemno);
                    cprice = sprice - ((sprice*disc)/100);
                }
                dr["CPRICE"] = cprice;
            }
            dr["PHYSICALSTOCK"] = qty;
            dr["CDATE"] = DateTime.Now;
            dr["NOMASTER"] = nostock;
            dr["MACHINENO"] = machno;
            dr["MUSER"] = inventoryby;
            dr["GROUPID"] = grpid;
            dr["COMP"] = Utils.Company;
            dr["TRANSFERED"] = 0;
            dr["INVENTORYYEAR"] = Yearclosed;
           _dsyearend.Rows.Add(dr);
            }
            catch(Exception ex)
            {
            }

        }
Beispiel #13
0
 public DsInventory.ITEMTRANSFERDETAILDataTable CreateItemtransferdetailsFrmMR(DsPurchase.PURCHASEREQUESTDataTable dst, string whid)
 {
     var ret = new DsInventory.ITEMTRANSFERDETAILDataTable();
     var dsS = new StockMaster().GetStockItemsForTransferDataTable(Utils.Company, whid);
     foreach (DsPurchase.PURCHASEREQUESTRow dr in dst.Rows)
     {
         if (!dr.IsISSUEDNull())
         {
             if (dr.ISSUED > 0)
             {
                 var q = from p in dsS where p.ITEMNO == dr.ITEMNO  orderby p.QTY ascending select p ;
                 var tb = q.CopyToDataTable();
                 if (tb.Rows.Count > 0)
                 {
                     AddItemsToItemMoveDetails(tb, ref ret, dr.ITEMNO, dr.ISSUED,dr.COMPANY);
                 }
             }
         }
     }
     return ret;
 }