CiviCRM

Från Wikimedia
Hoppa till navigering Hoppa till sök
Denna sida hålls inte längre uppdaterad, och behålls främst av historiska skäl.
Se eventuellt diskussionssidan för mer information.


CiviCRM är ett customer relationship management system som används inom Wikimedia Foundation och några Wikimedia chapters, bl.a. Wikimedia Deutschland och Wikimedia Sverige. Den här sidan dokumenterar några konventioner inom systemet.

En bra dokumentation finns på http://en.flossmanuals.net/CiviCRM/.

Contributions/Bidrag

  • Invoice ID: unikt internt ID, samma som id i databasen (ses t.ex. från cid-parameter i adressfält). Om där finns mer än ett bidrag i samma transaktion (t.ex. samtidig medlemsavgift och donation) är det det minsta id av alla bidrag, följt av ":3", ":2" osv., var den mindska ID har högsta löpnummret och visar hur mångar delar finns.
  • Transaction ID: unikt externt ID:
    • Payson betalningar: "PSN:{Payson ref}" eller "PSN:{Payson ref}:4", "PSN:{Payson ref}:3", ...
    • Bankgiro betalningar: "OCR:{OCR numret}" eller "OCR:{OCR numret}:1" osv., OCR numret beräknas från Invoice ID som fylls upp med nollor tills det blir 9 siffror, och en kontrollsiffra i slutet. Exempel: det unika interna ID är 456 och Invoice ID blir 456 (eller 456:3, 456:2, ...). OCR numret är nu 0000004564, och Transaction ID blir OCR:0000004564 (eller OCR:0000004564:3 ...)
    • Spontana bankgirobetalningar: "AVN:{avinummer}" avinummer anges i bankgiroutdraget. 12 siffror.
  • Receipt Date är dagen när vi först vet om en donation/lägga in bidrag i databasen, och Received är dagen när vi får bidrag.

Contribution types 2012

Varje kostnadsställe har dessa sex underkategorier så att projektledaren kan rapportera mer detaljerat. Om inget rapporteras sätts den automatiskt till "Övrigt"

  • Verksamhet
  • Material
  • Resor
  • Kost och Logi
  • Personal
  • Övrigt

Contribution types 2011

  • 1110 Member Dues, 1110
  • 1201 Donation, Donation till WMSE, 1201
  • 1202 Donation, anonym, Donation till WMSE, anonym, 1202
  • 1211 Campaign Contribution, Funraiser WMF, shared 50/50, 1211
  • 1212 Campaign Contribution, anonym, Funraiser WMF, shared 50/50, contributor does not want his name published, 1212
  • 1300 Projektbidrag, 1300
  • 1310 Wikimedia Foundation Grants, 1310
  • 1500 Övriga inträkter, 1500
  • 2110 Förbrukningsvaror kontorsmaterial, 2110
  • 2150 Teknikinköp, 2150
  • 2210 Bank och betalningsavgifter, 2210
  • 2220 Postbox, 2220
  • 2230 Server, 2230
  • 2300 Årsmöte, 2300
  • 3000 Infomaterial, 3000
  • 4001 Chaptersmöte, 4001
  • 4002 Wikimania, 4002
  • 4003 Nordenmöte, 4003
  • 4004 Planeringsmöte hösten, 4004
  • 4110 Närvaro på mässor, 4110
  • 4111 Bokmässan, 4111
  • 4121 GLAM-Wiki, 4121
  • 4211 Lokala aktiviteter, 4211
  • 4212 Internet i Sverige, 4212
  • 4213 Wikipedia 10 år, 4213
  • 4214 Filmprojekt, 4214
  • 4215 Stöd till gemenskapens projekt, 4215
  • 4220 Fotosafaris, 4220
  • 4221 Fotosafari FFKP, Cloudberry grant, 4221
  • 5100 Fundraising återbetalning Wikimedia Foundation, 5100
  • 5200 Oförutsedda utgifter, 5200
  • 5201 Uttgifter året innan, 5201
  • 9000 interna ombokningar, interna ombokningar mellan olika konto, 9000

Payment instruments

  • Cash, instrument_id=64, payment_instrument_id=3
  • EFT, instrument_id=66, payment_instrument_id=5
  • Payson API, instrument_id=525, payment_instrument_id=6

accounting

http://wiki.civicrm.org/confluence/pages/worddav/preview.action?fileName=CiviCon+2011+CiviAccounts.ppt&pageId=39452787 


SELECT *, CONCAT('PSN:',`trxn_id`) as 'concat'
FROM  `civicrm_contribution` 
WHERE  `payment_instrument_id` =6
 AND  `trxn_id` IS NOT NULL 
 AND  `trxn_id` NOT LIKE  'PSN:%'
LIMIT 0 , 30

UPDATE  `civicrm_contribution` 
SET `trxn_id` = CONCAT('PSN:',`trxn_id`)
WHERE  `payment_instrument_id` =6
 AND  `trxn_id` IS NOT NULL 
 AND  `trxn_id` NOT LIKE  'PSN:%'
 AND  `id` = 29

UPDATE civicrm_contribution_type
SET name=CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name FROM 2)))

UPDATE civicrm_contribution
SET `contribution_type_id` = 46
WHERE  `contribution_type_id` = 37
 AND  `receipt_date` >=  '2012-01-01'

UPDATE civicrm_contribution_type 
SET accounting_code = SUBSTRING(name, 1, 4)
WHERE SUBSTRING(name, 5, 1) = ' '


move ingoing balance 

UPDATE `civicrm_contribution`
SET receipt_date='2000-01-01', receive_date='2000-01-01'
WHERE `id`>= 4811 AND `id`<= 4818

UPDATE `civicrm_contribution`
SET receipt_date='2000-01-01', receive_date='2000-01-01'
WHERE `trxn_id` LIKE "ib2013:%" AND `receive_date` = '2013-01-01'

new contribution types new year 

UPDATE civicrm_contribution_type
SET name=CONCAT('M',name),
 accounting_code=CONCAT('M',accounting_code)
WHERE CONCAT(,SUBSTRING(accounting_code,1,1) * 1) = SUBSTRING(accounting_code,1,1)



INSERT INTO  `civicrm`.`civicrm_contribution_type` (`id` ,`name` ,`accounting_code` ,`description` ,`is_deductible` ,`is_reserved` ,`is_active`)
VALUES (NULL ,  '7201 Möte för generaldirektörer',  '7201', NULL ,  '0',  '0',  '1');

låsa system på databasnivå

DELIMITER ;;
CREATE TRIGGER civicrm_contribution_before_update BEFORE UPDATE ON civicrm_contribution FOR EACH ROW
IF OLD.receipt_date <  '2015-01-01' OR OLD.receive_date <  '2015-01-01' OR NEW.receipt_date <  '2015-01-01' OR NEW.receive_date < '2015-01-01' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Records before 2015-01-01 are locked.';
END IF;;

CREATE TRIGGER civicrm_contribution_before_delete BEFORE DELETE ON civicrm_contribution FOR EACH ROW
IF OLD.receipt_date <  '2015-01-01' OR OLD.receive_date <  '2015-01-01' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Records before 2015-01-01 are locked.';
END IF;; 

CREATE TRIGGER civicrm_contribution_before_insert BEFORE INSERT ON civicrm_contribution FOR EACH ROW
IF NEW.receipt_date <  '2015-01-01' OR NEW.receive_date <  '2015-01-01' THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Records before 2015-01-01 are locked.';
END IF;;
DELIMITER ;

skapa reports

timmarna på ett visst projekt: 

SELECT `civicrm_contact`.`display_name`,
 MONTH(civicrm_contribution.receive_date) as month,
 SUM( `civicrm_value_accounting_2`.`worktime_8` ) AS worktime
FROM `civicrm_contribution`
JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
WHERE `civicrm_contribution`.`payment_instrument_id` = 16
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`contribution_type_id` = 98
 AND YEAR(civicrm_contribution.receive_date) = '2012'
GROUP BY MONTH(civicrm_contribution.receive_date), `civicrm_value_accounting_2`.`contact_10`

Detaljerna visst projekt av visst person: 

SELECT * FROM (SELECT
 `civicrm_contribution`.id,
 `civicrm_contact`.`display_name`,
 `civicrm_contact`.`id` AS contact_id,
 MONTH(civicrm_contribution.receive_date) as month,
 DAYOFMONTH(civicrm_contribution.receive_date) as day,
 civicrm_note.note,
 `civicrm_value_accounting_2`.`worktime_8` AS worktime
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
 LEFT JOIN civicrm_note ON (civicrm_contribution.id = civicrm_note.entity_id AND civicrm_note.entity_table = 'civicrm_contribution')
WHERE `civicrm_contribution`.`payment_instrument_id` = 16
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`contribution_type_id` = 98
 AND YEAR(civicrm_contribution.receive_date) = '2012'
 AND `civicrm_contact`.`id` = 676
ORDER BY civicrm_note.id DESC) AS tmp
GROUP BY tmp.`id`

Detaljerna av visst person utanför ett visst projet: 

SELECT * FROM (SELECT
 `civicrm_contribution`.id,
 `civicrm_contact`.`display_name`,
 `civicrm_contact`.`id` AS contact_id,
 MONTH(civicrm_contribution.receive_date) as month,
 DAYOFMONTH(civicrm_contribution.receive_date) as day,
 civicrm_note.note,
 `civicrm_value_accounting_2`.`worktime_8` AS worktime,
 `civicrm_contribution_type`.name
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
 LEFT JOIN civicrm_note ON (civicrm_contribution.id = civicrm_note.entity_id AND civicrm_note.entity_table = 'civicrm_contribution')
 JOIN `civicrm_contribution_type` ON `civicrm_contribution`.`contribution_type_id` =`civicrm_contribution_type`.id
WHERE `civicrm_contribution`.`payment_instrument_id` = 16
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`contribution_type_id` != 98
 AND YEAR(civicrm_contribution.receive_date) = '2012'
 AND `civicrm_contact`.`id` = 676
ORDER BY civicrm_note.id DESC) AS tmp
GROUP BY tmp.`id`

årsarbetstid: 

SELECT
 `civicrm_contact`.`display_name`,
 `civicrm_contact`.`id` AS contact_id,
 SUM(`civicrm_value_accounting_2`.`worktime_8`) AS worktime
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
WHERE `civicrm_contribution`.`payment_instrument_id` = 16
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND YEAR(civicrm_contribution.receive_date) = '2012'
GROUP BY `civicrm_contact`.`id`

kostnader på ett visst projekt: 

SELECT
 `civicrm_contribution`.id,
 `civicrm_contact`.`display_name`,
 `civicrm_contact`.`id` AS contact_id,
 civicrm_contribution.source,
 MONTH(civicrm_contribution.receive_date) as month,
 DAYOFMONTH(civicrm_contribution.receive_date) as day
 civicrm_contribution.total_amount,
FROM `civicrm_contribution`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_contribution`.`contact_id`
WHERE `civicrm_contribution`.`payment_instrument_id` != 16
 AND `civicrm_contribution`.`payment_instrument_id` != 17
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`contribution_type_id` = 98
 AND YEAR(civicrm_contribution.receive_date) = '2012'
ORDER BY civicrm_contribution.source

detaljerad kostnader på ett visst projekt: 

SELECT * FROM (SELECT
 `civicrm_contribution`.id,
 `civicrm_contact`.`id` AS contact_id,
 `civicrm_contact`.`display_name`,
 `civicrm_value_accounting_2`.`category_6` AS category,
 `civicrm_contribution`.`total_amount`,
 civicrm_contribution.invoice_id,
 civicrm_contribution.trxn_id,
 civicrm_contribution.receive_date,
 civicrm_contribution.source,
 civicrm_note.note
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 LEFT JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_contribution`.`contact_id`
 LEFT JOIN civicrm_note ON (civicrm_contribution.id = civicrm_note.entity_id AND civicrm_note.entity_table =  'civicrm_contribution')
WHERE `civicrm_contribution`.`payment_instrument_id` != 17
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`contribution_type_id` = 188
 AND YEAR(civicrm_contribution.receive_date) = '2012'
ORDER BY category ASC, receive_date DESC, civicrm_note.id DESC) AS tmp
GROUP BY tmp.`id`
ORDER BY tmp.category ASC, tmp.receive_date DESC, tmp.contact_id ASC

kostnader i samtliga projekt: 

SELECT civicrm_contribution_type.name,
 SUM(civicrm_contribution.total_amount)
FROM civicrm_contribution
 JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id
 JOIN civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id
WHERE civicrm_contribution.receipt_date >= '2012-01-01 00:00:00'
 AND civicrm_contribution.receipt_date <= '2012-12-31 23:59:59'
 AND civicrm_contribution.cancel_date IS NULL
 AND civicrm_contact.is_deleted = 0
 AND civicrm_contribution.contribution_status_id = 1
 AND civicrm_contribution.payment_instrument_id <> 16
 AND civicrm_contribution.payment_instrument_id <> 17
GROUP BY civicrm_contribution.contribution_type_id
ORDER BY name ASC

summa pesonalkostnader under året 

SELECT
 `civicrm_contact`.`display_name`,
 `civicrm_value_accounting_2`.contact_10 as contact,
 SUM(`civicrm_contribution`.total_amount)
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
WHERE `civicrm_contribution`.`contribution_type_id` = 76
 and YEAR(`civicrm_contribution`.`receipt_date`) = 2012
 and `civicrm_value_accounting_2`.contact_10 > 0
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`payment_instrument_id` != 16
 AND `civicrm_contribution`.`payment_instrument_id` != 17
GROUP BY contact
ORDER BY contact

personalkostnader av visst person: 

SELECT
 `civicrm_contribution`.id,
 `civicrm_contact`.`display_name`,
 `civicrm_value_accounting_2`.contact_10 as contact,
 `civicrm_contribution`.total_amount,
 `civicrm_contribution`.source
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
WHERE `civicrm_contribution`.`contribution_type_id` = 76
 and YEAR(`civicrm_contribution`.`receipt_date`) = 2012
 and `civicrm_value_accounting_2`.contact_10 = 479
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND `civicrm_contribution`.`payment_instrument_id` != 16
 AND `civicrm_contribution`.`payment_instrument_id` != 17
ORDER BY contact


personalkostnader och timmar hela år per projekt och person: 

SELECT
 `civicrm_contact`.`id` AS contact_id,
 `civicrm_contact`.`display_name`,
 `civicrm_contribution`.`contribution_type_id`,
 `civicrm_contribution_type`.name,
 SUM(`civicrm_value_accounting_2`.`worktime_8`) AS worktime,
 SUM(`civicrm_contribution`.`total_amount`) AS cost
FROM `civicrm_contribution`
 JOIN `civicrm_value_accounting_2` ON `civicrm_contribution`.`id` = `civicrm_value_accounting_2`.`entity_id`
 JOIN `civicrm_contact` ON `civicrm_contact`.`id` = `civicrm_value_accounting_2`.`contact_10`
 JOIN `civicrm_contribution_type` ON `civicrm_contribution`.`contribution_type_id` =`civicrm_contribution_type`.id
WHERE `civicrm_contribution`.`payment_instrument_id` = 16
 AND `civicrm_contribution`.`contribution_status_id` = 1
 AND YEAR(civicrm_contribution.receive_date) = '2012'
GROUP BY `civicrm_contact`.`id`, `civicrm_contribution`.`contribution_type_id`
skapa kategorierna till betalningar
INSERT INTO civicrm_value_accounting_2 (entity_id, category_6)
 SELECT 
  civicrm_contribution.id AS entity_id,
  6570 AS category_6
 FROM civicrm_contribution
 LEFT JOIN civicrm_value_accounting_2
  ON civicrm_value_accounting_2.entity_id = civicrm_contribution.id
 WHERE civicrm_contribution.contribution_type_id = 168
  AND civicrm_value_accounting_2.id is null
uppdatera kategorierna till betalningar
UPDATE civicrm_contribution, civicrm_value_accounting_2
SET civicrm_value_accounting_2.category_6 = 1930
WHERE civicrm_value_accounting_2.entity_id = civicrm_contribution.id
 AND (civicrm_value_accounting_2.category_6 = 0 OR civicrm_value_accounting_2.category_6 is null)
 AND civicrm_contribution.contribution_type_id = 191



UPDATE civicrm_contribution, civicrm_value_accounting_2
SET civicrm_value_accounting_2.category_6 = 1732
WHERE civicrm_value_accounting_2.entity_id = civicrm_contribution.id
 AND civicrm_value_accounting_2.category_6 = 2990
 AND civicrm_contribution.contribution_type_id = 192
 AND civicrm_contribution.contact_id = 1413
 AND civicrm_contribution.trxn_id LIKE '%:TFA:%'
uppdatera contributuion type
UPDATE civicrm_contribution, civicrm_value_accounting_2
SET civicrm_contribution.contribution_type_id = 192
WHERE civicrm_value_accounting_2.entity_id = civicrm_contribution.id
 AND (civicrm_value_accounting_2.category_6 = 2514
 OR civicrm_value_accounting_2.category_6 = 2710
 OR civicrm_value_accounting_2.category_6 = 2731
 OR civicrm_value_accounting_2.category_6 = 2910
 OR civicrm_value_accounting_2.category_6 = 2920
 OR civicrm_value_accounting_2.category_6 = 2930
 OR civicrm_value_accounting_2.category_6 = 2931
 OR civicrm_value_accounting_2.category_6 = 2940
 OR civicrm_value_accounting_2.category_6 = 2941
 OR civicrm_value_accounting_2.category_6 = 2960
 OR civicrm_value_accounting_2.category_6 = 2990
 OR civicrm_value_accounting_2.category_6 = 2940
)
 AND civicrm_contribution.contribution_type_id = 186


ingoing balances: 

 SELECT SUM(civicrm_contribution.total_amount) as total_amount,
  civicrm_contribution.payment_instrument_id,
  civicrm_option_value.label
   FROM civicrm_contribution
 JOIN civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id
 LEFT JOIN civicrm_value_accounting_2 ON civicrm_contribution.id = civicrm_value_accounting_2.entity_id
 JOIN civicrm_option_value ON civicrm_option_value.value = civicrm_contribution.payment_instrument_id AND civicrm_option_value.option_group_id = 10
 WHERE civicrm_contribution.receive_date <= '2012-12-31 23:59:59'
 AND civicrm_contribution.receive_date > '2000-01-01 00:00:00'
 AND civicrm_contribution.cancel_date IS NULL
 AND civicrm_contact.is_deleted = 0
 AND civicrm_contribution.contribution_status_id = 1
 AND civicrm_value_accounting_2.category_6 > 5
 GROUP BY civicrm_contribution.payment_instrument_id 
 ORDER BY civicrm_contribution.payment_instrument_id DESC



 SELECT SUM(civicrm_contribution.total_amount) as total_amount,
  civicrm_value_accounting_2.category_6,
  civicrm_value_accounting_2.contact_10 
   FROM civicrm_contribution
 JOIN civicrm_contact ON civicrm_contribution.contact_id = civicrm_contact.id
 LEFT JOIN civicrm_value_accounting_2 ON civicrm_contribution.id = civicrm_value_accounting_2.entity_id
 WHERE civicrm_contribution.receipt_date <= '2012-12-31 23:59:59'
 AND civicrm_contribution.receipt_date > '2000-01-01 00:00:00'
 AND civicrm_contribution.cancel_date IS NULL
 AND civicrm_contact.is_deleted = 0
 AND civicrm_contribution.contribution_status_id = 1
 GROUP BY civicrm_value_accounting_2.category_6, civicrm_value_accounting_2.contact_10 
 ORDER BY civicrm_value_accounting_2.category_6, civicrm_value_accounting_2.contact_10 ASC

update category order 

UPDATE  `civicrm_option_value`
SET civicrm_option_value.weight = civicrm_option_value.value
WHERE  `option_group_id` =82

uppdate error

update to 4.2

https://github.com/civicrm/civicrm-core/blob/master/CRM/Upgrade/Incremental/php/FourTwo.php 

Index: CRM/Upgrade/Incremental/php/FourTwo.php
===================================================================
--- CRM/Upgrade/Incremental/php/FourTwo.php	(revision 45958)
+++ CRM/Upgrade/Incremental/php/FourTwo.php	(working copy)
@@ -157,8 +157,10 @@
     foreach($tables as $tableName => $fKey){
       $foreignKeyExists = CRM_Core_DAO::checkConstraintExists($tableName,$fKey);
       if ($foreignKeyExists){
-        CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP FOREIGN KEY {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
-        CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
+        if($tableName == 'civicrm_group') {
+//          CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP FOREIGN KEY {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
+//          CRM_Core_DAO::executeQuery("ALTER TABLE {$tableName} DROP INDEX {$fKey}", $params, TRUE, NULL, FALSE, FALSE);
+        }
       }
     }
  

array('civicrm_contribution_page' =>'FK_civicrm_contribution_page_payment_processor_id',
                   'civicrm_event' => 'FK_civicrm_event_payment_processor_id',
                   'civicrm_group' => 'FK_civicrm_group_saved_search_id',
                   );

SHOW CREATE TABLE  `civicrm_event`
ALTER TABLE civicrm_event DROP FOREIGN KEY FK_civicrm_event_payment_processor_id
ALTER TABLE civicrm_event DROP FOREIGN KEY civicrm_event_ibfk_12
ALTER TABLE civicrm_event DROP FOREIGN KEY civicrm_event_ibfk_4
ALTER TABLE civicrm_event DROP FOREIGN KEY civicrm_event_ibfk_8
ALTER TABLE civicrm_event DROP INDEX FK_civicrm_event_payment_processor_id

SHOW ENGINE INNODB STATUS
  1. 1025 - Error on rename of './civicrm_dev/#sql-1ed_e5751' to './civicrm_dev/civicrm_event' (errno: 150)

130713 17:39:54 Error in foreign key constraint of table civicrm_dev/civicrm_event: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , 

CONSTRAINT "civicrm_event_ibfk_12" FOREIGN KEY ("payment_processor_id") REFERENCES "civicrm_payment_processor" ("id") ON DELETE SET NULL

LATEST DETECTED DEADLOCK 


SELECT
CONCAT("ALTER TABLE `", `K`.`TABLE_NAME`, "` DROP FOREIGN KEY `", `K`.`CONSTRAINT_NAME`, "`;") "DROP"
FROM `information_schema`.`KEY_COLUMN_USAGE` `K`
WHERE `K`.`REFERENCED_TABLE_SCHEMA` = "civicrm_dev"
AND `K`.`CONSTRAINT_NAME` LIKE "%_ibfk_%"



Index: CRM/Utils/Type.php
===================================================================
--- CRM/Utils/Type.php	(revision 45958)
+++ CRM/Utils/Type.php	(working copy)
@@ -80,6 +80,7 @@
         break;
 
       case 256:
+      case 12:
         $string = 'Timestamp';
         break;


DELETE FROM `civicrm_job_log` WHERE `civicrm_job_log`.`run_time` <= STR_TO_DATE('2013-07-01 00:00:00', '%Y-%m-%d %H:%i:%s')



[Error: Upgrade DB to 4.3.alpha1: SQL]
Error Field	Error Value
Type	DB_Error
Code	-1
Message	DB Error: unknown error
Mode	16
UserInfo	ALTER TABLE `civicrm_pledge` DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id, DROP INDEX FK_civicrm_pledge_contribution_type_id [nativecode=1025 ** Error on rename of './civicrm_dev/civicrm_pledge' to './civicrm_dev/#sql2-1f0-17b0' (errno: 152)]
DebugInfo	ALTER TABLE `civicrm_pledge` DROP FOREIGN KEY FK_civicrm_pledge_contribution_type_id, DROP INDEX FK_civicrm_pledge_contribution_type_id [nativecode=1025 ** Error on rename of './civicrm_dev/civicrm_pledge' to './civicrm_dev/#sql2-1f0-17b0' (errno: 152)]
PEAR_Exception: DB Error: unknown error in unknown on line unknown
- DB_Error: DB Error: unknown error in unknown on line unknown
Exception trace
#	Function	Location
0	CRM_Core_Error::exceptionHandler(Object(DB_Error))	unknown:unknown
1	call_user_func(Array, Object(DB_Error))	/home/www/sites/dev/sites/all/modules/civicrm/packages/PEAR.php:931
2	PEAR_Error->PEAR_Error('DB Error: unknow…', -1, 16, Array, 'ALTER TABLE `civ…')	/home/www/sites/dev/sites/all/modules/civicrm/packages/DB.php:969
3	DB_Error->DB_Error(-1, 16, Array, 'ALTER TABLE `civ…')	/home/www/sites/dev/sites/all/modules/civicrm/packages/PEAR.php:564
4	PEAR->raiseError(null, -1, null, null, 'ALTER TABLE `civ…', 'DB_Error', true)	/home/www/sites/dev/sites/all/modules/civicrm/packages/DB/common.php:1905
5	DB_common->raiseError(-1, null, null, null, '1025 ** Error on…')	/home/www/sites/dev/sites/all/modules/civicrm/packages/DB/mysql.php:898
6	DB_mysql->mysqlRaiseError()	/home/www/sites/dev/sites/all/modules/civicrm/packages/DB/mysql.php:327
7	DB_mysql->simpleQuery('ALTER TABLE `civ…')	/home/www/sites/dev/sites/all/modules/civicrm/packages/DB/common.php:1216
8	DB_common->query('ALTER TABLE `civ…')	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Utils/File.php:284
9	CRM_Utils_File::sourceSQLFile('mysql://civicrm-…', ' SELECT @wo…', null, true)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Upgrade/Form.php:159
10	CRM_Upgrade_Form->source(' SELECT @wo…', true)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Upgrade/Form.php:299
11	CRM_Upgrade_Form->processLocales('/home/www/sites/…', '4.3.alpha1')	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Upgrade/Form.php:323
12	CRM_Upgrade_Form->processSQL('4.3.alpha1')	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourThree.php:981
13	CRM_Upgrade_Incremental_php_FourThree::task_4_3_x_runSql(Object(CRM_Queue_TaskContext), '4.3.alpha1')	unknown:unknown
14	call_user_func_array(Array, Array)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Queue/Task.php:79
15	CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Queue/Runner.php:186
16	CRM_Queue_Runner->runNext(true)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:44
17	{closure}()	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:80
18	CRM_Queue_ErrorPolicy->call(Object(Closure))	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:47
19	CRM_Queue_Page_AJAX::runNext(Array)	unknown:unknown
20	call_user_func(Array, Array)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Core/Invoke.php:258
21	CRM_Core_Invoke::runItem(Array)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Core/Invoke.php:70
22	CRM_Core_Invoke::_invoke(Array)	/home/www/sites/dev/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
23	CRM_Core_Invoke::invoke(Array)	/home/www/sites/dev/sites/all/modules/civicrm/drupal/civicrm.module:436
24	civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')	unknown:unknown
25	call_user_func_array('civicrm_invoke', Array)	/home/www/sites/dev/includes/menu.inc:517
26	menu_execute_active_handler()	/home/www/sites/dev/index.php:21
27	{main}	 



KEY `FK_civicrm_contribution_contact_id` (`contact_id`),
KEY `FK_civicrm_contribution_contribution_type_id` (`contribution_type_id`),
KEY `FK_civicrm_contribution_contribution_page_id` (`contribution_page_id`),
KEY `FK_civicrm_contribution_contribution_recur_id` (`contribution_recur_id`),
KEY `FK_civicrm_contribution_honor_contact_id` (`honor_contact_id`),
KEY `FK_civicrm_contribution_address_id` (`address_id`),
KEY `FK_civicrm_contribution_campaign_id` (`campaign_id`),
CONSTRAINT `FK_civicrm_contribution_address_id` FOREIGN KEY (`address_id`) REFERENCES `civicrm_address` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_contribution_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `civicrm_campaign` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_contribution_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_civicrm_contribution_contribution_page_id` FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_contribution_contribution_recur_id` FOREIGN KEY (`contribution_recur_id`) REFERENCES `civicrm_contribution_recur` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_contribution_contribution_type_id` FOREIGN KEY (`contribution_type_id`) REFERENCES `civicrm_financial_type` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_contribution_honor_contact_id` FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL



KEY `FK_civicrm_pledge_contact_id` (`contact_id`),
KEY `FK_civicrm_pledge_contribution_type_id` (`contribution_type_id`),
KEY `FK_civicrm_pledge_contribution_page_id` (`contribution_page_id`),
KEY `FK_civicrm_pledge_honor_contact_id` (`honor_contact_id`),
KEY `FK_civicrm_pledge_campaign_id` (`campaign_id`)

updating to 4.3.6

CRM/Upgrade/Incremental/php/FourThree.php (966) 

UPDATE  `civicrm_dev`.`civicrm_option_value` SET  `is_active` =  '1' WHERE  `civicrm_option_value`.`id` = 62;

find . -name '*.php' -o -name '*.inc' -o -name '*.module' -type f -exec sed -i 's/contribution_type/financial_type/g' {} \;

UPDATE  `variable` 
SET name = REPLACE(name, 'contribution_type', 'financial_type')
WHERE  `name` LIKE  '%contribution_type%

u7649541_a: old copy u7649541_b: active db u7649541_c: copy 2014-12-26 u7649541_d: copy 2014-12-27

other

mysqldump --quick -u holger -p civicrm | gzip > civicrm.gz
mysqldump --quick -u holger -p drupal7 | gzip > drupal7.gz
mysqladmin -h 'mysql.u7649541.fsdata.se' -u u7649541 -p create u7649541_a
gunzip < drupal7.gz| mysql -h 'mysql.u7649541.fsdata.se' -u u7649541 -p u7649541_a



mysqldump --quick  -h 'mysql.u7649541.fsdata.se' -u u7649541 -p u7649541_a | gzip > drupal7_test.gz
mysqladmin -h 'localhost' -u holger -p create drupal7_test
gunzip < drupal7_test.gz| mysql -h 'localhost' -u holger -p drupal7_test



Behövs inte längre, workaround: avaktivera och aktivera databaslogging. 

Index: CRM/Logging/Schema.php
===================================================================
--- CRM/Logging/Schema.php	(revision 45958)
+++ CRM/Logging/Schema.php	(working copy)
@@ -367,7 +367,7 @@
       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
       $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
 
-      $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
+      $sqlStmt = "INSERT INTO log_{tableName} (";
       foreach ($columns as $column) {
         $sqlStmt .= "$column, ";
      }

Fixat i CRM-10404 

Index: CRM/Utils/Type.php
===================================================================
--- CRM/Utils/Type.php	(revision 45958)
+++ CRM/Utils/Type.php	(working copy)
@@ -98,6 +98,10 @@
         $string = 'Time';
         break;
 
+      case 12:
+        $string = 'Timestamp';
+        break;
+
       case 16:
         $string = 'Boolean';
         break;

behövs inte längre, civiCRM finns inte längre i SVN. 

Index: xml/GenCode.php
===================================================================
--- xml/GenCode.php	(revision 45958)
+++ xml/GenCode.php	(working copy)
@@ -1,4 +1,5 @@
 <?php
+set_time_limit(45);
 ini_set('include_path', '.' . PATH_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'packages' . PATH_SEPARATOR . '..');
 ini_set('memory_limit', '512M');

updating civicrm 4.4LTS

make all status codes available again 

Index: civicrm/CRM/Contribute/BAO/Contribution.php
===================================================================
--- civicrm/CRM/Contribute/BAO/Contribution.php	(revision 366)
+++ civicrm/CRM/Contribute/BAO/Contribution.php	(working copy)
@@ -2921,6 +2921,7 @@
         return FALSE;
       }
     }
+    return;
     $contributionStatuses = CRM_Contribute_PseudoConstant::contributionStatus(NULL, 'name');
     $checkStatus = array(
       'Cancelled' => array('Completed', 'Refunded'),
Index: civicrm/CRM/Contribute/Form/Contribution.php
===================================================================
--- civicrm/CRM/Contribute/Form/Contribution.php	(revision 366)
+++ civicrm/CRM/Contribute/Form/Contribution.php	(working copy)
@@ -694,7 +694,7 @@
                    'Overdue',
                    'In Progress'
                  ) as $suppress) {
-          unset($status[CRM_Utils_Array::key($suppress, $statusName)]);
+//          unset($status[CRM_Utils_Array::key($suppress, $statusName)]);
         }
       }
       else {
@@ -709,9 +709,9 @@
         case 'Completed':
         case 'Cancelled':
         case 'Refunded':
-          unset($status[CRM_Utils_Array::key('In Progress', $statusName)]);
-          unset($status[CRM_Utils_Array::key('Pending', $statusName)]);
-          unset($status[CRM_Utils_Array::key('Failed', $statusName)]);
+//          unset($status[CRM_Utils_Array::key('In Progress', $statusName)]);
+//          unset($status[CRM_Utils_Array::key('Pending', $statusName)]);
+//          unset($status[CRM_Utils_Array::key('Failed', $statusName)]);
           break;
         case 'Pending':
         case 'In Progress':
Index: civicrm/templates/CRM/Contact/Page/Inline/ContactInfo.tpl
===================================================================
--- civicrm/templates/CRM/Contact/Page/Inline/ContactInfo.tpl	(revision 366)
+++ civicrm/templates/CRM/Contact/Page/Inline/ContactInfo.tpl	(working copy)
@@ -49,6 +49,12 @@
{ts}Nickname{/ts}
{$nick_name}
+      {if !empty($legal_identifier)}>

+

+
{ts}Legal Identifier{/ts}
+
{$legal_identifier}
+ 
+      {/if}
 
       {if $contact_type eq 'Organization'}

https://www.wikimedia.se/sv/civicrm/api/explorer#explorer

uppdate

Index: CRM/Logging/Schema.php
===================================================================
--- CRM/Logging/Schema.php	(revision 159)
+++ CRM/Logging/Schema.php	(working copy)
@@ -387,7 +387,7 @@
       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
       $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
 
-      $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
+      $sqlStmt = "INSERT INTO log_{tableName} (";
       foreach ($columns as $column) {
         $sqlStmt .= "$column, ";
       }
Index: CRM/Utils/Type.php
===================================================================
--- CRM/Utils/Type.php	(revision 159)
+++ CRM/Utils/Type.php	(working copy)
@@ -98,6 +98,10 @@
         $string = 'Time';
         break;
 
+      case 12:
+        $string = 'Timestamp';
+        break;
+
       case 16:
         $string = 'Boolean';
         break;
Hämtad från ”https://se.wikimedia.org/w/index.php?title=CiviCRM&oldid=71934”