/// <summary> /// Jumlah (∑) Pajak Impor sudah pertanggungjawaban /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary12() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(lop.po) AS data1 FROM len_option_po lop JOIN len_payment lp ON lp.po = lop.po WHERE lop.po_import = 1 AND ( lp.payment_method = 'T/T' OR lp.payment_method = 'L/C' ) AND YEAR (CURDATE()) AND STATUS = '1';"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Pajak Impor sudah pertanggungjawaban"; model.name2 = ""; model.link1 = POQUERY + "SELECT lop.po as result FROM len_option_po lop JOIN len_payment lp ON lp.po = lop.po WHERE lop.po_import = 1 AND ( lp.payment_method = 'T/T' OR lp.payment_method = 'L/C' ) AND YEAR (CURDATE()) AND STATUS = '1';"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) Impor TT total /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary4() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(*) AS data1 FROM len_payment lp LEFT JOIN len_option_po op ON op.po = lp.po WHERE payment_method = 'T/T' AND po_import IS TRUE AND YEAR (lp.claim_date) = YEAR (CURDATE());"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Impor TT total"; model.name2 = ""; model.link1 = POQUERY + "SELECT lp.po AS result FROM len_payment lp LEFT JOIN len_option_po op ON op.po = lp.po WHERE payment_method = 'T/T' AND po_import IS TRUE AND YEAR (lp.claim_date) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) Impor TT > 1 bulan belum pertanggungjawaban /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary5() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(claim_date) AS data1 FROM len_payment lp LEFT JOIN len_option_po o ON o.po = lp.po WHERE (claim_date) <= (NOW() - INTERVAL 30 DAY) AND o.po_import IS TRUE AND payment_method = 'T/T' AND STATUS = '0' AND YEAR (lp.claim_date) = YEAR (CURDATE());"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Impor TT > 1 bulan belum pertanggungjawaban"; model.name2 = ""; model.link1 = POQUERY + "SELECT lp.po AS result FROM len_payment lp LEFT JOIN len_option_po o ON o.po = lp.po WHERE (claim_date) <= (NOW() - INTERVAL 30 DAY) AND o.po_import IS TRUE AND payment_method = 'T/T' AND STATUS = '0' AND YEAR (lp.claim_date) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) DPB belum ada Pelaksananya /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary3() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT d.dpb) as data1 FROM len_enq_dpb_product d LEFT JOIN len_executor pd ON pd.dpb = d.dpb LEFT JOIN len_profile lp ON lp.user_id = pd.executor LEFT JOIN len_enq_dpb dp ON dp.dpb = d.dpb WHERE YEAR (d.updated) = YEAR (CURDATE()) AND pd.executor IS NULL AND dp.`status` != 'C' AND YEAR (dp.tgl_pengajuan) = YEAR (CURDATE()) ORDER BY d.dpb DESC"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) DPB belum ada Pelaksananya"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT DISTINCT d.dpb as result FROM len_enq_dpb_product d LEFT JOIN len_executor pd ON pd.dpb = d.dpb LEFT JOIN len_profile lp ON lp.user_id = pd.executor LEFT JOIN len_enq_dpb dp ON dp.dpb = d.dpb WHERE YEAR (d.updated) = YEAR (CURDATE()) AND pd.executor IS NULL AND dp.`status` != 'C' AND YEAR (dp.tgl_pengajuan) = YEAR (CURDATE()) ORDER BY d.dpb DESC"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) DPB sudah ada Pelaksananya /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary2() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT d.dpb) AS data1 FROM len_enq_dpb d LEFT JOIN len_executor pd ON pd.dpb = d.dpb LEFT JOIN len_profile lp ON lp.user_id = pd.executor WHERE YEAR (d.tgl_pengajuan) = YEAR (CURDATE()) AND pd.executor IS NOT NULL;"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) DPB sudah ada Pelaksananya"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT DISTINCT d.dpb AS result FROM len_enq_dpb d LEFT JOIN len_executor pd ON pd.dpb = d.dpb LEFT JOIN len_profile lp ON lp.user_id = pd.executor WHERE YEAR (d.tgl_pengajuan) = YEAR (CURDATE()) AND pd.executor IS NOT NULL;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) Total Tagihan masuk /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary14() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(payment_method) AS data1 FROM len_payment WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR(claim_date) = YEAR(CURDATE());"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total Tagihan masuk"; model.name2 = ""; model.link1 = POQUERY + "SELECT po AS result FROM len_payment WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR(claim_date) = YEAR(CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) DPB total /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary1() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT d.dpb) AS data1 FROM len_enq_dpb_product d LEFT JOIN len_enq_dpb dp ON dp.dpb = d.dpb WHERE YEAR (d.updated) = YEAR (CURDATE()) AND YEAR (dp.tgl_pengajuan) = YEAR (CURDATE());"; var result = db.Database.SqlQuery<SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) DPB total"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT DISTINCT d.dpb AS result FROM len_enq_dpb_product d LEFT JOIN len_enq_dpb dp ON dp.dpb = d.dpb WHERE YEAR (d.updated) = YEAR (CURDATE()) AND YEAR (dp.tgl_pengajuan) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return model; }
/// <summary> /// Jumlah (∑) DPB belum SPPH /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary3() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT e.dpb) AS data1 FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb WHERE p.initials = '" + _initial + "' AND s.spph IS NULL AND YEAR (e.created_at) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) DPB belum SPPH"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT e.dpb AS result FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb WHERE p.initials = '" + _initial + "' AND s.spph IS NULL AND YEAR (e.created_at) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Prosentase (%) Jumlah (∑) item barang datang / PO sudah jatuh tempo impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary25() { SummaryModel model = new SummaryModel(); string query = "SELECT IFNULL( ROUND( y.habis_kontrak / x.full_item * 100, 2 ), 0 ) data1 FROM ( SELECT COUNT(lep.po) AS full_item FROM len_executor e INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_po lep ON lep.po = s.po LEFT JOIN len_enq_po_product lepp ON lepp.po = lep.po LEFT JOIN len_product_delivered lpd ON lpd.product = lepp.product INNER JOIN len_option_po op ON op.po = lep.po WHERE o.initials = '" + _initial + "' AND DATE(lep.tgl_habis_kontrak) <= DATE(CURDATE()) AND op.po_import IS TRUE AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE())) x JOIN ( SELECT COUNT(lep.po) AS habis_kontrak FROM len_executor e INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_po lep ON lep.po = s.po LEFT JOIN len_enq_po_product lepp ON lepp.po = lep.po LEFT JOIN len_product_delivered lpd ON lpd.product = lepp.product INNER JOIN len_option_po op ON op.po = lep.po WHERE o.initials = '" + _initial + "' AND DATE(lep.tgl_habis_kontrak) <= DATE(CURDATE()) AND op.po_import IS TRUE AND lepp.qty = lpd.qty_delivered AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE())) y;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Prosentase (%) Jumlah (∑) item barang datang / PO sudah jatuh tempo"; model.name2 = ""; model.link1 = ""; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = true; return(model); }
/// <summary> /// Jumlah (∑) SPPH Total impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary18() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.spph) AS data1 FROM len_executor e JOIN len_profile p ON p.user_id = e.executor JOIN len_enq_spph_product s ON s.dpb = e.dpb JOIN len_option_po po ON po.po = s.po WHERE p.initials = '" + _initial + "' AND YEAR (e.created_at) = YEAR (CURDATE()) AND po.po_import IS TRUE;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) SPPH Total"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT DISTINCT s.dpb AS result FROM len_executor e JOIN len_profile p ON p.user_id = e.executor JOIN len_enq_spph_product s ON s.dpb = e.dpb JOIN len_option_po po ON po.po = s.po WHERE p.initials = '" + _initial + "' AND YEAR (e.created_at) = YEAR (CURDATE()) AND po.po_import IS TRUE;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// ∑ Jaminan Pelaksanaan akan berakhir kurang dari tgl habis kontrak amandemen lokal /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary11() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e INNER JOIN len_enq_spph s ON s.dpb = e.dpb INNER JOIN len_enq_po p ON p.po = s.po INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_option_po op ON op.po = s.po WHERE o.initials = '" + _initial + "' AND op.amendment IS TRUE AND op.jaminan_pelaksanaan = 1 AND op.po_import = 0 AND op.tgl_pelaksanaan < p.tgl_habis_kontrak AND YEAR (p.tgl_habis_kontrak) = YEAR (CURDATE())"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "∑ Jaminan Pelaksanaan akan berakhir < tgl habis kontrak amandemen"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e INNER JOIN len_enq_spph s ON s.dpb = e.dpb INNER JOIN len_enq_po p ON p.po = s.po INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_option_po op ON op.po = s.po WHERE o.initials = '" + _initial + "' AND op.amendment IS TRUE AND op.jaminan_pelaksanaan = 1 AND op.po_import = 0 AND op.tgl_pelaksanaan < p.tgl_habis_kontrak AND YEAR (p.tgl_habis_kontrak) = YEAR (CURDATE())"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Jaminan Uang Muka akan berakhir kurang dari 1 bulan /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary5() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lep.po) AS data1 FROM len_enq_po lep INNER JOIN len_option_po lop ON lop.po = lep.po WHERE lop.jaminan_um = 1 AND DATEDIFF(NOW(), lop.tgl_um) BETWEEN - 30 AND -1 AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Jaminan Uang Muka akan berakhir < 1 bulan"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep INNER JOIN len_option_po lop ON lop.po = lep.po WHERE lop.jaminan_um = 1 AND DATEDIFF(NOW(), lop.tgl_um) BETWEEN - 30 AND -1 AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Kontrak akan berakhir kurang dari 1 bulan impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary20() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lep.po) AS data1 FROM len_enq_po lep LEFT OUTER JOIN len_option_po o ON o.po = lep.po WHERE DATEDIFF( NOW(), lep.tgl_habis_kontrak ) BETWEEN - 30 AND - 1 AND (o.po_import IS TRUE) AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Kontrak akan berakhir < 1 bulan"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep LEFT OUTER JOIN len_option_po o ON o.po = lep.po WHERE DATEDIFF( NOW(), lep.tgl_habis_kontrak ) BETWEEN - 30 AND - 1 AND (o.po_import IS TRUE) AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Keterlambatan barang user /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary8() { SummaryModel model = new SummaryModel(); string query = "CALL get_not_green();"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Item"; model.name2 = ""; model.link1 = ""; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) UM sudah pertanggungjawaban impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary24() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lp.po) AS data1 FROM len_payment lp LEFT JOIN len_option_po o ON o.po = lp.po WHERE payment = 'Uang Muka' AND STATUS = '1' AND (o.po_import IS TRUE) AND YEAR (lp.claim_date) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) UM sudah pertanggungjawaban"; model.name2 = ""; model.link1 = POQUERY + "SELECT COUNT(DISTINCT lp.po) AS data1 FROM len_payment lp LEFT JOIN len_option_po o ON o.po = lp.po WHERE payment = 'Uang Muka' AND STATUS = '1' AND (o.po_import IS TRUE) AND YEAR (lp.claim_date) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Prosentase (%) Jumlah (∑) Total Item yang sudah Kontrak / Total item /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary2() { SummaryModel model = new SummaryModel(); string query = "SELECT IFNULL( ROUND( y.total_item_kontrak / x.total_item * 100, 2 ), 0 ) AS data1 FROM ( SELECT COUNT(DISTINCT ledp.product) AS total_item FROM len_enq_dpb_product ledp ) x CROSS JOIN ( SELECT COUNT(DISTINCT ledp.product) AS total_item_kontrak FROM len_enq_dpb_product ledp INNER JOIN len_enq_po_product lepp ON lepp.product = ledp.product ) y;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Prosentase (%) Jumlah (∑) Total Item yang sudah Kontrak / Total item"; model.name2 = ""; model.link1 = // POQUERY + "SELECT DISTINCT lepp.po AS result FROM len_enq_dpb_product ledp INNER JOIN len_enq_po_product lepp ON lepp.product = ledp.product WHERE YEAR (lepp.tgl_po) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = true; return(model); }
/// <summary> /// Prosentase (%) Jumlah (∑) Total Item yang sudah Kontrak / Total item impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary19() { SummaryModel model = new SummaryModel(); string query = "SELECT IFNULL( ROUND( z.total_item_po / x.total_item * 100, 2 ), 0 ) AS data1 FROM ( SELECT COUNT(lepp.product) AS total_item_po FROM len_enq_po_product lepp LEFT OUTER JOIN len_option_po o ON o.po = lepp.po WHERE YEAR (lepp.tgl_po) = YEAR (CURDATE()) AND (o.po_import IS TRUE)) z CROSS JOIN ( SELECT COUNT(d.product) AS total_item FROM len_enq_dpb_product d INNER JOIN len_enq_spph s ON s.dpb = d.dpb LEFT OUTER JOIN len_option_po o ON o.po = s.po WHERE YEAR (updated) = YEAR (CURDATE()) AND (o.po_import IS TRUE)) x;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Prosentase (%) Jumlah (∑) Total Item yang sudah Kontrak / Total item"; model.name2 = ""; model.link1 = "";// POQUERY + "SELECT DISTINCT lepp.po AS result FROM len_enq_dpb_product ledp INNER JOIN len_enq_po_product lepp ON lepp.product = ledp.product WHERE YEAR (lepp.tgl_po) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = true; return(model); }
/// <summary> /// Jumlah (∑) Total Item DPB belum dibuat SPPH /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary2() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(d.spph) AS data1 FROM len_enq_dpb_product_new d WHERE YEAR (d.updated) = YEAR (CURDATE()) AND d.user_dpb = '" + _userdpb + "' AND d.spph = 0;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total Item DPB belum dibuat SPPH"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT d.dpb AS result FROM len_enq_dpb_product_new d WHERE YEAR (d.updated) = YEAR (CURDATE()) AND d.user_dpb = '" + _userdpb + "' AND d.spph = 0;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Jaminan Uang Muka sudah berakhir impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary24() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lep.po) AS data1 FROM len_enq_po lep LEFT OUTER JOIN len_option_po lop ON lop.po = lep.po WHERE lop.tgl_um < NOW() AND lop.jaminan_um = 1 AND (lop.po_import IS TRUE) AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Jaminan Uang Muka sudah berakhir"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep LEFT OUTER JOIN len_option_po lop ON lop.po = lep.po WHERE lop.tgl_um < NOW() AND lop.jaminan_um = 1 AND (lop.po_import IS TRUE) AND YEAR (lep.tgl_habis_kontrak) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Total Item DPB belum dibuat PO /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary4() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT( DISTINCT sp.dpb, sp.product, sp.po, sp.spph, d.user_dpb ) AS data1 FROM len_enq_spph_product sp INNER JOIN len_enq_dpb d ON d.dpb = sp.dpb WHERE YEAR (sp.mail_date) = YEAR (CURDATE()) AND sp.po IS FALSE AND d.user_dpb = '" + _userdpb + "' ORDER BY sp.dpb DESC;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total Item DPB belum dibuat PO"; model.name2 = ""; model.link1 = DPBQUERY + "SELECT DISTINCT sp.dpb AS result FROM len_enq_spph_product sp INNER JOIN len_enq_dpb d ON d.dpb = sp.dpb WHERE YEAR (sp.mail_date) = YEAR (CURDATE()) AND sp.po IS FALSE AND d.user_dpb = '" + _userdpb + "' ORDER BY sp.dpb DESC;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Jaminan Uang Muka akan berakhir kurang dari 1 bulan lokal /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary10() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_po p ON p.po = s.po LEFT JOIN len_profile o ON o.user_id = e.executor LEFT JOIN len_option_po op ON op.po = s.po WHERE o.initials = '" + _initial + "' AND op.jaminan_um = 1 AND op.po_import = 0 AND DATEDIFF(NOW(), op.tgl_um) BETWEEN - 30 AND -1 AND YEAR (p.tgl_habis_kontrak) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Jaminan Uang Muka akan berakhir < 1 bulan"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_po p ON p.po = s.po LEFT JOIN len_profile o ON o.user_id = e.executor LEFT JOIN len_option_po op ON op.po = s.po WHERE o.initials = '" + _initial + "' AND op.jaminan_um = 1 AND op.po_import = 0 AND DATEDIFF(NOW(), op.tgl_um) BETWEEN - 30 AND -1 AND YEAR (p.tgl_habis_kontrak) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Total Tagihan masuk belum dibuat BAPB /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary2() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT( lp.po ) AS data1 FROM len_payment lp LEFT JOIN len_enq_bapb leb ON leb.po = lp.po WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR (lp.claim_date) = YEAR (CURDATE()) AND leb.bapb IS NULL;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total Tagihan masuk belum dibuat BAPB"; model.name2 = ""; model.link1 = POQUERY + "SELECT lp.po AS result FROM len_payment lp LEFT JOIN len_enq_bapb leb ON leb.po = lp.po WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR (lp.claim_date) = YEAR (CURDATE()) AND leb.bapb IS NULL;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Total PO sudah dibuat SPP lokal /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary15() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_spp sp ON sp.po = s.po LEFT JOIN len_option_po op ON op.po = s.po WHERE p.initials = '" + _initial + "' AND op.po_import IS FALSE AND sp.spp_number IS NOT NULL AND YEAR (e.created_at) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total PO sudah dibuat SPP"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_spp sp ON sp.po = s.po LEFT JOIN len_option_po op ON op.po = s.po WHERE p.initials = '" + _initial + "' AND op.po_import IS FALSE AND sp.spp_number IS NOT NULL AND YEAR (e.created_at) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Total Tagihan masuk sudah dibuat SPP /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary5() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT( DISTINCT lp.claim_date, ls.spp_number, lp.po ) AS data1 FROM len_payment lp LEFT JOIN len_spp ls ON ls.po = lp.po WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR (lp.claim_date) = YEAR (CURDATE()) AND ls.spp_number IS NOT NULL;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total Tagihan masuk sudah dibuat SPP"; model.name2 = ""; model.link1 = POQUERY + "SELECT lp.po AS result FROM len_payment lp LEFT JOIN len_spp ls ON ls.po = lp.po WHERE payment_method != 'T/T' AND payment_method != 'L/C' AND YEAR (lp.claim_date) = YEAR (CURDATE()) AND ls.spp_number IS NOT NULL;"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) UM Impor yang diajukan > 1 minggu impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary24() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e INNER JOIN len_enq_spph_product s ON s.dpb = e.dpb INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_option_po lop ON lop.po = s.po INNER JOIN len_payment pay ON pay.po = s.po WHERE o.initials = '" + _initial + "' AND lop.po_import = 1 AND pay.payment = 'Uang Muka' AND pay. STATUS IS FALSE AND DATEDIFF(pay.claim_date, NOW()) < - 7 AND YEAR (s.mail_date) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) UM Impor yang diajukan > 1 minggu"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e INNER JOIN len_enq_spph_product s ON s.dpb = e.dpb INNER JOIN len_profile o ON o.user_id = e.executor INNER JOIN len_option_po lop ON lop.po = s.po INNER JOIN len_payment pay ON pay.po = s.po WHERE o.initials = '" + _initial + "' AND lop.po_import = 1 AND pay.payment = 'Uang Muka' AND pay. STATUS IS FALSE AND DATEDIFF(pay.claim_date, NOW()) < - 7 AND YEAR (s.mail_date) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) UM Impor yang sudah dibayar /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary9() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lep.po) AS data1 FROM len_enq_po lep INNER JOIN len_payment lp ON lp.po = lep.po INNER JOIN len_option_po p ON p.po = lp.po WHERE lp.payment = 'UANG MUKA' AND p.po_import IS TRUE AND lp. STATUS = 1 AND YEAR (lep.tgl_po) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) UM Impor yang sudah dibayar"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep INNER JOIN len_payment lp ON lp.po = lep.po INNER JOIN len_option_po p ON p.po = lp.po WHERE lp.payment = 'UANG MUKA' AND p.po_import IS TRUE AND lp. STATUS = 1 AND YEAR (lep.tgl_po) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) Total PO belum dibuat BAPB /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary27() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_bapb ba ON ba.po = s.po JOIN len_option_po po ON po.po = s.po WHERE p.initials = '" + _initial + "' AND ba.bapb IS NULL AND po.po_import IS TRUE AND YEAR (e.created_at) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) Total PO belum dibuat BAPB"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e JOIN len_profile p ON p.user_id = e.executor LEFT JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT JOIN len_enq_bapb ba ON ba.po = s.po JOIN len_option_po po ON po.po = s.po WHERE p.initials = '" + _initial + "' AND ba.bapb IS NULL AND po.po_import IS TRUE AND YEAR (e.created_at) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) UM Impor yang diajukan > 1 minggu /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary10() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT lep.po) AS data1 FROM len_enq_po lep INNER JOIN len_payment lp ON lp.po = lep.po INNER JOIN len_option_po lop ON lop.po = lep.po WHERE DATEDIFF(lp.claim_date, NOW()) < - 7 AND lop.po_import = 1 AND lp.payment = 'UANG MUKA' AND lp. STATUS = 0 AND YEAR (lp.claim_date) = YEAR (CURDATE());"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) UM Impor yang diajukan > 1 minggu"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep INNER JOIN len_payment lp ON lp.po = lep.po INNER JOIN len_option_po lop ON lop.po = lep.po WHERE DATEDIFF(lp.claim_date, NOW()) < - 7 AND lop.po_import = 1 AND lp.payment = 'UANG MUKA' AND lp. STATUS = 0 AND YEAR (lp.claim_date) = YEAR (CURDATE());"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
/// <summary> /// Jumlah (∑) PO total /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary4() { SummaryModel model = new SummaryModel(); string query = "SELECT COUNT(DISTINCT s.po) AS data1 FROM len_executor e JOIN len_profile p ON p.user_id = e.executor JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT OUTER JOIN len_option_po o ON o.po = s.po WHERE p.initials = '" + _initial + "' AND s.po IS NOT FALSE AND YEAR (e.created_at) = YEAR (CURDATE()) AND ( (o.po_import IS NULL) || (o.po_import IS FALSE) );"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) PO total"; model.name2 = ""; model.link1 = POQUERY + "SELECT DISTINCT s.po AS result FROM len_executor e JOIN len_profile p ON p.user_id = e.executor JOIN len_enq_spph_product s ON s.dpb = e.dpb LEFT OUTER JOIN len_option_po o ON o.po = s.po WHERE p.initials = '" + _initial + "' AND s.po IS NOT FALSE AND YEAR (e.created_at) = YEAR (CURDATE()) AND ( (o.po_import IS NULL) || (o.po_import IS FALSE) );"; model.link2 = ""; model.data1 = result.data1; model.data2 = ""; model.percentage = false; return(model); }
//struktural impor /// <summary> /// Jumlah (∑) PO yang nego impor /// </summary> /// <returns>hasil summary dalam bentuk model summary</returns> public SummaryModel GetSummary18() { SummaryModel model = new SummaryModel(); string query = "SELECT nego.po_nego AS data1, po.total_po - nego.po_nego AS data2 FROM ( SELECT COUNT(*) AS po_nego FROM len_enq_po lep LEFT OUTER JOIN len_option_po lop ON lep.po = lop.po WHERE lop.negotiation = 1 AND (lop.po_import IS TRUE) AND YEAR (lep.tgl_po) = YEAR (CURDATE())) nego CROSS JOIN ( SELECT COUNT(DISTINCT(p.po)) AS total_po FROM len_enq_po p LEFT OUTER JOIN len_option_po lop ON lop.po = p.po WHERE YEAR (tgl_po) = YEAR (CURDATE()) AND (lop.po_import IS TRUE)) po;"; var result = db.Database.SqlQuery <SummaryModel>(query).FirstOrDefault(); model.name1 = "Jumlah (∑) PO yang nego"; model.name2 = "tidak nego"; model.link1 = POQUERY + "SELECT DISTINCT lep.po AS result FROM len_enq_po lep INNER JOIN len_option_po lop ON lep.po = lop.po WHERE lop.negotiation = 1 AND (lop.po_import IS TRUE) AND YEAR (lep.tgl_po) = YEAR (CURDATE());"; model.link2 = POQUERY + "SELECT DISTINCT p.po AS result FROM len_enq_po p LEFT JOIN len_option_po op ON op.po = p.po WHERE YEAR (tgl_po) = YEAR (CURDATE()) AND (op.po_import IS TRUE) AND ( op.negotiation <> 1 || op.negotiation IS NULL );"; model.data1 = result.data1; model.data2 = result.data2; model.percentage = false; return(model); }