$operation[] = "SELECT '4' as Oper, It.internalId, -STIr.Qty Qty, STIr.Unit, STIr.TotalCost RowTotal, STI.TransDate, STI.TransDate as OperDate, STI.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM Item It JOIN StockTransformationItemInRow STIr ON STIr.ArtCode = It.Code INNER JOIN StockTransformation STI ON STIr.masterId = STI.internalId WHERE STI.Invalid = '0' AND STI.Status = '1' AND It.ItemGroup = 'MERC' AND STIr.ArtCode <> 'MIGRACION'";
$operation[] = "SELECT '5' as Oper, It.internalId, STIr.Qty Qty, STIr.Unit, STIr.TotalCost RowTotal, STI.TransDate, STI.TransDate as OperDate, STI.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM Item It JOIN StockTransformationItemOutRow STIr ON STIr.ArtCode = It.Code INNER JOIN StockTransformation STI ON STIr.masterId = STI.internalId WHERE STI.Invalid = '0' AND STI.Status = '1' AND It.ItemGroup = 'MERC' AND STIr.ArtCode <> 'MIGRACION'";
$operation[] = "SELECT '6' as Oper, I.internalId, -SMr.Qty Qty, SMr.Unit , IFNULL(SMr.Cost,0) RowTotal, SMF.TransDate, SMF.TransDate as OperDate, SMF.FrStockDepo StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM StockMovementRow SMr INNER JOIN StockMovement SMF ON SMr.masterId = SMF.internalId INNER JOIN Item I ON I.Code = SMr.ArtCode WHERE SMF.Invalid = '0' AND SMF.Status = '1' AND I.ItemGroup = 'MERC'AND SMr.ArtCode <> 'MIGRACION'";
$operation[] = "SELECT '7' as Oper, I.internalId, SMr.Qty Qty, SMr.Unit , IFNULL(SMr.Cost,0) RowTotal, SMF.TransDate, SMF.TransDate as OperDate, SMF.ToStockDepo StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM StockMovementRow SMr INNER JOIN StockMovement SMF ON SMr.masterId = SMF.internalId INNER JOIN Item I ON I.Code = SMr.ArtCode WHERE SMF.Invalid = '0' AND SMF.Status = '1' AND I.ItemGroup = 'MERC'AND SMr.ArtCode <> 'MIGRACION'";
$operation[] = "SELECT '8' as Oper, I.internalId, -SDr.Qty Qty, SDr.Unit, SDr.Cost Price , SD.TransDate, SD.TransDate as OperDate, SD.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM Item I JOIN StockDepreciationRow SDr ON (I.Code = SDr.ArtCode) INNER JOIN StockDepreciation SD ON SDr.masterId = SD.internalId WHERE SD.Invalid = '0' AND SD.Status = '1' AND I.ItemGroup = 'MERC'AND SDr.ArtCode <> 'MIGRACION'";
$operation[] = "SELECT '9' as Oper, TI.internalId, -Dr.Qty*Drow.Qty Qty, Dr.Unit, Dr.Cost Price, D.TransDate, D.DeliveryDate as OperDate, D.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM TransactionRecipe Dr INNER JOIN Delivery D ON Dr.OriginNr = D.SerNr INNER JOIN DeliveryRow Drow ON Drow.masterId = D.internalId AND Drow.Recipe = Dr.RecipeCode INNER JOIN Item TI ON TI.Code = Dr.ArtCode WHERE D.Invalid = '0' AND (D.Status = '2' OR D.Status = '1') AND TI.Code <> 'MIGRACION' AND TI.ItemGroup = 'MERC'";
$operation[] = "SELECT '10' as Oper, TI.internalId, -Ir.Qty*Irow.Qty Qty, Ir.Unit, Ir.Cost Price, Inv.TransDate, Inv.InvoiceDate as OperDate, Inv.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM TransactionRecipe Ir INNER JOIN Invoice Inv ON Ir.OriginNr = Inv.SerNr INNER JOIN InvoiceItemRow Irow ON Irow.masterId = Inv.internalId AND Irow.Recipe = Ir.RecipeCode INNER JOIN Item TI ON TI.Code = Ir.ArtCode WHERE Inv.Invalid = '0' AND Inv.Status = '1' AND TI.Code <> 'MIGRACION' AND TI.ItemGroup = 'MERC'";
$operation[] = "SELECT '11' as Oper, TI.internalId, -SDr.Qty*SDrow.Qty Qty, SDr.Unit, SDr.Cost Price, SD.TransDate, SD.TransDate as OperDate, SD.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM TransactionRecipe SDr INNER JOIN StockDepreciation SD ON SDr.OriginNr = SD.SerNr INNER JOIN StockDepreciationRow SDrow ON SDrow.masterId = SD.internalId AND SDrow.Recipe = SDr.RecipeCode INNER JOIN Item TI ON TI.Code = SDr.ArtCode WHERE SD.Invalid = '0' AND SD.Status = '1' AND TI.Code <> 'MIGRACION' AND TI.ItemGroup = 'MERC'";
$operation[] = "SELECT '12' as Oper, TI.internalId, RCr.Qty*RCrow.Qty Qty, RCr.Unit, RCr.Cost Price, RC.TransDate, RC.TransDate as OperDate, RC.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM TransactionRecipe RCr INNER JOIN ReturnCustomer RC ON RCr.OriginNr = RC.SerNr INNER JOIN ReturnCustomerItemRow RCrow ON RCrow.masterId = RC.internalId AND RCrow.Recipe = RCr.RecipeCode INNER JOIN Item TI ON TI.Code = RCr.ArtCode WHERE RC.Invalid = '0' AND RC.Status = '1' AND TI.Code <> 'MIGRACION' AND ActStock = '1' AND TI.ItemGroup = 'MERC'";
$operation[] = "SELECT '13' as Oper, TI.internalId, -Dr.Qty Qty, Dr.Unit, Dr.Cost Price, D.TransDate, IFNULL(D.DeliveryDate, D.TransDate) as OperDate, D.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM Item TI JOIN DeliveryRow Dr ON (TI.Code = Dr.ArtCode) INNER JOIN Delivery D ON Dr.masterId = D.internalId WHERE D.Invalid = '0' AND (D.Status = '2' ) AND TI.ItemGroup = 'MERC'AND Dr.ArtCode <> 'MIGRACION'";
// OR D.Status = '1' Picking
$operation[] = "SELECT '14' as Oper, TI.internalId, RCIr.Qty, RCIr.Unit, RCIr.Price, RC.TransDate, RC.TransDate as OperDate, RC.StockDepo, Currency, CurrencyRate, 1, 1 AS Enabled FROM Item TI JOIN ReturnCustomerItemRow RCIr ON (TI.Code = RCIr.ArtCode) INNER JOIN ReturnCustomer RC ON RCIr.masterId = RC.internalId WHERE RC.Invalid = '0' AND RC.Status = '1' AND TI.ItemGroup = 'MERC' AND TI.Code <> 'MIGRACION' AND ActStock = '1'";
$i = 1;
foreach ($operation as $oper) {
printf("StartingOper" . $i . " \n");
if (!insertOperation($myOrigin, $myDestination, $oper, $maxInsert)) {
printf("Failed\n");
exit(0);
}
$i++;
}
$myDestination->query("INSERT INTO SalesByDay select `Transaction`.`item_id` AS `item_id`,`Transaction`.`OperDate` AS `OperDate`,`Transaction`.`Unit` AS `Unit`,sum(`Transaction`.`Qty`) AS `Qty` from `Transaction` where ((`Transaction`.`Enable` = '1') and (`Transaction`.`Oper` = '13')) group by `Transaction`.`item_id`,`Transaction`.`OperDate`,`Transaction`.`Unit`");
printf('SalesByDay=' . $myDestination->affected_rows . "\n");
$myDestination->query("INSERT INTO SalesByDay select `Transaction`.`item_id` AS `item_id`,`Transaction`.`OperDate` AS `OperDate`,`Transaction`.`Unit` AS `Unit`,sum(`Transaction`.`Qty`) AS `Qty` from `Transaction` where ((`Transaction`.`Enable` = '1') and (`Transaction`.`Oper` = '14')) group by `Transaction`.`item_id`,`Transaction`.`OperDate`,`Transaction`.`Unit`");
printf('ReturnsSalesByDay=' . $myDestination->affected_rows . "\n");
printf("Bye\n");
$myOrigin->close();
$myDestination->close();
function insertOperation($origin, $destination, $query, $maxInsert)
{
$i = 0;
{
echo "performing select operation\r\n\r\n";
global $db;
$query = 'SELECT * FROM coaches';
$allCoaches = $db->query($query);
foreach ($allCoaches as $aCoach) {
echo 'Coach ' . $aCoach['id'] . ":\r\n " . $aCoach['firstname'] . "\r\n " . $aCoach['lastname'] . "\r\n " . $aCoach['phone'] . "\r\n " . $aCoach['email'] . "\r\n\r\n";
}
}
/*
Checks for data in the POST array.
If it has data it switches on the two possible command types.
*/
if ($_POST != NULL) {
connectToDB();
switch ($_POST['command']) {
case 'INSERT':
insertOperation($_POST);
//pass the $_POST array to the insertOperation
break;
case 'SELECT':
$params = array($_POST['options']);
selectOperation($params);
//perform the select operation,
// I didn't really need to pass the $_POST array here...
break;
}
//closeDB();
} else {
echo "you didn't post anything...\r\n\r\n";
}
