def searchGoods_byForm(self, data):
cur = connection.cursor()
if data['tab_section'] == 'g_outwards':
Query = """SELECT ngo.idapp,ngo.fk_goods, g.itemcode,CONCAT(g.goodsname, ' ',g.brandname, ' ',grd.typeapp) as goods,@table_name := 'GO' AS tbl_name, ngo.serialnumber,
empl1.fk_employee,empl1.nik_employee,empl2.fk_resp,empl2.nik_resp FROM n_a_goods_outwards ngo INNER JOIN n_a_goods g ON ngo.fk_goods = g.idapp INNER JOIN n_a_goods_receive ngr
ON g.idapp = ngr.fk_goods INNER JOIN n_a_goods_receive_detail grd ON ngr.idapp = grd.fk_app AND ngo.serialnumber = grd.serialnumber
LEFT OUTER JOIN (SELECT idapp, nik AS nik_employee,employee_name AS fk_employee FROM employee)
AS empl1 ON ngo.fk_employee = empl1.idapp LEFT OUTER JOIN (SELECT idapp, nik AS nik_resp,employee_name AS fk_resp FROM employee) AS empl2 ON ngo.fk_responsibleperson = empl2.idapp
WHERE NOT EXISTS(SELECT m.serialnumber FROM n_a_maintenance m WHERE m.serialnumber=ngo.serialnumber AND m.isfinished=0) AND
CONCAT(g.goodsname, ' ',g.brandname, ' ',grd.typeapp) LIKE \'{0}\'"""
result = 'g_outwards'
elif data['tab_section'] == 'g_lending':
Query = """SELECT ngl.idapp,ngl.fk_goods, g.itemcode,CONCAT(g.goodsname, ' ',g.brandname, ' ',grd.typeapp) as goods,ngl.serialnumber,@table_name := 'GL' AS tbl_name,
empl1.fk_employee,empl1.nik_employee,empl2.fk_resp,empl2.nik_resp FROM n_a_goods_lending ngl INNER JOIN n_a_goods g ON ngl.fk_goods = g.idapp INNER JOIN
n_a_goods_receive ngr ON g.idapp = ngr.fk_goods INNER JOIN n_a_goods_receive_detail grd ON ngr.idapp = grd.fk_app AND
ngl.serialnumber = grd.serialnumber LEFT OUTER JOIN (SELECT idapp, nik AS nik_employee,employee_name AS fk_employee FROM employee)
AS empl1 ON ngl.fk_employee = empl1.idapp LEFT OUTER JOIN (SELECT idapp, nik AS nik_resp,employee_name AS fk_resp FROM employee) AS empl2 ON ngl.fk_responsibleperson = empl2.idapp
WHERE NOT EXISTS(SELECT gls.idapp FROM n_a_goods_lost gls WHERE gls.serialnumber=ngl.serialnumber) AND CONCAT(g.goodsname, ' ',g.brandname, ' ',grd.typeapp) LIKE \'{0}\'"""
result = 'g_lending'
elif data['tab_section'] == 'g_maintenance':
Query = """SELECT m.idapp, m.fk_goods, g.itemcode, m.fk_goods, m.typeapp, m.serialnumber, @table_name := 'GM' AS tbl_name,
CONCAT(g.goodsname, ' ', g.brandname, ' ', m.typeapp) AS goods FROM n_a_maintenance m INNER JOIN n_a_goods g ON
m.fk_goods = g.idapp WHERE NOT EXISTS (SELECT gls.idapp FROM n_a_goods_lost gls WHERE gls.serialnumber=m.serialnumber) AND
m.isfinished=0 AND CONCAT(g.goodsname, ' ',g.brandname, ' ',m.typeapp) LIKE \'{0}\'"""
result = 'g_maintenance'
cur.execute(Query.format('%' + data['goods_filter'] + '%'))
result = (result, query.dictfetchall(cur))
connection.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """CREATE TEMPORARY TABLE T_Receive_other ENGINE=InnoDB AS (SELECT ngr.IDApp,
ngr.refno,g.goodsname as goods, ngr.datereceived,sp.supliername,ngr.FK_ReceivedBy,
emp1.receivedby,ngr.FK_P_R_By ,emp2.pr_by,ngr.totalpurchase, ngr.totalreceived,
CONCAT(IFNULL(ngr.descriptions,' '),', ITEMS : ', IFNULL(ngr.DescBySystem,' ')) AS
descriptions,ngr.CreatedDate,ngr.CreatedBy FROM n_a_goods_receive_other AS ngr
INNER JOIN n_a_suplier AS sp ON sp.SuplierCode = ngr.FK_Suplier LEFT OUTER JOIN
(SELECT IDApp,Employee_Name AS receivedby FROM employee) AS emp1 ON
emp1.IDApp = ngr.FK_ReceivedBy LEFT OUTER JOIN (SELECT IDApp,Employee_Name AS
pr_by FROM employee) AS emp2 ON emp2.IDApp = ngr.FK_P_R_By
INNER JOIN n_a_goods as g ON g.IDApp = ngr.FK_goods WHERE """ + columnKey + rs.Sql(
) + ")"
cur.execute(Query)
Query = """SELECT * FROM T_Receive_other"""
cur.execute(Query)
result = query.dictfetchall(cur)
cur.execute('DROP TEMPORARY TABLE IF EXISTS T_Receive_other')
return result
def getData(self, idapp):
cur = connection.cursor()
#/idapp, fk_goods, isnew, goods, idapp_fk_goods, fk_employee, idapp_fk_employee, fk_employee_employee
# //daterequest,datereleased, fk_stock, fk_responsibleperson, idapp_fk_responsibleperson, fk_responsibleperson_employee,
# fk_sender, idapp_fk_sender, fk_sender_employee, descriptions,fk_usedemployee,idapp_fk_usedemployee,fk_usedemployee_employee, typeapp, serialnumber,
# //brandvalue, fk_frommaintenance, fk_return, fk_lending, fk_receive, lastinfo, initializeForm, hasRefData
Query = """SELECT g.itemcode AS fk_goods,ngo.isnew,g.goodsname AS goods,ngo.fk_goods AS idapp_fk_goods,emp.NIK AS fk_employee,\
ngo.fk_employee AS idapp_fk_employee,emp.employee_name AS fk_employee_employee,ngo.daterequest,ngo.datereleased,ngo.fk_stock,\
emp2.NIK AS fk_responsibleperson, ngo.FK_ResponsiblePerson AS idapp_fk_responsibleperson,IFNULL(ngo.lastinfo,'not yet used') AS lastinfo, \
emp1.NIK AS fk_sender,ngo.fk_sender AS idapp_fk_sender,emp1.employee_name AS fk_sender_employee,ngo.equipment_desc, \
ngo.descriptions,emp3.NIK AS fk_usedemployee,ngo.fk_usedemployee AS idapp_fk_usedemployee,emp3.employee_name AS fk_usedemployee_employee,ngo.typeapp,ngo.serialnumber, emp2.employee_name AS fk_responsibleperson_employee,g.brandname AS brandvalue, \
IFNULL(ngo.fk_frommaintenance,0)AS fk_frommaintenance,IFNULL(ngo.fk_return,0) AS fk_return, \
IFNULL(ngo.fk_lending,0) AS fk_lending,IFNULL(fk_receive,0) AS fk_receive, \
CASE WHEN EXISTS(SELECT fk_goods FROM n_a_disposal WHERE fk_goods = ngo.fk_goods AND serialnumber = ngo.serialnumber) THEN 1
WHEN EXISTS(SELECT fk_goods_outwards FROM n_a_goods_lost WHERE fk_goods_outwards = ngo.idapp) THEN 1
WHEN EXISTS(SELECT fk_goods_outwards FROM n_a_goods_return WHERE fk_goods_outwards = ngo.idapp) THEN 1
ELSE 0
END AS hasRefData
FROM n_a_goods g INNER JOIN n_a_goods_outwards ngo ON ngo.fk_goods = g.IDApp \
INNER JOIN employee emp ON emp.IDApp = ngo.fk_employee \
LEFT OUTER JOIN (SELECT IDApp, NIK,employee_name FROM employee)emp1 ON emp1.IDApp = ngo.fk_sender \
LEFT OUTER JOIN (SELECT IDApp,NIK,employee_name FROM employee)emp2 ON emp2.IDApp = ngo.FK_ResponsiblePerson
LEFT OUTER JOIN (SELECT IDApp,NIK,employee_name FROM employee)emp3 ON emp3.IDApp = ngo.fk_usedemployee WHERE ngo.idapp = %s"""
cur.execute(Query, [idapp])
data = query.dictfetchall(cur)
cur.close()
return data
def Get_Priviledge_Sys(self, user_id):
cur = connection.cursor()
Query = """SELECT ps.idapp, pf.form_name, ps.permission, ps.inactive, ps.createddate,
ps.createdby FROM n_a_sys_priviledge ps INNER JOIN n_a_priviledge_form pf ON
ps.fk_pform = pf.idapp WHERE ps.user_id=%(User_id)s ORDER BY pf.form_name_ori"""
cur.execute(Query, {'User_id': user_id})
return query.dictfetchall(cur)
def PopulateQuery(self,
columnKey,
ValueKey,
is_parent,
serialnumber=None,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar,
sidx='idapp',
sord='desc'):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """
SELECT ac.idapp, CONCAT(g.goodsname, ' ',g.brandname) as goods, ac.typeapp,
g.itemcode, g.depreciationmethod, ac.serialnumber, ac.year, ac.startdate,
ac.datedepreciation, ac.depr_expense, ac.depr_accumulation, ac.bookvalue,
ac.createddate, ac.createdby FROM n_a_acc_fa ac
INNER JOIN n_a_goods g ON ac.fk_goods = g.IDApp
WHERE ac.IsParent = """ + str(is_parent) + " AND "
if is_parent:
Query = Query + columnKey + rs.Sql(
) + " ORDER BY " + sidx + ' ' + sord
else:
Query = Query + """ac.serialnumber = '{}'""".format(serialnumber)
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def getReportAdHoc(self, idapp):
# """main_display_add_hoc = ['GoodsName', 'BrandName', 'SerialNumber', 'Type',
# 'DateReleased', 'ToEmployee', 'Equipment', 'Descriptions', 'Conditions', 'Eks_Employee', 'Sender']"""
# #buat dulu subquery
# to_employee = super(NA_BR_Goods_Outwards, self).get_queryset().filter(fk_employee=OuterRef('pk')).values('fk_employee')[:1]
# qs = super(NA_BR_Goods_Outwards, self).get_queryset() \
# .filter(refno__iexact=RefNo) \
# .select_related('n_a_goods','employee')
cur = connection.cursor()
Query = """SELECT g.GoodsName,IFNULL(ngd.brandName,g.brandName) AS BrandName,ngo.SerialNumber,ngd.TypeApp AS `Type`,
ngo.DateReleased,Emp1.employee_name as ToEmployee,ngo.Descriptions,ngo.Equipment_Desc AS Equipment,
CASE ngo.IsNew WHEN 0 THEN 'Bekas' WHEN 1 THEN 'Baru' ELSE 'Unknown' END AS Conditions,
emp2.employee_name AS Eks_Employee,emp3.employee_name AS Sender
FROM n_a_goods_outwards ngo INNER JOIN n_a_goods g ON ngo.fk_goods = g.IDApp
INNER JOIN n_a_goods_receive ngr ON ngr.FK_goods = ngo.FK_Goods
INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp
AND ngo.SerialNumber = ngd.SerialNumber
LEFT OUTER JOIN(SELECT IDApp, employee_name FROM employee)emp1 ON emp1.IDApp = ngo.fk_employee
LEFT OUTER JOIN(SELECT IDApp,employee_name FROM employee)emp2 ON emp2.IDApp = ngo.FK_UsedEmployee
LEFT OUTER JOIN(SELECT IDApp,employee_name FROM employee)emp3 ON emp3.IDApp = ngo.fk_sender
WHERE ngo.IDApp = %s"""
cur.execute(Query, [idapp])
data = query.dictfetchall(cur)
cur.close()
return data
def populate_query(self, columnKey, ValueKey, criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
cur = connection.cursor()
employee_temp_list = [
'employee_temp1',
'employee_temp2',
'employee_temp3',
'employee_temp4'
]
query_string = """
CREATE TEMPORARY TABLE {table_name} ENGINE=InnoDB AS
(SELECT idapp, employee_name FROM employee)
"""
for table_name in employee_temp_list:
cur.execute(
query_string.format(table_name=table_name)
)
query_string = """
CREATE TEMPORARY TABLE IF NOT EXISTS T_Outwards_GA ENGINE=InnoDB AS(
SELECT ngo.idapp, ngo.typeapp, ngo.isnew, ngo.daterequest,
ngo.datereleased, ngo.lastinfo, ngo.descriptions, ngo.equipment,
ngo.add_equipment, g.goodsname,
emp1.employee_name, emp2.employee_name AS used_employee,
emp3.employee_name AS resp_employee, emp4.employee_name AS sender
FROM n_a_ga_outwards AS ngo
LEFT OUTER JOIN
(SELECT idapp, goodsname FROM n_a_goods) AS g ON ngo.fk_goods = g.idapp
LEFT OUTER JOIN
employee_temp1 AS emp1
ON ngo.fk_employee = emp1.idapp
LEFT OUTER JOIN
employee_temp2 AS emp2
ON ngo.fk_usedemployee = emp2.idapp
LEFT OUTER JOIN
employee_temp3 AS emp3
ON ngo.fk_responsibleperson = emp3.idapp
LEFT OUTER JOIN
employee_temp4 AS emp4
ON ngo.fk_sender = emp4.idapp
""" + ")"
cur.execute(query_string)
query_string = """
SELECT * FROM T_Outwards_GA
"""
result = query.dictfetchall(cur)
employee_temp_list = ",".join(employee_temp_list)
query_string = """
DROP TEMPORARY TABLE IF EXISTS
""" + employee_temp_list
"""
buat function untuk cek ..!!!
dicek di log_event, apakah ada yg merubah
data employee, jika ada: maka delete temporary table
"""
cur.execute(query_string)
return result
def retrieve_data(self, idapp):
cur = connection.cursor()
query_string = """
SELECT ngo.idapp, ngo.fk_app, ngo.fk_goods, ngo.fk_receive, g.itemcode,
CONCAT(g.goodsname, ' ', ngr.brand, ' ', ngr.model) AS goodsname, ngr.typeapp,
ngr.colour, ngr.invoice_no, ngr.year_made,
emp1.idapp AS employee, emp1.nik AS employee_nik, emp1.employee_name,
emp2.idapp AS used_by, emp2.nik AS used_by_nik, emp2.employee_name AS used_by_name,
emp3.idapp AS resp_employee, emp3.nik AS resp_employee_nik, emp3.employee_name
AS resp_employee_name, emp4.idapp AS sender, emp4.nik AS sender_nik,
emp4.employee_name AS sender_name, ngo.isnew, ngo.daterequest, ngo.datereleased,
eq.equipment, add_eq.add_equipment, ngo.descriptions
FROM n_a_ga_outwards AS ngo
INNER JOIN
(SELECT idapp, itemcode, goodsname FROM n_a_goods) AS g ON ngo.fk_goods = g.idapp
INNER JOIN n_a_ga_receive AS ngr ON ngo.fk_receive = ngr.idapp
INNER JOIN n_a_ga_vn_history ngh ON ngo.fk_app = ngh.idapp
LEFT OUTER JOIN
(SELECT idapp, nik, employee_name FROM employee) AS emp1
ON ngo.fk_employee = emp1.idapp
LEFT OUTER JOIN
(SELECT idapp, nik, employee_name FROM employee) AS emp2
ON ngo.fk_usedemployee = emp2.idapp
LEFT OUTER JOIN
(SELECT idapp, nik, employee_name FROM employee) AS emp3
ON ngo.fk_responsibleperson = emp3.idapp
LEFT OUTER JOIN
(SELECT idapp, nik, employee_name FROM employee) AS emp4
ON ngo.fk_sender = emp4.idapp
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.idapp SEPARATOR ',') as equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS eq
ON ngo.idapp = eq.nagaoutwards_id
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.idapp SEPARATOR ',') as add_equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_add_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS add_eq
ON ngo.idapp = add_eq.nagaoutwards_id
WHERE ngo.idapp = %(idapp)s
"""
cur.execute(query_string, {
'idapp': idapp
})
result = query.dictfetchall(cur)
if result:
result = result[0]
return Data.Success, result
def populate_query(self, columnKey, ValueKey, criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar, sidx='idapp', sord='desc'):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
cur = connection.cursor()
query_string = """
CREATE TEMPORARY TABLE IF NOT EXISTS T_Outwards_GA ENGINE=InnoDB AS(
SELECT ngo.idapp, ngo.isnew, ngo.daterequest,
ngo.datereleased, ngo.lastinfo, g.goodsname,
ngr.brand, ngr.typeapp, ngr.invoice_no, ngh.reg_no,
emp1.employee_name, emp2.used_employee,
emp3.resp_employee, emp4.sender,
eq.equipment, add_eq.add_equipment, ngo.createddate, ngo.createdby,
ngo.descriptions
FROM n_a_ga_outwards AS ngo
INNER JOIN
(SELECT idapp, goodsname FROM n_a_goods) AS g ON ngo.fk_goods = g.idapp
INNER JOIN n_a_ga_receive AS ngr ON ngo.fk_receive = ngr.idapp
INNER JOIN n_a_ga_vn_history ngh ON ngo.fk_app = ngh.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name FROM employee) AS emp1
ON ngo.fk_employee = emp1.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS used_employee FROM employee) AS emp2
ON ngo.fk_usedemployee = emp2.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS resp_employee FROM employee) AS emp3
ON ngo.fk_responsibleperson = emp3.idapp
LEFT OUTER JOIN
(SELECT idapp, employee_name AS sender FROM employee) AS emp4
ON ngo.fk_sender = emp4.idapp
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.nameapp SEPARATOR ', ') as equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS eq
ON ngo.idapp = eq.nagaoutwards_id
LEFT OUTER JOIN (
SELECT GROUP_CONCAT(na_eq.nameapp SEPARATOR ', ') as add_equipment, eq.nagaoutwards_id
FROM n_a_equipment AS na_eq INNER JOIN n_a_ga_outwards_add_equipment
AS eq ON na_eq.idapp = eq.nagoodsequipment_id
GROUP BY eq.nagaoutwards_id
) AS add_eq
ON ngo.idapp = add_eq.nagaoutwards_id
WHERE """
query_string = query_string + columnKey + rs.Sql() + " ORDER BY " + \
sidx + ' ' + sord + ")"
cur.execute(query_string)
query_string = """
SELECT * FROM T_Outwards_GA
"""
cur.execute(query_string)
result = query.dictfetchall(cur)
cur.execute('DROP TEMPORARY TABLE T_Outwards_GA')
return result
def search_M_ByForm(self, value):
cur = connection.cursor()
Query = """SELECT g.idapp,g.itemcode,CONCAT(g.goodsname, ' ',g.brandname, ' ',grt.typeapp) AS goods,grt.serialnumber,
IF(NOW() <= grd.endofwarranty,'True','False') AS still_guarantee,grt.conditions,grt.minusdesc FROM n_a_goods_return grt INNER JOIN n_a_goods g ON grt.fk_goods = g.idapp
INNER JOIN n_a_goods_receive gr ON g.idapp = gr.fk_goods INNER JOIN n_a_goods_receive_detail grd ON gr.idapp = grd.fk_app
AND grt.serialnumber = grd.serialnumber WHERE NOT EXISTS (SELECT m.fk_goods FROM n_a_maintenance m WHERE m.fk_goods = g.idapp)
AND grt.conditions <> 'W' AND CONCAT(g.goodsname, ' ',g.brandname, ' ',g.typeapp) LIKE '%{0}%'""".format(
value)
cur.execute(Query)
result = query.dictfetchall(cur)
connection.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = ''
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def retriveData(self, idapp):
if self.dataExist(idapp=idapp):
cur = connection.cursor()
Query = """SELECT m.fk_goods,g.itemcode, CONCAT(g.goodsname, ' ',g.brandname) as goods,m.typeapp AS typeApp,m.serialnumber AS serialNum,grt.minusdesc AS minus, m.requestdate,
m.startdate, m.isstillguarantee, m.expense, m.maintenanceby,m.personalname, m.enddate, m.issucced, m.descriptions FROM n_a_maintenance m
INNER JOIN n_a_goods g ON m.fk_goods = g.idapp INNER JOIN n_a_goods_return grt on g.idapp = grt.fk_goods AND grt.serialnumber = m.serialnumber
WHERE m.idapp = %(IDApp)s"""
cur.execute(Query, {'IDApp': idapp})
result = query.dictfetchall(cur)
connection.close()
return (Data.Success, result)
else:
return (Data.Lost, )
def retriveData(self, idapp):
cur = connection.cursor()
if self.dataExist(idapp=idapp):
Query = """SELECT gls.fk_goods,g.itemcode, CONCAT(g.goodsname, ' ',g.brandname) AS goods, gls.typeApp, gls.serialNumber, empl1.nik_used, empl1.empl_used,empl1.fk_usedemployee,empl2.nik_resp,
empl2.empl_resp,empl3.fk_lostby,empl3.nik_lostby, empl3.empl_lostby, gls.descriptions FROM n_a_goods_lost gls INNER JOIN n_a_goods g ON
gls.fk_goods = g.idapp LEFT OUTER JOIN (SELECT idapp AS fk_usedemployee,nik AS nik_used,employee_name as empl_used FROM employee) AS empl1 ON gls.fk_usedemployee = empl1.fk_usedemployee
LEFT OUTER JOIN (SELECT idapp, nik AS nik_resp, employee_name AS empl_resp FROM employee) AS empl2 ON gls.fk_responsibleperson = empl2.idapp
LEFT OUTER JOIN (SELECT idapp AS fk_lostby, nik AS nik_lostby, employee_name AS empl_lostby FROM employee) AS empl3 ON gls.fk_lostby = empl3.fk_lostby
WHERE gls.idapp = %s"""
cur.execute(Query, [idapp])
result = query.dictfetchall(cur)[0]
return ('success', result)
else:
return ('Lost', )
def getGoods_data(self, idapp, serialnumber):
cur = connection.cursor()
if self.dataExist(serialnumber=serialnumber):
return (Data.Exists, Message.get_exists_info(self.get_createddate(serialnumber)['createddate']))
else:
Query = """SELECT g.idapp,g.itemcode,g.goodsname,g.brandname,grt.typeapp, grt.serialnumber,grt.minusdesc, IF(NOW() <= grd.endofwarranty, 'True','False')
AS still_guarantee FROM n_a_goods_return grt INNER JOIN n_a_goods g ON grt.fk_goods = g.idapp INNER JOIN n_a_goods_receive gr ON g.idapp = gr.fk_goods
INNER JOIN n_a_goods_receive_detail grd ON gr.idapp = grd.fk_app AND grd.serialnumber = grt.serialnumber WHERE g.idapp = %s"""
cur.execute(Query, [idapp])
result = query.dictfetchall(cur)
connection.close()
if len(result) == 0:
return (Data.Empty,)
return (Data.Success, result[0])
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar,
sidx='idapp',
sord='desc'):
filterfield = columnKey
if criteria == CriteriaSearch.NotEqual or criteria == CriteriaSearch.NotIn:
if criteria == CriteriaSearch.NotIn:
filterfield = columnKey + '__in'
else:
filterfield = columnKey + '__iexact'
elif criteria == CriteriaSearch.Equal:
filterfield = columnKey + '__exact'
elif criteria == CriteriaSearch.Greater:
filterfield = columnKey + '__gt'
elif criteria == CriteriaSearch.GreaterOrEqual:
filterfield = columnKey + '__gte'
elif criteria == CriteriaSearch.In:
filterfield = columnKey + '__in'
elif criteria == CriteriaSearch.Less:
filterfield = columnKey + '__lt'
elif criteria == CriteriaSearch.LessOrEqual:
filterfield = columnKey + '__lte'
elif criteria == CriteriaSearch.Like:
filterfield = columnKey + '__icontains'
cur = connection.cursor()
cur.execute("DROP TEMPORARY TABLE IF EXISTS T_GoodsLost_Manager")
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """CREATE TEMPORARY TABLE T_GoodsLost_Manager ENGINE=InnoDB AS(SELECT gls.idapp, gls.fromgoods, gls.serialnumber,
em.employee_name AS lost_by,gls.datelost, gls.reason,gls.descriptions, gls.createddate, gls.createdby,
CONCAT(g.goodsname, ' ',g.brandname, ' ',gls.typeapp) as goods, g.itemcode,emp1.used_by,emp2.resp_person
FROM n_a_goods_lost gls INNER JOIN n_a_goods g ON gls.fk_goods = g.idapp LEFT JOIN employee em ON gls.fk_lostby = em.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS used_by FROM employee) AS emp1 ON gls.fk_usedby = emp1.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS resp_person FROM employee) AS emp2 ON gls.fk_responsibleperson = emp2.idapp
WHERE """
Query = Query + columnKey + rs.Sql(
) + " ORDER BY " + sidx + " " + sord + ")"
cur.execute(Query)
cur.execute("""SELECT EXISTS(SELECT idapp FROM T_GoodsLost_Manager)""")
if cur.fetchone()[0] == 0:
return Data.Empty
Query = """SELECT * FROM T_GoodsLost_Manager"""
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def getGoods_data(self, IDApp):
cur = connection.cursor()
Query = """SELECT g.IDApp,grd.typeapp,CONCAT(g.goodsname, ' ',grd.brandname) as goods,
g.economiclife,grd.priceperunit as price_orig,grd.serialnumber,
CONCAT('Rp ',FORMAT(g.priceperunit,2,'de_DE')) as price_label,g.depreciationmethod
AS depr_method,g.createddate AS startdate,DATE_ADD(g.createddate,
INTERVAL SUM(g.economiclife*12) MONTH) as enddate FROM n_a_goods g
INNER JOIN n_a_goods_receive gr ON g.idapp = gr.fk_goods INNER JOIN
n_a_goods_receive_detail grd ON gr.idapp = grd.fk_app WHERE NOT EXISTS
(SELECT ac.FK_Goods FROM n_a_acc_fa ac WHERE ac.serialnumber = grd.serialnumber)
AND g.IDApp = %s"""
cur.execute(Query, [IDApp])
result = query.dictfetchall(cur)
connection.close()
return result
def retriveData(self, IDApp):
cur = connection.cursor()
Query = """SELECT ac.fk_goods,g.itemcode,g.brandname,g.goodsname AS goods_name,
ac.year,ac.startdate,
DATE_ADD(ac.startdate, INTERVAL SUM(g.economiclife*12) MONTH) as enddate,
CONCAT('Rp ',FORMAT(ac.depr_expense,2,'de_DE')) AS depr_expense,
CONCAT('Rp ',FORMAT(ac.depr_accumulation,2,'de_DE')) AS depr_accumulation,
CONCAT('Rp ',FORMAT(ac.bookvalue,2,'de_DE')) AS bookvalue,
CONCAT('Rp ',FORMAT(g.priceperunit,2,'de_DE')) AS price,g.economiclife FROM n_a_acc_fa ac
INNER JOIN n_a_goods g ON ac.fk_goods = g.idapp WHERE ac.idapp = %(IDApp)s"""
Params = {'IDApp': IDApp}
cur.execute(Query, Params)
result = query.dictfetchall(cur)
connection.close()
return result
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """SELECT m.idapp, m.requestdate, m.startdate, m.isstillguarantee, m.expense, m.maintenanceby, m.personalname,m.enddate,
g.itemcode,CONCAT(g.goodsname, ' ',g.brandname, ' ',m.typeapp) AS goods,m.serialnumber, m.issucced, m.descriptions, m.createddate,
m.createdby FROM n_a_maintenance m INNER JOIN n_a_goods g ON m.fk_goods = g.idapp WHERE """ + columnKey + rs.Sql(
)
cur.execute(Query)
result = query.dictfetchall(cur)
connection.close()
return result
def searchAcc_ByForm(self, q=None, idapp=None):
cur = connection.cursor()
query_string = """
CREATE TEMPORARY TABLE T_search_acc_fa ENGINE=InnoDB AS (
SELECT gr.idapp,g.itemcode,
CONCAT(g.goodsname, ' ',grd.brandname, ' ',IFNULL(grd.typeapp, ' ')) as goods,
grd.serialnumber, gr.datereceived AS startdate, grd.idapp AS idapp_detail_receive,
g.depreciationmethod, grd.priceperunit, g.economiclife, g.idapp AS fk_goods, grd.typeapp
FROM n_a_goods g INNER JOIN n_a_goods_receive gr
ON g.idapp = gr.fk_goods INNER JOIN n_a_goods_receive_detail grd
ON gr.idapp = grd.fk_app WHERE NOT EXISTS (SELECT ac.fk_goods FROM n_a_acc_fa ac
WHERE ac.serialnumber = grd.serialnumber)"""
query_param = {}
if q is not None:
query_string += ")"
cur.execute(query_string) # create temporary table
query_string = """
SELECT * FROM T_search_acc_fa saf WHERE
saf.goods LIKE %(q)s
OR saf.itemcode LIKE %(q)s OR saf.serialnumber LIKE %(q)s OR saf.depreciationmethod
LIKE %(q)s
"""
query_param.update({'q': '%' + q + '%'})
elif idapp is not None:
if isinstance(idapp, list):
idapp = ','.join(idapp)
query_string += """
AND grd.idapp IN ({idapp})
""".format(idapp=idapp)
else:
query_string += """
AND grd.idapp = %(idapp_detail_receive)s
"""
query_param.update({'idapp_detail_receive': idapp})
query_string += ")"
cur.execute(query_string, query_param) # create temporary table
query_string = """SELECT * FROM T_search_acc_fa"""
query_param.clear()
cur.execute(query_string, query_param)
result = query.dictfetchall(cur)
query_string = """
DROP TEMPORARY TABLE T_search_acc_fa
"""
cur.execute(query_string)
cur.close()
return result
def retriveData(self,idapp):
cur = connection.cursor()
if self.dataExist(idapp=idapp):
Query = """SELECT gls.idapp, gls.fk_goods,g.itemcode, CONCAT(g.goodsname, ' ',grd.brandname) AS goods, grd.typeApp, gls.serialNumber,gls.datelost,empl1.fk_usedby, empl1.nik_used, empl1.empl_used,empl2.fk_responsibleperson,empl2.nik_resp,
empl2.empl_resp,empl3.fk_lostby,empl3.nik_lostby, empl3.empl_lostby,gls.fromgoods AS fk_fromgoods,gls.status AS status_goods,gls.reason,gls.fk_goods_lending,gls.fk_goods_outwards,gls.fk_goods_return,gls.fk_maintenance, gls.descriptions FROM n_a_goods_lost gls INNER JOIN n_a_goods g ON
gls.fk_goods = g.idapp INNER JOIN n_a_goods_receive_detail grd ON gls.serialnumber=grd.serialnumber LEFT OUTER JOIN (SELECT idapp AS fk_usedby,nik AS nik_used,employee_name as empl_used FROM employee) AS empl1 ON gls.fk_usedby = empl1.fk_usedby
LEFT OUTER JOIN (SELECT idapp AS fk_responsibleperson, nik AS nik_resp, employee_name AS empl_resp FROM employee) AS empl2 ON gls.fk_responsibleperson = empl2.fk_responsibleperson
LEFT OUTER JOIN (SELECT idapp AS fk_lostby, nik AS nik_lostby, employee_name AS empl_lostby FROM employee) AS empl3 ON gls.fk_lostby = empl3.fk_lostby
WHERE gls.idapp = %s"""
cur.execute(Query, [idapp])
if cur.rowcount > 0:
result = query.dictfetchall(cur)[0]
return ('success', result)
else:
return ('unknown result',)
else:
return ('Lost',)
def PopulateQuery(self,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
cur = connection.cursor()
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = """SELECT ngr.idapp,ngr.datereturn,ngr.conditions,ngr.iscompleted,ngr.minusDesc,ngr.typeapp,ngr.serialnumber,
ngr.descriptions, ngr.createddate, ngr.createdby, CONCAT(g.goodsname, ' ',g.brandname,' ',g.typeapp) AS goods,emp1.fromemployee,
emp2.usedemployee FROM n_a_goods_return ngr INNER JOIN n_a_goods g ON ngr.fk_goods = g.idapp LEFT OUTER JOIN
(SELECT idapp, employee_name AS fromemployee FROM employee) AS emp1 ON ngr.fk_fromemployee = emp1.idapp
LEFT OUTER JOIN (SELECT idapp,employee_name AS usedemployee FROM employee) AS emp2 ON ngr.fk_usedemployee = emp2.idapp
WHERE """ + columnKey + rs.Sql()
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def retriveData(self, idapp):
cur = connection.cursor()
Query = """SELECT m.fk_goods,g.itemcode, CONCAT(g.goodsname, ' ',g.brandname, ' ', m.typeapp) as goods,m.typeapp AS typeApp,m.serialnumber AS serialNum,grt.minusdesc AS minus, m.requestdate,
m.startdate, m.isstillguarantee, m.expense, m.maintenanceby,m.personalname, m.enddate,m.isfinished, m.issucced, m.descriptions,
CASE WHEN (EXISTS(SELECT FK_Goods FROM n_a_disposal WHERE FK_Goods = m.fk_goods AND SerialNumber = m.serialnumber)) THEN 'True'
WHEN (EXISTS(SELECT FK_Goods FROM n_a_goods_deletion WHERE FK_Goods = m.fk_goods AND SerialNumber = m.serialnumber)) THEN 'True'
WHEN (EXISTS(SELECT FK_Goods FROM n_a_goods_lending WHERE FK_Goods = m.fk_goods AND FK_Maintenance = m.IDApp)) THEN 'True'
WHEN (EXISTS(SELECT FK_Goods FROM n_a_goods_lost WHERE FK_Goods = m.fk_goods AND SerialNumber = m.serialnumber)) THEN 'True'
WHEN (EXISTS(SELECT FK_Goods FROM n_a_goods_outwards WHERE FK_Goods = m.fk_goods AND FK_FromMaintenance = m.IDApp)) THEN 'True'
ELSE 'False'
END AS hasRefData FROM n_a_maintenance m
INNER JOIN n_a_goods g ON m.fk_goods = g.idapp INNER JOIN n_a_goods_return grt on g.idapp = grt.fk_goods AND grt.serialnumber = m.serialnumber
WHERE m.idapp = %(IDApp)s"""
cur.execute(Query, {'IDApp': idapp})
result = query.dictfetchall(cur)
connection.close()
return (Data.Success, result)
def retrieveData(self, idapp):
if self.dataExists(idapp=idapp):
if self.hasRef(idapp):
return (Data.HasRef, Message.HasRef_edit)
cur = connection.cursor()
query_string = """
CREATE TEMPORARY TABLE T_form_ga_receive ENGINE=InnoDB AS(
SELECT ngr.idapp, ngr.fk_goods, g.itemcode, g.goodsname, s.suppliercode,
s.suppliername, ngr.fk_p_r_by AS pr_by, emp1.pr_by_nik, emp1.pr_by_name,
ngr.fk_receivedby AS received_by, emp2.received_by_nik, emp2.received_by_name,
DATE_FORMAT(ngr.datereceived, \'%%d/%%m/%%Y\') AS datereceived, ngr.brand,
ngr.invoice_no, ngr.typeapp, ngr.machine_no,
ngr.chassis_no, DATE_FORMAT(ngr.year_made, \'%%Y\') AS year_made, ngr.colour,
ngr.model, ngr.kind, ngr.cylinder,
ngr.fuel, ngr.price, ngr.descriptions, ngh.reg_no,
DATE_FORMAT(ngh.date_reg, \'%%d/%%m/%%Y\') AS date_reg,
DATE_FORMAT(ngh.expired_reg, \'%%d/%%m/%%Y\') AS expired_reg,
DATE_FORMAT(ngh.bpkb_expired, \'%%d/%%m/%%Y\') AS bpkb_expired,
ngh.descriptions AS remark
FROM n_a_ga_receive AS ngr
INNER JOIN n_a_goods AS g ON ngr.fk_goods = g.idapp
INNER JOIN n_a_supplier AS s ON ngr.fk_supplier = s.suppliercode
LEFT OUTER JOIN (
SELECT idapp, nik AS pr_by_nik, employee_name AS pr_by_name
FROM employee
) AS emp1 ON ngr.fk_p_r_by = emp1.idapp
LEFT OUTER JOIN (
SELECT idapp, nik AS received_by_nik, employee_name AS received_by_name
FROM employee
) AS emp2 ON ngr.fk_receivedby = emp2.idapp
LEFT JOIN n_a_ga_vn_history AS ngh ON ngr.idapp = ngh.fk_app
WHERE ngr.idapp = %(idapp)s
)
"""
cur.execute(query_string, {'idapp': idapp})
query_string = """
SELECT * FROM T_form_ga_receive
"""
cur.execute(query_string)
result = query.dictfetchall(cur)[0]
return (Data.Success, result)
else:
return (Data.Lost,
Message.get_lost_info(pk=idapp,
table='n_a_goods_receive_other'))
def getGoods_data(self, idapp, fromgoods):
cur = connection.cursor()
if fromgoods == 'GO':
Query = """SELECT ngo.idapp,CONCAT(g.goodsname,' ',g.brandname,' ',ngo.typeapp) AS goods, ngo.serialnumber,g.itemcode,
@fromgoods := 'GO' AS fromgoods,emp1.idapp_used_by,emp1.used_by,emp1.nik_used_by FROM n_a_goods_outwards ngo
INNER JOIN n_a_goods g ON ngo.fk_goods = g.idapp LEFT OUTER JOIN (SELECT idapp AS idapp_used_by,nik AS nik_used_by,
employee_name AS used_by FROM employee) AS emp1 ON ngo.fk_employee = emp1.idapp_used_by WHERE
ngo.idapp = %(IDApp)s"""
elif fromgoods == 'GL':
Query = """SELECT ngl.idapp,CONCAT(g.goodsname,' ',g.brandname,' ',ngl.typeapp) AS goods, ngl.serialnumber,g.itemcode,
@fromgoods := 'GL' AS fromgoods,emp1.idapp_used_by,emp1.used_by,emp1.nik_used_by FROM n_a_goods_lending ngl
INNER JOIN n_a_goods g ON ngl.fk_goods = g.idapp LEFT OUTER JOIN (SELECT idapp AS idapp_used_by,nik AS nik_used_by,
employee_name AS used_by FROM employee) AS emp1 ON ngl.fk_employee = emp1.idapp_used_by WHERE
ngl.idapp = %(IDApp)s"""
cur.execute(Query, {'IDApp': idapp})
result = query.dictfetchall(cur)
cur.close()
return (Data.Success, result)
def retrieveData(self, idapp):
cur = connection.cursor()
Query = """SELECT ngr.typeApp,ngr.serialNumber,ngr.fk_goods,ngr.datereturn,ngr.conditions,
ngr.minusDesc AS minus, ngr.iscompleted,ngr.fk_goods_outwards AS idapp_fk_goods_outwards,
ngr.fk_goods_lend AS idapp_fk_goods_outwards, ngr.descriptions,
CONCAT(g.goodsname, ' ',g.brandname, ' ',ngr.typeapp) AS goods,g.itemcode,emp1.fromemployee,
emp1.nik_fromemployee,emp1.idapp_fromemployee,emp2.usedemployee,emp2.nik_usedemployee,
emp2.idapp_usedemployee FROM n_a_goods_return ngr INNER JOIN n_a_goods g ON ngr.fk_goods = g.idapp
LEFT OUTER JOIN
(SELECT idapp AS idapp_fromemployee,nik AS nik_fromemployee,employee_name AS fromemployee FROM employee)
AS emp1 ON ngr.fk_fromemployee = emp1.idapp_fromemployee
LEFT OUTER JOIN
(SELECT idapp AS idapp_usedemployee, nik AS nik_usedemployee,employee_name AS usedemployee FROM employee)
AS emp2 ON ngr.fk_usedemployee = emp2.idapp_usedemployee WHERE ngr.idapp = %(IDApp)s"""
cur.execute(Query, {'IDApp': idapp})
result = query.dictfetchall(cur)
cur.close()
return result[0]
def search_ga_by_form(self, q):
cur = connection.cursor()
query_string = """
SELECT g.idapp, CONCAT(g.goodsname, ' ', ngr.brand, ' ', ngr.model) AS goods,
g.itemcode, ngh.reg_no, ngh.expired_reg, ngh.bpkb_expired, ngr.descriptions,
ngr.idapp AS fk_receive, ngh.idapp AS fk_app, ngr.typeapp, ngr.invoice_no,
DATE_FORMAT(ngr.year_made,'%%Y') AS year_made, ngr.colour,
CASE
WHEN EXISTS(
SELECT ngo.idapp FROM n_a_ga_outwards ngo WHERE ngo.fk_app = ngh.idapp
)
THEN '0'
ELSE '1'
END AS info_is_new,
CASE
WHEN EXISTS(
SELECT ngo.idapp FROM n_a_ga_outwards ngo WHERE ngo.fk_app = ngh.idapp
)
THEN '0'
ELSE 'not yet used'
END AS last_info
FROM n_a_ga_receive ngr INNER JOIN
n_a_goods g ON ngr.fk_goods = g.idapp INNER JOIN n_a_ga_vn_history ngh
ON ngr.idapp = ngh.fk_app
WHERE """
query_string += query.like(
query_param='q',
fields=[
'g.itemcode',
'g.goodsname',
'ngr.brand',
'ngr.model',
'ngh.reg_no',
'ngr.typeapp',
'ngr.invoice_no'
]
)
cur.execute(query_string, {
'q': ('%' + q + '%')
})
return query.dictfetchall(cur)
def SearchGoods_byForm(self, q):
cur = connection.cursor()
Query = """
(SELECT ngo.idapp,CONCAT(g.goodsname,' ',g.brandname,' ',ngo.typeapp) AS goods,
ngo.fk_goods, ngo.serialnumber, g.itemcode, ngo.typeapp, @fromgoods := 'GO' AS fromgoods
FROM n_a_goods_outwards ngo INNER JOIN n_a_goods g ON ngo.fk_goods = g.idapp
WHERE CONCAT(g.goodsname,' ',g.brandname,' ',ngo.typeapp)
LIKE \'{q}\' OR g.itemcode LIKE \'{q}\' or ngo.serialnumber LIKE \'{q}\'
AND NOT EXISTS (SELECT idapp FROM n_a_goods_return WHERE fk_goods_outwards = ngo.idapp))
UNION
(SELECT ngl.idapp,CONCAT(g.goodsname,' ',g.brandname,' ',ngl.typeapp) AS goods,
ngl.fk_goods, ngl.serialnumber, g.itemcode, ngl.typeapp, @fromgoods := 'GL' AS
fromgoods FROM n_a_goods_lending ngl INNER JOIN n_a_goods g ON ngl.fk_goods = g.idapp
WHERE CONCAT(g.goodsname,' ',g.brandname,' ',ngl.typeapp)
LIKE \'{q}\' OR g.itemcode LIKE \'{q}\' or ngl.serialnumber LIKE \'{q}\'
AND NOT EXISTS (SELECT idapp FROM n_a_goods_return WHERE fk_goods_lend = ngl.idapp))
""".format(q=('%' + q + '%'))
cur.execute(Query)
result = query.dictfetchall(cur)
cur.close()
return result
def search_ga_by_form(self, q):
cur = connection.cursor()
query_string = """
SELECT ngr.idapp, CONCAT(g.goodsname, ' ', ngr.brand, ' ', ngr.model) AS goods,
g.itemcode, ngh.reg_no, ngh.expired_reg, ngh.bpkb_expired, ngr.descriptions,
CASE
WHEN(
SELECT EXISTS(
SELECT ngo.idapp FROM n_a_ga_outwards ngo WHERE ngo.fk_app = ngh.idapp
)
)
THEN '1'
ELSE '0'
END AS info_is_new
FROM n_a_ga_receive ngr INNER JOIN
n_a_goods g ON ngr.fk_goods = g.idapp INNER JOIN n_a_ga_vn_history ngh
ON ngr.idapp = ngh.fk_app
"""
cur.execute(query_string)
return query.dictfetchall(cur)
def retrieveData(self, idapp):
if self.dataExists(idapp=idapp):
cur = connection.cursor()
Query = """SELECT ngr.idapp,ngr.refno,ngr.FK_goods AS idapp_fk_goods, g.itemcode
AS fk_goods, goodsname as goods_desc,g.economiclife,ngr.datereceived,
ngr.fk_suplier,sp.supliername,ngr.fk_ReceivedBy as idapp_fk_receivedby,
emp1.fk_receivedby,emp1.employee_received,ngr.FK_P_R_By AS idapp_fk_p_r_by,
emp2.fk_p_r_by,emp2.employee_pr,ngr.totalpurchase,ngr.totalreceived,
ngr.descriptions,ngr.descbysystem FROM n_a_goods_receive_other AS ngr
INNER JOIN n_a_suplier AS sp ON sp.SuplierCode = ngr.FK_Suplier
LEFT OUTER JOIN (SELECT IDApp,NIK AS fk_receivedby,employee_name AS
employee_received FROM employee) AS emp1 ON emp1.IDApp = ngr.FK_ReceivedBy
LEFT OUTER JOIN (SELECT IDApp,NIK AS fk_p_r_by,employee_name AS employee_pr
FROM employee) AS emp2 ON emp2.IDApp = ngr.FK_P_R_By
INNER JOIN n_a_goods as g ON g.IDApp = ngr.FK_goods WHERE ngr.IDApp = %(IDApp)s"""
cur.execute(Query, {'IDApp': idapp})
result = query.dictfetchall(cur)
return (Data.Success, result[0])
else:
return (Data.Lost,
Message.get_lost_info(pk=idapp,
table='n_a_goods_receive_other'))
def PopulateQuery(self,
orderFields,
sortIndice,
pageSize,
PageIndex,
userName,
columnKey,
ValueKey,
criteria=CriteriaSearch.Like,
typeofData=DataType.VarChar):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
if columnKey == 'goods':
colKey = 'g.goodsname'
elif columnKey == 'typeapp':
colKey = 'ngd.TypeApp'
elif columnKey == 'serialnumber':
colKey = 'ngd.serialnumber'
elif columnKey == 'daterequest':
colKey = 'nga.daterequest'
elif columnKey == 'datereleased':
colKey = 'nga.datereleased'
elif columnKey == 'isnew':
colKey = 'nga.isnew'
elif columnKey == 'for_employee':
colKey = 'e.employee_name'
elif columnKey == 'responsible_by':
colKey = 'emp1.responsible_by'
elif columnKey == 'senderby':
colKey = 'emp2.senderby'
elif columnKey == 'refgoodsfrom':
colKey = 'ref.refgoodsfrom'
elif columnKey == 'createdby':
colKey = 'nga.createdby'
elif columnKey == 'createddate':
colKey = 'nga.createddate'
elif columnKey == 'descriptions':
colKey = 'nga.descriptions'
Query = "DROP TEMPORARY TABLE IF EXISTS T_Outwards_Manager_" + userName
cur = connection.cursor()
cur.execute(Query)
Query = """ CREATE TEMPORARY TABLE T_Outwards_Manager_""" + userName + """ ENGINE=MyISAM AS (SELECT e.territory,nga.idapp,g.goodsname AS goods,ngd.TypeApp AS goodstype,ngd.serialnumber,nga.daterequest,nga.datereleased,
nga.isnew,nga.fk_employee,e.employee_name as for_employee,e.TelpHP AS mobile,nga.fk_usedemployee,
CASE
WHEN(nga.fk_usedemployee IS NOT NUll) THEN(SELECT employee_name FROM `employee` WHERE idapp = nga.fk_usedemployee LIMIT 1)
END AS eks_employee,nga.fk_responsibleperson,emp1.responsible_by,nga.fk_sender,emp2.senderby,nga.fk_stock,
ref.refgoodsfrom,nga.createdby,nga.createddate,nga.equipment_desc,nga.descriptions
FROM n_a_goods_outwards nga INNER JOIN n_a_goods g ON g.IDApp = nga.FK_Goods
INNER JOIN n_a_goods_receive ngr ON ngr.FK_goods = nga.FK_Goods
INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp
AND nga.SerialNumber = ngd.SerialNumber
INNER JOIN (SELECT ng.IDApp,CASE
WHEN (ng.FK_Receive IS NOT NULL) THEN 'Receive PR (New)'
WHEN (ng.FK_RETURN IS NOT NULL) THEN 'RETURN Eks Employee'
WHEN (ng.FK_FromMaintenance IS NOT NULL) THEN 'After Service(Maintenance)'
WHEN (ng.FK_Lending IS NOT NULL) THEN 'RETURN (After being Lent)'
ELSE 'Other (Uncategorized)'
END AS refgoodsfrom FROM n_a_goods_Outwards ng)ref ON Ref.IDApp = nga.IDApp
INNER JOIN employee e on e.IDApp = nga.FK_Employee
LEFT OUTER JOIN (SELECT idapp,employee_name AS responsible_by FROM employee) emp1 ON emp1.idapp = nga.FK_ResponsiblePerson
LEFT OUTER JOIN (SELECT idapp,employee_name AS senderby FROM employee) emp2 ON emp2.idapp = nga.FK_Sender WHERE """ + colKey + rs.Sql(
) + ")"
cur.execute(Query)
strLimit = '20'
if int(PageIndex) <= 1:
strLimit = '0'
else:
strLimit = str((int(PageIndex) - 1) * int(pageSize))
if orderFields != '':
#Query = """SELECT * FROM T_Receive_Manager """ + (("ORDER BY " + ",".join(orderFields)) if len(orderFields) > 1 else " ORDER BY " + orderFields[0]) + (" DESC" if sortIndice == "" else sortIndice) + " LIMIT " + str(pageSize*(0 if PageIndex <= 1 else PageIndex)) + "," + str(pageSize)
Query = """SELECT * FROM T_Outwards_Manager_""" + userName + """ ORDER BY """ + orderFields + (
" DESC" if sortIndice == "" else ' ' +
sortIndice) + " LIMIT " + strLimit + "," + str(pageSize)
else:
Query = """SELECT * FROM T_Outwards_Manager_""" + userName + """ ORDER BY IDApp LIMIT """ + strLimit + "," + str(
pageSize)
cur.execute(Query)
result = query.dictfetchall(cur)
#get countRows
Query = """SELECT COUNT(*) FROM T_Outwards_Manager_""" + userName
cur.execute(Query)
row = cur.fetchone()
totalRecords = row[0]
cur.close()
return (result, totalRecords)
