using System; using Payroll.BO; using System.Data; using System.Linq; using System.Data.SqlClient; using Ease.CoreV35.DataAccess; using System.Collections.Generic; using Ease.Core.DataAccess; using HRM.BO; using HRM.DA; namespace Payroll.Service { internal class ClaimRequisitionDA { #region Constructor private ClaimRequisitionDA() { } #endregion #region ClaimRequisition #region Insert function internal static void Insert(TransactionContext tc, ClaimRequisition item) { //tc.ExecuteNonQuery(@"INSERT INTO ClaimRequisition //(ClaimRequisitionID, ClaimDate, ClaimAmount, Reason, BankID, BranchID, //Remarks, ClaimRuleID,WFStatus,PayrollTypeID, //EmployeeID, CostCenterID, GLCode, GLSide, //GLSideCode,PaymentDone,ParentID,OnBehalfEmpID, //SalaryMonth,SalaryPaidMonth,JVStatus,CreatedBy, CreatedDate,ClaimProcessDate,IsOnBehalf,IsAdvance,IsExpense,AdvanceItemId)" + //" VALUES(%n,%d,%n,%s,%n,%n,%s,%n,%n,%n,%n,%n,%s,%s,%s,%b,%n,%n,%d,%d,%b,%n,%d,%d,%n,%n,%n,%n)", //DataReader.GetNullValue(item.ID), item.ClaimDate, item.ClaimAmount, item.Remarks,item.BankID,item.BranchID, //item.Remarks, DataReader.GetNullValue(item.ClaimRuleID), item.WFStatus, item.PayrollTypeID, //DataReader.GetNullValue(item.EmployeeID), DataReader.GetNullValue(item.CostCenterID), item.GLCode, item.GLSide, //item.GLSideCode, item.PaymentDone, DataReader.GetNullValue(item.ParentID), DataReader.GetNullValue(item.OnBehalfEmplID), //item.SalaryMonth, item.SalaryPaidMonth, item.JVStatus, DataReader.GetNullValue(item.CreatedBy), DataReader.GetNullValue(item.CreatedDate),DataReader.GetNullValue(item.ClaimProcessDate), //item.IsOnBehalf,item.IsAdvance,item.IsExpense,item.AdvanceItemId //); string ssql = SQLParser.MakeSQL(@"INSERT INTO ClaimRequisition (ClaimRequisitionID, ClaimDate, ClaimAmount, Reason, BankID, BranchID, Remarks, ClaimRuleID, WFStatus, PayrollTypeID, EmployeeID, CostCenterID, GLCode, GLSide, GLSideCode, PaymentDone, ParentID, OnBehalfEmpID, SalaryMonth, SalaryPaidMonth, JVStatus, CreatedBy, CreatedDate, ClaimProcessDate, IsOnBehalf, IsAdvance, IsExpense, AdvanceItemId,RequisitionNo,ClaimBasicID)" + " VALUES(%n,%d,%n,%s,%n,%n,%s,%n,%n,%n,%n,%n,%s,%s,%s,%b,%n,%n,%d,%d,%b,%n,%d,%d,%n,%n,%n,%n,%s,%n)", DataReader.GetNullValue(item.ID), item.ClaimDate, item.ClaimAmount, item.Remarks, item.BankID, item.BranchID, item.Remarks, DataReader.GetNullValue(item.ClaimRuleID), item.RequsitionStatus, item.PayrollTypeID, DataReader.GetNullValue(item.EmployeeID), DataReader.GetNullValue(item.CostCenterID), item.GLCode, item.GLSide, item.GLSideCode, item.PaymentDone, DataReader.GetNullValue(item.ParentID), DataReader.GetNullValue(item.OnBehalfEmplID), item.SalaryMonth, item.SalaryPaidMonth, item.JVStatus, DataReader.GetNullValue(item.CreatedBy), DataReader.GetNullValue(item.CreatedDate), item.ClaimProcessDate, item.IsOnBehalf, item.IsAdvance, item.IsExpense, item.AdvanceItemId, item.RequisitionNo, item.ClaimBasicID ); tc.ExecuteNonQuery(ssql); } #endregion #region Update function internal static void Update(TransactionContext tc, ClaimRequisition item) { string ssql = SQLParser.MakeSQL(@"UPDATE ClaimRequisition SET ClaimDate=%d, ClaimAmount=%n, Reason=%s, BankID = %n, BranchID = %n, Remarks=%s, ClaimRuleID=%n,WFStatus=%n,PayrollTypeID = %n, EmployeeID=%n, CostCenterID=%n, GLCode=%s, GLSide=%s, GLSideCode=%s,PaymentDone=%b,ParentID=%n,OnBehalfEmpID=%n, SalaryMonth=%d,SalaryPaidMonth=%d, ModifiedBy=%n, ModifiedDate=%d,ClaimProcessDate = %d,IsOnBehalf=%n,IsAdvance=%n,IsExpense=%n,AdvanceItemId=%n" + " WHERE ClaimRequisitionID=%n", item.ClaimDate, item.ClaimAmount, item.Reason, item.BankID, item.BranchID, item.Remarks, DataReader.GetNullValue(item.ClaimRuleID), item.RequsitionStatus, item.PayrollTypeID, DataReader.GetNullValue(item.EmployeeID), DataReader.GetNullValue(item.CostCenterID), item.GLCode, item.GLSide, item.GLSideCode, item.PaymentDone, DataReader.GetNullValue(item.ParentID), DataReader.GetNullValue(item.OnBehalfEmplID), item.SalaryMonth, item.SalaryPaidMonth == DateTime.MinValue ? DateTime.Now : item.SalaryPaidMonth, DataReader.GetNullValue(item.ModifiedBy), DataReader.GetNullValue(item.ModifiedDate), item.ClaimProcessDate == DateTime.MinValue ? DateTime.Now : item.ClaimProcessDate, item.IsOnBehalf, item.IsAdvance, item.IsExpense, item.AdvanceItemId, DataReader.GetNullValue(item.ID)); tc.ExecuteNonQuery(ssql); } internal static int UpdateFromLineManager(TransactionContext tc, ClaimRequisition item) { tc.ExecuteNonQuery(@"UPDATE ClaimRequisition SET ClaimDate=%d, ClaimAmount=%n, Reason=%s, Remarks=%s, ModifiedBy=%n, ModifiedDate=%d WHERE ClaimRequisitionID=%n", item.ClaimDate, item.ClaimAmount, item.Reason, item.Remarks, DataReader.GetNullValue(item.ModifiedBy), DataReader.GetNullValue(item.ModifiedDate), DataReader.GetNullValue(item.ID)); return item.ID; } #endregion #region Get Function internal static IDataReader Get(TransactionContext tc) { return tc.ExecuteReader("SELECT * FROM ClaimRequisition"); } internal static IDataReader Get(TransactionContext tc, int claimBasicId, int employeeId) { string sql = SQLParser.MakeSQL("SELECT * FROM ClaimRequisition WHERE ClaimBasicID=%n AND employeeId=%n", claimBasicId, employeeId); return tc.ExecuteReader(sql); } internal static IDataReader GetClaimRequisitionItems(TransactionContext tc, string id) { string sql = string.Empty; sql = !string.IsNullOrEmpty(id) ? SQLParser.MakeSQL("SELECT * FROM ClaimRequisitionItem WHERE ClaimRequisitionID IN (%q)", id) : SQLParser.MakeSQL("SELECT * FROM ClaimRequisitionItem"); return tc.ExecuteReader(sql); } internal static void UpdateClaimRequisitionStatus(TransactionContext tc, int pkid, EnumClaimRequsitionStatus status) { string ssql = ""; ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET WFStatus=%n WHERE ClaimRequisitionID=%n", (int)status, pkid); tc.ExecuteNonQuery(ssql); } internal static IDataReader GetClaimRequistionByClIdAndMonth(TransactionContext tc, int claimBasicId, DateTime salaryMonth, int wf) { if (claimBasicId == 0) { return tc.ExecuteReader("SELECT * FROM ClaimRequisition"); } else { return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimBasicID=%n AND SalaryMonth BETWEEN %d AND %d AND WFStatus=%n", claimBasicId, GlobalFunctions.FirstDateOfMonth(salaryMonth), GlobalFunctions.LastDateOfMonth(salaryMonth), wf); } } internal static IDataReader Get(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimRequisitionID=%n", nID); } internal static IDataReader GetByClaimRequisitionID(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimRequisitionID=%n", nID); } internal static IDataReader GetByIds(TransactionContext tc, string ids) { return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimRequisitionID in (%q)", ids); } internal static DataSet GetClaimReqsByIds(TransactionContext tc, string ids) { string sSQL = SQLParser.MakeSQL(@"SELECT cr.*, e.NAME,e.EMAILADDRESS, e.EMPLOYEENO, d.NAME AS 'Designation' FROM ClaimRequisition cr INNER JOIN EMPLOYEE e ON cr.EmployeeID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE cr.ClaimRequisitionID IN (%q) AND WFStatus NOT IN (%n) ORDER BY cr.ClaimDate,cr.ClaimRequisitionID DESC", string.IsNullOrEmpty(ids) ? "0" : ids, (int)enumwfStatus.Decline); return tc.ExecuteDataSet(sSQL); } internal static IDataReader GetclaimRequisition(TransactionContext tc, int id) { return tc.ExecuteReader(@"SELECT cr.*, e.NAME, e.EMPLOYEENO, d.NAME AS 'Designation' FROM ClaimRequisition cr INNER JOIN EMPLOYEE e ON cr.EmployeeID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE cr.ClaimRequisitionID = %n", id); } internal static DataSet GetFilteredClaimReqsByIds(TransactionContext tc, string ids, DateTime fromDate, DateTime toDate, string employeeNo, string employeeName) { string sSQL = SQLParser.MakeSQL(@"SELECT cr.*, e.NAME, e.EMPLOYEENO,e.EMAILAddress, d.NAME AS 'Designation' FROM ClaimRequisition cr INNER JOIN EMPLOYEE e ON cr.EmployeeID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE cr.ClaimRequisitionID IN (%q) %q", ids, filteringClaimSearchParameter(fromDate, toDate, employeeNo, employeeName, null)); return tc.ExecuteDataSet(sSQL); } internal static DataTable GetPendingList(TransactionContext tc, int approverEmpid, string ids, DateTime fromDate, DateTime toDate) { string subQuery = string.Empty; if (fromDate != DateTime.MinValue) { subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("CAST(cr.ClaimDate as DATE) >= CAST(%d as Date)", fromDate); } if (toDate != DateTime.MinValue) { subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("CAST(cr.ClaimDate as DATE) <= CAST(%d as Date)", toDate); } if (!String.IsNullOrEmpty(ids)) { subQuery = SQLParser.TagSQL(subQuery) + SQLParser.MakeSQL("cr.EmployeeID in (%q)", ids); } string sSQL = SQLParser.MakeSQL(@"select b.WFMovementTranID TranID, a.* from ( SELECT cr.*, e.NAME, e.EMPLOYEENO,e.EMAILAddress, d.NAME AS 'Designation' FROM EMPLOYEE e inner JOIN ClaimRequisition cr ON cr.EmployeeID = e.EMPLOYEEID left JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID %q ) a inner join (SELECT wf.EmployeeID, w.ObjectID, wf.WFMovementTranID FROM WFMovementNext wf ,WFMovementTran w where w.WFMovementTranID = wf.WFMovementTranID and w.wftypeid in(4) and wf.Status =1 and wf.EmployeeID =%n ) b on a.ClaimRequisitionID = b.ObjectID", subQuery, approverEmpid); return tc.ExecuteDataTable(sSQL); } internal static IDataReader GetAll(TransactionContext tc, DateTime fromDate, DateTime toDate, int employeeId, EnumClaimRequsitionStatus? status) { string sSQL = SQLParser.MakeSQL(@"SELECT cr.*, e.NAME, e.EMPLOYEENO,e.EMAILAddress, d.NAME AS 'Designation' FROM ClaimRequisition cr INNER JOIN EMPLOYEE e ON cr.EmployeeID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE cr.EmployeeID=%n %q order by cr.ClaimDate desc", employeeId, filteringClaimSearchParameter(fromDate, toDate, null, null, status)); return tc.ExecuteReader(sSQL); } private static string filteringClaimSearchParameter(DateTime fromDate, DateTime toDate, string employeeNo, string employeeName, EnumClaimRequsitionStatus? status) { string returnQuery = string.Empty; if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) { returnQuery += " AND cr.ClaimDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"; } if (!String.IsNullOrEmpty(employeeNo)) { returnQuery += " AND e.EMPLOYEENO = '" + employeeNo + "'"; } if (!String.IsNullOrEmpty(employeeName)) { returnQuery += " AND e.NAME = '" + employeeName + "'"; } if (!String.IsNullOrEmpty(employeeNo)) { returnQuery += " AND e.EMPLOYEENO = '" + employeeNo + "'"; } if (status != null && status != EnumClaimRequsitionStatus.None) { returnQuery += " AND cr.wfstatus = " + (int)status + ""; } return returnQuery; } internal static DataSet ClaimForAdmin(TransactionContext tc, DateTime frmDate, DateTime toDate, int claimBasicId, string status, int employeeId) { DataSet oCostCenterWiseSalaryDetail = new DataSet(); string sSQL = ""; try { if (claimBasicId == 0) { if (employeeId == 0) { sSQL = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.NAME, clr.ClaimRequisitionID,c.ItemCode,clr.ClaimDate,clr.ClaimAmount,clr.WFStatus FROM ClaimRequisition clr JOIN ClaimBasic c ON clr.ClaimBasicID=c.ClaimBasicID JOIN EMPLOYEE e ON e.EMPLOYEEID=clr.EmployeeID WHERE clr.ClaimDate BETWEEN %d AND %d AND clr.WFStatus in (%q)", frmDate, toDate, status); } else { sSQL = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.NAME, clr.ClaimRequisitionID,c.ItemCode,clr.ClaimDate,clr.ClaimAmount,clr.WFStatus FROM ClaimRequisition clr JOIN ClaimBasic c ON clr.ClaimBasicID=c.ClaimBasicID JOIN EMPLOYEE e ON e.EMPLOYEEID=clr.EmployeeID WHERE clr.ClaimDate BETWEEN %d AND %d AND clr.WFStatus in (%q) and e.EMPLOYEEID=%n", frmDate, toDate, status, employeeId); } } else { if (employeeId == 0) { sSQL = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.NAME, clr.ClaimRequisitionID,c.ItemCode,clr.ClaimDate,clr.ClaimAmount,clr.WFStatus FROM ClaimRequisition clr JOIN ClaimBasic c ON clr.ClaimBasicID=c.ClaimBasicID JOIN EMPLOYEE e ON e.EMPLOYEEID=clr.EmployeeID WHERE clr.ClaimDate BETWEEN %d AND %d AND clr.WFStatus in (%q) AND c.ClaimBasicID = %n", frmDate, toDate, status, claimBasicId); } else { sSQL = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.NAME, clr.ClaimRequisitionID,c.ItemCode,clr.ClaimDate,clr.ClaimAmount,clr.WFStatus FROM ClaimRequisition clr JOIN ClaimBasic c ON clr.ClaimBasicID=c.ClaimBasicID JOIN EMPLOYEE e ON e.EMPLOYEEID=clr.EmployeeID WHERE clr.ClaimDate BETWEEN %d AND %d AND clr.WFStatus in (%q) and e.EMPLOYEEID=%n AND c.ClaimBasicID = %n", frmDate, toDate, status, employeeId, claimBasicId); } } oCostCenterWiseSalaryDetail = tc.ExecuteDataSet(sSQL); } catch (Exception ex) { throw new Exception(ex.Message); } return oCostCenterWiseSalaryDetail; } internal static DataSet GetClaimRequisitionItemsInGrid(TransactionContext tc, string ids) { string strSQLQuery = SQLParser.MakeSQL(@"SELECT cri.ClaimRequisitionItemID,cri.ClaimRequisitionID, cri.ClaimBasicItemID, cri.ClaimItemID, cbi.Name, cri.FileName, cri.Amount, cri.TranDate ,cb.ItemName ClaimMainItem,cri.WorkArea FROM ClaimRequisitionItem cri left JOIN ClaimBasicItem cbi ON cri.ClaimBasicItemID = cbi.ClaimBasicItemID left join ClaimBasic cb on cri.ClaimItemID = cb.ClaimBasicID WHERE ClaimRequisitionID IN (%q) order by cri.TranDate desc", ids); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetSignatures(TransactionContext tc, int objectId, int wftypeId) { string strSQLQuery = SQLParser.MakeSQL(@"SELECT e.NAME+','+d.NAME info, w.status FROM WFMOVEMENTTRAN w INNER JOIN EMPLOYEE e ON w.FROMEMPLOYEEID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE w.OBJECTID = %n AND w.WFSETUPID IN (SELECT WFSETUPID FROM WFSETUP WHERE WFTYPEID = %n)", objectId, wftypeId); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetSignaturesByUniqueNumber(TransactionContext tc, int uniqueNumber, int wftypeId) { string strSQLQuery = SQLParser.MakeSQL(@"SELECT e.NAME+','+d.NAME info, w.status FROM WFMOVEMENTTRAN w INNER JOIN EMPLOYEE e ON w.FROMEMPLOYEEID = e.EMPLOYEEID INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID WHERE w.uniquenumber = %n AND w.WFSETUPID IN (SELECT WFSETUPID FROM WFSETUP WHERE WFTYPEID = %n)", uniqueNumber, wftypeId); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetClaimRequistionId(TransactionContext tc, string claimRequistions) { DataSet oclaimRequistions = new DataSet(); try { string sSQL = SQLParser.MakeSQL(@"SELECT * FROM ClaimRequisition WHERE ClaimRequisitionId IN (%q) ", claimRequistions); oclaimRequistions = tc.ExecuteDataSet(sSQL); } catch (Exception ex) { throw new Exception(ex.Message); } return oclaimRequistions; } internal static DataSet GetClaimReqisitionRelationByClaimRquisitonId(TransactionContext tc, string claimRequistions) { DataSet oclaimRequistions = new DataSet(); try { string sSQL = SQLParser.MakeSQL(@"SELECT * FROM ClaimRequisitionRelation WHERE ClaimRequisitionID IN (%q) ", claimRequistions); oclaimRequistions = tc.ExecuteDataSet(sSQL); } catch (Exception ex) { throw new Exception(ex.Message); } return oclaimRequistions; } internal static DataSet GetClaimRequisitionHistory(TransactionContext tc, int id) { string sql = SQLParser.MakeSQL(@"SELECT cr.ClaimRequisitionID, cr.ClaimAmount Amount, cr.ClaimDate Date, cr.WFStatus Status ,cr.ClaimProcessDate SalaryMonth FROM ClaimRequisition cr WHERE cr.EmployeeID = %n AND cr.WFSTATUS <> 0 ORDER BY cr.ClaimDate DESC", id); return tc.ExecuteDataSet(sql); } internal static DataSet GetClaimAttachmentsInGrid(TransactionContext tc, string ids) { // string strSQLQuery = SQLParser.MakeSQL(@"SELECT cra.ClaimRequisitionAttachmentID, cra.ClaimID,cri.TranDate, cri.FileName, cra.Remarks FROM ClaimRequisitionAttachment cra // INNER JOIN ClaimRequisitionItem cri ON cra.ClaimID = cri.ClaimRequisitionID // WHERE cra.ClaimID IN (%q)", ids); string strSQLQuery = SQLParser.MakeSQL(@"SELECT cra.ClaimRequisitionAttachmentID, cra.ClaimID,cra.Date TranDate, cra.FileName, cra.Remarks FROM ClaimRequisitionAttachment cra WHERE cra.ClaimID IN (%q)", ids); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetEmpClaims(TransactionContext tc, DateTime dateTime, string sEmpID) { DataSet oBankAdvices = new DataSet(); try { string sTableName; sEmpID = IDHelper.GetIDs(tc, sEmpID, out sTableName); bool bfixedAmount = false; //ConfigurationManager.GetBoolValue("costcenter", "manadatoryinsalary", EnumConfigurationType.Logic); // need to be changed if (!bfixedAmount) { string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo, cr.SalaryMonth PaymentDate,BR.Name Branch, BR.Code BranchCode,B.Name Bank,sum(cr.ClaimAmount)Amount FROM EMPLOYEE E LEFT OUTER JOIN ClaimRequisition cr ON E.EMPLOYEEID = cr.EmployeeID LEFT OUTER JOIN BRANCHES BR ON E.BRANCHID = br.BRANCHID LEFT OUTER JOIN Banks B ON br.BANKID = b.BANKID WHERE cr.SalaryMonth = %d AND cr.EmployeeID IN (%q) AND cr.WFStatus = %n GROUP BY E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo, cr.SalaryMonth,BR.Name,BR.Code,B.Name order by E.EmployeeNo", dateTime, sEmpID, (int)enumwfStatus.End); oBankAdvices = tc.ExecuteDataSet(sSQL); } else { string sSQL = SQLParser.MakeSQL(@"SELECT E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo, cr.SalaryMonth PaymentDate,BR.Name Branch, BR.Code BranchCode,B.Name Bank,sum(cr.ClaimAmount)Amount FROM EMPLOYEE E LEFT OUTER JOIN ClaimRequisition cr ON E.EMPLOYEEID = cr.EmployeeID LEFT OUTER JOIN BRANCHES BR ON E.BRANCHID = br.BRANCHID LEFT OUTER JOIN Banks B ON br.BANKID = b.BANKID WHERE cr.SalaryMonth = %d AND cr.EmployeeID IN (%q) AND cr.WFStatus = %n GROUP BY E.EMPLOYEENO,E.NAME,E.EMAILADDRESS,E.AccountNo, cr.SalaryMonth,BR.Name,BR.Code,B.Name order by E.EmployeeNo", dateTime, sEmpID, (int)enumwfStatus.End); oBankAdvices = tc.ExecuteDataSet(sSQL); } if (sTableName != "") tc.ExecuteNonQuery("Drop TAble %q", sTableName); } catch (Exception ex) { throw new Exception(ex.Message); } return oBankAdvices; } internal static DataSet GetClaimBalanceInfo(TransactionContext tc, DateTime fromdate, DateTime toDate, string sSQL, int status) { string sStatus = String.Empty; if (status == 1) { sStatus = "AND cr.WFStatus = " + status.ToString(); } else { sStatus = "AND cr.WFStatus NOT IN (6, 0) "; } string sSQLQuery = SQLParser.MakeSQL(@"SELECT emp.EMPLOYEENO,emp.NAME,cr.ClaimDate,cr.ClaimAmount,cr.WFStatus,cr.ClaimRequisitionID FROM EMPLOYEE emp LEFT JOIN ClaimRequisition cr ON emp.EMPLOYEEID = cr.EmployeeID WHERE cr.ClaimDate BETWEEN %d AND %d %q %q ", fromdate, toDate, sSQL, sStatus); return tc.ExecuteDataSet(sSQLQuery); } internal static IDataReader GetClaimReqsByEmployeeIds(TransactionContext tc, string ids, DateTime fromDate, DateTime toDate, int currentUserID, EnumClaimWFStatus? appstatus, EnumClaimPaymentType? paymentType) { string sqlClause = ""; if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.ClaimDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"); if (!String.IsNullOrEmpty(ids)) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.EmployeeID in (%q)", ids); if (appstatus != null) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.WFStatus =%n", appstatus); if (paymentType != null && paymentType != EnumClaimPaymentType.UnPaid) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.PaymentType =%n", paymentType); if (paymentType == EnumClaimPaymentType.UnPaid) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.PaymentType <> %n AND cr.PaymentType <> %n", EnumClaimPaymentType.Paid, EnumClaimPaymentType.With_Salary); //string sSQL = SQLParser.MakeSQL(@"SELECT cr.*, e.NAME, e.EMPLOYEENO,e.EMAILAddress, d.NAME AS 'Designation' FROM ClaimRequisition cr // INNER JOIN EMPLOYEE e ON cr.EmployeeID = e.EMPLOYEEID // INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID %q", sqlClause); string sSQL = SQLParser.MakeSQL(@";WITH cte AS (SELECT locationid FROM claimdisbursementpermission AS cdp WHERE userid = %n), recursive_cte AS (SELECT child.locationid, child.code,child.parentid,child.tire FROM location as child WHERE locationid IN(SELECT * FROM cte) UNION ALL SELECT child.locationid,child.code,child.parentid,child.tire FROM recursive_cte parent INNER JOIN location child ON child.parentid = parent.locationid) SELECT DISTINCT cr.*,e.NAME, e.EMPLOYEENO,e.EMAILAddress, d.NAME AS 'Designation' FROM recursive_cte cte INNER JOIN employee AS e ON e.locationid = cte.locationid INNER JOIN claimrequisition AS cr ON cr.employeeid = e.employeeid AND cr.WFStatus <> %n INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID %q", currentUserID, (int)EnumClaimRequsitionStatus.Darft, sqlClause); return tc.ExecuteReader(sSQL); } #endregion #region Delete function internal static void Delete(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisition WHERE ClaimRuleID=%n", nID); } internal static void DeleteByID(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisition WHERE ClaimRequisitionID=%n", nID); } #endregion #endregion #region Childs #region ClaimRequisitionItem #region Insert function internal static void InsertClaimRequisitionItem(TransactionContext tc, ClaimRequisitionItem item) { tc.ExecuteNonQuery(@"INSERT INTO ClaimRequisitionItem (ClaimRequisitionItemID, ClaimRequisitionID, ClaimBasicItemID, Amount, TranDate, Remarks, FileName, FileLink, GLCode, GLSide, GLSideCode,ClaimBasicID,WorkArea,Field1,Field2,Field3,Field4,Field5,Field6,IsAdvance,IsExpense)" + " VALUES(%n,%n,%n,%n,%d,%s,%s,%s,%s,%s,%s,%n,%s,%s,%s,%s,%s,%s,%s,%b,%b)", DataReader.GetNullValue(item.ID), item.ClaimRequisitionID, item.ClaimBasicItemID, item.Amount, item.TranDate, item.Remarks, item.FileName, item.FileLink, item.GLCode, item.GLSide, item.GLSideCode, item.ClaimBasicID, item.WorkArea, item.Field1, item.Field2, item.Field3, item.Field4, item.Field5, item.Field6, item.IsAdvance, item.IsExpense); } #endregion #region Update function internal static void UpdateClaimRequisitionItem(TransactionContext tc, ClaimRequisitionItem item) { tc.ExecuteNonQuery(@"UPDATE ClaimRequisitionItem SET ClaimRequisitionItemID=%n, ClaimRequisitionID=%n, ClaimBasicItemID=%n, Amount=%n, TranDate=%d, Remarks=%s, FileName=%s, FileLink=%s, GLCode=%s, GLSide=%s, GLSideCode=%s,ClaimBasicID = %n,WorkArea = %s,Field1=%s,Field2=%s,Field3=%s,Field4=%s,Field5=%s,Field6=%s,IsAdvance=%b,IsExpense=%b" + " WHERE ClaimRequisitionItemID=%n", item.ClaimRequisitionID, item.ClaimBasicItemID, item.Amount, item.TranDate, item.Remarks, item.FileName, item.FileLink, item.GLCode, item.GLSide, item.GLSideCode, item.ClaimBasicID, item.WorkArea, item.Field1, item.Field2, item.Field3, item.Field4, item.Field5, item.Field6, item.IsAdvance, item.IsExpense, DataReader.GetNullValue(item.ID)); } internal static void UpdateClaimRwquisitionItemStatus(TransactionContext tc, string _claimRequisitionIDs, DateTime ClaimPaymentJVDate) { //tc.ExecuteNonQuery("UPDATE ClaimRequisitionItem SET JVSTATUS= 1,ClaimProcessDate = %d WHERE ClaimRequisitionItemID IN (%q)",ClaimPaymentJVDate, _claimRequisitionIDs); tc.ExecuteNonQuery("UPDATE ClaimRequisition SET JVSTATUS = 1,ClaimProcessDate = %d WHERE ClaimRequisitionID IN (%q)", ClaimPaymentJVDate, _claimRequisitionIDs); } #endregion #region Get Function internal static IDataReader GetClaimRequisitionItem(TransactionContext tc) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionItem"); } internal static IDataReader GetClaimRequisitionItem(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionItem WHERE ClaimRequisitionItemID=%n", nID); } internal static IDataReader GetClaimRequisitionItemByClaimRequisitionID(TransactionContext tc, int nID) { return tc.ExecuteReader(@"SELECT c.*, ClaimBasic.ItemName ClaimBasicName, ClaimBasicItem.Name ClaimBasicItemName FROM ClaimRequisitionItem c inner join ClaimBasic on ClaimBasic.ClaimBasicID = c.ClaimBasicID inner join ClaimBasicItem on ClaimBasicItem.ClaimBasicItemID = c.ClaimBasicItemID where c.ClaimRequisitionID=%n", nID); } internal static IDataReader GetClaimDisbursementReport(TransactionContext tc, string ids, DateTime fromDate, DateTime toDate, int currentUserID, EnumClaimWFStatus? appstatus, EnumClaimPaymentType? paymentType) { #region oldcode //return tc.ExecuteReader(@"SELECT DISTINCT cr.RequisitionNo,cr.ClaimRequisitionID,cr.ClaimDate,cri.ClaimRequisitionItemID,cb.ItemName BasicName, // cbi.Name Category,cb.ClaimBasicID,cr.IsPayment,cr.PaymentType,cr.PaymentDate, // e.NAME, e.EMPLOYEENO,e.EMPLOYEEID,cri.Field1,cri.Field2,cri.Field3,cri.Field4,cri.Field5,cri.Field6, // cb.IsField1, cb.IsField2, cb.IsField3, cb.IsField4, cb.IsField5, cb.IsField6,cb.Field1Type, cb.Field2Type,cb.Field3Type,cb.Field4Type,cb.Field5Type,cb.Field6Type, // cb.Field1 headf1,cb.Field2 headf2,cb.Field3 headf3,cb.Field4 headf4,cb.Field5 headf5,cb.Field6 headf6, // loc.DESCRIPTION AS 'Location',dept.TIRE,dept.DEPARTMENTID, // CASE WHEN dept.TIRE=1 THEN (SELECT dp.DESCRIPTION // FROM department dp WHERE dp.DEPARTMENTID=e.DEPARTMENTID) // WHEN dept.TIRE=2 THEN (SELECT dept1.DESCRIPTION // FROM department dp // LEFT JOIN department dept1 ON dept1.DEPARTMENTID = dp.PARENTID AND dept1.tire=1 // WHERE dp.DEPARTMENTID=e.DEPARTMENTID // ) // WHEN dept.TIRE=3 THEN (SELECT dept3.DESCRIPTION // FROM department dp // LEFT JOIN department dept2 ON dept2.DEPARTMENTID = dp.PARENTID AND dept2.tire=2 --AND dp.DEPARTMENTID=e.DEPARTMENTID // LEFT JOIN department dept3 ON dept3.DEPARTMENTID = dept2.PARENTID AND dept3.tire=1 // WHERE dp.DEPARTMENTID=e.DEPARTMENTID // ) // END AS Department, // CASE WHEN dept.TIRE=3 THEN (SELECT dp.DESCRIPTION // FROM department dp WHERE dp.DEPARTMENTID=e.DEPARTMENTID) // END AS Unit, // HOD=dbo.GetDeptHeadID(e.EMPLOYEEID), // cri.Amount,cri.Remarks,cri.ClaimRequisitionItemID // FROM claimrequisition AS cr // INNER JOIN ClaimrequisitionItem cri ON cr.ClaimRequisitionID=cri.ClaimRequisitionID // INNER JOIN ClaimBasicItem cbi ON cri.ClaimBasicItemID=cbi.ClaimBasicItemID // INNER JOIN ClaimBasic cb ON cri.ClaimBasicID=cb.ClaimBasicID AND cbi.ClaimBasicID=cb.ClaimBasicID // INNER JOIN employee AS e ON e.EMPLOYEEID=cr.EmployeeID // INNER JOIN DESIGNATION d ON e.DESIGNATIONID = d.DESIGNATIONID // INNER JOIN Location AS loc ON e.locationid = loc.locationid // INNER JOIN department dept ON e.DEPARTMENTID = dept.DEPARTMENTID"); #endregion string sqlClause = ""; if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.ClaimDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"); if (!String.IsNullOrEmpty(ids)) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.EmployeeID in (%q)", ids); if (appstatus != null) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.WFStatus =%n", appstatus); if (paymentType != null && paymentType != EnumClaimPaymentType.UnPaid) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.PaymentType =%n", paymentType); string sSQL = SQLParser.MakeSQL(@";WITH cte AS (SELECT locationid FROM claimdisbursementpermission AS cdp WHERE userid =%n), recursive_cte AS (SELECT child.locationid, child.code,child.parentid,child.tire FROM location AS child WHERE locationid IN(SELECT * FROM cte) UNION ALL SELECT child.locationid,child.code,child.parentid,child.tire FROM recursive_cte parent INNER JOIN location child ON child.parentid = parent.locationid) SELECT DISTINCT cr.requisitionno,cr.claimrequisitionid,cr.claimdate,cri.claimrequisitionitemid,cb.itemname BasicName, cbi.NAME Category,cb.claimbasicid,cr.ispayment,cr.paymenttype,cr.paymentdate, e.NAME, e.employeeno,e.employeeid,cri.field1,cri.field2,cri.field3,cri.field4,cri.field5,cri.field6, cb.isfield1, cb.isfield2, cb.isfield3, cb.isfield4, cb.isfield5, cb.isfield6,cb.field1type, cb.field2type,cb.field3type,cb.field4type,cb.field5type,cb.field6type, cb.Field1 headf1,cb.Field2 headf2,cb.Field3 headf3,cb.Field4 headf4,cb.Field5 headf5,cb.Field6 headf6, loc.description AS 'Location',dept.tire,dept.departmentid,d.NAME AS 'Designation',cr.PaymentRemarks, CASE WHEN dept.tire=1 THEN (SELECT dp.description FROM department dp WHERE dp.departmentid=e.departmentid) WHEN dept.tire=2 THEN (SELECT dept1.description FROM department dp LEFT JOIN department dept1 ON dept1.departmentid = dp.parentid AND dept1.tire=1 WHERE dp.departmentid=e.departmentid ) WHEN dept.tire=3 THEN (SELECT dept3.description FROM department dp LEFT JOIN department dept2 ON dept2.departmentid = dp.parentid AND dept2.tire=2 --AND dp.DEPARTMENTID=e.DEPARTMENTID LEFT JOIN department dept3 ON dept3.departmentid = dept2.parentid AND dept3.tire=1 WHERE dp.departmentid=e.departmentid ) END AS Department, CASE WHEN dept.tire=3 THEN (SELECT dp.description FROM department dp WHERE dp.departmentid=e.departmentid) END AS Unit, HOD=dbo.Getdeptheadid(e.employeeid), cri.amount,cri.remarks,cri.claimrequisitionitemid,u1.LOGINID FROM claimrequisition AS cr INNER JOIN claimrequisitionitem cri ON cr.claimrequisitionid=cri.claimrequisitionid INNER JOIN claimbasicitem cbi ON cri.claimbasicitemid=cbi.claimbasicitemid INNER JOIN claimbasic cb ON cri.claimbasicid=cb.claimbasicid AND cbi.claimbasicid=cb.claimbasicid INNER JOIN employee AS e ON e.employeeid=cr.employeeid AND cr.WFStatus <> %n INNER JOIN recursive_cte cte ON e.locationid = cte.locationid INNER JOIN designation d ON e.designationid = d.designationid INNER JOIN location AS loc ON e.locationid = loc.locationid INNER JOIN department dept ON e.departmentid = dept.departmentid LEFT JOIN USERS AS u1 ON u1.USERID=cr.paidby %q", currentUserID, (int)EnumClaimRequsitionStatus.Darft, sqlClause); return tc.ExecuteReader(sSQL); } internal static IDataReader GetClaimReportEcport(TransactionContext tc, DateTime fromDate, DateTime toDate, EnumClaimRequsitionStatus? status, int currentUserID) { string sqlClause = ""; if (fromDate != DateTime.MinValue && toDate != DateTime.MinValue) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.ClaimDate BETWEEN '" + fromDate + "' AND '" + toDate + "'"); if (status != EnumClaimRequsitionStatus.None) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.WFStatus = %n", status); if (currentUserID > 0) sqlClause = SQLParser.TagSQL(sqlClause) + SQLParser.MakeSQL("cr.EmployeeID= %n", currentUserID); string sSQL = SQLParser.MakeSQL(@"SELECT DISTINCT cr.requisitionno,cr.claimrequisitionid,cr.claimdate,cri.claimrequisitionitemid,cb.itemname BasicName, cbi.NAME Category,cb.claimbasicid,cr.ispayment,cr.paymenttype,cr.paymentdate, e.NAME, e.employeeno,e.employeeid,cri.field1,cri.field2,cri.field3,cri.field4,cri.field5,cri.field6, cb.isfield1, cb.isfield2, cb.isfield3, cb.isfield4, cb.isfield5, cb.isfield6,cb.field1type, cb.field2type,cb.field3type,cb.field4type,cb.field5type,cb.field6type, cb.Field1 headf1,cb.Field2 headf2,cb.Field3 headf3,cb.Field4 headf4,cb.Field5 headf5,cb.Field6 headf6, loc.description AS 'Location',cr.PaymentRemarks, cri.amount,cri.remarks,cri.claimrequisitionitemid FROM claimrequisition AS cr INNER JOIN claimrequisitionitem cri ON cr.claimrequisitionid=cri.claimrequisitionid INNER JOIN claimbasicitem cbi ON cri.claimbasicitemid=cbi.claimbasicitemid INNER JOIN claimbasic cb ON cri.claimbasicid=cb.claimbasicid AND cbi.claimbasicid=cb.claimbasicid INNER JOIN employee AS e ON e.employeeid=cr.employeeid AND cr.WFStatus <> %n INNER JOIN location AS loc ON e.locationid = loc.locationid %q", (int)EnumClaimRequsitionStatus.Darft, sqlClause); return tc.ExecuteReader(sSQL); } internal static IDataReader GetWfMovementStatusClaim(TransactionContext tc, string ObjectIDs) { string sSQL = SQLParser.MakeSQL(@"SELECT next.Status, wn.SENTTIME,NEXT.EmployeeID,objectID,wn.Tier FROM WFMOVEMENTTRAN wn, WFMOVEMENTNEXT next, Employee e where e.EmployeeID=next.EmployeeID and wn.WFMovementTranID=next.WFMovementTranID AND wn.wftypeid=4 AND next.[STATUS]<>0 and wn.objectID in (%q)", ObjectIDs); return tc.ExecuteReader(sSQL); } #endregion #region Delete function internal static void DeleteClaimRequisitionItem(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionItem WHERE ClaimRequisitionItemID=%n", nID); } internal static void DeleteClaimRequisitionItemByClaimRequisitionID(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionItem WHERE ClaimRequisitionID=%n", nID); } #endregion #endregion #region ClaimRequisitionAttachment #region Insert function internal static void InsertClaimRequisitionAttachment(TransactionContext tc, ClaimRequisitionAttachment item) { tc.ExecuteNonQuery(@"INSERT INTO ClaimRequisitionAttachment (ClaimRequisitionAttachmentID, ClaimID, Remarks, FileName, FileLink, Date)" + " VALUES(%n,%n,%s,%s,%s,%d)", DataReader.GetNullValue(item.ID), item.ClaimID, item.Remarks, item.FileName, item.FileLink, item.Date); } #endregion #region Update function internal static void UpdateClaimRequisitionAttachment(TransactionContext tc, ClaimRequisitionAttachment item) { tc.ExecuteNonQuery(@"UPDATE ClaimRequisitionAttachment SET ClaimID=%n, Remarks=%s, FileName=%s, FileLink=%s, Date=%d" + " WHERE ClaimRequisitionAttachmentID=%n", item.ClaimID, item.Remarks, item.FileName, item.FileLink, item.Date, DataReader.GetNullValue(item.ID)); } #endregion #region Get Function internal static IDataReader GetClaimRequisitionAttachment(TransactionContext tc) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionAttachment"); } internal static IDataReader GetClaimRequisitionAttachment(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionAttachment WHERE ClaimRequisitionAttachmentID=%n", nID); } internal static IDataReader GetClaimRequisitionAttachmentByClaimRequisitionID(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionAttachment WHERE ClaimRequisitionID=%n", nID); } #endregion #region Delete function internal static void DeleteClaimRequisitionAttachment(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionAttachment WHERE ClaimRequisitionAttachmentID=%n", nID); } internal static void DeleteClaimRequisitionAttachmentByClaimRequisitionID(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionAttachment WHERE ClaimID=%n", nID); } #endregion #endregion #region ClaimRequisitionRelation #region Insert function internal static void InsertClaimRequisitionRelation(TransactionContext tc, ClaimRequisitionRelation item) { tc.ExecuteNonQuery(@"INSERT INTO ClaimRequisitionRelation (ClaimRequisitionRelationID, ClaimRequisitionID, EnumRelationType, RelationTranID,ItemId,Name)" + " VALUES(%n,%n,%n,%n,%n,%s)", DataReader.GetNullValue(item.ID), item.ClaimRequisitionID, item.EnumRelationType, item.RelationTranID, item.ItemId, item.Name); } #endregion #region Update function internal static void UpdateClaimRequisitionRelation(TransactionContext tc, ClaimRequisitionRelation item) { tc.ExecuteNonQuery(@"UPDATE ClaimRequisitionRelation SET ClaimRequisitionID=%n, EnumRelationType=%n, RelationTranID=%n,ItemId=%n, Name=%s" + " WHERE ClaimRequisitionRelationID=%n", item.ClaimRequisitionID, item.EnumRelationType, item.RelationTranID, DataReader.GetNullValue(item.ID)); } #endregion #region Get Function internal static IDataReader GetClaimRequisitionRelation(TransactionContext tc) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionRelation"); } internal static IDataReader GetClaimRequisitionRelation(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionRelation WHERE ClaimRequisitionRelationID=%n", nID); } internal static IDataReader GetClaimRequisitionRelationByClaimRequisitionID(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionRelation WHERE ClaimRequisitionID=%n", nID); } internal static IDataReader GetClaimRequisitionRelationsByItemId(TransactionContext tc, int nID) { return tc.ExecuteReader("SELECT * FROM ClaimRequisitionRelation WHERE ItemId=%n", nID); } internal static void UpdateClaimRequisitionApproveStatus(TransactionContext tc, int pkid, EnumClaimRequsitionStatus status) { string ssql = ""; ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET wfStatus=%n WHERE ClaimRequisitionID=%n", (int)EnumClaimRequsitionStatus.Approved, pkid); tc.ExecuteNonQuery(ssql); } internal static void UpdateClaimRequisitionNotApproveStatus(TransactionContext tc, int pkid, EnumClaimRequsitionStatus status) { string ssql = ""; ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET wfStatus=%n WHERE ClaimRequisitionID=%n", (int)EnumClaimRequsitionStatus.Submitted, pkid); tc.ExecuteNonQuery(ssql); } internal static void UpdateClaimPaymentStatus(TransactionContext tc, ClaimRequisition item) { string ssql = ""; if (item.PaymentType != null && item.PaymentType != EnumClaimPaymentType.None && item.PaymentType != EnumClaimPaymentType.UnPaid) { if (item.PaymentType == EnumClaimPaymentType.Paid) { ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET PaymentType=%n,PaymentRemarks=%s,PaymentDate=%d,IsPayment=%b,PaidBy=%n,WFStatus=%n WHERE ClaimRequisitionID=%n", (int)item.PaymentType, item.PaymentRemarks, DateTime.Now, true, item.PaidBy, EnumClaimRequsitionStatus.Approved_and_Paid, item.ID); tc.ExecuteNonQuery(ssql); } else { ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET PaymentType=%n,PaymentRemarks=%s,PaymentDate=%d,IsPayment=%b,PaidBy=%n WHERE ClaimRequisitionID=%n", (int)item.PaymentType, item.PaymentRemarks, DateTime.Now, true, item.PaidBy, item.ID); tc.ExecuteNonQuery(ssql); } } } internal static void UpdateClaimPaymentStatusByClaimId(TransactionContext tc, string ids, EnumClaimPaymentType? claimPaymentType, string remarks, int currentUserID) { string ssql = ""; if (claimPaymentType != null && claimPaymentType != EnumClaimPaymentType.None) { ssql = SQLParser.MakeSQL( "UPDATE claimRequisition SET PaymentType=%n,PaymentRemarks=%s,PaymentDate=%d,IsPayment=%b,PaidBy=%n WHERE ClaimRequisitionID in (%q)", (int)claimPaymentType, remarks, DateTime.Now, true, currentUserID, ids); tc.ExecuteNonQuery(ssql); } } internal static IDataReader GetPrevAdavancedAmountbyEmployeeId(TransactionContext tc, int EmployeeId) { string ssql = ""; ssql = SQLParser.MakeSQL( "SELECT * FROM ClaimRequisition WHERE WFStatus = %n AND paymentType = %n AND IsPayment =%b AND isadvance = %b AND EmployeeID = %n", (int)EnumClaimRequsitionStatus.Approved_and_Paid, (int)EnumClaimPaymentType.Paid, true, true, EmployeeId); return tc.ExecuteReader(ssql); } #endregion #region Delete function internal static void DeleteClaimRequisitionRelation(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionRelation WHERE ClaimRequisitionRelationID=%n", nID); } internal static void DeleteClaimRequisitionRelationByClaimRequisitionID(TransactionContext tc, int nID) { tc.ExecuteNonQuery("DELETE FROM ClaimRequisitionRelation WHERE ClaimRequisitionID=%n", nID); } #endregion #endregion #endregion internal static IDataReader GetClaimRequistionByClaimPaymentID(TransactionContext tc, string claimBasicId, int wf) { if (claimBasicId == "") { return tc.ExecuteReader("SELECT * FROM ClaimRequisition"); } else { //return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimRequisitionID=%n AND WFStatus=%n", // claimBasicId, wf); return tc.ExecuteReader("SELECT * FROM ClaimRequisition WHERE ClaimRequisitionID IN (%q)", claimBasicId); } } internal static void UpdateClaimRwquisitionStatus(TransactionContext tc, string _claimRequisitionIDs) { tc.ExecuteNonQuery("UPDATE ClaimRequisition SET JVSTATUS= 1 WHERE ClaimRequisitionID IN (%q)", _claimRequisitionIDs); } internal static IDataReader GetClaimByprocessDate(TransactionContext tc, EnumwfStatus enumwfStatus, int empID)// DateTime processdate, { string ssql = SQLParser.MakeSQL("SELECT * FROM ClaimRequisition WHERE WFStatus = %n And EmployeeID = %n", (int)enumwfStatus, empID);// AND ClaimProcessDate = %d// processdate, return tc.ExecuteReader(ssql); } internal static double GetClearanceAmount(TransactionContext tc, int empID)// DateTime processdate, { object amount; string ssql = SQLParser.MakeSQL("SELECT SUM(ClaimAmount) FROM ClaimRequisition WHERE PaymentType = %n And EmployeeID = %n", (int)EnumClaimPaymentType.Paid_By_Clearance, empID);// AND ClaimProcessDate = %d// processdate, amount = tc.ExecuteScalar(ssql); if (amount != DBNull.Value) return Convert.ToDouble(amount); else return 0; } internal static IDataReader GetByReferenceId(TransactionContext tc, int refId, EnumFileType fileType) { return tc.ExecuteReader("SELECT * FROM FileAttachment where referenceId= %n and fileType=%n ", refId, (int)fileType); } } }