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);
}
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);
}
}
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);
}
}
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;
}
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;
}
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;
}
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();
}
}
}
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;
}
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)
{
}
}
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;
}