public string UpdateUser(User user)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(7)
{
CommandText = StoreProcedure.Instance.GetScript("UserUpdate")
};
param.ParamStringFixedLength(0, "@EMPLOYEE_ID", user.EMPLOYEE_ID);
param.ParamStringFixedLength(1, "@EMPLOYEE_NAME", user.EMPLOYEE_NAME);
param.ParamStringFixedLength(2, "@USER_LOGIN", user.USER_LOGIN);
param.ParamStringFixedLength(3, "@USER_PASS", user.USER_PASS);
param.ParamStringFixedLength(4, "@USER_REMARK", user.USER_REMARK);
param.ParamStringFixedLength(5, "@IS_ACTIVE", (user.IS_ACTIVE ? "Y" : "N"));
param.ParamInt32(6, "@USER_ID", user.USER_ID);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string UpdateProgramGroup(ProgramGroup procGroup, int userid)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(7)
{
CommandText = StoreProcedure.Instance.GetScript("ProgramGroupUpdate")
};
param.ParamStringFixedLength(0, "@PROG_TYPE_NAME", procGroup.PROG_TYPE_NAME);
param.ParamInt32(1, "@ORDER_BY", procGroup.ORDER_BY);
param.ParamStringFixedLength(2, "@ICON", procGroup.ICON);
param.ParamStringFixedLength(3, "@DESCRIPTION", procGroup.DESCRIPTION);
param.ParamStringFixedLength(4, "@PROG_TYPE_RESOURCE", procGroup.PROG_TYPE_RESOURCE);
param.ParamStringFixedLength(5, "@IS_ACTIVE", (procGroup.IS_ACTIVE ? "Y" : "N"));
param.ParamInt32(6, "@PROG_TYPE_ID", procGroup.PROG_TYPE_ID);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public void InsertStockByMinMax(List <StockByMinMax> lstStockByMinMax, DateTime lastupdate)
{
int flag = 0;
try
{
object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM STOCK_BY_MINMAX");
flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo);
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (StockByMinMax SQLiteInsert in lstStockByMinMax)
{
param = new SQLiteParam(20)
{
CommandText = StoreProcedure.Instance.GetScript("InsertStockByMinMax")
};
param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID);
param.ParamStringFixedLength(1, "@PARTY_NAME", SQLiteInsert.PARTY_NAME);
param.ParamStringFixedLength(2, "@WH_ID", SQLiteInsert.WH_ID);
param.ParamStringFixedLength(3, "@PRODUCT_NO", SQLiteInsert.PRODUCT_NO);
param.ParamStringFixedLength(4, "@PRODUCT_SEQ_NO", SQLiteInsert.PRODUCT_SEQ_NO);
param.ParamStringFixedLength(5, "@PRODUCT_NAME", SQLiteInsert.PRODUCT_NAME);
param.ParamStringFixedLength(6, "@PRODUCT_TYPE_ID", SQLiteInsert.PRODUCT_TYPE_ID);
param.ParamStringFixedLength(7, "@PRODUCT_TYPE_NAME", SQLiteInsert.PRODUCT_TYPE_NAME);
param.ParamInt32(8, "@BOX_QTY", SQLiteInsert.BOX_QTY);
param.ParamInt32(9, "@STOCK_PCS", SQLiteInsert.STOCK_PCS);
param.ParamInt32(10, "@STOCK_BOX", SQLiteInsert.STOCK_BOX);
param.ParamStringFixedLength(11, "@STATUS_RUNNING_MC", SQLiteInsert.STATUS_RUNNING_MC);
param.ParamInt32(12, "@PICK_PENDING", SQLiteInsert.PICK_PENDING);
param.ParamInt32(13, "@EXPECTED_DELAY", SQLiteInsert.EXPECTED_DELAY);
param.ParamInt32(14, "@FORECAST", SQLiteInsert.FORECAST);
param.ParamInt32(15, "@MIN_BOX", SQLiteInsert.MIN_BOX);
param.ParamInt32(16, "@MAX_BOX", SQLiteInsert.MAX_BOX);
param.ParamStringFixedLength(17, "@STATUS", SQLiteInsert.STATUS);
param.ParamInt32(18, "@FLAG", flag);
param.ParamDateTime(19, "@lastupdate", lastupdate);
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetStockbyMinmax(string s1, string s2, string s3)
{
try
{
DataTable dtResult;
string sql = string.Empty;
SQLiteParam param = new SQLiteParam(3)
{
CommandText = StoreProcedure.Instance.GetScript("GetStockByMinMax_Status")
};
param.ParamStringFixedLength(0, "@strS1", s1);
param.ParamStringFixedLength(1, "@strS2", s2);
param.ParamStringFixedLength(2, "@strS3", s3);
dtResult = GlobalSqliteDB.Instance.DataAc.GetDataTable(param);
return(dtResult);
}
catch (Exception ex)
{
throw ex;
}
}
public string UserChangePassword(string newPass, int userid)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(2)
{
CommandText = StoreProcedure.Instance.GetScript("UserChangePassword")
};
param.ParamStringFixedLength(0, "@USER_PASS", newPass);
param.ParamInt32(1, "@USER_ID", userid);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public int UserInsert(string empid, string empname, string userlogin, string password, string remark)
{
try
{
string sql = "INSERT INTO M_USER (EMPLOYEE_ID, EMPLOYEE_NAME, USER_LOGIN, USER_PASS, USER_REMARK) " +
"VALUES (@EMPLOYEE_ID, @EMPLOYEE_NAME, @USER_LOGIN, @USER_PASS, @USER_REMARK);";
SQLiteParam param = new SQLiteParam(5)
{
CommandText = sql
};
param.ParamStringFixedLength(0, "@EMPLOYEE_ID", empid);
param.ParamStringFixedLength(1, "@EMPLOYEE_NAME", empname);
param.ParamStringFixedLength(2, "@USER_LOGIN", userlogin);
param.ParamStringFixedLength(3, "@USER_PASS", password);
param.ParamStringFixedLength(4, "@USER_REMARK", remark);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
return(rowAfect);
}
catch (Exception ex)
{
throw ex;
}
}
public List <UserProgram> GetUserProgramList(int userId)
{
List <UserProgram> lstUserProg = null;
UserProgram userProg;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetUserProgramList")
};
param.ParamInt32(0, "@USER_ID", userId);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
lstUserProg = new List <UserProgram>();
while (SQLiteReader.Instance.SLReader.Read())
{
userProg = new UserProgram();
userProg.USER_PROG_ID = SQLiteReader.Instance.GetInteger("USER_PROG_ID");
userProg.PG_ICON = SQLiteReader.Instance.GetString("PG_ICON");
userProg.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME");
userProg.ICON = SQLiteReader.Instance.GetString("ICON");
userProg.PROG_ID = SQLiteReader.Instance.GetInteger("PROG_ID");
userProg.PROG_NAME = SQLiteReader.Instance.GetString("PROG_NAME");
userProg.IS_ACCESS = SQLiteReader.Instance.GetString("IS_ACCESS").ToUpper().Equals("Y");
userProg.IS_INSERT = SQLiteReader.Instance.GetString("IS_INSERT").ToUpper().Equals("Y");
userProg.IS_UPDATE = SQLiteReader.Instance.GetString("IS_UPDATE").ToUpper().Equals("Y");
userProg.IS_DELETE = SQLiteReader.Instance.GetString("IS_DELETE").ToUpper().Equals("Y");
userProg.IS_PRINT = SQLiteReader.Instance.GetString("IS_PRINT").ToUpper().Equals("Y");
userProg.CUSTOM1 = SQLiteReader.Instance.GetString("CUSTOM1");
userProg.IS_CUSTOM1 = SQLiteReader.Instance.GetString("IS_CUSTOM1").ToUpper().Equals("Y");
userProg.CUSTOM2 = SQLiteReader.Instance.GetString("CUSTOM2");
userProg.IS_CUSTOM2 = SQLiteReader.Instance.GetString("IS_CUSTOM2").ToUpper().Equals("Y");
userProg.CUSTOM3 = SQLiteReader.Instance.GetString("CUSTOM3");
userProg.IS_CUSTOM3 = SQLiteReader.Instance.GetString("IS_CUSTOM3").ToUpper().Equals("Y");
userProg.USER_ID = SQLiteReader.Instance.GetString("USER_ID");
userProg.FLAG = SQLiteReader.Instance.GetInteger("FLAG");
lstUserProg.Add(userProg);
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
lstUserProg = null;
}
return(lstUserProg);
//return UtilityBLL.ListToDataTable(lstUserProg, "M_USER_PROGRAM");
}
public void InsertStockByCustomer(List <StockByCustomer> lstStockByCustomer, DateTime lastupdate)
{
int flag = 0;
try
{
object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM STOCK_BY_CUSTOMER");
flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo);
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (StockByCustomer SQLiteInsert in lstStockByCustomer)
{
param = new SQLiteParam(12)
{
CommandText = StoreProcedure.Instance.GetScript("InsertStockByCustomer")
};
param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID);
param.ParamStringFixedLength(1, "@PARTY_NAME", SQLiteInsert.PARTY_NAME);
param.ParamStringFixedLength(2, "@WH_ID", SQLiteInsert.WH_ID);
param.ParamStringFixedLength(3, "@PRODUCT_NO", SQLiteInsert.PRODUCT_NO);
param.ParamStringFixedLength(4, "@PRODUCT_NAME", SQLiteInsert.PRODUCT_NAME);
param.ParamStringFixedLength(5, "@PRODUCT_TYPE_ID", SQLiteInsert.PRODUCT_TYPE_ID);
param.ParamStringFixedLength(6, "@PRODUCT_TYPE_NAME", SQLiteInsert.PRODUCT_TYPE_NAME);
param.ParamInt32(7, "@BOX_QTY", SQLiteInsert.BOX_QTY);
param.ParamInt32(8, "@QTY", SQLiteInsert.QTY);
param.ParamInt32(9, "@NO_OF_BOX", SQLiteInsert.NO_OF_BOX);
param.ParamInt32(10, "@FLAG", flag);
param.ParamDateTime(11, "@lastupdate", lastupdate);
//if (stkUpload.N_USER_DATE.HasValue)
// param.ParamDate(9, "@N_USER_DATE", stkUpload.N_USER_DATE.Value);
//else
// param.ParamNull(9, "@N_USER_DATE");
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertDeliveryBoardDetail(List <DeliveryBoardDetail> lstDeliveryBoardDetail, DateTime lastupdate)
{
int flag = 0;
try
{
object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM DELIVERY_DETAIL");
flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo);
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (DeliveryBoardDetail SQLiteInsert in lstDeliveryBoardDetail)
{
param = new SQLiteParam(16)
{
CommandText = StoreProcedure.Instance.GetScript("InsertDeliveryBoardDetail")
};
param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID);
param.ParamStringFixedLength(1, "@WH_ID", SQLiteInsert.WH_ID);
param.ParamDateTime(2, "@ETD_DATE", SQLiteInsert.ETD_DATE);
param.ParamStringFixedLength(3, "@PRODUCT_NO", SQLiteInsert.PRODUCT_NO);
param.ParamStringFixedLength(4, "@PRODUCT_NAME", SQLiteInsert.PRODUCT_NAME);
param.ParamInt32(5, "@QTY", SQLiteInsert.QTY);
param.ParamStringFixedLength(6, "@UNIT", SQLiteInsert.UNIT);
param.ParamInt32(7, "@NO_OF_BOX", SQLiteInsert.NO_OF_BOX);
param.ParamInt32(8, "@FREE_STOCK", SQLiteInsert.FREE_STOCK);
param.ParamInt32(9, "@ASSIGN_QTY", SQLiteInsert.ASSIGN_QTY);
param.ParamInt32(10, "@PICKED_QTY", SQLiteInsert.PICKED_QTY);
param.ParamInt32(11, "@LOADED_QTY", SQLiteInsert.LOADED_QTY);
param.ParamStringFixedLength(12, "@STATUS", SQLiteInsert.STATUS);
param.ParamStringFixedLength(13, "@REMARK", SQLiteInsert.REMARK);
param.ParamInt32(14, "@FLAG", flag);
param.ParamDateTime(15, "@lastupdate", lastupdate);
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public M_Program GetProgramByID(int progId)
{
M_Program mProgram = null;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetProgramById")
};
param.ParamInt32(0, "@PROG_ID", progId);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
while (SQLiteReader.Instance.SLReader.Read())
{
mProgram = new M_Program();
mProgram.PROG_ID = SQLiteReader.Instance.GetInteger("PROG_ID");
mProgram.PROG_NAME = SQLiteReader.Instance.GetString("PROG_NAME");
mProgram.PROG_KEY = SQLiteReader.Instance.GetString("PROG_KEY");
mProgram.PROG_GROUP.PROG_TYPE_ID = SQLiteReader.Instance.GetInteger("PROG_TYPE_ID");
mProgram.PROG_GROUP.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME");
mProgram.PROG_TYPE_ICON = SQLiteReader.Instance.GetString("PROG_TYPE_ICON");
if (!SQLiteReader.Instance.IsDBNull("ORDER_BY"))
{
mProgram.ORDER_BY = SQLiteReader.Instance.GetInteger("ORDER_BY");
}
mProgram.ICON = SQLiteReader.Instance.GetString("ICON");
mProgram.DESCRIPTION = SQLiteReader.Instance.GetString("DESCRIPTION");
mProgram.PROG_RESOURCE = SQLiteReader.Instance.GetString("PROG_RESOURCE");
mProgram.IS_ACTIVE = (SQLiteReader.Instance.GetString("IS_ACTIVE").ToUpper() == "Y");
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
mProgram = null;
}
return(mProgram);
}
public void InsertDeliveryBoard(List <DeliveryBoard> lstDeliveryBoard, DateTime lastupdate)
{
int flag = 0;
try
{
object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM DELIVERY_BOARD");
flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo);
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (DeliveryBoard SQLiteInsert in lstDeliveryBoard)
{
param = new SQLiteParam(9)
{
CommandText = StoreProcedure.Instance.GetScript("InsertDeliveryBoard")
};
param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID);
param.ParamStringFixedLength(1, "@PARTY_NAME", SQLiteInsert.PARTY_NAME);
param.ParamStringFixedLength(2, "@WH_ID", SQLiteInsert.WH_ID);
param.ParamDateTime(3, "@ETD_DATE", SQLiteInsert.ETD_DATE);
param.ParamStringFixedLength(4, "@ETD_TIME", SQLiteInsert.ETD_TIME);
param.ParamStringFixedLength(5, "@STATUS", SQLiteInsert.STATUS);
param.ParamStringFixedLength(6, "@RESPONSIBLE", SQLiteInsert.RESPONSIBLE);
param.ParamInt32(7, "@FLAG", flag);
param.ParamDateTime(8, "@lastupdate", lastupdate);
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <MenuAuthentication> GetMenuAuthentication(int userId)
{
List <MenuAuthentication> menuAuthenList = null;
MenuAuthentication menuAuthen;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetMenuAuthentication")
};
param.ParamInt32(0, "@USER_ID", userId);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
menuAuthenList = new List <MenuAuthentication>();
while (SQLiteReader.Instance.SLReader.Read())
{
menuAuthen = new MenuAuthentication();
menuAuthen.PROG_TYPE_ID = SQLiteReader.Instance.GetInteger("PROG_TYPE_ID");
menuAuthen.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME");
menuAuthen.PG_ICON = SQLiteReader.Instance.GetString("PG_ICON");
menuAuthen.PROG_TYPE_RESOURCE = SQLiteReader.Instance.GetString("PROG_TYPE_RESOURCE");
menuAuthen.PROG_ID = SQLiteReader.Instance.GetInteger("PROG_ID");
menuAuthen.PROG_NAME = SQLiteReader.Instance.GetString("PROG_NAME");
menuAuthen.ICON = SQLiteReader.Instance.GetString("ICON");
menuAuthen.PROG_KEY = SQLiteReader.Instance.GetString("PROG_KEY");
menuAuthen.PROG_RESOURCE = SQLiteReader.Instance.GetString("PROG_RESOURCE");
menuAuthenList.Add(menuAuthen);
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
menuAuthenList = null;
}
return(menuAuthenList);
}
public string InsertProgram(ref M_Program proc)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(8)
{
CommandText = StoreProcedure.Instance.GetScript("ProgramInsert")
};
param.ParamStringFixedLength(0, "@PROG_NAME", proc.PROG_NAME);
param.ParamStringFixedLength(1, "@PROG_KEY", proc.PROG_KEY);
param.ParamInt32(2, "@PROG_TYPE_ID", proc.PROG_GROUP.PROG_TYPE_ID);
if (proc.ORDER_BY.HasValue)
{
param.ParamInt32(3, "@ORDER_BY", proc.ORDER_BY.Value);
}
else
{
param.ParamNull(3, "@ORDER_BY");
}
param.ParamStringFixedLength(4, "@ICON", proc.ICON);
param.ParamStringFixedLength(5, "@DESCRIPTION", proc.DESCRIPTION);
param.ParamStringFixedLength(6, "@PROG_RESOURCE", proc.PROG_RESOURCE);
param.ParamStringFixedLength(7, "@IS_ACTIVE", (proc.IS_ACTIVE ? "Y" : "N"));
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
object resultid = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT SEQ FROM sqlite_sequence WHERE NAME = 'M_PROGRAM'");
if (resultid != null)
{
proc.PROG_ID = Convert.ToInt32(resultid);
}
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public bool UserAuthentication(string userlogin, string password, out int userid, out string empid)
{
bool result = false;
userid = 0;
empid = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(2)
{
CommandText = StoreProcedure.Instance.GetScript("CheckValidationUser")
};
param.ParamString(0, "@USER_LOGIN", userlogin);
param.ParamString(1, "@USER_PASS", password);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
while (SQLiteReader.Instance.SLReader.Read())
{
userid = SQLiteReader.Instance.GetInteger("USER_ID");
empid = SQLiteReader.Instance.GetString("EMPLOYEE_ID");
}
result = true;
}
else
{
result = false;
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertUserProgram(List <UserProgram> lstUserProg, int userid)
{
try
{
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (UserProgram userProg in lstUserProg)
{
param = new SQLiteParam(13)
{
CommandText = StoreProcedure.Instance.GetScript("UserProgramInsert")
};
param.ParamInt32(0, "@USER_ID", userid);
param.ParamInt32(1, "@PROG_ID", userProg.PROG_ID);
param.ParamStringFixedLength(2, "@IS_ACCESS", (userProg.IS_ACCESS ? "Y" : "N"));
param.ParamStringFixedLength(3, "@IS_INSERT", (userProg.IS_INSERT ? "Y" : "N"));
param.ParamStringFixedLength(4, "@IS_UPDATE", (userProg.IS_UPDATE ? "Y" : "N"));
param.ParamStringFixedLength(5, "@IS_DELETE", (userProg.IS_DELETE ? "Y" : "N"));
param.ParamStringFixedLength(6, "@IS_PRINT", (userProg.IS_PRINT ? "Y" : "N"));
param.ParamStringFixedLength(7, "@CUSTOM1", userProg.CUSTOM1);
param.ParamStringFixedLength(8, "@IS_CUSTOM1", (userProg.IS_CUSTOM1 ? "Y" : "N"));
param.ParamStringFixedLength(9, "@CUSTOM2", userProg.CUSTOM2);
param.ParamStringFixedLength(10, "@IS_CUSTOM2", (userProg.IS_CUSTOM2 ? "Y" : "N"));
param.ParamStringFixedLength(11, "@CUSTOM3", userProg.CUSTOM3);
param.ParamStringFixedLength(12, "@IS_CUSTOM3", (userProg.IS_CUSTOM3 ? "Y" : "N"));
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
}
catch (Exception ex)
{
throw ex;
}
}
public User GetUserByUserId(int userId)
{
User user = null;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetUserById")
};
param.ParamInt32(0, "@USER_ID", userId);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
while (SQLiteReader.Instance.SLReader.Read())
{
user = new User();
user.USER_ID = SQLiteReader.Instance.GetInteger("USER_ID");
user.EMPLOYEE_ID = SQLiteReader.Instance.GetString("EMPLOYEE_ID");
user.EMPLOYEE_NAME = SQLiteReader.Instance.GetString("EMPLOYEE_NAME");
user.USER_LOGIN = SQLiteReader.Instance.GetString("USER_LOGIN");
user.USER_PASS = SQLiteReader.Instance.GetString("USER_PASS");
user.USER_REMARK = SQLiteReader.Instance.GetString("USER_REMARK");
user.IS_ACTIVE = SQLiteReader.Instance.GetString("IS_ACTIVE").ToUpper().Equals("Y");
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
user = null;
}
return(user);
}
public string InsertUser(ref User user)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(6)
{
CommandText = StoreProcedure.Instance.GetScript("UserInsert")
};
param.ParamStringFixedLength(0, "@EMPLOYEE_ID", user.EMPLOYEE_ID);
param.ParamStringFixedLength(1, "@EMPLOYEE_NAME", user.EMPLOYEE_NAME);
param.ParamStringFixedLength(2, "@USER_LOGIN", user.USER_LOGIN);
param.ParamStringFixedLength(3, "@USER_PASS", user.USER_PASS);
param.ParamStringFixedLength(4, "@USER_REMARK", user.USER_REMARK);
param.ParamStringFixedLength(5, "@IS_ACTIVE", (user.IS_ACTIVE ? "Y" : "N"));
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalSqliteDB.Instance.DataAc.LastException != null)
{
throw GlobalSqliteDB.Instance.DataAc.LastException;
}
object resultid = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT SEQ FROM sqlite_sequence WHERE NAME = 'M_USER'");
if (resultid != null)
{
user.USER_ID = Convert.ToInt32(resultid);
}
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public string UpdateProgram(M_Program proc)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(9)
{
CommandText = StoreProcedure.Instance.GetScript("ProgramUpdate")
};
param.ParamStringFixedLength(0, "@PROG_NAME", proc.PROG_NAME);
param.ParamStringFixedLength(1, "@PROG_KEY", proc.PROG_KEY);
param.ParamInt32(2, "@PROG_TYPE_ID", proc.PROG_GROUP.PROG_TYPE_ID);
if (proc.ORDER_BY.HasValue)
{
param.ParamInt32(3, "@ORDER_BY", proc.ORDER_BY.Value);
}
else
{
param.ParamNull(3, "@ORDER_BY");
}
param.ParamStringFixedLength(4, "@ICON", proc.ICON);
param.ParamStringFixedLength(5, "@DESCRIPTION", proc.DESCRIPTION);
param.ParamStringFixedLength(6, "@PROG_RESOURCE", proc.PROG_RESOURCE);
param.ParamStringFixedLength(7, "@IS_ACTIVE", (proc.IS_ACTIVE ? "Y" : "N"));
param.ParamInt32(8, "@PROG_ID", proc.PROG_ID);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public ProgramGroup GetProgramGroupById(int progGroupId)
{
ProgramGroup procGroup = null;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetProgramGroupById")
};
param.ParamInt32(0, "@PROG_TYPE_ID", progGroupId);
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
while (SQLiteReader.Instance.SLReader.Read())
{
procGroup = new ProgramGroup();
procGroup.PROG_TYPE_ID = SQLiteReader.Instance.GetInteger("PROG_TYPE_ID");
procGroup.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME");
procGroup.ORDER_BY = SQLiteReader.Instance.GetInteger("ORDER_BY");
procGroup.ICON = SQLiteReader.Instance.GetString("ICON");
procGroup.DESCRIPTION = SQLiteReader.Instance.GetString("DESCRIPTION");
procGroup.PROG_TYPE_RESOURCE = SQLiteReader.Instance.GetString("PROG_TYPE_RESOURCE");
procGroup.IS_ACTIVE = (SQLiteReader.Instance.GetString("IS_ACTIVE").ToUpper() == "Y");
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
procGroup = null;
}
return(procGroup);
}
public void UpdateUserIsOnline(int userid, bool isOnline)
{
try
{
string sql = string.Format("UPDATE M_USER SET IS_ONLINE = '{0}' WHERE USER_ID = @USER_ID;",
(isOnline ? "Y" : "N"));
SQLiteParam param = new SQLiteParam(1)
{
CommandText = sql
};
param.ParamInt32(0, "@USER_ID", userid);
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
}
catch (Exception ex)
{
throw ex;
}
}
public void UpdateDeliveryBoardDetail()
{
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("UpdateDeliveryBoardDetail")
};
param.ParamStringFixedLength(0, "@FLAG", "1");
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public string InsertProgramGroup(ref ProgramGroup procGroup)
{
string result = string.Empty;
try
{
SQLiteParam param = new SQLiteParam(6)
{
CommandText = StoreProcedure.Instance.GetScript("ProgramGroupInsert")
};
param.ParamStringFixedLength(0, "@PROG_TYPE_NAME", procGroup.PROG_TYPE_NAME);
param.ParamInt32(1, "@ORDER_BY", procGroup.ORDER_BY);
param.ParamStringFixedLength(2, "@ICON", procGroup.ICON);
param.ParamStringFixedLength(3, "@DESCRIPTION", procGroup.DESCRIPTION);
param.ParamStringFixedLength(4, "@PROG_TYPE_RESOURCE", procGroup.PROG_TYPE_RESOURCE);
param.ParamStringFixedLength(5, "@IS_ACTIVE", (procGroup.IS_ACTIVE ? "Y" : "N"));
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(param);
object resultid = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT SEQ FROM sqlite_sequence WHERE NAME = 'M_PROGRAM_TYPE'");
if (resultid != null)
{
procGroup.PROG_TYPE_ID = Convert.ToInt32(resultid);
}
result = "OK";
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public DataTable GetDeliveryDTL(string party, DateTime date, string wh, string status)
{
try
{
SQLiteParam param;
if (status.ToLower() == "delay")
{
param = new SQLiteParam(3)
{
CommandText = StoreProcedure.Instance.GetScript("GetDeliveryBoardDetail_Delay")
};
param.ParamStringFixedLength(0, "@strWH_ID", wh);
param.ParamStringFixedLength(1, "@strPARTY_ID", party);
param.ParamDateTime(2, "@strETD_DATE", date);
}
else
{
param = new SQLiteParam(3)
{
CommandText = StoreProcedure.Instance.GetScript("GetDeliveryBoardDetail")
};
param.ParamStringFixedLength(0, "@strWH_ID", wh);
param.ParamStringFixedLength(1, "@strPARTY_ID", party);
param.ParamDateTime(2, "@strETD_DATE", date);
}
DataTable dtResult = GlobalSqliteDB.Instance.DataAc.GetDataTable(param);
return(dtResult);
}
catch (Exception ex)
{
throw ex;
}
}
public void InsertStockByMachine(List <StockByMachine> lstStockByMachine, DateTime lastupdate)
{
int flag = 0;
try
{
object result = GlobalSqliteDB.Instance.DataAc.ExecuteScalar(@"SELECT IFNULL(MIN(FLAG), 1) + 1 FROM STOCK_BY_MACHINE");
flag = Convert.ToInt32(result, NumberFormatInfo.CurrentInfo);
List <SQLiteParam> lstParam = new List <SQLiteParam>();
SQLiteParam param = null;
foreach (StockByMachine SQLiteInsert in lstStockByMachine)
{
param = new SQLiteParam(18)
{
CommandText = StoreProcedure.Instance.GetScript("InsertStockByMachine")
};
param.ParamStringFixedLength(0, "@PARTY_ID", SQLiteInsert.PARTY_ID);
param.ParamStringFixedLength(1, "@PARTY_NAME", SQLiteInsert.PARTY_NAME);
if (SQLiteInsert.START_DATE.HasValue)
{
param.ParamDateTime(2, "@START_DATE", SQLiteInsert.START_DATE.Value);
}
else
{
param.ParamNull(2, "@START_DATE");
}
if (SQLiteInsert.END_DATE.HasValue)
{
param.ParamDateTime(3, "@END_DATE", SQLiteInsert.END_DATE.Value);
}
else
{
param.ParamNull(3, "@END_DATE");
}
param.ParamStringFixedLength(4, "@STATUS", SQLiteInsert.STATUS);
param.ParamInt32(5, "@PLAN_QTY", SQLiteInsert.PLAN_QTY);
param.ParamStringFixedLength(6, "@PRODUCT_TYPE", SQLiteInsert.PRODUCT_TYPE);
param.ParamStringFixedLength(7, "@PROD_TYPE_S", SQLiteInsert.PROD_TYPE_S);
param.ParamStringFixedLength(8, "@PRODUCT_NO", SQLiteInsert.PRODUCT_NO);
param.ParamStringFixedLength(9, "@PRODUCT_NAME", SQLiteInsert.PRODUCT_NAME);
param.ParamInt32(10, "@STOCK_PCS", SQLiteInsert.STOCK_PCS);
param.ParamInt32(11, "@STOCK_BOX", SQLiteInsert.STOCK_BOX);
param.ParamStringFixedLength(12, "@MC_NO", SQLiteInsert.MC_NO);
param.ParamStringFixedLength(13, "@MACHINE_NAME", SQLiteInsert.MACHINE_NAME);
param.ParamInt32(14, "@MIN_BOX", SQLiteInsert.MIN_BOX);
param.ParamInt32(15, "@MAX_BOX", SQLiteInsert.MAX_BOX);
param.ParamInt32(16, "@FLAG", flag);
param.ParamDateTime(17, "@lastupdate", lastupdate);
lstParam.Add(param);
}
if (lstParam.Count <= 0)
{
return;
}
int rowAfect = GlobalSqliteDB.Instance.DataAc.ExecuteNonQuery(lstParam);
if (GlobalSqliteDB.Instance.LastException != null)
{
throw GlobalSqliteDB.Instance.LastException;
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <M_Program> GetProgramList(int?progId)
{
List <M_Program> lstProgram = null;
M_Program mProgram;
try
{
SQLiteParam param = new SQLiteParam(1)
{
CommandText = StoreProcedure.Instance.GetScript("GetProgramList")
};
if (progId.HasValue)
{
param.ParamInt32(0, "@PROG_TYPE_ID", progId.Value);
}
else
{
param.ParamNull(0, "@PROG_TYPE_ID");
}
SQLiteReader.Instance.SLReader = GlobalSqliteDB.Instance.DataAc.ExecuteDataReader(param);
if (SQLiteReader.Instance.HasRows)
{
lstProgram = new List <M_Program>();
while (SQLiteReader.Instance.SLReader.Read())
{
mProgram = new M_Program();
mProgram.PROG_ID = SQLiteReader.Instance.GetInteger("PROG_ID");
mProgram.PROG_NAME = SQLiteReader.Instance.GetString("PROG_NAME");
mProgram.PROG_KEY = SQLiteReader.Instance.GetString("PROG_KEY");
mProgram.PROG_GROUP.PROG_TYPE_ID = SQLiteReader.Instance.GetInteger("PROG_TYPE_ID");
mProgram.PROG_GROUP.PROG_TYPE_NAME = SQLiteReader.Instance.GetString("PROG_TYPE_NAME");
mProgram.PROG_TYPE_ICON = SQLiteReader.Instance.GetString("PROG_TYPE_ICON");
if (!SQLiteReader.Instance.IsDBNull("ORDER_BY"))
{
mProgram.ORDER_BY = SQLiteReader.Instance.GetInteger("ORDER_BY");
}
mProgram.ICON = SQLiteReader.Instance.GetString("ICON");
mProgram.IS_ACTIVE = (SQLiteReader.Instance.GetString("IS_ACTIVE").ToUpper() == "Y");
mProgram.DESCRIPTION = SQLiteReader.Instance.GetString("DESCRIPTION");
mProgram.PROG_RESOURCE = SQLiteReader.Instance.GetString("PROG_RESOURCE");
lstProgram.Add(mProgram);
}
}
//Always call Close when done reading.
SQLiteReader.Instance.Close();
}
catch (Exception ex)
{
string re = ex.Message;
lstProgram = null;
}
return(lstProgram);
}