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 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 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 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 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 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)
def PopulateQuery(self,orderFields,sortIndice,pageSize,PageIndex,userName,columnKey,ValueKey,criteria=CriteriaSearch.Like,typeofData=DataType.VarChar):
if columnKey == 'goodsname':
colKey = 'g.goodsname'
elif columnKey == 'typeapp':
colKey = 'gls.typeApp'
elif columnKey == 'serialnumber':
colKey = 'gls.serialnumber'
elif columnKey == 'itemcode':
colKey = 'g.itemcode'
elif columnKey == 'brandname':
colKey = 'ngd.BrandName'
elif columnKey == 'used_by':
colKey = 'emp1.used_by'
elif columnKey == 'lost_by':
colKey = 'em.lost_by'
elif columnKey == 'resp_person':
colKey = 'emp2.resp_person'
elif columnKey == 'descriptions':
colKey = 'gls.descriptions'
# 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_" + userName + """ ENGINE=MyISAM AS(SELECT gls.idapp,
CASE gls.fromgoods WHEN 'GO' then 'Goods Outwards' WHEN 'GL' THEN 'Goods Lending' WHEN 'GM' THEN 'Goods Lending' WHEN 'GR' THEN 'Goods Return' ELSE 'UNKNOWN' END AS fromgoods, gls.serialnumber,
em.employee_name AS lost_by,gls.datelost, gls.reason,gls.descriptions, gls.createddate, gls.createdby,
CONCAT(g.goodsname, ' ',ngd.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
INNER JOIN n_a_goods_receive ngr ON ngr.FK_Goods = gls.fk_goods INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp AND ngd.serialnumber = gls.serialnumber
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 """ + columnKey + rs.Sql() + ")"
cur.execute(Query)
strLimit = '20'#ambil yang paling kecil di grid
if int(PageIndex) <= 1:
strLimit = '0'
else:
strLimit = str((int(PageIndex)-1) * int(pageSize))
if orderFields != '':
Query = """SELECT * FROM T_GoodsLost_Manager_""" + userName + """ ORDER BY """ + orderFields + (" DESC" if sortIndice == "" else ' ' + sortIndice) + " LIMIT " + strLimit + "," + str(pageSize)
else:
Query = """SELECT * T_GoodsLost_Manager_""" + userName + """ ORDER BY IDApp LIMIT """ + strLimit + "," + str(pageSize)
cur.execute(Query)
result = query.dictfetchall(cur)
# get countRows
Query = """SELECT COUNT(*) FROM T_GoodsLost_Manager_""" + userName
cur.execute(Query)
row = cur.fetchone()
totalRecords = row[0]
cur.close()
return (result, totalRecords)
def PopulateQuery(self, orderFields, sortIndice, pageSize, PageIndex, userName, columnKey, ValueKey, criteria=CriteriaSearch.Like, typeofData=DataType.VarChar):
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
colKey = 'g.goodsname'
if columnKey == 'goods':
colKey = 'g.goodsname'
elif columnKey == 'typeapp':
colKey = 'm.TypeApp'
elif columnKey == 'serialnumber':
colKey = 'm.serialnumber'
elif columnKey == 'requestdate':
colKey = 'm.requestdate'
elif columnKey == 'startdate':
colKey = 'm.startdate'
elif columnKey == 'enddate':
colKey = 'm.enddate'
elif columnKey == 'isstillguarantee':
colKey = 'm.isstillguarantee'
elif columnKey == 'maintenanceby':
colKey = 'm.maintenanceby'
elif columnKey == 'personalname':
colKey = 'm.personalname'
elif columnKey == 'isfinished':
colKey = 'm.isfinished'
elif columnKey == 'issucced':
colKey = 'm.issucced'
elif columnKey == 'expense':
colKey = 'm.expense'
elif columnKey == 'createdby':
colKey = 'm.createdby'
elif columnKey == 'createddate':
colKey = 'm.createddate'
elif columnKey == 'descriptions':
colKey = 'm.descriptions'
cur = connection.cursor()
cur.execute(
"DROP TEMPORARY TABLE IF EXISTS T_Maintenance_Manager_" + userName)
rs = ResolveCriteria(criteria, typeofData, columnKey, ValueKey)
Query = "CREATE TEMPORARY TABLE T_Maintenance_Manager_" + userName + """ ENGINE=MyISAM AS(SELECT m.idapp, m.requestdate, m.startdate, m.isstillguarantee, m.expense, m.maintenanceby, m.personalname,m.enddate,
g.itemcode,CONCAT(g.goodsname, ' ',ngd.brandname, ' ',m.typeapp) AS goods,m.serialnumber, m.issucced,m.isfinished, m.descriptions, m.createddate,
m.createdby FROM n_a_maintenance m INNER JOIN n_a_goods g ON m.fk_goods = g.idapp
INNER JOIN n_a_goods_receive ngr ON ngr.fk_goods = g.IDApp INNER JOIN n_a_goods_receive_detail ngd ON ngd.FK_App = ngr.IDApp AND m.serialnumber = ngd.serialnumber WHERE """ + colKey + rs.Sql() + ")"
cur.execute(Query)
strLimit = '20' # ambil yang paling kecil di grid
if int(PageIndex) <= 1:
strLimit = '0'
else:
strLimit = str((int(PageIndex)-1) * int(pageSize))
if orderFields != '':
Query = "SELECT * FROM T_Maintenance_Manager_" + userName + " ORDER BY " + orderFields + \
(" DESC" if sortIndice == "" else ' ' + sortIndice) + \
" LIMIT " + strLimit + "," + str(pageSize)
else:
Query = "SELECT * T_Maintenance_Manager_" + userName + \
" ORDER BY IDApp LIMIT " + strLimit + "," + str(pageSize)
cur.execute(Query)
result = query.dictfetchall(cur)
# get countRows
Query = """SELECT COUNT(*) FROM T_Maintenance_Manager_""" + userName
cur.execute(Query)
row = cur.fetchone()
totalRecords = row[0]
cur.close()
return (result, totalRecords)
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