} // addMissingColumns
/// <summary>
///
/// </summary>
/// <param name="pf"></param>
/// <returns></returns>
public int AddMissingColumns(MPrintFormat pf)
{
String sql = "SELECT c.AD_Column_ID, c.ColumnName "
+ "FROM AD_Column c "
+ "WHERE NOT EXISTS "
+ "(SELECT * "
+ "FROM AD_PrintFormatItem pfi"
+ " INNER JOIN AD_PrintFormat pf ON (pfi.AD_PrintFormat_ID=pf.AD_PrintFormat_ID) "
+ "WHERE pf.AD_Table_ID=c.AD_Table_ID"
+ " AND pfi.AD_Column_ID=c.AD_Column_ID"
+ " AND pfi.AD_PrintFormat_ID='" + pf.GetAD_PrintFormat_ID() + "')" // 1
+ " AND c.AD_Table_ID='" + pf.GetAD_Table_ID() + "' " // 2
+ "ORDER BY 1";
int counter = 0;
IDataReader dr = null;
try
{
dr = DataBase.DB.ExecuteReader(sql);
while (dr.Read())
{
int AD_Column_ID = Utility.Util.GetValueOfInt(dr[0].ToString());
String ColumnName = dr.GetString(1);
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(pf, AD_Column_ID, 0);
if (pfi.Get_ID() != 0)
{ //log
}
else
{ //log
}
}
dr.Close();
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
}
log.Severe(e.ToString());
}
return(counter);
} // addMissingColumns
} // copy
/// <summary>
/// Create Items.
/// Using the display order of Fields in some Tab
/// </summary>
/// <param name="ctx">context</param>
/// <param name="format">format</param>
/// <returns>items</returns>
static private MPrintFormatItem[] CreateItems(Ctx ctx, MPrintFormat format)
{
List <MPrintFormatItem> list = new List <MPrintFormatItem>();
// Get Column List from Tab
String sql = "SELECT AD_Column_ID " //, Name, IsDisplayed, SeqNo
+ "FROM AD_Field "
+ "WHERE AD_Tab_ID=(SELECT MIN(AD_Tab_ID) FROM AD_Tab WHERE AD_Table_ID=@AD_Table_ID)"
+ " AND IsEncrypted='N' AND IsDisplayed='Y' AND ObscureType IS NULL "
+ "ORDER BY COALESCE(IsDisplayed,'N') DESC, SortNo, COALESCE(MRSeqNo, SeqNo), Name";
IDataReader dr = null;
try
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@AD_Table_ID", format.GetAD_Table_ID());
dr = SqlExec.ExecuteQuery.ExecuteReader(sql, param);
int seqNo = 1;
while (dr.Read())
{
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(format, Utility.Util.GetValueOfInt(dr[0].ToString()), seqNo++);
if (pfi != null)
{
list.Add(pfi);
}
}
dr.Close();
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
}
s_log.Severe(e.ToString());
//log, if any
}
// No Tab found for Table
if (list.Count == 0)
{
sql = "SELECT AD_Column_ID "
+ "FROM AD_Column "
+ "WHERE AD_Table_ID='" + format.GetAD_Table_ID() + "' "
+ "ORDER BY IsIdentifier DESC, SeqNo, Name";
IDataReader idr = null;
try
{
idr = SqlExec.ExecuteQuery.ExecuteReader(sql);
// DataTable dt = new DataTable();
//dt.Load(idr);
int seqNo = 1;
while (idr.Read())
{
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(format, Utility.Util.GetValueOfInt(idr[0].ToString()), seqNo++);
if (pfi != null)
{
list.Add(pfi);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
s_log.Severe(e.ToString());
//log, if any
}
}
//
MPrintFormatItem[] retValue = new MPrintFormatItem[list.Count];
retValue = list.ToArray();
return(retValue);
} // createItems
} // copy
/// <summary>
/// Create Items.
/// Using the display order of Fields in some Tab
/// </summary>
/// <param name="ctx">context</param>
/// <param name="format">format</param>
/// <returns>items</returns>
/// <summary>
/// Create Items.
/// Using the display order of Fields in some Tab
/// </summary>
/// <param name="ctx">context</param>
/// <param name="format">format</param>
/// <returns>items</returns>
static private MPrintFormatItem[] CreateItems(Ctx ctx, MPrintFormat format, int AD_tab_ID, bool isMRSeq = false)
{
List <MPrintFormatItem> list = new List <MPrintFormatItem>();
bool runOldCode = true;
// Get Column List from Tab
String sql = "SELECT AD_Column_ID " //, Name, IsDisplayed, SeqNo
+ "FROM AD_Field "
+ "WHERE ";
if (AD_tab_ID == 0)
{
sql += " AD_Tab_ID=(SELECT MIN(AD_Tab_ID) FROM AD_Tab WHERE AD_Table_ID=@AD_Table_ID)";
}
else
{
sql += " AD_Tab_ID=@AD_Table_ID";
}
//added check on 03/12/14 - "AND IsDisplayed='Y' AND IsActive='Y'"
sql += " AND IsEncrypted='N' AND (IsDisplayed='Y' OR MRISDISPLAYED='Y') AND IsActive='Y' AND ObscureType IS NULL ";
//Lakhwinder
if (isMRSeq)
{
try
{
runOldCode = false;
sql = "SELECT AD_Column_ID,Ad_Field_ID FROM AD_Field WHERE ";
if (AD_tab_ID == 0)
{
sql += " AD_Tab_ID=(SELECT MIN(AD_Tab_ID) FROM AD_Tab WHERE AD_Table_ID=@AD_Table_ID)";
}
else
{
sql += " AD_Tab_ID=@AD_Table_ID";
}
//added check on 03/12/14 - "AND IsDisplayed='Y' AND IsActive='Y'"
sql += " AND IsEncrypted='N' AND (IsDisplayed='Y' OR MRISDISPLAYED='Y') AND IsActive='Y' AND ObscureType IS NULL ";
bool isMESeqDefined = false;
//if multirow sequence is set on tab then MRIsDisplayed Column does have non null value
int count = Util.GetValueOfInt(DB.ExecuteScalar("SELECT COUNT(*) FROM AD_Field WHERE AD_Tab_ID =" + AD_tab_ID + " AND MRIsDisplayed IS NOT NULL"));
if (count > 0)//tab has defined multirow sequence
{
isMESeqDefined = true;
sql += " ORDER BY MRSeqNo ";
}
else//tab has undefined multirow sequence
{
sql += " ORDER BY NVL(MRSeqNo,999999),Name ";
}
SqlParameter[] param = new SqlParameter[1];
if (AD_tab_ID == 0)
{
param[0] = new SqlParameter("@AD_Table_ID", format.GetAD_Table_ID());
}
else
{
param[0] = new SqlParameter("@AD_Table_ID", AD_tab_ID);
}
DataSet ds = DB.ExecuteDataset(sql, param);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(format, Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_Column_ID"]), Util.GetValueOfInt(ds.Tables[0].Rows[i]["AD_Field_ID"]), i + 1, isMESeqDefined);
if (pfi != null)
{
list.Add(pfi);
}
}
}
}
catch
{
runOldCode = true;
sql += " ORDER BY COALESCE(MRISDISPLAYED,IsDisplayed) DESC, SortNo, MRSeqNo, COALESCE(IsDisplayed,'N'), SeqNo";
}
}
else
{
sql += " ORDER BY COALESCE(MRISDISPLAYED,IsDisplayed) DESC, SortNo, MRSeqNo, COALESCE(IsDisplayed,'N'), SeqNo";
}
// + "ORDER BY COALESCE(IsDisplayed,'N') DESC, SortNo, COALESCE(MRSeqNo, SeqNo), Name";
if (runOldCode)
{
IDataReader dr = null;
try
{
SqlParameter[] param = new SqlParameter[1];
if (AD_tab_ID == 0)
{
param[0] = new SqlParameter("@AD_Table_ID", format.GetAD_Table_ID());
}
else
{
param[0] = new SqlParameter("@AD_Table_ID", AD_tab_ID);
}
dr = SqlExec.ExecuteQuery.ExecuteReader(sql, param);
int seqNo = 1;
while (dr.Read())
{
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(format, Utility.Util.GetValueOfInt(dr[0].ToString()), seqNo++);
if (pfi != null)
{
list.Add(pfi);
}
}
dr.Close();
}
catch (Exception e)
{
if (dr != null)
{
dr.Close();
}
s_log.Severe(e.ToString());
//log, if any
}
}
// No Tab found for Table
if (list.Count == 0)
{
sql = "SELECT AD_Column_ID "
+ "FROM AD_Column "
+ "WHERE AD_Table_ID='" + format.GetAD_Table_ID() + "' "
+ "ORDER BY IsIdentifier DESC, SeqNo, Name";
IDataReader idr = null;
try
{
idr = SqlExec.ExecuteQuery.ExecuteReader(sql);
// DataTable dt = new DataTable();
//dt.Load(idr);
int seqNo = 1;
while (idr.Read())
{
MPrintFormatItem pfi = MPrintFormatItem.CreateFromColumn(format, Utility.Util.GetValueOfInt(idr[0].ToString()), seqNo++);
if (pfi != null)
{
list.Add(pfi);
}
}
idr.Close();
}
catch (Exception e)
{
if (idr != null)
{
idr.Close();
}
s_log.Severe(e.ToString());
//log, if any
}
}
//
MPrintFormatItem[] retValue = new MPrintFormatItem[list.Count];
retValue = list.ToArray();
return(retValue);
} // createItems
