public string assignPallet(mdlPallet palletinfo)
{
string result = "";
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"UPDATE PALLETS SET ASSIGNEDTO=@ASSIGNEDTO WHERE ID=@ID";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("ASSIGNEDTO", SqlDbType.VarChar).Value = palletinfo.EMPLOYEE;
cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = palletinfo.ID;
cmd.ExecuteNonQuery();
result = "Pallet successfull assigned";
cmd.Dispose();
dbconn.closeConnection();
return(result);
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getPalletLocation(string id)
{
List <mdlPallet> model = new List <mdlPallet>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT DATEDIFF(day, dbo.vrfid_logs_max.READTIME, GETDATE()) AS DAYS, dbo.vpallets.BARCODE, dbo.vpallets.RFID, dbo.vpallets.TYPE, dbo.vpallets.STYLE, dbo.vrfid_logs_max.READTIME,
dbo.vrfid_logs_max.AREA, dbo.vpallets.ACTIVE, dbo.vpallets.LOCATION
FROM dbo.vpallets LEFT OUTER JOIN
dbo.vrfid_logs_max ON dbo.vpallets.RFID = dbo.vrfid_logs_max.RFID
WHERE (dbo.vpallets.ACTIVE = 'Active') AND (dbo.vrfid_logs_max.READTIME IS NOT NULL) AND (dbo.vrfid_logs_max.AREAID=@AREA)";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = id;
SqlDataReader dr = cmd.ExecuteReader();
mdlPallet list = new mdlPallet();
while (dr.Read())
{
list = new mdlPallet();
list.BARCODE = dr["BARCODE"].ToString();
list.RFID = dr["RFID"].ToString();
list.LOCATION = dr["LOCATION"].ToString();
if (!string.IsNullOrEmpty(dr["DAYS"].ToString()))
{
list.DAYS = " - Last " + dr["DAYS"].ToString() + " day(s) ";
}
if (!string.IsNullOrEmpty(dr["READTIME"].ToString()))
{
list.READTIME = Convert.ToDateTime(dr["READTIME"].ToString()).ToString("dd-MMM-yyyy H:mm:ss tt");
}
list.AREA = dr["AREA"].ToString();
list.TYPE = dr["TYPE"].ToString();
list.STYLE = dr["STYLE"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getPalletInfo(string id, string gid)
{
mdlPallet model = new mdlPallet();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT * FROM PALLETS WHERE ID=@ID AND GID=@GID";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = id;
cmd.Parameters.Add("GID", SqlDbType.VarChar).Value = gid;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
model.ID = dr["ID"].ToString();
model.GID = dr["GID"].ToString();
model.BARCODE = dr["BARCODE"].ToString();
model.RFID = dr["RFID"].ToString();
model.TYPE = dr["TYPE"].ToString();
model.STYLE = dr["STYLE"].ToString();
model.SIZE = dr["SIZE"].ToString();
model.LOCATION = dr["LOCATION"].ToString();
model.SUPPLIER = dr["SUPPLIER"].ToString();
model.NAME = dr["NAME"].ToString();
model.ACTIVE = dr["ACTIVE"].ToString();
model.AREA = dr["AREA"].ToString();
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getPallets()
{
List <mdlPallet> model = new List <mdlPallet>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID,GID,BARCODE,RFID,TYPE,LOCATION,NAME,EMPLOYEE,ACTIVE,AREA FROM VPALLETS ";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
mdlPallet list = new mdlPallet();
while (dr.Read())
{
list = new mdlPallet();
list.ID = dr["ID"].ToString();
list.GID = dr["GID"].ToString();
list.BARCODE = dr["BARCODE"].ToString();
list.RFID = dr["RFID"].ToString();
list.TYPE = dr["TYPE"].ToString();
list.EMPLOYEE = dr["EMPLOYEE"].ToString();
list.LOCATION = dr["LOCATION"].ToString();
list.NAME = dr["NAME"].ToString();
list.ACTIVE = dr["ACTIVE"].ToString();
list.AREA = dr["AREA"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult searchPalletLocation(string id)
{
List <mdlPallet> model = new List <mdlPallet>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID,RFID,READTIME,LOCATION,READERNAME,TYPE,STYLE FROM VRFID_LOGS WHERE LOCATIONID=@LOCATIONID ";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("LOCATIONID", SqlDbType.VarChar).Value = id;
SqlDataReader dr = cmd.ExecuteReader();
mdlPallet list = new mdlPallet();
while (dr.Read())
{
list = new mdlPallet();
list.ID = dr["ID"].ToString();
list.RFID = dr["RFID"].ToString();
list.READTIME = Convert.ToDateTime(dr["READTIME"].ToString()).ToString("dd-MMM-yyyy H:mm:ss tt");
list.LOCATION = dr["LOCATION"].ToString();
list.READERNAME = dr["READERNAME"].ToString();
list.TYPE = dr["TYPE"].ToString();
list.STYLE = dr["STYLE"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getAreaTypeInventory()
{
List <mdlPallet> model = new List <mdlPallet>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT COUNT(dbo.vpallets.TYPE) AS CNT, dbo.vrfid_logs_max.AREA, dbo.vpallets.TYPE
FROM dbo.vpallets INNER JOIN
dbo.vrfid_logs_max ON dbo.vpallets.RFID = dbo.vrfid_logs_max.RFID
WHERE (dbo.vrfid_logs_max.READTIME IS NOT NULL) AND (dbo.vpallets.ACTIVE = 'Active')
GROUP BY dbo.vrfid_logs_max.AREA, dbo.vpallets.TYPE
ORDER BY dbo.vrfid_logs_max.AREA";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
mdlPallet list = new mdlPallet();
while (dr.Read())
{
list = new mdlPallet();
list.AREA = dr["AREA"].ToString();
list.TYPE = dr["TYPE"].ToString();
list.CNT = dr["CNT"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getActivePallets()
{
List <mdlPallet> model = new List <mdlPallet>();
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID,NAME FROM PALLETS WHERE ACTIVE='A'";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
mdlPallet list = new mdlPallet();
while (dr.Read())
{
list = new mdlPallet();
list.ID = dr["ID"].ToString();
list.NAME = dr["NAME"].ToString();
model.Add(list);
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
public string savePallet(mdlPallet palletinfo)
{
string result = "";
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID FROM PALLETS WHERE RFID=@RFID";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("RFID", SqlDbType.VarChar).Value = palletinfo.RFID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
result = "RFID already exist";
}
dr.Dispose();
cmd.Dispose();
strSQL = @"SELECT ID FROM PALLETS WHERE NAME=@NAME";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("NAME", SqlDbType.VarChar).Value = palletinfo.NAME;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
result = "Pallet name already exist";
}
dr.Dispose();
cmd.Dispose();
if (result == "")
{
strSQL = @"INSERT INTO PALLETS(GID,BARCODE,RFID,TYPE,STYLE,SIZE,LOCATION,SUPPLIER,ENCODEDBY,MODIFIEDBY,DATEMODIFIED,DATEENCODED,ACTIVE,AREA,NAME)
VALUES(CONVERT(varchar(10), right(newid(),10)),@BARCODE,@RFID,@TYPE,@STYLE,@SIZE,@LOCATION,@SUPPLIER,@ENCODEDBY,@MODIFIEDBY,GETDATE(),GETDATE(),@ACTIVE,@AREA,@NAME)";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("BARCODE", SqlDbType.VarChar).Value = palletinfo.BARCODE;
cmd.Parameters.Add("RFID", SqlDbType.VarChar).Value = palletinfo.RFID;
cmd.Parameters.Add("TYPE", SqlDbType.VarChar).Value = palletinfo.TYPE;
cmd.Parameters.Add("STYLE", SqlDbType.VarChar).Value = palletinfo.STYLE;
cmd.Parameters.Add("SIZE", SqlDbType.VarChar).Value = palletinfo.SIZE;
cmd.Parameters.Add("LOCATION", SqlDbType.VarChar).Value = palletinfo.LOCATION;
cmd.Parameters.Add("SUPPLIER", SqlDbType.VarChar).Value = palletinfo.SUPPLIER;
cmd.Parameters.Add("ENCODEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = palletinfo.ACTIVE;
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = palletinfo.AREA;
cmd.Parameters.Add("NAME", SqlDbType.VarChar).Value = palletinfo.NAME;
cmd.ExecuteNonQuery();
result = "Pallet successfully saved.";
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(result);
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
dbconn.closeConnection();
}
}
public string updatePallet(mdlPallet palletinfo)
{
string result = "";
Db_Connection dbconn = new Db_Connection();
try
{
dbconn.openConnection();
string strSQL = @"SELECT ID FROM PALLETS WHERE RFID=@RFID";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("RFID", SqlDbType.VarChar).Value = palletinfo.RFID;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["ID"].ToString() != palletinfo.ID)
{
result = "Pallet RFID already exist";
}
}
dr.Dispose();
cmd.Dispose();
strSQL = @"SELECT ID FROM PALLETS WHERE NAME=@NAME";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("NAME", SqlDbType.VarChar).Value = palletinfo.NAME;
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
if (dr["ID"].ToString() != palletinfo.ID)
{
result = "Pallet name already exist";
}
}
dr.Dispose();
cmd.Dispose();
if (result == "")
{
strSQL = @"UPDATE PALLETS SET BARCODE=@BARCODE,RFID=@RFID,TYPE=@TYPE,STYLE=@STYLE,SIZE=@SIZE,LOCATION=@LOCATION,SUPPLIER=@SUPPLIER,ACTIVE=@ACTIVE,
MODIFIEDBY=@MODIFIEDBY,DATEMODIFIED=GETDATE(),AREA=@AREA,NAME=@NAME WHERE ID=@ID AND GID=@GID";
cmd = new SqlCommand(strSQL, dbconn.DbConn);
cmd.Parameters.Add("BARCODE", SqlDbType.VarChar).Value = palletinfo.BARCODE;
cmd.Parameters.Add("RFID", SqlDbType.VarChar).Value = palletinfo.RFID;
cmd.Parameters.Add("TYPE", SqlDbType.VarChar).Value = palletinfo.TYPE;
cmd.Parameters.Add("STYLE", SqlDbType.VarChar).Value = palletinfo.STYLE;
cmd.Parameters.Add("SIZE", SqlDbType.VarChar).Value = palletinfo.SIZE;
cmd.Parameters.Add("LOCATION", SqlDbType.VarChar).Value = palletinfo.LOCATION;
cmd.Parameters.Add("SUPPLIER", SqlDbType.VarChar).Value = palletinfo.SUPPLIER;
cmd.Parameters.Add("ACTIVE", SqlDbType.VarChar).Value = palletinfo.ACTIVE;
cmd.Parameters.Add("MODIFIEDBY", SqlDbType.VarChar).Value = "currentuser";
cmd.Parameters.Add("AREA", SqlDbType.VarChar).Value = palletinfo.AREA;
cmd.Parameters.Add("NAME", SqlDbType.VarChar).Value = palletinfo.NAME;
cmd.Parameters.Add("ID", SqlDbType.VarChar).Value = palletinfo.ID;
cmd.Parameters.Add("GID", SqlDbType.VarChar).Value = palletinfo.GID;
cmd.ExecuteNonQuery();
dr.Dispose();
cmd.Dispose();
result = "Pallet successfully updated";
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
return(result);
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
dbconn.closeConnection();
}
}
public ActionResult getPalletCount()
{
Db_Connection dbconn = new Db_Connection();
Db_Connection dbconn1 = new Db_Connection();
List <mdlPallet> model = new List <mdlPallet>();
try
{
dbconn.openConnection();
dbconn1.openConnection();
string strSQL = @"SELECT DISTINCT RFID,NAME,EMPLOYEE FROM VPALLET_COUNT WHERE EMPLOYEE IS NOT NULL ORDER BY EMPLOYEE ASC";
SqlCommand cmd = new SqlCommand(strSQL, dbconn.DbConn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DateTime rundate = new DateTime();
bool isStarted = false;
int cnt = 0;
strSQL = "SELECT READTIME FROM VPALLET_COUNT WHERE RFID=@RFID AND EMPLOYEE=@EMPLOYEE ORDER BY READTIME ASC";
SqlCommand cmd1 = new SqlCommand(strSQL, dbconn1.DbConn);
cmd1.Parameters.Add("RFID", SqlDbType.VarChar).Value = dr["RFID"].ToString();
cmd1.Parameters.Add("EMPLOYEE", SqlDbType.VarChar).Value = dr["EMPLOYEE"].ToString();
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
if (isStarted == false)
{
isStarted = true;
cnt++;
}
else
{
TimeSpan time = Convert.ToDateTime(dr1["READTIME"].ToString()) - rundate;
if (time.TotalMinutes >= 5)
{
cnt++;
}
}
rundate = Convert.ToDateTime(dr1["READTIME"].ToString());
}
if (cnt > 0)
{
mdlPallet list = new mdlPallet();
list.EMPLOYEE = dr["NAME"].ToString();
list.CNT = cnt.ToString();
list.RFID = dr["RFID"].ToString();
model.Add(list);
}
cmd1.Dispose();
dr1.Dispose();
}
dr.Dispose();
cmd.Dispose();
dbconn.closeConnection();
dbconn1.closeConnection();
return(Json(model, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(model, JsonRequestBehavior.AllowGet));
}
finally
{
dbconn.closeConnection();
}
}
