public DataSet GetPayrollBankAllocData(string rpcd, List <trpexrpdd> lstDtl, List <ColumnInfo> lstParameters)
{
string sSql = BuildSql(rpcd, lstDtl, lstParameters);
BaseBll bll = new BaseBll();
DataSet ds = bll.ExecuteRawSQLQuery(sSql);
string strXML = ConvertToXML_Personal(ds, lstDtl);
DataSet dsFin = new DataSet();
XmlReader reader = XmlReader.Create(new System.IO.StringReader(strXML));
dsFin.ReadXml(reader);
return(dsFin);
}
public DataSet GetPersonalData(string rpcd, List <trpexrpdd> lstDtl, List <ColumnInfo> lstParameters)
{
var tables = lstDtl.Select(p => p.tbnm).Distinct().ToList();
StringBuilder sbSql = new StringBuilder();
StringBuilder sbTable = new StringBuilder();
#region Build Select
var columns = lstDtl.OrderBy(p => p.sqno).ToList();
sbSql.Append("select ");
for (int i = 0; i < columns.Count; i++)
{
if (i > 0)
{
sbSql.Append(",");
}
sbSql.Append(columns[i].tbnm + "." + columns[i].itfd);
}
#endregion
#region Build From
sbSql.Append(" " + "from" + " ");
for (int i = 0; i < tables.Count; i++)
{
sbSql.Append(tables[i].ToString());
if (i < tables.Count - 1)
{
sbSql.Append(",");
}
}
#endregion
#region Build Where
sbSql.Append(" " + "where 1=1" + " ");
for (int i = 0; i < lstParameters.Count; i++)
{
if (i == 0)
{
sbSql.Append(" " + "and" + " ");
}
var q = (from p in lstDtl
where p.itfd == lstParameters[i].ColumnName
select p).ToList();
if (q.Count > 0)
{
switch (q[0].itty)
{
case "string":
case "datetime":
sbSql.Append("(" + q[0].tbnm + "." + q[0].itfd + "='" + lstParameters[i].ColumnValue.ToString() + "')");
break;
case "float":
sbSql.Append("(" + q[0].tbnm + "." + q[0].itfd + "=" + lstParameters[i].ColumnValue + ")");
break;
}
}
}
#endregion
#region Build Order
sbSql.Append(" ");
var orders = lstDtl.Where(p => p.isor == "Y").ToList();
for (int i = 0; i < orders.Count; i++)
{
if (i == 0)
{
sbSql.Append(" " + "order by" + " ");
}
sbSql.Append(orders[i].tbnm + "." + orders[i].itfd);
if (i < orders.Count - 1)
{
sbSql.Append(",");
}
}
#endregion
BaseBll bll = new BaseBll();
//List< lstObj = bll.ExecuteRawSQLQueryy<object>(sbSql.ToString());
DataSet ds = bll.ExecuteRawSQLQuery(sbSql.ToString());
string strXML = ConvertToXML_Personal(ds, lstDtl);
DataSet dsFin = new DataSet();
XmlReader reader = XmlReader.Create(new System.IO.StringReader(strXML));
dsFin.ReadXml(reader);
return(dsFin);
}
