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