I would like to hire a PHP Developer
$10-30 USD
Paid on delivery
I have some MySQL statements I need created that I am having trouble, they are a little complex. I can provide the current statements I am using, example of output, and what I am, looking for.
Currently working statement:
select a.job_number, b.employee_end, b.qty_comp, b.`date`, a.job_scrap, a.job_time from ( select jobs.job_number, SUM([login to view URL]) as job_scrap, SUM(jobs.job_time) as job_time FROM jobs where jobs.job_number in ( SELECT j.job_number FROM jobs j WHERE j.moved_to = 'shipped' AND [login to view URL] BETWEEN '2017-01-01' AND '2017-01-01' AND j.qty_comp > 0 ) GROUP BY jobs.job_number ) as a, jobs b where a.job_number = b.job_number and b.moved_to = 'shipped' and [login to view URL] between %s AND %s and b.qty_comp > 0 group by b.job_number
example of output:
Job Number Qty Comp Date Shipped Job Time Total Cost
139153-1-1 14 2017-01-16 8,362 minutes / 139.37 hrs
156269-1-1 50 2017-01-17 1,060 minutes / 17.67 hrs
156984-1-1 600 2017-01-18 10,115 minutes / 168.58 hrs
158320 1 2017-01-18 45 minutes / 0.75 hrs
159114 200 2017-01-18 5 minutes / 0.08 hrs
160261 100 2017-01-17 10 minutes / 0.17 hrs
161321 50 2017-01-17 10 minutes / 0.17 hrs
161529 250 2017-01-17 10 minutes / 0.17 hrs
161713-1-1 10 2017-01-18 809 minutes / 13.48 hrs
I need to have total labor cost and total material cost, here are some of the statements I use in another part of my interface to achieve amounts:
Job Number / Qty Comp / Date Shipped / Job Time / Total Cost / Labor cost / material cost /
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
$colname_RS1 = "-1";
if (isset($_GET['job_number'])) {
$colname_RS1 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS1 = sprintf("SELECT job_number, cust, qty, inhands, event, moved_to, blank_type, cost, date_created, (qty * cost) AS blankprice
FROM prod_jobs
WHERE job_number = %s", GetSQLValueString($colname_RS1, "text"));
$RS1 = mysql_query($query_RS1, $JOB_TRACKER) or die(mysql_error());
$row_RS1 = mysql_fetch_assoc($RS1);
$totalRows_RS1 = mysql_num_rows($RS1);
$colname_RS2 = "-1";
if (isset($_GET['job_number'])) {
$colname_RS2 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS2 = sprintf("SELECT job_number, SUM(job_time), SUM(scrap), SUM(strikes) FROM jobs WHERE job_number = %s", GetSQLValueString($colname_RS2, "text"));
$RS2 = mysql_query($query_RS2, $JOB_TRACKER) or die(mysql_error());
$row_RS2 = mysql_fetch_assoc($RS2);
$totalRows_RS2 = mysql_num_rows($RS2);
/////////////////////////////////////////////////////////////////////////////////
$colname_RS3 = "-1";
if (isset($_GET['job_number'])) {
$colname_RS3 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS3 = sprintf("SELECT [login to view URL], SUM(emps.min_rate * jobs.job_time) AS SUMProduction_Total, jobs.job_number, jobs.employee_end FROM emps, jobs WHERE job_number = %s AND emps.employee=jobs.employee_end", GetSQLValueString($colname_RS3, "text"));
$RS3 = mysql_query($query_RS3, $JOB_TRACKER) or die(mysql_error());
$row_RS3 = mysql_fetch_assoc($RS3);
$totalRows_RS3 = mysql_num_rows($RS3);
////////////////////////////////////////////////////////////////////////////////
$colname_RS4 = "-1";
if (isset($_GET['job_number'])) {
$colname_RS4 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS4 = sprintf("SELECT job_number, employee, department, qty_start, qty_comp, date_created, job_time, scrap FROM jobs WHERE job_number = %s ORDER BY date_created DESC", GetSQLValueString($colname_RS4, "text"));
$RS4 = mysql_query($query_RS4, $JOB_TRACKER) or die(mysql_error());
$row_RS4 = mysql_fetch_assoc($RS4);
$totalRows_RS4 = mysql_num_rows($RS4);
////////////////////////////////////////////////////////////////////////////
$colname_RS5 = "-1";
if (isset($_GET['job_number'])) {
$colname_RS5 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS5 = sprintf("SELECT [login to view URL], (emps.min_rate) * (jobs.job_time) AS SUMProduction_Total, jobs.job_number, jobs.employee_end FROM emps, jobs WHERE job_number = %s AND emps.employee=jobs.employee_end ORDER BY employee_end DESC", GetSQLValueString($colname_RS5, "text"));
$RS5 = mysql_query($query_RS5, $JOB_TRACKER) or die(mysql_error());
$row_RS5 = mysql_fetch_array($RS5);
$totalRows_RS5 = mysql_num_rows($RS5);
///////////////////////////////////////////////////////////////////////////////
$colnamediecost_RS6 = "-1";
if (isset($_GET['job_number'])) {
$colnamediecost_RS6 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS6 = sprintf("SELECT sum(die_makers.job_time) as total,sum(die_makers.job_time)*(emps.min_rate) as cost,[login to view URL], [login to view URL], prod_jobs.job_number,die_makers.order_number FROM die_makers,prod_jobs, emps WHERE prod_jobs.job_number = %s AND prod_jobs.order_number=die_makers.order_number AND die_makers.employee=[login to view URL]", GetSQLValueString($colnamediecost_RS6, "text"));
$RS6 = mysql_query($query_RS6, $JOB_TRACKER) or die(mysql_error());
$row_RS6 = mysql_fetch_assoc($RS6);
$totalRows_RS6 = mysql_num_rows($RS6);
/////////////////////////////////////////////////////////////////////////
$colnameartcost_RS7 = "-1";
if (isset($_GET['job_number'])) {
$colnameartcost_RS7 = $_GET['job_number'];
}
mysql_select_db($database_JOB_TRACKER, $JOB_TRACKER);
$query_RS7 = sprintf("SELECT sum(art_dept.job_time) as total,sum(art_dept.job_time)*(emps.min_rate) as cost,[login to view URL], [login to view URL], prod_jobs.job_number,art_dept.order_number FROM art_dept,prod_jobs, emps WHERE prod_jobs.job_number =%s AND prod_jobs.order_number=art_dept.order_number AND art_dept.employee=[login to view URL]", GetSQLValueString($colnameartcost_RS7, "text"));
$RS7 = mysql_query($query_RS7, $JOB_TRACKER) or die(mysql_error());
$row_RS7 = mysql_fetch_assoc($RS7);
$totalRows_RS7 = mysql_num_rows($RS7);
/////////////////////////////////////////////////////////////////////////////
?>
<?PHP
$tpc=array("amt"=>$row_RS1['blankprice']+$row_RS5['SUMProduction_Total']+$row_RS6['cost']+$row_RS7['cost']);
?>
///////////////////////////////////////////////////////////////////////////////////
Project ID: #12834685
About the project
13 freelancers are bidding on average $62 for this job
Hello, sir. My ranking is TOP 5TH in freelancer.com as you can see my profile: (https://www.freelancer.com/u/kchg.html). I'd like to discuss with you in detail. Kind Regards
Hello! What is the current output of the SQL query you're using? With 98% to 99% completion rate, 900+ successfully completed projects, and a 4.99 reputation (maximum possible, 5.0) (can be verified on my profil More
I am ready to start your project.5+ years of web Development experience with expert level knowledge of frameworks & CORE EXPERTISE in CakePHP, laravel, Yii , Wordpress, HTML, CSS, Ajax. Commerce etc .. I have some ques More
Hey sir i can do this work as i am a custom PHP plus WordPress expert having an experience of 3 years. I can help you achieve this task as i have done similar tasks before and can do this work real quick for you and wi More
ve good expertise in PHP, Wordpress, Magento, Joomla. I have 7+ years of experience. I believe on time delivery with quality work. I believe in client satisfaction with good quality work. So believe on me to accom More