388 lines
17 KiB
C#
388 lines
17 KiB
C#
|
using System;
|
||
|
|
using System.Data;
|
||
|
|
using System.Linq;
|
||
|
|
using System.Text;
|
||
|
|
using Ease.Core.DataAccess;
|
||
|
|
using HRM.BO;
|
||
|
|
|
||
|
|
namespace HRM.DA
|
||
|
|
{
|
||
|
|
#region GLTranDetail
|
||
|
|
|
||
|
|
public class GLTranDetailDA
|
||
|
|
{
|
||
|
|
#region Constructor
|
||
|
|
|
||
|
|
public GLTranDetailDA()
|
||
|
|
{
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Insert function
|
||
|
|
|
||
|
|
public static void Insert(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("INSERT INTO GLTranDetail(" +
|
||
|
|
"GLTranDetailID," + //1
|
||
|
|
" GLTranID," + //2
|
||
|
|
" GLID," + //3
|
||
|
|
" IsContra," + //4
|
||
|
|
" ContraGLID," + //5
|
||
|
|
" ChequeDate," + //6
|
||
|
|
" ChequeNo," + //7
|
||
|
|
" Transide," + //8
|
||
|
|
" Amount," + //9
|
||
|
|
" DetailParamID," + //10
|
||
|
|
//" CostCenterID," +//11
|
||
|
|
" Description," + //12
|
||
|
|
" BankDetail," + //13
|
||
|
|
" InstrumentNo," + //14
|
||
|
|
" OperationalStatus," + //15
|
||
|
|
" Modifiedby," + //16
|
||
|
|
" ModifiedDate)" + //17
|
||
|
|
// 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
||
|
|
" VALUES(%n, %n, %n, %b, %n, %d, %s, %n, %n, %n, %n, %s, %s, %s, %n, %n, %d)",
|
||
|
|
oItem.ID, //1
|
||
|
|
oItem.GLTranID, //2
|
||
|
|
oItem.GLID, //3
|
||
|
|
oItem.IsContra, //4
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //6
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //7
|
||
|
|
oItem.Transide, //8
|
||
|
|
oItem.Amount, //9
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //10
|
||
|
|
//NullHandler.GetNullValue(oItem.CostCenterID.Integer),//11
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //12
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //13
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //14
|
||
|
|
(int)oItem.OperationalStatus, //15
|
||
|
|
NullHandler.GetNullValue(oItem.Modifiedby), //16
|
||
|
|
NullHandler.GetNullValue(oItem.ModifiedDate) //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Update function
|
||
|
|
|
||
|
|
public static void Update(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("UPDATE GLTranDetail SET"
|
||
|
|
+ " GLTranID=%n," //1
|
||
|
|
+ " GLID=%n," //2
|
||
|
|
+ " IsContra=%b," //3
|
||
|
|
+ " ContraGLID=%n," //4
|
||
|
|
+ " ChequeDate=%d," //5
|
||
|
|
+ " ChequeNo=%s," //6
|
||
|
|
+ " Transide=%n," //7
|
||
|
|
+ " Amount=%n," //8
|
||
|
|
+ " DetailParamID=%n," //9
|
||
|
|
+ " CostCenterID=%n," //10
|
||
|
|
+ " Description=%s," //11
|
||
|
|
+ " BankDetail=%s," //12
|
||
|
|
+ " InstrumentNo=%s," //13
|
||
|
|
+ " OperationalStatus=%n," //14
|
||
|
|
+ " Modifiedby=%n," //15
|
||
|
|
+ " ModifiedDate=%d" //16
|
||
|
|
+ " WHERE GLTranDetailID=%n", //17
|
||
|
|
oItem.GLTranID, //1
|
||
|
|
oItem.GLID, //2
|
||
|
|
oItem.IsContra, //3
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //4
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //6
|
||
|
|
oItem.Transide, //7
|
||
|
|
oItem.Amount, //8
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //9
|
||
|
|
// NullHandler.GetNullValue(oItem.CostCenterID.Integer), //10
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //11
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //12
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //13
|
||
|
|
oItem.OperationalStatus, //14
|
||
|
|
oItem.Modifiedby, //15
|
||
|
|
oItem.ModifiedDate, //16
|
||
|
|
oItem.ID //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region ID Generation function
|
||
|
|
|
||
|
|
public static int GetNewID(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.GenerateID("GLTranDetail", "GLTranDetailID");
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Get Function
|
||
|
|
|
||
|
|
public static IDataReader Get(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM GLTranDetail");
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader Get(TransactionContext tc, int nGLTranDetailID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM GLTranDetail WHERE GLTranDetailID=%n", nGLTranDetailID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByGLTranID(TransactionContext tc, int nGLTranID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM GLTranDetail WHERE GLTranID=%n", nGLTranID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader Get(TransactionContext tc, int nGLTranID, int nglID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM GLTranDetail WHERE GLTranID=%n AND GLID=%n", nGLTranID, nglID);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static IDataReader GetByVoucherDate(TransactionContext tc, DateTime firstDate, DateTime lastDate)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT GLTranDetail.* FROM GLTranDetail, GLTran "
|
||
|
|
+ " WHERE GLTranDetail.GLTranID = GLTran.GLTranID "
|
||
|
|
+ " AND GLTran.VoucherDate >= %d AND GLTran.VoucherDate <= %d", firstDate,
|
||
|
|
lastDate);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static IDataReader GetByOpenVoucherDate(TransactionContext tc, DateTime firstDate, DateTime lastDate)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT OpenGLTranDetail.* FROM OpenGLTranDetail, OpenGLTran "
|
||
|
|
+ " WHERE OpenGLTranDetail.OpenGLTranID = OpenGLTran.OpenGLTranID "
|
||
|
|
+ " AND OpenGLTran.VoucherDate >= %d AND OpenGLTran.VoucherDate <= %d", firstDate,
|
||
|
|
lastDate);
|
||
|
|
}
|
||
|
|
|
||
|
|
internal static IDataReader GetByYearEndVoucherDate(TransactionContext tc, DateTime firstDate,
|
||
|
|
DateTime lastDate)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT YearEndGLTranDetail.* FROM YearEndGLTranDetail, YearEndGLTran "
|
||
|
|
+ " WHERE YearEndGLTranDetail.YearEndGLTranID = YearEndGLTran.YearEndGLTranID "
|
||
|
|
+ " AND YearEndGLTran.VoucherDate >= %d AND YearEndGLTran.VoucherDate <= %d",
|
||
|
|
firstDate, lastDate);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Delete function
|
||
|
|
|
||
|
|
public static void Delete(TransactionContext tc, int nGLTranDetailID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM GLTranDetail WHERE GLTranDetailID=%n", nGLTranDetailID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByGLTranID(TransactionContext tc, int nGLTranID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM GLTranDetail WHERE GLTranID=%n", nGLTranID);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
#region Opening Blance Take and year end
|
||
|
|
|
||
|
|
public static void InsertOpen(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("INSERT INTO [OpenGLTranDetail](" +
|
||
|
|
"[OpenGLTranDetailID]," + //1
|
||
|
|
" OpenGLTranID," + //2
|
||
|
|
" GLID," + //3
|
||
|
|
" IsContra," + //4
|
||
|
|
" ContraGLID," + //5
|
||
|
|
" ChequeDate," + //6
|
||
|
|
" ChequeNo," + //7
|
||
|
|
" Transide," + //8
|
||
|
|
" Amount," + //9
|
||
|
|
" DetailParamID," + //10
|
||
|
|
// " CostCenterID," +//11
|
||
|
|
" Description," + //12
|
||
|
|
" BankDetail," + //13
|
||
|
|
" InstrumentNo," + //14
|
||
|
|
" OperationalStatus," + //15
|
||
|
|
" Modifiedby," + //16
|
||
|
|
" ModifiedDate)" + //17
|
||
|
|
// 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
||
|
|
" VALUES(%n, %n, %n, %b, %n, %d, %s, %n, %n, %n, %n, %s, %s, %s, %n, %n, %d)",
|
||
|
|
oItem.ID, //1
|
||
|
|
oItem.GLTranID, //2
|
||
|
|
oItem.GLID, //3
|
||
|
|
oItem.IsContra, //4
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //6
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //7
|
||
|
|
oItem.Transide, //8
|
||
|
|
oItem.Amount, //9
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //10
|
||
|
|
//NullHandler.GetNullValue(oItem.CostCenterID.Integer),//11
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //12
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //13
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //14
|
||
|
|
(int)oItem.OperationalStatus, //15
|
||
|
|
NullHandler.GetNullValue(oItem.Modifiedby), //16
|
||
|
|
NullHandler.GetNullValue(oItem.ModifiedDate) //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void InsertYearEnd(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("INSERT INTO [YearEndGLTranDetail](" +
|
||
|
|
"[YearEndGLTranDetailID]," + //1
|
||
|
|
" YearEndGLTranID," + //2
|
||
|
|
" GLID," + //3
|
||
|
|
" IsContra," + //4
|
||
|
|
" ContraGLID," + //5
|
||
|
|
" ChequeDate," + //6
|
||
|
|
" ChequeNo," + //7
|
||
|
|
" Transide," + //8
|
||
|
|
" Amount," + //9
|
||
|
|
" DetailParamID," + //10
|
||
|
|
// " CostCenterID," +//11
|
||
|
|
" Description," + //12
|
||
|
|
" BankDetail," + //13
|
||
|
|
" InstrumentNo," + //14
|
||
|
|
" OperationalStatus," + //15
|
||
|
|
" Modifiedby," + //16
|
||
|
|
" ModifiedDate)" + //17
|
||
|
|
// 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
||
|
|
" VALUES(%n, %n, %n, %b, %n, %d, %s, %n, %n, %n, %n, %s, %s, %s, %n, %n, %d)",
|
||
|
|
oItem.ID, //1
|
||
|
|
oItem.GLTranID, //2
|
||
|
|
oItem.GLID, //3
|
||
|
|
oItem.IsContra, //4
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //6
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //7
|
||
|
|
oItem.Transide, //8
|
||
|
|
oItem.Amount, //9
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //10
|
||
|
|
//NullHandler.GetNullValue(oItem.CostCenterID.Integer),//11
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //12
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //13
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //14
|
||
|
|
(int)oItem.OperationalStatus, //15
|
||
|
|
NullHandler.GetNullValue(oItem.Modifiedby), //16
|
||
|
|
NullHandler.GetNullValue(oItem.ModifiedDate) //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void UpdateOpen(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("UPDATE [OpenGLTranDetail] SET"
|
||
|
|
+ " OpenGLTranID=%n," //1
|
||
|
|
+ " GLID=%n," //2
|
||
|
|
+ " IsContra=%b," //3
|
||
|
|
+ " ContraGLID=%n," //4
|
||
|
|
+ " ChequeDate=%d," //5
|
||
|
|
+ " ChequeNo=%s," //6
|
||
|
|
+ " Transide=%n," //7
|
||
|
|
+ " Amount=%n," //8
|
||
|
|
+ " DetailParamID=%n," //9
|
||
|
|
// + " CostCenterID=%n," //10
|
||
|
|
+ " Description=%s," //11
|
||
|
|
+ " BankDetail=%s," //12
|
||
|
|
+ " InstrumentNo=%s," //13
|
||
|
|
+ " OperationalStatus=%n," //14
|
||
|
|
+ " Modifiedby=%n," //15
|
||
|
|
+ " ModifiedDate=%d" //16
|
||
|
|
+ " WHERE [OpenGLTranDetailID]=%n", //17
|
||
|
|
oItem.GLTranID, //1
|
||
|
|
oItem.GLID, //2
|
||
|
|
oItem.IsContra, //3
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //4
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //6
|
||
|
|
oItem.Transide, //7
|
||
|
|
oItem.Amount, //8
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //9
|
||
|
|
// NullHandler.GetNullValue(oItem.CostCenterID.Integer), //10
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //11
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //12
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //13
|
||
|
|
oItem.OperationalStatus, //14
|
||
|
|
oItem.Modifiedby, //15
|
||
|
|
oItem.ModifiedDate, //16
|
||
|
|
oItem.ID //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void UpdateYearEnd(TransactionContext tc, GLTranDetail oItem)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("UPDATE [YearEndGLTranDetail] SET"
|
||
|
|
+ " YearEndGLTranID=%n," //1
|
||
|
|
+ " GLID=%n," //2
|
||
|
|
+ " IsContra=%b," //3
|
||
|
|
+ " ContraGLID=%n," //4
|
||
|
|
+ " ChequeDate=%d," //5
|
||
|
|
+ " ChequeNo=%s," //6
|
||
|
|
+ " Transide=%n," //7
|
||
|
|
+ " Amount=%n," //8
|
||
|
|
+ " DetailParamID=%n," //9
|
||
|
|
// + " CostCenterID=%n," //10
|
||
|
|
+ " Description=%s," //11
|
||
|
|
+ " BankDetail=%s," //12
|
||
|
|
+ " InstrumentNo=%s," //13
|
||
|
|
+ " OperationalStatus=%n," //14
|
||
|
|
+ " Modifiedby=%n," //15
|
||
|
|
+ " ModifiedDate=%d" //16
|
||
|
|
+ " WHERE [YearEndGLTranDetailID]=%n", //17
|
||
|
|
oItem.GLTranID, //1
|
||
|
|
oItem.GLID, //2
|
||
|
|
oItem.IsContra, //3
|
||
|
|
NullHandler.GetNullValue(oItem.ContraGLID), //4
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeDate), //5
|
||
|
|
NullHandler.GetNullValue(oItem.ChequeNo), //6
|
||
|
|
oItem.Transide, //7
|
||
|
|
oItem.Amount, //8
|
||
|
|
NullHandler.GetNullValue(oItem.DetailParamID), //9
|
||
|
|
//NullHandler.GetNullValue(oItem.CostCenterID.Integer), //10
|
||
|
|
NullHandler.GetNullValue(oItem.Description), //11
|
||
|
|
NullHandler.GetNullValue(oItem.BankDetail), //12
|
||
|
|
NullHandler.GetNullValue(oItem.InstrumentNo), //13
|
||
|
|
oItem.OperationalStatus, //14
|
||
|
|
oItem.Modifiedby, //15
|
||
|
|
oItem.ModifiedDate, //16
|
||
|
|
oItem.ID //17
|
||
|
|
);
|
||
|
|
}
|
||
|
|
|
||
|
|
#region ID Generation function
|
||
|
|
|
||
|
|
public static int GetNewIDOpen(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.GenerateID("OpenGLTranDetail", "OpenGLTranDetailID");
|
||
|
|
}
|
||
|
|
|
||
|
|
public static int GetNewIDYearEnd(TransactionContext tc)
|
||
|
|
{
|
||
|
|
return tc.GenerateID("YearEndGLTranDetail", "YearEndGLTranDetailID");
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
|
||
|
|
public static void DeleteOpen(TransactionContext tc, int nGLTranDetailID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM OpenGLTranDetail WHERE OpenGLTranDetailID=%n", nGLTranDetailID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static void DeleteByGLTranIDOpen(TransactionContext tc, int nGLTranID)
|
||
|
|
{
|
||
|
|
tc.ExecuteNonQuery("DELETE FROM OpenGLTranDetail WHERE OpenGLTranID=%n", nGLTranID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetOpen(TransactionContext tc, int nGLTranDetailID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM OpenGLTranDetail WHERE OpenGLTranDetailID=%n", nGLTranDetailID);
|
||
|
|
}
|
||
|
|
|
||
|
|
public static IDataReader GetByGLTranIDOpen(TransactionContext tc, int nGLTranID)
|
||
|
|
{
|
||
|
|
return tc.ExecuteReader("SELECT * FROM OpenGLTranDetail WHERE OpenGLTranID=%n", nGLTranID);
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|
||
|
|
|
||
|
|
#endregion
|
||
|
|
}
|