private static String GetPRMailSubject(WoWiModel.WoWiEntities wowidb, WoWiModel.PR_authority_history auth)
{
return GetPRMailSubject(wowidb, auth, "request for approval");
}
private static String GetPRMailSubject(WoWiModel.WoWiEntities wowidb, WoWiModel.PR_authority_history auth, String message)
{
int prid = auth.pr_id;
WoWiModel.PR obj = (from p in wowidb.PRs where p.pr_id == prid select p).First();
int tid = wowidb.PR_item.First(c => c.pr_id == obj.pr_id).quotation_target_id;
WoWiModel.vendor ven = (from v in wowidb.vendors where v.id == obj.vendor_id select v).First();
String venderName = String.IsNullOrEmpty(ven.name) ? ven.c_name : ven.name;
//String quotaion_no = (from d in wowidb.Quotation_Version where d.Quotation_Version_Id == obj.quotaion_id select d.Quotation_No).First();
//var list = from q in wowidb.Quotation_Version
// where q.Quotation_No.Equals(quotaion_no)
// select new
// {
// No = q.Quotation_No,
// Version = q.Vername,
// Id = q.Quotation_Version_Id
// };
//var data = (from t in wowidb.Target_Rates from qt in wowidb.Quotation_Target from q in list where qt.quotation_id == q.Id & qt.target_id == t.Target_rate_id select new { Code =11, Desc = qt.target_description }).First();
var data = (from t in wowidb.Target_Rates from q in wowidb.Quotation_Version from qt in wowidb.Quotation_Target where qt.Quotation_Target_Id == tid & qt.target_id == t.Target_rate_id && q.Quotation_Version_Id == obj.quotaion_id select new { TargetName = ((from c in wowidb.Authorities where c.authority_id == qt.authority_id select c.authority_name).FirstOrDefault()), Quotation_Target_Id = qt.Quotation_Target_Id, ItemDescription = qt.target_description, ModelNo = q.Model_No, Qty = qt.unit }).First();
String templateStr = "PR No.#{0} / {1} / {2} / {3} is " + message;
return String.Format(templateStr, prid, venderName, data.ItemDescription, data.TargetName);
}
public static void PRStatusDone(WoWiModel.WoWiEntities wowidb, WoWiModel.PR_authority_history auth)
{
try
{
string mailSubject = GetPRMailSubject(wowidb, auth, "approved. PR Status is : " + statusByteToString((byte)auth.status));
string sender = "<br />" + PRApproval_URL + auth.pr_id;
string mailContent = mailSubject + sender;
string to = GetEmail((int)auth.requisitioner_id);
if (to != null)
{
if (auth.finance_id.HasValue)
{
//string cc = GetEmail((int)auth.finance_id);
//Mail(new String[] { to }, new String[] { cc }, mailSubject, mailContent);
//2014/1/14 : PR修改為要傳送MAIL給所有會計部門的人
var finances =
(from e in wowidb.employees
from jt in wowidb.employee_jobtitle
where (jt.jobtitle_name.Trim().Equals("Finance") || jt.jobtitle_name.Trim().Equals("Accounting"))
& e.jobtitle_id == jt.jobtitle_id
select new { e.email });
string[] cc = finances.Select(f => f.email).ToArray();
Mail(new String[] { to }, cc, mailSubject, mailContent);
}
else
{
Mail(new String[] { to }, null, mailSubject, mailContent);
}
}
}
catch (Exception)
{
//throw;
}
}
public static void WaitingForVPApprove(WoWiModel.WoWiEntities wowidb, WoWiModel.PR_authority_history auth)
{
try
{
string mailSubject = GetPRMailSubject(wowidb, auth, " Waiting For VP Approve");
string sender = auth.supervisor + "<br />" + PRApproval_URL + auth.pr_id;
string mailContent = GetPRMailContent(mailSubject, sender);
string to = GetEmail((int)auth.vp_id);
if (to != null)
{
string cc1 = GetEmail((int)auth.requisitioner_id);
string cc2 = GetEmail((int)auth.supervisor_id);
Mail(new String[] { to }, new String[] { cc1, cc2 }, mailSubject, mailContent);
}
}
catch (Exception)
{
//throw;
}
}
public static List<SalesClientSeasonReportData> GetSalesClientBySeason_OpenProjDate(WoWiModel.WoWiEntities wowidb, int year)
{
List<SalesClientSeasonReportData> list = new List<SalesClientSeasonReportData>();
SalesClientSeasonReportData temp;
try
{
//Find all Sales
var sales = from e in wowidb.employees from dep in wowidb.departments where e.department_id == dep.id && dep.name == "Sales" select e;
var quos = (from quo in wowidb.Quotation_Version from pj in wowidb.Projects where quo.Quotation_Version_Id == pj.Quotation_Id && pj.Create_Date.Year == year select quo.Quotation_No).Distinct();
var clients = (from qNo in quos from quo in wowidb.Quotation_Version from cli in wowidb.clientapplicants where quo.Quotation_No == qNo select quo.Client_Id).Distinct();
decimal M1D = 0, M1Q = 0, M2D = 0, M2Q = 0, M3D = 0, M3Q = 0, M4D = 0, M4Q = 0, TotD = 0, TotQ = 0;
foreach (var sale in sales)
{
decimal imaM1D = 0, imaM1Q = 0, imaM2D = 0, imaM2Q = 0, imaM3D = 0, imaM3Q = 0, imaM4D = 0, imaM4Q = 0, imaTotD = 0, imaTotQ = 0;
foreach (var client in clients)
{
decimal venM1D = 0, venM1Q = 0, venM2D = 0, venM2Q = 0, venM3D = 0, venM3Q = 0, venM4D = 0, venM4Q = 0, venTotD = 0, venTotQ = 0;
try
{
var quotas = from quo in wowidb.Quotation_Version where quo.SalesId == sale.id && quo.Client_Id == client select quo;
temp = new SalesClientSeasonReportData()
{
Sales = String.IsNullOrEmpty(sale.c_fname) ? sale.fname + " " + sale.lname : sale.c_lname + "" + sale.c_fname
};
var v = (from i in wowidb.clientapplicants where i.id == client select i).First();
temp.Client = String.IsNullOrEmpty(v.c_companyname) ? v.companyname : v.c_companyname;
foreach (var p in quotas)
{
try
{
int month = (from qs in wowidb.Quotation_Version from proj in wowidb.Projects where qs.Quotation_No == p.Quotation_No && qs.Quotation_Version_Id == proj.Quotation_Id select proj.Create_Date.Month).First();
switch (month)
{
case 1:
case 2:
case 3:
venM1D += (decimal)p.FinalTotalPrice;
//venM1Q += (decimal)pri.quantity;
break;
case 4:
case 5:
case 6:
venM2D += (decimal)p.FinalTotalPrice;
//venM2Q += (decimal)pri.quantity;
break;
case 7:
case 8:
case 9:
venM3D += (decimal)p.FinalTotalPrice;
//venM3Q += (decimal)pri.quantity;
break;
case 10:
case 11:
case 12:
venM4D += (decimal)p.FinalTotalPrice;
//venM4Q += (decimal)pri.quantity;
break;
}
}
catch (Exception)
{
//throw;
}
}//Single vender ends
venTotD = venM1D + venM2D + venM3D + venM4D;
//venTotQ = venM1Q + venM2Q + venM3Q + venM4Q;
imaM1D += venM1D;
//imaM1Q += venM1Q;
imaM2D += venM2D;
//imaM2Q += venM2Q;
imaM3D += venM3D;
//imaM3Q += venM3Q;
imaM4D += venM4D;
//imaM4Q += venM4Q;
imaTotD += venTotD;
//imaTotQ += venTotQ;
temp.Season01USD = venM1D.ToString("F2");
temp.Season02USD = venM2D.ToString("F2");
temp.Season03USD = venM3D.ToString("F2");
temp.Season04USD = venM4D.ToString("F2");
temp.TotalUSD = venTotD.ToString("F2");
//temp.Season01QTY = venM1Q.ToString("F0");
//temp.Season02QTY = venM2Q.ToString("F0");
//temp.Season03QTY = venM3Q.ToString("F0");
//temp.Season04QTY = venM4Q.ToString("F0");
//temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (imaTotD != 0)
{
temp = new SalesClientSeasonReportData()
{
Sales = (String.IsNullOrEmpty(sale.c_fname) ? sale.fname + " " + sale.lname : sale.c_lname + "" + sale.c_fname) + " Total : "
};
M1D += imaM1D;
//M1Q += imaM1Q;
M2D += imaM2D;
//M2Q += imaM2Q;
M3D += imaM3D;
//M3Q += imaM3Q;
M4D += imaM4D;
//M4Q += imaM4Q;
TotD += imaTotD;
//TotQ += imaTotQ;
temp.Season01USD = imaM1D.ToString("F2");
temp.Season02USD = imaM2D.ToString("F2");
temp.Season03USD = imaM3D.ToString("F2");
temp.Season04USD = imaM4D.ToString("F2");
temp.TotalUSD = imaTotD.ToString("F2");
//temp.Season01QTY = imaM1Q.ToString("F0");
//temp.Season02QTY = imaM2Q.ToString("F0");
//temp.Season03QTY = imaM3Q.ToString("F0");
//temp.Season04QTY = imaM4Q.ToString("F0");
//temp.TotalQTY = imaTotQ.ToString("F0");
list.Add(temp);
}
}//ima ends
if (TotD != 0)
{
temp = new SalesClientSeasonReportData()
{
Sales = "Balance Total : "
};
temp.Season01USD = M1D.ToString("F2");
temp.Season02USD = M2D.ToString("F2");
temp.Season03USD = M3D.ToString("F2");
temp.Season04USD = M4D.ToString("F2");
temp.TotalUSD = TotD.ToString("F2");
//temp.Season01QTY = M1Q.ToString("F0");
//temp.Season02QTY = M2Q.ToString("F0");
//temp.Season03QTY = M3Q.ToString("F0");
//temp.Season04QTY = M4Q.ToString("F0");
//temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<SalesClientMonthReportData> GetClientByMonth_OpenProjDate(WoWiModel.WoWiEntities wowidb, int year)
{
List<SalesClientMonthReportData> list = new List<SalesClientMonthReportData>();
SalesClientMonthReportData temp;
try
{
var quos = (from quo in wowidb.Quotation_Version from pj in wowidb.Projects where quo.Quotation_Version_Id == pj.Quotation_Id && pj.Create_Date.Year == year select quo.Quotation_No).Distinct();
var clients = (from qNo in quos from quo in wowidb.Quotation_Version from cli in wowidb.clientapplicants where quo.Quotation_No == qNo select quo.Client_Id).Distinct();
decimal imaM1D = 0, imaM1Q = 0, imaM2D = 0, imaM2Q = 0, imaM3D = 0, imaM3Q = 0, imaM4D = 0, imaM4Q = 0, imaM5D = 0, imaM5Q = 0, imaM6D = 0, imaM6Q = 0, imaM7D = 0, imaM7Q = 0, imaM8D = 0, imaM8Q = 0, imaM9D = 0, imaM9Q = 0, imaM10D = 0, imaM10Q = 0, imaM11D = 0, imaM11Q = 0, imaM12D = 0, imaM12Q = 0, imaTotD = 0, imaTotQ = 0;
foreach (var client in clients)
{
decimal venM1D = 0, venM1Q = 0, venM2D = 0, venM2Q = 0, venM3D = 0, venM3Q = 0, venM4D = 0, venM4Q = 0, venM5D = 0, venM5Q = 0, venM6D = 0, venM6Q = 0, venM7D = 0, venM7Q = 0, venM8D = 0, venM8Q = 0, venM9D = 0, venM9Q = 0, venM10D = 0, venM10Q = 0, venM11D = 0, venM11Q = 0, venM12D = 0, venM12Q = 0, venTotD = 0, venTotQ = 0;
try
{
var quotas = from quo in wowidb.Quotation_Version where quo.Client_Id == client select quo;
temp = new SalesClientMonthReportData();
var v = (from i in wowidb.clientapplicants where i.id == client select i).First();
temp.Client = String.IsNullOrEmpty(v.c_companyname) ? v.companyname : v.c_companyname;
foreach (var p in quotas)
{
try
{
int month = (from qs in wowidb.Quotation_Version from proj in wowidb.Projects where qs.Quotation_No == p.Quotation_No && qs.Quotation_Version_Id == proj.Quotation_Id select proj.Create_Date.Month).First();
switch (month)
{
case 1:
venM1D += (decimal)p.FinalTotalPrice;
//venM1Q += (decimal)p.quantity;
break;
case 2:
venM2D += (decimal)p.FinalTotalPrice;
//venM2Q += (decimal)pri.quantity;
break;
case 3:
venM3D += (decimal)p.FinalTotalPrice;
//venM3Q += (decimal)pri.quantity;
break;
case 4:
venM4D += (decimal)p.FinalTotalPrice;
//venM4Q += (decimal)pri.quantity;
break;
case 5:
venM5D += (decimal)p.FinalTotalPrice;
//venM5Q += (decimal)pri.quantity;
break;
case 6:
venM6D += (decimal)p.FinalTotalPrice;
//venM6Q += (decimal)pri.quantity;
break;
case 7:
venM7D += (decimal)p.FinalTotalPrice;
//venM7Q += (decimal)pri.quantity;
break;
case 8:
venM8D += (decimal)p.FinalTotalPrice;
//venM8Q += (decimal)pri.quantity;
break;
case 9:
venM9D += (decimal)p.FinalTotalPrice;
//venM9Q += (decimal)pri.quantity;
break;
case 10:
venM10D += (decimal)p.FinalTotalPrice;
//venM10Q += (decimal)pri.quantity;
break;
case 11:
venM11D += (decimal)p.FinalTotalPrice;
//venM11Q += (decimal)pri.quantity;
break;
case 12:
venM12D += (decimal)p.FinalTotalPrice;
//venM12Q += (decimal)pri.quantity;
break;
}
}
catch (Exception)
{
//throw;
}
}//Single vender ends
venTotD = venM1D + venM2D + venM3D + venM4D + venM5D + venM6D + venM7D + venM8D + venM9D + venM10D + venM11D + venM12D;
//venTotQ = venM1Q + venM2Q + venM3Q + venM4Q + venM5Q + venM6Q + venM7Q + venM8Q + venM9Q + venM10Q + venM11Q + venM12Q;
imaM1D += venM1D;
//imaM1Q += venM1Q;
imaM2D += venM2D;
//imaM2Q += venM2Q;
imaM3D += venM3D;
//imaM3Q += venM3Q;
imaM4D += venM4D;
//imaM4Q += venM4Q;
imaM5D += venM5D;
//imaM5Q += venM5Q;
imaM6D += venM6D;
//imaM6Q += venM6Q;
imaM7D += venM7D;
//imaM7Q += venM7Q;
imaM8D += venM8D;
//imaM8Q += venM8Q;
imaM9D += venM9D;
//imaM9Q += venM9Q;
imaM10D += venM10D;
//imaM10Q += venM10Q;
imaM11D += venM11D;
//imaM11Q += venM11Q;
imaM12D += venM12D;
//imaM12Q += venM12Q;
imaTotD += venTotD;
//imaTotQ += venTotQ;
temp.Month01USD = venM1D.ToString("F2");
temp.Month02USD = venM2D.ToString("F2");
temp.Month03USD = venM3D.ToString("F2");
temp.Month04USD = venM4D.ToString("F2");
temp.Month05USD = venM5D.ToString("F2");
temp.Month06USD = venM6D.ToString("F2");
temp.Month07USD = venM7D.ToString("F2");
temp.Month08USD = venM8D.ToString("F2");
temp.Month09USD = venM9D.ToString("F2");
temp.Month10USD = venM10D.ToString("F2");
temp.Month11USD = venM11D.ToString("F2");
temp.Month12USD = venM12D.ToString("F2");
temp.TotalUSD = venTotD.ToString("F2");
//temp.Month01QTY = venM1Q.ToString("F0");
//temp.Month02QTY = venM2Q.ToString("F0");
//temp.Month03QTY = venM3Q.ToString("F0");
//temp.Month04QTY = venM4Q.ToString("F0");
//temp.Month05QTY = venM5Q.ToString("F0");
//temp.Month06QTY = venM6Q.ToString("F0");
//temp.Month07QTY = venM7Q.ToString("F0");
//temp.Month08QTY = venM8Q.ToString("F0");
//temp.Month09QTY = venM9Q.ToString("F0");
//temp.Month10QTY = venM10Q.ToString("F0");
//temp.Month11QTY = venM11Q.ToString("F0");
//temp.Month12QTY = venM12Q.ToString("F0");
//temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//ima ends
if (imaTotD != 0)
{
temp = new SalesClientMonthReportData()
{
Client = "Balance Total : "
};
temp.Month01USD = imaM1D.ToString("F2");
temp.Month02USD = imaM2D.ToString("F2");
temp.Month03USD = imaM3D.ToString("F2");
temp.Month04USD = imaM4D.ToString("F2");
temp.Month05USD = imaM5D.ToString("F2");
temp.Month06USD = imaM6D.ToString("F2");
temp.Month07USD = imaM7D.ToString("F2");
temp.Month08USD = imaM8D.ToString("F2");
temp.Month09USD = imaM9D.ToString("F2");
temp.Month10USD = imaM10D.ToString("F2");
temp.Month11USD = imaM11D.ToString("F2");
temp.Month12USD = imaM12D.ToString("F2");
temp.TotalUSD = imaTotD.ToString("F2");
//temp.Month01QTY = imaM1Q.ToString("F0");
//temp.Month02QTY = imaM2Q.ToString("F0");
//temp.Month03QTY = imaM3Q.ToString("F0");
//temp.Month04QTY = imaM4Q.ToString("F0");
//temp.Month05QTY = imaM5Q.ToString("F0");
//temp.Month06QTY = imaM6Q.ToString("F0");
//temp.Month07QTY = imaM7Q.ToString("F0");
//temp.Month08QTY = imaM8Q.ToString("F0");
//temp.Month09QTY = imaM9Q.ToString("F0");
//temp.Month10QTY = imaM10Q.ToString("F0");
//temp.Month11QTY = imaM11Q.ToString("F0");
//temp.Month12QTY = imaM12Q.ToString("F0");
//temp.TotalQTY = imaTotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<IMAVenderCostSeasonReportData> GetVenderClientCostBySeason(WoWiModel.WoWiEntities wowidb, int year)
{
List<IMAVenderCostSeasonReportData> list = new List<IMAVenderCostSeasonReportData>();
IMAVenderCostSeasonReportData temp;
try
{
var venders = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date.Year == year select pr.vendor_id).Distinct();
decimal M1D = 0, M1Q = 0, M2D = 0, M2Q = 0, M3D = 0, M3Q = 0, M4D = 0, M4Q = 0, TotD = 0, TotQ = 0;
foreach (var ven in venders)
{
try
{
String vName = "";
var prs = from pr in wowidb.PRs where pr.vendor_id == ven select pr;
temp = new IMAVenderCostSeasonReportData();
var v = (from i in wowidb.vendors where i.id == ven select i).First();
temp.VenderName = String.IsNullOrEmpty(v.c_name) ? v.name : v.c_name;
vName = temp.VenderName;
decimal venM1D = 0, venM1Q = 0, venM2D = 0, venM2Q = 0, venM3D = 0, venM3Q = 0, venM4D = 0, venM4Q = 0, venTotD = 0, venTotQ = 0;
foreach (var p in prs)
{
try
{
var pj = (from proj in wowidb.Projects where proj.Project_Id == p.project_id select proj).First();
int month = pj.Create_Date.Month;
var quo = (from q in wowidb.Quotation_Version where q.Quotation_Version_Id == pj.Quotation_Id select q).First();
temp.Model = quo.Model_No;
int cid = (int)quo.Client_Id;
try
{
var client = (from cli in wowidb.clientapplicants where cli.id == cid select cli).First();
temp.Client = String.IsNullOrEmpty(client.c_companyname) ? client.companyname : client.c_companyname;
}
catch
{
}
var pri = (from pi in wowidb.PR_item where pi.pr_id == p.pr_id select pi).First();
try
{
//var tDesc = (from kk in wowidb.Quotation_Target from kkk in wowidb.Targets where pri.quotation_target_id == kk.Quotation_Target_Id && kk.target_id == kkk.target_id select kkk.target_description).First();
var tDesc = (from kk in wowidb.Quotation_Target where pri.quotation_target_id == kk.Quotation_Target_Id select kk.target_description).First();
temp.Country = tDesc;
}
catch (Exception)
{
}
decimal avenM1D = 0, avenM1Q = 0, avenM2D = 0, avenM2Q = 0, avenM3D = 0, avenM3Q = 0, avenM4D = 0, avenM4Q = 0, avenTotD = 0, avenTotQ = 0;
switch (month)
{
case 1:
case 2:
case 3:
avenM1D = (decimal)pri.amount;
avenM1Q = (decimal)pri.quantity;
break;
case 4:
case 5:
case 6:
avenM2D = (decimal)pri.amount;
avenM2Q = (decimal)pri.quantity;
break;
case 7:
case 8:
case 9:
avenM3D = (decimal)pri.amount;
avenM3Q = (decimal)pri.quantity;
break;
case 10:
case 11:
case 12:
avenM4D = (decimal)pri.amount;
avenM4Q = (decimal)pri.quantity;
break;
}
avenTotD = avenM1D + avenM2D + avenM3D + avenM4D ;
avenTotQ = avenM1Q + avenM2Q + avenM3Q + avenM4Q ;
venM1D += avenM1D;
venM1Q += avenM1Q;
venM2D += avenM2D;
venM2Q += avenM2Q;
venM3D += avenM3D;
venM3Q += avenM3Q;
venM4D += avenM4D;
venM4Q += avenM4Q;
venTotD += avenTotD;
venTotQ += avenTotQ;
temp.Season01USD = M1D.ToString("F2");
temp.Season02USD = M2D.ToString("F2");
temp.Season03USD = M3D.ToString("F2");
temp.Season04USD = M4D.ToString("F2");
temp.TotalUSD = avenTotD.ToString("F2");
temp.Season01QTY = M1Q.ToString("F0");
temp.Season02QTY = M2Q.ToString("F0");
temp.Season03QTY = M3Q.ToString("F0");
temp.Season04QTY = M4Q.ToString("F0");
temp.TotalQTY = avenTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Single vender ends
M1D += venM1D;
M1Q += venM1Q;
M2D += venM2D;
M2Q += venM2Q;
M3D += venM3D;
M3Q += venM3Q;
M4D += venM4D;
M4Q += venM4Q;
TotD += venTotD;
TotQ += venTotQ;
if (venTotD != 0)
{
temp = new IMAVenderCostSeasonReportData()
{
VenderName = " Total : "
};
temp.Season01USD = M1D.ToString("F2");
temp.Season02USD = M2D.ToString("F2");
temp.Season03USD = M3D.ToString("F2");
temp.Season04USD = M4D.ToString("F2");
temp.TotalUSD = venTotD.ToString("F2");
temp.Season01QTY = M1Q.ToString("F0");
temp.Season02QTY = M2Q.ToString("F0");
temp.Season03QTY = M3Q.ToString("F0");
temp.Season04QTY = M4Q.ToString("F0");
temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (TotD != 0)
{
temp = new IMAVenderCostSeasonReportData()
{
VenderName = "Balance Total : "
};
temp.Season01USD = M1D.ToString("F2");
temp.Season02USD = M2D.ToString("F2");
temp.Season03USD = M3D.ToString("F2");
temp.Season04USD = M4D.ToString("F2");
temp.TotalUSD = TotD.ToString("F2");
temp.Season01QTY = M1Q.ToString("F0");
temp.Season02QTY = M2Q.ToString("F0");
temp.Season03QTY = M3Q.ToString("F0");
temp.Season04QTY = M4Q.ToString("F0");
temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<SalesClientIntervalReportData> GetSalesClientByInterval_OpenProjDate(WoWiModel.WoWiEntities wowidb, DateTime f, DateTime t)
{
List<SalesClientIntervalReportData> list = new List<SalesClientIntervalReportData>();
SalesClientIntervalReportData temp;
try
{
//Find all Sales
var sales = from e in wowidb.employees from dep in wowidb.departments where e.department_id == dep.id && dep.name == "Sales" select e;
var quos = (from quo in wowidb.Quotation_Version from pj in wowidb.Projects where quo.Quotation_Version_Id == pj.Quotation_Id && pj.Create_Date >= f && pj.Create_Date <= t select quo.Quotation_No).Distinct();
var clients = (from qNo in quos from quo in wowidb.Quotation_Version from cli in wowidb.clientapplicants where quo.Quotation_No == qNo select quo.Client_Id).Distinct();
decimal TotD = 0, TotQ = 0;
foreach (var sale in sales)
{
decimal imaTotD = 0, imaTotQ = 0;
foreach (var client in clients)
{
decimal venTotD = 0, venTotQ = 0;
try
{
var quotas = from quo in wowidb.Quotation_Version where quo.SalesId == sale.id && quo.Client_Id == client select quo;
temp = new SalesClientIntervalReportData()
{
Sales = String.IsNullOrEmpty(sale.c_fname) ? sale.fname + " " + sale.lname : sale.c_lname + "" + sale.c_fname
};
var v = (from i in wowidb.clientapplicants where i.id == client select i).First();
temp.Client = String.IsNullOrEmpty(v.c_companyname) ? v.companyname : v.c_companyname;
foreach (var p in quotas)
{
try
{
venTotD += (decimal)p.FinalTotalPrice;
//venTotQ += (decimal)pri.quantity;
}
catch (Exception)
{
//throw;
}
}//Single vender ends
imaTotD += venTotD;
//imaTotQ += venTotQ;
temp.TotalUSD = venTotD.ToString("F2");
//temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (imaTotD != 0)
{
temp = new SalesClientIntervalReportData()
{
Sales = (String.IsNullOrEmpty(sale.c_fname) ? sale.fname + " " + sale.lname : sale.c_lname + "" + sale.c_fname) + " Total : "
};
TotD += imaTotD;
//TotQ += imaTotQ;
temp.TotalUSD = imaTotD.ToString("F2");
//temp.TotalQTY = imaTotQ.ToString("F0");
list.Add(temp);
}
}//ima ends
if (TotD != 0)
{
temp = new SalesClientIntervalReportData()
{
Sales = "Balance Total : "
};
temp.TotalUSD = TotD.ToString("F2");
//temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<IMAVenderCostIntervalReportData> GetVenderClientCostByInterval(WoWiModel.WoWiEntities wowidb, DateTime f, DateTime t)
{
List<IMAVenderCostIntervalReportData> list = new List<IMAVenderCostIntervalReportData>();
IMAVenderCostIntervalReportData temp;
try
{
var venders = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date >= f && pj.Create_Date <= t select pr.vendor_id).Distinct();
decimal TotD = 0, TotQ = 0;
foreach (var ven in venders)
{
decimal venTotD = 0, venTotQ = 0;
try
{
String vName = "";
var prs = from pr in wowidb.PRs where pr.vendor_id == ven select pr;
temp = new IMAVenderCostIntervalReportData();
var v = (from i in wowidb.vendors where i.id == ven select i).First();
temp.VenderName = String.IsNullOrEmpty(v.c_name) ? v.name : v.c_name;
vName = temp.VenderName;
foreach (var p in prs)
{
try
{
var pj = (from proj in wowidb.Projects where proj.Project_Id == p.project_id select proj).First();
int month = pj.Create_Date.Month;
var quo = (from q in wowidb.Quotation_Version where q.Quotation_Version_Id == pj.Quotation_Id select q).First();
temp.Model = quo.Model_No;
int cid = (int)quo.Client_Id;
try
{
var client = (from cli in wowidb.clientapplicants where cli.id == cid select cli).First();
temp.Client = String.IsNullOrEmpty(client.c_companyname) ? client.companyname : client.c_companyname;
}
catch
{
}
var pri = (from pi in wowidb.PR_item where pi.pr_id == p.pr_id select pi).First();
try
{
//var tDesc = (from kk in wowidb.Quotation_Target from kkk in wowidb.Targets where pri.quotation_target_id == kk.Quotation_Target_Id && kk.target_id == kkk.target_id select kkk.target_description).First();
var tDesc = (from kk in wowidb.Quotation_Target where pri.quotation_target_id == kk.Quotation_Target_Id select kk.target_description).First();
temp.Country = tDesc;
}
catch (Exception)
{
}
temp.TotalUSD = ((decimal)pri.amount).ToString("F2");
temp.TotalQTY = ((decimal)pri.quantity).ToString("F0");
list.Add(temp);
venTotD += (decimal)pri.amount;
venTotQ += (decimal)pri.quantity;
}
catch (Exception)
{
//throw;
}
}//Single vender ends
TotD += venTotD;
TotQ += venTotQ;
if (venTotD!=0)
{
temp = new IMAVenderCostIntervalReportData()
{
VenderName = " Total : "
};
temp.TotalUSD = venTotD.ToString("F2");
temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (TotD != 0)
{
temp = new IMAVenderCostIntervalReportData()
{
VenderName = "Balance Total : "
};
temp.TotalUSD = TotD.ToString("F2");
temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<IMAVenderCostSeasonReportData> GetIMACostBySeason(WoWiModel.WoWiEntities wowidb, int year)
{
List<IMAVenderCostSeasonReportData> list = new List<IMAVenderCostSeasonReportData>();
IMAVenderCostSeasonReportData temp;
try
{
//Find all IMAs
var imas = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date.Year == year select pr.create_user).Distinct();
var venders = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date.Year == year select pr.vendor_id).Distinct();
decimal M1D = 0, M1Q = 0, M2D = 0, M2Q = 0, M3D = 0, M3Q = 0, M4D = 0, M4Q = 0, TotD = 0, TotQ = 0;
foreach (var ima in imas)
{
decimal imaM1D = 0, imaM1Q = 0, imaM2D = 0, imaM2Q = 0, imaM3D = 0, imaM3Q = 0, imaM4D = 0, imaM4Q = 0, imaTotD = 0, imaTotQ = 0;
foreach (var ven in venders)
{
decimal venM1D = 0, venM1Q = 0, venM2D = 0, venM2Q = 0, venM3D = 0, venM3Q = 0, venM4D = 0, venM4Q = 0, venTotD = 0, venTotQ = 0;
try
{
var prs = from pr in wowidb.PRs where pr.create_user == ima && pr.vendor_id == ven select pr;
temp = new IMAVenderCostSeasonReportData()
{
IMA = ima
};
var v = (from i in wowidb.vendors where i.id == ven select i).First();
temp.VenderName = String.IsNullOrEmpty(v.c_name) ? v.name : v.c_name;
foreach (var p in prs)
{
try
{
int month = (from proj in wowidb.Projects where proj.Project_Id == p.project_id select proj.Create_Date.Month).First();
var pri = (from pi in wowidb.PR_item where pi.pr_id == p.pr_id select pi).First();
switch (month)
{
case 1:
case 2:
case 3:
venM1D += (decimal)pri.amount;
venM1Q += (decimal)pri.quantity;
break;
case 4:
case 5:
case 6:
venM2D += (decimal)pri.amount;
venM2Q += (decimal)pri.quantity;
break;
case 7:
case 8:
case 9:
venM3D += (decimal)pri.amount;
venM3Q += (decimal)pri.quantity;
break;
case 10:
case 11:
case 12:
venM4D += (decimal)pri.amount;
venM4Q += (decimal)pri.quantity;
break;
}
}
catch (Exception)
{
//throw;
}
}//Single vender ends
venTotD = venM1D + venM2D + venM3D + venM4D;
venTotQ = venM1Q + venM2Q + venM3Q + venM4Q;
imaM1D += venM1D;
imaM1Q += venM1Q;
imaM2D += venM2D;
imaM2Q += venM2Q;
imaM3D += venM3D;
imaM3Q += venM3Q;
imaM4D += venM4D;
imaM4Q += venM4Q;
imaTotD += venTotD;
imaTotQ += venTotQ;
temp.Season01USD = venM1D.ToString("F2");
temp.Season02USD = venM2D.ToString("F2");
temp.Season03USD = venM3D.ToString("F2");
temp.Season04USD = venM4D.ToString("F2");
temp.TotalUSD = venTotD.ToString("F2");
temp.Season01QTY = venM1Q.ToString("F0");
temp.Season02QTY = venM2Q.ToString("F0");
temp.Season03QTY = venM3Q.ToString("F0");
temp.Season04QTY = venM4Q.ToString("F0");
temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (imaTotD != 0)
{
temp = new IMAVenderCostSeasonReportData()
{
IMA = ima + " Total : "
};
M1D += imaM1D;
M1Q += imaM1Q;
M2D += imaM2D;
M2Q += imaM2Q;
M3D += imaM3D;
M3Q += imaM3Q;
M4D += imaM4D;
M4Q += imaM4Q;
TotD += imaTotD;
TotQ += imaTotQ;
temp.Season01USD = imaM1D.ToString("F2");
temp.Season02USD = imaM2D.ToString("F2");
temp.Season03USD = imaM3D.ToString("F2");
temp.Season04USD = imaM4D.ToString("F2");
temp.TotalUSD = imaTotD.ToString("F2");
temp.Season01QTY = imaM1Q.ToString("F0");
temp.Season02QTY = imaM2Q.ToString("F0");
temp.Season03QTY = imaM3Q.ToString("F0");
temp.Season04QTY = imaM4Q.ToString("F0");
temp.TotalQTY = imaTotQ.ToString("F0");
list.Add(temp);
}
}//ima ends
if (TotD != 0)
{
temp = new IMAVenderCostSeasonReportData()
{
IMA = "Balance Total : "
};
temp.Season01USD = M1D.ToString("F2");
temp.Season02USD = M2D.ToString("F2");
temp.Season03USD = M3D.ToString("F2");
temp.Season04USD = M4D.ToString("F2");
temp.TotalUSD = TotD.ToString("F2");
temp.Season01QTY = M1Q.ToString("F0");
temp.Season02QTY = M2Q.ToString("F0");
temp.Season03QTY = M3Q.ToString("F0");
temp.Season04QTY = M4Q.ToString("F0");
temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<IMAVenderCostIntervalReportData> GetIMACostByInterval(WoWiModel.WoWiEntities wowidb, DateTime f, DateTime t)
{
List<IMAVenderCostIntervalReportData> list = new List<IMAVenderCostIntervalReportData>();
IMAVenderCostIntervalReportData temp;
try
{
//Find all IMAs
var imas = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date >= f && pj.Create_Date <= t select pr.create_user).Distinct();
var venders = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date >= f && pj.Create_Date <= t select pr.vendor_id).Distinct();
decimal TotD = 0, TotQ = 0;
foreach (var ima in imas)
{
decimal imaTotD = 0, imaTotQ = 0;
foreach (var ven in venders)
{
decimal venTotD = 0, venTotQ = 0;
try
{
var prs = from pr in wowidb.PRs where pr.create_user == ima && pr.vendor_id == ven select pr;
temp = new IMAVenderCostIntervalReportData()
{
IMA = ima
};
var v = (from i in wowidb.vendors where i.id == ven select i).First();
temp.VenderName = String.IsNullOrEmpty(v.c_name) ? v.name : v.c_name;
foreach (var p in prs)
{
try
{
var pri = (from pi in wowidb.PR_item where pi.pr_id == p.pr_id select pi).First();
venTotD += (decimal)pri.amount;
venTotQ += (decimal)pri.quantity;
}
catch (Exception)
{
//throw;
}
}//Single vender ends
imaTotD += venTotD;
imaTotQ += venTotQ;
temp.TotalUSD = venTotD.ToString("F2");
temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (imaTotD != 0)
{
temp = new IMAVenderCostIntervalReportData()
{
IMA = ima + " Total : "
};
TotD += imaTotD;
TotQ += imaTotQ;
temp.TotalUSD = imaTotD.ToString("F2");
temp.TotalQTY = imaTotQ.ToString("F0");
list.Add(temp);
}
}//ima ends
if (TotD != 0)
{
temp = new IMAVenderCostIntervalReportData()
{
IMA = "Balance Total : "
};
temp.TotalUSD = TotD.ToString("F2");
temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static List<IMAVenderCostMonthReportData> GetVenderCostByMonth(WoWiModel.WoWiEntities wowidb, int year)
{
List<IMAVenderCostMonthReportData> list = new List<IMAVenderCostMonthReportData>();
IMAVenderCostMonthReportData temp;
try
{
var venders = (from pr in wowidb.PRs from pj in wowidb.Projects where pr.project_id == pj.Project_Id && pj.Create_Date.Year == year select pr.vendor_id).Distinct();
decimal M1D = 0, M1Q = 0, M2D = 0, M2Q = 0, M3D = 0, M3Q = 0, M4D = 0, M4Q = 0, M5D = 0, M5Q = 0, M6D = 0, M6Q = 0, M7D = 0, M7Q = 0, M8D = 0, M8Q = 0, M9D = 0, M9Q = 0, M10D = 0, M10Q = 0, M11D = 0, M11Q = 0, M12D = 0, M12Q = 0, TotD = 0, TotQ = 0;
foreach (var ven in venders)
{
decimal venM1D = 0, venM1Q = 0, venM2D = 0, venM2Q = 0, venM3D = 0, venM3Q = 0, venM4D = 0, venM4Q = 0, venM5D = 0, venM5Q = 0, venM6D = 0, venM6Q = 0, venM7D = 0, venM7Q = 0, venM8D = 0, venM8Q = 0, venM9D = 0, venM9Q = 0, venM10D = 0, venM10Q = 0, venM11D = 0, venM11Q = 0, venM12D = 0, venM12Q = 0, venTotD = 0, venTotQ = 0;
try
{
var prs = from pr in wowidb.PRs where pr.vendor_id == ven select pr;
temp = new IMAVenderCostMonthReportData();
var v = (from i in wowidb.vendors where i.id == ven select i).First();
temp.VenderName = String.IsNullOrEmpty(v.c_name) ? v.name : v.c_name;
foreach (var p in prs)
{
try
{
int month = (from proj in wowidb.Projects where proj.Project_Id == p.project_id select proj.Create_Date.Month).First();
var pri = (from pi in wowidb.PR_item where pi.pr_id == p.pr_id select pi).First();
switch (month)
{
case 1:
venM1D += (decimal)pri.amount;
venM1Q += (decimal)pri.quantity;
break;
case 2:
venM2D += (decimal)pri.amount;
venM2Q += (decimal)pri.quantity;
break;
case 3:
venM3D += (decimal)pri.amount;
venM3Q += (decimal)pri.quantity;
break;
case 4:
venM4D += (decimal)pri.amount;
venM4Q += (decimal)pri.quantity;
break;
case 5:
venM5D += (decimal)pri.amount;
venM5Q += (decimal)pri.quantity;
break;
case 6:
venM6D += (decimal)pri.amount;
venM6Q += (decimal)pri.quantity;
break;
case 7:
venM7D += (decimal)pri.amount;
venM7Q += (decimal)pri.quantity;
break;
case 8:
venM8D += (decimal)pri.amount;
venM8Q += (decimal)pri.quantity;
break;
case 9:
venM9D += (decimal)pri.amount;
venM9Q += (decimal)pri.quantity;
break;
case 10:
venM10D += (decimal)pri.amount;
venM10Q += (decimal)pri.quantity;
break;
case 11:
venM11D += (decimal)pri.amount;
venM11Q += (decimal)pri.quantity;
break;
case 12:
venM12D += (decimal)pri.amount;
venM12Q += (decimal)pri.quantity;
break;
}
}
catch (Exception)
{
//throw;
}
}//Single vender ends
venTotD = venM1D + venM2D + venM3D + venM4D + venM5D + venM6D + venM7D + venM8D + venM9D + venM10D + venM11D + venM12D;
venTotQ = venM1Q + venM2Q + venM3Q + venM4Q + venM5Q + venM6Q + venM7Q + venM8Q + venM9Q + venM10Q + venM11Q + venM12Q;
M1D += venM1D;
M1Q += venM1Q;
M2D += venM2D;
M2Q += venM2Q;
M3D += venM3D;
M3Q += venM3Q;
M4D += venM4D;
M4Q += venM4Q;
M5D += venM5D;
M5Q += venM5Q;
M6D += venM6D;
M6Q += venM6Q;
M7D += venM7D;
M7Q += venM7Q;
M8D += venM8D;
M8Q += venM8Q;
M9D += venM9D;
M9Q += venM9Q;
M10D += venM10D;
M10Q += venM10Q;
M11D += venM11D;
M11Q += venM11Q;
M12D += venM12D;
M12Q += venM12Q;
TotD += venTotD;
TotQ += venTotQ;
temp.Month01USD = venM1D.ToString("F2");
temp.Month02USD = venM2D.ToString("F2");
temp.Month03USD = venM3D.ToString("F2");
temp.Month04USD = venM4D.ToString("F2");
temp.Month05USD = venM5D.ToString("F2");
temp.Month06USD = venM6D.ToString("F2");
temp.Month07USD = venM7D.ToString("F2");
temp.Month08USD = venM8D.ToString("F2");
temp.Month09USD = venM9D.ToString("F2");
temp.Month10USD = venM10D.ToString("F2");
temp.Month11USD = venM11D.ToString("F2");
temp.Month12USD = venM12D.ToString("F2");
temp.TotalUSD = venTotD.ToString("F2");
temp.Month01QTY = venM1Q.ToString("F0");
temp.Month02QTY = venM2Q.ToString("F0");
temp.Month03QTY = venM3Q.ToString("F0");
temp.Month04QTY = venM4Q.ToString("F0");
temp.Month05QTY = venM5Q.ToString("F0");
temp.Month06QTY = venM6Q.ToString("F0");
temp.Month07QTY = venM7Q.ToString("F0");
temp.Month08QTY = venM8Q.ToString("F0");
temp.Month09QTY = venM9Q.ToString("F0");
temp.Month10QTY = venM10Q.ToString("F0");
temp.Month11QTY = venM11Q.ToString("F0");
temp.Month12QTY = venM12Q.ToString("F0");
temp.TotalQTY = venTotQ.ToString("F0");
list.Add(temp);
}
catch (Exception)
{
//throw;
}
}//Vender ends
if (TotD != 0)
{
temp = new IMAVenderCostMonthReportData()
{
VenderName = "Balance Total : "
};
temp.Month01USD = M1D.ToString("F2");
temp.Month02USD = M2D.ToString("F2");
temp.Month03USD = M3D.ToString("F2");
temp.Month04USD = M4D.ToString("F2");
temp.Month05USD = M5D.ToString("F2");
temp.Month06USD = M6D.ToString("F2");
temp.Month07USD = M7D.ToString("F2");
temp.Month08USD = M8D.ToString("F2");
temp.Month09USD = M9D.ToString("F2");
temp.Month10USD = M10D.ToString("F2");
temp.Month11USD = M11D.ToString("F2");
temp.Month12USD = M12D.ToString("F2");
temp.TotalUSD = TotD.ToString("F2");
temp.Month01QTY = M1Q.ToString("F0");
temp.Month02QTY = M2Q.ToString("F0");
temp.Month03QTY = M3Q.ToString("F0");
temp.Month04QTY = M4Q.ToString("F0");
temp.Month05QTY = M5Q.ToString("F0");
temp.Month06QTY = M6Q.ToString("F0");
temp.Month07QTY = M7Q.ToString("F0");
temp.Month08QTY = M8Q.ToString("F0");
temp.Month09QTY = M9Q.ToString("F0");
temp.Month10QTY = M10Q.ToString("F0");
temp.Month11QTY = M11Q.ToString("F0");
temp.Month12QTY = M12Q.ToString("F0");
temp.TotalQTY = TotQ.ToString("F0");
list.Add(temp);
}
}
catch (Exception)
{
//throw;
}
return list;
}
public static Object GetEmployeeList(WoWiModel.WoWiEntities db)
{
var list = from e in db.employees where e.status == "Active" orderby e.fname, e.c_fname select new { id = e.id, name = String.IsNullOrEmpty(e.fname) ? e.c_lname + " " + e.c_fname : e.fname + " " + e.lname };
return list;
}
