public List <WorkflowEn> GetList()
{
List <WorkflowEn> loEnList = new List <WorkflowEn>();
string sqlCmd = "SELECT * FROM sas_workflow";
try
{
if (!FormHelp.IsBlank(sqlCmd))
{
using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType,
DataBaseConnectionString, sqlCmd).CreateDataReader())
{
while (loReader.Read())
{
WorkflowEn loItem = LoadObject(loReader);
loEnList.Add(loItem);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(loEnList);
}
private WorkflowEn LoadObject(IDataReader argReader)
{
WorkflowEn loItem = new WorkflowEn();
loItem.WorkflowId = GetValue <int>(argReader, "workflow_id");
loItem.BatchCode = GetValue <string>(argReader, "batch_code");
loItem.DateTime = GetValue <DateTime>(argReader, "date_time");
loItem.WorkflowStatus = GetValue <Int16>(argReader, "workflow_status");
loItem.UserId = GetValue <string>(argReader, "user_id");
loItem.PageName = GetValue <string>(argReader, "page_name");
loItem.WorkflowRemarks = GetValue <string>(argReader, "workflow_remarks");
return(loItem);
}
public List <WorkflowEn> GetWorkflowDetails(int userid)
{
List <WorkflowEn> loEnList = new List <WorkflowEn>();
try
{
string sqlCmd = @"select distinct wf.workflow_id,mm.menuid,a.category,a.batchcode,
CASE WHEN Category IN ('Invoice','Credit Note','Debit Note') AND subtype = 'Student' THEN (select sum(transamount) from sas_accountsdetails where transid in (select transid from sas_accounts where batchcode = a.batchcode))
WHEN Category IN ('Credit Note','Debit Note') AND subtype = 'Sponsor' THEN SUM(a.tempamount)
WHEN Category IN ('Receipt') AND Description like '%CIMB CLICKS%' THEN (select SUM(transamount) as transamount from sas_accounts where batchcode = a.batchcode group by batchcode)
WHEN Category IN ('AFC') THEN (select SUM(transamount) as transamount from sas_accounts where batchcode = a.batchcode group by batchcode)
ELSE SUM(a.transamount) END as transamount,
wf.user_id as createdby,
CASE WHEN Category IN ('Receipt') AND Description like '%CIMB CLICKS%' THEN date_trunc('day',a.batchdate)
ELSE a.batchdate END as batchdate,
CASE WHEN Category IN ('AFC') THEN (select batchcode from sas_accounts where batchcode = a.batchcode group by batchcode)
ELSE a.description END as description,
wf.page_name, a.subtype
from sas_accounts a
inner join sas_workflow wf on a.batchcode=wf.batch_code
inner join sas_wf_approverlist wfa on a.batchcode=wfa.batchcode
inner join ur_menumaster mm on wfa.pagename = mm.pagename
where wf.workflow_status = 1
and a.category NOT IN ('SPA','STA')
and wfa.username = (select username from ur_users where userid =" + userid + @")
group by wf.workflow_id, mm.menuid, a.batchcode, a.category,wf.user_id,a.batchdate,a.description,wf.page_name,a.subtype
UNION
select distinct wf.workflow_id,mm.menuid,b.category,b.batchcode,
CASE WHEN Category IN ('Invoice','Credit Note','Debit Note') THEN (select sum(transamount) from sas_sponsorinvoicedetails where transid in (select transid from sas_sponsorinvoice where batchcode = b.batchcode))
ELSE SUM(b.transamount) END as transamount, wf.user_id as createdby, b.batchdate, b.description, wf.page_name, b.subtype
FROM sas_sponsorinvoice b
inner join sas_workflow wf ON b.batchcode=wf.batch_code
inner join sas_wf_approverlist wfa on b.batchcode=wfa.batchcode
inner join ur_menumaster mm on wfa.pagename = mm.pagename
where wf.workflow_status = 1
and b.category NOT IN ('SPA','STA')
and wfa.username = (select username from ur_users where userid =" + userid + @")
group by wf.workflow_id, mm.menuid, b.batchcode, b.category,wf.user_id,b.batchdate,b.description,wf.page_name,b.subtype
order by workflow_id desc";
if (!FormHelp.IsBlank(sqlCmd))
{
using (IDataReader loReader = _DatabaseFactory.ExecuteReader(Helper.GetDataBaseType,
DataBaseConnectionString, sqlCmd).CreateDataReader())
{
while (loReader.Read())
{
WorkflowEn obj = new WorkflowEn();
obj.WorkflowId = GetValue <int>(loReader, "workflow_id");
obj.MenuMasterEn = new MenuMasterEn();
obj.MenuMasterEn.MenuID = GetValue <int>(loReader, "menuid");
obj.AccountsEn = new AccountsEn();
obj.AccountsEn.Category = GetValue <string>(loReader, "category");
obj.AccountsEn.BatchCode = GetValue <string>(loReader, "batchcode");
obj.AccountsEn.TransactionAmount = GetValue <double>(loReader, "transamount");
obj.UserId = GetValue <string>(loReader, "createdby");
obj.AccountsEn.BatchDate = GetValue <DateTime>(loReader, "batchdate");
obj.AccountsEn.Description = GetValue <string>(loReader, "description");
obj.PageName = GetValue <string>(loReader, "page_name");
obj.AccountsEn.SubType = GetValue <string>(loReader, "subtype");
loEnList.Add(obj);
}
loReader.Close();
}
}
}
catch (Exception ex)
{
MaxModule.Helper.LogError(ex.Message);
}
return(loEnList);
}
