public string Save_SaveProjectFinancials(DL_BOMFinancialReiewModel FS)
{
try
{
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
DataSet ds = new DataSet();
SqlCommand dCmd = new SqlCommand("Insert_ProjectMilestones", conn);
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add(new SqlParameter("@OpportunityID", FS.OpportunityID));
dCmd.Parameters.Add(new SqlParameter("@BOMID", FS.BOMID));
dCmd.Parameters.Add(new SqlParameter("@DepositPerc", FS.DepositPerc));
dCmd.Parameters.Add(new SqlParameter("@Deposit", FS.Deposit));
dCmd.Parameters.Add(new SqlParameter("@PreDeliveryPerc", FS.PreDeliveryPerc));
dCmd.Parameters.Add(new SqlParameter("@PreDelivery", FS.PreDelivery));
dCmd.Parameters.Add(new SqlParameter("@FinalPerc", FS.FinalPerc));
dCmd.Parameters.Add(new SqlParameter("@Final", FS.Final));
conn.Open();
dCmd.ExecuteNonQuery();
conn.Close();
return("");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
public ActionResult ProjectFinancials(int OpportunityID, int BOMID)
{
BOMFinancialReviewModel FM = new BOMFinancialReviewModel();
DL_BOMFinancialReiewModel DFM = new DL_BOMFinancialReiewModel();
QuoteBusinessLogic BL = new QuoteBusinessLogic();
DFM = BL.GetAssemblyForBOMByOpportunityID(OpportunityID, BOMID);
FM = PopulateFinanceReviewViewModel(DFM);
return(PartialView(FM));
}
public ActionResult SaveProjectFinancials(List <BOMFinancialReviewModel> Financials)
{
decimal DepositPerc = Convert.ToDecimal(Financials[0].DepositPerc);
decimal PreDPerc = Convert.ToDecimal(Financials[0].PreDeliveryPerc);
decimal TotalPerc = DepositPerc + PreDPerc;
string Saved = "";
if (TotalPerc > 100)
{
Saved = "Project Milestones Exceeding 100%";
}
else
{
DL_BOMFinancialReiewModel cls = new DL_BOMFinancialReiewModel();
QuoteBusinessLogic BL = new QuoteBusinessLogic();
cls = PopulateFinancialReviewDL(Financials);
Saved = BL.BL_SaveProjectFinancials(cls);
}
return(Json(Saved, JsonRequestBehavior.AllowGet));
}
private DL_BOMFinancialReiewModel PopulateFinancialReviewDL(List <BOMFinancialReviewModel> FR)
{
DL_BOMFinancialReiewModel ret = new DL_BOMFinancialReiewModel();
if (FR == null)
{
return(ret);
}
foreach (var item in FR)
{
ret.BOMID = item.BOMID;
ret.OpportunityID = item.OpportunityID;
ret.Deposit = item.Deposit;
ret.DepositPerc = item.DepositPerc;
ret.PreDelivery = item.PreDelivery;
ret.PreDeliveryPerc = item.PreDeliveryPerc;
ret.Final = item.Final;
ret.FinalPerc = item.FinalPerc;
}
return(ret);
}
private BOMFinancialReviewModel PopulateFinanceReviewViewModel(DL_BOMFinancialReiewModel Assembly)
{
BOMFinancialReviewModel bl = new BOMFinancialReviewModel();
List <FinancialBOMAssemly> fa = new List <FinancialBOMAssemly>();
if (Assembly.BOMAssembly.Count > 0)
{
bl.FinalAgreedPrice = Assembly.FinalAgreedPrice;
bl.BOMTotal = Assembly.BOMTotal;
bl.CTOCerials = Assembly.CTOCerials;
bl.QuoteNo = Assembly.QuoteNo;
bl.Deposit = Assembly.Deposit;
bl.PreDelivery = Assembly.PreDelivery;
bl.Final = Assembly.Final;
bl.PONumber = Assembly.PONumber;
bl.DepositPerc = Assembly.DepositPerc;
bl.PreDeliveryPerc = Assembly.PreDeliveryPerc;
bl.FinalPerc = Assembly.FinalPerc;
bl.BOMID = Assembly.BOMID;
bl.OpportunityID = Assembly.OpportunityID;
foreach (var item in Assembly.BOMAssembly)
{
FinancialBOMAssemly b = new FinancialBOMAssemly();
b.AssemblyCode = item.AssemblyCode;
b.Area = item.Area;
b.Category = item.Category;
b.Device = item.Device;
b.Revenue = item.Revenue;
b.Qty = item.Qty;
fa.Add(b);
}
bl.BOMAssemly = fa;
}
return(bl);
}
public DL_BOMFinancialReiewModel GetAssemblyForBOMByOpportunityID(int OpportunityID, int BOMID)
{
DL_BOMAssembly ba = new DL_BOMAssembly();
DL_BOMFinancialReiewModel fr = new DL_BOMFinancialReiewModel();
fr = this.CustomerDL.GetAssemblyForBOMByOpportunityID(OpportunityID, BOMID);
decimal PMTotalDeductions = 0;
decimal TotalDeductions = 0;
decimal PMRevenue = 0;
decimal NumberOfUnits = 1;
decimal PMPerc = 0;
decimal dmlDeposit = 0;
decimal dmlPreDelivery = 0;
decimal dmlFinal = 0;
foreach (var item in fr.BOMAssembly)
{
if (item.Category == "Capital")
{
NumberOfUnits = item.Qty;
}
}
//Calculate the Total Deductions to calculate the PM Revenue
//Calculate the Total Deductions to calculate the Capital Revenue
if (fr.PMChargableAssemly.Count > 0)
{
foreach (var ch in fr.PMChargableAssemly)
{
foreach (var item in fr.BOMAssembly)
{
if (ch.AssemblyCode == item.AssemblyCode)
{
PMTotalDeductions += (item.Revenue * NumberOfUnits);
item.Revenue = (item.Revenue * NumberOfUnits);
}
if (item.Category != "Capital")
{
TotalDeductions += item.Revenue;
}
}
}
//Calculate the PM Revenue
foreach (var item in fr.BOMAssembly)
{
if (item.PMPercentage > 0)
{
PMPerc = item.PMPercentage;
PMRevenue = (fr.FinalAgreedPrice - PMTotalDeductions) * PMPerc;
item.Revenue = PMRevenue;
TotalDeductions += PMRevenue;
}
}
//Calculate Cpaital Revenue
foreach (var item in fr.BOMAssembly)
{
if (item.Category == "Capital")
{
item.Revenue = fr.FinalAgreedPrice - TotalDeductions;
}
}
}
dmlDeposit = Math.Round((fr.FinalAgreedPrice / 100) * fr.DepositPerc, 2);
dmlPreDelivery = Math.Round((fr.FinalAgreedPrice / 100) * fr.PreDeliveryPerc, 2);
dmlFinal = fr.FinalAgreedPrice - (dmlDeposit + dmlPreDelivery);
fr.Deposit = dmlDeposit;
fr.PreDelivery = dmlPreDelivery;
fr.Final = dmlFinal;
return(fr);
}
public string BL_SaveProjectFinancials(DL_BOMFinancialReiewModel FR)
{
string ret = this.CustomerDL.Save_SaveProjectFinancials(FR);
return(ret);
}
public DL_BOMFinancialReiewModel GetAssemblyForBOMByOpportunityID(int OpportunityID, int BOMID)
{
DL_BOMFinancialReiewModel bfw = new DL_BOMFinancialReiewModel();
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
//return cnn.Query<T>(sql).ToList();
conn.Open();
//------------Get the BOM Assemblies
SqlCommand dCmd = new SqlCommand("Get_AssemblyForBOMByOpportunityID", conn);
dCmd.CommandType = CommandType.StoredProcedure;
dCmd.Parameters.Add(new SqlParameter("@OpportunityID", OpportunityID));
dCmd.Parameters.Add(new SqlParameter("@BOMID", BOMID));
SqlDataAdapter da = new SqlDataAdapter(dCmd);
DataTable dt = new DataTable();
da.Fill(dt);
List <DL_BOMAssembly> lst = new List <DL_BOMAssembly>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
DL_BOMAssembly a = new DL_BOMAssembly();
a.AssemblyCode = dr["AssemblyCode"].ToString();
a.Area = dr["Area"].ToString();
a.Category = dr["Category"].ToString();
a.Device = dr["Device"].ToString();
if (dr["Price"].ToString() != null && dr["Price"].ToString() != "")
{
a.Revenue = Convert.ToDecimal(dr["Price"].ToString());
}
else
{
a.Revenue = 0;
}
if ((dr["Qty"].ToString() != null) && (dr["Qty"].ToString() != ""))
{
a.Qty = Convert.ToDecimal(dr["Qty"].ToString());
}
else
{
a.Qty = 1;
}
a.PMPercentage = Convert.ToDecimal(dr["PMPercentage"].ToString());
bfw.QuoteNo = dr["QuoteNo"].ToString();
lst.Add(a);
}
}
bfw.BOMAssembly = lst;
//------------Get the BOM Totals
SqlCommand Cmd = new SqlCommand("Get_BOMPriceTotalsByOpportunityID", conn);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add(new SqlParameter("@OpportunityID", OpportunityID));
Cmd.Parameters.Add(new SqlParameter("@BOMID", BOMID));
SqlDataAdapter ad = new SqlDataAdapter(Cmd);
DataTable t = new DataTable();
ad.Fill(t);
if (t != null && t.Rows.Count > 0)
{
foreach (DataRow r in t.Rows)
{
bfw.BOMTotal = Convert.ToDecimal(r["TotalPrice"].ToString());
bfw.FinalAgreedPrice = Convert.ToDecimal(r["FinalAgreedPrice"].ToString());
bfw.PONumber = r["PONumber"].ToString();
}
}
//------------Get the BOM Totals
SqlCommand Cm = new SqlCommand("Get_PMFeeNotChargableByBOMID", conn);
Cm.CommandType = CommandType.StoredProcedure;
Cm.Parameters.Add(new SqlParameter("@BOMID", BOMID));
SqlDataAdapter adp = new SqlDataAdapter(Cm);
DataTable tbl = new DataTable();
adp.Fill(tbl);
conn.Close();
List <DL_PMChargableAssemly> pm = new List <DL_PMChargableAssemly>();
if (tbl != null && tbl.Rows.Count > 0)
{
foreach (DataRow dr in tbl.Rows)
{
DL_PMChargableAssemly p = new DL_PMChargableAssemly();
p.AssemblyCode = dr["ITEMID"].ToString();
pm.Add(p);
}
}
bfw.PMChargableAssemly = pm;
//------------Get Project Milestones
SqlCommand CmPM = new SqlCommand("Get_ProjectMilestones", conn);
CmPM.CommandType = CommandType.StoredProcedure;
CmPM.Parameters.Add(new SqlParameter("@OpportunityID", OpportunityID));
CmPM.Parameters.Add(new SqlParameter("@BOMID", BOMID));
SqlDataAdapter adM = new SqlDataAdapter(CmPM);
DataTable tblM = new DataTable();
adM.Fill(tblM);
conn.Close();
if (tblM != null && tblM.Rows.Count > 0)
{
foreach (DataRow dr in tblM.Rows)
{
bfw.Deposit = Convert.ToDecimal(dr["Deposit"].ToString());
bfw.PreDelivery = Convert.ToDecimal(dr["PreDelivery"].ToString());
bfw.Final = Convert.ToDecimal(dr["Final"].ToString());
bfw.DepositPerc = Convert.ToDecimal(dr["DepositPerc"].ToString());
bfw.PreDeliveryPerc = Convert.ToDecimal(dr["PreDeliveryPerc"].ToString());
bfw.FinalPerc = Convert.ToDecimal(dr["FinalPerc"].ToString());
}
}
bfw.BOMID = BOMID;
bfw.OpportunityID = OpportunityID;
return(bfw);
}
}
