public List <DateTime> GetGroupMeetings(object groupId)
{
List <DateTime> lstGroupMeetings = new List <DateTime>();
AdoHelper objado = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupID", groupId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader dr = objado.ExecDataReaderProc("uspGetGroupMeetings", parms);
while (dr.Read())
{
DateTime meetingDate = new DateTime();
if (dr["meetingDate"] != DBNull.Value)
{
meetingDate = Convert.ToDateTime(dr["meetingDate"]).Date;
}
lstGroupMeetings.Add(meetingDate);
}
return(lstGroupMeetings);
}
public ResultDto ValidateLogin(string loginusername, string password)
{
ResultDto objResultDto = new ResultDto();
try
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@UserName", loginusername);
parms[0].SqlDbType = SqlDbType.VarChar;
parms[1] = new SqlParameter("@Password", password);
parms[1].SqlDbType = System.Data.SqlDbType.VarChar;
SqlDataReader drResult = obj.ExecDataReaderProc("uspValidateLogin", parms);
while (drResult.Read())
{
objResultDto.ObjectId = Convert.ToInt16(drResult["Result"].ToString());
objResultDto.Message = drResult["Message"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
return(objResultDto);
}
public List <GroupMeetingDto> GetMeetingInfoByGroupID(int groupId)
{
GroupMeetingDto grpMeetingDto = null;
List <GroupMeetingDto> lstGroup = new List <GroupMeetingDto>();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupId", groupId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("UspGetGroupMeeitngInfo", parms);
while (Dr.Read())
{
grpMeetingDto = new GroupMeetingDto();
grpMeetingDto.GroupMeetingID = Convert.ToInt32(Dr["GroupMeetingID"]);
grpMeetingDto.IsConducted = Convert.ToBoolean(Dr["IsConducted"]);
grpMeetingDto.IsSplMeeting = Convert.ToBoolean(Dr["IsSplMeeting"]);
grpMeetingDto.MeetingType = grpMeetingDto.IsSplMeeting == true ? "Special" : "Regular";
grpMeetingDto.MeetingMemberCount = Convert.ToInt32(Dr["MemberCount"]);
grpMeetingDto.MeetingDate = Convert.ToDateTime(Dr["MeetingDate"]);
lstGroup.Add(grpMeetingDto);
}
return(lstGroup);
}
public List <GroupMeetingLookupDto> Lookup(int GroupId)
{
List <GroupMeetingLookupDto> lstLookupDto = new List <GroupMeetingLookupDto>();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
GroupMeetingLookupDto grpmeetingLookup = null;
parms[0] = new SqlParameter("@GroupId", GroupId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGroupMeetingLookup", parms);
while (Dr.Read())
{
grpmeetingLookup = new GroupMeetingLookupDto();
grpmeetingLookup.MeetingDate = Convert.ToDateTime(Dr["MeetingDate"]);
grpmeetingLookup.MeetingLockStatus = Convert.ToString(Dr["LockStatus"]);
grpmeetingLookup.GroupMeetingID = Convert.ToInt32(Dr["GroupMeetingID"]);
grpmeetingLookup.IsConducted = Convert.ToBoolean(Dr["IsConducted"]);
grpmeetingLookup.IsSpecialMeeting = Convert.ToBoolean(Dr["IsSplMeeting"]);
grpmeetingLookup.MembersCount = Convert.ToInt32(Dr["MemberCount"]);
lstLookupDto.Add(grpmeetingLookup);
}
return(lstLookupDto);
}
public InterestMasterDto FedGetByID(int id)
{
InterestMasterDto interestMasterDto = new InterestMasterDto();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@InterestID", id);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspInterestByID2", parms);
if (Dr.Read())
{
interestMasterDto.PrincipalAHCode = Dr["AHCode"].ToString();
interestMasterDto.PrincipalAHName = Dr["AHName"].ToString();
if (Dr["IntrestRateID"] != DBNull.Value)
{
interestMasterDto.InterestRate = Convert.ToDecimal(Dr["IntrestRateID"]);
}
if (Dr["PrincipalAHID"] != DBNull.Value)
{
interestMasterDto.PrincipalAHID = Convert.ToInt32(Dr["PrincipalAHID"]);
}
}
return(interestMasterDto);
}
public AccountHeadDto ViewBalance(int ahid, bool isFedaration)
{
AccountHeadDto viewBalance = new AccountHeadDto();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@Ahid", ahid);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
parms[1] = new SqlParameter("@IsFedaration", isFedaration);
parms[1].SqlDbType = System.Data.SqlDbType.Bit;
SqlDataReader Dr = obj.ExecDataReaderProc("uspAccountHeadViewBalance", parms);
if (Dr.Read())
{
viewBalance.OpeningBalance = Convert.ToDecimal(Dr["OpeningBalance"].ToString());
viewBalance.ClosingBalance = Convert.ToDecimal(Dr["ClosingBalance"].ToString());
viewBalance.CurrentYearBalance = Convert.ToDecimal(Dr["CurrentYearBalance"].ToString());
viewBalance.CurrentYearBalanceDr = Convert.ToDecimal(Dr["CurrentYearBalanceDr"].ToString());
viewBalance.CurrentYearBalanceCr = Convert.ToDecimal(Dr["CurrentYearBalanceCr"].ToString());
viewBalance.OpeningBalanceType = Dr["BalanceType"].ToString();
}
return(viewBalance);
}
public List <AccountHeadDto> GetGroupAccountTree(int groupId)
{
List <AccountHeadDto> accountHeadlist = new List <AccountHeadDto>();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupID", groupId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGetGroupAccountTree", parms);
while (Dr.Read())
{
AccountHeadDto ahdto = new AccountHeadDto();
ahdto.AHCode = Dr["AHCode"] == DBNull.Value ? string.Empty : Convert.ToString(Dr["AHCode"]);
ahdto.AHID = Convert.ToInt32(Dr["AHID"]);
ahdto.AHLevel = Convert.ToInt32(Dr["AHLevel"]);
ahdto.AHName = Dr["AHName"] == DBNull.Value ? string.Empty : Convert.ToString(Dr["AHName"]);
ahdto.AHType = Dr["AHType"] == DBNull.Value ? 0: Convert.ToInt32(Dr["AHType"]);
ahdto.OpeningBalanceType = Dr["OpeningBalanceType"] == DBNull.Value ? string.Empty : Convert.ToString(Dr["OpeningBalanceType"]);
ahdto.ParentAHID = Dr["ParentAHID"] == DBNull.Value ? 0 : Convert.ToInt32(Dr["ParentAHID"]);
accountHeadlist.Add(ahdto);
}
return(accountHeadlist);
}
public List <GroupMeetingViewDto> GetGroupMeetingsView(int groupMeetingId)
{
var lstGroupMeetings = new List <GroupMeetingViewDto>();
GroupMeetingViewDto objMeeting = null;
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@GroupMeetingID", groupMeetingId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader dr = obj.ExecDataReaderProc("uspGetGroupMeetingsByMonth", parms);
while (dr.Read())
{
objMeeting = new GroupMeetingViewDto();
objMeeting.MeetingComments = Convert.ToString(dr["MeetingComments"]);
objMeeting.MeetingObjective = Convert.ToString(dr["MeetingObjective"]);
objMeeting.MeetingDate = Convert.ToDateTime(dr["MeetingDate"]);
if (dr["TransactionDate"] != DBNull.Value)
{
objMeeting.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
}
objMeeting.IsConducted = Convert.ToBoolean(dr["IsConducted"]);
objMeeting.IsSplMeeting = Convert.ToBoolean(dr["IsSplMeeting"]);
objMeeting.ReasonName = Convert.ToString(dr["ReasonName"]);
objMeeting.OtherReason = Convert.ToString(dr["OtherReason"]);
objMeeting.Members = Convert.ToString(dr["Members"]);
lstGroupMeetings.Add(objMeeting);
}
return(lstGroupMeetings);
}
public List <ReceiptTranscationDto> GetGroupReceiptAccountdetails(int groupID, string transactionDate)
{
List <ReceiptTranscationDto> lstGroupReceiptDto = new List <ReceiptTranscationDto>();
try
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@GroupID", groupID);
parms[0].SqlDbType = SqlDbType.Int;
parms[1] = new SqlParameter("@TransactionDate", transactionDate);
parms[1].SqlDbType = SqlDbType.VarChar;
SqlDataReader sdr = obj.ExecDataReaderProc("uspGetGroupReceiptTemplate", parms);
while (sdr.Read())
{
ReceiptTranscationDto objLoanOBLookup = new ReceiptTranscationDto();
objLoanOBLookup.AHID = Convert.ToInt32(sdr["AHID"]);
objLoanOBLookup.AHCode = Convert.ToString(sdr["AHCode"]);
objLoanOBLookup.AHName = sdr["AHName"].ToString();
objLoanOBLookup.ClosingBalance = Convert.ToDecimal(sdr["ClosingBalance"]);
objLoanOBLookup.SLAcNo = Convert.ToString(sdr["SLAccount"]);
lstGroupReceiptDto.Add(objLoanOBLookup);
}
}
catch (Exception ex)
{
}
return(lstGroupReceiptDto);
}
public List <ModuleActionDto> GetModuleActionsByUserId(int userid, bool isFederation)
{
List <ModuleActionDto> moduleActoins = new List <ModuleActionDto>();
try
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@UserId", userid);
parms[0].SqlDbType = SqlDbType.Int;
parms[1] = new SqlParameter("@IsFedertaion", isFederation);
parms[1].SqlDbType = SqlDbType.Bit;
SqlDataReader drResult = obj.ExecDataReaderProc("uspGetModlueActionsByUserId", parms);
while (drResult.Read())
{
ModuleActionDto moduleActionDto = new ModuleActionDto();
moduleActionDto.ActionName = drResult["ActionName"].ToString();
moduleActionDto.ModuleActionCode = drResult["ModuleActionCode"].ToString();
moduleActionDto.ModuleActionId = Convert.ToInt32(drResult["ModuleActionId"].ToString());
moduleActionDto.ModuleId = Convert.ToInt32(drResult["ModuleId"].ToString());
moduleActionDto.ModuleName = drResult["ModuleName"].ToString();
moduleActionDto.Status = Convert.ToInt32(drResult["Status"].ToString());
moduleActionDto.Url = drResult["Url"].ToString();
moduleActoins.Add(moduleActionDto);
}
}
catch (Exception)
{
}
return(moduleActoins);
}
public InterestMasterDto GetInterestRatesByID(int GroupID, int PrincipleAHID)
{
InterestMasterDto objInterestRates = null;
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@GroupId", GroupID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
parms[1] = new SqlParameter("@PrincipleAHID", PrincipleAHID);
parms[1].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGetInterestRates", parms);
if (Dr.Read())
{
objInterestRates = new InterestMasterDto();
if (!string.IsNullOrEmpty(Convert.ToString(Dr["PenalAHID"])))
{
objInterestRates.PenalAHID = Convert.ToInt32(Dr["PenalAHID"]);
}
objInterestRates.InterestAHID = Convert.ToInt32(Dr["InterestAHID"]);
objInterestRates.InterestRate = Convert.ToDecimal(Dr["ROI"]);
objInterestRates.DueDay = Convert.ToInt32(Dr["DueDay"]);
objInterestRates.InterestID = Convert.ToInt32(Dr["InterestID"]);
objInterestRates.PenalROI = Convert.ToDecimal(Dr["PROI"]);
}
return(objInterestRates);
}
public GroupLoanApplicationDto GetGroupApplicationDetailsByID(int loanMasterId)
{
GroupLoanApplicationDto objGroupLoanApp = new GroupLoanApplicationDto();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@LoanMasterID", loanMasterId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGroupLoanApplicationGetByLoanMasterID", parms);
if (Dr.Read())
{
objGroupLoanApp.LoanMasterId = Convert.ToInt32(Dr["LoanMasterID"]);
objGroupLoanApp.LoanCode = Convert.ToString(Dr["LoanCode"]);
objGroupLoanApp.LoanType = Convert.ToString(Dr["LoanType"]);
objGroupLoanApp.GroupID = Convert.ToInt32(Dr["GroupID"]);
objGroupLoanApp.LoanApplicationDate = Convert.ToDateTime(Dr["LoanApplicationDate"]);
objGroupLoanApp.LoanPurpose = Convert.ToInt32(Dr["LoanPurpose"]);
objGroupLoanApp.FundSourse = Convert.ToInt32(Dr["FundSourceID"]);
objGroupLoanApp.LoanAmountApplied = Convert.ToDecimal(Dr["LoanAmountApplied"]);
objGroupLoanApp.NoofInstallmentsProposed = Convert.ToByte(Dr["NoofInstallmentsProposed"]);
objGroupLoanApp.Mode = Convert.ToInt32(Dr["Mode"]);
objGroupLoanApp.ProjectID = Convert.ToInt32(Dr["ProjectID"]);
objGroupLoanApp.StatusCode = Convert.ToString(Dr["StatusCode"]);
objGroupLoanApp.Status = Convert.ToString(Dr["Status"]);
}
return(objGroupLoanApp);
}
public List <RepaymentDto> GetRepayment(int loanMasterID, string transactionDate)
{
List <RepaymentDto> listRepaymentDto = new List <RepaymentDto>();
try
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[2];
parms[0] = new SqlParameter("@MemberID", loanMasterID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
parms[1] = new SqlParameter("@TransactionDate", transactionDate);
parms[1].SqlDbType = System.Data.SqlDbType.VarChar;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGetRepayment", parms);
while (Dr.Read())
{
RepaymentDto objRepaymentDto = new RepaymentDto();
objRepaymentDto.AHID = Convert.ToInt32(Dr["AHID"]);
objRepaymentDto.Amount = Convert.ToDecimal(Dr["PrincipalBalance"]);
//objRepaymentDto.SLAccountAHID = Convert.ToInt32(Dr["SLAccountAHID"]);
objRepaymentDto.LoanRepaymentID = Convert.ToInt32(Dr["LoanMasterID"]);
listRepaymentDto.Add(objRepaymentDto);
}
}
catch (Exception ex)
{
}
return(listRepaymentDto);
}
public BankLoanDto GetBankLoanApplicationDetailsById(int bankLoanId)
{
BankLoanDto objLoanApplication = new BankLoanDto();
AdoHelper objAdo = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@BankLoanId", bankLoanId);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader dr = objAdo.ExecDataReaderProc("uspBankLoanApplicationGetByBankLoanID", parms);
if (dr.Read())
{
objLoanApplication.BankLoanId = Convert.ToInt32(dr["BankLoanId"]);
objLoanApplication.GroupId = Convert.ToInt32(dr["GroupId"]);
objLoanApplication.LoanRequestDate = Convert.ToDateTime(dr["RequestDate"]);
objLoanApplication.LoanAmountRequested = Convert.ToInt32(dr["RequestedAmount"]);
objLoanApplication.NoofInstallments = Convert.ToByte(dr["NoOfInStallments"]);
objLoanApplication.LoanAmountApproved = Convert.ToDecimal(dr["ApprovedAmount"]);
objLoanApplication.LoanAmountApprovedDate = Convert.ToDateTime(dr["ApprovedDate"]);
objLoanApplication.DisbursedAmount = Convert.ToDecimal(dr["DisbursementAmount"]);
objLoanApplication.DisbursedDate = Convert.ToDateTime(dr["DisbursementDate"]);
objLoanApplication.InterestRate = Convert.ToInt32(dr["InterestRate"]);
objLoanApplication.BankEntryId = Convert.ToInt32(dr["BankEntryId"]);
objLoanApplication.SLAHId = Convert.ToInt32(dr["SLAHID"]);
objLoanApplication.ReferenceNumber = Convert.ToString(dr["ReferenceNumber"]);
objLoanApplication.DueDate = Convert.ToDateTime(dr["DueDate"]);
objLoanApplication.Status = Convert.ToString(dr["Status"]);
objLoanApplication.LoanNumber = Convert.ToString(dr["LinkageNumber"]);
objLoanApplication.Narration = Convert.ToString(dr["Narration"]);
objLoanApplication.EMI = Convert.ToInt32(dr["EMI"]);
}
return(objLoanApplication);
}
public ActionResult KategoriListele()
{
ModelKategori.Clear();
using (AdoHelper adoHelper = new AdoHelper())
{
string sorgu = "Select * From kategori";
dictList.Clear();
SqlDataReader oku = adoHelper.ExecDataReaderProc(sorgu, dictList);
while (oku.Read())
{
var veri = new KategoriModel();
veri.KategoriId = Convert.ToInt32(oku["id"]);
veri.Kategori = oku["kategori"].ToString();
ModelKategori.Add(veri);
}
return(View(ModelKategori));
}
// BaglantiAc();
//string sorgu = "Select * From kategori";
//SqlCommand cmd = new SqlCommand(sorgu, cnn);
//SqlDataReader oku = cmd.ExecuteReader();
//while (oku.Read())
//{
// var veri = new KategoriModel();
// veri.KategoriId = Convert.ToInt32(oku["id"]);
// veri.Kategori = oku["kategori"].ToString();
// ModelKategori.Add(veri);
//}
//cnn.Close();
//return View(ModelKategori);
}
public List <BankMasterLookupDto> GetLookup(int?groupId)
{
List <BankMasterLookupDto> lstBanks = new List <BankMasterLookupDto>();
AdoHelper obj = new AdoHelper();
SqlParameter parm = new SqlParameter();
parm.ParameterName = "@GroupId";
if (groupId != null)
{
parm.Value = groupId.Value;
}
SqlDataReader Dr = obj.ExecDataReaderProc("uspBankMasterLookup", parm);
while (Dr.Read())
{
BankMasterLookupDto bankMasterDto = new BankMasterLookupDto();
bankMasterDto.AccountNumber = Dr["AccountNumber"].ToString();
bankMasterDto.BankEntryID = Convert.ToInt32(Dr["BankEntryID"].ToString());
bankMasterDto.BankCode = Dr["BankCode"].ToString();
bankMasterDto.BankName = Dr["BankName"].ToString();
bankMasterDto.IFSC = Dr["IFSC"].ToString();
bankMasterDto.isFederation = Dr["IS_FED"].ToString();
bankMasterDto.ClusterName = Dr["CLUSTERNAME"].ToString();
bankMasterDto.GroupName = Dr["GROUP_NAME"].ToString();
bankMasterDto.StatusID = Convert.ToInt32(Dr["StatusID"]);
bankMasterDto.StatusCode = Dr["StatusCode"].ToString();
bankMasterDto.GroupId = Convert.ToInt32(Dr["GroupId"]);
lstBanks.Add(bankMasterDto);
}
return(lstBanks);
}
public List <ModuleDto> GetModuleByRoleId(int roleId)
{
List <ModuleDto> modules = new List <ModuleDto>();
try
{
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@RoleId", roleId);
parms[0].SqlDbType = SqlDbType.Int;
SqlDataReader drResult = obj.ExecDataReaderProc("uspModulesGetByRoleId", parms);
while (drResult.Read())
{
ModuleDto moduleDto = new ModuleDto();
moduleDto.ModuleId = DBNull.Value == drResult["ModuleId"] ? 0 : Convert.ToInt16(drResult["ModuleId"].ToString());
moduleDto.IsFederation = DBNull.Value == drResult["IsFederation"] ? false : Boolean.Parse(drResult["IsFederation"].ToString());
moduleDto.ModuleName = DBNull.Value == drResult["ModuleName"] ? null : drResult["ModuleName"].ToString();
moduleDto.ModuleCode = DBNull.Value == drResult["ModuleCode"] ? "" : drResult["ModuleCode"].ToString();
moduleDto.Url = DBNull.Value == drResult["Url"] ? "" : drResult["Url"].ToString();
moduleDto.ControlId = DBNull.Value == drResult["ControlId"] ? "" : drResult["ControlId"].ToString();
moduleDto.DisplayOrder = DBNull.Value == drResult["DisplayOrder"] ? 0 : Convert.ToInt32(drResult["DisplayOrder"].ToString());
moduleDto.ParentID = DBNull.Value == drResult["ParentID"] ? 0 : Convert.ToInt32(drResult["ParentID"].ToString());
moduleDto.IsSeed = DBNull.Value == drResult["IsSeed"] ? true : Boolean.Parse(drResult["IsSeed"].ToString());
modules.Add(moduleDto);
}
}
catch (Exception)
{
}
return(modules);
}
public DisbursementVoucherDto GetDisbursementVoucher(int LoanMasterID)
{
DisbursementVoucherDto objDisbursementVoucherDto = new DisbursementVoucherDto();
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@LoanMasterID", LoanMasterID);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGetDisbursementVoucher", parms);
if (Dr.Read())
{
objDisbursementVoucherDto.Amount = Convert.ToDecimal(Dr["Amount"]);
objDisbursementVoucherDto.VoucherNumber = Convert.ToString(Dr["VoucherNumber"]);
objDisbursementVoucherDto.TransactionMode = Convert.ToString(Dr["TransactionMode"]);
objDisbursementVoucherDto.TransactionDate = Convert.ToDateTime(Dr["TransactionDate"]);
if (objDisbursementVoucherDto.TransactionMode == "B")
{
objDisbursementVoucherDto.ChequeDate = Convert.ToDateTime(Dr["ChequeDate"]);
objDisbursementVoucherDto.ChequeNumber = Convert.ToString(Dr["ChequeNumber"]);
objDisbursementVoucherDto.BankID = Convert.ToInt32(Dr["AHID"]);
objDisbursementVoucherDto.BankName = Convert.ToString(Dr["BankName"]);
}
}
return(objDisbursementVoucherDto);
}
public TypeQueryResult GetTypeQueryResult(string typeCode, string param1 = null, string param2 = null, string param3 = null, string param4 = null, string param5 = null, string param6 = null)
{
TypeQueryResult lstResult = new TypeQueryResult();
TypeQueryDto obj = null;
AdoHelper ado = new AdoHelper();
SqlParameter[] param = new SqlParameter[7];
param[0] = new SqlParameter("@TypeCode", typeCode);
param[1] = new SqlParameter("@Param1", param1);
param[2] = new SqlParameter("@Param2", param2);
param[3] = new SqlParameter("@Param3", param3);
param[4] = new SqlParameter("@Param4", param4);
param[5] = new SqlParameter("@Param5", param5);
param[6] = new SqlParameter("@Param6", param6);
SqlDataReader dr = ado.ExecDataReaderProc("uspGetTypeQueryResult", param);
while (dr.Read())
{
int fieldCount = dr.FieldCount;
obj = new TypeQueryDto();
obj.Id = Convert.ToString(dr["Id"]);
if (fieldCount > 1 && dr["Name"] != DBNull.Value)
{
obj.Name = Convert.ToString(dr["Name"]);
}
if (fieldCount > 2 && dr["Code"] != DBNull.Value)
{
obj.Code = Convert.ToString(dr["Code"]);
}
if (fieldCount > 3 && dr["Value1"] != DBNull.Value)
{
obj.Value1 = Convert.ToString(dr["Value1"]);
}
if (fieldCount > 4 && dr["Value2"] != DBNull.Value)
{
obj.Value2 = Convert.ToString(dr["Value2"]);
}
if (fieldCount > 5 && dr["Value3"] != DBNull.Value)
{
obj.Value3 = Convert.ToString(dr["Value3"]);
}
if (fieldCount > 6 && dr["Value4"] != DBNull.Value)
{
obj.Value4 = Convert.ToString(dr["Value4"]);
}
if (fieldCount > 7 && dr["Value5"] != DBNull.Value)
{
obj.Value5 = Convert.ToString(dr["Value5"]);
}
lstResult.Add(obj);
}
return(lstResult);
}
public List <KeHoachCT> GetKeHoachCongTac(JQueryDataTableParamModel param)
{
const string storeName = "st_GetKetHoachCongTac";
string query = @"SELECT DATENAME(dw,TuNgay) as Ngay, [IdKeHoach]
,khct.TenKeHoach
,nd.HoTen
,nd.ThuTu
,khct.[UserName]
,TuNgay AS ThoiGian
,[KeHoachCongTac]
,[TuGio] +[TuNgay] AS TuNgayModel
,[DenGio]+[TuNgay] AS DenNgayModel
,[DiaDiem]
,TuGio
,DenGio
,DenNgay AS DenNgayView
,cv.VietTat
,cv.TenChucVu as ChucVu
,CASE MucDo
WHEN 1 THEN N'Rất quan trọng'
WHEN 2 THEN N'Quan trọng'
WHEN 3 THEN N'Bình thường'
END AS MucDoView
FROM [KeHoachCongTac] khct LEFT JOIN [dbo].[NguoiDung] nd
on khct.UserName=nd.UserName
LEFT JOIN ChucVu cv
on cv.IdChucVu=nd.ChucVu
" ;
@query = @query + " WHERE khct.IsDeleted=0 AND ((TuNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "') OR "
+ "(DenNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "')) ";
if (!string.IsNullOrEmpty(param.CanBo))
{
@query = @query + " AND khct.UserName IN (" + param.CanBo + ")";
}
if (!string.IsNullOrEmpty(param.sSearch))
{
param.sSearch = param.sSearch.Trim();
@query = @query + " AND ( khct.TenKeHoach LIKE N'%" + param.sSearch +
"%' OR khct.KeHoachCongTac LIKE N'%" + param.sSearch +
"%' OR khct.DiaDiem LIKE N'%" + param.sSearch
+ "%' OR nd.HoTen LIKE N'%" + param.sSearch
+ "%')";
}
@query = @query + " ORDER BY nd.ThuTu ASC ";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@Query", @query),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
// GET: Results
public HttpResponseMessage Post(EXCOViewModel ManulEntryData)
{
ApiViewModel sendMessage = new ApiViewModel();
try
{
CurrentUser user = ApiUser;
AdoHelper ado = new AdoHelper();
SqlParameter param1 = new SqlParameter("@SelectedTests", ManulEntryData.SelectedTests);
SqlParameter param2 = new SqlParameter("@EntryBy", user.UserName);
SqlParameter param3 = new SqlParameter("@EntryDate", DateTime.Now);
SqlParameter param4 = new SqlParameter("@ExcoRating", ManulEntryData.ExcoRating);
SqlParameter param5 = new SqlParameter("@StartWT", ManulEntryData.StartWT);
SqlParameter param6 = new SqlParameter("@FinalWT", ManulEntryData.FinalWT);
SqlParameter param7 = new SqlParameter("@SpeciComment", ManulEntryData.SpeciComment);
SqlParameter param8 = new SqlParameter("@Operator", ManulEntryData.Operator);
SqlParameter param9 = new SqlParameter("@ExposedArea", ManulEntryData.ExposedArea);
SqlParameter param10 = new SqlParameter("@StartpH", ManulEntryData.StartpH);
SqlParameter param11 = new SqlParameter("@FinalpH", ManulEntryData.FinalpH);
SqlParameter param12 = new SqlParameter("@TestDate", ManulEntryData.TestDate);
SqlParameter param13 = new SqlParameter("@TimeHrs", ManulEntryData.TimeHrs);
SqlParameter param14 = new SqlParameter("@TimeMns", ManulEntryData.TimeMns);
SqlParameter param15 = new SqlParameter("@BatchNo", ManulEntryData.BatchNo);
sendMessage.Message = "";
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDEXCOResults_Insert", "RND", new object[]
{ param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12,
param13, param14, param15 }))
{
if (reader.HasRows)
{
while (reader.Read())
{
sendMessage.Custom = Convert.ToInt32(reader["TestingNo"]);
if (sendMessage.Message == "")
{
sendMessage.Message += sendMessage.Custom;
}
else
{
sendMessage.Message += ", " + sendMessage.Custom;
}
}
sendMessage.Success = true;
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
}
catch (Exception ex)
{
_logger.Error(ex.Message);
sendMessage.Success = false;
// return new HttpResponseMessage(HttpStatusCode.InternalServerError);
}
return(Serializer.ReturnContent(sendMessage, this.Configuration.Services.GetContentNegotiator(), this.Configuration.Formatters, this.Request));
}
public HttpResponseMessage Get(string UserName, string UserAnswer)
{
// SqlDataReader reader = null;
RNDLogin dbUser = null;
ApiViewModel VM = null;
try
{
VM = new ApiViewModel();
if (!string.IsNullOrEmpty(UserName) && !string.IsNullOrEmpty(UserAnswer))
{
AdoHelper ado = new AdoHelper();
SqlParameter param1 = new SqlParameter("@UserName", UserName);
SqlParameter param2 = new SqlParameter("@UserAnswer", UserAnswer);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDResetPassword", "RND", new object[] { param1, param2 }))
{
if (reader.HasRows && reader.Read())
{
dbUser = new RNDLogin();
dbUser.UserId = Convert.ToInt32(reader["UserId"]);
dbUser.FirstName = Convert.ToString(reader["FirstName"]);
dbUser.LastName = Convert.ToString(reader["LastName"]);
dbUser.UserType = Convert.ToString(reader["UserType"]);
dbUser.PermissionLevel = Convert.ToString(reader["PermissionLevel"]);
dbUser.IssueDate = (!string.IsNullOrEmpty(reader["IssueDate"].ToString())) ? Convert.ToDateTime(reader["IssueDate"]) : (DateTime?)null;
dbUser.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
dbUser.CreatedOn = Convert.ToDateTime(reader["CreatedOn"]);
dbUser.StatusCode = Convert.ToString(reader["StatusCode"]);
VM.Custom = dbUser;
string token = Guid.NewGuid().ToString("D") + Guid.NewGuid().ToString("D");
dbUser.UserName = UserName;
dbUser.Token = token;
VM.Custom = dbUser;
ado = new AdoHelper();
SqlParameter param3 = new SqlParameter("@UserId", dbUser.UserId);
SqlParameter param4 = new SqlParameter("@Token", token);
ado.ExecScalarProc("RNDSecurityTokens_Insert", "RND", new object[] { param3, param4 });
}
else
{
VM.Message = MessageConstants.InvalidUser;
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
}
}
catch (Exception ex)
{
_logger.Error(ex.Message);
return(new HttpResponseMessage(HttpStatusCode.InternalServerError));
}
return(Serializer.ReturnContent(VM, this.Configuration.Services.GetContentNegotiator(), this.Configuration.Formatters, this.Request));
}
/// <summary>
/// Validate the Hostname
/// </summary>
/// <param name="hostName"></param>
/// <returns></returns>
private bool AssignTenant(string hostName)
{
bool isValid = false;
try
{
if (!string.IsNullOrEmpty(hostName))
{
var token = HttpContext.Current.Request.Headers.Get("Token");
if (token != null)
{
//Map the token with database token to get user details
//Sample
//SqlDataReader reader = null;
CurrentUser dbCUser = null;
AdoHelper ado = new AdoHelper();
SqlParameter param1 = new SqlParameter("@Token", token);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDGetUser_ReadByID", "RND", new object[] { param1 }))
{
if (reader.HasRows && reader.Read())
{
dbCUser = new CurrentUser();
dbCUser.UserId = Convert.ToInt32(reader["UserId"]);
dbCUser.UserName = Convert.ToString(reader["UserName"]);
dbCUser.FullName = Convert.ToString(reader["FullName"]);
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close();
ado._conn.Dispose();
}
}
//var user = new CurrentUser { UserId = 1, UserName = "******", FullName = "Test User" };
if (dbCUser != null)
{
string data = JsonConvert.SerializeObject(dbCUser);
HttpContext.Current.Request.Headers.Remove("User");
HttpContext.Current.Request.Headers.Add("User", data);
isValid = true;
}
else
{
//dbCUser = new CurrentUser { UserId = 1, UserName = "******", FullName = "Test User" };
//string data = JsonConvert.SerializeObject(dbCUser);
//HttpContext.Current.Request.Headers.Remove("User");
//HttpContext.Current.Request.Headers.Add("User", data);
//isValid = true;
}
}
}
}
catch (Exception ex)
{
_logger.Error("Er" + ex.Message);
}
return(isValid);
}
// public HttpResponseMessage Post(string SelectedTests)
public HttpResponseMessage Get(string SelectedTests, string WorkStudyID)
{
AdoHelper ado = new AdoHelper();
//SqlDataReader reader = null;
List <RNDTesting> lstTests = new List <RNDTesting>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
//if (SelectedTests == null)
// SelectedTests = "ALL";
SqlParameter param0 = new SqlParameter("@TestingNos", SelectedTests);
SqlParameter param1 = new SqlParameter("@WorkStudyID", @WorkStudyID);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDPrintTesting", "RND", param0, param1))
{
if (reader.HasRows)
{
RNDTesting TM = null;
while (reader.Read())
{
TM = new RNDTesting();
TM.total = Convert.ToInt32(reader["total"]);
TM.TestingNo = Convert.ToInt32(reader["TestingNo"]);
TM.Alloy = Convert.ToString(reader["Alloy"]);
TM.GageThickness = Convert.ToString(reader["GageThickness"]);
TM.Hole = Convert.ToString(reader["Hole"]);
TM.Location1 = Convert.ToString(reader["Location1"]);
TM.Location2 = Convert.ToString(reader["Location2"]);
TM.Location3 = Convert.ToString(reader["Location3"]);
TM.LotID = Convert.ToString(reader["LotID"]);
TM.Orientation = Convert.ToString(reader["Orientation"]);
TM.PieceNo = Convert.ToString(reader["PieceNo"]);
TM.SpeciComment = Convert.ToString(reader["SpeciComment"]);
TM.TestType = Convert.ToString(reader["TestType"]);
TM.SubTestType = Convert.ToString(reader["SubTestType"]);
TM.Temper = Convert.ToString(reader["Temper"]);
TM.TestLab = Convert.ToString(reader["TestLab"]);
TM.UACPart = Convert.ToInt32(reader["UACPart"]);
TM.WorkStudyID = Convert.ToString(reader["WorkStudyID"]);
TM.Printed = Convert.ToChar(reader["Printed"]);
lstTests.Add(TM);
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
DataSearch <RNDTesting> ds = new DataSearch <RNDTesting>
{
items = lstTests,
total = (lstTests != null && lstTests.Count > 0) ? lstTests[0].total : 0
};
return(Serializer.ReturnContent(ds, this.Configuration.Services.GetContentNegotiator(), this.Configuration.Formatters, this.Request));
}
public ActionResult Index()
{
//BaglantiAc();
//string ySorgu = "SELECT COUNT(*) from haberler0";
//SqlCommand yCmd = new SqlCommand(ySorgu, cnn);
//SqlDataReader dr = yCmd.ExecuteReader();
//dr.Read();
//TempData["Counts"] = dr[0].ToString();
//cnn.Close();
//return View();
using (AdoHelper adoHelper = new AdoHelper())
{
string sorgu = "SELECT * FROM haberler0 where aktif=@aktif";
dictList.Clear();
dictList.Add("@aktif", 1);
SqlDataReader oku = adoHelper.ExecDataReaderProc(sorgu, dictList);
List <HaberModel> Listele = new List <HaberModel>();
//var dd = adoHelper.GetDefault<string>();
//dd.GetType();
while (oku.Read())
{
var veri = new HaberModel();
veri.id = oku["id"].ToString();
veri.resim = oku["img"].ToString();
veri.baslik = metinKısalt.baslik(oku["baslik"].ToString());
veri.icerik = metinKısalt.icerik(oku["icerik"].ToString());
veri.kategori = oku["kategori"].ToString();
veri.tarih = oku["tarih"].ToString();
x.Add(veri);
}
}
return(View(x));
//x.Clear();
//string sorgu = "SELECT * FROM haberler0 where aktif=1";
//SqlCommand cmd = new SqlCommand(sorgu, cnn);
//SqlDataReader oku = cmd.ExecuteReader();
//List<HaberModel> Listele = new List<HaberModel>();
//while (oku.Read())
//{
// var veri = new HaberModel();
// veri.id = oku["id"].ToString();
// veri.resim = oku["img"].ToString();
// veri.baslik = metinKısalt.baslik(oku["baslik"].ToString());
// veri.icerik = metinKısalt.icerik(oku["icerik"].ToString());
// veri.kategori = oku["kategori"].ToString();
// veri.tarih = oku["tarih"].ToString();
// x.Add(veri);
//}
//cnn.Close();
//return View(x);
//Information ınformation = new Information();
//ınformation.Title = "asd";
}
///// <summary>
/// Retrieve the Assign Material data and Assign to Grid
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
private DataSearch <RNDMaterial> GetAssignMaterial(DataGridoption option)
{
AdoHelper ado = new AdoHelper();
List <RNDMaterial> lstAssignMaterial = new List <RNDMaterial>();
List <SqlParameter> lstSqlParameter = new List <SqlParameter>();
lstSqlParameter.Add(new SqlParameter("@CurrentPage", option.pageIndex));
lstSqlParameter.Add(new SqlParameter("@NoOfRecords", option.pageSize));
AddSearchFilter(option, lstSqlParameter);
using (SqlDataReader reader = ado.ExecDataReaderProc("RNDAssignMaterial_Read", "RND", lstSqlParameter.Cast <object>().ToArray()))
{
if (reader.HasRows)
{
RNDMaterial AM = null;
while (reader.Read())
{
AM = new RNDMaterial();
AM.total = Convert.ToInt32(reader["total"]);
AM.RecID = Convert.ToInt32(reader["RecID"]);
AM.WorkStudyID = Convert.ToString(reader["WorkStudyID"]);
AM.SoNum = Convert.ToString(reader["SoNum"]);
AM.MillLotNo = Convert.ToInt32(reader["MillLotNo"]);
AM.CustPart = Convert.ToString(reader["CustPart"]);
AM.UACPart = Convert.ToDecimal(reader["UACPart"]);
AM.Alloy = Convert.ToString(reader["Alloy"]);
AM.Temper = Convert.ToString(reader["Temper"]);
AM.GageThickness = Convert.ToString(reader["GageThickness"]);
AM.Location2 = Convert.ToString(reader["Location2"]);
AM.Hole = Convert.ToString(reader["Hole"]);
AM.PieceNo = Convert.ToString(reader["PieceNo"]);
AM.Comment = Convert.ToString(reader["Comment"]);
AM.EntryDate = (!string.IsNullOrEmpty(reader["EntryDate"].ToString())) ? Convert.ToDateTime(reader["EntryDate"]) : (DateTime?)null;
AM.EntryBy = Convert.ToString(reader["EntryBy"]);
AM.DBCntry = Convert.ToString(reader["DBCntry"]);
// AM.RCS = Convert.ToChar(reader["RCS"]);
lstAssignMaterial.Add(AM);
}
}
if (ado._conn != null && ado._conn.State == System.Data.ConnectionState.Open)
{
ado._conn.Close(); ado._conn.Dispose();
}
}
DataSearch <RNDMaterial> ds = new DataSearch <RNDMaterial>
{
items = lstAssignMaterial,
total = (lstAssignMaterial != null && lstAssignMaterial.Count > 0) ? lstAssignMaterial[0].total : 0
};
return(ds);
}
public List <KeHoachCT> GetLichCongTac()
{
const string storeName = "st_getKeHoach";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public List <PhongBan> GetNguoiDungPhongBan()
{
const string storeName = "st_getNguoiDungPhongBan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <PhongBan>(reader));
}
}
public List <ScheduleDTO> GetSchedulesForDisbursement(int LoanMasterId, decimal LoanAmount, decimal interestrate, int loanperiod, DateTime StartPaymentDate, string LastPaidDateOrDisbDate = null)
{
List <ScheduleDTO> lstSchedules = new List <ScheduleDTO>();
ScheduleDTO objSchedule = null;
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[6];
parms[0] = new SqlParameter("@LoanAmount", LoanAmount);
parms[0].SqlDbType = System.Data.SqlDbType.Decimal;
parms[1] = new SqlParameter("@InterestRate", interestrate);
parms[1].SqlDbType = System.Data.SqlDbType.Decimal;
parms[2] = new SqlParameter("@LoanPeriod", loanperiod);
parms[2].SqlDbType = System.Data.SqlDbType.Int;
parms[3] = new SqlParameter("@StartPaymentDate", StartPaymentDate);
parms[3].SqlDbType = System.Data.SqlDbType.DateTime;
parms[4] = new SqlParameter("@CalculationStartDate", LastPaidDateOrDisbDate);
parms[4].SqlDbType = System.Data.SqlDbType.DateTime;
parms[5] = new SqlParameter("@LoanMasterId", LoanMasterId);
parms[5].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspGetEmiNew", parms);
while (Dr.Read())
{
objSchedule = new ScheduleDTO();
objSchedule.PERIOD = Convert.ToInt64(Dr["PERIOD"]);
objSchedule.Opening_Balance = Convert.ToDecimal(Dr["OPENING_BALANCE"]);
objSchedule.PAYDATE = Convert.ToDateTime(Dr["PAYDATE"]);
if (Dr["PAYMENT"] != DBNull.Value)
{
objSchedule.PAYMENT = Convert.ToDecimal(Dr["PAYMENT"]);
}
objSchedule.PRINCIPAL = Convert.ToDecimal(Dr["PRINCIPAL"]);
objSchedule.DAYS = Convert.ToInt32(Dr["DAYS"]);
objSchedule.INTEREST = Convert.ToDecimal(Dr["INTEREST"]);
objSchedule.CURRENT_BALANCE = Convert.ToDecimal(Dr["CURRENT_BALANCE"]);
objSchedule.INTERESTRate = interestrate;
lstSchedules.Add(objSchedule);
}
return(lstSchedules);
}
public InterestMasterDto GetIntrestRateByIntrestId(int Id)
{
InterestMasterDto objIntrestMasterDto = null;
AdoHelper obj = new AdoHelper();
SqlParameter[] parms = new SqlParameter[1];
parms[0] = new SqlParameter("@InterestID", Id);
parms[0].SqlDbType = System.Data.SqlDbType.Int;
SqlDataReader Dr = obj.ExecDataReaderProc("uspInterestByID", parms);
if (Dr.Read())
{
objIntrestMasterDto = new InterestMasterDto();
if (!string.IsNullOrEmpty(Convert.ToString(Dr["PenalAHID"])))
{
objIntrestMasterDto.PenalAHID = Convert.ToInt32(Dr["PenalAHID"]);
}
objIntrestMasterDto.InterestAHID = Convert.ToInt32(Dr["InterestAHID"]);
objIntrestMasterDto.InterestCode = Convert.ToString(Dr["InterestCode"]);
objIntrestMasterDto.InterestName = Convert.ToString(Dr["InterestName"]);
objIntrestMasterDto.InterestID = Convert.ToInt32(Dr["InterestID"]);
objIntrestMasterDto.PrincipalAHID = Convert.ToInt32(Dr["PrincipalAHID"]);
objIntrestMasterDto.AHName = Convert.ToString(Dr["AHName"]);
objIntrestMasterDto.InterestAHName = Convert.ToString(Dr["INTERESTAHNAME"]);
objIntrestMasterDto.CaluculationMethod = Convert.ToInt32(Dr["CaluculationMethod"]);
objIntrestMasterDto.Base = Convert.ToInt32(Dr["Base"]);
}
List <InterestRatesDto> lstrates = new List <InterestRatesDto>();
InterestRatesDto rateDto = null;
if (Dr.NextResult())
{
while (Dr.Read())
{
rateDto = new InterestRatesDto();
rateDto.IntrestRateID = Convert.ToInt32(Dr["IntrestRateID"]);
rateDto.ROI = Convert.ToInt32(Dr["ROI"]);
rateDto.PenalROI = Convert.ToInt32(Dr["PenalROI"]);
rateDto.FromDate = Convert.ToDateTime(Dr["FromDate"]);
if (Dr["ToDate"] != DBNull.Value)
{
rateDto.ToDate = Convert.ToDateTime(Dr["ToDate"]);
}
lstrates.Add(rateDto);
}
objIntrestMasterDto.InterestRates = lstrates;
}
return(objIntrestMasterDto);
}