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)