public static List <VasModel> DownloadAllVases()
{
using (IDbConnection connection = new iDB2Connection(ConnectionHelper.CnnVall("reflex")))
{
var result = connection.Query <VasModel>(
"SELECT distinct EVLVAS as Description, EVCICO as Flag, EVVIFC as FlagValue FROM GUEPRD_DAT.GNSDEVP WHERE EVTACC = 'SO'").ToList();
return(result);
}
}
// Old Version of vases - to delete after merge
//public static List<VasModel> GetVasListOfHd(string hd)
//{
// using (IDbConnection connection = new iDB2Connection(ConnectionHelper.CnnVall("reflex")))
// {
// return connection.Query<VasModel>(
// $"SELECT VAVCOD as Description FROM GUEPRD_DAT.gnvacop WHERE VANCOL = '{hd}'").ToList();
// }
//}
public static string DownloadOrderForHd(string hd)
{
using (IDbConnection connection = new iDB2Connection(ConnectionHelper.CnnVall("reflex")))
{
string Gei = connection.QuerySingleOrDefault <string>(
$"select distinct GENGEI from gueprddb.hlgeinp where GENSUP = '{hd}' limit 1");
var Ligne = connection.Query(
$"select distinct LGNANN, LGNLPR from gueprddb.HLLPRGP where LGNGEI = '{Gei}' limit 1").Single();
var Order = connection.Query(
$"select distinct P1NANO, P1NODP from gueprddb.HLPRPLP where P1NANN = '{Ligne.LGNANN}' and P1NLPR = '{Ligne.LGNLPR}' limit 1").Single();
string OrderReference = connection.QuerySingleOrDefault <string>(
$"select distinct OERODP from gueprddb.HLODPEP where OENANN = '{Order.P1NANO}' and OENODP = '{Order.P1NODP}' limit 1");
return(OrderReference.Trim());
}
}
//read full set data
public List <FPL1LTR> Get_GroupABCDE_dataFromAS400(string subgroup) //without parameters
{
List <FPL1LTR> result = new List <FPL1LTR>();
//SELECT PTNNO, PTNCNY, PTNMACAU, LTRNO, LTRGRP, LTRSUBGRP FROM zvincent.fpl1ltr WHERE ltrgrp<>'' ORDER BY LTRGRP,LTRSUBGRP,PTNMACAU,PTNCNY,PTNNO
string sqlStr = @"select * from zvincent.fpl1ltr WHERE ltrgrp<>'' and ltrsubgrp=@subgroup_P and (idx between @pFrom and @pTo) ORDER BY PTNMACAU,PTNCNY,PAGE,PTNNO for read only ";
using (iDB2Connection conn = new iDB2Connection(GlobalVar.getAS400ConnString())) //get as400 conn string in as400Model
{
conn.Open();
result = conn.Query <FPL1LTR>(sqlStr, new { subgroup_P = subgroup, pFrom = Convert.ToInt16(textBoxFrom.Text), pTo = Convert.ToInt32(textBoxTo.Text) }).ToList(); //To list (L1 letters)
} //------- end
return(result);
}
public static List <HdModel> DownloadBoxesForOrder(string order)
{
using (IDbConnection connection = new iDB2Connection(ConnectionHelper.CnnVall("reflex")))
{
var result = connection.Query <HdModel>(
"select distinct digits(GENSUP) as HdNumber, OERODP as OrderName from gueprddb.hlgeinp " +
"inner join gueprddb.HLLPRGP on LGNGEI = GENGEI " +
"inner join gueprddb.HLPRPLP on P1NANN = LGNANN and P1NLPR = LGNLPR " +
"inner join gueprddb.HLODPEP on OENANN = P1NANO and OENODP = P1NODP " +
$"where OERODP = '{order}'").ToList();
return(result);
;
}
}
//read AS400 zvincent.FPL1LTR to form FILENAME.pdf (Group A, B, C, D, E) output
public List <PDF_FileName> Get_PDF_FilenameFromAS400(string subgroup) //output PDF filename
{
List <PDF_FileName> result = new List <PDF_FileName>();
string sqlStr = @"SELECT PAGENO,PAGE,LTRSUBGRP,PTNCNY,trim(LTRSUBGRP)||'提款信'||'_'||ptncny||'_'||trim(frmIdx)||'-'||trim(toIdx)||'_'||'page'||pageNo||'_'||PAGE||'.PDF' FileName
From (SELECT ltrsubgrp,ptnmacau,ptncny,min(idx) frmIdx, max(idx) toidx,max(idx) - min(idx) + 1 pageNo, PAGE
from (SELECT case when length(trim(ltrsubgrp))=1 then integer(substr(ltrno,10)) else integer(substr(ltrno,11)) end idx,ltrsubgrp,ptnmacau,ptncny,PAGE
FROM zvincent.FPL1LTR
where ltrgrp <> '' and ltrsubgrp=@subgroup_P) a
group by ltrsubgrp,ptnmacau,ptncny,PAGE) a
order by ltrsubgrp,ptnmacau,ptncny,PAGE FOR READ ONLY";
using (iDB2Connection conn = new iDB2Connection(GlobalVar.getAS400ConnString())) //get as400 conn string in as400Model
{
conn.Open();
result = conn.Query <PDF_FileName>(sqlStr, new { subgroup_P = subgroup }).ToList(); //(pdf letters)
} //------- end
return(result);
}
public IEnumerable<SectionDetailCacheItem> GetSectionDetailInformationFromGenius(string[] policyReferences)
{
using (var connection = new iDB2Connection(_connectionString))
{
connection.Open();
var result = new List<SectionDetailCacheItem>();
foreach (var chunk in policyReferences.Chunk<string>(100))
{
if (!chunk.Any()) break;
result.AddRange(connection.Query<SectionDetailCacheItem>(
GetSectionDetailInformation,
new
{
PolicyReferences = chunk
},
null,
true,
600));
}
_sectionDetailCache = new Hashtable(result.ToDictionary(i => i.SectionDetailKey, i => i));
return result;
}
}
// TODO
public static List <VasModel> DownloadVasesForOrder(string orderName)
{
using (IDbConnection connection = new iDB2Connection(ConnectionHelper.CnnVall("reflex")))
{
string query = $@"
select Q.Parameter as Flag, Q.Value as FlagValue
FROM GUEPRD_DAT.GNORHEP as S,
TABLE(VALUES
('ORD_1', FL01OT), ('ORD_2', FL02OT), ('ORD_3', FL03OT),
('ORD_4', FL04OT), ('ORD_5', FL05OT), ('ORD_6', FL06OT),
('ORD_7', FL07OT), ('ORD_8', FL08OT), ('ORD_9', FL09OT),
('ORD_11', FL11OT), ('ORD_12', FL12OT), ('ORD_13', FL13OT),
('ORD_14', FL14OT), ('ORD_15', FL15OT), ('ORD_16', FL16OT),
('ORD_17', FL17OT), ('ORD_18', FL18OT), ('ORD_19', FL19OT),
('ORD_20', FL20OT))
AS Q(Parameter, Value)
where NORDOT = '{orderName}' " ;
var result = connection.Query <VasModel>(query).ToList();
return(result);
}
}
