public Result Delete(PolicyIssue model)
{
Result res = new Result(false);
try
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
string query = @"DELETE FROM PolicyIssueChequeReceived WHERE PolicyId = @PolicyId;
DELETE FROM PolicyIssue OUTPUT deleted.PolicyId WHERE PolicyId = @PolicyId;";
int id = connection.Query <int>(query, model, txn).First();
txn.Commit();
if (id > 0)
{
return(new Result(true));
}
}
}
catch (Exception ex)
{
return(new Result(false, ex.InnerException == null ? ex.Message : ex.InnerException.Message));
}
return(res);
}
public ActionResult UpdateCommitments(PolicyIssue model)
{
Result res = new PolicyIssueRepository().UpdatePaymentCommitments(model);
if (res.Value)
{
TempData["Success"] = "Saved Successfully!";
}
else
{
}
return(RedirectToAction("Index", new { type = model.PolicyStage }));
}
public ActionResult Delete(PolicyIssue model)
{
Result res = new PolicyIssueRepository().Delete(model);
if (res.Value)
{
TempData["Success"] = "Deleted Successfully!";
}
else
{
}
return(RedirectToAction("Index", new { type = model.PolicyStage }));
}
public Result Update(PolicyIssue model)
{
Result res = new Result(false);
try
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
string sql = @" UPDATE PolicyIssue SET
TranDate=@TranDate
,CusId=@CusId
,InsuredName=@InsuredName
,Address1=@Address1
,Address2=@Address2
,InsCmpId=@InsCmpId
,InsPrdId=@InsPrdId
,PolicySubDate=@PolicySubDate
,EffectiveDate=@EffectiveDate
,RenewalDate=@RenewalDate
,PremiumAmount=@PremiumAmount
,PolicyFee=@PolicyFee
,ExtraPremium=@ExtraPremium
,Totalpremium=@Totalpremium
,CommissionPerc=@CommissionPerc
,CommissionAmount=@CommissionAmount
,TotalCommission=@TotalCommission
,CustContPersonName=@CustContPersonName
,CustContDesignation=@CustContDesignation
,CustContEmail=@CustContEmail
,SalesMgId=@SalesMgId
,CustContMobile=@CustContMobile
WHERE PolicyId=@PolicyId";
int id = connection.Execute(sql, model);
if (id > 0)
{
return(new Result(true));
}
}
}
catch (Exception ex)
{
return(new Result(false, ex.InnerException == null ? ex.Message : ex.InnerException.Message));
}
return(res);
}
// public List<PolicyIssue> GetPaymentCommittedList(int Id,DateTime? FromDate, DateTime? ToDate, string PolicyNo = "", string Client = "", string SalesManager = "")
// {
// using (IDbConnection connection = OpenConnection(dataConnection))
// {
// string query = @"select SalesMgId into #TEMP from [User] U WHERE U.UserId=@Id
// union all
// select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U WHERE U.UserId=@Id )
// union all
// select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U WHERE U.UserId=@Id ))
// union all
// select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U where Reporting in (select SalesMgId from [User] U WHERE U.UserId=@Id )))
//
// select P.PolicyId,Concat(P.TranPrefix,'/',P.TranNumber)StrTranNumber,C.CusName,P.CustContPersonName,P.InsuredName,I.InsCmpName,
// IP.InsPrdName,IC.InsCoverName,P.EffectiveDate,P.RenewalDate,
// P.PremiumAmount,P.ExtraPremium,P.Totalpremium,P.CommissionAmount, S.SalesMgName,P.PolicyNo
// from PolicyIssue P
// left join Customer C on C.CusId = P.CusId
// left join InsuranceCompany I on I.InsCmpId = P.InsCmpId
// left join InsuranceProduct IP on IP.InsPrdId = P.InsPrdId
// left join InsuranceCoverage IC on IC.InsCoverId = P.InsCoverId
// left join SalesManager S on S.SalesMgId = P.SalesMgId
// where P.OldPolicyId IS NULL AND P.TranType='NewPolicy' and isnull(P.SalesMgId,0) IN (SELECT SalesMgId FROM #TEMP)
// AND cast(convert(varchar(20),P.TranDate,106) as datetime) between @FromDate and @ToDate
// AND C.CusName LIKE '%'+@Client+'%'
// AND isnull(P.PolicyNo,0) LIKE '%'+@PolicyNo+'%'
// AND ISNULL(S.SalesMgName,0) LIKE '%'+@SalesManager+'%'
// order by P.CreatedDate desc";
// return connection.Query<PolicyIssue>(query, new {Id = Id, FromDate = FromDate, ToDate = ToDate, PolicyNo = PolicyNo, Client = Client, SalesManager = SalesManager }).ToList();
// }
// }
// public List<PolicyIssue> GetPaymentCollectionList(DateTime? FromDate, DateTime? ToDate, string PolicyNo = "", string Client = "", string SalesManager = "")
// {
// using (IDbConnection connection = OpenConnection(dataConnection))
// {
// string query = @"select P.PolicyId,Concat(P.TranPrefix,'/',P.TranNumber)StrTranNumber,C.CusName,P.CustContPersonName,P.InsuredName,I.InsCmpName,IP.InsPrdName,IC.InsCoverName,P.EffectiveDate,P.RenewalDate,
// P.PremiumAmount,P.ExtraPremium,P.Totalpremium,P.CommissionAmount, S.SalesMgName,P.PolicyNo
// from PolicyIssue P
// left join Customer C on C.CusId = P.CusId
// left join InsuranceCompany I on I.InsCmpId = P.InsCmpId
// left join InsuranceProduct IP on IP.InsPrdId = P.InsPrdId
// left join InsuranceCoverage IC on IC.InsCoverId = P.InsCoverId
// left join SalesManager S on S.SalesMgId = P.SalesMgId
// where P.OldPolicyId IS NULL AND P.TranType='NewPolicy'
// AND cast(convert(varchar(20),P.TranDate,106) as datetime) between @FromDate and @ToDate
// AND C.CusName LIKE '%'+@Client+'%'
// AND isnull(P.PolicyNo,0) LIKE '%'+@PolicyNo+'%'
// AND ISNULL(S.SalesMgName,0) LIKE '%'+@SalesManager+'%'
// order by P.CreatedDate";
// return connection.Query<PolicyIssue>(query, new { FromDate = FromDate, ToDate = ToDate, PolicyNo = PolicyNo, Client = Client, SalesManager = SalesManager }).ToList();
// }
// }
public Result Insert(PolicyIssue model)
{
Result res = new Result(false);
try
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
model.TranNumber = PolicyIssueRepository.GetNextDocNo(model.TranType);
string sql = @"INSERT INTO PolicyIssue
(TranPrefix,TranNumber,TranDate,CusId,InsuredName,Address1,Address2,InsCmpId,InsPrdId,InsCoverId,PolicySubDate,EffectiveDate,RenewalDate,
PremiumAmount,PolicyFee,ExtraPremium,Totalpremium,CommissionPerc,CommissionAmount,CustContPersonName,CustContDesignation,CustContEmail,CustContMobile,CustContOfficeNo,
PaymentOption,SalesMgId,OperationManager,PolicyNo,Remarks,FinanceManager,PaymentTo,PayModeId,OldPolicyId,CIBEffectiveDate,AdditionEmpNo,
DeletionEmpNo,EndorcementTypeId,TranType,OldPolicyNo,OldCompany,OldProductType,OldPremiumAmt,CreatedBy,CreatedDate,ICActualDate,TotalCommission,PolicyStage)
VALUES
(@TranPrefix,@TranNumber,@TranDate,@CusId,@InsuredName,@Address1,@Address2,@InsCmpId,@InsPrdId,@InsCoverId,@PolicySubDate,@EffectiveDate,@RenewalDate,
@PremiumAmount,@PolicyFee,@ExtraPremium,@Totalpremium,@CommissionPerc,@CommissionAmount,@CustContPersonName,@CustContDesignation,@CustContEmail,@CustContMobile,@CustContOfficeNo,
@PaymentOption,@SalesMgId,@OperationManager,@PolicyNo,@Remarks,@FinanceManager,@PaymentTo,@PayModeId,@OldPolicyId,@CIBEffectiveDate,@AdditionEmpNo,
@DeletionEmpNo,@EndorcementTypeId,@TranType,@OldPolicyNo,@OldCompany,@OldProductType,@OldPremiumAmt,@CreatedBy,@CreatedDate,@ICActualDate,@TotalCommission,@PolicyStage);
SELECT CAST(SCOPE_IDENTITY() as int);";
model.PolicyId = connection.Query <int>(sql, model).Single();
// foreach (var item in model.Cheque)
// {
// item.PolicyId = model.PolicyId;
// sql = @"INSERT INTO PolicyIssueChequeReceived
// (PolicyId,ChequeNo,ChequeDate,BankName
// ,BankBranch,ChequeAmt )VALUES(@PolicyId,@ChequeNo,@ChequeDate,@BankName,@BankBranch,@ChequeAmt);SELECT CAST(SCOPE_IDENTITY() as int);";
// }
// int id = connection.Execute(sql, model.Cheque);
if (model.PolicyId > 0)
{
return(new Result(true));
}
}
}
catch (Exception ex)
{
return(new Result(false, ex.InnerException == null ? ex.Message : ex.InnerException.Message));
}
return(res);
}
public ActionResult Edit(PolicyIssue model)
{
model.TranDate = System.DateTime.Now;
if (!ModelState.IsValid)
{
var allErrors = ModelState.Values.SelectMany(v => v.Errors);
return(View(model));
}
Result res = new PolicyIssueRepository().Update(model);
if (res.Value)
{
TempData["Success"] = "Updated Successfully!";
}
else
{
}
return(RedirectToAction("Index", new { type = 1 }));
}
public ActionResult Create(int?type)
{
//ViewBag.Type = type;
FillDropdowns();
PolicyIssue model = new PolicyIssue();
model.TranType = "NewPolicy";
model.PolicyStage = type;
var internalid = PolicyIssueRepository.GetNextDocNo(model.TranType);
model.TranNumber = "CIB/NEW/" + internalid;
model.Cheque = new List <PolicyIssueChequeReceived>();
model.Cheque.Add(new PolicyIssueChequeReceived());
model.PolicySubDate = DateTime.Now;
model.EffectiveDate = DateTime.Now;
// model.RenewalDate = DateTime.Now.AddMonths(6);
model.ICActualDate = null;
return(View(model));
}
public ActionResult Create(PolicyIssue model)
{
model.TranPrefix = "CIB/REN";
model.TranDate = System.DateTime.Now;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID;
if (!ModelState.IsValid)
{
var allErrors = ModelState.Values.SelectMany(v => v.Errors);
return(View(model));
}
Result res = new PolicyRenewalRepository().Insert(model);
if (res.Value)
{
TempData["Success"] = "Saved Successfully!";
}
else
{
}
return(RedirectToAction("Index"));
}
public ActionResult Create(PolicyIssue model)
{
model.TranPrefix = model.TranNumber.Substring(0, model.TranNumber.LastIndexOf('/'));
model.TranDate = System.DateTime.Now;
model.CreatedDate = System.DateTime.Now;
model.CreatedBy = UserID;
if (!ModelState.IsValid)
{
FillDropdowns();
var allErrors = ModelState.Values.SelectMany(v => v.Errors);
return(View(model));
}
Result res = new PolicyIssueRepository().Insert(model);
if (res.Value)
{
TempData["Success"] = "Saved Successfully!";
}
else
{
}
return(RedirectToAction("Index", new { type = model.PolicyStage }));
}
public Result UpdatePaymentCommitments(PolicyIssue model)
{
Result res = new Result(false);
try
{
using (IDbConnection connection = OpenConnection(dataConnection))
{
IDbTransaction txn = connection.BeginTransaction();
string sql;
int id = 0;
if (model.PolicyStage == 3)
{
sql = @"UPDATE PolicyIssue SET PaymentTo=@PaymentTo,PayModeId=@PayModeId,PolicyStage=@PolicyStage WHERE PolicyId = @PolicyId
DELETE FROM PolicyIssueChequeReceived WHERE PolicyId = @PolicyId";
id = connection.Execute(sql, model, txn);
foreach (var item in model.Cheque)
{
item.PolicyId = model.PolicyId;
sql = @"INSERT INTO PolicyIssueChequeReceived
(PolicyId,ChequeNo,ChequeDate,BankName
,BankBranch,ChequeAmt,QuickBookRefNo )VALUES(@PolicyId,@ChequeNo,@ChequeDate,@BankName,@BankBranch,@ChequeAmt,@QuickBookRefNo);SELECT CAST(SCOPE_IDENTITY() as int);";
id = connection.Execute(sql, item, txn);
}
foreach (var item in model.Committed)
{
if (item.paid == true)
{
item.PolicyId = model.PolicyId;
sql = sql = @"UPDATE PolicyIssueCommittedDetails set paid=1 WHERE CommRowId = @CommRowId";
id = connection.Execute(sql, item, txn);
}
if (item.InsPaid == true)
{
item.PolicyId = model.PolicyId;
sql = sql = @"UPDATE PolicyIssueCommittedDetails set InsPaid =1 WHERE CommRowId = @CommRowId";
id = connection.Execute(sql, item, txn);
}
if (item.paid == false)
{
item.PolicyId = model.PolicyId;
sql = sql = @"UPDATE PolicyIssueCommittedDetails set paid=0 WHERE CommRowId = @CommRowId";
id = connection.Execute(sql, item, txn);
}
if (item.InsPaid == false)
{
item.PolicyId = model.PolicyId;
sql = sql = @"UPDATE PolicyIssueCommittedDetails set InsPaid =0 WHERE CommRowId = @CommRowId";
id = connection.Execute(sql, item, txn);
}
}
}
else
{
sql = @"UPDATE PolicyIssue SET PolicyNo=@PolicyNo,Remarks=@Remarks,PolicyStage=@PolicyStage WHERE PolicyId = @PolicyId
DELETE FROM PolicyIssueCommittedDetails WHERE PolicyId = @PolicyId";
connection.Execute(sql, model, txn);
foreach (var item in model.Committed)
{
item.PolicyId = model.PolicyId;
if (item.CommittedDate != null)
{
sql = @"INSERT INTO PolicyIssueCommittedDetails
(PolicyId,CommittedDate
,CommittedAmt,paid,InsPaid)VALUES(@PolicyId,@CommittedDate,@CommittedAmt,@paid,@InsPaid);SELECT CAST(SCOPE_IDENTITY() as int);";
id = connection.Execute(sql, item, txn);
}
}
}
txn.Commit();
if (id > 0)
{
return(new Result(true));
}
}
}
catch (Exception ex)
{
return(new Result(false, ex.InnerException == null ? ex.Message : ex.InnerException.Message));
}
return(res);
}
