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; }