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