public static string CreateSqlWarning(string NamaTabel)
{
string RetVal;
if (!DictSqlWarning.TryGetValue(NamaTabel, out RetVal))
{
string SqlQuery = "SELECT * FROM ViewWarningList WHERE TableSourceName LIKE @ts";
DataPersistance Dp = BaseFramework.DefaultDp;
IList <ViewWarningList> ListWl =
Dp.ListFastLoadEntitiesUsingSqlSelect <ViewWarningList>(
null, SqlQuery, string.Empty,
new FieldParam("ts", string.Concat("%", NamaTabel, "%")));
if (ListWl.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (ViewWarningList vwl in ListWl)
{
string SqlFilter = vwl.KodeDepartemen.Length > 0 ?
string.Concat(" AND KodeDepartemen=",
Dp.FormatSqlValue(vwl.KodeDepartemen)) : string.Empty;
if (vwl.KodeBagian.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeBagian=",
Dp.FormatSqlValue(vwl.KodeBagian));
}
if (vwl.KodeSeksi.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeSeksi=",
Dp.FormatSqlValue(vwl.KodeSeksi));
}
if (vwl.KodeGudang.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeGudang=",
Dp.FormatSqlValue(vwl.KodeGudang));
}
sb.Append(" UNION ALL SELECT ").Append(
Dp.FormatSqlValue(vwl.WarningName)).Append(
@" NamaPeringatan,NoDokumen,TglAkhir,Keterangan,KodeDepartemen,
KodeBagian,KodeSeksi,KodeGudang,Pembuat,")
.Append(Dp.FormatSqlValue(vwl.ResponsibleUser))
.Append(" PenanggungJawab,CAST(")
.Append(vwl.AutoWarningLetter ? "1" : "0")
.Append(" AS BIT) JenisWarning FROM (")
.Append(vwl.WarningQuery.Replace("@Tgl", vwl.NumDayToWarningLetter))
.Append(") x")
.Append(" WHERE NoDokumen LIKE @NoDok+'%'")
.Append(SqlFilter);
}
RetVal = sb.Remove(0, 11).ToString();
}
else
{
RetVal = string.Empty;
}
DictSqlWarning[NamaTabel] = RetVal;
}
return(RetVal);
}
private void CreateWarningLetter()
{
string SqlQuery = "SELECT * FROM ViewWarningList WHERE AutoWarningLetter=1";
DataPersistance Dp = BaseFramework.DefaultDp;
IList <ViewWarningList> ListWl =
Dp.ListFastLoadEntitiesUsingSqlSelect <ViewWarningList>(
null, SqlQuery, string.Empty);
if (ListWl.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder();
foreach (ViewWarningList vwl in ListWl)
{
string SqlFilter = vwl.KodeDepartemen.Length > 0 ?
string.Concat("KodeDepartemen=",
Dp.FormatSqlValue(vwl.KodeDepartemen)) : string.Empty;
if (vwl.KodeBagian.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeBagian=",
Dp.FormatSqlValue(vwl.KodeBagian));
}
if (vwl.KodeSeksi.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeSeksi=",
Dp.FormatSqlValue(vwl.KodeSeksi));
}
if (vwl.KodeGudang.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeGudang=",
Dp.FormatSqlValue(vwl.KodeGudang));
}
if (SqlFilter.StartsWith(" AND"))
{
SqlFilter = " WHERE " + SqlFilter.Substring(5);
}
else if (SqlFilter.Length > 0)
{
SqlFilter = " WHERE " + SqlFilter;
}
sb.Append(" UNION ALL SELECT ").Append(
Dp.FormatSqlValue(vwl.WarningName)).Append(
@" NamaPeringatan,NoDokumen,TglAkhir,Keterangan,KodeDepartemen,
KodeBagian,KodeSeksi,KodeGudang,Pembuat FROM (")
.Append(vwl.WarningQuery.Replace("@Tgl", vwl.NumDayToWarningLetter))
.Append(") x").Append(SqlFilter);
}
SqlQuery = string.Concat("SELECT * FROM (",
sb.ToString().Substring(10), @") x LEFT JOIN WarningLetter wl ON
x.NamaPeringatan=wl.WarningName AND x.NoDokumen=wl.ReffDocNumber
WHERE wl.WarningName IS NULL AND TglAkhir<",
Dp.FormatSqlValue(DateTime.Today));
IList <clsWarningList> ListWarning = Dp
.ListFastLoadEntitiesUsingSqlSelect <clsWarningList>(null,
SqlQuery, "NamaPeringatan,TglAkhir");
try
{
//using (EntityTransaction tr = new EntityTransaction(Dp))
//{
// foreach (clsWarningList wl in ListWarning)
// {
// SuratPeringatan sp = new SuratPeringatan(NoReg,
// wl.NamaPeringatan);
// sp.SaveNew();
// new WarningLetter(wl.NamaPeringatan,
// sp.NoSuratPeringatan, wl.NoDokumen).SaveNew();
// }
// tr.CommitTransaction();
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Pembuatan SP",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public IList <clsWarningList> CreateDataSource(bool Awl)
{
string SqlQuery = @"SELECT * FROM ViewWarningList
WHERE ResponsibleUser=@User AND AutoWarningLetter=@awl";
DataPersistance Dp = SDMDp;
IList <ViewWarningList> ListWl =
Dp.ListFastLoadEntitiesUsingSqlSelect <ViewWarningList>(
null, SqlQuery, string.Empty,
new FieldParam("User", BaseSecurity.CurrentLogin.CurrentUser),
new FieldParam("awl", Awl));
if (ListWl.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (ViewWarningList vwl in ListWl)
{
string SqlFilter = vwl.KodeDepartemen.Length > 0 ?
string.Concat("KodeDepartemen=",
Dp.FormatSqlValue(vwl.KodeDepartemen)) : string.Empty;
if (vwl.KodeBagian.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeBagian=",
Dp.FormatSqlValue(vwl.KodeBagian));
}
if (vwl.KodeSeksi.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeSeksi=",
Dp.FormatSqlValue(vwl.KodeSeksi));
}
if (vwl.KodeGudang.Length > 0)
{
SqlFilter = string.Concat(SqlFilter, " AND KodeGudang=",
Dp.FormatSqlValue(vwl.KodeGudang));
}
if (SqlFilter.StartsWith(" AND"))
{
SqlFilter = " WHERE " + SqlFilter.Substring(5);
}
else if (SqlFilter.Length > 0)
{
SqlFilter = " WHERE " + SqlFilter;
}
sb.Append(" UNION ALL SELECT ").Append(
Dp.FormatSqlValue(vwl.WarningName)).Append(
@" NamaPeringatan,NoDokumen,TglAkhir,Keterangan,KodeDepartemen,
KodeBagian,KodeSeksi,KodeGudang,Pembuat FROM (")
.Append(vwl.WarningQuery.Replace("@Tgl", vwl.NumDayToWarningLetter))
.Append(") x").Append(SqlFilter);
}
sb.Remove(0, 11);
return(Dp.ListFastLoadEntitiesUsingSqlSelect <clsWarningList>(null,
sb.ToString(), "NamaPeringatan,TglAkhir"));
}
else
{
return(new List <clsWarningList>());
}
}