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>()); } }