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