public static bool CanLogin(DataPersistance dp,
string RoleName, string UserName, string UserPassword)
{
string TrueStr = dp.FormatSqlValue(true);
string FalseStr = dp.FormatSqlValue(false);
dp.ValidateTableDef <User>();
dp.ValidateTableDef <RoleUser>();
string SqlQuery;
if (BaseSecurity.LoginWithRole)
{
SqlQuery = string.Concat(@"SELECT U.UserName
FROM (_System_User AS U INNER JOIN _System_RoleUser AS RU ON
U.UserName=RU.UserName) INNER JOIN _System_Role AS R ON
RU.RoleName=R.RoleName AND RU.ProductName=R.ProductName
WHERE RU.ProductName=@3 AND U.IsActive=", TrueStr,
@" AND U.UserName=@0 AND U.UserPassword=@1
AND (U.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(),
" BETWEEN U.StartDate AND U.EndDate) AND R.IsActive=",
TrueStr, @" AND R.RoleName=@2 AND
(R.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(),
" BETWEEN R.StartDate AND R.EndDate)");
return(dp.Find.IsExists(SqlQuery,
new FieldParam("0", UserName),
new FieldParam("1", UserPassword),
new FieldParam("2", RoleName),
new FieldParam("3", BaseFramework.ProductName)));
}
else
{
SqlQuery = string.Concat(@"SELECT U.UserName
FROM (_System_User AS U INNER JOIN _System_RoleUser AS RU ON
U.UserName=RU.UserName) INNER JOIN _System_Role AS R ON
RU.RoleName=R.RoleName AND RU.ProductName=R.ProductName
WHERE RU.ProductName=@3 AND U.IsActive=", TrueStr,
@" AND U.UserName=@0 AND U.UserPassword=@1
AND (U.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(),
" BETWEEN U.StartDate AND U.EndDate) AND R.IsActive=",
TrueStr, @" AND
(R.UseDateLimit=", FalseStr, " OR ", dp.GetSqlNow(),
" BETWEEN R.StartDate AND R.EndDate)");
return(dp.Find.IsExists(SqlQuery,
new FieldParam("0", UserName),
new FieldParam("1", UserPassword),
new FieldParam("3", BaseFramework.ProductName)));
}
}
/// <summary>
/// Update Field MataUangDasr di semua akun
/// </summary>
/// <param name="Dp"></param>
/// <param name="MataUang"></param>
public static void SetMataUangDasar(DataPersistance Dp,
string MataUang)
{
Dp.ExecuteNonQuery(string.Concat(
"UPDATE Akun SET MataUangDasar=",
Dp.FormatSqlValue(false, DataType.Boolean),
" WHERE MataUangDasar=",
Dp.FormatSqlValue(true, DataType.Boolean)));
Dp.ExecuteNonQuery(string.Concat(
"UPDATE Akun SET MataUangDasar=",
Dp.FormatSqlValue(true, DataType.Boolean),
" WHERE KodeMataUang=@0"),
new FieldParam("0", MataUang));
}
private void UpdateStrQuery()
{
string tmpFilter;
if (_Filter.Length > 0)
{
tmpFilter = _Filter;
}
else
{
tmpFilter = "1=1";
}
string TmpStr = DataPersistance.BuildKeyField(td);
if (_OrderFld._dtlsa == null)
{
strQuery = string.Concat("SELECT KeyField FROM (SELECT ",
TmpStr, " AS KeyField,", _OrderFld.FieldName, " FROM ",
td._TableName, " WHERE (", tmpFilter, ")) AS X");
_CriteriaPos = strQuery.Length - 6;
}
else
{
strQuery = string.Concat("SELECT KeyField FROM (SELECT KeyField,",
DataPersistance.GetSqlCoalesceNoFormat(_OrderFld.FieldName,
DataPersistance.FormatSqlValue(string.Empty)),
" AS ", _OrderFld.FieldName, " FROM (SELECT ", TmpStr, " AS KeyField,(", _OrderFld._dtlsa._SqlQuery, ") AS ",
_OrderFld.FieldName, " FROM ",
td._TableName, " WHERE (", tmpFilter, ")) AS X) AS X");
_CriteriaPos = strQuery.Length - 12;
}
IsKeyFieldEqualOrderField = TmpStr == _OrderFld.FieldName;
}
public void DrawTree(TreeListNode ParentNode)
{
//string ParentValue = ParentNode == null ?
// (string)TreeList.RootValue : (string)((object[])ParentNode.Tag)[0];
string ParentValue = ParentNode == null ?
(string)TreeList.RootValue : ((MyNode)ParentNode.Tag).KeyField;
IList <object> ListEntity =
Dp.FastLoadEntities(Td.ClassType, null,
string.Join(",", Columns), string.Concat(
TreeList.ParentFieldName, "=",
Dp.FormatSqlValue(ParentValue)), OrderField);
//if (ParentNode != null) ((object[])ParentNode.Tag)[1] = true;
if (ParentNode != null)
{
((MyNode)ParentNode.Tag).IsLoad = true;
}
if (ListEntity == null || ListEntity.Count == 0)
{
if (ParentNode != null)
{
ParentNode.HasChildren = false;
}
return;
}
object[] NodeValue = new object[Columns.Length - 1];
TreeList.BeginUnboundLoad();
try
{
foreach (object Entity in ListEntity)
{
for (int i = 1; i < Columns.Length; i++)
{
NodeValue[i - 1] = Td.GetFieldDef(Columns[i])
.GetValue(Entity);
}
TreeListNode Node = TreeList.AppendNode(NodeValue, ParentNode);
MyNode nd = new MyNode(Node,
(string)Td.GetFieldDef(Columns[0])
.GetValue(Entity));
Node.Tag = nd;
//Node.Tag = new object[2] {
// Td.GetFieldDef(Columns[0]).GetValue(Entity), false };
if (onAfterAddNode != null)
{
onAfterAddNode(Node, Entity);
}
}
}
finally
{
TreeList.EndUnboundLoad();
}
}
/// <summary>
/// Tambahkan jumlah counter pengunci pada akun
/// </summary>
/// <param name="Dp"></param>
/// <param name="IdAkun"></param>
public static void TambahJmlPengunci(DataPersistance Dp,
string IdAkun)
{
Dp.ExecuteNonQuery(string.Concat(
"UPDATE Akun SET Terkunci=",
Dp.FormatSqlValue(true, DataType.Boolean),
",JmlPengunci=JmlPengunci+1 WHERE IdAkun=@0"),
new FieldParam("0", IdAkun));
}
public static DataTable GetListRole(DataPersistance dp, string UserName)
{
return(dp.OpenDataTable(string.Concat("SELECT ",
dp.FormatSqlValue(false, DataType.Boolean),
@" AS Pilih,RoleName AS NamaPeran FROM _System_RoleUser
WHERE UserName=@0 AND ProductName=@1"),
new FieldParam("0", UserName),
new FieldParam("1", BaseFramework.ProductName)));
}
internal static bool IsUserAdminExist(DataPersistance dp)
{
string TrueStr = dp.FormatSqlValue(true);
string FalseStr = dp.FormatSqlValue(false);
string SqlQuery = string.Concat(
@"SELECT U.UserName FROM (_System_User AS U
INNER JOIN _System_RoleUser AS RU ON
U.UserName=RU.UserName) INNER JOIN _System_Role R ON
RU.RoleName=R.RoleName AND RU.ProductName=R.ProductName WHERE
R.ProductName=@0 AND U.IsAdmin=", TrueStr,
" AND U.IsActive=", TrueStr,
" AND (U.UseDateLimit=", FalseStr, " OR ",
dp.GetSqlNow(), @" BETWEEN U.StartDate AND
U.EndDate) AND R.IsActive=", TrueStr,
" AND (R.UseDateLimit=", FalseStr, " OR ",
dp.GetSqlNow(), " BETWEEN R.StartDate AND R.EndDate)");
return(dp.Find.IsExists(SqlQuery,
new FieldParam("0", BaseFramework.ProductName)));
}
private string BuildPKWhere(object Entity)
{
string RetVal = string.Empty;
foreach (FieldDef fld in td.KeyFields.Values)
{
RetVal = string.Concat(RetVal, " AND ",
dp.FormatSqlObject(fld.FieldName), "=",
dp.FormatSqlValue(fld.GetValue(Entity), fld.DataType));
}
return(RetVal.Substring(5));
}
internal static DataTable GetListRole(DataPersistance dp,
bool AllRole)
{
dp.ValidateTableDef <Role>();
if (AllRole)
{
return(dp.OpenDataTable(
"SELECT RoleName FROM _System_Role WHERE ProductName=@0 ORDER BY RoleName",
new FieldParam("0", BaseFramework.ProductName)));
}
else
{
return(dp.OpenDataTable(string.Concat(
"SELECT RoleName FROM _System_Role WHERE ProductName=@0 AND IsActive=",
dp.FormatSqlValue(true, DataType.Boolean),
" AND (UseDateLimit=",
dp.FormatSqlValue(false, DataType.Boolean),
" OR ", dp.GetSqlNow(),
" BETWEEN StartDate AND EndDate) ORDER BY RoleName"),
new FieldParam("0", BaseFramework.ProductName)));
}
}
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 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);
}
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>());
}
}