public CpPackageValueInfo[] GetByFilter(String tvcdb, List <DTO.Criteria> filters, ref string sErr, int indexPage = 0, int itemPerPage = 0)
{
DataTable list = GetByFilterToDataTable(tvcdb, filters, ref sErr, indexPage, itemPerPage);
if (!string.IsNullOrEmpty(sErr))
{
return(null);
}
CpPackageValueInfo[] res = null;
try
{
res = new CpPackageValueInfo[list.Rows.Count];
for (int i = 0; i < list.Rows.Count; i++)
{
res[i] = new CpPackageValueInfo(list.Rows[i]);
}
}
catch (Exception ex) { sErr = ex.Message; }
if (!string.IsNullOrEmpty(sErr))
{
return(null);
}
return(res);
}
public string Update(CpPackageValueInfo obj)
{
string sErr = _objDAO.Update(obj);
// delete details
// add details
return(sErr);
}
public int Add(CpPackageValueInfo obj, ref string sErr)
{
_objDAO.Add(obj, ref sErr);
if (string.IsNullOrEmpty(sErr))
{
//add details
}
return(1);
}
public string InsertUpdate(CpPackageValueInfo obj)
{
string sErr = string.Empty;
if (IsExist(obj.tvcdb, obj.packagecode, obj.valueid, ref sErr))
{
sErr = Update(obj);
}
else
{
obj.createdby = USER_ID;
if (string.IsNullOrEmpty(sErr))
{
Add(obj, ref sErr);
}
}
return(sErr);
}
public string Update(CpPackageValueInfo objEntr)
{
string sql = @"UPDATE cppackagevalue SET ";
if (objEntr.transdate != null)
{
sql += "transdate = @transdate,";
}
if (objEntr.employee != null)
{
sql += "employee = @employee,";
}
if (objEntr.valuedesc != null)
{
sql += "valuedesc = @valuedesc,";
}
if (objEntr.amount != null)
{
sql += "amount = @amount,";
}
if (objEntr.original != null)
{
sql += "original = @original,";
}
if (objEntr.status != null)
{
sql += "status = @status,";
}
if (objEntr.comments != null)
{
sql += "comments = @comments,";
}
if (objEntr.approvalstatus != null)
{
sql += "approvalstatus = @approvalstatus,";
}
if (objEntr.approvedby != null)
{
sql += "approvedby = @approvedby,";
}
if (objEntr.approveddate != null)
{
sql += "approveddate = @approveddate,";
}
if (objEntr.approvednote != null)
{
sql += "approvednote = @approvednote,";
}
if (objEntr.adjustno != null)
{
sql += "adjustno = @adjustno,";
}
if (objEntr.approvedref != null)
{
sql += "approvedref = @approvedref,";
}
if (objEntr.updatedby != null)
{
sql += "updatedby = @updatedby,";
}
sql += " lastupdate = getdate()";
sql += @" WHERE tvcdb = @tvcdb AND packagecode = @packagecode AND valueid = @valueid";
openConnection();
InitCommand(sql);
AddParameter(CpPackageValueInfo.Field.tvcdb, objEntr.tvcdb);
AddParameter(CpPackageValueInfo.Field.packagecode, objEntr.packagecode);
AddParameter(CpPackageValueInfo.Field.valueid, objEntr.valueid);
if (objEntr.transdate != null)
{
AddParameter(CpPackageValueInfo.Field.transdate, objEntr.transdate);
}
if (objEntr.employee != null)
{
AddParameter(CpPackageValueInfo.Field.employee, objEntr.employee);
}
if (objEntr.valuedesc != null)
{
AddParameter(CpPackageValueInfo.Field.valuedesc, objEntr.valuedesc);
}
if (objEntr.amount != null)
{
AddParameter(CpPackageValueInfo.Field.amount, objEntr.amount);
}
if (objEntr.original != null)
{
AddParameter(CpPackageValueInfo.Field.original, objEntr.original);
}
if (objEntr.status != null)
{
AddParameter(CpPackageValueInfo.Field.status, objEntr.status);
}
if (objEntr.comments != null)
{
AddParameter(CpPackageValueInfo.Field.comments, objEntr.comments);
}
if (objEntr.approvalstatus != null)
{
AddParameter(CpPackageValueInfo.Field.approvalstatus, objEntr.approvalstatus);
}
if (objEntr.approvedby != null)
{
AddParameter(CpPackageValueInfo.Field.approvedby, objEntr.approvedby);
}
if (objEntr.approveddate != null)
{
AddParameter(CpPackageValueInfo.Field.approveddate, objEntr.approveddate);
}
if (objEntr.approvednote != null)
{
AddParameter(CpPackageValueInfo.Field.approvednote, objEntr.approvednote);
}
if (objEntr.adjustno != null)
{
AddParameter(CpPackageValueInfo.Field.adjustno, objEntr.adjustno);
}
if (objEntr.approvedref != null)
{
AddParameter(CpPackageValueInfo.Field.approvedref, objEntr.approvedref);
}
if (objEntr.updatedby != null)
{
AddParameter(CpPackageValueInfo.Field.updatedby, objEntr.updatedby);
}
string sErr = string.Empty;
try { executeNonQuery(); }
catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
finally { closeConnection(); }
return(sErr);
}
/// <summary>
/// Return 1: Table is exist Identity Field
/// Return 0: Table is not exist Identity Field
/// Return -1: Erro
/// </summary>
/// <param name="tableName"></param>
public int Add(CpPackageValueInfo objEntr, ref string sErr)
{
string sqlFields = @" INSERT INTO cppackagevalue(tvcdb,packagecode,valueid,";
string sqlValues = @" VALUES(@tvcdb,@packagecode,@valueid,";
if (objEntr.transdate != null)
{
sqlFields += " transdate,"; sqlValues += " @transdate,";
}
if (objEntr.employee != null)
{
sqlFields += " employee,"; sqlValues += " @employee,";
}
if (objEntr.valuedesc != null)
{
sqlFields += " valuedesc,"; sqlValues += " @valuedesc,";
}
if (objEntr.amount != null)
{
sqlFields += " amount,"; sqlValues += " @amount,";
}
if (objEntr.original != null)
{
sqlFields += " original,"; sqlValues += " @original,";
}
if (objEntr.status != null)
{
sqlFields += " status,"; sqlValues += " @status,";
}
if (objEntr.comments != null)
{
sqlFields += " comments,"; sqlValues += " @comments,";
}
if (objEntr.approvalstatus != null)
{
sqlFields += " approvalstatus,"; sqlValues += " @approvalstatus,";
}
if (objEntr.approvedby != null)
{
sqlFields += " approvedby,"; sqlValues += " @approvedby,";
}
if (objEntr.approveddate != null)
{
sqlFields += " approveddate,"; sqlValues += " @approveddate,";
}
if (objEntr.approvednote != null)
{
sqlFields += " approvednote,"; sqlValues += " @approvednote,";
}
if (objEntr.adjustno != null)
{
sqlFields += " adjustno,"; sqlValues += " @adjustno,";
}
if (objEntr.approvedref != null)
{
sqlFields += " approvedref,"; sqlValues += " @approvedref,";
}
if (objEntr.createdby != null)
{
sqlFields += " createdby,"; sqlValues += " @createdby,";
}
if (objEntr.updatedby != null)
{
sqlFields += " updatedby,"; sqlValues += " @updatedby,";
}
sqlFields = sqlFields.Substring(0, sqlFields.Length - 1) + ")";
sqlValues = sqlValues.Substring(0, sqlValues.Length - 1) + ")";
int ret = -1;
string sql = sqlFields + sqlValues;
openConnection();
InitCommand(sql);
AddParameter(CpPackageValueInfo.Field.tvcdb, objEntr.tvcdb);
AddParameter(CpPackageValueInfo.Field.packagecode, objEntr.packagecode);
AddParameter(CpPackageValueInfo.Field.valueid, objEntr.valueid);
if (objEntr.transdate != null)
{
AddParameter(CpPackageValueInfo.Field.transdate, objEntr.transdate);
}
if (objEntr.employee != null)
{
AddParameter(CpPackageValueInfo.Field.employee, objEntr.employee);
}
if (objEntr.valuedesc != null)
{
AddParameter(CpPackageValueInfo.Field.valuedesc, objEntr.valuedesc);
}
if (objEntr.amount != null)
{
AddParameter(CpPackageValueInfo.Field.amount, objEntr.amount);
}
if (objEntr.original != null)
{
AddParameter(CpPackageValueInfo.Field.original, objEntr.original);
}
if (objEntr.status != null)
{
AddParameter(CpPackageValueInfo.Field.status, objEntr.status);
}
if (objEntr.comments != null)
{
AddParameter(CpPackageValueInfo.Field.comments, objEntr.comments);
}
if (objEntr.approvalstatus != null)
{
AddParameter(CpPackageValueInfo.Field.approvalstatus, objEntr.approvalstatus);
}
if (objEntr.approvedby != null)
{
AddParameter(CpPackageValueInfo.Field.approvedby, objEntr.approvedby);
}
if (objEntr.approveddate != null)
{
AddParameter(CpPackageValueInfo.Field.approveddate, objEntr.approveddate);
}
if (objEntr.approvednote != null)
{
AddParameter(CpPackageValueInfo.Field.approvednote, objEntr.approvednote);
}
if (objEntr.adjustno != null)
{
AddParameter(CpPackageValueInfo.Field.adjustno, objEntr.adjustno);
}
if (objEntr.approvedref != null)
{
AddParameter(CpPackageValueInfo.Field.approvedref, objEntr.approvedref);
}
if (objEntr.createdby != null)
{
AddParameter(CpPackageValueInfo.Field.createdby, objEntr.createdby);
}
if (objEntr.updatedby != null)
{
AddParameter(CpPackageValueInfo.Field.updatedby, objEntr.updatedby);
}
try
{
object tmp = executeScalar();
if (tmp != null && tmp != DBNull.Value)
{
ret = Convert.ToInt32(tmp);
}
else
{
ret = 0;
}
}
catch (Exception ex) { sErr = string.Format("{0} sql='{1}'", ex.Message, sql); }
finally { closeConnection(); }
return(ret);
}
