public int InsertPalletOnReset(WebRefFGSync.PalletEntity dr)
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
SqlCeCommand cmd = new SqlCeCommand();
string sqlString = "INSERT INTO Pallets (Pallet, SyncId, Location) " +
"VALUES (@p1, @p2, @p3);";
cmd.Parameters.Add("@p1", SqlDbType.NVarChar);
cmd.Parameters.Add("@p2", SqlDbType.BigInt);
cmd.Parameters.Add("@p3", SqlDbType.NVarChar);
cmd.Parameters["@p1"].Value = dr.Pallet;
cmd.Parameters["@p2"].Value = dr.SyncId;
cmd.Parameters["@p3"].Value = dr.Location;
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
cmd.Connection = this._connection;
adapter.SelectCommand = cmd;
if (this._connection.State != ConnectionState.Open)
{
this._connection.Open();
}
return(cmd.ExecuteNonQuery());
}
public int InsertDataAll(List <WebRefFGSync.InventAvailContract> data)//List<WebRefFGSync.PalletEntity> data)
{
int lastSyncId = 0;
try
{
//foreach (WebRefFGSync.PalletEntity row in data)
foreach (WebRefFGSync.InventAvailContract availCount in data)
{
WebRefFGSync.PalletEntity row = new WebRefFGSync.PalletEntity()
{
ItemNumber = availCount.itemIdField,
ProductName = availCount.productNameField,
Config = availCount.configurationIdField,
Size = availCount.sizeIdField,
Color = availCount.colorIdField,
Warehouse = availCount.locationIdField,
BatchNumber = availCount.batchIdField,
Location = availCount.wMSLocationIdField,
Pallet = availCount.serialIdField,
Available = availCount.availPhysicalField,
AvailableSpecified = true,
SyncId = availCount.syncIdField,
SyncIdSpecified = true
};
lastSyncId = availCount.syncIdField;
if (InsertPalletOnReset(row) > 0)
{
//iCount++;
InsertItemOnReset(row);
}
}
SaveOptions(Option.LastSyncId, lastSyncId.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
this._connection.Close();
}
return(lastSyncId);
}
public int InsertItemOnReset(WebRefFGSync.PalletEntity dr)
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter();
SqlCeCommand cmd = new SqlCeCommand();
string sqlString = "INSERT INTO Items ([Item number], [Product name], Configuration, Size, Color, Warehouse, [Batch number], Location, [Serial number], [Available Physical], SyncID) " +
"VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11);";
cmd.Parameters.Add("@p1", SqlDbType.NVarChar);
cmd.Parameters.Add("@p2", SqlDbType.NVarChar);
cmd.Parameters.Add("@p3", SqlDbType.NVarChar);
cmd.Parameters.Add("@p4", SqlDbType.NVarChar);
cmd.Parameters.Add("@p5", SqlDbType.NVarChar);
cmd.Parameters.Add("@p6", SqlDbType.NVarChar);
cmd.Parameters.Add("@p7", SqlDbType.NVarChar);
cmd.Parameters.Add("@p8", SqlDbType.NVarChar);
cmd.Parameters.Add("@p9", SqlDbType.NVarChar);
cmd.Parameters.Add("@p10", SqlDbType.Real);
cmd.Parameters.Add("@p11", SqlDbType.BigInt);
cmd.Parameters["@p1"].Value = dr.ItemNumber;
cmd.Parameters["@p2"].Value = dr.ProductName;
cmd.Parameters["@p3"].Value = dr.Config;
cmd.Parameters["@p4"].Value = dr.Size;
cmd.Parameters["@p5"].Value = dr.Color;
cmd.Parameters["@p6"].Value = dr.Warehouse;
cmd.Parameters["@p7"].Value = dr.BatchNumber;
cmd.Parameters["@p8"].Value = dr.Location;
cmd.Parameters["@p9"].Value = dr.Pallet;
cmd.Parameters["@p10"].Value = dr.Available;
cmd.Parameters["@p11"].Value = dr.SyncId;
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
cmd.Connection = this._connection;
adapter.SelectCommand = cmd;
if (this._connection.State != ConnectionState.Open)
{
this._connection.Open();
}
return(cmd.ExecuteNonQuery());
}
//use to submit the data to Desktop App
public List <WebRefFGSync.PalletEntity> GetFGData(long topSyncId)
{
List <WebRefFGSync.PalletEntity> rows = new List <WebRefFGSync.PalletEntity>();
DataTable dt = new DataTable("Pallets");
//long topSyncId = long.Parse("0"+GetOptions(Option.LastSyncId));
if (topSyncId.Equals(1))
{
topSyncId = 0;
}
string sqlString = "SELECT p.*,i.[Item number],i.[Product name],i.Configuration,i.Size,i.Color,i.Warehouse,i.[Batch number],i.Location,i.[Available physical] FROM Pallets p LEFT OUTER JOIN Items i ON p.Pallet=i.[Serial Number] WHERE p.SyncID >= @p1 AND p.UpdatedBy != ''";
SqlCeCommand cmd = new SqlCeCommand();
try
{
cmd.Parameters.Add("@p1", SqlDbType.BigInt);
cmd.Parameters[0].Value = topSyncId;
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
cmd.Connection = this._connection;
if (this._connection.State != ConnectionState.Open)
{
this._connection.Open();
}
SqlCeDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
WebRefFGSync.PalletEntity row = new WebRefFGSync.PalletEntity()
{
Pallet = dr["Pallet"].ToString(),
IsMatched = dr["IsMatched"] != DBNull.Value ? (bool)dr["IsMatched"] : false,
IsMatchedSpecified = true,
PresentInStock = dr["PresentInStock"] != DBNull.Value ? (bool)dr["PresentInStock"] : false,
PresentInStockSpecified = true,
ProcessByIT = true,
ProcessByITSpecified = true,
DateUpdated = (DateTime)dr["DateUpdated"],
DateUpdatedSpecified = true,
UpdatedBy = dr["UpdatedBy"].ToString(),
IsManual = dr["IsManual"] != DBNull.Value ? (bool)dr["IsManual"] : false,
IsManualSpecified = true,
DeviceName = dr["DeviceName"].ToString(),
SyncId = long.Parse(dr["SyncId"].ToString()),
SyncIdSpecified = true,
ItemNumber = dr["Item number"].ToString(),
ProductName = dr["Product name"].ToString(),
Config = dr["Configuration"].ToString(),
Size = dr["Size"].ToString(),
Color = dr["Color"].ToString(),
Location = dr["Location"].ToString(),
Warehouse = dr["Warehouse"].ToString(),
AvailableSpecified = true,
BatchNumber = dr["Batch number"].ToString()
};
if (dr["Available physical"] != DBNull.Value)
{
row.Available = decimal.Parse(dr["Available physical"].ToString());
}
if (dr["DateUpdated"] == DBNull.Value)
{
row.DateUpdated = new DateTime(1753, 1, 1);
}
else
{
row.DateUpdated = (DateTime)dr["DateUpdated"];
}
rows.Add(row);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
this._connection.Close();
}
return(rows);
}