private void updateOUT(InventoryRecord i)
{
string UpdateStatement = string.Format("UPDATE INVENTORY SET QOH = QOH - {0} WHERE LocalSKU = '{1}'", i.Quantity, i.SKU);
SqlCommand cmd = new SqlCommand(UpdateStatement, cn);
cmd.ExecuteNonQuery();
}
public static List <InventoryRecord> GetAllPerPerioud(DateTime DateFrom, DateTime DateTo)
{
List <InventoryRecord> irs = new List <InventoryRecord>();
try
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM POLog WHERE CretionDate >= @DateFrom And CretionDate <=@DateTo ORDER BY CretionDate DESC", Connection.DBConn);
cmd.Parameters.AddWithValue("@DateFrom", DateFrom.ToString("dd/MM/yyyy h:mm:ss tt"));
cmd.Parameters.AddWithValue("@DateTo", DateTo.ToString("dd/MM/yyyy h:mm:ss tt"));
OleDbDataReader dr = cmd.ExecuteReader();
InventoryRecord ir;
while (dr.Read())
{
ir = new InventoryRecord();
ir.ItemName = dr["ItemName"] == DBNull.Value ? "" : dr["ItemName"].ToString();
ir.SKU = dr["SKU"] == DBNull.Value ? "" : dr["SKU"].ToString();
ir.ReorderPoint = dr["ReorderPoint"] == DBNull.Value ? 0 : int.Parse(dr["ReorderPoint"].ToString());
ir.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
ir.QuantityNeeded = dr["QuantityNeeded"] == DBNull.Value ? 0 : int.Parse(dr["QuantityNeeded"].ToString());
ir.QtyToOrder = dr["QtyToOrder"] == DBNull.Value ? 0 : int.Parse(dr["QtyToOrder"].ToString());
ir.QOH = dr["QOH"] == DBNull.Value ? 0 : int.Parse(dr["QOH"].ToString());
ir.SupplierSKU = dr["SupplierSKU"] == DBNull.Value ? "" : dr["SupplierSKU"].ToString();
ir.CretionDate = dr["CretionDate"] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr["CretionDate"].ToString());
ir.ISordered = dr["ISordered"] == DBNull.Value ? false : Convert.ToBoolean(dr["ISordered"].ToString());
ir.ExtendedCost = dr["ExtendedCost"] == DBNull.Value ? 0 : decimal.Parse(dr["ExtendedCost"].ToString());
ir.Backordered = dr["Backordered"] == DBNull.Value ? 0 : decimal.Parse(dr["Backordered"].ToString());
ir.SoldL24M = dr["SoldL24M"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldL24M"].ToString());
ir.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
ir.SoldYTD = dr["SoldYTD"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldYTD"].ToString());
ir.SoldLast30 = dr["SoldLast30"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast30"].ToString());
ir.SoldLast14 = dr["SoldLast14"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast14"].ToString());
ir.OnOrder = dr["OnOrder"] == DBNull.Value ? 0 : decimal.Parse(dr["OnOrder"].ToString());
ir.Integer3 = dr["Integer3"] == DBNull.Value ? 0 : int.Parse(dr["Integer3"].ToString());
ir.Integer2 = dr["Integer2"] == DBNull.Value ? 0 : int.Parse(dr["Integer2"].ToString());
ir.Integer1 = dr["Integer1"] == DBNull.Value ? 0 : int.Parse(dr["Integer1"].ToString());
ir.Cost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
ir.HideCost = dr["HideCost"] == DBNull.Value ? 0 : decimal.Parse(dr["HideCost"].ToString());
ir.SoldLY = dr["SoldLY"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLY"].ToString());
ir.ISDeleted = dr["ISDeleted"] == DBNull.Value ? false : Convert.ToBoolean(dr["ISDeleted"].ToString());
ir.IsAuto = dr["IsAuto"] == DBNull.Value ? false : Convert.ToBoolean(dr["IsAuto"].ToString());
ir.ISDeleted = dr["ISDeleted"] == DBNull.Value ? false : Convert.ToBoolean(dr["ISDeleted"].ToString());
ir.CreateType = ir.IsAuto ? "Automatically" : "Individually";
ir.CretionDate = dr["CretionDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["CretionDate"]);
ir.Status = !ir.ISDeleted && !ir.ISordered ? "Pending" : ir.ISDeleted ? "Deleted" : "Ordered";
irs.Add(ir);
}
dr.Close();
dr.Dispose();
dr = null;
}
catch (Exception)
{
throw;
}
return(irs);
}
public static List <InventoryRecord> GetAllNotOrderedRecord(out string listOfLocalSKU)
{
List <InventoryRecord> irs = new List <InventoryRecord>();
listOfLocalSKU = "";
try
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM POLog WHERE ISordered = @ISordered And IsDeleted=@IsDeleted", Connection.DBConn);
cmd.Parameters.AddWithValue("@ISordered", false);
cmd.Parameters.AddWithValue("@IsDeleted", false);
OleDbDataReader dr = cmd.ExecuteReader();
InventoryRecord ir;
while (dr.Read())
{
ir = new InventoryRecord();
ir.ItemName = dr["ItemName"] == DBNull.Value ? "" : dr["ItemName"].ToString();
ir.SupplierID = dr["SupplierID"] == DBNull.Value ? "" : dr["SupplierID"].ToString();
ir.SKU = dr["SKU"] == DBNull.Value ? "" : dr["SKU"].ToString();
ir.ReorderPoint = dr["ReorderPoint"] == DBNull.Value ? 0 : int.Parse(dr["ReorderPoint"].ToString());
ir.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
ir.QuantityNeeded = dr["QuantityNeeded"] == DBNull.Value ? 0 : int.Parse(dr["QuantityNeeded"].ToString());
ir.QtyToOrder = dr["QtyToOrder"] == DBNull.Value ? 0 : int.Parse(dr["QtyToOrder"].ToString());
ir.QOH = dr["QOH"] == DBNull.Value ? 0 : int.Parse(dr["QOH"].ToString());
ir.SupplierSKU = dr["SupplierSKU"] == DBNull.Value ? "" : dr["SupplierSKU"].ToString();
ir.CretionDate = dr["CretionDate"] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr["CretionDate"].ToString());
ir.ISordered = dr["ISordered"] == DBNull.Value ? false : Convert.ToBoolean(dr["ISordered"].ToString());
ir.ExtendedCost = dr["ExtendedCost"] == DBNull.Value ? 0 : decimal.Parse(dr["ExtendedCost"].ToString());
ir.Backordered = dr["Backordered"] == DBNull.Value ? 0 : decimal.Parse(dr["Backordered"].ToString());
ir.SoldL24M = dr["SoldL24M"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldL24M"].ToString());
ir.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
ir.SoldYTD = dr["SoldYTD"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldYTD"].ToString());
ir.SoldLast30 = dr["SoldLast30"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast30"].ToString());
ir.SoldLast14 = dr["SoldLast14"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast14"].ToString());
ir.OnOrder = dr["OnOrder"] == DBNull.Value ? 0 : decimal.Parse(dr["OnOrder"].ToString());
ir.Integer3 = dr["Integer3"] == DBNull.Value ? 0 : int.Parse(dr["Integer3"].ToString());
ir.Integer2 = dr["Integer2"] == DBNull.Value ? 0 : int.Parse(dr["Integer2"].ToString());
ir.Integer1 = dr["Integer1"] == DBNull.Value ? 0 : int.Parse(dr["Integer1"].ToString());
ir.Cost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
ir.HideCost = dr["HideCost"] == DBNull.Value ? 0 : decimal.Parse(dr["HideCost"].ToString());
ir.SoldLY = dr["SoldLY"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLY"].ToString());
ir.OrderNumber = dr["OrderNumber"] == DBNull.Value ? 0 : int.Parse(dr["OrderNumber"].ToString());
listOfLocalSKU += "'" + ir.SKU + "',";
irs.Add(ir);
}
dr.Close();
dr.Dispose();
dr = null;
}
catch (Exception)
{
throw;
}
listOfLocalSKU = irs.Count > 0 ? listOfLocalSKU.Substring(0, listOfLocalSKU.Length - 1) : "";
return(irs);
}
public List <InventoryRecord> GetReorderQty(string SupplierID, string NumberOfDays, out string listOfLocalSKU)
{
listOfLocalSKU = "";
string StartDate = DateTime.Now.ToShortDateString();
string EndDate = DateTime.Now.AddDays(-1 * Convert.ToInt32(NumberOfDays)).ToShortDateString();
string SelectCommand = "SELECT SupplierID,i.[LocalSKU] as SKU,[ItemName],isup.SupplierSKU as SupplierSKU ,ReorderPoint,ReorderQuantity,QOH,i.Price10 as Cost,i.Integer1,i.Integer2,i.Integer3,i.Backordered,i.Price10 as ExtendedCost,isnull((select sum(Quantity) from POHistory poh where i.LocalSKU=poh.LocalSKU and type='E'),0) as OnOrder,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(day,-14,getdate()) and DetailDate <= getdate()),0) as SoldLast14,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(day,-30,getdate()) and DetailDate <= getdate()),0) as SoldLast30,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND year(od.DetailDate) = year(getdate())) ,0)as SoldYTD,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND YEAR(od.DetailDate)=year(getdate())-1 ),0) as SoldLY,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(MONTH,-24,getdate()) and DetailDate <= getdate()),0) as SoldL24M,isnull((SELECT MAX(Orders.OrderNumber) FROM [Order Details] INNER JOIN Orders ON ([Order Details].OrderNumber = Orders.OrderNumber) WHERE SKU =i.[LocalSKU] AND Approved = 'False' AND Cancelled = 'False'),0) as OrderNumber,isnull( (select SUM([QuantityNeeded]) as [QuantityNeeded] from [Order Details] od group by SKU ,DetailDate having SUM([QuantityNeeded])>0 and DetailDate between '" + DateTime.Now + "' and '" + EndDate + "' and od.SKU=i.LocalSKU),0) as QuantityNeeded FROM [dbo].[Inventory] i join InventorySuppliers isup on i.LocalSKU=isup.LocalSKU where ReorderQuantity > 0 And primarySupplier=1 and i.LocalSKU in (select LocalSKU from [InventorySuppliers] where SupplierID= " + SupplierID + " And primarySupplier=1 )";
SqlCommand cmd = new SqlCommand(SelectCommand, cn);
SqlDataReader dr = cmd.ExecuteReader();
List <InventoryRecord> inventoryrecords = new List <InventoryRecord>();
while (dr.Read())
{
int ReorderPoint = dr["ReorderPoint"] == DBNull.Value ? 0 : int.Parse(dr["ReorderPoint"].ToString());
int QuantityNeeded = dr["QuantityNeeded"] == DBNull.Value ? 0 : int.Parse(dr["QuantityNeeded"].ToString());
int QOH = dr["QOH"] == DBNull.Value ? 0 : int.Parse(dr["QOH"].ToString());
if ((ReorderPoint + QuantityNeeded) >= QOH)
{
InventoryRecord inventoryrecord = new InventoryRecord();
inventoryrecord.SupplierID = dr["SupplierID"] == DBNull.Value ? "" : dr["SupplierID"].ToString();
inventoryrecord.SKU = dr["SKU"] == DBNull.Value ? "" : dr["SKU"].ToString();
inventoryrecord.SupplierSKU = dr["SupplierSKU"] == DBNull.Value ? "" : dr["SupplierSKU"].ToString();
inventoryrecord.ItemName = dr["ItemName"] == DBNull.Value ? "" : dr["ItemName"].ToString();
inventoryrecord.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
inventoryrecord.ExtendedCost = dr["ExtendedCost"] == DBNull.Value ? 0 : decimal.Parse(dr["ExtendedCost"].ToString());
inventoryrecord.Backordered = dr["Backordered"] == DBNull.Value ? 0 : decimal.Parse(dr["Backordered"].ToString());
inventoryrecord.SoldL24M = dr["SoldL24M"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldL24M"].ToString());
inventoryrecord.SoldLY = dr["SoldLY"] == DBNull.Value ? 0 : int.Parse(dr["SoldLY"].ToString());
inventoryrecord.SoldYTD = dr["SoldYTD"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldYTD"].ToString());
inventoryrecord.SoldLast30 = dr["SoldLast30"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast30"].ToString());
inventoryrecord.SoldLast14 = dr["SoldLast14"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast14"].ToString());
inventoryrecord.OnOrder = dr["OnOrder"] == DBNull.Value ? 0 : decimal.Parse(dr["OnOrder"].ToString());
inventoryrecord.Integer3 = dr["Integer3"] == DBNull.Value ? 0 : int.Parse(dr["Integer3"].ToString());
inventoryrecord.Integer2 = dr["Integer2"] == DBNull.Value ? 0 : int.Parse(dr["Integer2"].ToString());
inventoryrecord.Integer1 = dr["Integer1"] == DBNull.Value ? 0 : int.Parse(dr["Integer1"].ToString());
inventoryrecord.OrderNumber = dr["OrderNumber"] == DBNull.Value ? 0 : int.Parse(dr["OrderNumber"].ToString());
inventoryrecord.Cost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
inventoryrecord.HideCost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
inventoryrecord.QOH = QOH;
inventoryrecord.ReorderPoint = ReorderPoint;
inventoryrecord.QuantityNeeded = QuantityNeeded;
inventoryrecord.QtyToOrder = QuantityNeeded + inventoryrecord.ReorderQuantity;
inventoryrecord.SupplierID = SupplierID;
inventoryrecord.ISordered = true;
listOfLocalSKU += "'" + inventoryrecord.SKU + "',";
inventoryrecords.Add(inventoryrecord);
}
}
dr.Close();
dr.Dispose();
dr = null;
listOfLocalSKU = inventoryrecords.Count > 0 ? listOfLocalSKU.Substring(0, listOfLocalSKU.Length - 1) : "";
return(inventoryrecords);
}
public void AddToLog(InventoryRecord IR, string TrType)
{
if (string.IsNullOrEmpty(LogKeyIN) && TrType == "IN")
{
LogKeyIN = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("00") + DateTime.Now.Day.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + DateTime.Now.Millisecond.ToString();
}
if (string.IsNullOrEmpty(LogKeyOUT) && TrType == "OUT")
{
LogKeyOUT = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString("00") + DateTime.Now.Day.ToString("00") + DateTime.Now.Hour.ToString("00") + DateTime.Now.Minute.ToString("00") + DateTime.Now.Second.ToString("00") + DateTime.Now.Millisecond.ToString();
}
OleDbCommand Cmd = new OleDbCommand("insert into [Log](SKU,Barcode,ProductName,QOH,OrderNumber,Quantity,Price,Price3,Location,OrderID,[Status],LogType) values('" + IR.SKU + "','" + IR.Barcode + "','" + IR.ProductName + "','" + IR.QOH + "','" + IR.OrderNumber + "','1','" + IR.Price + "','" + IR.Price3 + "','" + IR.Location + "','" + (TrType == "IN"?LogKeyIN:LogKeyOUT) + "','false','" + TrType + "')", cn);
Cmd.ExecuteNonQuery();
}
public static void Insert(InventoryRecord inventoryrecord, int ItemNumber, string cancelBy)
{
SqlConnection con = cn;
SqlCommand cmd = new SqlCommand("insert into POHistory([Type],PONumber,ItemNumber,[Date],EnteredBy,Quantity,Cost,LocalSKU,Lot,PiecesPerLot,NumberOfLots,TempQuantity,SuppliersSKU) values ('E',( select MAX(PONumber) from PurchaseOrders),@ItemNumber,GETDATE(),@EnteredBy,@Quantity,@Cost,@LocalSKU,'pieces',1,@NumberOfLots,@TempQuantity,@SuppliersSKU)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ItemNumber", ItemNumber);
cmd.Parameters.AddWithValue("@EnteredBy", cancelBy);
cmd.Parameters.AddWithValue("@Quantity", inventoryrecord.QtyToOrder);
cmd.Parameters.AddWithValue("@Cost", inventoryrecord.Cost);
cmd.Parameters.AddWithValue("@LocalSKU", inventoryrecord.SKU);
cmd.Parameters.AddWithValue("@NumberOfLots", inventoryrecord.QtyToOrder);
cmd.Parameters.AddWithValue("@TempQuantity", inventoryrecord.QtyToOrder);
cmd.Parameters.AddWithValue("@SuppliersSKU", inventoryrecord.SupplierSKU);
int x = cmd.ExecuteNonQuery();
}
public static void Insert(InventoryRecord inventoryrecord, int ItemNumber, DateTime DateExpected)
{
SqlConnection con = cn;
SqlCommand cmd = new SqlCommand(" insert into PurchaseOrderDetails(PONumber,ItemNumber,Ordered,Received,ExpectedCost,OrderNumber,DateExpected,Lot,PiecesPerLot,NumberOfLots,LocalSKU,SuppliersSKU) values (( select MAX(PONumber) from PurchaseOrders),@ItemNumber,@Ordered,0,@ExpectedCost,@OrderNumber,@DateExpected,'pieces',1,@NumberOfLots,@LocalSKU,@SuppliersSKU)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@ItemNumber", ItemNumber);
cmd.Parameters.AddWithValue("@Ordered", inventoryrecord.QtyToOrder);
cmd.Parameters.AddWithValue("@ExpectedCost", inventoryrecord.ExtendedCost);
cmd.Parameters.AddWithValue("@OrderNumber", inventoryrecord.OrderNumber);
cmd.Parameters.AddWithValue("@DateExpected", DateExpected);
cmd.Parameters.AddWithValue("@NumberOfLots", inventoryrecord.QtyToOrder);
cmd.Parameters.AddWithValue("@LocalSKU", inventoryrecord.SKU);
cmd.Parameters.AddWithValue("@SuppliersSKU", inventoryrecord.SupplierSKU);
cmd.ExecuteNonQuery();
}
public List <InventoryRecord> GetBySKU(string FillterType, string SKU)
{
string SelectCommand = "SELECT SupplierID,i.[LocalSKU] as SKU,[ItemName],isup.SupplierSKU as SupplierSKU ,ReorderPoint,ReorderQuantity,QOH,i.Price10 as Cost,i.Integer1,i.Integer2,i.Integer3,i.Backordered,i.Price10 as ExtendedCost,isnull((select sum(Quantity) from POHistory poh where i.LocalSKU=poh.LocalSKU and type='E'),0) as OnOrder,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(day,-14,getdate()) and DetailDate <= getdate()),0) as SoldLast14,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(day,-30,getdate()) and DetailDate <= getdate()),0) as SoldLast30,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND year(od.DetailDate) = year(getdate())) ,0)as SoldYTD,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND YEAR(od.DetailDate)=year(getdate())-1 ),0) as SoldLY,isnull((SELECT SUM(QuantityOrdered) FROM [dbo].[Order Details] od WHERE od.SKU = i.LocalSKU AND od.DetailDate >= DATEADD(MONTH,-24,getdate()) and DetailDate <= getdate()),0) as SoldL24M,isnull((SELECT MAX(Orders.OrderNumber) FROM [Order Details] INNER JOIN Orders ON ([Order Details].OrderNumber = Orders.OrderNumber) WHERE SKU =i.[LocalSKU] AND Approved = 'False' AND Cancelled = 'False'),0) as OrderNumber FROM [dbo].[Inventory] i join InventorySuppliers isup on i.LocalSKU=isup.LocalSKU where PrimarySupplier=1 and " + (FillterType.ToLower() == "s" ? "isup.SupplierSKU" : "i.LocalSKU") + "='" + SKU + "'";
SqlCommand cmd = new SqlCommand(SelectCommand, cn);
SqlDataReader dr = cmd.ExecuteReader();
List <InventoryRecord> inventoryrecords = new List <InventoryRecord>();
while (dr.Read())
{
InventoryRecord inventoryrecord = new InventoryRecord();
inventoryrecord.SKU = dr["SKU"] == DBNull.Value ? "" : dr["SKU"].ToString();
inventoryrecord.ItemName = dr["ItemName"] == DBNull.Value ? "" : dr["ItemName"].ToString();
inventoryrecord.SupplierSKU = dr["SupplierSKU"] == DBNull.Value ? "" : dr["SupplierSKU"].ToString();
inventoryrecord.SupplierID = dr["SupplierID"] == DBNull.Value ? "" : dr["SupplierID"].ToString();
inventoryrecord.QOH = dr["QOH"] == DBNull.Value ? 0 : int.Parse(dr["QOH"].ToString());
inventoryrecord.ReorderPoint = dr["ReorderPoint"] == DBNull.Value ? 0 : int.Parse(dr["ReorderPoint"].ToString());
inventoryrecord.ReorderQuantity = dr["ReorderQuantity"] == DBNull.Value ? 0 : int.Parse(dr["ReorderQuantity"].ToString());
inventoryrecord.QtyToOrder = 0;
inventoryrecord.ExtendedCost = dr["ExtendedCost"] == DBNull.Value ? 0 : decimal.Parse(dr["ExtendedCost"].ToString());
inventoryrecord.Backordered = dr["Backordered"] == DBNull.Value ? 0 : decimal.Parse(dr["Backordered"].ToString());
inventoryrecord.SoldL24M = dr["SoldL24M"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldL24M"].ToString());
inventoryrecord.SoldLY = dr["SoldLY"] == DBNull.Value ? 0 : int.Parse(dr["SoldLY"].ToString());
inventoryrecord.SoldYTD = dr["SoldYTD"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldYTD"].ToString());
inventoryrecord.SoldLast30 = dr["SoldLast30"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast30"].ToString());
inventoryrecord.SoldLast14 = dr["SoldLast14"] == DBNull.Value ? 0 : decimal.Parse(dr["SoldLast14"].ToString());
inventoryrecord.OnOrder = dr["OnOrder"] == DBNull.Value ? 0 : decimal.Parse(dr["OnOrder"].ToString());
inventoryrecord.Integer3 = dr["Integer3"] == DBNull.Value ? 0 : int.Parse(dr["Integer3"].ToString());
inventoryrecord.Integer2 = dr["Integer2"] == DBNull.Value ? 0 : int.Parse(dr["Integer2"].ToString());
inventoryrecord.Integer1 = dr["Integer1"] == DBNull.Value ? 0 : int.Parse(dr["Integer1"].ToString());
inventoryrecord.OrderNumber = dr["OrderNumber"] == DBNull.Value ? 0 : int.Parse(dr["OrderNumber"].ToString());
inventoryrecord.Cost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
inventoryrecord.HideCost = dr["Cost"] == DBNull.Value ? 0 : decimal.Parse(dr["Cost"].ToString());
inventoryrecords.Add(inventoryrecord);
}
dr.Close();
dr.Dispose();
dr = null;
return(inventoryrecords);
}
public void UpdateLog(InventoryRecord IR, string TrType)
{
OleDbCommand Cmd = new OleDbCommand("update [Log] set Quantity=" + IR.Quantity + " where OrderID='" + (TrType == "IN" ? LogKeyIN : LogKeyOUT) + "' And SKU ='" + IR.SKU + "' And LogType='" + TrType + "'", cn);
Cmd.ExecuteNonQuery();
}
public static int AddToLogAccDB(InventoryRecord ir)
{
int ID = 0;
try
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Connection.DBConn;
cmd.CommandText = "SELECT ID FROM POLog WHERE SKU = @SKU";
cmd.Parameters.AddWithValue("@SKU", ir.SKU);
ID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
ir.CretionDate = DateTime.Now;
ir.ISordered = ir.ISordered == null ? false : ir.ISordered;
if (ID > 0)
{
cmd.CommandText = "UPDATE POLog SET IsAuto = @IsAuto , ISordered = @ISordered, CretionDate=@CretionDate , ISDeleted=@ISDeleted, QtyToOrder = @QtyToOrder ,Cost=@Cost,Backordered=@Backordered,ExtendedCost=@ExtendedCost,OnOrder=@OnOrder,SoldLast14=@SoldLast14,SoldLast30=@SoldLast30,SoldYTD=@SoldYTD,SoldLY=@SoldLY,SoldL24M=@SoldL24M,OrderNumber=@OrderNumber,HideCost=@HideCost WHERE SKU = @SKU";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IsAuto", ir.IsAuto);
cmd.Parameters.AddWithValue("@ISordered", ir.ISordered);
cmd.Parameters.AddWithValue("@CretionDate", ir.CretionDate.ToString("dd/MM/yyyy h:mm:ss tt"));
cmd.Parameters.AddWithValue("@ISDeleted", ir.ISDeleted);
cmd.Parameters.AddWithValue("@QtyToOrder", ir.QtyToOrder);
cmd.Parameters.AddWithValue("@Cost", ir.Cost);
cmd.Parameters.AddWithValue("@Backordered", ir.Backordered);
cmd.Parameters.AddWithValue("@ExtendedCost", ir.ExtendedCost);
cmd.Parameters.AddWithValue("@OnOrder", ir.OnOrder);
cmd.Parameters.AddWithValue("@SoldLast14", ir.SoldLast14);
cmd.Parameters.AddWithValue("@SoldLast30", ir.SoldLast30);
cmd.Parameters.AddWithValue("@SoldYTD", ir.SoldYTD);
cmd.Parameters.AddWithValue("@SoldLY", ir.SoldLY);
cmd.Parameters.AddWithValue("@SoldL24M", ir.SoldL24M);
cmd.Parameters.AddWithValue("@OrderNumber", ir.OrderNumber);
cmd.Parameters.AddWithValue("@HideCost", ir.HideCost);
cmd.Parameters.AddWithValue("@SKU", ir.SKU);
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = "INSERT INTO POLog ([IsAuto],[ISDeleted],[ItemName],[SKU],[ReorderPoint],[ReorderQuantity],[QuantityNeeded],[QtyToOrder],[QOH],[CretionDate],[ISordered],[SupplierSKU],[SupplierID],[Cost],[Integer1],[Integer2],[Integer3],[Backordered],[ExtendedCost],[OnOrder],[SoldLast14],[SoldLast30],[SoldYTD],[SoldLY],[SoldL24M],[OrderNumber],[HideCost]) VALUES (@IsAuto,@ISDeleted,@ItemName,@SKU,@ReorderPoint,@ReorderQuantity,@QuantityNeeded,@QtyToOrder,@QOH,@CretionDate,@ISordered,@SupplierSKU,@SupplierID,@Cost,@Integer1,@Integer2,@Integer3,@Backordered,@ExtendedCost,@OnOrder,@SoldLast14,@SoldLast30,@SoldYTD,@SoldLY,@SoldL24M,@OrderNumber,@HideCost)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@IsAuto", ir.IsAuto);
cmd.Parameters.AddWithValue("@ISDeleted", ir.ISDeleted);
cmd.Parameters.AddWithValue("@ItemName", ir.ItemName);
cmd.Parameters.AddWithValue("@SKU", ir.SKU);
cmd.Parameters.AddWithValue("@ReorderPoint", ir.ReorderPoint);
cmd.Parameters.AddWithValue("@ReorderQuantity", ir.ReorderQuantity);
cmd.Parameters.AddWithValue("@QuantityNeeded", ir.QuantityNeeded);
cmd.Parameters.AddWithValue("@QtyToOrder", ir.QtyToOrder);
cmd.Parameters.AddWithValue("@QOH", ir.QOH);
cmd.Parameters.AddWithValue("@CretionDate", ir.CretionDate.ToString("dd/MM/yyyy h:mm:ss tt"));
cmd.Parameters.AddWithValue("@ISordered", ir.ISordered);
cmd.Parameters.AddWithValue("@SupplierSKU", ir.SupplierSKU);
cmd.Parameters.AddWithValue("@SupplierID", ir.SupplierID);
cmd.Parameters.AddWithValue("@Cost", ir.Cost);
cmd.Parameters.AddWithValue("@Integer1", ir.Integer1);
cmd.Parameters.AddWithValue("@Integer2", ir.Integer2);
cmd.Parameters.AddWithValue("@Integer3", ir.Integer3);
cmd.Parameters.AddWithValue("@Backordered", ir.Backordered);
cmd.Parameters.AddWithValue("@ExtendedCost", ir.ExtendedCost);
cmd.Parameters.AddWithValue("@OnOrder", ir.OnOrder);
cmd.Parameters.AddWithValue("@SoldLast14", ir.SoldLast14);
cmd.Parameters.AddWithValue("@SoldLast30", ir.SoldLast30);
cmd.Parameters.AddWithValue("@SoldYTD", ir.SoldYTD);
cmd.Parameters.AddWithValue("@SoldLY", ir.SoldLY);
cmd.Parameters.AddWithValue("@SoldL24M", ir.SoldL24M);
cmd.Parameters.AddWithValue("@OrderNumber", ir.OrderNumber);
cmd.Parameters.AddWithValue("@HideCost", ir.HideCost);
cmd.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
return(ID);
}
public InventoryRecord GetData(string SKU, string Barcode, ref string result)
{
InventoryRecord IR = new InventoryRecord();
try
{
if (string.IsNullOrWhiteSpace(SKU) && string.IsNullOrWhiteSpace(Barcode))
{
result = "1";
return(IR);
}
SqlCommand cmd = new SqlCommand();
if (string.IsNullOrWhiteSpace(Barcode))
{
Barcode = SKU;
}
string SelectStatement = "SELECT i.ItemName,i.Barcode,i.LocalSKU,i.Price,i.Price2,i.Price3,i.Price4,i.Price5,i.Price6,i.Price7,i.Price8,i.Price9,i.Price10,i.Text1,i.Text2,i.Text3,i.Text4,i.Text5,i.Integer1,i.Integer2,i.Integer3,i.Integer4,i.Integer5,i.QOH,i.Location,i.weight,i.Actualweight,i.Reorderpoint,i.Backordered,s.SupplierSKU FROM Inventory i left join (SELECT * FROM InventorySuppliers WHERE PrimarySupplier='true') s on i.LocalSKU=S.LocalSKU WHERE i.Barcode = '" + Barcode + "' OR i.LocalSKU = '" + Barcode + "' ";
//if (string.IsNullOrWhiteSpace(SKU))
//{
// SelectStatement = "SELECT * FROM Inventory WHERE Barcode = '" + Barcode + "'";
//}
//else
//{
// if (string.IsNullOrWhiteSpace(Barcode))
// {
// SelectStatement = "SELECT * FROM Inventory WHERE LocalSKU = '" + SKU + "'";
// }
// else
// {
// SelectStatement = "SELECT * FROM Inventory WHERE Barcode = '" + Barcode + "' AND LocalSKU = '" + SKU + "'";
// }
//}
cmd.CommandText = SelectStatement;
cmd.Connection = cn;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
IR.ProductName = dr["ItemName"].ToString();
IR.Barcode = dr["Barcode"].ToString();
IR.SKU = dr["LocalSKU"].ToString();
IR.Price = dr["Price"] == DBNull.Value ? 0 : (decimal)dr["Price"];
IR.QOH = (int)dr["QOH"];
IR.Price4 = dr["Price4"] == DBNull.Value ? 0 : (decimal)dr["Price4"];
IR.Price3 = dr["Price3"] == DBNull.Value ? 0 : (decimal)dr["Price3"];
IR.Price2 = dr["Price2"] == DBNull.Value ? 0 : (decimal)dr["Price2"];
IR.Price = dr["Price"] == DBNull.Value ? 0 : (decimal)dr["Price"];
IR.Location = dr["Location"] == DBNull.Value ? "" : dr["Location"].ToString();
IR.SupplierSKU = dr["SupplierSKU"] == DBNull.Value ? "" : dr["SupplierSKU"].ToString();
IR.Price5 = dr["Price5"] == DBNull.Value ? 0 : (decimal)dr["Price5"];
IR.Price6 = dr["Price6"] == DBNull.Value ? 0 : (decimal)dr["Price6"];
IR.Price7 = dr["Price7"] == DBNull.Value ? 0 : (decimal)dr["Price7"];
IR.Price8 = dr["Price8"] == DBNull.Value ? 0 : (decimal)dr["Price8"];
IR.Price9 = dr["Price9"] == DBNull.Value ? 0 : (decimal)dr["Price9"];
IR.Price10 = dr["Price10"] == DBNull.Value ? 0 : (decimal)dr["Price10"];
IR.Text1 = dr["Text1"] == DBNull.Value ? "" : dr["Text1"].ToString();
IR.Text2 = dr["Text2"] == DBNull.Value ? "" : dr["Text2"].ToString();
IR.Text3 = dr["Text3"] == DBNull.Value ? "" : dr["Text3"].ToString();
IR.Text4 = dr["Text4"] == DBNull.Value ? "" : dr["Text4"].ToString();
IR.Text5 = dr["Text5"] == DBNull.Value ? "" : dr["Text5"].ToString();
IR.Integer1 = dr["Integer1"] == DBNull.Value ? 0 : (int)dr["Integer1"];
IR.Integer2 = dr["Integer2"] == DBNull.Value ? 0 : (int)dr["Integer2"];
IR.Integer3 = dr["Integer3"] == DBNull.Value ? 0 : (int)dr["Integer3"];
IR.Integer4 = dr["Integer4"] == DBNull.Value ? 0 : (int)dr["Integer4"];
IR.Integer5 = dr["Integer5"] == DBNull.Value ? 0 : (int)dr["Integer5"];
IR.Weight = dr["Weight"] == DBNull.Value ? 0 : decimal.Parse(dr["Weight"].ToString());
IR.Actualweight = dr["Actualweight"] == DBNull.Value ? 0 : decimal.Parse(dr["Actualweight"].ToString());
IR.Backordered = dr["Backordered"] == DBNull.Value ? 0 : (int)dr["Backordered"];
IR.ReorderPoint = dr["ReorderPoint"] == DBNull.Value ? 0 : (int)dr["ReorderPoint"];
}
dr.Close();
SqlCommand cmdOD = new SqlCommand("SELECT MAX(Orders.OrderNumber) FROM [Order Details] INNER JOIN Orders ON ([Order Details].OrderNumber = Orders.OrderNumber) WHERE SKU = '" + IR.SKU + "' AND Approved = 'False' AND Cancelled = 'False'", cn);
try
{
IR.OrderNumber = (int)cmdOD.ExecuteScalar();
}
catch (Exception ex)
{
IR.OrderNumber = 0;
}
}
else
{
dr.Close();
if (!string.IsNullOrWhiteSpace(Barcode))
{
SqlCommand cmdList = new SqlCommand("SELECT Text2 fROM Lists WHERE Text1 = '" + Barcode + "'", cn);
string SKU2 = cmdList.ExecuteScalar() == null ? "" : cmdList.ExecuteScalar().ToString();
if (!string.IsNullOrWhiteSpace(SKU2))
{
cmd.CommandText = "SELECT i.ItemName,i.Barcode,i.LocalSKU,i.Price,i.Price2,i.Price3,i.Price4,i.Price5,i.Price6,i.Price7,i.Price8,i.Price9,i.Price10,i.Text1,i.Text2,i.Text3,i.Text4,i.Text5,i.Integer1,i.Integer2,i.Integer3,i.Integer4,i.Integer5,i.QOH,i.Location,i.weight,i.Actualweight,i.Reorderpoint,i.Backordered,s.SupplierSKU FROM Inventory i left join (SELECT * FROM InventorySuppliers WHERE PrimarySupplier='true') s on i.LocalSKU=S.LocalSKU WHERE i.LocalSKU = '" + SKU2 + "'";
SqlDataReader dr2 = cmd.ExecuteReader();
if (dr2.HasRows)
{
while (dr2.Read())
{
IR.ProductName = dr2["ItemName"].ToString();
IR.Barcode = dr2["Barcode"].ToString();
IR.SKU = dr2["LocalSKU"].ToString();
IR.Price = dr2["Price"] == DBNull.Value ? 0 : (decimal)dr2["Price"];
IR.QOH = (int)dr2["QOH"];
IR.SupplierSKU = dr2["SupplierSKU"] == DBNull.Value ? "" : dr2["SupplierSKU"].ToString();
IR.Price3 = dr2["Price3"] == DBNull.Value ? 0 : (decimal)dr2["Price3"];
IR.Price4 = dr2["Price4"] == DBNull.Value ? 0 : (decimal)dr2["Price4"];
IR.Price2 = dr2["Price2"] == DBNull.Value ? 0 : (decimal)dr2["Price2"];
IR.Price = dr2["Price"] == DBNull.Value ? 0 : (decimal)dr2["Price"];
IR.Location = dr2["Location"] == DBNull.Value ? "" : dr2["Location"].ToString();
IR.Price5 = dr2["Price5"] == DBNull.Value ? 0 : (decimal)dr2["Price5"];
IR.Price6 = dr2["Price6"] == DBNull.Value ? 0 : (decimal)dr2["Price6"];
IR.Price7 = dr2["Price7"] == DBNull.Value ? 0 : (decimal)dr2["Price7"];
IR.Price8 = dr2["Price8"] == DBNull.Value ? 0 : (decimal)dr2["Price8"];
IR.Price9 = dr2["Price9"] == DBNull.Value ? 0 : (decimal)dr2["Price9"];
IR.Price10 = dr2["Price10"] == DBNull.Value ? 0 : (decimal)dr2["Price10"];
IR.Text1 = dr2["Text1"] == DBNull.Value ? "" : dr2["Text1"].ToString();
IR.Text2 = dr2["Text2"] == DBNull.Value ? "" : dr2["Text2"].ToString();
IR.Text3 = dr2["Text3"] == DBNull.Value ? "" : dr2["Text3"].ToString();
IR.Text4 = dr2["Text4"] == DBNull.Value ? "" : dr2["Text4"].ToString();
IR.Text5 = dr2["Text5"] == DBNull.Value ? "" : dr2["Text5"].ToString();
IR.Integer1 = dr2["Integer1"] == DBNull.Value ? 0 : (int)dr2["Integer1"];
IR.Integer2 = dr2["Integer2"] == DBNull.Value ? 0 : (int)dr2["Integer2"];
IR.Integer3 = dr2["Integer3"] == DBNull.Value ? 0 : (int)dr2["Integer3"];
IR.Integer4 = dr2["Integer4"] == DBNull.Value ? 0 : (int)dr2["Integer4"];
IR.Integer5 = dr2["Integer5"] == DBNull.Value ? 0 : (int)dr2["Integer5"];
IR.Weight = dr2["Weight"] == DBNull.Value ? 0 : decimal.Parse(dr2["Weight"].ToString());
IR.Actualweight = dr2["Actualweight"] == DBNull.Value ? 0 : decimal.Parse(dr2["Actualweight"].ToString());
IR.Backordered = dr2["Backordered"] == DBNull.Value ? 0 : (int)dr2["Backordered"];
IR.ReorderPoint = dr2["ReorderPoint"] == DBNull.Value ? 0 : (int)dr2["ReorderPoint"];
}
dr2.Close();
SqlCommand cmdOD = new SqlCommand("SELECT MAX(OrderNumber) FROM [Order Details] WHERE SKU = '" + IR.SKU + "'", cn);
try
{
IR.OrderNumber = (int)cmdOD.ExecuteScalar();
}
catch (Exception ex)
{
IR.OrderNumber = 0;
}
}
else
{
dr2.Close();
result = "2";
return(IR);
}
}
else
{
dr.Close();
result = "2";
return(IR);
}
}
else
{
dr.Close();
result = "2";
return(IR);
}
}
}
catch (Exception)
{
throw;
}
result = "0";
return(IR);
}
